USP_DATAFORMTEMPLATE_EDIT_FAFNFG

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_FAFNFG (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME varchar(200),
    @NFGTYPECODEID uniqueidentifier, 
    @SITES xml,
    @ALLOWOTHERSJOINFROMGROUPPAGE bit,
    @ALLOWCOMPANIESJOINFROMGROUPPAGE bit,
    @ALLOWGROUPSCHOOSEHIERARCHYLEVEL bit,
--    @ALLOWOTHERSJOINFROMLOCALWEBSITE bit,
--    @ALLOWCOMPANIESJOINFROMLOCALWEBSITE bit,
    @TOTALREVENUEGOAL money,
    @TOTALDONORSGOAL integer,
    @PARTICIPANTSRECRECRUITMENTGOAL integer,
    @PARTICIPANTSRETAINEDGOAL decimal,
    @ContactID uniqueidentifier,
    @OrganizationConstituentID uniqueidentifier,
    @FAFGROUPSRECRUITMENTGOAL  int,
    @FAFGROUPSRETAINEDGOAL     decimal,
    @COMMUNICATIONSTOSEND      int,
    @GROUPCONSTITUENTID            uniqueidentifier,
    @CURRENTAPPUSERID        uniqueidentifier,
  @ROOTLEVELID uniqueidentifier,
  @NOTMATCH bit
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()
    DECLARE @KEYNAME as nvarchar(200)
    DECLARE @LEVELNAME as nvarchar(200)
    DECLARE @ORIGINALGROUPID uniqueidentifier



    declare @defaultSiteId uniqueidentifier,
        @dSite nvarchar(200)

    SET @KEYNAME = ( SELECT KEYNAME FROM dbo.Constituent WHERE ID = @GROUPCONSTITUENTID )
    SET @ORIGINALGROUPID = ( SELECT GROUPCONSTITUENTID FROM dbo.FAFNFGCAMPAIGN WHERE ID = @ID )

    SET @LEVELNAME = 
    ( select FAFNFGCAMPAIGNLEVEL.NAME
        from dbo.FAFNFGCAMPAIGNLEVEL 
        where FAFNFGCAMPAIGNLEVEL.NFGCAMPAIGNID = @ID
         and FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetLevel() = 1 
    )
    begin try
        -- handle updating the data


        IF @LEVELNAME <> @NAME AND @LEVELNAME IS NOT NULL
        BEGIN
            UPDATE FAFNFGCAMPAIGNLEVEL
                SET FAFNFGCAMPAIGNLEVEL.NAME = @NAME,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
            WHERE NFGCAMPAIGNID = @ID
            AND FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetLevel() = 1
        END


        update dbo.FAFNFGCAMPAIGN set
            contactID = @ContactID,
            OrganizationConstituentID = @OrganizationConstituentID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            NAME        = @NAME,
            NFGTYPECODEID = @NFGTYPECODEID,
            ALLOWOTHERSJOINFROMGROUPPAGE = @ALLOWOTHERSJOINFROMGROUPPAGE,
            ALLOWCOMPANIESJOINFROMGROUPPAGE = @ALLOWCOMPANIESJOINFROMGROUPPAGE,
            ALLOWGROUPSCHOOSEHIERARCHYLEVEL = @ALLOWGROUPSCHOOSEHIERARCHYLEVEL,
    --        ALLOWOTHERSJOINFROMLOCALWEBSITE = @ALLOWOTHERSJOINFROMLOCALWEBSITE,
    --        ALLOWCOMPANIESJOINFROMLOCALWEBSITE = @ALLOWCOMPANIESJOINFROMLOCALWEBSITE,
            TOTALREVENUEGOAL = @TOTALREVENUEGOAL,
            TOTALDONORSGOAL = @TOTALDONORSGOAL,
            PARTICIPANTSRECRECRUITMENTGOAL = @PARTICIPANTSRECRECRUITMENTGOAL,
            PARTICIPANTSRETAINEDGOAL = @PARTICIPANTSRETAINEDGOAL,
            FAFGROUPSRECRUITMENTGOAL = @FAFGROUPSRECRUITMENTGOAL,
            FAFGROUPSRETAINEDGOAL = @FAFGROUPSRETAINEDGOAL,
            COMMUNICATIONSTOSEND = @COMMUNICATIONSTOSEND            
        where ID = @ID


    -- Add/Update Relationship data
    DECLARE @CURRENTORGID uniqueidentifier
    DECLARE @RELATIONSHIPID uniqueidentifier
    declare @RECIPROCALTYPECODEID uniqueidentifier
    DECLARE @RELATIONSHIPTYPECODEID uniqueidentifier
    DECLARE @RELATIONSHIPCONSTITUENTID_EXISTS uniqueidentifier
    DECLARE @RECIPROCALCONSTITUENTID_EXISTS uniqueidentifier

    SELECT @RECIPROCALTYPECODEID = ID from dbo.RELATIONSHIPTYPECODE (NOLOCK) WHERE DESCRIPTION = 'FAF Organization'
    SELECT @RELATIONSHIPTYPECODEID = ID from dbo.RELATIONSHIPTYPECODE (NOLOCK) WHERE DESCRIPTION = 'FAF National Fundraising Group'



    SELECT @RELATIONSHIPCONSTITUENTID_EXISTS = RELATIONSHIPCONSTITUENTID 
           FROM DBO.RELATIONSHIP (NOLOCK)    
           WHERE RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID and RECIPROCALCONSTITUENTID = @ORGANIZATIONCONSTITUENTID
           AND RELATIONSHIPCONSTITUENTID = @GROUPCONSTITUENTID 

       /*           
      SET @RECIPROCALCONSTITUENTID_EXISTS = ( SELECT RECIPROCALCONSTITUENTID FROM DBO.RELATIONSHIP (NOLOCK)     
           WHERE RELATIONSHIPTYPECODEID = @RECIPROCALTYPECODEID and RELATIONSHIPCONSTITUENTID = @ORGANIZATIONCONSTITUENTID  ) 
           */

    SELECT    @RELATIONSHIPID = R.ID,
            @CURRENTORGID = R.RELATIONSHIPCONSTITUENTID
    FROM dbo.RELATIONSHIP R (NOLOCK) 
            WHERE R.RELATIONSHIPTYPECODEID =  @RECIPROCALTYPECODEID 
    AND RECIPROCALCONSTITUENTID = @ORIGINALGROUPID ---  @ORGANIZATIONCONSTITUENTID

    IF @GROUPCONSTITUENTID IS NOT NULL   AND  @ORGANIZATIONCONSTITUENTID IS NOT NULL
    BEGIN
        IF ( @CURRENTORGID <>   @ORGANIZATIONCONSTITUENTID OR @CURRENTORGID IS NULL) AND
            @RELATIONSHIPCONSTITUENTID_EXISTS IS NULL 
        BEGIN

            EXEC dbo.USP_DATAFORMTEMPLATE_ADD_ORGTOGROUP_RELATIONSHIP 
                          @ID = null, 
                          @CHANGEAGENTID = @CHANGEAGENTID,
                          @CONSTITUENTID = @GROUPCONSTITUENTID,
                          @RECIPROCALCONSTITUENTID = @ORGANIZATIONCONSTITUENTID,
                          @RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID,
                          @RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID,
                          @COMMENTS = ''
        END
    END


        /*  no need for site security

            if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1 
            begin 
             if @SITES is null
              raiserror('Site is required.',13,1) 
             else
             BEGIN

                select @defaultSiteId = SITEID from dbo.UFN_SITE_MYSITE_GET(@CURRENTAPPUSERID) 
                set @dSite = '<SITEID>'+ cast(@defaultSiteId as nvarchar(40)) +'</SITEID>';
                if CAST(@SITES AS NVARCHAR(MAX)) NOT LIKE '%'+@dSite+'%'
                  raiserror('You did not assign your default site to the event. To continue, edit site security and add your default site.', 13,1)
                end    
            END    
    */




    exec dbo.USP_NFG_GETSITES_UPDATEFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE

  if @NOTMATCH = 1
      exec dbo.USP_NFGLEVEL_REMOVECHILDLEVELSITES @ROOTLEVELID, @SITES;

    set @SITES = dbo.UFN_NFG_GETSITES_TOITEMLISTXML(@ID)
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;