classes/subscription/SubscriptionDAO.inc.php

<?php

// $Id: SubscriptionDAO.inc.php,v 1.30 2008/07/01 01:16:11 asmecher Exp $


import('subscription.Subscription');
import('subscription.SubscriptionType');

define('SUBSCRIPTION_DATE_START',   0x01);
define('SUBSCRIPTION_DATE_END',     0x02);
define('SUBSCRIPTION_DATE_BOTH', 0x03);

define('SUBSCRIPTION_USER',         0x01);
define('SUBSCRIPTION_MEMBERSHIP',   0x02);
define('SUBSCRIPTION_DOMAIN',    0x03);
define('SUBSCRIPTION_IP_RANGE',     0x04);

class SubscriptionDAO extends DAO {
   function &getSubscription($subscriptionId) {
      $result = &$this->retrieve(
         'SELECT * FROM subscriptions WHERE subscription_id = ?', $subscriptionId
      );

      $returner = null;
      if ($result->RecordCount() != 0) {
         $returner = &$this->_returnSubscriptionFromRow($result->GetRowAssoc(false));
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function getSubscriptionJournalId($subscriptionId) {
      $result = &$this->retrieve(
         'SELECT journal_id FROM subscriptions WHERE subscription_id = ?', $subscriptionId
      );

      $returner = isset($result->fields[0]) ? $result->fields[0] : false;  

      $result->Close();
      unset($result);

      return $returner;
   }

   function getSubscriptionIdByUser($userId, $journalId) {
      $result = &$this->retrieve(
         'SELECT subscription_id
            FROM subscriptions
            WHERE user_id = ?
            AND journal_id = ?',
         array(
            $userId,
            $journalId
         )
      );

      $returner = isset($result->fields[0]) ? $result->fields[0] : false;  

      $result->Close();
      unset($result);

      return $returner;
   }

   function subscriptionExistsByUser($userId, $journalId) {
      $result = &$this->retrieve(
         'SELECT COUNT(*)
            FROM subscriptions
            WHERE user_id = ?
            AND   journal_id = ?',
         array(
            $userId,
            $journalId
         )
      );

      $returner = isset($result->fields[0]) && $result->fields[0] != 0 ? true : false;

      $result->Close();
      unset($result);

      return $returner;
   }

   function &_returnSubscriptionFromRow(&$row) {
      $subscription = &new Subscription();
      $subscription->setSubscriptionId($row['subscription_id']);
      $subscription->setJournalId($row['journal_id']);
      $subscription->setUserId($row['user_id']);
      $subscription->setTypeId($row['type_id']);
      $subscription->setDateStart($this->dateFromDB($row['date_start']));
      $subscription->setDateEnd($this->dateFromDB($row['date_end']));
      $subscription->setMembership($row['membership']);
      $subscription->setDomain($row['domain']);
      $subscription->setIPRange($row['ip_range']);

      HookRegistry::call('SubscriptionDAO::_returnSubscriptionFromRow', array(&$subscription, &$row));

      return $subscription;
   }

   function _generateUserNameSearchSQL($search, $searchMatch, $prefix, &$params) {
      $first_last = $this->_dataSource->Concat($prefix.'first_name', '\' \'', $prefix.'last_name');
      $first_middle_last = $this->_dataSource->Concat($prefix.'first_name', '\' \'', $prefix.'middle_name', '\' \'', $prefix.'last_name');
      $last_comma_first = $this->_dataSource->Concat($prefix.'last_name', '\', \'', $prefix.'first_name');
      $last_comma_first_middle = $this->_dataSource->Concat($prefix.'last_name', '\', \'', $prefix.'first_name', '\' \'', $prefix.'middle_name');
      if ($searchMatch === 'is') {
         $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(?))";
      } else {
         $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(?))";
         $search = '%' . $search . '%';
      }
      $params[] = $params[] = $params[] = $params[] = $params[] = $search;
      return $searchSql;
   }

   function insertSubscription(&$subscription) {
      $returner = $this->update(
         sprintf('INSERT INTO subscriptions
            (journal_id, user_id, type_id, date_start, date_end, membership, domain, ip_range)
            VALUES
            (?, ?, ?, %s, %s, ?, ?, ?)',
            $this->dateToDB($subscription->getDateStart()), $this->dateToDB($subscription->getDateEnd())),
         array(
            $subscription->getJournalId(),
            $subscription->getUserId(),
            $subscription->getTypeId(),
            $subscription->getMembership(),
            $subscription->getDomain(),
            $subscription->getIPRange()
         )
      );

      $subscriptionId = $this->getInsertSubscriptionId();
      $subscription->setSubscriptionId($subscriptionId);

      if ($returner) {
         $this->insertSubscriptionIPRange($subscriptionId, $subscription->getIPRange());
      }

      return $subscriptionId;
   }

