procedure InsertRate(const sServiceTypeID, sCustomerID: string; const fRate: real); 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'); procedure UpdateRate(const sServiceTypeID, sCustomerID: string; const fRate: real); SQL.Add('UPDATE tDiscountedRate'); SQL.Add('SET Rate = '+FloatToStrF(fRate, ffFixed, 4, 8)); SQL.Add('WHERE ServiceTypeID = '+ sServiceTypeID); SQL.Add('AND CustomerID = '+ sCustomerID); procedure SetExServiceRates(const sSystemID, sExServiceStatusID: string); SQL.Add('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",'); SQL.Add('(100.0 * (CASE WHEN (ST1.Cost IS NULL) OR (ST1.Cost = 0) '+ 'THEN 1 ELSE (ST1.ChargeAmount / ST1.Cost) END)) AS "ExRate",'); SQL.Add('(CASE WHEN ST1.Rate IS NULL THEN 1 ELSE 0 END) AS "InsertNew"'); SQL.Add('FROM (SELECT ST0.*,'); SQL.Add('(CASE WHEN ST0.MaxExServiceAmount IS NULL THEN ST0.AmountExGST'); SQL.Add(' WHEN (ST0.MaxExServiceAmount < ST0.AmountExGST) '+ 'THEN ST0.MaxExServiceAmount'); SQL.Add(' ELSE ST0.AmountExGST END) AS "ChargeAmount"'); SQL.Add('FROM (SELECT ST.ServiceTypeID, ST.CustomerID, ST.Rate, '+ 'ST.MaxExServiceAmount,'); SQL.Add('SUM(ST.AmountExGST) AS "AmountExGST", SUM(ST.Cost) AS "Cost", '+ 'SUM(ST.Cost100) AS "Cost100"'); SQL.Add('FROM (SELECT SID.*, SI.ServiceID, SI.ServiceTypeID, '+ 'tGroup.CustomerID, tCustomer.MaxExServiceAmount,'); SQL.Add('CAST((CASE WHEN SID.Invoiced IS NULL '+ 'THEN SID.Cost ELSE SID.Invoiced END) AS money) AS "AmountExGST"'); SQL.Add('FROM ( SELECT SI0.ID, SI1.Rate,'); SQL.Add(' Max(CASE WHEN tRate.Cost IS NULL THEN 0 ELSE tRate.Cost END) '+ 'AS "Cost",'); SQL.Add(' Max(CASE WHEN tRate.Cost100 IS NULL THEN 0 ELSE tRate.Cost100 END) '+ 'AS "Cost100",'); SQL.Add(' SI0.Length AS "ServiceLen", SI0.Invoiced'); SQL.Add(' FROM (SELECT SC1.ID, tDiscountedRate.Rate'); SQL.Add(' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,'); SQL.Add(' tCustomer.ID AS "CustomerID"'); SQL.Add(' FROM tServiceID'); SQL.Add(' LEFT JOIN tServiceType AS ST '+ 'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))'); SQL.Add(' LEFT JOIN tCentre '+ 'ON ((tServiceID.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))'); SQL.Add(' LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))'); SQL.Add(' LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))'); SQL.Add(' LEFT JOIN tCustomer '+ 'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))'); SQL.Add(' WHERE ST.SystemID = '+sSystemID); SQL.Add(' AND tServiceID.StatusID = '+sExServiceStatusID); SQL.Add(' AND tServiceID.Active = 1'); SQL.Add(' AND tCustomer.InvoiceServices = 1) AS SC1'); SQL.Add(' LEFT JOIN tDiscountedRate '+ 'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND'); SQL.Add(' (SC1.CustomerID = tDiscountedRate.CustomerID) '+ 'AND (tDiscountedRate.Active = 1))) AS SI1'); SQL.Add('INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID'); SQL.Add('LEFT JOIN tRate '+ 'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+ 'AND (SI0.Length >= tRate.Length) '+ 'AND (tRate.Active = 1))'); SQL.Add('GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID'); SQL.Add('INNER JOIN tServiceID AS SI ON SID.ID = SI.ID'); SQL.Add('LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID'); SQL.Add('LEFT JOIN tCentre '+ 'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))'); SQL.Add('LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))'); SQL.Add('LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))'); SQL.Add('LEFT JOIN tCustomer '+ 'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))) AS ST'); SQL.Add('GROUP BY ST.ServiceTypeID, ST.CustomerID, ST.Rate, '+ 'ST.MaxExServiceAmount) AS ST0) AS ST1'); # ... while not Eof do begin sServiceTypeID := IntToStr(FieldByName('ServiceTypeID').AsInteger); sCustomerID := IntToStr(FieldByName('CustomerID').AsInteger); fRate := FieldByName('ExRate').AsFloat; if FieldByName('InsertNew').AsBoolean then InsertRate(sServiceTypeID, sCustomerID, fRate) else UpdateRate(sServiceTypeID, sCustomerID, fRate); Next; end; // not Eof. procedure GetServiceCharges( MessageForm.Caption := 'Creating Service Charges ...'; MessageForm.Msg.Caption := 'Reading system variables ...'; qryServices.SQL.Add('SELECT ID'); qryServices.SQL.Add('FROM tTransactionType'); // Service Records Invoice. qryServices.SQL.Add('WHERE Description LIKE ''%Service Record%'''); qryServices.SQL.Add('AND Active = 1'); qryServices.Open; if not qryServices.Eof then sTransactionID := IntToStr(qryServices['id']); qryServices.Close; qryServices.SQL.Clear; // Define status types. qryServices.SQL.Add('SELECT ID'); qryServices.SQL.Add('FROM tStatus'); // qryServices.SQL.Add('WHERE Status LIKE ''%Service'''); // Two entries: // Start temporary charges by tCustomer only (1 of 4): // Start temporary charges by tCustomer only (1 of 4): qryServices.SQL.Add('WHERE Status LIKE ''In Service'''); // One entry: // The above makes In Service detailed billing - Ex Service temp by Customer only. // End temporary charges by tCustomer only (1 of 4): // End temporary charges by tCustomer only (1 of 4): qryServices.SQL.Add('AND Active = 1'); // In Service(=2), qryServices.Open; // Ex Service(=4). sStatusID := '('; # ... while not qryServices.Eof do begin if (Length(sStatusID) > 1) then sStatusID := sStatusID +','; sStatusID := sStatusID + IntToStr(qryServices.FieldByName('id').AsInteger); qryServices.Next; end; // For amending charges later for "Ex Service" items... qryServices.SQL.Strings[2] := 'WHERE Status LIKE ''Ex Service'''; qryServices.Open; // Ex Service(=4). if not qryServices.Eof then begin sExServiceStatusID := IntToStr(qryServices.FieldByName('id').AsInteger); end; qryServices.Close; qryServices.SQL.Add('SELECT ID'); qryServices.SQL.Add('FROM tCustomer'); qryServices.SQL.Add('WHERE ShipTo LIKE ''VRT'''); qryServices.SQL.Add('AND Active = 1'); qryServices.Open; if qryServices.Eof then sVRTID := '0' // Define VRT CustomerID. else sVRTID := IntToStr(qryServices.FieldByName('id').AsInteger); qryServices.SQL.Add('SELECT ID'); qryServices.SQL.Add('FROM tSystem'); qryServices.SQL.Add('WHERE BatchTypeID = '+IntToStr(nBatchTypeID)); qryServices.SQL.Add('AND Active = 1'); qryServices.Open; if qryServices.Eof then sSystemID := '0' else sSystemID := IntToStr(qryServices.FieldByName('ID').AsInteger); // 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; with qryStringList do begin Clear; // 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): Add('SELECT IDENTITY(int,1,1) AS "SequNo", ServiceID, AmountExGST, GSTFlag,'); Add(' RateDescription, BatchID, FromDate, ToDate, TransactionTypeID,'); Add(' GSTAmount, AmountIncGST'); Add('INTO tDC_TempRJC_ExService'); Add('FROM (SELECT CID2.*, CAST((CID2.AmountExGST + CID2.GSTAmount) AS money) '+ 'AS "AmountIncGST"'); Add('FROM (SELECT CID1.*, CAST((CASE WHEN CID1.GSTFlag LIKE ''Y'' '+ 'THEN (CID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"'); Add('FROM (SELECT (tCustomer.ShipTo + '' Services Pre July 1999'') '+ 'AS "ServiceID", (tCustomer.MaxExServiceAmount / 12) AS "AmountExGST",'); Add(' (CASE WHEN tCustomer.ShipTo LIKE ''VRT'' THEN ''N'' ELSE ''Y'' END) '+ 'AS "GSTFlag",'); Add(' ''Network Access'' AS "RateDescription", CAST('+sBatchID+' AS int) '+ 'AS "BatchID",'); Add(' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",'); Add(' CAST('+sTransactionID+' AS int) AS "TransactionTypeID"'); Add('FROM tCustomer'); Add('WHERE tCustomer.MaxExServiceAmount > 0'); Add('AND tCustomer.InvoiceServices = 1) AS CID1) AS CID2) AS CID'); //********************************************************************** //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' ** //********************************************************************** end; // qryStringList. qryServices.SQL.Text := qrystringList.Text; qryServices.ExecSQL; // Copies data into temp table. with qryStringList do begin Clear; Add('SELECT COUNT(*) AS "Max"'); Add('FROM tDC_TempRJC_ExService'); end; // qryStringList. qryServices.SQL.Text := qryStringList.Text; qryServices.Open; if qryServices.Bof and qryServices.Eof then begin qryServices.Close; sSeqNoStart := '1'; end else begin lNothingSaved := False; sSeqNoStart := IntToStr(qryServices.FieldByName('Max').AsInteger + 1); qryServices.Close; with qryStringList do begin Clear; Add('INSERT INTO tDataCollection'); Add('(SequenceNo, BatchID, ServiceID, RateDescription,'); Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID)'); Add('SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+ 'BatchID, ServiceID, RateDescription,'); Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID'); Add('FROM tDC_TempRJC_ExService'); end; // qryStringList. 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.SQL.Clear; qryServices.SQL.Add('DROP TABLE tDC_TempRJC_ExService'); qryServices.ExecSQL; end; with qryStringList do begin Clear; // End temporary charges by tCustomer only in lieu of each service (2 of 4). // 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). // Start temporary charges by tCustomer only in lieu of each service (3 of 4). // Add('SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, '+ // 'RateDescription,'); Add('SELECT IDENTITY(int,'+sSeqNoStart+',1) AS "SequNo", BatchID, ServiceID, '+ 'RateDescription,'); // End temporary charges by tCustomer only in lieu of each service (3 of 4). // End temporary charges by tCustomer only in lieu of each service (3 of 4). Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, '+ 'ToDate, TransactionTypeID,'); Add(' Var01, Var02, Var03,'); Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,'); Add(' Var11, Var12, Var13, Var14, Var15'); Add('INTO tDC_TempRJC_ExService'); Add('FROM (SELECT SID2.*, '+ 'CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"'); Add('FROM (SELECT SID1.*,'); Add(' CAST((CASE WHEN SID1.GSTFlag LIKE ''Y'' THEN '+ '(SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"'); Add('FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", '+ 'CAST('+sBatchID+' AS int) AS "BatchID",'); Add(' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",'); Add(' CAST('+sTransactionID+' AS int) AS "TransactionTypeID",'); // Add(' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+ // 'SID.Cost ELSE SID.Invoiced END)/12*tServiceType.Frequency) AS money) '+ // 'AS "AmountExGST",'); Add(' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+ 'SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) '+ 'AS money) AS "AmountExGST",'); Add(' (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. Add('CONVERT(varchar(10), SI.InstallationDate, 103) AS "Var01",'); Add('SI.Service AS "Var02",'); Add('tStatus.Status AS "Var03",'); Add('CONVERT(varchar(50), SI.ServiceCode) AS "Var04",'); Add('tApplication.Description AS "Var05",'); Add(' CAST(SID.Cost AS varchar) AS "Var06",'); Add(' CAST(SID.Cost100 AS varchar) AS "Var07", '+ 'CAST(SID.Invoiced AS varchar) AS "Var08",'); Add(' CAST(SID.ServiceLen AS varchar) AS "Var09",'); Add(' SI.UpMDF AS "Var10", '+ 'SI.DownMDF AS "Var11",'); Add(' 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. Add(' 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. Add('FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost",'+ ' Max(tRate.Cost100) AS "Cost100",'); Add(' SI0.Length AS "ServiceLen", SI0.Invoiced'); Add('FROM (SELECT SC.ID, SC.Rate'); Add(' FROM (SELECT SC1.ID, (CASE WHEN tDiscountedRate.Rate '+ 'IS NULL THEN 100.0 ELSE tDiscountedRate.Rate END) / 100.0 AS "Rate"'); Add(' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,'); Add(' tCustomer.ID AS "CustomerID", (CASE'); Add(' WHEN ST.Frequency IS NULL THEN 1'); Add(' WHEN ST.Frequency = 0 THEN 1'); Add(' WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+ ' AS Real) / ST.Frequency) ='); Add(' ROUND((Month(tServiceID.InstallationDate)-'+sMonth+ ') / ST.Frequency,0,0) THEN 0'); Add(' ELSE 1'); Add(' END) AS "Period"'); Add(' FROM tServiceID'); Add(' LEFT JOIN tServiceType AS ST '+ 'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))'); Add(' LEFT JOIN tCentre '+ 'ON ((tServiceID.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))'); Add(' LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))'); Add(' LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))'); Add(' LEFT JOIN tCustomer '+ 'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))'); Add(' WHERE ST.SystemID = '+sSystemID); Add(' AND tServiceID.StatusID IN '+sStatusID); Add(' AND tServiceID.Active = 1'); Add(' AND tCustomer.InvoiceServices = 1) AS SC1'); Add('LEFT JOIN tDiscountedRate '+ 'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND'); Add(' (SC1.CustomerID = tDiscountedRate.CustomerID) '+ 'AND (tDiscountedRate.Active = 1))'); Add('WHERE SC1.Period = 0) AS SC) AS SI1'); Add('INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID'); Add('LEFT JOIN tRate '+ 'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+ 'AND (SI0.Length >= tRate.Length) '+ 'AND (tRate.Active = 1))'); Add('GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID'); Add('INNER JOIN tServiceID AS SI ON SID.ID = SI.ID'); Add('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. Add('LEFT JOIN tApplication ON '+ '((SI.ApplicationID = tApplication.ID) AND (tApplication.Active = 1))'); // End this part of change. Add('LEFT JOIN tStatus ON SI.StatusID = tStatus.ID'); Add(' LEFT JOIN tCentre '+ 'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))'); Add(' LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))'); Add(' LEFT JOIN tGroup '+ 'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))) AS SID1'); Add('WHERE SID1.AmountExGST IS NOT NULL) AS SID2) AS SIF'); end; // qryStringList. //********************************************************************** //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' ** //********************************************************************** qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; // Copies data into temp table. with qryStringList do begin Clear; Add('SELECT BatchID'); Add('FROM tDC_TempRJC_ExService'); end; // qryStringList. qryServices.SQL.Text := qryStringList.Text; qryServices.Open; if qryServices.Bof and qryServices.Eof then begin qryServices.Close; end else begin lNothingSaved := False; qryServices.Close; with qryStringList do begin Clear; Add('INSERT INTO tDataCollection'); Add('(SequenceNo, BatchID, ServiceID, RateDescription,'); Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID,'); Add(' Var01, Var02, Var03,'); Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,'); Add(' Var11, Var12, Var13, Var14, Var15)'); Add('SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+ 'BatchID, ServiceID, RateDescription,'); Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID,'); Add(' Var01, Var02, Var03,'); Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,'); Add(' Var11, Var12, Var13, Var14, Var15'); Add('FROM tDC_TempRJC_ExService'); end; // qryStringList. qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; end; // if no data saved. MessageForm.Msg.Caption := 'Adding new services ...'; MainForm.ProcessWinMessages; qryStringList.Clear; if tblTemp.Exists then begin qryStringList.Add('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. with qryStringList do begin Clear; // Start temporary charges by tCustomer only in lieu of each service (4 of 4). // Start temporary charges by tCustomer only in lieu of each service (4 of 4). // Add('SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, '+ // 'RateDescription,'); Add('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). Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, '+ 'ToDate, TransactionTypeID,'); Add(' Var01, Var02, Var03,'); Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,'); Add(' Var11, Var12, Var13, Var14, Var15'); Add('INTO tDC_TempRJC_ExService'); Add('FROM (SELECT SID2.*, '+ 'CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"'); Add('FROM (SELECT SID1.*,'); Add(' CAST((CASE WHEN SID1.GSTFlag LIKE ''Y'' THEN '+ '(SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"'); Add('FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", '+ 'CAST('+sBatchID+' AS int) AS "BatchID",'); Add(' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",'); Add(' CAST('+sTransactionID+' AS int) AS "TransactionTypeID",'); Add(' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+ 'SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) '+ 'AS money) AS "AmountExGST",'); Add(' (CASE WHEN tGroup.CustomerID = '+sVRTID+' THEN '+ '''N'' ELSE ''Y'' END) AS "GSTFlag",'); Add('CONVERT(varchar(10), SI.InstallationDate, 103) AS "Var01",'); Add('SI.Service AS "Var02",'); Add('tServiceType.Code AS "Var04",'); Add('tApplication.Description AS "Var05",'); Add(' CAST(SID.Cost AS varchar) AS "Var06",'); Add(' CAST(SID.Cost100 AS varchar) AS "Var07", '+ 'CAST(SID.Invoiced AS varchar) AS "Var08",'); Add(' CAST(SID.ServiceLen AS varchar) AS "Var09",'); Add(' SI.UpMDF AS "Var10", '+ 'SI.DownMDF AS "Var11",'); Add(' 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. Add(' 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"'); } Add('FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost",'+ ' Max(tRate.Cost100) AS "Cost100",'); Add(' SI0.Length AS "ServiceLen", SI0.Invoiced'); Add('FROM (SELECT SC.ID, SC.Rate'); Add(' FROM (SELECT SC1.ID, (CASE WHEN tDiscountedRate.Rate '+ 'IS NULL THEN 100.0 ELSE tDiscountedRate.Rate END) / 100.0 AS "Rate"'); Add(' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,'); // Add(' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID, '+ // 'SUBSTRING(tServiceID.SACCnumber,1,3) AS "SACCShipTo",'); Add(' tCustomer.ID AS "CustomerID", (CASE'); Add(' WHEN ST.Frequency IS NULL THEN 1'); Add(' WHEN ST.Frequency = 0 THEN 1'); Add(' WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+ ' AS Real) / ST.Frequency) ='); Add(' ROUND((Month(tServiceID.InstallationDate)-'+sMonth+ ') / ST.Frequency,0,0) THEN 0'); Add(' ELSE 1'); Add(' END) AS "Period"'); Add(' FROM tServiceID'); Add(' LEFT JOIN tServiceType AS ST '+ 'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))'); Add(' LEFT JOIN tCustomer '+ 'ON ((SUBSTRING(tServiceID.SACCnumber,1,3) = tCustomer.ShipTo) '+ 'AND (tCustomer.Active = 1))'); Add(' WHERE ST.SystemID = '+sSystemID); Add(' AND tServiceID.StatusID IN '+sStatusID); Add(' AND tServiceID.Active = 1'); Add(' AND ((tServiceID.SACCnumber IS NOT NULL) '+ 'AND (tServiceID.CustomerCentreID IS NULL))'); Add(' AND tCustomer.InvoiceServices = 1) AS SC1'); Add('LEFT JOIN tDiscountedRate '+ 'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND'); Add(' (SC1.CustomerID = tDiscountedRate.CustomerID) '+ 'AND (tDiscountedRate.Active = 1))'); Add('WHERE SC1.Period = 0) AS SC) AS SI1'); Add('INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID'); Add('LEFT JOIN tRate '+ 'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+ 'AND (SI0.Length >= tRate.Length))'); Add('GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID'); Add('INNER JOIN tServiceID AS SI ON SID.ID = SI.ID'); Add('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. Add('LEFT JOIN tApplication ON '+ '((SI.ApplicationID = tApplication.ID) AND (tApplication.Active = 1))'); // End this part of change. Add('LEFT JOIN tStatus ON SI.StatusID = tStatus.ID'); Add('LEFT JOIN tCentre '+ 'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))'); Add('LEFT JOIN tDepartment '+ 'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))'); Add('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.SQL.Text := qryStringList.Text; qryServices.ExecSQL; // Copies data into temp table. with qryStringList do begin Clear; Add('SELECT BatchID'); Add('FROM tDC_TempRJC_ExService'); end; // qryStringList. qryServices.SQL.Text := qryStringList.Text; qryServices.Open; if qryServices.Bof and qryServices.Eof and lNothingSaved then begin qryServices.Close; MessageDlg('No Services are available for invoicing.', mtWarning, [mbOk], 0); end else begin qryServices.Close; with qryStringList do begin Clear; Add('INSERT INTO tDataCollection'); Add('(SequenceNo, BatchID, ServiceID, RateDescription,'); Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID,'); Add(' Var01, Var02, Var03,'); Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,'); Add(' Var11, Var12, Var13, Var14, Var15)'); Add('SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+ 'BatchID, ServiceID, RateDescription,'); Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+ 'FromDate, ToDate, TransactionTypeID,'); Add(' Var01, Var02, Var03,'); Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,'); Add(' Var11, Var12, Var13, Var14, Var15'); Add('FROM tDC_TempRJC_ExService'); end; // qryStringList. MessageForm.Msg.Caption := 'Saving charges to tDataCollection ...'; MainForm.ProcessWinMessages; 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. UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); end; // if no data saved. MessageForm.Msg.Caption := 'Cleaning up ...'; MainForm.ProcessWinMessages; qryStringList.Clear; // 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.SQL.Clear; qryServices.SQL.Add('DROP TABLE tDC_TempRJC_ExService'); qryServices.ExecSQL; // Drop the temporary table. end; } MessageForm.Msg.Caption := 'Updating Cost/Cost100 in tServiceID ...'; MessageForm.Show; qryStringList.Clear; qryStringList.Add('UPDATE tServiceID'); qryStringList.Add('SET Cost = CostAmt, Cost100 = Cost100Amt'); qryStringList.Add('FROM (SELECT SI0.ID, Max(tRate.Cost) AS "CostAmt", '+ 'Max(tRate.Cost100) AS "Cost100Amt"'); qryStringList.Add('FROM tServiceID AS SI0'); qryStringList.Add('LEFT JOIN tServiceType '+ 'ON SI0.ServiceTypeID = tServiceType.ID'); qryStringList.Add('LEFT JOIN tRate '+ 'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) AND (SI0.Length >= tRate.Length))'); qryStringList.Add('WHERE tServiceType.SystemID = '+sSystemID); qryStringList.Add('AND SI0.Active = 1'); qryStringList.Add('GROUP BY SI0.ID) AS CostRate'); qryStringList.Add('WHERE tServiceID.ID = CostRate.ID'); qryServices.SQL.Text := qryStringList.Text; qryServices.ExecSQL; // Drop the temporary table. //**************************************************************** //**************************** END ******************************* //**************************************************************** tblTemp.Free; qryStringList.Free; qryServices.Close; qryServices.Free; MessageForm.Close; MessageForm.Free; end; // GetServiceCharges. procedure ServiceInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime; const BatchDescription : string; nBatchTypeID : integer); var lContinue : Boolean; EmptyList : TStringList; begin EmptyList := TStringList.Create; // Removes all entries for the batch. lContinue := True; DeleteFromDC( nBatch, EmptyList, lContinue); EmptyList.Free; GetServiceCharges( nBatch, dStartDate, dEndDate, BatchDescription, nBatchTypeID, lContinue); end; // ServiceInvoice. end. // Services.