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

create procedure pr_rm_daysum
as
begin
declare @ls_branchNO char(6),     -- 仓库编码
    @ls_itemNO char(20),        -- 商品编码
    @ls_operDate char(10),      -- 销售日期
    @ln_saleQty numeric(16,4),    -- 销售数量
    @ln_retQty numeric(16,4),   -- 退货数量
    @ln_givQty numeric(16,4),   -- 赠送数量
    @ln_saleAmt numeric(16,4),    -- 销售金额
    @ln_retAmt numeric(16,4),   -- 退货金额
    @ln_givAmt numeric(16,4),   -- 赠送金额
    @ln_salePrc numeric(16,4),    -- 销售单价
    @ln_preQty numeric(16,4),   -- 上次冲减数量
    @ln_preAmt numeric(16,4),   -- 上次冲减金额
    @ln_curQty numeric(16,4),   -- 本次应该冲减数量
    @ln_curAmt numeric(16,4),   -- 本次应该冲减金额
    @ln_curPrc numeric(16,4),   -- 本次冲减单价
    @ldt_operDate datetime  ,   -- 销售日期(dateTime型)
    @ln_curstock numeric(16,4)  , --current stock
    @ln_temp numeric(16,4),   --current stock
    @t_settleqty numeric(16,4),
    @t_chardate char(10),
    @t_branch   varchar(10),
    @chr_mindate varchar(10),
    @supcust_no  varchar(10),
    @sale_way   varchar(8),
    @sup_ly_rate numeric(16,4),
    @flow_id     numeric(16),
    @ln_costPrc numeric(16,4),
    @ln_tmpPrc numeric(16,4),
    @purchase_tax			NUMERIC(4,2),   --进项税
	@sale_tax			NUMERIC(4,2)  --销项锐


  -- 需做日结的仓库-商品(cursor): 本次应该冲减数量 <> 0
  -- 只限于本机构的仓库
  -- 在总部还包括托管仓库
  -- Modified by huangc  2005-01-10  POS分部
  if (select sys_var_value from t_sys_system where lower(sys_var_id)='is_distributed') = '1' 
  begin
      -- POS 让利汇总----------------------------------------------开始
      -- 商品让利汇总统计计算 Modified by wujs 2005-02-22
      -- (pre_qty <> sale_qty-ret_qty+giv_qty OR pre_amt <> sale_amt-ret_amt)
      select @chr_mindate = min(oper_date) from t_rm_daysum
          where (other1 is null or other1 = '0') and 
                 exists
                   (SELECT branch_no FROM t_bd_branch_info  
                     	WHERE ( t_bd_branch_info.branch_no = t_rm_daysum.branch_no ) and
			      ( t_bd_branch_info.property = '2' or
                                ( t_bd_branch_info.property = '3' and 
                                 substring(branch_no,1,2) in (select branch_no FROM t_bd_branch_info WHERE property = '0' and (trade_type='0' or trade_type='4'))
                                 )
			       )
                   )
      if (@chr_mindate is not null) -- 有日结数据才处理
      begin
         -- 删除已有数据
         delete from t_da_discount_daysum where oper_date >= @chr_mindate and rtrim(branch_no) in 
             (select branch_no from t_bd_branch_info where property = '2' or
                     (property = '3' and substring(branch_no,1,2) in (select branch_no FROM t_bd_branch_info WHERE property = '0' and (trade_type='0' or trade_type='4')))
              )
      
          -- 重新汇总POS让利  
          insert into t_da_discount_daysum (branch_no,item_no,oper_date,dct_type,sale_qty,sale_amt,dct_amt)
             select branch_no,item_no,convert(char(10),oper_date,120),
					(case when max(sell_way) = 'C' then 'zs' else spec_flag end) as spec_flag,
                    sum(case sell_way when 'B' then -sale_qnty else sale_qnty end),
                    sum(case sell_way when 'B' then -sale_money when 'C' then 0 else sale_money end),
                    sum(case sell_way when 'B' then -pref_amt when 'C' then (case when sale_money = 0 then pref_amt else sale_money end) else pref_amt end)
	      from t_rm_saleflow where (oper_date >= convert(datetime,@chr_mindate + ' 00:00:00', 120)) and 
	          ((spec_flag is not null and ltrim(spec_flag) > '') or sell_way = 'C') and
                  (rtrim(branch_no) in   (
                      SELECT branch_no FROM t_bd_branch_info WHERE property = '2' or
                            ( property = '3' and 
                              substring(branch_no,1,2) in (select branch_no FROM t_bd_branch_info WHERE property = '0' and (trade_type='0' or trade_type='4'))
                             )  ) )
               group by branch_no,item_no,convert(char(10),oper_date,120),spec_flag
      end -- End With (@chr_mindate is not null)
      -- POS 让利汇总----------------------------------------------结束
        
      -- (pre_qty <> sale_qty-ret_qty+giv_qty OR pre_amt <> sale_amt-ret_amt)
      declare lcur_toBeDaySumed cursor for 
        select flow_id,branch_no,item_no,oper_date,sale_qty,ret_qty,giv_qty,
               sale_amt,ret_amt,giv_amt,sale_price,pre_qty,pre_amt,sup_ly_rate
            from t_rm_daysum
            where (other1 is null or other1 = '0') and 
                   rtrim(branch_no) in 
                   (SELECT branch_no FROM t_bd_branch_info  
                     	WHERE property = '2' or
                            ( property = '3' and 
                              substring(branch_no,1,2) in (select branch_no FROM t_bd_branch_info WHERE property = '0' and (trade_type='0' or trade_type='4'))
                             )
                   )  for read only
  end
  else
  begin
      select @t_branch=sys_var_value+'%' from t_sys_system where sys_var_id='g_branch_no'
      
      select @chr_mindate = min(oper_date) from t_rm_daysum
        where branch_no like @t_branch and (other1 is null or other1 = '0')
        -- (pre_qty <> sale_qty-ret_qty+giv_qty OR pre_amt <> sale_amt-ret_amt)

      if (@chr_mindate is not null) -- 有日结数据才处理
      begin
         delete from  t_da_discount_daysum where oper_date >= @chr_mindate and branch_no like @t_branch
      
         insert into t_da_discount_daysum (branch_no,item_no,oper_date,dct_type,sale_qty,sale_amt,dct_amt)
            select branch_no,item_no,convert(char(10),oper_date,120),
				(case when max(sell_way) = 'C' then 'zs' else spec_flag end) as spec_flag,
               sum(case sell_way when 'B' then -sale_qnty else sale_qnty end),
               sum(case sell_way when 'B' then -sale_money when 'C' then 0 else sale_money end),
               sum(case sell_way when 'B' then -pref_amt when 'C' then (case when sale_money = 0 then pref_amt else sale_money end) else pref_amt end)
           from t_rm_saleflow where (oper_date >= convert(datetime,@chr_mindate + ' 00:00:00', 120) ) and 
	       ((spec_flag is not null and ltrim(spec_flag) > '') or sell_way = 'C') and
               (branch_no like @t_branch)
           group by branch_no,item_no,convert(char(10),oper_date,120),spec_flag
      end -- End With (@chr_mindate is not null)

      -- (pre_qty <> sale_qty-ret_qty+giv_qty OR pre_amt <> sale_amt-ret_amt)
      declare lcur_toBeDaySumed cursor for
      select flow_id,branch_no,item_no,oper_date,sale_qty,ret_qty,giv_qty,
             sale_amt,ret_amt,giv_amt,sale_price,pre_qty,pre_amt,sup_ly_rate
         from t_rm_daysum
         where branch_no like @t_branch and (other1 is null or other1 = '0')
        for read only
  end
  
