unit TelmaxUniComms;

interface

uses
  Messages, Classes, SysUtils, Graphics, Dialogs,
  Db, DbTables;

{
  // The following procedures are for use within this unit only and are
  // therefore commented out of this declaration section.
  function GetSupplierCodeID(const sSupplierCode : string) : string;
  function GetReferences(const nBatchNumber : LongInt) : boolean;
  procedure GetCustomerID(const sLastServiceID:string;var sCustomerID:string);

  procedure GetUniCCalls(
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const FName : string;
           var lReturnCode: Boolean );
  procedure GetUniCRent(
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const FName : string;
           var lReturnCode: Boolean );
  procedure GetUniCSwitchboard(
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const FName : string;
           var lReturnCode: Boolean );
}
  procedure UniCommsInvoice(nBatch: LongInt;
    const dStartDate, dEndDate: TDateTime;
    const BatchDescription, sDirectory : string );

implementation

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

const
  TEC_DEFAULT: string = '57';         // Default TEC value - used in Rent only.
  SFC_DEFAULT: string = '79';         // Default SFC value - used in Rent only.
  VRT_DEFAULT: string = '2846';       // Use for Vacant phones & invalid ServiceTypes.
  UC_SYSTEMID: string = '760';        // Unicomms SystemID -
var
  sSupplierID: string;                // Set in GetReferences().
  sRentTransactionGroupID : string;   // Set in GetReferences().
  sSystemID : string;                 // Set in GetReferences().
  qryTelmax : TQuery;                 // Set in BillingInput().
  sRentTransactionCodeID : string;    // Set in GetReferences().
  sServiceTransactionCodeID : string; // Set in GetReferences().
  sRentTransactionDesc : string;      // Set in GetReferences().
  sServiceTransactionDesc : string;   // Set in GetReferences().
  sCallCodeID : string;               // Set in GetReferences().
  sSupplyCallCodeID : string;         // Set in GetReferences().
  qCustID : TQuery;                   // Used in GetCustomerID().
  slCust : TStringList;               // Used in GetCustomerID().
  sVRTID : string;
  fGSTRate : Real;                    // GST Rate for Calcs-read from MainForm.
  sGSTRate : string;                  // fGSTRate as String-for SQL statements.
  qryServCode : TQuery;               // Used in GetSupplierCodeID().
  sSupplierDefaultTECcodeID : string; // Default - set in GetCustomerID().
  sSupplierDefaultSFCcodeID : string; // Default - set in GetCustomerID().

  curValueDefaultTEC : currency;      // Used in GetReferences().
  curValueDefaultSFC : currency;      // Used in GetReferences().

  sSWEx, sSWGST, sSWInc, sSWGSTflag : string;      // Switchboard.
  sVSWEx, sVSWGST, sVSWInc, sVSWGSTflag : string;  // Switchboard (VRT).


