php Validation on Date Range

ontarget · Jan 29, 2017

Hi I have a calendar viz which triggers a form when you double click on the event date in the cal.

I allow users to enter a "Arrival Date" and "Departure Date" and some other basic info into the form.

The system admin can tick a check box called "Accept Booking" which stores a value ["Booked"] in the DB

I wish to prevent other users from entering data into any cells between the Arrival and Departure dates after the admin has ticked "Booked" so i need some kind of date range validation.

The isunique values plugin works great if they select the "Arrival Date or Departure Date" as they are stored in the database - but doesn't cut the mustard for a range of dates as users can still select dates in-between.

I tried the following code (thanks to Rob for his post) as php validation on the "arrival Date" element
'bookings___date_time' and as a php plugin on the bookings form.
Unfortunately not working - no errors returned on the element php validation.
On the form php plugin it makes the form popup blank - maybe thats good!?
Here is the code - would really appreciate any help

PHP:

// Get the form field's value:
//arrival date hidden date field
$date = '{bookings___date_time}';
//departure date
$enddate = '{bookings___date_time_dep}';


// Get the db object
$db =JFactory::getDbo();

// Quote the values for security reasons:
$date = $db->quote($date);


// Build the query
$query = $db->getQuery(true);

$query->SELECT ('count(*)') 
->FROM('bookings') 
->WHERE('date_time >=  ' . $date .' AND  date_time_dep <= ' . $enddate .' AND  accept_booking ='  .["Booked"]);
$db->setQuery($query);


// Run the query
$found = $db->loadResult();


if ($found == 1) {
   return false;
} else {
   return true;
}

cheesegrits · Jan 29, 2017

OK, here's some code from a project I'm working on, which books electrical generators. I've created a separate file with a simple class that I run functions from, rather than having big chunks of code inline in things like validations.

So on both dates (start and end) I have two PHP validation plugins...

One to make sure start date is > end date ...

PHP:

require_once JPATH_ROOT . '/custom_scripts/foo.php';
return FooHelper::checkDateOrder($formModel);

And one to check for overlaps ....

PHP:

require_once JPATH_ROOT . '/custom_scripts/foo.php';
return FooHelper::checkDateOverlap($formModel);

The code they call is as follows ...

PHP:

defined('_JEXEC') or die;

class FooHelper{
    public static function checkDateOverlap($formModel)
    {
        $rowid = $formModel->formData['__pk_val'];
        $generatorId = $formModel->formData['generatoren_data___generator'];
        $generatorId = is_array($generatorId) ? $generatorId[0] : $generatorId;

        // need this nasty code in case this is after a failed validation, in which case dates are in a different format (*sigh*)
        $startDate = $formModel->formData['generatoren_data___startdate'];
        if (is_array($startDate))
        {
            $sDate = explode(' ', $startDate['date']);
            $sDate = $sDate[0] . ' ' . ltrim($startDate['time']);
        }
        else
        {
            $sDate = $startDate;
        }

        $endDate = $formModel->formData['generatoren_data___enddate'];

        if (is_array($endDate))
        {
            $eDate = explode(' ', $endDate['date']);
            $eDate = $eDate[0] . ' ' . ltrim($endDate['time']);
        }
        else
        {
            $eDate = $endDate;
        }

        if (!empty($generatorId)) {
            $myDb = FabrikWorker::getDbo();
            $myQuery = $myDb->getQuery(true);
            // this will select any overlapped rows, and we need to apply TZ offset as dates are stored UTC but submitted dates are local
            $myQuery
                ->select('COUNT(generator) AS foo')
                ->from('generatoren_data')
                ->where("CONVERT_TZ(generatoren_data.startdate, 'UTC', 'Europe/Amsterdam') <= '" . $eDate . "'")
                ->where("CONVERT_TZ(generatoren_data.enddate, 'UTC', 'Europe/Amsterdam') >= '" . $sDate . "'")
                ->where("generator = " . $myDb->quote($generatorId));

            // if we're editing a row, we need to exclude this rowid from the query
            if (!empty($rowid))
            {
                $myQuery->where('generatoren_data.id != ' . $myDb->quote($rowid));
            }

            $myDb->setQuery($myQuery);
            $sql = (string) $myQuery;
            $myCount = (int)$myDb->loadResult();

            // if we found any rows, there's an overlap, return false
            if ($myCount > 0)
            {
                return false;
            }
        }

        return true;
    }

