177 lines
13 KiB
Transact-SQL
177 lines
13 KiB
Transact-SQL
alter table HJGL_PackagingManage add CompileMan nvarchar(50)
|
|
alter table HJGL_PackagingManage add CompileDate datetime
|
|
go
|
|
ALTER TABLE [dbo].[PTP_TestPackage] DROP CONSTRAINT [FK_PTP_TestPackage_Base_Unit]
|
|
GO
|
|
alter table PTP_TestPackage alter column UnitId nvarchar(500)
|
|
go
|
|
|
|
alter table HJGL_ProductionSchedulingPlan alter column CompletedCount decimal(18,2)
|
|
alter table HJGL_ProductionSchedulingPlan add OnDayCompleteDyne decimal(18,2)
|
|
alter table HJGL_ProductionSchedulingPlan add NextDayCompleteDyne 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'OnDayCompleteDyne'
|
|
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'NextDayCompleteDyne'
|
|
GO
|
|
|
|
|
|
ALTER 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,
|
|
p.SteelType,
|
|
(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' and FlowingSection is not null and FlowingSection!='') as pipeline on pipeline.UnitWorkId = unitWork.UnitWorkId
|
|
--材质
|
|
left join (select SteelType,UnitWorkId from HJGL_Pipeline
|
|
left join Base_Material on Base_Material.MaterialId = HJGL_Pipeline.MaterialId
|
|
where PipeArea='1' and HJGL_Pipeline.MaterialId is not null and HJGL_Pipeline.MaterialId!=''
|
|
)as p on p.UnitWorkId = unitWork.UnitWorkId
|
|
----达因
|
|
--LEFT JOIN (select sum(Size) 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(Size) 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,
|
|
p.SteelType,
|
|
(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' and FlowingSection is not null and FlowingSection!='') as pipeline on pipeline.UnitWorkId = unitWork.UnitWorkId
|
|
|
|
--材质
|
|
left join (select SteelType,UnitWorkId from HJGL_Pipeline
|
|
left join Base_Material on Base_Material.MaterialId = HJGL_Pipeline.MaterialId
|
|
where PipeArea='1' and HJGL_Pipeline.MaterialId is not null and HJGL_Pipeline.MaterialId!=''
|
|
)as p on p.UnitWorkId = unitWork.UnitWorkId
|
|
|
|
--LEFT JOIN (select sum(Size) 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(Size) 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
|
|
|
|
|