USP_SALESORDER_CLEAR

CREATE procedure dbo.USP_SALESORDER_CLEAR
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime = null
)
as
    set nocount on;

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

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

    begin try
        exec dbo.USP_SALESORDER_ISCOMPLETE_RAISERROR @ID, @EXCLUDEGROUPSALES = 1;

        declare @ORDERSTATUS tinyint;
        declare @SALESMETHODTYPECODE tinyint;
        declare @REVENUEID uniqueidentifier;

        select
            @ORDERSTATUS = STATUSCODE,
            @SALESMETHODTYPECODE = SALESMETHODTYPECODE,
            @REVENUEID = REVENUEID
        from
            dbo.SALESORDER
        where
            ID = @ID;

        exec dbo.USP_SALESORDERTAXEXEMPTINFO_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

        declare @e int;
        declare @contextCache varbinary(128);

        set @contextCache = CONTEXT_INFO();

        if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID;

        -- Update quantity for merchandise items

        with MERCHANDISEITEMS_CTE as (
            select 
                MERCHANDISEPRODUCTINSTANCEID,
                QUANTITY
            from dbo.SALESORDERITEMMERCHANDISE SOIM
            inner join dbo.SALESORDERITEM SOI on SOIM.ID = SOI.ID
            where SOI.SALESORDERID = @ID
        )
        update dbo.MERCHANDISEPRODUCTINSTANCE set
            ONHANDQUANTITY = ONHANDQUANTITY + MI.QUANTITY,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from MERCHANDISEITEMS_CTE MI
        where ID = MI.MERCHANDISEPRODUCTINSTANCEID

        --Remove SalesOrderNotes (before items, since notes can have an item reference)

        delete from dbo.SALESORDERNOTE
        where SALESORDERID = @ID

        -- Remove taxes before removing fees and tickets

        delete from dbo.SALESORDERITEMTAX
        where TAXITEMID in
        (select ID from dbo.SALESORDERITEM where SALESORDERID = @ID)

        -- Fees need to be deleted before the item they are linked to  

        delete from dbo.SALESORDERITEM
        where SALESORDERID = @ID and TYPECODE = 3;

        -- Fees are no longer considered deleted

        delete from dbo.SALESORDERFEEDELETED
        where SALESORDERID = @ID;

        -- Drop the discounts

        delete from dbo.SALESORDERMANUALDISCOUNT
        where SALESORDERID = @ID;

        delete from dbo.SALESORDERADJUSTABLEDISCOUNT
        where SALESORDERID = @ID;

        delete from dbo.SALESORDERMEMBERSHIPPROMO
        where SALESORDERID = @ID;

        --Drop the discount limits

        delete from dbo.[SALESORDERDISCOUNTLIMITOVERRIDE]
        where [SALESORDERID] = @ID;

        -- Clear any sponsorship opportunity reservations.

        declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier

        declare sponsorships_cursor cursor LOCAL FAST_FORWARD for 
        select SPONSORSHIPOPPORTUNITYID 
        from dbo.SALESORDERITEMSPONSORSHIP SOIS
        inner join dbo.SALESORDERITEM SOI on SOIS.ID = SOI.ID
        where SOI.SALESORDERID = @ID

        OPEN sponsorships_cursor

        FETCH NEXT FROM sponsorships_cursor
        INTO @SPONSORSHIPOPPORTUNITYID

        while @@FETCH_STATUS = 0
        begin
            exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK @SPONSORSHIPOPPORTUNITYID, 0

            FETCH NEXT FROM sponsorships_cursor
            INTO @SPONSORSHIPOPPORTUNITYID
        end

        close sponsorships_cursor
        deallocate sponsorships_cursor

        -- Registrants

        declare @REGISTRANTS table (
            ID uniqueidentifier,
            GUESTOFREGISTRANTID uniqueidentifier
        );

        declare @HASPREREGISTEREDTICKETS bit = 0;

        insert into @REGISTRANTS
        (
            ID,
            GUESTOFREGISTRANTID
        )
        select
            REGISTRANT.ID,
            REGISTRANT.GUESTOFREGISTRANTID
        from
            dbo.SALESORDERITEM
        inner join
            dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEM.ID
        inner join
            dbo.REGISTRANT on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
        where
            SALESORDERITEM.SALESORDERID = @ID;

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

            insert into @REGISTRANTS
            (
                ID,
                GUESTOFREGISTRANTID
            )
            select
                GUESTOFREGISTRANTID,
                null
            from
                @REGISTRANTS
            where
                GUESTOFREGISTRANTID not in (select ID from @REGISTRANTS);
        end

        delete from dbo.SALESORDERITEM
        where SALESORDERID = @ID;

        if @HASPREREGISTEREDTICKETS = 1 begin
            -- Delete guests

            delete from dbo.REGISTRANT
            where ID in (select ID from @REGISTRANTS where GUESTOFREGISTRANTID is not null);

            -- Delete hosts if they don't have anymore guests and don't have their own ticket

            delete from dbo.REGISTRANT
            where ID in (
                select REGISTRANTSTODELETE.ID from @REGISTRANTS as REGISTRANTSTODELETE
                left outer join dbo.REGISTRANT on REGISTRANT.GUESTOFREGISTRANTID = REGISTRANTSTODELETE.ID
                left outer join dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANTSTODELETE.ID
                where REGISTRANT.ID is null and SALESORDERITEMTICKETREGISTRANT.ID is null
            );

            -- Update hosts who still have guests and don't have a ticket and delete their preferences

            update dbo.REGISTRANT set
                WILLNOTATTEND = 1,
                ATTENDED = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID in (
                select REGISTRANTSTODELETE.ID from @REGISTRANTS as REGISTRANTSTODELETE
                left outer join dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANTSTODELETE.ID
                where SALESORDERITEMTICKETREGISTRANT.ID is null
            )
            and WILLNOTATTEND <> 1;

            -- TODO: Make this more efficient

            delete from dbo.REGISTRANTPREFERENCE
            where REGISTRANTID in (
                select REGISTRANTSTODELETE.ID from @REGISTRANTS as REGISTRANTSTODELETE
                left outer join dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANTSTODELETE.ID
                where SALESORDERITEMTICKETREGISTRANT.ID is null
            );
        end

        -- Drop auto apply discount information                    

        delete from dbo.SALESORDERITEMDISCOUNTOPTION
        where SALESORDERID = @ID;            

        delete from dbo.SALESORDERAVAILABLEDISCOUNTGROUPCOMBINATION
        where SALESORDERID = @ID;

        delete from dbo.SALESORDERDISCOUNTSCENARIOCOMBINATION
        where SALESORDERID = @ID;

        delete from dbo.SALESORDERITEMSCENARIOSDONE
        where SALESORDERID = @ID;

        -- Reset the delivery method if there is only one

        declare @DELIVERYMETHODID uniqueidentifier;

        --Default delivery method on clear (except in case of online sales)

        if @SALESMETHODTYPECODE <> 2 begin
            select @DELIVERYMETHODID = DELIVERYMETHOD.ID
            from dbo.SALESMETHODDELIVERYMETHOD
                inner join dbo.SALESMETHOD on SALESMETHOD.ID = SALESMETHODDELIVERYMETHOD.SALESMETHODID
                inner join dbo.DELIVERYMETHOD on DELIVERYMETHOD.ID = SALESMETHODDELIVERYMETHOD.DELIVERYMETHODID
            where
                SALESMETHOD.TYPECODE = @SALESMETHODTYPECODE and
                DELIVERYMETHOD.ISACTIVE = 1 and
                SALESMETHODDELIVERYMETHOD.ISDEFAULT = 1
        end

        -- Delete any information about BBPAY transactions associated with this order

        delete from dbo.SALESORDERBBPAYTRANSACTION 
        where SALESORDERID = @ID;

        -- Blank out the order


        update dbo.SALESORDER set
            CONSTITUENTID = case 
                when @SALESMETHODTYPECODE = 2 then
                    CONSTITUENTID
                else null
            end,
            RECIPIENTID = null,
            ADDRESSID = null,
            PHONEID = null,
            EMAILADDRESSID = null,
            DELIVERYMETHODID = @DELIVERYMETHODID,
            TRANSACTIONDATE = null,
            SAMEASPATRON = 1,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID

        if not @contextCache is null
            set CONTEXT_INFO @contextCache

        select @e=@@error;

        if @e<>0 return -456; --always return non-zero sp result if an error occurs


        if @REVENUEID is not null begin
            exec dbo.USP_REVENUE_DELETE @REVENUEID, @CHANGEAGENTID
        end

        declare @PAYMENTID uniqueidentifier;

        declare payments_cursor cursor LOCAL FAST_FORWARD for 
        select PAYMENTID from dbo.SALESORDERPAYMENT where SALESORDERID = @ID;

        OPEN payments_cursor

        FETCH NEXT FROM payments_cursor
        INTO @PAYMENTID

        while @@FETCH_STATUS = 0 begin
            exec dbo.USP_REVENUE_DELETE @PAYMENTID, @CHANGEAGENTID

            FETCH NEXT FROM payments_cursor
            INTO @PAYMENTID
        end

        close payments_cursor
        deallocate payments_cursor

        -- If this was a reserved or unresolved order, we need to delete it completely.

        if @ORDERSTATUS in (6,7) begin
            exec dbo.USP_SALESORDER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
            --delete the sponsorship row in cms_sessionvariablebackup

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

    end try

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

    return 0;