ALTER TABLE [dbo].[HJGL_ProductionSchedulingPlan] DROP CONSTRAINT [FK_HJGL_ProductionSchedulingPlan_HJGL_Pipeline]
GO

ALTER TABLE [dbo].[HJGL_ProductionSchedulingPlan]  WITH CHECK ADD  CONSTRAINT [FK_HJGL_ProductionSchedulingPlan_WBS_UnitWork] FOREIGN KEY([PipelineId])
REFERENCES [dbo].[WBS_UnitWork] ([UnitWorkId])
GO

ALTER TABLE [dbo].[HJGL_ProductionSchedulingPlan] CHECK CONSTRAINT [FK_HJGL_ProductionSchedulingPlan_WBS_UnitWork]
GO

alter table HJGL_ProductionSchedulingPlan add CompletedCount int
alter table HJGL_ProductionSchedulingPlan add CompletedRate decimal(18,2)
alter table HJGL_ProductionSchedulingPlan add TotalCompletedRate decimal(18,2)
go

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已完成量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HJGL_ProductionSchedulingPlan', @level2type=N'COLUMN',@level2name=N'CompletedCount'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已完成百分比' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HJGL_ProductionSchedulingPlan', @level2type=N'COLUMN',@level2name=N'CompletedRate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已完成百分比汇总' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HJGL_ProductionSchedulingPlan', @level2type=N'COLUMN',@level2name=N'TotalCompletedRate'
GO

drop view View_HJGL_ProductionSchedulingPlanStatistics
go


CREATE PROCEDURE [dbo].[Sp_ProductionSchedulingPlanStatistics]
 @projectId nvarchar(50)=null,
 @unitWorkId nvarchar(50)=null,
 @flowingSection nvarchar(50)=null,
 @caliber nvarchar(50)=null
