use tigerzz
go
print 'item'
go
if not object_id('tempdb..#t0') is null
  drop table #t0
select item_no into #t0
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.item
create index idx_t0 on #t0(item_no)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.item
select f.*
  from item f
  left join #t0 z    on z.item_no = f.item_no
 where z.item_no is null
go
print 'item_branch'
go
if not object_id('tempdb..#t1') is null
  drop table #t1
select item_no, branch_no into #t1
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.item_branch
 where branch_no = (select system_value from sys_option where system_id ='g_branch_no')
create index idx_t1 on #t1(item_no, branch_no)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.item_branch
select f.*
  from item_branch f
  left join #t1 z
    on z.item_no = f.item_no and z.branch_no=f.branch_no
 where z.item_no is null
go
print 'ba_branch_sale_price'
go
if not object_id('tempdb..#t2') is null
  drop table #t2
select item_no, branch_no into #t2
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.ba_branch_sale_price
 where statue='Y' and branch_no = (select system_value from sys_option where system_id ='g_branch_no')
create index idx_t2 on #t2(item_no, branch_no)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.ba_branch_sale_price
(branch_no, item_no, sale_price, statue, oper_date, memo)
select f.branch_no, f.item_no, f.sale_price, f.statue, f.oper_date, f.memo
  from ba_branch_sale_price f
  left join #t2 z
    on z.item_no = f.item_no and z.branch_no=f.branch_no
 where z.item_no is null
go
print 'pos_daysum'
go
if not object_id('tempdb..#t3') is null
  drop table #t3
select oper_date, item_no, branch_no into #t3
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.pos_daysum
 where branch_no = (select system_value from sys_option where system_id ='g_branch_no') and oper_date >= '2021-10-15'
create index idx_t3 on #t3(oper_date, item_no, branch_no)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.pos_daysum
select f.*
  from pos_daysum f
  left join #t3 z
    on z.item_no = f.item_no and z.branch_no=f.branch_no and z.oper_date=f.oper_date
 where z.item_no is null and f.oper_date >= '2021-10-15'
go
print 'ac_payrec_record'
go
if not object_id('tempdb..#t4') is null
  drop table #t4
select sheet_no, settle_no into #t4
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.ac_payrec_record
create index idx_t4 on #t4(sheet_no, settle_no)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.ac_payrec_record
select f.*
  from ac_payrec_record f
  left join #t4 z
    on z.sheet_no = f.sheet_no and z.settle_no=f.settle_no 
 where z.sheet_no is null
go
print 'wh_inout_master'
go
if not object_id('tempdb..#t5') is null
  drop table #t5
select sheet_no into #t5
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.wh_inout_master
 where branch_no = (select system_value from sys_option where system_id ='g_branch_no') 
    or d_branch_no = (select system_value from sys_option where system_id ='g_branch_no') 
create index idx_t5 on #t5(sheet_no)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.wh_inout_master
select f.*
  from wh_inout_master f
  left join #t5 z
    on z.sheet_no = f.sheet_no
 where z.sheet_no is null
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.wh_inout_detail
(sheet_no, line_no, item_no, unit_no, unit_factor, in_qty, orgi_price, valid_price, sub_amt, sub_amt2, tax, is_tax, valid_date, other1, other2, other3, current_salepx, current_inpx, num1, num2, num3, remark1, remark2, remark3, batch_no)
select f.sheet_no, f.line_no, f.item_no, f.unit_no, f.unit_factor, f.in_qty, f.orgi_price, f.valid_price, f.sub_amt, f.sub_amt2, f.tax, f.is_tax, f.valid_date, f.other1, f.other2, f.other3, f.current_salepx, f.current_inpx, f.num1, f.num2, f.num3, f.remark1, f.remark2, f.remark3, f.batch_no
  from wh_inout_detail f
inner join wh_inout_master m on m.sheet_no=f.sheet_no and m.work_date >= '2021-10-15'
  left join #t5 z on z.sheet_no = f.sheet_no
 where z.sheet_no is null
go
print 'wh_inout_flow'
go
if not object_id('tempdb..#t6') is null
  drop table #t6
select voucher_no,oper_date, item_no, pre_qty into #t6
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.wh_inout_flow
 where branch_no = (select system_value from sys_option where system_id ='g_branch_no') and oper_date >= '2021-10-15'
create index idx_t6 on #t6(voucher_no,oper_date, item_no, pre_qty)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.wh_inout_flow
(voucher_no, oper_date, item_no, unit_no, unit_factor, real_qty, in_price, sale_price, current_inpx, current_salepx, current_costpx, sheet_amt, branch_no, d_branch_no, trans_no, db_no, supcust_no, sale_way, oper_id, pre_qty, valid_date, memo)
select f.voucher_no, f.oper_date, f.item_no, f.unit_no, f.unit_factor, f.real_qty, f.in_price, f.sale_price, f.current_inpx, f.current_salepx, f.current_costpx, f.sheet_amt, f.branch_no, f.d_branch_no, f.trans_no, f.db_no, f.supcust_no, f.sale_way, f.oper_id, f.pre_qty, f.valid_date, f.memo
  from wh_inout_flow f
  left join #t6 z
    on z.voucher_no = f.voucher_no and z.oper_date = f.oper_date and z.item_no=f.item_no and z.pre_qty=f.pre_qty
 where z.oper_date is null and f.oper_date >= '2021-10-15'
