correct syntax for punch import

General support regarding TimeTrex, such as
configuring policies/taxes or processing payroll.
Locked
lfrazier
Posts: 37
Joined: Wed Aug 12, 2009 8:12 am
Location: Northern Colorado

correct syntax for punch import

Post by lfrazier » Fri Sep 18, 2009 2:00 pm

Hi Shaun,

what is the proper syntax to run the punch import

here is my command:

Code: Select all

cd\xampp\php
php-win.exe "C:\xampp\htdocs\TimeTrex3\tools\import\import_punches.php" -n -u user_name "C:\xampp\htdocs\TimeTrex3\tools\import\punch_column_map.txt" "C:\xampp\htdocs\TimeTrex3\tools\import\punch.csv" 
Thanks,

Lloyd

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Fri Sep 18, 2009 3:08 pm

Use php.exe instead of php-win.exe so you can see output.

Run just import_punches.php with no arguments to see the proper usage help. The -u switch is not supported.

lfrazier
Posts: 37
Joined: Wed Aug 12, 2009 8:12 am
Location: Northern Colorado

Post by lfrazier » Mon Sep 21, 2009 8:03 am

Shaun,

I am still having a time with the punch import. I am not getting data in the database.

here is my mapping file:

Code: Select all

timetrex_column, csv_column, default_value, parse_hint
"user_name","","",""
"type_id","","Normal",""
"status_id","","In",""
"time_stamp","","","m/d/y#g:i A"
Here is my punch file:

Code: Select all

"user_name","type_id","status_id","time_stamp"
"jsmith","","In","9/17/2009 08:00 AM"
"jsmith","","Out","9/17/2009 12:00 PM"
"jsmith","","In","9/17/2009 01:00 PM"
"jsmith","","Out","9/17/2009 06:00 PM"
Here is the command line result with the -n option to show you feed back that indicates it is reading data:

Code: Select all

Column Mappings...
  TimeTrex Column: user_name => user_name Default:
  TimeTrex Column: type_id => type_id Default: Normal
  TimeTrex Column: status_id => status_id Default: In
  TimeTrex Column: time_stamp => time_stamp Default:
Sample Punches...
  Sample Punch: 1
    user_name: jsmith
    type_id: Normal
    status_id: In
    time_stamp: 9/17/2009 08:00 AM
  Sample Punch: 2
    user_name: jsmith
    type_id: Normal
    status_id: Out
    time_stamp: 9/17/2009 12:00 PM
Importing Punches...
  Importing Punch: 1. jsmith........................... 
What am I doing incorrectly?

Lloyd

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Mon Sep 21, 2009 8:25 am

Everything looks fine, what is the exact command you are using to run the import? Are you using the -n switch by chance? If so, make sure you remove it as that is to perform a "dry run" which doesn't actually carry out the import, it just shows you what it will do.

lfrazier
Posts: 37
Joined: Wed Aug 12, 2009 8:12 am
Location: Northern Colorado

Post by lfrazier » Mon Sep 21, 2009 8:45 am

Shaun,

here is my command: php import_punches.php simple_punch_column_map.txt punch.csv

here is the result when running without the -n option:

Code: Select all

Column Mappings...
  TimeTrex Column: user_name => user_name Default: 
  TimeTrex Column: type_id => type_id Default: Normal
  TimeTrex Column: status_id => status_id Default: In
  TimeTrex Column: time_stamp => time_stamp Default: 
Importing Punches...
  Importing Punch: 1. jsmith........................... 
The data does not make it into the database. Is there a log anywhere I can take a look at?

Thanks,

Lloyd

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Mon Sep 21, 2009 10:29 am

Are you pasting the entire output of the script here? If so, the script isn't finishing, so that would explain why you aren't seeing any data in the database.

You will want to check your PHP error log or System Event Log to see if you are getting any PHP errors. You could also try editing the import_punches.php file, and remove "//" from the 2nd last line.

Change:

Code: Select all

//Debug::Display();
To:

Code: Select all

Debug::Display();
Save the file and run your command again.

lfrazier
Posts: 37
Joined: Wed Aug 12, 2009 8:12 am
Location: Northern Colorado

Post by lfrazier » Mon Sep 21, 2009 11:18 am

that was the complete result of script.

I tried the debug and reran, but it was not any more verbose than previously.

I cannot find any log indicating issues.

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Mon Sep 21, 2009 12:37 pm

You may need to enable debugging in your timetrex.ini.php as per the FAQ entry, set the verbosity to 11 and enable debug display.

lfrazier
Posts: 37
Joined: Wed Aug 12, 2009 8:12 am
Location: Northern Colorado

Post by lfrazier » Mon Sep 21, 2009 1:01 pm

This is the result I get after setting Production=FALSE an verbose=11
in timetrex.ini.php

Code: Select all

-----<hr>
(mysqlt): SET SESSION sql_mode='ansi' 
   
-----<hr>
-----<hr>
(mysqlt): SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
   
-----<hr>
-----<hr>
(mysqlt): SET SESSION time_zone='America/Denver' 
   
-----<hr>
Column Mappings...
  TimeTrex Column: user_name => user_name Default: 
  TimeTrex Column: type_id => type_id Default: Normal
  TimeTrex Column: status_id => status_id Default: In
  TimeTrex Column: time_stamp => time_stamp Default: 
