Как сделать Assesment форму на сайте - оценка объекта по параметрам на сайте

Как реализовать форму оценки по параметрам

Добавляем в форме assesment столбцы f1-f11 с типом Слайдер. 

Прописываем процедуру GetItem: 

CREATE PROCEDURE [dbo].[fm_assesment_getItem]
    @itemID int,
	@username nvarchar(256)

AS
BEGIN
	declare @defaultValue int = 50
    declare @typeCode nvarchar(24)='hr'

	declare @isHR bit
    set @isHR = dbo.[sec_isUserInRole](@username, 'hrManager')
 	declare @userID int
    select @userID = id from as_users where username=@username

	if(@isHR =0) begin
    	select 1
        select '{"title": "Нет доступа к данным"}' NoForm
        return
    end

	-- SELECT 1
	select
    	try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f1'), @defaultValue) as int) f1,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f2'), @defaultValue) as int) f2,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f3'), @defaultValue) as int) f3,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f4'), @defaultValue) as int) f4,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f5'), @defaultValue) as int) f5,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f6'), @defaultValue) as int) f6,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f7'), @defaultValue) as int) f7,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f8'), @defaultValue) as int) f8,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f9'), @defaultValue) as int) f9,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f10'), @defaultValue) as int) f10,
        try_cast(isnull((select value from as_assesmentLog where itemID=@itemID and typeCode= @typeCode and fieldCode='f11'), @defaultValue) as int) f11

	-- SELECT 2
	select '' Subtitle,
    	'h3' headerTag,
    	'line' progressType,
        1 LineLabel
END

Прописываем процедуру progress bar: 

CREATE PROCEDURE [dbo].[fm_assesment_progress]
   @username nvarchar(256),
   @itemID int,
   @parameters ExtendedDictionaryParameter READONLY
