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

add 3 rows element calc

Discussion in 'Community' started by albertotactik, Oct 17, 2019.

  1. troester

    troester Well-Known Member Staff Member

    Level: Community
    No idea what you are doing.
    Your code is working fine (after substituting with my table and column names).
    What do you get by adding
    echo $query; exit;
     
  2. albertotactik

    albertotactik Member

    Level: Community
    hello!
    if I remove these 2 lines in red ...
    if it returns a value ...

    $ expedient = '{gpimpagats___gp_imp_cuotaimpagat_raw}';
    // get "annual deute" sum
    $ mydb = JFactory :: getDbo ();
    $ query = $ mydb-> getQuery (true);
    $ query-> select ('SUM ('. $ mydb-> quoteName ('gp_imp_totaldeute'). ')');
    $ query-> from ($ mydb-> quoteName ('gpimpagats'));
    // $ query-> where ($ mydb-> quoteName ('gp_imp_cuotaimpagat'). '='. $ mydb-> quote ($ expedient));
    // $ mydb-> setQuery ($ query);

    $ total_deute_anual = $ mydb-> loadResult ();
    // return result (in all rows)
    return $ total_deute_anual;
     
  3. albertotactik

    albertotactik Member

    Level: Community
    blank screen
     
  4. troester

    troester Well-Known Member Staff Member

    Level: Community
    With this
    Code (Text):
    $expedient = '{gpimpagats___gp_imp_cuotaimpagat_raw}';
    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('gp_imp_totaldeute').')');
    $query->from($mydb->quoteName('gpimpagats'));
    $query->where($mydb->quoteName('gp_imp_cuotaimpagat').' = '.$mydb->quote($expedient));
    echo $query; exit;
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;
     
  5. albertotactik

    albertotactik Member

    Level: Community
    guauuuu show this ... !!

    SELECT SUM(`gp_imp_totaldeute`) FROM `gpimpagats` WHERE `gp_imp_cuotaimpagat` = '20'
    :)

     
  6. albertotactik

    albertotactik Member

    Level: Community
    hello!!
    if i delete this line of code
    // echo $ query; exit;
    error 500 reappears :(

    The engine of the table is InnoDB
    in case it can be of help in information and detect the possible error
     
  7. lousyfool

    lousyfool Active Member

    Level: Community
    I think some information is missing or wrong. Please check and tell:

    1. The table name is "gpimpagats"? No prefix? (Like e.g. "xyz_gpimpagats" or so?)
    2. "gp_imp_cuotaimpagat" is the Fabrik name of the element with label "Expedient", yes?
    3. What element type is "gp_imp_cuotaimpagat"?
    4. "gp_imp_totaldeute" is the Fabrik name of a numeric element, yes?
    5. What element type is "gp_imp_totaldeute"?
     
  8. lousyfool

    lousyfool Active Member

    Level: Community
    6. If "gp_imp_totaldeute" is one of your other calc fields ("Deute anual"?), please also post the code you're using there.
     
  9. albertotactik

    albertotactik Member

    Level: Community
    I answer the 5 questions ... to see if there is any inconsistency
    Thanks for the help to detect my mistake.

    1x Yes, the name of the table is "gpimpagats" without prefix. InnoDB engine
    2x "gp_imp_cuotaimpagat" is the fabrik name of the element (field) with the informative label "renda unpaid gener"
    3x the type of "gp_imp_cuotaimpagat" is "field" and in the database it is defined as "varchar" (it should be "int"?)
    4x It is not a numerical type… it is a “calc” type and it is where the php code that adds all the annual elements….
    5x the element "gp_imp_totaldeute" is of type "calc"

    6. code calc "gp_imp_totaldeute"
    $myCalcdeute = ( ( int ) '{gpimpagats___gp_imp_cuotaimpagat}' + '{gpimpagats___gp_imp_cuotaimpagat2}' + '{gpimpagats___gp_imp_cuotaimpagat3}' + '{gpimpagats___gp_imp_cuotaimpagat4}' + '{gpimpagats___gp_imp_cuotaimpagat5}' + '{gpimpagats___gp_imp_cuotaimpagat6}' + '{gpimpagats___gp_imp_cuotaimpagat7}' + '{gpimpagats___gp_imp_cuotaimpagat8}' + '{gpimpagats___gp_imp_cuotaimpagat9}' + '{gpimpagats___gp_imp_cuotaimpagat10}' + '{gpimpagats___gp_imp_cuotaimpagat11}' + '{gpimpagats___gp_imp_cuotaimpagat12}');
    return $myCalcdeute ;

    thanks!!
     
  10. lousyfool

    lousyfool Active Member

    Level: Community
    I don't understand... this doesn't make sense to me...

    To my question #2 you confirm that "gp_imp_cuotaimpagat" is the element for "Expedient" (= user ID or name), not any amount.
    But in your other existing calc element you're adding various of them up! So must getting a sum of various user IDs?!

    In your existing calc elements, shouldn't you be adding up elements which are holding amounts (labeled "Deute something")?

    After all investigations, trial & error with the infos given, I think what would help best at this point would be a screenshot of the "gpimpagats" table in phpMyAdmin, at least the column headers and 2-3 rows...
    As you may be unfamiliar with it: just note that you do not want to do anything in phpMyAdmin... just open the table, take a screenshot, then close the browser tab!
     
  11. albertotactik

    albertotactik Member

    Level: Community
    very good ...
    I have been studying the code, tables and elements since this weekend to clarify myself.
    I try to summarize you with a jpg and a current code.
    I would like to review it from this "calc" code attached
    let's see if we could find the error ...
    If you need any screenshot or detail, tell me.
    I think we are very close to the error.

    Thanks+thanks in advance...

    Result:
    489 SELECT SUM(`gp_imp_totalpendent`) FROM `gpimpagats` WHERE `gp_imp_expedient` = '489'


    $expedient = '{gpimpagats___gp_imp_expedient_raw}';
    // take the value of the "file" and display it on the screen, to verify that it takes the value 489
    echo $expedient;
    // shows the file value = 489 (OK)

    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('gp_imp_totalpendent').')');
    // ' gp_imp_totalpendent 'there are 2 rows with the value 9 and 10
    $query->from($mydb->quoteName('gpimpagats'));
    $query->where($mydb->quoteName('gp_imp_expedient').' = '.$mydb->quote($expedient));
    // select the 2 rows that match the file value 489 ('gp_imp_expedient'=489)
    echo $query; exit;
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;
     

    Attached Files:

  12. lousyfool

    lousyfool Active Member

    Level: Community
    It helps a little bit, but it's still confusing because, at least for me, element labels and element/column names are not the same, or even only clearly related.
    This is getting too long here, we have to quit guessing and trial & error. So, simple questions based on your original image as here attached again:

    7. The Fabrik element name = DB column name for the label "Expedient" is "gp_imp_expedient", correct?
    8. What is the Fabrik element name = DB column name for the label "Deute anual abonat"?
    9. What is the Fabrik element name = DB column name for the label "Deute anual"?
    10. What is the Fabrik element name = DB column name for the label "Deute anual pendent"?
    11. None of the fields in questions #8 - #10 here above are calc elements, correct?

    With this information -- if correct -- it'll be easy to create the queries for your 3 new calc elements.
     

    Attached Files:

  13. lousyfool

    lousyfool Active Member

    Level: Community
    P.S.:
    12. What is the Fabrik element name = DB column name for the label "Any impagat" (= year)?
    If that's known, it's also easy to display the totals only in rows with the most recent year, not in rows of previous years.
     
  14. troester

    troester Well-Known Member Staff Member

    Level: Community
    What do you get if you put
    SELECT SUM(`gp_imp_totalpendent`) FROM `gpimpagats` WHERE `gp_imp_expedient` = '489'
    directly in phpMyAdmin?
     
  15. albertotactik

    albertotactik Member

    Level: Community
    hello!!
    I answer questions 7 to 12.
    Thanks and if necessary, I would answer 100 more questions.
    I appreciate the patience and dedication, with this topic.

    7. The Fabrik element name = DB column name for the label "Expedient" is "gp_imp_expedient", correct?
    gp_imp_expedient Expedient gpimpagats___gp_imp_expedient 0/0 Impagats databasejoin

    8. What is the Fabrik element name = DB column name for the label "Deute anual abonat"?
    gp_imp_totalpagat Deute anual abonat gpimpagats___gp_imp_totalpagat 0/0 calculimpagatsgrup calc

    9. What is the Fabrik element name = DB column name for the label "Deute anual"?
    gp_imp_totaldeute Deute anual gpimpagats___gp_imp_totaldeute 0/0 calculimpagatsgrup calc

    10. What is the Fabrik element name = DB column name for the label "Deute anual pendent"?
    gp_imp_totalpendent Deute anual pendent gpimpagats___gp_imp_totalpendent 0/0 calculimpagatsgrup calc

    11. None of the fields in questions #8 - #10 here above are calc elements, correct?
    yes ... gpimpagats___gp_imp_totalpagat, gpimpagats___gp_imp_totaldeute and gpimpagats___gp_imp_totalpendent, they are "calc" elements

    12. What is the Fabrik element name = DB column name for the label "Any impagat" (= year)?
    gp_imp_anyimpagat Any Impagat gpimpagats___gp_imp_anyimpagat 0/0 Impagats date

    value = year
    2019-10-21 12:10:46
    2018-10-16 06:07:00
     
  16. albertotactik

    albertotactik Member

    Level: Community
    hello!!
    If I make a query from the Navicat program.
    I get the following error ...
    SQL]SELECT SUM (`gp_imp_totalpendent`) FROM` gpimpagats` WHERE `gp_imp_expedient` = '489'
    [Err] 1146 - Table 'bdgpfabrik. gpimpagats' doesn't exist
    thanks!!
     
  17. troester

    troester Well-Known Member Staff Member

    Level: Community
    Ok, if it doesn't work directly on your DB it obviously can't work in Fabrik.
    Seems you are on the wrong database.
     
  18. albertotactik

    albertotactik Member

    Level: Community
    Hello...
    that the engine is InnoDB ... represents a problem?
    Or does it have to be MyISAM engine?
    Thank you
     
  19. troester

    troester Well-Known Member Staff Member

    Level: Community
    No, the error message says there's no table gpimpagats on the database bdgpfabrik
     
  20. troester

    troester Well-Known Member Staff Member

    Level: Community
    There seems to be a space before gpimpagats
    SELECT SUM (`gp_imp_totalpendent`) FROM` gpimpagats` WHERE `gp_imp_expedient` = '489'
    [Err] 1146 - Table 'bdgpfabrik. gpimpagats' doesn't exist

    Try to change
    $query->from($mydb->quoteName('gpimpagats'));
    to
    $query->from('gpimpagats');
     

Share This Page