unit TelstraFixedEBS; // Revision History. { RJC 120624: Copied to TelstraPhoneEBS to preserve for original format invoices. Then removed processing for summaries that are removed in the new OBS format. OBS may produce input files as CSV or long strings. Most changes are for Calls (DC). RJC 120325: BillingPhoneInput - Replaced TransactionType with Code for default rent, call, other, discount Telstra codes. GetPhoneAccountBills - Added reading code from file, not default. RJC 111204: Telstra() split into 2-TelstraPhoneEBS & TelstraMobileEBS to allow separate programs for fixed & mobile services with the old EBS billing files. This is to allow multiple invoices in hte one file and code added to sift through (same as for new OBS system). The ImportFile & GetMobileAccountBills routines also deleted. 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; procedure GetDetailFixedEBS( const FName:String; D:TDatabase; nBatchNumber:LongInt; var lReturnCode:Boolean); // Includes SaveSummaryChargesFixedEBS(). procedure BillingInputFixedEBS( stInFiles: Tstrings; nBatch: LongInt ); procedure TelstraInvoiceFixedEBS( nBatch: LongInt; sDirectory: string; nProgram: LongInt ); implementation uses Math, Main, DataMod, GenFns, DateFunctions, FileFunctions, CriticalTests, Progress, ConstantValues, TelstraFunctions; const LOGPROGRESS = FALSE; // ********************** EXECUTION LOG ************** LOGDEBUG = FALSE; // ********************** EXECUTION LOG ************** 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(). sLastCallCode : string; sLastRentCode : string; sLastOtherCode: string; sLastCallTransactionTypeID : string; sLastRentTransactionTypeID : string; sLastOtherTransactionTypeID : string; sLastDiscountTransactionTypeID : string; sLastSupplierCallCodeID : string; sLastSupplierRentCodeID : string; sLastSupplierOtherCodeID : string; LogTextFile : string; // sLastSupplierDiscountCodeID : string; lThisIsAPABX : boolean; // Set in TelstraInvoiceFixedEBS()... // .. used in SaveSummaryChargesFixedEBS. lDScallsOnly : boolean; // Set in TelstraInvoiceFixedEBS()... // .. used in GetDetailFixedEBS (DC & DS). procedure StartNewLogFile(const Value: string); // Only called once - to create the file. var LogFile : Textfile; begin // Define the file to be created. AssignFile(LogFile, LogTextFile); // Create the file for writing only. Rewrite(LogFile); // Now close the file. CloseFile(LogFile); end; // LogToFile. procedure LogToFile(const Value: string); var LogFile : Textfile; begin // Define the file to be written to. AssignFile(LogFile, LogTextFile); // Open the file for writing only. Append(LogFile); // Now write the text to the file. WriteLn(LogFile, Value); // Make sure the Log is saved if an error occurs - close the file. CloseFile(LogFile); end; // LogToFile. procedure GetDetailFixedEBS ( 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. lFirstDF : Boolean; lFirstDE : Boolean; 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. nBatchID: LongInt; sBatchID: string; sLastDetailCode : string[2]; // To detect a header line AFTER details. DBfromDate : string; // Section DB - save read dates. DBtoDate : string; 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. qryDC : TQuery; qryDS : TQuery; nListValues : integer; nDummy : Real; sInputLineCode : string; lContinue : boolean; lDataSaved : boolean; lValueIsReal : boolean; nDataCollectionID : LongInt; lNextAccountNumber : boolean; sNextAccountNumber : string; nNextAccountNumber : integer; sCallCode : string; nPos : integer; { procedure SaveSummaryChargesFixedEBS(nBatchNumber : LongInt); // Save those items in DS not matched with individual call details. const PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. var sBatchID : string; qryDCCount : TQuery; // qryDCUpdate : TQuery; qryDSCount : TQuery; // qryDS : TQuery; nEntries : integer; nDummy : Real; nSoFar : integer; nSavedCalls : integer; nSummaryCalls : integer; nDSCalls : integer; slAddFieldNames : TStringList; slAddFieldValues : TStringList; sServiceID : string; sCallCode : string; begin nBatchID := nBatchNumber; sBatchID := IntToStr(nBatchID); // Set strings for saving DS summary data. slAddFieldNames := TStringList.Create; with slAddFieldNames do begin Add('SequenceNo'); Add('ServiceID'); Add('CallCode'); Add('TransactionTypeID'); Add('RateDescription'); Add('AmountExGST'); Add('GSTAmount'); Add('AmountIncGST'); Add('GSTFlag'); Add('FromDate'); Add('ToDate'); Add('SupplierServiceCodeID'); Add('BatchID'); end; // slAddFieldNames. slAddFieldValues := TStringList.Create; qryDCCount := TQuery.Create(MainForm); qryDCCount.DatabaseName := 'dbPPdata'; with qryDCCount do begin SQL.Add('SELECT COUNT(*) AS NumberOfCalls'); SQL.Add('FROM tDataCollection'); SQL.Add('WHERE ServiceID LIKE ''PHONENUMBER''');// Dummy value - "bookmark". SQL.Add('AND CallCode LIKE ''CALLCODEVALUE'''); // Dummy value - "bookmark". SQL.Add('AND BatchID = '+sBatchID); end; // qryDCCount. qryDSCount := TQuery.Create(MainForm); qryDSCount.DatabaseName := 'dbPPdata'; // Count the number of DS entries to be processed. with qryDSCount do begin SQL.Add('SELECT COUNT(*) AS NumberDSCalls'); SQL.Add('FROM tDataCollect_Temp'); SQL.Add('WHERE BatchID = '+sBatchID); // if not lThisIsAPABX then // SQL.Add('AND Var01 LIKE ''003'''); Open; end; // qryDSCount. nEntries := qryDSCount.FieldByName('NumberDSCalls').AsInteger; if (nEntries > 0) then begin nFactor := 100.0 / nEntries; end else begin nFactor := 100.0; end; nCount := 0; nSoFar := 0; frmProgress.Msg.Caption := 'Processing DS Summary list ...'; frmProgress.Msg.Refresh; frmProgress.ProgressBar1.Position := nSoFar; frmProgress.ProcessWinMessages(MainForm); // Now reset the qryDSCount SQL statement for processing below. with qryDSCount do begin Close; SQL.Clear; SQL.Add('SELECT SUM(NumCalls) AS NumberDSCalls'); SQL.Add('FROM tDataCollect_Temp'); SQL.Add('WHERE ServiceID LIKE ''PHONENUMBER''');// Dummy value - "bookmark". SQL.Add('AND CallCode LIKE ''CALLCODEVALUE'''); // Dummy value - "bookmark". SQL.Add('AND BatchID = '+sBatchID); end; // qryDSCount. [ start curly brackets qryDCUpdate := TQuery.Create(MainForm); qryDCUpdate.DatabaseName := 'dbPPdata'; with qryDCUpdate do begin SQL.Add('UPDATE tDataCollect_Temp'); SQL.Add('SET Active = 1'); SQL.Add('WHERE ServiceID LIKE ''PHONENUMBER'''); SQL.Add('AND CallCode LIKE ''CALLCODEVALUE'''); SQL.Add('AND NumCalls = 1'); SQL.Add('AND BatchID = '+sBatchID); end; // qryDCUpdate. ] end curly brackets // Now open the Summaries and we are ready to see which ones to save. with qryDS do begin Close; SQL.Clear; SQL.Add('SELECT *'); // SQL.Add('FROM (SELECT TOP 10000 *'); SQL.Add(' FROM tDataCollect_Temp'); SQL.Add(' WHERE BatchID = '+sBatchID); // if not lThisIsAPABX then // SQL.Add(' AND Var01 LIKE ''003'''); // SQL.Add(' ORDER BY ServiceID, NumCalls DESC) AS DCT'); // SQL.Add('ORDER BY ServiceID, CallCode'); SQL.Add('ORDER BY ID'); [ start curly brackets // This sql puts the order right, starting at biggest NumCalls to smallest: select BatchID, ServiceID, CallCode, NumCalls, Var01 from (SELECT TOP 10000 BatchID, ServiceID, CallCode, NumCalls, Var01 FROM tDataCollect_Temp where BatchID = 36789 ORDER BY ServiceID, NumCalls DESC) AS DCT ORDER BY ServiceID, CallCode // Following commented is origina: SQL.Add('FROM tDataCollect_Temp'); SQL.Add('WHERE BatchID = '+sBatchID); SQL.Add('AND Var01 LIKE ''003'''); SQL.Add('ORDER BY ServiceID, CallCode'); ] end curly brackets Open; end; // qryDS. // And here we go. while not qryDS.Eof do begin // Get the number of details read into the tDataCollection table. qryDCCount.Close; qryDCCount.SQL.Strings[2] := 'WHERE ServiceID LIKE '''+ qryDS.FieldByName('ServiceID').AsString+''''; qryDCCount.SQL.Strings[3] := 'AND CallCode LIKE '''+ qryDS.FieldByName('CallCode').AsString+''''; qryDCCount.Open; if qryDCCount.Eof then nSavedCalls := 0 else nSavedCalls := qryDCCount.FieldByName('NumberOfCalls').AsInteger; // Get the number of calls in the Summary list of tDataCollext_Temp table. qryDSCount.Close; qryDSCount.SQL.Strings[2] := 'WHERE ServiceID LIKE '''+ qryDS.FieldByName('ServiceID').AsString+''''; qryDSCount.SQL.Strings[3] := 'AND CallCode LIKE '''+ qryDS.FieldByName('CallCode').AsString+''''; qryDSCount.Open; if qryDSCount.Eof then nDSCalls := 0 else nDSCalls := qryDSCount.FieldByName('NumberDSCalls').AsInteger; // Get the number of calls in this summary item, and ServiceID & CallCode. nSummaryCalls := qryDS.FieldByName('NumCalls').AsInteger; sServiceID := qryDS.FieldByName('ServiceID').AsString; sCallCode := qryDS.FieldByName('CallCode').AsString; // Test if there is a mismatch - assume only one entry per CallType. if (nSavedCalls < nDSCalls) then begin while not qryDS.Eof and (sServiceID = qryDS.FieldByName('ServiceID').AsString) and (sCallCode = qryDS.FieldByName('CallCode').AsString) do begin if ((nSavedCalls+nSummaryCalls) <= nDSCalls) then begin // Save this entry now in the tDataCollection table. with slAddFieldValues do begin Clear; Add(''''+qryDS.FieldByName('SequenceNo').AsString+''''); Add(''''+sServiceID+''''); Add(''''+sCallCode+''''); Add(qryDS.FieldByName('TransactionTypeID').AsString); Add(''''+qryDS.FieldByName('RateDescription').AsString+''''); Add(qryDS.FieldByName('AmountExGST').AsString); // String, NOT currency. Add(qryDS.FieldByName('GSTAmount').AsString); // String, NOT currency. Add(qryDS.FieldByName('AmountIncGST').AsString); // String, NOT currency. Add(''''+qryDS.FieldByName('GSTFlag').AsString+''''); Add(''''+qryDS.FieldByName('FromDate').AsString+''''); Add(''''+qryDS.FieldByName('ToDate').AsString+''''); Add(qryDS.FieldByName('SupplierServiceCodeID').AsString); Add(sBatchID); end; // slAddFieldValues. AddToTable('tDataCollection',slAddFieldNames,slAddFieldValues); // Update the number of calls saved in the tDataCollection table. nSavedCalls := nSavedCalls + nSummaryCalls; [ start curly brackets // Mark this DS summary entry for later saving in tDataCollection-set Active=1. qryDCUpdate.SQL.Strings[2] := 'WHERE ServiceID LIKE '''+ qryDS.FieldByName('ServiceID').AsString+''''; qryDCUpdate.SQL.Strings[3] := 'AND CallCode LIKE '''+ qryDS.FieldByName('CallCode').AsString+''''; qryDCUpdate.SQL.Strings[4] := 'AND NumCalls = '+IntToStr(nSummaryCalls); qryDCUpdate.ExecSQL; ] end curly brackets end; // Save entry in tDataCollection table. // Skip to the next entry and reload the number of calls in this Summary. qryDs.Next; if not qryDS.Eof then nSummaryCalls := qryDS.FieldByName('NumCalls').AsInteger; Inc(nCount); end; // while not qryDS.eof ... if not qryDS.Eof and ((sServiceID <> qryDS.FieldByName('ServiceID').AsString) or (sCallCode <> qryDS.FieldByName('CallCode').AsString)) then begin // Oops - we have passed out of this ServiceID and CallCode. Go back. qryDs.Prior; Dec(nCount); end; end; // if numbers are a mismatch. // Report progress to the user *********************************** // Report progress to the user *********************************** Inc(nCount); nDummy := nFactor * nCount; nSoFar := Round( nDummy ); // Make sure we don't exceed 100% on the Progress bar. if (nSoFar > PROGRESS_MAX_C) then begin frmProgress.ProgressBar1.Position := PROGRESS_MAX_C; end else begin frmProgress.ProgressBar1.Position := nSoFar; end; // Update the user's screen. frmProgress.ProcessWinMessages(MainForm); qryDS.Next; end; // qryDC.Eof. // // Done. Now add all Active entries into the tDataCollection table. qryDCCount.Close; // qryDCUpdate.Close; qryDSCount.Close; qryDS.Close; [ start curly brackets with qryDCUpdate do begin Unprepare; SQL.Clear; SQL.Add('INSERT INTO tDataCollection'); SQL.Add('(SequenceNo, '); SQL.Add(' ServiceID, '); SQL.Add(' CallCode, '); SQL.Add(' TransactionTypeID, '); SQL.Add(' RateDescription, '); SQL.Add(' AmountExGST, '); SQL.Add(' GSTAmount, '); SQL.Add(' AmountIncGST, '); SQL.Add(' GSTFlag, '); SQL.Add(' FromDate, '); SQL.Add(' ToDate, '); SQL.Add(' SupplierServiceCodeID, '); SQL.Add(' BatchID)'); SQL.Add(' SELECT '); SQL.Add(' SequenceNo, '); SQL.Add(' ServiceID, '); SQL.Add(' CallCode, '); SQL.Add(' TransactionTypeID, '); SQL.Add(' RateDescription, '); SQL.Add(' AmountExGST, '); SQL.Add(' GSTAmount, '); SQL.Add(' AmountIncGST, '); SQL.Add(' GSTFlag, '); SQL.Add(' FromDate, '); SQL.Add(' ToDate, '); SQL.Add(' SupplierServiceCodeID, '); SQL.Add(' BatchID'); SQL.Add(' FROM tDataCollect_Temp'); SQL.Add(' WHERE Active = 1'); ExecSQL; end; // qryDCUpdate. ] end curly brackets slAddFieldNames.Free; slAddFieldValues.Free; qryDCCount.Free; // DONE - Delete the DS entries in the temporary table. with qryDS do begin Close; SQL.Clear; SQL.Add('DELETE'); SQL.Add('FROM tDataCollect_Temp'); SQL.Add('WHERE BatchID = '+sBatchID); ExecSQL; end; // qryDS. // qryDS.Free; qryDSCount.Free; // qryDCUpdate.Free; end; // SaveSummaryChargesFixedEBS. } begin if LOGPROGRESS or LOGDEBUG then begin LogToFile('Start of importing code - GetDetailFixedEBS.'); end; lNextAccountNumber := False; sNextAccountNumber := ''; nNextAccountNumber := 0; sLastDetailCode := ' '; nMaxResults := 0; nBatchID := nBatchNumber; sBatchID := IntToStr(nBatchID); // lContinue := False; lDataSaved := False; sCallCode := ''; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; slAddFieldNames := TStringList.Create; // Calls to AddToTable() only. slAddFieldValues := TStringList.Create; // Calls to AddToTable() only. // Establish the query to be used to save the data read. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; // And the temporary table to save potential summary data without call details. qryDS := TQuery.Create(MainForm); qryDS.DatabaseName := 'dbPPdata'; // Make sure dates are defined. qryDC.SQL.Add('SELECT tBatch.BillingPeriod'); qryDC.SQL.Add('FROM tBatch'); qryDC.SQL.Add('WHERE tBatch.ID = '+sBatchID); qryDC.Open; SubString := qryDC.FieldByName('BillingPeriod').AsString; dStartBillingPeriod := StrDMYtoDate('01/'+Copy(SubString,6,2)+'/'+ Copy(SubString,1,4)); dEndBillingPeriod := LastDayInMonth(dStartBillingPeriod); qryDC.Close; qryDC.SQL.Clear; // LastEntryIs_1 := False; // SaveLastEntry_1 := False; lFirstDF := True; lReturnCode := False; CurrentFile := Fname; FileIteration := 0; // Original file ext'n, then 001,002, etc. // Set the earliest a valid date is likely to be. dMinDate := EncodeDate(1980,1,1); // Set a dummy date for initialisation. dBilling := dMinDate; if FileExists(CurrentFile) then 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: 30,930 lines in a file of 12,135,424 bytes. // Estimate the number of data lines in the file. if (nFileSize > 0) then begin nFactor := 100.0 / ((30930.0 / 12135424.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 Fixed Services Batch: ' + sBatchID; 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. lContinue := (Copy(T,1,8)<>'HDR:FCAB'); if not lContinue then begin frmProgress.Hide; MessageDlg('Incorrect input file type. This file is for'+#13#10+ 'an original-format Fixed Account. Try again.', mtWarning, [mbOK],0); end else begin lContinue := (Copy(T,1,8)<>'HDR:RACE'); if not lContinue then begin if LOGPROGRESS then begin LogToFile('Wrong file type - HDR:RACE.'); end; frmProgress.Hide; MessageDlg('Incorrect input file type.'+#13#10+ 'This file is for a Mobile Account. Try again.', mtWarning, [mbOK],0); end else begin // It may be 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 if LOGPROGRESS then begin LogToFile('Wrong file type - has commas (CSV).'); end; 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 if LOGPROGRESS then begin LogToFile('Wrong file type - has RB at line start (Mobile).'); end; frmProgress.Hide; MessageDlg('Incorrect input file type.'+#13#10+ 'This file is for a Mobile Account. Try again.', mtWarning, [mbOK],0); end; end; end; end; // HDR:RACE. // Each input file may have more than one account within. // Each account will be contiguous, with the first line starting with // DH - the Detail Header line for the account. if lContinue then begin // Skip to the accounts' Header Line and Determine the billing date // only once. // Make sure that we find the valid Account first. lContinue := False; // Reset then test for correct account. if LOGPROGRESS then begin LogToFile('Correct file type. Start skipping to the required account.'); end; SubString := 'DH'+sAccountNumber; while not lContinue and (not ((Length(Trim(T))=0) and Eof( TelstraFile ))) do begin lContinue := (Trim(Copy(T,1,12)) = SubString); 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 not lContinue then begin // Account selected is not in this file. if LOGPROGRESS then begin LogToFile('The required Account is not contained in this file.'); end; frmProgress.Hide; MessageDlg('The Account specified is not in the file selected.'+#13#10+ 'Please try again.', mtWarning, [mbOK],0); end else begin if LOGPROGRESS then begin LogToFile('Account found and now positioned at the beginning of that '+ 'account. Get the account dates.'); end; // Determine the billing date only once - for the first file only. if FileIteration = 0 then begin // First, extract the billing date (8-char date on the first valid line). SubString := Copy(T,13,8); dBilling := EncodeDate(StrToInt(Copy(SubString,1,4)), StrToInt(Copy(SubString,5,2)), StrToInt(Copy(SubString,7,2))); end; // 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 INTO 'tDataCollection' *********** //**************************************************************** try // The following assumes that: // DS section precedes the DE section; // Sections are contiguous; // Only the DS and HU (was:DE) sections are of interest here. nResultSet := -1; repeat frmProgress.ProcessWinMessages(MainForm); // Read only relevant text into the database. sInputLineCode := UpperCase(Copy(T,1,2)); // BILL INFORMATION ********************************************* // BILL INFORMATION ********************************************* if (sInputLineCode='BI') then begin // Do nothing - electronic copy of the printed bill, always // first in the input file. end // CALL DETAILS ************************************************* // CALL DETAILS ************************************************* else if (sInputLineCode='DC') then begin if not lDScallsOnly and ((Length(Trim(Copy(T,205,8)))>0) or not (Copy(T,45,1)='0')) then begin // This line is a detail call - save it only if a time stamp // (TxnTime) is included (Else it is a Summary and NOT a Detail) // when the ServiceID is 02- or 03- prefix. if LOGPROGRESS then begin LogToFile('DC: '+Copy(T,1,120)); end else if LOGDEBUG then begin SubString := Trim(Copy(T,45,17)); if (SubString = '0351442042') or (SubString = '0352439455') or (SubString = '0353681240') or (SubString = '0354724205') or (SubString = '0355614277') or (SubString = '0355614427') or (SubString = '0357821015') or (SubString = '0357841773') or (SubString = '0397401321') then begin LogToFile('DC: '+T); end; end; FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(Copy(T,32,10)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + '''' + SubString +''','; end; SubString := Trim(Copy(T,45,17)); sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(SubString) +''','; // Some services have an invalid character (vertical Tab) - remove. nPos := (Pos(Chr(12),T)); if nPos > 0 then begin // T := Copy(T,1,nPos-1) + Copy(T,nPos+1,Length(T)-nPos); Delete(T, nPos, 1); end; // Read the code common for each detail and summary - used below // when the summary (type "1") is read. sLastDetailCode := Copy(T,67,2); // Entry code. SubString := Copy(T,197,8); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; end; SubString := Copy(T,205,8); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(Copy(T,147,20)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Origin,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := Trim(Copy(T,70,9)); SubString := ConvertTimeToSecs(CleanString(SubString)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(Copy(T,268,2))); 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); 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 + sLastCallTransactionTypeID+','+ sLastSupplierCallCodeID+','; end else begin sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+ sSupplierCallCodeID+','; end; // CallCode. SubString := Trim(Copy(T,213,32)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Peak,'; if (Pos('N Peak',SubString)>0) then sListValues := sListValues +'1,' else sListValues := sListValues +'0,'; sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := Trim(Copy(T,89,17)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; // Read the invoice amounts, check values then save. SubString := Trim(Copy(T,312,13)); if Length(SubString) = 0 then SubString := '0.0' else begin ProveReal(SubString, lValueIsReal); if not lValueIsReal then SubString := '0.0'; end; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + SubString +','; SubString := Trim(Copy(T,325,13)); if Length(SubString) = 0 then SubString := '0.0' else begin ProveReal(SubString, lValueIsReal); if not lValueIsReal then SubString := '0.0'; end; 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,338,13)); if Length(SubString) = 0 then SubString := '0.0' else begin ProveReal(SubString, lValueIsReal); if not lValueIsReal then SubString := '0.0'; end; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + SubString +','; SubString := Trim(Copy(T,13,8)); sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatchID +')'; // Now make the SQL statement to save to the database table. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; lDataSaved := True; // Catch all other instances and reset checking variables. sLastDetailCode := Copy(T,65,2); end; // not lDScallsOnly. end // 'DC'. // CALL CHARGES IN SUMMARY SECTION ONLY ************************* // CALL CHARGES IN SUMMARY SECTION ONLY ************************* else if (sInputLineCode='DS') then begin sCallCode := CleanString(Copy(T,219,2)); // Call Code - Read once. if ( (Length(Copy(T,71,12))>0) and (not (Copy(T,42,3)='001')) and ( // 001=Rent - ignore. lDScallsOnly or // Import ALL calls in DS or ... // ... just 2 codes. ((sCallCode='10') or (sCallCode='39')) ) ) then begin if LOGPROGRESS then begin LogToFile('DS: '+Copy(T,1,420)); end else if LOGDEBUG then begin SubString := Trim(Copy(T,45,17)); if (SubString = '0351442042') or (SubString = '0352439455') or (SubString = '0353681240') or (SubString = '0354724205') or (SubString = '0355614277') or (SubString = '0355614427') or (SubString = '0357821015') or (SubString = '0357841773') or (SubString = '0397401321') then begin LogToFile('DS: '+T); end; end; FieldStringList.Clear; // 001=Rental - details in DF. ValueStringList.Clear; // 003=Summary only - no details. sListFields := '('; // 004=Total - details given. sListValues := 'VALUES ('; SubString := Copy(T,32,10); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + '''' + SubString +''','; end; SubString := Copy(T,45,10); sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(SubString) +''','; if (Length(sCallCode) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ sCallCode +''','; 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); 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 + sLastCallTransactionTypeID+','+ sLastSupplierCallCodeID+','; end else begin sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+ sSupplierCallCodeID+','; end; // CallCode. SubString := IntToStr(StrToInt(Copy(T,114,10))); sListFields := sListFields + 'NumCalls,'; sListValues := sListValues + SubString +','; SubString := SubString+' No '+ Trim(Copy(T,71,12))+' '+ Trim(Copy(T,101,10)); // Trim(Copy(T,71,12))+' '+ Trim(Copy(T,101,10)) + // ' ['+ Trim(Copy(T,175,10)) +'] ' + Trim(Copy(T,322,9)); sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; { sListFields := sListFields + 'RateDescription, Active,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; if (Pos(' VPN ', SubString) > 0) then sListValues := sListValues +'1,' else sListValues := sListValues +'0,'; } SubString := ''''+Copy(T,42,3)+''','; sListFields := sListFields + 'Var01,'; sListValues := sListValues + SubString; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Copy(T,358,13); if Length(SubString) = 0 then SubString := '0.0' else begin ProveReal(SubString, lValueIsReal); if not lValueIsReal then SubString := '0.0'; end; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + SubString +','; SubString := Copy(T,371,13); if Length(SubString) = 0 then SubString := '0.0' else begin ProveReal(SubString, lValueIsReal); if not lValueIsReal then SubString := '0.0'; end; 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 := Copy(T,384,13); if Length(SubString) = 0 then SubString := '0.0' else begin ProveReal(SubString, lValueIsReal); if not lValueIsReal then SubString := '0.0'; end; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + SubString +','; SubString := Trim(Copy(T,135,8)); if (Length(SubString)>0) then begin sListFields := sListFields + 'FromDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; end; SubString := Copy(T,143,8); if (Length(SubString)>0) then begin sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; end; sListFields := sListFields + 'BatchID, Active)'; sListValues := sListValues + sBatchID +', 0)'; // Now make the SQL statement to save to the database table. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollect_Temp'); 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. qryDS.Close; qryDS.SQL.Text := qryStringList.Text; qryDS.ExecSQL; lDataSaved := True; end; // if lDScallsOnly or ... end // 'DS'. // LIST OF EQUIPMENT ******************************************** // LIST OF EQUIPMENT ******************************************** else if (sInputLineCode='DE') then begin if LOGPROGRESS then begin LogToFile('DE: '+Copy(T,1,120)); end; // This line has the full service ID and all dates-commit to memory. SubString := Trim(Copy(T,45,17)); Inc( nResultSet ); SetLength( aResultSet, nResultSet + 1); aResultSet[ nResultSet ].Custno := SubString; // Save the billing period dates. SubString := Trim(Copy(T,253,8)); if (SubString = '') or (Length(SubString) <> 8) then begin dStartBillingPeriod := dBilling; end else begin dStartBillingPeriod := EncodeDate(StrToInt(Copy(SubString,1,4)), StrToInt(Copy(SubString,5,2)), StrToInt(Copy(SubString,7,2))); end; // if (SubString = '') or (Length(SubString) <> 8) then. SubString := Trim(Copy(T,261,8)); if (SubString = '') or (Length(SubString) <> 8) then begin dEndBillingPeriod := dBilling; end else begin dEndBillingPeriod := EncodeDate(StrToInt(Copy(SubString,1,4)), StrToInt(Copy(SubString,5,2)), StrToInt(Copy(SubString,7,2))); end; // if (SubString = '') or (Length(SubString) <> 8) then. // Read the code: SubString := UpperCase(Trim(Copy(T,331,2))); with aResultSet[ nResultSet ] do begin // Rent. if (RentFrom 0) then ServCode := SubString; end; // with aResultSet[ nResultSet ] do. lFirstDF := True; end // 'DE'. // RENT DETAILS. *************************************************** // RENT DETAILS. *************************************************** else if (sInputLineCode='DF') then begin if LOGPROGRESS then begin LogToFile('DF: '+Copy(T,1,120)); end; // This line is a detail rental - save it. // but the first thing is to correct the phone numbers. SubString := Trim(Copy(T,45,17)); if lFirstDF then begin // Return to the start of the results - check the phone numbers. lFirstDF := False; nMaxResults := Length( aResultSet ) - 1; nResultSet := 0; sPartResultSet := Trim(Copy(aResultSet[nResultSet].Custno,1,10)); if (aResultSet[ nResultSet ].Custno <> SubString) and (sPartResultSet <> SubString) then begin lFound := False; for nResultSet := 0 to nMaxResults do begin sPartResultSet := Trim(Copy(aResultSet[nResultSet].Custno,1,10)); if (aResultSet[ nResultSet ].Custno = SubString) or (sPartResultSet = SubString) then begin lFound := True; break; end; end; // for nResultSet := 0 to nMaxResults do. // Check that the array bounds have not been exceeded. if ( nResultSet > nMaxResults ) then begin // Return to the beginning of the array. nResultSet := 0; end; // if ( nResultSet > nMaxResults ) then. end else begin lFound := True; end; // if (aResultSet[ nResultSet ].Custno <> SubString) and.. end // if lFirstDF then. else begin lFound := True; Inc( nResultSet ); if (nResultSet > nMaxResults) then begin nResultSet := nMaxResults; end; // if (nResultSet > nMaxResults) then. sPartResultSet := Trim(Copy(aResultSet[nResultSet].Custno,1,10)); if (aResultSet[ nResultSet ].Custno <> SubString) and (sPartResultSet <> SubString) then begin // Must search through the array until it is found. lFound := False; if (nResultSet > nMaxResults) then begin nStartSearchAt := nMaxResults; end else begin nStartSearchAt := nResultSet; end; // if (nResultSet > nMaxResults) then..else. for nResultSet := 0 to nMaxResults do begin sPartResultSet := Trim(Copy(aResultSet[nResultSet].Custno,1,10)); if (aResultSet[ nResultSet ].Custno <> SubString) and (sPartResultSet <> SubString) then begin lFound := True; break; end; // if aResultSet[nResultSet].Custno=SubString then. end; // for nResultSet := 1 to Length( aResultSet ) do. if not lFound then begin nResultSet := nStartSearchAt; end; // if not lFound then. end; // if (aResultSet[nResultSet].Custno<>SubString) then. end; // if lFirstDF then. // Now make the SQL statement to store the data in the table. sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(Copy(T,32,10)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; // SequenceNo. SubString := AccountFormat(aResultSet[ nResultSet ].custno); sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ SubString +''','; SubString := CleanString(aResultSet[ nResultSet ].ServCode); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastRentCode <> SubString) then begin sLastRentCode := SubString; vSubString := SubString; with qryTelstra do lFound := Locate('Code',vSubString,[loCaseInsensitive]); if lFound then begin sLastRentTransactionTypeID := qryTelstra['TransactionType']; sLastSupplierRentCodeID := qryTelstra['SupplierCodeID']; end else begin sLastRentTransactionTypeID := sRentCodeID; sLastSupplierRentCodeID := sSupplierRentCodeID; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastRentCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add('''RENT'''); slAddFieldNames.Add('Description'); slAddFieldValues.Add('''RENT'''); slAddFieldNames.Add('TransactionTypeID'); slAddFieldValues.Add(sLastRentTransactionTypeID); 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; // qryOptus. end; // if (not) lFound. end; // if (sLastCallCode <> SubString) then begin. sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sLastRentTransactionTypeID+','+ sLastSupplierRentCodeID+','; end else begin sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sRentCodeID+','+ sSupplierRentCodeID+','; end; // RentCode. SubString := Trim(Copy(T,98,23)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; // RateDescription. sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + Copy(T,149,13) + ','; curValue := curFormat(StrToFloat(Copy(T,162,13))); sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Copy(T,162,13) +','; sListFields := sListFields + 'GSTFlag,'; if (curValue = 0.00) then sListValues := sListValues + '''N'',' else sListValues := sListValues + '''Y'','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + Copy(T,175,13) +','; if lFound then begin sListFields := sListFields + 'FromDate,'; sListValues := sListValues +''''+ FormattedDateString(aResultSet[ nResultSet ].RentFrom) +''','; sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ FormattedDateString(aResultSet[ nResultSet ].RentTill) +''','; end else begin sListFields := sListFields + 'FromDate,'; sListValues := sListValues +''''+ YMD2DMY(Copy(T,13,8)) +''','; // sListValues := sListValues +''''+ YMD2DMY(Copy(T,14,8)) +''',';//20060119 RJC. end; // if lFound then. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatchID +')'; // Now save to the database table. qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); qryStringList.Add(sListFields); qryStringList.Add(sListValues); // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; lDataSaved := True; end // if (sInputLineCode='DF') then. // OTHER DETAILS (miscellaneous). ********************************** // OTHER DETAILS (miscellaneous). ********************************** else if (sInputLineCode='DO') then begin if LOGPROGRESS then begin LogToFile('DO: '+Copy(T,1,120)); end; // This line is a detailed Other (Miscellaneous) charge - save it. // Now make the SQL statement to store the data in the table. sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(Copy(T,32,10)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(Trim(Copy(T,45,17))) +''','; SubString := Trim(Copy(T,82,70)); if (Length(Trim(Copy(T,153,15))) > 0) then begin if (Length(SubString) > 0) then begin SubString := SubString + ' (' + Trim(Copy(T,153,15)) + ')'; end else begin SubString := Trim(Copy(T,153,15)); end; // if (Length(SubString) > 0) then. end; // if (Length(Trim(Copy(T,153,15))) > 0) then. if (Length(Trim(Copy(T,268,90))) > 0) then begin if (Length(SubString) > 0) then begin SubString := SubString + ' - '; end; // if (Length(SubString) > 0) then. SubString := SubString + SingleSpaces(Trim(Copy(T,268,90))); SubString := SubString; end; // if (Length(Trim(Copy(T,268,120))) > 0) then. if (Length(Trim(Copy(T,358,30))) > 0) then begin if (Length(SubString) > 0) then begin SubString := SubString + ' - '; end; // if (Length(SubString) > 0) then. SubString := SubString + SingleSpaces(Trim(Copy(T,358,30))); end; // if (Length(Trim(Copy(T,268,120))) > 0) then. if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + Copy(T,414,13) +','; sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Copy(T,427,13) +','; curValue := curFormat(StrToFloat(Copy(T,427,13))); sListFields := sListFields + 'GSTFlag,'; if (curValue = 0.00) then begin sListValues := sListValues + '''N'','; end else begin sListValues := sListValues + '''Y'','; end; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + Copy(T,440,13) +','; sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ YMD2DMY(Copy(T,13,8)) +''','; // sListValues := sListValues +''''+ YMD2DMY(Copy(T,14,8)) +''',';// 20060119 RJC. SubString := Trim(Copy(T,230,2)); SubString := CleanString(SubString); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastOtherCode <> SubString) then begin sLastOtherCode := SubString; vSubString := SubString; with qryTelstra do lFound := Locate('Code',vSubString,[loCaseInsensitive]); if lFound then begin sLastOtherTransactionTypeID := qryTelstra.FieldByName('TransactionType').AsString; sLastSupplierOtherCodeID := qryTelstra.FieldByName('SupplierCodeID').AsString; end else begin sLastOtherTransactionTypeID := sOtherCodeID; sLastSupplierOtherCodeID := sSupplierOtherCodeID; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastOtherCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add('''OTHER'''); slAddFieldNames.Add('Description'); slAddFieldValues.Add('''OTHER'''); slAddFieldNames.Add('TransactionTypeID'); slAddFieldValues.Add(sLastOtherTransactionTypeID); 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 + sLastOtherTransactionTypeID+','+ sLastSupplierOtherCodeID+','; end else if (Length(sOtherCodeID)>0) then begin // No codes provided - use defsult codes. sListFields := sListFields + 'CallCode,'; sListValues := sListValues + sOtherCodeID +','; vSubString := sOtherCodeID; with qryTelstra do lFound := Locate('Code',vSubString,[loCaseInsensitive]); if lFound then begin sLastOtherTransactionTypeID := qryTelstra['TransactionType']; sLastSupplierOtherCodeID := qryTelstra['SupplierCodeID']; sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sLastOtherTransactionTypeID+','+ sLastSupplierOtherCodeID+','; end; // if lFound then begin. end; // OtherCode. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatchID +')'; // Now save to the database table. qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); qryStringList.Add(sListFields); qryStringList.Add(sListValues); // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; lDataSaved := True; end // if (sInputLineCode='DO') then. // BILLING ALL (summary). ****************************************** // BILLING ALL (summary). ****************************************** else if (sInputLineCode='DA') then begin if LOGPROGRESS then begin LogToFile('DA: '+Copy(T,1,120)); end; // This line may be a detailed discount - save it. if (UpperCase(Trim(Copy(T,65,15)))='BILL ROUNDING') then begin // Now make the SQL statement to store the data in the table. sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(Copy(T,32,10)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; sListFields := sListFields + 'ServiceID,'; sListValues := sListValues + '''ROUNDING'','; SubString := Trim(Copy(T,65,80)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; sListFields := sListFields + 'FromDate,'; SubString := Trim(Copy(T,159,8)); if (Length(SubString) > 0) then begin sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; end else begin sListValues := sListValues +''''+ FormattedDateString(dStartBillingPeriod) +''','; end; sListFields := sListFields + 'ToDate,'; SubString := Trim(Copy(T,167,8)); if (Length(SubString) > 0) then begin sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; end else begin sListValues := sListValues +''''+ FormattedDateString(dEndBillingPeriod) +''','; end; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + Copy(T,220,13) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + Copy(T,233,13) +','; curValue := curFormat(StrToFloat(Copy(T,246,13))); sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Copy(T,246,13) +','; sListFields := sListFields + 'GSTFlag,'; if (curValue = 0.00) then begin sListValues := sListValues + '''N'','; end else begin sListValues := sListValues + '''Y'','; end; if (Length(sOtherCodeID)>0) then begin // Code not provided - use generic ones. sListFields := sListFields + 'CallCode,'; sListValues := sListValues + sOtherCodeID +','; vSubString := sOtherCodeID; with qryTelstra do lFound := Locate('Code',vSubString,[loCaseInsensitive]); if lFound then begin sLastOtherTransactionTypeID := qryTelstra['TransactionType']; sListFields := sListFields + 'TransactionTypeID,'; sListValues := sListValues + sLastOtherTransactionTypeID+','; end; // if lFound then begin. end; // OtherCode. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatchID +')'; // Now save to the database table. qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); qryStringList.Add(sListFields); qryStringList.Add(sListValues); // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; lDataSaved := True; end; // BILL ROUNDING. end // if (sInputLineCode='DA') then. // BILLING DETAILS (account discounts). **************************** // BILLING DETAILS (account discounts). **************************** else if (sInputLineCode='DB') then begin if LOGPROGRESS then begin LogToFile('DB: '+Copy(T,1,120)); end; // This line is a detailed discount - save it only if the // total amount is not zero. curValue := curFormat(StrToFloat(Copy(T,300,13))); if (curValue = 0.00) then begin SubString := Trim(Copy(T,175,8)); if (Length(DBfromDate) = 0) and (Length(SubString)=8) then DBfromDate := ''''+YMD2DMY(SubString)+''','; SubString := Trim(Copy(T,183,8)); if (Length(DBtoDate) = 0) and (Length(SubString)=8) then DBtoDate := ''''+YMD2DMY(SubString)+''','; end else begin // Now make the SQL statement to store the data in the table. sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(Copy(T,32,10)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(Trim(Copy(T,45,17))) +''','; SubString := Trim(Copy(T,65,80)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := Trim(Copy(T,175,8)); if (Length(SubString) = 8) then begin sListFields := sListFields + 'FromDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; if (Length(DBfromDate) = 0) then DBfromDate := ''''+YMD2DMY(SubString)+''','; end else if (Length(DBfromDate) > 0) then begin sListFields := sListFields + 'FromDate,'; sListValues := sListValues + DBfromDate; end; // FromDate. SubString := Trim(Copy(T,183,8)); if (Length(SubString) = 8) then begin sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; if (Length(DBtoDate) = 0) then DBtoDate := ''''+YMD2DMY(SubString)+''','; end else if (Length(DBtoDate) > 0) then begin sListFields := sListFields + 'ToDate,'; sListValues := sListValues + DBtoDate; end; // ToDate. sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + Copy(T,223,11) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + Copy(T,300,13) +','; sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Copy(T,313,13) +','; curValue := curFormat(StrToFloat(Copy(T,313,13))); sListFields := sListFields + 'GSTFlag,'; if (curValue = 0.00) then begin sListValues := sListValues + '''N'','; end else begin sListValues := sListValues + '''Y'','; end; // if (curValue = 0.00) then. if (Length(sOtherCodeID)>0) then begin // No code provided - use the generic ones. sListFields := sListFields + 'CallCode,'; sListValues := sListValues + sDiscountCodeID +','; vSubString := sDiscountCodeID; with qryTelstra do lFound := Locate('Code',vSubString,[loCaseInsensitive]); if lFound then begin sLastDiscountTransactionTypeID := qryTelstra['TransactionType']; sListFields := sListFields + 'TransactionTypeID,'; sListValues := sListValues + sLastDiscountTransactionTypeID+','; end; // lFound. end; // OtherCode. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatchID +')'; // Now save to the database table. qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); qryStringList.Add(sListFields); qryStringList.Add(sListValues); // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; lDataSaved := True; end; // if (curValue <> 0.00) then..else. end; // if (sInputLineCode='DB') then. Inc(nCount); // Report progress to the user. nDummy := nFactor * nCount; nSoFar := Round( nDummy ); // 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,3,10)) = sAccountNumber); while not lContinue and not lNextAccountNumber and (not ((Length(Trim(T))=0) and Eof( TelstraFile ))) do begin SubString := Trim(Copy(T,3,10)); lContinue := (SubString = sAccountNumber); // Wait for 20 lines of the next account before ending processing. if not lContinue then begin 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; // T <> ''. until not lContinue or lNextAccountNumber or ((T='') and Eof( TelstraFile )); //// if lThisIsAPABX then SaveSummaryChargesFixedEBS(nBatchID); // SaveSummaryChargesFixedEBS(nBatchID); SaveTelstraSummaryCharges(nBatchID); 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 - correct account found. end; // lContinue - correct file type. //**************************************************************** //*********** END READ BILLS INTO 'tDataCollection' ************** //**************************************************************** // Now need to process the last line if CR-LF not at end. // BUT - nothing done ! // D.Commit; finally // TelstraFile (try). // Done with the input file; close it. CloseFile( TelstraFile ); FieldStringList.Free; ValueStringList.Free; qryStringList.Free; qryDC.Close; qryDC.Free; qryDS.Close; qryDS.Free; 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 GetDetailFixedEBS. procedure BillingInputFixedEBS( stInFiles: Tstrings; nBatch: LongInt ); // This program processes the Telstra billing reports supplied on disk // and places the data into the tDataCollection table for unverified bill // information. // // The parameters passed contains the names of the input files, which are // passed on to the GetDetailFixedEBS() 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); if LOGPROGRESS then begin LogToFile('SupplierID='+sSupplierID); end; 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; if LOGPROGRESS then begin LogToFile('Account Number='+sAccountNumber); end; 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; if LOGPROGRESS then begin LogToFile('SupplierRentCodeID='+sSupplierRentCodeID+ ',SupplierRentCodeID='+sSupplierCallCodeID+ ',SupplierCallCodeID='+sSupplierCallCodeID+ ',SupplierOtherCodeID='+sSupplierOtherCodeID+ ',SupplierDiscountCodeID='+sSupplierDiscountCodeID); end; // 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. if LOGPROGRESS then begin LogToFile('Input file='+sPartFileName); end; frmProgress.Msg.Caption := 'Reading ' + sPartFileName + ' ...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; // Now move the text data into the Table. // // MainForm.StartTime; // GetDetailFixedEBS(stInFiles.Strings[0], DM.dbPPdata, nBatch, lContinue); // // MainForm.EndTime; // frmProgress.Hide; frmProgress.Free; if lContinue then begin if LOGPROGRESS then begin LogToFile('Input file successfully imported'); end; 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; // BillingInputFixedEBS. procedure TelstraInvoiceFixedEBS( nBatch: LongInt; sDirectory: string; nProgram: LongInt ); // 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 // if program progress is to be logged then set the filename for the log file. if LOGPROGRESS or LOGDEBUG then begin LogTextFile := INIFILEDIRECTORY + 'FixedEBS' + FormatDateTime('yymmddhhnnss', Now) + '.txt'; // Create the file. StartNewLogFile(LogTextFile); end; // 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 // nProgram is a Global variable set by the User's selection in Main(). lThisIsAPABX := (nProgram = PIMPORTBILLINGFILE_TELSTRA_EBS_PABX); lDScallsOnly := (nProgram = PIMPORTBILLINGFILE_TELSTRA_FIXED_EBS_DSCALLSONLY); BillingInputFixedEBS(stFiles, nBatch); end; // if lContinue then. end; // if lContinue then. finally stFiles.Free; end; end; // procedure TelstraInvoiceFixedEBS. end.