def get_user_count(self, ndays=7):
"""
Fetch statistic in the ndays
@return {
date: {
user_count,
question_count
},
date: {
user_count,
question_count
}...
}
"""
results = {}
start_t = datetime.datetime.now()
start_t -= datetime.timedelta(days=ndays)
rows = self._sess.query(func.date(Histories.CreateAt), \
func.count(func.distinct(Histories.DeviceId)), \
func.count(Histories.id)) \
.filter(Histories.CreateAt >= func.date(start_t)) \
.group_by(func.date(Histories.CreateAt)) \
.all()
for row in rows:
(date, user_cnt, q_cnt) = row
results[date] = {"question_count": q_cnt, "user_count": user_cnt}
return results
def reconcile(self):
broker_fills = self.broker_fills()
# db_fills = Fill.filter_df(func.date(Fill.dt) == datetime.datetime.today().date())
query = session.query(Fill, Order.ib_id).join(Order).filter(func.date(Fill.dt) == datetime.datetime.today().date())
db_fills = pandas.read_sql(query.statement, query.session.bind)
broker_fills.ib_id = broker_fills.ib_id.astype('int64')
comparison = broker_fills.merge(db_fills.drop('dt', 1), on=['contractid', 'ib_id', 'trade_amt', 'trade_px'], how='left')
unaccounted_fills = comparison[comparison.orderid.isnull()]
for i in xrange(len(unaccounted_fills)):
fill = unaccounted_fills.iloc[i]
order = Order.filter(and_(Order.ib_id == int(fill.ib_id), func.date(Order.dt) == fill['dt'].date())).first()
if order:
unaccounted_fills.ix[fill.name,'orderid'] = order.id
else:
email = Email("CRITICAL: {0} Fill Reconciliation Failed".format(datetime.datetime.now()),"")
email.send(EMAIL_CRITICAL)
return False
upload = unaccounted_fills.drop(['cusip','id','ib_id'],1)
upload_dict = upload.to_dict(orient='records')
Fill.__table__.insert().execute(upload_dict)
email = Email("CRITICAL: {0} Reconciliation Success".format(datetime.datetime.now()),"")
email.send(EMAIL_CRITICAL)
return True
def get_total_distances_by_user_in_last_days(self, user_id: int, days: int):
"""
Returns the total distance taken by a specified user in last days.
:param user_id: ID of user.
:param days: Number of days for which the total distances are returned.
:returns: The total distances for each day as dictionary.
"""
last_day = dt.date.today()
first_day = last_day - dt.timedelta(days=days - 1)
query_result = db.session.query(func.date(Activity.datetime).label('day'),
func.sum(Activity.distance).label('distance')). \
filter(Activity.user_id == user_id,
func.date(Activity.datetime) >= first_day,
func.date(Activity.datetime) <= last_day,
func.date(Activity.datetime) >= self.SEASON.start_date,
func.date(Activity.datetime) <= self.SEASON.end_date). \
group_by('day'). \
order_by(asc('day')). \
all()
result = {}
for i in range(7):
result[(first_day + dt.timedelta(days=i)).isoformat()] = 0
for item in query_result:
result[item.day] = item.distance
return result
def events(self):
has_all_permissions = self.request.has_permission(ALL_PERMISSIONS)
converter = DateConverter()
start_date = converter.to_python(self.request.params.get('start')) or datetime.today().date()
end_date = converter.to_python(self.request.params.get('end')) or datetime.today().date()
if has_all_permissions:
interactions = Interaction.query()
contacts = ContactPerson.query()
else:
user = self.request.authenticated_user
interactions = Interaction.query_with_permissions(user)
contacts = ContactPerson.query_with_permissions(user)
interactions = interactions.filter(
func.date(Interaction.followup_date) >= start_date,
func.date(Interaction.followup_date) <= end_date,
Interaction.status != 'Deleted'
)
contacts = contacts.filter(
func.extract('month', ContactPerson.birth_date) >= start_date.month,
func.extract('month', ContactPerson.birth_date) <= end_date.month
)
return Event.to_json(interactions, contacts)
def get_by_filter(cls, filter):
ignorable = [
'a', 'de', 'ante', 'para', 'por', 'sob', 'sobre', 'após',
'perante', 'com', 'entre', 'desde', 'o', 'um', 'uma'
]
try:
if filter == 'all':
courses = cls.query.all()
elif filter == 'expired':
courses = db.session.query(Course).filter(
func.date(Course.expires_at) < datetime.today().date()
).all()
elif filter == 'active':
courses = db.session.query(Course).filter(
func.date(Course.expires_at) >= datetime.today().date()
).all()
else:
search_words = filter.split('%20')
courses = db.session.query(Course)
for word in search_words:
courses = courses.filter(Course.name.match(word))
courses = courses.all()
except Exception as e:
return None
courses_list = []
for course in courses:
courses_list.append(course.as_dict())
return courses_list
def dashboard():
campaigns = (Campaign.query
.filter(Campaign.status_code >= STATUS_PAUSED)
.order_by(desc(Campaign.status_code))
)
calls_by_campaign = (db.session.query(Campaign.id, func.count(Call.id))
.filter(Campaign.status_code >= STATUS_PAUSED)
.filter(Call.status == 'completed')
.join(Call).group_by(Campaign.id))
today = datetime.today()
month_start = today.replace(day=1) # first day of the current month
next_month = today.replace(day=28) + timedelta(days=4) # a day in next month (for months with 28,29,30,31)
month_end = next_month - timedelta(days=next_month.day) # the last day of the current month
calls_this_month = (db.session.query(func.count(Call.id))
.filter(Call.status == 'completed')
.filter(Call.timestamp >= month_start)
.filter(Call.timestamp <= month_end)
).scalar()
calls_by_day = (db.session.query(func.date(Call.timestamp), func.count(Call.id))
.filter(Call.status == 'completed')
.filter(Call.timestamp >= month_start)
.filter(Call.timestamp <= month_end)
.group_by(func.date(Call.timestamp))
.order_by(func.date(Call.timestamp))
)
return render_template('admin/dashboard.html',
campaigns=campaigns,
calls_by_campaign=dict(calls_by_campaign.all()),
calls_by_day=calls_by_day.all(),
calls_this_month=calls_this_month
)
def accounts(self):
start = request.params.get('start',None)
end = request.params.get('end',None)
limit = int(request.params.get('days','0'))
opts = request.params.get('opts','').split(',')
groupby = []
whereclause = []
if limit and not start and not end:
whereclause.append(Account.created >= UTCDateTime.now() - timedelta(days=limit))
if start:
whereclause.append(Account.created >= UTCDateTime.from_string(start))
if end:
whereclause.append(Account.created < UTCDateTime.from_string(end))
if 'perday' in opts:
if 'domain' in opts:
s = select([func.date(Account.created), Account.domain, func.count(Account.id)])
groupby.append(func.to_days(Account.created))
groupby.append(Account.domain)
else:
s = select([func.date(Account.created), func.count(Account.id)])
groupby.append(func.to_days(Account.created))
else:
s = select([func.count(Account.domain), Account.domain])
groupby.append(Account.domain)
if whereclause:
s = s.where(*whereclause)
if groupby:
s = s.group_by(*groupby)
return [list(a) for a in Session.execute(s).fetchall()]
def obtener_ausencia(self, fkpersona, fecha):
respuesta = ""
ajuste = self.db.query(Ajustes).filter(Ajustes.enabled == True).first()
if ajuste.oracle:
# version oracle
ausencia = self.db.query(Ausencia).filter(
Ausencia.fkpersona == fkpersona).filter(
Ausencia.estado == "Aceptado").filter(
and_(
func.to_date(Ausencia.fechai) <= fecha,
func.to_date(Ausencia.fechaf) >= fecha)).all()
else:
# version postgres
ausencia = self.db.query(Ausencia).filter(
Ausencia.fkpersona == fkpersona).filter(
Ausencia.estado == "Aceptado").filter(
and_(
func.date(Ausencia.fechai) <= fecha,
func.date(Ausencia.fechaf) >= fecha)).all()
if len(ausencia) > 0:
respuesta = ausencia[0].tipoausencia.nombre
return respuesta
def selectReportMonthDetails(userId, year, month, startDay, endDay):
"""選択された月の日別とその稼働時間を取得するDB処理
:param userId: 登録ユーザID
:param year: 登録年
:param month: 登録月
:param startDay: 月の初日
:param endDay: 月の最後の日
"""
subq1 = db.session.query(
func.generate_series(
func.date(startDay) - func.CURRENT_DATE(),
func.date(endDay) - func.CURRENT_DATE()).label('i')).subquery()
subq2 = db.session.query(
func.cast(func.date_part('day',
func.CURRENT_DATE() + subq1.c.i),
Integer).label('day')).subquery()
monthDetails = db.session.query(
subq2.c.day,
__model.rest_flg,
db.func.to_char(
__model.normal_working_hours + __model.overtime_hours +
__model.holiday_work_hours, '999D99').label('workTime'),
).outerjoin(
__model,
and_(subq2.c.day == __model.work_day, __model.user_id == userId,
__model.work_year == year,
__model.work_month == month)).order_by(subq2.c.day).all()
return monthDetails
def dashboard():
campaigns = (Campaign.query.filter(
Campaign.status_code >= STATUS_PAUSED).order_by(
desc(Campaign.status_code)))
calls_by_campaign = (db.session.query(Campaign.id, func.count(
Call.id)).filter(Campaign.status_code >= STATUS_PAUSED).filter(
Call.status == 'completed').join(Call).group_by(Campaign.id))
today = datetime.today()
month_start = today.replace(day=1) # first day of the current month
next_month = today.replace(day=28) + timedelta(
days=4) # a day in next month (for months with 28,29,30,31)
month_end = next_month - timedelta(
days=next_month.day) # the last day of the current month
calls_this_month = (db.session.query(func.count(
Call.id)).filter(Call.status == 'completed').filter(
Call.timestamp >= month_start).filter(
Call.timestamp <= month_end)).scalar()
calls_by_day = (db.session.query(
func.date(Call.timestamp),
func.count(Call.id)).filter(Call.status == 'completed').filter(
Call.timestamp >= month_start).filter(
Call.timestamp <= month_end).group_by(func.date(
Call.timestamp)).order_by(func.date(Call.timestamp)))
return render_template('admin/dashboard.html',
campaigns=campaigns,
calls_by_campaign=dict(calls_by_campaign.all()),
calls_by_day=calls_by_day.all(),
calls_this_month=calls_this_month)
def query_by_range(session, call_table, start, end):
return session.query(call_table).filter(
and_(
call_table.call_direction == 1,
func.date(call_table.start_time) >= func.date(start),
func.date(call_table.start_time) <= func.date(end)
)
)
def get_flows(user_id, group_id):
params = {"user_id": user_id, "group_id": group_id}
w = aliased(WatchlistItems)
flows = WatchlistItems.query.\
with_entities(func.date(w.trade_date).label("index"), func.sum(w.quantity*w.price*-1).label("flows")).\
filter_by(**params).\
group_by(func.date(w.trade_date)).\
order_by(w.trade_date).all()
return flows
def get_item_timeseries_after_date(item_type,
item_id,
after_date,
session=None):
return (session.query(
func.count(Impression.uid),
func.date(Impression.created_at)).distinct(Impression.uid).filter_by(
item_type=item_type, item_id=item_id).filter(
Impression.created_at >= after_date).group_by(
func.date(Impression.created_at)).all())
def query_avg_all_stations(session, days=None) -> Query:
subquery_date = get_date_subquery(session, days)
result = session.query(
func.avg(Price.price).label("average_price"),
func.date(Price.date_of_price).label("date_of_price"),
Fuel.fuel_type).join(Fuel).filter(
subquery_date.c.date_of_price ==
func.date(Price.date_of_price)).group_by(
Fuel.fuel_type,
func.date(Price.date_of_price)).order_by(Fuel.fuel_type)
return result
def get_complex_query(account_number):
return (
Trade.query(FxRate).
with_entities(Trade, func.date(FxRate.date).label('fxrates_histdate'), FxRate.price).
join(FxRate, Trade.symbol == FxRate.symbol).
filter(
Trade.account == account_number,
FxRate.date >= func.date(func.from_unixtime(Trade.opentime)),
FxRate.date <= func.date(func.from_unixtime(Trade.closetime))
).
order_by(Trade.id)
)
def _get_total_distance_by_user(self, user_id: int, activity_types: list):
"""
Returns the total distance taken by a specified user in a specified types of activity.
:param user_id: ID of user.
:param activity_types: Types of activities we want to sum to total distance.
:returns: The total distance in kilometres.
"""
return db.session.query(func.sum(Activity.distance)). \
filter(Activity.user_id == user_id,
func.date(Activity.datetime) >= self.SEASON.start_date,
func.date(Activity.datetime) <= self.SEASON.end_date,
Activity.type.in_(activity_types)). \
scalar()
def _get_global_total_distance(self, activity_types: list):
"""
Returns the total distance by all users in specified activity types.
:param activity_types: Types of activities we want to sum to total distance.
:returns: The total distance in kilometres.
"""
return db.session.query(func.sum(Activity.distance)). \
select_from(User). \
join(User.activities). \
filter(func.date(Activity.datetime) >= self.SEASON.start_date,
func.date(Activity.datetime) <= self.SEASON.end_date,
Activity.type.in_(activity_types),
User.verified). \
scalar() or 0
def run_billing(self) -> None:
"""
Charge every subscribers
"""
session = self._get_session()
q = session.query(Subscription).filter(
and_(
Subscription.service_id > 0,
func.date(Subscription.next_billing_date) == func.date(func.now()),
)
)
# send billing for each subscription
for sub in q:
celery_tasks.send_billing.delay(sub.msisdn, sub.service_id)
def getFrequentationByCoursByUniversityYearForGraph(session, ID_COURS,
PUBLIC_CONS,
DATE_CONS_YEAR):
LOG.info("----- getConsultationByCoursByUniversityYearForGraph -----")
COC = aliased(tables.ConsultationCoursMySQL)
nbConsultations = session.query(func.date(
COC.DATE_CONS), func.count(COC.DATE_CONS)).filter(
and_(
COC.ID_COURS == ID_COURS, COC.TYPE_CONS == "Cours",
COC.PUBLIC_CONS == PUBLIC_CONS,
COC.DATE_CONS.between("%s/09/01" % str(DATE_CONS_YEAR - 1),
"%s/08/31" %
str(DATE_CONS_YEAR)))).group_by(
func.date(COC.DATE_CONS))
return nbConsultations
def completed_student(self, start_date):
try:
result_set = self.staging_session\
.query(self.Content_Summary_Log.c.user_id,self.Content_Summary_Log.c.content_id,\
func.date(self.Content_Summary_Log.c.completion_timestamp),self.Content_Summary_Log.c.channel_id) \
.filter(self.Content_Summary_Log.c.completion_timestamp >= start_date)
for record in result_set:
_student_id = record[0]
student_id = self.uuid2int(_student_id)
content_id = record[1]
channel_id = record[3]
date = record[2]
old_record = self.nalanda_session.query(self.Mastery_Level_Student).filter(self.Mastery_Level_Student.student_id_id==student_id\
,self.Mastery_Level_Student.content_id==content_id,self.Mastery_Level_Student.channel_id==channel_id,self.Mastery_Level_Student.date==date)\
.first()
if not old_record:
nalanda_record = self.Mastery_Level_Student(id=str(uuid.uuid4()),student_id_id=student_id,content_id=content_id,\
channel_id=channel_id,date=date,completed=True)
self.nalanda_session.add(nalanda_record)
else:
self.nalanda_session.query(self.Mastery_Level_Student)\
.filter(self.Mastery_Level_Student.student_id_id==student_id,self.Mastery_Level_Student.content_id==content_id,\
self.Mastery_Level_Student.channel_id==channel_id,self.Mastery_Level_Student.date==date)\
.update({'completed':True})
self.nalanda_session.commit()
logging.basicConfig(filename='Transformer.log', level=logging.INFO)
logging.info('The synchronization of topic completion status is completed at' + time.strftime("%c"))
except Exception as e:
logging.basicConfig(filename='Transformer.log', level=logging.ERROR)
logging.error('There is an exception in the Transformer!')
logging.error(e)
logging.error(traceback.format_exc())
raise
def filter_reservations(format_json=None):
"""Show the reservations for a specficied date"""
res_date = request.args.get('date_filter')
biz_id = current_user.business_id
# stash res_date so it can be prepopulated
session['start_date'] = res_date
res = Reservation.query.join(Reservation.service).filter(
Service.business_id == biz_id,
func.date(Reservation.start_date) == res_date).all()
ser = current_user.business.services
if not format_json:
return render_template('reservation.html',
reservations=res,
services=ser)
else:
# coming from the map page and asking for addresses as json
res_dict = []
for r in res:
# need the animal/address info more than the reservation
r_dict = {}
r_dict['address'] = r.animal.people[0].format_address()
r_dict['animal_id'] = r.animal_id
r_dict['animal_name'] = r.animal.name
res_dict.append(r_dict)
return jsonify(res_dict)
def actualizar_ausencias(self, objeto):
fechas = BitacoraManager(self.db).rango_fechas(objeto.fechai,
objeto.fechaf)
for fech in fechas:
fecha_hoy = fech.date()
ajuste = self.db.query(Ajustes).filter(
Ajustes.enabled == True).first()
if ajuste.oracle:
# version oracle
asistencia_personal = self.db.query(Asistencia).filter(
Asistencia.fkpersona == objeto.fkpersona).filter(
func.to_date(Asistencia.fecha).between(
fecha_hoy, fecha_hoy)).all()
else:
# version postgres
asistencia_personal = self.db.query(Asistencia).filter(
Asistencia.fkpersona == objeto.fkpersona).filter(
func.date(Asistencia.fecha) == fecha_hoy).all()
for asistencia in asistencia_personal:
tipoausencia = self.db.query(Tipoausencia).filter(
Tipoausencia.id == objeto.fktipoausencia).first()
asistencia.observacion = tipoausencia.nombre
super().update(asistencia)
return objeto
def refund_settle_product(db, present_date, now, log):
refund_settle = db.execute(
select([
t_trans_list.c.spid, t_trans_list.c.cur_type,
t_trans_list.c.product_type, t_trans_list.c.bank_type,
t_refund_list.c.mode, t_refund_list.c.amount, t_refund_list.c.fee,
t_refund_list.c.id,
t_trans_list.c.id.label('trans_id')
]).select_from(
t_refund_list.join(
t_trans_list,
t_trans_list.c.id == t_refund_list.c.trans_id)).where(
and_(
t_trans_list.c.product_type !=
const.PRODUCT_TYPE.PROMOTION,
t_refund_list.c.settle_time.is_(None),
t_refund_list.c.status == const.REFUND_STATUS.SUCCESS,
t_refund_list.c.channel.in_(
(const.CHANNEL.API, const.CHANNEL.SP_SYSTEM)),
func.date(
t_refund_list.c.bank_settle_time) == present_date,
))).fetchall()
if not refund_settle:
log.info("[no refund list to settle]: present_date=<{}>".format(
present_date))
return [], []
refund_ids = [trans['id'] for trans in refund_settle]
settle_ids = settle_product_insert(db, refund_settle, now, present_date,
const.SETTLE_MODE.REFUND)
return refund_ids, settle_ids
def api_sectors():
sector_totals = db.session.query(
func.sum(models.ActivityFinances.transaction_value).label(
"total_disbursement"), models.CodelistCode.code,
models.CodelistCode.name,
func.strftime(
'%Y',
func.date(models.ActivityFinances.transaction_date,
'start of month',
'-6 month')).label("fiscal_year")).join(
models.Activity, models.ActivityFinancesCodelistCode,
models.CodelistCode).filter(
models.ActivityFinances.transaction_type == u"D",
models.ActivityFinancesCodelistCode.codelist_id
== u"mtef-sector").group_by(
models.CodelistCode.name,
models.CodelistCode.code,
"fiscal_year").all()
return jsonify(sectors=list(
map(
lambda s: {
"name": s.name,
"value": round(s.total_disbursement, 2),
"code": s.code,
"fy": s.fiscal_year
}, sector_totals)))
async def addquote(msg: Message) -> None:
# Permissions for this command are temporarily lowered to voice level.
if not msg.arg:
await msg.reply("Cosa devo salvare?")
return
db = Database.open()
with db.get_session() as session:
result = d.Quotes(
message=msg.arg,
roomid=msg.parametrized_room.roomid,
author=msg.user.userid,
date=func.date(),
)
session.add(result)
session.commit() # type: ignore # sqlalchemy
try:
if result.id:
await msg.reply("Quote salvata.")
if msg.room is None:
await msg.parametrized_room.send_modnote(
"QUOTE ADDED", msg.user, msg.arg)
return
except ObjectDeletedError:
pass
await msg.reply("Quote già esistente.")
def news_data():
adate = request.args.get('date', None)
category = request.args.get('category', None)
sentiment = request.args.get('sentiment', None)
limit = request.args.get('limit', None)
print(f'{adate} {category} {sentiment} {limit}')
results = db.session.query(
sentiment_results.title, sentiment_results.url,
sentiment_results.articleSummary, sentiment_results.source,
sentiment_results.description,
sentiment_results.articleSentiment).filter(
sentiment_results.category == category,
sentiment_results.articleSentiment == sentiment,
func.date(
sentiment_results.publishedAt) == func.date(adate)).limit(
int(limit)).all()
news_data = []
sentences = ""
for result in results:
# print(result[0])
# print(result[4])
news_data.append({
'title': result[0],
'url': result[1],
'summary': result[2],
'source': result[3]
})
sentences = sentences + " " + result[4]
img_str = ""
if len(sentences) > 0:
wordcloud = WordCloud(width=1200,
height=200,
background_color='white',
stopwords=STOPWORDS,
collocations=False,
min_font_size=10).generate(sentences)
image = wordcloud.to_image()
buffered = io.BytesIO()
image.save(buffered, format="JPEG")
img_str = base64.b64encode(buffered.getvalue()).decode("utf-8")
# prepare the response: data
response_data = {"news": news_data, "image": img_str}
return jsonify(response_data)
def daily_base_query():
dt = date_col()
date = func.date(dt)
dm = func.min(date).label("date")
# base query, daily means
query_by_day = select([dm]).group_by(date)
query_by_day = query_by_day.order_by(dt)
return query_by_day, dt
def daily_base_query():
dt = date_col()
date = func.date(dt)
dm = func.min(date).label("date")
# base query, daily means
query_by_day = select([dm]).group_by(date)
query_by_day = query_by_day.order_by(dt)
return query_by_day, dt
def get_num_pledges_datapoints(self):
pledges_per_day = db.session.query(func.date(
Pledge.time_created), func.count(Pledge.time_created)).filter(
Project.id == self.id,
Project.id == Pledge.project_id).group_by(
func.date(Pledge.time_created)).all()
datapoints = [[i + 1, 0] for i in range(self.duration + 2)
] # [(0,0), (1,0), ..., (30,0)]
for p in pledges_per_day:
time_pledged = datetime.datetime.strptime(p[0], "%Y-%m-%d")
day_num = (time_pledged.date() - self.time_start.date()).days
num_pledges = p[1]
datapoints[day_num] = [day_num, num_pledges]
return datapoints
def logPage():
from sqlalchemy.sql.expression import text
monthago = func.subdate(func.now(), text('interval 1 month'))
accessLog = db_session.query(func.date(AccessLog.rdate).label("rdate"), func.count().label("cnt")\
,func.count(func.IF(AccessLog.platform=="web",1,None)).label("webCnt"), func.count(func.IF(AccessLog.platform=="mobile",1,None)).label("mobileCnt") )\
.group_by( func.date(AccessLog.rdate) ).filter(AccessLog.rdate > monthago).all()
infoLog = db_session.query(func.date(User.rdate).label("rdate"), func.count().label("cnt") ).group_by( func.date(User.rdate) ).filter(User.rdate > monthago).all()
resultLog = db_session.query(func.date(Result.rdate).label("rdate"), func.count().label("cnt") ).group_by( func.date(Result.rdate) ).filter(Result.rdate > monthago).all()
weekago = func.subdate(func.now(), text('interval 7 day'))
accessUsers = AccessLog.query.order_by(AccessLog.id.desc()).filter(AccessLog.rdate > weekago).all()
try:
return render_template('log.html', accessLog=accessLog, infoLog=infoLog, resultLog=resultLog, accessUsers=accessUsers)
except TemplateNotFound:
abort(404)
def get(self):
"""
Retrieve daily statistics for tasks.
"""
end_date = date.today() - timedelta(days=30)
results = dict(
db.session.query(func.date(Task.date_started), func.count())
.filter(Task.date_started > end_date)
.group_by(func.date(Task.date_started))
)
points = []
for day in range(31):
point_date = end_date + timedelta(days=day)
points.append((int(point_date.strftime("%s")), results.get(point_date, 0)))
return self.respond(serialize(points), status_code=200)
def query_avg_price_period(session,
fuel_name,
day_from=None,
day_to=None) -> Query:
day_from, day_to = get_period(day_from, day_to)
result = session.query(
func.avg(Price.price).label("average_price"),
func.date(Price.date_of_price).label("date_of_price"), Fuel.fuel_type,
FuelCompany.fuel_company_name).join(Fuel).join(GasStation).join(
FuelCompany, FuelCompany.id == GasStation.fuel_company_id).filter(
Fuel.fuel_type == fuel_name).filter(
func.date(Price.date_of_price).between(
day_from, day_to)).group_by(
Fuel.fuel_type, func.date(Price.date_of_price),
FuelCompany.fuel_company_name).order_by(
func.date(Price.date_of_price).desc(),
FuelCompany.fuel_company_name)
return result
def get_date_subquery(session, days) -> subquery:
days = days_to_date(days)
if days is not None:
subquery_date = session.query(
literal(days).label("date_of_price")).subquery()
else:
subquery_date = session.query(
func.date(func.max(
Price.date_of_price)).label("date_of_price")).subquery()
return subquery_date
class Report(db.Model):
id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.Date,
index=True,
nullable=False,
default=date.today(),
server_default=func.date('now'))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
user = db.relationship('User')
tasks = db.relationship("ReportTask", back_populates="report")
def select_employee_by_period(born_to, born_from):
"""
Select employees who were born in a fixed period.
:param born_to: the start date of a fixed period.
:param born_from: the end date of a fixed period.
:return: the list of employees
"""
session = db.session
employees = session.query(Employee.first_name, Employee.patronymic,
Employee.last_name, Employee.birth_date,
Employee.age, Employee.phone, Employee.position,
Employee.experience, Employee.salary,
Department.name.label('department_name')) \
.select_from(Employee).join(Department,isouter=True) \
.filter(and_(func.date(Employee.birth_date) >= born_from), \
func.date(Employee.birth_date) <= born_to) \
.order_by(Employee.birth_date).all()
session.close()
return employees
def _get_top_users_total_distance_query(self, activity_types: list):
"""
Returns query for top users in the total distance in specified activity types.
:param activity_types: Types of activities we want to sum to total distance.
:returns: Query returning User and total distance.
"""
total_distances = db.session.query(Activity.user_id.label('user_id'),
func.sum(Activity.distance).label('total_distance')). \
filter(func.date(Activity.datetime) >= self.SEASON.start_date,
func.date(Activity.datetime) <= self.SEASON.end_date,
Activity.type.in_(activity_types)). \
group_by(Activity.user_id). \
subquery(with_labels=True)
return db.session.query(User, total_distances.c.total_distance). \
select_from(User). \
join(total_distances, User.id == total_distances.c.user_id). \
filter(User.competing,
User.verified). \
order_by(total_distances.c.total_distance.desc())
def test_update_summary_tables_end_date(self):
"""Test that the summary table task respects a date range."""
ce_table_name = AWS_CUR_TABLE_MAP['cost_entry']
daily_table_name = AWS_CUR_TABLE_MAP['line_item_daily']
summary_table_name = AWS_CUR_TABLE_MAP['line_item_daily_summary']
start_date = datetime.utcnow()
start_date = start_date.replace(day=1, month=(start_date.month - 1)).date()
end_date = start_date + timedelta(days=10)
daily_table = getattr(self.accessor.report_schema, daily_table_name)
summary_table = getattr(self.accessor.report_schema, summary_table_name)
ce_table = getattr(self.accessor.report_schema, ce_table_name)
ce_start_date = self.accessor._session\
.query(func.min(ce_table.interval_start))\
.filter(func.date(ce_table.interval_start) >= start_date).first()[0]
ce_end_date = self.accessor._session\
.query(func.max(ce_table.interval_start))\
.filter(func.date(ce_table.interval_start) <= end_date).first()[0]
# The summary tables will only include dates where there is data
expected_start_date = max(start_date, ce_start_date.date())
expected_end_date = min(end_date, ce_end_date.date())
update_summary_tables('testcustomer', start_date, end_date)
result_start_date, result_end_date = self.accessor._session.query(
func.min(daily_table.usage_start),
func.max(daily_table.usage_start)
).first()
self.assertEqual(result_start_date, expected_start_date)
self.assertEqual(result_end_date, expected_end_date)
result_start_date, result_end_date = self.accessor._session.query(
func.min(summary_table.usage_start),
func.max(summary_table.usage_start)
).first()
self.assertEqual(result_start_date, expected_start_date)
self.assertEqual(result_end_date, expected_end_date)
def get_amount_pledged_datapoints(self):
amount_per_day = db.session.query(
func.date(Pledge.time_created),
func.sum(Pledge.amount)
).filter(
Project.id == self.id,
Project.id == Pledge.project_id
).group_by(
func.date(Pledge.time_created)
).all()
ret = [[i + 1, 0] for i in range(self.duration)]
for p in amount_per_day:
time_pledged = datetime.datetime.strptime(p[0], '%Y-%m-%d')
day_num = (time_pledged.date() - self.time_start.date()).days + 1
amount = p[1]
ret[day_num] = [day_num, amount]
return ret
def get_amount_pledged_datapoints(self):
pledges_per_day = db.session.query(
func.date(Pledge.time_created),
func.sum(Pledge.amount)
).filter(
Project.id==self.id,
Project.id==Pledge.project_id
).group_by(
func.date(Pledge.time_created)
).all()
datapoints = [[i+1,0] for i in range(self.duration + 2)] # [(0,0), (1,0), ..., (30,0)]
for p in pledges_per_day:
time_pledged = datetime.datetime.strptime(p[0], "%Y-%m-%d")
day_num = (time_pledged.date() - self.time_start.date()).days
amount = p[1]
datapoints[day_num] = [day_num, amount]
return datapoints
def get(self):
"""
Retrieve daily statistics for tasks.
"""
end_date = date.today() - timedelta(days=30)
results = dict(db.session.query(
func.date(Task.date_started),
func.count(),
).filter(
Task.date_started > end_date,
).group_by(
func.date(Task.date_started),
))
points = []
for day in range(31):
point_date = end_date + timedelta(days=day)
points.append((
int(point_date.strftime('%s')),
results.get(point_date, 0),
))
return self.respond(serialize(points), status_code=200)
def remind():
today = datetime.date.today()
#today = datetime.date(2017, 8, 31)
day_name = today.strftime("%A")
subquery = ~Checkin.query.filter(User.id==Checkin.user_id, func.date(Checkin.checkin_timestamp)==today).exists()
users = db.session.query(User).filter(subquery).all()
html = render_template('reminderemail.html')
for user in users:
if ((day_name == 'Monday' and user.schedule_monday) or
(day_name == 'Tuesday' and user.schedule_tuesday) or
(day_name == 'Wednesday' and user.schedule_wednesday) or
(day_name == 'Thursday' and user.schedule_thursday) or
(day_name == 'Friday' and user.schedule_friday)):
send_email(user.email, "Please Sign In", html)
return redirect(url_for('admin.home'))
def reconcile_spreads(self):
todays_fills = session.query(Fill).filter(func.date(Fill.dt)== datetime.datetime.now().date()).subquery()
query = session.query(todays_fills, Series.type).join(Contract, and_(todays_fills.c.contractid == Contract.id)).join(Series, and_(Contract.seriesid == Series.id))
fills_df = pandas.read_sql(query.statement, query.session.bind)
spread_fills = fills_df[fills_df.type == 'SPD']
for orderid in spread_fills.orderid.unique():
order_fills = spread_fills[spread_fills.orderid == orderid]
#fills need reconciliation
if len(order_fills) % 3 == 0:
#first remove spread fills
kmeans = KMeans(n_clusters=2, random_state=0).fit(numpy.array([[a] for a in order_fills.trade_px.values]))
order_fills['labels'] = kmeans.labels_
if sum(order_fills['labels'])*1.0 / len(order_fills['labels']) > 0.5:
leg_fills = order_fills[order_fills['labels'] == 1]
else:
leg_fills = order_fills[order_fills['labels'] == 0]
#next consolidate individual legs
long_orders = leg_fills[leg_fills.trade_amt > 0]
short_orders = leg_fills[leg_fills.trade_amt < 0]
final_long_id = long_orders[long_orders.dt == long_orders.dt.max()].id.values[0]
final_short_id = short_orders[short_orders.dt == short_orders.dt.max()].id.values[0]
#delete other fills
ids_to_delete = order_fills[~order_fills.id.isin([final_long_id, final_short_id])].id.astype('int').values
#update remaining fills
spd = Contract.filter(Contract.id == int(order_fills.contractid.iloc[0])).first().spread
long_fill = Fill.filter(Fill.id == int(final_long_id))
short_fill = Fill.filter(Fill.id == int(final_short_id))
long_fill.update({'contractid': spd.long_leg.id})
short_fill.update({'contractid': spd.short_leg.id})
Fill.filter(Fill.id.in_(ids_to_delete)).delete(synchronize_session='fetch')
import pdb; pdb.set_trace()
session.commit()
else:
continue
def show_map():
"""Allow user to view reservations on a map."""
# filter reservations if date chosen
if session.get('start_date'):
res_date = session['start_date']
res = Reservation.query.join(Reservation.service).filter(Service.business_id
== current_user.business_id,
func.date(Reservation.start_date) == res_date).all()
else:
res = Reservation.query.join(Reservation.service).filter(Service.business_id
== current_user.business_id).all()
#then grab the animal each reservation pertains to
animals_list = []
for r in res:
animals_list.append(r.animal)
return render_template('map.html', animals=animals_list, place_id=current_user.business.place_id)
def show_reservations():
"""Show all reservations for a specific business."""
# join using the relationship attribute. gives you access to other table
# filter reservations if date chosen
if session.get('start_date'):
res_date = session['start_date']
res = Reservation.query.join(Reservation.service).filter(Service.business_id
== current_user.business_id,
func.date(Reservation.start_date) == res_date).all()
else:
res = Reservation.query.join(Reservation.service).filter(Service.business_id
== current_user.business_id).all()
ser = current_user.business.services
if res:
session['res_count'] = True
return render_template('reservation.html', reservations=res, services=ser)
def show(airport_name, latitude, longitude, altitude):
"""Show a logbook for <airport_name> located at given position."""
latitude = float(latitude)
longitude = float(longitude)
altitude = float(altitude)
# get_logbook('Königsdorf', 47.83, 11.46, 601)
latmin = latitude - 0.15
latmax = latitude + 0.15
lonmin = longitude - 0.15
lonmax = longitude + 0.15
max_altitude = altitude + 200
# make a query with current, previous and next "takeoff_landing" event, so we can find complete flights
sq = session.query(
TakeoffLanding.address,
func.lag(TakeoffLanding.address)
.over(
order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('address_prev'),
func.lead(TakeoffLanding.address)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('address_next'),
TakeoffLanding.timestamp,
func.lag(TakeoffLanding.timestamp)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('timestamp_prev'),
func.lead(TakeoffLanding.timestamp)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('timestamp_next'),
TakeoffLanding.track,
func.lag(TakeoffLanding.track)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('track_prev'),
func.lead(TakeoffLanding.track)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('track_next'),
TakeoffLanding.is_takeoff,
func.lag(TakeoffLanding.is_takeoff)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('is_takeoff_prev'),
func.lead(TakeoffLanding.is_takeoff)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('is_takeoff_next')) \
.filter(and_(between(TakeoffLanding.latitude, latmin, latmax),
between(TakeoffLanding.longitude, lonmin, lonmax))) \
.filter(TakeoffLanding.altitude < max_altitude) \
.subquery()
# find complete flights (with takeoff and landing) with duration < 1 day
complete_flight_query = session.query(sq.c.timestamp.label('reftime'), sq.c.address.label('address'), sq.c.timestamp.label('takeoff'), sq.c.track.label('takeoff_track'), sq.c.timestamp_next.label('landing'), sq.c.track_next.label('landing_track'), label('duration', sq.c.timestamp_next - sq.c.timestamp)) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.address == sq.c.address_next) \
.filter(sq.c.timestamp_next - sq.c.timestamp < timedelta(days=1))
# split complete flights (with takeoff and landing) with duration > 1 day into one takeoff and one landing
split_start_query = session.query(sq.c.timestamp.label('reftime'), sq.c.address.label('address'), sq.c.timestamp.label('takeoff'), sq.c.track.label('takeoff_track'), null().label('landing'), null().label('landing_track'), null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.address == sq.c.address_next) \
.filter(sq.c.timestamp_next - sq.c.timestamp >= timedelta(days=1))
split_landing_query = session.query(sq.c.timestamp_next.label('reftime'), sq.c.address.label('address'), null().label('takeoff'), null().label('takeoff_track'), sq.c.timestamp_next.label('landing'), sq.c.track_next.label('landing_track'), null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.address == sq.c.address_next) \
.filter(sq.c.timestamp_next - sq.c.timestamp >= timedelta(days=1))
# find landings without start
only_landings_query = session.query(sq.c.timestamp.label('reftime'), sq.c.address.label('address'), null().label('takeoff'), null().label('takeoff_track'), sq.c.timestamp.label('landing'), sq.c.track_next.label('landing_track'), null().label('duration')) \
.filter(sq.c.is_takeoff == false()) \
.filter(or_(sq.c.address != sq.c.address_prev,
sq.c.is_takeoff_prev == false()))
# find starts without landing
only_starts_query = session.query(sq.c.timestamp.label('reftime'), sq.c.address.label('address'), sq.c.timestamp.label('takeoff'), sq.c.track.label('takeoff_track'), null().label('landing'), null().label('landing_track'), null().label('duration')) \
.filter(sq.c.is_takeoff == true()) \
.filter(or_(sq.c.address != sq.c.address_next,
sq.c.is_takeoff_next == true()))
# unite all
union_query = complete_flight_query.union(
split_start_query,
split_landing_query,
only_landings_query,
only_starts_query) \
.subquery()
# get aircraft informations and sort all entries by the reference time
logbook_query = session.query(
union_query.c.reftime,
union_query.c.address,
union_query.c.takeoff,
union_query.c.takeoff_track,
union_query.c.landing,
union_query.c.landing_track,
union_query.c.duration,
Device.registration,
Device.aircraft) \
.outerjoin(Device, union_query.c.address == Device.address) \
.order_by(union_query.c.reftime)
print('--- Logbook ({}) ---'.format(airport_name))
def none_datetime_replacer(datetime_object):
return '--:--:--' if datetime_object is None else datetime_object.time()
def none_track_replacer(track_object):
return '--' if track_object is None else round(track_object / 10.0)
def none_timedelta_replacer(timedelta_object):
return '--:--:--' if timedelta_object is None else timedelta_object
def none_registration_replacer(registration_object, address):
return '[' + address + ']' if registration_object is None else registration_object
def none_aircraft_replacer(aircraft_object):
return '(unknown)' if aircraft_object is None else aircraft_object
for [reftime, address, takeoff, takeoff_track, landing, landing_track, duration, registration, aircraft] in logbook_query.all():
print('%10s %8s (%2s) %8s (%2s) %8s %8s %s' % (
reftime.date(),
none_datetime_replacer(takeoff),
none_track_replacer(takeoff_track),
none_datetime_replacer(landing),
none_track_replacer(landing_track),
none_timedelta_replacer(duration),
none_registration_replacer(registration, address),
none_aircraft_replacer(aircraft)))
def filter_reservations(format_json=None):
"""Show the reservations for a specficied date"""
res_date = request.args.get('date_filter')
biz_id = current_user.business_id
# stash res_date so it can be prepopulated
session['start_date'] = res_date
res = Reservation.query.join(Reservation.service).filter(Service.business_id == biz_id, func.date(Reservation.start_date) == res_date).all()
ser = current_user.business.services
if not format_json:
return render_template('reservation.html', reservations=res, services=ser)
else:
# coming from the map page and asking for addresses as json
res_dict = []
for r in res:
# need the animal/address info more than the reservation
r_dict = {}
r_dict['address'] = r.animal.people[0].format_address()
r_dict['animal_id'] = r.animal_id
r_dict['animal_name'] = r.animal.name
res_dict.append(r_dict)
return jsonify(res_dict)
def compute_takeoff_and_landing():
logger.info("Compute takeoffs and landings.")
takeoff_speed = 30
landing_speed = 30
# calculate the time where the computation starts
last_takeoff_landing_query = app.session.query(func.max(TakeoffLanding.timestamp))
last_takeoff_landing = last_takeoff_landing_query.one()[0]
if last_takeoff_landing is None:
# if the table is empty
last_takeoff_landing = datetime(2015, 1, 1, 0, 0, 0)
else:
# we get the beacons async. to be safe we delete takeoffs/landings from last 5 minutes and recalculate from then
# alternative: takeoff/landing has a primary key (timestamp,address)
last_takeoff_landing = last_takeoff_landing - timedelta(minutes=5)
app.session.query(TakeoffLanding) \
.filter(TakeoffLanding.timestamp > last_takeoff_landing) \
.delete()
# make a query with current, previous and next position, so we can detect takeoffs and landings
sq = app.session.query(
AircraftBeacon.address,
func.lag(AircraftBeacon.address).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('address_prev'),
func.lead(AircraftBeacon.address).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('address_next'),
AircraftBeacon.timestamp,
func.lag(AircraftBeacon.timestamp).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('timestamp_prev'),
func.lead(AircraftBeacon.timestamp).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('timestamp_next'),
AircraftBeacon.latitude,
func.lag(AircraftBeacon.latitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('latitude_prev'),
func.lead(AircraftBeacon.latitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('latitude_next'),
AircraftBeacon.longitude,
func.lag(AircraftBeacon.longitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('longitude_prev'),
func.lead(AircraftBeacon.longitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('longitude_next'),
AircraftBeacon.ground_speed,
AircraftBeacon.track,
func.lag(AircraftBeacon.track).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('track_prev'),
func.lead(AircraftBeacon.track).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('track_next'),
AircraftBeacon.ground_speed,
func.lag(AircraftBeacon.ground_speed).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('ground_speed_prev'),
func.lead(AircraftBeacon.ground_speed).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('ground_speed_next'),
AircraftBeacon.altitude,
func.lag(AircraftBeacon.altitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('altitude_prev'),
func.lead(AircraftBeacon.altitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('altitude_next')) \
.filter(AircraftBeacon.timestamp > last_takeoff_landing) \
.order_by(func.date(AircraftBeacon.timestamp), AircraftBeacon.address, AircraftBeacon.timestamp) \
.subquery()
# find takeoffs and landings (look at the trigger_speed)
takeoff_landing_query = app.session.query(
sq.c.address,
sq.c.timestamp,
sq.c.latitude,
sq.c.longitude,
sq.c.track,
sq.c.ground_speed,
sq.c.altitude,
case([(sq.c.ground_speed > takeoff_speed, True),
(sq.c.ground_speed < landing_speed, False)]).label('is_takeoff')) \
.filter(sq.c.address_prev == sq.c.address == sq.c.address_next) \
.filter(or_(and_(sq.c.ground_speed_prev < takeoff_speed, # takeoff
sq.c.ground_speed > takeoff_speed,
sq.c.ground_speed_next > takeoff_speed),
and_(sq.c.ground_speed_prev > landing_speed, # landing
sq.c.ground_speed < landing_speed,
sq.c.ground_speed_next < landing_speed))) \
.order_by(func.date(sq.c.timestamp), sq.c.timestamp)
# ... and save them
ins = insert(TakeoffLanding).from_select((TakeoffLanding.address, TakeoffLanding.timestamp, TakeoffLanding.latitude, TakeoffLanding.longitude, TakeoffLanding.track, TakeoffLanding.ground_speed, TakeoffLanding.altitude, TakeoffLanding.is_takeoff), takeoff_landing_query)
result = app.session.execute(ins)
counter = result.rowcount
app.session.commit()
logger.debug("New/recalculated takeoffs and landings: {}".format(counter))
return counter
def compute_logbook_entries(session=None):
logger.info("Compute logbook.")
if session is None:
session = app.session
or_args = [between(TakeoffLanding.timestamp, '2016-06-28 00:00:00', '2016-06-28 23:59:59')]
or_args = []
# 'wo' is the window order for the sql window function
wo = and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.device_id,
TakeoffLanding.timestamp,
TakeoffLanding.airport_id)
# make a query with current, previous and next "takeoff_landing" event, so we can find complete flights
sq = session.query(
TakeoffLanding.device_id,
func.lag(TakeoffLanding.device_id).over(order_by=wo).label('device_id_prev'),
func.lead(TakeoffLanding.device_id).over(order_by=wo).label('device_id_next'),
TakeoffLanding.timestamp,
func.lag(TakeoffLanding.timestamp).over(order_by=wo).label('timestamp_prev'),
func.lead(TakeoffLanding.timestamp).over(order_by=wo).label('timestamp_next'),
TakeoffLanding.track,
func.lag(TakeoffLanding.track).over(order_by=wo).label('track_prev'),
func.lead(TakeoffLanding.track).over(order_by=wo).label('track_next'),
TakeoffLanding.is_takeoff,
func.lag(TakeoffLanding.is_takeoff).over(order_by=wo).label('is_takeoff_prev'),
func.lead(TakeoffLanding.is_takeoff).over(order_by=wo).label('is_takeoff_next'),
TakeoffLanding.airport_id,
func.lag(TakeoffLanding.airport_id).over(order_by=wo).label('airport_id_prev'),
func.lead(TakeoffLanding.airport_id).over(order_by=wo).label('airport_id_next')) \
.filter(*or_args) \
.subquery()
# find complete flights (with takeoff and landing on the same day)
complete_flight_query = session.query(
sq.c.timestamp.label('reftime'),
sq.c.device_id.label('device_id'),
sq.c.timestamp.label('takeoff_timestamp'), sq.c.track.label('takeoff_track'), sq.c.airport_id.label('takeoff_airport_id'),
sq.c.timestamp_next.label('landing_timestamp'), sq.c.track_next.label('landing_track'), sq.c.airport_id_next.label('landing_airport_id'),
label('duration', sq.c.timestamp_next - sq.c.timestamp)) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) == func.date(sq.c.timestamp))
# split complete flights (with takeoff and landing on different days) into one takeoff and one landing
split_start_query = session.query(
sq.c.timestamp.label('reftime'),
sq.c.device_id.label('device_id'),
sq.c.timestamp.label('takeoff_timestamp'), sq.c.track.label('takeoff_track'), sq.c.airport_id.label('takeoff_airport_id'),
null().label('landing_timestamp'), null().label('landing_track'), null().label('landing_airport_id'),
null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) != func.date(sq.c.timestamp))
split_landing_query = session.query(
sq.c.timestamp_next.label('reftime'),
sq.c.device_id.label('device_id'),
null().label('takeoff_timestamp'), null().label('takeoff_track'), null().label('takeoff_airport_id'),
sq.c.timestamp_next.label('landing_timestamp'), sq.c.track_next.label('landing_track'), sq.c.airport_id_next.label('landing_airport_id'),
null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) != func.date(sq.c.timestamp))
# find landings without start
only_landings_query = session.query(
sq.c.timestamp.label('reftime'),
sq.c.device_id.label('device_id'),
null().label('takeoff_timestamp'), null().label('takeoff_track'), null().label('takeoff_airport_id'),
sq.c.timestamp.label('landing_timestamp'), sq.c.track.label('landing_track'), sq.c.airport_id.label('landing_airport_id'),
null().label('duration')) \
.filter(sq.c.is_takeoff == false()) \
.filter(or_(sq.c.device_id != sq.c.device_id_prev,
sq.c.is_takeoff_prev == false(),
sq.c.is_takeoff_prev == null()))
# find starts without landing
only_starts_query = session.query(
sq.c.timestamp.label('reftime'),
sq.c.device_id.label('device_id'),
sq.c.timestamp.label('takeoff_timestamp'), sq.c.track.label('takeoff_track'), sq.c.airport_id.label('takeoff_airport_id'),
null().label('landing_timestamp'), null().label('landing_track'), null().label('landing_airport_id'),
null().label('duration')) \
.filter(sq.c.is_takeoff == true()) \
.filter(or_(sq.c.device_id != sq.c.device_id_next,
sq.c.is_takeoff_next == true(),
sq.c.is_takeoff_next == null()))
# unite all computated flights
union_query = complete_flight_query.union(
split_start_query,
split_landing_query,
only_landings_query,
only_starts_query) \
.subquery()
# if a logbook entry exist --> update it
upd = update(Logbook) \
.where(and_(Logbook.device_id == union_query.c.device_id,
union_query.c.takeoff_airport_id != null(),
union_query.c.landing_airport_id != null(),
or_(and_(Logbook.takeoff_airport_id == union_query.c.takeoff_airport_id,
Logbook.takeoff_timestamp == union_query.c.takeoff_timestamp,
Logbook.landing_airport_id == null()),
and_(Logbook.takeoff_airport_id == null(),
Logbook.landing_airport_id == union_query.c.landing_airport_id,
Logbook.landing_timestamp == union_query.c.landing_timestamp)))) \
.values({"takeoff_timestamp": union_query.c.takeoff_timestamp,
"takeoff_track": union_query.c.takeoff_track,
"takeoff_airport_id": union_query.c.takeoff_airport_id,
"landing_timestamp": union_query.c.landing_timestamp,
"landing_track": union_query.c.landing_track,
"landing_airport_id": union_query.c.landing_airport_id,
"duration": union_query.c.duration})
result = session.execute(upd)
update_counter = result.rowcount
session.commit()
logger.debug("Updated logbook entries: {}".format(update_counter))
# if a logbook entry doesnt exist --> insert it
new_logbook_entries = session.query(union_query) \
.filter(~exists().where(
and_(Logbook.device_id == union_query.c.device_id,
or_(and_(Logbook.takeoff_airport_id == union_query.c.takeoff_airport_id,
Logbook.takeoff_timestamp == union_query.c.takeoff_timestamp),
and_(Logbook.takeoff_airport_id == null(),
union_query.c.takeoff_airport_id == null())),
or_(and_(Logbook.landing_airport_id == union_query.c.landing_airport_id,
Logbook.landing_timestamp == union_query.c.landing_timestamp),
and_(Logbook.landing_airport_id == null(),
union_query.c.landing_airport_id == null())))))
ins = insert(Logbook).from_select((Logbook.reftime,
Logbook.device_id,
Logbook.takeoff_timestamp,
Logbook.takeoff_track,
Logbook.takeoff_airport_id,
Logbook.landing_timestamp,
Logbook.landing_track,
Logbook.landing_airport_id,
Logbook.duration),
new_logbook_entries)
result = session.execute(ins)
insert_counter = result.rowcount
session.commit()
logger.debug("New logbook entries: {}".format(insert_counter))
return "{}/{}".format(update_counter, insert_counter)
