unit TelstraFixedTPAMS; // Revision History. { RJC 190514: Created from TelstraFixedEBS.pas, Telstra TPAMS reference billing structure document. } 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 GetDetailFixedTPAMS( const FName:String; D:TDatabase; nBatchNumber:LongInt; var lReturnCode:Boolean); // Includes SaveSummaryChargesFixedEBS(). procedure BillingInputFixedTPAMS( stInFiles: Tstrings; nBatch: LongInt ); } procedure TelstraInvoiceFixedTPAMS( 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. SequenceNumber: integer; // Generated a/c not in the files. nSupplierID: integer; // Set in BillingInputFixedTPAMS(). sSupplierID: string; // Set in BillingInputFixedTPAMS(). qryTelstra : TQuery; // Set in BillingInputFixedTPAMS(). sRentCodeID : string; // Set in BillingInputFixedTPAMS(). sCallCodeID : string; // Set in BillingInputFixedTPAMS(). sOtherCodeID : string; // Set in BillingInputFixedTPAMS(). sDiscountCodeID : string; // Set in BillingInputFixedTPAMS(). sSupplierRentCodeID : string; // Set in BillingInputFixedTPAMS(). sSupplierCallCodeID : string; // Set in BillingInputFixedTPAMS(). sSupplierOtherCodeID : string; // Set in BillingInputFixedTPAMS(). sSupplierDiscountCodeID : string; // Set in BillingInputFixedTPAMS(). sAccountNumber : string; // Set in BillingInputFixedTPAMS(). sLastCallCode : string; sLastRentCode : string; sLastOtherCode: string; sLastCallTransactionTypeID : string; sLastRentTransactionTypeID : string; sLastOtherTransactionTypeID : string; // sLastDiscountTransactionTypeID : string; sLastSupplierCallCodeID : string; sLastSupplierRentCodeID : string; sLastSupplierOtherCodeID : string; LogTextFile : string; sFromDate : string; sToDate : string; lDatesAreSet : boolean; lBillingDateIsSet : boolean; // sLastSupplierDiscountCodeID : string; lThisIsAPABX : boolean; // Set in TelstraInvoiceFixedEBS()... // .. used in SaveSummaryChargesFixedEBS. 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 GetDetailFixedTPAMS ( const FName: String; D: TDatabase; nBatchNumber : LongInt; var lReturnCode: Boolean ); const // Common, mostly, for input data (R), (N), (U). LineCode_C: integer = 0; // Charge type in this line. BillNumber_C: integer = 1; TelstraServiceNumber_C: integer = 2; ChargeCode_C: integer = 3; // ServiceID_C: integer = 4; // ServiceID field. GrossAmountExGST_C: integer = 5; AmountExGST_C: integer = 6; // AmountExGST field. AmountIncGST_C: integer = 7; // AmountIncGST field. GSTFree_C: integer = 8; // Y='No GST' else GST is paid. UniqueRefNumber_C: integer = 9; // Telstra unique ref number. ChargeDescription_C: integer = 10; // RateDescription (part). InvoiceSummaryGroup_C: integer = 11; // eg 'Other'. CostCentre_C: integer = 12; // eg always 'VRT'. ChargeQuantity_C: integer = 13; ChargeUnitofMeasure_C: integer = 14; StartDate_C: integer = 15; // yyyymmdd. StartTime_C: integer = 16; // hh:mm:ss. // (R): Recurring Charges Format EndDate_C: integer = 16; // yyyymmdd (Recurring only). // (N): Non-Recurring Charges Format PurchaseOrderNumber_C: integer = 17; InstalmentPaymentNumber_C: integer = 18; Merchant_C: integer = 19; Merchant_ABN_C: integer = 20; MerchantContact_C: integer = 21; Description_C: integer = 22; // // (U): Usage Charges Format UniqueRefNumber_UC: integer = 3; // Telstra unique ref number. ChargeCode_UC: integer = 4; // ServiceID_UC: integer = 5; // ServiceID field. GrossAmountExGST_UC: integer = 6; AmountExGST_UC: integer = 7; // AmountExGST field. AmountIncGST_UC: integer = 8; // AmountIncGST field. GSTFree_UC: integer = 9; // Y='No GST' else GST is paid. CallDuration_C: integer = 17; // Duration (seconds). CallMeterUnits_C: integer = 18; NumberDialled_C: integer = 19; // DialledNumber. Destination_C: integer = 20; // . Origin_C: integer = 21; // Origin. // (F) File Header BillCycle_C: integer = 1; // Telstra internal system ID. Customer_C: integer = 2; // for all invoices in the file. ProductionDateTime_C: integer = 3; TotalRows_C: integer = 4; // Total rows in the file. // (H) Invoice Header IssuedDate_C: integer = 3; // to populate var dBilling. // (I) Invoice Summary - not processed here. // (P) Payments and Adjustments - not processed here. // (S) Service Summary Charges - not processed here. PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. sComma : string = ','; sCR : string = Chr( 13 ); // Carriage return character. 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 ReadString: string; // line of text read from TelstraText. SubString: String; // A (temp) substring of ReadString. vSubString : variant; // - ditto, used in the Locate() function. ConvertString: TStringList; // Holds separate strings from ReadString 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; nPos : integer; begin if LOGPROGRESS or LOGDEBUG then begin LogToFile('Start of importing billing information - GetDetailFixedTPAMS.'); end; // lNextAccountNumber := False; // sNextAccountNumber := ''; // nNextAccountNumber := 0; sLastDetailCode := ' '; // nMaxResults := 0; nBatchID := nBatchNumber; sBatchID := IntToStr(nBatchID); lDataSaved := False; sFromDate := ''; sToDate := ''; lDatesAreSet := False; lBillingDateIsSet := False; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; slAddFieldNames := TStringList.Create; // Used in Calls to AddToTable(). slAddFieldValues := TStringList.Create; // Used in Calls to AddToTable(). // 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; // 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: 9,371 lines in a file of 18,613,820 bytes. // Estimate the number of data lines in the file. if (nFileSize > 0) then begin nFactor := 100.0 / ((9371.0 / 18613820.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 TPAMS 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, ReadString ); // Test for the TPAMS EBS file format for line #1 only. lContinue := ((Copy(ReadString,1,1)='F') AND (Pos(',',ReadString)>0) AND (Pos('TPAMS',ReadString)>0)); if not lContinue then begin if LOGPROGRESS then begin LogToFile('Wrong file type - expected a CSV file with ''F'', '+ '''TPAMS'' in the first line.'); end; frmProgress.Hide; MessageDlg('Incorrect input file type. This file is NOT formatted'+ #13#10+'as a TPAMS Account. Try again.', mtWarning, [mbOK],0); end else begin // lContinue is True... // Check the Header Line and Determine the billing date only once. if LOGPROGRESS then begin LogToFile('Correct file type. Start reading the account information.'); end; // Define ConvertString, used to convert CSV format to strings. ConvertString := TStringList.Create; // nProgressCounted := 0; // Initiate the data variables. nCount := 0; // D.StartTransaction; // Continue to search through the remaining text of the file // and on each each find, process the resultant string. //**************************************************************** //********* START READING BILLS INTO 'tDataCollection' *********** //**************************************************************** try // Step through all lines in the file, & process only those that // have the data that we want. // nResultSet := -1; repeat frmProgress.ProcessWinMessages(MainForm); // Replace all commas in ReadString with a carriage return character. ReadString := ReplacePartString( ReadString, sComma, sCR ); // Save the input string into ConvertString. ConvertString.Text := ReadString; // Separate fields into strings. // nStringCount := ConvertString.Count - 1; // Read only relevant text into the database. sInputLineCode := UpperCase(Trim(ConvertString[LineCode_C])); // USAGE DETAILS ************************************************* // USAGE DETAILS ************************************************* if (sInputLineCode='U') then begin // This line is a usage (call detail). Save it. if LOGPROGRESS then begin LogToFile('U: '+ReadString); end else if LOGDEBUG then begin SubString := Trim(ConvertString[ServiceID_UC]); 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('U: '+ReadString); end; end; FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; Inc(SequenceNumber); // Generate the sequence number. sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + ''''+IntToStr(SequenceNumber)+''','; SubString := Trim(ConvertString[ServiceID_UC]); sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(SubString) +''','; SubString := Trim(ConvertString[StartDate_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; end; SubString := Trim(ConvertString[StartTime_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString[Origin_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Origin,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := Trim(ConvertString[CallDuration_C]); // Seconds. if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := CleanString(Trim(ConvertString[ChargeCode_UC])); 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(ConvertString[ChargeDescription_C]); 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(ConvertString[NumberDialled_C]); 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(ConvertString[AmountExGST_UC]); 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 +','; curValue := curFormat(StrToFloat(SubString)); SubString := Trim(ConvertString[AmountIncGST_UC]); 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 +','; curValue := curFormat(StrToFloat(SubString)) - curValue; // =GST. SubString := Trim(ConvertString[GSTFree_UC]); sListFields := sListFields + 'GSTAmount,GSTFlag,'; if (SubString = 'Y') then begin sListValues := sListValues + FloatToStrF(curValue,ffFixed,12,4) + ',''N'','; end else begin sListValues := sListValues + FloatToStrF(curValue,ffFixed,12,4) + ',''Y'','; end; 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(ReadString,65,2); end // 'U'. // RECURRING DETAILS ********************************************* // RECURRING DETAILS ********************************************* else if (sInputLineCode='R') then begin // This line is a usage (call detail). Save it. if LOGPROGRESS then begin LogToFile('R: '+ReadString); end else if LOGDEBUG then begin SubString := Trim(ConvertString[ServiceID_C]); 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('R: '+ReadString); end; end; FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; Inc(SequenceNumber); // Generate the sequence number. sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + ''''+IntToStr(SequenceNumber)+''','; SubString := Trim(ConvertString[ServiceID_C]); sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(SubString) +''','; SubString := Trim(ConvertString[StartDate_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'FromDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; end; SubString := Trim(ConvertString[EndDate_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString[ChargeCode_C])); 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.FieldByName('TransactionType').AsString; sLastSupplierRentCodeID := qryTelstra.FieldByName('SupplierCodeID').AsString; end else begin sLastRentTransactionTypeID := sRentCodeID; sLastSupplierRentCodeID := sSupplierRentCodeID; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add('''RENT'''); slAddFieldNames.Add('Description'); slAddFieldValues.Add('''RENT'''); 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 (sLastRentCode <> 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(ConvertString[ChargeDescription_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; // Read the invoice amounts, check values then save. SubString := Trim(ConvertString[AmountExGST_C]); 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 +','; curValue := curFormat(StrToFloat(SubString)); SubString := Trim(ConvertString[AmountIncGST_C]); 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 +','; curValue := curFormat(StrToFloat(SubString)) - curValue; // =GST. SubString := Trim(ConvertString[GSTFree_C]); sListFields := sListFields + 'GSTAmount,GSTFlag,'; if (SubString = 'Y') then begin sListValues := sListValues + FloatToStrF(curValue,ffFixed,12,4) + ',''N'','; end else begin sListValues := sListValues + FloatToStrF(curValue,ffFixed,12,4) + ',''Y'','; end; 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(ReadString,65,2); end // 'R'. // NON-RECURRING DETAILS ********************************************* // NON-RECURRING DETAILS ********************************************* else if (sInputLineCode='R') then begin // This line is a usage (call detail). Save it. if LOGPROGRESS then begin LogToFile('N: '+ReadString); end else if LOGDEBUG then begin SubString := Trim(ConvertString[ServiceID_C]); 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('N: '+ReadString); end; end; FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; Inc(SequenceNumber); // Generate the sequence number. sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + ''''+IntToStr(SequenceNumber)+''','; SubString := Trim(ConvertString[ServiceID_C]); sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(SubString) +''','; SubString := Trim(ConvertString[StartDate_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'FromDate,'; sListValues := sListValues +''''+ YMD2DMY(SubString) +''','; end; SubString := Trim(ConvertString[EndDate_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString[ChargeCode_C])); 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(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add('''OTHER'''); slAddFieldNames.Add('Description'); slAddFieldValues.Add('''OTHER'''); 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 (sLastOtherCode <> SubString) then begin. sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sLastOtherTransactionTypeID+','+ sLastSupplierOtherCodeID+','; end else begin sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sOtherCodeID+','+ sSupplierOtherCodeID+','; end; // OtherCode. SubString := Trim(ConvertString[ChargeDescription_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; // Read the invoice amounts, check values then save. SubString := Trim(ConvertString[AmountExGST_C]); 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 +','; curValue := curFormat(StrToFloat(SubString)); SubString := Trim(ConvertString[AmountIncGST_C]); 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 +','; curValue := curFormat(StrToFloat(SubString)) - curValue; // =GST. SubString := Trim(ConvertString[GSTFree_C]); sListFields := sListFields + 'GSTAmount,GSTFlag,'; if (SubString = 'Y') then begin sListValues := sListValues + FloatToStrF(curValue,ffFixed,12,4) + ',''N'','; end else begin sListValues := sListValues + FloatToStrF(curValue,ffFixed,12,4) + ',''Y'','; end; 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(ReadString,65,2); end // 'N'. // FILE HEADER DETAILS **************************************** // FILE HEADER DETAILS **************************************** else if (sInputLineCode='F') then begin SubString := CleanString(Trim(ConvertString[TotalRows_C])); // Reset nFactor with an accurate count of rows in this file. if (Length(SubString) > 0) then begin nFactor := 100.0 / (StrToFloat(SubString)); end; end // 'F'. // INVOICE HEADER DETAILS ************************************* // INVOICE HEADER DETAILS ************************************* else if (not lBillingDateIsSet) and (sInputLineCode='H') then begin // Determine the billing date only once for the file. SubString := CleanString(Trim(ConvertString[IssuedDate_C])); // Extract the billing date (8-char date on the first valid line). SubString := CleanString(Trim(ConvertString[IssuedDate_C])); // dBilling := EncodeDate(StrToInt(Copy(SubString,1,4)), // StrToInt(Copy(SubString,5,2)), // StrToInt(Copy(SubString,7,2))); lBillingDateIsSet := True; { // LIST OF EQUIPMENT ******************************************** // LIST OF EQUIPMENT ******************************************** else if (sInputLineCode='DE') then begin if LOGPROGRESS then begin LogToFile('DE: '+Copy(ReadString,1,120)); end; // This line has the full service ID and all dates-commit to memory. SubString := Trim(Copy(ReadString,45,17)); Inc( nResultSet ); SetLength( aResultSet, nResultSet + 1); aResultSet[ nResultSet ].Custno := SubString; // Save the billing period dates. SubString := Trim(Copy(ReadString,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(ReadString,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(ReadString,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(ReadString,1,120)); end; // This line is a detail rental - save it. // but the first thing is to correct the phone numbers. SubString := Trim(Copy(ReadString,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(ReadString,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(ReadString,98,23)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; // RateDescription. sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + Copy(ReadString,149,13) + ','; curValue := curFormat(StrToFloat(Copy(ReadString,162,13))); sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Copy(ReadString,162,13) +','; sListFields := sListFields + 'GSTFlag,'; if (curValue = 0.00) then sListValues := sListValues + '''N'',' else sListValues := sListValues + '''Y'','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + Copy(ReadString,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(ReadString,13,8)) +''','; // sListValues := sListValues +''''+ YMD2DMY(Copy(ReadString,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(ReadString,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(ReadString,32,10)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(Trim(Copy(ReadString,45,17))) +''','; SubString := Trim(Copy(ReadString,82,70)); if (Length(Trim(Copy(ReadString,153,15))) > 0) then begin if (Length(SubString) > 0) then begin SubString := SubString + ' (' + Trim(Copy(ReadString,153,15)) + ')'; end else begin SubString := Trim(Copy(ReadString,153,15)); end; // if (Length(SubString) > 0) then. end; // if (Length(Trim(Copy(ReadString,153,15))) > 0) then. if (Length(Trim(Copy(ReadString,268,90))) > 0) then begin if (Length(SubString) > 0) then begin SubString := SubString + ' - '; end; // if (Length(SubString) > 0) then. SubString := SubString + SingleSpaces(Trim(Copy(ReadString,268,90))); SubString := SubString; end; // if (Length(Trim(Copy(ReadString,268,120))) > 0) then. if (Length(Trim(Copy(ReadString,358,30))) > 0) then begin if (Length(SubString) > 0) then begin SubString := SubString + ' - '; end; // if (Length(SubString) > 0) then. SubString := SubString + SingleSpaces(Trim(Copy(ReadString,358,30))); end; // if (Length(Trim(Copy(ReadString,268,120))) > 0) then. if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + Copy(ReadString,414,13) +','; sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Copy(ReadString,427,13) +','; curValue := curFormat(StrToFloat(Copy(ReadString,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(ReadString,440,13) +','; sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ YMD2DMY(Copy(ReadString,13,8)) +''','; // sListValues := sListValues +''''+ YMD2DMY(Copy(ReadString,14,8)) +''',';// 20060119 RJC. SubString := Trim(Copy(ReadString,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(ReadString,1,120)); end; // This line may be a detailed discount - save it. if (UpperCase(Trim(Copy(ReadString,65,15)))='BILL ROUNDING') then begin // Now make the SQL statement to store the data in the table. sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(Copy(ReadString,32,10)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; sListFields := sListFields + 'ServiceID,'; sListValues := sListValues + '''ROUNDING'','; SubString := Trim(Copy(ReadString,65,80)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; sListFields := sListFields + 'FromDate,'; SubString := Trim(Copy(ReadString,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(ReadString,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(ReadString,220,13) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + Copy(ReadString,233,13) +','; curValue := curFormat(StrToFloat(Copy(ReadString,246,13))); sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Copy(ReadString,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(ReadString,1,120)); end; // This line is a detailed discount - save it only if the // total amount is not zero. curValue := curFormat(StrToFloat(Copy(ReadString,300,13))); if (curValue = 0.00) then begin SubString := Trim(Copy(ReadString,175,8)); if (Length(DBfromDate) = 0) and (Length(SubString)=8) then DBfromDate := ''''+YMD2DMY(SubString)+''','; SubString := Trim(Copy(ReadString,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(ReadString,32,10)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(Trim(Copy(ReadString,45,17))) +''','; SubString := Trim(Copy(ReadString,65,80)); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := Trim(Copy(ReadString,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(ReadString,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(ReadString,223,11) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + Copy(ReadString,300,13) +','; sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + Copy(ReadString,313,13) +','; curValue := curFormat(StrToFloat(Copy(ReadString,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; // else if (sInputLineCode='H') 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, ReadString ); { if (Length(Trim(ReadString)) > 0) then begin lContinue := (Trim(Copy(ReadString,3,10)) = sAccountNumber); while not lContinue and not lNextAccountNumber and (not ((Length(Trim(ReadString))=0) and Eof( TelstraFile ))) do begin SubString := Trim(Copy(ReadString,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, ReadString); // 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; // ReadString <> ''. until not lContinue or lNextAccountNumber or ((ReadString='') and Eof( TelstraFile )); } until not lContinue or ((ReadString='') and 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 - 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 GetDetailFixedTPAMS. procedure BillingInputFixedTPAMS( stInFiles: Tstrings; nBatch: LongInt ); // This procedure processes the Telstra billing report files 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; lSuccessful : Boolean; sPartFileName : String; nBackSlashAt: Integer; nPos : integer; iCounter : integer; begin lSuccessful := false; SequenceNumber := 0; // Initiate glabal variable for sequence number. // 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; // qryTelatra.Eof - (TELSTRA not found). 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+#13#10+ 'SupplierCallCodeID='+sSupplierCallCodeID+#13#10+ 'SupplierOtherCodeID='+sSupplierOtherCodeID+#13#10+ 'SupplierDiscountCodeID='+sSupplierDiscountCodeID); end; // LOGPROGRESS. // Initialise the progress bar to display to the operator. frmProgress := TProgressForm.Create(MainForm); frmProgress.btnCancel.Visible := False; frmProgress.Show; // Process each file selected by the User. for iCounter := 0 to (stInFiles.Count - 1) do begin sPartFileName := stInFiles.Strings[iCounter]; 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; // GetDetailFixedTPAMS(stInFiles.Strings[iCounter], DM.dbPPdata, nBatch, lContinue); lSuccessful := lSuccessful OR lContinue; // Any successful file is okay. // // MainForm.EndTime; // end; // For each input file selected by the User. frmProgress.Hide; frmProgress.Free; if lSuccessful 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; // lContinue. end; // lSuccussful. end; // lContinue. end; // BillingInputFixedTPAMS. procedure TelstraInvoiceFixedTPAMS( 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 (*.csv)|*.CSV|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 Files'; 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 lThisIsAPABX := (nProgram = PIMPORTBILLINGFILE_TELSTRA_TPAMS_PABX);//Global. BillingInputFixedTPAMS(stFiles, nBatch); end; // if lContinue then. end; // if lContinue then. finally stFiles.Free; end; end; // procedure TelstraInvoiceFixedTPAMS. end.