public function index()
{
if (!empty($this->state->filter->group)) {
$subselect = ItemGroupHandler::getChildren($this->state->filter->group, 'item');
$this->addConstraint('id IN (' . implode(',', $subselect) . ')');
} else {
$subselect = array();
}
$nav = $this->getPagination();
// get the subset (based on limits) of records
$rows = SubscriptionPlanHandler::getFullPlanList($nav->limitstart, $nav->limit, $subselect, $this->state->sort, $this->state->search);
$gcolors = array();
foreach ($rows as $n => $row) {
$query = 'SELECT count(*)' . ' FROM #__acctexp_subscr' . ' WHERE plan = ' . $row->id . ' AND (status = \'Active\' OR status = \'Trial\')';
$this->db->setQuery($query);
$rows[$n]->usercount = $this->db->loadResult();
if ($this->db->getErrorNum()) {
echo $this->db->stderr();
return false;
}
$query = 'SELECT count(*)' . ' FROM #__acctexp_subscr' . ' WHERE plan = ' . $row->id . ' AND (status = \'Expired\')';
$this->db->setQuery($query);
$rows[$n]->expiredcount = $this->db->loadResult();
if ($this->db->getErrorNum()) {
echo $this->db->stderr();
return false;
}
$query = 'SELECT group_id' . ' FROM #__acctexp_itemxgroup' . ' WHERE type = \'item\'' . ' AND item_id = \'' . $rows[$n]->id . '\'';
$this->db->setQuery($query);
$groups = xJ::getDBArray($this->db);
$rows[$n]->groups = array();
foreach ($groups as $group) {
if (empty($group)) {
continue;
}
if (!isset($gcolors[$group])) {
$gcolors[$group] = array();
$gcolors[$group]['color'] = ItemGroupHandler::groupColor($group);
}
$rows[$n]->groups[] = (object) array('id' => $group, 'color' => $gcolors[$group]['color']);
}
}
$grouplist = ItemGroupHandler::getTree();
$glist = array();
$sel_groups = array();
$glist[] = JHTML::_('select.option', 0, '- - - - - -');
if (empty($this->state->filter->group)) {
$sel_groups[] = JHTML::_('select.option', 0, '- - - - - -');
}
foreach ($grouplist as $id => $glisti) {
if (defined('JPATH_MANIFESTS')) {
$glist[] = JHTML::_('select.option', $glisti[0], str_replace(' ', ' ', $glisti[1]));
} else {
$glist[] = JHTML::_('select.option', $glisti[0], $glisti[1]);
}
if (!empty($this->state->filter->group)) {
if (in_array($glisti[0], $this->state->filter->group)) {
$sel_groups[] = JHTML::_('select.option', $glisti[0], $glisti[1]);
}
}
}
$lists['filter_group'] = JHTML::_('select.genericlist', $glist, 'group[]', 'size="4" multiple="multiple"', 'value', 'text', $sel_groups);
$totals = array();
$query = 'SELECT count(*)' . ' FROM #__acctexp_subscr' . ' WHERE (status = \'Active\' OR status = \'Trial\')' . (empty($subselect) ? '' : ' AND plan IN (' . implode(',', $subselect) . ')');
$this->db->setQuery($query);
$totals['active'] = $this->db->loadResult();
if ($this->db->getErrorNum()) {
echo $this->db->stderr();
return false;
}
$query = 'SELECT count(*)' . ' FROM #__acctexp_subscr' . ' WHERE (status = \'Expired\')' . (empty($subselect) ? '' : ' AND plan IN (' . implode(',', $subselect) . ')');
$this->db->setQuery($query);
$totals['expired'] = $this->db->loadResult();
if ($this->db->getErrorNum()) {
echo $this->db->stderr();
return false;
}
foreach ($rows as $rid => $row) {
$rows[$rid]->link = 'index.php?option=com_acctexp&task=index&entity=Membership&plan=' . $row->id . '&groups[]=all';
$rows[$rid]->link_active = 'index.php?option=com_acctexp&task=index&entity=Membership&plan=' . $row->id . '&groups[]=active';
$rows[$rid]->link_expired = 'index.php?option=com_acctexp&task=index&entity=Membership&plan=' . $row->id . '&groups[]=expired';
if ($totals['expired']) {
$rows[$rid]->expired_percentage = $row->expiredcount / ($totals['expired'] / 100);
} else {
$rows[$rid]->expired_percentage = 0;
}
$rows[$rid]->expired_inner = false;
if ($rows[$rid]->expired_percentage > 45) {
$rows[$rid]->expired_inner = true;
}
if ($totals['active']) {
$rows[$rid]->active_percentage = $row->usercount / ($totals['active'] / 100);
} else {
$rows[$rid]->active_percentage = 0;
}
$rows[$rid]->active_inner = false;
if ($rows[$rid]->active_percentage > 45) {
$rows[$rid]->active_inner = true;
}
if ($totals['active'] + $totals['expired']) {
$rows[$rid]->total_percentage = ($row->expiredcount + $row->usercount) / (($totals['active'] + $totals['expired']) / 100);
} else {
$rows[$rid]->total_percentage = 0;
}
$rows[$rid]->total_inner = false;
if ($rows[$rid]->total_percentage > 20) {
$rows[$rid]->total_inner = true;
}
if (!empty($row->desc)) {
$rows[$rid]->desc = stripslashes(strip_tags($row->desc));
if (strlen($rows[$rid]->desc) > 50) {
$rows[$rid]->desc = substr($rows[$rid]->desc, 0, 50) . ' ...';
}
}
}
HTML_AcctExp::listSubscriptionPlans($rows, $this->state, $lists, $nav);
}