def edit_user():
form = UserForm()
if request.method == 'GET':
email =request.args.get('email')
db = OracleDb()
user = db.sqlalchemy_session.query(ormUser).filter(ormUser.email == email).one()
form.email.data = user.email
form.full_name.data = user.full_name
form.id.data = user.id
return render_template('user_form.html', form=form, form_name="Edit user", action="edit_user")
else:
if form.valemailate() == False:
return render_template('user_form.html', form=form, form_name="Edit user", action="edit_user")
else:
db = OracleDb()
# find user
user = db.sqlalchemy_session.query(ormUser).filter(ormUser.email == form.email.data).one()
# update fields from form data
user.id = form.id.data
user.full_name = form.full_name.data
db.sqlalchemy_session.commit()
return redirect(url_for('user'))
class UserHelper:
def __init__(self):
self.db = OracleDb()
def getVariable(self, user_name=None):
if user_name:
user_name = "'{0}'".format(user_name)
else:
user_name = 'null'
query = "select * from table(ORM_USER_FUNCTION.GetVariable({0}))".format(user_name)
result = self.db.execute(query)
return result.fetchall()
def getVariables(self, function_name=None):
if function_name:
function_name = "'{0}'".format(function_name)
else:
function_name = 'null'
query = "select * from table(orm_user_Function_search.GetVariable({0}))".format(function_name)
result = self.db.execute(query)
return result.fetchall()
def edit_user():
form = UserForm()
if request.method == 'GET':
user_id =request.args.get('user_id')
db = OracleDb()
user = db.sqlalchemy_session.query(ormUser).filter(ormUser.user_id == user_id).one()
# fill form and send to user
form.user_id.data = user.user_id
form.user_name.data = user.user_name
form.user_department.data = user.user_department
form.user_birthday.data = user.user_birthday
form.user_email.data = user.user_email
form.user_year.data = user.user_year
return render_template('user_form.html', form=form, form_name="Edit user", action="edit_user")
else:
if form.validate() == False:
return render_template('user_form.html', form=form, form_name="Edit user", action="edit_user")
else:
db = OracleDb()
# find user
user = db.sqlalchemy_session.query(ormUser).filter(ormUser.user_id == form.user_id.data).one()
# update fields from form data
user.user_department = form.user_department.data
user.user_birthday = form.user_birthday.data.strftime("%d-%b-%y")
user.user_email = form.user_email.data
user.user_name = form.user_name.data
user.user_year = form.user_year.data.strftime("%d-%b-%y")
db.sqlalchemy_session.commit()
return redirect(url_for('user'))
def edit_user():
form = UserForm()
if request.method == 'GET':
user_id = request.args.get('user_id')
db = OracleDb()
user = db.sqlalchemy_session.query(ormUser).filter(ormUser.user_id == user_id).one()
form.user_phone.data = user.user_phone
form.user_name.data = user.user_name
form.user_email.data = user.user_email
return render_template('user_form.html', form=form, form_name="Edit user", action="edit_user")
else:
if form.validate() == False:
return render_template('user_form.html', form=form, form_name="Edit user", action="edit_user")
else:
db = OracleDb()
# find user
user = db.sqlalchemy_session.query(ormUser).filter(ormUser.user_id == form.user_id.data).one()
# update fields from form data
user_name.data = user.user_name
user_surname.data = user.user_email
db.sqlalchemy_session.commit()
return redirect(url_for('user'))
def edit_owner():
form = OwnerForm()
if request.method == 'GET':
owner_phone = request.args.get('owner_phone')
db = OracleDb()
owner = db.sqlalchemy_session.query(ormOwner).filter(ormOwner.owner_phone == owner_phone).one()
form.owner_phone.data = owner.owner_phone
form.owner_birthday.data = owner.owner_birthday
return render_template('owner_form.html', form=form, form_name="Edit owner", action="edit_owner")
else:
if form.validate() == False:
return render_template('owner_form.html', form=form, form_name="Edit owner", action="edit_owner")
else:
db = OracleDb()
owner = db.sqlalchemy_session.query(ormOwner).filter(ormOwner.owner_phone == form.owner_phone.data).one()
owner.owner_phone = form.owner_phone.data
owner.owner_birthday = form.owner_birthday.data.strftime("%d-%b-%y")
db.sqlalchemy_session.commit()
return redirect(url_for('owner'))
def new_editor_file():
db = OracleDb()
if request.method == 'POST':
user_id = int(request.form.get('user_id'))
file_id = int(request.form.get('file_id'))
join_users = db.sqlalchemy_session.query(OrmUser).join(
OrmFileEditor).join(OrmFile).all()
exist = []
for user in join_users:
for file in user.orm_editor:
exist.append((user.user_id, file.file_id))
if (user_id, file_id) not in exist:
editor_obj = OrmFileEditor(user_id=user_id, file_id=file_id)
db = OracleDb()
db.sqlalchemy_session.add(editor_obj)
db.sqlalchemy_session.commit()
else:
flash("already have this row", 'error')
return redirect(url_for('index_edit_files'))
users = db.sqlalchemy_session.query(OrmUser).all()
files = db.sqlalchemy_session.query(OrmFile).all()
return render_template('editor_file_form.html',
users=users,
files=files,
form_name="New editor",
action="new_editor_file")
def search(self):
db = OracleDb()
user = f"'{self.user_name.data}'" if self.user_name.data != 'None' else 'null'
file = f"'{self.file_name.data}'" if self.file_name.data != 'None' else 'null'
query = f"select * from table(editors_search.find({file}, {user}))"
result = db.execute(query)
result = result.fetchall()
if result:
return result
else:
return 'Empty search table'
def search(self):
db = OracleDb()
nick = f"'{self.news_name.data}'" if selfnews_name.data != 'None' else 'null'
name = f"'{self.nick.data}'" if self.nick.data != 'None' else 'null'
query = f"select * from table(search.find({nick}, {name}))"
result = db.execute(query)
result = result.fetchall()
if result:
return result
else:
return 'search table'
def edit_file():
form = FileForm()
if request.method == 'GET':
file_id = request.args.get('file_id')
db = OracleDb()
file = db.sqlalchemy_session.query(OrmFile).filter(
OrmFile.file_id == file_id).one()
# fill form and send to user
form.file_id.data = file.file_id
form.file_name.data = file.file_name
form.file_type.data = file.file_type
form.file_context.data = file.file_context
form.file_owner.data = file.file_owner_id
form.file_date.data = file.file_date
return render_template('file_form.html',
form=form,
form_name="Edit file",
action="edit_file")
else:
if not form.validate():
return render_template('file_form.html',
form=form,
form_name="Edit file",
action="edit_file")
else:
db = OracleDb()
# find user
file = db.sqlalchemy_session.query(OrmFile).filter(
OrmFile.file_id == form.file_id.data).one()
# update fields from form data
file.file_id = form.file_id.data
file.file_name = form.file_name.data
file.file_type = form.file_type.data
file.file_context = form.file_context.data
file.file_owner_id = form.file_owner.data
file.file_date = form.file_date.data.strftime("%d-%b-%y")
db.sqlalchemy_session.commit()
return redirect(url_for('index_file'))
def edit_worker():
form = WorkerForm()
if request.method == 'GET':
worker_id = request.args.get('worker_id')
db = OracleDb()
worker = db.sqlalchemy_session.query(OrmWorker).filter(
OrmWorker.worker_id == worker_id).one()
# fill form and send to user
form.worker_name.data = worker.worker_name
form.worker_surname.data = worker.worker_surname
form.worker_patronymic.data = worker.worker_patronymic
form.worker_birth_date.data = worker.worker_birth_date
form.worker_job_title.data = worker.worker_job_title
return render_template('worker_form.html',
form=form,
form_name="Edit worker",
action="edit_worker?worker_id=" +
request.args.get('worker_id'))
else:
if form.validate() == False:
return render_template('worker_form.html',
form=form,
form_name="Edit worker",
action="edit_worker?worker_id=" +
request.args.get('worker_id'))
else:
db = OracleDb()
# find user
worker = db.sqlalchemy_session.query(OrmWorker).filter(
OrmWorker.worker_id == request.args.get('worker_id')).one()
# update fields from form data
worker.worker_name = form.worker_name.data
worker.worker_surname = form.worker_surname.data
worker.worker_patronymic = form.worker_patronymic.data
worker.worker_birth_date = form.worker_birth_date.data.strftime(
"%d-%m-%y")
worker.worker_job_title = form.worker_job_title.data
db.sqlalchemy_session.commit()
return redirect('/worker')
def Document():
db = OracleDb()
result = db.sqlalchemy_session.query(ormDocument).all()
return render_template('Document.html', Documents = result)
def place():
db = OracleDb()
result = db.sqlalchemy_session.query(User).join(date).join(place).all()
return render_template('userskill.html', users=result)
def new_user():
form = UF()
if request.method == 'POST':
if form.validate() == False:
return render_template('user_form.html',
form=form,
form_name="New user",
action="new_user")
else:
new_user = User(
user_birthday=form.user_birthday.data.strftime("%d-%b-%y"),
user_phone=form.user_phone.data,
user_name=form.user_name.data,
)
db = OracleDb()
db.sqlalchemy_session.add(new_user)
db.sqlalchemy_session.commit()
return redirect(url_for('user'))
return render_template('user_form.html',
form=form,
form_name="New user",
action="new_user")
def dashboard():
db = OracleDb()
query1 = (db.sqlalchemy_session.query(ormOwner.owner_phone,
func.count(ormCarOwner.owner_phone
).label('car_count')
).outerjoin(ormCarOwner).group_by(
ormOwner.owner_phone, ormOwner.owner_birthday)).all()
query2 = (db.sqlalchemy_session.query(ormCar.car_number,
func.count(ormCarOwner.owner_phone
).label('owner_count')
).outerjoin(ormCarOwner).group_by(
ormCar.car_number)).all()
owners, car_counts = zip(*query1)
bar = go.Bar(
x=owners,
y=car_counts
)
cars, owner_count = zip(*query2)
pie = go.Pie(
labels=cars,
values=owner_count
)
data = {"bar":[bar], "pie":[pie]}
graphsJSON = json.dumps(data, cls=plotly.utils.PlotlyJSONEncoder)
return render_template('dashboard.html', graphsJSON=graphsJSON)
def ownercar():
db = OracleDb()
result = db.sqlalchemy_session.query(ormOwner).join(ormCarOwner).join(ormCar).all()
return render_template('ownercar.html', owners=result)
def photoplace():
db = OracleDb()
result = db.sqlalchemy_session.query(ormUser).all()
return render_template('photoplace.html', photoplaces = result)
def new_user():
form = UserForm()
if request.method == 'POST':
if form.valemailate() == False:
return render_template('user_form.html',
form=form,
form_name="New user",
action="new_user")
else:
new_user = ormUser(
id=form.id.data,
full_name=form.full_name.data,
)
db = OracleDb()
db.sqlalchemy_session.add(new_user)
db.sqlalchemy_session.commit()
return redirect(url_for('user'))
return render_template('user_form.html',
form=form,
form_name="New user",
action="new_user")
def all_data():
db = OracleDb()
result = db.sqlalchemy_session.query(ormData).all()
print(result)
return render_template('DataAll.html', result=result)
def dashboard():
db = OracleDb()
query1 = (
db.sqlalchemy_session.query(
ormUser.user_name,
func.count(ormEvent.name).label('events')
).\
outerjoin(ormEvent).\
group_by(ormUser.email,ormUser.password)
).all()
query2 = (
db.sqlalchemy_session.query(
ormEvent.name,
func.count(ormUser.email).label('count_email')
). \
outerjoin(ormUser). \
group_by(ormEvent.name)
).all()
name, email = zip(*query1)
bar = go.Bar(x=name, y=email)
name, count_email = zip(*query2)
pie = go.Pie(labels=name, values=count_email)
data = {"bar": [bar], "pie": [pie]}
graphsJSON = json.dumps(data, cls=plotly.utils.PlotlyJSONEncoder)
return render_template('dashboard.html', graphsJSON=graphsJSON)
def new_data():
form = DataForm()
now = datetime.datetime.now()
form.date.data = now
if request.method == 'POST':
if form.validate() == False:
return render_template('data.html',
form=form,
form_name="New Data",
action="new_data")
else:
new_var = ormData(data=now,
value=form.value.data,
variable_name_fk=form.variable_name_fk.data,
test_fk=form.test_name_fk.data)
db = OracleDb()
db.sqlalchemy_session.add(new_var)
db.sqlalchemy_session.commit()
return redirect(url_for('all_data'))
return render_template('data.html',
form=form,
form_name="New Data",
action="new_data")
def delete_user():
user_id = request.form['user_id']
db = OracleDb()
result = db.sqlalchemy_session.query(ormUser).filter(ormUser.user_id ==user_id).one()
db.sqlalchemy_session.delete(result)
db.sqlalchemy_session.commit()
return user_id
def all_variable():
db = OracleDb()
result = db.sqlalchemy_session.query(ormVariable).all()
print(result)
return render_template('VariableAll.html', result=result)
def date():
db = OracleDb()
result = db.sqlalchemy_session.query(date).all()
return render_template('date.html', date=result)
def all_test():
db = OracleDb()
result = db.sqlalchemy_session.query(ormTest).all()
print(result)
return render_template('TestAll.html', results=result)
def all_userhobby():
db = OracleDb()
result = db.sqlalchemy_session.query(ormUserHobby).all()
print(result)
return render_template('all_userhobby.html', result=result)
def new_test():
form = TestForm()
if request.method == 'POST':
if form.validate() == False:
return render_template('test.html',
form=form,
form_name="New test",
action="new_test")
else:
new_var = ormTest(
test_name=form.test.data,
result=form.result.data,
)
db = OracleDb()
db.sqlalchemy_session.add(new_var)
db.sqlalchemy_session.commit()
return redirect(url_for('all_test'))
return render_template('test.html',
form=form,
form_name="New test",
action="new_test")
def user():
db = OracleDb()
result = db.sqlalchemy_session.query(ormUser).all()
return render_template('user.html', users = result)
def variable():
form = VarForm()
if request.method == 'POST':
if form.validate() == False:
return render_template('variable.html',
form=form,
form_name="New variable",
action="variable")
else:
new_var = ormVariable(
variable_name=form.variable.data,
variable_type=form.variable_type.data,
)
db = OracleDb()
db.sqlalchemy_session.add(new_var)
db.sqlalchemy_session.commit()
return redirect(url_for('all_variable'))
return render_template('variable.html',
form=form,
form_name="New variable",
action="variable")
def createDocByTemplate():
db = OracleDb()
result = db.sqlalchemy_session.query(ormUser).join(ormcreateDocByTemplate).join(ormDocument).all()
return render_template('creaateDocByTemplate.html', users = result)
def dashboard():
db = OracleDb()
query1 = (db.sqlalchemy_session.query(
func.count(ormVariable.variable_name),
ormVariable.variable_type.label('type')).group_by(
ormVariable.variable_type)).all()
print(query1)
# query2 = (
# db.sqlalchemy_session.query(
# func.count(ormData.data),
# ormTest.test_name.label('name')
# ).join(ormTest).group_by(ormTest.test_name)
# ).all()
query2 = (db.sqlalchemy_session.query(
ormData.data, func.count(ormTest.test_name)).join(ormTest).group_by(
ormData.data)).all()
query2 = [(y[0].strftime('%Y-%m-%d'), y[1]) for y in query2]
query2 = [(x, sum(map(itemgetter(1), y)))
for x, y in groupby(query2, itemgetter(0))]
variable, counts = zip(*query2)
bar = go.Bar(x=counts, y=variable)
skills, user_count = zip(*query1)
pie = go.Pie(labels=user_count, values=skills)
print(variable, counts)
print(skills, user_count)
data = {"bar": [bar], "pie": [pie]}
graphsJSON = json.dumps(data, cls=plotly.utils.PlotlyJSONEncoder)
return render_template('dashboard.html', graphsJSON=graphsJSON)