def activate(spreadsheet_id):
"""Activates an unsaved spreadsheet"""
user = security.authorize(request)
disable_current_spreadsheets(user.customer.id)
spreadsheet = EmployeeSpreadsheet.query \
.options(joinedload('employees')) \
.options(joinedload('employees.arrangements')) \
.filter(EmployeeSpreadsheet.customer_id == user.customer.id) \
.filter(EmployeeSpreadsheet.id == spreadsheet_id) \
.first()
if spreadsheet is None:
raise InvalidUsage('Spreadsheet not found')
spreadsheet.active = True
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_employee_input(data)
found_any_duplication = process_employee_changes(spreadsheet, data)
db.session.commit()
tasks.refresh_employee_travel_history.schedule(user.customer.id)
return jsonify({
"foundAnyDuplication": found_any_duplication,
})
def download(spreadsheet_id):
"""Downloads the spreadsheet data"""
user = security.authorize(request)
employee_spreadsheet = EmployeeSpreadsheet.query.get(spreadsheet_id)
if not employee_spreadsheet:
raise InvalidUsage('Employee Spreadsheet not found')
elif employee_spreadsheet.customer_id != user.customer.id:
raise InvalidUsage('Access denied')
data = aws.s3download(employee_spreadsheet.upload_key)
return send_file(data, attachment_filename=employee_spreadsheet.file_name)
def download(traveller_data_id):
"""Downloads the spreadsheet data"""
user = security.authorize(request)
traveller_data = TravellerData.query.get(traveller_data_id)
if not traveller_data:
raise InvalidUsage('Traveller data not found')
elif traveller_data.customer_id != user.customer.id:
raise InvalidUsage('Access denied')
data = aws.s3download(traveller_data.upload_key)
return send_file(data, attachment_filename=traveller_data.filename)
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
def init_celery_app(flask_app, default_queue, modules_to_include=None):
"""
This initializes Celery app for given Flask app.
:param flask_app: Flask app
:param default_queue: Name of queue for particular flask app
:param modules_to_include: list of modules' names containing Celery tasks
:type flask_app: Flask
:type default_queue: str
:type modules_to_include: list | None
:return: Celery app
:rtype: Celery
"""
if not isinstance(flask_app, Flask):
raise InvalidUsage('flask_app must be instance of Flask')
if not isinstance(default_queue, basestring):
raise InvalidUsage('Queue name must be str')
if modules_to_include and not isinstance(modules_to_include, list):
raise InvalidUsage('Include modules containing Celery tasks in a list')
celery_app = Celery(
flask_app.import_name,
broker=flask_app.config[TalentConfigKeys.REDIS_URL_KEY],
backend=flask_app.config[TalentConfigKeys.CELERY_RESULT_BACKEND_URL],
include=modules_to_include)
flask_app.config['CELERY_QUEUES'] = (Queue(default_queue,
routing_key=default_queue +
'_key'), )
flask_app.config['CELERY_DEFAULT_QUEUE'] = default_queue
flask_app.config['CELERY_DEFAULT_ROUTING_KEY'] = default_queue + '_key'
flask_app.config['CELERY_TIMEZONE'] = 'UTC'
celery_app.conf.update(flask_app.config)
celery_app.conf.update(accept_content)
logger = flask_app.config[TalentConfigKeys.LOGGER]
logger.info("Celery has been configured for %s successfully" %
flask_app.import_name)
TaskBase = celery_app.Task
class ContextTask(TaskBase):
abstract = True
def __call__(self, *args, **kwargs):
with flask_app.app_context():
return TaskBase.__call__(self, *args, **kwargs)
celery_app.Task = ContextTask
return celery_app
def add_user():
"""Adds a new user"""
security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_user_input(data, adding=True)
hashed_password = security.strong_hash(data['password'])
user = User(id=uuid4(),
firstname=data['firstName'],
secondname=data['secondName'] if 'secondName' in data else '',
customer_id=data['customerId'],
email=data['workEmail'],
password=hashed_password,
admin=data['admin'],
active=data['active'],
unsucessful_login_attemps=0)
db.session.add(user)
db.session.commit()
return ('', 204)
def update_customer(subject_customer_id):
"""Updates a customer"""
security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_customer_input(data)
has_contract_end_date = 'contractEndDate' in data and data[
'contractEndDate']
customer = Customer.query \
.options(joinedload('applications')) \
.get(subject_customer_id)
customer.name = data['name']
customer.address = data['address'] if 'address' in data else ''
customer.contract_end_date = data[
'contractEndDate'] if has_contract_end_date else None
customer.applications = [Application.query.get(id) for id in data['applications']] \
if 'applications' in data else []
customer.active = data['active']
db.session.commit()
return ('', 204)
def add_period():
"""Save a new traveller data period"""
user = security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
period = data
check_periods_input([period])
has_traveller_data = 'travellerData' in period and period['travellerData']
traveller_data_id = period['travellerData'][
'id'] if has_traveller_data else None
db.session.add(
TravellerDataPeriod(pk=uuid4(),
customer_id=user.customer.id,
from_date=period['from'],
to_date=period['to'],
traveller_data_id=traveller_data_id))
db.session.commit()
tasks.refresh_employee_travel_history.schedule(user.customer.id)
return ('', 204)
def update_user(subject_user_id):
"""Updates a user"""
security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_user_input(data, adding=False)
has_contract_end_date = 'contractEndDate' in data and data[
'contractEndDate']
user = User.query.get(subject_user_id)
user.firstname = data['firstName']
user.secondname = data['secondName'] if 'secondName' in data else ''
user.company_name = data['companyName'] if 'companyName' in data else ''
user.company_address = data[
'companyAddress'] if 'companyAddress' in data else ''
user.contract_end_date = data[
'contractEndDate'] if has_contract_end_date else None
user.email = data['workEmail']
user.active = data['active']
user.admin = data['admin']
if 'password' in data and data['password']:
hashed_password = security.strong_hash(data['password'])
user.password = hashed_password
db.session.commit()
return ('', 204)
def save_periods():
"""Updates traveller data periods"""
user = security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
periods = data
check_periods_input(periods)
TravellerDataPeriod.query.filter_by(customer_id=user.customer.id).delete()
for period in periods:
has_traveller_data = 'travellerData' in period and period[
'travellerData']
traveller_data_id = period['travellerData'][
'id'] if has_traveller_data else None
db.session.add(
TravellerDataPeriod(pk=uuid4(),
customer_id=user.customer.id,
from_date=period['from'],
to_date=period['to'],
traveller_data_id=traveller_data_id))
db.session.commit()
tasks.refresh_employee_travel_history.schedule(user.customer.id)
return ('', 204)
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 add_customer():
"""Adds a new customer"""
security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_customer_input(data)
has_contract_end_date = 'contractEndDate' in data and data[
'contractEndDate']
contract_end_date = data[
'contractEndDate'] if has_contract_end_date else None
customer = Customer(id=uuid4(),
name=data['name'],
address=data['address'] if 'address' in data else '',
contract_end_date=contract_end_date,
applications=[Application.query.get(id) for id in data['applications']] \
if 'applications' in data else [],
active=data['active'])
db.session.add(customer)
db.session.commit()
return ('', 204)
def recover():
"""Processes password recovery attempts"""
attempt = request.get_json()
if attempt is None:
raise InvalidUsage('Invalid data')
errors = []
if not 'email' in attempt or not attempt['email']:
errors.append('E-mail is required')
if errors:
raise InvalidUsage(errors)
if not 'captchaToken' in attempt or not attempt['captchaToken']:
raise InvalidUsage('Please provide a captcha token')
elif not security.check_captcha(attempt['captchaToken']):
raise InvalidUsage('Invalid captcha token')
user = User.query.filter_by(email=attempt['email']).first()
if not user:
# for security concerns it is not advisable to
# tell the potential attacker if the e-mail was valid
return ('', 204)
new_password = security.generate_password()
hashed_password = security.strong_hash(new_password)
emails.send(to_address=attempt['email'],
subject='MinTax - Password Recovery',
body='''We received your password recovery request.
Please find below your new password:<br/>
<br/>
<p style="margin-left: 25px; font-style: italic;">{0}</p><br/>
<br/>
We advise you to change your password on your next sign in.
You can do that on the settings page
available on the rightmost top menu.<br/>'''.format(new_password))
user.password = hashed_password
user.unsucessful_login_attemps = 0
db.session.commit()
return ('', 204)
def check_input(data):
"""Check the validity of input data"""
errors = []
check_question1(data, errors)
check_question3(data, errors)
check_question4(data, errors)
check_question5(data, errors)
check_question6(data, errors)
if errors:
raise InvalidUsage(errors)
def handle_integrity_error(error):
"""Captures integrity errors and returns a
better http response"""
custom_message = None
if error.orig and error.orig.diag:
if error.orig.diag.constraint_name == 'users_email_key':
custom_message = 'E-mail address already exists in the database'
if custom_message:
return handle_invalid_usage(InvalidUsage(custom_message))
raise error
def change_password():
"""Allows the user to change their password"""
user = security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
errors = []
if not 'current' in data or not data['current']:
errors.append('Current password is required')
filled_new_password = '******' in data and data['new']
filled_password_confirmation = 'confirmation' in data and data[
'confirmation']
if not filled_new_password:
errors.append('New password is required')
elif len(data['new']) < 8 or security.unsafe_password(data['new']):
errors.append(
'Your new password should have a minimum length of 8 characters and '
+
'include at least 1 lowercase letter, 1 uppercase letter and 1 number'
)
if not filled_password_confirmation:
errors.append('Password confirmation is required')
if filled_new_password and filled_password_confirmation and data[
'new'] != data['confirmation']:
errors.append('Your new password and the confirmation must match')
if errors:
raise InvalidUsage(errors)
hashed_password = security.strong_hash(data['current'])
if not hashed_password == user.password:
raise InvalidUsage('Current password is invalid')
hashed_new_password = security.strong_hash(data['new'])
user.password = hashed_new_password
db.session.commit()
return ('', 204)
def check_input(data):
"""Check the validity of input data"""
errors = []
# question 1
if 'numberOfCompanies' not in data \
or data['numberOfCompanies'] is None \
or data['numberOfCompanies'] == '':
errors.append('Question 1: Number of companies is required')
else:
val.check_posi_integer(errors, data['numberOfCompanies'],
'Question 1: Number of companies')
# question 2
if 'branchesOverseas' not in data or data['branchesOverseas'] is None:
errors.append('Question 2: Please provide an answer')
elif data['branchesOverseas'] not in ['Y', 'N']:
errors.append('Question 2: Invalid boolean answer')
# question 3
check_question3_branch_count(errors, data)
check_question3_companies(errors, data)
check_question3_duplicated_paye(errors, data)
check_question3_spr_company_count(errors, data)
# question 4
simplified_annual_payroll = False
if 'simplifiedAnnualPayroll' not in data or data['simplifiedAnnualPayroll'] is None:
errors.append('Question 4: Please provide an answer')
elif data['simplifiedAnnualPayroll'] not in ['Y', 'N']:
errors.append('Question 4: Invalid boolean answer')
elif data['simplifiedAnnualPayroll'] == 'Y':
simplified_annual_payroll = True
# question 5/6
question_number = 6 if simplified_annual_payroll else 5
if 'employeesOnAssignmentUK' not in data or data['employeesOnAssignmentUK'] is None:
errors.append('Question {0}: Please provide an answer'.format(question_number))
elif data['employeesOnAssignmentUK'] not in ['Y', 'N']:
errors.append('Question {0}: Invalid boolean answer'.format(question_number))
# question 6/7
question_number = question_number + 1
if 'anyNonTaxableEmployees' not in data or data['anyNonTaxableEmployees'] is None:
errors.append('Question {0}: Please provide an answer'.format(question_number))
elif data['anyNonTaxableEmployees'] not in ['Y', 'N']:
errors.append('Question {0}: Invalid boolean answer'.format(question_number))
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 ignore_employees():
"""Ignores multiple employees"""
user = security.authorize(request)
data = request.get_json()
if data is None or not isinstance(data, list):
raise InvalidUsage('Invalid data')
for employee in data:
db.session.add(
IgnoredEmployee(pk=uuid4(),
customer_id=user.customer.id,
traveller_name=employee['travellerName'],
employee_id=employee['employeeId']))
db.session.commit()
tasks.refresh_employee_travel_history.schedule(user.customer.id)
return ('', 204)
def update():
"""Receives company data and stores in the database"""
user = security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_input(data)
assumptions = Assumptions.query.filter_by(
customer_id=user.customer.id).first()
if assumptions is None:
assumptions = Assumptions(
user.customer.id, data['outboundFromUKWithoutInbound'],
data['businessTravellersTreaty3159'],
data['businessTravellersTreaty60183'],
data['useDeminimusIncidentalWorkdays'] == 'Y',
data['deminimusIncidentalWorkdays'],
data['useDeminimusEEAA1Workdays'] == 'Y',
data['deminimusEEAA1Workdays'], data['inboundToUKWithoutOutbound'])
db.session.add(assumptions)
else:
assumptions.outbound_uk_without_inbound = data[
'outboundFromUKWithoutInbound']
assumptions.business_trav_treaty_3159 = data[
'businessTravellersTreaty3159']
assumptions.business_trav_treaty_60183 = data[
'businessTravellersTreaty60183']
assumptions.use_demin_incidental_workdays = data[
'useDeminimusIncidentalWorkdays'] == 'Y'
assumptions.deminimus_incidental_workdays = data[
'deminimusIncidentalWorkdays']
assumptions.use_demin_eeaa1_workdays = data[
'useDeminimusEEAA1Workdays'] == 'Y'
assumptions.deminimus_eeaa1_workdays = data['deminimusEEAA1Workdays']
assumptions.inbound_uk_without_outbound = data[
'inboundToUKWithoutOutbound']
db.session.commit()
tasks.refresh_employee_travel_history.schedule(user.customer.id)
return ('', 204)
def update():
"""Receives company data and stores in the database"""
user = security.authorize(request)
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_input(data)
CompaniesInfo.query.filter_by(customer_id=user.customer.id).delete()
# the below instruction cascades automatically to branches
Company.query.filter_by(customer_id=user.customer.id).delete()
companies_info = CompaniesInfo(user.customer.id,
data['branchesOverseas'] == 'Y',
data['simplifiedAnnualPayroll'] == 'Y',
data['employeesOnAssignmentUK'] == 'Y',
data['anyNonTaxableEmployees'] == 'Y')
db.session.add(companies_info)
if 'companies' in data and isinstance(data['companies'], list):
for company_data in data['companies']:
company_id = uuid4()
company = Company(company_id,
user.customer.id,
company_data['name'],
company_data['paye'],
company_data['trackingMethod'],
company_data['otherTrackingMethod'],
company_data['simplifiedPayroll'],
company_data['simplifiedPayrollPaye'])
if data['branchesOverseas'] == 'Y':
for branch_data in company_data['branches']:
branch = Branch(uuid4(),
company_id,
branch_data['name'],
branch_data['country'])
company.branches.append(branch)
db.session.add(company)
db.session.commit()
return ('', 204)
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_employee_input(data):
"""Checks if the employee list input is valid"""
errors = []
employees = []
if 'additions' in data and isinstance(data['additions'], list):
employees += data['additions']
if 'changes' in data and isinstance(data['changes'], list):
employees += data['changes']
for idx, employee in enumerate(employees):
error_prefix = 'Employee #{0}'.format(idx + 1)
empty_name = 'name' not in employee or not employee['name']
empty_emp_id = 'employeeId' not in employee or not employee[
'employeeId']
if empty_name and empty_emp_id:
errors.append('{0}: Name or Id is required'.format(error_prefix))
check_employee_arrangements(errors, error_prefix, employee)
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 upload():
"""Receives a upload for processing"""
user = security.authorize(request)
if 'file' not in request.files:
raise InvalidUsage('Missing file data')
uploaded_file = request.files['file']
data = uploaded_file.read()
with BytesIO(data) as stream:
upload_key = aws.s3upload(stream)
try:
book = xlrd.open_workbook(file_contents=data)
employee_spreadsheet_id = uuid4()
# row key is a tuple like (name, employee_id)
employees = defaultdict(set)
for sheet in book.sheets():
if sheet.name not in KNOWN_EMPLOYEE_CATEGORIES:
continue
for row in range(1, sheet.nrows):
employee_name = spreadsheets.parse_text(
sheet.cell_value(rowx=row, colx=0))
employee_id = spreadsheets.parse_text(
sheet.cell_value(rowx=row, colx=1))
if employee_name or employee_id:
category = KNOWN_EMPLOYEE_CATEGORIES[sheet.name]
employees[(employee_name, employee_id)].add(category)
except xlrd.XLRDError:
raise InvalidUsage(
'Unsupported spreadsheet. Please check the uploaded file.')
emp_ssheet = dict(id=employee_spreadsheet_id,
customer_id=user.customer.id,
upload_date=datetime.now(),
active=False,
file_name=uploaded_file.filename,
upload_key=upload_key)
employee_rows = []
employee_arrangement_rows = []
for (emp_name, emp_id), categories in employees.items():
employee_row_id = uuid4()
employee_rows.append(
dict(id=employee_row_id,
employee_spreadsheet_id=employee_spreadsheet_id,
name=emp_name,
employee_id=emp_id))
for category in categories:
employee_arrangement_rows.append(
dict(id=uuid4(),
employee_id=employee_row_id,
category=category))
db.session.execute(EmployeeSpreadsheet.__table__.insert(), emp_ssheet)
if employee_rows:
db.session.execute(Employee.__table__.insert(), employee_rows)
if employee_arrangement_rows:
db.session.execute(EmployeeArrangement.__table__.insert(),
employee_arrangement_rows)
db.session.commit()
return ('', 204)
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,
})
def login():
"""Receives a login attempt, validates
it and generates an auth token"""
attempt = request.get_json()
if attempt is None:
raise InvalidUsage('Invalid data')
errors = []
if not 'email' in attempt or not attempt['email']:
errors.append('E-mail is required')
if not 'password' in attempt or not attempt['password']:
errors.append('Password is required')
if errors:
raise InvalidUsage(errors)
if not 'captchaToken' in attempt or not attempt['captchaToken']:
raise InvalidUsage('Please provide a captcha token')
elif not security.check_captcha(attempt['captchaToken']):
raise InvalidUsage('Invalid captcha token')
user = User.query \
.options(joinedload('customer.applications')) \
.filter_by(email=attempt['email']) \
.first()
if not user:
raise InvalidUsage('Invalid e-mail or password')
if user.unsucessful_login_attemps >= 3:
raise InvalidUsage(ACCOUNT_BLOCKED_MESSAGE)
hashed_password = security.strong_hash(attempt['password'])
if not hashed_password == user.password:
user.unsucessful_login_attemps += 1
db.session.commit()
if user.unsucessful_login_attemps >= 3:
raise InvalidUsage(ACCOUNT_BLOCKED_MESSAGE)
else:
raise InvalidUsage('Invalid e-mail or password')
if not user.active:
raise InvalidUsage('Disabled user account')
if not user.customer.active:
raise InvalidUsage('Disabled customer account')
if not MINTAX_BUSINESS_TRAVELLERS_ID in [
app.id for app in user.customer.applications
]:
raise InvalidUsage('Customer does not have access to this application')
auth_token = AuthToken(uuid4(), user)
user.unsucessful_login_attemps = 0
db.session.add(auth_token)
db.session.commit()
return jsonify({'authToken': auth_token.id})
def update():
"""Receives clarification answers and stores in the database"""
user = security.authorize(request)
customer = user.customer
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_input(data)
home_country_clarifications = HomeCountryClarification.query \
.filter_by(customer_id=customer.id) \
.all()
home_country_clars_by_employee_key = {
employee_key: sorted(clars, key=lambda clar: clar.to_date \
if clar.to_date else datetime(9999, 12, 31))
for employee_key, clars in groupby(home_country_clarifications,
lambda x: x.effective_employee_id)
}
if 'ignoredEmployees' in data and isinstance(data['ignoredEmployees'],
list):
for ignored_employee in data['ignoredEmployees']:
if 'undoIgnore' in ignored_employee and ignored_employee[
'undoIgnore']:
ignored_employee_record = IgnoredEmployee.query.get(
ignored_employee['id'])
db.session.delete(ignored_employee_record)
if 'ignoredTrips' in data and isinstance(data['ignoredTrips'], list):
for ignored_trip in data['ignoredTrips']:
if 'undoIgnore' in ignored_trip and ignored_trip['undoIgnore']:
ignored_trip_record = IgnoredTrip.query.get(ignored_trip['id'])
db.session.delete(ignored_trip_record)
if 'unclearHomeCountry' in data and isinstance(data['unclearHomeCountry'],
list):
for employee in data['unclearHomeCountry']:
employee_key = employee['employeeId'] if employee['employeeId'] \
else employee['travellerName']
if employee_key in home_country_clars_by_employee_key:
for existing_clarification in home_country_clars_by_employee_key[
employee_key]:
db.session.delete(existing_clarification)
for clar in employee['clarifications']:
empty_clarification = not clar['homeCountry'] and \
not clar['fromDate'] and \
not clar['toDate']
if not empty_clarification:
db.session.add(
HomeCountryClarification(
pk=uuid4(),
customer_id=customer.id,
effective_employee_id=employee_key,
home_country=clar['homeCountry'],
from_date=clar['fromDate'],
to_date=clar['toDate']))
if 'ignore' in employee and employee['ignore']:
db.session.add(
IgnoredEmployee(pk=uuid4(),
customer_id=customer.id,
traveller_name=employee['travellerName'],
employee_id=employee['employeeId']))
traveller_data_periods = TravellerDataPeriod.query \
.options(joinedload('traveller_data.travels')) \
.filter_by(customer_id=customer.id) \
.all()
incomplete_trips = [trip \
for period in traveller_data_periods \
for trip in period.traveller_data.travels
if trip.invalid]
incomplete_trips_by_id = {str(trip.id): trip for trip in incomplete_trips}
if 'incompleteTrips' in data and isinstance(data['incompleteTrips'], list):
for trip in data['incompleteTrips']:
if trip['id'] in incomplete_trips_by_id:
existing = incomplete_trips_by_id[trip['id']]
existing.origin_country_name = trip['originCountry'] \
if 'originCountry' in trip else None
existing.destination_country_name = trip['destinationCountry'] \
if 'destinationCountry' in trip else None
existing.departure_date = trip['departureDate']
existing.departure_time = trip['departureTime']
existing.arrival_date = trip['arrivalDate']
existing.arrival_time = trip['arrivalTime']
if trip['originCountry'] == 'GBR':
border_cross = (datetime.combine(trip['departureDate'],
trip['departureTime'])
if trip['departureDate']
and trip['departureTime'] else None)
else:
border_cross = (datetime.combine(trip['arrivalDate'],
trip['arrivalTime'])
if trip['arrivalDate']
and trip['arrivalTime'] else None)
existing.border_cross = border_cross
existing.invalid = not existing.departure_date \
or not existing.departure_time \
or not existing.arrival_date \
or not existing.arrival_time \
or not existing.origin_country_name \
or not existing.destination_country_name
inb_assump_confirmations = InboundAssumptionConfirmation.query \
.filter_by(customer_id=customer.id) \
.all()
inb_assump_by_trip_key = {(inb_assump.effective_employee_id,
inb_assump.to_date): inb_assump
for inb_assump in inb_assump_confirmations}
if 'inboundAssumptions' in data and isinstance(data['inboundAssumptions'],
list):
for trip in data['inboundAssumptions']:
if trip['inboundAssumptionConfirmed']:
confirmed = trip['inboundAssumptionConfirmed']
employee_key = trip['employeeId'] if trip['employeeId'] \
else trip['travellerName']
trip_key = (employee_key, trip['toDate'])
if trip_key in inb_assump_by_trip_key:
inb_assump = inb_assump_by_trip_key[trip_key]
inb_assump.confirmed = confirmed
inb_assump.correct_date = trip[
'correctFromDate'] if confirmed else None
else:
inb_assump = InboundAssumptionConfirmation(
uuid4(), customer.id, employee_key, trip['toDate'],
confirmed, trip['correctFromDate'])
db.session.add(inb_assump)
if trip['ignore'] and trip['outboundTrip']:
db.session.add(
_create_ignored_trip(trip['outboundTrip'], customer.id))
out_assump_confirmations = OutboundAssumptionConfirmation.query \
.filter_by(customer_id=customer.id) \
.all()
out_assump_by_trip_key = {(out_assump.effective_employee_id,
out_assump.from_date): out_assump
for out_assump in out_assump_confirmations}
if 'outboundAssumptions' in data and isinstance(
data['outboundAssumptions'], list):
for trip in data['outboundAssumptions']:
if trip['outboundAssumptionConfirmed']:
confirmed = trip['outboundAssumptionConfirmed']
employee_key = trip['employeeId'] if trip['employeeId'] \
else trip['travellerName']
trip_key = (employee_key, trip['fromDate'])
if trip_key in out_assump_by_trip_key:
out_assump = out_assump_by_trip_key[trip_key]
out_assump.confirmed = confirmed
out_assump.correct_date = trip[
'correctToDate'] if confirmed else None
else:
out_assump = OutboundAssumptionConfirmation(
uuid4(), customer.id, employee_key, trip['fromDate'],
confirmed, trip['correctToDate'])
db.session.add(out_assump)
if trip['ignore'] and trip['inboundTrip']:
db.session.add(
_create_ignored_trip(trip['inboundTrip'], customer.id))
border_cross_clarifs = BorderCrossTimeClarification.query \
.filter_by(customer_id=customer.id) \
.all()
border_cross_clarifs_by_key = {
(clarif.employee_name, clarif.employee_id, clarif.border_cross,
clarif.origin_country, clarif.destination_country): clarif
for clarif in border_cross_clarifs
}
if 'unclearBorderCrossTime' in data and isinstance(
data['unclearBorderCrossTime'], list):
for trip in data['unclearBorderCrossTime']:
if 'correctTime' not in trip or not trip['correctTime']:
continue
trip_key = (trip['travellerName'], trip['employeeId'],
trip['borderCross'], trip['originCountry'],
trip['destinationCountry'])
if trip_key in border_cross_clarifs_by_key:
clarif = border_cross_clarifs_by_key[trip_key]
clarif.correct_time = trip['correctTime']
else:
clarif = BorderCrossTimeClarification(
uuid4(), customer.id, trip['travellerName'],
trip['employeeId'], trip['borderCross'],
trip['originCountry'], trip['destinationCountry'],
trip['correctTime'])
db.session.add(clarif)
if trip['ignore']:
db.session.add(_create_ignored_trip(trip, customer.id))
same_person_confirmations = SamePersonConfirmation.query \
.filter_by(customer_id=customer.id) \
.all()
same_person_confirmations_by_id = {
x.effective_employee_id: x \
for x in same_person_confirmations
}
if 'duplicateIDs' in data and isinstance(data['duplicateIDs'], list):
answers = {(x['effectiveEmployeeId'], x['confirmed'])
for x in data['duplicateIDs']}
for effective_id, confirmed in answers:
record_exists = effective_id in same_person_confirmations_by_id
if confirmed and not record_exists:
same_person = SamePersonConfirmation(uuid4(), customer.id,
effective_id)
db.session.add(same_person)
if not confirmed and record_exists:
db.session.delete(
same_person_confirmations_by_id[effective_id])
for duplicate in data['duplicateIDs']:
if 'ignore' in duplicate and duplicate['ignore']:
db.session.add(
IgnoredEmployee(
pk=uuid4(),
customer_id=customer.id,
traveller_name=duplicate['travellerName'],
employee_id=duplicate['effectiveEmployeeId']))
db.session.commit()
tasks.refresh_employee_travel_history.schedule(user.customer.id)
return ('', 204)
def fetch():
"""Fetches most recent report results"""
user = security.authorize(request)
customer = user.customer
last_request = customer.last_travel_history_request
last_error = customer.last_travel_history_error
available_data = customer.last_available_travel_history
companies_info = CompaniesInfo.query.filter_by(
customer_id=user.customer.id).first()
progress_event_channel = 'travel-history-processing-progress-{0}-{1}' \
.format(customer.id, last_request.isoformat()) \
if last_request and (not available_data \
or last_request > available_data) \
else None
data = request.get_json()
if data is None:
raise InvalidUsage('Invalid data')
check_input(data)
emp_travel_history_entities = EmployeeTravelHistory.query \
.filter_by(customer_id=customer.id,
version_ts=available_data) \
.all()
emp_travel_history = [{
'traveller_name': entry.traveller_name,
'employee_id': entry.employee_id,
'category': entry.category,
'from_date': entry.from_date,
'to_date': entry.to_date,
'originally_unclear': entry.originally_unclear,
'originally_assumed_inbound': entry.originally_assumed_inbound,
'outbound_trip_id': entry.outbound_trip_id
} for entry in emp_travel_history_entities]
report_periods = ReportPeriod.query \
.filter_by(user_id=user.id) \
.all()
results_per_category = {}
categories = data['categories']
for category in categories:
report_period = next((x for x in report_periods \
if x.treaty_position == int(category)), None)
if 'fromDate' in data and data['fromDate'] and \
'toDate' in data and data['toDate']:
from_date = data['fromDate'].date()
to_date = data['toDate'].date()
if report_period:
report_period.from_date = from_date
report_period.to_date = to_date
else:
db.session.add(
ReportPeriod(uuid4(), user.id, category, from_date,
to_date))
elif report_period:
from_date = report_period.from_date
to_date = report_period.to_date
else:
latest_period = TravellerDataPeriod.query \
.filter_by(customer_id=customer.id) \
.order_by(TravellerDataPeriod.from_date.desc()) \
.first()
if latest_period:
from_date = latest_period.from_date
to_date = latest_period.to_date
else:
from_date = None
to_date = None
employees = processing.report_results.process(emp_travel_history,
from_date, to_date,
[category]) \
if from_date and to_date else []
results_per_category[category] = {
"stays": [{
"travellerName": employee['traveller_name'],
"employeeId": employee['employee_id'],
"days": stay['days'],
} for employee in employees for stay in employee['stays']],
"fromDate":
from_date.isoformat() if from_date else None,
"toDate":
to_date.isoformat() if to_date else None,
}
db.session.commit()
return jsonify({
"resultsPerCategory":
results_per_category,
"lastRequest":
last_request.isoformat() if last_request else None,
"availableData":
available_data.isoformat() if available_data else None,
"lastError":
last_error.isoformat() if last_error else None,
"progressEventChannel":
progress_event_channel,
"simplifiedPayroll":
companies_info.simplified_annual_payroll if companies_info else False,
})
