def build_single_movement_query(dbsession, owner, period):
"""Build a query that lists the unreconciled movements in open periods.
Return a query providing these columns:
- transfer_id
- date
- delta
- movement_ids
"""
movement_date_c = func.date(
func.timezone(get_tzname(owner), func.timezone('UTC',
FileMovement.ts)))
return (dbsession.query(
TransferRecord.transfer_id,
movement_date_c.label('date'),
file_movement_delta.label('delta'),
array([FileMovement.movement_id]).label('movement_ids'),
).select_from(FileMovement).join(
TransferRecord,
TransferRecord.id == FileMovement.transfer_record_id).join(
Period, Period.id == FileMovement.period_id).filter(
FileMovement.owner_id == owner.id,
FileMovement.file_id == period.file_id,
FileMovement.reco_id == null,
file_movement_delta != 0,
~Period.closed,
))
def __init__(self, owner):
self.table = FileMovement
self.date_c = func.date(
func.timezone(get_tzname(owner),
func.timezone('UTC', FileMovement.ts)))
self.id_c = FileMovement.movement_id
self.plural = 'movements'
def _get_printing_day(created_at):
return func.date_trunc(
'day',
func.timezone('Europe/London', func.timezone('UTC', created_at)) +
text(
# We add 6 hours 30 minutes to the local created_at time so that
# any letters created after 5:30pm get shifted into the next day
"interval '6 hours 30 minutes'"))
def get_sydney_month_from_utc_column(column):
"""
Where queries need to count notifications by month it needs to be
the month in AET.
The database stores all timestamps as UTC without the timezone.
- First set the timezone on created_at to UTC
- then convert the timezone to AET
- lastly truncate the datetime to month with which we can group
queries
"""
return func.date_trunc(
"month", func.timezone(aet_tz_str, func.timezone("UTC", column)))
def get_london_month_from_utc_column(column):
"""
Where queries need to count notifications by month it needs to be
the month in BST (British Summer Time).
The database stores all timestamps as UTC without the timezone.
- First set the timezone on created_at to UTC
- then convert the timezone to BST (or Europe/London)
- lastly truncate the datetime to month with which we can group
queries
"""
return func.date_trunc(
"month", func.timezone("Europe/London", func.timezone("UTC", column)))
def astimezone(self, tz):
"""Convert the datetime to a specific timezone.
This is useful if you want e.g. to cast to Date afterwards
but need a specific timezone instead of UTC.
When accessing the value returned by this method in Python
it will be a naive datetime object in the specified time
zone.
:param tz: A timezone name or tzinfo object.
"""
tz = getattr(tz, 'zone', tz)
return func.timezone(tz, func.timezone('UTC', self.expr))
def get_local_timezone_month_from_utc_column(column):
"""
Where queries need to count notifications by month it needs to be
the month in BST (British Summer Time).
The database stores all timestamps as UTC without the timezone.
- First set the timezone on created_at to UTC
- then convert the timezone to BST (or America/Toronto)
- lastly truncate the datetime to month with which we can group
queries
"""
return func.date_trunc(
"month",
func.timezone(os.getenv("TIMEZONE", "America/Toronto"),
func.timezone("UTC", column)))
def astimezone(self, tz):
"""Convert the datetime to a specific timezone.
This is useful if you want e.g. to cast to Date afterwards
but need a specific timezone instead of UTC.
When accessing the value returned by this method in Python
it will be a naive datetime object in the specified time
zone.
:param tz: A timezone name or tzinfo object.
"""
tz = getattr(tz, 'zone', tz)
return func.timezone(tz, func.timezone('UTC', self.expr))
def find_appointment_availability(cls, office_id: int, timezone: str,
first_date: datetime,
last_date: datetime):
"""Find appointment availability for dates in a month"""
query = db.session.query(Appointment).filter(
func.date_trunc(
'day',
func.timezone(timezone, Appointment.start_time)).between(
func.date_trunc('day', func.timezone(timezone,
first_date)),
func.date_trunc('day', func.timezone(timezone,
last_date))))
query = query.filter(Appointment.office_id == office_id)
query = query.order_by(Appointment.start_time.asc())
return query.all()
def get_chart_data():
d = cast(func.timezone('-04:00', PickupRecord.date_submitted), DATE).label('d')
counts = db.session.query(func.count('*'), PickupRecord.source, d).select_from(PickupRecord).group_by(d, PickupRecord.source).all()
data = {}
sources = set()
for row in counts:
date = str(row[2])
source = row[1]
count = row[0]
if date not in data:
data[date] = {}
data[date][source] = count
sources.add(source)
options = dict()
options['xAxis'] = sorted(data.keys())
options['series'] = []
for source in sources:
counts = []
for date in options['xAxis']:
if source in data[date]:
counts.append(data[date][source])
else:
counts.append(0)
options['series'].append({'name': source, 'data': counts})
return jsonify(options)
def get_day_entries(user_id, day=None, field_id=None):
day, tz = User.timezoned(date=day, user_id=user_id)
timezoned = func.Date(func.timezone(tz, FieldEntry.created_at))
q = db.session.query(FieldEntry)\
.filter(FieldEntry.user_id == user_id, timezoned == day.date())
if field_id:
q = q.filter(FieldEntry.field_id == field_id)
return q
def get_stats(self) -> typing.Dict[str, typing.Dict[str, int]]:
cutoff = clock.now() - datetime.timedelta(days=30)
metrics = [m.name for m in EventType]
metrics.append("NEW_USERS")
keys = sorted(metrics)
stats: typing.Dict[str, typing.Dict[str, int]] = collections.defaultdict(
lambda: {name: 0 for name in keys}
)
totals = {name: 0 for name in keys}
for date, type_code, count in (
self.filter(Event.timestamp > cutoff)
.with_entities(
func.DATE(func.timezone("PST", Event.timestamp)).label("date"),
Event.type_code,
func.count(Event.id),
)
.group_by("date", Event.type_code)
.order_by(desc("date"))
.all()
):
event_date = date.strftime("%Y-%m-%d")
event_type = EventType(type_code)
stats[event_date][event_type.name] = count
totals[event_type.name] += count
# Ew.
from airq.models.clients import Client
for date, count in (
Client.query.filter_phones()
.filter(Client.created_at > cutoff)
.with_entities(
func.DATE(func.timezone("PST", Client.created_at)).label("date"),
func.count(Client.id),
)
.group_by("date")
.order_by(desc("date"))
.all()
):
join_date = date.strftime("%Y-%m-%d")
stats[join_date]["NEW_USERS"] = count
stats["TOTAL"] = totals
return dict(stats)
class RecoverySession(db.Model):
"""
A recovery session is created once a user enters their username and
completes the reCaptcha. It is used to track Reset Tokens and Phone
Verification numbers per session.
"""
__tablename__ = "session"
id = Column(String(36), primary_key=True)
username = Column(String(64), nullable=False)
created = Column(DateTime, default=func.timezone('UTC', func.current_timestamp()))
def date(self, tz=None):
tz = get_timezone(tz)
if str(db.engine.url).startswith('sqlite'):
minutes_offset = round(
tz.utcoffset(datetime.datetime.now()).total_seconds() / 60)
return func.date(self.expr, f'{minutes_offset} minutes')
else:
tzname = tz.tzname(datetime.datetime.now())
datetime_at_tz = func.timezone(tzname, self.expr)
return func.DATE(datetime_at_tz)
def authored_month_counts_q(session):
s = session
# Careful with the datetime-truncation here - ensure we're working in UTC
# before we bin by month!
month_counts_qry = s.query(
func.date_trunc('month',
func.timezone('UTC',Voevent.author_datetime)
).distinct().label('month_id'),
(func.count(Voevent.ivorn)).label('month_count'),
).select_from(Voevent).group_by('month_id')
return month_counts_qry
def list_bundle_records(self):
"""List bundle transfers that contain unreconciled bundled movements.
"""
dbsession = self.dbsession
owner = self.owner
period = self.period
# bundle_transfer_ids_cte lists the bundle transfer IDs
# of the unreconciled bundled movements for this file.
bundle_transfer_ids_cte = (dbsession.query(
TransferRecord.bundle_transfer_id).select_from(FileMovement).join(
TransferRecord,
TransferRecord.id == FileMovement.transfer_record_id).join(
Period, Period.id == FileMovement.period_id).filter(
FileMovement.owner_id == owner.id,
FileMovement.file_id == period.file_id,
FileMovement.reco_id == null,
file_movement_delta != 0,
TransferRecord.bundle_transfer_id != null,
~Period.closed,
).distinct().cte('bundle_transfer_ids_cte'))
record_date_c = func.date(
func.timezone(get_tzname(owner),
func.timezone('UTC', TransferRecord.start)))
bundle_records = (dbsession.query(
TransferRecord.transfer_id,
TransferRecord.bundled_transfers,
record_date_c.label('date'),
).filter(
TransferRecord.owner_id == owner.id,
TransferRecord.transfer_id.in_(bundle_transfer_ids_cte),
TransferRecord.bundled_transfers != null,
func.jsonb_array_length(TransferRecord.bundled_transfers) > 0,
).order_by(TransferRecord.start, TransferRecord.transfer_id).all())
log.info("BundleFinder: %s unreconciled bundle(s) for period %s",
len(bundle_records), period.id)
return bundle_records
class ResetToken(db.Model):
"""
Reset tokens are generated once an identity has been verified. They allow
the user access to the reset page.
"""
__tablename__ = "token"
id = Column(Integer, primary_key=True)
username = Column(String(64), nullable=False)
created = Column(DateTime, default=func.timezone('UTC', func.current_timestamp()))
token = Column(String(36))
session = Column(String(36), ForeignKey("session.id"))
used = Column(Boolean)
def get_day_entries(user_id, day=None, field_id=None):
tz_ = db.session.query(User.timezone).filter_by(id=user_id).scalar()
tz_ = tz_ or 'America/Los_Angeles'
timezoned = func.Date(func.timezone(tz_, FieldEntry.created_at))
day = day.astimezone(tz.gettz(tz_)) if day else nowtz(tz_)
q = db.session.query(FieldEntry)\
.filter(FieldEntry.user_id == user_id, timezoned == day.date())
if field_id:
q = q.filter(FieldEntry.field_id == field_id)
return q
class Apartment(BaseModel):
__tablename__ = 'apartment'
def __init__(self, **data):
for key, value in data.items():
setattr(self, key, value)
id = Column(Integer, primary_key=True, autoincrement=True)
created_at = Column(DateTime,
server_default=func.timezone('UTC',
func.current_timestamp()))
touched_at = Column(DateTime,
server_default=func.timezone('UTC',
func.current_timestamp()),
onupdate=func.timezone('UTC',
func.current_timestamp()))
date_listed = Column(DateTime)
url = Column(String, unique=True, nullable=False)
title = Column(String)
address = Column(String)
price = Column(Numeric)
price_currency = Column(String, default='CAD')
pet_friendly = Column(Boolean)
bedrooms = Column(Integer)
bathrooms = Column(Integer)
furnished = Column(Boolean)
latitude = Column(Float)
longitude = Column(Float)
lat_deg = Column(Float)
lng_deg = Column(Float)
geocode_source = Column(String)
def json(self, degrees=False):
j = {}
for key in self._sa_class_manager.keys():
val = getattr(self, key)
if key in ('latitude', 'longitude') and degrees:
val = to_degrees(val)
j[key] = val
return j
def find_next_day_appointments(cls):
"""Find next day appointments."""
from app.models.theq import Office, PublicUser, Citizen, Timezone
tomorrow = current_pacific_time() + timedelta(days=1)
query = db.session.query(Appointment, Office, Timezone, PublicUser). \
join(Citizen, Citizen.citizen_id == Appointment.citizen_id). \
join(Office, Office.office_id == Appointment.office_id). \
join(Timezone, Timezone.timezone_id == Office.timezone_id). \
outerjoin(PublicUser, PublicUser.user_id == Citizen.user_id). \
filter(func.date_trunc('day',
func.timezone(Timezone.timezone_name, Appointment.start_time)) ==
tomorrow.strftime("%Y-%m-%d 00:00:00"))
return query.all()
def find_next_day_appointments(cls):
"""Find next day appointments."""
from app.models.theq import Office, PublicUser, Citizen, Timezone
tomorrow = datetime.now() + timedelta(days=1)
tomorrow = tomorrow.astimezone(tz.tzlocal())
query = db.session.query(Appointment, Office, Timezone, PublicUser). \
join(Citizen, Citizen.citizen_id == Appointment.citizen_id). \
join(Office, Office.office_id == Appointment.office_id). \
join(Timezone, Timezone.timezone_id == Office.timezone_id). \
outerjoin(PublicUser, PublicUser.user_id == Citizen.user_id). \
filter(func.date_trunc('day',
func.timezone(Timezone.timezone_name,Appointment.start_time)) ==
func.date_trunc('day', tomorrow))
return query.all()
def find_by_username_and_office_id(cls, office_id: int, user_name: str, start_time, timezone, appointment_id=None):
"""Find apponintment for the user at an office for a date."""
from app.models.theq import PublicUser, Citizen
start_datetime = parse(start_time)
query = db.session.query(Appointment). \
join(Citizen). \
join(PublicUser). \
filter(Appointment.citizen_id == Citizen.citizen_id). \
filter(Citizen.user_id == PublicUser.user_id). \
filter(func.date_trunc('day', func.timezone(timezone, Appointment.start_time)) == (func.date_trunc('day', func.timezone(timezone, start_datetime)))). \
filter(Appointment.office_id == office_id). \
filter(PublicUser.username == user_name). \
filter(Appointment.checked_in_time.is_(None))
if appointment_id:
query = query.filter(Appointment.appointment_id != appointment_id)
return query.all()
def message_totals(dbsession, user):
"Message totals query"
query = dbsession.query(
func.date(func.timezone(user.timezone, Message.timestamp))
.label('ldate'),
func.count('ldate').label('mail_total'),
func.sum(case([(Message.virusinfected > 0, 1)], else_=0))
.label('virus_total'),
func.sum(case([(and_(Message.virusinfected == 0,
Message.spam > 0), 1)],
else_=0)).label('spam_total'),
func.sum(Message.size).label('total_size'))\
.group_by('ldate')\
.order_by(desc('ldate'))
uquery = UserFilter(dbsession, user, query)
query = uquery.filter()
return query
def message_totals(dbsession, user):
"Message totals query"
query = dbsession.query(
func.date(func.timezone(user.timezone, Message.timestamp))
.label('ldate'),
func.count('ldate').label('mail_total'),
func.sum(case([(Message.virusinfected > 0, 1)], else_=0))
.label('virus_total'),
func.sum(case([(and_(Message.virusinfected == 0,
Message.spam > 0), 1)],
else_=0)).label('spam_total'),
func.sum(Message.size).label('total_size'))\
.group_by('ldate')\
.order_by(desc('ldate'))
uquery = UserFilter(dbsession, user, query)
query = uquery.filter()
return query
def get_stats(self) -> typing.Dict[str, typing.Dict[str, int]]:
keys = sorted(m.name for m in EventType)
stats: typing.Dict[str,
typing.Dict[str, int]] = collections.defaultdict(
lambda: {name: 0
for name in keys})
totals = {name: 0 for name in keys}
for date, type_code, count in (self.filter(
Event.timestamp > clock.now() -
datetime.timedelta(days=30)).with_entities(
func.DATE(func.timezone("PST",
Event.timestamp)).label("date"),
Event.type_code,
func.count(Event.id),
).group_by("date",
Event.type_code).order_by(desc("date")).all()):
send_date = date.strftime("%Y-%m-%d")
event_type = EventType(type_code)
stats[send_date][event_type.name] = count
totals[event_type.name] += count
stats["TOTAL"] = totals
return dict(stats)
def search_purchase_history(cls, # pylint:disable=too-many-arguments, too-many-locals, too-many-branches
auth_account_id: str, search_filter: Dict,
page: int, limit: int, return_all: bool, max_no_records: int = 0):
"""Search for purchase history."""
# Payment Account Sub Query
payment_account_sub_query = db.session.query(PaymentAccount).filter(
PaymentAccount.auth_account_id == auth_account_id).subquery('pay_accnt')
query = db.session.query(Payment, Invoice) \
.join(Invoice) \
.outerjoin(CreditPaymentAccount) \
.outerjoin(BcolPaymentAccount) \
.outerjoin(InternalPaymentAccount) \
.filter(or_(InternalPaymentAccount.account_id == payment_account_sub_query.c.id,
BcolPaymentAccount.account_id == payment_account_sub_query.c.id,
CreditPaymentAccount.account_id == payment_account_sub_query.c.id))
if search_filter.get('status', None):
query = query.filter(Payment.payment_status_code == search_filter.get('status'))
if search_filter.get('folioNumber', None):
query = query.filter(Invoice.folio_number == search_filter.get('folioNumber'))
if search_filter.get('businessIdentifier', None):
query = query.filter(Invoice.business_identifier == search_filter.get('businessIdentifier'))
if search_filter.get('createdBy', None): # pylint: disable=no-member
query = query.filter(
Payment.created_name.like('%' + search_filter.get('createdBy') + '%')) # pylint: disable=no-member
# Find start and end dates
created_from: datetime = None
created_to: datetime = None
if get_str_by_path(search_filter, 'dateFilter/startDate'):
created_from = datetime.strptime(get_str_by_path(search_filter, 'dateFilter/startDate'), '%m/%d/%Y')
if get_str_by_path(search_filter, 'dateFilter/endDate'):
created_to = datetime.strptime(get_str_by_path(search_filter, 'dateFilter/endDate'), '%m/%d/%Y')
if get_str_by_path(search_filter, 'weekFilter/index'):
created_from, created_to = get_week_start_and_end_date(
int(get_str_by_path(search_filter, 'weekFilter/index')))
if get_str_by_path(search_filter, 'monthFilter/month') and get_str_by_path(search_filter, 'monthFilter/year'):
month = int(get_str_by_path(search_filter, 'monthFilter/month'))
year = int(get_str_by_path(search_filter, 'monthFilter/year'))
created_from, created_to = get_first_and_last_dates_of_month(month=month, year=year)
if created_from and created_to:
# Truncate time for from date and add max time for to date
tz_name = current_app.config['LEGISLATIVE_TIMEZONE']
tz_local = pytz.timezone(tz_name)
created_from = created_from.replace(hour=0, minute=0, second=0, microsecond=0).astimezone(tz_local)
created_to = created_to.replace(hour=23, minute=59, second=59, microsecond=999999).astimezone(tz_local)
query = query.filter(
func.timezone(tz_name, func.timezone('UTC', Payment.created_on)).between(created_from, created_to))
# Add ordering
query = query.order_by(Payment.created_on.desc())
if not return_all:
# Add pagination
pagination = query.paginate(per_page=limit, page=page)
result, count = pagination.items, pagination.total
# If maximum number of records is provided, return it as total
if max_no_records > 0:
count = max_no_records if max_no_records < count else count
else:
# If maximum number of records is provided, set the page with that number
if max_no_records > 0:
pagination = query.paginate(per_page=max_no_records, page=1)
result, count = pagination.items, max_no_records
else:
result = query.all()
count = len(result)
return result, count
def now_utc():
return func.timezone("utc", func.now())
def test_compare_current_timestamp_fn_w_binds(self):
self._compare_default_roundtrip(
DateTime(), func.timezone("utc", func.current_timestamp())
)
def in_jst(timestamp: datetime) -> Function:
return func.timezone('Asia/Tokyo', timestamp)
def in_utc(timestamp: datetime) -> Function:
return func.timezone('UTC', timestamp)
def get_total_new(self) -> int:
"""Number of new clients in the last day"""
return (self.filter_phones().filter(
func.timezone("PST", Client.created_at) > now().date()).
with_entities(func.count(Client.id)).scalar() or 0)
def db_now_utc():
app.config.from_object('config.default')
database.init_app(app)
q = database.session.query(func.timezone('utc', func.now()))
print(q.scalar())