CREATE VIEW [dbo].[View_HJGL_ProductionSchedulingPlanStatistics]
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,
'<100' as Caliber,
isnull(weldJoint.Dia,0) as Dia
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)<100
			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

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,
'≥100' as Caliber,
isnull(weldJoint.Dia,0) as Dia
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)>=100
			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

GO