order_service.py

# -*- encoding: utf-8 -*-
# -----------------------------------------------------------------------------
# @File Name  : order_service.py
# @Time       : 2020/11/18 下午3:35
# @Author     : X. Peng
# @Email      : acepengxiong@163.com
# @Software   : PyCharm
# -----------------------------------------------------------------------------
import datetime
import time

from sqlalchemy import and_
from sqlalchemy.testing import in_

from app.api.engine import TAMP_SQL, tamp_pay_engine, tamp_user_engine, config, env, tamp_zhibo_engine, tamp_course_engine
from app.config.errors import Errors
from app.controller.errorhandler import CustomFlaskErr
from app.model.account_balance import AccountBalance
# from app.model.account_topup_order import AccountTopupOrder
from app.model.curriculum_order import OrderFlow
from app.model.tamp_user_models import CurriculumPrice, CurriculumColumn, CurriculumRes, CsFileRecord
from app.model.tamp_course_models import CoursePackage
from xml.etree import cElementTree as etree

from app.model.tamp_zhibo_models import ZhiboTheme, ZhiboFile
from app.utils.alipay.alipayWap import prePay
from app.utils.apple_pay import apple_pay
from app.utils.wxpay import wx_jsapi_pay
from app.utils.wxpay.wx_app_pay import WXPay
import requests

# course_price_api = "https://devtamper.tanpuyun.com/course/res/money/price"

course_price_api = "http://s-nginx/course/res/money/price"

course_rollback_api = "http://s-nginx/fatools/pay/course/rollback"

ad_course_rollback_api = "http://s-nginx/course/advertise/detail?id="

