Formatting
[memberdb.git] / include / payments.php
index 3ca6c2c..ee7b683 100644 (file)
 <?php
 
 // NOTE: combination of earnings and expenses
-
 $ACCOUNT_TYPES = array(
     'bank' => 'Konto',
     'cash' => 'Kasse'
 );
 
-/* DB functions {{{ */
-function db_get_payments($offset, $count, $where) {
+// DB functions
+function db_get_payments($offset, $count, $where)
+{
     $limit = '';
-    if (isset($offset) && isset($count)) $limit = sprintf(' LIMIT %1$d,%2$d', (int)$offset, (int)$count);
-    elseif (isset($offset)) $limit = sprintf(' LIMIT %1$d', (int)$count);
-    $sql = 'SELECT t.*, %4$s.nickname AS nickname FROM ((SELECT \'earning\' as ptype, %1$s.* FROM %1$s %3$s) UNION (SELECT \'expense\', %2$s.* FROM %2$s %3$s)) AS t LEFT JOIN %4$s ON t.member_id=%4$s.id ORDER BY `date` DESC, t.id DESC' . $limit;
-    return db_select_multi(sprintf($sql, DB_TABLE_EARNINGS, DB_TABLE_EXPENSES, $where, DB_TABLE_MEMBERS));
+    if (isset($offset) && isset($count)) {
+        $limit = sprintf(' LIMIT %d,%d', (int)$offset, (int)$count);
+    } elseif (isset($offset)) {
+        $limit = sprintf(' LIMIT %d', (int)$count);
+    }
+    $sql = "
+        SELECT t.*, members.nickname AS nickname
+        FROM (
+            (SELECT 'earning' AS ptype, `earnings`.* FROM `earnings` `earnings`)
+            UNION
+            (SELECT 'expense', expenses.* FROM `expenses` `earnings`)
+        ) AS t
+        LEFT JOIN `members` ON t.member_id=members.id
+        ORDER BY `date` DESC, t.id DESC
+    " . $limit;
+    return db_select_multi(sprintf($sql, $where));
 }
 
-function db_get_finished_payments($offset, $count) {
-    $where = ' WHERE status=\'paid\'';
+function db_get_finished_payments($offset, $count)
+{
+    $where = " WHERE status='paid'";
     $payments = db_get_payments($offset, $count, $where);
 
-    $bank  = 0;
-    $cash  = 0;
+    $bank = 0;
+    $cash = 0;
     $total = 0;
     if ($count != 0) { // XXX TODO hä? - An dieser Stelle gäbe es sinnigere Bedinungen
-        $limit = sprintf(' LIMIT %d,18446744073709551615', (int)($count + $offset));
-        $sql = 'SELECT SUM(IF(`account`=\'bank\', `value`, 0)) as `bank`, SUM(IF(`account`=\'cash\', `value`, 0)) as `cash`, SUM(`value`) AS `total` 
+        $limit = sprintf(' LIMIT %d,18446744073709551615',
+            (int)($count + $offset)); // SELECT mit Offset aber ohne Limit
+        $sql = "SELECT SUM(IF(`account`='bank', `value`, 0)) AS `bank`, SUM(IF(`account`='cash', `value`, 0)) AS `cash`, SUM(`value`) AS `total` 
                 FROM (
                     SELECT `value`, `account` 
-                    FROM (( SELECT `value`,                 `id`, `date`, `account` FROM %1$s %3$s) 
-                    UNION ( SELECT `value` * -1 AS `value`, `id`, `date`, `account` FROM %2$s %3$s)
+                    FROM (( SELECT `value`,                 `id`, `date`, `account` FROM `earnings` %1\$s) 
+                    UNION ( SELECT `value` * -1 AS `value`, `id`, `date`, `account` FROM `expenses` %1\$s)
                     ) AS t
-                    ORDER BY `date` DESC, t.id DESC' . $limit . '
+                    ORDER BY `date` DESC, t.id DESC" . $limit . '
                 ) AS u';
-        $row = db_select_single(sprintf($sql, DB_TABLE_EARNINGS, DB_TABLE_EXPENSES, $where));
-        $bank  = $row['bank'];
-        $cash  = $row['cash'];
+        $row = db_select_single(sprintf($sql, $where));
+        $bank = $row['bank'];
+        $cash = $row['cash'];
         $total = $row['total'];
     }
     for ($i = count($payments) - 1; $i >= 0; $i--) {
         $value = $payments[$i]['value'];
-        if ($payments[$i]['ptype'] == 'expense') $value = bcmul($value, '-1');
+        if ($payments[$i]['ptype'] == 'expense') {
+            $value = bcmul($value, '-1');
+        }
         switch ($payments[$i]['account']) {
-            case 'bank': $bank = bcadd($bank, $value); break;
-            case 'cash': $cash = bcadd($cash, $value); break;
-            default: break; // TODO XXX die?
+            case 'bank':
+                $bank = bcadd($bank, $value);
+                break;
+            case 'cash':
+                $cash = bcadd($cash, $value);
+                break;
+            default:
+                break; // TODO XXX die?
         }
         $total = bcadd($total, $value);
-        $payments[$i]['bank']   = $bank;
-        $payments[$i]['cash']   = $cash;
-        $payments[$i]['total']  = $total;
+        $payments[$i]['bank'] = $bank;
+        $payments[$i]['cash'] = $cash;
+        $payments[$i]['total'] = $total;
     }
     return $payments;
 }
 
-function db_count_finished_payments() {
-    $where = ' WHERE status=\'paid\'';
-    $sql = 'SELECT count(*) as `count` FROM ((SELECT * FROM %1$s %3$s) UNION (SELECT * FROM %2$s %3$s)) AS t';
-    $row = db_select_single(sprintf($sql, DB_TABLE_EARNINGS, DB_TABLE_EXPENSES, $where));
+function db_count_finished_payments()
+{
+    $where = " WHERE status='paid'";
+    $sql = 'SELECT count(*) AS `count` FROM ((SELECT * FROM `earnings` %1$s) UNION (SELECT * FROM `expenses` %1$s)) AS t';
+    $row = db_select_single(sprintf($sql, $where));
     return $row['count'];
 }
 
-function db_get_open_payments($offset, $count) {
-    return db_get_payments($offset, $count, ' WHERE status=\'open\'');
+function db_get_open_payments($offset, $count)
+{
+    return db_get_payments($offset, $count, " WHERE status='open'");
 }
 
-/* }}} */
-
-function render_finished_payments($count = 25) {/*{{{*/
-    global $EXPENSE_TYPES, $EARNING_TYPES, $ACCOUNT_TYPES;
+function render_finished_payments($count = 100)
+{
+    global $EXPENSE_TYPES, $EARNING_TYPES;
     $page = 1;
-    if (!empty($_REQUEST['page'])) $page = max(1, (int)$_REQUEST['page']);
+    if (!empty($_REQUEST['page'])) {
+        $page = max(1, (int)$_REQUEST['page']);
+    }
     $payments = db_get_finished_payments(($page - 1) * $count, $count);
     $max = db_count_finished_payments();
     ?>
     <h3 id="finished">Abgeschlossene Zahlungen</h3>
     <?php if (isset($payments)) : ?>
-        <table>
-            <tr>
-                <th>Id</th>
-                <th>Datum</th>
-                <th>Typ</th>
-                <th>Mitglied</th>
-                <th>Betrag</th>
-                <th>Beschreibung</th>
-                <th>Konto</th>
-                <th>Kasse</th>
-                <th>Gesamt</th>
-            </tr>
+    <table>
+        <tr>
+            <th>Id</th>
+            <th>Datum</th>
+            <th>Typ</th>
+            <th>Mitglied</th>
+            <th>Betrag</th>
+            <th>Beschreibung</th>
+            <th>Konto</th>
+            <th>Kasse</th>
+            <th>Gesamt</th>
+        </tr>
         <?php foreach ($payments as $payment) : ?>
-            <tr class="<?=$payment['ptype']?>">
-                <td style="text-align: right;"><a href="<?=html_escape(link_to('change_' . $payment['ptype'], array('id' => $payment['id'])))?>"><?=html_escape($payment['id'])?></a></td>
-                <td><?=html_escape(format_date(db_date2unixtime($payment['date'])))?></td>
-                <td><?=html_escape($payment['ptype'] == 'earning'? $EARNING_TYPES[$payment['type']] : $EXPENSE_TYPES[$payment['type']])?></td>
-                <td><?php if ($payment['member_id'] != 0) : ?><a href="<?=html_escape(link_to('view_member', array('id'=> $payment['member_id'])))?>"><?=html_escape($payment['nickname'])?><?php endif ?></a></td>
-                <td style="text-align: right;"><?=html_escape(format_money(($payment['ptype'] == 'earning' ? 1 : -1)  * $payment['value']))?></td>
-                <td><?=html_escape($payment['description'])?></td>
-                <td style="text-align: right;<?php if ($payment['account'] == 'bank') : ?> text-decoration:underline;<?php endif ?>"><?=html_escape(format_money($payment['bank']))?></td>
-                <td style="text-align: right;<?php if ($payment['account'] == 'cash') : ?> text-decoration:underline;<?php endif ?>"><?=html_escape(format_money($payment['cash']))?></td>
-                <td style="text-align: right;"><?=html_escape(format_money($payment['total']))?></td>
+            <tr class="<?php echo $payment['ptype'] ?>">
+                <td style="text-align: right;"><a href="<?php echo html_escape(link_to('change_' . $payment['ptype'],
+                        array('id' => $payment['id']))) ?>"><?php echo html_escape($payment['id']) ?></a></td>
+                <td><?php echo html_escape(format_date(db_date2unixtime($payment['date']))) ?></td>
+                <td><?php echo html_escape($payment['ptype'] == 'earning' ? $EARNING_TYPES[$payment['type']] : $EXPENSE_TYPES[$payment['type']]) ?></td>
+                <td><?php if ($payment['member_id'] != 0) : ?><a href="<?php echo html_escape(link_to('view_member',
+                        array('id' => $payment['member_id']))) ?>"><?php echo html_escape($payment['nickname']) ?><?php endif ?></a>
+                </td>
+                <td style="text-align: right;"><?php echo html_escape(format_money(($payment['ptype'] == 'earning' ? 1 : -1) * $payment['value'])) ?></td>
+                <td><?php echo html_escape($payment['description']) ?></td>
+                <td style="text-align: right;<?php if ($payment['account'] == 'bank') : ?> text-decoration:underline;<?php endif ?>"><?php echo html_escape(format_money($payment['bank'])) ?></td>
+                <td style="text-align: right;<?php if ($payment['account'] == 'cash') : ?> text-decoration:underline;<?php endif ?>"><?php echo html_escape(format_money($payment['cash'])) ?></td>
+                <td style="text-align: right;"><?php echo html_escape(format_money($payment['total'])) ?></td>
             </tr>
         <?php endforeach ?>
-        </table>
-        <?php paginate(link_to('finances') . '&page=%d#finished', $page, ceil($max / $count)); ?>
-    <?php else : ?>
-        <p>Bisher gibt's noch keine abgeschlossenen Zahlungen.</p>
-    <?php endif ?>
+    </table>
+    <?php paginate(link_to('finances') . '&page=%d#finished', $page, ceil($max / $count)); ?>
+<?php else : ?>
+    <p>Bisher gibt's noch keine abgeschlossenen Zahlungen.</p>
+<?php endif ?>
     <?php
+}
 
-}/*}}}*/
-
-function render_open_payments($offset = 0, $count = 25) {/*{{{*/
+function render_open_payments($offset = 0, $count = 25)
+{
     global $EXPENSE_TYPES, $EARNING_TYPES, $ACCOUNT_TYPES;
-    global $EXPENSE_TYPES, $EARNING_TYPES;
     $payments = db_get_open_payments($offset, $count);
     ?>
     <h3>Offene Zahlungen</h3>
     <?php if (isset($payments)) : ?>
-        <table>
-            <tr>
-                <th>Id</th>
-                <th>Datum</th>
-                <th>Typ</th>
-                <th>Mitglied</th>
-                <th>Quelle/Ziel</th>
-                <th>Betrag</th>
-                <th>Beschreibung</th>
-            </tr>
+    <table>
+        <tr>
+            <th>Id</th>
+            <th>Datum</th>
+            <th>Typ</th>
+            <th>Mitglied</th>
+            <th>Quelle/Ziel</th>
+            <th>Betrag</th>
+            <th>Beschreibung</th>
+        </tr>
         <?php foreach ($payments as $payment) : ?>
-            <tr class="<?=$payment['ptype']?>">
-                <td style="text-align: right;"><a href="<?=html_escape(link_to('change_' . $payment['ptype'], array('id' => $payment['id'])))?>"><?=html_escape($payment['id'])?></a></td>
-                <td><?=html_escape(format_date(db_date2unixtime($payment['date'])))?></td>
-                <td><?=html_escape($payment['ptype'] == 'earning'? $EARNING_TYPES[$payment['type']] : $EXPENSE_TYPES[$payment['type']])?></td>
-                <td><?php if ($payment['member_id'] != 0) : ?><a href="<?=html_escape(link_to('view_member', array('id'=> $payment['member_id'])))?>"><?=html_escape($payment['nickname'])?><?php endif ?></a></td>
-                <td><?=html_escape($ACCOUNT_TYPES[$payment['account']])?></td>
-                <td style="text-align: right;"><?=html_escape(format_money(($payment['ptype'] == 'earning'? 1 : -1)  * $payment['value']))?></td>
-                <td><?=html_escape($payment['description'])?></td>
+            <tr class="<?php echo $payment['ptype'] ?>">
+                <td style="text-align: right;"><a href="<?php echo html_escape(link_to('change_' . $payment['ptype'],
+                        array('id' => $payment['id']))) ?>"><?php echo html_escape($payment['id']) ?></a></td>
+                <td><?php echo html_escape(format_date(db_date2unixtime($payment['date']))) ?></td>
+                <td><?php echo html_escape($payment['ptype'] == 'earning' ? $EARNING_TYPES[$payment['type']] : $EXPENSE_TYPES[$payment['type']]) ?></td>
+                <td><?php if ($payment['member_id'] != 0) : ?><a href="<?php echo html_escape(link_to('view_member',
+                        array('id' => $payment['member_id']))) ?>"><?php echo html_escape($payment['nickname']) ?><?php endif ?></a>
+                </td>
+                <td><?php echo html_escape($ACCOUNT_TYPES[$payment['account']]) ?></td>
+                <td style="text-align: right;"><?php echo html_escape(format_money(($payment['ptype'] == 'earning' ? 1 : -1) * $payment['value'])) ?></td>
+                <td><?php echo html_escape($payment['description']) ?></td>
             </tr>
         <?php endforeach ?>
-        </table>
-    <?php else : ?>
-        <p>Bisher gibt's noch keine abgeschlossenen Zahlungen.</p>
-    <?php endif ?>
+    </table>
+<?php else : ?>
+    <p>Bisher gibt's noch keine abgeschlossenen Zahlungen.</p>
+<?php endif ?>
     <?php
-
-}/*}}}*/
-
+}