}
}
$sql = " CREATE TABLE IF NOT EXISTS {$wpdb->prefix}woo_sr_orders (\n `order_id` bigint(20) unsigned NOT NULL,\n `created_date` date NOT NULL DEFAULT '0000-00-00',\n `created_time` time NOT NULL DEFAULT '00:00:00',\n `status` ENUM('wc-pending' ,'wc-processing' ,'wc-on-hold' ,'wc-completed' ,'wc-cancelled' ,'wc-refunded' ,'wc-failed'),\n `type` ENUM('shop_order','shop_order_refund') NOT NULL DEFAULT 'shop_order',\n `parent_id` bigint(20) NOT NULL DEFAULT '0',\n `total` decimal(10,2) NOT NULL DEFAULT '0.00',\n `currency` varchar(5) NOT NULL,\n `discount` decimal(10,2) NOT NULL DEFAULT '0.00',\n `cart_discount` decimal(10,2) NOT NULL DEFAULT '0.00',\n `shipping` decimal(10,2) NOT NULL DEFAULT '0.00',\n `shipping_tax` decimal(10,2) NOT NULL DEFAULT '0.00',\n `shipping_method` varchar(50) NOT NULL,\n `tax` decimal(10,2) NOT NULL DEFAULT '0.00',\n `qty` smallint(5) unsigned NOT NULL DEFAULT '0',\n `payment_method` varchar(20) NOT NULL,\n `user_id` bigint(20) NOT NULL DEFAULT '0',\n `billing_email` varchar(255) NOT NULL,\n `customer_name` varchar(255) NOT NULL,\n `billing_country` varchar(20) NOT NULL,\n `trash` BIT(1) NOT NULL DEFAULT 0,\n `meta_values` longtext NOT NULL,\n `update_flag` BIT(1) NOT NULL DEFAULT 0,\n PRIMARY KEY (`order_id`),\n KEY `parent_id` (`parent_id`),\n KEY `currency` (`currency`),\n KEY `date_and_status` (`created_date`,`status`)\n ) {$collate};";
$wpdb->query($sql);
$sql = " CREATE TABLE IF NOT EXISTS {$wpdb->prefix}woo_sr_order_items (\n `order_item_id` bigint(20) NOT NULL,\n `order_date` date NOT NULL DEFAULT '0000-00-00',\n `order_time` time NOT NULL DEFAULT '00:00:00',\n `order_is_sale` tinyint(1) NOT NULL DEFAULT '1',\n `product_id` bigint(20) NOT NULL DEFAULT '0',\n `variation_id` bigint(20) NOT NULL DEFAULT '0',\n `order_id` bigint(20) NOT NULL,\n `type` enum('S','D','R') NOT NULL,\n `qty` smallint(6) NOT NULL,\n `total` decimal(10,2) NOT NULL,\n `trash` BIT(1) NOT NULL DEFAULT 0,\n `meta_values` longtext NOT NULL,\n `update_flag` BIT(1) NOT NULL default 0,\n PRIMARY KEY (`order_item_id`),\n KEY `composite` (`order_date`,`order_is_sale`,`type`,`product_id`)\n ) {$collate};";
$wpdb->query($sql);
$sql = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}woo_sr_orders_meta_all (post_id int NULL, meta_key text NOT NULL )";
$wpdb->query($sql);
return;
}
// if(defined(SR_BETA) && SR_BETA == "true") {
if (!isset($_GET['view']) && (isset($_GET['page']) && $_GET['page'] == 'wc-reports') && (!empty($sr_const['is_woo22']) && $sr_const['is_woo22'] == "true")) {
//chk if the SR db dump table exists or not
$table_name = "{$wpdb->prefix}woo_sr_orders";
if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") != $table_name || $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}woo_sr_orders") == 0) {
sr_create_snapshot_tables();
$query = "SELECT COUNT(*) as order_count\n FROM {$wpdb->prefix}posts\n WHERE post_type IN ('shop_order', 'shop_order_refund')";
$order_count = $wpdb->get_var($query);
if ($order_count > 0) {
?>
<div id="sr_data_sync_msg" class="updated woocommerce-message wc-connect" style="margin-top:25%;text-align:center;border:0px;display: block!important;">
<input id="sr_data_sync_orders" type="hidden" value="<?php
echo $order_count;
?>
">
<p><?php
_e('<strong>Smart Reporter Data Sync Required</strong> – We just need to sync your orders for faster reporting', $sr_text_domain);
?>
</p>
<p class="submit"> <a id="sr_sync_link" href="<?php
function sr_data_sync()
{
global $wpdb;
if (!empty($_POST['part']) && $_POST['part'] == 1) {
//Code for creating tables
sr_create_snapshot_tables();
$slimit = 0;
} else {
$slimit = ($_POST['part'] - 1) * 100;
}
// empty temp tables
$wpdb->query("DELETE FROM {$wpdb->prefix}woo_sr_orders_meta_all");
//Queries for inserting into temp table
$wpdb->query("INSERT INTO {$wpdb->prefix}woo_sr_orders (order_id, created_date, created_time, status, type, parent_id)\n\t\t\t\t\tSELECT ID as order_id, DATE(post_date) as date, TIME(post_date) as time, post_status as status, post_type as type, post_parent as parent_id \n\t\t\t\t\tFROM {$wpdb->prefix}posts\n\t\t\t\t\tWHERE post_type in ('shop_order', 'shop_order_refund')\n\t\t\t\t\tLIMIT " . $slimit . ", 100");
$o_ids = $wpdb->get_col("SELECT order_id FROM {$wpdb->prefix}woo_sr_orders WHERE update_flag = 0");
$v = '';
foreach ($o_ids as $id) {
$v .= "( " . $id . ", '_billing_country'), ( " . $id . ", '_billing_email'),( " . $id . ", '_billing_first_name'),\n\t\t\t\t\t\t( " . $id . ", '_billing_last_name'),( " . $id . ", '_cart_discount'),( " . $id . ", '_cart_discount_tax'),( " . $id . ", '_customer_user'),( " . $id . ", '_order_currency'),\n\t\t\t\t\t\t( " . $id . ", '_order_discount'),( " . $id . ", '_order_shipping'),( " . $id . ", '_order_shipping_tax'),( " . $id . ", '_order_tax'),( " . $id . ", '_order_total'),\n\t\t\t\t\t\t( " . $id . ", '_payment_method'), ";
}
$wpdb->query("INSERT INTO {$wpdb->prefix}woo_sr_orders_meta_all VALUES " . substr($v, 0, strlen($v) - 2));
$wpdb->query("UPDATE {$wpdb->prefix}woo_sr_orders AS sro\n\t\t\t\t\tSET sro.meta_values = (SELECT GROUP_CONCAT( IFNULL(pm.meta_value,'-') ORDER BY temp.meta_key SEPARATOR ' #sr# ') AS meta_values\n\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders_meta_all as temp \n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}postmeta AS pm ON (pm.meta_key = temp.meta_key AND pm.post_id = temp.post_id)\n\t\t\t\t\t\t\t\t\t\tWHERE temp.post_id = sro.order_id)\n\t\t\t\t\tWHERE sro.update_flag = 0");
//Code for transposing the concated data
// sro.cart_discount_tax = temp.cart_discount_tax,
$wpdb->query("UPDATE {$wpdb->prefix}woo_sr_orders AS sro\n\t\t\t\t\t\tJOIN (SELECT sro1.order_id AS oid, \n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 1), ' #sr# ', -1) AS billing_country,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 2), ' #sr# ', -1) AS billing_email,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 3), ' #sr# ', -1) AS billing_first_name,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 4), ' #sr# ', -1) AS billing_last_name,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 5), ' #sr# ', -1) AS cart_discount,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 6), ' #sr# ', -1) AS cart_discount_tax,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 7), ' #sr# ', -1) AS customer_user,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 8), ' #sr# ', -1) AS order_currency,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 9), ' #sr# ', -1) AS order_discount,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 10), ' #sr# ', -1) AS order_shipping,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 11), ' #sr# ', -1) AS order_shipping_tax,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 12), ' #sr# ', -1) AS order_tax,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 13), ' #sr# ', -1) AS order_total,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sro1.meta_values, ' #sr# ', 14), ' #sr# ', -1) AS payment_method\n\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders AS sro1) AS temp ON (temp.oid = sro.order_id)\n\t\t\t\t\t\tSET sro.billing_country = temp.billing_country,\n\t\t\t\t\t\t\tsro.billing_email = temp.billing_email,\n\t\t\t\t\t\t\tsro.customer_name = concat(temp.billing_first_name, temp.billing_last_name),\n\t\t\t\t\t\t\tsro.cart_discount = temp.cart_discount,\n\t\t\t\t\t\t\tsro.user_id = temp.customer_user,\n\t\t\t\t\t\t\tsro.currency = temp.order_currency,\n\t\t\t\t\t\t\tsro.discount = temp.order_discount,\n\t\t\t\t\t\t\tsro.shipping = temp.order_shipping,\n\t\t\t\t\t\t\tsro.shipping_tax = temp.order_shipping_tax,\n\t\t\t\t\t\t\tsro.tax = temp.order_tax,\n\t\t\t\t\t\t\tsro.total = temp.order_total,\n\t\t\t\t\t\t\tsro.payment_method = temp.payment_method\n\t\t\t\t\t\tWHERE sro.update_flag = 0");
// empty temp tables
$wpdb->query("DELETE FROM {$wpdb->prefix}woo_sr_orders_meta_all");
// Queries for order items
$wpdb->query("INSERT INTO {$wpdb->prefix}woo_sr_order_items (order_item_id, order_id, type )\n\t\t\tSELECT woi.order_item_id, woi.order_id,\n\t\t\t\tCASE WHEN sro.type = 'shop_order_refund' THEN 'R' ELSE 'S' END as type\n\t\t\tFROM {$wpdb->prefix}woocommerce_order_items as woi\n\t\t\t\tJOIN {$wpdb->prefix}woo_sr_orders AS sro ON (sro.order_id = woi.order_id AND sro.update_flag = 0)\n\t\t\tWHERE woi.order_item_type = 'line_item'");
$o_ids = $wpdb->get_col("SELECT order_item_id FROM {$wpdb->prefix}woo_sr_order_items WHERE update_flag = 0");
$v = '';
foreach ($o_ids as $id) {
$v .= "( " . $id . ", '_line_total'), ( " . $id . ", '_product_id'),( " . $id . ", '_qty'), ( " . $id . ", '_variation_id'), ";
}
$wpdb->query("INSERT INTO {$wpdb->prefix}woo_sr_orders_meta_all VALUES " . substr($v, 0, strlen($v) - 2));
$wpdb->query("UPDATE {$wpdb->prefix}woo_sr_order_items AS sroi\n\t\t\t\t\tSET sroi.meta_values = (SELECT GROUP_CONCAT( IFNULL(woi.meta_value,'-') ORDER BY temp.meta_key SEPARATOR ' #sr# ') AS meta_values\n\t\t\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_orders_meta_all as temp \n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woi ON (woi.meta_key = temp.meta_key AND woi.order_item_id = temp.post_id)\n\t\t\t\t\t\t\t\t\t\tWHERE temp.post_id = sroi.order_item_id)\n\t\t\t\t\tWHERE sroi.update_flag = 0");
$wpdb->query("UPDATE {$wpdb->prefix}woo_sr_order_items AS sroi\n\t\t\t\t\t\tJOIN (SELECT sroi1.order_item_id AS oid, \n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sroi1.meta_values, ' #sr# ', 1), ' #sr# ', -1) AS line_total,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sroi1.meta_values, ' #sr# ', 2), ' #sr# ', -1) AS product_id,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sroi1.meta_values, ' #sr# ', 3), ' #sr# ', -1) AS qty,\n\t\t\t\t\t\t\t\t\tSUBSTRING_INDEX(SUBSTRING_INDEX(sroi1.meta_values, ' #sr# ', 4), ' #sr# ', -1) AS variation_id\n\t\t\t\t\t\t\t\tFROM {$wpdb->prefix}woo_sr_order_items AS sroi1) AS temp ON (temp.oid = sroi.order_item_id)\n\t\t\t\t\t\tSET sroi.total = temp.line_total,\n\t\t\t\t\t\t\tsroi.product_id = temp.product_id,\n\t\t\t\t\t\t\tsroi.qty = temp.qty,\n\t\t\t\t\t\t\tsroi.variation_id = temp.variation_id\n\t\t\t\t\t\tWHERE sroi.update_flag = 0");
$wpdb->query("UPDATE {$wpdb->prefix}woo_sr_order_items AS oi\n\t\t\t\t\t\tJOIN {$wpdb->prefix}woo_sr_orders AS o ON (o.order_id = oi.order_id AND o.update_flag = 0)\n\t\t\t\t\tSET oi.order_date = o.created_date, \n\t\t\t\t\t\toi.order_time = o.created_time, \n\t\t\t\t\t\toi.order_is_sale = (CASE \n\t\t\t\t\t\t\t\t\t\t\tWHEN (o.type = 'shop_order' AND o.status IN ('wc-completed', 'wc-processing', 'wc-on-hold' )) THEN 1 ELSE 0 \n\t\t\t\t\t\t\t\t\t\t\tEND)");
// empty temp tables
$wpdb->query("DELETE FROM {$wpdb->prefix}woo_sr_orders_meta_all");
// empty meta_values col
$wpdb->query("UPDATE {$wpdb->prefix}woo_sr_order_items \n\t\t\t\t\t\tSET meta_values = '',\n\t\t\t\t\t\t\tupdate_flag = 1\n\t\t\t\t\t\tWHERE update_flag = 0");
// empty meta_values col
$wpdb->query("UPDATE {$wpdb->prefix}woo_sr_orders \n\t\t\t\t\t\tSET meta_values = '',\n\t\t\t\t\t\t\tupdate_flag = 1\n\t\t\t\t\t\tWHERE update_flag = 0");
if (!empty($_POST['sfinal']) && $_POST['sfinal'] == 1) {
$wpdb->query("DROP TABLE {$wpdb->prefix}woo_sr_orders_meta_all");
$wpdb->query("ALTER TABLE {$wpdb->prefix}woo_sr_orders DROP COLUMN meta_values, DROP COLUMN update_flag");
$wpdb->query("ALTER TABLE {$wpdb->prefix}woo_sr_order_items DROP COLUMN meta_values, DROP COLUMN update_flag");
}
exit;
}
