Ensures only one instance of WC_Payment_Gateways is loaded or can be loaded.
| public static instance ( ) : WC_Payment_Gateways | ||
| 리턴 | WC_Payment_Gateways | Main instance |
public function load_gateways()
{
$gateways = WC_Payment_Gateways::instance()->payment_gateways;
$order = $this->get('gateway_order');
// some poorly written plugins will init WC_Payment_Gateways before WP init
// check to see if POS Cash Gateway is present, if not: re-init WC_Payment_Gateways
if (!in_array('WC_POS_Gateways_Cash', array_map('get_class', $gateways))) {
WC_Payment_Gateways::instance()->init();
$gateways = WC_Payment_Gateways::instance()->payment_gateways;
}
// reorder
$i = count($gateways);
foreach ($gateways as $gateway) {
if (isset($order[$gateway->id])) {
$ordered_gateways[$order[$gateway->id]] = $gateway;
} else {
$ordered_gateways[++$i] = $gateway;
}
$settings = new WC_POS_Admin_Settings_Gateways($gateway->id);
$settings->merge_settings($gateway);
apply_filters('woocommerce_pos_load_gateway', $gateway);
}
ksort($ordered_gateways, SORT_NUMERIC);
return $ordered_gateways;
}
/**
* Get enabled payment gateways.
*
* @return array Array of enabled gateways
*/
public function getEnabledPaymentGateways()
{
$_enabledGateways = array();
$gateways = \WC_Payment_Gateways::instance();
if (sizeof($gateways->payment_gateways) > 0) {
foreach ($gateways->payment_gateways() as $gateway) {
if ($this->isEnabled($gateway)) {
$_enabledGateways[$gateway->id] = $gateway;
}
}
}
return $_enabledGateways;
}
public function load_gateways()
{
$gateways = WC_Payment_Gateways::instance()->payment_gateways;
$order = $this->get_data('gateway_order');
// reorder
$i = count($gateways);
foreach ($gateways as $gateway) {
if (isset($order[$gateway->id])) {
$ordered_gateways[$order[$gateway->id]] = $gateway;
} else {
$ordered_gateways[++$i] = $gateway;
}
$settings = new WC_POS_Admin_Settings_Gateways($gateway->id);
$settings->merge_settings($gateway);
apply_filters('woocommerce_pos_load_gateway', $gateway);
}
ksort($ordered_gateways, SORT_NUMERIC);
return $ordered_gateways;
}
public function __construct($options)
{
global $wpdb;
$this->wpdb = $wpdb;
$this->import = $options['import'];
$this->count = $options['count'];
$this->xml = $options['xml'];
$this->logger = $options['logger'];
$this->chunk = $options['chunk'];
$this->xpath = $options['xpath_prefix'];
$this->prices_include_tax = 'yes' === get_option('woocommerce_prices_include_tax', 'no');
$this->payment_gateways = WC_Payment_Gateways::instance()->get_available_payment_gateways();
$this->shipping_methods = WC()->shipping->get_shipping_methods();
if (class_exists('WC_Shipping_Zones')) {
$zones = WC_Shipping_Zones::get_zones();
if (!empty($zones)) {
foreach ($zones as $zone_id => $zone) {
if (!empty($zone['shipping_methods'])) {
foreach ($zone['shipping_methods'] as $method) {
$this->shipping_zone_methods[] = $method;
}
}
}
} else {
$zone = new WC_Shipping_Zone(0);
$this->shipping_zone_methods = $zone->get_shipping_methods();
}
}
$tax_classes = array_filter(array_map('trim', explode("\n", get_option('woocommerce_tax_classes'))));
if ($tax_classes) {
// Add Standard tax class
if (!in_array('', $tax_classes)) {
$tax_classes[] = '';
}
foreach ($tax_classes as $class) {
foreach (WC_Tax::get_rates_for_tax_class(sanitize_title($class)) as $rate_key => $rate) {
$this->tax_rates[$rate->tax_rate_id] = $rate;
}
}
}
add_filter('wp_all_import_is_post_to_skip', array(&$this, 'wp_all_import_is_post_to_skip'), 10, 5);
add_filter('wp_all_import_combine_article_data', array(&$this, 'wp_all_import_combine_article_data'), 10, 4);
}
/**
* Gets valid tokens from the database based on user defined criteria.
* @param array $args
* @return array
*/
public static function get_tokens($args)
{
global $wpdb;
$args = wp_parse_args($args, array('token_id' => '', 'user_id' => '', 'gateway_id' => '', 'type' => ''));
$sql = "SELECT * FROM {$wpdb->prefix}woocommerce_payment_tokens";
$where = array('1=1');
if ($args['token_id']) {
$token_ids = array_map('absint', is_array($args['token_id']) ? $args['token_id'] : array($args['token_id']));
$where[] = "token_id IN ('" . implode("','", array_map('esc_sql', $token_ids)) . "')";
}
if ($args['user_id']) {
$where[] = 'user_id = ' . absint($args['user_id']);
}
if ($args['gateway_id']) {
$gateway_ids = array($args['gateway_id']);
} else {
$gateways = WC_Payment_Gateways::instance();
$gateway_ids = $gateways->get_payment_gateway_ids();
}
$gateway_ids[] = '';
$where[] = "gateway_id IN ('" . implode("','", array_map('esc_sql', $gateway_ids)) . "')";
if ($args['type']) {
$where[] = 'type = ' . esc_sql($args['type']);
}
$token_results = $wpdb->get_results($sql . ' WHERE ' . implode(' AND ', $where));
$tokens = array();
if (!empty($token_results)) {
foreach ($token_results as $token_result) {
$_token = self::get($token_result->token_id, $token_result);
if (!empty($_token)) {
$tokens[$token_result->token_id] = $_token;
}
}
}
return $tokens;
}
/**
* Get gateways class
* @return WC_Payment_Gateways
*/
public function payment_gateways()
{
return WC_Payment_Gateways::instance();
}
/**
* Save settings.
*/
public function save()
{
global $current_section;
$wc_payment_gateways = WC_Payment_Gateways::instance();
if (!$current_section) {
WC_Admin_Settings::save_fields($this->get_settings());
$wc_payment_gateways->process_admin_options();
} else {
foreach ($wc_payment_gateways->payment_gateways() as $gateway) {
if (in_array($current_section, array($gateway->id, sanitize_title(get_class($gateway))))) {
do_action('woocommerce_update_options_payment_gateways_' . $gateway->id);
$wc_payment_gateways->init();
}
}
}
}
function translate_payment_instructions($id)
{
if (function_exists('icl_translate')) {
$gateways = WC()->payment_gateways();
foreach ($gateways->payment_gateways as $key => $gateway) {
if ($gateway->id == $id) {
WC_Payment_Gateways::instance()->payment_gateways[$key]->instructions = icl_translate('woocommerce', $gateway->id . '_gateway_instructions', $gateway->instructions);
break;
}
}
}
}
function translate_payment_instructions($id)
{
$gateways = WC()->payment_gateways();
foreach ($gateways->payment_gateways as $key => $gateway) {
if ($gateway->id == $id && isset(WC_Payment_Gateways::instance()->payment_gateways[$key]->instructions)) {
do_action('wpml_register_single_string', 'woocommerce', $gateway->id . '_gateway_instructions', $gateway->instructions);
WC_Payment_Gateways::instance()->payment_gateways[$key]->instructions = apply_filters('wpml_translate_single_string', $gateway->instructions, 'woocommerce', $gateway->id . '_gateway_instructions');
break;
}
}
}
function register_gateway_strings($fields)
{
$wc_payment_gateways = WC_Payment_Gateways::instance();
foreach ($wc_payment_gateways->payment_gateways() as $gateway) {
if (isset($_POST['woocommerce_' . $gateway->id . '_enabled'])) {
$gateway_id = $gateway->id;
break;
}
}
if (isset($gateway_id)) {
do_action('wpml_register_single_string', 'woocommerce', $gateway_id . '_gateway_title', $fields['title']);
if (isset($fields['description'])) {
do_action('wpml_register_single_string', 'woocommerce', $gateway_id . '_gateway_description', $fields['description']);
}
if (isset($fields['instructions'])) {
do_action('wpml_register_single_string', 'woocommerce', $gateway_id . '_gateway_instructions', $fields['instructions']);
}
}
return $fields;
}
<div class="panel woocommerce_options_panel" id="payment_order_data" style="display:none;">
<div class="options_group hide_if_grouped">
<table class="form-table" style="max-width:none;">
<tr>
<td>
<!-- Payment Method -->
<div class="form-field">
<p class="form-field"><?php
_e('Payment Method', 'wpai_woocommerce_addon_plugin');
?>
</p>
<div class="form-field">
<select id="payment_method" name="pmwi_order[payment_method]" style="width: 200px; font-size: 14px !important;" class="switcher rad4">
<?php
$payment_gateways = WC_Payment_Gateways::instance()->get_available_payment_gateways();
$payment_gateways_for_tooltip = array();
foreach ($payment_gateways as $id => $gateway) {
echo '<option value="' . esc_attr($id) . '" ' . selected($id, $post['pmwi_order']['payment_method'], false) . '>' . esc_html($gateway->title) . '</option>';
$payment_gateways_for_tooltip[] = esc_attr($id);
}
?>
<option value="xpath" <?php
if ("xpath" == $post['pmwi_order']['payment_method']) {
echo 'selected="selected"';
}
?>
><?php
_e("Set with XPath", "wpai_woocommerce_addon_plugin");
?>
</option>
</select>
function hocwp_wc_insert_order($data)
{
$post_id = hocwp_get_value_by_key($data, 'post_id');
if (hocwp_id_number_valid($post_id)) {
$post = get_post($post_id);
if (is_a($post, 'WP_Post') && 'product' == $post->post_type) {
$product = wc_get_product($post_id);
$variable_product = new WC_Product_Variable($product);
$variations = $variable_product->get_available_variations();
$variation_args = array();
$variation_id = null;
foreach ($variations as $variation) {
$variation_id = $variation['variation_id'];
$variation_args['variation'] = $variation['attributes'];
}
$name = hocwp_get_value_by_key($data, 'name');
$phone = hocwp_get_value_by_key($data, 'phone');
$email = hocwp_get_value_by_key($data, 'email');
$address = hocwp_get_value_by_key($data, 'address');
$message = hocwp_get_value_by_key($data, 'message');
$name = hocwp_sanitize_first_and_last_name($name);
$attributes = hocwp_get_value_by_key($data, 'attributes');
$addresses = array('first_name' => $name['first_name'], 'last_name' => $name['last_name'], 'email' => $email, 'phone' => $phone, 'address_1' => $address);
$args = array('customer_note' => $message, 'created_via' => 'programmatically');
if (is_user_logged_in()) {
$current = wp_get_current_user();
$args['customer_id'] = $current->ID;
}
$order = wc_create_order($args);
$gateway = WC_Payment_Gateways::instance();
$gateways = $gateway->get_available_payment_gateways();
if (hocwp_array_has_value($gateways)) {
$gateway = current($gateways);
$order->set_payment_method($gateway);
}
$order->set_address($addresses);
$order->set_address($addresses, 'shipping');
if (hocwp_array_has_value($attributes) && hocwp_id_number_valid($variation_id)) {
foreach ($attributes as $attribute) {
$attribute_name = hocwp_get_value_by_key($attribute, 'name');
$attribute_value = hocwp_get_value_by_key($attribute, 'value');
if (!empty($attribute_name) && !empty($attribute_value)) {
if (isset($variation_args['variation'][$attribute_name])) {
$variation_args['variation'][$attribute_name] = $attribute_value;
}
}
}
$variation_product = new WC_Product_Variation($variation_id);
$order->add_product($variation_product, 1, $variation_args);
} else {
$order->add_product($product);
}
$order->record_product_sales();
$order->calculate_totals();
$order->payment_complete();
return $order;
}
}
return false;
}
function sr_query_sales($cumm_dates, $date_series, $post)
{
$params = !empty($post['params']) ? $post['params'] : array();
if (!wp_verify_nonce($params['security'], 'smart-reporter-security')) {
die('Security check');
}
global $wpdb, $sr_text_domain;
$returns = array();
// Initialize the return data
$returns['chart'] = $returns['kpi'] = array();
$returns['meta'] = array('start_date' => $cumm_dates['cp_start_date'], 'end_date' => $cumm_dates['cp_end_date'], 's_link' => admin_url() . 'edit.php?post_type=shop_order&source=sr&sdate=' . $cumm_dates['cp_start_date'] . '&edate=' . $cumm_dates['cp_end_date']);
$returns['chart']['period'] = $date_series;
$periods_count = count($returns['chart']['period']);
$p2i = array_flip($returns['chart']['period']);
$time_str = $cumm_dates['format'] == '%H' ? ':00:00' : '';
if (!empty($post['cmd']) && ($post['cmd'] == 'cumm_sales' || $post['cmd'] == 'sr_summary')) {
$date_col = $cumm_dates['format'] == '%H' ? 'created_time' : 'created_date';
$chart_keys = array('sales', 'orders', 'discount');
$payment_methods = $shipping_methods = array();
// For each payment and shipping method...
foreach ((array) WC_Payment_Gateways::instance()->get_available_payment_gateways() as $key => $value) {
$chart_keys[] = 'pm_' . $key . '_sales';
$chart_keys[] = 'pm_' . $key . '_orders';
$returns['kpi']['pm'][$key] = array('title' => __($value->get_title(), $sr_text_domain), 'sales' => 0, 'orders' => 0, 's_link' => '&s=' . $value->get_title() . '&s_col=payment_method&s_val=' . $key);
}
foreach ((array) WC_Shipping::instance()->get_shipping_methods() as $key => $value) {
$chart_keys[] = 'sm_' . $key . '_sales';
$chart_keys[] = 'sm_' . $key . '_orders';
$returns['kpi']['sm'][$key] = array('title' => __($value->get_title(), $sr_text_domain), 'sales' => 0, 'orders' => 0, 's_link' => '&s=' . $value->get_title() . '&s_col=shipping_method&s_val=' . $key);
}
// Initialize chart data to 0
foreach ($chart_keys as $value) {
$returns['chart'][$value] = array_fill(0, $periods_count, 0);
}
// KPIs are single item stats.. init for current and last period (lp_)
$kpis = array('sales', 'refunds', 'orders', 'qty', 'discount', 'tax', 'shipping', 'shipping_tax');
foreach ($kpis as $value) {
$returns['kpi'][$value] = 0;
$returns['kpi']['lp_' . $value] = 0;
}
// Bring in grouped results for sales, discounts etc - then loop and process
// LAST_PERIOD is special 'period' value for comparing current period data
// with previous period
$query = $wpdb->prepare("SELECT 'LAST_PERIOD' as period, \n\t\t\t\t\t\t\t\t\t\t\tSUM( CASE WHEN type = 'shop_order' AND status != 'wc-refunded' THEN 1 ELSE 0 END ) as orders, \n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN -1*total \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE total END), 0) AS sales,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN total \n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN type = 'shop_order_refund' THEN -1*total \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE 0 END), 0) AS refunds,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE qty END), 0) AS qty,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE discount+cart_discount END), 0) AS discount,\n\t\t\t\t\t\t\t\t\t\t0 AS tax,\n\t\t\t\t\t\t\t\t\t\t0 AS shipping,\n\t\t\t\t\t\t\t\t\t\t0 AS shipping_tax,\n\t\t\t\t\t\t\t\t\t '' AS payment_method, \n\t\t\t\t\t\t\t\t\t '' AS shipping_method \n\t\t\t\t\t\t\t\t\tFROM `{$wpdb->prefix}woo_sr_orders` \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tcreated_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-refunded')\n\t\t\t\t\t\t\t\t\tUNION\n\t\t\t\t\t\t\t\t\tSELECT concat(DATE_FORMAT(" . $date_col . ", '%s'), '" . $time_str . "') AS period,\n\t\t\t\t\t\t\t\t\t\t\tSUM( CASE WHEN type = 'shop_order' AND status != 'wc-refunded' THEN 1 ELSE 0 END ) as orders, \n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN -1*total \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE total END), 0) AS sales,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN total \n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN type = 'shop_order_refund' THEN -1*total \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE 0 END), 0) AS refunds,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE qty END), 0) AS qty,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE discount+cart_discount END), 0) AS discount,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE tax END), 0) AS tax,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE shipping END), 0) AS shipping,\n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM( CASE WHEN status = 'wc-refunded' THEN 0 \n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE shipping_tax END), 0) AS shipping_tax,\n\t\t\t\t\t\t\t\t\t payment_method, \n\t\t\t\t\t\t\t\t\t shipping_method \n\t\t\t\t\t\t\t\t\tFROM `{$wpdb->prefix}woo_sr_orders` \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tcreated_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-refunded') \n\t\t\t\t\t\t\t\t\tGROUP BY period, payment_method, shipping_method", $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['format'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
// The first row will always be last period data
$row = array_shift($results);
foreach ($kpis as $value) {
$returns['kpi']['lp_' . $value] = $row[$value];
}
// Loop and total up values now
foreach ($results as $row) {
if (!array_key_exists($row['period'], $p2i)) {
error_log('Smart Reporter: Invalid value for "period" in DB results - ' . $row['period']);
continue;
}
// Total up sales, refunds, qty etc...
foreach ($kpis as $key) {
$returns['kpi'][$key] += $row[$key];
}
// Index of this period - this will be used to position different chart data at this period's index
$i = $p2i[$row['period']];
// Set values in charts - for data other than payment / shipping methods
foreach ($chart_keys as $key) {
if (substr($key, 1, 2) != 'm_') {
// will match pm_ and sm_ both in single condition
$returns['chart'][$key][$i] += $row[$key];
}
}
// Set values for shipping and payment methods
foreach (array('pm' => $row['payment_method'], 'sm' => $row['shipping_method']) as $type => $method) {
foreach (array('sales', 'orders') as $f) {
$key = $type . '_' . $method . '_' . $f;
if (array_key_exists($key, $returns['chart'])) {
$row[$f] = $type == 'sm' && $f == 'sales' ? $row['shipping'] : $row[$f];
$returns['chart'][$key][$i] += $row[$f];
$returns['kpi'][$type][$method][$f] += $row[$f];
}
}
}
}
// sorting the pm and sm by sales
$returns['kpi']['pm'] = array_slice(sr_multidimensional_array_sort($returns['kpi']['pm'], 'sales', 'DESC'), 0, 5);
$returns['kpi']['sm'] = array_slice(sr_multidimensional_array_sort($returns['kpi']['sm'], 'sales', 'DESC'), 0, 5);
}
}
if (!empty($post['cmd']) && ($post['cmd'] == 'cumm_cust_prod' || $post['cmd'] == 'sr_summary')) {
$chart_keys = array();
$date_col = $cumm_dates['format'] == '%H' ? 'order_time' : 'order_date';
// KPIs are single item stats.. init for current and last period (lp_)
$kpis = array('car', 'carts', 'carts_prod', 'orders', 'orders_prod', 'corders', 'corders_prod', 'aipc', 'swc');
foreach ($kpis as $value) {
$returns['kpi'][$value] = 0;
if ($value == 'car' || $value == 'aipc' || $value == 'swc') {
$returns['kpi']['lp_' . $value] = 0;
}
}
// ###############################
// Top Customers
// ###############################
$query = $wpdb->prepare("SELECT MAX(customer_name) as name,\n\t\t\t\t\t\t\t\t\t\tMAX(billing_email) as email, \n\t\t\t\t\t\t\t\t\t\tCASE WHEN user_id > 0 THEN user_id ELSE billing_email END as user, \n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM(total), 0) AS sales \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tcreated_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold') \n\t\t\t\t\t\t\t\t\tGROUP BY user \n\t\t\t\t\t\t\t\t\tORDER BY sales DESC\n\t\t\t\t\t\t\t\t\tLIMIT 5", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$returns['kpi']['top_cust'] = array();
foreach ($results as $row) {
$returns['kpi']['top_cust'][] = array('name' => $row['name'], 'email' => $row['email'], 'sales' => $row['sales'], 's_link' => '&s=' . $row['email'] . ($row['user'] > 0 ? '&s_col=user_id&s_val=' . $row['user'] : '******' . $row['email']));
}
}
// ###############################
// Billing Countries
// ###############################
$query = $wpdb->prepare("SELECT SUM( CASE WHEN type = 'shop_order' THEN 1 ELSE 0 END ) as orders, \n\t\t\t\t\t\t\t\t\t\tIFNULL(SUM(total), 0) AS sales,\n\t\t\t\t\t\t\t\t\t\tbilling_country \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tcreated_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND status in ('wc-completed', 'wc-processing', 'wc-on-hold') \n\t\t\t\t\t\t\t\t\tGROUP BY billing_country", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$returns['kpi']['billing_country'] = array();
$returns['kpi']['billing_country']['sales'] = $returns['kpi']['billing_country']['orders'] = array();
foreach ($results as $row) {
if (empty($row['billing_country'])) {
continue;
}
$returns['kpi']['billing_country']['sales'][$row['billing_country']] = $row['sales'];
$returns['kpi']['billing_country']['orders'][$row['billing_country']] = $row['orders'];
}
}
// ###############################
// Top Products
// ###############################
// Confirm the handling of the partial refunds
$t_p_ids = $t_v_ids = array();
$returns['kpi']['top_prod'] = array('sales' => array(), 'qty' => array());
$tp_results = array();
$query = $wpdb->prepare("SELECT oi.product_id as product_id, \n\t\t\t\t\t\t\t\t\t\t\toi.variation_id as variation_id,\n\t\t\t\t\t\t\t\t\t\t SUM( oi.total ) as sales,\n\t\t\t\t\t\t\t\t\t\t SUM( oi.qty ) as qty \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_order_items oi\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\toi.order_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND oi.order_is_sale = 1\n\t\t\t\t\t\t\t\t\tAND (oi.type = 'S' OR oi.type = 'R')\n\t\t\t\t\t\t\t\t\tGROUP BY product_id, variation_id\n\t\t\t\t\t\t\t\t\tORDER BY sales DESC\n\t\t\t\t\t\t\t\t\tLIMIT 5", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
$id = $row['product_id'];
$t_p_ids[] = $id;
if (!empty($row['variation_id'])) {
$t_v_ids[$row['variation_id']] = $row['product_id'];
$id = $row['variation_id'];
}
$returns['kpi']['top_prod']['sales'][$id] = array('title' => '-', 'sales' => $row['sales'], 'qty' => $row['qty']);
}
$keys = array_keys($returns['kpi']['top_prod']['sales']);
// array_walk($keys, function(&$value, $key) { $value = 'tps_'. $value; });
array_walk($keys, 'format_top_prod_keys', 'tps_');
$chart_keys = count($keys) > 0 ? array_merge($chart_keys, $keys) : $chart_keys;
$prod_cond = !empty($t_p_ids) ? ' AND ( (product_id IN (' . implode(",", $t_p_ids) . ') AND variation_id = 0)' : '';
$prod_cond .= !empty($t_v_ids) ? (!empty($prod_cond) ? ' OR ' : ' AND ( ') . 'variation_id IN (' . implode(",", array_keys($t_v_ids)) . ')' : '';
$prod_cond .= !empty($prod_cond) ? ' ) ' : '';
// Query to get the dates wise sales for the top products
$query = $wpdb->prepare("SELECT CASE WHEN variation_id > 0 THEN variation_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE product_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tEND AS tps_id,\n\t\t\t\t\t\t\t\t\t\t\t\tconcat(DATE_FORMAT(" . $date_col . ", '%s'), '" . $time_str . "') AS period,\n\t\t\t\t\t\t\t\t\t\t\t\tSUM( total ) as sales \n\t\t\t\t\t\t\t\t\t\tFROM wp_woo_sr_order_items \n\t\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t\torder_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\t\tAND order_is_sale = 1\n\t\t\t\t\t\t\t\t\t\tAND (type = 'S' OR type = 'R')\n\t\t\t\t\t\t\t\t\t\t" . $prod_cond . "\n\t\t\t\t\t\t\t\t\t\tGROUP BY order_date, tps_id", $cumm_dates['format'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$tp_results = $wpdb->get_results($query, 'ARRAY_A');
}
$query = $wpdb->prepare("SELECT oi.product_id as product_id,\n\t\t\t\t\t\t\t\t\t\t\toi.variation_id as variation_id, \n\t\t\t\t\t\t\t\t\t\t SUM( oi.total ) as sales,\n\t\t\t\t\t\t\t\t\t\t SUM( oi.qty ) as qty \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_order_items oi\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\toi.order_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\tAND oi.order_is_sale = 1\n\t\t\t\t\t\t\t\t\tAND (oi.type = 'S' OR oi.type = 'R')\n\t\t\t\t\t\t\t\t\tGROUP BY oi.product_id\n\t\t\t\t\t\t\t\t\tORDER BY qty DESC\n\t\t\t\t\t\t\t\t\tLIMIT 5", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$v_ids = array();
foreach ($results as $row) {
$id = $row['product_id'];
$t_p_ids[] = $id;
if (!empty($row['variation_id'])) {
$t_v_ids[$row['variation_id']] = $row['product_id'];
$v_ids[] = $row['variation_id'];
$id = $row['variation_id'];
}
$returns['kpi']['top_prod']['qty'][$id] = array('title' => '-', 'sales' => $row['sales'], 'qty' => $row['qty']);
}
$keys = array_keys($returns['kpi']['top_prod']['qty']);
array_walk($keys, 'format_top_prod_keys', 'tpq_');
$chart_keys = count($keys) > 0 ? array_merge($chart_keys, $keys) : $chart_keys;
$prod_cond = !empty($t_p_ids) ? ' AND ( (product_id IN (' . implode(",", $t_p_ids) . ') AND variation_id = 0)' : '';
$prod_cond .= !empty($v_ids) ? (!empty($prod_cond) ? ' OR ' : ' AND ( ') . 'variation_id IN (' . implode(",", array_keys($v_ids)) . ')' : '';
$prod_cond .= !empty($prod_cond) ? ' ) ' : '';
// Query to get the dates wise qty for the top products
$query = $wpdb->prepare("SELECT CASE WHEN variation_id > 0 THEN variation_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE product_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tEND AS tpq_id,\n\t\t\t\t\t\t\t\t\t\t\t\tconcat(DATE_FORMAT(" . $date_col . ", '%s'), '" . $time_str . "') AS period,\n\t\t\t\t\t\t\t\t\t\t\t\tSUM( qty ) as qty\n\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_order_items \n\t\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t\torder_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t\t\t\t\t\tAND order_is_sale = 1\n\t\t\t\t\t\t\t\t\t\tAND (type = 'S' OR type = 'R')\n\t\t\t\t\t\t\t\t\t\t" . $prod_cond . "\n\t\t\t\t\t\t\t\t\t\tGROUP BY order_date, tpq_id", $cumm_dates['format'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
$tp_results = count($results) > 0 ? array_merge($tp_results, $results) : $tp_results;
}
// Initialize chart data to 0
if (count($tp_results) > 0) {
foreach ($chart_keys as $value) {
$returns['chart'][$value] = array_fill(0, $periods_count, 0);
}
}
// Loop and assign the chart data
if (count($tp_results) > 0) {
foreach ($tp_results as $row) {
if (!array_key_exists($row['period'], $p2i)) {
error_log('Smart Reporter: Invalid value for "period" in DB results - ' . $row['period']);
continue;
}
// Index of this period - this will be used to position different chart data at this period's index
$i = $p2i[$row['period']];
// Set values in charts
if (!empty($row['tps_id'])) {
$returns['chart']['tps_' . $row['tps_id']][$i] += !empty($row['sales']) ? $row['sales'] : 0;
} else {
if (!empty($row['tpq_id'])) {
$returns['chart']['tpq_' . $row['tpq_id']][$i] += !empty($row['qty']) ? $row['qty'] : 0;
}
}
}
}
// ###############################
// Cart Abandonment Rate
// ###############################
$query = $wpdb->prepare("SELECT CASE \n\t\t\t\t\t\t\t\t\t\t\t\tWHEN last_update_time >= unix_timestamp('%s') THEN 'C' \n\t\t\t\t\t\t\t\t\t\t\t\tWHEN last_update_time >= unix_timestamp('%s') THEN 'L' \n\t\t\t\t\t\t\t\t\t\t\tEND as period,\n\t\t\t\t\t\t\t\t\t\tcount(distinct( CASE WHEN cart_is_abandoned = 0 THEN concat('O#', order_id) ELSE concat('C#', cart_id) END ) ) as count, \n\t\t\t\t\t\t\t\t\t\tSUM(qty) as items, \n\t\t\t\t\t\t\t\t\t\tcart_is_abandoned \n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_cart_items\n\t\t\t\t\t\t\t\t\tWHERE (last_update_time between unix_timestamp('%s') and unix_timestamp('%s')) \n\t\t\t\t\t\t\t\t\t\tOR (last_update_time between unix_timestamp('%s') and unix_timestamp('%s'))\n\t\t\t\t\t\t\t\t\tGROUP BY period, cart_is_abandoned", $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
if ($row['period'] == 'C') {
$c_acarts = $row['cart_is_abandoned'] == 1 ? $row['count'] : 0;
$returns['kpi']['carts'] += $row['count'];
$returns['kpi']['carts_prod'] += $row['items'];
} else {
$l_acarts = $row['cart_is_abandoned'] == 1 ? $row['count'] : 0;
$l_carts += $row['count'];
}
}
$returns['kpi']['car'] = !empty($returns['kpi']['carts']) ? $c_acarts / $returns['kpi']['carts'] : $c_acarts;
$returns['kpi']['lp_car'] = !empty($l_carts) ? $l_acarts / $l_carts : $l_acarts;
}
// ###############################
// Top Abandoned Products
// ###############################
$returns['kpi']['top_aprod'] = array();
// get the top abandoned products
$r_aprod = sr_get_abandoned_products(array('cp_start_date' => $cumm_dates['cp_start_date'], 'cp_end_date' => $cumm_dates['cp_end_date'], 'security' => $params['security'], 'limit' => 'LIMIT 5'));
$returns['kpi']['top_aprod'] = $r_aprod['a_prod'];
if (count($returns['kpi']['top_aprod']) > 0) {
// initializing the chart data
foreach (array_keys($returns['kpi']['top_aprod']) as $key) {
$returns['chart']['tapq_' . $key] = array_fill(0, $periods_count, 0);
}
// code for getting the chart data
if ($r_aprod['a_flag'] == true) {
$query = $wpdb->prepare("SELECT product_id as id,\n\t\t\t\t\t\t\t\t\t\t\t\t\tSUM(qty) as aqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(FROM_UNIXTIME(last_update_time), '%s') AS period\n\t\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_cart_items\n\t\t\t\t\t\t\t\t\t\t\tWHERE cart_is_abandoned = 1\n\t\t\t\t\t\t\t\t\t\t\t\tAND last_update_time between unix_timestamp('%s') and unix_timestamp('%s')\n\t\t\t\t\t\t\t\t\t\t\t\tAND product_id IN (" . implode(",", array_keys($returns['kpi']['top_aprod'])) . ")\n\t\t\t\t\t\t\t\t\t\t\tGROUP BY period, id", $cumm_dates['format'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
if (!array_key_exists($row['period'], $p2i)) {
error_log('Smart Reporter: Invalid value for "period" in DB results - ' . $row['period']);
continue;
}
// Index of this period - this will be used to position different chart data at this period's index
$i = $p2i[$row['period']];
// Set values in charts
$returns['chart']['tapq_' . $row['id']][$i] += $row['aqty'];
}
}
}
//get the variation ids
if (count(array_keys($returns['kpi']['top_aprod'])) > 0) {
$query = "SELECT id, post_parent\n\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}posts\n\t\t\t\t\t\t\t\tWHERE post_parent > 0\n\t\t\t\t\t\t\t\t\tAND id IN (" . implode(",", array_keys($returns['kpi']['top_aprod'])) . ")\n\t\t\t\t\t\t\t\tGROUP BY id";
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
$t_v_ids[$row['id']] = $row['post_parent'];
$t_p_ids[] = $row['post_parent'];
}
$t_p_ids = array_merge($t_p_ids, array_keys(array_diff_key($returns['kpi']['top_aprod'], $t_v_ids)));
} else {
$t_p_ids = array_merge($t_p_ids, array_keys($returns['kpi']['top_aprod']));
}
}
}
// Code for getting the product title
$data = sr_get_prod_title(array(&$returns['kpi']['top_prod']['sales'], &$returns['kpi']['top_prod']['qty'], &$returns['kpi']['top_aprod']), array('t_p_ids' => $t_p_ids, 't_v_ids' => $t_v_ids, 'security' => $params['security']));
// ###############################
// Top Coupons
// ###############################
$returns['kpi']['top_coupons'] = array();
$query = $wpdb->prepare("SELECT COUNT( oi.order_item_name ) AS count,\n\t\t\t\t\t\t\t\t\t\tSUM(oim.meta_value) AS amt,\n\t\t\t\t\t\t\t\t\t\toi.order_item_name AS name\n\t\t\t\t\t FROM {$wpdb->prefix}woo_sr_orders AS so\n\t\t\t\t\t \tJOIN {$wpdb->prefix}woocommerce_order_items as oi ON ( so.order_id = oi.order_id )\n\t\t\t\t\t \tJOIN {$wpdb->prefix}woocommerce_order_itemmeta as oim \n\t\t\t\t\t \t\tON (oi.order_item_id = oim.order_item_id \n\t\t\t\t\t \t\t\t\tAND oim.meta_key = 'discount_amount' )\n\t\t\t\t\t WHERE so.created_date BETWEEN '%s' AND '%s'\n\t\t\t\t\t AND so.status in ('wc-completed', 'wc-processing', 'wc-on-hold')\n\t\t\t\t\t AND oi.order_item_type = 'coupon'\n\t\t\t\t\t GROUP BY oi.order_item_name\n\t\t\t\t\t ORDER BY count DESC, amt DESC\n\t\t\t\t\t LIMIT 5", $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
$returns['kpi']['top_coupons'][] = array('title' => $row['name'], 'sales' => $row['amt'], 'count' => $row['count'], 's_link' => '&s=' . $row['name'] . '&s_col=order_item_name&s_val=' . $row['name']);
}
}
// ###############################
// Sales Funnel
// ###############################
$query = $wpdb->prepare("SELECT IFNULL( COUNT( DISTINCT( CASE WHEN so.created_date >= '%s' THEN so.order_id END ) ), 0) AS orders,\n\t\t \t\t\t\t\t\t\tIFNULL( COUNT( DISTINCT( CASE WHEN so.created_date >= '%s' THEN so.order_id END ) ), 0) AS lp_orders,\n\t\t\t\t\t\t\t\t\t\tIFNULL( SUM( CASE WHEN so.created_date >= '%s' THEN soim.qty END ), 0) AS orders_prod,\n\t\t\t\t\t\t\t\t\t\tIFNULL( SUM( CASE WHEN so.created_date >= '%s' THEN soim.qty END ), 0) AS lp_orders_prod,\n\t\t\t\t\t\t\t\t\t\tIFNULL( COUNT( DISTINCT(CASE WHEN so.created_date >= '%s' AND so.status = 'wc-completed' THEN so.order_id END) ), 0) AS corders,\n\t\t\t\t\t\t\t\t\t\tIFNULL( SUM( CASE WHEN so.created_date >= '%s' AND so.status = 'wc-completed' THEN soim.qty END ), 0) AS corders_prod\n\t\t\t\t\t FROM {$wpdb->prefix}woo_sr_orders AS so\n\t\t\t\t\t \tJOIN {$wpdb->prefix}woo_sr_order_items as soim\n\t\t\t\t\t \t\tON (so.order_id = soim.order_id\n\t\t\t\t \t\t\t\tAND so.type = 'shop_order'\n\t\t\t\t \t\t\t\tAND soim.order_is_sale = 1\n\t\t\t\t \t\t\t\tAND soim.type = 'S')\n\t\t\t\t\t WHERE (so.created_date BETWEEN '%s' AND '%s'\n\t\t\t\t \t\tOR so.created_date BETWEEN '%s' AND '%s')", $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
$lp_orders = $lp_orders_prod = 0;
if (count($results) > 0) {
foreach ($kpis as $value) {
$returns['kpi'][$value] = !empty($results[0][$value]) ? $results[0][$value] : $returns['kpi'][$value];
}
$lp_orders = $results[0]['lp_orders'];
$lp_orders_prod = $results[0]['lp_orders_prod'];
}
// ###############################
// Sales With Coupons
// ###############################
$query = $wpdb->prepare("SELECT IFNULL( COUNT( DISTINCT( CASE WHEN so.created_date >= '%s' THEN so.order_id END ) ), 0) AS cp_co,\n\t\t \t\t\t\t\t\t\t\tIFNULL( COUNT( DISTINCT( CASE WHEN so.created_date >= '%s' THEN so.order_id END ) ), 0) AS lp_co\n\t\t\t \t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders AS so\n\t\t\t\t \tJOIN {$wpdb->prefix}woocommerce_order_items as oi ON ( oi.order_id = so.order_id )\n\t\t\t\t WHERE (so.created_date BETWEEN '%s' AND '%s'\n\t\t\t\t \t\tOR so.created_date BETWEEN '%s' AND '%s')\n\t\t\t\t\t\t AND so.status in ('wc-completed', 'wc-processing', 'wc-on-hold')\n\t\t\t\t\t\t AND oi.order_item_type = 'coupon'", $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$returns['kpi']['swc'] = !empty($cp_orders) ? $results[0]['cp_co'] / $returns['kpi']['orders'] * 100 : 0;
$returns['kpi']['lp_swc'] = !empty($lp_orders) ? $results[0]['lp_co'] / $lp_orders * 100 : 0;
}
// ###############################
// Total Customers
// ###############################
$query = $wpdb->prepare("SELECT \n\t\t\t\t\t\t\t\t\t\tIFNULL(count( distinct ( CASE WHEN user_id > 0 AND created_date >= '%s' THEN user_id END ) ), 0) AS cust,\n\t\t\t\t\t\t\t\t\t\tIFNULL(count( distinct ( CASE WHEN user_id > 0 AND created_date >= '%s' THEN user_id END ) ), 0) AS old_cust,\n\t\t\t\t\t\t\t\t\t\tIFNULL(count( distinct ( CASE WHEN user_id = 0 AND created_date >= '%s' THEN billing_email END ) ), 0) AS guests,\n\t\t\t\t\t\t\t\t\t\tIFNULL(count( distinct ( CASE WHEN user_id = 0 AND created_date >= '%s' THEN billing_email END ) ), 0) AS old_guests\n\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders \n\t\t\t\t\t\t\t\t\tWHERE (created_date BETWEEN '%s' AND '%s' \n\t\t\t\t\t\t\t\t\t\t\tOR created_date BETWEEN '%s' AND '%s' )\n\t\t\t\t\t\t\t\t\t\tAND type = 'shop_order'\n\t\t\t\t\t\t\t\t\t\tAND status IN ('wc-completed', 'wc-processing', 'wc-on-hold')", $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['cp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_start_date'], $cumm_dates['lp_end_date'], $cumm_dates['cp_start_date'], $cumm_dates['cp_end_date']);
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
$cp_cust = $results[0]['cust'] + $results[0]['guests'];
$lp_cust = $results[0]['old_cust'] + $results[0]['old_guests'];
$returns['kpi']['aipc'] = !empty($cp_cust) ? $returns['kpi']['orders_prod'] / $cp_cust : $cp_cust;
$returns['kpi']['lp_aipc'] = !empty($lp_cust) ? $lp_orders_prod / $lp_cust : $lp_cust;
}
}
if (!empty($post['cmd']) && $post['cmd'] == 'aprod_export') {
$r_aprod = sr_get_abandoned_products(array('cp_start_date' => $cumm_dates['cp_start_date'], 'cp_end_date' => $cumm_dates['cp_end_date'], 'security' => $params['security'], 'limit' => ''));
//get the variation ids
$t_p_ids = $t_v_ids = array();
if (count(array_keys($r_aprod['a_prod'])) > 0) {
$query = "SELECT id, post_parent\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}posts\n\t\t\t\t\t\t\tWHERE post_parent > 0\n\t\t\t\t\t\t\t\tAND id IN (" . implode(",", array_keys($r_aprod['a_prod'])) . ")\n\t\t\t\t\t\t\tGROUP BY id";
$results = $wpdb->get_results($query, 'ARRAY_A');
if (count($results) > 0) {
foreach ($results as $row) {
$t_v_ids[$row['id']] = $row['post_parent'];
$t_p_ids[] = $row['post_parent'];
}
$t_p_ids = array_merge($t_p_ids, array_keys(array_diff_key($r_aprod['a_prod'], $t_v_ids)));
} else {
$t_p_ids = array_merge($t_p_ids, array_keys($r_aprod['a_prod']));
}
sr_get_prod_title(array(&$r_aprod['a_prod']), array('t_p_ids' => $t_p_ids, 't_v_ids' => $t_v_ids, 'security' => $params['security']));
return json_encode($r_aprod['a_prod']);
}
}
if (!empty($post['cmd']) && $post['cmd'] == 'sr_summary') {
return json_encode($returns['kpi']);
}
return json_encode($returns);
}
