unit Quetzal; interface uses Messages, SysUtils, Classes, Graphics, Controls, Dialogs, FileCtrl, db, dbTables; type TServiceDetails = record Custno : string; ServCode : string; ServiceID: string; RentFrom : TDateTime; RentTill : TDateTime; CallFrom : TDateTime; CallTill : TDateTime; end; TCallHeaderType = record SequenceNo : string[7]; ServiceID : string[17]; TxnDate : string[8]; TxnTime : string[5]; Origin : string[20]; Duration : string[9]; CallCode : string[2]; RateDescription : string[32]; DialledNumber : string[17]; // Temporary variable only. ServiceNotes : string[22]; AmountExGST : string[13]; GSTAmount : string[13]; AmountIncGST : string[13]; ToDate : string[8]; end; procedure RidDuplicates( sBatchID: string; slFields: TStringList ); procedure QuetzalInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string); procedure GetQuetzalFaults( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; var lReturnCode:Boolean); procedure RadioInvoice( nBatch: LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string); procedure GetRadioFaults( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; var lReturnCode:Boolean); procedure TaxiInvoice( nBatch: LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string); procedure GetTaxiFaults( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; var lReturnCode:Boolean); implementation uses Math, Main, DataMod, GenFns, DateFunctions, FileFunctions, CriticalTests, MyMessage, Progress; var // Declare static variables as frequently reset and reused. nCount: Integer; // Current status for frmProgress. nFactor: Real; // Weighting for nCount in progress. frmProgress: TProgressForm; // frmProgress report to the operator. procedure RidDuplicates( sBatchID: string; slFields: TStringList ); // Fields in the list must be varchar in the tDataCollection table. // Max of 8 fields can be passed and processed - excess fields ignored. const MAXFIELDS = 8; var qryDC : TQuery; sFields : string; sValue : string; nField : integer; nAllFields : integer; slDuplicates : TStringlist; slResults : TStringlist; lContinue : boolean; nDuplicates : integer; nDuppleID : integer; nTotalCount : integer; nBlock : integer; begin sFields := ''; nAllFields := slFields.Count; if nAllFields > MAXFIELDS then nAllFields := MAXFIELDS; if (nAllFields > 0) then begin Dec(nAllFields); for nField := 0 to nAllFields do begin sValue := Trim(slFields[nField]); if (Length(sValue) > 0) then if (Length(sFields) = 0) then sFields := sValue else sFields := sFields +', '+ sValue; end; end; if (Length(sFields) > 0) then begin frmProgress.Caption := 'Duplicates in Batch: ' + sBatchID; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; nFactor := 0.1000; // Count to 100 every 1000 records. nTotalCount := 0; nBlock := 0; slResults := TStringlist.Create; slDuplicates := TStringlist.Create; sFields := sFields+ ', id'; qryDC := TQuery.Create(nil); qryDC.DatabaseName := 'dbPPdata'; qryDC.SQL.Add('SELECT '+sFields); // Add ID to the results. qryDC.SQL.Add('FROM tDataCollection'); qryDC.SQL.Add('WHERE BatchID = '+sBatchID); qryDC.SQL.Add('ORDER BY '+sFields); for nField := 0 to nAllFields do slResults.Add(' '); try qryDC.Open; lContinue := False; while not qryDC.Eof do begin for nField := 0 to nAllFields do begin sValue := Trim(qryDC.Fields.Fields[nField].AsString); lContinue := (Length(sValue) > 0) and (slResults[nField] = sValue); if not lContinue then break; end; // for loop. if lContinue then // Duplicate record found - save id for later. slDuplicates.Add(IntToStr(qryDC.FieldByName('id').AsInteger)) else // Copy new data to the array for the next test. for nField := 0 to nAllFields do slResults[nField] := qryDC.Fields.Fields[nField].AsString; qryDC.Next; Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin // and not AbortProcess frmProgress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then nCount := 0; // restart progress bar. frmProgress.ProgressBar1.Position := Trunc(nCount * nFactor); frmProgress.Msg.Caption := 'Checking (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. frmProgress.ProcessWinMessages(MainForm); end; nDuplicates := slDuplicates.Count; if (nDuplicates > 0) then begin { //*********** START TEMP ******************************************************* qryDC.Close; qryDC.SQL.Clear; qryDC.SQL.Add('SELECT ParmText FROM tParameter'); qryDC.SQL.Add('WHERE Parm LIKE ''TBSdirectory'''); qryDC.Open; sTBSdirectory := qryDC.FieldByName('ParmText').AsString; qryDC.Close; lContinue := (Length(sTBSdirectory)>0) and DirectoryExists(sTBSdirectory); if lContinue then begin sFile := sTBSdirectory + '\Duplicates' + sBatchID + '.txt'; end else begin // Ask the operator if the file is to be saved to file. SaveDialog := TSaveDialog.Create(nil); SaveDialog.Title := 'Save ID for Duplicate faults'; SaveDialog.FileName := 'Duplicates'+sBatchID; SaveDialog.Filter := 'Text Files (*.txt)|*.txt|All Files (*.*)|*.*'; SaveDialog.FilterIndex := 1; SaveDialog.DefaultExt := 'txt'; // Use the program directory as the default directory. SaveDialog.InitialDir := ExtractFilePath(ParamStr(0)); SaveDialog.Options := [ofHideReadOnly,ofNoReadOnlyReturn]; lContinue := SaveDialog.Execute; sFile := SaveDialog.FileName; SaveDialog.Free; sTBSdirectory := ExtractFileDir(sFile); end; if lContinue and DirectoryExists(sTBSdirectory) then begin // Determine the parameters for the progress bar. Dec(nDuplicates); frmProgress.Msg.Caption := 'Saving IDs of duplicates...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; nFactor := 100.00/nDuplicates; nCount := 0; // Open the required file. AssignFile( SundryTextFile, sFile ); try Rewrite( SundryTextFile ); // OK, now output the data to the file. for nDuppleID := 0 to (slDuplicates.Count-1) do begin // Output the information to the file. Writeln( SundryTextFile, slDuplicates[nDuppleID] ); Inc(nCount); frmProgress.ProgressBar1.Position := Trunc(nCount * nFactor); frmProgress.ProcessWinMessages(MainForm); end; finally // Done, now close the output file. CloseFile( SundryTextFile ); end; end; //*********** END TEMP ********************************************************* }//{ // Remove these duplicated entries. Dec(nDuplicates); frmProgress.Msg.Caption := 'Removing duplicates ...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; nFactor := 100.00/nDuplicates; nCount := 0; qryDC.Close; qryDC.SQL.Clear; qryDC.SQL.Add('DELETE FROM tDataCollection'); qryDC.SQL.Add('WHERE ID = '); for nDuppleID := 0 to (slDuplicates.Count-1) do begin qryDC.SQL[1] := 'WHERE ID = '+slDuplicates[nDuppleID]; qryDC.ExecSQL; Inc(nCount); frmProgress.ProgressBar1.Position := Trunc(nCount * nFactor); frmProgress.ProcessWinMessages(MainForm); end; //} end; finally qryDC.Close; qryDC.Free; slResults.Free; slDuplicates.Free; end; // try..finally. end; // if (Length(sFields) > 0) then begin. end; // RidDuplicates. procedure GetQuetzalFaults ( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; var lReturnCode: Boolean ); // Shipto changed from MEG to MEF a/c RLD 080620 (Mainco billing change).RJC. // 110425 RJC. sLastName added-no bills for faults when charging for monitoring. var dLogDateTime : TDateTime; sBillYear : string; sBillMonth : string; sFromDate : string; sToDate : string; sBatch : string; sBatchDescription : string; sListValues : String; sListFields : String; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; qryDC : TQuery; qryQuetzal : TQuery; nListValues : integer; sFaultBillID : string; sFaultUnBillID : string; nTotalCount : integer; nBlock : integer; Factor : Real; lGSTFlag : boolean; sLastName : string; nBill : integer; sCust_ID : string; sGSTRate : string; MessageForm : TMessagesForm; begin sBatch := IntToStr(nBatchNumber); sBillYear := IntToStr(Year(dStartDate)); sBillMonth := IntToStr(Month(dStartDate)); sFromDate := ''''+ FormattedDateString(dStartDate) +''','; sToDate := ''''+ FormattedDateString(dEndDate) +''','; sBatchDescription := BatchDescription; Str(MAINFORM.GST_RATE:6:4, sGSTRate); FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryStringList.Capacity := 200; // Establish the query to be used to save the data read. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryQuetzal := TQuery.Create(MainForm); qryQuetzal.DatabaseName := 'dbQuetzal'; qryQuetzal.SQL.Capacity := 200; lReturnCode := True; nFactor := 100.0; nCount := 0; frmProgress := TProgressForm.Create(MainForm); frmProgress.btnCancel.Visible := False; frmProgress.Show; frmProgress.Caption := 'Quetzal Faults Batch: ' + sBatch; frmProgress.Msg.Caption := 'Reading ...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; Factor := 0.1000; // Count to 100 every 1000 records. nTotalCount := 0; nBlock := 0; sFaultBillID := '0'; sFaultUnBillID := '0'; qryDC.SQL.Add('SELECT ID, UPPER(Description) AS UDescription'); qryDC.SQL.Add('FROM tTransactionType'); qryDC.SQL.Add('WHERE Description LIKE ''Reported Fault%'''); qryDC.Open; while not qryDC.Eof do begin if (Pos('FAULT BILL',qryDC['UDescription']) > 0) then sFaultBillID := IntToStr(qryDC['id']) else if (Pos('FAULT NON-BILL',qryDC['UDescription']) > 0) then sFaultUnBillID := IntToStr(qryDC['id']); qryDC.Next; end; qryDC.Close; qryDC.SQL.Clear; qryStringList.Append('SELECT (CASE'); // qryStringList.Append(' WHEN Owner1 + CallSubject1 = ''FRTRADIO BASE EQ'' THEN ''FRT'''); qryStringList.Append(' WHEN CallSubject1 = ''RADIO BASE EQ'' AND (Owner1 IN '+ '(''FRT'', ''OSD'', ''IMD'', ''RBD'', ''RBV'')) THEN Owner1');// RJC 20050723. qryStringList.Append(' WHEN CallSubject1 = ''RADIO BASE EQ'' THEN ''MEF'''); qryStringList.Append(' WHEN ((TrainMaintainer IS NOT NULL) AND '+ '(LTRIM(TrainMaintainer) NOT LIKE '''')) THEN TrainMaintainer');// RJC 20050723. qryStringList.Append(' WHEN Owner1 = ''ZCA'' THEN ''ZMV'''); qryStringList.Append(' WHEN Owner1 = ''ZCC'' THEN ''ZMV'''); qryStringList.Append(' WHEN Owner1 = ''ZCD'' THEN ''ZMV'''); qryStringList.Append(' WHEN Owner1 = ''ZCE'' THEN ''ZMV'''); qryStringList.Append(' WHEN Owner1 = ''ZCF'' THEN ''ZMV'''); qryStringList.Append(' WHEN Owner1 = ''ZCH'' THEN ''ZMV'''); qryStringList.Append(' WHEN Owner1 = ''ZME'' THEN ''ZMD'''); {// 100106 Above replaces below a/c change in Franchise Connex to MTM -RJC. qryStringList.Add(' WHEN Owner1 = ''NXB'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''CGE'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''CME'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''CDF'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''CAA'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''CDM'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''CCM'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''CJD'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''CTT'' THEN ''MEF'''); qryStringList.Add(' WHEN Owner1 = ''MAH'' THEN ''MAF'''); } qryStringList.Append(' ELSE Owner1'); qryStringList.Append(' END) AS "Cust_ID", (CASE'); qryStringList.Append(' WHEN CallSubject1 LIKE ''CENTRACOM'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''SECURITY'' THEN 0'); qryStringList.Append(' WHEN CallSubject2 LIKE ''TELMAX 21'' THEN 0'); qryStringList.Append(' WHEN CallSubject2 LIKE ''TELMAX'' THEN 0'); qryStringList.Append(' WHEN CallSubject2 LIKE ''FLEXCOMMS'' THEN 0'); qryStringList.Append(' WHEN CallSubject2 LIKE ''QUETZAL'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''PHONE T/T'' THEN 0'); // Change 26-06-2005 as requested by RLD - next 2x lines. qryStringList.Append(' WHEN CallSubject1 LIKE ''PABX TELEPHONE'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''PABX VOICEMAIL'' THEN 0'); // Change 21-06-2005 as requested by RLD. qryStringList.Append(' WHEN CallSubject1 LIKE ''PABX EXCHANGE'' THEN 0'); // qryStringList.Add(' WHEN ((CallSubject1 LIKE ''EXCHANGE'') AND '+ // '(CallSubject2 LIKE ''PABX SYSTEM'')) THEN 0'); // end of change 21-06-2005. qryStringList.Append(' WHEN ((CallSubject1 LIKE ''LOCO-LTR FAULT'') AND '+ '(CallSubject2 LIKE ''LOCAL T/RADIO'')) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''RADIO PORTABLE/FIXED'') AND '+ '(CallSubject2 LIKE ''TWO WAY RADIO'')) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''SEPAC%'') AND '+ // '(Owner1 LIKE ''FRT'')) THEN 0'); '(Owner1 IN (''FRT'', ''OSD'', ''IMD'', ''RBD'', ''RBV''))) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''TTB-LOCO FAULT'') AND '+ '(CallSubject2 LIKE ''NUTR TTBR'')) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''FLEX COMM TERM'') AND '+ // '(Owner1 LIKE ''FRT'')) THEN 0'); '(Owner1 IN (''FRT'', ''OSD'', ''IMD'', ''RBD'', ''RBV''))) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''POST PHONE'') AND '+ // '(Owner1 LIKE ''FRT'')) THEN 0'); '(Owner1 IN (''FRT'', ''OSD'', ''IMD'', ''RBD'', ''RBV''))) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''MDC CONSOLE'') AND '+ // '(Owner1 LIKE ''FRT'')) THEN 0'); '(Owner1 IN (''FRT'', ''OSD'', ''IMD'', ''RBD'', ''RBV''))) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''SHUNT RADIO'') AND '+ // '(Owner1 LIKE ''FRT'')) THEN 0'); '(Owner1 IN (''FRT'', ''OSD'', ''IMD'', ''RBD'', ''RBV''))) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''PCM LINK'') AND '+ // '(Owner1 LIKE ''FRT'')) THEN 0'); '(Owner1 IN (''FRT'', ''OSD'', ''IMD'', ''RBD'', ''RBV''))) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''ASW - COMMS'') AND '+ '(Owner1 LIKE ''FRT'')) THEN 0'); qryStringList.Append(' WHEN ((CallSubject1 LIKE ''BASE STN EQUIP'') AND '+ // '((Owner1 LIKE ''FRT'') OR (Owner1 LIKE ''ART''))) THEN 0'); '(Owner1 IN (''FRT'', ''ART'', ''OSD'', ''IMD'', ''RBD'', ''RBV''))) THEN 0'); // qryStringList.Add(' WHEN ((CallSubject1 LIKE ''BASE STN EQUIP'') AND '+ // '(Owner1 LIKE ''ART'')) THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''VOICEMAIL'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''PHONE D-TERM'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''PHONE-IQUARTZ'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''PHONE EXICOM'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''PHONE QMASTER'' THEN 0'); qryStringList.Append(' WHEN CallSubject2 LIKE ''SECURITY'' THEN 0'); qryStringList.Append(' WHEN CallSubject2 LIKE ''ETAS'' THEN 0'); qryStringList.Append(' WHEN CallSubject2 LIKE ''LTR RADIO (End to End)'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''SMR RADIO'' THEN 0'); qryStringList.Append(' WHEN Owner1 LIKE ''VRT'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''%LOCO%'' THEN 0'); qryStringList.Append(' WHEN CallSubject1 LIKE ''RADIO DYNON'' THEN 0'); qryStringList.Append(' ELSE 1'); qryStringList.Append(' END) AS "Bill", qry_all.*'); qryStringList.Append('FROM (SELECT (CASE Assign'); qryStringList.Append(' WHEN ''HILLSIDE'' THEN ''ZCF'''); qryStringList.Append(' WHEN ''BAYSIDE'' THEN ''ZCF'''); {// 100106 Above replaces below a/c change in Franchise Connex to MTM -RJC. qryStringList.Append(' WHEN ''HILLSIDE'' THEN ''CME'''); qryStringList.Append(' WHEN ''BAYSIDE'' THEN ''CME'''); } qryStringList.Append(' WHEN ''FREIGHT VICTORIA'' THEN ''FRT'''); qryStringList.Append(' WHEN ''FREIGHT AUSTRALIA'' THEN ''FRT'''); qryStringList.Append(' WHEN ''V/LINE PASS'' THEN ''VLP'''); qryStringList.Append(' WHEN ''ARTC'' THEN ''ART'''); qryStringList.Append(' ELSE Assign'); qryStringList.Append(' END) AS "Owner1", QuetzalFaults.*'); qryStringList.Append('FROM (SELECT (CASE'); qryStringList.Append(' WHEN LEN(RTRIM(tTrain.TrainOwner)) > 0 '+ 'THEN tTrain.TrainOwner'); qryStringList.Append(' WHEN LEN(RTRIM(tLocation.LocOwner)) > 0 '+ 'THEN tLocation.LocOwner'); qryStringList.Append(' ELSE tName.Owner'); // qryStringList.Add(' END, tName.*'); // RJC 20050723. qryStringList.Append(' END) AS "Assign", tName.*, tTrain.TrainMaintainer'); qryStringList.Append('FROM ((SELECT Name2.*, Client.Ref3 AS Owner'); qryStringList.Append('FROM (SELECT Call.ClientCode, Call.CallNumber, '+ 'Call.LastName, Call.Logon, Call.CallSubject3,'); qryStringList.Append(' Call.SupportGroup, Call.LogDatTim, '+ 'Name1.LogDate, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2'); qryStringList.Append('FROM (SELECT CONVERT(Char(10),Call.LogDatTim,103) '+ 'AS LogDate, Call.ConfigId, Call.CallSubject1, Call.CallSubject2'); qryStringList.Append('FROM Call'); qryStringList.Append('WHERE Year(Call.LogDatTim) = '+sBillYear+ ' AND Month(Call.LogDatTim) = '+sBillMonth); qryStringList.Append('AND Call.ConfigID IS NOT NULL'); qryStringList.Append('AND (Call.CallSubject1 NOT LIKE ''W-%'' '+ 'AND Call.CallSubject1 NOT LIKE ''R-%'' '+ 'AND Call.CallSubject1 NOT LIKE ''C-%'' '+ 'AND Call.CallSubject1 NOT LIKE ''LASP%'')'); // RJC 20090802-'Lift' phones. qryStringList.Append('GROUP BY CONVERT(Char(10),Call.LogDatTim,103), '+ 'Call.ConfigID, Call.CallSubject1, Call.CallSubject2) AS Name1'); qryStringList.Append('LEFT JOIN Call ON ((Name1.LogDate = '+ 'CONVERT(Char(10),Call.LogDatTim,103))'); qryStringList.Append('AND (Name1.ConfigID = Call.ConfigID)'); qryStringList.Append('AND (Name1.CallSubject1 = Call.CallSubject1)'); qryStringList.Append('AND (Name1.CallSubject2 = Call.CallSubject2))'); qryStringList.Append('WHERE Year(Call.LogDatTim) = '+sBillYear+ ' AND Month(Call.LogDatTim) = '+sBillMonth+') AS Name2'); qryStringList.Append('INNER JOIN Client ON Name2.ClientCode = Client.ClientCode)'+ ' AS tName'); qryStringList.Append('LEFT JOIN (SELECT Logon.Ref3 AS LocOwner, Call.CallNumber'); qryStringList.Append('FROM (Call LEFT JOIN Logon '+ 'ON Call.LogonCode = Logon.LogonCode)'); qryStringList.Append('LEFT JOIN Configuration '+ 'ON Call.ConfigCode = Configuration.ConfigCode'); qryStringList.Append('WHERE Year(Call.LogDatTim) = '+sBillYear+ ' AND Month(Call.LogDatTim) = '+sBillMonth); qryStringList.Append('AND (Configuration.ConfigClassificationUnique <> 1)) '+ 'AS tLocation ON tName.CallNumber = tLocation.CallNumber)'); // qryStringList.Add('LEFT JOIN (SELECT Configuration.Ref2 AS TrainOwner, '+ // 'Call.CallNumber, Configuration.Ref1 AS TrainMaintainer'); qryStringList.Append('LEFT JOIN (SELECT Configuration.Ref1 AS TrainOwner, '+ 'Call.CallNumber, Configuration.Ref2 AS TrainMaintainer'); // RJC 20050723. qryStringList.Append('FROM (Call LEFT JOIN Configuration '+ 'ON Call.ConfigCode = Configuration.ConfigCode)'); qryStringList.Append('WHERE Year(Call.LogDatTim) = '+sBillYear+ ' AND Month(Call.LogDatTim) = '+sBillMonth); qryStringList.Append('AND (Configuration.Ref1 NOT LIKE ''Z%'' AND '+ 'Configuration.Ref1 NOT LIKE ''E:%'')'); qryStringList.Append('AND ((Configuration.ConfigClassificationUnique = 1) OR '+ '(Configuration.ConfigClassificationUnique = 4))) AS tTrain'); qryStringList.Append('ON tName.CallNumber = tTrain.CallNumber) AS QuetzalFaults)'+ ' AS qry_all'); qryStringList.Append('ORDER BY LogDate'); qryQuetzal.SQL.AddStrings(qryStringList); qryQuetzal.Open; qryStringList.Clear; //**************************************************************** //**************************************************************** //********* START READING BILLS INTO 'tDataCollection' *********** //**************************************************************** if qryQuetzal.Eof then begin MessageDlg('No entries found.', mtWarning, [mbOk], 0); lReturnCode := False; end else begin // NOTE: Charge amounts added below AFTER ALL entries added to tDataCollection. while not qryQuetzal.Eof do begin frmProgress.ProcessWinMessages(MainForm); FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + '''' + qryQuetzal.FieldByName('CallNumber').AsString +''','; sListFields := sListFields + 'ServiceID,'; sCust_ID := UpperCase(qryQuetzal.FieldByName('Cust_ID').AsString); sListValues := sListValues +''''+ sBatchDescription +' '+ sCust_ID +''','; sListFields := sListFields + 'ServiceType,'; if (sCust_ID = 'VRT') then begin sListValues := sListValues +'''QFV'','; end else begin sListValues := sListValues +'''QFR'','; end; sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ qryQuetzal.FieldByName('LogDate').AsString +''','; dLogDateTime := qryQuetzal.FieldByName('LogDatTim').AsDateTime; sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ TimeToStr(dLogDateTime) +''','; nBill := qryQuetzal.FieldByName('Bill').AsInteger; sListFields := sListFields + 'TransactionTypeID,'; if (nBill = 1) then sListValues := sListValues + sFaultBillID+',' else sListValues := sListValues + sFaultUnBillID+','; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; lGSTFlag := (sCust_ID <> 'VRT'); sListFields := sListFields + 'GSTFlag,'; if (lGSTFlag) then begin sListValues := sListValues + '''Y'','; end else begin sListValues := sListValues + '''N'','; end; // if GSTFlag='Y' then begin. sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; if not VarIsNull(qryQuetzal['CallNumber']) then begin sListFields := sListFields + 'Var01,'; // CallNumber. sListValues := sListValues +''''+ qryQuetzal.FieldByName('CallNumber').AsString +''','; end; sListFields := sListFields + 'Var02,'; // LogDatTim. sListValues := sListValues +''''+StandardDateTimeString(dLogDateTime)+''','; if not VarIsNull(qryQuetzal['CallSubject2']) then begin sListFields := sListFields + 'Var04,'; // CallSubject2. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('CallSubject2').AsString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if not VarIsNull(qryQuetzal['ConfigId']) then begin sListFields := sListFields + 'Var05,'; // ConfigId. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('ConfigId').AsString) +''','; end; if not VarIsNull(qryQuetzal['Owner1']) then begin sListFields := sListFields + 'Var06,'; // Owner1. sListValues := sListValues +''''+ qryQuetzal.FieldByName('Owner1').AsString +''','; end; if not VarIsNull(qryQuetzal['Cust_ID']) then begin sListFields := sListFields + 'Var07,'; // Cust_ID. sListValues := sListValues +''''+ qryQuetzal.FieldByName('Cust_ID').AsString +''','; end; if not VarIsNull(qryQuetzal['CallSubject1']) then begin sListFields := sListFields + 'Var08,'; // CallSubject1. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('CallSubject1').AsString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if not VarIsNull(qryQuetzal['CallSubject3']) then begin sListFields := sListFields + 'Var09,'; // CallSubject3. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('CallSubject3').AsString) +''','; end; if not VarIsNull(qryQuetzal['Logon']) then begin sListFields := sListFields + 'Var10,'; // Logon (Location). sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Logon').AsString) +''','; end; if not VarIsNull(qryQuetzal['LastName']) then begin sLastName := CleanString(qryQuetzal.FieldByName('LastName').AsString); sListFields := sListFields + 'Var14,'; // LastName. sListValues := sListValues +''''+ sLastName +''','; if UpperCase(sLastName) = 'HP' then nBill := 0; end; if not VarIsNull(qryQuetzal['Bill']) then begin sListFields := sListFields + 'Var15,'; // Bill. sListValues := sListValues + IntToStr(nBill) +','; end; sListFields := sListFields + 'BatchID)'; // BatchID. sListValues := sListValues + sBatch +')'; // Now make the SQL statement to save to the database table. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin // and not AbortProcess frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); nBlock := 0; if ((nCount * Factor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * Factor)>100.00) then. frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); frmProgress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. frmProgress.ProcessWinMessages(MainForm); qryQuetzal.Next; end; // while not qryQuetzal.Eof do begin if lReturnCode then begin // Now remove any duplicated entries - list the fields defining duplicates. FieldStringList.Clear; FieldStringList.Add('TxnDate'); FieldStringList.Add('Var08'); // CallSubject1. FieldStringList.Add('Var04'); // CallSubject2. FieldStringList.Add('Var05'); // ConfigID. RidDuplicates( sBatch, FieldStringList ); // Now update the charged rates. frmProgress.Hide; MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing Quetzal Faults ...'; MessageForm.Msg.Caption := 'Updating with applied rates ...'; MessageForm.Show; frmProgress.ProcessWinMessages(MainForm); qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET AmountExGST = ChargeRate.AmtExGST,'); qryStringList.Add(' GSTAmount = ChargeRate.GSTAmt,'); qryStringList.Add(' AmountIncGST = ChargeRate.AmtExGST + ChargeRate.GSTAmt'); qryStringList.Add('FROM (SELECT GSTrate.ID, GSTrate.AmtExGST,'); qryStringList.Add('(GSTrate.AmtExGST * GSTrate.GSTFactor) AS GSTAmt'); qryStringList.Add('FROM (SELECT tDataCollection.ID, '+ '(CASE WHEN tDataCollection.GSTFlag LIKE ''Y'' '+ 'THEN '+sGSTRate+' ELSE 0 END) AS GSTFactor, tRate.Cost AS AmtExGST'); qryStringList.Add('FROM (tDataCollection'); // qryStringList.Add('LEFT JOIN tServiceID '+ // 'ON tDataCollection.ServiceID = tServiceID.ServiceID)'); // qryStringList.Add('LEFT JOIN tRate '+ // 'ON tServiceID.ServiceTypeID = tRate.ServiceTypeID'); qryStringList.Add('LEFT JOIN tServiceType '+ 'ON tDataCollection.ServiceType = tServiceType.Code)'); qryStringList.Add('LEFT JOIN tRate '+ 'ON tServiceType.ID = tRate.ServiceTypeID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch); qryStringList.Add('AND tDataCollection.Var15 = ''1'''); // Bill=1. qryStringList.Add('AND tRate.Cost IS NOT NULL'+ ') AS GSTrate) AS ChargeRate'); qryStringList.Add('WHERE tDataCollection.ID = ChargeRate.ID'); qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; // Now update the values stored in the tBatch table. qryStringList.Clear; qryStringList.Add('UPDATE tBatch'); qryStringList.Add('SET GSTAmount = BatchRate.TotalGST,'); qryStringList.Add(' TotalPayable = BatchRate.TotalIncGST'); qryStringList.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); qryStringList.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch + ') AS BatchRate'); qryStringList.Add('WHERE tBatch.ID = '+ sBatch); qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); MessageForm.Close; MessageForm.Free; end; // if lReturnCode then begin. end; // Quetzal faults found. // Done with the input file; close it. FieldStringList.Free; ValueStringList.Free; frmProgress.Close; frmProgress.Free; qryStringList.Free; qryDC.Close; qryDC.Free; qryQuetzal.Close; qryQuetzal.Free; end; // procedure GetQuetzalFaults. procedure GetRadioFaults ( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; var lReturnCode: Boolean ); // 170120 RJC- Quetzal SQL simplified by RLD a/c NMC does ALL comms faults. var dLogDateTime : TDateTime; sFromDate : string; sToDate : string; sBatch : string; sBatchDescription : string; sListValues : String; sListFields : String; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; qryDC : TQuery; qryQuetzal : TQuery; nListValues : integer; TempDesc : string; sRadioAdminID : string; sRadioQuoteID : string; sRadioMinCalloutID : string; sRadioCalloutID : string; sRadioAdviceID : string; sRadioAerialID : string; sRadioStandardID : string; sRadioVRTNoChargeID : string; sRadioNoChargeID : string; curExGST : currency; curGST : currency; curIncGST : currency; nTotalCount : integer; nBlock : integer; Factor : Real; lGSTFlag : boolean; MessageForm : TMessagesForm; begin sBatch := IntToStr(nBatchNumber); sFromDate := ''''+ FormattedDateString(dStartDate) +''','; sToDate := ''''+ FormattedDateString(dEndDate) +''','; sBatchDescription := BatchDescription; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryStringList.Capacity := 200; // Establish the query to be used to save the data read. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryQuetzal := TQuery.Create(MainForm); qryQuetzal.DatabaseName := 'dbQuetzal'; lReturnCode := True; nFactor := 100.0; nCount := 0; frmProgress := TProgressForm.Create(MainForm); frmProgress.btnCancel.Visible := False; frmProgress.Show; frmProgress.Caption := 'Radio Faults Batch: ' + sBatch; frmProgress.Msg.Caption := 'Reading ...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; Factor := 0.1000; // Count to 100 every 1000 records. nTotalCount := 0; nBlock := 0; sRadioAdminID := '0'; sRadioQuoteID := '0'; sRadioMinCalloutID := '0'; sRadioCalloutID := '0'; sRadioAdviceID := '0'; sRadioAerialID := '0'; sRadioStandardID := '0'; sRadioVRTNoChargeID := '0'; sRadioNoChargeID := '0'; qryDC.SQL.Add('SELECT ID, UPPER(Description) AS UDescription'); qryDC.SQL.Add('FROM tTransactionType'); qryDC.SQL.Add('WHERE Description LIKE ''%Radio %'''); qryDC.Open; while not qryDC.Eof do begin TempDesc := qryDC.FieldByName('UDescription').AsString; if (Pos('ADMIN',TempDesc) > 0) then sRadioAdminID := IntToStr(qryDC['id']) else if (Pos('QUOTE',TempDesc) > 0) then sRadioQuoteID := IntToStr(qryDC['id']) else if (Pos('MINIMUM',TempDesc) > 0) then sRadioMinCalloutID := IntToStr(qryDC['id']) else if (Pos('CALL OUT',TempDesc) > 0) then sRadioCalloutID := IntToStr(qryDC['id']) else if (Pos('ADVICE',TempDesc) > 0) then sRadioAdviceID := IntToStr(qryDC['id']) else if (Pos('AERIAL',TempDesc) > 0) then sRadioAerialID := IntToStr(qryDC['id']) else if (Pos('CORE HOUR',TempDesc) > 0) then sRadioStandardID := IntToStr(qryDC['id']) else if (Pos('VRT NO CHARGE',TempDesc) > 0) then sRadioVRTNoChargeID := IntToStr(qryDC['id']) else if (Pos('NO CHARGE',TempDesc) > 0) then sRadioNoChargeID := IntToStr(qryDC['id']); qryDC.Next; end; qryDC.Close; qryDC.SQL.Clear; with qryStringList do begin Add('SELECT'); Add('ISNULL(NULLIF(Configuration.Ref1,''''), Call.OrgLvlCode) AS Owner,'); Add('CASE'); Add(' WHEN LEN(Configuration.Ref2) > 3'); Add(' THEN ISNULL(NULLIF(Configuration.Ref1, ''''), Call.OrgLvlCode)'); Add(' ELSE ISNULL(NULLIF(Configuration.Ref2,''''), '); Add(' ISNULL(NULLIF(Configuration.Ref1, ''''), Call.OrgLvlCode))'); Add('END AS Maintainer,'); Add('CASE'); Add(' WHEN Configuration.ConfigClassificationUnique = 6 THEN 0'); Add(' WHEN Call.CallSubject2 LIKE ''Base Sta%'' THEN 0'); Add(' ELSE 1'); Add('END AS Bill, Call.CallNumber, Call.CustomerRef AS Ref2,'); Add('Call.LogDatTim AS LogDate,'); Add('CASE'); Add(' WHEN Call.FixStatus = 1 THEN Call.Slt1WarningRespondDateTime'); Add(' ELSE NULL'); Add('END AS FixedDate,'); Add('Call.LastName,'); Add('Call.ConfigId AS Configuration,'); Add('Call.Logon AS Location,'); Add('Call.CallSubject1 AS Equipment,'); Add('Call.CallSubject2 AS System,'); Add('Call.CallSubject3 AS Fault,'); Add('Call.JobTracking AS Priority,'); Add('CASE'); // Abort all Quotes - now use the QuoteSystem for payment. Add(' WHEN Call.Slt1 LIKE ''QUOTE'''); Add(' THEN 0'); // Following is the original. Add(' WHEN (Call.Slt1 LIKE ''NO CHARGE'' OR Call.Slt1 LIKE ''QUOTE'')'); Add(' AND isnumeric(Call.Ref3) = 0'); Add(' THEN 0'); Add(' WHEN Call.Ref3 IS NULL'); Add(' AND (Call.Slt1 LIKE ''NO CHARGE'' OR Call.Slt1 LIKE ''QUOTE'')'); Add(' THEN 0'); Add(' WHEN Call.Slt1 LIKE ''NO CHARGE'' OR Call.Slt1 LIKE ''QUOTE'''); Add(' THEN CAST(Call.Ref3 AS REAL)'); Add(' WHEN CAST(CAST(slt.mincharge AS VARCHAR(32)) AS REAL) IS NULL'); Add(' THEN 0'); Add(' ELSE CAST(CAST(slt.mincharge AS VARCHAR(32)) AS REAL)'); Add('END AS Fee'); Add('FROM Call'); // Update 20170212 with the next line from RLD: Add('INNER JOIN Slt on Call.JobTracking = Slt.SltName'); Add('LEFT OUTER JOIN Location '+ 'ON Location.LocationUnique = Call.LocationUnique'); Add('LEFT OUTER JOIN Configuration '+ 'ON Configuration.ConfigId = Call.ConfigId'); // Update 20170212 with removal of the next line from RLD: // Add('INNER JOIN Slt on Slt.SltUnique = Call.SltUnique1'); Add('WHERE Year(Call.Slt1WarningRespondDateTime) = '+IntToStr(Year(dEndDate))); Add('AND Month(Call.Slt1WarningRespondDateTime) = '+IntToStr(Month(dEndDate))); Add('AND (Call.Fixstatus = 1) AND (Call.CallSubject1 LIKE ''RADIO DYNON%'')'); Add('AND (Call.CallSubject2 LIKE ''LOCO%'')'); Add('ORDER BY Call.CallNumber'); end; // qryStringList. qryQuetzal.SQL.Text := qryStringList.Text; qryQuetzal.Open; qryStringList.Clear; //**************************************************************** //**************************************************************** //********* START READING BILLS INTO 'tDataCollection' *********** //**************************************************************** if qryQuetzal.Eof then begin MessageDlg('No entries found.', mtWarning, [mbOk], 0); lReturnCode := False; end else begin // NOTE: Charge amounts added below AFTER ALL entries added to tDataCollection. while not qryQuetzal.Eof do begin frmProgress.ProcessWinMessages(MainForm); FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + '''' + qryQuetzal.FieldByName('CallNumber').AsString +''','; sListFields := sListFields + 'ServiceID,'; if VarIsNull(qryQuetzal['Maintainer']) or (Length(Trim(qryQuetzal.FieldByName('Maintainer').AsString)) = 0) then sListValues := sListValues +''''+ sBatchDescription +' '+ qryQuetzal.FieldByName('Owner').AsString +' '+ qryQuetzal.FieldByName('Priority').AsString +''',' else sListValues := sListValues +''''+ sBatchDescription +' '+ qryQuetzal.FieldByName('Maintainer').AsString +' '+ qryQuetzal.FieldByName('Priority').AsString +''','; dLogDateTime := qryQuetzal.FieldByName('LogDate').AsDateTime; sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ FormattedDateString(dLogDateTime) +''','; sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ TimeToStr(dLogDateTime) +''','; sListFields := sListFields + 'TransactionTypeID,'; TempDesc := UpperCase(Trim(qryQuetzal.FieldByName('Priority').AsString)); if (Pos('ADMIN',TempDesc) > 0) then sListValues := sListValues + sRadioAdminID +',' else if (Pos('QUOTE',TempDesc) > 0) then sListValues := sListValues + sRadioQuoteID +',' else if (TempDesc = 'MINIMUM CALLOUT') then sListValues := sListValues + sRadioMinCalloutID +',' else if (Pos('CALLOUT',TempDesc) > 0) then sListValues := sListValues + sRadioCalloutID +',' else if (Pos('ADVICE',TempDesc) > 0) then sListValues := sListValues + sRadioAdviceID +',' else if (TempDesc = 'AERIAL CHANGE') then sListValues := sListValues + sRadioAerialID +',' else if (Pos('STANDARD',TempDesc) > 0) then sListValues := sListValues + sRadioStandardID +',' else if (TempDesc = 'VRT NO CHARGE') then sListValues := sListValues + sRadioVRTNoChargeID +',' else if (TempDesc = 'NO CHARGE') then sListValues := sListValues + sRadioNoChargeID +',' else sListValues := sListValues + '0,'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; lGSTFlag := (UpperCase(qryQuetzal.FieldByName('Maintainer').AsString) <> 'VRT'); sListFields := sListFields + 'GSTFlag,'; if (lGSTFlag) then sListValues := sListValues + '''Y'',' else sListValues := sListValues + '''N'','; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate ; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; if not VarIsNull(qryQuetzal['CallNumber']) then begin sListFields := sListFields + 'Var01,'; // CallNumber. sListValues := sListValues +''''+ qryQuetzal.FieldByName('CallNumber').AsString +''','; end; if not VarIsNull(qryQuetzal['Ref2']) and (Length(Trim(qryQuetzal.FieldByName('Ref2').AsString)) > 0) then begin sListFields := sListFields + 'Var02,'; // Ref2=Purchase Order. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Ref2').AsString) +''','; end; sListFields := sListFields + 'Var03,'; // LogDate + FixedDate. sListValues := sListValues +''''+ FormattedDateString(dLogDateTime)+ ' ' + TimeToStr( dLogDateTime ); dLogDateTime := qryQuetzal.FieldByName('FixedDate').AsDateTime; sListValues := sListValues +' '+ FormattedDateString(dLogDateTime)+ ' ' + TimeToStr( dLogDateTime ) +''','; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if not VarIsNull(qryQuetzal['System']) then begin sListFields := sListFields + 'Var04,'; // System. sListValues := sListValues +''''+ qryQuetzal.FieldByName('System').AsString +''','; end; if not VarIsNull(qryQuetzal['Configuration']) then begin sListFields := sListFields + 'Var05,'; // Configuration. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Configuration').AsString) +''','; end; if not VarIsNull(qryQuetzal['Owner']) then begin sListFields := sListFields + 'Var06,'; // Owner. sListValues := sListValues +''''+ qryQuetzal.FieldByName('Owner').AsString +''','; end; if not VarIsNull(qryQuetzal['Maintainer']) then begin sListFields := sListFields + 'Var07,'; // Maintainer. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Maintainer').AsString) +''','; end; if not VarIsNull(qryQuetzal['Equipment']) then begin sListFields := sListFields + 'Var08,'; // Equipment. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Equipment').AsString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if not VarIsNull(qryQuetzal['Fault']) then begin sListFields := sListFields + 'Var09,'; // Fault. sListValues := sListValues + '''' + qryQuetzal.FieldByName('Fault').AsString +''','; end; if not VarIsNull(qryQuetzal['Location']) then begin sListFields := sListFields + 'Var10,'; // Location. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Location').AsString) +''','; end; if not VarIsNull(qryQuetzal['Priority']) then begin sListFields := sListFields + 'Var11,'; // Priority sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Priority').AsString) +''','; end; { // By default this is Dynon Loco so it is redundant and omitted. if not VarIsNull(qryQuetzal['ServiceCentre']) then begin sListFields := sListFields + 'Var12,'; // ServiceCentre. sListValues := sListValues + '''' + qryQuetzal.FieldByName('ServiceCentre').AsString +''','; end; } FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if not VarIsNull(qryQuetzal['Fee']) then begin // Fee curExGST := curFormat(StrToCurr(qryQuetzal.FieldByName('Fee').AsString)); sListFields := sListFields + 'Var13,AmountExGST,'; sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Fee').AsString) +''','+ Curr2Str(curExGST) +','; if lGSTFlag then begin curGST := 0.1 * curExGST; curIncGST := curSum(curExGST,curGST); sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Curr2Str(curGST) +','; end else curIncGST := curExGST; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + Curr2Str(curIncGST) +','; end; if not VarIsNull(qryQuetzal['LastName']) then begin sListFields := sListFields + 'Var14,'; // LastName. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('LastName').AsString) +''','; end; sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; // Now make the SQL statement to save to the database table. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin // and not AbortProcess frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); nBlock := 0; if ((nCount * Factor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * Factor)>100.00) then. frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); frmProgress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. frmProgress.ProcessWinMessages(MainForm); qryQuetzal.Next; end; // while not qryQuetzal.Eof do begin if lReturnCode then begin frmProgress.Hide; MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing Radio Faults ...'; MessageForm.Msg.Caption := 'Updating with applied rates ...'; MessageForm.Show; frmProgress.ProcessWinMessages(MainForm); // Now update the values stored in the tBatch table. qryStringList.Clear; qryStringList.Add('UPDATE tBatch'); qryStringList.Add('SET GSTAmount = BatchRate.TotalGST,'); qryStringList.Add(' TotalPayable = BatchRate.TotalIncGST'); qryStringList.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); qryStringList.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch+ ') AS BatchRate'); qryStringList.Add('WHERE tBatch.ID = '+ sBatch); qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); MessageForm.Close; MessageForm.Free; end; // if lReturnCode then begin. end; // Quetzal faults found. // Done with the input file; close it. FieldStringList.Free; ValueStringList.Free; frmProgress.Close; frmProgress.Free; qryStringList.Free; qryDC.Close; qryDC.Free; qryQuetzal.Close; qryQuetzal.Free; end; // procedure GetRadioFaults. procedure GetTaxiFaults ( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; var lReturnCode: Boolean ); var dLogDateTime : TDateTime; sBillYear : string; sBillMonth : string; sFromDate : string; sToDate : string; sBatch : string; sBatchDescription : string; sListValues : String; sListFields : String; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; qryDC : TQuery; qryQuetzal : TQuery; nListValues : integer; sTaxiBillID : string; nTotalCount : integer; nBlock : integer; Factor : Real; lGSTFlag : boolean; nBill : integer; sGSTRate : string; MessageForm : TMessagesForm; begin sBatch := IntToStr(nBatchNumber); sBillYear := IntToStr(Year(dStartDate)); sBillMonth := IntToStr(Month(dStartDate)); sFromDate := ''''+ FormattedDateString(dStartDate) +''','; sToDate := ''''+ FormattedDateString(dEndDate) +''','; sBatchDescription := BatchDescription; Str(MAINFORM.GST_RATE:6:4, sGSTRate); FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryStringList.Capacity := 200; // Establish the query to be used to save the data read. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryQuetzal := TQuery.Create(MainForm); qryQuetzal.DatabaseName := 'dbQuetzal'; lReturnCode := True; nFactor := 100.0; nCount := 0; frmProgress := TProgressForm.Create(MainForm); frmProgress.btnCancel.Visible := False; frmProgress.Show; frmProgress.Caption := 'Taxi Directorate Batch: ' + sBatch; frmProgress.Msg.Caption := 'Reading ...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; Factor := 0.1000; // Count to 100 every 1000 records. nTotalCount := 0; nBlock := 0; sTaxiBillID := '0'; qryDC.SQL.Add('SELECT ID, UPPER(Description) AS UDescription'); qryDC.SQL.Add('FROM tTransactionType'); qryDC.SQL.Add('WHERE Description LIKE ''Taxi Directorate%'''); qryDC.Open; while not qryDC.Eof do begin if (Pos('COMPLAINT',qryDC['UDescription']) > 0) then begin sTaxiBillID := IntToStr(qryDC['id']); break; end; qryDC.Next; end; qryDC.Close; qryDC.SQL.Clear; qryStringList.Add('SELECT ''DOH'' AS Owner1, ''DOH'' AS Cust_ID, 1 AS Bill, '+ 'Call.CallNumber, Call.LogDatTim, Call.LastName,'); qryStringList.Add(' Call.ConfigId, Call.Logon, Call.ITHelpUser,'+ 'Call.CallSubject1, Call.CallSubject2, Call.CallSubject3'); qryStringList.Add('FROM Call'); qryStringList.Add('WHERE Year(Call.LogDatTim) = '+sBillYear); qryStringList.Add('AND Month(Call.LogDatTim) = '+sBillMonth); qryStringList.Add('AND Call.CallSubject1 LIKE ''TAXI DIRECTORATE%'''); qryStringList.Add('ORDER BY Call.LogDatTim'); qryQuetzal.SQL := qryStringList; qryQuetzal.Open; qryStringList.Clear; //**************************************************************** //**************************************************************** //******* START READING Taxi BILLS INTO 'tDataCollection' ******** //**************************************************************** if qryQuetzal.Eof then begin MessageDlg('No entries found.', mtWarning, [mbOk], 0); lReturnCode := False; end else begin // NOTE: Charge amounts added below AFTER ALL entries added to tDataCollection. while not qryQuetzal.Eof do begin frmProgress.ProcessWinMessages(MainForm); FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + '''' + qryQuetzal.FieldByName('CallNumber').AsString +''','; sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ sBatchDescription +' '+ qryQuetzal.FieldByName('Cust_ID').AsString +''','; nBill := qryQuetzal.FieldByName('Bill').AsInteger; sListFields := sListFields + 'ServiceType,'; sListValues := sListValues +'''TDC'','; dLogDateTime := qryQuetzal.FieldByName('LogDatTim').AsDateTime; sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ FormattedDateString(dLogDateTime) +''','; sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ TimeToStr(dLogDateTime) +''','; sListFields := sListFields + 'TransactionTypeID,'; sListValues := sListValues + sTaxiBillID +','; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; lGSTFlag := (UpperCase(qryQuetzal.FieldByName('Cust_ID').AsString) <> 'VRT'); sListFields := sListFields + 'GSTFlag,'; if (lGSTFlag) then sListValues := sListValues + '''Y'',' else sListValues := sListValues + '''N'','; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; if not VarIsNull(qryQuetzal['CallNumber']) then begin sListFields := sListFields + 'Var01,'; // CallNumber. sListValues := sListValues +''''+ qryQuetzal.FieldByName('CallNumber').AsString +''','; end; sListFields := sListFields + 'Var02,'; // LogDatTim. sListValues := sListValues +''''+ FormattedDateString(dLogDateTime)+ ' ' + TimeToStr( dLogDateTime ) +''','; if not VarIsNull(qryQuetzal['CallSubject2']) then begin sListFields := sListFields + 'Var04,'; // CallSubject2. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('CallSubject2').AsString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if not VarIsNull(qryQuetzal['ConfigId']) then begin sListFields := sListFields + 'Var05,'; // ConfigId. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('ConfigId').AsString) +''','; end; if not VarIsNull(qryQuetzal['Owner1']) then begin sListFields := sListFields + 'Var06,'; // Owner1. sListValues := sListValues +''''+ qryQuetzal.FieldByName('Owner1').AsString +''','; end; if not VarIsNull(qryQuetzal['Cust_ID']) then begin sListFields := sListFields + 'Var07,'; // Cust_ID. sListValues := sListValues +''''+ qryQuetzal.FieldByName('Cust_ID').AsString +''','; end; if not VarIsNull(qryQuetzal['CallSubject1']) then begin sListFields := sListFields + 'Var08,'; // CallSubject1. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('CallSubject1').AsString) +''','; end; if not VarIsNull(qryQuetzal['CallSubject3']) then begin sListFields := sListFields + 'Var09,'; // CallSubject3 sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('CallSubject3').AsString) +''','; end; if not VarIsNull(qryQuetzal['Logon']) then begin sListFields := sListFields + 'Var10,'; // Logon (Location). sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('Logon').AsString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if not VarIsNull(qryQuetzal['ITHelpUser']) then begin sListFields := sListFields + 'Var12,'; // CallSubject3 sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('ITHelpUser').AsString) +''','; end; if not VarIsNull(qryQuetzal['LastName']) then begin sListFields := sListFields + 'Var14,'; // LastName. sListValues := sListValues +''''+ CleanString(qryQuetzal.FieldByName('LastName').AsString) +''','; end; if not VarIsNull(qryQuetzal['Bill']) then begin sListFields := sListFields + 'Var15,'; // Bill. sListValues := sListValues + IntToStr(nBill) +','; end; sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; // Now make the SQL statement to save to the database table. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin // and not AbortProcess frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); nBlock := 0; if ((nCount * Factor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * Factor)>100.00) then. frmProgress.ProgressBar1.Position := Trunc(nCount * Factor); frmProgress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. frmProgress.ProcessWinMessages(MainForm); qryQuetzal.Next; end; // while not qryQuetzal.Eof do begin if lReturnCode then begin frmProgress.Hide; MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing Taxi Directorate ...'; MessageForm.Msg.Caption := 'Updating with applied rates ...'; MessageForm.Show; frmProgress.ProcessWinMessages(MainForm); qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET AmountExGST = ChargeRate.AmtExGST,'); qryStringList.Add(' GSTAmount = ChargeRate.GSTAmt,'); qryStringList.Add(' AmountIncGST = ChargeRate.AmtExGST + ChargeRate.GSTAmt'); qryStringList.Add('FROM (SELECT GSTrate.ID, GSTrate.AmtExGST,'); qryStringList.Add('(GSTrate.AmtExGST * GSTrate.GSTFactor) AS GSTAmt'); qryStringList.Add('FROM (SELECT tDataCollection.ID, '+ '(CASE WHEN tDataCollection.GSTFlag LIKE ''Y'' '+ 'THEN '+sGSTRate+' ELSE 0 END) AS GSTFactor, tRate.Cost AS AmtExGST'); qryStringList.Add('FROM (tDataCollection'); qryStringList.Add('LEFT JOIN tServiceID '+ 'ON tDataCollection.ServiceID = tServiceID.ServiceID)'); qryStringList.Add('LEFT JOIN tRate '+ 'ON tServiceID.ServiceTypeID = tRate.ServiceTypeID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch); qryStringList.Add('AND tRate.Cost IS NOT NULL'+ ') AS GSTrate) AS ChargeRate'); qryStringList.Add('WHERE tDataCollection.ID = ChargeRate.ID'); qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; // Now update the values stored in the tBatch table. qryStringList.Clear; qryStringList.Add('UPDATE tBatch'); qryStringList.Add('SET GSTAmount = BatchRate.TotalGST,'); qryStringList.Add(' TotalPayable = BatchRate.TotalIncGST'); qryStringList.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); qryStringList.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch+ ') AS BatchRate'); qryStringList.Add('WHERE tBatch.ID = '+ sBatch); qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); MessageForm.Close; MessageForm.Free; end; // if lReturnCode then begin. end; // Quetzal faults found. // Done with the input file; close it. FieldStringList.Free; ValueStringList.Free; frmProgress.Close; frmProgress.Free; qryStringList.Free; qryDC.Close; qryDC.Free; qryQuetzal.Close; qryQuetzal.Free; end; // procedure GetTaxiFaults. procedure QuetzalInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string); var lContinue : Boolean; EmptyList : TStringList; begin EmptyList := TStringList.Create; // Removes all entries for the batch. lContinue := True; DeleteFromDC( nBatch, EmptyList, lContinue); EmptyList.Free; GetQuetzalFaults( nBatch, dStartDate, dEndDate, BatchDescription, lContinue); end; // QuetzalInvoice. procedure RadioInvoice( nBatch: LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string); var lContinue : Boolean; EmptyList : TStringList; begin EmptyList := TStringList.Create; // Removes all entries for the batch. lContinue := True; DeleteFromDC( nBatch, EmptyList, lContinue); EmptyList.Free; GetRadioFaults( nBatch, dStartDate, dEndDate, BatchDescription, lContinue); end; // RadioInvoice. procedure TaxiInvoice( nBatch: LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string); var lContinue : Boolean; EmptyList : TStringList; begin EmptyList := TStringList.Create; // Removes all entries for the batch. lContinue := True; DeleteFromDC( nBatch, EmptyList, lContinue); EmptyList.Free; GetTaxiFaults( nBatch, dStartDate, dEndDate, BatchDescription, lContinue); end; // TaxiInvoice. end. // Quetzal.