• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Insert x amount of Rows to a separate list based on radio selection

Add a few more variables to the dump ...

var_dump((string)$myQuery, $memberPlan, $activationDate, $billingDate, );exit;

-- hugh
 
Here's what I got.
Code:
C:\wamp64\www\harvey-app\plugins\fabrik_form\php\php.php(485) : eval()'d code:58:string '
INSERT INTO 00_calendar_inserts' (length=33)

C:\wamp64\www\harvey-app\plugins\fabrik_form\php\php.php(485) : eval()'d code:58:
array (size=1)
0 => string '3' (length=1)

C:\wamp64\www\harvey-app\plugins\fabrik_form\php\php.php(485) : eval()'d code:58:
object(DateTime)[1137]
public 'date' => string '2017-10-26 08:35:38.000000' (length=26)
public 'timezone_type' => int 3
public 'timezone' => string 'UTC' (length=3)

C:\wamp64\www\harvey-app\plugins\fabrik_form\php\php.php(485) : eval()'d code:58:
object(DateTime)[1152]
public 'date' => string '2017-10-26 08:35:38.000000' (length=26)
public 'timezone_type' => int 3
public 'timezone' => string 'UTC' (length=3)
 
I'm just puzzled as to why the query doesn't have the fields on it.

I can't really do this going bckwards and forwards on the forum, I'll need to log in to your site. Can you add whatever notes I need (which form, how I test it, etc) in your My Sites, and I'll log in and do it for you.

-- hugh
 
Okay. I've been working locally, so I'll have to copy it over to my live site. You all should already have credentials to log in. I'll let you know when it's up.
 
After puting this on my live site, the vardump looks a little different.
Code:
string(32) " INSERT INTO 00_calendar_inserts" array(1) { [0]=> string(1) "3" } object(DateTime)#1152 (3) { ["date"]=> string(26) "2017-10-27 18:41:55.000000" ["timezone_type"]=> int(3) ["timezone"]=> string(7) "CST6CDT" } object(DateTime)#1155 (3) { ["date"]=> string(26) "2017-10-27 18:41:55.000000" ["timezone_type"]=> int(3) ["timezone"]=> string(7) "CST6CDT" }
Anywho, I've updated the credentials on My Sites and made sure your account is active. I've been testing all this on the back end. in fabrik forms you are looking for 00-Calendar-Entry (this is what I use to fill out the form - it the one with the onafterprocess php plugin enabled), and 00-Calendar-Inserts, which exist only to see if the submission from 00-Calendar-Entry will receive the multiple rows.
 
There were a lot of issues. Here's the final code:

PHP:
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

/**
* we'll need the element model for the radio button so we can get the
* label, as that isn't submitted with the form, it only exists as internal
* params of the element
*/
$elementModel = $formModel->getElement('00_calendar_entry___interval');

// $memberPlan is probablu going to be an array, as it's a radio
$memberPlan = $formModel->getElementData('00_calendar_entry___interval_raw');
$memberPlan = is_array($memberPlan) ? $memberPlan[0] : $memberPlan;

// Use the model getEmailValue() method to get the human readable label
$planLabel = $elementModel->getEmailValue($memberPlan, $formModel->formData);

// get the two dates, convert them to PHP DateTime objects
$activationDate = $formModel->getElementData('00_calendar_entry___start_date');
$activationDate = new DateTime($activationDate);
$billingDate = $formModel->getElementData('00_calendar_entry___start_date');
$billingDate = new DateTime($billingDate);

// start building the query ..
$myQuery->insert('00_calendar_inserts');
$myQuery->columns(
   array(
      $myDb->quoteName('start_date'),
      $myDb->quoteName('interval'),
      $myDb->quoteName('billing_date')
   )
);

// loop around and insert the relevant number of times
for ($step = 1; $step <= 12; $step += $memberPlan) {
   // insert the current value of $billingDate
   $myQuery->values(
      implode(',',
        array(
          $myDb->quote($activationDate->format('Y-m-d')),
          $myDb->quote($planLabel),
          $myDb->quote($billingDate->format('Y-m-d'))
        )
      )
   );

   // increment the plan date by the number of mnonths in the plan period
   for ($i = 1; $i <= $memberPlan; $i++) {
      $billingDate->modify('+1 month');
   }
}

// run the query
$myDb->setQuery($myQuery);
// uncomment this line if you need to debug
//var_dump((string)$myQuery, $memberPlan, $activationDate, $billingDate);exit;
$myDb->execute();

Seems to work, this is the query it generates for a quarterly submission:

Code:
INSERT INTO 00_calendar_inserts (`start_date`,`interval`,`billing_date`) VALUES ('2017-10-10','Quarterly','2017-10-10'),('2017-10-10','Quarterly','2018-01-10'),('2017-10-10','Quarterly','2018-04-10'),('2017-10-10','Quarterly','2018-07-10')

Although I think you may need to tweak that "+1 month" bit, as per my comments about how PHP adds a month. Suggest you test with a variety of no-simple dates (like the 31st of a month which is followed by a month with 30 days).

