Java代码

String str ="call rpt_MastBatchNumberGoodsRpt(@a,@b,@c,@d,@e)";

Sql sql = Sqls.queryEntity(str);

sql.setEntity(dao().getEntity(Yikang2.class)); 
sql.params().set("a", "2017-9-11"); // 设置入参
sql.params().set("b", "2017-10-11"); // 设置入参
sql.params().set("c", ""); // 设置入参
sql.params().set("d", 0); // 设置入参
sql.params().set("e", 0); // 设置入参

dao().execute(sql);

List<Yikang2> list = sql.getList(Yikang2.class);

System.err.println("====="+Json.toJson(list));
return null;

存储过程代码

ALTER    procedure [dbo].[rpt_MastBatchNumberGoodsRpt]        
(        
    @begindate varchar(20),        
    @enddate varchar(20),        
    @sqlfilter varchar(4000),        
    @inStoreID integer,        
    @ShopID integer        
)          
as          
begin         
------------------------------------------------------------        
declare @wherestr varchar(4000)        
declare @sqlstr nvarchar(4000) 
declare @IsShowZooRow integer       
------------------------------------------------------------        
--创建临时表        
Create Table #temp(ID integer, IOFlag integer, batchcode varchar(64), StoreID integer,        
Qty decimal(22,8), Amt decimal(18,2))        
        
Create Table #table(ID integer, Code varchar(32), Name varchar(64), Specs varchar(64),  brandid integer,       
Unit varchar(64), batchcode varchar(64), StoreID integer,StartQty decimal(22,8), StartAmt decimal(18,2),         
InQty decimal(22,8), InAmt decimal(18,2), OutQty decimal(22,8), OutAmt decimal(18,2),         
EndQty decimal(22,8), EndAmt decimal(18,2))        
        
--设置过滤条件        
if(@sqlfilter='')        
begin        
  set @wherestr = ' '        
end 
else if(CHARINDEX('(IsShowZooRow=0)', @sqlfilter) >0)   
begin
	set @sqlfilter = REPLACE(@sqlfilter,'(IsShowZooRow=0)','1=1')
	set @wherestr = ' and (' + @sqlfilter + ')' ;   
end
else if(CHARINDEX('(IsShowZooRow=1)', @sqlfilter) >0)
begin
	set @sqlfilter = REPLACE(@sqlfilter,'(IsShowZooRow=1)','1=1')
	set @wherestr = ' and (' + @sqlfilter + ')' ;  
	set @IsShowZooRow = 1
end
else     
begin        
  set @wherestr = ' and (' + @sqlfilter + ')' ;        
end     
        
--分支过滤        
declare @shopstr1 varchar(80), @shopstr2 varchar(80)        
if @ShopID>0        
begin        
  set @shopstr1 = ' and StoreID in (select StoreID from l_store where ShopID = ' + cast(@ShopID as varchar) + ')'        
  set @shopstr2 = ' and ShopID = ' + cast(@ShopID as varchar)        
end else        
  select @shopstr1 = '', @shopstr2 = ''        
        
--仓库过滤        
if(@inStoreID>0)        
  set @wherestr = @wherestr + ' and StoreID = ' + cast(@inStoreID as varchar)        
        
--求进销存期间@PosDate        
declare @StartYear Integer, @StartMonth Integer, @PosDate varchar(10)        
select @StartYear = Year(datavalue) from am_sysparam where paramid=3001        
select @StartMonth = Month(datavalue) from am_sysparam where paramid=3001        
select @PosDate = convert(varchar(10), str(@StartYear,4)+'-'+str(@StartMonth,2)+'-01') 

--生产日期过滤
declare @pwherestr varchar(4000)
set @pwherestr = ''
if(len(@wherestr) > 0)
 set @pwherestr = replace(@wherestr,'produceddate','productdate')          
        
