function load_sr_woo_order_items($wpdb)
{
$insert_query = "REPLACE INTO {$wpdb->prefix}sr_woo_order_items \n ( `product_id`, `order_id`, `order_date`, `order_status`, `product_name`, `sku`, `category`, `quantity`, `sales`, `discount` ) VALUES ";
$all_order_items = array();
// WC's code to get all order items
if (defined('SR_IS_WOO16') && SR_IS_WOO16 == "true") {
$results = $wpdb->get_results("\n SELECT meta.post_id AS order_id, meta.meta_value AS items \n FROM {$wpdb->prefix}posts AS posts\n\t LEFT JOIN {$wpdb->prefix}postmeta AS meta ON posts.ID = meta.post_id\n\t LEFT JOIN {$wpdb->prefix}term_relationships AS rel ON posts.ID=rel.object_ID\n\t LEFT JOIN {$wpdb->prefix}term_taxonomy AS tax USING( term_taxonomy_id )\n\t LEFT JOIN {$wpdb->prefix}terms AS term USING( term_id )\n\n WHERE \tmeta.meta_key \t\t= '_order_items'\n AND \tposts.post_type \t= 'shop_order'\n AND \tposts.post_status \t= 'publish'\n AND \ttax.taxonomy\t\t= 'shop_order_status'\n AND\t\tterm.slug\t\t\tIN ('completed', 'processing', 'on-hold')\n \t\t", 'ARRAY_A');
$num_rows = $wpdb->num_rows;
if ($num_rows > 0) {
foreach ($results as $result) {
$all_order_items[$result['order_id']] = maybe_unserialize($result['items']);
}
}
} else {
$select_posts = 'SELECT posts.ID AS order_id,
posts.post_date AS order_date,
posts.post_status AS order_status';
if (defined('SR_IS_WOO22') && SR_IS_WOO22 == "true") {
// AND posts.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold')
$query_orders = $select_posts . " FROM {$wpdb->prefix}posts AS posts\n\t WHERE \tposts.post_type = 'shop_order'";
} else {
// AND term.slug IN ('completed', 'processing', 'on-hold')
$query_orders = $select_posts . " FROM {$wpdb->posts} AS posts\n\t\t LEFT JOIN {$wpdb->prefix}term_relationships AS rel ON posts.ID=rel.object_ID\n\t\t LEFT JOIN {$wpdb->prefix}term_taxonomy AS tax USING( term_taxonomy_id )\n\t\t LEFT JOIN {$wpdb->prefix}terms AS term USING( term_id )\n\n\t WHERE \tposts.post_type \t= 'shop_order'\n\t\t AND \tposts.post_status \t= 'publish'\n\t\t AND \ttax.taxonomy\t\t= 'shop_order_status'";
}
$results = $wpdb->get_results($query_orders, 'ARRAY_A');
$orders_num_rows = $wpdb->num_rows;
if ($orders_num_rows > 0) {
$order_ids = $order_post_details = array();
foreach ($results as $result) {
$order_ids[] = $result['order_id'];
$order_post_details[$result['order_id']] = array();
$order_post_details[$result['order_id']]['order_date'] = $result['order_date'];
$order_post_details[$result['order_id']]['order_status'] = $result['order_status'];
}
$order_id = implode(", ", $order_ids);
$order_id = trim($order_id);
$query_order_items = "SELECT order_items.order_item_id,\n order_items.order_id ,\n order_items.order_item_name AS order_prod,\n\t\t GROUP_CONCAT(order_itemmeta.meta_key\n\t\t ORDER BY order_itemmeta.meta_id\n\t\t SEPARATOR '###' ) AS meta_key,\n\t\t GROUP_CONCAT(order_itemmeta.meta_value\n\t\t ORDER BY order_itemmeta.meta_id\n\t\t SEPARATOR '###' ) AS meta_value\n FROM {$wpdb->prefix}woocommerce_order_items AS order_items\n \tLEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_itemmeta\n \t\tON (order_items.order_item_id = order_itemmeta.order_item_id)\n WHERE order_items.order_id IN ({$order_id})\n GROUP BY order_items.order_item_id\n ORDER BY FIND_IN_SET(order_items.order_id,'{$order_id}')";
$results = $wpdb->get_results($query_order_items, 'ARRAY_A');
$num_rows = $wpdb->num_rows;
// query to fetch sku of all prodcut's
$query_sku = "SELECT post_id, meta_value\n \t\t\t FROM {$wpdb->prefix}postmeta \n \t\t\t WHERE meta_key ='_sku' \n \t\t\t ORDER BY post_id ASC";
$skus = $wpdb->get_results($query_sku, 'ARRAY_A');
// query to fetch category of all prodcut's
$query_catgry = "SELECT posts.ID AS product_id,\n \t\t\t\t \tterms.name as category \n \t\t\t\t FROM {$wpdb->prefix}posts AS posts\n\t \t\t\t\t JOIN {$wpdb->prefix}term_relationships AS rel ON (posts.ID = rel.object_ID) \n\t \t\t\t\t JOIN {$wpdb->prefix}term_taxonomy AS tax ON (rel.term_taxonomy_id = tax.term_taxonomy_id) \n\t \t\t\t\t JOIN {$wpdb->prefix}terms AS terms ON (tax.term_taxonomy_id = terms.term_id) \n \t\t\t\t WHERE tax.taxonomy = 'product_cat' ";
$category = $wpdb->get_results($query_catgry, 'ARRAY_A');
$catgry_data = array();
foreach ($skus as $sku) {
// to make post_id as index & sku as value
if (!empty($sku['meta_value'])) {
$sku_data[$sku['post_id']] = $sku['meta_value'];
}
}
foreach ($category as $cat) {
// to make product_id as index & category as value
$key = $cat['product_id'];
if (array_key_exists($key, $catgry_data)) {
//if sub category exists then assign category in (parent, sub) format.
$catgry_data[$cat['product_id']] .= ', ' . $cat['category'];
} else {
$catgry_data[$cat['product_id']] = $cat['category'];
}
}
if ($num_rows > 0) {
foreach ($results as $result) {
$order_item_meta_values = explode('###', $result['meta_value']);
$order_item_meta_key = explode('###', $result['meta_key']);
if (count($order_item_meta_values) != count($order_item_meta_key)) {
continue;
}
$order_item_meta_key_values = array_combine($order_item_meta_key, $order_item_meta_values);
if (!empty($order_item_meta_key_values['_product_id'])) {
$key = $order_item_meta_key_values['_product_id'];
}
if (array_key_exists($key, $sku_data)) {
// if key exists then assign it's sku
$order_item_meta_key_values['sku'] = $sku_data[$key];
}
if (array_key_exists($key, $catgry_data)) {
// if key exists then assign it's category
$order_item_meta_key_values['category'] = $catgry_data[$key];
}
if (!empty($order_post_details[$result['order_id']])) {
$order_item_meta_key_values['order_date'] = $order_post_details[$result['order_id']]['order_date'];
$order_item_meta_key_values['order_status'] = $order_post_details[$result['order_id']]['order_status'];
}
if (empty($all_order_items[$result['order_id']])) {
$all_order_items[$result['order_id']] = array();
}
$all_order_items[$result['order_id']][] = $order_item_meta_key_values;
}
}
}
}
//end if
$values = sr_items_to_values($all_order_items);
if (count($values) > 0) {
$insert_query .= implode(',', $values);
$wpdb->query($insert_query);
}
}
function load_sr_woo_order_items($wpdb)
{
$insert_query = "REPLACE INTO {$wpdb->prefix}sr_woo_order_items \n ( `product_id`, `order_id`, `product_name`, `quantity`, `sales`, `discount` ) VALUES ";
$all_order_items = array();
// WC's code to get all order items
if (IS_WOO16) {
$results = $wpdb->get_results("\n SELECT meta.post_id AS order_id, meta.meta_value AS items FROM {$wpdb->posts} AS posts\n\n LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id\n LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID\n LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )\n LEFT JOIN {$wpdb->terms} AS term USING( term_id )\n\n WHERE \tmeta.meta_key \t\t= '_order_items'\n AND \tposts.post_type \t= 'shop_order'\n AND \tposts.post_status \t= 'publish'\n AND \ttax.taxonomy\t\t= 'shop_order_status'\n AND\t\tterm.slug\t\t\tIN ('completed', 'processing', 'on-hold')\n ", 'ARRAY_A');
foreach ($results as $result) {
$all_order_items[$result['order_id']] = maybe_unserialize($result['items']);
}
} else {
$results = $wpdb->get_col("\n SELECT posts.ID AS order_id FROM {$wpdb->posts} AS posts\n\n LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID\n LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )\n LEFT JOIN {$wpdb->terms} AS term USING( term_id )\n\n WHERE \tposts.post_type \t= 'shop_order'\n AND \tposts.post_status \t= 'publish'\n AND \ttax.taxonomy\t\t= 'shop_order_status'\n AND\tterm.slug\tIN ('completed', 'processing', 'on-hold')\n ");
$order_id = implode(", ", $results);
$query_order_items = "SELECT order_items.order_item_id,\n order_items.order_id ,\n order_items.order_item_name AS order_prod,\n GROUP_CONCAT(order_itemmeta.meta_key\n ORDER BY order_itemmeta.meta_id\n SEPARATOR '###' ) AS meta_key,\n GROUP_CONCAT(order_itemmeta.meta_value\n ORDER BY order_itemmeta.meta_id\n SEPARATOR '###' ) AS meta_value\n FROM {$wpdb->prefix}woocommerce_order_items AS order_items\n LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_itemmeta\n ON (order_items.order_item_id = order_itemmeta.order_item_id)\n WHERE order_items.order_id IN ({$order_id})\n GROUP BY order_items.order_item_id\n ORDER BY FIND_IN_SET(order_items.order_id,'{$order_id}')";
$results = $wpdb->get_results($query_order_items, 'ARRAY_A');
foreach ($results as $result) {
$order_item_meta_values = explode('###', $result['meta_value']);
$order_item_meta_key = explode('###', $result['meta_key']);
if (count($order_item_meta_values) != count($order_item_meta_key)) {
continue;
}
$order_item_meta_key_values = array_combine($order_item_meta_key, $order_item_meta_values);
if (!isset($all_order_items[$result['order_id']])) {
$all_order_items[$result['order_id']] = array();
}
$all_order_items[$result['order_id']][] = $order_item_meta_key_values;
}
}
//end if
$values = sr_items_to_values($all_order_items);
if (count($values) > 0) {
$insert_query .= implode(',', $values);
$wpdb->query($insert_query);
}
}
