USP_REPORT_ADVOCACYACTIONDETAIL

CREATE procedure dbo.USP_REPORT_ADVOCACYACTIONDETAIL
(
 @ACTIONCENTERID uniqueidentifier = NULL
,@ACTIONITEMID uniqueidentifier = NULL
,@LIVEACTIONITEMONLY bit = NULL
,@STARTDATE date = NULL
,@ENDDATE date = NULL
,@STATUS INT = NULL
,@TYPE INT = NULL
,@ACTIONCENTERS VARCHAR(MAX) = null
,@ACTIONITEMS VARCHAR(MAX) = null
)
as      


DECLARE @ACTIONCENTER_T table
(
    SITEID int
);

DECLARE @ACTIONITEM_T table
(
    ACTIONID uniqueidentifier
);

IF @TYPE = 2
BEGIN
SET @TYPE = NULL
END

IF @STATUS = 4
BEGIN
SET @STATUS = NULL
END


IF ISNULL(@ACTIONCENTERS, '') = '' 
    BEGIN
    INSERT INTO  @ACTIONCENTER_T(SITEID)
    SELECT SITECONTENTID
    FROM ACTIONCENTER (NOLOCK)
    END
ELSE
BEGIN
    insert into @ACTIONCENTER_T(SITEID)
    select element_value as SITEID
    FROM        UFN_PARSE_STRING(@ACTIONCENTERS,',')
END


IF ISNULL(@ACTIONITEMS, '') = '' 
    BEGIN
    INSERT INTO  @ACTIONITEM_T(ACTIONID)

    SELECT CAST( ID as uniqueidentifier )
    FROM ACTION_ITEM (NOLOCK)
    END
ELSE
BEGIN
    INSERT INTO @ACTIONITEM_T(ACTIONID)
    SELECT CAST(element_value as uniqueidentifier)  as SITEID
    FROM        UFN_PARSE_STRING(@ACTIONITEMS,',')
END

SELECT 
    AL.ID,
    AL.[ADVOCACYLOGID],
    AL.[SITECONTENTID],
    SC.GUID AS [ACTIONCENTERID],
    AL.[ACTIONITEMID],
    AL.[CONSTITUENTID],
    SC.TITLE AS [ACTIONCENTERNAME],
    ISNULL(AI.[NAME], 'None') AS [ACTIONITEMNAME],
    dbo.UFN_CONSTITUENT_BUILDNAME(C.ID) as [ADVOCATENAME],
    AL.[TARGETNAME],
    AL.[TYPE] AS [DELIVERYTYPE],
    AL.[STATUS],
    AL.[SUBJECTLINE],
    CONVERT(VARCHAR(40),AL.[DATEADDED], 101) AS [ACTIONDATE],
    AI.TYPE as ActionType,
    NULL AS PetitionCount
FROM dbo.ADVOCACYLOG AL (NOLOCK)
INNER JOIN dbo.SITECONTENT SC (NOLOCK)
    ON AL.SITECONTENTID = SC.ID
INNER JOIN dbo.ACTION_ITEM AI (NOLOCK)
    ON AL.ACTIONITEMID = AI.ID
INNER JOIN dbo.CONSTITUENT C (NOLOCK)
    ON AL.[CONSTITUENTID] = C.ID
INNER JOIN @ACTIONCENTER_T ACT
    ON AL.SITECONTENTID = ACT.SITEID 
INNER JOIN @ACTIONITEM_T AIT
    ON AL.ACTIONITEMID  = AIT.ACTIONID 
WHERE SC.GUID = ISNULL(@ACTIONCENTERID, SC.GUID)
AND AL.[ACTIONITEMID] = ISNULL(@ACTIONITEMID,  AL.[ACTIONITEMID])
--AND AI.STATUSCODE = CASE WHEN @LIVEACTIONITEMONLY > 0 THEN 1 ELSE AI.STATUSCODE END

AND AL.DATEADDED BETWEEN ISNULL(@STARTDATE, '1/1/1900')  AND DATEADD(dd, 1, ISNULL(@ENDDATE, '12/30/2999'))
AND AI.STATUSCODE = ISNULL(@STATUS, AI.[STATUSCODE])
AND AI.TYPECODE = ISNULL(@TYPE, AI.TYPECODE)

UNION