class TopUpOrderService:
    """"""
    def __init__(self):
        pass

    def get_order(self, args):
        pageNumber = args['pageNumber']
        pageSize = args['pageSize']
        user_id = args['user_id']
        offset = (pageNumber - 1)*pageSize
        with TAMP_SQL(tamp_user_engine) as tamp_user:
            tamp_user_session = tamp_user.session
            totalSize = tamp_user_session.query(OrderFlow.id).filter(
                and_(OrderFlow.createby == user_id, OrderFlow.ab_type == '6', OrderFlow.ab_status == 'SUCCESS'), OrderFlow.deletetag == '0').count()
            res = tamp_user_session.query(OrderFlow).filter(and_(
                OrderFlow.createby == user_id,
                OrderFlow.ab_type == '6',
                OrderFlow.ab_status == 'SUCCESS',
                OrderFlow.deletetag == '0'
                )
            ).order_by(
            OrderFlow.createtime.desc()).offset(offset).limit(pageSize)
            res = [r.to_dict() for r in res]
        allowed = {'id', 'ab_ordernum', 'transaction_serial_no', 'ab_payment', 'ab_pay_mode', 'createtime', 'pay_time', 'complete_time', 'ab_type'}
        for r in res:
            keys = set(r.keys()) - allowed
            for key in keys:
                del r[key]
        if not res:
            res = []
        return {
            'content': res,
            'pageNum': pageNumber,
            'pageSize': pageSize,
            'totalSize': totalSize
        }


    def create_order(self, args):
        args['ab_ordernum'] = time.strftime('%Y%m%d', time.localtime(time.time())) + str(int(time.time() * 100000))
        args['id'] = OrderFlow.__tablename__ + str(int(time.time() * 100000))
        args['amount'] = args['amount'] * 100
        args['ab_status'] = 'WAIT'
        subject = '探普币充值'
        out_trade_no = args['ab_ordernum']
        notify_url = config[env]['pay_url_prefix'] + '/tamp_order/micro_shop/topUpOrderNotify'
        pay_params = None
        if args['pay_method'] == 5:
            # 微信支付
            wx_pay = WXPay(out_trade_no, subject, args['amount'], args['remote_addr'], notify_url=notify_url)
            params = wx_pay.getReturnParams()
            pay_params = params
        elif args['pay_method'] == 6:
            # 支付宝支付
            total_amount = args['amount'] / 100
            res = prePay(subject, out_trade_no, total_amount, notify_url=notify_url)
            pay_params = res
        elif args['pay_method'] == 7:
            # 苹果支付
            # 入参校验
            receipt_data = args.get('receipt_data', '')
            product_id = args.get('product_id', '')
            if not receipt_data:
                raise CustomFlaskErr(Errors.INPUT_PARAMS_ERROR)
            if not product_id:
                raise CustomFlaskErr(Errors.INPUT_PARAMS_ERROR)
            apple_pay_res = apple_pay(receipt_data, product_id)
            if not apple_pay_res['success']:
                raise CustomFlaskErr(Errors.APPLE_VOCHER_INVALID)
            args['transaction_serial_no'] = apple_pay_res.get('transaction_id', '')
            with TAMP_SQL(tamp_user_engine) as tamp_user, TAMP_SQL(tamp_pay_engine) as tamp_pay:
                tamp_user_session, tamp_pay_session = tamp_user.session, tamp_pay.session
                if tamp_user_session.query(OrderFlow).filter(OrderFlow.transaction_serial_no == args['transaction_serial_no']).all():
                    raise CustomFlaskErr(Errors.APPLE_VOCHER_USED)
                args['ab_status'] = 'SUCCESS'
                if not tamp_pay_session.query(AccountBalance).filter(AccountBalance.user_id == args['user_id']).all():
                    tamp_pay_session.add(AccountBalance(id=AccountBalance.__tablename__+str(int(time.time()*100000)), user_id=args['user_id'], apple_balance=args['amount']))
                else:
                    tamp_pay_session.query(AccountBalance).filter(
                        AccountBalance.user_id == args['user_id']).update({
                        'apple_balance': AccountBalance.apple_balance + args['amount']
                    })
        args.pop('host_url', '')
        args.pop('remote_addr', '')
        args.pop('receipt_data', '')
        args.pop('product_id', '')
        args['ab_payment'] = args.pop('amount', '')
        args['ab_pay_mode'] = args.pop('pay_method', '')
        args['createby'] = args.pop('user_id', '')
        args['ab_type'] = '6'
        order_info = OrderFlow(**args)
        with TAMP_SQL(tamp_user_engine) as tamp_user:
            tamp_user_session = tamp_user.session
            tamp_user_session.add(order_info)
        return pay_params


