#include <infocon.style>

<future>
<page func=InfoCon title="Buchhaltung">
<script type="text/javascript" src="<root_prefix>jquery.editable.js"></script>

<?
  $dbh = pg_pconnect ("<dbhost>", "<dbport>", "<dbname>")
	       or die("Unable to connect to SQL server");

  pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");

  $table = "sales";

if ($_POST["submit"] == "List") {
?>
<table class="smallfont border" width=100% border=0 cellpadding=1 cellspacing=1>
<tr class="head">
  <th width=10%>Datum</th>
  <th width=10%>Buchung</th>
  <th width=10%>Kategorie</th>
  <th width=2%>&nbsp;</th>
  <th width=58%>Verwendungszweck</th>
  <th width=10%>Betrag</th>
</tr>
<?
  if (strlen($_POST[year])) {
    # $where[] = "date >= '$year-01-01'";
    # $year++;
    # $where[] = "date < '$year-01-01'";
    $where[] = "date ~* '$_POST[year]'";
  }
  if (strlen($_POST[category])) {
    $where[] = "category = '$_POST[category]'";
  }
  if ($_POST[input] && !$_POST[output]) {
    $where[] = "price > 0.0";
  } elseif ($_POST[output] && !$_POST[input]) {
    $where[] = "price < 0.0";
  }

  $query = "SELECT nr,date,billing_date,category,description,price,uid FROM $table ";
  if (count($where) > 0) {
    $query .= " WHERE " . implode ($where, " AND ");
  }
  $query .= " ORDER BY date,oid";
  $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");

  $sum = 0.0;
  $color = 0;
  for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
    $row = pg_fetch_array ($sth, $nr);

    # $date = explode (" ", $row['date']);
    # $date = explode ("-", $date[0]);
    # $date = sprintf ("%d.%d.%d", $date[2], $date[1], $date[0]);
    $date = substr ($row['date'],6,2) . "." . substr ($row['date'],4,2) . "." . substr ($row['date'],0,4);
    if ($row['billing_date'])
      $billing_date = substr ($row['billing_date'],8,2) . "." . substr ($row['billing_date'],5,2) . "." . substr ($row['billing_date'],0,4);
    else
      $billing_date = '&nbsp;';

    printf ("<tr class=\"t%d\">", $color);
    printf ("<td width=\"10%%\" align=\"right\">%s</td>", $date);
    printf ("<td width=\"10%%\" align=\"right\">%s</td>", $billing_date);
    printf ("<td width=\"10%%\" align=\"center\">%s</td>", $row['category']);
    printf ("<td width=\"2%%\" align=\"center\"><img src=\"<root_prefix>pix/if_europeanunion_4618.png\" %s alt=\"\" item_id=\"%d\"></td>",
	    $row['uid'] ? "class=\"eu\" title=\"{$row['uid']}\"" : "class=\"eu\"",
	    $row['nr']);
    printf ("<td width=\"58%%\"><span class=\"edit\" route=\"Sales/EditDescription\" item_id=\"%d\">%s</span></td>",
      $row['nr'], $row['description']);
    printf ("<td width=\"10%%\" align=\"right\">%5.2f</td>", $row['price']);
    $sum += $row['price'];
    echo ("</tr>");
    $color = !$color;
  }
  printf ("<tr class=\"t%d\">", $color);
  echo ("<td width=\"85%\" colspan=\"5\"><b>Summe</b></td>");
  printf ("<td width=\"15%%\" align=\"right\"><b>%5.2f</b></td>", $sum);
  echo ("</tr>");
  echo ("</table>");

} elseif ($_POST["submit"] == "Overview") {

    $query = "SELECT DISTINCT category FROM $table ";
    if (strlen($_POST[year]))
      $query .= "WHERE date ~* '$_POST[year]'";

    $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");

    for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
      $row = pg_fetch_array ($sth, $nr);
      $catz[] = $row['category'];
    }

    if (count ($catz) > 0) {
      echo ("<h3>Nach Kategorien sortiert</h3>");
      echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
      echo ("<tr class=\"head\">"
	   ."<th width=\"40%\">Kategorie</th>"
	   ."<th width=\"20%\">Einnahmen</th>"
	   ."<th width=\"20%\">Ausgaben</th>"
	   ."<th width=\"20%\">Summe</th></tr>");
      sort ($catz);

      $color = 0;
      $sum_output = 0;
      for ($i=0; $i < count ($catz); $i++) {
        $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price > 0.0", $catz[$i]);

	if (strlen($_POST[year])) {
	  $query .= " AND date ~* '$_POST[year]'";
	}
        $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
	$row = pg_fetch_array ($sth, 0);
	$input = $row[0]; $sum_input += $input;
        $query = sprintf ("SELECT SUM (price) FROM $table WHERE category = '%s' AND price < 0.0", $catz[$i]);
	if (strlen($_POST[year])) {
	  $query .= " AND date ~* '$_POST[year]'";
	}
        $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
	$row = pg_fetch_array ($sth, 0);
	$output = -$row[0]; $sum_output += $output;

	$sum = $input - $output;
	printf ("<tr class=\"t%d\"><td width=\"40%%\">%s</td>"
	       ."<td align=\"right\" width=\"20%%\">%.2f</td>"
	       ."<td align=\"right\" width=\"20%%\">%.2f</td>"
	       ."<td align=\"right\" width=\"20%%\" class=\"%s\">%.2f</td></tr>",
		$color, $catz[$i], $input, $output, $sum>0?'in':'out', $sum);
	$color = !$color;
      }

      $sum = $sum_input - $sum_output;
      printf ("<tr class=\"t%d\"><td width=\"40%%\"><b>Summe</b></td>"
	     ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
	     ."<td align=\"right\" width=\"20%%\"><b>%.2f</b></td>"
	     ."<td align=\"right\" width=\"20%%\" class=\"%s\"><b>%.2f</b></td></tr>",
	      $color, $sum_input, $sum_output, $sum>0?'in':'out', $sum);

      echo ("</table></div>");

    }
  } elseif ($_POST["submit"] == "Tally") {
    if (strlen($_POST[year]))
      $where = "date ~* '$_POST[year]' AND ";
    else
      $where = '';

    if (strlen($_POST[year]))
      printf("<h3>Jahresabrechnung %d</h3>", $_POST[year]);
    else
      echo ("<h3>Abrechnung</h3>");

    $input = tally ($dbh, "Einnahmen", $where, 'in');
    echo '<div style="padding-top: 15px;"></div>';
    $output = tally ($dbh, "Ausgaben", $where, 'out');

    if (strlen($_POST[year])) {
      echo '<div style="padding-top: 15px;"></div>';
      echo('<table class="smallfont border" cellspacing="1" cellpadding="1">');
      $foo = $input["tax"] - $output["tax"];
      printf('<tr><td>%s</td><td align="right">%.2f&nbsp;&euro;</td><td>(%s)</td></tr>',
	     'Summe Steuern', $foo, $foo>0?'Nachzahlung':'Rückzahlung');
      printf('<tr><td>%s</td><td align="right">%.2f&nbsp;&euro;</td><td>&nbsp;</td></tr>',
	     'Summe netto', $input["netto"] - $output["netto"]);
      $foo = $input["brutto"] - $output["brutto"];
      printf('<tr><td>%s</td><td align="right">%.2f&nbsp;&euro;</td><td>(%s)</td></tr>',
	     'Summe brutto', $foo, $foo>0?'Gewinn':'Verlust');
      if (($privat = privat($dbh, $where)))
	printf('<tr><td>%s</td><td align="right">%.2f&nbsp;&euro;</td><td>&nbsp;</td></tr>',
	       'Privatentnahme', $privat);
      echo('</table>');
    }
  }

