USP_DATALIST_FAFEVENTMARKETING

              CREATE procedure dbo.USP_DATALIST_FAFEVENTMARKETING
                    ( @EVENTID uniqueidentifier,
                      @FROMDATE datetime = null,
                      @TODATE datetime = null,
                      @SCHEDULEAPPROVED tinyint = null,
                      @MESSAGENAME nvarchar(255) = null
                    )
              as
                  set nocount on;

              declare @CLIENTSID int
              declare @EVENTSTARTDATE datetime
              declare @DAYSCOUNT int

              IF ISNULL(@MESSAGENAME,'') = ''
              SET @MESSAGENAME = null

              SELECT @CLIENTSID = CS.CLIENTSID, @EVENTSTARTDATE = E.STARTDATE
              FROM dbo.EVENTEXTENSION EE (NOLOCK)
              INNER JOIN dbo.CLIENTSITES CS (NOLOCK)
              ON CS.ID = EE.CLIENTSITESID
              INNER JOIN dbo.EVENT E (NOLOCK)
              ON EE.EVENTID = E.ID
              WHERE EE.EVENTID = @EVENTID

                IF @FROMDATE IS NULL
              SET @FROMDATE = '01/01/1990'

              IF @TODATE IS NULL
              SET @TODATE = '01/01/2099';


              with
                [MARKETINGMESSAGES]
              AS
              (
              SELECT  e.[ID],
          --        e.[EMAILTEMPLATEID],
                  e.[NAME] as MESSAGENAME,
                  e.[SUBJECT],
                EL.NAME as RECIPIENTSLIST,
                  (CASE WHEN ISNULL(EJ.[Status],1) > 1 then 1 else 0 end) as CONFIRMED,
                  e.SENDAFTERDATE as DATESCHEDULED,
                (CASE WHEN DATEDIFF(day,e.SENDAFTERDATE,@EVENTSTARTDATE) > 0 then CAST((DATEDIFF(day,e.SENDAFTERDATE,@EVENTSTARTDATE)) as varchar(10))+' days before event'
                       WHEN DATEDIFF(day,e.SENDAFTERDATE,@EVENTSTARTDATE) = 0 then 'On event start date'
                                  else  CAST((DATEDIFF(day,@EVENTSTARTDATE,e.SENDAFTERDATE)) as varchar(10))+' days after event start date' END) as SCHEDULEDTOSEND,
                ej.GUID As EMAILJOBGUID,
                (CASE WHEN ISNULL(EJ.[Status],1) > 2 then 1 else 0 end) as SENT
              FROM dbo.fnEmailList(@CLIENTSID,13) E
              INNER JOIN dbo.EVENTEMAILTEMPLATE EET (NOLOCK)
              ON EET.EMAILTEMPLATEID = E.EMAILTEMPLATEID
              LEFT OUTER JOIN dbo.EMAIL_EMAILLIST EEL (NOLOCK)
              ON EEL.EmailID = E.ID
              AND EEL.IsTest = 0
              LEFT OUTER JOIN dbo.EMAILLIST EL (NOLOCK)
              ON EL.ID = EEL.EMAILLISTID
           --   INNER JOIN dbo.EMAILSCHEDULE_EMAILLIST ESE (NOLOCK)
           --   ON ESE.EMAILLISTID = EL.ID
           --   INNER JOIN dbo.EMAILSCHEDULE ES (NOLOCK)
           --   ON ES.ID = ESE.EMAILSCHEDULEID
              LEFT OUTER JOIN dbo.EmailJob EJ (NOLOCK)
              ON e.ID = EJ.EmailID and e.EmailTemplateID = EJ.EmailTemplateID
              where EET.EVENTID = @EVENTID 
            --      AND ES.SCHEDULEENABLED = ISNULL(@SCHEDULEAPPROVED,ES.SCHEDULEENABLED)
                  AND E.SendAfterDate between @FROMDATE and @TODATE
                  AND e.NAME = ISNULL(@MESSAGENAME,e.NAME)
                AND EET.CONFIRMATIONTYPECODE = 33
                AND ISNULL(EEL.IsTest,0) = 0
                )
                SELECT ID,
                       MESSAGENAME,
                       SUBJECT,
                       dbo.UDA_BUILDLIST(RECIPIENTSLIST) RECIPIENTSLIST,
                       CONFIRMED,
                       DATESCHEDULED,
                       SCHEDULEDTOSEND,
                       EMAILJOBGUID,
                       SENT
                FROM [MARKETINGMESSAGES]
                WHERE CONFIRMED = ISNULL(@SCHEDULEAPPROVED,CONFIRMED)
                GROUP BY ID,MESSAGENAME,SUBJECT,CONFIRMED,DATESCHEDULED,SCHEDULEDTOSEND,EMAILJOBGUID,SENT
                ORDER BY DATESCHEDULED