[solved] what is the solution to prevent conflict event?

samani · Oct 7, 2017

hi.
i have a form have a event content type and simple dropdown element called location.
i want add some event to table.some event occur in location A and some location B. i want do something to prevent save two event in one time in one location when i want add new event.
what is the solution?
how can i validate and what plugin of validation must be use?

cheesegrits · Oct 9, 2017

You'd need to build a query in a PHP validation.

Are you using start & end date/time? Or are they all day events?

samani · Oct 9, 2017

i'm using start&end date/time.
it's one of default content type of fabrik.
i searched and find some solution like this:
https://stackoverflow.com/questions/6571538/checking-a-table-for-time-overlap
and
https://stackoverflow.com/questions/11098541/check-if-two-times-overlap
but i can't use that.
don't forget i have location for each event and i want prevent overlap two event in one location.

cheesegrits · Oct 9, 2017

Here's some code I use for doing something very similar (booking electrical generators).

You should be able to adapt this by just changing the 'generatoren_data___whatever' names, use your location instead of ___generator, and your start/end date names, and tweak the query with table/field names.

The only other issue might be the TZ conversion. Do you store your dates as GMT or "local"?

Code:

        $rowid = $formModel->formData['__pk_val'];
        $generatorId = $formModel->formData['generatoren_data___generator'];
        $generatorId = is_array($generatorId) ? $generatorId[0] : $generatorId;

        $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);
            $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 (!empty($rowid))
            {
                $myQuery->where('generatoren_data.id != ' . $myDb->quote($rowid));
            }

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

            if ($myCount > 0)
            {
                return false;
            }
        }

        return true;

samani · Oct 9, 2017

i'm using GMT. is it a problem?
must i use this for my country instead of using Europe/Amesterdam? :
http://php.net/manual/en/timezones.asia.php

cheesegrits · Oct 9, 2017

is it a problem?

Well, it's not a "problem", it just means you'll have to account for time zones in your query. The values for the dates you get in the form submission will be in local time, but the values you are comparing against in the database will be in GMT. So you'll have to handle that in your query, as per that example code - you can see where I do the date comparison, I do ...

Code:

                ->where("CONVERT_TZ(generatoren_data.enddate, 'UTC', 'Europe/Amsterdam') >= '" . $sDate . "'")

... and apply the 'Europe/Amsterdam' TZ offset to the stored dates, in order to compare them with the (local) dates coming from the form.

