1. Fabrik 3.8.1 has been released. It is mostly bug fixes and feature enhancements, but does include two new plugins (push notifications, and the sequence element). As usual we strongly recommend testing the new release on a sandbox if your application is mission critical, and always do an Akeeba backup before updating.
    Dismiss Notice

[SOLVED] 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'];
     
  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 Administrator Staff Member

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

    cheesegrits Support Gopher Staff Member

    Level: Community
    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: Community
    N/p, apologies for the delay in answering.

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

Share This Page