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)) {