Importing Punches...
-----<hr>
(mysqlt): SET AUTOCOMMIT=0 
   
-----<hr>
-----<hr>
(mysqlt): BEGIN 
   
-----<hr>
  Importing Punch: 1. jsmith........................... -----<hr>
(mysqlt): 
					select 	*
					from	users
					where	user_name = 'jsmith'
						AND deleted = 0 
   
-----<hr>
-----<hr>
(mysqlt): 
						select 	*
						from	user_preference
						where	user_id in ('2')
							AND deleted = 0 
   
-----<hr>
		ADOConnection.CacheExecute(86400, select * from branch where id = -1)% line  878, file: C:\xampp\htdocs\TimeTrex3\classes\modules\core\Factory.class.php
	Factory.getEmptyRecordSet()% line  778, file: C:\xampp\htdocs\TimeTrex3\classes\modules\core\Factory.class.php
        Factory.getSortSQL(Array[2])% line  101, file: C:\xampp\htdocs\TimeTrex3\classes\modules\company\BranchListFactory.class.php
        BranchListFactory.getByCompanyId(1)% line  274, file: C:\xampp\htdocs\TimeTrex3\tools\import\import_punches.php
        c:\xampp\tmp\timetrex3\/cf/adodb_cf502049f8c0c35a07b410f91d7d681d.cache reloaded, ttl=14235 [ select * from branch where id = -1 ]
-----<hr>
(mysqlt): 
					select 	*
					from	branch
					where	company_id = '1'
						AND deleted = 0 order by status_id asc,name asc 
   
-----<hr>
		ADOConnection.CacheExecute(86400, select * from department where id = -1)% line  878, file: C:\xampp\htdocs\TimeTrex3\classes\modules\core\Factory.class.php
	Factory.getEmptyRecordSet()% line  778, file: C:\xampp\htdocs\TimeTrex3\classes\modules\core\Factory.class.php
        Factory.getSortSQL(Array[2])% line  101, file: C:\xampp\htdocs\TimeTrex3\classes\modules\department\DepartmentListFactory.class.php
        DepartmentListFactory.getByCompanyId(1)% line  282, file: C:\xampp\htdocs\TimeTrex3\tools\import\import_punches.php
        c:\xampp\tmp\timetrex3\/59/adodb_592e6e89d03117d9d6f81917ca8fe4ca.cache reloaded, ttl=14235 [ select * from department where id = -1 ]
-----<hr>
(mysqlt): 
					select 	*
					from	department
					where	company_id = '1'
					AND deleted = 0 order by status_id asc,name asc 
   
-----<hr>
Fatal error: Call to a member function getTimeStamp() on a non-object in C:\xampp\htdocs\TimeTrex3\classes\modules\punch\PunchControlFactory.class.php on line 216

lfrazier
Posts: 37
Joined: Wed Aug 12, 2009 8:12 am
Location: Northern Colorado

Post by lfrazier » Mon Sep 21, 2009 1:21 pm

Shaun,

I ran this in TimeTrex 2.2.22 and it works just fine

It seems TimeTrex v3 is having the issue.

I hope this exercise was helpful for you in fixing any bugs.

FYI on versions of what I am running:

Code: Select all

PHP 5.2.9
MySQL 5.1.33
Apache 2.2.21

Windows XP Pro svc pak3

lfrazier
Posts: 37
Joined: Wed Aug 12, 2009 8:12 am
Location: Northern Colorado

Post by lfrazier » Mon Sep 21, 2009 1:25 pm

Shaun,

I have a final question related to this thread.

What is the importance of clearing out the cache from the tmp folder after doing a punch import?

Is it something that can be done as an every 24hr thing?

Or, is it necessary to do right after a punch import?

I ask this because I will be automating this process.

Thanks,

Lloyd

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Mon Sep 21, 2009 4:25 pm

I informed our developers and they posted an updated punch import script at the following URL for download:
http://www.timetrex.com/download/import_punches.zip

This fix will be available in TimeTrex v3.0.0-rc2 which is due to be released soon.

As for the importance of clearing the cache directory, this is a precautionary measure because usually when data is imported the import script isn't executed as the same user that the web server runs as, so when this happens it causes a permission conflict with cache files unless the cache directory is cleared out immediately after.

If you execute the import script as the same user that your TimeTrex web server runs as, there is no need to clear the cache directory.

lfrazier
Posts: 37
Joined: Wed Aug 12, 2009 8:12 am
Location: Northern Colorado

Post by lfrazier » Mon Sep 21, 2009 5:02 pm

Ok, Shaun,

I applied the import_punches.php fix.

The import brought the punches in.

Only issue is that it is causing an issue in the timesheet. It indicates missing punches. The Pay Policy only has one simple OT rule and nothing else. So, not sure why this is happening. I did not have this issue with TimeTrex 2 that had the same pay policy setup.

See screenshot: http://labortracking.wordpress.com/file ... rnshot.jpg

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Tue Sep 22, 2009 9:09 am

I'm not able to replicate your results here, what happens if you change the last out punch to 2:00PM instead of 6:00PM does it work?

If it does, then can you post a screenshot of your pay period schedule settings?

Locked