class AnonymousMessage(BaseModel):
__tablename__ = 'anonymous_message'
a_id = db.Column(db.Integer, nullable=False, comment='匿名信息所属帖子id')
am_floor = db.Column(db.Integer, nullable=True, comment='匿名信息在该帖的楼层')
am_content = db.Column(db.String(50), nullable=False, comment='匿名信息内容')
am_refer_to = db.Column(db.Integer, nullable=True, comment='匿名信息回复指向楼层')
am_timestamp = db.Column(db.Integer, nullable=False, comment='匿名信息回复时间戳')
class Employee(db.Model):
__tablename__ = 'employees'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text, nullable=False)
notes = db.Column(db.Text)
investigator = db.Column(db.Boolean)
# assignments = db.relationship(
# 'Assignment',
# backref='employee',
# lazy=True
# )
def __str__(self):
return self.name
def serialize(self):
return {
'id': self.id,
'name': self.name,
'notes': self.notes,
'investigator': self.investigator
}
@staticmethod
def get_all():
return Employee.query.order_by(Employee.name).all()
class Anonymous(BaseModel):
__tablename__ = 'anonymous'
a_title = db.Column(db.String(50), nullable=False, comment='匿名信息标题')
a_content = db.Column(db.String(150), nullable=False, comment='匿名信息内容')
a_timestamp = db.Column(db.BigInteger, nullable=False, comment='发帖时间戳')
a_max_floor = db.Column(db.Integer,
nullable=False,
default=1,
comment='目前楼层')
class Assignment(db.Model):
__tablename__ = 'assignments'
id = db.Column(db.Integer, primary_key=True)
employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
project_id = db.Column(db.Integer, db.ForeignKey('projects.id'))
first_month = db.Column(db.Integer, nullable=False)
last_month = db.Column(db.Integer, nullable=False)
effort = db.Column(db.Integer, nullable=False)
notes = db.Column(db.Text)
def serialize(self):
return {
'id': self.id,
'emp_id': self.employee_id,
'prj_id': self.project_id,
'first_month': self.first_month,
'last_month': self.last_month,
'effort': self.effort,
'notes': self.notes
}
@staticmethod
def get_all():
return Assignment.query.all()
class Precinct(db.Model):
__tablename__ = 'precincts'
id = db.Column(db.Integer, primary_key=True)
jurisdiction_name = db.Column(db.Text, nullable=False)
ward = db.Column(db.Text, nullable=False)
precinct = db.Column(db.Text, nullable=False)
state_house = db.Column(db.Text, nullable=False)
state_senate = db.Column(db.Text, nullable=False)
congress = db.Column(db.Text, nullable=False)
def __str__(self):
return '%s, %s %s' % (
self.jurisdiction_name,
self.ward,
self.precinct
)
def attrs(self):
return list(filter(lambda x: x[0] != '_', vars(self)))
def serialize(self):
result = {attr: getattr(self, attr) for attr in self.attrs()}
return result
@staticmethod
def get_all():
return Precinct.query.all();
@staticmethod
def get_one(pct_id):
return Precinct.query.get(pct_id)
class Membership(TimestampMixin, db.Model):
__tablename__ = 'memberships'
id = db.Column(db.Integer, primary_key=True)
group_id = db.Column(db.Integer, db.ForeignKey('groups.id'))
contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id'))
role = db.Column(db.Text)
comment = db.Column(db.Text)
def attrs(self):
return list(filter(lambda x: x[0] != '_', vars(self)))
def serialize(self):
return {attr: getattr(self, attr) for attr in self.attrs()}
@staticmethod
def get_all():
return Membership.query.all()
class Modification(db.Model):
__tablename__ = 'modifications'
table_name = db.Column(db.Text, primary_key=True)
changed_at = db.Column(db.DateTime)
@staticmethod
def get_all(serialized=False):
rex = Modification.query.all()
if serialized:
return [rec.serialize() for rec in rex]
else:
return rex
def attrs(self):
return list(filter(lambda x: x[0] != '_', vars(self)))
def serialize(self):
return {attr: getattr(self, attr) for attr in self.attrs()}
class BaseModel(db.Model):
__abstract__ = True
id = db.Column(db.Integer, primary_key=True)
def save(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
class User(TimestampMixin, db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.Text, unique=True, nullable=False)
password = db.Column(db.Text, nullable=False)
roles = db.Column(db.Text)
active = db.Column(
db.Boolean, default=True, server_default='true')
@property
def rolenames(self):
# noinspection PyBroadException
try:
return self.roles.split(',')
except Exception:
return []
@rolenames.setter
def rolenames(self, roles):
self.roles = roles
@classmethod
def lookup(cls, username):
return cls.query.filter_by(username=username).one_or_none()
def save(self):
db.session.add(self)
db.session.commit()
@classmethod
def identify(cls, user_id):
return cls.query.get(user_id)
@property
def identity(self):
return self.id
def is_valid(self):
return self.active
class Group(TimestampMixin, db.Model):
__tablename__ = 'groups'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text, nullable=False)
code = db.Column(db.Text, nullable=False)
description = db.Column(db.Text)
def __init__(self, d=None):
self.name = None
self.code = None
self.description = None
self.created_at = None
self.updated_at = None
if d:
for attr in d:
setattr(self, attr, d[attr])
def __str__(self):
return '%s (%s)' % (self.name, self.code)
def attrs(self):
return list(filter(lambda x: x[0] != '_', vars(self)))
def serialize(self):
return {attr: getattr(self, attr) for attr in self.attrs()}
@staticmethod
def get_all(with_members=False):
return Group.query.order_by(Group.name, ).all()
@staticmethod
def add(d):
sql = ("INSERT INTO groups "
"(name, code, description) "
"VALUES (:name,:code,:desc)")
vals = {'name': d['name'], 'code': d['code'], 'desc': d['description']}
new_id = db.session.execute(sql, vals)
db.session.commit()
return new_id.lastrowid
class User(BaseModel):
__tablename__ = 'user'
u_name = db.Column(db.String(30),
nullable=False,
unique=True,
comment='用户名')
u_password = db.Column(db.String(256), nullable=False, comment='密码')
u_email = db.Column(db.String(100),
nullable=False,
comment='邮箱',
unique=True)
u_email_check = db.Column(db.Boolean, default=False, comment='邮箱验证')
is_clan_member = db.Column(db.Boolean,
default=False,
comment='是否是战队成员 1是 0否')
is_admin = db.Column(db.Boolean, default=False, comment='是否是管理员 1是 0否')
u_avater = db.Column(db.String(150),
default='/static/defaultImg/default_avater.png',
comment='头像')
u_sign = db.Column(db.String(100), nullable=True, default="", comment='签名')
is_delete = db.Column(db.Boolean, default=False, comment='是否被删除 1是 0否')
u_create_time = db.Column(db.Integer, nullable=False, comment='创建时间')
class Project(db.Model):
__tablename__ = 'projects'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text, nullable=False)
nickname = db.Column(db.Text)
notes = db.Column(db.Text)
first_month = db.Column(db.Integer, nullable=False)
last_month = db.Column(db.Integer, nullable=False)
# start_date = db.Column(db.DateTime)
# end_date = db.Column(db.DateTime)
assignments = db.relationship('Assignment',
backref='project',
cascade='delete, delete-orphan',
lazy=True)
def __init__(self, d):
for attr in ['name', 'nickname', 'notes', 'first_month', 'last_month']:
setattr(self, attr, d[attr])
def __str__(self):
return self.nickname
def serialize(self):
return {
'id':
self.id,
'name':
self.name,
'nickname':
self.nickname,
'first_month':
self.first_month,
'last_month':
self.last_month,
'notes':
self.notes,
'asns': [asn.serialize()
for asn in self.assignments] if self.assignments else []
}
@staticmethod
def get_all():
return Project.query.order_by(Project.nickname).all()
@staticmethod
def get_one(prj_id):
return Project.query.filter_by(id=prj_id).first()
def add(self):
db.session.add(self)
db.session.commit()
return self.id
def drop(self):
db.session.delete(self)
db.session.commit()
class Article(db.Model):
__tablename__ = 'articles'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(120), nullable=False)
description = db.Column(db.String(120), nullable=False)
body = db.Column(db.String(1024), nullable=False)
created_at = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
is_deleted = db.Column(db.Boolean, default=False)
class Rent(BaseModel):
__tablename__ = 'rent'
date = db.Column(db.String(10), nullable=False, comment='付款日')
year = db.Column(db.Integer, nullable=False, comment='付款日-年')
month = db.Column(db.Integer, nullable=False, comment='付款日-月')
day = db.Column(db.Integer, nullable=False, comment='付款日-日')
house_num = db.Column(db.Integer, nullable=False, comment='房号')
meter_reading_last_month = db.Column(db.Float,
nullable=False,
comment='电表读数(上月)')
meter_reading_this_month = db.Column(db.Float,
nullable=False,
comment='电表读数(本月)')
electricity_consumption = db.Column(db.Float,
nullable=False,
comment='用电量')
electricity_expense = db.Column(db.Float, nullable=False, comment='电费')
water_meter_reading_last_month = db.Column(db.Float,
nullable=False,
comment='水表读数(上月)')
water_meter_reading_this_month = db.Column(db.Float,
nullable=False,
comment='水表读数(本月)')
water_consumption = db.Column(db.Float, nullable=False, comment='用水量')
water_expense = db.Column(db.Float, nullable=False, comment='水费')
other_fee = db.Column(db.Float, nullable=True, default=0, comment='其他费用')
rent_fee = db.Column(db.Float, nullable=False, comment='房租')
total_fee = db.Column(db.Float, nullable=False, comment='合计')
remark = db.Column(db.String(50), nullable=True, comment='备注')
class Voter(TimestampMixin, db.Model):
__tablename__ = 'voters'
last_name = db.Column(db.Text, nullable=False)
first_name = db.Column(db.Text, nullable=False)
middle_name = db.Column(db.Text)
name_suffix = db.Column(db.Text)
name_metaphone = db.Column(db.Text, nullable=False)
birth_year = db.Column(db.Integer)
gender = db.Column(db.Text)
house_number = db.Column(db.Integer)
street_prefix = db.Column(db.Text)
street_name = db.Column(db.Text)
street_type = db.Column(db.Text)
street_suffix = db.Column(db.Text)
unit = db.Column(db.Text)
street_metaphone = db.Column(db.Text)
city = db.Column(db.Text)
zipcode = db.Column(db.Text)
precinct_id = db.Column(db.Integer)
voter_id = db.Column(db.Integer, primary_key=True)
reg_date = db.Column(db.Text)
permanent_absentee = db.Column(db.Text)
status = db.Column(db.Text)
uocava = db.Column(db.Text)
party = db.Column(db.Text)
def __init__(self, d=None):
if d:
self.last_name = d['last_name'].upper()
self.name_metaphone = MatchLib.get_single(self.last_name)
self.first_name = d['first_name'].upper()
def __str__(self):
return PersonName.display_name({
'last_name': self.last_name,
'first_name': self.first_name,
'middle_name': self.middle_name,
'name_suffix': self.name_suffix
})
def attrs(self):
return list(filter(lambda x: x[0] != '_', vars(self)))
def serialize(self):
return {attr: getattr(self, attr) for attr in self.attrs()}
# @hybrid_property
# def person_name(self):
# from models.person_name import PersonName
#
# return PersonName({
# 'last': self.last,
# 'first': self.first,
# 'middle': self.middle,
# 'suffix': self.suffix
# })
#
# def serialize(self):
# return {
# 'id': self.id,
# 'name': self.person_name.serialize(),
# 'address': {
# 'house_number': self.house_number,
# 'pre_direction': self.pre_direction,
# 'street_name': self.street_name,
# 'street_type': self.street_type,
# 'suf_direction': self.suf_direction,
# 'unit': self.unit,
# 'street_metaphone': self.street_metaphone,
# 'city': self.city,
# 'zipcode': self.zipcode
# },
# 'voter_info': {
# 'voter_id': self.voter_id,
# 'precinct_id': self.precinct_id,
# 'birth_year': self.birth_year,
# 'gender': self.gender,
# 'reg_date': self.reg_date,
# 'permanent_absentee': self.permanent_absentee,
# 'status': self.status,
# 'uocava': self.uocava,
# 'party': self.party
# },
# 'created_at': self.created_at,
# 'updated_at': self.updated_at
# }
@staticmethod
def by_fuzzy_name_and_address(pn, addr):
return Voter.query.filter(
(Voter.street_metaphone == addr.street_metaphone)
& (Voter.street_name.like(addr.street_name[0] + '%'))
& (Voter.house_number.between(addr.block[0], addr.block[1]))
& (Voter.name_metaphone == pn.name_metaphone)
& (Voter.last_name.like(pn.last_name[0] + '%'))).all()
@staticmethod
def by_fuzzy_name(pn):
return Voter.query.filter(
(Voter.name_metaphone == pn.name_metaphone)
& (Voter.last_name.like(pn.last_name[0] + '%'))
& (Voter.first_name.like(pn.first_name[0] + '%'))).all()
@staticmethod
def fuzzy_lookup(params):
from models.person_name import PersonName
from models.address import Address
pn = PersonName(params)
if 'address' in params and params['address'] != '':
addr = Address(params)
matches = Voter.by_fuzzy_name_and_address(pn, addr)
if matches:
return matches
candidates = Voter.by_fuzzy_name(pn)
candidates_by_name = {
str(candidate): candidate
for candidate in candidates
}
names = [str(candidate) for candidate in candidates]
matches = MatchLib.get_best_partials(str(pn), names, 75)
return [candidates_by_name[match] for match in matches]
class Street(db.Model):
__tablename__ = 'streets'
id = db.Column(db.Integer, primary_key=True)
house_num_low = db.Column(db.Integer)
house_num_high = db.Column(db.Integer)
street_side = db.Column(db.Text)
street_prefix = db.Column(db.Text)
street_name = db.Column(db.Text)
street_type = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_metaphone = db.Column(db.Text, nullable=False)
ext_low = db.Column(db.Text)
ext_high = db.Column(db.Text)
city = db.Column(db.Text)
zipcode = db.Column(db.Text)
precinct_id = db.Column(db.ForeignKey('precincts.id'))
def __str__(self):
s = ''
if self.street_prefix:
s += ' %s' % self.street_prefix
s += ' %s' % self.street_name
if self.street_type:
s += ' %s' % self.street_type
if self.street_suffix:
s += ' %s' % self.street_suffix
return s.strip()
def attrs(self):
return list(filter(lambda x: x[0] != '_', vars(self)))
def serialize(self):
return {attr: getattr(self, attr) for attr in self.attrs()}
@staticmethod
def get_all():
return Street.query.all()
class Contact(TimestampMixin, db.Model):
__tablename__ = 'contacts'
id = db.Column(db.Integer, primary_key=True)
last_name = db.Column(db.Text, nullable=False)
first_name = db.Column(db.Text, nullable=False)
middle_name = db.Column(db.Text)
name_suffix = db.Column(db.Text)
nickname = db.Column(db.Text)
alias = db.Column(db.Text)
name_metaphone = db.Column(db.Text, nullable=False)
dob = db.Column(db.Integer)
gender = db.Column(db.Text)
email = db.Column(db.Text)
phone1 = db.Column(db.Text)
phone2 = db.Column(db.Text)
email_metaphone = db.Column(db.Text)
house_number = db.Column(db.Integer)
street_prefix = db.Column(db.Text)
street_name = db.Column(db.Text)
street_type = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_metaphone = db.Column(db.Text)
unit = db.Column(db.Text)
city = db.Column(db.Text)
zipcode = db.Column(db.Text)
precinct_id = db.Column(db.Integer)
voter_id = db.Column(db.Integer)
voter_reg_date = db.Column(db.Text)
active = db.Column(db.Boolean)
comment = db.Column(db.Text)
def __str__(self):
return PersonName.display_name({
'last': self.last_name,
'first': self.first_name,
'middle': self.middle_name,
'suffix': self.name_suffix
})
def attrs(self):
return list(filter(lambda x: x[0] != '_', vars(self)))
def serialize(self):
return {attr: getattr(self, attr) for attr in self.attrs()}
# def serialize(self):
# return {
# 'id': self.id,
# 'name': {
# 'last': self.last_name,
# 'first': self.first_name,
# 'middle': self.middle,
# 'suffix': self.suffix,
# 'nickname': self.nickname,
# 'alias': self.alias,
# 'metaphone': self.name_metaphone
# },
# 'address': {
# 'house_number': self.house_number,
# 'street_prefix': self.street_prefix,
# 'street_name': self.street_name,
# 'street_type': self.street_type,
# 'street_suffix': self.street_suffix,
# 'street_metaphone': self.street_metaphone,
# 'unit': self.unit,
# 'city': self.city,
# 'zipcode': self.zipcode
# },
# 'contact_info': {
# 'email': self.email,
# 'phone1': self.phone1,
# 'phone2': self.phone2
# },
# 'voter_info': {
# 'voter_id': self.voter_id,
# 'precinct_id': self.precinct_id,
# 'dob': self.dob,
# 'gender': self.gender,
# 'reg_date': self.voter_reg_date
# },
# 'record_info': {
# 'created_at': self.created_at,
# 'updated_at': self.updated_at
# }
# }
@staticmethod
def get_all():
return Contact.query \
.order_by(
Contact.last_name, Contact.first_name, Contact.middle_name
) \
.all()
@staticmethod
def get_missing_pct():
return Contact.query \
.filter((Contact.precinct_id.is_(None))) \
.order_by(
Contact.last_name, Contact.first_name, Contact.middle_name
) \
.all()
class TimestampMixin(object):
created_at = db.Column(
db.DateTime, nullable=False, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow)