File: /var/www/ojs/classes/subscription/InstitutionalSubscriptionDAO.inc.php
<?php
/**
* @file classes/subscription/InstitutionalSubscriptionDAO.inc.php
*
* Copyright (c) 2014-2021 Simon Fraser University
* Copyright (c) 2003-2021 John Willinsky
* Distributed under the GNU GPL v3. For full terms see the file docs/COPYING.
*
* @class InstitutionalSubscriptionDAO
* @ingroup subscription
* @see InstitutionalSubscription
*
* @brief Operations for retrieving and modifying InstitutionalSubscription objects.
*/
import('classes.subscription.SubscriptionDAO');
import('classes.subscription.InstitutionalSubscription');
define('SUBSCRIPTION_INSTITUTION_NAME', 0x20);
define('SUBSCRIPTION_DOMAIN', 0x21);
define('SUBSCRIPTION_IP_RANGE', 0x22);
class InstitutionalSubscriptionDAO extends SubscriptionDAO {
/**
* Retrieve an institutional subscription by subscription ID.
* @param $subscriptionId int Subscription ID
* @param $journalId int Journal ID
* @return InstitutionalSubscription
*/
function getById($subscriptionId, $journalId = null) {
$params = [(int) $subscriptionId];
if ($journalId) $params[] = (int) $journalId;
$result = $this->retrieve(
'SELECT s.*, iss.*
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
JOIN institutional_subscriptions iss ON (s.subscription_id = iss.subscription_id)
WHERE st.institutional = 1
AND s.subscription_id = ?
' . ($journalId?' AND s.journal_id = ?':''),
$params
);
$row = $result->current();
return $row ? $this->_fromRow((array) $row) : null;
}
/**
* Retrieve institutional subscriptions by user ID.
* @param $userId int
* @return object DAOResultFactory containing matching InstitutionalSubscriptions
*/
function getByUserId($userId, $rangeInfo = null) {
$result = $this->retrieveRange(
'SELECT s.*, iss.*
FROM subscriptions s
JOIN subscription_types st ON (st.type_id = s.type_id)
JOIN institutional_subscriptions iss ON (s.subscription_id = iss.subscription_id)
WHERE st.institutional = 1
AND s.user_id = ?',
[(int) $userId],
$rangeInfo
);
return new DAOResultFactory($result, $this, '_fromRow');
}
/**
* Retrieve institutional subscriptions by user ID and journal ID.
* @param $userId int
* @param $journalId int
* @param $rangeInfo RangeInfo
* @return object DAOResultFactory containing matching InstitutionalSubscriptions
*/
function getByUserIdForJournal($userId, $journalId, $rangeInfo = null) {
$result = $this->retrieveRange(
'SELECT s.*, iss.*
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
JOIN institutional_subscriptions iss ON (s.subscription_id = iss.subscription_id)
WHERE st.institutional = 1
AND s.user_id = ?
AND s.journal_id = ?',
[(int) $userId, (int) $journalId],
$rangeInfo
);
return new DAOResultFactory($result, $this, '_fromRow');
}
/**
* Retrieve institutional subscriptions by institution name.
* @param $institutionName string Institution name
* @param $journalId int Journal ID
* @param $exactMatch boolean True iff the match on institution name should be exact
* @param $rangeInfo RangeInfo
* @return object DAOResultFactory containing matching InstitutionalSubscriptions
*/
function getByInstitutionName($institutionName, $journalId, $exactMatch = true, $rangeInfo = null) {
$result = $this->retrieveRange(
'SELECT s.*, iss.*
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
JOIN institutional_subscriptions iss ON (s.subscription_id = iss.subscription_id)
WHERE AND st.institutional = 1' .
$exactMatch ? ' AND LOWER(iss.institution_name) = LOWER(?)'
: ' AND LOWER(iss.institution_name) LIKE LOWER(?)'
. ' AND s.journal_id = ?',
[$institutionName, (int) $journalId],
$rangeInfo
);
return new DAOResultFactory($result, $this, '_fromRow');
}
/**
* Return number of institutional subscriptions with given status for journal.
* @param status int
* @return int
*/
function getStatusCount($journalId, $status = null) {
$params = [(int) $journalId];
if ($status !== null) $params[] = (int) $status;
$result = $this->retrieve(
'SELECT COUNT(*) AS row_count
FROM subscriptions s,
JOIN subscription_types st ON (s.type_id = st.type_id)
WHERE st.institutional = 1 AND
s.journal_id = ?
' . ($status !== null?' AND s.status = ?':''),
$params
);
$row = $result->current();
return $row ? $row->row_count : 0;
}
/**
* Get the number of institutional subscriptions for a particular journal.
* @param $journalId int
* @return int
*/
function getSubscribedUserCount($journalId) {
return $this->getStatusCount($journalId);
}
/**
* Check if an institutional subscription exists for a given subscriptionId.
* @param $subscriptionId int Subscription ID
* @param $journalId int Optional journal ID
* @return boolean
*/
function subscriptionExists($subscriptionId, $journalId = null) {
$params = array((int) $subscriptionId);
if ($journalId) $params[] = (int) $journalId;
$result = $this->retrieve(
'SELECT COUNT(*) AS row_count
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
WHERE st.institutional = 1
AND s.subscription_id = ?
' . ($journalId?' AND s.journal_id = ?':''),
$params
);
$row = $result->current();
return $row ? (boolean) $row->row_count : false;
}
/**
* Check if an institutional subscription exists for a given user.
* @param $subscriptionId int Subscription ID
* @param $userId int User ID
* @return boolean
*/
function subscriptionExistsByUser($subscriptionId, $userId) {
$result = $this->retrieve(
'SELECT COUNT(*) AS row_count
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
WHERE st.institutional = 1
AND s.subscription_id = ?
AND s.user_id = ?',
[(int) $subscriptionId, (int) $userId]
);
$row = $result->current();
return $row ? (boolean) $row->row_count : false;
}
/**
* Check if an institutional subscription exists for a given user and journal.
* @param $userId int
* @param $journalId int
* @return boolean
*/
function subscriptionExistsByUserForJournal($userId, $journalId) {
$result = $this->retrieve(
'SELECT COUNT(*) AS row_count
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
WHERE st.institutional = 1
AND s.user_id = ?
AND s.journal_id = ?',
[(int) $userId, (int) $journalId]
);
$row = $result->current();
return $row ? (boolean) $row->row_count : false;
}
/**
* Check if an institutional subscription exists for a given institution name and journal.
* @param $institutionName string
* @param $journalId int
* @param $exactMatch boolean
* @return boolean
*/
function subscriptionExistsByInstitutionName($institutionName, $journalId, $exactMatch = true) {
$result = $this->retrieve(
'SELECT COUNT(*) AS row_count
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
JOIN institutional_subscriptions iss ON (s.subscription_id = iss.subscription_id)
WHERE st.institutional = 1' .
$exactMatch ? ' AND LOWER(iss.institution_name) = LOWER(?)'
: ' AND LOWER(iss.institution_name) LIKE LOWER(?)'
. ' AND s.journal_id = ?',
[$institutionName, (int) $journalId]
);
$row = $result->current();
return $row ? (boolean) $row->row_count : false;
}
/**
* Insert a new institutional subscription.
* @param $institutionalSubscription InstitutionalSubscription
* @return int
*/
function insertObject($institutionalSubscription) {
$subscriptionId = null;
if ($this->_insertObject($institutionalSubscription)) {
$subscriptionId = $institutionalSubscription->getId();
$this->update(
'INSERT INTO institutional_subscriptions
(subscription_id, institution_name, mailing_address, domain)
VALUES
(?, ?, ?, ?)',
[
(int) $subscriptionId,
$institutionalSubscription->getInstitutionName(),
$institutionalSubscription->getInstitutionMailingAddress(),
$institutionalSubscription->getDomain()
]
);
$this->_insertSubscriptionIPRanges($subscriptionId, $institutionalSubscription->getIPRanges());
}
return $subscriptionId;
}
/**
* Update an existing institutional subscription.
* @param $institutionalSubscription InstitutionalSubscription
* @return boolean
*/
function updateObject($institutionalSubscription) {
$this->_updateObject($institutionalSubscription);
$this->update(
'UPDATE institutional_subscriptions
SET institution_name = ?,
mailing_address = ?,
domain = ?
WHERE subscription_id = ?',
[
$institutionalSubscription->getInstitutionName(),
$institutionalSubscription->getInstitutionMailingAddress(),
$institutionalSubscription->getDomain(),
(int) $institutionalSubscription->getId()
]
);
$this->_deleteSubscriptionIPRanges($institutionalSubscription->getId());
$this->_insertSubscriptionIPRanges($institutionalSubscription->getId(), $institutionalSubscription->getIPRanges());
}
/**
* Delete an institutional subscription by subscription ID.
* @param $subscriptionId int
*/
function deleteById($subscriptionId, $journalId = null) {
if (!$this->subscriptionExists($subscriptionId, $journalId)) return;
$this->update('DELETE FROM subscriptions WHERE subscription_id = ?', [(int) $subscriptionId]);
$this->update('DELETE FROM institutional_subscriptions WHERE subscription_id = ?', [(int) $subscriptionId]);
$this->_deleteSubscriptionIPRanges($subscriptionId);
}
/**
* Delete institutional subscriptions by journal ID.
* @param $journalId int
*/
function deleteByJournalId($journalId) {
$result = $this->retrieve('SELECT s.subscription_id AS subscription_id FROM subscriptions s WHERE s.journal_id = ?', [(int) $journalId]);
foreach ($result as $row) {
$this->deleteById($row->subscription_id);
}
}
/**
* Delete institutional subscriptions by user ID.
* @param $userId int
*/
function deleteByUserId($userId) {
$result = $this->retrieve('SELECT s.subscription_id AS subscription_id FROM subscriptions s WHERE s.user_id = ?', [(int) $userId]);
foreach ($result as $row) {
$this->deleteById($row->subscription_id);
}
}
/**
* Delete institutional subscriptions by user ID and journal ID.
* @param $userId int User ID
* @param $journalId int Journal ID
*/
function deleteByUserIdForJournal($userId, $journalId) {
$result = $this->retrieve('SELECT s.subscription_id AS subscription_id FROM subscriptions s WHERE s.user_id = ? AND s.journal_id = ?', [(int) $userId, (int) $journalId]);
foreach ($result as $row) {
$this->deleteById($row->subscription_id);
}
}
/**
* Delete all institutional subscriptions by subscription type ID.
* @param $subscriptionTypeId int Subscription type ID
*/
function deleteByTypeId($subscriptionTypeId) {
$result = $this->retrieve('SELECT s.subscription_id AS subscription_id FROM subscriptions s WHERE s.type_id = ?', [(int) $subscriptionTypeId]);
foreach ($result as $row) {
$this->deleteById($row->subscription_id);
}
}
/**
* Retrieve all institutional subscriptions.
* @return object DAOResultFactory containing InstitutionalSubscriptions
*/
function getAll($rangeInfo = null) {
$result = $this->retrieveRange(
'SELECT s.*, iss.*
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
JOIN institutional_subscriptions iss ON (s.subscription_id = iss.subscription_id)
WHERE st.institutional = 1
ORDER BY iss.institution_name ASC, s.subscription_id',
[],
$rangeInfo
);
return new DAOResultFactory($result, $this, '_fromRow');
}
/**
* Retrieve institutional subscriptions matching a particular journal ID.
* @param $journalId int
* @param $status int
* @param $searchField int
* @param $searchMatch string "is" or "contains" or "startsWith"
* @param $search String to look in $searchField for
* @param $dateField int
* @param $dateFrom String date to search from
* @param $dateTo String date to search to
* @return object DAOResultFactory containing matching Subscriptions
*/
function getByJournalId($journalId, $status = null, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null) {
$userDao = DAORegistry::getDAO('UserDAO'); /* @var $userDao UserDAO */
$params = array_merge($userDao->getFetchParameters(), array((int) $journalId));
$ipRangeSql1 = $ipRangeSql2 = '';
$searchSql = $this->_generateSearchSQL($status, $searchField, $searchMatch, $search, $dateField, $dateFrom, $dateTo, $params);
if (!empty($search)) switch ($searchField) {
case SUBSCRIPTION_INSTITUTION_NAME:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(iss.institution_name) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(iss.institution_name) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(iss.institution_name) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
break;
case SUBSCRIPTION_DOMAIN:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(iss.domain) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(iss.domain) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(iss.domain) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
break;
case SUBSCRIPTION_IP_RANGE:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(isip.ip_string) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(isip.ip_string) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(isip.ip_string) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
$ipRangeSql1 = ', institutional_subscription_ip isip' ;
$ipRangeSql2 = ' AND s.subscription_id = isip.subscription_id';
break;
}
$result = $this->retrieveRange(
$sql = 'SELECT DISTINCT s.*, iss.institution_name, iss.mailing_address, iss.domain,
' . $userDao->getFetchColumns() .'
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
JOIN users u ON (s.user_id = u.user_id)
JOIN institutional_subscriptions iss ON (s.subscription_id = iss.subscription_id)
' . $userDao->getFetchJoins() . '
' . $ipRangeSql1 . '
WHERE st.institutional = 1
' . $ipRangeSql2 . '
AND s.journal_id = ?
' . $searchSql . ' ORDER BY iss.institution_name ASC, s.subscription_id',
$params,
$rangeInfo
);
return new DAOResultFactory($result, $this, '_fromRow', [], $sql, $params, $rangeInfo); // Counted in subscription grid paging
}
/**
* Check whether there is a valid institutional subscription for a given journal.
* @param $domain string
* @param $IP string
* @param $journalId int
* @param $check int Test using either start date, end date, or both (default)
* @param $checkDate date (YYYY-MM-DD) Use this date instead of current date
* @return int|false Found subscription ID, or false for none.
*/
function isValidInstitutionalSubscription($domain, $IP, $journalId, $check = SUBSCRIPTION_DATE_BOTH, $checkDate = null) {
if (empty($journalId) || (empty($domain) && empty($IP))) return false;
$today = $this->dateToDB(Core::getCurrentDate());
if ($checkDate == null) {
$checkDate = $today;
} else {
$checkDate = $this->dateToDB($checkDate);
}
switch($check) {
case SUBSCRIPTION_DATE_START:
$dateSql = sprintf('%s >= s.date_start AND %s >= s.date_start', $checkDate, $today);
break;
case SUBSCRIPTION_DATE_END:
$dateSql = sprintf('%s <= s.date_end AND %s >= s.date_start', $checkDate, $today);
break;
default:
$dateSql = sprintf('%s >= s.date_start AND %s <= s.date_end', $checkDate, $checkDate);
}
// Check if domain match
if (!empty($domain)) {
$result = $this->retrieve('
SELECT iss.subscription_id
FROM institutional_subscriptions iss
JOIN subscriptions s ON (iss.subscription_id = s.subscription_id)
JOIN subscription_types st ON (s.type_id = st.type_id)
WHERE POSITION(UPPER(LPAD(iss.domain, LENGTH(iss.domain)+1, \'.\')) IN UPPER(LPAD(?, LENGTH(?)+1, \'.\'))) != 0
AND iss.domain != \'\'
AND s.journal_id = ?
AND s.status = ' . SUBSCRIPTION_STATUS_ACTIVE . '
AND st.institutional = 1
AND (st.duration IS NULL OR (' . $dateSql . '))
AND (st.format = ' . SUBSCRIPTION_TYPE_FORMAT_ONLINE . '
OR st.format = ' . SUBSCRIPTION_TYPE_FORMAT_PRINT_ONLINE . ')',
[$domain, $domain, (int) $journalId]
);
$row = $result->current();
if ($row) return $row->subscription_id;
}
// Check for IP match
if (!empty($IP)) {
$IP = sprintf('%u', ip2long($IP));
$result = $this->retrieve(
'SELECT isip.subscription_id
FROM institutional_subscription_ip isip
JOIN subscriptions s ON (isip.subscription_id = s.subscription_id)
JOIN subscription_types st ON (s.type_id = st.type_id)
WHERE ((isip.ip_end IS NOT NULL
AND ? >= isip.ip_start AND ? <= isip.ip_end
AND s.journal_id = ?
AND s.status = ' . SUBSCRIPTION_STATUS_ACTIVE . '
AND st.institutional = 1
AND (st.duration IS NULL OR (' . $dateSql . '))
AND (st.format = ' . SUBSCRIPTION_TYPE_FORMAT_ONLINE . '
OR st.format = ' . SUBSCRIPTION_TYPE_FORMAT_PRINT_ONLINE . '))
OR (isip.ip_end IS NULL
AND ? = isip.ip_start
AND s.journal_id = ?
AND s.status = ' . SUBSCRIPTION_STATUS_ACTIVE . '
AND st.institutional = 1
AND (st.duration IS NULL OR (' . $dateSql . '))
AND (st.format = ' . SUBSCRIPTION_TYPE_FORMAT_ONLINE . '
OR st.format = ' . SUBSCRIPTION_TYPE_FORMAT_PRINT_ONLINE . ')))',
[$IP, $IP, (int) $journalId, $IP, (int) $journalId]
);
$row = $result->current();
if ($row) return $row->subscription_id;
}
return false;
}
/**
* Retrieve active institutional subscriptions matching a particular end date and journal ID.
* @param $dateEnd date (YYYY-MM-DD)
* @param $journalId int
* @return object DAOResultFactory containing matching InstitutionalSubscriptions
*/
function getByDateEnd($dateEnd, $journalId, $rangeInfo = null) {
$dateEnd = explode('-', $dateEnd);
$result = $this->retrieveRange(
'SELECT s.*, iss.*
FROM subscriptions s
JOIN subscription_types st ON (s.type_id = st.type_id)
JOIN institutional_subscriptions iss ON (s.subscription_id = iss.subscription_id)
WHERE s.status = ' . SUBSCRIPTION_STATUS_ACTIVE . '
AND st.institutional = 1
AND EXTRACT(YEAR FROM s.date_end) = ?
AND EXTRACT(MONTH FROM s.date_end) = ?
AND EXTRACT(DAY FROM s.date_end) = ?
AND s.journal_id = ?
ORDER BY iss.institution_name ASC, s.subscription_id',
array(
$dateEnd[0],
$dateEnd[1],
$dateEnd[2],
(int) $journalId
), $rangeInfo
);
return new DAOResultFactory($result, $this, '_fromRow');
}
/**
* Renew an institutional subscription by dateEnd + duration of subscription type
* if the institutional subscription is expired, renew to current date + duration
* @param $institutionalSubscription InstitutionalSubscription
* @return boolean
*/
function renewSubscription($institutionalSubscription) {
return $this->_renewSubscription($institutionalSubscription);
}
/**
* Generator function to create object.
* @return InstitutionalSubscription
*/
function newDataObject() {
return new InstitutionalSubscription();
}
/**
* Internal function to return an InstitutionalSubscription object from a row.
* @param $row array
* @return InstitutionalSubscription
*/
function _fromRow($row) {
$institutionalSubscription = parent::_fromRow($row);
$institutionalSubscription->setInstitutionName($row['institution_name']);
$institutionalSubscription->setInstitutionMailingAddress($row['mailing_address']);
$institutionalSubscription->setDomain($row['domain']);
$ipResult = $this->retrieve(
'SELECT ip_string
FROM institutional_subscription_ip
WHERE subscription_id = ?
ORDER BY institutional_subscription_ip_id ASC',
[(int) $institutionalSubscription->getId()]
);
$ipRanges = [];
foreach ($ipResult as $ipRow) {
$ipRanges[] = $ipRow->ip_string;
}
$institutionalSubscription->setIPRanges($ipRanges);
HookRegistry::call('InstitutionalSubscriptionDAO::_fromRow', array(&$institutionalSubscription, &$row));
return $institutionalSubscription;
}
/**
* Internal function to insert institutional subscription IP ranges.
* @param $subscriptionId int
* @param $ipRanges array
* @return boolean
*/
function _insertSubscriptionIPRanges($subscriptionId, $ipRanges) {
if (empty($ipRanges)) {
return true;
}
if (empty($subscriptionId)) {
return false;
}
$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($cidrIPString, $cidrBits) = explode('/', trim($curIPString));
if ($cidrBits == 0) {
$cidrMask = 0;
} else {
$cidrMask = (0xffffffff << (32 - $cidrBits));
}
$ipStart = sprintf('%u', ip2long($cidrIPString) & $cidrMask);
if ($cidrBits != 32) {
$ipEnd = sprintf('%u', ip2long($cidrIPString) | (~$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 = (boolean) $this->update(
'INSERT INTO institutional_subscription_ip
(subscription_id, ip_string, ip_start, ip_end)
VALUES
(?, ?, ?, ?)',
[
(int) $subscriptionId,
$curIPString,
$ipStart,
$ipEnd
]
);
} else {
$returner = false;
break;
}
}
return $returner;
}
/**
* Internal function to delete subscription ip ranges by subscription ID.
* @param $subscriptionId int
* @return boolean
*/
function _deleteSubscriptionIPRanges($subscriptionId) {
return $this->update(
'DELETE FROM institutional_subscription_ip WHERE subscription_id = ?', [(int) $subscriptionId]
);
}
}