Getting requests to show on the Schedule and other problems

Discussion for TimeTrex open source community developers.
Locked
jreade
Posts: 27
Joined: Wed Dec 17, 2008 1:42 pm

Getting requests to show on the Schedule and other problems

Post by jreade »

Basically the requests in my self-customized timetrex implementation and environment are requests for specific days off. So the way it would work is an employee might look at the schedule decide they can't work a specific day(s) in the future and then submit a request to have that day(s) off. Now for ease of the administrator, I would like to have the day range of the pending requests show up on the schedule by say making some notation (a background color change or something) on those dates. This would help the administrator be able to determine the trouble spots on the schedule just by looking at it. And when no more notations (trouble spots) exist, they would know that the schedule is suitable.

In order to achieve what I described above, I had to add in another field in the user_date table which I called date_stamp_end. Basically the date_stamp field is being used as the start date of the request and the date_stamp_end is being used as the end date. I added the required functions into UserDateFactory.class.php and the ability to record an end date of request into EditRequest.php and EditRequest.tpl.

Everything seems to work fine. When a request is made a start and end time get added to the user_date table for the user_date Id that is referenced in the request table. Great! Till I go to delete a request and realize that it only deletes (puts a 1 in the deleted column) out of the request table and doesn't do so for the related record in the user_date table. I went looking through the code and found this in UserRequestList.php :

foreach ($ids as $id) {
$rlf->getByIdAndCompanyId( $id, $current_company->getId() );
foreach ($rlf as $r_obj) {
$r_obj->setDeleted($delete);
$r_obj->Save();
}
}

That takes care of the deleting from the request table but doesn't touch the user_date table. So I added this to RequestListFactory.class.php:

function getMyUserDateID($id) {
if ( $id == '') {
return FALSE;
}
$ph = array(
'id' => $id,
);
$query = '
select user_date_id
from '. $this->getTable() .'
where id = ? AND deleted = 0';
$this->rs = $this->db->Execute($query, $ph);
return $this;
}

Then I edited UserRequestList.php :

foreach ($ids as $id) {
$rlf->getByIdAndCompanyId( $id, $current_company->getId() );
foreach ($rlf as $r_obj) {
$udlf = new UserDateListFactory();
$udlf->getById( $rlf->getMyUserDateID($id), NULL, NULL );
foreach ($udlf as $u_obj) {
$u_obj->setDeleted($delete);
$u_obj->Save();
}
$r_obj->setDeleted($delete);
$r_obj->Save();
}
}

However this doesn't work. The idea was to get the user_date_id from the request table and then find that record in the user_date table and set the record to deleted.

If some one could tell me where I went wrong that would be greatly appreciated.

Also I am looking for a way to look at all the records in the user_date table that have requests (that have not been deleted) related to them and pull out the date_stamp (start date) and date_stamp_end (end date). Then I would put a notation on the schedule for all the dates that have request pending on them.

Thank you so much for reading this small book I have written and all your help with this endeavor. And I would be happy to give you any code that I written if you would like to add this feature to your product.
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Post by mikeb »

Using the user_date table is definitely the wrong approach for this type of customization. The way you have used it is not what this table is designed for.

What you are trying to do is actually quite a lot of work to do properly.

Showing just days that employees want off isn't always that useful either, especially if you have employees that work multiple shifts. If an evening shift employee requests time off on the same day as a morning shift employee, if you looked at just the days it would essentially show a conflict (trouble spots) that multiple employees want the day off, but they don't actually conflict with each other because their shifts probably don't overlap at all.

Ignoring that for a minute, here is the approach you should take:

1. Make the current schedule factory statuses types, and copy the status fields from the Request factory to the schedule factory. This will allow scheduled shifts to be in "pending authorization" and "active" statuses.

2. Add a "request_id" field to the schedule table as well, so you know which request is assigned to which pending scheduled shifts.

3. Add functionality to the Request page so employees can actually specify start/end dates and times when submitting a schedule request. These start/end dates would simply be used to create the above mentioned "pending" scheduled shifts. For backwards compatibility, the "start" date specified would actually be the current "date" that is specified on the request.