SELECT   A.ID,
    A.[ADVOCACYLOGID],
    A.[SITECONTENTID],
    A.[ACTIONCENTERID],
    A.[ACTIONITEMID],
    A.[CONSTITUENTID],
    A.[ACTIONCENTERNAME],
    A.[ACTIONITEMNAME],
    A.[ADVOCATENAME],
    A.[TARGETNAME],
    A.[DELIVERYTYPE],
    A.[STATUS],
    A.[SUBJECTLINE],
    A.[ACTIONDATE],
    A.ActionType,
    A.PETITIONCOUNT FROM
(
select AP.ID,
    NULL AS [ADVOCACYLOGID],
    AP.SITECONTENTID,
    SC.GUID AS [ACTIONCENTERID],
    AP.[ACTIONITEMID],
    AP.[CONSTITUENTID],
    SC.TITLE AS [ACTIONCENTERNAME],
    ISNULL( AI.[NAME], 'None') AS [ACTIONITEMNAME],
    dbo.UFN_CONSTITUENT_BUILDNAME(C.ID) as [ADVOCATENAME],
    NULL AS [TARGETNAME],
    NULL AS [DELIVERYTYPE],
    NULL AS [STATUS],
    NULL AS [SUBJECTLINE],
    CONVERT(VARCHAR(40),AP.[DATEADDED], 101) AS [ACTIONDATE],
    AI.TYPE as ActionType,
    --AIP.PETITIONCOUNT AS PETITIONCOUNT,

    AP.DATEADDED,
    ROW_NUMBER() OVER( PARTITION  BY AP.[ACTIONITEMID] ORDER BY AP.DATEADDED ASC ) AS PETITIONCOUNT
FROM ADVOCACYPETITION  AP
INNER JOIN dbo.ACTIONITEMPETITION AIP
    ON AP.ACTIONITEMID = AIP.ACTIONITEMID 
INNER JOIN dbo.SITECONTENT SC (NOLOCK)
    ON AP.SITECONTENTID = SC.ID
INNER JOIN dbo.ACTION_ITEM AI (NOLOCK)
    ON AP.ACTIONITEMID = AI.ID
INNER JOIN dbo.CONSTITUENT C (NOLOCK)
    ON AP.[CONSTITUENTID] = C.ID
INNER JOIN @ACTIONCENTER_T ACT
    ON AP.SITECONTENTID = ACT.SITEID 
INNER JOIN @ACTIONITEM_T AIT
    ON AP.ACTIONITEMID  = AIT.ACTIONID     
WHERE SC.GUID = ISNULL(@ACTIONCENTERID, SC.GUID)
AND AP.[ACTIONITEMID] = ISNULL(@ACTIONITEMID,  AP.[ACTIONITEMID])
AND AI.STATUSCODE = CASE WHEN @LIVEACTIONITEMONLY > 0 THEN 1 ELSE AI.STATUSCODE END
AND AI.STATUSCODE = ISNULL(@STATUS, AI.[STATUSCODE])
AND AI.TYPECODE = ISNULL(@TYPE, AI.TYPECODE)
) as A
WHERE A.DATEADDED BETWEEN ISNULL(@STARTDATE, '1/1/1900')  AND DATEADD(dd, 1, ISNULL(@ENDDATE, '12/30/2999'))

UNION

SELECT 
    NULL AS ID,
    NULL AS [ADVOCACYLOGID],
    NULL AS [SITECONTENTID],
    SC.GUID AS [ACTIONCENTERID],
    NULL AS [ACTIONITEMID],
    NULL AS [CONSTITUENTID],
    SC.TITLE AS [ACTIONCENTERNAME],
    'None'  AS [ACTIONITEMNAME],
    NULL as [ADVOCATENAME],
    NULL AS [TARGETNAME],
    NULL AS [DELIVERYTYPE],
    NULL AS [STATUS],
    NULL AS [SUBJECTLINE],
    NULL AS [ACTIONDATE],
    NULL AS  ActionType,
    NULL AS PetitionCount
FROM SITECONTENT SC (NOLOCK)
INNER JOIN @ACTIONCENTER_T ACT
    ON SC.ID = ACT.SITEID 
WHERE SC.ContentTypesID = 123
AND ID NOT IN 
(
    SELECT SITECONTENTID FROM ADVOCACYLOG (NOLOCK)
)
AND ID NOT IN
(    
    SELECT SITECONTENTID FROM ADVOCACYPETITION (NOLOCK)
)