modules/buchhaltung/report.php
36114c78
 <?php
 
 require_role(ROLE_SYSADMIN);
 
 $title = 'Report';
 
 
 $year = date("Y")-1;
0fe41395
 if (isset($_GET['year'])) {
     $year = (int) $_GET['year'];
 }
36114c78
 
62d8a85e
 $typeresult = db_query("SELECT id, description, investment FROM buchhaltung.types");
672148af
 $dataresult = db_query("SELECT id, date, description, invoice_id, direction, type, amount, tax_rate, gross FROM buchhaltung.transactions WHERE date BETWEEN :from and :to ORDER BY date", array(":from" => $year."-01-01", ":to" => $year."-12-31"));
36114c78
 
 $types = array();
 $data_by_type = array();
 $sum_by_type = array();
62d8a85e
 $investment_types = array();
36114c78
 while ($t = $typeresult->fetch()) {
     $types[$t['id']] = $t['description'];
     $data_by_type[$t['id']] = array();
     $sum_by_type[$t['id']] = 0.0;
62d8a85e
     if ($t['investment'] == 1) {
         $investment_types[$t['id']] = $t;
     }
36114c78
 }
 
 while ($line = $dataresult->fetch()) {
     $data_by_type[$line['type']][] = $line;
 }
 
 
 output("Journal für $year (01.01.$year-31.12.$year, gruppiert nach Buchungskonten)");
 
 DEBUG($types);
62d8a85e
 DEBUG($investment_types);
36114c78
 $net_by_type = array(0 => array(-1 => array(), 0 => array(), 19 => array()));
 $umsatzsteuer = 0.0;
42dbfac4
 $ustbetraege = array();
36114c78
 $vorsteuer = 0.0;
 foreach ($types as $id => $t) {
     if (count($data_by_type[$id]) == 0) {
         continue;
     }
     output("<h3>$t</h3>");
672148af
     output('<table style="font-size: 10pt;">');
36114c78
     $umsatz19proz = 0.0;
     $umsatz0proz = 0.0;
     $umsatzandereproz = 0.0;
     $netsum = 0.0;
     $ustsum = 0.0;
     foreach ($data_by_type[$id] as $line) {
         $net = $line['amount'];
         if ($line['gross'] == 1 && $line['tax_rate'] > 0) {
             $net = $net / (1.0+($line['tax_rate']/100));
         }
         if ($line['direction'] == 'out') {
             $net = -$net;
         }
         $ust = $net * ($line['tax_rate']/100);
         if ($line['tax_rate'] == 19.0) {
             $umsatz19proz += $net;
         } elseif ($line['tax_rate'] == 0.0) {
             $umsatz0proz += $net;
         } else {
             $umsatzandereproz += $net;
         }
         $netsum += $net;
         $ustsum += $ust;
         if ($id == 0) {
42dbfac4
             if (!isset($ustbetraege[$line['tax_rate']])) {
                 $ustbetraege[$line['tax_rate']] = 0;
             }
             $ustbetraege[$line['tax_rate']] += $ust;
36114c78
             $umsatzsteuer += $ust;
         } else {
             $vorsteuer += $ust;
         }
         $gross = $net + $ust;
         $net = str_replace('.', ',', sprintf('%.2f €', $net));
         $ust = str_replace('.', ',', sprintf('%.2f €', $ust));
         $gross = str_replace('.', ',', sprintf('%.2f €', $gross));
42dbfac4
         output("<tr><td>".$line['date']."</td><td>".$line['description']."</td><td style=\"text-align: right;\">".$net."</td><td style=\"text-align: right;\">".$line['tax_rate']."%</td><td style=\"text-align: right;\">".$ust."</td><td style=\"text-align: right;\">".$gross."</td></tr>\n");
36114c78
     }
     if ($id == 0) {
         $net_by_type[0][-1] = $umsatzandereproz;
         $net_by_type[0][0] = $umsatz0proz;
         $net_by_type[0][19] = $umsatz19proz;
     } else {
         $net_by_type[$id] = $netsum;
     }
     $netsum = str_replace('.', ',', sprintf('%.2f €', $netsum));
     $ustsum = str_replace('.', ',', sprintf('%.2f €', $ustsum));
42dbfac4
     output("<tr><td colspan=\"2\" style=\"font-weight: bold;text-align: right;\">Summe $t:</td><td style=\"font-weight: bold;text-align: right;\">$netsum</td><td></td><td style=\"font-weight: bold;text-align: right;\">$ustsum</td><td></td></tr>\n");
36114c78
     output('</table>');
 }
 
 output("<h3>Summen</h3>");
 
 output('<table>');
 $einnahmensumme = 0.0;
 output("<tr><td>Einnahmen 19% USt netto</td><td style=\"text-align: right;\">".number_format($net_by_type[0][19], 2, ',', '.')." €</td></tr>");
 $einnahmensumme += $net_by_type[0][19];
