Query users table?

Discussion for TimeTrex open source community developers.
Locked
ruraldev
Posts: 14
Joined: Mon Aug 06, 2012 11:11 pm

Query users table?

Post by ruraldev »

I am trying to query the users table but not having much luck, I have extracted the query element from the full page just for testing purposes but it still returns NULL

I am not used to Postgresql so perhaps I am doing something wrong, can anyone help please. There is 1 user in the users table.

Thanks

Gordon

Code: Select all

<?php 

$conn = pg_pconnect ("host=localhost port=5433 dbname=timetrex user=timetrex password=xxxxxxx"); 
if (!$conn) { 
    echo "An error occured.\n"; 
    exit; 
} 

$result = pg_query ($conn, "SELECT * FROM users"); 
if (!$result) { 
    echo "An error occured.\n"; 
    exit; 
} 

$arr = pg_fetch_all ($result, 0, PGSQL_NUM); 

var_dump($arr); 

?>
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Query users table?

Post by shaunw »

Why are you trying to query the users table directly instead of using the supplied API?
ruraldev
Posts: 14
Joined: Mon Aug 06, 2012 11:11 pm

Re: Query users table?

Post by ruraldev »

Good point!

I have been playing with it and I think I have the wrong $TIMETREX_URL value, I changed it without copying the file so can't see the original value anymore.

I am on a windows PC and the url to reach Timetrex is http://localhost:8085/interface

Is this the value which should go into $TIMETREX_URL =

I am using it to query my local Timetrex using id of 1 only as this is the only user at the moment for testing

User and Password is correct, I just get a blank screen

Code: Select all

<?php
require_once('../classes/modules/api/client/TimeTrexClientAPI.class.php');

/*
 Global variables
*/
$TIMETREX_URL = 'http://localhost:8085/interface';
$TIMETREX_USERNAME = 'user';
$TIMETREX_PASSWORD = 'pass';

$api_session = new TimeTrexClientAPI();
$api_session->Login( $TIMETREX_USERNAME, $TIMETREX_PASSWORD );
if ( $TIMETREX_SESSION_ID == FALSE ) {
	echo "Login Failed!<br>\n";
	exit;
}
echo "Session ID: $TIMETREX_SESSION_ID<br>\n";

//
//Get data for two employees by primary key/ID.
// - Many other filter methods can be used, such as branch, department, user_name, province, state, etc...
//
$user_obj = new TimeTrexClientAPI( 'User' );
$result = $user_obj->getUser(
									array('filter_data' => 	array(
																'id' => array(1)
															)
									)
								);

$user_data = $result->getResult();
print $result;