function GetSupplierCodeID(const sSupplierCode : string) : string;
begin
  // Note: qryServCode is initiated and SQL defined in GetReferences().
  qryServCode.Close;
  qryServCode.SQL.Strings[4] :=
    '       tSupplierServiceCode.Code LIKE '''+sSupplierCode+'''';        // 4.
  qryServCode.Open;
  if qryServCode.Eof then
    Result := '0'
  else
    Result := IntToStr(qryServCode.FieldByName('SupplierCodeID').AsInteger);
  qryServCode.Close;
end;  // GetSupplierCodeID.


function GetReferences(const nBatchNumber : LongInt) : boolean;
var
  lSuccessful : boolean;
  qryTemp : TQuery;
  qryRef  : TQuery;
  curValue,curValueGST : currency;
  sErrorTxt : string;
begin
  // initialise the Static variables defined as Private for this form.
  fGSTRate := MAINFORM.GST_RATE;
  Str(fGSTRate:6:4, sGSTRate);
  lSuccessful := True;
  sErrorTxt := '';
  qryTelmax := TQuery.Create(MainForm);
  qryTelmax.DatabaseName := 'dbPPdata';
  with qryTelmax do begin
    SQL.Add('SELECT id');
    SQL.Add('FROM tTransactionGroup');
    SQL.Add('WHERE Description LIKE ''RENT''');
    Open;
    sRentTransactionGroupID := IntToStr(FieldByName('ID').AsInteger);
    Close;
    SQL.Clear;
    SQL.Add('SELECT tSystem.id');
    SQL.Add('FROM tSystem');
    SQL.Add('RIGHT JOIN tBatch ON tSystem.BatchTypeID = tBatch.BatchTypeID');
    SQL.Add('WHERE tBatch.ID = '+IntToStr(nBatchNumber));
    Open;
    sSystemID := IntToStr(FieldByName('ID').AsInteger);
    Close;
    SQL.Clear;
    SQL.Add('SELECT id');
    SQL.Add('FROM tSupplier');
    SQL.Add('WHERE SupplierName LIKE ''VICTRACK%''');
    Open;
  end;  // qryTelmax.
  if qryTelmax.Eof then begin
    lSuccessful := False;
    sErrorTxt := sErrorTxt + 'VICTRACK not found in tSupplier'+#13#10;
  end
  else begin
    sSupplierID := IntToStr(qryTelmax.FieldByName('ID').AsInteger);
    with qryTelmax do begin
      qryTelmax.Close;
      qryTelmax.SQL.Clear;
      qryTelmax.SQL.Add('SELECT CAST(ID AS VARCHAR(20)) AS TransactionType, '+
                                'Description');
      qryTelmax.SQL.Add('FROM tTransactionType');
      qryTelmax.SQL.Add('WHERE SystemID = '+sSystemID);
      qryTelmax.SQL.Add('AND TransactionGroupID = '+sRentTransactionGroupID);
      qryTelmax.SQL.Add('AND Description LIKE ''Equipment%''');
      qryTelmax.Open;
    end;  // qryTelmax.
    if qryTelmax.Eof then begin
      lSuccessful := False;
      sErrorTxt := sErrorTxt + 'Equipment not found in tTransactionType'+#13#10;
    end
    else begin
      sRentTransactionCodeID :=
                    Trim(qryTelmax.FieldByName('TransactionType').AsString)+',';
      sRentTransactionDesc := ''''+
                    Trim(qryTelmax.FieldByName('Description').AsString)+'''';
    end;
    qryTelmax.Close;
    qryTelmax.SQL.Strings[4] := 'AND Description LIKE ''Subscription%''';
    qryTelmax.Open;
    if qryTelmax.Eof then begin
      lSuccessful := False;
      sErrorTxt := sErrorTxt+'Subscription not found in tTransactionType'+#13#10;
    end
    else begin
      sServiceTransactionCodeID :=
                    Trim(qryTelmax.FieldByName('TransactionType').AsString)+',';
      sServiceTransactionDesc := ''''+
                    Trim(qryTelmax.FieldByName('Description').AsString)+'''';
    end;
    // Ensure the tSupplierServiceCode table includes all system entry
    // codes found in tRate.
    with qryTelmax do begin
      Close;
      SQL.Clear;
      SQL.Add('INSERT INTO tSupplierServiceCode');
      SQL.Add('  (Description, Code, AmountCategory, SupplierID, Active, '+
           'TransactionTypeID, TransactionTypeDesc)');
      SQL.Add('SELECT RateCode.Description, RateCode.Code, AmountCategory, '+
      'SupplierID, Active, TransactionTypeID, TransactionTypeDesc');
      SQL.Add('FROM (');
      SQL.Add('SELECT RateCode.Description, RateCode.Code, ''RENT'' AS AmountCategory, '+
                      sSupplierID +' AS SupplierID, 1 AS Active,');
      SQL.Add('    CASE');
      SQL.Add('      WHEN RateCode.RentType = ''TEC'' THEN '+sRentTransactionCodeID);
      SQL.Add('      ELSE '+sServiceTransactionCodeID);
      SQL.Add('    END AS TransactionTypeID,');
      SQL.Add('    CASE');
      SQL.Add('      WHEN RateCode.RentType = ''TEC'' THEN '+sRentTransactionDesc);
      SQL.Add('      ELSE '+sServiceTransactionDesc);
      SQL.Add('    END AS TransactionTypeDesc');
      SQL.Add('  FROM (');
      SQL.Add('    SELECT DISTINCT');
      SQL.Add('      CASE');
      SQL.Add('        WHEN ((tRate.Length > 9) AND '+
                       '(SUBSTRING(tRate.Description,1,3) LIKE ''TEC'')) '+
                       'THEN (''T''+ CAST( tRate.Length AS VARCHAR(2)))');
      SQL.Add('        WHEN ((tRate.Length > 9) AND '+
                       '(SUBSTRING(tRate.Description,1,3) LIKE ''SFC'')) '+
                       'THEN (''S''+ CAST( tRate.Length AS VARCHAR(2)))');
      SQL.Add('        WHEN ((tRate.Length < 10) AND '+
                       '(SUBSTRING(tRate.Description,1,3) LIKE ''TEC'')) '+
                       'THEN (''T0''+ CAST( tRate.Length AS VARCHAR(1)))');
      SQL.Add('        WHEN ((tRate.Length < 10) AND '+
                       '(SUBSTRING(tRate.Description,1,3) LIKE ''SFC'')) '+
                       'THEN (''S0''+ CAST( tRate.Length AS VARCHAR(1)))');
      SQL.Add('        ELSE (''*''+ CAST( tRate.Length AS VARCHAR(2)))');
      SQL.Add('      END AS Code,');
      SQL.Add('      tRate.Length AS CodeValue,');
      SQL.Add('      tRate.Description,');
      SQL.Add('      SUBSTRING(tRate.Description, 1, 3) AS RentType');
      SQL.Add('    FROM tRate');
      SQL.Add('      LEFT JOIN tServiceType ON '+
                     '((tRate.ServiceTypeID = tServiceType.ID) AND '+
                     '(tServiceType.Active = 1))');
      SQL.Add('    WHERE tServiceType.Description LIKE ''UCP %'' AND');
      SQL.Add('        tRate.Active = 1');
      SQL.Add('  ) AS RateCode');
      SQL.Add('  LEFT JOIN tSupplierServiceCode ON '+
                 '((tSupplierServiceCode.SupplierID = '+sSupplierID +') AND');
      SQL.Add('                 (tSupplierServiceCode.Code = RateCode.Code))');
      SQL.Add('  WHERE tSupplierServiceCode.Code IS NULL');
      SQL.Add(' ) AS RateDesc');
      ExecSQL;
    end;  // Telmax.

    // Prepare the SQL in GetSupplierCodeIS() the to get SupplierServiceCodeID -
    // Line 4 updated in the Rent prog loop for each TEC & SFC.
    qryServCode := TQuery.Create(MainForm);
    qryServCode.DatabaseName := 'dbPPdata';
    with qryServCode do begin
      SQL.Add('SELECT tSupplierServiceCode.ID AS SupplierCodeID');         // 0.
      SQL.Add('FROM   tSupplierServiceCode');                              // 1.
      SQL.Add('WHERE  tSupplierServiceCode.SystemID = '+sSystemID+' AND'); // 2.
      SQL.Add('       tSupplierServiceCode.Active = 1 AND');               // 3.
      SQL.Add('       tSupplierServiceCode.Code LIKE ');                   // 4.
    end;  // qryServCode.

    qryTelmax.SQL.Clear;
    qryTelmax.SQL.Add('SELECT Code, CAST(ID AS VARCHAR(20)) AS "SCodeID", '+
      'CAST(TransactionTypeID AS VARCHAR(20)) AS TransactionType');
    qryTelmax.SQL.Add('FROM tSupplierServiceCode');
    qryTelmax.SQL.Add('WHERE SupplierID = '+sSupplierID);
    qryTelmax.SQL.Add('AND Code LIKE ''TELCALL%''');
    qryTelmax.Open;
    if qryTelmax.Eof then begin
      lSuccessful := False;
      sErrorTxt := sErrorTxt+'TELCALL not found in tSupplierServiceCode'+#13#10;
    end
    else begin
      sCallCodeID := Trim(qryTelmax['TransactionType']);
      sSupplyCallCodeID := Trim(qryTelmax['SCodeID']);
    end;
    qryTelmax.Close;

    qryTemp := TQuery.Create(MainForm);
    qryTemp.DatabaseName := 'dbPPdata';
    if lSuccessful then begin
      qryTelmax.SQL.Delete(qryTelmax.SQL.Count-1);
      qryTelmax.Open;

      // Set defaults.
      sSWEx  := '0,';                         // Switchboard.
      sSWGST := '0,';
      sSWInc := '0,';
      sSWGSTflag := '''Y'',';
      sVSWEx  := '0,';                        // Switchboard (Victrack).
      sVSWGST := '0,';
      sVSWInc := '0,';
      sVSWGSTflag := '''N'',';
      curValueDefaultTEC := 0;
      curValueDefaultSFC := 0;
      sSupplierDefaultTECcodeID := GetSupplierCodeID('T'+TEC_DEFAULT);
      sSupplierDefaultSFCcodeID := GetSupplierCodeID('S'+SFC_DEFAULT);

      qryTemp.SQL.Add('SELECT id');
      qryTemp.SQL.Add('FROM tCustomer');
      qryTemp.SQL.Add('WHERE ShipTo LIKE ''VRT''');
      qryTemp.Open;
      if qryTemp.Eof then sVRTID := '0'
      else sVRTID := IntToStr(qryTemp.FieldByName('id').AsInteger);

      qryTemp.Close;                        // Uniccomms defaults - TEC rate.
      qryTemp.SQL.Clear;
      qryTemp.SQL.Add('SELECT tRate.Cost');                            // 0.
      qryTemp.SQL.Add('FROM tRate');                                   // 1.
      qryTemp.SQL.Add('LEFT JOIN tServiceType ON '+
                       '((tRate.ServiceTypeID = tServiceType.ID)');    // 2.
      qryTemp.SQL.Add('     AND (tServiceType.Active = 1))');          // 3.
      qryTemp.SQL.Add('WHERE tServiceType.ID = '+ VRT_DEFAULT+' AND ');// 4.
      qryTemp.SQL.Add('      tRate.Length = '+ TEC_DEFAULT+' AND');    // 5.
      qryTemp.SQL.Add('      tRate.Active = 1');                       // 6.
      qryTemp.Open;
      if qryTemp.Eof then begin
        lSuccessful := False;
        sErrorTxt := sErrorTxt+'VRT TEC-Default not found in tRate'+#13#10;
      end
      else begin
        // Continue setting the next default.
        curValueDefaultTEC :=
                   curFormat(qryTemp.FieldByName('cost').AsCurrency / 12);
        qryTemp.Close;                        // Uniccomms defaults - SFC rate.
//        qryTemp.SQL.Strings[4] := 'WHERE tServiceType.Code LIKE ''USF%'' AND';
        qryTemp.SQL.Strings[5] := '      tRate.Length = '+ SFC_DEFAULT+' AND';
        qryTemp.Open;
        if qryTemp.Eof then begin
          lSuccessful := False;
          sErrorTxt := sErrorTxt+'VRT SFC-Default not found in tRate'+#13#10;
        end
        else begin
          curValueDefaultSFC :=
                     curFormat(qryTemp.FieldByName('cost').AsCurrency / 12);
        end;  // SFC.
      end;  // TEC.
    end;  // lSuccessful.
    if lSuccessful then begin
      // Set base SQL - altered for each ServiceTypeID (for Switchboard only).
{
      qryTemp.Close;                        // Defaults - base SQL.
      qryTemp.SQL.Clear;
      qryTemp.SQL.Add('SELECT tRate.Length, tRate.Cost, tRate.Description, '+
                          'tSupplierServiceCode.ID');
      qryTemp.SQL.Add('FROM tRate');
      qryTemp.SQL.Add('LEFT JOIN tSupplierServiceCode ON '+
                       '((tRate.Description = tSupplierServiceCode.Description)');
      qryTemp.SQL.Add('     AND (tSupplierServiceCode.Active = 1))');
      qryTemp.SQL.Add('WHERE tRate.Active = 1 AND ');
      qryTemp.SQL.Add('      tRate.ServiceTypeID = 0');// Dummy, replaced later.
}
      qryRef := TQuery.Create(MainForm);
      qryRef.DatabaseName := 'dbPPdata';
      qryRef.SQL.Add('SELECT id, code, Description');
      qryRef.SQL.Add('FROM tServiceType');
      qryRef.SQL.Add('WHERE Code LIKE ''TECSW''');   // Switchboard.
      qryRef.Open;
      if not qryRef.Eof then begin
        qryTemp.Close;
        qryTemp.SQL.Clear;
        qryTemp.SQL.Add('SELECT Cost');
        qryTemp.SQL.Add('FROM tRate');
        qryTemp.SQL.Add('WHERE ServiceTypeID = '+
          IntToStr(qryRef.FieldByName('id').AsInteger));
        qryTemp.Open;
        if qryTemp.Eof then curValue := 0
        else curValue := curFormat(qryTemp.FieldByName('cost').AsCurrency);
        qryTemp.Close;
        curValueGST := curValue * fGSTRate;
        sSWEx  := CurrToStr(curValue)+',';
        sSWGST := CurrToStr(curValueGST)+',';
        sSWInc := CurrToStr(curValue + curValueGST)+',';
        sVSWEx  := sSWEx;
        sVSWInc := sSWEx;
      end;  // not qryRef.Eof.

      qryRef.Close;
      qryRef.Free;
      qryTemp.Close;
    end
    else begin
      // Errors encountered - show them to the User.
      MessageDlg(sErrorTxt, mtWarning, [mbOk], 0);
    end;
    qryTemp.Free;
  end;  // if qryTelmax.Eof then begin..else.
  Result := lSuccessful;
