3 abstract class AccountTable extends DatabaseTable {
4 protected $valuecolumn;
6 public function __construct($table, $id)
8 parent::__construct($table, $id);
11 public function sum($blzkto)
13 $sql = sprintf("SELECT sum(%s) FROM %s WHERE blz_kto = %s",
16 $this->db->quote($blzkto));
17 return $this->db->fetchValue($sql);
20 public function distinctYears($blzkto)
22 $sql = sprintf("SELECT DISTINCT substr(datum::text,0,5) AS year FROM %s ".
23 "WHERE blz_kto = %s ORDER BY year DESC",
24 $this->table, $this->db->quote($blzkto));
25 return $this->db->fetchObjectList($sql);
28 public function distinctCategories($blzkto)
30 $sql = sprintf("SELECT DISTINCT category FROM %s WHERE blz_kto = %s ORDER BY category",
31 $this->table, $this->db->quote($blzkto));
32 return $this->db->fetchObjectList($sql);
35 public function distinctFromTo($blzkto, $period=false)
37 $sql = sprintf("SELECT DISTINCT from_to FROM %s WHERE blz_kto = %s%s ORDER BY from_to",
38 $this->table, $this->db->quote($blzkto),
39 $period === false ? '' : ' AND ' . $period);
40 return $this->db->fetchObjectList($sql);
43 public function distinctStatements($blzkto)
45 $sql = sprintf("SELECT DISTINCT statement FROM %s WHERE blz_kto = %s ORDER BY statement DESC",
46 $this->table, $this->db->quote($blzkto));
47 return $this->db->fetchObjectList($sql);
50 public function filterTable(Array $data)
52 error_log('filter Table');
55 $where[] = sprintf("blz_kto = '%s'", $data['blzkto']);
56 if (strlen($data['year'])) {
57 $where[] = sprintf ("datum >= '%04d-01-01'", $data['year']);
59 $where[] = sprintf ("datum < '%04d-01-01'", $data['year']+1);
62 if (strlen($data['deadline']) && strlen(trim($data['deadline']))) {
63 $date = Utils::assertIsoDate(trim($data['deadline']));
64 $where[] = sprintf ("datum < '%s'", $date);
67 if (strlen($data['statement']) && strlen(trim($data['statement']))) {
68 $where[] = sprintf("statement = '%s'", $data['statement']);
71 if (strlen($data['category']) && strlen(trim($data['category']))) {
72 if (Utils::isAJAX()) $data['category'] = utf8_decode($data['category']);
73 $where[] = sprintf("category = '%s'", $data['category']);
76 if (strlen($data['keyword']) && strlen(trim($data['keyword']))) {
77 if (Utils::isAJAX()) $data['keyword'] = utf8_decode($data['keyword']);
78 $where[] = sprintf("descr ~* '%s'", $data['keyword']);
81 if (strlen($data['from_to']) && strlen(trim($data['from_to']))) {
82 if (Utils::isAJAX()) $data['from_to'] = utf8_decode($data['from_to']);
83 $where[] = sprintf("from_to = '%s'", $data['from_to']);
86 if ($data['input'] && !$data['output']) {
87 $where[] = "{$this->valuecolumn} > 0.0";
88 } elseif ($data['output'] && !$data['input']) {
89 $where[] = "{$this->valuecolumn} < 0.0";
92 $sql = sprintf("SELECT datum,id,category,descr,%s FROM %s WHERE %s ORDER BY datum,id",
95 implode (" AND ", $where));
101 foreach ($this->db->fetchAssocList($sql) as $row) {
102 $descr = explode ("\n", $row['descr']);
103 $date = Utils::assertGermanDate($row['datum']);
105 $out .= sprintf ("<tr class=\"t%d\">", $color);
106 $out .= sprintf ("<td width=\"10%%\" align=\"right\">%s</td>", $date);
107 $out .= sprintf ("<td width=\"10%%\" align=\"center\">%s</td>", $row['category']);
108 if (strpos($_SERVER['HTTP_USER_AGENT'], 'Mozilla/') === false)
109 $out .= sprintf ("<td width=\"70%%\"><a href=\"edit.php?id=%d%s\" target=\"_new\">%s</a></td>",
110 $row['id'], $add, $descr[0]);
112 $out .= sprintf ("<td width=\"70%%\"><span route=\"%s/EditDescr\" item_id=\"%d\" text=\"%s\">%s</span></td>",
114 $row['id'], $add, $descr[0]);
115 $out .= sprintf ("<td width=\"10%%\" align=\"right\" class=\"%s\">%5.2f</td>",
116 $row[$this->valuecolumn]>0?"in":"out", $row[$this->valuecolumn]);
117 $sum += $row[$this->valuecolumn];
118 if ($row[$this->valuecolumn] > 0) {
119 $sum_in += $row[$this->valuecolumn];
121 $sum_out += $row[$this->valuecolumn];
126 if (strlen($data['statement'])) {
127 $out .= sprintf ("<tr class=\"t%d\">", $color);
128 $out .= "<td width=\"90%\" colspan=\"3\"><strong>Summe Einnahmen</strong></td>";
129 $out .= sprintf ("<td width=\"10%%\" align=\"right\" class=\"in\">%5.2f</td>",$sum_in);
131 $out .= sprintf ("<tr class=\"t%d\">", $color);
132 $out .= "<td width=\"90%\" colspan=\"3\"><strong>Summe Ausgaben</strong></td>";
133 $out .= sprintf ("<td width=\"10%%\" align=\"right\" class=\"out\">%5.2f</td>", $sum_out*-1);
136 $out .= sprintf ("<tr class=\"t%d\">", $color);
137 $out .= "<td width=\"90%\" colspan=\"3\" align=\"left\"><strong>Summe</strong></td>";
138 $out .= sprintf ("<td width=\"10%%\" align=\"right\" class=\"%s\"><strong>%5.2f</strong></td>",
139 $sum>0?"in":"out", $sum);
145 public function ajaxFilter(Array $data)
147 return array('table' => utf8_encode($this->filterTable($data)));