Added Db class
[memberdb.git] / include / fees.php
1 <?php
2
3
4 /* HELPER functions FOR FEES {{{ */
5
6 // build an empty structure for holding monthly information
7 use MemberDB\Config\Config;
8
9 function _fees_build_month_array($start_date, $end_date, $preset = array())
10 {
11     $start_info = getdate($start_date);
12     $end_info = getdate($end_date);
13
14     if ($start_info['year'] > $end_info['year']) {
15         return $preset;
16     }
17     if ($start_info['year'] == $end_info['year'] && $start_info['mon'] > $end_info['mon']) {
18         return $preset;
19     }
20
21     $ret = $preset;
22     for ($year = $start_info['year']; $year <= $end_info['year']; $year++) {
23         if (!isset($ret[$year])) {
24             $ret[$year] = array();
25         }
26         for (
27             $month = (($year == $start_info['year']) ? $start_info['mon'] : 1);
28             $month <= (($year == $end_info['year']) ? $end_info['mon'] : 12);
29             $month++
30         ) {
31             if (isset($ret[$year][$month])) {
32                 continue;
33             }
34             $ret[$year][$month] = array(
35                 'is_member'        => null,
36                 'member_type'      => null,
37                 'fee'              => null,
38                 'payment_interval' => null
39             );
40         }
41     }
42     return $ret;
43 }
44
45 function _fees_apply_event_information(&$info, $events)
46 {
47     $config = Config::getInstance();
48     if (!empty($events)) {
49
50         foreach ($events as $event) {
51
52             $timestamp = db_date2unixtime($event['event_date']);
53             if ($timestamp < $config->get('founding_date')) {
54                 $timestamp = $config->get('founding_date');
55             }
56             $date_info = getdate($timestamp);
57
58             if (!isset($info[$date_info['year']])) {
59                 continue;
60             }
61             if (!isset($info[$date_info['year']][$date_info['mon']])) {
62                 continue;
63             }
64
65             if ($event['fee'] !== null) {
66                 $info[$date_info['year']][$date_info['mon']]['fee'] = $event['fee'];
67             }
68             if ($event['member_type'] !== null) {
69                 $info[$date_info['year']][$date_info['mon']]['member_type'] = $event['member_type'];
70             }
71             if ($event['event_type'] !== 'changed') {
72                 $info[$date_info['year']][$date_info['mon']]['is_member'] = ($event['event_type'] == 'joined' ? 1 : 0);
73             }
74             if ($event['payment_interval'] !== null) {
75                 $info[$date_info['year']][$date_info['mon']]['payment_interval'] = $event['payment_interval'];
76             }
77         }
78     }
79
80     $fee = 0;
81     $member_type = null;
82     $is_member = false;
83     $payment_interval = null;
84
85     foreach (array_keys($info) as $year) {
86         foreach (array_keys($info[$year]) as $month) {
87             if (!isset($info[$year][$month]['fee'])) {
88                 $info[$year][$month]['fee'] = $fee;
89             } else {
90                 $fee = $info[$year][$month]['fee'];
91             }
92
93             if (!isset($info[$year][$month]['member_type'])) {
94                 $info[$year][$month]['member_type'] = $member_type;
95             } else {
96                 $member_type = $info[$year][$month]['member_type'];
97             }
98
99             if (!isset($info[$year][$month]['is_member'])) {
100                 $info[$year][$month]['is_member'] = $is_member;
101             } else {
102                 $is_member = $info[$year][$month]['is_member'];
103             }
104
105             if (!isset($info[$year][$month]['payment_interval'])) {
106                 $info[$year][$month]['payment_interval'] = $payment_interval;
107             } else {
108                 $payment_interval = $info[$year][$month]['payment_interval'];
109             }
110         }
111     }
112     return;
113 }
114
115 function fees_get_list_for_member($member_id, $end_date)
116 {
117
118     static $cache = array();
119     $config = Config::getInstance();
120
121     $end_date = mktime(0, 0, 0, date('m', $end_date) + 1, 0, date('Y', $end_date)); // last day of given month
122
123     if (isset($cache[$member_id][$end_date])) {
124         return $cache[$member_id][$end_date];
125     }
126     if (isset($cache[$member_id])) {
127         foreach (array_reverse(array_keys($cache[$member_id])) as $cache_date) {
128             if ($cache_date <= $end_date) {
129                 $ret = _fees_build_month_array($cache_date /* XXX einen Monat später wäre an dieser Stelle richtiger*/,
130                     $end_date, $cache[$member_id][$cache_date]);
131                 _fees_apply_event_information($ret, db_get_events_for_member($member_id, $cache_date, $end_date));
132                 $cache[$member_id][$end_date] = $ret;
133                 return $ret;
134             }
135         }
136     }
137
138     $ret = _fees_build_month_array($config->get('founding_date'), $end_date);
139     if (empty($ret)) {
140         return;
141     }
142
143     _fees_apply_event_information($ret, db_get_events_for_member($member_id));
144
145     $cache[$member_id][$end_date] = $ret;
146     return $ret;
147 }
148
149 function fees_sum_for_member($member_id, $end_date)
150 {
151     $membership_info = fees_get_list_for_member($member_id, $end_date);
152
153     $total = '0';
154
155     foreach ($membership_info as $year => $months) {
156         foreach ($months as $month => $info) {
157             if ($info['is_member']) {
158                 $total = bcadd($total, $info['fee']);
159             }
160         }
161     }
162     return $total;
163 }
164
165 function fees_for_member_at_date($member_id, $end_date)
166 {
167     $membership_info = fees_get_list_for_member($member_id, $end_date);
168
169     $this_year = array_pop($membership_info);
170     $this_month = array_pop($this_year);
171     if ($this_month['is_member']) {
172         return $this_month['fee'];
173     }
174     return null;
175 }
176
177 function fees_info_for_member($member_id, $end_date)
178 {
179     $membership_info = fees_get_list_for_member($member_id, $end_date);
180
181     $this_year = array_pop($membership_info);
182     return array_pop($this_year);
183 }
184
185 function fees_sum_by_month($end_date)
186 {
187     $members = db_get_members();
188     $fees = array();
189     if (empty($members)) {
190         return array();
191     }
192     foreach ($members as $member) {
193         $membership_info = fees_get_list_for_member($member['id'], $end_date);
194         foreach ($membership_info as $year => $months) {
195             foreach ($months as $month => $info) {
196                 if (!isset($fees[$year][$month])) {
197                     $fees[$year][$month] = '0';
198                 }
199                 if ($info['is_member']) {
200                     $fees[$year][$month] = bcadd($fees[$year][$month], $info['fee']);
201                 }
202             }
203         }
204     }
205     return $fees;
206 }
207
208 function fees_get_list_for_month($year, $month)
209 {
210     $members = db_get_members();
211     $fees = array();
212     foreach ($members as $member) {
213         $membership_info = fees_get_list_for_member($member['id'], mktime(0, 0, 0, $month, 1, $year));
214
215         if (empty($membership_info)) {
216             continue;
217         }
218         $member['fee'] = $membership_info[$year][$month]['fee'];
219         $member['is_member'] = $membership_info[$year][$month]['is_member'];
220         $fees[] = $member;
221     }
222     return $fees;
223 }
224
225 function fee_next_directdebit_for_member($member_id, $max_date = null)
226 {
227
228     $config = Config::getInstance();
229     $member = db_get_member_with_id($member_id);
230     if (!$member['directdebit']) {
231         return null;
232     }
233
234     /** @var DateTime $direct_debit_date */
235     $direct_debit_date = $config->get('direct_debit')['date'];
236     $sum_old_fees = fees_sum_for_member($member_id, $direct_debit_date->getTimestamp() - 86400);
237     $sum_new_paid = finance_get_paid_fees_for_member($member_id);
238     $year = $direct_debit_date->format('Y');
239     $month = $direct_debit_date->format('n');
240     $day = 1;
241
242
243     while (true) {
244         $start_date = mktime(0, 0, 0, $month, $day, $year);
245         if (isset($max_date) && $start_date > $max_date) {
246             return null;
247         }
248
249         // check if fee is zero at the moment and skip to next event
250         // quit searching if theres no event in future
251         $current_fee = fees_for_member_at_date($member_id, $start_date);
252         if (empty($current_fee)) {
253             $events = db_get_events_for_member($member_id, $start_date + 86400);
254             if (empty($events)) {
255                 return null;
256             }
257             $start_date = db_date2unixtime($events[0]['event_date']);
258             $day = date('j', $start_date);
259             $month = date('n', $start_date);
260             $year = date('Y', $start_date);
261             continue;
262         }
263
264         $sum_fees = fees_sum_for_member($member_id, $start_date);
265         $sum_new_fees = bcsub($sum_fees, $sum_old_fees);
266         if (bccomp($sum_new_fees, $sum_new_paid) == 1) {
267             $info = fees_get_list_for_member($member_id, $start_date);
268             $months = 1;
269             $ret = array(
270                 'date'  => $start_date,
271                 'value' => bcsub($sum_new_fees, $sum_new_paid),
272                 'info'  => '',
273             );
274             switch ($info[$year][$month]['payment_interval']) {
275                 case 'monthly'   :
276                     $months = 1;
277                     break;
278                 case 'quarterly' :
279                     $months = 3;
280                     break;
281                 case 'halfyearly':
282                     $months = 6;
283                     break;
284                 case 'yearly'    :
285                     $months = 12;
286                     break;
287             }
288             if ($months == 1) {
289                 $ret['info'] = dtaus_string(sprintf('CCCFFM %d, %s', $member['number'], format_month($start_date)));
290                 return $ret;
291             }
292             $end_date = mktime(0, 0, 0, $month + $months - 1, 1, $year);
293             $sum_fee_end = fees_sum_for_member($member_id, $end_date);
294             $ret['value'] = bcadd($ret['value'], bcsub($sum_fee_end, $sum_fees));
295             $ret['info'] = dtaus_string(sprintf('CCCFFM %d, %s-%s', $member['number'], format_month($start_date),
296                 format_month($end_date)));
297             return $ret;
298         }
299         $day = 1;
300         $month++;
301         if ($month == 13) {
302             $month = 1;
303             $year++;
304         }
305     }
306
307
308 }
309
310 /* }}} */
311
312
313 function action_fees()
314 {/*{{{*/
315
316     if (isset($_REQUEST['member_id'])) {
317         render_fees_for_member($_REQUEST['member_id']);
318         return;
319     }
320     if (isset($_REQUEST['year']) && isset($_REQUEST['month'])) {
321         render_accrued_fees_for_month($_REQUEST['year'], $_REQUEST['month']);
322         return;
323     }
324
325     render_fees_by_member();
326     render_accrued_fees_by_month();
327     render_next_direct_debit();
328     render_future_fees();
329
330 }/*}}}*/
331
332 function render_fees_by_member()
333 {/*{{{*/
334     $members = db_get_members();
335     $config = Config::getInstance();
336     ?>
337     <h2>Mitgliedsbeitr&auml;ge nach Mitglied</h2>
338     <table>
339         <tr>
340             <th>Mitgliedsnummer</th>
341             <th>Nickname</th>
342             <th style="text-align: right;">Angefallene Beitr&auml;ge</th>
343             <th style="text-align: right;">Aktueller Beitrag</th>
344             <th style="text-align: right;">Offener Beitrag</th>
345         </tr>
346         <?php if (empty($members)) {
347             $members = array();
348         } ?>
349         <?php foreach ($members as $member) : ?>
350             <?php
351             $current_fee = fees_for_member_at_date($member['id'], time());
352
353             $sum_fees = fees_sum_for_member($member['id'], time());
354             /** @var DateTime $direct_debit */
355             $direct_debit = $config->get('direct_debit')['date'];
356             $sum_old_fees = fees_sum_for_member($member['id'], $direct_debit->getTimestamp() - 86400);
357             $sum_old_paid = finance_get_paid_fees_for_member($member['id'], true);
358             $sum_new_paid = finance_get_paid_fees_for_member($member['id']);
359             $sum_new_fees = bcsub($sum_fees, $sum_old_fees);
360             $open_fees = bcadd(bcsub($sum_old_fees, $sum_old_paid), max(bcsub($sum_new_fees, $sum_new_paid), 0));
361             ?>
362             <tr>
363                 <td><a href="<?= html_escape(link_to('fees',
364                         array('member_id' => $member['id']))) ?>"><?= html_escape($member['number']) ?></a></td>
365                 <td><?= html_escape($member['nickname']) ?></td>
366                 <td style="text-align: right;"><?= format_money($sum_fees) ?></td>
367                 <td style="text-align: right;"><?= isset($current_fee) ? format_money($current_fee) : '-' ?></td>
368                 <td style="text-align: right;"><?= $open_fees > 0 ? format_money($open_fees) : '-' ?></td>
369             </tr>
370         <?php endforeach ?>
371     </table>
372     <?php
373 }/*}}}*/
374
375 function render_future_fees()
376 {/*{{{*/
377     $total_paid = finance_get_total_paid_fees();
378     $this_year = date('Y');
379     $this_month = date('m');
380     $fees = fees_sum_by_month(mktime(0, 0, 0, date('m') + 6, date('d'), date('Y') + 1));
381     $total = 0;
382     foreach ($fees as $year => $months) {
383         foreach ($months as $month => $fee) {
384             $total = bcadd($total, $fee);
385             $fees[$year][$month] = array('total' => $total, 'fee' => $fee);
386         }
387     }
388     $fees = array_reverse($fees, true);
389     ?>
390     <h2>Beitragsprognose nach Monat</h2>
391     <table>
392         <tr>
393             <th>Monat</th>
394             <th style="text-align: right;">Mitgliedsbeitr&auml;ge</th>
395             <th style="text-align: right;">kummuliert</th>
396             <th style="text-align: right;">eingenommen</th>
397             <th style="text-align: right;"><strong>offen</strong></th>
398         </tr>
399         <?php foreach ($fees as $year => $months) : ?>
400             <?php $months = array_reverse($months, true); ?>
401             <?php foreach ($months as $month => $data) : ?>
402                 <tr<?php if ($year == $this_year && $month == $this_month) : ?> class="current"<?php endif ?>>
403                     <td><a href="<?= html_escape(link_to('fees',
404                             array('year' => $year, 'month' => $month))) ?>"><?= html_escape(format_month($year,
405                                 $month)) ?></a></td>
406                     <td style="text-align: right;"><?= html_escape(format_money($data['fee'])) ?></td>
407                     <td style="text-align: right;"><?= html_escape(format_money($data['total'])) ?></td>
408                     <td style="text-align: right;">
409                         <?php if ($year == $this_year && $month == $this_month) : ?>
410                             <?= html_escape(format_money($total_paid)) ?>
411                         <?php endif ?>
412                     </td>
413                     <td style="text-align: right;">
414                         <?php if ($year > $this_year || ($year >= $this_year && $month >= $this_month)) : ?>
415                             <?= html_escape(format_money(bcsub($data['total'], $total_paid))) ?>
416                         <?php endif ?>
417                     </td>
418                 </tr>
419             <?php endforeach ?>
420         <?php endforeach ?>
421     </table>
422     <?php
423 }/*}}}*/
424
425 function render_accrued_fees_by_month()
426 {/*{{{*/
427     $fees = fees_sum_by_month(time());
428     $fees = array_reverse($fees, true);
429     ?>
430     <h2>Angefallene Mitgliedsbeitr&auml;ge nach Monat</h2>
431     <table>
432         <tr>
433             <th>Monat</th>
434             <th style="text-align: right;">Mitgliedsbeitrag</th>
435         </tr>
436         <?php foreach ($fees as $year => $months) : ?>
437             <?php $months = array_reverse($months, true); ?>
438             <?php foreach ($months as $month => $fee) : ?>
439                 <tr>
440                     <td><a href="<?= html_escape(link_to('fees',
441                             array('year' => $year, 'month' => $month))) ?>"><?= html_escape(format_month($year,
442                                 $month)) ?></a></td>
443                     <td style="text-align: right;"><?= html_escape(format_money($fee)) ?></td>
444                 </tr>
445             <?php endforeach ?>
446         <?php endforeach ?>
447     </table>
448     <?php
449 }/*}}}*/
450
451 function render_accrued_fees_for_month($year, $month)
452 {/*{{{*/
453     $fees = fees_get_list_for_month($year, $month);
454     ?>
455     <h2>Angefallene Mitgliedsbeitr&auml;ge f&uuml;r <?= format_month($year, $month) ?></h2>
456     <table>
457         <tr>
458             <th>Mitgliedsnummer</th>
459             <th>Nickname</th>
460             <th style="text-align: right;">Mitgliedsbeitrag</th>
461         </tr>
462         <?php foreach ($fees as $info) : ?>
463             <tr>
464                 <td><a href="<?= html_escape(link_to('fees',
465                         array('member_id' => $info['id']))) ?>"><?= html_escape($info['number']) ?></a></td>
466                 <td><?= html_escape($info['nickname']) ?></td>
467                 <td style="text-align: right;"><?= html_escape($info['is_member'] ? format_money($info['fee']) : '-') ?></td>
468             </tr>
469         <?php endforeach ?>
470     </table>
471     <p><a href="<?= html_escape(link_to('fees')) ?>">Alle angefallenen Mitgliedsbeitr&auml;ge</a></p>
472     <?php
473 }/*}}}*/
474
475 function render_next_direct_debit()
476 {/*{{{*/
477     $members = db_get_members();
478     ?>
479     <h2>Nächste Abbuchungen nach Mitglied</h2>
480     <table>
481         <tr>
482             <th>Mitgliedsnummer</th>
483             <th>Nickname</th>
484             <th style="text-align: right;">Verwendungszweck</th>
485             <th style="text-align: right;">Betrag</th>
486         </tr>
487         <?php if (empty($members)) {
488             $members = array();
489         } ?>
490         <?php foreach ($members as $member) : ?>
491             <?php $next_debit = fee_next_directdebit_for_member($member['id']); ?>
492             <tr>
493                 <td><a href="<?= html_escape(link_to('fees',
494                         array('member_id' => $member['id']))) ?>"><?= html_escape($member['number']) ?></a></td>
495                 <td><?= html_escape($member['nickname']) ?></td>
496                 <?php if (empty($next_debit)) : ?>
497                     <td>-</td>
498                     <td style="text-align: right;">-</td>
499                 <?php else : ?>
500                     <td><?= html_escape($next_debit['info']) ?></td>
501                     <td style="text-align: right;"><?= format_money($next_debit['value']) ?></td>
502                 <?php endif ?>
503             </tr>
504         <?php endforeach ?>
505     </table>
506     <?php
507 }
508
509 function render_fees_for_member($member_id)
510 {/*{{{*/
511     global $MEMBER_TYPES, $EARNING_TYPES, $EXPENSE_TYPES;
512     $config = Config::getInstance();
513     /** @var DateTime $direct_debit */
514     $direct_debit = $config->get('direct_debit')['date'];
515
516     $member = db_get_member_with_id($member_id);
517     if (!isset($member)) {
518         redirect(link_to('fees'));
519     }
520
521     $membership_info = fees_get_list_for_member($member_id, time());
522     $membership_info = array_reverse($membership_info, true);
523
524     $paid_fees = finance_list_paid_fees_for_member($member_id, time(), true);
525
526     $sum_new_paid = finance_get_paid_fees_for_member($member_id);
527     $sum_old_paid = finance_get_paid_fees_for_member($member_id, true);
528     $sum_old_fees = fees_sum_for_member($member_id, $direct_debit->getTimestamp() - 86400);
529     $sum_fees = fees_sum_for_member($member_id, time());
530     $sum_new_fees = bcsub($sum_fees, $sum_old_fees);
531
532     $state = '';
533     $new_open = 0;
534     $old_open = 0;
535     if (bccomp($sum_new_fees, $sum_new_paid) == 1) {
536         $new_open = 1;
537     }
538     if (bccomp($sum_old_fees, $sum_old_paid) == 1) {
539         $old_open = 1;
540     }
541
542     if ($new_open && $old_open) {
543         $state = sprintf('Es sind noch %1$s Mitgliedsbeitrag offen, davon %2$s für die Zeit vor dem %3$s und %4$s für danach.',
544             format_money(bcadd(bcsub($sum_old_fees, $sum_old_paid), bcsub($sum_new_fees, $sum_new_paid))),
545             format_money(bcsub($sum_old_fees, $sum_old_paid)),
546             format_date($direct_debit->getTimestamp()),
547             format_money(bcsub($sum_new_fees, $sum_new_paid))
548         );
549     } elseif ($new_open) {
550         $state = sprintf('Es sind noch %1$s Mitgliedsbeitrag offen.',
551             format_money(bcsub($sum_new_fees, $sum_new_paid)));
552     } elseif ($old_open) {
553         $state = sprintf('Für die Zeit vor dem %1$s sind noch %2$s Mitgliedsbeitrag offen.',
554             format_date($direct_debit->getTimestamp()), format_money(bcsub($sum_old_fees, $sum_old_paid)));
555     }
556
557     $next_debit = fee_next_directdebit_for_member($member_id);
558
559     ?>
560     <h2>Mitgliedsbeitr&auml;ge
561         von <?= html_escape(!empty($member['nickname']) ? $member['nickname'] : sprintf('Mitglied Nr. %d',
562             $member['number'])) ?></h2>
563     <h3>Mitgliedsdetails</h3>
564     <table>
565         <tr>
566             <th>Mitgliedsnummer</th>
567             <th>Nickname</th>
568             <th>Status</th>
569         </tr>
570         <tr>
571             <td><a href="<?= html_escape(link_to('view_member',
572                     array('id' => $member['id']))) ?>"><?= html_escape($member['number']) ?></a></td>
573             <td><?= html_escape($member['nickname']) ?></strong></p></td>
574             <td>
575                 <?php if (empty($state)) : ?>
576                     Kein Beitragsrückstand
577                 <?php else : ?>
578                     <?= wordwrap(html_escape($state), 70, '<br/>') ?>
579                 <?php endif ?>
580             </td>
581         </tr>
582     </table>
583     <div style="float: left">
584         <h3>Angefallene Mitgliedsbeitr&auml;ge</h3>
585         <table>
586             <tr>
587                 <th>Monat</th>
588                 <th>Mitgliedsart</th>
589                 <th style="text-align: right;">Mitgliedsbeitrag</th>
590             </tr>
591             <?php foreach ($membership_info as $year => $months) : ?>
592                 <?php $months = array_reverse($months, true); ?>
593                 <?php foreach ($months as $month => $info) : ?>
594                     <tr>
595                         <td><?= html_escape(format_month($year, $month)) ?></td>
596                         <td><?= html_escape($info['is_member'] ? $MEMBER_TYPES[$info['member_type']] : 'Kein Mitglied') ?></td>
597                         <td style="text-align: right;"><?= html_escape($info['is_member'] ? format_money($info['fee']) : '-') ?></td>
598                     </tr>
599                 <?php endforeach ?>
600             <?php endforeach ?>
601         </table>
602         <p><a href="<?= html_escape(link_to('fees')) ?>">Alle angefallenen Mitgliedsbeitr&auml;ge</a></p>
603     </div>
604     <div style="float: left; margin-left: 1em;">
605         <h3>Nächste Abbuchung</h3>
606         <table>
607             <tr>
608                 <th>Verwendungszweck</th>
609                 <th style="text-align: right;">Betrag</th>
610             </tr>
611             <?php if (empty($next_debit)) : ?>
612                 <td>-</td>
613                 <td style="text-align: right;">-</td>
614             <?php else : ?>
615                 <td><?= html_escape($next_debit['info']) ?></td>
616                 <td style="text-align: right;"><?= format_money($next_debit['value']) ?></td>
617             <?php endif ?>
618         </table>
619         <h3>Bezahlte Mitgliedsbeitr&auml;ge</h3>
620         <table>
621             <tr>
622                 <th>Monat</th>
623                 <th style="text-align: right;">Typ</th>
624                 <th style="text-align: right;">Betrag</th>
625             </tr>
626             <?php foreach ($paid_fees as $payment) : ?>
627                 <tr>
628                     <td><?= html_escape(format_date(db_date2unixtime($payment['date']))) ?></td>
629                     <td><?= ($payment['value'] < 0) ? $EXPENSE_TYPES[$payment['type']] : $EARNING_TYPES[$payment['type']] ?></td>
630                     <td style="text-align: right;"><?= format_money($payment['value']) ?></td>
631                 </tr>
632             <?php endforeach ?>
633         </table>
634     </div>
635     <br style="clear: left;"/>
636     <?php
637 }/*}}}*/