(When Fabrik writes the data out to the table, if you are storing as GMT, we convert the times - but that doesn't happen till after validation has run, so during validation, they are still in local time.)

The problem you may run into with this code as-is, is that in order to use named time zones, your MySQL must have the time zone tables set up properly:

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_convert-tz

If you don't have the TZ tables, and don't feel like installing them ...

https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

... you'll have to use numbers instead of names, like ...

Code:

                ->where("CONVERT_TZ(generatoren_data.enddate, '+0:00', '+2:00') >= '" . $sDate . "'")

-- hugh

samani · Oct 11, 2017

your code is return null for me.Tip:i use repeating group in my form.

cheesegrits said:

Code:

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

in the scope of code that you say:
$sDate = explode(' ', $startDate['date']);
i must change to :
$sDate = explode(' ', $startDate[0]['date']);
to print a record of database.
what i must use instead of 0 in the $startDate[0]['date'] to get all record?

this is my end date when form submit :
(result of echo "<pre>";print_r($endDate); you can see in below)

Code:

Array
(
[0] => Array
(
[date] => 2017-09-16 15:53:00
[time] => 15:53
)

[1] => Array
(
[date] => 2017-09-23 18:00:00
[time] => 18:00
)

[2] => Array
(
[date] => 2017-09-23 18:00:00
[time] => 18:00
)

[3] => Array
(
[date] => 2017-09-25 16:34:00
[time] => 16:34
)

[4] => Array
(
[date] => 2017-09-27 16:34:00
[time] => 16:34
)

)

samani · Oct 11, 2017

i find this but i can't implement that in each way:
http://fabrikar.com/forums/index.php?threads/php-validation-on-date-range.46025/
can i ask from you do that for me?

cheesegrits · Oct 11, 2017

The repeat count for the repeat group currently being validated is in $repeatCounter, so ...

$startDate = $formModel->formData['generatoren_data___startdate'][$repeatCounter];

-- hugh

samani · Oct 11, 2017

i give this error:
1054 Unknown column 'Array' in 'where clause'
when i comment this line:
->where("locationOfClass = " . $myDb->quote($generatorId));
i give this error:
0 syntax error, unexpected 'if' (T_IF)

samani · Oct 11, 2017

when i comment these line:
if (!empty($rowid))
{
$myQuery->where('aryamsessions.id != ' . $myDb->quote($rowid));
}
0 syntax error, unexpected '$myDb' (T_VARIABLE)

cheesegrits · Oct 11, 2017

samani said:
i give this error:
1054 Unknown column 'Array' in 'where clause'
when i comment this line:
->where("locationOfClass = " . $myDb->quote($generatorId));
i give this error:
0 syntax error, unexpected 'if' (T_IF)

Very probably because you've commented out the ; that ends that set of chained calls. You'd have to add the ; one line up.

-- hugh

samani · Oct 11, 2017

i'm using this code that you get me and modify according below:

Code:

$rowid = $formModel->formData['aryamsessions___id'];
        $generatorId = $formModel->formData['aryamsessions___locationOfClass'];
        $generatorId = is_array($generatorId) ? $generatorId[0] : $generatorId;

        $startDate = $formModel->formData['aryamsessions___start'][$repeatCounter];
        if (is_array($startDate))
        {
             $sDate = explode(' ', $startDate['date']);
            $sDate = $sDate[0] . ' ' . ltrim($startDate['time']);
        }
        else
        {
            $sDate = $startDate;
        }

        $endDate = $formModel->formData['aryamsessions___end'][$repeatCounter];

        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);
            $myQuery
                ->select('COUNT(locationOfClass) AS foo')
                ->from('aryamsessions')
                ->where("aryamsessions.start <= '" . $eDate . "'")
                ->where("aryamsessions.end >= '" . $sDate . "'");
             //  ->where("locationOfClass = " . $myDb->quote($generatorId));

           if (!empty($rowid))
            {
                $myQuery->where('aryamsessions.id != ' . $myDb->quote($rowid));
            }

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

            if ($myCount > 0)
            {
                return false;
            }
        }

        return true;

and i get this error :
1054 Unknown column 'Array' in 'where clause'

samani · Oct 11, 2017

it seems these code must modify because when i commented these no error return

Code:

->where("aryamsessions.start <= '" . $eDate . "'")
                ->where("aryamsessions.end >= '" . $sDate . "'")
               ->where("locationOfClass = " . $myDb->quote($generatorId));

           if (!empty($rowid))
            {
                $myQuery->where('aryamsessions.id != ' . $myDb->quote($rowid));
            }

cheesegrits · Oct 11, 2017

If aryamsessions___locationOfClass is also in the repeat group you'll need to add the [$repeatCounter] to that as well, otherwise it'll be an array. Which is what the error is telling you.

I've gone about as far as I can with this in Standard support. This is now firmly in Pro support scope.

-- hugh

samani · Oct 20, 2017

finally i can resolve this problem but i want to do one thing in the Error message field to return some PHP variable that i write them in the PHP code box.
for example:
i want say in the error box(when validation return false) that which row of witch table conflict.i can do this with var_dump or echo but that message show in top of the page. i want show in the Error message field that show on below of the element.how to show some variable from the PHPcode in the Error message box?

cheesegrits · Oct 23, 2017

I think if you set ...

$this->errorMsg = "something";

... that should override the default message.

-- hugh

samani · Oct 23, 2017

thank you hugh. it's work.
how can i understand all thing such as ---->$this->errorMsg = "something";
is there any doc for study about all variable?

cheesegrits · Oct 23, 2017

The only documentation we have is the wiki. And actually, if you check the wiki for the PHP validation ...

http://fabrikar.com/forums/index.php?wiki/php-validation/#set-a-custom-error-message

... it shows you how to set a custom message. Which is actually to use $this->setMessage("your message"), rather than setting errorMsg directly (which works, and is what the setMessage() function does that internally, but as there's a function to do it, that's better than access variables directly).

-- hugh

[solved] what is the solution to prevent conflict event?

Member

Support Gopher

Member

Support Gopher

Member

Support Gopher

Member

Member

Support Gopher

Member

Member

Support Gopher

Member

Member

Support Gopher

Member

Attachments

Support Gopher

Member

Support Gopher

Useful links