-- 打开
  open lcur_toBeDaySumed
  if @@error != 0 goto myerror
  while 1=1
  begin
    -- 读取
    fetch lcur_toBeDaySumed into
      @flow_id,
      @ls_branchNO,
      @ls_itemNO,
      @ls_operDate,
      @ln_saleQty,
      @ln_retQty,
      @ln_givQty,
      @ln_saleAmt,
      @ln_retAmt,
      @ln_givAmt,
      @ln_salePrc,
      @ln_preQty,
      @ln_preAmt,
      @sup_ly_rate
    if @@error != 0 goto myerror
    if @@fetch_Status=0  -- 读取成功
    begin
		--chenhd 2017-09-19，取商品档案的进项税和销项税
		select @purchase_tax = purchase_tax, @sale_tax = sale_tax
		from t_bd_item_info
		where item_no = @ls_itemNO
        -- 本次应该冲减(数量/金额) = (前台销售-前台退货+前台赠送) - 上次冲减
        select @ln_curQty = (@ln_saleQty-@ln_retQty+@ln_givQty)-@ln_preQty
        select @ln_curAmt = (@ln_saleAmt-@ln_retAmt)-@ln_preAmt   -- 赠送不计金额
        
        if round(@ln_curQty,4) <> 0 OR round(@ln_curAmt,4) <> 0
        begin
            -- 转换日期时间格式: 121=yyyy-mm-dd , 销售发生日期+日结的当前时间
            select @ldt_operDate=convert(datetime,convert(char(11),convert(datetime,@ls_operDate,121),109)+' '+convert(char(8),getdate(),108),109)
       
     
            if @ln_curQty <> 0
            begin
                -- 本次冲减单价 = 本次应该冲减金额 / 本次应该冲减数量
                select @ln_curPrc = @ln_curAmt / @ln_curQty
                
                -- 判断是否为生鲜分割商品，是则将原料商品写流水
                declare @ls_itemNO_fg varchar(20)
                select @ls_itemNO_fg =''
                select @ls_itemNO_fg=isnull(comb_item_no,''),@ln_costPrc=t_bd_item_info.price from t_bd_item_combsplit,t_bd_item_info where t_bd_item_info.item_no=t_bd_item_combsplit.comb_item_no and t_bd_item_combsplit.item_no =@ls_itemNO and t_bd_item_info.combine_sta='6'
                if @ls_itemNO_fg <>'' and @ls_itemNO_fg is not null
                begin
                    --modified by wujs 2006-01-06
                    if exists(select 1 from t_pc_branch_price where branch_no = substring(@ls_branchNO,1,2) and item_no = @ls_itemNO_fg)
	                    select @supcust_no = supcust_no,@sale_way=sale_way from t_pc_branch_price
	                    where branch_no = substring(@ls_branchNO,1,2) and item_no = @ls_itemNO_fg
	            else
	                    select @supcust_no = main_supcust,@sale_way=item_sup_flag
            		    from t_bd_item_info where item_no = @ls_itemNO_fg
                    if (@sale_way = 'C' or @sale_way = 'F') --联营和扣率代销
                        insert t_im_flow(oper_date,item_no,real_qty,price,sheet_amt,branch_no,trans_no,db_no,
                                         oper_id,supcust_no,sale_way,sup_ly_rate,memo,costsheet_flag,purchase_tax,sale_tax)
                        values (@ldt_operDate,@ls_itemNO_fg,@ln_curQty,@ln_curPrc,@ln_curAmt,@ls_branchNO,'PT','-',
                                         '1001',@supcust_no,@sale_way,@sup_ly_rate,'分解'+substring(@ls_itemNO,1,13),'0',@purchase_tax,@sale_tax)
                    else
                        insert t_im_flow(oper_date,item_no,real_qty,price,sheet_amt,branch_no,trans_no,db_no,
                                         oper_id,supcust_no,memo,sale_way,costsheet_flag,purchase_tax,sale_tax)
                        values (@ldt_operDate,@ls_itemNO_fg,@ln_curQty,@ln_curPrc,@ln_curAmt,@ls_branchNO,'PT','-',
                                '1001',@supcust_no,'分解'+substring(@ls_itemNO,1,13),@sale_way,'0',@purchase_tax,@sale_tax)
                  if @@error != 0 goto myerror   
                  
                  select @ln_tmpPrc=avg_cost from t_im_branch_stock where branch_no=@ls_branchNO and item_no=@ls_itemno
                  if @ln_tmpPrc>0 select @ln_costPrc=@ln_tmpPrc
		  insert t_im_cs_flow(oper_date,branch_no,trans_type,item_no,sale_price,qty,cs_item_no,cs_sale_price,cs_cost_price,cs_qty)
		    values (@ls_operDate,@ls_branchNO,'6',@ls_itemno,@ln_curPrc,@ln_curQty,@ls_itemNO_fg,@ln_curPrc,@ln_costPrc,@ln_curQty)
		  if @@error != 0 goto myerror
                  
                end
                -- 判断是否为促销组合商品，是则将成份商品写流水
                -- 判断是否为生鲜加工商品，是则将成份商品写流水，如成分商品是生鲜分割品则处理同上
                else if exists(select 1 from t_bd_item_info where item_no=@ls_itemNO and combine_sta in ('7','1'))
                begin
                    exec pr_combitem_flow @ldt_operDate,@ls_itemNO,@ln_curQty,@ln_curPrc,@ls_branchNO
               	    if @@error != 0 goto myerror
                end
                else
                begin
                  --modified by wujs 2006-01-06
                  if exists(select 1 from t_pc_branch_price where branch_no = substring(@ls_branchNO,1,2) and item_no = @ls_itemNO)
	                    select @supcust_no = supcust_no,@sale_way=sale_way from t_pc_branch_price
	                    where branch_no = substring(@ls_branchNO,1,2) and item_no = @ls_itemNO
	          else
	                    select @supcust_no = main_supcust,@sale_way=item_sup_flag
            		    from t_bd_item_info where item_no = @ls_itemNO
          
                    if (@sale_way = 'C' or @sale_way = 'F') --联营和扣率代销
                        insert t_im_flow(oper_date,item_no,real_qty,price,sheet_amt,branch_no,trans_no,db_no,
                                         oper_id,supcust_no,sale_way,sup_ly_rate,memo,costsheet_flag,purchase_tax,sale_tax)
                        values  (@ldt_operDate,@ls_itemNO,@ln_curQty,@ln_curPrc,@ln_curAmt,@ls_branchNO,'PT','-',
                                         '1001',@supcust_no,@sale_way,@sup_ly_rate,'前台销售','0',@purchase_tax,@sale_tax)
                    else
                        insert t_im_flow(oper_date,item_no,real_qty,price,sheet_amt,branch_no,trans_no,db_no,
                                         oper_id,supcust_no,memo,sale_way,costsheet_flag,purchase_tax,sale_tax)
                        values  (@ldt_operDate,@ls_itemNO,@ln_curQty,@ln_curPrc,@ln_curAmt,@ls_branchNO,'PT','-',
                                         '1001',@supcust_no,'前台销售',@sale_way,'0',@purchase_tax,@sale_tax)
                    if @@error != 0 goto myerror
                end
          end
          else
          begin
                --modified by wujs 2006-01-06
                if exists(select 1 from t_pc_branch_price where branch_no = substring(@ls_branchNO,1,2) and item_no = @ls_itemNO)
	                select @supcust_no = supcust_no,@sale_way=sale_way from t_pc_branch_price
	                    where branch_no = substring(@ls_branchNO,1,2) and item_no = @ls_itemNO
	        else
	                select @supcust_no = main_supcust,@sale_way=item_sup_flag
	                from t_bd_item_info where item_no = @ls_itemNO
                if (@sale_way = 'C' or @sale_way = 'F') --联营和扣率代销
                    insert t_im_flow(oper_date,item_no,real_qty,price,sheet_amt,branch_no,trans_no,db_no,
                                     oper_id,supcust_no,sale_way,sup_ly_rate,memo,costsheet_flag,purchase_tax,sale_tax)
                    values (@ldt_operDate,@ls_itemNO,0,0,@ln_curAmt,@ls_branchNO,'PT','-',
                                     '1001',@supcust_no,@sale_way,@sup_ly_rate,'前台销售','0',@purchase_tax,@sale_tax)
                else
                    insert t_im_flow(oper_date,item_no,real_qty,price,sheet_amt,branch_no,trans_no,db_no,
                                     oper_id,supcust_no,memo,sale_way,costsheet_flag,purchase_tax,sale_tax)
                    values (@ldt_operDate,@ls_itemNO,0,0,@ln_curAmt,@ls_branchNO,'PT','-',
                                     '1001',@supcust_no,'前台销售',@sale_way,'0',@purchase_tax,@sale_tax)
                if @@error != 0 goto myerror
          end
          
          -- 更新前台商品销售日结表的上次冲减数量、金额
	  -- 赠送不计金额
	  -- other1 = '1' 记录已做日结处理 modified by wujs 2005-05-24
	  -- com_flag   = '0'  防止先上传后日结，导致总分部数据不一致  modified by shihn 2013-02-27
          update t_rm_daysum 
	    set pre_qty = @ln_preQty + @ln_curQty,
            pre_amt = @ln_preAmt + @ln_curAmt,    
            other1 = '1' , 
            com_flag   = '0'  
            where flow_id=@flow_id 
          -- branch_no=@ls_branchNO and item_no=@ls_itemNO and oper_date=@ls_operDate
          if @@error != 0 goto myerror
      end
    end
    else
    
    -- 读取失败，指针已到最后
    break
  end

myerror:
  close lcur_toBeDaySumed
  deallocate lcur_toBeDaySumed
  return
end
go