1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

adding months in while loop | Date calculation

Discussion in 'Community' started by SoilentRed, Sep 5, 2019.

  1. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    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 (Text):

    $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 (Text):
     
        $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 (Text):

    $date = $formModel->getElementData('afab_repman_signup___first_payment_date');
     
    actually returns the date, but adding

    Code (Text):

    $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
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    First off, check that you get a date, so ...

    Code (Text):

    // 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 (Text):

        $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 (Text):

        $formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
     
    Then you need to get rid of the ...

    Code (Text):

        $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
     
  3. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    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 (Text):

    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 (Text):
    $formModel->updateFormData($date, $myDateFormat, true);
    with
    Code (Text):
    $formModel->updateFormData('afab_repman_signup___first_payment_date', $myDateFormat, true);
    and adding
    Code (Text):
    $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 (Text):
    $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!
     
  4. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    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.
     
  5. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    Also, modify seems to be having a hard time
    I keep getting this back
    Code (Text):

    0 - Call to a member function modify() on string
     
     
  6. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    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 (Text):

    string(19) "2019-09-06 00:00:00"
     
    And when you convert it with $date = new DateTime($date); it returns... an array?
    Code (Text):

    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 (Text):

    0 - Call to a member function modify() on string
     
  7. juuser

    juuser Member

    Level: Community
    Hi,

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

    Code (Text):
    $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.
     
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    I'd have to see your full modified code again, as I don't know what changes you made.

    -- hugh
     
  9. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    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 (Text):

    // 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!
     
  10. cheesegrits

    cheesegrits Support Gopher Staff Member

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

    Code (Text):

    //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
     
  11. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    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.
     

    Attached Files:

  12. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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.
     
    SoilentRed likes this.
  13. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    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 (Text):

    //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++;
    }
     
     

    Attached Files:

  14. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    nevermind. didnt save. I think we got it.
     
  15. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    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!
     
  16. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     

Share This Page