• 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.

adding months in while loop | Date calculation

SoilentRed

Caaan do!
I've tried countless approaches to this stupid calculation while checking out the posts on the fabrik site. things have gotten wacky and my brain has fallen out.

I simply creating an installment plan

Code:
$base = 1600;
$installmentConstant = (int) '{afab_repman_signup___installment_plan_raw}';
$instAmtPaid = (int) '{afab_repman_signup___installment_plan_raw}';
$numOfInstallments = $base / $installmentConstant;
$count = 1;
$date = $formModel->getElementData('afab_repman_signup___first_payment_date');
$date = new DateTime($date);

while ( $installmentConstant <= $base ) {
$myQuery->values(
        implode(',',
            array(
                $myDb->quote('{afab_repman_signup___date_time}'),
                $myDb->quote('{afab_repman_signup___new_user_id}'),
                $myDb->quote('{afab_repman_signup___nia_agent_id}'),
                $myDb->quote($installmentConstant),
                $myDb->quote($date),
                $myDb->quote($nonMemberRepman),
                $myDb->quote('{afab_repman_signup___full_name}'),
                $myDb->quote('{afab_repman_signup___company_name}'),
                $myDb->quote('{afab_repman_signup___agent_name}'),
                $myDb->quote($numOfInstallments),
                $myDb->quote($count),
                $myDb->quote('{afab_repman_signup___franchise_fee}'),
                $myDb->quote('{afab_repman_signup___franchise_charge}')
        )
      )
   );
    $installmentConstant = $installmentConstant + $instAmtPaid;
    $myDateFormat = $date->modify('+1 month')->format('Y-m-d H:i:s');
    $formModel->updateFormData($date, $myDateFormat, true);
    $date = new DateTime($date);
    $count++;
}

I've read some things about converting the data to / from a string so i added this

Code:
    $myDateFormat = $date->modify('+1 month')->format('Y-m-d H:i:s');
    $formModel->updateFormData($date, $myDateFormat, true);
    $date = new DateTime($date);
but now I get error 500

stripping it all away and just having
Code:
$date = $formModel->getElementData('afab_repman_signup___first_payment_date');
actually returns the date, but adding

Code:
$date = new DateTime($date);

anywhere results in the date looking like 0000-00-00 00:00:00

Can someone simply help me add a month per row in my loop until a condition is met.

THANKS!!! ADFHSIGNJRETFKVSSKIRVOEFJ
 
First off, check that you get a date, so ...

Code:
// get raw date
$date = $formModel->getElementData('afab_repman_signup___first_payment_date', true);
$date = new DateTime($date);
var_dump($date); exit;

... and make sure you have a date structure that looks sane.

Assuming you do, then ... well, the first obvious issue is this line:

Code:
    $formModel->updateFormData($date, $myDateFormat, true);

... where you are trying to use your $date object as the element name, so that'd need to be ...

Code:
    $formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);

Then you need to get rid of the ...

Code:
    $date = new DateTime($date);

... at the bottom of the while loop, because that will a) error out and b) you already modified $date to add a month, so you don't need to touch it.

Also, I'm not sure what the $myQuery->values() stuff is doing, as you never use it, or declare $myDb or $myQuery ... unless there's some more code you haven't included.

-- hugh
 
HUGH!!!! Thanks for the prompt response.
I've been down that route. I've actually been down 43 different tests... So here's what happens in that case.

As you advised, I did a var_dump on $date and that returned

Code:
object(DateTime)#3014 (3) { ["date"]=> string(26) "2019-09-26 00:00:00.000000" ["timezone_type"]=> int(3) ["timezone"]=> string(3) "UTC" }

cool? o_O

So then, I try the code replacing
Code:
$formModel->updateFormData($date, $myDateFormat, true);
with
Code:
$formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
and adding
Code:
$date = new DateTime($date);
above the loop...

the form submits successfully, but the data the db is wrong... every row says 0000-00-00 00:00:00

The only way I can get a repeating row is by removing
Code:
$date = new DateTime($date);
all together as well as all modifiers within the loop

I have omitted a bunch of other code but it doesn't interfere with what I'm trying to do here. All that stuff already works and would probably on add noise to the convo. but if you think I should include it, I will.

As always, thanks for the help. you are THE FREAKING MAN!
 
