----VVCMC
DECLARE @25FiscalYearID INT = 23 --2025-26
DECLARE @26FiscalYearID INT = 24 --2026-27
DECLARE @27FiscalYearID INT = 25
DECLARE @28FiscalYearID INT = 26
DECLARE @29FiscalYearID INT = 27
DECLARE @30FiscalYearID INT = 28

DECLARE @FY2627StartDate DATE;
DECLARE @FY2526StartDate DATE;
DECLARE @FY2526EndDate DATE;

SELECT @FY2627StartDate = dtStartDate
FROM M_FiscalYear
WHERE BiFiscalYearID = @26FiscalYearID;

SELECT
    @FY2526StartDate = dtStartDate,
    @FY2526EndDate   = dtEndDate
FROM M_FiscalYear
WHERE biFiscalYearID = @25FiscalYearID;


SELECT
    WD.biBudgetTypeID [Budget Type],d.vcdepartment AS [विभाग],b.vcbudgetheadcode + ' - ' + b.vcbudgethead AS [लेखा संकेतांक / लेखाशिर्ष],
    'FY-' + WF.vcFiscalYear AS [आर्थिक वर्ष],WD.biWorkDetailID AS [फाईल क्रमांक],WD.vcNameofWork AS [कामाचे नाव],
    convert(varchar,dtAccountsApprovalDate,103) [Accounts Approval],convert(varchar,dtProposalDate,103) [Prastav Date],

(SELECT top 1 dtpaymentdate
    FROM T_Payment Lpmt
    WHERE Lpmt.intIsActive = 1 AND dtpaymentdate < '2026-04-01' and Lpmt.biworkdetailid=wd.biworkdetailid order by dtpaymentdate desc)
 [Last Payment Date],

    ISNULL(WD.nuTendorCost,0) AS [कामाचे एकूण रक्कम],
    ISNULL(WD.nuTendorCost,0) - ISNULL(P.TotalPaidTillFY2627,0) AS [०१/०४/२०२६ पर्यंत एकूण कामाचे शिल्लक],
    ISNULL(WP.nuprovisionamt,0) AS [2025-26 साठी कामाची तरतूद],
    ISNULL(P25.PaidFY2526,0) AS [Paid in 2025-26],
    ISNULL(WP.nuprovisionamt,0) - ISNULL(P25.PaidFY2526,0) AS [2026-27 स्पिलव्हर],
    ISNULL(WP1.nuprovisionamt,0) AS [2026-27 साठी कामाची तरतूद],
    ISNULL(WP2.nuprovisionamt,0) AS [2027-28 साठी कामाची तरतूद],
    ISNULL(WP3.nuprovisionamt,0) AS [2028-29 साठी कामाची तरतूद],
    ISNULL(WP4.nuprovisionamt,0) AS [2029-30 साठी कामाची तरतूद]

FROM T_WorkDetail WD

LEFT JOIN M_department d ON d.bidepartmentid = WD.bidepartmentid
LEFT JOIN M_Budgethead b ON b.bibudgetheadid = WD.bibudgetheadid
LEFT JOIN M_FiscalYear WF ON WD.dtcreateddate BETWEEN WF.dtStartDate AND WF.dtEndDate

/* Total payment till FY 2026 start */
LEFT JOIN
(
    SELECT biWorkDetailID,SUM(ISNULL(nuamountpaid,0)) AS TotalPaidTillFY2627
    FROM T_Payment
    WHERE intIsActive = 1 AND dtpaymentdate < @FY2627StartDate
    GROUP BY biWorkDetailID
) P
ON P.biWorkDetailID = WD.biWorkDetailID

LEFT JOIN T_workprovision WP ON WP.biWorkDetailID = WD.biWorkDetailID
   AND WP.intIsActive = 1 AND WP.bifiscalyearid = @25FiscalYearID

/* FY 2025-26 payment */
LEFT JOIN
(
    SELECT biWorkDetailID,SUM(ISNULL(nuamountpaid,0)) AS PaidFY2526
    FROM T_Payment
    WHERE intIsActive = 1 AND dtpaymentdate BETWEEN @FY2526StartDate AND @FY2526EndDate
    GROUP BY biWorkDetailID
) P25
ON P25.biWorkDetailID = WD.biWorkDetailID

LEFT JOIN T_workprovision WP1 ON WP1.biWorkDetailID = WD.biWorkDetailID
   AND WP1.intIsActive = 1 AND WP1.bifiscalyearid = @26FiscalYearID

LEFT JOIN T_workprovision WP2 ON WP2.biWorkDetailID = WD.biWorkDetailID
   AND WP2.intIsActive = 1 AND WP2.bifiscalyearid = @27FiscalYearID

LEFT JOIN T_workprovision WP3 ON WP3.biWorkDetailID = WD.biWorkDetailID
   AND WP3.intIsActive = 1 AND WP3.bifiscalyearid = @28FiscalYearID

LEFT JOIN T_workprovision WP4 ON WP4.biWorkDetailID = WD.biWorkDetailID
   AND WP4.intIsActive = 1 AND WP4.bifiscalyearid = @29FiscalYearID



WHERE WD.dtcreateddate < @FY2627StartDate
  AND WD.intIsActive = 1 and WD.biWorkDetailID=67372

  AND NOT EXISTS
  (
      SELECT 1
      FROM T_Payment TP
      WHERE TP.biWorkDetailID = WD.biWorkDetailID
        AND TP.intIsActive = 1
        AND TP.vcpaymenttype IN ('Final Payment','Last Payment')
        AND TP.dtpaymentdate < @FY2627StartDate
  ) 

ORDER BY
    d.vcdepartment,
    b.vcbudgetheadcode,
    WF.vcFiscalYear;