214 lines
24 KiB
Transact-SQL
214 lines
24 KiB
Transact-SQL
USE [SGGLDB_TCCC]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[sp_rpt_UnitWorkareaAnalyze] Script Date: 2021/9/6 19:03:42 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
|
||
ALTER PROC [dbo].[sp_rpt_UnitWorkareaAnalyze]
|
||
@UnitNo nVARCHAR(400) = NULL,
|
||
@AreaNo nVARCHAR(50) = NULL,
|
||
@installationId nVARCHAR(50) = NULL,
|
||
@ste_steeltype nvarchar(50) = NULL,
|
||
@startTime datetime = NULL,
|
||
@endTime datetime = NULL,
|
||
@projectId NVARCHAR(50) = NULL,
|
||
@supervisorUnitId NVARCHAR(50) = NULL,
|
||
@IsStandard NVARCHAR(50) = NULL
|
||
|
||
AS
|
||
/*单位工区进度分析*/
|
||
SELECT
|
||
WorkArea.ProjectId,
|
||
WorkArea.WorkAreaCode AS baw_areano,--工区代号
|
||
Unit.UnitCode AS bsu_unitcode,--单位代码
|
||
Unit.UnitName AS bsu_unitname, --单位名称
|
||
Installation.InstallationCode, --装置代号
|
||
Installation.InstallationName, --装置名称
|
||
-- 最近焊期
|
||
ISNULL(total_jot.total_jot,0) AS total_jot, --总焊口
|
||
--- 累计数 start
|
||
ISNULL(total_sjot.total_sjot,0) AS total_sjot, --预制总焊口数
|
||
ISNULL(total_fjot.total_fjot,0) AS total_fjot, --安装总焊口数
|
||
ISNULL(cut_total_jot.cut_total_jot,0) AS cut_total_jot, --切除焊口
|
||
CAST(ISNULL(total_jot.total_din,0) AS DECIMAL(19,2)) AS total_din,--总达因
|
||
CAST(ISNULL(total_sjot.total_Sdin,0) AS DECIMAL(19,2)) AS total_Sdin, --预制总达因
|
||
CAST(ISNULL(total_fjot.total_Fdin,0) AS DECIMAL(19,2)) AS total_Fdin, --安装总达因
|
||
ISNULL(finished_total_jot_bq.finished_total_jot_bq,0) AS finished_total_jot_bq, --本期完成焊口数
|
||
ISNULL(finished_total_sjot_bq.finished_total_sjot_bq,0) AS finished_total_sjot_bq, --本期完成预制焊口数
|
||
ISNULL(finished_total_fjot_bq.finished_total_fjot_bq,0) AS finished_total_fjot_bq, --本期完成安装焊口数
|
||
|
||
CAST((CASE ISNULL(total_jot.total_jot ,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_jot_bq.finished_total_jot_bq,0) / (1.0 * total_jot.total_jot)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_bq, --本期完成比例
|
||
|
||
CAST((CASE ISNULL(total_jot.total_jot,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_sjot_bq.finished_total_sjot_bq,0)/ (1.0 *total_jot.total_jot)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_s_bq, -- 本期预制完成比例
|
||
|
||
CAST((CASE ISNULL(total_jot.total_jot,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_fjot_bq.finished_total_fjot_bq,0)/ (1.0 *total_jot.total_jot)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_f_bq, -- 本期安装完成比例
|
||
|
||
CAST(ISNULL(finished_total_jot_bq.finished_total_din_bq ,0) AS DECIMAL(19,2)) AS finished_total_din_bq, --本期完成达因
|
||
CAST(ISNULL(finished_total_sjot_bq.finished_total_Sdin_bq,0) AS DECIMAL(19,2)) AS finished_total_Sdin_bq,----本期完成预制达因
|
||
CAST(ISNULL(finished_total_fjot_bq.finished_total_Fdin_bq,0) AS DECIMAL(19,2)) AS finished_total_Fdin_bq, ----本期完成安装达因
|
||
|
||
CAST((CASE ISNULL(total_jot.total_din,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_jot_bq.finished_total_din_bq ,0)/ (1.0 * total_jot.total_din)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_din_bq, --本期完成达因比例
|
||
CAST((CASE ISNULL(total_jot.total_din,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_sjot_bq.finished_total_Sdin_bq,0) /(1.0 * total_jot.total_din)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_din_s_bq, -- 本期完成预制达因比例
|
||
CAST((CASE ISNULL(total_jot.total_din,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_fjot_bq.finished_total_Fdin_bq,0)/ (1.0 * total_jot.total_din)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_din_f_bq, -- 本期完成安装达因比例
|
||
|
||
ISNULL(finished_total_jot.finished_total_jot,0) AS finished_total_jot, --完成焊口
|
||
ISNULL(finished_total_sjot.finished_total_sjot,0) AS finished_total_sjot, --完成预制焊口
|
||
ISNULL(finished_total_fjot.finished_total_fjot,0) AS finished_total_fjot,--完成安装焊口
|
||
|
||
CAST((CASE ISNULL(total_jot.total_jot ,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_jot.finished_total_jot,0)/ (1.0 * total_jot.total_jot)
|
||
END) AS DECIMAL(19,3)) AS finisedrate, --完成比例
|
||
CAST((CASE ISNULL(total_jot.total_jot,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_fjot.finished_total_fjot,0) / (1.0 * total_jot.total_jot)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_f, -- 安装完成比例
|
||
CAST((CASE ISNULL(total_jot.total_jot,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(finished_total_sjot.finished_total_sjot,0) / (1.0 * total_jot.total_jot)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_s, -- 预制完成比例
|
||
|
||
CAST(ISNULL(total_jot.finished_total_din,0) AS DECIMAL(19,2)) AS finished_total_din, --完成达因
|
||
CAST(ISNULL(total_sjot.finished_total_Sdin,0) AS DECIMAL(19,2)) AS finished_total_Sdin,--完成预制达因
|
||
CAST(ISNULL(total_fjot.finished_total_Fdin,0) AS DECIMAL(19,2)) AS finished_total_Fdin, --完成安装达因
|
||
|
||
CAST((CASE ISNULL(total_jot.total_din,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(total_jot.finished_total_din,0) / (1.0 * total_jot.total_din)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_din, ---完成达因比例
|
||
|
||
CAST((CASE ISNULL(total_jot.total_din,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(total_sjot.finished_total_Sdin,0) / (1.0 *total_jot.total_din)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_din_s, -- 完成预制达因比例
|
||
|
||
CAST((CASE ISNULL(total_jot.total_din,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(total_fjot.finished_total_Fdin,0) / (1.0 *total_jot.total_din)
|
||
END) AS DECIMAL(19,3)) AS finisedrate_din_f -- 完成安装达因比例
|
||
|
||
FROM ProjectData_WorkArea AS WorkArea
|
||
LEFT JOIN Base_Unit AS Unit ON WorkArea.UnitId =Unit.UnitId
|
||
LEFT JOIN Project_Installation AS Installation ON Installation.InstallationId = WorkArea.InstallationId
|
||
LEFT JOIN (SELECT COUNT(*) total_jot ,pw_isoinfo.WorkAreaId ,SUM(JOT_Size) AS total_din
|
||
,SUM(JOT_DoneDin) AS finished_total_din
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS total_jot ON total_jot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) total_sjot ,pw_isoinfo.WorkAreaId, SUM(JOT_Size) AS total_Sdin
|
||
,SUM(JOT_DoneDin) AS finished_total_Sdin
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.WLO_Code='S' AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS total_sjot ON total_sjot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) total_fjot ,pw_isoinfo.WorkAreaId, SUM(JOT_Size) AS total_Fdin
|
||
,SUM(JOT_DoneDin) AS finished_total_Fdin
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.WLO_Code='F' AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS total_fjot ON total_fjot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) cut_total_jot ,pw_isoinfo.WorkAreaId
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.JOT_JointStatus='104' AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS cut_total_jot ON cut_total_jot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_jot_bq ,pw_isoinfo.WorkAreaId ,SUM(JOT_DoneDin) AS finished_total_din_bq
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
LEFT JOIN BO_WeldReportMain ON BO_WeldReportMain.dreportid = pw_jointinfo.dreportid
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.DReportID is not null
|
||
and (jot_welddate >= @startTime OR @startTime IS NULL) and (jot_welddate <=@endTime OR @endTime IS NULL) AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS finished_total_jot_bq ON finished_total_jot_bq.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_sjot_bq ,pw_isoinfo.WorkAreaId,SUM(JOT_DoneDin) AS finished_total_Sdin_bq
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
LEFT JOIN BO_WeldReportMain ON BO_WeldReportMain.dreportid = pw_jointinfo.dreportid
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.DReportID is not null
|
||
and (jot_welddate >= @startTime OR @startTime IS NULL) and (jot_welddate <=@endTime OR @endTime IS NULL)
|
||
and WLO_Code='S' AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS finished_total_sjot_bq ON finished_total_sjot_bq.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_fjot_bq ,pw_isoinfo.WorkAreaId , SUM(JOT_DoneDin) AS finished_total_Fdin_bq
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
LEFT JOIN BO_WeldReportMain ON BO_WeldReportMain.dreportid = pw_jointinfo.dreportid
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.DReportID is not null
|
||
and (jot_welddate >= @startTime OR @startTime IS NULL) and (jot_welddate <=@endTime OR @endTime IS NULL)
|
||
and WLO_Code='F' AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS finished_total_fjot_bq ON finished_total_fjot_bq.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_jot ,pw_isoinfo.WorkAreaId
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and DReportID is not null AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS finished_total_jot ON finished_total_jot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_sjot ,pw_isoinfo.WorkAreaId
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and DReportID is not null
|
||
and WLO_Code='S' AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS finished_total_sjot ON finished_total_sjot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_fjot ,pw_isoinfo.WorkAreaId
|
||
FROM pw_jointinfo
|
||
LEFT JOIN pw_isoinfo ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and DReportID is not null
|
||
and WLO_Code='F' AND
|
||
(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
|
||
GROUP BY pw_isoinfo.WorkAreaId) AS finished_total_fjot ON finished_total_fjot.WorkAreaId = WorkArea.WorkAreaId
|
||
WHERE
|
||
(WorkArea.ProjectId=@projectId OR @projectId IS NULL ) AND
|
||
(WorkArea.WorkAreaId=@areano OR @AreaNo IS NULL ) AND
|
||
(WorkArea.UnitId=@unitno OR @UnitNo IS NULL) AND
|
||
(WorkArea.InstallationId=@installationId OR @installationId IS NULL) AND
|
||
(WorkArea.SupervisorUnitId=@supervisorUnitId OR @supervisorUnitId IS NULL)
|
||
ORDER BY Unit.UnitCode ,Installation.InstallationCode
|
||
|