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');