1 #include <infocon.style>
3 <page func=InfoCon title="Buchhaltung">
5 <table class=font width=100% border=0 cellpadding=2 cellspacing=0>
7 <th width=15%>Datum</th>
8 <th width=20%>Kategorie</th>
9 <th width=50%>Verwendungszweck</th>
10 <th width=15%>Betrag</th>
13 $dbh = pg_pconnect ("<dbhost>", "<dbport>", "<dbname>")
14 or die("Unable to connect to SQL server");
16 pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");
20 if ($_POST["submit"] == "List") {
21 if (strlen($_POST[year])) {
22 # $where[] = "date >= '$year-01-01'";
24 # $where[] = "date < '$year-01-01'";
25 $where[] = "date ~* '$_POST[year]'";
27 if (strlen($_POST[category])) {
28 $where[] = "category = '$_POST[category]'";
30 if ($_POST[input] && !$_POST[output]) {
31 $where[] = "price > 0.0";
32 } elseif ($_POST[output] && !$_POST[input]) {
33 $where[] = "price < 0.0";
36 $query = "SELECT date,oid,category,description,price FROM $table ";
37 if (count($where) > 0) {
38 $query .= " WHERE " . implode ($where, " AND ");
40 $query .= " ORDER BY date,oid";
41 $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
45 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
46 $row = pg_fetch_array ($sth, $nr);
47 $color = $color=="<cold>"?"<coln>":"<cold>";
49 # $date = explode (" ", $row['date']);
50 # $date = explode ("-", $date[0]);
51 # $date = sprintf ("%d.%d.%d", $date[2], $date[1], $date[0]);
52 $date = substr ($row['date'],6,2) . "." . substr ($row['date'],4,2) . "." . substr ($row['date'],0,4);
54 printf ("<tr bgcolor=\"#%s\">", $color);
55 printf ("<td width=\"15%%\" align=\"right\">%s</td>", $date);
56 printf ("<td width=\"20%%\">%s</td>", $row['category']);
57 printf ("<td width=\"50%%\"><a href=\"edit.php?oid=%d\">%s</a></td>",
58 $row['oid'], $row['description']);
59 printf ("<td width=\"15%%\" align=\"right\">%5.2f</td>", $row['price']);
60 $sum += $row['price'];
64 echo ("<td width=\"85%\" colspan=\"3\"><b>Summe</b></td>");
65 printf ("<td width=\"15%%\" align=\"right\"><b>%5.2f</b></td>", $sum);
69 } elseif ($_POST["submit"] == "Overview") {
71 $query = "SELECT DISTINCT category FROM $table ";
72 if (strlen($_POST[year]))
73 $query .= "WHERE date ~* '$_POST[year]'";
75 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
77 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
78 $row = pg_fetch_array ($sth, $nr);
79 $catz[] = $row['category'];
82 if (count ($catz) > 0) {
83 echo ("<h3>Nach Kategorien sortiert</h3>");
84 echo ("<div align=\"center\"><table class=\"font\" width=\"80%\" border=\"0\" cellpadding=\"2\" cellspacing=\"0\" summary=\"\">");
85 echo ("<tr bgcolor=\"#<colh>\">"
86 ."<th width=\"70%\">Kategorie</th>"
87 ."<th width=\"10%\">Einnahmen</th>"
88 ."<th width=\"10%\">Ausgaben</th>"
89 ."<th width=\"10%\">Summe</th></tr>");
94 for ($i=0; $i < count ($catz); $i++) {
95 $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price > 0.0", $catz[$i]);
96 $color = $color=="<cold>"?"<coln>":"<cold>";
98 if (strlen($_POST[year])) {
99 $query .= " AND date ~* '$_POST[year]'";
101 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
102 $row = pg_fetch_array ($sth, 0);
103 $input = $row[0]; $sum_input += $input;
104 $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 $output = -$row[0]; $sum_output += $output;
112 printf ("<tr bgcolor=\"#%s\"><td width=\"70%%\">%s</td>"
113 ."<td align=\"right\" width=\"10%%\">%.2f</td>"
114 ."<td align=\"right\" width=\"10%%\">%.2f</td>"
115 ."<td align=\"right\" width=\"10%%\">%.2f</td></tr>",
116 $color, $catz[$i], $input, $output, $input - $output);
119 printf ("<tr bgcolor=\"#%s\"><td width=\"70%%\"><b>Summe</b></td>"
120 ."<td align=\"right\" width=\"10%%\"><b>%.2f</b></td>"
121 ."<td align=\"right\" width=\"10%%\"><b>%.2f</b></td>"
122 ."<td align=\"right\" width=\"10%%\"><b>%.2f</b></td></tr>",
123 $color, $sum_input, $sum_output, $sum_input - $sum_output);
124 echo ("</table></div>");
127 } elseif ($_POST["submit"] == "Tally") {
128 if (strlen($_POST[year]))
129 $where = "date ~* '$_POST[year]' AND ";
133 echo ("<h3>Abrechnung</h3>");
135 $output = tally ($dbh, "Aus", $where . "price < 0.0");
136 $input = tally ($dbh, "Ein", $where . "price < 0.0");
138 printf ("<p>Summe Steuern: %.2f", $input["tax"] - $output["tax"]);
139 printf ("<br>Summe netto: %.2f", $input["netto"] - $output["netto"]);
140 printf ("<br>Summe btutto: %.2f</p>", $input["brutto"] - $output["brutto"]);
143 function tally ($dbh, $head, $where)
145 echo ("<div align=\"center\"><table class=\"font\" width=\"80%\" border=\"0\" cellpadding=\"2\" cellspacing=\"0\" summary=\"\">");
146 printf ("<tr bgcolor=\"#<colh>\">"
147 ."<th width=\"10%%\">%s%%</th>"
148 ."<th width=\"30%%\">Steuer</th>"
149 ."<th width=\"30%%\">netto</th>"
150 ."<th width=\"30%%\">brutto</th></tr>",
153 $query = "SELECT tax_percent,sum(tax_assigned) AS tax,sum(price-tax_assigned) AS netto,sum(price) AS brutto"
154 . " FROM sales WHERE " . $where
155 . " GROUP BY tax_percent ORDER BY tax_percent";
160 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
161 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
162 $row = pg_fetch_array ($sth, $nr);
163 $color = $color=="<cold>"?"<coln>":"<cold>";
165 $output = -$row[0]; $sum_output += $output;
167 printf ("<tr bgcolor=\"#%s\"><td align=\"right\" width=\"10%%\">%d</td>"
168 ."<td align=\"right\" width=\"30%%\">%.2f</td>"
169 ."<td align=\"right\" width=\"30%%\">%.2f</td>"
170 ."<td align=\"right\" width=\"30%%\">%.2f</td></tr>",
172 $row["tax_percent"], $row["tax"], $row["netto"], $row["brutto"]);
173 $sum["tax"] += $row["tax"];
174 $sum["netto"] += $row["netto"];
175 $sum["brutto"] += $row["brutto"];
178 printf ("<tr bgcolor=\"#%s\"><td width=\"10%%\"><b>Sum</b></td>"
179 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
180 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
181 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td></tr>",
183 $sum["tax"], $sum["netto"], $sum["brutto"]);
185 echo ("</table></div>\n");