USP_DATALIST_PARTICIPANT_DEFAULT_TEMPLATE

CREATE procedure dbo.USP_DATALIST_PARTICIPANT_DEFAULT_TEMPLATE(
  @CLIENTUSERSID int,
  @EVENTID uniqueidentifier,
  @NAME nvarchar(255) = null,
--  @COMMUNICATIONTYPE tinyint = 0, -- 1:Recruit participants, 2: Raise Money, 3: Fundraising Progress, 4: Gift Acknowledgement,

  @FAFTASKID uniqueidentifier = null,
  @TEMPLATEID uniqueidentifier = null,
  @ISLETTER bit  = 0
  )
as
    set nocount on;

 DECLARE @CONSTITUENTID uniqueidentifier
    SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)



    DECLARE @ParticipantTemplates TABLE
    (
      ID uniqueidentifier,
      Name nvarchar(510),
      Description nvarchar(510),
      ContentHTML ntext,
      Subject nvarchar(510),
      ContactType nvarchar(1000),
      Admintemplate int,
      EMAILTEMPLATEID int,
      DateAdded datetime, 
    --  ISRECOMMENDED int,

      AvailableAsLetter bit,
      ISDEFAULTFORTASK int,
      LASTDAYSENT datetime,
      TASKNAME nvarchar(200),
      EVENTNAME nvarchar(200)
    )


        INSERT INTO @ParticipantTemplates
        ( ID, Name, Description, ContentHTML, Subject,    ContactType,  Admintemplate, EMAILTEMPLATEID, DateAdded,  AvailableAsLetter, ISDEFAULTFORTASK, LASTDAYSENT, TASKNAME, EVENTNAME )
      SELECT
           EET.ID,
           ET.NAME,
             ET.[DESCRIPTION],
           ET.ContentHTML,
           ET.[SUBJECT]    ,
           CONTACTTYPE = dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID),
           1 AS [ADMINTEMPLATE],
           EET.EMAILTEMPLATEID,
           EET.DATEADDED,
          EET.AVAILABLEASLETTER,
          EET.ISDEFAULTFORTASK,
          '1/1/1900' AS [LASTDAYSENT],
          FT.NAME AS TASKNAME,
          E.NAME as EVENTNAME
      from dbo.EVENTEMAILTEMPLATE EET
          join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
          join FAFTASK FT ON EET.FAFTASKID = FT.ID
          join EVENT E on E.ID = EET.EVENTID
            where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE = 100

      --    AND EET.ISAVAILABLETOCOMPANYLEADER = 1

          AND EET.ISACTIVE = 1
          AND ET.NAME = ISNULL(@NAME,ET.NAME)
          AND EET.FAFTASKID = @FAFTASKID
          AND EET.ID = ISNULL( @TEMPLATEID, EET.ID )
          AND EET.ISDEFAULTFORTASK = 1

          -- see if there are any records retrieved

            DECLARE @COUNT integer
            SET @COUNT = ( select COUNT(*) FROM @ParticipantTemplates )
            IF @COUNT = 0
            BEGIN 
                DECLARE @EVENTNAME as varchar(255)
                SET @EVENTNAME = (SELECT TOP 1 NAME FROM EVENT WHERE ID = @eventid) 
             INSERT INTO @ParticipantTemplates
                ( ID, Name, Description, ContentHTML, Subject,    ContactType,  Admintemplate, EMAILTEMPLATEID, DateAdded,  AvailableAsLetter, ISDEFAULTFORTASK, LASTDAYSENT, TASKNAME, EVENTNAME )

                 SELECT TOP 1
                null as ID,
                '' as NAME ,
                '' as DESCRIPTION, 
                '' as ContentHTML,
                '' as [SUBJECT]    ,
                '' AS CONTACTTYPE,  -- = dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID),

                0 AS [ADMINTEMPLATE],
                null as EMAILTEMPLATEID,
                null as DATEADDED,
                0 as AVAILABLEASLETTER,
                0 as ISDEFAULTFORTASK,
                '1/1/1900' AS [LASTDAYSENT],
                FT.NAME AS TASKNAME,
                @EVENTNAME as EVENTNAME 
                FROM FAFTASK ft
                WHERE ft.id = @faftaskid
                  END


               INSERT INTO @ParticipantTemplates
        ( ID, Name, Description, ContentHTML, Subject, ContactType, Admintemplate, EMAILTEMPLATEID, DateAdded,  AvailableAsLetter,ISDEFAULTFORTASK, LASTDAYSENT, TASKNAME, EVENTNAME )
       select 
            PT.[ID],
            PT.[NAME],
            PT.[DESCRIPTION],
            PT.[CONTENTHTML],
            PT.[SUBJECT],
           PT.[CONTACTTYPE],
            0 AS [ADMINTEMPLATE],
            -1 AS EMAILTEMPLATEID,
            PT.DATEADDED,
             PT.[AVAILABLEASLETTER],
             -1 as [ISDEFAULTFORTASK],
        --     ISNULL( PT.LASTDAYSENT, '1/1/1900') AS LASTDAYSENT,

             PT.DATECHANGED AS LASTDAYSENT,
             FT.NAME as TASKNAME,
             E.NAME as EVENTNAME
         from 
            dbo.PARTICIPANTEMAILTEMPLATE PT
            JOIN FAFTASK FT ON PT.FAFTASKID = FT.ID
            JOIN EVENT E on E.ID = PT.EVENTID
            WHERE PT.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))
              AND PT.EVENTID = @EVENTID
            AND PT.DELETED = 0
            AND PT.NAME = ISNULL(@NAME,PT.NAME)
            AND PT.FAFTASKID = @FAFTASKID
            AND PT.ID = ISNULL( @TEMPLATEID, PT.ID )
            AND 
            (   PT.ISLETTER = @ISLETTER  OR
                ( @ISLETTER = 1 AND AVAILABLEASLETTER = 1 )
            )


            SELECT TOP 1 ID, Name, Description, ContentHTML, Subject, ContactType, Admintemplate, EMAILTEMPLATEID, DateAdded,  AvailableAsLetter, TaskName, EventName, ISDEFAULTFORTASK
            FROM @ParticipantTemplates
            ORDER by LASTDAYSENT DESC, ISDEFAULTFORTASK DESC