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 station_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(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 year_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 - TransportOrder.real_value).label('value')) \
.join(Order.transport_order) \
.filter(and_(Order.idCreator == 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]
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)
return chart.render()
def savings_month_wise_bar_graph():
if current_user and current_user.is_authenticated:
b = db.session.query(func.month(Expences.date_time),
func.year(Expences.date_time),
func.sum(Expences.debit).label('total'),
func.sum(Expences.credit).label('total')
).group_by(func.month(Expences.date_time),
func.year(Expences.date_time)) \
.filter(Expences.user_id == current_user.id)
_ = sorted([[calendar.month_name[i[0]], i[1], i[0], i[2], i[3]]
for i in b],
key=lambda x: (x[1], x[2]))
data_fig1 = [
go.Bar(x=[str(i[0]) + " " + str(i[1]) for i in _],
y=[i[4] - i[3] for i in _],
name="Savings",
marker={
'color': [i[4] - i[3] for i in _],
'colorscale': 'RdYlGn'
}),
]
layout_fig1 = go.Layout(
title="Savings Per month",
xaxis_title="Month And Year",
yaxis_title="Amount saved",
)
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 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 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 get_stats_stars_by_birthyear(db: Session):
query = db.query(func.year(models.Star.birthdate),
func.count(models.Movie.id).label("star_count")) \
.filter(models.Star.birthdate.isnot(None))
query = query.group_by(func.year(models.Star.birthdate)) \
.order_by(func.year(models.Star.birthdate))
return [{'birthyear': y, 'star_count': sc} for y, sc in query]
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 get_orders_report(date_from, date_to, mode):
if mode == 'days':
query = db.session.query(cast(Order.date, DATE),
func.count(Order.id_order))
query = query.filter(Order.date >= date_from).filter(
Order.date <= date_to)
query_result = query.group_by(cast(Order.date, DATE)).order_by(
cast(Order.date, DATE)).all()
return list(
map(lambda result: (result[0].isoformat(), result[1]),
query_result))
if mode == 'months':
query = db.session.query(func.year(Order.date), func.month(Order.date),
func.count(Order.id_order))
query = query.filter(Order.date >= date_from).filter(
Order.date <= date_to)
query_result = query.group_by(func.year(Order.date),
func.month(Order.date)).order_by(
func.year(Order.date),
func.month(Order.date)).all()
return list(
map(
lambda result:
(datetime(result[0], result[1], 1).isoformat(), result[2]),
query_result))
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 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 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 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 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_unused_sno_amount_price():
tspa = models.TmSparePartAll
# having(func.year(tspa.o_warehouse_date).notin_(['2019','2018']).label('year_o')).\
year_o = func.year(tspa.o_warehouse_date).label('year_o')
year_i = func.year(tspa.i_warehouse_date).label('year_i')
temp = db.session.query(year_i, year_o, func.sum(tspa.amount), func.sum(tspa.total_price)).\
group_by(year_i, year_o).\
having(text("year_o not in ('2018','2019')")).\
having(text('year_i < 2017')).\
order_by(tspa.i_warehouse_date).all()
return temp
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 get_years(house_id):
""" Return array of valid years of energy data for house X. """
years = db_session.query(func.year(EnergyMonthly.date)). \
filter(EnergyMonthly.house_id == house_id). \
group_by(func.year(EnergyMonthly.date)). \
order_by(func.year(EnergyMonthly.date))
json_items = []
for year in years:
json_items.append(str(year[0]))
return json_items
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 selectTableRowsUsingFormForDataAnalysis(form):
"""
Builds an sql query from form parameters, executes it, and returns the results as a list.
Used for Data Analysis Module.
:param form: Form containing user selected dimensions to aggregate data by.
:return: List of rows returned by the query. First row is table header.
"""
selectFields = []
# Populate selectFields from form
# Patient and Test Type
if form.patient.data:
selectFields.append(models.Record.patient_id.label(form.patient.label.text))
if form.test_type.data:
selectFields.append(models.Record.test_type.label(form.test_type.label.text))
# Test Date Period
# Hierarchy is Year, Year > Month, Year > Week
if form.test_date.data != form.NONE_LABEL:
# just year
if form.test_date.data == form.YEAR_LABEL:
selectFields.append(func.year(models.Record.test_date).label(form.YEAR_LABEL))
# month
if form.test_date.data == form.MONTH_LABEL:
selectFields.append(
func.concat(func.monthname(models.Record.test_date), ', ', func.year(models.Record.test_date)).label(
form.MONTH_LABEL))
# week
if form.test_date.data == form.WEEK_LABEL:
selectFields.append(
func.concat(func.weekofyear(models.Record.test_date), ', ', func.year(models.Record.test_date)).label(
form.WEEK_LABEL))
# construct query
query = db.session.query(models.Record).join(models.Image).group_by(*["`" + c.name + "`" for c in selectFields])
# Count results as number of images
selectFields.append(func.count('*').label("# of Images"))
# Execute the query with the selected fields
results = query.values(*selectFields)
# Reformat as list of rows with a header
# Construct header
resultsList = [[c.name for c in selectFields]]
# Add results from query
resultsList += list(results)
return resultsList
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 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 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 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 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 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 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_official_number(cls):
"""
Return the greatest official_number actually used in the
ManualInvoice table
"""
current_year = datetime.date.today().year
return DBSESSION().query(func.max(CancelInvoice.official_number)).filter(
func.year(CancelInvoice.taskDate) == current_year)
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 _likes_aggregation_by_year(criteria):
records = (
db.session
.query(
func.year(Like.created_at).label("year"),
func.count(Like.id).label("count"))
.select_from(UserProfile)
.join(Post)
.join(Like)
.filter(*criteria)
.group_by(func.year(Like.created_at))
.all()
)
return [{
"year": r.year,
"count": r.count
} for r in records]
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_top5_sno_data():
tsp = models.TmSparePart
temp = db.session.query(tsp.sno,func.year(tsp.o_warehouse_date),func.sum(tsp.amount).label('sum')).group_by(tsp.sno, func.year(tsp.o_warehouse_date)).order_by(desc('sum')).limit(5).all()
tsp_rs = []
for item in temp:
# print(type(item))
tsp_rs.append({'sno':item[0], 'sum':int(item[2])})
return tsp_rs
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 sales_cnts_by_week():
curr_year = datetime.now().year
cnts = db.session.query(func.week(Event.start_time),
func.count(func.week(Event.start_time)))\
.group_by(func.week(Event.start_time))\
.filter(func.year(Event.start_time) == curr_year).all()
return cnts
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 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 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 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 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, 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 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 get_years(house_id):
""" Return array of valid years of energy data for house X. """
years = db_session.query(EnergyMonthly.date). \
filter(EnergyMonthly.house_id == house_id). \
group_by(func.year(EnergyMonthly.date)). \
order_by(EnergyMonthly.date)
json_items = []
for year in years:
json_items.append(str(year[0].year))
return json_items
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 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
)
from ecorelevesensor.models.data import (
ProtocolArgos,
ProtocolGps,
ProtocolIndividualEquipment,
# ProtocolCaptureIndividual,
# ProtocolReleaseIndividual,
SatTrx,
Station
)
from ecorelevesensor.models import (TProtocolCaptureIndividual,TProtocolReleaseIndividual)
ReleaseSta = select([
Station.area.label('release_area'),
func.year(Station.date).label('release_year'),
TProtocolReleaseIndividual.FK_TInd_ID.label('ind_id')
]).select_from(join(Station, TProtocolReleaseIndividual,
TProtocolReleaseIndividual.FK_TSta_ID == Station.id
)).alias()
V_SearchIndiv = select([
Individual.id.label('id'),
Individual.chip_code.label('chip_code'),
Individual.breeding_ring.label('breeding_ring'),
Individual.release_ring.label('release_ring'),
Individual.age.label('age'),
Individual.mark1.label('mark1'),
Individual.mark2.label('mark2'),
Individual.monitoring_status.label('monitoring_status'),
Individual.origin.label('origin'),
