| 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 1501435553import loggingfrom time import sleepfrom Apietl.conn.TidbConn import TidbConnclass 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 -1if __name__ == '__main__':    status = JyOrderItemList().UpdateJyOrderItemList()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |