+<future>
<?
- $dbh = pg_pconnect ("<dbhost>", "<dbport>", "<dbname>")
- or die("Unable to connect to SQL server");
-
$ktoname = '';
- if (isset($_REQUEST[blzkto])) {
- $query = sprintf ("SELECT name FROM account_names WHERE blz_kto = '%s'",
- $_REQUEST[blzkto]);
- $sth = pg_exec ($dbh, $query);
-
- $both = explode (":", $_REQUEST[blzkto]);
- if (pg_NumRows ($sth) > 0) {
- $row = pg_fetch_array ($sth, 0);
- $ktoname = sprintf ("%s (BLZ %s, Konto %s)", $row['name'], $both[0], $both[1]);
- } else {
- $ktoname = sprintf ("BLZ %s, Konto %s", $both[0], $both[1]);
- }
+ if (isset($_REQUEST['blzkto'])) {
+ $account = new AccountName($_REQUEST['blzkto']);
+ $ktoname = $account->name();
}
?>
<h3 class=bar><?=$ktoname?></font></h3>
</define-tag>
-
-<define-tag account_overview>
-<?
- function account_names ($dbh, $display, $currency)
- {
- if ($currency == "eur")
- $table = "account";
- else
- $table = "account_dm";
-
- $query = "SELECT DISTINCT $table.blz_kto,name "
- ."FROM $table,account_names "
- ."WHERE $table.blz_kto = account_names.blz_kto "
- ."$display "
- ."ORDER BY name";
- $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
-
- $ret = array();
- for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
- $row = pg_fetch_array ($sth, $nr);
- $ret[$row['blz_kto']] = $row['name'];
- }
- return $ret;
- }
-
- function stand ($dbh,$blzkto,$currency)
- {
- if ($currency == "dm") {
- $query = "SELECT value_dm FROM account_dm WHERE blz_kto='$blzkto'";
- } elseif ($currency == "eur") {
- $query = "SELECT value_eur FROM account WHERE blz_kto='$blzkto'";
- } else {
- # Alert: unknown currency
- $query = '';
- }
- $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
-
- $sum = 0.0;
- for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
- $row = pg_fetch_array ($sth, $nr);
- $sum += $row[0];
- }
- return $sum;
- }
-?>
-</define-tag>
#include <infocon.style>
+<future>
<page func=InfoCon title="Kontoführung">
<blockquote>
<input type=hidden name=formtype value=admin>
<?
- $dbh = pg_pconnect ("<dbhost>", "<dbport>", "<dbname>")
- or die("Unable to connect to SQL server");
-
$query = "SELECT blz_kto,name,display,date_part('year',min(datum)) AS year_from,date_part('year', max(datum)) AS year_to " .
"FROM account_names JOIN account using(blz_kto) " .
"GROUP BY blz_kto,name,display ORDER BY name";
- $sth = pg_exec ($dbh, $query);
-
- for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
- $row = pg_fetch_array ($sth, $nr);
+ foreach ($db->fetchAssocList($query) as $row) {
$both = explode (":", $row['blz_kto']);
printf ("<br><input type=\"checkbox\" name=\"display_%s\" value=\"1\"%s> ".
"<input type=\"text\" name=\"name_%s\" value=\"%s\" size=\"30\"> ".
#include <infocon.style>
#include "account.inc"
-<account_overview>
-
<page func=InfoCon title="Kontoführung">
<blockquote>
<p><ul>
<?
- $accounts = account_names ($dbh, "", "eur");
+ $accounts = new AccountName(false);
- foreach ($accounts as $kto => $name) {
+ $account = new Accounting(false);
+ foreach ($accounts->getAccounts('eur') as $row) {
printf ("<li><a href=\"query.php?blzkto=%s\">%s</a> (Stand: %6.2f EUR)",
- urlencode($kto), $name, stand ($dbh, $kto, "eur"));
+ urlencode($row->blz_kto), $row->name, $account->sum($row->blz_kto));
}
- $accounts = account_names ($dbh, "", "dm");
-
- foreach ($accounts as $kto => $name) {
+ $account = new AccountingDM(false);
+ foreach ($accounts->getAccounts('dm') as $row) {
printf ("<li><a href=\"query.php?blzkto=%s&cur=dm\">%s</a> (Stand: %6.2f DM)",
- urlencode($kto), $name, stand ($dbh, $kto, "dm"));
+ urlencode($row->blz_kto), $row->name, $account->sum($row->blz_kto));
}
?>
</ul>
#include <infocon.style>
#include "account.inc"
+<future>
<page func=InfoCon title="Kontoführung">
<calendar_init 5>
$value = "value_eur";
$add = '';
$hidden = '';
- if ((strlen ($_GET[cur]) > 0) && ($_GET[cur] == "dm")) {
+ if ((strlen ($_GET['cur']) > 0) && ($_GET['cur'] == "dm")) {
$table = "account_dm";
$value = "value_dm";
$hidden = '<input type=hidden name=currency value="dm">';
$add = "¤cy=dm";
}
- if (!isset($_GET[blzkto]) && isset($_GET[id])) {
- pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");
+ if (!isset($_GET['blzkto']) && isset($_GET['id'])) {
+ if ($table == 'account')
+ $accounting = new Accounting($_GET['id']);
+ else
+ $accounting = new AccountingDM($_GET['id']);
+ $row = $accounting->fetch();
- $query = "SELECT $table.blz_kto,datum,category,descr,from_to,statement,$value,name "
- ."FROM $table JOIN account_names using(blz_kto) "
- ."WHERE $table.id = $_GET[id]";
- $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
-
- if (pg_NumRows ($sth) > 0) {
- $row = pg_fetch_array ($sth, 0);
- $date = explode (" ", $row['datum']);
+ if ($row) {
+ $date = explode (" ", $row->datum);
$date = explode ("-", $date[0]);
$date = sprintf ("%d.%d.%d", $date[2], $date[1], $date[0]);
- $blzkto = $row['blz_kto'];
+ $blzkto = $row->blz_kto;
$add .= '&blzkto='. $blzkto;
}
} else {
- $blzkto = $_GET[blzkto];
+ $accounting = new Accounting(false);
+ $blzkto = $_GET['blzkto'];
}
+ $account = new AccountName($blzkto);
?>
<style type="text/css">
}
</style>
-<h3 class=bar><?=$row['name']?></h3>
+<h3 class=bar><?=$account->fetch()->name;?></h3>
<form method=post action="update.php">
<input class=none type="hidden" name="id" value="<? echo $_GET[id]; ?>">
?>
<label for=statement>Auszug</label>
-<input id=statement name=statement size=15 maxlength=15 value="<?=$row['statement']?>" tabindex=1>
+<input id=statement name=statement size=15 maxlength=15 value="<?=$row->statement?>" tabindex=1>
<br class="none">
<label for=datum>Datum</label>
<select id=category name=category tabindex=3>
<option>
<?
- $query = "SELECT DISTINCT category FROM $table WHERE blz_kto = '$blzkto' ORDER BY category";
- $sth = pg_exec ($dbh, $query);
+ foreach ($accounting->distinctCategories($blzkto) as $cat)
+ printf ("<option value=\"%s\"%s>%s", $cat->category, $cat->category == $row->category?" selected":"", $cat->category);
- for ($nr=0; $nr <pg_NumRows ($sth); $nr++) {
- $cat = pg_fetch_array ($sth, $nr);
- printf ("<option value=\"%s\"%s>%s", $cat['category'], $cat['category'] == $row['category']?" selected":"", $cat['category']);
- }
?></select> <input id=category name=newcategory size=20 maxlength=50 value="" tabindex=4>
<br class="none">
<select id=from_to name=from_to tabindex=5>
<option>
<?
- $query = "SELECT DISTINCT from_to FROM $table ";
-
- if (!isset($_GET[id])) {
- $query .= "WHERE blz_kto = '$blzkto' AND datum >= now() - interval '2 years' ";
- }
+ foreach ($accounting->distinctFromTo($blzkto, isset($_GET['id']) ? false : "datum >= now() - interval '2 years'") as $name)
+ printf ("<option value=\"%s\"%s>%s", $name->from_to, $name->from_to == $row->from_to?" selected":"", $name->from_to);
- $query .= "ORDER BY from_to";
-
- $sth = pg_exec ($dbh, $query);
-
- for ($nr=0; $nr <pg_NumRows ($sth); $nr++) {
- $cat = pg_fetch_array ($sth, $nr);
- printf ("<option value=\"%s\"%s>%s", $cat['from_to'], $cat['from_to'] == $row['from_to']?" selected":"", $cat['from_to']);
- }
?></select> <input id=from_to name=newfrom_to size=30 maxlength=120 value="" tabindex=6>
<br class="none">
<label for=value>Betrag</label>
-<input id=value name=value size=40 maxlength=20 value="<? echo $row[$value]; ?>" tabindex=7>
+<input id=value name=value size=40 maxlength=20 value="<? echo $row->$value; ?>" tabindex=7>
<br class="none">
<label for=descr>Verwendungszweck</label>
-<textarea id=descr name=descr rows=5 cols=67 tabindex=8><? echo $row['descr']; ?></textarea>
+<textarea id=descr name=descr rows=5 cols=67 tabindex=8><? echo $row->descr; ?></textarea>
<p><center>
<? if (isset ($_GET[id])) { ?>
#include <infocon.style>
#include "account.inc"
-<account_overview>
-
<page func=InfoCon title="Kontoführung">
<blockquote>
<p><ul>
<?
- $accounts = account_names ($dbh, "AND display = 1", "eur");
+ $accounts = new AccountName(false);
+ $account = new Accounting(false);
- foreach ($accounts as $kto => $name) {
+ foreach ($accounts->getAccounts('eur', 1) as $row) {
printf ("<li><a href=\"query.php?blzkto=%s\">%s</a> (Stand: %6.2f EUR)",
- urlencode($kto), $name, stand ($dbh, $kto, "eur"));
+ urlencode($row->blz_kto), $row->name, $account->sum($row->blz_kto));
}
?>
</ul>
<th width=10%>Betrag</th>
</tr>
<?
- pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");
-
$table = "account";
$value = "value_eur";
$add = '';
- if ((strlen ($_POST[currency]) > 0) && ($_POST[currency] == "dm")) {
+ if ((strlen ($_POST['currency']) > 0) && ($_POST['currency'] == "dm")) {
$table = "account_dm";
$value = "value_dm";
$add = "&cur=dm";
}
- $where[] = "blz_kto = '$_POST[blzkto]'";
- if (strlen($_POST[year])) {
- $where[] = sprintf ("datum >= '%04d-01-01'", $_POST[year]);
+ $where[] = sprintf("blz_kto = '%s'", $_POST['blzkto']);
+ if (strlen($_POST['year'])) {
+ $where[] = sprintf ("datum >= '%04d-01-01'", $_POST['year']);
$year++;
- $where[] = sprintf ("datum < '%04d-01-01'", $_POST[year]+1);
+ $where[] = sprintf ("datum < '%04d-01-01'", $_POST['year']+1);
}
- if (strlen($_POST[deadline]) && strlen(trim($_POST[deadline]))) {
- $date = form_to_yyyymmdd ($_POST[deadline]);
+ if (strlen($_POST[deadline]) && strlen(trim($_POST['deadline']))) {
+ $date = form_to_yyyymmdd ($_POST['deadline']);
$where[] = sprintf ("datum < '%s'", $date);
}
- if (strlen($_POST[statement]) && strlen(trim($_POST[statement]))) {
- $where[] = "statement = '$_POST[statement]'";
+ if (strlen($_POST['statement']) && strlen(trim($_POST['statement']))) {
+ $where[] = sprintf("statement = '%s'", $_POST['statement']);
}
- if (strlen($_POST[category]) && strlen(trim($_POST[category]))) {
- $where[] = "category = '$_POST[category]'";
+ if (strlen($_POST['category']) && strlen(trim($_POST['category']))) {
+ $where[] = sprintf("category = '%s'", $_POST['category']);
}
- if (strlen($_POST[keyword]) && strlen(trim($_POST[keyword]))) {
- $where[] = "descr ~* '$_POST[keyword]'";
+ if (strlen($_POST['keyword']) && strlen(trim($_POST['keyword']))) {
+ $where[] = sprintf("descr ~* '%s'", $_POST['keyword']);
}
- if (strlen($_POST[from_to]) && strlen(trim($_POST[from_to]))) {
- $where[] = "from_to = '$_POST[from_to]'";
+ if (strlen($_POST['from_to']) && strlen(trim($_POST['from_to']))) {
+ $where[] = sprintf("from_to = '%s'", $_POST['from_to']);
}
- if ($_POST[input] && !$_POST[output]) {
+ if ($_POST['input'] && !$_POST['output']) {
$where[] = "$value > 0.0";
- } elseif ($_POST[output] && !$_POST[input]) {
+ } elseif ($_POST['output'] && !$_POST['input']) {
$where[] = "$value < 0.0";
}
$query = "SELECT datum,id,category,descr,$value FROM $table WHERE "
. implode ($where, " AND ")
. " ORDER BY datum,id";
- $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
$sum = 0.0;
$sum_in = 0.0;
$sum_out = 0.0;
$color = 0;
- for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
- $row = pg_fetch_array ($sth, $nr);
-
+ foreach ($db->fetchAssocList($query) as $row) {
$descr = explode ("
", $row['descr']);
$date = explode (" ", $row['datum']);
#include <infocon.style>
#include "account.inc"
+<future>
<page func=InfoCon title="Kontoführung">
<calendar_init -5>
<select id=year name=year>
<option>
<?
- $query = "SELECT DISTINCT substr(datum::text,0,5) AS year FROM $table ".
- "WHERE blz_kto = '$_GET[blzkto]' ".
- "ORDER BY year DESC";
- $sth = pg_exec ($dbh, $query);
-
- for ($nr=0; $nr <pg_NumRows ($sth); $nr++) {
- $row = pg_fetch_array ($sth, $nr);
- printf ("<option value=\"%s\">%s", $row['year'], $row['year']);
- }
+ if ($table == 'account')
+ $accounting = new Accounting(false);
+ else
+ $accounting = new AccountingDM(false);
+
+ foreach ($accounting->distinctYears($_GET['blzkto']) as $row)
+ printf ("<option value=\"%s\">%s", $row->year, $row->year);
?></select>
<br class="none">
<select id=statement name=statement>
<option>
<?
- $query = "SELECT DISTINCT statement FROM $table WHERE blz_kto = '$_GET[blzkto]' ORDER BY statement DESC";
- $sth = pg_exec ($dbh, $query);
-
- for ($nr=0; $nr <pg_NumRows ($sth); $nr++) {
- $cat = pg_fetch_array ($sth, $nr);
- printf ("<option value=\"%s\">%s", $cat['statement'], $cat['statement']);
- }
+ foreach ($accounting->distinctStatements($_GET['blzkto']) as $row)
+ printf ("<option value=\"%s\">%s", $row->statement, $row->statement);
?></select>
<? } ?>
<select id=category name=category>
<option>
<?
- $query = "SELECT DISTINCT category FROM $table WHERE blz_kto = '$_GET[blzkto]' ORDER BY category";
- $sth = pg_exec ($dbh, $query);
-
- for ($nr=0; $nr <pg_NumRows ($sth); $nr++) {
- $cat = pg_fetch_array ($sth, $nr);
- printf ("<option value=\"%s\">%s", $cat['category'], $cat['category']);
- }
+ foreach ($accounting->distinctCategories($_GET['blzkto']) as $row)
+ printf ("<option value=\"%s\">%s", $row->category, $row->category);
?></select>
<br class="none">
<select id=from_to name=from_to>
<option>
<?
- $query = "SELECT DISTINCT from_to FROM $table WHERE blz_kto = '$_GET[blzkto]' ORDER BY from_to";
- $sth = pg_exec ($dbh, $query);
-
- for ($nr=0; $nr <pg_NumRows ($sth); $nr++) {
- $cat = pg_fetch_array ($sth, $nr);
- printf ("<option value=\"%s\">%s", $cat['from_to'], $cat['from_to']);
- }
+ foreach ($accounting->distinctFromTo($_GET['blzkto']) as $row)
+ printf ("<option value=\"%s\">%s", $row->from_to, $row->from_to);
?></select>
<br class="none">
<string-eq "<get-var WML_SRC_BASENAME>" "update" />
<string-eq "<get-var WML_SRC_BASENAME>" "edit" />>>
<?
-function getblzkto($oid)
-{
- $dbh = pg_pconnect ("<dbconnstring>");
-
- if (!$dbh) return '';
-
- $sth = pg_exec ($dbh, 'SELECT blz_kto FROM account WHERE oid = '.$oid);
-
- if (!$sth) return '';
-
- if (pg_NumRows ($sth) != 1) return '';
-
- $row = pg_fetch_array ($sth, 0);
-
- return $row['blz_kto'];
-}
-
if (isset($_REQUEST['blzkto']))
printf(' <a href="edit.php?blzkto=%s">Neuer Eintrag</a><br>', $_REQUEST['blzkto']);
- elseif (isset($_GET['oid'])) {
- $kto = getblzkto($_GET['oid']);
- if (strlen($kto))
- printf(' <a href="edit.php?blzkto=%s">Neuer Eintrag</a><br>', $kto);
- }
?>
</when>
#include <phptools.inc>
#include "account.inc"
+<future>
<page func=InfoCon title="Kontoführung">
<?
- if ($_POST[formtype] == "admin" || $_POST[formtype] == "newaccount")
+ if ($_POST['formtype'] == "admin" || $_POST['formtype'] == "newaccount")
$ktoname = "Administration";
?>
<form_to_yyyymmdd>
<?
- if ($_POST[formtype] == "admin") {
+ if ($_POST['formtype'] == "admin") {
$query = "SELECT blz_kto,name,display FROM account_names ORDER BY name";
- $sth = pg_exec ($dbh, $query);
-
- for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
- $row = pg_fetch_array ($sth, $nr);
-
+ foreach ($db->fetchAssocList($query) as $row) {
if ($_POST['name_' . $row['blz_kto']] != $row['name'] ||
($_POST['display_' . $row['blz_kto']] ? 1 : 0) != $row['display']) {
$query = sprintf ("UPDATE account_names SET name='%s',display=%d,sys_user='%s',sys_edit=now() WHERE blz_kto = '%s'",
$_SERVER['REMOTE_USER'],
$row['blz_kto']);
- pg_exec ($dbh, $query);
+ $db->execute($query);
$output = "Kontoinformationen aktualisiert.";
}
}
- } elseif ($_POST[formtype] == "newaccount") {
+ } elseif ($_POST['formtype'] == "newaccount") {
if (isset($_POST['blz']) && isset($_POST['kto']) && isset($_POST['name'])) {
$query = sprintf ("INSERT INTO account_names (blz_kto,name,display,sys_user,sys_edit) ".
"VALUES ('%s:%s','%s',1,'%s',now())",
$_POST['kto'],
$_POST['name'],
$_SERVER['REMOTE_USER']);
- pg_exec ($dbh, $query);
+ $db->execute($query);
$query = sprintf ("INSERT INTO account (blz_kto,statement,datum,from_to,descr,category,value_eur,sys_user,sys_edit) ".
"VALUES ('%s:%s','%s','%s','Account Administration','Initial Zero Statement','Miscellaneous',0.0,'%s',now())",
date("Y") . "/00",
date("Y-m-j"),
$_SERVER['REMOTE_USER']);
- pg_exec ($dbh, $query);
+ $db->execute($query);
$output = "Neues Konto erstellt.";
} else {
} else {
$table = "account";
$field = "value_eur";
- if ((strlen ($_POST[currency]) > 0) && ($_POST[currency] == "dm")) {
+ if ((strlen ($_POST['currency']) > 0) && ($_POST['currency'] == "dm")) {
$table = "account_dm";
$field = "value_dm";
}
- if (strlen ($_POST[category]) == 0 && strlen ($_POST[newcategory]) > 0) {
- $category = $_POST[newcategory];
+ if (strlen ($_POST['category']) == 0 && strlen ($_POST['newcategory']) > 0) {
+ $category = $_POST['newcategory'];
} else {
- $category = $_POST[category];
+ $category = $_POST['category'];
}
- if (strlen ($_POST[from_to]) == 0 && strlen ($_POST[newfrom_to]) > 0) {
- $from_to = $_POST[newfrom_to];
+ if (strlen ($_POST['from_to']) == 0 && strlen ($_POST['newfrom_to']) > 0) {
+ $from_to = $_POST['newfrom_to'];
} else {
- $from_to = $_POST[from_to];
+ $from_to = $_POST['from_to'];
}
- $statement = sql_prepare ($_POST[statement]);
- $category = sql_prepare ($category);
- $descr = sql_prepare (trim($_POST[descr]));
- $from_to = sql_prepare ($from_to);
+ $statement = $db->quote($_POST['statement']);
+ $category = $db->quote($category);
+ $descr = $db->quote(trim($_POST['descr']));
+ $from_to = $db->quote($from_to);
- $date = form_to_yyyymmdd ($_POST[datum]);
+ $date = form_to_yyyymmdd ($_POST['datum']);
- $value = ereg_replace (",",".", $_POST[value]);
+ $value = str_replace (",",".", $_POST['value']);
- if (isset($_POST[id]) && $_POST[id] > 0 && strlen ($_POST[delete])) {
- $query = sprintf("DELETE FROM %s WHERE id = %d", $table, $_POST[id]);
+ if (isset($_POST['id']) && $_POST['id'] > 0 && strlen ($_POST['delete'])) {
+ $query = sprintf("DELETE FROM %s WHERE id = %d", $table, $_POST['id']);
$output = "Posten gelöscht.";
- } elseif (isset($_POST[id]) && $_POST[id] > 0) {
- $query = sprintf("UPDATE %s SET datum='%s',statement='%s',from_to='%s',descr='%s',category='%s',%s=%s,sys_user='%s',sys_edit=now() " .
+ } elseif (isset($_POST['id']) && $_POST['id'] > 0) {
+ $query = sprintf("UPDATE %s SET datum='%s',statement=%s,from_to=%s,descr=%s,category=%s,%s=%s,sys_user='%s',sys_edit=now() " .
"WHERE id = %d",
$table,
$date,
$output = "Posten aktualisiert.";
} else {
$query = sprintf("INSERT INTO %s (blz_kto,datum,statement,from_to,descr,category,%s,sys_user,sys_edit) ".
- "VALUES ('%s','%s','%s','%s','%s','%s',%s,'%s',now())",
+ "VALUES ('%s','%s',%s,%s,%s,%s,%s,'%s',now())",
$table, $field,
$_POST['blzkto'],
$date,
$output = "Neuen Posten aufgenommen.";
$output .= sprintf('</p><p><a href="edit.php?blzkto=%s">Weiteren Posten aufnehmen</a>', $_POST['blzkto']);
}
- $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
+ $db->execute($query) or die("Datenbank-Abfrage!");
}
echo ("<p>".$output."</p>");