USP_DATAFORMTEMPLATE_ADD_GLOBALOPTOUTMAILPREFERENCEADD

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_GLOBALOPTOUTMAILPREFERENCEADD
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @EMAILADDRESS UDT_EMAILADDRESS,
    @SENDMAIL bit,
    @STARTDATE datetime = 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 * 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, @ISNETCOMMUNITYEMAILOPTOUTMAPPEDWITHSOLICITCODE=case when SC.CONSENTCODE = 0 then 1 else 0 end
            from NETCOMMUNITYDEFAULTCODEMAP NCDCM inner join SOLICITCODE SC on SC.ID=NCDCM.DONOTEMAILSOLICITCODEID


     --update all constituent records with this email address

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

        begin
                merge dbo.CONSTITUENTSOLICITCODE as target
                using
                (
                    select distinct CONSTITUENT.ID CONSTITUENTID,EMAILADDRESS.EMAILADDRESS EMAILADDRESS 
                    from CONSTITUENT 
                    join EMAILADDRESS on EMAILADDRESS.CONSTITUENTID=CONSTITUENT.ID
                ) as source
                on (target.CONSTITUENTID=source.CONSTITUENTID and @EMAILADDRESS=source.EMAILADDRESS and target.SOLICITCODEID = @DONOTEMAILSOLICITCODEID)

                when matched and @SENDMAIL=1 then
                    delete when not matched and @SENDMAIL=0 and source.EMAILADDRESS=@EMAILADDRESS then

                    insert (CONSTITUENTID, SOLICITCODEID, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE)
                    values (source.CONSTITUENTID, @DONOTEMAILSOLICITCODEID, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @STARTDATE, @ENDDATE);
        end
        else   -- If Net Community Email Opt-out is mapped with Consent Solicit Code or Advanced consent solicit code

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

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

                insert into #CONSTITUENTLIST (constituentId ,isSolicitCodeExist , isEnddateUpdateRequired)
                select distinct C.ID , case when CSC.SOLICITCODEID is null then 0 else 1 end,0
                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 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,1,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@STARTDATE,'',
                case when @SENDMAIL=1 then 2 else 1 end as CONSENTPREFERENCECODE,@SOURCEEVIDENCECODEID,@SOURCEFILEPATH,@SUPPORTINGINFORMATION,@CONSENTSTATEMENT 
                from #CONSTITUENTLIST 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
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0