def check_periods_input(periods):
"""Checks the validity of periods input"""
errors = []
for period in periods:
period_id = period['travellerData']['filename'] \
if 'travellerData' in period and period['travellerData'] else 'New Period'
error_prefix = '{0}:'.format(period_id)
from_date = None
to_date = None
if 'from' not in period or not period['from']:
errors.append('{0} From date is required'.format(error_prefix))
else:
from_date = val.check_date(errors, period['from'],
'{0} From date'.format(error_prefix))
if 'to' not in period or not period['to']:
errors.append('{0} To date is required'.format(error_prefix))
else:
to_date = val.check_date(errors, period['to'],
'{0} To date'.format(error_prefix))
if from_date and to_date:
if to_date < from_date:
errors.append(
'{0} To date must be after From date'.format(error_prefix))
if 'travellerData' not in period or not period['travellerData']:
errors.append(
'{0} please upload the traveller data'.format(error_prefix))
if errors:
raise InvalidUsage(errors)
def check_user_input(data, adding):
"""Check the validity of input data"""
errors = []
if adding:
if 'customerId' not in data or not data['customerId']:
errors.append('Customer is required')
if 'firstName' not in data or not data['firstName']:
errors.append('First name is required')
elif len(data['firstName']) > 50:
errors.append(
'First name should have less than or exactly 50 characters')
if 'secondName' in data and len(data['secondName']) > 50:
errors.append(
'Second name should have less than or exactly 50 characters')
if 'companyName' in data and len(data['companyName']) > 50:
errors.append(
'Company name should have less than or exactly 50 characters')
if 'companyAddress' in data and len(data['companyAddress']) > 4000:
errors.append(
'Company address should have less than or exactly 4000 characters')
if 'contractEndDate' in data and data['contractEndDate']:
val.check_date(errors, data['contractEndDate'], 'Contract end date')
if 'workEmail' not in data or not data['workEmail']:
errors.append('Work e-mail is required')
elif len(data['workEmail']) > 500:
errors.append(
'Work e-mail should have less than or exactly 50 characters')
else:
val.check_email(errors, data['workEmail'], 'Work e-mail')
check_password(errors, data, adding)
if 'active' not in data:
errors.append('Active: missing attribute')
if 'admin' not in data:
errors.append('Admin: missing attribute')
if errors:
raise InvalidUsage(errors)
def edit_payment(self, _event=None):
self.response['foreground'] = 'red'
payment_type = self.payment_type.get()
method = self.method.get().title()
if not method:
self.response['text'] = "Payment method is empty."
return
amount = self.amount.get()
if not amount:
self.response['text'] = "Amount paid is empty."
return
if not validation.is_float(amount):
self.response['text'] = "Amount paid must be a monetary amount."
return
amount = f"{float(amount):.2f}"
date = self.date.get()
if not date:
self.response['text'] = "Date is empty."
return
if not validation.check_date(date):
self.response[
'text'] = "Date input is invalid.\nMust be of form YYYY-MM-DD"
return
date = datetime.datetime.strptime(date,
"%Y-%m-%d").strftime("%Y-%m-%d")
time = self.time.get()
if not time:
self.response['text'] = "Time is empty."
return
if not validation.check_time(time):
self.response[
'text'] = "Time input is invalid.\nMust be of form HH:MM"
return
time = datetime.datetime.strptime(time, "%H:%M").strftime("%H:%M")
description = self.description.get('1.0', tk.END)
payee_id = self.payee[0].get().split(' ', 1)[0]
record = (method, amount, date, time, description)
SQL.payments.edit_payment(self.payment_id, payment_type, record,
payee_id)
self.response['text'] = ""
self.master.show_details_frame()
messagebox.showinfo(
title="Successfully updated payment!",
message="Payment record has been successfully updated!")
def check_input(data):
"""Checks if the queries are valid"""
errors = []
if not 'categories' in data or len(data['categories']) > 1:
return
if 'fromDate' in data:
if not data['fromDate']:
errors.append('From date is required')
else:
data['fromDate'] = val.check_date(errors, data['fromDate'], 'From')
if 'toDate' in data:
if not data['toDate']:
errors.append('To date is required')
else:
data['toDate'] = val.check_date(errors, data['toDate'], 'To')
if errors:
raise InvalidUsage(errors)
def check_customer_input(data):
"""Check the validity of input data"""
errors = []
if 'name' not in data or not data['name']:
errors.append('Name is required')
elif len(data['name']) > 50:
errors.append('Name should have less than or exactly 50 characters')
if 'address' in data and len(data['address']) > 4000:
errors.append(
'Company address should have less than or exactly 4000 characters')
if 'contractEndDate' in data and data['contractEndDate']:
val.check_date(errors, data['contractEndDate'], 'Contract end date')
if 'active' not in data:
errors.append('Active: missing attribute')
if errors:
raise InvalidUsage(errors)
def check_employee_arrangements(errors, employee_error_prefix, employee):
"""Checks all the employee arrangements"""
if 'arrangements' in employee and isinstance(employee['arrangements'],
list):
last_eff_date = None
for idx, arrangement in enumerate(employee['arrangements']):
error_prefix = "{0} - Arrangement #{1}".format(
employee_error_prefix, idx + 1)
empty_category = 'category' not in arrangement or not arrangement[
'category']
if empty_category:
errors.append('{0}: Category is required'.format(error_prefix))
eff_from = None
empty_eff_from = 'effectiveFrom' not in arrangement or not arrangement[
'effectiveFrom']
if not empty_eff_from:
eff_from = val.check_date(
errors, arrangement['effectiveFrom'],
'{0}: Effective From'.format(error_prefix))
eff_to = None
empty_eff_to = 'effectiveTo' not in arrangement or not arrangement[
'effectiveTo']
if not empty_eff_to:
eff_to = val.check_date(
errors, arrangement['effectiveTo'],
'{0}: Effective To'.format(error_prefix))
if eff_from and eff_to and eff_to < eff_from:
errors.append(
'{0}: Effective To must be after Effective From'.format(
error_prefix))
if last_eff_date:
if eff_from and last_eff_date > eff_from:
errors.append(
'{0}: Effective From must be after previous arrangements'
.format(error_prefix))
elif eff_to and last_eff_date > eff_to:
errors.append(
'{0}: Effective To must be after previous arrangements'
.format(error_prefix))
last_eff_date = eff_to if eff_to else eff_from if eff_from else last_eff_date
def check_home_country_clarifications(errors, employee_error_prefix, employee):
"""Checks all the employee home country clarifications"""
if 'clarifications' in employee and isinstance(employee['clarifications'],
list):
last_eff_date = None
for idx, clar in enumerate(employee['clarifications']):
empty_clarification = not clar['homeCountry'] and \
not clar['fromDate'] and not clar['toDate']
if empty_clarification:
continue
error_prefix = "{0} - Clarification #{1}".format(
employee_error_prefix, idx + 1)
empty_country = 'homeCountry' not in clar or not clar['homeCountry']
if empty_country:
errors.append(
'{0}: Home country is required'.format(error_prefix))
eff_from = None
empty_eff_from = 'fromDate' not in clar or not clar['fromDate']
if not empty_eff_from:
eff_from = val.check_date(
errors, clar['fromDate'],
'{0}: From Date'.format(error_prefix))
eff_to = None
empty_eff_to = 'toDate' not in clar or not clar['toDate']
if not empty_eff_to:
eff_to = val.check_date(errors, clar['toDate'],
'{0}: To Date'.format(error_prefix))
if eff_from and eff_to and eff_to < eff_from:
errors.append('{0}: To Date must be after From Date'.format(
error_prefix))
if last_eff_date:
if eff_from and last_eff_date >= eff_from:
errors.append(
'{0}: From Date must be after previous clarifications'.
format(error_prefix))
elif eff_to and last_eff_date >= eff_to:
errors.append(
'{0}: To Date must be after previous clarifications'.
format(error_prefix))
last_eff_date = eff_to if eff_to else eff_from if eff_from else last_eff_date
def register_payment(self, _event=None):
self.response['foreground'] = 'red'
payment_type = self.payment_type
if payment_type == "Select Payment Type":
self.response['text'] = "Payment Type must be selected."
return
payee = self.payee[0].get().split(' ', 1)
if payee[0] == "Select":
self.response['text'] = "Payee must be selected."
return
method = self.method.get().title()
if not method:
self.response['text'] = "Payment method is empty."
return
amount = self.amount.get()
if not amount:
self.response['text'] = "Amount paid is empty."
return
if not validation.is_float(amount):
self.response['text'] = "Amount paid must be a monetary amount."
return
amount = f"{float(amount):.2f}"
date = self.date.get()
if not date:
self.response['text'] = "Date is empty."
return
if not validation.check_date(date):
self.response[
'text'] = "Date input is invalid.\nMust be of form YYYY-MM-DD"
return
date = datetime.datetime.strptime(date,
"%Y-%m-%d").strftime("%Y-%m-%d")
time = self.time.get()
if not time:
self.response['text'] = "Time is empty."
return
if not validation.check_time(time):
self.response[
'text'] = "Time input is invalid.\nMust be of form HH:MM"
return
time = datetime.datetime.strptime(time, "%H:%M").strftime("%H:%M")
description = self.description.get('1.0', tk.END)
record = (payment_type, method, amount, date, time, description)
SQL.payments.add_payment(record, payee[0])
# Clear entry fields after account creation
self.clear_fields()
self.response['text'] = ""
self.master.show_details_frame()
messagebox.showinfo(
title="Successfully added payment!",
message="Payment has been successfully added to the database.")
def check_input(data):
"""Check the validity of input data"""
errors = []
if 'unclearHomeCountry' in data and isinstance(data['unclearHomeCountry'],
list):
for idx, employee in enumerate(data['unclearHomeCountry']):
traveller_name = employee['travellerName'] if employee[
'travellerName'] else 'Unnamed'
employee_id = employee['employeeId'] if employee[
'employeeId'] else 'No ID'
error_prefix = '{0} ({1})'.format(traveller_name, employee_id)
check_home_country_clarifications(errors, error_prefix, employee)
if 'incompleteTrips' in data and isinstance(data['incompleteTrips'], list):
for idx, trip in enumerate(data['incompleteTrips']):
error_prefix = 'Line #{0}: '.format(idx + 1)
if trip['departureDate']:
trip['departureDate'] = val.check_date(
errors, trip['departureDate'],
'{0}Departure date'.format(error_prefix))
if trip['departureTime']:
trip['departureTime'] = val.check_time(
errors, trip['departureTime'],
'{0}Departure time'.format(error_prefix))
if trip['arrivalDate']:
trip['arrivalDate'] = val.check_date(
errors, trip['arrivalDate'],
'{0}Arrival date'.format(error_prefix))
if trip['arrivalTime']:
trip['arrivalTime'] = val.check_time(
errors, trip['arrivalTime'],
'{0}Arrival time'.format(error_prefix))
if 'inboundAssumptions' in data and isinstance(data['inboundAssumptions'],
list):
for idx, trip in enumerate(data['inboundAssumptions']):
error_prefix = 'Line #{0}: '.format(idx + 1)
trip['toDate'] = val.check_date(errors, trip['toDate'],
'{0}To date'.format(error_prefix))
if trip['inboundAssumptionConfirmed'] and trip[
'inboundAssumptionConfirmed'] == 'N':
if 'correctFromDate' not in trip or not trip['correctFromDate']:
errors.append('{0}Correct date is required for a "No" answer' \
.format(error_prefix))
else:
trip['correctFromDate'] = val.check_date(errors, trip['correctFromDate'],
'{0}Correct date' \
.format(error_prefix))
if trip['correctFromDate'] and trip['toDate'] \
and trip['correctFromDate'] > trip['toDate']:
errors.append('{0}Correct date must be equal or less than To date' \
.format(error_prefix))
if 'outboundAssumptions' in data and isinstance(
data['outboundAssumptions'], list):
for idx, trip in enumerate(data['outboundAssumptions']):
error_prefix = 'Line #{0}: '.format(idx + 1)
trip['fromDate'] = val.check_date(
errors, trip['fromDate'], '{0}From date'.format(error_prefix))
if trip['outboundAssumptionConfirmed'] and trip[
'outboundAssumptionConfirmed'] == 'N':
if 'correctToDate' not in trip or not trip['correctToDate']:
errors.append('{0}Correct date is required for a "No" answer' \
.format(error_prefix))
else:
trip['correctToDate'] = val.check_date(errors, trip['correctToDate'],
'{0}Correct date' \
.format(error_prefix))
if trip['correctToDate'] and trip['fromDate'] \
and trip['correctToDate'] < trip['fromDate']:
errors.append('{0}Correct date must be equal or after From date' \
.format(error_prefix))
if 'unclearBorderCrossTime' in data and isinstance(
data['unclearBorderCrossTime'], list):
for trip in data['unclearBorderCrossTime']:
employee_name = trip['travellerName'] if trip[
'travellerName'] else 'Unnamed'
employee_id = trip['employeeId'] if trip['employeeId'] else 'N/A'
border_cross = val.check_date(errors, trip['borderCross'],
'Border Cross Date')
border_cross_fmtd = border_cross.strftime(
"%d/%m/%y %H:%M") if border_cross else '?'
error_prefix = '{0} ({1}) - {2} - {3}/{4}: '.format(
employee_name, employee_id, border_cross_fmtd,
trip['originCountry'], trip['destinationCountry'])
if 'correctTime' in trip:
trip['correctTime'] = val.check_time(
errors, trip['correctTime'],
'{0}Correct Time'.format(error_prefix))
if errors:
raise InvalidUsage(errors)
def upload():
"""Receives a upload for processing"""
user = security.authorize(request)
if 'file' not in request.files:
raise InvalidUsage('Missing file data')
if 'id' not in request.form:
raise InvalidUsage('Missing request id')
upload_id = UUID(request.form['id'])
sse_channel = 'upload-progress-{0}'.format(upload_id)
from_date = None
if 'from' in request.form:
from_date = val.check_date([], request.form['from'], '')
from_date = from_date.date() if from_date else None
to_date = None
if 'to' in request.form:
to_date = val.check_date([], request.form['to'], '')
to_date = to_date.date() if to_date else None
uploaded_file = request.files['file']
data = uploaded_file.read()
sse.publish({'status': 'Storing a copy on our servers...'},
channel=sse_channel)
with BytesIO(data) as stream:
upload_key = aws.s3upload(stream)
sse.publish({
'status': 'Processing traveller data...',
'progress': 0
},
channel=sse_channel)
countries = Country.query.all()
country_aliases = CountryAlias.query.all()
try:
book = xlrd.open_workbook(file_contents=data)
def generate_rows():
"""Process the spreadsheet generating rows"""
if 'Traveller Data' not in book.sheet_names():
raise InvalidUsage(
'No "Traveller Data" worksheet found in this workbook')
sheet = book.sheet_by_name('Traveller Data')
if sheet.ncols < 20:
raise InvalidUsage('Invalid number of columns ' +
'in the spreadsheet (expected: 20).')
for row in range(1, sheet.nrows):
travel = create_travel_record(sheet, row, countries,
country_aliases)
if travel:
yield travel
rows = generate_rows()
total_rows = sum([sheet.nrows - 1 for sheet in book.sheets()])
traveller_data_id = uuid4()
upload_date = datetime.now()
traveller_data = dict(id=traveller_data_id,
customer_id=user.customer.id,
upload_date=upload_date,
upload_key=upload_key,
filename=uploaded_file.filename,
valid=False)
db.session.execute(TravellerData.__table__.insert(), traveller_data)
# count trips before or after the period if applicable
trips_before_period = 0
trips_after_period = 0
travel_count = 0
row_count = 0
invalid_trips_count = 0
error_count = 0
travel_rows = []
error_rows = []
for row in rows:
row_count += 1
if row['errors'] and row['ticket_type'] != 'Refund':
invalid_trips_count += 1
error_count += len(row['errors'])
row['id'] = uuid4()
row['traveller_data_id'] = traveller_data_id
travel_rows.append(row)
for error in row['errors']:
error_rows.append({
'travel_id': row['id'],
'error_code': error
})
if row['ticket_type'] != 'Refund':
travel_count += 1
if from_date:
if row['departure_date'] and row[
'departure_date'] < from_date:
trips_before_period += 1
elif row['arrival_date'] and row[
'arrival_date'] < from_date:
trips_before_period += 1
if to_date:
if row['departure_date'] and row[
'departure_date'] > to_date:
trips_after_period += 1
elif row['arrival_date'] and row['arrival_date'] > to_date:
trips_after_period += 1
if len(travel_rows) >= 1000:
progress_status = 'Processing traveller data ' + \
'({0} rows processed)...'.format(row_count)
sse.publish(
{
'status': progress_status,
'progress': row_count / total_rows * 100
},
channel=sse_channel)
db.session.execute(Travel.__table__.insert(), travel_rows)
travel_rows.clear()
if error_rows:
db.session.execute(TravellerDataError.__table__.insert(),
error_rows)
error_rows.clear()
if travel_rows:
db.session.execute(Travel.__table__.insert(), travel_rows)
if error_rows:
db.session.execute(TravellerDataError.__table__.insert(),
error_rows)
if error_count > 0:
query = TravellerData.__table__.update().where(
TravellerData.id == traveller_data_id)
db.session.execute(query, {'valid': False})
sse.publish({'status': 'Done!', 'progress': 100}, channel=sse_channel)
db.session.commit()
except xlrd.XLRDError:
raise InvalidUsage(
'Unsupported spreadsheet. Please check the uploaded file.')
return jsonify({
"entryCount":
travel_count,
"id":
traveller_data_id,
"filename":
uploaded_file.filename,
"dateUploaded":
upload_date.isoformat(),
"errorCount":
error_count,
"tripsBeforePeriod":
trips_before_period,
"tripsAfterPeriod":
trips_after_period,
"invalidCount":
invalid_trips_count,
"outsidePeriodCount":
trips_before_period + trips_after_period,
})
