function query()
{
extract($this->options, EXTR_SKIP);
$where = array();
$where[] = "o.created BETWEEN '" . sDB::mkdatetime($starts) . "' AND '" . sDB::mkdatetime($ends) . "'";
$where = join(" AND ", $where);
$id = $this->timecolumn('o.created');
$orders_table = ShoppDatabaseObject::tablename('purchase');
$purchased_table = ShoppDatabaseObject::tablename('purchased');
$query = "SELECT CONCAT({$id}) AS id,\n\t\t\t\t\t\t\tUNIX_TIMESTAMP(o.created) as period,\n\t\t\t\t\t\t\tSUM( ( SELECT SUM(p.quantity) FROM {$purchased_table} AS p WHERE o.id = p.purchase ) ) AS items,\n\t\t\t\t\t\t\tCOUNT(DISTINCT o.id) AS orders,\n\t\t\t\t\t\t\tSUM(o.subtotal) as subtotal,\n\t\t\t\t\t\t\tSUM(o.discount) as discounts\n\t\t\t\t\tFROM {$orders_table} AS o\n\t\t\t\t\tWHERE {$where}\n\t\t\t\t\tGROUP BY CONCAT({$id})";
return $query;
}
function query()
{
extract($this->options, EXTR_SKIP);
$where = array();
$where[] = "o.created BETWEEN '" . sDB::mkdatetime($starts) . "' AND '" . sDB::mkdatetime($ends) . "'";
$where[] = "o.txnstatus IN ('authed', 'captured', 'CHARGED')";
$where = join(" AND ", $where);
$id = $this->timecolumn('o.created');
$orders_table = ShoppDatabaseObject::tablename('purchase');
$purchased_table = ShoppDatabaseObject::tablename('purchased');
$query = "SELECT CONCAT({$id}) AS id,\n\t\t\t\t\t\t\tUNIX_TIMESTAMP(o.created) AS period,\n\t\t\t\t\t\t\tCOUNT(DISTINCT o.id) AS orders,\n\t\t\t\t\t\t\tSUM(o.subtotal) AS subtotal,\n\t\t\t\t\t\t\tSUM(o.tax) AS tax,\n\t\t\t\t\t\t\tSUM(o.freight) AS shipping,\n\t\t\t\t\t\t\tSUM(o.discount) AS discounts,\n\t\t\t\t\t\t\tSUM(o.total) AS total,\n\t\t\t\t\t\t\tAVG(o.total) AS orderavg,\n\t\t\t\t\t\t\tSUM( (SELECT SUM(p.quantity) FROM {$purchased_table} AS p WHERE o.id = p.purchase) ) AS items,\n\t\t\t\t\t\t\t(SELECT AVG(p.unitprice) FROM {$purchased_table} AS p WHERE o.id = p.purchase) AS itemavg\n\t\t\t\t\tFROM {$orders_table} AS o\n\t\t\t\t\tWHERE {$where}\n\t\t\t\t\tGROUP BY CONCAT({$id})";
return $query;
}
public function query()
{
extract($this->options, EXTR_SKIP);
$where = array();
$where[] = "o.created BETWEEN '" . sDB::mkdatetime($starts) . "' AND '" . sDB::mkdatetime($ends) . "'";
$where[] = "o.txnstatus IN ('authed', 'captured', 'CHARGED')";
$where = join(" AND ", $where);
$id = $this->timecolumn('o.created');
$orders_table = ShoppDatabaseObject::tablename('purchase');
$purchased_table = ShoppDatabaseObject::tablename('purchased');
$query = "SELECT CONCAT({$id}) AS id,\n\t\t\t\t\t\t\tUNIX_TIMESTAMP(o.created) as period,\n\t\t\t\t\t\t\tCOUNT(DISTINCT o.id) AS orders,\n\t\t\t\t\t\t\tSUM(o.subtotal) as subtotal,\n\t\t\t\t\t\t\tSUM(o.tax) as tax,\n\t\t\t\t\t\t\tSUM(p1.taxable) as taxable,\n\t\t\t\t\t\t\tAVG(p2.rate) as rate\n\t\t\t\t\tFROM {$orders_table} AS o\n\t\t\t\t\tLEFT JOIN (SELECT purchase, SUM(p.total) as taxable FROM {$purchased_table} AS p WHERE p.unittax > 0 GROUP BY purchase) p1 ON p1.purchase = o.id\n\t\t\t\t\tLEFT JOIN (SELECT purchase, AVG(p.unittax/p.unitprice) as rate FROM {$purchased_table} AS p WHERE p.unittax > 0 GROUP BY purchase) p2 ON p2.purchase = o.id\n\t\t\t\t\tWHERE {$where}\n\t\t\t\t\tGROUP BY CONCAT({$id})";
return $query;
}
function query()
{
$this->options = array_merge(array('orderby' => 'orders', 'order' => 'desc'), $this->options);
extract($this->options, EXTR_SKIP);
$where = array();
$where[] = "o.created BETWEEN '" . sDB::mkdatetime($starts) . "' AND '" . sDB::mkdatetime($ends) . "'";
$where[] = "o.txnstatus IN ('authed','captured')";
$where = join(" AND ", $where);
if (!in_array($order, array('asc', 'desc'))) {
$order = 'desc';
}
if (!in_array($orderby, array('orders', 'sold', 'grossed'))) {
$orderby = 'orders';
}
$ordercols = "{$orderby} {$order}";
$id = "o.cardtype";
$purchase_table = ShoppDatabaseObject::tablename('purchase');
$query = "SELECT CONCAT({$id}) AS id,\n\t\t\t\t\t\t\tCOUNT(DISTINCT o.id) AS orders,\n\t\t\t\t\t\t\tSUM(o.total) AS grossed\n\t\t\t\t\tFROM {$purchase_table} AS o\n\t\t\t\t\tWHERE {$where}\n\t\t\t\t\tGROUP BY CONCAT({$id}) ORDER BY {$ordercols}";
return $query;
}
function query()
{
$this->options = array_merge(array('orderby' => 'orders', 'order' => 'desc'), $this->options);
extract($this->options, EXTR_SKIP);
$where = array();
$where[] = "o.created BETWEEN '" . sDB::mkdatetime($starts) . "' AND '" . sDB::mkdatetime($ends) . "'";
$where = join(" AND ", $where);
if (!in_array($order, array('asc', 'desc'))) {
$order = 'desc';
}
if (!in_array($orderby, array('orders', 'sold', 'grossed'))) {
$orderby = 'orders';
}
$ordercols = "{$orderby} {$order}";
$id = 'c.id';
$purchase_table = ShoppDatabaseObject::tablename('purchase');
$purchased_table = ShoppDatabaseObject::tablename('purchased');
$customer_table = ShoppDatabaseObject::tablename('customer');
$query = "SELECT {$id} AS id,\n\t\t\t\t\t\t\tCONCAT(c.firstname,' ',c.lastname) AS customer,\n\t\t\t\t\t\t\tSUM( (SELECT SUM(p.quantity) FROM {$purchased_table} AS p WHERE o.id = p.purchase) ) AS sold,\n\t\t\t\t\t\t\tCOUNT(DISTINCT o.id) AS orders,\n\t\t\t\t\t\t\tSUM(o.total) AS grossed\n\t\t\t\t\tFROM {$purchase_table} as o\n\t\t\t\t\tINNER JOIN {$customer_table} AS c ON c.id=o.customer\n\t\t\t\t\tWHERE {$where}\n\t\t\t\t\tGROUP BY {$id} ORDER BY {$ordercols}";
return $query;
}
public function query()
{
$this->options = array_merge(array('orderby' => 'orders', 'order' => 'desc'), $this->options);
extract($this->options, EXTR_SKIP);
$where = array();
$where[] = "o.created BETWEEN '" . sDB::mkdatetime($starts) . "' AND '" . sDB::mkdatetime($ends) . "'";
$where[] = "orders.txnstatus IN ('authed','captured')";
$where = join(" AND ", $where);
if (!in_array($order, array('asc', 'desc'))) {
$order = 'desc';
}
if (!in_array($orderby, array('orders', 'sold', 'grossed'))) {
$orderby = 'orders';
}
$ordercols = "{$orderby} {$order}";
$id = "o.product,' ',o.price";
$purchase_table = ShoppDatabaseObject::tablename('purchase');
$purchased_table = ShoppDatabaseObject::tablename('purchased');
$product_table = WPDatabaseObject::tablename(ShoppProduct::$table);
$price_table = ShoppDatabaseObject::tablename('price');
$query = "SELECT CONCAT({$id}) AS id,\n\t\t\t\t\t\t\tCONCAT(p.post_title,' ', IF(pr.context != 'product',pr.label,'')) AS product,\n\t\t\t\t\t\t\tpr.sku as sku,\n\t\t\t\t\t\t\tSUM(o.quantity) AS sold,\n\t\t\t\t\t\t\tCOUNT(DISTINCT o.purchase) AS orders,\n\t\t\t\t\t\t\tSUM(o.total) AS grossed\n\t\t\t\t\tFROM {$purchased_table} AS o INNER JOIN {$purchase_table} AS orders ON orders.id=o.purchase\n\t\t\t\t\tJOIN {$product_table} AS p ON p.ID=o.product\n\t\t\t\t\tJOIN {$price_table} AS pr ON pr.id=o.price\n\t\t\t\t\tWHERE {$where}\n\t\t\t\t\tGROUP BY CONCAT({$id}) ORDER BY {$ordercols}";
return $query;
}
/**
* Handles orders list loading
*
* @author Jonathan Davis
* @since 1.2.1
*
* @return void
**/
public function loader()
{
if (!current_user_can('shopp_orders')) {
return;
}
$defaults = array('page' => false, 'deleting' => false, 'selected' => false, 'update' => false, 'newstatus' => false, 'pagenum' => 1, 'paged' => 1, 'per_page' => 20, 'start' => '', 'end' => '', 'status' => false, 's' => '', 'range' => '', 'startdate' => '', 'enddate' => '');
$args = array_merge($defaults, $_GET);
extract($args, EXTR_SKIP);
$url = add_query_arg(array_merge($_GET, array('page' => $this->Admin->pagename('orders'))), admin_url('admin.php'));
if ($page == "shopp-orders" && !empty($deleting) && !empty($selected) && is_array($selected) && current_user_can('shopp_delete_orders')) {
foreach ($selected as $selection) {
$Purchase = new ShoppPurchase($selection);
$Purchase->load_purchased();
foreach ($Purchase->purchased as $purchased) {
$Purchased = new ShoppPurchased($purchased->id);
$Purchased->delete();
}
$Purchase->delete();
}
if (count($selected) == 1) {
$this->notice(__('Order deleted.', 'Shopp'));
} else {
$this->notice(sprintf(__('%d orders deleted.', 'Shopp'), count($selected)));
}
}
$statusLabels = shopp_setting('order_status');
if (empty($statusLabels)) {
$statusLabels = array('');
}
$txnstatus_labels = Lookup::txnstatus_labels();
if ($update == "order" && !empty($selected) && is_array($selected)) {
foreach ($selected as $selection) {
$Purchase = new ShoppPurchase($selection);
$Purchase->status = $newstatus;
$Purchase->save();
}
if (count($selected) == 1) {
$this->notice(__('Order status updated.', 'Shopp'));
} else {
$this->notice(sprintf(__('%d orders updated.', 'Shopp'), count($selected)));
}
}
$Purchase = new ShoppPurchase();
$offset = get_option('gmt_offset') * 3600;
if (!empty($start)) {
$startdate = $start;
list($month, $day, $year) = explode("/", $startdate);
$starts = mktime(0, 0, 0, $month, $day, $year);
}
if (!empty($end)) {
$enddate = $end;
list($month, $day, $year) = explode("/", $enddate);
$ends = mktime(23, 59, 59, $month, $day, $year);
}
$pagenum = absint($paged);
$start = $per_page * ($pagenum - 1);
$where = array();
$joins = array();
if (!empty($status) || $status === '0') {
$where[] = "status='" . sDB::escape($status) . "'";
}
if (!empty($s)) {
$s = stripslashes($s);
$search = array();
if (preg_match_all('/(\\w+?)\\:(?="(.+?)"|(.+?)\\b)/', $s, $props, PREG_SET_ORDER) > 0) {
foreach ($props as $query) {
$keyword = sDB::escape(!empty($query[2]) ? $query[2] : $query[3]);
switch (strtolower($query[1])) {
case "txn":
$search[] = "txnid='{$keyword}'";
break;
case "company":
$search[] = "company LIKE '%{$keyword}%'";
break;
case "gateway":
$search[] = "gateway LIKE '%{$keyword}%'";
break;
case "cardtype":
$search[] = "cardtype LIKE '%{$keyword}%'";
break;
case "address":
$search[] = "(address LIKE '%{$keyword}%' OR xaddress='%{$keyword}%')";
break;
case "city":
$search[] = "city LIKE '%{$keyword}%'";
break;
case "province":
case "state":
$search[] = "state='{$keyword}'";
break;
case "zip":
case "zipcode":
case "postcode":
$search[] = "postcode='{$keyword}'";
break;
case "country":
$search[] = "country='{$keyword}'";
break;
case "promo":
case "discount":
$meta_table = ShoppDatabaseObject::tablename(ShoppMetaObject::$table);
$joins[$meta_table] = "INNER JOIN {$meta_table} AS m ON m.parent = o.id AND context='purchase'";
$search[] = "m.value LIKE '%{$keyword}%'";
break;
case "product":
$purchased = ShoppDatabaseObject::tablename(Purchased::$table);
$joins[$purchased] = "INNER JOIN {$purchased} AS p ON p.purchase = o.id";
$search[] = "p.name LIKE '%{$keyword}%' OR p.optionlabel LIKE '%{$keyword}%' OR p.sku LIKE '%{$keyword}%'";
break;
}
}
if (empty($search)) {
$search[] = "(id='{$s}' OR CONCAT(firstname,' ',lastname) LIKE '%{$s}%')";
}
$where[] = "(" . join(' OR ', $search) . ")";
} elseif (strpos($s, '@') !== false) {
$where[] = "email='" . sDB::escape($s) . "'";
} else {
$where[] = "(id='{$s}' OR CONCAT(firstname,' ',lastname) LIKE '%" . sDB::escape($s) . "%')";
}
}
if (!empty($starts) && !empty($ends)) {
$where[] = "created BETWEEN '" . sDB::mkdatetime($starts) . "' AND '" . sDB::mkdatetime($ends) . "'";
}
if (!empty($customer)) {
$where[] = "customer=" . intval($customer);
}
$where = !empty($where) ? "WHERE " . join(' AND ', $where) : '';
$joins = join(' ', $joins);
$countquery = "SELECT count(*) as total,SUM(IF(txnstatus IN ('authed','captured'),total,NULL)) AS sales,AVG(IF(txnstatus IN ('authed','captured'),total,NULL)) AS avgsale FROM {$Purchase->_table} AS o {$joins} {$where} ORDER BY o.created DESC LIMIT 1";
$this->ordercount = sDB::query($countquery, 'object');
$query = "SELECT o.* FROM {$Purchase->_table} AS o {$joins} {$where} ORDER BY created DESC LIMIT {$start},{$per_page}";
$this->orders = sDB::query($query, 'array', 'index', 'id');
$num_pages = ceil($this->ordercount->total / $per_page);
if ($paged > 1 && $paged > $num_pages) {
Shopp::redirect(add_query_arg('paged', null, $url));
}
}
/**
* Prepares a ShoppDatabaseObject for entry into the database
*
* Iterates the properties of a ShoppDatabaseObject and formats the data
* according to the datatype meta available for the property to create
* an array of key/value pairs that are easy concatenate into a valid
* SQL query
*
* @author Jonathan Davis
* @since 1.0
*
* @param ShoppDatabaseObject $Object The object to be prepared
* @return array Data structure ready for query building
**/
public static function prepare($Object, array $mapping = array())
{
$data = array();
// Go through each data property of the object
$properties = get_object_vars($Object);
foreach ($properties as $var => $value) {
$property = isset($mapping[$var]) ? $mapping[$var] : $var;
if (!isset($Object->_datatypes[$property])) {
continue;
}
// If the property is has a _datatype
// it belongs in the database and needs
// to be prepared
// Process the data
switch ($Object->_datatypes[$property]) {
case 'string':
// Escape characters in strings as needed
if (is_array($value) || is_object($value)) {
$data[$property] = "'" . addslashes(serialize($value)) . "'";
} else {
$data[$property] = "'" . sDB::escape($value) . "'";
}
break;
case 'list':
// If value is empty, skip setting the field
// so it inherits the default value in the db
if (!empty($value)) {
$data[$property] = "'{$value}'";
}
break;
case 'date':
// If it's an empty date, set it to the current time
if (is_null($value)) {
$value = current_time('mysql');
// If the date is an integer, convert it to an
// sql YYYY-MM-DD HH:MM:SS format
} elseif (!empty($value) && (is_int($value) || intval($value) > 86400)) {
$value = sDB::mkdatetime(intval($value));
}
$data[$property] = "'{$value}'";
break;
case 'float':
// Sanitize without rounding to protect precision
if (is_string($value) && method_exists('ShoppCore', 'floatval')) {
$value = ShoppCore::floatval($value, false);
} else {
$value = floatval($value);
}
case 'int':
// Normalize for MySQL float representations (@see bug #853)
// Force formating with full stop (.) decimals
// Trim excess 0's followed by trimming (.) when there is no fractional value
$value = rtrim(rtrim(number_format((double) $value, 6, '.', ''), '0'), '.');
$data[$property] = "'{$value}'";
if (empty($value)) {
$data[$property] = "'0'";
}
// Special exception for id fields
if ('id' == $property && empty($value)) {
$data[$property] = "NULL";
}
break;
default:
// Anything not needing processing
// passes through into the structure
$data[$property] = "'{$value}'";
}
}
return $data;
}
/**
* Renders the order stats widget
*
* @author Jonathan Davis
* @since 1.0
*
* @return void
**/
public static function stats_widget($args = false)
{
$ranges = array('today' => __('Today', 'Shopp'), 'week' => __('This Week', 'Shopp'), 'month' => __('This Month', 'Shopp'), 'quarter' => __('This Quarter', 'Shopp'), 'year' => __('This Year', 'Shopp'), 'yesterday' => __('Yesterday', 'Shopp'), 'lastweek' => __('Last Week', 'Shopp'), 'last30' => __('Last 30 Days', 'Shopp'), 'last90' => __('Last 3 Months', 'Shopp'), 'lastmonth' => __('Last Month', 'Shopp'), 'lastquarter' => __('Last Quarter', 'Shopp'), 'lastyear' => __('Last Year', 'Shopp'));
$defaults = array('before_widget' => '', 'before_title' => '', 'widget_name' => '', 'after_title' => '', 'after_widget' => '', 'range' => isset($_GET['shopp-stats-range']) ? $_GET['shopp-stats-range'] : '');
$args = array_merge($defaults, (array) $args);
extract($args, EXTR_SKIP);
if (!$range || !isset($ranges[strtolower($range)])) {
$range = 'last30';
}
$purchasetable = ShoppDatabaseObject::tablename(ShoppPurchase::$table);
$now = current_time('timestamp');
// $offset = get_option( 'gmt_offset' ) * 3600;
$daytimes = 86400;
$day = date('j', $now);
$month = date('n', $now);
$year = date('Y', $now);
$end = $now;
list($weekstart, $weekend) = array_values(get_weekstartend(current_time('mysql')));
switch ($range) {
case 'today':
$start = mktime(0, 0, 0, $month, $day, $year);
break;
case 'week':
$start = $weekstart;
$end = $weekend;
break;
case 'month':
$start = mktime(0, 0, 0, $month, 1, $year);
break;
case 'quarter':
$start = mktime(0, 0, 0, $month - (3 - $month % 3), 1, $year);
break;
case 'year':
$start = mktime(0, 0, 0, 1, 1, $year);
break;
case 'yesterday':
$start = mktime(0, 0, 0, $month, $day - 1, $year);
$end = mktime(23, 59, 59, $month, $day - 1, $year);
break;
case 'lastweek':
$start = $weekstart - 7 * $daytimes;
$end = $weekstart - 1;
break;
case 'last7':
$start = $now - 7 * $daytimes;
break;
case 'last30':
$start = $now - 30 * $daytimes;
break;
case 'last90':
$start = $now - 90 * $daytimes;
break;
case 'lastmonth':
$start = mktime(0, 0, 0, $month - 1, 1, $year);
$end = mktime(0, 0, 0, $month, 0, $year);
break;
case 'lastquarter':
$start = mktime(0, 0, 0, $month - (3 - $month % 3) - 3, 1, $year);
$end = mktime(23, 59, 59, date('n', $start) + 3, 0, $year);
break;
case 'lastyear':
$start = mktime(0, 0, 0, $month, 1, $year - 1);
$end = mktime(23, 59, 59, 1, 0, $year);
break;
}
// Include authorizations, captures and old 1.1 tranaction status CHARGED in sales data
$salestatus = array("'authed'", "'captured'", "'CHARGED'");
$txnstatus = "txnstatus IN (" . join(',', $salestatus) . ")";
$daterange = "created BETWEEN '" . sDB::mkdatetime($start) . "' AND '" . sDB::mkdatetime($end) . "'";
$query = "SELECT count(id) AS orders,\n\t\t\t\t\t\tSUM(total) AS sales,\n\t\t\t\t\t\tAVG(total) AS average,\n\t\t \t\t\t\tSUM(IF({$daterange},1,0)) AS wkorders,\n\t\t\t\t\t\tSUM(IF({$daterange},total,0)) AS wksales,\n\t\t\t\t\t\tAVG(IF({$daterange},total,null)) AS wkavg\n \t\t\t\t\tFROM {$purchasetable} WHERE {$txnstatus}";
$cached = get_transient('shopp_dashboard_stats_' . $range);
if (empty($cached)) {
$results = sDB::query($query);
$RecentBestsellers = new BestsellerProducts(array('range' => array($start, $end), 'show' => 5));
$RecentBestsellers->load(array('pagination' => false));
$RecentBestsellers->maxsold = 0;
foreach ($RecentBestsellers as $product) {
$RecentBestsellers->maxsold = max($RecentBestsellers->maxsold, $product->sold);
}
$LifeBestsellers = new BestsellerProducts(array('show' => 5));
$LifeBestsellers->load(array('pagination' => false));
$LifeBestsellers->maxsold = 0;
foreach ($LifeBestsellers as $product) {
$LifeBestsellers->maxsold = max($LifeBestsellers->maxsold, $product->sold);
}
set_transient('shopp_dashboard_stats_' . $range, array($results, $RecentBestsellers, $LifeBestsellers), 300);
} else {
list($results, $RecentBestsellers, $LifeBestsellers) = $cached;
}
echo $before_widget;
echo $before_title;
echo $widget_name;
echo $after_title;
$orderscreen = add_query_arg('page', ShoppAdmin::pagename('orders'), admin_url('admin.php'));
$productscreen = add_query_arg(array('page' => ShoppAdmin::pagename('products')), admin_url('admin.php'));
?>
<div class="table"><table>
<tr><th colspan="2"><form action="<?php
echo admin_url('index.php');
?>
">
<select name="shopp-stats-range" id="shopp-stats-range">
<?php
echo menuoptions($ranges, $range, true);
?>
</select>
<button type="submit" id="filter-button" name="filter" value="order" class="button-secondary hide-if-js"><?php
_e('Filter', 'Shopp');
?>
</button>
</form>
</th><th colspan="2"><?php
_e('Lifetime', 'Shopp');
?>
</th></tr>
<tbody>
<tr><td class="amount"><a href="<?php
echo esc_url($orderscreen);
?>
"><?php
echo (int) $results->wkorders;
?>
</a></td><td class="label"><?php
echo _n('Order', 'Orders', (int) $results->wkorders, 'Shopp');
?>
</td>
<td class="amount"><a href="<?php
echo esc_url($orderscreen);
?>
"><?php
echo (int) $results->orders;
?>
</a></td><td class="label"><?php
echo _n('Order', 'Orders', (int) $results->orders, 'Shopp');
?>
</td></tr>
<tr><td class="amount"><a href="<?php
echo esc_url($orderscreen);
?>
"><?php
echo money($results->wksales);
?>
</a></td><td class="label"><?php
_e('Sales', 'Shopp');
?>
</td>
<td class="amount"><a href="<?php
echo esc_url($orderscreen);
?>
"><?php
echo money($results->sales);
?>
</a></td><td class="label"><?php
_e('Sales', 'Shopp');
?>
</td></tr>
<tr><td class="amount"><a href="<?php
echo esc_url($orderscreen);
?>
"><?php
echo money($results->wkavg);
?>
</a></td><td class="label"><?php
_e('Average Order', 'Shopp');
?>
</td>
<td class="amount"><a href="<?php
echo esc_url($orderscreen);
?>
"><?php
echo money($results->average);
?>
</a></td><td class="label"><?php
_e('Average Order', 'Shopp');
?>
</td></tr>
<?php
if (!empty($RecentBestsellers->products) || !empty($LifeBestsellers->products)) {
?>
<tr>
<th colspan="2"><?php
printf(__('Bestsellers %s', 'Shopp'), $ranges[$range]);
?>
</th>
<th colspan="2"><?php
printf(__('Lifetime Bestsellers', 'Shopp'), $ranges[$range]);
?>
</th>
</tr>
<?php
reset($RecentBestsellers);
reset($LifeBestsellers);
$firstrun = true;
while (true) {
list($recentid, $recent) = each($RecentBestsellers->products);
list($lifetimeid, $lifetime) = each($LifeBestsellers->products);
if (!$recent && !$lifetime) {
break;
}
?>
<tr>
<?php
if (empty($RecentBestsellers->products) && $firstrun) {
echo '<td colspan="2" rowspan="5">' . __('None', 'Shopp') . '</td>';
}
?>
<?php
if (!empty($recent->id)) {
?>
<td class="salesgraph">
<div class="bar" style="width:<?php
echo $recent->sold / $RecentBestsellers->maxsold * 100;
?>
%;"><?php
echo $recent->sold;
?>
</div>
</td>
<td>
<a href="<?php
echo esc_url(add_query_arg('view', 'bestselling', $productscreen));
?>
"><?php
echo esc_html($recent->name);
?>
</a>
</td>
<?php
}
?>
<?php
if (empty($LifeBestsellers->products) && $firstrun) {
echo '<td colspan="2" rowspan="5">' . __('None', 'Shopp') . '</td>';
}
?>
<?php
if (!empty($lifetime->id)) {
?>
<td class="salesgraph">
<div class="bar" style="width:<?php
echo $lifetime->sold / $LifeBestsellers->maxsold * 100;
?>
%;"><?php
echo $lifetime->sold;
?>
</div>
</td>
<td>
<a href="<?php
echo esc_url(add_query_arg('view', 'bestselling', $productscreen));
?>
"><?php
echo esc_html($lifetime->name);
?>
</a>
</td>
<?php
}
?>
</tr>
<?php
$firstrun = false;
}
?>
<?php
}
?>
</tbody></table></div>
<script type="text/javascript">
jQuery(document).ready(function($){$('#shopp-stats-range').change(function(){$(this).parents('form').submit();});});
</script>
<?php
echo $after_widget;
}
/**
* Sets the status of a set of products
*
* @author Jonathan Davis
* @since 1.2
*
* @param array $ids Set of product IDs to update
* @param string $status The status to set: publish, draft, trash
* @return boolean
**/
static function publishset(array $ids, $status)
{
if (empty($ids) || !is_array($ids)) {
return false;
}
$settings = array('publish', 'draft', 'trash');
if (!in_array($status, $settings)) {
return false;
}
$table = WPShoppObject::tablename(self::$table);
$time = current_time('timestamp');
$post_date_gmt = sDB::mkdatetime($time + get_option('gmt_offset') * 3600);
$post_date = sDB::mkdatetime($time);
sDB::query("UPDATE {$table} SET post_status='{$status}', post_date='{$post_date}', post_date_gmt='{$post_date_gmt}', post_modified='{$post_date}', post_modified_gmt='{$post_date_gmt}' WHERE ID in (" . join(',', $ids) . ")");
foreach ($ids as $id) {
// Recount taxonomy counts #2968
$Post = get_post($id);
switch ($status) {
case 'trash':
do_action('wp_trash_post', $id);
break;
default:
do_action('save_post', $id, $Post);
break;
}
if (function_exists('clean_post_cache')) {
clean_post_cache($id);
}
wp_transition_post_status($status, $Product->status, $Post);
}
return true;
}
public function smart(array $options = array())
{
if (isset($options['range']) && is_array($options['range'])) {
$start = $options['range'][0];
$end = $options['range'][1];
if (!$end) {
$end = current_time('timestamp');
}
$purchased = ShoppDatabaseObject::tablename(Purchased::$table);
$this->loading['columns'] = "COUNT(*) AS sold";
$this->loading['joins'] = array($purchased => "INNER JOIN {$purchased} as pur ON pur.product=p.id");
$this->loading['where'] = array("pur.created BETWEEN '" . sDB::mkdatetime($start) . "' AND '" . sDB::mkdatetime($end) . "'");
$this->loading['orderby'] = 'sold DESC';
$this->loading['groupby'] = 'pur.product';
} else {
$this->loading['where'] = array(BestsellerProducts::threshold() . " < s.sold");
$this->loading['order'] = 'bestselling';
// Use overall bestselling stats
$this->loading = array_merge($options, $this->loading);
}
}
public function prepare_items()
{
$defaults = array('page' => false, 'deleting' => false, 'selected' => false, 'update' => false, 'newstatus' => false, 'pagenum' => 1, 'paged' => 1, 'per_page' => 20, 'start' => '', 'end' => '', 'status' => false, 's' => '', 'range' => '', 'startdate' => '', 'enddate' => '');
$args = array_merge($defaults, $this->request());
extract($args, EXTR_SKIP);
// $url = $this->url($_GET);
$statusLabels = shopp_setting('order_status');
if (empty($statusLabels)) {
$statusLabels = array('');
}
$txnstatus_labels = Lookup::txnstatus_labels();
$Purchase = new ShoppPurchase();
$offset = get_option('gmt_offset') * 3600;
if ($this->request('start')) {
list($month, $day, $year) = explode("/", $this->request('start'));
$starts = mktime(0, 0, 0, $month, $day, $year);
}
if ($this->request('end')) {
list($month, $day, $year) = explode("/", $this->request('end'));
$ends = mktime(23, 59, 59, $month, $day, $year);
}
$pagenum = absint($paged);
$start = $per_page * ($pagenum - 1);
$where = array();
$joins = array();
if (!empty($status) || '0' === $status) {
$where[] = "status='" . sDB::escape($status) . "'";
}
if (!empty($s)) {
$s = stripslashes($s);
$search = array();
if (preg_match_all('/(\\w+?)\\:(?="(.+?)"|(.+?)\\b)/', $s, $props, PREG_SET_ORDER) > 0) {
foreach ($props as $query) {
$keyword = sDB::escape(!empty($query[2]) ? $query[2] : $query[3]);
switch (strtolower($query[1])) {
case "txn":
$search[] = "txnid='{$keyword}'";
break;
case "company":
$search[] = "company LIKE '%{$keyword}%'";
break;
case "gateway":
$search[] = "gateway LIKE '%{$keyword}%'";
break;
case "cardtype":
$search[] = "cardtype LIKE '%{$keyword}%'";
break;
case "address":
$search[] = "(address LIKE '%{$keyword}%' OR xaddress='%{$keyword}%')";
break;
case "city":
$search[] = "city LIKE '%{$keyword}%'";
break;
case "province":
case "state":
$search[] = "state='{$keyword}'";
break;
case "zip":
case "zipcode":
case "postcode":
$search[] = "postcode='{$keyword}'";
break;
case "country":
$search[] = "country='{$keyword}'";
break;
case "promo":
case "discount":
$meta_table = ShoppDatabaseObject::tablename(ShoppMetaObject::$table);
$joins[$meta_table] = "INNER JOIN {$meta_table} AS m ON m.parent = o.id AND context='purchase'";
$search[] = "m.value LIKE '%{$keyword}%'";
break;
case "product":
$purchased = ShoppDatabaseObject::tablename(Purchased::$table);
$joins[$purchased] = "INNER JOIN {$purchased} AS p ON p.purchase = o.id";
$search[] = "p.name LIKE '%{$keyword}%' OR p.optionlabel LIKE '%{$keyword}%' OR p.sku LIKE '%{$keyword}%'";
break;
}
}
if (empty($search)) {
$search[] = "(id='{$s}' OR CONCAT(firstname,' ',lastname) LIKE '%{$s}%')";
}
$where[] = "(" . join(' OR ', $search) . ")";
} elseif (strpos($s, '@') !== false) {
$where[] = "email='" . sDB::escape($s) . "'";
} else {
$where[] = "(id='{$s}' OR CONCAT(firstname,' ',lastname) LIKE '%" . sDB::escape($s) . "%')";
}
}
if (!empty($starts) && !empty($ends)) {
$where[] = "created BETWEEN '" . sDB::mkdatetime($starts) . "' AND '" . sDB::mkdatetime($ends) . "'";
}
if (!empty($customer)) {
$where[] = "customer=" . intval($customer);
}
$where = !empty($where) ? "WHERE " . join(' AND ', $where) : '';
$joins = join(' ', $joins);
$countquery = "SELECT count(*) as total,SUM(IF(txnstatus IN ('authed','captured'),total,NULL)) AS sales,AVG(IF(txnstatus IN ('authed','captured'),total,NULL)) AS avgsale FROM {$Purchase->_table} AS o {$joins} {$where} ORDER BY o.created DESC LIMIT 1";
$this->ordercount = sDB::query($countquery, 'object');
$query = "SELECT o.* FROM {$Purchase->_table} AS o {$joins} {$where} ORDER BY created DESC LIMIT {$start},{$per_page}";
$this->items = sDB::query($query, 'array', 'index', 'id');
$num_pages = ceil($this->ordercount->total / $per_page);
if ($paged > 1 && $paged > $num_pages) {
Shopp::redirect(add_query_arg('paged', null, $url));
}
$Gateways = Shopp::object()->Gateways;
$this->gateways = array_merge($Gateways->modules, array('ShoppFreeOrder' => $Gateways->freeorder));
$this->statuses = (array) shopp_setting('order_status');
$this->txnstatuses = ShoppLookup::txnstatus_labels();
// Convert other date formats to numeric but preserve the order of the month/day/year or day/month/year
$date_format = get_option('date_format');
$date_format = preg_replace("/[^A-Za-z0-9]/", '', $date_format);
// Force month display to numeric with leading zeros
$date_format = str_replace(array('n', 'F', 'M'), 'm/', $date_format);
// Force day display to numeric with leading zeros
$date_format = str_replace(array('j'), 'd/', $date_format);
// Force year display to 4-digits
$date_format = str_replace('y', 'Y/', $date_format);
$this->dates = trim($date_format, '/');
$this->set_pagination_args(array('total_items' => $this->ordercount->total, 'total_pages' => $this->ordercount->total / $per_page, 'per_page' => $per_page));
}
/**
* Post activation maintenance
*
* @author Jonathan Davis
* @since 1.2.6
*
* @return void
**/
function maintenance()
{
global $wpdb;
$db_version = ShoppSettings::dbversion();
if ($db_version <= 1149) {
// Set mass packaging setting to 'all' for current realtime shipping rates {@see bug #1835}
if ('mass' == shopp_setting('shipping_packaging')) {
shopp_set_setting('shipping_packaging', 'all');
}
// Fix all product modified timestamps (for 1.2.6)
$post_type = 'shopp_product';
$post_modified = sDB::mkdatetime(current_time('timestamp'));
$post_modified_gmt = sDB::mkdatetime(current_time('timestamp') + get_option('gmt_offset') * 3600);
sDB::query("UPDATE {$wpdb->posts} SET post_modified='{$post_modified}', post_modified_gmt='{$post_modified_gmt}' WHERE post_type='{$post_type}' AND post_modified='0000-00-00 00:00:00'");
}
}
/**
* Sets the status of a set of products
*
* @author Jonathan Davis
* @since 1.2
*
* @param array $ids Set of product IDs to update
* @param string $status The status to set: publish, draft, trash
* @return boolean
**/
static function publishset(array $ids, $status)
{
if (empty($ids) || !is_array($ids)) {
return false;
}
$settings = array('publish', 'draft', 'trash');
if (!in_array($status, $settings)) {
return false;
}
$table = WPShoppObject::tablename(self::$table);
$time = current_time('timestamp');
$post_date_gmt = sDB::mkdatetime($time + get_option('gmt_offset') * 3600);
$post_date = sDB::mkdatetime($time);
sDB::query("UPDATE {$table} SET post_status='{$status}', post_date='{$post_date}', post_date_gmt='{$post_date_gmt}', post_modified='{$post_date}', post_modified_gmt='{$post_date_gmt}' WHERE ID in (" . join(',', $ids) . ")");
foreach ($ids as $id) {
// Recount taxonomy counts #2968
$laststatus = get_post_status($id);
$Post = new StdClass();
$Post->ID = $id;
$Post->post_type = ShoppProduct::$posttype;
wp_transition_post_status($status, $laststatus, $Post);
}
return true;
}
