TimeTrex Time and Attendance

Posted: **Wed Jan 17, 2007 9:30 am**

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

Posted: **Wed Jan 17, 2007 12:23 pm**

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
						';

Posted: **Wed Jan 17, 2007 1:31 pm**

Thank you

Posted: **Tue Feb 27, 2007 10:20 am**

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.

TimeTrex Time and Attendance

Modified code for ulf->getHighestEmployeeNumberByCompanyI

Modified code for ulf->getHighestEmployeeNumberByCompanyI