USP_DATALIST_PAYMENT_BYSALESMETHOD

CREATE procedure dbo.USP_DATALIST_PAYMENT_BYSALESMETHOD
(
    @DATETYPE tinyint = null
    ,@FROMDATE datetime = null
    ,@TODATE datetime = null
    ,@METHOD1NAME nvarchar(25) = null output
    ,@METHOD2NAME nvarchar(25) = null output
    ,@METHOD3NAME nvarchar(25) = null output
    ,@METHOD4NAME nvarchar(25) = null output
    ,@METHOD5NAME nvarchar(25) = null output
)
as
begin
    declare @INSERTSTARTDATE bit = 0;
    declare @INSERTENDDATE bit = 0;

    if @FROMDATE is not null and @TODATE is not null and cast(@FROMDATE as date) = cast(@TODATE as date)
    begin
        set @FROMDATE = DATEADD(day, -1, @FROMDATE)
        set @TODATE = DATEADD(day, 1, @TODATE)
    end
    if @FROMDATE is not null
    begin
        set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
        set @INSERTSTARTDATE = 1
    end
    if @TODATE is not null
    begin
        set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);
        set @INSERTENDDATE = 1
    end

    declare @Data TABLE ([DATE] date, METHOD1AMOUNT money, METHOD2AMOUNT money, METHOD3AMOUNT money, METHOD4AMOUNT money, METHOD5AMOUNT money)
    declare @temp TABLE ([DATE] date, AMOUNT money, METHOD nvarchar(25), METHODCODE integer)

    insert into @temp([DATE], AMOUNT, METHOD, METHODCODE)
    select T.[DATE], SUM(T.TRANSACTIONAMOUNT), T.METHOD, DENSE_RANK() over (order by T.METHOD asc)
    from (
        select FT.CALCULATEDDATE as [DATE], SUM(FT.TRANSACTIONAMOUNT) [TRANSACTIONAMOUNT], 'Back Office' [METHOD]
        from dbo.FINANCIALTRANSACTION as FT
        left join dbo.SALESORDERPAYMENT SP on SP.PAYMENTID = FT.ID and FT.TYPECODE = 0
        left join dbo.REVENUESPLIT RS on RS.REVENUEID = FT.ID and FT.TYPECODE = 0 and RS.APPLICATIONCODE = 2
        left join dbo.RESERVATIONSECURITYDEPOSITPAYMENT RP on FT.ID = RP.PAYMENTID
        where
            FT.DELETEDON is null
            and FT.TYPECODE in (0, 1)
            and SP.ID is null and RS.ID is null and RP.ID is null
            and ((@FROMDATE is null or FT.CALCULATEDDATE >= @FROMDATE) and (@TODATE is null or FT.CALCULATEDDATE <= @TODATE))
        group by FT.CALCULATEDDATE

        union all

        select cast(S.TRANSACTIONDATE as date), SUM(S.AMOUNT), S.SALESMETHODTYPE [METHOD]
        from dbo.SALESORDER S
        where ((@FROMDATE is null or S.TRANSACTIONDATE >= @FROMDATE) and (@TODATE is null or S.TRANSACTIONDATE <= @TODATE))
            and S.TRANSACTIONDATE is not null
        group by cast(S.TRANSACTIONDATE as date), S.SALESMETHODTYPE

        union all

        select FT.CALCULATEDDATE, -SUM(CP.AMOUNT), isnull(CHILDSALESORDER.SALESMETHODTYPE, EXTSALESORDER.SALESMETHODTYPE) [METHOD]
        from dbo.CREDITPAYMENT CP
        inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = CP.CREDITID
        inner join dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
        left outer join dbo.SALESORDER as CHILDSALESORDER on CHILDSALESORDER.REVENUEID = FT.PARENTID
        left outer join dbo.SALESORDER as EXTSALESORDER on EXTSALESORDER.ID = EXT.SALESORDERID
        where ((@FROMDATE is null or FT.CALCULATEDDATE >= @FROMDATE) and (@TODATE is null or FT.CALCULATEDDATE <= @TODATE))
        group by FT.CALCULATEDDATE, isnull(CHILDSALESORDER.SALESMETHODTYPE, EXTSALESORDER.SALESMETHODTYPE)

        union all

        select FT.CALCULATEDDATE, SUM(FT.TRANSACTIONAMOUNT) [TRANSACTIONAMOUNT], S.SALESMETHODTYPE [METHOD]
        from dbo.FINANCIALTRANSACTION as FT
        inner join dbo.RESERVATIONSECURITYDEPOSITPAYMENT RP on FT.ID = RP.PAYMENTID
        inner join dbo.SALESORDER S on S.ID = RP.RESERVATIONID
        where
            FT.DELETEDON is null
            and FT.TYPECODE = 0
            and ((@FROMDATE is null or FT.CALCULATEDDATE >= @FROMDATE) and (@TODATE is null or FT.CALCULATEDDATE <= @TODATE))
        group by FT.CALCULATEDDATE, S.SALESMETHODTYPE) T
    group by T.[DATE], T.METHOD
    option (recompile);

    insert into @Data
    select  pvt.[DATE], pvt.[1] [METHOD1AMOUNT], pvt.[2] [METHOD2AMOUNT], pvt.[3] [METHOD3AMOUNT], pvt.[4] [METHOD4AMOUNT], pvt.[5] [METHOD5AMOUNT]
    from (
        select T.[DATE], T.METHODCODE, T.AMOUNT
        from @temp T
    ) T
    pivot (
        SUM(AMOUNT) for METHODCODE in ([1], [2], [3], [4], [5])
    ) pvt

    select top 1 @METHOD1NAME = T.METHOD from @temp T where T.METHODCODE = 1
    select top 1 @METHOD2NAME = T.METHOD from @temp T where T.METHODCODE = 2
    select top 1 @METHOD3NAME = T.METHOD from @temp T where T.METHODCODE = 3
    select top 1 @METHOD4NAME = T.METHOD from @temp T where T.METHODCODE = 4
    select top 1 @METHOD5NAME = T.METHOD from @temp T where T.METHODCODE = 5

    update @Data set
        METHOD1AMOUNT = case when @METHOD1NAME is not null then isnull(METHOD1AMOUNT, 0) end
        ,METHOD2AMOUNT = case when @METHOD2NAME is not null then isnull(METHOD2AMOUNT, 0) end
        ,METHOD3AMOUNT = case when @METHOD3NAME is not null then isnull(METHOD3AMOUNT, 0) end
        ,METHOD4AMOUNT = case when @METHOD4NAME is not null then isnull(METHOD4AMOUNT, 0) end
        ,METHOD5AMOUNT = case when @METHOD5NAME is not null then isnull(METHOD5AMOUNT, 0) end;

    if @INSERTENDDATE = 1
        if not exists(select 1 from @Data where [DATE] = convert(varchar, @TODATE, 111))
            insert into @Data([DATE], METHOD1AMOUNT, METHOD2AMOUNT, METHOD3AMOUNT, METHOD4AMOUNT, METHOD5AMOUNT)
            values (convert(varchar, @TODATE, 111)
                ,case when @METHOD1NAME is not null then 0 else null end
                ,case when @METHOD2NAME is not null then 0 else null end
                ,case when @METHOD3NAME is not null then 0 else null end
                ,case when @METHOD4NAME is not null then 0 else null end
                ,case when @METHOD5NAME is not null then 0 else null end);
    if @INSERTSTARTDATE = 1
        if not exists(select 1 from @Data where [DATE] = @FROMDATE)
            insert into @Data([DATE], METHOD1AMOUNT, METHOD2AMOUNT, METHOD3AMOUNT, METHOD4AMOUNT, METHOD5AMOUNT)
            values (@FROMDATE
                ,case when @METHOD1NAME is not null then 0 else null end
                ,case when @METHOD2NAME is not null then 0 else null end
                ,case when @METHOD3NAME is not null then 0 else null end
                ,case when @METHOD4NAME is not null then 0 else null end
                ,case when @METHOD5NAME is not null then 0 else null end);

    if @INSERTSTARTDATE = 1 and @INSERTENDDATE = 1
    begin
        with DateRange([DATE]) as
        (
            select @FROMDATE [DATE]
            union all
            select DATEADD(day, 1, [DATE]) [DATE]
            from DateRange
            where [DATE] < convert(varchar, @TODATE, 111)
        )
        insert into @Data([DATE]
                , METHOD1AMOUNT
                , METHOD2AMOUNT
                , METHOD3AMOUNT
                , METHOD4AMOUNT
                , METHOD5AMOUNT)
            select T.[DATE]
                ,case when @METHOD1NAME is not null then 0 else null end
                ,case when @METHOD2NAME is not null then 0 else null end
                ,case when @METHOD3NAME is not null then 0 else null end
                ,case when @METHOD4NAME is not null then 0 else null end
                ,case when @METHOD5NAME is not null then 0 else null end
            from DateRange T
            where T.[DATE] not in (select [DATE] from @Data)
            OPTION (MAXRECURSION 7000);
    end

    select * from @Data
    order by [DATE];
end