Payroll Data export

[blabj]

I need to get payroll data exported into an interface table (which we upload to our payroll provider), and I'm wondering what is the best method.

I've been working on writing SQL, but its getting pretty massive (so far joining punch_control, user_date, user_date_total, users, custom, and deparment). But determining semantics is a bugger.

I'm wondering if a report, combined with the export option could do the job for me.

The schema I need to map to is as follows:

Code: Select all

trans_date  (date of punch)
empl_code
job_class (timetrex dept)
machine (timetrex custom field 1)
pay_rate (from premium)
reg_hours
ot1_hours  (over-time hours @ 1.5)
ot2_hours  (over-time hours @ 2.0)

Any ideas? Pay rate could be optional (since I have those stored in an external database), but it would be nice to not have to track it in two systems.

-Bob

[shaunw]

What payroll provider do you use?

[blabj]

Ceridian. We use Powerpay Web.. and use the import feature which requires specific XML format.

We already have interface program developed inhouse to translate from that interface table (above) into appropriate XML format. Kind of a middle-ware layer.

We've always needed this middle layer because of the complex union rules we have to enforce.. things like "if an employee works 25 or more hours in a week at a higher paid job class, he gets the entire week at the higher rate".

So previously, with Winstar - we were able to customize a flat-file output will all needed fields.

The middleware, loaded that into a "interface" database table, applied the complex rules, and produced the needed XML output.

We want to minimize the changes required, so if we can load the identical interface table, the backend export to XML can stay unchanged.

[blabj]

I've been able to determine which type_id's are regular and overtime, but which field actually stores the punch date?

I've tried user_date.date_stamp, but its just an integer.. which could be the timetrex way of storing dates.. in which case whats your epoch? I could derive but I want to make sure I'm using the right field.

-Bob

[shaunw]

user_date.date_stamp should be a date field if you are looking at the SQL table itself:

Code: Select all

its=> \d user_date;
                             Table "public.user_date"
    Column     |   Type   |                       Modifiers
---------------+----------+--------------------------------------------------------
 id            | integer  | not null default nextval('user_date_id_seq'::regclass)
 user_id       | integer  | not null
 pay_period_id | integer  | not null
 date_stamp    | date     | not null
 created_date  | integer  |
 created_by    | integer  |
 updated_date  | integer  |
 updated_by    | integer  |
 deleted_date  | integer  |
 deleted_by    | integer  |
 deleted       | smallint | not null default 0

If you are accessing it in PHP though, it would be an integer because PHP uses the Unix Epoch (January 1 1970 00:00:00 GMT) for dates.

Keep in mind this is just the date that the punch falls on (or displayed under in the TimeSheet), not the actual punch timestamp. For the punch time_stamp itself, you need to go through the punch_control table to get to the punch table, but you also need to consider which punches you want to use, likely "In" punches, so that would be punches of status_id = 10.

[blabj]

Thanks.. I'm using OpenOffice Base with JDBC mysql driver (its just easy to graphically do joins), and its returning date as integer.. so likely its something with that.. no big deal since my final query will be direct in mysql.

Could I get a list of user_date_total.type_id 's and status_id's (ie. what they mean)? Trial and error will get me there, but I don't want to miss anything.

Is it safe to assume that "deleted = 0" be used whenever there is a deleted field in a table?

Thanks again!

-Bob

[shaunw]

Here are a list of type_id/status_id for the punch table:

Code: Select all

			case 'status':
				$retval = array(
										10 => TTi18n::gettext('In'),
										20 => TTi18n::gettext('Out'),
									);
				break;
			case 'type':
				$retval = array(
										10 => TTi18n::gettext('Normal'),
										20 => TTi18n::gettext('Lunch'),
										30 => TTi18n::gettext('Break'),
									);
				break;

Yes, if there is a deleted column in the table, make sure you have include deleted = 0 in your where clause.

[blabj]

Thanks.. but can I get type_id/status_id for user_date_total ?

[blabj]

OK I have successfully exported necessary data via SQL - I'm extracting premiums, regular hours, ot hours, and absences. If anyone is interested in the SQLs - I'd be happy to post.

The last hurdle I have is pay_stub_amendment ... the effective date is an integer and not a date.

With a mysql query, how do I convert that integer to a date?

ie. I want to do "pay_stub_amendment.effective_date between '2008-12-28' and '2009-01-03'"

Thanks,

-Bob

[shaunw]

Code: Select all

select FROM_UNIXTIME( 1194873780, '%Y-%m-%d');

Code: Select all

select FROM_UNIXTIME(pay_stub_amendment.effective_date , '%Y-%m-%d');

[blabj]

Excellent.. thanks!

Specifically I'm doing this:

Code: Select all

SELECT FROM_UNIXTIME(pay_stub_amendment.effective_date,'%d/%m/%Y') AS trans_date,
 users.user_name, pay_stub_amendment.amount, pay_stub_amendment.description
FROM users, pay_stub_amendment
WHERE users.id = pay_stub_amendment.user_id
AND pay_stub_amendment.effective_date between UNIX_TIMESTAMP('2008-12-27')
  and UNIX_TIMESTAMP('2009-01-03')
AND pay_stub_amendment.deleted = 0
AND users.deleted = 0

[shaunw]

Just as a reminder, TimeTrex is a 24hr system, there could be pay stub amendments with an effective date of "2009-01-03 00:00:01" that won't be included in that query that you just posted.

[blabj]

Ya, I thought of that after I posted.. I've changed it to the following:

Code: Select all

SELECT FROM_UNIXTIME(pay_stub_amendment.effective_date,'%d/%m/%Y') AS trans_date, users.user_name,
 pay_stub_amendment.amount, pay_stub_amendment.description
FROM users, pay_stub_amendment
WHERE users.id = pay_stub_amendment.user_id
AND FROM_UNIXTIME(pay_stub_amendment.effective_date,'%Y-%m-%d')
  between '2008-12-28' and '2009-01-03'
AND pay_stub_amendment.deleted = 0
and users.deleted = 0