But, just so we're clear, while you are the man, my issue is still unsolved. Does it matter that the date is passing timezone details. I remember on an old app we had to convert the date and adjust the time locally, or something line that. Really, all I need is to add months.
 
Also, modify seems to be having a hard time
I keep getting this back
Code:
0 - Call to a member function modify() on string
 
Sorry, learning on the fly and thinking out loud

So a var_dump of $date = $formModel->getElementData('afab_repman_signup___first_payment_date'); returns a string

Code:
string(19) "2019-09-06 00:00:00"

And when you convert it with $date = new DateTime($date); it returns... an array?
Code:
object(DateTime)#3017 (3) { ["date"]=> string(26) "2019-09-13 00:00:00.000000" ["timezone_type"]=> int(3) ["timezone"]=> string(3) "UTC" }

but no matter how I try to access the modified function, it's giving me
Code:
0 - Call to a member function modify() on string
 
Hi,

I'm doing a date calculations in calc element like that and it works fine:

Code:
$ship_date = '{tablename___shipping_date}';
$ready_date = '{tablename___finished_date}';

$due_date = date ('Y-m-d', strtotime ('-2 weekdays', strtotime($ship_date)));
return $due_date;

Same approach works also eg. in form php plugin.
 
Hugh,

Here's is my latest effort of the code in it's entirety. Obviously I had to redact the API data I'm using for bitly, but that part goes off without a hitch. Likewise, the first database I insert records into is fine.

It's the final bit that gives me issues, with adding a month in a loop until my condition is met. Toward the bottom, you can see a bunch of coded-out lines. This will give you an idea of some of my efforts that have failed.

Code:
// Get a db connection.

$jsDb = JFactory::getDbo(); //

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

//$newid = (int)$jsDb->insertid(); //get new record id

$newuserid = $formModel->formData['new_user_id'];
// $newuserid;

$companylogo = $formModel->formData['company_logo'];

$fullUrl = 'https://www.mysite.com/index.php/reviews?&reviews___member_id='.$newuserid;

require_once JPATH_SITE . '/components/com_fabrik/libs/bitly/bitly.php';
         $bitly = new bitly('MYbitlyUSERNAME', 'MyBitlyAPIKEYPASSWORD');
       
         // bitlify it
         $url = (string) $bitly->shorten($fullUrl);
       
         //return $url;


// Insert columns.
$jsColumns = array('member_name', 'company_name', 'facebook_page_url', 'google_placeid', 'yelp_review_page', 'google_review_url', 'get_bit_ly', 'company_logo', 'nia_agent_id', 'installment_plan');

// Insert values.
$values = array(
 $jsDb->quote($newuserid),
 $jsDb->quote('{afab_repman_signup___company_name}'),
 $jsDb->quote('{afab_repman_signup___facebook_page_url}'),
 $jsDb->quote('{afab_repman_signup___google_placeid}'),
 $jsDb->quote('{afab_repman_signup___yelp_review_page}'),
 $jsDb->quote('{afab_repman_signup___google_review_url}'),
 $jsDb->quote($url),
 $jsDb->quote($companylogo),
 $jsDb->quote('{afab_repman_signup___nia_agent_id}'),
 $jsDb->quote('{afab_repman_signup___installment_plan}')
    );

// Prepare the insert query.
$jsQuery
    ->insert($jsDb->quoteName('review_feature'))
    ->columns($jsDb->quoteName($jsColumns))
    ->values(implode(',',$values));

// Reset the query using our newly populated query object.
 $jsDb->setQuery($jsQuery);
try {
// Execute the query
  $result = $jsDb->execute();
//use $jsDb->query() in Joomla2.5
}
catch (Exception $e) {
// catch any database errors.

}

/////////////////////////////////////////////////////
/// Begin Entering into 12_a_la_carte_memberships ///
/////////////////////////////////////////////////////



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



//get form data
$base = 1600;
$installmentConstant = (int) '{afab_repman_signup___installment_plan_raw}';
$instAmtPaid = (int) '{afab_repman_signup___installment_plan_raw}';
$numOfInstallments = $base / $installmentConstant;
$count = 1;
$date = $formModel->getElementData('afab_repman_signup___first_payment_date');
// $convdate = 'CONVERT_TZ(' . $myDb->quote($date->format('Y-m-d')) . ', "+0:00", "+6:00")';
// $date = new DateTime($date);
$myDateFormat = $date->modify('+1 month')->format('Y-m-d H:i:s');
$formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
// $date = strtotime($date);
$dt = new DateTime($date);
$nonMemberRepman = "RepMan (Non Memeber)";

