USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMTICKET_DAILYADMISSION_ADD

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMTICKET_DAILYADMISSION_ADD
(
    @ID uniqueidentifier = null output,
    @ORDERID uniqueidentifier,
    @PRICETYPES xml = null,
    @PROGRAMID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = 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;

    declare @QUANTITY integer;

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

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

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

    declare @CURRENTDATE datetime = getdate();

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

    --For online, we need to make sure that this ticket can be delivered
    if @SALESMETHODTYPECODE = 2
    begin
        declare @SALESMETHODID uniqueidentifier = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(2);
        declare @DELIVERYMETHODEXISTS bit = 0
        exec dbo.USP_DELIVERYMETHOD_INFO_BYSALESMETHODID
            @SALESMETHODID,
            @DELIVERYMETHODEXISTS output;

        if @DELIVERYMETHODEXISTS = 0
            raiserror('ERR_HASNODELIVERYMETHOD', 13, 1);
    end

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

    insert into @ORDERITEMS
    select
        isnull(T.items.value('(SALESORDERITEMID)[1]','uniqueidentifier'), newid()),
        T.items.value('(QUANTITY)[1]','integer'),
        T.items.value('(PRICETYPECODEID)[1]', 'uniqueidentifier'),
        T.items.value('(PRICETYPE)[1]','nvarchar(100)'),
        T.items.value('(FACEPRICE)[1]','decimal(19,2)')  -- 4/20/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.
    from @PRICETYPES.nodes('/PRICETYPES/ITEM') T(items)
    where T.items.value('(QUANTITY)[1]','integer') > 0;

    if not exists (
        select 1
        from @ORDERITEMS ITEMS
        inner join dbo.PROGRAMPRICE on PROGRAMPRICE.PRICETYPECODEID = ITEMS.PRICETYPECODEID and PROGRAMPRICE.PROGRAMID = @PROGRAMID
    )
    begin
        raiserror('The given price type is not available for this program.', 13, 1);
    end

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

        -- 4/20/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

        declare @TOTAL money;

        -- total in new order items
        select @TOTAL = SUM(QUANTITY * PRICE)
        from @ORDERITEMS;

        -- total in existing order items
        select @TOTAL = @TOTAL + isnull(SUM(SALESORDERITEM.TOTAL), 0)
        from dbo.SALESORDERITEM
        left join dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
        where
            SALESORDERITEM.SALESORDERID = @ORDERID
            and (
                SALESORDERITEM.TYPECODE <> 3
                or (
                    SALESORDERITEM.TYPECODE = 3 and SALESORDERITEMFEE.APPLIESTOCODE = 0
                )
            );

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

        --build description
        declare @DESCRIPTION nvarchar(100);
        select @DESCRIPTION = PROGRAM.NAME from dbo.PROGRAM where PROGRAM.ID = @PROGRAMID;

        update dbo.SALESORDERITEM set
            SALESORDERITEM.QUANTITY = SALESORDERITEM.QUANTITY + ITEMS.QUANTITY,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from @ORDERITEMS ITEMS
        where
            SALESORDERID = @ORDERID
            and SALESORDERITEM.ID in
            (
                select SALESORDERITEMTICKET.ID
                from dbo.SALESORDERITEMTICKET
                inner join dbo.SALESORDERITEM as B on SALESORDERITEMTICKET.ID = B.ID
                left join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
                where B.SALESORDERID = @ORDERID and
                    PROGRAMID = @PROGRAMID and
                    SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID and
                    SALESORDERITEMTICKETCOMBINATION.ID is null
            );

        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 1 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
                    PROGRAMID = @PROGRAMID and
                    SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID and
                    SALESORDERITEMTICKETCOMBINATION.ID is null
            );

        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
            ITEMS.ID in
            ( select ID from dbo.SALESORDERITEM where SALESORDERID = @ORDERID );

        exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;

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

        exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;

    end try

    begin catch
        -- 4/20/09, LeeCh, Bug 19897, Starts
        -- catch the datatype money overflow error,

        if ERROR_NUMBER() = 8115
        begin
            raiserror('ERR_TICKETINSERT_MONEYOVERFLOW', 13, 1);
        end

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

        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;