42dbfac4
 output("<tr><td>Einnahme Umsatzsteuer 19%</td><td style=\"text-align: right;\">".number_format($ustbetraege[19], 2, ',', '.')." €</td></tr>");
 $einnahmensumme += $ustbetraege[19];
36114c78
 output("<tr><td>Einnahmen innergem. Lieferung (steuerfrei §4/1b UStG)</td><td style=\"text-align: right;\">".number_format($net_by_type[0][0], 2, ',', '.')." €</td></tr>");
 $einnahmensumme += $net_by_type[0][0];
 output("<tr><td>Einnahmen EU-Ausland (VATMOSS)</td><td style=\"text-align: right;\">".number_format($net_by_type[0][-1], 2, ',', '.')." €</td></tr>");
 $einnahmensumme += $net_by_type[0][-1];
42dbfac4
 $einzelust = '';
 foreach ($ustbetraege as $satz => $ust) {
     if ($satz == 0 || $satz == 19) {
         continue;
     }
     output("<tr><td>- Umsatzsteuer $satz%</td><td style=\"text-align: right;\">".number_format($ust, 2, ',', '.')." €</td></tr>");
     $einnahmensumme += $ust;
 }
36114c78
 
 output("<tr><td><b>Summe Einnahmen:</b></td><td style=\"text-align: right;\"><b>".number_format($einnahmensumme, 2, ',', '.')." €</td></tr>");
 output("<tr><td colspan=\"2\"></td></tr>");
 $ausgabensumme = 0.0;
 foreach ($types as $id => $t) {
62d8a85e
     if ($id == 0 || !isset($net_by_type[$id]) || array_key_exists($id, $investment_types)) {
36114c78
         continue;
     }
     $ausgabensumme -= $net_by_type[$id];
     output("<tr><td>".$t."</td><td style=\"text-align: right;\">".number_format(-$net_by_type[$id], 2, ',', '.')." €</td></tr>");
 }
 
 output("<tr><td>Vorsteuer</td><td style=\"text-align: right;\">".number_format(-$vorsteuer, 2, ',', '.')." €</td></tr>");
 $ausgabensumme -= $vorsteuer;
 output("<tr><td><b>Summe Ausgaben:</b></td><td style=\"text-align: right;\"><b>".number_format($ausgabensumme, 2, ',', '.')." €</td></tr>");
 output("<tr><td colspan=\"2\"></td></tr>");
 
 output("<tr><td><b>Überschuss aus laufendem Betrieb:</b></td><td style=\"text-align: right;\"><b>".number_format($einnahmensumme-$ausgabensumme, 2, ',', '.')." €</td></tr>");
 output('</table>');
62d8a85e
 
 foreach ($investment_types as $id => $type) {
     if (isset($net_by_type[$id])) {
         output('<p>Neue Anlagegüter <strong>'.$type['description'].'</strong>: '.number_format(-$net_by_type[$id], 2, ',', '.')." €</p>");
     }
 }