USP_SMARTFIELD_ADDINSTANCE

Saves a new smart field instance.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SMARTFIELDCATALOGID	uniqueidentifier	IN
@SMARTFIELDDATAFORMITEM	xml	IN
@SMARTFIELDVALUEGROUPS	xml	IN
@NAME	nvarchar(100)	IN
@USEVALUEGROUP	bit	IN
@SYSTEMFIELD	bit	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@DATAFORMINSTANCECATALOGID	uniqueidentifier	IN
@DATEVALUEGROUPUNITCODE	tinyint	IN
@SOURCEQUERYVIEWCATALOGID	uniqueidentifier	IN
@SITESSELECTED	xml	IN
@SITEFILTERENABLEDFORINSTANCE	bit	IN
@DESCRIPTION	nvarchar(4000)	IN
@FEATUREID	uniqueidentifier	IN
@FEATURETYPE	tinyint	IN
@CURRENCYID	uniqueidentifier	IN
@SMARTFIELDCATEGORYCODEID	uniqueidentifier	IN
@SHOWINFUNDRAISERONTHEGO	bit	IN

Definition

Copy



CREATE procedure dbo.USP_SMARTFIELD_ADDINSTANCE
(
  @ID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @SMARTFIELDCATALOGID uniqueidentifier,
  @SMARTFIELDDATAFORMITEM xml = null,
  @SMARTFIELDVALUEGROUPS xml = null,
  @NAME nvarchar(100) = '',
  @USEVALUEGROUP bit = 0,
  @SYSTEMFIELD bit = 0,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATAFORMINSTANCECATALOGID uniqueidentifier,
  @DATEVALUEGROUPUNITCODE tinyint = 0,
  @SOURCEQUERYVIEWCATALOGID uniqueidentifier = null,
  @SITESSELECTED xml = null,
  @SITEFILTERENABLEDFORINSTANCE bit = 0,
  @DESCRIPTION nvarchar(4000) = '',
  @FEATUREID uniqueidentifier = null,
  @FEATURETYPE tinyint = 0,
  @CURRENCYID uniqueidentifier = null,
  @SMARTFIELDCATEGORYCODEID uniqueidentifier = null,
  @SHOWINFUNDRAISERONTHEGO bit = 0
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

  begin try
    if @ID is null
      set @ID = newid();

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

    set @CURRENTDATE = getdate();

    insert into dbo.SMARTFIELD
      (ID, NAME, DESCRIPTION, SMARTFIELDCATALOGID, SMARTFIELDDATAFORMITEM, USEVALUEGROUP, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SYSTEMFIELD, DATEVALUEGROUPUNITCODE, SOURCEQUERYVIEWCATALOGID, SITEFILTERENABLEDFORINSTANCE, CURRENCYID, SMARTFIELDCATEGORYCODEID, SHOWINFUNDRAISERONTHEGO)
    values
      (@ID, @NAME, isnull(@DESCRIPTION, ''), @SMARTFIELDCATALOGID, @SMARTFIELDDATAFORMITEM, @USEVALUEGROUP, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @SYSTEMFIELD, @DATEVALUEGROUPUNITCODE, @SOURCEQUERYVIEWCATALOGID, @SITEFILTERENABLEDFORINSTANCE, @CURRENCYID, @SMARTFIELDCATEGORYCODEID, @SHOWINFUNDRAISERONTHEGO);

    set @SMARTFIELDVALUEGROUPS = dbo.UFN_SMARTFIELDVALUEGROUP_GETGROUPS_2_SETITEMLISTXMLDEFAULTVALUES(@SMARTFIELDVALUEGROUPS);
    exec dbo.USP_SMARTFIELDVALUEGROUP_GETGROUPS_2_ADDFROMXML @ID, @SMARTFIELDVALUEGROUPS, @CHANGEAGENTID, @CURRENTDATE

    if @SITEFILTERENABLEDFORINSTANCE = 1
    begin
      -- Verify the user has permission for the sites being added

      /*
      this is no longer valid now that the smart field add/edit forms are NoSecurityRequired="true", SecurityUIDisplayFeature="false"
      if exists (    select SITEID from dbo.UFN_SMARTFIELDSITES_GET_FROMITEMLISTXML(@SITESSELECTED)
            where
              SITEID not in (    select SITEID
                      from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @FEATUREID, @FEATURETYPE)))
        raiserror('BBERR_NOPERMISSIONFORSITE', 13, 1);
      */

      exec dbo.USP_SMARTFIELDSITES_GET_ADDFROMXML @ID, @SITESSELECTED, @CHANGEAGENTID, @CURRENTDATE
    end
    /*
    this is no longer valid now that the smart field add/edit forms are NoSecurityRequired="true", SecurityUIDisplayFeature="false"
    else if (dbo.UFN_APPUSER_HASALLSITESFORFORM(@CURRENTAPPUSERID, @FEATUREID) = 0 and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('133f9bca-00f1-4007-9792-586b931340c6') = 1)
        raiserror('BBERR_NOPERMISSIONTODISABLESITESECURITY', 13, 1);
    */

    exec dbo.USP_SMARTFIELD_CREATESMARTFIELDTABLE @ID, @CHANGEAGENTID

    --Retrieve the ID of the query view that was created for this smart field.

    declare @QUERYVIEWID uniqueidentifier

    select 
      @QUERYVIEWID = QUERYVIEWCATALOG.ID
    from 
      dbo.SMARTFIELD
      inner join dbo.TABLECATALOG on TABLECATALOG.ID = SMARTFIELD.TABLECATALOGID
      inner join dbo.QUERYVIEWCATALOG on QUERYVIEWCATALOG.OBJECTNAME = 'V_QUERY_' + TABLECATALOG.TABLENAME
    where 
      SMARTFIELD.NAME = @NAME and 
      QUERYVIEWCATALOG.DISPLAYNAME = @NAME and 
      SMARTFIELD.SMARTFIELDCATALOGID = @SMARTFIELDCATALOGID

    --Grant permission to that query view to each role the current user is in that grants rights to this add form.

    insert into dbo.SYSTEMROLEPERM_QUERYVIEW
      (ID, SYSTEMROLEID, QUERYVIEWCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select 
      newid(), ROLELIST.SYSTEMROLEID, @QUERYVIEWID, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from 
      dbo.UFN_APPUSER_GETSYSTEMROLELIST(@CURRENTAPPUSERID) as ROLELIST
      inner join dbo.SYSTEMROLEPERM_DATAFORMINSTANCE on SYSTEMROLEPERM_DATAFORMINSTANCE.SYSTEMROLEID = ROLELIST.SYSTEMROLEID
    where 
      SYSTEMROLEPERM_DATAFORMINSTANCE.DATAFORMINSTANCECATALOGID = @DATAFORMINSTANCECATALOGID
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;