   function updateSubscription(&$subscription) {
      $subscriptionId = $subscription->getSubscriptionId();

      $returner = $this->update(
         sprintf('UPDATE subscriptions
            SET
               journal_id = ?,
               user_id = ?,
               type_id = ?,
               date_start = %s,
               date_end = %s,
               membership = ?,
               domain = ?,
               ip_range = ?
            WHERE subscription_id = ?',
            $this->dateToDB($subscription->getDateStart()), $this->dateToDB($subscription->getDateEnd())),
         array(
            $subscription->getJournalId(),
            $subscription->getUserId(),
            $subscription->getTypeId(),
            $subscription->getMembership(),
            $subscription->getDomain(),
            $subscription->getIPRange(),
            $subscriptionId
         )
      );

      if ($returner) {
         $this->deleteSubscriptionIPRangeBySubscriptionId($subscriptionId);
         $this->insertSubscriptionIPRange($subscriptionId, $subscription->getIPRange());
      }

      return $returner;
   }

   function renewSubscription(&$subscription){
      $subscriptionTypeDAO =& DAORegistry::getDAO('SubscriptionTypeDAO');
      $subscriptionType =& $subscriptionTypeDAO->getSubscriptionType($subscription->getTypeId());
      
      $duration = $subscriptionType->getDuration();
      $dateEnd = strtotime($subscription->getDateEnd());
      
      // if the subscription is expired, extend it to today + duration of subscription
      $time = time();
      if ($dateEnd < $time ) $dateEnd = $time;

      $subscription->setDateEnd($this->dateToDB(mktime(0, 0, 0, date("m", $dateEnd)+$duration, date("d", $dateEnd), date("Y", $dateEnd))));
      $this->updateSubscription($subscription);
   }

   function deleteSubscriptionById($subscriptionId) {
      $this->deleteSubscriptionIPRangeBySubscriptionId($subscriptionId);

      return $this->update(
         'DELETE FROM subscriptions WHERE subscription_id = ?', $subscriptionId
      );
   }

   function deleteSubscriptionsByJournal($journalId) {
      $result = &$this->retrieve(
         'SELECT subscription_id
          FROM   subscriptions
          WHERE  journal_id = ?',
          $journalId
      );

      if ($result->RecordCount() != 0) {
         while (!$result->EOF) {
            $subscriptionId = $result->fields[0];
            $this->deleteSubscriptionIPRangeBySubscriptionId($subscriptionId);
            $result->moveNext();
         }
      }

      $result->Close();
      unset($result);

      return $this->update(
         'DELETE FROM subscriptions WHERE journal_id = ?', $journalId
      );
   }

   function deleteSubscriptionsByUserId($userId) {
      $result = &$this->retrieve(
         'SELECT subscription_id
          FROM   subscriptions
          WHERE  user_id = ?',
          $userId
      );

      if ($result->RecordCount() != 0) {
         while (!$result->EOF) {
            $subscriptionId = $result->fields[0];
            $this->deleteSubscriptionIPRangeBySubscriptionId($subscriptionId);
            $result->moveNext();
         }
      }

      $result->Close();
      unset($result);

      return $this->update(
         'DELETE FROM subscriptions WHERE user_id = ?', $userId
      );
   }

   function deleteSubscriptionByTypeId($subscriptionTypeId) {
      $result = &$this->retrieve(
         'SELECT subscription_id
          FROM   subscriptions
          WHERE  type_id = ?',
          $subscriptionTypeId
      );

      if ($result->RecordCount() != 0) {
         while (!$result->EOF) {
            $subscriptionId = $result->fields[0];
            $this->deleteSubscriptionIPRangeBySubscriptionId($subscriptionId);
            $result->moveNext();
         }
      }

      $result->Close();
      unset($result);

      return $this->update(
         'DELETE FROM subscriptions WHERE type_id = ?', $subscriptionTypeId
      );
   }

   function &getSubscriptions($rangeInfo = null) {
      $result = &$this->retrieveRange(
         'SELECT s.* FROM subscriptions s, users u WHERE s.user_id = u.user_id ORDER BY u.last_name ASC, s.subscription_id', false, $rangeInfo
      );

      $returner = &new DAOResultFactory($result, $this, '_returnSubscriptionFromRow');

      return $returner;
   }

