def station_graph(request):
session = request.dbsession
result = OrderedDict()
# Calculate the bounds
today = datetime.date.today()
begin_date = datetime.date(day=1, month=today.month, year=today.year - 1)
end_date = datetime.date(day=1, month=today.month, year=today.year)
# Query
query = (
select(
[
func.count(Station.ID).label("nb"),
func.year(Station.StationDate).label("year"),
func.month(Station.StationDate).label("month"),
]
)
.where(and_(Station.StationDate >= begin_date, Station.StationDate < end_date))
.group_by(func.year(Station.StationDate), func.month(Station.StationDate))
)
"""
Execute query and sort result by year, month
(faster than an order_by clause in this case)
"""
data = session.execute(query).fetchall()
for nb, y, m in sorted(data, key=operator.itemgetter(1, 2)):
d = datetime.date(day=1, month=m, year=y).strftime("%b")
result[" ".join([d, str(y)])] = nb
return result
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 individual_graph(request):
session = request.dbsession
# Initialize Json object
result = OrderedDict()
# Calculate the bounds
today = datetime.date.today()
begin_date = datetime.date(day=1, month=today.month, year=today.year - 1)
end_date = datetime.date(day=1, month=today.month, year=today.year)
# Query
query = (
select(
[
func.count("*").label("nb"),
func.year(Individual.creationDate).label("year"),
func.month(Individual.creationDate).label("month"),
]
)
.where(and_(Individual.creationDate >= begin_date, Individual.creationDate < end_date))
.group_by(func.year(Individual.creationDate), func.month(Individual.creationDate))
)
data = session.execute(query).fetchall()
for nb, y, m in sorted(data, key=operator.itemgetter(1, 2)):
d = datetime.date(day=1, month=m, year=y).strftime("%b")
result[" ".join([d, str(y)])] = nb
return result
def by_date():
'''
issue_group_date = Issue.query.add_columns(func.sum(func.IF(Issue.status==1, 1, 0)), # sum/count total if status is Open
# func.sum(func.IF(Issue.status==2, 1, 0)), # sum/count total if status is Close
func.year(Issue.issue_date),
func.month(Issue.issue_date)
).\
group_by(func.year(Issue.issue_date),
func.month(Issue.issue_date)).\
all()
'''
group_date = Issue.query.filter(Issue.status==1)
group_date = group_date.add_columns(func.count(Issue.status), # sum/count total if status is Open
func.year(Issue.issue_date),
func.month(Issue.issue_date)
)
group_date = group_date.group_by(func.year(Issue.issue_date),
func.month(Issue.issue_date))
date_data = []
for element in group_date:
date_data.append(
{
"date": "{}-{}".format(element[-2],element[-1]+1), # + 1 For Months, due to javascript months is 0-11
#"date": dt.date(element[-2],element[-1],1),
"open": int(element[1]),
},
)
return date_data
def station_graph(request):
# Initialize Json object
result = OrderedDict()
# Calculate the bounds
today = datetime.date.today()
begin_date = datetime.date(day=1, month=today.month, year=today.year-1)
end_date = datetime.date(day=1, month=today.month, year=today.year)
# Query
query = select([
func.count(Station.id).label('nb'),
func.year(Station.date).label('year'),
func.month(Station.date).label('month')]
).where(and_(Station.date >= begin_date, Station.date < end_date)
).group_by(func.year(Station.date), func.month(Station.date)
)
"""
Execute query and sort result by year, month
(faster than an order_by clause in this case)
"""
data = DBSession.execute(query).fetchall()
for nb, y, m in sorted(data, key=operator.itemgetter(1,2)):
d = datetime.date(day=1, month=m, year=y).strftime('%b')
result[' '.join([d, str(y)])] = nb
return result
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 get_summary(value_class):
q = db_session.query(
func.date(SR_Values.datetime).label("date")
, func.sum(SR_Values.value).label("daily_value")
).filter(SR_Classes.id == SR_Values.value_class_id
).filter(SR_Classes.accum_flag == true()
).filter(SR_Classes.value_class == value_class
).filter(SR_Values.datetime > datetime.datetime(datetime.datetime.now().year, 1, 1)
).group_by(SR_Classes.value_class, func.month(SR_Values.datetime)
).order_by(SR_Classes.value_class, func.date(SR_Values.datetime))
print q
rows = [{ "name": x.date
, "value": x.daily_value
} for x in q.all()]
q = db_session.query(
func.date(SR_Values.datetime).label("date")
, func.avg(SR_Values.value).label("daily_value")
).filter(SR_Classes.id == SR_Values.value_class_id
).filter(SR_Classes.accum_flag == false()
).filter(SR_Classes.value_class == value_class
).filter(SR_Values.datetime > datetime.datetime(datetime.datetime.now().year, 1, 1)
).group_by(SR_Classes.value_class, func.month(SR_Values.datetime)
).order_by(SR_Classes.value_class, func.date(SR_Values.datetime))
rows.extend([{ "name": x.date
, "value": x.daily_value
} for x in q.all()])
print rows
return rows
def filter_query_remove_summer_months(self, table):
""" Return original query plus filters to remove summer months. """
self.base_query = self.base_query.\
filter(or_(func.month(table.date) < 5,
func.month(table.date) > 9))
return self.base_query
def index(all_flag=None):
# Filter per user, and open status
memo = Memo.query.filter(Memo.user_id==current_user.id)
issue = Issue.query.filter(Issue.user_id==current_user.id)
# Filter per status, all user
if all_flag == "all":
memo = Memo.query
issue = Issue.query
counter = {
't_memo': memo.count(),
't_issue': issue.count(),
'to_memo': memo.filter(Memo.status==3).count(),
'to_issue': issue.filter(Issue.status==1).count()
}
# add/join other table after filtering
memo = memo.join(Vendor, User).filter(Memo.status==3).\
order_by(asc(Memo.waiting_date)).\
values(Memo.id,Memo.subject, Vendor.short_hand, Memo.waiting_date, User.username)
issue = issue.join(Vendor, User).filter(Issue.status==1).\
order_by(asc(Issue.waiting_date)).\
values(User.username, Issue.issue_description,
Vendor.short_hand, Issue.waiting_date,
Issue.id)
issue_group_date = Issue.query.add_columns(func.sum(func.IF(Issue.status==1, 1, 0)), # sum/count total if status is Open
func.sum(func.IF(Issue.status==2, 1, 0)), # sum/count total if status is Close
func.year(Issue.waiting_date),
func.month(Issue.waiting_date)
).\
group_by(func.year(Issue.waiting_date),
func.month(Issue.waiting_date)).\
all()
issue_date_data = []
for element in issue_group_date:
issue_date_data.append(
{
"date": "{}-{}".format(element[3],element[4]),
"open": int(element[1]),
"close": int(element[2])
},
)
return render_template("index.html",
memo_data=memo,
issue_data=issue,
issue_date_data=issue_date_data,
counter=counter
)
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 get_records_between_dates(brand_name, d1, d2):
"""Get a list of tuple of (year, month, brand name, search counts of that month)."""
brand_name = brand_name if brand_name else ''
records = session.query(
func.year(SearchRecords.date), func.month(SearchRecords.date),
SearchRecords.brand_name, func.count(SearchRecords.brand_name)) \
.filter(SearchRecords.brand_name.ilike('%'+brand_name+'%'))\
.filter(SearchRecords.date.between(d1, d2))\
.group_by(func.year(SearchRecords.date), func.month(SearchRecords.date), SearchRecords.brand_name)\
.all()
print(*records, sep='\n')
return records
def run(cls, filters):
fd = filters['FromDate'].split('-')
td = filters['ToDate'].split('-')
FromDate = date(int(fd[0]), int(fd[1]), int(fd[2]))
ToDate = date(int(td[0]), int(td[1]), int(td[2]))
session = Session()
records = session.query(func.concat(func.year(User.CreatedDate),'-',func.month(User.CreatedDate)), func.count(User.CreatedDate).label('CNT'))\
.filter(User.CreatedDate.between(FromDate,ToDate))\
.group_by(func.concat(func.year(User.CreatedDate),'-',func.month(User.CreatedDate)))\
.order_by(func.concat(func.year(User.CreatedDate),'-',func.month(User.CreatedDate)).desc())\
.all()
session.close()
columns = ['Mes', 'Cantidad']
return {'Columns': columns, 'Rows': records}
def standard_deviation_demand(demand_list):
for demand in demand_list:
[min, max] = min_max_trimester(demand[2])
query = session.query(Order.QTDE).filter(
func.month(Order.DATAPED) >= min,
func.month(Order.DATAPED) <= max,
func.year(Order.DATAPED) == demand[1], Order.IDPROD == demand[0])
qtd_list = query.all()
qtd_array = []
for qtd in qtd_list:
qtd_array.append(qtd[0])
demand.append(statistics.stdev(qtd_array))
#[IDPROD, YEAR, TRIMESTER, AVERAGE_DEMAND, STANDARD_DEVIATION_DEMAND]
return demand_list
def expense_amt_data():
i_expenses = {}
expenses = session.query(func.round(func.sum(ExpenseInfo.expense_amt)),
func.month(ExpenseInfo.transaction_date)).\
group_by(func.month(ExpenseInfo.transaction_date))
for e in expenses.all():
i_expenses[e[1]] = str(e[0])
data = {
"expenses": i_expenses
}
return jsonify(data)
def lead_time_standard_deviation(demand_list):
for demand in demand_list:
[min, max] = min_max_trimester(demand[2])
query = session.query(Provision.DATACOMP, Provision.DATAARREC).filter(
Provision.IDPROD == demand[0],
func.month(Order.DATAPED) >= min,
func.month(Order.DATAPED) <= max,
func.year(Order.DATAPED) == demand[1])
provision_time = query.all()
provision_array = []
for item in provision_time:
provision_array.append(abs(item[1] - item[0]).days)
demand.append(statistics.stdev(provision_array))
#[IDPROD, YEAR, TRIMESTER, AVERAGE_DEMAND, STANDARD_DEVIATION_DEMAND, lead_time_mean, lead_time_standard_deviation]
return demand_list
def lead_time_mean(demand_list):
for demand in demand_list:
[min, max] = min_max_trimester(demand[2])
query = session.query(Provision.DATACOMP, Provision.DATAARREC).filter(
Provision.IDPROD == demand[0],
func.month(Order.DATAPED) >= min,
func.month(Order.DATAPED) <= max,
func.year(Order.DATAPED) == demand[1])
provision_time = query.all()
total_time = 0
for item in provision_time:
total_time += abs(item[1] - item[0]).days
demand.append(total_time / float(query.count()))
#[IDPROD, YEAR, TRIMESTER, AVERAGE_DEMAND, STANDARD_DEVIATION_DEMAND, lead_time_mean]
return demand_list
def _get_host_service_list_monthly_average_from_days(self, start, end):
return self.__mysql_session.query(HostService, func.avg(HostService.avg_Uptime).label('monthlyUptime')) \
.group_by(HostService.entityId) \
.group_by(func.month(HostService.timestampId)) \
.filter(HostService.aggregationType == 'd') \
.filter(HostService.timestampId >= start, HostService.timestampId <= end) \
.all()
def histogram(session, name, days=365):
today = util.utcnow().date()
start = today - timedelta(days=days)
stat_key = STAT_TYPE[name]
month_key = (func.year(Stat.time), func.month(Stat.time))
rows = session.query(func.max(Stat.value), *month_key).filter(
Stat.key == stat_key).filter(
Stat.time >= start).filter(
Stat.time < today).group_by(
*month_key).order_by(
*month_key
)
result = []
for num, year, month in rows.all():
# use first of August to plot the highest result for July
if month == 12: # pragma: no cover
next_month = date(year + 1, 1, 1)
else:
next_month = date(year, month + 1, 1)
if next_month >= today:
# we restrict dates to be at most yesterday
next_month = today - timedelta(days=1)
day = timegm(next_month.timetuple()) * 1000
result.append([day, num])
return [result]
def samples_per_month(self, year):
"""Fetch samples per month. Grouped by priority."""
query = (self.session.query(
models.Customer.priority.label("priority"),
func.month(models.Sample.received_at).label("month_no"),
func.count(models.Sample.id).label("count"),
).join(models.Sample.customer).filter(
models.Sample.received_at > self.get_from_date(year),
models.Sample.received_at < self.get_until_date(year),
).group_by("priority", "month_no"))
for cust_priority, results in groupby(
query, key=lambda result: result.priority):
counts = {
MONTHS[result.month_no]: result.count
for result in results
}
data = {
"name": cust_priority,
"results": {
month: counts.get(month) or None
for month in MONTHS.values()
},
}
yield data
def samples_per_month_application(self, year):
"""Fetch samples per month. Grouped by application cathegory."""
query = (self.session.query(
models.Application.category.label("category"),
func.month(models.Sample.received_at).label("month_no"),
func.count(models.Sample.id).label("count"),
).join(
models.Sample.application_version,
models.ApplicationVersion.application,
).filter(
models.Sample.received_at > self.get_from_date(year),
models.Sample.received_at < self.get_until_date(year),
models.Sample.received_at,
models.Application.category,
).group_by("category", "month_no"))
for application_cathegory, results in groupby(
query, key=lambda result: result.category):
counts = {
MONTHS[result.month_no]: result.count
for result in results
}
data = {
"name": application_cathegory,
"results": {
month: counts.get(month) or None
for month in MONTHS.values()
},
}
yield data
def get(self):
self.set_header("Content-Type", "application/json")
self.set_header('Access-Control-Allow-Origin', '*')
master_id = self.get_argument('master_id', '')
month = self.get_argument('month', 0)
print master_id, month
month = int(month)
if month == 0:
month = dt.datetime.now().month
ret = {}
try:
session = Session()
row = session.query(func.count(MasterBookingModifyRequest)) \
.filter(MasterBookingModifyRequest.master_id == master_id) \
.filter(func.month(MasterBookingModifyRequest.request_time) == month ) \
.one()
count = row[0]
ret['response'] = count
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
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.code == course).order_by(Dates.date).order_by(Classes.starttime).all()
for item in tempvar:
templist.append(item)
return templist
def generate_employee_summary(employee_id, year, month):
worklogs = Worklog.query.filter(Worklog.employee_id == employee_id,
func.month(Worklog.work_date) == month,
func.year(
Worklog.work_date) == year).all()
salary = decimal.Decimal(0.0)
for worklog in worklogs:
project_id = worklog.task.sprint.project_id
employee_role = ProjectEmployeeRole.query.filter(
ProjectEmployeeRole.project_id == project_id,
ProjectEmployeeRole.employee_id == employee_id).first()
if project_id and employee_role:
salary = salary + employee_role.rate * worklog.logged_hours
rows_deleted = Summarize.query.filter(Summarize.employee_id == employee_id,
Summarize.month == month,
Summarize.year == year).delete()
summary = Summarize(employee_id, month, year, float(salary))
try:
db.session.add(summary)
db.session.flush()
if summary in db.session:
db.session.commit()
return SummarizeSchema().jsonify(summary)
except DatabaseError:
return {'error': 'Summary did not generate properly'}, 501
def check_date(month, year):
# Kiểm tra xem có nhập xuất trong năm đó không
year_check_export = db.session.query(
Invoice.invoice_id).filter(func.year(Invoice.date) == year).first()
year_check_import = db.session.query(
BookImport.id).filter(func.year(BookImport.date) == year).first()
print(year_check_import, ' ', year_check_export)
if year_check_import is not None and year_check_export is not None:
month_check_export = db.session.query(Invoice.invoice_id).filter(
func.month(Invoice.date) == month).first()
month_check_import = db.session.query(BookImport.id).filter(
func.month(BookImport.date) == month).first()
print(month_check_import, ' ', year_check_export)
if month_check_import is not None and month_check_export is not None:
return True
return False
def histogram(session, name, days=365):
today = util.utcnow().date()
start = today - timedelta(days=days)
stat_key = STAT_TYPE[name]
month_key = (func.year(Stat.time), func.month(Stat.time))
rows = session.query(func.max(Stat.value), *month_key).filter(
Stat.key == stat_key).filter(
Stat.time >= start).filter(
Stat.time < today).group_by(
*month_key).order_by(
*month_key
)
result = []
for num, year, month in rows.all():
# use first of August to plot the highest result for July
if month == 12:
next_month = date(year + 1, 1, 1)
else:
next_month = date(year, month + 1, 1)
if next_month >= today:
# we restrict dates to be at most yesterday
next_month = today - timedelta(days=1)
day = next_month.strftime('%Y-%m-%d')
result.append({'day': day, 'num': num})
return result
def has_already_run(self):
"""检查任务是否已经执行过
@override
首先获取当前应该判断那个月
然后看看这个月是否已经执行过了
"""
try:
target_month, target_year = get_target_month_year()
session = db.get_session()
pushed_log_list = session.query(Push_log).\
filter(Push_log.task_id == self.task_id).\
filter(Push_log.push_plugin == self.get_task_plugin_name()).\
filter(Push_log.push_user == self.user).\
filter(func.month(Push_log.push_datetime) == target_month).\
filter(func.year(Push_log.push_datetime) == target_year).\
all()
pushed_log_num = len(pushed_log_list)
LOG.debug("[task_name: %s] [task_id: %d], pushed_log_list length is %d",
self.get_task_plugin_name(), self.task_id,
pushed_log_num)
if pushed_log_num > 0:
return True
return False
finally:
session.close()
def group_by_period(self):
"""Group by time period (per month)"""
return self \
.add_columns (label("month", func.month(DB.SCMLog.date)),
label("year", func.year(DB.SCMLog.date))) \
.group_by("month", "year").order_by("year", "month")
def _get_host_service_list_monthly_average_from_days(self, start, end):
return self.__mysql_session.query(HostService, func.avg(HostService.avg_Uptime).label('monthlyUptime')) \
.group_by(HostService.entityId) \
.group_by(func.month(HostService.timestampId)) \
.filter(HostService.aggregationType == 'd') \
.filter(HostService.timestampId >= start, HostService.timestampId <= end) \
.all()
def group_by_period (self):
"""Group by time period (per month)"""
return self \
.add_columns (label("month", func.month(SCMLog.date)),
label("year", func.year(SCMLog.date))) \
.group_by("month", "year").order_by("year", "month")
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 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_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 company_balance(self, id):
orders = meta.Session.query(func.year(Order.created_at).label('year'),
func.month(Order.created_at).label('month'),
func.sum(Order.real_value).label('value')) \
.join(Order.transport_order) \
.filter(and_(Order.idCompany == id, Order.created_at.between(h.today(365), h.today()))) \
.group_by( func.year(Order.created_at), func.month(Order.created_at)) \
.all()
profits = meta.Session.query(func.year(Order.created_at).label('year'),
func.month(Order.created_at).label('month'),
func.sum(Order.real_value - TransportOrder.real_value).label('value')) \
.join(Order.transport_order) \
.filter(and_(Order.idCompany == id, Order.created_at.between(h.today(365), h.today()))) \
.group_by( func.year(Order.created_at), func.month(Order.created_at)) \
.all()
chart = open_flash_chart()
t = title(text='Zestawienie')
b = bar()
b.values = [order.value for order in orders]
b2 = bar()
b2.values = [order.value for order in profits]
b2.colour = '#56acde'
lbl = labels(labels = [str(order.year) + ' ' + str(order.month) for order in orders])
x = x_axis()
x.labels = lbl
y = y_axis()
y.min, y.max= 0, max(b.values or [0])
y.labels = None
y.offset = False
chart.title = t
chart.y_axis = y
chart.x_axis = x
chart.add_element(b)
chart.add_element(b2)
return chart.render()
def get_charts():
incoming = request.args
year = date.today().year
if "year" in incoming:
year = incoming["year"]
chartsList = []
accountsObjects = Account.get_accounts(g.current_user)
for account in accountsObjects:
positive_result = db.session \
.query(cast(func.sum(Transaction.amount), sqlalchemy_string), func.month(Transaction.date)) \
.filter(Transaction.account_id == account.id,
Transaction.amount > 0,
db.func.date(Transaction.date) <= datetime.now().date(),
func.year(Transaction.date) == year) \
.group_by(func.month(Transaction.date)) \
.all()
negative_result = db.session \
.query(cast(func.sum(Transaction.amount), sqlalchemy_string), func.month(Transaction.date)) \
.filter(Transaction.account_id == account.id,
Transaction.amount < 0,
db.func.date(Transaction.date) <= datetime.now().date(),
func.year(Transaction.date) == year) \
.group_by(func.month(Transaction.date)) \
.all()
positive_data = []
negative_data = []
for positive_month in positive_result:
positive_data.append({
'amount': positive_month[0],
'month': positive_month[1]
})
for negative_month in negative_result:
negative_data.append({
'amount': negative_month[0],
'month': negative_month[1]
})
chartsList.append({
'account_id': account.id,
'positive_data': positive_data,
'negative_data': negative_data,
})
return jsonify(result=chartsList)
def individual_graph(request):
session = request.dbsession
result = OrderedDict()
today = datetime.date.today()
begin_date = datetime.date(day=1, month=today.month, year=today.year - 1)
end_date = datetime.date(day=1, month=today.month, year=today.year)
query = select([
func.count('*').label('nb'),
func.year(Individual.creationDate).label('year'),
func.month(Individual.creationDate).label('month')]
).where(and_(Individual.creationDate >= begin_date, Individual.creationDate < end_date)
).group_by(func.year(Individual.creationDate), func.month(Individual.creationDate)
)
data = session.execute(query).fetchall()
for nb, y, m in sorted(data, key=operator.itemgetter(1, 2)):
d = datetime.date(day=1, month=m, year=y).strftime('%b')
result[' '.join([d, str(y)])] = nb
return result
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 _likes_aggregation_by_month(criteria):
records = (
db.session
.query(
func.year(Like.created_at).label("year"),
func.month(Like.created_at).label("month"),
func.count(Like.id).label("count"))
.select_from(UserProfile)
.join(Post)
.join(Like)
.filter(*criteria)
.group_by(func.year(Like.created_at), func.month(Like.created_at))
.all()
)
return [{
"year": r.year,
"month": r.month,
"count": r.count
} for r in records]
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 individual_graph(request):
session = request.dbsession
result = OrderedDict()
today = datetime.date.today()
begin_date = datetime.date(day=1, month=today.month, year=today.year - 1)
end_date = datetime.date(day=1, month=today.month, year=today.year)
query = select([
func.count('*').label('nb'),
func.year(Individual.creationDate).label('year'),
func.month(Individual.creationDate).label('month')
]).where(
and_(Individual.creationDate >= begin_date,
Individual.creationDate < end_date)).group_by(
func.year(Individual.creationDate),
func.month(Individual.creationDate))
data = session.execute(query).fetchall()
for nb, y, m in sorted(data, key=operator.itemgetter(1, 2)):
d = datetime.date(day=1, month=m, year=y).strftime('%b')
result[' '.join([d, str(y)])] = nb
return result
def total_orders():
total_orders = session.query(
Order.IDPROD,
case([(func.month(Order.DATAPED) < 4, 1),
(func.month(Order.DATAPED) < 7, 2),
(func.month(Order.DATAPED) < 10, 3)],
else_=4), func.year(Order.DATAPED),
func.sum(Order.QTDE)).group_by(
case([(func.month(Order.DATAPED) < 4, 1),
(func.month(Order.DATAPED) < 7, 2),
(func.month(Order.DATAPED) < 10, 3)],
else_=4), func.year(Order.DATAPED), Order.IDPROD).all()
for demand in total_orders:
time = session.query(TimeDimension).filter(
TimeDimension.TRIMESTRE == demand[1],
TimeDimension.ANO == demand[2]).first()
if (time is None):
time = TimeDimension(TRIMESTRE=demand[1], ANO=demand[2])
session.add(time)
time = session.query(TimeDimension).filter(
TimeDimension.TRIMESTRE == time.TRIMESTRE,
TimeDimension.ANO == time.ANO).first()
order = NumOrdersFact(IDPROD=demand[0],
IDTEMPO=time.ID,
TOTAL_PEDIDO=float(demand[3]))
session.add(order)
session.commit()
def dispatch_request(self, **kwargs):
self.kwargs = kwargs
basequery = self.get_queryset()
income_data = (basequery.filter(Transaction.amount >= 0).with_entities(
func.year(Transaction.date) + '/' + func.month(Transaction.date),
func.sum(Transaction.amount)).group_by(
func.year(Transaction.date),
func.month(Transaction.date),
))
outcome_data = (basequery.filter(Transaction.amount < 0).with_entities(
func.year(Transaction.date) + '/' + func.month(Transaction.date),
0 - func.sum(Transaction.amount)).group_by(
func.year(Transaction.date),
func.month(Transaction.date),
))
all_data = (basequery.with_entities(
func.year(Transaction.date) + '/' + func.month(Transaction.date),
func.sum(Transaction.amount)).group_by(
func.year(Transaction.date),
func.month(Transaction.date),
))
context = self.get_context_data()
return render_template(self.template_name,
income_data=income_data,
outcome_data=outcome_data,
all_data=all_data,
**context)
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 populate_qtde():
real_demand = session.query(
Order.IDPROD,
case([
(func.month(Order.DATAPED) < 4, 1),
(func.month(Order.DATAPED) < 7, 2),
(func.month(Order.DATAPED) < 10, 3)
],
else_=4),
func.year(Order.DATAPED),
func.sum(Order.QTDE)
).group_by(
case([
(func.month(Order.DATAPED) < 4, 1),
(func.month(Order.DATAPED) < 7, 2),
(func.month(Order.DATAPED) < 10, 3)
],
else_=4),
func.year(Order.DATAPED),
Order.IDPROD
).all()
for demand in real_demand:
time = session.query(TimeDimension).filter(TimeDimension.TRIMESTRE == demand[1], TimeDimension.ANO == demand[2]).first()
if (time is None):
time = TimeDimension(TRIMESTRE = demand[1], ANO = demand[2])
session.add(time)
session.commit()
time = session.query(TimeDimension).filter(TimeDimension.TRIMESTRE == time.TRIMESTRE, TimeDimension.ANO == time.ANO).first()
order = engine.execute(insert(ProjectedDemandFact).values(IDPROD = demand[0], IDTEMPO = time.ID, QTDE = float(demand[3])).on_duplicate_key_update(QTDE = float(demand[3])))
session.commit()
def average_demand():
total_orders = session.query(
Order.IDPROD,
case([
(func.month(Order.DATAPED) < 4, 1),
(func.month(Order.DATAPED) < 7, 2),
(func.month(Order.DATAPED) < 10, 3)
],
else_=4),
func.year(Order.DATAPED),
func.sum(Order.QTDE)
).group_by(
case([
(func.month(Order.DATAPED) < 4, 1),
(func.month(Order.DATAPED) < 7, 2),
(func.month(Order.DATAPED) < 10, 3)
],
else_=4),
func.year(Order.DATAPED),
Order.IDPROD
).all()
mean_list = []
for total in total_orders:
#[IDPROD, YEAR, TRIMESTER, AVERAGE_DEMAND]
mean_list.append([total[0], total[2], total[1], float(total[3])/3.0])
return mean_list
def by_date():
# This is query for grouping Issue per Date
group_date = Csr.query.filter(Csr.status==1)
group_date = group_date.add_columns(func.count(Csr.status), # sum/count total if status is Open
func.year(Csr.report_date),
func.month(Csr.report_date)
)
group_date = group_date.group_by(func.year(Csr.report_date),
func.month(Csr.report_date))
group_date = group_date.all()
date_data = []
for element in group_date:
date_data.append(
{
"date": "{}-{}".format(element[-2],element[-1]+1), # + 1 For Months, due to javascript months is 0-11
#"date": dt.date(element[-2],element[-1],1),
"open": int(element[1]),
},
)
return date_data
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 histogram(session, stat_key, days=365):
today = util.utcnow().date()
start = today - timedelta(days=days)
month_key = (func.year(Stat.time), func.month(Stat.time))
rows = (session.query(func.max(Stat.value), *month_key)
.filter((Stat.key == stat_key),
(Stat.time >= start))
.group_by(*month_key)
.order_by(*month_key))
result = []
for num, year, month in rows.all():
# Use the first of the month to graph the value
# for the entire month.
day = timegm(date(year, month, 1).timetuple()) * 1000
result.append([day, num])
return [result]
def get_grouped_notices(user):
"""
Group notices by year and month
:param user:
:return:
"""
groups = list()
notice_group = Notice.query.order_by(Notice.created_on.desc()).filter_by(user_id=user.id).\
group_by(func.year(Notice.created_on), func.month(Notice.created_on)).all()
for item in notice_group:
notices = Notice.query.order_by(Notice.created_on.desc()).filter_by(user_id=user.id).filter(
(extract('year', Notice.created_on) == item.created_on.year) &
(extract('month', Notice.created_on) == item.created_on.month)
)
groups.append([item.created_on, notices])
return groups
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 dispatch_request(self, **kwargs):
self.kwargs = kwargs
basequery = self.get_queryset()
income_data = (
basequery
.filter(Transaction.amount >= 0)
.with_entities(
func.year(Transaction.date) + '/' + func.month(Transaction.date),
func.sum(Transaction.amount))
.group_by(
func.year(Transaction.date),
func.month(Transaction.date),
)
)
outcome_data = (
basequery
.filter(Transaction.amount < 0)
.with_entities(
func.year(Transaction.date) + '/' + func.month(Transaction.date),
0 - func.sum(Transaction.amount))
.group_by(
func.year(Transaction.date),
func.month(Transaction.date),
)
)
all_data = (
basequery
.with_entities(
func.year(Transaction.date) + '/' + func.month(Transaction.date),
func.sum(Transaction.amount))
.group_by(
func.year(Transaction.date),
func.month(Transaction.date),
)
)
context = self.get_context_data()
return render_template(
self.template_name,
income_data=income_data,
outcome_data=outcome_data,
all_data=all_data,
**context
)
