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. 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 GetMobileDataData( 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" // 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? 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. 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; sCallFrom : string; sCallTill : string; sMonthFrom: string; sYearFrom : string; sYearTill : string; sRateDescription : string; 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); sFieldValueList := ',353505,352505,351005,251599,323505,353501,150000,000000,'; ConvertString := TStringList.Create; // Used to convert SDF format to strings. try // Get the first occurence of the search string. Readln( InputTextFile, ReadString ); // To be sure the file isn't empty too... if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then begin 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); ConvertString.CommaText := ReadString; // Decode the line read. sBillCode := Trim(ConvertString.Strings[CODE_C]); lSaveToTable := (Pos(sBillCode,sFieldValueList)>0); if lSaveToTable then begin // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; cAbbr := Trim(UpperCase(ConvertString.Strings[ABBR_C])); SubString := Trim(ConvertString.Strings[LINE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ SubString +''','; end; // List of Unused Minimum Spend has a different structure // because it is grouped & listed separately to call details. if (sBillCode = '323505') then begin sServiceID := ''''+ AccountFormat(cAbbr) +''','; end; // Now register the ServiceID store value. sListFields := sListFields + 'ServiceID,'; sListValues := sListValues + sServiceID; // Process each billing code separately. // Calls (USAGE). if ((sBillCode = '353505') and (Pos(ConvertString.Strings[ABBR_C], '|DE|MO|') = 0)) then begin SubString := Trim(ConvertString.Strings[CODE2_C]) + Trim(ConvertString.Strings[CODE3_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[DESC1_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[DESC2_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':00'','; end; SubString := Trim(ConvertString.Strings[DESC4_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Origin,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := Trim(ConvertString.Strings[COST1_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 := ''; // SubString := Trim(ConvertString.Strings[DESC6_C]) +' '+ // Trim(ConvertString.Strings[DESC5_C]); SubString := Trim(ConvertString.Strings[DESC6_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; SubString := Trim(ConvertString.Strings[ABBR_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues + ''''+ sRateDescription + CleanString(SubString) +''','; end; SubString := Trim(ConvertString.Strings[CODE2_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Peak,'; if (UpperCase(SubString) = 'P') then sListValues := sListValues +'1,' else sListValues := sListValues +'0,'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[COST2_C]); // AmountExGST. curAmountCharged := StrToCurr(SubString); curTotalAmount := curAmountCharged; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + SubString +','; sListFields := sListFields + 'GSTFlag,'; if (Trim(ConvertString.Strings[ABBR_C]) = 'U-ROA') then begin // Roaming charges are GST-Free. sListValues := sListValues + '''N'','; curAmountCharged := 0; // GSTAmount. end else begin sListValues := sListValues + '''Y'','; curAmountCharged := curAmountCharged * fGSTRate; // GSTAmount. 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'. // Rental (CREDIT). else if (sBillCode = '352505') then begin SubString := Trim(ConvertString.Strings[DESC3_C]); curAmountCharged := StrToCurr(SubString); // AmountExGST. curTotalAmount := curAmountCharged; if (Round(curAmountCharged*1000)<>0) then begin sListFields := sListFields + 'TransactionTypeID,'; sListValues := sListValues + sDiscountCodeID+','; sListFields := sListFields + 'SupplierServiceCodeID,'; sListValues := sListValues + sSupplierDiscountCodeID+','; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + SubString +','; sListFields := sListFields + 'GSTFlag,'; sListValues := sListValues + '''Y'','; // Was 'N'-RJC071210. curAmountCharged := curAmountCharged * fGSTRate; // GSTAmount. curTotalAmount := curTotalAmount + curAmountCharged;// AmountIncGST. sListFields := sListFields + 'GSTAmount,'; sListValues := sListValues + CurrToStr(curAmountCharged) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + CurrToStr(curTotalAmount) +','; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sCallTill; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[ABBR_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+CleanString(SubString)+''','; end; end else begin lSaveToTable := False; end; // if (Round(StrToCurr(SubString)*100)<>0) then begin. end // '352505'. // ********** New phone number / service. *********************** else if (sBillCode = '150000') then begin // General information saved later. lSaveToTable := False; sServiceID := ''''+ AccountFormat(Trim(ConvertString.Strings[DESC3_C])) +''','; end // '150000'. // ********** Payment required. ********** // Rental (STDCHG). else if (sBillCode = '351005') then begin SubString := Trim(ConvertString.Strings[DESC2_C]); curAmountCharged := StrToCurr(SubString); // AmountExGST. curTotalAmount := curAmountCharged; if (Round(curAmountCharged*1000)<>0) then begin SubString := Trim(ConvertString.Strings[ABBR_C]); // Omit totals & items that have detailed transactions later. if (SubString = 'F-SMT') then lSaveToTable := False else if ((SubString = 'SRVC') and (LowerCase(Trim(ConvertString.Strings[DESC1_C])) = 'internet')) then lSaveToTable := False else begin sListFields := sListFields + 'TransactionTypeID,'; sListFields := sListFields + 'SupplierServiceCodeID,'; if (SubString = 'SRVC') then begin sListValues := sListValues + sRentCodeID+','; sListValues := sListValues + sSupplierRentCodeID+','; end else begin sListValues := sListValues + sCallCodeID+','; sListValues := sListValues + sSupplierCallCodeID+','; end; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + CurrToStr(curAmountCharged) +','; sListFields := sListFields + 'GSTFlag,'; sListValues := sListValues + '''Y'','; curAmountCharged := curAmountCharged * fGSTRate; // GSTAmount. 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; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[DESC1_C]+ Trim(ConvertString.Strings[DESC3_C])); nLenSubString := Length(SubString); if (nLenSubString > 0) then begin // Description added. SubString := Trim(ConvertString.Strings[DESC1_C]); if (Length(SubString) > 0) then begin if (Length(SubString) < nLenSubString) then begin SubString := SubString +' - '+ Trim(ConvertString.Strings[DESC3_C]); end; // if (Length(SubString) < nLenSubString) then begin. end // if (Length(SubString) > 0) then begin. else begin SubString := Trim(ConvertString.Strings[DESC3_C]); end; // if (Length(SubString) > 0) then begin..else. sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +''''+CleanString(SubString)+''','; end; // if (nLenSubString > 0) then begin. end; // if (SubString = 'F-SMT') then..else begin. end // '351005'. else begin lSaveToTable := False; end; // if (Round(StrToCurr(SubString)*100)<>0) then begin. end // '363005'. // ********** Payment required. ********** // Monthly Rental ('Features'). else if (sBillCode = '251599') then begin SubString := Trim(ConvertString.Strings[DESC1_C]); curAmountCharged := StrToCurr(SubString); // AmountExGST. curTotalAmount := curAmountCharged; if (Round(curAmountCharged*1000)<>0) then begin sListFields := sListFields + 'TransactionTypeID,'; sListValues := sListValues + sRentCodeID+','; sListFields := sListFields + 'SupplierServiceCodeID,'; sListValues := sListValues + sSupplierRentCodeID+','; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + SubString +','; sListFields := sListFields + 'GSTFlag,'; sListValues := sListValues + '''Y'','; curAmountCharged := curAmountCharged * fGSTRate; // GSTAmount. 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; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +'''Monthly features'','; end // if (Round(curAmountCharged*100)<>0) then begin. else begin lSaveToTable := False; end; // if (Round(StrToCurr(SubString)*100)<>0) then begin. end // '251599'. // ********** Payment required. ********** // Unused Minimum Spend - save the table contents to each ServiceID. else if (sBillCode = '323505') then begin SubString := Trim(ConvertString.Strings[DESC2_C]); curAmountCharged := StrToCurr(SubString); // Minimum Spend. SubString := Trim(ConvertString.Strings[DESC3_C]); curTotalAmount := StrToCurr(SubString); // Usage / Spend. sListFields := sListFields + 'TransactionTypeID,'; sListValues := sListValues + sRentCodeID+','; sListFields := sListFields + 'SupplierServiceCodeID,'; sListValues := sListValues + sSupplierRentCodeID+','; sListFields := sListFields + 'AmountExGST,'; sListValues := sListValues + CurrToStr(curAmountCharged) +','; sListFields := sListFields + 'AmountIncGST,'; sListValues := sListValues + CurrToStr(curTotalAmount) +','; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sCallFrom; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sCallTill; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; sListFields := sListFields + 'RateDescription,'; sListValues := sListValues +'''Unused Minimum Spend-'+ Trim(ConvertString.Strings[DESC1_C])+''','; sListFields := sListFields + 'Var01,'; sListValues := sListValues + '''MIN_SPEND'','; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; end // '323505'. // ********** Detail Header (sRateDescription) ********** // Reset the contents of sRateDescription. else if (sBillCode = '353501') then begin SubString := Trim(ConvertString.Strings[DESC4_C]); sRateDescription := CleanString(SubString); if (Length(sRateDescription) > 0) then begin sRateDescription := sRateDescription + ' '; end; lSaveToTable := False; end // '353501'. // ********** File Header informtion ********** else if (sBillCode = '000000') then begin // General information saved later. lSaveToTable := False; // First (actually the 2nd line) useful line of file. // This appears only once in the file, near the beginning. sCallFrom := ''''+YMD2DMY(ConvertString.Strings[DESC3_C]) +''','; sCallTill := ''''+YMD2DMY(ConvertString.Strings[DESC1_C]) +''','; sMonthFrom := Copy(sCallFrom,5,2); // Allow for quote marks. sYearFrom := Copy(sCallFrom,8,4); sYearTill := Copy(sCallTill,8,4); end // '000000'. else begin lSaveToTable := False; end; end; // if lSaveToTable then. if lSaveToTable then begin // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; end; // if lSaveToTable then begin. // 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 GetMobileDataData(). procedure AllocateMinSpend(const nBatch: LongInt); var sBatch : string; qryDC : TQuery; nMaxUsers : integer; cMinimumSpend : currency; cActualSpend : currency; cDiffSpend : currency; cAmountExGST : currency; cGSTAmount : currency; cAmountIncGST : currency; begin sBatch := IntToStr(nBatch); cAmountExGST := 0; fGSTRate := MAINFORM.GST_RATE; // 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 := qMinSpend.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. with qryDC do begin Close; SQL.Clear; SQL.Add('UPDATE tDataCollection'); SQL.Add('SET AmountExGST = '+ CurrToStr(cAmountExGST) +','); SQL.Add(' GSTAmount = '+ CurrToStr(cGSTAmount) +','); SQL.Add(' AmountIncGST = '+ CurrToStr(cAmountIncGST) +','); SQL.Add(' GSTFlag = ''Y'''); SQL.Add('WHERE BatchID = '+sBatch+' AND'); SQL.Add(' 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 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; lContinue : Boolean; sFile : String; slDummy : TStringList; begin // 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. GetMobileDataData(nBatch,sFile,lContinue); if lContinue then begin AllocateMinSpend(nBatch); 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; // 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().