IDC资讯中心
以维护日期的不同而改变显示内容. 如今天是 21 号. 维护了七天的数据即 21– 28 . 当在 22 号打开维护界面的时候,维护内容显示为 22 — 29 号的数据以及日期.
创建维护表:..
—————–创建数据记录表————————-
if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[pdl_weekprocedure]) and objectproperty(id, nisusertable) = 1)
drop table pdl_weekprocedure
go
if not exists (select * from dbo.sysobjects where id = object_id(n[dbo].[pdl_weekprocedure]) and objectproperty(id, nisusertable) = 1)
begin
create table pdl_weekprocedure (
[id] int identity(1,1) not null,
[dateid] datetime not null ,
[partnum] varchar(20) null ,
[revision] varchar(5) null default(0),
[todate] datetime null,
[qnty1] int null default(0),
[qnty2] int null default(0),
[qnty3] int null default(0),
[qnty4] int null default(0),
[qnty5] int null default(0),
[qnty6] int null default(0),
[qnty7] int null default(0)–,
— [weekdate] datetime
constraint [pk_weekprocedure] primary key clustered
(
[id]
) on [primary]
) on [primary]
end
—————–创建日期表——————–
if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[pdl_weekproceduredate]) and objectproperty(id, nisusertable) = 1)
drop table pdl_weekproceduredate
go
if not exists (select * from dbo.sysobjects where id = object_id(n[dbo].[pdl_weekproceduredate]) and objectproperty(id, nisusertable) = 1)
begin
create table pdl_weekproceduredate (
[id] int identity(1,1) not null,
[dateid] datetime not null ,
[date1] datetime null ,
[date2] datetime null ,
[date3] datetime null ,
[date4] datetime null ,
[date5] datetime null ,
[date6] datetime null ,
[date7] datetime null
constraint [pk_weekproceduredate] primary key clustered
(
[id]
) on [primary]
) on [primary]
end
———– 创建历一周交货排程史表 ———————————-
if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[pdl_weekprochistory]) and objectproperty(id, nisusertable) = 1)
drop table pdl_weekprochistory
go
if not exists (select * from dbo.sysobjects where id = object_id(n[dbo].[pdl_weekprochistory]) and objectproperty(id, nisusertable) = 1)
begin
create table pdl_weekprochistory (
[id] int identity(1,1) not null,
[dateid] datetime not null ,
[partnum] varchar(20) null ,
[revision] varchar(5) null default(0),
[qnty1] int null default(0),
[qnty2] int null default(0),
[qnty3] int null default(0),
[qnty4] int null default(0),
[qnty5] int null default(0),
[qnty6] int null default(0),
[qnty7] int null default(0),
[date1] datetime null ,
[date2] datetime null ,
[date3] datetime null ,
[date4] datetime null ,
[date5] datetime null ,
[date6] datetime null ,
[date7] datetime null ,
[iotime] datetime not null default(getdate()),
[outputdate] datetime null
constraint [pk_weekprochistory] primary key clustered
(
[id]
) on [primary]
) on [primary]
end
更新维护表日期数据:
/*
procedure name : 一周交货排程数据更新
author name : lyf
date : 01/13/2005
*/
alter procedure pdl_weekprocdateupdate as
declare @num int , @num1 int, @num2 int, @count int
declare @str varchar(4000)
declare @dateid datetime ,@date1 datetime , @date2 datetime ,@date3 datetime, @date4 datetime,
@date5 datetime, @date6 datetime, @date7 datetime
select @dateid = convert(datetime, convert(varchar,getdate(),101))
select @date1= convert(datetime, convert(varchar,getdate(),101))
select @date2= @date1+1 , @date3 = @date1+2, @date4= @date1+3,
@date5 = @date1+4, @date6 = @date1+5 , @date7 = @date1+6
—————修正更新为当天日期后向后推7天——–
if not exists(select * from pdl_weekproceduredate
where convert(datetime, convert(varchar, dateid, 101)) = convert(datetime, convert(varchar,@dateid,101)))
insert into pdl_weekproceduredate (dateid,date1,date2,date3,date4,date5,date6,date7)
values(@dateid, @date1, @date2, @date3, @date4, @date5, @date6,@date7)
———-将表 pdl_weekprocedure 里的数据换算成当天最新数据 ” 对应程序维护接口 “—
select @num = isnull(datediff(day, (select min(dateid) from pdl_weekprocedure ),
(select max(dateid) from pdl_weekproceduredate )),0)
if @num >0
begin
set @count = 1
set @num1 = @num
set @num2 = @num
set @num = 7 – @num
set @str=
while @count <= @num
begin
select @str = @str + qnty+convert(varchar,@count) + = qnty+convert(varchar, @num1+1)+,
set @count = @count +1
set @num1 = @num1+1
end
while @num2 >0
begin
select @str = @str + qnty+convert(varchar, (7-@num2+1)) + = 0,
set @num2 = @num2-1
end
select @str = left(@str , len(@str)-1)
————–插入一周交货排程维护历史————
insert into pdl_weekprochistory( dateid, partnum, revision, qnty1, qnty2, qnty3, qnty4, qnty5, qnty6, qnty7,
date1, date2, date3, date4, date5, date6, date7, iotime, outputdate )
select t1.dateid, t1.partnum, t1.revision, t1.qnty1, t1.qnty2, t1.qnty3, t1.qnty4, t1.qnty5, t1.qnty6, t1.qnty7,
t2.date1, t2.date2, t2.date3, t2.date4, t2.date5, t2.date6, t2.date7 , getdate(), t1.todate
from pdl_weekprocedure t1(nolock) , pdl_weekproceduredate t2 (nolock)
where t1.dateid = t2.dateid
————————更新pdl_weekprocedure 数据 —————————-
exec(update pdl_weekprocedure set + @str )
update pdl_weekprocedure
set dateid = (select max(dateid) from pdl_weekproceduredate )
————————-更新当天出货时间字段————————–
update pdl_weekprocedure set todate = null
where convert(datetime, convert(varchar,todate,101)) <> convert(datetime, convert(varchar,getdate(),101))
end
–delete pdl_weekproceduredate where dateid=2005-01-15 00:00:00.000
— update pdl_weekprocedure set dateid = convert(datetime, convert(varchar, getdate(),101))-1
生成最后数据集合:
/*
procedure name : 一周交货排程报表
author name : lyf
date : 01/18/2005
*/
— exec pdl_weekdeliveryprocedure ,,,,,
alter procedure pdl_weekdeliveryprocedure
@inbpartnum varchar(20) =, –dh024-030, — , mh075-013
@inepartnum varchar(20) =, –dh024-030, — , mh075-013
@inbrevision varchar(5)=,
@inerevision varchar(5)=,
@bdate varchar(100), –datetime, –=01/18/2005 ,
@edate varchar(100), –datetime — =01/21/2005
@flage int
as
declare @bdatetime datetime , @edatetime datetime
–set @bdate = isnull(@bdate, getdate())
–set @edate = isnull(@edate, getdate()+6)
————-调用更新维护纪录表数据存储过程——————-
exec pdl_weekprocdateupdate
select @bdatetime = convert(datetime,convert(varchar , getdate() , 101))
select @edatetime = convert(datetime,convert(varchar , @bdatetime +7 , 101))
/*取周出货状态数据表结构*/
select top 0 partnum , revision , qnty= 0 , weekdate=dateid , com= a9 — = convert(varchar(5), weekdate , 101)
into #weekconfig
from pdl_weekprocedure(nolock)
/*在制品数据*/
select t1.partnum, t1.revision ,
qnty = sum(case t1.pop
when 0
then ceiling(1 * t1.qnty /(power(isnull(t3.lpiece,1),case t1.pop when 0 then 0 else 1 end)*
power(isnull(t3.llpiece,1) , case t1.pop when 2 then 1 else 0 end)))
when 1
then ceiling(1 * t1.qnty * power(isnull(t3.lpiece,1),case t1.pop when 0 then 1 else 0 end) /
power(isnull(t3.llpiece,1) , case t1.pop when 2 then 1 else 0 end))
when 2
then (1 * t1.qnty * power(isnull(t3.lpiece,1),case t1.pop when 0 then 1 else 0 end)*
power(isnull(t3.llpiece,1) , case t1.pop when 2 then 0 else 1 end))
end), deldate = min(t4.expstkdate) , upp = convert(varchar,t3.lpiece) +*+ convert(varchar, t3.llpiece) ,
t5.id , t5.name
into #tmpstknwip
from stknwip t1(nolock),
lotinfo t2(nolock),
prodbasic t3(nolock) ,
orderdetail t4(nolock) ,
pdl_procgroup t5 (nolock) ,
pdl_procgroupdetail t6 (nolock)
where t1.lotnum=t2.lotnum
and t1.layer=t2.layer
and t2.isinsc <> 1
and t1.qnty>0
and t1.proccode not in(990,000)
and t1.partnum = t3.partnum
and t1.revision = t3.revision
–and t1.layer = t3.layer
and t2.ponum*=t4.ponum
and t2.poitem *=t4.serialnum
and t1.proccode = t6.proccode
and t5.id = t6.id
and (( t1.partnum >= @inbpartnum or @inbpartnum=)
and (t1.partnum <= @inepartnum or @inepartnum=))
and ((t1.revision = @inbrevision or @inbrevision =)
and (t1.revision = @inerevision or @inerevision =))
group by t1.partnum, t1.revision,
t3.lpiece , t3.llpiece , t5.id ,t5.name
order by t5.id , t1.partnum
/*wip完整性生成*/
declare @checkpartnum varchar(20) , @checkrevision varchar(5)
declare @wipproccode varchar(20) , @wipid varchar(5)
select top 1 @checkpartnum= partnum , @checkrevision = revision from #tmpstknwip
declare checkwip insensitive cursor
for
select procname = rtrim(ltrim(t1.name)) , id = rtrim(ltrim(t1.id))
from pdl_procgroup t1(nolock) , pdl_procgroupdetail t2 (nolock)
where t1.id = t2.id
group by t1.id, t1.name
open checkwip
begin
fetch next from checkwip into @wipproccode , @wipid
while @@fetch_status = 0
begin
if not exists(select * from #tmpstknwip where name = @wipproccode and id = @wipid )
insert into #tmpstknwip( partnum, revision , qnty, name , id)
select @checkpartnum ,@checkrevision , 0 ,@wipproccode ,@wipid
fetch next from checkwip into @wipproccode , @wipid
end
end
close checkwip
deallocate checkwip
/*合计在制品数量*/
select partnum, revision , qnty = sum(qnty) ,
deldate , upp , proccode = name , id
into #stknwip
from #tmpstknwip
group by partnum, revision , deldate , upp , id , name
order by id
/*判断完整性*/
if (( not exists( select * from #weekconfig )) and (not exists( select * from #stknwip )) )
begin
insert into #stknwip( partnum, revision , qnty, proccode , id)
select not data , no , 0 , proccode = name , id
from pdl_procgroup
insert into #weekconfig(partnum , revision , qnty ,weekdate , com)
select not data , no , 0 , @bdatetime, a9
end
else
begin
if not exists( select * from #stknwip )
insert into #stknwip( partnum, revision , qnty, proccode , id)
select not data , no , 0 , proccode = name , id
from pdl_procgroup
if not exists ( select * from #weekconfig )
insert into #weekconfig(partnum , revision , qnty ,weekdate, com)
select top 1 t1.partnum, t1.revision , qnty = 0 , @bdatetime , a9
from stknwip t1(nolock), lotinfo t2(nolock)
where t1.lotnum=t2.lotnum
and t1.layer=t2.layer
and t2.isinsc <> 1
and t1.qnty>0
and t1.proccode not in(990,000)
and (( t1.partnum >= @inbpartnum or @inbpartnum=)
and (t1.partnum <= @inepartnum or @inepartnum=))
and ((t1.revision = @inbrevision or @inbrevision =)
and (t1.revision = @inerevision or @inerevision =))
end
/*生成出货状态时间格式以及完整性生成*/
declare @partnum varchar(20) , @revision varchar(5) , @count int , @datecount int
declare @weekdate datetime , @com varchar(200)
select @com =a
declare adddatelist insensitive cursor
for
select partnum, revision from #weekconfig
group by partnum, revision
open adddatelist
begin
fetch next from adddatelist into @partnum , @revision
while @@fetch_status = 0
begin
select @count = count(weekdate) from #weekconfig
where partnum = @partnum and revision = @revision and weekdate >= @bdatetime and weekdate <= @edatetime
select @datecount = 0
if @count < 7
begin
while @datecount < 7
begin
select @weekdate = convert(datetime, convert(varchar , (@bdatetime + @datecount), 101))
if not exists( select * from #weekconfig
where partnum = @partnum and revision = @revision
and convert(datetime, convert(varchar ,weekdate , 101)) = @weekdate
)
begin
insert into #weekconfig(partnum, revision, qnty , weekdate , com)
values(@partnum , @revision , 0 , convert(datetime, convert(varchar , (@bdatetime + @datecount), 101)),
@com+ convert(varchar, @datecount))
select @datecount = @datecount + 1
end
else
begin
update #weekconfig set com = @com+ convert(varchar, @datecount)
where partnum = @partnum and revision = @revision
and convert(datetime, convert(varchar ,weekdate , 101)) = @weekdate
select @datecount = @datecount + 1
end
end
end
fetch next from adddatelist into @partnum , @revision
end
end
close adddatelist
deallocate adddatelist
/*生成交叉数据集*/
insert into #stknwip( partnum, revision , qnty, proccode , id)
select partnum , revision , qnty , com , 1a
from #weekconfig
where weekdate >= @bdatetime and weekdate <= @edatetime
declare @stkpartnum varchar(20) , @stkrevision varchar(5) , @stkdeldate datetime , @stkupp varchar(10)
declare cu_stknwip insensitive cursor
for
select partnum , revision from #stknwip
group by partnum , revision
open cu_stknwip
begin
fetch next from cu_stknwip into @stkpartnum , @stkrevision
while @@fetch_status = 0
begin
select @stkdeldate = min(deldate), @stkupp =min(upp)
from #stknwip
where partnum = @stkpartnum and revision = @stkrevision
update t1 set t1.deldate = @stkdeldate , t1.upp = @stkupp from #stknwip t1
where t1.partnum = @stkpartnum and t1.revision = @stkrevision
and (isnull(t1.deldate,01/01/1900)= 01/01/1900 or isnull(t1.upp,-) = -)
fetch next from cu_stknwip into @stkpartnum , @stkrevision
end
end
close cu_stknwip
deallocate cu_stknwip
declare @s varchar(8000)
set @s=
select @s=@s+,[+rtrim(proccode)+] = max(case proccode when +rtrim(proccode)+ then qnty else end)
from #stknwip group by proccode , id order by id
if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[x_tmpweekproc]) and objectproperty(id, nisusertable) = 1)
drop table [dbo].[x_tmpweekproc]
exec(select partnum, revision , deldate=min(deldate) ,upp +@s+ into x_tmpweekproc from #stknwip group by partnum , revision, upp )
select * into #stknwiptotal from x_tmpweekproc
drop table x_tmpweekproc
select t2.partnum,t2.revision, pqnty = sum(t2.pqnty)
into #inwarehouse
from pdl_passbas t1(nolock),
pdl_passdtl t2(nolock),
lotinfo t3(nolock)
where t1.paperno = t2.paperno
and (t1.paperdate >= @bdatetime)
and (t1.paperdate <= @edatetime)
and t2.aftproc >= 800
and t2.lotnum = t3.lotnum
and t1.finished = 1
and t1.iscancel = 0
group by t2.partnum,t2.revision
————————————增加仓位字段——————————
exec(alter table #stknwiptotal add [inwarehouse] int not null default(0))
———修改入库数———-
update t1 set t1.inwarehouse = isnull(t2.pqnty,0) from #stknwiptotal t1 , #inwarehouse t2
where t1.partnum = t2.partnum
————修改维护数据, 方便时间限定范围————————-
select top 0 dateid,partnum, revision , todaytime = dateid , qnty = 0 , datestr=qnty10 ,todate
into #todate
from pdl_weekprocedure
declare @daynum int , @checkdate datetime , @strint varchar(8000)
declare @partnumdate varchar(20) , @revisiondate varchar(5)
set @checkdate = convert(datetime , convert(varchar, getdate(), 101))
set @strint =
declare cu_partdate insensitive cursor
for
select distinct partnum, revision from pdl_weekprocedure
open cu_partdate
begin
fetch next from cu_partdate into @partnumdate , @revisiondate
while @@fetch_status = 0
begin
set @daynum = 1
while @daynum <= 7
begin
set @strint = convert(varchar, @daynum)
exec ( insert into #todate(dateid, partnum, revision , todaytime, qnty ,datestr, todate)
select t2.dateid, partnum=+@partnumdate+, revision=+@revisiondate +,
t1.[date + @strint +] , qnty = sum(isnull(t2.[qnty + @strint +],0)),
datestr = +qnty + @strint +, t2.todate
from pdl_weekproceduredate t1(nolock) ,
pdl_weekprocedure t2(nolock)
where t1.dateid = t2.dateid and t1.dateid= + @checkdate +
and t2.partnum = +@partnumdate + and t2.revision = +@revisiondate+
group by t1.[date + @strint +], t2.dateid, t2.todate )
set @daynum = @daynum + 1
end
fetch next from cu_partdate into @partnumdate , @revisiondate
end
end
close cu_partdate
deallocate cu_partdate
—————查询用户设置的时间范围 ——————————
select t1.* into #tmptodate from #todate t1 ,
(select distinct partnumrevision = partnum+revision
from #todate
where qnty>0 and ((todaytime>=@bdate or @bdate=)
and (todaytime <=@edate or @edate =))) t2
where (t1.partnum+t1.revision) = t2.partnumrevision
——————————转换数据显示格式—————————–
select top 0 dateid, partnum, revision, qnty1,qnty2,qnty3,qnty4,qnty5,qnty6,qnty7, todate
into #weekprocedure
from pdl_weekprocedure
declare @str varchar(8000)
set @str =
select @str =@str+,[+rtrim(datestr)+]=max(case todaytime when +rtrim(todaytime)+ then qnty else end)
from #tmptodate group by todaytime,datestr order by datestr
select @str = isnull(@str,0,0,0,0,0,0,0)
if (exists( select * from #tmptodate)) and (@flage = 0)
begin
insert into #weekprocedure(dateid, partnum, revision, qnty1,qnty2,qnty3,qnty4,qnty5,qnty6,qnty7, todate)
exec(select dateid, partnum, revision +@str+ ,todate from #tmptodate group by dateid, partnum, revision, todate)
update t1 set t1.[a0]=t2.qnty1, t1.[a1]=t2.qnty2, t1.[a2]=t2.qnty3, t1.deldate =t2.todate,
t1.[a3]=t2.qnty4, t1.[a4]=t2.qnty5, t1.[a5]=t2.qnty6, t1.[a6]=t2.qnty7
from #stknwiptotal t1, #weekprocedure t2(nolock)
where ltrim(rtrim(t1.partnum)) = ltrim(rtrim(t2.partnum))
and ltrim(rtrim(t1.revision)) = ltrim(rtrim(t2.revision))
and convert(datetime, @bdatetime) = t2.dateid
select t1.*, bdatetime = convert(datetime, @bdatetime) , edatetime =convert(datetime, @edatetime)
from #stknwiptotal t1 , #weekprocedure t2
where t1.partnum = t2.partnum and t1.revision = t2.revision
end
else
if @flage = 1
begin
update t1 set a0=qnty1, a1=qnty2, a2=qnty3, a3=qnty4, a4=qnty5, a5=qnty6, a6=qnty7, t1.deldate =t2.todate
from #stknwiptotal t1, pdl_weekprocedure t2
where ltrim(rtrim(t1.partnum)) = ltrim(rtrim(t2.partnum))
and ltrim(rtrim(t1.revision)) = ltrim(rtrim(t2.revision))
and convert(datetime, @bdatetime) = t2.dateid
select t1.*, bdatetime = convert(datetime, @bdatetime) , edatetime =convert(datetime, @edatetime)
from #stknwiptotal t1
end