function privat ($dbh, $where)
{
  $query = "SELECT sum(price) * -1 AS privat"
    . " FROM sales WHERE " . $where
    . " category = 'privat'";

  error_log($query);
  $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
  if (pg_NumRows($sth) != 1)
    return 0;

  $row = pg_fetch_array ($sth, $nr);
  return $row['privat'];
}

function tally ($dbh, $head, $where, $class)
{
  echo ("<div align=\"center\"><table class=\"smallfont border\" width=\"60%\" cellpadding=\"1\" cellspacing=\"1\" summary=\"\">");
  printf ("<tr class=\"head\"><th colspan=\"4\" align=\"left\"><strong>%s</strong></td></tr>", $head);
  printf ("<tr class=\"head\">"
	  ."<th width=\"10%%\">USt.</th>"
	  ."<th width=\"30%%\">Steuer</th>"
	  ."<th width=\"30%%\">netto</th>"
	  ."<th width=\"30%%\">brutto</th></tr>");
  
  $query = "SELECT tax_percent,sum(tax_assigned) AS tax,sum(price-tax_assigned) AS netto,sum(price) AS brutto"
    . " FROM sales WHERE " . $where
    . ($class=='in'?"price > 0.0":"price < 0.0")
    . " AND category <> 'privat' "
    . " GROUP BY tax_percent ORDER BY tax_percent";

  $color = 0;
  $sum = array();

  $sth = pg_exec ($dbh, $query); // or die("Datenbank-Abfrage!");
  for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
    $row = pg_fetch_array ($sth, $nr);

    if ($row["brutto"] < 0) {
      $row["tax"] = -$row["tax"];
      $row["netto"] = -$row["netto"];
      $row["brutto"] = -$row["brutto"];
    }

    printf ("<tr class=\"t%d\"><td align=\"right\" width=\"10%%\">%d%%</td>"
	    ."<td align=\"right\" width=\"30%%\">%.2f</td>"
	    ."<td align=\"right\" width=\"30%%\">%.2f</td>"
	    ."<td align=\"right\" width=\"30%%\">%.2f</td></tr>",
	    $color,
	    $row["tax_percent"], $row["tax"], $row["netto"], $row["brutto"]);
    $sum["tax"] += $row["tax"];
    $sum["netto"] += $row["netto"];
    $sum["brutto"] += $row["brutto"];
    $color = !$color;
  }

  printf ("<tr class=\"t%d\"><td width=\"10%%\"><b>Sum</b></td>"
	  ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
	  ."<td align=\"right\" width=\"30%%\"><b>%.2f</b></td>"
	  ."<td align=\"right\" width=\"30%%\" class=\"%s\"><b>%.2f</b></td></tr>",
	  $color,
	  $sum["tax"], $sum["netto"], $class, $sum["brutto"]);

  echo ("</table></div>\n");

  return $sum;
}

