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