class WeighingManagementModel(db.Model):
__tablename__ = 'weighing_management'
weighing_management_id = db.Column(db.Integer, primary_key=True,
autoincrement=True)
date_and_hour_of_management = db.Column(db.DateTime(timezone=True),
nullable=False)
date_of_actual_weighing = db.Column(db.DateTime(timezone=True),
nullable=False)
date_of_old_weighing = db.Column(db.DateTime(timezone=True),
nullable=False)
old_weight = db.Column(db.Numeric(5, 2), nullable=False)
actual_weight = db.Column(db.Numeric(5, 2), nullable=False)
bovine_id = db.Column(db.Integer, db.ForeignKey('beef_cattle.bovine_id', ondelete='CASCADE'))
def __init__(self, date_of_old_weighing,
old_weight, actual_weight, bovine_id):
self.date_and_hour_of_management = datetime.utcnow()
self.date_of_old_weighing = date_of_old_weighing
self.date_of_actual_weighing = datetime.utcnow()
self.old_weight = old_weight
self.actual_weight = actual_weight
self.bovine_id = bovine_id
def to_json(self):
weighing_management = {
'weighing_management_id': self.weighing_management_id,
'date_and_hour_of_management': self.date_and_hour_of_management,
'date_of_old_weighing': self.date_of_old_weighing,
'date_of_actual_weighing': self.date_of_actual_weighing,
'bovine_id': self.bovine_id,
'old_weight': float(self.old_weight),
'actual_weight': float(self.actual_weight),
}
return weighing_management
class PurchaseOrder(db.Model):
__tablename__ = "purchase_order"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
created_on = db.Column(db.DateTime, nullable=False)
vendor_id = db.Column(db.Integer,
db.ForeignKey('vendor.id'),
nullable=False)
vendor = db.relationship('Vendor', backref="purchase_order", lazy="joined")
shipping = db.Column(db.Numeric(12, 2), nullable=False, default=0.00)
tax = db.Column(db.Numeric(12, 2), nullable=False, default=0.00)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
@hybrid_property
def sub_total(self):
price = 0
for line in self.line_item:
price += line.total_price
return price
@hybrid_property
def total(self):
return self.sub_total + self.shipping + self.tax
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
def __repr__(self):
return dir(self)
class cPosition(db.Model):
__tablename__ = 'agni_etl_stage_ff1'
id = db.Column(db.Integer, primary_key=True)
asofdate = db.Column(db.Date)
accname = db.Column(db.String(50))
symbol = db.Column(db.String(50))
costbasis = db.Column(db.Numeric(20, 2))
currentvalue = db.Column(db.Numeric(20, 2))
class Bovine(db.Model):
bovine_id = db.Column(db.Integer(), primary_key=True, autoincrement=True)
name = db.Column(db.String(150), nullable=True)
date_of_birth = db.Column(db.String(15), nullable=False)
breed = db.Column(db.String(25), nullable=True)
actual_weight = db.Column(db.Numeric(7, 2), nullable=False)
is_beef_cattle = db.Column(db.Boolean(), nullable=True)
farm_id = db.Column(db.Integer, db.ForeignKey('farm.farm_id'))
batch_of_beef = db.Column(db.Integer, nullable=True)
__mapper_args__ = {'polymorphic_identity': 'bovine'}
def init(self, farm_id, name, date_of_birth, breed, actual_weight,
is_beef_cattle, batch_of_beef):
self.farm_id = farm_id
self.name = name
self.breed = breed
self.actual_weight = actual_weight
self.date_of_birth = date_of_birth
self.is_beef_cattle = is_beef_cattle
self.batch_of_beef = batch_of_beef
def to_json(self):
return {
'bovine_id': self.bovine_id,
'farm_id': self.farm_id,
'breed': self.breed,
'actual_weight': float(self.actual_weight),
'date_of_birth': str(self.date_of_birth),
'is_beef_cattle': self.is_beef_cattle,
'batch_of_beef': self.batch_of_beef
}
class LineItem(db.Model):
__tablename__ = "line_item"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
quantity = db.Column(db.Integer, nullable=False)
total_price = db.Column(db.Numeric(12, 2), nullable=False)
purchase_order_id = db.Column(db.Integer,
db.ForeignKey('purchase_order.id'),
nullable=False)
vendor_component_id = db.Column(db.Integer,
db.ForeignKey('vendor_component.id'),
nullable=False)
purchase_order = db.relationship("PurchaseOrder",
backref='line_item',
lazy="joined")
vendor_component = db.relationship("VendorComponent", uselist=False)
@hybrid_property
def unit_price(self):
return self.total_price / self.quantity
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class CatalogItem(db.Model):
__tablename__ = "catalog_items"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(128), nullable=False)
description = db.Column(db.String(512), nullable=False)
image_url = db.Column(db.String(512), nullable=True)
points_cost = db.Column(db.Integer, nullable=False)
actual_cost = db.Column(db.Numeric(10, 2), nullable=False)
created_date = db.Column(db.DateTime, default=func.now(), nullable=False)
catalog_id = db.Column(db.Integer, nullable=False)
def __init__(self,
name="",
description="",
image_url="",
points_cost="",
actual_cost="",
catalog_id=""):
self.name = name
self.description = description
self.image_url = image_url
self.points_cost = points_cost
self.actual_cost = actual_cost
self.catalog_id = catalog_id
class OrderItem(db.Model):
__tablename__ = "order_items"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
order_id = db.Column(db.Integer, nullable=False)
# The following 2 key constraints may cause errors if the imports are not right
catalog_id = db.Column(db.Integer, nullable=False)
catalog_item_id = db.Column(db.Integer, nullable=False)
quantity = db.Column(db.Integer, nullable=False)
points_cost = db.Column(db.Integer, nullable=False)
actual_cost = db.Column(db.Numeric(10, 2), nullable=False)
created_date = db.Column(db.DateTime, default=func.now(), nullable=False)
def __init__(self,
order_id="",
catalog_id="",
catalog_item_id="",
quantity="",
actual_cost="",
points_cost=""):
self.order_id = order_id
self.catalog_id = catalog_id
self.catalog_item_id = catalog_item_id
self.quantity = quantity
self.actual_cost = actual_cost
self.points_cost = points_cost
class Expense(db.Model):
id = db.Column(db.Integer, primary_key=True)
category = db.Column(db.String(150), nullable=False)
amount = db.Column(db.Numeric(6, 2))
month_id = db.Column(db.Integer, db.ForeignKey('month.id'), nullable=False)
description = db.Column(db.Text())
short_descr = db.Column(db.String(83), nullable=True)
def __repr__(self):
return f"Expense('{self.id}', '{self.amount}')"
def __init__(self, **kwargs):
super(Expense, self).__init__(**kwargs)
self.set_short_descr()
def set_short_descr(self):
if self.description and len(self.description) > 80:
self.short_descr = self.description[:80] + '...'
else:
self.short_descr = None
class Property(db.Model):
"""
Properties data
* NOTE: If change schema, change the related config file as well
"""
__tablename__ = "properties"
__default__ = {
"Numeric": -1.00,
"Int": -1,
"String": "",
}
__default_val__ = {
type(Decimal('-1.00')): -1.00,
type(-1.00): -1.00,
type(-1): -1,
type(""): "",
}
# the var name would be the Column name in db
# NOTE: neg values meaning not known
id = db.Column(db.Integer, primary_key=True)
mlsname = db.Column(db.String, nullable=False) # e.g: CRMLS
listingkey_numeric = db.Column(db.String, nullable=False)
listing_id = db.Column(db.String, nullable=False)
status = db.Column(db.String, nullable=False)
list_price = db.Column(db.Numeric(16, 2), nullable=False) # money
## Nullable fields
# price
close_price = db.Column(db.Numeric(16, 2),
nullable=True,
default=__default__["Numeric"]) # money
original_price = db.Column(db.Numeric(16, 2),
nullable=True,
default=__default__["Numeric"]) # money
prev_price = db.Column(db.Numeric(16, 2),
nullable=True,
default=__default__["Numeric"]) # money
low_price = db.Column(db.Numeric(16, 2),
nullable=True,
default=__default__["Numeric"]) # money
reduce_price = db.Column(db.Numeric(16, 2),
nullable=True,
default=__default__["Numeric"]) # money
reduce_percent = db.Column(db.Numeric(8, 2),
nullable=True,
default=__default__["Numeric"]) # percentage
# location
# county should be related to city
city = db.Column(db.String, nullable=True, default=__default__["String"])
streetname = db.Column(db.String,
nullable=True,
default=__default__["String"])
postalcode = db.Column(db.String,
nullable=True,
default=__default__["String"])
postalcodep4 = db.Column(db.String,
nullable=True,
default=__default__["String"])
neighborhood = db.Column(db.String,
nullable=True,
default=__default__["String"])
latitude = db.Column(db.Numeric(12, 9),
nullable=True,
default=__default__["Numeric"])
longitude = db.Column(db.Numeric(12, 9),
nullable=True,
default=__default__["Numeric"])
# infos
beds = db.Column(db.SmallInteger,
nullable=True,
default=__default__["Int"])
baths = db.Column(db.SmallInteger,
nullable=True,
default=__default__["Int"])
yearbuilt = db.Column(db.SmallInteger,
nullable=True,
default=__default__["Int"])
area = db.Column(db.Numeric(16, 2),
nullable=True,
default=__default__["Numeric"])
squarefeet = db.Column(db.Numeric(16, 2),
nullable=True,
default=__default__["Numeric"])
pricepersquare = db.Column(db.Numeric(6, 2),
nullable=True,
default=__default__["Numeric"])
# misc
# a string that can convert to json.
# Any field that does not related to seaching, indexing or sorting goes here
misc = db.Column(db.String, nullable=True, default=__default__["String"])
# images
coverimage = db.Column(db.String,
nullable=True,
default=__default__["String"])
image = db.Column(db.String, nullable=True, default=__default__["String"])
# timestamps
created_on = db.Column(db.DateTime, server_default=db.func.now())
updated_on = db.Column(db.DateTime,
server_default=db.func.now(),
server_onupdate=db.func.now())
# others
total_view = db.Column(db.Integer, default=0)
# indexing
# __table_args__ = (db.Index('listingkey_numeric', 'listing_id', 'list_price'), )
# NOTE: ban list, not touched by any function
__banlist__ = ['id', '_sa_instance_state']
# NOTE: no update list, not touched by self.diff(),
# but works with self.to_dict() and self.from_dict()
__noupdatelist__ = [
'coverimage', 'image', 'reduce_price', 'reduce_percent', 'created_on',
'updated_on', 'total_view'
]
def __init__(self, **kwargs):
"""
NOTE: the super().__init__(**kwargs) do the same thing as the code
below does.
:param kwargs:
"""
super().__init__(**kwargs)
#could be negative, meaning price rised
getcontext().prec = 2
# reduce price and percent
if kwargs['original_price'] and kwargs['list_price']:
self.reduce_price = Decimal(kwargs['original_price']) - Decimal(
kwargs['list_price'])
if self.reduce_price > 0 and Decimal(kwargs['original_price']) > 0:
reduce_percent = 100 * self.reduce_price / Decimal(
kwargs['original_price'])
if abs(reduce_percent) > Decimal('10000.00'):
neg = reduce_percent / abs(reduce_percent)
self.reduce_percent = neg * Decimal(
'10000.00') # db.Numeric(6,2)
else:
self.reduce_percent = reduce_percent
def __repr__(self):
return '<Listingkey: %s>' % self.listingkey_numeric
def diff(self, kwargs):
"""
Check if the kwargs data is the same as the property instance
for each val in return, [0] is the old val, [1] is the new val
NOTE: this function changes NOTHING
:param kwargs: dict
:return: dict of diff
"""
var_dict = deepcopy(self.__dict__)
res = {}
for key, val in var_dict.items():
if key[0] == '_' or key in self.__banlist__ or key in self.__noupdatelist__:
continue
if key == 'misc': # compare a dict str
misc_self = eval(val)
misc_kwargs = eval(kwargs[key])
if misc_self != misc_kwargs:
for k in misc_self:
if misc_self[k] != misc_kwargs[k]:
res[k] = [misc_self[k], misc_kwargs[k]]
continue
# if kwargs[key] is empty, val will be stored as None
# val won't be None, know default val by self.__default__
# kwargs[key] can be None, if None, compare to the default val
if kwargs[key] is None:
if val != self.__default_val__[type(val)]:
# if val not in self.__default__.values(): # ugly but works
res[key] = [val, kwargs[key]]
else:
if val != type(val)(kwargs[key]): # type(val): type casting
res[key] = [val, kwargs[key]]
# if res is not empty, will update and call server_onupdate=db.func.now()
if not res:
self.updated_on = db.func.now()
return res
def to_dict(self):
"""
Gather all data of this property and return a dict
:return: dict
"""
var_dict = deepcopy(self.__dict__)
data = {}
for key, val in var_dict.items():
if key[0] == '_' or key in self.__banlist__:
continue
else:
data[key] = val
return data
def update(self):
"""
Timestamp and other caculated fields, called after self.from_dict()
"""
getcontext().prec = 2
if self.original_price != self.__default__[
"Numeric"] and self.list_price != self.__default__["Numeric"]:
self.reduce_price = Decimal(self.original_price) - Decimal(
self.list_price)
if self.reduce_price > 0 and Decimal(self.original_price) > 0:
reduce_percent = 100 * self.reduce_price / Decimal(
self.original_price)
if abs(reduce_percent) > Decimal('10000.00'):
neg = reduce_percent / abs(reduce_percent)
self.reduce_percent = neg * Decimal(
'10000.00') # db.Numeric(6,2)
else:
self.reduce_percent = reduce_percent
return True
def from_dict(self, data):
"""
Update data from a dict
:param data:
:return: True if any field is changed
"""
flag = False
var_dict = deepcopy(self.__dict__) # original data
for key, val in data.items():
if key in self.__banlist__:
continue
elif key in self.__dict__:
if val is None:
# should set to default value
defaultval = self.__default_val__[type(var_dict[key])]
setattr(self, key, defaultval)
else:
setattr(self, key, val)
flag = True
self.update()
return flag
def getallcolum(self):
members = [
attr for attr in dir(self)
if not callable(getattr(self, attr)) and not attr.startswith("__")
]
return members
class Rankings(db.Model):
""" Rankings Table """
__tablename__ = "rankings"
ranking_id = db.Column(db.Integer, primary_key=True)
ranking_price = db.Column(db.Integer, default=None)
ranking_price_weight = db.Column(db.Numeric(7, 4), default=14.2857)
ranking_location = db.Column(db.Integer, default=None)
ranking_location_weight = db.Column(db.Numeric(7, 4), default=14.2857)
ranking_space = db.Column(db.Integer, default=None)
ranking_space_weight = db.Column(db.Numeric(7, 4), default=14.2857)
ranking_parking = db.Column(db.Integer, default=None)
ranking_parking_weight = db.Column(db.Numeric(7, 4), default=14.2857)
ranking_privacy = db.Column(db.Integer, default=None)
ranking_privacy_weight = db.Column(db.Numeric(7, 4), default=14.2857)
ranking_laundry = db.Column(db.Integer, default=None)
ranking_laundry_weight = db.Column(db.Numeric(7, 4), default=14.2857)
ranking_common_space = db.Column(db.Integer, default=None)
ranking_common_space_weight = db.Column(db.Numeric(7, 4), default=14.2857)
ranking_aggregate = db.Column(db.Numeric(5, 2), default=0.00)
r_apartment_url = db.Column(db.String(145),
db.ForeignKey("apartment.apartment_url"))
r_user_random_id = db.Column(db.String(20),
db.ForeignKey("users.user_random_id"))
def __repr__(self):
""" representation of Rankings instance."""
return f"""<Ranking #{self.ranking_id}
for Apartment #{self.r_apartment_url}>"""
@classmethod
def get_rankings(cls, r_apartment_url):
""" get the rankings for an apartment by its url """
rankings = Rankings.query.filter_by(
r_apartment_url=r_apartment_url).first()
return rankings
def serialize_for_apartment(self):
""" Return a dictionary of the rankings """
return {
"ranking_id": self.ranking_id,
"ranking_price": self.ranking_price,
"ranking_price_weight": float(self.ranking_price_weight),
"ranking_location": self.ranking_location,
"ranking_location_weight": float(self.ranking_location_weight),
"ranking_space": self.ranking_space,
"ranking_space_weight": float(self.ranking_space_weight),
"ranking_parking": self.ranking_parking,
"ranking_parking_weight": float(self.ranking_parking_weight),
"ranking_privacy": self.ranking_privacy,
"ranking_privacy_weight": float(self.ranking_privacy_weight),
"ranking_laundry": self.ranking_laundry,
"ranking_laundry_weight": float(self.ranking_laundry_weight),
"ranking_common_space": self.ranking_common_space,
"ranking_common_space_weight":
float(self.ranking_common_space_weight),
"ranking_aggregate": float(self.ranking_aggregate),
"r_apartment_url": self.r_apartment_url,
}
class cInvestmentModel(db.Model):
__tablename__ = 'agni_portfolio_model'
id = db.Column(db.Integer, primary_key=True)
account = db.Column(db.String(50))
category = db.Column(db.String(50))
allocation = db.Column(db.Numeric(20, 2))