class CurriculumOrderService:
    """消费订单"""
    def __init__(self):
        pass

    def get_order(self, args):
        """."""
        pageNumber = args['pageNumber']
        pageSize = args['pageSize']
        user_id = args['user_id']
        offset = (pageNumber - 1) * pageSize
        with TAMP_SQL(tamp_user_engine) as tamp_user, TAMP_SQL(tamp_zhibo_engine) as tamp_zhibo, TAMP_SQL(tamp_course_engine) as tamp_course:
            tamp_user_session = tamp_user.session
            tamp_zhibo_session = tamp_zhibo.session
            tamp_course_session = tamp_course.session
            # curriculum_column = tamp_user_session.query(CurriculumColumn.id, CurriculumColumn.title, CurriculumColumn.cover, CurriculumColumn.info).all()
            # curriculum_res = tamp_user_session.query(CurriculumRes.id, CurriculumRes.title, CurriculumRes.cover, CurriculumRes.teacher_name).all()
            # zhibo = tamp_zhibo_session.query(ZhiboTheme.id, ZhiboTheme.zt_name, ZhiboTheme.zt_img, ZhiboTheme.manager_name, ZhiboTheme.zt_starttime).all()
            # zhibo_file = tamp_zhibo_session.query(ZhiboFile.id, ZhiboFile.remark)
            
            curriculum_column = tamp_user_session.query(CurriculumColumn)
            curriculum_res = tamp_user_session.query(CurriculumRes)
            zhibo = tamp_zhibo_session.query(ZhiboTheme)
            zhibo_file = tamp_zhibo_session.query(ZhiboFile)
            
            res = tamp_user_session.query(OrderFlow).filter(
                and_(
                    OrderFlow.createby == user_id,
                    OrderFlow.ab_type != '6',
                    OrderFlow.ab_status == 'SUCCESS',
                    OrderFlow.deletetag == '0'
                )
            )
            totalSize = res.count()
            res = res.order_by(OrderFlow.createtime.desc()).offset(offset).limit(pageSize)
            
            # 找出 1栏目表 , 3直播间信息表, [4,5]课程资源, 7附件 的list id
            orders = [r.to_dict() for r in res if r.to_dict()['ab_status'] == 'SUCCESS']
            orders_dict = {'1':[], '3':[], '4':[], '5':[], '7':[], '300':[], '305':[]}
            for i in orders:
                prod_type = i.get('ab_type', '')
                prod_id = i.get('ab_proid', '')
                if (prod_type in '13457' or prod_type == '300' or prod_type == '305') and prod_id:
                    orders_dict[prod_type].append(prod_id)
                    
            # 四组组合查询，将 需要的 1栏目表 , 3直播间信息表, [4,5]课程资源, 7附件 查出
            
            curriculum_column = tamp_user_session.query(CurriculumColumn.id, CurriculumColumn.title, 
                                                        CurriculumColumn.cover, CurriculumColumn.info).\
                                                            filter(CurriculumColumn.id.in_(orders_dict["1"])).all()
            curriculum_res = tamp_user_session.query(CurriculumRes.id, CurriculumRes.title, 
                                                     CurriculumRes.cover, CurriculumRes.teacher_name).\
                                                         filter(CurriculumRes.id.in_(orders_dict["4"] + orders_dict["5"])).all()
            zhibo = tamp_zhibo_session.query(ZhiboTheme.id, ZhiboTheme.zt_name, ZhiboTheme.zt_img, 
                                             ZhiboTheme.manager_name, ZhiboTheme.zt_starttime).\
                                                 filter(ZhiboTheme.id.in_(orders_dict["3"])).all()
            zhibo_file = tamp_zhibo_session.query(ZhiboFile.id, ZhiboFile.remark).\
                                                filter(ZhiboFile.id.in_(orders_dict["7"])).all()
            course_package = tamp_course_session.query(CoursePackage.id, CoursePackage.main_title, 
                                                    CoursePackage.square_img, CoursePackage.bg_status,
                                                    CoursePackage.bg_img).\
                                            filter(CoursePackage.id.in_(orders_dict["300"])).all()
            course_file = tamp_user_session.query(CsFileRecord.guid, CsFileRecord.original_name).\
                                            filter(CsFileRecord.guid.in_(orders_dict["305"])).all()
            
            curriculum_column = {r[0]: {'title': r[1], 'cover': r[2], 'info': r[3]} for r in curriculum_column}
            curriculum_res = {r[0]: {'title': r[1], 'cover': r[2], 'info': r[3]} for r in curriculum_res}
            zhibo = {r[0]: {'title': r[1], 'cover': r[2], 'info': r[3], 'zb_start_time': int(r[4].timestamp()) if r[4] else 0} for r in zhibo}
            zhibo_file = {r[0]: {'title': r[1]} for r in zhibo_file}
            # orders = [r.to_dict() for r in res if r.to_dict()['ab_status'] == 'SUCCESS']
            course_package = {r[0]: {'title': r[1], 'square_img': r[2], 'bg_status': r[3], 'bg_img': r[4]} for r in course_package}
            course_file = {r[0]: {'title': r[1]} for r in course_file}
            
        allowed = {'id', 'title', 'cover', 'info', 'ab_type', 'ab_payment', 'ab_score', 'ab_pay_mode', 'ab_ordernum', 'transaction_serial_no', 'pay_method', 'createtime', 'pay_time', 'complete_time',
                   'ab_status', 'ab_pro_siid', 'zb_start_time', 'square_img', 'bg_status', 'bg_img'}
        temp_orders = []
        for order in orders:
            prod_type = order.get('ab_type', '')
            prod_id = order.get('ab_proid', '')
            if prod_type == '1' and curriculum_column.get(prod_id):
                order = {**order, **curriculum_column.get(prod_id, None)}
            elif prod_type == '3' and zhibo.get(prod_id, None):
                order = {**order, **zhibo.get(prod_id, None)}
            elif prod_type in ['4', '5'] and curriculum_res.get(prod_id, None):
                order = {**order, **curriculum_res.get(prod_id, None)}
            elif prod_type == '7' and zhibo_file.get(prod_id, None):
                order = {**order, **zhibo_file.get(prod_id, None)}
            elif prod_type == '300' and course_package.get(prod_id, None):
                order = {**order, **course_package.get(prod_id, None)}
            elif prod_type == '305' and course_file.get(prod_id, None):
                order = {**order, **course_file.get(prod_id, None)}

            keys = set(order.keys()) - allowed
            for key in keys:
                del order[key]
            temp_orders.append(order)
        # temp_orders = temp_orders[offset: offset+pageSize]
        # allowed = {'id', 'title', 'cover', 'info', 'ab_type', 'ab_payment', 'ab_score', 'ab_pay_mode', 'ab_ordernum', 'transaction_serial_no', 'pay_method', 'createtime', 'pay_time', 'complete_time',
        #            'ab_status', 'ab_pro_siid', 'zb_start_time'}
        # for r in temp_orders:
        #     keys = set(r.keys()) - allowed
        #     for key in keys:
        #         del r[key]
        # if not temp_orders:
        #     temp_orders = []
        return {
            'content': temp_orders,
            'pageNum': pageNumber,
            'pageSize': pageSize,
            'totalSize': totalSize
        }


    def create_order(self, args):
        """。"""
        with TAMP_SQL(tamp_user_engine) as tamp_user, TAMP_SQL(tamp_pay_engine) as tamp_pay:
            tamp_user_session, tamp_pay_session = tamp_user.session, tamp_pay.session
            args['ab_ordernum'] = time.strftime('%Y%m%d', time.localtime(time.time())) + str(int(time.time() * 100000))

            # 该用户是否重复购买同一商品
            repeat_buy = tamp_user_session.query(OrderFlow.id).filter(and_(OrderFlow.createby == args['user_id']),
                                                                      OrderFlow.ab_proid == args['ab_proid'],
                                                                      OrderFlow.ab_status == 'SUCCESS').all()
            if repeat_buy:
                raise CustomFlaskErr(Errors.REPEAT_BUY)
            if args['ab_type'] == 300 and args.get('ad') == 'April':
                amount = requests.get(ad_course_rollback_api + args.get('ad_id'),
                                    timeout = 5)
                try:
                    amount = amount.json()
                    amount = [int(amount["attributes"]["bargainPrice"])]
                except:
                    raise CustomFlaskErr(Errors.PROD_NOPRICE)

            elif args['ab_type'] == 300:
                data = {
                            "payReqs": [{"resId": args['ab_proid']}],
                            "userId": args['user_id']
                        }
                headers = {
                    "Content-Type": "application/json",
                    # "tampToken": args['tampToken'],
                    "env": args['env']
                }
                amount = requests.post(course_price_api, 
                                    headers = headers,
                                    json = data,
                                    timeout = 5)
                try:
                    amount = amount.json()
                    '''
                        {
                            "statusCode": "0000",
                            "message": "成功",
                            "attributes": {
                                "COURSE_PACKAGE16185457769740": {
                                    "resId": "COURSE_PACKAGE16185457769740",
                                    "totalPrice": "998",
                                    "chargeMode": 3,
                                    "discountPrice": "8",
                                    "time3Type": null,
                                    "memberDiscountPrice": null,
                                    "memberTime3Type": null,
                                    "finalPrice": "998",
                                    "isNeedBuy": 1,
                                    "isBuy": 2
                                }
                            },
                            "success": true
                        }
                    '''
                    amount = [int(amount["attributes"][args['ab_proid']]["finalPrice"])]
                except:
                    raise CustomFlaskErr(Errors.PROD_NOPRICE)
            else:
                amount = tamp_user_session.query(CurriculumPrice.price).filter(CurriculumPrice.rel_id == args['ab_proid']).first()
                if not amount:
                    raise CustomFlaskErr(Errors.PROD_NOPRICE)

            amount = amount[0]

            args['id'] = OrderFlow.__tablename__ + str(int(time.time() * 100000))
            args['ab_payment'] = amount
            args['ab_status'] = 'WAIT'
            subject = args.get('prod_name', '')
            out_trade_no = args['ab_ordernum']
            notify_url = config[env]['pay_url_prefix'] + '/tamp_order/micro_shop/consumeOrderNotify'
            pay_params = None         

            if int(amount) == 0:
                args['ab_status'] = 'SUCCESS'
            elif args['ab_pay_mode'] == 4:
                # 探普币支付
                if args['env'] == 'ios':
                    res = tamp_pay_session.query(AccountBalance.apple_balance).filter(
                        AccountBalance.user_id == args['user_id']).first()
                    if not res:
                        raise CustomFlaskErr(Errors.TANGPU_BALANCE_NOT_ENOUGH)
                    elif res[0] < args['ab_payment']:
                        raise CustomFlaskErr(Errors.TANGPU_BALANCE_NOT_ENOUGH)
                    else:
                        tamp_pay_session.query(AccountBalance).filter(
                            AccountBalance.user_id == args['user_id']).update({
                            'apple_balance': AccountBalance.apple_balance - args['ab_payment']
                        })
                        args['ab_status'] = 'SUCCESS'
                elif args['env'] == 'android':
                    res = tamp_pay_session.query(AccountBalance.android_balance).filter(
                        AccountBalance.user_id == args['user_id']).first()
                    if not res:
                        raise CustomFlaskErr(Errors.TANGPU_BALANCE_NOT_ENOUGH)
                    elif res[0] < args['ab_payment']:
                        raise CustomFlaskErr(Errors.TANGPU_BALANCE_NOT_ENOUGH)
                    else:
                        tamp_pay_session.query(AccountBalance).filter(
                            AccountBalance.user_id == args['user_id']).update({
                            'android_balance': AccountBalance.android_balance - args['ab_payment']
                        })
                        args['ab_status'] = 'SUCCESS'
            elif args['ab_pay_mode'] == 5:
                # 微信App支付
                wx_pay = WXPay(out_trade_no, subject, args['ab_payment'], args['remote_addr'], notify_url)
                params = wx_pay.getReturnParams()
                pay_params = params
            elif args['ab_pay_mode'] == 6:
                # 支付宝支付
                total_amount = args['ab_payment'] / 100
                res = prePay(subject, out_trade_no, total_amount, notify_url)
                pay_params = res
            elif args['ab_pay_mode'] == 8:
                # 微信公众号支付
                # 特殊情况，广告推广支付
                ad = args.get("ad")
                if ad == 'April':
                    args.pop('ad', '')
                    subject = subject + ',ad='+ ad
                wx_pay = wx_jsapi_pay.WXPay(args['openid'], out_trade_no, subject, args['ab_payment'], args['remote_addr'], notify_url)
                params = wx_pay.getReturnParams()
                pay_params = params
            args['createby'] = args.pop('user_id', '')
            args.pop('prod_name', '')
            args.pop('env', '')
            args.pop('remote_addr', '')
            args.pop('host_url', '')
            args.pop('openid', '')
            args.pop('tampToken', '')
            args.pop('ad', '')
            args.pop('ad_id', '')
            order_info = OrderFlow(**args)
            tamp_user_session.add(order_info)
            return pay_params


