unit QuoteSystem;

// 080629: New field ITHELPUSER saved to Var09 for Jobs and Purchases.
// 121008: TransactionType name check generalised from (6,50) to (nPos,50).
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 QuoteSystemPurchasesInvoice(nBatch: LongInt;
    const dStartDate, dEndDate: TDateTime; const BatchDescription : string;
    const lAnyTime : boolean);
  procedure QuoteSystemJobsInvoice(nBatch: LongInt;
    const dStartDate, dEndDate: TDateTime; const BatchDescription : string;
    const lAnyTime : boolean);
  procedure QuoteSystemInvoice(nBatch: LongInt;
    const dStartDate, dEndDate: TDateTime; const BatchDescription : string;
    const lAnyTime : boolean);


implementation

uses
  Math,
  Main, DataMod, GenFns, DateFunctions, FileFunctions,
  CriticalTests, MyMessage, Progress;

const
  // Declare preset values, like default values determined from viewing data.
  DEFAULT_TRANSACTIONTYPEID = 65;     // In case TransactionTypeID not found.
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.
  slAddFieldNames  : TStringList;     // Calls to AddToTable() only.
  slAddFieldValues : TStringList;     // Calls to AddToTable() only.

procedure SetupForNewTransactionTypes;
begin
  with slAddFieldNames do begin
    Clear;
    Add('Description');
    Add('TransactionGroupID');
    Add('SystemID');
    Add('Active');
    Add('StartDate');
    Add('LastUpdatedBy');
  end;  // slAddFieldNames.
end;  // SetupForNewTransactionTypes.

procedure AddNewTransactionType
          (
           const sDescription : string;
           const nTransactionGroupID : LongInt;
           const nSystemID : LongInt
          );
