368 lines
45 KiB
Transact-SQL
368 lines
45 KiB
Transact-SQL
|
||
/****** Object: StoredProcedure [dbo].[sp_rpt_UnitWorkAreaQuality] Script Date: 2021/9/6 19:15:46 ******/
|
||
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 --检测口数
|
||
|
||
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
|
||
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
|
||
|
||
|
||
|
||
|
||
|
||
|