    public static function checkDateOrder($formModel)
    {
        $startDate = $formModel->formData['generatoren_data___startdate'];

        if (is_array($startDate))
        {
            $sDate = explode(' ', $startDate['date']);
            $sDate = $sDate[0] . ' ' . ltrim($startDate['time']);
        }
        else
        {
            $sDate = $startDate;
        }

        $sDate = strtotime($sDate);

        $endDate = $formModel->formData['generatoren_data___enddate'];

        if (is_array($endDate))
        {
            $eDate = explode(' ', $endDate['date']);
            $eDate = $eDate[0] . ' ' . ltrim($endDate['time']);
        }
        else
        {
            $eDate = $endDate;
        }

        $eDate = strtotime($eDate);

        return $eDate > $sDate;
    }
}

Obviously you don't need the "generator ID" stuff, and instead you'll need to replace that with your test for "booked".

But this should be enough to get you started.

-- hugh

ontarget · Jan 29, 2017

Hi Hugh
Thanks for sharing this looks like you are running the validation on the form php plugin rather than on the individual elements - correct?
If yes what Process script setting would you recommend?
Thanks

cheesegrits · Jan 30, 2017

Nope, that's a PHP validation.

-- hugh

ontarget · Jan 30, 2017

Ok thanks will check it out
Sorry was late when i read it! zzzz

So on both dates (start and end) I have two PHP validation plugins...

ontarget · Jan 30, 2017

Hi Hugh
My work colleague came up with a possible solution using JS and I wondered if you could help us implement it.
What I am trying to do is prevent people clicking on dates that are marked "Booked" in the calendar viz so he suggested why not simply prevent them from clicking on the cell in the first place rather than trying to validate a date range through php
something like:

JavaScript:

Fabrik.addEvent('fabrik.viz.calendar.event',function(e){
e.preventDefault();
});

He had a look at this but theme js is loaded before fabrik so wont work. There is a mention of adding js in http://fabrikar.com/forums/index.php?wiki/calendar-visualization/

but we are not sure where to add the JS - can you suggest anything that could achieve this...
Here is a link to the page so you can see the calendar viz with some entries marked "Booked"
http://www.4coischnoicholidayhomedingle.com/bookings.html
The "Booked" is activated by the admin marking a checkbox so in the db it has a value of ["Booked"] or Null

ontarget · Jan 30, 2017

I now have the fullcalendar viz working so i dont know if that makes it any easier to disable event clicking - e.g can i add this somehow?
https://fullcalendar.io/docs/mouse/eventClick/

My JS is pretty poor but maybe something like

Code:

{    "eventClick": function(event){ if (event.url) {             return false;         } } }

cheesegrits · Jan 31, 2017

Yeah, unfortunately that fabrik.viz.calendar.event was from the original, now deprecated, calendar viz.

I've had a look at the new code, and there's not really an easy way for you to hook into it. So ... in this commit:

https://github.com/Fabrik/fabrik/commit/0b2f3a744de20f2051e5250846a8ff646ccfa020

... I've added a new event, fabrik.viz.fullcalendar.dateinlimits, which is called from the dateInLimits() method, which is where we check the start/end dates you can configure in the Limits settings of the viz. It takes the viz object and a date as args, and if you return false, we'll ignore the double click.

Use it like this, in a ./components/com_fabrik/js/viz_X.js (where X is your numeric viz ID):

JavaScript:

requirejs(['fab/fabrik'], function () {
    Fabrik.addEvent('fabrik.viz.fullcalendar.dateinlimits', function (viz, date) {
        fconsole('date: ' + date);
        return true;
    });
});

