ProgramID: 2402|Service Records procedure ServiceInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string; nBatchTypeID : integer); var DeleteFromDC( nBatch, EmptyList, lContinue); GetServiceCharges( nBatch, dStartDate, dEndDate, BatchDescription, nBatchTypeID, lContinue); end; // ServiceInvoice. procedure DeleteFromDC(const nThisBatch:LongInt; const FieldValues: TStringList; const lSuccess: boolean); Message 'Flushing tDataCollection data...'; DatabaseName := 'dbPPdata'; 'DELETE 'FROM tDataCollection 'WHERE BatchID = $nthisBatch end // DeleteFromDC procedure GetServiceCharges( const nBatchNumber : LongInt; const dStartDate, dEndDate : TDateTime; const BatchDescription : string; const nBatchTypeID : integer; var lReturnCode: Boolean ); var tblTemp.DatabaseName := 'dbPPdata'; tblTemp.TableName := 'tDC_TempRJC_ExService'; // Establish the query to be used to save the data read. qryServices.DatabaseName := 'dbPPdata'; // Ensure the temporary table does not exist. if tblTemp.Exists then begin tblTemp.DeleteTable; end; lReturnCode := True; MessageForm.Caption := 'Creating Service Charges ...'; MessageForm.Msg.Caption := 'Reading system variables ...'; // Define transaction types. // qryServices 'dbPPdata'; 'SELECT ID 'FROM tTransactionType // Service Records Invoice. 'WHERE Description LIKE '%Service Record%' 'AND Active = 1 if FOUND sTransactionID := 'id' end; // qryServices 'dbPPdata'; qryServices.SQL.Add // (Strings[1]) 'SELECT ID'); 'FROM tStatus'); 'AND Active = 1'); // In Service(=2), qryServices.Open; // Ex Service(=4). // build a string with the comma separated list of the FOUND tStatus.ID's sStatusID := '('; while not qryServices.Eof do begin if (Length(sStatusID) > 1) then sStatusID := sStatusID +','; sStatusID := sStatusID + 'id'; qryServices.Next; end; sStatusID := sStatusID +')'; // get ExServicveStausID // For amending charges later for "Ex Service" items... qryServices.SQL.Strings[2] := 'WHERE Status LIKE ''Ex Service'' qryServices.Open; // Ex Service(=4). if FOUNDthen begin sExServiceStatusID := 'id end; qryServices.Close; // get VRT tCustomer.ID // qryServices 'dbPPdata'; 'SELECT ID 'FROM tCustomer 'WHERE ShipTo LIKE ''VRT'' 'AND Active = 1 if NOTFOUND sVRTID := '0' // Define VRT CustomerID. else sVRTID := 'id' // get tSystem.ID for the passwd nBatchTypeID // qryServices 'dbPPdata'; 'SELECT ID 'FROM tSystem 'WHERE BatchTypeID = '+nBatchTypeID 'AND Active = 1 if NOTFOUND.Eof then sSystemID := '0' else sSystemID := 'ID').AsInteger); // This is the proc I previously sent // I will repeat a decoded version of this proc bellow for completeness // Define the maximum charges (ie rate) for ExService types by CustomerID. SetExServiceRates(sSystemID, sExServiceStatusID); // Save the invoicing data using a temporary file. MessageForm.Msg.Caption := 'Copying charges to a Temporary table ...'; MainForm.ProcessWinMessages; // Start temporary charges by tCustomer only in lieu of each service (2 of 4): // These charges account for sStatus = sExServiceStatusID (=2). // Start temporary charges by tCustomer only in lieu of each service (2 of 4): //set qryStringList to... 'SELECT IDENTITY(int,1,1) AS "SequNo", ServiceID, AmountExGST, GSTFlag, ' RateDescription, BatchID, FromDate, ToDate, TransactionTypeID, ' GSTAmount, AmountIncGST 'INTO tDC_TempRJC_ExService 'FROM (SELECT CID2.*, CAST((CID2.AmountExGST + CID2.GSTAmount) AS money) '+ 'AS "AmountIncGST" 'FROM (SELECT CID1.*, CAST((CASE WHEN CID1.GSTFlag LIKE ''Y'' '+ 'THEN (CID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount" 'FROM (SELECT (tCustomer.ShipTo + '' Services Pre July 1999'') '+ 'AS "ServiceID", (tCustomer.MaxExServiceAmount / 12) AS "AmountExGST", ' (CASE WHEN tCustomer.ShipTo LIKE ''VRT'' THEN ''N'' ELSE ''Y'' END) '+ 'AS "GSTFlag", ' ''Network Access'' AS "RateDescription", CAST('+sBatchID+' AS int) '+ 'AS "BatchID", ' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate", ' CAST('+sTransactionID+' AS int) AS "TransactionTypeID" 'FROM tCustomer 'WHERE tCustomer.MaxExServiceAmount > 0 'AND tCustomer.InvoiceServices = 1) AS CID1) AS CID2) AS CID //********************************************************************** //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' ** //********************************************************************** // qryServices 'dbPPdata'; qryServices.SQL.Text := qrystringList.Text; qryServices.ExecSQL; // Copies data into temp table. // count rows in temp table tDC_TempRJC_ExServicecreated above //set qryStringList to... 'SELECT COUNT(*) AS "Max" FROM tDC_TempRJC_ExService // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.Open; if NOTFOUND sSeqNoStart := '1'; else FOUND lNothingSaved := False; sSeqNoStart := Max // from select count()S above //set qryStringList to... 'INSERT INTO tDataCollection'); '(SequenceNo, BatchID, ServiceID, RateDescription,'); ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ ' FromDate, ToDate, TransactionTypeID)'); 'SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+ ' BatchID, ServiceID, RateDescription,'); ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID'); 'FROM tDC_TempRJC_ExService'); end; // qryStringList. // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; end; // Import data into tDataCollection. // Again, Ensure the temporary table does not exist. if tblTemp.Exists then begin // qryServices 'dbPPdata'; DROP TABLE tDC_TempRJC_ExService qryServices.ExecSQL; end; //set qryStringList to... // End temporary charges by tCustomer only in lieu of each service (2 of 4). // Start temporary charges by tCustomer only in lieu of each service (3 of 4). // 'SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, '+ // 'RateDescription, 'SELECT IDENTITY(int,'+sSeqNoStart+',1) AS "SequNo", BatchID, ServiceID, '+ 'RateDescription, // End temporary charges by tCustomer only in lieu of each service (3 of 4). ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, '+ 'ToDate, TransactionTypeID, ' Var01, Var02, Var03, ' Var04, Var05, Var06, Var07, Var08, Var09, Var10, ' Var11, Var12, Var13, Var14, Var15 'INTO tDC_TempRJC_ExService 'FROM (SELECT SID2.*, '+ 'CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST" 'FROM (SELECT SID1.*, ' CAST((CASE WHEN SID1.GSTFlag LIKE ''Y'' THEN '+ '(SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount" 'FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", '+ 'CAST('+sBatchID+' AS int) AS "BatchID", ' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate", ' CAST('+sTransactionID+' AS int) AS "TransactionTypeID", ' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+ 'SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) '+ 'AS money) AS "AmountExGST", ' (CASE WHEN tGroup.CustomerID = '+sVRTID+' THEN '+ '''N'' ELSE ''Y'' END) AS "GSTFlag", // Changes per Dec 2008 RLD: change Var orders to use generic report & avoid new. 'CONVERT(varchar(10), SI.InstallationDate, 103) AS "Var01",'); 'SI.Service AS "Var02", 'tStatus.Status AS "Var03", 'CONVERT(varchar(50), SI.ServiceCode) AS "Var04", 'tApplication.Description AS "Var05", ' CAST(SID.Cost AS varchar) AS "Var06", ' CAST(SID.Cost100 AS varchar) AS "Var07", '+ 'CAST(SID.Invoiced AS varchar) AS "Var08", ' CAST(SID.ServiceLen AS varchar) AS "Var09", ' SI.UpMDF AS "Var10", '+ 'SI.DownMDF AS "Var11", ' RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var12", '+ 'CAST(SI.StatusID AS varchar) AS "Var13", '+ 'SI.SACCnumber AS "Var14", '+ 'tServiceType.Code AS "Var15" //{ Changes per Dec 2008 RLD: change Var orders to use generic report & avoid new. // ' CAST(SID.Cost AS varchar) AS "Var01", // Add(' CAST(SID.Cost100 AS varchar) AS "Var02", '+ // 'CAST(SID.Invoiced AS varchar) AS "Var03", // Add(' CAST(SID.ServiceLen AS varchar) AS "Var04", // Add(' SI.UpMDF AS "Var05", SI.DownMDF AS "Var06", // Add(' RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var07", '+ // 'CAST(SI.StatusID AS varchar) AS "Var08", tStatus.Status AS "Var09", '+ // 'SI.SACCnumber AS "Var10" //} // End changes per dec 2008. 'FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost",'+ ' Max(tRate.Cost100) AS "Cost100", ' SI0.Length AS "ServiceLen", SI0.Invoiced 'FROM (SELECT SC.ID, SC.Rate ' FROM (SELECT SC1.ID, (CASE WHEN tDiscountedRate.Rate '+ 'IS NULL THEN 100.0 ELSE tDiscountedRate.Rate END) / 100.0 AS "Rate" ' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID, ' tCustomer.ID AS "CustomerID", (CASE ' WHEN ST.Frequency IS NULL THEN 1 ' WHEN ST.Frequency = 0 THEN 1 ' WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+ ' AS Real) / ST.Frequency) = ' ROUND((Month(tServiceID.InstallationDate)-'+sMonth+ ') / ST.Frequency,0,0) THEN 0 ' ELSE 1 ' END) AS "Period" ' FROM tServiceID ' LEFT JOIN tServiceType AS ST '+ 'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1)) ' LEFT JOIN tCentre '+ 'ON ((tServiceID.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1)) ' LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1)) ' LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1)) ' LEFT JOIN tCustomer '+ 'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1)) ' WHERE ST.SystemID = '+sSystemID); ' AND tServiceID.StatusID IN '+sStatusID); ' AND tServiceID.Active = 1 ' AND tCustomer.InvoiceServices = 1) AS SC1 'LEFT JOIN tDiscountedRate '+ 'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND ' (SC1.CustomerID = tDiscountedRate.CustomerID) '+ 'AND (tDiscountedRate.Active = 1)) 'WHERE SC1.Period = 0) AS SC) AS SI1 'INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID 'LEFT JOIN tRate '+ 'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+ 'AND (SI0.Length >= tRate.Length)) 'GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID 'INNER JOIN tServiceID AS SI ON SID.ID = SI.ID 'LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID // Changes per Dec 2008 RLD: Next line added to change Var orders // and to use generic report & avoid creating a new report. 'LEFT JOIN tApplication ON '+ '((SI.ApplicationID = tApplication.ID) AND (tApplication.Active = 1)) // End this part of change. 'LEFT JOIN tStatus ON SI.StatusID = tStatus.ID ' LEFT JOIN tCentre '+ 'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1)) ' LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1)) ' LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))) AS SID1 'WHERE SID1.AmountExGST IS NOT NULL) AS SID2) AS SIF end; // qryStringList. //********************************************************************** //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' ** //********************************************************************** // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; // Copies data into temp table. //set qryStringList to... 'SELECT BatchID 'FROM tDC_TempRJC_ExService // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.Open; if FOUND lNothingSaved := False; //set qryStringList to... 'INSERT INTO tDataCollection '(SequenceNo, BatchID, ServiceID, RateDescription, ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID, ' Var01, Var02, Var03, ' Var04, Var05, Var06, Var07, Var08, Var09, Var10, ' Var11, Var12, Var13, Var14, Var15) 'SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+ 'BatchID, ServiceID, RateDescription, ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID, ' Var01, Var02, Var03, ' Var04, Var05, Var06, Var07, Var08, Var09, Var10, ' Var11, Var12, Var13, Var14, Var15 'FROM tDC_TempRJC_ExService end; // qryStringList. // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; end; // if no data saved. MessageForm.Msg.Caption := 'Adding new services ...'; if tblTemp.Exists then begin // qryServices 'dbPPdata'; 'DROP TABLE tDC_TempRJC_ExService qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; // Drop the temporary table. end; // ************************************************************************* // ************************************************************************* // Now add any new services that have not yet been completed in tServiceID. //set qryStringList to... // Start temporary charges by tCustomer only in lieu of each service (4 of 4). // 'SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, '+ // 'RateDescription, 'SELECT IDENTITY(int,'+sSeqNoStart+',1) AS "SequNo", BatchID, ServiceID, '+ 'RateDescription, // End temporary charges by tCustomer only in lieu of each service (4 of 4). // End temporary charges by tCustomer only in lieu of each service (4 of 4). ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, '+ 'ToDate, TransactionTypeID, ' Var01, Var02, Var03, ' Var04, Var05, Var06, Var07, Var08, Var09, Var10, ' Var11, Var12, Var13, Var14, Var15 'INTO tDC_TempRJC_ExService 'FROM (SELECT SID2.*, '+ 'CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST" 'FROM (SELECT SID1.*, ' CAST((CASE WHEN SID1.GSTFlag LIKE ''Y'' THEN '+ '(SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount" 'FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", '+ 'CAST('+sBatchID+' AS int) AS "BatchID", ' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate", ' CAST('+sTransactionID+' AS int) AS "TransactionTypeID", ' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+ 'SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) '+ 'AS money) AS "AmountExGST", ' (CASE WHEN tGroup.CustomerID = '+sVRTID+' THEN '+ '''N'' ELSE ''Y'' END) AS "GSTFlag", 'CONVERT(varchar(10), SI.InstallationDate, 103) AS "Var01", 'SI.Service AS "Var02", 'tServiceType.Code AS "Var04", 'tApplication.Description AS "Var05", ' CAST(SID.Cost AS varchar) AS "Var06", ' CAST(SID.Cost100 AS varchar) AS "Var07", '+ 'CAST(SID.Invoiced AS varchar) AS "Var08", ' CAST(SID.ServiceLen AS varchar) AS "Var09", ' SI.UpMDF AS "Var10", '+ 'SI.DownMDF AS "Var11", ' RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var12", '+ 'CAST(SI.StatusID AS varchar) AS "Var13", '+ 'tStatus.Status AS "Var03", '+ 'SI.SACCnumber AS "Var14", '+ 'CAST(SID.Invoiced AS varchar) AS "Var15" //{ Changes per Dec 2008 RLD: change Var orders to use generic report & avoid new. // ' CAST(SID.Cost AS varchar) AS "Var01", // ' CAST(SID.Cost100 AS varchar) AS "Var02", '+ // 'CAST(SID.Invoiced AS varchar) AS "Var03", // ' CAST(SID.ServiceLen AS varchar) AS "Var04", // ' SI.UpMDF AS "Var05", SI.DownMDF AS "Var06", // ' RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var07", '+ // 'CAST(SI.StatusID AS varchar) AS "Var08", tStatus.Status AS "Var09", '+ // 'SI.SACCnumber AS "Var10" //} 'FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost",'+ ' Max(tRate.Cost100) AS "Cost100", ' SI0.Length AS "ServiceLen", SI0.Invoiced 'FROM (SELECT SC.ID, SC.Rate ' FROM (SELECT SC1.ID, (CASE WHEN tDiscountedRate.Rate '+ 'IS NULL THEN 100.0 ELSE tDiscountedRate.Rate END) / 100.0 AS "Rate" ' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID, ' tCustomer.ID AS "CustomerID", (CASE ' WHEN ST.Frequency IS NULL THEN 1 ' WHEN ST.Frequency = 0 THEN 1 ' WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+ ' AS Real) / ST.Frequency) = ' ROUND((Month(tServiceID.InstallationDate)-'+sMonth+ ') / ST.Frequency,0,0) THEN 0 ' ELSE 1 ' END) AS "Period" ' FROM tServiceID ' LEFT JOIN tServiceType AS ST '+ 'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1)) ' LEFT JOIN tCustomer '+ 'ON ((SUBSTRING(tServiceID.SACCnumber,1,3) = tCustomer.ShipTo) '+ 'AND (tCustomer.Active = 1)) ' WHERE ST.SystemID = '+sSystemID); ' AND tServiceID.StatusID IN '+sStatusID); ' AND tServiceID.Active = 1 ' AND ((tServiceID.SACCnumber IS NOT NULL) '+ 'AND (tServiceID.CustomerCentreID IS NULL)) ' AND tCustomer.InvoiceServices = 1) AS SC1 'LEFT JOIN tDiscountedRate '+ 'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND ' (SC1.CustomerID = tDiscountedRate.CustomerID) '+ 'AND (tDiscountedRate.Active = 1)) 'WHERE SC1.Period = 0) AS SC) AS SI1 'INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID 'LEFT JOIN tRate '+ 'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+ 'AND (SI0.Length >= tRate.Length)) 'GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID 'INNER JOIN tServiceID AS SI ON SID.ID = SI.ID 'LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID // Changes per Dec 2008 RLD: Next line added to change Var orders // and to use generic report & avoid creating a new report. 'LEFT JOIN tApplication ON '+ '((SI.ApplicationID = tApplication.ID) AND (tApplication.Active = 1)) // End this part of change. 'LEFT JOIN tStatus ON SI.StatusID = tStatus.ID 'LEFT JOIN tCentre '+ 'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1)) 'LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1)) 'LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))) AS SID1'+ ') AS SID2) AS SIF end; // qryStringList. //********************************************************************** //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' ** //********************************************************************** // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; // Copies data into temp table. //set qryStringList to... 'SELECT BatchID 'FROM tDC_TempRJC_ExService // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.Open; if NOT FOUND qryServices.Close; MessageDlg('No Services are available for invoicing.', mtWarning, [mbOk], 0); else FOUND //set qryStringList to... 'INSERT INTO tDataCollection '(SequenceNo, BatchID, ServiceID, RateDescription, ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID, ' Var01, Var02, Var03, ' Var04, Var05, Var06, Var07, Var08, Var09, Var10, ' Var11, Var12, Var13, Var14, Var15) 'SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+ 'BatchID, ServiceID, RateDescription, ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID, ' Var01, Var02, Var03, ' Var04, Var05, Var06, Var07, Var08, Var09, Var10, ' Var11, Var12, Var13, Var14, Var15 'FROM tDC_TempRJC_ExService MessageForm.Msg.Caption := 'Saving charges to tDataCollection ...'; // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; // Import data into tDataCollection. // Note tBatch does not display any amounts - do not calculate them. // Update the status of the batch. // (see below) UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); end; MessageForm.Msg.Caption := 'Cleaning up ...'; // NOTE: Do not delete temporary table as existence persists for the session // even if dropped or deleted. // It remains okay for the 1st run, but subsequent runs would otherwise // cause an error. RJC:090720. //{ // if tblTemp.Exists then begin // // qryServices 'dbPPdata'; // qryServices.SQL.Clear; // qryServices.SQL.'DROP TABLE tDC_TempRJC_ExService // qryServices.ExecSQL; // Drop the temporary table. // end; //} MessageForm.Msg.Caption := 'Updating Cost/Cost100 in tServiceID ...'; //set qryStringList to... 'UPDATE tServiceID'); 'SET Cost = CostAmt, Cost100 = Cost100Amt'); 'FROM (SELECT SI0.ID, Max(tRate.Cost) AS "CostAmt", '+ 'Max(tRate.Cost100) AS "Cost100Amt"'); 'FROM tServiceID AS SI0'); 'LEFT JOIN tServiceType '+ 'ON SI0.ServiceTypeID = tServiceType.ID'); 'LEFT JOIN tRate '+ 'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) AND (SI0.Length >= tRate.Length))'); 'WHERE tServiceType.SystemID = '+sSystemID); 'AND SI0.Active = 1'); 'GROUP BY SI0.ID) AS CostRate'); 'WHERE tServiceID.ID = CostRate.ID'); // qryServices 'dbPPdata'; qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; // Drop the temporary table. //**************************************************************** //**************************** END ******************************* //**************************************************************** end; // GetServiceCharges. procedure SetExServiceRates(const sSystemID, sExServiceStatusID: string); qInsert.DatabaseName := 'dbPPdata'; qUpdate.DatabaseName := 'dbPPdata'; // set qry string DatabaseName := 'dbPPdata'; 'SELECT ST1.ServiceTypeID, ST1.CustomerID, // SQL.Add('(100.0 * (CASE WHEN (ST1.Cost100 IS NULL) OR (ST1.Cost100 = 0) '+ // 'THEN 1 ELSE (ST1.ChargeAmount / ST1.Cost100) END)) AS "ExRate", '(100.0 * (CASE WHEN (ST1.Cost IS NULL) OR (ST1.Cost = 0) '+ 'THEN 1 ELSE (ST1.ChargeAmount / ST1.Cost) END)) AS "ExRate", '(CASE WHEN ST1.Rate IS NULL THEN 1 ELSE 0 END) AS "InsertNew" 'FROM (SELECT ST0.*, '(CASE WHEN ST0.MaxExServiceAmount IS NULL THEN ST0.AmountExGST ' WHEN (ST0.MaxExServiceAmount < ST0.AmountExGST) '+ 'THEN ST0.MaxExServiceAmount ' ELSE ST0.AmountExGST END) AS "ChargeAmount" 'FROM (SELECT ST.ServiceTypeID, ST.CustomerID, ST.Rate, '+ 'ST.MaxExServiceAmount, 'SUM(ST.AmountExGST) AS "AmountExGST", SUM(ST.Cost) AS "Cost", '+ 'SUM(ST.Cost100) AS "Cost100" 'FROM (SELECT SID.*, SI.ServiceID, SI.ServiceTypeID, '+ 'tGroup.CustomerID, tCustomer.MaxExServiceAmount, 'CAST((CASE WHEN SID.Invoiced IS NULL '+ 'THEN SID.Cost ELSE SID.Invoiced END) AS money) AS "AmountExGST" 'FROM ( SELECT SI0.ID, SI1.Rate, ' Max(CASE WHEN tRate.Cost IS NULL THEN 0 ELSE tRate.Cost END) '+ 'AS "Cost", ' Max(CASE WHEN tRate.Cost100 IS NULL THEN 0 ELSE tRate.Cost100 END) '+ 'AS "Cost100", ' SI0.Length AS "ServiceLen", SI0.Invoiced ' FROM (SELECT SC1.ID, tDiscountedRate.Rate ' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID, ' tCustomer.ID AS "CustomerID" ' FROM tServiceID ' LEFT JOIN tServiceType AS ST '+ 'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1)) ' LEFT JOIN tCentre '+ 'ON ((tServiceID.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1)) ' LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1)) ' LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1)) ' LEFT JOIN tCustomer '+ 'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1)) ' WHERE ST.SystemID = '+sSystemID ' AND tServiceID.StatusID = '+sExServiceStatusID ' AND tServiceID.Active = 1 ' AND tCustomer.InvoiceServices = 1) AS SC1 ' LEFT JOIN tDiscountedRate '+ 'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND ' (SC1.CustomerID = tDiscountedRate.CustomerID) '+ 'AND (tDiscountedRate.Active = 1))) AS SI1 'INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID 'LEFT JOIN tRate '+ 'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+ 'AND (SI0.Length >= tRate.Length)) 'GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID 'INNER JOIN tServiceID AS SI ON SID.ID = SI.ID 'LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID 'LEFT JOIN tCentre '+ 'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1)) 'LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1)) 'LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1)) 'LEFT JOIN tCustomer '+ 'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))) AS ST 'GROUP BY ST.ServiceTypeID, ST.CustomerID, ST.Rate, '+ 'ST.MaxExServiceAmount) AS ST0) AS ST1 while FOUND sServiceTypeID := 'ServiceTypeID sCustomerID := 'CustomerID fRate := 'ExRate AsFloat; if FieldByName('InsertNew').AsBoolean then InsertRate(sServiceTypeID, sCustomerID, fRate) else UpdateRate(sServiceTypeID, sCustomerID, fRate); Next; end; // not Eof. end; // qry. qry.Free; end; // SetExServiceRates. // from Rod // I don't think InsertRate does anything as EXeceSQL is notr called // and also I think there is an error in ithe sql anyhow // (proc unmolosted) procedure InsertRate(const sServiceTypeID, sCustomerID: string; const fRate: real); begin with qInsert do begin SQL.Clear; SQL.Add('INSERT INTO tDiscountedRate'); SQL.Add('(ServiceTypeID, CustomerID, Rate, Active)'); SQL.Add('VALUES ('+sServiceTypeID+','); SQL.Add('VALUES ('+sCustomerID+','); SQL.Add('VALUES ('+FloatToStrF(fRate, ffFixed, 4, 8)+', 1'); end; end; // InsertRate. procedure UpdateRate(const sServiceTypeID, sCustomerID: string; const fRate: real); begin //set qUpdate string 'UPDATE tDiscountedRate'); 'SET Rate = '+FloatToStrF(fRate, ffFixed, 4, 8)); 'WHERE ServiceTypeID = '+ sServiceTypeID); 'AND CustomerID = '+ sCustomerID); // qUpdate 'dbPPdata'; ExecSQL; end; // UpdateRate. UpdateBatchStatus(const nThisBatch, nStatus: LongInt); var begin nBatch := nThisBatch; nOutcome := nStatus; qryBA.DatabaseName := 'dbPPdata'; // set qryStringList 'SELECT BatchStatusID FROM tBatch 'WHERE (ID = '+ IntToStr(nBatch)+')); // qryBA. 'dbPPdata'; qryBA.SQL := qryStringList; qryBA.Open; 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. // set qryStringList 'UPDATE tBatch'); 'SET BatchStatusID = '+nOutcome 'FROM tBatch'); 'WHERE ID = '+nBatch // qryBA. 'dbPPdata'; qryBA.SQL := qryStringList; qryBA.ExecSQL; end; // if (qryDC['BatchStatusID'] <> nOutcome) then. // Update the tBatchStatusHistory table. // set qryStringList 'INSERT INTO tBatchStatusHistory'); ' (BatchID, BatchStatusID, StatusChangeDate,UserID)'); ' VALUES ('+IntToStr(nBatch)+','+IntToStr(nOutcome)+ ','''+StandardDateString(Now)+''','+IntToStr(MainForm.OPERATORNUMBER)+')') // qryBA. 'dbPPdata'; ; qryBA.SQL := qryStringList; qryBA.ExecSQL; end; // if qryDC.Eof and qryDC.Bof then. qryBA.Close; qryBA.Free; // Now update the list of viewable Batch numbers in tDataCollection // (in TabTransactionSummary). MainForm.SetBillingPeriodBatch; end; // UpdateBatchStatus().