Currently MERCI only supports a default set of rules for each content type. In future releases we want to add a different set of rules for each role. The role based rules feature would allow users in a role like "Photography 100" to reserve a digital SLR for only 3 hours, but users in the "Photography 400" would be able to check out the same content type for 24 hours.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

deproduction’s picture

Title: Role based rules allow override of MERCI defaults » Check availability of a resource
Priority: Normal » Critical

A very high percentage of my use of our equipment reservation system is to answer the question "when is Studio A available". At present, I have no way of answering that question. I say "well, when do you want it?" and check that time. If it is not available at that time, they ask, "well, when would it be available?" and I have no way of knowing.
Producers tend to use this tool when away from the facility, and we get emails and phone calls saying "it told me there was a conflict with X resource, but i have no way of knowing why, how to resolve that conflict." There is no information presented, and the only option for the user is to try again and again. Last week, one producer "Andrew Skeels" said he tried 5 different times to reserve an edit station in our lab at various different hours, and was not notified until he called that all edit stations were reserved all that day. I asked him to "report a problem" and we would get back to him.

My thought is that the best solution for this problem would be a graphical display similar to ORS, but any other solution you have would be an improvement.

deproduction’s picture

RajeshMca’s picture

Assigned: Unassigned » RajeshMca
Priority: Critical » Normal
darrick’s picture

I've setup a calendar view which can filter by date and user for reservations. So going to the path users/user-id/reservations/optional-date will pull up all reseravations for user-id.

It would be nice to have a views field for the booked items of a reservation node (although I suppose one could pull this info up with a overridden theme function.)

Also would be nice to to have a views filter the content type of the booked items for a node. That way you could have an exposed filter for the reserved items and then start filtering all the reservations based upon what items are reserved.

darrick’s picture

FileSize
10.34 KB
1.36 KB

So I've added a patch for merci.views.inc which adds the views filter for the content type of a reservation nodes booked items.

I'm new at views, so maybe there is a better way to do this. Like via relationships or some sub-view. But this code gives me what I want for now. I've also attached an export of my view.

darrick’s picture

I felt maybe I was doing this backwards. So I created a calendar view filtering for reservation items and hacked in the code to get the date these items are tied to. This gave a calendar view of the availability of resources but then for bucket items I can only get a view of each bucket item but not a calendar view of whether there are no bucket items available at all during a certain time period.

I found this link helpful to accomplish that: http://drupal.org/node/320596

So, I'm now staring at the merci_get_available_bucket_count and merci_build_reservable_items functions and am not sure how to implement that as views arguments or filters.

What I'd like to end up doing is overriding the calendar theme functions so a user can browse to the calendar page, filter on the reserveable item and then click on an empty space where the item is shown to be available, which would bring up the add reservation page with the datetime and item pre-filled in. At least that is how phpScheduleIt does it. Which is what our access center is currently using.

kreynen’s picture

Assigned: RajeshMca » Unassigned

I was off for a few days or I would have responded sooner. I'll take a look at the patch. We have a few Views and Blocks to roll in as well. I posted some descriptions and screenshots here...

http://groups.drupal.org/node/19669#comment-68067

This is approach we've been working on to deal with buckets. This was written as a stand alone php file so that we could test the performance of the query. With multiple items a bucket, multiple buckets in a Reservation, and Reservations spanning multiple days we want to be sure this isn't going to make the system unusable for checkouts with larger inventories.

<?
	
	// ! Database connection
	
	$_DB = mysql_connect( 'localhost' , 'dom' , 'PASSWORD' );
	mysql_select_db( 'dom_drupal' );
	
	// ! Variables you might change
	
	$start_date = '2009-02-09';					// MySQL date format string
	$end_date = '2009-02-16'; 					// MySQL date format string
	$type = 'canon_xl1_dv_camera';				// Content type string
	$ignore_merci_statuses = array( 3 , 4 );	// Array of status integers

	// ! Initialize reservation counts
	
	$reservation_counts = array();

	$datetime = strtotime( $start_date . ' 00:00:00' );
	
	while ( date( 'Y-m-d' , $datetime ) < $end_date )
	{
	
		$formatted_date = date( 'Y-m-d H:i:s' , $datetime );
		$reservation_counts[ $formatted_date ] = 0;

		$datetime = strtotime( $formatted_date . ' +15 minutes' );
	
	} // while

	// ! Get reservation times from database

	$reservation_times = array();
	
	$sql = "SELECT r.field_merci_date_value AS start, r.field_merci_date_value2 AS end
	FROM node n
	JOIN merci_reservation m
		ON m.vid = n.nid
	JOIN content_type_merci_reservation r
		ON r.vid = n.nid
	WHERE n.type = '" . addslashes( $type ) . "'
	AND n.status = 0
	AND m.status NOT IN ( " . implode( ' , ' , $ignore_merci_statuses ) . " )
	AND r.field_merci_date_value < '" . $end_date . "'
	AND r.field_merci_date_value2 >= '" . $start_date . "'";

	$result = mysql_query( $sql , $_DB );

	if ( mysql_num_rows( $result ) > 0 )
	{

		while ( $reservation_time = mysql_fetch_object( $result ) )
			$reservation_times[] = $reservation_time;

	} // if	
	
	// ! Update reservation counts
	
	foreach ( $reservation_times as $reservation_time )
	{
	
		$time = $reservation_time -> start;
			
		while ( $time < $reservation_time -> end )
		{
		
			if ( isset( $reservation_counts[ $time ] ) )
				$reservation_counts[ $time ]++;
				
			$time = date( 'Y-m-d H:i:s' , strtotime( $time . ' +15 minutes' ) );
		
		} // while
	
	} // foreach
	
	echo( '<pre><b>reservations:</b>' . "\n\n" );
	print_r( $reservation_times );
	echo( "\n" . '<b>result:</b>' . "\n\n" );
	print_r( $reservation_counts );
	echo( '</pre>' );