begin
  with slAddFieldValues do begin
    Clear;
    // Description
    Add(''''+ sDescription +'''');
    // TransactionGroupID
    Add(IntToStr(nTransactionGroupID));
    // SystemID
    Add(IntToStr(nSystemID));
    // Active
    Add('1');
    // StartDate
    Add(DateToStr(Now));
    // LastUpdatedBy
    Add('''TRANS-MIT''');
  end;  // slAddFieldValues.
  AddToTable('tTransactionType', slAddFieldNames, slAddFieldValues);
end;  // InstallNewTransactionType.

procedure GetQuotePurchases
          (
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const lAnyTime : boolean;
           var lReturnCode: Boolean
           );
// 080629: New field ITHELPUSER saved to Var09 for Jobs and Purchases.
var
  dLogDateTime : TDateTime;
  sBillPeriod : string;
  sFromDate : string;
  sToDate : string;
  sBatch : string;
  sBatchDescription : string;
  curAmtExGST : currency;
  curAmtGST : currency;

  sBatchTypeID : string;
  nSystemID : integer;
  sSystemID : string;
  nTransactionGroupID : integer;
  vDescription : variant;
  lFound : boolean;
  sTransactionTypeID : string;
  sCode : string;
  sDescription : string;
  sShipTo : string;
  sServiceID : string;
  sServiceTypeID : string;
  slTransactionTypeName : TStringList;
  slTransactionTypeID : TStringList;
  nTransactionType : integer;
  i : integer;
  nPos : integer;

  sSubString : string;
  sListValues : String;
  sListFields : String;
  FieldStringList : TStringList;
  ValueStringList : TStringList;
  qryStringList : TStringList;
  qryDC : TQuery;
  qryQuoteSystem : TQuery;
  qryLook : TQuery;
  qryTT : TQuery;
  nListValues : integer;
  nTotalCount : integer;
  nBlock : integer;
  Factor : Real;
  lGSTFlag : boolean;
  fGSTRate : Real;
  MessageForm : TMessagesForm;
begin
  sBatch := IntToStr(nBatchNumber);
  sBillPeriod := IntToStr(Year(dStartDate)) +'/'+
    Right('0'+IntToStr(Month(dStartDate)),2);
  sFromDate := FormattedDateString(dStartDate);
  sToDate := FormattedDateString(dEndDate);
  sBatchDescription := BatchDescription;
  fGSTRate := MAINFORM.GST_RATE;

  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';
  qryQuoteSystem := TQuery.Create(MainForm);
  qryQuoteSystem.DatabaseName := 'dbQuote';
  qryLook := TQuery.Create(MainForm);
  qryLook.DatabaseName := 'dbPPdata';
  qryTT := TQuery.Create(MainForm);
  qryTT.DatabaseName := 'dbPPdata';

  lReturnCode := True;
  nFactor := 100.0;
  nCount := 0;

  frmProgress := TProgressForm.Create(MainForm);
  frmProgress.btnCancel.Visible := False;
  frmProgress.Show;

  frmProgress.Caption := 'Quote System Purchases 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;

  // Determine the SystemID - used for searching the tTransasactionType table.
  with qryTT do begin
    SQL.Add('SELECT BatchTypeID');
    SQL.Add('FROM tBatch');
    SQL.Add('WHERE ID = '+sBatch);
    Open;
  end;  // qryTT.
  if qryTT.Eof then begin
    sBatchTypeID := '0';
    nSystemID := 0;
  end
  else begin
    sBatchTypeID := IntToStr(qryTT.FieldByName('BatchTypeID').AsInteger);
    with qryTT do begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT ID');
      SQL.Add('FROM tSystem');
      SQL.Add('WHERE BatchTypeID = '+sBatchTypeID);
      Open;
    end;  // qryTT.
    if qryTT.Eof then begin
      nSystemID := 0;
    end
    else begin
      nSystemID := qryTT.FieldByName('ID').AsInteger;
    end;
  end;  // qryTT.Eof.
  sSystemID := IntToStr(nSystemID);

  with qryTT do begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT ID, Description, TransactionGroupID');
    SQL.Add('FROM tTransactionType');
    SQL.Add('WHERE SystemID = '+sSystemID);
    SQL.Add('  AND Active = 1');
    Open;
    if not Eof then begin
      nTransactionGroupID := qryTT.FieldByName('TransactionGroupID').AsInteger;
    end
    else begin
      nTransactionGroupID := 3;       // Default = 'OTHER'.
    end;
    Close;
    // Reset to only the fields required.
    SQL.Strings[0] := 'SELECT ID, Description';
    Open;
  end;  // qryTT.
{
  slTransactionTypeName := TStringList.Create;
  slTransactionTypeID := TStringList.Create;
  with qryDC do begin
    SQL.Add('SELECT ID, UPPER(Description) AS UDescription');
    SQL.Add('FROM tTransactionType');
    SQL.Add('WHERE Description LIKE ''%Purchas%''');
    SQL.Add('AND Description NOT LIKE ''%Jobs%''');
    Open;
    while not qryDC.Eof do begin
      slTransactionTypeName.Add(FieldByName('UDescription').AsString);
      slTransactionTypeID.Add(IntToStr(FieldByName('ID').AsInteger));
      Next;
    end;
    Close;
    SQL.Clear;
  end;  // qryDC.
}
  with qryStringList do begin
    Add('SELECT *');
    Add('FROM BillPurchasing');
    if lAnyTime then begin
      Add('LEFT JOIN Quote_Items ON BillPurchasing.CallNumber = '+
                    'Quote_Items.CallNumber');
    end;
    Add('WHERE BillPurchasing.BillPeriod LIKE '''+sBillPeriod+'''');
    if lAnyTime then begin
      Add('  AND (Quote_Items.ppTaken IS NULL OR '+
                 'Quote_Items.ppTaken = 0)');
    end;
    Add('ORDER BY BillPurchasing.LogDateTime');
  end;  // qryStringList.

  qryQuoteSystem.SQL.Text := qryStringList.Text;
  qryQuoteSystem.Open;
  qryStringList.Clear;

  with qryLook do begin
    SQL.Add('SELECT Code, Description');
    SQL.Add('FROM tServiceType');
    SQL.Add('WHERE ID =');
  end;  // qryLook.

  //****************************************************************
  //****************************************************************
  //********* START READING BILLS INTO 'tDataCollection' ***********
  //****************************************************************
  if qryQuoteSystem.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 qryQuoteSystem.Eof do begin
      frmProgress.ProcessWinMessages(MainForm);

      FieldStringList.Clear;
      ValueStringList.Clear;
      sListFields := '(';
      sListValues := 'VALUES (';
      sListFields := sListFields + 'SequenceNo,';
      sListValues := sListValues + '''' +
        qryQuoteSystem.FieldByName('SeqNo').AsString +''',';
      // NB ShipTo (following) needed NOW for the default values.
      sShipTo := Trim(UpperCase(qryQuoteSystem.FieldByName('ShipTo').AsString));
      if VarIsNull(qryQuoteSystem['ServiceTypeID']) then begin
        sServiceTypeID := '';          // Forces the next entry to reset values.
        if (sShipTo = 'VRT') then begin
          sCode := 'WPV';              // Default.
          sDescription := 'PURCHASING';
        end
        else begin
          sCode := 'WPC';              // Default.
          sDescription := 'PURCHASING INSTALL EQPT';
        end;
        // Look up the code in the tServiceType table using description.
        qryLook.Close;
        qryLook.SQL.Strings[2] := 'WHERE Description LIKE '+
          ''''+ sDescription +'''';
        qryLook.Open;
        if not qryLook.Eof then begin
          // Found - update the sCode value.
          sCode := qryLook.FieldByName('Code').AsString;
        end;
      end
      else begin
        // Reset the values only if the contents of sServiceTypeID have changed.
        if (sServiceTypeID <> IntToStr(qryQuoteSystem['ServiceTypeID'])) then begin
          sServiceTypeID := IntToStr(
            qryQuoteSystem.FieldByName('ServiceTypeID').AsInteger);
          // Look up the code in the tServiceType table using ID.
          qryLook.Close;
          qryLook.SQL.Strings[2] := 'WHERE ID = ' + sServiceTypeID;
          qryLook.Open;
          if qryLook.Eof then begin
            if (sShipTo = 'VRT') then begin
              sCode := 'WPV';               // Default.
              sDescription := 'PURCHASING';
            end
            else begin
              sCode := 'WPC';               // Default.
              sDescription := 'PURCHASING INSTALL EQPT';
            end;
          end
          else begin
            sCode := qryLook.FieldByName('Code').AsString;
            sDescription := qryLook.FieldByName('Description').AsString;
          end;
        end;
      end;
      sListFields := sListFields + 'ServiceType,';
      sListValues := sListValues + '''' + sCode +''',';
      sListFields := sListFields + 'ServiceID,';
      // RJC 060621: sCode added to the ServiceID entry.
      sServiceID := Trim('PURCHASING '+ sShipTo +' '+ sCode);
      sListValues := sListValues + '''' + sServiceID +''',';
      if not VarIsNull(qryQuoteSystem['LogDateTime']) then begin
        sListFields := sListFields + 'TxnDate,';
        dLogDateTime := qryQuoteSystem.FieldByName('LogDateTime').AsDateTime;
        sListValues := sListValues +''''+FormattedDateString(dLogDateTime)+''',';
        sListFields := sListFields + 'TxnTime,';
        sListValues := sListValues +''''+ TimeToStr(dLogDateTime) +''',';
      end;

      // Now set the transaction type values - first the description.
      if (sShipTo = 'VRT') then sServiceID := Trim(sShipTo +' '+ sDescription)
      else                      sServiceID := Trim(sDescription);
      // Now set the transaction type values - search for it.
      vDescription := sServiceID;
      with qryTT do
        lFound := Locate('Description', vDescription, [loCaseInsensitive]);
      // We have it in the table.
      if lFound then begin
        sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger);
      end
      else begin
        // New - create a new entry in tTransactionType.
        AddNewTransactionType( sServiceID, nTransactionGroupID, nSystemID);
        qryTT.Close;
        qryTT.Open;
        // Now retrieve the index number - ID.
        with qryTT do
          lFound := Locate('Description', vDescription, [loCaseInsensitive]);
        if lFound then begin
          // We have it - of course.
          sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger);
        end
        else begin
          // Something wrong - use a generic value.
          sTransactionTypeID := IntToStr(DEFAULT_TRANSACTIONTYPEID);
        end;  // lFound.
      end;  // lFound.
      sListFields := sListFields + 'TransactionTypeID,';
      sListValues := sListValues + sTransactionTypeID + ',';
{
      nTransactionType := -1;
      for i := 0 to slTransactionTypeName.Count-1 do begin
        sSubString := slTransactionTypeName.Strings[i];
        nPos := Pos(' ',sSubString)+1;
        if (Copy(sSubString,nPos,50) = sServiceID) then begin
          nTransactionType := i;
          break;
        end;
      end;  // for.
      if (nTransactionType > -1) and
         (nTransactionType < slTransactionTypeName.Count) then begin
        sListFields := sListFields + 'TransactionTypeID,';
        sListValues := sListValues + slTransactionTypeID[nTransactionType]+',';
      end;
}
      // Save the values.
      FieldStringList.Add(sListFields);
      ValueStringList.Add(sListValues);
      sListFields := '';
      sListValues := '';

      if not VarIsNull(qryQuoteSystem['AmtExGST']) then begin
        curAmtExGST := qryQuoteSystem.fieldByName('AmtExGST').AsCurrency;
        sListFields := sListFields + 'AmountExGST,';           // AmtExGST.
        sListValues := sListValues + CurrToStr(curAmtExGST)+',';
        lGSTFlag := (UpperCase(sShipTo) <> 'VRT');
        sListFields := sListFields + 'GSTFlag,';
        if (lGSTFlag) then begin
          sListValues := sListValues + '''Y'',';
          curAmtGST := curAmtExGST * fGSTRate;
        end
        else begin
          sListValues := sListValues + '''N'',';
          curAmtGST := 0.00;
        end;  // if GSTFlag='Y' then begin.
        sListFields := sListFields + 'GSTAmount,AmountIncGST,';
        sListValues := sListValues + CurrToStr(curAmtGST)+','+
          CurrToStr(curAmtExGST + curAmtGST)+',';
      end;  // AmtExGST.
      sListFields := sListFields + 'FromDate,';
      sListValues := sListValues +''''+ sFromDate +''',';
      sListFields := sListFields + 'ToDate,';
      sListValues := sListValues +''''+ sToDate +''',';
      if not VarIsNull(qryQuoteSystem['CallNumber']) then begin
        sListFields := sListFields + 'Var01,';           // CallNumber.
        sListValues := sListValues +''''+
          qryQuoteSystem.FieldByName('CallNumber').AsString +''',';
      end;
      if not VarIsNull(qryQuoteSystem['Supplier']) then begin
        sListFields := sListFields + 'Var02,';             // Supplier.
        sListValues := sListValues +''''+
          qryQuoteSystem.FieldByName('Supplier').AsString+''',';
      end;
      if not VarIsNull(qryQuoteSystem['BilledDate']) then begin
        sListFields := sListFields + 'Var03,';             // BilledDate.
        sListValues := sListValues +''''+
          StandardDateTimeString(qryQuoteSystem.FieldByName('BilledDate').AsDateTime)+
          ''',';
      end;
      if not VarIsNull(qryQuoteSystem['CustRef']) then begin
        sListFields := sListFields + 'Var04,';             // CustRef.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('CustRef').AsString) +''',';
      end;
      FieldStringList.Add(sListFields);
      ValueStringList.Add(sListValues);
      sListFields := '';
      sListValues := '';
      if not VarIsNull(qryQuoteSystem['Description']) then begin
        sListFields := sListFields + 'Var05,';           // Description.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('Description').AsString) +''',';
      end;
      if (Length(sShipTo) > 0) then begin
        sListFields := sListFields + 'Var06,';           // ShipTo.
        sListValues := sListValues +''''+ sShipTo +''',';
      end;
      if not VarIsNull(qryQuoteSystem['PurchaseOrder']) then begin
        sListFields := sListFields + 'Var07,';           // PurchaseOrder.
        sListValues := sListValues +''''+
          qryQuoteSystem.FieldByName('PurchaseOrder').AsString +''',';
      end;
      if not VarIsNull(qryQuoteSystem['PurchaseOrderDate']) then begin
        sListFields := sListFields + 'Var08,';           // PurchaseOrderDate.
        sListValues := sListValues +''''+ StandardDateTimeString(
          qryQuoteSystem.FieldByName('PurchaseOrderDate').AsDateTime) +''',';
      end;
