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)));
}
public function insert(Array $item)
{
$item[$this->valuecolumn] = $item['value'];
unset($item['value']);
if (!array_key_exists('sys_user', $item))
$item['sys_user'] = $_SERVER['REMOTE_USER'];
return $this->db->insertInto($this->table, $item);
}
public function ajaxGetCategoryInfo(Array $data)
{
$result = [];
$sql = sprintf("SELECT DISTINCT category FROM %s WHERE blz_kto = %s AND category <> '' AND category IS NOT NULL ORDER BY category",
$this->table,
$this->db->quote($data['blzkto']));
foreach ($this->db->fetchObjectList($sql) as $cat) {
$condition = sprintf("datum >= now() - interval '%s' AND category = %s",
$this->lookup_period,
$this->db->quote($cat->category));
$names = [['id' => '', 'title' => '']];
foreach ($this->distinctFromTo($data['blzkto'], $condition) as $row) {
$names[] = ['id' => utf8_encode($row->from_to),
'title' => utf8_encode($row->from_to)];
}
$result[utf8_encode($cat->category)] = $names;
}
return ['list' => $result];
}
public function ajaxInsert(Array $data)
{
if (!strlen($data['category']) && strlen($data['newcategory']))
$data['category'] = $data['newcategory'];
if (!strlen($data['from_to']) && strlen($data['newfrom_to']))
$data['from_to'] = $data['newfrom_to'];
return $this->insert(['blz_kto' => $data['blzkto'],
'datum' => Functions::date_german_to_iso($data['datum']),
'statement' => $data['statement'],
'from_to' => utf8_decode($data['from_to']),
'descr' => utf8_decode($data['descr']),
'category' => utf8_decode($data['category']),
'value' => str_replace (",",".", $data['value'])]);
}
protected function getCommonPrefix(Array $list)
{
$pl = 0; // common prefix length
$n = count($list);
$l = strlen($list[0]);
while ($pl < $l) {
$c = $list[0][$pl];
for ($i=1; $i<$n; $i++) {
if ($list[$i][$pl] !== $c) break 2;
}
$pl++;
}
$prefix = substr($list[0], 0, $pl);
return $prefix;
}
public function ajaxGetItems(Array $data)
{
$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'",
$this->valuecolumn,
$this->table,
$this->quote($data['blzkto']),
$this->quote(utf8_decode($data['category'])),
$this->quote(utf8_decode($data['from_to'])),
$this->lookup_period);
$values = $this->db->fetchObjectList($sql);
$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",
$this->table,
$this->quote($data['blzkto']),
$this->quote(utf8_decode($data['category'])),
$this->quote(utf8_decode($data['from_to'])),
$this->lookup_period);
$list = [];
foreach ($this->db->fetchObjectList($sql) as $row)
$list[] = utf8_encode($row->descr);
return ['use_value' => count($values) == 1,
'value' => count($values) == 1 ? $values[0]->value : '',
'list' => $list,
'prefix' => $this->getCommonPrefix($list)];
}
}