USE [SGGLDB_TCCC]
GO
/****** Object:  StoredProcedure [dbo].[sp_rpt_UnitWorkareaAnalyze]    Script Date: 2021/9/6 19:03:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER  PROC [dbo].[sp_rpt_UnitWorkareaAnalyze]
          @UnitNo  nVARCHAR(400) = NULL,
          @AreaNo  nVARCHAR(50) = NULL,
          @installationId nVARCHAR(50) = NULL,      
          @ste_steeltype nvarchar(50) = NULL,
		  @startTime datetime = NULL,
          @endTime datetime = NULL,
          @projectId NVARCHAR(50) = NULL,
		  @supervisorUnitId NVARCHAR(50) = NULL,
		  @IsStandard NVARCHAR(50) = NULL
       
AS
/*单位工区进度分析*/
SELECT 
		WorkArea.ProjectId,
		WorkArea.WorkAreaCode  AS baw_areano,--工区代号
		Unit.UnitCode AS bsu_unitcode,--单位代码
		Unit.UnitName AS bsu_unitname, --单位名称
		Installation.InstallationCode, --装置代号
		Installation.InstallationName, --装置名称
		--   最近焊期
		ISNULL(total_jot.total_jot,0) AS total_jot, --总焊口
		--- 累计数 start
		ISNULL(total_sjot.total_sjot,0) AS  total_sjot, --预制总焊口数
		ISNULL(total_fjot.total_fjot,0) AS total_fjot, --安装总焊口数
		ISNULL(cut_total_jot.cut_total_jot,0) AS cut_total_jot, --切除焊口
		CAST(ISNULL(total_jot.total_din,0) AS DECIMAL(19,2)) AS total_din,--总达因
		CAST(ISNULL(total_sjot.total_Sdin,0) AS DECIMAL(19,2)) AS total_Sdin, --预制总达因
		CAST(ISNULL(total_fjot.total_Fdin,0) AS DECIMAL(19,2)) AS total_Fdin, --安装总达因
		ISNULL(finished_total_jot_bq.finished_total_jot_bq,0) AS finished_total_jot_bq, --本期完成焊口数
		ISNULL(finished_total_sjot_bq.finished_total_sjot_bq,0) AS finished_total_sjot_bq, --本期完成预制焊口数
		ISNULL(finished_total_fjot_bq.finished_total_fjot_bq,0) AS finished_total_fjot_bq, --本期完成安装焊口数
	
	CAST((CASE ISNULL(total_jot.total_jot ,0) 
		WHEN 0 THEN 0
		ELSE  1.0 * ISNULL(finished_total_jot_bq.finished_total_jot_bq,0) / (1.0 * total_jot.total_jot)
		END) AS DECIMAL(19,3)) AS finisedrate_bq,	--本期完成比例
		
	CAST((CASE ISNULL(total_jot.total_jot,0) 
		WHEN 0 THEN 0
		ELSE 1.0 * ISNULL(finished_total_sjot_bq.finished_total_sjot_bq,0)/ (1.0 *total_jot.total_jot)
		END) AS DECIMAL(19,3)) AS finisedrate_s_bq,	-- 本期预制完成比例
		
	CAST((CASE ISNULL(total_jot.total_jot,0) 
		WHEN 0 THEN 0
		ELSE 1.0 * ISNULL(finished_total_fjot_bq.finished_total_fjot_bq,0)/ (1.0 *total_jot.total_jot)
		END) AS DECIMAL(19,3)) AS finisedrate_f_bq,   -- 本期安装完成比例
		
	CAST(ISNULL(finished_total_jot_bq.finished_total_din_bq ,0) AS DECIMAL(19,2)) AS finished_total_din_bq, --本期完成达因
	CAST(ISNULL(finished_total_sjot_bq.finished_total_Sdin_bq,0) AS DECIMAL(19,2)) AS finished_total_Sdin_bq,----本期完成预制达因
	CAST(ISNULL(finished_total_fjot_bq.finished_total_Fdin_bq,0) AS DECIMAL(19,2)) AS finished_total_Fdin_bq, ----本期完成安装达因	
	
	CAST((CASE ISNULL(total_jot.total_din,0) 
		WHEN 0 THEN 0
		ELSE 1.0 * ISNULL(finished_total_jot_bq.finished_total_din_bq ,0)/ (1.0 * total_jot.total_din)
		END) AS DECIMAL(19,3)) AS finisedrate_din_bq,  --本期完成达因比例
	CAST((CASE ISNULL(total_jot.total_din,0) 
		WHEN 0 THEN 0
		ELSE 1.0 * ISNULL(finished_total_sjot_bq.finished_total_Sdin_bq,0) /(1.0 * total_jot.total_din)
		END) AS DECIMAL(19,3)) AS	finisedrate_din_s_bq,  -- 本期完成预制达因比例
	CAST((CASE ISNULL(total_jot.total_din,0) 
		WHEN 0 THEN 0
		ELSE 1.0 * ISNULL(finished_total_fjot_bq.finished_total_Fdin_bq,0)/  (1.0 * total_jot.total_din)
		END) AS DECIMAL(19,3)) AS	finisedrate_din_f_bq,   -- 本期完成安装达因比例		

	ISNULL(finished_total_jot.finished_total_jot,0) AS finished_total_jot,  --完成焊口		
	ISNULL(finished_total_sjot.finished_total_sjot,0) AS finished_total_sjot, --完成预制焊口
	ISNULL(finished_total_fjot.finished_total_fjot,0) AS finished_total_fjot,--完成安装焊口

	CAST((CASE ISNULL(total_jot.total_jot ,0)
			WHEN 0 THEN 0
			ELSE 1.0 * ISNULL(finished_total_jot.finished_total_jot,0)/ (1.0 * total_jot.total_jot)
			END) AS DECIMAL(19,3)) AS finisedrate,  --完成比例
	CAST((CASE ISNULL(total_jot.total_jot,0) 
			WHEN 0 THEN 0
			ELSE 1.0 * ISNULL(finished_total_fjot.finished_total_fjot,0)  / (1.0 * total_jot.total_jot)
			END) AS DECIMAL(19,3)) AS finisedrate_f,  -- 安装完成比例
	CAST((CASE ISNULL(total_jot.total_jot,0) 
			WHEN 0 THEN 0
			ELSE 1.0 * ISNULL(finished_total_sjot.finished_total_sjot,0)  / (1.0 * total_jot.total_jot) 
			END) AS DECIMAL(19,3)) AS finisedrate_s, -- 预制完成比例
	
	CAST(ISNULL(total_jot.finished_total_din,0)  AS DECIMAL(19,2)) AS finished_total_din,	 --完成达因
	CAST(ISNULL(total_sjot.finished_total_Sdin,0) AS DECIMAL(19,2)) AS finished_total_Sdin,--完成预制达因
	CAST(ISNULL(total_fjot.finished_total_Fdin,0) AS DECIMAL(19,2)) AS finished_total_Fdin,  --完成安装达因	
	
	CAST((CASE ISNULL(total_jot.total_din,0)
		WHEN 0 THEN 0
		ELSE 1.0 * ISNULL(total_jot.finished_total_din,0)  / (1.0 * total_jot.total_din)
		END) AS DECIMAL(19,3)) AS finisedrate_din, ---完成达因比例
		
	CAST((CASE ISNULL(total_jot.total_din,0) 
		WHEN 0 THEN 0
		ELSE 1.0 * ISNULL(total_sjot.finished_total_Sdin,0) / (1.0 *total_jot.total_din) 
		END) AS DECIMAL(19,3)) AS finisedrate_din_s,  -- 完成预制达因比例
		
	CAST((CASE ISNULL(total_jot.total_din,0)
		WHEN 0 THEN 0
		ELSE 1.0 * ISNULL(total_fjot.finished_total_Fdin,0) / (1.0 *total_jot.total_din) 
		END) AS DECIMAL(19,3)) AS finisedrate_din_f   -- 完成安装达因比例
		
