1. We suggest you do NOT update to Joomla 3.8.10 until we can resolve an issue it causes with caching in Fabrik. If you do install it, you'll need to disable Joomla's "System Cache" in the global System settings.
  2. Apologies for the recent server outage, a planned migration by our host provider to a new location turned into a bit of a nightmare.

calculation...

Discussion in 'Professional Support' started by bea, Jun 22, 2018.

Thread Status:
Not open for further replies.
  1. bea

    bea Active Member

    Level: Professional
    Hi,
    it seems, that I'm out of Fabrik....

    I need to calculate the order of records with same date and same shift, because I need it to get the right data from another database. There will be always 3 records within a shift and they should numbered like 1,2,3.
    I've no idea how to write the calc ...
    Cheers,
    Bianka

    Elements:
    $shift = $data['fab_cp1_line_tour___shift_raw'];
    $date = $data['fab_cp1_line_tour___date_raw'];
    $id = $data['fab_cp1_line_tour___id_raw'];
     

    Attached Files:

  2. bea

    bea Active Member

    Level: Professional
    Hi,
    I've solved myself with 2 different scripts. Don't know, which one is better...
    update: I had to add the date element, but in second script it doesn't work (error)
    Cheers


    PHP:

    $db = JFactory::getDbo();
    $date ='{fab_cp1_line_tour___date_raw}';
    $shift = (int)'{fab_cp1_line_tour___shift_raw}';
    $id = '{fab_cp1_line_tour___id_raw}';
    $query = $db->getQuery(true);
    $query
      ->select('MAX(id)')
      ->from('fab_cp1_line_tour')
      ->where('date= ' . $db->quote($date))
      ->where('shift = ' . $db->quote($shift));
    $db->setQuery($query);
    $maxid  = $db->loadResult();
    $query = $db->getQuery(true);
    $query
      ->select('MIN(id)+1')
      ->from('fab_cp1_line_tour')
      ->where('date= ' . $db->quote($date))
      ->where('shift = ' . $db->quote($shift));
    $db->setQuery($query);
    $midid  = $db->loadResult();
    $query = $db->getQuery(true);
    $query
         ->select('MIN(id)')
         ->from('fab_cp1_line_tour')
      ->where('date= ' . $db->quote($date))
      ->where('shift = ' . $db->quote($shift));
    $db->setQuery($query);
    $minid  = $db->loadResult();
    if ($minid == $id) {
    return '1';}
    if ($midid == $id) {
    return '2';}
    if ($maxid == $id) {
    return '3';}
    else {return '';}
     
    PHP:
    $db = JFactory::getDbo();
    $date ='{fab_cp1_line_tour___date_raw}';
    $shift = '{fab_cp1_line_tour___shift_raw}';
    $id = '{fab_cp1_line_tour___id_raw}';
    $db = & JFactory::getDBO();
    $query = "SELECT MAX(id) FROM `fab_cp1_line_tour` WHERE `shift` = $shift AND `date` = $date";
    $db->setQuery($query);
    $maxid =  $db->loadResult();
    $query = "SELECT MIN(id)+1 FROM `fab_cp1_line_tour` WHERE `shift` = $shift AND `date` = $date";
    $db->setQuery($query);
    $midid =  $db->loadResult();
    $query = "SELECT MIN(id) FROM `fab_cp1_line_tour` WHERE `shift` = $shift AND `date` = $date";
    $db->setQuery($query);
    $minid =  $db->loadResult();
    if ($minid == $id) {
    return '1';}
    if ($midid == $id) {
    return '2';}
    if ($maxid == $id) {
    return '3';}
    else {return '';}
     
    Last edited: Jun 23, 2018
  3. bea

    bea Active Member

    Level: Professional
    Friendly bump
     
  4. troester

    troester Well-Known Member Staff Member

    Level: Standard
    You didn't quote $date in your 2nd script.
     
  5. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    IMHO, it's always better to use the query builder rather than construct queries by hand, unless you need to do something the query builder can't handle.

    And as you are querying the same table with the same conditions, you could do it in a single query instead of three:

    Code (Text):

    $db = JFactory::getDbo();
    $date = '{fab_cp1_line_tour___date_raw}';
    $shift = (int)'{fab_cp1_line_tour___shift_raw}';
    $id = '{fab_cp1_line_tour___id_raw}';

    $query = $db->getQuery(true);
    $query
       ->select('MAX(id) AS maxid')
       ->select('MIN(id)+1 AS midid')
       ->select('MIN(id) AS minid')
       ->from('fab_cp1_line_tour')
       ->where('date= ' . $db->quote($date))
       ->where('shift = ' . $db->quote($shift));
    $db->setQuery($query);
    $results = $db->loadObject();

    if ($results->minid == $id) {
       return'1';
    }
    else if ($results->midid == $id) {
       return'2';
    }
    else if ($results->maxid == $id){
       return'3';
    }
    else {
       return'';
    }
     
    -- hugh
     
  6. bea

    bea Active Member

    Level: Professional
    Many thanks!
     
  7. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    N/p, apologies for the delay in answering.

    -- hugh
     
Thread Status:
Not open for further replies.

Share This Page