unit SaveToTransaction;
{
Revised 20150913 RJC SaveTransaction()
        Remove the use of tDataCollection.ServiceType for VicTrack Unified
        Communications invoices. The field is populated to assist the processing
        of the data only. It contains redundant information and otherwise links
        incorrectly to other entries in the tServiceType table.
}
interface

uses SysUtils, dbtables, bde, classes, Dialogs;

  procedure SaveTransaction(Const nThisBatch: LongInt;var lSuccessful: Boolean);
  procedure PrepareGeneralVarData;
  procedure PrepareGeneralIDdata;
  procedure PrepareGeneralDCdata;
  procedure GetTransactionReferences;


implementation

uses
  CurrentRentCalls, DataMod, DateFunctions, GenFns, GenericPrint,
  Main, MyMessage, Progress;

var
  nBatch : LongInt;
  sBatch : string;
  qryDC : TQuery;                   // tDataCollection.
  qrySS : TQuery;                   // tServiceSplit.
  qryTT : TQuery;                   // tTransaction.
  FieldStringList : TStringList;
  ValueStringList : TStringList;
  qryStringList : TStringList;
  sListValues : String;
  sListFields : String;

  MinDate : TDateTime;
  MaxDate : TDateTime;
  
  nTransactionRent  : Integer;
  nTransactionCall  : Integer;
  nTransactionOther : Integer;
  nSplitRent  : Integer;
  nSplitCall  : Integer;
  nSplitOther : Integer;


procedure GetTransactionReferences;
var
  sValue : string;
  qAns : TQuery;
begin
  qAns := TQuery.Create(nil);
  qAns.DatabaseName := 'dbPPdata';
  qAns.SQL.Add('SELECT tTransactionType.ID, tTransactionType.Description');
  qAns.SQL.Add('FROM tTransactionType');
  qAns.SQL.Add('WHERE tTransactionType.Description LIKE ''Split%''');
  qAns.Open;
  while not qAns.eof do begin
    sValue := UpperCase(Trim(Copy(
      qAns.FieldByName('Description').AsString,6,5)));
    if (sValue = 'RENT') then begin
      nSplitRent := qAns['ID'];
    end
    else if (sValue = 'CALL') then begin
      nSplitCall := qAns['ID'];
    end
    else if (sValue = 'OTHER') then begin
      nSplitOther := qAns['ID'];
    end;
    qAns.Next;
  end;
  qAns.Close;
  qAns.Free;
end;  // GetTransactionReferences().


procedure PrepareGeneralDCdata;
var
  sTempString : String;
