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



Create procedure [dbo].pr_jxc_clsamt_tax 
	@as_from char(10),@as_to char(10),@as_branch varchar(6),@as_have_tax char(1) = '0' 
--exec pr_jxc_amt_tax @as_from='2015-04-01',@as_to='2015-04-30',@as_branch='01',@as_have_tax='0'   
--WITH ENCRYPTION
as
begin 
	 --@as_have_tax = '0' 不含税，@as_have_tax = '1' 含税
	 --上期结存 + 本期入 － 本期出 + 盘点盈亏 ＝ 本期结存   -----(管库存)
	 declare @ls_mindate varchar(10),  
	  	 @ls_maxdate varchar(10)  ,
		 @gl_itm_cls_nolen int
	select @gl_itm_cls_nolen = CONVERT(int,sys_var_value) from t_sys_system where sys_var_id = 'gl_itm_cls_nolen'
	create table #tab_result
			    (branch_no varchar(10) not null default '',
			     branch_name varchar(30) not null default '',
			     item_clsno varchar(12) not null default '',
			     item_clsname varchar(20) not null default '',
			     init_amt decimal(18,4) not null default 0,
				 in_amt decimal(18,4) not null default 0,
				 in_samt decimal(18,4) not null default 0,
				 mi_amt decimal(18,4) not null default 0,
				 mo_amt decimal(18,4) not null default 0,
				 out_samt decimal(18,4) not null default 0,
				 out_amt decimal(18,4) not null default 0,
				 check_amt decimal(18,4) not null default 0,
				 check_samt decimal(18,4) not null default 0,
				 real_init_amt decimal(18,4) not null default 0,
				 out_amt_stock decimal(18,4) not null default 0,
				 out_samt_stock decimal(18,4) not null default 0,
				 num2_amt decimal(18,4) not null default 0,
				 avg_cost_amt   decimal(18,4) not null default 0 ,
				 item_sup_flag varchar(2) not null default '' 
			     )


	  
	 select @ls_mindate = min(oper_date),  
	  	@ls_maxdate = max(oper_date)  
	 from t_da_jxc_daysum  
	  
	 if @as_from < @ls_mindate  
	 	 select @as_from = @ls_mindate  
	 if @as_to > @ls_maxdate  
	 	 select @as_to = @ls_maxdate  
	 --检索小于起始日期的最大日期的结存，作为本次报表的期初  
	 select   branch.branch_no, 
		  branch.branch_name,   
		  item.item_no, 
		  left(item.item_clsno,@gl_itm_cls_nolen) as item_clsno, 
		  (case daysum.purchase_tax when 0 then 0.17 else daysum.purchase_tax end ) purchase_tax,  
		  daysum.sale_tax,  
		   sum((case when item.item_stock = '1' then 0 else isnull(daysum.settle_qty,0) end)) init_qty,  
		  sum((case when item.item_stock = '1' then 0 else isnull(daysum.settle_amt,0) end)) init_amt,
          max(item.item_stock) as item_stock,
          max(item.item_sup_flag) as item_sup_flag    
	 into #tb_last  
	 from t_da_jxc_daysum daysum,t_bd_item_info item,t_bd_branch_info branch  
	 where daysum.branch_no like @as_branch   
	 and daysum.item_no = item.item_no  
	 and daysum.branch_no = branch.branch_no  
	 and daysum.oper_date =  
	 (  
	  select max(oper_date)  
	  from t_da_jxc_daysum  
	  where oper_date <= @as_to
	  and item_no = daysum.item_no  
	  and branch_no = daysum.branch_no  
	  )  
	 group by branch.branch_no, branch.branch_name,item.item_no,item.item_clsno,daysum.purchase_tax,daysum.sale_tax   
 
	 --检索本次业务统计期间内，所有业务项的合计  
	 select branch.branch_no,   
		branch.branch_name,   
		item.item_no,  
		left(item.item_clsno,@gl_itm_cls_nolen) as item_clsno, 
		(case daysum.purchase_tax when 0 then 0.17 else daysum.purchase_tax end ) purchase_tax,   
		daysum.sale_tax,  
		sum(isnull(daysum.pi_qty, 0)) pi_qty,  --采购 
		sum(isnull(daysum.pi_costamt, 0)) pi_amt,  
		sum(isnull(daysum.so_qty, 0)) so_qty,  --批发销售
		sum(isnull(daysum.so_costamt, 0)) so_amt,  
		sum(isnull(daysum.pos_qty, 0)) pos_qty, --pos销售 
		sum(isnull(daysum.pos_costamt, 0)) pos_amt,  
		sum(isnull(daysum.mi_qty, 0)) mi_qty,  --调入（影响成本）
		sum(isnull(daysum.mi_costamt, 0)) mi_amt,  
		sum(isnull(daysum.mo_qty, 0)) mo_qty,  --调出（影响成本）
		sum(isnull(daysum.mo_costamt, 0)) mo_amt,  
		sum(isnull(daysum.mi_qty2, 0)) mi_qty2,  --调入（视同销售）
		sum(isnull(daysum.mi_costamt2, 0)) mi_amt2,  
		sum(isnull(daysum.mo_qty2, 0)) mo_qty2,  --调出（视同销售）
		sum(isnull(daysum.mo_costamt2, 0)) mo_amt2,  
		sum(isnull(daysum.oo_qty, 0)) oo_qty,  --调整出库
		sum(isnull(daysum.oo_costamt, 0)) oo_amt,  
		sum(isnull(daysum.oi_qty, 0)) oi_qty,  --调整入库
		sum(isnull(daysum.oi_costamt, 0)) oi_amt,  
		sum(isnull(daysum.pd_qty, 0)) pd_qty,  --盘点
		sum(isnull(daysum.pd_costamt, 0)) pd_amt,  
		sum(isnull(daysum.lp_qty, 0)) lp_qty,  --礼品兑换
		sum(isnull(daysum.lp_costamt, 0)) lp_amt,  
		sum(isnull(daysum.ko_qty, 0)) ko_qty,  --领用出库
		sum(isnull(daysum.ko_costamt, 0)) ko_amt,  
		sum(isnull(daysum.jo_qty, 0)) jo_qty,  --报损
		sum(isnull(daysum.jo_costamt, 0)) jo_amt,   
		sum(isnull(daysum.kb_amt, 0)) kb_amt,   --扣补金额
		sum(isnull(daysum.pos_zs_amt, 0)) pos_zs_amt,   --前台赠送金额
		sum(isnull(daysum.pos_dct_amt, 0)) pos_dct_amt,  --前台折扣金额  
	    max(item.item_stock) as item_stock,
	    max(item.item_sup_flag) as item_sup_flag    ,   
		sum(isnull(daysum.avg_cost_amt, 0)) avg_cost_amt,  --销售成本额		
		sum(isnull(daysum.num2,0)) num2_amt --差异金额
	into #tb_now  
	from t_da_jxc_daysum daysum,  
		t_bd_item_info item,  
		t_bd_branch_info branch  
	where daysum.oper_date between @as_from and @as_to and   
		daysum.item_no = item.item_no and  
		daysum.branch_no = branch.branch_no and  
		daysum.branch_no like @as_branch  
	group by branch.branch_no, branch.branch_name,item.item_no,item.item_clsno,daysum.purchase_tax,daysum.sale_tax  
 
	  
		 --将业务发生数据转移到临时表中  
	select #tb_last.branch_no, 
		#tb_last.branch_name,   
		#tb_last.item_no,
		#tb_last.item_clsno,  
		#tb_last.purchase_tax,  
		#tb_last.sale_tax,  
		isnull(#tb_last.init_qty, 0) init_qty,  
		isnull(#tb_last.init_amt, 0) init_amt,  

		sum(isnull(#tb_now.pi_qty, 0)) pi_qty,  --采购 
		sum(isnull(#tb_now.pi_amt, 0)) pi_amt,  
		sum(isnull(#tb_now.so_qty, 0)) so_qty,  --批发销售
		sum(isnull(#tb_now.so_amt, 0)) so_amt,  
		sum(isnull(#tb_now.pos_qty, 0)) pos_qty, --pos销售 
		sum(isnull(#tb_now.pos_amt, 0)) pos_amt,  
		sum(isnull(#tb_now.mi_qty, 0)) mi_qty,  --调入（影响成本）
		sum(isnull(#tb_now.mi_amt, 0)) mi_amt,  
		sum(isnull(#tb_now.mo_qty, 0)) mo_qty,  --调出（影响成本）
		sum(isnull(#tb_now.mo_amt, 0)) mo_amt,  
		sum(isnull(#tb_now.mi_qty2, 0)) mi_qty2,  --调入（视同销售）
		sum(isnull(#tb_now.mi_amt2, 0)) mi_amt2,  
		sum(isnull(#tb_now.mo_qty2, 0)) mo_qty2,  --调出（视同销售）
		sum(isnull(#tb_now.mo_amt2, 0)) mo_amt2,  
		sum(isnull(#tb_now.oo_qty, 0)) oo_qty,  --调整出库
		sum(isnull(#tb_now.oo_amt, 0)) oo_amt,  
		sum(isnull(#tb_now.oi_qty, 0)) oi_qty,  --调整入库
		sum(isnull(#tb_now.oi_amt, 0)) oi_amt,  
		sum(isnull(#tb_now.pd_qty, 0)) pd_qty,  --盘点
		sum(isnull(#tb_now.pd_amt, 0)) pd_amt,  
		sum(isnull(#tb_now.lp_qty, 0)) lp_qty,  --礼品兑换
		sum(isnull(#tb_now.lp_amt, 0)) lp_amt,  
		sum(isnull(#tb_now.ko_qty, 0)) ko_qty,  --领用出库
		sum(isnull(#tb_now.ko_amt, 0)) ko_amt,  
		sum(isnull(#tb_now.jo_qty, 0)) jo_qty,  --报损
		sum(isnull(#tb_now.jo_amt, 0)) jo_amt,   
		sum(isnull(#tb_now.kb_amt, 0)) kb_amt,   --扣补金额
		sum(isnull(#tb_now.pos_zs_amt, 0)) pos_zs_amt,   --前台赠送金额
		sum(isnull(#tb_now.pos_dct_amt, 0)) pos_dct_amt,  --前台折扣金额 
	    max(#tb_last.item_stock) as item_stock,
	    max(#tb_last.item_sup_flag) as item_sup_flag    ,   
		sum(isnull(#tb_now.avg_cost_amt, 0)) avg_cost_amt,  --销售成本额	
		sum(isnull(#tb_now.num2_amt,0)) num2_amt --差异金额 
	into    #tb_main  
	from #tb_last left join #tb_now   
	on  #tb_last.branch_no = #tb_now.branch_no   
		and #tb_last.item_no = #tb_now.item_no  
		and #tb_last.purchase_tax = #tb_now.purchase_tax  
		and #tb_last.sale_tax = #tb_now.sale_tax  
	group by #tb_last.branch_no, #tb_last.branch_name,#tb_last.item_no,#tb_last.item_clsno,#tb_last.purchase_tax,#tb_last.sale_tax ,
		#tb_last.init_qty  ,#tb_last.init_amt 
	union  
	select #tb_now.branch_no,
		#tb_now.branch_name,   
		#tb_now.item_no,  
		#tb_now.item_clsno, 
		#tb_now.purchase_tax,  
		#tb_now.sale_tax,    
		isnull(#tb_last.init_qty, 0) init_qty,  
		isnull(#tb_last.init_amt, 0) init_amt,   
		sum(isnull(#tb_now.pi_qty, 0)) pi_qty,  --采购 
		sum(isnull(#tb_now.pi_amt, 0)) pi_amt,  
	
	sum(isnull(#tb_now.so_qty, 0)) so_qty,  --批发销售
		sum(isnull(#tb_now.so_amt, 0)) so_amt,  
		sum(isnull(#tb_now.pos_qty, 0)) pos_qty, --pos销售 
		sum(isnull(#tb_now.pos_amt, 0)) pos_amt,  
		sum(isnull(#tb_now.mi_qty, 0)) mi_qty,  --调入（影响成本）
		sum(isnull(#tb_now.mi_amt, 0)) mi_amt,  
		sum(isnull(#tb_now.mo_qty, 0)) mo_qty,  --调出（影响成本）
		sum(isnull(#tb_now.mo_amt, 0)) mo_amt,  
		sum(isnull(#tb_now.mi_qty2, 0)) mi_qty2,  --调入（视同销售）
		sum(isnull(#tb_now.mi_amt2, 0)) mi_amt2,  
		sum(isnull(#tb_now.mo_qty2, 0)) mo_qty2,  --调出（视同销售）
		sum(isnull(#tb_now.mo_amt2, 0)) mo_amt2,  
		sum(isnull(#tb_now.oo_qty, 0)) oo_qty,  --调整出库
		sum(isnull(#tb_now.oo_amt, 0)) oo_amt,  
		sum(isnull(#tb_now.oi_qty, 0)) oi_qty,  --调整入库
		sum(isnull(#tb_now.oi_amt, 0)) oi_amt,  
		sum(isnull(#tb_now.pd_qty, 0)) pd_qty,  --盘点
		sum(isnull(#tb_now.pd_amt, 0)) pd_amt,  
		sum(isnull(#tb_now.lp_qty, 0)) lp_qty,  --礼品兑换
		sum(isnull(#tb_now.lp_amt, 0)) lp_amt,  
		sum(isnull(#tb_now.ko_qty, 0)) ko_qty,  --领用出库
		sum(isnull(#tb_now.ko_amt, 0)) ko_amt,  
		sum(isnull(#tb_now.jo_qty, 0)) jo_qty,  --报损
		sum(isnull(#tb_now.jo_amt, 0)) jo_amt,   
		sum(isnull(#tb_now.kb_amt, 0)) kb_amt,   --扣补金额
		sum(isnull(#tb_now.pos_zs_amt, 0)) pos_zs_amt,   --前台赠送金额
		sum(isnull(#tb_now.pos_dct_amt, 0)) pos_dct_amt,  --前台折扣金额
		max(#tb_now.item_stock) as item_stock,
	    max(#tb_now.item_sup_flag) as item_sup_flag    ,   
		sum(isnull(#tb_now.avg_cost_amt, 0)) avg_cost_amt,  --销售成本额	
		sum(isnull(#tb_now.num2_amt,0)) num2_amt --差异金额
	from #tb_last right join #tb_now   
	on  #tb_last.branch_no = #tb_now.branch_no   
	and #tb_last.item_no = #tb_now.item_no  
	and #tb_last.purchase_tax = #tb_now.purchase_tax  
	and #tb_last.sale_tax = #tb_now.sale_tax   
	group by #tb_now.branch_no, #tb_now.branch_name,#tb_now.item_no,#tb_now.item_clsno,#tb_now.purchase_tax,#tb_now.sale_tax,
		#tb_last.init_qty  ,#tb_last.init_amt 

  ---init_amt  -(  in_amt  -  out_samt  -  out_amt  +  check_amt )
	 --计算合计 含税 
	if @as_have_tax = '0' -----youdp--2012-08-13-----------------

		insert into  #tab_result
					(branch_no,
					 branch_name,
					 item_clsno,
					 item_clsname,
					 init_amt ,
					 in_amt,
					 mi_amt ,
					 mo_amt ,
					 out_samt,
					 out_amt ,
					 check_amt ,
					 real_init_amt ,
					 out_amt_stock,
					 out_samt_stock,
					 num2_amt,
					 in_samt,
					 check_samt
					 )		
		select branch_no,
			   branch_name,
			   item_clsno,
			   item_clsname = (select item_clsname from t_bd_item_cls where item_clsno = tab_a.item_clsno),
		       sum(init_amt) as init_amt,
	           sum(case when item_stock = '1' then 0 else in_amt end) as in_amt,
			   sum(mi_amt) as mi_amt,
			   sum(mo_amt) as mo_amt,
			   sum(case when item_stock = '1' then 0 else out_samt end ) as out_samt,
			   sum(case when item_stock = '1' then 0 else out_amt end ) as out_amt,
			   sum(case when item_stock = '1' then 0 else check_amt end ) as check_amt,
			   sum(case when item_stock = '1' then 0 else (init_amt  - num2_amt - (  in_amt  -  out_samt  -  out_amt  +  check_amt - mo_amt + mi_amt )) end) as real_init_amt,
			   sum(case when item_stock = '1' then out_amt else 0 end ) as out_amt_stock,
			   sum(case when item_stock = '1' then out_samt else 0 end ) as out_samt_stock,
			   sum(case when item_stock = '1' then 0 else num2_amt end ) as num2_amt,
		       --sum(num2_amt) as num2_amt,  --zhongbin 2015-09-10 成本差异金额不受是否管理库存限制。
		       sum(case when item_stock = '1' then in_amt else 0 end) as in_samt,
		       sum(case when item_stock = '1' then check_amt else 0 end ) as check_samt
				
        from (
			select branch_no,
				branch_name, 
				item_clsno,
				sum(init_amt/(1+(case when isnull(purchase_tax,0)=0 then 0 else purchase_tax end))) init_amt,  
				sum((pi_amt)/(1+(case when isnull(purchase_tax,0)=0 then 0 else purchase_tax end))) in_amt,  --本期入库 (采购 )   
				sum((mi_amt + mi_amt2)/(1+(case when isnull(purchase_tax,0)=0 then 0 else purchase_tax end))) mi_amt,  --调入+ 库存调整调入
				sum((mo_amt + mo_amt2)/(1+(case when isnull(purchase_tax,0)=0 then 0 else purchase_tax end))) mo_amt,  --调出+ 库存调整调出 
				sum((oo_amt + ko_amt + lp_amt + jo_amt - oi_amt)/(1+(case when isnull(purchase_tax,0)=0 then 0 else purchase_tax end))) out_samt,  --本期出库 [库存调整调出 +  报损 + 领用 + 礼品兑换] 
				sum((case when item_sup_flag='C' then avg_cost_amt else (so_amt + pos_amt)end )/(1+(case when isnull(purchase_tax,0)=0 then 0 else purchase_tax end))) out_amt,  --联营则销售成本金额否则本期销售 (pos 销售 + 批发 )        
   				sum(pd_amt/(1+(case when isnull(purchase_tax,0)=0 then 0 else purchase_tax end))) check_amt,  --盘盈盘亏
			    max(item_stock) as item_stock,
			    sum(num2_amt/(1+(case when isnull(purchase_tax,0)=0 then 0 else purchase_tax end))) as num2_amt --成本差异金额
			from #tb_main  
			group by branch_no,branch_name,item_clsno,item_no
			) as tab_a
		group by  branch_no,branch_name,item_clsno
		order by branch_no,branch_name
	else
		insert into  #tab_result
				(branch_no,
				 branch_name,
				 item_clsno,
				 item_clsname,
				 init_amt ,
				 in_amt,
				 mi_amt ,
				 mo_amt ,
				 out_samt,
				 out_amt ,
				 check_amt ,
				 real_init_amt ,
				 out_amt_stock,
				 out_samt_stock,
				 num2_amt,
				 in_samt,
				 check_samt
				 )	
		select branch_no,
			   branch_name,
			   item_clsno,
			   item_clsname = (select item_clsname from t_bd_item_cls where item_clsno = tab_a.item_clsno),
		       sum(init_amt) as init_amt,
	           sum(case when item_stock = '1' then 0 else in_amt end) as in_amt, 
			   sum(mi_amt) as mi_amt,
			   sum(mo_amt) as mo_amt,
			   sum(case when item_stock = '1' then 0 else out_samt end ) as out_samt,
			   sum(case when item_stock = '1' then 0 else out_amt end ) as out_amt,
			   sum(case when item_stock = '1' then 0 else check_amt end ) as check_amt,
			   sum(case when item_stock = '1' then 0 else (init_amt  - num2_amt - (  in_amt  -  out_samt  -  out_amt  +  check_amt - mo_amt + mi_amt )) end) as real_init_amt,
			   sum(case when item_stock = '1' then out_amt else 0 end ) as out_amt_stock,
			   sum(case when item_stock = '1' then out_samt else 0 end ) as out_samt_stock,
			   sum(case when item_stock = '1' then 0 else num2_amt end ) as num2_amt,
		       --sum(num2_amt) as num2_amt,  --zhongbin 2015-09-10 成本差异金额不受是否管理库存限制。
		       sum(case when item_stock = '1' then in_amt else 0 end) as in_samt,
		       sum(case when item_stock = '1' then check_amt else 0 end ) as check_samt
		 
		from (
				select branch_no,
					branch_name,
					item_clsno, 
					sum(init_amt) as init_amt,  
					sum(pi_amt) in_amt,  --本期入库 (采购 )
					sum(mi_amt + mi_amt2) mi_amt,  --调入+ 库存调整调入 
					sum(mo_amt + mo_amt2) mo_amt,  --调出+ 库存调整调出 
					sum(oo_amt + ko_amt + lp_amt + jo_amt - oi_amt) out_samt,  --本期出库 [库存调整调出 +  报损 + 领用 + 礼品兑换] 
					 sum(case when item_sup_flag='C' then avg_cost_amt else (so_amt + pos_amt)end) out_amt,  --联营直接取avg_cost_amt否则本期销售 (pos 销售 + 批发 ) 
					sum(pd_amt) check_amt,  --盘盈盘亏
					max(item_stock) as item_stock,
					sum(num2_amt) as num2_amt --成本差异金额
				from #tb_main  
				group by branch_no,branch_name,item_clsno, item_no
				) as tab_a
		group by branch_no,branch_name,item_clsno
		order by branch_no,branch_name


	select branch_no,
		   branch_name,
		   item_clsno,
		   item_clsname,
          (case when abs(round(init_amt,4)) = 0 then 0 else init_amt end) as init_amt,
		  (case when abs(round(in_amt,4)) = 0 then 0 else in_amt end) as in_amt,
		  (case when abs(round(mi_amt,4)) = 0 then 0 else mi_amt end) as mi_amt,
		  (case when abs(round(mo_amt,4)) = 0 then 0 else mo_amt end) as mo_amt,
		  (case when abs(round(out_samt,4)) = 0 then 0 else out_samt end) as out_samt,
		  (case when abs(round(out_amt,4)) = 0 then 0 else out_amt end) as out_amt,
		  (case when abs(round(check_amt,4)) = 0 then 0 else check_amt end) as check_amt,
		  (case when abs(round(real_init_amt,4)) = 0 then 0 else real_init_amt end) as real_init_amt,
	      (case when abs(round(out_amt_stock,4)) = 0 then 0 else out_amt_stock end) as out_amt_stock,
		  (case when abs(round(out_samt_stock,4)) = 0 then 0 else out_samt_stock end) as out_samt_stock,
	      (case when abs(round(num2_amt,4)) = 0 then 0 else num2_amt end) as num2_amt,
	      (case when abs(round(in_samt,4)) = 0 then 0 else in_samt end) as in_samt,
	      (case when abs(round(check_samt,4)) = 0 then 0 else check_samt end) as check_samt
	from #tab_result
	order by branch_no,branch_name
	drop table #tab_result
end 
go