/* //Example returned data: )
Array
(
    [0] => Array
        (
            [id] => 1023
            [company_id] => 1001
            [status_id] => 10
            [status] => Active
            [group_id] => 0
            [group] =>
            [user_name] => demoadmin1
            [phone_id] => 12345
            [phone_password] => 12345
            [employee_number] => 10
            [title_id] => 0
            [title] =>
            [default_branch_id] => 0
            [default_branch] =>
            [default_department_id] => 1
            [default_department] => Administration
            [permission_control_id] => 7
            [permission_control] => Administrator (92) #1
            [pay_period_schedule_id] => 260
            [pay_period_schedule] => USBiWeekly
            [policy_group_id] => 9
            [policy_group] => Default
            [first_name] => Demo
            [middle_name] => J
            [last_name] => Admin
            [second_last_name] =>
            [sex_id] => 10
            [sex] => MALE
            [address1] => Blah
            [address2] =>
            [city] => Kelowna
            [country] => CA
            [province] => BC
            [postal_code] => V4T1E3
            [work_phone] => 555-555-5555
            [work_phone_ext] =>
            [home_phone] => 555-555-5555
            [mobile_phone] => 555-555-5555
            [fax_phone] =>
            [home_email] => test@democo.com
            [work_email] => test@democo.com
            [birth_date] => 07-Nov-04
            [hire_date] => 07-Nov-04
            [termination_date] =>
            [currency_id] => 3
            [currency] => CAD
            [sin] => 1XXXXX789
            [note] =>
            [deleted] =>
            [created_by_id] => 1
            [created_by] =>
            [created_date] => 08-Nov-04 11:18 AM
            [updated_by_id] => 1023
            [updated_by] => Demo Admin
            [updated_date] => 01-Oct-08 12:49 PM
        )

    [1] => Array
        (
            [id] => 11353
            [company_id] => 1001
            [status_id] => 10
            [status] => Active
            [group_id] => 0
            [group] =>
            [user_name] => payroll
            [phone_id] =>
            [phone_password] =>
            [employee_number] => 16
            [title_id] => 0
            [title] =>
            [default_branch_id] => 297
            [default_branch] => Mississauga
            [default_department_id] => 0
            [default_department] =>
            [permission_control_id] => 7
            [permission_control] => Administrator (92) #1
            [pay_period_schedule_id] => 260
            [pay_period_schedule] => USBiWeekly
            [policy_group_id] => 9
            [policy_group] => Default
            [first_name] => Mrs. Payroll
            [middle_name] =>
            [last_name] => Admin
            [second_last_name] =>
            [sex_id] => 10
            [sex] => MALE
            [address1] =>
            [address2] =>
            [city] =>
            [country] => CA
            [province] => ON
            [postal_code] =>
            [work_phone] =>
            [work_phone_ext] =>
            [home_phone] =>
            [mobile_phone] =>
            [fax_phone] =>
            [home_email] =>
            [work_email] =>
            [birth_date] => 13-Aug-09
            [hire_date] => 01-Aug-08
            [termination_date] =>
            [currency_id] => 3
            [currency] => CAD
            [sin] =>
            [note] =>
            [deleted] =>
            [created_by_id] => 1023
            [created_by] => Demo Admin
            [created_date] => 13-Aug-09 8:09 PM
            [updated_by_id] => 1023
            [updated_by] => Demo Admin
            [updated_date] => 13-Aug-09 8:09 PM
        )
)
*/

//
//Update data for the second employee, mark their status as Terminated and update Termination Date
//
$user_data[1]['status_id'] = 20; //Terminated
$user_data[1]['termination_date'] = '01-Jul-09';

$result = $user_obj->setUser( $user_data[1] );
if ( $result->isValid() === TRUE ) {
	echo "Employee data saved successfully.<br>\n";
} else {
	echo "Employee save failed.<br>\n";
	print $result; //Show error messages
}

//
//Update employee record in a single operation. Several records can be updated in a single operation as well.
//
$user_data = array(
				   'id' => 11353,
				   'termination_date' => '02-Jul-09'
				   );

$result = $user_obj->setUser( $user_data );
if ( $result->isValid() === TRUE ) {
	echo "Employee data saved successfully.<br>\n";
} else {
	echo "Employee save failed.<br>\n";
	print $result; //Show error messages
}

//
//Add new employee, several new employees can be added in a single operation as well.
//
$user_data = array(
					'status_id' => 10, //Active
					'first_name' => 'Michael',
					'last_name' => 'Jackson',
					'employee_number' => 239842,
					'user_name' => 'mjackson',
					'password' => 'whiteglove123',
					'hire_date' => '01-Oct-09'
					);

$result = $user_obj->setUser( $user_data );
if ( $result->isValid() === TRUE ) {
	echo "Employee added successfully.<br>\n";
	$insert_id = $result->getResult(); //Get employees new ID on success.
} else {
	echo "Employee save failed.<br>\n";
	print $result; //Show error messages
}

//
//Get TimeSheet Summary report data in raw PHP native array format. 'csv' and 'pdf' are also valid formats.
//
$report_obj = new TimeTrexClientAPI( 'TimesheetSummaryReport' );
$config = $report_obj->getTemplate( 'by_employee+regular+overtime+premium+absence' )->getResult();
$result = $report_obj->getTimesheetSummaryReport( $config, 'raw' );
echo "Report Data: <br>\n";
print $result;


//
//Switch to a different user without having to logout/login.
//This only works if the currently logged in user has proper permissions to carryout this action.
//
$auth_obj = new TimeTrexClientAPI( 'Authentication' );
$result = $auth_obj->getCurrentUser();
print $result;

$auth_obj->switchUser( 'john.doe' ); //UserName or UserID acceptable.
$result = $auth_obj->getCurrentUser();
print $result;

?>
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Query users table?

Post by shaunw »

