def customer():
"""
Return customer page. Includes most frequent customer last year. Also,
Airline staff can also see all the boarding records of a customer.
Args:
None
Returns:
Airline Staff customer page
"""
customer_info = None
customer_email = None
if request.method == "POST":
customer_email = request.form['customer_email']
cursor = get_cursor()
cursor.execute(
"SELECT flight.flight_id, dept_airport, dept_time, arrv_airport, arrv_time FROM flight LEFT JOIN ticket on ticket.flight_id = flight.flight_id WHERE ticket.customer_email = %s AND flight.airline = %s",
(customer_email, g.user[5]))
customer_info = cursor.fetchall()
if not customer_info:
customer_info = "e" # Stands for empty. Front-end will display No data
cursor = get_cursor()
cursor.execute(
"SELECT name, email, COUNT(customer_email) FROM customer RIGHT JOIN ticket on customer.email = ticket.customer_email WHERE airline = %s GROUP BY email ORDER BY COUNT(customer_email) DESC LIMIT 1",
(g.user[5], ))
top_customer = cursor.fetchone()
return render_template('a/customer.html',
top_customer=top_customer,
customer_info=customer_info,
email=customer_email)
def addplane():
"""
Return add plane page. Airline staffs can add planes for their company.
Args:
None
Returns:
Airline Staff add flights page
"""
cursor = get_cursor()
cursor.execute(
"SELECT airplane_id, seat FROM airplane WHERE airline = %s ",
(g.user[5]))
airplanes = cursor.fetchall()
if request.method == "POST":
error = None
seat = request.form['seat']
db = get_db()
cursor = get_cursor()
if int(seat) <= 0:
error = "Number should be greater than 0."
if error is None:
try:
cursor.execute(
"INSERT INTO airplane (airline, seat) values (%s, %s)",
(g.user[5], seat))
db.commit()
return redirect(url_for('a.confirm', action="Add airplane"))
except pymysql.Error as e:
db.rollback()
flash(error)
return render_template('a/addplane.html', airplanes=airplanes)
def revenue():
"""
Revenue from the last month/year from direct sell/indirect sell
Args:
None
Returns:
Airline Staff revenue page
"""
cursor = get_cursor()
cursor.execute(
"SELECT SUM(sold_price)*0.9 FROM ticket WHERE BAID IS NOT NULL AND airline = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()",
(g.user[5]))
indirect_sell_month = cursor.fetchone()
cursor.execute(
"SELECT SUM(sold_price) FROM ticket WHERE BAID IS NULL AND airline = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()",
(g.user[5]))
direct_sell_month = cursor.fetchone()
cursor.execute(
"SELECT SUM(sold_price)*0.9 FROM ticket WHERE BAID IS NOT NULL AND airline = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()",
(g.user[5]))
indirect_sell_year = cursor.fetchone()
cursor.execute(
"SELECT SUM(sold_price) FROM ticket WHERE BAID IS NULL AND airline = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()",
(g.user[5]))
direct_sell_year = cursor.fetchone()
"""
Top destination
Args:
None
Returns:
Airline Staff top destination page
"""
cursor = get_cursor()
cursor.execute(
"SELECT arrv_airport FROM flight WHERE airline = %s AND dept_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AND CURDATE() GROUP BY arrv_airport ORDER BY COUNT(arrv_airport) DESC LIMIT 3 ",
(g.user[5]))
last_three_months_dest = cursor.fetchall()
cursor.execute(
"SELECT arrv_airport FROM flight WHERE flight.airline = %s AND dept_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() GROUP BY arrv_airport ORDER BY COUNT(arrv_airport) DESC LIMIT 3 ",
g.user[5])
last_year_dest = cursor.fetchall()
return render_template('a/revenue.html',
direct_sell_month=direct_sell_month,
indirect_sell_month=indirect_sell_month,
direct_sell_year=direct_sell_year,
indirect_sell_year=indirect_sell_year,
last_three_months_dest=last_three_months_dest,
last_year_dest=last_year_dest)
def load_logged_in_user():
"""
If logged in and session hasn't expired, user doesn't need to login again.
By default, all identity information is stored in g.user
Args:
None
Returns:
None
"""
role = session.get('role')
if role == 'a':
username = session.get('username')
if username is None:
g.user = None
else:
cursor = get_cursor()
cursor.execute("SELECT * FROM staff WHERE username = %s",
(username, ))
g.user = cursor.fetchone()
g.username = username
g.role = role
elif role == 'b':
BAID = session.get('BAID')
if BAID is None:
g.user = None
else:
cursor = get_cursor()
cursor.execute("SELECT * FROM booking_agent WHERE BAID = %s",
(BAID, ))
g.user = cursor.fetchone()
g.BAID = BAID # Booking Agent ID
g.username = BAID
g.role = role
elif role == 'c':
email = session.get('email')
name = session.get('name')
if email is None:
g.user = None
g.role = role
else:
cursor = get_cursor()
cursor.execute("SELECT * FROM customer WHERE email = %s",
(email, ))
g.user = cursor.fetchone()
g.username = g.user[1] # username column
g.role = role
else:
g.user = None
def addairport():
"""
Return add airport page. Airline staffs can add airports for their company.
Args:
None
Returns:
Airline Staff add airport page
"""
if request.method == "POST":
error = None
name = request.form['name']
city = request.form['city']
db = get_db()
cursor = get_cursor()
cursor.execute("SELECT * FROM airport WHERE name = %s", (name, ))
if cursor.fetchone() is not None:
error = "The airport is already in the system"
flash(error)
else:
try:
cursor.execute(
"INSERT INTO airport (name, city) values (%s, %s)",
(name, city))
db.commit()
return redirect(url_for('a.confirm', action="Add airport"))
except pymysql.Error as e:
db.rollback()
flash(e)
return render_template('a/addairport.html')
def customer():
"""
Show top 5 customer based on tickets purchased in last 6 months, and top 5 customer based on commissions received last year.
Args:
None
Returns:
Booking Agent index page
"""
cursor = get_cursor()
# 6 months number
cursor.execute(
"SELECT name, email, COUNT(email) FROM customer RIGHT JOIN ticket on customer.email = ticket.customer_email WHERE BAID=%s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND NOW() GROUP BY(email) ORDER BY COUNT(email) DESC LIMIT 5 ",
(g.BAID))
six_months_cnt = cursor.fetchall()
ticket_based = [[], []]
for a, b, c in six_months_cnt:
ticket_based[0].append(a)
ticket_based[1].append(c)
# one year commission
cursor.execute(
"SELECT name, email, SUM(sold_price)*0.1 FROM customer RIGHT JOIN ticket on customer.email = ticket.customer_email WHERE BAID = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND NOW() GROUP BY(email) ORDER BY SUM(sold_price) DESC LIMIT 5 ",
(g.BAID))
one_year_commission = cursor.fetchall()
comm_based = [[], []]
for a, b, c in one_year_commission:
comm_based[0].append(a)
comm_based[1].append(float(c))
return render_template('b/customer.html',
six_months_cnt=six_months_cnt,
one_year_commission=one_year_commission,
ticket_based=ticket_based,
comm_based=comm_based)
def commission():
"""
View total amount of commission received in the past 30 days and the average commission he/she received per ticket booked in the past 30 days and total number of tickets sold by him in the past 30 days. Specifing time range is also allowed.
Args:
None
Returns:
Booking Agent commission page
"""
cursor = get_cursor()
search_commission = None
search_cnt = None
if request.method == "POST":
start_date = request.form["start_date"]
end_date = request.form["end_date"]
cursor.execute(
"SELECT SUM(sold_price)*0.1 FROM ticket WHERE BAID = %s AND purchase_date_time BETWEEN %s AND %s",
(
g.BAID,
start_date,
end_date,
))
search_commission = cursor.fetchall()
search_commission = search_commission[0]
cursor.execute(
"SELECT COUNT(*) FROM ticket WHERE BAID = %s AND purchase_date_time BETWEEN %s AND %s",
(g.BAID, start_date, end_date))
search_cnt = cursor.fetchone()
# fetch past 30 days commission
cursor.execute(
"SELECT SUM(sold_price)*0.1 FROM ticket WHERE BAID = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()",
(g.BAID, ))
thirty_day_commission = cursor.fetchone()
cursor.execute(
"SELECT AVG(sold_price)*0.1 FROM ticket WHERE BAID = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()",
(g.BAID, ))
thirty_day_avg = cursor.fetchone()
cursor.execute(
"SELECT COUNT(*) FROM ticket WHERE BAID = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()",
(g.BAID, ))
thirty_day_cnt = cursor.fetchone()
return render_template('b/commission.html',
search_cnt=search_cnt,
search_commission=search_commission,
thirty_day_avg=thirty_day_avg,
thirty_day_cnt=thirty_day_cnt,
thirty_day_commission=thirty_day_commission)
def flights():
"""
Return Airline staff flights page. By default it displays the flights in 30 days. By POST query it returns the date within certain time range.
Args:
None
Returns:
Airline Staff flights page
"""
cursor = get_cursor()
cursor.execute("SELECT name FROM airport")
airports = cursor.fetchall()
if request.method == "POST":
start_date = request.form['start_date']
end_date = request.form['end_date']
dept_airport = request.form['dept_airport']
arrv_airport = request.form['arrv_airport']
cursor.execute(
'SELECT dept_airport, arrv_airport, DATE_FORMAT(dept_time, "%%Y %%M %%D %%T"), DATE_FORMAT(dept_time, "%%Y %%M %%D %%T"), flight_status, base_price, flight_id FROM flight WHERE airline = %s AND DATE(dept_time) BETWEEN DATE(%s) AND DATE(%s) AND dept_airport = %s AND arrv_airport = %s',
(g.user[5], start_date, end_date, dept_airport, arrv_airport))
flights = cursor.fetchall()
n_flights = []
for flight in flights:
flight = list(flight)
if flight[4] == 0:
flight[4] = "On Time"
elif flight[4] == 1:
flight[4] = "Delayed"
n_flights.append(flight)
else:
# get flights in the following 30 days
cursor.execute(
'SELECT dept_airport, arrv_airport, DATE_FORMAT(dept_time, "%%Y %%M %%D %%T"), DATE_FORMAT(dept_time, "%%Y %%M %%D %%T"), flight_status, base_price, flight_id FROM flight WHERE airline = %s AND DATE(dept_time) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)',
(g.user[5]))
flights = cursor.fetchall()
n_flights = []
for flight in flights:
flight = list(flight)
if flight[4] == 0:
flight[4] = "On Time"
elif flight[4] == 1:
flight[4] = "Delayed"
n_flights.append(flight)
return render_template('a/flights.html',
airports=airports,
flights=n_flights)
def booking_agent():
"""
Return booking agent page. Includes top 5 booking agent in terms of tickets
number sold/ commission received in the last year and the last 5 months, and
a list of all agents.
Args:
None
Returns:
Airline Staff booking agent page
"""
cursor = get_cursor()
# top 5 booking agent in terms of number in three months
cursor.execute(
"SELECT BAID, COUNT(BAID)FROM ticket WHERE BAID IS NOT NULL AND airline = %s AND DATE(purchase_date_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AND CURDATE() GROUP BY BAID ORDER BY COUNT(BAID) DESC LIMIT 5",
(g.user[5], ))
last_three_months_n = cursor.fetchall()
# top 5 booking agent in terms of commission fee in three month
cursor.execute(
"SELECT BAID, SUM(sold_price)*0.1 FROM ticket WHERE BAID IS NOT NULL AND airline=%s AND DATE(purchase_date_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AND CURDATE() GROUP BY BAID ORDER BY SUM(sold_price) DESC LIMIT 5",
(g.user[5], ))
last_three_months_c = cursor.fetchall()
# top 5 booking agent in terms of number in one year
cursor.execute(
"SELECT BAID, COUNT(BAID)FROM ticket WHERE BAID IS NOT NULL AND airline = %s AND DATE(purchase_date_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() GROUP BY BAID ORDER BY COUNT(BAID) DESC LIMIT 5",
(g.user[5], ))
last_year_n = cursor.fetchall()
# top 5 booking agent in terms of commission fee in one year
cursor.execute(
"SELECT BAID, SUM(sold_price)*0.1 FROM ticket WHERE BAID IS NOT NULL AND airline = %s AND DATE(purchase_date_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() GROUP BY BAID ORDER BY SUM(sold_price) DESC LIMIT 5",
(g.user[5], ))
last_year_c = cursor.fetchall()
cursor.execute(
"SELECT BAID, COUNT(BAID)FROM ticket WHERE BAID IS NOT NULL AND airline = %s GROUP BY BAID ORDER BY COUNT(BAID) DESC",
(g.user[5], ))
all_agents = cursor.fetchall()
return render_template(
'a/ba.html',
last_three_months_n=last_three_months_n,
last_three_months_c=last_three_months_c,
last_year_n=last_year_n,
last_year_c=last_year_c,
all_agents=all_agents,
)
def addflights():
"""
Return add flights page. Airline staffs can add flights for their company.
Args:
None
Returns:
Airline Staff add flights page
"""
if request.method == "POST":
error = None
airline = g.user[5]
airplane_id = request.form['airplane_id']
base_price = request.form['base_price']
flight_status = request.form['flight_status']
dept_time = request.form['dept_date'] + ' ' + request.form['dept_time']
arrv_time = request.form['arrv_date'] + ' ' + request.form['arrv_time']
dept_airport = request.form['dept_airport']
arrv_airport = request.form['arrv_airport']
try:
db = get_db()
cursor = db.cursor()
cursor.execute(
"INSERT INTO flight (airline, airplane_id, base_price, flight_status, dept_time, arrv_time, dept_airport, arrv_airport) values (%s,%s,%s,%s,%s,%s,%s,%s)",
(airline, airplane_id, base_price, flight_status, dept_time,
arrv_time, dept_airport, arrv_airport))
db.commit()
return redirect(url_for('a.confirm', action="Add Flight"))
except pymysql.Error as e:
flash(e)
db.rollback()
cursor = get_cursor()
# select all airplane of the company
cursor.execute("SELECT airplane_id FROM airplane WHERE airline = %s",
(g.user[5]))
airplanes = cursor.fetchall()
# select all airports
cursor.execute("SELECT name FROM airport")
airports = cursor.fetchall()
return render_template('a/addflights.html',
airplanes=airplanes,
airports=airports)
def flights():
"""
Return Customer flights.
Args:
None
Returns:
Customer flights page
"""
cursor = get_cursor()
cursor.execute(
"SELECT airline, dept_time, dept_airport, arrv_time, arrv_airport, flight_status FROM flight NATURAL JOIN ticket WHERE customer_email = %s", (g.user[0],))
flights = list(cursor.fetchall())
n_flights = [] # store them in a new list to change the status
for row in flights:
row = list(row)
if row[-1] == 0: # 0 for on time
row[-1] = 'On time'
elif row[-1] == 1: # 1 for delay
row[-1] = 'Delayed'
n_flights.append(row)
return render_template('c/flights.html', flights=n_flights)
def flight_info(flight_id):
"""
Return certain flight info. Displaying all the passengers.
Args:
None
Returns:
Airline Staff flights page
"""
if request.method == "POST":
error = None
flight_id = request.form["flight_id"]
status = request.form["status"]
db = get_db()
cursor = db.cursor()
try:
cursor.execute(
"UPDATE flight SET flight_status=%s WHERE flight_id = %s",
(status, flight_id))
db.commit()
return redirect(url_for('a.confirm', action="Change Status"))
except pymysql.Error as e:
db.rollback()
flash(e)
cursor = get_cursor()
cursor.execute(
"SELECT email, name FROM customer JOIN ticket ON email = customer_email WHERE airline = %s AND flight_id = %s",
(
g.user[5],
flight_id,
))
customers = cursor.fetchall()
return render_template("a/flight_info.html",
flight_id=flight_id,
customers=customers)
def flights():
"""
View all the flights the agent purchased representing a customer.
Args:
None
Returns:
Booking Agent index page
"""
cursor = get_cursor()
cursor.execute(
"SELECT customer.name, customer.email, airline, dept_time, dept_airport, arrv_time, arrv_airport, flight_status FROM flight NATURAL JOIN ticket JOIN customer on customer.email = ticket.customer_email WHERE BAID = %s AND dept_time > NOW()",
(g.BAID, ))
flights = list(cursor.fetchall())
n_flights = [] # store them in a new list to change the status
for row in flights:
row = list(row)
if row[-1] == 0: # 0 for on time
row[-1] = 'On time'
elif row[-1] == 1: # 1 for delay
row[-1] = 'Delayed'
n_flights.append(row)
return render_template('b/flights.html', flights=n_flights)
def reports():
mon_convert = {
1: 'Jan',
2: 'Feb',
3: 'Mar',
4: 'Apr',
5: 'May',
6: 'June',
7: 'July',
8: 'Aug',
9: 'Sep',
10: 'Oct',
11: 'Nov',
12: 'Dec'
}
current_month = int(datetime.datetime.now().strftime("%m"))
"""
Ticket info in the past month/year based on time range.
Args:
None
Returns:
Airline Staff report page
"""
cursor = get_cursor()
search_result = None
start_date = None
end_date = None
if request.method == "POST":
start_date = request.form["start_date"]
end_date = request.form["end_date"]
cursor.execute(
"SELECT COUNT(*) FROM ticket WHERE purchase_date_time BETWEEN %s AND %s AND airline = %s",
(start_date, end_date, g.user[5]))
search_result = cursor.fetchone()
#fetch last one month date
cursor.execute(
"SELECT name, customer_email, purchase_date_time FROM ticket LEFT JOIN customer ON ticket.customer_email = customer.email WHERE purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE() AND airline = %s",
(g.user[5]))
last_month = cursor.fetchall()
#fetch last one year date
cursor.execute(
"SELECT MONTH(purchase_date_time), COUNT(*) FROM ticket WHERE purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() AND airline = %s GROUP BY MONTH(purchase_date_time) ORDER BY MONTH(purchase_date_time) ASC",
(g.user[5]))
last_year = cursor.fetchall()
#rearrange query result of last year
last_year_month = []
last_year_sale = []
for i in last_year:
last_year_month.append(i[0])
last_year_sale.append(i[1])
last_year_convert = [[], []]
for i in range(1, 13):
month = i + current_month
if month > 12:
month = month - 12
if month in last_year_month:
idx = last_year_month.index(month)
last_year_convert[0].append(mon_convert[month])
last_year_convert[1].append(last_year_sale[idx])
else:
last_year_convert[0].append(mon_convert[month])
last_year_convert[1].append(0)
# Revenue Comparison
cursor = get_cursor()
cursor.execute(
"SELECT SUM(sold_price)*0.9 FROM ticket WHERE BAID IS NOT NULL AND airline = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()",
(g.user[5]))
indirect_sell_month = cursor.fetchone()
cursor.execute(
"SELECT SUM(sold_price) FROM ticket WHERE BAID IS NULL AND airline = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()",
(g.user[5]))
direct_sell_month = cursor.fetchone()
cursor.execute(
"SELECT SUM(sold_price)*0.9 FROM ticket WHERE BAID IS NOT NULL AND airline = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()",
(g.user[5]))
indirect_sell_year = cursor.fetchone()
cursor.execute(
"SELECT SUM(sold_price) FROM ticket WHERE BAID IS NULL AND airline = %s AND purchase_date_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()",
(g.user[5]))
direct_sell_year = cursor.fetchone()
# Top destination
cursor = get_cursor()
cursor.execute(
"SELECT arrv_airport FROM flight WHERE airline = %s AND dept_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AND CURDATE() GROUP BY arrv_airport ORDER BY COUNT(arrv_airport) DESC LIMIT 3 ",
(g.user[5]))
last_three_months_dest = cursor.fetchall()
cursor.execute(
"SELECT arrv_airport FROM flight WHERE flight.airline = %s AND dept_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE() GROUP BY arrv_airport ORDER BY COUNT(arrv_airport) DESC LIMIT 3 ",
g.user[5])
last_year_dest = cursor.fetchall()
return render_template('a/reports.html',
last_month=last_month,
last_year=last_year_convert,
search_result=search_result,
start_date=start_date,
end_date=end_date,
direct_sell_month=direct_sell_month,
indirect_sell_month=indirect_sell_month,
direct_sell_year=direct_sell_year,
indirect_sell_year=indirect_sell_year,
last_three_months_dest=last_three_months_dest,
last_year_dest=last_year_dest)
def bill():
"""
Return Past year bill.
Search bill for a user-specified date range
Args:
None
Returns:
Customer index page
"""
mon_convert = { 1: 'Jan',
2: 'Feb',
3: 'Mar',
4: 'Apr',
5: 'May',
6: 'June',
7: 'July',
8: 'Aug',
9: 'Sep',
10: 'Oct',
11: 'Nov',
12: 'Dec'}
current_month = int(datetime.datetime.now().strftime("%m"))
search_total = 'n' # n stands for not searching
start_date, end_date = None, None
search_monthly = [[],[]]
date_s, date_e = datetime.date(1901, 1, 1), datetime.date(1901, 1, 1)
if request.method == "POST":
error = None
start_date = request.form["start_date"] # date processing
end_date = request.form["end_date"]
date_s = datetime.datetime.strptime(start_date, '%Y-%m')
date_e = datetime.datetime.strptime(
end_date, '%Y-%m') + relativedelta.relativedelta(months=1) - relativedelta.relativedelta(days=1)
month_count = relativedelta.relativedelta(
date_e, date_s).months + 1 + relativedelta.relativedelta(date_e, date_s).years * 12
start_date = date_s.strftime('%y-%m-%d')
end_date = date_e.strftime('%y-%m-%d')
cursor = get_cursor()
cursor.execute("SELECT SUM(sold_price) from ticket where DATE(purchase_date_time) BETWEEN %s AND %s AND customer_email = %s GROUP BY customer_email",
(start_date, end_date, g.user[0],))
search_total = cursor.fetchall()
if not search_total:
search_total = 'e' # e stands for empty
search_monthly_query = []
search_monthly = [[], []]
for i in range(month_count):
monthly_start = (
date_s + relativedelta.relativedelta(months=i)).strftime('%y-%m-%d')
monthly_end = (date_s + relativedelta.relativedelta(months=i + 1) -
relativedelta.relativedelta(days=1)).strftime('%y-%m-%d')
cursor.execute("SELECT SUM(sold_price) from ticket where DATE(purchase_date_time) BETWEEN %s AND %s AND customer_email = %s GROUP BY customer_email",
(monthly_start, monthly_end, g.user[0],))
search_monthly_query.append(cursor.fetchone())
for i in range(len(search_monthly_query)):
month = (date_s.month + i)
while month > 12:
month -= 12
search_monthly[0].append(mon_convert[month])
if search_monthly_query[i]:
search_monthly[1].append(float(search_monthly_query[i][0]))
else:
search_monthly[1].append(0)
cursor = get_cursor()
cursor.execute(
"SELECT SUM(sold_price) from ticket where purchase_date_time BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW() AND customer_email = %s GROUP BY customer_email", (g.user[0],))
past_year_spent = cursor.fetchone()
if not past_year_spent:
past_year_spent = 'e' # e stands for empty
past_six_month_spent = []
for i in range(5, -1, -1):
cursor.execute(
"SELECT SUM(sold_price) from ticket where MONTH(purchase_date_time) = MONTH(NOW()) - %s AND customer_email = %s GROUP BY customer_email", (i, g.user[0],))
past_six_month_spent.append(cursor.fetchone())
if len(past_six_month_spent) == 0:
past_six_month_spent = 'e' # e stands for empty
else:
past_six_month = [[], []] # create label and change data type
for i in range(0, 6):
month = i + current_month - 5
if month < 1:
month = month + 12
past_six_month[0].append(mon_convert[month])
if past_six_month_spent[i] is None:
past_six_month[1].append(0)
else:
past_six_month[1].append(float(past_six_month_spent[i][0]))
return render_template('c/bill.html', past_year_spent=past_year_spent[0], past_six_month=past_six_month, past_six_month_spent=past_six_month_spent, search_total=search_total[0][0], start_date=date_s.strftime('%y-%m'), end_date=date_e.strftime('%y-%m'), search_monthly = search_monthly)
