b314aa79bebc2c6b655d342f8b82fc467a7dd42a
[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(
21         'SELECT SUM(value) AS paid_fees FROM `earnings` WHERE member_id=? AND type=? AND `status`=\'paid\'',
22         [$member_id, $old_fees ? 'old_fee' : 'fee']
23     );
24
25     $paid_fees = $row['paid_fees'];
26
27     if ($old_fees) {
28         return $paid_fees;
29     }
30
31     /** @var DateTime $direct_debit */
32     $direct_debit = Config::getInstance()->get('direct_debit')['date']
33     $invoiced_fees = fees_sum_for_member($member_id, $direct_debit->getTimestamp() - 86400);
34     $paid_invoiced_fees = finance_get_paid_fees_for_member($member_id, true);
35     if (bccomp($paid_invoiced_fees, $invoiced_fees) == 1) {
36         $paid_fees = bcadd($paid_fees, bcsub($paid_invoiced_fees, $invoiced_fees));
37     }
38
39
40     $refunded = db_select_single(
41         "SELECT SUM(value) AS refunded_fees FROM `expenses` WHERE member_id=? AND type IN('member_fee') AND `status`='paid'",
42         [$member_id]
43     );
44     return bcsub($paid_fees, $refunded['refunded_fees']);
45 }
46
47 function finance_list_paid_fees_for_member($member_id)
48 {
49     return db_select_multi("
50                 (SELECT id, date, type, status, account, value, member_id, description, created_at, modified_at FROM `earnings` WHERE member_id=? AND type IN('old_fee', 'fee'))
51                 UNION
52                 (SELECT id, date, type, status, account, value*-1, member_id, description, created_at, modified_at FROM `expenses` WHERE member_id=? AND type IN('member_fee'))
53                 ORDER BY `date` DESC",
54         [$member_id, $member_id]
55     );
56 }
57
58 function action_finances()
59 {/*{{{*/
60     ?>
61     <h2>Finanzen</h2>
62     <?php
63     action_create_expense();
64     action_create_earning();
65     echo '<br style="clear: left;"/>';
66     render_open_payments();
67     render_finished_payments();
68     echo '<a href="' . link_to('export') . '">.xls runterladen</a>';
69 }/*}}}*/