unit TelstraMobileEBS;
// Revision History.
{
RJC 090504: Calls - DialledNumber shifted 3 Chars left, and Origin 2 Chars left
                    a/c errors in field for SMS calls.
}


interface
                                
uses
  Messages, SysUtils, Classes, Graphics, Controls, Dialogs,
  db, dbTables;

type
  TServiceDetails = record
    Custno   : string;
    ServCode : string;
    ServiceID: string;
    RentFrom : TDateTime;
    RentTill : TDateTime;
    CallFrom : TDateTime;
    CallTill : TDateTime;
  end;

  TCallHeaderType = record
    SequenceNo      : string[7];
    ServiceID       : string[17];
    TxnDate         : string[8];
    TxnTime         : string[5];
    Origin          : string[20];
    Duration        : string[9];
    CallCode        : string[2];
    RateDescription : string[83];  // Was 32: RJC-080706.
    DialledNumber   : string[17];  // Temporary variable only.
    ServiceNotes    : string[80];
    AmountExGST     : string[13];
    GSTAmount       : string[13];
    AmountIncGST    : string[13];
    ToDate          : string[8];
  end;

  procedure GetDetailMobileEBS(const FName:String;
    D:TDatabase;
    nBatchNumber : LongInt;
    var lReturnCode:Boolean);
  procedure BillingInputMobileEBS( stInFiles: Tstrings; nBatch: LongInt );
  procedure TelstraInvoiceMobileEBS( nBatch: LongInt; sDirectory: string );


implementation

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

var
  // Declare static variables as frequently reset and reused.
  nCount: Integer;                    // Current status for frmProgress.
  nFactor: Real;                      // Weighting for nCount in progress.
  frmProgress: TProgressForm;         // frmProgress report to the operator.
  nSupplierID: integer;              // Set in BillingInput().
  sSupplierID: string;               // Set in BillingInput().
  qryTelstra : TQuery;               // Set in BillingInput().
  sRentCodeID : string;              // Set in BillingInput().
  sCallCodeID : string;              // Set in BillingInput().
  sOtherCodeID : string;             // Set in BillingInput().
  sDiscountCodeID : string;          // Set in BillingInput().
  sSupplierRentCodeID : string;      // Set in BillingInput().
  sSupplierCallCodeID : string;      // Set in BillingInput().
  sSupplierOtherCodeID : string;     // Set in BillingInput().
  sSupplierDiscountCodeID : string;  // Set in BillingInput().
  sAccountNumber : string;           // Set in BillingInput().
  lRealValue : boolean;    // Used before converting Duration to seconds.

  sLastCallCode : string;
  sLastCallTransactionTypeID : string;
  sLastDiscountTransactionTypeID : string;
  sLastSupplierCallCodeID : string;
  sLastSupplierDiscountCodeID : string;
  
procedure GetDetailMobileEBS
          (
          const FName: String;
          D: TDatabase;
          nBatchNumber : LongInt;
          var lReturnCode: Boolean
          );
const
  PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%.
var
  nFileSize: Integer;                  // File size in bytes.
  sPartFileName : string;
  sPartResultSet : string;
  EntryCode : string;
  nBackSlashAt : Integer;
  TelstraFile: TextFile;               // Used for accessing the data.
  CurrentFile: String;                 // Currently open file.
  FileIteration: Integer;              // Current file iteration being processed
  T: string;                           // Line of data read from TextFile.
  SubString: String;                   // A (temp) substring of T.
  vSubString : variant;                // - ditto, used in the Locate() function.
  curValue: Currency;                  // A (temp) currency conversion value.
  nSoFar: Integer;                     // Status - num of records processed.
  dBilling: TDateTime;                 // Billing date.
//  dStartBillingPeriod: TDateTime;      // Start of the payment billing period.
//  dEndBillingPeriod: TDateTime;        // End of the payment billing period.
  sBatch : string;
  LastEntryIs_1 : boolean;
  CallHeader : TCallHeaderType;
  sLastDetailCode : string[2];         // To detect a header line AFTER details.

  DBfromDate : string;                 // Section DB - save read dates.
  DBtoDate : string;

  sInvoiceYear : string;
  sInvoiceNextYear : string;
  sInvoiceMonth : string;
  nInvoiceMonth : integer;

  aResultSet : Array of TServiceDetails;
  nResultSet : Integer;
  nMaxResults : Integer;
  nStartSearchAt : Integer;
  lFound : Boolean;
  dMinDate : TDateTime;
  nCommaPosition : integer;
  sListValues : String;
  sListFields : String;
  FieldStringList : TStringList;
  ValueStringList : TStringList;
  qryStringList : TStringList;
  slAddFieldNames : TStringList;         // Calls to AddToTable() only.
  slAddFieldValues : TStringList;        // Calls to AddToTable() only.
  slMonth : TStringList;
  sMonth : string;
  sServiceDate : string;
  qryDC : TQuery;
  nListValues : integer;
  nDummy : Real;
  sInputLineCode : string;
  lSaveToTable : boolean;
  lDataSaved : boolean;
  lContinue : boolean;
  sRateDescription : string;
  sRateDescEnhancement : string;
  sLastService : string;
  sLastServiceID : string;

  nDataCollectionID : LongInt;
