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 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 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 fetch_customer(requested_customer_id):
"""Fetches a specific customer data"""
security.authorize(request)
customer = Customer.query \
.options(joinedload('applications')) \
.get(requested_customer_id)
has_contract_end_date = customer.contract_end_date
contract_end_date = customer.contract_end_date.isoformat(
) if has_contract_end_date else None
return jsonify({
'name': customer.name,
'address': customer.address or '',
'contractEndDate': contract_end_date,
'applications': [app.id for app in customer.applications],
'active': customer.active,
})
def fetch_employees():
"""Fetches all employees from a spreadsheet"""
user = security.authorize(request)
spreadsheet = EmployeeSpreadsheet.query.filter_by(
customer_id=user.customer.id)
unsaved = request.args.get('unsaved') == 'true'
if not unsaved:
spreadsheet = spreadsheet.filter_by(active=True)
spreadsheet = spreadsheet.order_by(
EmployeeSpreadsheet.upload_date.desc()).first()
if spreadsheet:
employees = Employee.query \
.options(joinedload('arrangements')) \
.filter_by(employee_spreadsheet_id=spreadsheet.id) \
.order_by(Employee.name, Employee.employee_id) \
.all()
else:
employees = []
duplicated_ids = Employee.duplicated_ids(employees)
mapper = lambda employee: convert_employee(employee, duplicated_ids)
return jsonify(list(map(mapper, employees)))
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 additional clarifications"""
user = security.authorize(request)
customer = user.customer
travel_history_ts = customer.last_available_travel_history
ignored_employees = IgnoredEmployee.query \
.filter_by(customer_id=customer.id) \
.all()
ignored_employees_keys = {(emp.traveller_name, emp.employee_id)
for emp in ignored_employees}
ignored_trips = IgnoredTrip.query \
.filter_by(customer_id=customer.id) \
.all()
ignored_trips_keys = {
(trip.traveller_name, trip.employee_id, trip.departure_date,
trip.departure_time, trip.arrival_date, trip.arrival_time,
trip.origin_country_name, trip.destination_country_name)
for trip in ignored_trips
}
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)
}
def employee_home_country_clarifications(employee):
"""Returns a list with all home country clarifications given an employee key"""
employee_key = employee[1] if employee[1] else employee[0]
return home_country_clars_by_employee_key[employee_key] \
if employee_key in home_country_clars_by_employee_key else []
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}
def inbound_assumption_answer(history_entry):
"""Returns any existing answer for an inbound assumption confirmation, or None"""
effective_employee_id = history_entry.employee_id if history_entry.employee_id \
else history_entry.traveller_name
trip_key = (effective_employee_id, history_entry.to_date)
if trip_key in inb_assump_by_trip_key:
return 'Y' if inb_assump_by_trip_key[trip_key].confirmed else 'N'
else:
return None
def inbound_assumption_correct_date(history_entry):
"""Returns any existing correct date for an inbound assumption confirmation, or None"""
effective_employee_id = history_entry.employee_id if history_entry.employee_id \
else history_entry.traveller_name
trip_key = (effective_employee_id, history_entry.to_date)
correct_date = inb_assump_by_trip_key[trip_key].correct_date \
if trip_key in inb_assump_by_trip_key else None
return correct_date.isoformat() if correct_date else None
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}
def outbound_assumption_answer(history_entry):
"""Returns any existing answer for an outbound assumption confirmation, or None"""
effective_employee_id = history_entry.employee_id if history_entry.employee_id \
else history_entry.traveller_name
trip_key = (effective_employee_id, history_entry.from_date)
if trip_key in out_assump_by_trip_key:
return 'Y' if out_assump_by_trip_key[trip_key].confirmed else 'N'
else:
return None
def outbound_assumption_correct_date(history_entry):
"""Returns any existing correct date for an outbound assumption confirmation, or None"""
effective_employee_id = history_entry.employee_id if history_entry.employee_id \
else history_entry.traveller_name
trip_key = (effective_employee_id, history_entry.from_date)
correct_date = out_assump_by_trip_key[trip_key].correct_date \
if trip_key in out_assump_by_trip_key else None
return correct_date.isoformat() if correct_date else None
originally_unclear = EmployeeTravelHistory.query \
.filter_by(customer_id=customer.id,
version_ts=travel_history_ts,
originally_unclear=True) \
.order_by(EmployeeTravelHistory.traveller_name,
EmployeeTravelHistory.employee_id) \
.all()
originally_unclear_employees = {(history.traveller_name,
history.employee_id)
for history in originally_unclear}
incomplete_trips = Travel.query \
.join(Travel.traveller_data) \
.join(TravellerData.traveller_data_periods) \
.filter(TravellerDataPeriod.customer_id == customer.id) \
.filter(Travel.invalid) \
.filter(Travel.ticket_type != 'Refund') \
.order_by(TravellerData.filename, Travel.row) \
.all()
inbound_assumptions_filter = and_(
EmployeeTravelHistory.customer_id == customer.id,
EmployeeTravelHistory.version_ts == travel_history_ts,
EmployeeTravelHistory.originally_assumed_inbound,
~EmployeeTravelHistory.category.in_([
TRAVEL_HISTORY_UK_EMPLOYEE,
TRAVEL_HISTORY_UK_EXPATRIATE,
]))
outbound_trip_joined_load = joinedload('outbound_trip')
traveller_data_joined_load = joinedload('outbound_trip.traveller_data')
inbound_assumptions = EmployeeTravelHistory.query \
.options(outbound_trip_joined_load) \
.options(traveller_data_joined_load) \
.filter(inbound_assumptions_filter) \
.order_by(EmployeeTravelHistory.traveller_name,
EmployeeTravelHistory.employee_id) \
.all()
outbound_assumptions_filter = and_(
EmployeeTravelHistory.customer_id == customer.id,
EmployeeTravelHistory.version_ts == travel_history_ts,
EmployeeTravelHistory.originally_assumed_outbound,
~EmployeeTravelHistory.category.in_([
TRAVEL_HISTORY_UK_EMPLOYEE,
TRAVEL_HISTORY_UK_EXPATRIATE,
]))
inbound_trip_joined_load = joinedload('inbound_trip')
inb_trav_data_joined_load = joinedload('inbound_trip.traveller_data')
outbound_assumptions = EmployeeTravelHistory.query \
.options(inbound_trip_joined_load) \
.options(inb_trav_data_joined_load) \
.filter(outbound_assumptions_filter) \
.order_by(EmployeeTravelHistory.traveller_name,
EmployeeTravelHistory.employee_id) \
.all()
distinct_travels_ignored_trips = (and_(
IgnoredTrip.customer_id == customer.id,
IgnoredTrip.traveller_name == Travel.traveller_name,
IgnoredTrip.employee_id == Travel.employee_id,
IgnoredTrip.origin_country_name == Travel.origin_country_name,
IgnoredTrip.destination_country_name ==
Travel.destination_country_name,
IgnoredTrip.departure_date == Travel.departure_date,
IgnoredTrip.departure_time == Travel.departure_time,
IgnoredTrip.arrival_date == Travel.arrival_date,
IgnoredTrip.arrival_time == Travel.arrival_time))
# pylint: disable=C0121
distinct_travels = db.session.query(Travel.traveller_name,
Travel.employee_id,
Travel.border_cross,
Travel.departure_date,
Travel.arrival_date,
Travel.origin_country_name,
Travel.destination_country_name) \
.join(Travel.traveller_data) \
.join(TravellerData.traveller_data_periods) \
.outerjoin(IgnoredTrip, distinct_travels_ignored_trips) \
.filter(IgnoredTrip.id == None) \
.filter(TravellerDataPeriod.customer_id == \
customer.id) \
.filter(Travel.invalid.is_(False)) \
.filter(Travel.ticket_type != 'Refund') \
.distinct() \
.subquery()
unclear_border = db.session.query(distinct_travels.c.traveller_name,
distinct_travels.c.employee_id,
distinct_travels.c.departure_date,
distinct_travels.c.arrival_date,
distinct_travels.c.border_cross) \
.group_by(distinct_travels.c.traveller_name,
distinct_travels.c.employee_id,
distinct_travels.c.departure_date,
distinct_travels.c.arrival_date,
distinct_travels.c.border_cross) \
.having(func.count(1) > 1) \
.subquery()
unclear_border_join_conditions = and_(
unclear_border.c.traveller_name == Travel.traveller_name,
unclear_border.c.employee_id == Travel.employee_id,
unclear_border.c.border_cross == Travel.border_cross,
unclear_border.c.departure_date == Travel.departure_date,
unclear_border.c.arrival_date == Travel.arrival_date)
query = db.session.query(Travel.traveller_name, Travel.employee_id,
Travel.border_cross, Travel.origin_country_name,
Travel.destination_country_name,
Travel.departure_date, Travel.departure_time,
Travel.arrival_date, Travel.arrival_time,
Travel.row, TravellerData.filename)
unclear_border_cross_time = query.distinct() \
.join(unclear_border, unclear_border_join_conditions) \
.join(Travel.traveller_data) \
.join(TravellerData.traveller_data_periods) \
.filter(TravellerDataPeriod.customer_id == customer.id) \
.filter(Travel.invalid.is_(False)) \
.filter(Travel.ticket_type != 'Refund') \
.order_by(Travel.traveller_name,
Travel.employee_id,
Travel.border_cross) \
.all()
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
}
def border_cross_time_correct_time(trip):
"""Returns any existing correct time for an unclear border cross time, or None"""
trip_key = (trip.traveller_name, trip.employee_id, trip.border_cross,
trip.origin_country_name, trip.destination_country_name)
correct_time = border_cross_clarifs_by_key[trip_key].correct_time \
if trip_key in border_cross_clarifs_by_key else None
return correct_time.strftime("%H:%M") if correct_time else None
# fetches every distinct combination of (effective_employee_id, traveller_name)
# from the travels table that is not ignored
effective_id_case_expression = case(
[(Travel.employee_id != '', Travel.employee_id)],
else_=Travel.traveller_name)
ignored_employee_join_expr = and_(
IgnoredEmployee.customer_id == customer.id,
IgnoredEmployee.traveller_name == Travel.traveller_name,
IgnoredEmployee.employee_id == Travel.employee_id)
# pylint: disable=C0121
distinct_eff_ee_ids = db.session.query(effective_id_case_expression.label('eff_id'),
Travel.traveller_name) \
.join(Travel.traveller_data) \
.join(TravellerData.traveller_data_periods) \
.outerjoin(IgnoredEmployee, ignored_employee_join_expr) \
.filter(TravellerDataPeriod.customer_id == customer.id) \
.filter(IgnoredEmployee.id == None) \
.distinct() \
.subquery()
# filters the query above keeping only the effective IDs
# with at least two different traveller names
duplicated_eff_ee_ids = db.session.query(distinct_eff_ee_ids.c.eff_id) \
.group_by(distinct_eff_ee_ids.c.eff_id) \
.having(func.count(1) > 1) \
.subquery()
# aggregates traveller names so we can present the user
# which names are actually duplicated
duplicated_ee_names = db.session.query(duplicated_eff_ee_ids.c.eff_id,Travel.traveller_name, func.array_agg(func.distinct(Travel.origin_country_name)) \
.label('origin_cnt'), func.array_agg(func.distinct(Travel.destination_country_name)).label('dest_cnt')) \
.group_by(Travel.traveller_name, duplicated_eff_ee_ids.c.eff_id) \
.filter(effective_id_case_expression ==duplicated_eff_ee_ids.c.eff_id) \
.join(Travel.traveller_data) \
.join(TravellerData.traveller_data_periods) \
.filter(TravellerDataPeriod.customer_id == customer.id) \
.filter(Travel.traveller_name != '') \
.order_by(duplicated_eff_ee_ids.c.eff_id) \
.distinct() \
.all()
def get_base_name(name):
if ' ' in name:
name = name.replace(' ', '/')
if name[:2].lower() == 'mr' or name[:2].lower(
) == 'ms' or name[:2].lower() == 'dr':
return name[2:].split('/')
if name[:4].lower() == 'miss':
return name[4:].split('/')
if name[-2:].lower() == 'mr' or name[-2:].lower(
) == 'ms' or name[-2:].lower() == 'dr':
return name[:-2].split('/')
if name[-4:].lower() == 'miss':
return name[:-4].split('/')
return name.split('/')
tmp_duplicated_ee_names = duplicated_ee_names[:]
for den in tmp_duplicated_ee_names:
ds = [d for d in duplicated_ee_names if d.eff_id == den.eff_id]
if len(ds) == 1:
duplicated_ee_names.remove(ds[0])
continue
names = [d.traveller_name for d in ds]
base_name = get_base_name(den.traveller_name)
tmp = names[:]
ds_len = len(ds)
for idx, n in enumerate(tmp):
t = n
flg = 0
t = t.replace(' ', '')
for bn in base_name:
if bn in t:
if bn == 'R':
if 'RMR' in t:
t = t.replace('RMR', 'MR')
continue
elif 'RDR' in t:
t = t.replace('RDR', 'DR')
continue
elif bn == 'S':
if 'SMS' in t:
t = t.replace('SMS', 'MS')
continue
elif 'SMISS' in t:
t = t.replace('SMISS', 'MISS')
continue
elif bn == 'M':
if 'MMS' in t:
t = t.replace('MMS', 'MS')
continue
elif 'MMISS' in t:
t = t.replace('MMISS', 'MISS')
continue
t = t.replace(bn, '')
else:
flg = 1
break
if flg == 1:
continue
t = t.replace('/', '').replace(' ', '').strip().lower()
if t == 'mr' or t == 'ms' or t == 'miss' or t == 'dr' or t == '':
if den.traveller_name != n:
duplicated_ee_names.remove(ds[idx])
tmp_duplicated_ee_names.remove(ds[idx])
ds_len -= 1
if ds_len == 1:
if den in duplicated_ee_names:
duplicated_ee_names.remove(den)
#Loading ans save Orgin and Dest countries
same_person_confirmations = SamePersonConfirmation.query \
.filter_by(customer_id=customer.id) \
.all()
same_person_confirmations_ee_ids = [
x.effective_employee_id for x in same_person_confirmations
]
#-----------------
#-----------------
return jsonify({
"ignoredEmployees": list(map(lambda employee: {
"id": employee.id,
"travellerName": employee.traveller_name,
"employeeId": employee.employee_id,
"undoIgnore": False,
}, ignored_employees)),
"ignoredTrips": list(map(lambda trip: {
"id": trip.id,
"travellerName": trip.traveller_name,
"employeeId": trip.employee_id,
"origin": trip.origin_country_name,
"destination": trip.destination_country_name,
"departureDate": trip.departure_date.isoformat() if trip.departure_date else None,
"departureTime": trip.departure_time.strftime('%H:%M') \
if trip.departure_time else None,
"arrivalDate": trip.arrival_date.isoformat() if trip.arrival_date else None,
"arrivalTime": trip.arrival_time.strftime('%H:%M') if trip.arrival_time else None,
"undoIgnore": False,
}, ignored_trips)),
"unclearHomeCountry": list(map(lambda employee: {
"travellerName": employee[0],
"employeeId": employee[1],
"ignore": False,
"clarifications": list(map(lambda clarification: {
"fromDate": clarification.from_date.isoformat() \
if clarification.from_date else None,
"toDate": clarification.to_date.isoformat() \
if clarification.to_date else None,
"homeCountry": clarification.home_country,
}, employee_home_country_clarifications(employee))),
}, filter(lambda employee: (employee[0], employee[1]) not in ignored_employees_keys,
originally_unclear_employees))),
"incompleteTrips": list(map(lambda trip: {
"id": trip.id,
"travellerName": trip.traveller_name,
"employeeId": trip.employee_id,
"sourceSpreadsheet": trip.traveller_data.filename,
"sourceRowNumber": trip.row,
"departureDate": trip.departure_date.isoformat() if trip.departure_date else None,
"departureTime": trip.departure_time.strftime('%H:%M') \
if trip.departure_time else None,
"arrivalDate": trip.arrival_date.isoformat() if trip.arrival_date else None,
"arrivalTime": trip.arrival_time.strftime('%H:%M') \
if trip.arrival_time else None,
"originCountry": trip.origin_country_name,
"destinationCountry": trip.destination_country_name,
}, incomplete_trips)),
"inboundAssumptions": list(map(lambda history_entry: {
"travellerName": history_entry.traveller_name,
"employeeId": history_entry.employee_id,
"fromDate": history_entry.from_date.isoformat() if history_entry.from_date else None,
"toDate": history_entry.to_date.isoformat() if history_entry.to_date else None,
"inboundAssumptionConfirmed": inbound_assumption_answer(history_entry),
"correctFromDate": inbound_assumption_correct_date(history_entry),
"sourceSpreadsheet": history_entry.outbound_trip.traveller_data.filename \
if history_entry.outbound_trip else None,
"sourceRowNumber": history_entry.outbound_trip.row \
if history_entry.outbound_trip else None,
"ignore": False,
"outboundTrip": {
"travellerName": history_entry.outbound_trip.traveller_name,
"employeeId": history_entry.outbound_trip.employee_id,
"originCountry": history_entry.outbound_trip.origin_country_name,
"destinationCountry": history_entry.outbound_trip.destination_country_name,
"departureDate": history_entry.outbound_trip.departure_date.isoformat() \
if history_entry.outbound_trip.departure_date else None,
"departureTime": history_entry.outbound_trip.departure_time.strftime('%H:%M') \
if history_entry.outbound_trip.departure_time else None,
"arrivalDate": history_entry.outbound_trip.arrival_date.isoformat() \
if history_entry.outbound_trip.arrival_date else None,
"arrivalTime": history_entry.outbound_trip.arrival_time.strftime('%H:%M') \
if history_entry.outbound_trip.arrival_time else None,
} if history_entry.outbound_trip else None,
}, filter(lambda a: not a.outbound_trip or \
(a.outbound_trip.traveller_name, a.outbound_trip.employee_id,
a.outbound_trip.departure_date, a.outbound_trip.departure_time,
a.outbound_trip.arrival_date, a.outbound_trip.arrival_time,
a.outbound_trip.origin_country_name,
a.outbound_trip.destination_country_name) \
not in ignored_trips_keys,
inbound_assumptions))),
"outboundAssumptions": list(map(lambda history_entry: {
"travellerName": history_entry.traveller_name,
"employeeId": history_entry.employee_id,
"fromDate": history_entry.from_date.isoformat() if history_entry.from_date else None,
"toDate": history_entry.to_date.isoformat() if history_entry.to_date else None,
"outboundAssumptionConfirmed": outbound_assumption_answer(history_entry),
"correctToDate": outbound_assumption_correct_date(history_entry),
"sourceSpreadsheet": history_entry.inbound_trip.traveller_data.filename \
if history_entry.inbound_trip else None,
"sourceRowNumber": history_entry.inbound_trip.row \
if history_entry.inbound_trip else None,
"ignore": False,
"inboundTrip": {
"travellerName": history_entry.inbound_trip.traveller_name,
"employeeId": history_entry.inbound_trip.employee_id,
"originCountry": history_entry.inbound_trip.origin_country_name,
"destinationCountry": history_entry.inbound_trip.destination_country_name,
"departureDate": history_entry.inbound_trip.departure_date.isoformat() \
if history_entry.inbound_trip.departure_date else None,
"departureTime": history_entry.inbound_trip.departure_time.strftime('%H:%M') \
if history_entry.inbound_trip.departure_time else None,
"arrivalDate": history_entry.inbound_trip.arrival_date.isoformat() \
if history_entry.inbound_trip.arrival_date else None,
"arrivalTime": history_entry.inbound_trip.arrival_time.strftime('%H:%M') \
if history_entry.inbound_trip.arrival_time else None,
} if history_entry.inbound_trip else None,
}, filter(lambda a: not a.inbound_trip or \
(a.inbound_trip.traveller_name, a.inbound_trip.employee_id,
a.inbound_trip.departure_date, a.inbound_trip.departure_time,
a.inbound_trip.arrival_date, a.inbound_trip.arrival_time,
a.inbound_trip.origin_country_name,
a.inbound_trip.destination_country_name) \
not in ignored_trips_keys,
outbound_assumptions))),
"unclearBorderCrossTime": list(map(lambda trip: {
"travellerName": trip.traveller_name,
"employeeId": trip.employee_id,
"borderCross": trip.border_cross.isoformat(),
"originCountry": trip.origin_country_name,
"destinationCountry": trip.destination_country_name,
"correctTime": border_cross_time_correct_time(trip),
"departureDate": trip.departure_date.isoformat() \
if trip.departure_date else None,
"departureTime": trip.departure_time.strftime('%H:%M') \
if trip.departure_time else None,
"arrivalDate": trip.arrival_date.isoformat() \
if trip.arrival_date else None,
"arrivalTime": trip.arrival_time.strftime('%H:%M') \
if trip.arrival_time else None,
"sourceSpreadsheet": trip.filename,
"sourceRowNumber": trip.row,
"ignore": False,
}, unclear_border_cross_time)),
"duplicateIDs": list(map(lambda dup: {
"effectiveEmployeeId": dup.eff_id,
"travellerName": dup.traveller_name,
"confirmed": dup.eff_id in same_person_confirmations_ee_ids,
"originCountry": dup.origin_cnt,
"destinationCountry": dup.dest_cnt,
"ignore": False,
}, duplicated_ee_names)),
})
def execute(customer_id, version_ts):
"""Called whenever a relevant entity is changed by
a client. Processes all employee travel history and
stores the result in the database"""
try:
sse_channel = 'travel-history-processing-progress-{0}-{1}'.format(
customer_id, version_ts)
# collect relevant data
sse.publish({
'status': 'Collecting relevant data...',
'progress': 0
},
channel=sse_channel)
employee_spreadsheet = EmployeeSpreadsheet.query \
.options(joinedload('employees')) \
.options(joinedload('employees.arrangements')) \
.filter_by(customer_id=customer_id, active=True) \
.first()
raw_employees_data = employee_spreadsheet.employees if employee_spreadsheet else []
raw_travel_data = Travel.query \
.yield_per(1000) \
.join(Travel.traveller_data) \
.join(TravellerData.traveller_data_periods) \
.filter(TravellerDataPeriod.customer_id == customer_id) \
.all()
raw_travel_data = sorted(raw_travel_data,
key=lambda x: x.effective_employee_id)
assumptions = Assumptions.query.get(customer_id)
treaties = Treaty.query.all()
home_country_clarifications = HomeCountryClarification.query \
.filter_by(customer_id=customer_id) \
.all()
inb_assu_confirmations = InboundAssumptionConfirmation.query \
.filter_by(customer_id=customer_id) \
.filter_by(confirmed=False) \
.all()
out_assu_conf = OutboundAssumptionConfirmation.query \
.filter_by(customer_id=customer_id) \
.filter_by(confirmed=False) \
.all()
border_cross_clarifs = BorderCrossTimeClarification.query \
.filter_by(customer_id=customer_id) \
.all()
ignored_employees = IgnoredEmployee.query \
.filter_by(customer_id=customer_id) \
.all()
ignored_trips = IgnoredTrip.query \
.filter_by(customer_id=customer_id) \
.all()
# process it
sse.publish({
'status': 'Processing employees...',
'progress': 10
},
channel=sse_channel)
employees = processing.employees.process(raw_employees_data)
sse.publish({
'status': 'Processing trips...',
'progress': 20
},
channel=sse_channel)
trips = processing.trips.process(raw_travel_data, border_cross_clarifs,
ignored_employees, ignored_trips)
sse.publish(
{
'status': 'Generating employee travel history...',
'progress': 30
},
channel=sse_channel)
emp_travel_history = processing.employee_travel_history.process(
employees, trips, home_country_clarifications, assumptions,
treaties, inb_assu_confirmations, out_assu_conf)
# we DONT want to modify any of the processed state,
# only the calculated values
db.session.expunge_all()
# store results
sse.publish({
'status': 'Storing results...',
'progress': 90
},
channel=sse_channel)
if emp_travel_history:
for row in emp_travel_history:
row['customer_id'] = customer_id
row['version_ts'] = version_ts
db.session.execute(EmployeeTravelHistory.__table__.insert(),
emp_travel_history)
# invalidate all current report periods so the user can see
# the most recent data
report_period_ids = db.session.query(ReportPeriod.id) \
.join('user') \
.filter(User.customer_id == customer_id)
ReportPeriod.query \
.filter(ReportPeriod.id.in_(report_period_ids)) \
.delete(synchronize_session=False)
parsed_version_ts = datetime.strptime(version_ts,
"%Y-%m-%dT%H:%M:%S.%f")
customer = Customer.query.get(customer_id)
if not customer.last_available_travel_history or \
customer.last_available_travel_history < parsed_version_ts:
customer.last_available_travel_history = parsed_version_ts
db.session.commit()
sse.publish({
'status': 'Finished!',
'progress': 100
},
channel=sse_channel)
except:
db.session.rollback()
parsed_version_ts = datetime.strptime(version_ts,
"%Y-%m-%dT%H:%M:%S.%f")
customer = Customer.query.get(customer_id)
if not customer.last_travel_history_error or \
customer.last_travel_history_error < parsed_version_ts:
customer.last_travel_history_error = parsed_version_ts
db.session.commit()
sse.publish({
'status': 'Finished with errors!',
'progress': 100
},
channel=sse_channel)
raise
