def month_balance(self, id):
orders = meta.Session.query(func.month(Order.created_at).label('month'),
func.day(Order.created_at).label('day'),
func.sum(Order.real_value - TransportOrder.real_value).label('value')) \
.join(Order.transport_order) \
.group_by( func.month(Order.created_at), func.day(Order.created_at)) \
.filter(and_(Order.idCreator == id, Order.created_at.between(h.today(31), h.today()))) \
.all()
chart = open_flash_chart()
t = title(text='Zestawienie')
b = bar()
b.values = [order.value for order in orders]
lbl = labels(labels = [str(order.month) + ' ' + str(order.day) for order in orders])
x = x_axis()
x.labels = lbl
y = y_axis()
y.min, y.max= 0, max(b.values or [0])
chart.title = t
chart.y_axis = y
chart.x_axis = x
chart.add_element(b)
return chart.render()
def first_heatmap_graph(month="May", year=2020, expence_type="expence"):
if current_user and current_user.is_authenticated:
if expence_type == "expence":
b = db.session.query(func.day(Expences.date_time),
func.sum(Expences.debit).label('total'),
).group_by(func.date(Expences.date_time)) \
.filter(func.month(Expences.date_time) == datetime.strptime(month, '%B').month) \
.filter(func.year(Expences.date_time) == int(year)) \
.filter(Expences.user_id == current_user.id)
else:
b = db.session.query(func.day(Expences.date_time),
func.sum(Expences.credit).label('total'),
).group_by(func.date(Expences.date_time)) \
.filter(func.month(Expences.date_time) == datetime.strptime(month, '%B').month) \
.filter(func.year(Expences.date_time) == int(year)) \
.filter(Expences.user_id == current_user.id)
data_date = [0 for _ in range(1, 33)]
for i in b:
data_date[i[0]] = i[1]
act_data = []
for i in calendar.Calendar().monthdayscalendar(month=datetime.strptime(
month, '%B').month,
year=2020):
a = []
for j in i:
a.append(data_date[j])
act_data.append(a)
calendar_dates = calendar.Calendar().monthdayscalendar(
month=datetime.strptime(month, '%B').month, year=2020)
for i in range(len(calendar_dates)):
for j in range(len(calendar_dates[i])):
if calendar_dates[i][j] == 0:
calendar_dates[i][j] = ""
fig = ff.create_annotated_heatmap(
[act_data[i] for i in range(len(act_data) - 1, -1, -1)],
x=[
'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
'Saturday', 'Sunday'
],
annotation_text=[
calendar_dates[i]
for i in range(len(calendar_dates) - 1, -1, -1)
],
colorscale='Reds',
)
fig.update_layout(
title="Per Day for the month of " + month,
xaxis_title="Day of the week",
yaxis_title="Week",
)
return fig
else:
return go.Figure(
data=[go.Bar(x=[1, 2], y=[1, 2])],
layout=go.Layout(title="Please login To view Your Data"))
def first_bar_graph(month="May", year=2020, expence_type="expence"):
if current_user and current_user.is_authenticated:
if expence_type == "expence":
b = db.session.query(func.day(Expences.date_time),
func.sum(Expences.debit).label('total'),
).group_by(func.date(Expences.date_time)) \
.filter(func.month(Expences.date_time) == datetime.strptime(month, '%B').month) \
.filter(func.year(Expences.date_time) == int(year)) \
.filter(Expences.user_id == current_user.id)
else:
b = db.session.query(func.day(Expences.date_time),
func.sum(Expences.credit).label('total'),
).group_by(func.date(Expences.date_time)) \
.filter(func.month(Expences.date_time) == datetime.strptime(month, '%B').month) \
.filter(func.year(Expences.date_time) == int(year)) \
.filter(Expences.user_id == current_user.id)
data_date = [0 for _ in range(32)]
date_31 = [i for i in range(1, 32)]
for i in b:
data_date[i[0]] = i[1]
data_date_1 = [
0,
]
for i in data_date:
data_date_1.append(data_date_1[-1] + i)
data_fig1 = [
go.Bar(x=date_31,
y=data_date_1[2:],
name="Per Day Cumulative",
visible='legendonly'),
go.Bar(x=date_31, y=data_date[1:], name="Per Day", text="pop")
]
layout_fig1 = go.Layout(
legend_title_text='Click To Disable or Enable..',
legend=dict(x=0.6, y=1.25),
title="Per Day for the month of " + month,
barmode='overlay',
xaxis_title="Date of the month",
yaxis_title="Amount",
margin=dict(l=5, r=10),
)
return go.Figure(data=data_fig1, layout=layout_fig1)
else:
return go.Figure(
data=[go.Bar(x=[1, 2], y=[1, 2])],
layout=go.Layout(title="Please login To view Your Data"))
def get(self):
datas = []
dates = db.session.query(
func.count(Comment.mid), func.year(Comment.insert_time),
func.month(Comment.insert_time),
func.day(Comment.insert_time)).group_by(
func.year(Comment.insert_time),
func.month(Comment.insert_time),
func.day(Comment.insert_time)).filter(
Comment.insert_time >= (datetime.datetime.now() -
datetime.timedelta(30))).all()
for date in dates:
datas.append({
"numbers": date[0],
"date": f'{date[1]}-{date[2]}-{date[3]}'
})
return datas
def get_statistics_by_day(db: Session, date):
return (
db.query(
func.sum(models.HourlyStats.request_count).label("request_count")
+ func.sum(models.HourlyStats.invalid_count).label("invalid_count")
)
.filter(
func.year(models.HourlyStats.time) == date.year,
func.month(models.HourlyStats.time) == date.month,
func.day(models.HourlyStats.time) == date.day,
)
.group_by(
func.year(models.HourlyStats.time),
func.month(models.HourlyStats.time),
func.day(models.HourlyStats.time),
)
.all()
)
def myslots():
templist = db.session.query(
Dates.date, func.year(Dates.date), func.month(Dates.date),
func.day(Dates.date), Classes.starttime, Classes.endtime,
Classes.content, Classes.id, Courses.code, Courses.id).distinct().join(
Dates.classes).join(Classes.teachers).join(Classes.courses).filter(
Teachers.email == session['user']).order_by(
Dates.date).order_by(Classes.starttime).all()
return templist
def apply_get_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(ApplyGet.create_time).label('hour'), func.sum(ApplyGet.money_apply)) \
.filter(ApplyGet.create_time >= time_local_to_utc(start_time),
ApplyGet.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(ApplyGet.create_time).label('date'), func.sum(ApplyGet.money_apply)) \
.filter(ApplyGet.create_time >= time_local_to_utc(start_time),
ApplyGet.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(ApplyGet.create_time).label('month'), func.sum(ApplyGet.money_apply)) \
.filter(ApplyGet.create_time >= time_local_to_utc(start_time),
ApplyGet.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 apply_timeseries(self, query, column=Revision.rev_timestamp):
"""
Take a query and slice it up into equal time intervals
Parameters
query : a sql alchemy query
column : defaults to Revision.rev_timestamp, specifies the timestamp
column to use for the timeseries
Returns
The query parameter passed in, with a grouping by the desired time slice
"""
choice = self.timeseries.data
if choice == TimeseriesChoices.NONE:
return query
query = query.add_column(func.year(column))
query = query.group_by(func.year(column))
if choice == TimeseriesChoices.YEAR:
return query
query = query.add_column(func.month(column))
query = query.group_by(func.month(column))
if choice == TimeseriesChoices.MONTH:
return query
query = query.add_column(func.day(column))
query = query.group_by(func.day(column))
if choice == TimeseriesChoices.DAY:
return query
query = query.add_column(func.hour(column))
query = query.group_by(func.hour(column))
if choice == TimeseriesChoices.HOUR:
return query
def apply_timeseries(self, query, rev=Revision):
"""
Take a query and slice it up into equal time intervals
Parameters
query : a sql alchemy query
rev : defaults to Revision, specifies the object that
contains the appropriate rev_timestamp
Returns
The query parameter passed in, with a grouping by the desired time slice
"""
choice = self.timeseries.data
if choice == TimeseriesChoices.NONE:
return query
query = query.add_column(func.year(rev.rev_timestamp))
query = query.group_by(func.year(rev.rev_timestamp))
if choice == TimeseriesChoices.YEAR:
return query
query = query.add_column(func.month(rev.rev_timestamp))
query = query.group_by(func.month(rev.rev_timestamp))
if choice == TimeseriesChoices.MONTH:
return query
query = query.add_column(func.day(rev.rev_timestamp))
query = query.group_by(func.day(rev.rev_timestamp))
if choice == TimeseriesChoices.DAY:
return query
query = query.add_column(func.hour(rev.rev_timestamp))
query = query.group_by(func.hour(rev.rev_timestamp))
if choice == TimeseriesChoices.HOUR:
return query
def count_by_8_days(self, user_id, form_id):
c = self.db.session\
.query(
func.count(Value.id).label("count"),
func.year(Value.created_at).label("year"),
func.month(Value.created_at).label("month"),
func.day(Value.created_at).label("day"),
)\
.filter(
Value.user_id == user_id,
Value.form_id == form_id,
Value.created_at >= timedelta(days=8)
)\
.order_by(func.max(Value.created_at).desc())\
.group_by(
func.year(Value.created_at),
func.month(Value.created_at),
func.day(Value.created_at)
)\
.all()
return [c._asdict() for c in c]
def scheduleInCourse(course):
# Lista med kurstillfallen som anvands i en kurs
templist = []
tempvar = db.session.query(
Dates.date, func.year(Dates.date), func.month(Dates.date),
func.day(Dates.date), Classes.starttime, Classes.endtime,
Classes.content, Classes.id).distinct().join(Dates.classes).join(
Classes.courses).filter(Courses.id == course).order_by(
Dates.date).order_by(Classes.starttime).all()
for item in tempvar:
print "x"
templist.append(item)
return templist
def expenditure_income_line_graph(month="May", year=2020):
if current_user and current_user.is_authenticated:
b = db.session.query(func.day(Expences.date_time),
func.sum(Expences.debit).label('total'),
func.sum(Expences.credit).label('total')
).group_by(func.date(Expences.date_time)) \
.filter(func.month(Expences.date_time) == datetime.strptime(month, '%B').month) \
.filter(func.year(Expences.date_time) == int(year)) \
.filter(Expences.user_id == current_user.id)
data_vs_income = [0 for _ in range(32)]
data_vs_expence = [0 for _ in range(32)]
date_31 = [i for i in range(1, 32)]
for i in b:
data_vs_income[i[0]] = i[1]
data_vs_expence[i[0]] = i[2]
data_vs_income_commutative = [
0,
]
data_vs_expence_commutative = [
0,
]
for i in data_vs_income:
data_vs_income_commutative.append(data_vs_income_commutative[-1] +
i)
for i in data_vs_expence:
data_vs_expence_commutative.append(
data_vs_expence_commutative[-1] + i)
data_fig1 = [
go.Scatter(x=date_31,
y=data_vs_income_commutative[2:],
name="Data vs Income Commutative",
mode="markers+lines"),
go.Scatter(x=date_31,
y=data_vs_expence_commutative[2:],
name="Data vs Expense Commutative",
mode="markers+lines"),
]
layout_fig1 = go.Layout(
title="Income Expense Comparison <br> For the month of " + month,
barmode='overlay',
xaxis_title="Date of the month",
yaxis_title="Commutative Amount",
legend=dict(x=0.6, y=1.15),
margin=dict(l=20, r=25),
)
return go.Figure(data=data_fig1, layout=layout_fig1)
else:
return go.Figure(
data=[go.Pie(labels=[1], values=["None"])],
layout=go.Layout(title="Please Login To View Your Data!"))
def count_by_8_days(self, form_id, type) -> int:
c = self.db.session\
.query(
func.count(Event.created_at).label("count"),
func.year(Event.created_at).label("year"),
func.month(Event.created_at).label("month"),
func.day(Event.created_at).label("day"),
)\
.filter(
Event.form_id == form_id,
Event.created_at >= timedelta(days=8)
)\
.order_by(func.max(Event.created_at).desc())\
.group_by(
func.year(Event.created_at),
func.month(Event.created_at),
func.day(Event.created_at)
)\
.all()
logging.debug("count_by_8_days")
return [c._asdict() for c in c]
def group_query_by_interval(self, table):
""" Return original query plus grouping for interval. """
if 'year' in self.args['interval']:
self.base_query = self.base_query.\
group_by(func.year(table.date))
self.base_query = self.base_query.order_by(func.year(table.date))
elif 'month' in self.args['interval']:
self.base_query = self.base_query.\
group_by(func.year(table.date),
func.month(table.date))
self.base_query = self.base_query.order_by(func.year(table.date),
func.month(table.date))
elif 'day' in self.args['interval']:
self.base_query = self.base_query.\
group_by(func.year(table.date),
func.month(table.date),
func.day(table.date))
self.base_query = self.base_query.order_by(func.year(table.date),
func.month(table.date),
func.day(table.date))
elif 'hour' in self.args['interval']:
self.base_query = self.base_query.\
group_by(func.year(table.date),
func.month(table.date),
func.day(table.date),
func.hour(table.date))
self.base_query = self.base_query.order_by(func.year(table.date),
func.month(table.date),
func.day(table.date),
func.hour(table.date))
return self.base_query
def get(self, store_id):
self.parser.add_argument('start_at', type=lib.arrow_datetime, help="开始时间必填", required=True, location="args")
self.parser.add_argument('end_at', type=lib.arrow_datetime, help="结束时间", location="args")
self.parser.add_argument('model', type=unicode, location="args", default="month")
self.parser.add_argument('company_id', type=int, help="公司 ID", location="args")
args = self.parser.parse_args()
if args.model == 'day':
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at, '%Y年%m月%d日').label('date'),
)
elif args.model == 'week':
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at, '%Y年第%v周').label('date'),
)
else:
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at, '%Y年%m月').label('date'),
)
if not args.end_at:
args.end_at = tool.now(False)
order_query = order_query.filter(
models.Order.created_at >= args.start_at.naive,
models.Order.created_at <= args.end_at.naive,
)
if args.company_id:
order_query = order_query.filter(
models.Order.company_id == args.company_id,
)
if args.model == "day":
order_query = order_query.group_by(func.day(models.Order.created_at))
elif args.model == "week":
order_query = order_query.group_by(func.week(models.Order.created_at))
else:
order_query = order_query.group_by(func.month(models.Order.created_at))
xAxis = list()
series_data = list()
for i in order_query:
xAxis.append(i.date)
series_data.append(i.total)
return tool.success({
'xAxis': xAxis,
'series_data': series_data,
})
def api_watt_month_view(request):
start = datetime.datetime.now() - datetime.timedelta(30)
query = DBSession.query(
func.sum(WattLog.value).label("value"),
func.count(WattLog.value).label("count"),
)
query = query.filter(WattLog.created_at > start)
query = query.group_by(
func.year(WattLog.created_at),
func.month(WattLog.created_at),
func.day(WattLog.created_at),
func.hour(WattLog.created_at),
)
ret = 0
for d in query.all():
avg = d.value / d.count
ret += avg
return {
"watt": ret,
}
def __query_joined_members(duration: timedelta):
"""
"""
since: datetime = datetime.datetime.utcnow() - duration
joinedSubquery = db.session.query(
func.year(HistoryEntry.time).label('year'),
func.month(HistoryEntry.time).label('month'),
func.day(HistoryEntry.time).label('day'),
HistoryEntry.targetID.label('target'),
)\
.filter(
and_(
HistoryEntry.action == HistoryEntry.EVENT_AUTO_RM_PL,
HistoryEntry.time >= since
)
).distinct().subquery("joinedFleet")
return db.session.query(joinedSubquery.c.year, joinedSubquery.c.month,
func.count(joinedSubquery.c.target)).group_by(
joinedSubquery.c.year,
joinedSubquery.c.month).all()
def main(argv):
print(Supportparams.exists('/home/clinical/DEMUX//150703_D00134_0206_AH5HGFBCXX/Unaligned4/support.txt')) # 515
print(Datasource.exists('/home/clinical/DEMUX//150703_D00134_0206_AH5HGFBCXX/Unaligned4/Basecall_Stats_H5HGFBCXX/Demultiplex_Stats.htm')) #515
print(Flowcell.exists('H5HGFBCXX')) # 512
print(Demux.exists(512, 'Y101,I8,I8,Y101')) # 474
print(Project.exists('240540')) #552
print(Sample.exists('ADM1136A1_XTA08', 'CAGCGTTA')) #6651
print(Unaligned.exists(18, 487, 1)) #13902
print(xstats.parse('/mnt/hds/proj/bioinfo/DEMUX/151009_ST-E00198_0059_BH2V2YCCXX'))
print(Backup.exists('151117_D00410_0187_AHWYGMADXX'))
print(Backup.exists('141212_D00134_0166_AHB058ADXX'))
print(Backup.exists('131219_D00134_0057_BH829YADXX'))
print(Backup.exists('131219_D00134_0057_BH829YADXX', 'tape005_006'))
print(Backup.exists('131219_D00134_0057_BH829YADXX', 'tape007_005'))
print(Backuptape.exists('tape005_006'))
rs = SQL.query(
func.year(Datasource.rundate).label('year'),\
func.month(Datasource.rundate).label('month'),\
func.count(Datasource.datasource_id.distinct()).label('runs'),\
func.round(func.sum(Unaligned.readcounts / 2000000), 2).label('mil reads'),\
func.round(func.sum(Unaligned.readcounts) / (func.count(Datasource.datasource_id.distinct())*2000000), 1).label('mil reads fc lane')
).\
outerjoin(Demux).\
outerjoin(Flowcell).\
outerjoin(Unaligned).\
group_by(func.year(Datasource.rundate), func.month(Datasource.rundate)).\
order_by(func.year(Datasource.rundate).desc(), func.month(Datasource.rundate).desc(), func.day(Datasource.rundate).desc()).\
all()
print(rs)
def champion_stats(champion, role):
"""Gives detailed champion stats for a particular champion.
Args:
champion: Champion ID for the champion in question.
role: The role that the champion players.
Returns:
json: JSON formatted response for the champion statistics.
"""
champ = (
db.session.query(ChampionData)
.filter_by(champion_id=champion, role=role)
.first()
)
days_ago = datetime.datetime.now() - datetime.timedelta(days=7)
champ_list = (
db.session.query(
Champion.champion_id.label("champion_id"),
func.count(Champion.id).label("num_seen"),
func.avg(Champion.won, type_=Integer).label("won"),
Match.match_id.label("match_id"),
Match.match_time.label("match_time")
)
.filter(Champion.champion_id == champion, Champion.role == role)
.join(Champion.match)
.filter(Match.match_time > days_ago)
.group_by(func.day(Match.match_time))
.order_by(Match.match_time.desc())
.all()
)
stats = {
'champion_info': {
'champion_id': champ.champion_id,
'champion_name': champ.get_name()
},
'counters': compile_sorted_champions(
champ.get_compiled_weights("counters")
),
'assists': compile_sorted_champions(
champ.get_compiled_weights("assists")
),
# TODO(Need to divide this by the number of matches collected.)
# This is for that particular day.
'days_seen': {
'labels': [data.match_time.strftime(
"%b %d (%A)"
) for data in champ_list],
'data': [data.num_seen for data in champ_list]
},
'days_won': {
'labels': [data.match_time.strftime(
"%b %d (%A)"
) for data in champ_list],
'data': [round(data.won, 2) for data in champ_list]
}
}
return jsonify(stats)
def get(self, store_id):
self.parser.add_argument('start_at',
type=lib.arrow_datetime,
help="开始时间必填",
required=True,
location="args")
self.parser.add_argument('end_at',
type=lib.arrow_datetime,
help="结束时间",
location="args")
self.parser.add_argument('model',
type=unicode,
location="args",
default="month")
self.parser.add_argument('company_id',
type=int,
help="公司 ID",
location="args")
args = self.parser.parse_args()
if args.model == 'day':
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at,
'%Y年%m月%d日').label('date'),
)
elif args.model == 'week':
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at,
'%Y年第%v周').label('date'),
)
else:
order_query = models.db.session.query(
func.count(1).label('total'),
func.date_format(models.Order.created_at,
'%Y年%m月').label('date'),
)
if not args.end_at:
args.end_at = tool.now(False)
order_query = order_query.filter(
models.Order.created_at >= args.start_at.naive,
models.Order.created_at <= args.end_at.naive,
)
if args.company_id:
order_query = order_query.filter(
models.Order.company_id == args.company_id, )
if args.model == "day":
order_query = order_query.group_by(
func.day(models.Order.created_at))
elif args.model == "week":
order_query = order_query.group_by(
func.week(models.Order.created_at))
else:
order_query = order_query.group_by(
func.month(models.Order.created_at))
xAxis = list()
series_data = list()
for i in order_query:
xAxis.append(i.date)
series_data.append(i.total)
return tool.success({
'xAxis': xAxis,
'series_data': series_data,
})