//  sLastCallCode : string;
//  sLastTransactionTypeID : string;
  lNextAccountNumber : boolean;
  sNextAccountNumber : string;
  nNextAccountNumber : integer;
begin
  lNextAccountNumber := False;                   // Initiate variables.
  sNextAccountNumber := '';
  nNextAccountNumber := 0;
  nInvoiceMonth := 0;

  sLastDetailCode := '  ';
  sRateDescEnhancement := '';
  nMaxResults := 0;
  sBatch := IntToStr(nBatchNumber);
  lDataSaved := False;

  FieldStringList := TStringList.Create;
  ValueStringList := TStringList.Create;
  qryStringList := TStringList.Create;
  slAddFieldNames := TStringList.Create;         // Calls to AddToTable() only.
  slAddFieldValues := TStringList.Create;        // Calls to AddToTable() only.
  slMonth := TStringList.Create;
  slMonth := fShortMonthLoad(slMonth);

  // Establish the query to be used to save the data read.
  qryDC := TQuery.Create(MainForm);
  qryDC.DatabaseName := 'dbPPdata';

  lReturnCode := False;
  CurrentFile := Fname;
  FileIteration := 0;            // Original file extension, then 001,002, etc.
  dMinDate := EncodeDate(1980,1,1);
  while FileExists(CurrentFile) do begin
    // Read the file size to determine the approximate number of records.
    nFileSize := GetFileSize(CurrentFile);
    // Now we can calculate the factor for the progress bar to be displayed.
    // From prev. data: 8876 lines in a file of 1,585,557 bytes.
    // Estimate the number of data lines in the file.
    if (nFileSize > 0) then begin
      nFactor := 100.0 / 8876.0 * (1585557.0/nFileSize);
    end
    else begin
      nFactor := 100.0;
    end;
    nCount := 0;
    sPartFileName := CurrentFile;
    while Pos('\',sPartFileName)>0 do begin
      nBackSlashAt  := Pos('\',sPartFileName);
      sPartFileName := Copy(sPartFileName,
        nBackSlashAt+1, Length(sPartFileName)-nBackSlashAt);
    end;
    frmProgress.Caption := 'Telstra Mobile Services Batch: '+sBatch;
    frmProgress.Msg.Caption := 'Reading ' + sPartFileName + ' ...';
    frmProgress.ProgressBar1.Position := 0;
    frmProgress.Msg.Refresh;

    // Open the input (text) billing file for reading.
    AssignFile( TelstraFile, CurrentFile );
    Reset( TelstraFile );
    try
      Readln( TelstraFile, T );
      // Test for the original EBS file format for a Mobile or Data.
      if (Copy(T,1,8)='HDR:RACE') then begin
        // This is an account for Mobile services.
        lContinue := True;
      end
      else begin
        lContinue := (Copy(T,1,8)<>'HDR:FCAB');
        if not lContinue then begin
          frmProgress.Hide;
          MessageDlg('Incorrect input file type.'+#13#10+
            'This is a Fixed Calls type file. Try again.', mtWarning, [mbOK],0);
        end
        else begin
          // It may be the old file or a new combined file. Continue to test.
          // Ensure that this is not a CSV file format (OBS billing)
          lContinue := (Pos(',',T)=0);
          if not lContinue then begin
            frmProgress.Hide;
            MessageDlg('Incorrect input file type.'+#13#10+
              'File type DAT is required. Try again.', mtWarning, [mbOK],0);
          end
          else begin
            // Ensure that this is a Mobile file format.
            lContinue := (Copy(T,1,2)='RB');
            if not lContinue then begin
              frmProgress.Hide;
              MessageDlg('Incorrect input file type.'+#13#10+
              'This is NOT a Mobile-type file. Try again.',mtWarning, [mbOK],0);
            end;
          end;
        end;
      end; // HDR:RACE.
      if not lContinue then begin
        // Account selected is not in this file.
        frmProgress.Hide;
        MessageDlg('The Account specified is not in the file selected.'+#13#10+
          'Please try again.',
          mtWarning, [mbOK],0);
      end
      else begin   // lContinue = True.
      lContinue := False;  // Reset then test for correct account.
      // Skip to the Header Line and Determine the billing date only once.
      // Make sure that we find the valid Account first.
      while not lContinue and
        (not ((Length(Trim(T))=0) and Eof( TelstraFile ))) do begin
        lContinue := (Trim(Copy(T,37,10)) = sAccountNumber);
        if not lContinue then begin
          Readln( TelstraFile, T);
          // Report progress to the user.
          Inc(nCount);
          nDummy := nFactor * nCount;
          nSoFar := Round( nDummy );
          // Do not know how big the file is - do not exceed 100%.
          if (nSoFar > PROGRESS_MAX_C) then begin
            frmProgress.ProgressBar1.Position := PROGRESS_MAX_C;
          end
          else begin
            frmProgress.ProgressBar1.Position := nSoFar;
          end;  // PROGRESS_MAX_C.
        end;  // not lContinue.
      end;  // while <> SubString.
      if lContinue then begin

//      D.StartTransaction;
      // Continue to search through the remaining text of the file
      // for occurrences of the search string.  On each find, process
      // the resultant string.
      //****************************************************************
      //********* START READING BILLS **********************************
      //****************************************************************
      try
        repeat
          frmProgress.ProcessWinMessages(MainForm);
          lSaveToTable := True;
          // Read only relevant text into the database.
          // If T is shorter than indicated, Copy() returns an empty string.
          sInputLineCode := UpperCase(Copy(T, 82, 2));

          if (Length(sInvoiceYear) = 0) then begin
            // Only set the values once.
            sInvoiceYear := '20'+Copy(T,55,2);
            sInvoiceNextYear := IntToStr(StrToInt(sInvoiceYear)+1);
            sInvoiceMonth := Copy(T,57,2);
            nInvoiceMonth := StrToInt(sInvoiceMonth);
          end;

          // CALL DETAILS.
          if (sInputLineCode='2D') then begin
            sInputLineCode := UpperCase(Trim(Copy(T, 82, 5)));
            if (Pos(' ',sInputLineCode) > 0) then begin
              lSaveToTable := False;
            end
            else if (Length(Trim(Copy(T, 87, 5))) = 0) then begin
              // Column heading, skip on.
              if (Copy(sInputLineCode,5,1) = 'D') then begin
                // Heading - Read the transaction type description.
                sRateDescription := CleanString(Trim(Copy(T,92,30)));
              end;  // ..and ignore any totals etc.
              lSaveToTable := False;
            end
            else if (Trim(Copy(T, 87, 5)) = 'SEQ') then begin
              // Column heading, skip on.
              lSaveToTable := False;
            end
            else begin
              // This line is a detail call & not a header/summary - save it.
              FieldStringList.Clear;
              ValueStringList.Clear;
              sListFields := '(';
              sListValues := 'VALUES (';
              SubString := Trim(Copy(T,87,5));
              if (Length(SubString) > 0) then begin
                sListFields := sListFields + 'SequenceNo,';
                sListValues := sListValues + '''' + SubString +''',';
              end;
              SubString := Trim(Copy(T,61,17));
              if not (SubString = sLastService) then begin
                sLastService := SubString;
                sLastServiceID := AccountFormat(sLastService);
              end;
              sListFields := sListFields + 'ServiceID,';
              sListValues := sListValues +''''+ sLastServiceID +''',';
              // Read the code common for each detail and summary - used below
              // when the summary (type "1") is read.
              SubString := Trim(Copy(T,108,13)); // Prev.110 Amended RJC 090504.
              if (Length(SubString) > 0) then begin
                sListFields := sListFields + 'Origin,';
                sListValues := sListValues +''''+ CleanString(SubString) +''',';
              end;
              SubString := Copy(T,93,8);
              if (Length(SubString) > 0) then begin
                // Only 2-digit year, add 2000.
                SubString := Copy(SubString,1,6) +'20'+ Copy(SubString,7,2);
                sListFields := sListFields + 'TxnDate,';
                sListValues := sListValues +''''+ SubString +''',';
              end;

              SubString := Trim(Copy(T,145,9));
//              if (Length(SubString) > 0) then begin
              sRateDescEnhancement := '';
              if (Length(SubString) = 0) or (Pos(':',SubString) > 0) then begin
//                if (Pos(':',SubString) > 0) then begin
                if (Length(SubString) > 0) then begin


if (Copy(substring,1,1) = 'C') then begin
substring := Copy(substring,1,length(substring));
end;

                  ProveTimeString(SubString,lRealValue);
                  if lRealValue then begin
                    SubString := ConvertTimeToSecs(CleanString(SubString));
                    sListFields := sListFields + 'Duration,';
                    sListValues := sListValues +''''+ SubString +''',';
                  end;
                end;  // Duration.
                // Time.
                SubString := Copy(T,103,5);
                if (Length(SubString) > 0) then begin
                  sListFields := sListFields + 'TxnTime,';
                  sListValues := sListValues +''''+ SubString +':00'',';
                end;
                // Peak and RateDescription.

                SubString := sRateDescription;
                if (Length(Trim(Copy(T,141,4))) > 0) then
                  SubString := SubString +' - '+ Trim(Copy(T,141,4));
                if (Length(SubString) > 0) then begin
                  sListFields := sListFields + 'Peak,';
                  if (Right(SubString,2) = ' P') then
                    sListValues := sListValues +'1,'
                  else
                    sListValues := sListValues +'0,';
                  sListFields := sListFields + 'RateDescription,';
                  sListValues := sListValues +''''+ SubString+''',';
                end;



              end
              else begin
                // This is data usage - WAP.
                sRateDescEnhancement := ' '+CleanString(SubString);

                // Define rate charged and full rate description.
                SubString := sRateDescription; // Changed to 133 a/c file change.
                if (Length(Trim(Copy(T,133,4))) > 0) then
                  SubString := SubString +' - '+ Trim(Copy(T,133,4));
                if (Length(SubString) > 0) then begin
                  sListFields := sListFields + 'Peak,';
                  if (Right(SubString,2) = ' P') then
                    sListValues := sListValues +'1,'
                  else
                    sListValues := sListValues +'0,';
                  sListFields := sListFields + 'RateDescription,';
                  sListValues := sListValues +''''+
                                 SubString+sRateDescEnhancement+''',';
                end;  //Copy(T,133,4).
                // Transaction time.
                SubString := Copy(T,102,5);  // Changed to 102 a/c file change.
                if (Length(SubString) > 0) then begin
                  sListFields := sListFields + 'TxnTime,';
                  sListValues := sListValues +''''+ SubString +':00'',';
                end;

                // Duration.
                SubString := Trim(Copy(T,136,9));


if (Copy(substring,1,1) = 'C') then begin
substring := Copy(substring,1,length(substring));
end;


                ProveTimeString(SubString,lRealValue);
                if ((Length(SubString) > 0) and lRealValue) then begin




                  SubString := ConvertTimeToSecs(CleanString(SubString));
                  sListFields := sListFields + 'Duration,';
                  sListValues := sListValues +''''+ SubString +''',';
                end;  // Duration.



              end;




              SubString := CleanString(Copy(sInputLineCode,1,4));
              if (Length(SubString) > 0) then begin
                sListFields := sListFields + 'CallCode,';
                sListValues := sListValues +''''+ SubString +''',';
                if (sLastCallCode <> SubString) then begin
                  sLastCallCode := SubString;
                  vSubString := SubString;
                  with qryTelstra do
                    lFound := Locate('Code',vSubString,[loCaseInsensitive]);
                  if lFound then begin
                    sLastCallTransactionTypeID :=
                      qryTelstra.FieldByName('TransactionType').AsString;
                    sLastSupplierCallCodeID :=
                      qryTelstra.FieldByName('SupplierCodeID').AsString;
                  end
                  else begin
                    sLastCallTransactionTypeID := sCallCodeID;
                    sLastSupplierCallCodeID := sSupplierCallCodeID;
                    slAddFieldNames.Clear;
                    slAddFieldValues.Clear;
                    slAddFieldNames.Add('Code');
                    slAddFieldValues.Add(''''+sLastCallCode+'''');
                    slAddFieldNames.Add('AmountCategory');
                    slAddFieldValues.Add('''CALLS''');
                    slAddFieldNames.Add('Description');
                    slAddFieldValues.Add('''CALLS''');
                    slAddFieldNames.Add('TransactionTypeID');
                    slAddFieldValues.Add(sLastCallTransactionTypeID);
                    slAddFieldNames.Add('SupplierID');
                    slAddFieldValues.Add(sSupplierID);
                    slAddFieldNames.Add('Active');
                    slAddFieldValues.Add('1');
                    AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues);
                    qryTelstra.Close;
                    qryTelstra.Open;
                    with qryTelstra do
                      Locate('Code',vSubString,[loCaseInsensitive]);
                  end;  // if (not) lFound.
                end;  // if (sLastCallCode <> SubString) then begin.
                sListFields := sListFields + 'TransactionTypeID,'+
                  'SupplierServiceCodeID,';
                sListValues := sListValues + sLastCallTransactionTypeID+','+
                  sLastSupplierCallCodeID+',';
              end
              else begin
                sListFields := sListFields + 'TransactionTypeID,'+
                  'SupplierServiceCodeID,';
                sListValues := sListValues + sCallCodeID+','+
                  sSupplierCallCodeID+',';
              end;  // CallCode.

              SubString := Trim(Copy(T,121,17)); // Prev.124 Amended RJC 090504.
              if (Length(SubString) > 0) then begin
                sListFields := sListFields + 'DialledNumber,';
                sListValues := sListValues +''''+
                CleanString(SubString) +''',';
              end;
              FieldStringList.Add(sListFields);
              ValueStringList.Add(sListValues);
              sListFields := '';
              sListValues := '';

              SubString := Trim(Copy(T,166,11));
              sListFields := sListFields + 'AmountExGST,';
              sListValues := sListValues + SubString +',';
              SubString := Trim(Copy(T,178,11));
              curValue := curFormat(StrToFloat(SubString));
              sListFields := sListFields + 'GSTAmount,GSTFlag,';
              if (curValue = 0.00) then begin
                sListValues := sListValues + SubString + ',''N'',';
              end
              else begin
                sListValues := sListValues + SubString + ',''Y'',';
              end;
              SubString := Trim(Copy(T,189,11));
              sListFields := sListFields + 'AmountIncGST,';
              sListValues := sListValues + SubString +',';
              SubString := '20'+ Trim(Copy(T,55,6));
              sListFields := sListFields + 'ToDate,';
              sListValues := sListValues +''''+ YMD2DMY(SubString) +''',';
            end;  // ..sInputLineCode contents.
          end  // if (sInputLineCode='2D') then.


          else if (sInputLineCode='2S') or (sInputLineCode='1A') or
            (sInputLineCode='2A') then begin
            // This line is a summary subtotal - check it for Discounts.
            sInputLineCode := UpperCase(Trim(Copy(T, 82, 5)));

            // SERVICES.
            if (sInputLineCode = '2S02D') or
              (sInputLineCode = '2S02V')then begin
              // This is a discount or rounding item, or a Blackberry item.
              FieldStringList.Clear;
              ValueStringList.Clear;
              sListFields := '(';
              sListValues := 'VALUES (';
              SubString := Trim(Copy(T,61,17));
              if not (SubString = sLastService) then begin
                sLastService := SubString;
                sLastServiceID := AccountFormat(sLastService);
              end;
              sListFields := sListFields + 'ServiceID,';
              sListValues := sListValues +''''+ sLastServiceID +''',';
              SubString := CleanString(Copy(sInputLineCode,1,4));
              if (Length(SubString) > 0) then begin
                sListFields := sListFields + 'CallCode,';
                sListValues := sListValues +''''+ SubString +''',';
                if (sLastCallCode <> SubString) then begin
                  sLastCallCode := SubString;
                  vSubString := SubString;
                  with qryTelstra do
                    lFound := Locate('Code',vSubString,[loCaseInsensitive]);
                  if lFound then begin
                    sLastDiscountTransactionTypeID :=
                      qryTelstra.FieldByName('TransactionType').AsString;
                    sLastSupplierDiscountCodeID :=
                      qryTelstra.FieldByName('SupplierCodeID').AsString;
                  end
                  else begin
                    sLastDiscountTransactionTypeID := sDiscountCodeID;
                    sLastSupplierDiscountCodeID := sSupplierDiscountCodeID;
                    slAddFieldNames.Clear;
                    slAddFieldValues.Clear;
                    slAddFieldNames.Add('Code');
                    slAddFieldValues.Add(''''+sLastCallCode+'''');
                    slAddFieldNames.Add('AmountCategory');
                    slAddFieldValues.Add('''OTHER''');
                    slAddFieldNames.Add('Description');
                    slAddFieldValues.Add('''OTHER''');
                    slAddFieldNames.Add('TransactionTypeID');
                    slAddFieldValues.Add(sLastDiscountTransactionTypeID);
                    slAddFieldNames.Add('SupplierID');
                    slAddFieldValues.Add(sSupplierID);
                    slAddFieldNames.Add('Active');
                    slAddFieldValues.Add('1');
                    AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues);
                    with qryTelstra do begin
                      Close;
                      Open;
                      Locate('Code',vSubString,[loCaseInsensitive]);
                    end;  // qryTelstra.
                  end;  // if (not) lFound.
                end;  // if (sLastCallCode <> SubString) then begin.
                sListFields := sListFields + 'TransactionTypeID,'+
                  'SupplierServiceCodeID,';
                sListValues := sListValues + sLastDiscountTransactionTypeID+','+
                  sLastSupplierDiscountCodeID+',';
              end
              else begin
                sListFields := sListFields + 'TransactionTypeID,'+
                  'SupplierServiceCodeID,';
                sListValues := sListValues + sDiscountCodeID+','+
                  sSupplierDiscountCodeID+',';
              end;  // CallCode.
              sRateDescription := CleanString(Trim(Copy(T,92,30)));
              SubString := sRateDescription;
              if (Length(SubString) > 0) then begin
                sListFields := sListFields + 'RateDescription,';
                sListValues := sListValues +''''+ SubString +''',';
              end;
              FieldStringList.Add(sListFields);
              ValueStringList.Add(sListValues);
              sListFields := '';
              sListValues := '';

              SubString := Trim(Copy(T,166,11));
              if (Length(SubString) = 0) then SubString := '0';
              sListFields := sListFields + 'AmountExGST,';
              sListValues := sListValues + SubString +',';
              SubString := Trim(Copy(T,178,11));
              if (Length(SubString) = 0) then SubString := '0';
              curValue := curFormat(StrToFloat(SubString));
              sListFields := sListFields + 'GSTAmount,GSTFlag,';
              if (curValue = 0.00) then begin
                sListValues := sListValues + SubString + ',''N'',';
              end
              else begin
                sListValues := sListValues + SubString + ',''Y'',';
              end;
              SubString := Trim(Copy(T,189,11));
              if (Length(SubString) = 0) then SubString := '0';
              sListFields := sListFields + 'AmountIncGST,';
              sListValues := sListValues + SubString +',';
              // Find the applicable dates for the services.
              SubString := Trim(Copy(T,125,22));
              if (Length(SubString) < 16) then begin
                SubString := '20'+ Trim(Copy(T,55,6));
                sListFields := sListFields + 'ToDate,';
                sListValues := sListValues +''''+ YMD2DMY(SubString) +''',';
              end
              else begin
                sMonth := fMonthNumber(Copy(SubString,4,3), slMonth);
                if ((StrToInt(sMonth) < 7) and (nInvoiceMonth > 6)) then begin
                  // Next year after invoice date.
                  sServiceDate := YMD2DMY(sInvoiceNextYear + sMonth +
                                      Right('0'+Trim(Copy(SubString,1,2)),2));
                end
                else begin
                  // Same year as invoice date.
                  sServiceDate := YMD2DMY(sInvoiceYear + sMonth +
                                      Right('0'+Trim(Copy(SubString,1,2)),2));
                end;
                sListFields := sListFields + 'FromDate,';
                sListValues := sListValues +''''+ sServiceDate +''',';
                sMonth := fMonthNumber(Copy(SubString,14,3), slMonth);
                if ((StrToInt(sMonth) < 7) and (nInvoiceMonth > 6)) then begin
                  // Next year after invoice date.
                  sServiceDate := YMD2DMY(sInvoiceNextYear + sMonth +
                                      Right('0'+Trim(Copy(SubString,11,2)),2));
                end
                else begin
                  // Same year as invoice date.
                  sServiceDate := YMD2DMY(sInvoiceYear + sMonth +
                                      Right('0'+Trim(Copy(SubString,11,2)),2));
                end;
                sListFields := sListFields + 'ToDate,';
                sListValues := sListValues +''''+ sServiceDate +''',';
              end;
            end  // 2s02d or 2s02v (Services).

            // DISCOUNTS
            else if ((sInputLineCode = '2S10V') and
              ((Pos('DISCOUNT', UpperCase(Trim(Copy(T, 90, 35)))) > 0) or
              (Pos('REBATE', UpperCase(Trim(Copy(T, 90, 35)))) > 0) or
              (Pos('INCLUDED', UpperCase(Trim(Copy(T, 90, 35)))) > 0) or
              (Pos('ALLOWANCE', UpperCase(Trim(Copy(T, 90, 35)))) > 0))) or
              (sInputLineCode = '1A02V') or ((sInputLineCode = '2A02V') and
              (Pos('ROUND', UpperCase(Trim(Copy(T, 90, 35))))>0)) then begin
              // This is a discount item.
              FieldStringList.Clear;
              ValueStringList.Clear;
              sListFields := '(';
              sListValues := 'VALUES (';
              SubString := Trim(Copy(T,61,17));
              if not (SubString = sLastService) then begin
                sLastService := SubString;
                sLastServiceID := AccountFormat(sLastService);
              end;
              sListFields := sListFields + 'ServiceID,';
              sListValues := sListValues +''''+ sLastServiceID +''',';
              SubString := CleanString(Copy(sInputLineCode,1,4));
              if (Length(SubString) > 0) then begin
                sListFields := sListFields + 'CallCode,';
                sListValues := sListValues +''''+ SubString +''',';
                if (sLastCallCode <> SubString) then begin
                  sLastCallCode := SubString;
                  vSubString := SubString;
                  with qryTelstra do
                    lFound := Locate('Code',vSubString,[loCaseInsensitive]);
                  if lFound then begin
                    sLastDiscountTransactionTypeID :=
                      qryTelstra.FieldByName('TransactionType').AsString;
                    sLastSupplierDiscountCodeID :=
                      qryTelstra.FieldByName('SupplierCodeID').AsString;
                  end
                  else begin
                    sLastDiscountTransactionTypeID := sDiscountCodeID;
                    sLastSupplierDiscountCodeID := sSupplierDiscountCodeID;
                    slAddFieldNames.Clear;
                    slAddFieldValues.Clear;
                    slAddFieldNames.Add('Code');
                    slAddFieldValues.Add(''''+sLastCallCode+'''');
                    slAddFieldNames.Add('AmountCategory');
                    slAddFieldValues.Add('''OTHER''');
                    slAddFieldNames.Add('Description');
                    slAddFieldValues.Add('''OTHER''');
                    slAddFieldNames.Add('TransactionTypeID');
                    slAddFieldValues.Add(sLastDiscountTransactionTypeID);
                    slAddFieldNames.Add('SupplierID');
                    slAddFieldValues.Add(sSupplierID);
                    slAddFieldNames.Add('Active');
                    slAddFieldValues.Add('1');
                    AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues);
                    with qryTelstra do begin
                      Close;
                      Open;
                      Locate('Code',vSubString,[loCaseInsensitive]);
                    end;  // qryTelstra.
                  end;  // if (not) lFound.
                end;  // if (sLastCallCode <> SubString) then begin.
                sListFields := sListFields + 'TransactionTypeID,'+
                  'SupplierServiceCodeID,';
                sListValues := sListValues + sLastDiscountTransactionTypeID+','+
                  sLastSupplierDiscountCodeID+',';
              end
              else begin
                sListFields := sListFields + 'TransactionTypeID,'+
                  'SupplierServiceCodeID,';
                sListValues := sListValues + sDiscountCodeID+','+
                  sSupplierDiscountCodeID+',';
              end;  // CallCode.
              sRateDescription := CleanString(Trim(Copy(T,92,30)));
              SubString := sRateDescription;
              if (Length(SubString) > 0) then begin
                sListFields := sListFields + 'RateDescription,';
                sListValues := sListValues +''''+ SubString +''',';
              end;
              FieldStringList.Add(sListFields);
              ValueStringList.Add(sListValues);
              sListFields := '';
              sListValues := '';

              SubString := Trim(Copy(T,166,11));
              if (Length(SubString)=0) and
                (Length(Trim(Copy(T,178,11)+Copy(T,189,11)))=0) then begin
                lSaveToTable := False;               // No discount - ignore.
              end
              else begin
                if (Length(SubString) = 0) then SubString := '0';
                sListFields := sListFields + 'AmountExGST,';
                sListValues := sListValues + SubString +',';
                SubString := Trim(Copy(T,178,11));
                curValue := curFormat(StrToFloat(SubString));
                if (Length(SubString) = 0) then SubString := '0';
                sListFields := sListFields + 'GSTAmount,GSTFlag,';
                if (curValue = 0.00) then begin
                  sListValues := sListValues + SubString + ',''N'',';
                end
                else begin
                  sListValues := sListValues + SubString + ',''Y'',';
                end;
                SubString := Trim(Copy(T,189,11));
                if (Length(SubString) = 0) then SubString := '0';
                sListFields := sListFields + 'AmountIncGST,';
                sListValues := sListValues + SubString +',';
                SubString := '20'+ Trim(Copy(T,55,6));
                sListFields := sListFields + 'ToDate,';
                sListValues := sListValues +''''+ YMD2DMY(SubString) +''',';
              end;  // if (Length(SubString)=0) then begin..else.
            end  // sInputLineCode = '2S10V' (Discounts).

            else begin
              lSaveToTable := False;
            end;  // ..else begin.
          end  // ..else if (sInputLineCode='2S') then begin.

          else begin
            lSaveToTable := False;
          end;  // ..else begin.
          if lSaveToTable then begin
            // 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;
            lDataSaved := True;
          end;  // if lSaveToTable then begin.
          Inc(nCount);
          // Report progress to the user.
          nSoFar := Round(nCount * nFactor);
          // Do not know how big the file is - make sure don't exceed 100%.
          if (nSoFar > PROGRESS_MAX_C) then begin
            frmProgress.ProgressBar1.Position := PROGRESS_MAX_C;
          end
          else begin
            frmProgress.ProgressBar1.Position := nSoFar;
          end;
          // Read the next line of text file data.
          Readln( TelstraFile, T );


            if (Length(Trim(T)) > 0) then begin
              lContinue := (Trim(Copy(T,37,10)) = sAccountNumber);
              while not lContinue and not lNextAccountNumber and
                (not ((Length(Trim(T))=0) and Eof( TelstraFile ))) do begin
                SubString := Trim(Copy(T,37,10));
                lContinue := (SubString = sAccountNumber);
                if not lContinue then begin
                  // Wait for 20 lines of the next account before ending processing.
                  if (sNextAccountNumber = SubString) then begin
                    Inc(nNextAccountNumber);
                    lNextAccountNumber := (nNextAccountNumber = 20);
                  end
                  else begin
                    sNextAccountNumber := SubString;
                    nNextAccountNumber := 1;
                  end;
                  Readln( TelstraFile, T);
                  // Report progress to the user.
                  Inc(nCount);
                  nDummy := nFactor * nCount;
                  nSoFar := Round( nDummy );
                  // Do not know how big the file is - do not exceed 100%.
                  if (nSoFar > PROGRESS_MAX_C) then begin
                    frmProgress.ProgressBar1.Position := PROGRESS_MAX_C;
                  end
                  else begin
                    frmProgress.ProgressBar1.Position := nSoFar;
                  end;
                end;  // not lContinue.
              end;  // while not AccountNumber.
            end;  // not Eof.

        until not lContinue or lNextAccountNumber or Eof( TelstraFile );
        lReturnCode := lDataSaved;;
      except    // D.StartTransaction.
        lReturnCode := False;
//        D.Rollback;       // On failure, undo the changes - not useful anyway.
        raise;            // Raise the exception to avoid call to CommitUpdates.
      end;    // D.StartTransaction.

      end;  // lContinue.
      end;  // not lContinue.

      // Now need to process the last line if CR-LF not at end.
      //  BUT - nothing done !
//      D.Commit;
      //****************************************************************
      //*********** END READING BILLS **********************************
      //****************************************************************
    finally    // TelstraFile (try).
      // Done with the input file; close it.
      CloseFile( TelstraFile );
    end;    // TelstraFile (try).
    // Select the next file if exists - extension 001, 002, etc.
    Inc(FileIteration);
    CurrentFile := Copy(CurrentFile,1,Pos('.',CurrentFile));
    CurrentFile := CurrentFile + Right(('00'+IntToStr(FileIteration)),3);
  end;  // if FileExists(CurrentFile) then begin.
end;  // procedure GetDetailMobileEBS.


procedure BillingInputMobileEBS( stInFiles: Tstrings; nBatch: LongInt );
// This program processes the Telstra billing reports supplied on disk
// and places the data into the Temp.dbf database for unverified bill
// information.
//
// The parameter passed contains the names of the input files, which are
// passed on to the ImportFile() procedure.
//
var
  lContinue : Boolean;
  sPartFileName : String;
  nBackSlashAt: Integer;
  nPos : integer;
begin
  // initialise the Static table elements defined as Private for this form.
  qryTelstra := TQuery.Create(MainForm);
  qryTelstra.DatabaseName := 'dbPPdata';
  qryTelstra.SQL.Add('SELECT id');
  qryTelstra.SQL.Add('FROM tSupplier');
  qryTelstra.SQL.Add('WHERE SupplierName LIKE ''TELSTRA%''');
  qryTelstra.Open;
  if qryTelstra.Eof then begin
    MessageDlg('TELSTRA not found in the tSupplier table. Processing stopped.',
      mtWarning, [mbOk], 0);
  end
  else begin
    nSupplierID := qryTelstra['id'];
    sSupplierID := IntToStr(nSupplierID);

    with qryTelstra do begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT tValidAccountNumber.AccountNo');
      SQL.Add('FROM tValidAccountNumber');
      SQL.Add('INNER JOIN tBatch ON tValidAccountNumber.BatchTypeID = tBatch.BatchTypeID');
      SQL.Add('WHERE tBatch.ID = '+IntToStr(nBatch));
      Open;
      if Eof then begin
        MessageDlg('BatchType not found in the tValidAccountNumber table. '+
          'Processing stopped.',
          mtWarning, [mbOk], 0);
        lContinue := False;
      end
      else begin
        sAccountNumber := Trim(FieldByName('AccountNo').AsString);
        nPos := Pos(' ',sAccountNumber);
        while (nPos > 0) do begin
          sAccountNumber := Copy(sAccountNumber,1,nPos-1) +
                            Copy(sAccountNumber,nPos+1,Length(sAccountNumber)-nPos);
          nPos := Pos(' ',sAccountNumber);
        end;  // nPos.
        lContinue := True;
      end;  // Eof.
      Close;
    end;  // qryTelstra.
  end;
  if lContinue then begin

    with qryTelstra do begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT Code, CAST(TransactionTypeID AS VARCHAR(20)) '+
        'AS TransactionType, CAST(ID AS VARCHAR(20)) AS SupplierCodeID');
      SQL.Add('FROM tSupplierServiceCode');
      SQL.Add('WHERE SupplierID = '+sSupplierID);
      SQL.Add('AND Active = 1');
      SQL.Add('AND Description LIKE ''RENT''');
      Open;
    end;  // qryTelstra.
    if qryTelstra.Eof then sRentCodeID := 'NULL'
    else begin
      sRentCodeID := Trim(qryTelstra['Code']);
      sSupplierRentCodeID := Trim(qryTelstra['SupplierCodeID']);
    end;
    qryTelstra.Close;
    qryTelstra.SQL.Strings[qryTelstra.SQL.Count-1] :=
      'AND Description LIKE ''CALLS''';
    qryTelstra.Open;
    if qryTelstra.Eof then sCallCodeID := 'NULL'
    else begin
      sCallCodeID := Trim(qryTelstra['Code']);
      sSupplierCallCodeID := Trim(qryTelstra['SupplierCodeID']);
    end;
    qryTelstra.Close;
    qryTelstra.SQL.Strings[qryTelstra.SQL.Count-1] :=
      'AND Description LIKE ''Unknown%''';
    qryTelstra.Open;
    if qryTelstra.Eof then sOtherCodeID := 'NULL'
    else begin
      sOtherCodeID := qryTelstra['Code'];
      sSupplierOtherCodeID := Trim(qryTelstra['SupplierCodeID']);
    end;
    qryTelstra.Close;
    qryTelstra.SQL.Strings[qryTelstra.SQL.Count-1] :=
      'AND Description LIKE ''MISCELLANEOUS''';
    qryTelstra.Open;
    if qryTelstra.Eof then sDiscountCodeID := 'NULL'
    else begin
      sDiscountCodeID := Trim(qryTelstra['Code']);
      sSupplierDiscountCodeID := Trim(qryTelstra['SupplierCodeID']);
    end;
    qryTelstra.Close;
    qryTelstra.SQL.Delete(qryTelstra.SQL.Count-1);
    qryTelstra.Open;

    // Initialise the progress bar to display to the operator.
    frmProgress := TProgressForm.Create(MainForm);
    frmProgress.btnCancel.Visible := False;
    frmProgress.Show;

    sPartFileName := stInFiles.Strings[0];
    while Pos('\',sPartFileName)>0 do begin
      nBackSlashAt  := Pos('\',sPartFileName);
      sPartFileName := Copy(sPartFileName,
        nBackSlashAt+1, Length(sPartFileName)-nBackSlashAt);
    end;  // while Pos('\',sPartFileName)>0 do begin.
    frmProgress.Msg.Caption := 'Reading ' + sPartFileName + ' ...';
    frmProgress.ProgressBar1.Position := 0;
    frmProgress.Msg.Refresh;
    // Now move the text data into the Table.
//
// MainForm.StartTime;
//
    GetDetailMobileEBS( stInFiles.Strings[0], DM.dbPPdata, nBatch, lContinue);
//
// MainForm.EndTime;
//

    frmProgress.Hide;
    frmProgress.Free;
    if lContinue then begin
      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;
    end;  // lContinue.
  end;  // lContinue.
end;  // BillingInputMobileEBS.


procedure TelstraInvoiceMobileEBS( nBatch: LongInt; sDirectory: string );
// This function prompts the user for the name of the Telstra supplied
// text file and then calls the BillingInput function (which reads the
// file into a table and controls its processing).
var
  lContinue : Boolean;
  stFiles : TStringList;
  OpenDlg: TOpenDialog;
begin
  // Create the stringlist which contains the names of selected filenames.
  stFiles := TStringList.Create;
  try
    // Now, we must establish which files are to be processed.
    // Select the source directory.
    OpenDlg := TOpenDialog.Create(MainForm);
    OpenDlg.Filter := 'Telstra Billing file (*.dat)|*.DAT|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 Telstra Account Data File';
    OpenDlg.Options := [ofFileMustExist, ofNoChangeDir];
    // Read and save the user's choices before continuing.
    lContinue := OpenDlg.Execute;
    stFiles.Text := OpenDlg.Files.Text;
    OpenDlg.Free;
    if lContinue then begin
      lContinue := stFiles.Count > 0;
      // Now call for processing of the input files.
      if lContinue then begin
        BillingInputMobileEBS(stFiles, nBatch);
      end;  // if lContinue then.
    end;  // if lContinue then.
  finally
    stFiles.Free;
  end;
end;    // procedure TelstraInvoiceMobileEBS.


end.