HSCC Programming Problem: OfferTrak Inc

This is a modified version of the 2015 HSCC Programming Problem that was presented to contestants

The problem is designed to be solved using a variety of skills such as:

Problem Statement
Read this document carefully.

Tracking information concerning candidates and the components of the recruiting process is valuable to recruiters during the hiring process.

Develop a system to calculate salary and other information that is required during the hiring process.

This App will allow recruiters to keep track of that information.

This App should be responsive in order to display and work well on all devices - laptops, desktops, mobile phones, tablets, etc.


Recruiters working with this OfferTrak Inc App will be able to do the following:
  1. Login to the App
    • URL for this App shall be: http://localhost/offertrak/
    • Password Security must be programmed into the application
    • Users will be required to login with their email id and password
    • On successful login:
      • display the App Dashboard which shows some basic stats and links
        examples: # of applicants in database, # of jobs and job offers, links to various reports, etc.
      • Reset bad_login_count to 0 on successful login
    • If this is the first login, user must be prompted to change password

      Passwords must meet the following criteria:

      • 8-character minimum
      • 16-character maximum
      • at least 1 lower case letter
      • at least 1 upper case letter
      • at least 1 number
      • at least 1 special character
    • Password must be saved in encrypted format in the database
    • Password Modified Date must be updated in the database
    • The password rules and instructions must be displayed on the change password screen
    • Account MUST be locked after 3 failed attempts. The bad_login_count field on the offertrak_users table can be used to track attempts
    • When a wrong password is entered, display the number of logins left before the account is locked
    • Administrators will have the ability to unlock a locked account. If bad_login_count is greater than or equal to 3, the account is locked.
  2. Self-register
    • Recruiters must be able to register (signup) themselves to access the App
    • An administrator must approve new users in the Manage Users page
  3. Add or Edit and Applicant
    • Recruiters should be able to add or edit an applicant to the offertrak_applicants table
    • After saving the applicant information, the Detailed Job Applicant Report page should be displayed
    • Recruiters can edit any applicant in the database
  4. View General Job Applicant list:

    This report should track the applicants in the database

    This report should track the positions (jobs) that are offered to job applicants

    Show the following headers:

    • Full Applicant name
    • Recruiting Source (Email/Job Fair, etc)
    • Tax Filing Status
    • Number of active Job Offers

    Clicking (or touching) a particular row should show the detailed Job Applicant Report

  5. View Detailed Job Applicant report:

    Recruiters should be able to view the report which shows the following:

    • Applicant name
    • Job titles offered - an applicant may be offered more than 1 job
    • Job location - city/state
    • Salaries offered
    • Date of offer
    • Status of application such as whether required information has been received: cover letter, references, etc.
    • Additional information on how applicant found the job (email, search engine, referral, etc)
    • Salary, insurance, workers compenstation, and tax information associated with the job offer; Use the metrics provided in tables below
    • Costs associated with recruiting the applicant such as: Recruiter or Agency fees, Travel expenses, etc.
    • This detailed report must also be available in printer-ready format for submitting to interested authorities such as management or the IRS

    6. When displaying the offer information the following costs will be calculated or displayed, based on the salary attached to the offer and a total of the offer will be displayed.

    How to calculate costs

    1. Insurance costs are 14% of offered salary
    2. Workers compensation are 8% of offered salary
    3. Taxes (see Tax Rates in the table below)
      • Taxes are calculated by determining the tax bracket - which is based on applicant filing status e.g Single, Married, etc.
      • multiply the salary x percentage tax rate to determine the amount of taxes
    4. Recruiter or agency fees are 11% of the offered salary
    5. Travel expenses are capped at $1500
    6. When the salary field changes, all fields will be recalculated, and redisplayed.
    7. The percentages used in the calculations will be displayed on the screens and reports.
    8. You should always display the description or name for an item, rather than the code value.

    Database

    The database and tables (with test data), have been supplied to you as MySQL database SQL file.

    You will need it to build the tables and populate the test data.

    The following tables are included:


    While changing the database structure (types of fields and relationships) is possible, it is not recommended.

    Refer to this schema offertrak.sql for specific field names, data types, keys, and relationships


    Tips and Hints

    These are not provided in a real contest