反向同步订单

数据中间表

//个检订单表  HM_YW_TJZ000_QZJ， 
//个检项目表 HM_YW_TJYW00_QZJ ，
//团检订单表 ESI_HMZXDD_TTTJZ0，
//团检项目表 ESI_HMZXDD_TTYWZJ
// grant select on ESI_HMZXDD_TTYWZJ to TX_SUPERMAN;
// CREATE OR REPLACE PUBLIC SYNONYM ESI_HMZXDD_TTYWZJ FOR tx_jk.ESI_HMZXDD_TTYWZJ;

反向同步逻辑

同步逻辑

• 1.先从视图查询近两日备单数据

• 2.查询待同步数据
  时间为最近两天
  select * from tbl_inv_group_person_v where modifydate >’20231223’

• 3.同步至中间表tbl_inv_group_person

• 4.查询中间表最近7日待上传数据

//最近7天待同步数据
select * from tbl_inv_group_person where upload =0 and inserttime>sysdate -1
and not exists(select 1 from tbl_errorlog where relatedid = hisexamineenum)

• 5.查询最近订单的单位，同步至中间表tbl_inv_company

• 6.查询中间表待上传数据，upload = 0
  //查询单位信息
  select from HM_YW_TTRW00 a inner join HM_YW_TJTT00 b on a.TTID00=b.TTID00
  select from tbl_inv_company where hiscompanycode =’分组id’
  //查询单位错误日志
  select * from tbl_errorlog where relatedid =’companycode’

• 7.查询最近订单的单位下的套餐，同步至中间表tbl_inv_meal

• 8.查询中间表待上传数据，upload = 0
  //查询套餐信息
  select from TBL_INV_MEAL_V a where 1=1
  select from tbl_inv_meal
  //如果单项为空则不同步
  select from tbl_inv_meal_item
  //查询套餐错误日志
  select from tbl_errorlog where relatedid =’hisgroupcode’

• 9.上传订单信息
  //查询订单信息
  select from tbl_inv_group_person
  //查询订单错误日志
  select from tbl_errorlog where relatedid =’体检编号’

• 10.查看同步订单信息
  select * from tbl_order where hisexamineenum =’体检变化’
  upload :0未上传 1已经上传
  myordernum:线上订单编号