File: /var/www/ojs/classes/subscription/SubscriptionDAO.inc.php
<?php
/**
* @file classes/subscription/SubscriptionDAO.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 SubscriptionDAO
* @ingroup subscription
* @see Subscription
*
* @brief Abstract class for retrieving and modifying subscriptions.
*/
import('classes.subscription.Subscription');
import('classes.subscription.SubscriptionType');
define('SUBSCRIPTION_USER', 0x01);
define('SUBSCRIPTION_MEMBERSHIP', 0x02);
define('SUBSCRIPTION_REFERENCE_NUMBER', 0x03);
define('SUBSCRIPTION_NOTES', 0x04);
abstract class SubscriptionDAO extends DAO {
/**
* Retrieve subscription by subscription ID.
* @param $subscriptionId int
* @return Subscription
*/
abstract function getById($subscriptionId);
/**
* Retrieve subscription journal ID by subscription ID.
* @param $subscriptionId int
* @return int|false
*/
function getSubscriptionJournalId($subscriptionId) {
$result = $this->retrieve(
'SELECT journal_id FROM subscriptions WHERE subscription_id = ?', [(int) $subscriptionId]
);
$row = $result->current();
return $row ? $row->journal_id : false;
}
/**
* Retrieve subscription status options as associative array.
* @return array
*/
static function getStatusOptions() {
return [
SUBSCRIPTION_STATUS_ACTIVE => 'subscriptions.status.active',
SUBSCRIPTION_STATUS_NEEDS_INFORMATION => 'subscriptions.status.needsInformation',
SUBSCRIPTION_STATUS_NEEDS_APPROVAL => 'subscriptions.status.needsApproval',
SUBSCRIPTION_STATUS_AWAITING_MANUAL_PAYMENT => 'subscriptions.status.awaitingManualPayment',
SUBSCRIPTION_STATUS_AWAITING_ONLINE_PAYMENT => 'subscriptions.status.awaitingOnlinePayment',
SUBSCRIPTION_STATUS_OTHER => 'subscriptions.status.other'
];
}
/**
* Return number of subscriptions with given status.
* @param status int
* @return int
*/
abstract function getStatusCount($status);
/**
* Check if subscription exists for a given subscriptionId.
* @param $subscriptionId int
* @return boolean
*/
abstract function subscriptionExists($subscriptionId);
/**
* Check if subscription exists given a user.
* @param $subscriptionId int
* @param $userId int
* @return boolean
*/
abstract function subscriptionExistsByUser($subscriptionId, $userId);
/**
* Check if subscription exists given a user and journal.
* @param $subscriptionId int
* @param $userId int
* @return boolean
*/
abstract function subscriptionExistsByUserForJournal($userId, $journalId);
/**
* Insert a subscription.
* @param $subscription Subscription
*/
abstract function insertObject($subscription);
/**
* Function to get the ID of the last inserted subscription.
* @return int
*/
function getInsertId() {
return $this->_getInsertId('subscriptions', 'subscription_id');
}
/**
* Update existing subscription.
* @param $subscription Subscription
* @return boolean
*/
abstract function updateObject($subscription);
/**
* Delete subscription by subscription ID.
* @param $subscriptionId int Subscription ID
* @param $journalId int Journal ID
*/
abstract function deleteById($subscriptionId, $journalId);
/**
* Delete subscriptions by journal ID.
* @param $journalId int
* @return boolean
*/
abstract function deleteByJournalId($journalId);
/**
* Delete subscriptions by user ID.
* @param $userId int
* @return boolean
*/
abstract function deleteByUserId($userId);
/**
* Delete all subscriptions by subscription type ID.
* @param $subscriptionTypeId int
* @return boolean
*/
abstract function deleteByTypeId($subscriptionTypeId);
/**
* Retrieve all subscriptions.
* @return object DAOResultFactory containing Subscriptions
*/
abstract function getAll($rangeInfo = null);
/**
* Retrieve 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
*/
abstract function getByJournalId($journalId, $status = null, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, $rangeInfo = null);
/**
* Retrieve subscriptions matching a particular end date and journal ID.
* @param $dateEnd string (YYYY-MM-DD)
* @param $journalId int
* @return object DAOResultFactory containing matching Subscriptions
*/
abstract function getByDateEnd($dateEnd, $journalId, $rangeInfo = null);
/**
* Function to renew a subscription by dateEnd + duration of subscription type
* if the subscription is expired, renew to current date + duration
* @param $subscription Subscription
* @return boolean
*/
abstract function renewSubscription($subscription);
/**
* Internal function to generate subscription based search query.
* @return string
*/
protected function _generateSearchSQL($status = null, $searchField = null, $searchMatch = null, $search = null, $dateField = null, $dateFrom = null, $dateTo = null, &$params = null) {
$searchSql = '';
if (!empty($search)) switch ($searchField) {
case IDENTITY_SETTING_GIVENNAME:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(COALESCE(ugl.setting_value,ugpl.setting_value)) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(COALESCE(ugl.setting_value,ugpl.setting_value)) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(COALESCE(ugl,ugpl)) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
break;
case IDENTITY_SETTING_FAMILYNAME:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(COALESCE(ufl.setting_value,ufpl.setting_value)) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(COALESCE(ufl.setting_value,ufpl.setting_value)) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(COALESCE(ufl.setting_value,ufpl.setting_value)) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
break;
case USER_FIELD_USERNAME:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(u.username) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(u.username) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(u.username) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
break;
case USER_FIELD_EMAIL:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(u.email) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(u.email) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(u.email) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
break;
case SUBSCRIPTION_MEMBERSHIP:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(s.membership) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(s.membership) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(s.membership) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
break;
case SUBSCRIPTION_REFERENCE_NUMBER:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(s.reference_number) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(s.reference_number) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(s.reference_number) LIKE LOWER(?)';
$search = $search . '%';
}
$params[] = $search;
break;
case SUBSCRIPTION_NOTES:
if ($searchMatch === 'is') {
$searchSql = ' AND LOWER(s.notes) = LOWER(?)';
} elseif ($searchMatch === 'contains') {
$searchSql = ' AND LOWER(s.notes) LIKE LOWER(?)';
$search = '%' . $search . '%';
} else { // $searchMatch === 'startsWith'
$searchSql = ' AND LOWER(s.notes) 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;
}
if (!empty($status)) {
$searchSql .= ' AND s.status = ' . (int) $status;
}
return $searchSql;
}
/**
* Generator function to create object.
* @return Subscription
*/
abstract function newDataObject();
/**
* Internal function to return a Subscription object from a row.
* @param $row array
* @return Subscription
*/
function _fromRow($row) {
$subscription = $this->newDataObject();
$subscription->setId($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->setStatus($row['status']);
$subscription->setMembership($row['membership']);
$subscription->setReferenceNumber($row['reference_number']);
$subscription->setNotes($row['notes']);
HookRegistry::call('SubscriptionDAO::_fromRow', [&$subscription, &$row]);
return $subscription;
}
/**
* Internal function to insert a new Subscription.
* @param $subscription Subscription
* @return int Subscription ID
*/
function _insertObject($subscription) {
$dateStart = $subscription->getDateStart();
$dateEnd = $subscription->getDateEnd();
$this->update(
sprintf('INSERT INTO subscriptions
(journal_id, user_id, type_id, date_start, date_end, status, membership, reference_number, notes)
VALUES
(?, ?, ?, %s, %s, ?, ?, ?, ?)',
$dateStart!==null?$this->dateToDB($dateStart):'null',
$dateEnd!==null?$this->datetimeToDB($dateEnd):'null'
), [
(int) $subscription->getJournalId(),
(int) $subscription->getUserId(),
(int) $subscription->getTypeId(),
(int) $subscription->getStatus(),
$subscription->getMembership(),
$subscription->getReferenceNumber(),
$subscription->getNotes()
]
);
$subscriptionId = $this->getInsertId();
$subscription->setId($subscriptionId);
return $subscriptionId;
}
/**
* Internal function to update a Subscription.
* @param $subscription Subscription
*/
function _updateObject($subscription) {
$dateStart = $subscription->getDateStart();
$dateEnd = $subscription->getDateEnd();
$this->update(
sprintf('UPDATE subscriptions
SET
journal_id = ?,
user_id = ?,
type_id = ?,
date_start = %s,
date_end = %s,
status = ?,
membership = ?,
reference_number = ?,
notes = ?
WHERE subscription_id = ?',
$dateStart!==null?$this->dateToDB($dateStart):'null',
$dateEnd!==null?$this->datetimeToDB($dateEnd):'null'
), [
(int) $subscription->getJournalId(),
(int) $subscription->getUserId(),
(int) $subscription->getTypeId(),
(int) $subscription->getStatus(),
$subscription->getMembership(),
$subscription->getReferenceNumber(),
$subscription->getNotes(),
(int) $subscription->getId()
]
);
}
/**
* Internal function to renew a subscription by dateEnd + duration of subscription type
* if the subscription is expired, renew to current date + duration
* @param $subscription Subscription
* @return boolean
*/
function _renewSubscription($subscription) {
if ($subscription->isNonExpiring()) return;
$subscriptionTypeDao = DAORegistry::getDAO('SubscriptionTypeDAO'); /* @var $subscriptionTypeDao SubscriptionTypeDAO */
$subscriptionType = $subscriptionTypeDao->getById($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(mktime(23, 59, 59, date("m", $dateEnd)+$duration, date("d", $dateEnd), date("Y", $dateEnd)));
$this->updateObject($subscription);
}
}