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.