go
print 'pm_spec_price'
go
if not object_id('tempdb..#t7') is null
  drop table #t7
select branch_no, start_date, item_no, spe_price into #t7
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.pm_spec_price
 where branch_no = (select system_value from sys_option where system_id ='g_branch_no') 
create index idx_t7 on #t7(branch_no, start_date, item_no, spe_price)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.pm_spec_price
(branch_no, item_no, special_type, start_date, end_date, old_price, spe_price, discount, sale_qty, sold_qty, other1, other2, other3, total_spec_qty, spec_need_integral, spec_week_day, spec_month_day, flag1, flag2, num1, num2, vip_type, spec_subject, in_price_flag)
select f.branch_no, f.item_no, f.special_type, f.start_date, f.end_date, f.old_price, f.spe_price, f.discount, f.sale_qty, f.sold_qty, f.other1, f.other2, f.other3, f.total_spec_qty, f.spec_need_integral, f.spec_week_day, f.spec_month_day, f.flag1, f.flag2, f.num1, f.num2, f.vip_type, f.spec_subject, f.in_price_flag
  from pm_spec_price f
  left join #t7 z
    on z.branch_no = f.branch_no and z.start_date = f.start_date and z.item_no=f.item_no and z.spe_price=f.spe_price
 where z.item_no is null
go
print 'ac_payrec_flow'
go
if not object_id('tempdb..#t8') is null
  drop table #t8
select voucher_no into #t8
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.ac_payrec_flow
 where branch_no = (select system_value from sys_option where system_id ='g_branch_no') 
create index idx_t8 on #t8(voucher_no)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.ac_payrec_flow
(pay_type, voucher_no, trans_no, coin_no, sheet_amt, discount, paid_amt, tax_amt, pay_way, sale_way, pay_percent, pay_date, supcust_no, memo, other1, other2, other3, branch_no, other4)
select f.pay_type, f.voucher_no, f.trans_no, f.coin_no, f.sheet_amt, f.discount, f.paid_amt, f.tax_amt, f.pay_way, f.sale_way, f.pay_percent, f.pay_date, f.supcust_no, f.memo, f.other1, f.other2, f.other3, f.branch_no, f.other4
  from ac_payrec_flow f
  left join #t8 z
    on z.voucher_no = f.voucher_no
 where z.voucher_no is null
go
print 'pos_t_saleflow'
go
if not object_id('tempdb..#t9') is null
  drop table #t9
select flow_no,branch_no,flow_id into #t9
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.pos_t_saleflow
 where branch_no = (select system_value from sys_option where system_id ='g_branch_no') and oper_date >= '2021-10-15'
create index idx_t8 on #t9(flow_no,branch_no,flow_id)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.pos_t_saleflow
(flow_id, flow_no, branch_no, item_no, source_price, sale_price, sale_qty, sale_amt, sale_way, oper_id, sale_man, counter_no, oper_date, remote_flag, reason_no, spec_flag, item_remark, fanli_px)
select f.flow_id, f.flow_no, f.branch_no, f.item_no, f.source_price, f.sale_price, f.sale_qty, f.sale_amt, f.sale_way, f.oper_id, f.sale_man, f.counter_no, f.oper_date, f.remote_flag, f.reason_no, f.spec_flag, f.item_remark, f.fanli_px
  from pos_t_saleflow f
  left join #t9 z
    on z.flow_no = f.flow_no and z.branch_no=f.branch_no and z.flow_id=f.flow_id
 where z.flow_no is null and f.oper_date >= '2021-10-15'
go
print 'pos_t_payflow'
go
if not object_id('tempdb..#ta') is null
  drop table #ta
select flow_no,branch_no,flow_id into #ta
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.pos_t_payflow
 where branch_no = (select system_value from sys_option where system_id ='g_branch_no') and oper_date >= '2021-10-15'
create index idx_t8 on #ta(flow_no,branch_no,flow_id)
insert opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.pos_t_payflow
(flow_id, flow_no, sale_amt, branch_no, pay_way, sale_way, card_no, vip_no, coin_no, coin_rate, pay_amt, oper_date, oper_id, counter_no, sale_man, memo, voucher_no, remote_flag, exchange_flag, shift_date, shift_no, other1, other2, other3, integral)
select f.flow_id, f.flow_no, f.sale_amt, f.branch_no, f.pay_way, f.sale_way, f.card_no, f.vip_no, f.coin_no, f.coin_rate, f.pay_amt, f.oper_date, f.oper_id, f.counter_no, f.sale_man, f.memo, f.voucher_no, f.remote_flag, f.exchange_flag, f.shift_date, f.shift_no, f.other1, f.other2, f.other3, f.integral
  from pos_t_payflow f
  left join #ta z
    on z.flow_no = f.flow_no and z.branch_no=f.branch_no and z.flow_id=f.flow_id
 where z.flow_no is null and f.oper_date >= '2021-10-15'
go
update cm_point set com_no_down =  (
select top 1 com_no
  from opendatasource('sqloledb', 'data source=tiger_hq_ms;user id=huwei;password=sztiger').tigerzz.dbo.cm_data
 order by com_no desc
) 
go

