def remove_ballast_from_virtual_group():
ballast_id = request.args.get('ballast_id')
virtual_group_id = request.args.get('virtual_group_id')
db = Database()
sql_string = 'DELETE FROM virtual_group_items WHERE ballast_id=' + ballast_id + ' AND virtual_group_id=' + virtual_group_id
db.execute(sql_string)
return 'http200'
def __init__(self, args):
self.args = args
self.database_bought = Database(config.BOUGHT_FILE,
config.BOUGHT_FIELDS)
self.database_sold = Database(config.SOLD_FILE, config.SOLD_FIELDS)
self.database_products = Database(config.PRODUCTS_FILE,
config.PRODUCTS_FIELDS)
# parse --now, --today and --yesterday
today = Today().get_date()
today = datetime.strptime(today, config.DATE_FORMAT)
if self.args['yesterday'] == True:
today = Today().get_date()
today = datetime.strptime(today, config.DATE_FORMAT)
today = today + timedelta(days=-1)
self.today = today
# --export-format
self.export = self.args['export_format']
def add_ballast_to_virtual_group():
ballast_id = request.args.get('ballast_id')
virtual_group_id = request.args.get('virtual_group_id')
db = Database()
sql_string = 'INSERT INTO virtual_group_items (ballast_id, virtual_group_id) VALUES (' + ballast_id + ', ' + virtual_group_id + ')'
db.execute(sql_string)
return 'http200'
def update_ballast():
ballast_name = request.form.get('ballast_name')
ballast_id = request.form.get('ID')
ballast_id = ballast_id[3:]
db = Database()
db.update_ballast_name(ballast_id, ballast_name)
return ballast_name
def rename_virtual_group():
virtual_group_ID = request.args.get('virtual_group_id')
group_name = request.args.get('new_name')
db = Database()
sql_string = 'UPDATE virtual_group SET name="' + group_name + '" WHERE ID = ' + virtual_group_ID
db.execute(sql_string)
return 'http200'
def do_set_arc_level(self, arc_level, visualID):
db = Database()
visual_item = db.get_visual_item(visualID)
var = 100
if visual_item.get('visual_type') == 0:
var = 1
elif visual_item.get('visual_type') == 1:
var = 1
elif visual_item.get('visual_type') == 2:
group = db.get_group(visual_item.get('visual_ID_of_type'))
channel = self.get_chan(group.get('dali_group_channel'))
channel.set_ballast_or_group_address(
group.get('dali_group_number'))
channel.set_address_mode(AddressModes.group)
channel.set_direct_arc_enabled(1)
channel.set_arc_level(arc_level)
elif visual_item.get('visual_type') == 3:
ballast = db.get_ballast(visual_item.get('visual_ID_of_type'))
channel = self.get_chan(ballast.get('ballast_channel'))
channel.set_ballast_or_group_address(
ballast.get('ballast_short_address'))
channel.set_address_mode(AddressModes.ballast)
channel.set_direct_arc_enabled(1)
channel.set_arc_level(arc_level)
return 'http200'
class Buy():
"""Handles the SuperPy buy action
Uses the bought and products database to record the buy action
New products are added to the product database,
complete with a new EAN13 barcode
Bought products are then stored in the bought database
"""
def __init__(self, args):
self.args = args
check_required_arguments(
args, ('product_name', 'price', 'expiration_date'))
self.database_bought = Database(
config.BOUGHT_FILE, config.BOUGHT_FIELDS)
self.database_products = Database(
config.PRODUCTS_FILE, config.PRODUCTS_FIELDS)
self.product_name = args['product_name']
def run(self):
# check id product exists in product database
product = filter_list(
self.database_products.data, 'product_name', [self.product_name])
barcode = ''
if len(product) == 0:
# generate a new barcode
barcode = Barcode(config.STORE_BARCODE_PREFIX)
# add product to products database
self.database_products.add({
'product_name': self.product_name,
'full_name': self.product_name.title(),
'ean13': barcode,
})
else:
# use preexisting barcode
barcode = product[0]['ean13']
# add buy to bought database
self.database_bought.add(
{
'id': self.database_bought.rowcount + 1,
'product_name': self.product_name,
'buy_date': Today().get_date(),
'buy_price': self.args['price'],
'expiration_date': self.args['expiration_date'],
'ean13': barcode
})
return 'OK'
def download_groups_from_ballast():
ballast_id = request.args.get('ballast_id')
dcs = DaliChannels()
db = Database()
ballast = db.get_ballast(ballast_id)
dc = dcs.get_chan(ballast.get('ballast_channel'))
answer = dc.download_groups_from_ballast(ballast_id)
return answer
def get_scene_values_for_group():
group_id = int(request.args.get('group_id'))
scene_number = int(request.args.get('scene_number'))
channel = dcs.get_chan_via_group_id(group_id)
channel.dali_goto_scene(scene_number)
db = Database()
data = db.get_scene_values_for_group(group_id, scene_number)
return json.dumps(data)
def get_ballasts_in_group():
virtual_group_id = request.args.get('virtual_group_id')
channel_number = request.args.get('channel_number')
db = Database()
sql_string = 'SELECT ballasts.ID as BallastID, ballast_name, virtual_group_items.ballast_id FROM ballasts LEFT JOIN virtual_group_items ON ballasts.ID = virtual_group_items.ballast_id ' \
'WHERE ballast_channel = ' \
+ channel_number + ' AND virtual_group_ID=' + virtual_group_id + ' ORDER BY ballast_short_address ASC'
data = db.get_sql_data(sql_string)
return json.dumps(data)
def __init__(self, args={}):
self.args = args
self.today = Database(config.TODAY_FILE, config.TODAY_FIELDS)
# set today if database is empty
if len(self.today.data) == 0:
self.args['advance_time'] = 0
self.args['init'] = True
self.run()
def update_scene_value():
ballast_id = int(request.args.get('ballast_id'))
scene_number = int(request.args.get('scene_number'))
scene_value = int(request.args.get('scene_value'))
db = Database()
data = db.update_scene_value(ballast_id, scene_number, scene_value)
channel = dcs.get_chan_via_ballast_id(ballast_id)
channel.dali_set_scene_value(scene_number, scene_value)
channel.dali_goto_scene(scene_number)
return data
def insert_visual():
visual_name = request.args.get('visual_name')
visual_page_id = int(request.args.get('visual_page_ID'))
visual_type = int(request.args.get('visual_type'))
visual_columns = int(request.args.get('visual_columns'))
visual_id_of_type = int(request.args.get('visual_ID_of_type'))
db = Database()
db.insert_visual(visual_name, visual_page_id, visual_type, visual_columns, visual_id_of_type)
return 'http200'
def do_control_ballast(self, action, ballastID):
db = Database()
ballast = db.get_ballast(ballastID)
channel = self.get_chan(ballast.get('ballast_channel'))
channel.set_ballast_or_group_address(
ballast.get('ballast_short_address'))
channel.set_address_mode(AddressModes.ballast)
channel.set_direct_arc_enabled(0)
channel.do_command(action)
return 'http200'
def __init__(self, args):
self.args = args
check_required_arguments(args, ('product_name', 'price'))
self.database_bought = Database(config.BOUGHT_FILE,
config.BOUGHT_FIELDS)
self.database_sold = Database(config.SOLD_FILE, config.SOLD_FIELDS)
def do_set_arc_level_ballast(self, arc_level, ballast_id):
db = Database()
ballast = db.get_ballast(ballast_id)
channel = self.get_chan(ballast.get('ballast_channel'))
channel.set_ballast_or_group_address(
ballast.get('ballast_short_address'))
channel.set_address_mode(AddressModes.ballast)
channel.set_direct_arc_enabled(1)
channel.set_arc_level(arc_level)
return 'http200'
class Sell():
"""Handles the SuperPy sell action
Uses the bought and products database to record the sell action
Sold products that are in inventory are stored in the sold database
The product with the earliest expiration date is sold first
"""
def __init__(self, args):
self.args = args
check_required_arguments(args, ('product_name', 'price'))
self.database_bought = Database(config.BOUGHT_FILE,
config.BOUGHT_FIELDS)
self.database_sold = Database(config.SOLD_FILE, config.SOLD_FIELDS)
def run(self):
bought_id = self.get_bought_id()
if bought_id == None:
return 'ERROR: Product not in stock'
self.database_sold.add({
'id': self.database_sold.rowcount + 1,
'bought_id': bought_id,
'sell_date': Today().get_date(),
'sell_price': self.args['price'],
})
return 'OK'
def get_bought_id(self):
inventory = filter_list(self.database_bought.data, 'product_name',
[self.args['product_name']])
if len(inventory) == 0:
return None
# sell the product with the earliest expiration date
inventory = sort_list(inventory, 'expiration_date')
if len(inventory) == 1:
return inventory[0]['id']
for item in inventory:
is_sold = filter_list(self.database_sold.data, 'bought_id',
[item['id']])
if len(is_sold) == 0:
return item['id']
return None
def get_aluno(matricula):
try:
if matricula is None:
abort(404)
with Database.getConn().cursor() as cursor:
sql = "SELECT nome, email, endereco, cpf, matricula, telefone FROM aluno WHERE matricula = %s"
cursor.execute(sql, (matricula))
result = cursor.fetchone()
from classes.Aluno import Aluno
return jsonify(Aluno(result).__dict__)
finally:
Database.closeConn()
def download_scene_from_ballast(self, ballast_short_address, ballast_id,
scene_number):
self.__address_mode = AddressModes.ballast
self.__ballast_or_group_address = ballast_short_address
scene_value = self.dali_query_scene_level(scene_number)
db = Database()
db.update_scene_value(ballast_id, scene_number, scene_value)
return 'Ballast ' + str(self.__ballast_or_group_address) \
+ ' - scene ' \
+ str(scene_number) \
+ ' level: '\
+ str(scene_value)
def index():
try:
with Database.getConn().cursor() as cursor:
sql = "SELECT nome, email, endereco, matricula FROM aluno"
cursor.execute(sql)
result = cursor.fetchall()
return_data = []
for aluno in result:
from classes.Aluno import Aluno
return_data.append(Aluno(aluno).__dict__)
return jsonify(return_data)
finally:
Database.closeConn()
def do_value():
db = Database()
ballast_id = request.form.get('ID')
nv = db.get_ballast_id_and_channel(ballast_id)
ballast_short_address = nv['ballast_short_address']
channel_number = nv['ballast_channel']
channel = dcs.get_chan(channel_number)
channel.set_direct_arc_enabled(1)
channel.set_address_mode(AddressModes.ballast)
channel.set_ballast_or_group_address(ballast_short_address)
value = int(request.form.get('value'))
channel.set_arc_level(value)
return 'ok'
def delete_aluno():
try:
if request.form['matricula'] is None:
abort(404)
return_data = {"success": False}
with Database.getConn().cursor() as cursor:
sql = "DELETE FROM aluno WHERE matricula = %s"
cursor.execute(sql, (request.form['matricula']))
Database.getConn().commit()
return_data['success'] = True
return jsonify(return_data)
finally:
Database.closeConn()
def save(self):
""" Method save or update data do bdd """
if not self.__is_error:
fields = list(self.fields)
table = str(self.table)
if hasattr(self, 'PK_id') and int(self.PK_id) > 0:
# Update data
DB.update(table, fields, self,)
else:
# Save data
# if isset primary key id unset
self.PK_id = None
DB.save(table, fields, self)
def __init__(self, args):
self.args = args
check_required_arguments(
args, ('product_name', 'price', 'expiration_date'))
self.database_bought = Database(
config.BOUGHT_FILE, config.BOUGHT_FIELDS)
self.database_products = Database(
config.PRODUCTS_FILE, config.PRODUCTS_FIELDS)
self.product_name = args['product_name']
def UploadGroupsToDevice(self, ballast_sa):
db = Database()
ballast_data = db.get_ballast_by_short_address(ballast_sa)
self.__address_mode = AddressModes.ballast
sa = ballast_data['ballast_short_address']
self.__ballast_or_group_address = sa
for i in range(16):
if ballast_data['ballast_group_' + str(i)]:
self.DaliAddToGroup(sa, i)
else:
self.DaliRemoveFromGroup(sa, i)
return 'Ballast ' + str(sa) + ': groups uploaded'
def insert_page():
db = Database()
c = db.conn.cursor()
sql_string = 'INSERT INTO pages (page_name, page_sort_order) VALUES ("new",1)'
c.execute(sql_string)
db.conn.commit()
return 'http200'
def delete_equipment():
user = flask_login.current_user
# only admin is allowed to delete a new equipment
if not user.is_admin():
return 'http403'
db = Database()
ID = request.args.get('ID')
sql1 = 'DELETE FROM equipment WHERE ID = "' + ID + '"'
sql2 = 'DELETE FROM pictures WHERE equipment_id = "' + ID + '"'
sql3 = 'DELETE FROM documents WHERE equipment_id = "' + ID + '"'
db.conn.row_factory = db.dict_factory
c = db.conn.cursor()
c.execute(sql1)
c.execute(sql2)
c.execute(sql3)
db.conn.commit()
updir = os.path.join('database/files')
for filename in os.listdir(updir):
if filename.startswith("equipment_picture_id_" +
ID) or filename.startswith(
"equipment_document_id_" + ID):
os.remove(os.path.join(updir, filename))
c.close()
return 'http200'
def upload_equipment_file():
equipment_id = request.args.get('equipment_id')
if request.method == 'POST':
file = request.files['file']
filename = secure_filename(file.filename)
file_type = filename.rsplit('.', 1)[-1]
if files and allowed_file(file.filename):
db = Database()
c = db.conn.cursor()
if file_type == 'png' or file_type == 'jpg' or file_type == 'jpeg' or file_type == 'gif':
# prepare upload picture to files
filename = 'equipment_picture_id_' + str(
equipment_id) + '_filename_' + filename
# prepare upload picture to pictures table in db
sql_string = 'INSERT INTO pictures (picture_name, equipment_id) VALUES ("' + filename + '","' + equipment_id + '")'
else:
# prepare upload document to files
filename = 'equipment_document_id_' + str(
equipment_id) + '_filename_' + filename
# prepare upload document to documents table in db
sql_string = 'INSERT INTO documents (document_name, equipment_id) VALUES ("' + filename + '","' + equipment_id + '")'
c.execute(sql_string)
db.conn.commit()
updir = os.path.join('database/files')
file.save(os.path.join(updir, filename))
file_size = os.path.getsize(os.path.join(updir, filename))
json_string = '{"name": "' + filename + '", "size": "' + str(
file_size) + '"}'
return json.dumps(json_string)
def deny_access():
user = flask_login.current_user
# only admin is allowed to do this
if not user.is_admin():
return 'http403'
db = Database()
user_id = request.args.get('user_id')
sql1 = 'DELETE FROM users WHERE ID = "' + user_id + '"'
sql2 = 'DELETE FROM documents WHERE user_id = "' + user_id + '"'
db.conn.row_factory = db.dict_factory
c = db.conn.cursor()
c.execute(sql1)
c.execute(sql2)
db.conn.commit()
my_dir = os.path.join('database/files/')
for filename in os.listdir(my_dir):
if filename.startswith("user_register_upload_id_" + user_id):
os.remove(os.path.join(my_dir, filename))
# my_dir = os.path.join('static/images/upload/')
# for filename in os.listdir(my_dir):
# if filename.startswith("user_register_upload_id_" + user_id):
# os.remove(os.path.join(my_dir, filename))
c.close()
# sent email to user that he got denied access to the db
return 'http200'
def change_password_form():
email_hash = request.form.get('email_hash')
new_pw_hash = request.form.get('new_pw_hash')
confirm_pw_hash = request.form.get('confirm_pw_hash')
if new_pw_hash == "" or confirm_pw_hash == "":
flash("Make sure that both passwords are the filled in.")
return render_template("change_password.html", email_hash=email_hash)
if new_pw_hash != confirm_pw_hash:
flash("Make sure that both new passwords are the same.")
return render_template("change_password.html", email_hash=email_hash)
db = Database()
db.conn.row_factory = db.dict_factory
c = db.conn.cursor()
sql_string = 'SELECT ID, user_email FROM users'
c.execute(sql_string)
data = c.fetchall()
email = ''
for user in data:
user_email_hash = hashlib.sha256(
user['user_email'].encode()).hexdigest()
if user_email_hash == email_hash:
email = user['user_email']
sql1 = 'UPDATE users SET user_set_pw="1", user_pw_hash = "' + new_pw_hash + '" WHERE user_email = "' + email + '"'
c.execute(sql1)
db.conn.commit()
c.close()
flash("Password successfully changed.")
return redirect(url_for('auth.login'))
def setup(self):
config = configparser.ConfigParser()
config.read(testpath + '/' + 'config.ini')
self.db = Database(config['db']['host'], config['db']['user'], config['db']['pass'], config['db']['database'])
self.db.q("DROP TABLE IF EXISTS `test`")
self.db.q(
"CREATE TABLE `test` (\
`id` int(11) NOT NULL AUTO_INCREMENT,\
`name` varchar(200) NOT NULL,\
`tested` tinyint(1) NOT NULL DEFAULT '0',\
PRIMARY KEY (`id`)\
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;"
)
class Test_Database(object):
"""Unit tests for Database class"""
model = None
db = None
def setup(self):
config = configparser.ConfigParser()
config.read(testpath + '/' + 'config.ini')
self.db = Database(config['db']['host'], config['db']['user'], config['db']['pass'], config['db']['database'])
self.db.q("DROP TABLE IF EXISTS `test`")
self.db.q(
"CREATE TABLE `test` (\
`id` int(11) NOT NULL AUTO_INCREMENT,\
`name` varchar(200) NOT NULL,\
`tested` tinyint(1) NOT NULL DEFAULT '0',\
PRIMARY KEY (`id`)\
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;"
)
def teardown(self):
self.db.q("DROP TABLE IF EXISTS `test`")
self.db.close()
def test_fetch_one(self):
assert self.db.fetch_one("SELECT 1") == 1
def test_fetch_all(self):
data = self.db.fetch_all("SELECT 1 as a, 2 as b UNION SELECT 3 as a, 4 as b")
compare = [
{'a': 1, 'b': 2},
{'a': 3, 'b': 4},
]
assert compare == data
def test_fetch_col(self):
data = self.db.fetch_col("SELECT 1 as a, 2 as b UNION SELECT 3 as a, 4 as b")
compare = [1, 3]
assert compare == data
def test_fetch_row(self):
data = self.db.fetch_row("SELECT 1 as a, 2 as b UNION SELECT 3 as a, 4 as b")
compare = {'a': 1, 'b': 2}
assert compare == data
def atest_fetch_pairs(self):
data = self.db.fetch_pairs("SELECT 1 as a, 2 as b UNION SELECT 3 as a, 4 as b")
compare = {1: 2, 3: 4}
assert compare == data
def test_escape(self):
assert self.db.escape(r"a\b'c") == r"a\\b\'c"
def test_quote(self):
assert self.db.quote(r"a\b'c") == r"'a\\b\'c'"
def test_q(self):
curs = self.db.q("SELECT 1", True)
assert curs.__class__.__name__ == 'MySQLCursor'
curs.fetchall()
curs.close()
def test_insert(self):
assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 0
self.db.insert('test', {'name': 'aaa'})
assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 1
with pytest.raises(mysql.connector.IntegrityError):
self.db.insert('test', {'id': 1, 'name': 'aaa'})
self.db.insert('test', {'id': 1, 'name': 'aaa'}, ignore=True) # Without ex
def test_insert_mass(self):
assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 0
data = []
for i in range(1, 75):
data.append({'id': i, 'name': 'aaa' + str(i)})
self.db.insert_mass('test', data)
assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 74
self.db.q("TRUNCATE TABLE `test`")
data = []
for i in range(1, 30):
data.append({'id': i, 'name': 'aaa' + str(i)})
self.db.insert_mass('test', data)
assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 29
data = []
for i in range(1, 30):
data.append({'id': i, 'name': 'aaa' + str(i)})
with pytest.raises(mysql.connector.IntegrityError):
self.db.insert_mass('test', data)
data = []
for i in range(1, 30):
data.append({'id': i, 'name': 'aaa' + str(i)})
self.db.insert_mass('test', data, ignore=True)
def test_update(self):
self.db.insert('test', {'name': 'aaa'})
self.db.insert('test', {'name': 'bbb'})
self.db.insert('test', {'name': 'ccc'})
assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `tested`=0") == 3
self.db.update('test', {'tested': 1}, "`name`='aaa'")
assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `tested`=0") == 2
def test_update_mass(self):
data = []
for i in range(1, 30):
data.append({'id': i, 'name': 'aaa' + str(i)})
self.db.insert_mass('test', data)
assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `tested`=0") == 29
self.db.update_mass('test', 'tested', {"`name`='aaa1'": 1, "`name`='aaa2'": 1, "`name`='aaa3'": 1, })
assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `tested`=0") == 26
def test_replace(self):
assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 0
self.db.insert('test', {'id': 1, 'name': 'aaa'})
assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `name`='aaa' AND `id`=1") == 1
self.db.replace('test', {'id': 1, 'name': 'aaa1'})
assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `name`='aaa1' AND `id`=1") == 1
