// Code for updating "Ex Service" services within Capped charges.

// A. Detail all services.

// 1. Totals by CustomerID
SELECT ES1.*
FROM (SELECT ES.CustomerID, Sum(ES.MaxExServiceAmount) AS "MaxExServiceAmount"
FROM (SELECT tServiceID.ServiceTypeID, tGroup.CustomerID, tCustomer.MaxExServiceAmount
FROM tServiceID
INNER 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
WHERE ST.SystemID = 53
AND tServiceID.Active = 1
AND tServiceID.StatusID = 4) AS ES
WHERE ES.CustomerID IS NOT NULL
GROUP BY ES.CustomerID) AS ES1
WHERE ES1.MaxExServiceAmount IS NOT NULL
AND ES1.MaxExServiceAmount > 0

// 2. Totals plus servicetype.


SELECT ES1.CustomerID, (CASE WHEN ES1.Rate > 66 THEN 66 ELSE Es1.Rate END) AS "Rate


SELECT ES2.ServiceTypeID, ES2.CustomerID, (ES2.MaxExServiceAmount / tCustomer. * 100) AS "Rate"

FROM (SELECT ES1.*
FROM (

SELECT ES.ServiceTypeID, ES.CustomerID, 

FROM (SELECT tServiceID.ServiceTypeID, tGroup.CustomerID
FROM tServiceID
INNER 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
WHERE ST.SystemID = 53
AND tServiceID.Active = 1
AND tServiceID.StatusID = 4) AS ES

WHERE ES.CustomerID IS NOT NULL
GROUP BY ES.CustomerID

) AS ES1
WHERE ES1.MaxExServiceAmount IS NOT NULL
AND ES1.MaxExServiceAmount > 0) AS ES2
LEFT JOIN tCustomer ON ES2.CustomerID = tCustomer.ID
WHERE tCustomer.ShipTo IS NOT NULL










// 2. Totals plus calculations.


SELECT SID.*, SI.ServiceID, 
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(tRate.Cost) AS "Cost", Max(tRate.Cost100) 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 = 53
  AND tServiceID.StatusID = 4
  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))





























// 3. Totals plus calculations.
// 3A.Final:
SELECT ST1.ServiceTypeID, ST1.CustomerID,
(100.0 * (CASE WHEN (ST1.Cost100 IS NULL) OR (ST1.Cost100 = 0) THEN 1 ELSE (ST1.ChargeAmount / ST1.Cost100) END)) AS "ExRate",
(CASE WHEN ST1.Rate IS NULL THEN 1 ELSE 0 END) AS "Insert"

// 3B.Develpoment:
SELECT ST1.*,
(100.0 * (CASE WHEN (ST1.Cost100 IS NULL) OR (ST1.Cost100 = 0) THEN 1 ELSE (ST1.ChargeAmount / ST1.Cost100) END)) AS "ExRate",
(CASE WHEN ST1.Rate IS NULL THEN 1 ELSE 0 END) AS "Insert"
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 = 53
  AND tServiceID.StatusID = 4
  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













// X. The SQL for saving the charges for Cable Services.

SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, RateDescription,
 AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID,
 Var01, Var02, Var03,
 Var04, Var05, Var06, Var07, Var08, Var09, Var10
INTO tDC_Temprjc2006
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",
 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 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
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


// B. No Details, just one for each customer.

// 1. Totals by CustomerID - starters using Manual().

// ***********************************************************************************************

    // Include values.
    sTemp := 'ServiceID,GSTFlag,AmountExGST,GSTAmount,AmountIncGST,FromDate,ToDate';
    qryStringList := TStringList.Create;
    qryStringList.Add('INSERT INTO tDataCollection');
    qryStringList.Add('(BatchID,TransactionTypeID,'+ sTemp +')');
    // Include values.
    if (edtGST.Value = 0) then sGSTflag := 'N' else sGSTflag := 'Y';
    sTemp := ''''+ cbServiceID.Text +''','''+ sGSTflag +''','+
      CurrToStr(edtExGST.Value) +','+ CurrToStr(edtGST.Value) +','+
      CurrToStr(edtIncGST.Value) +',';
    if (Copy(Trim(edtFrom.Text),1,1) = '/') then
      sTemp := sTemp + 'NULL,'
    else
      sTemp := sTemp + ''''+ FormattedDateString(edtFrom.Date) +''',';
    if (Copy(Trim(edtTo.Text),1,1) = '/') then
      sTemp := sTemp + 'NULL'
    else
      sTemp := sTemp + ''''+ FormattedDateString(edtTo.Date) +'''';
    if lAddRateDesc then sTemp := sTemp + ','''+ sRateDesc +'''';
    if lAddReference then sTemp := sTemp + ','''+ sReference +'''';
    qryStringList.Add('VALUES ('+sBatchID +','+ sTransactionID +','+sTemp+')');
    with qry do begin
      SQL.Text := qryStringList.Text;
      ExecSQL;
      Close;
    end;  // qry.
    qryStringList.Free;

// ***********************************************************************************************







// 1. Totals by CustomerID

SELECT IDENTITY(int,1,1) AS "SequNo", ServiceID, AmountExGST, GSTFlag,
 RateDescription, BatchID, FromDate, ToDate, TransactionTypeID,
 GSTAmount, AmountIncGST
INTO tDC_Temprjc2006
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", (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


// ********************** START TEST START TEST START TEST ***************************

SELECT IDENTITY(int,1,1) AS "SequNo", ServiceID, AmountExGST, GSTFlag,
 RateDescription, BatchID, FromDate, ToDate, TransactionTypeID,
 GSTAmount, AmountIncGST
INTO tDC_Temprjc2006
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 * 0.1) ELSE 0 END) AS money) AS "GSTAmount"
FROM (

SELECT (tCustomer.ShipTo + ' Services Pre July 1999') AS "ServiceID", (MaxExServiceAmount / 12) AS "AmountExGST",
 (CASE WHEN tCustomer.ShipTo LIKE 'VRT' THEN 'N' ELSE 'Y' END) AS "GSTFlag",
 'Network Access' AS "RateDescription", CAST(11111 AS int) AS "BatchID",
 ('20080601') AS "FromDate", ('20080631') AS "ToDate",
 CAST(21 AS int) AS "TransactionTypeID"
FROM tCustomer
WHERE tCustomer.MaxExServiceAmount > 0
AND tCustomer.InvoiceServices = 1

) AS CID1

) AS CID2
) AS CID

// ********************** END TEST END TEST END TEST ***************************