用户自定义编码及系统单据状态、单据类型的颜色配置 - .Net 平台应用与技巧

# 一、概述
如大家所知，OrBit平台已提供通过存储过程数据集返回的ColorControlId字段，实现网格样式（背景色、字体色）的简单配置。随着这项应用的普及，需要频繁的在存储过程中拼接颜色的控制码（ColorControlId），现提供一种在前端配置颜色（颜色选择器/RGB）、存储过程调用函数输出颜色控制码（ColorControlId）的便捷方法。实现方式如下：

[示例]
![](http://121.37.203.18:8006/media//202206/2022-06-16_172955.png)

OAP文件：
[【附件】OrBitMOM 22_2723_62254(颜色配置（UserCode、Bill）).oap](/media/attachment/2022/06/OrBitMOM_22_2723_62254颜色配置UserCodeBill.oap)

# 二、应用实例
1. 用户自定义编码(UserCode)
![](http://121.37.203.18:8006/media//202206/2022-06-16_172804.png)

2. 系统单据状态(BillState)
![](http://121.37.203.18:8006/media//202206/2022-06-16_172818.png)

3. 系统单据类型(BillType)
![](http://121.37.203.18:8006/media//202206/2022-06-16_172834.png)

4. 其他

# 三、存储过程调用
1. 存储过程输出颜色控制码（ColorControlId）
![](/media//202206/2022-06-17_111011.png)

2. 函数说明（Fn_BillAttrColorCode）
【参数】
@AttrName 	-- 欲设值样式的单元格列名
@AttrValue 	-- 主键Id
 
【脚本】
```sql
ALTER function [dbo].[Fn_BillAttrColorCode](
	@AttrName nvarchar(50),		-- 运用颜色显示的列名
	@AttrValue nvarchar(500)	-- 状态值(状态Id或者状态码)
)
returns nvarchar(max) 
as
begin

declare 
		@var_Result nvarchar(max) = '',
		@var_XCode nvarchar(50) = '',
		@var_YCode nvarchar(50) = '',
		@var_Z nvarchar(50) = '',
		@var_PKHeader nvarchar(4) = null

set @var_PKHeader = left(@AttrValue,4)
	-- 单据状态
	if @var_PKHeader = 'BLST' 
	begin
		select 
			@var_XCode = 'X' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+BackColor+')','1'))),
			@var_YCode = 'Y' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+ForeColor+')','0'))),
			@var_Z = IIF(ISNULL(IsBold,0) = 1,'Z','')
		from BillStates bs 
		where bs.BillStatesId = @AttrValue
	end
	-- 单据类型
	else if @var_PKHeader = 'BLTP'
	begin
		select 
			@var_XCode = 'X' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+BackColor+')','1'))),
			@var_YCode = 'Y' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+ForeColor+')','0'))),
			@var_Z = IIF(ISNULL(IsBold,0) = 1,'Z','')
		from BillType bt
		where bt.BillTypeId = @AttrValue
	end
	-- 异常类型
	else if @var_PKHeader = 'EXTP'
	begin
		select
			@var_XCode = 'X' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+BackColor+')','1'))),
			@var_YCode = 'Y' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+ForeColor+')','0'))),
			@var_Z = IIF(ISNULL(IsBold,0) = 1,'Z','') 
		from ExceptionType et 
		where et.ExceptionTypeId = @AttrValue
	end
	-- 用户编码
	else if @var_PKHeader = 'URC1'
	begin
		select
			@var_XCode = 'X' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+BackColor+')','1'))),
			@var_YCode = 'Y' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+ForeColor+')','0'))),
			@var_Z = IIF(ISNULL(IsBold,0) = 1,'Z','') 
		from UserCode uc 
		where uc.UserCodeId = @AttrValue
	end	
	-- 图例
	else if @var_PKHeader = 'CLGD'
	begin
		select
			@var_XCode = 'X' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+BackColor+')','1'))),
			@var_YCode = 'Y' + ltrim(rtrim(dbo.Fn_IsNullOrEmpty('('+ForeColor+')','0'))),
			@var_Z = IIF(ISNULL(IsBold,0) = 1,'Z','') 
		from Calendar_Legend uc 
		where uc.Calendar_LegendId = @AttrValue
	end

if @AttrName = 'CT_X'
	begin
		set @var_Result = @var_XCode
	end
	else if  @AttrName = 'CT_Y'
	begin
		set @var_Result = @var_YCode+@var_Z
	end
	else 
	begin
		set @var_Result = @var_XCode + @var_YCode + @var_Z
		if isnull(@var_Result,'') != ''
		begin
			set @var_Result = '['+ @AttrName + ':' + @var_Result + ']'
		end
	end

return @var_Result
end
```
【调用示例】
![](/media//202206/2022-06-17_135918.png)
```sql
SELECT  EAM_EqAssetMaintRecordId,
		EAM_EqAssetMaintFormId,
		MaintTypeId,
		MaintType,
		MaintItemId,
		MaintPosition,
		MaintItem,
		MaintStateId,
		MaintStates.FNameDesc AS MaintState,
		MaintDate,
		MaintUser,
		GL_AuditState AS GL_AuditStateId,
		GL_AuditStates.FNameDesc AS GL_AuditState,
		GL_AduitDate,
		GroupLeader,
		AM_AuditState AS AM_AuditStateId,
		AM_AuditStates.FNameDesc AS AM_AuditState, 
		AM_AuditDate,
		AssetManager,
		MaintMethod,
		MaintStandard,
		ExceptionHandleStd,
		ImportanceLevel,
		MaintConditionId,
		MaintCondition,
		MaintItemType,
		Cycle,
		RspPerson,
		ResultTypeId,
		ResultType,
		LowerLimit,
		UpperLimit,
		MaintValue,
		MaintResult,
		Remark,
		ExceptionHandleId,
		ExceptionHandle,
		IsDoublePlan,
		MaintStdHours,
		MaintActHours,
		StartTime,
		EndTime,
		PlanSchemeId,
		PlanScheme,
		PlanDes,
		SetValue,
		EaryWarningValue,
		FstPlanDate,
		CurrWorkQty,
		AMR.CreateDate,
		'[RowColor:Z]' + dbo.Fn_BillAttrColorCode('MaintState',MaintStates.FId) +
		dbo.Fn_BillAttrColorCode('GL_AuditState',GL_AuditStates.FId) +
		dbo.Fn_BillAttrColorCode('AM_AuditState',AM_AuditStates.FId) AS ColorControlId
FROM	EAM_EqAssetMaintRecord AMR
LEFT OUTER JOIN dbo.TFn_UserCodeToTable('EAM MaintStates/维保状态') MaintStates ON AMR.MaintStateId = MaintStates.FId
LEFT OUTER JOIN dbo.TFn_UserCodeToTable('EAM MaintAuditStates/维保审核状态') GL_AuditStates ON AMR.GL_AuditState = GL_AuditStates.FName
LEFT OUTER JOIN dbo.TFn_UserCodeToTable('EAM MaintAuditStates/维保审核状态') AM_AuditStates ON AMR.AM_AuditState = AM_AuditStates.FName 
```