UFN_QUERY_DATAFORMINSTANCE_FORMFIELDS

CREATE function dbo.UFN_QUERY_DATAFORMINSTANCE_FORMFIELDS
(
    @DATAFORMINSTANCEID uniqueidentifier
)
returns @FIELDTABLE TABLE
(
    ID uniqueidentifier not null,
    FIELDID varchar(100),
    CAPTION varchar(100),
    DATATYPE varchar(50),
    READONLY bit,
    HIDDEN bit,
    REQUIRED bit,
    CUSTOMIZED bit,

    MAXLENGTH integer,
    DESCRIPTION nvarchar(max),
    MAXVALUE integer,
    MINVALUE integer,
    PRECISION integer,
    SCALE integer,
    TIMEPRECISION integer,
    DATETIMEWITHOFFSETPRECISION integer,
    INCLUDETIMEONDATE bit,
    ALLOWMONTHDAYONFUZZYDATE bit,
    DEFAULTVALUETEXT nvarchar(max),
    FIXEDVALUETEXT nvarchar(max),
    CAPTIONRESOURCEKEY nvarchar(max),
    CATEGORY nvarchar(max),
    CATEGORYRESOURCEKEY nvarchar(max),
    DONOTAPPLYFORMAT bit,
    MULTILINE bit,
    APPLYPHONEFORMATTING bit,
    ALLOWZOOM bit,
    AVAILABLETOCLIENT bit,
    DESCRIPTORTYPE nvarchar(20),
    INSTALLEDPRODUCTSLIST nvarchar(max),
    INSTALLED bit
)
with execute as caller
as begin

    declare @FORMUIXML xml;
    select @FORMUIXML = FORMUIXML
        from dbo.DATAFORMINSTANCECATALOG 
        where ID = @DATAFORMINSTANCEID;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert @FIELDTABLE
    select 
        @DATAFORMINSTANCEID as ID,
        dataform.fields.value('@FieldID', 'nvarchar(100)') as FIELDID,
        coalesce(DFFCC.CAPTION, dataform.fields.value('@Caption', 'nvarchar(100)')) as CAPTION,
        coalesce(dataform.fields.value('@DataType', 'nvarchar(50)'), 'String') as DATATYPE,
        coalesce(dataform.fields.value('@ReadOnly', 'bit'), 0) as READONLY,
        coalesce(DFFC.ISHIDDEN, dataform.fields.value('@Hidden', 'bit'), 0) as HIDDEN,
        coalesce(DFFC.ISREQUIRED, dataform.fields.value('@Required', 'bit'), 0) as REQUIRED,
        case when DFFC.ID is null then 0 else 1 end as CUSTOMIZED,

        coalesce(dataform.fields.value('@MaxLength', 'integer'), 0) as MAXLENGTH,
        dataform.fields.value('@Description', 'nvarchar(max)') as DESCRIPTION,
        coalesce(dataform.fields.value('@MaxValue', 'integer'), 0) as MAXVALUE,
        coalesce(dataform.fields.value('@MinValue', 'integer'), -1) as MINVALUE,
        coalesce(dataform.fields.value('@Precision', 'integer'), -1) as PRECISION,
        coalesce(dataform.fields.value('@Scale', 'integer'), -1) as SCALE,
        coalesce(dataform.fields.value('@TimePrecision', 'integer'), 0) as TIMEPRECISION,
        coalesce(dataform.fields.value('@DateTimeWithOffsetPrecision', 'integer'), 3) as DATETIMEWITHOFFSETPRECISION,
        coalesce(dataform.fields.value('@IncludeTimeOnDate', 'bit'), 0) as INCLUDETIMEONDATE,
        coalesce(dataform.fields.value('@AllowMonthDayOnFuzzyDate', 'bit'), 0) as ALLOWMONTHDAYONFUZZYDATE,
        dataform.fields.value('@DefaultValueText', 'nvarchar(max)') as DEFAULTVALUETEXT,
        dataform.fields.value('@FixedValueText', 'nvarchar(max)') as FIXEDVALUETEXT,
        dataform.fields.value('@CaptionResourceKey', 'nvarchar(max)') as CAPTIONRESOURCEKEY,
        dataform.fields.value('@Category', 'nvarchar(max)') as CATEGORY,
        dataform.fields.value('@CategoryResourceKey', 'nvarchar(max)') as CATEGORYRESOURCEKEY,
        coalesce(dataform.fields.value('@DoNotApplyFormat', 'bit'), 0) as DONOTAPPLYFORMAT,
        coalesce(dataform.fields.value('@Multiline', 'bit'), 0) as MULTILINE,
        coalesce(dataform.fields.value('@ApplyPhoneFormatting', 'bit'), 0) as APPLYPHONEFORMATTING,
        coalesce(dataform.fields.value('@AllowZoom', 'bit'), 0) as ALLOWZOOM,
        coalesce(dataform.fields.value('@AvailableToClient', 'bit'), 1) as AVAILABLETOCLIENT,
        case
            when dataform.fields.exist('common:CodeTable') = 1 then 'CodeTable'
            when dataform.fields.exist('common:ValueList') = 1  then 'ValueList'
            when dataform.fields.exist('common:SimpleDataList') = 1  then 'SimpleDataList'
            when dataform.fields.exist('common:Collection') = 1  then 'Collection'
            when dataform.fields.exist('common:SearchList') = 1  then 'SearchList'
            when dataform.fields.exist('common:Link') = 1  then 'Link'
            when dataform.fields.exist('common:File') = 1  then 'File'
            when dataform.fields.exist('common:CreditCard') = 1  then 'CreditCard'
            when dataform.fields.exist('common:Html') = 1  then 'Html'
            else 'None'
        end as DESCRIPTORTYPE,
        dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(dataform.fields.query('common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
        dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(dataform.fields.query('common:InstalledProductList')) as INSTALLED

    from @FORMUIXML.nodes('common:FormMetaData/common:FormFields/common:FormField') as dataform(fields)    
        left join dbo.DATAFORMFIELDCHARACTERISTIC as DFFC on @DATAFORMINSTANCEID = DFFC.DATAFORMINSTANCEID
            and dataform.fields.value('@FieldID', 'nvarchar(100)') = DFFC.FIELDID
        left join dbo.DATAFORMFIELDCHARACTERISTICCAPTION as DFFCC on DFFCC.DATAFORMFIELDCHARACTERISTICID = DFFC.ID

    return;

end