USP_DATALIST_PDACCOUNTSEGMENTEVENTMAP

Returns a list of account segment values mapped to events.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DATEFILTER	tinyint	IN	Events
@INCLUDEINACTIVE	bit	IN	Include inactive
@SITEID	uniqueidentifier	IN	Site
@EVENTCATEGORYCODEID	uniqueidentifier	IN	Category
@EVENTLOCATIONID	uniqueidentifier	IN	Location
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SEGMENTNAMEORVALUE	nvarchar(200)	IN
Definition

Copy


CREATE procedure dbo.USP_DATALIST_PDACCOUNTSEGMENTEVENTMAP(
    @ID uniqueidentifier,
    @DATEFILTER tinyint = 0,
    @INCLUDEINACTIVE bit = 0,
    @SITEID uniqueidentifier = null,
    @EVENTCATEGORYCODEID uniqueidentifier = null,
    @EVENTLOCATIONID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
  @SEGMENTNAMEORVALUE nvarchar(200) = null
    )
    as

      set nocount on;
    --sanitize the search values, adding mask chars at beginning and end so it performs a 'contains' style match
      set @SEGMENTNAMEORVALUE = dbo.UFN_TEXTSEARCH_SANITIZE(@SEGMENTNAMEORVALUE,'/',DEFAULT,DEFAULT,DEFAULT) ;

        declare @DATE datetime
        declare @STARTDATE datetime
        declare @ENDDATE datetime

        set @DATE = getdate();

        if @DATEFILTER = 0        -- all
            begin
                set @STARTDATE = null
                set @ENDDATE = null
            end

        if @DATEFILTER = 1        -- this week
            begin
                set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0)
                set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0)
            end

        if @DATEFILTER = 2        -- this month
            begin
                set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0)
                set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0)
            end

        if @DATEFILTER = 3        -- this quarter
            begin
                set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0)
                set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0)
            end

        if @DATEFILTER = 4        --this calendar year
            begin

                set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0)
                set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0)
            end

        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)

        declare @EVENTS table(ID uniqueidentifier)
        insert into @EVENTS
        select ID from dbo.EVENT T2
        where
            (
                (@INCLUDEINACTIVE = 1)
                or
                (T2.ISACTIVE = 1)
            )
        and
            (
                (@STARTDATE is null)
                or
                (T2.STARTDATE between @STARTDATE and @ENDDATE)
            )
        and
            (
                (@SITEID is null)
                or
                (T2.ID in (select EVENTID from dbo.EVENTSITE where EVENTSITE.SITEID = @SITEID))
            )
        and
            (
                (@EVENTCATEGORYCODEID is null)
                or
                (T2.EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID)
            )
        and
            (
                (@EVENTLOCATIONID is null)
                or
                (T2.EVENTLOCATIONID = @EVENTLOCATIONID)
            )

    select * from (
        select
            T1.ID,
            LONGDESCRIPTION,
            T1.ID as LONGDESCRIPTIONID,
            PDACCOUNTSEGMENTVALUEID,
            T3.SHORTDESCRIPTION,
            T1.PDACCOUNTSTRUCTUREID,
            ISDEFAULT
        from dbo.PDACCOUNTSEGMENTMAPPING T1
            left join dbo.PDACCOUNTSEGMENTVALUE T3 on T1.PDACCOUNTSEGMENTVALUEID = T3.ID
        where T1.PDACCOUNTSTRUCTUREID = @ID
            and T1.ISDEFAULT = 1

        union all

        select 
            T1.ID,
            LONGDESCRIPTION,
            isnull(nullif(LONGDESCRIPTIONID,''),T1.ID ) LONGDESCRIPTIONID,
            PDACCOUNTSEGMENTVALUEID,
            T3.SHORTDESCRIPTION,
            T1.PDACCOUNTSTRUCTUREID,
            ISDEFAULT
        from dbo.PDACCOUNTSEGMENTMAPPING T1
            left join dbo.PDACCOUNTSEGMENTVALUE T3 on T1.PDACCOUNTSEGMENTVALUEID = T3.ID
            inner join @EVENTS T2 on nullif(T1.LONGDESCRIPTIONID,'') = T2.ID
        where T1.PDACCOUNTSTRUCTUREID = @ID
            and T1.ISDEFAULT = 0

        union all

        select
            NEWID(),
            V1.LONGDESCRIPTION,
            V1.LONGDESCRIPTIONID,
            null,
            null,
            V1.PDACCOUNTSTRUCTUREID,
            0
        from
            dbo.PDACCOUNTSEGMENTMAPPINGVIEW V1
            inner join @EVENTS T2 on V1.LONGDESCRIPTIONID = T2.ID
        where 
            V1.PDACCOUNTSTRUCTUREID = @ID
            and
            V1.LONGDESCRIPTIONID not in (select LONGDESCRIPTIONID from PDACCOUNTSEGMENTMAPPING where PDACCOUNTSTRUCTUREID = @ID)) ALLITEMS

    where
      (ALLITEMS.LONGDESCRIPTION like @SEGMENTNAMEORVALUE escape '/' or ALLITEMS.SHORTDESCRIPTION like @SEGMENTNAMEORVALUE escape '/')
        order by LONGDESCRIPTION