//{
      if not VarIsNull(qryQuoteSystem['ITHelpUser']) then begin
        sListFields := sListFields + 'Var09,';           // ITHelpUser.
        sListValues := sListValues +''''+
          qryQuoteSystem.FieldByName('ITHelpUser').AsString +''',';
      end;
//}
      FieldStringList.Add(sListFields);
      ValueStringList.Add(sListValues);
      sListFields := '';
      sListValues := '';
      if not VarIsNull(qryQuoteSystem['Location']) then begin
        sListFields := sListFields + 'Var10,';           // Location.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('Location').AsString) +''',';
      end;
      if not VarIsNull(qryQuoteSystem['LastName']) then begin
        sListFields := sListFields + 'Var14,';           // LastName.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('LastName').AsString) +''',';
      end;
      if not VarIsNull(qryQuoteSystem['Bill']) then begin
        sListFields := sListFields + 'Var15,';           // Bill.
        sListValues := sListValues + IntToStr(qryQuoteSystem['Bill']) +',';
      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);
      qryQuoteSystem.Next;
    end;  // while not qryQuoteSystem.Eof do begin
    if lReturnCode then begin
      frmProgress.Hide;
      MessageForm := TMessagesForm.Create(MainForm);
      MessageForm.Caption := sBatch +': Quote System ...';
      MessageForm.Msg.Caption := 'Updating tBatch data ...';
      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;
  qryQuoteSystem.Close;
  qryQuoteSystem.Free;