Plus of course you need to figure out what to do if someone edits and changes the plan, from (say) monthly to quarterly. If that's a possibility, I would suggest adding a 'parent_id' field to the 00_calendar_inserts table, and setting in the values() to $formModel->formData['rowid']. Then you'll know which inserts belong to the row being edited, and can remove them and re-add.

-- hugh
 
Hugh, Thank you so much. this is brilliantly coming together. The only bit I'm running into issues with is, like you said, the way that php adds a month. Really strange actually. When I select 10/31/2017, it respectively increments the subsequent entries to the last day of every month. For some reason, when I do the same thing for 01/31/2018, it seems to be adding 30 days. I assume it's doing the same thing with the January date, but because the following month is short, it's forcing subsequent months to the second. What's also weird is that that even though I selected the 01/31, on the inserts table, the start date for each row says 01/30.

Plus of course you need to figure out what to do if someone edits and changes the plan, from (say) monthly to quarterly. If that's a possibility, I would suggest adding a 'parent_id' field to the 00_calendar_inserts table, and setting in the values() to $formModel->formData['rowid']. Then you'll know which inserts belong to the row being edited, and can remove them and re-add.
That's a great idea and I will probably be back in your kitchen to roll that in a later phase. I'm using this concept for when we add new users to our system, so I think their user id would suffice for the receiving entries?

One last thing, I see the little lock on the entries form. May I check that in or are you still working on it?
 
Hugh,

Is this the solution referenced you think would help?

Code:
$startDate =new \DateTime('2015-08-30');
$endDate = clone $startDate;

$billing_count ='6';
$billing_unit ='m';

$endDate->add(new \DateInterval('P'. $billing_count . strtoupper( $billing_unit )));

if( intval( $endDate->format('n'))>( intval( $startDate->format('n'))+ intval( $billing_count ))%12){if( intval( $startDate->format('n'))+ intval( $billing_count )!=12){
$endDate->modify('last day of -1 month');}}
 
Oops, no, ti was this one, I was misled by the 3 upvotes, thinking that was the answer #.

Code:
$time = newDateTime('2014-01-31');
echo $time->format('d-m-Y H:i') . '<br/>';

$time->add(add_months(1, $time));

echo $time->format('d-m-Y H:i') . '<br/>';


function add_months($months, \DateTime $object)
{
    $next = newDateTime($object->format('d-m-Y H:i:s'));
    $next->modify('last day of +' . $months . ' month');

    if ($object->format('d') > $next->format('d'))
    {
        return $object->diff($next);
    }
    else
    {
        returnnewDateInterval('P' . $months . 'M');
    }
}

So basically you would copy the function add_months() into your code (right at the top would be fine) then instead of doing ...

Code:
   for($i=1;$i<=$memberPlan;$i++){
     $billingDate->modify('+1 month');
   }

... do ...

Code:
   $billingDate->add(add_months($memberPlan, $billingDate));

If that works, I'll probably add it as a helper function to make life easier for future users.

-- hugh
 
Thanks for your response. This is the error I'm getting:

0 Call to undefined function newDateTime()

This happens with or without a vardump.

Here is the code I'm using:

Code:
function add_months($months, \DateTime $object)
{
    $next = newDateTime($object->format('d-m-Y H:i:s'));
    $next->modify('last day of +' . $months . ' month');

    if ($object->format('d') > $next->format('d'))
    {
        return $object->diff($next);
    }
    else
    {
        returnnewDateInterval('P' . $months . 'M');
    }
}
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

/**
* we'll need the element model for the radio button so we can get the
* label, as that isn't submitted with the form, it only exists as internal
* params of the element
*/


$elementModel = $formModel->getElement('00_calendar_entry___interval');

// $memberPlan is probablu going to be an array, as it's a radio
$memberPlan = $formModel->getElementData('00_calendar_entry___interval_raw');
$memberPlan = is_array($memberPlan) ? $memberPlan[0] : $memberPlan;

// Use the model getEmailValue() method to get the human readable label
$planLabel = $elementModel->getEmailValue($memberPlan, $formModel->formData);

// get the two dates, convert them to PHP DateTime objects
$activationDate = $formModel->getElementData('00_calendar_entry___start_date');
$activationDate = new DateTime($activationDate);
$billingDate = $formModel->getElementData('00_calendar_entry___start_date');
$billingDate = new DateTime($billingDate);

// start building the query ..
$myQuery->insert('00_calendar_inserts');
$myQuery->columns(
   array(
      $myDb->quoteName('start_date'),
      $myDb->quoteName('interval'),
      $myDb->quoteName('billing_date')
   )
);

// loop around and insert the relevant number of times
for ($step = 1; $step <= 12; $step += $memberPlan) {
   // insert the current value of $billingDate
   $myQuery->values(
      implode(',',
        array(
          $myDb->quote($activationDate->format('Y-m-d')),
          $myDb->quote($planLabel),
          $myDb->quote($billingDate->format('Y-m-d'))
        )
      )
   );

   // increment the plan date by the number of mnonths in the plan period
   /*for ($i = 1; $i <= $memberPlan; $i++) {
      $billingDate->modify('+1 month');
   }*/
   
   $billingDate->add(add_months($memberPlan, $billingDate));
}

