Beispiel #1
0
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,
            ))
Beispiel #2
0
 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'"))
Beispiel #4
0
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)))
Beispiel #5
0
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)))
Beispiel #6
0
        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))
Beispiel #7
0
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)))
Beispiel #8
0
        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))
Beispiel #9
0
 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)
Beispiel #11
0
    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
Beispiel #12
0
    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)
Beispiel #13
0
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()))
Beispiel #14
0
        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)
Beispiel #15
0
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
Beispiel #16
0
    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
Beispiel #17
0
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)
Beispiel #18
0
    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
Beispiel #19
0
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
Beispiel #20
0
    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()
Beispiel #21
0
    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()
Beispiel #22
0
    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()
Beispiel #23
0
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
Beispiel #24
0
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
Beispiel #25
0
 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)
Beispiel #26
0
    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
Beispiel #27
0
def now_utc():
    return func.timezone("utc", func.now())
Beispiel #28
0
 def test_compare_current_timestamp_fn_w_binds(self):
     self._compare_default_roundtrip(
         DateTime(), func.timezone("utc", func.current_timestamp())
     )
Beispiel #29
0
def in_jst(timestamp: datetime) -> Function:
    return func.timezone('Asia/Tokyo', timestamp)
Beispiel #30
0
def in_utc(timestamp: datetime) -> Function:
    return func.timezone('UTC', timestamp)
Beispiel #31
0
 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)
Beispiel #32
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())