USP_DATAFORMTEMPLATE_CREATEORUPDATE_SP

CREATE procedure dbo.USP_DATAFORMTEMPLATE_CREATEORUPDATE_SP
(
    @ID uniqueidentifier output, 
    @TEMPLATENAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @RECORDTYPE nvarchar(50) = '',
    @MODE smallint,
    @LOADPROC nvarchar(128) = '', 
    @SAVEPROC nvarchar(128) = '',
    @FORMDEFXML xml,
    @EXCEPTIONSXML xml = '',
    @SPEC xml,
    @CONTEXTPARAMETERNAME nvarchar(128) = '',
    @CONTEXTRECORDTYPE nvarchar(50) = '',
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(60) = N'',
  @MARTKEY nvarchar(255) = null
)
as
set nocount on

declare @RecTypeID uniqueidentifier
declare @OBJID int
declare @ExistingID uniqueidentifier

if @MARTKEY = ''
  set @MARTKEY = null;

if @ID is null
    begin
      select @ExistingID = [ID] from dbo.[DATAFORMTEMPLATECATALOG] where [TEMPLATENAME] = @TEMPLATENAME
      set @ID = @ExistingID
    end
else
    select @ExistingID = [ID] from dbo.[DATAFORMTEMPLATECATALOG] where [ID] = @ID

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

if @SPECUINAME is null
    set @SPECUINAME = N'';

-- view mode

if @MODE = 0 
    begin
        set @SAVEPROC = ''
        set @EXCEPTIONSXML = ''
    end

if @MARTKEY is null 
  begin

    if len(@LOADPROC) = 0
        begin
            if @MODE <> 2
                begin
                    raiserror ('You must specify a valid load procedure name.', 16, 1)
                    return 1;
                end 
        end
    else
        begin
            select @OBJID = id from dbo.sysobjects where name = @LOADPROC and type in ('P', 'PC')
            if @OBJID is null
                begin
                    raiserror ('Procedure ''%s'' does not exist or you do not have security access.', 16, 1, @LOADPROC)
                    return 1;
                end
        end

    if len(@SAVEPROC) = 0
        begin
            if @MODE <> 0
                begin
                    raiserror ('You must specify a valid save procedure name.', 16, 1)
                    return 2;
                end 
        end
    else
        begin
            select @OBJID = id from dbo.sysobjects where type in ('P', 'PC') and name = @SAVEPROC
            if @OBJID is null
                begin
                    raiserror ('Procedure ''%s'' does not exist or you do not have security access.', 16, 1, @SAVEPROC)
                    return 2;
                end
        end

  end

declare @CONTEXTRECORDTYPEID uniqueidentifier;

if coalesce(@CONTEXTRECORDTYPE,'') <> ''
    begin
    exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME 
            @NAME=@CONTEXTRECORDTYPE,
            @CHANGEAGENTID=@CHANGEAGENTID,
            @RECORDTYPEID=@CONTEXTRECORDTYPEID output;
    end

declare @SECURITYUIFOLDER nvarchar(255);
declare @SECURITYUIDISPLAYFEATURE bit;

