def lists(page=1): """ 投资申请列表 """ form = ApplyPutSearchForm(request.form) apply_put_id = request.args.get('apply_put_id', '', type=int) user_id = request.args.get('user_id', '', type=int) type_apply = request.args.get('type_apply', '', type=str) money_apply = request.args.get('money_apply', '', type=str) status_apply = request.args.get('status_apply', '', type=str) status_order = request.args.get('status_order', '', type=str) status_delete = request.args.get('status_delete', '', type=str) start_time = request.args.get('start_time', '', type=str) end_time = request.args.get('end_time', '', type=str) op = request.args.get('op', 0, type=int) form.apply_put_id.data = apply_put_id form.user_id.data = user_id form.type_apply.data = type_apply form.money_apply.data = money_apply form.status_apply.data = status_apply form.status_order.data = status_order form.status_delete.data = status_delete form.start_time.data = start_time form.end_time.data = end_time search_condition_apply_put = [ ApplyPut.status_delete == STATUS_DEL_NO, ] if apply_put_id: search_condition_apply_put.append(ApplyPut.id == apply_put_id) if user_id: search_condition_apply_put.append(ApplyPut.user_id == user_id) if status_apply: search_condition_apply_put.append(ApplyPut.status_apply == status_apply) if status_order: search_condition_apply_put.append(ApplyPut.status_order == status_order) if status_delete: search_condition_apply_put.append(ApplyPut.status_delete == status_delete) if start_time: search_condition_apply_put.append(ApplyPut.create_time >= time_local_to_utc(start_time)) if end_time: search_condition_apply_put.append(ApplyPut.create_time <= time_local_to_utc(end_time)) # pagination = get_apply_put_rows(page) try: pagination = ApplyPut.query. \ filter(*search_condition_apply_put). \ outerjoin(UserProfile, ApplyPut.user_id == UserProfile.user_id). \ add_entity(UserProfile). \ order_by(ApplyPut.id.desc()). \ paginate(page, PER_PAGE_BACKEND, False) db.session.commit() return render_template('apply_put/list.html', title='apply_put_list', pagination=pagination, form=form) except Exception as e: db.session.rollback() flash(e.message, category='warning') return redirect(url_for('index'))
def apply_put_stats(time_based='hour'): """ 投资申请统计 :return: """ # 按小时统计 if time_based == 'hour': start_time, end_time = get_current_day_time_ends() hours = get_hours(False) hours_zerofill = get_hours() result = dict(zip(hours, [0] * len(hours))) rows = db.session \ .query(func.hour(ApplyPut.create_time).label('hour'), func.sum(ApplyPut.money_apply)) \ .filter(ApplyPut.create_time >= time_local_to_utc(start_time), ApplyPut.create_time <= time_local_to_utc(end_time)) \ .group_by('hour') \ .limit(len(hours)) \ .all() result.update(dict(rows)) return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)] # 按日期统计 if time_based == 'date': start_time, end_time = get_current_month_time_ends() today = datetime.today() days = get_days(year=today.year, month=today.month, zerofill=False) days_zerofill = get_days(year=today.year, month=today.month) result = dict(zip(days, [0] * len(days))) rows = db.session \ .query(func.day(ApplyPut.create_time).label('date'), func.sum(ApplyPut.money_apply)) \ .filter(ApplyPut.create_time >= time_local_to_utc(start_time), ApplyPut.create_time <= time_local_to_utc(end_time)) \ .group_by('date') \ .limit(len(days)) \ .all() result.update(dict(rows)) return [(days_zerofill[i], result[day]) for i, day in enumerate(days)] # 按月份统计 if time_based == 'month': start_time, end_time = get_current_year_time_ends() months = get_months(False) months_zerofill = get_months() result = dict(zip(months, [0] * len(months))) rows = db.session \ .query(func.month(ApplyPut.create_time).label('month'), func.sum(ApplyPut.money_apply)) \ .filter(ApplyPut.create_time >= time_local_to_utc(start_time), ApplyPut.create_time <= time_local_to_utc(end_time)) \ .group_by('month') \ .limit(len(months)) \ .all() result.update(dict(rows)) return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
def user_active_stats(time_based='hour'): """ 用户激活统计 :return: """ # 按小时统计 if time_based == 'hour': start_time, end_time = get_current_day_time_ends() hours = get_hours(False) hours_zerofill = get_hours() result = dict(zip(hours, [0] * len(hours))) rows = db.session \ .query(func.hour(User.create_time).label('hour'), func.count(User.id)) \ .filter(User.create_time >= time_local_to_utc(start_time), User.create_time <= time_local_to_utc(end_time), User.status_active == STATUS_ACTIVE_OK) \ .group_by('hour') \ .limit(len(hours)) \ .all() result.update(dict(rows)) return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)] # 按日期统计 if time_based == 'date': start_time, end_time = get_current_month_time_ends() today = datetime.today() days = get_days(year=today.year, month=today.month, zerofill=False) days_zerofill = get_days(year=today.year, month=today.month) result = dict(zip(days, [0] * len(days))) rows = db.session \ .query(func.day(User.create_time).label('date'), func.count(User.id)) \ .filter(User.create_time >= time_local_to_utc(start_time), User.create_time <= time_local_to_utc(end_time), User.status_active == STATUS_ACTIVE_OK) \ .group_by('date') \ .limit(len(days)) \ .all() result.update(dict(rows)) return [(days_zerofill[i], result[day]) for i, day in enumerate(days)] # 按月份统计 if time_based == 'month': start_time, end_time = get_current_year_time_ends() months = get_months(False) months_zerofill = get_months() result = dict(zip(months, [0] * len(months))) rows = db.session \ .query(func.month(User.create_time).label('month'), func.count(User.id)) \ .filter(User.create_time >= time_local_to_utc(start_time), User.create_time <= time_local_to_utc(end_time), User.status_active == STATUS_ACTIVE_OK) \ .group_by('month') \ .limit(len(months)) \ .all() result.update(dict(rows)) return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
def sales_orders_order_stats(time_based='hour'): """ 报价成交统计 :return: """ condition = [SalesOrder.status_order == STATUS_ORDER_OK] # 按小时统计 if time_based == 'hour': start_time, end_time = get_current_day_time_ends() hours = get_hours(False) hours_zerofill = get_hours() result = dict(zip(hours, [0] * len(hours))) condition.extend( [ SalesOrder.create_time >= time_local_to_utc(start_time), SalesOrder.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.hour(SalesOrder.create_time).label('hour'), func.count(SalesOrder.id)) \ .filter(*condition) \ .group_by('hour') \ .limit(len(hours)) \ .all() result.update(dict(rows)) return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)] # 按日期统计 if time_based == 'date': start_time, end_time = get_current_month_time_ends() today = datetime.today() days = get_days(year=today.year, month=today.month, zerofill=False) days_zerofill = get_days(year=today.year, month=today.month) result = dict(zip(days, [0] * len(days))) condition.extend( [ SalesOrder.create_time >= time_local_to_utc(start_time), SalesOrder.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.day(SalesOrder.create_time).label('date'), func.count(SalesOrder.id)) \ .filter(*condition) \ .group_by('date') \ .limit(len(days)) \ .all() result.update(dict(rows)) return [(days_zerofill[i], result[day]) for i, day in enumerate(days)] # 按月份统计 if time_based == 'month': start_time, end_time = get_current_year_time_ends() months = get_months(False) months_zerofill = get_months() result = dict(zip(months, [0] * len(months))) condition.extend( [ SalesOrder.create_time >= time_local_to_utc(start_time), SalesOrder.create_time <= time_local_to_utc(end_time) ] ) rows = db_bearing.session \ .query(func.month(SalesOrder.create_time).label('month'), func.count(SalesOrder.id)) \ .filter(*condition) \ .group_by('month') \ .limit(len(months)) \ .all() result.update(dict(rows)) return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
def supplier_end_user_stats(time_based='hour'): """ 终端客户统计 :return: """ condition = [Supplier.company_type == TYPE_COMPANY_FINAL_USER] # 按小时统计 if time_based == 'hour': start_time, end_time = get_current_day_time_ends() hours = get_hours(False) hours_zerofill = get_hours() result = dict(zip(hours, [0] * len(hours))) condition.extend( [ Supplier.create_time >= time_local_to_utc(start_time), Supplier.create_time <= time_local_to_utc(end_time) ] ) rows = db.session \ .query(func.hour(Supplier.create_time).label('hour'), func.count(Supplier.id)) \ .filter(*condition) \ .group_by('hour') \ .limit(len(hours)) \ .all() result.update(dict(rows)) return [(hours_zerofill[i], result[hour]) for i, hour in enumerate(hours)] # 按日期统计 if time_based == 'date': start_time, end_time = get_current_month_time_ends() today = datetime.today() days = get_days(year=today.year, month=today.month, zerofill=False) days_zerofill = get_days(year=today.year, month=today.month) result = dict(zip(days, [0] * len(days))) condition.extend( [ Supplier.create_time >= time_local_to_utc(start_time), Supplier.create_time <= time_local_to_utc(end_time) ] ) rows = db.session \ .query(func.day(Supplier.create_time).label('date'), func.count(Supplier.id)) \ .filter(*condition) \ .group_by('date') \ .limit(len(days)) \ .all() result.update(dict(rows)) return [(days_zerofill[i], result[day]) for i, day in enumerate(days)] # 按月份统计 if time_based == 'month': start_time, end_time = get_current_year_time_ends() months = get_months(False) months_zerofill = get_months() result = dict(zip(months, [0] * len(months))) condition.extend( [ Supplier.create_time >= time_local_to_utc(start_time), Supplier.create_time <= time_local_to_utc(end_time) ] ) rows = db.session \ .query(func.month(Supplier.create_time).label('month'), func.count(Supplier.id)) \ .filter(*condition) \ .group_by('month') \ .limit(len(months)) \ .all() result.update(dict(rows)) return [(months_zerofill[i], result[month]) for i, month in enumerate(months)]
def lists(page=1): """ 会员列表 """ form = UserSearchForm(request.form) user_id = request.args.get('user_id', '', type=int) user_name = request.args.get('user_name', '', type=str) start_time = request.args.get('start_time', '', type=str) end_time = request.args.get('end_time', '', type=str) status_active = request.args.get('status_active', '', type=str) status_lock = request.args.get('status_lock', '', type=str) op = request.args.get('op', 0, type=int) form.user_id.data = user_id form.user_name.data = user_name form.start_time.data = start_time form.end_time.data = end_time form.status_active.data = status_active form.status_lock.data = status_lock search_condition_user = [User.status_delete == STATUS_DEL_NO] search_condition_user_profile = [] # 多次连接同一张表,需要别名 user_profile_c = aliased(UserProfile) # 子 user_profile_p = aliased(UserProfile) # 父 if user_id: search_condition_user.append(User.id == user_id) if start_time: search_condition_user.append( User.create_time >= time_local_to_utc(start_time)) if end_time: search_condition_user.append( User.create_time <= time_local_to_utc(end_time)) if status_active: search_condition_user.append(User.status_active == status_active) if status_lock: search_condition_user.append(User.status_lock == status_lock) if user_name: search_condition_user_profile.append( user_profile_c.nickname == user_name) # 处理导出 if op == 1: if not SWITCH_EXPORT or SWITCH_EXPORT == 'OFF': flash(u'导出功能关闭,暂不支持导出', 'warning') return redirect(url_for('user.lists')) data_list = [] try: query_sets = User.query. \ filter(*search_condition_user). \ outerjoin(user_profile_c, User.id == user_profile_c.user_id). \ filter(*search_condition_user_profile). \ outerjoin(user_profile_p, user_profile_c.user_pid == user_profile_p.user_id). \ outerjoin(Wallet, User.id == Wallet.user_id). \ outerjoin(BitCoin, User.id == BitCoin.user_id). \ outerjoin(Score, User.id == Score.user_id). \ outerjoin(Bonus, User.id == Bonus.user_id). \ outerjoin(Scheduling, User.id == Scheduling.user_id). \ add_entity(user_profile_c). \ add_entity(user_profile_p). \ add_entity(Wallet). \ add_entity(BitCoin). \ add_entity(Score). \ add_entity(Bonus). \ add_entity(Scheduling). \ all() db.session.commit() column_names = [ u'用户编号', u'用户名称', u'等级', u'手机号码', u'推荐人', u'钱包余额', u'数字货币', u'积分', u'奖金', u'激活状态', u'锁定状态', u'创建时间' ] data_list.append(column_names) for (user, user_profile_c, user_profile_p, wallet, bit_coin, score, bonus) in query_sets: row = [ user.id if user else '', user_profile_c.nickname if user_profile_c else '', user_profile_c.type_level if user_profile_c else 0, user_profile_c.phone if user_profile_c else '', user_profile_p.nickname if user_profile_p else '', wallet.amount_current if wallet else 0, bit_coin.amount if bit_coin else 0, score.amount if score else 0, bonus.amount if bonus else 0, user.status_active if user else 0, user.status_lock if user else 0, user.create_time if user else '' ] data_list.append(row) return excel.make_response_from_array( data_list, "csv", file_name="用户列表_%s" % datetime.now().strftime('%Y-%m-%d %H:%M:%S')) except Exception as e: db.session.rollback() flash(e.message, category='warning') return redirect(url_for('index')) # 处理查询 try: pagination = User.query. \ filter(*search_condition_user). \ outerjoin(user_profile_c, User.id == user_profile_c.user_id). \ filter(*search_condition_user_profile). \ outerjoin(user_profile_p, user_profile_c.user_pid == user_profile_p.user_id). \ outerjoin(Wallet, User.id == Wallet.user_id). \ outerjoin(BitCoin, User.id == BitCoin.user_id). \ outerjoin(Score, User.id == Score.user_id). \ outerjoin(Bonus, User.id == Bonus.user_id). \ outerjoin(Scheduling, User.id == Scheduling.user_id). \ add_entity(user_profile_c). \ add_entity(user_profile_p). \ add_entity(Wallet). \ add_entity(BitCoin). \ add_entity(Score). \ add_entity(Bonus). \ add_entity(Scheduling). \ paginate(page, PER_PAGE_BACKEND, False) db.session.commit() return render_template( 'user/list.html', title='user_list', pagination=pagination, form=form, STATUS_LOCK_OK=STATUS_LOCK_OK, STATUS_DEL_OK=STATUS_DEL_OK, ) except Exception as e: db.session.rollback() print traceback.print_exc() flash(e.message, category='warning') return redirect(url_for('index'))
def lists(page=1): """ 订单列表 """ form = OrderSearchForm(request.form) order_id = request.args.get('order_id', '', type=int) apply_put_id = request.args.get('apply_put_id', '', type=int) apply_get_id = request.args.get('apply_get_id', '', type=int) apply_put_uid = request.args.get('apply_put_uid', '', type=int) apply_get_uid = request.args.get('apply_get_uid', '', type=int) status_audit = request.args.get('status_audit', '', type=str) status_pay = request.args.get('status_pay', '', type=str) status_rec = request.args.get('status_rec', '', type=str) start_time = request.args.get('start_time', '', type=str) end_time = request.args.get('end_time', '', type=str) op = request.args.get('op', 0, type=int) form.order_id.data = order_id form.apply_put_id.data = apply_put_id form.apply_get_id.data = apply_get_id form.apply_put_uid.data = apply_put_uid form.apply_get_uid.data = apply_get_uid form.status_audit.data = status_audit form.status_pay.data = status_pay form.status_rec.data = status_rec form.start_time.data = start_time form.end_time.data = end_time # 搜索条件 search_condition_order = [ Order.status_delete == STATUS_DEL_NO, ] if order_id: search_condition_order.append(Order.id == order_id) if apply_put_id: search_condition_order.append(Order.apply_put_id == apply_put_id) if apply_get_id: search_condition_order.append(Order.apply_get_id == apply_get_id) if apply_put_uid: search_condition_order.append(Order.apply_put_uid == apply_put_uid) if apply_get_uid: search_condition_order.append(Order.apply_get_uid == apply_get_uid) if status_audit: search_condition_order.append(Order.status_audit == status_audit) if status_pay: search_condition_order.append(Order.status_pay == status_pay) if status_rec: search_condition_order.append(Order.status_rec == status_rec) if start_time: search_condition_order.append( Order.create_time >= time_local_to_utc(start_time)) if end_time: search_condition_order.append( Order.create_time <= time_local_to_utc(end_time)) # 多次连接同一张表,需要别名 user_profile_put = aliased(UserProfile) user_profile_get = aliased(UserProfile) try: pagination = Order.query. \ filter(*search_condition_order). \ outerjoin(user_profile_put, Order.apply_put_uid == user_profile_put.user_id). \ add_entity(user_profile_put). \ outerjoin(user_profile_get, Order.apply_get_uid == user_profile_get.user_id). \ add_entity(user_profile_get). \ order_by(Order.id.desc()). \ paginate(page, PER_PAGE_BACKEND, False) db.session.commit() return render_template('order/list.html', title='order_list', pagination=pagination, form=form) except Exception as e: db.session.rollback() flash(e.message, category='warning') return redirect(url_for('index'))