/****** 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