unit Telmax; interface uses Messages, Classes, SysUtils, Graphics, Dialogs, Db, DbTables; function GetReferences() : boolean; procedure GetCustomerID(const sLastServiceID:string;var sCustomerID:string); { procedure GetTimsCalls( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); procedure GetTimsRent( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); procedure GetTimsVoicemail( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); procedure GetTimsSwitchboard( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); procedure GetTimsSACC( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); procedure TelmaxCallInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); procedure TelmaxRentInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); procedure TelmaxVoiceMailInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); procedure TelmaxSwitchInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); } procedure TelmaxSACCInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); procedure TelmaxInvoice(nBatch: LongInt; const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); implementation uses CriticalTests, Math, Main, DataMod, Progress, DateFunctions, FileFunctions, MyMessage, GenFns; var nSupplierID: integer; // Set in GetReferences(). sSupplierID: string; // Set in GetReferences(). qryTelmax : TQuery; // Set in BillingInput(). sRentCodeID : string; // Set in GetReferences(). sCallCodeID : string; // Set in GetReferences(). sSupplyRentCodeID : string; // Set in GetReferences(). sSupplyCallCodeID : string; // Set in GetReferences(). qCustID : TQuery; // Used in GetCustID(). slCust : TStringList; // Used in GetCustID(). sVRTID : string; fGSTRate : Real; // GST Rate - read from MainForm. sGSTRate : string; // String version of fGSTRate-for SQL statements. sTEC12Ex, sTEC12GST, sTEC12Inc : string; // TEC 12. sTECorEx, sTECorGST, sTECorInc : string; // TEC default. sTECGSTflag : string; // TEC flag. sTVC12Ex, sTVC12GST, sTVC12Inc : string; // TEC 12 (VRT). sTVCorEx, sTVCorGST, sTVCorInc : string; // TEC default (VRT). sTVCGSTflag : string; // TEC flag (VRT). sVMEx, sVMGST, sVMInc, sVMGSTflag : string; // Voicemail. sVVMEx, sVVMGST, sVVMInc, sVVMGSTflag :string; // Voicemail (VRT). sSWEx, sSWGST, sSWInc, sSWGSTflag : string; // Switchboard. sVSWEx, sVSWGST, sVSWInc, sVSWGSTflag : string; // Switchboard (VRT). function GetReferences : boolean; var lSuccessful : boolean; qry : TQuery; qryRef : TQuery; sRefCode : string; curValue,curValueGST : currency; begin // initialise the Static table elements defined as Private for this form. fGSTRate := MAINFORM.GST_RATE; Str(fGSTRate:6:4, sGSTRate); lSuccessful := True; qryTelmax := TQuery.Create(MainForm); qryTelmax.DatabaseName := 'dbPPdata'; qryTelmax.SQL.Add('SELECT id'); qryTelmax.SQL.Add('FROM tSupplier'); qryTelmax.SQL.Add('WHERE SupplierName LIKE ''VICTRACK%'''); qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin nSupplierID := qryTelmax['id']; sSupplierID := IntToStr(nSupplierID); qryTelmax.Close; qryTelmax.SQL.Clear; qryTelmax.SQL.Add('SELECT Code, CAST(ID AS VARCHAR(20)) AS "SCodeID", '+ 'CAST(TransactionTypeID AS VARCHAR(20)) AS TransactionType'); qryTelmax.SQL.Add('FROM tSupplierServiceCode'); qryTelmax.SQL.Add('WHERE SupplierID = '+sSupplierID); qryTelmax.SQL.Add('AND Code LIKE ''TELRENT%'''); qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin sRentCodeID := Trim(qryTelmax['TransactionType']); sSupplyRentCodeID := Trim(qryTelmax['SCodeID']); end; qryTelmax.Close; qryTelmax.SQL.Strings[qryTelmax.SQL.Count-1] := 'AND Code LIKE ''TELCALL%'''; qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin sCallCodeID := Trim(qryTelmax['TransactionType']); sSupplyCallCodeID := Trim(qryTelmax['SCodeID']); end; qryTelmax.Close; if lSuccessful then begin qryTelmax.SQL.Delete(qryTelmax.SQL.Count-1); qryTelmax.Open; // Set defaults. sTEC12Ex := '0,'; // TEC Not a Victrack user. sTEC12GST := '0,'; sTEC12Inc := '0,'; sTECorEx := '0,'; sTECorGST := '0,'; sTECorInc := '0,'; sTECGSTflag := '''Y'','; sTVC12Ex := '0,'; // TEC A Victrack user. sTVC12GST := '0,'; sTVC12Inc := '0,'; sTVCorEx := '0,'; sTVCorGST := '0,'; sTVCorInc := '0,'; sTVCGSTflag := '''N'','; sVMEx := '0,'; // Voicemail. sVMGST := '0,'; sVMInc := '0,'; sVMGSTflag := '''Y'','; sVVMEx := '0,'; // Voicemail (Victrack). sVVMGST := '0,'; sVVMInc := '0,'; sVVMGSTflag := '''N'','; sSWEx := '0,'; // Switchboard. sSWGST := '0,'; sSWInc := '0,'; sSWGSTflag := '''Y'','; sVSWEx := '0,'; // Switchboard (Victrack). sVSWGST := '0,'; sVSWInc := '0,'; sVSWGSTflag := '''N'','; qry := TQuery.Create(MainForm); qry.DatabaseName := 'dbPPdata'; qry.SQL.Add('SELECT id'); qry.SQL.Add('FROM tCustomer'); qry.SQL.Add('WHERE ShipTo LIKE ''VRT'''); qry.Open; if qry.Eof then sVRTID := '0' else sVRTID := IntToStr(qry.FieldByName('id').AsInteger); qry.Close; qryRef := TQuery.Create(MainForm); qryRef.DatabaseName := 'dbPPdata'; qryRef.SQL.Add('SELECT id, code'); qryRef.SQL.Add('FROM tServiceType'); qryRef.SQL.Add('WHERE Code LIKE ''TEC%'''); qryRef.Open; while not qryRef.Eof do begin sRefCode := qryRef.FieldByName('Code').AsString; if (sRefCode = 'TEC12') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; sTEC12Ex := CurrToStr(curValue)+','; sTEC12GST := CurrToStr(curValueGST)+','; sTEC12Inc := CurrToStr(curValue + curValueGST)+','; end else if (sRefCode = 'TECor') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; // sTECorEx := Curr2Str(curValue)+','; // sTECorGST := Curr2Str(curValueGST)+','; // sTECorInc := Curr2Str(curSum(curValue,curValueGST))+','; sTECorEx := CurrToStr(curValue)+','; sTECorGST := CurrToStr(curValueGST)+','; sTECorInc := CurrToStr(curValue + curValueGST)+','; end else if (sRefCode = 'TECVM') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; // sVMEx := Curr2Str(curValue)+','; // sVMGST := Curr2Str(curValueGST)+','; // sVMInc := Curr2Str(curSum(curValue,curValueGST))+','; sVMEx := CurrToStr(curValue)+','; sVMGST := CurrToStr(curValueGST)+','; sVMInc := CurrToStr(curValue + curValueGST)+','; sVVMEx := sVMEx; sVVMGST := '0,'; sVVMInc := sVMEx; end else if (sRefCode = 'TECSW') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency); qry.Close; curValueGST := curValue * fGSTRate; // sSWEx := Curr2Str(curValue)+','; // sSWGST := Curr2Str(curValueGST)+','; // sSWInc := Curr2Str(curSum(curValue,curValueGST))+','; sSWEx := CurrToStr(curValue)+','; sSWGST := CurrToStr(curValueGST)+','; sSWInc := CurrToStr(curValue + curValueGST)+','; sVSWEx := sSWEx; sVSWGST := '0,'; sVSWInc := sSWEx; end; qryRef.Next; end; // while not qryRef.Eof do begin. // Get references for TVC (Victrack) and TEC (non-Victrack) users. qryRef.Close; qryRef.SQL.Strings[2] := 'WHERE Code LIKE ''TVC%'''; qryRef.Open; while not qryRef.Eof do begin sRefCode := qryRef.FieldByName('Code').AsString; if (sRefCode = 'TVC12') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; sTVC12Ex := Curr2Str(curValue)+','; sTVC12GST := '0,'; sTVC12Inc := sTVC12Ex; end else if (sRefCode = 'TVCor') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; sTVCorEx := Curr2Str(curValue)+','; sTVCorGST := '0,'; sTVCorInc := sTVCorEx; end; qryRef.Next; end; // while not qryRef.Eof do begin. qryRef.Close; qryRef.Free; end else begin MessageDlg('Entries for RENT, CALLS must be added to '#13#10+ 'tSupplierServiceCode for VICTRACK before proceeding.', mtWarning, [mbOk], 0); end; end; // if qryTelmax.Eof then begin..else. Result := lSuccessful; end; // procedure GetReferences(). procedure GetCustomerID(const sLastServiceID:string;var sCustomerID:string); begin slCust[5] := 'WHERE tServiceID.ServiceID LIKE '''+ sLastServiceID +''''; qCustID.Close; qCustID.SQL := slCust; qCustID.Open; if qCustID.Eof or VarIsNull(qCustID['CustomerID']) then sCustomerID := '' else sCustomerID := IntToStr(qCustID.FieldByName('CustomerID').AsInteger); qCustID.Close; end; // GetCustomerID(). procedure GetTimsCalls( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // Data handled is for calls made from the Victrack fixed phones (PABX) network // and recorded by the Telmax monitoing software. // This function reads the input file and saves it in the database. // const RECORD_BYTES_C: Integer = 50; // 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%. SHIPTO_C: integer = 0; // ShipTo. SERVID_C: integer = 1; // ServiceID. EXTN_C: integer = 2; // Extension. CALLDATE_C: integer = 3; // Date call made. ENDTIME_C: integer = 4; // Time call ended. SITE_C: integer = 5; // SiteID. INLET_C: integer = 6; // Inlet number. DIRECTN_C: integer = 7; // Inlet direction. DIALLED_C: integer = 8; // DialledNumber. CALLTYPE_C: integer = 9; // Call type. CTYPDESC_C: integer = 10; // Call type description. TIME_C: integer = 11; // Call duration. COST_C: integer = 12; // Call charge field. var InputTextFile: System.TextFile; InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. Progress: TProgressForm; nCount: Integer; nFactor : Real; nBlock : Integer; vSubString : variant; // - ditto, used in the Locate() function. nTotalCount : integer; sFromDate : string; sToDate : 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; sLastTransactionTypeID : string; sLastSupplyCallCodeID : string; lFound : boolean; lFinished : boolean; sCallTill : string; MessageForm : TMessagesForm; begin lReturnCode := False; sBatch := IntToStr(nBatchNumber); sFromDate := ''''+FormattedDateString(dStartDate)+''','; sToDate := ''''+FormattedDateString(dEndDate)+''','; 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'; sCallTill := ''; if GetReferences then begin // Open the input (text) billing file for reading. AssignFile( InputTextFile, Fname ); FileMode := 0; // Set file access to read only. // Bigger buffer for faster reads. System.SetTextBuf(InputTextFile, InputTextFileBuffer); Reset( InputTextFile ); Progress := TProgressForm.Create(MainForm); Progress.Caption := 'Reading The Telmax Call File'; Progress.btnCancel.Visible := False; Progress.Show; Progress.ProcessWinMessages(MainForm); 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 // Initiate the data variables. nCount := 0; lFinished := False; nFactor := 0.0100; // Count to 100 every 10,000 records. nBlock := 0; nTotalCount := 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); // Decode the line read after converting the input line to SDF format. ConvertString.CommaText := ConvertCDE2SDF(ReadString); // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ServiceID, Var05,'; sListValues := sListValues + '''' + SubString +''', '''+ SubString +''',';; end; SubString := Trim(ConvertString.Strings[CALLTYPE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; vSubString := SubString; with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCallCodeID := Trim(qryTelmax['SCodeID'])+','; end else begin sLastTransactionTypeID := sCallCodeID; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add('''CALLS'''); slAddFieldNames.Add('Description'); slAddFieldValues.Add('''Telmax Call'''); slAddFieldNames.Add('TransactionTypeDesc'); slAddFieldValues.Add('''Telmax Call'''); slAddFieldNames.Add('TransactionTypeID'); slAddFieldValues.Add(sLastTransactionTypeID); slAddFieldNames.Add('SupplierID'); slAddFieldValues.Add(sSupplierID); slAddFieldNames.Add('Active'); slAddFieldValues.Add('1'); AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues); qryTelmax.Close; qryTelmax.Open; with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCallCodeID := Trim(qryTelmax['SCodeID'])+','; end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCallCodeID := sSupplyCallCodeID; end; end; // if (not) lFound. end; // if (sLastCallCode <> SubString) then begin. if (Length(sLastTransactionTypeID) > 0) then begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+sLastSupplyCallCodeID; end; end else begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+sLastSupplyCallCodeID; end; SubString := Trim(ConvertString.Strings[CALLDATE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[ENDTIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''','; end; SubString := Trim(ConvertString.Strings[TIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+SubString+''','; end; SubString := Trim(ConvertString.Strings[CTYPDESC_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,Peak,'; sListValues := sListValues +''''+ CleanString(SubString) +''',1,'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[COST_C]); sListFields := sListFields + 'AmountExGST,AmountIncGST,GSTFlag,'; sListValues := sListValues + SubString +',' + SubString +',''N'','; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // tServiceType.Description. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // ServiceID.Description. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var03,'; // tLocation.Description. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var04,'; // tLocation.Description. sListValues := sListValues +''''+ SubString +''','; 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(nTotalCount); Inc(nBlock); if (nBlock=100) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * nFactor)>100.00) then. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. if Eof( InputTextFile ) then lFinished := True; Readln( InputTextFile, ReadString ); // Read in the next line of data. if Eof( InputTextFile ) and (Length(Trim(ReadString)) = 0) then lFinished := True; until lFinished; Progress.Hide; MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing Telmax Call charges'; MessageForm.Msg.Caption := 'Updating with applied rates ...'; MessageForm.Show; Progress.ProcessWinMessages(MainForm); qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET GSTAmount = GSTrate.GSTAmt,'); qryStringList.Add(' AmountIncGST = GSTrate.AmtExGST + GSTrate.GSTAmt,'); qryStringList.Add(' GSTflag = ''Y'''); qryStringList.Add('FROM (SELECT tDataCollection.ID, '+ 'tDataCollection.AmountExGST AS "AmtExGST", '+ '(tDataCollection.AmountExGST * '+sGSTRate+') AS "GSTAmt"'); qryStringList.Add('FROM (tDataCollection'); qryStringList.Add('LEFT JOIN tServiceID '+ 'ON tDataCollection.ServiceID = tServiceID.ServiceID)'); qryStringList.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch); qryStringList.Add('AND tDataCollection.AmountExGST <> 0'); qryStringList.Add('AND tServiceType.Code LIKE ''TQ'') AS GSTrate'); qryStringList.Add('WHERE tDataCollection.ID = GSTrate.ID'); qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; MessageForm.Close; MessageForm.Free; 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 GetTimsCalls(). procedure GetTimsRent( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // Data handled is for rent of the Victrack fixed phones (PABX) network // This function reads the input file and saves it to the database. // const PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. SHIPTO_C: integer = 0; // ShipTo. SERVID_C: integer = 1; // ServiceID. EXTN_C: integer = 2; // Extension. STARTDATE_C: integer = 3; // Date for start of the Rental period. ENDDATE_C: integer = 4; // Date for end of the Rental period. SITE_C: integer = 5; // SiteID. TEC_C: integer = 6; // TEC number - eg T02R02S03, which is // tec & value, RSC & value, SFC & value. var InputTextFile: TextFile; InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. Progress: TProgressForm; nCount: Integer; nFactor: Real; nBlock : Integer; nTotalCount : integer; sFromDate : string; sToDate : string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; LastServiceID : string; sCustomerID : string; sRent12ExGST, sRent12IncGST, s12GST : string; sRentExGST, sRentIncGST, sGST : string; sGSTflag : string; sPhoneRentDigital : string; sPhoneRentDefault : string; nListValues : integer; qryDC : TQuery; begin lReturnCode := False; sBatch := IntToStr(nBatchNumber); sFromDate := FormattedDateString(dStartDate); sToDate := FormattedDateString(dEndDate); FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; if GetReferences then begin // Get transactiontypes. sPhoneRentDigital := '0,'; sPhoneRentDefault := sPhoneRentDigital; qryDC.SQL.Add('SELECT id, Description'); qryDC.SQL.Add('FROM tTransactionType'); qryDC.SQL.Add('WHERE Description LIKE ''% Phone Rent'''); qryDC.Open; // Test for Digital and Standard Phone Rent transactions. while not qryDC.Eof do begin if (Copy(qryDC.FieldByName('Description').AsString,1,5) = 'Digit') then sPhoneRentDigital := IntToStr(qryDC.FieldByName('id').AsInteger)+',' else if (Copy(qryDC.FieldByName('Description').AsString,1,5) = 'Stand') then sPhoneRentDefault := IntToStr(qryDC.FieldByName('id').AsInteger)+','; qryDC.Next; end; qryDC.Close; qryDC.SQL.Clear; // 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 Telmax Rent File'; Progress.btnCancel.Visible := False; Progress.Show; Progress.ProcessWinMessages(MainForm); 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 // Initiate the data variables. nFactor := 0.1000; // Count to 100 every 1,000 records. nBlock := 0; nTotalCount := 0; 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); // Decode the line read after converting the input line to SDF format. ConvertString.CommaText := ConvertCDE2SDF(ReadString); // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ServiceID,Var05,Var11,'; sListValues := sListValues + ''''+SubString +''','''+ SubString+''','''+SubString+''','; // sListFields := sListFields + 'ServiceID,Var11,'; // sListValues := sListValues + ''''+SubString+''','''+SubString+''','; end; // Set defaults and calculate charge rates. if (SubString <> LastServiceID) then begin // Find references to define the applicable charges. LastServiceID := SubString; GetCustomerID(LastServiceID,sCustomerID); if (sCustomerID = sVRTID) then begin sRent12ExGST := sTVC12Ex; sRent12IncGST := sTVC12Inc; s12GST := sTVC12GST; sRentExGST := sTVCorEx; sRentIncGST := sTVCorInc; sGST := sTVCorGST; sGSTflag := sTVCGSTflag; end else begin sRent12ExGST := sTEC12Ex; sRent12IncGST := sTEC12Inc; s12GST := sTEC12GST; sRentExGST := sTECorEx; sRentIncGST := sTECorInc; sGST := sTECorGST; sGSTflag := sTECGSTflag; end; // if (sCustomerID = sVRTID) then begin..else. end; // if new ServiceID. // Determine the applicable charges. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := UpperCase(Copy(Trim(ConvertString.Strings[TEC_C]),2,2)); // TEC='XX'-TEC unknown: unused or not allocated, set rent at default; // TEC='NN'-allocated elsewhere a/c reallocation, do not charge again. FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'+ 'TransactionTypeID,SupplierServiceCodeID,'); if (Pos(SubString, 'NN') = 0) then begin if (SubString = '12') then ValueStringList.Add(sRent12ExGST+s12GST+sRent12IncGST+sGSTFlag+ sPhoneRentDigital+sSupplyRentCodeID+',') else ValueStringList.Add(sRentExGST+sGST+sRentIncGST+sGSTFlag+ sPhoneRentDefault+sSupplyRentCodeID+','); end else begin ValueStringList.Add('0,0,0,''N'','+ sRentCodeID+','+ sSupplyRentCodeID+','); end; SubString := Trim(ConvertString.Strings[STARTDATE_C]); sListFields := sListFields + 'FromDate,'; if (Length(SubString) > 0) then sListValues := sListValues +''''+ SubString +''',' else sListValues := sListValues + sFromDate; SubString := Trim(ConvertString.Strings[ENDDATE_C]); sListFields := sListFields + 'ToDate,'; if (Length(SubString) > 0) then sListValues := sListValues +''''+ SubString +''',' else sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // Var01. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // Var02. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[TEC_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var09,'; // Var09. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SHIPTO_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var10,'; // Var10. sListValues := sListValues +''''+ SubString +''','; 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(nTotalCount); Inc(nBlock); if (nBlock=10) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * Factor)>=100.00) then. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); // Now update the values stored in the tDataCollection table with // any alternate service charge rates stored separately in tRates. qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.AmountExGST = DC4.AmountExGST,'); qryStringList.Add(' tDataCollection.GSTAmount = DC4.GSTAmount,'); qryStringList.Add(' tDataCollection.AmountIncGST = DC4.AmountIncGST'); qryStringList.Add('FROM (SELECT DC3.*, '+ 'CAST((DC3.AmountExGST + DC3.GSTAmount) AS money) AS "AmountIncGST"'); qryStringList.Add('FROM (SELECT DC2.ID, DC2.AmountExGST,'); qryStringList.Add(' CAST((CASE WHEN tDataCollection.GSTFlag LIKE ''Y'' '+ 'THEN (DC2.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"'); qryStringList.Add('FROM (SELECT DC1.ID, (Max(tRate.Cost)/12) AS "AmountExGST"'); qryStringList.Add('FROM ((SELECT DC0.ID, DC0.ServiceID, (CASE'); qryStringList.Add(' WHEN DC0.TEC IS NULL THEN 0'); qryStringList.Add(' WHEN DC0.TEC = ''XX'' THEN 0'); qryStringList.Add(' WHEN DC0.TEC = ''NN'' THEN NULL'); qryStringList.Add(' WHEN DATALENGTH(RTRIM(DC0.TEC)) = 0 THEN 0'); qryStringList.Add(' ELSE CAST(DC0.TEC AS int) END) AS "Length"'); qryStringList.Add('FROM (SELECT tDataCollection.ID, '+ 'tDataCollection.ServiceID,'); qryStringList.Add(' SUBSTRING(tDataCollection.Var09,2,2) AS "TEC"'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('LEFT JOIN tTransactionType '+ 'ON tDataCollection.TransactionTypeID = tTransactionType.ID'); qryStringList.Add('LEFT JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tTransactionGroup.Description LIKE ''RENT'') AS DC0) AS DC1'); qryStringList.Add('LEFT JOIN tServiceID AS SI1 '+ 'ON DC1.ServiceID = SI1.ServiceID)'); qryStringList.Add('INNER JOIN tRate '+ 'ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) '+ 'AND (ROUND(DC1.Length,0,0) = ROUND(tRate.Length,0,0)))'); qryStringList.Add('WHERE DC1.Length IS NOT NULL'); qryStringList.Add('AND tRate.Cost IS NOT NULL'); qryStringList.Add('GROUP BY DC1.ID) AS DC2'); qryStringList.Add('INNER JOIN tDataCollection '+ 'ON tDataCollection.ID = DC2.ID) AS DC3) AS DC4'); qryStringList.Add('WHERE tDataCollection.ID = DC4.ID'); // Execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; 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 GetTimsRent. procedure GetTimsVoiceMail( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // Data handled is for rent of Voicemail services on the Victrack fixed phones // (PABX) network. // This function reads the input file and saves the data to the database. // const PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. SHIPTO_C: integer = 0; // ShipTo. SERVID_C: integer = 1; // ServiceID. EXTN_C: integer = 2; // Extension. CALLDATE_C: integer = 3; // Date call made. ENDTIME_C: integer = 4; // Time call ended. SITE_C: integer = 5; // SiteID. INLET_C: integer = 6; // Inlet number. DIRECTN_C: integer = 7; // Inlet direction. DIALLED_C: integer = 8; // DialledNumber. CALLTYPE_C: integer = 9; // Call type. CTYPDESC_C: integer = 10; // Call type description. TIME_C: integer = 11; // Call duration. COST_C: integer = 12; // Call charge field. var InputTextFile: TextFile; InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. Progress: TProgressForm; nCount: Integer; nFactor: Real; nBlock : Integer; nTotalCount : integer; sFromDate : string; sToDate : string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; LastServiceID : string; sCustomerID : string; sRentExGST : string; sRentIncGST : string; sGST : string; sGSTflag : string; // sServiceType : string; Deleted-default ok. nListValues : integer; qryDC : TQuery; // MessageForm : TMessagesForm; lDummyEntry : boolean; curGSTCall : currency; lFound : boolean; sLastCallCode : string; sLastTransactionTypeID : string; sLastSupplyCodeID : string; slAddFieldNames, slAddFieldValues : TStringList; begin lReturnCode := False; sBatch := IntToStr(nBatchNumber); sFromDate := ''''+FormattedDateString(dStartDate)+''','; sToDate := ''''+FormattedDateString(dEndDate)+''','; slAddFieldNames := TStringList.Create; // Calls to AddToTable() only. slAddFieldValues := TStringList.Create; // Calls to AddToTable() only. FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; if GetReferences then begin // 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 Telmax Voicemail File'; Progress.btnCancel.Visible := False; Progress.Show; Progress.ProcessWinMessages(MainForm); 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 // Initiate the data variables. nFactor := 0.1000; // Count to 100 every 1,000 records. nBlock := 0; nTotalCount := 0; 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); // Decode the line read after converting the input line to SDF format. ConvertString.CommaText := ConvertCDE2SDF(ReadString); SubString := UpperCase(Trim(ConvertString.Strings[CALLTYPE_C])); lDummyEntry := (SubString = 'X'); // 'X'-Dummy entry: exists for every Extension for Web Billing. // If also DialledNumber='VOICEMAIL', then Voicemail is enabled and // rent is due (must add this charge as a transaction). if not lDummyEntry or (lDummyEntry and (UpperCase(Trim(ConvertString.Strings[DIALLED_C])) = 'VOICEMAIL')) then begin // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ServiceID,Var05,'; sListValues := sListValues + '''VM'+SubString+''',''VM'+SubString+''','; end; // Set defaults and calculate charge rates. if (SubString <> LastServiceID) then begin // Find references to define the applicable charges. LastServiceID := SubString; GetCustomerID('VM'+LastServiceID,sCustomerID); if (sCustomerID = sVRTID) then begin sRentExGST := sVVMEx; sRentIncGST := sVVMInc; sGST := sVVMGST; sGSTflag := sVVMGSTflag; // sServiceType := '''VM1'','; (or VVI) Deleted-default ok. end else begin sRentExGST := sVMEx; sRentIncGST := sVMInc; sGST := sVMGST; sGSTflag := sVMGSTflag; // sServiceType := '''VM'','; (orVOS,VOI)Deleted-default ok. end; // if (sCustomerID = sVRTID) then begin..else. end; // if new ServiceID. // Determine the applicable charges. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'); // FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'+ // 'ServiceType,'); SubString := UpperCase(Trim(ConvertString.Strings[CALLTYPE_C])); if lDummyEntry then // Rent transaction to be created. ValueStringList.Add(sRentExGST+sGST+sRentIncGST+sGSTFlag) // ValueStringList.Add(sRentExGST+sGST+sRentIncGST+sGSTFlag+ // sServiceType) else begin SubString := Trim(ConvertString.Strings[COST_C]); if (sCustomerID = sVRTID) then begin ValueStringList.Add(SubString+',0,'+SubString+','+ sGSTFlag); // sGSTFlag+sServiceType); end else begin // curGSTCall := curFormat(fGSTRate * StrToCurr(SubString)); // ValueStringList.Add(SubString+','+Curr2Str(curGSTCall)+','+ // Curr2Str(curSum(curGSTCall,StrToCurr(SubString)))+','+ // sGSTFlag); curGSTCall := fGSTRate * StrToCurr(SubString); ValueStringList.Add(SubString+','+CurrToStr(curGSTCall)+','+ CurrToStr(curGSTCall + StrToCurr(SubString))+','+ sGSTFlag); // sGSTFlag+sServiceType); end; // if (sCustomerID = sVRTID) then begin. end; // if (SubString = 'X') then..else. SubString := Trim(ConvertString.Strings[CALLTYPE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; with qryTelmax do if lDummyEntry then lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sRentCodeID]), [loCaseInsensitive]) else lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCodeID := Trim(qryTelmax['SCodeID']); end else begin if lDummyEntry then begin sLastTransactionTypeID := sRentCodeID; sLastSupplyCodeID := sSupplyRentCodeID; end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCodeID := sSupplyCallCodeID; end; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); if lDummyEntry then slAddFieldValues.Add('''RENT''') else slAddFieldValues.Add('''CALLS'''); slAddFieldNames.Add('Description'); slAddFieldNames.Add('TransactionTypeDesc'); if lDummyEntry then begin slAddFieldValues.Add('''Voicemail Rent'''); slAddFieldValues.Add('''Telmax Rent'''); end else begin slAddFieldValues.Add('''Voicemail Call'''); slAddFieldValues.Add('''Telmax Call'''); end; slAddFieldNames.Add('TransactionTypeID'); slAddFieldValues.Add(sLastTransactionTypeID); slAddFieldNames.Add('SupplierID'); slAddFieldValues.Add(sSupplierID); slAddFieldNames.Add('Active'); slAddFieldValues.Add('1'); AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues); qryTelmax.Close; qryTelmax.Open; with qryTelmax do if lDummyEntry then lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sRentCodeID]), [loCaseInsensitive]) else lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; end else begin if lDummyEntry then begin sLastTransactionTypeID := sRentCodeID; sLastSupplyCodeID := sSupplyRentCodeID; end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCodeID := sSupplyCallCodeID; end; end; end; // if (not) lFound. end; // if (sLastCallCode <> SubString) then begin. if (Length(sLastTransactionTypeID) > 0) then begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; if lDummyEntry then begin // sListValues := sListValues + sRentCodeID+','; sListValues := sListValues + sRentCodeID+','+sSupplyRentCodeID+','; end else begin // sListValues := sListValues + sCallCodeID+','; sListValues := sListValues + sCallCodeID+','+sSupplyCallCodeID+','; end; end; end else begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; if lDummyEntry then begin sListValues := sListValues + sRentCodeID+','+sSupplyRentCodeID+','; end else begin sListValues := sListValues + sCallCodeID+','+sSupplyCallCodeID+','; end; end; SubString := Trim(ConvertString.Strings[CALLDATE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[ENDTIME_C]); if (Length(SubString) = 6) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''','; end; SubString := Trim(ConvertString.Strings[TIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+SubString+''','; end; SubString := Trim(ConvertString.Strings[CTYPDESC_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,Peak,'; sListValues := sListValues +''''+ CleanString(SubString) +''',0,'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // Extn. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // SiteID. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var03,'; // InletNo. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var04,'; // Inlet direction. sListValues := sListValues +''''+ SubString +''','; 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; end; // if (SubString <> 'X') or ((SubString = 'X') and.. // Keep track of how much work has been done to date. Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then nCount := 0; // reset progress bar. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) 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; // GetTimsVoiceMail. procedure GetTimsSwitchboard( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // Data handled is for rent of the Victrack fixed phones (PABX) network // This function reads the input file and saves it to the database. // const PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. SHIPTO_C: integer = 0; // ShipTo. SERVID_C: integer = 1; // ServiceID. EXTN_C: integer = 2; // Extension. CALLDATE_C: integer = 3; // Date call made. ENDTIME_C: integer = 4; // Time call ended. SITE_C: integer = 5; // SiteID. INLET_C: integer = 6; // Inlet number. DIRECTN_C: integer = 7; // Inlet direction. DIALLED_C: integer = 8; // DialledNumber. CALLTYPE_C: integer = 9; // Call type. CTYPDESC_C: integer = 10; // Call type description. TIME_C: integer = 11; // Call duration. COST_C: integer = 12; // Call charge field. var InputTextFile: TextFile; InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. Progress: TProgressForm; nCount: Integer; nFactor: Real; nBlock : Integer; nTotalCount : integer; sFromDate : string; sToDate : string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; LastServiceID : string; sCustomerID : string; sCallExGST : string; sCallIncGST : string; sGST : string; sGSTflag : string; // sServiceType : string; Deleted-default ok. nListValues : integer; qryDC : TQuery; // MessageForm : TMessagesForm; lDummyEntry : boolean; sLastCallCode : string; lFound : boolean; sLastTransactionTypeID : string; sLastSupplyCodeID : string; slAddFieldNames, slAddFieldValues : TStringList; begin lReturnCode := False; sBatch := IntToStr(nBatchNumber); sFromDate := ''''+FormattedDateString(dStartDate)+''','; sToDate := ''''+FormattedDateString(dEndDate)+''','; slAddFieldNames := TStringList.Create; // Calls to AddToTable() only. slAddFieldValues := TStringList.Create; // Calls to AddToTable() only. FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; if GetReferences then begin // 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 Telmax Switchboard File'; Progress.btnCancel.Visible := False; Progress.Show; Progress.ProcessWinMessages(MainForm); ConvertString := TStringList.Create; // Used to convert SDF format to strings. try // Read the first line of the input file. Readln( InputTextFile, ReadString ); // To be sure the file isn't empty too... if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then begin // Initiate the data variables. nFactor := 0.1000; // Count to 100 every 1,000 records. nBlock := 0; nTotalCount := 0; 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); // Decode the line read after converting the input line to SDF format. ConvertString.CommaText := ConvertCDE2SDF(ReadString); SubString := UpperCase(Trim(ConvertString.Strings[CALLTYPE_C])); lDummyEntry := (SubString = 'X'); // 'X'-Dummy entry: exists for every Extension for Web Billing. if not lDummyEntry then begin // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ServiceID,Var05,'; sListValues := sListValues + '''SW'+SubString+''',''SW'+SubString+''','; end; // Set defaults and calculate charge rates. if (SubString <> LastServiceID) then begin // Find references to define the applicable charges. LastServiceID := SubString; GetCustomerID('SW'+LastServiceID,sCustomerID); if (sCustomerID = sVRTID) then begin sCallExGST := sVSWEx; sCallIncGST := sVSWInc; sGST := sVSWGST; sGSTflag := sVSWGSTflag; // sServiceType := '''SW1'','; Deleted-default ok. end else begin sCallExGST := sSWEx; sCallIncGST := sSWInc; sGST := sSWGST; sGSTflag := sSWGSTflag; // sServiceType := '''SW'','; Deleted-default ok. end; // if (sCustomerID = sVRTID) then begin..else. end; // if new ServiceID. // Determine the applicable charges. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'); ValueStringList.Add(sCallExGST+sGST+sCallIncGST+sGSTFlag); // FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'+ // 'ServiceType,'); // ValueStringList.Add(sCallExGST+sGST+sCallIncGST+sGSTFlag+ // sServiceType); SubString := Trim(ConvertString.Strings[CALLTYPE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCodeID := Trim(qryTelmax['SCodeID']); end else begin sLastTransactionTypeID := sCallCodeID; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add('''CALLS'''); slAddFieldNames.Add('Description'); slAddFieldValues.Add('''Switchboard Call'''); slAddFieldNames.Add('TransactionTypeDesc'); slAddFieldValues.Add('''Telmax Call'''); slAddFieldNames.Add('TransactionTypeID'); slAddFieldValues.Add(sLastTransactionTypeID); slAddFieldNames.Add('SupplierID'); slAddFieldValues.Add(sSupplierID); slAddFieldNames.Add('Active'); slAddFieldValues.Add('1'); AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues); qryTelmax.Close; qryTelmax.Open; with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCodeID := Trim(qryTelmax['SCodeID']); end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCodeID := sSupplyCallCodeID; end; end; // if (not) lFound. end; // if (sLastCallCode <> SubString) then begin. if (Length(sLastTransactionTypeID) > 0) then begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; sListValues := sListValues + sLastTransactionTypeID+','+ sLastSupplyCodeID+','; end; end else begin sListFields := sListFields + 'TransactionTypeID,SupplierserviceCodeID,'; sListValues := sListValues + sCallCodeID+','+sLastSupplyCodeID+','; end; SubString := Trim(ConvertString.Strings[CALLDATE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[ENDTIME_C]); if (Length(SubString) = 6) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''','; end; SubString := Trim(ConvertString.Strings[TIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+SubString+''','; end; SubString := Trim(ConvertString.Strings[CTYPDESC_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,Peak,'; sListValues := sListValues +''''+ CleanString(SubString) +''',0,'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // Extn. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // SiteID. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var03,'; // InletNo. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var04,'; // Inlet direction. sListValues := sListValues +''''+ SubString +''','; 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; end; // if (SubString <> 'X') or ((SubString = 'X') and.. // Keep track of how much work has been done to date. Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then nCount := 0; // reset progress bar. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) 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; // GetTimsSwitchboard. procedure GetTimsSACC( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // Data handled is for rent of the Victrack fixed phones (PABX) network // This function reads the input file and saves it to the database. // const PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. CALLDATE_C: integer = 0; // Date call made. EXPANDCALLTYPE_C: integer = 1; // Expanded call type. INLET_C: integer = 2; // Inlet number. INLETTYPE_C: integer = 3; // Inlet type. OUTLET_C: integer = 4; // Outlet number. OUTLETTYPE_C: integer = 5; // Inlet type. DIALLED_C: integer = 6; // DialledNumber. ENDTIME_C: integer = 7; // Time call ended. COST_C: integer = 8; // Call charge field. SITE_C: integer = 9; // SiteID. var InputTextFile: TextFile; InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. Progress: TProgressForm; nCount: Integer; nFactor: Real; nBlock : Integer; nTotalCount : integer; sFromDate : string; sToDate : string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; LastServiceID : string; sCustomerID : string; sCallExGST : string; sCallIncGST : string; sGST : string; sGSTflag : string; sServiceType : string; curExGST, curGST : currency; nListValues : integer; qryDC : TQuery; // MessageForm : TMessagesForm; begin lReturnCode := False; sBatch := IntToStr(nBatchNumber); sFromDate := ''''+FormattedDateString(dStartDate)+''','; sToDate := ''''+FormattedDateString(dEndDate)+''','; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; if GetReferences then begin // 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 Telmax SACC File'; Progress.btnCancel.Visible := False; Progress.Show; Progress.ProcessWinMessages(MainForm); 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 // Initiate the data variables. nFactor := 0.1000; // Count to 100 every 1,000 records. nBlock := 0; nTotalCount := 0; 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); // Decode the line read after converting the input line to SDF format. ConvertString.CommaText := ConvertCDE2SDF(ReadString); // Reset the strings for defining the data. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; SubString := Trim(ConvertString.Strings[DIALLED_C]); if (Length(SubString) > 0) then begin // Remove any inserted leading '0' used to gain an outside line. sListFields := sListFields + 'ServiceID,Var05,'; if (Copy(SubString,1,4)='0011') or (Copy(SubString,1,1)<>'0')or (Copy(SubString,1,2)='04') then SubString := AccountFormat(SubString) else SubString := AccountFormat(Copy(SubString,2,20)); sListValues := sListValues + ''''+SubString+''','''+SubString+''','; end; // Find references to define the applicable charges. if (LastServiceID <> SubString) then begin LastServiceID := SubString; GetCustomerID(LastServiceID,sCustomerID); end; sCallExGST := Trim(ConvertString.Strings[COST_C]); if (sCustomerID = sVRTID) then begin sCallIncGST := sCallExGST+','; sGST := '0,'; sGSTflag := '''N'','; sServiceType := '''SA1'','; end else begin curExGST := StrToCurr(sCallExGST); curGST := fGSTRate * curExGST; sGST := CurrToStr(curGST)+','; sCallIncGST := CurrToStr(curExGST + curGST)+','; sGSTflag := '''Y'','; sServiceType := '''SA'','; end; // if (sCustomerID = sVRTID) then begin..else. sCallExGST := sCallExGST+','; // Determine the applicable charges. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'+ 'ServiceType,'); ValueStringList.Add(sCallExGST+sGST+sCallIncGST+sGSTFlag+ sServiceType); sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+sSupplyCallCodeID+','; SubString := Trim(ConvertString.Strings[CALLDATE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[ENDTIME_C]); if (Length(SubString) = 6) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; // SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+LastServiceID+''','; end; sListFields := sListFields + 'RateDescription,Peak,'; sListValues := sListValues + '''Call Diversion'',0,'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // SiteID. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var03,'; // InletNo. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLETTYPE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var06,'; // Inlet type. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[OUTLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var07,'; // Outlet number. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[OUTLETTYPE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var08,'; // Outlet type. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[EXPANDCALLTYPE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var09,'; // Expanded call type. sListValues := sListValues +''''+ SubString +''','; 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(nTotalCount); Inc(nBlock); if (nBlock=10) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then nCount := 0; // reset progress bar. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); Progress.ProcessWinMessages(MainForm); 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; // GetTimsSACC. { procedure TelmaxCallInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); // This function prompts the user for the name of the Telmax supplied // text file (comma delimited) and then calls the function which reads the // file into tDataCollection table for further processing. var OpenDlg : TOpenDialog; lContinue : Boolean; sFile : String; slCust : TStringList; begin slCust := TStringList.Create; lContinue := True; // First, we must establish which file is the one to be processed. OpenDlg := TOpenDialog.Create(MainForm); OpenDlg.Filter := 'Telmax Call Billing file (c*.txt)|C*.TXT|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 Telmax 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 DeleteFromDC( nBatch, slCust, lContinue); GetTimsCalls(nBatch,dStartDate,dEndDate,BatchDescription,sFile,lContinue); if lContinue then begin MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing VicTrack Call charges'; MessageForm.Msg.Caption := 'Updating tBatch rates ...'; MessageForm.Show; Progress.ProcessWinMessages(MainForm); // Now update the values stored in the tBatch table. slCust.Clear; slCust.Add('UPDATE tBatch'); slCust.Add('SET GSTAmount = BatchRate.TotalGST,'); slCust.Add(' TotalPayable = BatchRate.TotalIncGST'); slCust.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); slCust.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); slCust.Add('FROM tDataCollection'); slCust.Add('WHERE tDataCollection.BatchID = '+ IntToStr(nBatch) + ') AS BatchRate'); slCust.Add('WHERE tBatch.ID = '+ IntToStr(nBatch)); qryDC.Close; qryDC.SQL := slCust; qryDC.ExecSQL; 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. slCust.Free; end; // procedure TelmaxCallInvoice. procedure TelmaxRentInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); // This function prompts the user for the name of the Telmax supplied // text file (comma delimited) and then calls the function which reads the // file into tDataCollection table for further processing. var OpenDlg : TOpenDialog; lContinue : Boolean; sFile : String; begin // Initiate global variables. qCustID := TQuery.Create(MainForm); // Used in GetCustomerID(). qCustID.DatabaseName := 'dbPPdata'; slCust := TStringList.Create; // Used in GetCustomerID(). lContinue := True; // First, we must establish which file is the one to be processed. OpenDlg := TOpenDialog.Create(MainForm); OpenDlg.Filter := 'Telmax Rent Billing file (r*.txt)|R*.TXT|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 Telmax 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 DeleteFromDC( nBatch, slCust, lContinue); slCust.Clear; slCust.Add('SELECT tGroup.CustomerID'); slCust.Add('FROM tServiceID'); slCust.Add('LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID'); slCust.Add('LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID'); slCust.Add('LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID'); slCust.Add('WHERE tServiceID.ServiceID LIKE '''''); GetTimsRent(nBatch,dStartDate,dEndDate,BatchDescription,sFile,lContinue); if lContinue then begin MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing Telmax Rental charges'; MessageForm.Msg.Caption := 'Updating tBatch rates ...'; MessageForm.Show; Progress.ProcessWinMessages(MainForm); // Now update the values stored in the tBatch table. slCust.Clear; slCust.Add('UPDATE tBatch'); slCust.Add('SET GSTAmount = BatchRate.TotalGST,'); slCust.Add(' TotalPayable = BatchRate.TotalIncGST'); slCust.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); slCust.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); slCust.Add('FROM tDataCollection'); slCust.Add('WHERE tDataCollection.BatchID = '+ IntToStr(nBatch) + ') AS BatchRate'); slCust.Add('WHERE tBatch.ID = '+ IntToStr(nBatch)); qryDC.Close; qryDC.SQL := slCust; qryDC.ExecSQL; 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. // House keeping. qCustID.Close; // Used in GetCustomerID(). qCustID.Free; slCust.Free; // Used in GetCustomerID(). end; // procedure TelmaxRentInvoice. procedure TelmaxVoiceMailInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); // This function prompts the user for the name of the Telmax supplied // text file (comma delimited) and then calls the function which reads the // file into tDataCollection table for further processing. var OpenDlg : TOpenDialog; lContinue : Boolean; sFile : String; begin // Initiate global variables. qCustID := TQuery.Create(MainForm); // Used in GetCustomerID(). qCustID.DatabaseName := 'dbPPdata'; slCust := TStringList.Create; // Used in GetCustomerID(). lContinue := True; // First, we must establish which file is the one to be processed. OpenDlg := TOpenDialog.Create(MainForm); OpenDlg.Filter := 'Telmax Voicemail file (v*.txt)|V*.TXT|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 Telmax 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 DeleteFromDC( nBatch, slCust, lContinue); slCust.Clear; slCust.Add('SELECT tGroup.CustomerID'); slCust.Add('FROM tServiceID'); slCust.Add('LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID'); slCust.Add('LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID'); slCust.Add('LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID'); slCust.Add('WHERE tServiceID.ServiceID LIKE '''''); GetTimsVoiceMail(nBatch,dStartDate,dEndDate,BatchDescription,sFile,lContinue); if lContinue then begin MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing Voicemail charges'; MessageForm.Msg.Caption := 'Updating tBatch rates ...'; MessageForm.Show; Progress.ProcessWinMessages(MainForm); // Now update the values stored in the tBatch table. slCust.Clear; slCust.Add('UPDATE tBatch'); slCust.Add('SET GSTAmount = BatchRate.TotalGST,'); slCust.Add(' TotalPayable = BatchRate.TotalIncGST'); slCust.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); slCust.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); slCust.Add('FROM tDataCollection'); slCust.Add('WHERE tDataCollection.BatchID = '+ IntToStr(nBatch) + ') AS BatchRate'); slCust.Add('WHERE tBatch.ID = '+ IntToStr(nBatch)); qryDC.Close; qryDC.SQL := slCust; qryDC.ExecSQL; 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; // procedure TelmaxVoiceMailInvoice. procedure TelmaxSwitchInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); // This function prompts the user for the name of the Telmax supplied // text file (comma delimited) and then calls the function which reads the // file into tDataCollection table for further processing. var OpenDlg : TOpenDialog; lContinue : Boolean; sFile : String; begin // Initiate global variables. qCustID := TQuery.Create(MainForm); // Used in GetCustomerID(). qCustID.DatabaseName := 'dbPPdata'; slCust := TStringList.Create; // Used in GetCustomerID(). lContinue := True; // First, we must establish which file is the one to be processed. OpenDlg := TOpenDialog.Create(MainForm); OpenDlg.Filter := 'Telmax Switchboard Billing file (s*.txt)|S*.TXT|'+ '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 Telmax 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 DeleteFromDC( nBatch, slCust, lContinue); slCust.Clear; slCust.Add('SELECT tGroup.CustomerID'); slCust.Add('FROM tServiceID'); slCust.Add('LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID'); slCust.Add('LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID'); slCust.Add('LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID'); slCust.Add('WHERE tServiceID.ServiceID LIKE '''''); GetTimsSwitchboard(nBatch,dStartDate,dEndDate,BatchDescription,sFile,lContinue); if lContinue then begin MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing Switchboard Call charges'; MessageForm.Msg.Caption := 'Updating tBatch rates ...'; MessageForm.Show; Progress.ProcessWinMessages(MainForm); // Now update the values stored in the tBatch table. slCust.Clear; slCust.Add('UPDATE tBatch'); slCust.Add('SET GSTAmount = BatchRate.TotalGST,'); slCust.Add(' TotalPayable = BatchRate.TotalIncGST'); slCust.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); slCust.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); slCust.Add('FROM tDataCollection'); slCust.Add('WHERE tDataCollection.BatchID = '+ IntToStr(nBatch) + ') AS BatchRate'); slCust.Add('WHERE tBatch.ID = '+ IntToStr(nBatch)); qryDC.Close; qryDC.SQL := slCust; qryDC.ExecSQL; 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; // procedure TelmaxSwitchInvoice. } procedure TelmaxSACCInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); // This function prompts the user for the name of the Telmax supplied // text file (comma delimited) and then calls the function which reads the // file into tDataCollection table for further processing. var OpenDlg : TOpenDialog; lContinue : Boolean; sFile : String; MessageForm : TMessagesForm; begin // Initiate global variables. qCustID := TQuery.Create(MainForm); // Used in GetCustomerID(). qCustID.DatabaseName := 'dbPPdata'; slCust := TStringList.Create; // Used in GetCustomerID(). lContinue := True; // First, we must establish which file is the one to be processed. OpenDlg := TOpenDialog.Create(MainForm); OpenDlg.Filter := 'Telmax SACC Billing file '+ '(trancsv*.*)|TRANCSV*.*|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 Telmax 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 DeleteFromDC( nBatch, slCust, lContinue); slCust.Clear; slCust.Add('SELECT tGroup.CustomerID'); slCust.Add('FROM tServiceID'); slCust.Add('LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID'); slCust.Add('LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID'); slCust.Add('LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID'); slCust.Add('WHERE tServiceID.ServiceID LIKE '''''); GetTimsSACC(nBatch,dStartDate,dEndDate,BatchDescription,sFile,lContinue); if lContinue then begin MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing SACC charges'; MessageForm.Msg.Caption := 'Updating tBatch rates ...'; MessageForm.Show; MainForm.ProcessWinMessages; // Now update the values stored in the tBatch table. slCust.Clear; slCust.Add('UPDATE tBatch'); slCust.Add('SET GSTAmount = BatchRate.TotalGST,'); slCust.Add(' TotalPayable = BatchRate.TotalIncGST'); slCust.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); slCust.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); slCust.Add('FROM tDataCollection'); slCust.Add('WHERE tDataCollection.BatchID = '+ IntToStr(nBatch) + ') AS BatchRate'); slCust.Add('WHERE tBatch.ID = '+ IntToStr(nBatch)); qCustID.Close; qCustID.SQL := slCust; qCustID.ExecSQL; MessageForm.Close; 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; // procedure TelmaxSACCInvoice. procedure TelmaxInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); // This function prompts the user for the name of the Telmax supplied // text file (comma delimited) and then calls the function which reads the // file into tDataCollection table for further processing. var OpenDlg : TOpenDialog; lContinue : Boolean; sFile : string; sRentFile, sCallFile, sVoiceMailFile, sSwitchboardFile : string; slFileList : TStringList; sPath : string; sMask : string; MessageForm : TMessagesForm; begin // Initiate global variables. qCustID := TQuery.Create(MainForm); // Used in GetCustomerID(). qCustID.DatabaseName := 'dbPPdata'; slCust := TStringList.Create; // Used in GetCustomerID(). lContinue := True; // First, we must establish which file is the one to be processed. OpenDlg := TOpenDialog.Create(MainForm); OpenDlg.Options := [ofPathMustExist,ofAllowMultiSelect]; OpenDlg.Filter := 'Telmax Billing files (*.txt)|*.TXT|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 Telmax Input Directory'; // 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 // MainForm.StartTime; // // slFileList := TStringList.Create; sPath := ExtractFilePath(sFile); sMask := 'c*.txt'; // Calls GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sCallFile := slFileList[0] else lContinue := False; if lContinue then begin sMask := 'r*.txt'; // Rent slFileList.Clear; GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sRentFile := slFileList[0] else lContinue := False; end; if lContinue then begin sMask := 'v*.txt'; // Voicemail slFileList.Clear; GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sVoiceMailFile := slFileList[0] else lContinue := False; end; if lContinue then begin sMask := 's*.txt'; // Switchboard slFileList.Clear; GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sSwitchboardFile := slFileList[0] else lContinue := False; end; slFileList.Free; if lContinue and FileExists(sRentFile) and FileExists(sCallFile) and FileExists(sVoiceMailFile) and FileExists(sSwitchboardFile) then begin DeleteFromDC( nBatch, slCust, lContinue); slCust.Clear; slCust.Add('SELECT tGroup.CustomerID'); slCust.Add('FROM tServiceID'); slCust.Add('LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID'); slCust.Add('LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID'); slCust.Add('LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID'); slCust.Add('WHERE tServiceID.ServiceID LIKE '''''); // Process the Calls last - it is really BIG !!! GetTimsRent(nBatch,dStartDate,dEndDate, BatchDescription,sRentFile,lContinue); if lContinue then GetTimsSwitchboard(nBatch,dStartDate,dEndDate, BatchDescription,sSwitchboardFile,lContinue); if lContinue then GetTimsVoiceMail(nBatch,dStartDate,dEndDate, BatchDescription,sVoiceMailFile,lContinue); if lContinue then GetTimsCalls(nBatch,dStartDate,dEndDate, BatchDescription,sCallFile,lContinue); if lContinue then begin MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing VicTrack PABX charges'; MessageForm.Msg.Caption := 'Updating tBatch rates ...'; MessageForm.Show; MainForm.ProcessWinMessages; // Now update the values stored in the tBatch table. slCust.Clear; slCust.Add('UPDATE tBatch'); slCust.Add('SET GSTAmount = BatchRate.TotalGST,'); slCust.Add(' TotalPayable = BatchRate.TotalIncGST'); slCust.Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST,'); slCust.Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); slCust.Add('FROM tDataCollection'); slCust.Add('WHERE tDataCollection.BatchID = '+ IntToStr(nBatch) + ') AS BatchRate'); slCust.Add('WHERE tBatch.ID = '+ IntToStr(nBatch)); qCustID.Close; qCustID.SQL := slCust; qCustID.ExecSQL; MessageForm.Close; MessageForm.Free; UpdateBatchStatus(nBatch, MainForm.BATCH_STATUS_IMPORT); // ********************************************************************* // ********************************************************************* // Test for critical errors that prevent progress to the next stage. lContinue := False; CriticalErrorTest( nBatch, lContinue ); // MainForm.EndTime; // // 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 else begin slCust.Clear; lContinue := True; DeleteFromDC( nBatch, slCust, lContinue); end; // if lContinue then begin. end else begin MessageDlg('One or more Input files missing.', mtWarning, [mbAbort], 0 ); end; // if lContinue and FileExists(sRentFile) and..then begin..else. end; // if (Length(Trim(sFile)) > 0) then begin. // House keeping. qCustID.Close; // Used in GetCustomerID(). qCustID.Free; slCust.Free; // Used in GetCustomerID(). end; // procedure TelmaxInvoice. end.