ocs  2.3.6
About: OCS (Open Conference System) is a Web publishing tool that will create a complete Web presence for your scholarly conference.
  Fossies Dox: ocs-2.3.6.tar.gz  ("inofficial" and yet experimental doxygen-generated source code documentation)  

RegistrationDAO.inc.php
Go to the documentation of this file.
1 <?php
2 
17 //$Id$
18 
19 
20 import('registration.Registration');
21 import('registration.RegistrationType');
22 
23 define('REGISTRATION_DATE_REGISTERED', 0x01);
24 define('REGISTRATION_DATE_PAID', 0x02);
25 
26 define('REGISTRATION_USER', 0x01);
27 define('REGISTRATION_MEMBERSHIP', 0x02);
28 define('REGISTRATION_DOMAIN', 0x03);
29 define('REGISTRATION_IP_RANGE', 0x04);
30 
31 class RegistrationDAO extends DAO {
37  function &getRegistration($registrationId) {
38  $result =& $this->retrieve(
39  'SELECT * FROM registrations WHERE registration_id = ?', $registrationId
40  );
41 
42  $returner = null;
43  if ($result->RecordCount() != 0) {
44  $returner =& $this->_returnRegistrationFromRow($result->GetRowAssoc(false));
45  }
46 
47  $result->Close();
48  unset($result);
49  return $returner;
50  }
51 
57  function getRegistrationSchedConfId($registrationId) {
58  $result =& $this->retrieve(
59  'SELECT sched_conf_id FROM registrations WHERE registration_id = ?', $registrationId
60  );
61 
62  $returner = isset($result->fields[0]) ? $result->fields[0] : 0;
63 
64  $result->Close();
65  unset($result);
66 
67  return $returner;
68  }
69 
76  function getRegistrationIdByUser($userId, $schedConfId) {
77  $result =& $this->retrieve(
78  'SELECT registration_id
79  FROM registrations
80  WHERE user_id = ?
81  AND sched_conf_id = ?',
82  array(
83  $userId,
84  $schedConfId
85  )
86  );
87 
88  $returner = isset($result->fields[0]) ? $result->fields[0] : 0;
89 
90  $result->Close();
91  unset($result);
92 
93  return $returner;
94  }
95 
101  function getRegistrationsByUser($userId, $rangeInfo = null) {
102  $result =& $this->retrieveRange(
103  'SELECT *
104  FROM registrations
105  WHERE user_id = ?',
106  $userId,
107  $rangeInfo
108  );
109 
110  $returner = new DAOResultFactory($result, $this, '_returnRegistrationFromRow');
111 
112  return $returner;
113  }
114 
115 
122  function registrationExistsByUser($userId, $schedConfId) {
123  $result =& $this->retrieve(
124  'SELECT COUNT(*)
125  FROM registrations
126  WHERE user_id = ?
127  AND sched_conf_id = ?',
128  array(
129  $userId,
130  $schedConfId
131  )
132  );
133  $returner = isset($result->fields[0]) && $result->fields[0] != 0 ? true : false;
134 
135  $result->Close();
136  unset($result);
137 
138  return $returner;
139  }
140 
146  function &_returnRegistrationFromRow(&$row) {
147  $registration = new Registration();
148  $registration->setId($row['registration_id']);
149  $registration->setSchedConfId($row['sched_conf_id']);
150  $registration->setUserId($row['user_id']);
151  $registration->setTypeId($row['type_id']);
152  $registration->setDateRegistered($this->dateFromDB($row['date_registered']));
153  $registration->setDatePaid($this->dateFromDB($row['date_paid']));
154  $registration->setMembership($row['membership']);
155  $registration->setDomain($row['domain']);
156  $registration->setIPRange($row['ip_range']);
157  $registration->setSpecialRequests($row['special_requests']);
158 
159  HookRegistry::call('RegistrationDAO::_returnRegistrationFromRow', array(&$registration, &$row));
160 
161  return $registration;
162  }
163 
168  function _generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params) {
169  $first_last = $this->_dataSource->Concat($prefix.'first_name', '\' \'', $prefix.'last_name');
170  $first_middle_last = $this->_dataSource->Concat($prefix.'first_name', '\' \'', $prefix.'middle_name', '\' \'', $prefix.'last_name');
171  $last_comma_first = $this->_dataSource->Concat($prefix.'last_name', '\', \'', $prefix.'first_name');
172  $last_comma_first_middle = $this->_dataSource->Concat($prefix.'last_name', '\', \'', $prefix.'first_name', '\' \'', $prefix.'middle_name');
173  if ($searchMatch === 'is') {
174  $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(?))";
175  } elseif ($searchMatch === 'contains') {
176  $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(?))";
177  $search = '%' . $search . '%';
178  } else { // $searchMatch === 'startsWith'
179  $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(?))";
180  $search = $search . '%';
181  }
182  $params[] = $params[] = $params[] = $params[] = $params[] = $search;
183  return $searchSql;
184  }
185 
191  function insertRegistration(&$registration) {
192  $ret = $this->update(
193  sprintf('INSERT INTO registrations
194  (sched_conf_id, user_id, type_id, date_registered, date_paid, membership, domain, ip_range, special_requests)
195  VALUES
196  (?, ?, ?, %s, %s, ?, ?, ?, ?)',
197  $this->dateToDB($registration->getDateRegistered()), $this->dateToDB($registration->getDatePaid())),
198  array(
199  $registration->getSchedConfId(),
200  $registration->getUserId(),
201  $registration->getTypeId(),
202  $registration->getMembership(),
203  $registration->getDomain(),
204  $registration->getIPRange(),
205  $registration->getSpecialRequests()
206  )
207  );
208  $registration->setId($this->getInsertRegistrationId());
209  return $registration->getId();
210  }
211 
217  function updateRegistration(&$registration) {
218  return $this->update(
219  sprintf('UPDATE registrations
220  SET
221  sched_conf_id = ?,
222  user_id = ?,
223  type_id = ?,
224  date_registered = %s,
225  date_paid = %s,
226  membership = ?,
227  domain = ?,
228  ip_range = ?,
229  special_requests = ?
230  WHERE registration_id = ?',
231  $this->dateToDB($registration->getDateRegistered()), $this->dateToDB($registration->getDatePaid())),
232  array(
233  $registration->getSchedConfId(),
234  $registration->getUserId(),
235  $registration->getTypeId(),
236  $registration->getMembership(),
237  $registration->getDomain(),
238  $registration->getIPRange(),
239  $registration->getSpecialRequests(),
240  $registration->getId()
241  )
242  );
243  }
244 
250  function deleteRegistrationById($registrationId) {
251  return $this->update(
252  'DELETE FROM registrations WHERE registration_id = ?', $registrationId
253  );
254  }
255 
260  function deleteRegistrationsBySchedConf($schedConfId) {
261  return $this->update(
262  'DELETE FROM registrations WHERE sched_conf_id = ?', $schedConfId
263  );
264  }
265 
270  function deleteRegistrationsByUserId($userId) {
271  return $this->update(
272  'DELETE FROM registrations WHERE user_id = ?', $userId
273  );
274  }
275 
280  function deleteRegistrationByUserIdSchedConf($userId, $schedConfId) {
281  return $this->update(
282  'DELETE
283  FROM registrations
284  WHERE user_id = ?
285  AND sched_conf_id = ?',
286  array(
287  $userId,
288  $schedConfId
289  )
290  );
291  }
292 
298  function deleteRegistrationByTypeId($registrationTypeId) {
299  return $this->update(
300  'DELETE FROM registrations WHERE type_id = ?', $registrationTypeId
301  );
302  }
303 
315  function &getRegistrationsBySchedConfId($schedConfId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
316  $params = array($schedConfId);
317  $searchSql = '';
318 
319  if (!empty($search)) switch ($searchField) {
320  case REGISTRATION_USER:
321  $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'u.', $params);
322  break;
324  if ($searchMatch === 'is') {
325  $searchSql = ' AND LOWER(r.membership) = LOWER(?)';
326  } elseif ($searchMatch === 'contains') {
327  $searchSql = ' AND LOWER(r.membership) LIKE LOWER(?)';
328  $search = '%' . $search . '%';
329  } else { // $searchMatch === 'startsWith'
330  $searchSql = ' AND LOWER(r.membership) LIKE LOWER(?)';
331  $search = $search . '%';
332  }
333  $params[] = $search;
334  break;
335  case REGISTRATION_DOMAIN:
336  if ($searchMatch === 'is') {
337  $searchSql = ' AND LOWER(r.domain) = LOWER(?)';
338  } elseif ($searchMatch === 'contains') {
339  $searchSql = ' AND LOWER(r.domain) LIKE LOWER(?)';
340  $search = '%' . $search . '%';
341  } else { // $searchMatch === 'startsWith'
342  $searchSql = ' AND LOWER(r.domain) LIKE LOWER(?)';
343  $search = $search . '%';
344  }
345  $params[] = $search;
346  break;
348  if ($searchMatch === 'is') {
349  $searchSql = ' AND LOWER(r.ip_range) = LOWER(?)';
350  } elseif ($searchMatch === 'contains') {
351  $searchSql = ' AND LOWER(r.ip_range) LIKE LOWER(?)';
352  $search = '%' . $search . '%';
353  } else { // $searchName === 'startsWith'
354  $searchSql = ' AND LOWER(r.ip_range) LIKE LOWER(?)';
355  $search = $search . '%';
356  }
357  $params[] = $search;
358  break;
359  }
360 
361  if (!empty($dateFrom) || !empty($dateTo)) switch($dateField) {
363  if (!empty($dateFrom)) {
364  $searchSql .= ' AND r.date_registered >= ' . $this->datetimeToDB($dateFrom);
365  }
366  if (!empty($dateTo)) {
367  $searchSql .= ' AND r.date_registered <= ' . $this->datetimeToDB($dateTo);
368  }
369  break;
371  if (!empty($dateFrom)) {
372  $searchSql .= ' AND r.date_paid >= ' . $this->datetimeToDB($dateFrom);
373  }
374  if (!empty($dateTo)) {
375  $searchSql .= ' AND r.date_paid <= ' . $this->datetimeToDB($dateTo);
376  }
377  break;
378  }
379 
380  $sql = 'SELECT r.*
381  FROM
382  registrations r,
383  users u
384  WHERE r.user_id = u.user_id
385  AND sched_conf_id = ?';
386 
387  $result =& $this->retrieveRange(
388  $sql . ' ' . $searchSql . ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : ''),
389  count($params)===1?array_shift($params):$params,
390  $rangeInfo
391  );
392 
393  $returner = new DAOResultFactory($result, $this, '_returnRegistrationFromRow');
394 
395  return $returner;
396  }
397 
405  function &getRegisteredUsers($schedConfId, $paid = true, $dbResultRange = null) {
406  $result =& $this->retrieveRange(
407  'SELECT DISTINCT u.*
408  FROM users u,
409  registrations r
410  WHERE u.user_id = r.user_id AND
411  r.sched_conf_id = ?
412  ' . ($paid?' AND r.date_paid IS NOT NULL':''),
413  (int) $schedConfId,
414  $dbResultRange
415  );
416 
417  $userDao =& DAORegistry::getDAO('UserDAO');
418  $returner = new DAOResultFactory($result, $userDao, '_returnUserFromRowWithData');
419  return $returner;
420  }
421 
428  function &getRegisteredUserCount($schedConfId, $paid = true) {
429  $result =& $this->retrieve(
430  'SELECT COUNT(DISTINCT u.user_id) AS user_count
431  FROM users u,
432  registrations r
433  WHERE u.user_id = r.user_id AND
434  r.sched_conf_id = ?
435  ' . ($paid?' AND r.date_paid IS NOT NULL':''),
436  (int) $schedConfId
437  );
438 
439  $returner = $result->fields['user_count'];
440  $result->Close();
441 
442  return $returner;
443  }
444 
453  function isValidRegistration($domain, $IP, $userId, $schedConfId) {
454  if ($userId != null) {
455  $valid = $this->isValidRegistrationByUser($userId, $schedConfId);
456  if ($valid !== false) { return $valid; }
457  }
458 
459  if ($domain != null) {
460  $valid = $this->isValidRegistrationByDomain($domain, $schedConfId);
461  if ($valid !== false) { return $valid; }
462  }
463 
464  if ($IP != null) {
465  $valid = $this->isValidRegistrationByIP($IP, $schedConfId);
466  if ($valid) { return $valid; }
467  }
468 
469  return false;
470  }
471 
478  function isValidRegistrationByUser($userId, $schedConfId) {
479  $result =& $this->retrieve(
480  'SELECT registrations.registration_id,
481  registration_types.expiry_date
482  FROM registrations, registration_types
483  WHERE registrations.user_id = ?
484  AND registrations.sched_conf_id = ?
485  AND registrations.type_id = registration_types.type_id
486  AND date_paid IS NOT NULL',
487  array(
488  $userId,
489  $schedConfId
490  ));
491 
492  $returner = false;
493 
494  if ($result->RecordCount() != 0) {
495  $expiryDate = $result->fields['expiry_date'];
496  $registrationId = $result->fields['registration_id'];
497 
498  if ($expiryDate === null ||
499  strtotime($this->datetimeFromDB($expiryDate)) > time()
500  ) $returner = $registrationId;
501  }
502 
503  $result->Close();
504  unset($result);
505 
506  return $returner;
507  }
508 
515  function isValidRegistrationByDomain($domain, $schedConfId) {
516  $result =& $this->retrieve(
517  'SELECT registrations.registration_id,
518  registration_types.expiry_date,
519  POSITION(UPPER(domain) IN UPPER(?)) AS domain_position
520  FROM registrations, registration_types
521  WHERE POSITION(UPPER(domain) IN UPPER(?)) != 0
522  AND domain != \'\'
523  AND registrations.sched_conf_id = ?
524  AND registrations.type_id = registration_types.type_id
525  AND registration_types.institutional = 1
526  AND date_paid IS NOT NULL',
527  array(
528  $domain,
529  $domain,
530  $schedConfId
531  ));
532 
533  $returner = false;
534 
535  if ($result->RecordCount() != 0) {
536  while (!$returner && !$result->EOF) {
537  $expiryDate = $result->fields['expiry_date'];
538  $posMatch = $result->fields['domain_position'];
539  $registrationId = $result->fields['registration_id'];
540 
541  // Ensure we have a proper match (i.e. bar.com should not match foobar.com but should match foo.bar.com)
542  if ( $posMatch > 1) {
543  if ( substr($domain, $posMatch-2, 1) != '.') {
544  $result->moveNext();
545  continue;
546  }
547  }
548 
549  if ($expiryDate === null ||
550  strtotime($this->datetimeFromDB($expiryDate)) > time()
551  ) $returner = $registrationId;
552 
553  $result->moveNext();
554  }
555  }
556 
557  $result->Close();
558  unset($result);
559 
560  // By default, not a valid registration
561  return $returner;
562  }
563 
570  function isValidRegistrationByIP($IP, $schedConfId) {
571  $result =& $this->retrieve(
572  'SELECT r.registration_id,
573  rt.expiry_date,
574  r.ip_range
575  FROM registrations r, registration_types rt
576  WHERE r.ip_range IS NOT NULL
577  AND r.sched_conf_id = ?
578  AND r.type_id = rt.type_id
579  AND rt.institutional = 1
580  AND r.date_paid IS NOT NULL',
581  $schedConfId
582  );
583 
584  $returner = false;
585 
586  if ($result->RecordCount() != 0) {
587  $matchFound = false;
588 
589  while (!$returner && !$result->EOF) {
590  $ipRange = $result->fields['ip_range'];
591 
592  // Get all IPs and IP ranges
593  $ipRanges = explode(REGISTRATION_IP_RANGE_SEPERATOR, $ipRange);
594 
595  // Check each IP and IP range
596  while (list(, $curIPString) = each($ipRanges)) {
597  // Parse and check single IP string
598  if (strpos($curIPString, REGISTRATION_IP_RANGE_RANGE) === false) {
599 
600  // Check for wildcards in IP
601  if (strpos($curIPString, REGISTRATION_IP_RANGE_WILDCARD) === false) {
602 
603  // Check non-CIDR IP
604  if (strpos($curIPString, '/') === false) {
605  if (ip2long(trim($curIPString)) == ip2long($IP)) {
606  $matchFound = true;
607  break;
608  }
609  // Check CIDR IP
610  } else {
611  list($curIPString, $cidrMask) = explode('/', trim($curIPString));
612  $cidrMask = 0xffffffff << (32 - $cidrMask);
613 
614  if ((ip2long($IP) & $cidrMask) == (ip2long($curIPString) & $cidrMask)) {
615  $matchFound = true;
616  break;
617  }
618  }
619 
620  } else {
621  // Turn wildcard IP into IP range
622  $ipStart = sprintf('%u', ip2long(str_replace(REGISTRATION_IP_RANGE_WILDCARD, '0', trim($curIPString))));
623  $ipEnd = sprintf('%u', ip2long(str_replace(REGISTRATION_IP_RANGE_WILDCARD, '255', trim($curIPString))));
624  $IP = sprintf('%u', ip2long($IP));
625 
626  if ($IP >= $ipStart && $IP <= $ipEnd) {
627  $matchFound = true;
628  break;
629  }
630  }
631  // Parse and check IP range string
632  } else {
633  list($ipStart, $ipEnd) = explode(REGISTRATION_IP_RANGE_RANGE, $curIPString);
634 
635  // Replace wildcards in start and end of range
636  $ipStart = sprintf('%u', ip2long(str_replace(REGISTRATION_IP_RANGE_WILDCARD, '0', trim($ipStart))));
637  $ipEnd = sprintf('%u', ip2long(str_replace(REGISTRATION_IP_RANGE_WILDCARD, '255', trim($ipEnd))));
638  $IP = sprintf('%u', ip2long($IP));
639 
640  if ($IP >= $ipStart && $IP <= $ipEnd) {
641  $matchFound = true;
642  break;
643  }
644  }
645 
646  }
647 
648  if ($matchFound == true) {
649  break;
650  } else {
651  $result->moveNext();
652  }
653  }
654 
655  // Found a match. Ensure registration is still valid
656  if ($matchFound == true) {
657  $registrationId = $result->fields['registration_id'];
658  $expiryDate = $result->fields['expiry_date'];
659 
660  if ($expiryDate === null ||
661  strtotime($this->datetimeFromDB($expiryDate)) > time()
662  ) $returner = $registrationId;
663  }
664  }
665 
666  $result->Close();
667  unset($result);
668 
669  return $returner;
670  }
671 
677  return $this->getInsertId('registrations', 'registration_id');
678  }
679 
685  function getSortMapping($heading) {
686  switch ($heading) {
687  case 'user': return 'u.last_name';
688  case 'type': return 'r.type_id';
689  case 'registered': return 'r.date_registered';
690  case 'paid': return 'r.date_paid';
691  case 'id': return 'r.registration_id';
692  default: return null;
693  }
694  }
695 }
696 
697 ?>
config TEMPLATE inc php
Definition: config.inc.php:4
Operations for retrieving and modifying objects from a database.
Definition: DAO.inc.php:30
& getRegistrationsBySchedConfId($schedConfId, $searchField=null, $searchMatch=null, $search=null, $dateField=null, $dateFrom=null, $dateTo=null, $rangeInfo=null, $sortBy=null, $sortDirection=SORT_DIRECTION_ASC)
& getRegisteredUsers($schedConfId, $paid=true, $dbResultRange=null)
& retrieve($sql, $params=false, $callHooks=true)
Definition: DAO.inc.php:62
_generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params)
insertRegistration(&$registration)
& _returnRegistrationFromRow(&$row)
getInsertId($table='', $id='', $callHooks=true)
Definition: DAO.inc.php:225
getRegistrationSchedConfId($registrationId)
const REGISTRATION_IP_RANGE_SEPERATOR
const REGISTRATION_DATE_REGISTERED
deleteRegistrationsByUserId($userId)
& getRegistration($registrationId)
const REGISTRATION_IP_RANGE_WILDCARD
dateToDB($d)
Definition: DAO.inc.php:267
isValidRegistrationByDomain($domain, $schedConfId)
const REGISTRATION_DATE_PAID
& retrieveRange($sql, $params=false, $dbResultRange=null, $callHooks=true)
Definition: DAO.inc.php:152
const REGISTRATION_MEMBERSHIP
datetimeFromDB($dt)
Definition: DAO.inc.php:276
isValidRegistrationByUser($userId, $schedConfId)
deleteRegistrationsBySchedConf($schedConfId)
const REGISTRATION_IP_RANGE_RANGE
Wrapper around ADORecordSet providing "factory" features for generating objects from DAOs.
isValidRegistration($domain, $IP, $userId, $schedConfId)
& getDAO($name, $dbconn=null)
getRegistrationIdByUser($userId, $schedConfId)
deleteRegistrationById($registrationId)
deleteRegistrationByTypeId($registrationTypeId)
getRegistrationsByUser($userId, $rangeInfo=null)
call($hookName, $args=null)
updateRegistration(&$registration)
const REGISTRATION_DOMAIN
Basic class describing a registration.
datetimeToDB($dt)
Definition: DAO.inc.php:258
update($sql, $params=false, $callHooks=true, $dieOnError=true)
Definition: DAO.inc.php:186
getDirectionMapping($direction)
Definition: DAO.inc.php:541
const REGISTRATION_USER
& getRegisteredUserCount($schedConfId, $paid=true)
isValidRegistrationByIP($IP, $schedConfId)
deleteRegistrationByUserIdSchedConf($userId, $schedConfId)
Operations for retrieving and modifying Registration objects.
const SORT_DIRECTION_ASC
Definition: DAO.inc.php:27
dateFromDB($d)
Definition: DAO.inc.php:285
const REGISTRATION_IP_RANGE
registrationExistsByUser($userId, $schedConfId)