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': '' }