// run the query
$myDb->setQuery($myQuery);
// uncomment this line if you need to debug
var_dump((string)$myQuery, $memberPlan, $activationDate, $billingDate);exit;
$myDb->execute();
 
Did you fix the returnnewDateInterval?

The problem is that when I copy and paste code from Stack Exchange, it removes some (but not all) spaces.

-- hugh
 
I did. Here is the code now...

Code:
function add_months($months, \DateTime $object)
{
    $next = new DateTime($object->format('d-m-Y H:i:s'));
    $next->modify('last day of +' . $months . ' month');

    if ($object->format('d') > $next->format('d'))
    {
        return $object->diff($next);
    }
    else
    {
        return new DateInterval('P' . $months . 'M');
    }
}
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

/**
* we'll need the element model for the radio button so we can get the
* label, as that isn't submitted with the form, it only exists as internal
* params of the element
*/


$elementModel = $formModel->getElement('00_calendar_entry___interval');

// $memberPlan is probablu going to be an array, as it's a radio
$memberPlan = $formModel->getElementData('00_calendar_entry___interval_raw');
$memberPlan = is_array($memberPlan) ? $memberPlan[0] : $memberPlan;

// Use the model getEmailValue() method to get the human readable label
$planLabel = $elementModel->getEmailValue($memberPlan, $formModel->formData);

// get the two dates, convert them to PHP DateTime objects
$activationDate = $formModel->getElementData('00_calendar_entry___start_date');
$activationDate = new DateTime($activationDate);
$billingDate = $formModel->getElementData('00_calendar_entry___start_date');
$billingDate = new DateTime($billingDate);

// start building the query ..
$myQuery->insert('00_calendar_inserts');
$myQuery->columns(
   array(
      $myDb->quoteName('start_date'),
      $myDb->quoteName('interval'),
      $myDb->quoteName('billing_date')
   )
);

// loop around and insert the relevant number of times
for ($step = 1; $step <= 12; $step += $memberPlan) {
   // insert the current value of $billingDate
   $myQuery->values(
      implode(',',
        array(
          $myDb->quote($activationDate->format('Y-m-d')),
          $myDb->quote($planLabel),
          $myDb->quote($billingDate->format('Y-m-d'))
        )
      )
   );

   // increment the plan date by the number of mnonths in the plan period
   /*for ($i = 1; $i <= $memberPlan; $i++) {
      $billingDate->modify('+1 month');
   }*/

   $billingDate->add(add_months($memberPlan, $billingDate));
}

// run the query
$myDb->setQuery($myQuery);
// uncomment this line if you need to debug
//var_dump((string)$myQuery, $memberPlan, $activationDate, $billingDate);exit;
$myDb->execute();

See the attached images. It shows that I select the 15th, but it subtracts a day on all the inserts
 

Attachments

  • Screen Shot 11-02-17 at 01.59 PM.JPG
    Screen Shot 11-02-17 at 01.59 PM.JPG
    52.2 KB · Views: 39
  • Screen Shot 11-02-17 at 01.591 PM.JPG
    Screen Shot 11-02-17 at 01.591 PM.JPG
    154.5 KB · Views: 35
That's probably time zone related. The date submitted in the form will be in local time, and you are writing the derived dates based on that as-is. So if your date elements on the 'insert' list (Start Date and Billing Date) are set to "Store as GMT", then when Fabrik displays them, it applies your local TZ offset to them.

Normally when storing a date through Fabrik, if the date is set to "Store as GMT", then we would un-apply the TZ offset before storing to the database, so GMT is stored, and then the local offset is applied during rendering. But if you've stored the time as local, then Fabrik applies the TZ offset again when rendering ... yada yada.

So ... you'll have to (un)apply your TZ offset to the calculated dates, either in PHP or in the query. PHP timezone handling sucks big festering ones, so I usually do it in MySQL.

So say your local TZ offset is GMT -6

Code:
    $active = 'CONVERT_TZ(' . $myDb->quote($activationDate->format('Y-m-d')) . ', "+0:00", "+6:00")',
    $billing = 'CONVERT_TZ(' . $myDb->quote($billingDate->format('Y-m-d')). ', "+0:00", "+6:00")'
   $myQuery->values(
      implode(',',
        array(
          $active,
          $myDb->quote($planLabel),
          $billing
        )
      )
   );

NOTE - this won't take DST into account. If that matters, you'll need to make sure your server has the MySQL tz tables installed:

https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html#time-zone-installation

... and use symbolic TZ names instead of numbers:

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

-- hugh
 
Nope, because time is part of a date. The date "2017-11-06" is implicitly "2017-11-06 00:00:00" ... which if rendered in GMT will be Nov 6th, but if rendered in CST will be Nov 5th. The earth is a sphere, and rotates. Date handling would be LOT simpler if it were flat and always faced the sun.

Welcome to the wonderful world of date handling. If you think this is crazy, next time you have a few hours spare I'll tell you how crazy it gets in our date element code, where we have to deal with time zones, daylight saving, PHP date formatting, JavaScript date formatting, MySQL date formatting, local vs GMT time ...

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top