4. When the request is authorized or declined, it would clear out the attached pending scheduled shifts and allow the supervisor to make the necessary changes.

If you really want to get in-depth, you could have employees specify the start/end date, along with the times, and even the schedule status (working/absence). Assuming they provide enough information when the supervisor authorizes the request the schedule can automatically be updated without any further intervention from the supervisor. The problem with this approach is that the employee needs to know how all the policies work in order to submit the request properly.

This is just a very basic overview of a "quick & dirty" implementation of the mentioned feature.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
jreade
Posts: 27
Joined: Wed Dec 17, 2008 1:42 pm

Post by jreade »

Thank you very much!

The shifts thing is not a problem in my case. I'm making this customized timetrex for a doctor's office and the only thing it is really being used for is to do scheduling as to who is working which days. All the payroll stuff is not being used and is hidden from the user. Also there is no time stuff just date. Dr. Smith is on call today, Dr. Jones in the OR, Dr. Doe is in the office, etc. It is also only for the doctor's not appointments or anything like that. Having more than one person have a request on a day doesn't mean anything as far as displaying the day as a "trouble spot", as long as a request exists on that day it is displayed on as a trouble spot till there are no requests on that day.

But anyways, I'm gonna get started on this and I'll let you know how it comes out.

Just out of curiosity... what is the user_date table designed for?
jreade
Posts: 27
Joined: Wed Dec 17, 2008 1:42 pm

Post by jreade »

I'm having trouble getting EditRequest.php to submit a request and a new schedule shift. I think it might be a problem having two "data = array ( ..."

Are there any pointers you can give me?
jreade
Posts: 27
Joined: Wed Dec 17, 2008 1:42 pm

Post by jreade »

Also how do I get the request ID into the schedule table?
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Post by mikeb »

You will need to be more specific, I'm not sure what you are asking.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
jreade
Posts: 27
Joined: Wed Dec 17, 2008 1:42 pm

Post by jreade »

I've put the following code into the EditRequest.php:

$sf = new ScheduleFactory();

$fail_transaction = FALSE;
$sf->StartTransaction();

$sf->setID( '' );

$sf->setUserDateId( UserDateFactory::findOrInsertUserDate($data['user_id'], $date_stamp) );
$sf->setStatus( 30 );
$sf->setRequestID( $request_id ); //This is my own function written in ScheduleFactory.class.php

$sf->setStartTime( $start_time );
$sf->setEndTime( $end_time );

if ( $sf->isValid() ) {
$sf->setEnableReCalculateDay(TRUE);
$sf->Save();
} else {
$fail_transaction = TRUE;
}

if ( $fail_transaction == FALSE ) {
$sf->CommitTransaction();
} else {
$sf->FailTransaction();
}

Thinking that that would allow me to add a new record to the schedule table with the user_date_id, status_id, request_id, start_time, and end_time. I figured that would be all I needed.

I also added this:

if ( isset($data) ) {
$data['date_stamp'] = TTDate::parseDateTime($data['date_stamp']);
$data['date_stamp_end'] = TTDate::parseDateTime($data['date_stamp_end']);

if ( $data['start_time'] != '') {
$start_time = strtotime( $data['start_time'], $date_stamp ) ;
}
else {
$start_time = NULL;
}

if ( $data['end_time'] != '') {
$end_time = strtotime( $data['end_time'], $date_stamp_end ) ;
}
else {
$end_time = NULL;
}
}

before the new RequestFactory is called at the top of EditRequest.php


However, this only adds new records to the request table and userdate, but not the schedule table. Is there something that jumps out at you that I'm missing?

Thank you so much for all your help!
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Post by mikeb »

At the top of EditRequest.php, uncomment this line:

Code: Select all

//Debug::setVerbosity(11);
That will enable full debugging, and show you all debug text as well as SQL queries being run. You can then look to see if there are any insert SQL queries being run on the schedule table. It may be that the scheduled shift is being inserted, just for the wrong date.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
Locked