if exists(select 1 from sysobjects where name='tr_wm_sheet_master')
  drop Trigger tr_wm_sheet_master
go

create trigger tr_wm_sheet_master on t_wm_sheet_master 
for update 
as
begin
  if not update(approve_flag) return

  declare
    @sheet_no  	     char(18) ,--工作单号
    @trans_no        char(2)         ,--库存操作方式
    @db_no           char(1)      ,--出入库方式
    @voucher_no      char(18)         ,--原始凭证号
    @branch_no       char(6)        ,--本店代码
    @supcust_no      char(8)        ,--供应商客户代码
    @pay_way         char(3)         ,--付款方式
    @discount        numeric(5,4)   ,
    @coin_no         char(3)         ,--币种
    @order_status    char(1),
    @approve_flag    char(1)      ,--审批标志  --0-未审核，1-审核
    @oper_id         char(4),
    @order_man       char(4),
    @confirm_man     char(4),
    @oper_date       datetime   ,--操作日期
    @work_date       datetime   ,--审核日期
    @valid_date      datetime   ,--订单有效期
    @pay_date        datetime   ,--付款日期
    @sale_way        char(1),
    @trans_flag      char(1),
    @acct_flag       char(1),
    @stock_qty	     numeric(16,4),--库存
    @item_no         char(20)        ,--货品代码
    @order_qty       numeric(16,4)   ,--数量
    @real_qty        numeric(16,4)   ,--数量
    @send_qty        numeric(16,4)   ,--赠品数量
    @orgi_price      numeric(16,4)   ,--进货单价
    @valid_price     numeric(16,4)   ,--零售单价
    @cost_price      numeric(16,4)   ,--零售单价
    @sale_price      numeric(16,4)   ,--零售单价
    @sub_amt         numeric(16,4),
    @tax             numeric(5,4),
    @total_amt       numeric(16,4),   --金额
    @old_trans       char(2),
    @so_voucher_no   char(18) ,       --原始凭证号
    @ln_dctAmt       numeric(16,4),
    @ls_operDate     char(10),
    @ln_discountAmt  numeric(16,4),
    @ls_saleWay      char(1),      -- 销售方式
    @sheet_no_ac     char(18) ,--工作单号
    @trans_no_ac     char(2)         ,--库存操作方式
    @oper_date_ac    datetime   ,--操作日期
    @pay_date_ac     datetime   ,--付款日期
    @supcust_no_ac   char(8) ,       --供应商客户代码
    @stock_cost      numeric(16,4),  --成本价格
    @change_price    char(1),
    @stock_money     numeric(16,4)  --成本金额

  declare sm_cur_1 cursor for
    select a.sheet_no,a.trans_no,a.db_no,isnull(a.voucher_no,''),a.branch_no,
        isnull(a.supcust_no,''),isnull(a.pay_way,'A'),a.discount,isnull(a.coin_no,'RMB'),
        a.order_status,a.approve_flag,
        a.oper_id,a.order_man,a.confirm_man,
        a.oper_date,a.work_date,a.valid_date,a.pay_date,
        a.trans_flag,a.acct_flag,
        b.item_no,b.order_qty,b.real_qty,b.orgi_price,b.valid_price,
        b.sub_amt,b.tax,b.sale_price,b.send_qty
    from inserted a, t_wm_sheet_detail b
    where ( a.sheet_no = b.sheet_no )
      and (a.approve_flag = '1') and  (b.real_qty<>0 or send_qty<>0) --单据数量不得为零

    select @total_amt=0
    select @ln_discountAmt =0

  open sm_cur_1
  fetch sm_cur_1 into
        @sheet_no,@trans_no,@db_no,@voucher_no,@branch_no,
        @supcust_no,@pay_way, @discount,@coin_no,
        @order_status,@approve_flag,
        @oper_id,@order_man,@confirm_man,
        @oper_date,@work_date,@valid_date,@pay_date,
        @trans_flag,@acct_flag,
        @item_no,@order_qty,@real_qty,@orgi_price,@valid_price,
        @sub_amt,@tax,@sale_price,@send_qty
    
  while @@fetch_status=0
  begin

    select @sheet_no_ac = @sheet_no
    select @trans_no_ac = @trans_no
    select @oper_date_ac = @oper_date 
    select @pay_date_ac  = @pay_date
    select @supcust_no_ac = @supcust_no

    select @change_price = isnull(change_cost,'0') from t_sys_sheet_no where sheet_id = @trans_no

    select @stock_qty = null
    select @stock_money = null
    select @stock_cost = null
    select @stock_qty = stock_qty, @stock_cost = avg_cost ,@stock_money = cost_amt
      from t_im_branch_stock where branch_no = @branch_no and item_no = @item_no

    if @stock_qty is null
	select @stock_qty = 0


    if @trans_no='SO' --销售出库
    begin
      if @voucher_no <> ''
      begin
        --修改销售定单到货数量order_qty、状态
  
    	UPDATE t_wm_sheet_detail SET order_qty = isnull(order_qty,0) + isnull(@real_qty,0) --add hehf by 20100604
          where  sheet_no = @voucher_no and item_no = @item_no
        if @@error != 0 goto myerror
  
        IF not exists(select sheet_no from t_wm_sheet_detail
          where sheet_no = @voucher_no and real_qty > order_qty group by sheet_no)
          UPDATE t_wm_sheet_master  SET order_status = '2' where sheet_no = @voucher_no
        else
          UPDATE t_wm_sheet_master  SET order_status = '1' where sheet_no = @voucher_no
        if @@error != 0 goto myerror
      end 
      --维护供应商客户与商品关系
      if exists(select 1 from t_bd_supcust_item where supcust_no=@supcust_no and item_no=@item_no)
        update t_bd_supcust_item set
          top_price=(case when top_price<@valid_price then @valid_price else top_price end),
          --现有最低价为0或NULL的情况，应用当前业务价格代换
          bottom_price=(case when (bottom_price>@valid_price) or bottom_price=0 or bottom_price is null then @valid_price else bottom_price end),
          last_price=@valid_price
          where supcust_no=@supcust_no and item_no=@item_no
      else
        insert into t_bd_supcust_item(supcust_no,item_no,top_price,bottom_price,last_price)
          values(@supcust_no,@item_no,@valid_price,@valid_price,@valid_price)
    
      if @@error != 0 goto myerror

      --让利额
      select @ln_dctAmt=0
      select @ln_dctAmt=(sale_price - @valid_price)*@real_qty from t_bd_item_info where item_no=@item_no
      if @ln_dctAmt<0 or @ln_dctAmt is null
         select @ln_dctAmt=0
         
      select @ls_operDate=convert(char(10),@work_date,121)

      select @total_amt=@total_amt+@real_qty*@valid_price
      select @ln_discountAmt = @ln_discountAmt + @ln_dctAmt  
    end
    else if @trans_no='RI'  --db_no='-'
    begin
      --出入库数量改为负数
      
      select @real_qty=-@real_qty 	--add hehf by 20100604
      select @send_qty = -@send_qty 	--add hehf by 20100604
      --让利额
      select @ln_dctAmt=0
      select @ln_dctAmt=(sale_price - @valid_price)* @real_qty from t_bd_item_info where item_no=@item_no
      if @ln_dctAmt<0 or @ln_dctAmt is null
         select @ln_dctAmt=0
         
      select @ls_operDate=convert(char(10),@work_date,121)
         
      select @total_amt=@total_amt+@real_qty*@valid_price
      select @ln_discountAmt = @ln_discountAmt - @ln_dctAmt

    end
    
    /*取成本价已改为在t_im_flow表的插入时取，此处成本价取当前价
    if @change_price = '1'
	   begin
		if @stock_cost is null
	   	select @stock_cost = @valid_price
	   else
	   	if @stock_qty + @real_qty <> 0
	   	select @stock_cost = ( @stock_money + @real_qty * @valid_price )/(@stock_qty + @real_qty )
	   end
    else
	   if @stock_cost is null
	      select @stock_cost = isnull(price,0) from t_bd_item_info where item_no = @item_no  
     */
     select @cost_price = @valid_price
     

