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); // 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 GetTimsRent( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // Data handled is for rent of the Victrack fixed phones (PABX) network // This function reads the input file and saves it to the database. // begin lReturnCode := False; sBatch := IntToStr(nBatchNumber); sFromDate := FormattedDateString(dStartDate); sToDate := FormattedDateString(dEndDate); FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; if GetReferences then begin // Get transactiontypes. sPhoneRentDigital := '0,'; sPhoneRentDefault := sPhoneRentDigital; 'SELECT id, Description 'FROM tTransactionType 'WHERE Description LIKE ''% Phone Rent'' // Test for Digital and Standard Phone Rent transactions. while not qryDC.Eof do begin if (Copy(qryDC.FieldByName('Description').AsString,1,5) = 'Digit') then sPhoneRentDigital := IntToStr(qryDC.FieldByName('id').AsInteger)+',' else if (Copy(qryDC.FieldByName('Description').AsString,1,5) = 'Stand') then sPhoneRentDefault := IntToStr(qryDC.FieldByName('id').AsInteger)+','; qryDC.Next; end; // Open the input (text) billing file for reading. AssignFile( InputTextFile, Fname ); // Bigger buffer for faster reads. System.SetTextBuf(InputTextFile, InputTextFileBuffer); Reset( InputTextFile ); Progress.Caption := 'Reading The Telmax Rent File'; 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. // 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. sListFields := '('; sListValues := 'VALUES ('; 'SequenceNo,'; ''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; 'ServiceID,Var05,Var11,'; ''''+SubString +''','''+ SubString+''','''+SubString+''','; // 'ServiceID,Var11,'; // ''''+SubString+''','''+SubString+''','; end; // Set defaults and calculate charge rates. if (SubString <> LastServiceID) then begin // Find references to define the applicable charges. LastServiceID := SubString; GetCustomerID(LastServiceID,sCustomerID); if (sCustomerID = sVRTID) then begin sRent12ExGST := sTVC12Ex; sRent12IncGST := sTVC12Inc; s12GST := sTVC12GST; sRentExGST := sTVCorEx; sRentIncGST := sTVCorInc; sGST := sTVCorGST; sGSTflag := sTVCGSTflag; end else begin sRent12ExGST := sTEC12Ex; sRent12IncGST := sTEC12Inc; s12GST := sTEC12GST; sRentExGST := sTECorEx; sRentIncGST := sTECorInc; sGST := sTECorGST; sGSTflag := sTECGSTflag; end; // if (sCustomerID = sVRTID) then begin..else. end; // if new ServiceID. // Determine the applicable charges. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := UpperCase(Copy(Trim(ConvertString.Strings[TEC_C]),2,2)); // TEC='XX'-TEC unknown: unused or not allocated, set rent at default; // TEC='NN'-allocated elsewhere a/c reallocation, do not charge again. FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'+ 'TransactionTypeID,SupplierServiceCodeID,'); if (Pos(SubString, 'NN') = 0) then begin if (SubString = '12') then ValueStringList.Add(sRent12ExGST+s12GST+sRent12IncGST+sGSTFlag+ sPhoneRentDigital+sSupplyRentCodeID+',') else ValueStringList.Add(sRentExGST+sGST+sRentIncGST+sGSTFlag+ sPhoneRentDefault+sSupplyRentCodeID+','); end else begin ValueStringList.Add('0,0,0,''N'','+ sRentCodeID+','+ sSupplyRentCodeID+','); end; SubString := Trim(ConvertString.Strings[STARTDATE_C]); sListFields := sListFields + 'FromDate,'; if (Length(SubString) > 0) then sListValues := sListValues +''''+ SubString +''',' else sListValues := sListValues + sFromDate; SubString := Trim(ConvertString.Strings[ENDDATE_C]); sListFields := sListFields + 'ToDate,'; if (Length(SubString) > 0) then sListValues := sListValues +''''+ SubString +''',' else sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // Var01. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // Var02. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[TEC_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var09,'; // Var09. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SHIPTO_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var10,'; // Var10. sListValues := sListValues +''''+ SubString +''','; end; // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; // Keep track of how much work has been done to date. Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * Factor)>=100.00) then. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); // Now update the values stored in the tDataCollection table with // any alternate service charge rates stored separately in tRates. qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET tDataCollection.AmountExGST = DC4.AmountExGST,'); qryStringList.Add(' tDataCollection.GSTAmount = DC4.GSTAmount,'); qryStringList.Add(' tDataCollection.AmountIncGST = DC4.AmountIncGST'); qryStringList.Add('FROM (SELECT DC3.*, '+ 'CAST((DC3.AmountExGST + DC3.GSTAmount) AS money) AS "AmountIncGST"'); qryStringList.Add('FROM (SELECT DC2.ID, DC2.AmountExGST,'); qryStringList.Add(' CAST((CASE WHEN tDataCollection.GSTFlag LIKE ''Y'' '+ 'THEN (DC2.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"'); qryStringList.Add('FROM (SELECT DC1.ID, (Max(tRate.Cost)/12) AS "AmountExGST"'); qryStringList.Add('FROM ((SELECT DC0.ID, DC0.ServiceID, (CASE'); qryStringList.Add(' WHEN DC0.TEC IS NULL THEN 0'); qryStringList.Add(' WHEN DC0.TEC = ''XX'' THEN 0'); qryStringList.Add(' WHEN DC0.TEC = ''NN'' THEN NULL'); qryStringList.Add(' WHEN DATALENGTH(RTRIM(DC0.TEC)) = 0 THEN 0'); qryStringList.Add(' ELSE CAST(DC0.TEC AS int) END) AS "Length"'); qryStringList.Add('FROM (SELECT tDataCollection.ID, '+ 'tDataCollection.ServiceID,'); qryStringList.Add(' SUBSTRING(tDataCollection.Var09,2,2) AS "TEC"'); qryStringList.Add('FROM tDataCollection'); qryStringList.Add('LEFT JOIN tTransactionType '+ 'ON tDataCollection.TransactionTypeID = tTransactionType.ID'); qryStringList.Add('LEFT JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+sBatch); qryStringList.Add('AND tTransactionGroup.Description LIKE ''RENT'') AS DC0) AS DC1'); qryStringList.Add('LEFT JOIN tServiceID AS SI1 '+ 'ON DC1.ServiceID = SI1.ServiceID)'); qryStringList.Add('INNER JOIN tRate '+ 'ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) '+ 'AND (ROUND(DC1.Length,0,0) = ROUND(tRate.Length,0,0)))'); qryStringList.Add('WHERE DC1.Length IS NOT NULL'); qryStringList.Add('AND tRate.Cost IS NOT NULL'); qryStringList.Add('GROUP BY DC1.ID) AS DC2'); qryStringList.Add('INNER JOIN tDataCollection '+ 'ON tDataCollection.ID = DC2.ID) AS DC3) AS DC4'); qryStringList.Add('WHERE tDataCollection.ID = DC4.ID'); // Execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; lReturnCode := True; end; // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then. finally // Done with the input file and resources, close them. end; end; // if GetReferences then begin. end; // procedure GetTimsRent. 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. // // eg. switchboard call data record (from s9notccm_082021.txt) //EDH,EDI01DYL,15851,01/08/2021,122152,55,29010,out,11100,O,Outgoing Internal Network,216,0.0,^M^J //VAC,DRSVACA,57726,25/08/2021,095016,99,70010,out,11111,O,Outgoing Internal Network,4,0.0,^M^J 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); // biuld a string witu sql insert values 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 :='(' sListFields := 'VALUES (' 'SequenceNo,' ''''+ Right('0000'+IntToStr(nTotalCount),5)+''',' // SERVID_C col is Telmax21 Extension Group, (TBS serviceid) SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin 'ServiceID,Var05,' '''SW'+SubString+''',''SW'+SubString+''',' end; // Set defaults and calculate charge rates. if (SubString <> LastServiceID) then begin // Find references to define the applicable charges. // (see GetReferencesi function) 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. // add to list failds and values FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'); ValueStringList.Add(sCallExGST+sGST+sCallIncGST+sGSTFlag); // for CallType code SubString := Trim(ConvertString.Strings[CALLTYPE_C]); if (Length(SubString) > 0) then begin 'CallCode,'; ''''+ SubString +''','; // create a list of Call type codes // for adding to the tSupplierServiceCodes table 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); with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCodeID := Trim(qryTelmax['SCodeID']); end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCodeID := sSupplyCallCodeID; end; end; // if (not) lFound. end; // if (sLastCallCode <> SubString) then begin. if (Length(sLastTransactionTypeID) > 0) then begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; sListValues := sListValues + sLastTransactionTypeID+','+ sLastSupplyCodeID+','; end; end else begin sListFields := sListFields + 'TransactionTypeID,SupplierserviceCodeID,'; sListValues := sListValues + sCallCodeID+','+sLastSupplyCodeID+','; end; SubString := Trim(ConvertString.Strings[CALLDATE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[ENDTIME_C]); if (Length(SubString) = 6) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''','; end; SubString := Trim(ConvertString.Strings[TIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+SubString+''','; end; SubString := Trim(ConvertString.Strings[CTYPDESC_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,Peak,'; sListValues := sListValues +''''+ CleanString(SubString) +''',0,'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // Extn. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // SiteID. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var03,'; // InletNo. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var04,'; // Inlet direction. sListValues := sListValues +''''+ SubString +''','; end; // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; end; // if (SubString <> 'X') or ((SubString = 'X') and.. // Keep track of how much work has been done to date. Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then nCount := 0; // reset progress bar. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); lReturnCode := True; end; // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then. finally // Done with the input file and resources, close them. CloseFile( InputTextFile ); Progress.Close; ConvertString.Free; FieldStringList.Free; ValueStringList.Free; qryStringList.Free; qryDC.Free; end; Progress.Close; end; // if GetReferences then begin. end; // GetTimsSwitchboard. procedure GetTimsVoiceMail( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // Data handled is for rent of Voicemail services on the Victrack fixed phones // (PABX) network. // This function reads the input file and saves the data to the database. // const PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. SHIPTO_C: integer = 0; // ShipTo. SERVID_C: integer = 1; // ServiceID. EXTN_C: integer = 2; // Extension. CALLDATE_C: integer = 3; // Date call made. ENDTIME_C: integer = 4; // Time call ended. SITE_C: integer = 5; // SiteID. INLET_C: integer = 6; // Inlet number. DIRECTN_C: integer = 7; // Inlet direction. DIALLED_C: integer = 8; // DialledNumber. CALLTYPE_C: integer = 9; // Call type. CTYPDESC_C: integer = 10; // Call type description. TIME_C: integer = 11; // Call duration. COST_C: integer = 12; // Call charge field. var InputTextFile: TextFile; InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. Progress: TProgressForm; nCount: Integer; nFactor: Real; nBlock : Integer; nTotalCount : integer; sFromDate : string; sToDate : string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; LastServiceID : string; sCustomerID : string; sRentExGST : string; sRentIncGST : string; sGST : string; sGSTflag : string; // sServiceType : string; Deleted-default ok. nListValues : integer; qryDC : TQuery; // MessageForm : TMessagesForm; lDummyEntry : boolean; curGSTCall : currency; lFound : boolean; sLastCallCode : string; sLastTransactionTypeID : string; sLastSupplyCodeID : string; slAddFieldNames, slAddFieldValues : TStringList; begin lReturnCode := False; sBatch := IntToStr(nBatchNumber); sFromDate := ''''+FormattedDateString(dStartDate)+''','; sToDate := ''''+FormattedDateString(dEndDate)+''','; slAddFieldNames := TStringList.Create; // Calls to AddToTable() only. slAddFieldValues := TStringList.Create; // Calls to AddToTable() only. FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; if GetReferences then begin // Open the input (text) billing file for reading. AssignFile( InputTextFile, Fname ); // Bigger buffer for faster reads. System.SetTextBuf(InputTextFile, InputTextFileBuffer); Reset( InputTextFile ); Progress := TProgressForm.Create(MainForm); Progress.Caption := 'Reading The Telmax Voicemail File'; Progress.btnCancel.Visible := False; Progress.Show; Progress.ProcessWinMessages(MainForm); ConvertString := TStringList.Create; // Used to convert SDF format to strings. try // Get the first occurence of the search string. Readln( InputTextFile, ReadString ); // To be sure the file isn't empty too... if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then begin // Initiate the data variables. nFactor := 0.1000; // Count to 100 every 1,000 records. nBlock := 0; nTotalCount := 0; nCount := 0; // Continue to search through the remaining text of the file // for occurrences of the search string. On each each find, // process the resultant string. repeat Progress.ProcessWinMessages(MainForm); // Decode the line read after converting the input line to SDF format. ConvertString.CommaText := ConvertCDE2SDF(ReadString); SubString := UpperCase(Trim(ConvertString.Strings[CALLTYPE_C])); lDummyEntry := (SubString = 'X'); // 'X'-Dummy entry: exists for every Extension for Web Billing. // If also DialledNumber='VOICEMAIL', then Voicemail is enabled and // rent is due (must add this charge as a transaction). if not lDummyEntry or (lDummyEntry and (UpperCase(Trim(ConvertString.Strings[DIALLED_C])) = 'VOICEMAIL')) then begin // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ServiceID,Var05,'; sListValues := sListValues + '''VM'+SubString+''',''VM'+SubString+''','; end; // Set defaults and calculate charge rates. if (SubString <> LastServiceID) then begin // Find references to define the applicable charges. LastServiceID := SubString; GetCustomerID('VM'+LastServiceID,sCustomerID); if (sCustomerID = sVRTID) then begin sRentExGST := sVVMEx; sRentIncGST := sVVMInc; sGST := sVVMGST; sGSTflag := sVVMGSTflag; // sServiceType := '''VM1'','; (or VVI) Deleted-default ok. end else begin sRentExGST := sVMEx; sRentIncGST := sVMInc; sGST := sVMGST; sGSTflag := sVMGSTflag; // sServiceType := '''VM'','; (orVOS,VOI)Deleted-default ok. end; // if (sCustomerID = sVRTID) then begin..else. end; // if new ServiceID. // Determine the applicable charges. FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'); // FieldStringList.Add('AmountExGST,GSTAmount,AmountIncGST,GSTFlag,'+ // 'ServiceType,'); SubString := UpperCase(Trim(ConvertString.Strings[CALLTYPE_C])); if lDummyEntry then // Rent transaction to be created. ValueStringList.Add(sRentExGST+sGST+sRentIncGST+sGSTFlag) // ValueStringList.Add(sRentExGST+sGST+sRentIncGST+sGSTFlag+ // sServiceType) else begin SubString := Trim(ConvertString.Strings[COST_C]); if (sCustomerID = sVRTID) then begin ValueStringList.Add(SubString+',0,'+SubString+','+ sGSTFlag); // sGSTFlag+sServiceType); end else begin // curGSTCall := curFormat(fGSTRate * StrToCurr(SubString)); // ValueStringList.Add(SubString+','+Curr2Str(curGSTCall)+','+ // Curr2Str(curSum(curGSTCall,StrToCurr(SubString)))+','+ // sGSTFlag); curGSTCall := fGSTRate * StrToCurr(SubString); ValueStringList.Add(SubString+','+CurrToStr(curGSTCall)+','+ CurrToStr(curGSTCall + StrToCurr(SubString))+','+ sGSTFlag); // sGSTFlag+sServiceType); end; // if (sCustomerID = sVRTID) then begin. end; // if (SubString = 'X') then..else. SubString := Trim(ConvertString.Strings[CALLTYPE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; with qryTelmax do if lDummyEntry then lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sRentCodeID]), [loCaseInsensitive]) else lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCodeID := Trim(qryTelmax['SCodeID']); end else begin if lDummyEntry then begin sLastTransactionTypeID := sRentCodeID; sLastSupplyCodeID := sSupplyRentCodeID; end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCodeID := sSupplyCallCodeID; end; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); if lDummyEntry then slAddFieldValues.Add('''RENT''') else slAddFieldValues.Add('''CALLS'''); slAddFieldNames.Add('Description'); slAddFieldNames.Add('TransactionTypeDesc'); if lDummyEntry then begin slAddFieldValues.Add('''Voicemail Rent'''); slAddFieldValues.Add('''Telmax Rent'''); end else begin slAddFieldValues.Add('''Voicemail Call'''); slAddFieldValues.Add('''Telmax Call'''); end; slAddFieldNames.Add('TransactionTypeID'); slAddFieldValues.Add(sLastTransactionTypeID); slAddFieldNames.Add('SupplierID'); slAddFieldValues.Add(sSupplierID); slAddFieldNames.Add('Active'); slAddFieldValues.Add('1'); AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues); qryTelmax.Close; qryTelmax.Open; with qryTelmax do if lDummyEntry then lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sRentCodeID]), [loCaseInsensitive]) else lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; end else begin if lDummyEntry then begin sLastTransactionTypeID := sRentCodeID; sLastSupplyCodeID := sSupplyRentCodeID; end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCodeID := sSupplyCallCodeID; end; end; end; // if (not) lFound. end; // if (sLastCallCode <> SubString) then begin. if (Length(sLastTransactionTypeID) > 0) then begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; if lDummyEntry then begin // sListValues := sListValues + sRentCodeID+','; sListValues := sListValues + sRentCodeID+','+sSupplyRentCodeID+','; end else begin // sListValues := sListValues + sCallCodeID+','; sListValues := sListValues + sCallCodeID+','+sSupplyCallCodeID+','; end; end; end else begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; if lDummyEntry then begin sListValues := sListValues + sRentCodeID+','+sSupplyRentCodeID+','; end else begin sListValues := sListValues + sCallCodeID+','+sSupplyCallCodeID+','; end; end; SubString := Trim(ConvertString.Strings[CALLDATE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[ENDTIME_C]); if (Length(SubString) = 6) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''','; end; SubString := Trim(ConvertString.Strings[TIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+SubString+''','; end; SubString := Trim(ConvertString.Strings[CTYPDESC_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,Peak,'; sListValues := sListValues +''''+ CleanString(SubString) +''',0,'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // Extn. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // SiteID. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var03,'; // InletNo. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var04,'; // Inlet direction. sListValues := sListValues +''''+ SubString +''','; end; // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; end; // if (SubString <> 'X') or ((SubString = 'X') and.. // Keep track of how much work has been done to date. Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=10) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then nCount := 0; // reset progress bar. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. Readln( InputTextFile, ReadString ); // Read in the next line of data. until Eof( InputTextFile ); lReturnCode := True; end; // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then. finally // Done with the input file and resources, close them. CloseFile( InputTextFile ); Progress.Close; ConvertString.Free; FieldStringList.Free; ValueStringList.Free; qryStringList.Free; qryDC.Free; end; Progress.Close; end; // if GetReferences then begin. end; // GetTimsVoiceMail. procedure GetTimsCalls( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const FName : string; var lReturnCode: Boolean ); // Data handled is for calls made from the Victrack fixed phones (PABX) network // and recorded by the Telmax monitoing software. // This function reads the input file and saves it in the database. // const RECORD_BYTES_C: Integer = 50; // Typical minimum bytes per record. PROGRESS_COUNTER_C: Integer=100; // Update Progress every 100 records read PROGRESS_MAX_C: Integer = 100; // Maximum progress is 100%. SHIPTO_C: integer = 0; // ShipTo. SERVID_C: integer = 1; // ServiceID. EXTN_C: integer = 2; // Extension. CALLDATE_C: integer = 3; // Date call made. ENDTIME_C: integer = 4; // Time call ended. SITE_C: integer = 5; // SiteID. INLET_C: integer = 6; // Inlet number. DIRECTN_C: integer = 7; // Inlet direction. DIALLED_C: integer = 8; // DialledNumber. CALLTYPE_C: integer = 9; // Call type. CTYPDESC_C: integer = 10; // Call type description. TIME_C: integer = 11; // Call duration. COST_C: integer = 12; // Call charge field. var InputTextFile: System.TextFile; InputTextFileBuffer: array[1..4096] of Char; // 4KB buffer. ReadString: string; // Read in from the text file. ConvertString: TStringList; // Used to convert SDF format to strings. Progress: TProgressForm; nCount: Integer; nFactor : Real; nBlock : Integer; vSubString : variant; // - ditto, used in the Locate() function. nTotalCount : integer; sFromDate : string; sToDate : string; SubString: string; sBatch : string; sListFields : string; sListValues : string; FieldStringList : TStringList; ValueStringList : TStringList; qryStringList : TStringList; slAddFieldNames : TStringList; // Calls to AddToTable() only. slAddFieldValues : TStringList; // Calls to AddToTable() only. nListValues : integer; qryDC : TQuery; sLastCallCode : string; sLastTransactionTypeID : string; sLastSupplyCallCodeID : string; lFound : boolean; lFinished : boolean; sCallTill : string; MessageForm : TMessagesForm; begin lReturnCode := False; sBatch := IntToStr(nBatchNumber); sFromDate := ''''+FormattedDateString(dStartDate)+''','; sToDate := ''''+FormattedDateString(dEndDate)+''','; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; qryStringList := TStringList.Create; slAddFieldNames := TStringList.Create; // Calls to AddToTable() only. slAddFieldValues := TStringList.Create; // Calls to AddToTable() only. qryDC := TQuery.Create(MainForm); qryDC.DatabaseName := 'dbPPdata'; sCallTill := ''; if GetReferences then begin // Open the input (text) billing file for reading. AssignFile( InputTextFile, Fname ); FileMode := 0; // Set file access to read only. // Bigger buffer for faster reads. System.SetTextBuf(InputTextFile, InputTextFileBuffer); Reset( InputTextFile ); Progress := TProgressForm.Create(MainForm); Progress.Caption := 'Reading The Telmax Call File'; Progress.btnCancel.Visible := False; Progress.Show; Progress.ProcessWinMessages(MainForm); ConvertString := TStringList.Create; // Used to convert SDF format to strings. try // Get the first occurence of the search string. Readln( InputTextFile, ReadString ); // To be sure the file isn't empty too... if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then begin // Initiate the data variables. nCount := 0; lFinished := False; nFactor := 0.0100; // Count to 100 every 10,000 records. nBlock := 0; nTotalCount := 0; // Continue to search through the remaining text of the file // for occurrences of the search string. On each each find, // process the resultant string. repeat Progress.ProcessWinMessages(MainForm); // Decode the line read after converting the input line to SDF format. ConvertString.CommaText := ConvertCDE2SDF(ReadString); // Save the strings into the table. FieldStringList.Clear; ValueStringList.Clear; sListFields := '('; sListValues := 'VALUES ('; sListFields := sListFields + 'SequenceNo,'; sListValues := sListValues +''''+ Right('0000'+IntToStr(nTotalCount),5)+''','; SubString := Trim(ConvertString.Strings[SERVID_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'ServiceID, Var05,'; sListValues := sListValues + '''' + SubString +''', '''+ SubString +''',';; end; SubString := Trim(ConvertString.Strings[CALLTYPE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'CallCode,'; sListValues := sListValues +''''+ SubString +''','; if (sLastCallCode <> SubString) then begin sLastCallCode := SubString; vSubString := SubString; with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCallCodeID := Trim(qryTelmax['SCodeID'])+','; end else begin sLastTransactionTypeID := sCallCodeID; slAddFieldNames.Clear; slAddFieldValues.Clear; slAddFieldNames.Add('Code'); slAddFieldValues.Add(''''+sLastCallCode+''''); slAddFieldNames.Add('AmountCategory'); slAddFieldValues.Add('''CALLS'''); slAddFieldNames.Add('Description'); slAddFieldValues.Add('''Telmax Call'''); slAddFieldNames.Add('TransactionTypeDesc'); slAddFieldValues.Add('''Telmax Call'''); slAddFieldNames.Add('TransactionTypeID'); slAddFieldValues.Add(sLastTransactionTypeID); slAddFieldNames.Add('SupplierID'); slAddFieldValues.Add(sSupplierID); slAddFieldNames.Add('Active'); slAddFieldValues.Add('1'); AddToTable('tSupplierServiceCode',slAddFieldNames,slAddFieldValues); qryTelmax.Close; qryTelmax.Open; with qryTelmax do lFound := Locate('Code;TransactionType', VarArrayOf([SubString,sCallCodeID]), [loCaseInsensitive]); if lFound then begin sLastTransactionTypeID := qryTelmax['TransactionType']; sLastSupplyCallCodeID := Trim(qryTelmax['SCodeID'])+','; end else begin sLastTransactionTypeID := sCallCodeID; sLastSupplyCallCodeID := sSupplyCallCodeID; end; end; // if (not) lFound. end; // if (sLastCallCode <> SubString) then begin. if (Length(sLastTransactionTypeID) > 0) then begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+sLastSupplyCallCodeID; end; end else begin sListFields := sListFields + 'TransactionTypeID,SupplierServiceCodeID,'; sListValues := sListValues + sCallCodeID+','+sLastSupplyCallCodeID; end; SubString := Trim(ConvertString.Strings[CALLDATE_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnDate,'; sListValues := sListValues +''''+ SubString +''','; end; SubString := Trim(ConvertString.Strings[ENDTIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'TxnTime,'; sListValues := sListValues +''''+ Copy(SubString,1,2)+ ':'+ Copy(SubString,3,2) +':'+ Copy(SubString,5,2) +''','; end; SubString := Trim(ConvertString.Strings[TIME_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Duration,'; sListValues := sListValues +''''+ CleanString(SubString) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := CleanString(Trim(ConvertString.Strings[DIALLED_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'DialledNumber,'; sListValues := sListValues +''''+SubString+''','; end; SubString := Trim(ConvertString.Strings[CTYPDESC_C]); if (Length(SubString) > 0) then begin sListFields := sListFields + 'RateDescription,Peak,'; sListValues := sListValues +''''+ CleanString(SubString) +''',1,'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; SubString := Trim(ConvertString.Strings[COST_C]); sListFields := sListFields + 'AmountExGST,AmountIncGST,GSTFlag,'; sListValues := sListValues + SubString +',' + SubString +',''N'','; sListFields := sListFields + 'FromDate,'; sListValues := sListValues + sFromDate; sListFields := sListFields + 'ToDate,'; sListValues := sListValues + sToDate; SubString := CleanString(Trim(ConvertString.Strings[EXTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var01,'; // tServiceType.Description. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[SITE_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var02,'; // ServiceID.Description. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[INLET_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var03,'; // tLocation.Description. sListValues := sListValues +''''+ SubString +''','; end; SubString := CleanString(Trim(ConvertString.Strings[DIRECTN_C])); if (Length(SubString) > 0) then begin sListFields := sListFields + 'Var04,'; // tLocation.Description. sListValues := sListValues +''''+ SubString +''','; end; // Now make the SQL statement to save to the database table. sListFields := sListFields + 'BatchID)'; sListValues := sListValues + sBatch +')'; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); for nListValues := 0 to FieldStringList.Count-1 do begin qryStringList.Add(FieldStringList[nListValues]); end; for nListValues := 0 to ValueStringList.Count-1 do begin qryStringList.Add(ValueStringList[nListValues]); end; // Update and execute the query. qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; // Keep track of how much work has been done to date. Inc(nCount); Inc(nTotalCount); Inc(nBlock); if (nBlock=100) then begin Progress.ProgressBar1.Position := Trunc(nCount * nFactor); nBlock := 0; if ((nCount * nFactor)>=100.00) then begin // restart the progress bar. nCount := 0; end; // if ((nCount * nFactor)>100.00) then. Progress.ProgressBar1.Position := Trunc(nCount * nFactor); Progress.Msg.Caption := 'Processing (transaction: '+ IntToStr(nTotalCount)+')...'; end; // if (nBlock=10) then. Progress.ProcessWinMessages(MainForm); // Let windows update all messages. if Eof( InputTextFile ) then lFinished := True; Readln( InputTextFile, ReadString ); // Read in the next line of data. if Eof( InputTextFile ) and (Length(Trim(ReadString)) = 0) then lFinished := True; until lFinished; Progress.Hide; MessageForm := TMessagesForm.Create(MainForm); MessageForm.Caption := 'Importing Telmax Call charges'; MessageForm.Msg.Caption := 'Updating with applied rates ...'; MessageForm.Show; Progress.ProcessWinMessages(MainForm); qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET GSTAmount = GSTrate.GSTAmt,'); qryStringList.Add(' AmountIncGST = GSTrate.AmtExGST + GSTrate.GSTAmt,'); qryStringList.Add(' GSTflag = ''Y'''); qryStringList.Add('FROM (SELECT tDataCollection.ID, '+ 'tDataCollection.AmountExGST AS "AmtExGST", '+ '(tDataCollection.AmountExGST * '+sGSTRate+') AS "GSTAmt"'); qryStringList.Add('FROM (tDataCollection'); qryStringList.Add('LEFT JOIN tServiceID '+ 'ON tDataCollection.ServiceID = tServiceID.ServiceID)'); qryStringList.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID'); qryStringList.Add('WHERE tDataCollection.BatchID = '+ sBatch); qryStringList.Add('AND tDataCollection.AmountExGST <> 0'); qryStringList.Add('AND tServiceType.Code LIKE ''TQ'') AS GSTrate'); qryStringList.Add('WHERE tDataCollection.ID = GSTrate.ID'); qryDC.Close; qryDC.SQL := qryStringList; qryDC.ExecSQL; MessageForm.Close; MessageForm.Free; lReturnCode := True; end; // if (Length( ReadString ) > 0) or (not Eof( InputTextFile )) then. finally // Done with the input file and resources, close them. CloseFile( InputTextFile ); Progress.Close; ConvertString.Free; FieldStringList.Free; ValueStringList.Free; qryStringList.Free; qryDC.Free; end; Progress.Close; end; // if GetReferences then begin. end; // procedure GetTimsCalls(). procedure AddToTable(const sTableInsert: string; const sFields, sValues: TStringList); // Generic procedure to add the values to the named table. // All parameters must be strings. var qryAdd : TQuery; slStrings : TStringList; nItem, nTotal : integer; sResults : string; sThisTable : string; begin sThisTable := sTableInsert; nTotal := sFields.Count; if (sValues.Count = nTotal) then begin slStrings := TStringList.Create; slStrings.Add('INSERT INTO '+ sThisTable); sResults := '('; for nItem := 0 to nTotal-1 do begin sResults := sResults+ sFields[nItem] + ','; if (Length(sResults) > 60) and (nItem < nTotal-1) then begin slStrings.Add(sResults); sResults := ''; end; // if line > 60 chars. end; // for nItem := 0 to nTotal do begin. sResults := Copy(sResults, 1, Length(sResults)- 1); // Remove last com ma. sResults := sResults + ')'; slStrings.Add(sResults); sResults := 'VALUES ('; for nItem := 0 to nTotal-1 do begin sResults := sResults+ sValues[nItem] + ','; if (Length(sResults) > 60) and (nItem < nTotal-1) then begin slStrings.Add(sResults); sResults := ''; end; // if line > 60 chars. end; // for nItem := 0 to nTotal do begin. sResults := Copy(sResults, 1, Length(sResults)- 1); // Remove last com ma. sResults := sResults + ')'; slStrings.Add(sResults); qryAdd := TQuery.Create(MainForm); qryAdd.DatabaseName := 'dbPPdata'; qryAdd.SQL := slStrings; qryAdd.ExecSQL; qryAdd.Free; slStrings.Free; end else begin MessageDlg('Number of fields and data differ when'#13#10'appending to ''' + sThisTable+'''',mtWarning,[mbOk],0); end; // same number of strings. end; // procedure AddToTable(). function GetReferences : boolean; var lSuccessful : boolean; qry : TQuery; qryRef : TQuery; sRefCode : string; curValue,curValueGST : currency; begin // initialise the Static table elements defined as Private for this form. fGSTRate := MAINFORM.GST_RATE; Str(fGSTRate:6:4, sGSTRate); lSuccessful := True; qryTelmax := TQuery.Create(MainForm); qryTelmax.DatabaseName := 'dbPPdata'; qryTelmax.SQL.Add('SELECT id'); qryTelmax.SQL.Add('FROM tSupplier'); qryTelmax.SQL.Add('WHERE SupplierName LIKE ''VICTRACK%'''); qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin nSupplierID := qryTelmax['id']; sSupplierID := IntToStr(nSupplierID); qryTelmax.Close; qryTelmax.SQL.Clear; qryTelmax.SQL.Add('SELECT Code, CAST(ID AS VARCHAR(20)) AS "SCodeID", '+ 'CAST(TransactionTypeID AS VARCHAR(20)) AS TransactionType'); qryTelmax.SQL.Add('FROM tSupplierServiceCode'); qryTelmax.SQL.Add('WHERE SupplierID = '+sSupplierID); qryTelmax.SQL.Add('AND Code LIKE ''TELRENT%'''); qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin sRentCodeID := Trim(qryTelmax['TransactionType']); sSupplyRentCodeID := Trim(qryTelmax['SCodeID']); end; qryTelmax.Close; qryTelmax.SQL.Strings[qryTelmax.SQL.Count-1] := 'AND Code LIKE ''TELCALL%'''; qryTelmax.Open; if qryTelmax.Eof then begin lSuccessful := False; end else begin sCallCodeID := Trim(qryTelmax['TransactionType']); sSupplyCallCodeID := Trim(qryTelmax['SCodeID']); end; qryTelmax.Close; if lSuccessful then begin qryTelmax.SQL.Delete(qryTelmax.SQL.Count-1); qryTelmax.Open; // Set defaults. sTEC12Ex := '0,'; // TEC Not a Victrack user. sTEC12GST := '0,'; sTEC12Inc := '0,'; sTECorEx := '0,'; sTECorGST := '0,'; sTECorInc := '0,'; sTECGSTflag := '''Y'','; sTVC12Ex := '0,'; // TEC A Victrack user. sTVC12GST := '0,'; sTVC12Inc := '0,'; sTVCorEx := '0,'; sTVCorGST := '0,'; sTVCorInc := '0,'; sTVCGSTflag := '''N'','; sVMEx := '0,'; // Voicemail. sVMGST := '0,'; sVMInc := '0,'; sVMGSTflag := '''Y'','; sVVMEx := '0,'; // Voicemail (Victrack). sVVMGST := '0,'; sVVMInc := '0,'; sVVMGSTflag := '''N'','; sSWEx := '0,'; // Switchboard. sSWGST := '0,'; sSWInc := '0,'; sSWGSTflag := '''Y'','; sVSWEx := '0,'; // Switchboard (Victrack). sVSWGST := '0,'; sVSWInc := '0,'; sVSWGSTflag := '''N'','; qry := TQuery.Create(MainForm); qry.DatabaseName := 'dbPPdata'; qry.SQL.Add('SELECT id'); qry.SQL.Add('FROM tCustomer'); qry.SQL.Add('WHERE ShipTo LIKE ''VRT'''); qry.Open; if qry.Eof then sVRTID := '0' else sVRTID := IntToStr(qry.FieldByName('id').AsInteger); qry.Close; qryRef := TQuery.Create(MainForm); qryRef.DatabaseName := 'dbPPdata'; qryRef.SQL.Add('SELECT id, code'); qryRef.SQL.Add('FROM tServiceType'); qryRef.SQL.Add('WHERE Code LIKE ''TEC%'''); qryRef.Open; while not qryRef.Eof do begin sRefCode := qryRef.FieldByName('Code').AsString; if (sRefCode = 'TEC12') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; sTEC12Ex := CurrToStr(curValue)+','; sTEC12GST := CurrToStr(curValueGST)+','; sTEC12Inc := CurrToStr(curValue + curValueGST)+','; end else if (sRefCode = 'TECor') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; // sTECorEx := Curr2Str(curValue)+','; // sTECorGST := Curr2Str(curValueGST)+','; // sTECorInc := Curr2Str(curSum(curValue,curValueGST))+','; sTECorEx := CurrToStr(curValue)+','; sTECorGST := CurrToStr(curValueGST)+','; sTECorInc := CurrToStr(curValue + curValueGST)+','; end else if (sRefCode = 'TECVM') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; curValueGST := curValue * fGSTRate; // sVMEx := Curr2Str(curValue)+','; // sVMGST := Curr2Str(curValueGST)+','; // sVMInc := Curr2Str(curSum(curValue,curValueGST))+','; sVMEx := CurrToStr(curValue)+','; sVMGST := CurrToStr(curValueGST)+','; sVMInc := CurrToStr(curValue + curValueGST)+','; sVVMEx := sVMEx; sVVMGST := '0,'; sVVMInc := sVMEx; end else if (sRefCode = 'TECSW') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency); qry.Close; curValueGST := curValue * fGSTRate; // sSWEx := Curr2Str(curValue)+','; // sSWGST := Curr2Str(curValueGST)+','; // sSWInc := Curr2Str(curSum(curValue,curValueGST))+','; sSWEx := CurrToStr(curValue)+','; sSWGST := CurrToStr(curValueGST)+','; sSWInc := CurrToStr(curValue + curValueGST)+','; sVSWEx := sSWEx; sVSWGST := '0,'; sVSWInc := sSWEx; end; qryRef.Next; end; // while not qryRef.Eof do begin. // Get references for TVC (Victrack) and TEC (non-Victrack) users. qryRef.Close; qryRef.SQL.Strings[2] := 'WHERE Code LIKE ''TVC%'''; qryRef.Open; while not qryRef.Eof do begin sRefCode := qryRef.FieldByName('Code').AsString; if (sRefCode = 'TVC12') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; sTVC12Ex := Curr2Str(curValue)+','; sTVC12GST := '0,'; sTVC12Inc := sTVC12Ex; end else if (sRefCode = 'TVCor') then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT Cost'); qry.SQL.Add('FROM tRate'); qry.SQL.Add('WHERE ServiceTypeID = '+ IntToStr(qryRef.FieldByName('id').AsInteger)); qry.Open; if qry.Eof then curValue := 0 else curValue := curFormat(qry.FieldByName('cost').AsCurrency / 12); qry.Close; sTVCorEx := Curr2Str(curValue)+','; sTVCorGST := '0,'; sTVCorInc := sTVCorEx; end; qryRef.Next; end; // while not qryRef.Eof do begin. qryRef.Close; qryRef.Free; end else begin MessageDlg('Entries for RENT, CALLS must be added to '#13#10+ 'tSupplierServiceCode for VICTRACK before proceeding.', mtWarning, [mbOk], 0); end; end; // if qryTelmax.Eof then begin..else. Result := lSuccessful; end; // procedure GetReferences(). procedure GetCustomerID(const sLastServiceID:string;var sCustomerID:string); begin slCust[5] := 'WHERE tServiceID.ServiceID LIKE '''+ sLastServiceID +''''; qCustID.Close; qCustID.SQL := slCust; qCustID.Open; if qCustID.Eof or VarIsNull(qCustID['CustomerID']) then sCustomerID := '' else sCustomerID := IntToStr(qCustID.FieldByName('CustomerID').AsInteger); qCustID.Close; end; // GetCustomerID(). procedure 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().