classes/registration/RegistrationDAO.inc.php

Go to the documentation of this file.
00001 <?php
00002 
00017 //$Id$
00018 
00019 
00020 import('registration.Registration');
00021 import('registration.RegistrationType');
00022 
00023 define('REGISTRATION_DATE_REGISTERED', 0x01);
00024 define('REGISTRATION_DATE_PAID', 0x02);
00025 
00026 define('REGISTRATION_USER',      0x01);
00027 define('REGISTRATION_MEMBERSHIP',   0x02);
00028 define('REGISTRATION_DOMAIN',    0x03);
00029 define('REGISTRATION_IP_RANGE',     0x04);
00030 
00031 class RegistrationDAO extends DAO {
00037    function &getRegistration($registrationId) {
00038       $result =& $this->retrieve(
00039          'SELECT * FROM registrations WHERE registration_id = ?', $registrationId
00040       );
00041 
00042       $returner = null;
00043       if ($result->RecordCount() != 0) {
00044          $returner =& $this->_returnRegistrationFromRow($result->GetRowAssoc(false));
00045       }
00046 
00047       $result->Close();
00048       unset($result);
00049       return $returner;
00050    }
00051 
00057    function getRegistrationSchedConfId($registrationId) {
00058       $result =& $this->retrieve(
00059          'SELECT sched_conf_id FROM registrations WHERE registration_id = ?', $registrationId
00060       );
00061 
00062       $returner = isset($result->fields[0]) ? $result->fields[0] : 0;   
00063 
00064       $result->Close();
00065       unset($result);
00066 
00067       return $returner;
00068    }
00069 
00076    function getRegistrationIdByUser($userId, $schedConfId) {
00077       $result =& $this->retrieve(
00078          'SELECT registration_id
00079             FROM registrations
00080             WHERE user_id = ?
00081             AND sched_conf_id = ?',
00082          array(
00083             $userId,
00084             $schedConfId
00085          )
00086       );
00087 
00088       $returner = isset($result->fields[0]) ? $result->fields[0] : 0;   
00089 
00090       $result->Close();
00091       unset($result);
00092 
00093       return $returner;
00094    }
00095 
00101    function getRegistrationsByUser($userId, $rangeInfo = null) {
00102       $result =& $this->retrieveRange(
00103          'SELECT *
00104             FROM registrations
00105             WHERE user_id = ?',
00106          $userId,
00107          $rangeInfo
00108       );
00109 
00110       $returner = new DAOResultFactory($result, $this, '_returnRegistrationFromRow');
00111 
00112       return $returner;
00113    }
00114 
00115 
00122    function registrationExistsByUser($userId, $schedConfId) {
00123       $result =& $this->retrieve(
00124          'SELECT COUNT(*)
00125             FROM registrations
00126             WHERE user_id = ?
00127             AND   sched_conf_id = ?',
00128          array(
00129             $userId,
00130             $schedConfId
00131          )
00132       );
00133       $returner = isset($result->fields[0]) && $result->fields[0] != 0 ? true : false;
00134 
00135       $result->Close();
00136       unset($result);
00137 
00138       return $returner;
00139    }
00140 
00146    function &_returnRegistrationFromRow(&$row) {
00147       $registration = new Registration();
00148       $registration->setId($row['registration_id']);
00149       $registration->setSchedConfId($row['sched_conf_id']);
00150       $registration->setUserId($row['user_id']);
00151       $registration->setTypeId($row['type_id']);
00152       $registration->setDateRegistered($this->dateFromDB($row['date_registered']));
00153       $registration->setDatePaid($this->dateFromDB($row['date_paid']));
00154       $registration->setMembership($row['membership']);
00155       $registration->setDomain($row['domain']);
00156       $registration->setIPRange($row['ip_range']);
00157       $registration->setSpecialRequests($row['special_requests']);
00158 
00159       HookRegistry::call('RegistrationDAO::_returnRegistrationFromRow', array(&$registration, &$row));
00160 
00161       return $registration;
00162    }
00163 
00168    function _generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params) {
00169       $first_last = $this->_dataSource->Concat($prefix.'first_name', '\' \'', $prefix.'last_name');
00170       $first_middle_last = $this->_dataSource->Concat($prefix.'first_name', '\' \'', $prefix.'middle_name', '\' \'', $prefix.'last_name');
00171       $last_comma_first = $this->_dataSource->Concat($prefix.'last_name', '\', \'', $prefix.'first_name');
00172       $last_comma_first_middle = $this->_dataSource->Concat($prefix.'last_name', '\', \'', $prefix.'first_name', '\' \'', $prefix.'middle_name');
00173       if ($searchMatch === 'is') {
00174          $searchSql = " AND (LOWER({$prefix}last_name) = LOWER(?) OR LOWER($first_last) = LOWER(?) OR LOWER($first_middle_last) = LOWER(?) OR LOWER($last_comma_first) = LOWER(?) OR LOWER($last_comma_first_middle) = LOWER(?))";
00175       } elseif ($searchMatch === 'contains') {
00176          $searchSql = " AND (LOWER({$prefix}last_name) LIKE LOWER(?) OR LOWER($first_last) LIKE LOWER(?) OR LOWER($first_middle_last) LIKE LOWER(?) OR LOWER($last_comma_first) LIKE LOWER(?) OR LOWER($last_comma_first_middle) LIKE LOWER(?))";
00177          $search = '%' . $search . '%';
00178       } else { // $searchMatch === 'startsWith'
00179          $searchSql = " AND (LOWER({$prefix}last_name) LIKE LOWER(?) OR LOWER($first_last) LIKE LOWER(?) OR LOWER($first_middle_last) LIKE LOWER(?) OR LOWER($last_comma_first) LIKE LOWER(?) OR LOWER($last_comma_first_middle) LIKE LOWER(?))";
00180          $search = $search . '%';
00181       }
00182       $params[] = $params[] = $params[] = $params[] = $params[] = $search;
00183       return $searchSql;
00184    }
00185 
00191    function insertRegistration(&$registration) {
00192       $ret = $this->update(
00193          sprintf('INSERT INTO registrations
00194             (sched_conf_id, user_id, type_id, date_registered, date_paid, membership, domain, ip_range, special_requests)
00195             VALUES
00196             (?, ?, ?, %s, %s, ?, ?, ?, ?)',
00197             $this->dateToDB($registration->getDateRegistered()), $this->dateToDB($registration->getDatePaid())),
00198          array(
00199             $registration->getSchedConfId(),
00200             $registration->getUserId(),
00201             $registration->getTypeId(),
00202             $registration->getMembership(),
00203             $registration->getDomain(),
00204             $registration->getIPRange(),
00205             $registration->getSpecialRequests()
00206          )
00207       );
00208       $registration->setId($this->getInsertRegistrationId());
00209       return $registration->getId();
00210    }
00211 
00217    function updateRegistration(&$registration) {
00218       return $this->update(
00219          sprintf('UPDATE registrations
00220             SET
00221                sched_conf_id = ?,
00222                user_id = ?,
00223                type_id = ?,
00224                date_registered = %s,
00225                date_paid = %s,
00226                membership = ?,
00227                domain = ?,
00228                ip_range = ?,
00229                special_requests = ?
00230             WHERE registration_id = ?',
00231             $this->dateToDB($registration->getDateRegistered()), $this->dateToDB($registration->getDatePaid())),
00232          array(
00233             $registration->getSchedConfId(),
00234             $registration->getUserId(),
00235             $registration->getTypeId(),
00236             $registration->getMembership(),
00237             $registration->getDomain(),
00238             $registration->getIPRange(),
00239             $registration->getSpecialRequests(),
00240             $registration->getId()
00241          )
00242       );
00243    }
00244 
00250    function deleteRegistrationById($registrationId) {
00251       return $this->update(
00252          'DELETE FROM registrations WHERE registration_id = ?', $registrationId
00253       );
00254    }
00255 
00260    function deleteRegistrationsBySchedConf($schedConfId) {
00261       return $this->update(
00262          'DELETE FROM registrations WHERE sched_conf_id = ?', $schedConfId
00263       );
00264    }
00265 
00270    function deleteRegistrationsByUserId($userId) {
00271       return $this->update(
00272          'DELETE FROM registrations WHERE user_id = ?', $userId
00273       );
00274    }
00275 
00280    function deleteRegistrationByUserIdSchedConf($userId, $schedConfId) {
00281       return $this->update(
00282          'DELETE
00283             FROM registrations
00284             WHERE user_id = ?
00285             AND sched_conf_id = ?',
00286             array(
00287                $userId,
00288                $schedConfId
00289             )
00290       );
00291    }
00292 
00298    function deleteRegistrationByTypeId($registrationTypeId) {
00299       return $this->update(
00300          'DELETE FROM registrations WHERE type_id = ?', $registrationTypeId
00301          );
00302    }
00303 
00315    function &getRegistrationsBySchedConfId($schedConfId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
00316       $params = array($schedConfId);
00317       $searchSql = '';
00318 
00319       if (!empty($search)) switch ($searchField) {
00320          case REGISTRATION_USER:
00321             $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'u.', $params);
00322             break;
00323          case REGISTRATION_MEMBERSHIP:
00324             if ($searchMatch === 'is') {
00325                $searchSql = ' AND LOWER(r.membership) = LOWER(?)';
00326             } elseif ($searchMatch === 'contains') {
00327                $searchSql = ' AND LOWER(r.membership) LIKE LOWER(?)';
00328                $search = '%' . $search . '%';
00329             } else { // $searchMatch === 'startsWith'
00330                $searchSql = ' AND LOWER(r.membership) LIKE LOWER(?)';
00331                $search = $search . '%';
00332             }
00333             $params[] = $search;
00334             break;
00335          case REGISTRATION_DOMAIN:
00336             if ($searchMatch === 'is') {
00337                $searchSql = ' AND LOWER(r.domain) = LOWER(?)';
00338             } elseif ($searchMatch === 'contains') {
00339                $searchSql = ' AND LOWER(r.domain) LIKE LOWER(?)';
00340                $search = '%' . $search . '%';
00341             } else { // $searchMatch === 'startsWith'
00342                $searchSql = ' AND LOWER(r.domain) LIKE LOWER(?)';
00343                $search = $search . '%';
00344             }
00345             $params[] = $search;
00346             break;
00347          case REGISTRATION_IP_RANGE:
00348             if ($searchMatch === 'is') {
00349                $searchSql = ' AND LOWER(r.ip_range) = LOWER(?)';
00350             } elseif ($searchMatch === 'contains') {
00351                $searchSql = ' AND LOWER(r.ip_range) LIKE LOWER(?)';
00352                $search = '%' . $search . '%';
00353             } else { // $searchName === 'startsWith'
00354                $searchSql = ' AND LOWER(r.ip_range) LIKE LOWER(?)';
00355                $search = $search . '%';
00356             }
00357             $params[] = $search;
00358             break;
00359       }
00360 
00361       if (!empty($dateFrom) || !empty($dateTo)) switch($dateField) {
00362          case REGISTRATION_DATE_REGISTERED:
00363             if (!empty($dateFrom)) {
00364                $searchSql .= ' AND r.date_registered >= ' . $this->datetimeToDB($dateFrom);
00365             }
00366             if (!empty($dateTo)) {
00367                $searchSql .= ' AND r.date_registered <= ' . $this->datetimeToDB($dateTo);
00368             }
00369             break;
00370          case REGISTRATION_DATE_PAID:
00371             if (!empty($dateFrom)) {
00372                $searchSql .= ' AND r.date_paid >= ' . $this->datetimeToDB($dateFrom);
00373             }
00374             if (!empty($dateTo)) {
00375                $searchSql .= ' AND r.date_paid <= ' . $this->datetimeToDB($dateTo);
00376             }
00377             break;
00378       }
00379 
00380       $sql = 'SELECT r.*
00381             FROM
00382                registrations r,
00383                users u
00384             WHERE r.user_id = u.user_id
00385             AND sched_conf_id = ?';
00386  
00387       $result =& $this->retrieveRange(
00388          $sql . ' ' . $searchSql . ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : ''),
00389          count($params)===1?array_shift($params):$params,
00390          $rangeInfo
00391       );
00392 
00393       $returner = new DAOResultFactory($result, $this, '_returnRegistrationFromRow');
00394 
00395       return $returner;
00396    }
00397 
00405    function &getRegisteredUsers($schedConfId, $paid = true, $dbResultRange = null) {
00406       $result =& $this->retrieveRange(
00407          'SELECT DISTINCT u.*
00408          FROM  users u,
00409             registrations r
00410          WHERE u.user_id = r.user_id AND
00411             r.sched_conf_id = ?
00412             ' . ($paid?' AND r.date_paid IS NOT NULL':''),
00413          (int) $schedConfId,
00414          $dbResultRange
00415       );
00416 
00417       $userDao =& DAORegistry::getDAO('UserDAO');
00418       $returner = new DAOResultFactory($result, $userDao, '_returnUserFromRowWithData');
00419       return $returner;
00420    }
00421 
00428    function &getRegisteredUserCount($schedConfId, $paid = true) {
00429       $result =& $this->retrieve(
00430          'SELECT COUNT(DISTINCT u.user_id) AS user_count
00431          FROM  users u,
00432             registrations r
00433          WHERE u.user_id = r.user_id AND
00434             r.sched_conf_id = ?
00435             ' . ($paid?' AND r.date_paid IS NOT NULL':''),
00436          (int) $schedConfId
00437       );
00438 
00439       $returner = $result->fields['user_count'];
00440       $result->Close();
00441 
00442       return $returner;
00443    }
00444 
00453    function isValidRegistration($domain, $IP, $userId, $schedConfId) {
00454       if ($userId != null) {
00455          $valid = $this->isValidRegistrationByUser($userId, $schedConfId);
00456          if ($valid !== false) { return $valid; }
00457       }
00458 
00459       if ($domain != null) {
00460          $valid = $this->isValidRegistrationByDomain($domain, $schedConfId);
00461          if ($valid !== false) { return $valid; }
00462       }  
00463 
00464       if ($IP != null) {
00465          $valid = $this->isValidRegistrationByIP($IP, $schedConfId);
00466          if ($valid) { return $valid; }
00467       }
00468 
00469       return false;
00470    }
00471 
00478    function isValidRegistrationByUser($userId, $schedConfId) {
00479       $result =& $this->retrieve(
00480          'SELECT registrations.registration_id,
00481                registration_types.expiry_date
00482          FROM registrations, registration_types
00483          WHERE registrations.user_id = ?
00484          AND   registrations.sched_conf_id = ?
00485          AND   registrations.type_id = registration_types.type_id
00486          AND   date_paid IS NOT NULL',
00487          array(
00488             $userId,
00489             $schedConfId
00490          ));
00491 
00492       $returner = false;
00493 
00494       if ($result->RecordCount() != 0) {
00495          $expiryDate = $result->fields['expiry_date'];
00496          $registrationId = $result->fields['registration_id'];
00497 
00498          if ($expiryDate === null ||
00499             strtotime($this->datetimeFromDB($expiryDate)) > time()
00500          ) $returner = $registrationId;
00501       }
00502 
00503       $result->Close();
00504       unset($result);
00505 
00506       return $returner;
00507    }
00508 
00515    function isValidRegistrationByDomain($domain, $schedConfId) {
00516       $result =& $this->retrieve(
00517          'SELECT registrations.registration_id,
00518                registration_types.expiry_date,
00519                POSITION(UPPER(domain) IN UPPER(?)) AS domain_position
00520          FROM registrations, registration_types
00521          WHERE POSITION(UPPER(domain) IN UPPER(?)) != 0
00522          AND   domain != \'\'
00523          AND   registrations.sched_conf_id = ?
00524          AND   registrations.type_id = registration_types.type_id
00525          AND   registration_types.institutional = 1
00526          AND   date_paid IS NOT NULL',
00527          array(
00528             $domain,
00529             $domain,
00530             $schedConfId
00531          ));
00532 
00533       $returner = false;
00534 
00535       if ($result->RecordCount() != 0) {
00536          while (!$returner && !$result->EOF) {
00537             $expiryDate = $result->fields['expiry_date'];
00538             $posMatch = $result->fields['domain_position'];
00539             $registrationId = $result->fields['registration_id'];
00540 
00541             // Ensure we have a proper match (i.e. bar.com should not match foobar.com but should match foo.bar.com)
00542             if ( $posMatch > 1) {
00543                if ( substr($domain, $posMatch-2, 1) != '.') {
00544                   $result->moveNext();
00545                   continue;
00546                }
00547             }
00548 
00549             if ($expiryDate === null ||
00550                strtotime($this->datetimeFromDB($expiryDate)) > time()
00551             ) $returner = $registrationId;
00552 
00553             $result->moveNext();
00554          }
00555       }
00556 
00557       $result->Close();
00558       unset($result);
00559 
00560       // By default, not a valid registration
00561       return $returner;
00562    }
00563 
00570    function isValidRegistrationByIP($IP, $schedConfId) {
00571       $result =& $this->retrieve(
00572          'SELECT  r.registration_id,
00573                rt.expiry_date,
00574                r.ip_range 
00575          FROM registrations r, registration_types rt
00576          WHERE r.ip_range IS NOT NULL   
00577          AND   r.sched_conf_id = ?
00578          AND   r.type_id = rt.type_id
00579          AND   rt.institutional = 1
00580          AND   r.date_paid IS NOT NULL',
00581          $schedConfId
00582          );
00583 
00584       $returner = false;
00585 
00586       if ($result->RecordCount() != 0) {
00587          $matchFound = false;
00588 
00589          while (!$returner && !$result->EOF) {
00590             $ipRange = $result->fields['ip_range'];
00591 
00592             // Get all IPs and IP ranges
00593             $ipRanges = explode(REGISTRATION_IP_RANGE_SEPERATOR, $ipRange);
00594 
00595             // Check each IP and IP range
00596             while (list(, $curIPString) = each($ipRanges)) {
00597                // Parse and check single IP string
00598                if (strpos($curIPString, REGISTRATION_IP_RANGE_RANGE) === false) {
00599 
00600                   // Check for wildcards in IP
00601                   if (strpos($curIPString, REGISTRATION_IP_RANGE_WILDCARD) === false) {
00602 
00603                      // Check non-CIDR IP
00604                      if (strpos($curIPString, '/') === false) {
00605                         if (ip2long(trim($curIPString)) == ip2long($IP)) {
00606                            $matchFound = true;
00607                            break;
00608                         }
00609                      // Check CIDR IP
00610                      } else {
00611                         list($curIPString, $cidrMask) = explode('/', trim($curIPString));
00612                         $cidrMask = 0xffffffff << (32 - $cidrMask);
00613 
00614                         if ((ip2long($IP) & $cidrMask) == (ip2long($curIPString) & $cidrMask)) {
00615                            $matchFound = true;
00616                            break;
00617                         }
00618                      }
00619 
00620                   } else {
00621                      // Turn wildcard IP into IP range
00622                      $ipStart = sprintf('%u', ip2long(str_replace(REGISTRATION_IP_RANGE_WILDCARD, '0', trim($curIPString))));
00623                      $ipEnd = sprintf('%u', ip2long(str_replace(REGISTRATION_IP_RANGE_WILDCARD, '255', trim($curIPString)))); 
00624                      $IP = sprintf('%u', ip2long($IP)); 
00625 
00626                      if ($IP >= $ipStart && $IP <= $ipEnd) {
00627                         $matchFound = true;
00628                         break;
00629                      }
00630                   }
00631                // Parse and check IP range string
00632                } else {
00633                   list($ipStart, $ipEnd) = explode(REGISTRATION_IP_RANGE_RANGE, $curIPString);
00634 
00635                   // Replace wildcards in start and end of range
00636                   $ipStart = sprintf('%u', ip2long(str_replace(REGISTRATION_IP_RANGE_WILDCARD, '0', trim($ipStart))));
00637                   $ipEnd = sprintf('%u', ip2long(str_replace(REGISTRATION_IP_RANGE_WILDCARD, '255', trim($ipEnd))));
00638                   $IP = sprintf('%u', ip2long($IP)); 
00639 
00640                   if ($IP >= $ipStart && $IP <= $ipEnd) {
00641                      $matchFound = true;
00642                      break;
00643                   }
00644                }
00645 
00646             }
00647 
00648             if ($matchFound == true) {
00649                break;
00650             } else {
00651                $result->moveNext();
00652             }
00653          }
00654 
00655          // Found a match. Ensure registration is still valid
00656          if ($matchFound == true) {
00657             $registrationId = $result->fields['registration_id'];
00658             $expiryDate = $result->fields['expiry_date'];
00659 
00660             if ($expiryDate === null ||
00661                strtotime($this->datetimeFromDB($expiryDate)) > time()
00662             ) $returner = $registrationId;
00663          }
00664       }
00665 
00666       $result->Close();
00667       unset($result);
00668 
00669       return $returner;
00670    }
00671 
00676    function getInsertRegistrationId() {
00677       return $this->getInsertId('registrations', 'registration_id');
00678    }
00679 
00685    function getSortMapping($heading) {
00686       switch ($heading) {
00687          case 'user': return 'u.last_name';
00688          case 'type': return 'r.type_id';
00689          case 'registered': return 'r.date_registered';
00690          case 'paid': return 'r.date_paid';
00691          case 'id': return 'r.registration_id';
00692          default: return null;
00693       }
00694    }
00695 }
00696 
00697 ?>

Generated on 25 Jul 2013 for Open Conference Systems by  doxygen 1.4.7