以维护日期的不同而改变显示内容. 如今天是 21 号. 维护了七天的数据即 21-- 28 . 当在 22 号打开维护界面的时候,维护内容显示为 22 -- 29 号的数据以及日期.
创建维护表:..
-----------------创建数据记录表-------------------------
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[pdl_weekprocedure]') and objectproperty(id, n'isusertable') = 1)
drop table pdl_weekprocedure
go
if not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[pdl_weekprocedure]') and objectproperty(id, n'isusertable') = 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, n'isusertable') = 1)
drop table pdl_weekproceduredate
go
if not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[pdl_weekproceduredate]') and objectproperty(id, n'isusertable') = 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, n'isusertable') = 1)
drop table pdl_weekprochistory
go
if not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[pdl_weekprochistory]') and objectproperty(id, n'isusertable') = 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, ([email protected]+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 @[email protected]+',['+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, n'isusertable') = 1)
drop table [dbo].[x_tmpweekproc]
exec('select partnum, revision , deldate=min(deldate) ,upp '[email protected]+' 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='''[email protected]+''', revision='''[email protected] +''',
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 = ''' [email protected] +''' and t2.revision = ''' [email protected]+'''
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>[email protected] or @bdate='')
and (todaytime <[email protected] 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 [email protected]+',['+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 '[email protected]+' ,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
