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