end;  // procedure GetReferences().

procedure GetCustomerID(const sLastServiceID:string;
                        var   sServiceTypeID,
                              sServiceTypeDesc,
                              sCustomerID:string);
begin
  // Update slCust - defined in UniCommsInvoice().
  slCust[6] := 'WHERE tServiceID.ServiceID LIKE '''+ sLastServiceID +'''';
  qCustID.Close;
  qCustID.SQL := slCust;
  qCustID.Open;
  if qCustID.Eof or VarIsNull(qCustID['ServiceTypeID']) then begin
    sServiceTypeID := '';
    sServiceTypeDesc := '';
  end
  else begin
    sServiceTypeID := IntToStr(qCustID.FieldByName('ServiceTypeID').AsInteger);
    sServiceTypeDesc := qCustID.FieldByName('ServiceTypeDesc').AsString;
  end;
//  if qCustID.Eof or VarIsNull(qCustID['ServiceTypeID']) then sServiceTypeID := ''
//  else sServiceTypeID := IntToStr(qCustID.FieldByName('ServiceTypeID').AsInteger);
  if qCustID.Eof or VarIsNull(qCustID['CustomerID']) then sCustomerID := ''
  else sCustomerID := IntToStr(qCustID.FieldByName('CustomerID').AsInteger);
  qCustID.Close;
end;  // GetCustomerID().

procedure GetUniCCalls(
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const FName : string;
           var lReturnCode: Boolean );
// Data handled is for calls made from the Victrack fixed phones (PABX) network
// and recorded by the Telmax monitoing software.
// This function reads the input file and saves it in the database.
//
const
  RECORD_BYTES_C: Integer = 50;        // Typical minimum bytes per record.
  PROGRESS_COUNTER_C: Integer=100;     // Update Progress every 100 records read
  PROGRESS_MAX_C: Integer = 100;       // Maximum progress is 100%.
  SHIPTO_C: integer = 0;               // ShipTo.
  SERVID_C: integer = 1;               // ServiceID.
  EXTN_C: integer = 2;                 // Extension.
  CALLDATE_C: integer = 3;             // Date call made.
  ENDTIME_C: integer = 4;              // Time call ended.
  SITE_C: integer = 5;                 // SiteID.
  INLET_C: integer = 6;                // Inlet number.
  DIRECTN_C: integer = 7;              // Inlet direction.
  DIALLED_C: integer = 8;              // DialledNumber.
  CALLTYPE_C: integer = 9;             // Call type.
  CTYPDESC_C: integer = 10;            // Call type description.
  TIME_C: integer = 11;                // Call duration.
  COST_C: integer = 12;                // Call charge field.
var
  InputTextFile: System.TextFile;
  InputTextFileBuffer: array[1..4096] of Char;  // 4KB buffer.
  ReadString: string;                  // Read in from the text file.
  ConvertString: TStringList;          // Used to convert SDF format to strings.
  Progress: TProgressForm;
  nCount: Integer;
  nFactor : Real;
  nBlock : Integer;
  vSubString : variant;                // - ditto, used in the Locate() function.

  nTotalCount : integer;
  sFromDate : string;
  sToDate : string;

  SubString: string;
  sBatch : string;
  sListFields : string;
  sListValues : string;
  FieldStringList : TStringList;
  ValueStringList : TStringList;
  qryStringList : TStringList;
  slAddFieldNames : TStringList;         // Calls to AddToTable() only.
  slAddFieldValues : TStringList;        // Calls to AddToTable() only.
  nListValues : integer;
  qryDC : TQuery;
  sLastCallCode : string;
  sLastTransactionTypeID : string;
  sLastSupplyCallCodeID : string;
  lFound : boolean;
  lFinished : boolean;
  sCallTill : string;
  MessageForm : TMessagesForm;
begin
  lReturnCode := False;
  sBatch := IntToStr(nBatchNumber);
  sFromDate := ''''+FormattedDateString(dStartDate)+''',';
  sToDate := ''''+FormattedDateString(dEndDate)+''',';

  FieldStringList := TStringList.Create;
  ValueStringList := TStringList.Create;
  qryStringList := TStringList.Create;
  slAddFieldNames := TStringList.Create;         // Calls to AddToTable() only.
  slAddFieldValues := TStringList.Create;        // Calls to AddToTable() only.
  qryDC := TQuery.Create(MainForm);
  qryDC.DatabaseName := 'dbPPdata';

  sCallTill := '';
