def insert_user(username, password):
mp = MysqlOp()
mp.op_sql('INSERT INTO user (username, password) VALUES (%s, %s)',
(username, user.hash_pwd(password)))
return mp.op_sql(
'INSERT INTO user_roles (user_id, role_id) VALUES (%s, %s)',
(mp.cur.lastrowid, 2))
def insert_sensor_data(temperature: str, humidity: str, uuid: str, fire: bool,
illumination: bool, solid: bool):
logger.info('insert_sensor_data')
return MysqlOp().op_sql(
'INSERT INTO sensor_data (hardware_uuid, temperature, humidity, is_fire, is_dry, is_illum) VALUES (%s, %s, %s, %s, %s, %s)',
(uuid, temperature, humidity, int(fire), int(solid),
int(illumination)))
def get_sensor_pagination(page, size, ordered, where_sql, *args):
logger.info('get_sensor_pagination')
return MysqlOp().select_all(
f"SELECT sensor_data.id, hardware_uuid, temperature, humidity, is_fire, is_dry, is_illum, record_time, `name` "
f"FROM sensor_data LEFT JOIN hardware ON sensor_data.hardware_uuid = hardware.uuid "
f" {where_sql} ORDER BY {ordered} ASC LIMIT %s OFFSET %s",
(*args, size, (page - 1) * size))
def count_total_user_hardware(where_sql, *args):
logger.info('count_total_user_hardware')
return MysqlOp().select_one(
f'SELECT COUNT(`user_id`) as len from user_hardware '
f'LEFT JOIN `user` ON `user`.id = user_hardware.user_id '
f'LEFT JOIN hardware ON hardware.uuid = user_hardware.hardware_uuid '
f'{where_sql}', (*args, ))
def get_user_pagination(page, size, where_sql, *args):
logger.info('get_user_pagination')
return MysqlOp().select_all(
f"SELECT `user`.id, username, role.`name` FROM `user` "
f"LEFT JOIN user_roles ON `user`.id = user_roles.user_id "
f"LEFT JOIN role ON user_roles.role_id = role.id "
f"{where_sql} LIMIT %s OFFSET %s", (*args, size, (page - 1) * size))
def get_rfid_pagination(page, size, ordered, where_sql, *args):
logger.info('get_sensor_pagination')
return MysqlOp().select_all(
f"SELECT username, log_time, hardware_uuid, `name` FROM RFID_log "
f"LEFT JOIN `user` ON RFID_log.user_id = `user`.id "
f"LEFT JOIN hardware ON RFID_log.hardware_uuid = hardware.uuid "
f"{where_sql} ORDER BY {ordered} ASC LIMIT %s OFFSET %s",
(*args, size, (page - 1) * size))
def get_hardware_pagination_by_username(username, page, size, ordered,
where_sql, *args):
logger.info('get_hardware_pagination_by_username')
return MysqlOp().select_all(
f'SELECT * FROM hardware WHERE uuid IN '
f'(SELECT hardware_uuid FROM user_hardware WHERE user_id = (SELECT id FROM `user` WHERE username = %s)) {where_sql} '
f'ORDER BY {ordered} ASC LIMIT %s OFFSET %s',
(username, *args, size, (page - 1) * size))
def get_sensor_pagination_by_username(username, page, size, ordered, where_sql, *args):
logger.info('get_hardware_pagination_by_username')
return MysqlOp().select_all(
f'SELECT sensor_data.id, hardware_uuid, temperature, humidity, is_fire, is_dry, is_illum, record_time, `name` '
f'FROM sensor_data LEFT JOIN hardware ON sensor_data.hardware_uuid = hardware.uuid'
f' WHERE hardware_uuid IN (SELECT hardware_uuid FROM user_hardware '
f'WHERE user_id = (SELECT id FROM `user` WHERE username = %s)) {where_sql} '
f'ORDER BY {ordered} ASC LIMIT %s OFFSET %s',
(username, *args, size, (page - 1) * size))
def get_rfid_pagination_by_username(username, page, size, ordered, where_sql,
*args):
logger.info('get_hardware_pagination_by_username')
return MysqlOp().select_all(
f'SELECT username, log_time, hardware_uuid, `name` FROM RFID_log '
f'LEFT JOIN `user` ON RFID_log.user_id = `user`.id '
f'LEFT JOIN hardware ON RFID_log.hardware_uuid = hardware.uuid '
f'WHERE hardware_uuid IN (SELECT hardware_uuid '
f'FROM user_hardware WHERE user_id = (SELECT id FROM `user` WHERE username = %s)) {where_sql} '
f'ORDER BY {ordered} ASC LIMIT %s OFFSET %s',
(username, *args, size, (page - 1) * size))
def get_hardware_pagination(page, size, ordered, where_sql, *args):
logger.info('get_hardware_pagination')
return MysqlOp().select_all(
f"SELECT * FROM hardware {where_sql} ORDER BY {ordered} ASC LIMIT %s OFFSET %s",
(*args, size, (page - 1) * size))
def update_threshold_by_uuid(uuid, temperature_limit, humidity_limit):
logger.info('update_threshold_by_uuid')
return MysqlOp().op_sql(
'UPDATE hardware SET temperature_limit = %s, humidity_limit = %s WHERE uuid = %s',
(temperature_limit, humidity_limit, uuid))
def update_by_rid(rid, id):
return MysqlOp().op_sql(
'UPDATE user_roles SET role_id = %s WHERE user_id = %s', (rid, id))
def count_total_role(where_sql, *args):
return MysqlOp().select_one(
f'SELECT COUNT(`id`) as len from role {where_sql}', (*args, ))
def get_user_hardware_pagination(page, size, where_sql, *args):
return MysqlOp().select_all(
f"SELECT `user`.id , hardware.`name`, hardware.uuid, `user`.username FROM user_hardware "
f"LEFT JOIN `user` ON `user`.id = user_hardware.user_id "
f"LEFT JOIN hardware ON hardware.uuid = user_hardware.hardware_uuid "
f"{where_sql} LIMIT %s OFFSET %s", (*args, size, (page - 1) * size))
def update_hardware_by_id(id, name, humidity_limit, temperature_limit):
logger.info('update_hardware_by_id')
return MysqlOp().op_sql(
'UPDATE hardware SET `name` = %s, temperature_limit = %s, humidity_limit = %s WHERE id = %s',
(name, temperature_limit, humidity_limit, id))
from db import MysqlOp
a = MysqlOp().select_one(
"SELECT id, username FROM `user` WHERE username = '******' AND id IN (SELECT user_roles.user_id FROM user_roles WHERE user_roles.role_id IN (SELECT role.id FROM role WHERE role.`name` = 'admin'))"
)
print(a)
def get_role_pagination(page, size, where_sql, *args):
return MysqlOp().select_all(
f'SELECT * FROM role {where_sql} LIMIT %s OFFSET %s',
(*args, size, (page - 1) * size))
def add_role(name):
mp = MysqlOp()
return mp.op_sql('INSERT INTO role (`name`) VALUES (%s)',
(name)), mp.cur.lastrowid
def insert_hardware(uuid: str):
logger.info('insert_hardware')
return MysqlOp().op_sql('INSERT INTO hardware (uuid) VALUES (%s)', (uuid))
def get_id_by_uuid(uuid: str):
logger.info('get_id_by_uuid')
return MysqlOp().select_one('SELECT id FROM hardware WHERE uuid = %s',
(uuid))
def count_total(where_sql, *args):
logger.info('count_total')
return MysqlOp().select_one(
f'SELECT COUNT(`id`) as len from hardware {where_sql}', (*args, ))
def delete_user_hardware_by_user_id(id):
logger.info('delete_user_hardware_by_user_id')
return MysqlOp().op_sql('DELETE FROM user_hardware WHERE user_id = %s',
(id))
def get_all_hardware_list():
return MysqlOp().select_all('SELECT uuid, `name` FROM hardware')
def get_sensor_data_hourly(uuid):
return MysqlOp().select_all(
'SELECT temperature, humidity, record_time FROM sensor_data WHERE hardware_uuid = %s AND id > ((SELECT MAX(id) FROM sensor_data) - 100)',
(uuid))
def insert_user_hardware_data(id, uuid):
logger.info('insert_user_hardware')
return MysqlOp().op_sql(
'INSERT INTO user_hardware (user_id, hardware_uuid) VALUES (%s, %s)',
(id, uuid))
def update_user_by_id(username, id):
return MysqlOp().op_sql('UPDATE `user` SET username = %s WHERE id = %s',
(username, id))
def count_total(where_sql, *args):
return MysqlOp().select_one(
f'SELECT COUNT(`id`) as len from sensor_data {where_sql}', (*args, ))
def delete_user_by_id(id):
return MysqlOp().op_sql('DELETE FROM user WHERE id = %s', (id))
def insert_rfid_log(user_id, hardware_uuid):
logger.info('insert_rfid_log')
return MysqlOp().op_sql(
'INSERT INTO RFID_log (hardware_uuid, user_id) VALUES (%s, %s)',
(hardware_uuid, user_id))
def select_role_id_by_userid(name):
mp = MysqlOp()
return mp.select_one('SELECT role.id as id FROM role WHERE `name` = %s',
(name, ))