public function Settings()
{
$settings = array();
$settings['activate'] = array('toggle');
$settings['block'] = array('toggle');
$settings['username'] = array('inputD');
$settings['username_rand'] = array('inputC');
$settings['password'] = array('inputD');
$xsettings = array();
if (defined('JPATH_MANIFESTS')) {
$settings['set_fields'] = array('toggle');
$db = JFactory::getDBO();
$query = 'SELECT DISTINCT `profile_key`' . ' FROM #__user_profiles';
$db->setQuery($query);
$pkeys = xJ::getDBArray($db);
if (!empty($pkeys)) {
foreach ($pkeys as $k) {
$title = ucfirst(str_replace('profile.', '', $k));
$settings['jprofile_' . str_replace(".", "_", $k)] = array('inputE', $title, $title);
$expname = $title . " " . JText::_('MI_MI_JOOMLAUSER_EXPMARKER');
$xsettings['jprofile_' . str_replace(".", "_", $k) . '_exp'] = array('inputE', $expname, $expname);
}
}
}
$rewriteswitches = array('cms', 'user', 'expiration', 'subscription', 'plan', 'invoice');
$settings = AECToolbox::rewriteEngineInfo($rewriteswitches, $settings);
$settings['aectab_reg'] = array('tab', 'Expiration', 'Expiration');
$settings['set_fields_exp'] = array('toggle');
$settings = array_merge($settings, $xsettings);
$settings = AECToolbox::rewriteEngineInfo($rewriteswitches, $settings);
return $settings;
}
public function getUserPipelineEvents($userid)
{
$db = JFactory::getDBO();
// Entries for this user only
$query = 'SELECT `id`' . ' FROM #__acctexp_displaypipeline' . ' WHERE `userid` = \'' . $userid . '\' AND `only_user` = \'1\'';
$db->setQuery($query);
$events = xJ::getDBArray($db);
// Entries for all users
$query = 'SELECT `id`' . ' FROM #__acctexp_displaypipeline' . ' WHERE `only_user` = \'0\'';
$db->setQuery($query);
$events = array_merge($events, xJ::getDBArray($db));
$return = '';
if (empty($events)) {
return $return;
}
foreach ($events as $eventid) {
$displayPipeline = new displayPipeline();
$displayPipeline->load($eventid);
if ($displayPipeline->id) {
// If expire & expired -> delete
if ($displayPipeline->expire) {
$expstamp = strtotime($displayPipeline->expstamp);
if ($expstamp - (int) gmdate('U') < 0) {
$displayPipeline->delete();
continue;
}
}
// If displaymax exceeded -> delete
$displayremain = $displayPipeline->displaymax - $displayPipeline->displaycount;
if ($displayremain <= 0) {
$displayPipeline->delete();
continue;
}
// If this can only be displayed once per user, prevent it from being displayed again
if ($displayPipeline->once_per_user) {
$params = $displayPipeline->params;
if (isset($displayPipeline->params['displayedto'])) {
$users = $displayPipeline->params['displayedto'];
if (in_array($userid, $users)) {
continue;
} else {
$users[] = $userid;
$displayPipeline->params['displayedto'] = $users;
}
}
}
// Ok, now append text
$return .= stripslashes($displayPipeline->displaytext);
// Update display if at least one display would remain
if ($displayremain > 1) {
$displayPipeline->displaycount = $displayPipeline->displaycount + 1;
$displayPipeline->check();
$displayPipeline->store();
} else {
$displayPipeline->delete();
}
}
}
return $return;
}
public function getListForSubject($subject)
{
$db = JFactory::getDBO();
$query = 'SELECT `id`' . ' FROM #__acctexp_displaypipeline' . ' WHERE `subject` = \'' . $subject . '\'';
$db->setQuery($query);
$buckets = xJ::getDBArray($db);
return $buckets;
}
public function deleteOldEvents()
{
$db = JFactory::getDBO();
// Load all events happening now or before now
$query = 'SELECT `id`' . ' FROM #__acctexp_event' . ' WHERE `due_date` <= \'' . date('Y-m-d H:i:s', (int) gmdate('U')) . '\'' . ' AND `status` = \'waiting\'';
$db->setQuery($query);
$events = xJ::getDBArray($db);
// Call each event individually
foreach ($events as $evid) {
$event = new aecEvent();
$event->load($evid);
$event->trigger();
}
}
public function Action()
{
if (empty($_POST['type']) || empty($_POST['search'])) {
return "<h3>Incomplete Query.</h3>";
}
$db = JFactory::getDBO();
$types = array('config' => array('config', 'aecConfig'), 'processor' => array('config_processors', 'PaymentProcessor'), 'coupons' => array('coupons', 'Coupon'), 'displaypipeline' => array('displaypipeline', 'displayPipeline'), 'eventlog' => array('eventlog', 'eventLog'), 'invoice' => array('invoices', 'Invoice'), 'itemgroups' => array('itemgroups', 'ItemGroup'), 'history' => array('log_history', 'logHistory'), 'metauser' => array('metauser', 'metaUserDB'), 'mi' => array('microintegrations', 'microIntegration'), 'plans' => array('plans', 'SubscriptionPlan'), 'subscr' => array('subscr', 'Subscription'));
$changes = 0;
foreach ($_POST['type'] as $type) {
$query = 'SELECT `id` FROM `#__acctexp_' . $types[$type][0] . '`';
$db->setQuery($query);
$ids = xJ::getDBArray($db);
foreach ($ids as $id) {
$objclass = $types[$type][1];
$obj = new $objclass();
$obj->load($id);
if (!empty($_POST['armed']) && !empty($_POST['replace'])) {
if (AECToolbox::searchinObjectProperties($obj, $_POST['search'])) {
$mod = AECToolbox::searchreplaceinObjectProperties($obj, $_POST['search'], $_POST['replace']);
$mod->check();
$mod->store();
$changes++;
}
} else {
if (AECToolbox::searchinObjectProperties($obj, $_POST['search'])) {
$changes++;
}
}
}
}
$return = '';
$return .= "<h3>Query Result:</h3>";
$return .= "<p>Searching for <strong>" . $_POST['search'] . "</strong></p>";
$return .= "<p>Replacing it with <strong>" . $_POST['replace'] . "</strong></p>";
$return .= "<p>Found <strong>" . $changes . "</strong> database entries.</p>";
if ($_POST['armed']) {
$return .= "<p>Modified <strong>" . $changes . "</strong> database entries.</p>";
}
return $return;
}
public function loadUser()
{
$users = array();
if (is_object($this->request->user)) {
$db = JFactory::getDBO();
if (isset($this->request->user->username)) {
$query = 'SELECT `id`' . ' FROM #__users' . ' WHERE LOWER( `username` ) LIKE \'%' . xJ::escape($db, strtolower($this->request->user->username)) . '%\'';
$db->setQuery($query);
$users = xJ::getDBArray($db);
}
if (empty($users) && isset($this->request->user->name)) {
$query = 'SELECT `id`' . ' FROM #__users' . ' WHERE LOWER( `name` ) LIKE \'%' . xJ::escape($db, strtolower($this->request->user->name)) . '%\'';
$db->setQuery($query);
$users = xJ::getDBArray($db);
}
if (empty($users) && isset($this->request->user->email)) {
$query = 'SELECT `id`' . ' FROM #__users' . ' WHERE LOWER( `email` ) = \'' . xJ::escape($db, $this->request->user->email) . '\'';
$db->setQuery($query);
$users = xJ::getDBArray($db);
}
if (empty($users) && isset($this->request->user->userid)) {
$query = 'SELECT `id`' . ' FROM #__users' . ' WHERE `id` = \'' . xJ::escape($db, $this->request->user->userid) . '\'';
$db->setQuery($query);
$users = xJ::getDBArray($db);
}
if (empty($users) && isset($this->request->user->invoice_number)) {
$query = 'SELECT `userid`' . 'FROM #__acctexp_invoices' . ' WHERE LOWER( `invoice_number` ) = \'' . xJ::escape($db, $this->request->user->invoice_number) . '\'' . ' OR LOWER( `secondary_ident` ) = \'' . xJ::escape($db, $this->request->user->invoice_number) . '\'';
$db->setQuery($query);
$users = xJ::getDBArray($db);
}
} else {
$users = AECToolbox::searchUser($this->request->user);
}
if (!count($users)) {
$this->error = 'user not found';
} elseif (count($users) > 1) {
$this->error = 'multiple users found';
} else {
if (!empty($this->metaUser->userid)) {
if ($this->metaUser->userid != $users[0]) {
$this->metaUser = new metaUser($users[0]);
}
} else {
$this->metaUser = new metaUser($users[0]);
}
}
}
$db->setQuery($query);
$plans = xJ::getDBArray($db);
foreach ($plans as $planid) {
$plan = new SubscriptionPlan();
$plan->load($planid);
if (!empty($plan->params['processors'])) {
foreach ($plan->params['processors'] as $pi) {
if (!in_array($pi, $allprocs)) {
$allprocs[] = $pi;
}
}
}
}
$query = 'SELECT id FROM #__acctexp_config_processors';
$db->setQuery($query);
$procs = xJ::getDBArray($db);
foreach ($procs as $procid) {
// Check whether the processor has a plan it is applied to
if (!in_array($procid, $allprocs)) {
// Double check whether we have a history entry
$query = 'SELECT id FROM #__acctexp_log_history WHERE `proc_id` = \'' . $procid . '\'';
$db->setQuery($query);
if (!$db->loadResult()) {
$query = 'DELETE FROM #__acctexp_config_processors WHERE `id` = \'' . $procid . '\'';
$db->setQuery($query);
$db->query();
}
}
}
}
$eucaInstalldb->addColifNotExists('hidden', "int(4) NOT NULL default '0'", 'microintegrations');
public function Action()
{
if (empty($_POST['start_date'])) {
return null;
}
$db = JFactory::getDBO();
$start_timeframe = $_POST['start_date'] . ' 00:00:00';
if (!empty($_POST['end_date'])) {
$end_timeframe = $_POST['end_date'] . ' 23:59:59';
} else {
$end_timeframe = date('Y-m-d', (int) gmdate('U'));
}
$query = 'SELECT `id`' . ' FROM #__acctexp_log_history' . ' WHERE transaction_date >= \'' . $start_timeframe . '\'' . ' AND transaction_date <= \'' . $end_timeframe . '\'' . ' ORDER BY transaction_date ASC';
$db->setQuery($query);
$entries = xJ::getDBArray($db);
if (empty($entries)) {
return "nothing to list";
}
$historylist = array();
$groups = array();
foreach ($entries as $id) {
$entry = new logHistory($db);
$entry->load($id);
$refund = false;
if (is_array($entry->response)) {
$filter = array('subscr_signup', 'paymentreview', 'subscr_eot', 'subscr_failed', 'subscr_cancel');
$refund = false;
foreach ($entry->response as $v) {
if (in_array($v, $filter)) {
continue 2;
} elseif ($v == 'refund') {
$refund = true;
}
}
}
$date = date('Y-m-d', strtotime($entry->transaction_date));
$iFactory = new InvoiceFactory($entry->user_id, null, null, null, null, null, false, true);
if ($iFactory->userid != $entry->user_id) {
continue;
}
$iFactory->loadMetaUser();
$iFactory->touchInvoice($entry->invoice_number, false, true);
if ($iFactory->invoice_number != $entry->invoice_number) {
continue;
}
$iFactory->puffer();
$iFactory->loadItems();
$iFactory->loadItemTotal();
if (isset($iFactory->items->total)) {
$amount = $iFactory->items->total->cost['amount'];
} else {
continue;
}
$tax = 0;
foreach ($iFactory->items->tax as $item) {
$tax += $item['cost'];
}
if ($refund) {
$historylist[$date]['amount'] -= $amount;
if ($tax) {
$historylist[$date]['taxed'] -= $amount;
$historylist[$date]['tax'] -= $tax;
} else {
$historylist[$date]['untaxed'] -= $amount;
}
} else {
$historylist[$date]['amount'] += $amount;
if ($tax) {
$historylist[$date]['taxed'] += $amount;
$historylist[$date]['tax'] += $tax;
} else {
$historylist[$date]['untaxed'] += $amount;
}
}
}
$return = "";
$return .= '<table style="background-color: fff; width: 30%; margin: 0 auto; text-align: center !important; font-size: 180%;">';
$i = 0;
foreach ($historylist as $date => $history) {
$i++;
if (date('j', strtotime($date)) == 1 || $i === 1) {
$month = array();
}
$return .= '<tr style="border-bottom: 2px solid #999 !important; height: 2em;">';
$return .= '<td title="Date" style="text-align: left !important; color: #aaa;">' . $date . '</td>';
$return .= '<td style="width: 5em;"> </td>';
$return .= '<td title="Non-Taxed" style="font-weight: bold; width: 5em;">' . AECToolbox::correctAmount($history['untaxed']) . '</td>';
if (!empty($history['taxed'])) {
$return .= '<td style="width: 5em;">+</td>';
$return .= '<td title="Taxed including Tax" style="font-weight: bold; width: 5em;">' . AECToolbox::correctAmount($history['taxed'] + $history['tax']) . '</td>';
$return .= '<td title="Taxed" style="font-weight: bold; width: 5em; color: #aaa;">(' . AECToolbox::correctAmount($history['taxed']) . '</td>';
$return .= '<td style="width: 5em; color: #aaa;">+</td>';
$return .= '<td title="Tax" style="font-weight: bold; width: 5em; color: #aaa;">' . AECToolbox::correctAmount($history['tax']) . ')</td>';
} else {
$return .= '<td colspan="5"></td>';
}
$return .= '<td style="width: 5em;">=</td>';
$return .= '<td style="width: 5em;"> </td>';
$return .= '<td title="Grand Total" style="text-align: right !important; color: #608919;">' . AECToolbox::correctAmount($history['amount'] + $history['tax']) . '</td>';
$return .= '</tr>';
$return .= '<tr style="height: 1px; background-color: #999;">';
$return .= '<td colspan="11"></td>';
$return .= '</tr>';
if (isset($month)) {
$month['amount'] += $history['amount'];
$month['tax'] += $history['tax'];
$month['taxed'] += $history['taxed'];
$month['untaxed'] += $history['untaxed'];
}
if (isset($month) && (date('j', strtotime($date)) == date('t', strtotime($date)) || $i == count($historylist))) {
$return .= '<tr style="border-bottom: 2px solid #999 !important; height: 2em; background-color: #ddd;">';
$return .= '<td title="Date" style="text-align: left !important; color: #aaa;">Month</td>';
$return .= '<td style="width: 5em;"> </td>';
$return .= '<td title="Non-Taxed" style="font-weight: bold; width: 5em;">' . AECToolbox::correctAmount($month['untaxed']) . '</td>';
if (!empty($month['taxed'])) {
$return .= '<td style="width: 5em;">+</td>';
$return .= '<td title="Taxed including Tax" style="font-weight: bold; width: 5em;">' . AECToolbox::correctAmount($month['taxed'] + $month['tax']) . '</td>';
$return .= '<td title="Taxed" style="font-weight: bold; width: 5em; color: #aaa;">(' . AECToolbox::correctAmount($month['taxed']) . '</td>';
$return .= '<td style="width: 5em; color: #aaa;">+</td>';
$return .= '<td title="Tax" style="font-weight: bold; width: 5em; color: #aaa;">' . AECToolbox::correctAmount($month['tax']) . ')</td>';
} else {
$return .= '<td colspan="5"></td>';
}
$return .= '<td style="width: 5em;">=</td>';
$return .= '<td style="width: 5em;"> </td>';
$return .= '<td title="Grand Total" style="text-align: right !important; color: #608919;">' . AECToolbox::correctAmount($month['amount'] + $month['tax']) . '</td>';
$return .= '</tr>';
$return .= '<tr style="height: 1px; background-color: #999;">';
$return .= '<td colspan="11"></td>';
$return .= '</tr>';
}
}
$return .= '</table><br /><br />';
return $return;
}
public function Action()
{
if (empty($_POST['start_date'])) {
return null;
}
$db = JFactory::getDBO();
$start_timeframe = $_POST['start_date'] . ' 00:00:00';
if (empty($end)) {
$end = date('Y-m-d', (int) gmdate('U'));
}
$end_timeframe = $end . ' 23:59:59';
$query = 'SELECT `id`' . ' FROM #__acctexp_log_history' . ' WHERE transaction_date >= \'' . $start_timeframe . '\'' . ' AND transaction_date <= \'' . $end_timeframe . '\'' . ' ORDER BY transaction_date ASC';
$db->setQuery($query);
$entries = xJ::getDBArray($db);
if (empty($entries)) {
return "nothing to list";
}
$historylist = array();
$groups = array();
foreach ($entries as $id) {
$entry = new logHistory();
$entry->load($id);
$refund = false;
if (is_array($entry->response)) {
$filter = array('new_case', 'subscr_signup', 'paymentreview', 'subscr_eot', 'subscr_failed', 'subscr_cancel', 'Pending', 'Denied');
$refund = false;
foreach ($entry->response as $v) {
if (in_array($v, $filter)) {
continue 2;
} elseif ($v == 'refund' || $v == 'Reversed' || $v == 'Refunded') {
$refund = true;
}
}
}
$date = date('Y-m-d', strtotime($entry->transaction_date));
$pgroups = ItemGroupHandler::parentGroups($entry->plan_id);
if (!in_array($pgroups[0], $groups)) {
$groups[] = $pgroups[0];
}
if (!isset($historylist[$date])) {
$historylist[$date] = array('amount' => null, 'groups' => null);
}
if ($refund) {
$historylist[$date]['amount'] -= (double) $entry->amount;
$historylist[$date]['groups'][$pgroups[0]]--;
} else {
$historylist[$date]['amount'] += (double) $entry->amount;
$historylist[$date]['groups'][$pgroups[0]]++;
}
}
foreach ($historylist as $date => $entry) {
ksort($historylist[$date]['groups']);
}
$return = "";
$return .= '<table style="background-color: fff; width: 30%; margin: 0 auto; text-align: center !important; font-size: 180%;">';
$groupnames = array();
foreach ($groups as $group) {
$groupnames[$group] = ItemGroupHandler::groupName($group);
}
$closer = 0;
$incomplete = false;
foreach ($historylist as $date => $history) {
if (date('D', strtotime($date)) == 'Mon') {
$week = array();
} elseif (!isset($week)) {
$week = array();
$incomplete = true;
}
$return .= '<tr style="border-bottom: 2px solid #999 !important; height: 2em;">';
$return .= '<td title="Date" style="text-align: left !important; color: #aaa;">' . $date . '</td>';
$return .= '<td style="width: 5em;"> </td>';
foreach ($groups as $group) {
if (empty($history['groups'][$group])) {
$count = 0;
} else {
$count = $history['groups'][$group];
}
$return .= '<td title="' . $groupnames[$group] . '" style="font-weight: bold; width: 5em;">' . $count . '</td>';
if (isset($week)) {
$week['groups'][$group] += $count;
}
}
if (isset($week)) {
$week['amount'] += $history['amount'];
}
$return .= '<td style="width: 5em;"> </td>';
$return .= '<td title="Amount" style="text-align: right !important; color: #608919;">' . AECToolbox::correctAmount($history['amount']) . '</td>';
$return .= '</tr>';
$return .= '<tr style="height: 1px; background-color: #999;">';
$return .= '<td colspan="' . (count($groups) + 4) . '"></td>';
$return .= '</tr>';
$closer = 0;
if (date('D', strtotime($date)) == 'Sun') {
$return .= '<tr ' . ($incomplete ? 'title="Incomplete!"' : '') . 'style="border-bottom: 2px solid #999 !important; height: 2em; background-color: #ddd;">';
$return .= '<td style="text-align: left !important; color: #aaa;">' . ($incomplete ? '(Week)' : 'Week') . '</td>';
$return .= '<td style="width: 5em;"> </td>';
foreach ($groups as $group) {
if (empty($week['groups'][$group])) {
$count = 0;
} else {
$count = $week['groups'][$group];
}
if ($incomplete) {
$return .= '<td title="' . $groupnames[$group] . '" style="font-weight: bold; width: 5em;">(' . $count . ')</td>';
} else {
$return .= '<td title="' . $groupnames[$group] . '" style="font-weight: bold; width: 5em;">' . $count . '</td>';
}
}
$return .= '<td style="width: 5em;"> </td>';
if ($incomplete) {
$return .= '<td title="Amount" style="text-align: right !important; color: #608919;">(' . AECToolbox::correctAmount($week['amount']) . ')</td>';
} else {
$return .= '<td title="Amount" style="text-align: right !important; color: #608919;">' . AECToolbox::correctAmount($week['amount']) . '</td>';
}
$return .= '</tr>';
$return .= '<tr style="height: 1px; background-color: #999;">';
$return .= '<td colspan="' . (count($groups) + 4) . '"></td>';
$return .= '</tr>';
$closer = 1;
$incomplete = false;
}
}
if (!$closer) {
$return .= '<tr style="border-bottom: 2px solid #999 !important; height: 2em; background-color: #ddd;">';
$return .= '<td title="Date" style="text-align: left !important; color: #aaa;">(Week)</td>';
$return .= '<td style="width: 5em;"> </td>';
foreach ($groups as $group) {
if (empty($week['groups'][$group])) {
$count = 0;
} else {
$count = $week['groups'][$group];
}
$return .= '<td title="' . $groupnames[$group] . '" style="font-weight: bold; width: 5em;">' . $count . '</td>';
}
$return .= '<td style="width: 5em;"> </td>';
$return .= '<td title="Amount" style="text-align: right !important; color: #608919;">' . AECToolbox::correctAmount($week['amount']) . '</td>';
$return .= '</tr>';
$return .= '<tr style="height: 1px; background-color: #999;">';
$return .= '<td colspan="' . (count($groups) + 4) . '"></td>';
$return .= '</tr>';
$closer = 1;
}
$return .= '</table><br /><br />';
return $return;
}
public function Action()
{
$db = JFactory::getDBO();
// Find all entries lacking an existing user account
$tables = array('cart' => 'userid', 'couponsxuser' => 'userid', 'invoices' => 'userid', 'metauser' => 'userid', 'subscr' => 'userid', 'log_history' => 'user_id');
$found = array('total' => 0);
foreach ($tables as $table => $key) {
$query = 'SELECT count(*)' . ' FROM #__acctexp_' . $table . ' AS a' . ' LEFT JOIN #__users AS b ON a.' . $key . ' = b.id' . ' WHERE b.id is null';
$db->setQuery($query);
$count = $db->loadResult();
$found[$table] = $count;
$found['total'] += $count;
}
if (!empty($_POST['delete'])) {
$return = '<p>Deleted a total of ' . $found['total'] . ' entries.<p>' . '<ul>';
foreach ($found as $table => $count) {
if ($table != 'total' && $count) {
$query = 'SELECT a.id' . ' FROM #__acctexp_' . $table . ' AS a' . ' LEFT JOIN #__users AS b ON a.' . $tables[$table] . ' = b.id' . ' WHERE b.id is null';
$db->setQuery($query);
$ids = xJ::getDBArray($db);
$query = 'DELETE' . ' FROM #__acctexp_' . $table . ' WHERE id IN (' . implode(',', $ids) . ')';
$db->setQuery($query);
$db->query();
$return .= '<li>deleted ' . $count . ' entries in table ' . $table . '</li>';
}
}
$query = 'SELECT count(*)' . ' FROM #__acctexp_eventlog' . ' WHERE tags = \'debug\'';
$db->setQuery($query);
$dcount = $db->loadResult();
if ($dcount) {
$query = 'DELETE' . ' FROM #__acctexp_eventlog' . ' WHERE tags = \'debug\'';
$db->setQuery($query);
$db->query();
$return .= '<li>removed ' . $dcount . ' debug entries in the eventlog</li>';
}
if (!empty($_POST['alltemp'])) {
$query = 'SELECT count(*)' . ' FROM #__acctexp_temptoken';
$db->setQuery($query);
$dcount = $db->loadResult();
if ($dcount) {
$query = 'TRUNCATE TABLE#__acctexp_temptoken';
$db->setQuery($query);
$db->query();
$return .= '<li>removed ' . $dcount . ' temptokens (full cleanup)</li>';
}
} else {
$query = 'SELECT count(*)' . ' FROM #__acctexp_temptoken' . ' WHERE created_date < \'' . date('Y-m-d H:i:s', (int) gmdate('U') - 3600) . '\'';
$db->setQuery($query);
$dcount = $db->loadResult();
if ($dcount) {
$query = 'DELETE' . ' FROM #__acctexp_temptoken' . ' WHERE created_date < \'' . date('Y-m-d H:i:s', (int) gmdate('U') - 3600) . '\'';
$db->setQuery($query);
$db->query();
$return .= '<li>removed ' . $dcount . ' temptokens</li>';
}
}
return $return;
} else {
$return = '<p>Found a total of ' . $found['total'] . ' entries.<p>' . '<ul>';
foreach ($found as $table => $count) {
if ($table != 'total') {
$return .= '<li>' . $count . ' entries in table ' . $table . '</li>';
}
}
$query = 'SELECT count(*)' . ' FROM #__acctexp_eventlog' . ' WHERE tags = \'debug\'';
$db->setQuery($query);
$count = $db->loadResult();
if ($count) {
$return .= '<li>' . $count . ' debug entries in the eventlog</li>';
}
$query = 'SELECT count(*)' . ' FROM #__acctexp_temptoken' . ' WHERE created_date < \'' . date('Y-m-d H:i:s', (int) gmdate('U') - 3600) . '\'';
$db->setQuery($query);
$count = $db->loadResult();
if ($count) {
$return .= '<li>' . $count . ' temptokens older than an hour</li>';
}
return $return;
}
}
public function request($type, $start, $end)
{
$tree = new stdClass();
switch ($type) {
case 'sales':
$tree = array();
if (empty($end)) {
$end = date('Y-m-d H:i:s', (int) gmdate('U'));
}
$query = 'SELECT `id`' . ' FROM #__acctexp_log_history' . ' WHERE transaction_date >= \'' . $start . '\'' . ' AND transaction_date <= \'' . $end . '\'' . ' ORDER BY transaction_date ASC';
$this->db->setQuery($query);
$entries = xJ::getDBArray($this->db);
if (empty($entries)) {
echo json_encode($tree);
exit;
}
$historylist = array();
$groups = array();
foreach ($entries as $id) {
$entry = new logHistory();
$entry->load($id);
$entry->amount = AECToolbox::correctAmount($entry->amount);
$refund = false;
if (is_array($entry->response) && !empty($entry->response)) {
$filter = array('new_case', 'subscr_signup', 'paymentreview', 'subscr_eot', 'subscr_failed', 'subscr_cancel', 'Pending', 'Denied');
foreach ($entry->response as $v) {
if (in_array($v, $filter)) {
continue 2;
} elseif ($v == 'refund' || $v == 'Reversed' || $v == 'Refunded') {
$refund = true;
}
}
} else {
continue;
}
$pgroups = ItemGroupHandler::parentGroups($entry->plan_id);
if (empty($pgroups[0])) {
$pgroups[0] = 0;
}
if (!in_array($pgroups[0], $groups)) {
$groups[] = $pgroups[0];
}
$sale = new stdClass();
$sale->id = $id;
//$sale->invoice = $entry->invoice_number;
$sale->date = $entry->transaction_date;
//$sale->datejs = date( 'F d, Y H:i:s', strtotime( $entry->transaction_date ) );
$sale->plan = $entry->plan_id;
$sale->group = $pgroups[0];
$sale->amount = $refund ? -$entry->amount : $entry->amount;
$tree[] = $sale;
}
break;
}
echo json_encode($tree);
exit;
}
$result = $db->loadObject();
if (is_object($result)) {
if (strcmp($result->Field, 'extra01') === 0) {
$queri = array();
$queri[] = "ALTER TABLE #__acctexp_subscr CHANGE `extra01` `recurring` int(1) NOT NULL default '0'";
$queri[] = "ALTER TABLE #__acctexp_subscr DROP `extra02`";
$queri[] = "ALTER TABLE #__acctexp_subscr DROP `extra03`";
$queri[] = "ALTER TABLE #__acctexp_subscr DROP `extra04`";
$eucaInstalldb->multiQueryExec($queri);
}
}
$db->setQuery("SELECT count(*) FROM #__acctexp_config_processors");
$oldplans = $db->loadResult() == 0 && in_array($app->getCfg('dbprefix') . 'acctexp_processors_plans', $tables);
if ($oldplans || in_array($app->getCfg('dbprefix') . 'acctexp_config_paypal', $tables)) {
$db->setQuery("SELECT proc_id FROM #__acctexp_processors_plans");
$db_processors = xJ::getDBArray($db);
if (is_array($db_processors)) {
$used_processors = array_unique($db_processors);
$legacy_processors_db = array("", "paypal", "vklix", "authorize", "allopass", "2checkout", "epsnetpay", "paysignet", "worldpay", "alertpay");
$legacy_processors_name = array("", "paypal", "viaklix", "authorize", "allopass", "2checkout", "epsnetpay", "paysignet", "worldpay", "alertpay");
foreach ($used_processors as $i => $n) {
$db->setQuery("SELECT * FROM #__acctexp_config_" . $legacy_processors_db[$n]);
$old_cfg = $db->loadObject();
$pp = new PaymentProcessor();
$pp->loadName($legacy_processors_name[$n]);
$pp->init();
switch ($legacy_processors_name[$n]) {
case 'paypal':
$pp->settings['business'] = $old_cfg->business;
$pp->settings['testmode'] = $old_cfg->testmode;
$pp->settings['tax'] = $old_cfg->tax;
public function info($switches = array(), $params = null)
{
$lang = JFactory::getLanguage();
if (is_array($switches)) {
if (!count($switches)) {
$switches = array('cms', 'user', 'subscription', 'invoice', 'plan', 'system');
}
} else {
if (empty($switches)) {
$switches = array('cms', 'user', 'subscription', 'invoice', 'plan', 'system');
} else {
$temp = $switches;
$switches = array($temp);
}
}
$rewrite = array();
if (in_array('system', $switches)) {
$rewrite['system'][] = 'timestamp';
$rewrite['system'][] = 'timestamp_backend';
$rewrite['system'][] = 'server_timestamp';
$rewrite['system'][] = 'server_timestamp_backend';
}
if (in_array('cms', $switches)) {
$rewrite['cms'][] = 'absolute_path';
$rewrite['cms'][] = 'live_site';
}
$newlang = array();
if (in_array('user', $switches)) {
$rewrite['user'][] = 'id';
$rewrite['user'][] = 'username';
$rewrite['user'][] = 'name';
$rewrite['user'][] = 'first_name';
$rewrite['user'][] = 'first_first_name';
$rewrite['user'][] = 'last_name';
$rewrite['user'][] = 'email';
$rewrite['user'][] = 'activationcode';
$rewrite['user'][] = 'activationlink';
if (defined('JPATH_MANIFESTS')) {
$db = JFactory::getDBO();
$query = 'SELECT DISTINCT `profile_key`' . ' FROM #__user_profiles';
$db->setQuery($query);
$pkeys = xJ::getDBArray($db);
if (empty($pkeys)) {
// Try to reconstruct this from the plugin
$query = 'SELECT params' . ' FROM #__extensions' . ' WHERE `name` = \'plg_user_profile\'' . ' AND enabled = 1';
$db->setQuery($query);
$jparams = (array) json_decode($db->loadResult());
foreach ($jparams as $k => $x) {
if (strpos($k, 'profile-') !== false) {
$pkeys[] = str_replace("profile-require_", "", $k);
}
}
}
if (!empty($pkeys)) {
foreach ($pkeys as $pkey) {
$content = str_replace(".", "_", $pkey);
$rewrite['user'][] = $content;
$name = 'REWRITE_KEY_USER_' . strtoupper($content);
if (!$lang->hasKey($name)) {
$newlang[$name] = $content;
}
}
}
}
if (aecComponentHelper::detect_component('anyCB')) {
$db = JFactory::getDBO();
$query = 'SELECT name, title' . ' FROM #__comprofiler_fields' . ' WHERE `table` != \'#__users\'' . ' AND name != \'NA\'';
$db->setQuery($query);
$objects = $db->loadObjectList();
if (is_array($objects)) {
foreach ($objects as $object) {
$rewrite['user'][] = $object->name;
if (strpos($object->title, '_') === 0) {
$content = $object->name;
} else {
$content = $object->title;
}
$name = 'REWRITE_KEY_USER_' . strtoupper($object->name);
if (!$lang->hasKey($name)) {
$newlang[$name] = $content;
}
}
}
}
if (aecComponentHelper::detect_component('JOMSOCIAL')) {
$db = JFactory::getDBO();
$query = 'SELECT `id`, `name`' . ' FROM #__community_fields' . ' WHERE `type` != \'group\'';
$db->setQuery($query);
$fields = $db->loadObjectList();
if (is_array($fields)) {
foreach ($fields as $field) {
$rewrite['user'][] = 'js_' . $field->id;
$content = $field->name;
$name = 'REWRITE_KEY_USER_JS_' . $field->id;
if (!$lang->hasKey($name)) {
$newlang[$name] = $content;
}
}
}
}
}
if (!empty($newlang)) {
if (!isset($lang->_strings)) {
$lang->_strings = $newlang;
} else {
$lang->_strings = array_merge($newlang, $lang->_strings);
}
}
if (in_array('subscription', $switches)) {
$rewrite['subscription'][] = 'id';
$rewrite['subscription'][] = 'type';
$rewrite['subscription'][] = 'status';
$rewrite['subscription'][] = 'signup_date';
$rewrite['subscription'][] = 'signup_date_backend';
$rewrite['subscription'][] = 'lastpay_date';
$rewrite['subscription'][] = 'lastpay_date_backend';
$rewrite['subscription'][] = 'plan';
$rewrite['subscription'][] = 'previous_plan';
$rewrite['subscription'][] = 'recurring';
$rewrite['subscription'][] = 'lifetime';
$rewrite['subscription'][] = 'expiration_date';
$rewrite['subscription'][] = 'expiration_date_backend';
$rewrite['subscription'][] = 'expiration_daysleft';
$rewrite['subscription'][] = 'notes';
}
if (in_array('invoice', $switches)) {
$rewrite['invoice'][] = 'id';
$rewrite['invoice'][] = 'number';
$rewrite['invoice'][] = 'number_format';
$rewrite['invoice'][] = 'created_date';
$rewrite['invoice'][] = 'transaction_date';
$rewrite['invoice'][] = 'method';
$rewrite['invoice'][] = 'amount';
$rewrite['invoice'][] = 'currency';
$rewrite['invoice'][] = 'coupons';
}
if (in_array('plan', $switches)) {
$rewrite['plan'][] = 'name';
$rewrite['plan'][] = 'desc';
$rewrite['plan'][] = 'notes';
}
if (!empty($params)) {
$params[] = array('accordion_start', 'accordion-small');
$params[] = array('accordion_itemstart', JText::_('REWRITE_ENGINE_TITLE'));
$list = '<div class="rewriteinfoblock">' . "\n" . '<p>' . JText::_('REWRITE_ENGINE_DESC') . '</p>' . "\n" . '</div>' . "\n";
$params[] = array('literal', $list);
$params[] = array('accordion_itemend', '');
foreach ($rewrite as $area => $keys) {
$params[] = array('accordion_itemstart', JText::_('REWRITE_AREA_' . strtoupper($area)));
$list = '<div class="rewriteinfoblock">' . "\n" . '<ul>' . "\n";
foreach ($keys as $key) {
if ($lang->hasKey('REWRITE_KEY_' . strtoupper($area . "_" . $key))) {
$list .= '<li>[[' . $area . "_" . $key . ']] => ' . JText::_('REWRITE_KEY_' . strtoupper($area . "_" . $key)) . '</li>' . "\n";
} else {
$list .= '<li>[[' . $area . "_" . $key . ']] => ' . ucfirst(str_replace('_', ' ', $key)) . '</li>' . "\n";
}
}
$list .= '</ul>' . "\n" . '</div>' . "\n";
$params[] = array('literal', $list);
$params[] = array('accordion_itemend', '');
}
$params[] = array('accordion_itemstart', JText::_('REWRITE_ENGINE_AECJSON_TITLE'));
$list = '<div class="rewriteinfoblock">' . "\n" . '<p>' . JText::_('REWRITE_ENGINE_AECJSON_DESC') . '</p>' . "\n" . '</div>' . "\n";
$params[] = array('literal', $list);
$params[] = array('accordion_itemend', '');
$params[] = array('div_end', '');
return $params;
} else {
$return = '';
foreach ($rewrite as $area => $keys) {
$return .= '<div class="rewriteinfoblock">' . "\n" . '<p><strong>' . JText::_('REWRITE_AREA_' . strtoupper($area)) . '</strong></p>' . "\n" . '<ul>' . "\n";
foreach ($keys as $key) {
if ($lang->hasKey('REWRITE_KEY_' . strtoupper($area . "_" . $key))) {
$return .= '<li>[[' . $area . "_" . $key . ']] => ' . JText::_('REWRITE_KEY_' . strtoupper($area . "_" . $key)) . '</li>' . "\n";
} else {
$return .= '<li>[[' . $area . "_" . $key . ']] => ' . ucfirst(str_replace('_', ' ', $key)) . '</li>' . "\n";
}
}
$return .= '</ul>' . "\n" . '</div>' . "\n";
}
$return .= '<div class="rewriteinfoblock">' . "\n" . '<p><strong>' . JText::_('REWRITE_ENGINE_AECJSON_TITLE') . '</strong></p>' . "\n" . '<p>' . JText::_('REWRITE_ENGINE_AECJSON_DESC') . '</p>' . "\n" . '</div>' . "\n";
return $return;
}
}
public function getSubscriptionID($userid, $usage = null, $primary = 1, $similar = false, $bias = null)
{
$query = 'SELECT `id`' . ' FROM #__acctexp_subscr' . ' WHERE `userid` = \'' . $userid . '\'';
if (!empty($usage)) {
$plan = new SubscriptionPlan();
$plan->load($usage);
$allplans = array($usage, $plan->getSimilarPlans());
if (count($allplans) > 1) {
foreach ($allplans as $apid => $pid) {
$allplans[$apid] = '`plan` = \'' . $pid . '\'';
}
$query .= ' AND (' . implode(' OR ', $allplans) . ')';
} else {
$query .= ' AND ' . '`plan` = \'' . $usage . '\'';
}
}
if (!empty($primary)) {
$query .= ' AND `primary` = \'1\'';
} elseif (!is_null($primary)) {
$query .= ' AND `primary` = \'0\'';
}
$this->_db->setQuery($query);
if (!empty($bias)) {
$subscriptionids = xJ::getDBArray($this->_db);
if (in_array($bias, $subscriptionids)) {
$subscriptionid = $bias;
}
} else {
$subscriptionid = $this->_db->loadResult();
}
if (!isset($subscriptionid)) {
$subscriptionid = null;
}
if (empty($subscriptionid) && !$similar) {
return $this->getSubscriptionID($userid, $usage, false, true, $bias);
}
return $subscriptionid;
}
static function searchUser($search)
{
$db = JFactory::getDBO();
$k = 0;
if (strpos($search, "@") !== false) {
// Try user email
$queries[$k] = 'FROM #__users' . ' WHERE LOWER( `email` ) = \'' . $search . '\'';
$qfields[$k] = 'id';
$k++;
// If its not that, how about the username and name?
$queries[$k] = 'FROM #__users' . ' WHERE LOWER( `username` ) LIKE \'%' . $search . '%\' OR LOWER( `name` ) LIKE \'%' . $search . '%\'';
$qfields[$k] = 'id';
$k++;
} else {
// Try username and name
$queries[$k] = 'FROM #__users' . ' WHERE LOWER( `username` ) LIKE \'%' . $search . '%\' OR LOWER( `name` ) LIKE \'%' . $search . '%\'';
$qfields[$k] = 'id';
$k++;
// If its not that, how about the user email?
$queries[$k] = 'FROM #__users' . ' WHERE LOWER( `email` ) LIKE \'%' . $search . '%\'';
$qfields[$k] = 'id';
$k++;
}
// Try to find this as a userid
$queries[$k] = 'FROM #__users' . ' WHERE `id` = \'' . $search . '\'';
$qfields[$k] = 'id';
$k++;
// Or maybe its an invoice number?
$queries[$k] = 'FROM #__acctexp_invoices' . ' WHERE LOWER( `invoice_number` ) = \'' . $search . '\'' . ' OR LOWER( `secondary_ident` ) = \'' . $search . '\'';
$qfields[$k] = 'userid';
$k++;
$ids = array();
foreach ($queries as $qid => $base_query) {
$query = 'SELECT count(*) ' . $base_query;
$db->setQuery($query);
$existing = $db->loadResult();
if ($existing) {
$query = 'SELECT `' . $qfields[$qid] . '` ' . $base_query;
$db->setQuery($query);
$ids = array_merge($ids, xJ::getDBArray($db));
}
}
return $ids;
}
public function exportSales()
{
$db = JFactory::getDBO();
$query = 'SELECT `id`' . ' FROM #__acctexp_log_history' . ' WHERE transaction_date >= \'' . $this->filter['date_start'] . '\'' . ' AND transaction_date <= \'' . $this->filter['date_end'] . '\'' . ' ORDER BY transaction_date ASC';
$db->setQuery($query);
$entries = xJ::getDBArray($db);
switch ($this->options['collate']) {
default:
case 'day':
$collation = 'Y-m-d';
break;
case 'week':
$collation = 'Y-W';
break;
case 'month':
$collation = 'Y-m';
break;
case 'year':
$collation = 'Y';
break;
}
$collators = array();
switch ($this->options['breakdown']) {
default:
case 'plan':
break;
case 'group':
$all_groups = ItemGroupHandler::getGroups();
$collators = array();
foreach ($all_groups as $gid) {
$collators[$gid] = ItemGroupHandler::getChildren($gid, 'item');
}
break;
}
$historylist = array();
foreach ($entries as $id) {
$entry = new logHistory();
$entry->load($id);
if (empty($entry->plan_id) || empty($entry->amount)) {
continue;
}
if (!empty($this->filter['groupid'])) {
if (empty($this->filter['planid'])) {
$this->filter['planid'] = array();
}
$children = ItemGroupHandler::getChildren($this->filter['groupid'], 'item');
if (!empty($children)) {
$this->filter['planid'] = array_merge($this->filter['planid'], $children);
$this->filter['planid'] = array_unique($this->filter['planid']);
}
}
if (!empty($this->filter['planid'])) {
if (!in_array($entry->plan_id, $this->filter['planid'])) {
continue;
}
}
if (!empty($this->filter['method'])) {
if (!in_array($entry->proc_id, $this->filter['method'])) {
continue;
}
}
$refund = false;
if (is_array($entry->response)) {
$filter = array('new_case', 'subscr_signup', 'paymentreview', 'subscr_eot', 'subscr_failed', 'subscr_cancel', 'Pending', 'Denied');
$refund = false;
foreach ($entry->response as $v) {
if (in_array($v, $filter)) {
continue 2;
} elseif ($v == 'refund' || $v == 'Reversed' || $v == 'Refunded') {
$refund = true;
}
}
}
$date = date($collation, strtotime($entry->transaction_date));
if ($this->options['breakdown'] == 'plan') {
if (!array_key_exists($entry->plan_id, $collators)) {
$collators[$entry->plan_id] = 0;
}
}
if (!isset($historylist[$date])) {
$historylist[$date] = array();
}
$historylist[$date][] = $entry;
}
$line = array("line" => "Date");
if ($this->options['breakdown'] == 'plan') {
foreach ($collators as $col => $colamount) {
$line['plan-' . $col] = "Plan #{$col}: " . SubscriptionPlanHandler::planName($col);
}
} elseif ($this->options['breakdown'] == 'group') {
foreach ($collators as $col => $colplans) {
$line['group-' . $col] = "Group #{$col}:" . ItemGroupHandler::groupName($col);
}
}
$line['total_sum'] = "Total";
// Remove whitespaces and newlines
foreach ($line as $larrid => $larrval) {
$line[$larrid] = trim($larrval);
if (is_numeric($larrval)) {
$line[$larrid] = AECToolbox::correctAmount($larrval);
}
}
$this->exphandler->putDescription($line);
$totalsum = 0;
$collate_all = array();
foreach ($collators as $col => $colv) {
$collate_all[$col] = 0;
}
foreach ($historylist as $date => $collater) {
$linesum = 0;
$collatex = array();
foreach ($collators as $col => $colv) {
$collatex[$col] = 0;
}
foreach ($collater as $entry) {
if ($this->options['breakdown'] == 'plan') {
$collatex[$entry->plan_id] += $entry->amount;
$collate_all[$entry->plan_id] += $entry->amount;
$linesum += $entry->amount;
$totalsum += $entry->amount;
} else {
$pgroup = 0;
foreach ($collators as $gid => $gplans) {
if ($entry->plan_id == $gid) {
$pgroup = $gid;
break;
}
}
if ($pgroup) {
$collatex[$pgroup] += $entry->amount;
$collate_all[$pgroup] += $entry->amount;
}
$linesum += $entry->amount;
$totalsum += $entry->amount;
}
}
$line = array("date" => $date);
foreach ($collators as $col => $colamount) {
if ($this->options['breakdown'] == 'plan') {
$line['plan-' . $col] = $collatex[$col];
} else {
$line['group-' . $col] = $collatex[$col];
}
}
$line['total_sum'] = $linesum;
// Remove whitespaces and newlines
$i = 0;
foreach ($line as $larrid => $larrval) {
$line[$larrid] = trim($larrval);
if (is_numeric($larrval) && $i) {
$line[$larrid] = AECToolbox::correctAmount($larrval);
}
$i++;
}
$this->exphandler->putln($line);
}
$line = array("line" => "Grand Total");
foreach ($collate_all as $col => $colamount) {
if ($this->options['breakdown'] == 'plan') {
$line['plan-' . $col] = $colamount;
} else {
$line['group-' . $col] = $colamount;
}
}
$line['total_sum'] = $totalsum;
// Remove whitespaces and newlines
foreach ($line as $larrid => $larrval) {
$line[$larrid] = trim($larrval);
if (is_numeric($larrval)) {
$line[$larrid] = AECToolbox::correctAmount($larrval);
}
}
$this->exphandler->putSum($line);
}
static function getHigherACLGroups($group_id)
{
$db = JFactory::getDBO();
$query = 'SELECT g2.id' . ' FROM #__core_acl_aro_groups AS g1' . ' INNER JOIN #__core_acl_aro_groups AS g2 ON g1.lft < g2.lft AND g1.rgt > g2.rgt' . ' WHERE g1.id = ' . $group_id . ' GROUP BY g2.id' . ' ORDER BY g2.lft';
$db->setQuery($query);
return xJ::getDBArray($db);
}
public function jaclSessionExtra($metaUser, $gid)
{
$sessionextra = array();
$db = JFactory::getDBO();
$acl = JFactory::getACL();
$gid_name = $acl->get_group_name($gid, 'ARO');
// Check for main entry
$query = 'SELECT `group_id`' . ' FROM #__jaclplus_user_group' . ' WHERE `id` = \'' . (int) $metaUser->userid . '\'' . ' AND `group_type` = \'main\'';
$db->setQuery($query);
if ($db->loadResult()) {
$query = 'UPDATE #__jaclplus_user_group' . ' SET `group_id` = \'' . (int) $gid . '\'' . ' WHERE `id` = \'' . (int) $metaUser->userid . '\'' . ' AND `group_type` = \'main\'';
$db->setQuery($query);
$db->query() or die($db->stderr());
} else {
$query = 'INSERT INTO #__jaclplus_user_group' . ' VALUES( \'' . (int) $metaUser->userid . '\', \'main\', \'' . (int) $gid . '\', \'\' )';
$db->setQuery($query);
$db->query() or die($db->stderr());
}
// Get Session
$query = 'SELECT *' . ' FROM #__session' . ' WHERE `userid` = \'' . (int) $metaUser->userid . '\'';
$db->setQuery($query);
$session = $db->loadObject();
if (!empty($session->userid)) {
$query = 'SELECT `group_id`' . ' FROM #__jaclplus_user_group' . ' WHERE `id` = \'' . (int) $metaUser->userid . '\'';
$db->setQuery($query);
$groups = xJ::getDBArray($db);
$query = 'SELECT `value`' . ' FROM #__core_acl_aro_groups' . ' WHERE `id` IN (' . implode(',', $groups) . ')';
$db->setQuery($query);
$valuelist = xJ::getDBArray($db);
$sessiongroups = array();
foreach ($valuelist as $vlist) {
$values = explode(',', $vlist);
$sessiongroups = array_merge($sessiongroups, $values);
}
$sessiongroups = array_unique($sessiongroups);
asort($sessiongroups);
$sessionextra['gids'] = $gid;
$sessionextra['jaclplus'] = implode(',', $sessiongroups);
}
return $sessionextra;
}
/**
* @param boolean $pre_expiration
*/
public function getSubscribers($pre_expiration)
{
$expiration_limit = $this->getExpirationLimit($pre_expiration);
// Select all the users that are Active and have an expiration date
$query = 'SELECT `id`' . ' FROM #__acctexp_subscr' . ' WHERE `expiration` <= \'' . $expiration_limit . '\'' . ' AND `expiration` != \'0000-00-00 00:00:00\'' . ' AND `status` != \'Expired\'' . ' AND `status` != \'Closed\'' . ' AND `status` != \'Excluded\'' . ' AND `status` != \'Pending\'' . ' ORDER BY `expiration` ASC';
$this->_db->setQuery($query);
return xJ::getDBArray($this->_db);
}
public function getGroupsByUser($userId, $recursive = true)
{
$db = JFactory::getDBO();
// Build the database query to get the rules for the asset.
$query = $db->getQuery(true);
$query->select($recursive ? 'b.id' : 'a.id');
$query->from('#__user_usergroup_map AS map');
$query->where('map.user_id = ' . (int) $userId);
$query->leftJoin('#__usergroups AS a ON a.id = map.group_id');
// If we want the rules cascading up to the global asset node we need a self-join.
if ($recursive) {
$query->leftJoin('#__usergroups AS b ON b.lft <= a.lft AND b.rgt >= a.rgt');
}
// Execute the query and load the rules from the result.
$db->setQuery($query);
$result = xJ::getDBArray($db);
// Clean up any NULL or duplicate values, just in case
JArrayHelper::toInteger($result);
if (empty($result)) {
$result = array('1');
} else {
$result = array_unique($result);
}
return $result;
}
/**
* @param string $type
*/
static function getChildren($groups, $type)
{
if (empty($groups)) {
return array();
}
$db = JFactory::getDBO();
$where = array();
if (is_array($groups) && !empty($groups)) {
$where[] = '`group_id` IN (' . implode(',', $groups) . ')';
} elseif (!empty($groups)) {
$where[] = '`group_id` = ' . $groups . '';
}
if (!empty($type)) {
$where[] = '`type` = \'' . $type . '\'';
}
$query = 'SELECT item_id' . ' FROM #__acctexp_itemxgroup';
if (!empty($where)) {
$query .= ' WHERE ( ' . implode(' AND ', $where) . ' )';
}
$db->setQuery($query);
$result = xJ::getDBArray($db);
if (!empty($result)) {
foreach ($result as $k => $v) {
if (empty($v)) {
unset($result[$k]);
}
}
// Order results
$query = 'SELECT id' . ' FROM #__acctexp_' . ($type == 'group' ? 'itemgroups' : 'plans') . ' WHERE id IN (' . implode(',', $result) . ')' . ' ORDER BY `ordering` ASC';
$db->setQuery($query);
return xJ::getDBArray($db);
} else {
return $result;
}
}
public function listPlans()
{
$db = JFactory::getDBO();
$db->setQuery('SELECT id' . ' FROM #__acctexp_plans');
return xJ::getDBArray($db);
}
<?php
/**
* @version $Id: upgrade_0_12_6_RC2m.inc.php
* @package AEC - Account Control Expiration - Membership Manager
* @subpackage Install Includes
* @copyright 2006-2015 Copyright (C) David Deutsch
* @author David Deutsch <*****@*****.**> & Team AEC - http://www.valanx.org
* @license GNU/GPL v.3 http://www.gnu.org/licenses/gpl.html or, at your option, any later version
*/
// Dont allow direct linking
defined('_JEXEC') or die('Direct Access to this location is not allowed.');
$query = 'SELECT `id`' . ' FROM #__acctexp_metauser';
$db->setQuery($query);
$entries = xJ::getDBArray($db);
/*
* This may seem odd, but due to unforseen consequences, json encoding and decoding
* actually fixes some numeric properties so that we can switch them over to arrays,
* which is done with get_object_vars as its the quickest AND, uhm, dirtiest method.
* without the encoding and decoding, get_object_vars just purrs out an empty array.
*/
foreach ($entries as $eid) {
$meta = new metaUserDB();
$meta->load($eid);
if (!empty($meta->params)) {
if (is_object($meta->params)) {
if (is_object($meta->params->mi)) {
$new = get_object_vars(json_decode(json_encode($meta->params->mi)));
$meta->params->mi = $new;
}
}
public function Action()
{
$db = JFactory::getDBO();
$found = array();
$query = 'SELECT count(*)' . ' FROM #__acctexp_invoices' . ' WHERE `created_date` < \'' . date('Y-m-d H:i:s', strtotime("-" . $_POST['cutoff'] . " months", (int) gmdate('U'))) . '\'' . ' AND `transaction_date` = \'0000-00-00 00:00:00\'';
$db->setQuery($query);
$found['total_old'] = $db->loadResult();
$query = 'SELECT `id`' . ' FROM #__acctexp_plans' . ' WHERE `active` = \'0\'';
$db->setQuery($query);
$dbaplans = xJ::getDBArray($db);
$found['total_unpub'] = 0;
if (!empty($dbaplans)) {
$query = 'SELECT count(*)' . ' FROM #__acctexp_invoices' . ' WHERE `transaction_date` = \'0000-00-00 00:00:00\'' . ' AND `usage` IN (' . implode(',', $dbaplans) . ')';
$db->setQuery($query);
$found['total_unpub'] = $db->loadResult();
}
$query = 'SELECT `id`' . ' FROM #__acctexp_plans' . ' WHERE `visible` = \'0\'';
$db->setQuery($query);
$dbxplans = xJ::getDBArray($db);
$found['total_invis'] = 0;
if (!empty($dbxplans)) {
$query = 'SELECT count(*)' . ' FROM #__acctexp_invoices' . ' WHERE `transaction_date` = \'0000-00-00 00:00:00\'' . ' AND `usage` IN (' . implode(',', $dbxplans) . ')';
$db->setQuery($query);
$found['total_invis'] = $db->loadResult();
}
if (!empty($_POST['delete'])) {
$return = '<p>Deleted a total of ' . $found['total_old'] . ' invoices older than ' . $_POST['cutoff'] . ' months.<p>';
$query = 'DELETE' . ' FROM #__acctexp_invoices' . ' WHERE `created_date` < \'' . date('Y-m-d H:i:s', strtotime("-" . $_POST['cutoff'] . " months", (int) gmdate('U'))) . '\'' . ' AND `transaction_date` = \'0000-00-00 00:00:00\'';
$db->setQuery($query);
$db->query();
if (!empty($_POST['delete_unpub']) && !empty($dbaplans)) {
$query = 'SELECT count(*)' . ' FROM #__acctexp_invoices' . ' WHERE `transaction_date` = \'0000-00-00 00:00:00\'' . ' AND `usage` IN (' . implode(',', $dbaplans) . ')';
$db->setQuery($query);
$found['total_unpub'] = $db->loadResult();
if (!empty($found['total_unpub'])) {
$query = 'DELETE' . ' FROM #__acctexp_invoices' . ' WHERE `transaction_date` = \'0000-00-00 00:00:00\'' . ' AND `usage` IN (' . implode(',', $dbaplans) . ')';
$db->setQuery($query);
$db->query();
$return .= '<p>Deleted ' . $found['total_unpub'] . ' unpaid invoices that reference payment plans that are unpublished.<p>';
} else {
$return .= '<p>Found no further unpaid invoices that reference payment plans that are unpublished.<p>';
}
}
if (!empty($_POST['delete_invis']) && !empty($dbxplans)) {
$query = 'SELECT count(*)' . ' FROM #__acctexp_invoices' . ' WHERE `transaction_date` = \'0000-00-00 00:00:00\'' . ' AND `usage` IN (' . implode(',', $dbxplans) . ')';
$db->setQuery($query);
$found['total_invis'] = $db->loadResult();
if (!empty($found['total_invis'])) {
$query = 'DELETE' . ' FROM #__acctexp_invoices' . ' WHERE `transaction_date` = \'0000-00-00 00:00:00\'' . ' AND `usage` IN (' . implode(',', $dbxplans) . ')';
$db->setQuery($query);
$db->query();
$return .= '<p>Deleted ' . $found['total_invis'] . ' unpaid invoices that reference payment plans that are invisible.<p>';
} else {
$return .= '<p>Found no further unpaid invoices that reference payment plans that are invisible.<p>';
}
}
return $return;
} else {
$return = "";
if (!empty($found['total_old'])) {
$return .= '<p>Found ' . $found['total_old'] . ' unpaid invoices older than ' . $_POST['cutoff'] . ' months.<p>';
} else {
$return .= '<p>Found no unpaid invoices older than ' . $_POST['cutoff'] . ' months.<p>';
}
if (!empty($found['total_unpub'])) {
$return .= '<p>Found ' . $found['total_unpub'] . ' unpaid invoices that reference payment plans that are unpublished.<p>';
} else {
$return .= '<p>Found no unpaid invoices that reference payment plans that are unpublished.<p>';
}
if (!empty($found['total_invis'])) {
$return .= '<p>Found ' . $found['total_invis'] . ' unpaid invoices that reference payment plans that are invisible.<p>';
} else {
$return .= '<p>Found no unpaid invoices that reference payment plans that are invisible.<p>';
}
$return .= '<p>Note: Individual counts may overlap as outdated invoices are often both referencing unpublished and invisible plans.<p>';
return $return;
}
}
public function Action()
{
$db = JFactory::getDBO();
$entries = 0;
$planlist = array();
$pplist = array();
$processors = array('authorize_arb', 'google_checkout', 'hsbc', 'iats', 'paypal_wpp', 'sparkassen_internetkasse', 'usaepay');
$query = 'SELECT `id`' . ' FROM #__acctexp_invoices' . ' WHERE `method` IN (' . implode(',', $processors) . ')';
$db->setQuery($query);
$invoices = xJ::getDBArray($db);
foreach ($invoices as $id) {
$invoice = new Invoice();
$invoice->load($id);
// Skip non-rebilled
if (count($invoice->transactions) < 2) {
continue;
}
foreach ($invoice->transactions as $tid => $transaction) {
if (!$tid) {
// Skip first entry
continue;
}
if (!empty($_POST['safe'])) {
$query = 'SELECT `id`' . ' FROM #__acctexp_log_history' . ' WHERE transaction_date = \'' . $transaction->timestamp . '\'' . ' AND proc_name = \'' . $transaction->processor . '\'' . ' AND invoice_number = \'' . $invoice->invoice_number . '\'';
$db->setQuery($query);
$hasentry = $db->loadResult();
if ($hasentry) {
continue;
}
}
$entries++;
if (!empty($_POST['create'])) {
$entry = new logHistory();
$user = new cmsUser();
$user->load($invoice->userid);
if (!isset($planlist[$invoice->usage])) {
$plan = new SubscriptionPlan();
$plan->load($invoice->usage);
$planlist[$invoice->usage] = $plan;
}
if (!isset($pplist[$invoice->method])) {
$pp = new SubscriptionPlan();
$pp->load($invoice->method);
$pplist[$invoice->method] = $pp;
}
if ($pplist[$invoice->method]->id) {
$entry->proc_id = $pplist[$invoice->method]->id;
$entry->proc_name = $pplist[$invoice->method]->processor_name;
}
$entry->user_id = $user->id;
$entry->user_name = $user->username;
if ($planlist[$invoice->usage]->id) {
$entry->plan_id = $planlist[$invoice->usage]->id;
$entry->plan_name = $planlist[$invoice->usage]->name;
}
$entry->transaction_date = $transaction->timestamp;
$entry->amount = $transaction->amount;
$entry->invoice_number = $invoice->invoice_number;
$entry->response = 'Created by the Rebuild Rebills Tool';
$entry->cleanup();
$entry->check();
$entry->store();
}
}
}
if (empty($entries)) {
if ($_POST['create']) {
return "No Invoices found to create History Entries from.";
} else {
return "No Invoices with data found.";
}
} else {
if ($_POST['create']) {
return $entries . " History Entries created.";
} else {
return "No History Entries created, found " . $entries . " that can be converted (select 'Create' from the settings above and carry out the query again)";
}
}
}
static function getActiveListbyList($milist)
{
if (empty($milist)) {
return array();
}
$db = JFactory::getDBO();
$milist = array_unique($milist);
$query = 'SELECT `id`' . ' FROM #__acctexp_microintegrations' . ' WHERE `id` IN (' . xJ::escape($db, implode(',', $milist)) . ')' . ' AND `active` = \'1\'' . ' ORDER BY `ordering` ASC';
$db->setQuery($query);
return xJ::getDBArray($db);
}
public function loadJSuser()
{
$db = JFactory::getDBO();
$query = 'SELECT `id`' . ' FROM #__community_fields' . ' WHERE `type` != \'group\'';
$db->setQuery($query);
$ids = xJ::getDBArray($db);
$query = 'SELECT `field_id`, `value`' . ' FROM #__community_fields_values' . ' WHERE `field_id` IN (' . implode(',', $ids) . ')' . ' AND `user_id` = \'' . (int) $this->userid . '\'';
$db->setQuery($query);
$fields = $db->loadObjectList();
$this->jsUser = array();
foreach ($ids as $fid) {
foreach ($fields as $field) {
if ($field->field_id == $fid) {
$this->jsUser[$fid] = $field->value;
}
}
if (!isset($this->jsUser[$fid])) {
$this->jsUser[$fid] = null;
}
$this->rewrite['user_js_' . $fid] = $this->jsUser[$fid];
}
if (!empty($this->jsUser)) {
$this->hasJSprofile = true;
}
}
public function Action()
{
if (empty($_POST['start_date'])) {
return null;
}
$db = JFactory::getDBO();
$query = 'SELECT `id`' . ' FROM #__acctexp_microintegrations' . ' WHERE `class_name` LIKE \'%mi_supporttimetracker%\'';
$db->setQuery($query);
$mis = xJ::getDBArray($db);
$planlist = array();
foreach ($mis as $mi) {
$plans = MicroIntegrationHandler::getPlansbyMI($mi);
$planlist = array_merge($planlist, $plans);
}
$planlist = array_unique($planlist);
$userlist = array();
foreach ($planlist as $planid) {
$users = SubscriptionPlanHandler::getPlanUserlist($planid);
$userlist = array_merge($userlist, $users);
}
$userlist = array_unique($userlist);
$start_timeframe = strtotime($_POST['start_date'] . ' 00:00:00');
if (!empty($_POST['end_date'])) {
$end_timeframe = strtotime($_POST['end_date'] . ' 23:59:59');
} else {
$end_timeframe = (int) gmdate('U');
}
$unused = $used = 0;
$minordebt = 0;
$minordebtthresh = 0;
$minordebtth = 0;
$minordebtlist = array();
$majordebt = 0;
$majordebtlist = array();
$historylist = array();
foreach ($userlist as $userid) {
$db->setQuery('SELECT a.id, a.name, a.username, b.custom_params' . ' FROM #__users AS a' . ' LEFT JOIN `#__acctexp_metauser` AS b ON a.id = b.userid' . ' WHERE a.id = ' . $userid . ' AND b.custom_params <> ""');
$user = $db->loadObject();
if (empty($user->id)) {
continue;
}
$uparams = unserialize(base64_decode($user->custom_params));
if (!empty($uparams['support_minutes_history'])) {
if (is_array($uparams['support_minutes_history'])) {
foreach ($uparams['support_minutes_history'] as $history) {
if ($history['tstamp'] > $start_timeframe && $history['tstamp'] <= $end_timeframe) {
if (!empty($history['userid']) && $history['minutes_used']) {
$add = array();
$add['userid'] = $user->id;
$add['name'] = $user->name;
$add['username'] = $user->username;
$historylist[$history['userid']][] = array_merge($history, $add);
}
}
}
}
}
if (!empty($uparams['support_minutes_used'])) {
$utotal = $uparams['support_minutes'] - $uparams['support_minutes_used'];
} else {
$utotal = $uparams['support_minutes'];
}
if ($utotal < 0) {
if ($utotal < -30) {
$majordebtlist[] = array('userid' => $user->id, 'username' => $user->username, 'debt' => $utotal);
$majordebt += $utotal;
} elseif ($utotal < -14) {
$minordebtlist[] = array('userid' => $user->id, 'username' => $user->username, 'debt' => $utotal);
$minordebt += $utotal;
} else {
$minordebtthresh++;
$minordebtth += $utotal;
$minordebt += $utotal;
}
}
if (!empty($uparams['support_minutes'])) {
$unused += $uparams['support_minutes'] - $uparams['support_minutes_used'];
}
if (!empty($uparams['support_minutes_used'])) {
$used += $uparams['support_minutes_used'];
}
}
$return = "";
foreach ($historylist as $userid => $history_list) {
if (empty($history_list)) {
continue;
}
$total_minutes = 0;
$metaUser = new metaUser($userid);
$return .= '<h1>' . $metaUser->cmsUser->name . '</h1>';
$return .= '<table class="adminlist">';
$return .= '<tr><th>Date</th><th>Username</th><th>Time Used</th><th>Details</th></tr>';
$history_list = $this->historySort($history_list);
foreach ($history_list as $history) {
$userlink = '<a href="';
$userlink .= JURI::base() . 'index.php?option=com_acctexp&task=edit&entity=Membership&userid=' . $history['userid'];
$userlink .= '">';
$userlink .= $history['name'] . ' (' . $history['username'] . ')';
$userlink .= '</a>';
$return .= '<tr>';
$return .= '<td>' . date('Y-m-d H:i:s', $history['tstamp']) . '</td>';
$return .= '<td>' . $userlink . '</td>';
$return .= '<td>' . $history['minutes_used'] . '</td>';
$return .= '<td>' . $history['details'] . '</td>';
$return .= '</tr>';
$total_minutes += $history['minutes_used'];
}
$return .= '<tr><td><strong>TOTAL</strong></td><td></td><td><strong>' . $total_minutes . '</strong> (' . round($total_minutes / 60, 2) . ' hours)</td><td></td></tr>';
$return .= '</table><br /><br />';
}
$return .= '<p>Total used minutes: <strong>' . $used . '</strong> (' . round($used / 60, 2) . ' hours)</p>';
$return .= '<p>Total unused minutes: ' . $unused . ' (' . round($unused / 60, 2) . ' hours)</p>';
$return .= '<p>Total minor debt: ' . $minordebt . ' (' . round($minordebt / 60, 2) . ' hours)</p>';
$minordebtlist = $this->debtSort($minordebtlist);
foreach ($minordebtlist as $user) {
$return .= '<a href="' . JURI::base() . 'index.php?option=com_acctexp&task=edit&entity=Membership&userid=' . $user['userid'] . '">' . $user['username'] . ' (' . $user['debt'] . ' minutes)</a> ';
}
$return .= '<p>' . $minordebtthresh . ' Users below the -15 minute threshhold, total debt: ' . $minordebtth . ' (' . round($majordebt / 60, 2) . ' hours)</p>';
$return .= '<p>Total major debt: ' . $majordebt . ' (' . round($majordebt / 60, 2) . ' hours)</p>';
$majordebtlist = $this->debtSort($majordebtlist);
foreach ($majordebtlist as $user) {
$return .= '<a href="' . JURI::base() . 'index.php?option=com_acctexp&task=edit&entity=Membership&userid=' . $user['userid'] . '">' . $user['username'] . ' (' . $user['debt'] . ' minutes)</a> ';
}
return $return;
}
/**
* @param integer $userid
* @param integer $start
* @param integer $limit
*/
static function InvoiceIdList($userid, $start, $limit, $sort = '`transaction_date` DESC')
{
$db = JFactory::getDBO();
$query = 'SELECT `id`' . ' FROM #__acctexp_invoices' . ' WHERE `userid` = \'' . $userid . '\'' . ' AND `active` = \'1\'' . ' ORDER BY ' . $sort . ', `id` DESC' . ' LIMIT ' . $start . ',' . $limit;
$db->setQuery($query);
return xJ::getDBArray($db);
}
if (in_array($app->getCfg('dbprefix') . "acctexp", $tables)) {
// create new primary and expiration fields
$eucaInstalldb->addColifNotExists('primary', "int(4) NOT NULL default '0'", 'subscr');
$eucaInstalldb->addColifNotExists('expiration', "datetime NULL default '0000-00-00 00:00:00'", 'subscr');
// All Subscriptions are primary
$query = 'UPDATE #__acctexp_subscr' . ' SET `primary` = \'1\'';
$db->setQuery($query);
$db->query();
// copy expiration date
$query = 'UPDATE #__acctexp_subscr as a' . ' INNER JOIN #__acctexp as b ON a.userid = b.userid' . ' SET a.expiration = b.expiration';
$db->setQuery($query);
$db->query();
// Get plans
$query = 'SELECT `id`' . ' FROM #__acctexp_plans';
$db->setQuery($query);
$pplans = xJ::getDBArray($db);
// Assign new make_primary flag to all old plans
foreach ($pplans as $planid) {
$subscription_plan = new SubscriptionPlan();
$subscription_plan->addParams(array('make_primary' => 1));
}
// delete old expiration table
$eucaInstalldb->dropTableifExists('acctexp', false);
}
// fix for 0.12.4.15f mistake
$eucaInstalldb->addColifNotExists('primary', "int(1) NOT NULL default '0'", 'subscr');
$eucaInstalldb->addColifNotExists('subscr_id', "int(11) NULL", 'invoices');
$eucaInstalldb->addColifNotExists('conditions', "text NULL", 'invoices');
$eucaInstalldb->addColifNotExists('subscr_id', "int(11) NULL", 'invoices');
$eucaInstalldb->addColifNotExists('conditions', "text NULL", 'invoices');
$eucaInstalldb->addColifNotExists('invoice_number_format', "varchar(64)", 'invoices');
