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 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;
}
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 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);
}
$db->setQuery("UPDATE #__acctexp_itemxgroup SET group_id='1' WHERE group_id='0'");
$db->query();
// Fixing secondary invoice numbers for CCBill
$query = 'SELECT id FROM #__acctexp_config_processors WHERE name = \'ccbill\'';
$db->setQuery($query);
$ccid = $db->loadResult();
// Checking whether CCBill is installed at all
if ($ccid) {
// Get all history entries for CCBill
$query = 'SELECT id FROM #__acctexp_log_history WHERE proc_id = \'' . $ccid . '\'';
$db->setQuery($query);
$list = xJ::getDBArray($db);
if (!empty($list)) {
foreach ($list as $hid) {
$history = new logHistory();
$history->load($hid);
$params = parameterHandler::decode(stripslashes($history->response));
// Check for the parameters we need
if (isset($params['subscription_id']) && isset($params['invoice'])) {
$query = 'UPDATE #__acctexp_invoices SET `secondary_ident` = \'' . $params['subscription_id'] . '\' WHERE invoice_number = \'' . $params['invoice'] . '\'';
$db->setQuery($query);
$db->query();
}
}
}
}
// Haunted by ghosts of xmas past
$query = 'SELECT `id` FROM #__acctexp_subscr WHERE `params` LIKE \'%_jsoon%\'';
$db->setQuery($query);
$list = xJ::getDBArray($db);
if (!empty($list)) {
