def authorization():
if 'send' in request.form and request.form['send'] == 'auth':
result = []
login = request.form.get('login')
password = request.form.get('password')
print(login)
print(password)
try:
if login and password:
with UseDatabase(
current_app.config['dbconfig']['Guest']) as cursor:
cursor.execute("""SELECT role
FROM lab4.user_groups
WHERE login='******'
AND password='******'""" % (login, password))
schema = ['user_group']
for con in cursor.fetchall():
result.append(dict(zip(schema, con)))
if len(result) > 0:
session['user_group'] = result[0]['user_group']
return redirect('/menu')
except ConnectionErrors as err:
print("Connection error:", str(err))
str_err = "Connection error: " + str(err)
return render_template('auth.html', message=str_err)
except SQLError as err:
print("Request error:", str(err))
str_err = "Request error: " + str(err)
else:
return render_template('auth.html')
else:
return render_template('auth.html')
def check():
with UseDatabase(current_app.config['dbconfig']["Manager"]) as cursor:
SQL = """SELECT surname, addr
FROM personal LEFT JOIN delivery_note USING(personal_id)
WHERE delivery_id IS NULL"""
cursor.execute(SQL)
result = cursor.fetchall()
res = []
schema = ['surname', 'addr']
for row in result:
res.append(dict(zip(schema, row)))
return render_template('results4.html', rows=res)
def check():
if 'send' in request.form and request.form['send'] == 'send':
data = request.form.get('client')
if data:
with UseDatabase(
current_app.config['dbconfig']["Manager"]) as cursor:
res = execute_query(cursor, data)
return render_template('results2.html', client=data, rows=res)
else:
return render_template('input.html')
else:
return render_template('input.html')
def check():
with UseDatabase(current_app.config['dbconfig']["Manager"]) as cursor:
SQL = """SELECT surname, addr, birthday, job, emp_date FROM personal LEFT JOIN (SELECT *
FROM delivery_note WHERE MONTH(delivery_date)=3 AND YEAR(delivery_date)=2017) t
USING(personal_id) where delivery_id is NULL"""
cursor.execute(SQL)
result = cursor.fetchall()
res = []
schema = ['surname', 'addr', 'birthday', 'job', 'emp_date']
for row in result:
res.append(dict(zip(schema, row)))
return render_template('results5.html', rows=res)
def check():
with UseDatabase(current_app.config['dbconfig']["Manager"]) as cursor:
SQL = """SELECT surname, COUNT(delivery_id), SUM(weight)
FROM personal JOIN delivery_note USING(personal_id)
WHERE MONTH(delivery_date)=3 AND YEAR(delivery_date)=2017
GROUP BY surname"""
cursor.execute(SQL)
result = cursor.fetchall()
res = []
schema = ['surname', 'trans_num', 'total_weight']
for row in result:
res.append(dict(zip(schema, row)))
return render_template('results1.html', rows=res)
def view_report():
if 'send' in request.form and request.form['send'] == 'send':
data = request.form.get('year')
if data:
with UseDatabase(
current_app.config['dbconfig']["Director"]) as cursor:
res = execute_query(cursor, data)
return render_template('view_report_result.html',
rows=res,
year=data)
else:
return render_template('view_report_input.html')
else:
return render_template('view_report_input.html')
def purchase():
cart_size = get_full_cart_len()
try:
with UseDatabase(current_app.config['dbconfig']["Manager"]) as cursor:
catalog = show_catalog(cursor)
if 'choice' in request.form and request.form[
'choice'] == "Добавить":
choice_name = request.form.get('choice_name')
choice_id = request.form.get('choice_id')
amount = request.form.get('amount')
put_into_cart(amount, choice_id, choice_name)
cart_size = get_full_cart_len()
return render_template('catalog.html',
catalog=catalog,
in_cart=cart_size)
elif 'show_cart' in request.form and request.form['show_cart'][
0:16] == "Показать корзину":
return render_template('cart.html', cart=session['cart'])
elif 'purchase' in request.form and request.form[
'purchase'] == "Оформить заказ":
return render_template('order.html', cart=session['cart'])
elif 'exit' in request.form and request.form['exit'] == "Купить":
save_cart(cursor)
return render_template('res.html')
elif 'delete' in request.form and request.form[
'delete'] == "Удалить":
item_to_delete = request.form.get('item_to_delete')
session['cart'] = delete_from_cart(item_to_delete)
return render_template('cart.html', cart=session['cart'])
else:
return render_template('catalog.html',
catalog=catalog,
in_cart=cart_size)
except ConnectionErrors as err:
print("Connection error:", str(err))
str_err = "Connection error: " + str(err)
return render_template('catalog.html', message=str_err)
except SQLError as err:
print("Request error:", str(err))
str_err = "Request error: " + str(err)
return render_template('catalog.html', message=str_err)
def check():
with UseDatabase(current_app.config['dbconfig']["Manager"]) as cursor:
SQL = """SELECT *
FROM client
WHERE client_id in (select client_id
from client join (select * from reestr where reestr_year=YEAR(CURDATE())) r USING(client_id)
where weight_sum = (SELECT MAX(weight_sum) FROM reestr WHERE reestr_year=YEAR(CURDATE())))"""
cursor.execute(SQL)
result = cursor.fetchall()
res = []
schema = [
'client_id', 'client_name', 'address', 'district', 'contact',
'phone'
]
for row in result:
res.append(dict(zip(schema, row)))
return render_template('results3.html', rows=res)
def input():
if 'send' in request.form and request.form['send'] == 'send':
data = request.form.get('department')
if data:
try:
with UseDatabase(current_app.config['dbconfig']["Worker"]) as cursor:
workers = get_workers(cursor, data)
except ConnectionErrors as err:
print("Connection error:", str(err))
str_err = "Connection error: " + str(err)
except SQLError as err:
print("Request error:", str(err))
str_err = "Request error: " + str(err)
return render_template('results.html', workers=workers)
else:
return render_template('input.html')
else:
return render_template('input.html')
def check():
with UseDatabase(current_app.config['dbconfig']["Manager"]) as cursor:
SQL = """select * from avg_1_2017
union select * from avg_2_2017
union select * from avg_3_2017
union select * from avg_4_2017
union select * from avg_5_2017
union select * from avg_6_2017
union select * from avg_7_2017
union select * from avg_8_2017
union select * from avg_9_2017
union select * from avg_10_2017
union select * from avg_11_2017
union select * from avg_12_2017"""
cursor.execute(SQL)
result = cursor.fetchall()
res = []
schema = ['num', 'avg_cost']
for row in result:
res.append(dict(zip(schema, row)))
return render_template('results6.html', rows=res)
def authorization():
if 'send' in request.form and request.form['send'] == 'auth':
result = []
login = request.form.get('login')
password = request.form.get('password')
print(login)
print(password)
if login and password:
with UseDatabase(
current_app.config['dbconfig']['Guest']) as cursor:
cursor.execute("""SELECT role
FROM curs.user_groups
WHERE login='******'
AND password='******'""" % (login, password))
schema = ['user_group']
for con in cursor.fetchall():
result.append(dict(zip(schema, con)))
if len(result) > 0:
session['user_group'] = result[0]['user_group']
return redirect('/menu')
else:
return render_template('auth.html', status=1)
else:
return render_template('auth.html', status=0)
def create_delivery_note():
if 'form' not in session:
session['form'][0] = []
with UseDatabase(current_app.config['dbconfig']["Manager"]) as cursor:
options = show_options(cursor)
if 'send' in request.form and request.form['send'] == "Сформировать":
delivery_date = request.form.get('date')
driver = {
"driver_id": request.form.get('driver'),
"driver_name": ''
}
client = {
"client_id": request.form.get('client'),
"client_name": ''
}
transport = {
"transport_id": request.form.get('transport'),
"transport_name": ''
}
amount = request.form.get('amount')
weight = request.form.get('weight')
if check_form(delivery_date, driver["driver_id"],
client["client_id"], transport["transport_id"],
amount, weight):
save_form(delivery_date, driver, client, transport, amount,
weight)
return render_template('create_delivery_note_form.html',
form=session['form'],
options=options,
is_filled=0)
driver["driver_name"] = get_name_from_options(
options, driver["driver_id"], 'driver')
client["client_name"] = get_name_from_options(
options, client["client_id"], 'client')
transport["transport_name"] = get_name_from_options(
options, transport["transport_id"], 'transport')
save_form(delivery_date, driver, client, transport, amount, weight)
return render_template('create_delivery_note_confirm.html',
form=session['form'])
elif 'create_note' in request.form and request.form[
'create_note'] == "Подтвердить":
save_into_db(cursor)
return render_template('create_delivery_note_result.html')
elif 'edit_note' in request.form and request.form[
'edit_note'] == "Изменить":
return render_template('create_delivery_note_form.html',
form=session['form'],
options=options,
is_filled=1)
elif 'cancel' in request.form and request.form['cancel'] == "Отмена":
session['form'].clear()
session['form'] = [{}]
return render_template('create_delivery_note_form.html',
form=session['form'],
options=options,
is_filled=1)
else:
return render_template('create_delivery_note_form.html',
form=session['form'],
options=options,
is_filled=1)