class ItemTax(BaseMixin, db.Model): tax_value = db.Column(db.Float(precision=2)) tax_amount = db.Column(db.Float(precision=2)) item_id = db.Column(UUID, db.ForeignKey('item.id'), index=True) tax_id = db.Column(UUID, db.ForeignKey('tax.id'), index=True) tax = db.relationship('Tax', foreign_keys=[tax_id]) item = db.relationship('Item', back_populates='taxes', foreign_keys=[item_id])
class PointStoreModelMixin(db.Model): __abstract__ = True value = db.Column(db.Float(), nullable=True) value_original = db.Column(db.Float(), nullable=True) value_raw = db.Column(db.String(), nullable=True) fault = db.Column(db.Boolean(), default=False, nullable=False) fault_message = db.Column(db.String()) ts_value = db.Column(db.DateTime()) ts_fault = db.Column(db.DateTime())
class PointStoreModelMixin(object): value = db.Column(db.Float(), nullable=True) value_original = db.Column(db.Float(), nullable=True) value_raw = db.Column(db.String(), nullable=True) fault = db.Column(db.Boolean(), default=False, nullable=False) fault_message = db.Column(db.String()) ts = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now())
class Item(BaseMixin, db.Model, ReprMixin): __repr_fields__ = ['id', 'order_id', 'product_id'] name = db.Column(db.String(55)) unit_price = db.Column(db.Float(precision=2)) quantity = db.Column(db.Float(precision=2)) discount = db.Column(db.FLOAT(precision=2), default=0, nullable=False) stock_adjust = db.Column(db.Boolean(), default=False) order_id = db.Column(db.ForeignKey('order.id'), nullable=True, index=True) stock_id = db.Column(db.ForeignKey('stock.id'), nullable=True, index=True) order = db.relationship('Order', foreign_keys=[order_id], single_parent=True, back_populates='items', cascade="all, delete-orphan") taxes = db.relationship('ItemTax', uselist=True, cascade='all, delete-orphan', back_populates='item') stock = db.relationship('Stock', foreign_keys=[stock_id], single_parent=True, back_populates='order_items') @hybrid_property def total_price(self): return float(self.unit_price * self.quantity) @hybrid_property def discounted_total_price(self): return float(self.discounted_unit_price * self.quantity) @hybrid_property def discounted_unit_price(self): return float(self.unit_price - (self.unit_price * self.discount) / 100) @hybrid_property def discount_amount(self): return float((self.total_price * self.discount) / 100) @hybrid_property def is_combo(self): return self.combo_id is not None @hybrid_property def store_id(self): return self.order.store_id @store_id.expression def store_id(self): return select([Order.store_id ]).where(Order.id == self.order_id).as_scalar()
class Item(BaseMixin, db.Model, ReprMixin): __repr_fields__ = ['id', 'order_id', 'product_id'] unit_price = db.Column(db.Float(precision=2)) quantity = db.Column(db.Float(precision=2)) discount = db.Column(db.FLOAT(precision=2), default=0, nullable=False) parent_id = db.Column(UUID, db.ForeignKey('item.id'), nullable=True, index=True) product_id = db.Column(UUID, db.ForeignKey('product.id'), nullable=True, index=True) order_id = db.Column(UUID, db.ForeignKey('order.id'), nullable=True, index=True) stock_id = db.Column(UUID, db.ForeignKey('stock.id'), nullable=True, index=True) combo_id = db.Column(UUID, db.ForeignKey('combo.id'), nullable=True, index=True) parent = db.relationship('Item', uselist=False, remote_side='Item.id') children = db.relationship('Item', remote_side='Item.parent_id') product = db.relationship('Product', foreign_keys=[product_id]) order = db.relationship('Order', foreign_keys=[order_id], single_parent=True, back_populates='items', cascade="all, delete-orphan") taxes = db.relationship('ItemTax', uselist=True, cascade='all, delete-orphan', back_populates='item') add_ons = db.relationship('ItemAddOn', uselist=True, cascade='all, delete-orphan', back_populates='item') stock = db.relationship('Stock', foreign_keys=[stock_id], single_parent=True, back_populates='order_items') @hybrid_property def total_price(self): return float(self.unit_price * self.quantity) @hybrid_property def discounted_total_price(self): return float(self.discounted_unit_price * self.quantity) @hybrid_property def discounted_unit_price(self): return float(self.unit_price-(self.unit_price * self.discount)/100) @hybrid_property def discount_amount(self): return float((self.total_price*self.discount)/100) @hybrid_property def is_combo(self): return self.combo_id is not None @hybrid_property def retail_shop_id(self): return self.order.retail_shop_id @retail_shop_id.expression def retail_shop_id(self): return select([Order.retail_shop_id]).where(Order.id == self.order_id).as_scalar()
class Stock(db.Model, BaseMixin, ReprMixin): __repr_fields__ = ['id', 'product_id'] purchase_amount = db.Column(db.Float(precision=2)) selling_amount = db.Column(db.Float(precision=2)) units_purchased = db.Column(db.SmallInteger, nullable=False) batch_number = db.Column(db.String(25), nullable=True) expiry_date = db.Column(db.Date, nullable=False) purchase_date = db.Column(db.Date, nullable=True, default=db.func.current_timestamp()) is_sold = db.Column(db.Boolean(), default=False, index=True) distributor_bill_id = db.Column(db.Integer, db.ForeignKey('distributor_bill.id'), nullable=False, index=True) product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False, index=True) distributor_bill = db.relationship('DistributorBill', single_parent=True, back_populates='purchased_items') product = db.relationship('Product', single_parent=True, foreign_keys=product_id) order_items = db.relationship('Item', uselist=True, back_populates='stock', lazy='dynamic') @hybrid_property def units_sold(self): total_sold = self.order_items.with_entities(func.Sum(Item.quantity))\ .filter(Item.stock_id == self.id).scalar() if total_sold: if total_sold >= self.units_purchased and not self.is_sold: self.is_sold = True db.session.commit() return total_sold else: return 0 @units_sold.expression def units_sold(cls): return select([func.coalesce(func.Sum(Item.quantity), 0) ]).where(Item.stock_id == cls.id).as_scalar()
class Entry(db.Model): id = db.Column(db.Integer, primary_key=True) product_id = db.Column(db.Integer, db.ForeignKey('product.id')) location_id = db.Column(db.Integer, db.ForeignKey('location.id')) # Decimal(10,2) is a number with 8 digits before the decimal and 2 digts after price = db.Column(db.Float(10, 2)) date = db.Column(db.DateTime)
class Discount(BaseMixin, db.Model, ReprMixin): name = db.Column(db.String(55), nullable=True) value = db.Column(db.Float(precision=2), nullable=False) type = db.Column(db.Enum('PERCENTAGE', 'FIXED', name='varchar'), nullable=False, default='PERCENTAGE') orders = db.relationship('Order', secondary='order_discount')
class MovieModel(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(120)) popularity = db.Column(db.Integer) director = db.Column(db.String(120)) imdb_score = db.Column(db.Float(precision=2)) genre = db.Column(ARRAY(db.String)) def to_json(self): data = { "id": self.id, "name": self.name, "99popularity": self.popularity, "director": self.director, "imdb_score": self.imdb_score, "genre": self.genre } return data @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).first() @classmethod def find_by_id(cls, _id): return cls.query.get(_id)
class Order(BaseMixin, db.Model, ReprMixin): __repr_fields__ = ['id', 'customer_id'] edit_stock = db.Column(db.Boolean(), default=True) sub_total = db.Column(db.Float(precision=2), default=0, nullable=True) total = db.Column(db.Float(precision=2), default=0, nullable=True) amount_paid = db.Column(db.Float(precision=2), default=0, nullable=True) auto_discount = db.Column(db.Float(precision=2), default=0, nullable=True) is_void = db.Column(db.Boolean(), default=False) invoice_number = db.Column(db.Integer) reference_number = db.Column(db.String(12), nullable=True) customer_id = db.Column(UUID, db.ForeignKey('customer.id'), nullable=True, index=True) user_id = db.Column(UUID, db.ForeignKey('user.id'), nullable=False, index=True) address_id = db.Column(UUID, db.ForeignKey('address.id'), nullable=True, index=True) retail_shop_id = db.Column(UUID, db.ForeignKey('retail_shop.id'), nullable=False, index=True) current_status_id = db.Column(UUID, db.ForeignKey('status.id'), nullable=True, index=True) items = db.relationship('Item', uselist=True, back_populates='order', lazy='dynamic', cascade="all, delete-orphan") customer = db.relationship('Customer', foreign_keys=[customer_id]) created_by = db.relationship('User', foreign_keys=[user_id]) address = db.relationship('Address', foreign_keys=[address_id]) retail_shop = db.relationship('RetailShop', foreign_keys=[retail_shop_id]) discounts = db.relationship('Discount', secondary='order_discount', uselist=True) denominations = db.relationship('Denomination', secondary='order_denomination', uselist=False) current_status = db.relationship('Status', uselist=False, foreign_keys=[current_status_id]) time_line = db.relationship('Status', secondary='order_status') @hybrid_property def total_discount(self): return sum([discount.value if discount.type == 'VALUE' else float(self.total*discount/100) for discount in self.discounts]) @hybrid_property def items_count(self): return self.items.with_entities(func.Count(Item.id)).scalar() @items_count.expression def items_count(cls): return select([func.Count(Item.id)]).where(Item.order_id == cls.id).as_scalar() @hybrid_property def amount_due(self): if self.total and self.amount_paid: return self.total - self.amount_paid return self.total
class CustomerTransaction(BaseMixin, db.Model, ReprMixin): amount = db.Column(db.Float(precision=2), nullable=False, default=0) customer_id = db.Column(UUID, db.ForeignKey('customer.id'), nullable=False, index=True) customer = db.relationship('Customer', foreign_keys=[customer_id])
class GameScore(db.Model): __tablename__ = 'game_score' id = db.Column(db.Integer, primary_key=True) game_id = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False) white_score = db.Column(db.Float(precision=1), default=0, nullable=False) black_score = db.Column(db.Float(precision=1), default=0, nullable=False) game = db.relationship("ChessGame", back_populates="score") @property def winner(self): """ Retrieve winner :return: """ winner = None if self.white_score == 1: winner = self.game.white_player winner.color = Color.WHITE elif self.black_score == 1: winner = self.game.black_player winner.color = Color.BLACK return winner @property def loser(self): """ Retrieve loser :return: """ loser = None if self.white_score == 0: loser = self.game.white_player loser.color = Color.WHITE elif self.black_score == 0: loser = self.game.black_player loser.color = Color.BLACK return loser def __repr__(self): return "<GameScore(id='{}', white_score='{}', black_score='{}', game_id='{}')>".format( self.id, self.white_score, self.black_score, self.game_id)
class Tax(BaseMixin, db.Model, ReprMixin): name = db.Column(db.String(25), nullable=False, index=True) value = db.Column(db.Float(precision=2), nullable=False) is_disabled = db.Column(db.Boolean(), default=False) store_id = db.Column(db.ForeignKey('store.id', ondelete='CASCADE'), index=True, nullable=False) store = db.relationship('Store', foreign_keys=[store_id], uselist=False, backref='taxes') products = db.relationship('Product', back_populates='taxes', secondary='product_tax', lazy='dynamic') UniqueConstraint(name, store_id)
class Tax(db.Model, BaseMixin, ReprMixin): name = db.Column(db.String(25), nullable=False) value = db.Column(db.Float(precision=2), nullable=False) is_disabled = db.Column(db.Boolean(), default=False) retail_shop_id = db.Column( db.Integer, db.ForeignKey('retail_shop.id', ondelete='CASCADE')) retail_shop = db.relationship('RetailShop', foreign_keys=[retail_shop_id], uselist=False, backref='taxes') products = db.relationship('Product', back_populates='taxes', secondary='product_tax', lazy='dynamic')
class Order(db.Model): id = db.Column(UUIDType(binary=False), primary_key=True, default=generate_uuid) cart_id = db.Column(UUIDType(binary=False), db.ForeignKey('cart.id')) date_ordered = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) subtotal = db.Column(db.Float(), nullable=False) def __repr__(self): return f"Order(''{self.cart_id}', '{self.date_ordered}', '{self.subtotal}')" def serialize(self): return { "id": self.id, "cart_id": self.cart_id, "date_ordered": self.date_ordered, "subtotal": self.subtotal }
class Product(db.Model): id = db.Column(UUIDType(binary=False), primary_key=True, default=generate_uuid) title = db.Column(db.String(120), nullable=False) price = db.Column(db.Float(), nullable=False) inventory_count = db.Column(db.Integer(), nullable=False) cart_items = db.relationship('CartItem', backref='product', lazy=True, cascade="delete") def __repr__(self): return f"Product('{self.title}', '{self.price}', '{self.inventory_count}')" def serialize(self): return { "id": self.id, "title": self.title, "price": self.price, "inventory_count": self.inventory_count, "cart_item_ids": [item.id for item in self.cart_items] }
class BACnetPointStoreModel(db.Model): __tablename__ = 'bac_points_store' point_uuid = db.Column(db.String, db.ForeignKey('bac_points.uuid'), primary_key=True, nullable=False) present_value = db.Column(db.Float(), nullable=False) ts = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) def __repr__(self): return f"PointStore(point_uuid = {self.point_uuid})" @classmethod def find_by_point_uuid(cls, point_uuid): return cls.query.filter_by(point_uuid=point_uuid).first() @classmethod def create_new_point_store_model(cls, point_uuid): point = cls.find_by_point_uuid(point_uuid) pv = 0.0 if point is not None: pv = point.relinquish_default return BACnetPointStoreModel(point_uuid=point_uuid, present_value=pv) def update(self) -> bool: res = db.session.execute(self.__table__ .update() .values(present_value=self.present_value) .where(and_(self.__table__.c.point_uuid == self.point_uuid, self.__table__.c.present_value != self.present_value))) updated: bool = bool(res.rowcount) if updated: """BACnet > Generic point value""" self.__sync_point_value_bp_to_gp_process() """BACnet > Modbus point value""" self.__sync_point_value_bp_to_mp_process() return updated def sync_point_value_bp_to_mp(self): response: Response = gw_request(f"/ps/api/mappings/mp_gbp/bacnet/{self.point_uuid}") if response.status_code == 200: gw_request( api=f"/ps/api/modbus/points_value/uuid/{json.loads(response.data).get('modbus_point_uuid')}", body={"value": self.present_value}, http_method=HttpMethod.PATCH ) def __sync_point_value_bp_to_mp_process(self): gevent.spawn(self.sync_point_value_bp_to_mp) def sync_point_value_bp_to_gp(self, generic_point_uuid: str): gw_request( api=f"/ps/api/generic/points_value/uuid/{generic_point_uuid}", body={"value": self.present_value}, http_method=HttpMethod.PATCH ) def __sync_point_value_bp_to_gp_process(self): mapping: BPGPointMapping = BPGPointMapping.find_by_bacnet_point_uuid(self.point_uuid) if mapping: gevent.spawn(self.sync_point_value_bp_to_gp, mapping.generic_point_uuid) @classmethod def sync_points_values_bp_to_gp_process(cls): mappings: List[BPGPointMapping] = BPGPointMapping.find_all() for mapping in mappings: point_store: BACnetPointStoreModel = BACnetPointStoreModel.find_by_point_uuid(mapping.bacnet_point_uuid) if point_store: point_store.__sync_point_value_bp_to_gp_process()
class Threshold(db.Model): __tablename__ = 'thresholds' id = db.Column(db.Integer, primary_key=True) soil_ph_min = db.Column(db.Float(precision=4)) soil_ph_max = db.Column(db.Float(precision=4)) soil_temp_min = db.Column(db.Float(precision=4)) soil_temp_max = db.Column(db.Float(precision=4)) soil_humi_min = db.Column(db.Float(precision=4)) soil_humi_max = db.Column(db.Float(precision=4)) air_temp_min = db.Column(db.Float(precision=4)) air_temp_max = db.Column(db.Float(precision=4)) air_humi_min = db.Column(db.Float(precision=4)) air_humi_max = db.Column(db.Float(precision=4)) air_pres_min = db.Column(db.Float(precision=4)) air_pres_max = db.Column(db.Float(precision=4)) created_at = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp(), nullable=False) updated_at = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp(), onupdate=db.func.current_timestamp(), nullable=False) # Device[one]-Threslhold[one] # backref = device device_id = db.Column(db.Integer, db.ForeignKey('devices.id')) def __init__(self, data): self.soil_ph_min = data.get('soil_ph_min') self.soil_ph_max = data.get('soil_ph_max') self.soil_temp_min = data.get('soil_temp_min') self.soil_temp_max = data.get('soil_temp_max') self.soil_humi_min = data.get('soil_humi_min') self.soil_humi_max = data.get('soil_humi_max') self.air_temp_min = data.get('air_temp_min') self.air_temp_max = data.get('air_temp_max') self.air_humi_min = data.get('air_humi_min') self.air_humi_max = data.get('air_humi_max') self.air_pres_min = data.get('air_pres_min') self.air_pres_max = data.get('air_pres_max') # self.device_id = data.get('device_id') def __repr__(self): return f'{self.__class__.__name__}()' def __str__(self): return f'<Threshold: {self.id}>' def save(self): db.session.add(self) db.session.commit() def update(self, data): for k, v in data.items(): setattr(self, k, v) db.session.commit() def delete(self): db.session.delete(self) db.session.commit() @staticmethod def restore(): from utils.default import threshold_data self.update(threshold_data) return 'restored'
class Measurement(db.Model): __tablename__ = 'measurements' id = db.Column(db.Integer, primary_key=True) soil_ph = db.Column(db.Float(precision=4)) soil_temp = db.Column(db.Float(precision=4)) soil_humi = db.Column(db.Float(precision=4)) air_temp = db.Column(db.Float(precision=4)) air_humi = db.Column(db.Float(precision=4)) air_pres = db.Column(db.Float(precision=4)) alarm_status = db.Column(db.Boolean()) batt_status = db.Column(db.Integer) timestamp = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp(), nullable=False) # Device[one]-Measurements[many] # backref = device device_id = db.Column(db.Integer, db.ForeignKey('devices.id')) def __init__(self, data): self.soil_ph = data.get('soil_ph') self.soil_temp = data.get('soil_temp') self.soil_humi = data.get('soil_humi') self.air_temp = data.get('air_temp') self.air_humi = data.get('air_humi') self.air_pres = data.get('air_pres') self.alarm_status = data.get('alarm_status') self.batt_status = data.get('batt_status') self.timestamp = data.get('timestamp') # self.device_id = data.get('device_id') def __repr__(self): return f'{self.__class__.__name__}()' def __str__(self): return f'<Measurement at: {self.timestamp}>' def save(self): db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit() @staticmethod def latest(self): latest = Measurement.query.order_by('-timestamp').first() if not last: return {'message': 'No measurements yet'} return last @staticmethod def last(self, number): measurements = Measurement.query.order_by('-timestamp').all().limit( number) if not last: return {'message': 'No measurements yet'} return measurements
class PriorityArrayModel(db.Model): __tablename__ = 'priority_array' point_uuid = db.Column(db.String, db.ForeignKey('points.uuid'), primary_key=True, nullable=False) _1 = db.Column(db.Float(), nullable=True) _2 = db.Column(db.Float(), nullable=True) _3 = db.Column(db.Float(), nullable=True) _4 = db.Column(db.Float(), nullable=True) _5 = db.Column(db.Float(), nullable=True) _6 = db.Column(db.Float(), nullable=True) _7 = db.Column(db.Float(), nullable=True) _8 = db.Column(db.Float(), nullable=True) _9 = db.Column(db.Float(), nullable=True) _10 = db.Column(db.Float(), nullable=True) _11 = db.Column(db.Float(), nullable=True) _12 = db.Column(db.Float(), nullable=True) _13 = db.Column(db.Float(), nullable=True) _14 = db.Column(db.Float(), nullable=True) _15 = db.Column(db.Float(), nullable=True) _16 = db.Column(db.Float(), nullable=True) def __repr__(self): return f"PriorityArray(uuid = {self.point_uuid})" def to_dict(self) -> dict: return { c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs } def delete_from_db(self): db.session.delete(self) db.session.commit() def update_with_no_commit(self, **kwargs): for key, value in kwargs.items(): if hasattr(self, key): setattr(self, key, value) return self.check_self() def update(self, **kwargs): highest_priority_value: float = self.update_with_no_commit(**kwargs) db.session.commit() return highest_priority_value def check_self(self) -> float: highest_priority_value: float = self.get_highest_priority_value_from_priority_array( self) if highest_priority_value is None: from src.models.point.model_point import PointModel point: PointModel = self.point self._16 = point.fallback_value highest_priority_value = point.fallback_value return highest_priority_value @classmethod def create_priority_array_model(cls, point_uuid, priority_array_write, fallback_value): priority_array = PriorityArrayModel(point_uuid=point_uuid, **priority_array_write) if cls.get_highest_priority_value_from_priority_array( priority_array) is None: priority_array._16 = fallback_value return priority_array @classmethod def find_by_point_uuid(cls, point_uuid): return cls.query.filter_by(point_uuid=point_uuid).first() @classmethod def get_highest_priority_value(cls, point_uuid): priority_array: PriorityArrayModel = cls.find_by_point_uuid(point_uuid) return cls.get_highest_priority_value_from_priority_array( priority_array) @classmethod def get_highest_priority_value_from_priority_array(cls, priority_array): if priority_array: for i in range(1, 17): value = getattr(priority_array, f'_{i}', None) if value is not None: return value return None
class BACnetPointModel(db.Model): __tablename__ = 'bac_points' uuid = db.Column(db.String(80), primary_key=True, nullable=False) object_type = db.Column(db.Enum(PointType), nullable=False) object_name = db.Column(db.String(80), nullable=False, unique=True) use_next_available_address = db.Column(db.Boolean(), nullable=False, default=False) address = db.Column(db.Integer(), nullable=True, unique=False) relinquish_default = db.Column(db.Float(), nullable=False) priority_array_write = db.relationship('PriorityArrayModel', backref='point', lazy=False, uselist=False, cascade="all,delete") event_state = db.Column(db.Enum(BACnetEventState), nullable=False) units = db.Column(db.Enum(Units), nullable=False) description = db.Column(db.String(120), nullable=False) enable = db.Column(db.Boolean(), nullable=False) fault = db.Column(db.Boolean(), nullable=True) data_round = db.Column(db.Integer(), nullable=True) data_offset = db.Column(db.Float(), nullable=True) cov = db.Column(db.Float(), nullable=True) source = db.Column(db.Enum(Sources), default=Sources.OWN) point_store = db.relationship('BACnetPointStoreModel', backref='point', lazy=False, uselist=False, cascade="all,delete") bp_gp_mapping = db.relationship('BPGPointMapping', backref='point', lazy=True, uselist=False, cascade="all,delete") created_on = db.Column(db.DateTime, server_default=db.func.now()) updated_on = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) def __repr__(self): return f"BACnetPointModel({self.uuid})" @validates('object_name') def validate_object_name(self, _, value): if not re.match("^([A-Za-z0-9_-])+$", value): raise ValueError( "object_name should be alphanumeric and can contain '_', '-'") return value @classmethod def find_all(cls, *args, **kwargs): if 'source' in kwargs: return cls.query.filter_by(source=kwargs['source']).all() return cls.query.all() @classmethod def find_by_uuid(cls, uuid): return cls.query.filter_by(uuid=uuid).first() @classmethod def filter_by_uuid(cls, uuid): return cls.query.filter_by(uuid=uuid) @classmethod def find_by_object_id(cls, object_type, address): return cls.query.filter((BACnetPointModel.object_type == object_type) & (BACnetPointModel.address == address)).first() @classmethod def find_by_object_name(cls, object_name): return cls.query.filter( BACnetPointModel.object_name == object_name).first() @classmethod def get_next_available_address(cls, current_address): addresses = cls.query.filter().with_entities( BACnetPointModel.address).all() sorted_addresses = sorted({address[0] for address in addresses} - {current_address}) available_address = 1 for address in sorted_addresses: if address == available_address: available_address += 1 else: break return available_address @classmethod def delete_all_from_db(cls): cls.query.delete() db.session.commit() def save_to_db(self, priority_array_write: dict): self.priority_array_write = PriorityArrayModel(point_uuid=self.uuid, **priority_array_write) self.point_store = BACnetPointStoreModel.create_new_point_store_model( self.uuid) db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class Temperature(db.Model): __tablename__ = 'temperatures' query_class = TemperatureQuery id = db.Column(db.Integer, primary_key=True, autoincrement=True) date = db.Column(db.Date) time = db.Column(db.Time) zipcode = db.Column(db.Integer) country_code = db.Column(db.String(3)) district = db.Column(db.String(60)) city_name = db.Column(db.String(60)) address = db.Column(db.String(60)) temperature = db.Column(db.Float(asdecimal=True)) unit = db.Column(db.String(2)) def __init__(self, document): self.from_json_object(document) def to_json_object(self): return { 'id': self.id, 'date': str(self.date), 'time': str(self.time), 'zipcode': self.zipcode, 'country_code': self.country_code, 'city_name': self.city_name, 'district': self.district, 'address': self.address, 'temperature': str(int(round(float(self.temperature)))), 'unit': self.unit } def from_json_object(self, document): for key, value in document.items(): setattr(self, key, value) @staticmethod def validation_schema(): return { 'date': { 'type': 'date', 'required': True }, 'time': { 'type': 'string', 'required': True }, 'zipcode': { 'type': 'integer', 'required': True }, 'country_code': { 'type': 'string', 'required': True, 'min': 2, 'max': 3 }, 'city_name': { 'type': 'string', 'required': True, 'max': 60 }, 'district': { 'type': 'string', 'required': True, 'max': 60 }, 'address': { 'type': 'string', 'required': True, 'max': 60 }, 'temperature': { 'type': 'float', 'required': True }, 'unit': { 'type': 'string', 'required': True, 'max': 2 }, }
class Product(BaseMixin, db.Model, ReprMixin): name = db.Column(db.String(127), unique=False, nullable=False, index=True) min_stock = db.Column(db.SmallInteger, nullable=False) auto_discount = db.Column(db.FLOAT(precision=2), default=0, nullable=False) description = db.Column(db.JSON(), nullable=True) sub_description = db.Column(db.Text(), nullable=True) is_disabled = db.Column(db.Boolean(), default=False) default_quantity = db.Column(db.Float(precision=2), default=1) quantity_label = db.Column(db.Enum('KG', 'GM', 'MG', 'L', 'ML', 'TAB', 'SYRUP', 'OTH', 'TAB', 'ML', 'CAP', 'INJ', 'BOTTLE', 'VAIL', 'KIT', 'STRIP', 'OTHER', 'PACK', 'SET', 'LTR', 'SACHET', 'PILLS', 'SYRINGE', 'SYRUP', 'ROLL', name='varchar'), default='OTH', nullable=True) is_loose = db.Column(db.Boolean(), default=False) barcode = db.Column(db.String(13), nullable=True) retail_shop_id = db.Column(UUID, db.ForeignKey('retail_shop.id', ondelete='CASCADE'), index=True, nullable=False) brand_id = db.Column(UUID, db.ForeignKey('brand.id'), index=True, nullable=False) retail_shop = db.relationship('RetailShop', foreign_keys=[retail_shop_id], uselist=False, back_populates='products') taxes = db.relationship('Tax', back_populates='products', secondary='product_tax') tags = db.relationship('Tag', back_populates='products', secondary='product_tag') brand = db.relationship('Brand', foreign_keys=[brand_id], uselist=False, back_populates='products') stocks = db.relationship('Stock', uselist=True, cascade="all, delete-orphan", lazy='dynamic') # distributors = db.relationship('Distributor', back_populates='products', secondary='product_distributor') combos = db.relationship('Combo', back_populates='products', secondary='combo_product') salts = db.relationship('Salt', back_populates='products', secondary='product_salt') add_ons = db.relationship('AddOn', back_populates='products', secondary='product_add_on') UniqueConstraint('barcode', 'retail_shop_id', 'bar_retail_un') @hybrid_property def available_stock(self): return self.stocks.filter(Stock.is_sold != True, Stock.expired == False)\ .with_entities(func.coalesce(func.Sum(Stock.units_purchased), 0)-func.coalesce(func.Sum(Stock.units_sold), 0)).scalar() @hybrid_property def available_stocks(self): return self.stocks.filter(and_(or_(Stock.is_sold != True), Stock.expired == False)).all() @available_stock.expression def available_stock(cls): return select([func.coalesce(func.Sum(Stock.units_purchased), 0)-func.coalesce(func.Sum(Stock.units_sold), 0)])\ .where(and_(or_(Stock.is_sold != True), Stock.product_id == cls.id)).as_scalar() @hybrid_property def mrp(self): mrp = self.stocks.filter(or_(Stock.is_sold != True))\ .with_entities(Stock.selling_amount).order_by(Stock.id).first() return mrp[0] if mrp else 0 @hybrid_property def similar_products(self): if len(self.salts): return [i[0] for i in Product.query.with_entities(Product.id) .join(ProductSalt, and_(ProductSalt.product_id == Product.id)) .filter(ProductSalt.salt_id.in_([i.id for i in self.salts])).group_by(Product.id) .having(func.Count(func.Distinct(ProductSalt.salt_id)) == len(self.salts)).all()] return [] @hybrid_property def last_purchase_amount(self): return self.stocks.order_by(desc(Stock.purchase_date)).first().purchase_amount @hybrid_property def last_selling_amount(self): return self.stocks.order_by(desc(Stock.purchase_date)).first().selling_amount @hybrid_property def stock_required(self): return abs(self.min_stock - self.available_stock) @stock_required.expression def stock_required(self): return self.min_stock - self.available_stock @hybrid_property def is_short(self): return self.min_stock >= self.available_stock @hybrid_property def product_name(self): return self.name @hybrid_property def distributors(self): return self.brand.distributors.all() @hybrid_property def brand_name(self): return self.brand.name @brand_name.expression def brand_name(self): return select([Brand.name]).where(Brand.id == self.brand_id).as_scalar()
class PointModel(ModelBase): __tablename__ = 'points' uuid = db.Column(db.String(80), primary_key=True, nullable=False) name = db.Column(db.String(80), nullable=False) device_uuid = db.Column(db.String, db.ForeignKey('devices.uuid'), nullable=False) enable = db.Column(db.Boolean(), nullable=False, default=True) history_enable = db.Column(db.Boolean(), nullable=False, default=True) history_type = db.Column(db.Enum(HistoryType), nullable=False, default=HistoryType.INTERVAL) history_interval = db.Column(db.Integer, nullable=False, default=15) writable = db.Column(db.Boolean, nullable=False, default=True) priority_array_write = db.relationship('PriorityArrayModel', backref='point', lazy=True, uselist=False, cascade="all,delete") cov_threshold = db.Column(db.Float, nullable=False, default=0) value_round = db.Column(db.Integer(), nullable=False, default=2) value_operation = db.Column(db.String, nullable=True, default="x + 0") input_min = db.Column(db.Float()) input_max = db.Column(db.Float()) scale_min = db.Column(db.Float()) scale_max = db.Column(db.Float()) tags = db.Column(db.String(320), nullable=True) point_store = db.relationship('PointStoreModel', backref='point', lazy=True, uselist=False, cascade="all,delete") point_store_history = db.relationship('PointStoreHistoryModel', backref='point', lazy=True, cascade="all,delete") history_sync_log = db.relationship('HistorySyncLogModel', backref='hsl', lazy=True, cascade="all,delete") fallback_value = db.Column(db.Float(), nullable=True) disable_mqtt = db.Column(db.Boolean, nullable=False, default=True) type = db.Column(db.Enum(GenericPointType), nullable=False, default=GenericPointType.FLOAT) unit = db.Column(db.String, nullable=True) __table_args__ = (UniqueConstraint('name', 'device_uuid'), ) def __repr__(self): return f"Point(uuid = {self.uuid})" @validates('name') def validate_name(self, _, value): if not re.match("^([A-Za-z0-9_-])+$", value): raise ValueError( "name should be alphanumeric and can contain '_', '-'") return value @validates('value_operation') def validate_value_operation(self, _, value): try: if value and value.strip(): eval_arithmetic_expression(value.lower().replace( 'x', str(random.randint(1, 9)))) except Exception: raise ValueError( "Invalid value_operation, must be a valid arithmetic expression" ) return value @classmethod def find_by_name(cls, network_name: str, device_name: str, point_name: str): results = cls.query.filter_by(name=point_name) \ .join(DeviceModel).filter_by(name=device_name) \ .join(NetworkModel).filter_by(name=network_name) \ .first() return results def save_to_db(self): self.point_store = PointStoreModel.create_new_point_store_model( self.uuid) super().save_to_db() def update_point_value( self, point_store: PointStoreModel, cov_threshold: float = None, priority_array_write_obj: PriorityArrayModel = None) -> bool: if not point_store.fault: if cov_threshold is None: cov_threshold = self.cov_threshold value = point_store.value_original if value is not None: value = self.apply_scale(value, self.input_min, self.input_max, self.scale_min, self.scale_max) value = self.apply_value_operation(value, self.value_operation) value = round(value, self.value_round) point_store.value = self.apply_point_type(value) return point_store.update(cov_threshold, priority_array_write_obj) @validates('tags') def validate_tags(self, _, value): """ Rules for tags: - force all tags to be lower case - if there is a gap add an underscore - no special characters """ if value is not None: try: return validate_json(value) except ValueError: raise ValueError('tags needs to be a valid JSON') return value @validates('history_interval') def validate_history_interval(self, _, value): if self.history_type == HistoryType.INTERVAL and value is not None and value < 1: raise ValueError( "history_interval needs to be at least 1, default is 15 (in minutes)" ) return value @validates('input_min') def validate_input_min(self, _, value): if value is not None and self.input_max is not None and value > self.input_max: raise ValueError("input_min cannot be greater than input_max") return value @validates('input_max') def validate_input_max(self, _, value): if self.input_min is not None and value is not None and self.input_min > value: raise ValueError("input_min cannot be greater than input_max") return value @validates('scale_min') def validate_scale_min(self, _, value): if value is not None and self.scale_max is not None and value > self.scale_max: raise ValueError("scale_min cannot be greater than scale_max") return value @validates('scale_max') def validate_scale_max(self, _, value): if self.scale_min is not None and value is not None and self.scale_min > value: raise ValueError("scale_min cannot be greater than scale_max") return value def update(self, **kwargs) -> bool: publish_cov: bool = self.disable_mqtt != kwargs.get('disable_mqtt') changed: bool = super().update(**kwargs) updated: bool = self.update_point_value(self.point_store, 0) if updated or publish_cov: self.publish_cov(self.point_store) return changed def update_point_store(self, value: float, priority: int, priority_array_write: dict): priority_array_write_obj, highest_priority_value = self.update_priority_value_without_commit( value, priority, priority_array_write) point_store = PointStoreModel(point_uuid=self.uuid, value_original=highest_priority_value) updated = self.update_point_value( point_store, priority_array_write_obj=priority_array_write_obj) if updated: self.publish_cov(point_store) def update_priority_value_without_commit(self, value: float, priority: int, priority_array_write: dict) -> \ (Union[PriorityArrayModel, None], Union[float, None]): if priority_array_write: priority_array: PriorityArrayModel = PriorityArrayModel.find_by_point_uuid( self.uuid) if priority_array: highest_priority_value: float = priority_array.update_with_no_commit( **priority_array_write) return priority_array, highest_priority_value return None, None if not priority: priority = 16 if priority not in range(1, 17): raise ValueError('priority should be in range(1, 17)') if priority: priority_array: PriorityArrayModel = PriorityArrayModel.find_by_point_uuid( self.uuid) if priority_array: highest_priority_value: float = priority_array.update_with_no_commit( **{f"_{priority}": value}) return priority_array, highest_priority_value return None, None def update_priority_value(self, value: float, priority: int, priority_array_write: dict): self.update_priority_value_without_commit(value, priority, priority_array_write) db.session.commit() @classmethod def apply_value_operation(cls, original_value, value_operation: str) -> float or None: """Do calculations on original value with the help of point details""" if original_value is None or value_operation is None or not value_operation.strip( ): return original_value return eval_arithmetic_expression(value_operation.lower().replace( 'x', str(original_value))) @classmethod def apply_scale(cls, value: float, input_min: float, input_max: float, output_min: float, output_max: float) \ -> float or None: if value is None or input_min is None or input_max is None or output_min is None or output_max is None: return value if input_min == input_max or output_min == output_max: return value scaled = ( (value - input_min) / (input_max - input_min)) * (output_max - output_min) + output_min if scaled > max(output_max, output_min): return max(output_max, output_min) elif scaled < min(output_max, output_min): return min(output_max, output_min) else: return scaled def apply_point_type(self, value: float): if value is not None: if self.type == GenericPointType.STRING: value = None elif self.type == GenericPointType.INT: value = round(value, 0) elif self.type == GenericPointType.BOOL: value = float(bool(value)) return value def publish_cov(self, point_store: PointStoreModel, device: DeviceModel = None, network: NetworkModel = None, force_clear: bool = False): if point_store is None: raise Exception('Point.publish_cov point_store cannot be None') if device is None: device = DeviceModel.find_by_uuid(self.device_uuid) if network is None: network = NetworkModel.find_by_uuid(device.network_uuid) if device is None or network is None: raise Exception( f'Cannot find network or device for point {self.uuid}') priority = self._get_highest_priority_field() if self.history_enable \ and (self.history_type == HistoryType.COV or self.history_type == HistoryType.COV_AND_INTERVAL) \ and network.history_enable \ and device.history_enable: PointStoreHistoryModel.create_history(point_store) db.session.commit() if not self.disable_mqtt: from src.services.mqtt_client import MqttClient MqttClient.publish_point_cov(Drivers.GENERIC.name, network, device, self, point_store, force_clear, priority) def _get_highest_priority_field(self): for i in range(1, 17): value = getattr(self.priority_array_write, f'_{i}', None) if value is not None: return i return 16
class Stock(BaseMixin, db.Model, ReprMixin): __repr_fields__ = ['id', 'purchase_date'] purchase_amount = db.Column(db.Float(precision=2), nullable=False, default=0) selling_amount = db.Column(db.Float(precision=2), nullable=False, default=0) units_purchased = db.Column(db.SmallInteger, nullable=False, default=1) batch_number = db.Column(db.String(25), nullable=True) expiry_date = db.Column(db.Date, nullable=True) is_sold = db.Column(db.Boolean(), default=False, index=True) default_stock = db.Column(db.Boolean, default=False, nullable=True) distributor_bill_id = db.Column(db.ForeignKey('distributor_bill.id'), nullable=True, index=True) product_id = db.Column(db.ForeignKey('product.id'), nullable=False, index=True) store_id = db.Column(db.ForeignKey('store.id'), nullable=False, index=True) distributor_bill = db.relationship('DistributorBill', single_parent=True, back_populates='purchased_items') product = db.relationship('Product', single_parent=True, foreign_keys=[product_id], back_populates='stocks') order_items = db.relationship('Item', uselist=True, back_populates='stock', lazy='dynamic') store = db.relationship('Store', uselist=False, foreign_keys=[store_id]) @hybrid_property def units_sold(self): total_sold = self.order_items.with_entities(func.Sum(Item.quantity)) \ .filter(Item.stock_id == self.id, Item.stock_adjust.isnot(True)).scalar() if total_sold: if total_sold >= self.units_purchased and not self.is_sold: self.is_sold = True db.session.commit() return total_sold else: return 0 @units_sold.expression def units_sold(cls): return select([func.coalesce(func.Sum(Item.quantity), 0)])\ .where(and_(Item.stock_id == cls.id, Item.stock_adjust.isnot(True))).as_scalar() @hybrid_property def product_name(self): return self.product.name @product_name.expression def product_name(self): return select([Product.name]).where(Product.id == self.product_id).as_scalar() @hybrid_property def expired(self): return self.expiry_date is not None and self.expiry_date < datetime.now().date() @expired.expression def expired(self): return and_(or_(self.is_sold.isnot(True)), func.coalesce(self.expiry_date, datetime.now().date()) < datetime.now().date()).label('expired') @hybrid_property def distributor_id(self): return self.distributor_bill.distributor_id @distributor_id.expression def distributor_id(self): return select([DistributorBill.distributor_id]).where( DistributorBill.id == self.distributor_bill_id).as_scalar() @hybrid_property def distributor_name(self): return self.distributor_bill.distributor.name @distributor_name.expression def distributor_name(self): return select([Distributor.name]).where(and_(DistributorBill.id == self.distributor_bill_id, Distributor.id == DistributorBill.distributor_id)).as_scalar() @hybrid_property def purchase_date(self): if self.distributor_bill_id: return self.distributor_bill.purchase_date return self.created_on @purchase_date.expression def purchase_date(cls): return select([DistributorBill.purchase_date]).where(DistributorBill.id == cls.distributor_bill_id).as_scalar() @hybrid_property def brand_name(self): return self.product.brand.name @brand_name.expression def brand_name(self): return select([Brand.name]).where(and_(Product.id == self.product_id, Brand.id == Product.brand_id)).as_scalar()