unit OptusMobileData; // 131105-RJC Copy from OptusMobile to OptusMobileData. // 131027-RJC 323505 Minimum Spend, and AllocateMinSpend() added. interface uses Messages, Classes, SysUtils, Graphics, Dialogs, Db, DbTables; procedure OptusMobileDataInvoice( const nBatch: LongInt; sDirectory: string ); // Not declaring the rest of functions/procedures here makes them private. implementation uses CriticalTests, Math, Main, DataMod, Progress, DateFunctions, FileFunctions, MyMessage, GenFns; var nSupplierID: integer; // Set in GetReferences(). sSupplierID: string; // Set in GetReferences(). qryOptus : TQuery; // Set in GetReferences(). sRentCodeID : string; // Set in GetReferences(). sCallCodeID : string; // Set in GetReferences(). sOtherCodeID : string; // Set in GetReferences(). sDiscountCodeID : string; // Set in GetReferences(). sSupplierRentCodeID : string; // Set in GetReferences(). sSupplierCallCodeID : string; // Set in GetReferences(). sSupplierOtherCodeID : string; // Set in GetReferences(). sSupplierDiscountCodeID : string; // Set in GetReferences(). fGSTRate : Real; // GST Rate - read from MainForm. sGSTRate : string; // String version of fGSTRate-for SQL statements. sFromDate : string; // Set in GetMobileDataHeadData(). sToDate : string; // Set in GetMobileDataHeadData(). InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. function GetDataReferences : boolean; var lSuccessful : boolean; begin // initialise the Static table elements defined as Private for this form. fGSTRate := MAINFORM.GST_RATE; Str(fGSTRate:6:4, sGSTRate); lSuccessful := True; qryOptus := TQuery.Create(MainForm); qryOptus.DatabaseName := 'dbPPdata'; qryOptus.SQL.Add('SELECT id'); qryOptus.SQL.Add('FROM tSupplier'); qryOptus.SQL.Add('WHERE SupplierName LIKE ''OPTUS%'''); qryOptus.Open; if qryOptus.Eof then begin lSuccessful := False; end else begin nSupplierID := qryOptus['id']; sSupplierID := IntToStr(nSupplierID); qryOptus.Close; qryOptus.SQL.Clear; qryOptus.SQL.Add('SELECT Code, CAST(TransactionTypeID AS VARCHAR(20)) '+ 'AS TransactionType, CAST(ID AS VARCHAR(20)) AS SupplierCodeID'); qryOptus.SQL.Add('FROM tSupplierServiceCode'); qryOptus.SQL.Add('WHERE SupplierID = '+sSupplierID); qryOptus.SQL.Add('AND Active = 1'); qryOptus.SQL.Add('AND Description LIKE ''RENT'''); qryOptus.Open; if qryOptus.Eof then lSuccessful := False else begin sRentCodeID := Trim(qryOptus['TransactionType']); sSupplierRentCodeID := Trim(qryOptus['SupplierCodeID']); end; qryOptus.Close; qryOptus.SQL.Strings[qryOptus.SQL.Count-1] := 'AND Description LIKE ''CALLS'''; qryOptus.Open; if qryOptus.Eof then lSuccessful := False else begin sCallCodeID := Trim(qryOptus['TransactionType']); sSupplierCallCodeID := Trim(qryOptus['SupplierCodeID']); end; qryOptus.Close; qryOptus.SQL.Strings[qryOptus.SQL.Count-1] := 'AND Description LIKE ''UNKNOWN%'''; qryOptus.Open; if qryOptus.Eof then lSuccessful := False else begin sOtherCodeID := qryOptus['TransactionType']; sSupplierOtherCodeID := Trim(qryOptus['SupplierCodeID']); end; qryOptus.Close; qryOptus.SQL.Strings[qryOptus.SQL.Count-1] := 'AND Description LIKE ''MISC%'''; qryOptus.Open; if qryOptus.Eof then lSuccessful := False else begin sDiscountCodeID := Trim(qryOptus['TransactionType']); sSupplierDiscountCodeID := Trim(qryOptus['SupplierCodeID']); end; qryOptus.Close; if lSuccessful then begin qryOptus.SQL.Delete(qryOptus.SQL.Count-1); qryOptus.Open; end else begin MessageDlg('Entries for RENT, CALLS, UNKNOWN, MISCELLANEOUS must'#13#10+ 'be added to tSupplierServiceCode for OPTUS before proceeding.', mtWarning, [mbOk], 0); end; end; // if qryOptus.Eof then begin..else. Result := lSuccessful; end; // procedure GetDataReferences(). procedure GetMobileDataHeadData( const nBatch: LongInt; const FName : string; var lReturnCode: Boolean ); // Data handled is for calls made from Optus mobile phones. // This function scans the input file for specific data and saves it. // const RECORD_BYTES_C: Integer = 570; // Typical minimum bytes per record-CHD. PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. // Header File Format - VICTRACK.CHD SequenceID_C: integer = 0; // SequenceID field. BillingPointCode_C: integer = 1; BillingPointName_C: integer = 2; OrganisationCode_C: integer = 3; OrganisationName_C: integer = 4; AgencyCode_C: integer = 5; AgencyName_C: integer = 6; BusinessunitCode_C: integer = 7; BusinessUnitName_C: integer = 8; DepartmentCode_C: integer = 9; DepartmentName_C: integer = 10; CostCentreCode_C: integer = 11; // CallCode field. CostCentreName_C: integer = 12; SupplierCode_C: integer = 13; SupplierName_C: integer = 14; AccountCode_C: integer = 15; AccountName_C: integer = 16; InvoiceCode_C: integer = 17; IssueDate_C: integer = 18; InvDescription_C: integer = 19; ServiceCode_C: integer = 20; // ServiceID field. ServiceName_C: integer = 21; Charge_C: integer = 22; // Call amount field. GST_C: integer = 23; // GST amount field. Description_C: integer = 24; // RateDescription field. ChargeFrom_C: integer = 25; // FromDate field. ChargeTo_C: integer = 26; // ToDate field. Quantity_C: integer = 27; Duration_C: integer = 28; WorkOrder_C: integer = 29; Level1_C: integer = 30; Level2_C: integer = 31; Level3_C: integer = 32; PrevDisputed_C: integer = 33; ReportCode_C: integer = 34; ReportCodeDescription_C: integer = 35; LocationAddress_C: integer = 36; LocationSuburb_C: integer = 37; LocationPostcode_C: integer = 38; sTab : string = Chr( 09 ); sCR : string = Chr( 13 ); // sCRLF : string = Chr( 13 ) + Chr( 10 ); var nFileSize: Integer; // Actual file size. InputTextFile: TextFile; sCaption : String; // Used for modifying Progress caption. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. nProgressCounted : integer; // Count each set of PROGRESS_COUNTER_C. Progress: TProgressForm; nCount: Integer; nSoFar: Integer; nFactor: Real; vSubString : variant; // Used in the Locate() function. // sFieldValueList : String; // Lists those billing codes to be handled here. // lSaveToTable : boolean; // sBillCode: String; // cAbbr: String; sServiceID: string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; slAddFieldNames : TStringList; // Calls to AddToTable() only. slAddFieldValues : TStringList; // Calls to AddToTable() only. nListValues : integer; qryDC : TQuery; sLastCallCode : string; sLastCallTransactionTypeID : string; sLastSupplierCallCodeID : string; lFound : boolean; // nLenSubString : integer; curAmountCharged : Currency; curTotalAmount : Currency; sRateDescription : string; linvoiceDatesAreRead : boolean; nStringCount : integer; begin lReturnCode := False; sBatch := IntToStr( nBatch ); sServiceID := 'NULL,'; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; slAddFieldNames := TStringList.Create; // Calls to AddToTable() only. slAddFieldValues := TStringList.Create; // Calls to AddToTable() only. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; sToDate := ''; sFromDate := ''; linvoiceDatesAreRead := False; if GetDataReferences then begin nFileSize := GetFileSize(FName); if (nFileSize > 0) then nFactor := 100.0 * RECORD_BYTES_C / nFileSize else nFactor := 100.0; // Open the input (text) billing file for reading. AssignFile( InputTextFile, Fname ); // Bigger buffer for faster reads. System.SetTextBuf(InputTextFile, InputTextFileBuffer); Reset( InputTextFile ); Progress := TProgressForm.Create(MainForm); Progress.Caption := 'Reading The Input Text File'; Progress.btnCancel.Visible := False; // Convert nFileSize to x100kBytes (based on 1.0MB=1,048,576B). nFileSize := nFileSize div 104858; Str( (nFileSize / 10.0):5:1, sCaption ); Progress.Msg.Caption := 'Reading ('+Trim(sCaption)+'MB) ...'; Progress.Show; Progress.ProcessWinMessages(MainForm); ConvertString := TStringList.Create; // To convert data to separate strings. try Readln( InputTextFile, ReadString ); // To be sure the file isn't empty too... if (Length( Trim(ReadString)) > 0) or (not Eof( InputTextFile)) then begin // The first line is column headers - skip to the next line (data). Readln( InputTextFile, ReadString ); nProgressCounted := 0; // Initiate the data variables. nCount := 0; // Continue to search through the remaining text of the file // for occurrences of the search string. On each each find, // process the resultant string. repeat if ((Pos('RECURRING', ReadString)>0) or (Pos('Govt number', ReadString)>0)) then begin // Replace tabs (chr(09)) with CRLF. ReadString := ReplacePartString( ReadString, sTab, sCR ); // Save the input string into ConvertString. ConvertString.Text := ReadString; // Separate fields into strings. nStringCount := ConvertString.Count - 1; //Read the invoice period dates for all of the billing. if not linvoiceDatesAreRead then begin if ( Length(sFromDate) = 0 ) then begin SubString := Trim(ConvertString.Strings[ChargeFrom_C]); if (Length(SubString) = 10) then // dd/mm/yyyy. sFromDate := ''''+ SubString + ''','; end; if ( Length(sToDate) = 0 ) then begin SubString := Trim(ConvertString.Strings[ChargeTo_C]); if (Length(SubString) = 10) then // dd/mm/yyyy. sToDate := ''''+ SubString + ''','; end; linvoiceDatesAreRead := (( Length(sFromDate) = 0 ) and ( Length(sToDate) = 0 )); end; // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(ConvertString.Strings[SequenceID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(UpperCase(ConvertString.Strings[ServiceCode_C])); sServiceID := AccountFormat(SubString); // Now register the ServiceID store value. sListFields := sListFields + 'ServiceID,'; sListValues := sListValues + ''''+ sServiceID +''','; SubString := Trim(ConvertString.Strings[CostCentreCode_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; vSubString := SubString; with qryOptus do lFound := Locate('Code',vSubString,[loCaseInsensitive]); if lFound then begin sLastCallTransactionTypeID := qryOptus.FieldByName('TransactionType').AsString; sLastSupplierCallCodeID := qryOptus.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('''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 qryOptus 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 + sLastCallTransactionTypeID+','+ sLastSupplierCallCodeID+','; end // if (Length(SubString) > 0) then begin. else begin sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+ sSupplierCallCodeID+','; end; // CallCode. if (nStringCount >= Description_C) then begin SubString := Trim(ConvertString.Strings[Description_C]); sRateDescription := CleanString(SubString); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues + ''''+ sRateDescription + ''','; end; // not empty. end; // StringCount. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[Charge_C]);// AmountExGST. curAmountCharged := StrToCurr(SubString); curTotalAmount := curAmountCharged; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + SubString +','; sListFields := sListFields + 'GSTFlag,'; if (Pos('Roaming', sRateDescription) > 0) then begin // Roaming charges are GST-Free. sListValues := sListValues + '''N'','; curAmountCharged := 0; // GSTAmount. end else begin sListValues := sListValues + '''Y'','; // curAmountCharged := curAmountCharged * fGSTRate; SubString := Trim(ConvertString.Strings[GST_C]);// GSTAmount. curAmountCharged := StrToCurr(SubString); end; curTotalAmount := curTotalAmount+curAmountCharged;// AmountIncGST. sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + CurrToStr(curAmountCharged) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + CurrToStr(curTotalAmount) +','; if linvoiceDatesAreRead or (Length(sFromDate) > 0) then begin sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; end; if linvoiceDatesAreRead or (Length(sToDate) > 0) then begin sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; end; // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; // Keep track of how much work has been done to date. Inc(nCount); Inc(nProgressCounted); if (nProgressCounted > PROGRESS_COUNTER_C) then begin // Report progress to the user. nSoFar := Round(nCount * nFactor); // Do not know how big the file is - make sure don't exceed 100%. if (nSoFar > PROGRESS_MAX_C) then Progress.ProgressBar1.Position := PROGRESS_MAX_C else Progress.ProgressBar1.Position := nSoFar; nProgressCounted := 0; end; // if (nProgressCounted > PROGRESS_COUNTER_C) then. end; // if RECURRING ... Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); lReturnCode := True; end; // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then. finally // Done with the input file and resources, close them. CloseFile( InputTextFile ); Progress.Close; ConvertString.Free; FieldStringList.Free; ValueStringList.Free; qryStringList.Free; qryDC.Free; end; Progress.Close; end; // if GetReferences then begin. end; // procedure GetMobileDataHeadData(). procedure GetMobileDataCallData( const nBatch: LongInt; const FName : string; var lReturnCode: Boolean ); // Data handled is for calls made from Optus mobile phones and fixed phones. // This function scans the input file for relevant data and saves it. // const RECORD_BYTES_C: Integer = 150; // Typical minimum bytes per record. PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. // Call Detail Format - VICTRACK.CLD SequenceID_C: integer = 0; // SequenceID field. BillingPointCode_C: integer = 1; OrganisationCode_C: integer = 2; AgencyCode_C: integer = 3; BusinessUnitCode_C: integer = 4; DepartmentCode_C: integer = 5; CostCentreCode_C: integer = 6; // CallCode field. SupplierCode_C: integer = 7; ServiceCode_C: integer = 8; // ServiceID field. CallDate_C: integer = 9; // date of call field. CallTime_C: integer = 10; // time of call field. Dialled_C: integer = 11; // Dialled number field. Duration_C: integer = 12; // Call duration field. Charge_C: integer = 13; // Call charge field. GST_C: integer = 14; // GST amount field. Type_C: integer = 15; // RateDescription field. Rate_C: integer = 16; // Peak / off-peak field. Origin_C: integer = 17; // origin of call field. Destination_C: integer = 18; // Destination of call field. sTab : string = Chr( 09 ); sCR : string = Chr( 13 ); // sCRLF : string = Chr( 13 ) + Chr( 10 ); var nFileSize: Integer; // Actual file size. InputTextFile: TextFile; // InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. sCaption : String; // Used for modifying Progress caption. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. nProgressCounted : integer; // Count each set of PROGRESS_COUNTER_C. Progress: TProgressForm; nCount: Integer; nSoFar: Integer; nFactor: Real; vSubString : variant; // Used in the Locate() function. sServiceID: string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; slAddFieldNames : TStringList; // Calls to AddToTable() only. slAddFieldValues : TStringList; // Calls to AddToTable() only. nListValues : integer; qryDC : TQuery; sLastCallCode : string; sLastCallTransactionTypeID : string; sLastSupplierCallCodeID : string; lFound : boolean; curAmountCharged : Currency; curTotalAmount : Currency; { Made global: sCallFrom : string; sCallTill : string; sMonthFrom: string; sYearFrom : string; sYearTill : string; } sRateDescription : string; nStringCount : integer; begin lReturnCode := False; sBatch := IntToStr( nBatch ); sServiceID := 'NULL,'; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; slAddFieldNames := TStringList.Create; // Calls to AddToTable() only. slAddFieldValues := TStringList.Create; // Calls to AddToTable() only. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; { sCallFrom := ''; sCallTill := ''; sMonthFrom := ''; sYearFrom := ''; sYearTill := ''; } if GetDataReferences then begin nFileSize := GetFileSize(FName); if (nFileSize > 0) then nFactor := 100.0 * RECORD_BYTES_C / nFileSize else nFactor := 100.0; // Open the input (text) billing file for reading. AssignFile( InputTextFile, Fname ); // Bigger buffer for faster reads. System.SetTextBuf(InputTextFile, InputTextFileBuffer); Reset( InputTextFile ); Progress := TProgressForm.Create(MainForm); Progress.Caption := 'Reading The Input Text File'; Progress.btnCancel.Visible := False; // Convert nFileSize to x100kBytes (based on 1.0MB=1,048,576B). nFileSize := nFileSize div 104858; Str( (nFileSize / 10.0):5:1, sCaption ); Progress.Msg.Caption := 'Reading ('+Trim(sCaption)+'MB) ...'; Progress.Show; Progress.ProcessWinMessages(MainForm); ConvertString := TStringList.Create; // To convert data to separate strings. try Readln( InputTextFile, ReadString ); // To be sure the file isn't empty too... if (Length( Trim(ReadString)) > 0) or (not Eof( InputTextFile)) then begin // The first line is column headers - skip to the next line (data). Readln( InputTextFile, ReadString ); nProgressCounted := 0; // Initiate the data variables. nCount := 0; // Continue to search through the remaining text of the file // for occurrences of the search string. On each each find, // process the resultant string. repeat // Progress.ProcessWinMessages(MainForm); // Replace tabs (chr(09)) with CRLF. ReadString := ReplacePartString( ReadString, sTab, sCR ); // Save the input string into ConvertString. ConvertString.Text := ReadString; // Separate fields into strings. nStringCount := ConvertString.Count - 1; // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(ConvertString.Strings[SequenceID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(UpperCase(ConvertString.Strings[ServiceCode_C])); sServiceID := AccountFormat(SubString); // Now register the ServiceID store value. sListFields := sListFields + 'ServiceID,'; sListValues := sListValues + ''''+ sServiceID +''','; SubString := Trim(ConvertString.Strings[CostCentreCode_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; vSubString := SubString; with qryOptus do lFound := Locate('Code',vSubString,[loCaseInsensitive]); if lFound then begin sLastCallTransactionTypeID := qryOptus.FieldByName('TransactionType').AsString; sLastSupplierCallCodeID := qryOptus.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 qryOptus 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 + sLastCallTransactionTypeID+','+ sLastSupplierCallCodeID+','; end // if (Length(SubString) > 0) then begin. else begin sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+ sSupplierCallCodeID+','; end; // CallCode. SubString := Trim(ConvertString.Strings[CallDate_C]); if (Length(SubString) > 0) then begin { // Year is not set (always '0000') - fix it. SubString := YMD2DMY(SubString); If (Copy(SubString,4,2)=sMonthFrom) then SubString := Copy(SubString,1,6) + sYearFrom else SubString := Copy(SubString,1,6) + sYearTill; } sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[CallTime_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ SubString +':00'','; end; if (nStringCount >= Origin_C) then begin SubString := Trim(ConvertString.Strings[Origin_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Origin,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; // not empty. end; // StringCount. if (nStringCount >= Destination_C) then begin SubString := Trim(ConvertString.Strings[Destination_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Destination,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; // not empty. end; // StringCount. if (nStringCount >= Dialled_C) then begin SubString := Trim(ConvertString.Strings[Dialled_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; // not empty. end; // StringCount. SubString := Trim(ConvertString.Strings[Duration_C]); if (Length(SubString) > 0) then begin SubString := ConvertTimeToSecs(CleanString(SubString)); sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ SubString +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if (nStringCount >= Type_C) then begin SubString := Trim(ConvertString.Strings[Type_C]); sRateDescription := CleanString(SubString); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues + ''''+ sRateDescription + ''','; end; // not empty. end; // StringCount. if (nStringCount >= Rate_C) then begin SubString := Trim(ConvertString.Strings[Rate_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Peak,'; if (UpperCase(SubString) = 'P') then sListValues := sListValues +'1,' else sListValues := sListValues +'0,'; end; end; // StringCount. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[Charge_C]);// AmountExGST. curAmountCharged := StrToCurr(SubString); curTotalAmount := curAmountCharged; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + SubString +','; sListFields := sListFields + 'GSTFlag,'; if (Pos('Roaming', sRateDescription) > 0) then begin // Roaming charges are GST-Free. sListValues := sListValues + '''N'','; curAmountCharged := 0; // GSTAmount. end else begin sListValues := sListValues + '''Y'','; // curAmountCharged := curAmountCharged * fGSTRate; SubString := Trim(ConvertString.Strings[GST_C]);// GSTAmount. curAmountCharged := StrToCurr(SubString); end; curTotalAmount := curTotalAmount+curAmountCharged;// AmountIncGST. sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + CurrToStr(curAmountCharged) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + CurrToStr(curTotalAmount) +','; { sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sCallFrom; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sCallTill; end // '353505'. } // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; // Keep track of how much work has been done to date. Inc(nCount); Inc(nProgressCounted); if (nProgressCounted > PROGRESS_COUNTER_C) then begin // Report progress to the user. nSoFar := Round(nCount * nFactor); // Do not know how big the file is - make sure don't exceed 100%. if (nSoFar > PROGRESS_MAX_C) then Progress.ProgressBar1.Position := PROGRESS_MAX_C else Progress.ProgressBar1.Position := nSoFar; nProgressCounted := 0; end; // if (nProgressCounted > PROGRESS_COUNTER_C) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); lReturnCode := True; end; // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then. finally // Done with the input file and resources, close them. CloseFile( InputTextFile ); Progress.Close; ConvertString.Free; FieldStringList.Free; ValueStringList.Free; qryStringList.Free; qryDC.Free; end; Progress.Close; end; // if GetReferences then begin. end; // procedure GetMobileDataCallData(). procedure GetMobileDataRentData( const nBatch: LongInt; const FName : string; var lReturnCode: Boolean ); // Data handled is for calls made from Optus mobile phones. // This function scans the input file for relevant data and saves it. // // Variable sFieldValueList contains the billing codes processed below: // 150000 Start of details for a service. // 352505 Rental credit details. // 351005 Rental details. // 251599 Rental monthly "features" // 352005 Other charges and credit. // 323505 Unused Minimum Spend // 353501 Call details header. // 353505 Call details. // 000000 Invoicing dates. const RECORD_BYTES_C: Integer = 150; // Typical minimum bytes per record. PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. { LINE_C: integer = 0; // Bill entry number. CODE_C: integer = 1; // Billing code. ABBR_C: integer = 2; // Abbreviation-'M', or rent Description. DESC1_C: integer = 3; // Description-date of call. DESC2_C: integer = 4; // Description-time of call. DESC3_C: integer = 5; // Description-rent amount. DESC4_C: integer = 6; // Description-origin of call. DESC5_C: integer = 7; // Rate Description field. DESC6_C: integer = 8; // Dialled number. CODE2_C: integer = 9; // Peak/Off Peak. CODE3_C: integer = 10; // Call type S,N,L. COST1_C: integer = 13; // Call duration field. COST2_C: integer = 14; // Call charge field. DESC7_C: integer = 15; // GST flag? } // Rental Format - VICTRACK.L4D SequenceID_C: integer = 0; // Bill entry number. BillingPointCode_C: integer = 1; BillingPointName_C: integer = 2; OrganisationCode_C: integer = 3; OrganisationName_C: integer = 4; AgencyCode_C: integer = 5; AgencyName_C: integer = 6; BusinessUnitCode_C: integer = 7; BusinessUnitName_C: integer = 8; DepartmentCode_C: integer = 9; DepartmentName_C: integer = 10; CostCentreCode_C: integer = 11; // Billing code. CostCentreName_C: integer = 12; SupplierCode_C: integer = 13; SupplierName_C: integer = 14; AccountCode_C: integer = 15; AccountName_C: integer = 16; InvoiceCode_C: integer = 17; IssueDate_C: integer = 18; InvDescription_C: integer = 19; ServiceCode_C: integer = 20; // ServiceID field. ServiceName_C: integer = 21; Charge_C: integer = 22; // Service charge ex GST. GST_C: integer = 23; // Service charge GST. Description_C: integer = 24; // Rate Description-field #1. ChargeFrom_C: integer = 25; // Date - start of invoicing. ChargeTo_C: integer = 26; // Date - end of invoicing. Quantity_C: integer = 27; Duration_C: integer = 28; WorkOrder_C: integer = 29; Level1_C: integer = 30; Level2_C: integer = 31; Level3_C: integer = 32; PrevDisputed_C: integer = 33; ReportCode_C: integer = 34; ReportCodeDescription_C: integer = 35; // Rate Description-field #2. sTab : string = Chr( 09 ); sCR : string = Chr( 13 ); // sCRLF : string = Chr( 13 ) + Chr( 10 ); var nFileSize: Integer; // Actual file size. InputTextFile: TextFile; // InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. sCaption : String; // Used for modifying Progress caption. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. nProgressCounted : integer; // Count each set of PROGRESS_COUNTER_C. Progress: TProgressForm; nCount: Integer; nSoFar: Integer; nFactor: Real; vSubString : variant; // Used in the Locate() function. sServiceID: string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; slAddFieldNames : TStringList; // Calls to AddToTable() only. slAddFieldValues : TStringList; // Calls to AddToTable() only. nListValues : integer; qryDC : TQuery; qrytDCUpdate : TQuery; sLastCallCode : string; sLastCallTransactionTypeID : string; sLastSupplierCallCodeID : string; lFound : boolean; sCallFrom : string; sCallTill : string; sBaseServiceID : string; curAmountCharged : Currency; curTotalAmount : Currency; sRateDescription : string; lUpdateDatesCollected : boolean; nStringCount : integer; begin lReturnCode := False; // lUpdateDatesCollected := False; sBatch := IntToStr( nBatch ); sServiceID := 'NULL,'; sCallFrom := ''; sCallTill := ''; sBaseServiceID := ''; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; slAddFieldNames := TStringList.Create; // Used in Calls to AddToTable() only. slAddFieldValues := TStringList.Create; // Used in Calls to AddToTable() only. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qrytDCUpdate := TQuery.Create(MainForm); qrytDCUpdate.DatabaseName := 'dbPPdata'; if GetDataReferences then begin nFileSize := GetFileSize(FName); if (nFileSize > 0) then nFactor := 100.0 * RECORD_BYTES_C / nFileSize else nFactor := 100.0; // Open the input (text) billing file for reading. AssignFile( InputTextFile, Fname ); // Bigger buffer for faster reads. System.SetTextBuf(InputTextFile, InputTextFileBuffer); Reset( InputTextFile ); Progress := TProgressForm.Create(MainForm); Progress.Caption := 'Reading The ''L4D'' Text File'; Progress.btnCancel.Visible := False; // Convert nFileSize to x100kBytes (based on 1.0MB=1,048,576B). nFileSize := nFileSize div 104858; Str( (nFileSize / 10.0):5:1, sCaption ); Progress.Msg.Caption := 'Reading ('+Trim(sCaption)+'MB) ...'; Progress.Show; Progress.ProcessWinMessages(MainForm); ConvertString := TStringList.Create; // Used to convert SDF format to strings. try // Get the first line in the file. Readln( InputTextFile, ReadString ); // To be sure the file isn't empty too... if (Length( Trim(ReadString)) > 0) or (not Eof( InputTextFile)) then begin // The first line is column headers - skip to the next line (data). Readln( InputTextFile, ReadString ); nProgressCounted := 0; // Initiate the data variables. nCount := 0; // Continue to search through the remaining text of the file // for occurrences of the search string. On each each find, // process the resultant string. repeat Progress.ProcessWinMessages(MainForm); // Replace tabs (chr(09)) with CRLF. ReadString := ReplacePartString( ReadString, sTab, sCR ); // Save the input string into ConvertString. ConvertString.Text := ReadString; // Separate fields into strings. nStringCount := ConvertString.Count - 1; // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; SubString := Trim(ConvertString.Strings[SequenceID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(UpperCase(ConvertString.Strings[ServiceCode_C])); sServiceID := AccountFormat(SubString); // Now register the ServiceID store value. sListFields := sListFields + 'ServiceID,'; sListValues := sListValues + ''''+ sServiceID +''','; SubString := Trim(ConvertString.Strings[CostCentreCode_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; vSubString := SubString; with qryOptus do lFound := Locate('Code',vSubString,[loCaseInsensitive]); if lFound then begin sLastCallTransactionTypeID := qryOptus.FieldByName('TransactionType').AsString; sLastSupplierCallCodeID := qryOptus.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('''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 qryOptus 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 + sLastCallTransactionTypeID+','+ sLastSupplierCallCodeID+','; end // if (Length(SubString) > 0) then begin. else begin sListFields := sListFields + 'TransactionTypeID,'+ 'SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+ sSupplierCallCodeID+','; end; // CallCode. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[Description_C]); if (nStringCount >= ReportCodeDescription_C) then begin SubString := SubString + ' - '+ Trim(ConvertString.Strings[ReportCodeDescription_C]); end else begin SubString := SubString + ' - Mobile Rental Costs'; end; // nStringCount. sRateDescription := CleanString(SubString); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues + ''''+ sRateDescription +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[Charge_C]);// AmountExGST. curAmountCharged := StrToCurr(SubString); curTotalAmount := curAmountCharged; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + SubString +','; sListFields := sListFields + 'GSTFlag,'; if (Pos('Roaming', sRateDescription) > 0) then begin // Roaming charges are GST-Free. sListValues := sListValues + '''N'','; curAmountCharged := 0; // GSTAmount. end else begin sListValues := sListValues + '''Y'','; // curAmountCharged := curAmountCharged * fGSTRate; SubString := Trim(ConvertString.Strings[GST_C]);// GSTAmount. curAmountCharged := StrToCurr(SubString); end; curTotalAmount := curTotalAmount+curAmountCharged;// AmountIncGST. sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + CurrToStr(curAmountCharged) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + CurrToStr(curTotalAmount) +','; // Add dates. sCallFrom := Trim(ConvertString.Strings[ChargeFrom_C]); sCallTill := Trim(ConvertString.Strings[ChargeTo_C]); if ((Length(sCallFrom)=10) and (Length(sCallTill)=10)) then begin // Dates not stored in Call details - update tDataCollection later. sListFields := sListFields + 'FromDate,'; sListValues := sListValues + ''''+ sCallFrom +''','; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + ''''+ sCallTill +''','; lUpdateDatesCollected := True; end else begin lUpdateDatesCollected := False; end; // Populate call dates in tDataCollection if new ServiceID. if lUpdateDatesCollected and (sServiceID <> sBaseServiceID) then begin sBaseServiceID := sServiceID; qrytDCUpdate.SQL.Clear; with qrytDCUpdate do begin SQL.Add('UPDATE tDataCollection'); SQL.Add('SET FromDate = '''+ sCallFrom +''','); SQL.Add(' ToDate = '''+ sCallTill +''''); SQL.Add('WHERE BatchID = '+ sBatch); SQL.Add(' AND ServiceID = '''+ sServiceID +''''); SQL.Add(' AND (FromDate IS NULL'); SQL.Add(' OR ToDate IS NULL)'); end; qrytDCUpdate.ExecSQL; end; // lUpdateDatesCollected. // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; // Keep track of how much work has been done to date. Inc(nCount); Inc(nProgressCounted); if (nProgressCounted > PROGRESS_COUNTER_C) then begin // Report progress to the user. nSoFar := Round(nCount * nFactor); // Do not know how big the file is - make sure don't exceed 100%. if (nSoFar > PROGRESS_MAX_C) then Progress.ProgressBar1.Position := PROGRESS_MAX_C else Progress.ProgressBar1.Position := nSoFar; nProgressCounted := 0; end; // if (nProgressCounted > PROGRESS_COUNTER_C) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); lReturnCode := True; end; // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then. finally // Done with the input file and resources, close them. CloseFile( InputTextFile ); Progress.Close; ConvertString.Free; FieldStringList.Free; ValueStringList.Free; qryStringList.Free; qrytDCUpdate.Free; qryDC.Free; end; Progress.Close; end; // if GetReferences then begin. end; // procedure GetMobileDataRentData(). procedure AllocateMinSpend(const nBatch: LongInt); // If the Minimum Spend is not met then all data charges are adjusted to the // minimum stated in the invoice. Otherwise, the actual expenditure is as // invoiced. var sBatch : string; qryDC : TQuery; nMaxUsers : integer; cMinimumSpend : currency; cActualSpend : currency; cDiffSpend : currency; { cAmountExGST : currency; cGSTAmount : currency; cAmountIncGST : currency; } begin sBatch := IntToStr(nBatch); // Get constants, the number of services, and costs. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; with qryDC do begin SQL.Add('SELECT Count(*) AS NumUsers, Sum(AmountExGST) AS AmtMinSpend,'); SQL.Add(' Sum(AmountIncGST) AS AmtActualSpend'); SQL.Add('FROM tDataCollection'); SQL.Add('WHERE BatchID = '+sBatch+' AND'); SQL.Add(' Var01 LIKE ''MIN_SPEND'''); Open; end; // qryDC. // Save constants for later use in this procedure. nMaxUsers := qryDC.FieldByName('NumUsers').AsInteger; cMinimumSpend := qryDC.FieldByName('AmtMinSpend').AsCurrency; cActualSpend := qryDC.FieldByName('AmtActualSpend').AsCurrency; cDiffSpend := (cMinimumSpend - cActualSpend); qryDC.Close; // Now, get the individual tallies. if (nMaxUsers > 0) and (cDiffSpend > 0) then begin { // First, remove those services that met the minimum spend requirements. with qryDC do begin SQL.Clear; SQL.Add('DELETE'); SQL.Add('FROM tDataCollection'); SQL.Add('WHERE BatchID = '+sBatch+' AND'); SQL.Add('Var01 LIKE ''MIN_SPEND'' AND'); SQL.Add('(AmountExGST <= AmountIncGST)'); ExecSQL; end; // Now record how many services have to pay the excess charge. with qryDC do begin Close; SQL.Clear; SQL.Add('SELECT Count(*) AS NumUsers'); SQL.Add('FROM tDataCollection'); SQL.Add('WHERE BatchID = '+sBatch+' AND'); SQL.Add(' Var01 LIKE ''MIN_SPEND'''); Open; end; // qryDC. nMaxUsers := qryDC.FieldByName('NumUsers').AsInteger; // And how much each will pay to account for the additional charge. cAmountExGST := (cDiffSpend / nMaxUsers); cGSTAmount := cAmountExGST * fGSTRate; // GSTAmount. cAmountIncGST := cAmountExGST + cGSTAmount; // AmountIncGST. } // Now process the distribution of the additional fee. // NB sGSTRate is global in this file & set in GetDataReferences() above. with qryDC do begin Close; SQL.Clear; SQL.Add('UPDATE tDataCollection'); SQL.Add('SET tDataCollection.AmountExGST = theMinSpend.MinSpend,'); SQL.Add(' tDataCollection.GSTAmount = theMinSpend.MinGST,'); SQL.Add(' tDataCollection.AmountIncGST = theMinSpend.MinTotal,'); SQL.Add(' tDataCollection.GSTFlag = ''Y'''); SQL.Add('FROM ('); SQL.Add(' SELECT origMinSpend.MinSpend, origMinSpend.MinGST,'); SQL.Add(' (origMinSpend.MinSpend + origMinSpend.MinGST) AS MinTotal,'); // SQL.Add(' origMinSpend.ServiceID, origMinSpend.BatchID'); SQL.Add(' origMinSpend.ServiceID'); SQL.Add(' FROM ('); SQL.Add(' SELECT (tDataCollection.AmountExGST - '+ 'tDataCollection.AmountIncGST) AS MinSpend,'); SQL.Add(' ((tDataCollection.AmountExGST - '+ 'tDataCollection.AmountIncGST)*'+sGSTRate+') AS MinGST,'); SQL.Add(' tDataCollection.ServiceID'); // SQL.Add(' tDataCollection.ServiceID, tDataCollection.BatchID, '+ // 'tDataCollection.Var01'); SQL.Add(' FROM tDataCollection'); SQL.Add(' WHERE tDataCollection.BatchID = '+sBatch+' AND'); SQL.Add(' tDataCollection.Var01 LIKE ''MIN_SPEND'''); SQL.Add(' ) AS origMinSpend'); SQL.Add(') AS theMinSpend'); SQL.Add('WHERE tDataCollection.BatchID = '+sBatch+' AND'); SQL.Add(' tDataCollection.ServiceID LIKE theMinSpend.ServiceID AND '); SQL.Add(' tDataCollection.Var01 LIKE ''MIN_SPEND'''); ExecSQL; end; // qryDC. end // A fee is charged.. else begin // Done. Remove this summary data read under invoice code='323505'. with qryDC do begin SQL.Clear; SQL.Add('DELETE'); SQL.Add('FROM tDataCollection'); SQL.Add('WHERE BatchID = '+sBatch+' AND'); SQL.Add('Var01 LIKE ''MIN_SPEND'''); ExecSQL; end; end; // Have users & a shortfall spend. qryDC.Close; qryDC.Free; end; // procedure AllocateMinSpend(). procedure ConfirmDates(const nBatch: LongInt); var sBatch : string; qryDC : TQuery; sCallFrom : string; sCallTill : string; begin sBatch := IntToStr(nBatch); qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; sCallFrom := ''; sCallTill := ''; // Set date values if one or both missing. with qryDC do begin SQL.Add('SELECT LTRIM(RTRIM(FromDate)) AS FromDate'); SQL.Add('FROM tDataCollection'); SQL.Add('WHERE BatchID = '+sBatch); Open; end; // qryDC. while not qryDC.Eof and (Length(sCallFrom) < 10) do begin sCallFrom := qryDC.FieldByName('FromDate').AsString; qryDC.Next; end; // while. with qryDC do begin Close; SQL.Clear; SQL.Add('SELECT LTRIM(RTRIM(ToDate)) AS ToDate'); SQL.Add('FROM tDataCollection'); SQL.Add('WHERE BatchID = '+sBatch); Open; while not Eof and (Length(sCallTill) < 10) do begin sCallTill := FieldByName('ToDate').AsString; Next; end; end; // Ready. Ensure dates are populated. with qryDC do begin Close; SQL.Clear; SQL.Add('UPDATE tDataCollection'); SQL.Add('SET FromDate = '''+sCallFrom+''''); SQL.Add('WHERE BatchID = '+sBatch+' AND'); SQL.Add(' (FromDate IS NOT null) AND'); SQL.Add(' (LEN(LTRIM(RTRIM(FromDate))) < 10)'); ExecSQL; SQL.Clear; SQL.Add('UPDATE tDataCollection'); SQL.Add('SET ToDate = '''+sCallTill+''''); SQL.Add('WHERE (BatchID = '+sBatch+') AND'); SQL.Add(' (ToDate IS NOT null) AND'); SQL.Add(' (LEN(LTRIM(RTRIM(ToDate))) < 10)'); ExecSQL; end; // qryDC. end; // ConfirmDates. procedure OptusMobileDataInvoice( const nBatch: LongInt; sDirectory: string ); // This function prompts the user for the name of the Vodafone supplied // text file and then calls the VodaMobile function (which reads the // file into a table and controls its processing). var OpenDlg : TOpenDialog; Progress: TProgressForm; lContinue : Boolean; sFile : String; slDummy : TStringList; sPos : integer; begin // Assign the buffer size for reading text files (default-128B). // First, we must establish which file is the one to be processed. OpenDlg := TOpenDialog.Create(MainForm); OpenDlg.Filter := 'Optus Billing file (*.bil)|*.BIL|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 Optus Input File'; // Read and save the user's choices before continuing. lContinue := OpenDlg.Execute; sFile := OpenDlg.FileName; OpenDlg.Free; if lContinue and (Length(Trim(sFile)) > 0) then begin if FileExists(sFile) then begin // Remove any data already loaded. slDummy := TStringList.Create; DeleteFromDC(nBatch, slDummy, lContinue); slDummy.Free; // Reload the data. sPos := Pos('.', sFile); // First, process the header details file. sFile := Copy(sFile, 1, sPos) + 'CHD'; GetMobileDataHeadData(nBatch,sFile,lContinue); if lContinue then begin // Now, process the call details file. sFile := Copy(sFile, 1, sPos) + 'CLD'; GetMobileDataCallData(nBatch,sFile,lContinue); if lContinue then begin // Now process the rent details file. sPos := Pos('.', sFile); sFile := Copy(sFile, 1, sPos) + 'L4D'; GetMobileDataRentData(nBatch,sFile,lContinue); end; // lContinue. end; // lContinue. if lContinue then begin // Add reporting of progress in data reviews. Progress := TProgressForm.Create(MainForm); Progress.Caption := 'Reviewing the Input data'; Progress.ProgressBar1.Visible := False; Progress.btnCancel.Visible := False; Progress.Msg.Caption := 'Allocating Minimum Spend ...'; Progress.Show; Progress.ProcessWinMessages(MainForm); AllocateMinSpend(nBatch); Progress.Msg.Caption := 'Checking invoice dates ...'; Progress.ProcessWinMessages(MainForm); ConfirmDates(nBatch); UpdateBatchStatus(nBatch, MainForm.BATCH_STATUS_IMPORT); Progress.Hide; Progress.ProcessWinMessages(MainForm); Progress.Close; // ******************************************************************* // ******************************************************************* // 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; // if lContinue then begin. end; // if lContinue then begin. end else begin MessageDlg('That file does not exist.', mtWarning, [mbAbort], 0 ); end; // if FileExists(sFile) then begin. end; // if (Length(Trim(sFile)) > 0) then begin. end; // OptusMobileDataInvoice(). end. // OptusMobileData().