--解决销售时所属仓库错了
use tigerzz
go

if not object_id('tempdb..#a') is null
  drop table #a

declare @adt_from datetime, @adt_to datetime, @err_branch varchar(4), @right_branch varchar(4), @oper_id varchar(10), @machine_no char(2)
set @adt_from = '2020-06-14'                 /*开始日期*/
set @adt_to = '2020-06-16 23:59:59.9'        /*结束日期*/
set @err_branch = '08'                       /*错误仓库*/
set @right_branch = '12'                     /*正确仓库*/
set @oper_id = '1203'                        /*收银员编号*/
set @machine_no = '88'                       /*错误的收款机编号*/

/*假如该天已经日结，那么要先删除了*/
select * into #a from wh_inout_flow
where trans_no = 'S'
  and oper_date between @adt_from and @adt_to
  and (branch_no = @err_branch or branch_no = @right_branch)

update wh_stock set stock_qty = stock_qty + b.qty
  from (select branch_no, item_no, qty = sum(real_qty)
          from #a
         group by branch_no, item_no) b
 where wh_stock.branch_no = b.branch_no
   and wh_stock.item_no = b.item_no

delete wh_inout_flow
where trans_no = 'S'
  and oper_date between @adt_from and @adt_to
  and (branch_no = @err_branch or branch_no = @right_branch)

/*修改收银和销售流水数据*/
update pos_t_saleflow set branch_no = @right_branch
 where branch_no = @err_branch and oper_id = @oper_id
   and oper_date between @adt_from and @adt_to
   and flow_no like @machine_no + '%'

update pos_t_payflow set branch_no = @right_branch
 where branch_no = @err_branch and oper_id = @oper_id
   and oper_date between @adt_from and @adt_to
   and flow_no like @machine_no + '%'

/*删除销售汇总数据*/
delete pos_daysum
 where oper_date between @adt_from and @adt_to
   and (branch_no = @err_branch or branch_no = @right_branch)

/*重算销售汇总数据*/
alter table pos_daysum disable trigger all

insert pos_daysum(branch_no, item_no, oper_date, sale_qty, ret_qty, giv_qty, sale_amt, ret_amt,
giv_amt, sale_price, pre_qty, pre_amt, normal_sale_amt, spec_sale_amt, other1, other2, other3)
select branch_no, item_no, convert(char(10), oper_date, 120),
       sale_qty = sum(case when sale_way='A' then sale_qty else 0 end),
       ret_qty = sum(case when sale_way='B' then sale_qty else 0 end),
       giv_qty = sum(case when sale_way='C' then sale_qty else 0 end),
       sale_amt = sum(case when sale_way='A' then sale_amt else 0 end),
       ret_amt = sum(case when sale_way='B' then sale_amt else 0 end),
       giv_amt = sum(case when sale_way='C' then sale_amt else 0 end),
       sale_price = max(source_price),
       0, 0,
       normal_sale_amt = sum(case when sale_way = 'A' then 1 when sale_way = 'B' then -1 else 0 end
         * case when spec_flag not in ('4', '5', '6', '7', 'V', 'G') then sale_amt else 0 end),
       spec_sale_amt = sum(case when sale_way = 'A' then 1 when sale_way = 'B' then -1 else 0 end
         * case when spec_flag in ('4', '5', '6', '7', 'V', 'G') then sale_amt else 0 end),
       null, null, null
  from pos_t_saleflow
 where oper_date between @adt_from and @adt_to
   and (branch_no = @err_branch or branch_no = @right_branch)
 group by branch_no, item_no, convert(char(10), oper_date, 120)

alter table pos_daysum enable trigger all

/*结束*/