def process(self, *args, **kwargs):
self.logtimes()
with make_db(self.db) as db:
try:
nodes = db.query(models.TrNode)
for node in nodes:
r = db.query(
func.sum(models.TrOnline.input_total).label("input_total"),
func.sum(models.TrOnline.output_total).label("output_total")
).filter(
models.TrOnline.account_number == models.TrAccount.account_number,
models.TrAccount.customer_id == models.TrCustomer.customer_id,
models.TrCustomer.node_id == node.id
).first()
if r and all([r.input_total,r.output_total]):
stat = models.TrFlowStat()
stat.node_id = node.id
stat.stat_time = int(time.time())
stat.input_total = r.input_total
stat.output_total = r.output_total
db.add(stat)
# clean expire data
_time = int(time.time()) - (86400 * 2)
db.query(models.TrFlowStat).filter(models.TrFlowStat.stat_time < _time).delete()
db.commit()
logger.info("flow stat task done")
except Exception as err:
db.rollback()
logger.error('flow_stat_job err,%s'%(str(err)))
return self.get_notify_interval()
def single_order_list(id):
order_list = database.DbOrderList.query.get(id)
orders = []
customers = []
if order_list is None:
flash('This order list does not exist')
else:
orders = database.DbOrder.query.filter_by(list_id=id).all()
# count_payed = case([(database.DbOrderItem.payed,
# database.DbItem.price * database.DbOrderItem.quantity)],
# else_=0)
# Very similar to query in single_order, could re-use code
customers = (db.session.query(func.sum(database.DbOrderItem.quantity).label('quantity'),
func.sum(database.DbItem.price * database.DbOrderItem.quantity).label('total_price'),
# func.sum(count_payed).label('payed'),
database.User)
.join(database.DbOrder)
.join(database.DbOrderList)
.join(database.DbItem)
.filter(database.DbOrderList.id == order_list.id)
.join(database.User)
.group_by(database.User.id)
.all())
return render_template('order_list_single.html',
list=order_list,
orders=orders,
customers=customers)
def allocation():
loc = "nav-allocation"
if request.method == "POST" and request.form["action"] == "insert":
#insert new allocation
amount = request.form["amount"]
category_id = request.form["categoryid"]
allocation = Transaction(g.user.id, amount, "Allocation", category_id)
db.session.add(allocation)
db.session.commit()
success = (True if allocation.id != -1 else False)
return render_template("allocation.html", success = success, locid = loc)
elif request.method == "POST" and request.form["action"] == "add":
#show add allocation
return render_template("allocation.html", action = "add", net_income = net_income(), category = Category.query.filter_by(category_type="Expense"), locid = loc)
else:
#show allocation list
allocation_total = 0
qry = db.session.query(Transaction, func.sum(Transaction.amount).label("total_amount")).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id)
for _res in qry.all():
allocation_total = _res.total_amount
percentage_list = []
qry = db.session.query(Transaction, Category, func.sum(Transaction.amount).label("total_amount")).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id).join(Category).group_by(Category.description)
for _res in qry.all():
a = { "percent" : int(round(((_res.total_amount / allocation_total) * 100))), "description" : _res.Category.description, "amount" : _res.total_amount }
percentage_list.append(a)
rs = db.session.query(Transaction, Category).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id).join(Category)
return render_template("allocation.html", action = "show", allocation = rs, percentage_list=percentage_list, locid = loc)
def project_outline(name):
# name=request.args.get('name')
P=models.Projects.query.all()
project=models.Projects.query.filter_by(id=name).first()
G=project.goals.all()
gform=goal_form(request.values)
delete_form=DeleteRow_form()
q_sum = (db.session.query(
Projects.id.label("project_id"),
Goals.id.label("goal_id"),
func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"),
func.count(Tasks.id).label("total"),
).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id).filter(Projects.id == name) )
if request.method == 'POST' and gform.submit.data:
if gform.validate() == False:
flash('Failed Field validation.')
flash_errors(gform)
return redirect(url_for('project_outline', name=name))
else:
p=models.Goals(goal=gform.goal.data,proj=project)
db.session.add(p)
db.session.commit()
return redirect(url_for('project_outline', name=name))
if request.method == 'POST' and delete_form.submitd.data:
pstratrow = delete_form.row_id.data
pstrat=models.Goals.query.filter_by(id=pstratrow).first()
db.session.delete(pstrat)
db.session.commit()
return redirect(url_for('project_outline',name=name))
# if request.method == 'POST' and delete_form.submit.data:
# delete_row=
return render_template("index_for_goal.html",project=project,G=G,gform=gform,P=P,zipit=zip(G,q_sum),delete_form=delete_form)
def get_biggest_donations(cls, limit=None, offset=None):
"""Getter for biggest donations.
Donations from the same person are grouped.
Args:
limit: Maximum number of donations to be returned.
offset: Offset of the result.
Returns:
Tuple with two items. First is total number if donations. Second
is a list of donations sorted by amount with a specified offset.
"""
query = db.session.query(
cls.first_name.label("first_name"),
cls.last_name.label("last_name"),
cls.editor_name.label("editor_name"),
func.max(cls.payment_date).label("payment_date"),
func.sum(cls.amount).label("amount"),
func.sum(cls.fee).label("fee"),
)
query = query.filter(cls.anonymous == False)
query = query.group_by(cls.first_name, cls.last_name, cls.editor_name)
query = query.order_by(desc("amount"))
count = query.count() # Total count should be calculated before limits
if limit is not None:
query = query.limit(limit)
if offset is not None:
query = query.offset(offset)
return count, query.all()
def get_cash_flows():
date_range_filter_schema = DateRangeFilterSchema().load(request.args)
if date_range_filter_schema.errors:
return {'errors': date_range_filter_schema.errors}, 400
cash_flow_schema = CashFlowSchema()
amounts = db.session.query(
func.sum(Record.amount).label("cash_flow"),
func.sum(
case([(Record.record_type == Record.RECORD_TYPE_INCOME, Record.amount)], else_=0)
).label('income'),
func.sum(
case([(Record.record_type == Record.RECORD_TYPE_EXPENSE, Record.amount)], else_=0)
).label('expense'),
func.date_trunc('month', Record.date).label("date"),
).group_by(
func.date_trunc('month', Record.date)
).order_by(
func.date_trunc('month', Record.date)
)
if 'date_from' in date_range_filter_schema.data:
amounts = amounts.filter(Record.date >= date_range_filter_schema.data['date_from'])
if 'date_to' in date_range_filter_schema.data:
amounts = amounts.filter(Record.date < date_range_filter_schema.data['date_to'])
return {'objects': cash_flow_schema.dump(amounts, many=True).data}
def get_taxi_data_count(color):
if color == 'all':
base_query = session.query(func.sum(TripStats.total_record_cnt))
yellow_count = base_query.filter(TripStats.taxi_type == 2).first()[0]
green_count = base_query.filter(TripStats.taxi_type == 1).first()[0]
return return_json({
"yellow": {
"day": int(yellow_count/365),
"week": int(yellow_count/52),
"month": int(yellow_count/12),
"year": int(yellow_count)
},
"green": {
"day": int(green_count/365),
"week": int(green_count/52),
"month": int(green_count/12),
"year": int(green_count)
}
})
taxi_type = COLOR_CODE[color]
count = session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == taxi_type).first()[0]
if 'average' in request.args:
if request.args['average'] == 'day':
return return_json({"count": int(count/365), "type": color})
elif request.args['average'] == 'month':
return return_json({"count": int(count/12), "type": color})
elif request.args['average'] == 'week':
return return_json({"count": int(count/52), "type": color})
return return_json({"count": count, "type": color})
def _repayments(self):
today_repayments = Plan.query.filter(
cast(Plan.plan_time, Date) == date.today(),
Plan.status == get_enum('PLAN_PENDING')
).order_by('plan_time desc').limit(10)
today_repay_amount = db_session.query(
func.sum(Plan.amount)
).filter(
cast(Plan.plan_time, Date) == date.today(),
Plan.status == get_enum('PLAN_PENDING')
).scalar()
if not today_repay_amount:
today_repay_amount = 0
total_repay_amount = db_session.query(
func.sum(Plan.amount)
).filter(Plan.status == get_enum('PLAN_PENDING')).scalar()
if not total_repay_amount:
total_repay_amount = 0
app.jinja_env.globals['today_repay_amount'] = today_repay_amount
app.jinja_env.globals['total_repay_amount'] = total_repay_amount
app.jinja_env.globals['today_repayments'] = today_repayments
def strategy_outline(name,goal):
P=models.Projects.query.all()
project=models.Projects.query.filter_by(id=name).first()
pgoal=models.Goals.query.filter_by(id=goal).first()
S=pgoal.strategies.all()
sform=strategy_form(request.values)
delete_form=DeleteRow_form()
q_sum = (db.session.query(
Projects.id.label("project_id"),
func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"),
func.count(Tasks.id).label("total"),
Strategies.id.label("strategy_id"),
Goals.id.label("goal_id"),
).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id,Strategies.id).filter(Goals.id == goal) )
if request.method == 'POST' and sform.submit.data:
print sform.validate()
if sform.validate() == False:
flash('Failed Field validation.')
flash_errors(sform)
return redirect(url_for('strategy_outline',name=name,goal=goal))
else:
p=models.Strategies(strategy=sform.strategy.data,goa=pgoal)
db.session.add(p)
db.session.commit()
return redirect(url_for('strategy_outline',name=name,goal=goal))
if request.method == 'POST' and delete_form.submitd.data:
pstratrow = delete_form.row_id.data
pstrat=models.Strategies.query.filter_by(id=pstratrow).first()
db.session.delete(pstrat)
db.session.commit()
return redirect(url_for('strategy_outline',name=name,goal=goal))
return render_template("index_for_strategy.html",project=project,S=S,sform=sform,pgoal=pgoal,P=P,zipit=zip(S,q_sum),delete_form=delete_form)
def _columns(self, total_row=False):
columns = (
self._column_helper("rations", "male", "st"),
self._column_helper("rations", "female", "st"),
self._column_helper("rations", "male", "sc"),
self._column_helper("rations", "female", "sc"),
self._column_helper("rations", "male", "others"),
self._column_helper("rations", "female", "others"),
self._column_helper("rations", "male", "disabled"),
self._column_helper("rations", "female", "disabled"),
self._column_helper("rations", "male", "minority"),
self._column_helper("rations", "female", "minority"),
self._column_helper("absent", "male"),
self._column_helper("absent", "female"),
self._column_helper("partial", "male"),
self._column_helper("partial", "female"),
self._column_helper("rations", "migrant", "male"),
self._column_helper("rations", "migrant", "female"),
func.count(self.table.c.case_id).filter(
self.table.c.sex == 'M').label("child_count_male"),
func.count(self.table.c.case_id).filter(
self.table.c.sex == 'F').label("child_count_female"),
func.sum(self.table.c.num_rations_distributed).filter(
self.table.c.sex == 'M').label("thr_total_rations_male"),
func.sum(self.table.c.num_rations_distributed).filter(
self.table.c.sex == 'F').label("thr_total_rations_female"),
)
if not total_row:
return (self.table.c.awc_id.label("owner_id"),) + columns
return columns
def child_victimisation_worksheet(self, wb):
from dexter.models.views import DocumentChildrenView
ws = wb.add_worksheet("child_secondary_victimisation")
rows = self.filter(
db.session.query(
func.sum(
DocumentChildrenView.c.secondary_victim_source == "secondary-victim-source", type_=Integer
).label("secondary_victim_source"),
func.sum(
DocumentChildrenView.c.secondary_victim_identified == "secondary-victim-identified", type_=Integer
).label("secondary_victim_identified"),
func.sum(
DocumentChildrenView.c.secondary_victim_victim_of_abuse == "secondary-victim-abused", type_=Integer
).label("secondary_victim_victim_of_abuse"),
func.sum(
DocumentChildrenView.c.secondary_victim_source_identified_abused
== "secondary-victim-source-identified-abused",
type_=Integer,
).label("secondary_victim_source_identified_abused"),
).join(Document)
).all()
if not rows:
return
d = rows[0]._asdict()
data = [[k, d[k]] for k in sorted(d.keys(), key=len)]
ws.add_table(
0,
0,
len(data),
1,
{"name": "ChildSecondaryVictimisation", "data": data, "columns": [{"header": ""}, {"header": "count"}]},
)
def agg_supply(timeid):
s_dict = {k: 0 for k in range(1, 14)}
##pull units devoted to production...
if timeid == 1 or session.query(Inventory.id).filter(Inventory.timeid==timeid-1).count() == 0:
result = session.query(Business.industryid, func.sum(Business.produnits)).group_by(Business.industryid).order_by(Business.industryid).all()
for id, produnits in result:
s = produnits * 500
s_dict[id] = s
##commit supply to the database
supply = Supply(timeid=timeid, industryid=id, endunits=s)
session.add(supply)
session.commit()
else:
result = session.query(Business.industryid, func.sum(Business.produnits), func.sum(Inventory.units)).filter(Inventory.timeid==timeid-1, Business.id==Inventory.businessid).group_by(Business.industryid).order_by(Business.industryid).all()
for id, produnits, inventory in result:
##calculate the supply for each record...
s = inventory + produnits * 500
s_dict[id] = s
##commit supply to the database
supply = Supply(timeid=timeid, industryid=id, endunits=s)
session.add(supply)
session.commit()
##return a supply dataframe for program
df = pd.DataFrame(s_dict.values(), index=s_dict.keys(), columns=["supply"])
return df
def get_line_sum(): line = int(request.form['line']) chosen_direction = request.form['direction'] if chosen_direction in ['Inbound','Outbound']: for result in db.session.query(func.count(Output.ZONE_NUMBER.distinct())).filter_by(LINE_NUMBER = line, DIRECTION = chosen_direction): length = result[0] zones, fare, cost = initialize_variables(length) i = 0 while (i < length): zones[i] = db.session.query(Output.ZONE_NUMBER.distinct()).filter_by(LINE_NUMBER = line, DIRECTION = chosen_direction)[i][0] for fare_sum in db.session.query(func.sum(Output.FARE_COLLECTED)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i], DIRECTION = chosen_direction): fare[i] = fare_sum[0] for cost_sum in db.session.query(func.sum(Output.TOTAL_OPERATING_COST)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i], DIRECTION = chosen_direction): cost[i] = cost_sum[0] i += 1 return zones, fare, cost else: for result in db.session.query(func.count(Output.ZONE_NUMBER.distinct())).filter_by(LINE_NUMBER = line): length = result[0] zones, fare, cost = initialize_variables(length) i = 0 while (i < length): zones[i] = db.session.query(Output.ZONE_NUMBER.distinct()).filter_by(LINE_NUMBER = line)[i][0] for fare_sum in db.session.query(func.sum(Output.FARE_COLLECTED)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i]): fare[i] = fare_sum[0] for cost_sum in db.session.query(func.sum(Output.TOTAL_OPERATING_COST)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i]): cost[i] = cost_sum[0] i += 1 return zones, fare, cost
def _investments(self):
today_investments = Investment.query.filter(
cast(Investment.added_at, Date) == date.today(),
Investment.status.in_(
(get_enum('INVESTMENT_PENDING'),
get_enum('INVESTMENT_SUCCESSED'))
)
).order_by("added_at desc").limit(10)
history_investments = db_session.query(
func.date_trunc('day', Investment.added_at),
func.sum(Investment.amount)).group_by(
func.date_trunc('day', Investment.added_at)
).order_by(func.date_trunc('day', Investment.added_at)).all()
total_investments = db_session.query(
func.sum(Investment.amount)).scalar()
today_invest_amount = db_session.query(
func.sum(Investment.amount)).filter(
cast(Investment.added_at, Date) == date.today(),
Investment.status.in_(
(get_enum('INVESTMENT_PENDING'),
get_enum('INVESTMENT_SUCCESSED'))
)
).scalar()
if not today_invest_amount:
today_invest_amount = 0
app.jinja_env.globals['today_invest_amount'] = today_invest_amount
app.jinja_env.globals['today_investments'] = today_investments
app.jinja_env.globals['total_investments'] = total_investments
app.jinja_env.globals['history_investments'] = history_investments
def get_scan_information():
if not ValidateClass.check_login():
return redirect(ADMIN_URL + '/index')
if request.method == "POST":
start_time_stamp = request.form.get("start_time_stamp")[0:10]
end_time_stamp = request.form.get("end_time_stamp")[0:10]
start_time_array = datetime.datetime.fromtimestamp(int(start_time_stamp))
end_time_array = datetime.datetime.fromtimestamp(int(end_time_stamp))
if start_time_stamp >= end_time_stamp:
return jsonify(tag="danger", msg="wrong date select.", code=1002)
task_count = CobraTaskInfo.query.filter(
and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp)
).count()
vulns_count = CobraResults.query.filter(
and_(CobraResults.created_at >= start_time_array, CobraResults.created_at <= end_time_array)
).count()
projects_count = CobraProjects.query.filter(
and_(CobraProjects.last_scan >= start_time_array, CobraProjects.last_scan <= end_time_array)
).count()
files_count = db.session.query(func.sum(CobraTaskInfo.file_count).label('files')).filter(
and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp)
).first()[0]
code_number = db.session.query(func.sum(CobraTaskInfo.code_number).label('codes')).filter(
and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp)
).first()[0]
return jsonify(code=1001, task_count=task_count, vulns_count=vulns_count, projects_count=projects_count,
files_count=int(files_count), code_number=int(code_number))
def calc_level_type_skill(self, until=None):
query1 = (
db.session.query(func.count(TriggeredEvent.id), func.sum(TriggeredEvent.given_skill_points).label("sum_1"))
.join(Participation)
.join(LevelInstance)
.join(Level)
.join(Level.level_types)
.filter(Participation.pid == self.pid)
.filter(LevelType.id == self.ltid)
)
query2 = (
db.session.query(func.sum(TriggeredEvent.given_score_points).label("sum_1"))
.join(Participation)
.join(LevelInstance)
.join(Level)
.join(Level.level_types)
.filter(Participation.pid == self.pid)
.filter(LevelType.id == self.ltid)
.group_by(LevelInstance.id)
.order_by(desc("sum_1"))
)
if until:
query1 = query1.filter(TriggeredEvent.timestamp < until)
query2 = query2.filter(TriggeredEvent.timestamp < until)
considered_rows, skill_points = query1.all()[0] if query1.all()[0] else 0
score_points = query2.all()[0][0] if query2.all() else 0
return considered_rows, skill_points, score_points
def execute(mk_db):
log.msg("start flow stat task..")
db = mk_db()
try:
nodes = db.query(models.SlcNode)
for node in nodes:
r = db.query(
func.sum(models.SlcRadOnline.input_total).label("input_total"),
func.sum(models.SlcRadOnline.output_total).label("output_total")
).filter(
models.SlcRadOnline.account_number == models.SlcRadAccount.account_number,
models.SlcRadAccount.member_id == models.SlcMember.member_id,
models.SlcMember.node_id == node.id
).first()
if r:
stat = models.SlcRadFlowStat()
stat.node_id = node.id
stat.stat_time = int(time.time())
stat.input_total = r.input_total
stat.output_total = r.output_total
db.add(stat)
db.commit()
log.msg("flow stat task done")
except Exception as err:
db.rollback()
log.err(err,'flow_stat_job err')
finally:
db.close()
def get_taxi_data_distribution(color):
if color != 'all':
taxi_type = COLOR_CODE[color]
# TODO:
# By default, we will just return a key-value pair of month and count
out_data = []
if 'interval' in request.args:
if request.args['interval'] == 'month':
# distribution by month
# NOTE: Using a different mechanism now!!!!!!
for i in range(1, 13):
# out_data.append({
# "date": i,
# "count": session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == taxi_type, extract('month', TripStats.datetime) == i).first()[0]
# })
out_data.append({
"date": i,
"type": 2,
"count": session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == 2, extract('month', TripStats.datetime) == i).first()[0]
})
out_data.append({
"date": i,
"type": 1,
"count": session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == 1, extract('month', TripStats.datetime) == i).first()[0]
})
elif request.args['interval'] == 'week':
# distribution by weekday
yellow_trips = TripStats.query.filter(TripStats.taxi_type == 2).all()
green_trips = TripStats.query.filter(TripStats.taxi_type == 1).all()
mid_data_yellow = {}
mid_data_green = {}
for i in range(0, 8):
for trip in yellow_trips:
if i in mid_data_yellow and trip.datetime.weekday() == i:
mid_data_yellow[i] += trip.total_record_cnt
elif trip.datetime.weekday() == i:
mid_data_yellow[i] = trip.total_record_cnt
for i in range(0, 8):
for trip in green_trips:
if i in mid_data_green and trip.datetime.weekday() == i:
mid_data_green[i] += trip.total_record_cnt
elif trip.datetime.weekday() == i:
mid_data_green[i] = trip.total_record_cnt
for key, val in mid_data_yellow.items():
out_data.append({
"date": key,
"type": 2,
"count": val
})
for key, val in mid_data_green.items():
out_data.append({
"date": key,
"type": 1,
"count": val
})
return return_json(out_data)
def get_avg_amount(self):
c1 = func.sum(DBEntry.amount)
c2 = func.sum(DBProjectUser.balancing)
sum_balancing_q = database.session.query(c2)\
.filter(DBProjectUser.project_id == self.project_id)
avg_q = database.session.query(c1.op("/")(sum_balancing_q.subquery()))\
.filter(DBEntry.project_id == self.project_id)
return avg_q.scalar()
def analytics_time_spent_to_music():
return dict(users=[
(db.session.query(User).get(user_id), timedelta_in_words(int(time_spent), 2))
for user_id, time_spent in db.session.query(Scrobble.user_id, func.sum(ApproximateTrackLength.length)).\
outerjoin((ApproximateTrackLength, Scrobble.approximate_track_length)).\
group_by(Scrobble.user_id).\
order_by(-func.sum(ApproximateTrackLength.length))
])
def __call__(self, column_clause, cuboid=None):
if cuboid and cuboid.fact_count_column is not None:
count = func.sum(cuboid.fact_count_column)
return case([(count == 0, 0)], else_=(
func.sum(column_clause * cuboid.fact_count_column) /
cast(count,
types.Numeric)))
return func.avg(column_clause)
def graphs_stats():
P=models.Projects.query.all()
q_sum = (db.session.query(
Projects.id.label("project_id"),
func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"),
func.count(Tasks.id).label("total"),
).outerjoin(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id))
return render_template("graph_stats.html", P=P,q_sum=q_sum,zipit=zip(P,q_sum))
def get_billing_data_per_year(year, output_type):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Usage.usage_date), func.sum(Usage.cost), Usage.usage_value,
Usage.measurement_unit). \
filter(func.extract('year', Usage.usage_date) == year).group_by(func.unix_timestamp(Usage.usage_date))
else:
billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year).group_by(func.extract(output_type, Usage.usage_date))
return billing_data
def balance(self): #empty tables return None instead of 0. So replace Nones with 0s money_in=int(self.session.query(func.sum(db.Pay.amount)).filter(db.Pay.user==self._user).scalar() or 0) money_out=int(self.session.query(func.sum(db.Sale.amount)).filter(db.Sale.user==self._user).scalar() or 0) transfers_in=int(self.session.query(func.sum(db.Transfer.amount)).filter(db.Transfer.recipient==self._user).scalar() or 0) transfers_out=int(self.session.query(func.sum(db.Transfer.amount)).filter(db.Transfer.sender==self._user).scalar() or 0) external_in=int(self.session.query(func.sum(db.PayExternal.amount)).filter(db.PayExternal.user==self._user).scalar() or 0) res=money_in-money_out+transfers_in-transfers_out+external_in return res
def get_billing_data_per_year_month(year, value_to_match, output_type):
if year == value_to_match:
billing_data = db_session.query(Usage.project_id, func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year).group_by(Usage.project_id)
else:
billing_data = db_session.query(Usage.project_id, func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year,
func.extract(output_type, Usage.usage_date) == value_to_match).group_by(Usage.project_id)
return billing_data
def fetch_dates_counts(word, exact_search, request):
'''
vrati pole poli typu [date, count]
'''
if exact_search:
rows = request.db_session.query(Occurence.date, func.sum(Occurence.count).label("count")).\
join(Word).filter(Word.text.like(word)).group_by(Occurence.date).order_by(Occurence.date).all()
else:
word = strip_accents(word.lower())
rows = request.db_session.query(Occurence.date, func.sum(Occurence.count).label("count")).\
join(Word).filter(Word.base_word.like(word+'%')).group_by(Occurence.date).having(Occurence.date > DATE).order_by(Occurence.date).all()
return rows
def get_total_row(self):
filters = self._filter_filters(self.helper.sql_alchemy_filters)
filter_values = self.helper.sql_alchemy_filter_values
query = (
self.helper.adapter.session_helper.Session.query(
func.sum(self.table.c.count),
func.sum(self.table.c.visit_on_time),
)
.filter(*filters)
.params(filter_values)
)
return ["Total"] + [r for r in query.first()]
def get_billing_data_per_year_per_center(year, project_ids, output_type):
if output_type == 'week':
billing_data = db_session.query(Usage.project_id, func.extract(output_type, Usage.usage_date),
func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids)). \
group_by(func.extract(output_type, Usage.usage_date))
else:
billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids)). \
group_by(func.extract(output_type, Usage.usage_date))
return billing_data
def _instance_data_get_for_user(context, project_id, user_id):
result = model_query(context, models.Instance, (
func.count(models.Instance.id),
func.sum(models.Instance.vcpus),
func.sum(models.Instance.memory_mb))).\
filter_by(project_id=project_id)
if user_id:
result = result.filter_by(user_id=user_id).first()
else:
result = result.first()
# NOTE(vish): convert None to 0
return (result[0] or 0, result[1] or 0, result[2] or 0)
def analytics_track_length_artist_top():
user = db.session.query(User).get(int(request.args.get("user")))
return dict(user=user, top=[
(artist, timedelta_in_words(int(length), 2))
for artist, length in db.session.query(Scrobble.artist, func.sum(ApproximateTrackLength.length)).\
outerjoin((ApproximateTrackLength, Scrobble.approximate_track_length)).\
filter(Scrobble.user == user, ApproximateTrackLength.track != None).\
group_by(Scrobble.artist).\
order_by(-func.sum(ApproximateTrackLength.length))
[:1000]
])
def test_recursive_union_alias_two(self):
# I know, this is the PG VALUES keyword,
# we're cheating here. also yes we need the SELECT,
# sorry PG.
t = select(func.values(1).label("n")).cte("t", recursive=True)
t = t.union_all(select(t.c.n + 1).where(t.c.n < 100)).alias("ta")
s = select(func.sum(t.c.n))
self.assert_compile(
s,
"WITH RECURSIVE t(n) AS "
"(SELECT values(:values_1) AS n "
"UNION ALL SELECT t.n + :n_1 AS anon_1 "
"FROM t "
"WHERE t.n < :n_2) "
"SELECT sum(ta.n) AS sum_1 FROM t AS ta",
)
def _build_R_ui(self):
top_songs_sorted = session.query(TopSong)\
.order_by(TopSong.date.desc(), TopSong.song_id.desc())\
.limit(self.n_top_songs)\
.subquery('top_songs_sorted')
top_songs = session.query(
top_songs_sorted.c.country_id,
top_songs_sorted.c.song_id,
func.sum(top_songs_sorted.c.streams).label('total_streams'))\
.group_by(top_songs_sorted.c.country_id, top_songs_sorted.c.song_id)\
.all()
ratings_matrix = pd.DataFrame(top_songs).pivot(
'country_id', 'song_id', 'total_streams').fillna(0).astype(float)
return np.log(1 + ratings_matrix / self.eps)
def summarise(cls, start_ts=0, end_ts=0):
""""Gets job statistics finished between start_ts and end_ts.
Grouped by host
"""
id_query = Job.id_between(start_ts, end_ts)
query = cls.query.join(Host).\
filter(cls.job_id.in_(id_query)).\
group_by(Host.name).\
with_entities(Host.name,
func.count(cls.job_id),
func.sum(cls.cores))
fields = ['host', 'job_count', 'cores']
return [dict(zip(fields, q)) for q in query.all()]
def workspace_hour_data_active(today=None, session=None):
today = _today(today)
created, session = _session(session)
spec = and_(ActivityLog.date == today, ActivityLog.command != "idle")
cols = ['Workspace', 'Hour', 'Time']
title = "Workspace & Hour - Active"
res = session.query(ActivityLog.workspace,
ActivityLog.hour,
func.sum(ActivityLog.seconds))\
.filter(spec)\
.group_by(ActivityLog.workspace,
ActivityLog.hour)
res = _if_created(created, res, session)
return {"title": title, "cols": cols, "data": res}
def workspace_command_data(today=None, session=None):
today = _today(today)
spec = and_(ActivityLog.date == today)
cols = ['Workspace', 'Command', 'Time']
title = "Workspace & Command"
created, session = _session(session)
res = session.query(ActivityLog.workspace,
ActivityLog.command,
func.sum(ActivityLog.seconds))\
.filter(spec)\
.group_by(ActivityLog.workspace,
ActivityLog.command)
res = _if_created(created, res, session)
return {"title": title, "cols": cols, "data": res}
def show_todo_list():
#form = TodoListForm()
if request.method == 'GET':
#Businesslist = Business.query.filter_by(id=id).first_or_404()
Businesslists = db.session.query(TodoList,TodoList.id,TodoList.user_id,TodoList.title,TodoList.score, Business.savetime, func.sum(Business.number).label('number') )\
.filter_by(user_id=current_user.id)\
.join(Business, TodoList.id == Business.id).filter(Business.savetime < datetime.today(),Business.savetime > (datetime.today() + timedelta(days = -1)))\
.group_by(TodoList.title).all()
BusinessSum = db.session.query(TodoList, TodoList.user_id,func.sum(Business.number).label('sum'))\
.filter_by(user_id=current_user.id)\
.join(Business, TodoList.id == Business.id).filter(Business.savetime < datetime.today(),Business.savetime > (datetime.today() + timedelta(days = -1))).all()
if len(Businesslists) < 1:
flash("没有家务日志,请添加")
return render_template('index.html',
todolists=Businesslists,
todosum=BusinessSum)
def mostPopular():
pID = []
pSrc = []
pName = []
pRecord = sessionDB.query(OrderList.pID, func.sum(OrderList.quantity).label('total'))\
.group_by(OrderList.pID).order_by('total')[:-4:-1]
for p in pRecord:
pID.append(p.pID)
pPicture = sessionDB.query(Product.picture,
Product.p_name).filter(Product.pID.in_(pID))
for p in pPicture:
pSrc.append(p.picture)
pName.append(p.p_name)
return pSrc, pName
def investpercentage():
overview = {}
data = []
items = db.session.query(func.sum(Invest.money), Invest.p2p_id,
P2P.name).filter(Invest.p2p_id == P2P.id,
Invest.status == 0).group_by(
Invest.p2p_id).all()
for i in items:
item = {}
item["sum_money"] = float(i[0])
item["p2p_id"] = int(i[1])
item["p2p_name"] = str(i[2])
data.append(item)
overview["percentage"] = data
return jsonify(overview)
def check_funds_blackjack():
try:
data = json.loads(request.data)
bet_amount = data["amount"]
total_tickets = (db.session.query(func.sum(
Transaction.ticket_amount)).filter(
Transaction.user_id == session["user_id"]).scalar())
if bet_amount > total_tickets:
return {
"success": False,
"message":
"You are wagering more tickets than you currently have",
}
return {"success": True}
except json.decoder.JSONDecodeError:
return {"error": "Malformed request"}, 400
def Create(self, config: corpus_pb2.Corpus):
with self.Session() as session:
if not self.IsDone(session):
self.Import(session, config)
self.SetDone(session)
session.commit()
# Logging output.
num_input_files = session.query(PreprocessedContentFile).count()
num_files = (
session.query(PreprocessedContentFile)
.filter(PreprocessedContentFile.preprocessing_succeeded == True)
.count()
)
input_chars, input_lines, total_walltime, total_time, = session.query(
func.sum(PreprocessedContentFile.charcount),
func.sum(PreprocessedContentFile.linecount),
func.sum(PreprocessedContentFile.wall_time_ms),
func.sum(PreprocessedContentFile.preprocess_time_ms),
).first()
char_count, line_count = (
session.query(
func.sum(PreprocessedContentFile.charcount),
func.sum(PreprocessedContentFile.linecount),
)
.filter(PreprocessedContentFile.preprocessing_succeeded == True)
.first()
)
app.Log(
1,
"Content files: %s chars, %s lines, %s files.",
humanize.Commas(input_chars),
humanize.Commas(input_lines),
humanize.Commas(num_input_files),
)
app.Log(
1,
"Pre-processed %s files in %s (%.2fx speedup).",
humanize.Commas(num_input_files),
humanize.Duration((total_walltime or 0) / 1000),
(total_time or 1) / (total_walltime or 1),
)
app.Log(
1,
"Pre-processing discard rate: %.1f%% (%s files).",
(1 - (num_files / max(num_input_files, 1))) * 100,
humanize.Commas(num_input_files - num_files),
)
app.Log(
1,
"Pre-processed corpus: %s chars, %s lines, %s files.",
humanize.Commas(char_count),
humanize.Commas(line_count),
humanize.Commas(num_files),
)
def summary():
end_sum = db.session.query(trans.act_group.label("act_group"),func.sum(trans.amount).label('total')).group_by(trans.act_group).order_by(trans.act_int)
result = convert_q_json(end_sum)
final_totals = {}
final_totals['bounds'] = bounds
#final_totals['totals'] = result
response = app.response_class(
response=json.dumps(final_totals),
status=200,
mimetype='application/json'
)
print(response)
return response
def Global_sales_year_data():
results = db.session.query(video_game.Year,
func.sum(video_game.Global_Sales)).group_by(
video_game.Year).all()
video_game_year = [result[0] for result in results]
video_game_global_sales_year = [result[1] for result in results]
trace = {
"x": video_game_year,
"y": video_game_global_sales_year,
"type": "bar",
"marker": {
"color": "black"
}
}
return jsonify(trace)
def total_received_incl_pending_wei(self):
"""
The total received by an account, counting transfers that are either pending or complete locally
"""
amount = (
db.session.query(
func.sum(server.models.credit_transfer.CreditTransfer._transfer_amount_wei).label('total')
)
.execution_options(show_all=True)
.filter(server.models.credit_transfer.CreditTransfer.recipient_transfer_account_id == self.id)
.filter(or_(
server.models.credit_transfer.CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE,
server.models.credit_transfer.CreditTransfer.transfer_status == TransferStatusEnum.PENDING))
.first().total
)
return amount or 0
def get_cluster_stats(self, context, project_id=None):
query = model_query(models.Cluster)
node_count_col = models.Cluster.node_count
master_count_col = models.Cluster.master_count
ncfunc = func.sum(node_count_col + master_count_col)
if project_id:
query = query.filter_by(project_id=project_id)
nquery = query.session.query(ncfunc.label("nodes")).filter_by(
project_id=project_id)
else:
nquery = query.session.query(ncfunc.label("nodes"))
clusters = query.count()
nodes = int(nquery.one()[0]) if nquery.one()[0] else 0
return clusters, nodes
def get(self, year):
notas = \
Despesas.query.with_entities(
Endereco.regiaoIes,
func.sum(Rubrica.valor_pago_reais).label('despesa')
).filter(
Despesas.id == Rubrica.id_despesa,
Despesas.ano_mes_lancamento.like(f"{year}%"),
Endereco.cod_ies == Despesas.idIes
).group_by(
Endereco.regiaoIes
).all()
serialized_schema = RegionsWithExpenses(many=True)
output = serialized_schema.dump(notas).data
return output
def _get_patients(payment_min, payment_max):
stmt = db.session.query(Payment.patient_id,
func.sum(Payment.amount).label('total_amount'))
if payment_min:
stmt = stmt.filter(Payment.amount >= payment_min)
if payment_max:
stmt = stmt.filter(Payment.amount <= payment_max)
stmt = stmt.group_by(Payment.patient_id).subquery()
query = db.session.query(Patient, func.ifnull(stmt.c.total_amount, 0.0)). \
outerjoin(stmt, Patient.id == stmt.c.patient_id)
patients_tuple = query.all()
patients_list = map(_sql_tuple_to_patients, patients_tuple)
patients_json = getPatientsSchema.dump(patients_list)
return patients_json
def _record_test_rerun_counts(self, test_list):
job = self.step.job
rerun_count = db.session.query(func.count(TestCase.id)).filter(
TestCase.job_id == job.id,
TestCase.reruns > 0,
).as_scalar()
create_or_update(ItemStat,
where={
'item_id': self.step.id,
'name': 'test_rerun_count',
},
values={
'value': sum(1 for t in test_list if t.reruns),
})
create_or_update(ItemStat,
where={
'item_id': job.id,
'name': 'test_rerun_count',
},
values={
'value': rerun_count,
})
instance = try_create(ItemStat,
where={
'item_id': job.build_id,
'name': 'test_rerun_count',
},
defaults={'value': rerun_count})
if not instance:
ItemStat.query.filter(
ItemStat.item_id == job.build_id,
ItemStat.name == 'test_rerun_count',
).update(
{
'value':
select([func.sum(ItemStat.value)]).where(
and_(
ItemStat.name == 'test_rerun_count',
ItemStat.item_id.in_(
select([Job.id]).where(
Job.build_id == job.build_id, )))),
},
synchronize_session=False)
def monthly_breakdown(today=None, session=None):
created, session = _session(session)
_really_today = _today()
today = _today(today)
link_format = "<div class='{css_class}'><a href='/{date}/'>{day}<br>{day_data}</a></div>"
title = "Month Activity - %s" % today.strftime("%b %Y")
cols = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']
rows = []
row = []
cal = calendar.Calendar(6)
cnt = 0
for t in cal.itermonthdates(today.year, today.month):
spec = and_(ActivityLog.date == t, ActivityLog.command != 'idle')
res = session.query(ActivityLog.date,
ActivityLog.workspace,
func.sum(ActivityLog.seconds))\
.filter(spec)\
.group_by(ActivityLog.workspace)\
.order_by(ActivityLog.workspace)
if cnt % 7 == 0 and len(row) != 0:
rows.append(row)
row = []
day_data = []
print("RES:", res)
for r in res:
if r[2] > 0:
contents = "{workspace} <span class='pull-right'>{hh_mm_ss}</span>".format(
workspace=r[1], hh_mm_ss=hh_mm_ss(r[2]))
day_data.append(contents)
css_class = ["day"]
if t == _really_today:
css_class.append('really_today')
if t == today:
css_class.append("today")
link = link_format.format(date="%s" % t,
day="%s" % t.day,
day_data="%s" % "<br>".join(day_data),
css_class=" ".join(css_class))
row.append(link)
cnt += 1
rows.append(row)
ssr(created, session)
return {"title": title, "cols": cols, "data": rows}
def get_amount_pledged_datapoints(self):
pledges_per_day = db.session.query(func.date(
Pledge.time_created), func.sum(Pledge.amount)).filter(
Project.id == self.id,
Project.id == Pledge.project_id).group_by(
func.date(Pledge.time_created)).all()
datapoints = [[i + 1, 0] for i in range(self.duration + 2)
] # [(0,0), (1,0), ..., (30,0)]
for p in pledges_per_day:
time_pledged = datetime.datetime.strptime(p[0], "%Y-%m-%d")
day_num = (time_pledged.date() - self.time_start.date()).days
amount = p[1]
datapoints[day_num] = [day_num, amount]
return datapoints
def get_squad(self, club_id):
squad = []
s = self.session()
players = s.query(Player).filter(Player.appearances.any(team_id=club_id)).all()
for player in players:
goals = s.query(Event).filter(Event.player_id == player.id,
Event.type == 'i-report-ball').count()
yellow_cards = s.query(Event).filter(Event.player_id == player.id,
Event.type == 'i-yellow-card').count()
red_cards = s.query(Event).filter(Event.player_id == player.id,
Event.type == 'i-red-card').count()
last_number = s.query(Squad.number).filter(Squad.player_id == player.id).limit(1).first()[0]
numbers = s.query(Squad.number, func.count(Squad.number)).filter(Squad.player_id == player.id).group_by(
Squad.number).all()
minutes_played = s.query(func.sum(Squad.time_played)).filter(Squad.player_id == player.id).first()[0]
squad.append((player.name, minutes_played, numbers, last_number, goals, yellow_cards, red_cards))
return squad
def get_profile_view(user_id):
if user_id is None and "user_id" in session:
user_id = session["user_id"]
try:
user_profile = get_user_profile(user_id)
total_tickets = (db.session.query(func.sum(
Transaction.ticket_amount)).filter(
Transaction.user_id == user_id).scalar())
return {
"name": user_profile.name,
"username": user_profile.username,
"registration_datetime": user_profile.registration_datetime,
"total_tickets": total_tickets,
}
except NoResultFound:
return {"error": "Result not found"}, 404
def get_current_month_put_amount(user_id=None):
"""
获取当月投资总额
:return:
"""
start_time, end_time = get_current_month_time_ends()
condition = [
ApplyPut.create_time >= start_time,
ApplyPut.create_time <= end_time
]
if user_id:
condition.append(ApplyPut.user_id == user_id)
res = db.session \
.query(func.sum(ApplyPut.money_apply).label('amount')) \
.filter(*condition) \
.first()
return res.amount or 0
def posterpoints_badge(pid):
post = Post.query.filter_by(post_id=pid).first()
points = db.session.query(
Userpoints.user_id, func.sum(Pointrules.add_points)).outerjoin(
Pointrules, Userpoints.points_id == Pointrules.point_id).group_by(
Userpoints.user_id).filter(
Userpoints.user_id == post.user_id).all()
#print(points)
if points:
if points[0][1] >= 100:
#print(points[0][1])
pointbadgefirst = Userbadges.query.filter(
Userbadges.badge_id == 17,
Userbadges.user_id == points[0][0]).all()
if not pointbadgefirst:
pointbadge = Userbadges(badge_id=17, user_id=points[0][0])
db.session.add(pointbadge)
db.session.commit()
if points[0][1] >= 390:
#print(points[0][1])
pointbadgefirst = Userbadges.query.filter(
Userbadges.badge_id == 18,
Userbadges.user_id == points[0][0]).all()
if not pointbadgefirst:
pointbadge = Userbadges(badge_id=18, user_id=points[0][0])
db.session.add(pointbadge)
db.session.commit()
if points[0][1] >= 930:
#print(points[0][1])
pointbadgefirst = Userbadges.query.filter(
Userbadges.badge_id == 19,
Userbadges.user_id == points[0][0]).all()
if not pointbadgefirst:
pointbadge = Userbadges(badge_id=19, user_id=points[0][0])
db.session.add(pointbadge)
db.session.commit()
if points[0][1] >= 1390:
#print(points[0][1])
pointbadgefirst = Userbadges.query.filter(
Userbadges.badge_id == 20,
Userbadges.user_id == points[0][0]).all()
if not pointbadgefirst:
pointbadge = Userbadges(badge_id=20, user_id=points[0][0])
db.session.add(pointbadge)
db.session.commit()
return post, points
def get_aggregated_stats(environment_id):
unit = db_session.get_session()
now = timeutils.utcnow_ts()
query = unit.query(
models.Instance.instance_type,
func.sum(
func.coalesce(models.Instance.destroyed, now) -
models.Instance.created), func.count()).filter(
models.Instance.environment_id == environment_id)
res = query.group_by(models.Instance.instance_type).all()
return [{
'type': int(record[0]),
'duration': int(record[1]),
'count': int(record[2])
} for record in res]
def Stats(self, request, context):
with session_scope() as session:
date_ = func.date_trunc("day", Report.time_received)
stats = (session.query(date_, func.sum(StrandInReport.state)).join(
StrandInReport,
StrandInReport.report_id == Report.report_id).filter(
StrandInReport.strand_id == request.strand_id).group_by(
date_).all())
return sb_pb2.StatsRes(
strand_id=1,
times=[timestamp_from_datetime(datetime.datetime.now())],
total_incubating_strands=[6],
total_infected_strands=[3],
total_removed_strands=[2],
)
def resolve_token_votes(self, args, context, info):
votes = []
for token_category in self.get_token_categories():
query = self.db.query(
func.sum(getattr(self.get_vote_class(),
"vote_value"))).filter_by(
vote_spec_id=self.id,
tombstone_date=None,
token_category_id=token_category.id)
# when there is no votes, query.first() equals (None,)
# in this case set num_token to 0
num_token = query.first()[0] or 0
votes.append(
VotesByCategory(token_category_id=token_category.graphene_id(),
num_token=num_token))
return votes
def index():
if current_user.role != 1:
return redirect(url_for('base'))
if request.method == 'POST':
data = my_json.loads(request.form['data'])
airline = data['airline']
aircraft = data['aircraft']
services = list(data['services'])
try:
act = LandingAct(ID_AIRLINE=airline,
ID_DISPATCHER=current_user.get_id(),
ID_AIRCRAFT=aircraft,
DATE=date.today())
db.session.add(act)
db.session.commit()
for i in range(len(services)):
list_service = ListService(ID_ACT=act.ID_ACT,
ID_SERVICE=services[i])
db.session.add(list_service)
db.session.commit()
cost = db.session.query(func.sum(Tariff.COST).label('total')).join(
ListService,
ListService.ID_SERVICE == Tariff.ID_SERVICE).filter(
Tariff.ID_AIRCRAFT == aircraft,
ListService.ID_ACT == act.ID_ACT).first()
if cost.total is None:
db.session.add(Score(SCORE=0, ID_ACT=act.ID_ACT))
else:
db.session.add(Score(SCORE=cost.total, ID_ACT=act.ID_ACT))
except Exception as e:
print()
print(e)
db.session.rollback()
return " ", 202
else:
db.session.commit()
return " ", 200
airlines = db.session.query(Airline).all()
aircrafts = db.session.query(Aircraft).all()
tex = db.session.query(TechService).all()
earth = db.session.query(EarthService).all()
return render_template('add.html',
airline=airlines,
aircraft=aircrafts,
tex_service=tex,
earth_service=earth)
def index(self):
lst = []
SumDoanhSoTheoNgay = db.session.query(
func.sum(Bill.total_price).label('sum'), Bill.order_time).filter(
func.month(Bill.order_time) ==
datetime.datetime.today().month).group_by(
func.date(Bill.order_time)).all()
# #co check status
# SumDoanhSoTheoNgay = db.session.query(func.sum(Bill.total_price).label('sum'), Bill.order_time).filter(
# Bill.status == 4).filter(func.month(Bill.order_time) == datetime.datetime.today().month).group_by(
# func.date(Bill.order_time)).all()
for i in SumDoanhSoTheoNgay:
lst.append([i[1].day, int(i[0])])
lst.sort(key=myFunc)
return self.render('admin/analytics.html', lst=lst)
def size_report(self):
from ckan import model
from ckanext.archiver.model import Archival
kb = 1024
mb = 1024*1024
gb = pow(1024, 3)
size_bins = [
(kb, '<1 KB'), (10*kb, '1-10 KB'), (100*kb, '10-100 KB'),
(mb, '100 KB - 1 MB'), (10*mb, '1-10 MB'), (100*mb, '10-100 MB'),
(gb, '100 MB - 1 GB'), (10*gb, '1-10 GB'), (100*gb, '10-100 GB'),
(gb*gb, '>100 GB'),
]
previous_bin = (0, '')
counts = []
total_sizes = []
print '{:>15}{:>10}{:>20}'.format(
'file size', 'no. files', 'files size (bytes)')
for size_bin in size_bins:
q = model.Session.query(Archival) \
.filter(Archival.size > previous_bin[0]) \
.filter(Archival.size <= size_bin[0]) \
.filter(Archival.cache_filepath != '') \
.join(model.Resource,
Archival.resource_id == model.Resource.id) \
.filter(model.Resource.state != 'deleted') \
.join(model.Package,
Archival.package_id == model.Package.id) \
.filter(model.Package.state != 'deleted')
count = q.count()
counts.append(count)
total_size = model.Session.query(func.sum(Archival.size)) \
.filter(Archival.size > previous_bin[0]) \
.filter(Archival.size <= size_bin[0]) \
.filter(Archival.cache_filepath != '') \
.join(model.Resource,
Archival.resource_id == model.Resource.id) \
.filter(model.Resource.state != 'deleted') \
.join(model.Package,
Archival.package_id == model.Package.id) \
.filter(model.Package.state != 'deleted') \
.all()[0][0]
total_size = int(total_size or 0)
total_sizes.append(total_size)
print '{:>15}{:>10,}{:>20,}'.format(size_bin[1], count, total_size)
previous_bin = size_bin
print 'Totals: {:,} {:,}'.format(sum(counts), sum(total_sizes))
def index():
if 'username' in session:
name = session['username']
catdata = db.session.query(category_master).filter(
category_master.user_id == session['username']).all()
expdata = db.session.query(
expense_master.cat_id,
func.sum(expense_master.amount).label('total')).filter(
expense_master.user_id == session['username']).group_by(
expense_master.cat_id).all()
return render_template('index.html',
catdata=catdata,
expdata=expdata,
name=session['username'])
else:
error = 'Invalid request. Please enter username and password!'
return render_template('login.html', error=error)
