198 lines
9.2 KiB
Transact-SQL
198 lines
9.2 KiB
Transact-SQL
/******
|
|
新部署环境 枚举数据
|
|
wyw
|
|
2022-08-12
|
|
*******/
|
|
|
|
INSERT [dbo].[T_FM_ENUMS] ([ID], [NAME], [VALUE], [CODE], [NUM], [IS_DELETED], [ORG_ID], [ENTITY_ORG_TPYE], [FORM_ID], [FLOW_STATUS], [FLOW_SEND_STATUS], [FLOW_ID], [CREATE_TIME], [MODIFY_TIME], [CREATER_ID], [MODIFIER_ID]) VALUES (N'11c61cc0-3ed1-4600-9089-2028f764fb71', N'公司级', 3, N'HMLevleEnum', 1, 0, N'b043b28b-bbc3-c452-6052-4fba1457abfa', 0, NULL, 0, 0, NULL, CAST(N'2022-04-22T18:59:26.2933333' AS DateTime2), CAST(N'2022-04-22T18:59:26.2966667' AS DateTime2), N'8f26c255-fdfd-47ff-b83e-6309815958dd', N'8f26c255-fdfd-47ff-b83e-6309815958dd')
|
|
GO
|
|
INSERT [dbo].[T_FM_ENUMS] ([ID], [NAME], [VALUE], [CODE], [NUM], [IS_DELETED], [ORG_ID], [ENTITY_ORG_TPYE], [FORM_ID], [FLOW_STATUS], [FLOW_SEND_STATUS], [FLOW_ID], [CREATE_TIME], [MODIFY_TIME], [CREATER_ID], [MODIFIER_ID]) VALUES (N'11c61cc0-3ed1-4600-9089-2028f764fb72', N'部门级', 0, N'HMLevleEnum', 2, 0, N'b043b28b-bbc3-c452-6052-4fba1457abfa', 0, NULL, 0, 0, NULL, CAST(N'2022-04-22T18:59:26.2933333' AS DateTime2), CAST(N'2022-04-22T18:59:26.2966667' AS DateTime2), N'8f26c255-fdfd-47ff-b83e-6309815958dd', N'8f26c255-fdfd-47ff-b83e-6309815958dd')
|
|
GO
|
|
INSERT [dbo].[T_FM_ENUMS] ([ID], [NAME], [VALUE], [CODE], [NUM], [IS_DELETED], [ORG_ID], [ENTITY_ORG_TPYE], [FORM_ID], [FLOW_STATUS], [FLOW_SEND_STATUS], [FLOW_ID], [CREATE_TIME], [MODIFY_TIME], [CREATER_ID], [MODIFIER_ID]) VALUES (N'11c61cc0-3ed1-4600-9089-2028f764fb73', N'车间级', 1, N'HMLevleEnum', 3, 0, N'b043b28b-bbc3-c452-6052-4fba1457abfa', 0, NULL, 0, 0, NULL, CAST(N'2022-04-22T18:59:26.2933333' AS DateTime2), CAST(N'2022-04-22T18:59:26.2966667' AS DateTime2), N'8f26c255-fdfd-47ff-b83e-6309815958dd', N'8f26c255-fdfd-47ff-b83e-6309815958dd')
|
|
GO
|
|
INSERT [dbo].[T_FM_ENUMS] ([ID], [NAME], [VALUE], [CODE], [NUM], [IS_DELETED], [ORG_ID], [ENTITY_ORG_TPYE], [FORM_ID], [FLOW_STATUS], [FLOW_SEND_STATUS], [FLOW_ID], [CREATE_TIME], [MODIFY_TIME], [CREATER_ID], [MODIFIER_ID]) VALUES (N'11c61cc0-3ed1-4600-9089-2028f764fb74', N'班组级', 2, N'HMLevleEnum', 4, 0, N'b043b28b-bbc3-c452-6052-4fba1457abfa', 0, NULL, 0, 0, NULL, CAST(N'2022-04-22T18:59:26.2933333' AS DateTime2), CAST(N'2022-04-22T18:59:26.2966667' AS DateTime2), N'8f26c255-fdfd-47ff-b83e-6309815958dd', N'8f26c255-fdfd-47ff-b83e-6309815958dd')
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/******
|
|
数据结构修改 检查项目分类添加 区域 限制 数据初始化
|
|
wyw
|
|
2022-08-29
|
|
*******/
|
|
insert into T_BS_CHECK_PROJECT_CATEGORY_OBJECT
|
|
select NEWID() ID, CHECKOBJECT,PROJECT_CATEGORY_ID,0 IS_DELETED, ORG_ID as ORG_ID,0 ENTITY_ORG_TPYE,null FORM_ID,0 FLOW_STATUS,0 FLOW_SEND_STATUS,null FLOW_ID,GETDATE() CREATE_TIME,null MODIFY_TIME,null CREATER_ID ,null MODIFIER_ID from (
|
|
select distinct CHECKOBJECT, T_BS_CHECK_PROJECT_PROJECT_CATEGORY.CHECK_PROJECT_CATEGORY_ID PROJECT_CATEGORY_ID ,T_BS_CHECK_MAIN.ORG_ID from T_BS_CHECK_MAIN
|
|
inner join T_BS_CHECK_PROJECT_PROJECT_CATEGORY on T_BS_CHECK_PROJECT_PROJECT_CATEGORY.ID=T_BS_CHECK_MAIN.CHECK_PROJECT_PROJECT_CATEGORY_ID
|
|
inner join T_BS_CHECK_PROJECT_CATEGORY on T_BS_CHECK_PROJECT_PROJECT_CATEGORY.CHECK_PROJECT_CATEGORY_ID=.T_BS_CHECK_PROJECT_CATEGORY.id
|
|
where T_BS_CHECK_MAIN.CHECK_PROJECT_ID is not null and T_BS_CHECK_MAIN.CHECK_PROJECT_PROJECT_CATEGORY_ID is not null
|
|
) tb
|
|
|
|
/******
|
|
数据结构修改 安全检查 数据初始化
|
|
wyw
|
|
2022-08-30
|
|
*******/
|
|
update T_BS_SAFE_CHECK_DETAIL set RISK_AREA_ID=tb.RISK_AREA_ID,CHECK_PROJECT_ID=tb.CHECK_PROJECT_ID,CHECK_PROJECT_PROJECT_CATEGORY_ID=tb.CHECK_PROJECT_PROJECT_CATEGORY_ID,CHECK_QUESTION_ID=tb.CHECK_QUESTION_ID,CHECKCONTENT=tb.CHECKCONTENT,CHECKPROOF=tb.CHECKPROOF,SERIOUS_RISK=tb.SERIOUS_RISK
|
|
from (
|
|
select T_BS_SAFE_CHECK_DETAIL.ID, T_BS_CHECK_MAIN.RISK_AREA_ID RISK_AREA_ID,T_BS_CHECK_MAIN.CHECK_PROJECT_ID,T_BS_CHECK_MAIN.CHECK_PROJECT_PROJECT_CATEGORY_ID,T_BS_CHECK_MAIN.CHECK_QUESTION_ID,T_BS_CHECK_MAIN.CHECKCONTENT,T_BS_CHECK_MAIN.CHECKPROOF,T_BS_CHECK_MAIN.SERIOUS_RISK from T_BS_SAFE_CHECK_DETAIL
|
|
inner join T_BS_CHECK_MAIN on T_BS_CHECK_MAIN.ID=T_BS_SAFE_CHECK_DETAIL.CHECK_MAIN_ID
|
|
where T_BS_SAFE_CHECK_DETAIL.CHECKCONTENT is null
|
|
) tb
|
|
where T_BS_SAFE_CHECK_DETAIL.ID=tb.ID
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/****** 安全检查 库结构修改 2022/12/08 wyw **********/
|
|
|
|
--数据初始化
|
|
--T_BS_CHECK_MAIN 加字段 CHECK_CONTENTS_ID
|
|
--T_BS_CHECK_QUESTION CHECK_CONTENTS_ID QUESTION_LEVEL
|
|
--T_BS_RISK_SUBMIT_CONTENT CHECK_CONTENTS_ID
|
|
--T_BS_SAFE_CHECK_DETAIL CHECK_CONTENTS_ID
|
|
|
|
|
|
|
|
|
|
--delete T_BS_CHECK_CONTENTS
|
|
--update T_BS_CHECK_MAIN set CHECK_CONTENTS_ID = null
|
|
--update T_BS_RISK_SUBMIT_CONTENT set CHECK_CONTENTS_ID = null
|
|
--update T_BS_SAFE_CHECK_DETAIL set CHECK_CONTENTS_ID = null
|
|
--update T_BS_CHECK_QUESTION set CHECK_CONTENTS_ID = null
|
|
|
|
|
|
|
|
|
|
--改进方法 正式环境操作
|
|
--1、检查问题 设置 问题等级
|
|
--2、检查问题 与整改建议与措施一一对应
|
|
|
|
|
|
|
|
--检查问题 与整改建议与措施一一对应
|
|
select * from
|
|
(
|
|
select (select count(1) from T_BS_CHECK_QUESTION q where q.DESCREPTION=T_BS_CHECK_QUESTION.DESCREPTION ) c,--T_BS_CHECK_MAIN.QUESTION_LEVEL ,
|
|
T_BS_CHECK_QUESTION. * from T_BS_CHECK_QUESTION
|
|
) tb where c>1 order by c desc,DESCREPTION
|
|
|
|
-- 处理SQL
|
|
update T_BS_CHECK_MAIN set CHECK_QUESTION_ID='E58FB0E9-746C-4BE5-93C4-BE59CA2A0AB2' where CHECK_QUESTION_ID in ('')
|
|
update T_BS_SAFE_CHECK_DETAIL set CHECK_QUESTION_ID='E58FB0E9-746C-4BE5-93C4-BE59CA2A0AB2' where CHECK_QUESTION_ID in ('')
|
|
update T_BS_RISK_SUBMIT_CONTENT set CHECK_QUESTION_ID='E58FB0E9-746C-4BE5-93C4-BE59CA2A0AB2' where CHECK_QUESTION_ID in ('')
|
|
update T_BS_SAFE_CHECK_DETAIL_QUESTION set SAFE_CHECK_QUESTION_ID='E58FB0E9-746C-4BE5-93C4-BE59CA2A0AB2' where SAFE_CHECK_QUESTION_ID in ('')
|
|
delete T_BS_CHECK_QUESTION where ID in ('')
|
|
|
|
--一个检查问题 对应一个检查问题等级
|
|
|
|
-- T_BS_CHECK_MAIN CHECK_QUESTION_ID is null 删除
|
|
select * from T_BS_CHECK_MAIN where CHECK_QUESTION_ID is null
|
|
--delete T_BS_CHECK_MAIN where CHECK_QUESTION_ID is null
|
|
select * from T_BS_SAFE_CHECK_DETAIL where CHECK_MAIN_ID in (select ID from T_BS_CHECK_MAIN where CHECK_QUESTION_ID is null)
|
|
delete T_BS_SAFE_CHECK_DETAIL_USER where SAFE_CHECK_DETAIL_ID in ('0E442725-D402-DE03-6126-69E684AB72B4','9C3BC389-8BB9-D9D3-8CEA-FE566F54B6C7')
|
|
delete T_BS_SAFE_CHECK_DETAIL_QUESTION where SAFE_CHECK_DETAIL_ID in ('0E442725-D402-DE03-6126-69E684AB72B4','9C3BC389-8BB9-D9D3-8CEA-FE566F54B6C7')
|
|
delete T_BS_SAFE_CHECK_DETAIL where ID in ('0E442725-D402-DE03-6126-69E684AB72B4','9C3BC389-8BB9-D9D3-8CEA-FE566F54B6C7')
|
|
delete T_BS_CHECK_MAIN where CHECK_QUESTION_ID is null
|
|
|
|
|
|
|
|
select * from (
|
|
select CHECK_QUESTION_ID,QUESTION_LEVEL,( select count(1) from T_BS_CHECK_MAIN m where m.CHECK_QUESTION_ID=T_BS_CHECK_MAIN.CHECK_QUESTION_ID and m.QUESTION_LEVEL!=T_BS_CHECK_MAIN.QUESTION_LEVEL ) c from T_BS_CHECK_MAIN group by CHECK_QUESTION_ID,QUESTION_LEVEL
|
|
) tb where tb.c>0 order by CHECK_QUESTION_ID
|
|
|
|
|
|
update T_BS_CHECK_MAIN set QUESTION_LEVEL='' where CHECK_QUESTION_ID in ('')
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/******
|
|
检查内容 数据 插入
|
|
wyw
|
|
2022-08-30
|
|
ORG_ID 单独替换
|
|
*******/
|
|
insert into [dbo].[T_BS_CHECK_CONTENTS]
|
|
select NEWID() ID , CHECKCONTENT, 0 [IS_DELETED],'B043B28B-BBC3-C452-6052-4FBA1457ABFA' [ORG_ID],0 [ENTITY_ORG_TPYE],null [FORM_ID], 0 [FLOW_STATUS], 0 [FLOW_SEND_STATUS],null [FLOW_ID],'2022-12-06' [CREATE_TIME],'2022-12-06' [MODIFY_TIME],'8F26C255-FDFD-47FF-B83E-6309815958DD' [CREATER_ID],'8F26C255-FDFD-47FF-B83E-6309815958DD' [MODIFIER_ID],[CHECKOBJECT] from T_BS_CHECK_MAIN group by CHECKCONTENT ,[CHECKOBJECT]--1846
|
|
|
|
|
|
|
|
/******
|
|
检查问题 数据 检查内容绑定
|
|
wyw
|
|
2022-08-30
|
|
*******/
|
|
update T_BS_CHECK_QUESTION set CHECK_CONTENTS_ID=tb.CHECKCONTENTID, QUESTION_LEVEL=tb.QUESTION_LEVEL from (
|
|
select T_BS_CHECK_MAIN.CHECKCONTENT,T_BS_CHECK_CONTENTS.ID as CHECKCONTENTID, T_BS_CHECK_QUESTION.ID as QUESTIONID,T_BS_CHECK_MAIN.QUESTION_LEVEL from T_BS_CHECK_QUESTION
|
|
inner join T_BS_CHECK_MAIN on T_BS_CHECK_QUESTION.ID=T_BS_CHECK_MAIN.CHECK_QUESTION_ID and T_BS_CHECK_MAIN.QUESTION_LEVEL=T_BS_CHECK_MAIN.QUESTION_LEVEL
|
|
inner join T_BS_CHECK_CONTENTS on T_BS_CHECK_CONTENTS.CHECKCONTENT=T_BS_CHECK_MAIN.CHECKCONTENT and T_BS_CHECK_MAIN.CHECKOBJECT=T_BS_CHECK_MAIN.CHECKOBJECT
|
|
)tb
|
|
where tb.QUESTIONID=T_BS_CHECK_QUESTION.ID
|
|
|
|
|
|
|
|
|
|
|
|
-- T_BS_CHECK_MAIN 的 CHECK_CONTENTS_ID 赋值
|
|
update T_BS_CHECK_MAIN set CHECK_CONTENTS_ID =tb.cID from (
|
|
select ( select ID from T_BS_CHECK_CONTENTS where T_BS_CHECK_CONTENTS.CHECKCONTENT= T_BS_CHECK_MAIN.CHECKCONTENT and T_BS_CHECK_CONTENTS.CHECKOBJECT= T_BS_CHECK_MAIN.CHECKOBJECT) cID, * from T_BS_CHECK_MAIN
|
|
)tb where T_BS_CHECK_MAIN.ID=tb.ID
|
|
|
|
|
|
|
|
/******
|
|
安全检查明细 数据 CHECK_CONTENTS_ID 数据初始化
|
|
wyw
|
|
2022-08-30
|
|
*******/
|
|
update T_BS_SAFE_CHECK_DETAIL set CHECK_CONTENTS_ID=tb.CHECK_CONTENTS_ID from(
|
|
select
|
|
T_BS_CHECK_MAIN.CHECK_CONTENTS_ID as CHECK_CONTENTS_ID, T_BS_SAFE_CHECK_DETAIL.ID as detailID from T_BS_SAFE_CHECK_DETAIL
|
|
inner join T_BS_CHECK_MAIN on CHECK_MAIN_ID=T_BS_CHECK_MAIN.ID
|
|
where T_BS_SAFE_CHECK_DETAIL.CHECK_MAIN_ID is not null
|
|
) tb where tb.detailID=T_BS_SAFE_CHECK_DETAIL.ID
|
|
|
|
|
|
|
|
|
|
/******
|
|
隐患上报明细 数据 CHECK_CONTENTS_ID 数据初始化
|
|
wyw
|
|
2022-08-30
|
|
*******/
|
|
update T_BS_RISK_SUBMIT_CONTENT set CHECK_CONTENTS_ID=tb.CHECK_CONTENTS_ID from(
|
|
select
|
|
T_BS_CHECK_MAIN.CHECK_CONTENTS_ID as CHECK_CONTENTS_ID, T_BS_RISK_SUBMIT_CONTENT.ID as detailID from T_BS_RISK_SUBMIT_CONTENT
|
|
inner join T_BS_CHECK_MAIN on CHECK_MAIN_ID=T_BS_CHECK_MAIN.ID
|
|
where T_BS_RISK_SUBMIT_CONTENT.CHECK_MAIN_ID is not null
|
|
) tb where tb.detailID=T_BS_RISK_SUBMIT_CONTENT.ID
|
|
|
|
|
|
|
|
|
|
/****** 安全检查 库结构修改 2022/12/08 wyw **********/
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|