modules/xnetevents/xnetevents.inc.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2014 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22 // {{{ function get_event_order()
  23 /* get the order to paste the events
  24  * @param $asso_id: group's id
  25  */
  26 function get_event_order($asso_id)
  27 {
  28     $order = XDB::fetchOneCell('SELECT g.event_order
  29                                   FROM groups as g
  30                                  WHERE id = {?}',
  31                                        $asso_id);
  32     return $order;
  33 }
  34 // }}}
  35
  36 // {{{ function get_events()
  37 /* get the events of the given group ordered by the standard order for the group
  38  * @param $asso_id: group's id
  39  * @param $order: order to paste the events (asc or desc)
  40  * @param $archive: whether to get the archived events (1) or the actuals (0)
  41  */
  42 function get_events($asso_id, $order, $archive)
  43 {
  44     if ($order != 'asc' && $order != 'desc') {
  45         $order = 'desc';
  46     }
  47     $evts = XDB::fetchAllAssoc('eid', "SELECT ge.eid, ge.uid, ge.intitule, ge.debut, ge.fin, ge.show_participants, ge.deadline_inscription, ge.accept_nonmembre, ge.paiement_id
  48                                          FROM group_events as ge
  49                                         WHERE asso_id = {?} and archive = {?}
  50                                      ORDER BY ge.debut $order",
  51                                               $asso_id, $archive);
  52     return $evts;
  53 }
  54 // }}}
  55
  56 // {{{ function get_event() (detail, for subs page only for now)
  57 /* get event details
  58  * @param $eid: event's id
  59  */
  60 function get_event(&$eid)
  61 {
  62     if (!is_numeric($eid)) {
  63         $id = XDB::fetchOneCell("SELECT eid
  64                                    FROM group_events
  65                                   WHERE short_name = {?}",
  66                                         $eid);
  67         $eid = $id;
  68     }
  69     $evt = XDB::fetchOneAssoc('SELECT ge.uid, ge.intitule, ge.descriptif, ge.debut, ge.fin, ge.deadline_inscription, ge.accept_nonmembre, ge.paiement_id
  70                                          FROM group_events as ge
  71                                         WHERE eid = {?}',
  72                                         $eid);
  73     if (!is_null($evt['deadline_inscription']) && strtotime($evt['deadline_inscription']) <= time()) {
  74         $evt['inscr_open'] = false;
  75     } else {
  76         $evt['inscr_open'] = true;
  77     }
  78     $evt['organizer'] = User::getSilent($evt['uid']);
  79     $evt['date'] = make_event_date($evt['debut'], $evt['fin']);
  80
  81     return $evt;
  82 }
  83 // }}}
  84
  85 // {{{ function get_event_items()
  86 /** get items of the given event
  87  *
  88  * @param $eid : event's id
  89  *
  90  */
  91 function get_event_items($eid)
  92 {
  93     $evt = XDB::fetchAllAssoc('item_id', 'SELECT gei.item_id, gei.titre, gei.details, gei.montant
  94         FROM group_event_items as gei
  95         WHERE eid = {?}',
  96         $eid);
  97     return $evt;
  98 }
  99 // }}}
 100
 101 // {{{ function get_event_subscription()
 102 /* get all participations if uid is not specified, only the user's participation if uid specified
 103  * @param $eid: event's id
 104  * @param $uid: user's id
 105  */
 106 function get_event_subscription($eid, $uid = null)
 107 {
 108     if (!is_null($uid)) {
 109         $where = ' and gep.uid = '.$uid;
 110     }
 111     else {
 112         $where = '';
 113     }
 114    $sub =  XDB::fetchAllAssoc('item_id','SELECT gep.item_id, gep.nb, gep.paid FROM group_event_participants as gep
 115                                           WHERE gep.eid = {?}'.$where,
 116                                                 $eid);
 117    return $sub;
 118 }
 119 // }}}
 120
 121 // {{{ function get_event_telepaid()
 122 /* get the total payments made by a user for an event
 123  * @param $eid: event's id
 124  * @param $uid: user's id
 125  */
 126 function get_event_telepaid($eid, $uid)
 127 {
 128    $telepaid = XDB::fetchOneCell('SELECT SUM(pt.amount)
 129                                     FROM payment_transactions AS pt
 130                                LEFT JOIN group_events as ge ON (ge.paiement_id = pt.ref)
 131                                    WHERE pt.status = "confirmed" AND ge.eid = {?} AND pt.uid = {?}',
 132                                          $eid, $uid);
 133     return $telepaid;
 134 }
 135 // }}}
 136
 137 // {{{ function get_event_detail()
 138
 139 function get_event_detail($eid, $item_id = false, $asso_id = null)
 140 {
 141     global $globals;
 142     if (is_null($asso_id)) {
 143         $asso_id = $globals->asso('id');
 144     }
 145     if (!$item_id) {
 146         $where = '';
 147         $group_by = 'e.eid';
 148     } else {
 149         $where = XDB::format(' AND ei.item_id = {?}', $item_id);
 150         $group_by = 'ei.item_id';
 151     }
 152     $evt = XDB::fetchOneAssoc('SELECT  SUM(nb) AS nb_tot, COUNT(DISTINCT ep.uid) AS nb, e.*, SUM(IF(nb > 0, 1, 0)) AS user_count,
 153                                        IF(e.deadline_inscription,
 154                                           e.deadline_inscription >= LEFT(NOW(), 10),
 155                                           1) AS inscr_open,
 156                                        LEFT(e.debut, 10) AS first_day, LEFT(e.fin, 10) AS last_day,
 157                                        LEFT(NOW(), 10) AS now,
 158                                        ei.titre, e.subscription_notification
 159                                  FROM  group_events             AS e
 160                            INNER JOIN  group_event_items        AS ei ON (e.eid = ei.eid)
 161                             LEFT JOIN  group_event_participants AS ep ON(e.eid = ep.eid AND ei.item_id = ep.item_id)
 162                                 WHERE  (e.eid = {?} OR e.short_name = {?}) AND e.asso_id = {?}' . $where . '
 163                              GROUP BY  ' . $group_by,
 164                               $eid, $eid, $asso_id);
 165
 166     if (!$evt) {
 167         return null;
 168     }
 169     if ($GLOBALS['IS_XNET_SITE'] && $evt['accept_nonmembre'] == 0 && !is_member() && !may_update()) {
 170         return false;
 171     }
 172
 173     if (!$item_id) {
 174         /* Don't try to be to smart here, in case we're getting the global summary, we cannot have
 175          * a general formula to estimate the total number of comers since 'moments' may (or may not be)
 176          * disjuncted. As a consequence, we can only provides the number of user having fullfiled the
 177          * registration procedure.
 178          */
 179         $evt['user_count'] = $evt['nb_tot'] = $evt['nb'];
 180         $evt['titre'] = '';
 181         $evt['item_id'] = 0;
 182         $evt['csv_name'] = urlencode($evt['intitule']);
 183     } else {
 184         $evt['csv_name'] = urlencode($evt['intitule'] . '.' . $evt['titre']);
 185     }
 186
 187     $evt['moments'] = XDB::fetchAllAssoc('SELECT  titre, details, montant, ei.item_id, nb,
 188                                                   ep.paid, FIND_IN_SET(\'notify_payment\', ep.flags) AS notify_payment
 189                                             FROM  group_event_items        AS ei
 190                                        LEFT JOIN  group_event_participants AS ep ON (ep.eid = ei.eid AND ep.item_id = ei.item_id
 191                                                                                                              AND uid = {?})
 192                                            WHERE  ei.eid = {?}',
 193                                            S::i('uid'), $evt['eid']);
 194     $evt['topay'] = 0;
 195     $evt['paid'] = 0;
 196     $evt['notify_payment'] = false;
 197     foreach ($evt['moments'] as $m) {
 198         $evt['topay'] += $m['nb'] * $m['montant'];
 199         if ($m['montant']) {
 200             $evt['money'] = true;
 201         }
 202         $evt['paid'] += $m['paid'];
 203         $evt['notify_payment'] = $evt['notify_payment'] || $m['notify_payment'];
 204     }
 205
 206     $montant = XDB::fetchOneCell('SELECT  SUM(amount) AS sum_amount
 207                                     FROM  payment_transactions AS t
 208                                    WHERE  status = "confirmed" AND ref = {?} AND uid = {?}',
 209                                    $evt['paiement_id'], S::v('uid'));
 210     $evt['telepaid'] = $montant;
 211     $evt['paid'] += $montant;
 212     $evt['organizer'] = User::getSilent($evt['uid']);
 213
 214     $evt['date'] = make_event_date($evt['debut'], $evt['fin']);
 215
 216     $evt['show_participants'] = ($evt['show_participants'] && $GLOBALS['IS_XNET_SITE'] && (is_member() || may_update()));
 217
 218     return $evt;
 219 }
 220
 221 // }}}
 222
 223 // {{{ function get_event_participants()
 224 function get_event_participants(&$evt, $item_id, array $tri = array(), $limit = null, $offset = 0)
 225 {
 226     global $globals;
 227
 228     $eid    = $evt['eid'];
 229     $money  = $evt['money'] && (function_exists('may_update')) && may_update();
 230     $pay_id = $evt['paiement_id'];
 231
 232     $append = $item_id ? XDB::format(' AND ep.item_id = {?}', $item_id) : '';
 233     $query = XDB::fetchAllAssoc('uid', 'SELECT  ep.uid, SUM(ep.paid) AS paid, SUM(ep.nb) AS nb,
 234                                                 FIND_IN_SET(\'notify_payment\', ep.flags) AS notify_payment
 235                                           FROM  group_event_participants AS ep
 236                                          WHERE  ep.eid = {?} AND nb > 0 ' . $append . '
 237                                       GROUP BY  ep.uid', $eid);
 238     $uf = new UserFilter(new PFC_True(), $tri);
 239     $users = User::getBulkUsersWithUIDs($uf->filter(array_keys($query), new PlLimit($limit, $offset)));
 240     $tab = array();
 241     foreach ($users as $user) {
 242         $uid = $user->id();
 243         $tab[$uid] = $query[$uid];
 244         $tab[$uid]['user'] = $user;
 245     }
 246
 247     if ($item_id) {
 248         return $tab;
 249     }
 250
 251     $evt['adminpaid'] = 0;
 252     $evt['telepaid']  = 0;
 253     $evt['topay']     = 0;
 254     $evt['paid']      = 0;
 255     foreach ($tab as $uid=>&$u) {
 256         $u['adminpaid'] = (float)$u['paid'];
 257         $u['montant'] = 0;
 258         if ($money && $pay_id) {
 259             $montant = XDB::fetchOneCell('SELECT  SUM(amount)
 260                                             FROM  payment_transactions AS t
 261                                            WHERE  status = "confirmed" AND ref = {?} AND uid = {?}',
 262                                          $pay_id, $uid);
 263             $u['paid'] += $montant;
 264         }
 265         $u['telepayment'] = $u['paid'] - $u['adminpaid'];
 266         $res_ = XDB::iterator('SELECT  ep.nb, ep.item_id, ei.montant
 267                                  FROM  group_event_participants AS ep
 268                            INNER JOIN  group_event_items AS ei ON (ei.eid = ep.eid AND ei.item_id = ep.item_id)
 269                                 WHERE  ep.eid = {?} AND ep.uid = {?}',
 270                             $eid, $uid);
 271         while ($i = $res_->next()) {
 272             $u[$i['item_id']] = $i['nb'];
 273             $u['montant'] += $i['montant'] * $i['nb'];
 274         }
 275         $evt['telepaid']  += $u['telepayment'];
 276         $evt['adminpaid'] += $u['adminpaid'];
 277         $evt['paid']      += $u['paid'];
 278         $evt['topay']     += $u['montant'];
 279     }
 280     return $tab;
 281 }
 282 // }}}
 283
 284 //  {{{ function subscribe()
 285 /** set or update the user's subscription
 286  *
 287  * @param $uid: user's id
 288  * @param $eid: event's id
 289  * @param $subs: user's new subscription
 290  *
 291  */
 292 function subscribe($uid, $eid, $subs = array())
 293 {
 294     global $globals;
 295     // get items
 296     $items = get_event_items($eid);
 297     // get previous subscription
 298     $old_subs = get_event_subscription($eid, $uid);
 299     $participate = false;
 300     $updated = false;
 301     // TODO : change the way to deal with manual payment
 302     $paid = 0;
 303     foreach ($old_subs as $item_id => $s) {
 304         $paid += $s['paid'];
 305     }
 306     $paid_updated = false;
 307     // for each item of the event
 308     foreach ($items as $item_id => $details) {
 309         // check if there is an old subscription
 310         if (array_key_exists($item_id, $old_subs)) {
 311             // compares new and old subscription
 312             if ($old_subs[$item_id]['nb'] != $subs[$item_id]) {
 313                 if ($subs[$item_id] != 0) {
 314                     echo "je m'inscris  ";
 315                     XDB::execute('INSERT INTO group_event_participants (eid, uid, item_id, nb, flags, paid)
 316                                        VALUES ({?}, {?}, {?}, {?}, {?}, {?})
 317                       ON DUPLICATE KEY UPDATE nb = VALUES(nb), flags = VALUES(flags), paid = VALUES(paid)',
 318                                              $eid, $uid, $item_id, $subs[$item_id],(Env::has('notify_payment') ? 'notify_payment' : 0), (!$paid_updated ? $paid : 0));
 319                     $participate = true;
 320                     $paid_updated = true;
 321                 } else { // we do not store non-subscription to event items
 322                     XDB::execute('DELETE FROM group_event_participants
 323                                         WHERE eid = {?} AND uid = {?} AND item_id = {?}',
 324                                               $eid, $uid, $item_id);
 325                 }
 326                 $updated = true;
 327             }
 328         } else { // if no old subscription
 329             if ($subs[$item_id] != 0) {
 330                 XDB::execute('INSERT INTO group_event_participants (eid, uid, item_id, nb, flags, paid)
 331                                    VALUES ({?}, {?}, {?}, {?}, {?}, {?})',
 332                                           $eid, $uid, $item_id, $subs[$item_id], '', 0);
 333                 $participate = true;
 334                 $updated = true;
 335             }
 336         }
 337     }
 338     // item 0 stores whether the user participates globally or not, if he has to be notified when payment is created and his manual payment
 339     /*
 340     if (array_key_exists(0, $old_subs)) {
 341         XDB::execute('UPDATE group_event_participants
 342                          SET nb = {?}
 343                        WHERE eid = {?}, uid = {?}, item_id = 0',
 344                              ($participate ? 1 : 0), $eid, $uid);
 345     } else {
 346         XDB::execute('INSERT INTO group_event_participants (eid, uid, item_id, nb, flags, paid)
 347                            VALUES ({?}, {?}, {?}, {?}, {?}, {?})',
 348                                   $eid, $uid, 0, ($participate ? 1 : 0), (Env::has('notify_payment') ? 'notify_payment' : ''), 0);
 349     }
 350     */
 351     // if subscription is updated, we have to update the event aliases
 352     if ($updated) {
 353         $short_name = get_event_detail($eid)['short_name'];
 354         subscribe_lists_event($uid, $short_name, ($participate ? 1 : -1), 0);
 355     }
 356     return $updated;
 357 }
 358 //  }}}
 359
 360 // TODO : correct this function to be compatible with subscribe() (use $eid, remove useless argument)
 361 // TODO : correct other calls
 362 //  {{{ function subscribe_lists_event()
 363 /** Subscribes user to various event related mailing lists.
 364  *
 365  * @param $uid: user's id.
 366  * @param short_name: event's short_name, which corresponds to the beginning of the emails.
 367  * @param participate: indicates if the user takes part at the event or not;
 368  *      -1 means he did not answer, 0 means no, and 1 means yes.
 369  * @param paid: has the user already payed anything?
 370  *      0 means no, a positive amount means yes.
 371  * @param payment: is this function called from a payment page?
 372  *      If true, only payment related lists should be updated.
 373  */
 374 function subscribe_lists_event($uid, $short_name, $participate, $paid, $payment = false)
 375 {
 376     global $globals;
 377     require_once 'emails.inc.php';
 378
 379     if (is_null($short_name)) {
 380         return;
 381     }
 382
 383     /** If $payment is not null, we do not retrieve the value of $participate,
 384      * thus we do not alter participant and absent lists.
 385      */
 386     if ($payment === true) {
 387         if ($paid > 0) {
 388             delete_from_list_alias($uid, $short_name . $globals->xnet->unpayed_list, $globals->xnet->evts_domain, 'event');
 389             add_to_list_alias($uid, $short_name . $globals->xnet->payed_list, $globals->xnet->evts_domain, 'event');
 390         }
 391     } else {
 392         switch ($participate) {
 393           case -1:
 394             delete_from_list_alias($uid, $short_name . $globals->xnet->participant_list, $globals->xnet->evts_domain, 'event');
 395             delete_from_list_alias($uid, $short_name . $globals->xnet->unpayed_list, $globals->xnet->evts_domain, 'event');
 396             delete_from_list_alias($uid, $short_name . $globals->xnet->payed_list, $globals->xnet->evts_domain, 'event');
 397             add_to_list_alias($uid, $short_name . $globals->xnet->absent_list, $globals->xnet->evts_domain, 'event');
 398             break;
 399           case 0:
 400             delete_from_list_alias($uid, $short_name . $globals->xnet->participant_list, $globals->xnet->evts_domain, 'event');
 401             delete_from_list_alias($uid, $short_name . $globals->xnet->absent_list, $globals->xnet->evts_domain, 'event');
 402             delete_from_list_alias($uid, $short_name . $globals->xnet->unpayed_list, $globals->xnet->evts_domain, 'event');
 403             delete_from_list_alias($uid, $short_name . $globals->xnet->payed_list, $globals->xnet->evts_domain, 'event');
 404             break;
 405           case 1:
 406             add_to_list_alias($uid, $short_name . $globals->xnet->participant_list, $globals->xnet->evts_domain, 'event');
 407             delete_from_list_alias($uid, $short_name . $globals->xnet->absent_list, $globals->xnet->evts_domain, 'event');
 408             if ($paid > 0) {
 409                 delete_from_list_alias($uid, $short_name . $globals->xnet->unpayed_list, $globals->xnet->evts_domain, 'event');
 410                 add_to_list_alias($uid, $short_name . $globals->xnet->payed_list, $globals->xnet->evts_domain, 'event');
 411             } else {
 412                 add_to_list_alias($uid, $short_name . $globals->xnet->unpayed_list, $globals->xnet->evts_domain, 'event');
 413                 delete_from_list_alias($uid, $short_name . $globals->xnet->payed_list, $globals->xnet->evts_domain, 'event');
 414             }
 415             break;
 416         }
 417     }
 418 }
 419 // }}}
 420
 421 //  {{{ function event_change_shortname()
 422 function event_change_shortname($page, $eid, $old, $new)
 423 {
 424     global $globals;
 425     require_once 'emails.inc.php';
 426
 427     if (is_null($old)) {
 428         $old = '';
 429     }
 430     // Quelques vérifications sur l'alias (caractères spéciaux)
 431     if ($new && !preg_match( "/^[a-zA-Z0-9\-.]{3,20}$/", $new)) {
 432         $page->trigError("Le raccourci demandé n'est pas valide.
 433                     Vérifie qu'il comporte entre 3 et 20 caractères
 434                     et qu'il ne contient que des lettres non accentuées,
 435                     des chiffres ou les caractères - et .");
 436         return $old;
 437     } elseif ($new && (is_int($new) || ctype_digit($new))) {
 438         $page->trigError("Le raccourci demandé ne peut être accepté car il
 439                          ne contient que des chiffres. Rajoute-lui par exemple
 440                          une lettre.");
 441         return $old;
 442     }
 443
 444     //vérifier que l'alias n'est pas déja pris
 445     if ($new && $old != $new) {
 446         $res = XDB::query('SELECT COUNT(*)
 447                              FROM group_events
 448                             WHERE short_name = {?}',
 449                            $new);
 450         if ($res->fetchOneCell() > 0) {
 451             $page->trigError("Le raccourci demandé est déjà utilisé. Choisis en un autre.");
 452             return $old;
 453         }
 454     }
 455
 456     if ($old == $new) {
 457         return $new;
 458     }
 459
 460     if ($old && $new) {
 461         // if had a previous shortname change the old lists
 462         foreach (explode(',', $globals->xnet->event_lists) as $suffix) {
 463             XDB::execute('UPDATE  email_virtual
 464                              SET  email = {?}
 465                            WHERE  type = \'event\' AND email = {?}',
 466                          $new . $suffix, $old . $suffix);
 467         }
 468
 469         return $new;
 470     }
 471
 472     if (!$old && $new) {
 473         // if we have a first new short_name create the lists
 474         $lastid = array();
 475         $where = array(
 476             $globals->xnet->participant_list => 'g.nb > 0',
 477             $globals->xnet->payed_list       => '(g.paid > 0 OR p.amount > 0)',
 478             $globals->xnet->unpayed_list     => 'g.nb > 0 AND g.paid = 0 AND p.amount IS NULL'
 479         );
 480
 481         foreach (array($globals->xnet->participant_list, $globals->xnet->payed_list, $globals->xnet->unpayed_list) as $suffix) {
 482             $uids = XDB::fetchColumn('SELECT  g.uid
 483                                         FROM  group_event_participants AS g
 484                                   INNER JOIN  group_events             AS e ON (g.eid = e.eid)
 485                                    LEFT JOIN  payment_transactions     AS p ON (e.paiement_id = p.ref AND g.uid = p.uid)
 486                                        WHERE  g.eid = {?} AND ' . $where[$suffix],
 487                                      $eid);
 488             foreach ($uids as $uid) {
 489                 add_to_list_alias($uid, $new . $suffix, $globals->xnet->evts_domain, 'event');
 490             }
 491         }
 492
 493         $uids = XDB::fetchColumn('SELECT  m.uid
 494                                     FROM  group_members            AS m
 495                                LEFT JOIN  group_event_participants AS e ON (e.uid = m.uid AND e.eid = {?})
 496                                    WHERE  m.asso_id = {?} AND e.uid IS NULL',
 497                                  $eid, $globals->asso('id'));
 498         foreach ($uids as $uid) {
 499             add_to_list_alias($uid, $new . $globals->xnet->absent_list, $globals->xnet->evts_domain, 'event');
 500         }
 501
 502         return $new;
 503     }
 504
 505     if ($old && !$new) {
 506         // if we delete the old short name, delete the lists
 507         foreach (explode(',', $globals->xnet->event_lists) as $suffix) {
 508             delete_list_alias($old . $suffix, $globals->xnet->evts_domain);
 509         }
 510
 511         return $new;
 512     }
 513
 514     // cannot happen
 515     return $old;
 516 }
 517 // }}}
 518
 519 //  {{{ function make_event_date()
 520 function make_event_date($debut, $fin)
 521 {
 522     $start     = strtotime($debut);
 523     $end       = strtotime($fin);
 524 //    $first_day = $e['first_day'];
 525 //    $last_day  = $e['last_day'];
 526     $first_day = strftime("%d %B %Y", $start);
 527     $last_day = strftime("%d %B %Y", $end);
 528     $date = "";
 529     if ($start && $end != $start) {
 530         if ($first_day == $last_day) {
 531           $date .= "le " . strftime("%d %B %Y", $start) . " de "
 532                 . strftime("%H:%M", $start) . " à " . strftime("%H:%M", $end);
 533         } else {
 534           $date .= "du " . strftime("%d %B %Y à %H:%M", $start)
 535                 . "\nau " . strftime("%d %B %Y à %H:%M", $end);
 536         }
 537     } else {
 538         $date .= "le " . strftime("%d %B %Y à %H:%M", $start);
 539     }
 540     return $date;
 541 }
 542 // }}}
 543
 544 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker fenc=utf-8:
 545 ?>