end;  // GetQuotePurchases.


procedure GetQuoteJobs
          (
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const lAnyTime : boolean;
           var lReturnCode: Boolean
           );
// 080629: New field ITHELPUSER saved to Var09 for Jobs and Purchases.
var
  dLogDateTime : TDateTime;
  sBillPeriod : string;
  sFromDate : string;
  sToDate : string;
  sBatch : string;
  sBatchDescription : string;
  curAmtExGST : currency;
  curAmtGST : currency;

  sBatchTypeID : string;
  nSystemID : integer;
  sSystemID : string;
  nTransactionGroupID : integer;
  vDescription : variant;
  lFound : boolean;
  sTransactionTypeID : string;
  sCode : string;
  sDescription : string;
  sShipTo : string;
  sServiceID : string;
  sServiceTypeID : string;
  slTransactionTypeName : TStringList;
  slTransactionTypeID : TStringList;
  nTransactionType : integer;
  i : integer;
  nPos : integer;

  sSubString : string;
  sListValues : String;
  sListFields : String;
  FieldStringList : TStringList;
  ValueStringList : TStringList;
  qryStringList : TStringList;
  qryDC : TQuery;
  qryQuoteSystem : TQuery;
  qryLook : TQuery;
  qryTT : TQuery;
  nListValues : integer;
  nTotalCount : integer;
  nBlock : integer;
  Factor : Real;
  lGSTFlag : boolean;
  fGSTRate : Real;
  MessageForm : TMessagesForm;
