示例#1
0
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,
    })
示例#2
0
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)
示例#3
0
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)
示例#4
0
 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
示例#5
0
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
示例#6
0
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)
示例#7
0
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)
示例#8
0
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)
示例#9
0
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)
示例#10
0
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)
示例#11
0
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)
示例#12
0
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)
示例#13
0
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)
示例#14
0
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)
示例#15
0
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
示例#16
0
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)
示例#17
0
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)
示例#18
0
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)
示例#19
0
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)
示例#20
0
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)
示例#21
0
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)
示例#22
0
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)
示例#23
0
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)
示例#24
0
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)
示例#25
0
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)
示例#26
0
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)
示例#27
0
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,
    })
示例#28
0
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})
示例#29
0
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)
示例#30
0
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,
    })