//  if GetReferences then begin
    // Open the input (text) billing file for reading.
    AssignFile( InputTextFile, Fname );
    FileMode := 0;  // Set file access to read only.
    // Bigger buffer for faster reads.
    System.SetTextBuf(InputTextFile, InputTextFileBuffer);
    Reset( InputTextFile );

    Progress := TProgressForm.Create(MainForm);
    Progress.Caption := 'Reading The Telmax Call File';
    Progress.btnCancel.Visible := False;
    Progress.Show;
    Progress.ProcessWinMessages(MainForm);

    ConvertString := TStringList.Create; // Used to convert SDF format to strings.
    try
      // Get the first occurence of the search string.
      Readln( InputTextFile, ReadString );

      // To be sure the file isn't empty too...
      if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then begin
        // Initiate the data variables.
        nCount := 0;
        lFinished := False;
        nFactor := 0.0100;        // Count to 100 every 10,000 records.
        nBlock := 0;
        nTotalCount := 0;
        // Continue to search through the remaining text of the file
        // for occurrences of the search string.  On each each find,
        // process the resultant string.
        repeat
          Progress.ProcessWinMessages(MainForm);
          // Decode the line read after converting the input line to SDF format.
          ConvertString.CommaText := ConvertCDE2SDF(ReadString);

          // Save the strings into the table.
          FieldStringList.Clear;
          ValueStringList.Clear;
          sListFields := '(';
          sListValues := 'VALUES (';

          sListFields := sListFields + 'SequenceNo,';
          sListValues := sListValues +''''+
            Right('0000'+IntToStr(nTotalCount),5)+''',';
          SubString := Trim(ConvertString.Strings[SERVID_C]);
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'ServiceID, Var05,';
            sListValues := sListValues + '''' + SubString +''', '''+
              SubString +''',';;
          end;
          SubString := Trim(ConvertString.Strings[CALLTYPE_C]);
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'CallCode,';
            sListValues := sListValues +''''+ SubString +''',';
            if (sLastCallCode <> SubString) then begin
              sLastCallCode := SubString;
              vSubString := SubString;
              with qryTelmax do
                lFound := Locate('Code;TransactionType',
                  VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]);
              if lFound then begin
                sLastTransactionTypeID := qryTelmax['TransactionType'];
                sLastSupplyCallCodeID := Trim(qryTelmax['SCodeID'])+',';
              end
              else begin
                sLastTransactionTypeID := sCallCodeID;
                slAddFieldNames.Clear;
                slAddFieldValues.Clear;
                slAddFieldNames.Add('Code');
                slAddFieldValues.Add(''''+sLastCallCode+'''');
                slAddFieldNames.Add('AmountCategory');
                slAddFieldValues.Add('''CALLS''');
                slAddFieldNames.Add('Description');
                slAddFieldValues.Add('''Telmax Call''');
                slAddFieldNames.Add('TransactionTypeDesc');
                slAddFieldValues.Add('''Telmax Call''');
                slAddFieldNames.Add('TransactionTypeID');
                slAddFieldValues.Add(sLastTransactionTypeID);
                slAddFieldNames.Add('SupplierID');
                slAddFieldValues.Add(sSupplierID);
                slAddFieldNames.Add('Active');
                slAddFieldValues.Add('1');
                AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues);
                qryTelmax.Close;
                qryTelmax.Open;
                with qryTelmax do
                  lFound := Locate('Code;TransactionType',
                    VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]);
                if lFound then begin
                  sLastTransactionTypeID := qryTelmax['TransactionType'];
                  sLastSupplyCallCodeID  := Trim(qryTelmax['SCodeID'])+',';
                end
                else begin
                  sLastTransactionTypeID := sCallCodeID;
                  sLastSupplyCallCodeID  := sSupplyCallCodeID;
                end;
              end;  // if (not) lFound.
            end;  // if (sLastCallCode <> SubString) then begin.
            if (Length(sLastTransactionTypeID) > 0) then begin
              sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,';
              sListValues := sListValues + sCallCodeID+','+sLastSupplyCallCodeID;
            end;
          end
          else begin
            sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,';
            sListValues := sListValues + sCallCodeID+','+sLastSupplyCallCodeID;
          end;
          SubString := Trim(ConvertString.Strings[CALLDATE_C]);
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'TxnDate,';
            sListValues := sListValues +''''+ SubString +''',';
          end;
          SubString := Trim(ConvertString.Strings[ENDTIME_C]);
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'TxnTime,';
            sListValues := sListValues +''''+ Copy(SubString,1,2)+
              ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''',';
          end;
          SubString := Trim(ConvertString.Strings[TIME_C]);
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Duration,';
            sListValues := sListValues +''''+ CleanString(SubString) +''',';
          end;
          FieldStringList.Add(sListFields);
          ValueStringList.Add(sListValues);
          sListFields := '';
          sListValues := '';

          SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C]));
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'DialledNumber,';
            sListValues := sListValues +''''+SubString+''',';
          end;
          SubString := Trim(ConvertString.Strings[CTYPDESC_C]);
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'RateDescription,Peak,';
            sListValues := sListValues +''''+ CleanString(SubString) +''',1,';
          end;
          FieldStringList.Add(sListFields);
          ValueStringList.Add(sListValues);
          sListFields := '';
          sListValues := '';

          SubString := Trim(ConvertString.Strings[COST_C]);
          sListFields := sListFields + 'AmountExGST,AmountIncGST,GSTFlag,';
          sListValues := sListValues + SubString +',' + SubString +',''N'',';
          sListFields := sListFields + 'FromDate,';
          sListValues := sListValues + sFromDate;
          sListFields := sListFields + 'ToDate,';
          sListValues := sListValues + sToDate;

          SubString := CleanString(Trim(ConvertString.Strings[EXTN_C]));
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Var01,';      // tServiceType.Description.
            sListValues := sListValues +''''+ SubString +''',';
          end;
          SubString := CleanString(Trim(ConvertString.Strings[SITE_C]));
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Var02,';      // ServiceID.Description.
            sListValues := sListValues +''''+ SubString +''',';
          end;
          SubString := CleanString(Trim(ConvertString.Strings[INLET_C]));
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Var03,';      // tLocation.Description.
            sListValues := sListValues +''''+ SubString +''',';
          end;
          SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C]));
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Var04,';      // tLocation.Description.
            sListValues := sListValues +''''+ SubString +''',';
          end;
          // Now make the SQL statement to save to the database table.
          sListFields := sListFields + 'BatchID)';
          sListValues := sListValues + sBatch +')';
          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;
          // Keep track of how much work has been done to date.
          Inc(nCount);
          Inc(nTotalCount);
          Inc(nBlock);
          if (nBlock=100) then begin
            Progress.ProgressBar1.Position := Trunc(nCount * nFactor);
            nBlock := 0;
            if ((nCount * nFactor)>=100.00) then begin
              // restart the progress bar.
              nCount := 0;
            end;  // if ((nCount * nFactor)>100.00) then.
            Progress.ProgressBar1.Position := Trunc(nCount * nFactor);
            Progress.Msg.Caption := 'Processing (transaction: '+
              IntToStr(nTotalCount)+')...';
          end;  // if (nBlock=10) then.
          Progress.ProcessWinMessages(MainForm);    // Let windows update all messages.
          if Eof( InputTextFile ) then lFinished := True;
          Readln( InputTextFile, ReadString ); // Read in the next line of data.
          if Eof( InputTextFile ) and (Length(Trim(ReadString)) = 0) then
            lFinished := True;
        until lFinished;

        Progress.Hide;
        MessageForm := TMessagesForm.Create(MainForm);
        MessageForm.Caption := 'Importing Telmax Call charges';
        MessageForm.Msg.Caption := 'Updating with applied rates ...';
        MessageForm.Show;
        Progress.ProcessWinMessages(MainForm);
        qryStringList.Clear;
        qryStringList.Add('UPDATE tDataCollection');
        qryStringList.Add('SET GSTAmount = GSTrate.GSTAmt,');
        qryStringList.Add('  AmountIncGST = GSTrate.AmtExGST + GSTrate.GSTAmt,');
        qryStringList.Add('  GSTflag = ''Y''');
        qryStringList.Add('FROM (SELECT tDataCollection.ID, '+
          'tDataCollection.AmountExGST AS "AmtExGST", '+
          '(tDataCollection.AmountExGST * '+sGSTRate+') AS "GSTAmt"');
        qryStringList.Add('FROM (tDataCollection');
        qryStringList.Add('LEFT JOIN tServiceID '+
          'ON tDataCollection.ServiceID = tServiceID.ServiceID)');
        qryStringList.Add('LEFT JOIN tServiceType '+
          'ON tServiceID.ServiceTypeID = tServiceType.ID');
        qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch);
        qryStringList.Add('AND tDataCollection.AmountExGST <> 0');
        qryStringList.Add('AND tServiceType.Code LIKE ''TQ'') AS GSTrate');
        qryStringList.Add('WHERE tDataCollection.ID = GSTrate.ID');
        qryDC.Close;
        qryDC.SQL := qryStringList;
        qryDC.ExecSQL;
        MessageForm.Close;
        MessageForm.Free;
        lReturnCode := True;
      end;  // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then.
    finally
      // Done with the input file and resources, close them.
      CloseFile( InputTextFile );
      Progress.Close;
      ConvertString.Free;
      FieldStringList.Free;
      ValueStringList.Free;
      qryStringList.Free;
      qryDC.Free;
    end;

    Progress.Close;
//  end;  // if GetReferences then begin.
end;  // procedure GetUniCCalls().


procedure GetUniCRent(
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const FName : string;
           var lReturnCode: Boolean );
// Data handled is for rent of the Victrack fixed phones (PABX) network
// This function reads the input file and saves it to the database.
//
const
  PROGRESS_COUNTER_C: Integer=100;     // Update Progress every 100 records read
  PROGRESS_MAX_C: Integer = 100;       // Maximum progress is 100%.
  SHIPTO_C: integer = 0;               // ShipTo.
  SERVID_C: integer = 1;               // ServiceID.
  EXTN_C: integer = 2;                 // Extension.
  STARTDATE_C: integer = 3;            // Date for start of the Rental period.
  ENDDATE_C: integer = 4;              // Date for end of the Rental period.
  SITE_C: integer = 5;                 // SiteID.
  TEC_C: integer = 6;                  // TEC number - eg T02R02S03, which is
                                       // tec & value, RSC & value, SFC & value.
var
  InputTextFile: TextFile;
  InputTextFileBuffer: array[1..4096] of Char;  // 4KB buffer.
  ReadString: string;                  // Read in from the text file.
  ConvertString: TStringList;          // Used to convert SDF format to strings.
  Progress: TProgressForm;
  nCount: Integer;
  nFactor: Real;
  nBlock : Integer;

  nTotalCount : integer;
  sFromDate : string;
  sToDate : string;

  SubString: string;
  sBatch : string;
  sListFields : string;
  sListValues : string;
  FieldStringList : TStringList;
  ValueStringList : TStringList;
  qryStringList : TStringList;
  sUTECString : string;
  LastServiceID : string;
  sServiceTypeID : string;
  sServiceTypeDesc : string;
  sCustomerID : string;
  sSupplierCodeID : string;

  sGSTflag : string;
  lGSTflag : boolean;
  nListValues : integer;
  qryDC  : TQuery;
  qryTEC : TQuery;
  curValue    : currency;
  curValueGST : currency;

  lServiceTypeOkay : boolean;