   function &getSubscriptionsByJournalId($journalId, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null) {

      $params = array($journalId);
      $searchSql = '';

      if (!empty($search)) switch ($searchField) {
         case SUBSCRIPTION_USER:
            $searchSql = $this->_generateUserNameSearchSQL($search, $searchMatch, 'u.', $params);
            break;
         case SUBSCRIPTION_MEMBERSHIP:
            if ($searchMatch === 'is') {
               $searchSql = ' AND LOWER(s.membership) = LOWER(?)';
            } else {
               $searchSql = ' AND LOWER(s.membership) LIKE LOWER(?)';
               $search = '%' . $search . '%';
            }
            $params[] = $search;
            break;
         case SUBSCRIPTION_DOMAIN:
            if ($searchMatch === 'is') {
               $searchSql = ' AND LOWER(s.domain) = LOWER(?)';
            } else {
               $searchSql = ' AND LOWER(s.domain) LIKE LOWER(?)';
               $search = '%' . $search . '%';
            }
            $params[] = $search;
            break;
         case SUBSCRIPTION_IP_RANGE:
            if ($searchMatch === 'is') {
               $searchSql = ' AND LOWER(s.ip_range) = LOWER(?)';
            } else {
               $searchSql = ' AND LOWER(s.ip_range) LIKE LOWER(?)';
               $search = '%' . $search . '%';
            }
            $params[] = $search;
            break;
      }

      if (!empty($dateFrom) || !empty($dateTo)) switch($dateField) {
         case SUBSCRIPTION_DATE_START:
            if (!empty($dateFrom)) {
               $searchSql .= ' AND s.date_start >= ' . $this->datetimeToDB($dateFrom);
            }
            if (!empty($dateTo)) {
               $searchSql .= ' AND s.date_start <= ' . $this->datetimeToDB($dateTo);
            }
            break;
         case SUBSCRIPTION_DATE_END:
            if (!empty($dateFrom)) {
               $searchSql .= ' AND s.date_end >= ' . $this->datetimeToDB($dateFrom);
            }
            if (!empty($dateTo)) {
               $searchSql .= ' AND s.date_end <= ' . $this->datetimeToDB($dateTo);
            }
            break;
      }

      $sql = 'SELECT s.*
            FROM
            subscriptions s,
            users u
            WHERE s.user_id = u.user_id
            AND journal_id = ?';
 
      $result = &$this->retrieveRange(
         $sql . ' ' . $searchSql . ' ORDER BY u.last_name ASC, s.subscription_id',
         count($params)===1?array_shift($params):$params,
         $rangeInfo
      );

      $returner = &new DAOResultFactory($result, $this, '_returnSubscriptionFromRow');

      return $returner;
   }

   function &getSubscriptionsByDateEnd($dateEnd, $journalId, $rangeInfo = null) {
      $dateEnd = explode('-', $dateEnd);

      $result = &$this->retrieveRange(
         'SELECT  s.*
         FROM  subscriptions s,
            users u
         WHERE u.user_id = s.user_id AND
            EXTRACT(YEAR FROM date_end) = ? AND
            EXTRACT(MONTH FROM date_end) = ? AND
            EXTRACT(DAY FROM date_end) = ? AND
            journal_id = ?
         ORDER BY u.last_name ASC, s.subscription_id',
         array(
            $dateEnd[0],
            $dateEnd[1],
            $dateEnd[2],
            $journalId
         ), $rangeInfo
      );

      $returner = &new DAOResultFactory($result, $this, '_returnSubscriptionFromRow');

      return $returner;
   }

