USE [erpdb1]
GO
/****** Object: StoredProcedure [dbo].[sp_orderdate] Script Date: 12/29/2014 15:41:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_orderdate]
as
declare cu_orderpur1019 cursor for SELECT holiday FROM a_a1ErpHoliday
declare @ho_day datetime --临时取得的天数
declare @wk_day int --生产要用的天数
declare @real_day int --实际工时天数
declare @to_wkdate datetime --最初的天数
declare @total_feiyong int --总的费用
begin
update a_a1ErpDelivery set curdate =GETDATE();
-----已确认未生产的情况
select @total_feiyong = CEILING(isnull(SUM(dingdanzhu.clf_),0)) FROM dingdanzhu with (nolock) left outer join fltab_ on dingdanzhu.biaodanid = fltab_.biaodanid WHERE ( ( dingdanzhu.tg = '未' ) ) and cx IS NULL and cxqr IS not NULL and qlstatus IS NULL and cw is not null
and (bz <> 'mn' and bz <> '模拟下单' and bz <> '模拟下单!') ;
-------产线未确认的情况
select @total_feiyong = @total_feiyong + CEILING(isnull(SUM(dingdanzhu.clf_),0)) FROM dingdanzhu with (nolock) left outer join fltab_ on dingdanzhu.biaodanid = fltab_.biaodanid
where cxqr IS NULL and qlstatus IS NULL and cw is not null and (bz <> 'mn' and bz <> '模拟下单' and bz <> '模拟下单!') ;
-------不欠未配的情况
select @total_feiyong = @total_feiyong + CEILING(isnull(SUM(dingdanzhu.clf_),0)) FROM dingdanzhu with (nolock) left outer join fltab_ on dingdanzhu.biaodanid = fltab_.biaodanid where qlstatus is null and cw is not null and ckdate IS NULL and (bz <> 'mn' and bz <> '模拟下单' and bz <> '模拟下单!') ;
-------欠料红色的情况
select @total_feiyong = @total_feiyong + CEILING(isnull(SUM(dingdanzhu.clf_),0)) FROM dingdanzhu with (nolock) left outer join fltab_ on dingdanzhu.biaodanid = fltab_.biaodanid WHERE qlstatus = 't' and (bz <> 'mn' and bz <> '模拟下单' and bz <> '模拟下单!') ;
select @total_feiyong = CEILING(@total_feiyong /10000);
select @wk_day= ceiling(@total_feiyong/erpcapacity) from a_a1ErpDelivery;
set @real_day = @wk_day;
set @to_wkdate = dateadd(day,@wk_day,GETDATE())
open cu_orderpur1019
fetch next from cu_orderpur1019 into @ho_day
while @@fetch_status = 0
begin
if ( @ho_day > GETDATE() and @ho_day < @to_wkdate )
begin
set @wk_day = @wk_day +1 ;
set @to_wkdate = dateadd(day,1,@to_wkdate)
end
fetch next from cu_orderpur1019 into @ho_day
end
update a_a1ErpDelivery set complatedate = @to_wkdate,wkday = @wk_day,realday =@real_day,erporder=@total_feiyong,curdate =GETDATE() where cn=1;
close cu_orderpur1019
DEALLOCATE cu_orderpur1019
end
订单---查询根椐当前产能和假期，完成订单的时间
