USP_DATAFORMTEMPLATE_ADD_FAFNFG

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CAMPAIGNID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@NFGTYPECODEID	uniqueidentifier	IN
@ORGANIZATIONCONSTITUENTID	uniqueidentifier	IN
@CONTACTID	uniqueidentifier	IN
@SITES	xml	IN
@ALLOWOTHERSJOINFROMGROUPPAGE	bit	IN
@ALLOWCOMPANIESJOINFROMGROUPPAGE	bit	IN
@ALLOWGROUPSCHOOSEHIERARCHYLEVEL	bit	IN
@TOTALREVENUEGOAL	money	IN
@TOTALDONORSGOAL	int	IN
@PARTICIPANTSRECRECRUITMENTGOAL	int	IN
@PARTICIPANTSRETAINEDGOAL	decimal(18, 0)	IN
@FAFGROUPSRECRUITMENTGOAL	int	IN
@FAFGROUPSRETAINEDGOAL	decimal(18, 0)	IN
@COMMUNICATIONSTOSEND	int	IN
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_FAFNFG
(
    @ID uniqueidentifier = null output,
      @CAMPAIGNID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier,
      @NAME nvarchar(100) = '',
      @NFGTYPECODEID uniqueidentifier = null,
      @ORGANIZATIONCONSTITUENTID uniqueidentifier = null,
    @CONTACTID uniqueidentifier = null,
    @SITES xml = null,
    @ALLOWOTHERSJOINFROMGROUPPAGE bit = 0,
    @ALLOWCOMPANIESJOINFROMGROUPPAGE bit = 0,
    @ALLOWGROUPSCHOOSEHIERARCHYLEVEL bit = 0,
    @TOTALREVENUEGOAL    money =0.00,
    @TOTALDONORSGOAL     int =0,
    @PARTICIPANTSRECRECRUITMENTGOAL  int =0,
    @PARTICIPANTSRETAINEDGOAL  decimal = 0,
    @FAFGROUPSRECRUITMENTGOAL  int =0,
    @FAFGROUPSRETAINEDGOAL     decimal =0,
    @COMMUNICATIONSTOSEND      int = 0
)
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()

declare @GroupConstituentID  uniqueidentifier
Set @GroupConstituentID = newid()
declare @NFGGroupTypeID  uniqueidentifier
SELECT @NFGGroupTypeID = ID FROM dbo.GROUPTYPE WHERE NAME = 'FAF National Fundraising Group'

begin try
    -- create a group constituent record
    insert into dbo.CONSTITUENT   
          (  
             ID, ISGroup, KEYNAME, IsConstituent, IsOrganization, 
             ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED  
          )  
     values  
          (   
             @GroupConstituentID, 1, @NAME, 1,  0,
             @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE  
          ); 


    if exists(SELECT ID FROM dbo.GROUPTYPE WHERE NAME = 'FAF National Fundraising Group')
      begin
         Insert into dbo.groupData
              (
                  ID, GroupTypeCode, GroupTypeID,
                  ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED  
              )
          values
              (
                 @GroupConstituentID, 1, @NFGGroupTypeID,
                 @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE  
              )
      end

  -- create NFG
     insert into dbo.FAFNFGCAMPAIGN
        (ID, GroupConstituentID, CampaignID, OrganizationConstituentID, NFGTypeCodeID, Name, ContactID,
         ALLOWOTHERSJOINFROMGROUPPAGE, ALLOWCOMPANIESJOINFROMGROUPPAGE, ALLOWGROUPSCHOOSEHIERARCHYLEVEL, 
         TOTALREVENUEGOAL, TOTALDONORSGOAL,
         PARTICIPANTSRECRECRUITMENTGOAL, PARTICIPANTSRETAINEDGOAL,
         FAFGROUPSRECRUITMENTGOAL, FAFGROUPSRETAINEDGOAL, COMMUNICATIONSTOSEND,
         ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
        (@ID, @GroupConstituentID, @CAMPAIGNID, @ORGANIZATIONCONSTITUENTID, @NFGTYPECODEID, @Name, @CONTACTID,
         @ALLOWOTHERSJOINFROMGROUPPAGE, @ALLOWCOMPANIESJOINFROMGROUPPAGE, @ALLOWGROUPSCHOOSEHIERARCHYLEVEL, 
         @TOTALREVENUEGOAL, @TOTALDONORSGOAL,
         @PARTICIPANTSRECRECRUITMENTGOAL, @PARTICIPANTSRETAINEDGOAL,
         @FAFGROUPSRECRUITMENTGOAL, @FAFGROUPSRETAINEDGOAL, @COMMUNICATIONSTOSEND,
         @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

    exec dbo.USP_NFG_GETSITES_ADDFROMXML @ID, @SITES, @CHANGEAGENTID;

    -- create a default level
    INSERT into dbo.FAFNFGCAMPAIGNLEVEL
          (ID, NFGCAMPAIGNID, NAME, 
           HIERARCHYPATH, CONTACTID,
           ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    VALUES(
           NEWID(), @ID, @NAME, 
           dbo.UFN_LEVELHIERARCHY_GETNEWDESCENDANTVALUE(@ID, null), @CONTACTID,
          @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

    -- create a relationship type
    if @ORGANIZATIONCONSTITUENTID is not null
      Begin
         declare @RELATIONSHIPTYPECODEID uniqueidentifier
         declare @RECIPROCALTYPECODEID 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'

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

    -- add default site security
    Declare @SiteID  uniqueidentifier
    Select @SiteID = SiteID from dbo.AppUser where ID = @CURRENTAPPUSERID
    if @SiteID is not null 
       Begin
            Insert into dbo.Constituentsite
                (ID, ConstituentID, SiteID,
                 ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            Values
                (NEWID(), @GroupConstituentID, @SiteID,
                 @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
       End


end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch


return 0