Of course, the hard part is figuring out whether to allow the date. At the moment, you'd have to handle that yourself. But what I think I may do is add something similar to what we have in the date element, where you can provide some PHP code that builds a list of 'blackout' dates that shouldn't be selectable in the date widget. And I figured I'd pu that in the "Limits" settings tab, hence putting the check for it in the dateInLimits() method. Just seemed like the right place.

I don't have time to do that right now but this should at least give you something to start playing with, and if you have any suggestions for the best approach, let me know.

You'll need to do a github update to pick up the new code. Insert usual warnings here about not github updating a live site without testing in a sandbox first, etc.

ontarget · Feb 1, 2017

Hugh I have gone a bit crazy with this but I'm like a dog with a (very tasty) bone.
I have gone back to a php validation method instead and I have the sql query working perfectly in my test page - the query validates the date overlaps and if a booking exists.
So I try to run the same query on the {bookings___date_time} element php validation and as a php plugin on the form - no joy - the form submits irrespective of my query.
Here is the query as entered into a php validation on the {bookings___date_time} element.

PHP:

$startdate = '{bookings___date_time}';
$enddate = '{bookings___date_time_dep}';
// Get the db object
$dbc =JFactory::getDbo();

  $q = "SELECT *
    FROM bookings
    WHERE  '$startdate' >= date_time
    AND  '$enddate' <= date_time_dep
    AND accept_booking =  'Booked' ";

$r = mysqli_query($dbc , $q);

if ( mysqli_num_rows ($r) > 0)
{
return false;
}
else
{
    return true;
}

Here is a screen shot of my settings in the element validation.
It seems to just ignore "result false;"

cheesegrits · Feb 1, 2017

That mysql query code is just wrong, don't try and use the native mysqli_whatever calls, use the J! API calls like my example code does - get the DBO, get the query, set the query, etc.

I've really gone as far as I can on this on Standard support. I'd be happy to login to your site and write this for you if you want to bump up to Pro support, or pay for a little of my time.

You are on the right track, and your query essentially does pretty much what mine does, you just need to use the right magic incantations to make it happen.

-- hugh

ontarget · Feb 3, 2017

Hi Hugh I appreciate that. I will pay for the pro support.
Here is the J! code I have written not sure if my " " or ' ' are correct - also I am putting this as a php validation on the form plugin - with the process script set to "onLoad"

PHP:

// Get the db object
$db =& JFactory::getDBO();
$tr_table = $db->quoteName('bookings');
$dt = $db->quoteName('date_time');
$dd = $db->quoteName('date_time_dep');
$ab = $db->quoteName('accept_booking');

$query = "SELECT * FROM $tr_table WHERE '{bookings___date_time}' >= $dt AND '{bookings___date_time_dep}' <= $dd AND $ab ='Booked'";
$db->setQuery($query);
$row = $db->loadRow();
if($row > 0)
{
return false;
}
else
{
  return true;
}

ontarget · Feb 3, 2017

Solved!

PHP:

// Get the db object
$db =& JFactory::getDBO();
$tr_table = $db->quoteName('bookings');
$dt = $db->quoteName('date_time');
$dd = $db->quoteName('date_time_dep');
//$ab = $db->quoteName('accept_booking');
$ab = $db->quoteName('radio_accept_booking');


$query = "SELECT * FROM $tr_table WHERE '{bookings___date_time}' >= $dt AND '{bookings___date_time_dep}' <= $dd AND $ab ='Booked'";

$db->setQuery($query);
$row = $db->loadRow();
if($row > 0)
{
echo "Booked OUT - Please Choose a different date";
return false;
}
else
{
  return true;
}

I forgot to update $ab to my new field doh!

php Validation on Date Range

ontarget

Active Member

cheesegrits

Support Gopher

ontarget

Active Member

cheesegrits

Support Gopher

ontarget

Active Member

ontarget

Active Member

ontarget

Active Member

cheesegrits

Support Gopher

ontarget

Active Member

cheesegrits

Support Gopher

ontarget

Active Member

ontarget

Active Member

Useful links

Members online