[SOLVED] Calculation with data

[bea]

Hi,
I've tried to optimize a calculation with data from different databases, but without success.
The calculation works correct, but I want to get scrap2 and ist_bags with one query to calculate $total at the end. With array and loadObjectList doesn't work.
Would be great to get a hint for this.

Cheers,
Bianka

$date ='{fab_cp4_line_tour___date_raw}';
$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
    ->select('SUM(losses3_scrap1)')
    ->from('fab_cp1_line_tour')
    ->where('date= ' . $myDb->quote($date))
    ->where('machine= 7')
    ->where('status= 2')
    ->where('shift_records = shiftorder');
$myDb->setQuery($myQuery);
$scrap2 = $myDb->loadResult();

$myDb = FabrikWorker::getDbo(false, 2);
$myQuery = $myDb->getQuery(true);
$myQuery
    ->select('h24_scrap2')
    ->from('24h_daten')
    ->where('date= ' . $myDb->quote($date))
    ->where('machine= 7');
$myDb->setQuery($myQuery);
$scrap2 = $myDb->loadResult();
$myDb = FabrikWorker::getDbo(false, 2);
$myQuery = $myDb->getQuery(true);
$myQuery
    ->select('h24_ist_bags')
    ->from('24h_daten')
    ->where('date= ' . $myDb->quote($date))
    ->where('machine= 7');
$myDb->setQuery($myQuery);
$ist_bags = $myDb->loadResult();
$total = ((($scrap1+$scrap2)*100) /$ist_bags);

if ($total == NULL) {
return '';}
else return sprintf('%01.1f',$total).'%';

Test with loadObjectList:

$myDb = FabrikWorker::getDbo(false, 2);
$myQuery = $myDb->getQuery(true);
$myQuery
    ->select(array('h24_scrap2','h24_ist_bags'))
    ->from('24h_daten')
    ->where('date= ' . $myDb->quote($date))
    ->where('machine= 7');
$myDb->setQuery($myQuery);
$rows = $myDb->loadObjectList();
var_dump($rows);exit;

Result:

array(1) { [0]=> object(stdClass)#4648 (2) { ["h24_scrap2"]=> string(4) "1000" ["h24_ist_bags"]=> string(5) "20000" } }

 

[troester]

instead of var_dump

foreach ($rows as $row) {
$scrap2 = $row->h24_scrap2;
..


Gesendet von meinem SM-G930F mit Tapatalk

 

[cheesegrits]

The loadObjectList() returns an array of objects, one array entry per row selected. If you are only expecting one row in the result, just do loadObject() instead ...

$row = $myDb->loadObject();

... then you'll have $row->ht4_scrap2 and $row->h24_ist_bags.

-- hugh

 

[bea]

Hi troester, hi Hugh,
many thanks for your support. Finally loadObject works.
Cheers,
Bianka

$date ='{fab_cp4_line_tour___date_raw}';
$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
    ->select('SUM(losses3_scrap1)')
    ->from('fab_cp1_line_tour')
    ->where('date= ' . $myDb->quote($date))
    ->where('machine= 7')
    ->where('status= 2')
    ->where('shift_records = shiftorder');
$myDb->setQuery($myQuery);
$scrap2 = $myDb->loadResult();

$myDb = FabrikWorker::getDbo(false, 2);
$myQuery = $myDb->getQuery(true);
$myQuery
    ->select(array('h24_scrap2','h24_ist_bags'))
    ->from('24h_daten')
    ->where('date= ' . $myDb->quote($date))
    ->where('machine= 7');
$myDb->setQuery($myQuery);
$row = $myDb->loadObject();
$scrap1 = $row->h24_scrap2;
$ist_bags = $row->h24_ist_bags;
$total = (($scrap1+$scrap2)*100) /$ist_bags;
if ($total == NULL) {
return '';}
else return sprintf('%01.1f',$total).'%';

 

[cheesegrits]

You should probably put the sanity check around the loadObject result, otherwise if that query doesn't return anything, it'll blow up with a fatal 'cannot access property of null' or some such when you try and user $row->whatever. Also make sure $scrap2 isn't empty.

$row=$myDb->loadObject();
if (!empty($row) && !empty($scrap2)) {
   $total = (($row->h24_scrap2 + $scrap2) * 100) / $row->h24_ist_bags;
   return sprintf('%01.1f',$total).'%';
}
return '';

Also, assigning an object to a variable before you use it, like ...

$scrap1 = $row->h24_scrap2;

... is kinda pointless, if you aren't then modifying the copy you just made. Just use it in-place.

-- hugh

 

[bea]

Hi Hugh,
many thanks. I noticed also the scrap name mistake in my script.
Now it works and looks much better.
Cheers,
Bianka

Update:
I had to change

if (!empty($row) && !empty($scrap1))

to

if (($row != NULL) && ($scrap1 != NULL))

because '0' can be a possible.

$date ='{fab_cp4_line_tour___date_raw}';
$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
    ->select('SUM(losses3_scrap1)')
    ->from('fab_cp1_line_tour')
    ->where('date= ' . $myDb->quote($date))
    ->where('machine= 7')
    ->where('status= 2')
    ->where('shift_records = shiftorder');
$myDb->setQuery($myQuery);
$scrap1 = $myDb->loadResult();

$myDb = FabrikWorker::getDbo(false, 2);
$myQuery = $myDb->getQuery(true);
$myQuery
    ->select(array('h24_scrap2','h24_ist_bags'))
    ->from('24h_daten')
    ->where('date= ' . $myDb->quote($date))
    ->where('machine= 7');
$myDb->setQuery($myQuery);
$row = $myDb->loadObject();
if (($row != NULL) && ($scrap1 != NULL)){
   $total = (($row->h24_scrap2 + $scrap1 ) * 100) / $row->h24_ist_bags;
   return sprintf('%01.1f',$total).'%';
}
return '';