USP_DATAFORMTEMPLATE_ADD_DOCUMENTANDTICKETPRINT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_DOCUMENTANDTICKETPRINT
(
    @ID uniqueidentifier = null output,
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @DOCUMENTS xml = null,
    @REPRINTREASONCODE uniqueidentifier = 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 = getdate();

    begin try
        --238198 Occasionally, in hosting, we are seeing the same ticket (same TICKET.ID) appearing multiple times in the same print job (TICKETPRINTINFO.REPRINTJOB)
        --Making changes to prevent this
        declare @TICKETSTOPRINT table (
            TICKETID uniqueidentifier,
            EVENTDATE datetime null,
            EVENTTIME dbo.UDT_HOURMINUTE null,
            NAME nvarchar(255) null,
            BARCODE bigint null,
            HIDDENPRINTCOUNT integer null
        )
        insert into @TICKETSTOPRINT
        select 
            ID,
            EVENTDATE,
            EVENTTIME,
            NAME,
            BARCODE,
            HIDDENPRINTCOUNT
        from dbo.UFN_DOCUMENT_GETSALESDOCUMENTSANDTICKETSTOPRINT_FROMITEMLISTXML(@DOCUMENTS)
        where 
            [TOPRINT] = 1 and
            TYPECODE = 0

        insert into dbo.TICKETPRINTINFO
        (
            ID,
            TICKETID,
            APPUSERID,
            TICKETREPRINTREASONCODEID,
            REPRINTJOB,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            newid(),
            [TICKET].ID,
            @CURRENTAPPUSERID,
            case
                when [PRINTTICKETS].[HIDDENPRINTCOUNT] = 0 then
                    null
                else
                    @REPRINTREASONCODE
            end,
            @ID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from dbo.TICKET
        inner join dbo.SALESORDERITEMTICKET with (nolock) on
            TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
        inner join dbo.SALESORDERITEM with (nolock) on
            SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
        cross apply (
            --238198 
            select top 1
                EVENTDATE,
                EVENTTIME,
                NAME, 
                BARCODE,
                HIDDENPRINTCOUNT
            from @TICKETSTOPRINT [TICKETS]
            where [TICKETS].TICKETID = TICKET.ID
        ) [PRINTTICKETS]
        where 
            SALESORDERITEM.SALESORDERID = @SALESORDERID and
            --238198
            not exists(select 1 from dbo.TICKETPRINTINFO where TICKETID = TICKET.ID and REPRINTJOB = @ID)
        order by
            [PRINTTICKETS].EVENTDATE,
            [PRINTTICKETS].EVENTTIME,
            [PRINTTICKETS].NAME,
            [PRINTTICKETS].BARCODE

        -- handle inserting the data
        insert into dbo.DOCUMENTPRINTINGHISTORY
        (
            ID,
            SALESORDERID,
            TYPECODE,
            APPUSERID,
            REPRINTJOBID,
            CREDITCARDPAYMENTMETHODDETAILID,
            CREDITID,
            CREDITPAYMENTID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            newid(),
            @SALESORDERID,
            TYPECODE,
            @CURRENTAPPUSERID,        
            @ID,
            case
                when TYPECODE = 2 then
                    CREDITCARDPAYMENTMETHODDETAILID
                else
                    null
            end as CREDITCARDPAYMENTMETHODDETAILID,
            case
                when TYPECODE = 5 or TYPECODE = 6 then
                    CREDITID
                else
                    null
            end as CREDITID,
            case
                when TYPECODE = 6 then
                    CREDITPAYMENTID
                else
                    null
            end as CREDITPAYMENTID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            dbo.UFN_DOCUMENT_GETSALESDOCUMENTSANDTICKETSTOPRINT_FROMITEMLISTXML(@DOCUMENTS)
        where TOPRINT = 1 and
            TYPECODE > 0
        order by
            TYPECODE;
    end try

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

    return 0;