?>
<div style="padding-top: 5px;"></div>

<script type="text/javascript">
<protect>
$(function(){
    make_editable('table.smallfont tr span.edit');

    $('table.smallfont tr img.eu').click(eu_popup);
});

var euwindow = false;
function eu_popup(event)
{
    var content = ['<div id="eubody">',
		   '<input type="hidden" id="eu_nr">',
		   '<label for="eu_uid"></label>',
		   '<input type="text" id="eu_uid" style="width:100%;">',
		   '<br>',
		   '<button style="margin-top:5px;">Speichern</button>',
		   '</div>'];

    if (!euwindow) {
	euwindow = new Popup('Inner-EU-Rechnung', '250px', false, content.join(''));
	euwindow.centerPopup();
	$('div#eubody button').click(function(event){
	    $.invoke('Sales/SetUID',
		     {id: $('#eu_nr').val(),
		      uid: $('#eu_uid').val()},
		     function(data){
			 euwindow.closePopup();
			 show_message('UID des Lieferanten für innergemeinschaftlichen Erwerb gespeichert', 3);
		     });
	});
    } else {
	euwindow.openPopup();
    }

    $('#eu_nr').val($(event.target).attr('item_id'));
    $.invoke('Sales/GetUID',
	     {id: $(event.target).attr('item_id')},
	     function(data){
		 $('label[for="eu_uid"]').text(data.description);
		 $('#eu_uid').val(data.uid);
	     });
}

</protect>
</script>
</page>

# Local variables:
# mode: php
# end: