ProgramID: 2103|Telmax21 Unified Comms // In Main else if (nProgramID = PAUTOMATIC_TELMAX_UNICOMMS) then begin with qry do begin // get folder to start in for getting filename //SQL.DatabaseName := 'dbPPdata'; 'SELECT tParameter.ParmText 'FROM tParameter 'WHERE tParameter.Parm LIKE ''InDataRootDirectory'' Open; if NOT FOUND Eof then sImportFromDir := '' else sImportFromDir := FieldByName('ParmText').AsString; Close; end; // qry. UniCommsInvoice(nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName, sImportFromDir ); end; // Telmax. // In TelmaxUniComms.pas // Global References (set GetReferences, GetCustomerID) const TEC_DEFAULT: string = '57'; // Default TEC value - used in Rent only. SFC_DEFAULT: string = '79'; // Default SFC value - used in Rent only. VRT_DEFAULT: string = '2846'; // Use for Vacant phones & invalid Service Types. UC_SYSTEMID: string = '760'; // Unicomms SystemID - var sSupplierID: string; // Set in GetReferences(). sRentTransactionGroupID : string; // Set in GetReferences(). sSystemID : string; // Set in GetReferences(). sRentTransactionCodeID : string; // Set in GetReferences(). sServiceTransactionCodeID : string; // Set in GetReferences(). sRentTransactionDesc : string; // Set in GetReferences(). sServiceTransactionDesc : string; // Set in GetReferences(). sCallCodeID : string; // Set in GetReferences(). sSupplyCallCodeID : string; // Set in GetReferences(). qCustID : TQuery; // Used in GetCustomerID(). slCust : TStringList; // Used in GetCustomerID(). sVRTID : string; fGSTRate : Real; // GST Rate for Calcs-read from MainForm. sGSTRate : string; // fGSTRate as String-for SQL statements. qryServCode : TQuery; // Used in GetSupplierCodeID(). sSupplierDefaultTECcodeID : string; // Default, set in GetCustomerID(). sSupplierDefaultSFCcodeID : string; // Default, set in GetCustomerID(). sSupplierDefaultTECcodeDesc: string;// Default, set in GetCustomerID(). sSupplierDefaultSFCcodeDesc: string;// Default, set in GetCustomerID(). curValueDefaultTEC : currency; // Default, set in GetReferences(). curValueDefaultSFC : currency; // Default, set in GetReferences(). sSWEx, sSWGST, sSWInc, sSWGSTflag : string; // Switchboard. sVSWEx, sVSWGST, sVSWInc, sVSWGSTflag : string; // Switchboard (VRT procedure UniCommsInvoice(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. qCustID.DatabaseName := 'dbPPdata'; // First, we must establish which file is the one to be processed. // this would be the 3 irent csll and switchboads files in the UC folder below // Telmax21 HOME ./links/billing_indata/Aug_2021/TELMAX21/PP in Folders UC and NotUC (Both where they are comined is not used) total 20 drwxrwxrwx 5 bilmax21 catcom 4096 Sep 6 00:15 . drwxrwxrwx 3 bilmax21 catcom 4096 Sep 6 00:19 .. drwxrwxrwx 2 bilmax21 catcom 4096 Sep 6 00:19 BOTH drwxrwxrwx 2 bilmax21 catcom 4096 Sep 6 00:19 UC drwxrwxrwx 2 bilmax21 catcom 4096 Sep 6 00:19 notUC sh-3.2# ls -altr * BOTH: total 92984 drwxrwxrwx 5 bilmax21 catcom 4096 Sep 6 00:15 .. -rw-rw-r-- 1 bilmax21 catcom 16411 Sep 6 00:19 v9_082021.txt -rw-rw-r-- 1 bilmax21 catcom 234611 Sep 6 00:19 s9_082021.txt -rw-rw-r-- 1 bilmax21 catcom 738525 Sep 6 00:19 r9_082021.txt -rw-rw-r-- 1 bilmax21 catcom 94098192 Sep 6 00:19 c9_082021.txt drwxrwxrwx 2 bilmax21 catcom 4096 Sep 6 00:19 . >> UC: >> total 71408 >> drwxrwxrwx 5 bilmax21 catcom 4096 Sep 6 00:15 .. >> -rw-rw-r-- 1 bilmax21 catcom 42981 Sep 6 00:19 s9ccm_082021.txt >> -rw-rw-r-- 1 bilmax21 catcom 375997 Sep 6 00:19 r9ccm_082021.txt >> -rw-rw-r-- 1 bilmax21 catcom 72609403 Sep 6 00:19 c9ccm_082021.txt >> drwxrwxrwx 2 bilmax21 catcom 4096 Sep 6 00:19 . notUC: total 21596 drwxrwxrwx 5 bilmax21 catcom 4096 Sep 6 00:15 .. -rw-rw-r-- 1 bilmax21 catcom 16411 Sep 6 00:19 v9notccm_082021.txt -rw-rw-r-- 1 bilmax21 catcom 191630 Sep 6 00:19 s9notccm_082021.txt -rw-rw-r-- 1 bilmax21 catcom 362528 Sep 6 00:19 r9notccm_082021.txt -rw-rw-r-- 1 bilmax21 catcom 21488789 Sep 6 00:19 c9notccm_082021.txt drwxrwxrwx 2 bilmax21 catcom 4096 Sep 6 00:19 . // OpenDlg.Filter := 'Telmax Unified Comms Billing files (*.txt)|*.TXT|All files (*.*)|*.*'; // 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 Input File Directory'; // get UC calls , rent , switch // Read and save the user's choices before continuing. lContinue := OpenDlg.Execute; if lContinue and (Length(Trim(sFile)) > 0) then begin sPath := ExtractFilePath(sFile); sMask := 'c*.txt'; // Calls GetFileList(sPath, sMask, slFileList); if lContinue then begin sMask := 'r*.txt'; // Rent GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sRentFile := slFileList[0] end; if lContinue then begin sMask := 's*.txt'; // Switchboard GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sSwitchboardFile := slFileList[0] end; // if all files exist if lContinue and FileExists(sRentFile) and FileExists(sCallFile) and FileExists(sSwitchboardFile) then begin if not GetReferences(nBatch) then begin MessageDlg('An error occurred reading data references - Cancelled', mtWarning, [mbAbort], 0 ); end else begin DeleteFromDC( nBatch, slCust, lContinue); // prepare slCust.SQL 'SELECT tServiceID.ServiceTypeID, '+ 'tServiceType.Description AS ServiceTypeDesc, '+ 'tGroup.CustomerID // 0. 'FROM tServiceID // 1. 'LEFT JOIN tServiceType ON '+ 'tServiceID.ServiceTypeID = tServiceType.ID // 2. 'LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID 'LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID 'LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID // 5. 'WHERE tServiceID.ServiceID LIKE '''' // 6. ' AND tServiceType.SystemID = '+ UC_SYSTEMID); // 7. ' AND tServiceID.Active = 1 // 8. // Process the Calls last - it is really BIG !!! GetUniCRent(nBatch,dStartDate,dEndDate, BatchDescription,sRentFile,lContinue); if lContinue then GetUniCSwitchboard(nBatch,dStartDate,dEndDate, BatchDescription,sSwitchboardFile,lContinue); if lContinue then GetUniCCalls(nBatch,dStartDate,dEndDate, BatchDescription,sCallFile,lContinue); if lContinue then begin MessageForm.Caption := 'Importing VicTrack PABX charges'; MessageForm.Msg.Caption := 'Updating tBatch rates ...'; MainForm.ProcessWinMessages; // Now update the values stored in the tBatch table. 'UPDATE tBatch 'SET GSTAmount = BatchRate.TotalGST, ' TotalPayable = BatchRate.TotalIncGST 'FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ 'AS TotalIncGST, ' Sum(tDataCollection.GSTAmount) AS TotalGST 'FROM tDataCollection 'WHERE tDataCollection.BatchID = '+ IntToStr(nBatch) + ') AS BatchRate 'WHERE tBatch.ID = '+ IntToStr(nBatch)); qCustID.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 else begin slCust.Clear; // When empty, all entries deleted from tDataCollection. lContinue := True; DeleteFromDC( nBatch, slCust, lContinue); end; // if lContinue then begin. end; // GetReferences. end else begin MessageDlg('One or more Input files missing or'+#13+#10+ ' references not available.', 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 UniCommsInvoice. procedure GetFileList(const sPath, sMask: string; var slFileList: TStringList); var FileInfo: TSearchRec; sFileSpec: string; begin sFileSpec := sPath + sMask; if (FindFirst(sFileSpec, faAnyFile, FileInfo) = 0) then begin repeat slfileList.Add(FileInfo.Name); until FindNext(FileInfo) <> 0; end; FindClose(FileInfo); end; // GetFileList. // sets global References function GetReferences(const nBatchNumber : LongInt) : boolean; var begin // initialise the Static variables defined as Private for this form. fGSTRate := MAINFORM.GST_RATE; Str(fGSTRate:6:4, sGSTRate); lSuccessful := True; sErrorTxt := ''; qryTelmax.DatabaseName := 'dbPPdata'; with qryTelmax do begin 'SELECT id 'FROM tTransactionGroup 'WHERE Description LIKE ''RENT'' Open; // set sRentTransactionGroupID := IntToStr(FieldByName('ID').AsInteger); 'SELECT tSystem.id 'FROM tSystem 'RIGHT JOIN tBatch ON tSystem.BatchTypeID = tBatch.BatchTypeID 'WHERE tBatch.ID = '+IntToStr(nBatchNumber)); Open; // set sSystemID := IntToStr(FieldByName('ID').AsInteger); 'SELECT id 'FROM tSupplier 'WHERE SupplierName LIKE ''VICTRACK%'' Open; end; // qryTelmax. if qryTelmax.Eof then begin lSuccessful := False; sErrorTxt := sErrorTxt + 'VICTRACK not found in tSupplier'+#13#10; end else begin // set VICTRACK SupplierID with qryTelmax do begin // get transactiontype for Equipment 'SELECT CAST(ID AS VARCHAR(20)) AS TransactionType, Description 'FROM tTransactionType 'WHERE SystemID = '+sSystemID); 'AND TransactionGroupID = '+sRentTransactionGroupID); 'AND Description LIKE ''Equipment%'' end; // qryTelmax. if qryTelmax.Eof then begin lSuccessful := False; sErrorTxt := sErrorTxt + 'Equipment not found in tTransactionType'+#13#10; end else begin sRentTransactionCodeID := Trim(qryTelmax.FieldByName('TransactionType').AsString); sRentTransactionDesc := ''''+ Trim(qryTelmax.FieldByName('Description').AsString)+''''; end; // get transactiontype for Subscription qryTelmax.SQL.Strings[4] := 'AND Description LIKE ''Subscription%'''; qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; sErrorTxt := sErrorTxt+'Subscription not found in tTransactionType'+#13#10 ; end else begin sServiceTransactionCodeID := Trim(qryTelmax.FieldByName('TransactionType').AsString); sServiceTransactionDesc := ''''+ Trim(qryTelmax.FieldByName('Description').AsString)+''''; end; // Ensure the tSupplierServiceCode table includes all system entry // codes found in tRate. with qryTelmax do begin 'INSERT INTO tSupplierServiceCode ' (Description, Code, AmountCategory, SupplierID, Active, '+ 'TransactionTypeID, TransactionTypeDesc) 'SELECT RateDesc.Description, RateDesc.Code, ' RateDesc.AmountCategory, RateDesc.SupplierID, ' RateDesc.Active, RateDesc.TransactionTypeID, ' RateDesc.TransactionTypeDesc 'FROM ( ' SELECT RateCode.Description, RateCode.Code, ' ''RENT'' AS AmountCategory, ' '+sSupplierID +' AS SupplierID, ' 1 AS Active, ' TransactionTypeID = ' CASE ' WHEN RateCode.RentType = ''TEC'' THEN '+sRentTransactionCodeID ' ELSE '+sServiceTransactionCodeID); ' END, ' TransactionTypeDesc = ' CASE ' WHEN RateCode.RentType = ''TEC'' THEN '+sRentTransactionDesc ' ELSE '+sServiceTransactionDesc ' END ' FROM ( ' SELECT DISTINCT ' Code = ' CASE ' WHEN ((tRate.Length > 9) AND '+ '(SUBSTRING(tRate.Description,1,3) LIKE ''TEC'')) '+ 'THEN (''T''+ CAST( tRate.Length AS VARCHAR(2))) ' WHEN ((tRate.Length > 9) AND '+ '(SUBSTRING(tRate.Description,1,3) LIKE ''SFC'')) '+ 'THEN (''S''+ CAST( tRate.Length AS VARCHAR(2))) ' WHEN ((tRate.Length < 10) AND '+ '(SUBSTRING(tRate.Description,1,3) LIKE ''TEC'')) '+ 'THEN (''T0''+ CAST( tRate.Length AS VARCHAR(1))) ' WHEN ((tRate.Length < 10) AND '+ '(SUBSTRING(tRate.Description,1,3) LIKE ''SFC'')) '+ 'THEN (''S0''+ CAST( tRate.Length AS VARCHAR(1))) ' ELSE (''*''+ CAST( tRate.Length AS VARCHAR(2))) ' END, ' tRate.Length AS CodeValue, ' tRate.Description, ' SUBSTRING(tRate.Description, 1, 3) AS RentType ' FROM tRate ' LEFT JOIN tServiceType ON '+ '((tRate.ServiceTypeID = tServiceType.ID) AND '+ '(tServiceType.Active = 1)) ' WHERE tServiceType.Description LIKE ''UCP %'' AND ' tRate.Active = 1 ' ) AS RateCode ' LEFT JOIN tSupplierServiceCode ON '+ '((tSupplierServiceCode.SupplierID = '+sSupplierID +') AND ' (tSupplierServiceCode.Code = RateCode.Code)) ' WHERE tSupplierServiceCode.Code IS NULL ' ) AS RateDesc ExecSQL; end; // Telmax. // Prepare the SQL in GetSupplierCodeIS() the to get SupplierServiceCodeID - // Line 4 updated in the Rent prog loop for each TEC & SFC. qryServCode.DatabaseName := 'dbPPdata'; with qryServCode do begin 'SELECT tSupplierServiceCode.ID AS SupplierCodeID // 0. 'FROM tSupplierServiceCode // 1. 'WHERE tSupplierServiceCode.SupplierID = '+sSupplierID+' AND ' tSupplierServiceCode.Active = 1 AND // 3. ' tSupplierServiceCode.Code LIKE // 4. end; // qryServCode. // set CallCodeID, and SupplyCallCodeID qryTelmax.SQL.Clear; 'SELECT Code, CAST(ID AS VARCHAR(20)) AS "SCodeID", '+ 'CAST(TransactionTypeID AS VARCHAR(20)) AS TransactionType 'FROM tSupplierServiceCode 'WHERE SupplierID = '+sSupplierID); 'AND Code LIKE ''TELCALL%'' qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; sErrorTxt := sErrorTxt+'TELCALL not found in tSupplierServiceCode'+#13#10; end else begin sCallCodeID := Trim(qryTelmax['TransactionType']); sSupplyCallCodeID := Trim(qryTelmax['SCodeID']); end; qryTemp.DatabaseName := 'dbPPdata'; if lSuccessful then begin // remove last line of above qryTelmax SQL and execute qryTelmax.SQL.Delete(qryTelmax.SQL.Count-1); qryTelmax.Open; // Set defaults. sSWEx := '0,'; // Switchboard. sSWGST := '0,'; sSWInc := '0,'; sSWGSTflag := '''Y'','; sVSWEx := '0,'; // Switchboard (Victrack). sVSWGST := '0,'; sVSWInc := '0,'; sVSWGSTflag := '''N'','; curValueDefaultTEC := 0; curValueDefaultSFC := 0; sSupplierDefaultTECcodeID := GetSupplierCodeID('T'+TEC_DEFAULT); sSupplierDefaultSFCcodeID := GetSupplierCodeID('S'+SFC_DEFAULT); // set VRT CustomerID 'SELECT id 'FROM tCustomer 'WHERE ShipTo LIKE ''VRT'' if NOT FOUND.Eof then sVRTID := '0' else sVRTID := IntToStr(qryTemp.FieldByName('id').AsInteger); // set Uniccomms defaults - TEC rate. 'SELECT tRate.Cost, tRate.Description // 0. 'FROM tRate // 1. 'LEFT JOIN tServiceType ON '+ '((tRate.ServiceTypeID = tServiceType.ID) // 2. ' AND (tServiceType.Active = 1)) // 3. 'WHERE tServiceType.ID = '+ VRT_DEFAULT+' AND // 4. ' tRate.Length = '+ TEC_DEFAULT+' AND // 5. ' tRate.Active = 1 // 6. // commented out { if NOTFOUND then begin lSuccessful := False; sErrorTxt := sErrorTxt+'VRT TEC-Default not found in tRate'+#13#10; end else begin } // Continue setting the next default. curValueDefaultTEC := curFormat(qryTemp.FieldByName('cost').AsCurrency / 12); sSupplierDefaultTECcodeDesc := qryTemp.FieldByName('Description').AsString; // Uniccomms defaults - SFC rate. qryTemp.SQL.Strings[5] := ' tRate.Length = '+ SFC_DEFAULT+' AND'; qryTemp.Open; // Commented out { if NOTFOUND then begin lSuccessful := False; sErrorTxt := sErrorTxt+'VRT SFC-Default not found in tRate'+#13#10; end else begin } curValueDefaultSFC := curFormat(qryTemp.FieldByName('cost').AsCurrency / 12); sSupplierDefaultSFCcodeDesc := qryTemp.FieldByName('Description').AsString; { end; // SFC. { end; // TEC. } end; // lSuccessful. if lSuccessful then begin // Set base SQL - altered for each ServiceTypeID (for Switchboard only). qryRef.DatabaseName := 'dbPPdata'; 'SELECT id, code, Description 'FROM tServiceType 'WHERE Code LIKE ''TECSW'' // Switchboard. qryRef.Open; if FOUND then begin qryTemp.DatabaseName := 'dbPPdata'; 'SELECT Cost 'FROM tRate 'WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); if NOTFOUND then curValue := 0 else curValue := curFormat(qryTemp.FieldByName('cost').AsCurrency); curValueGST := curValue * fGSTRate; sSWEx := CurrToStr(curValue)+','; sSWGST := CurrToStr(curValueGST)+','; sSWInc := CurrToStr(curValue + curValueGST)+','; sVSWEx := sSWEx; sVSWInc := sSWEx; end; // not qryRef.Eof. end else begin // Errors encountered - show them to the User. MessageDlg(sErrorTxt, mtWarning, [mbOk], 0); end; qryTemp.Free; end; // if qryTelmax.Eof then begin..else. Result := lSuccessful; end; // procedure GetReferences(). procedure GetCustomerID(const sLastServiceID:string; var sServiceTypeID, sServiceTypeDesc, sCustomerID:string); begin // Update slCust - defined in UniCommsInvoice(). slCust[6] := 'WHERE tServiceID.ServiceID LIKE '''+ sLastServiceID +''''; qCustID.SQL := slCust; if NOTFOUND or VarIsNull(qCustID['ServiceTypeID']) then begin sServiceTypeID := ''; sServiceTypeDesc := ''; end else begin sServiceTypeID := IntToStr(qCustID.FieldByName('ServiceTypeID').AsInteger); sServiceTypeDesc := qCustID.FieldByName('ServiceTypeDesc').AsString; end; if NOTFOUND or VarIsNull(qCustID['CustomerID']) then sCustomerID := '' else sCustomerID := IntToStr(qCustID.FieldByName('CustomerID').AsInteger); qCustID.Close; end; // GetCustomerID(). // I'll leave the processing of the CSV data records intact // so you have the full logic procedure GetUniCCalls( 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 GetUniCCalls(). procedure GetUniCRent( 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; sUTECString : string; LastServiceID : string; sServiceTypeID : string; sServiceTypeDesc : string; sCustomerID : string; sSupplierCodeID : string; sSupplierCodeDesc : string; sGSTflag : string; lGSTflag : boolean; nListValues : integer; qryDC : TQuery; qryTEC : TQuery; curValue : currency; curValueGST : currency; lServiceTypeOkay : boolean; begin // Initiate all variables. lReturnCode := False; lServiceTypeOkay := True; lGSTflag := TRUE; sGSTflag := '''Y'','; 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'; qryTEC := TQuery.Create(MainForm); qryTEC.DatabaseName := 'dbPPdata'; // Prepare the SQL to get rent & service costs - // Line 3 updated in prog loop when ServiceID (ServiceType) updated, // Line 4 updated in prog loop for each TEC & SFC. with qryTEC do begin SQL.Add('SELECT tRate.Length, tRate.Cost, tRate.Description AS RateDesc');//0. SQL.Add('FROM tRate'); // 1. SQL.Add('WHERE tRate.Active = 1 '); // 2. SQL.Add(' AND tRate.ServiceTypeID = 0'); // 3. SQL.Add(' AND tRate.Length = 0'); // 4. end; // qryTEC. 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 UniComms 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 := 1; 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 ('; SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ServiceID,Var05,Var11,'; sListValues := sListValues + ''''+SubString +''','''+ SubString+''','''+SubString+''','; end; // Save to string list. // Main 0. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; // Set default charge rates. if (SubString <> LastServiceID) then begin // Find references to define the applicable charges. LastServiceID := SubString; GetCustomerID(LastServiceID,sServiceTypeID,sServiceTypeDesc, sCustomerID); // Set the GSTflags - for later cost calculations. if (sCustomerID = sVRTID) then begin lGSTflag := FALSE; sGSTflag := '''N'','; end else begin lGSTflag := TRUE; sGSTflag := '''Y'','; end; // Update the SQL statement for TEC & SFC. lServiceTypeOkay := True; // Checked when costing TEC. if (Length(sServiceTypeID) = 0) then begin sServiceTypeID := '0'; lServiceTypeOkay := False; end; qryTEC.Close; qryTEC.SQL.strings[3] := ' AND tRate.ServiceTypeID = '+sServiceTypeID; // 3. end; // LastServiceID. sListFields := sListFields + 'SequenceNo,TransactionTypeID,'; sListValues := sListValues +''''+ Right('0000'+IntToStr(nTotalCount),5)+''','+ sRentTransactionCodeID+','; // Set in GetReferences(). // TEC start.. sUTECString := CleanString(Trim(ConvertString.Strings[TEC_C])); SubString := Copy(sUTECString,2,2); // if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var06, Var09,'; // Var06, Var09. sListValues := sListValues +'''T'+ SubString +''',' + ''''+ sUTECString +''','; // end; // Save to string list. // Main 1. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; // TEC='XX'-TEC unknown: unused or not allocated, set rent at default; // TEC='NN'-allocated elsewhere a/c reallocation, do not charge again. sListFields := sListFields + 'AmountExGST, GSTAmount, AmountIncGST,'+ 'SupplierServiceCodeID, GSTFlag, RateDescription, '; if (SubString = 'NN') then begin sListValues := sListValues + '0,0,0,NULL,''N'',' + '''TEC Reallocated'','; end else begin // if (SubString = 'XX') or ( not lServiceTypeOkay ) then begin if not lServiceTypeOkay then begin // Use the VRT default rate - servicetypeid not in tRate. curValue := curValueDefaultTEC; sSupplierCodeID := sSupplierDefaultTECcodeID; sSupplierCodeDesc := sSupplierDefaultTECcodeDesc; end // ...lServiceTypeOkay // Determine the cost from the values read from the tRate table. else if (SubString = 'XX') then begin // Use the default TEC for this ServiceTypeID. qryTEC.Close; qryTEC.SQL.strings[4] := ' AND tRate.Length = '+ TEC_DEFAULT; // 4. qryTEC.Open; if qryTEC.Eof then begin // Not available - use the VRT_DEFAULT rates. curValue := curValueDefaultTEC; sSupplierCodeID := sSupplierDefaultTECcodeID; sSupplierCodeDesc := sSupplierDefaultTECcodeDesc; end else begin // Use the default rate listed for this service. curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12); sSupplierCodeID := sSupplierDefaultTECcodeID; sSupplierCodeDesc := TRIM(qryTEC.FieldByName('RateDesc').AsString); end; end // ...'XX' else begin // Use the default rate. qryTEC.Close; qryTEC.SQL.strings[4] := ' AND tRate.Length = '+SubString; // 4. qryTEC.Open; if qryTEC.Eof then begin // Not found - is the default for this servicetype availble? qryTEC.Close; qryTEC.SQL.strings[4] := ' AND tRate.Length = '+ TEC_DEFAULT; // 4. qryTEC.Open; if qryTEC.Eof then begin // Not available - use the VRT_DEFAULT rates. curValue := curValueDefaultTEC; sSupplierCodeID := sSupplierDefaultTECcodeID; sSupplierCodeDesc := sSupplierDefaultTECcodeDesc; end else begin // Use the default rate listed for this service. curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12); sSupplierCodeID := sSupplierDefaultTECcodeID; sSupplierCodeDesc := TRIM(qryTEC.FieldByName('RateDesc').AsString); end; end else begin // Use the actual charge rate for this service. curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12); sSupplierCodeID := GetSupplierCodeID(Copy(sUTECString,1,3)); sSupplierCodeDesc := TRIM(qryTEC.FieldByName('RateDesc').AsString); end; // Eof. end; // ..'XX'. if lGSTflag then curValueGST := curValue * fGSTRate else curValueGST := 0; sListValues := sListValues + CurrToStr(curValue)+',' + CurrToStr(curValueGST)+',' + CurrToStr(curValue + curValueGST)+',' + sSupplierCodeID +','+ sGSTFlag + ''''+sSupplierCodeDesc+''','; end; // 'NN'. qryTEC.Close; // Save to string list. // Main 2. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; // TEC ..end sListFields := sListFields + 'ServiceType,'; // ServiceType. sListValues := sListValues + ' '''+Trim(sServiceTypeDesc+ ' T'+SubString)+''','; // Save to string list. // Main 3. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; 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; // Save to string list. // Main 4. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; 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[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); // Main 4(5). 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; // Now add a record for the Service used. Inc(nTotalCount); FieldStringList.Strings[1] := 'SequenceNo, TransactionTypeID, '+ 'Var07, Var09,'; // Var07,09 ValueStringList.Strings[1] := ''''+ Right('0000'+IntToStr(nTotalCount),5)+''','+ sServiceTransactionCodeID+', '''+ // Set in GetReferences(). Copy(sUTECString,7,3) +''',''' + sUTECString +''','; // Main 1. // Start SFC ... sListValues := ''; // Flush any remnants in this string -in case. SubString := UpperCase(Copy(sUTECString,8,2)); // TEC='XX'-TEC unknown: unused or not allocated, set rent at default; // TEC='NN'-allocated elsewhere a/c reallocation, do not charge again. if (SubString = 'NN') then begin sListValues := '0, 0, 0, NULL, ''N'', ''SFC Reallocated'','; end else begin // if (SubString = 'XX') or ( not lServiceTypeOkay ) then begin if not lServiceTypeOkay then begin // Use the VRT default rate - servicetypeid not in tRate. curValue := curValueDefaultSFC; sSupplierCodeID := sSupplierDefaultSFCcodeID; sSupplierCodeDesc := sSupplierDefaultSFCcodeDesc; end // ...lServiceTypeOkay // Determine the cost from the values read from the tRate table. else if SubString = 'XX' then begin // Use the default SFC for this ServiceTypeID. qryTEC.Close; qryTEC.SQL.strings[4] := ' AND tRate.Length = '+ SFC_DEFAULT; // 4. qryTEC.Open; if qryTEC.Eof then begin // Not available - use the VRT_DEFAULT rates. curValue := curValueDefaultSFC; sSupplierCodeID := sSupplierDefaultSFCcodeID; sSupplierCodeDesc := sSupplierDefaultSFCcodeDesc; end else begin // Use the default rate listed for this service. curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12); sSupplierCodeID := sSupplierDefaultSFCcodeID; sSupplierCodeDesc := TRIM(qryTEC.FieldByName('RateDesc').AsString); end; end // ...'XX' else begin // Use the default rate - even if SubString = 'XX'. qryTEC.Close; qryTEC.SQL.strings[4] := ' AND tRate.Length = '+SubString; // 4. qryTEC.Open; if qryTEC.Eof then begin // Not found - is the default for this servicetype availble? qryTEC.Close; qryTEC.SQL.strings[4] := ' AND tRate.Length = '+ SFC_DEFAULT; // 4. qryTEC.Open; if qryTEC.Eof then begin // Not available - use the VRT_DEFAULT rates. curValue := curValueDefaultSFC; sSupplierCodeID := sSupplierDefaultSFCcodeID; sSupplierCodeDesc := sSupplierDefaultSFCcodeDesc; end else begin // Use the default rate listed for this service. curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12); sSupplierCodeID := sSupplierDefaultSFCcodeID; sSupplierCodeDesc := TRIM(qryTEC.FieldByName('RateDesc').AsString); end; end else begin // Use the actual charge rate for this service. curValue := curFormat(qryTEC.FieldByName('cost').AsCurrency / 12); sSupplierCodeID := GetSupplierCodeID(Copy(sUTECString,7,3)); sSupplierCodeDesc := TRIM(qryTEC.FieldByName('RateDesc').AsString); end; // Eof. end; // ..'XX'. if lGSTflag then curValueGST := curValue * fGSTRate else curValueGST := 0; sListValues := CurrToStr(curValue)+',' + CurrToStr(curValueGST)+',' + CurrToStr(curValue + curValueGST)+',' + sSupplierCodeID +','+ sGSTFlag + ''''+ sSupplierCodeDesc +''','; end; // 'NN'. qryTEC.Close; // Save to string list. ValueStringList.Strings[2] := sListValues; // Main 2. sListValues := ''; ValueStringList.Strings[3] := ' '''+ Trim(sServiceTypeDesc+ ' S'+SubString)+''','; // Main 3. // ... End SFC. // Now save the the tDataCollection table. 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; // Done. Values updated from the tRates table for TEC & SFC values. 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 GetUniCRent. procedure GetUniCSwitchboard( 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; sServiceTypeID : string; sServiceTypeDesc : string; sCustomerID : string; sCallExGST : string; sCallIncGST : string; sGST : string; sGSTflag : string; nListValues : integer; qryDC : TQuery; { lDummyEntry : boolean; } sLastCallCode : string; lFound : boolean; sLastTransactionTypeID : string; sLastSupplyCodeID : string; slAddFieldNames, slAddFieldValues : TStringList; { lTESTING :boolean; } begin { lTESTING := TRUE; } 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); { if lTESTING then begin SubString := UpperCase(Trim(ConvertString.Strings[0])); if substring = 'KDR' then begin lDummyEntry := FALSE; end; end; } 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,sServiceTypeID,sCustomerID); GetCustomerID('SW'+LastServiceID,sServiceTypeID,sServiceTypeDesc, sCustomerID); if (sCustomerID = sVRTID) then begin sCallExGST := sVSWEx; sCallIncGST := sVSWInc; sGST := sVSWGST; sGSTflag := sVSWGSTflag; end else begin sCallExGST := sSWEx; sCallIncGST := sSWInc; sGST := sSWGST; sGSTflag := sSWGSTflag; 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); 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; // GetUniCsSwitchboard. procedure UpdateBatchStatus(const nThisBatch, nStatus: LongInt); qryBA.DatabaseName := 'dbPPdata'; 'SELECT BatchStatusID FROM tBatch 'WHERE (ID = '+ IntToStr(nBatch)+') if FOUND // (Test avoids a possible error - but can't get here unless it exists). if (qryBA['BatchStatusID'] <> nOutcome) then begin // Update the tBatch table. qryStringList := 'UPDATE tBatch 'SET BatchStatusID = '+IntToStr(nOutcome) 'FROM tBatch 'WHERE ID = '+ IntToStr(nBatch) qryBA.ExecSQL; end; // if (qryDC['BatchStatusID'] <> nOutcome) then. // Update the tBatchStatusHistory table. qryStringList := 'INSERT INTO tBatchStatusHistory ' (BatchID, BatchStatusID, StatusChangeDate,UserID) ' VALUES ('+IntToStr(nBatch)+','+IntToStr(nOutcome)+ ','''+StandardDateString(Now)+''','+IntToStr(MainForm.OPERATORNUMBER)+' ) qryBA.ExecSQL; end; // if qryDC.Eof and qryDC.Bof then. // Now update the list of viewable Batch numbers in tDataCollection // (in TabTransactionSummary). MainForm.SetBillingPeriodBatch; end; // UpdateBatchStatus(). procedure CriticalErrorTest(Const nThisBatch: LongInt;var lSuccessful: Boolean); var CriticalMsg : TMessagesForm; nBatch : LongInt; sBatch : string; qryDC : TQuery; // tDataCollection. qryBatchType : TQuery; qryStringList : TStringList; lResultIsGood : boolean; begin // MainForm.StartTime; // // nBatch := nThisBatch; sBatch := Trim(IntToStr(nBatch)); lSuccessful := True; lResultIsGood := True; qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; CriticalMsg := TMessagesForm.Create(MainForm); CriticalMsg.Caption := sBatch+': Critical Error Checks'; CriticalMsg.Msg.Caption := 'Clearing previous results ...'; CriticalMsg.Show; CriticalMsg.ProcessWinMessages(MainForm); qryBatchType := TQuery.Create(MainForm); qryBatchType.DatabaseName := 'dbPPdata'; qryStringList.Clear; qryStringList.Add('SELECT tBatchType.*, tBatch.BatchStatusID'); qryStringList.Add('FROM tBatchType INNER JOIN tBatch'); qryStringList.Add(' ON tBatchType.ID = tBatch.BatchTypeID'); qryStringList.Add('WHERE tBatch.ID = ' + sBatch); qryBatchType.SQL := qryStringList; qryBatchType.Open; { if (qryBatchType['BatchStatusID'] = MainForm.BATCH_STATUS_CPASS) then begin lSuccessful := True; end else if (qryBatchType['BatchStatusID'] < MainForm.BATCH_STATUS_CPASS) then begin } if (qryBatchType['BatchStatusID'] <= MainForm.BATCH_STATUS_CPASS) then begin // First, reset any Critical Errors that have been corrected. Critical_Clear(nBatch); if qryBatchType.Eof and qryBatchType.Bof then begin // Always test for Critical Error 1 (unknown service). CriticalMsg.Msg.Caption := 'Checking for Unknown Services ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_1(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,1,lResultIsGood); lSuccessful := lResultIsGood; end else begin if qryBatchType.FieldByName('CriticalCheck01').AsBoolean then begin // Test for Critical Error 1 (unknown service). - ALWAYS. CriticalMsg.Msg.Caption := 'Checking for Unknown Services ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_1(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,1,lResultIsGood); end; // CriticalCheck02. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck02').AsBoolean then begin // Test for Critical Error 2 - CriticalMsg.Msg.Caption := 'Checking for additional Services ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_2(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,2,lResultIsGood); end; // CriticalCheck02. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck03').AsBoolean then begin // Test for Critical Error 3 - CriticalMsg.Msg.Caption := 'Checking for omitted Services ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_3(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,3,lResultIsGood); end; // CriticalCheck03. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck04').AsBoolean then begin // Test for Critical Error 4 - CriticalMsg.Msg.Caption := 'Checking for unexpected calls ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_4(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,4,lResultIsGood); end; // CriticalCheck04. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck05').AsBoolean then begin // Test for Critical Error 5 - CriticalMsg.Msg.Caption := 'Checking Splits are only between valid Services ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_5(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,5,lResultIsGood); end; // CriticalCheck05. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck06').AsBoolean then begin // Test for Critical Error 6 - CriticalMsg.Msg.Caption := 'Checking System references are correct ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_6(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,6,lResultIsGood); end; // CriticalCheck06. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck07').AsBoolean then begin // Test for Critical Error 7 - CriticalMsg.Msg.Caption := 'Checking for Terminated Services ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_7(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,7,lResultIsGood); end; // CriticalCheck07. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck08').AsBoolean then begin // Test for Critical Error 8 - CriticalMsg.Msg.Caption := 'Checking for incomplete Quetzal data ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_8(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,8,lResultIsGood); end; // CriticalCheck08. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck09').AsBoolean then begin // Test for Critical Error 9 - CriticalMsg.Msg.Caption := 'Checking for unwanted split services ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_9(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,9,lResultIsGood); end; // CriticalCheck09. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck10').AsBoolean then begin // Test for Critical Error 10 - CriticalMsg.Msg.Caption := 'Checking CustomerCentreID is allocated ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_10(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,10,lResultIsGood); end; // CriticalCheck10. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck11').AsBoolean then begin // Test for Critical Error 11 - CriticalMsg.Msg.Caption := 'Checking TEC and SFC values are known ...'; CriticalMsg.ProcessWinMessages(MainForm); CriticalTest_11(nBatch, lResultIsGood); // Update the tCriticalCheck table. UpdateCriticalTestList(nBatch,11,lResultIsGood); end; // CriticalCheck11. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck12').AsBoolean then begin // Test for Critical Error 12 - // CriticalMsg.Msg.Caption := 'Checking Splits are only between valid Services ...'; // CriticalMsg.ProcessWinMessages(MainForm); // CriticalTest_12(nBatch, lResultIsGood); // Update the tCriticalCheck table. // UpdateCriticalTestList(nBatch,12,lResultIsGood); end; // CriticalCheck12. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck13').AsBoolean then begin // Test for Critical Error 13 - // CriticalMsg.Msg.Caption := 'Checking Splits are only between valid Services ...'; // CriticalMsg.ProcessWinMessages(MainForm); // CriticalTest_13(nBatch, lResultIsGood); // Update the tCriticalCheck table. // UpdateCriticalTestList(nBatch,13,lResultIsGood); end; // CriticalCheck13. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck14').AsBoolean then begin // Test for Critical Error 14 - // CriticalMsg.Msg.Caption := 'Checking Splits are only between valid Services ...'; // CriticalMsg.ProcessWinMessages(MainForm); // CriticalTest_14(nBatch, lResultIsGood); // Update the tCriticalCheck table. // UpdateCriticalTestList(nBatch,14,lResultIsGood); end; // CriticalCheck14. if lSuccessful then lSuccessful := lResultIsGood; if qryBatchType.FieldByName('CriticalCheck15').AsBoolean then begin // Test for Critical Error 15 - // CriticalMsg.Msg.Caption := 'Checking Splits are only between valid Services ...'; // CriticalMsg.ProcessWinMessages(MainForm); // CriticalTest_15(nBatch, lResultIsGood); // Update the tCriticalCheck table. // UpdateCriticalTestList(nBatch,15,lResultIsGood); end; // CriticalCheck15. if lSuccessful then lSuccessful := lResultIsGood; end; // if then begin. // Update the tBatch Status. if lSuccessful then begin // Critical check passed. UpdateBatchStatus(nBatch, MainForm.BATCH_STATUS_CPASS); end // if lSuccessful then begin. else begin // Critical errors. UpdateBatchStatus(nBatch, MainForm.BATCH_STATUS_CERRS); end; // if lSuccessful then begin..else. end; // BATCH_STATUS_CPASS. // Done - do housework. qryStringList.Free; qryBatchType.Close; qryBatchType.Free; qryDC.Close; qryDC.Free; CriticalMsg.Close; CriticalMsg.Free; // MainForm.EndTime; // // end; // procedure CriticalErrorTest. end.