/*

nakakalula.

pero maganda result ng query na to. parang format ng billing ng pldt.

*/

--select dbo.billstatementdate('2005-09-14')

CREATE FUNCTION BillingReport(@DueDateFrom DATETIME, @DueDateTo DATETIME)
RETURNS TABLE
AS


RETURN

SELECT TOP 100 PERCENT
a.*,
[xxx] = convert(varchar(1),''),
LastBillPayments.DatePaid, LastBillPayments.Penalty, LastBillPayments.OrNo, LastBillPayments.Discount, LastBillPayments.Payment, PaymentResult = LastBillPayments.MonthBal - LastBillPayments.ExcessPayment
FROM
(
SELECT
X.*,
BalanceFromLastBill = LastBillBalance.MonthBal - LastBillBalance.ExcessPayment,
LastPaymentSortOrder = LastBill.PaymentSortOrder,
LastBillStatementDate = LastBill.BillStatementDate,
LastDueDate = LastBill.DueDate,
LastPayImmediately = LastBill.PayImmediately
FROM
(
select CurrentBill.BranchCode, CurrentBill.InvoiceNo,
ai.Fullname,
ai.modelno,
ai.FullAddress,
ai.ZipCode,
CurrentBill.pmtstructureno,

NthStatement = dbo.NthWholeWord(CurrentBill.Nthm) + ' of ' + CONVERT(VARCHAR,aifh.TermsInMonths) + ' month terms',

MonthlyInstallment = (CASE WHEN CurrentBill.NthM <= aifh.TermsInMonths THEN aifh.MonthlyInstallment ELSE 0 END),



CurrentCharges = CurrentBill.Penalty + (CASE WHEN CurrentBill.NthM <= aifh.TermsInMonths THEN aifh.MonthlyInstallment ELSE 0 END),
CurrentPaymentSortOrder = CurrentBill.PaymentSortOrder,
CurrentNthM = CurrentBill.NthM,

CurrentBillStatementDate = CurrentBill.BillStatementDate,
CurrentPenalty = CurrentBill.Penalty,
CurrentPayImmediately = CurrentBill.PayImmediately,
CurrentDueToPay = dbo.ZeroIfNeg(CurrentBill.PayImmediately),
CurrentDueDate = CurrentBill.DueDate

from accountinstallmentfinancingdetailfinal AS CurrentBill
inner join accountinstallment as ai
on CurrentBill.invoiceno = ai.invoiceno
and CurrentBill.pmtstructureno = ai.activepaymentstructure

inner join accountinstallmentfinancingheader as aifh
on CurrentBill.invoiceno = aifh.invoiceno
and CurrentBill.pmtstructureno = aifh.pmtstructureno


-- include only duedate with no payment, if non null duedate that is in...
-- ...the last row, it means duedate has still no payment(s)
where
CurrentBill.DueDate between @DueDateFrom and @DueDateTo
-- and CurrentBill.invoiceno = '01-020130'
-- and CurrentBill.invoiceno = '01-020129'
-- and CurrentBill.invoiceno = '09-0107'


and exists
(
SELECT LastRow.InvoiceNo, LastRow.PmtStructureNo, MAX(PaymentSortOrder)
FROM AccountInstallmentFinancingDetailFinal AS LastRow
WHERE
CurrentBill.InvoiceNo = LastRow.InvoiceNo
AND CurrentBill.PmtStructureNo = LastRow.PmtStructureNo
GROUP BY LastRow.InvoiceNo, LastRow.PmtStructureNo
HAVING CurrentBill.PaymentSortOrder = MAX(LastRow.PaymentSortOrder)

)

) AS X
LEFT JOIN AccountInstallmentFinancingDetailFinal AS LastBill
ON LastBill.InvoiceNo = X.INvoiceNo
AND LastBill.PmtStructureNo = X.PmtStructureNo
AND LastBill.NthM = X.CurrentNthM - 1

LEFT JOIN AccountInstallmentFinancingDetailFinal AS LastBillBalance
ON LastBillBalance.InvoiceNo = X.INvoiceNo
AND LastBillBalance.PmtStructureNo = X.PmtStructureNo
AND LastBillBalance.PaymentSortOrder = X.CurrentPaymentSortOrder - 1
) AS A
LEFT JOIN AccountInstallmentFinancingDetailFinal as LastBillPayments
ON LastBillPayments.InvoiceNo = A.InvoiceNo
AND LastBillPayments.PmtStructureNo = A.PmtStructureNo
And LastBillPayments.PaymentSortorder BETWEEN A.LastPaymentSortOrder + 1 AND A.CurrentPaymentSortOrder - 1



ORDER BY LastBillPayments.InvoiceNo, LastBillPayments.PaymentSortOrder