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)