xinjiang/DataBase/版本日志/SGGLDB_V2025-02-25-xiaj.sql

--1<EFBFBD><EFBFBD><EFBFBD><EFBFBD>˾<EFBFBD><EFBFBD>ѵ<EFBFBD>̲IJ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ø<EFBFBD>λ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>̶<EFBFBD>
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Training_CompanyTrainingItem' AND COLUMN_NAME = 'WorkPostNames')
BEGIN
	ALTER TABLE Training_CompanyTrainingItem ADD WorkPostNames nvarchar(max);
	ALTER TABLE Training_CompanyTrainingItem ALTER COLUMN WorkPostIds nvarchar(max);
	ALTER TABLE Training_CompanyTrainingItem ADD Outcome INT DEFAULT(0) ;
END
GO
ALTER TABLE Training_CompanyTrainingItem
DROP COLUMN Effect;

--2<EFBFBD><EFBFBD>HSE<EFBFBD>ճ<EFBFBD>Ѳ<EFBFBD>첹<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>޽̲<EFBFBD>Id
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'HSSE_Hazard_HazardRegister' AND COLUMN_NAME = 'CompanyTrainingItemId')
BEGIN
	ALTER TABLE HSSE_Hazard_HazardRegister ADD CompanyTrainingItemId nvarchar(50);
END
GO
--3<EFBFBD><EFBFBD>HSE<EFBFBD>쵼<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>鲹<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ˡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>޽̲<EFBFBD>Id
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Check_ProjectLeaderCheck' AND COLUMN_NAME = 'CompanyTrainingItemId')
BEGIN
	ALTER TABLE Check_ProjectLeaderCheck ADD ResponsibleMan nvarchar(50);
	ALTER TABLE Check_ProjectLeaderCheck ADD CompanyTrainingItemId nvarchar(50);
END
GO

--5<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѵ<EFBFBD>ƻ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>𡢼<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>¼Id
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Training_Plan' AND COLUMN_NAME = 'IsRetakeCourse')
BEGIN
	--<EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѵ
	ALTER TABLE Training_Plan ADD IsRetakeCourse INT DEFAULT(0) ;
	--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
	ALTER TABLE Training_Plan ADD CheckType nvarchar(50) ;
	--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>¼Id
	ALTER TABLE Training_Plan ADD CheckId nvarchar(50) ;
	--<EFBFBD><EFBFBD>ѵ<EFBFBD>̲<EFBFBD>Id
	ALTER TABLE Training_Plan ADD CompanyTrainingItemId nvarchar(50) ;
END
GO


/****** Object:  View [dbo].[View_Hazard_HazardRegister]    Script Date: 2025-2-25 18:12:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[View_Hazard_HazardRegister] AS SELECT Registration.HazardRegisterId, 
	Registration.HazardCode, 
	Registration.RegisterDate, 
	Registration.RegisterDef, 
	Registration.Rectification, 
	Registration.Place, 
	Registration.ResponsibleUnit, 
	Registration.Observer, 
	Registration.Risk_Level,
	Registration.RectifiedDate, 
	Registration.ProjectId, 
	Project.UnitId AS PUnitId,
	ProjectUnit.UnitName AS PUnitName,
	Registration.states,
	Registration.IsEffective,
	Registration.ResponsibleMan,
	Registration.CheckManId,
	Registration.CheckTime,
	Registration.RectificationPeriod,
	Registration.ImageUrl,
	Registration.RectificationImageUrl,
	Registration.RectificationTime,
	Registration.ConfirmMan,
	Registration.ConfirmDate,
	Registration.HandleIdea, 
	Registration.CutPayment, 
	Registration.ProblemTypes, 
	Registration.CheckSpecialId,
	Registration.CheckItemDetailId,
	Registration.SupCheckItemSetId,
	Registration.CheckItemSetId,
	Registration.SafeSupervisionId,
	Registration.SafeSupervisionIsOK,
	Project.ProjectName,
	Registration.CheckCycle,
	ISNULL(WorkArea.UnitWorkName,'') AS WorkAreaName,
	Unit.UnitName  as ResponsibilityUnitName,
	User1.UserName AS ResponsibilityManName,
	User1.UserId AS ResponsibilityManId,
	ISNULL(User1.Telephone,'') AS ResponsibilityManTel,
	User2.UserName AS CheckManName,
	ISNULL(User2.Telephone,'') AS  CheckManTel,
	User2.UnitId AS SendUnitId,
	User3.UserName AS ConfirmManName,
	ISNULL(User3.Telephone,'') AS  ConfirmManTel,
	User4.UserName AS ResponsibilityManName2,
	ISNULL(User4.Telephone,'') AS  ResponsibilityMan2Tel,
	(CASE WHEN Registration.states='1' and (Registration.SafeSupervisionIsOK is null OR Registration.SafeSupervisionIsOK=0) THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' 
	WHEN Registration.states='1' and Registration.SafeSupervisionIsOK=1 THEN '<EFBFBD>ϸ<EFBFBD>' 
	WHEN Registration.states='2' THEN '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>-<2D><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' 
	WHEN Registration.states='3' THEN '<EFBFBD>ѱջ<EFBFBD>' 
	ELSE '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' END ) AS StatesStr,
	RegisterTypes.RegisterTypesId,
	RegisterTypes.RegisterTypesName,
	Registration.DIC_ID,
	Registration.CCManIds,
	CCManNames = STUFF(( SELECT ',' + UserName FROM Sys_User
				    where PATINDEX('%,' + RTRIM(Sys_User.UserId) + ',%',',' + Registration.CCManIds + ',')>0
					ORDER BY PATINDEX('%,' + RTRIM(Registration.CCManIds) + ',%',',' + Registration.CCManIds + ',')
					FOR XML PATH('')), 1, 1,''),
	Registration.Requirements,
	Registration.CompanyTrainingItemId
FROM dbo.HSSE_Hazard_HazardRegister AS Registration
LEFT JOIN dbo.Base_Project AS Project ON Project.ProjectId = Registration.ProjectId
LEFT JOIN dbo.Base_Unit AS ProjectUnit ON Project.UnitId = ProjectUnit.UnitId
LEFT JOIN dbo.WBS_UnitWork AS WorkArea ON WorkArea.UnitWorkId = Registration.Place 
LEFT JOIN dbo.HSSE_Hazard_HazardRegisterTypes AS RegisterTypes ON RegisterTypes.RegisterTypesId = Registration.RegisterTypesId 
LEFT JOIN dbo.Base_Unit AS Unit ON Unit.UnitId = Registration.ResponsibleUnit
LEFT JOIN dbo.Sys_User AS User1 ON User1.UserId = Registration.ResponsibleMan   
LEFT JOIN dbo.Sys_User AS User2 ON User2.UserId = Registration.CheckManId
LEFT JOIN dbo.Sys_User AS User3 ON User3.UserId = Registration.ConfirmMan
LEFT JOIN dbo.Sys_User AS User4 ON User4.UserId = Registration.ResponsibleMan2


GO