if @MODE = 0
    begin
        --view

        set @SECURITYUIFOLDER =
            coalesce(@SPEC.value(
                '
                declare namespace bbafx="bb_appfx_viewdataformtemplate";
                declare namespace c="bb_appfx_commontypes";
                /bbafx:ViewDataFormTemplateSpec[1]/@c:SecurityUIFolder
                '
                ,'nvarchar(255)')
            ,'');
        set @SECURITYUIDISPLAYFEATURE = cast(
            coalesce(@SPEC.value(
                '
                declare namespace bbfa="bb_appfx_viewdataformtemplate";
                /bbfa:ViewDataFormTemplateSpec[1]/@SecurityUIDisplayFeature
                ','bit')
            , 
                1
           ) as bit);
    end
else if @MODE = 1
    begin
        --edit

        set @SECURITYUIFOLDER=
            coalesce(@SPEC.value(
                '
                declare namespace bbafx="bb_appfx_editdataformtemplate";
                declare namespace c="bb_appfx_commontypes";
                /bbafx:EditDataFormTemplateSpec[1]/@c:SecurityUIFolder
                '
                ,'nvarchar(255)')
            ,'');
        set @SECURITYUIDISPLAYFEATURE = cast(
            coalesce(@SPEC.value(
                '
                declare namespace bbfa="bb_appfx_editdataformtemplate";
                /bbfa:EditDataFormTemplateSpec[1]/@SecurityUIDisplayFeature
                ','bit')
            , 
                1
           ) as bit);
    end
else if @MODE=2
    begin
        --add

        set @SECURITYUIFOLDER=
        coalesce(@SPEC.value(
                '
                declare namespace bbafx="bb_appfx_adddataformtemplate";
                declare namespace c="bb_appfx_commontypes";
                /bbafx:AddDataFormTemplateSpec[1]/@c:SecurityUIFolder
                '
                ,'nvarchar(255)')
        ,'');
        set @SECURITYUIDISPLAYFEATURE = cast(
            coalesce(@SPEC.value(
                '
                declare namespace bbfa="bb_appfx_adddataformtemplate";
                /bbfa:AddDataFormTemplateSpec[1]/@SecurityUIDisplayFeature
                ','bit')
            , 
                1
           ) as bit);
    end

--Add record type if it does not exist.

set @RecTypeID = null
if coalesce(@RECORDTYPE,'') <> ''
    exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME=@RECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@RecTypeID output;

--------------------------------------------------------------------------------------------------------------------

/* 
pdg 10.6.2010 move the determination of this metadata to loadspec time instead of at runtime 
*/    
--------------------------------------------------------------------------------------------------------------------

declare @HasChangeAgent bit = 0;
declare @HasTSLong bit = 0;
declare @HasAppUserForLoad bit = 0;
declare @HasAppUserForSave bit = 0;
declare @HasIDForLoad bit = 0;

if len(@LOADPROC) > 0 and @MARTKEY is null
begin
    select @HasTSLong = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @LOADPROC and PARAMETER_NAME = '@TSLONG';
    select @HasAppUserForLoad = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @LOADPROC and PARAMETER_NAME = '@CURRENTAPPUSERID';
    select @HasIDForLoad = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @LOADPROC and PARAMETER_NAME = '@ID';
end

if len(@SAVEPROC) > 0 and @MARTKEY is null
begin
    select @HasChangeAgent = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @SAVEPROC and PARAMETER_NAME = '@CHANGEAGENTID';    
    select @HasAppUserForSave = 1 from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_SCHEMA='dbo' and SPECIFIC_NAME = @SAVEPROC and PARAMETER_NAME = '@CURRENTAPPUSERID';
end
--------------------------------------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------------------------------------------------------------

declare @NOSECURITYREQUIRED bit=0;

if @MODE = 0
    set @NOSECURITYREQUIRED =
    coalesce(@SPEC.value(
        'declare namespace bbspec="bb_appfx_viewdataformtemplate";
         declare namespace c="bb_appfx_commontypes";
         /bbspec:ViewDataFormTemplateSpec[1]/@NoSecurityRequired'
        , 'bit')
    , 0);

if @MODE = 1
    set @NOSECURITYREQUIRED =
    coalesce(@SPEC.value(
        'declare namespace bbspec="bb_appfx_editdataformtemplate";
         declare namespace c="bb_appfx_commontypes";
         /bbspec:EditDataFormTemplateSpec[1]/@NoSecurityRequired'
        , 'bit')
    , 0);

if @MODE = 2
    set @NOSECURITYREQUIRED=
    coalesce(@SPEC.value(
        'declare namespace bbspec="bb_appfx_adddataformtemplate";
         declare namespace c="bb_appfx_commontypes";
         /bbspec:AddDataFormTemplateSpec[1]/@NoSecurityRequired'
        , 'bit')
    , 0);

----------------------------------------------------------------------------------------------------------------------------------------------


if @ExistingID is null 
    begin
        if @ID is null set @ID = NewID()

        insert into dbo.[DATAFORMTEMPLATECATALOG] (
            [ID], [TEMPLATENAME], [DESCRIPTION], [LOADPROCEDURE], [SAVEPROCEDURE], [RECORDTYPEID], [MODE], [FORMDEFINITIONXML], [EXCEPTIONSXML], [TEMPLATESPECXML], 
            [CONTEXTPARAMETERNAME], [CONTEXTRECORDTYPEID], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [SECURITYUIFOLDER], [HASAPPUSERFORLOAD], 
            [HASAPPUSERFORSAVE], [HASCHANGEAGENT], [HASIDFORLOAD], [HASTSLONG], [NOSECURITYREQUIRED], [SPECUINAME], [MARTKEY], [SECURITYUIDISPLAYFEATURE]
            )
            values (
            @ID, @TEMPLATENAME, @DESCRIPTION,  @LOADPROC, @SAVEPROC, @RecTypeID, @MODE, @FORMDEFXML, @EXCEPTIONSXML, @SPEC, 
            @CONTEXTPARAMETERNAME, @CONTEXTRECORDTYPEID, @CHANGEAGENTID, @CHANGEAGENTID, GETDATE(), GETDATE(), @SECURITYUIFOLDER, @HasAppUserForLoad,
            @HasAppUserForSave, @HasChangeAgent, @HasIDForLoad, @HasTSLong, @NOSECURITYREQUIRED, @SPECUINAME, @MARTKEY, @SECURITYUIDISPLAYFEATURE
            )
    end

else
    update dbo.[DATAFORMTEMPLATECATALOG] 
    set [TEMPLATENAME] = @TEMPLATENAME,
        [DESCRIPTION] = @DESCRIPTION, 
        [FORMDEFINITIONXML] = @FORMDEFXML,
        [RECORDTYPEID] = @RecTypeID,
        [MODE] = @MODE,
        [IMPLEMENTATIONTYPE] = 0,
        [LOADPROCEDURE] = @LOADPROC,
        [SAVEPROCEDURE] = @SAVEPROC,
        [EXCEPTIONSXML] = @EXCEPTIONSXML,
        [STATICPARAMETERDEFINITION] = null,
        [TEMPLATESPECXML] = @SPEC,
        [CONTEXTPARAMETERNAME] = @CONTEXTPARAMETERNAME,
        [CONTEXTRECORDTYPEID] = @CONTEXTRECORDTYPEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [SECURITYUIFOLDER] = @SECURITYUIFOLDER,
        [DATECHANGED] = getdate(),
        [HASAPPUSERFORLOAD] = @HasAppUserForLoad,
        [HASAPPUSERFORSAVE] = @HasAppUserForSave,
        [HASCHANGEAGENT] = @HasChangeAgent,
        [HASIDFORLOAD] = @HasIDForLoad,
        [HASTSLONG] = @HasTSLong,
        [NOSECURITYREQUIRED] = @NOSECURITYREQUIRED,
        [ASSEMBLYNAME] = '',
        [CLASSNAME] = '',
        [SPECUINAME] = @SPECUINAME,
        [MARTKEY] = @MARTKEY,
        [SECURITYUIDISPLAYFEATURE] = @SECURITYUIDISPLAYFEATURE
    where [ID] = @ID;

return 0;