begin
  // Initiate all variables.
  lReturnCode := False;
  lServiceTypeOkay := True;
  lGSTflag := TRUE;
  sGSTflag := '''Y'',';
  sBatch := IntToStr(nBatchNumber);
  sFromDate := FormattedDateString(dStartDate);
  sToDate := FormattedDateString(dEndDate);

  FieldStringList := TStringList.Create;
  ValueStringList := TStringList.Create;
  qryStringList := TStringList.Create;
  qryDC  := TQuery.Create(MainForm);
  qryDC.DatabaseName := 'dbPPdata';
  qryTEC := TQuery.Create(MainForm);
  qryTEC.DatabaseName := 'dbPPdata';

  // Prepare the SQL to get rent & service costs -
  // Line 3 updated in prog loop when ServiceID (ServiceType) updated,
  // Line 4 updated in prog loop for each TEC & SFC.
  with qryTEC do begin
    SQL.Add('SELECT tRate.Length, tRate.Cost, tRate.Description AS RateDesc');//0.
{
    SQL.Add('SELECT tRate.Length, tRate.Cost, tRate.Description AS RateDesc, '+
                    'tSupplierServiceCode.ID AS SupplierCodeID');     // 0.
}
    SQL.Add('FROM   tRate');                                          // 1.
{
    SQL.Add('       LEFT JOIN tSupplierServiceCode ON ((tRate.Description = '+
                         'tSupplierServiceCode.Description) '+
                         'AND (tSupplierServiceCode.Active = 1))');   // 2.
}
    SQL.Add('WHERE  tRate.Active = 1 ');                              // 2(3).
    SQL.Add('       AND tRate.ServiceTypeID = 0');                    // 3(4).
    SQL.Add('       AND tRate.Length = 0');                           // 4(5).
  end;  // qryTEC.

    qryDC.SQL.Clear;
    // Open the input (text) billing file for reading.
    AssignFile( InputTextFile, Fname );
    // Bigger buffer for faster reads.
    System.SetTextBuf(InputTextFile, InputTextFileBuffer);
    Reset( InputTextFile );

    Progress := TProgressForm.Create(MainForm);
    Progress.Caption := 'Reading The UniComms Rent File';
    Progress.btnCancel.Visible := False;
    Progress.Show;
    Progress.ProcessWinMessages(MainForm);
    ConvertString := TStringList.Create; // Used to convert SDF format to strings.
    try
      // Get the first occurence of the search string.
      Readln( InputTextFile, ReadString );

      // To be sure the file isn't empty too...
      if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then begin
        // Initiate the data variables.
        nFactor := 0.1000;        // Count to 100 every 1,000 records.
        nBlock := 0;
        nTotalCount := 1;
        nCount := 0;
        // Continue to search through the remaining text of the file
        // for occurrences of the search string.  On each each find,
        // process the resultant string.
        repeat
          Progress.ProcessWinMessages(MainForm);
          // Decode the line read after converting the input line to SDF format.
          ConvertString.CommaText := ConvertCDE2SDF(ReadString);

          // Save the strings into the table.
          FieldStringList.Clear;
          ValueStringList.Clear;
          sListFields := '(';
          sListValues := 'VALUES (';

          SubString := Trim(ConvertString.Strings[SERVID_C]);
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'ServiceID,Var05,Var11,';
            sListValues := sListValues + ''''+SubString +''','''+
              SubString+''','''+SubString+''',';
          end;
          // Save to string list.                                     // 0.
          FieldStringList.Add(sListFields);
          ValueStringList.Add(sListValues);
          sListFields := '';
          sListValues := '';

          // Set default charge rates.
          if (SubString <> LastServiceID) then begin
            // Find references to define the applicable charges.
            LastServiceID := SubString;
            GetCustomerID(LastServiceID,sServiceTypeID,sServiceTypeDesc,
                          sCustomerID);
            // Set the GSTflags - for later cost calculations.
            if (sCustomerID = sVRTID) then begin
              lGSTflag := FALSE;
              sGSTflag := '''N'',';
            end
            else begin
              lGSTflag := TRUE;
              sGSTflag := '''Y'',';
            end;
            // Update the SQL statement for TEC & SFC.
            lServiceTypeOkay := True;           // Checked when costing TEC.
            if (Length(sServiceTypeID) = 0) then begin
              sServiceTypeID := '0';
              lServiceTypeOkay := False;
            end;
            qryTEC.Close;
{            qryTEC.SQL.strings[4] :=
}
            qryTEC.SQL.strings[3] :=
              '       AND tRate.ServiceTypeID = '+sServiceTypeID;  // 3(4).
          end;  // LastServiceID.
          sListFields := sListFields + 'SequenceNo,TransactionTypeID,';
          sListValues := sListValues +''''+
            Right('0000'+IntToStr(nTotalCount),5)+''','+
            sRentTransactionCodeID;             // Set in GetReferences().

// TEC start..
          sUTECString := CleanString(Trim(ConvertString.Strings[TEC_C]));
          SubString := Copy(sUTECString,2,2);
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Var06, Var09,';      // Var06, Var09.
            sListValues := sListValues +'''T'+ SubString +''',' +
                                        ''''+ sUTECString +''',';
          end;
          // Save to string list.                                     // 1.
          FieldStringList.Add(sListFields);
          ValueStringList.Add(sListValues);
          sListFields := '';
          sListValues := '';

          // TEC='XX'-TEC unknown: unused or not allocated, set rent at default;
          // TEC='NN'-allocated elsewhere a/c reallocation, do not charge again.
          sListFields := sListFields + 'AmountExGST, GSTAmount, AmountIncGST,'+
            'SupplierServiceCodeID, GSTFlag, RateDescription, ';
          if (SubString = 'NN') then begin
            sListValues := sListValues + '0,0,0,NULL,''N'',' +
                                          '''TEC Reallocated'',';
          end
          else begin
            if (SubString = 'XX') or ( not lServiceTypeOkay ) then begin
              // Use the VRT default rate - servicetypeid not in tRate.
              curValue := curValueDefaultTEC;
              sSupplierCodeID := sSupplierDefaultTECcodeID;
            end  // ...XX
            // Determine the cost from the values read from the tRate table.
            else begin
              // Use the default rate.
              qryTEC.Close;
              qryTEC.SQL.strings[4] :=
                '       AND tRate.Length = '+SubString;              // 4.
              qryTEC.Open;
              if qryTEC.Eof then begin
                // Not found - is the default for this servicetype availble?
                qryTEC.Close;
                qryTEC.SQL.strings[4] :=
                  '       AND tRate.Length = '+ TEC_DEFAULT;         // 4.
                qryTEC.Open;
                if qryTEC.Eof then begin
                  // Not available - use the VRT_DEFAULT rates.
                  curValue := curValueDefaultTEC;
                  sSupplierCodeID := sSupplierDefaultTECcodeID;
                end
                else begin
                  // Use the default rate listed for this service.
                  curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12);
                  sSupplierCodeID := GetSupplierCodeID('T'+TEC_DEFAULT);
                end;
              end
              else begin
                // Use the actual charge rate for this service.
                curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12);
                sSupplierCodeID := GetSupplierCodeID(Copy(sUTECString,1,3));
              end;  // Eof.
            end;  // ..'XX'.
            if lGSTflag then curValueGST := curValue * fGSTRate
                        else curValueGST := 0;
            sListValues := sListValues +
                 CurrToStr(curValue)+',' +
                 CurrToStr(curValueGST)+',' +
                 CurrToStr(curValue + curValueGST)+',' +
                 sSupplierCodeID +','+
                 sGSTFlag +''''+
                 TRIM(qryTEC.FieldByName('RateDesc').AsString)+''',';
          end;  // 'NN'.
          qryTEC.Close;
          // Save to string list.                                     // 2.
          FieldStringList.Add(sListFields);
          ValueStringList.Add(sListValues);
          sListFields := '';
          sListValues := '';
// TEC ..end

          sListFields := sListFields + 'ServiceType,';  // ServiceType.
//          sListValues := sListValues + ' '''+sRentServType+
          sListValues := sListValues + ' '''+Trim(sServiceTypeDesc+
                                       ' T'+SubString)+''',';
          // Save to string list.                                     // 3.
          FieldStringList.Add(sListFields);
          ValueStringList.Add(sListValues);
          sListFields := '';
          sListValues := '';

          SubString := Trim(ConvertString.Strings[STARTDATE_C]);
          sListFields := sListFields + 'FromDate,';
          if (Length(SubString) > 0) then
            sListValues := sListValues +''''+ SubString +''','
          else
            sListValues := sListValues + sFromDate;
          SubString := Trim(ConvertString.Strings[ENDDATE_C]);
          sListFields := sListFields + 'ToDate,';
          if (Length(SubString) > 0) then
            sListValues := sListValues +''''+ SubString +''','
          else
            sListValues := sListValues + sToDate;
          // Save to string list.                                     // 4.
          FieldStringList.Add(sListFields);
          ValueStringList.Add(sListValues);
          sListFields := '';
          sListValues := '';

          SubString := CleanString(Trim(ConvertString.Strings[EXTN_C]));
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Var01,';      // Var01.
            sListValues := sListValues +''''+ SubString +''',';
          end;
          SubString := CleanString(Trim(ConvertString.Strings[SITE_C]));
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Var02,';      // Var02.
            sListValues := sListValues +''''+ SubString +''',';
          end;
          SubString := CleanString(Trim(ConvertString.Strings[SHIPTO_C]));
          if (Length(SubString) > 0) then begin
            sListFields := sListFields + 'Var10,';      // Var10.
            sListValues := sListValues +''''+ SubString +''',';
          end;
          // Now make the SQL statement to save to the database table.
          sListFields := sListFields + 'BatchID)';
          sListValues := sListValues + sBatch +')';
          FieldStringList.Add(sListFields);                           // 5.
          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;

          // Now add a record for the Service used.
          Inc(nTotalCount);
          FieldStringList.Strings[1] := 'SequenceNo, TransactionTypeID, '+
                                        'Var07, Var09,'; // Var07,09
          ValueStringList.Strings[1] := ''''+
            Right('0000'+IntToStr(nTotalCount),5)+''','+
            sServiceTransactionCodeID+''''+     // Set in GetReferences().
            Copy(sUTECString,7,3) +''',''' + sUTECString +''',';

