TimeTrex Time and Attendance

I have the report I need setup in the HTML interface as a "Payroll Export Report" to give me
"First Name","Last Name","Employee #","Pay Period","other_id4","other_id3","other_id2","other_id1","Hours Code","Hours","Hourly Rate"
for the last pay period but I'd prefer to pull this directly by API.

I've tried PayrollExportReport and TimesheetSummaryReport and with the template 'by_employee+all_time' they are both giving me
"regular_time"
"overtime_time" (Overtime and Doubletime)
"absence_time" (Holiday, sick and vacation)

I definitely need each paycode separated by line.

Then I tried creating the config array manually which then dropped "regular_time", "overtime_time" and "absence_time" .

$config = array(
"-1010-time_period" => array(
"time_period" => "last_pay_period"
),
"-5010-columns" => array(
"last_name",
"first_name",
"employee_number",
"pay_period",
"other_id4",
"other_id3",
"other_id2",
"other_id1",
"hour_code"
),
"-5011-group" => array(
"last_name",
"first_name",
"employee_number",
"pay_period",
"other_id4",
"other_id3",
"other_id2",
"other_id1"

)
);

Any suggestions (or pointers to some documentation) to get this export working through the API?

Thanks
Rois

We always recommend that get the report working through the UI first, then its really easy to extract that setup and access it through the API.

So have you gotten the report to return all the data you need through the UI?

Yes I have it returning exactly what and how I want it through the UI in a saved report. I've attached a copy of the timetrex.log while I was running the UI version hoping to get some clues.

I pruned it down for what I didn't think mattered so hopefully I didn't over prune.

We have multiple separate companies on our TimeTrex install. I see there are lots of ids in the log that would make this customer specific. I'll go with that if that's the only option but I was hoping to make it more generic so I can re-use it directly from the API for other customers easily. Also, if it's every hour code I won't miss newly added ones.

The goal:
* Last_pay_Period
* All employees
* Hours for each hour codes being used for that employee
* Summary grouping by employee, hourcode

I realize "other_id4","other_id3","other_id2","other_id1" and "Hourly Rate"are repeated for every line of each employee and I've programmed around that in my receiving code but I'm using them for the customer to communicate ROP changes as well as other pay related items needing to be on the payroll like mileage, commissions, etc . . .

Thoughts?

There is pretty much no way to make the reports generic across companies in TimeTrex, as even if the setup is 100% identical across them, all pay codes have different IDs. So you would either need to have your script dynamically generate the entire report config (which again could be difficult if the setup varies at all), or use a Saved Report with the same name in each company, and simply extract that saved report.

If you want to use the saved report method, please see the tools/export/export_report.php script, as you can pass it command line arguments for the type of report and the saved report name, and it will extract that report to a file. You can look at its source code to see how it grabs the report setup as well if you want.

Pretty old but I'm coming back to this. At this point I want every client's export to be identical and unchangeable, I'm going with the option to build the config from data retrieved through the API. I've got this working but, it is returning the "-1002-columns". I need it to return the "export_columns" as the API call does through the UI. I believe it's because I don't know how to send the json "1" => "payroll_export" through the API to trigger the "Export" data return as I get in the UI when I click "Export".

I'm watching the API calls through the Firefox development menu for the Timetrex UI.

api/json/api.php?Class=APIPayrollExportReport&Method=getPayrollExportReport&MessageID=<<MessageID removed for forum post>>
json "{\"0\":{\"-1001-time_period\":{\"time_period\":\"last_pay_period\"},\"-1002-columns\":[\"first_name\",\"last_name\",\"worked_time\",\"regular_time\",\"overtime_time\",\"absence_time\",\"premium_time\"],\"-1003-group\":[\"first_name\",\"last_name\"],\"-1004-sort\":[{\"last_name\":\"asc\"},{\"first_name\":\"asc\"}],\"-1000-template\":\"by_employee+all_time\",\"other\":{\"page_orientation\":\"P\",\"font_size\":0,\"auto_refresh\":false,\"disable_grand_total\":false,\"maximum_page_limit\":100,\"show_duplicate_v…4-9a40-9e46-37311cc1e501\":{\"hour_code\":\"VAC\"},\"pay_code:11ea160e-9332-0df0-ad6a-71b199d98266\":{\"hour_code\":\"UVAC\"}},\"export_columns\":[\"employee_number\",\"last_name\",\"first_name\",\"pay_period\",\"custom_field-05ea47a2-ba2d-8c0e-1c39-59fc4d0a9190\",\"custom_field-05ea47a2-ba14-b100-471d-59fc4d0a9190\",\"custom_field-05ea47a2-b9fc-9143-d65b-59fc4d0a9190\",\"custom_field-05ea47a2-b9e0-5567-6bef-59fc4d0a9190\",\"custom_field-05ea47a2-ba46-96ae-f0be-59fc4d0a9190\"]}}},\"1\":\"payroll_export\"}"

I can build the config and send 'raw', which returns the "-1002-columns" result. If I change 'raw' to 'payroll_export', the result is an empty string.

Any thoughts on how I duplicate the UI process and what I might be missing?

Can you provide details about how you send "raw" specifically? Are you using the command line tools to obtain this report, or making API calls directly from your own code?

API from my own code. I copied the code from https://www.timetrex.com/workforce-management-api for buildURL, handleResult and postToURL and made minor alteration to work in my code base.

So you should be able to use the exact JSON payload provided by the UI when tracing is enabled. The "payroll_export" is just a 2nd parameter in the data structure, like this:

I found some array vs object formatting issues but now the only think returned from curl for $result is the column headers and no data.

$result = ""Employee #","Last Name","First Name","Pay Period","Production Pay","Sealant/Production Bonus","Ordering Bonus","Bonus","Other Payouts","Hours Code","Hours","Hourly Rate"
"
When I run it from the UI I'm getting 70 records or data.

Below is the json_encode and then the line that's doing urlencode for $post_data (copied out of my php debug session). Maybe I don't have something configured correctly because the urlencode below has hex strings and the one I copied out the developer console just had the double-quotes escaped with a backslash. Does that matter or do you see something else I may need to tweak?

json_encode($data) = "{"0":{"-1001-time_period":{"time_period":"last_pay_period"},"-1002-columns":["first_name","last_name","worked_time","regular_time","overtime_time","absence_time","premium_time"],"-1003-group":["first_name","last_name"],"-1004-sort":[{"last_name":"asc"},{"first_name":"asc"}],"-1000-template":"by_employee+all_time","other":{"page_orientation":"P","font_size":0,"auto_refresh":false,"disable_grand_total":false,"maximum_page_limit":100,"show_duplicate_values":false},"chart":{"enable":false,"display_mode":10,"type":10,"include_sub_total":false,"axis_scale_static":false,"combine_columns":true},"form":{"export_type":"csv_advanced","csv_advanced":{"columns":{"pay_code => 11ea160e-932b-c080-a094-f7409f3b995a":{"hour_code":"BEREAV"},"pay_code => 11ea160e-9316-6d20-9783-cc3520d730b7":{"hour_code":"BRK"},"pay_code => 11ea160e-9329-ec10-9d72-a00f817b3ee4":{"hour_code":"JURY"},"pay_code => 11ea160e-9314-5440-aaf3-a78804dd3a2e":{"hour_code":"LNH"},"pay_code => 11ea160e-9319-8440-8b5e-b8ce15bf37f2":{"hour_code":"OT1"},"pay_code => 11ea160e-931c-09c0-a1da-6650317da887":{"hour_code":"OT1"},"pay_code => 11ea160e-9337-3fd0-beb7-f488f0d88961":{"hour_code":"PTO"},"pay_code => 11ea160e-931e-95a0-9541-ec4cc4d22464":{"hour_code":"PRE1"},"pay_code => 11ea160e-9321-3230-b00c-25e32501c484":{"hour_code":"PRE2"},"pay_code => 11ea160e-9312-3230-ae91-4434b99a31e0":{"hour_code":"REG"},"pay_code => 11ea160e-932e-4ac0-8dc3-23bfc81b53b8":{"hour_code":"SICK"},"pay_code => 11ea160e-9330-3500-80c2-a28405e4cf6e":{"hour_code":"USICK"},"pay_code => 11ea160e-9328-1bb0-8614-8bde8928801f":{"hour_code":"STAT"},"pay_code => 11ea160e-9323-4ae0-831f-d23e1eb16fa0":{"hour_code":"BANK"},"pay_code => 11ea160e-9325-4fe0-b773-7ae89a90d5ad":{"hour_code":"BANK"},"pay_code => 11ea160e-9301-2800-8220-617d520a0f22":{"hour_code":"UNPAID"},"pay_code => 11ea160e-9334-9a40-9e46-37311cc1e501":{"hour_code":"VAC"},"pay_code => 11ea160e-9332-0df0-ad6a-71b199d98266":{"hour_code":"UVAC"}},"export_columns":["employee_number","last_name","first_name","pay_period","custom_field-05ea47a2-ba2d-8c0e-1c39-59fc4d0a9190","custom_field-05ea47a2-ba14-b100-471d-59fc4d0a9190","custom_field-05ea47a2-b9fc-9143-d65b-59fc4d0a9190","custom_field-05ea47a2-b9e0-5567-6bef-59fc4d0a9190","custom_field-05ea47a2-ba46-96ae-f0be-59fc4d0a9190"]}}},"1":"payroll_export"}"

'json='.urlencode( json_encode($data) ) = "json=%7B%220%22%3A%7B%22-1001-time_period%22%3A%7B%22time_period%22%3A%22last_pay_period%22%7D%2C%22-1002-columns%22%3A%5B%22first_name%22%2C%22last_name%22%2C%22worked_time%22%2C%22regular_time%22%2C%22overtime_time%22%2C%22absence_time%22%2C%22premium_time%22%5D%2C%22-1003-group%22%3A%5B%22first_name%22%2C%22last_name%22%5D%2C%22-1004-sort%22%3A%5B%7B%22last_name%22%3A%22asc%22%7D%2C%7B%22first_name%22%3A%22asc%22%7D%5D%2C%22-1000-template%22%3A%22by_employee%2Ball_time%22%2C%22other%22%3A%7B%22page_orientation%22%3A%22P%22%2C%22font_size%22%3A0%2C%22auto_refresh%22%3Afalse%2C%22disable_grand_total%22%3Afalse%2C%22maximum_page_limit%22%3A100%2C%22show_duplicate_values%22%3Afalse%7D%2C%22chart%22%3A%7B%22enable%22%3Afalse%2C%22display_mode%22%3A10%2C%22type%22%3A10%2C%22include_sub_total%22%3Afalse%2C%22axis_scale_static%22%3Afalse%2C%22combine_columns%22%3Atrue%7D%2C%22form%22%3A%7B%22export_type%22%3A%22csv_advanced%22%2C%22csv_advanced%22%3A%7B%22columns%22%3A%7B%22pay_code+%3D%3E+11ea160e-932b-c080-a094-f7409f3b995a%22%3A%7B%22hour_code%22%3A%22BEREAV%22%7D%2C%22pay_code+%3D%3E+11ea160e-9316-6d20-9783-cc3520d730b7%22%3A%7B%22hour_code%22%3A%22BRK%22%7D%2C%22pay_code+%3D%3E+11ea160e-9329-ec10-9d72-a00f817b3ee4%22%3A%7B%22hour_code%22%3A%22JURY%22%7D%2C%22pay_code+%3D%3E+11ea160e-9314-5440-aaf3-a78804dd3a2e%22%3A%7B%22hour_code%22%3A%22LNH%22%7D%2C%22pay_code+%3D%3E+11ea160e-9319-8440-8b5e-b8ce15bf37f2%22%3A%7B%22hour_code%22%3A%22OT1%22%7D%2C%22pay_code+%3D%3E+11ea160e-931c-09c0-a1da-6650317da887%22%3A%7B%22hour_code%22%3A%22OT1%22%7D%2C%22pay_code+%3D%3E+11ea160e-9337-3fd0-beb7-f488f0d88961%22%3A%7B%22hour_code%22%3A%22PTO%22%7D%2C%22pay_code+%3D%3E+11ea160e-931e-95a0-9541-ec4cc4d22464%22%3A%7B%22hour_code%22%3A%22PRE1%22%7D%2C%22pay_code+%3D%3E+11ea160e-9321-3230-b00c-25e32501c484%22%3A%7B%22hour_code%22%3A%22PRE2%22%7D%2C%22pay_code+%3D%3E+11ea160e-9312-3230-ae91-4434b99a31e0%22%3A%7B%22hour_code%22%3A%22REG%22%7D%2C%22pay_code+%3D%3E+11ea160e-932e-4ac0-8dc3-23bfc81b53b8%22%3A%7B%22hour_code%22%3A%22SICK%22%7D%2C%22pay_code+%3D%3E+11ea160e-9330-3500-80c2-a28405e4cf6e%22%3A%7B%22hour_code%22%3A%22USICK%22%7D%2C%22pay_code+%3D%3E+11ea160e-9328-1bb0-8614-8bde8928801f%22%3A%7B%22hour_code%22%3A%22STAT%22%7D%2C%22pay_code+%3D%3E+11ea160e-9323-4ae0-831f-d23e1eb16fa0%22%3A%7B%22hour_code%22%3A%22BANK%22%7D%2C%22pay_code+%3D%3E+11ea160e-9325-4fe0-b773-7ae89a90d5ad%22%3A%7B%22hour_code%22%3A%22BANK%22%7D%2C%22pay_code+%3D%3E+11ea160e-9301-2800-8220-617d520a0f22%22%3A%7B%22hour_code%22%3A%22UNPAID%22%7D%2C%22pay_code+%3D%3E+11ea160e-9334-9a40-9e46-37311cc1e501%22%3A%7B%22hour_code%22%3A%22VAC%22%7D%2C%22pay_code+%3D%3E+11ea160e-9332-0df0-ad6a-71b199d98266%22%3A%7B%22hour_code%22%3A%22UVAC%22%7D%7D%2C%22export_columns%22%3A%5B%22employee_number%22%2C%22last_name%22%2C%22first_name%22%2C%22pay_period%22%2C%22custom_field-05ea47a2-ba2d-8c0e-1c39-59fc4d0a9190%22%2C%22custom_field-05ea47a2-ba14-b100-471d-59fc4d0a9190%22%2C%22custom_field-05ea47a2-b9fc-9143-d65b-59fc4d0a9190%22%2C%22custom_field-05ea47a2-b9e0-5567-6bef-59fc4d0a9190%22%2C%22custom_field-05ea47a2-ba46-96ae-f0be-59fc4d0a9190%22%5D%7D%7D%7D%2C%221%22%3A%22payroll_export%22%7D"

Because you are getting column headers back, its likely that the filter criteria you are specifying is incorrect, so there are no rows to return. Check the TimeTrex server log, it might provide more information. It should show you a nicely formatted version of the report criteria it receives, so you can compare it between the UI and the API calls to find any differences.

I will also mention you should not be passing the JSON data on the URL, therefore urlencoding is not necessary. It should be passed in the POST body instead.

Kind of wondered about the urlencode but it's in the postToURL function on https://www.timetrex.com/workforce-management-api. I edited it out.

// $post_data = 'json='.urlencode( json_encode($data) );
$post_data = 'json='.json_encode($data);

I'm getting result data back but it's coming in as CSV instead of JSON. Since I've seen "export_type: "csv_advanced"" I'll just write something in to handle it that way instead of json_decode.

Thanks for the assistance.

The payroll export report is primarily designed to export in CSV or other proprietary formats, so it won't return JSON if you pass 'payroll_export' (Export button in the UI) as the action, as that tells it to use the format you specified in the export setup (ie: not JSON). If you want it to return JSON, you have to do the equivalent of clicking the "View" button in the UI, but then you wouldn't get the export mapping applied.

It was easy enough to convert to an array from CSV.

Export_columns question: I'm using the employee's user id (UUID) instead of "Employee #" in all my programming so that I don't have to worry about someone changing my key. Any way to get the user's UUID added to "export_columns". I can pull my users every time to map "Employee #" to user's UUID but would rather avoid it if possible? I've tried "id" and "user_id" and they both come up an empty string.

That is on the roadmap for a future version.