That file is just an example, you should make a copy of it and remove any code that you do not want to run. If you still receive a blank screen check your PHP error logs or Apache error log for more details, as its likely a PHP error.
ruraldev
Posts: 14
Joined: Mon Aug 06, 2012 11:11 pm

Re: Query users table?

Post by ruraldev »

Is the url correct?

If I know the file content is correct then I will work on the rest, I have removed everything after printing array for testing.

Thanks

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

Re: Query users table?

Post by shaunw »

The URL should be something like this:

http://localhost:8085/api/soap/api.php
ruraldev
Posts: 14
Joined: Mon Aug 06, 2012 11:11 pm

Re: Query users table?

Post by ruraldev »

I assume I also need to change
class TimeTrexClientAPI {
protected $url = 'http://localhost:8085/api/soap/api.php';
protected $session_id = NULL;
protected $class_factory = NULL;
protected $namespace = 'urn:api';
protected $protocol_version = 1;
in TimeTrexClientAPI.class.php

Is that correct?
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Query users table?

Post by shaunw »

No, you shouldn't need to make any changes to the TimeTrexClientAPI.class.php file.
ruraldev
Posts: 14
Joined: Mon Aug 06, 2012 11:11 pm

Re: Query users table?

Post by ruraldev »

I installed it on my mac and now I am getting somewhere!

Error log shows

[Thu Aug 09 18:44:33 2012] [error] [client ::1] PHP Warning: require_once(../classes/modules/api/client/TimeTrexClientAPI.class.php) [<a href='function.require-once'>function.require-once</a>]: failed to open stream: No such file or directory in /Applications/TimeTrex/timetrex/api/soap/api_client.php on line 2
[Thu Aug 09 18:44:33 2012] [error] [client ::1] PHP Fatal error: require_once() [<a href='function.require'>function.require</a>]: Failed opening required '../classes/modules/api/client/TimeTrexClientAPI.class.php' (include_path='.:/Applications/TimeTrex/php/lib/php') in /Applications/TimeTrex/timetrex/api/soap/api_client.php on line 2


Quick edit so that it can find the file and now I get

Session ID: a3c452376d56c1e2fd07e5ee9c7812cb
=====================================
Function: getUser()
Args: Array
-------------------------------------
Returned:
IsValid: YES
Return Value: Array
=====================================

Not what I was expecting!
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Query users table?

Post by shaunw »

Looks like its working to me, its just displaying "array" instead of the actual data contained within the array, which is likely what you want to see. If you put something like print_r($user_data); near the end of the script, it will display the contents of the array for you.
ruraldev
Posts: 14
Joined: Mon Aug 06, 2012 11:11 pm

Re: Query users table?

Post by ruraldev »

Thanks Shaun.

I am getting there, now I have a form for the user to login and select punch in or out

User data in a session variable

It would appear the last page will receive the data and make the punch, my next job!

Gordon
ruraldev
Posts: 14
Joined: Mon Aug 06, 2012 11:11 pm

Re: Query users table?

Post by ruraldev »

I had everything working so I thought I better sort out the others errors I had which were the cache was not writable and the cron wasn't running.

When I sorted the cache and added some new employees for some reason I broke the login, could it be related?

User and pass is correct as I log into the program with the same credentials?

Could the cron not working prevent the login? (I am having real problems with the cron setup!)

Code: Select all

 Global variables
*/
$TIMETREX_URL = 'http://localhost/timetrex/api/soap/api.php';
$TIMETREX_USERNAME = 'user';
$TIMETREX_PASSWORD = 'pass';

$api_session = new TimeTrexClientAPI();
$api_session->Login( $TIMETREX_USERNAME, $TIMETREX_PASSWORD );
if ( $TIMETREX_SESSION_ID == FALSE ) {
	echo "Login Failed!<br>\n";
	exit;
}
ruraldev
Posts: 14
Joined: Mon Aug 06, 2012 11:11 pm

Re: Query users table?

Post by ruraldev »

OK, got everything working, however for some weird reason when I disable Debug i get the login error.

Cron is working fine now and I have enabled debug again so that it all works.

Why would debug settings affect the api login?
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Query users table?

Post by shaunw »

Enabling it could prevent the login from working if you use verbosity level 11. But disabling it shouldn't cause any problems that I'm aware of.
Locked