Fixed sql stuff...
[memberdb.git] / include / finances.php
1 <?php
2
3
4 use MemberDB\Config\Config;
5
6 function finance_get_total_paid_fees()
7 {
8     $paid = db_select_single('SELECT SUM(value) AS paid_fees FROM `earnings` WHERE type IN(\'old_fee\', \'fee\') AND `status`=\'paid\'');
9     $refunded = db_select_single('SELECT SUM(value) AS refunded_fees FROM `expenses` WHERE type IN(\'member_fee\') AND `status`=\'paid\'');
10
11     return bcsub($paid['paid_fees'], $refunded['refunded_fees']);
12 }
13
14
15 function finance_get_paid_fees_for_member($member_id, $old_fees = false)
16 {
17     // $old_fees: Beiträge die nicht durch Lastschrift eingezogen werden.
18     //            Falls zu viel bezahlt, müssen diese auf die per Lastschrift
19     //            eingezogenen Beiträge angerechnet werden.
20     $row = db_select_single(sprintf(
21         'SELECT SUM(value) AS paid_fees FROM `earnings` WHERE member_id=\'%d\' AND type=\'%s\' AND `status`=\'paid\'',
22         db_escape($member_id),
23         $old_fees ? 'old_fee' : 'fee'
24     ));
25
26     $paid_fees = $row['paid_fees'];
27
28     if ($old_fees) {
29         return $paid_fees;
30     }
31
32     /** @var DateTime $direct_debit */
33     $direct_debit = Config::getInstance()->get('direct_debit')['date']
34     $invoiced_fees = fees_sum_for_member($member_id, $direct_debit->getTimestamp() - 86400);
35     $paid_invoiced_fees = finance_get_paid_fees_for_member($member_id, true);
36     if (bccomp($paid_invoiced_fees, $invoiced_fees) == 1) {
37         $paid_fees = bcadd($paid_fees, bcsub($paid_invoiced_fees, $invoiced_fees));
38     }
39
40
41     $refunded = db_select_single(sprintf(
42         'SELECT SUM(value) AS refunded_fees FROM `expenses` WHERE member_id=\'%d\' AND type IN(\'member_fee\') AND `status`=\'paid\'',
43         db_escape($member_id)
44     ));
45     return bcsub($paid_fees, $refunded['refunded_fees']);
46 }
47
48 function finance_list_paid_fees_for_member($member_id)
49 {
50     return db_select_multi(sprintf('
51                 (SELECT id, date, type, status, account, value, member_id, description, created_at, modified_at FROM `earnings` WHERE member_id=\'%1$d\' AND type IN(\'old_fee\', \'fee\'))
52                 UNION
53                 (SELECT id, date, type, status, account, value*-1, member_id, description, created_at, modified_at FROM `expenses` WHERE member_id=\'%1$d\' AND type IN(\'member_fee\'))
54                 ORDER BY `date` DESC',
55         db_escape($member_id)
56     ));
57 }
58
59 function action_finances()
60 {/*{{{*/
61     ?>
62     <h2>Finanzen</h2>
63     <?php
64     action_create_expense();
65     action_create_earning();
66     echo '<br style="clear: left;"/>';
67     render_open_payments();
68     render_finished_payments();
69     echo '<a href="' . link_to('export') . '">.xls runterladen</a>';
70 }/*}}}*/