$myQuery->insert('12_a_la_carte_memberships');
$myQuery->columns(
   array(
      $myDb->quoteName('date_time'),
      $myDb->quoteName('member_id'),
      $myDb->quoteName('agent_id'),
      $myDb->quoteName('installment_amounts'),
      $myDb->quoteName('activation_date'),
      $myDb->quoteName('service_name'),
      $myDb->quoteName('member_name'),
      $myDb->quoteName('company_name'),
      $myDb->quoteName('agent_name'),
      $myDb->quoteName('number_of_installments'),
      $myDb->quoteName('installment_number'),
      $myDb->quoteName('franchise_fee'),
      $myDb->quoteName('franchise_charge')
   )
);

while ( $installmentConstant <= $base ) {
// $date = 'CONVERT_TZ(' . $myDb->quote($date->format('Y-m-d')) . ', "+0:00", "+6:00")';
  $myQuery->values(
      implode(',',
            array(
                $myDb->quote('{afab_repman_signup___date_time}'),
                $myDb->quote('{afab_repman_signup___new_user_id}'),
                $myDb->quote('{afab_repman_signup___nia_agent_id}'),
                $myDb->quote($installmentConstant),
                $myDb->quote($date),
                $myDb->quote($nonMemberRepman),
                $myDb->quote('{afab_repman_signup___full_name}'),
                $myDb->quote('{afab_repman_signup___company_name}'),
                $myDb->quote('{afab_repman_signup___agent_name}'),
                $myDb->quote($numOfInstallments),
                $myDb->quote($count),
                $myDb->quote('{afab_repman_signup___franchise_fee}'),
                $myDb->quote('{afab_repman_signup___franchise_charge}')
        )
      )
   );
    //echo $date . " Repman installment " . $count . " of " . $numOfInstallments . " $" . $instAmtPaid . "<br />";
    $installmentConstant = $installmentConstant + $instAmtPaid;
    // $date = strtotime( "+1 months", $date );
    // $date = date( 'Y-m-d', $repeat );
  //  $myDateFormat = $date->modify('+1 month')->format('Y-m-d H:i:s');
  // $formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
    // $formModel->updateFormData($date, $myDateFormat, true);
    // $date = new DateTime($date);
    $count++;
}

// run the query
$myDb->setQuery($myQuery);
// uncomment this line if you need to debug
var_dump($date);exit;
// $myDb->execute();
As always, your help and guidance is valued.

Cheers!
 
