USP_ADD_GLOBALOPTOUTPREFERENCEADD_LISTUNSUBSCRIBE

create procedure dbo.USP_ADD_GLOBALOPTOUTPREFERENCEADD_LISTUNSUBSCRIBE
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @EMAILADDRESS nvarchar(255),
    @SENDMAIL bit,
    @STARTDATE date = null,
    @ENDDATE datetime = null,
    @CONSENTSTATEMENT nvarchar(max)='',
    @SOURCEEVIDENCECODEID uniqueidentifier=null,
    @SOURCEFILEPATH nvarchar(260)='',
    @SUPPORTINGINFORMATION  nvarchar(max)=''
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

    --check if there are constituents with this email address

    if (exists
        (select 1 from CONSTITUENT 
         join EMAILADDRESS on  Constituent.ID = EMAILADDRESS.CONSTITUENTID 
         where EMAILADDRESS.EMAILADDRESS = @EMAILADDRESS)
    )
    begin
        declare @DONOTEMAILSOLICITCODEID uniqueidentifier,@ISNETCOMMUNITYEMAILOPTOUTMAPPEDWITHSOLICITCODE as bit

        select top 1 @DONOTEMAILSOLICITCODEID = NCDCM.DONOTEMAILSOLICITCODEID,
                    @SOURCEEVIDENCECODEID=NCDCM.DATAPROTECTIONEVIDENCESOURCECODEID,
                    @ISNETCOMMUNITYEMAILOPTOUTMAPPEDWITHSOLICITCODE=case when SC.CONSENTCODE = 0 then 1 else 0 end
            from NETCOMMUNITYDEFAULTCODEMAP NCDCM 
            inner join SOLICITCODE SC on SC.ID=NCDCM.DONOTEMAILSOLICITCODEID


                if  OBJECT_ID('tempdb..#CONSTITUENTLIST') is not null    
                  drop table #CONSTITUENTLIST  

                create table #CONSTITUENTLIST (constituentId uniqueidentifier,isSolicitCodeExist bit, isEnddateUpdateRequired bit,consentSolicitCodeId uniqueidentifier,sequence int)

                insert into #CONSTITUENTLIST (constituentId ,isSolicitCodeExist , isEnddateUpdateRequired,sequence)
                select distinct C.ID , case when CSC.SOLICITCODEID is null then 0 else 1 end,0,1
                from CONSTITUENT C
                inner join EMAILADDRESS EA on EA.CONSTITUENTID=C.ID 
                left join CONSTITUENTSOLICITCODE CSC on CSC.CONSTITUENTID=EA.CONSTITUENTID and CSC.SOLICITCODEID=@DONOTEMAILSOLICITCODEID
                where EA.EMAILADDRESS=@EMAILADDRESS

                update #CONSTITUENTLIST set sequence= source.sequence
                  from(
                    select csc.constituentId, coalesce(max(CSC.SEQUENCE),0) + 1 as sequence from dbo.CONSTITUENTSOLICITCODE CSC
                    inner join #CONSTITUENTLIST CL on CSC.CONSTITUENTID=CL.constituentId 
                    group by csc.constituentId
                  )source
                where #CONSTITUENTLIST.CONSTITUENTID = source.CONSTITUENTID;

                 if @ISNETCOMMUNITYEMAILOPTOUTMAPPEDWITHSOLICITCODE=1 -- If Net Community Email Opt-out is mapped with Standard Solicit Code

                 begin
                  merge dbo.CONSTITUENTSOLICITCODE as target
                    using #CONSTITUENTLIST source
                    on (target.CONSTITUENTID=source.CONSTITUENTID  and target.SOLICITCODEID = @DONOTEMAILSOLICITCODEID and (target.ENDDATE is null or target.ENDDATE=@STARTDATE))

                    when matched then
                      update set target.EndDate=Null

                    when not matched then
                      insert (CONSTITUENTID, SOLICITCODEID, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE)
                      values (source.CONSTITUENTID, @DONOTEMAILSOLICITCODEID, source.sequence, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @STARTDATE, @ENDDATE); 

                 end
                 else
                 begin

      -- If Net Community Email Opt-out is mapped with Consent Solicit Code or Advanced consent solicit code                            


                update #CONSTITUENTLIST set isEnddateUpdateRequired= result,consentSolicitCodeId=id
                from(
                      select csc.CONSTITUENTID,case when dbo.[UFN_DATE_GETEARLIESTTIME](CSC.STARTDATE) < dbo.[UFN_DATE_GETEARLIESTTIME](@STARTDATE) then 1  else 0 end as result,csc.id
                      from CONSTITUENTSOLICITCODE CSC 
                      inner join #CONSTITUENTLIST CL on CSC.CONSTITUENTID=CL.constituentId and CL.isSolicitCodeExist=1 and SOLICITCODEID=@DONOTEMAILSOLICITCODEID
                      and CSC.ENDDATE is null
                )CNL where CNL.CONSTITUENTID=#CONSTITUENTLIST.constituentId

            --Update only CONSENTPREFERENCECODE if user changes preference on same day.

                update CSC Set CONSENTPREFERENCECODE= case when @SENDMAIL=1 then 2 else 1 end
                from CONSTITUENTSOLICITCODE CSC
                inner join #CONSTITUENTLIST C on C.constituentId=CSC.CONSTITUENTID and isSolicitCodeExist=1 and isEnddateUpdateRequired=0
                where csc.ID=c.consentSolicitCodeId

            --Update only ENDDATE (current date - 1),if user changes preference on later days.

                update CSC Set ENDDATE= dateadd(day, -1, dbo.[UFN_DATE_GETEARLIESTTIME](@STARTDATE))
                from CONSTITUENTSOLICITCODE CSC
                inner join #CONSTITUENTLIST C on C.constituentId=CSC.CONSTITUENTID and isSolicitCodeExist=1 and isEnddateUpdateRequired=1
                where csc.ID=c.consentSolicitCodeId


            --Insert user consent preferences, if it is first time or after date adjustments have been made.

                insert into CONSTITUENTSOLICITCODE (
                        ID,   
                        CONSTITUENTID, 
                        SOLICITCODEID, 
                        SEQUENCE, 
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED, 
                        STARTDATE,   
                        COMMENTS, 
                        CONSENTPREFERENCECODE, 
                        SOURCECODEID, 
                        SOURCEFILEPATH, 
                        SUPPORTINGINFORMATION,  
                        CONSENTSTATEMENT
                        )
                select newid(),constituentId,@DONOTEMAILSOLICITCODEID,CL.sequence,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@STARTDATE,'',
                case when @SENDMAIL=1 then 2 else 1 end as CONSENTPREFERENCECODE,@SOURCEEVIDENCECODEID,@SOURCEFILEPATH,@SUPPORTINGINFORMATION,@CONSENTSTATEMENT 
                from #CONSTITUENTLIST CL where (isSolicitCodeExist =0 or isEnddateUpdateRequired=1)        
        end
    end

    --update non-constituents

    --also do this to keep track of constituents because multiple constits can have the same email

          merge dbo.EmailList_GlobalSubscription as target
          using
          (
              select @EMAILADDRESS EMAILADDRESS, @SENDMAIL HASOPTED
          ) as source
          on (target.EmailAddress = source.EMAILADDRESS)

          when matched then
              update set HasOpted=source.HASOPTED

          when NOT matched by target then
              insert (EmailAddress, HasOpted) values (source.EMAILADDRESS, source.HASOPTED);

end try

begin catch
    if  OBJECT_ID('tempdb..#CONSTITUENTLIST') is not null    
                drop table #CONSTITUENTLIST
    exec dbo.USP_RAISE_ERROR
    return 1
end catch
if  OBJECT_ID('tempdb..#CONSTITUENTLIST') is not null    
                drop table #CONSTITUENTLIST

return 0