Employer pays its employees by the hour (there are no salaried employees.) Employees belong to one of two job groups which determine their wages; job group A is paid $20/hr, and job group B is paid $30/hr. Each employee is identified by a string called an "employee id" that is globally unique in their system.
Hours are tracked per employee, per day in comma-separated value files (CSV). Each individual CSV file is known as a "time report", and will contain:
- A header, denoting the columns in the sheet (
date
,hours worked
,employee id
,job group
) - 0 or more data rows
- A footer row where the first cell contains the string
report id
, and the second cell contains a unique identifier for this report.
File content guarantees:
- Columns will always be in that order.
- There will always be data in each column.
- There will always be a well-formed header line.
- There will always be a well-formed footer line.
Example input files named sample.csv
and sample2.csv
are included in this repo.
We've agreed to build the following web-based prototype for our partner.
- Accepts a comma separated file with the schema described in the previous section.
- Parses the given file, and store the timekeeping information in a relational database for archival reasons.
- After upload, the application should displays a payroll report. This report is accessible to the user without them having to upload a file first.
- If an attempt is made to upload two files with the same report id, the second upload fails
The payroll report is structured as follows:
- There are 3 columns in the report:
Employee Id
,Pay Period
,Amount Paid
- A
Pay Period
is a date interval that is roughly biweekly. Each month has two pay periods; the first half is from the 1st to the 15th inclusive, and the second half is from the 16th to the end of the month, inclusive. - Each employee will have a single row in the report for each pay period
that they have recorded hours worked. The
Amount Paid
is be reported as the sum of the hours worked in that pay period multiplied by the hourly rate for their job group. - If an employee was not paid in a specific pay period, there will not be a row for that employee + pay period combination in the report.
- The report should be sorted by employee id and then pay period start
- The report is based on all of the data across all of the uploaded time reports, for all time.
As an example, a sample file with the following data:
date | hours worked | employee id | job group |
---|---|---|---|
4/11/2016 | 10 | 1 | A |
14/11/2016 | 5 | 1 | A |
20/11/2016 | 3 | 2 | B |
should produce the following payroll report:
Employee ID | Pay Period | Amount Paid |
---|---|---|
1 | 1/11/2016 - 15/11/2016 | $300.00 |
2 | 16/11/2016 - 30/11/2016 | $90.00 |
$ mkdir -p $GOPATH/src/github.com/wung-s
$ cd $GOPATH/src/github.com/wung-s
$ git clone git@github.com:wung-s/payroll.git && cd payroll
$ buffalo db create -a
$ buffalo db migrate
$ buffalo task db:seed
$ PORT=4000 buffalo dev
TIMEZONE=EDT
- Only one partner
- No authentication
- Date in the
csv
are always in the formatDD/MM/YYYY