I would have thought this should work (I've just copied s fragment of your code, everything before and after stays the same)

Code:
//get form data
$base = 1600;
$installmentConstant = (int) '{afab_repman_signup___installment_plan_raw}';
$instAmtPaid = (int) '{afab_repman_signup___installment_plan_raw}';
$numOfInstallments = $base / $installmentConstant;
$count = 1;
$date = $formModel->getElementData('afab_repman_signup___first_payment_date');
$date = new DateTime($date);
$myDateFormat = $date->modify('+1 month')->format('Y-m-d H:i:s');
$formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
$nonMemberRepman = "RepMan (Non Memeber)";

$myQuery->insert('12_a_la_carte_memberships');
$myQuery->columns(
   array(
      $myDb->quoteName('date_time'),
      $myDb->quoteName('member_id'),
      $myDb->quoteName('agent_id'),
      $myDb->quoteName('installment_amounts'),
      $myDb->quoteName('activation_date'),
      $myDb->quoteName('service_name'),
      $myDb->quoteName('member_name'),
      $myDb->quoteName('company_name'),
      $myDb->quoteName('agent_name'),
      $myDb->quoteName('number_of_installments'),
      $myDb->quoteName('installment_number'),
      $myDb->quoteName('franchise_fee'),
      $myDb->quoteName('franchise_charge')
   )
);

while ( $installmentConstant <= $base ) {
  $myQuery->values(
      implode(',',
            array(
                $myDb->quote('{afab_repman_signup___date_time}'),
                $myDb->quote('{afab_repman_signup___new_user_id}'),
                $myDb->quote('{afab_repman_signup___nia_agent_id}'),
                $myDb->quote($installmentConstant),
                $myDb->quote($date),
                $myDb->quote($nonMemberRepman),
                $myDb->quote('{afab_repman_signup___full_name}'),
                $myDb->quote('{afab_repman_signup___company_name}'),
                $myDb->quote('{afab_repman_signup___agent_name}'),
                $myDb->quote($numOfInstallments),
                $myDb->quote($count),
                $myDb->quote('{afab_repman_signup___franchise_fee}'),
                $myDb->quote('{afab_repman_signup___franchise_charge}')
        )
      )
   );
    $installmentConstant = $installmentConstant + $instAmtPaid;
   $myDateFormat = $date->modify('+1 month')->format('Y-m-d H:i:s');
   $formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
    $count++;
}

So just set $date up with "new Datetime()" once, to turn it into a PHP date object, then just use modify() on that in the loop.

-- hugh
 
I've tried the code you've laid out here, and the form submits, but again, it does not capture and increment the months. see attached.
 

Attachments

  • fabrik-loop.jpg
    fabrik-loop.jpg
    214.8 KB · Views: 65
Well, hang on, is that date supposed to be written out with your custom query? If so, then you need to set whatever the field you want the date in, in your values array.
 
Okay, I think I see what you're saying. So I passed $myDateFormat into the activation_date column and it captures the date, but it does not increment the month to month date. Here's how the code looks now.

Code:
//get form data
$base = 1600;
$installmentConstant = (int) '{afab_repman_signup___installment_plan_raw}';
$instAmtPaid = (int) '{afab_repman_signup___installment_plan_raw}';
$numOfInstallments = $base / $installmentConstant;
$count = 1;
$date = $formModel->getElementData('afab_repman_signup___first_payment_date');
$date = new DateTime($date);
$myDateFormat = $date->modify('+1 month')->format('Y-m-d H:i:s');
$formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
$nonMemberRepman = "RepMan (Non Memeber)";

$myQuery->insert('12_a_la_carte_memberships');
$myQuery->columns(
   array(
      $myDb->quoteName('date_time'),
      $myDb->quoteName('member_id'),
      $myDb->quoteName('agent_id'),
      $myDb->quoteName('installment_amounts'),
      $myDb->quoteName('activation_date'),
      $myDb->quoteName('service_name'),
      $myDb->quoteName('member_name'),
      $myDb->quoteName('company_name'),
      $myDb->quoteName('agent_name'),
      $myDb->quoteName('number_of_installments'),
      $myDb->quoteName('installment_number'),
      $myDb->quoteName('franchise_fee'),
      $myDb->quoteName('franchise_charge')
   )
);

while ( $installmentConstant <= $base ) {
  $myQuery->values(
      implode(',',
            array(
                $myDb->quote('{afab_repman_signup___date_time}'),
                $myDb->quote('{afab_repman_signup___new_user_id}'),
                $myDb->quote('{afab_repman_signup___nia_agent_id}'),
                $myDb->quote($installmentConstant),
                $myDb->quote($date),
                $myDb->quote($nonMemberRepman),
                $myDb->quote('{afab_repman_signup___full_name}'),
                $myDb->quote('{afab_repman_signup___company_name}'),
                $myDb->quote('{afab_repman_signup___agent_name}'),
                $myDb->quote($numOfInstallments),
                $myDb->quote($count),
                $myDb->quote('{afab_repman_signup___franchise_fee}'),
                $myDb->quote('{afab_repman_signup___franchise_charge}')
        )
      )
   );
    $installmentConstant = $installmentConstant + $instAmtPaid;
   $myDateFormat = $date->modify('+1 month')->format('Y-m-d H:i:s');
   $formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
    $count++;
}
 

Attachments

  • fabrik-loop-2.jpg
    fabrik-loop-2.jpg
    167.1 KB · Views: 67
Yep. It's working. I hadn't saved the change I made and ran a test. After I passed $myDateFormat into the activation_date column it worked :) Thanks Hugh! I was lost in the weeds and you saved the day! Bless you kind sir!
 
Glad you got it working. This kind of thing is frustrating from where I sit, as it's the kind of code I could write in literally two minutes for myself, when I know exactly what I'm trying to achieve, which table is which, etc ... but trying to help someone else ... when I'm not 100% sure what you are trying to do, or what element is what, and I don't have all the code, and I'm not sure exactly what changes you are applying ... it can take forever. Which is kind of why we don't do "all you can eat" subscription support any more.

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

Thank you.

Members online

Back
Top