AS
/************排产计划统计**********/
SELECT distinct unitWork.UnitWorkId,
unitWork.UnitWorkCode,
unitWork.UnitWorkName,
unitWork.ProjectId,
pipeline.FlowingSection,
(case p.SteelType when '1' then '碳钢' when '2' then '不锈钢' when '3' then '铬钼钢' when '4' then '低合金钢' when '5' then '镍合金钢' when '6' then '钛合金钢' when '7' then '其他' end) as Material,
'<'+ @caliber as Caliber,
isnull(weldJoint.Dia,0) as Dia,
isnull(maxDia.maxTotalDia+weldJoint.Dia,0) as totalDia,
isnull(cTotal.cTotalCount,0) as cTotalCount,
(case when isnull(total.totalCount,0)>0 then
isnull(cTotal.cTotalCount,0) / isnull(cast(total.totalCount as decimal(18,2)),0)*100.0 else 0 end) as rate,
(case when isnull(ScTotal.ScTotalCount,0)>0 then 
isnull(Stotal.StotalCount,0)/isnull(cast(ScTotal.ScTotalCount as decimal(18,2)),0)*100.0 else 0 end)as Srate
FROM WBS_UnitWork AS unitWork
LEFT JOIN (select FlowingSection,UnitWorkId from HJGL_Pipeline where PipeArea='1') as pipeline on pipeline.UnitWorkId = unitWork.UnitWorkId
--材质
LEFT JOIN (select distinct Base_Material.SteelType,UnitWorkId from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId = HJGL_WeldJoint.Material1Id
			where PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口') as p on p.UnitWorkId = unitWork.UnitWorkId
--达因
LEFT JOIN (select sum(Dia) as Dia,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId  = HJGL_WeldJoint.Material1Id
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			and cast(SUBSTRING(HJGL_WeldJoint.DNDia,CHARINDEX('N',HJGL_WeldJoint.DNDia)+1,len(HJGL_WeldJoint.DNDia)-charindex('N',HJGL_WeldJoint.DNDia)) as int)<@caliber
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia) as weldJoint on weldJoint.UnitWorkId =unitWork.UnitWorkId
			and weldJoint.FlowingSection = pipeline.FlowingSection
--大于口径的达因
LEFT JOIN (select sum(Dia) as maxTotalDia,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId  = HJGL_WeldJoint.Material1Id
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			and cast(SUBSTRING(HJGL_WeldJoint.DNDia,CHARINDEX('N',HJGL_WeldJoint.DNDia)+1,len(HJGL_WeldJoint.DNDia)-charindex('N',HJGL_WeldJoint.DNDia)) as int)>=@caliber
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia) as maxDia on maxDia.UnitWorkId =unitWork.UnitWorkId
			and maxDia.FlowingSection = pipeline.FlowingSection
--总焊口数
LEFT JOIN (select count(*) as totalCount,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId  = HJGL_WeldJoint.Material1Id
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			and cast(SUBSTRING(HJGL_WeldJoint.DNDia,CHARINDEX('N',HJGL_WeldJoint.DNDia)+1,len(HJGL_WeldJoint.DNDia)-charindex('N',HJGL_WeldJoint.DNDia)) as int)<@caliber
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia) as total on total.UnitWorkId =unitWork.UnitWorkId
			and total.FlowingSection = pipeline.FlowingSection
--完成焊口数
LEFT JOIN (select count(*) as cTotalCount,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId  = HJGL_WeldJoint.Material1Id
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			and cast(SUBSTRING(HJGL_WeldJoint.DNDia,CHARINDEX('N',HJGL_WeldJoint.DNDia)+1,len(HJGL_WeldJoint.DNDia)-charindex('N',HJGL_WeldJoint.DNDia)) as int)<@caliber
			and HJGL_WeldJoint.WeldingDailyId is not null
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia) as cTotal on cTotal.UnitWorkId =unitWork.UnitWorkId
			and cTotal.FlowingSection = pipeline.FlowingSection
--汇总总焊口
LEFT JOIN (select count(*) as StotalCount,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection) as Stotal on Stotal.UnitWorkId =unitWork.UnitWorkId
			and total.FlowingSection = pipeline.FlowingSection
--完成焊口数
LEFT JOIN (select count(*) as ScTotalCount,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
		   and HJGL_WeldJoint.WeldingDailyId is not null
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection) as ScTotal on ScTotal.UnitWorkId =unitWork.UnitWorkId
			and cTotal.FlowingSection = pipeline.FlowingSection
where (unitWork.ProjectId=@projectId or @projectId is null)
and (unitWork.UnitWorkId=@unitWorkId or @unitWorkId is null)
and (pipeline.FlowingSection=@flowingSection or @flowingSection is null)
union 

SELECT distinct unitWork.UnitWorkId,
unitWork.UnitWorkCode,
unitWork.UnitWorkName,
unitWork.ProjectId,
pipeline.FlowingSection,
(case p.SteelType when '1' then '碳钢' when '2' then '不锈钢' when '3' then '铬钼钢' when '4' then '低合金钢' when '5' then '镍合金钢' when '6' then '钛合金钢' when '7' then '其他' end) as Material,
'≥'+@caliber as Caliber,
isnull(weldJoint.Dia,0) as Dia,
isnull(weldJoint.Dia+minDia.minTotalDia,0) as TotalDia,
isnull(cTotal.cTotalCount,0) as cTotalCount,
(case when isnull(total.totalCount,0)>0 then 
isnull(cTotal.cTotalCount,0) / isnull(cast(total.totalCount as decimal(18,2)),0)*100.0 else 0 end) as rate,
(case when isnull(ScTotal.ScTotalCount,0)>0 then
isnull(Stotal.StotalCount,0)/isnull(cast(ScTotal.ScTotalCount as decimal(18,2)),0)*100.0 else 0 end)as Srate
FROM WBS_UnitWork AS unitWork
LEFT JOIN (select FlowingSection,UnitWorkId from HJGL_Pipeline where PipeArea='1') as pipeline on pipeline.UnitWorkId = unitWork.UnitWorkId

LEFT JOIN (select distinct Base_Material.SteelType,UnitWorkId from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId = HJGL_WeldJoint.Material1Id
			where PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口') as p on p.UnitWorkId = unitWork.UnitWorkId

LEFT JOIN (select sum(Dia) as Dia,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId  = HJGL_WeldJoint.Material1Id
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			and cast(SUBSTRING(HJGL_WeldJoint.DNDia,CHARINDEX('N',HJGL_WeldJoint.DNDia)+1,len(HJGL_WeldJoint.DNDia)-charindex('N',HJGL_WeldJoint.DNDia)) as int)>=@caliber
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia) as weldJoint on weldJoint.UnitWorkId =unitWork.UnitWorkId
			and weldJoint.FlowingSection = pipeline.FlowingSection
--小于口径的达因
LEFT JOIN (select sum(Dia) as minTotalDia,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId  = HJGL_WeldJoint.Material1Id
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			and cast(SUBSTRING(HJGL_WeldJoint.DNDia,CHARINDEX('N',HJGL_WeldJoint.DNDia)+1,len(HJGL_WeldJoint.DNDia)-charindex('N',HJGL_WeldJoint.DNDia)) as int)<@caliber			
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia) as minDia on minDia.UnitWorkId =unitWork.UnitWorkId
			and minDia.FlowingSection = pipeline.FlowingSection

--总焊口数
LEFT JOIN (select count(*) as totalCount,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId  = HJGL_WeldJoint.Material1Id
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			and cast(SUBSTRING(HJGL_WeldJoint.DNDia,CHARINDEX('N',HJGL_WeldJoint.DNDia)+1,len(HJGL_WeldJoint.DNDia)-charindex('N',HJGL_WeldJoint.DNDia)) as int)>=@caliber
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia) as total on total.UnitWorkId =unitWork.UnitWorkId
			and total.FlowingSection = pipeline.FlowingSection
--完成焊口数
LEFT JOIN (select count(*) as cTotalCount,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			left join Base_Material on Base_Material.MaterialId  = HJGL_WeldJoint.Material1Id
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			and cast(SUBSTRING(HJGL_WeldJoint.DNDia,CHARINDEX('N',HJGL_WeldJoint.DNDia)+1,len(HJGL_WeldJoint.DNDia)-charindex('N',HJGL_WeldJoint.DNDia)) as int)>=@caliber
			and HJGL_WeldJoint.WeldingDailyId is not null
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection,Base_Material.SteelType,HJGL_WeldJoint.DNDia) as cTotal on cTotal.UnitWorkId =unitWork.UnitWorkId
			and cTotal.FlowingSection = pipeline.FlowingSection

--汇总总焊口
LEFT JOIN (select count(*) as StotalCount,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection) as Stotal on Stotal.UnitWorkId =unitWork.UnitWorkId
			and total.FlowingSection = pipeline.FlowingSection
--完成焊口数
LEFT JOIN (select count(*) as ScTotalCount,HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection from HJGL_WeldJoint
			left join HJGL_Pipeline on HJGL_Pipeline.PipelineId = HJGL_WeldJoint.PipelineId
			where HJGL_Pipeline.PipeArea='1' and HJGL_WeldJoint.JointAttribute='预制口'
		   and HJGL_WeldJoint.WeldingDailyId is not null
			group by HJGL_Pipeline.UnitWorkId,HJGL_Pipeline.FlowingSection) as ScTotal on ScTotal.UnitWorkId =unitWork.UnitWorkId
			and cTotal.FlowingSection = pipeline.FlowingSection
where (unitWork.ProjectId=@projectId or @projectId is null)
and (unitWork.UnitWorkId=@unitWorkId or @unitWorkId is null)
and (pipeline.FlowingSection=@flowingSection or @flowingSection is null)
GO