1 #include <infocon.style>
3 <page func=InfoCon title="Buchhaltung">
6 $dbh = pg_pconnect ("<dbhost>", "<dbport>", "<dbname>")
7 or die("Unable to connect to SQL server");
9 pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");
13 if ($_POST["submit"] == "List") {
15 <table class="smallfont border" width=100% border=0 cellpadding=1 cellspacing=1>
17 <th width=10%>Datum</th>
18 <th width=10%>Kategorie</th>
19 <th width=70%>Verwendungszweck</th>
20 <th width=10%>Betrag</th>
23 if (strlen($_POST[year])) {
24 # $where[] = "date >= '$year-01-01'";
26 # $where[] = "date < '$year-01-01'";
27 $where[] = "date ~* '$_POST[year]'";
29 if (strlen($_POST[category])) {
30 $where[] = "category = '$_POST[category]'";
32 if ($_POST[input] && !$_POST[output]) {
33 $where[] = "price > 0.0";
34 } elseif ($_POST[output] && !$_POST[input]) {
35 $where[] = "price < 0.0";
38 $query = "SELECT date,oid,category,description,price FROM $table ";
39 if (count($where) > 0) {
40 $query .= " WHERE " . implode ($where, " AND ");
42 $query .= " ORDER BY date,oid";
43 $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
47 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
48 $row = pg_fetch_array ($sth, $nr);
50 # $date = explode (" ", $row['date']);
51 # $date = explode ("-", $date[0]);
52 # $date = sprintf ("%d.%d.%d", $date[2], $date[1], $date[0]);
53 $date = substr ($row['date'],6,2) . "." . substr ($row['date'],4,2) . "." . substr ($row['date'],0,4);
55 printf ("<tr class=\"t%d\">", $color);
56 printf ("<td width=\"10%%\" align=\"right\">%s</td>", $date);
57 printf ("<td width=\"10%%\" align=\"center\">%s</td>", $row['category']);
58 printf ("<td width=\"70%%\"><a href=\"edit.php?oid=%d\">%s</a></td>",
59 $row['oid'], $row['description']);
60 printf ("<td width=\"10%%\" align=\"right\">%5.2f</td>", $row['price']);
61 $sum += $row['price'];
65 printf ("<tr class=\"t%d\">", $color);
66 echo ("<td width=\"85%\" colspan=\"3\"><b>Summe</b></td>");
67 printf ("<td width=\"15%%\" align=\"right\"><b>%5.2f</b></td>", $sum);
71 } elseif ($_POST["submit"] == "Overview") {
73 $query = "SELECT DISTINCT category FROM $table ";
74 if (strlen($_POST[year]))
75 $query .= "WHERE date ~* '$_POST[year]'";
77 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
79 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
80 $row = pg_fetch_array ($sth, $nr);
81 $catz[] = $row['category'];
84 if (count ($catz) > 0) {
85 echo ("<h3>Nach Kategorien sortiert</h3>");
86 echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
87 echo ("<tr class=\"head\">"
88 ."<th width=\"40%\">Kategorie</th>"
89 ."<th width=\"20%\">Einnahmen</th>"
90 ."<th width=\"20%\">Ausgaben</th>"
91 ."<th width=\"20%\">Summe</th></tr>");
96 for ($i=0; $i < count ($catz); $i++) {
97 $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price > 0.0", $catz[$i]);
99 if (strlen($_POST[year])) {
100 $query .= " AND date ~* '$_POST[year]'";
102 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
103 $row = pg_fetch_array ($sth, 0);
104 $input = $row[0]; $sum_input += $input;
105 $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price < 0.0", $catz[$i]);
106 if (strlen($_POST[year])) {
107 $query .= " AND date ~* '$_POST[year]'";
109 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
110 $row = pg_fetch_array ($sth, 0);
111 $output = -$row[0]; $sum_output += $output;
113 $sum = $input - $output;
114 printf ("<tr class=\"t%d\"><td width=\"40%%\">%s</td>"
115 ."<td align=\"right\" width=\"20%%\">%.2f</td>"
116 ."<td align=\"right\" width=\"20%%\">%.2f</td>"
117 ."<td align=\"right\" width=\"20%%\" class=\"%s\">%.2f</td></tr>",
118 $color, $catz[$i], $input, $output, $sum>0?'in':'out', $sum);
122 $sum = $sum_input - $sum_output;
123 printf ("<tr class=\"t%d\"><td width=\"40%%\"><b>Summe</b></td>"
124 ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
125 ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
126 ."<td align=\"right\" width=\"20%%\" class=\"%s\"><b>%.2f</b></td></tr>",
127 $color, $sum_input, $sum_output, $sum>0?'in':'out', $sum);
129 echo ("</table></div>");
132 } elseif ($_POST["submit"] == "Tally") {
133 if (strlen($_POST[year]))
134 $where = "date ~* '$_POST[year]' AND ";
138 if (strlen($_POST[year]))
139 printf("<h3>Jahresabrechnung %d</h3>", $_POST[year]);
141 echo ("<h3>Abrechnung</h3>");
143 $input = tally ($dbh, "Einnahmen", $where, 'in');
144 echo '<div style="padding-top: 15px;"></div>';
145 $output = tally ($dbh, "Ausgaben", $where, 'out');
147 if (strlen($_POST[year])) {
148 echo '<div style="padding-top: 15px;"></div>';
149 echo('<table class="smallfont border" cellspacing="1" cellpadding="1">');
150 $foo = $input["tax"] - $output["tax"];
151 printf('<tr><td>%s</td><td align="right">%.2f €</td><td>(%s)</td></tr>',
152 'Summe Steuern', $foo, $foo>0?'Nachzahlung':'Rückzahlung');
153 printf('<tr><td>%s</td><td align="right">%.2f €</td><td> </td></tr>',
154 'Summe netto', $input["netto"] - $output["netto"]);
155 $foo = $input["brutto"] - $output["brutto"];
156 printf('<tr><td>%s</td><td align="right">%.2f €</td><td>(%s)</td></tr>',
157 'Summe brutto', $foo, $foo>0?'Gewinn':'Verlust');
158 if (($privat = privat($dbh, $where)))
159 printf('<tr><td>%s</td><td align="right">%.2f €</td><td> </td></tr>',
160 'Privatentnahme', $privat);
165 function privat ($dbh, $where)
167 $query = "SELECT sum(price) * -1 AS privat"
168 . " FROM sales WHERE " . $where
169 . " category = 'privat'";
172 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
173 if (pg_NumRows($sth) != 1)
176 $row = pg_fetch_array ($sth, $nr);
177 return $row['privat'];
180 function tally ($dbh, $head, $where, $class)
182 echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
183 printf ("<tr class=\"head\"><th colspan=\"4\" align=\"left\"><strong>%s</strong></td></tr>", $head);
184 printf ("<tr class=\"head\">"
185 ."<th width=\"10%%\">USt.</th>"
186 ."<th width=\"30%%\">Steuer</th>"
187 ."<th width=\"30%%\">netto</th>"
188 ."<th width=\"30%%\">brutto</th></tr>");
190 $query = "SELECT tax_percent,sum(tax_assigned) AS tax,sum(price-tax_assigned) AS netto,sum(price) AS brutto"
191 . " FROM sales WHERE " . $where
192 . ($class=='in'?"price > 0.0":"price < 0.0")
193 . " AND category <> 'privat' "
194 . " GROUP BY tax_percent ORDER BY tax_percent";
199 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
200 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
201 $row = pg_fetch_array ($sth, $nr);
203 if ($row["brutto"] < 0) {
204 $row["tax"] = -$row["tax"];
205 $row["netto"] = -$row["netto"];
206 $row["brutto"] = -$row["brutto"];
209 printf ("<tr class=\"t%d\"><td align=\"right\" width=\"10%%\">%d%%</td>"
210 ."<td align=\"right\" width=\"30%%\">%.2f</td>"
211 ."<td align=\"right\" width=\"30%%\">%.2f</td>"
212 ."<td align=\"right\" width=\"30%%\">%.2f</td></tr>",
214 $row["tax_percent"], $row["tax"], $row["netto"], $row["brutto"]);
215 $sum["tax"] += $row["tax"];
216 $sum["netto"] += $row["netto"];
217 $sum["brutto"] += $row["brutto"];
221 printf ("<tr class=\"t%d\"><td width=\"10%%\"><b>Sum</b></td>"
222 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
223 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
224 ."<td align=\"right\" width=\"30%%\" class=\"%s\"><b>%.2f</b></td></tr>",
226 $sum["tax"], $sum["netto"], $class, $sum["brutto"]);
228 echo ("</table></div>\n");
234 <div style="padding-top: 5px;"></div>