89 lines
3.0 KiB
Transact-SQL
89 lines
3.0 KiB
Transact-SQL
|
|
update Check_CheckSpecialDetail
|
|
set HiddenHazardType=1
|
|
where HiddenHazardType='0' or HiddenHazardType='低' or HiddenHazardType='一般' or HiddenHazardType='' or HiddenHazardType is null
|
|
|
|
go
|
|
update Check_CheckSpecialDetail
|
|
set HiddenHazardType=2
|
|
where HiddenHazardType='较大' or HiddenHazardType='较高'
|
|
|
|
go
|
|
update Check_CheckSpecialDetail
|
|
set HiddenHazardType=3
|
|
where HiddenHazardType='高' or HiddenHazardType='重大'
|
|
|
|
go
|
|
|
|
|
|
|
|
ALTER VIEW [dbo].[View_CheckSpecialDetail]
|
|
AS
|
|
/*专项检查明细列表视图*/
|
|
SELECT detail.CheckSpecialDetailId,
|
|
detail.CheckSpecialId,
|
|
detail.CheckItem,
|
|
checkItemSet.RegisterTypesName as CheckItemName,
|
|
--detail.CheckItemType,
|
|
detail.Unqualified,
|
|
detail.SortIndex,
|
|
detail.UnitId,
|
|
detail.HandleStep,
|
|
detail.CompleteStatus,
|
|
(case when detail.CompleteStatus =1 then '已整改' else '待整改' end) as CompleteStatusName,
|
|
detail.LimitedDate,
|
|
detail.CompletedDate,
|
|
--detail.Suggestions,
|
|
detail.CheckContent,
|
|
detail.CheckArea,
|
|
'' as HandleWay,
|
|
detail.Suggestions,
|
|
detail.HandleStep as RectifyOpinion,
|
|
'd72a27c9-4ba9-41c5-ab0b-c010409f20f2' as ProposeUnitId,
|
|
workArea.UnitWorkName + (CASE WHEN workArea.ProjectType ='1' THEN '(建筑)'
|
|
WHEN workArea.ProjectType ='2' THEN '(安装)' ELSE '' END)AS CheckAreaName,
|
|
detail.DataId,
|
|
detail.DataType,
|
|
unit.UnitName,
|
|
dbo.GetConstTextByIds(detail.HandleStep) as HandleStepStr,
|
|
--const.ConstText as HandleStepStr,
|
|
(CASE WHEN detail.DataType like '%1%'
|
|
THEN '下发整改单:'+(SELECT RectifyNoticesCode FROM Check_RectifyNotices WHERE detail.DataId like '%'+RectifyNoticesId+'%')
|
|
else '' end)+
|
|
(CASE WHEN detail.DataType like '%2%'
|
|
THEN '下发处罚单:'+(SELECT PunishNoticeCode FROM Check_PunishNotice WHERE detail.DataId like '%'+PunishNoticeId+'%')
|
|
else '' end)+
|
|
(CASE WHEN detail.DataType = '3'
|
|
THEN '下发暂停令:'+(SELECT PauseNoticeCode FROM Check_PauseNotice WHERE detail.DataId like '%'+ PauseNoticeId+'%')
|
|
else '' end) AS HandleStepLink
|
|
,
|
|
--(case when detail.riskLevel ='0' then '低'
|
|
-- when detail.riskLevel = '1' then '一般'
|
|
-- when detail.riskLevel = '2' then '较高'
|
|
-- when detail.riskLevel = '3' then '高'
|
|
-- else detail.riskLevel end) as riskLevel,
|
|
(case when detail.HiddenHazardType = '1' then '一般'
|
|
when detail.HiddenHazardType = '2' then '较高'
|
|
when detail.HiddenHazardType = '3' then '高'
|
|
else '一般' end) as riskLevel,
|
|
--sysuser.PersonName as HSEManage
|
|
'' as HSEManage
|
|
,(CASE WHEN detail.HiddenHazardType='3' THEN '重大'
|
|
WHEN detail.HiddenHazardType='2' THEN '较大'
|
|
WHEN detail.HiddenHazardType='1' THEN '一般' ELSE '' END) AS HiddenHazardTypeName
|
|
FROM dbo.Check_CheckSpecialDetail AS detail
|
|
LEFT JOIN dbo. HSSE_Hazard_HazardRegisterTypes AS checkItemSet ON checkItemSet.RegisterTypesId=detail.CheckItem
|
|
LEFT JOIN dbo.Base_Unit AS unit ON unit.UnitId=detail.UnitId
|
|
LEFT JOIN dbo.WBS_UnitWork AS workArea ON detail.CheckArea=workArea.UnitWorkId
|
|
--LEFT JOIN dbo.Person_Persons AS sysuser ON sysuser.PersonId=detail.HSEManage
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
|