ProgramID: 2102|Telmax Call Diversion // In Main else if (nProgramID = PAUTOMATIC_TELMAX_DIVERSIONS) then begin with qry do begin // get default folder 'SELECT tParameter.ParmText'); SQL.Add('FROM tParameter'); SQL.Add('WHERE tParameter.Parm LIKE ''InDataRootDirectory'' '); if Eof then sImportFromDir := '' else sImportFromDir := FieldByName('ParmText').AsString; Close; end; // qry. TelmaxSACCInvoice(nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName, sImportFromDir ); end 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. qCustID := TQuery.Create(MainForm); // Used in GetCustomerID(). qCustID.DatabaseName := 'dbPPdata'; // First, we must establish which file is the one to be processed. OpenDlg.Filter := 'Telmax SACC Billing file '+ '(trancsv*.*)|TRANCSV*.*|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 Telmax Input File'; // Read and save the user's choices before continuing. if lContinue and (Length(Trim(sFile)) > 0) then begin if FileExists(sFile) then begin DeleteFromDC( nBatch, slCust, lContinue); // get tGroup.CustomerID from 'dbPPdata'.tServiceID // where ServiceID is blank ??? qCustID.DatabaseName := 'dbPPdata'; 'SELECT tGroup.CustomerID 'FROM tServiceID 'LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID 'LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID 'LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID 'WHERE tServiceID.ServiceID LIKE '' // Process Call Diversions file // the trancsv file is generated by the bacth script // transitswithsitecsv although I dont think its been run since June 2019 // files on the Telmax21 with name beinging with trancsv...i .... ./reports/tqnew/system/trancsv_26325 ./reports/tqnew/system/trancsv_2846 ./reports/tqnew/system/trancsv_28449 ./reports/tqnew/system/trancsv_7331 ./reports/tqnew/system/trancsv_23076 ./reports/tqnew/system/trancsv_10155 ./reports/tqnew/system/trancsv_16313 ./reports/tqnew/system/trancsv_15133 ./reports/tqnew/system/trancsv_22977 ./reports/tqnew/system/trancsv_24614 ./reports/tqnew/system/trancsv_2354 ./reports/tqnew/system/trancsv_21440 ./reports/tqnew/system/trancsv_8314 ./reports/tqnew/system/trancsv_17466 ./reports/tqnew/system/trancsv_21708 ./reports/tqnew/tmp/trancsv_26612 ./reports/robynd/system/trancsv_25779 ./reports/robynd/system/trancsv_21981 ./reports/robynd.s1/system/trancsv_25779 ./reports/robynd.s1/system/trancsv_21981 sh-3.2# ls -altr ./reports/tqnew/system/trancsv* | tail -rw-rw-rw- 1 catcom catcom 23659 Aug 8 2018 ./reports/tqnew/system/trancsv_30879 -rw-rw-rw- 1 catcom catcom 21836 Sep 5 2018 ./reports/tqnew/system/trancsv_21351 -rw-rw-rw- 1 catcom catcom 18489 Oct 5 2018 ./reports/tqnew/system/trancsv_31437 -rw-rw-rw- 1 catcom catcom 22546 Nov 9 2018 ./reports/tqnew/system/trancsv_26325 -rw-rw-rw- 1 catcom catcom 18392 Dec 12 2018 ./reports/tqnew/system/trancsv_32490 -rw-rw-rw- 1 catcom catcom 16104 Feb 11 2019 ./reports/tqnew/system/trancsv_28805 -rw-rw-rw- 1 catcom catcom 15743 Mar 14 2019 ./reports/tqnew/system/trancsv_12868 -rw-rw-rw- 1 catcom catcom 16805 Apr 10 2019 ./reports/tqnew/system/trancsv_25299 -rw-rw-rw- 1 catcom catcom 24021 May 14 2019 ./reports/tqnew/system/trancsv_15899 -rw-rw-rw- 1 tqnew catcom 16649 Jun 13 2019 ./reports/tqnew/system/trancsv_17983 // trancsv file format // eg. 01/05/2019,O,201001,B,144012,K,0433885450,060624,0.23,99,^M # generated on the telmax21 by the sql for each day in the period # which finds Operator Assisted transit calls out to the public # transit calls are ones where the inlet and outlet are bnoth trunks runisql 1>/dev/null 2>&1 <= $MAXCOST and ( inlettype <> "L" OR ( inlettype = "L" and outlettype = "K" and expandedcalltype = "A" ) ) $SITEBIT and siteid != 28 order by endtime SQLCMD GetTimsSACC(nBatch,dStartDate,dEndDate,BatchDescription,sFile,lContinue); if lContinue then begin MessageForm.Caption := 'Importing SACC charges'; MessageForm.Msg.Caption := 'Updating tBatch rates ...'; // Now update the values stored in the tBatch table. qCustID.DatabaseName := 'dbPPdata'; '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)); 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 DeleteFromDC(const nThisBatch:LongInt; const FieldValues: TStringList; const lSuccess: boolean); //Delete From tDatacollection table  Message 'Flushing tDataCollection data...'; DatabaseName := 'dbPPdata'; 'DELETE 'FROM tDataCollection 'WHERE BatchID = $nthisBatch end // DeleteFromDC 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 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 begin CriticalMsg.Caption := sBatch+': Critical Error Checks'; CriticalMsg.Msg.Caption := 'Clearing previous results ...'; qryBatchType.DatabaseName := 'dbPPdata'; 'SELECT tBatchType.*, tBatch.BatchStatusID'); 'FROM tBatchType INNER JOIN tBatch'); ' ON tBatchType.ID = tBatch.BatchTypeID'); 'WHERE tBatch.ID = ' + sBatch); 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 ...'; // 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 ...'; 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 ...'; 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 ...'; 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 ...'; 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 ...'; 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 ...'; 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 ...'; 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 ...'; 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 ...'; 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 ...'; 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 ...'; 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 - // 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. end; // procedure CriticalErrorTest. // I'll leave the Critcal test checks intact procedure UpdateCriticalTestList( const nThisBatch, Test_nn: LongInt; const lSuccessful: Boolean); var qryCC : TQuery; // CriticalCheck. qryStringList : TStringList; nBatch : LongInt; lPassed : integer; TestNumber : Integer; begin nBatch := nThisBatch; TestNumber := Test_nn; // If Outcome is True, then result is good - NO ERRORS. if lSuccessful then lPassed := 1 else lPassed := 0; qryStringList := TStringList.Create; qryCC := TQuery.Create(MainForm); qryCC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT * FROM tCriticalCheck'); qryStringList.Add('WHERE BatchID = '+ IntToStr(nBatch)); qryStringList.Add('AND CriticalChecknn = '+IntToStr(TestNumber)); qryCC.SQL := qryStringList; qryCC.Open; if qryCC.Eof and qryCC.Bof then // No data returned - must add it. begin qryStringList.Clear; qryStringList.Add('INSERT INTO tCriticalCheck'); qryStringList.Add(' (BatchID, CriticalChecknn, Passed)'); qryStringList.Add(' VALUES ( '+IntToStr(nBatch) + ', '+IntToStr(TestNumber)+', ' + IntToStr(lPassed) + ' )'); qryCC.Close; qryCC.SQL := qryStringList; qryCC.ExecSQL; end else if (qryCC['Passed'] <> lPassed) then // Already entered - must update it. begin qryStringList.Clear; qryStringList.Add('UPDATE tCriticalCheck'); qryStringList.Add('SET Passed = '+IntToStr(lPassed)); qryStringList.Add('FROM tCriticalCheck'); qryStringList.Add('WHERE BatchID = '+ IntToStr(nBatch)); qryStringList.Add('AND CriticalChecknn = '+IntToStr(TestNumber)); qryCC.Close; qryCC.SQL := qryStringList; qryCC.ExecSQL; end; // if qryDC.Eof and qryDC.Bof then. qryCC.Close; qryCC.Free; qryStringList.Free; end; // UpdateCriticalTestList. procedure Critical_Clear(Const nThisBatch: LongInt); // Flush the results from all previous Critical Tests. var nBatch : LongInt; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin nBatch := nThisBatch; qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; // First, reset ALL Critical Errors. qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET StatusID = NULL'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+IntToStr(nBatch)); qryDC.SQL := qryStringList; qryDC.ExecSQL; qryDC.Close; qryDC.Free; qryStringList.Free; end; // Critical_Clear. procedure CriticalTest_1(Const nThisBatch: LongInt;var lSuccessful: Boolean); // Test for Unknown Services in the bill / invoice. var sBatch : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT TOP 1 DC.ServiceID'); qryStringList.Add('FROM (SELECT tDataCollection.ServiceID'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add('LEFT JOIN tServiceID'); qryStringList.Add(' ON DC.ServiceID = tServiceID.ServiceID'); qryStringList.Add('WHERE tServiceID.ServiceID IS NULL'); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 1'); qryStringList.Add('FROM tDataCollection LEFT JOIN tServiceID'); qryStringList.Add(' ON tDataCollection.ServiceID = tServiceID.ServiceID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tServiceID.ServiceID IS NULL'); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_1. procedure CriticalTest_2(Const nThisBatch: LongInt;var lSuccessful: Boolean); // Test for Services in the invoice that belong on another bill. var sBatch : string; qryDC : TQuery; qryStringList : TStringList; sSupplierAccountID : string; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryDC.SQL.Add('SELECT SupplierAccountID FROM tBatch'); qryDC.SQL.Add('WHERE ID = '+sBatch); qryDC.Open; sSupplierAccountID := IntToStr(qryDC.FieldByName('SupplierAccountID').AsInteger); qryDC.Close; qryStringList.Add('SELECT TOP 1 DC.ServiceID'); qryStringList.Add('FROM (SELECT tDataCollection.ServiceID'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); // qryStringList.Add(' AND tDataCollection.StatusID IS NULL'); // qryStringList.Add(' GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add(' AND tDataCollection.StatusID IS NULL) AS DC'); qryStringList.Add('LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID'); qryStringList.Add('WHERE tServiceID.SupplierAccountID <> '+sSupplierAccountID); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; // Mark all remaining records that failed. qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 2'); qryStringList.Add('FROM (SELECT DISTINCT tDataCollection.ServiceID'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); // qryStringList.Add(' AND tDataCollection.StatusID IS NULL'); // qryStringList.Add(' GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add(' AND tDataCollection.StatusID IS NULL) AS DC'); qryStringList.Add('LEFT JOIN tServiceID ON DC.ServiceID = tServiceID.ServiceID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.ServiceID = DC.ServiceID'); qryStringList.Add('AND tServiceID.SupplierAccountID <> '+sSupplierAccountID); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_2. procedure CriticalTest_3(Const nThisBatch: LongInt;var lSuccessful: Boolean); // Test (and set?) results for Services Omitted in the bill / invoice. var sBatch : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; sSupplierAccountID : string; nTotalMissed : integer; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); with qryDC do begin DatabaseName := 'dbPPdata'; SQL.Add('SELECT SupplierAccountID FROM tBatch'); SQL.Add('WHERE ID = '+sBatch); Open; end; // qryDC. if (qryDC.Eof and qryDC.Bof) or (qryDC.FieldByName('SupplierAccountID').AsInteger = 0) then begin // Account number not registered - cannot do the test. lSuccessful := True; end else begin sSupplierAccountID := IntToStr(qryDC.FieldByName('SupplierAccountID').AsInteger); qryDC.Close; qryStringList.Add('SELECT COUNT(*) AS MissedNum'); qryStringList.Add('FROM (SELECT tServiceID.ServiceID'); qryStringList.Add(' FROM tServiceID'); qryStringList.Add(' WHERE tServiceID.Active = 1'); qryStringList.Add(' AND tServiceID.SupplierAccountID = '+sSupplierAccountID+ ') AS SID'); qryStringList.Add('LEFT JOIN (SELECT tDataCollection.ServiceID'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add(' GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add('ON SID.ServiceID = DC.ServiceID'); qryStringList.Add('WHERE DC.ServiceID IS NULL'); with qryDC do begin SQL := qryStringList; Open; nTotalMissed := FieldByName('MissedNum').AsInteger; Close; end; // qryDC. if (nTotalMissed = 0) then begin // No data returned. lSuccessful := True; end // if qryDC.Eof and qryDC.Bof then begin. else begin lSuccessful := False; // Must print the results (for now) as the transactions do not exist.. // that is why we have an error! qryStringList.Clear; qryStringList.Add('SELECT SID.ServiceID, SID.AccountNo'); qryStringList.Add('FROM (SELECT tServiceID.ServiceID, '+ 'tValidAccountNumber.AccountNo'); qryStringList.Add(' FROM tServiceID'); qryStringList.Add('LEFT JOIN tValidAccountNumber '+ 'ON tServiceID.SupplierAccountID = tValidAccountNumber.ID'); qryStringList.Add(' WHERE tServiceID.Active = 1'); qryStringList.Add(' AND tServiceID.SupplierAccountID = '+ sSupplierAccountID+') AS SID'); qryStringList.Add('LEFT JOIN (SELECT tDataCollection.ServiceID'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add(' GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add('ON SID.ServiceID = DC.ServiceID'); qryStringList.Add('WHERE DC.ServiceID IS NULL'); qryStringList.Add('ORDER BY SID.ServiceID'); with qryDC do begin Close; SQL := qryStringList; Open; PrintQuery( qryDC, 'Batch: '+sBatch+' - List of '+IntToStr(nTotalMissed)+ ' Services Omitted From The Invoice'); Close; end; // qryDC. end; // nTotalMissed = 0. end; // SupplierAccountID = 0..else. qryDC.Free; qryStringList.Free; end; // CriticalTest_3. procedure CriticalTest_4(Const nThisBatch: LongInt;var lSuccessful: Boolean); // Test and set results for Services in the invoice that belong on another bill. var sBatch : string; qryDC : TQuery; // tDataCollection. qryDC2: TQuery; // Update tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT TOP 1 DC2.*'); qryStringList.Add('FROM (SELECT DC1.*, tServiceType.NoCalls'); qryStringList.Add(' FROM (SELECT DC.ServiceID, DC.Call, ''Code'' = CASE'); qryStringList.Add(' WHEN DC.ServiceType IS NULL '+ 'THEN tServiceType.Code ELSE DC.ServiceType END'); qryStringList.Add('FROM ((SELECT tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType,'); qryStringList.Add(' Sum(CASE tTransactionGroup.Description '+ 'WHEN ''CALL'' THEN tDataCollection.AmountIncGST ELSE 0 END) AS CALL'); 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(' GROUP BY tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType) AS DC'); qryStringList.Add(' LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON DC1.Code = tServiceType.Code) AS DC2'); qryStringList.Add('WHERE DC2.Call <> 0'); qryStringList.Add('AND DC2.NoCalls = 1'); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; // Mark all remaining records that failed. qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 4'); qryStringList.Add('FROM (SELECT DC2.ServiceID'); qryStringList.Add('FROM (SELECT DC1.*, tServiceType.NoCalls'); qryStringList.Add(' FROM (SELECT DC.ServiceID, DC.Call, ''Code'' = CASE'); qryStringList.Add(' WHEN DC.ServiceType IS NULL '+ 'THEN tServiceType.Code ELSE DC.ServiceType END'); qryStringList.Add('FROM ((SELECT tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType,'); qryStringList.Add(' Sum(CASE tTransactionGroup.Description '+ 'WHEN ''CALL'' THEN tDataCollection.AmountIncGST ELSE 0 END) AS CALL'); 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(' GROUP BY tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType) AS DC'); qryStringList.Add(' LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON DC1.Code = tServiceType.Code) AS DC2'); qryStringList.Add('WHERE DC2.Call <> 0'); qryStringList.Add('AND DC2.NoCalls = 1) AS DC3'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.ServiceID = DC3.ServiceID'); qryDC2 := TQuery.Create(MainForm); qryDC2.DatabaseName := 'dbPPdata'; qryDC2.Sql := qryStringList; qryDC2.ExecSQL; qryDC2.Close; qryDC2.Free; end; qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_4. procedure CriticalTest_5(Const nThisBatch: LongInt;var lSuccessful: Boolean); // Test and set results for Split Services required and not found. var sBatch : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT TOP 1 DC2.ServiceID, DC2.SID'); qryStringList.Add('FROM (SELECT DC1.*, tServiceType.ID, '+ 'tServiceType.SplitService'); qryStringList.Add('FROM (SELECT DC.ServiceID, tServiceID.ID AS SID, '); qryStringList.Add(' (CASE WHEN DC.ServiceType IS NULL '+ 'THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"'); qryStringList.Add('FROM ((SELECT tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = ' +sBatch); qryStringList.Add(' GROUP BY tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType) AS DC'); qryStringList.Add(' LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1'); qryStringList.Add(' LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code'); qryStringList.Add(' WHERE DC1.SID IS NOT NULL'); qryStringList.Add(' AND tServiceType.SplitService = 1) AS DC2'); qryStringList.Add('LEFT JOIN tServiceSplit '+ 'ON DC2.SID = tServiceSplit.ServiceID'); qryStringList.Add('WHERE tServiceSplit.ServiceID IS NULL'); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; // Mark all remaining records that failed. qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 5'); qryStringList.Add('FROM (SELECT DC2.ServiceID'); qryStringList.Add('FROM (SELECT DC1.*, tServiceType.ID, '+ 'tServiceType.SplitService'); qryStringList.Add('FROM (SELECT DC.ServiceID, tServiceID.ID AS SID, '); qryStringList.Add(' (CASE WHEN DC.ServiceType IS NULL '+ 'THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"'); qryStringList.Add('FROM ((SELECT tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add(' AND tDataCollection.StatusID IS NULL'); qryStringList.Add(' GROUP BY tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType) AS DC'); qryStringList.Add(' LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1'); qryStringList.Add(' LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code'); qryStringList.Add(' WHERE DC1.SID IS NOT NULL'); qryStringList.Add(' AND tServiceType.SplitService = 1) AS DC2'); qryStringList.Add('LEFT JOIN tServiceSplit '+ 'ON DC2.SID = tServiceSplit.ServiceID'); qryStringList.Add('WHERE tServiceSplit.ServiceID IS NULL) AS DC2'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.ServiceID = DC2.ServiceID'); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_5. procedure CriticalTest_6(Const nThisBatch: LongInt;var lSuccessful: Boolean); // Test and set results for Unknown Services in the bill / invoice. var sBatch : string; sBatchTypeID : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT tBatch.BatchTypeID'); qryStringList.Add('FROM tBatch'); qryStringList.Add('WHERE tBatch.ID = '+sBatch); qryDC.SQL := qryStringList; qryDC.Open; sBatchTypeID := IntToStr(qryDC.FieldByName('BatchTypeID').AsInteger); qryDC.Close; qryStringList.Clear; qryStringList.Add('SELECT TOP 1 DC.ServiceID'); qryStringList.Add('FROM (((SELECT tDataCollection.ServiceID'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add('LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID)'); qryStringList.Add('LEFT JOIN tSystem ON tServiceType.SystemID = tSystem.ID'); qryStringList.Add('WHERE tSystem.BatchTypeID <> '+ sBatchTypeID); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 6'); qryStringList.Add('FROM (SELECT DC.ServiceID'); qryStringList.Add('FROM (((SELECT tDataCollection.ServiceID'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add('LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID)'); qryStringList.Add('LEFT JOIN tSystem ON tServiceType.SystemID = tSystem.ID'); qryStringList.Add('WHERE tSystem.BatchTypeID <> '+ sBatchTypeID+') AS DCupdate'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.ServiceID = DCupdate.ServiceID'); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_6. procedure CriticalTest_7(Const nThisBatch: LongInt;var lSuccessful: Boolean); // Test and set results for Terminated Services in the bill / invoice. var sBatch : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT TOP 1 DC.ServiceID'); qryStringList.Add('FROM (SELECT tDataCollection.ServiceID'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add('LEFT JOIN tServiceID'); qryStringList.Add(' ON DC.ServiceID = tServiceID.ServiceID'); qryStringList.Add('WHERE tServiceID.TerminationDate IS NOT NULL'); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 7'); qryStringList.Add('FROM tDataCollection LEFT JOIN tServiceID'); qryStringList.Add(' ON tDataCollection.ServiceID = tServiceID.ServiceID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.StatusID IS NULL'); qryStringList.Add('AND tServiceID.TerminationDate IS NOT NULL'); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_7. procedure CriticalTest_8(Const nThisBatch: LongInt;var lSuccessful: Boolean); // Test and set results for incomplete Quetzal data (Var04,05,08) which will // require alteration of the Quetzal database to correct it. var sBatch : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT TOP 1 DC.ServiceID'); qryStringList.Add('FROM (SELECT tDataCollection.ServiceID, '+ 'tDataCollection.Var04, tDataCollection.Var05, tDataCollection.Var08'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('GROUP BY tDataCollection.ServiceID, '+ 'tDataCollection.Var04, tDataCollection.Var05, tDataCollection.Var08) AS DC'); qryStringList.Add('WHERE DC.Var04 IS NULL'); qryStringList.Add('OR DC.Var05 IS NULL'); qryStringList.Add('OR DC.Var08 IS NULL'); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 8'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.StatusID IS NULL'); qryStringList.Add('AND (tDataCollection.Var04 IS NULL'); qryStringList.Add(' OR tDataCollection.Var05 IS NULL'); qryStringList.Add(' OR tDataCollection.Var08 IS NULL)'); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_8. procedure CriticalTest_9(const nThisBatch: LongInt;var lSuccessful: Boolean); // Test and set results for entries in tServiceSplit that do not have // tServiceType.SplitService checked - may result in duplicated transactions. var sBatch : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT TOP 1 DC2.ServiceID, DC2.SID'); qryStringList.Add('FROM (SELECT DC1.*, tServiceType.ID, '+ 'tServiceType.SplitService'); qryStringList.Add('FROM (SELECT DC.ServiceID, tServiceID.ID AS SID, (CASE'); qryStringList.Add(' WHEN DC.ServiceType IS NULL '+ 'THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"'); qryStringList.Add('FROM ((SELECT tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = ' +sBatch); qryStringList.Add(' GROUP BY tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType) AS DC'); qryStringList.Add(' LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1'); qryStringList.Add(' LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code'); qryStringList.Add(' WHERE DC1.SID IS NOT NULL'); qryStringList.Add(' AND tServiceType.SplitService <> 1) AS DC2'); qryStringList.Add('LEFT JOIN tServiceSplit '+ 'ON DC2.SID = tServiceSplit.ServiceID'); qryStringList.Add('WHERE tServiceSplit.ServiceID IS NOT NULL'); qryStringList.Add('AND tServiceSplit.Active = 1'); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; // Mark all remaining records that failed. qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 9'); qryStringList.Add('FROM (SELECT DC2.ServiceID'); qryStringList.Add('FROM (SELECT DC1.*, tServiceType.ID, '+ 'tServiceType.SplitService'); qryStringList.Add('FROM (SELECT DC.ServiceID, tServiceID.ID AS SID, (CASE'); qryStringList.Add(' WHEN DC.ServiceType IS NULL '+ 'THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"'); qryStringList.Add('FROM ((SELECT tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add(' AND tDataCollection.StatusID IS NULL'); qryStringList.Add(' GROUP BY tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType) AS DC'); qryStringList.Add(' LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1'); qryStringList.Add(' LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code'); qryStringList.Add(' WHERE DC1.SID IS NOT NULL'); qryStringList.Add(' AND tServiceType.SplitService <> 1) AS DC2'); qryStringList.Add('LEFT JOIN tServiceSplit '+ 'ON DC2.SID = tServiceSplit.ServiceID'); qryStringList.Add('WHERE tServiceSplit.ServiceID IS NOT NULL'); qryStringList.Add('AND tServiceSplit.Active = 1) AS DC3'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.ServiceID = DC3.ServiceID'); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; { // Mark all records where any of the splits do not add to 100%. qryStringList.Clear; qryStringList.Add('SELECT *'); qryStringList.Add('FROM (SELECT ServiceID,'); qryStringList.Add(' Sum(CASE CallPercentage WHEN NULL THEN 0 ELSE CallPercentage END) AS "CallPercent",'); qryStringList.Add(' Sum(CASE RentPercentage WHEN NULL THEN 0 ELSE RentPercentage END) AS "RentPercent",'); qryStringList.Add(' Sum(CASE OtherPercentage WHEN NULL THEN 0 ELSE OtherPercentage END) AS "OtherPercent"'); qryStringList.Add('FROM tServiceSplit'); qryStringList.Add('WHERE Active = 1'); qryStringList.Add('GROUP BY ServiceID) AS SS'); qryStringList.Add('WHERE CallPercent <> 100'); qryStringList.Add('OR RentPercent <> 100'); qryStringList.Add('OR OtherPercent <> 100'); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; // Mark all remaining records that failed. qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 9'); qryStringList.Add('FROM (SELECT DC2.ServiceID'); qryStringList.Add('FROM (SELECT DC1.*, tServiceType.ID, '+ 'tServiceType.SplitService'); qryStringList.Add('FROM (SELECT tServiceID.ID AS SID, DC.ServiceID, (CASE'); qryStringList.Add(' WHEN DC.ServiceType IS NULL '+ 'THEN tServiceType.Code ELSE DC.ServiceType END) AS "Code"'); qryStringList.Add('FROM ((SELECT tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add(' AND tDataCollection.StatusID IS NULL'); qryStringList.Add(' GROUP BY tDataCollection.ServiceID, '+ 'tDataCollection.ServiceType) AS DC'); qryStringList.Add(' LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID)'); qryStringList.Add(' LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID) AS DC1'); qryStringList.Add(' LEFT JOIN tServiceType ON DC1.Code = tServiceType.Code'); qryStringList.Add(' WHERE tServiceType.SplitService <> 1) AS DC2'); qryStringList.Add('LEFT JOIN tServiceSplit '+ 'ON DC2.SID = tServiceSplit.ServiceID'); qryStringList.Add('WHERE tServiceSplit.ServiceID IS NOT NULL'); qryStringList.Add('AND tServiceSplit.Active = 1) AS DC3'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.ServiceID = DC3.ServiceID'); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; } qryDC.Free; qryStringList.Free; end; // CriticalTest_9. procedure CriticalTest_10(const nThisBatch: LongInt;var lSuccessful: Boolean); // Test for entries with tServiceID.CutomerCentreID empty. var sBatch : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; qryStringList.Add('SELECT TOP 1 DC.ServiceID'); qryStringList.Add('FROM (SELECT tDataCollection.ServiceID'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = ' +sBatch); qryStringList.Add(' GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add(' INNER JOIN tServiceID '+ 'ON ((DC.ServiceID = tServiceID.ServiceID) AND (tServiceID.Active = 1))'); qryStringList.Add('WHERE tServiceID.CustomerCentreID IS NULL'); qryDC.SQL := qryStringList; qryDC.Open; if qryDC.Eof and qryDC.Bof then begin // No data returned. lSuccessful := True; end else begin lSuccessful := False; // Mark all remaining records that failed. qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.StatusID = 10'); qryStringList.Add('FROM (SELECT DC.ServiceID'); qryStringList.Add('FROM (SELECT tDataCollection.ServiceID'); qryStringList.Add(' FROM tDataCollection'); qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add(' GROUP BY tDataCollection.ServiceID) AS DC'); qryStringList.Add('INNER JOIN tServiceID '+ 'ON ((DC.ServiceID = tServiceID.ServiceID) AND (tServiceID.Active = 1))'); qryStringList.Add('WHERE tServiceID.CustomerCentreID IS NULL) AS DC3'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tDataCollection.ServiceID = DC3.ServiceID'); qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_10. procedure CriticalTest_11(const nThisBatch: LongInt;var lSuccessful: Boolean); // Test TEC & SFC values are registered and proced in tRates. var sBatch : string; qryDC : TQuery; // tDataCollection. qryStringList : TStringList; begin sBatch := IntToStr(nThisBatch); qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; // First pass - confirm allocated TEC is available in tRate and used. qryStringList.Add('SELECT TOP 1 tDataCollection.ID'); // 0. qryStringList.Add('FROM tDataCollection'); // 1. qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); // 2. qryStringList.Add('AND (tDataCollection.Var09 IS NOT NULL)'); // 3. qryStringList.Add('AND (tDataCollection.Var06 NOT IN (''TXX'', ''TNN''))');//4 qryStringList.Add('AND (SUBSTRING(RateDescription,5,2) <> SUBSTRING(Var06,2,2))'); qryDC.SQL := qryStringList; qryDC.Open; lSuccessful := (qryDC.Eof and qryDC.Bof); if lSuccessful then begin // No data returned, so check if the SFC is okay... qryStringList.strings[4] := 'AND (tDataCollection.Var07 NOT IN (''SXX'', ''SNN''))'; qryStringList.strings[5] := 'AND (SUBSTRING(RateDescription,5,2) <> SUBSTRING(Var07,2,2))'; qryDC.Close; qryDC.SQL := qryStringList; qryDC.Open; lSuccessful := (qryDC.Eof and qryDC.Bof); end; if not lSuccessful then begin // Mark all records that failed. // Start with TEC entries ... qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); // 0. qryStringList.Add('SET tDataCollection.StatusID = 11'); // 1. qryStringList.Add('FROM (SELECT DISTINCT tDataCollection.ServiceID,'+ ' tTransactionType.TransactionGroupID'); // 2. // qryStringList.Add('FROM (SELECT tDataCollection.ID'); // 2. qryStringList.Add(' FROM tDataCollection'); // 3. qryStringList.Add('LEFT JOIN tTransactionType '+ 'ON tDataCollection.TransactionTypeID = tTransactionType.ID'); // 4. qryStringList.Add(' WHERE tDataCollection.BatchID = '+sBatch); // 5. qryStringList.Add(' AND (tDataCollection.Var09 IS NOT NULL)'); // 6. qryStringList.Add(' AND (tDataCollection.Var06 NOT IN (''TXX'', ''TNN''))'); qryStringList.Add(' AND (SUBSTRING(tDataCollection.RateDescription,5,2)'+ ' <> SUBSTRING(tDataCollection.Var06,2,2))');// 8. qryStringList.Add(' AND (tDataCollection.StatusID IS NULL)) AS DC'); // 9. qryStringList.Add('WHERE tDataCollection.ServiceID = DC.ServiceID'); //10. // qryStringList.Add('WHERE tDataCollection.ID = DC.ID'); // 9. qryStringList.Add('AND (DC.TransactionGroupID = 1)'); // RENT. //11. // qryDC.Close; qryDC.Sql := qryStringList; qryDC.ExecSQL; // ... and now the SFC entries. qryStringList.strings[7] := 'AND (tDataCollection.Var07 NOT IN (''SXX'', ''SNN''))'; qryStringList.strings[8] := 'AND (SUBSTRING(tDataCollection.RateDescription,5,2)'+ ' <> SUBSTRING(tDataCollection.Var07,2,2))'; qryDC.Sql := qryStringList; qryDC.ExecSQL; end; // Done. Clean up. qryDC.Close; qryDC.Free; qryStringList.Free; end; // CriticalTest_11. 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.