date overlap warning

Hi you all.

I would like to insist on that feature and atract more attention to it. This requested feature would allow to easily create reservation systems since reservations usually work on a date range where they admit only 1 entry within the date range, or a limited number of entries.

There are many many examples for that: small resorts with just 4 rooms, conferences with just 100 atendees, restaurants with 12 tables, trainers and consultants with a calendar to admit requests. CCK, Date and Calendar are enough for this situations despite somewhere else on drupal groups there is a group discussing reservation APIs for drupal.

As I see it is not so difficult, it's something that could done by CCK through the date widget at validation(-hook?) time. Date module (Date CCK widget) could have its own validation fielset with some conditions. I subscribe all the states that JohnG propose plus the same with a threshold limit (for allowing overlapping dates until you reach the limit (12, for example)), and also the granularity to consider which overlapping period (by hours, or days, or ...).

I've changed the version status to 5.x-2.x-dev since I think that it's something that complements very nicely that module with a lot of projection on other areas. Any chance to see any advance on that area?

In any case, thanks for date and calendar as they are right now :-)

Hi you all.

I've tried to have a look at the code to see where should the validation code to be added in order to include the "date overlap warning" proposed by JohnG. I'm not a proficient coder, just good enough to have a look at code and see where to make (small) changes if I see I could do it alone ;-)

On date_elements.inc I've seen some functions involved on data validation. Specially functions _date_field_validate at line 38 and date_combo_validate($element) at line 418 have finnished verifying that starting and ending date are correct and on the proper format. Would that be the place to add some function to look at the table if the entered date range is free or not?

Would it be correct something like?:

$query = "SELECT nid FROM {content_type_ournodetype}
            WHERE (%d >= field_ournodetype_date_value AND %d < field_ournodetype_date_value2)
              OR (%d > field_ournodetype_date_value AND %d <= field_ournodetype_date_value2)
              OR (%d <= field_ournodetype_date_value AND %d >= field_ournodetype_date_value2)";
$result = db_query($query, $item['value'], $item['value'], $item['value2'], $item['value2'], $item['value'], $item['value2']);

If it's correct, which would be the best place for that code? Which is the proper way to call what I've written as "content_type_ournodetype" inside that CCK module?

Thanks!

That checks for a conflicting date, but remember you can have more than one resource being reserved. You might have room1 and room2 used from 10am-12pm on the same day for example.

I posted some sql code that checks for a conflict below. I'm thinking of using workflow_ng to check for conflicts when a reservation is submitted. Workflow_ng lets you run your own php snippet when a content type is submitted.

Two things I haven't worked on yet are repeating dates and when you reserve more than one resource at a time.

http://groups.drupal.org/node/11084#comment-43947

Just to list other alternatives that might be better for you to use unless you want to code your own solution like I am trying:

-resource_conflict module - works with event module, not date/calendar modules

-bookings api module - drupal 6 only and still very early in development

Hi Doug and thanks for your proposals.

I know that what I'm asking is still basic, but I don't know how to face that step with date.module and that's why I've tried to start somewhere. Many validations could be added but the one that JohnG asks and I try to find a way to solve is my first step, and I sincerely believe that a very logical addition to the date module. Only with that we could help us to build reservations systems, very basic, maybe, but it could be a first step to keep growing to a more complete solution.

In fact what I'm trying is exactly the same that you describe on the url you post from Bookings API. I've drove my solution the same way except I've used taxonomy for marking which room is reserved. Your code is very complete but I'm afraid that instead of Workflow NG maybe it could be better to use CCK Validation module (drupal.org/project/cck_validation), which it just lets you add a new field on your content type where writing custom validations. Did you know about that module?

In any case, wouldn't be better that the wonderful and growing date.module v.2 would gave us that feature itself? :-D

Thanks for the tip about cck_validation. I also think it would be nice to have this feature in the date module, but I went ahead and got it working for me using the cck_validation module. Below is my php code. I tried to make it easily customizable but it still isn't perfect.

