ProgramID: 2101|Telmax Rent and Calls // In Main when selecting Telmax Rent and Calls if (nProgramID = PAUTOMATIC_TELMAX_RENT_CALLS) then begin // (2101) // import files are on the Telmax21, generated monthly by the Bilmax21 // For Telmax Rent and Call // Calls, Rent, Switchboard, Voicemail // For UC // Calls, Rent, Switchboard // ie. Telmax21 HOME ./links/billing_indata/Aug_2021/TELMAX21/PP in Folders UC and NotUC (Both iwhere 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 . // // get share folder containing Telmax21/Bilmax21 PP export files qry.DatabaseName := 'dbPPdata'; 'SELECT tParameter.ParmText'); 'FROM tParameter'); 'WHERE tParameter.Parm LIKE ''InDataRootDirectory'''); if NOTFOUND sImportFromDir := '' else sImportFromDir := FieldByName('ParmText').AsString; end; // qry. // import Telmax2ent and Calls TelmaxInvoice(nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName, sImportFromDir ); end // Telmax. procedure TelmaxInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription, sDirectory : string ); // This function prompts the user for the name of the Telmax supplied // text file (comma delimited) and then calls the function which reads the // file into tDataCollection table for further processing. var lContinue : Boolean; OpenDlg.Filter := 'Telmax Billing files (*.txt)|*.TXT|All files (*.*)|*.*'; / get starting folder for browsing if (Length(sDirectory) > 0) then OpenDlg.InitialDir := sDirectory else OpenDlg.InitialDir := ExtractFilePath(ParamStr(0)); OpenDlg.Title := 'Select The Telmax Input Directory'; if lContinue and (Length(Trim(sFile)) > 0) then begin // get Calls filename sPath := ExtractFilePath(sFile); sMask := 'c*.txt'; // Calls GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sCallFile := slFileList[0] else lContinue := False; // get Rent filename if lContinue then begin sMask := 'r*.txt'; // Rent slFileList.Clear; GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sRentFile := slFileList[0] else lContinue := False; end; // get Voicemail filename if lContinue then begin sMask := 'v*.txt'; // Voicemail slFileList.Clear; GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sVoiceMailFile := slFileList[0] else lContinue := False; end; // get Switchboard filename if lContinue then begin sMask := 's*.txt'; // Switchboard slFileList.Clear; GetFileList(sPath, sMask, slFileList); if slFileList.Count > 0 then sSwitchboardFile := slFileList[0] else lContinue := False; end; // if All files exist if lContinue and FileExists(sRentFile) and FileExists(sCallFile) and FileExists(sVoiceMailFile) and FileExists(sSwitchboardFile) then begin //Delete From tDatacollection table DeleteFromDC( nBatch, slCust, lContinue); // get tGroup.CustomerID from 'dbPPdata'.tServiceID // where ServiceID is blank ??? '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 Rent file GetTimsRent(nBatch,dStartDate,dEndDate, BatchDescription,sRentFile,lContinue); ZZZZZZZZ // Process Switchboard FILE if lContinue then GetTimsSwitchboard(nBatch,dStartDate,dEndDate, BatchDescription,sSwitchboardFile,lContinue); // Prcess Voicemail file if lContinue then GetTimsVoiceMail(nBatch,dStartDate,dEndDate, BatchDescription,sVoiceMailFile,lContinue); // Process the Calls file, last - it is really BIG !!! if lContinue then GetTimsCalls(nBatch,dStartDate,dEndDate, BatchDescription,sCallFile,lContinue); // if got files ok if lContinue then begin MessageForm.Caption := 'Importing VicTrack PABX 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)); qCustID.SQL := slCust; qCustID.ExecSQL; UpdateBatchStatus(nBatch, MainForm.BATCH_STATUS_IMPORT); // ********************************************************************* // ********************************************************************* // Test for critical errors that prevent progress to the next stage. lContinue := False; CriticalErrorTest( nBatch, lContinue ); // MainForm.EndTime; // // if lContinue then begin MessageDlg('Batch '+ IntToStr(nBatch) + ': Passed all tests for Critical Errors.', mtInformation, [mbOk], 0); end else begin MessageDlg('Batch '+ IntToStr(nBatch) + ': Critical errors found.'#13#10#13#10'These must be resolved ' + 'before this invoice can be progressed.', mtWarning, [mbOk], 0); end; // if lContinue then begin. end else begin slCust.Clear; lContinue := True; DeleteFromDC( nBatch, slCust, lContinue); end; // if lContinue then begin. end else begin MessageDlg('One or more Input files missing.', mtWarning, [mbAbort], 0 ); end; // if lContinue and FileExists(sRentFile) and..then begin..else. end; // if (Length(Trim(sFile)) > 0) then begin. // House keeping. qCustID.Close; // Used in GetCustomerID(). qCustID.Free; slCust.Free; // Used in GetCustomerID(). end; // procedure TelmaxInvoice. 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 GetTimsSwitchboard( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // var passed by reference // Data handled is for switchboad calls on 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%. // these are the fields in the RENT CSV 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. 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. qryDC.DatabaseName := 'dbPPdata'; // see function GetReferences below if GetReferences then begin // Open the input (text) billing file for reading. AssignFile( InputTextFile, Fname ); Progress.Caption := 'Reading The Telmax Switchboard File'; 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 // 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 // Decode the line read after converting the input line to SDF format. // Eg: converts the input string AES,AES01BLY,in,,N,Incoming Internal,20, // to "AES","AES01BLY","in","","N","Incoming Internal","20" ConvertString.CommaText := ConvertCDE2SDF(ReadString); SubString := UpperCase(Trim(ConvertString.Strings[CALLTYPE_C])); lDummyEntry := (SubString = 'X'); // 'X'-Dummy entry: exists for every Extension for Web Billing. if not lDummyEntry then begin // Save the strings into the table. sListFields := '('; 'VALUES ('; 'SequenceNo,'; ''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; ZZZZZZZZ SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ServiceID,Var05,'; sListValues := sListValues + '''SW'+SubString+''',''SW'+SubString+''','; end; // Set defaults and calculate charge rates. if (SubString <> LastServiceID) then begin // Find references to define the applicable charges. LastServiceID := SubString; GetCustomerID('SW'+LastServiceID,sCustomerID); if (sCustomerID = sVRTID) then begin sCallExGST := sVSWEx; sCallIncGST := sVSWInc; sGST := sVSWGST; sGSTflag := sVSWGSTflag; // sServiceType := '''SW1'','; Deleted-default ok. end else begin sCallExGST := sSWEx; sCallIncGST := sSWInc; sGST := sSWGST; sGSTflag := sSWGSTflag; // sServiceType := '''SW'','; Deleted-default ok. end; // if (sCustomerID = sVRTID) then begin..else. end; // if new ServiceID. // Determine the applicable charges. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'); ValueStringList.Add(sCallExGST+sGST+sCallIncGST+sGSTFlag); // FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'+ // 'ServiceType,'); // ValueStringList.Add(sCallExGST+sGST+sCallIncGST+sGSTFlag+ // sServiceType); SubString := Trim(ConvertString.Strings[CALLTYPE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCodeID := Trim(qryTelmax['SCodeID']); end else begin sLastTransactionTypeID := sCallCodeID; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add('''CALLS'''); slAddFieldNames.Add('Description'); slAddFieldValues.Add('''Switchboard Call'''); slAddFieldNames.Add('TransactionTypeDesc'); slAddFieldValues.Add('''Telmax Call'''); slAddFieldNames.Add('TransactionTypeID'); slAddFieldValues.Add(sLastTransactionTypeID); slAddFieldNames.Add('SupplierID'); slAddFieldValues.Add(sSupplierID); slAddFieldNames.Add('Active'); slAddFieldValues.Add('1'); AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues); qryTelmax.Close; qryTelmax.Open; with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCodeID := Trim(qryTelmax['SCodeID']); end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCodeID := sSupplyCallCodeID; end; end; // if (not) lFound. end; // if (sLastCallCode <> SubString) then begin. if (Length(sLastTransactionTypeID) > 0) then begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; sListValues := sListValues + sLastTransactionTypeID+','+ sLastSupplyCodeID+','; end; end else begin sListFields := sListFields + 'TransactionTypeID,SupplierserviceCodeID,'; sListValues := sListValues + sCallCodeID+','+sLastSupplyCodeID+','; end; SubString := Trim(ConvertString.Strings[CALLDATE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[ENDTIME_C]); if (Length(SubString) = 6) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''','; end; SubString := Trim(ConvertString.Strings[TIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+SubString+''','; end; SubString := Trim(ConvertString.Strings[CTYPDESC_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,Peak,'; sListValues := sListValues +''''+ CleanString(SubString) +''',0,'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // Extn. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // SiteID. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var03,'; // InletNo. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var04,'; // Inlet direction. sListValues := sListValues +''''+ SubString +''','; end; // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; end; // if (SubString <> 'X') or ((SubString = 'X') and.. // Keep track of how much work has been done to date. Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then nCount := 0; // reset progress bar. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); lReturnCode := True; end; // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then. finally // Done with the input file and resources, close them. CloseFile( InputTextFile ); Progress.Close; ConvertString.Free; FieldStringList.Free; ValueStringList.Free; qryStringList.Free; qryDC.Free; end; Progress.Close; end; // if GetReferences then begin. end; // GetTimsSwitchboard. function GetReferences : boolean; var lSuccessful : boolean; qry : TQuery; qryRef : TQuery; sRefCode : string; curValue,curValueGST : currency; begin // initialise the Static table elements defined as Private for this form. fGSTRate := MAINFORM.GST_RATE; Str(fGSTRate:6:4, sGSTRate); lSuccessful := True; qryTelmax := TQuery.Create(MainForm); qryTelmax.DatabaseName := 'dbPPdata'; qryTelmax.SQL.Add('SELECT id'); qryTelmax.SQL.Add('FROM tSupplier'); qryTelmax.SQL.Add('WHERE SupplierName LIKE ''VICTRACK%'''); qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin nSupplierID := qryTelmax['id']; sSupplierID := IntToStr(nSupplierID); qryTelmax.Close; qryTelmax.SQL.Clear; qryTelmax.SQL.Add('SELECT Code, CAST(ID AS VARCHAR(20)) AS "SCodeID", '+ 'CAST(TransactionTypeID AS VARCHAR(20)) AS TransactionType'); qryTelmax.SQL.Add('FROM tSupplierServiceCode'); qryTelmax.SQL.Add('WHERE SupplierID = '+sSupplierID); qryTelmax.SQL.Add('AND Code LIKE ''TELRENT%'''); qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin sRentCodeID := Trim(qryTelmax['TransactionType']); sSupplyRentCodeID := Trim(qryTelmax['SCodeID']); end; qryTelmax.Close; qryTelmax.SQL.Strings[qryTelmax.SQL.Count-1] := 'AND Code LIKE ''TELCALL%'''; qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin sCallCodeID := Trim(qryTelmax['TransactionType']); sSupplyCallCodeID := Trim(qryTelmax['SCodeID']); end; qryTelmax.Close; if lSuccessful then begin qryTelmax.SQL.Delete(qryTelmax.SQL.Count-1); qryTelmax.Open; // Set defaults. sTEC12Ex := '0,'; // TEC Not a Victrack user. sTEC12GST := '0,'; sTEC12Inc := '0,'; sTECorEx := '0,'; sTECorGST := '0,'; sTECorInc := '0,'; sTECGSTflag := '''Y'','; sTVC12Ex := '0,'; // TEC A Victrack user. sTVC12GST := '0,'; sTVC12Inc := '0,'; sTVCorEx := '0,'; sTVCorGST := '0,'; sTVCorInc := '0,'; sTVCGSTflag := '''N'','; sVMEx := '0,'; // Voicemail. sVMGST := '0,'; sVMInc := '0,'; sVMGSTflag := '''Y'','; sVVMEx := '0,'; // Voicemail (Victrack). sVVMGST := '0,'; sVVMInc := '0,'; sVVMGSTflag := '''N'','; sSWEx := '0,'; // Switchboard. sSWGST := '0,'; sSWInc := '0,'; sSWGSTflag := '''Y'','; sVSWEx := '0,'; // Switchboard (Victrack). sVSWGST := '0,'; sVSWInc := '0,'; sVSWGSTflag := '''N'','; qry := TQuery.Create(MainForm); qry.DatabaseName := 'dbPPdata'; qry.SQL.Add('SELECT id'); qry.SQL.Add('FROM tCustomer'); qry.SQL.Add('WHERE ShipTo LIKE ''VRT'''); qry.Open; if qry.Eof then sVRTID := '0' else sVRTID := IntToStr(qry.FieldByName('id').AsInteger); qry.Close; qryRef := TQuery.Create(MainForm); qryRef.DatabaseName := 'dbPPdata'; qryRef.SQL.Add('SELECT id, code'); qryRef.SQL.Add('FROM tServiceType'); qryRef.SQL.Add('WHERE Code LIKE ''TEC%'''); qryRef.Open; while not qryRef.Eof do begin sRefCode := qryRef.FieldByName('Code').AsString; if (sRefCode = 'TEC12') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; sTEC12Ex := CurrToStr(curValue)+','; sTEC12GST := CurrToStr(curValueGST)+','; sTEC12Inc := CurrToStr(curValue + curValueGST)+','; end else if (sRefCode = 'TECor') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; // sTECorEx := Curr2Str(curValue)+','; // sTECorGST := Curr2Str(curValueGST)+','; // sTECorInc := Curr2Str(curSum(curValue,curValueGST))+','; sTECorEx := CurrToStr(curValue)+','; sTECorGST := CurrToStr(curValueGST)+','; sTECorInc := CurrToStr(curValue + curValueGST)+','; end else if (sRefCode = 'TECVM') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; // sVMEx := Curr2Str(curValue)+','; // sVMGST := Curr2Str(curValueGST)+','; // sVMInc := Curr2Str(curSum(curValue,curValueGST))+','; sVMEx := CurrToStr(curValue)+','; sVMGST := CurrToStr(curValueGST)+','; sVMInc := CurrToStr(curValue + curValueGST)+','; sVVMEx := sVMEx; sVVMGST := '0,'; sVVMInc := sVMEx; end else if (sRefCode = 'TECSW') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency); qry.Close; curValueGST := curValue * fGSTRate; // sSWEx := Curr2Str(curValue)+','; // sSWGST := Curr2Str(curValueGST)+','; // sSWInc := Curr2Str(curSum(curValue,curValueGST))+','; sSWEx := CurrToStr(curValue)+','; sSWGST := CurrToStr(curValueGST)+','; sSWInc := CurrToStr(curValue + curValueGST)+','; sVSWEx := sSWEx; sVSWGST := '0,'; sVSWInc := sSWEx; end; qryRef.Next; end; // while not qryRef.Eof do begin. // Get references for TVC (Victrack) and TEC (non-Victrack) users. qryRef.Close; qryRef.SQL.Strings[2] := 'WHERE Code LIKE ''TVC%'''; qryRef.Open; while not qryRef.Eof do begin sRefCode := qryRef.FieldByName('Code').AsString; if (sRefCode = 'TVC12') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; sTVC12Ex := Curr2Str(curValue)+','; sTVC12GST := '0,'; sTVC12Inc := sTVC12Ex; end else if (sRefCode = 'TVCor') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; sTVCorEx := Curr2Str(curValue)+','; sTVCorGST := '0,'; sTVCorInc := sTVCorEx; end; qryRef.Next; end; // while not qryRef.Eof do begin. qryRef.Close; qryRef.Free; end else begin MessageDlg('Entries for RENT, CALLS must be added to '#13#10+ 'tSupplierServiceCode for VICTRACK before proceeding.', mtWarning, [mbOk], 0); end; end; // if qryTelmax.Eof then begin..else. Result := lSuccessful; end; // procedure GetReferences().