USP_DATALIST_ORDERTICKETSELECT

CREATE procedure dbo.USP_DATALIST_ORDERTICKETSELECT
(
    @ID uniqueidentifier,
    @DATESELECTTYPE tinyint = null,
    @STARTDATE date = null,
    @ENDDATE date = null,
    @NAME nvarchar(512) = null,
    @SHOWPAST bit = null,
    @CATEGORY uniqueidentifier = null
)
as
    set nocount on;

    --@STARTDATE and @ENDDATE must be passed in the default system time zone


    -- Get sales method ID and constituent ID

    declare @CONSTITUENTID uniqueidentifier
    declare @SALESMETHODID uniqueidentifier

    select
        @CONSTITUENTID = SALESORDER.CONSTITUENTID,
        @SALESMETHODID = SALESMETHOD.ID
    from dbo.SALESORDER 
    inner join dbo.SALESMETHOD on SALESORDER.SALESMETHODTYPECODE = SALESMETHOD.TYPECODE
    where SALESORDER.ID = @ID

    set @NAME = '%' + replace(replace(@NAME, '*', '%'), '?', '_') + '%';

    declare @CURRENTDATE date = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate())    

    if @SHOWPAST is null
        set @SHOWPAST = 0

    if @DATESELECTTYPE is null
        set @DATESELECTTYPE = 1;

    set @STARTDATE = case @DATESELECTTYPE
        when 1 then @CURRENTDATE
        when 2 then @CURRENTDATE
        when 3 then @STARTDATE
        when 4 then @STARTDATE
    end;

    set @ENDDATE = case @DATESELECTTYPE
        when 1 then @CURRENTDATE
        when 2 then dateadd(week,1, @CURRENTDATE)
        when 3 then @STARTDATE
        when 4 then @ENDDATE
    end;

    declare @FILTER nvarchar(110);
    set @FILTER = isnull(convert(nvarchar(1), @DATESELECTTYPE), '') + '|' + 
        isnull(convert(nvarchar(50), @STARTDATE, 101), '') + '|' + 
        isnull(convert(nvarchar(50), @ENDDATE, 101), '') + '|' + 
        convert(nvarchar(36), @ID) + '|' +
        convert(nvarchar(1), @SHOWPAST);

    --If past events shouldn't be shown and the start date is before now, fix start date to now

    if @SHOWPAST = 0 and (@STARTDATE < @CURRENTDATE)
        set @STARTDATE = @CURRENTDATE;

    declare @CURRENTDATETIMEWITHOFFSET datetimeoffset = sysdatetimeoffset();
    declare @STARTDATETIMEWITHOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@STARTDATE, 0);
    declare @ENDDATETIMEWITHOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@ENDDATE, 0);

    set @STARTDATETIMEWITHOFFSET = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(@STARTDATETIMEWITHOFFSET);
    set @ENDDATETIMEWITHOFFSET = dbo.UFN_DATETIMEOFFSET_GETLATESTTIME(@ENDDATETIMEWITHOFFSET);

    select 
      [ID], 
      [PROGRAMID],
      [NAME],
      [AVAILABILITY],
      [STARTDATE],
      [STARTTIME], 
      [ENDTIME],
      [PROGRAMCATEGORY],                    
      [ISDAILYADMISSION],
      [ISCOMBINATION],
      [EVENTSTATUSCODE],
      convert(nvarchar(50), [ID]) + '|' + @FILTER as [IDWITHFILTER]
    from dbo.UFN_SALESORDER_PROGRAMTICKETSELECT_DATETIMEOFFSET
          (
            @SALESMETHODID,
            @CONSTITUENTID,
            @DATESELECTTYPE,
            @STARTDATETIMEWITHOFFSET,
            @ENDDATETIMEWITHOFFSET,
            @NAME,
            @SHOWPAST,
            @CURRENTDATETIMEWITHOFFSET
          )
    order by
        [ISCOMBINATION] desc,
        [ISDAILYADMISSION] desc,
        [NAME] asc,
        [STARTDATE] asc,
        [STARTTIME] asc;