def topUpSuccessAction(order_no, transaction_id):
    """."""
    with TAMP_SQL(tamp_user_engine) as tamp_user, TAMP_SQL(tamp_pay_engine) as tamp_pay:
        tamp_user_session, tamp_pay_session = tamp_user.session, tamp_pay.session
        topup_order = tamp_user_session.query(OrderFlow).filter(OrderFlow.ab_ordernum == order_no).first()
        if not topup_order:
            raise CustomFlaskErr(Errors.NO_ORDERS)
        else:
            topup_order = topup_order.to_dict()
        # 幂等校验
        if topup_order.get('ab_status', '') == 'SUCCESS':
            return False
        tamp_user_session.query(OrderFlow).filter(OrderFlow.ab_ordernum == order_no).update({
            'ab_status': 'SUCCESS',
            'transaction_serial_no': transaction_id,
            'pay_time': datetime.datetime.now(),
            'complete_time': datetime.datetime.now()
        })
        # 账户充值
        account_topup_order = tamp_user_session.query(OrderFlow).filter(OrderFlow.ab_ordernum == order_no).first().to_dict()
        user_id = account_topup_order.get('createby', '')
        ab_payment = account_topup_order.get('ab_payment', '')
        # 安卓支付宝，微信支付
        if not tamp_pay_session.query(AccountBalance).filter(AccountBalance.user_id == user_id).all():
            tamp_pay_session.add(AccountBalance(id=AccountBalance.__tablename__ + str(int(time.time()*10000)), user_id=user_id, android_balance=ab_payment))
        else:
            tamp_pay_session.query(AccountBalance).filter(AccountBalance.user_id == user_id).update({
                'android_balance': AccountBalance.android_balance + ab_payment
            })