   function insertSubscriptionIPRange($subscriptionId, $IP) {
      if (empty($IP)) {
         return true;
      }

      if (empty($subscriptionId)) {
         return false;
      }

      // Get all IPs and IP ranges
      $ipRanges = explode(SUBSCRIPTION_IP_RANGE_SEPERATOR, $IP);

      $returner = true;
      
      while (list(, $curIPString) = each($ipRanges)) {
         $ipStart = null;
         $ipEnd = null;

         // Parse and check single IP string
         if (strpos($curIPString, SUBSCRIPTION_IP_RANGE_RANGE) === false) {

            // Check for wildcards in IP
            if (strpos($curIPString, SUBSCRIPTION_IP_RANGE_WILDCARD) === false) {

               // Get non-CIDR IP
               if (strpos($curIPString, '/') === false) {
                  $ipStart = sprintf("%u", ip2long(trim($curIPString)));

               // Convert CIDR IP to IP range
               } else {
                  list($curIPString, $cidrBits) = explode('/', trim($curIPString));

                  if ($cidrBits == 0) {
                     $cidrMask = 0;
                  } else {
                     $cidrMask = (0xffffffff << (32 - $cidrBits));
                  }

                  $ipStart = sprintf('%u', ip2long($curIPString) & $cidrMask);

                  if ($cidrBits != 32) {
                     $ipEnd = sprintf('%u', ip2long($curIPString) | (~$cidrMask & 0xffffffff));
                  }
               }

            // Convert wildcard IP to IP range
            } else {
               $ipStart = sprintf('%u', ip2long(str_replace(SUBSCRIPTION_IP_RANGE_WILDCARD, '0', trim($curIPString))));
               $ipEnd = sprintf('%u', ip2long(str_replace(SUBSCRIPTION_IP_RANGE_WILDCARD, '255', trim($curIPString)))); 
            }

         // Convert wildcard IP range to IP range
         } else {
            list($ipStart, $ipEnd) = explode(SUBSCRIPTION_IP_RANGE_RANGE, $curIPString);

            // Replace wildcards in start and end of range
            $ipStart = sprintf('%u', ip2long(str_replace(SUBSCRIPTION_IP_RANGE_WILDCARD, '0', trim($ipStart))));
            $ipEnd = sprintf('%u', ip2long(str_replace(SUBSCRIPTION_IP_RANGE_WILDCARD, '255', trim($ipEnd))));
         }

         // Insert IP or IP range
         if (($ipStart != null) && ($returner)) {
            $returner = $this->update(
               sprintf('INSERT INTO subscription_ip
                  (subscription_id, ip_start, ip_end)
                  VALUES
                  (?, ?, ?)'),
               array(
                  $subscriptionId,
                  $ipStart,
                  $ipEnd
               ) 
            );
         } else {
            $returner = false;
            break;
         }

      }

      return $returner;
   }

   function deleteSubscriptionIPRangeBySubscriptionId($subscriptionId) {
      return $this->update(
         'DELETE FROM subscription_ip WHERE subscription_id = ?', $subscriptionId
      );
   }

   function isValidSubscription($domain, $IP, $userId, $journalId, $check = SUBSCRIPTION_DATE_BOTH, $checkDate = null) {
      $valid = false;

      if ($userId != null) {
         $valid = $this->isValidSubscriptionByUser($userId, $journalId, $check, $checkDate);
         if ($valid !== false) { return $valid; }
      }

      if ($domain != null) {
         $valid = $this->isValidSubscriptionByDomain($domain, $journalId, $check, $checkDate);
         if ($valid !== false) { return $valid; }
      }  

      if ($IP != null) {
         $valid = $this->isValidSubscriptionByIP($IP, $journalId, $check, $checkDate);
         if ($valid !== false) { return $valid; }
      }

      return false;
    }

   function isValidSubscriptionByUser($userId, $journalId, $check = SUBSCRIPTION_DATE_BOTH, $checkDate = null) {
      $today = $this->dateToDB(Core::getCurrentDate()); 

      if ($checkDate == null) {
         $checkDate = $today;
      } else {
         $checkDate = $this->dateToDB($checkDate);
      }

      switch($check) {
         case SUBSCRIPTION_DATE_START:
            $sqlDate = sprintf('AND %s >= date_start AND %s >= date_start', $checkDate, $today);
            break;
         case SUBSCRIPTION_DATE_END:
            $sqlDate = sprintf('AND %s <= date_end AND %s >= date_start', $checkDate, $today);
            break;
         default:
            $sqlDate = sprintf('AND %s >= date_start AND %s <= date_end', $checkDate, $checkDate);
      }

      $result = &$this->retrieve(
         sprintf('SELECT subscription_id
            FROM subscriptions, subscription_types
            WHERE subscriptions.user_id = ?
            AND   subscriptions.journal_id = ? '
            . $sqlDate .
            ' AND   subscriptions.type_id = subscription_types.type_id
            AND   (subscription_types.format = ' . SUBSCRIPTION_TYPE_FORMAT_ONLINE .' OR subscription_types.format = ' . SUBSCRIPTION_TYPE_FORMAT_PRINT_ONLINE . ')'),
         array(
            $userId,
            $journalId
         ));

      if ($result->RecordCount() != 0) {
         $returner = $result->fields[0];
      } else {
         $returner = false;
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function isValidSubscriptionByDomain($domain, $journalId, $check = SUBSCRIPTION_DATE_BOTH, $checkDate = null) {
      $today = $this->dateToDB(Core::getCurrentDate()); 

      if ($checkDate == null) {
         $checkDate = $today;
      } else {
         $checkDate = $this->dateToDB($checkDate);
      }

      switch($check) {
         case SUBSCRIPTION_DATE_START:
            $sqlDate = sprintf('AND %s >= date_start AND %s >= date_start', $checkDate, $today);
            break;
         case SUBSCRIPTION_DATE_END:
            $sqlDate = sprintf('AND %s <= date_end AND %s >= date_start', $checkDate, $today);
            break;
         default:
            $sqlDate = sprintf('AND %s >= date_start AND %s <= date_end', $checkDate, $checkDate);
      }

      $result = &$this->retrieve(
         sprintf('SELECT subscription_id 
            FROM subscriptions, subscription_types
            WHERE POSITION(UPPER(LPAD(domain, LENGTH(domain)+1, \'.\')) IN UPPER(LPAD(?, LENGTH(?)+1, \'.\'))) != 0
            AND   domain != \'\'
            AND   subscriptions.journal_id = ? '
            . $sqlDate . 
            ' AND   subscriptions.type_id = subscription_types.type_id
            AND   subscription_types.institutional = 1
            AND   (subscription_types.format = ' . SUBSCRIPTION_TYPE_FORMAT_ONLINE .' OR subscription_types.format = ' . SUBSCRIPTION_TYPE_FORMAT_PRINT_ONLINE . ')'),
         array(
            $domain,
            $domain,
            $journalId
         ));

      if ($result->RecordCount() != 0) {
         $returner = $result->fields[0];
      } else {
         $returner = false;
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function isValidSubscriptionByIP($IP, $journalId, $check = SUBSCRIPTION_DATE_BOTH, $checkDate = null) {
      if (empty($IP) || empty($journalId)) {
         return false;
      }

      $IP = sprintf('%u', ip2long($IP));
      $today = $this->dateToDB(Core::getCurrentDate()); 

      if ($checkDate == null) {
         $checkDate = $today;
      } else {
         $checkDate = $this->dateToDB($checkDate);
      }

      switch($check) {
         case SUBSCRIPTION_DATE_START:
            $sqlDate = sprintf('AND %s >= date_start AND %s >= date_start', $checkDate, $today);
            break;
         case SUBSCRIPTION_DATE_END:
            $sqlDate = sprintf('AND %s <= date_end AND %s >= date_start', $checkDate, $today);
            break;
         default:
            $sqlDate = sprintf('AND %s >= date_start AND %s <= date_end', $checkDate, $checkDate);
      }

      $result = &$this->retrieve(
         sprintf('SELECT subscription_ip.subscription_id
            FROM subscription_ip, subscriptions, subscription_types
            WHERE ((ip_end IS NOT NULL
            AND   ? >= ip_start AND ? <= ip_end
            AND   subscription_ip.subscription_id = subscriptions.subscription_id   
            AND   subscriptions.journal_id = ? '
            . $sqlDate .
            ' AND   subscriptions.type_id = subscription_types.type_id
            AND   subscription_types.institutional = 1
            AND   (subscription_types.format = ' . SUBSCRIPTION_TYPE_FORMAT_ONLINE .' OR subscription_types.format = ' . SUBSCRIPTION_TYPE_FORMAT_PRINT_ONLINE . '))
            OR    (ip_end IS NULL
            AND   ? = ip_start
            AND   subscription_ip.subscription_id = subscriptions.subscription_id   
            AND   subscriptions.journal_id = ? '
            . $sqlDate .
            ' AND   subscriptions.type_id = subscription_types.type_id
            AND   subscription_types.institutional = 1
            AND   (subscription_types.format = ' . SUBSCRIPTION_TYPE_FORMAT_ONLINE .' OR subscription_types.format = ' . SUBSCRIPTION_TYPE_FORMAT_PRINT_ONLINE . ')))'),
         array (
               $IP,
               $IP,
               $journalId,
               $IP,
               $journalId
         ));

      if ($result->RecordCount() != 0) {
         $returner = $result->fields[0];
      } else {
         $returner = false;
      }

      $result->Close();
      unset($result);

      return $returner;
   }

   function getInsertSubscriptionId() {
      return $this->getInsertId('subscriptions', 'subscription_id');
   }
}

?>

---