FROM ProjectData_WorkArea AS  WorkArea
LEFT JOIN Base_Unit AS Unit ON WorkArea.UnitId =Unit.UnitId
LEFT JOIN Project_Installation AS Installation ON Installation.InstallationId = WorkArea.InstallationId
LEFT JOIN (SELECT COUNT(*) total_jot ,pw_isoinfo.WorkAreaId ,SUM(JOT_Size) AS total_din
			,SUM(JOT_DoneDin) AS finished_total_din
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null)  AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS total_jot ON total_jot.WorkAreaId = WorkArea.WorkAreaId
			
LEFT JOIN (SELECT COUNT(*) total_sjot ,pw_isoinfo.WorkAreaId, SUM(JOT_Size) AS total_Sdin
			,SUM(JOT_DoneDin) AS finished_total_Sdin
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and  pw_jointinfo.WLO_Code='S'  AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS total_sjot ON total_sjot.WorkAreaId = WorkArea.WorkAreaId			

LEFT JOIN (SELECT COUNT(*) total_fjot ,pw_isoinfo.WorkAreaId, SUM(JOT_Size)	AS total_Fdin
		,SUM(JOT_DoneDin) AS finished_total_Fdin
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and  pw_jointinfo.WLO_Code='F'  AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS total_fjot ON total_fjot.WorkAreaId = WorkArea.WorkAreaId	

LEFT JOIN (SELECT COUNT(*) cut_total_jot ,pw_isoinfo.WorkAreaId	
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and  pw_jointinfo.JOT_JointStatus='104' AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL) 
			GROUP BY pw_isoinfo.WorkAreaId)	AS cut_total_jot ON cut_total_jot.WorkAreaId = WorkArea.WorkAreaId	

	LEFT JOIN (SELECT COUNT(*) finished_total_jot_bq ,pw_isoinfo.WorkAreaId ,SUM(JOT_DoneDin) AS finished_total_din_bq	
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			LEFT JOIN BO_WeldReportMain ON BO_WeldReportMain.dreportid = pw_jointinfo.dreportid 
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.DReportID is not null
					and (jot_welddate >= @startTime OR @startTime IS NULL) and (jot_welddate <=@endTime OR @endTime IS NULL) AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS finished_total_jot_bq ON finished_total_jot_bq.WorkAreaId = WorkArea.WorkAreaId	

LEFT JOIN (SELECT COUNT(*) finished_total_sjot_bq ,pw_isoinfo.WorkAreaId,SUM(JOT_DoneDin) AS finished_total_Sdin_bq
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			LEFT JOIN BO_WeldReportMain ON BO_WeldReportMain.dreportid = pw_jointinfo.dreportid 
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.DReportID is not null
					and (jot_welddate >= @startTime OR @startTime IS NULL) and (jot_welddate <=@endTime OR @endTime IS NULL)
					 and WLO_Code='S'  AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS finished_total_sjot_bq ON finished_total_sjot_bq.WorkAreaId = WorkArea.WorkAreaId
			
LEFT JOIN (SELECT COUNT(*) finished_total_fjot_bq ,pw_isoinfo.WorkAreaId , SUM(JOT_DoneDin) AS finished_total_Fdin_bq
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			LEFT JOIN BO_WeldReportMain ON BO_WeldReportMain.dreportid = pw_jointinfo.dreportid 
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and pw_jointinfo.DReportID is not null
					and (jot_welddate >= @startTime OR @startTime IS NULL) and (jot_welddate <=@endTime OR @endTime IS NULL)
					 and WLO_Code='F'  AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS finished_total_fjot_bq ON finished_total_fjot_bq.WorkAreaId = WorkArea.WorkAreaId													

LEFT JOIN (SELECT COUNT(*) finished_total_jot ,pw_isoinfo.WorkAreaId 
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and DReportID is not null  AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS finished_total_jot ON finished_total_jot.WorkAreaId = WorkArea.WorkAreaId
			
LEFT JOIN (SELECT COUNT(*) finished_total_sjot ,pw_isoinfo.WorkAreaId 
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and DReportID is not null 
			 and WLO_Code='S'  AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS finished_total_sjot ON finished_total_sjot.WorkAreaId = WorkArea.WorkAreaId			

LEFT JOIN (SELECT COUNT(*) finished_total_fjot ,pw_isoinfo.WorkAreaId 
			FROM pw_jointinfo 
			LEFT JOIN pw_isoinfo  ON pw_jointinfo.ISO_ID = pw_isoinfo.ISO_ID
			LEFT JOIN Base_Material ON Base_Material.MaterialId = pw_jointinfo.MaterialId
			WHERE  (Base_Material.SteelType =@ste_steeltype or @ste_steeltype is null) and DReportID is not null 
			 and WLO_Code='F'  AND
					(pw_isoinfo.Is_Standard=@IsStandard OR @IsStandard IS NULL)
			GROUP BY pw_isoinfo.WorkAreaId)	AS finished_total_fjot ON finished_total_fjot.WorkAreaId = WorkArea.WorkAreaId	
WHERE
	(WorkArea.ProjectId=@projectId OR @projectId IS NULL ) AND
	(WorkArea.WorkAreaId=@areano OR @AreaNo IS NULL ) AND 
	(WorkArea.UnitId=@unitno OR @UnitNo IS NULL) AND
	(WorkArea.InstallationId=@installationId OR @installationId IS NULL) AND
	(WorkArea.SupervisorUnitId=@supervisorUnitId OR @supervisorUnitId IS NULL)
ORDER BY Unit.UnitCode ,Installation.InstallationCode