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%>Buchung</th>
19 <th width=10%>Kategorie</th>
20 <th width=60%>Verwendungszweck</th>
21 <th width=10%>Betrag</th>
24 if (strlen($_POST[year])) {
25 # $where[] = "date >= '$year-01-01'";
27 # $where[] = "date < '$year-01-01'";
28 $where[] = "date ~* '$_POST[year]'";
30 if (strlen($_POST[category])) {
31 $where[] = "category = '$_POST[category]'";
33 if ($_POST[input] && !$_POST[output]) {
34 $where[] = "price > 0.0";
35 } elseif ($_POST[output] && !$_POST[input]) {
36 $where[] = "price < 0.0";
39 $query = "SELECT date,billing_date,oid,category,description,price FROM $table ";
40 if (count($where) > 0) {
41 $query .= " WHERE " . implode ($where, " AND ");
43 $query .= " ORDER BY date,oid";
44 $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
48 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
49 $row = pg_fetch_array ($sth, $nr);
51 # $date = explode (" ", $row['date']);
52 # $date = explode ("-", $date[0]);
53 # $date = sprintf ("%d.%d.%d", $date[2], $date[1], $date[0]);
54 $date = substr ($row['date'],6,2) . "." . substr ($row['date'],4,2) . "." . substr ($row['date'],0,4);
55 if ($row['billing_date'])
56 $billing_date = substr ($row['billing_date'],8,2) . "." . substr ($row['billing_date'],5,2) . "." . substr ($row['billing_date'],0,4);
58 $billing_date = ' ';
60 printf ("<tr class=\"t%d\">", $color);
61 printf ("<td width=\"10%%\" align=\"right\">%s</td>", $date);
62 printf ("<td width=\"10%%\" align=\"right\">%s</td>", $billing_date);
63 printf ("<td width=\"10%%\" align=\"center\">%s</td>", $row['category']);
64 printf ("<td width=\"0%%\"><a href=\"edit.php?oid=%d\">%s</a></td>",
65 $row['oid'], $row['description']);
66 printf ("<td width=\"10%%\" align=\"right\">%5.2f</td>", $row['price']);
67 $sum += $row['price'];
71 printf ("<tr class=\"t%d\">", $color);
72 echo ("<td width=\"85%\" colspan=\"4\"><b>Summe</b></td>");
73 printf ("<td width=\"15%%\" align=\"right\"><b>%5.2f</b></td>", $sum);
77 } elseif ($_POST["submit"] == "Overview") {
79 $query = "SELECT DISTINCT category FROM $table ";
80 if (strlen($_POST[year]))
81 $query .= "WHERE date ~* '$_POST[year]'";
83 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
85 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
86 $row = pg_fetch_array ($sth, $nr);
87 $catz[] = $row['category'];
90 if (count ($catz) > 0) {
91 echo ("<h3>Nach Kategorien sortiert</h3>");
92 echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
93 echo ("<tr class=\"head\">"
94 ."<th width=\"40%\">Kategorie</th>"
95 ."<th width=\"20%\">Einnahmen</th>"
96 ."<th width=\"20%\">Ausgaben</th>"
97 ."<th width=\"20%\">Summe</th></tr>");
102 for ($i=0; $i < count ($catz); $i++) {
103 $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price > 0.0", $catz[$i]);
105 if (strlen($_POST[year])) {
106 $query .= " AND date ~* '$_POST[year]'";
108 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
109 $row = pg_fetch_array ($sth, 0);
110 $input = $row[0]; $sum_input += $input;
111 $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price < 0.0", $catz[$i]);
112 if (strlen($_POST[year])) {
113 $query .= " AND date ~* '$_POST[year]'";
115 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
116 $row = pg_fetch_array ($sth, 0);
117 $output = -$row[0]; $sum_output += $output;
119 $sum = $input - $output;
120 printf ("<tr class=\"t%d\"><td width=\"40%%\">%s</td>"
121 ."<td align=\"right\" width=\"20%%\">%.2f</td>"
122 ."<td align=\"right\" width=\"20%%\">%.2f</td>"
123 ."<td align=\"right\" width=\"20%%\" class=\"%s\">%.2f</td></tr>",
124 $color, $catz[$i], $input, $output, $sum>0?'in':'out', $sum);
128 $sum = $sum_input - $sum_output;
129 printf ("<tr class=\"t%d\"><td width=\"40%%\"><b>Summe</b></td>"
130 ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
131 ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
132 ."<td align=\"right\" width=\"20%%\" class=\"%s\"><b>%.2f</b></td></tr>",
133 $color, $sum_input, $sum_output, $sum>0?'in':'out', $sum);
135 echo ("</table></div>");
138 } elseif ($_POST["submit"] == "Tally") {
139 if (strlen($_POST[year]))
140 $where = "date ~* '$_POST[year]' AND ";
144 if (strlen($_POST[year]))
145 printf("<h3>Jahresabrechnung %d</h3>", $_POST[year]);
147 echo ("<h3>Abrechnung</h3>");
149 $input = tally ($dbh, "Einnahmen", $where, 'in');
150 echo '<div style="padding-top: 15px;"></div>';
151 $output = tally ($dbh, "Ausgaben", $where, 'out');
153 if (strlen($_POST[year])) {
154 echo '<div style="padding-top: 15px;"></div>';
155 echo('<table class="smallfont border" cellspacing="1" cellpadding="1">');
156 $foo = $input["tax"] - $output["tax"];
157 printf('<tr><td>%s</td><td align="right">%.2f €</td><td>(%s)</td></tr>',
158 'Summe Steuern', $foo, $foo>0?'Nachzahlung':'Rückzahlung');
159 printf('<tr><td>%s</td><td align="right">%.2f €</td><td> </td></tr>',
160 'Summe netto', $input["netto"] - $output["netto"]);
161 $foo = $input["brutto"] - $output["brutto"];
162 printf('<tr><td>%s</td><td align="right">%.2f €</td><td>(%s)</td></tr>',
163 'Summe brutto', $foo, $foo>0?'Gewinn':'Verlust');
164 if (($privat = privat($dbh, $where)))
165 printf('<tr><td>%s</td><td align="right">%.2f €</td><td> </td></tr>',
166 'Privatentnahme', $privat);
171 function privat ($dbh, $where)
173 $query = "SELECT sum(price) * -1 AS privat"
174 . " FROM sales WHERE " . $where
175 . " category = 'privat'";
178 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
179 if (pg_NumRows($sth) != 1)
182 $row = pg_fetch_array ($sth, $nr);
183 return $row['privat'];
186 function tally ($dbh, $head, $where, $class)
188 echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
189 printf ("<tr class=\"head\"><th colspan=\"4\" align=\"left\"><strong>%s</strong></td></tr>", $head);
190 printf ("<tr class=\"head\">"
191 ."<th width=\"10%%\">USt.</th>"
192 ."<th width=\"30%%\">Steuer</th>"
193 ."<th width=\"30%%\">netto</th>"
194 ."<th width=\"30%%\">brutto</th></tr>");
196 $query = "SELECT tax_percent,sum(tax_assigned) AS tax,sum(price-tax_assigned) AS netto,sum(price) AS brutto"
197 . " FROM sales WHERE " . $where
198 . ($class=='in'?"price > 0.0":"price < 0.0")
199 . " AND category <> 'privat' "
200 . " GROUP BY tax_percent ORDER BY tax_percent";
205 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
206 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
207 $row = pg_fetch_array ($sth, $nr);
209 if ($row["brutto"] < 0) {
210 $row["tax"] = -$row["tax"];
211 $row["netto"] = -$row["netto"];
212 $row["brutto"] = -$row["brutto"];
215 printf ("<tr class=\"t%d\"><td align=\"right\" width=\"10%%\">%d%%</td>"
216 ."<td align=\"right\" width=\"30%%\">%.2f</td>"
217 ."<td align=\"right\" width=\"30%%\">%.2f</td>"
218 ."<td align=\"right\" width=\"30%%\">%.2f</td></tr>",
220 $row["tax_percent"], $row["tax"], $row["netto"], $row["brutto"]);
221 $sum["tax"] += $row["tax"];
222 $sum["netto"] += $row["netto"];
223 $sum["brutto"] += $row["brutto"];
227 printf ("<tr class=\"t%d\"><td width=\"10%%\"><b>Sum</b></td>"
228 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
229 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
230 ."<td align=\"right\" width=\"30%%\" class=\"%s\"><b>%.2f</b></td></tr>",
232 $sum["tax"], $sum["netto"], $class, $sum["brutto"]);
234 echo ("</table></div>\n");
240 <div style="padding-top: 5px;"></div>