// Start SFC ...
          SubString := UpperCase(Copy(sUTECString,8,2));
          // TEC='XX'-TEC unknown: unused or not allocated, set rent at default;
          // TEC='NN'-allocated elsewhere a/c reallocation, do not charge again.
          if (SubString = 'NN') then begin
            sListValues := sListValues + '0,0,0,NULL,''N'',' +
                                          '''SFC Reallocated'',';
          end
          else begin
            if (SubString = 'XX') or ( not lServiceTypeOkay ) then begin
              // Use the VRT default rate - servicetypeid not in tRate.
              curValue := curValueDefaultSFC;
              sSupplierCodeID := sSupplierDefaultSFCcodeID;
            end  // ...XX
            // Determine the cost from the values read from the tRate table.
            else begin
              // Use the default rate.
              qryTEC.Close;
              qryTEC.SQL.strings[4] :=
                '       AND tRate.Length = '+SubString;              // 4.
              qryTEC.Open;
              if qryTEC.Eof then begin
                // Not found - is the default for this servicetype availble?
                qryTEC.Close;
                qryTEC.SQL.strings[4] :=
                  '       AND tRate.Length = '+ SFC_DEFAULT;         // 4.
                qryTEC.Open;
                if qryTEC.Eof then begin
                  // Not available - use the VRT_DEFAULT rates.
                  curValue := curValueDefaultSFC;
                  sSupplierCodeID := sSupplierDefaultSFCcodeID;
                end
                else begin
                  // Use the default rate listed for this service.
                  curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12);
                  sSupplierCodeID := GetSupplierCodeID('S'+SFC_DEFAULT);
                end;
              end
              else begin
                // Use the actual charge rate for this service.
                curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12);
                sSupplierCodeID := GetSupplierCodeID(Copy(sUTECString,7,3));
              end;  // Eof.
            end;  // ..'XX'.
            if lGSTflag then curValueGST := curValue * fGSTRate
                        else curValueGST := 0;
            sListValues := sListValues +
                 CurrToStr(curValue)+',' +
                 CurrToStr(curValueGST)+',' +
                 CurrToStr(curValue + curValueGST)+',' +
                 sSupplierCodeID +','+
                 sGSTFlag +''''+
                 TRIM(qryTEC.FieldByName('RateDesc').AsString)+''',';
          end;  // 'NN'.
          qryTEC.Close;
// ... End SFC.

          ValueStringList.Strings[3] := ' '''+ Trim(sServiceTypeDesc+
                                        ' S'+SubString)+''',';

          // Now save the the tDataCollection table.
          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;

          // Keep track of how much work has been done to date.
          Inc(nCount);
          Inc(nTotalCount);
          Inc(nBlock);
          if (nBlock=10) then begin
            Progress.ProgressBar1.Position := Trunc(nCount * nFactor);
            nBlock := 0;
            if ((nCount * nFactor)>=100.00) then begin
              // restart the progress bar.
              nCount := 0;
            end;  // if ((nCount * Factor)>=100.00) then.
            Progress.ProgressBar1.Position := Trunc(nCount * nFactor);
            Progress.Msg.Caption := 'Processing (transaction: '+
              IntToStr(nTotalCount)+')...';
          end;  // if (nBlock=10) then.
          Progress.ProcessWinMessages(MainForm);    // Let windows update all messages.
          Readln( InputTextFile, ReadString ); // Read in the next line of data.
        until Eof( InputTextFile );
        // Now update the values stored in the tDataCollection table with
        // any alternate service charge rates stored separately in tRates.
        qryStringList.Clear;
        // Done. Values updated from the tRates table for TEC & SFC values.
        lReturnCode := True;
      end;  // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then.
    finally
      // Done with the input file and resources, close them.
      CloseFile( InputTextFile );
      Progress.Close;
      ConvertString.Free;
      FieldStringList.Free;
      ValueStringList.Free;
      qryStringList.Free;
      qryDC.Free;
    end;
    Progress.Close;
//  end;  // if GetReferences then begin.
end;  // procedure GetUniCRent.


procedure GetUniCSwitchboard(
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const FName : string;
           var lReturnCode: Boolean );
// Data handled is for rent of the Victrack fixed phones (PABX) network
// This function reads the input file and saves it to the database.
//
const
  PROGRESS_COUNTER_C: Integer=100;     // Update Progress every 100 records read
  PROGRESS_MAX_C: Integer = 100;       // Maximum progress is 100%.
  SHIPTO_C: integer = 0;               // ShipTo.
  SERVID_C: integer = 1;               // ServiceID.
  EXTN_C: integer = 2;                 // Extension.
  CALLDATE_C: integer = 3;             // Date call made.
  ENDTIME_C: integer = 4;              // Time call ended.
  SITE_C: integer = 5;                 // SiteID.
  INLET_C: integer = 6;                // Inlet number.
  DIRECTN_C: integer = 7;              // Inlet direction.
  DIALLED_C: integer = 8;              // DialledNumber.
  CALLTYPE_C: integer = 9;             // Call type.
  CTYPDESC_C: integer = 10;            // Call type description.
  TIME_C: integer = 11;                // Call duration.
  COST_C: integer = 12;                // Call charge field.
var
  InputTextFile: TextFile;
  InputTextFileBuffer: array[1..4096] of Char;  // 4KB buffer.
  ReadString: string;                  // Read in from the text file.
  ConvertString: TStringList;          // Used to convert SDF format to strings.
  Progress: TProgressForm;
  nCount: Integer;
  nFactor: Real;
  nBlock : Integer;
  nTotalCount : integer;
  sFromDate : string;
  sToDate : string;
  SubString: string;
  sBatch : string;
  sListFields : string;
  sListValues : string;
  FieldStringList : TStringList;
  ValueStringList : TStringList;
  qryStringList : TStringList;
  LastServiceID : string;
  sServiceTypeID : string;
  sServiceTypeDesc : string;
  sCustomerID : string;
  sCallExGST    : string;
  sCallIncGST   : string;
  sGST          : string;
  sGSTflag      : string;
  nListValues : integer;
  qryDC : TQuery;
  lDummyEntry : boolean;
  sLastCallCode : string;
  lFound : boolean;
  sLastTransactionTypeID : string;
  sLastSupplyCodeID : string;
  slAddFieldNames, slAddFieldValues : TStringList;
begin
  lReturnCode := False;
  sBatch := IntToStr(nBatchNumber);
  sFromDate := ''''+FormattedDateString(dStartDate)+''',';
  sToDate := ''''+FormattedDateString(dEndDate)+''',';
  slAddFieldNames := TStringList.Create;         // Calls to AddToTable() only.
  slAddFieldValues := TStringList.Create;        // Calls to AddToTable() only.
  FieldStringList := TStringList.Create;
  ValueStringList := TStringList.Create;
  qryStringList := TStringList.Create;
  qryDC := TQuery.Create(MainForm);
  qryDC.DatabaseName := 'dbPPdata';
