USP_DATAFORMTEMPLATE_EDIT_RECONCILIATIONCLOSESHIFT2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECONCILIATIONCLOSESHIFT2 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @ACTUALCASH money,
    @COMMENT nvarchar(max),
    @WORKSTATIONID uniqueidentifier,
    @CASHDENOMINATIONS xml,
    @COINDENOMINATIONS xml,
    @CHECKNUM int,
    @CHECKTOTAL money,
    @STATUSCODE tinyint,
    @CURRENTAPPUSERID uniqueidentifier
)
as
    set nocount on;

    declare @APPUSERID uniqueidentifier;
    select @APPUSERID = APPUSERID from dbo.RECONCILIATION where ID = @ID;
    declare @CLOSEDRAWERCONFIGURATIONOPTIONCODE tinyint
    select @CLOSEDRAWERCONFIGURATIONOPTIONCODE = CONFIGURATIONOPTIONCODE from dbo.CLOSEDRAWERCONFIGURATION;

    if @CLOSEDRAWERCONFIGURATIONOPTIONCODE = 0
        set @CURRENTAPPUSERID = @APPUSERID;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime = getdate();
    declare @CLIENTDATETIME datetime = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);

    begin try
        -- Check if there are pending orders with application user
        if exists (
            select SALESORDERPAYMENT.ID 
            from dbo.SALESORDERPAYMENT
            inner join dbo.SALESORDER 
                on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
            where 
                SALESORDERPAYMENT.RECONCILIATIONID is null 
                and SALESORDER.STATUSCODE <> 1
                and SALESORDERPAYMENT.APPUSERID = @APPUSERID
                and SALESORDER.SALESMETHODTYPECODE <> 3
        )
        begin
            raiserror('ERR_RECONCILIATION_PENDINGORDEREXISTS', 13, 1);
        end

        -- update SALESORDERPAYMENT table
        declare @OLDSTATUSCODE tinyint
        declare @OLDSUBMITTEDDATE datetime
        declare @CASHSALESMETHODCODE tinyint;
        declare @CHECKSALESMETHODCODE tinyint;

        select @OLDSTATUSCODE = STATUSCODE
            ,@OLDSUBMITTEDDATE = ORIGINALSUBMISSIONDATE
            ,@CASHSALESMETHODCODE = CASHOVERSHORTSALESMETHODCODE
            ,@CHECKSALESMETHODCODE = CHECKOVERSHORTSALESMETHODCODE
        from dbo.RECONCILIATION 
        where ID = @ID ;

        -- update RECONCILIATION table
        update dbo.[RECONCILIATION] 
        set
            [ACTUALCASH] = @ACTUALCASH,
            [COMMENT] = @COMMENT,
            [STATUSCODE] = @STATUSCODE,
            [ORIGINALSUBMISSIONDATE] = case when @OLDSTATUSCODE = 0 and @STATUSCODE = 1 then @CLIENTDATETIME else @OLDSUBMITTEDDATE end,
            [RECONCILIATIONDATE] = @CLIENTDATETIME,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE,
            [APPUSERID] = @CURRENTAPPUSERID
        where [ID] = @ID;

        -- only update SALESORDERPAYMENT table when
        -- current reconciliation is open
        if @OLDSTATUSCODE = 0 begin
            update dbo.SALESORDERPAYMENT with (rowlock)
            set
                RECONCILIATIONID = @ID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.[SALESORDERPAYMENT]
            where 
                SALESORDERPAYMENT.RECONCILIATIONID is null and
                SALESORDERPAYMENT.DONOTRECONCILE = 0 and
                (@CLOSEDRAWERCONFIGURATIONOPTIONCODE = 2 or SALESORDERPAYMENT.APPUSERID = @APPUSERID)

            -- update reservation security deposit payment table
            update dbo.RESERVATIONSECURITYDEPOSITPAYMENT with (rowlock)
            set
                RECONCILIATIONID = @ID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.[RESERVATIONSECURITYDEPOSITPAYMENT]
            inner join dbo.[SALESORDER]
                on [RESERVATIONSECURITYDEPOSITPAYMENT].[RESERVATIONID] = [SALESORDER].[ID]
            where 
                RESERVATIONSECURITYDEPOSITPAYMENT.RECONCILIATIONID is null
                and (@CLOSEDRAWERCONFIGURATIONOPTIONCODE = 2 or RESERVATIONSECURITYDEPOSITPAYMENT.APPUSERID = @APPUSERID)
                and [SALESORDER].[SALESMETHODTYPECODE] <> 2;


            update dbo.[CREDITPAYMENT]
            set
                [RECONCILIATIONID] = @ID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
            where 
                [CREDITPAYMENT].[RECONCILIATIONID] is null and 
                (@CLOSEDRAWERCONFIGURATIONOPTIONCODE = 2 or [CREDITPAYMENT].[APPUSERID] = @APPUSERID) 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
                );
        end

        -- Do not get the sale method code if it is not the default
        if @CASHSALESMETHODCODE = 0
            select @CASHSALESMETHODCODE = isnull(MIN(T.SALESMETHODTYPECODE), @CASHSALESMETHODCODE)
            from (
                select SO.SALESMETHODTYPECODE
                from dbo.SALESORDERPAYMENT P 
                inner join dbo.REVENUEPAYMENTMETHOD PM on PM.REVENUEID = P.PAYMENTID
                inner join dbo.SALESORDER SO on SO.ID = P.SALESORDERID
                where P.RECONCILIATIONID = @ID and PM.PAYMENTMETHODCODE = 0
                union all
                select SO.SALESMETHODTYPECODE
                from dbo.RESERVATIONSECURITYDEPOSITPAYMENT P
                inner join dbo.REVENUEPAYMENTMETHOD PM on PM.REVENUEID = P.PAYMENTID
                inner join dbo.SALESORDER SO on SO.ID = P.RESERVATIONID
                where P.RECONCILIATIONID = @ID and PM.PAYMENTMETHODCODE = 0
                union all
                select isnull(CHILDSALESORDER.SALESMETHODTYPECODE, SALESORDER.SALESMETHODTYPECODE)
                from dbo.CREDITPAYMENT P
                inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = P.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 on SALESORDER.ID = EXT.SALESORDERID
                where P.RECONCILIATIONID = @ID and P.PAYMENTMETHODCODE = 0
                and (CHILDSALESORDER.ID is not null or EXT.SALESORDERID is not null)
            ) T

        -- Do not get the sale method code if it is not the default
        if @CHECKSALESMETHODCODE = 3
            select @CHECKSALESMETHODCODE = isnull(MAX(T.SALESMETHODTYPECODE), @CHECKSALESMETHODCODE)
            from (
                select SO.SALESMETHODTYPECODE
                from dbo.SALESORDERPAYMENT P 
                inner join dbo.REVENUEPAYMENTMETHOD PM on PM.REVENUEID = P.PAYMENTID
                inner join dbo.SALESORDER SO on SO.ID = P.SALESORDERID
                where P.RECONCILIATIONID = @ID and PM.PAYMENTMETHODCODE = 1
                union all
                select SO.SALESMETHODTYPECODE
                from dbo.RESERVATIONSECURITYDEPOSITPAYMENT P
                inner join dbo.REVENUEPAYMENTMETHOD PM on PM.REVENUEID = P.PAYMENTID
                inner join dbo.SALESORDER SO on SO.ID = P.RESERVATIONID
                where P.RECONCILIATIONID = @ID and PM.PAYMENTMETHODCODE = 1
                union all
                select isnull(CHILDSALESORDER.SALESMETHODTYPECODE, SALESORDER.SALESMETHODTYPECODE)
                from dbo.CREDITPAYMENT P
                inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = P.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 on SALESORDER.ID = EXT.SALESORDERID
                where P.RECONCILIATIONID = @ID and P.PAYMENTMETHODCODE = 1
                and (CHILDSALESORDER.ID is not null or EXT.SALESORDERID is not null)
            ) T

        update dbo.[RECONCILIATION] 
        set
            [CASHOVERSHORTSALESMETHODCODE] = @CASHSALESMETHODCODE,
            [CHECKOVERSHORTSALESMETHODCODE] = @CHECKSALESMETHODCODE,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        where [ID] = @ID;

        declare @TempDenom table(
            ID uniqueidentifier default null,
            DENOMINATIONID uniqueidentifier,
            NAME nvarchar(50),
            QUANTITY int,
            VALUE money);

        insert into @TempDenom
        select
            ID,
            DENOMINATIONID,
            NAME,
            QUANTITY,
            VALUE
        from dbo.UFN_CLOSEDRAWER_GETDENOMINATIONS_FROMITEMLISTXML(@CASHDENOMINATIONS)

        insert into @TempDenom
        select
            ID,
            DENOMINATIONID,
            NAME,
            QUANTITY,
            VALUE
        from dbo.UFN_CLOSEDRAWER_GETDENOMINATIONS_FROMITEMLISTXML(@COINDENOMINATIONS)

        -- update CLOSEDRAWERDENOMINATION table
        merge CLOSEDRAWERDENOMINATION as target
        using @TempDenom as source
        on (target.ID = source.ID)
        when matched then
            update set
                target.QUANTITY = source.QUANTITY,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        when not matched and source.QUANTITY > 0 then
            insert(
                RECONCILIATIONID,
                CURRENCYDENOMINATIONID,
                QUANTITY,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values (
                @ID,
                source.DENOMINATIONID,
                source.QUANTITY,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

        -- update RECONCILIATIONDETAIL table

        -- build a temporary table containing the values from the XML
        declare @TempTbl table (
            ID uniqueidentifier default null,
            PAYMENTMETHODCODE int,
            CREDITTYPECODEID uniqueidentifier,
            OTHERPAYMENTMETHODCODEID uniqueidentifier,
            ISREFUND bit,
            AMOUNT money,
            EXPECTED int
        );

        if exists (select ID from dbo.CLOSEDRAWERCONFIGURATION where CONFIGURATIONOPTIONCODE != 0)
            set @APPUSERID = null;

        insert into @TempTbl 
        select 
            [ID],
            [PAYMENTMETHODCODE], 
            [CREDITTYPECODEID],
            [OTHERPAYMENTMETHODCODEID],
            [ISREFUND],
            [TOTALAMOUNT],
            [EXPECTED]
        from dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS(@ID, @APPUSERID )
        where 
            EXPECTED <> 0 and ISLABEL = 0

        update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
        update @TempTbl set CREDITTYPECODEID = null where CREDITTYPECODEID = '00000000-0000-0000-0000-000000000000';
        update @TempTbl set OTHERPAYMENTMETHODCODEID = null where OTHERPAYMENTMETHODCODEID = '00000000-0000-0000-0000-000000000000';

        if @@Error <> 0
            return 1;

        -- update the items that exist in the XML table and the db
        update dbo.[RECONCILIATIONDETAIL]
        set [RECONCILIATIONDETAIL].[QUANTITY]= [temp].EXPECTED,
            [RECONCILIATIONDETAIL].[AMOUNT] = [temp].AMOUNT,
            [RECONCILIATIONDETAIL].CHANGEDBYID = @CHANGEAGENTID,
            [RECONCILIATIONDETAIL].DATECHANGED = @CURRENTDATE 
        from @TempTbl as [temp]
        where 
            [temp].ID = [RECONCILIATIONDETAIL].ID and
            (
                ([RECONCILIATIONDETAIL].[QUANTITY] <> [temp].[EXPECTED]) or 
                ([RECONCILIATIONDETAIL].[QUANTITY] is null and [temp].[EXPECTED] is not null) or 
                ([RECONCILIATIONDETAIL].[QUANTITY] is not null and [temp].[EXPECTED] is null) 
            ) and
            [temp].[ISREFUND] = 0 and temp.PAYMENTMETHODCODE != 1;

        --Update refund
        update dbo.[RECONCILIATIONCREDITDETAIL]
        set 
            [RECONCILIATIONCREDITDETAIL].[QUANTITY]= [temp].EXPECTED,
            [RECONCILIATIONCREDITDETAIL].CHANGEDBYID = @CHANGEAGENTID,
            [RECONCILIATIONCREDITDETAIL].DATECHANGED = @CURRENTDATE 
        from @TempTbl as [temp]
        where [temp].ID = [RECONCILIATIONCREDITDETAIL].ID
            and [temp].[ISREFUND] = 1;

        if @@Error <> 0
            return 3;    

        -- insert new items
        insert into [RECONCILIATIONDETAIL] (
            [ID],
            [RECONCILIATIONID],
            [PAYMENTMETHODCODE],
            [QUANTITY],
            [AMOUNT],
            [CREDITTYPECODEID],
            [OTHERPAYMENTMETHODCODEID],
            [ADDEDBYID], 
            [CHANGEDBYID], 
            [DATEADDED], 
            [DATECHANGED]
        )
        select [ID],
            @ID, 
            [PAYMENTMETHODCODE],
            [EXPECTED],
            [AMOUNT],
            [CREDITTYPECODEID],
            [OTHERPAYMENTMETHODCODEID],
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from @TempTbl as [temp]
        where 
            not exists (select ID from dbo.[RECONCILIATIONDETAIL] as data where data.ID = [temp].ID) and
            [temp].[ISREFUND] = 0

        --Insert refund
        if @ID in (select [ID] from @TempTbl where [ISREFUND] = 1) and not exists (select [ID] from dbo.[RECONCILIATIONCREDITDETAIL] where [ID] = @ID)
            insert into [RECONCILIATIONCREDITDETAIL] (
                [ID],
                [QUANTITY],
                [ADDEDBYID], 
                [CHANGEDBYID], 
                [DATEADDED], 
                [DATECHANGED]
            )
            select 
                [ID],
                [EXPECTED],
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @TempTbl as [temp]
            where 
                [temp].[ID] = @ID and 
                [temp].[ISREFUND] = 1

        if exists(select 1 from dbo.RECONCILIATIONDETAIL where RECONCILIATIONID = @ID and PAYMENTMETHODCODE = 1)
            update dbo.RECONCILIATIONDETAIL set
                QUANTITY = @CHECKNUM,
                AMOUNT = @CHECKTOTAL,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where RECONCILIATIONID = @ID and PAYMENTMETHODCODE = 1
        else
            insert into dbo.RECONCILIATIONDETAIL(
                RECONCILIATIONID,
                PAYMENTMETHODCODE,
                QUANTITY,
                AMOUNT,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values (
                @ID,
                1,
                @CHECKNUM,
                @CHECKTOTAL,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

        if @@Error <> 0
            return 4;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0;