USP_SALESORDERITEMTICKETS_EDIT

CREATE procedure dbo.USP_SALESORDERITEMTICKETS_EDIT 
(
    @ID uniqueidentifier,
    @EVENTID uniqueidentifier,
    @ORDERID uniqueidentifier,
    @PRICETYPES xml,
    @ISDAILYADMISSION bit,
    @CHANGEAGENTID uniqueidentifier = null,
    @PROGRAMID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @DATA xml = null,
    @OPTIONS xml = null,
    @CALLBACKURL nvarchar(255) = '',
    @SYSTEMTYPENAME nvarchar(255) = '',
    @ASSEMBLYNAME nvarchar(255) = '',
    @ATTRIBUTES xml = null,
    @CATEGORYNAME nvarchar(255) = '',
    @EXPIREDCALLBACKURL nvarchar(255) = '',
    @ACKNOWLEDGEMENT nvarchar(max) = ''
)
as
    set nocount on;

    /* OLD WAY
    declare @NUMBERS table(numbers integer)

    declare @INCREMENT integer
    */

    declare @TOTAL money;
    declare @QUANTITY integer

    select @QUANTITY = SUM(T.items.value('(QUANTITY)[1]', 'integer'))
    from @PRICETYPES.nodes('/PRICETYPES/ITEM') T(items)

    declare @SALESMETHODTYPECODE tinyint = 0
    declare @SALESORDERSTATUSCODE tinyint = 0
    select 
        @SALESMETHODTYPECODE = [SALESMETHODTYPECODE],
        @SALESORDERSTATUSCODE = [STATUSCODE]
    from dbo.[SALESORDER]
    where [ID] = @ORDERID

    --For online, we need to make sure that this ticket can be delivered

    if @SALESMETHODTYPECODE = 2 and @SALESORDERSTATUSCODE <> 7 --unless it's an unresolved order

    begin
        declare @SALESMETHODID uniqueidentifier = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(2)
        declare @HASUNRESTRICTEDDELIVERYMETHOD bit = 0
        declare @EARLIESTVALIDEVENTDATETIMEWITHOFFSET datetimeoffset = null
        declare @DELIVERYMETHODEXISTS bit = 0
        exec dbo.USP_DELIVERYMETHOD_INFO_BYSALESMETHODID 
            @SALESMETHODID, 
            @DELIVERYMETHODEXISTS output, 
            @HASUNRESTRICTEDDELIVERYMETHOD output, 
            @EARLIESTVALIDEVENTDATETIMEWITHOFFSET output

        if @ISDAILYADMISSION = 1
        begin
            if @DELIVERYMETHODEXISTS = 0
                raiserror('ERR_HASNODELIVERYMETHOD', 13, 1)
        end
        else
        begin
            if @DELIVERYMETHODEXISTS = 1
            begin
                if @HASUNRESTRICTEDDELIVERYMETHOD = 0
                begin
                    if @EARLIESTVALIDEVENTDATETIMEWITHOFFSET > (select top 1 [STARTDATETIMEWITHOFFSET] from dbo.[EVENT] where [ID] = @EVENTID)
                        raiserror('ERR_HASNODELIVERYMETHOD', 13, 1)
                end
            end
            else
                raiserror('ERR_HASNODELIVERYMETHOD', 13, 1)
        end
    end

    /*
    set @increment = 1;
    while @increment <= @QUANTITY
    begin
        insert into @NUMBERS
        values (@increment);

        select @increment = @increment + 1;
    end;

    from @ORDERITEMS ITEMS
    inner join @NUMBERS N on ITEMS.QUANTITY >= N.NUMBERS;
    */

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

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

    declare @ORDERITEMS table
        (ID uniqueidentifier,
        QUANTITY int,
        PRICETYPECODEID uniqueidentifier,
        PRICETYPE nvarchar(100),
        PRICE money);

    insert into @ORDERITEMS
    select 
        T.items.value('(ID)[1]','uniqueidentifier'), 
        T.items.value('(QUANTITY)[1]','integer'), 
        T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier'),
        T.items.value('(PRICETYPE)[1]','nvarchar(100)'),
        -- 4/16/09, LeeCh, Bug 19897

        -- Changed decimal(9,2) into decimal (19,2) to

        --     avoid decimal overflow error

        -- See work item fix and attachment for details

        T.items.value('(FACEPRICE)[1]','decimal(19,2)')
    from @PRICETYPES.nodes('/PRICETYPES/ITEM') T(items)
    where T.items.value('(QUANTITY)[1]','integer') > 0;

    declare @contextCache varbinary(128);
    declare @e int;
    declare @DESCRIPTION nvarchar(100);

    begin try
        if @ISDAILYADMISSION <> 1
        begin
            select @PROGRAMID = PROGRAMID from dbo.EVENT where ID = @EVENTID;

            -- Check availability

            if dbo.UFN_SALESORDERTICKET_VALIDQUANTITY(@EVENTID, @ORDERID, @QUANTITY) = 0
            begin
                raiserror('ERR_TICKETINSERT_UNAVAILABLE', 13, 1)
            end

            -- 4/20/09, LeeCh, Bug 19897, Rework

            -- Need to check order items in table SALESORDERITEM

            --     since the @ORDERITEMS has only updated order items for

            --     either scheduled program or daily admission program with

            --     specific event id or program id


            -- 4/16/09, LeeCh, Bug 19897, Starts

            -- Check if total overflows by

                    --     adding the total in new order items and 

            --     the total in existing order items

            -- An overflow error will be processed in the 

            --     catch block


            -- total in new order items

            select @TOTAL = SUM(QUANTITY * PRICE)
            from @ORDERITEMS;

            -- total in existing order items - daily admission programs and scheduled programs but different event

            select @TOTAL = @TOTAL + isnull(SUM(SALESORDERITEM.TOTAL), 0)
            from dbo.SALESORDERITEM
            left join dbo.SALESORDERITEMFEE on
                SALESORDERITEM.ID = SALESORDERITEMFEE.ID
            inner join dbo.SALESORDERITEMTICKET on 
                SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
            where 
                SALESORDERITEM.SALESORDERID = @ORDERID and
                ((not SALESORDERITEMTICKET.PROGRAMID is null) or
                ((not SALESORDERITEMTICKET.EVENTID is null) and 
                SALESORDERITEMTICKET.EVENTID <> @EVENTID)) and
                (SALESORDERITEM.TYPECODE <> 3 or 
                (SALESORDERITEM.TYPECODE = 3 and
                SALESORDERITEMFEE.APPLIESTOCODE = 0))

            -- 4/16/09, LeeCh, Bug 19897, Ends

            -- 4/20/09, LeeCh, Bug 19894, Ends


            --build description


            select @DESCRIPTION = EVENT.NAME from dbo.EVENT where EVENT.ID = @EVENTID;

            -- cache current context information 

            set @contextCache = CONTEXT_INFO();

            -- set CONTEXT_INFO to @CHANGEAGENTID 

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            -- delete discount details                

            delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
            where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID in
            (
                select ID
                from dbo.SALESORDERITEM
                where SALESORDERITEM.SALESORDERID = @ORDERID
            )                

            -- delete any fees attached to items no longer in the table

            delete from dbo.[SALESORDERITEM]
            where ID in
            (
                select SALESORDERITEM.ID from SALESORDERITEM
                inner join dbo.SALESORDERITEMFEE on
                    SALESORDERITEM.ID = SALESORDERITEMFEE.ID
                where 
                    SALESORDERITEMFEE.SALESORDERITEMID in
                    (
                        select SALESORDERITEM.ID from dbo.SALESORDERITEM
                        inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                        left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
                        where SALESORDERID = @ORDERID
                            and SALESORDERITEMTICKET.EVENTID = @EVENTID
                            and SALESORDERITEMTICKETCOMBINATION.ID is null
                        EXCEPT select ID from @ORDERITEMS
                    )
            )

            -- delete any items that no longer exist in the XML table

            delete from dbo.[SALESORDERITEM] where [SALESORDERITEM].ID in 
                (
                    select SALESORDERITEM.ID from dbo.SALESORDERITEM
                    inner join dbo.SALESORDERITEMTICKET on 
                        SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                    left join dbo.SALESORDERITEMTICKETCOMBINATION on 
                        SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
                    where SALESORDERID = @ORDERID
                        and SALESORDERITEMTICKET.EVENTID = @EVENTID
                        and SALESORDERITEMTICKETCOMBINATION.ID is null
                    EXCEPT select ID from @ORDERITEMS
                )    

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

            -- update the items that exist in the XML table and the db

            update dbo.[SALESORDERITEM]
                    set [SALESORDERITEM].[QUANTITY]=ITEMS.[QUANTITY],
                    [SALESORDERITEM].CHANGEDBYID = @CHANGEAGENTID,
                    [SALESORDERITEM].DATECHANGED = @CURRENTDATE,
                    [SALESORDERITEM].PRICE = ITEMS.[PRICE]
            from dbo.[SALESORDERITEM] inner join @ORDERITEMS as [ITEMS] on [SALESORDERITEM].ID = [ITEMS].ID

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into dbo.SALESORDERITEM
                (ID, SALESORDERID, TYPECODE, DESCRIPTION, QUANTITY, PRICE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DATA, OPTIONS, CALLBACKURL, SYSTEMTYPENAME, ASSEMBLYNAME, ATTRIBUTES, CATEGORYNAME, EXPIREDCALLBACKURL, ACKNOWLEDGEMENT)
            select
                ITEMS.ID, 
                @ORDERID, 
                0, 
                @DESCRIPTION + ' - ' + ITEMS.PRICETYPE, 
                ITEMS.QUANTITY, 
                ITEMS.PRICE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE,
                @DATA,
                @OPTIONS,
                @CALLBACKURL,
                @SYSTEMTYPENAME,
                @ASSEMBLYNAME,
                @ATTRIBUTES,
                @CATEGORYNAME,
                @EXPIREDCALLBACKURL,
                @ACKNOWLEDGEMENT
            from @ORDERITEMS ITEMS
            where not exists (select ID from dbo.[SALESORDERITEM] as data where data.ID = [ITEMS].ID)

            if @@Error <> 0
                return 4;

            insert into dbo.SALESORDERITEMTICKET
            (
                ID,
                EVENTID,
                PROGRAMID,
                PROGRAMNAME,
                PROGRAMCATEGORYNAME,
                PRICETYPECODEID,
                PRICE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED, 
                DATECHANGED
            )
            select
                ITEMS.ID, 
                @EVENTID, 
                (select PROGRAMID from dbo.EVENT where ID = @EVENTID),
                coalesce((
                    select [PROGRAM].[NAME] 
                    from dbo.[EVENT]
                    inner join dbo.[PROGRAM]
                        on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                    where [EVENT].[ID] = @EVENTID
                ),''),
                coalesce((
                    select [PROGRAMCATEGORYCODE].[DESCRIPTION]
                    from dbo.[EVENT]
                    inner join dbo.[PROGRAM]
                        on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                    inner join dbo.[PROGRAMCATEGORYCODE]
                        on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
                    where [EVENT].[ID] = @EVENTID
                ),''),
                ITEMS.PRICETYPECODEID,
                ITEMS.PRICE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @ORDERITEMS ITEMS
            where not exists (select ID from dbo.[SALESORDERITEMTICKET] as data where data.ID = [ITEMS].ID)


            exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;


            if @@Error <> 0
                return 4;
        end
        else
        begin
            if @PROGRAMID is null

            select top 1
                @PROGRAMID = PROGRAMID
            from dbo.SALESORDERITEM
            inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
            where SALESORDERITEM.ID = @ID

            -- 4/20/09, LeeCh, Bug 19897, Rework

            -- Need to check order items in table SALESORDERITEM

            --     since the @ORDERITEMS has only updated order items for

            --     either scheduled program or daily admission program with

            --     specific event id or program id


            -- Check if total overflows by

            --     adding the total in new order items and 

            --     the total in existing order items

            -- An overflow error will be processed in the 

            --     catch block


            -- total in new order items

            select @TOTAL = SUM(QUANTITY * PRICE)
            from @ORDERITEMS;

            -- total in existing order items - scheduled programs and daily admission programs but different program

            select @TOTAL = @TOTAL + isnull(SUM(SALESORDERITEM.TOTAL), 0)
            from dbo.SALESORDERITEM
            left join dbo.SALESORDERITEMFEE on
              SALESORDERITEM.ID = SALESORDERITEMFEE.ID
            inner join dbo.SALESORDERITEMTICKET on 
                SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
            where 
                SALESORDERITEM.SALESORDERID = @ORDERID and
                ((not SALESORDERITEMTICKET.EVENTID is null) or
                ((not SALESORDERITEMTICKET.PROGRAMID is null) and 
                SALESORDERITEMTICKET.PROGRAMID <> @PROGRAMID)) and
                (SALESORDERITEM.TYPECODE <> 3 or 
                (SALESORDERITEM.TYPECODE = 3 and
                SALESORDERITEMFEE.APPLIESTOCODE = 0))

            -- 4/20/09, LeeCh, Bug 19894, Ends


            --build description

            select @DESCRIPTION = PROGRAM.NAME from dbo.PROGRAM where PROGRAM.ID = @PROGRAMID;

            -- cache current context information 

            set @contextCache = CONTEXT_INFO();

            -- set CONTEXT_INFO to @CHANGEAGENTID 

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            -- delete discount details                

            delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
            where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID in
            (
                select ID
                from dbo.SALESORDERITEM
                where SALESORDERITEM.SALESORDERID = @ORDERID
            )                    

            -- delete any fees attached to items no longer in the table

            delete from dbo.[SALESORDERITEM]
            where ID in
            (
                select SALESORDERITEM.ID from SALESORDERITEM
                inner join dbo.SALESORDERITEMFEE on
                    SALESORDERITEM.ID = SALESORDERITEMFEE.ID
                where 
                    SALESORDERITEMFEE.SALESORDERITEMID in
                    (
                        select SALESORDERITEM.ID from dbo.SALESORDERITEM
                        inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                        left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
                        where SALESORDERID = @ORDERID
                            and SALESORDERITEMTICKET.PROGRAMID = @PROGRAMID
                            and SALESORDERITEMTICKETCOMBINATION.ID is null
                        EXCEPT select ID from @ORDERITEMS
                    )
            )    

            -- delete any items that no longer exist in the XML table

            delete from dbo.[SALESORDERITEM] where [SALESORDERITEM].ID in 
                (
                    select SALESORDERITEM.ID from dbo.SALESORDERITEM
                    inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                    left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
                    where SALESORDERID = @ORDERID
                        and SALESORDERITEMTICKET.PROGRAMID = @PROGRAMID
                        and SALESORDERITEMTICKETCOMBINATION.ID is null
                    EXCEPT select ID from @ORDERITEMS
                )

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

            -- update the items that exist in the XML table and the db

            update dbo.[SALESORDERITEM]
                    set [SALESORDERITEM].[QUANTITY]=ITEMS.[QUANTITY],
                    [SALESORDERITEM].CHANGEDBYID = @CHANGEAGENTID,
                    [SALESORDERITEM].DATECHANGED = @CURRENTDATE,
                    [SALESORDERITEM].PRICE = [ITEMS].PRICE
            from dbo.[SALESORDERITEM] inner join @ORDERITEMS as [ITEMS] on [SALESORDERITEM].ID = [ITEMS].ID

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into dbo.SALESORDERITEM
                (ID, SALESORDERID, TYPECODE, DESCRIPTION, QUANTITY, PRICE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DATA, OPTIONS, CALLBACKURL, SYSTEMTYPENAME, ASSEMBLYNAME, ATTRIBUTES, CATEGORYNAME, EXPIREDCALLBACKURL, ACKNOWLEDGEMENT)
            select
                ITEMS.ID, 
                @ORDERID, 
                0, 
                @DESCRIPTION + ' - ' + ITEMS.PRICETYPE, 
                ITEMS.QUANTITY, 
                ITEMS.PRICE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE,
                @DATA,
                @OPTIONS,
                @CALLBACKURL,
                @SYSTEMTYPENAME,
                @ASSEMBLYNAME,
                @ATTRIBUTES,
                @CATEGORYNAME,
                @EXPIREDCALLBACKURL,
                @ACKNOWLEDGEMENT
            from @ORDERITEMS ITEMS
            where not exists (select ID from dbo.[SALESORDERITEM] as data where data.ID = [ITEMS].ID)

            if @@Error <> 0
                return 4;

            insert into dbo.SALESORDERITEMTICKET
            (
                ID,
                PROGRAMID,
                PROGRAMNAME,
                PROGRAMCATEGORYNAME,
                PRICETYPECODEID,
                PRICE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED, 
                DATECHANGED
            )
            select
                ITEMS.ID, 
                @PROGRAMID, 
                coalesce((select [PROGRAM].[NAME] from dbo.[PROGRAM] where [PROGRAM].[ID] = @PROGRAMID),''),
                coalesce((
                    select [PROGRAMCATEGORYCODE].[DESCRIPTION]
                    from dbo.[PROGRAM]
                    inner join dbo.[PROGRAMCATEGORYCODE]
                        on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
                    where [PROGRAM].[ID] = @PROGRAMID
                ),''),
                ITEMS.PRICETYPECODEID,
                ITEMS.PRICE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @ORDERITEMS ITEMS
            where not exists (select ID from dbo.[SALESORDERITEMTICKET] as data where data.ID = [ITEMS].ID)

            exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;

            if @@Error <> 0
                return 4;                    
        end

        exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;
        exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;

        exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;

        return 0;

    end try

    begin catch
        -- 4/16/09, LeeCh, Bug 19897, Starts

        -- catch the datatype money overflow error, 

        if ERROR_NUMBER() = 8115
        begin
            raiserror('ERR_TICKETINSERT_MONEYOVERFLOW', 13, 1)
        end
        -- 4/16/09, LeeCh, Bug 19897, Ends


        exec dbo.USP_RAISE_ERROR
        return 1
    end catch        
return 0;