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