• 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

SoilentRed

Caaan do!
I have two forms. Form "A" has a radio element that selects Monthly, Quarterly or annually. List "B" will collects data from form "A" and insert it to list "B" based on the selection. If Annual is selected, I need to insert one row into List "B" with a calculation. If Quarterly is selected, I need to insert 3 rows with Calculations into List "B". Finally if monthly is selected, I need to insert 12 rows with calculations.

would you please provide guidance on how this is done? Thank you!
 
Add a php plugin to form A.
Grab the radio value and additional data you need, do your calclations and create the records in table B.

See WIKI form php plugin for getting the data and WIKI common php tasks for the insert.

Gesendet von meinem GT-P5210 mit Tapatalk
 
I'm missing some code that is probably obvious to you. It's the bit about multiple inserts. The documentation covers a single row, but I need multiple if , say quarterly is selected, the data inserted should be:

john doe, 01/01/2017
john doe, 04/01/2017
john doe, 07/01/2017
john doe, 10/01/2017

How do I tell fabrik to do this?
 
I just upgraded my subscription to the Professional level in case you wish to take this to the professional thread.
 
This flies over my head. Can't I just capture the date and then create 12 variables?

$month1 = date
$month2 = date + 1 months
$month3 = date + 2 months
etc...

Then create an if statement with a foreach that says if $plan = quarterly, insert into table b $month1, name; $month4, name etc...

I know I sound like a novice. It's cause I am. Really need my hand held through this.

You're appreciated!
 
Something like this ...

Code:
// some code not shown here that grabs your radio button value and other stuff you need
// I'll assume you have $plan, $name

// ste the start date - you'll prolly want to modify this, but for now hard wire it to first day of this year
$startDate = new DateTime('2017-01-01');

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

switch ($plan) {
   case 'monthly':
      $interval = 1;
      break;
   case 'quartely':
      $interval= 3;
      break;
   case 'annual':
      $interval = 12;
      break;
}

$myQuery->insert('yourtable);

// list the fields you want to insert ...
$myQuery->columns(
   $myDb->quoteName('name'),
   $myDb->quoteName('bill_date')
);

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

   // increment the start date by a month, $interval number of times
   for ($i = 1; $i <= $interval; $i++) {
      $startDate->modify('first day of next month');
   }
}

// run the query
$myDb->setQuery($myQuery);
$myDb->execute();

This code *should* tolerate any start date which is the first of a month.

If you need to have variable start days, let me know.

-- hugh
 
Hugh, thanks for the response. Since we will be generating invoices on the 1rst and the 15th, the inputs do not need to round to the first. They just need to be exactly x amount of months from the date selected. When the first and the 15th of every month comes around, we will run a report in fabrik that queries everything from 1rst to the 14th or 15 to the 31st. depending on the billing cycle. How do we capture one month from the day selected or 6 months from the day selected?
 
They just need to be exactly x amount of months from the date selected. They just need to be exactly x amount of months from the date selected

That's where things get funky.

What is "exactly" 1 month from Aug 31st? Sept 30th? Sep 1st?

That's why I was hoping you were dealing with 1st of the month. :)

-- hugh
 
Hey Hugh! It's your favorite customer. So I've had a chance to dabble with this, but it's still not inserting rows. I've tried a few things differently. But, here's where I'm at:
Instead of creating a switch case, in fabrik I have a radio element with monthly, quarterly, biannual and annual. For their values I put the number that should work as the interval (label: monthly, Value: 1; label: quarterly, Value: 3; etc...)
Since we always need to keep reference the user's start date, I have table "A" with the the start date and interval. In table "b" I have the the same columns plus another called "billing date".

I get no errors but the loop is not inserting any rows into table "b".

Here's what I got:

Code:
// Get a db connection.
$myDb = JFactory::getDbo();

// Create a new query object.
$myQuery = $myDb->getQuery(true);

$memberPlan = $myApp->input->getString('00_calendar_entry___interval_raw');
$activationDate = $myApp->input->getString('00_calendar_entry___start_date');
$billingDate = $myApp->input->getString('00_calendar_entry___start_date');

$myQuery->insert('00_calendar_inserts');

// list the fields you want to insert ...
$myQuery->columns(
  $myDb->quoteName('start_date'),
    $myDb->quoteName('interval'),
    $myDb->quoteName('billing_date')
    );
// loop around and insert the relevant number of times
for ( $i = $memberPlan ; $i <= 12 ; $i + $i) {
   // insert the current value of $startDate
   $myQuery->values(
      $myDb->quote($activationDate),
      $myDb->quote($memberPlan),
      $myDb->quote($billingDate->format('Y-m-d', strtotime("+".$i." month", strtotime($billingDate))))
      )
};

// run the query
$myDb->setQuery($myQuery);
$myDb->execute();

As always, your help and insights are appreciated.
 
most obvious problem is ...

Code:
$billingDate = $myApp->input->getString('00_calendar_entry___start_date');
...
      $myDb->quote($billingDate->format('Y-m-d', strtotime("+".$i." month", strtotime($billingDate))))

