def create_task(id_parent, id_children, description, coin):
conn, c = connect_db()
uid_task = str(uuid.uuid4())
purchases = [(uid_task, id_children, id_parent, description, coin, '0')]
c.executemany('INSERT INTO tasks VALUES (?,?,?,?,?,?)', purchases)
conn.commit()
conn.close()
def view_regex():
if session['id'] is not None:
if session['status'] == 'parent':
conn, c = connect_db()
sql = (
"SELECT id_child FROM children where id_parent = '{}'".format(
session['id']))
c.execute(sql)
result_c = c.fetchall()
ans = []
for child in result_c:
print(child)
sql = (
"SELECT id_child, description FROM regex where id_child = '{}'"
.format(child[0]))
print(sql)
c.execute(sql)
result_p = c.fetchall()
print(result_p)
for i in result_p:
sql = "SELECT name, surname, patronymic FROM children where id_child = '{}'".format(
i[0])
c.execute(sql)
r = c.fetchall()
ans.append((r[0][0], r[0][1], r[0][2], i[1]))
print(ans)
return render_template('view_regex.html',
title='add_regex',
result=ans,
valid=session['status'])
def create_table_regex():
conn, c = connect_db()
c.execute(
'''create table regex (id_regex varchar(255), id_child varchar(255),
description varchar(255))''')
conn.commit()
conn.close()
def create_table_requests():
conn, c = connect_db()
c.execute(
'''create table requests (id_requests varchar(255), id_child varchar(255), id_parent varchar(255),
description varchar(255), coin int, status int)''')
conn.commit()
conn.close()
def create_regex(id_children, description):
conn, c = connect_db()
uid_regex = str(uuid.uuid4())
purchases = [(uid_regex, id_children, description)]
c.executemany('INSERT INTO regex VALUES (?,?,?)', purchases)
conn.commit()
conn.close()
def close_requests():
#try:
if session['id'] is not None:
if session['status'] == 'parent':
conn, c = connect_db()
sql = (
"SELECT id_requests, description "
"FROM requests where id_parent = '{}' and status = 0".format(
session['id']))
c.execute(sql)
result = c.fetchall()
print(result)
form = closetaskform()
form.tasks.choices = result
if form.validate_on_submit():
close_requests_user(form.tasks.data)
sql = "select id_child, coin from requests where status = 1 and id_requests = '{}'".format(
form.tasks.data)
c.execute(sql)
result = c.fetchall()
transaction.bonus(result[0][1], session['id'], result[0][0])
return redirect("/requests")
return render_template('close_requests.html',
title='add_task',
form=form,
valid=session['status'])
def close_requests_user(id_task):
conn, c = connect_db()
sql = "update requests SET status = 1 "\
"where id_requests = '{0}'".format(id_task)
print(sql)
c.execute(sql)
conn.commit()
conn.close()
def create_task(id_parent, description, coin):
conn, c = connect_db()
uid_task = str(uuid.uuid4())
'''
c.execute(create table tasks (id_task varchar(255), id_parent varchar(255), id_child int,
description varchar(255), coin int, status int))
'''
conn.commit()
conn.close()
def create_table_children():
conn, c = connect_db()
c.execute(
'''create table children (id_child varchar(255), id_parent varchar(255), login varchar(255),
password varchar(255), name varchar(255), surname varchar(255), patronymic varchar(255),
sex varchar(255), balance_needs int, balance_close int, balance_open int,
number_close int, number_open int, number_needs int)''')
conn.commit()
conn.close()
def create_child(id_parent, login, password, name, surname, patronymic, sex,
number_close, number_open, number_needs):
conn, c = connect_db()
uid_child = str(uuid.uuid4())
purchases = [(uid_child, id_parent, login, password, name, surname, patronymic, sex, 0, 0, 0,
number_close, number_open, number_needs)]
c.executemany('INSERT INTO children VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)', purchases)
conn.commit()
conn.close()
def close_task_user(id_task, id_user, status):
conn, c = connect_db()
sql = "update tasks SET status = '{0}' "\
"where id_task = '{1}' and "\
"(id_child = '{2}' or id_parent = '{3}')".format(status, id_task, id_user, id_user)
print(sql)
c.execute(sql)
conn.commit()
conn.close()
def close_task():
if session['id'] is not None:
if session['status'] == 'parent':
conn, c = connect_db()
sql = ("SELECT id_task, description "
"FROM tasks where id_parent = '{}' and status = '1'".format(
session['id']))
c.execute(sql)
result = c.fetchall()
print(result)
form = closetaskform()
form.tasks.choices = result
if form.validate_on_submit():
close_task_user(form.tasks.data, session['id'], 2)
sql = "select id_child, coin from tasks where status = 2 and id_task = '{}'".format(
form.tasks.data)
c.execute(sql)
result = c.fetchall()
print(result[0][1], ' ', result[0][0])
transaction.in_close_to_open(result[0][1], result[0][0])
return redirect("/close_task")
return render_template('close_task.html',
title='close_task',
form=form,
valid=session['status'])
elif session['status'] == 'children':
conn, c = connect_db()
sql = ("SELECT id_task, description "
"FROM tasks where id_child = '{}' and status = '0'".format(
session['id']))
c.execute(sql)
result = c.fetchall()
form = closetaskform()
form.tasks.choices = result
if form.validate_on_submit():
print(form.tasks.data)
close_task_user(form.tasks.data, session['id'], 1)
return redirect('/view_task')
return render_template('close_task.html',
title='close_task',
form=form,
valid=session['status'])
return redirect('/index')
def view_task():
if session['id'] is not None:
if session['status'] == 'parent':
print(session["id"])
conn, c = connect_db()
sql = "SELECT tasks.id_child, tasks.description, tasks.coin, tasks.status " \
"FROM tasks where tasks.id_parent = '{}' order by tasks.status".format(session['id'])
"""sql = ("SELECT children.name, children.surname, children.patronymic, tasks.description, tasks.coin,"
" tasks.status FROM tasks, children where tasks.id_parent = '{}' and children.id_parent = '{}'"
" and tasks.id_parent = children.id_parent"
"".format(session['id'], session['id']))"""
c.execute(sql)
result = c.fetchall()
ans = []
for i in result:
sql = "SELECT name, surname, patronymic FROM children where id_child = '{}'".format(
i[0])
c.execute(sql)
r = c.fetchall()
ans.append((r[0][0], r[0][1], r[0][2], i[1], i[2], i[3]))
return render_template('view_task_parent.html',
title='view_task',
valid=session['status'],
tasksp=ans)
elif session['status'] == 'children':
conn, c = connect_db()
sql = ("SELECT * FROM tasks where id_child = '{}' and status = 0".
format(session['id']))
c.execute(sql)
result = c.fetchall()
sql = (
"SELECT name, surname, patronymic FROM children where id_child = '{}'"
.format(session['id']))
c.execute(sql)
resul = c.fetchall()
# Добавить рендеринг результата
return render_template('view_task_child.html',
title='view_task',
valid=session['status'],
tasks=result,
fio=resul[0])
return redirect('/index')
def create_requests(id_children, description, coin):
conn, c = connect_db()
uid_regex = str(uuid.uuid4())
sql = "SELECT id_parent FROM children where id_child = '{}'".format(
id_children)
c.execute(sql)
id_parent = c.fetchall()[0][0]
purchases = [(uid_regex, id_children, id_parent, description, coin, 0)]
c.executemany('INSERT INTO requests VALUES (?,?,?,?,?,?)', purchases)
conn.commit()
conn.close()
def create_parent(login, password, name, surname, patronymic, sex, number_parents,
balance_needs, balance_close, balance_open, balance_parent, tel_number):
conn, c = connect_db()
uid_parent = str(uuid.uuid4())
uid_child = "nothing"
purchases = [(uid_parent, uid_child, login, password, name, surname, patronymic,
sex, number_parents, balance_needs, balance_close, balance_open,
balance_parent, tel_number)]
#print (purchases)
c.executemany('INSERT INTO parents VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)', purchases)
conn.commit()
conn.close()
def request():
#try:
if session['id'] is not None:
if session['status'] == 'parent':
conn, c = connect_db()
sql = "SELECT id_child, description, coin FROM requests where id_parent = '{}' and status = 0".format(
session['id'])
c.execute(sql)
result = c.fetchall()
ans = []
for i in result:
sql = "SELECT name, surname, patronymic FROM children where id_child = '{}'".format(
i[0])
c.execute(sql)
r = c.fetchall()
ans.append((r[0][0], r[0][1], r[0][2], i[1], i[2]))
return render_template('request_parent.html',
title='add_task',
result=ans,
valid=session['status'])
elif session['status'] == 'children':
form = requestaddform()
conn, c = connect_db()
sql = "SELECT description, coin FROM requests where id_child = '{}'".format(
session['id'])
c.execute(sql)
result = c.fetchall()
if form.validate_on_submit():
uid_parent = session['id']
create_requests(uid_parent, form.description.data,
form.coin.data)
return redirect('/index')
return render_template('request_child.html',
title='add_task',
form=form,
result=result,
valid=session['status'])
def index():
try:
if session['id'] is not None:
if session['status'] == 'parent':
balance_p = balance_parent(session['id'])
conn, c = connect_db()
sql = (
"SELECT id_child, name, surname, patronymic FROM children where id_parent = '{}'"
.format(session['id']))
c.execute(sql)
balance_c = {
child[1] + ' ' + child[2] + ' ' + child[3]:
balance_child(child[0])
for child in c.fetchall()
}
return render_template("index_parent.html",
len_balance_c=len(balance_c) + 2,
balance_p=balance_p,
balance_c=balance_c,
valid=session['status'])
elif session['status'] == 'children':
conn, c = connect_db()
sql = (
"SELECT id_child, name, surname, patronymic FROM children where id_child = '{}'"
.format(session['id']))
c.execute(sql)
balance_c = {
child[1] + ' ' + child[2] + ' ' + child[3]:
balance_child(child[0])
for child in c.fetchall()
}
return render_template("index_children.html",
len_balance_c=len(balance_c) + 1,
balance_c=balance_c,
valid=session['status'])
except:
return render_template("index.html")
def block_pay_children():
#try:
if session['id'] is not None:
if session['status'] == 'parent':
conn, c = connect_db()
sql = (
"SELECT id_child, login FROM children where id_parent = '{}'".
format(session['id']))
c.execute(sql)
form = bonusform()
form.childrens.choices = c.fetchall()
if form.validate_on_submit():
transaction.mulctl(form.coin.data, form.childrens.data)
return redirect("/block_pay_children")
return render_template('block_pay_children.html',
title='add_task',
form=form,
valid=session['status'])
def add_score():
try:
if session['id'] is not None:
if session['status'] == 'parent':
form = bonusform()
conn, c = connect_db()
sql = (
"SELECT id_child, login FROM children where id_parent = '{}'"
.format(session['id']))
c.execute(sql)
#form = Addtaskform()
form.childrens.choices = c.fetchall()
if form.validate_on_submit():
transaction.bonus(form.coin.data, session['id'],
form.childrens.data)
print(11111)
return redirect("/score")
#conn, c = connect_db()
sql = ("SELECT id_child, name, surname, patronymic "
"FROM children where id_parent = '{}'".format(
session['id']))
c.execute(sql)
l = {
child[1] + ' ' + child[2] + ' ' + child[3]:
average_score(child[1] + ' ' + child[2] + ' ' + child[3])
for child in c.fetchall()
}
s = 0
ans = []
for score in l:
s = 0
for sc in l[score]:
if sc != 'name' and sc != 'mean':
s += float(l[score][sc])
l[score]['mean'] = str(round(s / (len(l[score]) - 1), 1))
#ans.append(str(round(s/(len(l[score])-1), 1)))
return render_template('score.html',
title='add_regex',
users=l,
form=form,
valid=session['status'])
except:
return redirect('/index')
def login_in(login, password):
print (login)
print(password)
conn, c = connect_db()
sql = ("SELECT id_parent FROM parents where login = '******' and password = '******'".format(login, password))
c.execute(sql)
print(sql)
result = c.fetchall()
print(result)
if len(result) != 0:
return {'id': result[0], 'status': 'parent'}
sql = ("SELECT id_child FROM children where login = '******' and password = '******'".format(login, password))
c.execute(sql)
print(sql)
result = c.fetchall()
conn.close()
if len(result) != 0:
return {'id': result[0], 'status': 'children'}
else:
return 'Error'
def add_task():
if session['id'] is not None:
if session['status'] == 'parent':
conn, c = connect_db()
sql = (
"SELECT id_child, login FROM children where id_parent = '{}'".
format(session['id']))
c.execute(sql)
form = Addtaskform()
form.childrens.choices = c.fetchall()
if form.validate_on_submit():
uid_parent = session['id']
create_task(form.childrens.data, uid_parent,
form.description.data, form.coin.data)
return redirect('/view_task')
return render_template('add_task.html',
title='add_task',
form=form,
valid=session['status'])
return redirect('/index')
def add_regex():
try:
if session['id'] is not None:
if session['status'] == 'parent':
conn, c = connect_db()
sql = (
"SELECT id_child, login FROM children where id_parent = '{}'"
.format(session['id']))
c.execute(sql)
form = addregexform()
form.childrens.choices = c.fetchall()
if form.validate_on_submit():
create_regex(form.childrens.data, form.description.data)
return redirect('/view_regex')
return render_template('add_regex.html',
title='add_regex',
form=form,
valid=session['status'])
except:
return redirect('/index')
#for other os from app.api.database.connect_db import connect_db #for linux """ import sys import os #directory_user_cabinet = os.getcwd() directory_user_cabinet="/home/raldenprog/CFT/the_best_service/hackaton_cft/app/api/database" sys.path.insert(0, directory_user_cabinet) from connect_db import connect_db """ conn, c = connect_db()