I'll still be using workflow_ng for things like emailing when a reservation is submitted.

To summarize the way I set this up, I have a content type called 'resource' and a content type called 'reservation'.

In the reservation content type I have a node reference field ( field_reservationresource, multiple values allowed) and a date field (field_reservationdatetime, repeating dates allowed), and also some other fields (name & email of person making reservation, etc.). Then I have a cck_validation field with the php code below. Set the process validation to 'field' instead of 'widget'.

I have a calendar view (using latest dev releases of calendar and date) showing reservations, and an exposed filter for filtering the calendar by resource (node reference field).

Here is the cck_validation field code:

//debug
//drupal_set_message('<pre>$node: '.print_r($node,TRUE).'</pre>');

//MODIFY next 4 variables to match your fields and preferences:
$reservation_type = "reservation";
$date_field = "reservationdatetime";
$resource_field = "reservationresource";
//message to show if a conflict is found, after conflicts are listed:
$helpwithconflict = 'View the <a href="/resources/calendar" target="cal">resource calendar</a> to see other reservations in a calendar view.';

$conflicts_detected = array();

$resourceids = array();

//MODIFY reservationresource on next line  ($node is readonly)
foreach ($node->field_reservationresource as $res) {
	$resourceids[] = $res['nid'];
}
$resources = implode(', ', $resourceids); 

$ignoreself = ($node->nid)? " AND reserve.nid != {$node->nid}" : '';

//MODIFY reservationdatetime on next line ($node is readonly)
foreach ($node->field_reservationdatetime as $rkey => $reservedate)  {
	$date_start = $reservedate['value'];
	$date_end = $reservedate['value2'];

	$sql = "SELECT reserve.nid, 
	d.field_{$date_field}_value as d1, d.field_{$date_field}_value2 as d2
	FROM {content_type_$reservation_type} reserve
	LEFT JOIN {content_field_$date_field} d ON 
		reserve.nid=d.nid
	LEFT JOIN {content_field_$resource_field} resource ON
		reserve.nid=resource.nid
	WHERE
		resource.field_{$resource_field}_nid IN ( $resources )
	$ignoreself
	AND
	(
		('$date_start' >= d.field_{$date_field}_value
		AND
		'$date_start' < d.field_{$date_field}_value2
		)
	OR
		('$date_end' > d.field_{$date_field}_value
		AND
		'$date_end' <= d.field_{$date_field}_value2
		)
	OR
		('$date_start' <= d.field_{$date_field}_value
		AND
		'$date_end' >= d.field_{$date_field}_value2
		)
	);";

	//debug
	//drupal_set_message("<pre>$sql</pre>");

	$result = db_query($sql);
	while ($conflict = db_fetch_object($result)) {
		$conflicts_detected[] = $conflict;
	}
}

$conflictcount = count($conflicts_detected);
if ($conflictcount > 0) {
	$error_msg = "$conflictcount conflicting reservation time(s) detected: <ol>\n";
	foreach ($conflicts_detected as $c) {
		$error_msg .= "<li><a href=\"/node/".$c->nid;
		$error_msg .= "\" target=\"c\">Reservation #".$c->nid.'</a> from ';
		$error_msg .= date("F j, Y, g:i a",strtotime($c->d1));
		$error_msg .= " to ".date("F j, Y, g:i a",strtotime($c->d2));
		$error_msg .= "</li>\n";
	}
	$error_msg .= "</ol>\n".$helpwithconflict;
	form_set_error('field_'.$date_field, $error_msg);
}

Hmm, thanks a lot. I'll have it a look :-)

Feature requests are going to the D6 version, to be backported if possible.

Datetime conflicts functionality in Date module will be great for me as well.
+1

Subscribing. This would be a great feature.

There is Resource Conflict module, but it is not fully working for 6.x - http://drupal.org/node/289351

Looks like Resource Conflict module provides this functionality...
http://drupal.org/project/resource_conflict

