#include <infocon.style>
-<page title="Kontoführung">
+<future>
+<page func=InfoCon title="Buchhaltung">
+<script type="text/javascript" src="<root_prefix>jquery.editable.js"></script>
-<headline "Buchhaltung">
-
-<table width=100% border=1 cellpadding=2 cellspacing=0>
-<tr>
- <th width=15%>Datum</th>
- <th width=20%>Kategorie</th>
- <th width=50%>Verwendungszweck</th>
- <th width=15%>Betrag</th>
-</tr>
<?
- $dbh = pg_pconnect ("kuolema", "5432", "<db>")
+ $dbh = pg_pconnect ("<dbhost>", "<dbport>", "<dbname>")
or die("Unable to connect to SQL server");
pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");
- if (strlen($year)) {
+ $table = "sales";
+
+if ($_POST["submit"] == "List") {
+?>
+<table class="smallfont border" width=100% border=0 cellpadding=1 cellspacing=1>
+<tr class="head">
+ <th width=10%>Datum</th>
+ <th width=10%>Buchung</th>
+ <th width=10%>Kategorie</th>
+ <th width=60%>Verwendungszweck</th>
+ <th width=10%>Betrag</th>
+</tr>
+<?
+ if (strlen($_POST[year])) {
# $where[] = "date >= '$year-01-01'";
# $year++;
# $where[] = "date < '$year-01-01'";
- $where[] = "date ~* '$year'";
+ $where[] = "date ~* '$_POST[year]'";
}
- if (strlen($category)) {
- $where[] = "category = '$category'";
+ if (strlen($_POST[category])) {
+ $where[] = "category = '$_POST[category]'";
}
- if ($input && !$output) {
+ if ($_POST[input] && !$_POST[output]) {
$where[] = "price > 0.0";
- } elseif ($output && !$input) {
+ } elseif ($_POST[output] && !$_POST[input]) {
$where[] = "price < 0.0";
}
- $query = "SELECT date,oid,category,description,price FROM sales ";
+ $query = "SELECT nr,date,billing_date,category,description,price FROM $table ";
if (count($where) > 0) {
$query .= " WHERE " . implode ($where, " AND ");
}
$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 ("-", $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);
-
- echo ("<tr>");
- printf ("<td width=\"15%%\" align=\"right\">%s</td>", $date);
- printf ("<td width=\"20%%\">%s</td>", $row['category']);
- printf ("<td width=\"50%%\"><a href=\"edit.php3?oid=%d\">%s</a></td>", $row['oid'], $row['description']);
- printf ("<td width=\"15%%\" align=\"right\">%5.2f</td>", $row['price']);
+ 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 ("<tr class=\"t%d\">", $color);
+ printf ("<td width=\"10%%\" align=\"right\">%s</td>", $date);
+ printf ("<td width=\"10%%\" align=\"right\">%s</td>", $billing_date);
+ printf ("<td width=\"10%%\" align=\"center\">%s</td>", $row['category']);
+ printf ("<td width=\"0%%\"><span class=\"edit\" route=\"Sales/EditDescription\" item_id=\"%d\">%s</span></td>",
+ $row['nr'], $row['description']);
+ printf ("<td width=\"10%%\" align=\"right\">%5.2f</td>", $row['price']);
$sum += $row['price'];
echo ("</tr>");
+ $color = !$color;
}
- echo ("<tr>");
- echo ("<td width=\"85%\" colspan=\"3\">Summe</td>");
- printf ("<td width=\"15%%\" align=\"right\">%5.2f</td>", $sum);
+ printf ("<tr class=\"t%d\">", $color);
+ echo ("<td width=\"85%\" colspan=\"4\"><b>Summe</b></td>");
+ printf ("<td width=\"15%%\" align=\"right\"><b>%5.2f</b></td>", $sum);
echo ("</tr>");
+ echo ("</table>");
- if (!strlen($category)) {
+} elseif ($_POST["submit"] == "Overview") {
- $query = "SELECT DISTINCT category FROM sales ";
- if (strlen($year)) {
- $query .= "WHERE date ~* '$year'";
- }
+ $query = "SELECT DISTINCT category FROM $table ";
+ if (strlen($_POST[year]))
+ $query .= "WHERE date ~* '$_POST[year]'";
$sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
if (count ($catz) > 0) {
echo ("<h3>Nach Kategorien sortiert</h3>");
- echo ("<table width=\"100%\" border=\"1\" cellpadding=\"2\" cellspacing=\"0\">");
- echo ("<tr><th width=\"70%\">Kategorie</th><th width=\"10%\">Einnahmen</th><th width=\"10%\">Ausgaben</th>"
- ."<th width=\"10%\">Summe</th></tr>");
+ echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
+ echo ("<tr class=\"head\">"
+ ."<th width=\"40%\">Kategorie</th>"
+ ."<th width=\"20%\">Einnahmen</th>"
+ ."<th width=\"20%\">Ausgaben</th>"
+ ."<th width=\"20%\">Summe</th></tr>");
sort ($catz);
+ $color = 0;
+ $sum_output = 0;
for ($i=0; $i < count ($catz); $i++) {
- $query = sprintf ("SELECT SUM (price) FROM sales WHERE category = '%s' AND price > 0.0", $catz[$i]);
- if (strlen($year)) {
- $query .= " AND date ~* '$year'";
+ $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 sales WHERE category = '%s' AND price < 0.0", $catz[$i]);
- if (strlen($year)) {
- $query .= " AND date ~* '$year'";
+ $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;
- printf ("<tr><td width=\"70%%\">%s</td><td align=\"right\" width=\"10%%\">%.2f</td>"
- ."<td align=\"right\" width=\"10%%\">%.2f</td>"
- ."<td align=\"right\" width=\"10%%\">%.2f</td></tr>",$catz[$i], $input, $output, $input - $output);
+ $sum = $input - $output;
+ printf ("<tr class=\"t%d\"><td width=\"40%%\">%s</td>"
+ ."<td align=\"right\" width=\"20%%\">%.2f</td>"
+ ."<td align=\"right\" width=\"20%%\">%.2f</td>"
+ ."<td align=\"right\" width=\"20%%\" class=\"%s\">%.2f</td></tr>",
+ $color, $catz[$i], $input, $output, $sum>0?'in':'out', $sum);
+ $color = !$color;
}
- printf ("<tr><td width=\"70%%\">Summe</td><td align=\"right\" width=\"10%%\">%.2f</td>"
- ."<td align=\"right\" width=\"10%%\">%.2f</td>"
- ."<td align=\"right\" width=\"10%%\">%.2f</td></tr>",$sum_input, $sum_output, $sum_input - $sum_output);
- echo ("</table>");
+ $sum = $sum_input - $sum_output;
+ printf ("<tr class=\"t%d\"><td width=\"40%%\"><b>Summe</b></td>"
+ ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
+ ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
+ ."<td align=\"right\" width=\"20%%\" class=\"%s\"><b>%.2f</b></td></tr>",
+ $color, $sum_input, $sum_output, $sum>0?'in':'out', $sum);
+ echo ("</table></div>");
+
+ }
+ } elseif ($_POST["submit"] == "Tally") {
+ if (strlen($_POST[year]))
+ $where = "date ~* '$_POST[year]' AND ";
+ else
+ $where = '';
+
+ if (strlen($_POST[year]))
+ printf("<h3>Jahresabrechnung %d</h3>", $_POST[year]);
+ else
+ echo ("<h3>Abrechnung</h3>");
+
+ $input = tally ($dbh, "Einnahmen", $where, 'in');
+ echo '<div style="padding-top: 15px;"></div>';
+ $output = tally ($dbh, "Ausgaben", $where, 'out');
+
+ if (strlen($_POST[year])) {
+ echo '<div style="padding-top: 15px;"></div>';
+ echo('<table class="smallfont border" cellspacing="1" cellpadding="1">');
+ $foo = $input["tax"] - $output["tax"];
+ printf('<tr><td>%s</td><td align="right">%.2f €</td><td>(%s)</td></tr>',
+ 'Summe Steuern', $foo, $foo>0?'Nachzahlung':'Rückzahlung');
+ printf('<tr><td>%s</td><td align="right">%.2f €</td><td> </td></tr>',
+ 'Summe netto', $input["netto"] - $output["netto"]);
+ $foo = $input["brutto"] - $output["brutto"];
+ printf('<tr><td>%s</td><td align="right">%.2f €</td><td>(%s)</td></tr>',
+ 'Summe brutto', $foo, $foo>0?'Gewinn':'Verlust');
+ if (($privat = privat($dbh, $where)))
+ printf('<tr><td>%s</td><td align="right">%.2f €</td><td> </td></tr>',
+ 'Privatentnahme', $privat);
+ echo('</table>');
}
}
-?>
-</table><p>
-<a href="edit.php3">Neuer Eintrag</a><p>
+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 ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
+ printf ("<tr class=\"head\"><th colspan=\"4\" align=\"left\"><strong>%s</strong></td></tr>", $head);
+ printf ("<tr class=\"head\">"
+ ."<th width=\"10%%\">USt.</th>"
+ ."<th width=\"30%%\">Steuer</th>"
+ ."<th width=\"30%%\">netto</th>"
+ ."<th width=\"30%%\">brutto</th></tr>");
+
+ $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 ("<tr class=\"t%d\"><td align=\"right\" width=\"10%%\">%d%%</td>"
+ ."<td align=\"right\" width=\"30%%\">%.2f</td>"
+ ."<td align=\"right\" width=\"30%%\">%.2f</td>"
+ ."<td align=\"right\" width=\"30%%\">%.2f</td></tr>",
+ $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 ("<tr class=\"t%d\"><td width=\"10%%\"><b>Sum</b></td>"
+ ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
+ ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
+ ."<td align=\"right\" width=\"30%%\" class=\"%s\"><b>%.2f</b></td></tr>",
+ $color,
+ $sum["tax"], $sum["netto"], $class, $sum["brutto"]);
+ echo ("</table></div>\n");
+ return $sum;
+}
+
+?>
+<div style="padding-top: 5px;"></div>
+
+<script type="text/javascript">
+<protect>
+$(function(){
+ make_editable('table.smallfont tr span.edit');
+});
+</protect>
+</script>
</page>
# Local variables:
-# mode: text
-# mode: auto-fill
-# mode: iso-accents
+# mode: php
# end: