JyOrderItemList.py 2.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2024/6/20 19:43
  4. # @Site :
  5. # @File : JyOrderItemList.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from time import sleep
  11. from Apietl.conn.TidbConn import TidbConn
  12. class JyOrderItemList:
  13. def __init__(self):
  14. self.conn = TidbConn.get_pro_connection()
  15. def UpdateJyOrderItemList(self):
  16. try:
  17. sql = """select item_id, max(stat_date) as stat_date from ruixuan.jy_order_list
  18. where stat_date>=date_format(date_sub(now(),interval 2 day),'%Y%m%d')
  19. group by item_id"""
  20. itemList = TidbConn.quary(self.conn, sql)
  21. for item in itemList:
  22. item_id = item[0]
  23. maxdate = item[1]
  24. newSql = """
  25. select
  26. item_id,
  27. ifnull(regimental_promotion_rate,0) as regimental_promotion_rate,
  28. ifnull(commission_rate,0) as commission_rate,
  29. ifnull(regimental_promotion_amount,0) as regimental_promotion_amount,
  30. ifnull(mcn_promotion_amount,0) as mcn_promotion_amount
  31. from ruixuan.jy_order_list
  32. where item_id={item_id} and stat_date={maxdate}
  33. order by order_create_time limit 1
  34. """.format(item_id=item_id, maxdate=maxdate)
  35. newValue = TidbConn.quary(self.conn, newSql)
  36. item_id = newValue[0][0]
  37. regimental_promotion_rate = newValue[0][1]
  38. commission_rate = newValue[0][2]
  39. regimental_promotion_amount = newValue[0][3]
  40. mcn_promotion_amount = newValue[0][4]
  41. update_sql = """
  42. update ruixuan.jy_order_item_list set regimental_promotion_rate={regimental_promotion_rate},
  43. commission_rate={commission_rate},
  44. regimental_promotion_amount={regimental_promotion_amount},
  45. mcn_promotion_amount={mcn_promotion_amount}
  46. where item_id = {item_id}
  47. """.format(regimental_promotion_rate=regimental_promotion_rate,
  48. commission_rate=commission_rate,
  49. regimental_promotion_amount=regimental_promotion_amount,
  50. mcn_promotion_amount=mcn_promotion_amount,
  51. item_id=item_id
  52. )
  53. TidbConn.upsert(self.conn, update_sql)
  54. self.conn.commit()
  55. sleep(0.5)
  56. TidbConn.conn_close(conn=self.conn)
  57. return 0
  58. except Exception as e:
  59. logging.error(e)
  60. return -1
  61. if __name__ == '__main__':
  62. status = JyOrderItemList().UpdateJyOrderItemList()
  63. if status == 0:
  64. print("作业执行成功")
  65. exit(0)
  66. else:
  67. print("作业执行失败")
  68. exit(-1)