def view_tool(tool_id):
c = None
accessories = utilities.tool_accessories(tool_id)
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
c.execute(
"SELECT * FROM tools AS t JOIN categories AS c ON t.category_id = c.category_id WHERE t.tool_id = %s",
(tool_id))
tools = c.fetchall()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
if c is not None:
c.close()
return {'tools': tools, 'accessories': accessories}
def create_clerk():
login = request.forms.get('login', '').strip()
first_name = request.forms.get('first_name', '').strip()
last_name = request.forms.get('last_name', '').strip()
password = request.forms.get('password', '').strip()
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
sql = "INSERT INTO clerks(login, first_name, last_name, password) VALUES (%s, %s, %s, %s)"
c.execute(sql, (login, first_name, last_name, password))
cust_id = c.lastrowid
connection.commit()
except pymysql.err.IntegrityError:
return template('create_clerk.tpl',
message="The clerk profile already exists.")
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
else:
c.close()
#return template('view_profile.tpl', message='New customer profile created.', cust_id=cust_id)
redirect("/view_clerk/%s" % cust_id)
def generate_customer_report():
try:
connection = dbapi.connect()
c = connection.cursor()
sql = "SELECT C.email, C.last_name, C.first_name, COUNT(RT.tool_id)AS rentals\
FROM \
CUSTOMERS AS C JOIN \
RESERVATIONS AS R ON C.customer_id=R.customer_id JOIN \
RESERVATIONS_TOOLS AS RT ON R.reservation_id=RT.reservation_id \
WHERE \
R.start_date<=%s AND R.end_date>=%s \
GROUP BY C.email \
ORDER BY rentals DESC, C.last_name"
now = datetime.datetime.now()
start_of_month = '%d-%d-%d' % (now.year, now.month, 1)
last_day = calendar.monthrange(now.year, now.month)
end_of_month = '%d-%d-%d' % (now.year, now.month, last_day[1])
c.execute(sql, (end_of_month, start_of_month))
rows = c.fetchall()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
c.close()
return template('customer_report', rows=rows)
def new_profile():
email = request.forms.get('email', '').strip()
first_name = request.forms.get('first_name', '').strip()
last_name = request.forms.get('last_name', '').strip()
password = request.forms.get('password', '').strip()
address = request.forms.get('address', '').strip()
work_phone_cc = request.forms.get('work_phone_cc', '').strip()
work_phone_number = request.forms.get('work_phone_number', '').strip()
home_phone_cc = request.forms.get('home_phone_cc', '').strip()
home_phone_number = request.forms.get('home_phone_number', '').strip()
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
sql = "INSERT INTO customers(email, first_name, last_name, password, address, work_phone_cc, work_phone_number, home_phone_cc, home_phone_number) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
c.execute(sql, (email, first_name, last_name, password, address, work_phone_cc, work_phone_number, home_phone_cc, home_phone_number))
customer_id = c.lastrowid
connection.commit()
except pymysql.err.IntegrityError:
return template('create_profile.tpl', message="The profile already exists.")
except pymysql.err.Error as e:
return template('error.tpl', message='An error occurred. Error {!r}, errno is {}'.format(e, e.args[0]))
else:
c.close()
response.set_cookie("customer_id", str(customer_id))
redirect('/customer_main_menu')
def view_profile(customer_id):
print(customer_id)
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
c.execute(
"SELECT CONCAT(first_name, ' ', last_name) AS customer_name, email, address, \
CONCAT(work_phone_cc, ' ', work_phone_number) AS work_phone, CONCAT(home_phone_cc, ' ', home_phone_number) AS home_phone \
FROM customers \
WHERE customer_id = %s", (customer_id))
customer = c.fetchone()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
c.close()
reservations = utilities.get_reservation_details_by_customer(customer_id)
return {'customer': customer, 'reservations': reservations, 'message': ''}
def create_tool():
short_description = request.forms.get('short_description', '').strip()
full_description = request.forms.get('full_description', '').strip()
deposit = request.forms.get('deposit', '').strip()
day_price = request.forms.get('day_price', '').strip()
original_price = request.forms.get('original_price', '').strip()
# slightly funky, but have to split out the first value to be the int
category_id = int(request.forms.get('category_id', '').split()[0])
accessories_field = request.forms.get('accessories', '[]')
accessories = eval(accessories_field)
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
sql = "INSERT INTO tools(short_description, full_description, deposit, day_price, original_price, category_id) VALUES (%s, %s, %s, %s, %s, %s)"
c.execute(sql, (short_description, full_description, deposit, day_price, original_price, category_id))
tool_id = c.lastrowid
for accessory in accessories:
c.execute("INSERT INTO tool_accessories(tool_id, description) VALUES(%s, %s)", (tool_id, accessory));
connection.commit()
except pymysql.err.IntegrityError:
return template('create_tool.tpl', message="The tool already exists.")
except pymysql.err.Error as e:
return template('error.tpl', message='An error occurred. Error {!r}, errno is {}'.format(e, e.args[0]))
finally:
c.close()
redirect("/view_tool/%s" % tool_id)
def reservation_receipt(reservation_id):
try:
credit_card = request.forms.get('credit_card', '').strip()
expiration_date = request.forms.get('expiration_date', '')
expiration_date_datetime = datetime.datetime.strptime(
expiration_date, '%m/%d/%Y')
utilities.update_credit_card(reservation_id, credit_card,
expiration_date_datetime)
utilities.update_pickup_clerk(reservation_id)
reservation = utilities.view_reservation(reservation_id)
tools = utilities.reservation_tools(reservation_id)
data = reservation.copy()
connection = dbapi.connect() # return db connection
c = connection.cursor()
sql = "SELECT first_name, last_name FROM customers WHERE customer_id = %s"
c.execute(sql, data['customer_id'])
tmp = c.fetchone()
data['customer_name'] = tmp['first_name'] + ' ' + tmp['last_name']
sql = "SELECT first_name, last_name FROM clerks WHERE clerk_id = %s"
c.execute(sql, data['clerk_id_pickup'])
tmp = c.fetchone()
data['clerk_pickup_name'] = tmp['first_name'] + ' ' + tmp['last_name']
c.close()
table = ""
deposit = 0
rental = 0
for row in tools:
table = table + "<tr><td>{tool_id}</td><td>{short_description}</td></tr>".format(
tool_id=row['tool_id'],
short_description=row['short_description'])
deposit += row['deposit']
rental += row['day_price']
data['tool_table'] = table
data['deposit_required'] = deposit
rental = rental * utilities.date_differance(reservation['start_date'],
reservation['end_date'])
data['estimated_cost'] = rental
data['credit_card'] = credit_card
data['expiration_date'] = expiration_date
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
except:
return template('error.tpl',
message='An error occurred in pick_up_reservation')
else:
return template('reservation_receipt.tpl', data)
def process_service_order():
tool_id = int(request.forms.get('tool_id', ''))
start_date = request.forms.get('start_date', '')
start_date_datetime = datetime.datetime.strptime(start_date, '%m/%d/%Y')
print("start: {:%b, %d %Y}".format(start_date_datetime))
end_date = request.forms.get('end_date', '')
end_date_datetime = datetime.datetime.strptime(end_date, '%m/%d/%Y')
print("end: {:%b, %d %Y}".format(end_date_datetime))
cost = float(request.forms.get('cost', ''))
try:
reserved_tools = [{'tool_id': tool_id}]
no_longer_availables = dbapi.check_available_tools(
reserved_tools, start_date_datetime, end_date_datetime)
print('not_available_for_service' + str(no_longer_availables))
if len(no_longer_availables) > 0:
return {
'tool_id':
tool_id,
'start_date':
start_date,
'end_date':
end_date,
'cost':
cost,
'message':
'Tool ' + str(tool_id) + ' is not available for service.'
}
clerk_id = int(request.get_cookie('clerk_id'))
connection = dbapi.connect() # return db connection
c = connection.cursor()
c.execute(
"INSERT INTO service_orders(clerk_id, tool_id, start_date, end_date, est_cost) VALUES(%s, %s, %s, %s, %s)",
(clerk_id, tool_id, start_date_datetime, end_date_datetime, cost))
connection.commit()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
return {
'tool_id': '',
'start_date': '',
'end_date': '',
'cost': '',
'message': 'Service order added.'
}
def generate_inventory_report():
try:
connection = dbapi.connect()
c = connection.cursor()
sql = "\
SELECT \
t.tool_id, \
t.short_description, \
(t.day_price * COALESCE(rs.days_rented, 0)) AS rental_profit, \
(t.original_price + COALESCE(so.service_order_cost, 0)) AS cost_of_tool, \
((t.day_price * COALESCE(rs.days_rented, 0)) - (t.original_price + COALESCE(so.service_order_cost, 0))) AS total_profit \
FROM \
tools AS t \
LEFT JOIN ( \
SELECT tool_id, SUM(est_cost) service_order_cost \
FROM service_orders \
WHERE end_date < %s \
GROUP BY tool_id) so ON t.tool_id = so.tool_id \
LEFT JOIN ( \
SELECT rt.tool_id, SUM(DATEDIFF(r.end_date, r.start_date)) days_rented \
FROM reservations AS r JOIN reservations_tools AS rt ON r.reservation_id = rt.reservation_id \
WHERE r.end_date < %s \
GROUP BY rt.tool_id) rs ON t.tool_id = rs.tool_id \
WHERE NOT EXISTS (SELECT * FROM sells WHERE t.tool_id = tool_id) \
ORDER BY total_profit DESC"
now = datetime.datetime.now()
first_day_of_month = now.replace(day=1)
last_day_of_month = now.replace(
day=calendar.monthrange(now.year, now.month)[1])
print("now: {:%b, %d %Y}".format(now))
print("first: {:%b, %d %Y}".format(first_day_of_month))
print("last : {:%b, %d %Y}".format(last_day_of_month))
c.execute(sql, (last_day_of_month, last_day_of_month))
#print('inventory_report: ' + str(c._last_executed))
rows = c.fetchall()
print(rows)
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
c.close()
return {'rows': rows}
def sell_tool_post():
tool_id = int(request.forms.get('tool_id', ''))
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
c.execute("SELECT *, (original_price / 2) AS sell_price FROM tools AS t JOIN categories AS c ON t.category_id = c.category_id WHERE t.tool_id = %s", (tool_id))
data = c.fetchone()
c.close()
except pymysql.err.Error as e:
return template('error.tpl', message='An error occurred. Error {!r}, errno is {}'.format(e, e.args[0]))
return data
def view_clerk(clerk_id):
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
sql = "SELECT * FROM clerks WHERE clerk_id = %s"
c.execute(sql, (clerk_id))
data = c.fetchone()
except pymysql.err.Error as e:
return template('error.tpl', message='An error occurred. Error {!r}, errno is {}'.format(e, e.args[0]))
else:
data['message'] = '' # Template expects a message. Used for debugging or informing the user of something without altering the template
return data
finally:
c.close()
def login():
login = request.forms.get('login', '').strip()
password = request.forms.get('password', '').strip()
user_type = request.forms.get('usertype', '').strip()
c = None
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
if user_type == "clerks":
sql = "SELECT clerk_id, login, password FROM clerks WHERE login=%s"
c.execute(sql, (login)) # login or email
result = c.fetchone()
if result is not None and password == result['password']:
response.set_cookie("clerk_id", str(result['clerk_id']))
redirect('/clerk_main_menu')
else:
return {'message': 'Login or password incorrect'}
elif user_type == "customers":
sql = "SELECT customer_id, email, password FROM customers WHERE email=%s"
c.execute(sql, (login)) # login or email
result = c.fetchone()
if result is not None:
if password == result['password']:
response.set_cookie("customer_id",
str(result['customer_id']))
redirect('/customer_main_menu')
else:
return {'message': 'Email or password incorrect'}
else:
redirect('/create_profile')
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
if c is not None:
c.close()
def update_credit_card(reservation_id, cc, ed):
try:
connection = dbapi.connect()
c = connection.cursor()
sql = "UPDATE RESERVATIONS SET credit_card=%s,expiration_date=%s WHERE reservation_id = %s"
c.execute(sql, (cc, ed, reservation_id))
connection.commit()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
c.close()
def update_dropoff_clerk(reservation_id):
try:
connection = dbapi.connect()
c = connection.cursor()
sql = "UPDATE RESERVATIONS SET clerk_id_dropoff=%s WHERE reservation_id = %s"
clerk_id = int(request.get_cookie('clerk_id'))
c.execute(sql, (clerk_id, reservation_id))
connection.commit()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
c.close()
def view_reservation(reservation_id):
try:
connection = dbapi.connect()
c = connection.cursor()
sql = "SELECT * FROM RESERVATIONS " \
"WHERE reservation_id = %s"
c.execute(sql, (reservation_id))
data = c.fetchone()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
else:
return data
finally:
c.close()
def generate_clerk_report():
try:
connection = dbapi.connect()
c = connection.cursor()
sql = "SELECT CONCAT (C.first_name, ' ', C.last_name) as clerk_name, \
COALESCE ( P.rcount, 0) AS pickup_count, \
COALESCE ( D.rcount, 0) AS dropoff_count, \
COALESCE ( P.rcount, 0) + COALESCE( D.rcount,0) AS total_count \
FROM \
CLERKS C LEFT JOIN (SELECT PICKUPS.clerk_id_pickup, COUNT(*) \
AS rcount FROM RESERVATIONS AS PICKUPS WHERE \
PICKUPS.clerk_id_pickup IS NOT NULL \
AND PICKUPS.start_date BETWEEN %s AND %s \
GROUP BY PICKUPS.clerk_id_pickup) \
P ON C.clerk_id=P.clerk_id_pickup \
LEFT JOIN \
(SELECT DROPOFFS.clerk_id_dropoff, COUNT(*) AS rcount \
FROM RESERVATIONS AS DROPOFFS \
WHERE \
DROPOFFS.clerk_id_dropoff IS NOT NULL \
AND DROPOFFS.end_date BETWEEN %s AND %s \
GROUP BY DROPOFFS.clerk_id_dropoff ) \
D ON C.clerk_id=D.clerk_id_dropoff \
ORDER BY total_count DESC"
now = datetime.datetime.now()
start_of_month = '%d-%d-%d' % (now.year, now.month, 1)
last_day = calendar.monthrange(now.year, now.month)
end_of_month = '%d-%d-%d' % (now.year, now.month, last_day[1])
print(end_of_month)
c.execute(sql, (start_of_month, end_of_month, start_of_month, end_of_month))
rows = c.fetchall()
print(rows)
except pymysql.err.Error as e:
return template('error.tpl', message='An error occurred. Error {!r}, errno is {}'.format(e, e.args[0]))
finally:
c.close()
return template('clerk_report', rows=rows)
def get_reservation_details_by_customer(customer_id):
try:
connection = dbapi.connect()
c = connection.cursor()
c.execute(
"SELECT r.reservation_id, r.start_date, r.end_date, r.credit_card, r.expiration_date, c.customer_id, \
CONCAT(c.first_name, ' ', c.last_name) AS customer_name, \
CONCAT(d.first_name, ' ', d.last_name) dropoff_clerk, \
CONCAT(p.first_name, ' ', p.last_name) pickup_clerk, \
t.tool_id, short_description, deposit, day_price, ta.description \
FROM \
customers c \
JOIN \
reservations r ON (c.customer_id = r.customer_id) \
JOIN \
reservations_tools rt ON (r.reservation_id = rt.reservation_id) \
JOIN \
tools t ON (t.tool_id = rt.tool_id) \
LEFT OUTER JOIN \
tool_accessories ta ON (t.tool_id = ta.tool_id) \
LEFT OUTER JOIN \
clerks d ON (r.clerk_id_dropoff = d.clerk_id) \
LEFT OUTER JOIN \
clerks p ON (r.clerk_id_pickup = p.clerk_id) \
WHERE \
c.customer_id = %s \
ORDER BY r.start_date DESC, r.reservation_id, t.tool_id",
(customer_id))
rows = c.fetchall()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
c.close()
return get_reservation_data(rows)
def tool_accessories(tool_id):
c = None
try:
connection = dbapi.connect()
c = connection.cursor()
sql = "SELECT * FROM tool_accessories NATURAL JOIN tools WHERE tool_id = %s"
c.execute(sql, (tool_id))
data = c.fetchall()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
else:
return data
finally:
if c is not None:
c.close()
def sell_the_tool():
c = None
tool_id = int(request.forms.get('tool_id', ''))
clerk_id = int(request.get_cookie('clerk_id'))
sale_date = time.strftime('%Y-%m-%d %H:%M:%S')
try:
connection = dbapi.connect()
c = connection.cursor()
sql = "INSERT INTO sells(tool_id, clerk_id, sale_date) VALUES (%s, %s, %s)"
c.execute(sql, (tool_id, clerk_id, sale_date))
connection.commit()
except pymysql.err.Error as e:
return template('error.tpl', message='An error occurred. Error {!r}, errno is {}'.format(e, e.args[0]))
finally:
if c is not None:
c.close()
return
def drop_off(reservation_id):
try:
utilities.update_dropoff_clerk(reservation_id)
data = utilities.view_reservation(reservation_id)
tools = utilities.reservation_tools(reservation_id)
connection = dbapi.connect() # return db connection
c = connection.cursor()
sql = "SELECT first_name, last_name FROM customers WHERE customer_id = %s"
c.execute(sql, data['customer_id'])
tmp = c.fetchone()
data['customer_name'] = tmp['first_name'] + ' ' + tmp['last_name']
sql = "SELECT first_name, last_name FROM clerks WHERE clerk_id = %s"
c.execute(sql, data['clerk_id_dropoff'])
tmp = c.fetchone()
data['clerk_dropoff_name'] = tmp['first_name'] + ' ' + tmp['last_name']
c.close()
deposit = 0
rental = 0
for row in tools:
deposit += row['deposit']
rental += row['day_price']
data['rental_cost'] = rental * utilities.date_differance(data['start_date'], data['end_date'])
data['deposit_held'] = deposit
data['remaining_cost'] = data['rental_cost'] - data['deposit_held']
except pymysql.err.Error as e:
return template('error.tpl', message='An error occurred. Error {!r}, errno is {}'.format(e, e.args[0]))
except:
return template('error.tpl', message='An error occurred in drop_off')
else:
return template('drop_off.tpl', data)
def make_reservation():
print("Posting to make_reservation.")
start_date = request.forms.get('start_date', '')
start_date_datetime = datetime.datetime.strptime(start_date, '%m/%d/%Y')
end_date = request.forms.get('end_date', '')
end_date_datetime = datetime.datetime.strptime(end_date, '%m/%d/%Y')
reserved_tools_field = request.forms.get('reserved_tools', '[]')
reserved_tools = eval(reserved_tools_field)
if request.forms.get('Calculate Total', '').strip():
print('Calculating total for: ' + reserved_tools_field)
days = utilities.date_differance(start_date_datetime,
end_date_datetime)
print('Day count: ' + str(days))
deposit = 0.0
rental_price = 0.0
for reserved_tool in reserved_tools:
deposit += reserved_tool['deposit']
rental_price += reserved_tool['day_price'] * days
return template('reservation_summary.tpl',
reserved_tools=reserved_tools_field,
start_date=start_date,
end_date=end_date,
rental_price=rental_price,
deposit=deposit)
elif request.forms.get('Reserve Tools', '').strip():
if len(reserved_tools) < 1 or len(reserved_tools) > 50:
print('Trying to reserve ' + str(len(reserved_tools)) + ' tools.')
category = request.forms.get('category', '1')
tools = dbapi.get_available_tools(category, start_date_datetime,
end_date_datetime)
return {
'start_date': start_date,
'end_date': end_date,
'categories': dbapi.get_categories(),
'tools': tools,
'selected_category': category,
'reserved_tools': reserved_tools_field,
'message': 'Only 1 to 50 tools can be reserved at a time.'
}
no_longer_availables = dbapi.check_available_tools(
reserved_tools, start_date_datetime, end_date_datetime)
if len(no_longer_availables) > 0:
category = request.forms.get('category', '1')
tools = dbapi.get_available_tools(category, start_date_datetime,
end_date_datetime)
tool_message = ''
for no_longer_available in no_longer_availables:
if len(tool_message) > 0:
tool_message += ', '
tool_message += no_longer_available['short_description']
return {
'start_date':
start_date,
'end_date':
end_date,
'categories':
dbapi.get_categories(),
'tools':
tools,
'selected_category':
category,
'reserved_tools':
reserved_tools_field,
'message':
'The following tools are no longer available: ' + tool_message
}
customer_id = int(request.get_cookie('customer_id'))
try:
connection = dbapi.connect() # return db connection
c = connection.cursor()
c.execute(
"INSERT INTO reservations(customer_id, start_date, end_date) VALUES(%s, %s, %s)",
(customer_id, start_date_datetime, end_date_datetime))
reservation_id = c.lastrowid
reserved_tools = eval(reserved_tools_field)
for reserved_tool in reserved_tools:
c.execute(
"INSERT INTO reservations_tools(reservation_id, tool_id) VALUES(%s, %s)",
(reservation_id, reserved_tool['tool_id']))
connection.commit()
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
finally:
c.close() # make sure the connection gets closed
redirect('/view_reservation/%s' % reservation_id)
else:
category = request.forms.get('category', '1')
try:
categories = dbapi.get_categories()
print("categories: " + str(categories))
tools = dbapi.get_available_tools(category, start_date_datetime,
end_date_datetime)
print("available tools: " + str(tools))
# removed already reserved tools from returned list
tools = tuple(x for x in tools
if not tool_in_list(x, reserved_tools))
print('return tools: ' + str(tools))
except pymysql.err.Error as e:
return template(
'error.tpl',
message='An error occurred. Error {!r}, errno is {}'.format(
e, e.args[0]))
return {
'start_date': start_date,
'end_date': end_date,
'categories': categories,
'tools': tools,
'selected_category': category,
'reserved_tools': reserved_tools_field,
'message': ''
}