//  if GetReferences then begin
    // Open the input (text) billing file for reading.
    AssignFile( InputTextFile, Fname );
    // Bigger buffer for faster reads.
    System.SetTextBuf(InputTextFile, InputTextFileBuffer);
    Reset( InputTextFile );

    Progress := TProgressForm.Create(MainForm);
    Progress.Caption := 'Reading The Telmax Switchboard File';
    Progress.btnCancel.Visible := False;
    Progress.Show;
    Progress.ProcessWinMessages(MainForm);
    ConvertString := TStringList.Create; // Used to convert SDF format to strings.
    try
      // Read the first line of the input file.
      Readln( InputTextFile, ReadString );

      // To be sure the file isn't empty too...
      if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then begin
        // Initiate the data variables.
        nFactor := 0.1000;        // Count to 100 every 1,000 records.
        nBlock := 0;
        nTotalCount := 0;
        nCount := 0;
        // Continue to search through the remaining text of the file
        // for occurrences of the search string.  On each each find,
        // process the resultant string.
        repeat
          Progress.ProcessWinMessages(MainForm);
          // Decode the line read after converting the input line to SDF format.
          ConvertString.CommaText := ConvertCDE2SDF(ReadString);

          SubString := UpperCase(Trim(ConvertString.Strings[CALLTYPE_C]));
          lDummyEntry := (SubString = 'X');
          // 'X'-Dummy entry: exists for every Extension for Web Billing.
          if not lDummyEntry then begin
            // Save the strings into the table.
            FieldStringList.Clear;
            ValueStringList.Clear;
            sListFields := '(';
            sListValues := 'VALUES (';

            sListFields := sListFields + 'SequenceNo,';
            sListValues := sListValues +''''+
              Right('0000'+IntToStr(nTotalCount),5)+''',';
            SubString := Trim(ConvertString.Strings[SERVID_C]);
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'ServiceID,Var05,';
              sListValues := sListValues + '''SW'+SubString+''',''SW'+SubString+''',';
            end;
            // Set defaults and calculate charge rates.
            if (SubString <> LastServiceID) then begin
              // Find references to define the applicable charges.
              LastServiceID := SubString;
//              GetCustomerID('SW'+LastServiceID,sServiceTypeID,sCustomerID);
              GetCustomerID('SW'+LastServiceID,sServiceTypeID,sServiceTypeDesc,
                            sCustomerID);
              if (sCustomerID = sVRTID) then begin
                sCallExGST    := sVSWEx;
                sCallIncGST   := sVSWInc;
                sGST          := sVSWGST;
                sGSTflag      := sVSWGSTflag;
              end
              else begin
                sCallExGST    := sSWEx;
                sCallIncGST   := sSWInc;
                sGST          := sSWGST;
                sGSTflag      := sSWGSTflag;
              end;  // if (sCustomerID = sVRTID) then begin..else.
            end;  // if new ServiceID.

            // Determine the applicable charges.
            FieldStringList.Add(sListFields);
            ValueStringList.Add(sListValues);
            sListFields := '';
            sListValues := '';

            FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,');
            ValueStringList.Add(sCallExGST+sGST+sCallIncGST+sGSTFlag);
            SubString := Trim(ConvertString.Strings[CALLTYPE_C]);
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'CallCode,';
              sListValues := sListValues +''''+ SubString +''',';
              if (sLastCallCode <> SubString) then begin
                sLastCallCode := SubString;
                with qryTelmax do
                  lFound := Locate('Code;TransactionType',
                    VarArrayOf([SubString,sCallCodeID]),
                    [loCaseInsensitive]);
                if lFound then begin
                  sLastTransactionTypeID := qryTelmax['TransactionType'];
                  sLastSupplyCodeID  := Trim(qryTelmax['SCodeID']);
                end
                else begin
                  sLastTransactionTypeID := sCallCodeID;
                  slAddFieldNames.Clear;
                  slAddFieldValues.Clear;
                  slAddFieldNames.Add('Code');
                  slAddFieldValues.Add(''''+sLastCallCode+'''');
                  slAddFieldNames.Add('AmountCategory');
                  slAddFieldValues.Add('''CALLS''');
                  slAddFieldNames.Add('Description');
                  slAddFieldValues.Add('''Switchboard Call''');
                  slAddFieldNames.Add('TransactionTypeDesc');
                  slAddFieldValues.Add('''Telmax Call''');
                  slAddFieldNames.Add('TransactionTypeID');
                  slAddFieldValues.Add(sLastTransactionTypeID);
                  slAddFieldNames.Add('SupplierID');
                  slAddFieldValues.Add(sSupplierID);
                  slAddFieldNames.Add('Active');
                  slAddFieldValues.Add('1');
                  AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues);
                  qryTelmax.Close;
                  qryTelmax.Open;
                  with qryTelmax do
                    lFound := Locate('Code;TransactionType',
                      VarArrayOf([SubString,sCallCodeID]),
                      [loCaseInsensitive]);
                  if lFound then begin
                    sLastTransactionTypeID := qryTelmax['TransactionType'];
                    sLastSupplyCodeID  := Trim(qryTelmax['SCodeID']);
                  end
                  else begin
                    sLastTransactionTypeID := sCallCodeID;
                    sLastSupplyCodeID  := sSupplyCallCodeID;
                  end;
                end;  // if (not) lFound.
              end;  // if (sLastCallCode <> SubString) then begin.
              if (Length(sLastTransactionTypeID) > 0) then begin
                sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,';
                sListValues := sListValues + sLastTransactionTypeID+','+
                  sLastSupplyCodeID+',';
              end;
            end
            else begin
              sListFields := sListFields + 'TransactionTypeID,SupplierserviceCodeID,';
              sListValues := sListValues + sCallCodeID+','+sLastSupplyCodeID+',';
            end;
            SubString := Trim(ConvertString.Strings[CALLDATE_C]);
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'TxnDate,';
              sListValues := sListValues +''''+ SubString +''',';
            end;
            SubString := Trim(ConvertString.Strings[ENDTIME_C]);
            if (Length(SubString) = 6) then begin
              sListFields := sListFields + 'TxnTime,';
              sListValues := sListValues +''''+ Copy(SubString,1,2)+
                ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''',';
            end;
            SubString := Trim(ConvertString.Strings[TIME_C]);
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'Duration,';
              sListValues := sListValues +''''+ CleanString(SubString) +''',';
            end;
            FieldStringList.Add(sListFields);
            ValueStringList.Add(sListValues);
            sListFields := '';
            sListValues := '';

            SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C]));
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'DialledNumber,';
              sListValues := sListValues +''''+SubString+''',';
            end;
            SubString := Trim(ConvertString.Strings[CTYPDESC_C]);
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'RateDescription,Peak,';
              sListValues := sListValues +''''+ CleanString(SubString) +''',0,';
            end;
            FieldStringList.Add(sListFields);
            ValueStringList.Add(sListValues);
            sListFields := '';
            sListValues := '';

            sListFields := sListFields + 'FromDate,';
            sListValues := sListValues + sFromDate;
            sListFields := sListFields + 'ToDate,';
            sListValues := sListValues + sToDate;
            SubString := CleanString(Trim(ConvertString.Strings[EXTN_C]));
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'Var01,';         // Extn.
              sListValues := sListValues +''''+ SubString +''',';
            end;
            SubString := CleanString(Trim(ConvertString.Strings[SITE_C]));
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'Var02,';         // SiteID.
              sListValues := sListValues +''''+ SubString +''',';
            end;
            SubString := CleanString(Trim(ConvertString.Strings[INLET_C]));
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'Var03,';         // InletNo.
              sListValues := sListValues +''''+ SubString +''',';
            end;
            SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C]));
            if (Length(SubString) > 0) then begin
              sListFields := sListFields + 'Var04,';         // Inlet direction.
              sListValues := sListValues +''''+ SubString +''',';
            end;

            // Now make the SQL statement to save to the database table.
            sListFields := sListFields + 'BatchID)';
            sListValues := sListValues + sBatch +')';
            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;
          end;  // if (SubString <> 'X') or ((SubString = 'X') and..
          // Keep track of how much work has been done to date.
          Inc(nCount);
          Inc(nTotalCount);
          Inc(nBlock);
          if (nBlock=10) then begin
            Progress.ProgressBar1.Position := Trunc(nCount * nFactor);
            nBlock := 0;
            if ((nCount * nFactor)>=100.00) then nCount := 0; // reset progress bar.
            Progress.ProgressBar1.Position := Trunc(nCount * nFactor);
            Progress.Msg.Caption := 'Processing (transaction: '+
              IntToStr(nTotalCount)+')...';
          end;  // if (nBlock=10) then.
          Progress.ProcessWinMessages(MainForm);    // Let windows update all messages.
          Readln( InputTextFile, ReadString ); // Read in the next line of data.
        until Eof( InputTextFile );
        lReturnCode := True;
      end;  // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then.
    finally
      // Done with the input file and resources, close them.
      CloseFile( InputTextFile );
      Progress.Close;
      ConvertString.Free;
      FieldStringList.Free;
      ValueStringList.Free;
      qryStringList.Free;
      qryDC.Free;
    end;
    Progress.Close;