if upper(@trans_no) = 'SS'
begin
  close sm_cur_1
  deallocate sm_cur_1
  return
end    
	
      -- 判断是否为促销组合商品，是则将成份商品写流水
      if exists(select 1 from t_bd_item_info where item_no=@item_no and combine_sta ='1')
      begin 
         exec pr_combitem_flow @work_date,@item_no,@real_qty,@valid_price,@branch_no,@oper_id,@sheet_no,'A'
         if @@error != 0 return		 
      end
      else
      begin
      Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,sale_price,
	    sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id,sale_way,
	    pre_qnty,valid_date,memo,acc_date)
	values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@cost_price,@sale_price,@real_qty*@valid_price,
	        @branch_no,'',@trans_no,'-',@supcust_no,@oper_id,'A',
		@stock_qty - @real_qty , null,@voucher_no,@pay_date)
	 
	--add hehf by 20100604
       if @@error != 0 goto myerror
	end 	
       if @send_qty<>0 
	 begin
		if exists(select 1 from t_bd_item_info where item_no=@item_no and combine_sta ='1')
	        begin
	           exec pr_combitem_flow @work_date,@item_no,@send_qty,@valid_price,@branch_no,@oper_id,@sheet_no,'C'
	           if @@error != 0 return
	        end
	else
          begin
		Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,sale_price,
			sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id,sale_way,
	pre_qnty,valid_date,memo,acc_date)
		values(@sheet_no,@work_date,@item_no,@send_qty,0,@cost_price,@sale_price,0,
			@branch_no,'',@trans_no,'-',@supcust_no,@oper_id,'A',
			@stock_qty - @send_qty, null,@voucher_no,@pay_date)
		
 	  end
	--add hehf by 20100604 end 

       if @@error != 0 goto myerror
	end
    fetch sm_cur_1 into
        @sheet_no,@trans_no,@db_no,@voucher_no,@branch_no,
        @supcust_no,@pay_way, @discount,@coin_no,
        @order_status,@approve_flag,
        @oper_id,@order_man,@confirm_man,
        @oper_date,@work_date,@valid_date,@pay_date,
        @trans_flag,@acct_flag,
        @item_no,@order_qty,@real_qty,@orgi_price,@valid_price,
        @sub_amt,@tax,@sale_price,@send_qty
  end

  close sm_cur_1
  deallocate sm_cur_1


  --应收账
  if @trans_no='SO' or @trans_no='RI'
  begin
    if @pay_date is null
      select @pay_date=getdate()

      insert into t_fm_recpay_flow(pay_type,voucher_no,trans_no,
        should_amt,tax_amt,sale_way,oper_date,settle_flag,supcust_no,pay_way,acc_date,acc_type)
       values(1, @sheet_no_ac, @trans_no_ac, @total_amt,0,'A',@oper_date_ac,'0',@supcust_no_ac,'A',@pay_date_ac,'C')
    if @@error != 0 goto myerror

  end

return
myerror:
  close sm_cur_1
  deallocate sm_cur_1
  rollback
  return
end
go
