Problem array in calc element

[louis037]

Hello everyone!
Just a question : I try to get times form database. The request is based on a calc which search the values of times on a db, . The ajax calculation is based on a multiple dropdown select in a databasejoin element called '{conventions___targeted_competence_raw}'.
Here's my code :

$competenceTarget = '{conventions___targeted_competence_raw}';
if ($competenceTarget > 0){
  $myDb = FabrikWorker::getDbo( false, 3 );
  $myQuery = $myDb->getQuery( true );
  $myQuery->select( 'exam_duration' )->from( 'data_reac_activity_types' )->where( 'id IN (' .    $myDb->quote( $competenceTarget ) . ')' );
  $myDb->setQuery( $myQuery );
  $results = $myDb->loadAssocList();
  //$results = implode(',', $results);
  //var_dump($results);
return $results;
} else {
  return '0';
}

The form shows an error : "Notice: Array to string conversion in..."
When I write :

return $competenceTarget;

The calc element shows the values (2,6...) as comma separated values.
I want also add the values of 'exam_duration' (in database the values are in HH:mm:ss. I get it with this code for one value :

$actionTitle = (int) '{conventions___action_title_raw}';
$myDb = FabrikWorker::getDbo( false, 3 );
$myQuery = $myDb->getQuery( true );
$myQuery->select( 'exam_duration' )->from( 'data_reac_professional_titles' )->where( 'id = ' . $myDb->quote( $actionTitle ) );
$myDb->setQuery( $myQuery );
$result = $myDb->loadResult();
$parsed = date_parse( $result );
$resultsSeconds = $parsed[ 'hour' ] * 3600 + $parsed[ 'minute' ] * 60 + $parsed[ 'second' ];
$resultHours = ceil( $resultsSeconds / 60 / 60 );
return $resultHours;

How can I manage to do it on multiple values?

Thank you very much

 

[cheesegrits]

Well, when you do ...

$results = $myDb->loadAssocList();

... $results is an array. So you can't just return that as the value of the calc (you can only return scalar values ... string or number).

So you need to process that array and do whatever it is you want to do with each value, and create a single string to return. But I'm not sure what you are trying to do.

-- hugh

 

[louis037]

Hello cheesegrits, thanks you for your quick reply.
What I'm trying to do is to get in $competenceTarget = '{conventions___targeted_competence_raw}' the ids from a table. They are loaded by a multiple select. These ids are for make my request :
I try to get the times (stored as HH:mm:ss) from another table and add them up to make a big total of these values.
The total is necessarily in hours.
I don't manage to work with the values from '{conventions___targeted_competence_raw} which can be an array : 7,5,3... or a single value, and I'm not able to do the correct request to get the corresponding values from my table 'data_reac_activity_types'. Moreover, the addition seems complicated...

Thank you for helping me. ps. My website is in my profile (crm-erp.hpc......com)

 

[cheesegrits]

Well, that first query gives you and array of the durations, so just loop round that the results and total them up. Something like ...

$total = 0;
foreach ($results as $result) {
   $time = $result['exam_duration'];
   $seconds = 0;
   foreach (array_reverse(explode(':', $time)) as $k => $v) {
      $seconds += pow(60, $k) * $v;
   }
   $total += $seconds;
}
$h = floor($seconds /3600);
$m = floor(($seconds %3600)/60);
$s = $seconds %60;
return sprintf("%d:%02d:%02d", $h, $m, $s);

Oh, and don't check "if ($competenceTarget > 0){", do "if (!empty($competanceTarget)) {"

-- hugh

 

[louis037]

Thanks, I'll try that and tell you if it works properly.
hugh

 

[louis037]

Well...
It doesn't work : it gives me just the first value. It's better but when I select multiple values in multiple select "targeted_competences", it gives me the first value.
here's my code :

$competenceTarget = '{conventions___targeted_competence_raw}';
if ( !empty( $competenceTarget ) ) {
    $myDb = FabrikWorker::getDbo( false, 3 );
    $myQuery = $myDb->getQuery( true );
    $myQuery->select( 'exam_duration' )->from( 'data_reac_activity_types' )->where( 'id IN (' . $myDb->quote( $competenceTarget ) . ')' );
    $myDb->setQuery( $myQuery );
    $results = $myDb->loadAssocList();
    //$results = implode(',', $results);
    $total = 0;
    foreach ( $results as $result ) {
        $time = $result[ 'exam_duration' ];
        $seconds = 0;
        foreach ( array_reverse( explode( ':', $time ) ) as $k => $v ) {
            $seconds += pow( 60, $k ) * $v;
        }
        $total += $seconds;
    }
    $h = floor( $seconds / 3600 );
    $m = floor( ( $seconds % 3600 ) / 60 );
    $s = $seconds % 60;
    return sprintf( "%d:%02d:%02d", $h, $m, $s );
}
else {
    return '0';
}

The calc element is "conventions___calc_ccp" : there's in which I pasted my code.
The link to the form is : http://crm-erp.hpc-formation.com/formulaires-administratifs/conventions/form/8/1
http://crm-erp.hpc-formation.com/formulaires-administratifs/conventions/form/8/1
Maybe I try something wrong, but where????

 

[cheesegrits]

Ooops, should be $total, not $seconds, in the part after the foreach.

-- hugh

 

[louis037]

Thank you.... But it doesn't work : it shows only the first value in HH:mm:ss. Here's the modified code :

//Get the total hours from CCP
$competenceTarget = '{conventions___targeted_competence_raw}';
if ( !empty( $competenceTarget ) ) {
    $myDb = FabrikWorker::getDbo( false, 3 );
    $myQuery = $myDb->getQuery( true );
    $myQuery->select( 'exam_duration' )->from( 'data_reac_activity_types' )->where( 'id IN (' . $myDb->quote( $competenceTarget ) . ')' );
    $myDb->setQuery( $myQuery );
    $results = $myDb->loadAssocList();
    //$results = implode(',', $results);
    $total = 0;
    foreach ( $results as $result ) {
        $time = $result[ 'exam_duration' ];
        $seconds = 0;
        foreach ( array_reverse( explode( ':', $time ) ) as $k => $v ) {
            $seconds += pow( 60, $k ) * $v;
        }
        $total += $seconds;
    }
    $h = floor( $total / 3600 );
    $m = floor( ( $total % 3600 ) / 60 );
    $s = $total % 60;
    return sprintf( "%d:%02d:%02d", $h, $m, $s );
}
else {
    return '0';
}

Where I'm wrong?
Thanks

 

[Sophist]

Why not add code to add debugging to the fabrikdebug log e.g. dump the $myQuery and $results so you can see exactly what is being selected.

See http://fabrikar.com/forums/index.ph...allways-on-the-top-of-page.48185/#post-250607 for details.

 

[louis037]

Hello Sophist,

When I add

&fabrikdebug=1

Nothing happens althrough I activated the debug in params in fabrik...

 

[Sophist]

Yes - you do need to activate debug in Fabrik's options.

If there are no other query parameters, then you need to use "?fabrikdebug=1" instead.

 

[louis037]

Yes, It is! I'm crazy...

 

[Sophist]

Let us know how you get on and what the problem was.

 

[Sophist]

Did you get this sorted? What was the problem?

 

[louis037]

I'm sorry to not have responded to you, there's no problem now, I just replaced the '?' by '&'