def create_token(
rooms: Dict[str, str], expire_minutes: int = 30, admin: Optional[str] = None
) -> str:
token_id = os.urandom(16).hex()
expiry = f"+{expire_minutes} minute"
values = []
if admin is not None:
values.append(
d.Tokens(
token=token_id,
room=None,
rank=admin,
expiry=func.datetime("now", expiry),
)
)
for room in rooms:
values.append(
d.Tokens(
token=token_id,
room=room,
rank=rooms[room],
expiry=func.datetime("now", expiry),
)
)
db = Database.open()
with db.get_session() as session:
session.add_all(values) # type: ignore # sqlalchemy
return token_id
def job_subscription_inner(context: CallbackContext, session):
now = datetime.now()
today = now.date()
later = now + timedelta(minutes=2)
time_guard = now - timedelta(minutes=2)
receivers = Receiver.query.filter_by(is_subscribed=True) \
.filter(func.datetime(now) <= Receiver.aligned_notification_time(today)) \
.filter(func.datetime(later) > Receiver.aligned_notification_time(today)) \
.filter((Receiver.last_messaged == None) | (Receiver.last_messaged < time_guard)) # noqa: E711
receivers = receivers.all()
logger.debug('Query returned %d receivers.', len(receivers))
word = select_word()
message = build_message(word)
for r in receivers:
try:
context.bot.send_message(chat_id=r.chat_id,
text=message,
parse_mode=ParseMode.MARKDOWN)
r.set_messaged(timestamp=now)
except TelegramError:
# TODO: Handle Unauthorized, ChatMigrated.
pass
session.commit()
def last_day(self):
utc = timezone("UTC")
eastern = timezone('US/Eastern')
hours = 30
now = datetime.utcnow()
query = db.session.query(
func.round(func.avg(Reading.temperature)),
func.round(func.avg(Reading.humidity)),
Reading.timestamp
)\
.filter_by(channel=self.channel)\
.filter(Reading.timestamp > func.datetime('now', '-30 hours'))\
.group_by(func.strftime('%d %H', Reading.timestamp))\
.order_by(Reading.timestamp.desc())\
.limit(hours)
readings = query.all()
times = ["" for i in range(hours)]
est = utc.localize(datetime.utcnow()).astimezone(eastern)
for i in range(0, hours):
times[i] = "\"" + (
est - timedelta(hours=i)).strftime("%I%p").lstrip('0') + "\""
times.reverse()
temps = [0 for i in range(hours)]
hums = [0 for i in range(hours)]
for r in readings:
idx = hours - self._hours(now - r[2]) - 1
temps[idx] = r[0] / 10
hums[idx] = r[1]
self.last_day_data = {
"temperatures": temps,
"humidities": hums,
"timestamps": times
}
return self.last_day_data
def get_busiest_airports() -> Response:
"""Get the busiest airport"""
limit = request.args.get("limit", 10)
query = request.args.get("query")
since = int(request.args.get("since", 0))
if query:
result = flights_engine.execute(
union(
select([flights.c.origin]).distinct().where(
flights.c.origin.like(f"%{query}%")),
select([flights.c.destination]).distinct().where(
flights.c.destination.like(f"%{query}%")),
))
if result is None:
abort(404)
return jsonify([row[0] for row in result])
return jsonify([
row.origin for row in flights_engine.execute(
select([flights.c.origin]).where(
func.coalesce(flights.c.actual_off, flights.c.actual_out) > (
select([
func.datetime(func.max(flights.c.actual_off),
text(f"'-{since} hours'"))
]))).group_by(flights.c.origin).order_by(
func.count().desc(), flights.c.origin).limit(limit))
])
class UserSessionToken(db.Model):
__tablename__ = 'users_session'
id = db.Column(db.Integer, primary_key=True)
session = db.Column(db.String, unique=True)
user_id = db.Column(db.ForeignKey('users.id'), unique=True, nullable=False)
created_at = db.Column(db.DateTime, server_default=func.datetime())
def __pick_cti(self, **kwargs):
"""
Picks a single case_tube_idx for each case,
filters on specified criteria, and returns pd.DataFrame of case, case_tube_idx
Notable attributes:
.results -- stores the result of query; datatype specified by <exporttype>
"""
log.info('Looking for case_tube_indx\'s')
# Build query
# ## First case_tube_idx
q1 = self.session.query(TubeCases.case_number,
func.max(func.datetime(TubeCases.date)).label("last_dttm")).\
group_by(TubeCases.case_number)
q1.joined_tables = ['TubeCases']
q1 = add_mods_to_query(q1, **kwargs)
q1 = q1.subquery()
self.q = self.session.query(TubeCases.case_number,
func.max(TubeCases.case_tube_idx).label("case_tube_idx")).\
join(q1, and_(TubeCases.case_number == q1.c.case_number,
TubeCases.date == q1.c.last_dttm)).\
group_by(TubeCases.case_number)
self.q.joined_tables = ['TubeCases']
# Add common filters
self.__add_mods_to_query(**kwargs)
log.debug("\nQuery:\n{}\n".format(self.q.statement))
# Output
df = self.__q2df()
df.sort(['case_number', 'case_tube_idx'], inplace=True)
return df
def airport_enroute(airport: str) -> Response:
"""Get a list of flights enroute to a certain airport"""
airport = airport.upper()
result = flights_engine.execute(flights.select().where(
and_(
flights.c.destination == airport,
flights.c.flight_number != "BLOCKED",
func.coalesce(flights.c.actual_in, flights.c.actual_on,
flights.c.cancelled) == None,
flights.c.estimated_on.between(
select([
func.datetime(func.max(flights.c.actual_off),
text(f"'-5 hours'"))
]),
select([
func.datetime(func.max(flights.c.actual_off),
text(f"'+6 hours'"))
]),
),
)))
if result is None:
abort(404)
return jsonify([dict(e) for e in result])
def airport_scheduled(airport: str) -> Response:
"""Get a list of scheduled flights from a certain airport"""
airport = airport.upper()
result = flights_engine.execute(flights.select().where(
and_(
flights.c.origin == airport,
flights.c.flight_number != "BLOCKED",
or_(
func.coalesce(flights.c.actual_out,
flights.c.actual_off) == None,
# You can actually get true_cancel'ed flights with an actual_out/off. Weird?
flights.c.true_cancel,
),
flights.c.scheduled_off.between(
select([
func.datetime(func.max(flights.c.actual_off),
text(f"'-5 hours'"))
]),
select([
func.datetime(func.max(flights.c.actual_off),
text(f"'+6 hours'"))
]),
),
or_(
flights.c.cancelled == None,
and_(
flights.c.true_cancel,
flights.c.cancelled > (select([
func.datetime(func.max(flights.c.actual_off),
text(f"'-5 hours'"))
])),
),
),
)))
if result is None:
abort(404)
return jsonify([dict(e) for e in result])
def airport_departures(airport: str) -> Response:
"""Get a list of departures for a certain airport"""
airport = airport.upper()
result = flights_engine.execute(flights.select().where(
and_(
flights.c.origin == airport,
flights.c.flight_number != "BLOCKED",
func.coalesce(flights.c.actual_out, flights.c.actual_off) >
(select([
func.datetime(func.max(flights.c.actual_off),
text(f"'-5 hours'"))
])),
)))
if result is None:
abort(404)
return jsonify([dict(e) for e in result])
class EventLog(db.Model):
id = db.Column(db.Integer, primary_key=True, nullable=False)
user = db.relationship('User')
order_id = db.Column(db.Integer, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
timestamp = db.Column(db.DateTime,
nullable=False,
default=datetime.now(tz=timezone.utc),
server_default=func.datetime('now'))
type = db.Column(db.Enum(EventType),
nullable=False,
default=EventType.comment)
data = db.Column(db.String(),
nullable=False,
default='',
server_default='')
class ApiData(db.Model):
id = db.Column(db.Integer, primary_key=True, nullable=False)
timestamp = db.Column(db.DateTime,
nullable=False,
default=datetime.now(tz=timezone.utc),
server_default=func.datetime('now'))
ecwid_id = db.Column(db.Integer,
db.ForeignKey('ecwid.id'),
nullable=False,
index=True,
unique=True)
notify_1C = db.Column(db.Boolean,
nullable=False,
default=True,
server_default=expression.true())
email_1C = db.Column(db.String(128), nullable=True)
hub = db.relationship('Ecwid')
def aligned_notification_time(cls, date):
hour = extract('hour', cls.notification_time)
minute = extract('minute', cls.notification_time)
add_hours = func.printf('+%d hours', hour)
add_minutes = func.printf('+%d minutes', minute)
return func.datetime(date, add_hours, add_minutes)
class User(db.Model):
"""
Table contain all authorized users by their phone numbers
"""
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
phone = db.Column(db.String, unique=True)
created_at = db.Column(db.DateTime, server_default=func.datetime())
def __repr__(self):
return f'({self.id}, {self.phone})'
@staticmethod
def get_by_phone(phone: str) -> 'User' or None:
"""
Find registered user by phone number
:param phone: user phone number
:return: user model
"""
return User.query.filter(User.phone == phone).first()
@staticmethod
def get_by_session(session_token: str):
return User.query\
.join(UserSessionToken)\
.filter(UserSessionToken.session == session_token)\
.first()
@classmethod
def login(cls, phone: str) -> 'UserConfirmationToken':
"""
Start new authenticating process - delete current active sessions
and create new confirmation token
:param phone: user registration phone number
:return: confirmation token
"""
user = cls.get_by_phone(phone)
if not user:
# create user if not exists with same number
user = User(phone=phone)
db.session.add(user)
db.session.commit()
# find all active sessions and delete them
UserSessionToken.query\
.filter(UserSessionToken.user_id == user.id)\
.delete()
db.session.commit()
# create new confirmation token
new_token = UserConfirmationToken(user_id=user.id,
token=str(uuid.uuid4()))
db.session.add(new_token)
db.session.commit()
return new_token
@classmethod
def confirm_auth(cls, phone: str, confirmation_token: str) -> \
('UserSessionToken', 'User') or None:
"""
Create new session token if combination from token and phone is ok
:param phone: user phone number
:param confirmation_token:
:return: new session token or nothing
"""
# find confirmation token
user = cls.get_by_phone(phone)
token_model = UserConfirmationToken.query\
.filter(UserConfirmationToken.token == confirmation_token)\
.filter(UserConfirmationToken.user_id == user.id)\
.first()
# return nothing if token does not exists
if not token_model:
return
# and create new user session when token was found
new_session = UserSessionToken(user_id=user.id,
session=str(uuid.uuid4()))
db.session.add(new_session)
# delete confirmation token
db.session.delete(token_model)
db.session.commit()
return new_session, user
@property
def serialized(self):
return dict(id=self.id, phone=self.phone)