if exists(select 1 from sysobjects where name='pr_da_jxc_daysum_over')
  drop procedure pr_da_jxc_daysum_over
go

create procedure pr_da_jxc_daysum_over
  @branch_no  varchar(20)
as
begin
	--处理商品没有发生业务的商品也插入到毛利表里面
	declare @item_no varchar(30),
			@stock_qty decimal(16,4),
			@stock_amt decimal(16,4),
			@avg_price decimal(16,4),
			@price decimal(16,4),
			@base_price decimal(16,4),
			@sale_price decimal(16,4),
			@main_supcust varchar(10),
			@item_sup_flag varchar(4),
			@int int,
			@oper_date char(10)
	
	--首先找到没有毛利表里面没有的商品
	select @oper_date=convert(char(10),getdate(),120)
	declare cur_item cursor for 
			select item_no,price,base_price,sale_price,main_supcust,item_sup_flag 
			from t_bd_item_info with(nolock) 
			where item_stock='0' and NOT Exists(select item_no from t_da_jxc_daysum with(nolock) where branch_no=@branch_no and oper_date =@oper_date and  item_no = t_bd_item_info.item_no)
	open cur_item 
    fetch cur_item into @item_no,@price,@base_price,@sale_price,@main_supcust,@item_sup_flag
	while (@@fetch_Status = 0)
	begin
		select @stock_qty=sum(stock_qty),@stock_amt=sum(stock_qty * avg_cost)
		from t_im_branch_stock with(nolock) where item_no=@item_no and left(branch_no,3)=@branch_no
		
		
		if @stock_qty>0 
			select @avg_price = @stock_amt/@stock_qty
		else
			select @avg_price =@price 

		insert into t_da_jxc_daysum(item_no,oper_date,branch_no,cost_price,init_qty,init_amt,init_amt2,settle_qty,settle_amt,settle_amt2,main_sup,so_price,sale_price,item_sup_flag,ass_qty)
		values(@item_no,@oper_date,@branch_no,@avg_price,@stock_qty,@stock_amt,@stock_qty* @sale_price,@stock_qty,@stock_amt,@stock_qty* @sale_price,@main_supcust,@base_price,@sale_price,@item_sup_flag,@stock_qty)

		 fetch cur_item into @item_no,@price,@base_price,@sale_price,@main_supcust,@item_sup_flag
	end

	close cur_item
    deallocate cur_item  
	
end
go