I've tried resource_conflict many times, but without any success.
I've got some similar functionality implemented here:
http://drupal.org/project/booking_timeslots

But in my opinion it should be somehow integrated in Date module as well.

It's not fixed as a Date feature.
In my opinion it should be part of Date as well.

IMO, this should NOT be part of the Date Module.
This additional functionality would be MUCH better served in an additional module.
The Date module ONLY provides a date/time field type and an api for handling dates in Drupal.

Reasons:
1.) Adding resource booking / confilict manager feature is completely outside the scope of the Date module.
2.) It would add complexity & overhead to the date module which not everyone needs or wants
3.) UI & Logistical issues. I forsee lots of different usage scenarios where the UI and implementation could be drastically different. A seperate module(s) could better and more swiftly be modified and customized.

I am not against this functionality and is would be a great feature to have. I just think it would be better served outside the date module itself. The Date module is complex enough the way it is. I want to keep it lean. Its ONLY purpose is to provide a way to store and display date and time information in Drupal. Other modules use and act on that information (Views, Calendar, Resource Conflict...)

I suggest either starting a new module for this feature or continuing development on the Resource Conflict module. To me, it looks like the Resource Conflict module has a good start on this and would be the best direction to follow. It just might need some updates and features added.

Thanks for answering.

Here is my take on cck_validation field for detecting conflicts in dates using #5 as a base. It uses a CCK field as a resource, not separate content type. It is also aware of node status (checks against only published nodes) and node revisions (uses only latest version).

Thanks dugh!

//debug
//drupal_set_message('<pre>$node: '.print_r($node,TRUE).'</pre>');

$reservation_type = "csievent";
$date_field = "csievent_date";
$resource_field = "csievent_room";
//message to show if a conflict is found, after conflicts are listed:
$helpwithconflict = 'View the <a href="/calendar" target="cal">resource calendar</a> to see other reservations in a calendar view.';

$conflicts_detected = array();
$resource = $node->field_csievent_room[0]['value'];
$ignoreself = ($node->nid)? " AND reserve.nid != {$node->nid}" : '';

foreach ($node->field_csievent_date as $rkey => $reservedate)  {
    $date_start = $reservedate['value'];
    $date_end = $reservedate['value2'];

    $sql = "SELECT reserve.nid, node.title as title, d.field_{$date_field}_value as d1, d.field_{$date_field}_value2 as d2 FROM {content_type_$reservation_type} reserve LEFT JOIN node ON node.nid=reserve.nid LEFT JOIN {content_field_$date_field} d ON reserve.nid=d.nid WHERE  node.status=1 AND node.vid = d.vid AND (('$date_start' >= d.field_{$date_field}_value AND '$date_start' < d.field_{$date_field}_value2) OR ('$date_end' > d.field_{$date_field}_value AND '$date_end' <= d.field_{$date_field}_value2) OR ('$date_start' <= d.field_{$date_field}_value AND '$date_end' >= d.field_{$date_field}_value2)) $ignoreself AND '$resource' = reserve.field_{$resource_field}_value;";

    //debug
    //drupal_set_message("<pre>$sql</pre>");

    $result = db_query($sql);
    while ($conflict = db_fetch_object($result)) {
        $conflicts_detected[] = $conflict;
    }
}

$conflictcount = count($conflicts_detected);
if ($conflictcount > 0) {
    $error_msg = "$conflictcount conflicting reservation time(s) detected: <ol>\n";
    foreach ($conflicts_detected as $c) {
        $error_msg .= "<li><a href=\"/node/".$c->nid;
        $error_msg .= "\" target=\"c\">Reservation #".$c->nid.' ('.$c->title.')</a> from ';
        $error_msg .= date("F j, Y, g:i a",strtotime($c->d1));
        $error_msg .= " to ".date("F j, Y, g:i a",strtotime($c->d2));
        $error_msg .= "</li>\n";
    }
    $error_msg .= "</ol>\n".$helpwithconflict;
    form_set_error('field_'.$date_field, $error_msg);
}