begin
  sBatch := IntToStr(nBatchNumber);
  sBillPeriod := IntToStr(Year(dStartDate)) +'/'+
    Right('0'+IntToStr(Month(dStartDate)),2);
  sFromDate := FormattedDateString(dStartDate);
  sToDate := FormattedDateString(dEndDate);
  sBatchDescription := BatchDescription;
  fGSTRate := MAINFORM.GST_RATE;

  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';
  qryQuoteSystem := TQuery.Create(MainForm);
  qryQuoteSystem.DatabaseName := 'dbQuote';
  qryLook := TQuery.Create(MainForm);
  qryLook.DatabaseName := 'dbPPdata';
  qryTT := TQuery.Create(MainForm);
  qryTT.DatabaseName := 'dbPPdata';

  lReturnCode := True;
  nFactor := 100.0;
  nCount := 0;

  frmProgress := TProgressForm.Create(MainForm);
  frmProgress.btnCancel.Visible := False;
  frmProgress.Show;

  frmProgress.Caption := 'Quote System Jobs 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;

  // Determine the SystemID - used for searching the tTransasactionType table.
  with qryTT do begin
    SQL.Add('SELECT BatchTypeID');
    SQL.Add('FROM tBatch');
    SQL.Add('WHERE ID = '+sBatch);
    Open;
  end;  // qryTT.
  if qryTT.Eof then begin
    sBatchTypeID := '0';
    nSystemID := 0;
  end
  else begin
    sBatchTypeID := IntToStr(qryTT.FieldByName('BatchTypeID').AsInteger);
    with qryTT do begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT ID');
      SQL.Add('FROM tSystem');
      SQL.Add('WHERE BatchTypeID = '+sBatchTypeID);
      Open;
    end;  // qryTT.
    if qryTT.Eof then begin
      nSystemID := 0;
    end
    else begin
      nSystemID := qryTT.FieldByName('ID').AsInteger;
    end;
  end;  // qryTT.Eof.
  sSystemID := IntToStr(nSystemID);

  with qryTT do begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT ID, Description, TransactionGroupID');
    SQL.Add('FROM tTransactionType');
    SQL.Add('WHERE SystemID = '+sSystemID);
    SQL.Add('  AND Active = 1');
    Open;
    if not Eof then begin
      nTransactionGroupID := qryTT.FieldByName('TransactionGroupID').AsInteger;
    end
    else begin
      nTransactionGroupID := 3;       // Default = 'OTHER'.
    end;
    Close;
    // Reset to only the fields required.
    SQL.Strings[0] := 'SELECT ID, Description';
    Open;
  end;  // qryTT.
{
  slTransactionTypeName := TStringList.Create;
  slTransactionTypeID := TStringList.Create;
  with qryDC do begin
    SQL.Add('SELECT ID, UPPER(Description) AS UDescription');
    SQL.Add('FROM tTransactionType');
    SQL.Add('WHERE Description LIKE ''%Jobs %''');
    SQL.Add('AND Description LIKE ''%Labour%''');
    Open;
    while not Eof do begin
    slTransactionTypeName.Add(FieldByName('UDescription').AsString);
    slTransactionTypeID.Add(IntToStr(FieldByName('ID').AsInteger));
      Next;
    end;
    Close;
    SQL.Strings[3] := 'AND Description LIKE ''%TH Install%''';
    Open;
    while not Eof do begin
    slTransactionTypeName.Add(FieldByName('UDescription').AsString);
    slTransactionTypeID.Add(IntToStr(FieldByName('ID').AsInteger));
      Next;
    end;
    Close;
    SQL.Clear;
  end;  // qryDC.
}

  with qryStringList do begin
    Add('SELECT *');
    Add('FROM BillJobs');
    if lAnyTime then begin
      Add('LEFT JOIN Quote_Items ON BillJobs.CallNumber = '+
                    'Quote_Items.CallNumber');
    end;  
    Add('WHERE BillJobs.BillPeriod LIKE '''+sBillPeriod+'''');
    if lAnyTime then begin
      Add('  AND (Quote_Items.ppTaken IS NULL OR '+
                 'Quote_Items.ppTaken = 0)');
    end;
    Add('ORDER BY BillJobs.LogDateTime');
  end;  // with qryStringList.
  qryQuoteSystem.SQL := qryStringList;
  qryQuoteSystem.Open;
  qryStringList.Clear;

  qryLook.SQL.Add('SELECT Code, Description');
  qryLook.SQL.Add('FROM tServiceType');
  qryLook.SQL.Add('WHERE ID =');


  //****************************************************************
  //****************************************************************
  //********* START READING BILLS INTO 'tDataCollection' ***********
  //****************************************************************
  if qryQuoteSystem.Eof then begin
    MessageDlg('No entries found.', mtWarning, [mbOk], 0);
    lReturnCode := False;
  end
  else begin
    // NOTE: Charge amounts added later AFTER ALL entries added to tDataCollection.
    while not qryQuoteSystem.Eof do begin
      frmProgress.ProcessWinMessages(MainForm);
      FieldStringList.Clear;
      ValueStringList.Clear;
      sListFields := '(';
      sListValues := 'VALUES (';
      sListFields := sListFields + 'SequenceNo,';
      sListValues := sListValues + '''' +
        qryQuoteSystem.FieldByName('SeqNo').AsString +''',';
      // NB ShipTo (following) needed NOW for the default values.
      sShipTo := Trim(UpperCase(qryQuoteSystem.FieldByName('ShipTo').AsString));
      if VarIsNull(qryQuoteSystem['ServiceTypeID']) then begin
        sServiceTypeID := '';          // Forces the next entry to reset values.
        if (sShipTo = 'VRT') then begin
          sCode := 'JI1';              // Default.
          sDescription := 'JOBS LABOUR INSTALL';
        end
        else begin
          sCode := 'JIL';              // Default.
          sDescription := 'JOBS INSTALL LABOUR';
        end;
        // Look up the code in the tServiceType table using description.
        qryLook.Close;
        qryLook.SQL.Strings[2] := 'WHERE Description LIKE '+
          ''''+ sDescription +'%''';
        qryLook.Open;
        if not qryLook.Eof then begin
          sCode := qryLook.FieldByName('Code').AsString;
        end;
      end
      else begin
        // Reset the values only if the contents of sServiceTypeID have changed.
        if (sServiceTypeID <> IntToStr(qryQuoteSystem['ServiceTypeID'])) then begin
          sServiceTypeID := IntToStr(
            qryQuoteSystem.FieldByName('ServiceTypeID').AsInteger);
          // Look up the code in the tServiceType table using ID.
          qryLook.Close;
          qryLook.SQL.Strings[2] := 'WHERE ID = ' + sServiceTypeID;
          qryLook.Open;
          if qryLook.Eof then begin
            if (sShipTo = 'VRT') then begin
              sCode := 'JI1';               // VRT Default.
              sDescription := 'JOBS LABOUR INSTALL';
            end
            else begin
              sCode := 'JIL';               // Default if not VRT.
              sDescription := 'JOBS INSTALL LABOUR';
            end;
          end
          else begin
            sCode := qryLook.FieldByName('Code').AsString;
            sDescription := qryLook.FieldByName('Description').AsString;
          end;
        end;
      end;
      sListFields := sListFields + 'ServiceType,';
      sListValues := sListValues + '''' + sCode +''',';
      sListFields := sListFields + 'ServiceID,';
      // RJC 060621: sCode added to the ServiceID entry.
      sServiceID := Trim('JOBS '+ sShipTo +' '+ sCode);
      sListValues := sListValues + '''' + sServiceID +''',';
      if not VarIsNull(qryQuoteSystem['LogDateTime']) then begin
        sListFields := sListFields + 'TxnDate,';
        dLogDateTime := qryQuoteSystem.FieldByName('LogDateTime').AsDateTime;
        sListValues := sListValues +''''+FormattedDateString(dLogDateTime)+''',';
        sListFields := sListFields + 'TxnTime,';
        sListValues := sListValues +''''+ TimeToStr(dLogDateTime) +''',';
      end;

      // Now set the transaction type values.
      if (sShipTo = 'VRT') then sServiceID := Trim(sShipTo +' '+ sDescription)
      else                      sServiceID := Trim(sDescription);
      // Now set the transaction type values - first the description.
      if (sShipTo = 'VRT') then sServiceID := Trim(sShipTo +' '+ sDescription)
      else                      sServiceID := Trim(sDescription);
      // Now set the transaction type values - search for it.
      vDescription := sServiceID;
      with qryTT do
        lFound := Locate('Description', vDescription, [loCaseInsensitive]);
      // We have it in the table.
      if lFound then begin
        sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger);
      end
      else begin
        // New - create a new entry in tTransactionType.
        AddNewTransactionType( sServiceID, nTransactionGroupID, nSystemID);
        qryTT.Close;
        qryTT.Open;
        // Now retrieve the index number - ID.
        with qryTT do
          lFound := Locate('Description', vDescription, [loCaseInsensitive]);
        if lFound then begin
          // We have it - of course.
          sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger);
        end
        else begin
          // Something wrong - use a generic value.
          sTransactionTypeID := IntToStr(DEFAULT_TRANSACTIONTYPEID);
        end;  // lFound.
      end;  // lFound.
      sListFields := sListFields + 'TransactionTypeID,';
      sListValues := sListValues + sTransactionTypeID + ',';
{
      nTransactionType := -1;
      for i := 0 to slTransactionTypeName.Count-1 do begin
        sSubString := slTransactionTypeName.Strings[i];
        nPos := Pos(' ',sSubString)+1;
        if (Copy(sSubString,nPos,50) = sServiceID) then begin
          nTransactionType := i;
          break;
        end;
      end;  // for.
      if (nTransactionType > -1) and
         (nTransactionType < slTransactionTypeName.Count) then begin
        sListFields := sListFields + 'TransactionTypeID,';
        sListValues := sListValues + slTransactionTypeID[nTransactionType]+',';
      end;
}
      FieldStringList.Add(sListFields);
      ValueStringList.Add(sListValues);
      sListFields := '';
      sListValues := '';
      
      if not VarIsNull(qryQuoteSystem['AmtExGST']) then begin
