USP_DATAFORMTEMPLATE_ADD_ADVOCACY_PETITION

The save procedure used by the add dataform template "Advocacy Petition Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@ACTIONITEMID	uniqueidentifier	IN	Actionitem
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@SITECONTENTID	int	IN	SitecontentID
@FEDERALDISTRICTCODE	varchar(4)	IN	Federal District Code
@UPPERSTATEDISTRICTCODE	varchar(5)	IN	Upper District Code
@LOWERSTATEDISTRICTCODE	varchar(5)	IN	Lower District Code
@ISANONYMOUS	bit	IN	IsAnonymous

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ADVOCACY_PETITION
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @ACTIONITEMID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier,
    @SITECONTENTID int,
    @FEDERALDISTRICTCODE varchar(4) = null,
      @UPPERSTATEDISTRICTCODE varchar(5) = null,
      @LOWERSTATEDISTRICTCODE varchar(5) = null,
    @ISANONYMOUS bit = 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 @CURRENTCOUNT int
SELECT  @CURRENTCOUNT = PETITIONCOUNT FROM dbo.ACTIONITEMPETITION (NOLOCK) WHERE ACTIONITEMID = @ACTIONITEMID
SET @CURRENTCOUNT = ISNULL(@CURRENTCOUNT,0)

begin try
    -- handle inserting the data

  IF NOT EXISTS (SELECT 1 FROM dbo.ADVOCACYPETITION (NOLOCK) WHERE ACTIONITEMID = @ACTIONITEMID AND CONSTITUENTID = @CONSTITUENTID)
  BEGIN
    insert into dbo.ADVOCACYPETITION
        (ID, ACTIONITEMID, CONSTITUENTID, SITECONTENTID, FEDERALDISTRICTCODE, UPPERSTATEDISTRICTCODE, LOWERSTATEDISTRICTCODE, ISANONYMOUS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
        (@ID, @ACTIONITEMID, @CONSTITUENTID, @SITECONTENTID, @FEDERALDISTRICTCODE, @UPPERSTATEDISTRICTCODE, @LOWERSTATEDISTRICTCODE, @ISANONYMOUS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

    UPDATE ACTIONITEMPETITION 
    SET PETITIONCOUNT = @CURRENTCOUNT + 1
    WHERE ACTIONITEMID = @ACTIONITEMID

    UPDATE AI SET STATUSCODE = 2
    FROM dbo.ACTION_ITEM AI (NOLOCK)
    INNER JOIN dbo.ACTIONITEMPETITION AP (NOLOCK)
    ON AI.ID = AP.ACTIONITEMID
    WHERE AI.STATUSCODE = 1
    AND ISNULL(AP.GOAL,0) > 0
    AND AP.PETITIONCOUNT >= AP.GOAL
    AND AI.ID = @ACTIONITEMID
  END
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0