483 lines
60 KiB
Transact-SQL
483 lines
60 KiB
Transact-SQL
USE [SGGLDB]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[sp_rpt_UnitWorkAreaQuality] Script Date: 2022/3/12 19:38:34 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
|
||
|
||
|
||
ALTER PROC [dbo].[sp_rpt_UnitWorkAreaQuality]
|
||
@unitNo nVARCHAR(400) = NULL,
|
||
@areaNo nVARCHAR(50) = NULL,
|
||
@installationId nVARCHAR(50) = NULL,
|
||
@date1 datetime = NULL,
|
||
@date2 datetime = NULL,
|
||
@ste_steeltype nvarchar(50) = NULL,
|
||
@projectId NVARCHAR(50) = NULL,
|
||
@supervisorUnitId NVARCHAR(50) = NULL,
|
||
@IsStandard NVARCHAR(50) = NULL
|
||
AS
|
||
/*单位工区质量分析*/
|
||
SELECT
|
||
WorkArea.ProjectId
|
||
,Unit.UnitCode AS bsu_unitcode --单位代码
|
||
,Unit.UnitName AS bsu_unitname --单位名称
|
||
,Installation.InstallationCode AS devicecode --装置代号
|
||
,Installation.InstallationName AS devicename --装置名称
|
||
,WorkArea.WorkAreaCode AS baw_areano --工区代号
|
||
|
||
,ISNULL(total_jot.total_jot,0) AS total_jot --总焊口
|
||
,ISNULL(total_sjot.total_sjot,0) AS total_sjot --预制总焊口数
|
||
,ISNULL(total_fjot.total_fjot,0) AS total_fjot --安装总焊口数
|
||
|
||
,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 --完成安装总焊口数
|
||
|
||
,ISNULL(current_total_film.current_total_film ,0) AS current_total_film --本期总拍片数
|
||
,ISNULL(current_total_film.current_pass_film,0) AS current_pass_film --本期合格片数
|
||
|
||
,CAST((CASE ISNULL(current_total_film.current_total_film,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(current_total_film.current_pass_film,0) /(1.0 * current_total_film.current_total_film)
|
||
END ) AS DECIMAL(19,3)) AS current_passreate --本期合格率
|
||
|
||
,ISNULL(current_point_total_film.current_point_total_film ,0) AS current_point_total_film -- 本期点口片数
|
||
,ISNULL(current_point_total_film.current_point_pass_film ,0) AS current_point_pass_film --本期点口合格片数
|
||
,CAST((CASE ISNULL(current_point_total_film.current_point_total_film,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(current_point_total_film.current_point_pass_film,0) /(1.0 * current_point_total_film.current_point_total_film)
|
||
END ) AS DECIMAL(19,3)) AS cuurent_point_passreate --本期点口合格率
|
||
|
||
,ISNULL(current_ext_total_film.current_ext_total_film,0) AS current_ext_total_film --本期扩透总片数
|
||
,ISNULL(current_ext_total_film.current_ext_pass_film,0) AS current_ext_pass_film --本期扩透合格片数
|
||
,CAST((CASE ISNULL(current_ext_total_film.current_ext_total_film,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(current_ext_total_film.current_ext_pass_film,0) /(1.0 * current_ext_total_film.current_ext_total_film)
|
||
END ) AS DECIMAL(19,3)) AS current_ext_passreate --本期扩透合格率
|
||
|
||
,ISNULL(current_trust_count_total.current_trust_count_total,0) AS current_trust_count_total --本期总委托数
|
||
,ISNULL(current_check_count_total.current_check_count_total,0) AS current_check_count_total --本期总检测数
|
||
|
||
,ISNULL(current_check_count_total.total_film ,0) AS total_film --总拍片数
|
||
,ISNULL(current_check_count_total.pass_film ,0) AS pass_film --合格片数
|
||
,CAST((CASE ISNULL(current_check_count_total.total_film,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(current_check_count_total.pass_film,0) /(1.0 * current_check_count_total.total_film)
|
||
END ) AS DECIMAL(19,3)) AS passreate --合格率
|
||
|
||
,ISNULL(point_total_film.point_total_film ,0) AS point_total_film -- 点口总片数
|
||
,ISNULL(point_total_film.point_pass_film ,0) AS point_pass_film --点口合格片数
|
||
,CAST((CASE ISNULL(point_total_film.point_total_film,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(point_total_film.point_pass_film,0) /(1.0 * point_total_film.point_total_film)
|
||
END ) AS DECIMAL(19,3)) AS point_passreate --点口合格率
|
||
|
||
,ISNULL(ext_total_film.ext_total_film ,0) AS ext_total_film --扩透总片数
|
||
,ISNULL(ext_total_film.ext_pass_film ,0) AS ext_pass_film --扩透合格片数
|
||
,CAST((CASE ISNULL(ext_total_film.ext_total_film,0)
|
||
WHEN 0 THEN 0
|
||
ELSE 1.0 * ISNULL(ext_total_film.ext_pass_film,0) /(1.0 * ext_total_film.ext_total_film)
|
||
END ) AS DECIMAL(19,3)) AS ext_passreate --扩透合格率
|
||
|
||
,ISNULL(trust_count_total.trust_count_total ,0) AS trust_count_total --委托总数
|
||
,ISNULL(point_count_total ,0) AS point_count_total ----点口总焊口数
|
||
,ISNULL(extend_count_total ,0) AS extend_count_total ----扩透总焊口数
|
||
,ISNULL(repair_count_total ,0) AS repair_count_total --返修焊口数
|
||
,ISNULL(trust_check_total.trust_check_total ,0) AS trust_check_total --检测口数
|
||
,tofd.donedin as tofd_total_donedin,
|
||
tofdpass.donedin as tofd_pass_donedin,
|
||
tofdnopass.donedin as tofd_no_pass_donedin,
|
||
CAST((CASE ISNULL(tofdpass.donedin,0) WHEN 0 THEN 0
|
||
ELSE 1.0 * (ISNULL(tofdpass.donedin , 0)-ISNULL(tofdnopass.donedin , 0)) /(1.0 * tofdpass.donedin) END) AS DECIMAL(19,3))
|
||
AS tofdpassrate,--tofd合格率
|
||
tdfw.donedin as tdfw_total_donedin,
|
||
tdfwpass.donedin as tdfw_pass_donedin,
|
||
tdfwnopass.donedin as tdfw_no_pass_donedin,
|
||
CAST((CASE ISNULL(tdfwpass.donedin,0) WHEN 0 THEN 0
|
||
ELSE 1.0 * (ISNULL(tdfwpass.donedin , 0)-ISNULL(tdfwnopass.donedin , 0)) /(1.0 * tdfwpass.donedin) END) AS DECIMAL(19,3))
|
||
AS tdfwpassrate,--tofd合格率
|
||
ut.donedin as ut_total_donedin,
|
||
utpass.donedin as ut_pass_donedin,
|
||
utnopass.donedin as ut_no_pass_donedin,
|
||
CAST((CASE ISNULL(ut.donedin,0) WHEN 0 THEN 0
|
||
ELSE 1.0 * (ISNULL(utpass.donedin , 0)-ISNULL(utnopass.donedin , 0)) /(1.0 * utpass.donedin) END) AS DECIMAL(19,3))
|
||
AS utpassrate --tofd合格率
|
||
FROM (select WorkAreaCode,WorkAreaId,UnitId,InstallationId,ProjectId,SupervisorUnitId
|
||
from ProjectData_WorkArea where (WorkAreaId = @areaNo or @areaNo is null)
|
||
and (SupervisorUnitId=@supervisorUnitId OR @supervisorUnitId IS NULL)) 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 ,isoinfo.WorkAreaId
|
||
FROM (select JOT_ID,ISO_ID,MaterialId from pw_jointinfo
|
||
where ProjectId = @projectId ) as jointinfo
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
|
||
GROUP BY isoinfo.WorkAreaId) AS total_jot ON total_jot.WorkAreaId = WorkArea.WorkAreaId
|
||
--预制总焊口数
|
||
LEFT JOIN (SELECT COUNT(*) total_sjot ,isoinfo.WorkAreaId
|
||
FROM (select JOT_ID,ISO_ID,MaterialId,WLO_Code from pw_jointinfo
|
||
where ProjectId = @projectId ) as jointinfo
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) AND WLO_Code='S'
|
||
|
||
GROUP BY isoinfo.WorkAreaId) AS total_sjot ON total_sjot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--安装总焊口数
|
||
LEFT JOIN (SELECT COUNT(*) total_fjot ,isoinfo.WorkAreaId
|
||
FROM (select JOT_ID,ISO_ID,MaterialId,WLO_Code from pw_jointinfo
|
||
where ProjectId = @projectId ) as jointinfo
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) AND WLO_Code='F'
|
||
GROUP BY isoinfo.WorkAreaId) AS total_fjot ON total_fjot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--完成总焊口
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_jot ,isoinfo.WorkAreaId
|
||
FROM (select JOT_ID,ISO_ID,MaterialId,DReportID from pw_jointinfo
|
||
where ProjectId = @projectId ) as jointinfo
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) AND DReportID is not null
|
||
GROUP BY isoinfo.WorkAreaId) AS finished_total_jot ON finished_total_jot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--完成预制总焊口数
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_sjot ,isoinfo.WorkAreaId
|
||
FROM (select JOT_ID,ISO_ID,MaterialId,DReportID,WLO_Code from pw_jointinfo
|
||
where ProjectId = @projectId ) as jointinfo
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) AND DReportID is not null AND WLO_Code='S'
|
||
GROUP BY isoinfo.WorkAreaId) AS finished_total_sjot ON finished_total_sjot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--完成安装总焊口数
|
||
LEFT JOIN (SELECT COUNT(*) finished_total_fjot ,isoinfo.WorkAreaId
|
||
FROM (select JOT_ID,ISO_ID,MaterialId,DReportID,WLO_Code from pw_jointinfo
|
||
where ProjectId = @projectId ) as jointinfo
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) AND DReportID is not null AND WLO_Code='F'
|
||
GROUP BY isoinfo.WorkAreaId) AS finished_total_fjot ON finished_total_fjot.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--本期总拍片数
|
||
LEFT JOIN (SELECT SUM(cht_totalfilm) AS current_total_film
|
||
,SUM(cht_passfilm) AS current_pass_film
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CHT_CheckID from CH_Check
|
||
where ProjectId = @projectId
|
||
and (InstallationId=@installationId OR @installationId IS NULL)
|
||
and (UnitId=@unitNo OR @unitNo IS NULL)
|
||
AND (cht_checkdate >= @date1 OR @date1 IS NULL) and (cht_checkdate <= @date2 OR @date2 IS NULL))as Checks
|
||
LEFT JOIN CH_CheckItem ON ch_checkitem.CHT_CheckID = Checks.CHT_CheckID
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_checkitem.CHT_CheckID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
GROUP BY isoinfo.WorkAreaId) AS current_total_film ON current_total_film.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--本期点口片数 --本期点口合格片数
|
||
LEFT JOIN (SELECT SUM(cht_totalfilm) AS current_point_total_film
|
||
,SUM(cht_passfilm) AS current_point_pass_film
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CHT_CheckID from CH_Check
|
||
where ProjectId = @projectId
|
||
and (InstallationId=@installationId OR @installationId IS NULL)
|
||
and (UnitId=@unitNo OR @unitNo IS NULL)
|
||
AND (cht_checkdate >= @date1 OR @date1 IS NULL) and (cht_checkdate <= @date2 OR @date2 IS NULL))as Checks
|
||
LEFT JOIN CH_CheckItem ON ch_checkitem.CHT_CheckID = Checks.CHT_CheckID
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_checkitem.CHT_CheckID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
AND JOT_JointStatus='101'
|
||
GROUP BY isoinfo.WorkAreaId) AS current_point_total_film ON current_point_total_film.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--本期扩透片数 --本期扩透合格片数
|
||
LEFT JOIN (SELECT SUM(cht_totalfilm) AS current_ext_total_film
|
||
,SUM(cht_passfilm) AS current_ext_pass_film
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CHT_CheckID from CH_Check
|
||
where ProjectId = @projectId
|
||
and (InstallationId=@installationId OR @installationId IS NULL)
|
||
and (UnitId=@unitNo OR @unitNo IS NULL)
|
||
AND (cht_checkdate >= @date1 OR @date1 IS NULL) and (cht_checkdate <= @date2 OR @date2 IS NULL))as Checks
|
||
LEFT JOIN CH_CheckItem ON ch_checkitem.CHT_CheckID = Checks.CHT_CheckID
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_checkitem.CHT_CheckID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = jointinfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
AND JOT_JointStatus='102'
|
||
GROUP BY isoinfo.WorkAreaId) AS current_ext_total_film ON current_ext_total_film.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--本期总委托数
|
||
LEFT JOIN (SELECT COUNT(*) AS current_trust_count_total
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CH_TrustID from CH_Trust
|
||
where ProjectId =@projectId AND (ch_trustdate >= @date1 OR @date1 IS NULL)
|
||
and (ch_trustdate <= @date2 OR @date2 IS NULL)
|
||
and (CH_TrustUnit = @unitNo OR @unitNo IS NULL)) as ch_trust
|
||
LEFT JOIN CH_TrustItem ON ch_trust.ch_trustid = ch_trustitem.ch_trustid
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_trustitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND(WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
GROUP BY isoinfo.WorkAreaId) AS current_trust_count_total ON current_trust_count_total.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--本期总检测数
|
||
LEFT JOIN (SELECT COUNT(*) AS current_check_count_total
|
||
,SUM(cht_totalfilm) AS total_film
|
||
,SUM(cht_passfilm) AS pass_film
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CHT_CheckID from CH_Check
|
||
where ProjectId = @projectId
|
||
and (InstallationId=@installationId OR @installationId IS NULL)
|
||
and (UnitId=@unitNo OR @unitNo IS NULL)
|
||
AND (cht_checkdate >= @date1 OR @date1 IS NULL) and (cht_checkdate <= @date2 OR @date2 IS NULL))as Checks
|
||
LEFT JOIN CH_CheckItem ON ch_checkitem.CHT_CheckID = Checks.CHT_CheckID
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_checkitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
GROUP BY isoinfo.WorkAreaId) AS current_check_count_total ON current_check_count_total.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--点口总片数 --点口合格片数 -- 点口合格率
|
||
LEFT JOIN (SELECT SUM(cht_totalfilm) AS point_total_film
|
||
,SUM(cht_passfilm) AS point_pass_film
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CHT_CheckID from CH_Check
|
||
where ProjectId = @projectId
|
||
and (InstallationId=@installationId OR @installationId IS NULL)
|
||
and (UnitId=@unitNo OR @unitNo IS NULL)
|
||
AND (cht_checkdate >= @date1 OR @date1 IS NULL) and (cht_checkdate <= @date2 OR @date2 IS NULL))as Checks
|
||
LEFT JOIN CH_CheckItem ON ch_checkitem.CHT_CheckID = Checks.CHT_CheckID
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_checkitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
AND JOT_JointStatus='101'
|
||
GROUP BY isoinfo.WorkAreaId) AS point_total_film ON point_total_film.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--扩透总片数 --扩透合格片数 --扩透合格率
|
||
LEFT JOIN (SELECT SUM(cht_totalfilm) AS ext_total_film
|
||
,SUM(cht_passfilm) AS ext_pass_film
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CHT_CheckID from CH_Check
|
||
where ProjectId = @projectId
|
||
and (InstallationId=@installationId OR @installationId IS NULL)
|
||
and (UnitId=@unitNo OR @unitNo IS NULL)
|
||
AND (cht_checkdate >= @date1 OR @date1 IS NULL) and (cht_checkdate <= @date2 OR @date2 IS NULL))as Checks
|
||
LEFT JOIN CH_CheckItem ON ch_checkitem.CHT_CheckID = Checks.CHT_CheckID
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_checkitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
AND JOT_JointStatus='102'
|
||
GROUP BY isoinfo.WorkAreaId) AS ext_total_film ON ext_total_film.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
|
||
--委托总数
|
||
LEFT JOIN (SELECT COUNT(*) AS trust_count_total
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CH_TrustID from CH_Trust
|
||
where ProjectId =@projectId AND (ch_trustdate >= @date1 OR @date1 IS NULL)
|
||
and (ch_trustdate <= @date2 OR @date2 IS NULL)
|
||
and (CH_TrustUnit = @unitNo OR @unitNo IS NULL)) as ch_trust
|
||
LEFT JOIN CH_TrustItem ON ch_trust.ch_trustid = ch_trustitem.ch_trustid
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_trustitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
GROUP BY isoinfo.WorkAreaId) AS trust_count_total ON trust_count_total.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
----点口总焊口数
|
||
LEFT JOIN (SELECT COUNT(*) point_count_total ,isoinfo.WorkAreaId
|
||
FROM (select CH_TrustID from CH_Trust
|
||
where ProjectId =@projectId AND (ch_trustdate >= @date1 OR @date1 IS NULL)
|
||
and (ch_trustdate <= @date2 OR @date2 IS NULL)
|
||
and (CH_TrustUnit = @unitNo OR @unitNo IS NULL)) as ch_trust
|
||
LEFT JOIN CH_TrustItem ON ch_trust.ch_trustid = ch_trustitem.ch_trustid
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_trustitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
AND JOT_JointStatus='101'
|
||
GROUP BY isoinfo.WorkAreaId) AS point_count_total ON point_count_total.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
----扩透总焊口数
|
||
LEFT JOIN (SELECT COUNT(*) extend_count_total ,isoinfo.WorkAreaId
|
||
FROM (select CH_TrustID from CH_Trust
|
||
where ProjectId =@projectId AND (ch_trustdate >= @date1 OR @date1 IS NULL)
|
||
and (ch_trustdate <= @date2 OR @date2 IS NULL)
|
||
and (CH_TrustUnit = @unitNo OR @unitNo IS NULL)) as ch_trust
|
||
LEFT JOIN CH_TrustItem ON ch_trust.ch_trustid = ch_trustitem.ch_trustid
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_trustitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
AND JOT_JointStatus='102'
|
||
GROUP BY isoinfo.WorkAreaId) AS extend_count_total ON extend_count_total.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--返修焊口数
|
||
LEFT JOIN (SELECT COUNT(*) AS repair_count_total --返修口数
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CH_TrustID,CH_TrustType from CH_Trust
|
||
where ProjectId =@projectId AND (ch_trustdate >= @date1 OR @date1 IS NULL)
|
||
and (ch_trustdate <= @date2 OR @date2 IS NULL)
|
||
and (CH_TrustUnit = @unitNo OR @unitNo IS NULL)) as Trust
|
||
LEFT JOIN CH_TrustItem ON Trust.ch_trustid = ch_trustitem.ch_trustid
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_trustitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND(WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
AND Trust.CH_TrustType='2' and JOT_JointStatus !='102'
|
||
GROUP BY isoinfo.WorkAreaId) AS repair_count_total ON repair_count_total.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--检测口数
|
||
LEFT JOIN (SELECT COUNT(*) AS trust_check_total
|
||
,isoinfo.WorkAreaId
|
||
FROM (select CHT_CheckID from CH_Check
|
||
where ProjectId = @projectId
|
||
and (InstallationId=@installationId OR @installationId IS NULL)
|
||
and (UnitId=@unitNo OR @unitNo IS NULL)
|
||
AND (cht_checkdate >= @date1 OR @date1 IS NULL) and (cht_checkdate <= @date2 OR @date2 IS NULL))as Checks
|
||
LEFT JOIN CH_CheckItem ON ch_checkitem.CHT_CheckID = Checks.CHT_CheckID
|
||
LEFT JOIN PW_JointInfo as JointInfo ON JointInfo.JOT_ID = ch_checkitem.JOT_ID
|
||
LEFT JOIN (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON jointinfo.ISO_ID = isoinfo.ISO_ID
|
||
LEFT JOIN Base_Material ON Base_Material.MaterialId = JointInfo.MaterialId
|
||
WHERE (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)
|
||
GROUP BY isoinfo.WorkAreaId) AS trust_check_total ON trust_check_total.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
|
||
--总达因 --TOFD
|
||
LEFT JOIN (
|
||
SELECT SUM(jot_donedin) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_NDTMethod in ('1b5a29de-b9dd-40af-9176-3f0156e1268b','4ba5ff2a-0a8a-4f60-b549-b2604f72c43e','7d28665b-21fc-45a0-affd-d7b28b9791e1')
|
||
group by isoinfo.WorkAreaId
|
||
) as tofd on tofd.WorkAreaId = WorkArea.WorkAreaId
|
||
--总达因 --合格--TOFD
|
||
LEFT JOIN (
|
||
SELECT SUM(jot_donedin) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join CH_CheckItem d on a.JOT_ID = d.JOT_ID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_NDTMethod in ('1b5a29de-b9dd-40af-9176-3f0156e1268b','4ba5ff2a-0a8a-4f60-b549-b2604f72c43e','7d28665b-21fc-45a0-affd-d7b28b9791e1') and d.CHT_CheckResult='合格'
|
||
group by isoinfo.WorkAreaId
|
||
) as tofdpass on tofdpass.WorkAreaId = WorkArea.WorkAreaId
|
||
--总达因 --二次委托--TOFD
|
||
LEFT JOIN (
|
||
SELECT SUM(DefectLength) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join CH_CheckItem d on a.JOT_ID = d.JOT_ID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_TrustType='2' and a.JOT_JointStatus !='102' and c.CH_NDTMethod in ('1b5a29de-b9dd-40af-9176-3f0156e1268b','4ba5ff2a-0a8a-4f60-b549-b2604f72c43e','7d28665b-21fc-45a0-affd-d7b28b9791e1') and d.CHT_CheckResult='合格'
|
||
group by isoinfo.WorkAreaId
|
||
) as tofdnopass on tofdnopass.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
|
||
|
||
--总达因 --TDFW
|
||
LEFT JOIN (
|
||
SELECT SUM(jot_donedin) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_NDTMethod in ('a5e9c1bb-1fad-4e65-82bf-03003cc56ab41')
|
||
group by isoinfo.WorkAreaId
|
||
) as tdfw on tdfw.WorkAreaId = WorkArea.WorkAreaId
|
||
--总达因 --合格--TDFW
|
||
LEFT JOIN (
|
||
SELECT SUM(jot_donedin) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join CH_CheckItem d on a.JOT_ID = d.JOT_ID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_NDTMethod in ('a5e9c1bb-1fad-4e65-82bf-03003cc56ab41') and d.CHT_CheckResult='合格'
|
||
group by isoinfo.WorkAreaId
|
||
) as tdfwpass on tdfwpass.WorkAreaId = WorkArea.WorkAreaId
|
||
--总达因 --合格--TDFW
|
||
LEFT JOIN (
|
||
SELECT SUM(DefectLength) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join CH_CheckItem d on a.JOT_ID = d.JOT_ID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_TrustType='2' and a.JOT_JointStatus !='102' and c.CH_NDTMethod in ('a5e9c1bb-1fad-4e65-82bf-03003cc56ab41') and d.CHT_CheckResult='合格'
|
||
group by isoinfo.WorkAreaId
|
||
) as tdfwnopass on tdfwnopass.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--总达因 --UT
|
||
LEFT JOIN (
|
||
SELECT SUM(jot_donedin) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_NDTMethod in ('a5e9c1bb-1fad-4e65-82bf-03003cc56ab4')
|
||
group by isoinfo.WorkAreaId
|
||
) as ut on ut.WorkAreaId = WorkArea.WorkAreaId
|
||
--总达因 --合格--UT
|
||
LEFT JOIN (
|
||
SELECT SUM(jot_donedin) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join CH_CheckItem d on a.JOT_ID = d.JOT_ID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_NDTMethod in ('a5e9c1bb-1fad-4e65-82bf-03003cc56ab4') and d.CHT_CheckResult='合格'
|
||
group by isoinfo.WorkAreaId
|
||
) as utpass on utpass.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
--总达因 --合格--UT
|
||
LEFT JOIN (
|
||
SELECT SUM(DefectLength) as donedin, isoinfo.WorkAreaId from PW_JointInfo a left join CH_TrustItem b on a.JOT_ID = b.JOT_ID
|
||
left join CH_Trust c on b.CH_TrustID = c.CH_TrustID
|
||
left join CH_CheckItem d on a.JOT_ID = d.JOT_ID
|
||
left join (select iso_id,WorkAreaId from pw_isoinfo
|
||
where (pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) AND (WorkAreaId = @areaNo or @areaNo is null) and ProjectId = @projectId ) as isoinfo ON a.ISO_ID = isoinfo.ISO_ID
|
||
where c.CH_TrustType='2' and a.JOT_JointStatus !='102' and c.CH_NDTMethod in ('a5e9c1bb-1fad-4e65-82bf-03003cc56ab4') and d.CHT_CheckResult='合格'
|
||
group by isoinfo.WorkAreaId
|
||
) as utnopass on utnopass.WorkAreaId = WorkArea.WorkAreaId
|
||
|
||
|
||
|
||
WHERE (WorkArea.ProjectId = @projectId ) 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
|
||
|
||
|
||
|
||
|
||
|
||
|