def your_commission():
if not current_user.is_authenticated or not current_user.get_type(
) == 'agent':
flash('You must be logged in as a booking agent for this action')
return redirect(url_for('main.index'))
form = CommissionForm()
if form.validate_on_submit():
start_date = form.start.data
end_date = form.end.data
earliest = datetime.combine(start_date, datetime.min.time())
print(earliest)
latest = datetime.combine(end_date + timedelta(days=1),
datetime.min.time())
all_data=db.session.query(Ticket,Purchase).join(Purchase,Ticket.ticket_id==Purchase.ticket_id)\
.filter(Purchase.email_booking==current_user.get_identifier()).filter(Purchase.date>=earliest).all()
print('all data:', all_data)
data=db.session.query(Ticket,Purchase).join(Purchase,Ticket.ticket_id==Purchase.ticket_id)\
.filter(Purchase.email_booking==current_user.get_identifier()).filter(Purchase.date>=earliest).filter(Purchase.date<=latest).all()
total = 0
print('data:', data)
ticket_count = 0
for row in data:
total += row[0].price
ticket_count += 1
total /= 10
if ticket_count == 0:
average_commission = 0
else:
average_commission = total / ticket_count
return render_template('agent/display_commission.html',
commission=total,
num_tickets=ticket_count,
average=average_commission)
return render_template('agent/commission_form.html', form=form)
def add_airplane():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
form = AddAirplaneForm()
if form.validate_on_submit():
airline_name = db.session.query(Airline_Staff).filter(
Airline_Staff.username ==
current_user.get_identifier()).first().airline_name
plane_id = form.airplane_id.data
seat_count = form.seat_count.data
if seat_count <= 0:
flash('Airplane must have at least one seat')
return render_template('/staff/add_airplane.html', form=form)
plane = db.session.query(Airplane).filter_by(airline_name=airline_name,
id=plane_id).first()
if plane is None:
new_plane = Airplane(airline_name=airline_name,
id=plane_id,
seat_count=seat_count)
db.session.add(new_plane)
db.session.commit()
flash('You have added plane ' + str(plane_id) + ' to the system')
return redirect(url_for('main.index'))
flash('There is already a flight with this ID in your airline')
return render_template('staff/add_airplane.html', form=form)
def airline_flights():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
form = AirlineFlightsForm()
if form.validate_on_submit():
first = aliased(Airport)
second = aliased(Airport)
earliest = datetime.combine(form.start.data, datetime.min.time())
latest = datetime.combine(form.finish.data + timedelta(days=1),
datetime.min.time())
airline_name = db.session.query(Airline_Staff).filter(
Airline_Staff.username ==
current_user.get_identifier()).first().airline_name
data = db.session.query(Flight, first, second).join(first, Flight.departs == first.name) \
.join(second, Flight.arrives == second.name). \
filter(Flight.departure_time > earliest).filter(Flight.departure_time < latest).\
filter(Flight.airline_name==airline_name)
if len(form.source_city.data) > 0:
data = data.filter(first.city == form.source_city.data)
if len(form.dest_city.data) > 0:
data = data.filter(second.city == form.dest_city.data)
if len(form.source_airport.data) > 0:
data = data.filter(first.name == form.source_airport.data)
if len(form.dest_airport.data) > 0:
data = data.filter(second.name == form.dest_airport.data)
data = data.all()
return render_template('staff/view_airline_flights.html', data=data)
return render_template('staff/airline_flights.html', form=form)
def frequent_customers():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
airline_name = db.session.query(Airline_Staff).filter(
Airline_Staff.username ==
current_user.get_identifier()).first().airline_name
freq_customer=db.session.query(Purchase.email_customer.label("customer"),func.count(Purchase.ticket_id)).join(Ticket,Purchase.ticket_id==Ticket.ticket_id)\
.filter(Ticket.airline_name==airline_name).group_by(Purchase.email_customer).order_by(func.count(Purchase.ticket_id).desc()).first()
if freq_customer is not None:
freq_customer = freq_customer.customer
else:
freq_customer = "not existent"
flash('Looks like no-one has bought anything from your airline yet')
form = FrequentCustomersForm()
if form.validate_on_submit():
customer = db.session.query(Customer).filter_by(
email=form.email.data).first()
if customer is not None:
session['email'] = form.email.data
return redirect('view_customer_flights')
flash('This user does not exist')
return render_template('staff/frequent_customers.html',
form=form,
freq_customer=freq_customer)
def confirm_purchase():
if not current_user.is_authenticated or not current_user.get_type()=='customer':
flash('You must be logged in as a customer for this action')
return redirect(url_for('main.index'))
if ('airline' not in session) or ('flight_num' not in session) or ('departure' not in session):
flash('no flight selected')
return redirect(url_for('main.index'))
airline_name = session.get('airline')
flight_num = session.get('flight_num')
departure_time = session.get('departure')
price=session.get('price')
session.pop('airline')
session.pop('flight_num')
session.pop('departure')
session.pop('price')
form=ConfirmPurchaseForm()
if form.validate_on_submit():
#Create ticket and Purchase objects
ticket=Ticket(ticket_id=len(Ticket.query.all()),
airline_name=airline_name,
flight_num=flight_num,
departure_time=departure_time,
price=price)
purchase=Purchase(ticket_id=ticket.ticket_id,
email_customer=current_user.get_identifier(),
card_num=form.card_number.data,
card_expiration=form.card_expiration.data,
date=datetime.now())
db.session.add(ticket)
db.session.add(purchase)
db.session.commit()
flash('Purchase successful :)')
return redirect(url_for('main.index'))
flight = db.session.query(Flight).filter_by(airline_name=airline_name,
flight_num=flight_num,
departure_time=departure_time,
).first()
arrival_time=flight.arrival_time
source=flight.departs
destination=flight.arrives
session['airline'] = airline_name
session['flight_num'] = flight_num
session['departure'] = departure_time
session['price']=price
return render_template('customer/purchase_confirmation.html',form=form,
airline_name=airline_name,
flight_num=flight_num,
departure_time=departure_time,
arrival_time=arrival_time,
source=source,
destination=destination,
price=price)
def top_destinations():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
airline_name = db.session.query(Airline_Staff).filter(
Airline_Staff.username ==
current_user.get_identifier()).first().airline_name
last_90 = datetime.now() - timedelta(days=90)
last_365 = datetime.now() - timedelta(days=365)
top_90=db.session.query(Flight.arrives,func.count(Ticket.ticket_id)).\
join(Flight,Flight.airline_name==Ticket.airline_name).filter\
(Flight.departure_time==Ticket.departure_time).filter\
(Flight.flight_num==Ticket.flight_num).filter(Flight.airline_name==airline_name).\
filter(Flight.departure_time>=last_90).group_by(Flight.arrives).\
order_by(func.count(Ticket.ticket_id)).all()[:5]
top_365 = db.session.query(Flight.arrives, func.count(Ticket.ticket_id)). \
join(Flight, Flight.airline_name == Ticket.airline_name).filter \
(Flight.departure_time == Ticket.departure_time).filter \
(Flight.flight_num == Ticket.flight_num).filter(Flight.airline_name == airline_name). \
filter(Flight.departure_time >= last_365).group_by(Flight.arrives).\
order_by(func.count(Ticket.ticket_id)).all()[:5]
return render_template('staff/top_destinations.html',
top_90=top_90,
top_365=top_365)
def myflights():
if not current_user.is_authenticated or not current_user.get_type()=='customer':
flash('You must be logged in as a customer for this action')
return redirect(url_for('main.index'))
data=db.session.query(Ticket,Purchase,Flight).join(Purchase,\
Ticket.ticket_id==Purchase.ticket_id).join(Flight, Ticket.airline_name==Flight.airline_name)\
.filter(Purchase.email_customer==current_user.get_identifier()).filter(Flight.departure_time >= datetime.now()).filter(Ticket.flight_num==Flight.flight_num).\
filter(Ticket.departure_time==Flight.departure_time)
return render_template('customer/customer_flights.html',data=data)
def viewflightratings():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
staff_airline_table = Airline_Staff.query.filter(
Airline_Staff.username == current_user.get_identifier()).first()
staff_airline = staff_airline_table.airline_name
ave_ticket_ratings=Ticket.query.join(Purchase, \
Ticket.ticket_id==Purchase.ticket_id)\
.add_columns(Ticket.airline_name.label('airline_name'),\
Ticket.flight_num.label('flight_num'),\
Ticket.departure_time.label('departure_time'),\
Purchase.rating.label('rating'),\
Purchase.comment.label('comment'))\
.with_entities(Ticket.flight_num.label('flight_num'),\
Ticket.departure_time.label('departure_time'),\
sql_func.avg(Purchase.rating).label("ave_rating"))\
.group_by(Ticket.flight_num,Ticket.departure_time)\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.rating!=None)
ticket_rating_comment=Ticket.query.join(Purchase, \
Ticket.ticket_id==Purchase.ticket_id)\
.add_columns(Ticket.airline_name.label('airline_name'),\
Ticket.flight_num.label('flight_num'),\
Ticket.departure_time.label('departure_time'),\
Purchase.rating.label('rating'),\
Purchase.comment.label('comment'))\
.with_entities(Ticket.flight_num.label('flight_num'),\
Ticket.departure_time.label('departure_time'),\
Purchase.rating.label('rating'),\
Purchase.comment.label('comment'))\
.filter(Ticket.airline_name==staff_airline)\
.filter(or_(Purchase.rating!=None, Purchase.comment!=None, Purchase.comment!=''))
return render_template('staff/viewflightratings.html',\
ave_ticket_ratings=ave_ticket_ratings,\
ticket_rating_comment=ticket_rating_comment)
def view_customer_flights():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
email = session.get('email')
session.pop('email')
airline_name = db.session.query(Airline_Staff).filter(
Airline_Staff.username ==
current_user.get_identifier()).first().airline_name
data=db.session.query(Purchase,Ticket,Flight).join(Ticket,Ticket.ticket_id==Purchase.ticket_id).\
join(Flight,Flight.airline_name==Ticket.airline_name).filter(Flight.flight_num==Ticket.flight_num).\
filter(Flight.departure_time==Ticket.departure_time).\
filter(Ticket.airline_name==airline_name).order_by(Ticket.departure_time).filter(Purchase.email_customer==email)
data = data.all()
session['email'] = email
return render_template('staff/view_customer_flights.html',
data=data,
email=email)
def passenger_list():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
form = PassengerListForm()
airline_name = db.session.query(Airline_Staff).filter(
Airline_Staff.username ==
current_user.get_identifier()).first().airline_name
if form.validate_on_submit():
flight_num = form.flight_num.data
departure_time = form.departure_time.data
flight = db.session.query(Flight).filter_by(
airline_name=airline_name,
flight_num=flight_num,
departure_time=departure_time).first()
if flight is not None:
data=db.session.query(Purchase.email_customer).join(Ticket,Ticket.ticket_id==Purchase.ticket_id).\
filter(Ticket.airline_name==airline_name,Ticket.flight_num==flight_num,Ticket.departure_time==departure_time).distinct().all()
return render_template('staff/view_passenger_list.html', data=data)
return render_template('staff/passenger_list.html', form=form)
def change_flight_status():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
form = ChangeStatusForm()
if form.validate_on_submit():
airline_name = db.session.query(Airline_Staff).filter(
Airline_Staff.username ==
current_user.get_identifier()).first().airline_name
flight_num = form.flight_num.data
departure_time = form.departure_time.data
flight = db.session.query(Flight).filter_by(
airline_name=airline_name,
flight_num=flight_num,
departure_time=departure_time).first()
if flight is not None:
flight.status = form.new_status.data
db.session.commit()
flash('Updated status to:' + form.new_status.data)
return redirect(url_for('main.index'))
flash('Could not find this flight for your airline')
return render_template('staff/change_flight_status.html', form=form)
def staffviewreports():
staff_airline_table = Airline_Staff.query.filter(
Airline_Staff.username == current_user.get_identifier()).first()
staff_airline = staff_airline_table.airline_name
date_last_month = datetime.now() - timedelta(days=30)
date_last_year = datetime.now() - timedelta(days=365)
sold_last_month=Purchase.query.join(Ticket, \
Purchase.ticket_id==Ticket.ticket_id)\
.add_columns(Ticket.airline_name.label('airline_name'))\
.with_entities(sql_func.count(Purchase.ticket_id).label("count"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>date_last_month).first()
num_sold_last_month = sold_last_month.count
if num_sold_last_month == None:
num_sold_last_month = 0
sold_last_year=Purchase.query.join(Ticket, \
Purchase.ticket_id==Ticket.ticket_id)\
.add_columns(Ticket.airline_name.label('airline_name'))\
.with_entities(sql_func.count(Purchase.ticket_id).label("count"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>date_last_year).first()
num_sold_last_year = sold_last_year.count
if num_sold_last_year == None:
num_sold_last_year = 0
query_list_months = intervals_for_num_months(datetime.now(), 12)
graph_labels = make_list_labels(query_list_months)
monthly_sums = []
for i in range(len(query_list_months) - 1):
default_month=Purchase.query.join(Ticket, \
Purchase.ticket_id==Ticket.ticket_id)\
.add_columns(Ticket.airline_name.label('airline_name'))\
.with_entities(sql_func.count(Purchase.ticket_id).label("count"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>=query_list_months[i])\
.filter(Purchase.date<query_list_months[i+1]).first()
default_month_sum = default_month.count
if default_month_sum == None:
default_month_sum = 0
monthly_sums.append(default_month_sum)
form = ReportForm()
if form.validate_on_submit():
inputed_start_date = form.start.data
start_date = date_past = datetime.strptime(str(inputed_start_date),
'%Y-%m-%d')
inputed_end_date = form.end.data
end_date = date_past = datetime.strptime(str(inputed_end_date),
'%Y-%m-%d')
if end_date <= start_date:
flash('Invalid date range')
period_table=Purchase.query.join(Ticket, \
Purchase.ticket_id==Ticket.ticket_id)\
.add_columns(Ticket.airline_name.label('airline_name'))\
.with_entities(sql_func.count(Purchase.ticket_id).label("count"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>=start_date)\
.filter(Purchase.date<end_date).first()
period_table_count = period_table.count
if period_table_count == None:
period_table_count = 0
query_ranges = intervals_for_range(end_date, start_date)
ranges_labels = make_list_labels(query_ranges)
counts = []
for i in range(len(query_ranges) - 1):
thrity_days=Purchase.query.join(Ticket, \
Purchase.ticket_id==Ticket.ticket_id)\
.add_columns(Ticket.airline_name.label('airline_name'))\
.with_entities(sql_func.count(Purchase.ticket_id).label("count"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>=query_ranges[i])\
.filter(Purchase.date<query_ranges[i+1]).first()
thrity_days_count = thrity_days.count
if thrity_days_count == None:
thrity_days_count = 0
counts.append(thrity_days_count)
return render_template('staff/reports_from_form.html',\
period_table_count=period_table_count,\
counts=counts,\
query_ranges=query_ranges,\
ranges_labels=ranges_labels,\
max_ranges=max(counts),\
start_date=start_date,end_date=end_date)
return render_template('staff/staffviewreports.html', sold_last_month=sold_last_month,\
num_sold_last_month=num_sold_last_month,\
num_sold_last_year=num_sold_last_year,\
monthly_sums=monthly_sums,\
query_list_months=query_list_months,\
graph_labels=graph_labels,\
max_default=max(monthly_sums),\
form=form)
def viewbookingagents():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
# staff_airline_table=Airline_Staff.query.filter(Airline_Staff.username==current_user.get_id().split('_')[1:]).first()
staff_airline_table = Airline_Staff.query.filter(
Airline_Staff.username == current_user.get_identifier()).first()
staff_airline = staff_airline_table.airline_name
agent_purchased_ticket = Purchase.query.join(
Ticket, Purchase.ticket_id == Ticket.ticket_id).add_columns(
Purchase.email_booking.label('email_booking'),
Purchase.date.label('date'),
Ticket.airline_name.label('airline_name')).filter(
Ticket.airline_name == staff_airline).filter(
Purchase.email_booking != None)
# top 5 agents based on num of tickets - past month (last 30 days)
date_30_days_ago = datetime.now() - timedelta(days=30)
date_365_days_ago = datetime.now() - timedelta(days=365)
agent_purchased_ticket_30_days = Purchase.query.join(
Ticket, Purchase.ticket_id == Ticket.ticket_id).add_columns(
Purchase.email_booking.label('email_booking'),
Purchase.date.label('date'),
Ticket.airline_name.label('airline_name')).filter(
Ticket.airline_name == staff_airline).filter(
Purchase.email_booking != None).filter(
Purchase.date > date_30_days_ago)
grouped_agent_purchased_ticket_30_days = Purchase.query.join(
Ticket, Purchase.ticket_id == Ticket.ticket_id).add_columns(
Purchase.email_booking.label('email_booking'),
Purchase.date.label('date'),
Ticket.airline_name.label('airline_name')).with_entities(
Purchase.email_booking,
sql_func.count(
Purchase.email_booking).label("count")).group_by(
Purchase.email_booking).filter(
Ticket.airline_name == staff_airline).filter(
Purchase.email_booking != None).filter(
Purchase.date > date_30_days_ago).order_by(
desc("count"))[:5]
# top 5 based on num of tickets - past year (last 365 days)
grouped_agent_purchased_ticket_one_year = Purchase.query.join(
Ticket, Purchase.ticket_id == Ticket.ticket_id).add_columns(
Purchase.email_booking.label('email_booking'),
Purchase.date.label('date'),
Ticket.airline_name.label('airline_name')).with_entities(
Purchase.email_booking,
sql_func.count(Purchase.email_booking).label(
"count")).group_by(Purchase.email_booking).filter(
Ticket.airline_name == staff_airline).filter(
Purchase.email_booking != None).filter(
Purchase.date > date_365_days_ago).order_by(
desc("count"))[:5]
# order_by("count")[:5]
#
commissions_grouped_agent = Purchase.query.join(
Ticket, Purchase.ticket_id == Ticket.ticket_id).add_columns(
Purchase.email_booking.label('email_booking'),
Purchase.date.label('date'),
Ticket.airline_name.label('airline_name'),
Ticket.price.label('price')).with_entities(
Purchase.email_booking.label('email_booking'),
(sql_func.sum(Ticket.price) /
10).label("total_commission")).group_by(
Purchase.email_booking).filter(
Ticket.airline_name == staff_airline).filter(
Purchase.email_booking != None).filter(
Purchase.date > date_365_days_ago).order_by(
desc("total_commission"))[:5]
return render_template('staff/viewbookingagents.html', \
staff_airline=staff_airline,\
agent_purchased_ticket_30_days=agent_purchased_ticket_30_days,\
agent_purchased_ticket=agent_purchased_ticket,\
grouped_agent_purchased_ticket_30_days=grouped_agent_purchased_ticket_30_days,\
grouped_agent_purchased_ticket_one_year=grouped_agent_purchased_ticket_one_year,\
commissions_grouped_agent=commissions_grouped_agent)
def spending():
if not current_user.is_authenticated or not current_user.get_type()=='customer':
flash('You must be logged in as a customer for this action')
return redirect(url_for('main.index'))
# default part
# date one year ago
one_year_ago = datetime.now() - timedelta(days=365)
# table purchases within past year
default_total_table=Purchase.query.join(Ticket, Purchase.ticket_id==Ticket.ticket_id)\
.add_columns(Purchase.email_customer.label('email_customer'), Purchase.date.label('date'), Ticket.price.label('price'))\
.filter(Purchase.email_customer==current_user.get_identifier())\
.filter(Purchase.date>one_year_ago)
# sum for all purchases last year
default_total_sum_table=Purchase.query.join(Ticket, Purchase.ticket_id==Ticket.ticket_id).with_entities(func.sum(Ticket.price).label('all_sum')).filter(Purchase.email_customer==current_user.get_identifier()).filter(Purchase.date>one_year_ago).first()
default_total_sum=default_total_sum_table.all_sum
if default_total_sum==None:
default_total_sum=0
query_list_months=intervals_for_num_months(datetime.now(), 6)
graph_labels=make_list_labels(query_list_months)
monthly_sums=[]
# .filter(Purchase.email_customer==current_user.get_id().split('_')[1:])\
for i in range(len(query_list_months)-1):
default_month=Purchase.query.join(Ticket, Purchase.ticket_id==Ticket.ticket_id)\
.with_entities(func.sum(Ticket.price).label('all_sum'))\
.filter(Purchase.email_customer==current_user.get_identifier())\
.filter(Purchase.date>query_list_months[i])\
.filter(Purchase.date<query_list_months[i+1]).first()
default_month_sum=default_month.all_sum
if default_month_sum==None:
default_month_sum=0
monthly_sums.append(default_month_sum)
form=SpendingForm()
if form.validate_on_submit():
inputed_start_date=form.start.data
start_date=date_past=datetime.strptime(str(inputed_start_date), '%Y-%m-%d')
inputed_end_date=form.end.data
end_date=date_past=datetime.strptime(str(inputed_end_date), '%Y-%m-%d')
if end_date<=start_date:
flash('Invalid date range')
# sum for all purchases from that period
period_table=Purchase.query.join(Ticket, Purchase.ticket_id==Ticket.ticket_id)\
.with_entities(func.sum(Ticket.price).label('all_sum'))\
.filter(Purchase.email_customer==current_user.get_identifier())\
.filter(Purchase.date>=start_date)\
.filter(Purchase.date<=end_date).first()
period_table_sum=period_table.all_sum
if period_table_sum==None:
period_table_sum=0
#query_ranges=intervals_for_range(end_date, start_date-timedelta(seconds=1))
query_ranges=intervals_for_range(end_date, start_date)
ranges_labels=make_list_labels(query_ranges)
sums=[]
for i in range(len(query_ranges)-1):
thrity_days=Purchase.query.join(Ticket, Purchase.ticket_id==Ticket.ticket_id)\
.with_entities(func.sum(Ticket.price).label('all_sum'))\
.filter(Purchase.email_customer==current_user.get_identifier())\
.filter(Purchase.date>=query_ranges[i])\
.filter(Purchase.date<query_ranges[i+1]).first()
thrity_days_sums=thrity_days.all_sum
if thrity_days_sums==None:
thrity_days_sums=0
sums.append(thrity_days_sums)
return render_template('customer/spending_from_form.html',\
period_table_sum=period_table_sum,\
sums=sums,\
query_ranges=query_ranges,\
ranges_labels=ranges_labels,\
max_ranges=max(sums),\
start_date=start_date,end_date=end_date)
return render_template('customer/spending.html',\
default_total_sum=default_total_sum,\
monthly_sums=monthly_sums,\
query_list_months=query_list_months,\
graph_labels=graph_labels,\
max_default=max(monthly_sums),\
form=form)
def ratings():
if not current_user.is_authenticated or not current_user.get_type()=='customer':
flash('You must be logged in as a customer for this action')
return redirect(url_for('main.index'))
# display completed flights of that user on the form page
#completedflights=Purchase.query.join(Ticket, Purchase.ticket_id==Ticket.ticket_id).add_columns(Ticket.airline_name, Ticket.flight_num, Ticket.departure_time) Purchase.email_customer.label('email_customer'), Purchase.date.label('date'), Ticket.price.label('price')).filter(Purchase.email_customer==current_user.get_id().split('_')[1:])
# display past ratings and comments
past_flights=Ticket.query.join(Purchase, Ticket.ticket_id==Purchase.ticket_id)\
.join(Flight, Ticket.airline_name==Flight.airline_name)\
.add_columns(Ticket.airline_name.label("airline_name"),\
Ticket.ticket_id.label("ticket_id"),\
Ticket.flight_num.label("flight_num"),\
Ticket.departure_time.label("departure_time"),\
Purchase.rating.label("rating"),\
Purchase.comment.label("comment"))\
.filter(Flight.arrival_time<datetime.now())\
.filter(Purchase.email_customer==current_user.get_identifier())\
.filter(Ticket.departure_time==Flight.departure_time)\
.filter(Ticket.flight_num==Flight.flight_num)
#result=past_flights.ticket_id
form = CompletedFlights()
if form.validate_on_submit():
if form.rating.data is not None and (form.rating.data<0 or form.rating.data>5):
flash('Please enter a rating between 0 and 5')
#return render_template('customer/selecttorate.html', past_flights=past_flights, form=form)
airline_name=form.airline_name.data
flight_num=form.flight_num.data
departure_time=form.departure.data
check=db.session.query(Purchase)\
.join(Ticket, Purchase.ticket_id==Ticket.ticket_id)\
.join(Flight, Ticket.flight_num==Flight.flight_num)\
.filter(Ticket.airline_name==Flight.airline_name)\
.filter(Ticket.departure_time==Flight.departure_time)\
.filter(Purchase.email_customer==current_user.get_identifier())\
.filter(Flight.arrival_time<datetime.now())\
.filter(Flight.airline_name==airline_name)\
.filter(Flight.flight_num==flight_num)\
.filter(Flight.departure_time==departure_time).first()
# flight=db.session.query(Flight).filter_by(airline_name=airline_name,flight_num=flight_num,departure_time=departure_time).first()
# if flight is not None:
# flight.status=form.new_status.data
# db.session.commit()
# flash('Updated status to:'+form.new_status.data)
#
# purchased_flight=db
#
if check is not None:
tick=check.ticket_id
purchased_flight=db.session.query(Purchase).filter_by(ticket_id=tick).first()
if form.rating.data is not None and form.rating.data>0 and form.rating.data<=5:
purchased_flight.rating=form.rating.data
db.session.commit()
flash('Rating Updated to '+str(form.rating.data))
else:
flash('Rating Not Updated')
if form.comment.data is not None:
purchased_flight.comment=form.comment.data
db.session.commit()
flash('Comment Updated to '+form.comment.data)
# db.session.add(check)
# db.commit()
# session['ticket_id']=ticket_id
#
# if check is not None:
#
#
# return redirect('rate')
# # create new form to comment
# # do return redirect to page to comment
#
# flash('Flight not completed')
return redirect(url_for('main.index'))
return render_template('customer/selecttorate.html', past_flights=past_flights, form=form)
def revenuecomparison():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
staff_airline_table = Airline_Staff.query.filter(
Airline_Staff.username == current_user.get_identifier()).first()
staff_airline = staff_airline_table.airline_name
# last month
date_last_month = datetime.now() - timedelta(days=30)
cust_last_month=Purchase.query.join(Ticket,\
Purchase.ticket_id==Ticket.ticket_id)\
.with_entities(sql_func.sum(Ticket.price).label("sum_month_cust"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>date_last_month)\
.filter(Purchase.email_booking==None)
cust_last_month_value = cust_last_month[0][0]
booking_last_month=Purchase.query.join(Ticket,\
Purchase.ticket_id==Ticket.ticket_id)\
.with_entities(sql_func.sum(Ticket.price).label("sum_month_book"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>date_last_month)\
.filter(Purchase.email_booking!=None)
booking_last_month_value = booking_last_month[0][0]
values_month = [cust_last_month_value, booking_last_month_value]
message_month = "Revenue Comparison (Last Month)"
if values_month == [None, None]:
message_month = "No Revenue this Last Month"
for i in range(len(values_month)):
if values_month[i] == None:
values_month[i] = (0)
labels = ["Direct Revenue", "Indirect Revenue (includes Commission)"]
# last year
date_last_year = datetime.now() - timedelta(days=365)
cust_last_year=Purchase.query.join(Ticket,\
Purchase.ticket_id==Ticket.ticket_id)\
.with_entities(sql_func.sum(Ticket.price).label("sum_year_cust"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>date_last_year)\
.filter(Purchase.email_booking==None)
cust_last_year_value = cust_last_year[0][0]
booking_last_year=Purchase.query.join(Ticket,\
Purchase.ticket_id==Ticket.ticket_id)\
.with_entities(sql_func.sum(Ticket.price).label("sum_year_book"))\
.filter(Ticket.airline_name==staff_airline)\
.filter(Purchase.date>date_last_year)\
.filter(Purchase.email_booking!=None)
booking_last_year_value = booking_last_year[0][0]
values_year = [cust_last_year_value, booking_last_year_value]
message_year = "Revenue Comparison (Last Year)"
if values_year == [None, None]:
message_year = "No Revenue this Last Year"
for i in range(len(values_year)):
if values_year[i] == None:
values_year[i] = (0)
#colors=["#F7464A", "#46BFBD"]
colors = ["#F7464A", "#800000"]
return render_template('staff/revenuecomparison.html',\
booking_last_month=booking_last_month,\
cust_last_month=cust_last_month,\
cust_last_month_value=cust_last_month_value,\
booking_last_month_value=booking_last_month_value,\
set_month=zip(values_month, labels, colors),\
max_month=max(values_month),\
set_year=zip(values_year, labels, colors),\
max_year=max(values_year), \
message_month=message_month,\
message_year=message_year)
def add_flight():
if not current_user.is_authenticated or not current_user.get_type(
) == 'staff':
flash('You must be logged in as an airline staff for this action')
return redirect(url_for('main.index'))
form = AddFlightForm()
if form.validate_on_submit():
airline_name = db.session.query(Airline_Staff).filter(
Airline_Staff.username ==
current_user.get_identifier()).first().airline_name
flight_num = form.flight_num.data
departure_time = form.departure_time.data
arrival_time = form.arrival_time.data
price = form.price.data
status = form.status.data
departs = form.departs.data
arrives = form.arrives.data
airplane_id = form.airplane_id.data
if not departure_time > datetime.now():
flash('Flight must be in the future')
return render_template('/staff/add_flight.html', form=form)
flight = db.session.query(Flight).filter_by(
airline_name=airline_name,
flight_num=flight_num,
departure_time=departure_time).first()
if flight is not None:
flash(
'There is already a flight with that number and departure time in your airline'
)
return render_template('staff/add_flight.html', form=form)
airplane = db.session.query(Airplane).filter_by(
airline_name=airline_name, id=airplane_id).first()
if departure_time > arrival_time:
flash('arrival must be after departure')
return render_template('staff/add_flight.html', form=form)
if airplane is None:
flash('There is no airplane with this id in your airline')
return render_template('staff/add_flight.html', form=form)
departs_airport = db.session.query(Airport).filter_by(
name=departs).first()
if departs_airport is None:
flash('There is no airport with this name(departure airport)')
return render_template('staff/add_flight.html', form=form)
arrives_airport = db.session.query(Airport).filter_by(
name=arrives).first()
if arrives_airport is None:
flash('There is no airport with this name(arrival airport)')
return render_template('staff/add_flight.html', form=form)
new_flight = Flight(airline_name=airline_name,
flight_num=flight_num,
departure_time=departure_time,
arrival_time=arrival_time,
price=price,
status=status,
departs=departs,
arrives=arrives,
airplane_id=airplane_id)
db.session.add(new_flight)
db.session.commit()
flash('Added Flight')
return redirect(url_for('main.index'))
return render_template('staff/add_flight.html', form=form)
def topcustomers():
if not current_user.is_authenticated or not current_user.get_type(
) == 'agent':
flash('You must be logged in as a booking agent for this action')
return redirect(url_for('main.index'))
date_six_months_ago = datetime.now() - timedelta(days=180)
date_year_ago = datetime.now() - timedelta(days=365)
top_num_tickets = Purchase.query.join(
Ticket, Purchase.ticket_id == Ticket.ticket_id).add_columns(
Purchase.email_customer, Purchase.email_booking,
Ticket.ticket_id).with_entities(
Purchase.email_customer,
func.count(Ticket.ticket_id).label('count_tickets')).group_by(
Purchase.email_customer).filter(
Purchase.email_booking ==
current_user.get_identifier()).filter(
Purchase.date > date_six_months_ago).order_by(
desc("count_tickets"))[:5]
# make list of customer names
# make list of number of tickets puchased
customers = []
num_tickets = []
for line in top_num_tickets:
cust = line.email_customer
tick = line.count_tickets
customers.append(cust)
num_tickets.append(tick)
max_tick = 0
if num_tickets != []:
max_tick = max(num_tickets)
top_commissions=Purchase.query.join(Ticket, Purchase.ticket_id==Ticket.ticket_id)\
.add_columns(Purchase.email_customer, Purchase.email_booking, Ticket.ticket_id, Ticket.price)\
.with_entities(Purchase.email_customer, (func.sum(Ticket.price)/10).label('sum_commissions'))\
.group_by(Purchase.email_customer).filter(Purchase.email_booking==current_user.get_identifier())\
.filter(Purchase.date>date_year_ago).order_by(desc("sum_commissions"))[:5]
com_customers = []
list_commissions = []
max_comm = 0
for line in top_commissions:
cust = line.email_customer
com = line.sum_commissions
com_customers.append(cust)
list_commissions.append(com)
if list_commissions != []:
max_comm = max(list_commissions)
# query=Purchase.query.filter_by(email_booking = current_user.get_identifier()).join(Ticket).all()
return render_template('agent/viewtopcustomers.html',top_num_tickets=top_num_tickets,\
customers=customers,\
num_tickets=num_tickets,\
top_commissions=top_commissions,\
com_customers=com_customers,\
list_commissions=list_commissions,\
max_tick=max_tick,\
max_comm=max_comm)
