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