------------------------------------------------------------        
--向表#temp插入数据        
--期初结存1：进销存期间期初        
set @sqlstr =        
'insert into #temp        
Select GoodsID As ID, 100 As IOFlag, BatchCode, StoreID,Sum(Quantity) As Qty, Sum(Amount) As Amt         
From VIEW_BILLFLOWBATCH D          
Where BillType < 0 And BatchCode <> '''' And BatchCode Is Not NULL And         
      BillDate < ''' + @PosDate + '''' + @shopstr1 + @wherestr        
+ ' Group By GoodsID,BillKind, BatchCode,StoreID '        
execute sp_executesql @sqlstr        

    
--期初结存2：进销存期间至当前期初发生        
set @sqlstr =        
'insert into #temp        
Select GoodsID As ID, Case BillKind WHEN 1 THEN 100 WHEN 2 THEN -100 END As IOFlag, BatchCode, m.StoreID,         
       Sum(Quantity) As Qty, Sum(D.Amount) As Amt        
From I_IOStore M Left Join view_IostoredetailBatch D On M.BillID = D.BillID          
Where M.BillID >= 0 And BillState = 1 And (D.Quantity <> 0 Or D.Quantity Is Not NULL) And        
      BillDate >= ''' + @PosDate + ''' and ' + ' BillDate < ''' + @begindate + '''' + @shopstr2 + @pwherestr         
+ ' Group By GoodsID,BillKind, BatchCode,m.StoreID '        
execute sp_executesql @sqlstr        
        
set @sqlstr =        
'insert into #temp        
Select GoodsID As ID, Case BillKind WHEN 1 THEN 1 WHEN 2 THEN -1 END As IOFlag, BatchCode,m.StoreID,         
       Sum(Quantity) As Qty, Sum(D.Amount) As Amt        
From I_IOStore M Left Join view_IostoredetailBatch D On M.BillID = D.BillID          
Where M.BillID >= 0 And BillState = 1 And (D.Quantity <> 0 Or D.Quantity Is Not NULL) And        
      BillDate >= ''' + @begindate + ''' and ' + ' BillDate <= ''' + @enddate + '''' + @shopstr2 + @pwherestr         
+ ' Group By GoodsID,BillKind, BatchCode,m.StoreID '        
execute sp_executesql @sqlstr        
        
        
--计算期初、本期的收入、发出数量、金额        
declare @ID Integer,  @IOFlag Integer, @batchcode varchar(64),@StoreID integer,        
        @LastQty decimal(22,8), @LastAmt decimal(18,2), @Qty decimal(22,8), @Amt decimal(18,2)        
select  @ID=0, @IOFlag=0, @BatchCode='', @LastQty=0, @LastAmt=0, @Qty=0, @Amt=0        
        
declare @OldID Integer, @Oldbatchcode varchar(64),@OldStoreID integer,    
        @SumInQty decimal(22,8), @SumInAmt decimal(18,2), @SumOutQty decimal(22,8), @SumOutAmt decimal(18,2)        
select  @OldID=0, @OldBatchCode='', @OldStoreID=0,@SumInQty=0, @SumInAmt=0, @SumOutQty=0, @SumOutAmt=0        
        
declare cur_calc cursor local forward_only static read_only for        
Select * from #temp order by storeid,ID, BatchCode, IOFlag        
open cur_calc        
fetch next from cur_calc into        
      @ID, @IOFlag, @BatchCode, @StoreID,@Qty, @Amt        
      while @@fetch_status = 0        
      begin        
 if (@ID <> @OldID or @BatchCode <> @OldBatchCode or @StoreID <> @OldStoreID)        
 begin        
          select @OldID = @ID, @OldBatchCode = @BatchCode,@OldStoreID=@StoreID       
          select @LastQty=0, @LastAmt=0, @SumInQty=0, @SumInAmt=0, @SumOutQty=0, @SumOutAmt=0        
          insert into #table values(@OldID, '', '', '',null, '', @OldBatchCode, @OldStoreID,0, 0, 0, 0, 0, 0, 0, 0)        
 end        
        
        if @IOFlag = -100 --期初发出        
        begin        
          set @LastQty = @LastQty - @Qty        
          set @LastAmt = @LastAmt - @Amt        
          --更新期初结存        
          update #table set StartQty = @LastQty, StartAmt = @LastAmt        
          where ID = @ID and BatchCode = @BatchCode and StoreID=@StoreID       
        end else        
        if @IOFlag = 100  --期初收入        
        begin        
          set @LastQty = @LastQty + @Qty        
          set @LastAmt = @LastAmt + @Amt        
          --更新期初结存        
          update #table set StartQty = @LastQty, StartAmt = @LastAmt        
          where ID = @ID and BatchCode = @BatchCode and StoreID=@StoreID        
        end else        
        if @IOFlag = -1  --发出数量、金额        
        begin        
          set @SumOutQty = @SumOutQty + @Qty        
          set @SumOutAmt = @SumOutAmt + @Amt        
          update #table set OutQty = @SumOutQty, OutAmt = @SumOutAmt        
          where ID = @ID and BatchCode = @BatchCode and StoreID=@StoreID           
        end else        
        if @IOFlag =  0  --费用分摊金额        
        begin        
          set @SumInAmt = @SumInAmt + @Amt        
          update #table set InAmt = @SumInAmt        
          where ID = @ID and BatchCode = @BatchCode and StoreID=@StoreID           
        end else        
        if @IOFlag =  1  --收入数量、金额        
        begin        
          set @SumInQty = @SumInQty + @Qty        
          set @SumInAmt = @SumInAmt + @Amt        
          update #table set InQty = @SumInQty, InAmt = @SumInAmt        
          where ID = @ID and BatchCode = @BatchCode and StoreID=@StoreID           
        end        
        
        fetch next from cur_calc into @ID, @IOFlag, @BatchCode,@StoreID, @Qty, @Amt        
        
      end        
close cur_calc        
deallocate cur_calc        
--计算期初、本期的收入、发出数量、金额 end        
        
--计算期末结存数量、金额        
update #table        
set Code = G.Code, Name = G.Name, Specs = G.Specs, brandid = g.brandid,        
    Unit = (select unitname from l_goodsunit where goodsid = ID and isbase = 1),        
    EndQty = StartQty + InQty - OutQty, EndAmt = StartAmt + InAmt - OutAmt        
from l_goods G        
where #table.ID = G.GoodsID       
        
--合计        
if (exists(select top 1 1 from #table))        
insert into #table(ID, Name, StartQty, StartAmt, InQty, InAmt, OutQty, OutAmt, EndQty, EndAmt)         
select 99999, '合计', Sum(StartQty), Sum(StartAmt), Sum(InQty), Sum(InAmt), Sum(OutQty), Sum(OutAmt),         
       Sum(EndQty), Sum(EndAmt)          
from #table         
        
--返回结果集        

if(@IsShowZooRow = 1)
	begin
		select m.Code, m.Name, Specs, brd.name as brandname,Unit, BatchCode,st.name as storename,StartQty, InQty, OutQty, EndQty, m.ID,m.storeid        
		from #table m      
		left join l_brand brd on brd.brandid = m.brandid      
		left join l_store st on st.StoreID=m.StoreID 
		where (StartQty<>0 or InQty<>0 or OutQty<>0 or EndQty<>0)   
		order by ID   
	end
else
	begin  
		select m.Code, m.Name, Specs, brd.name as brandname,Unit, BatchCode,st.name as storename,StartQty, InQty, OutQty, EndQty, m.ID,m.storeid        
		from #table m      
		left join l_brand brd on brd.brandid = m.brandid      
		left join l_store st on st.StoreID=m.StoreID    
		order by ID        
	end  
--删除临时表        
drop table #temp        
drop table #table        
        
end