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.