class Role(db.Model):
id = db.Column(mysql.INTEGER(50), primary_key=True)
name = db.Column(db.String(120), unique=True, nullable=False)
users = db.relationship("User")
create_player = db.Column(db.Boolean, unique=False, nullable=False)
modify_player = db.Column(db.Boolean, unique=False, nullable=False)
delete_player = db.Column(db.Boolean, unique=False, nullable=False)
create_rep = db.Column(db.Boolean, unique=False, nullable=False)
modify_rep = db.Column(db.Boolean, unique=False, nullable=False)
delete_rep = db.Column(db.Boolean, unique=False, nullable=False)
toggle_market = db.Column(db.Boolean, unique=False, nullable=False)
give_points = db.Column(db.Boolean, unique=False, nullable=False)
create_game = db.Column(db.Boolean, unique=False, nullable=False)
update_game = db.Column(db.Boolean, unique=False, nullable=False)
delete_game = db.Column(db.Boolean, unique=False, nullable=False)
admin_panel = db.Column(db.Boolean, unique=False, nullable=False)
def __repr__(self):
return 'id:%r name:%r' % (self.id, self.name)
def toJSON(self):
permissions = [
'create_player', 'modify_player', 'delete_player', 'create_rep',
'modify_rep', 'delete_rep', 'toggle_market', 'give_points',
'create_game', 'update_game', 'delete_game', 'admin_panel'
]
has_permissions = []
for p in permissions:
if getattr(self, p):
has_permissions.append(p)
return {
'id': self.id,
'name': self.name,
'permissions': has_permissions
}
class Availability(db.Model):
__tablename__ = "availabilities"
facility_id = db.Column(db.Integer,
db.ForeignKey('facilities.id', ondelete="CASCADE"),
nullable=False,
primary_key=True)
mondayStart = db.Column(db.Integer)
mondayEnd = db.Column(db.Integer)
tuesdayStart = db.Column(db.Integer)
tuesdayEnd = db.Column(db.Integer)
wednesdayStart = db.Column(db.Integer)
wednesdayEnd = db.Column(db.Integer)
thursdayStart = db.Column(db.Integer)
thursdayEnd = db.Column(db.Integer)
fridayStart = db.Column(db.Integer)
fridayEnd = db.Column(db.Integer)
saturdayStart = db.Column(db.Integer)
saturdayEnd = db.Column(db.Integer)
sundayStart = db.Column(db.Integer)
sundayEnd = db.Column(db.Integer)
class deploys(JsonSerializer,db.Model):
id = db.Column(db.BIGINT,primary_key=True)
project_id = db.Column(db.BIGINT)
host_id = db.Column(db.BIGINT)
branch = db.Column(db.VARCHAR)
class Profiles(db.Model):
__tablename__ = 'profiles'
profileid = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(), nullable=False)
fname = db.Column(db.String(), nullable=False)
lname = db.Column(db.String(), nullable=False)
account_active = db.Column(db.Boolean(), default=True)
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
profile_image = db.relationship("ProfileImage",
backref="profile",
uselist=False)
post = db.relationship("Post", backref="profile", lazy="dynamic")
github = db.Column(db.String(), nullable=False)
front_end = db.Column(db.Boolean(), nullable=False)
back_end = db.Column(db.Boolean(), nullable=False)
full_stack = db.Column(db.Boolean(), nullable=False)
messages = db.relationship("Messages", backref="profile")
post = db.relationship("Post", cascade="all, delete")
admin = db.Column(db.Boolean(), default=False)
class LevelDB(base):
__tablename__ = 'LevelDB'
id = db.Column(db.Integer, primary_key=True)
data = db.Column(db.String(500), nullable=False)
transliter = db.Column(db.String(500), nullable=False)
class Students(db.Model):
"""学生表模型类"""
__tablename__ = "students"
id = db.Column(INTEGER, primary_key=True) # id主键
sno = db.Column(INTEGER, unique=True, nullable=False) # 学号
sname = db.Column(VARCHAR(20), nullable=False) # 姓名
ssex = db.Column(TINYINT(1), nullable=False) # 性别
endate = db.Column(DATE, nullable=False) # 入学日期
nativeplace = db.Column(VARCHAR(100), nullable=False) # 籍贯
birthday = db.Column(DATE, nullable=False) # 生日
password = db.Column(CHAR(32), nullable=False) # 密码
tel = db.Column(CHAR(11), nullable=False) # 电话
degree = db.Column(VARCHAR(20), nullable=False) # 学位信息
pid = db.Column(VARCHAR(30), nullable=False) # 身份证号
lid = db.Column(INTEGER, db.ForeignKey("levels.id")) # 年级id
caid = db.Column(INTEGER, db.ForeignKey("campuses.id")) # 校区id
collid = db.Column(INTEGER, db.ForeignKey("colleges.id")) # 学院id
clid = db.Column(INTEGER, db.ForeignKey("classes.id")) # 班级id
selcourses = db.relationship("Selcourses", backref="student")
achievements = db.relationship("Achievements", backref="student")
def set_pwd(self, pwd):
self.password = Encryption.md5(pwd)
def set_pid(self, pid):
self.pid = Encryption.base64_encode(pid)
def __repr__(self):
return str(self.sno) + ":" + self.sname
class oper_log(JsonSerializer, db.Model):
id = db.Column(db.BIGINT, primary_key=True)
deploy_id = db.Column(db.BIGINT)
user_id = db.Column(db.BIGINT)
username = db.Column(db.VARCHAR)
comment = db.Column(db.VARCHAR)
self.picture = picture
self.fname = fname
self.lname = lname
self.bio = bio
self.username = username
self.email = email
self.password = password
def __repr__(self):
return f"User('{self.username}', '{self.email}', '{self.bio}')"
company_comment = db.Table(
'company_comment',
db.Column('company_id',
db.Integer,
db.ForeignKey('company.id'),
primary_key=True),
db.Column('comment_id',
db.Integer,
db.ForeignKey('comment.id'),
primary_key=True))
class Company(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
picture = db.Column(db.String(100))
name = db.Column(db.String(100))
bio = db.Column(db.Text())
specialization = db.Column(db.String(100))
username = db.Column(db.String(10))
email = db.Column(db.String(50), nullable=False, unique=True)
class Ticket(db.Model):
__tablename__ = 'ticket'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
type_id = db.Column(db.Integer,
db.ForeignKey('ticket_type.id'),
nullable=False,
index=True)
paid = db.Column(db.Boolean, default=False, nullable=False, index=True)
# Until a ticket is paid for, we track the payment's expiry
expires = db.Column(db.DateTime, nullable=False)
expired = column_property(and_(expires < func.now(),
paid == False)) # noqa
emailed = db.Column(db.Boolean, default=False, nullable=False)
transfer_reminder_sent = db.Column(db.Boolean,
default=False,
nullable=False)
payment_id = db.Column(db.Integer, db.ForeignKey('payment.id'))
refund_id = db.Column(db.Integer, db.ForeignKey('refund.id'))
attribs = db.relationship("TicketAttrib", backref="ticket", cascade='all')
transfers = db.relationship('TicketTransfer', backref='ticket')
checkin = db.relationship('TicketCheckin',
uselist=False,
backref='ticket',
cascade='all')
type = db.relationship('TicketType', backref='tickets')
def __init__(self, user_id, type=None, type_id=None):
if type:
self.type = type
self.type_id = type.id
elif type_id is not None:
self.type_id = type_id
self.type = TicketType.query.get(type_id)
if not self.type:
raise ValueError('Ticket type not found')
else:
raise ValueError('Type must be specified')
self.user_id = user_id
self.expires = datetime.utcnow() + timedelta(hours=2)
self.ignore_capacity = False
def clone(self, new_code=None, ignore_capacity=False):
if new_code is not None:
raise NotImplementedError('Changing codes not yet supported')
other = Ticket(type=self.type)
for attrib in self.attribs:
other.attribs.append(TicketAttrib(attrib.name, attrib.value))
other.expires = self.expires
other.ignore_capacity = ignore_capacity
return other
def check_in(self):
if not self.checkin:
self.checkin = TicketCheckin(self)
if self.checkin.checked_in:
raise CheckinStateException("Ticket is already checked in")
self.checkin.checked_in = True
db.session.commit()
def undo_check_in(self):
if not self.checkin:
self.checkin = TicketCheckin(self)
if not self.checkin.checked_in:
raise CheckinStateException("Ticket is not yet checked in")
self.checkin.checked_in = False
db.session.commit()
def badge_up(self):
if not self.checkin:
self.checkin = TicketCheckin(self)
if self.checkin.badged_up:
raise CheckinStateException("Badge is already issued")
self.checkin.badged_up = True
db.session.commit()
def undo_badge_up(self):
if not self.checkin:
self.checkin = TicketCheckin(self)
if not self.checkin.badged_up:
raise CheckinStateException("Badge is not yet issued")
self.checkin.badged_up = False
db.session.commit()
def __repr__(self):
attrs = [self.type.admits]
if self.paid:
attrs.append('paid')
if self.expired:
attrs.append('expired')
if self.id is None:
return "<Ticket %s: %s>" % (self.type.name, ', '.join(attrs))
else:
return "<Ticket %s %s: %s>" % (self.id, self.type.name,
', '.join(attrs))
def transfer(self, from_user, to_user):
"""
Change the user a ticket is assigned to and remove the qrcode/receipt so
that the old values can't be used.
"""
if not self.type.is_transferable:
raise Exception('This ticket cannot be transferred.')
self.user = to_user
self.emailed = False
self.qrcode = None
self.receipt = None
db.session.add(TicketTransfer(self, to_user, from_user))
db.session.commit()
class UserDiversity(db.Model):
__tablename__ = "diversity"
user_id = db.Column(db.Integer,
db.ForeignKey("user.id"),
nullable=False,
primary_key=True)
age = db.Column(db.String)
gender = db.Column(db.String)
ethnicity = db.Column(db.String)
@classmethod
def get_export_data(cls):
valid_ages = []
ages = defaultdict(int)
sexes = defaultdict(int)
ethnicities = defaultdict(int)
for row in cls.query:
matches = re.findall(r"\b[0-9]{1,3}\b", row.age)
if matches:
valid_ages += map(int, matches)
elif not row.age:
ages[""] += 1
else:
ages["other"] += 1
# Someone might put "more X than Y" or "both X",
# but this mostly works. 'other' includes the error rate.
matches_m = re.findall(r"\b(male|man|m)\b", row.gender, re.I)
matches_f = re.findall(r"\b(female|woman|f)\b", row.gender, re.I)
if matches_m or matches_f:
sexes["male"] += len(matches_m)
sexes["female"] += len(matches_f)
elif not row.gender:
sexes[""] += 1
else:
sexes["other"] += 1
# This is largely junk, because people put jokes or expressions of surprise, which can
# only reasonably be categorised as "other". Next time, we should use an autocomplete,
# explain why we're collecting this information, and separate "other" from "unknown".
matches_white = re.findall(r"\b(white|caucasian|wasp)\b",
row.ethnicity, re.I)
# People really like putting their heritage, which gives another data point or two.
matches_anglo = re.findall(
r"\b(british|english|irish|scottish|welsh|american|australian|canadian|zealand|nz)\b",
row.ethnicity,
re.I,
)
if matches_white or matches_anglo:
if matches_white and matches_anglo:
ethnicities["both"] += 1
elif matches_white:
ethnicities["white"] += 1
elif matches_anglo:
ethnicities["anglosphere"] += 1
elif not row.ethnicity:
ethnicities[""] += 1
else:
ethnicities["other"] += 1
ages.update(bucketise(valid_ages, [0, 15, 25, 35, 45, 55, 65]))
data = {
"private": {
"diversity": {
"age": ages,
"sex": sexes,
"ethnicity": ethnicities
}
},
"tables": ["diversity"],
}
return data
from main import db
from models.Category import Category
from models.Sprint import Sprint
from sqlalchemy.orm import backref, relationship
from models.Member import Member
leagues_categories = db.Table(
"leagues_categories",
db.Column('league_id', db.Integer, db.ForeignKey('leagues.id')),
db.Column('category_id', db.Integer, db.ForeignKey('categories.id')))
class League(db.Model):
__tablename__ = "leagues"
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(), nullable=False, unique=True)
description = db.Column(db.String(), nullable=False)
owner = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
leagues_categories = db.relationship('Category',
secondary=leagues_categories,
backref=db.backref(
'leagues_categories',
lazy='dynamic'))
sprints = db.relationship("Sprint",
cascade="all, delete",
backref=backref("league"))
members = relationship("User", secondary="members")
def __repr__(self):
return f"<Title {self.title}>"
class User(db.Model, UserMixin):
__tablename__ = "user"
__versioned__ = {"exclude": ["favourites", "calendar_favourites"]}
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String, unique=True, index=True)
name = db.Column(db.String, nullable=False, index=True)
phone = db.Column(db.String, nullable=True)
company = db.Column(db.String)
will_have_ticket = db.Column(db.Boolean, nullable=False,
default=False) # for CfP filtering
checkin_note = db.Column(db.String, nullable=True)
# Whether the user has opted in to receive promo emails after this event:
promo_opt_in = db.Column(db.Boolean, nullable=False, default=False)
diversity = db.relationship("UserDiversity",
uselist=False,
backref="user",
cascade="all, delete-orphan")
shipping = db.relationship("UserShipping",
uselist=False,
backref="user",
cascade="all, delete-orphan")
payments = db.relationship("Payment",
lazy="dynamic",
backref="user",
cascade="all")
permissions = db.relationship("Permission",
backref="user",
cascade="all",
secondary=UserPermission)
votes = db.relationship("CFPVote", backref="user", lazy="dynamic")
proposals = db.relationship(
"Proposal",
primaryjoin="Proposal.user_id == User.id",
backref="user",
lazy="dynamic",
cascade="all, delete-orphan",
)
anonymised_proposals = db.relationship(
"Proposal",
primaryjoin="Proposal.anonymiser_id == User.id",
backref="anonymiser",
lazy="dynamic",
cascade="all, delete-orphan",
)
messages_from = db.relationship(
"CFPMessage",
primaryjoin="CFPMessage.from_user_id == User.id",
backref="from_user",
lazy="dynamic",
)
purchases = db.relationship("Purchase",
lazy="dynamic",
primaryjoin="Purchase.purchaser_id == User.id")
owned_purchases = db.relationship(
"Purchase", lazy="dynamic", primaryjoin="Purchase.owner_id == User.id")
owned_tickets = db.relationship("Ticket",
lazy="select",
primaryjoin="Ticket.owner_id == User.id")
transfers_to = db.relationship(
"PurchaseTransfer",
backref="to_user",
lazy="dynamic",
primaryjoin="PurchaseTransfer.to_user_id == User.id",
cascade="all, delete-orphan",
)
transfers_from = db.relationship(
"PurchaseTransfer",
backref="from_user",
lazy="dynamic",
primaryjoin="PurchaseTransfer.from_user_id == User.id",
cascade="all, delete-orphan",
)
def __init__(self, email, name):
self.email = email
self.name = name
@classmethod
def get_export_data(cls):
data = {
"public": {
"users": {
"count": cls.query.count()
}
},
"tables": ["user"],
"private": {
# Volunteer and speaker emails are exported here in order to issue vouchers for the next event
"volunteer_emails":
[u.email for u in User.query.join(ShiftEntry)],
"speaker_emails": [
u.email for u in User.query.join(
Proposal, Proposal.user_id == User.id).filter(
Proposal.state.in_(("accepted", "finished")))
],
},
}
return data
def get_owned_tickets(self, paid=None, type=None):
" Get tickets owned by a user, filtered by type and payment state. "
for ticket in self.owned_tickets:
if (paid is True and not ticket.is_paid_for
or paid is False and ticket.is_paid_for):
continue
if type is not None and ticket.type != type:
continue
yield ticket
def login_code(self, key):
return generate_login_code(key, int(time.time()), self.id)
def sso_code(self, key):
return generate_sso_code(key, int(time.time()), self.id)
@property
def checkin_code(self):
return generate_checkin_code(app.config["SECRET_KEY"], self.id)
@property
def bar_training_token(self):
return generate_bar_training_token(app.config["SECRET_KEY"], self.id)
def has_permission(self, name, cascade=True):
if cascade:
if name != "admin" and self.has_permission("admin"):
return True
if (name.startswith("cfp_") and name != "cfp_admin"
and self.has_permission("cfp_admin")):
return True
for permission in self.permissions:
if permission.name == name:
return True
return False
def grant_permission(self, name):
try:
perm = Permission.query.filter_by(name=name).one()
except NoResultFound:
perm = Permission(name)
db.session.add(perm)
self.permissions.append(perm)
def revoke_permission(self, name):
for user_perm in self.permissions:
if user_perm.name == name:
self.permissions.remove(user_perm)
def __repr__(self):
return "<User %s>" % self.email
@classmethod
def get_by_email(cls, email):
return User.query.filter(
func.lower(User.email) == func.lower(email)).one_or_none()
@classmethod
def does_user_exist(cls, email):
return bool(User.get_by_email(email))
@classmethod
def get_by_code(cls, key, code):
uid = verify_login_code(key, time.time(), code)
if uid is None:
return None
return User.query.filter_by(id=uid).one()
@classmethod
def get_by_checkin_code(cls, key, code):
uid = verify_checkin_code(key, code)
if uid is None:
return None
return User.query.filter_by(id=uid).one()
@classmethod
def get_by_api_token(cls, key, code):
uid = verify_api_token(key, code)
if uid is None:
# FIXME: raise an exception instead of returning None
return None
return User.query.filter_by(id=uid).one()
@classmethod
def get_by_bar_training_token(cls, code):
uid = verify_bar_training_token(app.config["SECRET_KEY"], code)
if uid is None:
raise ValueError("Invalid token")
return User.query.filter_by(id=uid).one()
@property
def is_cfp_accepted(self):
for proposal in self.proposals:
if proposal.state in {"accepted", "finished"}:
return True
return False
class CreateTimeMixin:
create_time = db.Column(
db.DateTime,
default=datetime.datetime.now,
)
class UpdateTimeMixin(CreateTimeMixin):
update_time = db.Column(
db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now,
)
class CalendarEvent(db.Model):
__tablename__ = 'calendar_event'
id = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.String)
start_dt = db.Column(db.DateTime(), nullable=False)
end_dt = db.Column(db.DateTime(), nullable=False)
displayed = db.Column(db.Boolean, nullable=False, default=True)
source_id = db.Column(db.Integer,
db.ForeignKey(CalendarSource.id),
nullable=False,
index=True)
summary = db.Column(db.String, nullable=True)
description = db.Column(db.String, nullable=True)
location = db.Column(db.String, nullable=True)
source = db.relationship(CalendarSource, backref='events')
calendar_favourites = db.relationship('User',
secondary=FavouriteCalendarEvent,
backref='calendar_favourites')
favourite_count = column_property(select([
func.count(FavouriteCalendarEvent.c.user_id)
]).where(FavouriteCalendarEvent.c.user_id == id, ),
deferred=True)
@classmethod
def get_export_data(cls):
events = cls.query.with_entities(
cls.source_id,
cls.uid,
cls.start_dt,
cls.end_dt,
cls.summary,
cls.description,
cls.location,
cls.favourite_count,
).order_by(cls.source_id, cls.id)
data = {
'public': {
'events': events,
},
'tables': ['calendar_event', 'favourite_calendar_event'],
}
return data
@property
def title(self):
return self.summary
@property
def venue(self):
if self.source.main_venue:
return self.source.main_venue
else:
return self.location
@property
def type(self):
return self.source.type
@property
def slug(self):
slug = slugify_unicode(self.summary).lower()
if len(slug) > 60:
words = re.split(' +|[,.;:!?]+', self.summary)
break_words = ['and', 'which', 'with', 'without', 'for', '-', '']
for i, word in reversed(list(enumerate(words))):
new_slug = slugify_unicode(' '.join(words[:i])).lower()
if word in break_words:
if len(new_slug) > 10 and not len(new_slug) > 60:
slug = new_slug
break
elif len(slug) > 60 and len(new_slug) > 10:
slug = new_slug
if len(slug) > 60:
slug = slug[:60] + '-'
return slug
@property
def latlon(self):
if self.source.mapobj:
obj = to_shape(self.source.mapobj.geom)
if isinstance(obj, Point):
return (obj.x, obj.y)
return None
@property
def map_link(self):
latlon = self.latlon
if latlon:
return 'https://map.emfcamp.org/?lat=%s&lon=%s&title=%s#19/%s/%s' % (
latlon[0], latlon[1], self.source.main_venue, latlon[0],
latlon[1])
return None
__table_args__ = (UniqueConstraint(source_id, uid), )
class TicketType(db.Model):
__tablename__ = 'ticket_type'
id = db.Column(db.Integer, primary_key=True)
fixed_id = db.Column(db.Integer, unique=True)
name = db.Column(db.String, nullable=False)
order = db.Column(db.Integer, nullable=False)
# admits should possible be an enum
admits = db.Column(db.String, nullable=False)
type_limit = db.Column(db.Integer, nullable=False)
personal_limit = db.Column(db.Integer, nullable=False)
has_badge = db.Column(db.Boolean, nullable=False)
is_transferable = db.Column(db.Boolean, default=True, nullable=False)
# Nullable fields
expires = db.Column(db.DateTime)
expired = column_property(and_(~expires.is_(None), expires < func.now()))
description = db.Column(db.String)
discount_token = db.Column(db.String)
# replace with capacity table?
admits_types = ('full', 'kid', 'campervan', 'car', 'other')
def __init__(self,
order,
admits,
name,
type_limit,
personal_limit,
expires=None,
discount_token=None,
description=None,
has_badge=True,
is_transferable=True):
if admits not in self.admits_types:
raise Exception('unknown admission type')
self.name = name
self.order = order
self.admits = admits
self.expires = expires
self.has_badge = has_badge
self.type_limit = type_limit
self.description = description
self.discount_token = discount_token
self.personal_limit = personal_limit
self.is_transferable = is_transferable
def __repr__(self):
if self.fixed_id is not None:
clsname = 'FixedTicketType %s' % self.fixed_id
else:
clsname = 'TicketType %s' % self.id
return "<%s: %s>" % (clsname, self.name)
def get_price(self, currency):
for price in self.prices:
if price.currency == currency:
return price.value
def get_price_ex_vat(self, currency):
return self.get_price(currency) / Decimal('1.2')
def get_sold(self, query=None):
if query is None:
query = Ticket.query
# A ticket is sold if it's set to paid, or if it's reserved
# (there's a valid payment associated with it and it's not refunded).
sold_tickets = query.filter(
Ticket.type == self, Ticket.refund_id.is_(None),
or_(
Ticket.paid,
Payment.query.filter(
Payment.state != 'new',
Payment.state != 'cancelled',
Payment.state != 'refunded',
).filter(Payment.tickets.expression).exists()))
return sold_tickets
def get_remaining(self):
if self.expired:
return 0
return self.type_limit - self.get_sold().count()
def user_limit(self, user, discount_token):
if self.expired:
return 0
# Why do we need to check this?
if self.discount_token is not None and self.discount_token != discount_token:
return 0
if user.is_authenticated():
# How many have been sold to this user
user_count = self.get_sold(user.tickets).count()
else:
user_count = 0
return min(self.personal_limit - user_count, self.get_remaining())
@classmethod
def get_types_for_token(cls, token):
return TicketType.query.filter_by(discount_token=token,
expired=False).all()
@classmethod
@cache.memoize(timeout=60)
def get_price_cheapest_full(cls):
""" Get the cheapest full ticket price. This may return
None if there are no tickets (currently) available. """
types = TicketType.query.filter_by(admits='full', discount_token=None)
prices = [
tt.get_price('GBP') for tt in types
if tt.get_remaining() > 0 and 'supporter' not in tt.name.lower()
]
if len(prices) > 0:
return min(prices)
else:
return None
@classmethod
def get_ticket_sales(cls):
""" Get the number of tickets sold, by ticket type.
Returns a dict of type -> count """
# FIXME: should this filter by payment type?
full_tickets = TicketType.query.filter_by(admits='full').all()
kid_tickets = TicketType.query.filter_by(admits='kid').all()
admissions_tickets = full_tickets + kid_tickets
ticket_totals = {}
for ticket_type in admissions_tickets:
ticket_totals[ticket_type] = ticket_type.get_sold().count()
return ticket_totals
@classmethod
def get_tickets_remaining(cls):
""" Get the total number of tickets remaining. """
total = Payment.query.filter(
Payment.state != 'new',
Payment.state != 'cancelled',
Payment.state != 'refunded',
).join(Ticket).join(Ticket.type).filter(
or_(TicketType.admits == 'full',
TicketType.admits == 'kid')).count()
return app.config.get('MAXIMUM_ADMISSIONS') - total
class Courses(db.Model):
"""课程表模型类"""
__tablename__ = "courses"
id = db.Column(INTEGER, primary_key=True) # id主键
cname = db.Column(VARCHAR(30), nullable=False) # 课程名称
ctype = db.Column(VARCHAR(10), nullable=False) # 课程类型
classweek = db.Column(CHAR(3), nullable=False) # 上课星期
classtime = db.Column(TIME, nullable=False) # 上课时间
cnum = db.Column(SMALLINT, nullable=False) # 上课最大人数
cretime = db.Column(DATETIME, nullable=False) # 课程创建时间
updtime = db.Column(DATETIME, nullable=False) # 课程修改/审批时间
credit = db.Column(TINYINT, nullable=False) # 学分
ispass = db.Column(TINYINT(1), nullable=False, default=0) # 是否通过审批
isexamine = db.Column(TINYINT(1), nullable=False, default=0) # 是否已被审批
isend = db.Column(TINYINT(1), nullable=False, default=0) # 课程是否结束标志
rid = db.Column(INTEGER, db.ForeignKey("classrooms.id")) # 上课教室id
tid = db.Column(INTEGER, db.ForeignKey("teachers.id")) # 课程开设教师id
caid = db.Column(INTEGER, db.ForeignKey("campuses.id")) # 开课校区id
selcourses = db.relationship("Selcourses", backref="course")
achievements = db.relationship("Achievements", backref="course")
def list_to_dict(self):
return {
"id": self.id,
"cname": self.cname,
"ctype": self.ctype,
"week": self.classweek,
"ctime": str(self.classweek) + str(self.classtime),
"croom": self.classroom.rname,
"cnum": self.cnum,
"credit": self.credit,
"caname": self.campus.caname,
"tname": self.teacher.tname,
"caid": self.caid
}
def __repr__(self):
return self.cname
class Grid(db.Model):
__tablename__ = 'grid'
id = db.Column(db.Integer, primary_key=True)
population = db.Column(db.Float, nullable=True)
income = db.Column(db.Integer, nullable=True)
averlight = db.Column(db.Float, nullable=True)
places = db.Column(db.Integer, nullable=True)
b_diversity = db.Column(db.Float, nullable=True)
dev_intensity = db.Column(db.Integer, nullable=True)
cell_id = db.Column(db.Integer, nullable=True)
lng = db.Column(db.Float, nullable=True)
lat = db.Column(db.Float, nullable=True)
inc_cat = db.Column(db.Integer, nullable=True)
msa = db.Column(db.String(15), nullable=True)
def __init__(self, population, income, averlight, places, b_diversity,
dev_intensity, cell_id, lng, lat, inc_cat, msa):
self.population = population
self.income = income
self.averlight = averlight
self.places = places
self.b_diversity = b_diversity
self.dev_intensity = dev_intensity
self.cell_id = cell_id
self.lng = lng
self.lat = lat
self.inc_cat = inc_cat
self.msa = msa
def __repr__(self):
return '<id {}>'.format(self.id)
class Proposal(Base):
__tablename__ = 'proposal'
# Ignore the version table
id = db.Column(db.Integer, primary_key=True)
hide_from_schedule = db.Column(db.Boolean, default=False, nullable=False)
sdg_event_mappings = content["sdg"]
self.remove_all_sdg()
for sdg_event_mapping in sdg_event_mappings:
self.add_sdg(sdg_event_mapping["id"])
class Tag(db.Model):
event_id = db.Column(db.Integer,
db.ForeignKey('event.id'),
primary_key=True)
tag = db.Column(db.String(20), primary_key=True)
event = db.relationship(Event, backref='event')
# event_tags = db.Table(
# "event_tags",
# db.Column("event_id", db.Integer, db.ForeignKey("event.id"), primary_key=True),
# db.Column("tag", db.String(20), primary_key=True)
# )
event_rsvps = db.Table(
"event_rsvps",
db.Column("event_id",
db.Integer,
db.ForeignKey("event.id"),
primary_key=True),
db.Column("username",
db.String(20),
db.ForeignKey("user.username"),
primary_key=True))
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(15), unique=True)
email = db.Column(db.String(50), unique=True)
password = db.Column(db.String(80))
class Event(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), nullable=False)
description = db.Column(db.String(128), )
parent_id = db.Column(db.String(20), db.ForeignKey('user.username'))
longitude = db.Column(db.Float, )
latitude = db.Column(db.Float, )
ongoing = db.Column(db.Boolean, )
startDate = db.Column(db.DateTime, )
endDate = db.Column(db.DateTime, )
sdg = db.relationship("SDG", secondary=sdg.sdg_to_events)
def toDict(self):
eventDict = {}
eventDict["name"] = self.name
eventDict["description"] = self.description
if (self.startDate is not None and self.endDate is not None):
eventDict["startDate"] = self.startDate.isoformat()
eventDict["endDate"] = self.endDate.isoformat()
else:
eventDict["startDate"] = None
eventDict["endDate"] = None
if self.latitude is not None and self.longitude is not None:
eventDict["latitude"] = self.latitude
eventDict["longitude"] = self.longitude
eventDict["id"] = self.id
sdg_events_rows = db.session.query(
sdg.sdg_to_events).filter_by(event_id=self.id).limit(100)
if sdg_events_rows.count() > 0:
eventDict["sdg"] = []
for sdg_event_row in sdg_events_rows:
eventDict["sdg"].append(sdg.get_sdg(sdg_event_row.sdg_id))
tag_event_rows = Tag.query.filter_by(event_id=self.id).limit(100)
if tag_event_rows.count() > 0:
eventDict["tags"] = []
for tag_event_row in tag_event_rows:
eventDict["tags"].append(tag_event_row.tag)
return eventDict
def remove_all_sdg(self):
'''
Run this when doing an update!
'''
self.sdg = []
db.session.commit()
def add_sdg(self, sdg_id):
sdg_obj = sdg.SDG.query.filter_by(id=sdg_id).first()
# self.ad
self.sdg.append(sdg_obj)
db.session.add(self)
db.session.commit()
def add_tag(self, tag):
tag = Tag(event_id=self.id, tag=tag)
db.session.add(tag)
db.session.commit()
def fromDict(self, content):
if "name" in content.keys():
self.name = content["name"]
if "description" in content.keys():
self.description = content["description"]
try:
if "longitude" in content.keys():
self.longitude = float(content["longitude"])
if "latitude" in content.keys():
self.latitude = float(content["latitude"])
except Exception:
print("Could not grab latLong", content["longitude"],
content["latitude"])
if "ongoing" in content.keys():
self.ongoing = content["ongoing"] == True
else:
self.ongoing = False
if "startDate" in content.keys() and content["startDate"] is not None:
self.startDate = dateutil.parser.parse(content["startDate"])
if "endDate" in content.keys() and content["endDate"] is not None:
self.endDate = dateutil.parser.parse(content["endDate"])
if "sdg" in content.keys() and self.id is not None:
sdg_event_mappings = content["sdg"]
self.remove_all_sdg()
for sdg_event_mapping in sdg_event_mappings:
self.add_sdg(sdg_event_mapping["id"])
class SmartPhoneNotificationSensor(db.Model):
id = db.Column(db.Integer, primary_key=True)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
send_interval = db.Column(db.String)
smart_phone_id = db.Column(db.Integer, db.ForeignKey("smart_phone.id"))
metrics = db.relationship("SmartPhoneNotificationSensorData", backref="smart_phone_notification_sensor", lazy='dynamic', cascade="all, delete-orphan")
def __repr__(self):
return "<SmartPhoneNotificzationSensor {}>".format(self.id)
#return getattr(self,attr)
def created(self):
return self.created_at.strftime("%d/%m/%Y %H:%M:%S")
def show_notification(self, new_metric):
print("\nFrom SmartPhone")
print("Notification Received!")
print(new_metric.notification, '\n')
def add_metric(self, notification):
waitTime = random.randint(3,8)
#time.sleep(waitTime)
if waitTime < 5:
new_metric = SmartPhoneNotificationSensorData(smart_phone_notification_sensor=self)
new_metric.notification = notification
self.show_notification(new_metric)
db.session.add(new_metric)
db.session.commit()
return 1
else:
return 0
def add_metric_from_dict(self, D):
try:
new_metric = SmartPhoneNotificationSensorData(smart_phone_notification_sensor=self)
print(D)
for k, v in D.items():
#if type(getattr(SmartPhoneNotificationSensorData, k)) == property :
if hasattr(new_metric, k + '_raw_point_x'):
print('Probably a position attr. Trying to set as a POINT attr.')
k = k + '_raw_point'
x, y = v.split(',')
x.strip()
y.strip()
setattr(new_metric, k + '_x', float(x))
setattr(new_metric, k + '_y', float(y))
else:
setattr(new_metric, k, v)
db.session.add(new_metric)
db.session.commit()
except Exception as e:
print('Error inserting metric!', e)
def number_of_metrics(self):
return self.metrics.count()
def get_metrics_to_plot(self, axis, metric_name):
metrics = self.metrics.filter(getattr(SmartPhoneNotificationSensorData, metric_name) != None).order_by(SmartPhoneNotificationSensorData.created_at.desc()).limit(30).all()
if axis == 'x':
result = [metric.created_at.isoformat() for metric in metrics]
else:
result = [getattr(metric, metric_name) for metric in metrics]
return result
def get_last_metric_data(self, metric_name):
if not hasattr(SmartPhoneNotificationSensorData, metric_name):
return None
metric = self.metrics.filter(getattr(SmartPhoneNotificationSensorData, metric_name) != None).order_by(SmartPhoneNotificationSensorData.created_at.desc()).first()
return metric
class Tag(db.Model):
event_id = db.Column(db.Integer,
db.ForeignKey('event.id'),
primary_key=True)
tag = db.Column(db.String(20), primary_key=True)
event = db.relationship(Event, backref='event')
class MonitorCryingSensor(db.Model):
id = db.Column(db.Integer, primary_key=True)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
send_interval = db.Column(db.String)
monitor_id = db.Column(db.Integer, db.ForeignKey("monitor.id"))
metrics = db.relationship("MonitorCryingSensorData",
backref="monitor_crying_sensor",
lazy='dynamic',
cascade="all, delete-orphan")
def __repr__(self):
return "<MonitorCryingSensor {}>".format(self.id)
def created(self):
return self.created_at.strftime("%d/%m/%Y %H:%M:%S")
def add_metric(self, crying):
new_metric = MonitorCryingSensorData(monitor_crying_sensor=self)
new_metric.crying = crying
db.session.add(new_metric)
db.session.commit()
def add_metric_from_dict(self, D):
try:
new_metric = MonitorCryingSensorData(monitor_crying_sensor=self)
print(D)
for k, v in D.items():
#if type(getattr(MonitorCryingSensorData, k)) == property :
if hasattr(new_metric, k + '_raw_point_x'):
print(
'Probably a position attr. Trying to set as a POINT attr.'
)
k = k + '_raw_point'
x, y = v.split(',')
x.strip()
y.strip()
setattr(new_metric, k + '_x', float(x))
setattr(new_metric, k + '_y', float(y))
else:
setattr(new_metric, k, v)
db.session.add(new_metric)
db.session.commit()
except Exception as e:
print('Error inserting metric!', e)
def number_of_metrics(self):
return self.metrics.count()
def get_metrics_to_plot(self, axis, metric_name):
metrics = self.metrics.filter(
getattr(MonitorCryingSensorData, metric_name) != None).order_by(
MonitorCryingSensorData.created_at.desc()).limit(30).all()
if axis == 'x':
result = [metric.created_at.isoformat() for metric in metrics]
else:
result = [getattr(metric, metric_name) for metric in metrics]
return result
def get_last_metric_data(self, metric_name):
if not hasattr(MonitorCryingSensorData, metric_name):
return None
metric = self.metrics.filter(
getattr(MonitorCryingSensorData, metric_name) != None).order_by(
MonitorCryingSensorData.created_at.desc()).first()
return metric
@classmethod
def get_enabled_events(self):
sources = CalendarSource.query.filter_by(published=True,
displayed=True)
events = []
for source in sources:
events.extend(source.events)
return events
FavouriteCalendarEvent = db.Table(
'favourite_calendar_event',
db.Model.metadata,
db.Column('user_id',
db.Integer,
db.ForeignKey('user.id'),
primary_key=True),
db.Column('event_id',
db.Integer,
db.ForeignKey('calendar_event.id'),
primary_key=True),
)
class CalendarEvent(db.Model):
__tablename__ = 'calendar_event'
id = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.String)
start_dt = db.Column(db.DateTime(), nullable=False)
end_dt = db.Column(db.DateTime(), nullable=False)
class University(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), unique=True, nullable=False)
pubkey = db.Column(db.String(255), unique=True, nullable=False)
address = db.Column(db.String(255), unique=True, nullable=False)
class CalendarSource(db.Model):
__tablename__ = 'calendar_source'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), index=True)
url = db.Column(db.String, nullable=False)
name = db.Column(db.String)
type = db.Column(db.String, default="Village")
priority = db.Column(db.Integer, default=0)
enabled = db.Column(db.Boolean, nullable=False, default=False)
refreshed_at = db.Column(db.DateTime())
displayed = db.Column(db.Boolean, nullable=False, default=False)
published = db.Column(db.Boolean, nullable=False, default=False)
main_venue = db.Column(db.String)
map_obj_id = db.Column(db.Integer, db.ForeignKey('map_object.id'))
contact_phone = db.Column(db.String)
contact_email = db.Column(db.String)
user = db.relationship('User', backref="calendar_sources")
mapobj = db.relationship('MapObject')
# Make sure these are identifiable to the memoize cache
def __repr__(self):
return "<%s %s: %s>" % (self.__class__.__name__, self.id, self.url)
@classmethod
def get_export_data(cls):
sources = cls.query.with_entities(
cls.id,
cls.name,
cls.type,
cls.enabled,
cls.url,
cls.main_venue,
cls.lat,
cls.lon,
cls.priority,
).order_by(cls.id)
data = {
'public': {
'sources': sources,
},
'tables': ['calendar_source'],
}
return data
def refresh(self):
request = requests.get(self.url)
cal = Calendar.from_ical(request.text)
if self.name is None:
self.name = cal.get('X-WR-CALNAME')
for event in self.events:
event.displayed = False
local_tz = pendulum.timezone('Europe/London')
alerts = []
uids_seen = set()
out_of_range_event = False
for component in cal.walk():
if component.name == 'VEVENT':
summary = component.get('Summary')
# postgres converts to UTC if given an aware datetime, so strip it up front
start_dt = pendulum.instance(component.get('dtstart').dt)
start_dt = local_tz.convert(start_dt).naive()
end_dt = pendulum.instance(component.get('dtend').dt)
end_dt = local_tz.convert(end_dt).naive()
name = summary
if summary and start_dt:
name = "'{}' at {}".format(summary, start_dt)
elif summary:
name = "'{}'".format(summary)
elif start_dt:
name = 'Event at {}'.format(start_dt)
else:
name = len(self.events) + 1
if not component.get('uid'):
alerts.append(('danger', "{} has no UID".format(name)))
continue
uid = str(component['uid'])
if uid in uids_seen:
alerts.append(
('danger', "{} has duplicate UID {}".format(name,
uid)))
continue
uids_seen.add(uid)
if 'rrule' in component:
alerts.append(
('warning',
"{} has rrule, which is not processed".format(uid)))
# Allow a bit of slop for build-up events
if start_dt < event_start() - pendulum.duration(
days=2) and not out_of_range_event:
alerts.append((
'warning',
"At least one event ({}) is before the start of the event"
.format(uid)))
out_of_range_event = True
if end_dt > event_end() + pendulum.duration(
days=1) and not out_of_range_event:
alerts.append((
'warning',
"At least one event ({}) is after the end of the event"
.format(uid)))
out_of_range_event = True
if start_dt > end_dt:
alerts.append((
'danger',
"Start time for {} is after its end time".format(uid)))
out_of_range_event = True
try:
event = CalendarEvent.query.filter_by(source_id=self.id,
uid=uid).one()
except NoResultFound:
event = CalendarEvent(uid=uid)
self.events.append(event)
if len(self.events) > 1000:
raise Exception("Too many events in feed")
event.start_dt = start_dt
event.end_dt = end_dt
event.summary = component.get('summary')
event.description = component.get('description')
event.location = component.get('location')
event.displayed = True
self.refreshed_at = pendulum.now()
return alerts
@classmethod
def get_enabled_events(self):
sources = CalendarSource.query.filter_by(published=True,
displayed=True)
events = []
for source in sources:
events.extend(source.events)
return events
class PayrollsModel(db.Model):
__tablename__ = 'payrolls'
id = db.Column(db.Integer, primary_key=True)
employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
dept_name = db.Column(db.String(50), db.ForeignKey('departments.dpt_name'))
payroll_month = db.Column(db.String(50), nullable=False)
gross_salary = db.Column(db.Float)
nssf_deductions = db.Column(db.Float)
nhif_deductions = db.Column(db.Float)
taxable_income = db.Column(db.Float)
PAYE = db.Column(db.Float)
overtime = db.Column(db.Float)
salary_advance = db.Column(db.Float)
other_deductions = db.Column(db.Float)
net_salary = db.Column(db.Float)
#creating entry
def instert_into_database(self): #this is an instance method
db.session.add(self)
db.session.commit()
@classmethod
def fetch_by_id(cls,emp_id):
return cls.query.filter_by(employee_id=emp_id).first()
@classmethod
def fetch_all(cls):
return cls.query.all()
class Ticket(db.Model):
__tablename__ = 'ticket'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
code = db.Column(db.String,
db.ForeignKey('ticket_type.code'),
nullable=False,
index=True)
paid = db.Column(db.Boolean, default=False, nullable=False)
expires = db.Column(db.DateTime, nullable=False)
receipt = db.Column(db.String, unique=True)
payment_id = db.Column(db.Integer, db.ForeignKey('payment.id'))
attribs = db.relationship("TicketAttrib", backref="ticket", cascade='all')
type = db.relationship(TicketType, backref="tickets")
def __init__(self, type=None, code=None):
if type:
self.type = type
self.code = type.code
elif code is not None:
self.code = code
self.type = TicketType.query.get(code)
else:
raise ValueError('Type must be specified')
self.expires = datetime.utcnow() + timedelta(hours=2)
self.ignore_capacity = False
def expired(self):
if self.paid:
return False
return self.expires < datetime.utcnow()
def clone(self, new_code=None, ignore_capacity=False):
if new_code is not None:
raise NotImplementedError('Changing codes not yet supported')
other = Ticket(type=self.type)
for attrib in self.attribs:
other.attribs.append(TicketAttrib(attrib.name, attrib.value))
other.expires = self.expires
other.ignore_capacity = ignore_capacity
return other
def create_receipt(self):
while True:
random.seed()
self.receipt = ''.join(random.sample(safechars_lower, 6))
try:
db.session.commit()
break
except IntegrityError:
db.session.rollback()
def __repr__(self):
attrs = [self.code]
if self.paid:
attrs.append('paid')
if self.expired():
attrs.append('expired')
return "<Ticket %s: %s>" % (self.id, ', '.join(attrs))