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


create procedure pr_rpt_branch_manage_analyse
(
 @month char(6),
 @branch_no varchar(8)
)

as
begin
   --select @month = '200905', @branch_no = '000001'
   declare  @item_clsno_len smallint
   
   select @item_clsno_len = convert(smallint,sys_var_value) from t_sys_system where sys_var_id = 'gl_itm_cls_nolen'
   select @item_clsno_len = isnull(@item_clsno_len, 2)
      
   --查询门店的销售额
   select c.item_clsno,
          sum(a.so_amt + a.pos_amt) as branch_sales_amt
   into #rpt_branch_manage_analyse2
     from t_da_jxc_month a,
          t_bd_item_info b,
          t_bd_item_cls c
    where a.item_no = b.item_no
      and c.item_clsno = substring(b.item_clsno, 1, @item_clsno_len)
      and left(a.branch_no,3) = left(@branch_no,3)
      and oper_date = @month
   group by c.item_clsno
   
   --客单数量
   select c.item_clsno,
          count(distinct a.branch_no + a.voucher_no) as billcnt
     into #rpt_branch_manage_analyse3
     from t_im_flow a,
          t_bd_item_info b,
          t_bd_item_cls c
    where a.item_no = b.item_no
      and (a.trans_no = 'SO' or a.trans_no = 'PT')
      and c.item_clsno = substring(b.item_clsno, 1, @item_clsno_len)
      and left(a.branch_no,3) = left(@branch_no,3)
      and oper_date >= left(@month,4) + '-' + right(@month,2) + '-01 00:00:00' 
      and oper_date <  dateadd(month, 1, left(@month,4) + '-' + right(@month,2) + '-01')
   group by c.item_clsno
   
   
   select cls.item_clsno,
          cls.item_clsname,          
          isnull(bb.branch_sales_amt,0) as branch_sales_amt,
          isnull(cc.billcnt,0)          as custnum
    from t_bd_item_cls cls
      left join #rpt_branch_manage_analyse2 as bb on cls.item_clsno = bb.item_clsno 
      left join #rpt_branch_manage_analyse3 as cc on cls.item_clsno = cc.item_clsno 
    where cls.cls_parent is null or cls.cls_parent = ''

   --客单金额 = 销售金额 / 客单数量
end
go