def tobs():
"""
`/api/v1.0/tobs`
Query the dates and temperature observations of the most active station for the last year of data.
Return a JSON list of temperature observations (TOBS) for the previous year.
"""
# create the sqlite link
session, Measurement, _ = sqlite_link()
# get the most active station
most_active_station = most_occurrence(session, Measurement.station)
# get the most recent date,
# we will use that as the ending date
end_date = dt.datetime.strptime(most_recent(session, Measurement.date),
datetime_format)
# use the ending date as a means to go back
# in time a year
start_date = end_date - rd.relativedelta(years=1)
# get all the data with that station,
# within the range
station_data_range = session.query(Measurement).filter(
# filter most active station
Measurement.station == most_active_station,
# use the func.DATE to make sure your date is actually
# comparing datetime objects as opposed to a string objects
func.DATE(Measurement.date) >= func.DATE(start_date.date())).order_by(
Measurement.date)
# create the sending data
stations_list = query_to_json_dict_list(station_data_range)
# Close Session
session.close()
# return data
return jsonify(stations_list)
def start(start_date):
"""Return a JSON list of the minimum temperature, the average temperature, and the max temperature for a given start"""
print("Server received request for start period search page...")
if len(start_date) == 10:
session = Session(engine)
sel = [
Measurement.station,
func.min(Measurement.tobs),
func.max(Measurement.tobs),
func.avg(Measurement.tobs)
]
results4=session.query(*sel).filter(func.DATE(Measurement.date) >= func.DATE(start_date)).\
group_by(Measurement.station).order_by(Measurement.station).all()
session.close()
if len(results4) > 0:
all_starts = []
for row in results4:
start_dict = {}
start_dict[row[
0]] = f"Min. Temperature :{row[1]}, Max. Temperature :{row[2]}, Avg. Temperature :{round(row[3],2)}"
all_starts.append(start_dict)
return jsonify(all_starts)
else:
return jsonify({
"Warning":
f"No records found for this date {start_date}. Try another date!!!"
}), 404
else:
return jsonify({
"error":
f"Invalid Date format {start_date}. Please use YYYY-MM-DD format and try again!!!"
}), 404
return jsonify(all_starts)
def persentase_hadir_data(tgl):
out = '''*BWS Sulawesi 2*
*Kehadiran Data*
%(tgl)s (0:0 - 23:55)
''' % {
'tgl': tgl.strftime('%d %b %Y')
}
pos_list = Lokasi.query.filter(Lokasi.jenis == '1')
if pos_list.count():
str_pos = ''
j_data = 0
i = 1
for l in pos_list:
banyak_data = Periodik.query.filter(
Periodik.lokasi_id == l.id,
func.DATE(Periodik.sampling) == tgl).count()
persen_data = (banyak_data / 288) * 100
j_data += persen_data
str_pos += '%s. %s ' % (i, l.nama + ': *%.1f%%*\n' % (persen_data))
i += 1
str_pos = '\n*Pos Hujan: %.1f%%*\n\n' % (j_data / (i - 1)) + str_pos
out += str_pos
# end pos_hujan
pos_list = Lokasi.query.filter(Lokasi.jenis == '2')
if pos_list.count():
str_pos = ''
i = 1
j_data = 0
persen_data = 0
for l in pos_list:
banyak_data = Periodik.query.filter(
Periodik.lokasi_id == l.id,
func.DATE(Periodik.sampling) == tgl).count()
persen_data = (banyak_data / 288) * 100
j_data += persen_data
str_pos += '%s. %s ' % (i, l.nama + ': *%.1f%%*\n' % (persen_data))
i += 1
str_pos = '\n*Pos TMA: %.1f%%*\n\n' % (j_data / (i - 1)) + str_pos
out += str_pos
# end pos_tma_list
pos_list = Lokasi.query.filter(Lokasi.jenis == '4')
if pos_list.count():
str_pos = ''
i = 1
j_data = 0
persen_data = 0
for l in pos_list:
banyak_data = Periodik.query.filter(
Periodik.lokasi_id == l.id,
func.DATE(Periodik.sampling) == tgl).count()
persen_data = (banyak_data / 288) * 100
j_data += persen_data
str_pos += '%s. %s ' % (i, l.nama + ': *%.1f%%*\n' % (persen_data))
i += 1
str_pos = '\n*Pos Klimatologi: %.1f%%*\n\n' % (j_data /
(i - 1)) + str_pos
out += str_pos
return out
def week_data(start_date):
last_visits = (db.session
.query(FlaskUsage.id)
.filter(FlaskUsage.datetime > func.DATE(start_date))
.count()
)
week_data = {
'month': start_date.strftime("%m"),
'0': {'date': start_date.strftime("%d/%m"), 'count': last_visits}
}
# 1(day ago) - yesterday, 2 - 2 days ago, 3 - 3 days ago .. etc. up to 6 days ago - represents a week
for i in range(1, 7):
day = start_date - timedelta(days=i)
count = db.session.query(FlaskUsage.id).filter(and_(
FlaskUsage.datetime < func.DATE(day + timedelta(days=1)),
FlaskUsage.datetime > func.DATE(day)
)).count()
#Visits for the specified day
week_data[str(i)] = {'date' : day.strftime("%d/%m") , 'count' : count}
return week_data
def load_all(
self,
session: Session,
date_start: Optional[dt.date] = None,
date_end: Optional[dt.date] = None,
limit: Optional[int] = None,
) -> List[DataAccess]:
""" Load all entries of the given data owner. """
query = session.query(DataAccess).filter(
DataAccess.data_owners.any(owner_rid=self.logged_in_user))
if date_start:
query = query.filter(func.DATE(DataAccess.timestamp) >= date_start)
if date_end:
query = query.filter(func.DATE(DataAccess.timestamp) <= date_end)
query = query.order_by(DataAccess.timestamp.desc())
if limit:
query = query.limit(limit)
data_accesses: List[DataAccess] = query.options(
# eager load data types
selectinload(DataAccess.data_types), ).all()
return data_accesses
def with_goods_summary(self, staff):
start_date = self.args["start_date"]
end_date = self.args["end_date"]
page = self.args["page"]
limit = self.args["limit"]
if limit > constants.PAGE_MAX_LIMIT:
limit = constants.PAGE_SIZE
goods_id_tuple = self.session.query(models.PurchaseOrderGoods.goods_id)\
.filter(models.PurchaseOrderGoods.purchaser_id == staff.id,
models.PurchaseOrderGoods.firm_id >= 0,
models.PurchaseOrderGoods.status >= 0)\
.distinct()\
.offset(page * limit)\
.limit(limit)\
.all()
goods_ids = [goods_id[0] for goods_id in goods_id_tuple] or [0]
purchase_goods_objs = self.session.query(models.PurchaseOrderGoods)\
.filter(models.PurchaseOrderGoods.goods_id.in_(goods_ids),
func.DATE(models.PurchaseOrderGoods.create_time) >= start_date,
func.DATE(models.PurchaseOrderGoods.create_time) < end_date,
models.PurchaseOrderGoods.purchaser_id == staff.id,
models.PurchaseOrderGoods.firm_id >= 0,
models.PurchaseOrderGoods.status >= 0)\
.all()
purchase_goods_dict = defaultdict(list)
for purchase_goods in purchase_goods_objs:
purchase_goods_dict[purchase_goods.goods_id].append(
purchase_goods.to_dict())
purchase_goods_data_list = list()
for goods_id, purchase_goods_info in purchase_goods_dict.items():
data = dict()
data["goods_id"] = purchase_goods_info[0]["goods_id"]
data["goods_name"] = purchase_goods_info[0]["goods_name"]
# 采购次数
data["purchase_count"] = len([
purchase_goods["firm_id"]
for purchase_goods in purchase_goods_info
])
# 总件数
data["goods_amount"] = check_float(
sum(purchase_goods["actual_amount"]
for purchase_goods in purchase_goods_info))
# 总支出
data["goods_subtotal"] = check_float(
sum(purchase_goods["subtotal"]
for purchase_goods in purchase_goods_info))
# 供货商名称列表
data["firm_name_list"] = list({
purchase_goods["firm_name"]
for purchase_goods in purchase_goods_info
})
purchase_goods_data_list.append(data)
has_more = len(goods_ids) >= limit
return self.send_success(
purchase_goods_data_list=purchase_goods_data_list,
has_more=has_more)
def workday_status(self,
dttm: datetime,
session: Optional[Session] = None):
"""X."""
selected_intervals = session.query(WorkInterval).filter(
func.DATE(WorkInterval.started_at) == func.DATE(dttm))
w_duration = pendulum.duration(
seconds=sum([x.duration for x in selected_intervals]))
if w_duration.seconds > 0:
first_start = (selected_intervals.order_by(
WorkInterval.started_at).first().started_at)
workday_duration = pendulum.duration(
minutes=conf.DEFAULT_WORKDAY_DURATION)
planed_end = first_start + workday_duration
remaining = workday_duration - w_duration
self.print_status(
data={
"first_start": first_start,
"planned_end": planed_end,
"w_duration": w_duration,
"remaining": remaining,
"workday_duration": workday_duration,
})
def search_by_form_filter(cls, date_from, date_to, sense_id, user,
relation_type):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
search_chain = list()
if date_from is not '':
search_chain.append(
func.DATE(TrackerSenseRelationsHistory.datetime) >= date_from)
if date_to is not '':
search_chain.append(
func.DATE(TrackerSenseRelationsHistory.datetime) <= date_to)
if sense_id is not '':
search_chain.append(
or_(TrackerSenseRelationsHistory.source_id == sense_id))
if user is not '':
search_chain.append(TrackerSenseRelationsHistory.user == user)
if relation_type is not '':
search_chain.append(
TrackerSenseRelationsHistory.relation_id == relation_type)
return and_(*search_chain)
def filter_texts(args):
#sorting in decreasing order by creation time
query = Text.query.order_by(-Text.created_at)
if 'date_to' in args:
query = query.filter(func.DATE(Text.created_at) <= args['date_to'])
if 'date_from' in args:
query = query.filter(
func.DATE(Text.created_at) >= args['date_from'])
if 'all_tags' in args:
tags = Tag.query.filter(Tag.content.in_(args['all_tags'])).all()
if len(tags) < len(args['all_tags']):
return [], None
#dirty hack TODO: get a better solution
for t in tags:
query = query.filter(Text.tags.contains(t))
elif 'any_tags' in args:
query = query.join(Tag, Text.tags).join(
Tag.query.join(Text, Tag.texts).filter(
Tag.content.in_(args['any_tags'])))
if 'no_tags' in args:
tags = Tag.query.filter(Tag.content.in_(args['no_tags'])).all()
#new dirty hack TODO: get a better solution
for t in tags:
query = query.filter(~Text.tags.contains(t))
query = query.offset(args['offset'])
if query.count() <= args['max_n_results']:
offset = None
else:
offset = args['offset'] + args['max_n_results']
texts = query.limit(args['max_n_results']).all()
return texts, offset
def chart():
labels = db.session.query(
func.DATE(
GameResult.time_created).label('date')).group_by('date').order_by(
asc('date')).all()
labels = [date[0] for date in labels]
query_wins = db.session.query(
func.DATE(GameResult.time_created).label('date'), GameResult.win,
func.count(GameResult.win)).group_by('date').group_by(GameResult.win)
wins = query_wins.all()
print(wins)
wins_table = {}
for date, win, g_count in wins:
wins_table[date] = wins_table.get(date, {})
wins_table[date][win] = g_count
values = []
for label in labels:
if label in wins_table:
wins = wins_table.get(label, {})
values.append(
wins.get(True, 0) / (wins.get(True, 1) + wins.get(False, 1)))
else:
values.append(None)
return render_template('chart.html', values=values, labels=labels)
def lead_log(date_report, session):
"""
Create a file with all leads to control
"""
query = session.query(db.Calltouch.datetime, db.Calltouch.department, db.Calltouch.telephone, db.Calltouch.fio,
db.Calltouch.deadline, db.Calltouch.status) \
.filter(func.DATE(db.Calltouch.deadline) == date_report)
c = 1
for i in query.all():
with open(os.path.join(config.basedir, 'leads_log.txt'),
'ab') as file:
datetime, dept, telephone, fio, deadline, status = i
string = u'Lead #{} {} {} {} {} {} {}\n'.format(
c, str(datetime), dept, telephone, fio, deadline,
status).encode('utf-8')
file.write(string)
query_new = session.query(db.Telephony.datetime, db.Telephony.call_type,
db.Telephony.telephone_from, db.Telephony.telephone_to, db.Telephony.duration) \
.filter(func.DATE(db.Telephony.datetime) == date_report, db.Telephony.telephone_to == telephone)
for j in query_new.all():
datetime_new, call_type, telephone_from, telephone_to, duration = j
new_string = u'{} {} {} {} {}\n'.format(
str(datetime_new), call_type, telephone_from,
telephone_to, duration).encode('utf-8')
file.write(new_string)
c += 1
def get_available_dates_after_date(date, date_range=3):
"""
Returns a list of dates that are not fully booked or otherwise unavailable
that are within a certain distance from a given date
"""
assert date_range >= 0
cutoff_date = date + datetime.timedelta(days=date_range)
unavailable_date_models = (
DB.session.query(models.UnavailableDate)
.filter(
and_(
func.DATE(models.UnavailableDate.date) >= date.date(),
func.DATE(models.UnavailableDate.date) <= cutoff_date.date(),
),
)
.all()
)
unavailable_dates = set(model.date.date() for model in unavailable_date_models)
all_dates_in_range = set(
date.date() + datetime.timedelta(days=day) for day in range(date_range)
)
available_dates = set()
for possible_date in all_dates_in_range:
available_times = get_available_times_for_date(possible_date)
free_timeslots = len(
[hour for hour, free in available_times.items() if free > 0]
)
if free_timeslots > 0:
available_dates.add(possible_date)
DB.session.commit()
return list(
datetime.datetime(available.year, available.month, available.day)
for available in available_dates.difference(unavailable_dates)
)
def get_car(db: Session,
name: str = "",
price_ge: int = None,
price_le: int = None,
mileage_ge: int = None,
mileage_le: int = None,
date_ge: date = None,
date_le: date = None,
limit: int = None) -> List[CarDB]:
queries = [CarDB.car_name.like(f'%{name}%')]
if price_ge:
queries.append(CarDB.price >= price_ge)
if price_le:
queries.append(CarDB.price <= price_le)
if mileage_ge:
queries.append(CarDB.mileage >= mileage_ge)
if mileage_le:
queries.append(CarDB.mileage <= mileage_le)
if date_ge:
queries.append(func.DATE(CarDB.entry_date) >= date_ge)
if date_le:
queries.append(func.DATE(CarDB.entry_date) <= date_le)
return db.query(CarDB).filter(*queries).limit(limit).all()
def add_order(data, id):
userID = data["id"]
body = request.get_json()
menus = body['menus']
if not menus:
return {'success': False, 'message': 'ไม่พบเมนู'}, 400
have_order = Order.query.filter(
func.DATE(Order.created_at) == date.today(), Order.shop_id == id,
Order.customer_id == userID,
or_(Order.status == 'ordering', Order.status == 'waiting')).count()
if not have_order:
new_order = Order(
customer_id=userID,
shop_id=id,
note=body["note"],
menus=[
OrderMenu(menu_id=menu['id'],
extra=menu['is_extra'],
total=menu['total']) for menu in menus
],
queue='A%02d' % Order.query.filter(
func.DATE(Order.created_at) == date.today()).count())
db.session.add(new_order)
db.session.commit()
return {
'success': True,
'order': new_order.getData(),
}, 201
return {'success': False, 'message': 'คุณมีออเดอร์ของร้านนี้อยู่แล้ว'}, 400
def check_status(date_report, session):
"""
Finds lead in db and check its status
"""
query = session.query(func.min(db.Calltouch.datetime), func.min(db.Telephony.datetime), db.Telephony.duration,
db.Calltouch.telephone, db.Calltouch.status, db.Calltouch.deadline) \
.join(db.Telephony, db.Telephony.telephone_to == db.Calltouch.telephone) \
.filter(func.DATE(db.Calltouch.deadline) == date_report, func.DATE(db.Telephony.datetime) == date_report,
db.Telephony.datetime > db.Calltouch.datetime, db.Calltouch.status != 'Doubled').group_by(db.Calltouch.telephone)
for i in query.all():
lead_date, call_date, duration, telephone, status, deadline = i
if duration <= 40 and call_date <= deadline:
status = 'Short call'
elif duration >= 40 and call_date > deadline:
status = 'Late call'
elif duration <= 40 and call_date > deadline:
status = 'Short and Late call'
elif duration >= 40 and call_date <= deadline:
status = 'Good call'
else:
status = 'Bad call'
session.query(db.Calltouch).filter(
db.Calltouch.datetime == lead_date,
db.Calltouch.telephone == telephone).update(
{'status': status}, synchronize_session=False)
def retrieve_products_on_date(cls, date):
Session = sessionmaker(bind=db.engine)
session = Session()
try:
query1 = ProductValuationsModel.query.filter(
func.DATE(ProductValuationsModel.DateOfValuation) <=
parse_iso_date(date)).with_entities(
ProductValuationsModel.ProductID,
func.max(ProductValuationsModel.DateOfValuation).label(
"MaxDate"),
ProductValuationsModel.ProductPrice).group_by(
ProductValuationsModel.ProductID)
statement1 = query1.cte("query_1") #SELECT statement
query2 = cls.query.filter(func.DATE(cls.DateEnteredInSystem) <= parse_iso_date(date), or_(parse_iso_date(date) <= func.DATE(cls.DateDeleted), cls.DateDeleted == None), cls.ProductID == ProductSellersModel.ProductID).\
with_entities(cls.ProductID, cls.ProductName, cls.DateEnteredInSystem, ProductSellersModel.CompanyCode)
statement2 = query2.cte("query_2") #SELECT statement
query3 = session.query(statement2).filter(
statement1.c.ProductID ==
statement2.c.ProductID).with_entities(
statement2.c.ProductID, statement2.c.ProductName,
statement2.c.DateEnteredInSystem, statement2.c.CompanyCode,
statement1.c.ProductPrice)
return query3
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_dataset_by_sensor_and_date(self, sensor, acq_date):
ds = self.session.query(Catalog_Dataset).filter(
Catalog_Dataset.sensor == sensor).filter(
func.DATE(Catalog_Dataset.acq_time) == func.DATE(
acq_date)).all()
print ds
return ds
def search_by_form_filter(cls, date_from, date_to, sense_id, user, pos,
status):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
search_chain = list()
if date_from is not '':
search_chain.append(
func.DATE(TrackerSenseHistory.datetime) >= date_from)
if date_to is not '':
search_chain.append(
func.DATE(TrackerSenseHistory.datetime) <= date_to)
if sense_id is not '':
search_chain.append(TrackerSenseHistory.key_id == sense_id)
if user is not '':
search_chain.append(TrackerSenseHistory.user == user)
if pos is not '':
search_chain.append(TrackerSenseHistory.key_pos == pos)
if status is not '':
search_chain.append(TrackerSenseHistory.key_status == status)
return and_(*search_chain)
def form_data():
try:
result = {}
patient_id = request.args.get('patient_id', '')
date = request.args.get('date', '')
if not bool(
User.query.filter_by(id=patient_id,
user_type='patient').first()):
return jsonify(response(False, 'Patient does not exists'))
if not date:
return jsonify(response(False, 'Date not provided'))
date = [int(i) for i in date.split('-')]
must = MUSTForm.query.filter(
patient_id == patient_id,
func.DATE(MUSTForm.timestamp) == datetime.date(
date[0], date[1], date[2])).order_by(MUSTForm.id.desc()).all()
mna = MNAForm.query.filter(
patient_id == patient_id,
func.DATE(MNAForm.timestamp) == datetime.date(
date[0], date[1], date[2])).order_by(MNAForm.id.desc()).all()
nrs = NRSForm.query.filter(
patient_id == patient_id,
func.DATE(NRSForm.timestamp) == datetime.date(
date[0], date[1], date[2])).order_by(NRSForm.id.desc()).all()
mnst20 = MNST20Form.query.filter(
patient_id == patient_id,
func.DATE(NRSForm.timestamp) == datetime.date(
date[0], date[1],
date[2])).order_by(MNST20Form.id.desc()).all()
result['must'] = [{
i.name: getattr(x, i.name)
for i in x.__table__.columns
} for x in must] if must else []
result['mna'] = [{
i.name: getattr(x, i.name)
for i in x.__table__.columns
} for x in mna] if mna else []
result['nrs'] = [{
i.name: getattr(x, i.name)
for i in x.__table__.columns
} for x in nrs] if nrs else []
result['mnst20'] = [{
i.name: getattr(x, i.name)
for i in x.__table__.columns
} for x in mnst20] if nrs else []
return jsonify(response(True, result=result))
except Exception as e:
return jsonify(
response(
False,
'Some unknown error occurred. Please try again after sometime.',
{"traceback": str(e)}))
def goods_summary_detail(self, staff):
goods_id = self.args["goods_id"]
start_date = self.args["start_date"]
end_date = self.args["end_date"]
purchase_goods_objs = self.session.query(models.PurchaseOrderGoods)\
.filter(func.DATE(models.PurchaseOrderGoods.create_time) >= start_date,
func.DATE(models.PurchaseOrderGoods.create_time) < end_date,
models.PurchaseOrderGoods.goods_id == goods_id,
models.PurchaseOrderGoods.purchaser_id == staff.id,
models.PurchaseOrderGoods.firm_id >= 0,
models.PurchaseOrderGoods.status >= 0)\
.all()
day_purchase_dict = defaultdict(list)
for purchase_goods in purchase_goods_objs:
purchase_date = TimeFunc.time_to_str(purchase_goods.create_time,
_type="date")
day_purchase_dict[purchase_date].append(purchase_goods.to_dict())
purchase_goods_data_list = list()
goods_summary_data = defaultdict(int)
total_actual_amount = 0
total_spending = 0
for date, purchase_goods_info in day_purchase_dict.items():
data = dict()
data["date"] = date
# 供货商列表
data["firm_name_list"] = list({
purchase_goods["firm_name"]
for purchase_goods in purchase_goods_info
})
# 总件数
data["goods_amount"] = check_float(
sum(purchase_goods["actual_amount"]
for purchase_goods in purchase_goods_info))
# 总支出
data["goods_subtotal"] = check_float(
sum(purchase_goods["subtotal"]
for purchase_goods in purchase_goods_info))
# 单价/元
data["price"] = check_float(data["goods_subtotal"] /
data["goods_amount"])
purchase_goods_data_list.append(data)
total_actual_amount += data["goods_amount"]
total_spending += data["goods_subtotal"]
# 按商品汇总数据
goods_summary_data["total_actual_amount"] += check_float(
total_actual_amount)
goods_summary_data["total_spending"] += check_float(total_spending)
# 排序
purchase_goods_data_list = sorted(purchase_goods_data_list,
key=lambda x: x["date"],
reverse=True)
return self.send_success(
purchase_goods_data_list=purchase_goods_data_list,
goods_summary_data=goods_summary_data)
def dashboard_project_stats(project: str):
"""
Displays the project dashboard
:param project: The project to show
:return: The template for the route
"""
proj_id = get_id_from_project_name(project)
# Project does not exist, error code is used by Javascript, not Flask
if proj_id == -1:
return redirect(
url_for("bp_dashboard.dashboard_project_list", error=404))
# Total graphs
total_spam = db.session.query(TComments).filter(
TComments.is_spam == True).count()
total_comments = db.session.query(TComments) \
.filter(TComments.is_spam == False)\
.filter(TComments.is_published == True).count()
total_unpublished = db.session.query(TComments).filter(TComments.is_spam == False)\
.filter(TComments.is_published == False).count()
# 7 day graph
dates = dates_of_the_week()
spam = list()
published = list()
unpublished = list()
for each in dates:
spam_comments = db.session.query(TComments).filter(TComments.project_id == proj_id) \
.filter(func.DATE(TComments.created_on) == each.date()) \
.filter(TComments.is_spam == True).all()
pub_comments = db.session.query(TComments).filter(func.DATE(TComments.created_on) == each.date()) \
.filter(TComments.project_id == proj_id) \
.filter(TComments.is_spam == False) \
.filter(TComments.is_published == True).all()
unpub_comments = db.session.query(TComments).filter(func.DATE(TComments.created_on) == each.date()) \
.filter(TComments.project_id == proj_id) \
.filter(TComments.is_spam == False) \
.filter(TComments.is_published == False).all()
published.append(len(pub_comments))
spam.append(len(spam_comments))
unpublished.append(len(unpub_comments))
return render_template('project-stats.html',
dates=dates,
spam=spam,
project=project,
published=published,
unpublished=unpublished,
total_spam=total_spam,
total_comments=total_comments,
total_unpublished=total_unpublished)
def group_by_date(db: Session, datetime_column, limit=None):
# TODO: Add index on date received and only query the last 7 days
q = db.query(func.Date(datetime_column),
func.count(func.DATE(datetime_column))).group_by(
func.DATE(datetime_column))
q = q.order_by(asc(func.DATE(datetime_column))).limit(limit).all()
return {date: count for date, count in q}
def get_demographics_data(self, state_id, date):
db_demographics = self.get_table('demographics')
query = db_demographics.select().where(
and_(db_demographics.columns.state_id == state_id,
func.DATE(db_demographics.columns.date) == func.DATE(date)))
result = self.db_engine.execute(query).fetchall()
if len(result) > 0:
return result[0].id
else:
return None
def days_interval(self, d_start_date, d_end_date, step=7, label="date"):
session = self.get_session()
cnt = session.query(func.DATE(d_start_date).label(label)) \
.cte(name="cnt", recursive=True)
next_date = func.DATE(self.to_days(cnt.c[label]) + (step)).label(label)
end_crit = next_date <= d_end_date
if step < 0:
end_crit = next_date >= d_end_date
union_all = cnt.union_all(select([next_date], cnt).where(end_crit))
return session.query(union_all)
def send_favorite_project_activities(user_id: int):
current_app.logger.debug("Sending Favorite Project Activities")
favorited_projects = UserService.get_projects_favorited(user_id)
contributed_projects = UserService.get_projects_mapped(user_id)
if contributed_projects is None:
contributed_projects = []
for favorited_project in favorited_projects.favorited_projects:
contributed_projects.append(favorited_project.project_id)
recently_updated_projects = (
Project.query.with_entities(
Project.id, func.DATE(Project.last_updated).label("last_updated")
)
.filter(Project.id.in_(contributed_projects))
.filter(
func.DATE(Project.last_updated)
> datetime.date.today() - datetime.timedelta(days=300)
)
)
user = UserService.get_user_dto_by_id(user_id)
if user.projects_notifications is False:
return
messages = []
for project in recently_updated_projects:
activity_message = []
query_last_active_users = """ select distinct(user_id) from
(select user_id from task_history where project_id = :project_id
order by action_date desc limit 15 ) t """
last_active_users = db.engine.execute(
text(query_last_active_users), project_id=project.id
)
for recent_user_id in last_active_users:
recent_user_details = UserService.get_user_by_id(recent_user_id)
user_profile_link = MessageService.get_user_profile_link(
recent_user_details.username
)
activity_message.append(user_profile_link)
activity_message = str(activity_message)[1:-1]
project_link = MessageService.get_project_link(project.id)
message = Message()
message.message_type = MessageType.PROJECT_ACTIVITY_NOTIFICATION.value
message.project_id = project.id
message.to_user_id = user.id
message.subject = (
"Recent activities from your contributed/favorited Projects"
)
message.message = (
f"{activity_message} contributed to Project {project_link} recently"
)
messages.append(dict(message=message, user=user))
MessageService._push_messages(messages)
def call_dept(phone, datetime, session):
"""
Telephone to name of department
"""
try:
query = session.query(db.Telephony.department) \
.filter(db.Telephony.telephone_from == phone,
func.DATE(db.Telephony.datetime) == func.DATE(datetime)).order_by(db.Telephony.datetime.desc())
return query.first()[0]
except TypeError:
return 'Unknown'
def get_trades_after(cls, startDate, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
func.DATE(DerivativeTradesModel.DateOfTrade) >=
parse_iso_date(startDate)).offset(offset).limit(limit)
else:
return cls.query.filter(
func.DATE(DerivativeTradesModel.DateOfTrade) >=
parse_iso_date(startDate))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def get_trades_modified_before(cls, endDate, offset, limitFlag, limit):
try:
if limitFlag:
return cls.query.filter(
func.DATE(DerivativeTradesModel.LastModifiedDate) <=
parse_iso_date(endDate)).offset(offset).limit(limit)
else:
return cls.query.filter(
func.DATE(DerivativeTradesModel.LastModifiedDate) <=
parse_iso_date(endDate))
except exc.ProgrammingError:
raise exc.ProgrammingError("", "", 1)
def show_last_intervals(self,
dttm: datetime,
n: int = 5,
session: Optional[Session] = None):
ses = reversed(
session.query(WorkInterval).filter(
func.DATE(WorkInterval.started_at) <= func.DATE(dttm)).
order_by(WorkInterval.started_at.desc()).limit(n).all())
for s in ses:
print("|", s.id, "|", s.started_at, "|", s.ended_at, "|")
return ses
def get_release_id(datestamp: datetime, process_name: str) -> Tuple[int, datetime]:
query = (
select([
ReleaseReference.id,
ReleaseReference.timestamp
])
.select_from(
join(
ReleaseReference, ReleaseCategory,
ReleaseReference.id == ReleaseCategory.release_id
)
)
.where(
and_(
func.DATE(ReleaseReference.timestamp) == func.DATE(datestamp.isoformat()),
ReleaseCategory.process_name == process_name
)
)
)
session = Session()
try:
response = session.execute(query)
result = response.fetchone()
if result is not None:
return result
except Exception as err:
session.rollback()
raise err
finally:
session.close()
try:
release = ReleaseReference(timestamp=datestamp)
session.begin()
session.add(release)
session.commit()
category = ReleaseCategory(release_id=release.id, process_name=process_name)
session.begin()
session.add(category)
session.commit()
except Exception as err:
session.rollback()
raise err
finally:
session.close()
return get_release_id(datestamp, process_name)