You are trying to do DateTime operations on a string, which will error out.

Look at the code i gave you, that creates DateTime object, then does modify() on it.

-- hugh
 
I might be missing something. the code you gave me has:

Code:
$startDate = new DateTime('2017-01-01');

But that defines the date because it was assumed that the I needed the first day of the year. I'm taking some queues from another thread you worked on here: http://fabrikar.com/forums/index.php?threads/date-calculation.47547/

Still no dice. Here's where I'm at with the code.

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

$memberPlan = $myApp->input->getString('00_calendar_entry___interval_raw');
$planLabel = $myApp->input->getString('00_calendar_entry___interval');
$activationDate = $formModel->getElementData('00_calendar_entry___start_date');
$activationDate = new DateTime($activationDate);
$billingDate = $formModel->getElementData('00_calendar_entry___start_date');
$billingDate = new DateTime($billingDate);

$myQuery->insert('00_calendar_inserts');

// list the fields you want to insert ...
$myQuery->columns(
  $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 $startDate
   $myQuery->values(
      $myDb->quote($activationDate),
      $myDb->quote($planLabel),
      $myDb->quote($billingDate->format('Y-m-d'))
   );

   // increment the start date by a month, $interval number of times
   for ($i = 1; $i <= $memberPlan; $i++) {
      $billingDate->modify('+'.$i.'month');
   }
}

// run the query
$myDb->setQuery($myQuery);
$myDb->execute();

You're time and patience is appreciated. We'll get there.
 
Esteemed hugh,

Don't give up on me, brev.

I feel like I've made some progress. Was able to generate some errors which got me to check my syntax and adjust other things...

Anyways, Here's where the code is now, closer to what you gave me.

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

$memberPlan = $formModel->getElementData('00_calendar_entry___interval_raw');
$planLabel = $formModel->getElementData('00_calendar_entry___interval');
$activationDate = $formModel->getElementData('00_calendar_entry___start_date');
$activationDate = new DateTime($activationDate);
$billingDate = $formModel->getElementData('00_calendar_entry___start_date');
$billingDate = new DateTime($billingDate);

$myQuery->insert('00_calendar_inserts');

// list the fields you want to insert ...
$myQuery->columns(
  $myDb->quoteName('start_date'),
  $myDb->quoteName('interval'),
    $myDb->quoteName('billing_date')
);

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

   // increment the start date by a month, $interval number of times
   for ($i = $memberPlan; $i <= 12; $i++) {
      $billingDate->modify('+'.$i.'month');
   }
}

// run the query
$myDb->setQuery($myQuery);
$myDb->execute();

It's giving me the following error:

An error has occurred.
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Thoughts?
 
All,

My subscription is up in a few days. I really need this piece done so I can get out of your hair. Haven't heard from you in a few days. Please let me know your thoughts.
 
Sorry, I've been on the road for much of the last week. Don't worry about the sub expiring, if it does we'll move this to wherever you can post and I'll finish it for you.

I think columns() may need to be an array (the function argument says "a column name or an array of column names")

Code:
$myQuery->columns(
   array(
      $myDb->quoteName('start_date'),
      $myDb->quoteName('interval'),
      $myDb->quoteName('billing_date')
   )
);

You'll need to add some debug output into your code.

First obvious candidate is to see what you query is before executing it ...

Code:
var_dump((string)$myQuery);exit;

... before you execute it. See what that outputs.

-- hugh
 
Okay, I switched the columns to an array with the code you just gave me and here is what the vardump gave:

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)

When I put it back to the way I had it, it gives the same error:

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)
 
affirmative. here's what I got:
Code:
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);


$memberPlan = $formModel->getElementData('00_calendar_entry___interval_raw');
$planLabel = $formModel->getElementData('00_calendar_entry___interval');
$activationDate = $formModel->getElementData('00_calendar_entry___start_date');
$activationDate = new DateTime($activationDate);
$billingDate = $formModel->getElementData('00_calendar_entry___start_date');
$billingDate = new DateTime($billingDate);

/*switch ($plan) {
   case 'monthly':
      $interval = 1;
      break;
   case 'quartely':
      $interval= 3;
      break;
   case 'annual':
      $interval = 12;
      break;
}*/

$myQuery->insert('00_calendar_inserts');

// list the fields you want to insert ...
/*$myQuery->columns(
  $myDb->quoteName('start_date'),
  $myDb->quoteName('interval'),
    $myDb->quoteName('billing_date')
);*/

$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 = $memberPlan; $step <= 12; $step++) {
   // insert the current value of $billingDate
   $myQuery->values(
      $myDb->quote($activationDate),
      $myDb->quote($planLabel),
      $myDb->quote($billingDate->format('Y-m-d'))
   );

   // increment the start date by a month, $interval number of times
   for ($i = $memberPlan; $i <= 12; $i++) {
      $billingDate->modify('+'.$i.'month');
   }
}

// run the query
$myDb->setQuery($myQuery);
var_dump((string)$myQuery);exit;
$myDb->execute();
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top