#include ", "", "") or die("Unable to connect to SQL server"); pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!"); $table = "sales"; if ($_POST["submit"] == "List") { ?> = '$year-01-01'"; # $year++; # $where[] = "date < '$year-01-01'"; $where[] = "date ~* '$_POST[year]'"; } if (strlen($_POST[category])) { $where[] = "category = '$_POST[category]'"; } if ($_POST[input] && !$_POST[output]) { $where[] = "price > 0.0"; } elseif ($_POST[output] && !$_POST[input]) { $where[] = "price < 0.0"; } $query = "SELECT nr,date,billing_date,category,description,price,uid FROM $table "; if (count($where) > 0) { $query .= " WHERE " . implode ($where, " AND "); } $query .= " ORDER BY date,oid"; $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!"); $sum = 0.0; $color = 0; for ($nr=0; $nr < pg_NumRows ($sth); $nr++) { $row = pg_fetch_array ($sth, $nr); # $date = explode (" ", $row['date']); # $date = explode ("-", $date[0]); # $date = sprintf ("%d.%d.%d", $date[2], $date[1], $date[0]); $date = substr ($row['date'],6,2) . "." . substr ($row['date'],4,2) . "." . substr ($row['date'],0,4); if ($row['billing_date']) $billing_date = substr ($row['billing_date'],8,2) . "." . substr ($row['billing_date'],5,2) . "." . substr ($row['billing_date'],0,4); else $billing_date = ' '; printf ("", $color); printf ("", $date); printf ("", $billing_date); printf ("", $row['category']); printf ("", $row['uid'] ? "class=\"eu\" title=\"{$row['uid']}\"" : "class=\"eu\"", $row['nr']); printf ("", $row['nr'], $row['description']); printf ("", $row['price']); $sum += $row['price']; echo (""); $color = !$color; } printf ("", $color); echo (""); printf ("", $sum); echo (""); echo ("
Datum Buchung Kategorie   Verwendungszweck Betrag
%s%s%spix/if_europeanunion_4618.png\" %s alt=\"\" item_id=\"%d\">%s%5.2f
Summe%5.2f
"); } elseif ($_POST["submit"] == "Overview") { $query = "SELECT DISTINCT category FROM $table "; if (strlen($_POST[year])) $query .= "WHERE date ~* '$_POST[year]'"; $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!"); for ($nr=0; $nr < pg_NumRows ($sth); $nr++) { $row = pg_fetch_array ($sth, $nr); $catz[] = $row['category']; } if (count ($catz) > 0) { echo ("

Nach Kategorien sortiert

"); echo ("
"); echo ("" ."" ."" ."" .""); sort ($catz); $color = 0; $sum_output = 0; for ($i=0; $i < count ($catz); $i++) { $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price > 0.0", $catz[$i]); if (strlen($_POST[year])) { $query .= " AND date ~* '$_POST[year]'"; } $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!"); $row = pg_fetch_array ($sth, 0); $input = $row[0]; $sum_input += $input; $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price < 0.0", $catz[$i]); if (strlen($_POST[year])) { $query .= " AND date ~* '$_POST[year]'"; } $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!"); $row = pg_fetch_array ($sth, 0); $output = -$row[0]; $sum_output += $output; $sum = $input - $output; printf ("" ."" ."" ."", $color, $catz[$i], $input, $output, $sum>0?'in':'out', $sum); $color = !$color; } $sum = $sum_input - $sum_output; printf ("" ."" ."" ."", $color, $sum_input, $sum_output, $sum>0?'in':'out', $sum); echo ("
KategorieEinnahmenAusgabenSumme
%s%.2f%.2f%.2f
Summe%.2f%.2f%.2f
"); } } elseif ($_POST["submit"] == "Tally") { if (strlen($_POST[year])) $where = "date ~* '$_POST[year]' AND "; else $where = ''; if (strlen($_POST[year])) printf("

Jahresabrechnung %d

", $_POST[year]); else echo ("

Abrechnung

"); $input = tally ($dbh, "Einnahmen", $where, 'in'); echo '
'; $output = tally ($dbh, "Ausgaben", $where, 'out'); if (strlen($_POST[year])) { echo '
'; echo(''); $foo = $input["tax"] - $output["tax"]; printf('', 'Summe Steuern', $foo, $foo>0?'Nachzahlung':'Rückzahlung'); printf('', 'Summe netto', $input["netto"] - $output["netto"]); $foo = $input["brutto"] - $output["brutto"]; printf('', 'Summe brutto', $foo, $foo>0?'Gewinn':'Verlust'); if (($privat = privat($dbh, $where))) printf('', 'Privatentnahme', $privat); echo('
%s%.2f €(%s)
%s%.2f € 
%s%.2f €(%s)
%s%.2f € 
'); } } function privat ($dbh, $where) { $query = "SELECT sum(price) * -1 AS privat" . " FROM sales WHERE " . $where . " category = 'privat'"; error_log($query); $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!"); if (pg_NumRows($sth) != 1) return 0; $row = pg_fetch_array ($sth, $nr); return $row['privat']; } function tally ($dbh, $head, $where, $class) { echo ("
"); printf ("", $head); printf ("" ."" ."" ."" .""); $query = "SELECT tax_percent,sum(tax_assigned) AS tax,sum(price-tax_assigned) AS netto,sum(price) AS brutto" . " FROM sales WHERE " . $where . ($class=='in'?"price > 0.0":"price < 0.0") . " AND category <> 'privat' " . " GROUP BY tax_percent ORDER BY tax_percent"; $color = 0; $sum = array(); $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!"); for ($nr=0; $nr < pg_NumRows ($sth); $nr++) { $row = pg_fetch_array ($sth, $nr); if ($row["brutto"] < 0) { $row["tax"] = -$row["tax"]; $row["netto"] = -$row["netto"]; $row["brutto"] = -$row["brutto"]; } printf ("" ."" ."" ."", $color, $row["tax_percent"], $row["tax"], $row["netto"], $row["brutto"]); $sum["tax"] += $row["tax"]; $sum["netto"] += $row["netto"]; $sum["brutto"] += $row["brutto"]; $color = !$color; } printf ("" ."" ."" ."", $color, $sum["tax"], $sum["netto"], $class, $sum["brutto"]); echo ("
%s
USt.Steuernettobrutto
%d%%%.2f%.2f%.2f
Sum%.2f%.2f%.2f
\n"); return $sum; } ?>
# Local variables: # mode: php # end: