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); } }