184 lines
13 KiB
Transact-SQL
184 lines
13 KiB
Transact-SQL
|
|
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
|
|
|
|
|