Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 2 of 2 total
Thread Dataset export to CSV file
Thu, May 21 2020 1:45 AMPermanent Link

Bill

Hi all,
I often need to export to MS Excel or other external applications some data from local dataset for little calculation or printing needings.
Is there an easy or a general way exporting local data avoiding a pass back to server?
Thanks in advance
Thu, May 21 2020 11:13 AMPermanent Link

Walter Matte

Tactical Business Corporation

Bill

I have written my own server - so hopefully this approach can work for you.

I get the request and build the response......  I built the CSV file as lines in a TStringList - then returned the TEXT


// set your headers....

           Response.ContentType := 'text/plain';
           Response.HeaderText :=
               Response.HeaderText +
               RtcString('Access-Control-Allow-Origin: *' + #13#10) +
               RtcString('Cache-Control: must-revalidate, post-check=0, pre-check=0' + #13#10) +
               RtcString('Cache-Control: public' + #13#10) +
               RtcString('Content-Description: File Transfer' + #13#10) +
               RtcString('Content-Disposition: attachment; filename=SageINV.csv' + #13#10);    


// this is the content                  
       Write(RtcString(Conn.EWBSAGE(Request.Query)));    // EWBSAGE Routine below creates CSV
         



function TdmUniSQL.EWBSAGE(Query: TRtcHttpValues): string;
var
 i   : integer;
 fDT : TDateTime;
 sl  : TStringList;
 sTax: string;
begin
 for i := 0 to Query.ItemCount - 1 do
 begin
   if Uppercase(Query.ItemName[i]) = 'SFROMDT' then
//      fDT := StrToDateTime(Query.ItemValue[i], fmtset);
     fDT := myStr2DT(Query.ItemValue[i]);   
 end;       

 sl := TStringList.Create;
 sl.Add('<Version>');
 sl.Add('"12001","1"');      // 1 Canada, 2 USA, 3 French, 7 International
 sl.Add('</Version>');

 tbCtrl.Open;
 
 qGen.SQL.Clear;
 qGen.SQL.Add('SELECT');
 qGen.SQL.Add('CMP.Company,');
 qGen.SQL.Add('INV.InvId,');
 qGen.SQL.Add('INV.CompaniesId,');
 qGen.SQL.Add('INV.JobordersId,');
 qGen.SQL.Add('INV.PeopleId,');
 qGen.SQL.Add('INV.FromDate,');
 qGen.SQL.Add('INV.ToDate,');
 qGen.SQL.Add('INV.InvDate,');
 qGen.SQL.Add('INV.InvNum,');
 qGen.SQL.Add('INV.InvAmt,');
 qGen.SQL.Add('INV.HSTAmt,');
 qGen.SQL.Add('INV.QSTAmt,');
 qGen.SQL.Add('INV.TotalAmt');
 qGen.SQL.Add('FROM');
 qGen.SQL.Add('dbo.PR2Inv INV');
 qGen.SQL.Add('INNER JOIN dbo.Companies CMP ON (INV.CompaniesId = CMP.CompaniesID)');
 qGen.SQL.Add('Where InvDate = :InvDate');
 
 qGen.ParamByName('InvDate').AsDate := fDT;

 qGen.Open;
 qGen.First;
 while not qGen.EOF do
 begin
   qHelp.SQL.Clear;
   qHelp.SQL.Add('SELECT');
   qHelp.SQL.Add('INV.InvId,');
   qHelp.SQL.Add('T.CompaniesId,');
   qHelp.SQL.Add('T.JobOrdersId,');
   qHelp.SQL.Add('T.PeopleId,');
   qHelp.SQL.Add('PO.BillRate,');
   qHelp.SQL.Add('Sum(TS.WorkHours) AS RegHours,');
   qHelp.SQL.Add('Sum(TS.WorkHours * PO.BillRate) AS RegAmt');
   qHelp.SQL.Add('FROM');
   qHelp.SQL.Add('dbo.PR2Inv INV');
   qHelp.SQL.Add('INNER JOIN dbo.PR2Time T ON (T.CompaniesId = INV.CompaniesId) and (T.JobOrdersId = INV.JobOrdersID) and (T.PeopleId = INV.PeopleId)');
   qHelp.SQL.Add('INNER JOIN dbo.PR2TimeSum TS ON (T.TimeId = TS.TimeId)');
   qHelp.SQL.Add('INNER JOIN dbo.Positions PO ON (T.PositionsId = PO.PositionsID)');
   qHelp.SQL.Add('WHERE');
   qHelp.SQL.Add('INV.InvId = :InvId AND');
   qHelp.SQL.Add('TS.WorkDate >= INV.FromDate AND');
   qHelp.SQL.Add('TS.WorkDate <= INV.ToDate');
   qHelp.SQL.Add('GROUP BY');
   qHelp.SQL.Add('INV.InvId,');
   qHelp.SQL.Add('T.CompaniesId,');
   qHelp.SQL.Add('T.JobOrdersId,');
   qHelp.SQL.Add('T.PeopleId,');
   qHelp.SQL.Add('PO.BillRate');
   
   qHelp.ParamByName('InvId').AsInteger := qGen.FieldByName('InvId').AsInteger;

   qHelp.Open;
   if qHelp.RecordCount > 0 then
   begin
     sTax := '';
     if qGen.FieldByName('HSTAmt').AsFloat > 0 then
     begin
       sTax := sTax + ',"' + tbCtrl.FieldByName('SageHST').AsString + '","0","1","' +
                             Format('%0.02f',[tbCtrl.FieldByName('HSTRate').AsFloat]) + '","' +
                             Format('%0.02f',[qGen.FieldByName('HSTAmt').AsFloat]) + '"';
     end;
       
     if qGen.FieldByName('QSTAmt').AsFloat > 0 then
     begin
       sTax := sTax + ',"' + tbCtrl.FieldByName('SageQST').AsString + '","0","1","' +
                             Format('%0.02f',[tbCtrl.FieldByName('QSTRate').AsFloat]) + '","' +
                             Format('%0.02f',[qGen.FieldByName('QSTAmt').AsFloat]) + '"';
     end;
       
     sl.Add('<SalInvoice>');
     sl.Add('"' + Copy(qGen.FieldByName('Company').AsString,1,52) + '",,,,,,,,,,,,');
     sl.Add('"' + Inttostr(qHelp.RecordCount) + '",,"' + qGen.FieldByName('InvNum').AsString + '","' +
                  FormatDateTime('m-d-yyyy',qGen.FieldByName('InvDate').AsDateTime) + '","0","","' +
                  Format('%0.02f',[qGen.FieldByName('TotalAmt').AsFloat]) + '","0.00"' + sTax);
     qHelp.First;
     while not qHelp.EOF do
     begin
       sl.Add('"' + tbCtrl.FieldByName('SageItem').AsString + '","' +
                  Format('%0.04f',[qHelp.FieldByName('RegHours').AsFloat]) + '","' +
                  Format('%0.04f',[qHelp.FieldByName('BillRate').AsFloat]) + '","' +
                  Format('%0.02f',[qHelp.FieldByName('RegAmt').AsFloat]) + '"');
                                                                     
       qHelp.Next;
     end;               
 
     sl.Add('</SalInvoice>');
   end;
   qHelp.Close;
   qGen.Next;             
 end;              
 qGen.Close;
  
 tbCtrl.Close;
  
 result := sl.Text;

 sl.Free;
end;



Walter
Image