I have a third party application that keeps track of every invoice of my customers. Every new invoice generates a record in a table named CUEN_M01 (where CUEN is for cuentas por cobrar which in english is accounts receivable and 01 is for movements in enterprise 01).
Then every other movement(s) related to that invoice is recorded in another table named CUEN_DET01 (DET01 for details in enterprise 01).
In my Lazarus application I already have this statements which calculate the ending balance of each invoice / reference (field REFER) a customer owes:
SQLQuery2.SQL.Text:= 'SELECT MIN(CVE_CLIE) AS CVE_CLIE, REFER, MAX(NO_PARTIDA) AS NO_PARTIDA, MIN(F_VENC) as F_VENC, min(FACTURA) as FACTURA, MAX(DOCTO) as DOCTO,' +
' MIN(DESCR) AS DESCR, SUM(IMPORTE) AS IMPORTE FROM(' +
' SELECT CM.CVE_CLIE, CM.REFER, 0 AS NO_PARTIDA, CM.FECHA_VENC AS F_VENC, CM.NO_FACTURA AS FACTURA, CM.DOCTO, C.DESCR, CM.IMPORTE*CM.SIGNO AS IMPORTE' +
' FROM CUEN_M01 CM JOIN CONC01 C ON CM.NUM_CPTO = C.NUM_CPTO' +
' WHERE CM.CVE_CLIE = ' + quotedstr(clave) +
' UNION ALL' +
' SELECT CD.CVE_CLIE, CD.REFER, CD.NO_PARTIDA, CD.FECHA_VENC AS F_VENC, CD.NO_FACTURA AS FACTURA, CD.DOCTO, C.DESCR, CD.IMPORTE*CD.SIGNO AS IMPORTE' +
' FROM CUEN_DET01 CD JOIN CONC01 C ON CD.NUM_CPTO = C.NUM_CPTO' +
' WHERE CD.CVE_CLIE = ' + quotedstr(clave) + ') AS CARACHO' +
' GROUP BY REFER' +
' HAVING ABS(SUM(IMPORTE)) > .005' +
' ORDER BY REFER, NO_PARTIDA, F_VENC';
SQLQuery2.Active := True;
DBGrid2.Refresh;
In my program the string clave contains the customer code/id.
Now, what I would like to do is to create a ZMQueryDataSet type TZMQueryDataSet whith the same information as the SQLQuery2 (which type is TSQLQuery) I already have,
then I want to save this dataset to a csv table as explained in project1 I downloaded with ZMQuery.lpk. This is the code in project1:
procedure TForm1.Button2Click(Sender: TObject);
var
vOriginalTableName:string;
begin
try
vOriginalTableName:=ZMQueryDataset1.TableName;
ZMQueryDataset1.TableName:='Test';
ShowMessage('Dataset is going to be saved to: '+
ZMQueryDataset1.ZMConnection.DatabasePath +
ZMQueryDataset1.TableName+'.csv');
ZMQueryDataset1.SaveToTable(FormatSettings.DecimalSeparator);
finally
ZMQueryDataset1.TableName:=vOriginalTableName;
end;
end;
I do not know wether there is an easier way to accomplish the task, your tips are welcome.
In advance thanks a million
Recent
