1 #include <infocon.style>
4 <page func=InfoCon title="Buchhaltung">
5 <script type="text/javascript" src="<root_prefix>jquery.editable.js"></script>
8 $dbh = pg_pconnect ("<dbhost>", "<dbport>", "<dbname>")
9 or die("Unable to connect to SQL server");
11 pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");
15 if ($_POST["submit"] == "List") {
17 <table class="smallfont border" width=100% border=0 cellpadding=1 cellspacing=1>
19 <th width=10%>Datum</th>
20 <th width=10%>Buchung</th>
21 <th width=10%>Kategorie</th>
22 <th width=2%> </th>
23 <th width=58%>Verwendungszweck</th>
24 <th width=10%>Betrag</th>
27 if (strlen($_POST[year])) {
28 # $where[] = "date >= '$year-01-01'";
30 # $where[] = "date < '$year-01-01'";
31 $where[] = "date ~* '$_POST[year]'";
33 if (strlen($_POST[category])) {
34 $where[] = "category = '$_POST[category]'";
36 if ($_POST[input] && !$_POST[output]) {
37 $where[] = "price > 0.0";
38 } elseif ($_POST[output] && !$_POST[input]) {
39 $where[] = "price < 0.0";
42 $query = "SELECT nr,date,billing_date,category,description,price,uid FROM $table ";
43 if (count($where) > 0) {
44 $query .= " WHERE " . implode ($where, " AND ");
46 $query .= " ORDER BY date,oid";
47 $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
51 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
52 $row = pg_fetch_array ($sth, $nr);
54 # $date = explode (" ", $row['date']);
55 # $date = explode ("-", $date[0]);
56 # $date = sprintf ("%d.%d.%d", $date[2], $date[1], $date[0]);
57 $date = substr ($row['date'],6,2) . "." . substr ($row['date'],4,2) . "." . substr ($row['date'],0,4);
58 if ($row['billing_date'])
59 $billing_date = substr ($row['billing_date'],8,2) . "." . substr ($row['billing_date'],5,2) . "." . substr ($row['billing_date'],0,4);
61 $billing_date = ' ';
63 printf ("<tr class=\"t%d\">", $color);
64 printf ("<td width=\"10%%\" align=\"right\">%s</td>", $date);
65 printf ("<td width=\"10%%\" align=\"right\">%s</td>", $billing_date);
66 printf ("<td width=\"10%%\" align=\"center\">%s</td>", $row['category']);
67 printf ("<td width=\"2%%\" align=\"center\"><img src=\"<root_prefix>pix/if_europeanunion_4618.png\" %s alt=\"\" item_id=\"%d\"></td>",
68 $row['uid'] ? "class=\"eu\" title=\"{$row['uid']}\"" : "class=\"eu\"",
70 printf ("<td width=\"58%%\"><span class=\"edit\" route=\"Sales/EditDescription\" item_id=\"%d\">%s</span></td>",
71 $row['nr'], $row['description']);
72 printf ("<td width=\"10%%\" align=\"right\">%5.2f</td>", $row['price']);
73 $sum += $row['price'];
77 printf ("<tr class=\"t%d\">", $color);
78 echo ("<td width=\"85%\" colspan=\"5\"><b>Summe</b></td>");
79 printf ("<td width=\"15%%\" align=\"right\"><b>%5.2f</b></td>", $sum);
83 } elseif ($_POST["submit"] == "Overview") {
85 $query = "SELECT DISTINCT category FROM $table ";
86 if (strlen($_POST[year]))
87 $query .= "WHERE date ~* '$_POST[year]'";
89 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
91 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
92 $row = pg_fetch_array ($sth, $nr);
93 $catz[] = $row['category'];
96 if (count ($catz) > 0) {
97 echo ("<h3>Nach Kategorien sortiert</h3>");
98 echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
99 echo ("<tr class=\"head\">"
100 ."<th width=\"40%\">Kategorie</th>"
101 ."<th width=\"20%\">Einnahmen</th>"
102 ."<th width=\"20%\">Ausgaben</th>"
103 ."<th width=\"20%\">Summe</th></tr>");
108 for ($i=0; $i < count ($catz); $i++) {
109 $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price > 0.0", $catz[$i]);
111 if (strlen($_POST[year])) {
112 $query .= " AND date ~* '$_POST[year]'";
114 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
115 $row = pg_fetch_array ($sth, 0);
116 $input = $row[0]; $sum_input += $input;
117 $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price < 0.0", $catz[$i]);
118 if (strlen($_POST[year])) {
119 $query .= " AND date ~* '$_POST[year]'";
121 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
122 $row = pg_fetch_array ($sth, 0);
123 $output = -$row[0]; $sum_output += $output;
125 $sum = $input - $output;
126 printf ("<tr class=\"t%d\"><td width=\"40%%\">%s</td>"
127 ."<td align=\"right\" width=\"20%%\">%.2f</td>"
128 ."<td align=\"right\" width=\"20%%\">%.2f</td>"
129 ."<td align=\"right\" width=\"20%%\" class=\"%s\">%.2f</td></tr>",
130 $color, $catz[$i], $input, $output, $sum>0?'in':'out', $sum);
134 $sum = $sum_input - $sum_output;
135 printf ("<tr class=\"t%d\"><td width=\"40%%\"><b>Summe</b></td>"
136 ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
137 ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
138 ."<td align=\"right\" width=\"20%%\" class=\"%s\"><b>%.2f</b></td></tr>",
139 $color, $sum_input, $sum_output, $sum>0?'in':'out', $sum);
141 echo ("</table></div>");
144 } elseif ($_POST["submit"] == "Tally") {
145 if (strlen($_POST[year]))
146 $where = "date ~* '$_POST[year]' AND ";
150 if (strlen($_POST[year]))
151 printf("<h3>Jahresabrechnung %d</h3>", $_POST[year]);
153 echo ("<h3>Abrechnung</h3>");
155 $input = tally ($dbh, "Einnahmen", $where, 'in');
156 echo '<div style="padding-top: 15px;"></div>';
157 $output = tally ($dbh, "Ausgaben", $where, 'out');
159 if (strlen($_POST[year])) {
160 echo '<div style="padding-top: 15px;"></div>';
161 echo('<table class="smallfont border" cellspacing="1" cellpadding="1">');
162 $foo = $input["tax"] - $output["tax"];
163 printf('<tr><td>%s</td><td align="right">%.2f €</td><td>(%s)</td></tr>',
164 'Summe Steuern', $foo, $foo>0?'Nachzahlung':'Rückzahlung');
165 printf('<tr><td>%s</td><td align="right">%.2f €</td><td> </td></tr>',
166 'Summe netto', $input["netto"] - $output["netto"]);
167 $foo = $input["brutto"] - $output["brutto"];
168 printf('<tr><td>%s</td><td align="right">%.2f €</td><td>(%s)</td></tr>',
169 'Summe brutto', $foo, $foo>0?'Gewinn':'Verlust');
170 if (($privat = privat($dbh, $where)))
171 printf('<tr><td>%s</td><td align="right">%.2f €</td><td> </td></tr>',
172 'Privatentnahme', $privat);
177 function privat ($dbh, $where)
179 $query = "SELECT sum(price) * -1 AS privat"
180 . " FROM sales WHERE " . $where
181 . " category = 'privat'";
184 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
185 if (pg_NumRows($sth) != 1)
188 $row = pg_fetch_array ($sth, $nr);
189 return $row['privat'];
192 function tally ($dbh, $head, $where, $class)
194 echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
195 printf ("<tr class=\"head\"><th colspan=\"4\" align=\"left\"><strong>%s</strong></td></tr>", $head);
196 printf ("<tr class=\"head\">"
197 ."<th width=\"10%%\">USt.</th>"
198 ."<th width=\"30%%\">Steuer</th>"
199 ."<th width=\"30%%\">netto</th>"
200 ."<th width=\"30%%\">brutto</th></tr>");
202 $query = "SELECT tax_percent,sum(tax_assigned) AS tax,sum(price-tax_assigned) AS netto,sum(price) AS brutto"
203 . " FROM sales WHERE " . $where
204 . ($class=='in'?"price > 0.0":"price < 0.0")
205 . " AND category <> 'privat' "
206 . " GROUP BY tax_percent ORDER BY tax_percent";
211 $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
212 for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
213 $row = pg_fetch_array ($sth, $nr);
215 if ($row["brutto"] < 0) {
216 $row["tax"] = -$row["tax"];
217 $row["netto"] = -$row["netto"];
218 $row["brutto"] = -$row["brutto"];
221 printf ("<tr class=\"t%d\"><td align=\"right\" width=\"10%%\">%d%%</td>"
222 ."<td align=\"right\" width=\"30%%\">%.2f</td>"
223 ."<td align=\"right\" width=\"30%%\">%.2f</td>"
224 ."<td align=\"right\" width=\"30%%\">%.2f</td></tr>",
226 $row["tax_percent"], $row["tax"], $row["netto"], $row["brutto"]);
227 $sum["tax"] += $row["tax"];
228 $sum["netto"] += $row["netto"];
229 $sum["brutto"] += $row["brutto"];
233 printf ("<tr class=\"t%d\"><td width=\"10%%\"><b>Sum</b></td>"
234 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
235 ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
236 ."<td align=\"right\" width=\"30%%\" class=\"%s\"><b>%.2f</b></td></tr>",
238 $sum["tax"], $sum["netto"], $class, $sum["brutto"]);
240 echo ("</table></div>\n");
246 <div style="padding-top: 5px;"></div>
248 <script type="text/javascript">
251 make_editable('table.smallfont tr span.edit');
253 $('table.smallfont tr img.eu').click(eu_popup);
256 var euwindow = false;
257 function eu_popup(event)
259 var content = ['<div id="eubody">',
260 '<input type="hidden" id="eu_nr">',
261 '<label for="eu_uid"></label>',
262 '<input type="text" id="eu_uid" style="width:100%;">',
264 '<button style="margin-top:5px;">Speichern</button>',
268 euwindow = new Popup('Inner-EU-Rechnung', '250px', false, content.join(''));
269 euwindow.centerPopup();
270 $('div#eubody button').click(function(event){
271 $.invoke('Sales/SetUID',
272 {id: $('#eu_nr').val(),
273 uid: $('#eu_uid').val()},
275 euwindow.closePopup();
276 show_message('UID des Lieferanten für innergemeinschaftlichen Erwerb gespeichert', 3);
280 euwindow.openPopup();
283 $('#eu_nr').val($(event.target).attr('item_id'));
284 $.invoke('Sales/GetUID',
285 {id: $(event.target).attr('item_id')},
287 $('label[for="eu_uid"]').text(data.description);
288 $('#eu_uid').val(data.uid);