USP_DATAFORMTEMPLATE_ADD_BATCHTICKETPRINTJOB

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHTICKETPRINTJOB
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier,
    @ITEMS xml,
    @WORKSTATIONMACHINENAME nvarchar(255),
    @PRINTERNAME nvarchar(255),
    @STARTDATE date = null,
    @ENDDATE date = null,
    @DELIVERYMETHODID uniqueidentifier = null,
    @DOCUMENTID uniqueidentifier = null,
    @INCLUDEDAILYADMISSION bit = 0,
    @GROUPBYCODE tinyint = 0  -- Event
)
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 @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);
    declare @WORKSTATIONID uniqueidentifier;

    set @WORKSTATIONID = dbo.UFN_WORKSTATION_GETIDBYMACHINENAME(@WORKSTATIONMACHINENAME);

    begin try
        declare @SELECTEDITEMS table (
            ID uniqueidentifier,
            ITEMTYPE tinyint
        );

        insert into @SELECTEDITEMS (
            ID,
            ITEMTYPE
        )
        select
            T.item.value('(ID)[1]','uniqueidentifier') as ID,
            T.item.value('(ITEMTYPE)[1]','tinyint') as ITEMTYPE
        from
            @ITEMS.nodes('ITEMS/ITEM') as T(item);

        insert into dbo.BATCHTICKETPRINTJOB (
            ID,
            APPUSERID,
            PRINTERNAME,
            PRINTDATEWITHTIMEOFFSET,
            DELIVERYMETHODID,
            DOCUMENTID,
            GROUPBYCODE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values (
            @ID,
            @CURRENTAPPUSERID,
            @PRINTERNAME,
            @CURRENTDATETIMEOFFSET,
            @DELIVERYMETHODID,
            @DOCUMENTID,
            @GROUPBYCODE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

        declare    @TICKETSTOPRINT table (
            TICKETPRINTINFOID uniqueidentifier,
            TICKETID uniqueidentifier,
            SEQUENCE int identity
        );

        insert into @TICKETSTOPRINT (
            TICKETPRINTINFOID,
            TICKETID
        )
        select
            newid(),
            TICKET.ID
        from
            dbo.TICKET with (updlock)
        inner join
            dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
        inner join
            dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        inner join
            dbo.DELIVERYMETHOD on DELIVERYMETHOD.ID =  SALESORDER.DELIVERYMETHODID
        inner join
            dbo.PROGRAM on PROGRAM.ID = TICKET.PROGRAMID
        left outer join
            dbo.EVENT on EVENT.ID = TICKET.EVENTID
        inner join
            @SELECTEDITEMS as SELECTEDITEMS
                on 
                    SELECTEDITEMS.ID in (TICKET.PROGRAMID, TICKET.EVENTID, SALESORDER.RECIPIENTID)
                    or (
                        SALESORDER.RECIPIENTID is null
                        and (
                            SELECTEDITEMS.ID = SALESORDER.CONSTITUENTID
                            or (
                                SELECTEDITEMS.ITEMTYPE = 1  -- Patron
                                and SELECTEDITEMS.ID is null
                                and SALESORDER.CONSTITUENTID is null
                            )
                        )
                    )
        left outer join
            dbo.CONSTITUENT on CONSTITUENT.ID = isnull(SALESORDER.RECIPIENTID, SALESORDER.CONSTITUENTID)
        where
            TICKET.STATUSCODE in (0,1)  -- Active, Reserved
            and TICKET.ID not in (select TICKETID from dbo.TICKETPRINTINFO)  -- Not printed
            and (
                (@DELIVERYMETHODID is null and DELIVERYMETHOD.PRINTCODE = 0)  -- Defer for later printing
                or SALESORDER.DELIVERYMETHODID = @DELIVERYMETHODID
            )
            and (@INCLUDEDAILYADMISSION = 1 or TICKET.EVENTID is not null)
            and (
                TICKET.EVENTID is null
                or (
                    (
                        @STARTDATE is null
                        or EVENT.STARTDATE >= @STARTDATE
                    )
                    and (
                        @ENDDATE is null
                        or EVENT.ENDDATE <= @ENDDATE
                    )
                )
            )
            and PROGRAM.ISACTIVE = 1
            and PROGRAM.ID in (
                select PROGRAMDOCUMENT.PROGRAMID
                from dbo.PROGRAMDOCUMENT
                inner join dbo.DOCUMENT on DOCUMENT.ID = PROGRAMDOCUMENT.DOCUMENTID
                where DOCUMENT.ISACTIVE = 1
            )
        order by
            case when @GROUPBYCODE = 1 then CONSTITUENT.KEYNAME end,
            case when @GROUPBYCODE = 1 then CONSTITUENT.FIRSTNAME end,
            case when @GROUPBYCODE = 1 then CONSTITUENT.ID end,
            case when @GROUPBYCODE = 1 then SALESORDER.TRANSACTIONDATE end,
            case when @GROUPBYCODE = 1 then SALESORDER.ID end,
            EVENT.NAME,
            EVENT.STARTDATETIME,
            EVENT.ID,
            PROGRAM.NAME,
            PROGRAM.ID,
            case when @GROUPBYCODE = 0 then CONSTITUENT.KEYNAME end,
            case when @GROUPBYCODE = 0 then CONSTITUENT.FIRSTNAME end,
            case when @GROUPBYCODE = 0 then CONSTITUENT.ID end,
            case when @GROUPBYCODE = 0 then SALESORDER.TRANSACTIONDATE end,
            case when @GROUPBYCODE = 0 then SALESORDER.ID end;

        if @@rowcount = 0
            raiserror('BBERR_NOTICKETSTOPRINT', 13, 1);

        insert into dbo.TICKETPRINTINFO (
            ID,
            TICKETID,
            APPUSERID,
            WORKSTATIONID,
            PRINTDATEWITHTIMEOFFSET,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            TICKETPRINTINFOID,
            TICKETID,
            @CURRENTAPPUSERID,
            @WORKSTATIONID,
            @CURRENTDATETIMEOFFSET,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            @TICKETSTOPRINT
        order by
            SEQUENCE;

        insert into dbo.BATCHTICKETPRINTJOBTICKETLINK (
            ID,
            BATCHTICKETPRINTJOBID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            TICKETPRINTINFOID,
            @ID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            @TICKETSTOPRINT;
    end try

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

    return 0;