//        curAmtExGST := curFormat(qryQuoteSystem['AmtExGST']);
        curAmtExGST := qryQuoteSystem.FieldByName('AmtExGST').AsCurrency;
        sListFields := sListFields + 'AmountExGST,';           // AmtExGST.
//        sListValues := sListValues + Curr2Str(curAmtExGST)+',';
        sListValues := sListValues + CurrToStr(curAmtExGST)+',';
        lGSTFlag := (UpperCase(sShipTo) <> 'VRT');
        sListFields := sListFields + 'GSTFlag,';
        if (lGSTFlag) then begin
          sListValues := sListValues + '''Y'',';
//          curAmtGST := curFormat(curAmtExGST * fGSTRate);
          curAmtGST := curAmtExGST * fGSTRate;
        end
        else begin
          sListValues := sListValues + '''N'',';
          curAmtGST := 0.00;
        end;  // if GSTFlag='Y' then begin.
        sListFields := sListFields + 'GSTAmount,AmountIncGST,';
//        sListValues := sListValues + Curr2Str(curAmtGST)+','+
//          Curr2Str(curSum(curAmtExGST,curAmtGST))+',';
        sListValues := sListValues + CurrToStr(curAmtGST)+','+
          CurrToStr(curAmtExGST + curAmtGST)+',';
      end;  // AmtExGST.
      sListFields := sListFields + 'FromDate,';
      sListValues := sListValues +''''+ sFromDate +''',';
      sListFields := sListFields + 'ToDate,';
      sListValues := sListValues +''''+ sToDate +''',';
      if not VarIsNull(qryQuoteSystem['CallNumber']) then begin
        sListFields := sListFields + 'Var01,';                 // CallNumber.
        sListValues := sListValues +''''+
          qryQuoteSystem.FieldByName('CallNumber').AsString +''',';
      end;
      if not VarIsNull(qryQuoteSystem['Supplier']) then begin
        sListFields := sListFields + 'Var02,';                 // Supplier.
        sListValues := sListValues +''''+
          qryQuoteSystem.FieldByName('Supplier').AsString+''',';
      end;
      if not VarIsNull(qryQuoteSystem['BilledDate']) then begin
        sListFields := sListFields + 'Var03,';                 // BilledDate.
        sListValues := sListValues +''''+
          StandardDateTimeString(qryQuoteSystem.FieldByName('BilledDate').AsDateTime)+
          ''',';
      end;
      if not VarIsNull(qryQuoteSystem['CustRef']) then begin
        sListFields := sListFields + 'Var04,';                 // CustRef.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('CustRef').AsString) +''',';
      end;
      FieldStringList.Add(sListFields);
      ValueStringList.Add(sListValues);
      sListFields := '';
      sListValues := '';
      if not VarIsNull(qryQuoteSystem['Description']) then begin
        sListFields := sListFields + 'Var05,';                 // Description.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('Description').AsString) +''',';
      end;
      if (Length(sShipTo) > 0) then begin
        sListFields := sListFields + 'Var06,';                 // ShipTo.
        sListValues := sListValues +''''+ sShipTo +''',';
      end;
      if not VarIsNull(qryQuoteSystem['CustRef']) then begin
        // Repeat the CustRef saved in Var04 into Var07 too - RLD 170120.  
        sListFields := sListFields + 'Var07,';                 // CustRef.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('CustRef').AsString) +''',';
      end;
      if not VarIsNull(qryQuoteSystem['ITHelpUser']) then begin
        sListFields := sListFields + 'Var09,';                 // ITHelpUser.
        sListValues := sListValues +''''+
          qryQuoteSystem.FieldByName('ITHelpUser').AsString +''',';
      end;
      FieldStringList.Add(sListFields);
      ValueStringList.Add(sListValues);
      sListFields := '';
      sListValues := '';
      if not VarIsNull(qryQuoteSystem['Location']) then begin
        sListFields := sListFields + 'Var10,';                 // Location.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('Location').AsString) +''',';
      end;
      if not VarIsNull(qryQuoteSystem['ProgOnly']) then begin
        sListFields := sListFields + 'Var12,';                 // ProgOnly.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('ProgOnly').AsString) +''',';
      end;
      if not VarIsNull(qryQuoteSystem['ProgLocation']) then begin
        sListFields := sListFields + 'Var13,';                 // ProgLocation.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('ProgLocation').AsString) +''',';
      end;
      if not VarIsNull(qryQuoteSystem['LastName']) then begin
        sListFields := sListFields + 'Var14,';                 // LastName.
        sListValues := sListValues +''''+
          CleanString(qryQuoteSystem.FieldByName('LastName').AsString) +''',';
      end;
      if not VarIsNull(qryQuoteSystem['Bill']) then begin
        sListFields := sListFields + 'Var15,';                 // Bill.
        sListValues := sListValues + IntToStr(qryQuoteSystem['Bill']) +',';
      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);
      qryQuoteSystem.Next;
    end;  // while not qryQuoteSystem.Eof do begin
    if lReturnCode then begin
      frmProgress.Hide;
      MessageForm := TMessagesForm.Create(MainForm);
      MessageForm.Caption := sBatch +': Quote System ...';
      MessageForm.Msg.Caption := 'Updating tBatch data ...';
      MessageForm.Show;
      frmProgress.ProcessWinMessages(MainForm);

      // Now update the values stored in the tBatch table.
      with qryStringList do begin
        Clear;
        Add('UPDATE tBatch');
        Add('SET GSTAmount = BatchRate.TotalGST,');
        Add(' TotalPayable = BatchRate.TotalIncGST');
        Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+
          'AS TotalIncGST,');
        Add(' Sum(tDataCollection.GSTAmount) AS TotalGST');
        Add('FROM tDataCollection');
        Add('WHERE tDataCollection.BatchID = '+ sBatch + ') AS BatchRate');
        Add('WHERE tBatch.ID = '+ sBatch);
      end;  // qryStringList.
      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;
  qryQuoteSystem.Close;
  qryQuoteSystem.Free;
