public function index($subscriptionid, $userid = array())
{
$groups = $this->state->filter->status;
if (is_array($groups) && count($groups) === 1) {
if ($this->state->filter->status[0] == 'all') {
$groups = array('active', 'excluded', 'expired', 'pending', 'cancelled', 'hold', 'closed');
}
}
if (is_array($groups)) {
foreach ($groups as $k => $v) {
$groups[$k] = strtolower($v);
}
}
if (array_search('notconfig', $this->state->filter->status)) {
$set_group = 'notconfig';
} else {
$set_group = strtolower($this->state->filter->status[0]);
}
if (!empty($orderby)) {
if ($set_group == 'notconfig') {
$forder = array('name ASC', 'name DESC', 'lastname ASC', 'lastname DESC', 'username ASC', 'username DESC', 'signup_date ASC', 'signup_date DESC', 'lastpay_date ASC', 'lastpay_date DESC');
} else {
$forder = array('expiration ASC', 'expiration DESC', 'lastpay_date ASC', 'lastpay_date DESC', 'name ASC', 'name DESC', 'lastname ASC', 'lastname DESC', 'username ASC', 'username DESC', 'signup_date ASC', 'signup_date DESC', 'lastpay_date ASC', 'lastpay_date DESC', 'plan_name ASC', 'plan_name DESC', 'status ASC', 'status DESC', 'type ASC', 'type DESC');
}
if (!in_array($orderby, $forder)) {
$this->state->sort = 'name ASC';
}
}
// define displaying at html
$action = array();
switch ($set_group) {
case 'active':
$action[0] = 'active';
$action[1] = JText::_('AEC_HEAD_ACTIVE_SUBS');
break;
case 'excluded':
$action[0] = 'excluded';
$action[1] = JText::_('AEC_HEAD_EXCLUDED_SUBS');
break;
case 'expired':
$action[0] = 'expired';
$action[1] = JText::_('AEC_HEAD_EXPIRED_SUBS');
break;
case 'pending':
$action[0] = 'pending';
$action[1] = JText::_('AEC_HEAD_PENDING_SUBS');
break;
case 'cancelled':
$action[0] = 'cancelled';
$action[1] = JText::_('AEC_HEAD_CANCELLED_SUBS');
break;
case 'hold':
$action[0] = 'hold';
$action[1] = JText::_('AEC_HEAD_HOLD_SUBS');
break;
case 'closed':
$action[0] = 'closed';
$action[1] = JText::_('AEC_HEAD_CLOSED_SUBS');
break;
case 'manual':
case 'notconfig':
$action[0] = 'manual';
$action[1] = JText::_('AEC_HEAD_MANUAL_SUBS');
break;
}
$where = array();
$where_or = array();
$notconfig = false;
$planid = trim(aecGetParam('assign_planid', null));
$users_selected = is_array($subscriptionid) && count($subscriptionid) || is_array($userid) && count($userid);
if (!empty($planid) && $users_selected) {
$plan = new SubscriptionPlan();
$plan->load($planid);
if (!empty($subscriptionid)) {
foreach ($subscriptionid as $sid) {
$metaUser = new metaUser(false, $sid);
$metaUser->establishFocus($plan);
$metaUser->focusSubscription->applyUsage($planid, 'none', 1);
}
}
if (!empty($userid)) {
foreach ($userid as $uid) {
$metaUser = new metaUser($uid);
$metaUser->establishFocus($plan);
$metaUser->focusSubscription->applyUsage($planid, 'none', 1);
$subscriptionid[] = $metaUser->focusSubscription->id;
}
}
// Also show active users now
if (!in_array('active', $groups)) {
$groups[] = 'active';
}
}
$set_status = trim(aecGetParam('set_status', null));
$add_or_set_expiration = trim(aecGetParam('add_or_set_expiration', null));
$set_time = trim(aecGetParam('set_time', null));
$set_time_unit = trim(aecGetParam('set_time_unit', null));
if ((!empty($set_status) || !empty($add_or_set_expiration)) && is_array($subscriptionid) && count($subscriptionid) > 0) {
foreach ($subscriptionid as $k) {
$subscriptionHandler = new Subscription();
if (!empty($k)) {
$subscriptionHandler->load($k);
} else {
$subscriptionHandler->createNew($k, '', 1);
}
if (strcmp($set_status, 'now') === 0) {
$subscriptionHandler->expire();
if (!in_array('expired', $groups)) {
$groups[] = 'expired';
}
} elseif (strcmp($set_status, 'exclude') === 0) {
$subscriptionHandler->setStatus('Excluded');
if (!in_array('excluded', $groups)) {
$groups[] = 'excluded';
}
} elseif (strcmp($set_status, 'close') === 0) {
$subscriptionHandler->setStatus('Closed');
if (!in_array('closed', $groups)) {
$groups[] = 'closed';
}
} elseif (strcmp($set_status, 'hold') === 0) {
$subscriptionHandler->setStatus('Hold');
if (!in_array('hold', $groups)) {
$groups[] = 'hold';
}
} elseif (strcmp($set_status, 'include') === 0) {
$subscriptionHandler->setStatus('Active');
if (!in_array('active', $groups)) {
$groups[] = 'active';
}
} elseif (strcmp($set_status, 'lifetime') === 0) {
if (!$subscriptionHandler->isLifetime()) {
$subscriptionHandler->expiration = '9999-12-31 00:00:00';
$subscriptionHandler->lifetime = 1;
}
$subscriptionHandler->setStatus('Active');
if (!in_array('active', $groups)) {
$groups[] = 'active';
}
}
if ($set_status !== 'lifetime' && !empty($add_or_set_expiration) && !empty($set_time) && !empty($set_time_unit)) {
if ($add_or_set_expiration == 'set') {
$subscriptionHandler->setExpiration($set_time_unit, $set_time, 0);
$subscriptionHandler->lifetime = 0;
$subscriptionHandler->storeload();
if (!in_array('active', $groups)) {
$groups[] = 'active';
}
} elseif ($add_or_set_expiration == 'add') {
if ($subscriptionHandler->lifetime) {
$subscriptionHandler->setExpiration($set_time_unit, $set_time, 0);
} else {
$subscriptionHandler->setExpiration($set_time_unit, $set_time, 1);
}
$subscriptionHandler->lifetime = 0;
$subscriptionHandler->storeload();
if (!in_array('active', $groups)) {
$groups[] = 'active';
}
}
}
}
}
if (is_array($groups)) {
if (in_array('notconfig', $groups)) {
$notconfig = true;
$groups = array('notconfig');
} else {
if (in_array('excluded', $groups)) {
$where_or[] = "a.status = 'Excluded'";
}
if (in_array('expired', $groups)) {
$where_or[] = "a.status = 'Expired'";
}
if (in_array('active', $groups)) {
$where_or[] = "(a.status = 'Active' || a.status = 'Trial')";
}
if (in_array('pending', $groups)) {
$where_or[] = "a.status = 'Pending'";
}
if (in_array('cancelled', $groups)) {
$where_or[] = "a.status = 'Cancelled'";
}
if (in_array('hold', $groups)) {
$where_or[] = "a.status = 'Hold'";
}
if (in_array('closed', $groups)) {
$where_or[] = "a.status = 'Closed'";
}
}
}
if (isset($search) && $search != '') {
if ($notconfig) {
$where[] = "(username LIKE '%{$search}%' OR name LIKE '%{$search}%')";
} else {
$where[] = "(b.username LIKE '%{$search}%' OR b.name LIKE '%{$search}%')";
}
}
$group_plans = ItemGroupHandler::getChildren($this->state->filter->group, 'item');
if (!empty($this->state->filter->plan) || !empty($group_plans)) {
$plan_selection = array();
if (!empty($this->state->filter->plan)) {
$plan_selection = $this->state->filter->plan;
}
if (!empty($group_plans)) {
$plan_selection = array_merge($plan_selection, $group_plans);
}
if (empty($plan_selection[0])) {
unset($plan_selection[0]);
}
$plan_selection = array_unique($plan_selection);
if (!$notconfig && !empty($plan_selection)) {
$where[] = "a.plan IN (" . implode(',', $plan_selection) . ")";
}
}
// get the total number of records
if ($notconfig) {
$where[] = 'b.status is null';
$query = 'SELECT count(*)' . ' FROM #__users AS a' . ' LEFT JOIN #__acctexp_subscr AS b ON a.id = b.userid' . (count($where) ? ' WHERE ' . implode(' AND ', $where) : '');
} else {
$query = 'SELECT count(*)' . ' FROM #__acctexp_subscr AS a' . ' INNER JOIN #__users AS b ON a.userid = b.id';
if (count($where_or)) {
$where[] = count($where_or) ? '(' . implode(' OR ', $where_or) . ')' : '';
}
$query .= count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
}
$this->db->setQuery($query);
$nav = $this->getPagination($this->db->loadResult());
$orderby = $this->state->sort;
// get the subset (based on limits) of required records
if ($notconfig) {
$forder = array('name ASC', 'name DESC', 'lastname ASC', 'lastname DESC', 'username ASC', 'username DESC', 'signup_date ASC', 'signup_date DESC');
if (!in_array($this->state->sort, $forder)) {
$orderby = 'name ASC';
}
if (strpos($orderby, 'lastname') !== false) {
$orderby = str_replace('lastname', 'SUBSTRING_INDEX(name, \' \', -1)', $orderby);
}
$query = 'SELECT a.id, a.name, a.username, a.registerDate as signup_date' . ' FROM #__users AS a' . ' LEFT JOIN #__acctexp_subscr AS b ON a.id = b.userid' . (count($where) ? ' WHERE ' . implode(' AND ', $where) : '') . ' ORDER BY ' . str_replace('signup_date', 'registerDate', $orderby) . ' LIMIT ' . $nav->limitstart . ',' . $nav->limit;
if (strpos($orderby, 'SUBSTRING_INDEX') !== false) {
$orderby = str_replace('SUBSTRING_INDEX(name, \' \', -1)', 'lastname', $orderby);
}
} else {
if (strpos($orderby, 'lastname') !== false) {
$orderby = str_replace('lastname', 'SUBSTRING_INDEX(b.name, \' \', -1)', $orderby);
}
$query = 'SELECT a.*, b.name, b.username, b.email, c.name AS plan_name' . ' FROM #__acctexp_subscr AS a' . ' INNER JOIN #__users AS b ON a.userid = b.id' . ' LEFT JOIN #__acctexp_plans AS c ON a.plan = c.id' . (count($where) ? ' WHERE ' . implode(' AND ', $where) : '') . ' ORDER BY ' . $orderby . ' LIMIT ' . $nav->limitstart . ',' . $nav->limit;
if (strpos($orderby, 'SUBSTRING_INDEX') !== false) {
$orderby = str_replace('SUBSTRING_INDEX(b.name, \' \', -1)', 'lastname', $orderby);
}
}
$this->state->sort = $orderby;
$this->db->setQuery('SET SQL_BIG_SELECTS=1');
$this->db->query();
$this->db->setQuery($query);
$rows = $this->db->loadObjectList();
if ($this->db->getErrorNum()) {
echo $this->db->stderr();
return false;
}
$this->db->setQuery('SET SQL_BIG_SELECTS=0');
$this->db->query();
$processors = PaymentProcessorHandler::getObjectList(PaymentProcessorHandler::getProcessorList());
$procs = array('free' => 'Free', 'none' => 'None');
foreach ($processors as $processor) {
$procs[$processor->processor_name] = $processor->processor->info['longname'];
}
foreach ($rows as $k => $row) {
if (!isset($rows[$k]->type)) {
continue;
}
$rows[$k]->type = $procs[$rows[$k]->type];
}
// Get list of plans for filter
$query = 'SELECT `id`, `name`' . ' FROM #__acctexp_plans' . ' ORDER BY `ordering`';
$this->db->setQuery($query);
$db_plans = $this->db->loadObjectList();
$plans2[] = JHTML::_('select.option', '0', JText::_('BIND_USER'), 'id', 'name');
if (is_array($db_plans)) {
$plans2 = array_merge($plans2, $db_plans);
}
$lists['set_plan'] = JHTML::_('select.genericlist', $plans2, 'assign_planid', 'class="form-control inputbox" size="1"', 'id', 'name', 0);
$lists['filter_plan'] = '<select id="plan-filter-select" name="plan[]" multiple="multiple" size="5">';
foreach ($db_plans as $plan) {
$lists['filter_plan'] .= '<option value="' . $plan->id . '"' . (in_array($plan->id, $this->state->filter->plan) ? ' selected="selected"' : '') . '>' . $plan->name . '</option>';
}
$lists['filter_plan'] .= '</select>';
$grouplist = ItemGroupHandler::getTree();
$lists['filter_group'] = '<select id="group-filter-select" name="group[]" multiple="multiple" size="5">';
foreach ($grouplist as $glisti) {
if (defined('JPATH_MANIFESTS')) {
$lists['filter_group'] .= '<option value="' . $glisti[0] . '"' . (in_array($glisti[0], $this->state->filter->group) ? ' selected="selected"' : '') . '>' . str_replace(' ', ' ', $glisti[1]) . '</option>';
} else {
$lists['filter_group'] .= '<option value="' . $glisti[0] . '"' . (in_array($glisti[0], $this->state->filter->group) ? ' selected="selected"' : '') . '>' . $glisti[1] . '</option>';
}
}
$lists['filter_group'] .= '</select>';
$status = array('excluded' => JText::_('AEC_SEL_EXCLUDED'), 'pending' => JText::_('AEC_SEL_PENDING'), 'active' => JText::_('AEC_SEL_ACTIVE'), 'expired' => JText::_('AEC_SEL_EXPIRED'), 'closed' => JText::_('AEC_SEL_CLOSED'), 'cancelled' => JText::_('AEC_SEL_CANCELLED'), 'hold' => JText::_('AEC_SEL_HOLD'), 'notconfig' => JText::_('AEC_SEL_NOT_CONFIGURED'));
$lists['filter_status'] = '<select id="status-group-select" name="status[]" multiple="multiple" size="5">';
foreach ($status as $id => $txt) {
$lists['filter_status'] .= '<option value="' . $id . '"' . (in_array($id, $groups) ? ' selected="selected"' : '') . '>' . $txt . '</option>';
}
$lists['filter_status'] .= '</select>';
$group_selection = array();
$group_selection[] = JHTML::_('select.option', '', JText::_('Set Status'));
$group_selection[] = JHTML::_('select.option', 'now', JText::_('EXPIRE_NOW'));
$group_selection[] = JHTML::_('select.option', 'exclude', JText::_('EXPIRE_EXCLUDE'));
$group_selection[] = JHTML::_('select.option', 'lifetime', JText::_('AEC_CMN_LIFETIME'));
$group_selection[] = JHTML::_('select.option', 'include', JText::_('EXPIRE_INCLUDE'));
$group_selection[] = JHTML::_('select.option', 'close', JText::_('EXPIRE_CLOSE'));
$group_selection[] = JHTML::_('select.option', 'hold', JText::_('EXPIRE_HOLD'));
$lists['set_status'] = JHTML::_('select.genericlist', $group_selection, 'set_status', 'class="form-control inputbox" size="1"', 'value', 'text', "");
$group_selection = array();
$group_selection[] = JHTML::_('select.option', 'add', JText::_('Add'));
$group_selection[] = JHTML::_('select.option', 'set', JText::_('Set'));
$lists['add_or_set_expiration'] = JHTML::_('select.genericlist', $group_selection, 'add_or_set_expiration', 'class="form-control inputbox" size="1"', 'value', 'text', "");
// make the select list for first trial period units
$perunit[] = JHTML::_('select.option', 'D', JText::_('PAYPLAN_PERUNIT1'));
$perunit[] = JHTML::_('select.option', 'W', JText::_('PAYPLAN_PERUNIT2'));
$perunit[] = JHTML::_('select.option', 'M', JText::_('PAYPLAN_PERUNIT3'));
$perunit[] = JHTML::_('select.option', 'Y', JText::_('PAYPLAN_PERUNIT4'));
$lists['set_time_unit'] = JHTML::_('select.genericlist', $perunit, 'set_time_unit', 'class="form-control inputbox" size="1"', 'value', 'text');
HTML_AcctExp::listSubscriptions($rows, $nav, $this->state, $lists, $subscriptionid, $action);
}