# 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)