USP_DATALIST_ORGANIZATION_CALENDAR
Returns all items on the organization calendar as a datalist.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ITEMTYPE | tinyint | IN | Type |
| @STARTDATE | datetime | IN | Start date |
| @ENDDATE | datetime | IN | End date |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_ORGANIZATION_CALENDAR(
@ITEMTYPE tinyint = 0,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
as
set nocount on;
declare @CURRENTDATE date;
set @CURRENTDATE = getdate();
if @ITEMTYPE = 0 --Appeal mailings
begin
select
MKTSEGMENTATION.ID,
null as PARENTID,
MKTSEGMENTATION.MAILDATE as STARTDATE,
null as ENDDATE,
MKTSEGMENTATION.NAME,
'Appeal mailing' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.MKTSEGMENTATION
left outer join
dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
left join
dbo.CHANGEAGENT on MKTSEGMENTATION.ADDEDBYID = CHANGEAGENT.ID
where
( (APPEALMAILING.ID is not null) )
and (MKTSEGMENTATION.MAILDATE is not null)
and
(
( (MKTSEGMENTATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (MKTSEGMENTATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (MKTSEGMENTATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
)
union all
select
APPEALMAILINGTASK.SEGMENTATIONID,
null as PARENTID,
dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) as STARTDATE,
null as ENDDATE,
APPEALMAILINGTASK.SUBJECT as NAME,
'Appeal mailing task' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
1 as TYPECODE
from
dbo.APPEALMAILINGTASK
inner join
dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = APPEALMAILINGTASK.SEGMENTATIONID
left outer join
dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
left join
dbo.CHANGEAGENT on APPEALMAILINGTASK.ADDEDBYID = CHANGEAGENT.ID
where
(APPEALMAILINGTASK.DATEDUE <> '00000000')
and
(
( (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
)
order by STARTDATE
end
else if @ITEMTYPE = 1 --Calendar items
begin
select
CALENDARITEM.ID,
null as PARENTID,
CALENDARITEM.STARTDATE,
CALENDARITEM.ENDDATE,
CALENDARITEM.NAME,
'Calendar item' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.CALENDARITEM
left join
dbo.CHANGEAGENT on CALENDARITEM.ADDEDBYID = CHANGEAGENT.ID
where
(
( (CALENDARITEM.STARTDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (CALENDARITEM.STARTDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (CALENDARITEM.ENDDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
or ( (CALENDARITEM.ENDDATE between @STARTDATE and @ENDDATE))
or ( (CALENDARITEM.STARTDATE <= @STARTDATE) and (CALENDARITEM.ENDDATE >= @ENDDATE or @ENDDATE is null))
or ( (CALENDARITEM.STARTDATE <= @ENDDATE) and (@STARTDATE is null))
)
order by STARTDATE
end
else if @ITEMTYPE = 2 --Events and invitations
begin
select --Events
EVENT.ID,
null as PARENTID,
EVENT.STARTDATE,
EVENT.ENDDATE,
EVENT.NAME,
'Event' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.EVENT
left outer join
dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
left join
dbo.CHANGEAGENT on EVENT.ADDEDBYID = CHANGEAGENT.ID
where
(
(@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is not null) and (EVENT.STARTDATE <= @ENDDATE and @STARTDATE <= EVENT.ENDDATE))
or ( (EVENT.STARTDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (EVENT.ENDDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
or ( (EVENT.STARTDATE <= @ENDDATE) and (@STARTDATE is null) )
or ( (EVENT.ENDDATE >= @STARTDATE) and (@ENDDATE is null) )
)
and EVENT.ISACTIVE = 1
and EVENT.PROGRAMID is null
union all
select --Invitations
INVITATION.ID,
INVITATION.EVENTID as PARENTID,
INVITATION.MAILDATE as STARTDATE,
null as ENDDATE,
INVITATION.NAME,
'Event invitation' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.INVITATION
inner join
dbo.EVENT on EVENT.ID = INVITATION.EVENTID
left outer join
dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
left join
dbo.CHANGEAGENT on INVITATION.ADDEDBYID = CHANGEAGENT.ID
where
(
( (INVITATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (INVITATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (INVITATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
)
and EVENT.ISACTIVE = 1
and EVENT.PROGRAMID is null
order by STARTDATE
end
else if @ITEMTYPE = 3 --Marketing efforts
begin
select
MKTSEGMENTATION.ID,
null as PARENTID,
MKTSEGMENTATION.MAILDATE as STARTDATE,
null as ENDDATE,
MKTSEGMENTATION.NAME,
'Marketing effort' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.MKTSEGMENTATION
left join
dbo.CHANGEAGENT on MKTSEGMENTATION.ADDEDBYID = CHANGEAGENT.ID
where
(
( (MKTSEGMENTATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (MKTSEGMENTATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (MKTSEGMENTATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
)
and (MKTSEGMENTATION.ID not in (select ID from dbo.APPEALMAILING))
order by STARTDATE
end
else if @ITEMTYPE = 4 --Plan activities
begin
select
M.ID,
M.PARENTMARKETINGPLANITEMID PARENTID,
dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.STARTDATE) as STARTDATE,
dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.ENDDATE) as ENDDATE,
M.NAME,
'Plan activity' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.[MKTMARKETINGPLANITEM] M
left join
dbo.MKTMARKETINGPLANITEMTEMPLATEITEM on (M.MARKETINGPLANID=MKTMARKETINGPLANITEMTEMPLATEITEM.MARKETINGPLANID and M.LEVEL=MKTMARKETINGPLANITEMTEMPLATEITEM.LEVEL)
left join
dbo.MKTMARKETINGPLANITEM on MKTMARKETINGPLANITEM.ID = M.[PARENTMARKETINGPLANITEMID]
left join
dbo.CHANGEAGENT on M.ADDEDBYID = CHANGEAGENT.ID
where
(
(@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is null) and (dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.STARTDATE) >= @STARTDATE))
or ((@STARTDATE is null and @ENDDATE is not null) and (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(M.STARTDATE) <= @ENDDATE))
or ((@STARTDATE is not null and @ENDDATE is not null) and (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(M.STARTDATE) <= @ENDDATE and (@STARTDATE <= dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.ENDDATE))))
or ((dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(M.STARTDATE) <= @ENDDATE) and (@STARTDATE is null))
or ((dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.ENDDATE) >= @STARTDATE) and (@ENDDATE is null))
)
union all
select
MKTMARKETINGPLANITEMTASK.ID,
MKTMARKETINGPLANITEMTASK.MARKETINGPLANITEMID PARENTID,
MKTMARKETINGPLANITEMTASK.DUEDATE as STARTDATE,
null as ENDDATE,
MKTMARKETINGPLANITEMTASK.SUBJECT as NAME,
'Plan activity task' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
1 as TYPECODE
from
dbo.[MKTMARKETINGPLANITEMTASK]
inner join
dbo.[MKTMARKETINGPLANITEM] M on (M.[ID]=MKTMARKETINGPLANITEMTASK.[MARKETINGPLANITEMID])
left join
dbo.CHANGEAGENT on MKTMARKETINGPLANITEMTASK.ADDEDBYID = CHANGEAGENT.ID
where
(
(@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is not null) and (dbo.UFN_DATE_FROMFUZZYDATE(MKTMARKETINGPLANITEMTASK.DUEDATE) between @STARTDATE and @ENDDATE))
or ((@ENDDATE is null and @STARTDATE is not null) and (dbo.UFN_DATE_LATESTFROMFUZZYDATE(MKTMARKETINGPLANITEMTASK.DUEDATE) >= @STARTDATE))
or ((@STARTDATE is null and @ENDDATE is not null) and (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(MKTMARKETINGPLANITEMTASK.DUEDATE) <= @ENDDATE))
)
union all
select
MKTMARKETINGPLANITEM.ID,
MKTMARKETINGPLANITEM.PARENTMARKETINGPLANITEMID PARENTID,
convert(nvarchar(8),MKTMARKETINGPLANITEM.[MAILDATE],112) as STARTDATE,
convert(nvarchar(8),MKTMARKETINGPLANITEM.[MAILDATE],112) as ENDDATE,
MKTMARKETINGPLANITEM.NAME + ' - Mail date' as NAME,
'Plan activity' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.[MKTMARKETINGPLANITEM]
left join
dbo.MKTMARKETINGPLANITEMTEMPLATEITEM on (MKTMARKETINGPLANITEM.MARKETINGPLANID=MKTMARKETINGPLANITEMTEMPLATEITEM.MARKETINGPLANID and MKTMARKETINGPLANITEM.LEVEL=MKTMARKETINGPLANITEMTEMPLATEITEM.LEVEL)
left join
dbo.CHANGEAGENT on MKTMARKETINGPLANITEM.ADDEDBYID = CHANGEAGENT.ID
where
(
((@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is null) and (MAILDATE >= @STARTDATE))
or ((@STARTDATE is null and @ENDDATE is not null) and (MAILDATE <= @ENDDATE))
or ((@STARTDATE is not null and @ENDDATE is not null) and (MAILDATE <= @ENDDATE and (@STARTDATE <= MAILDATE ))))
and MKTMARKETINGPLANITEM.MAILDATE is not null
)
order by STARTDATE
end
else if @ITEMTYPE = 5 --Programs
begin
select
EVENT.ID as [ID],
null as PARENTID,
EVENT.STARTDATE,
EVENT.ENDDATE,
EVENT.NAME,
'Program event' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.EVENT
left join dbo.CHANGEAGENT on EVENT.ADDEDBYID = CHANGEAGENT.ID
where
(
(@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is not null) and (EVENT.STARTDATE <= @ENDDATE and @STARTDATE <= EVENT.ENDDATE))
or ( (EVENT.STARTDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (EVENT.ENDDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
or ( (EVENT.STARTDATE <= @ENDDATE) and (@STARTDATE is null) )
or ( (EVENT.ENDDATE >= @STARTDATE) and (@ENDDATE is null) )
)
and
EVENT.PROGRAMID is not null
order by
STARTDATE
end
else if @ITEMTYPE = 6 --Reservations
begin
select RESERVATION.ID,
null as PARENTID,
ARRIVALDATE as STARTDATE,
isnull((select top 1 ITINERARYITEM.ENDDATE
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARY.RESERVATIONID = RESERVATION.ID
order by ENDDATE desc), ARRIVALDATE) ENDDATE,
NAME,
'Reservation' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from dbo.RESERVATION
inner join
dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
left join
dbo.CHANGEAGENT on RESERVATION.ADDEDBYID = CHANGEAGENT.ID
where
(STATUSCODE <> 1 and STATUSCODE <> 5)
and (
( (ARRIVALDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (ARRIVALDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (ARRIVALDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
or ( isnull((select top 1 ITINERARYITEM.ENDDATE
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARY.RESERVATIONID = RESERVATION.ID
order by ENDDATE desc), ARRIVALDATE) between @STARTDATE and @ENDDATE)
or ( (isnull((select top 1 ITINERARYITEM.ENDDATE
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARY.RESERVATIONID = RESERVATION.ID
order by ENDDATE desc), ARRIVALDATE) <=@ENDDATE) and (@STARTDATE is null))
or ( (isnull((select top 1 ITINERARYITEM.ENDDATE
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARY.RESERVATIONID = RESERVATION.ID
order by ENDDATE desc), ARRIVALDATE) >= @STARTDATE) and (@ENDDATE is null))
)
order by STARTDATE
end
else -- All items
begin
select
MKTSEGMENTATION.ID,
null as PARENTID,
MKTSEGMENTATION.MAILDATE as STARTDATE,
null as ENDDATE,
MKTSEGMENTATION.NAME,
'Appeal mailing' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.MKTSEGMENTATION
left outer join
dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
left join
dbo.CHANGEAGENT on MKTSEGMENTATION.ADDEDBYID = CHANGEAGENT.ID
where
( (APPEALMAILING.ID is not null) )
and (MKTSEGMENTATION.MAILDATE is not null)
and
(
( (MKTSEGMENTATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (MKTSEGMENTATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (MKTSEGMENTATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
)
union all
select
APPEALMAILINGTASK.SEGMENTATIONID,
null as PARENTID,
dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) as STARTDATE,
null as ENDDATE,
APPEALMAILINGTASK.SUBJECT as NAME,
'Appeal mailing task' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
1 as TYPECODE
from
dbo.APPEALMAILINGTASK
inner join
dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = APPEALMAILINGTASK.SEGMENTATIONID
left outer join
dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
left join
dbo.CHANGEAGENT on APPEALMAILINGTASK.ADDEDBYID = CHANGEAGENT.ID
where
(APPEALMAILINGTASK.DATEDUE <> '00000000')
and
(
( (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
)
union all
select
EVENT.ID,
null as PARENTID,
EVENT.STARTDATE,
EVENT.ENDDATE,
EVENT.NAME,
'Event' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.EVENT
left outer join
dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
left join
dbo.CHANGEAGENT on EVENT.ADDEDBYID = CHANGEAGENT.ID
where
(
(@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is not null) and (EVENT.STARTDATE <= @ENDDATE and @STARTDATE <= EVENT.ENDDATE))
or ( (EVENT.STARTDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (EVENT.ENDDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
or ( (EVENT.STARTDATE <= @ENDDATE) and (@STARTDATE is null) )
or ( (EVENT.ENDDATE >= @STARTDATE) and (@ENDDATE is null) )
)
and EVENT.ISACTIVE = 1
and EVENT.PROGRAMID is null
union all
select
INVITATION.ID,
INVITATION.EVENTID as PARENTID,
INVITATION.MAILDATE as STARTDATE,
null as ENDDATE,
INVITATION.NAME,
'Event invitation' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.INVITATION
inner join
dbo.EVENT on EVENT.ID = INVITATION.EVENTID
left outer join
dbo.EVENTCOORDINATOR on EVENTCOORDINATOR.EVENTID = EVENT.ID
left join
dbo.CHANGEAGENT on INVITATION.ADDEDBYID = CHANGEAGENT.ID
where
(
( (INVITATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (INVITATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (INVITATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
)
and EVENT.ISACTIVE = 1
and EVENT.PROGRAMID is null
union all
select
EVENT.ID as [ID],
null as PARENTID,
EVENT.STARTDATE,
EVENT.ENDDATE,
EVENT.NAME,
'Program event' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.EVENT
left join dbo.CHANGEAGENT on EVENT.ADDEDBYID = CHANGEAGENT.ID
where
(
(@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is not null) and (EVENT.STARTDATE <= @ENDDATE and @STARTDATE <= EVENT.ENDDATE))
or ( (EVENT.STARTDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (EVENT.ENDDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
or ( (EVENT.STARTDATE <= @ENDDATE) and (@STARTDATE is null) )
or ( (EVENT.ENDDATE >= @STARTDATE) and (@ENDDATE is null) )
)
and EVENT.PROGRAMID is not null
and (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('bb1c17bc-9e0b-4683-b490-ee40d511fa05') = 1)
union all
select
RESERVATION.ID,
null as PARENTID,
ARRIVALDATE as STARTDATE,
isnull((select top 1 ITINERARYITEM.ENDDATE
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARY.RESERVATIONID = RESERVATION.ID
order by ENDDATE desc), ARRIVALDATE) ENDDATE,
NAME,
'Reservation' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from dbo.RESERVATION
inner join
dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
left join
dbo.CHANGEAGENT on RESERVATION.ADDEDBYID = CHANGEAGENT.ID
where
(STATUSCODE <> 1 and STATUSCODE <> 5)
and (
( (ARRIVALDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (ARRIVALDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (ARRIVALDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
or ( isnull((select top 1 ITINERARYITEM.ENDDATE
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARY.RESERVATIONID = RESERVATION.ID
order by ENDDATE desc), ARRIVALDATE) between @STARTDATE and @ENDDATE)
or ( (isnull((select top 1 ITINERARYITEM.ENDDATE
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARY.RESERVATIONID = RESERVATION.ID
order by ENDDATE desc), ARRIVALDATE) <=@ENDDATE) and (@STARTDATE is null))
or ( (isnull((select top 1 ITINERARYITEM.ENDDATE
from dbo.ITINERARYITEM
inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
where ITINERARY.RESERVATIONID = RESERVATION.ID
order by ENDDATE desc), ARRIVALDATE) >= @STARTDATE) and (@ENDDATE is null))
)
and (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('bb1c17bc-9e0b-4683-b490-ee40d511fa05') = 1)
union all
select
CALENDARITEM.ID,
null as PARENTID,
CALENDARITEM.STARTDATE,
CALENDARITEM.ENDDATE,
CALENDARITEM.NAME,
'Calendar item' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.CALENDARITEM
left join
dbo.CHANGEAGENT on CALENDARITEM.ADDEDBYID = CHANGEAGENT.ID
where
(
( (CALENDARITEM.STARTDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (CALENDARITEM.STARTDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (CALENDARITEM.ENDDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
or ( (CALENDARITEM.ENDDATE between @STARTDATE and @ENDDATE))
or ( (CALENDARITEM.STARTDATE <= @STARTDATE) and (CALENDARITEM.ENDDATE >= @ENDDATE or @ENDDATE is null))
or ( (CALENDARITEM.STARTDATE <= @ENDDATE) and (@STARTDATE is null))
)
union all
select
MKTSEGMENTATION.ID,
null as PARENTID,
MKTSEGMENTATION.MAILDATE as STARTDATE,
null as ENDDATE,
MKTSEGMENTATION.NAME,
'Marketing effort' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.MKTSEGMENTATION
left join
dbo.CHANGEAGENT on MKTSEGMENTATION.ADDEDBYID = CHANGEAGENT.ID
where
(
( (MKTSEGMENTATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
or ( (MKTSEGMENTATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
or ( (MKTSEGMENTATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
)
and (MKTSEGMENTATION.ID not in (select ID from dbo.APPEALMAILING))
union all
select
M.[ID],
M.[PARENTMARKETINGPLANITEMID] PARENTID,
dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.STARTDATE) as STARTDATE,
dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.ENDDATE) as ENDDATE,
M.[NAME] as NAME,
'Plan activity' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.[MKTMARKETINGPLANITEM] M
left join
dbo.MKTMARKETINGPLANITEMTEMPLATEITEM on (M.MARKETINGPLANID=MKTMARKETINGPLANITEMTEMPLATEITEM.MARKETINGPLANID and M.LEVEL=MKTMARKETINGPLANITEMTEMPLATEITEM.LEVEL)
left join
dbo.MKTMARKETINGPLANITEM on MKTMARKETINGPLANITEM.ID = M.[PARENTMARKETINGPLANITEMID]
left join
dbo.CHANGEAGENT on M.ADDEDBYID = CHANGEAGENT.ID
where
(
(@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is null) and (dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.STARTDATE) >= @STARTDATE))
or ((@STARTDATE is null and @ENDDATE is not null) and (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(M.STARTDATE) <= @ENDDATE))
or ((@STARTDATE is not null and @ENDDATE is not null) and (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(M.STARTDATE) <= @ENDDATE and (@STARTDATE <= dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.ENDDATE))))
or ((dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(M.STARTDATE) <= @ENDDATE) and (@STARTDATE is null))
or ((dbo.UFN_DATE_LATESTFROMFUZZYDATE(M.ENDDATE) >= @STARTDATE) and (@ENDDATE is null))
)
and (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('bb9873d7-f1ed-430a-8ab4-f09f47056538') = 1 or
dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') = 1)
union all
select
MKTMARKETINGPLANITEMTASK.[ID],
MKTMARKETINGPLANITEMTASK.[MARKETINGPLANITEMID] PARENTID,
MKTMARKETINGPLANITEMTASK.[DUEDATE] as STARTDATE,
null as ENDDATE,
MKTMARKETINGPLANITEMTASK.[SUBJECT] as NAME,
'Plan activity task' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
1 as TYPECODE
from
dbo.[MKTMARKETINGPLANITEMTASK]
inner join
dbo.[MKTMARKETINGPLANITEM] M on (M.[ID]=MKTMARKETINGPLANITEMTASK.[MARKETINGPLANITEMID])
left join
dbo.CHANGEAGENT on MKTMARKETINGPLANITEMTASK.ADDEDBYID = CHANGEAGENT.ID
where
(
(@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is not null) and (dbo.UFN_DATE_FROMFUZZYDATE(MKTMARKETINGPLANITEMTASK.DUEDATE) between @STARTDATE and @ENDDATE))
or ((@ENDDATE is null and @STARTDATE is not null) and (dbo.UFN_DATE_LATESTFROMFUZZYDATE(MKTMARKETINGPLANITEMTASK.DUEDATE) >= @STARTDATE))
or ((@STARTDATE is null and @ENDDATE is not null) and (dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(MKTMARKETINGPLANITEMTASK.DUEDATE) <= @ENDDATE))
)
and (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('bb9873d7-f1ed-430a-8ab4-f09f47056538') = 1 or
dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') = 1)
union all
select
MKTMARKETINGPLANITEM.[ID],
MKTMARKETINGPLANITEM.[PARENTMARKETINGPLANITEMID] PARENTID,
convert(nvarchar(8),MKTMARKETINGPLANITEM.[MAILDATE],112) as [STARTDATE],
convert(nvarchar(8),MKTMARKETINGPLANITEM.[MAILDATE],112) as [ENDDATE],
MKTMARKETINGPLANITEM.[NAME] + ' - Mail date' as NAME,
'Plan activity' as TYPE,
CHANGEAGENT.USERNAME as CREATEDBY,
0 as TYPECODE
from
dbo.[MKTMARKETINGPLANITEM]
left join
dbo.MKTMARKETINGPLANITEMTEMPLATEITEM on (MKTMARKETINGPLANITEM.MARKETINGPLANID=MKTMARKETINGPLANITEMTEMPLATEITEM.MARKETINGPLANID and MKTMARKETINGPLANITEM.LEVEL=MKTMARKETINGPLANITEMTEMPLATEITEM.LEVEL)
left join
dbo.CHANGEAGENT on MKTMARKETINGPLANITEM.ADDEDBYID = CHANGEAGENT.ID
where
(
((@STARTDATE is null and @ENDDATE is null)
or ((@STARTDATE is not null and @ENDDATE is null) and (MAILDATE >= @STARTDATE))
or ((@STARTDATE is null and @ENDDATE is not null) and (MAILDATE <= @ENDDATE))
or ((@STARTDATE is not null and @ENDDATE is not null) and (MAILDATE <= @ENDDATE and (@STARTDATE <= MAILDATE ))))
and MKTMARKETINGPLANITEM.MAILDATE is not null
)
and (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('bb9873d7-f1ed-430a-8ab4-f09f47056538') = 1 or
dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') = 1)
order by STARTDATE
end