def consumeSucessAction(order_no, transaction_id):
    """."""
    with TAMP_SQL(tamp_user_engine) as tamp_user:
        tamp_user_session = tamp_user.session
        curriculum_order = tamp_user_session.query(OrderFlow).filter(OrderFlow.ab_ordernum == order_no).first()
        if not curriculum_order:
            raise CustomFlaskErr(Errors.NO_ORDERS)
        else:
            curriculum_order = curriculum_order.to_dict()
        # 幂等校验
        if curriculum_order.get('ab_status', '') == 'SUCCESS':
            return False
        tamp_user_session.query(OrderFlow).filter(OrderFlow.ab_ordernum == order_no).update({
            'ab_status': 'SUCCESS',
            'transaction_serial_no': transaction_id,
            'pay_time': datetime.datetime.now(),
            'complete_time': datetime.datetime.now()
        })

        if curriculum_order.get('ab_type') == '300':
            data = {"resId": curriculum_order.get('ab_proid'),
                    "userId": curriculum_order.get('createby'),
                    "phone": curriculum_order.get('register_phone')}
            headers = {
                "Content-Type": "application/json",
                "env": "ios"
            }
            try:
                tmp = requests.post(course_rollback_api, 
                                headers = headers,
                                json = data,
                                timeout = 5)
                print(tmp.json())
            except:
                print("用户提升级别接口失败")



