valuecolumn,
$this->table,
$this->db->quote($blzkto));
return $this->db->fetchValue($sql);
}
public function distinctYears($blzkto)
{
$sql = sprintf("SELECT DISTINCT substr(datum::text,0,5) AS year FROM %s ".
"WHERE blz_kto = %s ORDER BY year DESC",
$this->table, $this->db->quote($blzkto));
return $this->db->fetchObjectList($sql);
}
public function distinctCategories($blzkto)
{
$sql = sprintf("SELECT DISTINCT category FROM %s WHERE blz_kto = %s ORDER BY category",
$this->table, $this->db->quote($blzkto));
return $this->db->fetchObjectList($sql);
}
public function distinctFromTo($blzkto, $period=false)
{
$sql = sprintf("SELECT DISTINCT from_to FROM %s WHERE blz_kto = %s%s ORDER BY from_to",
$this->table, $this->db->quote($blzkto),
$period === false ? '' : ' AND ' . $period);
return $this->db->fetchObjectList($sql);
}
public function distinctStatements($blzkto)
{
$sql = sprintf("SELECT DISTINCT statement FROM %s WHERE blz_kto = %s ORDER BY statement DESC",
$this->table, $this->db->quote($blzkto));
return $this->db->fetchObjectList($sql);
}
public function filterTable(Array $data)
{
error_log('filter Table');
$out = '';
$where[] = sprintf("blz_kto = '%s'", $data['blzkto']);
if (strlen($data['year'])) {
$where[] = sprintf ("datum >= '%04d-01-01'", $data['year']);
$year++;
$where[] = sprintf ("datum < '%04d-01-01'", $data['year']+1);
}
if (strlen($data['deadline']) && strlen(trim($data['deadline']))) {
$date = Utils::assertIsoDate(trim($data['deadline']));
$where[] = sprintf ("datum < '%s'", $date);
}
if (strlen($data['statement']) && strlen(trim($data['statement']))) {
$where[] = sprintf("statement = '%s'", $data['statement']);
}
if (strlen($data['category']) && strlen(trim($data['category']))) {
if (Utils::isAJAX()) $data['category'] = utf8_decode($data['category']);
$where[] = sprintf("category = '%s'", $data['category']);
}
if (strlen($data['keyword']) && strlen(trim($data['keyword']))) {
if (Utils::isAJAX()) $data['keyword'] = utf8_decode($data['keyword']);
$where[] = sprintf("descr ~* '%s'", $data['keyword']);
}
if (strlen($data['from_to']) && strlen(trim($data['from_to']))) {
if (Utils::isAJAX()) $data['from_to'] = utf8_decode($data['from_to']);
$where[] = sprintf("from_to = '%s'", $data['from_to']);
}
if ($data['input'] && !$data['output']) {
$where[] = "{$this->valuecolumn} > 0.0";
} elseif ($data['output'] && !$data['input']) {
$where[] = "{$this->valuecolumn} < 0.0";
}
$sql = sprintf("SELECT datum,id,category,descr,%s FROM %s WHERE %s ORDER BY datum,id",
$this->valuecolumn,
$this->table,
implode (" AND ", $where));
$sum = 0.0;
$sum_in = 0.0;
$sum_out = 0.0;
$color = 0;
foreach ($this->db->fetchAssocList($sql) as $row) {
$descr = explode ("\n", $row['descr']);
if (!strlen($descr[0])) $descr[0] = '(leer)';
$date = Utils::assertGermanDate($row['datum']);
$out .= sprintf ("", $color);
$out .= sprintf ("| %s | ", $date);
$out .= sprintf ("%s | ", $row['category']);
if (strpos($_SERVER['HTTP_USER_AGENT'], 'Mozilla/') === false)
$out .= sprintf ("%s | ",
$row['id'], $add, $descr[0]);
else
$out .= sprintf ("%s | ",
get_class($this),
$row['id'], $add, $descr[0]);
$out .= sprintf ("%5.2f | ",
$row[$this->valuecolumn]>0?"in":"out", $row[$this->valuecolumn]);
$sum += $row[$this->valuecolumn];
if ($row[$this->valuecolumn] > 0) {
$sum_in += $row[$this->valuecolumn];
} else {
$sum_out += $row[$this->valuecolumn];
}
$out .= "
";
$color = !$color;
}
if (strlen($data['statement'])) {
$out .= sprintf ("", $color);
$out .= "| Summe Einnahmen | ";
$out .= sprintf ("%5.2f | ",$sum_in);
$out .= "
";
$out .= sprintf ("", $color);
$out .= "| Summe Ausgaben | ";
$out .= sprintf ("%5.2f | ", $sum_out*-1);
$out .= "
";
}
$out .= sprintf ("", $color);
$out .= "| Summe | ";
$out .= sprintf ("%5.2f | ",
$sum>0?"in":"out", $sum);
$out .= "
";
return $out;
}
public function ajaxFilter(Array $data)
{
return array('table' => utf8_encode($this->filterTable($data)));
}
}