3 class SpritLog extends DatabaseTable {
5 public function __construct($id=false)
7 parent::__construct('sprit_log', $id);
10 public function distinctYears()
12 $sql = "SELECT EXTRACT(YEAR from date) AS year,sum(price) AS sum,sum(km) AS km FROM sprit_log GROUP BY year ORDER BY year DESC";
13 return $this->db->fetchObjectList($sql);
16 public function formatYear($year)
19 $sql = sprintf("SELECT * FROM sprit_log WHERE EXTRACT(YEAR from date) = %d ORDER BY date ASC", $year);;
24 foreach ($this->db->fetchObjectList($sql) as $row) {
25 $out .= sprintf('<tr id="%d"><td>%s</td><td>%s</td><td><span route="SpritLog/EditTankstelle" item_id="%d">%s</span></td>' .
26 '<td class="right">%.2f</td><td class="right">%.2f</td><td class="right">%.2f</td>' .
27 '<td class="right">%d</td><td class="right">%d</td></tr>',
28 $row->id, assert_german_date($row->date),
29 $row->city, $row->id, $row->tankstelle,
30 $row->price_liter, $row->liter,$row->price,
31 $row->km, $row->km_total);
32 $total_km += $row->km;
33 $total_liter += $row->liter;
34 $total_price += $row->price;
38 $out = '<table class="smallfont spritlog" width="100%">' .
40 '<th width="70">Datum</th><th width="130" class="left">Ort</th><th class="left">Tankstelle</th>' .
41 '<th width="40">EUR/l</th><th width="40">l</th><th width="40">EUR</th><th width="40">km</th><th width="40">gesamt</th>' .
43 '<tbody>' . $out . '</tbody>' .
45 '<th colspan="4" class="left">Summe</th>' .
46 sprintf('<th class="right">%.2f</th><th class="right">%.2f</th><th class="right">%d</th><th> </th>',
47 $total_liter, $total_price, $total_km).
51 return array($out, $total_liter, $total_price, $total_km);
54 public function ajaxList(Array $data)
56 list($table, $liter, $price, $km) = $this->formatYear($data['year']);
58 return array('html' => array('list_'.$data['year'] => $table,
59 'sum_'.$data['year'] => sprintf('€ %.2f %d km', $price, $km),
63 public function ajaxAdd(Array $data)
65 $data = array('machine' => intval($data['machine']),
66 'date' => assert_iso_date($data['date']),
67 'city' => $data['city'],
68 'tankstelle' => $data['tankstelle'],
69 'price_liter' => str_replace(',','.',$data['price_liter']),
70 'liter' => str_replace(',','.',$data['liter']),
71 'price' => str_replace(',','.',$data['price']),
72 'km' => intval($data['km']),
73 'km_total' => intval($data['km_total']),
74 'sys_edit' => 'now()',
75 'sys_user' => $_SERVER['REMOTE_USER']);
77 foreach ($data as $k => $v)
79 return ajax_error(sprintf('Field %s must not be empty', $k));
81 if (empty($data['id'])) {
82 $ok = $this->db->insertInto('sprit_log', $data);
84 $ok = $this->db->update('sprit_log', $data, 'id = ' . intval($data['id']));
87 $d = explode('-', $data['date']);
88 return array('status' => $ok, 'year' => $d[0]);
91 public function ajaxSuggestCity(Array $data)
93 $data['query'] .= '%';
94 $sql = sprintf("SELECT DISTINCT city FROM sprit_log WHERE lower(city) LIKE %s ORDER BY city",
95 $this->quote(strtolower($data['query'])));
97 foreach ($this->fetchObjectList($sql) as $row)
98 $list[] = array('value' => $row->city, 'data' => $row->city);
99 return array('suggestions' => $list);
102 public function ajaxSuggestTankstelle(Array $data)
104 $data['query'] .= '%';
105 $sql = sprintf("SELECT DISTINCT tankstelle FROM sprit_log WHERE city = %s AND lower(tankstelle) LIKE %s ORDER BY tankstelle",
106 $this->quote($data['city']),
107 $this->quote(strtolower($data['query'])));
109 foreach ($this->fetchObjectList($sql) as $row)
110 $list[] = array('value' => $row->tankstelle, 'data' => $row->tankstelle);
111 return array('suggestions' => $list);
114 public function ajaxEditTankstelle(Array $data)
116 if (!strlen($data['content'])) return false;
117 return $this->modify('tankstelle', $data['content']);