as
declare
@ratio decimal(7,4),
@addCount1 decimal(7,4),
@addCount2 decimal(7,4),
@iTrayNum int , --托号
@produce nvarchar(30), --产品
@boxnum int --当前托上剩余量
create table table1 (ID int identity(1,1),produce nvarchar(30),boxnum int,
evetbouxnumb int,ratio decimal(7,4),tptype varchar(30))
insert into table1 select
cr.produce,
cr.boxnum,
cr.evetbouxnumb,
convert(decimal(7,4),
convert(decimal(7,4),cr.boxnum) / convert(decimal(7,4),cr.evetbouxnumb)) ratio,
cr.tptype
from yy_ContractResidue cr order by ratio asc
select * from table1
--delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
--select * from table1
--select top 1 ratio from table1 order by id desc
--select sum(ratio) from table1
set @iTrayNum = 20
if (select sum(ratio) from table1) <= 1
begin
insert into yy_PackageListC select * from table1
insert into yy_PackageListC(tpnum) values (@iTrayNum)
end
else
begin
declare pcurr cursor for select produce,boxnum,ratio from table1
open pcurr
set @addCount1 = 0
--set @addCount2 = 0
fetch next from pcurr into @produce,@boxnum,@ratio
set @addCount1 = @ratio + (select top 1 ratio from table1 order by id desc )
while (@@fetch_status = 0)
begin
--set @addCount2 = @addCount1 + @addCount2
if @addCount1 < 1
begin
print (@addCount1)
fetch next from pcurr into @produce,@boxnum,@ratio
set @addCount1 = @ratio + @addCount1
--print (@addCount1)
end
if @addCount1 = 1
begin
delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
fetch next from pcurr into @produce,@boxnum,@ratio
set @addCount1 = @ratio + (select top 1 ratio from table1 order by id desc )
end
if @addCount1 > 1
begin
--print (@addCount1)
delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
--fetch next from pcurr into @produce,@boxnum,@ratio
set @addCount1 = @ratio + (select top 1 ratio from table1 order by id desc )
--print (@addCount1)
-- fetch next from pcurr into @produce,@boxnum,@ratio
end
/*
if @addCount = 1
begin
set @iTrayNum = @iTrayNum + 1
insert into yy_PackageListC(produce,boxnum,tpnum) values (@produce,@boxnum,@iTrayNum)
insert into yy_PackageListC(produce,boxnum,tpnum) values ((select top 1 produce from table1 order by id desc),
(select top 1 boxnum from table1 order by id desc),
@iTrayNum)
delete from table1 where ratio = @ratio
delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
fetch next from pcurr into @produce,@boxnum,@ratio
end
if @addCount > 1
begin
--print ('上面的数据是余数表各系数最佳拼托组合之和')
set @iTrayNum = @iTrayNum
insert into yy_PackageListC(produce,boxnum,tpnum) values ((select top 1 produce from table1 order by id desc),
(select top 1 boxnum from table1 order by id desc),
@iTrayNum)
delete from table1 where ratio = (select top 1 ratio from table1 order by id desc )
--set @addCount = 0
--set @addCount = @ratio + @addCount
print (@addCount)
end */
--fetch next from pcurr into @produce,@boxnum,@ratio
end
end
close pcurr
deallocate pcurr
drop table table1
/*因为其中含有部分项目属性代码，懒得删。。*/