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

 create proc pr_wxsf_item_stockquery
 (
 @oper_type nvarchar(20),
 @item_no nvarchar(20),
 @branch_no nvarchar(20), --门店号
 @item_clsno nvarchar(20),
 @sale_qty numeric(16,4)
 )
  WITH ENCRYPTION
 as
 begin
   declare @item_sup_flag char(1)
   --@oper_type='0' 单品库存查询
   --如果一店多仓库，SUM stock_qty后返回.
   --要求列名[item_no],stock_qty,mng_stock
   --mng_stock '1'表示管理库存，‘0’表示不管理。若意义不同，请自行转义。
	if @oper_type='0' 
	begin
		--先检测商品是不是联营,如果是联营,则不管理库存,判断库存就没意义了 zhangyi 20200311
		select top 1 @item_sup_flag = item_sup_flag from t_bd_item_info where item_no = @item_no
		if @item_sup_flag = 'C' --联营
		begin
			--联营始终返回9999
			select @item_no as item_no,9999 as stock_qty,'0' as mng_stock
			return;
		end
		 
		declare @combine_sta char(1)
		select @combine_sta=combine_sta from t_bd_item_info where item_no= @item_no
		
		if @combine_sta='1'
			begin
				--检查成份商品是否充足
				declare @cnt int 
				select @cnt=count(1) from t_bd_item_combsplit b with(nolock)  
						left join t_im_branch_stock c with(nolock) on b.item_no =c.item_no and c.branch_no like @branch_no + '%'
						left join (select item_no,branch_no,sum(isnull(real_qty,0)) as real_qty from t_order_bill_weixin with(nolock)
							where (IsDownload IS NULL OR IsDownload = '0') and oper_date between convert(datetime, CONVERT(varchar(10), GETDATE() - 30, 120) + ' 00:00:00') and convert(datetime, CONVERT(varchar(10), GETDATE(), 120) + ' 23:59:59')
							group by item_no,branch_no
							) d on d.item_no =c.item_no and left(d.branch_no,2)=left(c.branch_no,2)
					where b.comb_item_no=@item_no and (isnull(c.stock_qty,0) - d.real_qty) < (b.item_qty *  @sale_qty) 

				if @cnt>0
				begin
					--成份商品库存不足
					select @item_no as item_no,0 as stock_qty,'1' as mng_stock
					return;
				end
				else				
				begin
					select @item_no as item_no,@sale_qty as stock_qty,'1' as mng_stock
					return;
				end
			end			
		else
			begin
				SELECT   
				a.[item_no], sum(isnull(b.stock_qty,0) - isnull(c.real_qty,0)) as stock_qty,case a.item_stock when '0' then '1' else '0' end as mng_stock   
					from t_bd_item_info  a  with(nolock)
					left join t_im_branch_stock  b with(nolock) on a.item_no =b.item_no and b.branch_no like @branch_no + '%'
					left join (select item_no,branch_no,sum(real_qty) as real_qty from t_order_bill_weixin with(nolock)
							where (IsDownload IS NULL OR IsDownload = '0') and oper_date between convert(datetime, CONVERT(varchar(10), GETDATE() - 30, 120) + ' 00:00:00') and convert(datetime, CONVERT(varchar(10), GETDATE(), 120) + ' 23:59:59')
							group by item_no,branch_no
							) c on b.item_no =c.item_no and left(c.branch_no,2)=left(b.branch_no,2)
					where a.item_no=@item_no  
				group by a.[item_no], a.item_stock
			end	
	end 
 end
go
