def settings():
if request.method == 'GET':
conn = mysql.connect()
cursor = conn.cursor()
subscription = int.from_bytes(
get_subscription(cursor, session['email'], True), 'big')
if subscription == 1:
return render_template('adminSubscription.html',
subscription="checked")
else:
return render_template('adminSubscription.html', subscription="")
elif request.method == 'POST':
conn = mysql.connect()
cursor = conn.cursor()
flag = request.form.get("flag")
# USER IS SUBSCRIBING
if flag == 'SUBSCRIBE':
query = 'UPDATE admin SET isSubscribed = %s WHERE email = %s'
cursor.execute(query, (1, session['email']))
conn.commit()
conn.close()
return jsonify({'response': 200})
# USER IS UNSUBSCRIBING
elif flag == 'UNSUBSCRIBE':
query = 'UPDATE admin SET isSubscribed = %s WHERE email = %s'
cursor.execute(query, (0, session['email']))
conn.commit()
conn.close()
return jsonify({'response': 200})
def update_status():
if current_user.is_authenticated:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("Select id,is_admin from shopper where id=%s",
[current_user.id])
result = cursor.fetchone()
cursor.close()
conn.close()
if result['is_admin'] == True:
userid = None
prodid = None
oid = None
if request.method == "POST":
userid = request.form.get("userid")
prodid = request.form.get("prodid")
oid = request.form.get("oid")
elif request.method == "GET":
userid = request.args.get("userid")
prodid = request.args.get("prodid")
oid = request.args.get("oid")
if userid and prodid:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"select oid,sid,iid,quantity,trackStatus from orderlist where sid = %s and iid = %s and oid = %s",
[userid, prodid, oid])
order = cursor.fetchone()
cursor.execute(
"update orderlist set trackStatus=%s where oid = %s",
[int(order["trackStatus"]) + 1,
int(order["oid"])])
conn.commit()
cursor.close()
conn.close()
flash('Successfully updated status', "Success")
return redirect(
url_for('adminPrint.view_selected_user') +
"?sid={}".format(userid))
else:
flash('updation failed', "danger")
return redirect(url_for('adminPrint.view_user_booking'))
else:
flash('requires admin login', "info")
return redirect(url_for('mainPrint.lnding'))
else:
flash('Login required', "info")
return redirect(url_for('mainPrint.lnding'))
def dept_to_emp(plan_id):
conn = mysql.connect()
cursor = conn.cursor()
query = 'SELECT * FROM plan WHERE id = %s'
cursor.execute(query, plan_id)
records = cursor.fetchall()
funding_amount = records[0][2]
source_fund_FK = records[0][7]
query = 'SELECT token FROM department_lookup WHERE id = %s'
cursor.execute(query, source_fund_FK)
source_token = cursor.fetchall()[0][0]
query = 'SELECT * FROM employee_plan WHERE ep_plan_FK = %s'
cursor.execute(query, plan_id)
records = cursor.fetchall()
t = Transaction(cursor, conn)
for row in records:
query = 'SELECT token FROM employee WHERE id = %s'
cursor.execute(query, row[0])
dest_token = cursor.fetchall()[0][0]
transfer = client.transfer(funding_amount, source_token, dest_token, dest_token_is_user=True)
t.insert(source_token, dest_token, Transaction.current_time(transfer.created_time), funding_amount)
conn.commit()
conn.close()
complete_employee_plan(plan_id)
simulate_employee_plan(plan_id)
def change_pass():
if request.method == 'POST':
conn = mysql.connect()
cursor = conn.cursor()
new_password = request.form.get('newPassword')
confirm_new_password = request.form.get('confirmNewPassword')
if new_password != confirm_new_password:
return jsonify({'Response': 400})
else:
new_password = bcrypt.hashpw(new_password.encode('utf-8'),
bcrypt.gensalt())
cursor.execute('UPDATE admin SET pass=%s WHERE email=%s',
(new_password, session['email']))
conn.commit()
conn.close()
send_change_conf_email(session['email'], session['firstName'])
flash('Your information has been recorded.')
return redirect(url_for('admin_bp.change_pass'))
else:
return render_template('adminChangePassword.html')
def register_confirmation(sending_token, email=None, user_id=None, name=None):
try:
if 'register_token' in session:
conn = mysql.connect()
cursor = conn.cursor()
verification_token = secrets.token_urlsafe(16)
query = 'INSERT INTO user_token (userID_utoken_FK,token) VALUES (%s, %s)'
cursor.execute(query, (user_id, verification_token))
conn.commit()
conn.close()
verification_url = f'http://127.0.0.1:5000/conf/email_confirmation/{verification_token}'
production_url = f'https://www.nilebookstore.com/conf/email_confirmation/{verification_token}'
message_body = 'Hi ' + name + \
f',\n\nPlease click on the following link to confirm your registration here at ' \
f'Nile!\n\nDevelopment:{verification_url}\n_________________\n\nProduction:' \
f'{production_url}\n\nRegards, Nile Bookstore Management '
msg = Message(subject='Nile Registration Confirmation',
recipients=[email, '*****@*****.**'],
sender='*****@*****.**',
body=message_body)
mail.send(msg)
else:
return redirect(url_for('common_bp.landing_page'))
except pymysql.err.IntegrityError:
return render_template('confirmation/reg_conf.html')
return render_template('confirmation/reg_conf.html')
def edit_promo_form():
if request.method == 'POST':
promo_id = request.form.get('promoId')
conn = mysql.connect()
cursor = conn.cursor()
query = """
SELECT * FROM promotion WHERE ID=%s
"""
cursor.execute(query, promo_id)
results = cursor.fetchall()
conn.close()
if len(results) == 0:
flash('A promotion with that ID does not exists.')
return render_template('./forms/edit_promo_form.html')
else:
return render_template('./forms/edit_promo_form.html',
id=results[0][0],
name=results[0][2],
code=results[0][1],
discount=format(results[0][3], ".0%"),
endDate=results[0][5],
notes=results[0][6])
return render_template('./forms/edit_promo_form.html')
def simulate_startup():
conn = mysql.connect()
cursor = conn.cursor()
t = Transaction(cursor, conn)
ec = EmployeeCard(cursor, conn)
for dept, e_list in client.department_employees.items():
dept_bal = client.retrieve_balance(
dept).gpa.available_balance * (random.randint(1, 19) / 100)
employees = random.sample(e_list, 5)
for e in employees:
transfer = client.transfer(
dept_bal, dept, e, dest_token_is_user=True)
t.insert(dept, e, Transaction.current_time(
transfer.created_time), dept_bal)
card = client.client_sdk.cards.list_for_user(e)[0].token
mid_identifer = random.choice(MIDS)
employee_transaction = simulate(
card, amount=dept_bal * (random.randint(1, 19) / 100), mid=mid_identifer)
t.insert(card, mid_identifer, Transaction.current_time(
employee_transaction.created_time), employee_transaction.amount, is_card=True)
ec.insert(e, card)
conn.close()
def change_name():
if request.method == 'GET':
return render_template('adminChangeName.html')
else:
conn = mysql.connect()
cursor = conn.cursor()
fname = request.form.get('inputFirstname')
firstLetter = fname[0].upper()
fname = firstLetter + fname[1:].lower()
lname = request.form.get('inputLastname')
firstLetter = lname[0].upper()
lname = firstLetter + lname[1:].lower()
session['firstName'] = fname
cursor.execute('UPDATE admin SET firstname = %s WHERE email = %s',
(fname, session['email']))
conn.commit()
session['lastName'] = lname
cursor.execute('UPDATE admin SET lastname = %s WHERE email = %s',
(lname, session['email']))
conn.commit()
conn.close()
send_change_conf_email(session['email'], session['firstName'])
flash('Your information has been recorded.')
return redirect(url_for('admin_bp.change_name'))
def view_selected_user():
if current_user.is_authenticated:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("Select id,is_admin from shopper where id=%s",
[current_user.id])
result = cursor.fetchone()
cursor.close()
conn.close()
if result['is_admin'] == True:
sid = None
if request.method == "POST":
sid = request.form.get("sid")
elif request.method == "GET":
sid = request.args.get("sid")
if sid:
items = []
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"select oid,sid,iid,quantity,trackStatus from orderlist where sid = %s",
[sid])
orders = cursor.fetchall()
for order in orders:
cursor.execute(
"Select id,itemname,price,image from product where id = %s",
[int(order.get("iid"))])
tmp = cursor.fetchone()
tmp["oid"] = order.get("oid")
tmp["sid"] = sid
tmp["quantity"] = order.get("quantity")
tmp["trackStatus"] = order.get("trackStatus")
tmp["stackCost"] = order.get("quantity") * tmp.get("price")
items.append(tmp.copy())
cursor.close()
conn.close()
return render_template("viewUserBooking.html", items=items)
else:
flash('no user selected', "info")
redirect(url_for('adminPrint.view_user_booking'))
else:
flash('requires admin login', "info")
return redirect(url_for('mainPrint.lnding'))
else:
flash('Login required', "info")
return redirect(url_for('mainPrint.lnding'))
def overview(ctx=None):
"""
Given a department (?department), return all the employees in that department
{
[
{
"name": "Firstname Lastname"
"id": employeeID
},
{
"name": "Firstname Lastname"
"id": employeeID
},
{
"name": "Firstname Lastname"
"id": employeeID
},
]
}
"""
conn = mysql.connect()
cursor = conn.cursor()
dept = request.args.get('department')
name = request.args.get('term')
if dept is None or name is None:
return jsonify(query_error=True)
query = '''SELECT token FROM department_lookup WHERE department = %s'''
cursor.execute(query, dept)
dept_token = cursor.fetchall()[0][0]
query = '''SELECT id, first_name, last_name FROM employee WHERE employee_dept_FK = %s'''
cursor.execute(query, dept_token)
employee = cursor.fetchall()
print(employee, file=sys.stderr)
if name and ' ' in name:
fname = name[:name.find(' ')].lower()
lname = name[name.find(' '):].lower()
e_payload = [{
"name": f'{e[1]} {e[2]}',
"id": e[0]
} for e in employee if fname in e[1] and lname in e[2]]
else:
name = name.lower()
e_payload = [{
"name": f'{e[1]} {e[2]}',
"id": e[0]
} for e in employee if name in e[1].lower() or name in e[2].lower()]
conn.close()
return jsonify(e_payload)
def login(ctx=None):
if not session.get('db_init'):
try:
load_values()
print('starting simulation....')
# simulate_startup()
except Exception as e:
print(e)
if check_login(session):
return redirect(url_for('user_bp.overview'))
form = LoginForm()
if request.method == 'GET':
session['company_name'] = client.BUSINESS_NAME
return render_template('login.html', form=form)
else:
if form.validate_on_submit():
conn = mysql.connect()
cursor = conn.cursor()
query = '''
SELECT manager_dept_FK,first_name, last_name, email, title, pass, gender
FROM manager WHERE email = %s
'''
cursor.execute(query, (request.form.get('email')))
try:
manager = cursor.fetchall()[0]
print(manager)
query = '''SELECT department FROM department_lookup WHERE id = %s'''
cursor.execute(query, (manager[0]))
department = cursor.fetchall()[0][0]
if manager[5] == request.form.get('password'):
session['logged_in'] = True
session['manager_fname'] = manager[1]
session['manager_lname'] = manager[2]
session['manager_email'] = manager[3]
session['manager_dept'] = department
session['manager_title'] = manager[4]
session['manager_gender'] = manager[6]
print(session)
conn.close()
return redirect(url_for('user_bp.overview'))
else:
flash("Account could not be found", category='err')
conn.close()
return redirect(url_for("common_bp.login"))
except:
flash("Account does not exist.", category='err')
conn.close()
return redirect(url_for('common_bp.login'))
else:
flash(gather_form_errors(form)[0], category='err')
return redirect(url_for('common_bp.login'))
def load_user(user_id):
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"Select username,id,pword,is_admin from shopper where id=%s",
[int(user_id)])
result = cursor.fetchone()
cursor.close()
conn.close()
return user(result["username"], result["id"], result["is_admin"])
def load_values():
conn = mysql.connect()
cursor = conn.cursor()
print('\n\nINITIALIZING DB\n\n')
query1 = """
INSERT INTO department_lookup (token, department)
VALUES (%s,%s)"""
for i, dept in enumerate(client.departments):
cursor.execute(query1, (dept.token, client.DEPARTMENT_LIST[i]))
print(dept.token + client.DEPARTMENT_LIST[i] + ' has been inserted.',
file=stderr)
emp = Employee(cursor, conn, immediate_commit=False)
for e in client.employees:
emp.insert(e.token, e.first_name, e.last_name, e.parent_token)
print(e.token + 'h has been inserted.', file=stderr)
desc = "My primary role is managing different banking sectors involved in asset management, sanctioning loans, " \
"mortgages, investments, and account operations. I oversee the efficient day to day processes as well as " \
"preparing forecasts and reports to drive the overall success of our clients and the department. "
man = Manager(cursor, conn, immediate_commit=False)
for dept in client.DEPARTMENT_LIST:
man.insert(client.MANAGERS[dept]['email'],
client.MANAGERS[dept]['pass'],
client.MANAGERS[dept]['first_name'],
client.MANAGERS[dept]['last_name'], 'Sr. Division Manager',
desc, client.MANAGERS[dept]['manager_dept_FK'],
client.MANAGERS[dept]['gender'])
trans = Transaction(cursor, conn, immediate_commit=False)
for t in client.transactions:
if t.recipient_user_token is None:
trans.insert(
t.sender_business_token,
t.recipient_business_token,
Transaction.current_time(t.created_time),
t.amount,
)
else:
trans.insert(
t.sender_business_token,
t.recipient_user_token,
Transaction.current_time(t.created_time),
t.amount,
)
conn.commit()
conn.close()
simulate_startup()
session['db_init'] = True
create_background_scheduler()
def price():
conn = mysql.connect()
cursor = conn.cursor()
min_price = float(request.form.get('min_price'))
max_price = float(request.form.get('max_price'))
print(min_price, max_price)
query = '''SELECT
title,
price,
CONCAT(authorFirstName, ' ', authorLastName) AS author_name,
ISBN,
summary,
publisher,
publicationDate,
numPages,
(SELECT binding FROM binding WHERE binding.id=book.bindingID_book_FK) AS binding,
(SELECT genre FROM genre WHERE genre.id=book.genreID_book_FK) AS genre,
(SELECT type FROM product_type WHERE product_type.id=book.typeID_book_FK) AS type,
nile_cover_ID
FROM book
WHERE %s < price AND %s > price'''
try:
cursor.execute(query, (min_price, max_price))
results = cursor.fetchall()
print(results)
header = [desc[0] for desc in cursor.description]
books = [dict(zip(header, result)) for result in results]
print(books)
genres = get_genres(cursor)
genre_counts = get_genres_count(cursor)
bindings = get_bindings(cursor)
binding_counts = get_bindings_count(cursor)
conn.close()
return render_template('browse.html',
books=books,
genres=genres,
genre_counts=genre_counts,
bindings=bindings,
binding_counts=binding_counts)
except pymysql.Error as e:
print(e)
return redirect(url_for('common_bp.landing_page'))
def active_plans():
conn = mysql.connect()
cursor = conn.cursor()
now = time_now()
now_time = now.strftime("%Y-%m-%d %H:%M:%S")
q = """SELECT COUNT(id) FROM plan
WHERE (start_date >= %s AND end_date <= %s) OR (end_date is NULL)"""
cursor.execute(q, (now_time, now_time))
data = cursor.fetchall()[0][0]
conn.close()
return float(data)
def profile(ctx=None):
conn = mysql.connect()
cursor = conn.cursor()
query = 'SELECT description FROM manager WHERE email = %s'
cursor.execute(query, (session['manager_email']))
description = cursor.fetchall()[0][0]
conn.close()
if description is None or description.strip() == '':
description = "None Provided"
return render_template('profile/profile.html', description=description)
def add_books_form():
if request.method == 'POST':
book_title = string.capwords(request.form.get('bookTitle')).strip()
isbn = request.form.get('isbn').replace("-", "").strip()
author_firstname = string.capwords(
request.form.get('authorFirstName')).strip()
author_lastname = string.capwords(
request.form.get('authorLastName')).strip()
edition = request.form.get('edition').strip()
publisher = request.form.get('publisher').strip()
genre = request.form.get('genre').strip()
p_type = request.form.get('productType').strip()
num_pages = request.form.get('numPages').strip()
date_published = request.form.get('datePublished').strip()
recv_stock = request.form.get('recvStock').strip()
binding_type = request.form.get('bindingType').strip()
price = request.form.get('price').strip()
cover_id = request.form.get('coverID').strip()
book_summary = request.form.get('bookSummary').strip()
print(cover_id, file=sys.stderr)
conn = mysql.connect()
cursor = conn.cursor()
query = """
INSERT INTO book (ISBN, bindingID_book_FK, genreID_book_FK, typeID_book_FK, title, price, numPages, nile_cover_ID,
edition, publisher, publicationDate, stock, authorFirstName, authorLastName, summary)
VALUES (
%s,
(SELECT id FROM binding WHERE binding = %s),
(SELECT id FROM genre WHERE genre = %s),
(SELECT id from product_type where type = %s),
%s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s)
"""
try:
cursor.execute(
query,
(isbn, binding_type, genre, p_type, book_title, price,
num_pages, cover_id, edition, publisher, date_published,
recv_stock, author_firstname, author_lastname, book_summary))
conn.commit()
conn.close()
except Exception as e:
flash('Book already exists in the database')
return redirect(url_for('admin_bp.add_books_form'))
flash('Book has been successfully added.')
return redirect(url_for('admin_bp.add_books_form'))
return render_template('./forms/manage_books_form.html')
def validate_Username(self, Username):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("Select username from shopper where username=%s",
[Username.data])
user = cursor.fetchone()
cursor.close()
conn.close()
if user and len(user) > 0:
raise ValueError("Username Already Exist")
def view_user_booking():
if current_user.is_authenticated:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("Select id,is_admin from shopper where id=%s",
[current_user.id])
result = cursor.fetchone()
cursor.close()
conn.close()
if result['is_admin'] == True:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("Select id,username,is_admin from shopper")
users = cursor.fetchall()
cursor.close()
conn.close()
return render_template("viewUser.html", users=users)
else:
flash('requires admin login', "info")
return redirect(url_for('mainPrint.lnding'))
else:
flash('Login required', "info")
return redirect(url_for('mainPrint.lnding'))
def savefile(filedata):
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("Select max(id) as last from product")
result = cursor.fetchone()
cursor.close()
conn.close()
uploade_filename, file_extension = os.path.splitext(filedata.filename)
save_file_name = str(int(result["last"]) + 1) + file_extension
filedata.save(
os.path.join(current_app.root_path, 'static', 'images', 'shoes',
save_file_name))
return os.path.join('shoes', save_file_name)
def addProduct():
if current_user.is_authenticated:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("Select id,is_admin from shopper where id=%s",[current_user.id])
result=cursor.fetchone()
cursor.close()
conn.close()
if result['is_admin']== True:
addProduct=addItemForm()
if addProduct.validate_on_submit():
Itm_name=addProduct.Item_Name.data
price=addProduct.Price.data
filename=savefile(addProduct.Picture.data)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("insert into product (itemname,image,price) value (%s,%s,%s)",[Itm_name,filename,price])
conn.commit()
cursor.close()
conn.close()
print(Itm_name,price,filename)
flash('new product added',"success")
return render_template("addProduct.html",form=addProduct)
else:
flash('You are not an admin. Please use an admin account',"danger")
return redirect(url_for("mainPrint.lnding"))
else:
flash('login is required',"warning")
return redirect(url_for("mainPrint.lnding"))
def create_plan():
form = create_plan_form(session)
if request.method == 'GET':
return render_template('plans/create_plan/create_plan_partial.html',
form=form,
current_date=custom_strftime(
SupportedTimeFormats.FMT_SIDE_UI,
datetime.now()))
else:
if form.validate_on_submit():
conn = mysql.connect()
cursor = conn.cursor()
return create_plan_execution(conn, cursor, Forminator(form))
else:
return short_error(form)
def query_isbn(search_query=None):
if request.args is None:
return redirect(url_for('common_bp.landing_page'))
else:
if len(request.args) == 1:
try:
conn = mysql.connect()
cursor = conn.cursor()
isbn = request.args['inputISBN']
query = '''SELECT
title,
price,
CONCAT(authorFirstName, ' ', authorLastName) AS author_name,
ISBN,
summary,
publisher,
publicationDate,
numPages,
(SELECT binding FROM binding WHERE binding.id=book.bindingID_book_FK) AS binding,
(SELECT genre FROM genre WHERE genre.id=book.genreID_book_FK) AS genre,
(SELECT type FROM product_type WHERE product_type.id=book.typeID_book_FK) AS type,
nile_cover_ID
FROM book
WHERE book.ISBN=%s ORDER BY title'''
cursor.execute(query, (isbn))
results = cursor.fetchall()
header = [desc[0] for desc in cursor.description]
books = [dict(zip(header, result)) for result in results]
genres = get_genres(cursor)
genre_counts = get_genres_count(cursor)
bindings = get_bindings(cursor)
binding_counts = get_bindings_count(cursor)
conn.close()
return render_template('browse.html',
books=books,
genres=genres,
genre_counts=genre_counts,
bindings=bindings,
binding_counts=binding_counts)
except pymysql.Error as e:
print(e)
return redirect(url_for('common_bp.landing_page'))
def complete_employee_plan(plan_id):
conn = mysql.connect()
cursor = conn.cursor()
query = 'SELECT ep_employee_FK FROM employee_plan WHERE ep_plan_FK = %s'
cursor.execute(query, plan_id)
# list of employees IDs under a plan
employee_ids = cursor.fetchall()
query = 'SELECT start_date, end_date FROM plan WHERE id = %s'
cursor.execute(query, plan_id)
records = cursor.fetchall()
# start date
start_date = records[0][0]
# end date
end_date = records[0][1]
query = 'SELECT token FROM employee WHERE id = %s'
employee_tokens = []
for eid in employee_ids:
cursor.execute(query, eid[0])
employee_tokens.append(cursor.fetchone()[0])
for et in employee_tokens:
payload = {
'user_token': et,
'card_product_token': os.environ['SAM_CARD_PRODUCT_TOKEN'],
}
if end_date:
delta = end_date - start_date
days = delta.days
payload['expiration_offset'] = {
"unit": "DAYS",
"value": days
}
ec = EmployeeCard(cursor, conn, True)
card = client.client_sdk.cards.create(payload)
ec.insert(et, card.token)
query = 'UPDATE plan SET complete = 1 WHERE id = %s'
cursor.execute(query, plan_id)
conn.commit()
conn.close()
def department_utilization():
conn = mysql.connect()
cursor = conn.cursor()
spending = {}
q = """
SELECT SUM(amount) AS total_spending FROM transaction WHERE src_token = %s
GROUP BY src_token
"""
for dept in client.departments:
name = client.READABLE_DEPARTMENTS[dept.business_name_dba]
cursor.execute(q, dept.token)
spending[name] = float(cursor.fetchall()[0][0])
conn.close()
return spending
def current_outgoing_transactions(dept_code):
conn = mysql.connect()
cursor = conn.cursor()
token = find_department_token(dept_code)
"""
Where the source token is the department token
Get list of employees from client.department_employees given token
Query db where src token is employee token
"""
e_list = client.department_employees[token]
now = time_now()
start_date = now.strftime("%Y-%m-%d %H:%M:%S")
twenty_four_ago = (now - timedelta(days=1)).strftime("%Y-%m-%d %H:%M:%S")
q = """SELECT SUM(amount) FROM transaction
WHERE src_token = %s AND create_time BETWEEN %s AND %s
GROUP BY src_token"""
e_query = '''SELECT SUM(t.amount) FROM transaction t
JOIN employee_card ec ON t.src_token = ec.ec_card_token
WHERE ec.ec_employee_token = %s
AND t.create_time BETWEEN %s AND %s
'''
amounts = []
for e in e_list:
cursor.execute(e_query, (e, twenty_four_ago, start_date))
cf = cursor.fetchall()
if cf[0][0] is not None:
amounts.append(cf[0][0])
cursor.execute(q, (token, twenty_four_ago, start_date))
cf = cursor.fetchall()
if len(cf) != 0:
amounts.append(cf[0][0])
tot = float(sum(amounts))
conn.close()
return {
"number_transactions": len(amounts),
"total": tot
}
def department_size():
conn = mysql.connect()
cursor = conn.cursor()
dept_value = request.args.get('department')
if not dept_value.strip():
return short_error(err_list=[UNKNOWN_ERROR])
q = """SELECT count(employee_dept_FK) as length
FROM employee e
JOIN department_lookup dl on e.employee_dept_FK = dl.token
WHERE dl.department = %s;"""
cursor.execute(q, dept_value)
return jsonify(size=cursor.fetchall()[0][
0] # This will always return something, even 0
)
def delete_plan():
conn = mysql.connect()
cursor = conn.cursor()
q = """DELETE FROM employee_plan WHERE ep_plan_FK = %s"""
q2 = """DELETE FROM plan WHERE id = %s"""
try:
cursor.execute(q, session['MANAGE_FORM']['id'])
cursor.execute(q2, session['MANAGE_FORM']['id'])
conn.commit()
conn.close()
return short_success(manip_type=ManipulationType.DELETED)
except Exception:
# An exception here shouldn't really occur, so log it
conn.close()
return short_error(err_list=[UNKNOWN_ERROR])
def test_4_valid_login_logout(self):
response = self.login('patkennedy79', 'FlaskIsAwesome')
# print response.data
# print help(self.app)
# assert 'You were logged in' in response.data
self.assertEqual(response.status_code, 200)
self.assertNotIn(b'Username or Password Wrong!', response.data)
rv = self.logout()
# assert 'You were logged out' in rv.data
self.assertEqual(rv.status_code, 200)
# rv = self.login('adminx', 'default')
# assert 'Invalid username' in rv.data
# rv = self.login('admin', 'defaultx')
# assert 'Invalid password' in rv.data
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute('delete from users where username="******"')
conn.commit()
def generate_employee_spending_graph(dept_code):
conn = mysql.connect()
cursor = conn.cursor()
dept_token = find_department_token(dept_code)
now = time_now()
start_date = now.strftime("%Y-%m-%d %H:%M:%S")
month_ago = (now - timedelta(days=31)).strftime("%Y-%m-%d %H:%M:%S")
prev_month = (now - timedelta(days=62)).strftime("%Y-%m-%d %H:%M:%S")
q = """SELECT sum(t.amount), day(t.create_time) as e_sum FROM employee e
JOIN employee_card ec on e.token = ec.ec_employee_token
JOIN transaction t on src_token = ec.ec_card_token
WHERE e.employee_dept_FK = %s
AND t.create_time >= %s AND t.create_time <= %s
GROUP BY e_sum
ORDER BY e_sum"""
prev_query = """SELECT sum(t.amount), day(t.create_time) as e_sum FROM employee e
JOIN employee_card ec on e.token = ec.ec_employee_token
JOIN transaction t on src_token = ec.ec_card_token
WHERE e.employee_dept_FK = %s
AND t.create_time >= %s AND t.create_time <= %s
GROUP BY e_sum
ORDER BY e_sum"""
cursor.execute(q, (dept_token, month_ago, start_date))
cf = cursor.fetchall()
employee_sum = {}
for record in cf:
employee_sum[record[1]] = float(record[0])
prev_sum = {}
cursor.execute(prev_query, (dept_token, prev_month, month_ago))
cf = cursor.fetchall()
for record in cf:
prev_sum[record[1]] = float(record[0])
conn.close()
return {'current_month': employee_sum, 'previous_month': prev_sum}
