USP_DATALIST_SALESRECONCILIATIONREPORT_RECONCILIATIONDETAILS

CREATE procedure dbo.USP_DATALIST_SALESRECONCILIATIONREPORT_RECONCILIATIONDETAILS
(
    @RECONCILIATIONID uniqueidentifier,
    @ISSUPERVISOR bit = 0
)
as
    set nocount on;

    select
        coalesce(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE,REFUND.PAYMENTMETHODCODE) as PAYMENTMETHODCODE,
        dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(coalesce(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE, REFUND.PAYMENTMETHODCODE)) as PAYMENTMETHOD,
        case isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE)
            when 0 then ACTUAL.NAME
            when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(isnull(isnull(EXPECTED.CREDITTYPECODEID, ACTUAL.CREDITTYPECODEID), REFUND.CREDITTYPECODEID)) 
            when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(isnull(isnull(EXPECTED.OTHERPAYMENTMETHODCODEID, ACTUAL.OTHERPAYMENTMETHODCODEID), REFUND.OTHERPAYMENTMETHODCODEID))
            else ''
        end as PAYMENTTYPE,
        isnull(ACTUAL.ACTUALAMOUNT, 0) as ACTUALAMOUNT,
        case
            when isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE) = 0 then
                case when ACTUAL.SEQUENCE = 1 then isnull(EXPECTED.EXPECTEDAMOUNT, 0) else 0 end
            else isnull(EXPECTED.EXPECTEDAMOUNT, 0)
        end as EXPECTEDAMOUNT,
        case
            when isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE) = 0 then
                case when ACTUAL.SEQUENCE = 1 then isnull(REFUND.REFUNDAMOUNT, 0) else 0 end
            else isnull(REFUND.REFUNDAMOUNT, 0)
        end as REFUNDAMOUNT,
        isnull(ACTUAL.ACTUALCOUNT, 0) as ACTUALCOUNT,
        isnull(EXPECTED.EXPECTEDCOUNT, 0) as EXPECTEDCOUNT,
        cast(0 as bit) as [ISREFUND],
        rank() over (partition by isnull(isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE),REFUND.PAYMENTMETHODCODE) order by ACTUAL.VALUE desc) SORTINGCOLUMN
    from (
        -- expected count and amount

        -- from payment tables

        select
            R.ID, 
            REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE as PAYMENTMETHODCODE,
            CREDITCARDPAYMENTMETHODDETAIL.[CREDITTYPECODEID],
            OTHERPAYMENTMETHODDETAIL.[OTHERPAYMENTMETHODCODEID],
            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 [RECONCILIATIONID] = @RECONCILIATIONID and [DONOTRECONCILE] = 0

            union all 
            select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT
            from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
            where [RECONCILIATIONID] = @RECONCILIATIONID
        ) as SALESORDERPAYMENT on R.ID = SALESORDERPAYMENT.RECONCILIATIONID
        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
        left join dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID 
        left join dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
        where R.ID = @RECONCILIATIONID 
        group by
            R.ID,
            REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
            CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID,
            OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID
    ) as EXPECTED 
    full outer join (
        -- actual count and amount

        -- from reconciliation tables

        select R.ID, 
            rd.PAYMENTMETHODCODE as PAYMENTMETHODCODE,
            rd.CREDITTYPECODEID,
            rd.OTHERPAYMENTMETHODCODEID,
            sum(RD.QUANTITY) as ACTUALCOUNT,
            sum(RD.AMOUNT) as ACTUALAMOUNT,
            null as NAME,
            null as VALUE,
            null as SEQUENCE
        from dbo.RECONCILIATION R 
        inner join dbo.RECONCILIATIONDETAIL RD on R.ID = RD.RECONCILIATIONID 
        where
            R.ID = @RECONCILIATIONID
            and (
                RD.QUANTITY != 0
                or RD.AMOUNT != 0
                or (
                    @ISSUPERVISOR = 1
                    and exists (
                        select 1
                        from (
                            select PAYMENTID, RECONCILIATIONID from dbo.SALESORDERPAYMENT where [RECONCILIATIONID] = @RECONCILIATIONID and [DONOTRECONCILE] = 0
                            union all 
                            select PAYMENTID, RECONCILIATIONID from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where [RECONCILIATIONID] = @RECONCILIATIONID
                        ) as SALESORDERPAYMENT
                        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
                        where REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 and SALESORDERPAYMENT.RECONCILIATIONID = @RECONCILIATIONID
                    )
                )
            )
        group by R.ID, RD.PAYMENTMETHODCODE, RD.CREDITTYPECODEID, RD.OTHERPAYMENTMETHODCODEID

        union all
        select 
            R.ID
            ,0 as PAYMENTMETHODCODE
            ,null
            ,null
            ,ISNULL(C.QUANTITY, 0) as ACTUALCOUNT
            ,ISNULL(C.QUANTITY * C.VALUE, R.ACTUALCASH + R.STARTINGCASH) as ACTUALAMOUNT
            ,ISNULL(case when C.NUMBER = 1 then case C.TYPECODE when 0 then 'Currency ' else 'Coin ' end else '' end + C.NAME, 'Total cash') NAME
            ,isnull(C.VALUE, 0)
            ,isnull(C.OVERALLNUMBER, 1)
        from dbo.RECONCILIATION R
        left join (
            select
                CD.RECONCILIATIONID
                ,CD.QUANTITY
                ,D.VALUE
                ,D.TYPECODE
                ,D.NAME
                ,ROW_NUMBER() over (partition by D.TYPECODE order by D.VALUE desc) NUMBER
                ,ROW_NUMBER() over (order by D.VALUE desc) OVERALLNUMBER
            from dbo.CLOSEDRAWERDENOMINATION CD
            inner join dbo.CURRENCYDENOMINATION D on CD.CURRENCYDENOMINATIONID = D.ID
            where CD.RECONCILIATIONID = @RECONCILIATIONID and CD.QUANTITY > 0
        ) C on C.RECONCILIATIONID = R.ID
        where
            R.ID = @RECONCILIATIONID
            and (
                C.RECONCILIATIONID is not null
                or R.ACTUALCASH != 0
                or R.STARTINGCASH != 0
                or (
                    @ISSUPERVISOR = 1
                    and (
                        exists (
                            select 1
                            from (
                                select PAYMENTID, RECONCILIATIONID from dbo.SALESORDERPAYMENT where [RECONCILIATIONID] = @RECONCILIATIONID and [DONOTRECONCILE] = 0
                                union all 
                                select PAYMENTID, RECONCILIATIONID from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where [RECONCILIATIONID] = @RECONCILIATIONID
                            ) as SALESORDERPAYMENT
                            inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
                            where REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 and SALESORDERPAYMENT.RECONCILIATIONID = @RECONCILIATIONID
                        )
                        or exists (
                            select 1
                            from dbo.CREDITPAYMENT CP
                            where CP.RECONCILIATIONID = @RECONCILIATIONID
                            and CP.PAYMENTMETHODCODE = 0
                        )
                    )
                )
            )

            union all
            select
                R.ID
                ,0 as PAYMENTMETHODCODE
                ,null
                ,null
                ,1
                ,-R.STARTINGCASH
                ,'Starting balance'
                ,-R.STARTINGCASH
                ,-R.STARTINGCASH
            from dbo.RECONCILIATION R
            where R.ID = @RECONCILIATIONID and R.STARTINGCASH != 0
    ) as ACTUAL
        on EXPECTED.ID = ACTUAL.ID and EXPECTED.PAYMENTMETHODCODE = ACTUAL.PAYMENTMETHODCODE 
        and ( EXPECTED.PAYMENTMETHODCODE in (0, 1) or
              (EXPECTED.PAYMENTMETHODCODE = 2 and EXPECTED.CREDITTYPECODEID = ACTUAL.CREDITTYPECODEID) or
              (EXPECTED.PAYMENTMETHODCODE = 10 and EXPECTED.OTHERPAYMENTMETHODCODEID = ACTUAL.OTHERPAYMENTMETHODCODEID))
    full outer join (
        select
            [CREDITS].[PAYMENTMETHODCODE],
            [CREDITS].[CREDITTYPECODEID],
            [CREDITS].[OTHERPAYMENTMETHODCODEID],
            sum([CREDITS].[AMOUNT]) as [REFUNDAMOUNT]
        from (
            select 
                [PAYMENTMETHODCODE],
                case when [PAYMENTMETHODCODE] = 2 then
                    (
                        select top 1
                            [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]
                        from
                            dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                        inner join
                            dbo.[REVENUEPAYMENTMETHOD] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
                        left outer join
                            dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = REVENUEPAYMENTMETHOD.REVENUEID
                        where 
                            [REVENUEPAYMENTMETHOD].[REVENUEID] = [CREDITPAYMENT].[REVENUEID] or
                            LI.ID = [CREDITPAYMENT].[REVENUESPLITID]
                    ) 
                    else null
                end as [CREDITTYPECODEID],
                [OTHERPAYMENTMETHODCODEID],
                [AMOUNT]
            from dbo.[CREDITPAYMENT]
            where [CREDITPAYMENT].[RECONCILIATIONID] = @RECONCILIATIONID
        ) as [CREDITS]
        group by
            [CREDITS].[PAYMENTMETHODCODE],
            [CREDITS].[CREDITTYPECODEID],
            [CREDITS].[OTHERPAYMENTMETHODCODEID]
    ) as REFUND
        on 
            (
                EXPECTED.PAYMENTMETHODCODE = REFUND.PAYMENTMETHODCODE and
                (
                    EXPECTED.PAYMENTMETHODCODE in (0, 1) or
                    (EXPECTED.PAYMENTMETHODCODE = 2 and EXPECTED.CREDITTYPECODEID = REFUND.CREDITTYPECODEID) or 
                    (EXPECTED.PAYMENTMETHODCODE = 10 and EXPECTED.OTHERPAYMENTMETHODCODEID = REFUND.OTHERPAYMENTMETHODCODEID)
                )
            ) or
            (
                ACTUAL.PAYMENTMETHODCODE = REFUND.PAYMENTMETHODCODE and
                (
                    ACTUAL.PAYMENTMETHODCODE in (0, 1) or
                    (ACTUAL.PAYMENTMETHODCODE = 2 and EXPECTED.CREDITTYPECODEID = REFUND.CREDITTYPECODEID) or 
                    (ACTUAL.PAYMENTMETHODCODE = 10 and EXPECTED.OTHERPAYMENTMETHODCODEID = REFUND.OTHERPAYMENTMETHODCODEID)
                )
            )

    union all
    select
        null as [PAYMENTMETHODCODE],
        null as [PAYMENTMETHOD],
        null as [PAYMENTTYPE],
        null as [ACTUALAMOUNT],
        coalesce(sum([CREDITPAYMENT].[AMOUNT]),0) as [EXPECTEDAMOUNT],
        null as [REFUNDAMOUNT], --we aren't refunding a refund

        coalesce((select [QUANTITY] from dbo.[RECONCILIATIONCREDITDETAIL] where [ID] = @RECONCILIATIONID),0) as [QUANTITY],
        count(distinct FT.ID) as [EXPECTED],
        cast(1 as bit) as [ISREFUND],
        null as SORTINGCOLUMN
    from dbo.FINANCIALTRANSACTION as FT
    inner join dbo.[CREDITPAYMENT] on [CREDITPAYMENT].[CREDITID] = FT.ID
    where [CREDITPAYMENT].[RECONCILIATIONID] = @RECONCILIATIONID
    having count(distinct FT.ID) > 0

    order by ISREFUND asc, PAYMENTMETHODCODE asc, SORTINGCOLUMN asc, PAYMENTTYPE asc
    option (recompile);

    return 0;