if exists(select 1 from sysobjects where name='p_calculate_init_settle')
  drop proc p_calculate_init_settle
go


create procedure p_calculate_init_settle 
(
  @oper_date	varchar(10),
  @branch_no	varchar(6) ,
  @item_no		varchar(40),
  @real_qty		numeric(16,4)
)
as
begin
  if not exists(select 1 from t_da_jxc_daysum 
                  where oper_date >@oper_date and 
                   branch_no = @branch_no and
                   item_no = @item_no )
     return
  if exists(select 1 from sysobjects where name='#temp_jxc_daysum' and type='U')
     drop table #temp_jxc_daysum
  create table #temp_jxc_daysum
  (
     flow_id numeric(16,0) identity,
     oper_date varchar(10),
     branch_no varchar(6),
     item_no  varchar(40),
     cost_price numeric(16,4),
     init_qty numeric(16,4),
     init_amt numeric(16,4),
     init_amt2 numeric(16,4),
     settle_qty numeric(16,4),
     settle_amt numeric(16,4),
     settle_amt2 numeric(16,4)
  )
  declare @flow_id int,
          @max_flow_id int,
          @init_qty numeric(16,4),
          @init_amt numeric(16,4),
          @init_amt2 numeric(16,4),
          @settle_qty numeric(16,4),
          @settle_amt numeric(16,4),
          @settle_amt2 numeric(16,4),
          @sale_price numeric(16,4),
          @cost_price numeric(16,4)
insert into #temp_jxc_daysum(oper_date,branch_no,item_no,cost_price,init_qty,init_amt,
     init_amt2,settle_qty,settle_amt,settle_amt2)
    select oper_date,branch_no,item_no,cost_price,init_qty,init_amt,
     init_amt2,settle_qty,settle_amt,settle_amt2
    from t_da_jxc_daysum 
    where oper_date >@oper_date and 
          branch_no = @branch_no and
          item_no = @item_no
    order by oper_date asc
    select @max_flow_id = MAX(flow_id) from #temp_jxc_daysum
    select @flow_id = 1
    while @flow_id <= @max_flow_id
    begin
       if @flow_id = 1 
          select @init_qty = settle_qty,@init_amt = settle_amt,@init_amt2 = settle_amt2 
          from t_da_jxc_daysum
            where oper_date = @oper_date and 
                  branch_no = @branch_no and
                  item_no = @item_no
       else
          select @init_qty = settle_qty,@init_amt = settle_amt,@init_amt2 = settle_amt2 
          from #temp_jxc_daysum
            where flow_id = @flow_id - 1
       
       select @settle_qty = settle_qty ,@settle_amt = settle_amt,@settle_amt2 = settle_amt2,@cost_price = cost_price 
       from #temp_jxc_daysum
       where flow_id = @flow_id
       
       if @settle_qty <> 0 
          select @sale_price = @settle_amt2 / @settle_qty
       else
          select @sale_price from t_bd_item_info where item_no = @item_no 
          
       select @settle_qty = @settle_qty - @real_qty
       select @settle_amt = @settle_qty * @cost_price
       select @settle_amt2 = @settle_qty * @sale_price
       
       update #temp_jxc_daysum set init_qty = @init_qty,init_amt = @init_amt,init_amt2 = @init_amt2,
              settle_qty = @settle_qty ,settle_amt = @settle_amt,settle_amt2 =@settle_amt2
          where flow_id = @flow_id
       select @flow_id = @flow_id + 1
    end
    update t_da_jxc_daysum set init_qty = a.init_qty,init_amt = a.init_amt,init_amt2 = a.init_amt2,
           settle_qty = a.settle_qty,settle_amt = a.settle_amt,settle_amt2=a.settle_amt2
           from #temp_jxc_daysum a
           where t_da_jxc_daysum.oper_date = a.oper_date and
                 t_da_jxc_daysum.branch_no = a.branch_no and
                 t_da_jxc_daysum.item_no = a.item_no
  drop table #temp_jxc_daysum
end
go
