Delicious      Technorati      Blinklist      Furl      Reddit

TimeTrex Time and Attendance

TimeTrex Time and Attendance


* FAQ    * Search
* Login   * Register

All times are UTC - 8 hours [ DST ]




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 28 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Thu Jul 12, 2007 1:19 pm 
Offline

Joined: Fri Jul 06, 2007 11:24 am
Posts: 52
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 12, 2007 2:43 pm 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 12, 2007 7:35 pm 
Offline

Joined: Fri Jul 06, 2007 11:24 am
Posts: 52
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)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 13, 2007 9:43 am 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 13, 2007 10:47 am 
Offline

Joined: Fri Jul 06, 2007 11:24 am
Posts: 52
I need help on how to proceed from here.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 13, 2007 11:54 am 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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:
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:
# 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:
# 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.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 18, 2007 3:09 pm 
Offline

Joined: Fri Jul 06, 2007 11:24 am
Posts: 52
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 18, 2007 4:57 pm 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 19, 2007 10:11 am 
Offline

Joined: Fri Jul 06, 2007 11:24 am
Posts: 52
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?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 19, 2007 11:01 am 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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.

Quote:
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.

Quote:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 19, 2007 11:32 am 
Offline

Joined: Fri Jul 06, 2007 11:24 am
Posts: 52
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?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 19, 2007 12:11 pm 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
Quote:
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.

Quote:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 19, 2007 12:27 pm 
Offline

Joined: Fri Jul 06, 2007 11:24 am
Posts: 52
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 19, 2007 1:58 pm 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 19, 2007 7:48 pm 
Offline

Joined: Fri Jul 06, 2007 11:24 am
Posts: 52
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 19, 2007 8:33 pm 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 20, 2007 9:16 am 
Offline

Joined: Thu Jun 21, 2007 11:16 am
Posts: 58
I have been following this thread and preparing my own import files. I have a couple of questions.

Quote:
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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 20, 2007 10:56 am 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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.


Top
 Profile  
 
 Post subject: Default Vaules
PostPosted: Tue Dec 04, 2007 9:35 am 
Offline

Joined: Fri Nov 16, 2007 12:22 pm
Posts: 12
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!


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 04, 2007 9:46 am 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 04, 2007 9:50 am 
Offline

Joined: Fri Nov 16, 2007 12:22 pm
Posts: 12
I see, thank you.


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 25, 2008 6:00 am 
Offline

Joined: Sun May 25, 2008 5:56 am
Posts: 8
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..


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 25, 2008 1:07 pm 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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.


Top
 Profile  
 
 Post subject: It worked!
PostPosted: Tue May 27, 2008 11:35 pm 
Offline

Joined: Sun May 25, 2008 5:56 am
Posts: 8
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.


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 28, 2008 8:00 am 
Offline

Joined: Tue Sep 19, 2006 2:22 pm
Posts: 6519
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 28 posts ]  Go to page 1, 2  Next

All times are UTC - 8 hours [ DST ]


Who is online

Users browsing this forum: Google [Bot] and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron


Time and Attendance




Powered by phpBB® Forum Software © phpBB Group