--drop PROCEDURE [dbo].[Sp_HJGL_ProductionPlanStatistics]
CREATE PROCEDURE [dbo].[Sp_HJGL_ProductionPlanStatistics]
 @projectId nvarchar(50)=null
AS
BEGIN
 SET NOCOUNT ON; -- 减少网络流量，提高性能
 
 -- 使用CTE预先过滤和聚合数据
 WITH PipelineFiltered AS (
  SELECT UnitWorkId, FlowingSection, PipelineId
  FROM HJGL_Pipeline 
  WHERE PipeArea='1' 
    AND FlowingSection IS NOT NULL 
    AND FlowingSection != ''
    AND (@projectId IS NULL OR UnitWorkId IN (
      SELECT UnitWorkId FROM WBS_UnitWork WHERE ProjectId = @projectId
    ))
 ),
 
 -- 工厂预制总达因
 TotalDiaCTE AS (
  SELECT p.UnitWorkId, p.FlowingSection, SUM(wj.Size) as TotalDia
  FROM HJGL_WeldJoint wj
  INNER JOIN PipelineFiltered p ON p.PipelineId = wj.PipelineId
  WHERE wj.JointAttribute='预制口'
  GROUP BY p.UnitWorkId, p.FlowingSection
 ),
 
 -- 当日完成工作量
 CurrentDayCompletedCTE AS (
  SELECT p.UnitWorkId, p.FlowingSection, SUM(wj.Size) as CurrentDayCompletedDia
  FROM HJGL_WeldJoint wj
  INNER JOIN PipelineFiltered p ON p.PipelineId = wj.PipelineId
  INNER JOIN HJGL_WeldingDaily wd ON wd.WeldingDailyId = wj.WeldingDailyId
  WHERE wj.JointAttribute='预制口'
    AND wj.WeldingDailyId IS NOT NULL
    AND wd.WeldingDate >= CAST(GETDATE() AS DATE)  -- 优化日期比较，使用日期范围
    AND wd.WeldingDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
  GROUP BY p.UnitWorkId, p.FlowingSection
 ),
 
 -- 累计已完成量
 TotalCompletedCTE AS (
  SELECT p.UnitWorkId, p.FlowingSection, SUM(wj.Size) as totalCompletedDia
  FROM HJGL_WeldJoint wj
  INNER JOIN PipelineFiltered p ON p.PipelineId = wj.PipelineId
  WHERE wj.JointAttribute='预制口'
    AND wj.WeldingDailyId IS NOT NULL
  GROUP BY p.UnitWorkId, p.FlowingSection
 ),
 
 -- 当前焊工数量
 WelderCountCTE AS (
  SELECT p.UnitWorkId, p.FlowingSection, 
         COUNT(DISTINCT pp.PersonId) as WelderCount  -- 使用DISTINCT避免重复计数
  FROM Person_Persons pp
  INNER JOIN HJGL_WeldJoint wj ON wj.BackingWelderId = pp.PersonId
  INNER JOIN PipelineFiltered p ON p.PipelineId = wj.PipelineId
  WHERE wj.JointAttribute='预制口'
    AND wj.WeldingDailyId IS NOT NULL
  GROUP BY p.UnitWorkId, p.FlowingSection
 ),
 
 -- 预警焊工数量
 WarningWelderCountCTE AS (
  SELECT p.UnitWorkId, p.FlowingSection, 
         COUNT(DISTINCT pp.PersonId) as WarningWelderCount
  FROM Person_Persons pp
  INNER JOIN HJGL_WeldJoint wj ON wj.BackingWelderId = pp.PersonId
  INNER JOIN PipelineFiltered p ON p.PipelineId = wj.PipelineId
  INNER JOIN Welder_WelderQualify wq ON wq.WelderId = pp.PersonId
  WHERE wj.JointAttribute='预制口'
    AND wj.WeldingDailyId IS NOT NULL
    AND wq.LimitDate < GETDATE()  -- 焊工资格过期预警
  GROUP BY p.UnitWorkId, p.FlowingSection
 )
 
 SELECT DISTINCT 
  unitWork.UnitWorkId,
  unitWork.UnitWorkCode,
  unitWork.UnitWorkName,
  unitWork.ProjectId,
  pipeline.FlowingSection,
  isnull(total.TotalDia,0) as TotalDia,--工厂预制总达因
  p.PlanStartDate,--计划开始日期
  p.PlanEndDate,--计划完成日期
  p.Days as TotalDays,--总天数
  (case when p.PlanEndDate>= getdate() then (p.Days-(DATEDIFF(day,p.PlanStartDate,GETDATE()))) else '' end) as RemainingDays,--剩余天数
  (case when p.Days>0 then cast(isnull(total.TotalDia,0)/p.Days as decimal(18,2)) else 0 end) as AvgDayCompleteDia, --平均每日应完成工作量
  ISNULL(currentDay.CurrentDayCompletedDia,0) as CurrentDayCompletedDia, --当日完成工作量
  cast(case when (case when p.PlanEndDate>= getdate() then (p.Days-(DATEDIFF(day,p.PlanStartDate,GETDATE()))) else 0 end)>0 then
  (isnull(total.TotalDia,0)-isnull(totalCompleted.totalCompletedDia,0))/ (case when p.PlanEndDate>= getdate() then (p.Days-(DATEDIFF(day,p.PlanStartDate,GETDATE()))) else 0 end) else 0 end as decimal(18,2)) as NextDayComplete, --次日应完成量
  isnull(totalCompleted.totalCompletedDia,0) as totalCompletedDia, --累计已完成量
  cast(cast((case when isnull(total.TotalDia,0)>0 then 
  (isnull(totalCompleted.totalCompletedDia,0) / isnull(total.TotalDia,0)*100) else 0 end) as decimal(18,2)) as varchar(10))+'%' as CompletedRate, --已完成百分比
  welder.WelderCount, --当前焊工数量
  warningWelder.WarningWelderCount --预警焊工数量
 FROM WBS_UnitWork AS unitWork
 INNER JOIN PipelineFiltered pipeline ON pipeline.UnitWorkId = unitWork.UnitWorkId  -- 使用INNER JOIN，因为pipeline数据必须存在
 LEFT JOIN HJGL_ProductionSchedulingPlan p ON p.PipelineId = unitWork.UnitWorkId AND p.FlowNum = pipeline.FlowingSection
 --工厂预制总达因
 LEFT JOIN TotalDiaCTE total ON total.UnitWorkId = unitWork.UnitWorkId AND total.FlowingSection = pipeline.FlowingSection
 --当日完成工作量
 LEFT JOIN CurrentDayCompletedCTE currentDay ON currentDay.UnitWorkId = unitWork.UnitWorkId AND currentDay.FlowingSection = pipeline.FlowingSection
 --累计已完成量
 LEFT JOIN TotalCompletedCTE totalCompleted ON totalCompleted.UnitWorkId = unitWork.UnitWorkId AND totalCompleted.FlowingSection = pipeline.FlowingSection
 --当前焊工数量
 LEFT JOIN WelderCountCTE welder ON welder.UnitWorkId = unitWork.UnitWorkId AND welder.FlowingSection = pipeline.FlowingSection
 --预警焊工数量
 LEFT JOIN WarningWelderCountCTE warningWelder ON warningWelder.UnitWorkId = unitWork.UnitWorkId AND warningWelder.FlowingSection = pipeline.FlowingSection
 WHERE (@projectId IS NULL OR unitWork.ProjectId = @projectId)
   AND pipeline.FlowingSection IS NOT NULL;
 
END