class sdController(db.Model): __tablename__ = "sd31_controllers" id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20), comment="Name") display_name = db.Column(db.String(50), comment="Display name") comment = db.Column(db.Text, comment="Comment") create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time") cust_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Customer id") status = db.Column(db.Integer, server_default="0", comment="Device status flag. bit 0: warning, bit 1: error") model_id = db.Column(db.Integer, db.ForeignKey(sdDeviceModel.id), server_default="0", comment="Traffic Light model/part no") serial_no = db.Column(db.String(50), nullable=False, comment="Serial No") device = db.relationship("sdDevice", uselist=False, backref="controller") __table_args__ = ( db.UniqueConstraint("serial_no"), db.UniqueConstraint("cust_id", "name") ) def __repr__(self): return f"<sdController id={self.id}/name={self.name}/display_name={self.display_name}/sn={self.serial_no}>" @staticmethod def add(cust_id, controller): model = db.session.query(sdDeviceModel)\ .filter(sdDeviceModel.name == controller.get("model_name")).first() obj = sdController() obj.cust_id = cust_id obj.name = controller.get("name") obj.display_name = controller.get("display_name") obj.comment = controller.get("comment") obj.serial_no = controller.get("serial_no") obj.model = model return obj @staticmethod def update(cust_id, controller): sdn = controller.get("serial_no") model = db.session.query(sdDeviceModel)\ .filter(sdDeviceModel.name == controller.get("model_name")).first() obj = db.session.query(sdController).filter(sdController.serial_no == sdn).first() if obj: obj.cust_id = cust_id obj.name = controller.get("name") obj.display_name = controller.get("display_name") obj.comment = controller.get("comment") obj.serial_no = controller.get("serial_no") obj.model = model else: obj = sdController.add(cust_id, controller) return obj
class sdDeviceInfo(db.Model): __tablename__ = "sd23_device_infos" id = db.Column(db.Integer, primary_key=True, autoincrement=True) device_id = db.Column(db.Integer, db.ForeignKey(sdDevice.id), comment="Device id") comment = db.Column(db.Text, comment="Comment") create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time") cust_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Customer id") voltage = db.Column(db.Float, comment="Voltage") current = db.Column(db.Float, comment="Current") pf = db.Column(db.Float, comment="Power factor") power = db.Column(db.Float, comment="Power value") temperature = db.Column(db.Float, comment="Temperature") __table_args__ = (db.UniqueConstraint("cust_id", "device_id"), ) def __repr__(self): return ( f"<sdDeviceInfo id={self.id}/device_id={self.device_id}/cust_id={self.cust_id}" f"/voltage={self.voltage}/current={self.current}/pf={self.pf}/power={self.power}>" f"/temperature={self.temperature}/lumming={self.lumming}")
class Inventory(db.Model, TimestampMixin): """ Inventory information """ id = db.Column( db.Integer, primary_key=True, ) product_id = db.Column( db.Integer, db.ForeignKey("product.id"), ) store_id = db.Column( db.Integer, db.ForeignKey("store.id"), ) qty = db.Column( db.Integer, default=0, ) __table_args__ = ( db.UniqueConstraint( "store_id", "product_id", ), ) @hybrid_property def branch_number(self): store = Store.query.filter_by(id=self.store_id).first() return store.branch_number @hybrid_property def sku(self): product = Product.query.filter_by(id=self.product_id).first() return product.sku
class sdUserGroup(db.Model): __tablename__ = "sd13_user_groups" id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20), comment="Name", nullable=False) display_name = db.Column(db.String(50), comment="Display name") comment = db.Column(db.Text, comment="Comment") create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time") cust_id = db.Column(db.Integer, db.ForeignKey("sd10_customers.id"), comment="Customer id") rel_dg_ug = db.relationship('sdDeviceGroup', secondary=rel_dg_ug, backref='user_groups') rel_u_ug = db.relationship('sdUser', secondary=rel_u_ug, backref='user_groups') rel_role_ug = db.relationship('sdRole', secondary=rel_role_ug, backref='user_groups') __table_args__ = (db.UniqueConstraint("cust_id", "name"),) def __repr__(self): return f"<sdUserGroup id={self.id}/name={self.name}/display_name={self.display_name}/cust_id={self.cust_id}" @staticmethod def getall(cust_id): usergroups = db.session.query(sdUserGroup).filter(sdUserGroup.cust_id == cust_id).all() return usergroups @staticmethod def add(cust_id, name, display_name, comment): obj = sdUserGroup() obj.cust_id = cust_id obj.name = name obj.display_name = display_name obj.comment = comment return obj # delete_all_users: delete all users, device groups record corresponding to the user group from m2m table @staticmethod def delete_all_users(cust_id, user_list): user_groups = db.session.query(sdUserGroup).filter(sdUserGroup.cust_id == cust_id, sdUserGroup.id.in_(user_list)).all() # TAG clear() attribute is active only without lazy = dynamic for user_group in user_groups: dg_rels = user_group.rel_dg_ug dg_rels.clear() u_rels = user_group.rel_u_ug u_rels.clear() role_rels = user_group.rel_role_ug role_rels.clear() @staticmethod def delete(cust_id, user_list): sdUserGroup.query.filter(sdUserGroup.cust_id == cust_id, sdUserGroup.id.in_(user_list)).delete(synchronize_session=False) @staticmethod def update(cust_id, group_id, new_name, new_display_name, new_comment): obj = db.session.query(sdUserGroup).filter(sdUserGroup.cust_id == cust_id, sdUserGroup.id == group_id).first() obj.name = new_name if new_name else obj.name obj.display_name = new_display_name if new_display_name else obj.display_name obj.comment = new_comment if new_comment else obj.comment return obj # user join user group @staticmethod def join_users(cust_id, user_group_id, user_list): selected_user_group = db.session.query(sdUserGroup).filter( sdUserGroup.id == user_group_id, sdUserGroup.cust_id == cust_id).first() query_obj = db.session.query(sdUser).filter(sdUser.id.in_(user_list), sdUser.cust_id == cust_id).all() selected_user_group.rel_u_ug.extend(query_obj) # user leave user group @staticmethod def leave_users(user_group_id, user_list): db.session.query(rel_u_ug).filter(rel_u_ug.c.user_group_id == user_group_id).\ filter(rel_u_ug.c.user_id.in_(user_list)).\ delete(synchronize_session=False) @staticmethod def get_status_privilege(usergroup_obj, cust_id, flag, priv_set): # status_privilege = [] for ug_obj in usergroup_obj: # get roles of user group role_ug = ug_obj.rel_role_ug for role in role_ug: # get status privilege of role, filter cust_id status_privilege = sdStatusPrivilege.get_privilege(role.id, cust_id) if flag == 2: for priv_obj in status_privilege: # get status tuple priv_set.add(priv_obj.status_to) else: for priv_obj in status_privilege: # get status tuple priv_set.add((str(priv_obj.status_from), str(priv_obj.status_to))) return priv_set @staticmethod def give_user_id_get_roles(user_id): user_obj = db.session.query(sdUser).filter(sdUser.id == user_id).first() # get user groups of users usergroup_obj = user_obj.user_groups role_set = set() # Gathering data from models using relationship for ug_obj in usergroup_obj: # get roles of user group role_ug = ug_obj.rel_role_ug for role in role_ug: # get privilege of role role_set.add(role.id) return role_set @staticmethod def give_user_id_get_auths(user_id, flag, cust_id=None): user_obj = db.session.query(sdUser).filter(sdUser.id == user_id).first() # get user groups of users usergroup_obj = user_obj.user_groups priv_set = set() # Gathering data from models using relationship if flag == 0: for ug_obj in usergroup_obj: # get roles of user group role_ug = ug_obj.rel_role_ug for role in role_ug: # get privilege of role priv = role.privileges for route in priv: # get api route priv_set.add(route.api_route) else: priv_set = sdUserGroup.get_status_privilege(usergroup_obj, cust_id, flag, priv_set) return priv_set
class sdUser(db.Model): __tablename__ = "sd11_users" id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20), nullable=False, comment="Name") display_name = db.Column(db.String(50), comment="Display name") comment = db.Column(db.Text, comment="Comment") status = db.Column(db.Integer, server_default="0", comment="Status") create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time") password_hash = db.Column("password", db.String(64), comment="Password") cust_id = db.Column(db.Integer, db.ForeignKey("sd10_customers.id"), comment="Customer id") email = db.Column(db.String(100), comment="Email") telephone = db.Column(db.String(30), comment="Telephone number") line_id = db.Column(db.String(30), comment="LINE id") __table_args__ = (db.UniqueConstraint("cust_id", "name"), ) def __repr__(self): return f"<sdUser id={self.id}/name={self.name}/display_name={self.display_name}>" @property def password(self): raise AttributeError("password field cannot be read") @password.setter def password(self, new_password): self.password_hash = bcrypt.generate_password_hash( new_password).decode("utf-8") def check_password(self, password): return bcrypt.check_password_hash(self.password_hash, password) @staticmethod def search(cust_id, user_name): return sdUser.query.filter_by(cust_id=cust_id).filter_by( name=user_name).first() @staticmethod def getall(cust_id): users = db.session.query(sdUser).filter( sdUser.cust_id == cust_id).all() return users @staticmethod def getdetail(cust_id, user_list): users = db.session.query(sdUser).filter( sdUser.cust_id == cust_id, sdUser.id.in_(user_list)).all() return users @staticmethod def add(cust_id, name, display_name, password, email, telephone, comment): obj = sdUser() obj.cust_id = cust_id obj.name = name obj.display_name = display_name obj.password = password obj.email = email obj.telephone = telephone obj.comment = comment return obj # HINT delete_all_user_groups: delete all user groups record corresponding to the user from m2m table @staticmethod def delete_all_user_groups(cust_id, user_list): users = db.session.query(sdUser).filter( sdUser.cust_id == cust_id, sdUser.id.in_(user_list)).all() for user in users: ug_rels = user.user_groups ug_rels.clear() @staticmethod def delete(cust_id, user_list): sdUser.query.filter( sdUser.cust_id == cust_id, sdUser.id.in_(user_list)).delete(synchronize_session=False) @staticmethod def update(cust_id, user_id, name, display_name, password, email, telephone, comment): obj = db.session.query(sdUser).filter(sdUser.id == user_id).first() if obj: # HINT This method has too much if/else, # here should handle by Frontend, or use this method obj.password = password if password else obj.password obj.name = name if name else obj.name obj.display_name = display_name if display_name else obj.display_name obj.email = email if email else obj.email obj.telephone = telephone if telephone else obj.telephone obj.comment = comment if comment else obj.comment else: obj = sdUser() obj.cust_id = cust_id obj.name = name obj.display_name = display_name obj.password = password obj.email = email obj.telephone = telephone obj.comment = comment return obj @staticmethod def get_admin(cust_id): admin = db.session.query(sdUser).filter( sdUser.cust_id.in_(cust_id), sdUser.name == Constant.ADMIN).all() return admin
class sdDevice(db.Model): __tablename__ = "sd21_devices" id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20), comment="Name") display_name = db.Column(db.String(50), comment="Display name") comment = db.Column(db.Text, comment="Comment") create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time") dev_type = db.Column(db.Integer, server_default="0", comment="Device type: 0: unknown, 1: traffic light, 2: others") cust_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Customer id") vendor_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Vendor id") status = db.Column(db.Integer, server_default="0", comment="Device status flag(bit). 0: warning, bit 1: error,可持續新增") power_status = db.Column(db.Integer, comment="Device power status. null: unknown, 0: off, 1: on") device_group_id = db.Column(db.Integer, db.ForeignKey("sd22_device_groups.id"), comment="Device group id, null means ungroup") controller_id = db.Column(db.Integer, db.ForeignKey(sdController.id), comment="Controller id") led_id = db.Column(db.Integer, db.ForeignKey(sdLed.id), comment="LED id") vender_device_id = db.Column(db.String(25), comment="Vender device id") wgs_x = db.Column(db.Float, comment="GPS X") wgs_y = db.Column(db.Float, comment="GPS Y") address = db.Column(db.String(100), comment="Address") device_info = db.relationship("sdDeviceInfo", uselist=False, backref="device") __table_args__ = (db.UniqueConstraint("cust_id", "name"),) def __repr__(self): return ( f"<sdDevice id={self.id}/name={self.name}/display_name={self.display_name}/cust_id={self.cust_id}" f"/wgs_x={self.wgs_x}/wgs_y={self.wgs_y}/address={self.address}>" f"/controller_id={self.controller_id}/led_id={self.led_id}" ) @staticmethod def delete(cust_id, device_list): sdDevice.query.filter(sdDevice.cust_id == cust_id, sdDevice.id.in_(device_list)).delete(synchronize_session=False) @staticmethod def get_detail(cust_id, device_id_list): devices = db.session.query(sdDevice).filter(or_(sdDevice.cust_id == cust_id, sdDevice.vendor_id == cust_id), sdDevice.id.in_(device_id_list)).all() return devices @staticmethod def get_devices_in_groups(cust_id, group_id_list): id_list = [] device_id_tuple = db.session.query(sdDevice.id).filter(sdDevice.cust_id == cust_id, sdDevice.device_group_id.in_(group_id_list)).all() for device_id in device_id_tuple: id_list.append(device_id[0]) return id_list @staticmethod def add(cust_id, device, led, controller): obj = sdDevice() obj.cust_id = cust_id obj.name = device.get("name") obj.display_name = device.get("display_name") obj.comment = device.get("comment") obj.controller = controller obj.led = led obj.wgs_x = device.get('wgs_x') obj.wgs_y = device.get('wgs_y') obj.address = device.get('address') return obj @staticmethod def update(cust_id, device, data): obj = db.session.query(sdDevice).filter(sdDevice.id == device["id"]).first() if obj: obj.cust_id = cust_id obj.name = device.get("name") obj.display_name = device.get("display_name") obj.comment = device.get("comment") obj.wgs_x = device.get("wgs_x") obj.wgs_y = device.get("wgs_y") obj.address = device.get("address") # if threre is led or controller then update. led = data.get("led") if led: obj.led = led controller = data.get("controller") if controller: obj.controller = controller return obj