3 abstract class AccountTable extends DatabaseTable {
4 protected $valuecolumn;
5 protected $lookup_period = "2 years";
7 public function __construct($table, $id)
9 parent::__construct($table, $id);
12 public function sum($blzkto)
14 $sql = sprintf("SELECT sum(%s) FROM %s WHERE blz_kto = %s",
17 $this->db->quote($blzkto));
18 return $this->db->fetchValue($sql);
21 public function distinctYears($blzkto)
23 $sql = sprintf("SELECT DISTINCT substr(datum::text,0,5) AS year FROM %s ".
24 "WHERE blz_kto = %s ORDER BY year DESC",
25 $this->table, $this->db->quote($blzkto));
26 return $this->db->fetchObjectList($sql);
29 public function distinctCategories($blzkto)
31 $sql = sprintf("SELECT DISTINCT category FROM %s WHERE blz_kto = %s ORDER BY category",
32 $this->table, $this->db->quote($blzkto));
33 return $this->db->fetchObjectList($sql);
36 public function distinctFromTo($blzkto, $period=false)
38 $sql = sprintf("SELECT DISTINCT from_to FROM %s WHERE blz_kto = %s%s ORDER BY from_to",
39 $this->table, $this->db->quote($blzkto),
40 $period === false ? '' : ' AND ' . $period);
41 return $this->db->fetchObjectList($sql);
44 public function distinctStatements($blzkto)
46 $sql = sprintf("SELECT DISTINCT statement FROM %s WHERE blz_kto = %s ORDER BY statement DESC",
47 $this->table, $this->db->quote($blzkto));
48 return $this->db->fetchObjectList($sql);
51 public function filterTable(Array $data)
53 error_log('filter Table');
56 $where[] = sprintf("blz_kto = '%s'", $data['blzkto']);
57 if (strlen($data['year'])) {
58 $where[] = sprintf ("datum >= '%04d-01-01'", $data['year']);
60 $where[] = sprintf ("datum < '%04d-01-01'", $data['year']+1);
63 if (strlen($data['deadline']) && strlen(trim($data['deadline']))) {
64 $date = Utils::assertIsoDate(trim($data['deadline']));
65 $where[] = sprintf ("datum < '%s'", $date);
68 if (strlen($data['statement']) && strlen(trim($data['statement']))) {
69 $where[] = sprintf("statement = '%s'", $data['statement']);
72 if (strlen($data['category']) && strlen(trim($data['category']))) {
73 if (Utils::isAJAX()) $data['category'] = utf8_decode($data['category']);
74 $where[] = sprintf("category = '%s'", $data['category']);
77 if (strlen($data['keyword']) && strlen(trim($data['keyword']))) {
78 if (Utils::isAJAX()) $data['keyword'] = utf8_decode($data['keyword']);
79 $where[] = sprintf("descr ~* '%s'", $data['keyword']);
82 if (strlen($data['from_to']) && strlen(trim($data['from_to']))) {
83 if (Utils::isAJAX()) $data['from_to'] = utf8_decode($data['from_to']);
84 $where[] = sprintf("from_to = '%s'", $data['from_to']);
87 if ($data['input'] && !$data['output']) {
88 $where[] = "{$this->valuecolumn} > 0.0";
89 } elseif ($data['output'] && !$data['input']) {
90 $where[] = "{$this->valuecolumn} < 0.0";
93 $sql = sprintf("SELECT datum,id,category,descr,%s FROM %s WHERE %s ORDER BY datum,id",
96 implode (" AND ", $where));
102 foreach ($this->db->fetchAssocList($sql) as $row) {
103 $descr = explode ("\n", $row['descr']);
104 if (!strlen($descr[0])) $descr[0] = '(leer)';
105 $date = Utils::assertGermanDate($row['datum']);
107 $out .= sprintf ("<tr class=\"t%d\">", $color);
108 $out .= sprintf ("<td width=\"10%%\" align=\"right\">%s</td>", $date);
109 $out .= sprintf ("<td width=\"10%%\" align=\"center\">%s</td>", $row['category']);
110 if (strpos($_SERVER['HTTP_USER_AGENT'], 'Mozilla/') === false)
111 $out .= sprintf ("<td width=\"70%%\"><a href=\"edit.php?id=%d%s\" target=\"_new\">%s</a></td>",
112 $row['id'], $add, $descr[0]);
114 $out .= sprintf ("<td width=\"70%%\"><span route=\"%s/EditDescr\" item_id=\"%d\" text=\"%s\">%s</span></td>",
116 $row['id'], $add, $descr[0]);
117 $out .= sprintf ("<td width=\"10%%\" align=\"right\" class=\"%s\">%5.2f</td>",
118 $row[$this->valuecolumn]>0?"in":"out", $row[$this->valuecolumn]);
119 $sum += $row[$this->valuecolumn];
120 if ($row[$this->valuecolumn] > 0) {
121 $sum_in += $row[$this->valuecolumn];
123 $sum_out += $row[$this->valuecolumn];
128 if (strlen($data['statement'])) {
129 $out .= sprintf ("<tr class=\"t%d\">", $color);
130 $out .= "<td width=\"90%\" colspan=\"3\"><strong>Summe Einnahmen</strong></td>";
131 $out .= sprintf ("<td width=\"10%%\" align=\"right\" class=\"in\">%5.2f</td>",$sum_in);
133 $out .= sprintf ("<tr class=\"t%d\">", $color);
134 $out .= "<td width=\"90%\" colspan=\"3\"><strong>Summe Ausgaben</strong></td>";
135 $out .= sprintf ("<td width=\"10%%\" align=\"right\" class=\"out\">%5.2f</td>", $sum_out*-1);
138 $out .= sprintf ("<tr class=\"t%d\">", $color);
139 $out .= "<td width=\"90%\" colspan=\"3\" align=\"left\"><strong>Summe</strong></td>";
140 $out .= sprintf ("<td width=\"10%%\" align=\"right\" class=\"%s\"><strong>%5.2f</strong></td>",
141 $sum>0?"in":"out", $sum);
147 public function ajaxFilter(Array $data)
149 return array('table' => utf8_encode($this->filterTable($data)));
152 public function insert(Array $item)
154 $item[$this->valuecolumn] = $item['value'];
155 unset($item['value']);
157 if (!array_key_exists('sys_user', $item))
158 $item['sys_user'] = $_SERVER['REMOTE_USER'];
160 return $this->db->insertInto($this->table, $item);
163 public function ajaxGetCategoryInfo(Array $data)
166 $sql = sprintf("SELECT DISTINCT category FROM %s WHERE blz_kto = %s AND category <> '' AND category IS NOT NULL ORDER BY category",
168 $this->db->quote($data['blzkto']));
170 foreach ($this->db->fetchObjectList($sql) as $cat) {
171 $condition = sprintf("datum >= now() - interval '%s' AND category = %s",
172 $this->lookup_period,
173 $this->db->quote($cat->category));
175 $names = [['id' => '', 'title' => '']];
176 foreach ($this->distinctFromTo($data['blzkto'], $condition) as $row) {
177 $names[] = ['id' => utf8_encode($row->from_to),
178 'title' => utf8_encode($row->from_to)];
180 $result[utf8_encode($cat->category)] = $names;
183 return ['list' => $result];
186 public function ajaxInsert(Array $data)
188 if (!strlen($data['category']) && strlen($data['newcategory']))
189 $data['category'] = $data['newcategory'];
190 if (!strlen($data['from_to']) && strlen($data['newfrom_to']))
191 $data['from_to'] = $data['newfrom_to'];
193 return $this->insert(['blz_kto' => $data['blzkto'],
194 'datum' => Functions::date_german_to_iso($data['datum']),
195 'statement' => $data['statement'],
196 'from_to' => utf8_decode($data['from_to']),
197 'descr' => utf8_decode($data['descr']),
198 'category' => utf8_decode($data['category']),
199 'value' => str_replace (",",".", $data['value'])]);
202 protected function getCommonPrefix(Array $list)
204 $pl = 0; // common prefix length
206 $l = strlen($list[0]);
209 for ($i=1; $i<$n; $i++) {
210 if ($list[$i][$pl] !== $c) break 2;
214 $prefix = substr($list[0], 0, $pl);
219 public function ajaxGetItems(Array $data)
221 $sql = sprintf("SELECT DISTINCT %s AS value FROM %s WHERE blz_kto = %s AND category = %s AND from_to = %s AND datum >= now() - interval '%s'",
224 $this->quote($data['blzkto']),
225 $this->quote(utf8_decode($data['category'])),
226 $this->quote(utf8_decode($data['from_to'])),
227 $this->lookup_period);
228 $values = $this->db->fetchObjectList($sql);
230 $sql = sprintf("SELECT DISTINCT descr FROM %s WHERE blz_kto = %s AND category = %s AND from_to = %s AND datum >= now() - interval '%s' ORDER BY descr",
232 $this->quote($data['blzkto']),
233 $this->quote(utf8_decode($data['category'])),
234 $this->quote(utf8_decode($data['from_to'])),
235 $this->lookup_period);
238 foreach ($this->db->fetchObjectList($sql) as $row)
239 $list[] = utf8_encode($row->descr);
241 return ['use_value' => count($values) == 1,
242 'value' => count($values) == 1 ? $values[0]->value : '',
244 'prefix' => $this->getCommonPrefix($list)];