USP_SALESORDER_COMPLETE

create procedure dbo.USP_SALESORDER_COMPLETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @STOREMARKETINGINFORMATION bit = 0,
    @CONSTITUENTPOSTCODE nvarchar(12) = null,
    @CONSTITUENTCOUNTRYID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @UPDATEORDERAPPUSER bit = 0
)
as begin
    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try            
        -- Save marketing (location) information

        -- Moved this to the complete record operation to save a DataFormSave call

        if @STOREMARKETINGINFORMATION = 1 and not exists(select 1 from dbo.SALESORDERMARKETINGINFORMATION with (nolock) where ID = @ID) begin
            insert into dbo.SALESORDERMARKETINGINFORMATION
                (ID, POSTCODE, COUNTRYID, NOTCOLLECTED, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (@ID, @CONSTITUENTPOSTCODE, @CONSTITUENTCOUNTRYID, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
        end

        declare @ORDERSTATUS tinyint
        declare @CONSTITUENTID uniqueidentifier
        declare @SALESMETHODTYPECODE tinyint
        declare @TRANSACTIONDATE datetime
        declare @DELIVERYMETHODID uniqueidentifier;

        select
            @ORDERSTATUS = SALESORDER.STATUSCODE,
            @CONSTITUENTID = SALESORDER.CONSTITUENTID,
            @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE,
            @TRANSACTIONDATE = SALESORDER.TRANSACTIONDATE,
            @DELIVERYMETHODID = SALESORDER.DELIVERYMETHODID
        from
            dbo.SALESORDER with (nolock)
        where
            SALESORDER.ID = @ID;

        if @UPDATEORDERAPPUSER = 1 begin
            update dbo.[SALESORDER]
            set 
                [APPUSERID] = @CURRENTAPPUSERID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
            where [ID] = @ID
        end    

        declare @HASSPONSORSHIPS bit = 1;
        -- First check if there is a sponsorship in the sales order, if there is one, ignore item sanity checks

        -- Item sanity check: This check is mainly to prevent unresolved online sales orders that fail to build out items (memberships, preregistered events) from being completed without the ticket seller correcting the issue

        if not exists(select * from dbo.SALESORDERITEM where SALESORDERID = @ID and TYPECODE = 12) begin
            set @HASSPONSORSHIPS = 0;

            --See if a membership on the order is invalid

            declare @MEMBERSHIPERRORCODE tinyint =  dbo.UFN_SALESORDER_EXISTSINVALIDMEMBERSHIP_CODE(@ID)

            if @MEMBERSHIPERRORCODE = 1
                raiserror('BBERR_MEMBERSHIPWITHOUTPRIMARYMEMBER', 13, 1);
            else if @MEMBERSHIPERRORCODE = 2
                raiserror('BBERR_MEMBERSHIPWITHINACTIVELEVEL', 13, 1);
            else if @MEMBERSHIPERRORCODE = 3
                raiserror('BBERR_MEMBERSHIPWITHINACTIVETERM', 13, 1);
            else if @MEMBERSHIPERRORCODE = 4
                raiserror('BBERR_MEMBERSHIP_INACTIVELEVELANDTERM', 13, 1);

            --Check that preregistered event items have ticket registrants

            if exists(
                select 1
                from dbo.[SALESORDERITEM]
                where 
                    [SALESORDERITEM].[SALESORDERID] = @ID and
                    [SALESORDERITEM].[TYPECODE] = 0 and
                    not exists(
                        select 1
                        from dbo.[SALESORDERITEMTICKET]
                        inner join dbo.[PROGRAM]
                            on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
                        where 
                            [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID] and
                            (
                                [PROGRAM].[ISPREREGISTERED] = 0 or
                                exists (
                                    select 1
                                    from dbo.[SALESORDERITEMTICKETREGISTRANT]
                                    where [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
                                )
                            )
                    )
            )
                raiserror('BBERR_PREREGISTEREDEVENTWITHOUTREGISTRANTS', 13, 1);

            --Check that event registrations have a registrant

            if exists(
                select 1
                from dbo.[SALESORDERITEM]
                where 
                    [SALESORDERITEM].[SALESORDERID] = @ID and
                    [SALESORDERITEM].[TYPECODE] = 6 and
                    not exists(
                        select 1
                        from dbo.[SALESORDERITEMEVENTREGISTRATION]
                        where [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
                    )
            )
                raiserror('BBERR_EVENTREGISTRATIONWITHOUTREGISTRANTS', 13, 1);
        end

        -- Confirm delivery method is set and is not blocked

        if @SALESMETHODTYPECODE not in (2, 3) begin  -- Online Sales, Group Sales

            if @DELIVERYMETHODID is null begin
                raiserror('BBERR_DELIVERYMETHODREQUIRED', 13, 1);
            end

            if dbo.UFN_DELIVERYMETHOD_VALIDFORORDER(@DELIVERYMETHODID, @ID) = 0 begin
                raiserror('BBERR_INVALIDDELIVERYMETHOD', 13, 1);
            end

            -- Confirm required fields for delivery method are set

            -- We have to ensure the address actually has an address!

            if 0 = dbo.UFN_SALESORDER_VALIDATEDELIVERY(@ID) begin
                raiserror('BBERR_RECIPIENTINFOMISSING', 13, 1);
            end
        end

        if @SALESMETHODTYPECODE = 3 and @CONSTITUENTID is null begin
            raiserror('BBERR_CONSTITUENTREQUIRED.', 13, 1);
        end

        if @HASSPONSORSHIPS = 0 begin
            exec dbo.USP_SALESORDER_VERIFYZEROBALANCE @ID;
        end

        exec dbo.USP_SALESORDER_ISCOMPLETE_RAISERROR @ID, @EXCLUDEGROUPSALES = 1, @ORDERSTATUSCODE = @ORDERSTATUS, @SALESMETHODTYPECODE = @SALESMETHODTYPECODE;

        declare @REVENUEDATE datetime = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

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

        if @ORDERSTATUS <> 1 begin
            --mark the order as complete

            exec dbo.USP_SALESORDER_COMPLETEORDER @ID, @TRANSACTIONDATE, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE, @REVENUEDATE;
        end        
        else begin
            update dbo.SALESORDER
                set TRANSACTIONDATE = @TRANSACTIONDATE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID;
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch    

    return 0;
end