calc element with where condition

[Martooti]

Hi,
I want to make an currency exchange calc inside the list. Exchange rates change monthly.
I have one table with `Date` (yyyy-mm-dd) , `Dolar`, and `Dolar_Equivalent` (in Egyptian Pound)
and another table with `Month` (dropdown: 1,2,3.., 12) , `Year`(dropdown : 2015, 2016,...), `Exchange_rate`
Dolar_Equivalent I wish to be a calc that multiply `Dolar` by `Exchange_rate` when `Date` from first table equals `Month` and `Year` from the second table.
I tried to use:
"return (int)'{ rt_cars_accounting___dolar}' * '{rt_exchange___dolar_equivalent}' WHERE 'substr( {rt_cars_accounting___date}, 5, 2 )'= '{rt_exchange___month}' AND 'substr( {rt_cars_accounting___date}, 0, 4 )' = '{rt_exchange___year}' ;" but I am getting an error.
Any advice?
Thanks in advance

 

[cheesegrits]

Yeah, I'm not surprised you get an error, that ... erm ... nothing like valid PHP code.

Try something like this ...

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
if (!empty('{rt_cars_accounting___date}') && !empty('{rt_cars_accounting___Dolar}')) {
   $query->select('Exchange_rate')->from('rt_exchange')->where('MONTH("{rt_cars_accounting___date}") = rt_exchange___month AND YEAR("{rt_cars_accounting___date}") = rt_exchange___year');
   $db->setQuery($query);
   $rx = (float) $db->loadResult();
   if (!empty($rx)) {
      return (float)'{rt_cars_accounting___Dolar}' * $rx;
   }
}
return '0';

-- hugh

 

[Martooti]

Thank you for your answer Hugh.
Unfortuantely I am getting error:
Error

An error has occurred with a eval'd field - please inform the web-site owner.
Debug: Eval exception : eqiuvalent::_getV() : $db = FabrikWorker::getDbo(); $query = $db->getQuery(true); if (!empty('') && !empty('')) { $query->select('dolar_rate')->from('rt_exchange')->where('MONTH("") = rt_exchange___month AND YEAR("") = rt_exchange___year'); $db->setQuery($query); $rx = (float) $db->loadResult(); if (!empty($rx)) { return (float)'' * $rx; } } return '0'; : syntax error, unexpected '''' (T_CONSTANT_ENCAPSED_STRING)

link

 

[cheesegrits]

Ah, OK, try:

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
$date = '{rt_cars_accounting___date}';
$dollar = '{rt_cars_accounting___Dolar}';
if (!empty($date) && !empty($dollar)) {
  $query->select('Exchange_rate')->from('rt_exchange')->where('MONTH("{rt_cars_accounting___date}") = rt_exchange___month AND YEAR("{rt_cars_accounting___date}") = rt_exchange___year');
  $db->setQuery($query);
  $rx = (float) $db->loadResult();
  if (!empty($rx)) {
      return (float)$dollar * $rx;
  }
}
return '0';

-- hugh

 

[Martooti]

With this code when I open the form there is no errors, but when submin I got :

" Unknown column 'rt_exchange___month' in 'where clause' SQL=SELECT dolar_rate FROM rt_exchange WHERE MONTH("2015-03-04 13:02:12") = rt_exchange___month AND YEAR("2015-03-04 13:02:12") = rt_exchange___year "

So I changed it to

"$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
$date = '{rt_cars_accounting___date}';
$dollar = '{rt_cars_accounting___dolar}';
if (!empty($date) && !empty($dollar)) {
$query->select('dolar_rate')->from('rt_exchange')->where('MONTH("{rt_cars_accounting___date}") = "{rt_exchange___month}" AND YEAR("{rt_cars_accounting___date}") = "{rt_exchange___year}"');
$db->setQuery($query);
$rx = (float) $db->loadResult();
if (!empty($rx)) {
return (float)$dollar * $rx;
}
}
return '0';"

and like that I can submit without errors but every time calc element shows " 0 "

 

[troester]

and another table with `Month` (dropdown: 1,2,3.., 12) , `Year`(dropdown : 2015, 2016,...), `Exchange_rate`

If rt_exchange is your DB table and month/year are the columns I think it must be

$query->select('Exchange_rate')->from('rt_exchange')->where('MONTH("{rt_cars_accounting___date}") = month AND YEAR("{rt_cars_accounting___date}") = year');

{} is for element placeholders, not for DB columns.

 

[Martooti]

I changed

$query->select('dolar_rate')->from('rt_exchange')->where('MONTH("{rt_cars_accounting___date}") = "{rt_exchange___month}" AND YEAR("{rt_cars_accounting___date}") = "{rt_exchange___year}"');

TO
$query->select('dolar_rate')->from('rt_exchange')->where('MONTH("{rt_cars_accounting___date}") = rt_exchange.month AND YEAR("{rt_cars_accounting___date}") = rt_exchange.year');

and it is working this way

Thank you all for your amazing support!

 

[cheesegrits]

Thanks for the correction, troester.

-- hugh