class Picture(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
# type 0: avatar 1 => linkinde url
type = db.Column(db.Integer())
picturable_id = db.Column(db.Integer())
filename = db.Column(db.String(255))
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
__table_args__ = (
db.Index("idx_type_picturable_id", "type", "picturable_id"),
)
def __init__(self, type, picturable_id, filename):
self.type = type
self.picturable_id = picturable_id
self.filename = filename
@staticmethod
def get_filename_with_user_id(user_id):
picture = Picture.query.filter_by(type=0, picturable_id=user_id).order_by(Picture.id.desc()).first()
if picture:
return picture.filename
return None
class Patient(db.Model):
hcnumber = db.Column(db.String(14), primary_key=True)
fname = db.Column(db.String(30), nullable=False)
lname = db.Column(db.String(30), nullable=False)
birthday = db.Column(db.DateTime(), nullable=False)
gender = db.Column(db.String(1), nullable=False)
phone = db.Column(db.String(10), nullable=False)
email = db.Column(db.String(120), nullable=False)
address = db.Column(db.String(120), nullable=False)
password_hash = db.Column(db.String(100), nullable=False)
lastAnnual = db.Column(db.DateTime(), nullable=True)
def __repr__(self):
return '<Patient %r %r>' % self.fname % self.lname
# to iterate over a patient to retrieve specific attributes
def __iter__(self):
yield 'hcnumber', self.hcnumber
yield 'fname', self.fname
yield 'lname', self.lname
yield 'birthday', self.birthday
yield 'gender', self.gender
yield 'phone', self.phone
yield 'email', self.email
yield 'address', self.address
yield 'password_hash', self.password_hash
yield 'lastAnnual', self.lastAnnual
class File(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
# 0 => s3 file
type = db.Column(db.Integer())
is_active = db.Column(db.Boolean(), default=True, index=True)
foreign_id = db.Column(db.Integer(), nullable=False, index=True)
# like report's item id and itme id
item_id = db.Column(db.Integer(), nullable=False, index=True)
filename = db.Column(db.String(255))
# the raw name of upload
raw_name = db.Column(db.String(255))
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
__table_args__ = (
db.Index("idx_foreign_id_item_id_type_is_active", "foreign_id", 'item_id', 'type', 'is_active'),
)
def __init__(self, type, foreign_id, item_id, filename, raw_name, is_active=True):
self.type = type
self.foreign_id = foreign_id
self.item_id = item_id
self.filename = filename
self.is_active = is_active
self.raw_name = raw_name
class Event(db.Model):
"""
Model for Events platform
Attributes:
---------------
room_id : int
room_id in db
name : str
name of the event in db
location : str
location of the event in db
description: str
event description in db
start_time : str
event start time
end_time : str
event end time
"""
room_id = db.Column(db.Integer(),
ForeignKey('chatroom.room_id'),
primary_key=True)
name = db.Column(db.String(45))
location = db.Column(db.String(45))
description = db.Column(db.String(255))
start_time = db.Column(db.DateTime())
end_time = db.Column(db.DateTime())
def __init__(self, room_id, name, location, start_time, end_time,
description):
"""
Initialize the model Event
"""
self.room_id = room_id
self.name = name
self.location = location
self.start_time = start_time
self.end_time = end_time
self.description = description
def get_event_with_room_id(room_id):
"""
Get function for event with room_id
:return: return event with the room_id
"""
return Event.query.filter_by(room_id=room_id).first()
def delete_event_with_room_id(room_id):
"""
Delete function for event with room_id
"""
event = Event.get_event_with_room_id(room_id)
if event:
db.session.delete(event)
db.session.commit()
class Zipcode(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
geoid = db.Column(db.String(255), index=True, unique=True)
city_geoid = db.Column(db.String(255), db.ForeignKey('city.geoid',onupdate="SET NULL", ondelete="SET NULL"), index=True)
lat = db.Column(db.Float())
lng = db.Column(db.Float())
properties = db.Column(JSON)
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
class County(db.Model): __bind_key__ = 'wehomeproperty' id = db.Column(db.Integer(), index=True, primary_key=True) name = db.Column(db.String(255), index=True) geoid = db.Column(db.String(255), index=True, unique=True) cbsa_geoid = db.Column(db.String(255), index=True) lat = db.Column(db.Float()) lng = db.Column(db.Float()) properties = db.Column(JSON) created_at = db.Column(db.DateTime(), default=datetime.datetime.now) updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
class Administrator(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
user_id = db.Column(db.Integer(), db.ForeignKey("user.id", ondelete="CASCADE"),
nullable=False, index=True, unique=True)
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
@staticmethod
def is_user_admin(user_id):
admin = Administrator.query.filter_by(user_id=user_id).first()
return admin is not None
class State(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
geoid = db.Column(db.String(255), index=True, unique=True)
name = db.Column(db.String(255))
name_abbr = db.Column(db.String(255))
lat = db.Column(db.Float())
lng = db.Column(db.Float())
properties = db.Column(JSON)
cities = db.relationship('City', backref='state')
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
class City(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
geoid = db.Column(db.String(255), index=True, unique=True)
area_geoid = db.Column(db.String(255), db.ForeignKey('area.geoid'), index=True)
state_geoid = db.Column(db.String(255), db.ForeignKey('state.geoid'),index=True)
zipcodes = db.relationship('Zipcode', backref='city')
neighbors = db.relationship('Neighbor', backref='city_')
name = db.Column(db.String(255), index=True)
lat = db.Column(db.Float())
lng = db.Column(db.Float())
properties = db.Column(JSON)
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
class IpQuery(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
ip = db.Column(db.String(255), index=True)
home_id = db.Column(db.String(255))
source_name = db.Column(db.String(255))
date = db.Column(db.Date())
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
def __init__(self, ip, home_id, source_name, date):
self.ip = ip
self.home_id = home_id
self.source_name = source_name
self.date = date
class AppUser(db.Model):
email = db.Column(db.String(255), primary_key=True)
name = db.Column(db.String(255))
first_name = db.Column(db.String(255))
last_name = db.Column(db.String(255))
picture_url = db.Column(db.String(500))
count_logins = db.Column(db.Integer(), default=0)
last_login = db.Column(db.DateTime())
date_joined = db.Column(db.DateTime())
def __init__(self, **kwargs):
super(AppUser, self).__init__(**kwargs)
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class Offer(db.Model):
id = db.Column(db.Integer(), primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
asal = db.Column(db.String(255))
tujuan = db.Column(db.String(255))
fee = db.Column(db.Integer())
time = db.Column(db.DateTime())
passenger = db.Column(db.Integer)
color = db.Column(db.Integer)
@staticmethod
def get_all_offers():
offers = Offer.query.all()
offers = {
x.id: {
'user_id': x.user_id,
'asal': x.asal,
'tujuan': x.tujuan,
'fee': x.fee,
'time': x.time,
'passenger': x.passenger,
'passengers': [y.id for y in x.passenger_list],
'color': x.color
}
for x in offers
}
return offers
class User_info(db.Model):
"""
Model for more specific user information
Attributes
------------------
user_id : int
user id from db
created_at ：str
created date and time from db
phone : int
user phone number from db
"""
user_id = db.Column(db.Integer(), ForeignKey('user.id'), primary_key=True)
created_at = db.Column(db.DateTime())
phone = db.Column(db.Integer())
def __init__(self, user_id, created_at, phone):
"""
Initialize User_Info model
"""
self.user_id = user_id
self.created_at = created_at
self.phone = phone
class User(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
name = db.Column(db.String(255), index=True, unique=True)
rules = relationship("Rule", secondary=user_rule_association, back_populates="users")
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
@staticmethod
def get_user_with_name_or_id(id=None, name=None):
if id:
user = User.query.filter_by(id=id).first()
elif name:
user = User.query.filter_by(name=name).first()
if user:
return user
else:
return None
class CensusReport(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
type = db.Column(db.Integer()) # Refer utils.type.CENSUS_REPORT
geoid = db.Column(db.String(255))
census = db.Column(JSON)
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
__table_args__ = (
db.Index("idx_census_report", "type", 'geoid'),
)
def __init__(self, type, geoid, census):
self.type = type
self.geoid = geoid
self.census = census
class Request(db.Model):
id = db.Column(db.Integer(), primary_key=True)
person_id = db.Column(db.Integer(),
db.ForeignKey('person.id'),
nullable=False)
product_id = db.Column(db.Integer(),
db.ForeignKey('product.id'),
nullable=False)
request_justfication = db.Column(db.Text())
request_date = db.Column(db.DateTime())
class Album(db.Model):
"""A Photo Album"""
__tablename__ = 'albums'
album_id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), unique=True)
description = db.Column(db.String(255))
key_photo_id = db.Column(db.Integer(), db.ForeignKey('photos.photo_id'))
created_at = db.Column(db.DateTime(timezone=True))
edited_at = db.Column(db.DateTime(timezone=True))
photos = db.relationship("Photo", backref="album")
@classmethod
def set_time(self):
"""Return current date time object."""
return datetime.now()
class Area(db.Model):
__bind_key__ = 'wehomeproperty'
id = db.Column(db.Integer(), index=True, primary_key=True)
geoid = db.Column(db.String(255), index=True, unique=True)
cities = db.relationship('City', backref='area')
name = db.Column(db.String(255), index=True)
eng_name = db.Column(db.String(255), index=True)
lat = db.Column(db.Float())
lng = db.Column(db.Float())
layer_type = db.Column(db.String(255), index=True)
properties = db.Column(JSON())
# for IRR
property_tax = db.Column(db.Float())
vaccancy_rate = db.Column(db.Float())
property_management_fee = db.Column(db.Float())
leasing_commission = db.Column(db.Float())
insurance_cost = db.Column(db.Float())
repair = db.Column(db.Float())
cap_ex = db.Column(db.Float())
acquisition_cost = db.Column(db.Float())
disposition_cost = db.Column(db.Float())
rent_growth = db.Column(db.Float())
#
down_payment = db.Column(db.Float())
loan_interest_rate = db.Column(db.Float())
expenses = db.Column(db.Float())
closing_costs_misc = db.Column(db.Float())
#
history = db.Column(db.Text())
block_villa_median = db.Column(db.Float())
block_apartment_median = db.Column(db.Float())
deal_average_price = db.Column(db.Float())
list_average_price = db.Column(db.Float())
occ_rate_long = db.Column(db.Float())
occ_rate_airbnb = db.Column(db.Float())
return_long = db.Column(db.Float())
return_airbnb = db.Column(db.Float())
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
class Task(db.Model):
class STATUS:
COMPLETED = 'COMPLETED'
IN_PROGRESS = 'IN_PROGRESS'
id = db.Column(db.Integer(), primary_key=True)
date = db.Column(db.DateTime())
task = db.Column(db.String(255))
user_id = db.Column(db.String(255))
status = db.Column(db.String(255))
def __init__(self, task, user_id, status):
self.date = datetime.utcnow().date()
self.task = task
self.user_id = user_id
self.status = status
@staticmethod
def get_latest_tasks():
# import pdb;pdb.set_trace()
user_to_task = {}
result = db.engine.execute(
"""SELECT date, task, t.user_id, status from task t
INNER JOIN (SELECT user_id, max(date) as MaxDate from task group by user_id) tm
on t.user_id = tm.user_id and t.date = tm.MaxDate""")
for t in result:
# Task.query.join(subq, and_(Task.user_id == subq.user_id, Task.date == subq.date))
# all_tasks = Task.query.filter(Task.date >= datetime.utcnow().date())
# user_to_task = {}
# for t in all_tasks:
if t.user_id in user_to_task:
user_to_task.get(t.user_id).append(dict(t))
else:
user_to_task[t.user_id] = [dict(t)]
return user_to_task
@staticmethod
def get_tasks_for_user(user_id):
return Task.query.filter_by(user_id=user_id)
@property
def serialize(self):
"""Return object data in easily serializeable format"""
return {
'date' : self.date.strftime("%Y-%m-%d"),
'task' : self.task,
'user_id' : self.user_id,
'status' : self.status,
}
class ExtraFile(db.Model):
__bind_key__ = 'wehomeproperty'
'''for user or property extra file'''
id = db.Column(db.Integer(), index=True, primary_key=True)
# user_id or property_id
foreign_id = db.Column(db.Integer(), nullable=False, index=True)
file_id = db.Column(db.Integer())
# 0 => user inspection report 1 => user contract file
type = db.Column(db.Integer())
url = db.Column(db.String(255))
# 0 => discard 1 => used
is_active = db.Column(db.Boolean(), default=True, index=True)
created_at = db.Column(db.DateTime(), default=datetime.datetime.now)
updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
def __init__(self, foreign_id, file_id, type, url, is_active=1):
self.foreign_id = foreign_id
self.file_id = file_id
self.type = type
self.url = url
self.is_active = is_active
class Neighborhood(db.Model): __bind_key__ = 'wehomeproperty' id = db.Column(db.Integer(), index=True, primary_key=True) name = db.Column(db.String(255)) region_id = db.Column(db.Integer(), index=True, unique=True) city = db.Column(db.String(255)) state = db.Column(db.String(255)) past_rent_ratio = db.Column(db.Float()) past_increase_ratio = db.Column(db.Float()) forecast_rent_ratio = db.Column(db.Float()) forecast_increase_ratio = db.Column(db.Float()) home_value_rent_price_history = db.Column(db.Text()) home_value_sale_price_history = db.Column(db.Text()) market_health_index = db.Column(db.Float()) rent_final_point = db.Column(db.Float()) zestimate = db.Column(db.Float()) neighborhood_score = db.Column(db.Float()) area_geoid = db.Column(db.Integer(), index=True) # for IRR hoa = db.Column(db.Float()) property_tax = db.Column(db.Float()) vaccancy_rate = db.Column(db.Float()) property_management_fee = db.Column(db.Float()) leasing_commission = db.Column(db.Float()) insurance_cost = db.Column(db.Float()) repair = db.Column(db.Float()) cap_ex = db.Column(db.Float()) acquisition_cost = db.Column(db.Float()) disposition_cost = db.Column(db.Float()) rent_growth = db.Column(db.Float()) properties = db.Column(LONGTEXT()) created_at = db.Column(db.DateTime(), default=datetime.datetime.now) updated_at = db.Column(db.DateTime(), default=datetime.datetime.now, onupdate=datetime.datetime.now)
class ChallengeFollower(db.Model):
id = db.Column(db.Integer(), primary_key=True)
challenge_instance_id = db.Column(db.Integer(),
db.ForeignKey("challenge_instance.id"),
nullable=False)
challenge_instance = db.relationship('ChallengeInstance')
follower_id = db.Column(db.Integer(),
db.ForeignKey("user.id"),
nullable=False)
follower = db.relationship('User')
date_joined = db.Column(db.DateTime(), nullable=False)
def __init__(self, challenge, follower):
self.challenge_instance = challenge
self.follower = follower
self.date_joined = datetime.datetime.now()
class ChallengeInstance(db.Model):
id = db.Column(db.Integer(), primary_key=True)
challenge_id = db.Column(db.Integer,
db.ForeignKey("challenge.id"),
nullable=False)
challenge = db.relationship('Challenge')
date_created = db.Column(db.DateTime(), nullable=False)
creator_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=True)
creator = db.relationship('User')
def __init__(self, challenge, creator):
self.challenge = challenge
self.date_created = datetime.datetime.utcnow()
self.creator = creator
class Message(db.Model):
"""
Model for messages in chatroom
Attributes:
-----------------
id : int
message id in db
user_id : int
user_id of sender in db
room_id : int
room_id of the message existing in db
sendTime : str
message sent time from db
content : str
raw message
"""
id = db.Column(db.Integer(), primary_key=True)
user_id = db.Column(db.Integer())
room_id = db.Column(db.Integer())
sendTime = db.Column(db.DateTime())
content = db.Column(db.String(255))
def __init__(self, user_id, room_id, sendTime, content):
"""
Initialize model Message
"""
self.user_id = user_id
self.room_id = room_id
self.content = content
self.sendTime = sendTime
def get_messages_from_room_id(room_id):
"""
Get function for message using room_id
:return: return messages in the room
"""
return Message.query.filter_by(room_id=room_id).all()
def delete_messages_from_room_id(room_id):
"""
Delete messages using room_id
"""
Message.query.filter_by(room_id=room_id).delete()
db.session.commit()
class Event(db.Model, DictSerializable):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255))
start_date = db.Column(db.DateTime())
guid = db.Column(db.String(255), unique=True, index=True)
is_open = db.Column(db.Boolean())
outcomes = db.relationship('Outcome', backref='event', lazy='joined')
def __init__(self, name, start_date, guid):
self.name = name
self.start_date = start_date
self.is_open = True
self.guid = guid
def close_outcomes(self):
for outcome in self.outcomes:
outcome.close_date = datetime.datetime.now()
outcome.is_open = False
@staticmethod
def get_events():
events = Event.query.filter(Event.start_date >= datetime.datetime.now()) \
.outerjoin(Event.outcomes) \
.order_by(Event.start_date).all()
serialized_events = []
for event in events:
outcomes = []
for outcome in event.outcomes:
outcome_serialized = outcome.full_serialize()
# del outcome_serialized['event']
outcomes.append(outcome_serialized)
serialized = Event.serialize(event)
serialized['outcomes'] = outcomes
serialized_events.append(serialized)
return serialized_events
@staticmethod
def get_outcomes(event_id):
event = Event.query.get(event_id)
if event:
return event.outcomes
else:
return None
class ChallengeParticipant(db.Model):
id = db.Column(db.Integer(), primary_key=True)
challenge_instance_id = db.Column(db.Integer(),
db.ForeignKey("challenge_instance.id"),
nullable=False)
challenge_instance = db.relationship('ChallengeInstance')
participant_id = db.Column(db.Integer(),
db.ForeignKey("user.id"),
nullable=False)
participant = db.relationship('User')
date_joined = db.Column(db.DateTime(), nullable=False)
def __init__(self, challenge, participant):
self.challenge_instance = challenge
self.participant = participant
self.date_joined = datetime.datetime.now()
class Challenge(db.Model):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(200), nullable=False) # no empty names please
desc = db.Column(
db.Text(),
nullable=False) # we want to know what it is beyond a name too
date_created = db.Column(db.DateTime(), nullable=False)
preview_uri = db.Column(db.Text()) #preview URI
listing_uri = db.Column(db.Text()) #listing URI
creator_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=True)
creator = db.relationship('User') # who?
def __init__(self, name, desc, preview_uri, listing_uri, creator):
self.name = name
self.desc = desc
self.date_created = datetime.datetime.utcnow()
self.preview_uri = preview_uri
self.listing_uri = listing_uri
self.creator = creator
class Request(db.Model):
id = db.Column(db.Integer(), primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
asal = db.Column(db.String(255))
tujuan = db.Column(db.String(255))
time = db.Column(db.DateTime())
color = db.Column(db.Integer)
@staticmethod
def get_all_requests():
requests = Request.query.all()
requests = {
x.id: {
'user_id': x.user_id,
'asal': x.asal,
'tujuan': x.tujuan,
'time': x.time,
'color': x.color
}
for x in requests
}
return requests
class Escrow(db.Model, DictSerializable):
id = db.Column(db.Integer(), primary_key=True)
offer_id = db.Column(db.Integer(), db.ForeignKey('offer.id'))
bid_id = db.Column(db.Integer(), db.ForeignKey("bid.id"))
wallet_id = db.Column(db.Integer(), db.ForeignKey('wallet.id'))
comission = db.Column(
db.Numeric(precision=BTC_PRECISION,
scale=(BTC_PRECISION - BTC_NUMERAL)))
value = db.Column(
db.Numeric(precision=BTC_PRECISION,
scale=(BTC_PRECISION - BTC_NUMERAL)))
active = db.Column(db.Boolean())
payout_date = db.Column(db.DateTime())
incoming_hash = db.Column(db.String(255))
def __init__(self, offer_id, wallet_id, bid_id, comission, value):
self.offer_id = offer_id
self.wallet_id = wallet_id
self.bid_id = bid_id
self.comission = comission
self.value = value
self.active = True
class ChallengeComment(db.Model):
id = db.Column(db.Integer(), primary_key=True)
challenge_instance_id = db.Column(db.Integer(),
db.ForeignKey("challenge_instance.id"),
nullable=False)
challenge_instance = db.relationship('ChallengeInstance')
comment = db.Column(db.String(500),
nullable=False) #let's keep it short...
commenter_id = db.Column(db.Integer(),
db.ForeignKey("user.id"),
nullable=False)
commenter = db.relationship('User')
date_created = db.Column(db.DateTime(), nullable=False)
def __init__(self, challenge, comment, commenter):
self.challenge_instance = challenge
self.comment = comment
self.commenter = commenter
self.date_created = db.Column(db.DateTime(), nullable=False)
