def three_query(self, model, session, method="day", format="%Y-%m-%d %H"):
"""查询最大值、最小值、平均值"""
model_query = session.query(func.date_format(model.create_dt, format),
func.min(model.percent),
func.max(model.percent),
func.avg(model.percent))
data = None
if method == "day":
data = model_query.filter(
func.to_days(model.create_dt) == func.to_days(
func.now()) # 过滤出今天的数据
).group_by(func.date_format(model.create_dt, format) # 按照小时分组
).order_by(
func.date_format(model.create_dt,
format).asc() # 按照时间升序排序
).all()
if method == "month":
data = model_query.filter(
func.date_format(model.create_dt, "%Y-%m") == func.date_format(
func.curdate(), "%Y-%m") # 过滤前月的数据
).group_by(func.date_format(model.create_dt, format) # 按照天来分组
).order_by(
func.date_format(model.create_dt,
format).asc() # 按照时间升序排序
).all()
return data
async def three_query(model, session, method="day", _format="%Y%m%d%H"):
"""
func.date_format(model.create_dt, format)转化日期格式，把第1个参数转化为format格式
"""
model_query = session.query(func.date_format(model.create_dt, _format),
func.min(model.percent),
func.max(model.percent),
func.avg(model.percent))
data = None
if method == "day":
# 过滤出今天的数据
# 按照小时分组
# 按照时间升序排序
data = await as_future(
model_query.filter(
func.to_days(model.create_dt) ==
func.to_days(func.now())).group_by(
func.date_format(model.create_dt, _format)).order_by(
model.create_dt.asc()).all)
if method == "month":
# 过滤出当前月的数据
# 按照天来分组
# 按照时间升序排序
data = await as_future(
model_query.filter(
func.date_format(model.create_dt, "%Y%m") ==
func.date_format(func.curdate(), "%Y%m")).group_by(
model.create_date).order_by(model.create_dt.asc()).all)
return data
def report_stats():
query = db.session.query(Ticket, func.count(Ticket.id)).filter(
Ticket.is_closed == False).group_by(Ticket.current_area_id)
area_ticket_count = [(r[0].current_area.name, r[1]) for r in query]
tickets_opened_today = 0
n = db.session.query(func.count(distinct(TicketReopenEvent.id))) \
.filter(func.to_days(TicketReopenEvent.at) == func.to_days(func.now())) \
.group_by(func.to_days(TicketReopenEvent.at)).scalar() or 0
tickets_opened_today = tickets_opened_today + n
n = db.session.query(func.count(Ticket.id)) \
.filter(func.to_days(Ticket.created_at) == func.to_days(func.now())) \
.group_by(func.to_days(Ticket.created_at)).scalar() or 0
tickets_opened_today = tickets_opened_today + n
n = db.session.query(func.count(TicketCloseEvent.id)) \
.filter(func.to_days(TicketCloseEvent.at) == func.to_days(func.now())) \
.group_by(func.to_days(TicketCloseEvent.at)).scalar() or 0
tickets_closed_today = n
return render_template('report_stats.html',
area_ticket_count=area_ticket_count,
tickets_opened_today=tickets_opened_today,
tickets_closed_today=tickets_closed_today,
now=datetime.now())
def three_query(self, model, session, method="Day", format="%Y%m%d%H"):
model_query = session.query(
func.date_format(model.create_dt, format), #转化日期格式
func.max(model.percent),
func.min(model.percent),
func.avg(model.percent),
)
data = None
if method == "Day":
data = model_query.filter(
func.to_days(model.create_dt) == func.to_days(
func.now())).group_by(
func.date_format(model.create_dt, format)).order_by(
model.create_dt.asc()).all()
if method == "month":
data = model_query.filter(
func.date_format(model.create_dt, "%Y%m") == func.date_format(
func.curdate(), "%Y%m")).group_by(
model.create_dt).order_by(model.create_dt.asc()).all()
return data
def submit_by_day(cls, days=30):
date_limit = datetime.now() - timedelta(days)
# multi-database support (MySQL, PostgreSQL, SQLite) for date conversion
engine = db.Session.get_bind()
if "sqlite" in engine.driver: # could be 'sqlite', or 'pysqlite'
fn = cast(func.julianday(cls.created_on), Integer)
elif engine.driver == "postgresql":
fn = cast(cls.created_on, sqlalchemy.types.Date)
else:
fn = func.to_days(cls.created_on)
query = (
db.Session.query(fn.label("day"), func.count("*").label("count"))
.filter(cls.created_on > date_limit)
.group_by("day")
.order_by("day ASC")
)
results = [row.count for row in query.all()]
return results
def get(self):
"""
获取用户总收益
:return:
"""
parse = reqparse.RequestParser()
parse.add_argument('coin_name', type=str, required=False)
args = parse.parse_args()
coin_name = args.get('coin_name')
if not coin_name:
coin_name = BHD_COIN_NAME
if coin_name == BHD_COIN_NAME:
model = IncomeRecord
elif coin_name == LHD_NAME:
model = LHDIncomeRecord
elif coin_name == DISK_NAME:
model = DISKIncomeRecord
elif coin_name == HDD_NAME:
model = HDDIncomeRecord
elif coin_name == NEWBI_NAME:
model = NBIncomeRecord
else:
return make_resp(400, False, message="请求币种错误")
account_key = g.account_key
# 合作-裸挖-挖矿总收入
mining_total_amount = model.query.filter_by(
account_key=account_key).filter(
model.type.in_([IncomeTypeMining,
IncomeTYpeMiningEcol])).with_entities(
func.sum(model.amount)).first()[0]
# 昨天挖矿所得
mining_last_day = model.query.filter_by(
account_key=account_key).filter(
func.to_days(model.create_time) == func.to_days(func.now()) -
1).filter(
model.type.in_([IncomeTypeMining,
IncomeTYpeMiningEcol])).with_entities(
func.sum(model.amount)).first()[0]
if coin_name == NEWBI_NAME:
return make_resp(mining_total_amount=mining_total_amount,
mining_last_day=mining_last_day)
# 合作所得总
coop_total_amount = model.query.filter_by(
account_key=account_key, type=IncomeTYpeCoopReward).with_entities(
func.sum(model.amount)).first()[0]
# 昨日合作所得
coop_last_day = model.query.filter_by(
account_key=account_key, type=IncomeTYpeCoopReward).filter(
func.to_days(model.create_time) == func.to_days(func.now()) -
1).with_entities(func.sum(model.amount)).first()[0]
if coin_name == LHD_NAME:
return make_resp(
mining_total_amount=mining_total_amount,
mining_last_day=mining_last_day,
coop_total_amount=coop_total_amount,
coop_last_day=coop_last_day,
)
# 理财收益
manage_money_amount = model.query.filter_by(
account_key=account_key, type=IncomeTYpemanagemoney).with_entities(
func.sum(model.amount)).first()[0]
# 活动总收益
activity_rewards_total_amount = ActivityReward.query.filter_by(
account_key=account_key).filter(
ActivityReward.amount > 0).with_entities(
func.sum(ActivityReward.amount)).first()[0]
# 昨日活动收益
activity_rewards_last_day = ActivityReward.query.filter_by(
account_key=account_key).filter(
func.to_days(
ActivityReward.create_time) == func.to_days(func.now()) -
1).filter(ActivityReward.amount > 0).with_entities(
func.sum(ActivityReward.amount)).first()[0]
return make_resp(
mining_total_amount=mining_total_amount,
mining_last_day=mining_last_day,
coop_total_amount=coop_total_amount,
coop_last_day=coop_last_day,
activity_rewards_total_amount=activity_rewards_total_amount,
activity_rewards_last_day=activity_rewards_last_day,
manage_money_amount=manage_money_amount)
def get(self):
"""
按天获取收益列表
:return:
"""
account_key = g.account_key
parse = reqparse.RequestParser()
now = int(time.time())
parse.add_argument('limit', type=int, required=False, default=10)
parse.add_argument('offset', type=int, required=False, default=0)
parse.add_argument('from', type=int, required=False, default=0)
parse.add_argument('end', type=int, required=False, default=now)
parse.add_argument('coin_name', type=str, required=False)
parse.add_argument('status', type=int, required=False)
args = parse.parse_args()
limit = args.get('limit')
offset = args.get('offset')
from_ts = args.get('from')
end_ts = args.get('end')
coin_name = args.get('coin_name')
status = args.get('status')
from_dt = datetime.fromtimestamp(from_ts)
end_dt = datetime.fromtimestamp(end_ts)
kwargs = {"account_key": account_key}
if not coin_name:
coin_name = BHD_COIN_NAME
if coin_name == BHD_COIN_NAME:
model = IncomeRecord
elif coin_name == LHD_NAME:
model = LHDIncomeRecord
elif coin_name == DISK_NAME:
model = DISKIncomeRecord
elif coin_name == HDD_NAME:
model = HDDIncomeRecord
elif coin_name == NEWBI_NAME:
model = NBIncomeRecord
else:
return make_resp(400, False, message="请求币种错误")
# manage_money_amount = model.query.filter_by(
# account_key=account_key, type=IncomeTYpemanagemoney
# ).filter(
# and_(model.create_time > from_dt,
# model.create_time < end_dt)
# ).with_entities(func.sum(model.amount)).first()[0]
infos = model.query.filter_by(**kwargs).with_entities( # 查询指定的列
model.type,
func.sum(model.amount),
func.date(model.create_time),
func.avg(model.capacity),
).filter(and_(
model.create_time > from_dt, model.create_time < end_dt)).order_by(
model.create_time.desc()).group_by(
model.type, func.date(
model.create_time)).limit(limit).offset(offset).all()
total_records = model.query.filter_by(**kwargs).group_by(
func.to_days(model.create_time)).count()
date_incomes = {}
for income_type, amount, create_time, capacity in infos:
create_time = str(create_time)
if create_time not in date_incomes:
date_incomes[create_time] = {
'coop_income': 0,
'mining_income': 0,
"manage_money_income": 0,
'capacity': 0
}
day_income = date_incomes[create_time]
filed_name = "coop_income"
if income_type == IncomeTypeMining:
filed_name = "mining_income"
day_income["capacity"] = capacity
elif income_type == IncomeTYpemanagemoney:
filed_name = "manage_money_income"
day_income[filed_name] = amount
day_income["total_income"] = day_income.get("total_income",
0) + amount
records = sorted(date_incomes.items(),
key=lambda k: k[0],
reverse=True)
return make_resp(records=records, total_records=total_records)