USP_DATALIST_RECONCILIATION

CREATE procedure dbo.USP_DATALIST_RECONCILIATION
as
    set nocount on;

    declare @OPENSTATUS nvarchar(20);
    select @OPENSTATUS = [dbo].[UFN_RECONCILIATION_STATUSCODE_GETDESCRIPTION](0);

    select RECONCILIATIONLIST.ID, 
        RECONCILIATION.APPUSERID as APPUSERID,
        RECONCILIATION.STATUSCODE,
        dbo.UFN_APPUSER_GETNAME([APPUSER].[ID]) as [USERNAME],
        RECONCILIATION.RECONCILIATIONDATE as RECONCILIATIONDATE,
        RECONCILIATION.[STATUS],
        isnull([0], 0) as CASHAMOUNTDIFFERENCE, 
        isnull([1], 0) as CHECKCOUNTDIFFERENCE, 
        isnull([2], 0) as CREDITCARDCOUNTDIFFERENCE, 
        isnull([10], 0) as OTHERCOUNTDIFFERENCE,
        dbo.UFN_APPUSER_GETNAME(RECONCILIATION.APPROVEDBYID) as APPROVEDBYNAME,
        RECONCILIATION.APPROVALDATE,
        RECONCILIATION.ORIGINALSUBMISSIONDATE,
        RECONCILIATIONLIST.CHECKDIFFERENCE,
        RECONCILIATIONLIST.TOTALOTHERAMOUNT
        ,RECONCILIATIONLIST.[CASHCOUNT]
        ,RECONCILIATIONLIST.[CHECKCOUNT]
    from
    (
        select DIFFERENCETABLE.ID
            ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 0 then DIFFERENCETABLE.DIFFERENCE end) as [0]
            ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 1 then DIFFERENCETABLE.DIFFERENCE end) as [1]
            ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 2 then DIFFERENCETABLE.DIFFERENCE end) as [2]
            ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 10 then DIFFERENCETABLE.DIFFERENCE end) as [10]
            ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 1 then DIFFERENCETABLE.OTHERAMOUNTS end) as [CHECKDIFFERENCE]
            ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE in (2, 10) then DIFFERENCETABLE.OTHERAMOUNTS end) as [TOTALOTHERAMOUNT]
            ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 0 then DIFFERENCETABLE.[COUNTS] end) as [CASHCOUNT]
            ,SUM(case when DIFFERENCETABLE.PAYMENTMETHODCODE = 1 then DIFFERENCETABLE.[COUNTS] end) as [CHECKCOUNT]
         from (
            -- count amount difference for cash and count difference for the rest

            select isnull(isnull(EXPECTED.ID, ACTUAL.ID),REFUND.ID) as ID,
                isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE) as PAYMENTMETHODCODE,
                case isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE)
                    when 0 then isnull(ACTUAL.ACTUALAMOUNT, 0)  - (isnull(EXPECTED.EXPECTEDAMOUNT, 0) - isnull(REFUND.REFUNDAMOUNT, 0))
                    else isnull(ACTUAL.ACTUALCOUNT, 0) - isnull(EXPECTED.EXPECTEDCOUNT, 0)
                end as [DIFFERENCE]
                ,case isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE)
                    when 1 then isnull(ACTUAL.ACTUALAMOUNT, 0)  - (isnull(EXPECTED.EXPECTEDAMOUNT, 0) - isnull(REFUND.REFUNDAMOUNT, 0))
                    when 2 then isnull(EXPECTED.EXPECTEDAMOUNT, 0) - ISNULL(REFUND.REFUNDAMOUNT, 0)
                    when 10 then isnull(EXPECTED.EXPECTEDAMOUNT, 0)  - ISNULL(REFUND.REFUNDAMOUNT, 0)
                end [OTHERAMOUNTS]
                ,case isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE)
                    when 0 then isnull(EXPECTED.EXPECTEDCOUNT, 0) 
                    when 1 then isnull(EXPECTED.EXPECTEDCOUNT, 0) 
                end [COUNTS]
            from 
            (
                -- expected count and amount

                -- from payment tables

                select
                    R.ID, 
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE as PAYMENTMETHODCODE,
                    count(SALESORDERPAYMENT.ID)as EXPECTEDCOUNT,
                    sum(SALESORDERPAYMENT.AMOUNT) as EXPECTEDAMOUNT
                from dbo.RECONCILIATION as R
                inner join (select ID, SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT from dbo.SALESORDERPAYMENT where [SALESORDERPAYMENT].[DONOTRECONCILE] = 0
                                        union all 
                                        select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
                            ) as SALESORDERPAYMENT on R.ID = SALESORDERPAYMENT.RECONCILIATIONID
                inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
                where R.STATUSCODE in (1, 2)
                group by R.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
            ) as EXPECTED         
            full outer join
            (
                -- actual count and amount

                -- from reconciliation tables

                select R.ID, 
                    rd.PAYMENTMETHODCODE as PAYMENTMETHODCODE, 
                    sum(RD.QUANTITY) as ACTUALCOUNT,
                    sum(RD.AMOUNT) as ACTUALAMOUNT
                from dbo.RECONCILIATION R 
                inner join dbo.RECONCILIATIONDETAIL RD on R.ID = RD.RECONCILIATIONID 
                where R.STATUSCODE in (1, 2)
                group by R.ID, RD.PAYMENTMETHODCODE

                union all

                select R.ID, 
                    0 as PAYMENTMETHODCODE, 
                    0 as ACTUALCOUNT, 
                    coalesce(ACTUALCASH,0) as ACTUALAMOUNT
                from dbo.RECONCILIATION R
                where R.STATUSCODE in (1, 2)
            ) as ACTUAL on EXPECTED.ID = ACTUAL.ID and EXPECTED.PAYMENTMETHODCODE = ACTUAL.PAYMENTMETHODCODE 
            full outer join
            (
                select
                    [RECONCILIATION].[ID] as [ID],
                    case when [CREDITPAYMENT].[PAYMENTMETHODCODE] = 1 then 2 else [CREDITPAYMENT].[PAYMENTMETHODCODE] end as [PAYMENTMETHODCODE],
                    sum([CREDITPAYMENT].[AMOUNT]) as [REFUNDAMOUNT]
                from dbo.[CREDITPAYMENT]
                inner join dbo.[RECONCILIATION]
                    on [CREDITPAYMENT].[RECONCILIATIONID] = [RECONCILIATION].[ID]
                where 
                    [RECONCILIATION].[STATUSCODE] in (1,2)
                group by 
                    case when [CREDITPAYMENT].[PAYMENTMETHODCODE] = 1 then 2 else [CREDITPAYMENT].[PAYMENTMETHODCODE] end,
                    [RECONCILIATION].[ID]
            ) as REFUND on EXPECTED.ID = REFUND.ID and EXPECTED.PAYMENTMETHODCODE = REFUND.PAYMENTMETHODCODE 
        ) as DIFFERENCETABLE group by DIFFERENCETABLE.ID
    ) as RECONCILIATIONLIST
    inner join dbo.RECONCILIATION on RECONCILIATIONLIST.ID = RECONCILIATION.ID
    inner join dbo.APPUSER on RECONCILIATION.APPUSERID = APPUSER.ID

    union all 
    -- open reconciliations with payment >0

    select 
        null as ID, 
        [APPUSER].[ID] as APPUSERID,
        0 as STATUSCODE,
        dbo.UFN_APPUSER_GETNAME([APPUSER].[ID]) as [USERNAME],
        null as RECONCILIATIONDATE,
        @OPENSTATUS as [STATUS],
        null as CASHAMOUNTDIFFERENCE, 
        null as CHECKCOUNTDIFFERENCE, 
        null as CREDITCARDCOUNTDIFFERENCE, 
        null as OTHERCOUNTDIFFERENCE,
        null as APPROVEDBYNAME,
        null as APPROVALDATE,
        null as ORIGINALSUBMISSIONDATE,
        null as CHECKDIFFERENCE,
        null as TOTALOTHERAMOUNT
        ,null as [CASHCOUNT]
        ,null as [CHECKCOUNT]
    from dbo.[APPUSER]
    where
        (
            exists (
                select [ID]
                from dbo.[RECONCILIATION]
                where 
                    [RECONCILIATION].[STATUSCODE] = 0 and
                    [RECONCILIATION].[APPUSERID] = [APPUSER].[ID]
            ) or
            exists (
                select [ID]
                from dbo.[SALESORDERPAYMENT]
                where 
                    [SALESORDERPAYMENT].[DONOTRECONCILE] = 0 and
                    [SALESORDERPAYMENT].[RECONCILIATIONID] is null and
                    [SALESORDERPAYMENT].[APPUSERID] = [APPUSER].[ID] and
                    [SALESORDERPAYMENT].[AMOUNT] > 0
            ) or             
            exists (
                    select [ID] from dbo.[RESERVATIONSECURITYDEPOSITPAYMENT]
                where 
                    [RESERVATIONSECURITYDEPOSITPAYMENT].[RECONCILIATIONID] is null and
                    [RESERVATIONSECURITYDEPOSITPAYMENT].[APPUSERID] = [APPUSER].[ID] and
                    [RESERVATIONSECURITYDEPOSITPAYMENT].[AMOUNT] > 0
            ) or
            exists (
                select [ID]
                from dbo.[CREDITPAYMENT]
                where 
                    [CREDITPAYMENT].[RECONCILIATIONID] is null and
                    [CREDITPAYMENT].[APPUSERID] = [APPUSER].[ID] and
                    [CREDITPAYMENT].[REFUNDPROCESSED] = 1 and
                    [CREDITPAYMENT].[CREDITID] not in (
                        select FT.ID
                        from dbo.FINANCIALTRANSACTION as FT
                        inner join dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
                        left outer join dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
                        where SALESORDER.ID is null and EXT.SALESORDERID is null
                    )
            )
        )
        and (select top 1 C.CONFIGURATIONOPTIONCODE from CLOSEDRAWERCONFIGURATION C) = 0

    order by STATUSCODE desc, RECONCILIATIONDATE desc, [USERNAME];

    return 0;