123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2024/6/20 19:43
- # @Site :
- # @File : JyOrderItemList.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- # @Tel 1501435553
- import logging
- from time import sleep
- from Apietl.conn.TidbConn import TidbConn
- class JyOrderItemList:
- def __init__(self):
- self.conn = TidbConn.get_pro_connection()
- def UpdateJyOrderItemList(self):
- try:
- sql = """select item_id, max(stat_date) as stat_date from ruixuan.jy_order_list
- where stat_date>=date_format(date_sub(now(),interval 2 day),'%Y%m%d')
- group by item_id"""
- itemList = TidbConn.quary(self.conn, sql)
- for item in itemList:
- item_id = item[0]
- maxdate = item[1]
- newSql = """
- select
- item_id,
- ifnull(regimental_promotion_rate,0) as regimental_promotion_rate,
- ifnull(commission_rate,0) as commission_rate,
- ifnull(regimental_promotion_amount,0) as regimental_promotion_amount,
- ifnull(mcn_promotion_amount,0) as mcn_promotion_amount
- from ruixuan.jy_order_list
- where item_id={item_id} and stat_date={maxdate}
- order by order_create_time limit 1
- """.format(item_id=item_id, maxdate=maxdate)
- newValue = TidbConn.quary(self.conn, newSql)
- item_id = newValue[0][0]
- regimental_promotion_rate = newValue[0][1]
- commission_rate = newValue[0][2]
- regimental_promotion_amount = newValue[0][3]
- mcn_promotion_amount = newValue[0][4]
- update_sql = """
- update ruixuan.jy_order_item_list set regimental_promotion_rate={regimental_promotion_rate},
- commission_rate={commission_rate},
- regimental_promotion_amount={regimental_promotion_amount},
- mcn_promotion_amount={mcn_promotion_amount}
- where item_id = {item_id}
- """.format(regimental_promotion_rate=regimental_promotion_rate,
- commission_rate=commission_rate,
- regimental_promotion_amount=regimental_promotion_amount,
- mcn_promotion_amount=mcn_promotion_amount,
- item_id=item_id
- )
- TidbConn.upsert(self.conn, update_sql)
- self.conn.commit()
- sleep(0.5)
- TidbConn.conn_close(conn=self.conn)
- return 0
- except Exception as e:
- logging.error(e)
- return -1
- if __name__ == '__main__':
- status = JyOrderItemList().UpdateJyOrderItemList()
- if status == 0:
- print("作业执行成功")
- exit(0)
- else:
- print("作业执行失败")
- exit(-1)
|