270 lines
14 KiB
Transact-SQL
270 lines
14 KiB
Transact-SQL
drop view dbo.View_ProjectPictureReport;
|
||
go
|
||
drop view dbo.ProjectSitePersonNum ;
|
||
go
|
||
drop view dbo.View_DayReport;
|
||
go
|
||
drop view dbo.View_DayPersonReport;
|
||
go
|
||
drop view dbo.View_HSSEAccidentReport;
|
||
go
|
||
drop view dbo.View_HSSECheckReport;
|
||
go
|
||
drop view dbo.View_MaterPhyQuantity;
|
||
go
|
||
--ʵÎ﹤³ÌÁ¿
|
||
create view dbo.View_MaterPhyQuantity as
|
||
WITH CTE AS (SELECT project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
sysConst.ConstText AS UnitTypeName,
|
||
CONVERT(VARCHAR(7), ReportDate, 120) AS YearMonth,
|
||
SUM(x.Total) AS Totals,
|
||
SUM(x.PlanCompletedNum) AS MonthTotalPlanCompletedNum,
|
||
SUM(x.ActCompletedNum) AS MonthTotalActCompletedNum,
|
||
ROW_NUMBER() OVER (PARTITION BY project.ProjectId ORDER BY CONVERT(VARCHAR(7), ReportDate, 120) ASC) AS RN
|
||
|
||
FROM HSSE_MaterPhyQuantity x
|
||
|
||
LEFT JOIN Sys_Const AS sysConst
|
||
ON sysConst.GroupId = 'MaterPhyQuantityType' AND
|
||
x.MaterPhyQuantityType = sysConst.ConstValue
|
||
|
||
LEFT JOIN Base_Project project ON x.ProjectId = project.ProjectId
|
||
GROUP BY project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
sysConst.ConstText,
|
||
CONVERT(VARCHAR(7), ReportDate, 120))
|
||
SELECT CTE.ProjectId,
|
||
CTE.ProjectName,
|
||
CTE.ProjectCode,
|
||
CTE.UnitTypeName,
|
||
CTE.YearMonth,
|
||
CTE.Totals,
|
||
CTE.MonthTotalPlanCompletedNum,
|
||
CTE.MonthTotalActCompletedNum,
|
||
SUM(CTE.MonthTotalPlanCompletedNum)
|
||
OVER (PARTITION BY CTE.ProjectId,CTE.UnitTypeName ORDER BY CTE.YearMonth ASC) AS AccumulatedPlanCompletedNum,
|
||
SUM(CTE.MonthTotalActCompletedNum)
|
||
OVER (PARTITION BY CTE.ProjectId,CTE.UnitTypeName ORDER BY CTE.YearMonth ASC) AS AccumulatedActCompletedNum
|
||
FROM CTE
|
||
WHERE CTE.RN <= (SELECT COUNT(*) FROM CTE)
|
||
;
|
||
go
|
||
--ÏîĿͼƬ
|
||
|
||
create view dbo.View_ProjectPictureReport as
|
||
select ProjectCode, ProjectName, Title, type.Name, AttachFile.AttachUrl, UploadDate
|
||
from InformationProject_Picture InFo
|
||
left join Base_Project project on InFo.ProjectId = project.ProjectId
|
||
left join Base_PictureType type on InFo.PictureType = type.PictureTypeId
|
||
left join AttachFile on InFo. PictureId = AttachFile.ToKeyId
|
||
where MenuType = 'Menu_HSSE'
|
||
and type.Name in ('ÏîÄ¿ÏÖ³¡Í¼', 'ÏîĿЧ¹ûͼ')
|
||
|
||
go
|
||
--ÏÖ³¡ÈËÊýÊÓͼ
|
||
create view dbo.ProjectSitePersonNum as
|
||
SELECT
|
||
pro.ProjectName,
|
||
pro.ProjectCode,
|
||
COUNT(CASE WHEN Post.PostType IN ('1', '4') AND Person.IsForeign = 0 AND Punit.UnitType='1' and Unit.IsBuild = 0 THEN 1 END) AS GenManagerCount,--×Ü°üÈËÊý
|
||
COUNT(CASE WHEN Post.PostType IN ('1', '4') AND Person.IsForeign = 0 AND Punit.UnitType='2' and Unit.IsBuild = 0 THEN 1 END) AS ManagerSystemOutChinaCount,--·Ö°ü¹ÜÀíϵͳÍâÖйú¼®ÈËÊý
|
||
COUNT(CASE WHEN Post.PostType IN ('1', '4') AND Person.IsForeign = 1 AND Punit.UnitType='2' and Unit.IsBuild = 0 THEN 1 END) AS ManagerSystemOutForeignCount,--·Ö°ü¹ÜÀíϵͳÍâÍâ¼®ÈËÊý
|
||
COUNT(CASE WHEN Post.PostType IN ('1', '4') AND Person.IsForeign = 0 AND Punit.UnitType='2' and Unit.IsBuild = 1 THEN 1 END) AS ManagerSystemInChinaCount,--·Ö°ü¹ÜÀíϵͳÄÚÖйú¼®ÈËÊý
|
||
COUNT(CASE WHEN Post.PostType IN ('1', '4') AND Person.IsForeign = 1 AND Punit.UnitType='2' and Unit.IsBuild = 1 THEN 1 END) AS ManagerSystemInForeignCount,--·Ö°ü¹ÜÀíϵͳÄÚÍâ¼®ÈËÊý
|
||
COUNT(CASE WHEN Post.PostType IN ('2', '3') AND Person.IsForeign = 0 AND Punit.UnitType='2' and Unit.IsBuild = 0 THEN 1 END) AS OperManSystemOutChinaCount,--·Ö°üÖ±½ÓϵͳÍâÖйú¼®ÈËÊý
|
||
COUNT(CASE WHEN Post.PostType IN ('2', '3') AND Person.IsForeign = 1 AND Punit.UnitType='2' and Unit.IsBuild = 0 THEN 1 END) AS OperManSystemOutForeignCount,--·Ö°üÖ±½ÓϵͳÍâÍâ¼®ÈËÊý
|
||
COUNT(CASE WHEN Post.PostType IN ('2', '3') AND Person.IsForeign = 0 AND Punit.UnitType='2' and Unit.IsBuild = 1 THEN 1 END) AS OperManSystemInChinaCount,--·Ö°üÖ±½ÓϵͳÄÚÖйú¼®ÈËÊý
|
||
COUNT(CASE WHEN Post.PostType IN ('2', '3') AND Person.IsForeign = 1 AND Punit.UnitType='2' and Unit.IsBuild = 1 THEN 1 END) AS OperManSystemInForeignCount,--·Ö°üÖ±½ÓϵͳÄÚÍâ¼®ÈËÊý
|
||
(CASE
|
||
WHEN (select count(*) from RealName_SynchroSet where RealName_SynchroSet.UnitId='b4f3d912-ca6d-440c-a8d7-bc6a5d5a1f84' and proCode=pro.ContractNo)=1 THEN 'ÊÇ'
|
||
ELSE '·ñ'
|
||
END) AS IsRealName
|
||
FROM
|
||
Base_Project pro
|
||
INNER JOIN SitePerson_Person AS Person ON pro.ProjectId = Person.ProjectId
|
||
INNER JOIN Base_Unit AS Unit ON Unit.UnitId = Person.UnitId
|
||
INNER JOIN Project_ProjectUnit as Punit on Unit.UnitId = Punit.UnitId
|
||
INNER JOIN Base_WorkPost AS Post ON Post.WorkPostId = Person.WorkPostId
|
||
WHERE
|
||
Post.PostType IN ('1', '2', '3', '4')
|
||
GROUP BY
|
||
pro.ProjectName, pro.ProjectCode,pro.ContractNo
|
||
go
|
||
--È˹¤Ê±
|
||
|
||
create view dbo.View_DayReport as
|
||
WITH CTE AS (SELECT project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
sysConst.ConstText AS UnitTypeName,
|
||
CONVERT(VARCHAR(7), CompileDate, 120) AS YearMonth,
|
||
SUM(x.PersonWorkTime) AS MonthTotalPersonWorkTime,
|
||
ROW_NUMBER() OVER (PARTITION BY project.ProjectId ORDER BY CONVERT(VARCHAR(7), CompileDate, 120) ASC) AS RN
|
||
|
||
FROM SitePerson_DayReportDetail x
|
||
|
||
LEFT JOIN SitePerson_DayReport a ON x.DayReportId = a.DayReportId
|
||
|
||
LEFT JOIN Base_Unit b ON x.UnitId = b.UnitId
|
||
|
||
LEFT JOIN Project_ProjectUnit ProUnit ON b.UnitId = ProUnit.UnitId
|
||
|
||
LEFT JOIN Sys_Const AS sysConst
|
||
ON sysConst.GroupId = 'ProjectUnitType' AND ProUnit.UnitType = sysConst.ConstValue
|
||
|
||
LEFT JOIN Base_Project project ON a.ProjectId = project.ProjectId
|
||
|
||
WHERE ProUnit.ProjectId = a.ProjectId
|
||
GROUP BY project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
sysConst.ConstText,
|
||
CONVERT(VARCHAR(7), CompileDate, 120))
|
||
SELECT CTE.ProjectId,
|
||
CTE.ProjectName,
|
||
CTE.ProjectCode,
|
||
CTE.UnitTypeName,
|
||
CTE.YearMonth,
|
||
CTE.MonthTotalPersonWorkTime,
|
||
SUM(CTE.MonthTotalPersonWorkTime)
|
||
OVER (PARTITION BY CTE.ProjectId,CTE.UnitTypeName ORDER BY CTE.YearMonth ASC) AS AccumulatedPersonWorkTime
|
||
|
||
FROM CTE
|
||
WHERE CTE.RN <= (SELECT COUNT(*) FROM CTE)
|
||
;
|
||
go
|
||
--È˹¤ÈÕ
|
||
|
||
create view dbo.View_DayPersonReport as
|
||
WITH CTE AS (SELECT project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
sysConst.ConstText AS UnitTypeName,
|
||
CONVERT(VARCHAR(7), CompileDate, 120) AS YearMonth,
|
||
SUM(x.RealPersonNum) AS MonthTotalPersonNum,
|
||
ROW_NUMBER() OVER (PARTITION BY project.ProjectId ORDER BY CONVERT(VARCHAR(7), CompileDate, 120) ASC) AS RN
|
||
|
||
FROM SitePerson_DayReportDetail x
|
||
|
||
LEFT JOIN SitePerson_DayReport a ON x.DayReportId = a.DayReportId
|
||
|
||
LEFT JOIN Base_Unit b ON x.UnitId = b.UnitId
|
||
|
||
LEFT JOIN Project_ProjectUnit ProUnit ON b.UnitId = ProUnit.UnitId
|
||
|
||
LEFT JOIN Sys_Const AS sysConst
|
||
ON sysConst.GroupId = 'ProjectUnitType' AND ProUnit.UnitType = sysConst.ConstValue
|
||
|
||
LEFT JOIN Base_Project project ON a.ProjectId = project.ProjectId
|
||
|
||
WHERE ProUnit.ProjectId = a.ProjectId
|
||
GROUP BY project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
sysConst.ConstText,
|
||
CONVERT(VARCHAR(7), CompileDate, 120))
|
||
SELECT CTE.ProjectId,
|
||
CTE.ProjectName,
|
||
CTE.ProjectCode,
|
||
CTE.UnitTypeName,
|
||
CTE.YearMonth,
|
||
CTE.MonthTotalPersonNum,
|
||
SUM(CTE.MonthTotalPersonNum)
|
||
OVER (PARTITION BY CTE.ProjectId,CTE.UnitTypeName ORDER BY CTE.YearMonth ASC) AS AccumulatedPersonNum
|
||
|
||
FROM CTE
|
||
WHERE CTE.RN <= (SELECT COUNT(*) FROM CTE)
|
||
;
|
||
go
|
||
--ÏÖ³¡HSE״̬£¨Ê¹ʱ¨¸æͳ¼Æ£©
|
||
|
||
create view dbo.View_HSSEAccidentReport as
|
||
with CTE AS (SELECT project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
AccidentType.ConstText AS AccidentTypeName,
|
||
CONVERT(VARCHAR(7), ReportDate, 120) AS YearMonth,
|
||
count(a.AccidentReportId) AS MonthTotal,
|
||
ROW_NUMBER() OVER (PARTITION BY project.ProjectId ORDER BY CONVERT(VARCHAR(7), ReportDate, 120) ASC) AS RN
|
||
FROM Accident_AccidentReport a
|
||
LEFT JOIN Base_Project project ON a.ProjectId = project.ProjectId
|
||
LEFT JOIN Sys_Const AS AccidentType ON AccidentType.ConstValue = a.AccidentTypeId and
|
||
AccidentType.GroupId = 'AccidentReportRegistration'
|
||
GROUP BY project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
CONVERT(VARCHAR(7), ReportDate, 120), AccidentType.ConstText)
|
||
SELECT CTE.ProjectId,
|
||
CTE.ProjectName,
|
||
CTE.ProjectCode,
|
||
CTE.YearMonth,
|
||
cte.AccidentTypeName,
|
||
CTE.MonthTotal,
|
||
SUM(CTE.MonthTotal)
|
||
OVER (PARTITION BY CTE.ProjectId,CTE.AccidentTypeName ORDER BY CTE.YearMonth ASC) AS Accumulated
|
||
FROM CTE
|
||
WHERE CTE.RN <= (SELECT COUNT(*) FROM CTE)
|
||
go
|
||
--ÏÖ³¡HSE¼ì²éÇé¿ö
|
||
|
||
create view dbo.View_HSSECheckReport as
|
||
with Daily as (
|
||
SELECT project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
'°²È«ÈÕ³£Ñ²²é' AS CheckTypeName,
|
||
CONVERT(VARCHAR(7), CheckTime, 120) AS YearMonth,
|
||
count(Hazardreg.HazardRegisterId) AS MonthTotal,
|
||
ROW_NUMBER() OVER (PARTITION BY project.ProjectId ORDER BY CONVERT(VARCHAR(7), CheckTime, 120) ASC) AS RN
|
||
FROM HSSE_Hazard_HazardRegister Hazardreg
|
||
LEFT JOIN Base_Project project ON Hazardreg.ProjectId = project.ProjectId
|
||
GROUP BY project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
CONVERT(VARCHAR(7), CheckTime, 120)),
|
||
|
||
CTE AS (SELECT project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
(CASE
|
||
WHEN a.CheckType = '1' THEN 'ÿÖÜ°²È«¼ì²é'
|
||
WHEN a.CheckType = '2' THEN 'ÿÔ°²È«´ó¼ì²é'
|
||
WHEN a.CheckType = '3' THEN '½ÚÇ°½Úºó°²È«¼ì²é'
|
||
WHEN a.CheckType = '0' THEN 'רÏȫ¼ì²é'
|
||
ELSE 'רÏî¼ì²é' END) AS CheckTypeName,
|
||
CONVERT(VARCHAR(7), CheckTime, 120) AS YearMonth,
|
||
count(a.CheckSpecialId) AS MonthTotal,
|
||
ROW_NUMBER() OVER (PARTITION BY project.ProjectId ORDER BY CONVERT(VARCHAR(7), CheckTime, 120) ASC) AS RN
|
||
FROM Check_CheckSpecial a
|
||
LEFT JOIN Base_Project project ON a.ProjectId = project.ProjectId
|
||
where a.CheckType in ('1','2','0')
|
||
GROUP BY project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
CheckType,
|
||
CONVERT(VARCHAR(7), CheckTime, 120))
|
||
SELECT CTE.ProjectId,
|
||
CTE.ProjectName,
|
||
CTE.ProjectCode,
|
||
CTE.YearMonth,
|
||
cte.CheckTypeName,
|
||
CTE.MonthTotal,
|
||
SUM(CTE.MonthTotal)
|
||
OVER (PARTITION BY CTE.ProjectId,CTE.CheckTypeName ORDER BY CTE.YearMonth ASC) AS Accumulated
|
||
FROM CTE
|
||
WHERE CTE.RN <= (SELECT COUNT(*) FROM CTE)
|
||
union
|
||
SELECT Daily.ProjectId,
|
||
Daily.ProjectName,
|
||
Daily.ProjectCode,
|
||
Daily.YearMonth,
|
||
Daily.CheckTypeName,
|
||
Daily.MonthTotal,
|
||
SUM(Daily.MonthTotal)
|
||
OVER (PARTITION BY Daily.ProjectId,Daily.CheckTypeName ORDER BY Daily.YearMonth ASC) AS Accumulated
|
||
FROM Daily
|
||
WHERE Daily.RN <= (SELECT COUNT(*) FROM Daily)
|