//  end;  // if GetReferences then begin.
end;  // GetUniCsSwitchboard.


procedure UniCommsInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime;
  const BatchDescription, sDirectory : string );
// This function prompts the user for the name of the Telmax supplied
// text file (comma delimited) and then calls the function which reads the
// file into tDataCollection table for further processing.
var
  OpenDlg : TOpenDialog;
  lContinue : Boolean;
  sFile : string;
  sRentFile, sCallFile, sSwitchboardFile : string;
  slFileList : TStringList;
  sPath : string;
  sMask : string;
  MessageForm : TMessagesForm;
begin
  // Initiate global variables.
  qCustID := TQuery.Create(MainForm);    // Used in GetCustomerID().
  qCustID.DatabaseName := 'dbPPdata';
  slCust := TStringList.Create;          // Used in GetCustomerID().
  lContinue := True;
  // First, we must establish which file is the one to be processed.
  OpenDlg := TOpenDialog.Create(MainForm);
  OpenDlg.Options := [ofPathMustExist,ofAllowMultiSelect];
  OpenDlg.Filter := 'Telmax Unified Comms Billing files (*.txt)|*.TXT|All files (*.*)|*.*';
  OpenDlg.FilterIndex := 1;
  // Use the program directory as the default directory.
  if (Length(sDirectory) > 0) then OpenDlg.InitialDir := sDirectory
  else                             OpenDlg.InitialDir := ExtractFilePath(ParamStr(0));
  OpenDlg.Title := 'Select The Input File Directory';
  // Read and save the user's choices before continuing.
  lContinue := OpenDlg.Execute;
  sFile := OpenDlg.FileName;
  OpenDlg.Free;
  if lContinue and (Length(Trim(sFile)) > 0) then begin
    slFileList := TStringList.Create;
    sPath := ExtractFilePath(sFile);
    sMask := 'c*.txt';                                            // Calls
    GetFileList(sPath, sMask, slFileList);
    if slFileList.Count > 0 then
      sCallFile := slFileList[0]
    else
      lContinue := False;
    if lContinue then begin
      sMask := 'r*.txt';                                          // Rent
      slFileList.Clear;
      GetFileList(sPath, sMask, slFileList);
      if slFileList.Count > 0 then
        sRentFile := slFileList[0]
      else
        lContinue := False;
    end;
    if lContinue then begin
      sMask := 's*.txt';                                          // Switchboard
      slFileList.Clear;
      GetFileList(sPath, sMask, slFileList);
      if slFileList.Count > 0 then
        sSwitchboardFile := slFileList[0]
      else
        lContinue := False;
    end;
    slFileList.Free;
    if lContinue and FileExists(sRentFile) and FileExists(sCallFile) and
      FileExists(sSwitchboardFile) then begin
      if not GetReferences(nBatch) then begin
        MessageDlg('An error occurred when getting data references',
                        mtWarning, [mbAbort], 0 );
      end
      else begin
      DeleteFromDC( nBatch, slCust, lContinue);
      slCust.Clear;
      slCust.Add('SELECT tServiceID.ServiceTypeID, '+
                         'tServiceType.Description AS ServiceTypeDesc, '+
                         'tGroup.CustomerID');                             // 0.
      slCust.Add('FROM tServiceID');                                       // 1.
      slCust.Add('LEFT JOIN tServiceType ON '+
                       'tServiceID.ServiceTypeID = tServiceType.ID');      // 2.
      slCust.Add('LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID');
      slCust.Add('LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID');
      slCust.Add('LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID');   // 5.
      slCust.Add('WHERE tServiceID.ServiceID LIKE ''''');                  // 6.
      slCust.Add('  AND tServiceType.SystemID = '+ UC_SYSTEMID);           // 7.
      slCust.Add('  AND tServiceID.Active = 1');                           // 8.
      
      // Process the Calls last - it is really BIG !!!
      GetUniCRent(nBatch,dStartDate,dEndDate,
        BatchDescription,sRentFile,lContinue);
      if lContinue then GetUniCSwitchboard(nBatch,dStartDate,dEndDate,
        BatchDescription,sSwitchboardFile,lContinue);
      if lContinue then GetUniCCalls(nBatch,dStartDate,dEndDate,
        BatchDescription,sCallFile,lContinue);
      if lContinue then begin
        MessageForm := TMessagesForm.Create(MainForm);
        MessageForm.Caption := 'Importing VicTrack PABX charges';
        MessageForm.Msg.Caption := 'Updating tBatch rates ...';
        MessageForm.Show;
        MainForm.ProcessWinMessages;

        // Now update the values stored in the tBatch table.
        // slCust no longer used, so recycle it as a temporary stringlist.
        slCust.Clear;
        slCust.Add('UPDATE tBatch');
        slCust.Add('SET GSTAmount = BatchRate.TotalGST,');
        slCust.Add(' TotalPayable = BatchRate.TotalIncGST');
        slCust.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+
          'AS TotalIncGST,');
        slCust.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST');
        slCust.Add('FROM tDataCollection');
        slCust.Add('WHERE tDataCollection.BatchID = '+
          IntToStr(nBatch) + ') AS BatchRate');
        slCust.Add('WHERE tBatch.ID = '+ IntToStr(nBatch));
        qCustID.Close;
        qCustID.SQL := slCust;
        qCustID.ExecSQL;
        MessageForm.Close;
        MessageForm.Free;

        UpdateBatchStatus(nBatch, MainForm.BATCH_STATUS_IMPORT);
        // *********************************************************************
        // *********************************************************************
        // Test for critical errors that prevent progress to the next stage.
        lContinue := False;
        CriticalErrorTest( nBatch, lContinue );
        if lContinue then begin
          MessageDlg('Batch '+ IntToStr(nBatch) +
            ': Passed all tests for Critical Errors.', mtInformation,
            [mbOk], 0);
        end
        else begin
          MessageDlg('Batch '+ IntToStr(nBatch) +
            ': Critical errors found.'#13#10#13#10'These must be resolved ' +
            'before this invoice can be progressed.',
            mtWarning, [mbOk], 0);
        end;  // if lContinue then begin.
      end
      else begin
        slCust.Clear;   // When empty, all entries deleted from tDataCollection.
        lContinue := True;
        DeleteFromDC( nBatch, slCust, lContinue);
      end;  // if lContinue then begin.
      end;  // GetReferences.
    end
    else begin
      MessageDlg('One or more Input files missing or'+#13+#10+
                 '   references not available.', mtWarning, [mbAbort], 0 );
    end;  // if lContinue and FileExists(sRentFile) and..then begin..else.
  end;  // if (Length(Trim(sFile)) > 0) then begin.
  // House keeping.
  qCustID.Close;    // Used in GetCustomerID().
  qCustID.Free;
  slCust.Free;      // Used in GetCustomerID().
end;  // procedure UniCommsInvoice.


end.  // TelmaxUniComms.pas