USP_ADD_APPLYTICKETSTOMEMBERSHIP

create procedure dbo.USP_ADD_APPLYTICKETSTOMEMBERSHIP
(
    @ID uniqueidentifier = null output,    
    @SALESORDERITEMID uniqueidentifier,    
    @CHANGEAGENTID uniqueidentifier = null,    
    @SALESORDERID uniqueidentifier = null,
    @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID uniqueidentifier = null,
    @SOURCESALESORDERID uniqueidentifier = null,
    @PROMOTIONNAME nvarchar(255) = '',
    @ITEMS xml = null,
    @TOTALDISCOUNT money = null
)
as
    set nocount on;

    if @ID is null
        set @ID = newid();

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @ITEMSTABLE table (
        APPLIED bit,
        TICKETID uniqueidentifier,
        SALESORDERITEMID uniqueidentifier,
        AMOUNTTOAPPLY money
    )

    insert into @ITEMSTABLE (
        APPLIED,
        TICKETID,
        SALESORDERITEMID,
        AMOUNTTOAPPLY
    )
    select
        T.item.value('(APPLIED)[1]','bit'),
        T.item.value('(TICKETID)[1]','uniqueidentifier'),
        T.item.value('(SALESORDERITEMID)[1]','uniqueidentifier'),
        T.item.value('(AMOUNTTOAPPLY)[1]','decimal(20, 4)')
    from
        @ITEMS.nodes('/ITEMS/ITEM') as T(item)

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

        if not (select count(*) from @ITEMSTABLE where APPLIED = 1) > 0
        begin    
            raiserror('ERR_APPLYTICKETSTOMEMBERSHIP_QUANTITYAPPLYINGZERO', 13, 1);
        end               

        declare @MEMBERSHIPCOST as money;

        select 
            @MEMBERSHIPCOST = (TOTAL - coalesce(DISCOUNTS.TOTALDISCOUNT, 0))
        from
            dbo.SALESORDERITEM
        left outer join (
            select
                sum(AMOUNT) TOTALDISCOUNT,
                SALESORDERITEMID
            from
                dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
            where
                SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = @SALESORDERITEMID and
                SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID <> @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
            group by
                SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
        ) DISCOUNTS on SALESORDERITEM.ID = DISCOUNTS.SALESORDERITEMID
        where
            SALESORDERITEM.ID = @SALESORDERITEMID;

        if @MEMBERSHIPCOST < @TOTALDISCOUNT
            set @TOTALDISCOUNT = @MEMBERSHIPCOST;

        if @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID is null begin
            set @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = @ID;

            insert into dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION (
                ID,
                SALESORDERITEMID,
                PROMOTIONNAME,        
                AMOUNT,             
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values (
                @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID,
                @SALESORDERITEMID,        
                @PROMOTIONNAME,  --change

                @TOTALDISCOUNT,              
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )
        end
        else begin
            -- Hack since this is an add form that edits a record

            set @ID = @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID;

            update dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION set
                AMOUNT = @TOTALDISCOUNT,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID = @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID;

            --delete current promo information    

            delete from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM
            where SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
        end

        --insert new applied items  

        insert into dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM (
            ID,
            SALESORDERITEMMEMBERSHIPITEMPROMOTIONID,
            APPLIEDSALESORDERITEMID,
            QUANTITY,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )   
        select
            newid(),
            @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID,
            ITEMSTABLE.SALESORDERITEMID,
            count(*),
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            @ITEMSTABLE as ITEMSTABLE
        where
            ITEMSTABLE.APPLIED = 1
        group by
            ITEMSTABLE.SALESORDERITEMID

        --apply tickets

        insert into dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMTICKET (
            ID,
            SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMID,
            TICKETID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED,
            AMOUNTAPPLIED
        )    
        select
            newid(),
            APPLIED.ID,
            TICKET.ID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
            ITEMSTABLE.AMOUNTTOAPPLY
        from
            dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION PROMOTION
        inner join
            dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM APPLIED on PROMOTION.ID = APPLIED.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
        inner join
            dbo.TICKET on APPLIED.APPLIEDSALESORDERITEMID = TICKET.SALESORDERITEMTICKETID
        inner join
            @ITEMSTABLE ITEMSTABLE on ITEMSTABLE.TICKETID = TICKET.ID
        where                            
            PROMOTION.ID = @SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
            and ITEMSTABLE.APPLIED = 1
            and TICKET.STATUSCODE in (0, 1)  -- Active, Reserved

            and TICKET.APPLIEDTOMEMBERSHIP = 0

        --set tickets to applied

        update dbo.TICKET set
            APPLIEDTOMEMBERSHIP = ITEMSTABLE.APPLIED,
            APPLIEDTOMEMBERSHIPSALESORDERID = case 
                                                  ITEMSTABLE.APPLIED
                                              when 1 then
                                                  @SALESORDERID
                                              else
                                                  null
                                              end,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            dbo.TICKET
            inner join @ITEMSTABLE ITEMSTABLE
                on ITEMSTABLE.TICKETID = TICKET.ID
    end try

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

    return 0;