857 lines
32 KiB
Transact-SQL
857 lines
32 KiB
Transact-SQL
----获取安全人工时
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_SafeWorkTime')
|
||
DROP VIEW View_IOC_Home_SafeWorkTime
|
||
GO
|
||
Create view View_IOC_Home_SafeWorkTime
|
||
as
|
||
SELECT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
SUM(ISNULL(mmsb.TotalManhoursTotal, 0)) AS SafeWorkTime --安全工时
|
||
FROM Manager_ManhoursSortB mmsb
|
||
JOIN
|
||
Manager_MonthReportB mmb ON mmsb.MonthReportId = mmb.MonthReportId
|
||
JOIN
|
||
Base_Project Project ON mmb.ProjectId = Project.ProjectId
|
||
WHERE mmb.MonthReportId IN (SELECT TOP 1 MonthReportId
|
||
FROM Manager_MonthReportB
|
||
WHERE ProjectId = Project.ProjectId
|
||
ORDER BY Months DESC)
|
||
GROUP BY Project.ProjectId, Project.ProjectName, Project.ProjectCode
|
||
go
|
||
|
||
-----获取安全隐患整改单
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_SafeRectifyNotices')
|
||
DROP VIEW View_IOC_Home_SafeRectifyNotices
|
||
go
|
||
Create view View_IOC_Home_SafeRectifyNotices
|
||
as
|
||
SELECT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
R.RectifyNoticesId,--安全隐患整改单主键id
|
||
R.RectifyNoticesCode,--安全隐患整改单编码
|
||
Unit.UnitName,--单位名称
|
||
R.CheckManNames,--检查人姓名
|
||
WorkAreaName= STUFF((SELECT ',' + UnitWorkName
|
||
FROM dbo.WBS_UnitWork
|
||
where PATINDEX('%,' + RTRIM(UnitWorkId) + ',%', ',' + R.WorkAreaId + ',') > 0
|
||
FOR XML PATH('')), 1, 1, ''),--单位工程名称
|
||
R.CheckedDate,--检查日期
|
||
DutyPerson.UserName AS DutyPersonName,--责任人
|
||
R.DutyPersonTime,--接收人接收日期
|
||
R.CompleteDate,--日期
|
||
(CASE
|
||
WHEN States = 5 THEN '已完成'
|
||
ELSE '未完成' END) AS StatesName
|
||
FROM Check_RectifyNotices AS R
|
||
LEFT JOIN Base_Project AS Project ON Project.ProjectId = R.ProjectId
|
||
LEFT JOIN Base_Unit AS Unit ON Unit.UnitId = R.UnitId
|
||
LEFT JOIN Sys_User AS CompileMan ON CompileMan.UserId = R.CompleteManId
|
||
LEFT JOIN Sys_User AS DutyPerson ON DutyPerson.UserId = R.DutyPersonId
|
||
WHERE States IS NOT NULL
|
||
or States != '0'
|
||
go
|
||
|
||
----工程概况
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_ProjectOverview')
|
||
DROP VIEW View_IOC_Home_ProjectOverview
|
||
GO
|
||
Create view View_IOC_Home_ProjectOverview
|
||
as
|
||
select ProjectId,--项目id
|
||
ProjectName,--项目名称
|
||
ProjectCode,--项目编号
|
||
ShortName,--项目简称
|
||
ProjectAddress,--项目地址
|
||
OwnUnit = STUFF((SELECT ',' + UnitName
|
||
FROM dbo.Project_ProjectUnit
|
||
left join Base_Unit on Project_ProjectUnit.UnitId = Base_Unit.UnitId
|
||
where Project_ProjectUnit.ProjectId = Project.ProjectId
|
||
and UnitType = '4'
|
||
FOR XML PATH('')), 1, 1, ''),--建设单位
|
||
JLUnit = STUFF((SELECT ',' + UnitName
|
||
FROM dbo.Project_ProjectUnit
|
||
left join Base_Unit on Project_ProjectUnit.UnitId = Base_Unit.UnitId
|
||
where Project_ProjectUnit.ProjectId = Project.ProjectId
|
||
and UnitType = '3'
|
||
FOR XML PATH('')), 1, 1, ''),--监理单位
|
||
SGUnit = STUFF((SELECT ',' + UnitName
|
||
FROM dbo.Project_ProjectUnit
|
||
left join Base_Unit on Project_ProjectUnit.UnitId = Base_Unit.UnitId
|
||
where Project_ProjectUnit.ProjectId = Project.ProjectId
|
||
and UnitType = '2'
|
||
FOR XML PATH('')), 1, 1, ''),--施工单位
|
||
ContractDays= DATEDIFF(day, Project.StartDate, Project.EndDate),--合同总工期
|
||
ContractEndDate=Project.EndDate,--合同到期时间
|
||
RemainingDays=DATEDIFF(day, Project.EndDate, getdate())--距离竣工剩余时间
|
||
from Base_Project Project
|
||
go
|
||
-----质量统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_CQMSStatistics')
|
||
DROP VIEW View_IOC_Home_CQMSStatistics
|
||
GO
|
||
Create view View_IOC_Home_CQMSStatistics
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
QuestionDef,--问题描述
|
||
RectifyOpinion,----整改要求
|
||
HandleWay,--处理方式
|
||
LimitDate,--整改时间
|
||
(CASE
|
||
WHEN ck.State = '6' THEN '已完成'
|
||
ELSE '未完成' END) AS States --状态
|
||
from Check_JointCheckDetail ckdetail
|
||
left join Check_JointCheck ck on ckdetail.JointCheckId = ck.JointCheckId
|
||
left join Base_Project project on ck.ProjectId = project.ProjectId
|
||
union all
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
QuestionDef,--问题描述
|
||
RectifyOpinion,----整改要求
|
||
HandleWay,--处理方式
|
||
LimitDate,--整改时间
|
||
(CASE
|
||
WHEN ckControl.State = '7' THEN '已完成'
|
||
ELSE '未完成' END) AS States --状态
|
||
from Check_CheckControl ckControl
|
||
left join Base_Project project on ckControl.ProjectId = project.ProjectId
|
||
go
|
||
-----劳务数据
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_LabourData')
|
||
DROP VIEW View_IOC_Home_LabourData
|
||
GO
|
||
Create view View_IOC_Home_LabourData
|
||
as
|
||
SELECT DISTINCT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
record.Unitname,--单位名称
|
||
IdentityCard=record.IDCardNo, --身份证号码
|
||
workpost.WorkPostName,--岗位名称
|
||
(case
|
||
when workpost.PostType = '1' then '管理人员'
|
||
else '作业人员' end) as PostType--岗位类型
|
||
FROM T_d_EmployInOutRecord record
|
||
left join Base_Project project on record.ProjectId = project.ProjectId
|
||
left join SitePerson_Person person on person.IdentityCard = record.IDCardNo
|
||
left join Base_WorkPost workpost on person.WorkPostId = workpost.WorkPostId
|
||
WHERE record.RecordDate = CAST(GETDATE() AS DATE)
|
||
go
|
||
-----待办事项
|
||
--存储过程
|
||
---预警信息
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_Warning')
|
||
DROP VIEW View_IOC_Home_Warning
|
||
GO
|
||
Create view View_IOC_Home_Warning
|
||
as
|
||
SELECT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
y.PersonName,--人员姓名
|
||
y.IdentityCard,--人员身份证号号码
|
||
x.CertificateNo,--证书编号
|
||
CertificateName,--证书名称
|
||
x.SendDate,--发证时间
|
||
x.LimitDate--证书到期时间
|
||
FROM QualityAudit_PersonQuality x
|
||
JOIN SitePerson_Person y ON x.PersonId = y.PersonId
|
||
left join Base_Project project on y.ProjectId = project.ProjectId
|
||
WHERE x.LimitDate <= GETDATE()
|
||
go
|
||
---环境检测数据
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_Environmental')
|
||
DROP VIEW View_IOC_Home_Environmental
|
||
GO
|
||
Create view View_IOC_Home_Environmental
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
pm10,--PM10
|
||
pm25,--PM2.5
|
||
noise,--噪声
|
||
tem,--温度
|
||
hum,--湿度
|
||
ws,--风速
|
||
CompileDate
|
||
from Environmental_EnvironmentalMonitoring x
|
||
left join Base_Project project on x.ProjectId = project.ProjectId
|
||
go
|
||
--进度数据
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_Schedule')
|
||
DROP VIEW View_IOC_Home_Schedule
|
||
GO
|
||
Create view View_IOC_Home_Schedule
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
x.ScheduleId,--进度主键
|
||
x.ScheduleName,--进度名称
|
||
x.ScheduleDate,--日期
|
||
x.SortId--排序
|
||
from Sys_Schedule x
|
||
left join Base_Project project on x.ProjectId = project.ProjectId
|
||
go
|
||
--风险管控
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_Home_Hazard')
|
||
DROP VIEW View_IOC_Home_Hazard
|
||
GO
|
||
Create view View_IOC_Home_Hazard
|
||
as
|
||
SELECT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
x.HazardSelectedItemId,--风险管控主键id
|
||
x.HazardItems,--危险因素明细
|
||
x.DefectsType,--危险源类别
|
||
y.RiskLevel,--危险级别
|
||
y.RiskLevelName--危险级别名称
|
||
FROM Hazard_HazardSelectedItem x
|
||
JOIN Base_RiskLevel y ON x.HazardLevel = y.RiskLevelId
|
||
left join Base_Project project on x.ProjectId = project.ProjectId
|
||
go
|
||
--质量人员培训
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_CQMS_PersonTrain')
|
||
DROP VIEW View_IOC_CQMS_PersonTrain
|
||
GO
|
||
Create view View_IOC_CQMS_PersonTrain
|
||
as
|
||
SELECT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
x.InspectionPersonCode,--编号
|
||
x.PersonName,--培训人员
|
||
x.CompileDate--提交日期
|
||
FROM Comprehensive_InspectionPerson x
|
||
left join Base_Project project on x.ProjectId = project.ProjectId
|
||
where x.IsTrain = 1
|
||
go
|
||
--质量技术交底
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_CQMS_TechnicalDisclose')
|
||
DROP VIEW View_IOC_CQMS_TechnicalDisclose
|
||
GO
|
||
Create view View_IOC_CQMS_TechnicalDisclose
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
x.DesignDetailsCode,--交底编号
|
||
x.DetailsMan,--交底人
|
||
x.CompileDate,--提交日期
|
||
x.JoinPersonNum--交底人数
|
||
from Comprehensive_DesignDetails x
|
||
left join Base_Project project on project.ProjectId = x.ProjectId;
|
||
go
|
||
--质量计量器具数据
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_CQMS_InspectionMachine')
|
||
DROP VIEW View_IOC_CQMS_InspectionMachine
|
||
GO
|
||
Create view View_IOC_CQMS_InspectionMachine
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
x.InspectionMachineCode,--报验编号
|
||
x.InspectionMachineName,--名称
|
||
x.SpecificationModel,--规格型号
|
||
x.NextTestDate,--下次检验日期
|
||
x.TestCycle,--检验周期
|
||
x.IsVerification,--是否在校验期内
|
||
x.InspectionDate,--校验日期
|
||
x.LeaveDate,--离场时间
|
||
x.UnitsCount,--台数
|
||
(case x.IsCheckOK when 1 then '校准合格' else '校准不合格' end) as IsCheckOK --是否合格
|
||
from Comprehensive_InspectionMachine x
|
||
left join Base_Project project on project.ProjectId = x.ProjectId
|
||
where x.IsOnSite = 1
|
||
and x.InspectionType like '%计量%';
|
||
go
|
||
--质量控制点
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_CQMS_Breakdown')
|
||
DROP VIEW View_IOC_CQMS_Breakdown
|
||
GO
|
||
Create view View_IOC_CQMS_Breakdown
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
x.BreakdownCode,--分项编号
|
||
x.BreakdownName,--分项名称
|
||
x.Basis,--执行依据或规范
|
||
x.CheckPoints,--质量检查要点
|
||
x.RecordAndCode,--质量记录及格式文本号
|
||
x.Class--控制等级
|
||
from WBS_BreakdownProject x
|
||
left join Base_Project project on project.ProjectId = x.ProjectId
|
||
where x.IsSelected = 1
|
||
and (x.Class like '%A%' or x.Class like '%B%' or x.Class like '%C%');
|
||
go
|
||
--质量问题治理数据
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_CQMS_CheckControl')
|
||
DROP VIEW View_IOC_CQMS_CheckControl
|
||
GO
|
||
Create view View_IOC_CQMS_CheckControl
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
UnitName,
|
||
QuestionDef,--问题描述
|
||
RectifyOpinion,----整改要求
|
||
HandleWay,--处理方式
|
||
RectifyDate,--整改时间
|
||
(case x.State when '7' then '已整改' else '未整改' end) as state
|
||
from Check_CheckControl x
|
||
left join Base_Project project on project.ProjectId = x.ProjectId
|
||
left join Base_Unit unit on x.UnitId = unit.UnitId
|
||
go
|
||
--质量施工方案
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_CQMS_ConstructSolution')
|
||
DROP VIEW View_IOC_CQMS_ConstructSolution
|
||
GO
|
||
Create view View_IOC_CQMS_ConstructSolution
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
UnitName,--单位名称
|
||
SolutionName,--方案名称
|
||
(case x.State
|
||
when '0' then '重报'
|
||
when '2' then '会签'
|
||
when '3' then '审批完成'
|
||
else '编制' end) as state
|
||
from Solution_CQMSConstructSolution x
|
||
left join Base_Project project on project.ProjectId = x.ProjectId
|
||
left join Base_Unit unit on x.UnitId = unit.UnitId
|
||
go
|
||
--质量验收数据
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_CQMS_InspectionManagement')
|
||
DROP VIEW View_IOC_CQMS_InspectionManagement
|
||
GO
|
||
Create view View_IOC_CQMS_InspectionManagement
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
unit.UnitName,--单位名称
|
||
y.InspectionCode,--共检通知单编号
|
||
unitwork.UnitWorkName,--单位工程名称
|
||
branch.DivisionName,--分部
|
||
controlPointType.BreakdownName,--控制点内容
|
||
(case y.IsOnceQualified
|
||
when '1' then '合格'
|
||
else '不合格' end) as state --是否合格
|
||
from ProcessControl_InspectionManagementDetail x
|
||
left join ProcessControl_InspectionManagement y on x.InspectionId = y.InspectionId
|
||
left join Base_Project project on project.ProjectId = y.ProjectId
|
||
left join Base_Unit unit on y.UnitId = unit.UnitId
|
||
left join WBS_UnitWork unitwork on x.UnitWorkId = unitwork.UnitWorkId
|
||
left join WBS_DivisionProject branch on x.Branch = branch.DivisionProjectId
|
||
left join WBS_BreakdownProject controlPointType on x.ControlPointType = controlPointType.breakdownProjectId
|
||
go
|
||
--质量检查待处理事项(明细)
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_CQMS_ToDoItem')
|
||
DROP VIEW View_IOC_CQMS_ToDoItem
|
||
GO
|
||
Create view View_IOC_CQMS_ToDoItem
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
UnitName,--单位名称
|
||
CheckDate,--检查时间
|
||
CheckSite,--部位
|
||
(case x.State
|
||
when '3' then '待整改'
|
||
when '3' then '待整改'
|
||
when '5' then '待复查验收'
|
||
when '6' then '待复查验收'
|
||
else '' end) as state --状态
|
||
from Check_CheckControl x
|
||
left join Base_Project project on project.ProjectId = x.ProjectId
|
||
left join Base_Unit unit on x.UnitId = unit.UnitId
|
||
where State in ('3', '4', '5', '6')
|
||
go
|
||
|
||
--安全
|
||
--项目在场人员
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HSSE_PresentPerson')
|
||
DROP VIEW View_IOC_HSSE_PresentPerson
|
||
GO
|
||
Create view View_IOC_HSSE_PresentPerson
|
||
as
|
||
SELECT DISTINCT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
IdentityCard=record.IDCardNo,--身份证号码
|
||
record.EmployName --姓名
|
||
FROM T_d_EmployInOutRecord record
|
||
left join Base_Project project on record.ProjectId = project.ProjectId
|
||
WHERE record.RecordDate = CAST(GETDATE() AS DATE)
|
||
go
|
||
|
||
--项目安全人工时
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HSSE_SafeWorkTime')
|
||
DROP VIEW View_IOC_HSSE_SafeWorkTime
|
||
GO
|
||
Create view View_IOC_HSSE_SafeWorkTime
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
unit.UnitName,
|
||
unit.UnitId,
|
||
SUM(COALESCE(detail.PersonWorkTime, 0)) as PersonWorkTime
|
||
from Project_ProjectUnit prounit
|
||
left join Base_Project Project on Project.ProjectId = prounit.ProjectId
|
||
left join SitePerson_DayReport report on report.ProjectId = prounit.ProjectId
|
||
left join SitePerson_DayReportDetail detail
|
||
on detail.DayReportId = report.DayReportId and detail.UnitId = prounit.UnitId
|
||
left join Base_Unit unit on unit.UnitId = prounit.UnitId
|
||
group by Project.ProjectCode, Project.ProjectId, Project.ProjectName, unit.UnitName, unit.UnitId
|
||
|
||
go
|
||
---安全检查问题统计-按单位统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HSSE_CheckStatistics_Unit')
|
||
DROP VIEW View_IOC_HSSE_CheckStatistics_Unit
|
||
GO
|
||
Create view View_IOC_HSSE_CheckStatistics_Unit
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
unit.UnitName,
|
||
unit.UnitId,
|
||
COUNT(CASE WHEN register.states = '1' THEN 1 END) AS NeedRectificationCount,
|
||
COUNT(register.HazardRegisterId) AS AllCount
|
||
from Project_ProjectUnit prounit
|
||
left join Base_Project Project on Project.ProjectId = prounit.ProjectId
|
||
left join View_Hazard_HazardRegister register
|
||
on register.ProjectId = prounit.ProjectId and register.ResponsibleUnit = prounit.UnitId
|
||
left join Base_Unit unit on unit.UnitId = prounit.UnitId
|
||
group by Project.ProjectCode, Project.ProjectId, Project.ProjectName, unit.UnitName, unit.UnitId
|
||
go
|
||
--安全检查问题统计-按类型统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HSSE_CheckStatistics_Type')
|
||
DROP VIEW View_IOC_HSSE_CheckStatistics_Type
|
||
GO
|
||
Create view View_IOC_HSSE_CheckStatistics_Type
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
register.RegisterTypesName,--问题类别
|
||
COUNT(CASE WHEN register.states = '1' THEN 1 END) AS NeedRectificationCount,--待整改数量
|
||
COUNT(register.HazardRegisterId) AS AllCount --全部数量
|
||
from View_Hazard_HazardRegister register
|
||
left join Base_Project Project on Project.ProjectId = register.ProjectId
|
||
group by Project.ProjectCode, Project.ProjectId, Project.ProjectName, register.RegisterTypesName
|
||
go
|
||
--作业许统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HSSE_LicenseStatistics')
|
||
DROP VIEW View_IOC_HSSE_LicenseStatistics
|
||
GO
|
||
Create view View_IOC_HSSE_LicenseStatistics
|
||
as
|
||
with cte as
|
||
(select ProjectId,
|
||
manager.LicenseManagerCode as LicenseCode,
|
||
(case LicenseTypeCode
|
||
when '4' then '动火'
|
||
when '2' then '高处'
|
||
when '7' then '受限'
|
||
when '04' then '射线'
|
||
when '5' then '断路'
|
||
when '8' then '动土'
|
||
when '' then '夜间'
|
||
when '3' then '吊装' end
|
||
) as TypeName
|
||
from License_LicenseManager manager
|
||
left join Base_LicenseType license on manager.LicenseTypeId = license.LicenseTypeId
|
||
union all
|
||
select ProjectId, LicenseCode, '动火' as TypeName
|
||
from License_FireWork
|
||
where States = '3'
|
||
union all
|
||
select ProjectId, LicenseCode, '高处' as TypeName
|
||
from License_HeightWork
|
||
where States = '3'
|
||
union all
|
||
select ProjectId, LicenseCode, '受限' as TypeName
|
||
from License_LimitedSpace
|
||
where States = '3'
|
||
union all
|
||
select ProjectId, LicenseCode, '射线' as TypeName
|
||
from License_RadialWork
|
||
where States = '3'
|
||
union all
|
||
select ProjectId, LicenseCode, '断路' as TypeName
|
||
from License_OpenCircuit
|
||
where States = '3'
|
||
union all
|
||
select ProjectId, LicenseCode, '动土' as TypeName
|
||
from License_BreakGround
|
||
where States = '3'
|
||
union all
|
||
select ProjectId, LicenseCode, '夜间' as TypeName
|
||
from License_NightWork
|
||
where States = '3'
|
||
union all
|
||
select ProjectId, LicenseCode, '吊装' as TypeName
|
||
from License_LiftingWork
|
||
where States = '3')
|
||
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
cte.LicenseCode,
|
||
TypeName
|
||
from cte
|
||
left join Base_Project Project on Project.ProjectId = cte.ProjectId
|
||
where cte.TypeName is not null
|
||
go
|
||
--入场教育培训统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HSSE_TrainStatistics')
|
||
DROP VIEW View_IOC_HSSE_TrainStatistics
|
||
GO
|
||
Create view View_IOC_HSSE_TrainStatistics
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
detail.PersonId,--培训人员id
|
||
person.PersonName,--培训人员姓名
|
||
unit.UnitName,--培训单位
|
||
detail.CheckScore,--考试成绩
|
||
detail.CheckResult --考核结果(true-合格，false-不合格)
|
||
from EduTrain_TrainRecordDetail detail
|
||
join EduTrain_TrainRecord record on detail.TrainingId = record.TrainingId
|
||
join Base_Project Project on record.ProjectId = Project.ProjectId
|
||
join SitePerson_Person person on detail.PersonId = person.PersonId
|
||
join Base_Unit unit on person.UnitId = unit.UnitId
|
||
where record.TrainTypeId = 'dfb6a37e-4412-4ba9-ad59-3bc505bc21f7'
|
||
go
|
||
--事故数据统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HSSE_AccidentStatistics')
|
||
DROP VIEW View_IOC_HSSE_AccidentStatistics
|
||
GO
|
||
Create view View_IOC_HSSE_AccidentStatistics
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
count(case when report.AccidentTypeId in ('1', '2', '3', '4') THEN 1 END) as PersonalInjuryNum,--人身伤害
|
||
count(case when report.AccidentTypeId = '5' THEN 1 END) as AttemptedAccidentNumNum,--未遂事故
|
||
count(case when report.AccidentTypeId in ('6', '7') THEN 1 END) as FireNum,--火灾
|
||
count(case when report.AccidentTypeId in ('8', '9') THEN 1 END) as MechanicalEquipmentNum,--机械设备
|
||
count(case when report.AccidentTypeId = '10' THEN 1 END) as EnvironmentalEffectNum,--环境影响
|
||
count(case when report.AccidentTypeId = '11' THEN 1 END) as OtherNum--其他
|
||
from Accident_AccidentReport as report
|
||
join Base_Project Project on report.ProjectId = Project.ProjectId
|
||
group by Project.ProjectId, Project.ProjectName, Project.ProjectCode
|
||
go
|
||
--施工进度统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_JDGL_ProgressStatistics')
|
||
DROP VIEW View_IOC_JDGL_ProgressStatistics
|
||
GO
|
||
Create view View_IOC_JDGL_ProgressStatistics
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
count(detail.SpotCheckDetailId) as Num,
|
||
unitwork.UnitWorkName,
|
||
(case unitwork.ProjectType
|
||
when '1' then '建筑单位工程'
|
||
when '2' then '安装单位工程' end) as ProjectType
|
||
from View_Check_SoptCheckDetail detail
|
||
join Base_Project Project on Project.ProjectId = detail.ProjectId
|
||
join WBS_UnitWork unitwork on unitwork.UnitWorkId = detail.UnitWorkId
|
||
where IsOK = 1
|
||
group by Project.ProjectId, Project.ProjectName, Project.ProjectCode, unitwork.UnitWorkName, unitwork.ProjectType
|
||
go
|
||
--焊接检测单管理-单位（计算一次合格率）
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HJGL_CheckManageStatistics_Unit')
|
||
DROP VIEW View_IOC_HJGL_CheckManageStatistics_Unit
|
||
GO
|
||
Create view View_IOC_HJGL_CheckManageStatistics_Unit
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
unit.UnitName,
|
||
unit.UnitId,
|
||
SUM(cht_totalfilm) AS CHT_TotalFilm, --拍片总数
|
||
SUM(cht_passfilm) AS CHT_PassFilm --合格片数
|
||
from Project_ProjectUnit prounit
|
||
join Base_Project Project on Project.ProjectId = prounit.ProjectId
|
||
join PW_IsoInfo IsoInfo on IsoInfo.UnitId = prounit.UnitId and isoinfo.ProjectId = prounit.ProjectId
|
||
join PW_JointInfo JointInfo on IsoInfo.ISO_ID = JointInfo.ISO_ID
|
||
join CH_CheckItem ch_checkitem on JointInfo.JOT_ID = ch_checkitem.JOT_ID
|
||
join Base_Unit unit on unit.UnitId = prounit.UnitId
|
||
where IsoInfo.UnitId is not null
|
||
group by Project.ProjectCode, Project.ProjectId, Project.ProjectName, unit.UnitName, unit.UnitId
|
||
go
|
||
--焊接检测单管理-单位工程（计算一次合格率）
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HJGL_CheckManageStatistics_WorkArea')
|
||
DROP VIEW View_IOC_HJGL_CheckManageStatistics_WorkArea
|
||
GO
|
||
Create view View_IOC_HJGL_CheckManageStatistics_WorkArea
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
WorkArea.WorkAreaName,
|
||
WorkArea.WorkAreaId,
|
||
SUM(cht_totalfilm) AS CHT_TotalFilm, --拍片总数
|
||
SUM(cht_passfilm) AS CHT_PassFilm --合格片数
|
||
from ProjectData_WorkArea WorkArea
|
||
join Base_Project Project on Project.ProjectId = WorkArea.ProjectId
|
||
join PW_IsoInfo IsoInfo on IsoInfo.WorkAreaId = WorkArea.WorkAreaId and isoinfo.ProjectId = WorkArea.ProjectId
|
||
join PW_JointInfo JointInfo on IsoInfo.ISO_ID = JointInfo.ISO_ID
|
||
join CH_CheckItem ch_checkitem on JointInfo.JOT_ID = ch_checkitem.JOT_ID
|
||
group by Project.ProjectCode, Project.ProjectId, Project.ProjectName, WorkArea.WorkAreaName, WorkArea.WorkAreaId
|
||
go
|
||
--焊工信息
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HJGL_WelderData')
|
||
DROP VIEW View_IOC_HJGL_WelderData
|
||
GO
|
||
Create view View_IOC_HJGL_WelderData
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
welder.WED_Name as PersonName,
|
||
unit.UnitName,
|
||
welder.IdentityCard
|
||
from BS_Welder welder
|
||
join Base_Project Project on Project.ProjectId = welder.ProjectId
|
||
join Base_Unit unit on welder.WED_Unit = unit.UnitId
|
||
where WED_IfOnGuard = 1;
|
||
go
|
||
--焊接工程量统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HJGL_WeldingQuantityStatistics')
|
||
DROP VIEW View_IOC_HJGL_WeldingQuantityStatistics
|
||
GO
|
||
Create view View_IOC_HJGL_WeldingQuantityStatistics
|
||
as
|
||
SELECT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
pw_isoinfo.ISO_IsoNo,--管线号
|
||
pw_jointinfo.JOT_JointNo,--焊口号
|
||
(case when pw_isoinfo.[Is_Standard] = 0 then JOT_Size else pw_jointinfo.JOT_Size end) AS Din --达因
|
||
,
|
||
JOT_DoneDin as Finished_din, --完成达因
|
||
(CASE WHEN DReportID is not null THEN 1 ELSE 0 END) IsFinish --是否完成
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT join Base_Project Project on Project.ProjectId = pw_jointinfo.ProjectId
|
||
go
|
||
--焊接进度分析——单位工程
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HJGL_ProgressStatistics_WorkArea')
|
||
DROP VIEW View_IOC_HJGL_ProgressStatistics_WorkArea
|
||
GO
|
||
Create view View_IOC_HJGL_ProgressStatistics_WorkArea
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
WorkArea.WorkAreaName,
|
||
WorkArea.WorkAreaId,
|
||
SUM(JOT_Size) AS Total_din, --总达因
|
||
SUM(JOT_DoneDin) AS Finished_total_Din --完成达因
|
||
from ProjectData_WorkArea WorkArea
|
||
join Base_Project Project on Project.ProjectId = WorkArea.ProjectId
|
||
join PW_IsoInfo IsoInfo on IsoInfo.WorkAreaId = WorkArea.WorkAreaId and isoinfo.ProjectId = WorkArea.ProjectId
|
||
join PW_JointInfo JointInfo on IsoInfo.ISO_ID = JointInfo.ISO_ID
|
||
join CH_CheckItem ch_checkitem on JointInfo.JOT_ID = ch_checkitem.JOT_ID
|
||
group by Project.ProjectCode, Project.ProjectId, Project.ProjectName, WorkArea.WorkAreaName, WorkArea.WorkAreaId
|
||
go
|
||
--焊接进度分析——分包商
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HJGL_ProgressStatistics_Unit')
|
||
DROP VIEW View_IOC_HJGL_ProgressStatistics_Unit
|
||
GO
|
||
Create view View_IOC_HJGL_ProgressStatistics_Unit
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
unit.UnitName,
|
||
unit.UnitId,
|
||
SUM(JOT_Size) AS Total_din, --总达因
|
||
SUM(JOT_DoneDin) AS Finished_total_Din --完成达因
|
||
from Project_ProjectUnit prounit
|
||
join Base_Project Project on Project.ProjectId = prounit.ProjectId
|
||
join PW_IsoInfo IsoInfo on IsoInfo.UnitId = prounit.UnitId and isoinfo.ProjectId = prounit.ProjectId
|
||
join PW_JointInfo JointInfo on IsoInfo.ISO_ID = JointInfo.ISO_ID
|
||
join CH_CheckItem ch_checkitem on JointInfo.JOT_ID = ch_checkitem.JOT_ID
|
||
join Base_Unit unit on unit.UnitId = prounit.UnitId
|
||
where IsoInfo.UnitId is not null
|
||
group by Project.ProjectCode, Project.ProjectId, Project.ProjectName, unit.UnitName, unit.UnitId
|
||
go
|
||
--焊接缺陷统计
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HJGL_DefectStatistics')
|
||
DROP VIEW View_IOC_HJGL_DefectStatistics
|
||
GO
|
||
Create view View_IOC_HJGL_DefectStatistics
|
||
as
|
||
select Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
Defects_Definition,--缺陷名称
|
||
count(*) as Num --缺陷数量
|
||
from CH_CheckItem a
|
||
left join CH_Check b on a.CHT_CheckID = b.CHT_CheckID
|
||
join Base_Project Project on Project.ProjectId = b.ProjectId
|
||
where Defects_Definition <> ''
|
||
and CHT_CheckResult = '不合格'
|
||
group by Defects_Definition, Project.ProjectId, Project.ProjectName, Project.ProjectCode
|
||
go
|
||
--功效分析
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_HJGL_EfficacyAnalysis')
|
||
DROP VIEW View_IOC_HJGL_EfficacyAnalysis
|
||
GO
|
||
Create view View_IOC_HJGL_EfficacyAnalysis
|
||
as
|
||
SELECT Project.ProjectId,--项目id
|
||
Project.ProjectName,--项目名称
|
||
Project.ProjectCode,--项目编码
|
||
jot_welddate as Welddate,
|
||
Day,
|
||
Month,
|
||
COUNT(DISTINCT JOT_CellWelder) AS WelderNum
|
||
FROM (SELECT DAY(jot_welddate) AS Day,
|
||
MONTH(jot_welddate) AS Month,
|
||
JOT_CellWelder,
|
||
jot_welddate,
|
||
PW_IsoInfo.ProjectId
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN BO_WeldReportMain ON BO_WeldReportMain.dreportid = pw_jointinfo.dreportid
|
||
|
||
UNION
|
||
|
||
SELECT DAY(jot_welddate) AS Day,
|
||
MONTH(jot_welddate) AS Month,
|
||
JOT_FloorWelder,
|
||
jot_welddate,
|
||
PW_IsoInfo.ProjectId
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN BO_WeldReportMain ON BO_WeldReportMain.dreportid = pw_jointinfo.dreportid) AS a
|
||
join Base_Project Project on Project.ProjectId = a.ProjectId
|
||
GROUP BY Day, Month, jot_welddate, Project.ProjectId, Project.ProjectName, Project.ProjectCode;
|
||
go
|
||
--安全人工时
|
||
IF EXISTS(SELECT 1
|
||
FROM sys.views
|
||
WHERE name = 'View_IOC_KQGL_SafeWorktime')
|
||
DROP VIEW View_IOC_KQGL_SafeWorktime
|
||
GO
|
||
create view dbo.View_IOC_KQGL_SafeWorktime as
|
||
WITH CTE AS (SELECT project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
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 Base_Project project ON a.ProjectId = project.ProjectId
|
||
|
||
WHERE ProUnit.ProjectId = a.ProjectId
|
||
GROUP BY project.ProjectId,
|
||
project.ProjectName,
|
||
project.ProjectCode,
|
||
CONVERT(VARCHAR(7), CompileDate, 120))
|
||
SELECT CTE.ProjectId,
|
||
CTE.ProjectName,
|
||
CTE.ProjectCode,
|
||
CTE.YearMonth,
|
||
CTE.MonthTotalPersonWorkTime,
|
||
SUM(CTE.MonthTotalPersonWorkTime)
|
||
OVER (PARTITION BY CTE.ProjectId ORDER BY CTE.YearMonth ASC) AS AccumulatedPersonWorkTime
|
||
|
||
FROM CTE
|
||
WHERE CTE.RN <= (SELECT COUNT(*) FROM CTE) |