machine = $machine;
}
public function distinctYears()
{
$sql = "SELECT EXTRACT(YEAR from date) AS year,sum(price) AS sum,sum(km) AS km FROM sprit_log " .
($this->machine ? "WHERE machine = {$this->machine} " : "") .
"GROUP BY year ORDER BY year DESC";
return $this->db->fetchObjectList($sql);
}
public function formatYear($year)
{
$out = '';
$sql = sprintf("SELECT *, liter/(km/100.00) AS consumption FROM sprit_log WHERE %sEXTRACT(YEAR from date) = %d ORDER BY date ASC",
$this->machine ? "machine = {$this->machine} AND " : "",
$year);
$total_km = 0;
$total_liter = 0;
$total_price = 0;
foreach ($this->db->fetchObjectList($sql) as $row) {
$out .= sprintf('| %s | %s | %s | ' .
'%.3f | %.2f | %.2f | ' .
'%d | %d | %.2f |
',
$row->id, assert_german_date($row->date),
$row->city, $row->id, $row->tankstelle,
$row->price_liter, $row->liter,$row->price,
$row->km, $row->km_total,
$row->consumption);
$total_km += $row->km;
$total_liter += $row->liter;
$total_price += $row->price;
}
if (strlen($out)) {
$consumption = $total_liter / ($total_km / 100);
$out = '' .
'' .
'| Datum | Ort | Tankstelle | ' .
'EUR/l | l | EUR | km | gesamt | ' .
'⌀ | ' .
'
' .
'' . $out . '' .
'' .
'| Summe | ' .
sprintf('%.2f | %.2f | %d | | %.2f | ',
$total_liter, $total_price, $total_km, $consumption).
'
' .
'
';
}
return array($out, $total_liter, $total_price, $total_km);
}
public function ajaxList(Array $data)
{
if (isset($data['machine']))
$this->setMachine($data['machine']);
list($table, $liter, $price, $km) = $this->formatYear($data['year']);
return array('html' => array('list_'.$data['year'] => $table,
'sum_'.$data['year'] => sprintf('€ %.2f %d km', $price, $km),
));
}
public function ajaxAdd(Array $data)
{
$data = array('machine' => intval($data['machine']),
'date' => assert_iso_date($data['date']),
'city' => $data['city'],
'tankstelle' => $data['tankstelle'],
'price_liter' => str_replace(',','.',$data['price_liter']),
'liter' => str_replace(',','.',$data['liter']),
'price' => str_replace(',','.',$data['price']),
'km' => intval($data['km']),
'km_total' => intval($data['km_total']),
'sys_edit' => 'now()',
'sys_user' => $_SERVER['REMOTE_USER']);
foreach ($data as $k => $v)
if (empty($v))
return ajax_error(sprintf('Field %s must not be empty', $k));
if (empty($data['id'])) {
$ok = $this->db->insertInto('sprit_log', $data);
} else {
$ok = $this->db->update('sprit_log', $data, 'id = ' . intval($data['id']));
}
$d = explode('-', $data['date']);
return array('status' => $ok, 'year' => $d[0]);
}
public function ajaxSuggestCity(Array $data)
{
$data['query'] .= '%';
$sql = sprintf("SELECT DISTINCT city FROM sprit_log WHERE lower(city) LIKE %s ORDER BY city",
$this->quote(strtolower($data['query'])));
$list = array();
foreach ($this->fetchObjectList($sql) as $row)
$list[] = array('value' => $row->city, 'data' => $row->city);
return array('suggestions' => $list);
}
public function ajaxSuggestTankstelle(Array $data)
{
$data['query'] .= '%';
$sql = sprintf("SELECT DISTINCT tankstelle FROM sprit_log WHERE city = %s AND lower(tankstelle) LIKE %s ORDER BY tankstelle",
$this->quote($data['city']),
$this->quote(strtolower($data['query'])));
$list = array();
foreach ($this->fetchObjectList($sql) as $row)
$list[] = array('value' => $row->tankstelle, 'data' => $row->tankstelle);
return array('suggestions' => $list);
}
public function ajaxEditTankstelle(Array $data)
{
if (!strlen($data['content'])) return false;
$content = str_replace(['&','<','>'], ['&','<','>'], $data['content']);
return $this->modify('tankstelle', utf8_decode($content));
}
}