unit TelstraCRSdbpaTPAMS; // Revision History. { RJC 190610: Created from TelstraFixedTPAMS.pas, Telstra TPAMS reference billing structure document using the CRS format (CSV). } 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 GetDetailCRSdbpaTPAMS( const FName:String; D:TDatabase; nBatchNumber:LongInt; var lReturnCode:Boolean); // Includes SaveSummaryChargesFixedEBS(). procedure BillingInputCRSdbpaTPAMS( stInFiles: Tstrings; nBatch: LongInt ); } procedure TelstraInvoiceCRSdbpaTPAMS( 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 GetDetailCRSdbpaTPAMS ( const FName: String; D: TDatabase; nBatchNumber : LongInt; var lReturnCode: Boolean ); const // Common, mostly, for input data (R), (N), (U). UniqueRecordID_C: integer = 0; // SequenceNo AccountNumber_C: integer = 1; // BillPeriodStartDate_C: integer = 2; // 'dd/mm/yyyy' - (FromDate) BillPeriodEndDate_C: integer = 3; // 'dd/mm/yyyy' - (ToDate) ChargeableItem_C: integer = 4; // ServiceID. RemedyCINumber_C: integer = 5; // RemedyCIDescription_C: integer = 6; // ProductType_C: integer = 7; // StaffID_C: integer = 8; // User_C: integer = 9; // CostCentre_C: integer = 10; // eg always 'VRT'. CustomProduct_C: integer = 11; // eg 'Other'. ChargeCategory_C: integer = 12; // ChargeTypeCategory_C: integer = 13; // ChargeTypeCode_C: integer = 14; // CallCode ChargeType_C: integer = 15; // RateDescription InvoiceSummaryGroup_C: integer = 16; // Qty_C: integer = 17; // Unit_C: integer = 18; // Rate_C: integer = 19; // StartDate_C: integer = 20; // 'dd/mm/yyyy' - TxnDate StartTime_C: integer = 21; // 'hh:mm:ss' - TxnTime EndDate_C: integer = 22; // 'dd/mm/yyyy' - ToDate CommissionDate_C: integer = 23; // 'dd/mm/yyyy' - not used. DecommissionDate_C: integer = 24; // 'dd/mm/yyyy' - not used. NumberDialed_C: integer = 25; // DialledNumber OriginPlace_C: integer = 26; // Origin RateDescription_C: integer = 27; // CallEventSessionCount_C: integer = 28; // Duration_C: integer = 29; // Duration VolumekB_C: integer = 30; // PurchaseOrder_C: integer = 31; // ProjectNumber_C: integer = 32; // ExclGST_C: integer = 33; // AmountExGST InclGST_C: integer = 34; // AmountIncGST AdditionalInformation_C: integer = 35; // ServiceRequestTitle_C: integer = 36; // LeasePaymentMade_C: integer = 37; // RemainingLeasePayments_C: integer = 38; // SiteCode_C: integer = 39; // SiteName_C: integer = 40; // Location_C: integer = 41; // AEnd_C: integer = 42; // BEnd_C: integer = 43; // DisputeReason_C: integer = 44; // DisputeDescription_C: integer = 45; // 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. sCallType: string; // 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 - GetDetailCRSTPAMS.'); 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 CRS 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 ); // Define ConvertString, used to convert CSV format to strings. ConvertString := TStringList.Create; try Readln( TelstraFile, ReadString ); // Test for the CRSdbpa TPAMS EBS file format for line #1 only, which has // the headers for all of the data fields in teh file. lContinue := ((Copy(ReadString,1,17)='Unique Record ID,') AND (Length(ReadString)>20)); if not lContinue then begin if LOGPROGRESS then begin LogToFile('Wrong file type - expected a CSV file with '+ '''Unique Record ID,'' starting the first line.'); end; frmProgress.Hide; MessageDlg('Incorrect input file type. This file is NOT formatted'+ #13#10+'as a CRS TPAMS Account. Try again.', mtWarning, [mbOK],0); end else begin // when 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; // Ready to start now. Read the next line - the first with data. Readln( TelstraFile, ReadString ); // Initiate the count of lines read. nCount := 0; // D.StartTransaction; // Continue to step through the remaining text of the file // and on each each line, 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); if LOGPROGRESS then begin LogToFile(ReplacePartString( ReadString, sCR, sComma )); end; // 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. if not LOGPROGRESS and LOGDEBUG then begin SubString := Trim(ConvertString[ChargeableItem_C]); // Choose the conditions being debugged: 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. SubString := Trim(ConvertString[UniqueRecordID_C]); nPos := Pos('-',SubString); while (nPos > 0) do begin SubString := Copy(SubString, nPos+1, Length(SubString)); nPos := Pos('-',SubString); end; // while nPos > 0. sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues + ''''+SubString+''','; SubString := Trim(ConvertString[ChargeableItem_C]); sListFields := sListFields + 'ServiceID,'; sListValues := sListValues +''''+ AccountFormat(SubString) +''','; SubString := Trim(ConvertString[StartDate_C]); if (Length(SubString) > 8) then begin SubString := Right('0'+SubString,10); sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; sListFields := sListFields + 'FromDate,'; sListValues := sListValues +''''+ SubString +''','; end else begin SubString := Trim(ConvertString[BillPeriodStartDate_C]); SubString := Right('0'+SubString,10); sListFields := sListFields + 'FromDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString[StartTime_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString[EndDate_C]); if (Length(SubString) > 8) then begin SubString := Right('0'+SubString,10); sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ SubString +''','; end else begin SubString := Trim(ConvertString[BillPeriodEndDate_C]); SubString := Right('0'+SubString,10); sListFields := sListFields + 'ToDate,'; sListValues := sListValues +''''+ SubString +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString[OriginPlace_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Origin,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := Trim(ConvertString[Duration_C]); // Seconds. if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := CleanString(Trim(ConvertString[ChargeTypeCode_C])); 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; // Define if Call, Rent or Other. if (Pos('Other',ConvertString[CustomProduct_C]) > 0 ) then sCallType := 'OTHER' else if (ConvertString[StartTime_C] = '00:00:00') then sCallType := 'RENT' else sCallType := 'CALL'; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add(''''+sCallType+''''); slAddFieldNames.Add('Description'); slAddFieldValues.Add(''''+sCallType+''''); 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[ChargeType_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[NumberDialed_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[ExclGST_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[InclGST_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. sListFields := sListFields + 'GSTAmount,GSTFlag,'; if (curValue > 0.0) then begin sListValues := sListValues + FloatToStrF(curValue,ffFixed,12,4)+ ',''Y'','; end else begin sListValues := sListValues + FloatToStrF(curValue,ffFixed,12,4)+ ',''N'','; 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 := 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; 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) +','; } // 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 ); 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 BillingInputCRSdbpaTPAMS( 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; // GetDetailCRSdbpaTPAMS(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; // BillingInputCRSdbpaTPAMS. procedure TelstraInvoiceCRSdbpaTPAMS( 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. BillingInputCRSdbpaTPAMS(stFiles, nBatch); end; // if lContinue then. end; // if lContinue then. finally stFiles.Free; end; end; // procedure TelstraInvoiceCRSdbpaTPAMS. end.