end;  // GetQuoteJobs.


procedure QuoteSystemPurchasesInvoice(nBatch: LongInt;const dStartDate, dEndDate:
   TDateTime; const BatchDescription : string;
    const lAnyTime : boolean);
var
  lContinue : Boolean;
  EmptyList : TStringList;
begin
  EmptyList := TStringList.Create;         // Removes all entries for the batch.
  lContinue := True;
  // If lAnytime then tDataCollection has been processed first - else, restart.
//  if not lAnyTime then DeleteFromDC( nBatch, EmptyList, lContinue);
  DeleteFromDC( nBatch, EmptyList, lContinue);
  EmptyList.Free;
  // Initiate values for the addition of any new entries in tTransactionType.
  slAddFieldValues := TStringList.Create; // Used in calls to AddToTable() only.
  slAddFieldNames := TStringList.Create;  // Used in calls to AddToTable() only.
  SetupForNewTransactionTypes;
  GetQuotePurchases( nBatch, dStartDate, dEndDate, BatchDescription,
                     lAnyTime, lContinue);
end;  // QuoteSystemPurchasesInvoice.

procedure QuoteSystemJobsInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime;
    const BatchDescription : string;
    const lAnyTime : boolean);
var
  lContinue : Boolean;
  EmptyList : TStringList;