def getAccountBalance(user_id):
    """查询探普币余额"""
    with TAMP_SQL(tamp_pay_engine) as tamp_pay:
        tamp_pay_session = tamp_pay.session
        account_balance = tamp_pay_session.query(AccountBalance).filter(AccountBalance.user_id == user_id).first()
        if not account_balance:
            return {'user_id': user_id, 'android_balance': 0, 'apple_balance': 0}
        else:
            return account_balance.to_dict()


def xml2dict(content):
    """将从微信服务器接收到的xml转为dict."""
    raw = {}
    root = etree.fromstring(content)
    for child in root:
        raw[child.tag] = child.text
    return raw


def dict2xml(dict_):
    """将dict转为要发送到微信服务器的xml格式."""
    s = ""
    for k, v in dict_.items():
        s += "<{0}>{1}</{0}>".format(k, v)
    s = "<xml>{0}</xml>".format(s)
    return s.encode("utf-8")

def alipayWxPayCheck(res_info):
    """支付宝，微信支付异步通知"""
    success = False
    body = ''
    transaction_id = ''
    header = {'Content-Type': 'application/text'}
    res = {}
    if 'xml' in res_info:
        # 微信支付
        res = xml2dict(res_info)
        if res['return_code'] == 'SUCCESS' and res['result_code'] == 'SUCCESS':
            success = True
            transaction_id = res.get('transaction_id', '')
            header = {'Content-Type': 'application/xml'}
            body = '<xml> <return_code><![CDATA[SUCCESS]]></return_code> <return_msg><![CDATA[OK]]></return_msg> </xml>'
    else:
        # 支付宝支付
        res_info = res_info.split('&')
        for ss in res_info:
            key, value = ss.split('=')
            res[key] = value
        if res.get('trade_status', '') == 'TRADE_SUCCESS':
            success = True
            transaction_id = res.get('trade_no', '')
            body = 'success'.encode()

    return {'success': success, 'header': header, 'body': body, 'order_no': res['out_trade_no'], 'transaction_id': transaction_id}


