function get_data_woo($post, $offset, $limit, $is_export = false) { global $wpdb, $woocommerce, $post_status, $parent_sort_id, $order_by, $post_type, $variation_name, $from_variation, $parent_name, $attributes; $_POST = $post; // Fix: PHP 5.4 $products = array(); // getting the active module $active_module = isset($_POST['active_module']) ? $_POST['active_module'] : 'Products'; // $active_module = $_POST ['active_module']; variation_query_params(); // Restricting LIMIT for export CSV if ($is_export === true) { $limit_string = ""; $image_size = "full"; } else { $limit_string = "LIMIT {$offset},{$limit}"; $image_size = "thumbnail"; } $wpdb->query("SET SESSION group_concat_max_len=999999"); // To increase the max length of the Group Concat Functionality $view_columns = json_decode(stripslashes($_POST['viewCols'])); if ($active_module == 'Products') { // <-products $tax_status = array('taxable' => __('Taxable', 'smart-manager'), 'shipping' => __('Shipping only', 'smart-manager'), 'none' => __('None', 'smart-manager')); if (isset($_POST['incVariation']) && $_POST['incVariation'] === 'true') { $show_variation = true; } else { $parent_name = ''; $post_status = "('publish', 'draft')"; $post_type = "('product')"; $parent_sort_id = ''; $order_by = " ORDER BY products.id desc"; $show_variation = false; } // if max-join-size issue occurs $query = "SET SQL_BIG_SELECTS=1;"; $wpdb->query($query); //Query for getting all the distinct attribute meta key names $query_variation = "SELECT DISTINCT meta_key as variation\n FROM {$wpdb->prefix}postmeta\n WHERE meta_key like 'attribute_%'"; $variation = $wpdb->get_col($query_variation); //Query to get all the distinct term names along with their slug names $query = "SELECT terms.slug as slug, terms.name as term_name FROM {$wpdb->prefix}terms AS terms\n\t\t\t\t\tJOIN {$wpdb->prefix}postmeta AS postmeta ON ( postmeta.meta_value = terms.slug AND postmeta.meta_key LIKE 'attribute_%' ) GROUP BY terms.slug"; $attributes_terms = $wpdb->get_results($query, 'ARRAY_A'); $attributes = array(); foreach ($attributes_terms as $attributes_term) { $attributes[$attributes_term['slug']] = $attributes_term['term_name']; } //Query to get the term_taxonomy_id for all the product categories $query_terms = "SELECT terms.name, wt.term_taxonomy_id FROM {$wpdb->prefix}term_taxonomy AS wt\n JOIN {$wpdb->prefix}terms AS terms ON (wt.term_id = terms.term_id)\n WHERE wt.taxonomy like 'product_cat'"; $results = $wpdb->get_results($query_terms, 'ARRAY_A'); $rows_terms = $wpdb->num_rows; if (!empty($results)) { for ($i = 0; $i < sizeof($results); $i++) { $term_taxonomy_id[$i] = $results[$i]['term_taxonomy_id']; $term_taxonomy[$results[$i]['term_taxonomy_id']] = $results[$i]['name']; } //Imploding the term_taxonomy_id to be used in the main query of the products module $term_taxonomy_id_query = "AND wtr.term_taxonomy_id IN (" . implode(",", $term_taxonomy_id) . ")"; } else { $term_taxonomy_id_query = ''; } $results_trash = array(); //Code to get the ids of all the products whose post_status is thrash $query_trash = "SELECT ID FROM {$wpdb->prefix}posts \n WHERE post_status = 'trash'\n AND post_type IN ('product')"; $results_trash = $wpdb->get_col($query_trash); $rows_trash = $wpdb->num_rows; $query_deleted = "SELECT distinct products.post_parent \n FROM {$wpdb->prefix}posts as products \n WHERE NOT EXISTS (SELECT * FROM {$wpdb->prefix}posts WHERE ID = products.post_parent) \n AND products.post_parent > 0 \n AND products.post_type = 'product_variation'"; $results_deleted = $wpdb->get_col($query_deleted); $rows_deleted = $wpdb->num_rows; for ($i = sizeof($results_trash), $j = 0; $j < sizeof($results_deleted); $i++, $j++) { $results_trash[$i] = $results_deleted[$j]; } if ($rows_trash > 0 || $rows_deleted > 0) { $trash_id = " AND products.post_parent NOT IN (" . implode(",", $results_trash) . ")"; } else { $trash_id = ""; } // Query to delete the unwanted '_regular_price' meta_key from variations if ($_POST['SM_IS_WOO16'] == "true") { $query_delete_variations = "DELETE FROM {$wpdb->prefix}postmeta \n WHERE meta_key = '_regular_price'\n AND post_id IN (SELECT id FROM {$wpdb->prefix}posts\n WHERE post_parent > 0\n AND post_type IN ('product_variation')\n AND post_status IN ('publish', 'draft'))"; $wpdb->query($query_delete_variations); } //Code to get the attribute terms for all attributes $query_attribute_names = "SELECT terms.name AS attribute_terms,\n taxonomy.taxonomy as attribute_name,\n taxonomy.term_taxonomy_id as term_taxonomy_id\n FROM {$wpdb->prefix}terms as terms\n JOIN {$wpdb->prefix}term_taxonomy as taxonomy ON (taxonomy.term_id = terms.term_id)\n WHERE taxonomy.taxonomy LIKE 'pa_%'\n GROUP BY taxonomy.taxonomy, taxonomy.term_taxonomy_id"; $results_attribute_names = $wpdb->get_results($query_attribute_names, 'ARRAY_A'); $product_attributes = array(); $temp_attribute_nm = ""; foreach ($results_attribute_names as $results_attribute_name) { if ($results_attribute_name['attribute_name'] != $temp_attribute_nm) { $product_attributes[$results_attribute_name['attribute_name']] = array(); } $product_attributes[$results_attribute_name['attribute_name']][$results_attribute_name['term_taxonomy_id']] = $results_attribute_name['attribute_terms']; $temp_attribute_nm = $results_attribute_name['attribute_name']; } $query_attribute_label = "SELECT attribute_name, attribute_label\n FROM {$wpdb->prefix}woocommerce_attribute_taxonomies"; $results_attribute_label = $wpdb->get_results($query_attribute_label, 'ARRAY_A'); $attributes_label = array(); foreach ($results_attribute_label as $results_attribute_label1) { $attributes_label['pa_' . $results_attribute_label1['attribute_name']] = $results_attribute_label1['attribute_label']; } //Query to get the Category Ids $query_categories = "SELECT products.id as id,\n GROUP_CONCAT(distinct wtr.term_taxonomy_id order by wtr.object_id SEPARATOR '###') AS term_taxonomy_id\n FROM {$wpdb->prefix}posts as products\n JOIN {$wpdb->prefix}term_relationships as wtr ON (products.id = wtr.object_id)\n WHERE products.post_status IN {$post_status}\n AND products.post_type IN {$post_type}\n {$trash_id}\n GROUP BY id"; $records_categories = $wpdb->get_results($query_categories, 'ARRAY_A'); $category_ids_all = array(); foreach ($records_categories as $records_category) { $category_ids_all[$records_category['id']] = $records_category['term_taxonomy_id']; } // GROUP_CONCAT(distinct wtr.term_taxonomy_id order by wtr.object_id SEPARATOR '###') AS term_taxonomy_id, $select = "SELECT SQL_CALC_FOUND_ROWS products.id,\n\t\t\t\t\tproducts.post_title,\n products.post_title as post_title_search,\n\t\t\t\t\tproducts.post_content,\n\t\t\t\t\tproducts.post_excerpt,\n\t\t\t\t\tproducts.post_status,\n\t\t\t\t\tproducts.post_parent,\n\t\t\t\t\tGROUP_CONCAT(prod_othermeta.meta_key order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_key,\n\t\t\t\t\tGROUP_CONCAT(prod_othermeta.meta_value order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_value\n\t\t\t\t\t{$parent_sort_id}"; //Used as an alternative to the SQL_CALC_FOUND_ROWS function of MYSQL Database $select_count = "SELECT COUNT(*) as count"; // To get the count of the number of rows generated from the above select query $search = ""; $search_condn = ""; if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = trim($_POST['searchText']); $search = ""; $product_type = wp_get_object_terms($records[$i]['id'], 'product_type', array('fields' => 'slugs')); $count_all_double_quote = substr_count($search_on, '"'); if ($count_all_double_quote > 0) { $search_ons = array_filter(array_map('trim', explode($wpdb->_real_escape('"'), $search_on))); $search_on = implode(",", $search_ons); } else { $search_on = $wpdb->_real_escape($search_on); $search_ons = explode(' ', $search_on); } //Function to prepare the conditions for the query function prepare_cond($search_ons, $column_nm) { $cond = ""; foreach ($search_ons as $search_on) { $cond .= $column_nm . " LIKE '%" . $search_on . "%'"; $cond .= " OR "; } return substr($cond, 0, -3); } //Query for getting the slug name for the term name typed in the search box of the products module $query_terms = "SELECT slug FROM {$wpdb->prefix}terms WHERE (" . prepare_cond($search_ons, "name") . ") AND name IN ('" . implode("','", $attributes) . "');"; $records_slug = $wpdb->get_col($query_terms); $rows = $wpdb->num_rows; $search_text = $search_ons; // if($rows > 0){ if ($rows > 0 && !empty($records_slug)) { $search_text = $records_slug; } //Query to get the term_taxonomy_id for the category name typed in the search text box of the products module $query_category = "SELECT tr.object_id FROM {$wpdb->prefix}term_relationships AS tr\n JOIN {$wpdb->prefix}term_taxonomy AS wt ON (wt.term_taxonomy_id = tr.term_taxonomy_id)\n JOIN {$wpdb->prefix}terms AS terms ON (wt.term_id = terms.term_id)\n WHERE wt.taxonomy like 'product_cat'\n AND (" . prepare_cond($search_ons, "terms.name") . ")"; $results_category = $wpdb->get_col($query_category); $rows_category = $wpdb->num_rows; if ($rows_category > 0) { $search_category = " OR products.ID IN (" . implode(",", $results_category) . ") OR products.post_parent IN (" . implode(",", $results_category) . ")"; } else { $search_category = ""; } //Query to get the post id if title or status or content or excerpt matches $query_title = "SELECT ID FROM {$wpdb->prefix}posts \n WHERE post_type IN ('product')\n AND (" . prepare_cond($search_ons, "post_title") . "OR " . prepare_cond($search_ons, "post_status") . "OR " . prepare_cond($search_ons, "post_content") . "OR " . prepare_cond($search_ons, "post_excerpt") . ")"; $results_title = $wpdb->get_col($query_title); $rows_title = $wpdb->num_rows; if ($rows_title > 0) { $search_title = " OR products.ID IN (" . implode(",", $results_title) . ") OR products.post_parent IN (" . implode(",", $results_title) . ")"; } else { $search_title = ""; } $visible = stristr("Catalog & Search", $search_on); if ($visible === FALSE) { $query_tax_visible = "SELECT post_id FROM {$wpdb->prefix}postmeta \n WHERE meta_key IN ('_tax_status','_visibility')\n AND meta_value LIKE '%{$search_on}%'"; } else { if (count($search_ons) > 1) { $query_tax_visible = "SELECT post_id FROM {$wpdb->prefix}postmeta \n WHERE meta_key IN ('_visibility')\n AND (meta_value LIKE '%visible%')"; } else { $query_tax_visible = "SELECT post_id FROM {$wpdb->prefix}postmeta \n WHERE meta_key IN ('_visibility')\n AND (meta_value LIKE '%visible%'\n OR meta_value LIKE '%{$search_on}%')"; } } $results_tax_visible = $wpdb->get_col($query_tax_visible); $rows_tax_visible = $wpdb->num_rows; if ($rows_tax_visible > 0) { $search_tax_visible = " OR products.ID IN (" . implode(",", $results_tax_visible) . ") OR products.post_parent IN (" . implode(",", $results_tax_visible) . ")"; } else { $search_tax_visible = ""; } if (is_array($search_ons) && count($search_ons) >= 1) { $search_condn = " HAVING "; foreach ($search_ons as $search_on) { $search_condn .= " (concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%{$search_on}%'\n OR post_content LIKE '%{$search_on}%'\n OR post_excerpt LIKE '%{$search_on}%'\n\n OR prod_othermeta_value LIKE '%{$search_on}%')\n\n "; $search_condn .= " OR"; } if ($rows == 1) { $query_ids1 = "SELECT GROUP_CONCAT(post_id ORDER BY post_id SEPARATOR ',') as id FROM {$wpdb->prefix}postmeta WHERE meta_value IN ('" . implode("','", $search_text) . "') AND meta_key like 'attribute_%'"; $records_id1 = implode(",", $wpdb->get_col($query_ids1)); $search_condn .= " products.id IN ({$records_id1})"; $search_condn .= " OR"; } $search_condn_count = " AND(" . substr($search_condn_count, 0, -2) . ")"; $search_condn = substr($search_condn, 0, -2); $search_condn .= $search_title . $search_category . $search_tax_visible; } else { $search_condn = " HAVING concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%{$search_on}%'\n OR post_content LIKE '%{$search_on}%'\n OR post_excerpt LIKE '%{$search_on}%'\n\n OR prod_othermeta_value LIKE '%{$search_on}%'\n\n {$search_title}\n {$search_category}\n {$search_tax_visible}\n "; if ($rows == 1) { $query_ids1 = "SELECT GROUP_CONCAT(post_id ORDER BY post_id SEPARATOR ',') as id FROM {$wpdb->prefix}postmeta WHERE meta_value IN ('" . implode("','", $search_text) . "') AND meta_key like 'attribute_%';"; $records_id1 = implode(",", $wpdb->get_col($query_ids1)); if (!empty($records_id1)) { $search_condn .= " OR products.id IN ({$records_id1})"; } } } } // LEFT JOIN {$wpdb->prefix}term_relationships as wtr ON (products.id = wtr.object_id // )"; // Removed $term_taxonomy_id_query as it was conflicting with Attributes Column $from = "FROM {$wpdb->prefix}posts as products\n\t\t\t\t\t\tJOIN {$wpdb->prefix}postmeta as prod_othermeta ON (prod_othermeta.post_id = products.id and\n\t\t\t\t\t\tprod_othermeta.meta_key IN ('_regular_price','_sale_price','_sale_price_dates_from','_sale_price_dates_to','_sku','_stock','_weight','_height','_length','_width','_price','_thumbnail_id','_tax_status','_min_variation_regular_price','_min_variation_sale_price','_visibility','_product_attributes','" . implode("','", $variation) . "') )"; $where = " WHERE products.post_status IN {$post_status}\n\t\t\t\t\t\tAND products.post_type IN {$post_type}\n {$trash_id}\n {$search}"; $group_by = " GROUP BY products.id "; //Query for getting the actual data loaded into the smartManager $query = "{$select} {$from} {$where} {$group_by} {$search_condn} {$order_by} {$limit_string};"; $records = $wpdb->get_results($query, 'ARRAY_A'); $num_rows = $wpdb->num_rows; //Query for getting the count of the number of products loaded into the smartManager $recordcount_result = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $recordcount_result[0]['count']; if ($num_rows <= 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { for ($i = 0; $i < $num_rows; $i++) { $records[$i]['post_content'] = str_replace('"', '\'', $records[$i]['post_content']); $records[$i]['post_excerpt'] = str_replace('"', '\'', $records[$i]['post_excerpt']); $prod_meta_values = explode('###', $records[$i]['prod_othermeta_value']); $prod_meta_key = explode('###', $records[$i]['prod_othermeta_key']); if (count($prod_meta_values) != count($prod_meta_key)) { continue; } unset($records[$i]['prod_othermeta_value']); unset($records[$i]['prod_othermeta_key']); $prod_meta_key_values = array_combine($prod_meta_key, $prod_meta_values); $product_type = wp_get_object_terms($records[$i]['id'], 'product_type', array('fields' => 'slugs')); // Code to get the Category Name from the term_taxonomy_id if (isset($category_ids_all[$records[$i]['id']])) { //$category_id = explode('###', $records[$i]['term_taxonomy_id']); $category_names = ""; // unset($records[$i]['term_taxonomy_id']); $category_id = explode('###', $category_ids_all[$records[$i]['id']]); for ($j = 0; $j < sizeof($category_id); $j++) { if (isset($term_taxonomy[$category_id[$j]])) { $category_names .= $term_taxonomy[$category_id[$j]] . ', '; } } if ($category_names != "") { $category_names = substr($category_names, 0, -2); $records[$i]['category'] = $category_names; } } else { $records[$i]['category'] = ""; } $records[$i]['category'] = $records[$i]['post_parent'] > 0 && $product_type[0] == 'simple' || $records[$i]['post_parent'] == 0 ? $records[$i]['category'] : ''; // To hide category name from Product's variations //Attributes Column if (isset($prod_meta_key_values['_product_attributes']) && $prod_meta_key_values['_product_attributes'] != "") { $prod_attr = unserialize($prod_meta_key_values['_product_attributes']); $attributes_list = ""; foreach ($prod_attr as $prod_attr1) { $attribute_terms = ""; if (isset($attributes_label[$prod_attr1['name']]) && isset($product_attributes[$prod_attr1['name']])) { foreach ($category_id as $category_id1) { if (isset($product_attributes[$prod_attr1['name']][$category_id1])) { $attribute_terms .= $product_attributes[$prod_attr1['name']][$category_id1] . ', '; } } if ($attribute_terms != "") { $attribute_terms = substr($attribute_terms, 0, -2); } $attributes_list .= $attributes_label[$prod_attr1['name']] . ": [" . $attribute_terms . "]"; } else { $attributes_list .= $prod_attr1['name'] . ": [" . str_replace(" |", ",", $prod_attr1['value']) . "]"; } if ($attributes_list != "") { $attributes_list .= "<br>"; } } // $records[$i]['product_attributes'] = substr( $attributes_list, 0, -3); $records[$i]['product_attributes'] = $attributes_list; } else { $records[$i]['product_attributes'] = ""; } if (isset($prod_meta_key_values['_sale_price_dates_from']) && !empty($prod_meta_key_values['_sale_price_dates_from'])) { $prod_meta_key_values['_sale_price_dates_from'] = date('Y-m-d', (int) $prod_meta_key_values['_sale_price_dates_from']); } if (isset($prod_meta_key_values['_sale_price_dates_to']) && !empty($prod_meta_key_values['_sale_price_dates_to'])) { $prod_meta_key_values['_sale_price_dates_to'] = date('Y-m-d', (int) $prod_meta_key_values['_sale_price_dates_to']); } $records[$i] = array_merge((array) $records[$i], $prod_meta_key_values); $thumbnail = isset($records[$i]['_thumbnail_id']) ? wp_get_attachment_image_src($records[$i]['_thumbnail_id'], $image_size) : ''; $records[$i]['thumbnail'] = $thumbnail[0] != '' ? $thumbnail[0] : false; $records[$i]['_tax_status'] = !empty($prod_meta_key_values['_tax_status']) ? $prod_meta_key_values['_tax_status'] : ''; // Setting product type for grouped products if ($records[$i]['post_parent'] != 0) { $product_type_parent = wp_get_object_terms($records[$i]['post_parent'], 'product_type', array('fields' => 'slugs')); if ($product_type_parent[0] == "grouped") { $records[$i]['product_type'] = $product_type_parent[0]; } } else { $records[$i]['product_type'] = $product_type[0]; } if ($show_variation === true) { if ($records[$i]['post_parent'] != 0 && $product_type_parent[0] != "grouped") { $records[$i]['post_status'] = get_post_status($records[$i]['post_parent']); if ($_POST['SM_IS_WOO16'] == "true") { $records[$i]['_regular_price'] = $records[$i]['_price']; } $variation_names = ''; foreach ($variation as $slug) { $variation_names .= isset($attributes[$prod_meta_key_values[$slug]]) && !empty($attributes[$prod_meta_key_values[$slug]]) ? $attributes[$prod_meta_key_values[$slug]] . ', ' : ucfirst($prod_meta_key_values[$slug]) . ', '; } $records[$i]['post_title'] = get_the_title($records[$i]['post_parent']) . " - " . trim($variation_names, ", "); } else { if ($records[$i]['post_parent'] == 0 && $product_type[0] == 'variable') { $records[$i]['_regular_price'] = ""; $records[$i]['_sale_price'] = ""; } else { $records[$i]['_regular_price'] = trim($records[$i]['_regular_price']); if (empty($records[$i]['_regular_price'])) { $records[$i]['_regular_price'] = $records[$i]['_price']; } } } $products[$records[$i]['id']]['post_title'] = $records[$i]['post_title']; $products[$records[$i]['id']]['variation'] = $variation_names; } elseif ($show_variation === false && SMPRO) { if ($product_type[0] == 'variable') { $records[$i]['_regular_price'] = $records[$i]['_min_variation_regular_price']; $records[$i]['_sale_price'] = $records[$i]['_min_variation_sale_price']; } else { $records[$i]['_regular_price'] = trim($records[$i]['_regular_price']); // if ( empty( $records[$i]['_regular_price'] ) ) { // $records[$i]['_regular_price'] = $records[$i]['_price']; // } $records[$i]['_sale_price'] = trim($records[$i]['_sale_price']); } } else { $records[$i]['_regular_price'] = $records[$i]['_regular_price']; $records[$i]['_sale_price'] = $records[$i]['_sale_price']; } unset($records[$i]['prod_othermeta_value']); unset($records[$i]['prod_othermeta_key']); } } } elseif ($active_module == 'Customers') { //BOF Customer's module $search_condn = customers_query($_POST['searchText']); $query_terms = "SELECT id FROM {$wpdb->prefix}posts AS posts\n JOIN {$wpdb->prefix}term_relationships AS term_relationships \n ON term_relationships.object_id = posts.ID \n JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy \n ON term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id \n JOIN {$wpdb->prefix}terms AS terms \n ON term_taxonomy.term_id = terms.term_id\n WHERE terms.name IN ('completed','processing','on-hold','pending')\n AND posts.post_status IN ('publish')"; $terms_post = implode(",", $wpdb->get_col($query_terms)); //Query for getting the max of post id for all the Guest Customers $query_post_guest = "SELECT post_ID FROM {$wpdb->prefix}postmeta\n WHERE meta_key ='_customer_user' AND meta_value=0\n AND post_id IN ({$terms_post})"; $post_id_guest = $wpdb->get_col($query_post_guest); $num_guest = $wpdb->num_rows; if ($num_guest > 0) { $query_max_id = "SELECT GROUP_CONCAT(distinct postmeta1.post_ID \n ORDER BY posts.post_date DESC SEPARATOR ',' ) AS all_id,\n GROUP_CONCAT(postmeta2.meta_value \n ORDER BY posts.post_date DESC SEPARATOR ',' ) AS order_total, \n date_format(max(posts.post_date),'%b %e %Y, %r') AS date,\n count(postmeta1.post_id) as count,\n sum(postmeta2.meta_value) as total\n \n FROM {$wpdb->prefix}postmeta AS postmeta1\n JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta1.post_id)\n INNER JOIN {$wpdb->prefix}postmeta AS postmeta2\n ON (postmeta2.post_ID = postmeta1.post_ID AND postmeta2.meta_key IN ('_order_total'))\n\n WHERE postmeta1.meta_key IN ('_billing_email')\n AND postmeta1.post_ID IN (" . implode(",", $post_id_guest) . ") \n GROUP BY postmeta1.meta_value\n ORDER BY date desc"; $result_max_id = $wpdb->get_results($query_max_id, 'ARRAY_A'); } //Query for getting the max of post id for all the Registered Customers $query_post_user = "******"; $post_id_user = $wpdb->get_col($query_post_user); $num_user = $wpdb->num_rows; if ($num_user > 0) { $query_max_user = "******" . implode(",", $post_id_user) . ") \n GROUP BY postmeta1.meta_value\n ORDER BY date"; $result_max_user = $wpdb->get_results($query_max_user, 'ARRAY_A'); } //Code for generating the total orders, count of orders , max ids and last order total arrays for ($i = 0; $i < sizeof($result_max_id); $i++) { $temp = explode(",", $result_max_id[$i]['all_id']); $max_ids[$i] = $temp[0]; $order_count[$max_ids[$i]] = $result_max_id[$i]['count']; $order_total[$max_ids[$i]] = $result_max_id[$i]['total']; //Code for getting the last Order Total $temp = explode(",", $result_max_id[$i]['order_total']); $last_order_total[$max_ids[$i]] = $temp[0]; } if (!empty($result_max_id)) { $j = sizeof($max_ids); $k = sizeof($order_count); $l = sizeof($order_total); $m = sizeof($last_order_total); } for ($i = 0; $i < sizeof($result_max_user); $i++, $j++, $k++, $l++, $m++) { $temp = explode(",", $result_max_user[$i]['all_id']); $max_ids[$j] = $temp[0]; $order_count[$max_ids[$j]] = $result_max_user[$i]['count']; $order_total[$max_ids[$j]] = $result_max_user[$i]['total']; $temp = explode(",", $result_max_user[$i]['order_total']); $last_order_total[$max_ids[$j]] = $temp[0]; } $max_id = implode(",", $max_ids); $customers_query = "SELECT SQL_CALC_FOUND_ROWS\n DISTINCT(GROUP_CONCAT( postmeta.meta_value\n ORDER BY postmeta.meta_id SEPARATOR '###' ) )AS meta_value,\n GROUP_CONCAT(distinct postmeta.meta_key\n ORDER BY postmeta.meta_id SEPARATOR '###' ) AS meta_key,\n date_format(max(posts.post_date),'%b %e %Y, %r') AS date,\n posts.ID AS id\n\n FROM {$wpdb->prefix}posts AS posts\n RIGHT JOIN {$wpdb->prefix}postmeta AS postmeta\n ON (posts.ID = postmeta.post_id AND postmeta.meta_key IN\n ('_billing_first_name' , '_billing_last_name' , '_billing_email',\n '_billing_address_1', '_billing_address_2', '_billing_city', '_billing_state',\n '_billing_country','_billing_postcode', '_billing_phone','_customer_user'))"; $where = " WHERE posts.post_type LIKE 'shop_order' \n\t\t\t\t\t AND posts.post_status IN ('publish')\n\t\t\t\t\t AND posts.ID IN ({$max_id})"; $group_by = " GROUP BY posts.ID"; $limit_query = " ORDER BY FIND_IN_SET(posts.ID,'{$max_id}') {$limit_string}"; $query = "{$customers_query} {$where} {$group_by} {$search_condn} {$limit_query};"; $result = $wpdb->get_results($query, 'ARRAY_A'); $num_rows = $wpdb->num_rows; //To get Total count $customers_count_result = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $customers_count_result[0]['count']; if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { $postmeta = array(); $j = 0; $k = 0; for ($i = 0; $i < sizeof($result); $i++) { $meta_value = explode('###', $result[$i]['meta_value']); $meta_key = explode('###', $result[$i]['meta_key']); //note: while merging the array, $data as to be the second arg if (count($meta_key) == count($meta_value)) { $temp[$i] = array_combine($meta_key, $meta_value); } if ($temp[$i]['_customer_user'] == 0) { $postmeta[$j] = $temp[$i]; $j++; } elseif ($temp[$i]['_customer_user'] > 0) { $user[$k] = $temp[$i]['_customer_user']; $k++; } unset($meta_value); unset($meta_key); } //Query for getting th Registered Users data from wp_usermeta and wp_users table if (!is_null($user)) { $user_ids = implode(",", $user); $query_users = "SELECT users.ID,users.user_email,\n GROUP_CONCAT( usermeta.meta_value ORDER BY usermeta.umeta_id SEPARATOR '###' ) AS meta_value,\n GROUP_CONCAT(distinct usermeta.meta_key\n ORDER BY usermeta.umeta_id SEPARATOR '###_' ) AS meta_key\n FROM {$wpdb->users} AS users\n JOIN {$wpdb->usermeta} AS usermeta\n ON (users.ID = usermeta.user_id AND usermeta.meta_key IN\n ('billing_first_name' , 'billing_last_name' , 'billing_email',\n 'billing_address_1', 'billing_address_2', 'billing_city', 'billing_state',\n 'billing_country','billing_postcode', 'billing_phone'))\n WHERE users.ID IN ({$user_ids})\n GROUP BY users.ID\n ORDER BY FIND_IN_SET(users.ID,'{$user_ids}');"; $result_users = $wpdb->get_results($query_users, 'ARRAY_A'); $num_rows_users = $wpdb->num_rows; for ($i = 0, $j = sizeof($postmeta); $i < sizeof($result_users); $i++, $j++) { $meta_value = explode('###', $result_users[$i]['meta_value']); $result_users[$i]['meta_key'] = "_" . $result_users[$i]['meta_key']; $meta_key = explode('###', $result_users[$i]['meta_key']); //note: while merging the array, $data as to be the second arg if (count($meta_key) == count($meta_value)) { $postmeta[$j] = array_combine($meta_key, $meta_value); $postmeta[$j]['_customer_user'] = $result_users[$i]['ID']; $postmeta[$j]['_billing_email'] = $result_users[$i]['user_email']; } unset($meta_value); unset($meta_key); } } $user_id = array(); for ($i = 0; $i < sizeof($postmeta); $i++) { if ($postmeta[$i]['_customer_user'] == 0) { $user_email[$i] = "'" . $postmeta[$i]['_billing_email'] . "'"; } elseif ($postmeta[$i]['_customer_user'] > 0) { $user_id[$i] = $postmeta[$i]['_customer_user']; } } for ($i = 0; $i < sizeof($postmeta); $i++) { $postmeta[$i]['id'] = $max_ids[$i]; if (SMPRO == true) { $result[$i]['_order_total'] = $last_order_total[$result[$i]['id']]; $postmeta[$i]['count_orders'] = $order_count[$result[$i]['id']]; $postmeta[$i]['total_orders'] = $order_total[$result[$i]['id']]; $result[$i]['last_order'] = $result[$i]['date']; } else { $postmeta[$i]['count_orders'] = 'Pro only'; $postmeta[$i]['total_orders'] = 'Pro only'; $result[$i]['_order_total'] = 'Pro only'; $result[$i]['last_order'] = 'Pro only'; } $result[$i]['_billing_address'] = isset($postmeta[$i]['_billing_address_1']) ? $postmeta[$i]['_billing_address_1'] . ', ' . $postmeta[$i]['_billing_address_2'] : $postmeta[$i]['_billing_address_2']; $postmeta[$i]['_billing_state'] = isset($woocommerce->countries->states[$postmeta[$i]['_billing_country']][$postmeta[$i]['_billing_state']]) ? $woocommerce->countries->states[$postmeta[$i]['_billing_country']][$postmeta[$i]['_billing_state']] : $postmeta[$i]['_billing_state']; $postmeta[$i]['_billing_country'] = isset($woocommerce->countries->countries[$postmeta[$i]['_billing_country']]) ? $woocommerce->countries->countries[$postmeta[$i]['_billing_country']] : $postmeta[$i]['_billing_country']; unset($result[$i]['date']); unset($result[$i]['meta_key']); unset($result[$i]['meta_value']); unset($postmeta[$i]['_billing_address_1']); unset($postmeta[$i]['_billing_address_2']); //NOTE: storing old email id in an extra column in record so useful to indentify record with emailid during updates. if ($postmeta[$i]['_billing_email'] != '' || $postmeta[$i]['_billing_email'] != null) { $records[] = array_merge($postmeta[$i], $result[$i]); } } } unset($result); unset($postmeta); } elseif ($active_module == 'Orders') { if (SMPRO == true && function_exists('sm_woo_get_packing_slip') && $_POST['label'] == 'getPurchaseLogs') { $log_ids_arr = json_decode(stripslashes($_POST['log_ids'])); if (is_array($log_ids_arr)) { $log_ids = implode(', ', $log_ids_arr); } sm_woo_get_packing_slip($log_ids, $log_ids_arr); } //Code to get all the term_names along with the term_taxonomy_id in an array $query_terms = "SELECT terms.name,term_taxonomy.term_taxonomy_id \n FROM {$wpdb->prefix}term_taxonomy AS term_taxonomy\n JOIN {$wpdb->prefix}terms AS terms ON terms.term_id = term_taxonomy.term_id\n WHERE taxonomy LIKE 'shop_order_status'"; $terms = $wpdb->get_results($query_terms, 'ARRAY_A'); for ($i = 0; $i < sizeof($terms); $i++) { $terms_name[$terms[$i]['term_taxonomy_id']] = $terms[$i]['name']; $terms_id[$i] = $terms[$i]['term_taxonomy_id']; } $terms_post = implode(",", $terms_id); //Code for Sequential Orders compatibility if (is_plugin_active('woocommerce-sequential-order-numbers-pro/woocommerce-sequential-order-numbers.php')) { $order_formatted = ", '_order_number_formatted'"; } else { $order_formatted = ""; } $select_query = "SELECT SQL_CALC_FOUND_ROWS posts.ID as id,\n posts.post_excerpt as order_note,\n\t\t\t\t\t\t\t\tdate_format(posts.post_date,'%b %e %Y, %r') AS date,\n\t\t\t\t\t\t\t\tGROUP_CONCAT( postmeta.meta_value \n\t\t\t\t\t\t\t\tORDER BY postmeta.meta_id\n\t\t\t\t\t\t\t\tSEPARATOR '###' ) AS meta_value,\n\t\t\t\t\t\t\t\tGROUP_CONCAT(distinct postmeta.meta_key\n\t\t\t\t\t\t\t\tORDER BY postmeta.meta_id \n\t\t\t\t\t\t\t\tSEPARATOR '###' ) AS meta_key,\n\t\t\t\t\t\t\t\tterm_relationships.term_taxonomy_id AS term_taxonomy_id\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}posts AS posts \n\t\t\t\t\t\t\t\t\tJOIN {$wpdb->prefix}term_relationships AS term_relationships \n\t\t\t\t\t\t\t\t\t\t\tON term_relationships.object_id = posts.ID \n\t\t\t\t\t\t\t\t\tRIGHT JOIN {$wpdb->prefix}postmeta AS postmeta \n\t\t\t\t\t\t\t\t\t\t\tON (posts.ID = postmeta.post_id AND postmeta.meta_key IN \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t('_billing_first_name' , '_billing_last_name' , '_billing_email',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'_shipping_first_name', '_shipping_last_name', '_shipping_address_1', '_shipping_address_2',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'_shipping_city', '_shipping_state', '_shipping_country','_shipping_postcode',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'_shipping_method', '_payment_method', '_order_items', '_order_total',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'_shipping_method_title', '_payment_method_title','_customer_user','_billing_phone',\n '_order_shipping', '_order_discount', '_cart_discount', '_order_tax', '_order_shipping_tax', '_order_currency', 'coupons'" . $order_formatted . "))"; $group_by = " GROUP BY posts.ID"; $limit_query = " ORDER BY posts.ID DESC {$limit_string} ;"; $where = " WHERE posts.post_type LIKE 'shop_order' \n\t\t\t\t\tAND posts.post_status IN ('publish','draft','auto-draft')\n AND term_relationships.term_taxonomy_id IN ({$terms_post})"; if (isset($_POST['fromDate'])) { $from_date = date('Y-m-d H:i:s', (int) strtotime($_POST['fromDate'])); $date_start = date('Y-m-d', (int) strtotime($_POST['fromDate'])); $date = date('Y-m-d', (int) strtotime($_POST['toDate'])); if ($date_start == $date && $date == date('Y-m-d')) { $curr_time_gmt = date('H:i:s', time() - date("Z")); $new_date = $date . " " . $curr_time_gmt; $to_date = date('Y-m-d H:i:s', (int) strtotime($new_date) + get_option('gmt_offset') * HOUR_IN_SECONDS); } else { $to_date = $date . " 23:59:59"; } if (SMPRO == true) { $where .= " AND posts.post_date BETWEEN '{$from_date}' AND '{$to_date}'"; } } if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $multiple_search_terms = explode('\\"', trim($_POST['searchText'])); $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); //Query for getting the user_id based on the email enetered in the Search Box $query_user_email = "SELECT id FROM {$wpdb->prefix}users \n WHERE user_email like '%{$search_on}%'"; $result_user_email = $wpdb->get_col($query_user_email); $num_rows_email = $wpdb->num_rows; if ($num_rows_email == 0) { $query_user_email = "SELECT DISTINCT p2.meta_value \n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_email'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value > 0\n AND p1.meta_value like '%{$search_on}%'"; $result_user_email = $wpdb->get_col($query_user_email); $num_rows_email1 = $wpdb->num_rows; } //Query for getting the user_id based on the Customer phone number enetered in the Search Box $query_user_phone = "SELECT user_id FROM {$wpdb->prefix}usermeta \n WHERE meta_key='billing_phone' \n AND meta_value like '%{$search_on}%'"; $result_user_phone = $wpdb->get_col($query_user_phone); $num_rows_phone = $wpdb->num_rows; if ($num_rows_phone == 0) { $query_user_phone = "SELECT DISTINCT p2.meta_value \n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_phone'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value > 0\n AND p1.meta_value like '%{$search_on}%'"; $result_user_phone = $wpdb->get_col($query_user_phone); $num_rows_phone1 = $wpdb->num_rows; } $query_terms = "SELECT term_taxonomy_id FROM {$wpdb->prefix}term_taxonomy\n WHERE term_id IN (SELECT term_id FROM {$wpdb->prefix}terms"; // name like '%$search_on%') // $multiple_search_terms = explode( '\"', $search_on ); if (!empty($multiple_search_terms)) { $query_terms .= " WHERE"; foreach ($multiple_search_terms as $search_status) { $search_status = trim($search_status); if (!empty($search_status)) { $query_terms .= " name like '%{$search_status}%' OR"; } } $query_terms = trim($query_terms, ' OR'); } $query_terms .= ")"; $result_terms = implode(",", $wpdb->get_col($query_terms)); $num_terms = $wpdb->num_rows; // Start: Query for searching product names in order if ($_POST['SM_IS_WOO16'] == "false") { $query_product_names = "SELECT order_id\n FROM {$wpdb->prefix}woocommerce_order_items"; if (!empty($multiple_search_terms)) { $query_product_names .= " WHERE"; foreach ($multiple_search_terms as $product_name) { $product_name = trim($product_name); if (!empty($product_name)) { $query_product_names .= " order_item_name LIKE '%{$product_name}%' OR"; } } $query_product_names = trim($query_product_names, ' OR'); } } else { $query_product_names = "SELECT post_id\n FROM {$wpdb->prefix}postmeta\n WHERE meta_key LIKE '%_order_items%'"; if (!empty($multiple_search_terms)) { $query_product_names .= " AND ("; foreach ($multiple_search_terms as $product_name) { $product_name = trim($product_name); if (!empty($product_name)) { $query_product_names .= " meta_value LIKE '%{$product_name}%' OR"; } } $query_product_names = trim($query_product_names, ' OR'); $query_product_names .= ")"; } } $result_product_ids = $wpdb->get_col($query_product_names); $num_product_ids = $wpdb->num_rows; // End: Query for searching product names in order //Query to get the post_id of the products whose sku code matches with the one type in the search text box of the Orders Module $query_sku = "SELECT post_id FROM {$wpdb->prefix}postmeta\n WHERE meta_key = '_sku'\n AND meta_value like '%{$search_on}%'"; $result_sku = $wpdb->get_col($query_sku); $rows_sku = $wpdb->num_rows; //Code for handling the Search functionality of the Orders Module using the SKU code of the product if ($rows_sku > 0) { if ($_POST['SM_IS_WOO16'] == "false") { $query_order_by_sku = "SELECT order_id\n FROM {$wpdb->prefix}woocommerce_order_items AS woocommerce_order_items\n LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woocommerce_order_itemmeta USING ( order_item_id )\n WHERE woocommerce_order_itemmeta.meta_key IN ( '_product_id', '_variation_id' )\n AND woocommerce_order_itemmeta.meta_value IN ( " . implode(',', $result_sku) . " )"; $results_order_by_sku = $wpdb->get_col($query_order_by_sku); $num_order_by_sku = $wpdb->num_rows; if ($num_order_by_sku > 0) { $search_condn = " HAVING id IN ( " . implode(',', $results_order_by_sku) . " )"; } } else { //Query for getting all the distinct attribute meta key names $query_variation = "SELECT DISTINCT meta_key as variation\n FROM {$wpdb->prefix}postmeta\n WHERE meta_key like 'attribute_%'"; $variation = $wpdb->get_col($query_variation); //Query to get all the product title's as displayed in the products module along wih the post_id and SKU code in an array $query_product = "SELECT posts.id, posts.post_title, posts.post_parent, \n GROUP_CONCAT( postmeta.meta_value \n ORDER BY postmeta.meta_id\n SEPARATOR ',' ) AS meta_value\n FROM {$wpdb->prefix}posts AS posts\n JOIN {$wpdb->prefix}postmeta AS postmeta\n ON (posts.ID = postmeta.post_id\n AND postmeta.meta_key IN ('_sku','" . implode("','", $variation) . "'))\n GROUP BY posts.id"; $result_product = $wpdb->get_results($query_product, 'ARRAY_A'); //Code to store all the products title in an array with the post_id as the array index for ($i = 0; $i < sizeof($result_product); $i++) { $product_title[$result_product[$i]['id']]['post_title'] = $result_product[$i]['post_title']; $product_title[$result_product[$i]['id']]['variation_title'] = $result_product[$i]['meta_value']; $product_title[$result_product[$i]['id']]['post_parent'] = $result_product[$i]['post_parent']; } $post_title = array(); $variation_title = array(); $search_condn = "HAVING"; for ($i = 0; $i < sizeof($result_sku); $i++) { $product_type = wp_get_object_terms($result_sku[$i], 'product_type', array('fields' => 'slugs')); // Getting the type of the product //Code to prepare the search condition for the search using SKU Code if ($product_title[$result_sku[$i]]['post_parent'] == 0) { $post_title[$i] = $product_title[$result_sku[$i]]['post_title']; $search_condn .= " meta_value like '%s:4:\"name\"%\"{$post_title[$i]}\"%' "; $search_condn .= "OR"; } elseif ($product_title[$result_sku[$i]]['post_parent'] > 0) { $temp = explode(",", $product_title[$result_sku[$i]]['variation_title']); $post_title[$i] = $product_title[$product_title[$result_sku[$i]]['post_parent']]['post_title']; $search_condn .= " meta_value like '%s:4:\"name\"%\"{$post_title[$i]}\"%' "; $search_condn .= "AND ("; for ($j = 1; $j < sizeof($temp); $j++) { $search_condn .= " meta_value like '%s:10:\"meta_value\"%\"{$temp[$j]}\"%' "; $search_condn .= "OR"; } $search_condn = substr($search_condn, 0, -2) . ")"; $search_condn .= "OR"; } } $variation_title = array_unique($variation_title); $search_condn = substr($search_condn, 0, -2); } } elseif ($num_product_ids > 0) { $search_condn = " HAVING id IN ( " . implode(',', $result_product_ids) . " )"; } elseif ($num_rows_email > 0) { // Query to bring the matching email of the Guest uers $query = "SELECT DISTINCT p1.meta_value \n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_email'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value = 0\n AND p1.meta_value like '%{$search_on}%'"; $result_email_guest = $wpdb->get_col($query); $rows_email_guest = $wpdb->num_rows; $query_email = "SELECT DISTINCT(p1.meta_value)\n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_email'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value IN (" . implode(",", $result_user_email) . ")"; $result_email = $wpdb->get_col($query_email); if ($rows_email_guest > 0) { for ($i = 0, $j = sizeof($result_email); $i < sizeof($result_email_guest); $i++, $j++) { $result_email[$j] = $result_email_guest[$i]; } } $search_condn = "HAVING"; for ($i = 0; $i < sizeof($result_email); $i++) { $search_condn .= " meta_value like '%{$result_email[$i]}%' "; $search_condn .= "OR"; } $search_condn = substr($search_condn, 0, -2); } elseif ($num_rows_phone > 0) { // Query to bring the matching Phone No. of the Guest uers $query = "SELECT DISTINCT p1.meta_value \n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_phone'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value = 0\n AND p1.meta_value like '%{$search_on}%'"; $result_phone_guest = $wpdb->get_col($query); $rows_phone_guest = $wpdb->num_rows; $query_phone = "SELECT DISTINCT(p1.meta_value)\n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_email'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value IN (" . implode(",", $result_user_phone) . ")"; $result_phone = $wpdb->get_col($query_phone); if ($rows_phone_guest > 0) { for ($i = 0, $j = sizeof($result_phone); $i < sizeof($result_phone_guest); $i++, $j++) { $result_phone[$j] = $result_phone_guest[$i]; } } $search_condn = "HAVING"; for ($i = 0; $i < sizeof($result_phone); $i++) { $search_condn .= " meta_value like '%{$result_phone[$i]}%' "; $search_condn .= "OR"; } $search_condn = substr($search_condn, 0, -2); } elseif ($num_rows_email1 > 0 || $num_rows_phone1 > 0) { $search_condn = " HAVING id = 0"; } elseif ($num_terms > 0) { $search_condn = " HAVING term_taxonomy_id IN ({$result_terms})"; } else { $search_condn = " HAVING id like '{$search_on}%'\n\t\t\t\t\t\t\t\t OR date like '%{$search_on}%'\n\t\t\t\t\t\t\t\t OR meta_value like '%{$search_on}%'"; } } //get the state id if the shipping state is numeric or blank $query = "{$select_query} {$where} {$group_by} {$search_condn} {$limit_query}"; $results = $wpdb->get_results($query, 'ARRAY_A'); //To get the total count $orders_count_result = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $orders_count_result[0]['count']; //Query to get the email id from the wp_users table for the Registered Customers $query_users = "SELECT users.ID,users.user_email,usermeta.meta_value\n FROM {$wpdb->prefix}users AS users, {$wpdb->prefix}usermeta AS usermeta\n WHERE usermeta.user_id = users.id \n AND usermeta.meta_key = 'billing_phone'\n GROUP BY users.ID"; $result_users = $wpdb->get_results($query_users, 'ARRAY_A'); if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { foreach ($results as $data) { $order_ids[] = $data['id']; } if ($_POST['SM_IS_WOO16'] == "false") { $order_id = implode(",", $order_ids); $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 AND order_items.order_item_type LIKE 'line_item'\n GROUP BY order_items.order_item_id\n ORDER BY FIND_IN_SET(order_items.order_id,'{$order_id}')"; $results_order_items = $wpdb->get_results($query_order_items, 'ARRAY_A'); $num_rows_order_items = $wpdb->num_rows; //code for formatting order items array if ($num_rows_order_items > 0) { $order_items = array(); foreach ($results_order_items as $results_order_item) { if (!isset($order_items[$results_order_item['order_id']])) { $order_items[$results_order_item['order_id']] = array(); } $order_items[$results_order_item['order_id']][] = $results_order_item; } } $query_order_coupons = "SELECT order_id,\n GROUP_CONCAT(order_item_name\n ORDER BY order_item_id \n SEPARATOR ', ' ) AS coupon_used\n FROM {$wpdb->prefix}woocommerce_order_items\n WHERE order_id IN ({$order_id})\n AND order_item_type LIKE 'coupon'\n GROUP BY order_id\n ORDER BY FIND_IN_SET(order_id,'{$order_id}')"; $results_order_coupons = $wpdb->get_results($query_order_coupons, 'ARRAY_A'); $num_rows_coupons = $wpdb->num_rows; if ($num_rows_coupons > 0) { $order_coupons = array(); foreach ($results_order_coupons as $results_order_coupon) { $order_coupons[$results_order_coupon['order_id']] = $results_order_coupon['coupon_used']; } } $query_variation_ids = "SELECT order_itemmeta.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_itemmeta.meta_key LIKE '_variation_id'\n AND order_itemmeta.meta_value > 0\n AND order_items.order_id IN ({$order_id})"; $result_variation_ids = $wpdb->get_col($query_variation_ids); if (count($result_variation_ids) > 0) { $query_variation_att = "SELECT postmeta.post_id AS post_id,\n GROUP_CONCAT(postmeta.meta_value\n ORDER BY postmeta.meta_id \n SEPARATOR ',' ) AS meta_value\n FROM {$wpdb->prefix}postmeta AS postmeta\n WHERE postmeta.meta_key LIKE 'attribute_%'\n AND postmeta.post_id IN (" . implode(",", $result_variation_ids) . ")\n GROUP BY postmeta.post_id"; // $results_variation_att = $wpdb->get_results($query_variation_att, 'ARRAY_A'); } $query_terms = "SELECT terms.slug as slug, terms.name as term_name\n FROM {$wpdb->prefix}terms AS terms\n JOIN {$wpdb->prefix}postmeta AS postmeta \n ON ( postmeta.meta_value = terms.slug \n AND postmeta.meta_key LIKE 'attribute_%' ) \n GROUP BY terms.slug"; $attributes_terms = $wpdb->get_results($query_terms, 'ARRAY_A'); $attributes = array(); foreach ($attributes_terms as $attributes_term) { $attributes[$attributes_term['slug']] = $attributes_term['term_name']; } $variation_att_all = array(); if (is_array($results_variation_att) && count($results_variation_att) > 0) { for ($i = 0; $i < sizeof($results_variation_att); $i++) { $variation_attributes = explode(", ", $results_variation_att[$i]['meta_value']); $attributes_final = array(); foreach ($variation_attributes as $variation_attribute) { $attributes_final[] = isset($attributes[$variation_attribute]) ? $attributes[$variation_attribute] : ucfirst($variation_attribute); } $results_variation_att[$i]['meta_value'] = implode(", ", $attributes_final); $variation_att_all[$results_variation_att[$i]['post_id']] = $results_variation_att[$i]['meta_value']; } } } foreach ($results as $data) { $meta_key = explode('###', $data['meta_key']); $meta_value = explode('###', $data['meta_value']); if (count($meta_key) == count($meta_value)) { $postmeta = array_combine($meta_key, $meta_value); //Code to replace the email of the Registered Customers with the one from the wp_users if ($postmeta['_customer_user'] > 0) { for ($index = 0; $index < sizeof($result_users); $index++) { if ($postmeta['_customer_user'] == $result_users[$index]['ID']) { $postmeta['_billing_email'] = $result_users[$index]['user_email']; $postmeta['_billing_phone'] = $result_users[$index]['meta_value']; break; } } } if ($_POST['SM_IS_WOO16'] == "true") { if (is_serialized($postmeta['_order_items'])) { $order_items = unserialize(trim($postmeta['_order_items'])); foreach ((array) $order_items as $order_item) { if (isset($order_item['item_meta']) && count($order_item['item_meta']) > 0) { $variation_data = array(); foreach ($order_item['item_meta'] as $meta) { $variation_data['attribute_' . $meta['meta_name']] = $meta['meta_value']; } $variation_details = woocommerce_get_formatted_variation($variation_data, true); } $data['details'] += $order_item['qty']; $data['order_total_ex_tax'] += $order_item['line_total']; $product_id = $order_item['variation_id'] > 0 ? $order_item['variation_id'] : $order_item['id']; $sm_sku = get_post_meta($product_id, '_sku', true); if (!empty($sm_sku)) { $sku_detail = '[SKU: ' . $sm_sku . ']'; } else { $sku_detail = ''; } $product_full_name = !empty($variation_details) ? $order_item['name'] . ' (' . $variation_details . ')' : $order_item['name']; $data['products_name'] .= $product_full_name . ' ' . $sku_detail . '[' . __('Qty', 'smart-manager') . ': ' . $order_item['qty'] . '][' . __('Price', 'smart-manager') . ': ' . $order_item['line_total'] / $order_item['qty'] . '], '; } isset($data['details']) ? $data['details'] .= ' items' : ($data['details'] = ''); $data['products_name'] = substr($data['products_name'], 0, -2); //To remove extra comma ', ' from returned string } else { $data['details'] = 'Details'; } } else { if (!empty($order_items[$data['id']])) { foreach ($order_items[$data['id']] as $order_item) { $prod_meta_values = explode('###', $order_item['meta_value']); $prod_meta_key = explode('###', $order_item['meta_key']); if (count($prod_meta_values) != count($prod_meta_key)) { continue; } unset($order_item['meta_value']); unset($order_item['meta_key']); update_post_meta($index, $sku_detail, $meta_value); $prod_meta_key_values = array_combine($prod_meta_key, $prod_meta_values); // if ($data['id'] == $order_item['order_id']) { $data['details'] += $prod_meta_key_values['_qty']; $data['order_total_ex_tax'] += $prod_meta_key_values['_line_total']; $product_id = $prod_meta_key_values['_variation_id'] > 0 ? $prod_meta_key_values['_variation_id'] : $prod_meta_key_values['_product_id']; $sm_sku = get_post_meta($product_id, '_sku', true); if (!empty($sm_sku)) { $sku_detail = '[SKU: ' . $sm_sku . ']'; } else { $sku_detail = ''; } $variation_att = isset($variation_att_all[$prod_meta_key_values['_variation_id']]) && !empty($variation_att_all[$prod_meta_key_values['_variation_id']]) ? $variation_att_all[$prod_meta_key_values['_variation_id']] : ''; $product_full_name = !empty($variation_att) ? $order_item['order_prod'] . ' (' . $variation_att . ')' : $order_item['order_prod']; $data['products_name'] .= $product_full_name . ' ' . $sku_detail . '[' . __('Qty', 'smart-manager') . ': ' . $prod_meta_key_values['_qty'] . '][' . __('Price', 'smart-manager') . ': ' . $prod_meta_key_values['_line_total'] / $prod_meta_key_values['_qty'] . '], '; $data['coupons'] = isset($order_coupons[$order_item['order_id']]) ? $order_coupons[$order_item['order_id']] : ""; // } } isset($data['details']) ? $data['details'] .= ' items' : ($data['details'] = ''); $data['products_name'] = substr($data['products_name'], 0, -2); //To remove extra comma ', ' from returned string } } //Code to get the Order_Status using the $terms_name array $data['order_status'] = $terms_name[$data['term_taxonomy_id']]; $name_emailid[0] = "<font class=blue>" . $postmeta['_billing_first_name'] . "</font>"; $name_emailid[1] = "<font class=blue>" . $postmeta['_billing_last_name'] . "</font>"; $name_emailid[2] = "(" . $postmeta['_billing_email'] . ")"; //email comes at 7th position. $data['name'] = implode(' ', $name_emailid); //in front end,splitting is done with this space. $data['_shipping_address'] = $postmeta['_shipping_address_1'] . ', ' . $postmeta['_shipping_address_2']; unset($data['meta_value']); $postmeta['_shipping_method'] = isset($postmeta['_shipping_method_title']) ? $postmeta['_shipping_method_title'] : $postmeta['_shipping_method']; $postmeta['_payment_method'] = isset($postmeta['_payment_method_title']) ? $postmeta['_payment_method_title'] : $postmeta['_payment_method']; $postmeta['_shipping_state'] = isset($woocommerce->countries->states[$postmeta['_shipping_country']][$postmeta['_shipping_state']]) ? $woocommerce->countries->states[$postmeta['_shipping_country']][$postmeta['_shipping_state']] : $postmeta['_shipping_state']; $postmeta['_shipping_country'] = isset($woocommerce->countries->countries[$postmeta['_shipping_country']]) ? $woocommerce->countries->countries[$postmeta['_shipping_country']] : $postmeta['_shipping_country']; $data['display_id'] = $data['id']; //Code for Sequential Orders compatibility if ($order_formatted != "" && isset($postmeta['_order_number_formatted'])) { $data['display_id'] = $postmeta['_order_number_formatted']; } $records[] = array_merge($postmeta, $data); } } unset($meta_value); unset($meta_key); unset($postmeta); unset($results); } } if (!isset($_POST['label']) && $_POST['label'] != 'getPurchaseLogs') { $encoded['items'] = $records; $encoded['totalCount'] = $num_records; unset($records); return $encoded; } }
function get_data_woo($post, $offset, $limit, $is_export = false) { global $wpdb, $woocommerce, $post_status, $parent_sort_id, $order_by, $post_type, $variation_name, $from_variation, $parent_name, $attributes; $_POST = $post; // Fix: PHP 5.4 $products = array(); // getting the active module $active_module = isset($_POST['active_module']) ? $_POST['active_module'] : 'Products'; // $active_module = $_POST ['active_module']; variation_query_params(); // Restricting LIMIT for export CSV if ($is_export === true) { $limit_string = ""; $image_size = "full"; } else { $limit_string = "LIMIT {$offset},{$limit}"; $image_size = "thumbnail"; } $wpdb->query("SET SESSION group_concat_max_len=999999"); // To increase the max length of the Group Concat Functionality $view_columns = !empty($_POST['viewCols']) ? json_decode(stripslashes($_POST['viewCols'])) : ''; if ($active_module == 'Products') { // <-products $tax_status = array('taxable' => __('Taxable', $sm_text_domain), 'shipping' => __('Shipping only', $sm_text_domain), 'none' => __('None', $sm_text_domain)); if (isset($_POST['incVariation']) && $_POST['incVariation'] === 'true') { $show_variation = true; } else { $parent_name = ''; $post_status = "('publish', 'pending', 'draft')"; $post_type = "('product')"; $parent_sort_id = ''; $order_by = " ORDER BY {$wpdb->prefix}posts.id desc"; $show_variation = false; } // if max-join-size issue occurs $query = "SET SQL_BIG_SELECTS=1;"; $wpdb->query($query); //Query for getting all the distinct attribute meta key names $query_variation = "SELECT DISTINCT meta_key as variation\n FROM {$wpdb->prefix}postmeta\n WHERE meta_key like 'attribute_%'"; $variation = $wpdb->get_col($query_variation); //Query to get all the distinct term names along with their slug names $query = "SELECT terms.slug as slug, terms.name as term_name FROM {$wpdb->prefix}terms AS terms\n JOIN {$wpdb->prefix}postmeta AS postmeta ON ( postmeta.meta_value = terms.slug AND postmeta.meta_key LIKE 'attribute_%' ) GROUP BY terms.slug"; $attributes_terms = $wpdb->get_results($query, 'ARRAY_A'); $attributes = array(); foreach ($attributes_terms as $attributes_term) { $attributes[$attributes_term['slug']] = $attributes_term['term_name']; } //Query to get the term_taxonomy_id for all the product categories $query_terms = "SELECT terms.name, wt.term_taxonomy_id FROM {$wpdb->prefix}term_taxonomy AS wt\n JOIN {$wpdb->prefix}terms AS terms ON (wt.term_id = terms.term_id)\n WHERE wt.taxonomy like 'product_cat'"; $results = $wpdb->get_results($query_terms, 'ARRAY_A'); $rows_terms = $wpdb->num_rows; if (!empty($results)) { for ($i = 0; $i < sizeof($results); $i++) { $term_taxonomy_id[$i] = $results[$i]['term_taxonomy_id']; $term_taxonomy[$results[$i]['term_taxonomy_id']] = $results[$i]['name']; } //Imploding the term_taxonomy_id to be used in the main query of the products module $term_taxonomy_id_query = "AND wtr.term_taxonomy_id IN (" . implode(",", $term_taxonomy_id) . ")"; } else { $term_taxonomy_id_query = ''; } $results_trash = array(); //Code to get the ids of all the products whose post_status is thrash $query_trash = "SELECT ID FROM {$wpdb->prefix}posts \n WHERE post_status = 'trash'\n AND post_type IN ('product')"; $results_trash = $wpdb->get_col($query_trash); $rows_trash = $wpdb->num_rows; //Code to get the taxonomy id for 'simple' product_type $query_taxonomy_id = "SELECT taxonomy.term_taxonomy_id as term_taxonomy_id\n FROM {$wpdb->prefix}terms as terms\n JOIN {$wpdb->prefix}term_taxonomy as taxonomy ON (taxonomy.term_id = terms.term_id)\n WHERE taxonomy.taxonomy = 'product_type'\n AND terms.slug = 'variable'"; $variable_taxonomy_id = $wpdb->get_var($query_taxonomy_id); if (!empty($variable_taxonomy_id)) { $query_post_parent_not_variable = "SELECT distinct products.post_parent \n FROM {$wpdb->prefix}posts as products \n WHERE NOT EXISTS (SELECT * \n FROM {$wpdb->prefix}term_relationships \n WHERE object_id = products.post_parent\n AND term_taxonomy_id = " . $variable_taxonomy_id . ") \n AND products.post_parent > 0 \n AND products.post_type = 'product_variation'"; $results_post_parent_not_variable = $wpdb->get_col($query_post_parent_not_variable); $rows_post_parent_not_variable = $wpdb->num_rows; for ($i = sizeof($results_trash), $j = 0; $j < sizeof($results_post_parent_not_variable); $i++, $j++) { $results_trash[$i] = $results_post_parent_not_variable[$j]; } } if ($rows_trash > 0 || $rows_post_parent_not_variable > 0) { $trash_id = " AND {$wpdb->prefix}posts.post_parent NOT IN (" . implode(",", $results_trash) . ")"; } else { $trash_id = ""; } // Query to delete the unwanted '_regular_price' meta_key from variations if ($_POST['SM_IS_WOO16'] == "true") { $query_delete_variations = "DELETE FROM {$wpdb->prefix}postmeta \n WHERE meta_key = '_regular_price'\n AND post_id IN (SELECT id FROM {$wpdb->prefix}posts\n WHERE post_parent > 0\n AND post_type IN ('product_variation')\n AND post_status IN ('publish', 'pending', 'draft'))"; $wpdb->query($query_delete_variations); } //Code to get the attribute terms for all attributes $query_attribute_names = "SELECT terms.name AS attribute_terms,\n taxonomy.taxonomy as attribute_name,\n taxonomy.term_taxonomy_id as term_taxonomy_id\n FROM {$wpdb->prefix}terms as terms\n JOIN {$wpdb->prefix}term_taxonomy as taxonomy ON (taxonomy.term_id = terms.term_id)\n WHERE taxonomy.taxonomy LIKE 'pa_%'\n GROUP BY taxonomy.taxonomy, taxonomy.term_taxonomy_id"; $results_attribute_names = $wpdb->get_results($query_attribute_names, 'ARRAY_A'); $product_attributes = array(); $temp_attribute_nm = ""; foreach ($results_attribute_names as $results_attribute_name) { if ($results_attribute_name['attribute_name'] != $temp_attribute_nm) { $product_attributes[$results_attribute_name['attribute_name']] = array(); } $product_attributes[$results_attribute_name['attribute_name']][$results_attribute_name['term_taxonomy_id']] = $results_attribute_name['attribute_terms']; $temp_attribute_nm = $results_attribute_name['attribute_name']; } $query_attribute_label = "SELECT attribute_name, attribute_label\n FROM {$wpdb->prefix}woocommerce_attribute_taxonomies"; $results_attribute_label = $wpdb->get_results($query_attribute_label, 'ARRAY_A'); $attributes_label = array(); foreach ($results_attribute_label as $results_attribute_label1) { $attributes_label['pa_' . $results_attribute_label1['attribute_name']] = $results_attribute_label1['attribute_label']; } //Query to get the Category Ids $query_categories = "SELECT {$wpdb->prefix}posts.id as id,\n GROUP_CONCAT(distinct {$wpdb->prefix}term_relationships.term_taxonomy_id order by {$wpdb->prefix}term_relationships.object_id SEPARATOR ' #sm# ') AS term_taxonomy_id\n FROM {$wpdb->prefix}posts\n JOIN {$wpdb->prefix}term_relationships ON ({$wpdb->prefix}posts.id = {$wpdb->prefix}term_relationships.object_id)\n WHERE {$wpdb->prefix}posts.post_status IN {$post_status}\n AND {$wpdb->prefix}posts.post_type IN {$post_type}\n {$trash_id}\n GROUP BY id"; $records_categories = $wpdb->get_results($query_categories, 'ARRAY_A'); $category_ids_all = array(); foreach ($records_categories as $records_category) { $category_ids_all[$records_category['id']] = $records_category['term_taxonomy_id']; } if (!empty($_POST['SM_IS_WOO21']) && $_POST['SM_IS_WOO21'] == "true" || !empty($_POST['SM_IS_WOO22']) && $_POST['SM_IS_WOO22'] == "true") { $query_taxonomy_id_variable = "SELECT term_taxonomy.term_taxonomy_id\n FROM {$wpdb->prefix}term_taxonomy AS term_taxonomy \n JOIN {$wpdb->prefix}terms AS terms ON (term_taxonomy.term_id = terms.term_id)\n WHERE terms.name LIKE 'variable'"; $results_taxonomy_id_variable = $wpdb->get_col($query_taxonomy_id_variable); if (!empty($results_taxonomy_id_variable)) { $cond_taxonomy_id_variable = "AND term_relationships.term_taxonomy_id IN (" . implode(",", $results_taxonomy_id_variable) . ")"; } else { $cond_taxonomy_id_variable = ""; } $query_variation_reg_price = "SELECT postmeta.post_id as variation_parent_id,\n postmeta.meta_value as variation_id\n FROM {$wpdb->prefix}postmeta as postmeta\n JOIN {$wpdb->prefix}term_relationships AS term_relationships \n ON (term_relationships.object_id = postmeta.post_id)\n WHERE postmeta.meta_key IN ('_min_price_variation_id')\n {$cond_taxonomy_id_variable}\n GROUP BY postmeta.post_id"; $results_variation_reg_price = $wpdb->get_results($query_variation_reg_price, 'ARRAY_A'); $rows_variation_reg_price = $wpdb->num_rows; $variation_reg_price = array(); if ($rows_variation_reg_price > 0) { foreach ($results_variation_reg_price as $results_variation_reg_price1) { $variation_reg_price[$results_variation_reg_price1['variation_parent_id']] = $results_variation_reg_price1['variation_id']; } } } // GROUP_CONCAT(distinct wtr.term_taxonomy_id order by wtr.object_id SEPARATOR ' #sm# ') AS term_taxonomy_id, $post_meta_select = !empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWoo' ? ", GROUP_CONCAT({$wpdb->prefix}postmeta.meta_key order by {$wpdb->prefix}postmeta.meta_id SEPARATOR ' #sm# ') AS prod_othermeta_key\n , GROUP_CONCAT({$wpdb->prefix}postmeta.meta_value order by {$wpdb->prefix}postmeta.meta_id SEPARATOR ' #sm# ') AS prod_othermeta_value" : ""; $select = "SELECT SQL_CALC_FOUND_ROWS {$wpdb->prefix}posts.id,\n {$wpdb->prefix}posts.post_title,\n {$wpdb->prefix}posts.post_title as post_title_search,\n {$wpdb->prefix}posts.post_content,\n {$wpdb->prefix}posts.post_excerpt,\n {$wpdb->prefix}posts.post_status,\n {$wpdb->prefix}posts.post_parent\n {$post_meta_select}\n {$parent_sort_id}"; //Used as an alternative to the SQL_CALC_FOUND_ROWS function of MYSQL Database $select_count = "SELECT COUNT(*) as count"; // To get the count of the number of rows generated from the above select query $search = ""; $search_condn = ""; //Code to clear the advanced search temp table if (empty($_POST['search_query']) || empty($_POST['search_query'][0]) || !empty($_POST['searchText'])) { $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp"); delete_option('sm_advanced_search_query'); } $sm_advanced_search_results_persistent = 0; //flag to handle persistent search results //Code fo handling advanced search functionality if (!empty($_POST['search_query']) && !empty($_POST['search_query'][0]) || !empty($_POST['searchText'])) { if (empty($_POST['searchText'])) { $search_query_diff = get_option('sm_advanced_search_query') != '' ? array_diff($_POST['search_query'], get_option('sm_advanced_search_query')) : $_POST['search_query']; } else { $search_query_diff = ''; } if (!empty($search_query_diff)) { $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp"); // query to reset advanced search temp table $advanced_search_query = array(); $i = 0; update_option('sm_advanced_search_query', $_POST['search_query']); foreach ($_POST['search_query'] as $search_string_array) { $search_string_array = json_decode(stripslashes($search_string_array), true); if (is_array($search_string_array)) { $advanced_search_query[$i] = array(); $advanced_search_query[$i]['cond_posts'] = ''; $advanced_search_query[$i]['cond_postmeta'] = ''; $advanced_search_query[$i]['cond_terms'] = ''; $advanced_search_query[$i]['cond_postmeta_col_name'] = ''; $advanced_search_query[$i]['cond_postmeta_col_value'] = ''; $advanced_search_query[$i]['cond_postmeta_operator'] = ''; $advanced_search_query[$i]['cond_terms_col_name'] = ''; $advanced_search_query[$i]['cond_terms_col_value'] = ''; $advanced_search_query[$i]['cond_terms_operator'] = ''; $search_value_is_array = 0; //flag for array of search_values foreach ($search_string_array as $search_string) { $search_col = !empty($search_string['col_name']) ? $search_string['col_name'] : ''; $search_operator = !empty($search_string['operator']) ? $search_string['operator'] : ''; $search_data_type = !empty($search_string['type']) ? $search_string['type'] : 'string'; $search_value = !empty($search_string['value']) ? $search_string['value'] : ($search_data_type == "number" ? '0' : ''); if (!empty($search_string['table_name']) && $search_string['table_name'] == $wpdb->prefix . 'posts') { if ($search_data_type == "number") { $advanced_search_query[$i]['cond_posts'] .= $search_string['table_name'] . "." . $search_col . " " . $search_operator . " " . $search_value; } else { if ($search_operator == 'is') { $advanced_search_query[$i]['cond_posts'] .= $search_string['table_name'] . "." . $search_col . " LIKE '" . $search_value . "'"; } else { if ($search_operator == 'is not') { $advanced_search_query[$i]['cond_posts'] .= $search_string['table_name'] . "." . $search_col . " NOT LIKE '" . $search_value . "'"; } else { $advanced_search_query[$i]['cond_posts'] .= $search_string['table_name'] . "." . $search_col . " " . $search_operator . "'%" . $search_value . "%'"; } } } $advanced_search_query[$i]['cond_posts'] .= " AND "; } else { if (!empty($search_string['table_name']) && $search_string['table_name'] == $wpdb->prefix . 'postmeta') { if ($search_col == '_tax_status' && $search_value == 'Shipping only') { $search_value = 'shipping'; } else { if ($search_col == '_visibility' && $search_value == 'Catalog & Search') { $search_value = 'visible'; } } $advanced_search_query[$i]['cond_postmeta_col_name'] .= $search_col; $advanced_search_query[$i]['cond_postmeta_col_value'] .= $search_value; if ($search_data_type == "number") { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value " . $search_operator . " " . $search_value . " )"; $advanced_search_query[$i]['cond_postmeta_operator'] .= $search_operator; } else { if ($search_operator == 'is') { $advanced_search_query[$i]['cond_postmeta_operator'] .= 'LIKE'; if ($search_col == '_product_attributes') { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value LIKE '%" . $search_value . "%'" . " )"; } else { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value LIKE '" . $search_value . "'" . " )"; } } else { if ($search_operator == 'is not') { $advanced_search_query[$i]['cond_postmeta_operator'] .= 'NOT LIKE'; if ($search_col == '_product_attributes') { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value NOT LIKE '%" . $search_value . "%'" . " )"; } else { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value NOT LIKE '" . $search_value . "'" . " )"; } } else { $advanced_search_query[$i]['cond_postmeta_operator'] .= $search_operator; $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value " . $search_operator . "'%" . $search_value . "%'" . " )"; } } } $advanced_search_query[$i]['cond_postmeta'] .= " AND "; $advanced_search_query[$i]['cond_postmeta_col_name'] .= " AND "; $advanced_search_query[$i]['cond_postmeta_col_value'] .= " AND "; $advanced_search_query[$i]['cond_postmeta_operator'] .= " AND "; } else { if (!empty($search_string['table_name']) && $search_string['table_name'] == $wpdb->prefix . 'term_relationships') { $advanced_search_query[$i]['cond_terms_col_name'] .= $search_col; $advanced_search_query[$i]['cond_terms_col_value'] .= $search_value; if ($search_operator == 'is') { $advanced_search_query[$i]['cond_terms'] .= " ( " . $wpdb->prefix . "term_taxonomy.taxonomy LIKE '" . $search_col . "' AND " . $wpdb->prefix . "terms.slug LIKE '" . $search_value . "'" . " )"; $advanced_search_query[$i]['cond_terms_operator'] .= 'LIKE'; } else { if ($search_operator == 'is not') { $advanced_search_query[$i]['cond_terms'] .= " ( " . $wpdb->prefix . "term_taxonomy.taxonomy LIKE '" . $search_col . "' AND " . $wpdb->prefix . "terms.slug NOT LIKE '" . $search_value . "'" . " )"; $advanced_search_query[$i]['cond_terms_operator'] .= 'NOT LIKE'; } else { $advanced_search_query[$i]['cond_terms'] .= " ( " . $wpdb->prefix . "term_taxonomy.taxonomy LIKE '" . $search_col . "' AND " . $wpdb->prefix . "terms.slug " . $search_operator . "'%" . $search_value . "%'" . " )"; $advanced_search_query[$i]['cond_terms_operator'] .= $search_operator; } } $advanced_search_query[$i]['cond_terms'] .= " AND "; $advanced_search_query[$i]['cond_terms_col_name'] .= " AND "; $advanced_search_query[$i]['cond_terms_col_value'] .= " AND "; $advanced_search_query[$i]['cond_terms_operator'] .= " AND "; } } } } $advanced_search_query[$i]['cond_posts'] = !empty($advanced_search_query[$i]['cond_posts']) ? substr($advanced_search_query[$i]['cond_posts'], 0, -4) : ''; $advanced_search_query[$i]['cond_postmeta'] = !empty($advanced_search_query[$i]['cond_postmeta']) ? substr($advanced_search_query[$i]['cond_postmeta'], 0, -4) : ''; $advanced_search_query[$i]['cond_terms'] = !empty($advanced_search_query[$i]['cond_terms']) ? substr($advanced_search_query[$i]['cond_terms'], 0, -4) : ''; $advanced_search_query[$i]['cond_postmeta_col_name'] = !empty($advanced_search_query[$i]['cond_postmeta_col_name']) ? substr($advanced_search_query[$i]['cond_postmeta_col_name'], 0, -4) : ''; $advanced_search_query[$i]['cond_postmeta_col_value'] = !empty($advanced_search_query[$i]['cond_postmeta_col_value']) ? substr($advanced_search_query[$i]['cond_postmeta_col_value'], 0, -4) : ''; $advanced_search_query[$i]['cond_postmeta_operator'] = !empty($advanced_search_query[$i]['cond_postmeta_operator']) ? substr($advanced_search_query[$i]['cond_postmeta_operator'], 0, -4) : ''; $advanced_search_query[$i]['cond_terms_col_name'] = !empty($advanced_search_query[$i]['cond_terms_col_name']) ? substr($advanced_search_query[$i]['cond_terms_col_name'], 0, -4) : ''; $advanced_search_query[$i]['cond_terms_col_value'] = !empty($advanced_search_query[$i]['cond_terms_col_value']) ? substr($advanced_search_query[$i]['cond_terms_col_value'], 0, -4) : ''; $advanced_search_query[$i]['cond_terms_operator'] = !empty($advanced_search_query[$i]['cond_terms_operator']) ? substr($advanced_search_query[$i]['cond_terms_operator'], 0, -4) : ''; } $i++; } } else { if (!empty($_POST['searchText'])) { if (substr($_POST['searchText'], 0, 2) == '\\"' && substr($_POST['searchText'], -2) == '\\"') { $search_string = substr($_POST['searchText'], 2, strlen($_POST['searchText']) - 4); $advanced_search_query[0]['cond_posts'] = $wpdb->prefix . 'posts' . ".id = '" . $search_string . "'"; $advanced_search_query[1]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_title = '" . $search_string . "'"; $advanced_search_query[2]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_status = '" . $search_string . "'"; $advanced_search_query[3]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_content = '" . $search_string . "'"; $advanced_search_query[4]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_excerpt = '" . $search_string . "'"; $advanced_search_query[5]['cond_postmeta'] = $wpdb->prefix . 'postmeta' . ".meta_value = '" . $search_string . "'"; $advanced_search_query[6]['cond_terms'] = $wpdb->prefix . "term_taxonomy.taxonomy = '" . $search_string . "'"; $advanced_search_query[7]['cond_terms'] = $wpdb->prefix . "terms.slug = '" . $search_string . "'"; $advanced_search_query[8]['cond_terms'] = $wpdb->prefix . "terms.name = '" . $search_string . "'"; } else { $advanced_search_query[0]['cond_posts'] = $wpdb->prefix . 'posts' . ".id LIKE '" . $_POST['searchText'] . "'"; $advanced_search_query[1]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_title LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[2]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_status LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[3]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_content LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[4]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_excerpt LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[5]['cond_postmeta'] = $wpdb->prefix . 'postmeta' . ".meta_value LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[6]['cond_terms'] = $wpdb->prefix . "term_taxonomy.taxonomy LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[7]['cond_terms'] = $wpdb->prefix . "terms.slug LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[8]['cond_terms'] = $wpdb->prefix . "terms.name LIKE '%" . $_POST['searchText'] . "%'"; } } else { $sm_advanced_search_results_persistent = 1; } } } // if (isset ( $_POST ['searchText'] ) && $_POST ['searchText'] != '') { // $search_on = trim ( $_POST ['searchText'] ); // $search = ""; // $product_type = wp_get_object_terms($records[$i]['id'], 'product_type', array('fields' => 'slugs')); // $count_all_double_quote = substr_count( $search_on, '"' ); // if ( $count_all_double_quote > 0 ) { // $search_ons = array_filter( array_map( 'trim', explode( $wpdb->_real_escape( '"' ), $search_on ) ) ); // $search_on = implode(",", $search_ons); // } else { // $search_on = $wpdb->_real_escape( $search_on ); // $search_ons = explode( ' ', $search_on ); // } // //Function to prepare the conditions for the query // function prepare_cond($search_ons,$column_nm) { // $cond = ""; // foreach ($search_ons as $search_on) { // $cond .= $column_nm . " LIKE '%" . $search_on . "%'"; // $cond .= " OR "; // } // return substr( $cond, 0, -3 ); // }; // //Query for getting the slug name for the term name typed in the search box of the products module // $query_terms = "SELECT slug FROM {$wpdb->prefix}terms WHERE (". prepare_cond($search_ons,"name") .") AND name IN ('" .implode("','",$attributes) . "');"; // $records_slug = $wpdb->get_col ( $query_terms ); // $rows = $wpdb->num_rows; // $search_text = $search_ons; // // if($rows > 0){ // if($rows > 0 && (!empty($records_slug))){ // $search_text = $records_slug; // } // //Query to get the term_taxonomy_id for the category name typed in the search text box of the products module // $query_category = "SELECT tr.object_id FROM {$wpdb->prefix}term_relationships AS tr // JOIN {$wpdb->prefix}term_taxonomy AS wt ON (wt.term_taxonomy_id = tr.term_taxonomy_id) // JOIN {$wpdb->prefix}terms AS terms ON (wt.term_id = terms.term_id) // WHERE wt.taxonomy like 'product_cat' // AND (". prepare_cond($search_ons,"terms.name") . ")"; // $results_category = $wpdb->get_col( $query_category ); // $rows_category = $wpdb->num_rows; // if ($rows_category > 0) { // $search_category = " OR products.ID IN (" .implode(",",$results_category). ") OR products.post_parent IN (" .implode(",",$results_category). ")"; // } // else { // $search_category = ""; // } // //Query to get the post id if title or status or content or excerpt matches // $query_title = "SELECT ID FROM {$wpdb->prefix}posts // WHERE post_type IN ('product') // AND (". prepare_cond($search_ons,"post_title"). // "OR ". prepare_cond($search_ons,"post_status"). // "OR ". prepare_cond($search_ons,"post_content"). // "OR ". prepare_cond($search_ons,"post_excerpt") .")"; // $results_title = $wpdb->get_col( $query_title ); // $rows_title = $wpdb->num_rows; // if ($rows_title > 0) { // $search_title = " OR products.ID IN (" .implode(",",$results_title). ") OR products.post_parent IN (" .implode(",",$results_title). ")"; // } // else { // $search_title = ""; // } // $visible = stristr("Catalog & Search", $search_on); // if ($visible === FALSE) { // $query_tax_visible = "SELECT post_id FROM {$wpdb->prefix}postmeta // WHERE meta_key IN ('_tax_status','_visibility') // AND meta_value LIKE '%$search_on%'"; // } // else { // if(count( $search_ons ) > 1) { // $query_tax_visible = "SELECT post_id FROM {$wpdb->prefix}postmeta // WHERE meta_key IN ('_visibility') // AND (meta_value LIKE '%visible%')"; // } // else { // $query_tax_visible = "SELECT post_id FROM {$wpdb->prefix}postmeta // WHERE meta_key IN ('_visibility') // AND (meta_value LIKE '%visible%' // OR meta_value LIKE '%$search_on%')"; // } // } // $results_tax_visible = $wpdb->get_col( $query_tax_visible ); // $rows_tax_visible = $wpdb->num_rows; // if ($rows_tax_visible > 0) { // $search_tax_visible = " OR products.ID IN (" .implode(",",$results_tax_visible). ") OR products.post_parent IN (" .implode(",",$results_tax_visible). ")"; // } // else { // $search_tax_visible = ""; // } // if ( is_array( $search_ons ) && count( $search_ons ) >= 1 ) { // $search_condn = " HAVING "; // foreach ( $search_ons as $search_on ) { // $search_condn .= " (concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%$search_on%' // OR post_content LIKE '%$search_on%' // OR post_excerpt LIKE '%$search_on%' // OR prod_othermeta_value LIKE '%$search_on%') // "; // $search_condn .= " OR"; // } // if( $rows == 1 ) { // $query_ids1 = "SELECT GROUP_CONCAT(post_id ORDER BY post_id SEPARATOR ',') as id FROM {$wpdb->prefix}postmeta WHERE meta_value IN ('". implode("','",$search_text) ."') AND meta_key like 'attribute_%'"; // $records_id1 = implode(",",$wpdb->get_col ( $query_ids1 )); // $search_condn .= " products.id IN ($records_id1)"; // $search_condn .= " OR"; // } // $search_condn_count = " AND(" . substr( $search_condn_count, 0, -2 ) . ")"; // $search_condn = substr( $search_condn, 0, -2 ); // $search_condn .= $search_title . $search_category .$search_tax_visible; // } // else { // $search_condn = " HAVING concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%$search_on%' // OR post_content LIKE '%$search_on%' // OR post_excerpt LIKE '%$search_on%' // OR prod_othermeta_value LIKE '%$search_on%' // $search_title // $search_category // $search_tax_visible // "; // if( $rows == 1 ) { // $query_ids1 = "SELECT GROUP_CONCAT(post_id ORDER BY post_id SEPARATOR ',') as id FROM {$wpdb->prefix}postmeta WHERE meta_value IN ('". implode("','",$search_text) ."') AND meta_key like 'attribute_%';"; // $records_id1 = implode(",",$wpdb->get_col ( $query_ids1 )); // if ( !empty( $records_id1 ) ) // $search_condn .= " OR products.id IN ($records_id1)"; // } // } // } //Code for handling advanced search conditions if (!empty($advanced_search_query)) { $index_search_string = 1; // index to keep a track of flags in the advanced search temp foreach ($advanced_search_query as &$advanced_search_query_string) { //Condn for terms if (!empty($advanced_search_query_string['cond_terms'])) { $cond_terms_array = explode(" AND ", $advanced_search_query_string['cond_terms']); $cond_terms_col_name = !empty($advanced_search_query_string['cond_terms_col_name']) ? explode(" AND ", $advanced_search_query_string['cond_terms_col_name']) : ''; $cond_terms_col_value = !empty($advanced_search_query_string['cond_terms_col_value']) ? explode(" AND ", $advanced_search_query_string['cond_terms_col_value']) : ''; $cond_terms_operator = !empty($advanced_search_query_string['cond_terms_operator']) ? explode(" AND ", $advanced_search_query_string['cond_terms_operator']) : ''; $cond_terms_post_ids = ''; $cond_cat_post_ids = array(); // array for storing the cat post ids $index = 0; $terms_cat_search_taxonomy_ids = array(); $terms_att_search_flag = 0; $query_terms_search_count_array = array(); $terms_advanced_search_from = ''; $terms_advanced_search_where = ''; $result_terms_search = ''; foreach ($cond_terms_array as $cond_terms) { $query_advanced_search_taxonomy_id = "SELECT {$wpdb->prefix}term_taxonomy.term_taxonomy_id\n FROM {$wpdb->prefix}term_taxonomy\n JOIN {$wpdb->prefix}terms\n ON ( {$wpdb->prefix}terms.term_id = {$wpdb->prefix}term_taxonomy.term_id)\n WHERE " . $cond_terms; $result_advanced_search_taxonomy_id = $wpdb->get_col($query_advanced_search_taxonomy_id); if (!empty($result_advanced_search_taxonomy_id)) { $terms_search_result_flag = $index == sizeof($cond_terms_array) - 1 ? ', ' . $index_search_string : ', 0'; $terms_advanced_search_select = "SELECT DISTINCT {$wpdb->prefix}posts.id " . $terms_search_result_flag; //code for getting the post ids for attributes if (!empty($cond_terms_col_name[$index]) && trim($cond_terms_col_name[$index]) != 'product_cat') { $terms_advanced_search_select .= " ,0"; $terms_advanced_search_from = " FROM {$wpdb->prefix}posts\n LEFT JOIN {$wpdb->prefix}term_relationships\n ON ({$wpdb->prefix}term_relationships.object_id = {$wpdb->prefix}posts.id)\n JOIN {$wpdb->prefix}postmeta\n ON ( {$wpdb->prefix}postmeta.post_id = {$wpdb->prefix}posts.id)"; $terms_advanced_search_where = "WHERE (({$wpdb->prefix}term_relationships.term_taxonomy_id IN (" . implode(",", $result_advanced_search_taxonomy_id) . ")\n AND {$wpdb->prefix}posts.id NOT IN (SELECT post_parent FROM {$wpdb->prefix}posts \n WHERE post_parent > 0 ) )\n OR ({$wpdb->prefix}postmeta.meta_key LIKE 'attribute_" . trim($cond_terms_col_name[$index]) . "' AND {$wpdb->prefix}postmeta.meta_value " . $cond_terms_operator[$index] . " '" . trim($cond_terms_col_value[$index]) . "'))"; //Flag to handle the child ids for cat advanced search $terms_att_search_flag = 1; } else { if (!empty($cond_terms_col_name[$index]) && trim($cond_terms_col_name[$index]) == 'product_cat') { $terms_advanced_search_select .= " ,1"; $terms_advanced_search_from = "FROM {$wpdb->prefix}posts\n JOIN {$wpdb->prefix}term_relationships\n ON ({$wpdb->prefix}term_relationships.object_id = {$wpdb->prefix}posts.id)"; $terms_advanced_search_where = "WHERE {$wpdb->prefix}term_relationships.term_taxonomy_id IN (" . implode(",", $result_advanced_search_taxonomy_id) . ")"; } } //Query to find if there are any previous conditions $count_temp_previous_cond = $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp \n SET flag = 0\n WHERE flag = " . $index_search_string); //Code to handle condition if the ids of previous cond are present in temp table if ($index == 0 && $count_temp_previous_cond > 0 || !empty($result_terms_search)) { $terms_advanced_search_from .= " JOIN {$wpdb->base_prefix}sm_advanced_search_temp\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.id)"; $terms_advanced_search_where .= "AND {$wpdb->base_prefix}sm_advanced_search_temp.flag = 0"; } $result_terms_search = array(); if (!empty($terms_advanced_search_select) && !empty($terms_advanced_search_from) && !empty($terms_advanced_search_where)) { $query_terms_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\n (" . $terms_advanced_search_select . " " . $terms_advanced_search_from . " " . $terms_advanced_search_where . " " . ")"; $result_terms_search = $wpdb->query($query_terms_search); } //Code to handle child ids in case of category search if (!empty($result_terms_search) && trim($cond_terms_col_name[$index]) == 'product_cat') { //query when attr cond has been applied if ($terms_att_search_flag == 1) { $query_terms_search_cat_child = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\n ( SELECT {$wpdb->prefix}posts.id " . $terms_search_result_flag . " ,1\n FROM {$wpdb->prefix}posts\n JOIN {$wpdb->base_prefix}sm_advanced_search_temp AS temp1\n ON (temp1.product_id = {$wpdb->prefix}posts.id)\n JOIN {$wpdb->base_prefix}sm_advanced_search_temp AS temp2\n ON (temp2.product_id = {$wpdb->prefix}posts.post_parent)\n WHERE temp2.cat_flag = 1 )"; } else { //query when no attr cond has been applied $query_terms_search_cat_child = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\n ( SELECT {$wpdb->prefix}posts.id " . $terms_search_result_flag . " ,1\n FROM {$wpdb->prefix}posts \n JOIN {$wpdb->base_prefix}sm_advanced_search_temp\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.post_parent)\n WHERE {$wpdb->base_prefix}sm_advanced_search_temp.cat_flag = 1 )"; } $result_terms_search_cat_child = $wpdb->query($query_terms_search_cat_child); } } $index++; } //Query to reset the cat_flag $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp SET cat_flag = 0"); //Code to delete the unwanted post_ids $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp WHERE flag = 0"); } //Cond for postmeta if (!empty($advanced_search_query_string['cond_postmeta'])) { $cond_postmeta_array = explode(" AND ", $advanced_search_query_string['cond_postmeta']); $cond_postmeta_col_name = !empty($advanced_search_query_string['cond_postmeta_col_name']) ? explode(" AND ", $advanced_search_query_string['cond_postmeta_col_name']) : ''; $cond_postmeta_col_value = !empty($advanced_search_query_string['cond_postmeta_col_value']) ? explode(" AND ", $advanced_search_query_string['cond_postmeta_col_value']) : ''; $cond_postmeta_operator = !empty($advanced_search_query_string['cond_postmeta_operator']) ? explode(" AND ", $advanced_search_query_string['cond_postmeta_operator']) : ''; $index = 0; $cond_postmeta_post_ids = ''; $result_postmeta_search = ''; foreach ($cond_postmeta_array as $cond_postmeta) { $postmeta_advanced_search_from = ''; $postmeta_advanced_search_where = ''; $cond_postmeta_col_name1 = !empty($cond_postmeta_col_name[$index]) ? trim($cond_postmeta_col_name[$index]) : ''; $cond_postmeta_col_value1 = !empty($cond_postmeta_col_value[$index]) ? trim($cond_postmeta_col_value[$index]) : ''; $cond_postmeta_operator1 = !empty($cond_postmeta_operator[$index]) ? trim($cond_postmeta_operator[$index]) : ''; $cond_postmeta_custom_att = $cond_postmeta_col_name1 == '_product_attributes' ? " OR (wp_postmeta.meta_key LIKE 'attribute%' AND wp_postmeta.meta_value " . $cond_postmeta_operator1 . " '%" . $cond_postmeta_col_value1 . "%')" : ''; $postmeta_search_result_flag = $index == sizeof($cond_postmeta_array) - 1 ? ', ' . $index_search_string : ', 0'; //Query to find if there are any previous conditions $count_temp_previous_cond = $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp \n SET flag = 0\n WHERE flag = " . $index_search_string); //Code to handle condition if the ids of previous cond are present in temp table if ($index == 0 && $count_temp_previous_cond > 0 || !empty($result_postmeta_search)) { $postmeta_advanced_search_from = " JOIN {$wpdb->base_prefix}sm_advanced_search_temp\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}postmeta.post_id)"; $postmeta_advanced_search_where = " AND {$wpdb->base_prefix}sm_advanced_search_temp.flag = 0"; } $query_postmeta_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\n (SELECT DISTINCT {$wpdb->prefix}postmeta.post_id " . $postmeta_search_result_flag . " ,0\n FROM {$wpdb->prefix}postmeta " . $postmeta_advanced_search_from . "\n WHERE " . $cond_postmeta . " " . $cond_postmeta_custom_att . " " . $postmeta_advanced_search_where . ")"; $result_postmeta_search = $wpdb->query($query_postmeta_search); $index++; } //Query to delete the unwanted post_ids $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp WHERE flag = 0"); } //Cond for posts if (!empty($advanced_search_query_string['cond_posts'])) { $cond_posts_array = explode(" AND ", $advanced_search_query_string['cond_posts']); $index = 0; $cond_posts_post_ids = ''; $result_posts_search = ''; foreach ($cond_posts_array as $cond_posts) { $posts_advanced_search_from = ''; $posts_advanced_search_where = ''; $posts_search_result_flag = $index == sizeof($cond_posts_array) - 1 ? ', ' . $index_search_string : ', 0'; $posts_search_result_cat_flag = $index == sizeof($cond_posts_array) - 1 ? ", 999" : ', 0'; //Query to find if there are any previous conditions $count_temp_previous_cond = $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp \n SET flag = 0\n WHERE flag = " . $index_search_string); //Code to handle condition if the ids of previous cond are present in temp table if ($index == 0 && $count_temp_previous_cond > 0 || !empty($result_posts_search)) { $posts_advanced_search_from = " JOIN {$wpdb->base_prefix}sm_advanced_search_temp\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.id)"; $posts_advanced_search_where = " AND {$wpdb->base_prefix}sm_advanced_search_temp.flag = 0"; } $query_posts_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\n (SELECT DISTINCT {$wpdb->prefix}posts.id " . $posts_search_result_flag . " " . $posts_search_result_cat_flag . "\n FROM {$wpdb->prefix}posts " . $posts_advanced_search_from . "\n WHERE " . $cond_posts . " " . $posts_advanced_search_where . ")"; $result_posts_search = $wpdb->query($query_posts_search); $index++; } //Query to get the variations of the parent product in result set $query_posts_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\n (SELECT DISTINCT {$wpdb->prefix}posts.id ," . $index_search_string . ", 0\n FROM {$wpdb->prefix}posts \n JOIN {$wpdb->base_prefix}sm_advanced_search_temp \n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.post_parent)\n WHERE {$wpdb->base_prefix}sm_advanced_search_temp.cat_flag = 999\n AND {$wpdb->base_prefix}sm_advanced_search_temp.flag = " . $index_search_string . ")"; $result_posts_search = $wpdb->query($query_posts_search); //Query to delete the unwanted post_ids $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp WHERE flag = 0"); } $index_search_string++; } } //for combined $advanced_search_from = ''; $advanced_search_where = ''; if (!empty($advanced_search_query) || $sm_advanced_search_results_persistent == 1) { $advanced_search_from = " JOIN {$wpdb->base_prefix}sm_advanced_search_temp\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.id)"; $advanced_search_where = " AND {$wpdb->base_prefix}sm_advanced_search_temp.flag > 0"; } $from = "FROM {$wpdb->prefix}posts"; // and // {$wpdb->prefix}postmeta.meta_key IN ('_regular_price','_sale_price','_sale_price_dates_from','_sale_price_dates_to','_sku','_stock','_weight','_height','_length','_width','_price','_thumbnail_id','_tax_status','_min_variation_regular_price','_min_variation_sale_price','_min_variation_price','_visibility','_product_attributes','" . implode( "','", $variation ) . "') $from_export = "FROM {$wpdb->prefix}posts\n JOIN {$wpdb->prefix}postmeta ON ({$wpdb->prefix}postmeta.post_id = {$wpdb->prefix}posts.id)"; $where = " WHERE {$wpdb->prefix}posts.post_status IN {$post_status}\n AND {$wpdb->prefix}posts.post_type IN {$post_type}\n {$trash_id}\n {$search}"; $group_by = " GROUP BY {$wpdb->prefix}posts.id "; //Query for getting the actual data loaded into the smartManager $query = !empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWoo' ? "{$select} {$from_export} {$advanced_search_from} {$where} {$advanced_search_where} {$group_by} {$search_condn} {$order_by} {$limit_string};" : "{$select} {$from} {$advanced_search_from} {$where} {$advanced_search_where} {$group_by} {$search_condn} {$order_by} {$limit_string};"; // $query = "$select $from_export $where $group_by $search_condn $order_by"; $records = $wpdb->get_results($query, 'ARRAY_A'); $num_rows = $wpdb->num_rows; //Query for getting the count of the number of products loaded into the smartManager $recordcount_result = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $recordcount_result[0]['count']; if ($num_rows <= 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', $sm_text_domain); } else { if (empty($_POST['func_nm'])) { $post_ids = array(); foreach ($records as $record) { $post_ids[] = $record['id']; } // and // prod_othermeta.meta_key IN ('_regular_price','_sale_price','_sale_price_dates_from','_sale_price_dates_to','_sku','_stock','_weight','_height','_length','_width','_price','_thumbnail_id','_tax_status','_min_variation_regular_price','_min_variation_sale_price','_min_variation_price','_visibility','_product_attributes','" . implode( "','", $variation ) . "') $query_postmeta = "SELECT prod_othermeta.post_id as post_id,\n\n GROUP_CONCAT(prod_othermeta.meta_key order by prod_othermeta.meta_id SEPARATOR ' #sm# ') AS prod_othermeta_key,\n GROUP_CONCAT(prod_othermeta.meta_value order by prod_othermeta.meta_id SEPARATOR ' #sm# ') AS prod_othermeta_value\n FROM {$wpdb->prefix}postmeta as prod_othermeta \n WHERE post_id IN (" . implode(",", $post_ids) . ") \n GROUP BY post_id"; $records_postmeta = $wpdb->get_results($query_postmeta, 'ARRAY_A'); $products_meta_data = array(); foreach ($records_postmeta as $record_postmeta) { $products_meta_data[$record_postmeta['post_id']] = array(); $products_meta_data[$record_postmeta['post_id']]['prod_othermeta_key'] = $record_postmeta['prod_othermeta_key']; $products_meta_data[$record_postmeta['post_id']]['prod_othermeta_value'] = $record_postmeta['prod_othermeta_value']; } foreach ($records as &$record) { $record['prod_othermeta_key'] = $products_meta_data[$record['id']]['prod_othermeta_key']; $record['prod_othermeta_value'] = $products_meta_data[$record['id']]['prod_othermeta_value']; } } $export_column_header = array(); for ($i = 0; $i < $num_rows; $i++) { // $records[$i]['post_content'] = str_replace('"','\'',$records[$i]['post_content']); // $records[$i]['post_excerpt'] = str_replace('"','\'',$records[$i]['post_excerpt']); // $records[$i]['post_excerpt'] = json_encode(addslashes($records[$i]['post_excerpt'])); //$records[$i]['post_content'] = json_encode(addslashes($records[$i]['post_content'])); //$records[$i]['post_excerpt'] = htmlspecialchars($records[$i]['post_excerpt']); //$records[$i]['post_content'] = htmlspecialchars($records[$i]['post_content']); $records[$i]['post_excerpt'] = ''; $records[$i]['post_content'] = ''; $prod_meta_values = explode(' #sm# ', $records[$i]['prod_othermeta_value']); $prod_meta_key = explode(' #sm# ', $records[$i]['prod_othermeta_key']); if (count($prod_meta_values) != count($prod_meta_key)) { continue; } if (!empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWoo' && empty($export_column_header)) { $export_column_header = $prod_meta_key; } unset($records[$i]['prod_othermeta_value']); unset($records[$i]['prod_othermeta_key']); $temp = array_combine($prod_meta_key, $prod_meta_values); $product_type = wp_get_object_terms($records[$i]['id'], 'product_type', array('fields' => 'slugs')); //for formatting meta keys of custom keys $prod_meta_key_values = array(); if (empty($_POST['func_nm'])) { foreach ($temp as $key => $val) { $key = preg_replace('/[^A-Za-z0-9\\-_]/', '', $key); $prod_meta_key_values[$key] = $val; // Removes special chars. } } else { $prod_meta_key_values = $temp; } // Code to get the Category Name from the term_taxonomy_id if (isset($category_ids_all[$records[$i]['id']])) { //$category_id = explode('###', $records[$i]['term_taxonomy_id']); $category_names = ""; // unset($records[$i]['term_taxonomy_id']); $category_id = explode(' #sm# ', $category_ids_all[$records[$i]['id']]); for ($j = 0; $j < sizeof($category_id); $j++) { if (isset($term_taxonomy[$category_id[$j]])) { $category_names .= $term_taxonomy[$category_id[$j]] . ', '; } } if ($category_names != "") { $category_names = substr($category_names, 0, -2); $records[$i]['category'] = $category_names; } } else { $records[$i]['category'] = ""; } $product_type = !empty($product_type[0]) ? $product_type[0] : ''; $records[$i]['category'] = $records[$i]['post_parent'] > 0 && $product_type == 'simple' || $records[$i]['post_parent'] == 0 ? !empty($records[$i]['category']) ? $records[$i]['category'] : '' : ''; // To hide category name from Product's variations //Attributes Column if (isset($prod_meta_key_values['_product_attributes']) && $prod_meta_key_values['_product_attributes'] != "") { $prod_attr = unserialize($prod_meta_key_values['_product_attributes']); $attributes_list = ""; //cond added for handling blank data if (is_array($prod_attr) && !empty($prod_attr)) { foreach ($prod_attr as $prod_attr1) { $attribute_terms = ""; if (isset($attributes_label[$prod_attr1['name']]) && isset($product_attributes[$prod_attr1['name']])) { if (!empty($category_id)) { foreach ($category_id as $category_id1) { if (isset($product_attributes[$prod_attr1['name']][$category_id1])) { $attribute_terms .= $product_attributes[$prod_attr1['name']][$category_id1] . ', '; } } } if ($attribute_terms != "") { $attribute_terms = substr($attribute_terms, 0, -2); $attributes_list .= $attributes_label[$prod_attr1['name']] . ": [" . $attribute_terms . "]"; $attributes_list .= "<br>"; } } elseif ($prod_attr1['is_taxonomy'] == 0) { $attributes_list .= $prod_attr1['name'] . ": [" . str_replace(" |", ",", $prod_attr1['value']) . "]"; $attributes_list .= "<br>"; } } } // $records[$i]['product_attributes'] = substr( $attributes_list, 0, -3); $records[$i]['product_attributes'] = $attributes_list; } else { $records[$i]['product_attributes'] = ""; } if (isset($prod_meta_key_values['_sale_price_dates_from']) && !empty($prod_meta_key_values['_sale_price_dates_from'])) { $prod_meta_key_values['_sale_price_dates_from'] = date('Y-m-d', (int) $prod_meta_key_values['_sale_price_dates_from']); } if (isset($prod_meta_key_values['_sale_price_dates_to']) && !empty($prod_meta_key_values['_sale_price_dates_to'])) { $prod_meta_key_values['_sale_price_dates_to'] = date('Y-m-d', (int) $prod_meta_key_values['_sale_price_dates_to']); } $records[$i] = array_merge((array) $records[$i], $prod_meta_key_values); $thumbnail = isset($records[$i]['_thumbnail_id']) ? wp_get_attachment_image_src($records[$i]['_thumbnail_id'], $image_size) : ''; $records[$i]['thumbnail'] = !empty($thumbnail[0]) ? $thumbnail[0] : false; $records[$i]['_tax_status'] = !empty($prod_meta_key_values['_tax_status']) ? $prod_meta_key_values['_tax_status'] : ''; // Setting product type for grouped products if ($records[$i]['post_parent'] != 0) { $product_type_parent = wp_get_object_terms($records[$i]['post_parent'], 'product_type', array('fields' => 'slugs')); if ($product_type_parent[0] == "grouped") { $records[$i]['product_type'] = $product_type_parent[0]; } } else { // $records[$i]['product_type'] = $product_type[0]; $records[$i]['product_type'] = $product_type; } $records[$i]['total_sales'] = !empty($records[$i]['total_sales']) ? $records[$i]['total_sales'] : '0'; //added in woo23 if ($show_variation === true) { if ($records[$i]['post_parent'] != 0 && $product_type_parent[0] != "grouped") { $records[$i]['post_status'] = get_post_status($records[$i]['post_parent']); // Code for assigning the parent sku if sku is blank if (empty($records[$i]['_sku'])) { $records[$i]['_sku'] = get_post_meta($records[$i]['post_parent'], '_sku'); } if ($_POST['SM_IS_WOO16'] == "true") { $records[$i]['_regular_price'] = $records[$i]['_price']; } $variation_names = ''; foreach ($variation as $slug) { $prod_meta_key_values_slug = !empty($prod_meta_key_values[$slug]) ? $prod_meta_key_values[$slug] : ''; $variation_names .= isset($attributes[$prod_meta_key_values_slug]) && !empty($attributes[$prod_meta_key_values_slug]) ? $attributes[$prod_meta_key_values_slug] . ', ' : ucfirst($prod_meta_key_values_slug) . ', '; } $records[$i]['post_title'] = get_the_title($records[$i]['post_parent']) . " - " . trim($variation_names, ", "); $records[$i]['product_attributes'] = ''; //for clearing the attributes field for variations if exists $records[$i]['total_sales'] = ''; //added in woo23 // } else if ($records[$i]['post_parent'] == 0 && $product_type[0] == 'variable') { } else { if ($records[$i]['post_parent'] == 0 && $product_type == 'variable') { $records[$i]['_regular_price'] = ""; $records[$i]['_sale_price'] = ""; } else { $records[$i]['_regular_price'] = trim($records[$i]['_regular_price']); if (empty($records[$i]['_regular_price'])) { $records[$i]['_regular_price'] = $records[$i]['_price']; } } } $products[$records[$i]['id']]['post_title'] = $records[$i]['post_title']; $products[$records[$i]['id']]['variation'] = !empty($variation_names) ? $variation_names : ''; } elseif ($show_variation === false && SMPRO) { // if ($product_type[0] == 'variable') { if ($product_type == 'variable') { // WOO 2.1 compatibility if ($_POST['SM_IS_WOO21'] == "true" || $_POST['SM_IS_WOO22'] == "true") { $records[$i]['_regular_price'] = isset($variation_reg_price[$records[$i]['id']]) ? get_post_meta($variation_reg_price[$records[$i]['id']], '_regular_price', true) : 0; $records[$i]['_sale_price'] = !empty($records[$i]['_min_variation_price']) && !empty($variation_reg_price[$records[$i]['id']]) && get_post_meta($variation_reg_price[$records[$i]['id']], '_sale_price', true) ? $records[$i]['_min_variation_price'] : ''; } else { $records[$i]['_regular_price'] = $records[$i]['_min_variation_regular_price']; $records[$i]['_sale_price'] = $records[$i]['_min_variation_sale_price']; } } else { $records[$i]['_regular_price'] = trim($records[$i]['_regular_price']); // if ( empty( $records[$i]['_regular_price'] ) ) { // $records[$i]['_regular_price'] = $records[$i]['_price']; // } $records[$i]['_sale_price'] = trim($records[$i]['_sale_price']); } } else { $records[$i]['_regular_price'] = $records[$i]['_regular_price']; $records[$i]['_sale_price'] = $records[$i]['_sale_price']; } unset($records[$i]['prod_othermeta_value']); unset($records[$i]['prod_othermeta_key']); } } } elseif ($active_module == 'Customers') { //BOF Customer's module $search_condn = customers_query($_POST['searchText']); $terms_post_cond = ''; $terms_post_cond_join = ''; // WOO 2.2 compatibility if ($_POST['SM_IS_WOO22'] == "true") { $terms_post_cond_join = 'JOIN ' . $wpdb->prefix . 'posts AS posts ON (posts.ID = postmeta.post_id)'; $terms_post_cond = "AND posts.post_status IN ('wc-completed','wc-processing','wc-on-hold','wc-pending')"; } else { $query_terms = "SELECT id FROM {$wpdb->prefix}posts AS posts\n JOIN {$wpdb->prefix}term_relationships AS term_relationships \n ON term_relationships.object_id = posts.ID \n JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy \n ON term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id \n JOIN {$wpdb->prefix}terms AS terms \n ON term_taxonomy.term_id = terms.term_id\n WHERE terms.name IN ('completed','processing','on-hold','pending')\n AND posts.post_status IN ('publish')"; $terms_post = implode(",", $wpdb->get_col($query_terms)); $terms_post_cond = "AND postmeta.post_id IN ({$terms_post})"; } //Query for getting the max of post id for all the Guest Customers $query_post_guest = "SELECT postmeta.post_ID FROM {$wpdb->prefix}postmeta AS postmeta\n {$terms_post_cond_join}\n WHERE postmeta.meta_key ='_customer_user' AND postmeta.meta_value=0\n {$terms_post_cond}"; $post_id_guest = $wpdb->get_col($query_post_guest); $num_guest = $wpdb->num_rows; $result_max_id = ''; if ($num_guest > 0) { $query_max_id = "SELECT GROUP_CONCAT(distinct postmeta1.post_ID \n ORDER BY posts.post_date DESC SEPARATOR ',' ) AS all_id,\n GROUP_CONCAT(postmeta2.meta_value \n ORDER BY posts.post_date DESC SEPARATOR ',' ) AS order_total, \n date_format(max(posts.post_date),'%b %e %Y, %r') AS date,\n count(postmeta1.post_id) as count,\n sum(postmeta2.meta_value) as total\n \n FROM {$wpdb->prefix}postmeta AS postmeta1\n JOIN {$wpdb->prefix}posts AS posts ON (posts.ID = postmeta1.post_id)\n INNER JOIN {$wpdb->prefix}postmeta AS postmeta2\n ON (postmeta2.post_ID = postmeta1.post_ID AND postmeta2.meta_key IN ('_order_total'))\n\n WHERE postmeta1.meta_key IN ('_billing_email')\n AND postmeta1.post_ID IN (" . implode(",", $post_id_guest) . ") \n GROUP BY postmeta1.meta_value\n ORDER BY date desc"; $result_max_id = $wpdb->get_results($query_max_id, 'ARRAY_A'); } //Query for getting the max of post id for all the Registered Customers $query_post_user = "******"; $post_id_user = $wpdb->get_col($query_post_user); $num_user = $wpdb->num_rows; if ($num_user > 0) { $query_max_user = "******" . implode(",", $post_id_user) . ") \n GROUP BY postmeta1.meta_value\n ORDER BY date"; $result_max_user = $wpdb->get_results($query_max_user, 'ARRAY_A'); } //Code for generating the total orders, count of orders , max ids and last order total arrays for ($i = 0; $i < sizeof($result_max_id); $i++) { $temp = !empty($result_max_id[$i]['all_id']) ? explode(",", $result_max_id[$i]['all_id']) : array(); $max_ids[$i] = !empty($temp) ? $temp[0] : 0; $order_count[$max_ids[$i]] = !empty($result_max_id[$i]['count']) ? $result_max_id[$i]['count'] : ''; $order_total[$max_ids[$i]] = !empty($result_max_id[$i]['total']) ? $result_max_id[$i]['total'] : ''; //Code for getting the last Order Total $temp = !empty($result_max_id[$i]['order_total']) ? explode(",", $result_max_id[$i]['order_total']) : ''; $last_order_total[$max_ids[$i]] = !empty($temp) ? $temp[0] : ''; } $j = $k = $l = $m = 0; //initiliazing variables if (!empty($result_max_id)) { $j = sizeof($max_ids); $k = sizeof($order_count); $l = sizeof($order_total); $m = sizeof($last_order_total); } for ($i = 0; $i < sizeof($result_max_user); $i++, $j++, $k++, $l++, $m++) { $temp = !empty($result_max_user[$i]['all_id']) ? explode(",", $result_max_user[$i]['all_id']) : ''; $max_ids[$j] = !empty($temp[0]) ? $temp[0] : 0; $order_count[$max_ids[$j]] = !empty($result_max_user[$i]['count']) ? $result_max_user[$i]['count'] : ''; $order_total[$max_ids[$j]] = !empty($result_max_user[$i]['total']) ? $result_max_user[$i]['total'] : ''; $temp = !empty($result_max_user[$i]['order_total']) ? explode(",", $result_max_user[$i]['order_total']) : ''; $last_order_total[$max_ids[$j]] = !empty($temp[0]) ? $temp[0] : ''; } $max_id = !empty($max_ids) ? implode(",", $max_ids) : ''; $max_id_join = ''; $orderby_cond = ''; if (!empty($max_ids)) { $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp"); $max_ids_inserted = !empty($max_ids) ? '(' . implode("),(", $max_ids) . ')' : ''; $wpdb->query("REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp (product_id) VALUES " . $max_ids_inserted); $max_id_join = " JOIN {$wpdb->base_prefix}sm_advanced_search_temp as temp ON (temp.product_id = posts.id)"; //$orderby_cond = "ORDER BY temp.product_id"; $orderby_cond = "ORDER BY posts.ID"; } // $postid_cond = (!empty($max_ids)) ? "AND posts.ID IN ($max_id)" : ''; // $orderby_cond = (!empty($max_ids)) ? "ORDER BY FIND_IN_SET(posts.ID,'$max_id')" : "ORDER BY posts.ID"; $customers_query = "SELECT SQL_CALC_FOUND_ROWS\n DISTINCT(GROUP_CONCAT( postmeta.meta_value\n ORDER BY postmeta.meta_id SEPARATOR '###' ) )AS meta_value,\n GROUP_CONCAT(distinct postmeta.meta_key\n ORDER BY postmeta.meta_id SEPARATOR '###' ) AS meta_key,\n date_format(max(posts.post_date),'%b %e %Y, %r') AS date,\n posts.ID AS id\n\n FROM {$wpdb->prefix}posts AS posts\n RIGHT JOIN {$wpdb->prefix}postmeta AS postmeta\n ON (posts.ID = postmeta.post_id AND postmeta.meta_key IN\n ('_billing_first_name' , '_billing_last_name' , '_billing_email',\n '_billing_address_1', '_billing_address_2', '_billing_city', '_billing_state',\n '_billing_country','_billing_postcode', '_billing_phone','_customer_user'))"; // WOO 2.2 compatibility $post_status_cond = "AND posts.post_status IN ('publish')"; if ($_POST['SM_IS_WOO22'] == "true") { $post_status_cond = "AND posts.post_status NOT IN ('trash')"; } $where = " WHERE posts.post_type LIKE 'shop_order' \n {$post_status_cond}\n {$postid_cond}"; $group_by = " GROUP BY posts.ID"; $limit_query = " {$orderby_cond} {$limit_string}"; $query = "{$customers_query} {$max_id_join} {$where} {$group_by} {$search_condn} {$limit_query};"; $result = $wpdb->get_results($query, 'ARRAY_A'); $num_rows = $wpdb->num_rows; //To get Total count $customers_count_result = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $customers_count_result[0]['count']; if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', $sm_text_domain); } else { $postmeta = array(); $user = array(); $user_order_ids = array(); $j = 0; $k = 0; for ($i = 0; $i < sizeof($result); $i++) { $meta_value = explode('###', $result[$i]['meta_value']); $meta_key = explode('###', $result[$i]['meta_key']); //note: while merging the array, $data as to be the second arg if (count($meta_key) == count($meta_value)) { $temp[$i] = array_combine($meta_key, $meta_value); } if ($temp[$i]['_customer_user'] == 0) { $postmeta[$j] = $temp[$i]; $postmeta[$j]['order_id'] = $result[$i]['id']; $j++; } elseif ($temp[$i]['_customer_user'] > 0) { $user[$k] = $temp[$i]['_customer_user']; $user_order_ids[$temp[$i]['_customer_user']] = $result[$i]['id']; $k++; } unset($meta_value); unset($meta_key); } //Query for getting the Registered Users data from wp_usermeta and wp_users table if (!empty($user)) { $user_ids = implode(",", $user); $query_users = "SELECT users.ID,users.user_email,\n GROUP_CONCAT( usermeta.meta_value ORDER BY usermeta.umeta_id SEPARATOR '###' ) AS meta_value,\n GROUP_CONCAT(distinct usermeta.meta_key\n ORDER BY usermeta.umeta_id SEPARATOR '###_' ) AS meta_key\n FROM {$wpdb->users} AS users\n JOIN {$wpdb->usermeta} AS usermeta\n ON (users.ID = usermeta.user_id AND usermeta.meta_key IN\n ('billing_first_name' , 'billing_last_name' , 'billing_email',\n 'billing_address_1', 'billing_address_2', 'billing_city', 'billing_state',\n 'billing_country','billing_postcode', 'billing_phone'))\n WHERE users.ID IN ({$user_ids})\n GROUP BY users.ID\n ORDER BY FIND_IN_SET(users.ID,'{$user_ids}');"; $result_users = $wpdb->get_results($query_users, 'ARRAY_A'); $num_rows_users = $wpdb->num_rows; for ($i = 0, $k = sizeof($postmeta); $i < sizeof($result_users); $i++, $k++) { $meta_value = explode('###', $result_users[$i]['meta_value']); $result_users[$i]['meta_key'] = "_" . $result_users[$i]['meta_key']; $meta_key = explode('###', $result_users[$i]['meta_key']); //note: while merging the array, $data as to be the second arg if (count($meta_key) == count($meta_value)) { $postmeta[$k] = array_combine($meta_key, $meta_value); $postmeta[$k]['_customer_user'] = $result_users[$i]['ID']; $postmeta[$k]['order_id'] = $user_order_ids[$result_users[$i]['ID']]; $postmeta[$k]['_billing_email'] = $result_users[$i]['user_email']; } unset($meta_value); unset($meta_key); } } $user_id = array(); for ($i = 0; $i < sizeof($postmeta); $i++) { if ($postmeta[$i]['_customer_user'] == 0) { $user_email[$i] = "'" . $postmeta[$i]['_billing_email'] . "'"; } elseif ($postmeta[$i]['_customer_user'] > 0) { $user_id[$i] = $postmeta[$i]['_customer_user']; } } for ($i = 0; $i < sizeof($postmeta); $i++) { $postmeta[$i]['id'] = $max_ids[$i]; if (SMPRO == true) { $result[$i]['_order_total'] = $last_order_total[$postmeta[$i]['order_id']]; $postmeta[$i]['count_orders'] = $order_count[$postmeta[$i]['order_id']]; $postmeta[$i]['total_orders'] = $order_total[$postmeta[$i]['order_id']]; $result[$i]['last_order'] = $result[$i]['date']; } else { $postmeta[$i]['count_orders'] = 'Pro only'; $postmeta[$i]['total_orders'] = 'Pro only'; $result[$i]['_order_total'] = 'Pro only'; $result[$i]['last_order'] = 'Pro only'; } $billing_address_2 = !empty($postmeta[$i]['_billing_address_2']) ? $postmeta[$i]['_billing_address_2'] : ''; $result[$i]['_billing_address'] = isset($postmeta[$i]['_billing_address_1']) ? $postmeta[$i]['_billing_address_1'] . ', ' . $billing_address_2 : $billing_address_2; $postmeta[$i]['_billing_state'] = isset($woocommerce->countries->states[$postmeta[$i]['_billing_country']][$postmeta[$i]['_billing_state']]) ? $woocommerce->countries->states[$postmeta[$i]['_billing_country']][$postmeta[$i]['_billing_state']] : $postmeta[$i]['_billing_state']; $postmeta[$i]['_billing_country'] = isset($woocommerce->countries->countries[$postmeta[$i]['_billing_country']]) ? $woocommerce->countries->countries[$postmeta[$i]['_billing_country']] : $postmeta[$i]['_billing_country']; unset($result[$i]['date']); unset($result[$i]['meta_key']); unset($result[$i]['meta_value']); unset($postmeta[$i]['_billing_address_1']); unset($postmeta[$i]['_billing_address_2']); //NOTE: storing old email id in an extra column in record so useful to indentify record with emailid during updates. if ($postmeta[$i]['_billing_email'] != '' || $postmeta[$i]['_billing_email'] != null) { $records[] = array_merge($postmeta[$i], $result[$i]); } } } unset($result); unset($postmeta); } elseif ($active_module == 'Orders') { if (SMPRO == true && function_exists('sm_woo_get_packing_slip') && (!empty($_POST['label']) && $_POST['label'] == 'getPurchaseLogs')) { $log_ids_arr = json_decode(stripslashes($_POST['log_ids'])); if (is_array($log_ids_arr)) { $log_ids = implode(', ', $log_ids_arr); } sm_woo_get_packing_slip($log_ids, $log_ids_arr); } if ($_POST['SM_IS_WOO22'] == "true") { $orders_select_col = ",posts.post_status as order_status"; $orders_join_cond = ""; $orders_where_cond = " AND posts.post_status NOT IN('trash')"; } else { //Code to get all the term_names along with the term_taxonomy_id in an array $query_terms = "SELECT terms.name,term_taxonomy.term_taxonomy_id \n FROM {$wpdb->prefix}term_taxonomy AS term_taxonomy\n JOIN {$wpdb->prefix}terms AS terms ON terms.term_id = term_taxonomy.term_id\n WHERE taxonomy LIKE 'shop_order_status'"; $terms = $wpdb->get_results($query_terms, 'ARRAY_A'); for ($i = 0; $i < sizeof($terms); $i++) { $terms_name[$terms[$i]['term_taxonomy_id']] = $terms[$i]['name']; $terms_id[$i] = $terms[$i]['term_taxonomy_id']; } $terms_post = implode(",", $terms_id); $orders_select_col = ",term_relationships.term_taxonomy_id AS term_taxonomy_id"; $orders_join_cond = "JOIN {$wpdb->prefix}term_relationships AS term_relationships \n ON (term_relationships.object_id = posts.ID )"; $orders_where_cond = "AND posts.post_status IN ('publish','draft','auto-draft')\n AND term_relationships.term_taxonomy_id IN ({$terms_post})"; } //Code for Sequential Orders compatibility if (is_plugin_active('woocommerce-sequential-order-numbers-pro/woocommerce-sequential-order-numbers.php')) { $order_formatted = ", '_order_number_formatted'"; } else { $order_formatted = ""; } $select_query = "SELECT SQL_CALC_FOUND_ROWS posts.ID as id,\n posts.post_excerpt as order_note,\n date_format(posts.post_date,'%b %e %Y, %r') AS date,\n GROUP_CONCAT( postmeta.meta_value \n ORDER BY postmeta.meta_id\n SEPARATOR '###' ) AS meta_value,\n GROUP_CONCAT(distinct postmeta.meta_key\n ORDER BY postmeta.meta_id \n SEPARATOR '###' ) AS meta_key\n {$orders_select_col}\n \n FROM {$wpdb->prefix}posts AS posts \n {$orders_join_cond}\n RIGHT JOIN {$wpdb->prefix}postmeta AS postmeta \n ON (posts.ID = postmeta.post_id AND postmeta.meta_key IN \n ('_billing_first_name' , '_billing_last_name' , '_billing_email',\n '_shipping_first_name', '_shipping_last_name', '_shipping_address_1', '_shipping_address_2',\n '_shipping_city', '_shipping_state', '_shipping_country','_shipping_postcode',\n '_shipping_method', '_payment_method', '_order_items', '_order_total',\n '_shipping_method_title', '_payment_method_title','_customer_user','_billing_phone',\n '_order_shipping', '_order_discount', '_cart_discount', '_order_tax', '_order_shipping_tax', '_order_currency', 'coupons'" . $order_formatted . "))"; $group_by = " GROUP BY posts.ID"; $limit_query = " ORDER BY posts.ID DESC {$limit_string} ;"; $where = " WHERE posts.post_type LIKE 'shop_order' \n {$orders_where_cond}"; if (isset($_POST['fromDate'])) { $from_date = date('Y-m-d H:i:s', (int) strtotime($_POST['fromDate'])); $date_start = date('Y-m-d', (int) strtotime($_POST['fromDate'])); $date = date('Y-m-d', (int) strtotime($_POST['toDate'])); if ($date_start == $date && $date == date('Y-m-d')) { $curr_time_gmt = date('H:i:s', time() - date("Z")); $new_date = $date . " " . $curr_time_gmt; $to_date = date('Y-m-d H:i:s', (int) strtotime($new_date) + get_option('gmt_offset') * HOUR_IN_SECONDS); } else { $to_date = $date . " 23:59:59"; } if (SMPRO == true) { $where .= " AND posts.post_date BETWEEN '{$from_date}' AND '{$to_date}'"; } } $search_condn = ''; if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $multiple_search_terms = explode('\\"', trim($_POST['searchText'])); $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); //Query for getting the user_id based on the email enetered in the Search Box $query_user_email = "SELECT id FROM {$wpdb->prefix}users \n WHERE user_email like '%{$search_on}%'"; $result_user_email = $wpdb->get_col($query_user_email); $num_rows_email = $wpdb->num_rows; if ($num_rows_email == 0) { $query_user_email = "SELECT DISTINCT p2.meta_value \n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_email'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value > 0\n AND p1.meta_value like '%{$search_on}%'"; $result_user_email = $wpdb->get_col($query_user_email); $num_rows_email1 = $wpdb->num_rows; } //Query for getting the user_id based on the Customer phone number enetered in the Search Box $query_user_phone = "SELECT user_id FROM {$wpdb->prefix}usermeta \n WHERE meta_key='billing_phone' \n AND meta_value like '%{$search_on}%'"; $result_user_phone = $wpdb->get_col($query_user_phone); $num_rows_phone = $wpdb->num_rows; if ($num_rows_phone == 0) { $query_user_phone = "SELECT DISTINCT p2.meta_value \n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_phone'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value > 0\n AND p1.meta_value like '%{$search_on}%'"; $result_user_phone = $wpdb->get_col($query_user_phone); $num_rows_phone1 = $wpdb->num_rows; } $query_terms = "SELECT term_taxonomy_id FROM {$wpdb->prefix}term_taxonomy\n WHERE term_id IN (SELECT term_id FROM {$wpdb->prefix}terms"; // name like '%$search_on%') // $multiple_search_terms = explode( '\"', $search_on ); if (!empty($multiple_search_terms)) { $query_terms .= " WHERE"; foreach ($multiple_search_terms as $search_status) { $search_status = trim($search_status); if (!empty($search_status)) { $query_terms .= " name like '%{$search_status}%' OR"; } } $query_terms = trim($query_terms, ' OR'); } $query_terms .= ")"; $result_terms = implode(",", $wpdb->get_col($query_terms)); $num_terms = $wpdb->num_rows; // Start: Query for searching product names in order if ($_POST['SM_IS_WOO16'] == "false") { $query_product_names = "SELECT order_id\n FROM {$wpdb->prefix}woocommerce_order_items"; if (!empty($multiple_search_terms)) { $query_product_names .= " WHERE"; foreach ($multiple_search_terms as $product_name) { $product_name = trim($product_name); if (!empty($product_name)) { $query_product_names .= " order_item_name LIKE '%{$product_name}%' OR"; } } $query_product_names = trim($query_product_names, ' OR'); } } else { $query_product_names = "SELECT post_id\n FROM {$wpdb->prefix}postmeta\n WHERE meta_key LIKE '%_order_items%'"; if (!empty($multiple_search_terms)) { $query_product_names .= " AND ("; foreach ($multiple_search_terms as $product_name) { $product_name = trim($product_name); if (!empty($product_name)) { $query_product_names .= " meta_value LIKE '%{$product_name}%' OR"; } } $query_product_names = trim($query_product_names, ' OR'); $query_product_names .= ")"; } } $result_product_ids = $wpdb->get_col($query_product_names); $num_product_ids = $wpdb->num_rows; // End: Query for searching product names in order //Query to get the post_id of the products whose sku code matches with the one type in the search text box of the Orders Module $query_sku = "SELECT post_id FROM {$wpdb->prefix}postmeta\n WHERE meta_key = '_sku'\n AND meta_value like '%{$search_on}%'"; $result_sku = $wpdb->get_col($query_sku); $rows_sku = $wpdb->num_rows; //Code for handling the Search functionality of the Orders Module using the SKU code of the product if ($rows_sku > 0) { if ($_POST['SM_IS_WOO16'] == "false") { $query_order_by_sku = "SELECT order_id\n FROM {$wpdb->prefix}woocommerce_order_items AS woocommerce_order_items\n LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woocommerce_order_itemmeta USING ( order_item_id )\n WHERE woocommerce_order_itemmeta.meta_key IN ( '_product_id', '_variation_id' )\n AND woocommerce_order_itemmeta.meta_value IN ( " . implode(',', $result_sku) . " )"; $results_order_by_sku = $wpdb->get_col($query_order_by_sku); $num_order_by_sku = $wpdb->num_rows; if ($num_order_by_sku > 0) { $search_condn = " HAVING id IN ( " . implode(',', $results_order_by_sku) . " )"; } } else { //Query for getting all the distinct attribute meta key names $query_variation = "SELECT DISTINCT meta_key as variation\n FROM {$wpdb->prefix}postmeta\n WHERE meta_key like 'attribute_%'"; $variation = $wpdb->get_col($query_variation); //Query to get all the product title's as displayed in the products module along wih the post_id and SKU code in an array $query_product = "SELECT posts.id, posts.post_title, posts.post_parent, \n GROUP_CONCAT( postmeta.meta_value \n ORDER BY postmeta.meta_id\n SEPARATOR ',' ) AS meta_value\n FROM {$wpdb->prefix}posts AS posts\n JOIN {$wpdb->prefix}postmeta AS postmeta\n ON (posts.ID = postmeta.post_id\n AND postmeta.meta_key IN ('_sku','" . implode("','", $variation) . "'))\n GROUP BY posts.id"; $result_product = $wpdb->get_results($query_product, 'ARRAY_A'); //Code to store all the products title in an array with the post_id as the array index for ($i = 0; $i < sizeof($result_product); $i++) { $product_title[$result_product[$i]['id']]['post_title'] = $result_product[$i]['post_title']; $product_title[$result_product[$i]['id']]['variation_title'] = $result_product[$i]['meta_value']; $product_title[$result_product[$i]['id']]['post_parent'] = $result_product[$i]['post_parent']; } $post_title = array(); $variation_title = array(); $search_condn = "HAVING"; for ($i = 0; $i < sizeof($result_sku); $i++) { $product_type = wp_get_object_terms($result_sku[$i], 'product_type', array('fields' => 'slugs')); // Getting the type of the product //Code to prepare the search condition for the search using SKU Code if ($product_title[$result_sku[$i]]['post_parent'] == 0) { $post_title[$i] = $product_title[$result_sku[$i]]['post_title']; $search_condn .= " meta_value like '%s:4:\"name\"%\"{$post_title[$i]}\"%' "; $search_condn .= "OR"; } elseif ($product_title[$result_sku[$i]]['post_parent'] > 0) { $temp = explode(",", $product_title[$result_sku[$i]]['variation_title']); $post_title[$i] = $product_title[$product_title[$result_sku[$i]]['post_parent']]['post_title']; $search_condn .= " meta_value like '%s:4:\"name\"%\"{$post_title[$i]}\"%' "; $search_condn .= "AND ("; for ($j = 1; $j < sizeof($temp); $j++) { $search_condn .= " meta_value like '%s:10:\"meta_value\"%\"{$temp[$j]}\"%' "; $search_condn .= "OR"; } $search_condn = substr($search_condn, 0, -2) . ")"; $search_condn .= "OR"; } } $variation_title = array_unique($variation_title); $search_condn = substr($search_condn, 0, -2); } } elseif ($num_product_ids > 0) { $search_condn = " HAVING id IN ( " . implode(',', $result_product_ids) . " )"; } elseif ($num_rows_email > 0) { // Query to bring the matching email of the Guest uers $query = "SELECT DISTINCT p1.meta_value \n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_email'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value = 0\n AND p1.meta_value like '%{$search_on}%'"; $result_email_guest = $wpdb->get_col($query); $rows_email_guest = $wpdb->num_rows; $query_email = "SELECT DISTINCT(p1.meta_value)\n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_email'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value IN (" . implode(",", $result_user_email) . ")"; $result_email = $wpdb->get_col($query_email); if ($rows_email_guest > 0) { for ($i = 0, $j = sizeof($result_email); $i < sizeof($result_email_guest); $i++, $j++) { $result_email[$j] = $result_email_guest[$i]; } } $search_condn = "HAVING"; for ($i = 0; $i < sizeof($result_email); $i++) { $search_condn .= " meta_value like '%{$result_email[$i]}%' "; $search_condn .= "OR"; } $search_condn = substr($search_condn, 0, -2); } elseif ($num_rows_phone > 0) { // Query to bring the matching Phone No. of the Guest uers $query = "SELECT DISTINCT p1.meta_value \n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_phone'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value = 0\n AND p1.meta_value like '%{$search_on}%'"; $result_phone_guest = $wpdb->get_col($query); $rows_phone_guest = $wpdb->num_rows; $query_phone = "SELECT DISTINCT(p1.meta_value)\n FROM {$wpdb->prefix}postmeta AS p1, {$wpdb->prefix}postmeta AS p2 \n WHERE p1.post_id = p2.post_id \n AND p1.meta_key = '_billing_email'\n AND p2.meta_key = '_customer_user'\n AND p2.meta_value IN (" . implode(",", $result_user_phone) . ")"; $result_phone = $wpdb->get_col($query_phone); if ($rows_phone_guest > 0) { for ($i = 0, $j = sizeof($result_phone); $i < sizeof($result_phone_guest); $i++, $j++) { $result_phone[$j] = $result_phone_guest[$i]; } } $search_condn = "HAVING"; for ($i = 0; $i < sizeof($result_phone); $i++) { $search_condn .= " meta_value like '%{$result_phone[$i]}%' "; $search_condn .= "OR"; } $search_condn = substr($search_condn, 0, -2); } elseif ($num_rows_email1 > 0 || $num_rows_phone1 > 0) { $search_condn = " HAVING id = 0"; } elseif ($num_terms > 0) { $search_condn = " HAVING term_taxonomy_id IN ({$result_terms})"; } else { $search_condn = " HAVING id like '{$search_on}%'\n OR date like '%{$search_on}%'\n OR meta_value like '%{$search_on}%'"; } if ($_POST['SM_IS_WOO22'] == "true") { $search_on = 'wc-' . str_replace(" ", "-", $search_on); if (!empty($search_condn)) { $search_condn .= " OR order_status LIKE '%{$search_on}%'"; } else { $search_condn = " HAVING order_status LIKE '%{$search_on}%'"; } } } //get the state id if the shipping state is numeric or blank $query = "{$select_query} {$where} {$group_by} {$search_condn} {$limit_query}"; $results = $wpdb->get_results($query, 'ARRAY_A'); //To get the total count $orders_count_result = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $orders_count_result[0]['count']; if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', $sm_text_domain); } else { foreach ($results as $data) { $order_ids[] = $data['id']; } if ($_POST['SM_IS_WOO16'] == "false") { $order_id = implode(",", $order_ids); // Code for handling export functionality if (!empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWoo') { $order_id_cond = ""; $order_id_order_by = ""; } else { $order_id_cond = " AND order_items.order_id IN ({$order_id})"; $order_id_order_by = "ORDER BY FIND_IN_SET(order_items.order_id,'{$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 order_items.order_item_type,\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_item_type LIKE 'line_item'\n WHERE order_items.order_item_type IN ('line_item', 'shipping') \n {$order_id_cond}\n GROUP BY order_items.order_item_id\n {$order_id_order_by}"; $results_order_items = $wpdb->get_results($query_order_items, 'ARRAY_A'); $num_rows_order_items = $wpdb->num_rows; //code for formatting order items array if ($num_rows_order_items > 0) { $order_items = array(); $order_shipping_method = array(); foreach ($results_order_items as $results_order_item) { if (!isset($order_items[$results_order_item['order_id']])) { $order_items[$results_order_item['order_id']] = array(); } if ($results_order_item['order_item_type'] == 'shipping') { $order_shipping_method[$results_order_item['order_id']] = $results_order_item['order_prod']; } else { $order_items[$results_order_item['order_id']][] = $results_order_item; } } } //Code for export functionality if (!empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWoo') { $coupons_order_id_cond = ""; $coupons_order_id_order_by = ""; } else { $coupons_order_id_cond = " AND order_id IN ({$order_id})"; $coupons_order_id_order_by = "ORDER BY FIND_IN_SET(order_id,'{$order_id}')"; } $query_order_coupons = "SELECT order_id,\n GROUP_CONCAT(order_item_name\n ORDER BY order_item_id \n SEPARATOR ', ' ) AS coupon_used\n FROM {$wpdb->prefix}woocommerce_order_items\n WHERE order_item_type LIKE 'coupon'\n {$coupons_order_id_cond}\n GROUP BY order_id\n {$coupons_order_id_order_by}"; $results_order_coupons = $wpdb->get_results($query_order_coupons, 'ARRAY_A'); $num_rows_coupons = $wpdb->num_rows; if ($num_rows_coupons > 0) { $order_coupons = array(); foreach ($results_order_coupons as $results_order_coupon) { $order_coupons[$results_order_coupon['order_id']] = $results_order_coupon['coupon_used']; } } $query_variation_ids = "SELECT order_itemmeta.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_itemmeta.meta_key LIKE '_variation_id'\n AND order_itemmeta.meta_value > 0\n AND order_items.order_id IN ({$order_id})"; $result_variation_ids = $wpdb->get_col($query_variation_ids); if (count($result_variation_ids) > 0) { $query_variation_att = "SELECT postmeta.post_id AS post_id,\n GROUP_CONCAT(postmeta.meta_value\n ORDER BY postmeta.meta_id \n SEPARATOR ',' ) AS meta_value\n FROM {$wpdb->prefix}postmeta AS postmeta\n WHERE postmeta.meta_key LIKE 'attribute_%'\n AND postmeta.post_id IN (" . implode(",", $result_variation_ids) . ")\n GROUP BY postmeta.post_id"; // $results_variation_att = $wpdb->get_results($query_variation_att, 'ARRAY_A'); } $query_terms = "SELECT terms.slug as slug, terms.name as term_name\n FROM {$wpdb->prefix}terms AS terms\n JOIN {$wpdb->prefix}postmeta AS postmeta \n ON ( postmeta.meta_value = terms.slug \n AND postmeta.meta_key LIKE 'attribute_%' ) \n GROUP BY terms.slug"; $attributes_terms = $wpdb->get_results($query_terms, 'ARRAY_A'); $attributes = array(); foreach ($attributes_terms as $attributes_term) { $attributes[$attributes_term['slug']] = $attributes_term['term_name']; } $variation_att_all = array(); if (!empty($results_variation_att) && is_array($results_variation_att) && count($results_variation_att) > 0) { for ($i = 0; $i < sizeof($results_variation_att); $i++) { $variation_attributes = explode(", ", $results_variation_att[$i]['meta_value']); $attributes_final = array(); foreach ($variation_attributes as $variation_attribute) { $attributes_final[] = isset($attributes[$variation_attribute]) ? $attributes[$variation_attribute] : ucfirst($variation_attribute); } $results_variation_att[$i]['meta_value'] = implode(", ", $attributes_final); $variation_att_all[$results_variation_att[$i]['post_id']] = $results_variation_att[$i]['meta_value']; } } } $customer_user_ids = $reg_users = array(); foreach ($results as $data) { $meta_key = explode('###', $data['meta_key']); $meta_value = explode('###', $data['meta_value']); if (count($meta_key) == count($meta_value)) { continue; } $postmeta = array_combine($meta_key, $meta_value); if ($postmeta['_customer_user'] == 0) { continue; } $customer_user_ids[] = $postmeta['_customer_user']; } if (!empty($customer_user_ids)) { //Query to get the email id from the wp_users table for the Registered Customers $query_users = "SELECT users.ID,users.user_email,usermeta.meta_value\n FROM {$wpdb->prefix}users AS users, {$wpdb->prefix}usermeta AS usermeta\n WHERE usermeta.user_id = users.id\n AND usermeta.meta_key = 'billing_phone'\n AND users.ID IN (" . implode(',', $customer_user_ids) . ")\n GROUP BY users.ID"; $result_users = $wpdb->get_results($query_users, 'ARRAY_A'); $result_users_count = $wpdb->num_rows; if ($result_users_count > 0) { foreach ($result_users as $result_user) { $reg_users[$result_user['ID']] = array('billing_email' => $result_user['user_email'], 'billing_phone' => $result_user['meta_value']); } } } foreach ($results as $data) { $meta_key = explode('###', $data['meta_key']); $meta_value = explode('###', $data['meta_value']); if (count($meta_key) == count($meta_value)) { $postmeta = array_combine($meta_key, $meta_value); //Code to replace the email of the Registered Customers with the one from the wp_users if ($postmeta['_customer_user'] > 0 && !empty($reg_users[$postmeta['_customer_user']])) { $postmeta['_billing_email'] = $reg_users[$postmeta['_customer_user']]['billing_email']; $postmeta['_billing_phone'] = $reg_users[$postmeta['_customer_user']]['billing_phone']; // for ( $index=0;$index<sizeof($result_users);$index++ ) { // if ( $postmeta['_customer_user'] == $result_users[$index]['ID'] ){ // $postmeta['_billing_email'] = $result_users[$index]['user_email']; // $postmeta['_billing_phone'] = $result_users[$index]['meta_value']; // break; // } // } } if ($_POST['SM_IS_WOO16'] == "true") { if (is_serialized($postmeta['_order_items'])) { $order_items = unserialize(trim($postmeta['_order_items'])); foreach ((array) $order_items as $order_item) { if (isset($order_item['item_meta']) && count($order_item['item_meta']) > 0) { $variation_data = array(); foreach ($order_item['item_meta'] as $meta) { $variation_data['attribute_' . $meta['meta_name']] = $meta['meta_value']; } $variation_details = woocommerce_get_formatted_variation($variation_data, true); } $data['details'] += $order_item['qty']; $data['order_total_ex_tax'] += $order_item['line_total']; $product_id = $order_item['variation_id'] > 0 ? $order_item['variation_id'] : $order_item['id']; $sm_sku = get_post_meta($product_id, '_sku', true); if (!empty($sm_sku)) { $sku_detail = '[SKU: ' . $sm_sku . ']'; } else { $sku_detail = ''; } $product_full_name = !empty($variation_details) ? $order_item['name'] . ' (' . $variation_details . ')' : $order_item['name']; $data['products_name'] .= $product_full_name . ' ' . $sku_detail . '[' . __('Qty', $sm_text_domain) . ': ' . $order_item['qty'] . '][' . __('Price', $sm_text_domain) . ': ' . $order_item['line_total'] / $order_item['qty'] . '], '; } isset($data['details']) ? $data['details'] .= ' items' : ($data['details'] = ''); $data['products_name'] = substr($data['products_name'], 0, -2); //To remove extra comma ', ' from returned string } else { $data['details'] = 'Details'; } } else { if (!empty($order_items[$data['id']])) { foreach ($order_items[$data['id']] as $order_item) { $prod_meta_values = explode('###', $order_item['meta_value']); $prod_meta_key = explode('###', $order_item['meta_key']); if (count($prod_meta_values) != count($prod_meta_key)) { continue; } unset($order_item['meta_value']); unset($order_item['meta_key']); $sku_detail = !empty($sku_detail) ? $sku_detail : ''; $index = !empty($index) ? $index : ''; update_post_meta($index, $sku_detail, $meta_value); $prod_meta_key_values = array_combine($prod_meta_key, $prod_meta_values); // if ($data['id'] == $order_item['order_id']) { $data['details'] = !empty($data['details']) ? $data['details'] : ''; $data['order_total_ex_tax'] = !empty($data['order_total_ex_tax']) ? $data['order_total_ex_tax'] : ''; $data['details'] += $prod_meta_key_values['_qty']; $data['order_total_ex_tax'] += $prod_meta_key_values['_line_total']; $product_id = $prod_meta_key_values['_variation_id'] > 0 ? $prod_meta_key_values['_variation_id'] : $prod_meta_key_values['_product_id']; $sm_sku = get_post_meta($product_id, '_sku', true); if (!empty($sm_sku)) { $sku_detail = '[SKU: ' . $sm_sku . ']'; } else { $sku_detail = ''; } $variation_att = isset($variation_att_all[$prod_meta_key_values['_variation_id']]) && !empty($variation_att_all[$prod_meta_key_values['_variation_id']]) ? $variation_att_all[$prod_meta_key_values['_variation_id']] : ''; $product_full_name = !empty($variation_att) ? $order_item['order_prod'] . ' (' . $variation_att . ')' : $order_item['order_prod']; $data['products_name'] = !empty($data['products_name']) ? $data['products_name'] : ''; $data['products_name'] .= $product_full_name . ' ' . $sku_detail . '[' . __('Qty', $sm_text_domain) . ': ' . $prod_meta_key_values['_qty'] . '][' . __('Price', $sm_text_domain) . ': ' . $prod_meta_key_values['_line_total'] / $prod_meta_key_values['_qty'] . '], '; $data['coupons'] = isset($order_coupons[$order_item['order_id']]) ? $order_coupons[$order_item['order_id']] : ""; // } } isset($data['details']) ? $data['details'] .= ' items' : ($data['details'] = ''); $data['products_name'] = substr($data['products_name'], 0, -2); //To remove extra comma ', ' from returned string } } //Code to get the Order_Status using the $terms_name array if ($_POST['SM_IS_WOO22'] == "true") { $data['order_status'] = 'wc-' === substr($data['order_status'], 0, 3) ? substr($data['order_status'], 3) : $data['order_status']; } else { $data['order_status'] = $terms_name[$data['term_taxonomy_id']]; } $name_emailid[0] = "<font class=blue>" . $postmeta['_billing_first_name'] . "</font>"; $name_emailid[1] = "<font class=blue>" . $postmeta['_billing_last_name'] . "</font>"; $name_emailid[2] = "(" . $postmeta['_billing_email'] . ")"; //email comes at 7th position. $data['name'] = implode(' ', $name_emailid); //in front end,splitting is done with this space. $data['_shipping_address'] = $postmeta['_shipping_address_1'] . ', ' . $postmeta['_shipping_address_2']; unset($data['meta_value']); $postmeta['_shipping_method'] = isset($postmeta['_shipping_method_title']) ? $postmeta['_shipping_method_title'] : (!empty($postmeta['_shipping_method']) ? $postmeta['_shipping_method'] : ''); $postmeta['_shipping_method'] = !empty($order_shipping_method[$data['id']]) ? $order_shipping_method[$data['id']] : $postmeta['_shipping_method']; $payment_method = !empty($postmeta['_payment_method']) ? $postmeta['_payment_method'] : ''; $postmeta['_payment_method'] = isset($postmeta['_payment_method_title']) ? $postmeta['_payment_method_title'] : $payment_method; $postmeta['_shipping_state'] = isset($woocommerce->countries->states[$postmeta['_shipping_country']][$postmeta['_shipping_state']]) ? $woocommerce->countries->states[$postmeta['_shipping_country']][$postmeta['_shipping_state']] : $postmeta['_shipping_state']; $postmeta['_shipping_country'] = isset($woocommerce->countries->countries[$postmeta['_shipping_country']]) ? $woocommerce->countries->countries[$postmeta['_shipping_country']] : $postmeta['_shipping_country']; $data['display_id'] = $data['id']; //Code for Sequential Orders compatibility if ($order_formatted != "" && isset($postmeta['_order_number_formatted'])) { $data['display_id'] = $postmeta['_order_number_formatted']; } $records[] = array_merge($postmeta, $data); } } unset($meta_value); unset($meta_key); unset($postmeta); unset($results); } } if (!isset($_POST['label']) || !empty($_POST['label']) && $_POST['label'] != 'getPurchaseLogs') { $encoded['items'] = $records; $encoded['totalCount'] = $num_records; // Code for passing the column headers for export for handling custom columns if (!empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWoo' && $active_module == 'Products') { $encoded['column_header'] = $export_column_header; } unset($records); return $encoded; } }