USP_DATAFORM_SETFIELDCHARACTERISTICS

CREATE procedure dbo.USP_DATAFORM_SETFIELDCHARACTERISTICS
(
  @DATAFORMINSTANCEID uniqueidentifier,
  @FIELDID nvarchar(200),
  @CULTURE nvarchar(5),
  @CAPTION nvarchar(200),
  @ISHIDDEN bit,
  @ISREQUIRED bit,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  begin try
    if dbo.UFN_DATAFORM_ISINSTANCEFORMFIELDIDVALID(@DATAFORMINSTANCEID, @FIELDID) = 0 begin
      raiserror('BBERR_DATAFORMFIELDCHARACTERISTIC_VALIDFIELDID', 13, 1);
    end

    if @ISHIDDEN = 0 and dbo.UFN_DATAFORM_ISINSTANCEFORMFIELDHIDDEN(@DATAFORMINSTANCEID, @FIELDID) = 1 begin
      raiserror('BBERR_DATAFORMFIELDCHARACTERISTIC_VALIDHIDDENFIELD', 13, 1);
    end

    if @ISREQUIRED = 0 and dbo.UFN_DATAFORM_ISINSTANCEFORMFIELDREQUIRED(@DATAFORMINSTANCEID, @FIELDID) = 1 begin
      raiserror('BBERR_DATAFORMFIELDCHARACTERISTIC_VALIDREQUIREDFIELD', 13, 1);
    end

    if @ISREQUIRED = 1 and dbo.UFN_DATAFORM_ISINSTANCEFORMFIELDREADONLY(@DATAFORMINSTANCEID, @FIELDID) = 1 begin
      raiserror('BBERR_DATAFORMFIELDCHARACTERISTIC_VALIDREADONLYFIELD', 13, 1);
    end

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

    declare @ROWFIELDID uniqueidentifier
    -- check for existing row of this field characteristic; determine for insert or update.

    select @ROWFIELDID = ID from dbo.DATAFORMFIELDCHARACTERISTIC where DATAFORMINSTANCEID = @DATAFORMINSTANCEID and FIELDID = @FIELDID;

    if @ROWFIELDID is null begin

      set @ROWFIELDID = newid();

      insert into dbo.DATAFORMFIELDCHARACTERISTIC (
        ID, 
        DATAFORMINSTANCEID, 
        FIELDID, 
        ISHIDDEN, 
        ISREQUIRED, 
        ADDEDBYID, 
        CHANGEDBYID
      ) values (
        @ROWFIELDID, 
        @DATAFORMINSTANCEID, 
        @FIELDID, 
        @ISHIDDEN, 
        @ISREQUIRED, 
        @CHANGEAGENTID, 
        @CHANGEAGENTID
      );

    end else begin

      update dbo.DATAFORMFIELDCHARACTERISTIC set 
        ISHIDDEN = @ISHIDDEN, 
        ISREQUIRED = @ISREQUIRED, 
        CHANGEDBYID = @CHANGEAGENTID
      where ID = @ROWFIELDID;

    end

    -- check for existing caption of this field/culture  

    if not exists(select ID from dbo.DATAFORMFIELDCHARACTERISTICCAPTION where DATAFORMFIELDCHARACTERISTICID = @ROWFIELDID and CULTURE = @CULTURE) begin

      insert into dbo.DATAFORMFIELDCHARACTERISTICCAPTION (
        DATAFORMFIELDCHARACTERISTICID, 
        CULTURE, 
        CAPTION, 
        ADDEDBYID, 
        CHANGEDBYID
      ) values (
        @ROWFIELDID, 
        @CULTURE, 
        coalesce(@CAPTION, ''), 
        @CHANGEAGENTID, 
        @CHANGEAGENTID
      );

    end else begin

      update dbo.DATAFORMFIELDCHARACTERISTICCAPTION set 
        CAPTION = coalesce(@CULTURE, ''), 
        CHANGEDBYID = @CHANGEAGENTID
      where CULTURE = @CULTURE and DATAFORMFIELDCHARACTERISTICID = @ROWFIELDID;

    end
  end try

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

  return 0;