begin
  sListFields := '(BatchID,DataRefID,';
  sListValues := 'VALUES ('+ sBatch +','+
    IntToStr(qryDC.FieldByName('DataRefID').AsInteger) +',';
  if not VarIsNull(qryDC['SequenceNo']) then begin
    sTempString := Trim(qryDC.FieldByName('SequenceNo').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'SequenceNo,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // SequenceNo.
  if not VarIsNull(qryDC['TxnDate']) then begin
    sListFields := sListFields+ 'TxnDate,';
    sListValues := sListValues+''''+Trim(qryDC.FieldByName('TxnDate').AsString)+
      ''',';
  end;  // TxnDate.
  if not VarIsNull(qryDC['Duration']) then begin
    sTempString := Trim(qryDC.FieldByName('Duration').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields+ 'Duration,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Duration.
  if not VarIsNull(qryDC['CallCode']) then begin
    sTempString := Trim(qryDC.FieldByName('CallCode').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields+ 'CallCode,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // CallCode.
  if not VarIsNull(qryDC['RateDescriptionID']) then begin
    // RateDescription is already stored, so copy the loaded value.
    sListFields := sListFields+ 'RateDescriptionID,';
    sListValues := sListValues +
      IntToStr(qryDC.FieldByName('RateDescriptionID').AsInteger) +',';
  end;  // RateDescription.
  if not VarIsNull(qryDC['FromDate']) then begin
    sListFields := sListFields+ 'FromDate,';
    sListValues := sListValues+''''+
      Trim(qryDC.FieldByName('FromDate').AsString) +''',';
  end;  // FromDate.
  if not VarIsNull(qryDC['ToDate']) then begin
    sListFields := sListFields+ 'ToDate,';
    sListValues := sListValues+''''+
      Trim(qryDC.FieldByName('ToDate').AsString) +''',';
  end;  // ToDate.
  FieldStringList.Add(sListFields);
  ValueStringList.Add(sListValues);
  sListFields := '';
  sListValues := '';

  if not VarIsNull(qryDC['StatusID']) and
    (qryDC.FieldByName('StatusID').AsInteger > 0) then begin
    sListFields := sListFields + 'StatusID,';
    sListValues := sListValues +
      IntToStr(qryDC.FieldByName('StatusID').AsInteger) +',';
  end;  // StatusID.
  if not VarIsNull(qryDC['OriginID']) and
    (qryDC.FieldByName('OriginID').AsInteger > 0) then begin
    sListFields := sListFields+ 'OriginID,';
    sListValues := sListValues +
      IntToStr(qryDC.FieldByName('OriginID').AsInteger) +',';
  end;  // OriginID.
  if not VarIsNull(qryDC['DestinationID']) and
    (qryDC.FieldByName('DestinationID').AsInteger > 0) then begin
    sListFields := sListFields+ 'DestinationID,';
    sListValues := sListValues +
      IntToStr(qryDC.FieldByName('DestinationID').AsInteger) +',';
  end;  // DestinationID.
  if not VarIsNull(qryDC['DialledNumberID']) and
    (qryDC.FieldByName('DialledNumberID').AsInteger > 0) then begin
    // Origin is already stored, so copy the loaded value.
    sListFields := sListFields+ 'DialledNumberID,';
    sListValues := sListValues +
      IntToStr(qryDC.FieldByName('DialledNumberID').AsInteger) +',';
  end;  // DialledNumberID.
  if not VarIsNull(qryDC['SupplierServiceCodeID']) and
    (qryDC.FieldByName('SupplierServiceCodeID').AsInteger > 0) then begin
    // Origin is already stored, so copy the loaded value.
    sListFields := sListFields+ 'SupplierServiceCodeID,';
    sListValues := sListValues +
      IntToStr(qryDC.FieldByName('SupplierServiceCodeID').AsInteger) +',';
  end;  // SupplierServiceCodeID.
end;  // PrepareGeneralDCdata.


procedure PrepareGeneralIDdata;
begin
  sListFields := sListFields + 'ServiceID,ServiceTypeID,';
  sListValues := sListValues +
    IntToStr(qryDC['SID']) +','+ IntToStr(qryDC['ServiceTypeID']) +',';
  if not VarIsNull(qryDC['PersonID']) then begin
    sListFields := sListFields + 'PersonID,';
    sListValues := sListValues + IntToStr(qryDC['PersonID']) +',';
  end;  // PersonID.
  if not VarIsNull(qryDC['CentreID']) then begin
    sListFields := sListFields + 'CentreID,';
    sListValues := sListValues +
      IntToStr(qryDC['CentreID']) +',';
  end;  // CentreID.
  if not VarIsNull(qryDC['ActivityID']) then begin
    sListFields := sListFields + 'ActivityID,';
    sListValues := sListValues +
      IntToStr(qryDC['ActivityID']) +',';
  end;  // ActivityID.
  // ElementID is recorded last in this SQL - read from tTransactionType.
  if not VarIsNull(qryDC['SubledgerID']) then begin
    sListFields := sListFields + 'SubledgerID,';
    sListValues := sListValues +
      IntToStr(qryDC['SubledgerID']) +',';
  end;  // SubledgerID.
  if not VarIsNull(qryDC['CustomerCentreID']) then begin
    sListFields := sListFields + 'CustomerCentreID,';
    sListValues := sListValues +
      IntToStr(qryDC['CustomerCentreID']) +',';
  end;  // CustomerCentreID.
  if not VarIsNull(qryDC['CustomerActivityID']) then begin
    sListFields := sListFields + 'CustomerActivityID,';
    sListValues := sListValues +
      IntToStr(qryDC['CustomerActivityID']) +',';
  end;  // CustomerActivityID.
  if not VarIsNull(qryDC['CustomerElementID']) then begin
    sListFields := sListFields + 'CustomerElementID,';
    sListValues := sListValues +
      IntToStr(qryDC['CustomerElementID']) +',';
  end;  // CustomerElementID.
  if not VarIsNull(qryDC['CustomerSubledgerID']) then begin
    sListFields := sListFields + 'CustomerSubledgerID,';
    sListValues := sListValues +
      IntToStr(qryDC['CustomerSubledgerID']) +',';
  end;  // CustomerSubledgerID.
  if not VarIsNull(qryDC['LocationID']) then begin
    sListFields := sListFields + 'LocationID,';
    sListValues := sListValues + IntToStr(qryDC['LocationID']) +',';
  end;  // LocationID.
  if not VarIsNull(qryDC['UpEnd']) then begin
    sListFields := sListFields+ 'UpEnd,';
    sListValues := sListValues + '''' +
      Trim(qryDC.FieldByName('UpEnd').AsString) +''',';
  end;  // UpEnd.
  if not VarIsNull(qryDC['UpMDF']) then begin
    sListFields := sListFields+ 'UpMDF,';
    sListValues := sListValues + '''' +
      Trim(qryDC.FieldByName('UpMDF').AsString) +''',';
  end;  // UpMDF.
  if not VarIsNull(qryDC['DownEnd']) then begin
    sListFields := sListFields+ 'DownEnd,';
    sListValues := sListValues + '''' +
      Trim(qryDC.FieldByName('DownEnd').AsString) +''',';
  end;  // DownEnd.
  if not VarIsNull(qryDC['DownMDF']) then begin
    sListFields := sListFields+ 'DownMDF,';
    sListValues := sListValues + '''' +
      Trim(qryDC.FieldByName('DownMDF').AsString) +''',';
    end;  // DownMDF.
  if (qryDC.FieldByName('Length').AsFloat > 0.00) then begin
    sListFields := sListFields + 'Length,';
    sListValues := sListValues + FloatToStr(qryDC['Length']) +',';
  end;  // CustomerSubledgerID.
  if (Length(sListFields) > 0) then begin
    FieldStringList.Add(sListFields);
    ValueStringList.Add(sListValues);
    sListFields := '';
    sListValues := '';
  end;  // if (Length(sListFields) > 0) then.
end;  // PrepareGeneralIDdata().


procedure PrepareGeneralVarData;
var
  sTempString : string;
begin
  if not VarIsNull(qryDC['Var01']) then begin
    sTempString := Trim(qryDC.FieldByName('Var01').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var01,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var01.
  if not VarIsNull(qryDC['Var02']) then begin
    sTempString := Trim(qryDC.FieldByName('Var02').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var02,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var02.
  if not VarIsNull(qryDC['Var03']) then begin
    sTempString := Trim(qryDC.FieldByName('Var03').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var03,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var03.
  if not VarIsNull(qryDC['Var04']) then begin
    sTempString := Trim(qryDC.FieldByName('Var04').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var04,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var04.
  if not VarIsNull(qryDC['Var05']) then begin
    sTempString := Trim(qryDC.FieldByName('Var05').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var05,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var05.
  if (Length(sListFields) > 0) then begin
    FieldStringList.Add(sListFields);
    ValueStringList.Add(sListValues);
    sListFields := '';
    sListValues := '';
  end;  // if (Length(sListFields) > 0) then.

  if not VarIsNull(qryDC['Var06']) then begin
    sTempString := Trim(qryDC.FieldByName('Var06').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var06,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var06.
  if not VarIsNull(qryDC['Var07']) then begin
    sTempString := Trim(qryDC.FieldByName('Var07').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var07,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var07.
  if not VarIsNull(qryDC['Var08']) then begin
    sTempString := Trim(qryDC.FieldByName('Var08').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var08,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var08.
  if not VarIsNull(qryDC['Var09']) then begin
    sTempString := Trim(qryDC.FieldByName('Var09').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var09,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var09.
  if not VarIsNull(qryDC['Var10']) then begin
    sTempString := Trim(qryDC.FieldByName('Var10').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var10,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var10.
  if (Length(sListFields) > 0) then begin
    FieldStringList.Add(sListFields);
    ValueStringList.Add(sListValues);
    sListFields := '';
    sListValues := '';
  end;  // if (Length(sListFields) > 0) then.

  if not VarIsNull(qryDC['Var11']) then begin
    sTempString := Trim(qryDC.FieldByName('Var11').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var11,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var11.
  if not VarIsNull(qryDC['Var12']) then begin
    sTempString := Trim(qryDC.FieldByName('Var12').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var12,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var12.
  if not VarIsNull(qryDC['Var13']) then begin
    sTempString := Trim(qryDC.FieldByName('Var13').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var13,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var13.
  if not VarIsNull(qryDC['Var14']) then begin
    sTempString := Trim(qryDC.FieldByName('Var14').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var14,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var14.
  if not VarIsNull(qryDC['Var15']) then begin
    sTempString := Trim(qryDC.FieldByName('Var15').AsString);
    if (Length(sTempString)>0) then begin
      sListFields := sListFields + 'Var15,';
      sListValues := sListValues + '''' + sTempString +''',';
    end;
  end;  // Var15.
  if (Length(sListFields) > 0) then begin
    FieldStringList.Add(sListFields);
    ValueStringList.Add(sListValues);
    sListFields := '';
    sListValues := '';
  end;  // if (Length(sListFields) > 0) then.
end;  // PrepareGeneralVarData().


procedure SaveTransaction(Const nThisBatch: LongInt;var lSuccessful: Boolean);
const
  BIGGESTCHUNK = 20000;               // Max num tDataCollection records
  BATCHTYPEID_VICTRACK_UNICOMMS = 664;// VicTrack Unified Communications.
var                                   // processed in a single SELECT statement.
  lUniComms : boolean;                // Set by BATCHTYPEID_VICTRACK_UNICOMMS.
  MessageForm : TMessagesForm;
  FullAmountExGST : Currency;
  FullGSTAmount : Currency;
  FullAmountIncGST : Currency;
  nListValues : Integer;
  sTempString : String;
  Progress : TProgressForm;
  nCount : Integer;
  nTotalCount : Integer;
  nBlock : Integer;
  Factor : Real;
  nTransSaved : Integer;
  nElement : Integer;
  sRevenueFlag : string;
  sThisServiceID : String;
  nSID : integer;
  nMaxDCRecords : LongInt;
  nDCRecordsProcessed : LongInt;
  nMaxBlock : integer;
  lUseChunks : boolean;
  nWhereIsAt : LongInt;
  nStrings : Integer;
  curTempAmt : Currency;

  nSoFar  : integer;
  curExGST : Currency;
  curIncGST : Currency;
  curGST : Currency;
  curExGSTTally  : real;
  curGSTTally : real;
  curIncGSTTally  : real;
  nPercentage : integer;
  lSplitAccount : boolean;
  lServiceIsNotSplit : boolean;

  lAbortProcess : boolean;
  LastRun : boolean;
  sCustomerID : string;
  nDataRefID : integer;
  nEndOfChunk : integer;
begin
//  nWhereIsAt := -1;
  nBatch := nThisBatch;
  sBatch := Trim(IntToStr(nBatch));
  nDCRecordsProcessed := 0;
  nTransactionRent  := 0;
  nTransactionCall  := 0;
  nTransactionOther := 0;
  nSplitRent  := 0;
  nSplitCall  := 0;
  nSplitOther := 0;
  MinDate := Date - 10000;      // Tolerate +/-10000 days in the billing dates
  MaxDate := Date + 10000;      // (+/-27 years) referred to "today".

  MessageForm := TMessagesForm.Create(MainForm);
  MessageForm.Caption := 'Writing transactions ...';
  MessageForm.Msg.Caption := 'Loading reference data ...';
  MessageForm.Show;
  // Open the progress form.
  Progress := TProgressForm.Create(MainForm);
  // Display a message to the user.
  Progress.Msg.Caption := 'Saving verified data ...';
  Progress.btnCancel.Enabled := False;
  Progress.btnCancel.Visible := False;
  Progress.ProcessWinMessages(MainForm);

  GetTransactionReferences;

  // Must gather the data from the tDataCollection table - it is best to
  // link in all of the required data now too in the same query.
  qryDC := TQuery.Create(MainForm);
  qryDC.DatabaseName := 'dbPPdata';

  // SPECIAL Start...
  // Unified Communications invoicing populates the "ServiceType" in
  //   tDataCollection to help processing of the invoice. Some values provide
  //   errant links to tServiceType by the following SQL statements. Therefore
  //   the link is modified for this BatchTypeID only.
  with qryDC do begin
    SQL.Add('SELECT BatchTypeID');
    SQL.Add('FROM tBatch');
    SQL.Add('WHERE ID = '+sBatch);
    Open;
  end;  // qryDC.
  lUniComms := (qryDC.FieldByName('BatchTypeID').AsInteger =
                BATCHTYPEID_VICTRACK_UNICOMMS);
  qryDC.Close;
  qryDC.SQL.Clear;
  // SPECIAL ...end

  MessageForm.Msg.Caption := 'Updating tOrigin reference data ...';
  Progress.ProcessWinMessages(MainForm);
  // Update the references first to speed it up.
  with qryDC do begin
    SQL.Add('INSERT INTO tOrigin');
    SQL.Add('(Description, Active)');
    SQL.Add('SELECT DISTINCT '+
      'tDataCollection.Origin AS Description, (1) AS "Active"');
    SQL.Add('FROM tDataCollection');
    SQL.Add('LEFT JOIN tOrigin '+
      'ON UPPER(tDataCollection.Origin) = UPPER(tOrigin.Description)');
    SQL.Add('WHERE tDataCollection.BatchID = '+sBatch);
    SQL.Add('AND tDataCollection.Origin IS NOT NULL');
    SQL.Add('AND tOrigin.ID IS NULL');
    ExecSQL;
    Close;
    MessageForm.Msg.Caption := 'Updating tRateDescription reference data ...';
    Progress.ProcessWinMessages(MainForm);
    SQL.Clear;
    SQL.Add('INSERT INTO tRateDescription');
    SQL.Add('(Description, Active)');
    SQL.Add('SELECT DISTINCT '+
      'tDataCollection.RateDescription AS Description, (1) AS "Active"');
    SQL.Add('FROM tDataCollection');
    SQL.Add('LEFT JOIN tRateDescription '+
      'ON UPPER(tDataCollection.RateDescription) = UPPER(tRateDescription.Description)');
    SQL.Add('WHERE tDataCollection.BatchID = '+sBatch);
    SQL.Add('AND tDataCollection.RateDescription IS NOT NULL');
    SQL.Add('AND tRateDescription.ID IS NULL');
    ExecSQL;
    Close;
    MessageForm.Msg.Caption := 'Updating tDialledNumber reference data ...';
    Progress.ProcessWinMessages(MainForm);
    SQL.Clear;
    SQL.Add('INSERT INTO tDialledNumber');
    SQL.Add('(Description, Active)');
    SQL.Add('SELECT DISTINCT tDataCollection.DialledNumber AS Description,'+
      ' (1) AS "Active"');
    SQL.Add('FROM tDataCollection');
    SQL.Add('LEFT JOIN tDialledNumber '+
      'ON UPPER(tDataCollection.DialledNumber) = UPPER(tDialledNumber.Description)');
    SQL.Add('WHERE tDataCollection.BatchID = '+sBatch);
    SQL.Add('AND tDataCollection.DialledNumber IS NOT NULL');
    SQL.Add('AND tDialledNumber.ID IS NULL');
    ExecSQL;
    Close;
    MessageForm.Msg.Caption := 'Updating tDestination reference data ...';
    Progress.ProcessWinMessages(MainForm);
    SQL.Clear;
    SQL.Add('INSERT INTO tDestination');
    SQL.Add('(Description, Active)');
    SQL.Add('SELECT DISTINCT '+
      'tDataCollection.Destination AS Description, (1) AS "Active"');
    SQL.Add('FROM tDataCollection');
    SQL.Add('LEFT JOIN tDestination '+
      'ON UPPER(tDataCollection.Destination) = UPPER(tDestination.Description)');
    SQL.Add('WHERE tDataCollection.BatchID = '+sBatch);
    SQL.Add('AND tDataCollection.Destination IS NOT NULL');
    SQL.Add('AND tDestination.ID IS NULL');
    ExecSQL;
    Close;
  end;  // qryDC.

  qryStringList := TStringList.Create;

  // Testing for split transactions.
  // NOTE: Entries with RevenueFlag set (0 or 1) may not be split services.
  lSuccessful := True;             // Set to false if something is wrong.
  with qryDC do begin
    MessageForm.Msg.Caption := 'Testing for split transactions...';
    Progress.ProcessWinMessages(MainForm);
    SQL.Clear;
    SQL.Add('SELECT COUNT(*) AS TotalCount');
    SQL.Add('FROM ((SELECT tDataCollection.*, tServiceID.ID AS SID,');
    if lUniComms then begin
      // Always ignore ServiceType for Unified Comms invoice transactions.
      SQL.Add(' tServiceType.Code');
    end
    else begin
      SQL.Add(' (CASE WHEN tDataCollection.ServiceType IS NULL THEN '+
         'tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code"');
    end;
    SQL.Add(' FROM (tDataCollection');
    SQL.Add(' LEFT JOIN tServiceID '+
      'ON tDataCollection.ServiceID = tServiceID.ServiceID)');
    SQL.Add(' LEFT JOIN tServiceType '+
    'ON tServiceID.ServiceTypeID = tServiceType.ID');
    SQL.Add(' WHERE tDataCollection.BatchID = '+sBatch);
    SQL.Add(' AND tDataCollection.RevenueFlag IS NULL');
    SQL.Add(' AND tServiceID.ServiceID IS NOT NULL) AS DC');
    SQL.Add('LEFT JOIN tServiceType ON DC.Code = tServiceType.Code)');
    SQL.Add('INNER JOIN tServiceSplit ON DC.SID = tServiceSplit.ServiceID');
    SQL.Add('WHERE tServiceType.SplitService = 1');
    SQL.Add('AND tServiceSplit.ServiceID IS NOT NULL');
    SQL.Add('AND tServiceSplit.Active = 1');
    Open;
    nMaxDCRecords := FieldByName('TotalCount').AsInteger;
    Close;
  end;  // qryDC.
  lAbortProcess := False;
  if (nMaxDCRecords > 0) then begin
    // Check that all split services are totalled to 100%.
    with qryDC do begin
      SQL.Clear;
      SQL.Add('SELECT DC1.*');
      SQL.Add('FROM (SELECT DC.SID, DC.ServiceID, Sum(RentPercentage) AS "Rent",'+
        ' Sum(CallPercentage) AS "Call", Sum(OtherPercentage) AS "Other"');
      SQL.Add('FROM ((SELECT DISTINCT tDataCollection.ServiceID,'+
        ' tServiceID.ID AS SID,');
      if lUniComms then begin
        // Always ignore ServiceType for Unified Comms invoice transactions.
        SQL.Add(' tServiceType.Code');
      end
      else begin
        SQL.Add(' (CASE WHEN tDataCollection.ServiceType IS NULL THEN '+
           'tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code"');
      end;
      SQL.Add(' FROM (tDataCollection');
      SQL.Add(' LEFT JOIN tServiceID '+
        'ON tDataCollection.ServiceID = tServiceID.ServiceID)');
      SQL.Add(' LEFT JOIN tServiceType '+
        'ON tServiceID.ServiceTypeID = tServiceType.ID');
      SQL.Add(' WHERE tDataCollection.BatchID = '+sBatch);
      SQL.Add(' AND tDataCollection.RevenueFlag IS NULL');
      SQL.Add(' AND tServiceID.ServiceID IS NOT NULL) AS DC');
      SQL.Add('LEFT JOIN tServiceType ON DC.Code = tServiceType.Code)');
      SQL.Add('INNER JOIN tServiceSplit ON DC.SID = tServiceSplit.ServiceID');
      SQL.Add('WHERE tServiceType.SplitService = 1');
      SQL.Add('AND tServiceSplit.ServiceID IS NOT NULL');
      SQL.Add('AND tServiceSplit.Active = 1');
      SQL.Add('GROUP BY DC.SID, DC.ServiceID) AS DC1');
      SQL.Add('WHERE DC1.Rent <> 100');
      SQL.Add('OR DC1.Call <> 100');
      SQL.Add('OR DC1.Other <> 100');
      Open;
    end;  // qryDC.
    if not qryDC.Eof then begin
      // tServiceSplit table incomplete - cannot proceed until data is fixed.
      lAbortProcess := True;
      // Tell the user what must be fixed.
      PrintQuery(qryDC, 'tSplitService - Errors in totals (expect 100%)');
    end;  // qryDC.Eof.
  end;

  if lAbortProcess then begin
    MessageDlg('Errors found in the tServiceSplit table. Refer to printout.',
      mtWarning, [mbOk], 0);
    MessageForm.Hide;
    MessageForm.Close;
    MessageForm.Free;
  end
  else begin
    // **************************************************************
    // **************************************************************
    // *********** EXPENSES FOR NON-SPLIT SERVICES ******************
    // **************************************************************
    // **************************************************************
    MessageForm.Msg.Caption := 'Saving Expenses transactions ...';
    Progress.ProcessWinMessages(MainForm);
    with qryStringList do begin
      Add('INSERT INTO tTransaction');
      Add('(DataRefID, SequenceNo, Duration, CallCode, BatchID, '+
        'TxnDate, AmountExGST, GSTAmount, AmountIncGST, FromDate, ToDate,');
      Add(' TransactionTypeID, StatusID, GSTFlag, ActivityID, '+
        'CentreID, ServiceID, SubledgerID, CustomerCentreID,');
      Add(' CustomerActivityID, CustomerElementID, '+
        'CustomerSubledgerID, LocationID, UpEnd, UpMDF, DownEnd, DownMDF, Length, '+
        'PersonID,');
      Add(' ServiceTypeID, RevenueFlag, ElementID, CustomerID, '+
        'DialledNumberID, OriginID, DestinationID, RateDescriptionID, Peak,');
      Add(' Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, '+
        'Var09, Var10, Var11, Var12, Var13, Var14, Var15, SupplierServiceCodeID)');
      Add('SELECT DC2.ID AS DataRefID, DC2.SequenceNo, DC2.Duration, '+
        'DC2.CallCode, DC2.BatchID,');
      Add(' (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE '+
        'SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + '+
        'SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL '+
        'THEN '''' ELSE '' '' + DC2.TxnTime END) END) AS "TxnDate",');
      Add(' DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,');
      Add(' (CASE DC2.FromDate WHEN NULL THEN NULL ELSE '+
        'SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + '+
        'SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",');
      Add(' (CASE DC2.ToDate WHEN NULL THEN NULL ELSE '+
        'SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + '+
        'SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",');
      Add(' DC2.TransactionTypeID, DC2.StatusID, '+
        '(CASE DC2.GSTFlag WHEN ''Y'' THEN 1 ELSE 0 END) AS "GSTFlag",');
      Add(' tServiceID.ActivityID, tServiceID.CentreID, '+
        'DC2.SID AS "ServiceID", tServiceID.SubledgerID, tServiceID.CustomerCentreID,');
      Add(' tServiceID.CustomerActivityID, '+
        'tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, '+
        'tServiceID.LocationID, tServiceID.UpEnd,');
      Add(' tServiceID.UpMDF, tServiceID.DownEnd, '+
            'tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, '+
        'DC2.ServiceTypeID,');
      Add(' (0) AS "RevenueFlag", DC2.ExpenseElementID AS "ElementID",');
      Add(' tGroup.CustomerID, '+
        'tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", '+
        'tDestination.ID as "DestinationID", '+
        'tRateDescription.ID AS "RateDescriptionID", DC2.Peak,');
      Add(' DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,');
      Add(' DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,');
      Add(' DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15,'+
        ' DC2.SupplierServiceCodeID');
      Add('FROM (((((SELECT DC1.*');
      Add('FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", '+
        'tServiceType.ExpenseElementID, tServiceType.RevenueElementID');
      Add('FROM (SELECT tDataCollection.*, ');
      if lUniComms then begin
        // Always ignore ServiceType for Unified Comms invoice transactions.
        Add(' tServiceType.Code, ');
      end
      else begin
        Add(' (CASE WHEN tDataCollection.ServiceType IS NULL THEN '+
         'tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", ');
      end;
      Add(' tServiceType.SplitService,');
      Add(' tServiceSplit.ServiceID AS SplitServiceID, '+
        'tServiceSplit.Active, tServiceID.ID AS "SID"');
      Add('FROM ((tDataCollection');
      Add('LEFT JOIN tServiceID '+
        'ON tDataCollection.ServiceID = tServiceID.ServiceID)');
      Add('LEFT JOIN tServiceType '+
        'ON tServiceID.ServiceTypeID = tServiceType.ID)');
      Add('LEFT JOIN tServiceSplit '+
        'ON tServiceID.ID = tServiceSplit.ServiceID');
      Add('WHERE tDataCollection.BatchID = '+sBatch);
      Add('AND (tDataCollection.RevenueFlag IS NULL '+
        'OR tDataCollection.RevenueFlag = 0)');
      Add('AND tServiceID.ServiceID IS NOT NULL) AS DC');
      Add('LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1');
      Add('WHERE (DC1.SplitService <> 1 '+
        'OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL '+
        'OR DC1.Active <> 1)))) AS DC2');
      Add('LEFT JOIN (tServiceID LEFT JOIN (tCentre '+
      'LEFT JOIN (tDepartment LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)');
      Add('ON tCentre.DepartmentID = tDepartment.ID) '+
        'ON tServiceID.CustomerCentreID = tCentre.ID) ON DC2.SID = tServiceID.ID)');
      Add('LEFT JOIN tDialledNumber '+
        'ON DC2.DialledNumber = tDialledNumber.Description)');
      Add('LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)');
      Add('LEFT JOIN tDestination '+
        'ON DC2.Destination = tDestination.Description)');
      Add('LEFT JOIN tRateDescription '+
        'ON DC2.RateDescription = tRateDescription.Description');
      Add('WHERE DC2.ExpenseElementID IS NOT NULL');
      Add('AND DC2.ExpenseElementID > 0');
    end;
    with qryDC do begin
      SQL := qryStringList;
      ExecSQL;
      Close;
    end;  // qryDC.
    // **************************************************************
    // **************************************************************
    // *********** REVENUE FOR NON-SPLIT SERVICES *******************
    // **************************************************************
    // **************************************************************
    MessageForm.Msg.Caption := 'Saving Revenue transactions...';
    Progress.ProcessWinMessages(MainForm);
    nCount := qryStringList.Count - 1;
    qryStringList.Strings[15] := '(1) AS "RevenueFlag", '+
      'DC2.RevenueElementID AS "ElementID",';
    qryStringList.Strings[nCount-11] := 'AND (tDataCollection.RevenueFlag '+
      'IS NULL OR tDataCollection.RevenueFlag = 1)';
    qryStringList.Strings[nCount-1] := 'WHERE DC2.RevenueElementID IS NOT NULL';
    qryStringList.Strings[nCount] := 'AND DC2.RevenueElementID > 0';
    with qryDC do begin
      SQL := qryStringList;
      ExecSQL;
      Close;
    end;  // qryDC.

    // **************************************************************
    // **************************************************************
    // ******************* SPLIT SERVICES ***************************
    // **************************************************************
    // **************************************************************
    Progress.ProcessWinMessages(MainForm);
    if (nMaxDCRecords = 0) then begin
      MessageForm.Hide;
      MessageForm.Close;
      MessageForm.Free;
      Progress.ProcessWinMessages(MainForm);
    end
    else begin
      lUseChunks := (nMaxDCRecords > BIGGESTCHUNK);
      FieldStringList := TStringList.Create;
      ValueStringList := TStringList.Create;

      // Now prepare to load the data.
      with qryStringList do begin
        Clear;
        if lUseChunks then begin
          Add('SELECT TOP '+IntToStr(BIGGESTCHUNK)+
            ' DC2.ID AS "DataRefID",');                                   // 0.
        end
        else begin
          Add('SELECT DC2.ID AS "DataRefID",');                           //(0).
        end;
        Add(' DC2.SequenceNo, DC2.Duration, DC2.CallCode, '+
          'DC2.BatchID, DC2.ServiceID, DC2.SID,');                        // 1.
        Add(' (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE '+
          'SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + '+
            'SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL '+
            'THEN '''' ELSE '' '' + DC2.TxnTime END) END) AS "TxnDate",');// 2.
        Add(' DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,');//3.
        Add(' (CASE DC2.FromDate WHEN NULL THEN NULL ELSE '+
          'SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + '+
          'SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",');             // 4.
        Add(' (CASE DC2.ToDate WHEN NULL THEN NULL ELSE '+
          'SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + '+
          'SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",');                 // 5.
        Add(' DC2.TransactionTypeID, DC2.StatusID, '+
          '(CASE DC2.GSTFlag WHEN ''Y'' THEN 1 ELSE 0 END) AS "GSTFlag",');// 6.
        Add(' tServiceID.ActivityID, tServiceID.CentreID, '+
          'tServiceID.SubledgerID, tServiceID.CustomerCentreID,');        // 7.
        Add(' tServiceID.CustomerActivityID, '+
          'tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, '+
          'tServiceID.LocationID, tServiceID.UpEnd,');                    // 8.
        Add(' tServiceID.UpMDF, tServiceID.DownEnd, '+
          'tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, '+
          'DC2.ServiceTypeID,');                                          // 9.
        Add(' tTransactionGroup.Description AS "TransactionGroup", '+
          'DC2.RevenueElementID, DC2.ExpenseElementID,');                 // 10.
        Add(' tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID",'+
          ' tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", '+
          'tRateDescription.ID AS "RateDescriptionID", DC2.Peak,');       // 11.
        Add(' DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,');   // 12.
        Add(' DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,');   // 13.
        Add(' DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15,'+
          ' DC2.SupplierServiceCodeID');                                  // 14.
        Add('FROM ((((((((SELECT DC1.*');                                 // 15.
        Add('FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", '+
          'tServiceType.ExpenseElementID, tServiceType.RevenueElementID');// 16.
        if lUniComms then begin
          // Always ignore ServiceType for Unified Comms invoice transactions.
          Add('FROM (SELECT tDataCollection.*, ');                        // 17.
          Add(' tServiceType.Code,  tServiceType.SplitService,');         // 18.
        end
        else begin
          Add('FROM (SELECT tDataCollection.*, '+
            '(CASE WHEN tDataCollection.ServiceType IS NULL THEN');       // 17.
          Add(' tServiceType.Code ELSE tDataCollection.ServiceType END)'+
            ' AS "Code", tServiceType.SplitService,');                    // 18.
        end;  // lUniComms.











        Add(' tServiceSplit.ServiceID AS SplitServiceID, '+
          'tServiceSplit.Active, tServiceID.ID AS "SID"');                // 19.
        Add('FROM ((tDataCollection');                                    // 20.
        Add('LEFT JOIN tServiceID '+
          'ON tDataCollection.ServiceID = tServiceID.ServiceID)');        // 21.
        Add('LEFT JOIN tServiceType '+
          'ON tServiceID.ServiceTypeID = tServiceType.ID)');              // 22.
        Add('LEFT JOIN tServiceSplit '+
          'ON tServiceID.ID = tServiceSplit.ServiceID');                  // 23.
//        Add('WHERE tDataCollection.BatchID = '+sBatch);         // 24.
// The following line is redundant (checked in a CriticalError test) and
// it slows the Query - removed:
//        Add('AND tServiceID.ServiceID IS NOT NULL) AS DC');     // 25.
        Add('WHERE tDataCollection.BatchID = '+sBatch);                   // 24.
        Add('AND tDataCollection.RevenueFlag IS NULL');                   // 25.
        Add('AND tServiceID.ServiceID IS NOT NULL) AS DC');               // 26.
        Add('LEFT JOIN tServiceType '+
          'ON DC.Code = tServiceType.Code) AS DC1');                      // 27.
        Add('WHERE (DC1.SplitService = 1 '+
          'AND (DC1.SplitServiceID IS NOT NULL AND DC1.Active = 1)))) AS DC2');// 28.
        Add('LEFT JOIN (tServiceID LEFT JOIN (tCentre '+
          'LEFT JOIN (tDepartment LEFT JOIN tGroup '+
          'ON tDepartment.GroupID = tGroup.ID)');                         // 29.
        Add('ON tCentre.DepartmentID = tDepartment.ID) '+                 // 30.
          'ON tServiceID.CustomerCentreID = tCentre.ID) ON DC2.SID = tServiceID.ID)');
        Add('LEFT JOIN tDialledNumber '+
          'ON DC2.DialledNumber = tDialledNumber.Description)');          // 31.
        Add('LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)');    // 32.
        Add('LEFT JOIN tDestination '+
          'ON DC2.Destination = tDestination.Description)');              // 33.
        Add('LEFT JOIN tRateDescription '+
          'ON DC2.RateDescription = tRateDescription.Description)');      // 34.
        Add('LEFT JOIN tTransactionType '+
          'ON DC2.TransactionTypeID = tTransactionType.ID)');             // 35.
        Add('LEFT JOIN tTransactionGroup '+
          'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID');// 36.
        Add('ORDER BY DC2.ID');                                           // 37.
      end;  // qryStringList.
      // Define where the WHERE/ORDER statements start for Chunk processing.
      nWhereIsAt := 37;                          // Used in loop IF chunks read.
//      nWhereIsAt := 36;                        // Used in loop IF chunks read.
      MessageForm.Msg.Caption := 'Loading invoices for split service funding...';
      Progress.ProcessWinMessages(MainForm);

      with qryDC do begin
        Close;
        SQL := qryStringList;
        Open;
      end;  // qryDC.
      MessageForm.Hide;
      MessageForm.Close;
      MessageForm.Free;
      if qryDC.Eof and qryDC.Bof then begin
        // No data returned.
        lSuccessful := False;
      end
      else begin
        // Open the progress form.
        Progress.Show;
        Progress.ProcessWinMessages(MainForm);
        nCount := 0;
        nBlock := 0;
        if (nMaxDCRecords > 999) then begin
          Factor := 0.0100;        // Count to 100 every 10000 records.
          nMaxBlock := 100;        // Update the screen every 100 records.
        end
        else if (nMaxDCRecords > 99) then begin
          Factor := 0.1000;        // Count to 100 every 1000 records.
          nMaxBlock := 10;         // Update the screen every 10 records.
        end
        else begin
          Factor := 1.0000;        // Count to 100 every 100 records.
          nMaxBlock := 1;          // Update the screen every record.
        end;
        nTotalCount := 0;
        // Create the query for retrieving service split details.
        qrySS := TQuery.Create(MainForm);
        qrySS.DatabaseName := 'dbPPdata';

        // Create the query that will update the tTransaction table.
        qryTT := TQuery.Create(MainForm);
        qryTT.DatabaseName := 'dbPPdata';

        // Save the service we are starting with.
        sThisServiceID := Trim(qryDC.FieldByName('ServiceID').AsString);
        nSID := 0;
        lAbortProcess := False;
        // PROCESS ALL RECORDS IN tDataCollection FOR THIS BATCH.
        while not (qryDC.Eof or lAbortProcess) do begin
          // Flush the string lists used to build the SQL statement.
          qryStringList.Clear;
          FieldStringList.Clear;
          ValueStringList.Clear;

          if (qryDC.FieldByName('SID').AsInteger <> nSID) then begin
            // Update the display of the current service being processed.
            sThisServiceID := Trim(qryDC.FieldByName('ServiceID').AsString);
            nSID := qryDC.FieldByName('SID').AsInteger;
            Progress.Caption := 'Splitting: ' + sThisServiceID;
            with qrySS do begin
              Close;
              SQL.Clear;
              SQL.Add('SELECT * FROM tServiceSplit');
              SQL.Add('WHERE ServiceID = '+IntToStr(nSID));
              Open;
              Progress.ProcessWinMessages(MainForm);
              lServiceIsNotSplit := (Bof and Eof);
            end;  // qrySS.
          end
          else if qrySS.Active then begin
            // Same service but charges are split, so return to first split entry.
            qrySS.First;
            lServiceIsNotSplit := (qrySS.Bof and qrySS.Eof);
          end
          else begin
            lServiceIsNotSplit := True;
          end;
          // ******************************************************************
          Progress.ProcessWinMessages(MainForm);
          PrepareGeneralDCdata;        // Bulk DataCollection data (rest at end).
          // ******************************************************************
          PrepareGeneralIDdata;        // ServiceID references.
          // ******************************************************************
          PrepareGeneralVarData;       // Var_ and Var_Description entries.
          // ******************************************************************
          // Final entries are the variables between Expenditure and Revenue.
          // Amounts are here too a/c SplitService requirements.
          // Start final entries.
          FullAmountExGST := qryDC.FieldByName('AmountExGST').AsCurrency;
          FullGSTAmount := qryDC.FieldByName('GSTAmount').AsCurrency;
          FullAmountIncGST := qryDC.FieldByName('AmountIncGST').AsCurrency;

          // Initialise variables used if the amount is to be split using
          // the "repeat...until" statement.
          nSoFar  := 0;
          curExGST := 0.00;
          curGST := 0.00;
          curIncGST := 0.00;
          curExGSTTally := 0.00;
          curGSTTally := 0.00;
          curIncGSTTally := 0.00;
          nPercentage := 100;
          nTransSaved := 0;
          lSplitAccount := True;
          nDataRefID := qryDC.FieldByName('DataRefID').AsInteger;
          // Repeat for all transaction types and any applicable splits of revenue.
          repeat
            sListFields := '';
            sListValues := '';
            Inc(nTransSaved);
            sListFields := sListFields+ 'GSTFlag,';
            sListValues := sListValues + IntToStr(qryDC['GSTFlag']) + ',';
            if (nTransSaved = 1) or lServiceIsNotSplit then begin
              // This is a regular transaction. Test for CustomerID.
              if not VarIsNull(qryDC['CustomerID']) then begin
                sListFields := sListFields + 'CustomerID,';
                sListValues := sListValues + IntToStr(qryDC['CustomerID']) +',';
              end;  // CustomerID.
              if not VarIsNull(qryDC['TransactionTypeID']) then begin
                sListFields := sListFields+ 'TransactionTypeID,';
                sListValues := sListValues+ IntToStr(qryDC['TransactionTypeID'])+',';
              end;  // TransactionType.
              // AmountExGST, GSTAmount, AmountIncGST.
              sListFields := sListFields+ 'AmountExGST,GSTAmount,AmountIncGST,';
//            sListValues := sListValues + Curr2Str(FullAmountExGST) +','+
//              Curr2Str(FullGSTAmount) +','+
//              Curr2Str(FullAmountIncGST) +',';
              sListValues := sListValues + CurrToStr(FullAmountExGST) +','+
              CurrToStr(FullGSTAmount) +','+
              CurrToStr(FullAmountIncGST) +',';
              lSplitAccount := (nTransSaved = 1);       // True for first pass only.
            end  // if (nTransSaved = 1) or lServiceIsNotSplit then.
            else begin
              // Revenue for this service's charge is to be split between customers.
              // Add the type of transaction (and remember the percentage value).
              sTempString := UpperCase(Trim(
                qryDC.FieldByName('TransactionGroup').AsString));
              if (Length(sTempString)>0) then begin
                sListFields := sListFields+ 'TransactionTypeID,';

                if (sTempString = 'RENT') then begin
                  sListValues := sListValues + IntToStr(nSplitRent) +',';
                  // Set the value of nPercentage (splits).
                  while not qrySS.eof and
                    (qrySS.FieldByName('RentPercentage').AsInteger = 0) do begin
                    qrySS.Next;
                  end;  // while not qrySS.eof and..do.
                  if not qrySS.Eof or (not VarIsNull(qrySS['CustomerID']) and
                    (StrToInt(sCustomerID) <>
                    qrySS.FieldByName('CustomerID').AsInteger)) then begin
                     nPercentage := qrySS.FieldByName('RentPercentage').AsInteger;
                     if not VarIsNull(qrySS['CustomerID']) then
                      sCustomerID := IntToStr(qrySS.FieldByName('CustomerID').AsInteger)
                    else
                      sCustomerID := '';
                  end;
                  // Move to the next valid split.
                  qrySS.Next;
                  while not qrySS.eof and
                    (qrySS.FieldByName('RentPercentage').AsInteger = 0) do begin
                    qrySS.Next;
                  end;  // while not qrySS.eof and..do.
                  if qrySS.eof then begin
                    nPercentage := (1000 - nSoFar) div 10;
                    lSplitAccount := False;
                  end;  // if qrySS.eof then..else.
                end  // if (sTempString = 'RENT') then.

                else if (sTempString = 'CALL') then begin
                  sListValues := sListValues + IntToStr(nSplitCall) +',';
                  while not qrySS.eof and
                    (qrySS.FieldByName('CallPercentage').AsInteger = 0) do begin
                    qrySS.Next;
                  end;  // while not qrySS.eof and..do.
                  if not qrySS.Eof or (not VarIsNull(qrySS['CustomerID']) and
                    (StrToInt(sCustomerID) <>
                    qrySS.FieldByName('CustomerID').AsInteger)) then begin
                    nPercentage := qrySS.FieldByName('CallPercentage').AsInteger;
                    if not VarIsNull(qrySS['CustomerID']) then
                      sCustomerID := IntToStr(qrySS.FieldByName('CustomerID').AsInteger)
                    else
                      sCustomerID := '';
                  end;
                  // Move to the next valid split.
                  qrySS.Next;
                  while not qrySS.eof and
                    (qrySS.FieldByName('CallPercentage').AsInteger = 0) do begin
                    qrySS.Next;
                  end;  // while not qrySS.eof and..do.
                  if qrySS.eof then begin
                    nPercentage := (1000 - nSoFar) div 10;
                    lSplitAccount := False;
                  end;  // if qrySS.eof then..else.
                end  // else if (sTempString = 'CALL') then.

                else if (sTempString = 'OTHER') then begin
                  sListValues := sListValues + IntToStr(nSplitOther) +',';
                  while not qrySS.eof and
                    (qrySS.FieldByName('OtherPercentage').AsInteger = 0) do begin
                    qrySS.Next;
                  end;  // while not qrySS.eof and..do.
                  if not qrySS.Eof or (not VarIsNull(qrySS['CustomerID']) and
                    (StrToInt(sCustomerID) <>
                    qrySS.FieldByName('CustomerID').AsInteger)) then begin
                    nPercentage := qrySS.FieldByName('OtherPercentage').AsInteger;
                    if not VarIsNull(qrySS['CustomerID']) then
                      sCustomerID := IntToStr(qrySS.FieldByName('CustomerID').AsInteger)
                    else
                      sCustomerID := '';
                  end;
                  // Move to the next valid split.
                  qrySS.Next;
                  while not qrySS.eof and
                    (qrySS.FieldByName('OtherPercentage').AsInteger = 0) do begin
                    qrySS.Next;
                  end;  // while not qrySS.eof and..do.
                  if qrySS.eof then begin
                    nPercentage := (1000 - nSoFar) div 10;
                    lSplitAccount := False;
                  end;  // if qrySS.eof then..else.
                end
                else begin
                  sCustomerID := '';
                end;  // else if (sTempString = 'OTHER') then.
              end;  // TransactionType.

              // Test for CustomerID (reference contained in tServiceSplit).
              if (Length(sCustomerID) > 0) then begin
                sListFields := sListFields + 'CustomerID,';
                sListValues := sListValues + sCustomerID +',';
              end
              else if
              not VarIsNull(qryDC['CustomerID'])then begin
                sListFields := sListFields + 'CustomerID,';
                sListValues := sListValues + IntToStr(qryDC['CustomerID']) +',';
              end;  // CustomerID.

              // Note: Rates in the tServiceSplit table range 1..100 and therefore
              // figures must be divided by 100(%) to get the correct values.
              if nSoFar < 1000 then begin
                // Charges to be further split.
//                curExGST := Round( FullAmountExGST * nPercentage ) / 100.00;
                curExGST := FullAmountExGST * nPercentage / 100.00;
//                curExGSTTally := curSum(curExGSTTally, curExGST);
                curExGSTTally := curExGSTTally + curExGST;
//                curIncGST := Round( FullAmountIncGST * nPercentage ) / 100.00;
                curIncGST := FullAmountIncGST * nPercentage / 100.00;
//                curIncGSTTally := curSum(curIncGSTTally, curIncGST);
                curIncGSTTally := curIncGSTTally + curIncGST;
                // GST is the difference between the two calculated values.
//                curGST := curFormat(curIncGST - curExGST);
                curGST := curIncGST - curExGST;
//                curGSTTally := curSum(curGSTTally, curGST);
                curGSTTally := curGSTTally + curGST;

                nSoFar := nSoFar + (nPercentage * 10);
                if (nSoFar = 1000) then begin
                  // Last split, make sure that no Cents have been dropped off.
//                  curExGST := curFormat(
//                    curSum(curExGST, FullAmountExGST) -curExGSTTally);
//                  curIncGST := curFormat(
//                    curSum(curIncGST, FullAmountIncGST) -curIncGSTTally);
//                  curGST := curFormat(
//                    curSum(curGST, FullGSTAmount) - curGSTTally);
                  curExGST := curExGST + FullAmountExGST - curExGSTTally;
                  curIncGST := curIncGST + FullAmountIncGST - curIncGSTTally;
                  curGST := curGST + FullGSTAmount - curGSTTally;
                  lSplitAccount := False;  // Finished splitting this transaction.
                end
                else begin
                  // Move to the next entry in tServiceSplit.
                  qrySS.Next;
                end;  // if (nSoFar = 1000) then.
              end; // if nSoFar < 1000 then.
              // AmountExGST, GSTAmount, AmountIncGST.
              sListFields := sListFields+ 'AmountExGST,GSTAmount,AmountIncGST,';
//              sListValues := sListValues + Curr2Str(curExGST) +','+
//                Curr2Str(curGST) +','+ Curr2Str(curIncGST) +',';
              sListValues := sListValues + CurrToStr(curExGST) +','+
                CurrToStr(curGST) +','+ CurrToStr(curIncGST) +',';
            end;  // if (nTransSaved = 1) or lServiceIsNotSplit then..else.
            // Final Test - whether the transaction should be created at all.
            if (nTransSaved = 1) then begin
              nElement := qryDC.FieldByName('ExpenseElementID').AsInteger;
              sRevenueFlag := '0';                                     // FALSE.
            end
            else begin
              nElement := qryDC.FieldByName('RevenueElementID').AsInteger;
              sRevenueFlag := '1';                                     // TRUE.
            end;  // if (nTransSaved = 1) then..else.
            if (nElement > 0) then begin
              // ESSENTIAL COMPONENT - proceed with saving the entry.
              sListFields := sListFields + 'ElementID,';
              sListValues := sListValues +
                IntToStr(nElement) +',';
              // NOTE LAST ENTRY OMITS TRAILING COMMA !!
              sListFields := sListFields + 'RevenueFlag)';
              sListValues := sListValues + sRevenueFlag + ')';

              // Close off the SQL statements.
              qryStringList.Clear;
              qryStringList.Add('INSERT INTO tTransaction');
              for nListValues := 0 to FieldStringList.Count-1 do begin
                qryStringList.Add(FieldStringList[nListValues]);
              end;
              qryStringList.Add(sListFields);
              for nListValues := 0 to ValueStringList.Count-1 do begin
                qryStringList.Add(ValueStringList[nListValues]);
              end;
              qryStringList.Add(sListValues);
              // Update and execute the query.
              qryTT.Close;
              qryTT.SQL := qryStringList;
              qryTT.ExecSQL;
            end;  // if (nElement > 0) then.
            sListFields := '';
            sListValues := '';
          until lAbortProcess or not lSplitAccount;
          // End ServiceID references - tServiceID.

          // Only process the first entry-appears once for every entry of
          // ServiceID found in tSplitService.
          while not qryDC.Eof and (qryDC.FieldByName('DataRefID').AsInteger =
            nDataRefID) do begin
            qryDC.Next;

            Inc(nDCRecordsProcessed);
            Inc(nCount);
            Inc(nTotalCount);
            Inc(nBlock);
          end;
          if (nBlock=nMaxBlock) and not lAbortProcess then begin
            Progress.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.
            Progress.ProgressBar1.Position := Trunc(nCount * Factor);
            Progress.Msg.Caption := 'Processing (transaction: '+
              IntToStr(nTotalCount)+')...';
          end;  // nBlock=nMaxBlock.
          Progress.ProcessWinMessages(MainForm);

          if qryDC.Eof and lUseChunks and (nDCRecordsProcessed < nMaxDCRecords) and
            not lAbortProcess then begin
            // Read another chunk of the tDataCollection table.
            qryDC.Close;
            // Copy the SQL statement into qryStringList.
            qryStringList.Text := qryDC.SQL.Text;
            qryStringList.Strings[0] := 'SELECT DC2.ID AS "DataRefID",';
            nEndOfChunk := nDataRefID;
            LastRun := False;
            // Repeat until data found - a chunk may have been copied to tAdjustment.
            repeat
              qryDC.Close;
              // Delete any Where and Order By statements at the end of
              // the SQL statement above - however many lines there are.
              if (nWhereIsAt > 0) then        // Set above in the first SQL of loop.
              repeat
                qryStringList.Delete(nWhereIsAt);
              until (qryStringList.Count = nWhereIsAt);
              // Now add the new Where and Order By statements.
              qryStringList.Add('WHERE DC2.ID > ' + IntToStr(nDataRefID));
              // and make sure that we don't miss the end of the data a/c adjustments.
              if ((nDCRecordsProcessed + BIGGESTCHUNK) < (nMaxDCRecords+1000)) then begin
                Inc(nEndOfChunk, BIGGESTCHUNK);
                qryStringList.Add('AND DC2.ID < ' + IntToStr(nEndOfChunk + 1));
              end
              else begin
                LastRun := True;
              end;  // more than the biggest chunk remains.
              qryStringList.Add('ORDER BY DC2.ID');
              qryDC.SQL := qryStringList;
              Progress.Msg.Caption := 'Loading...';
              qryDC.Open;
              Progress.Msg.Caption := 'Processing (transaction: '+
                IntToStr(nTotalCount)+')...';
            until not qryDC.Eof or LastRun
          end;  // if qryDC.Eof & lUseChunks & nDCRecordsProcessed<nMaxDCRecords then.
        end;  // while not qryDC.Eof do.
        lSuccessful := not lAbortProcess;
        Progress.Close;                  // All done, remove progressbar.
        Progress.Free;
        qryDC.Close;
        qrySS.Close;
        qrySS.Free;
        qryTT.Close;
        qryTT.Free;
      end;  // if qryDC.Eof and qry.Bof then..else.
    end;  // if (nMaxDCRecords > 0) then begin.
    if lSuccessful then begin
      // Update the current rent & calls in the tServiceID table.
      UpdateCurrentRentCalls(nBatch);
      Record_DataTaken(nBatch);
      // Remove transactions from the tDataCollection table.
      DeleteTransactions('tDataCollection', nBatch, '', '',
        '=', '', lSuccessful);
      UpdateBatchStatus(nBatch, MainForm.BATCH_STATUS_TRANSNS);
    end
    else begin
      // Remove transactions from the tTransaction table.
      DeleteTransactions('tTransaction', nBatch, '', '',
        '=', '', lSuccessful);
      UpdateBatchStatus(nBatch, MainForm.BATCH_STATUS_CPASS);
    end;  // if lSuccessful then begin..else.
  end;  // not lAbortProcess.
  qryDC.Free;
  qryStringList.Free;
  ValueStringList.Free;
end;  // procedure SaveTransaction().

end.