modules/xnetevents/xnetevents.inc.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2009 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_detail()
  23
  24 function get_event_detail($eid, $item_id = false, $asso_id = null)
  25 {
  26     global $globals;
  27     if (is_null($asso_id)) {
  28         $asso_id = $globals->asso('id');
  29     }
  30     $res = XDB::query(
  31         "SELECT  SUM(nb) AS nb_tot, COUNT(DISTINCT ep.uid) AS nb, e.*,
  32                  IF(e.deadline_inscription, e.deadline_inscription >= LEFT(NOW(), 10), 1) AS inscr_open,
  33                  LEFT(10, e.debut) AS first_day, LEFT(10, e.fin) AS last_day,
  34                  LEFT(NOW(), 10) AS now, ei.titre,
  35                  al.vid AS absent_list, pl.vid AS participant_list,
  36                  bl.vid AS payed_list, ul.vid AS booked_unpayed_list,
  37                  a.nom, a.prenom, a.promo, aa.alias
  38            FROM  #groupex#.evenements              AS e
  39      INNER JOIN  #x4dat#.auth_user_md5             AS a  ON a.user_id = e.organisateur_uid
  40      INNER JOIN  #x4dat#.aliases                   AS aa ON (aa.type = 'a_vie' AND aa.id = a.user_id)
  41      INNER JOIN  #groupex#.evenements_items        AS ei ON (e.eid = ei.eid)
  42       LEFT JOIN  #groupex#.evenements_participants AS ep ON (e.eid = ep.eid AND ei.item_id = ep.item_id)
  43       LEFT JOIN  #x4dat#.virtual                   AS al ON (al.type = 'evt' AND al.alias = CONCAT(short_name, {?}))
  44       LEFT JOIN  #x4dat#.virtual                   AS pl ON (pl.type = 'evt' AND pl.alias = CONCAT(short_name, {?}))
  45       LEFT JOIN  #x4dat#.virtual                   AS bl ON (bl.type = 'evt' AND bl.alias = CONCAT(short_name, {?}))
  46       LEFT JOIN  #x4dat#.virtual                   AS ul ON (ul.type = 'evt' AND ul.alias = CONCAT(short_name, {?}))
  47           WHERE  (e.eid = {?} OR e.short_name = {?}) AND ei.item_id = {?} AND e.asso_id = {?}
  48        GROUP BY  ei.item_id",
  49         '-absents@' . $globals->xnet->evts_domain,
  50         '-participants@' . $globals->xnet->evts_domain,
  51         '-paye@' . $globals->xnet->evts_domain,
  52         '-participants-non-paye@' . $globals->xnet->evts_domain,
  53         $eid, $eid, $item_id ? $item_id : 1, $asso_id);
  54
  55     $evt = $res->fetchOneAssoc();
  56
  57     if (!$evt) {
  58         return null;
  59     }
  60     if ($GLOBALS['IS_XNET_SITE'] && $evt['accept_nonmembre'] == 0 && !is_member() && !may_update()) {
  61         return false;
  62     }
  63
  64     // smart calculation of the total number
  65     if (!$item_id) {
  66         $res = XDB::query(
  67                "SELECT MAX(nb)
  68                   FROM #groupex#.evenements              AS e
  69             INNER JOIN #groupex#.evenements_items        AS ei ON (e.eid = ei.eid)
  70              LEFT JOIN #groupex#.evenements_participants AS ep
  71                        ON (e.eid = ep.eid AND ei.item_id = ep.item_id)
  72                  WHERE e.eid = {?}
  73               GROUP BY ep.uid", $evt['eid']);
  74         $evt['nb_tot'] = array_sum($res->fetchColumn());
  75         $evt['titre'] = '';
  76         $evt['item_id'] = 0;
  77     }
  78
  79     $res = XDB::query(
  80         "SELECT titre, details, montant, ei.item_id, nb, ep.paid, FIND_IN_SET('notify_payment', ep.flags) AS notify_payment
  81            FROM #groupex#.evenements_items        AS ei
  82       LEFT JOIN #groupex#.evenements_participants AS ep
  83                 ON (ep.eid = ei.eid AND ep.item_id = ei.item_id AND uid = {?})
  84           WHERE ei.eid = {?}",
  85             S::v('uid'), $evt['eid']);
  86     $evt['moments'] = $res->fetchAllAssoc();
  87
  88     $evt['topay'] = 0;
  89     $evt['paid'] = 0;
  90     $evt['notify_payment'] = false;
  91     foreach ($evt['moments'] as $m) {
  92         $evt['topay'] += $m['nb'] * $m['montant'];
  93         if ($m['montant']) {
  94             $evt['money'] = true;
  95         }
  96         $evt['paid']  = $m['paid'];
  97         $evt['notify_payment'] = $evt['notify_payment'] || $m['notify_payment'];
  98     }
  99
 100     $req = XDB::query(
 101         "SELECT montant
 102            FROM {$globals->money->mpay_tprefix}transactions AS t
 103          WHERE ref = {?} AND uid = {?}", $evt['paiement_id'], S::v('uid'));
 104     $montants = $req->fetchColumn();
 105
 106     $evt['telepaid'] = 0;
 107     foreach ($montants as $m) {
 108         $p = strtr(substr($m, 0, strpos($m, 'EUR')), ',', '.');
 109         $evt['paid'] += trim($p);
 110         $evt['telepaid'] += trim($p);
 111     }
 112
 113     make_event_date($evt);
 114
 115     return $evt;
 116 }
 117
 118 // }}}
 119
 120 // {{{ function get_event_participants()
 121 function get_event_participants(&$evt, $item_id, $tri, $limit = '') {
 122     global $globals;
 123
 124     if (Env::has('initiale')) {
 125         $where = 'AND IF(u.nom IS NULL, m.nom,
 126                          IF(u.nom_usage<>"", u.nom_usage, u.nom))
 127                   LIKE "'.addslashes(Env::v('initiale')).'%"';
 128     } else {
 129         $where = '';
 130     }
 131
 132     $eid    = $evt['eid'];
 133     $money  = $evt['money'] && (function_exists('may_update')) && may_update();
 134     $pay_id = $evt['paiement_id'];
 135
 136     $query =
 137           "SELECT  IF(m.origine != 'X',m.nom,IF(u.nom_usage<>'', u.nom_usage, u.nom)) AS nom,
 138                    IF(m.origine != 'X',m.prenom,u.prenom) AS prenom,
 139                    IF(m.origine != 'X','extérieur',u.promo) AS promo,
 140                    IF(m.origine != 'X' OR u.perms = 'pending',m.email,a.alias) AS email,
 141                    IF(m.origine != 'X',m.sexe,FIND_IN_SET('femme', u.flags)) AS femme,
 142                    m.perms='admin' AS admin,
 143                    (m.origine = 'X' OR m.origine IS NULL) AS x,
 144                    ep.uid, SUM(ep.paid) AS paid, SUM(ep.nb) AS nb,
 145                    FIND_IN_SET('notify_payment', ep.flags) AS notify_payment
 146              FROM  #groupex#.evenements_participants AS ep
 147        INNER JOIN  #groupex#.evenements  AS e ON (ep.eid = e.eid)
 148         LEFT JOIN  #groupex#.membres     AS m ON ( ep.uid = m.uid AND e.asso_id = m.asso_id)
 149         LEFT JOIN  #x4dat#.auth_user_md5 AS u ON ( u.user_id = ep.uid )
 150         LEFT JOIN  #x4dat#.aliases       AS a ON ( a.id = ep.uid AND a.type='a_vie' )
 151             WHERE  ep.eid = {?}
 152                     ".(($item_id)?" AND item_id = $item_id":"")."
 153                     $where
 154          GROUP BY  ep.uid
 155          ORDER BY  $tri $limit";
 156
 157     if ($item_id) {
 158         $res = XDB::query($query, $eid);
 159         return $res->fetchAllAssoc();
 160     }
 161
 162     $res = XDB::iterator($query, $eid);
 163     $tab = array();
 164     $user = 0;
 165
 166     $evt['adminpaid'] = 0;
 167     $evt['telepaid']  = 0;
 168     $evt['topay']     = 0;
 169     $evt['paid']      = 0;
 170     while ($u = $res->next()) {
 171         if ($u['nb'] == 0) {
 172             continue;
 173         }
 174         $u['adminpaid'] = $u['paid'];
 175         $u['montant'] = 0;
 176         if ($money && $pay_id) {
 177             $res_ = XDB::query(
 178                 "SELECT montant
 179                    FROM {$globals->money->mpay_tprefix}transactions AS t
 180                   WHERE ref = {?} AND uid = {?}",
 181                 $pay_id, $u['uid']);
 182             $montants = $res_->fetchColumn();
 183             foreach ($montants as $m) {
 184                 $p = strtr(substr($m, 0, strpos($m, "EUR")), ",", ".");
 185                 $u['paid'] += trim($p);
 186             }
 187         }
 188         $u['telepayment'] = $u['paid'] - $u['adminpaid'];
 189         $res_ = XDB::iterator(
 190                 "SELECT  ep.nb, ep.item_id, ei.montant
 191                    FROM  #groupex#.evenements_participants AS ep
 192              INNER JOIN  #groupex#.evenements_items AS ei ON (ei.eid = ep.eid AND ei.item_id = ep.item_id)
 193                   WHERE  ep.eid = {?} AND ep.uid = {?}",
 194             $eid, $u['uid']);
 195         while ($i = $res_->next()) {
 196             $u[$i['item_id']] = $i['nb'];
 197             $u['montant'] += $i['montant']*$i['nb'];
 198         }
 199         $tab[] = $u;
 200         $evt['telepaid']  += $u['telepayment'];
 201         $evt['adminpaid'] += $u['adminpaid'];
 202         $evt['paid']      += $u['paid'];
 203         $evt['topay']     += $u['montant'];
 204     }
 205     return $tab;
 206 }
 207 // }}}
 208
 209 //  {{{ function subscribe_lists_event()
 210 function subscribe_lists_event($participate, $uid, $evt, $paid, $payment = null)
 211 {
 212     global $globals;
 213     $page =& Platal::page();
 214
 215     $participant_list  = $evt['participant_list'];
 216     $absent_list       = $evt['absent_list'];
 217     $unpayed_list      = $evt['booked_unpayed_list'];
 218     $payed_list        = $evt['payed_list'];
 219
 220     $user = User::getSilent($uid);
 221     if ($user) {
 222         $email = $user->forlifeEmail();
 223     } else {
 224         $res = XDB::query("SELECT  email
 225                              FROM  #groupex#.membres
 226                             WHERE  uid = {?} AND asso_id = {?}",
 227                           $uid, $globals->asso('id'));
 228         $email = $res->fetchOneCell();
 229     }
 230
 231     function subscribe($list, $email)
 232     {
 233         if ($list && $email) {
 234             XDB::execute("REPLACE INTO  virtual_redirect
 235                                 VALUES  ({?},{?})",
 236                          $list, $email);
 237         }
 238     }
 239
 240     function unsubscribe($list, $email)
 241     {
 242         if ($list && $email) {
 243             XDB::execute("DELETE FROM  virtual_redirect
 244                                 WHERE  vid = {?} AND redirect = {?}",
 245                          $list, $email);
 246         }
 247     }
 248
 249     if (is_null($payment)) {
 250         if (is_null($participate)) {
 251             unsubscribe($participant_list, $email);
 252             subscribe($absent_list, $email);
 253         } elseif ($participate) {
 254             subscribe($participant_list, $email);
 255             unsubscribe($absent_list, $email);
 256         } else {
 257             unsubscribe($participant_list, $email);
 258             unsubscribe($absent_list, $email);
 259         }
 260     }
 261     if ($paid > 0) {
 262         unsubscribe($unpayed_list, $email);
 263         subscribe($payed_list, $email);
 264     } else {
 265         unsubscribe($payed_list, $email);
 266         subscribe($unpayed_list, $email);
 267     }
 268 }
 269 // }}}
 270
 271 //  {{{ function event_change_shortname()
 272 function event_change_shortname(&$page, $eid, $old, $new)
 273 {
 274     global $globals;
 275
 276     if (is_null($old)) {
 277         $old = '';
 278     }
 279     // Quelques vérifications sur l'alias (caractères spéciaux)
 280     if ($new && !preg_match( "/^[a-zA-Z0-9\-.]{3,20}$/", $new)) {
 281         $page->trigError("Le raccourci demandé n'est pas valide.
 282                     Vérifie qu'il comporte entre 3 et 20 caractères
 283                     et qu'il ne contient que des lettres non accentuées,
 284                     des chiffres ou les caractères - et .");
 285         return $old;
 286     } elseif ($new && ctype_digit($new)) {
 287         $page->trigError("Le raccourci demandé ne peut être accepté car il
 288                          ne contient que des chiffres. Rajoute-lui par exemple
 289                          une lettre.");
 290         return $old;
 291     }
 292
 293     //vérifier que l'alias n'est pas déja pris
 294     if ($new && $old != $new) {
 295         $res = XDB::query('SELECT COUNT(*)
 296                              FROM #groupex#.evenements
 297                             WHERE short_name = {?}',
 298                            $new);
 299         if ($res->fetchOneCell() > 0) {
 300             $page->trigError("Le raccourci demandé est déjà utilisé. Choisis en un autre.");
 301             return $old;
 302         }
 303     }
 304
 305     if ($old == $new) {
 306         return $new;
 307     }
 308
 309     if ($old && $new) {
 310         // if had a previous shortname change the old lists
 311         foreach (array('-absents@', '-participants@', '-paye@', '-participants-non-paye@') as $v) {
 312             $v .= $globals->xnet->evts_domain;
 313             XDB::execute("UPDATE  virtual
 314                              SET  alias = {?}
 315                            WHERE  type = 'evt' AND alias = {?}",
 316                          $new . $v, $old . $v);
 317         }
 318         return $new;
 319     }
 320
 321     if (!$old && $new) {
 322         // if we have a first new short_name create the lists
 323         $lastid = array();
 324         $where = array(
 325             '-participants@'          => 'ep.nb > 0',
 326             '-paye@'                  => 'ep.paid > 0',
 327             '-participants-non-paye@' => 'ep.nb > 0 AND ep.paid = 0'
 328         );
 329
 330         foreach (array('-absents@', '-participants@', '-paye@', '-participants-non-paye@') as $v) {
 331             XDB::execute("INSERT INTO  virtual
 332                                   SET  type = 'evt', alias = {?}",
 333                          $new . $v . $globals->xnet->evts_domain);
 334
 335             $lastid[$v] = XDB::insertId();
 336         }
 337
 338         foreach (array('-participants@', '-paye@', '-participants-non-paye@') as $v) {
 339             XDB::execute("INSERT IGNORE INTO virtual_redirect (
 340                 SELECT  {?} AS vid, IF(a.alias IS NULL, m.email, CONCAT(a.alias, {?})) AS redirect
 341                   FROM  #groupex#.evenements_participants AS ep
 342              LEFT JOIN  #groupex#.membres     AS m ON (ep.uid = m.uid)
 343              LEFT JOIN  #x4dat#.auth_user_md5 AS u ON (u.user_id = ep.uid)
 344              LEFT JOIN  #x4dat#.aliases       AS a ON (a.id = ep.uid AND a.type = 'a_vie')
 345                  WHERE  ep.eid = {?} AND " . $where[$v] . "
 346               GROUP BY  ep.uid)",
 347                 $lastid[$v], '@' . $globals->mail->domain, $eid);
 348         }
 349         XDB::execute("INSERT IGNORE INTO virtual_redirect (
 350             SELECT  {?} AS vid, IF(a.alias IS NULL, m.email, CONCAT(a.alias, {?})) AS redirect
 351               FROM  #groupex#.membres     AS m
 352          LEFT JOIN  #groupex#.evenements_participants AS ep ON (ep.uid = m.uid AND ep.eid = {?})
 353          LEFT JOIN  #x4dat#.auth_user_md5 AS u ON (u.user_id = m.uid)
 354          LEFT JOIN  #x4dat#.aliases       AS a ON (a.id = m.uid AND a.type = 'a_vie')
 355              WHERE  m.asso_id = {?} AND ep.uid IS NULL
 356           GROUP BY  m.uid)",
 357             $lastid['-absents@'], '@' . $globals->mail->domain, $eid, $globals->asso('id'));
 358
 359         return $new;
 360     }
 361
 362     if ($old && !$new) {
 363         // if we delete the old short name, delete the lists
 364         foreach (array('-absents@', '-participants@', '-paye@', '-participants-non-paye@') as $v) {
 365             $v .= $globals->xnet->evts_domain;
 366             XDB::execute("DELETE  virtual, virtual_redirect
 367                             FROM  virtual
 368                        LEFT JOIN  virtual_redirect USING(vid)
 369                            WHERE  virtual.alias = {?}",
 370                          $infos['short_name'] . $v);
 371         }
 372         return $new;
 373     }
 374
 375     // cannot happen
 376     return $old;
 377 }
 378 // }}}
 379
 380 //  {{{ function make_event_date()
 381 function make_event_date(&$e)
 382 {
 383     $start     = strtotime($e['debut']);
 384     $end       = strtotime($e['fin']);
 385     $first_day = strtotime($e['first_day']);
 386     $last_day  = strtotime($e['last_day']);
 387     unset($e['debut'], $e['fin'], $e['first_day'], $e['last_day']);
 388
 389     $date = "";
 390     if ($start && $end != $start) {
 391         if ($first_day == $last_day) {
 392           $date .= "le " . strftime("%d %B %Y", $start) . " de "
 393                 . strftime("%H:%M", $start) . " à " . strftime("%H:%M", $end);
 394         } else {
 395           $date .= "du " . strftime("%d %B %Y à %H:%M", $start)
 396                 . "\nau " . strftime("%d %B %Y à %H:%M", $end);
 397         }
 398     } else {
 399         $date .= "le " . strftime("%d %B %Y à %H:%M", $start);
 400     }
 401     $e['date'] = $date;
 402 }
 403 // }}}
 404
 405 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 406 ?>