create procedure TurnPage
(
@ContainerNo varchar(16),
@isFilter smallint,
@PageSize int,
@CurrPage int,
@isCheck smallint, --- check valid result
@ResultTotal int output,
@ResultSub int output,
@SortOrder varchar(10)
)
As
Begin
declare @topnum int,@previous int
declare @ContPrefix varchar(6)
Select @ContPrefix=dbo.LLL_GetContainerPrefix(@ContainerNo)
--select @PageSize = 30
--select @CurrPage = 2
declare @i int,@IDStr nvarchar(4000),@strSQL nvarchar(4000)
--declare @strCountSQL nvarchar(1000)
create table #wtemp(
ContainerNo varchar(16) ,
BoxNo varchar(16),
so varchar(80) NULL,
PackageNo varchar(10) NULL,
nw real NULL,
gw real NULL,
Contents varchar(120) NULL
)
--print 'AAAAAAAAAA'
-- If(Left(@ContainerNo,1) = 'N' Or Left(@ContainerNo,3) = 'LLC')
If @ContPrefix='Nor' or @ContPrefix='LLC'
Begin
insert #wtemp SELECT distinct ContainerNo,BoxNo,wo,PackageNo,nw,gw,Contents
FROM ssItemNoBox Where ContainerNo = @ContainerNo order by BoxNo
End
Else
Begin
insert #wtemp SELECT distinct ContainerNo,BoxNo,wo,PackageNo,nw,gw,Contents
FROM ssOtherItemNoBox Where ContainerNo = @ContainerNo order by BoxNo
End
select @topnum = @CurrPage * @PageSize
select @previous = (@CurrPage - 1) * @PageSize
select @i = 0
select @strSQL = N''
select @strSQL = @strSQL + N' select top '+str(@topnum)+ ' @i = @i + 1 '
select @strSQL = @strSQL + N', @IdStr = '
select @strSQL = @strSQL + N'case when @i > '+str(@previous)+' then @IdStr + ltrim(rtrim(str(BoxNo))) + '','' '
select @strSQL = @strSQL + N'else N''''end '
select @strSQL = @strSQL + N'from #wtemp '
if(@isCheck = 1)
begin
--Print 'AAAAAAAAAAA'
select @strSQL = ltrim(rtrim(@strSQL)) + N' Where ((gw- nw >10) Or nw > gw Or gw >100 Or ( gw <> 0 And gw = nw )) '
select @ResultSub = count(BoxNo) From #wtemp Where ((gw- nw >10) Or nw > gw Or gw >100 Or ( gw <> 0 And gw = nw ))
end
else
begin
if(@isFilter != 0)
begin
select @strSQL = ltrim(rtrim(@strSQL)) + N' order by BoxNo asc '
select @ResultSub = count(BoxNo) From #wtemp
end
else
begin
select @strSQL = ltrim(rtrim(@strSQL)) + N' Where (nw = 0 Or gw = 0) order by BoxNo asc '
select @ResultSub = count(BoxNo) From #wtemp Where (nw = 0 Or gw = 0)
end
end
Select @IdStr = N''
--print @strSQL
exec sp_executesql @strSQL,N'@i int,@IdStr varchar(4000) output',@i,@IdStr output
if len(rtrim(ltrim(@IdStr))) > 0
begin
select @IdStr = left(@IdStr,len(@IdStr)-1)
end
--print @IdStr
if(@IDStr != '')
select @strSQL = 'select * from #wtemp as temp where BoxNo in ('+@IDStr+') Order by BoxNo ' + @SortOrder
else
select @strSQL = 'select * from #wtemp as temp Order by BoxNo ' + @SortOrder --no any recorder
--print @strSQL
select @ResultTotal = count(BoxNo) from #wtemp
--print @ResultTotal
--print @ResultSub
exec(@strSQL)
--select top 2 * from #wtemp as temp where BoxNo in ('010001','010004') order by BoxNo desc
--select * from #wtemp as temp1
--SELECT distinct ContainerNo,BoxNo,so,PackageNo,nw,gw,Contents FROM ssOtherItemNoBox as temp Where ContainerNo = @ContainerNo and (nw = 0 Or gw = 0) order by BoxNo
End