USP_SALESORDER_COMPLETEORDER

CREATE procedure dbo.USP_SALESORDER_COMPLETEORDER
(
    @SALESORDERID uniqueidentifier,
    @TRANSACTIONDATE datetime,
    @CONSTITUENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime,
    @REVENUEDATE datetime = null
)
as
    set nocount on;

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

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    if @REVENUEDATE is null
        set @REVENUEDATE = @TRANSACTIONDATE;

    declare @SALESMETHODTYPECODE tinyint;
    declare @CURRENTTRANSACTIONDATE datetime;

    select
        @SALESMETHODTYPECODE = SALESMETHODTYPECODE,
        @CURRENTTRANSACTIONDATE = TRANSACTIONDATE
    from dbo.SALESORDER with (nolock)
    where ID = @SALESORDERID

    --Exit if there are no items in the order

    if @SALESMETHODTYPECODE <> 3 begin
        if not exists (
            select 1 
            from dbo.[SALESORDERITEM] 
            where [SALESORDERID] = @SALESORDERID
        )
            return;
    end
    -- Raise error for Group Sales since there is no Itinerary.

    else if not exists (
        select 1
        from dbo.[ITINERARY]
        where [RESERVATIONID] = @SALESORDERID
    )
    begin
        raiserror('BBERR_CHECKIN_NOITINERARY', 13, 1);
    end

    -- This is ugly, but since setting transaction date

    -- causes more work by the trigger, only set it if needed.

    if @CURRENTTRANSACTIONDATE is null begin
        update dbo.SALESORDER with (rowlock) set 
            STATUSCODE = 1,
            TRANSACTIONDATE = @TRANSACTIONDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @SALESORDERID;
    end
    else begin
        update dbo.SALESORDER with (rowlock) set 
            STATUSCODE = 1,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @SALESORDERID;
    end

    if @SALESMETHODTYPECODE = 2 begin  -- Online Sales

        -- Remove reservation records if they exist

        declare @contextCache varbinary(128);

        /* cache current context information */
        set @contextCache = CONTEXT_INFO();

        /* set CONTEXT_INFO to @CHANGEAGENTID */
        set CONTEXT_INFO @CHANGEAGENTID

        -- For online sales

        delete from dbo.SALESORDERRESERVEDITEM with (rowlock)
        where ID in (select ID from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID);

        /* reset CONTEXT_INFO to previous value */
        if not @contextCache is null
            set CONTEXT_INFO @contextCache
    end

    declare @HASSPONSORSHIPS bit = 0;

    if exists(select 1 from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 12) begin -- Sponsorship

        -- Create sponsorships

        exec dbo.USP_SALESORDER_ADDSPONSORSHIPS @SALESORDERID, @TRANSACTIONDATE, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE
        set @HASSPONSORSHIPS = 1;

        -- delete the session variable backup record after the sponsorship has been created.

        delete from dbo.CMS_SESSIONVARIABLEBACKUP where KEYGUID = @SALESORDERID;
    end

    -- Update cash payment to have correct amount tendered and revenue amount

    if @SALESMETHODTYPECODE <> 3 -- Not Group Sales

        exec dbo.USP_ORDERPAYMENT_UPDATECASHPAYMENTS @SALESORDERID, @CHANGEAGENTID, 0, @CURRENTDATE

    -- Only add order to revenue if the total price before discount is greater than 0

    declare @ORDERTOTALPREDISCOUNT money
    select @ORDERTOTALPREDISCOUNT = isnull(sum(SALESORDERITEM.TOTAL), 0)
    from dbo.SALESORDERITEM with (nolock)
    where SALESORDERITEM.SALESORDERID = @SALESORDERID and
            SALESORDERITEM.TYPECODE not in (5, 13) --exclude discounts and membership promotions


    set @TRANSACTIONDATE = cast(@TRANSACTIONDATE as date);

    declare @HASMEMBERSHIPS bit = 0;

    declare @MEMBERSHIPEXPIRATIONDATES table (
        SALESORDERITEMMEMBERSHIPID uniqueidentifier,
        CURRENTEXPIRATIONDATE datetime,
        NEWEXPIRATIONDATE datetime
    )

    insert into @MEMBERSHIPEXPIRATIONDATES
    select
        SALESORDERITEMMEMBERSHIP.ID,
        SALESORDERITEMMEMBERSHIP.EXPIRATIONDATE,
        dbo.UFN_SALESORDERITEMMEMBERSHIP_CALCULATEFINALEXPIRATIONDATE(SALESORDERITEMMEMBERSHIP.ID, @TRANSACTIONDATE)
    from
        dbo.SALESORDERITEM
    inner join
        dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
    where
        SALESORDERITEM.SALESORDERID = @SALESORDERID;

    if @@rowcount > 0 begin
        set @HASMEMBERSHIPS = 1;

        -- Apply any term extension membership promotions

        -- Bug 127775, if user manually changes the expiration date on a membership card,

        -- don't change it here.

        update dbo.SALESORDERITEMMEMBERSHIPCARD with (rowlock) set 
            EXPIRATIONDATE = MEMBERSHIPEXPIRATIONDATES.NEWEXPIRATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from 
            dbo.SALESORDERITEMMEMBERSHIPCARD SOIMC
        inner join
            dbo.SALESORDERITEMMEMBER SOIMB on SOIMC.SALESORDERITEMMEMBERID = SOIMB.ID
        inner join
            @MEMBERSHIPEXPIRATIONDATES as MEMBERSHIPEXPIRATIONDATES on MEMBERSHIPEXPIRATIONDATES.SALESORDERITEMMEMBERSHIPID = SOIMB.SALESORDERITEMMEMBERSHIPID
        where
            SOIMC.EXPIRATIONDATE = MEMBERSHIPEXPIRATIONDATES.CURRENTEXPIRATIONDATE;

        update dbo.SALESORDERITEMMEMBERSHIP set
            EXPIRATIONDATE = MEMBERSHIPEXPIRATIONDATES.NEWEXPIRATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.SALESORDERITEMMEMBERSHIP
        inner join
            @MEMBERSHIPEXPIRATIONDATES as MEMBERSHIPEXPIRATIONDATES on MEMBERSHIPEXPIRATIONDATES.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID
    end

    if @ORDERTOTALPREDISCOUNT > 0 begin
        declare @REVENUEID uniqueidentifier = newid();

        -- create order revenue item only if there are no sponsorships in the order.

        if @HASSPONSORSHIPS = 0
            exec dbo.USP_SALESORDER_ADDREVENUE @SALESORDERID, @REVENUEID, @REVENUEDATE, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

        --add discounts

        exec dbo.USP_SALESORDER_ADDDISCOUNTCREDITS @SALESORDERID, @CHANGEAGENTID, @CURRENTDATE, @REVENUEDATE

        --create splits for sales order payments

        exec dbo.USP_SALESORDER_ADDPAYMENTREVENUE @SALESORDERID, @REVENUEID, @CHANGEAGENTID, @CURRENTDATE, null

        --gift fees

        if dbo.UFN_GIFTFEE_ENABLED() = 1 begin
            if exists (select 1 from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE in (1, 2, 6)) -- Membership, Donation, Event Registration

            begin
                declare @PAYMENTREVENUEID uniqueidentifier

                -- An assumption is made that the constituent on the order

                -- will be the same as the constituent on the payment records,

                -- which should always be the case

                declare PAYMENTSCURSOR cursor local fast_forward for 
                select 
                    SALESORDERPAYMENT.PAYMENTID
                from 
                    dbo.SALESORDERPAYMENT
                where 
                    SALESORDERPAYMENT.SALESORDERID = @SALESORDERID

                open PAYMENTSCURSOR
                fetch next from PAYMENTSCURSOR into @PAYMENTREVENUEID
                while @@fetch_status = 0
                begin
                    exec dbo.USP_PAYMENT_ADDGIFTFEES @PAYMENTREVENUEID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;
                    fetch next from PAYMENTSCURSOR into @PAYMENTREVENUEID
                end
                close PAYMENTSCURSOR
                deallocate PAYMENTSCURSOR
            end
        end
    end

    if exists (select * from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 0) begin  -- Ticket

        --create tickets

        exec dbo.USP_SALESORDER_GENERATETICKETS @SALESORDERID, @CHANGEAGENTID, @CURRENTDATE
    end

    if @HASMEMBERSHIPS = 1 begin
        -- Handle membership items that have a no cost

        exec dbo.USP_SALESORDER_HANDLEZEROCOSTMEMBERSHIPS @SALESORDERID, @TRANSACTIONDATE, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

        if @SALESMETHODTYPECODE = 2 begin -- Online Sales

            exec dbo.USP_SALESORDER_MEMBERSHIP_ADDADDRESSES @SALESORDERID, @CHANGEAGENTID, @CURRENTDATE
        end
    end

    return 0;