def getAllOrders(args):
    """."""
    pageNumber = args['pageNumber']
    pageSize = args['pageSize']
    user_id = args['user_id']
    offset = (pageNumber - 1) * pageSize
    with TAMP_SQL(tamp_user_engine) as tamp_user, TAMP_SQL(tamp_zhibo_engine) as tamp_zhibo, TAMP_SQL(tamp_course_engine) as tamp_course:
        tamp_user_session = tamp_user.session
        tamp_zhibo_session = tamp_zhibo.session
        tamp_course_session = tamp_course.session
        
        # 全部订单
        # curriculum_column = tamp_user_session.query(CurriculumColumn.id, CurriculumColumn.title, CurriculumColumn.cover,
        #                                             CurriculumColumn.info).all()
        # curriculum_res = tamp_user_session.query(CurriculumRes.id, CurriculumRes.title, CurriculumRes.cover,
        #                                          CurriculumRes.teacher_name).all()
        # zhibo = tamp_zhibo_session.query(ZhiboTheme.id, ZhiboTheme.zt_name, ZhiboTheme.zt_img, ZhiboTheme.manager_name,
        #                                  ZhiboTheme.zt_starttime).all()
        res = tamp_user_session.query(OrderFlow).filter(
            and_(
                OrderFlow.createby == user_id,
                OrderFlow.ab_status == 'SUCCESS',
                OrderFlow.deletetag == '0'
            )
        )
        totalSize = res.count()
        res = res.order_by(OrderFlow.createtime.desc()).offset(offset).limit(pageSize)
        
        # 找出 1栏目表 , 3直播间信息表, [4,5]课程资源, 7附件 的list id
        orders = [r.to_dict() for r in res if r.to_dict()['ab_status'] == 'SUCCESS']
        orders_dict = {'1':[], '3':[], '4':[], '5':[], '7':[], '300':[], '305':[]}
        for i in orders:
            prod_type = i.get('ab_type', '')
            prod_id = i.get('ab_proid', '')
            if (prod_type in '13457' or prod_type == '300' or prod_type == '305') and prod_id:
                orders_dict[prod_type].append(prod_id)
        # 四组组合查询，将 需要的 1栏目表 , 3直播间信息表, [4,5]课程资源, 7附件, 300 课程包 查出
            
        curriculum_column = tamp_user_session.query(CurriculumColumn.id, CurriculumColumn.title, 
                                                    CurriculumColumn.cover, CurriculumColumn.info).\
                                                        filter(CurriculumColumn.id.in_(orders_dict["1"])).all()
        curriculum_res = tamp_user_session.query(CurriculumRes.id, CurriculumRes.title, 
                                                    CurriculumRes.cover, CurriculumRes.teacher_name).\
                                                        filter(CurriculumRes.id.in_(orders_dict["4"] + orders_dict["5"])).all()
        zhibo = tamp_zhibo_session.query(ZhiboTheme.id, ZhiboTheme.zt_name, ZhiboTheme.zt_img, 
                                            ZhiboTheme.manager_name, ZhiboTheme.zt_starttime).\
                                                filter(ZhiboTheme.id.in_(orders_dict["3"])).all()
        zhibo_file = tamp_zhibo_session.query(ZhiboFile.id, ZhiboFile.remark).\
                                            filter(ZhiboFile.id.in_(orders_dict["7"])).all()
        course_package = tamp_course_session.query(CoursePackage.id, CoursePackage.main_title, 
                                                    CoursePackage.square_img, CoursePackage.bg_status,
                                                    CoursePackage.bg_img).\
                                            filter(CoursePackage.id.in_(orders_dict["300"])).all()
        course_file = tamp_user_session.query(CsFileRecord.guid, CsFileRecord.original_name).\
                                            filter(CsFileRecord.guid.in_(orders_dict["305"])).all()
                
        
        curriculum_column = {r[0]: {'title': r[1], 'cover': r[2], 'info': r[3]} for r in curriculum_column}
        curriculum_res = {r[0]: {'title': r[1], 'cover': r[2], 'info': r[3]} for r in curriculum_res}
        zhibo = {r[0]: {'title': r[1], 'cover': r[2], 'info': r[3], 'zb_start_time':  int(r[4].timestamp()) if r[4] else 0} for r in zhibo}
        zhibo_file = {r[0]: {'title': r[1]} for r in zhibo_file}
        course_package = {r[0]: {'title': r[1], 'square_img': r[2], 'bg_status': r[3], 'bg_img': r[4]} for r in course_package}
        course_file = {r[0]: {'title': r[1]} for r in course_file}
        
        allowed = {'id', 'title', 'cover', 'info', 'ab_type', 'ab_payment', 'ab_score', 'ab_pay_mode', 'ab_ordernum',
                   'transaction_serial_no', 'pay_method', 'createtime', 'pay_time', 'complete_time',
                   'ab_status', 'ab_pro_siid', 'zt_start_time', 'square_img', 'bg_status', 'bg_img'}
        temp_orders = []
        for order in orders:
            prod_type = order.get('ab_type', '')
            prod_id = order.get('ab_proid', '')
            
            if prod_type == '1' and curriculum_column.get(prod_id, None):
                order = {**order, **curriculum_column.get(prod_id, None)}
            elif prod_type == '3' and zhibo.get(prod_id, None):
                order = {**order, **zhibo.get(prod_id, None)}
            elif prod_type in ['4', '5'] and curriculum_res.get(prod_id, None):
                order = {**order, **curriculum_res.get(prod_id, None)}
            elif prod_type == '7' and zhibo_file.get(prod_id, None):
                order = {**order, **zhibo_file.get(prod_id, None)}
            elif prod_type == '300' and course_package.get(prod_id, None):
                order = {**order, **course_package.get(prod_id, None)}
            elif prod_type == '305' and course_file.get(prod_id, None):
                order = {**order, **course_file.get(prod_id, None)}
            
            # 将多余字段删除
            keys = set(order.keys()) - allowed
            for key in keys:
                del order[key] 
            
            temp_orders.append(order)
        
        # for r in temp_orders:
        #     keys = set(r.keys()) - allowed
        #     for key in keys:
        #         del r[key]
        # if not temp_orders:
        #     temp_orders = []
        return {
            'content': temp_orders,
            'pageNum': pageNumber,
            'pageSize': pageSize,
            'totalSize': totalSize
        }