begin
  EmptyList := TStringList.Create;         // Removes all entries for the batch.
  lContinue := True;
  // If lAnytime then tDataCollection has been processed first - else, restart.
//  if not lAnyTime then DeleteFromDC( nBatch, EmptyList, lContinue);
  DeleteFromDC( nBatch, EmptyList, lContinue);
  EmptyList.Free;
  // Initiate values for the addition of any new entries in tTransactionType.
  slAddFieldValues := TStringList.Create; // Used in calls to AddToTable() only.
  slAddFieldNames := TStringList.Create;  // Used in calls to AddToTable() only.
  SetupForNewTransactionTypes;
  GetQuoteJobs( nBatch, dStartDate, dEndDate, BatchDescription, 
                lAnyTime, lContinue);
end;  // QuoteSystemJobsInvoice.

procedure QuoteSystemInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime;
    const BatchDescription : string;
    const lAnyTime : boolean);
var
  lContinue : Boolean;
  EmptyList : TStringList;
begin
  EmptyList := TStringList.Create;         // Removes all entries for the batch.
  lContinue := True;
  // If lAnytime then tDataCollection has been processed first - else, restart.
//  if not lAnyTime then DeleteFromDC( nBatch, EmptyList, lContinue);
  DeleteFromDC( nBatch, EmptyList, lContinue);
  EmptyList.Free;
  // Initiate values for the addition of any new entries in tTransactionType.
  slAddFieldValues := TStringList.Create; // Used in calls to AddToTable() only.
  slAddFieldNames := TStringList.Create;  // Used in calls to AddToTable() only.
  SetupForNewTransactionTypes;
  GetQuotePurchases( nBatch, dStartDate, dEndDate, BatchDescription, 
                     lAnyTime, lContinue);
  if lContinue then
    GetQuoteJobs( nBatch, dStartDate, dEndDate, BatchDescription, 
                  lAnyTime, lContinue);
end;  // QuoteSystemInvoice.


end.  // QuoteSystem.