modules/xnetevents/xnetevents.inc.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2006 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)
  25 {
  26     global $globals;
  27     $res = XDB::query(
  28         "SELECT SUM(nb) AS nb_tot, e.*,
  29                 IF(e.deadline_inscription, e.deadline_inscription >= LEFT(NOW(), 10),
  30                    1) AS inscr_open,
  31                 LEFT(10, e.debut) AS debut_day, LEFT(10, e.fin) AS fin_day,
  32                 LEFT(NOW(), 10) AS now,
  33                 ei.titre,
  34                 al.vid AS absent_list, pl.vid AS participant_list,
  35                 a.nom, a.prenom, a.promo, aa.alias
  36            FROM groupex.evenements              AS e
  37      INNER JOIN x4dat.auth_user_md5             AS a  ON a.user_id = e.organisateur_uid
  38      INNER JOIN x4dat.aliases                   AS aa ON (aa.type = 'a_vie' AND aa.id = a.user_id)
  39      INNER JOIN groupex.evenements_items        AS ei ON (e.eid = ei.eid)
  40       LEFT JOIN groupex.evenements_participants AS ep ON(e.eid = ep.eid AND ei.item_id = ep.item_id)
  41       LEFT JOIN virtual AS al ON(al.type = 'evt' AND al.alias = CONCAT(short_name, {?}))
  42       LEFT JOIN virtual AS pl ON(pl.type = 'evt' AND pl.alias = CONCAT(short_name, {?}))
  43           WHERE e.eid = {?} AND ei.item_id = {?} AND e.asso_id = {?}
  44        GROUP BY ei.item_id",
  45        '-absents@'.$globals->xnet->evts_domain,
  46        '-participants@'.$globals->xnet->evts_domain,
  47        $eid, $item_id ? $item_id : 1, $globals->asso('id'));
  48
  49     $evt = $res->fetchOneAssoc();
  50
  51     if (!$evt) {
  52         return null;
  53     }
  54
  55     // smart calculation of the total number
  56     if (!$item_id) {
  57         $res = XDB::query(
  58                "SELECT MAX(nb)
  59                   FROM groupex.evenements              AS e
  60             INNER JOIN groupex.evenements_items        AS ei ON (e.eid = ei.eid)
  61              LEFT JOIN groupex.evenements_participants AS ep
  62                        ON (e.eid = ep.eid AND ei.item_id = ep.item_id)
  63                  WHERE e.eid = {?}
  64               GROUP BY ep.uid", $eid);
  65         $evt['nb_tot'] = array_sum($res->fetchColumn());
  66         $evt['titre'] = '';
  67         $evt['item_id'] = 0;
  68     }
  69
  70     $res = XDB::query(
  71         "SELECT titre, details, montant, ei.item_id, nb
  72            FROM groupex.evenements_items        AS ei
  73       LEFT JOIN groupex.evenements_participants AS ep
  74                 ON (ep.eid = ei.eid AND ep.item_id = ei.item_id AND uid = {?})
  75           WHERE ei.eid = {?}",
  76             S::v('uid'), $evt['eid']);
  77     $evt['moments'] = $res->fetchAllAssoc();
  78
  79     $evt['topay'] = 0;
  80     foreach ($evt['moments'] as $m) {
  81         $evt['topay'] += $m['nb'] * $m['montant'];
  82     }
  83
  84     $req = XDB::query(
  85         "SELECT montant
  86            FROM {$globals->money->mpay_tprefix}transactions AS t
  87          WHERE ref = {?} AND uid = {?}", $evt['paiement_id'], S::v('uid'));
  88     $montants = $req->fetchColumn();
  89
  90     $evt['paid'] = 0;
  91     foreach ($montants as $m) {
  92         $p = strtr(substr($m, 0, strpos($m, 'EUR')), ',', '.');
  93         $evt['paid'] += trim($p);
  94     }
  95
  96     return $evt;
  97 }
  98
  99 // }}}
 100
 101 // {{{ function get_event_participants()
 102 function get_event_participants($evt, $item_id, $tri, $limit = '') {
 103     global $globals;
 104
 105     if (Env::has('initiale')) {
 106         $where = 'AND IF(u.nom IS NULL, m.nom,
 107                          IF(u.nom_usage<>"", u.nom_usage, u.nom))
 108                   LIKE "'.addslashes(Env::get('initiale')).'%"';
 109     } else {
 110         $where = '';
 111     }
 112
 113     $eid    = $evt['eid'];
 114     $money  = $evt['money'] && may_update();
 115     $pay_id = $evt['paiement_id'];
 116
 117     $query =
 118         "SELECT  IF(u.nom IS NULL,m.nom,IF(u.nom_usage<>'', u.nom_usage, u.nom)) AS nom,
 119                    IF(u.nom IS NULL,m.prenom,u.prenom) AS prenom,
 120                    IF(u.nom IS NULL,'extérieur',u.promo) AS promo,
 121                    IF(u.nom IS NULL,m.email,a.alias) AS email,
 122                    IF(u.nom IS NULL,0,FIND_IN_SET('femme', u.flags)) AS femme,
 123                    m.perms='admin' AS admin,
 124                    NOT(u.nom IS NULL) AS x,
 125                    ep.uid, ep.paid, SUM(nb) AS nb
 126                FROM  groupex.evenements_participants AS ep
 127          INNER JOIN  groupex.evenements AS e ON (ep.eid = e.eid)
 128           LEFT JOIN  groupex.membres AS m ON ( ep.uid = m.uid AND e.asso_id = m.asso_id)
 129           LEFT JOIN  auth_user_md5   AS u ON ( u.user_id = ep.uid )
 130           LEFT JOIN  aliases         AS a ON ( a.id = ep.uid AND a.type='a_vie' )
 131               WHERE  ep.eid = {?}
 132                     ".(($item_id)?" AND item_id = $item_id":"")."
 133                     $where
 134            GROUP BY  ep.uid
 135            ORDER BY  $tri $limit";
 136
 137     if ($item_id) {
 138         $res = XDB::query($query, $eid);
 139         return $res->fetchAllAssoc();
 140     }
 141
 142     $res = XDB::iterator($query, $eid);
 143     $tab = array();
 144     $user = 0;
 145
 146     while ($u = $res->next()) {
 147         $u['montant'] = 0;
 148         if ($money && $pay_id) {
 149             $res_ = XDB::query(
 150                 "SELECT montant
 151                    FROM {$globals->money->mpay_tprefix}transactions AS t
 152                   WHERE ref = {?} AND uid = {?}",
 153                 $pay_id, $u['uid']);
 154             $montants = $res_->fetchColumn();
 155             foreach ($montants as $m) {
 156                     $p = strtr(substr($m, 0, strpos($m, "EUR")), ",", ".");
 157                     $u['paid'] += trim($p);
 158             }
 159         }
 160         $res_ = XDB::iterator(
 161             "SELECT ep.nb, ep.item_id, ei.montant
 162                FROM groupex.evenements_participants AS ep
 163          INNER JOIN groupex.evenements_items AS ei ON (ei.eid = ep.eid AND ei.item_id = ep.item_id)
 164               WHERE ep.eid = {?} AND ep.uid = {?}",
 165             $eid, $u['uid']);
 166         while ($i = $res_->next()) {
 167             $u[$i['item_id']] = $i['nb'];
 168             $u['montant'] += $i['montant']*$i['nb'];
 169         }
 170         $tab[] = $u;
 171     }
 172     return $tab;
 173 }
 174 // }}}
 175
 176 //  {{{ function subscribe_lists_event()
 177 function subscribe_lists_event($participate, $uid, $evt) {
 178     global $globals,$page;
 179
 180     $participant_list = $evt['participant_list'];
 181     $absent_list      = $evt['absent_list'];
 182
 183     $email = S::v('forlife');
 184
 185     if ($email) {
 186         $email .= '@'.$globals->mail->domain;
 187     } else {
 188         $res = XDB::query("SELECT email FROM groupex.membres WHERE uid = {?} AND asso_id = {?}", S::v('uid'), $globals->asso('id'));
 189         $email = $res->fetchOneCell();
 190     }
 191
 192     $subscribe = $participate ? $participant_list : (is_member()?$absent_list:0);
 193     $unsubscri = $participate ? $absent_list : $participant_list;
 194
 195     if ($subscribe) {
 196         XDB::execute(
 197             "REPLACE INTO virtual_redirect VALUES({?},{?})",
 198                  $subscribe, $email);
 199     }
 200
 201     if ($unsubscri) {
 202         XDB::execute(
 203             "DELETE FROM virtual_redirect WHERE vid = {?} AND redirect = {?}",
 204                 $unsubscri, $email);
 205     }
 206
 207 }
 208 // }}}
 209
 210 function event_change_shortname(&$page, $old, $new)
 211 {
 212     global $globals;
 213
 214     // Quelques vérifications sur l'alias (caractères spéciaux)
 215     if ($old && !preg_match( "/^[a-zA-Z0-9\-.]{3,20}$/", $old)) {
 216         $page->trig("Le raccourci demandé n'est pas valide.
 217                     Vérifie qu'il comporte entre 3 et 20 caractères
 218                     et qu'il ne contient que des lettres non accentuées,
 219                     des chiffres ou les caractères - et .");
 220         return $old;
 221     }
 222
 223     //vérifier que l'alias n'est pas déja pris
 224     if ($new && $old != $new) {
 225         $res = XDB::query('SELECT COUNT(*) FROM virtual
 226                                     WHERE alias LIKE {?}',
 227                                     $new.'-absents@%');
 228         if ($res->fetchOneCell() > 0) {
 229             $page->trig("Le raccourci demandé est déjà utilisé. Choisis en un autre.");
 230             return $old;
 231         }
 232     }
 233
 234     if ($old == $new) {
 235         return $new;
 236     }
 237
 238     if ($old && $new) {
 239         // if had a previous shortname change the old lists
 240         foreach (array('-absents@', '-participants@') as $v) {
 241             $v .= $globals->xnet->evts_domain;
 242             XDB::execute("UPDATE virtual SET alias = {?}
 243                                      WHERE type = 'evt' AND alias = {?}",
 244                                      $new.$v, $old.$v);
 245         }
 246
 247         return $new;
 248     }
 249
 250     if (!$old && $new) {
 251         // if we have a first new short_name create the lists
 252
 253         XDB::execute("INSERT INTO virtual SET type = 'evt', alias = {?}",
 254                 $new.'-participants@'.$globals->xnet->evts_domain);
 255
 256         $lastid = mysql_insert_id();
 257         XDB::execute(
 258           "INSERT INTO virtual_redirect (
 259                 SELECT {?} AS vid, IF(u.nom IS NULL, m.email, CONCAT(a.alias, {?})) AS redirect
 260                   FROM groupex.evenements_participants AS ep
 261              LEFT JOIN groupex.membres AS m ON (ep.uid = m.uid)
 262              LEFT JOIN auth_user_md5   AS u ON (u.user_id = ep.uid)
 263              LEFT JOIN aliases         AS a ON (a.id = ep.uid AND a.type = 'a_vie')
 264                  WHERE ep.eid = {?}
 265               GROUP BY ep.uid)",
 266               $lastid, '@'.$globals->mail->domain, $eid);
 267
 268         XDB::execute("INSERT INTO virtual SET type = 'evt', alias = {?}",
 269                 $new.'-absents@'.$globals->xnet->evts_domain);
 270
 271         $lastid = mysql_insert_id();
 272         XDB::execute("INSERT INTO virtual_redirect (
 273             SELECT {?} AS vid, IF(u.nom IS NULL, m.email, CONCAT(a.alias, {?})) AS redirect
 274                   FROM groupex.membres AS m
 275              LEFT JOIN groupex.evenements_participants AS ep ON (ep.uid = m.uid)
 276              LEFT JOIN auth_user_md5   AS u ON (u.user_id = m.uid)
 277              LEFT JOIN aliases         AS a ON (a.id = m.uid AND a.type = 'a_vie')
 278                  WHERE m.asso_id = {?} AND ep.uid IS NULL
 279               GROUP BY m.uid)",
 280              $lastid, "@".$globals->mail->domain, $globals->asso('id'));
 281
 282         return $new;
 283     }
 284
 285     if ($old && !$new) {
 286         // if we delete the old short name, delete the lists
 287         foreach (array('-absents@', '-participants@') as $v) {
 288             $v .= $globals->xnet->evts_domain;
 289             XDB::execute("DELETE virtual, virtual_redirect FROM virtual
 290                                  LEFT JOIN virtual_redirect USING(vid)
 291                                      WHERE virtual.alias = {?}",
 292                                    $infos['short_name'].$v);
 293         }
 294         return $new;
 295     }
 296
 297     // cannot happen
 298     return $old;
 299 }
 300
 301 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker:
 302 ?>