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