USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLAN_PAGEDATA

The load procedure used by the view dataform template "Stewardship Plan Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PLANID	uniqueidentifier	INOUT	PLANID
@CONSTITUENTNAME	nvarchar(154)	INOUT	CONSTITUENTNAME
@ISGROUP	bit	INOUT	ISGROUP
@ISORG	bit	INOUT	ISORG
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@GRANTEDCONTITUENTINTERESTS	bit	INOUT	GRANTEDCONTITUENTINTERESTS
@GRANTEDCONTITUENTRECOGNITIONS	bit	INOUT	GRANTEDCONTITUENTRECOGNITIONS
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@PLANNAME	nvarchar(100)	INOUT	PLANNAME
@HASMANAGER	bit	INOUT	HASMANAGER
@HASPENDINGSTEPS	bit	INOUT	HASPENDINGSTEPS
@ISACTIVE	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLAN_PAGEDATA
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @PLANID uniqueidentifier = null output,
  @CONSTITUENTNAME nvarchar(154) = null output,
  @ISGROUP bit = null output,
  @ISORG bit = null output,    
  @CURRENTAPPUSERID uniqueidentifier = null,
  @GRANTEDCONTITUENTINTERESTS bit = null output,
  @GRANTEDCONTITUENTRECOGNITIONS bit = null output,
  @CONSTITUENTID uniqueidentifier = null output,
  @PLANNAME nvarchar(100) = null output,
  @HASMANAGER bit = null output,
  @HASPENDINGSTEPS bit = null output,
  @ISACTIVE bit = null output
)
as begin
  set nocount on;

  set @DATALOADED = 0;

  select 
    @DATALOADED = 1,
    @PLANID = STEWARDSHIPPLAN.ID,
    @PLANNAME = STEWARDSHIPPLAN.NAME,
    @CONSTITUENTID = CONSTITUENT.ID,
    @CONSTITUENTNAME = NF.NAME,
    @ISGROUP = CONSTITUENT.ISGROUP,
    @ISORG = CONSTITUENT.ISORGANIZATION,
    @HASMANAGER = case when STEWARDSHIPPLAN.MANAGERID is null then 0 else 1 end,
    @HASPENDINGSTEPS = case when exists (select 1 from dbo.STEWARDSHIPPLANSTEP where PLANID = STEWARDSHIPPLAN.ID and COMPLETED = 0) then 1 else 0 end,
    @ISACTIVE = STEWARDSHIPPLAN.ISACTIVE
  from
    dbo.STEWARDSHIPPLAN 
  inner join
    dbo.CONSTITUENT on CONSTITUENT.ID = STEWARDSHIPPLAN.CONSTITUENTID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
  where
    STEWARDSHIPPLAN.ID = @ID

  declare @CONSTITUENTINTERESTDATALISTID uniqueidentifier = '0349A43B-0F0F-4C7C-B59E-EEAC3D22BE56'
  declare @CONSTITUENTRECOGNITIONDATALISTID uniqueidentifier = 'd1f5df19-4662-4439-90a3-05ddd6baeafb'

  set @GRANTEDCONTITUENTINTERESTS = 0;
  set @GRANTEDCONTITUENTRECOGNITIONS = 0;

  if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
  begin
    set @GRANTEDCONTITUENTINTERESTS = 1;
    set @GRANTEDCONTITUENTRECOGNITIONS = 1;
  end
  else
    begin
      declare @CONSTITUENTSITES table(SITEID uniqueidentifier)

      insert into @CONSTITUENTSITES
      select SITEID from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID)

      if (select count(SITEID) from @CONSTITUENTSITES) = 0
      begin
        if dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, null, @CONSTITUENTINTERESTDATALISTID, 2) = 1
        begin
          set @GRANTEDCONTITUENTINTERESTS = 1;
        end

        if dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, null, @CONSTITUENTRECOGNITIONDATALISTID, 2) = 1
        begin
          set @GRANTEDCONTITUENTRECOGNITIONS = 1;
        end
      end
      else
      begin
        if (select count(SITEID) from @CONSTITUENTSITES where dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, SITEID, @CONSTITUENTINTERESTDATALISTID, 2) = 1) > 0
        begin
          set @GRANTEDCONTITUENTINTERESTS = 1;
        end

        if (select count(SITEID) from @CONSTITUENTSITES where dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID,SITEID,@CONSTITUENTRECOGNITIONDATALISTID,2) = 1) > 0
        begin
          set @GRANTEDCONTITUENTRECOGNITIONS = 1;
        end
      end
  end

  return 0;
end