Modified code for ulf->getHighestEmployeeNumberByCompanyI

Discussion for TimeTrex open source community developers.
Locked
lstoumbos
Posts: 80
Joined: Wed Jan 10, 2007 7:41 am

Modified code for ulf->getHighestEmployeeNumberByCompanyI

Post by lstoumbos »

I was having trouble with the next available employee_number. It was returning 9 when I had over 10 employees. So what I did is changed the code in the file UserListFactory.class.php on line 564 from

Code: Select all

$query = 'select * from'. $this->getTable() .' as a where company_id = ?AND employee_number != \'\' AND deleted = 0 ORDER BY employee_number desc LIMIT 1
to

Code: Select all

$query = 'select * from'. $this->getTable() .' as a where company_id = ?AND employee_number != \'\' AND deleted = 0 ORDER BY CAST(employee_number as unsigned) desc LIMIT 1;
The cast allows mysql to select the correct employee_number.
MySQL = 5.0.24a
TimeTrex = 1.4.1
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Post by mikeb »

The proposed fix doesn't work on PostgreSQL. However if you change the entire query to this, it should work better.

Code: Select all

		$ph = array(
					'id' => $id,
					'id2' => $id,
					);

		$query = '
					select 	*
					from	'. $this->getTable() .' as a
					where	company_id = ?
						AND employee_number = ( select max(employee_number)
												from '. $this->getTable() .'
												where company_id = ?
													AND employee_number != \'\'
													AND deleted = 0
												)
						AND deleted = 0
					LIMIT 1
						';
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
lstoumbos
Posts: 80
Joined: Wed Jan 10, 2007 7:41 am

Post by lstoumbos »

Thank you
lstoumbos
Posts: 80
Joined: Wed Jan 10, 2007 7:41 am

Post by lstoumbos »

This bug has returned. I change the code to

Code: Select all

employee_number = ( select max(CAST( employee_number as unsigned))
and that fixed the problem. Although I know this won't work for postgres.

It stopped working at employee 100 it was returning 99 as the highest.
Locked