270 lines
14 KiB
MySQL
270 lines
14 KiB
MySQL
|
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
|
|||
|
|
--ʵ<EFBFBD>﹤<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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
|
|||
|
|
--<EFBFBD><EFBFBD>ĿͼƬ
|
|||
|
|
|
|||
|
|
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 ('<EFBFBD><EFBFBD>Ŀ<EFBFBD>ֳ<EFBFBD>ͼ', '<EFBFBD><EFBFBD>ĿЧ<EFBFBD><EFBFBD>ͼ')
|
|||
|
|
|
|||
|
|
go
|
|||
|
|
--<EFBFBD>ֳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͼ
|
|||
|
|
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,--<EFBFBD>ܰ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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,--<EFBFBD>ְ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD>й<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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,--<EFBFBD>ְ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD>⼮<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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,--<EFBFBD>ְ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD>й<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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,--<EFBFBD>ְ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD>⼮<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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,--<EFBFBD>ְ<EFBFBD>ֱ<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD>й<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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,--<EFBFBD>ְ<EFBFBD>ֱ<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD>⼮<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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,--<EFBFBD>ְ<EFBFBD>ֱ<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD>й<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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,--<EFBFBD>ְ<EFBFBD>ֱ<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD>⼮<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
(CASE
|
|||
|
|
WHEN (select count(*) from RealName_SynchroSet where RealName_SynchroSet.UnitId='b4f3d912-ca6d-440c-a8d7-bc6a5d5a1f84' and proCode=pro.ContractNo)=1 THEN '<EFBFBD><EFBFBD>'
|
|||
|
|
ELSE '<EFBFBD><EFBFBD>'
|
|||
|
|
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
|
|||
|
|
--<EFBFBD>˹<EFBFBD>ʱ
|
|||
|
|
|
|||
|
|
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
|
|||
|
|
--<EFBFBD>˹<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
|
|||
|
|
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
|
|||
|
|
--<EFBFBD>ֳ<EFBFBD>HSE״̬<EFBFBD><EFBFBD><EFBFBD>¹ʱ<EFBFBD><EFBFBD><EFBFBD>ͳ<EFBFBD>ƣ<EFBFBD>
|
|||
|
|
|
|||
|
|
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
|
|||
|
|
--<EFBFBD>ֳ<EFBFBD>HSE<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
|
|||
|
|
create view dbo.View_HSSECheckReport as
|
|||
|
|
with Daily as (
|
|||
|
|
SELECT project.ProjectId,
|
|||
|
|
project.ProjectName,
|
|||
|
|
project.ProjectCode,
|
|||
|
|
'<EFBFBD><EFBFBD>ȫ<EFBFBD>ճ<EFBFBD>Ѳ<EFBFBD><EFBFBD>' 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 'ÿ<EFBFBD>ܰ<EFBFBD>ȫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
WHEN a.CheckType = '2' THEN 'ÿ<EFBFBD>°<EFBFBD>ȫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
WHEN a.CheckType = '3' THEN '<EFBFBD><EFBFBD>ǰ<EFBFBD>ں<EFBFBD><EFBFBD><EFBFBD>ȫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
WHEN a.CheckType = '0' THEN 'ר<EFBFBD>ȫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'
|
|||
|
|
ELSE 'ר<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' 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)
|