?>

That just kicks out an array. The plan is to format that across the top of the Reservation showing green where there are several items left in the bucket, orange when there a only a few left, and red where the conflict is for each item for each day.

                2          3         4          5         6        7        8        9          10
DV Camera 3/3 - [g][g][g][g][g][o][o][o][o][r][r][r][o][g][g][g][g][g][g][g][g][g][g][o][r][o][r][g]
DV Camera 3/4 - [g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][o][r][o][g][g][g][g][g][g][g][g]
Light Kit 3/3 - [g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][o][r][o][g][g][g][g][g]
Light Kit 3/4 - [g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][g][o][r][o][g][g][g][g][g]
darrick’s picture

Well this is where I've ended up. I think for representing buckets in the template_preprocess functions you could first get the quantity of bucket items for a bucket then loop over the bucket items and build a array of start and end dates when all the items are reserved. i.e

$no_bucket_items = get_total_bucket_items($bucket_type); 

foreach( $bucket_items as $item){

// Place item on an array

// remove old item from array if new items start is greater or equal to old items end.

// If the array == $no_bucket_items then create add bucket blackout start to blackout array.

// If the array decrease from $no_bucket_items to $no_bucket_items -1 then update bucket blackout end and push on to blackout list array.

}

return $bucket_blackout_list

Then override the calendar view day theme.

I don't mind working on this if you feel I'm headed in the right direction. And thanks for the shout out on the groups list.

darrick’s picture

So I took Kevin's idea and mashed it together with mine. I had to change the SQL query in order to get all the items. But this returns an array of times and counts the items are not available.

<?

// ! Database connection


$_DB = mysql_connect( 'localhost' , USER , PASSWORD );
mysql_select_db( DATABASE );

// // ! Variables you might change
// Views definition.

$start_date = '2009-02-09'; // MySQL date format string
$end_date = '2009-03-20'; // MySQL date format string
$type = 'dv_camera'; // Content type string
$ignore_merci_statuses = array( 3 , 4 ); // Array of status integers

$sql = "SELECT UNIX_TIMESTAMP(r.field_merci_date_value) AS start, UNIX_TIMESTAMP(r.field_merci_date_value2) AS end
        FROM node n
        LEFT JOIN merci_reservation_detail m
        ON m.placeholder_nid = n.nid
        inner JOIN content_type_merci_reservation r
        ON r.vid = m.vid
        WHERE n.type = '" . addslashes( $type ) . "'
        AND n.status = 1 
        AND r.field_merci_date_value >= '" . $start_date . "'
        AND r.field_merci_date_value2 < '" . $end_date . "'";

$result = mysql_query( $sql , $_DB );

// Override template preprocess here.

$counts = array();
if ( mysql_num_rows( $result ) > 0 )
{

        // Loop over each reserved item.
        while ( $item = mysql_fetch_object( $result ) ){
                $return_counts = array();
                // Loop over the reservation count blocks.
                foreach ($counts as $count){
                        // Is this item in this count block.
                        if($item->start >= $count['start']
                                && $item->start < $count['end']){
                                        $count['count']++;
                                        // Split front of block.
                                        if($item->start > $count['start']){
                                                $new_count = $count;
                                                $count['start'] = $item->start;
                                                $new_count['end'] = $item->start;
                                                $new_count['count'] = $count['count']-1;
                                                array_push($return_counts, $new_count);

                                        }
                                        // Split back of block.
                                        if($item->end < $count['end']){
                                                $new_count2 = $count;
                                                $count['end'] = $item->end;
                                                $new_count2['start'] = $item->end;
                                                $new_count2['count'] = $count['count']-1;
                                                array_push($return_counts, $new_count2);
                                        }
                                        $found = true;
                                }
                        array_push($return_counts, $count);
                }
                // Didn't find a block this reservation is in so create a new one.
                if(!isset($found)){
                        $new_count = array('start'=>$item->start,
                                'end'=>$item->end,
                                'count'=>1,
                        );
                        array_push($return_counts, $new_count);
                }
                $counts = $return_counts;
        }
}
$reservation_counts = $counts;

// Theme the result.
echo( "\n" . '<b>result:</b>' . "\n\n" );
print_r( $counts );
echo( '</pre>' );

kreynen’s picture

FileSize
52.88 KB

This is running on Denver Open Media. If we don't see any performance issue, I'll commit this to CVS.

kreynen’s picture

Status: Active » Fixed

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.