AS
BEGIN
	declare @typeCode nvarchar(24)='hr'

	declare @isHR bit
    set @isHR = dbo.[sec_isUserInRole](@username, 'hrManager')

	if(@isHR =0) begin
    	select 1 Result, '' Msg
        return
    end
	declare @val int = 0

	declare @f1 int, @f1K float = 0.99, @f1E bit
	select @f1 = try_cast(Value2 as int) from @parameters where [key]='f1'
    declare @f2 int, @f2K float = 0.99, @f2E bit
	select @f2 = try_cast(Value2 as int) from @parameters where [key]='f2'
    declare @f3 int, @f3K float = 0.99, @f3E bit
	select @f3 = try_cast(Value2 as int) from @parameters where [key]='f3'
    declare @f4 int, @f4K float = 0.99, @f4E bit
	select @f4 = try_cast(Value2 as int) from @parameters where [key]='f4'
    declare @f5 int, @f5K float = 0.99, @f5E bit
	select @f5 = try_cast(Value2 as int) from @parameters where [key]='f5'
    declare @f6 int, @f6K float = 0.99, @f6E bit
	select @f6 = try_cast(Value2 as int) from @parameters where [key]='f6'
    declare @f7 int, @f7K float = 0.99, @f7E bit
	select @f7 = try_cast(Value2 as int) from @parameters where [key]='f7'
    declare @f8 int, @f8K float = 0.99, @f8E bit
	select @f8 = try_cast(Value2 as int) from @parameters where [key]='f8'
    declare @f9 int, @f9K float = 0.99, @f9E bit
	select @f9 = try_cast(Value2 as int) from @parameters where [key]='f9'
    declare @f10 int, @f10K float = 0.99, @f10E bit
	select @f10 = try_cast(Value2 as int) from @parameters where [key]='f10'
    declare @f11 int, @f11K float = 0.99, @f11E bit
	select @f11 = try_cast(Value2 as int) from @parameters where [key]='f11'

	select
    	@f1E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f1' ), 1, 0),
        @f2E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f2' ), 1, 0),
        @f3E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f3' ), 1, 0),
        @f4E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f4' ), 1, 0),
        @f5E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f5' ), 1, 0),
        @f6E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f6' ), 1, 0),
        @f7E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f7' ), 1, 0),
        @f8E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f8' ), 1, 0),
        @f9E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f9' ), 1, 0),
        @f10E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f10' ), 1, 0),
        @f11E = iif( exists (select id from as_assesmentLog where itemID = @itemID and typeCode=@typeCode and fieldCode='f11' ), 1, 0)

    if(@f1E=1) update as_assesmentLog set value = @f1, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f1'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f1', @f1, getdate(), @username)

    if(@f2E=1) update as_assesmentLog set value = @f2, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f2'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f2', @f2, getdate(), @username)

    if(@f3E=1) update as_assesmentLog set value = @f3, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f3'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f3', @f3, getdate(), @username)

    if(@f4E=1) update as_assesmentLog set value = @f4, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f4'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f4', @f4, getdate(), @username)

    if(@f5E=1) update as_assesmentLog set value = @f5, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f5'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f5', @f5, getdate(), @username)

    if(@f6E=1) update as_assesmentLog set value = @f6, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f6'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f6', @f6, getdate(), @username)

    if(@f7E=1) update as_assesmentLog set value = @f7, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f7'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f7', @f7, getdate(), @username)

    if(@f8E=1) update as_assesmentLog set value = @f8, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f8'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f8', @f8, getdate(), @username)

    if(@f9E=1) update as_assesmentLog set value = @f9, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f9'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f9', @f9, getdate(), @username)

    if(@f10E=1) update as_assesmentLog set value = @f10, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f10'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f10', @f10, getdate(), @username)

    if(@f11E=1) update as_assesmentLog set value = @f11, updated = getdate(), updatedBy=@username where itemID = @itemID and typeCode=@typeCode and fieldCode='f11'
    else insert into [as_assesmentLog] (itemID, typeCode, fieldCode, value, updated, updatedBy) values(@itemID, @typeCode, 'f11', @f11, getdate(), @username)

    set @val =  (
      	@f1 * @f1K  + @f2 * @f2K  +@f3 * @f3K  +@f4 * @f4K  +@f5 * @f5K  +
      	@f6 * @f6K  +@f7 * @f7K  +@f8 * @f8K  +@f9 * @f9K  +@f10 * @f10K  +@f11 * @f11K
    ) /
    (
    	100 * @f1K  + 100 * @f2K  + 100 * @f3K  + 100 * @f4K  + 100 * @f5K  +
      	100 * @f6K  + 100 * @f7K  + 100 * @f8K  + 100 * @f9K  + 100 * @f10K  + 100 * @f11K
    )
    * 100

    declare @text nvarchar(128)
    set @text= case
    	when @val < 30 then 'Категория D (точно не работаем)'
    	when @val < 50 then 'Категория C'
    	when @val < 75 then 'Категория B'
    	else 'Категория A'
    end

    declare @valID int = (select id from as_assesmentValues where itemID = @itemID and typeCode= @typeCode)
    if(@valID is null) begin
    	insert into as_assesmentValues(itemID, typeCode, value) values(@itemID, @typeCode, @val)
    end else begin
    	update as_assesmentValues set value = @val where id = @valID
    end

    -- SELECT 1
	select 1 Result, '' Msg

    -- SELECT 2 набор индикаторов прогресса
    select 'Общая оценка ('+cast(@val as nvarchar)+')' Title, @val Value,  @text  [Text]

END

Универсальная структура таблиц БД: 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[as_assesmentLog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[itemID] [int] NULL,
	[value] [int] NULL,
	[updated] [datetime] NULL,
	[updatedBy] [nvarchar](128) NULL,
	[typeCode] [nvarchar](24) NULL,
	[fieldCode] [nvarchar](128) NULL,
 CONSTRAINT [PK_as_assesmentLog] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[as_assesmentValues](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[itemID] [int] NULL,
	[typeCode] [nvarchar](24) NULL,
	[value] [float] NULL,
 CONSTRAINT [PK_as_assesmentValues] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO