Page 1 of 2

Importing User and Pay data

Posted: Thu Jul 12, 2007 1:19 pm
by rons
Can you provide an example of how the command line import feature for users and their pay stub information works? The /tools/import php and mapping txt files are not clear cut and can this be included in future manuals.

Posted: Thu Jul 12, 2007 2:43 pm
by shaunw
The best way to start importing employees into TimeTrex is to start with an excel spreadsheet with all the data that you want to import separated into columns.

Can you please post the name of each column (column header) and a single sample line from the file? That way I can give you a specific example of what to do.

Posted: Thu Jul 12, 2007 7:35 pm
by rons
user_data.csv file format with data
Column Data
company_id 1
status_id 10
user_name johnsmith
password test
sin 111223333
first_name John
middle_name F
last_name Smith
address1 10 Main St
City Anywhere
Province MD
postal_code 11111
hire_date 01/01/2007
wage_type_id 1 (hourly)
wage 15.00
wage_weekly_time 40:00
federal_income_tax_deduction 2
province_income_tax_deduction 2
filing_status S (Single)

Posted: Fri Jul 13, 2007 9:43 am
by shaunw
Okay, so I see you have made your source file match the TimeTrex format somewhat. It looks pretty good except the wage type id. Hourly is wage_type_id=10, or if you just use the text "hourly" it should work fine.

Can you post the mapping file you are using as well as the command line you have tried, if at all?

Posted: Fri Jul 13, 2007 10:47 am
by rons
I need help on how to proceed from here.

Posted: Fri Jul 13, 2007 11:54 am
by shaunw
Okay, from here you need to setup the user_column_map.txt file, since your source file columns match the TimeTrex columns, there isn't much to do.

Code: Select all

timetrex_column, csv_column, default_value, parse_hint
"company_id","","",""
"status_id","","",""
"currency_id","","",""
"pay_period_schedule_id","","",""
"policy_group_id","","",""
"user_name","","",""
"password","","",""
"phone_id","","",""
"phone_password","","",""
"ibutton_id","","",""
"employee_number","","",""
"title_id","","",""
"default_branch_id","","",""
"default_department_id","","",""
"first_name","","",""
"middle_name","","",""
"last_name","","",""
"sex_id","","",""
"address1","","",""
"address2","","",""
"city","","",""
"country","","",""
"province","","",""
"postal_code","","",""
"work_phone","","",""
"work_phone_ext","","",""
"home_phone","","",""
"mobile_phone","","",""
"fax_phone","","",""
"home_email","","",""
"work_email","","",""
"birth_date","","","m/d/y"
"hire_date","","","m/d/y"
"termination_date","","",""
"sin","","",""
"other_id1","","",""
"other_id2","","",""
"other_id3","","",""
"other_id4","","",""
"other_id5","","",""
"note","","",""
"wage_type_id","","10",""
"wage","","",""
"wage_weekly_time","","40:00","10"
"wage_effective_date","","01/01/2007","m/d/y"
"labor_burden_percent","","",""
"federal_income_tax_deduction_id","","Federal Income Tax",""
"federal_income_tax_user_value1","","",""
"federal_income_tax_user_value2","","",""
"federal_income_tax_user_value3","","",""
"federal_income_tax_user_value4","","",""
"federal_income_tax_user_value5","","",""
"province_income_tax_deduction_id","","State Income Tax",""
"province_income_tax_user_value1","","",""
"province_income_tax_user_value2","","",""
"province_income_tax_user_value3","","",""
"province_income_tax_user_value4","","",""
"province_income_tax_user_value5","","",""
Really the only change is setting the default value for your wage_effective_date since you didn't specify that in your source file.

The next step is to try a import, if you run the following command it will tell you what command line options are available:

Code: Select all

# php tools/import/import_users.php -h
Usage: import_users.php [OPTIONS] [Column MAP file] [CSV File]
  Options:
    -u <unique column>  Update already existing users based on unique column, ie: employee_number,user_name,sin,
    -n                          Dry-run, display the first two lines to confirm mapping is correct
WARNING: Clear TimeTrex cache after running this.
So now if you run:

Code: Select all

# php tools/import/import_users.php -n -u user_name tools/import/user_column_map.txt user_data.csv
That will attempt the import in "dry run" mode, which won't actually change your database at all, it just tests to make sure there are no errors. If there are no errors you can remove the "-n" switch to actually import the data.

Posted: Wed Jul 18, 2007 3:09 pm
by rons
When trying to test the file import feature with the command php tools/import/import_users.php -h, I am getting these errors:
Warning: require_once(/home/swiftbo/public_html/TimeTrex/tools/import/../includes/global.inc.php): failed to open stream: No such file or directory in /home/swiftbo/public_html/TimeTrex/tools/import/import_users.php on line 28

Fatal error: require_once(): Failed opening required '/home/swiftbo/public_html/TimeTrex/tools/import/../includes/global.inc.php' (include_path='.:/usr/share/php:/usr/share/pear') in /home/swiftbo/public_html/TimeTrex/tools/import/import_users.php on line 28

Posted: Wed Jul 18, 2007 4:57 pm
by shaunw
There are two ways you can fix this issue.

1. Move the files to the tools directory, not tools/import.

or

2. Near the top of the import_users.php file, replace the two require_once() lines with the following:

Code: Select all

require_once( dirname(__FILE__) . DIRECTORY_SEPARATOR .'..'. DIRECTORY_SEPARATOR .'..'. DIRECTORY_SEPARATOR .'includes'. DIRECTORY_SEPARATOR .'global.inc.php');
require_once( dirname(__FILE__) . DIRECTORY_SEPARATOR .'..'. DIRECTORY_SEPARATOR .'..'. DIRECTORY_SEPARATOR .'includes'. DIRECTORY_SEPARATOR .'CLI.inc.php');
The above should only be two lines, so it may be wrapped in your browser.

Posted: Thu Jul 19, 2007 10:11 am
by rons
After successfully importing the employee data as per the previous posts, it looks like the payroll info (i.e wage, wage_id,wage_weekly_time) did not post to the system. It may be that I needed to include the pay_period_schedule_id and other fields. Also, when trying to edit the imported employees files, the system is requiring an employee number. Can the system generated employee_id be used as the employee number?
What are the essential fields that must be imported to generate a weekly payroll without having to use the timesheet module? Secondly, can we change the pay and other info in weekly imports?

Posted: Thu Jul 19, 2007 11:01 am
by shaunw
Did you change the wage_type_id to 10 instead of 1? As well did you set the wage_effective_date and parse hint if necessary? Both of those are required to import the wage information.

You can update your CSV file and re-import using the same command I gave you (make sure -u user_name is there) this will tell TimeTrex to only update existing records based on the employees user_name, not insert completely new employees. (unless it can't find the user_name, then it will insert a new record)

On the same note you can add the employee_number column to your CSV file and re-import to set that for all employees. There is no other automated way to do this.
What are the essential fields that must be imported to generate a weekly payroll without having to use the timesheet module?
I'm not sure I understand what you are asking in this question. To generate paystubs you must have some sort of data in the system, either time in their timesheet, or pay stub amendments to tell TimeTrex that they are earning money. TimeSheet data (punches) can be imported with the import_punches.php script.
Secondly, can we change the pay and other info in weekly imports?
As I mentioned above, you can change data with an import assuming you add the -u user_name switch to tell TimeTrex to do so.

Wage data is handled slightly different though, since TimeTrex can store multiple wages and effective dates for each employee (creating a wage history), if you modify an employees wage and the effective date matches that of another wage entry, it will be updated. If the effective date does not match, a new entry will be inserted, which is what you want in most cases.

Posted: Thu Jul 19, 2007 11:32 am
by rons
I had the wage_type_id set to hourly and I changed it to 10 and had the wage_effective_date default to 07/09/2007. I do not know what the parse hint field is. I re-imported the file which updated all the records.
I want to be able to generate the weekly paystubs just from the imported data without having to manually adjust each file imported. Is that possible?

Posted: Thu Jul 19, 2007 12:11 pm
by shaunw
I had the wage_type_id set to hourly and I changed it to 10 and had the wage_effective_date default to 07/09/2007. I do not know what the parse hint field is. I re-imported the file which updated all the records.
Just to clarify, you're saying that it did import the wage data this time around?

The reason for the parse hint is to give TimeTrex a hint as to what the data its parsing is. For example:

07/09/2007

Is it DD/MM/YYYY, or MM/DD/YYYY? TimeTrex doesn't know, so this where the parse hint comes in.
I want to be able to generate the weekly paystubs just from the imported data without having to manually adjust each file imported. Is that possible?
From what I gather your employees are hourly, so how do you plan on telling TimeTrex how many hours to pay them?

If its the same amount of time every week, you can use the auto-pilot scheduler feature and then you don't need to import any data beyond the employee information.

Posted: Thu Jul 19, 2007 12:27 pm
by rons
I thought that was the purpose for the wage and wage_weekly_time fields to tell timetrex to pay the person wage x wage_weekly_time for that week. Our hourly employees wage and weekly time varies each week.

Posted: Thu Jul 19, 2007 1:58 pm
by shaunw
The wage columns define what each employees hourly or salary wage is. The wage_weekly_time is only used for salaried employees so TimeTrex knows how to calculate their hourly wage mainly for job costing situations.

Therefore TimeTrex also needs to know how many hours to pay each employee, (hourly ones anyways) once it has the hours it multiplies that by their hourly wage to get the pay stub earnings amount. This is also where overtime and such comes in, do your employees work overtime at all?

So for hourly employees the wage_weekly_time is not used at all.

Again though, you can use the auto-pilot feature to automatically give employees hours, or use the punch importing script to import hours into TimeTrex for each employee. Both methods should be pretty easy to do.

Posted: Thu Jul 19, 2007 7:48 pm
by rons
Our hourly employees use timesheets that are approved by their supervisor. They work one or more assignments per week with different pay rates and hours. We input their total weekly hours for each project each week in our existing back office system and export this information to our payroll processor.
I want a way to import user and payroll information which would include wage rate and total hours worked per wage rate into Timetrex and then create a weekly pay stub based on that. I don't think that the auto-pilot feature would work since the hours and pay vary. Also, the import punch feature does not provide a way to add weekly totals per wage.
We define overtime as any hours over 40 in a work week and the excess is paid at time and a half.
If this is not possible with the standard timetrex program, I would like to discuss the cost to add this feature for our company and then make it available to the opensource community.

Posted: Thu Jul 19, 2007 8:33 pm
by shaunw
You're correct, the auto-pilot feature while it could work probably isn't the ideal solution for you.

If you aren't entering the hours directly into TimeTrex, the next best thing is to have your current system generate a CSV file that can be imported into TimeTrex as Pay Stub Amendments. This will allow you to make any number of entries per employee each of which can have its own rate and hours.

Keep in mind TimeTrex won't calculate any of its policies if you do it this way, if you want TimeTrex to calculate overtime, premium times, accruals and such, you need to enter the employee time into the timesheet.

Unfortunately we don't have a PS amendment import script yet, so that would have to be custom developed for you. Please contact sales@timetrex.com or call 1-800-714-5153 to discuss.

Posted: Fri Jul 20, 2007 9:16 am
by quepasa
I have been following this thread and preparing my own import files. I have a couple of questions.
WARNING: Clear TimeTrex cache after running this.
Does this mean deleting all files in the cache directory, or is there something we need to run? Should we clear the cache even when using the -n option, or just after a real import?

Also, if I am only interested in attendance, and not interested in wages and taxes, is there anything wrong with removing the defaults for those fields and not defining them in the csv file? I am specifically referring to wage_type_id, wage_weekly_time, federal_income_tax_deduction_id, and province_income_tax_deduction_id.

Thank you,

-Ben

Posted: Fri Jul 20, 2007 10:56 am
by shaunw
Correct, that message means you should clear our your TimeTrex cache directory. It should be done after each run if you other people are using TimeTrex at the same time. Alternatively if you run the script as the same user that your webserver runs as, its a non-issue. What you want to avoid is having cache files owned by root/administrator that the webserver can't update, because that can cause big problems.

Yes, you can either remove the columns altogether, or remove the default values for any columns you do not wish to import. Some are required (like user_name, status and such) but wage and tax columns are not.

Default Vaules

Posted: Tue Dec 04, 2007 9:35 am
by zackf
I see that the value for hourly is 10, is there a manual or way I can see what the other types (eg exempt) are?

I would like to know the possible values because we are getting this all set up for our business!

Posted: Tue Dec 04, 2007 9:46 am
by shaunw
The import script defaults to the hourly wage type unless it sees any of the following (no quotes of course):

"Salary"
"Salaried"
"S"

This refers to an annual salary too of course. "Exempt" isn't a wage type, that is most likely handled in other policies.

Posted: Tue Dec 04, 2007 9:50 am
by zackf
I see, thank you.

Posted: Sun May 25, 2008 6:00 am
by mcube
While importing users i received following errors..

.
.
.
.
Importing User: 64. ................................
Failed!
ERROR: Company is invalid
PHP Notice: Undefined index: company_id in C:\Program Files\TimeTrex\timetrex\
tools\import\import_users.php on line 385
PHP Notice: Undefined index: first_name in C:\Program Files\TimeTrex\timetrex\
tools\import\import_users.php on line 441
PHP Notice: Undefined index: last_name in C:\Program Files\TimeTrex\timetrex\t
ools\import\import_users.php on line 441
PHP Notice: Undefined index: company_id in C:\Program Files\TimeTrex\timetrex\
tools\import\import_users.php on line 496
Update Column Not Found In CSV File!
Importing User: 65. ................................
Failed!
ERROR: Company is invalid
PHP Notice: Undefined index: company_id in C:\Program Files\TimeTrex\timetrex\
tools\import\import_users.php on line 385
PHP Notice: Undefined index: first_name in C:\Program Files\TimeTrex\timetrex\
tools\import\import_users.php on line 441
PHP Notice: Undefined index: last_name in C:\Program Files\TimeTrex\timetrex\t
ools\import\import_users.php on line 441
PHP Notice: Undefined index: company_id in C:\Program Files\TimeTrex\timetrex\
tools\import\import_users.php on line 496
Update Column Not Found In CSV File!
Importing User: 66. ................................
Failed!
ERROR: Company is invalid
Total Errors: 66
Rolling back transaction!
WARNING: Clear TimeTrex cache after running this.

C:\Program Files\TimeTrex\php>
------------------------------------------------------------------------------
Import File CSV

timetrex_column, csv_column, default_value, parse_hint
"company_id","","",""
"status_id","","",""
"currency_id","","",""
"pay_period_schedule_id","","",""
"user_name","","",""
"password","","",""
"employee_number","","",""
"default_branch_id","","",""
"default_department_id","","",""
"first_name","","",""
"last_name","","",""
"sex","","",""
"city","","",""
"country","","",""
"postal_code","","",""
"fax_phone","","",""
"hire_date","","","d/m/y"
---------------------------------------------
Sample data

1,1,1,1,palbishn,password,1,1,001,Bishnu ,Pal,Male,Doha,Qatar,12345,12345678,16/11/05
1,2,1,1,Sugathan ,password,1,1,002,Sugathan ,V.,Male,Doha,Qatar,12345,12345678,02/08/05

Please help..

Posted: Sun May 25, 2008 1:07 pm
by shaunw
Please include the first line (column headers) of your sample data. Your sample data doesn't make much sense to me the way you showed it.

It worked!

Posted: Tue May 27, 2008 11:35 pm
by mcube
Thanks Shawn,

It worked. I assumed that if field headings are matching with Timetrex table fields it will not require headings in csv file - i was wrong.

I had some doubt about id created for records in table so i went to databse and tried to find out related records. For some of the table names are guesable but for others its difficult - Can i find schema of database somewhere?

Manoj.

Posted: Wed May 28, 2008 8:00 am
by shaunw
You can do a SQL dump to get the most up to date schema, or your best bet probably is to use a SQL schema viewer like PgAdmin or PHPMyAdmin