function get_data_wpsc_38($post, $offset, $limit, $is_export = false) { global $wpdb, $post_status, $parent_sort_id, $order_by; $_POST = $post; // Fix: PHP 5.4 $regions_ids = get_regions_ids(); $country_results = $wpdb->get_results("SELECT isocode, country FROM " . WPSC_TABLE_CURRENCY_LIST, 'ARRAY_A'); $country_data = array(); foreach ($country_results as $country_result) { $country_data[$country_result['isocode']] = $country_result['country']; } //Code to handle the show variations query function variation_query_params() { global $wpdb, $post_status, $parent_sort_id, $order_by; $post_status = "('publish', 'pending', 'draft', 'inherit') AND {$wpdb->prefix}posts.ID NOT IN \r\n\t\t\t\t\t\t\t( SELECT product.ID FROM {$wpdb->prefix}posts AS product \r\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}posts AS product_variation \r\n\t\t\t\t\t\t\tON product_variation.ID = product.post_parent \r\n\t\t\t\t\t\t\tWHERE product_variation.post_status = 'trash' ) "; $parent_sort_id = " ,if({$wpdb->prefix}posts.post_parent = 0,{$wpdb->prefix}posts.id,{$wpdb->prefix}posts.post_parent - 1 + ({$wpdb->prefix}posts.id)/pow(10,char_length(cast({$wpdb->prefix}posts.id as char)))\t) as parent_sort_id"; $order_by = " ORDER BY parent_sort_id desc"; } // getting the active module // $active_module = $_POST ['active_module']; $active_module = isset($_POST['active_module']) ? $_POST['active_module'] : 'Products'; variation_query_params(); 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 $wpsc_default_image = WP_PLUGIN_URL . '/wp-e-commerce/wpsc-theme/wpsc-images/noimage.png'; if (isset($_POST['incVariation']) && $_POST['incVariation'] == 'true') { $show_variation = true; } else { // query params for non-variation products $show_variation = false; $post_status = "('publish', 'pending', 'draft')"; $parent_sort_id = ''; $order_by = " ORDER BY {$wpdb->prefix}posts.id desc"; } $query_ids = "SELECT `ID` FROM {$wpdb->prefix}posts \r\n WHERE `post_type` = 'wpsc-product' \r\n AND `post_status` = 'publish' \r\n AND `post_parent`=0 \r\n AND `ID` NOT IN ( SELECT distinct `post_parent` \r\n FROM {$wpdb->prefix}posts WHERE `post_parent`>0)"; $result_ids = $wpdb->get_col($query_ids); $num_ids = $wpdb->num_rows; if ($num_ids > 0) { for ($i = 0; $i < sizeof($result_ids); $i++) { $simple_ids[$result_ids[$i]] = 0; } } // if max-join-size issue occurs $query = "SET SQL_BIG_SELECTS=1;"; $wpdb->query($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 \r\n WHERE post_status = 'trash'\r\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 \r\n FROM {$wpdb->prefix}posts as products \r\n WHERE NOT EXISTS (SELECT * FROM {$wpdb->prefix}posts WHERE ID = products.post_parent) \r\n AND products.post_parent > 0 \r\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 {$wpdb->prefix}posts.post_parent NOT IN (" . implode(",", $results_trash) . ")"; } else { $trash_id = ""; } //Query to get the Category Ids $query_categories = "SELECT {$wpdb->prefix}posts.id as id,\r\n GROUP_CONCAT(distinct {$wpdb->prefix}term_relationships.term_taxonomy_id order by {$wpdb->prefix}term_relationships.object_id SEPARATOR '###') AS term_taxonomy_id\r\n FROM {$wpdb->prefix}posts\r\n JOIN {$wpdb->prefix}term_relationships ON ({$wpdb->prefix}posts.id = {$wpdb->prefix}term_relationships.object_id)\r\n WHERE {$wpdb->prefix}posts.post_status IN {$post_status}\r\n AND {$wpdb->prefix}posts.post_type LIKE 'wpsc-product'\r\n {$trash_id}\r\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']; } //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\r\n JOIN {$wpdb->prefix}terms AS terms ON (wt.term_id = terms.term_id)\r\n WHERE wt.taxonomy LIKE 'wpsc_product_category'"; $results = $wpdb->get_results($query_terms, 'ARRAY_A'); $rows_terms = $wpdb->num_rows; if ($rows_terms > 0) { foreach ($results as $result) { $term_taxonomy[$result['term_taxonomy_id']] = $result['name']; } } // CAST(GROUP_CONCAT(DISTINCT term_relationships.term_taxonomy_id order by term_relationships.term_taxonomy_id SEPARATOR ',') AS CHAR(1000)) AS term_taxonomy_id $post_meta_select = !empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWpsc' ? ", GROUP_CONCAT(prod_othermeta.meta_key order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_key,\r\n\t\t\t\t\tGROUP_CONCAT(prod_othermeta.meta_value order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_value" : ""; $select = "SELECT SQL_CALC_FOUND_ROWS {$wpdb->prefix}posts.id,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_title,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_title as post_title_search,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_content,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_excerpt,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_status,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_parent\r\n\t\t\t\t\t{$post_meta_select}\r\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 = ""; //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 //Advanced Search Code 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_value == 'inches') { $search_value = 'in'; } else { if ($search_value == 'pounds') { $search_value = 'pound'; } else { if ($search_value == 'ounces') { $search_value = 'ounce'; } else { if ($search_value == 'grams') { $search_value = 'gram'; } else { if ($search_value == 'kilograms') { $search_value = 'kilogram'; } } } } } $advanced_search_query[$i]['cond_postmeta_col_name'] .= $search_col; $advanced_search_query[$i]['cond_postmeta_col_value'] .= $search_value; if ($search_col != '_wpsc_price' && $search_col != '_wpsc_special_price' && $search_col != '_wpsc_sku' && $search_col != '_wpsc_stock') { $search_col_temp = $search_col; $search_value_temp = $search_value; $search_col = '_wpsc_product_metadata'; if ($search_operator == 'is' || $search_operator == 'is not') { $search_value = '%' . $search_col_temp . '";s:' . strlen($search_value_temp) . ':%"' . $search_value_temp . '";%'; } else { $search_value = '%' . $search_col_temp . '%' . $search_value_temp . '%'; } } if ($search_data_type == "number") { if ($search_col != '_wpsc_price' && $search_col != '_wpsc_special_price' && $search_col != '_wpsc_sku' && $search_col != '_wpsc_stock') { $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 . " )"; } else { $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'; $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'; $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; if ($search_col != '_wpsc_price' && $search_col != '_wpsc_special_price' && $search_col != '_wpsc_sku' && $search_col != '_wpsc_stock') { $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 . "'" . " )"; } else { $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'])) { $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'] ); $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 ) ) ); } else { $search_on = $wpdb->_real_escape( $search_on ); $search_ons = explode( ' ', $search_on ); } //Code for searching using modified post title $query_title = "SELECT ID FROM {$wpdb->prefix}posts WHERE post_title LIKE '%$search_on%' AND post_type = 'wpsc-product'"; $records_title = $wpdb->get_col ( $query_title ); $rows_title = $wpdb->num_rows; if ($rows_title > 0) { $search_title = "OR products.post_parent IN ( SELECT ID FROM {$wpdb->prefix}posts WHERE post_title LIKE '%$search_on%' AND post_type = 'wpsc-product')"; } else { $search_title = " "; } if ( is_array( $search_ons ) && ! empty( $search_ons ) ) { $term_taxonomy_ids = get_term_taxonomy_ids( '"' . implode( '","', $search_ons ) . '"' ); $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 if(post_status = 'publish','Published',post_status) LIKE '$search_on%' OR prod_othermeta_value LIKE '%$search_on%' $search_title OR"; } if ( is_array( $term_taxonomy_ids ) && !empty( $term_taxonomy_ids ) ) { foreach ( $term_taxonomy_ids as $term_taxonomy_id ) { $search_condn .= " term_taxonomy_id LIKE '%$term_taxonomy_id%' OR"; } } $search_condn = substr( $search_condn, 0, -2 ); } else { $term_taxonomy_ids = get_term_taxonomy_ids( '"' . $search_on . '"' ); $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 if(post_status = 'publish','Published',post_status) LIKE '$search_on%' OR prod_othermeta_value LIKE '%$search_on%' $search_title "; if ( is_array( $term_taxonomy_ids ) && !empty( $term_taxonomy_ids ) ) { foreach ( $term_taxonomy_ids as $term_taxonomy_id ) { $search_condn .= " OR term_taxonomy_id LIKE '%$term_taxonomy_id%'"; } } } } else { $search_condn = ''; } */ //code for the advanced Search condition handling //Code for term_relationships if (!empty($advanced_search_query)) { $advanced_search_post_ids = array(); $index_search_string = 1; foreach ($advanced_search_query as &$advanced_search_query_string) { 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 $result_terms_search = ''; $index = 0; foreach ($cond_terms_array as $cond_terms) { $query_advanced_search_taxonomy_id = "SELECT {$wpdb->prefix}term_taxonomy.term_taxonomy_id\r\n FROM {$wpdb->prefix}term_taxonomy\r\n JOIN {$wpdb->prefix}terms\r\n ON ( {$wpdb->prefix}terms.term_id = {$wpdb->prefix}term_taxonomy.term_id)\r\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; $terms_advanced_search_select .= " ,1"; $terms_advanced_search_from = "FROM {$wpdb->prefix}posts\r\n JOIN {$wpdb->prefix}term_relationships\r\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 \r\n SET flag = 0\r\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\r\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"; } $query_terms_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\r\n (" . $terms_advanced_search_select . " " . $terms_advanced_search_from . " " . $terms_advanced_search_where . " " . ")"; $result_terms_search = $wpdb->query($query_terms_search); //query when no attr cond has been applied $query_terms_search_cat_child = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\r\n ( SELECT {$wpdb->prefix}posts.id " . $terms_search_result_flag . " ,1\r\n FROM {$wpdb->prefix}posts \r\n JOIN {$wpdb->base_prefix}sm_advanced_search_temp\r\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.post_parent)\r\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"); } //Condn 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 = ''; $postmeta_advanced_search_from = ''; $postmeta_advanced_search_where = ''; foreach ($cond_postmeta_array as $cond_postmeta) { $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]) : ''; $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 \r\n SET flag = 0\r\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\r\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"; } if ($cond_postmeta_col_name1 == 'weight' || $cond_postmeta_col_name1 == 'height' || $cond_postmeta_col_name1 == 'width' || $cond_postmeta_col_name1 == 'length' || $cond_postmeta_col_name1 == 'local' || $cond_postmeta_col_name1 == 'international') { $postmeta_advanced_search_query = "SELECT DISTINCT temp.post_id " . $postmeta_search_result_flag . " ,0\r\n FROM (SELECT {$wpdb->prefix}postmeta.post_id, SUBSTRING_INDEX( SUBSTRING( {$wpdb->prefix}postmeta.meta_value, (\r\n INSTR( {$wpdb->prefix}postmeta.meta_value, '" . $cond_postmeta_col_name1 . "' ) + LENGTH('" . $cond_postmeta_col_name1 . "') ) +4 ) ,';', 1) \r\n AS " . $cond_postmeta_col_name1 . "\r\n FROM {$wpdb->prefix}postmeta " . $postmeta_advanced_search_from . "\r\n WHERE meta_key LIKE '_wpsc_product_metadata'\r\n {$postmeta_advanced_search_where}\r\n GROUP BY post_id\r\n HAVING " . $cond_postmeta_col_name1 . " " . $cond_postmeta_operator1 . " " . $cond_postmeta_col_value1 . ") AS temp"; } else { $postmeta_advanced_search_query = "SELECT DISTINCT {$wpdb->prefix}postmeta.post_id " . $postmeta_search_result_flag . " ,0\r\n FROM {$wpdb->prefix}postmeta " . $postmeta_advanced_search_from . "\r\n WHERE " . $cond_postmeta . " " . $postmeta_advanced_search_where; } $query_postmeta_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\r\n (" . $postmeta_advanced_search_query . ")"; $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'; //Query to find if there are any previous conditions $count_temp_previous_cond = $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp \r\n SET flag = 0\r\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\r\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\r\n (SELECT DISTINCT {$wpdb->prefix}posts.id " . $posts_search_result_flag . " ,0\r\n FROM {$wpdb->prefix}posts " . $posts_advanced_search_from . "\r\n WHERE " . $cond_posts . "AND {$wpdb->prefix}posts.post_type = 'wpsc-product'" . $posts_advanced_search_where . ")"; $result_posts_search = $wpdb->query($query_posts_search); $index++; } //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\r\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"; $from_export = "FROM {$wpdb->prefix}posts\r\n \t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}postmeta as prod_othermeta ON (prod_othermeta.post_id = {$wpdb->prefix}posts.id and\r\n \t\t\t\t\t\t prod_othermeta.meta_key IN ('_wpsc_price', '_wpsc_special_price', '_wpsc_sku', '_wpsc_stock', '_thumbnail_id','_wpsc_product_metadata') )\r\n LEFT JOIN {$wpdb->prefix}term_relationships AS term_relationships ON ( {$wpdb->prefix}posts.id = term_relationships.object_id )"; $where = "WHERE {$wpdb->prefix}posts.post_status IN {$post_status}\r\n\t\t\t\t\tAND {$wpdb->prefix}posts.post_type = 'wpsc-product'\r\n {$trash_id}"; $group_by = " GROUP BY {$wpdb->prefix}posts.id "; $query = !empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWpsc' ? "{$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_where $advanced_search_string $group_by $search_condn $order_by $limit_string;"; $records = $wpdb->get_results($query); $num_rows = $wpdb->num_rows; //To get the total count $recordcount_query = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $recordcount_query[0]['count']; if (!empty($advanced_search_query) && !empty($advanced_search_post_ids)) { $advanced_search_post_ids = array_unique($advanced_search_post_ids); $num_records = sizeof($advanced_search_post_ids); } 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; } $query_postmeta = "SELECT prod_othermeta.post_id as post_id,\r\n GROUP_CONCAT(prod_othermeta.meta_key order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_key,\r\n GROUP_CONCAT(prod_othermeta.meta_value order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_value\r\n FROM {$wpdb->prefix}postmeta as prod_othermeta \r\n WHERE post_id IN (" . implode(",", $post_ids) . ") AND\r\n prod_othermeta.meta_key IN ('_wpsc_price', '_wpsc_special_price', '_wpsc_sku', '_wpsc_stock', '_thumbnail_id','_wpsc_product_metadata')\r\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 = !empty($products_meta_data[$record->id]['prod_othermeta_key']) ? $products_meta_data[$record->id]['prod_othermeta_key'] : ''; $record->prod_othermeta_value = !empty($products_meta_data[$record->id]['prod_othermeta_value']) ? $products_meta_data[$record->id]['prod_othermeta_value'] : ''; } } foreach ($records as &$record) { $record->post_content = str_replace('"', '\'', $record->post_content); $record->post_excerpt = str_replace('"', '\'', $record->post_excerpt); // if ( intval($record->post_parent) == 0 ) { // $category_terms = wp_get_object_terms($record->id, 'wpsc_product_category', array( 'fields' => 'names', 'orderby' => 'name', 'order' => 'ASC' )); // $record->category = implode( ', ', $category_terms ); // To hide category name from Product's variations // } $product_type = wp_get_object_terms($record->id, 'product_type', array('fields' => 'slugs')); // Code to get the Category Name from the term_taxonomy_id if (isset($category_ids_all[$record->id])) { $category_names = ""; $category_id = explode('###', $category_ids_all[$record->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); $record->category = $category_names; } } else { $record->category = ""; } $product_type = !is_wp_error($product_type) ? !empty($product_type[0]) ? $product_type[0] : '' : ''; // Code for WP_Error and empty check $record->category = $record->post_parent > 0 && $product_type == 'simple' || $record->post_parent == 0 ? !empty($record->category) ? $record->category : '' : ''; // To hide category name from Product's variations $prod_meta_values = explode('###', $record->prod_othermeta_value); $prod_meta_key = explode('###', $record->prod_othermeta_key); if (count($prod_meta_key) != count($prod_meta_values)) { continue; } $prod_meta_key_values = array_combine($prod_meta_key, $prod_meta_values); if (intval($record->post_parent) > 0) { $title = get_post_field('post_title', $record->post_parent, 'raw'); $variation_terms = wp_get_object_terms($record->id, 'wpsc-variation', array('fields' => 'names', 'orderby' => 'name', 'order' => 'ASC')); $record->post_title = $title . ' - (' . implode(', ', $variation_terms) . ')'; } // $thumbnail = isset( $prod_meta_key_values['_thumbnail_id'] ) ? wp_get_attachment_image_src( $prod_meta_key_values['_thumbnail_id'], $image_size ) : ''; // $record->thumbnail = ( $thumbnail[0] != '' ) ? $thumbnail[0] : false; $thumbnail = wpsc_the_product_thumbnail('', '', $record->id, ''); $record->thumbnail = $thumbnail != '' ? $thumbnail : false; foreach ($prod_meta_key_values as $key => $value) { if (is_serialized($value)) { $unsez_data = unserialize($value); $unsez_data['weight'] = wpsc_convert_weight($unsez_data['weight'], "pound", $unsez_data['weight_unit']); // get the weight by converting it to repsective unit foreach ((array) $unsez_data as $meta_key => $meta_value) { if (is_array($meta_value)) { foreach ($meta_value as $sub_metakey => $sub_metavalue) { in_array($sub_metakey, $view_columns) ? $record->{$sub_metakey} = $sub_metavalue : ''; } } else { in_array($meta_key, $view_columns) ? $record->{$meta_key} = $meta_value : ''; } if ($record->post_parent == 0 && wpsc_product_has_children($record->id)) { if ($show_variation == true) { $record->_wpsc_price = $record->_wpsc_special_price = ' '; } elseif ($show_variation == false) { $parent_price = version_compare(WPSC_VERSION, '3.8.10', '>=') == 1 ? wpsc_product_variation_price_from($record->id) : wpsc_product_variation_price_available($record->id); $record->_wpsc_price = substr($parent_price, 1, strlen($parent_price)); $record->_wpsc_special_price = substr($parent_price, 1, strlen($parent_price)); } } } unset($prod_meta_key_values[$value]); } else { in_array($key, $view_columns) ? $record->{$key} = $value : ''; } } unset($record->prod_othermeta_value); unset($record->prod_meta); unset($record->prod_othermeta_key); } } } elseif ($active_module == 'Orders') { if (SMPRO == true && function_exists('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); } get_packing_slip($log_ids, $log_ids_arr); } else { if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); } if (isset($_POST['fromDate'])) { $from_date = strtotime($_POST['fromDate']); $to_date = strtotime($_POST['toDate']); if ($to_date == 0) { $to_date = strtotime('today'); } // move it forward till the end of day $to_date += 86399; // Swap the two dates if to_date is less than from_date if ($to_date < $from_date) { $temp = $to_date; $to_date = $from_date; $from_date = $temp; } $where = " WHERE wtpl.date BETWEEN '{$from_date}' AND '{$to_date}'"; } //Code to get the variation names $term_taxonomy_id_query = "SELECT term_relationships.object_id AS object_id,\r\n GROUP_CONCAT( DISTINCT terms.name ORDER BY terms.term_id SEPARATOR ',' ) AS variations\r\n FROM {$wpdb->prefix}term_relationships AS term_relationships\r\n LEFT JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy ON ( term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id )\r\n LEFT JOIN {$wpdb->prefix}terms AS terms ON ( terms.term_id = term_taxonomy.term_id )\r\n WHERE term_taxonomy.taxonomy = 'wpsc-variation'\r\n GROUP BY object_id"; $term_taxonomy_id_results = $wpdb->get_results($term_taxonomy_id_query, 'ARRAY_A'); $term_taxonomy_id_rows = $wpdb->num_rows; $term_names = array(); if ($term_taxonomy_id_rows > 0) { foreach ($term_taxonomy_id_results as $term_taxonomy_id_result) { $term_names[$term_taxonomy_id_result['object_id']] = $term_taxonomy_id_result['variations']; } } // LEFT JOIN {$wpdb->prefix}term_relationships AS term_relationships ON ( term_relationships.object_id = wtcc.prodid ) // LEFT JOIN // ( // ) AS terms ON ( terms.object_id = wtcc.prodid ) // CAST( CONCAT( if( products.post_parent > 0, SUBSTRING_INDEX( products.post_title, '(', 1 ), products.post_title ), if( products.post_parent > 0, CONCAT( if( terms.variations IS NULL, '', '(' ), // terms.variations, // if( terms.variations IS NULL, '', ')' ) ), '' ), // if( postmeta.meta_value != '',' [', ' '), // postmeta.meta_value, // if( postmeta.meta_value != '',']', ' ' ) ) // AS CHAR(1000000) ) AS product_details, $product_details = "SELECT wtcc.prodid AS product_id,\r\n postmeta.meta_value as sku,\r\n products.post_title as title,\r\n products.post_parent as post_parent,\r\n wtcc.name AS additional_product_name\r\n FROM " . WPSC_TABLE_CART_CONTENTS . " AS wtcc\r\n LEFT JOIN {$wpdb->prefix}posts AS products ON ( products.ID = wtcc.prodid )\r\n LEFT JOIN {$wpdb->prefix}postmeta AS postmeta ON ( postmeta.post_id = wtcc.prodid AND postmeta.meta_key = '_wpsc_sku' )\r\n GROUP BY product_id"; $results = $wpdb->get_results($product_details, 'ARRAY_A'); $product_details_results = array(); foreach ($results as $result) { $product_details = ''; if ($result['post_parent'] > 0) { $product_details = substr($result['title'], 0, strpos($result['title'], '(')); if (!empty($term_names[$result['product_id']])) { $product_details .= '(' . $term_names[$result['product_id']] . ')'; } } else { $product_details = $result['title']; } if (!empty($result['sku'])) { $product_details .= ' [' . $result['sku'] . ']'; } $product_details_results[$result['product_id']] = !empty($result['product_details']) ? $result['product_details'] : $result['additional_product_name']; } if (!empty($search_on)) { //Query for searching for Shipping_Country $search_condn_country_query = "SELECT DISTINCT wtcl.isocode\r\n FROM " . WPSC_TABLE_CURRENCY_LIST . " AS wtcl\r\n WHERE wtcl.country LIKE '%{$search_on}%'\r\n OR wtcl.continent LIKE '%{$search_on}%'\r\n "; $country_search_ons = $wpdb->get_col($search_condn_country_query); //Query for searching for Shipping_Region $search_condn_region_query = "SELECT DISTINCT wtrt.id\r\n FROM " . WPSC_TABLE_REGION_TAX . " AS wtrt\r\n WHERE wtrt.name LIKE '%{$search_on}%'\r\n "; $region_search_ons = $wpdb->get_col($search_condn_region_query); //Code for handling the search using user email id $email_query = "SELECT ID FROM {$wpdb->users} \r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_rows = $wpdb->num_rows; if ($email_rows > 0) { $email_query1 = "SELECT ID FROM {$wpdb->prefix}wpsc_purchase_logs \r\n WHERE user_ID IN (" . implode(",", $email_result) . ")"; $email_result1 = $wpdb->get_col($email_query1); $email_search = !empty($email_result1) ? " OR wtsfd.log_id IN ( " . implode(',', $email_result1) . " )" : ''; } //Code for handling search using shipping_county OR shipping_Region if (!empty($country_search_ons) || !empty($region_search_ons)) { $search_on_region_country .= " ("; foreach ($country_search_ons as $country_search_on) { $search_on_region_country .= "meta_values LIKE '%###{$country_search_on}###%' OR "; } for ($j = 0; $j < sizeof($region_search_ons); $j++) { $search_on_region_country .= "meta_values LIKE '%###{$region_search_ons[$j]}###%' OR "; } $search_on_region_country = trim($search_on_region_country, ' OR '); $search_on_region_country .= " )"; } else { $search_condn_checkout_form_details_query = " meta_values LIKE '%{$search_on}%' \r\n {$email_search}"; $search_on_region_country = ''; } } else { $search_on_region_country = ''; $search_condn_checkout_form_details_query = ''; } $having = !empty($search_condn_checkout_form_details_query) || !empty($search_on_region_country) ? " HAVING " . $search_condn_checkout_form_details_query . $search_on_region_country : ''; $checkout_form_details_select_query = "SELECT wtsfd.log_id AS purchase_log_id,\r\n GROUP_CONCAT( wtcf.unique_name ORDER BY wtcf.id SEPARATOR '###' ) AS meta_keys,\r\n GROUP_CONCAT( wtsfd.value ORDER BY wtsfd.form_id SEPARATOR '###' ) AS meta_values"; $checkout_form_details_from_query = " FROM " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS wtsfd\r\n LEFT JOIN " . WPSC_TABLE_CHECKOUT_FORMS . " as wtcf \r\n ON (wtsfd.form_id = wtcf.id)\r\n WHERE wtcf.active = 1 \r\n AND wtcf.unique_name IN ('billingfirstname', 'billinglastname', 'billingemail',\r\n 'shippingfirstname', 'shippinglastname', 'shippingaddress',\r\n 'shippingcity', 'shippingstate', 'shippingcountry', 'shippingpostcode','billingphone')\r\n "; $results = $wpdb->get_results($checkout_form_details_select_query . $checkout_form_details_from_query . " GROUP BY purchase_log_id" . $having, 'ARRAY_A'); $result_shipping = $results; $matched_checkout_form_details = false; if (empty($results)) { $results = $wpdb->get_results($checkout_form_details_select_query . $checkout_form_details_from_query . " GROUP BY purchase_log_id", 'ARRAY_A'); } else { $matched_checkout_form_details = true; } $checkout_form_details = array(); foreach ($results as $result) { $checkout_form_details[$result['purchase_log_id']] = array(); $checkout_form_details[$result['purchase_log_id']]['meta_keys'] = $result['meta_keys']; $checkout_form_details[$result['purchase_log_id']]['meta_values'] = $result['meta_values']; } $purchase_logs_select_query = "SELECT wtpl.id, \r\n wtpl.totalprice AS amount, \r\n wtpl.processed AS order_status, \r\n wtpl.user_ID AS customer_id, \r\n wtpl.date AS unixdate,\r\n wtpl.notes,\r\n wtpl.track_id,\r\n GROUP_CONCAT( CAST(wtcc.prodid AS CHAR) ORDER BY wtcc.id SEPARATOR ',' ) AS product_ids,\r\n CONCAT( CAST(SUM(wtcc.quantity) AS CHAR(100)), ' items') AS details"; $purchase_logs_from_query = " FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\n LEFT JOIN " . WPSC_TABLE_CART_CONTENTS . " AS wtcc ON ( wtcc.purchaseid = wtpl.id )\r\n "; if (!empty($search_on)) { $search_condn_purchase_log_ids = get_all_matched_purchase_log_ids($search_on, $checkout_form_details_from_query); $variation_search_query = "SELECT DISTINCT tr.object_id\r\n FROM {$wpdb->prefix}term_relationships AS tr\r\n LEFT JOIN {$wpdb->prefix}term_taxonomy AS tt ON ( tt.term_taxonomy_id = tr.term_taxonomy_id )\r\n LEFT JOIN {$wpdb->prefix}terms AS t ON ( t.term_id = tt.term_id )\r\n WHERE tt.taxonomy = 'wpsc-variation'\r\n AND t.name LIKE '%{$search_on}%'\r\n\r\n "; $object_ids = $wpdb->get_col($variation_search_query); $variation_search_ids = !empty($object_ids) ? " OR wtcc.prodid IN ( " . implode(',', $object_ids) . " )" : ''; $email_query = "SELECT ID FROM {$wpdb->users} \r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_search = !empty($email_result) ? " OR wtpl.user_ID IN ( " . implode(',', $email_result) . " )" : ''; $search_condn_purchase_logs = " AND ( wtpl.id LIKE '%{$search_on}%'\r\n OR totalprice LIKE '%{$search_on}%'\r\n OR notes LIKE '%{$search_on}%'\r\n OR date LIKE '%{$search_on}%'\r\n OR wtpl.track_id LIKE '%{$search_on}%'\r\n OR CASE wtpl.processed\r\n\t\t\t\t\t\t\t\t WHEN 1 THEN 'Incomplete Sale'\r\n\t\t\t\t\t\t\t\t WHEN 2 THEN 'Order Received'\r\n\t\t\t\t\t\t\t\t WHEN 3 THEN 'Accepted Payment'\r\n\t\t\t\t\t\t\t\t WHEN 4 THEN 'Job Dispatched'\r\n\t\t\t\t\t\t\t\t WHEN 5 THEN 'Closed Order'\r\n\t\t\t\t\t\t\t\t ELSE 'Payment Declined'\r\n\t\t\t\t\t\t\t END like '%{$search_on}%'\r\n OR wtcc.name LIKE '%{$search_on}%'\r\n {$variation_search_ids}\r\n {$email_search}\r\n )\r\n {$search_condn_purchase_log_ids}\r\n "; } else { $search_condn_purchase_logs = ''; } $query = $purchase_logs_select_query . $purchase_logs_from_query . $where . $search_condn_purchase_logs . " GROUP BY wtpl.id ORDER BY wtpl.id DESC {$limit_string}"; $results = $wpdb->get_results($query, 'ARRAY_A'); if (empty($results)) { $log_id = array(); for ($i = 0; $i < sizeof($result_shipping); $i++) { $log_id[$i] = $result_shipping[$i]['purchase_log_id']; } if (!empty($log_id)) { $where .= "AND wtpl.id IN(" . implode(",", $log_id) . ")"; $query = $purchase_logs_select_query . $purchase_logs_from_query . $where . " GROUP BY wtpl.id ORDER BY wtpl.id DESC {$limit_string}"; $results = $wpdb->get_results($query, 'ARRAY_A'); } } if (!$is_export) { $orders_count_result = $wpdb->get_results(substr($query, 0, strpos($query, 'LIMIT')), 'ARRAY_A'); $num_records = count($orders_count_result); } else { $num_records = count($results); } $query = "SELECT ID,user_email FROM {$wpdb->users}"; $reg_user = $wpdb->get_results($query, 'ARRAY_A'); for ($i = 0; $i < sizeof($reg_user); $i++) { $user_email[$reg_user[$i]['ID']] = $reg_user[$i]['user_email']; } //To get the total count if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', $sm_text_domain); } else { foreach ($results as $data) { if ($matched_checkout_form_details && !isset($checkout_form_details[$data['id']])) { continue; } $checkout_form_details_keys = explode('###', $checkout_form_details[$data['id']]['meta_keys']); $checkout_form_details_values = explode('###', $checkout_form_details[$data['id']]['meta_values']); if (count($checkout_form_details_keys) == count($checkout_form_details_values)) { $checkout_form_data = array_combine($checkout_form_details_keys, $checkout_form_details_values); $name_emailid[0] = "<font class=blue>" . $checkout_form_data['billingfirstname'] . "</font>"; $name_emailid[1] = "<font class=blue>" . $checkout_form_data['billinglastname'] . "</font>"; $name_emailid[2] = "(" . $checkout_form_data['billingemail'] . ")"; //email comes at 7th position. $data['name'] = implode(' ', $name_emailid); //in front end,splitting is done with this space. $data['date'] = gmdate('Y-m-d H:i:s', $data['unixdate']); //Code to display the order date in GMT format if ($data['customer_id'] > 0) { $data['reg_email'] = $user_email[$data['customer_id']]; } else { $data['reg_email'] = ""; } $prod_ids = explode(',', $data['product_ids']); $products_name = ''; foreach ($prod_ids as $prod_id) { $products_name .= $product_details_results[$prod_id] . ', '; } $data['products_name'] = trim($products_name, ', '); if (!empty($checkout_form_data['shippingstate'])) { $ship_state = $checkout_form_data['shippingstate']; $checkout_form_data['shippingstate'] = $regions_ids[$ship_state] != '' ? $regions_ids[$ship_state] : $ship_state; } if (!empty($checkout_form_data['shippingcountry'])) { $ship_country = $checkout_form_data['shippingcountry']; $checkout_form_data['shippingcountry'] = $country_data[$ship_country] != '' ? $country_data[$ship_country] : $ship_country; } $records[] = !empty($checkout_form_data) ? array_merge($checkout_form_data, $data) : $data; } unset($data); unset($checkout_form_details_keys); unset($checkout_form_details_values); unset($checkout_form_data); } } } } else { //BOF Customer's module if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); } else { $search_on = ""; } $email_form_id = $wpdb->get_var("SELECT id FROM " . WPSC_TABLE_CHECKOUT_FORMS . " WHERE unique_name = 'billingemail'"); $query_max_users_ids = "SELECT GROUP_CONCAT(wtpl.id ORDER BY wtpl.date DESC SEPARATOR ',' ) AS last_order_id,\r\n GROUP_CONCAT(wtpl.totalprice ORDER BY wtpl.date DESC SEPARATOR ',' ) AS _order_total,\r\n DATE_FORMAT( MAX(FROM_UNIXTIME( wtpl.date )),'%b %e %Y' ) AS Last_Order,\r\n COUNT(wtpl.id) AS count_orders,\r\n SUM(wtpl.totalprice) AS total_orders\r\n \r\n FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\n LEFT JOIN " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS customer_email ON ( customer_email.log_id = wtpl.id AND customer_email.form_id = {$email_form_id} )\r\n WHERE wtpl.user_ID > 0\r\n Group by wtpl.user_ID"; $result_max_users_ids = $wpdb->get_results($query_max_users_ids, 'ARRAY_A'); $query_max_guest_ids = "SELECT GROUP_CONCAT(wtpl.id ORDER BY wtpl.date DESC SEPARATOR ',' ) AS last_order_id,\r\n GROUP_CONCAT(wtpl.totalprice ORDER BY wtpl.date DESC SEPARATOR ',' ) AS _order_total,\r\n DATE_FORMAT( MAX(FROM_UNIXTIME( wtpl.date )),'%b %e %Y' ) AS Last_Order,\r\n COUNT(wtpl.id) AS count_orders,\r\n SUM(wtpl.totalprice) AS total_orders\r\n \r\n FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\n LEFT JOIN " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS customer_email ON ( customer_email.log_id = wtpl.id AND customer_email.form_id = {$email_form_id} )\r\n WHERE wtpl.user_ID = 0\r\n GROUP BY customer_email.value\r\n ORDER BY Last_Order DESC"; $result_max_guest_ids = $wpdb->get_results($query_max_guest_ids, 'ARRAY_A'); for ($i = 0; $i < sizeof($result_max_guest_ids); $i++) { $temp_id = !empty($result_max_guest_ids[$i]['last_order_id']) ? explode(",", $result_max_guest_ids[$i]['last_order_id']) : ''; $max_id[$i] = !empty($temp_id[0]) ? $temp_id[0] : 0; $count_orders[$max_id[$i]] = !empty($result_max_guest_ids[$i]['count_orders']) ? $result_max_guest_ids[$i]['count_orders'] : ''; $total_orders[$max_id[$i]] = !empty($result_max_guest_ids[$i]['total_orders']) ? $result_max_guest_ids[$i]['total_orders'] : ''; $order_date[$max_id[$i]] = !empty($result_max_guest_ids[$i]['Last_Order']) ? $result_max_guest_ids[$i]['Last_Order'] : ''; $temp_tot = !empty($result_max_guest_ids[$i]['_order_total']) ? explode(",", $result_max_guest_ids[$i]['_order_total']) : ''; $last_order_total[$max_id[$i]] = !empty($temp_tot[0]) ? $temp_tot[0] : ''; } $j = !empty($max_id) ? sizeof($max_id) : 0; for ($i = 0; $i < sizeof($result_max_users_ids); $i++, $j++) { $temp_id = !empty($result_max_users_ids[$i]['last_order_id']) ? explode(",", $result_max_users_ids[$i]['last_order_id']) : ''; $max_id[$j] = !empty($temp_id) ? $temp_id[0] : 0; $count_orders[$max_id[$j]] = !empty($result_max_users_ids[$i]['count_orders']) ? $result_max_users_ids[$i]['count_orders'] : ''; $total_orders[$max_id[$j]] = !empty($result_max_users_ids[$i]['total_orders']) ? $result_max_users_ids[$i]['total_orders'] : ''; $order_date[$max_id[$j]] = !empty($result_max_users_ids[$i]['Last_Order']) ? $result_max_users_ids[$i]['Last_Order'] : ''; $temp_tot = !empty($result_max_users_ids[$i]['_order_total']) ? explode(",", $result_max_users_ids[$i]['_order_total']) : ''; $last_order_total[$max_id[$j]] = !empty($temp_id) ? $temp_tot[0] : ''; } $total_search = ""; if (!empty($search_on)) { $searched_region = $wpdb->get_col("SELECT code FROM " . WPSC_TABLE_REGION_TAX . " WHERE name LIKE '%{$search_on}%'"); $searched_country = $wpdb->get_col("SELECT isocode FROM " . WPSC_TABLE_CURRENCY_LIST . " WHERE country LIKE '%{$search_on}%' OR continent LIKE '%{$search_on}%'"); $found_country_region = array_merge($searched_region, $searched_country); $found_country_region_having = ''; foreach ($found_country_region as $country_region) { $found_country_region_having .= " OR meta_values LIKE '%{$country_region}%'"; } $email_query = "SELECT ID FROM {$wpdb->users} \r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_rows = $wpdb->num_rows; //Query to get the user ids of the rows whose content matches the search text $user_detail_query = "SELECT DISTINCT user_id FROM {$wpdb->usermeta} \r\n WHERE meta_key IN ('first_name','last_name','wpshpcrt_usr_profile') \r\n AND meta_value LIKE '%{$search_on}%'"; $user_detail_result = $wpdb->get_col($user_detail_query); $user_detail_rows = $wpdb->num_rows; //Code to merge all the user ids into a single array if ($user_detail_rows > 0) { for ($i = 0, $j = sizeof($email_result); $i < sizeof($user_detail_result); $i++, $j++) { $email_result[$j] = $user_detail_result[$i]; } } if ($email_rows > 0 || $user_detail_rows > 0) { $email_query1 = "SELECT ID FROM {$wpdb->prefix}wpsc_purchase_logs \r\n WHERE user_ID IN (" . implode(",", $email_result) . ")"; $email_result1 = $wpdb->get_col($email_query1); $email_search = !empty($email_result1) ? " OR wtsfd.log_id IN ( " . implode(',', $email_result1) . " )" : ''; } } $where_log_id = !empty($max_id) ? 'WHERE log_id IN (' . implode(",", $max_id) . ')' : ''; $max_id_imploded = !empty($max_id) ? implode(",", $max_id) : ''; $orderby_log_id = !empty($max_id) ? "ORDER BY FIND_IN_SET(log_id,'{$max_id_imploded}')" : 'ORDER BY log_id'; $customer_details_query_select = "SELECT wtsfd.log_id AS log_id,\r\n GROUP_CONCAT( wtcf.unique_name ORDER BY wtcf.id SEPARATOR '###' ) AS meta_keys,\r\n GROUP_CONCAT( wtsfd.value ORDER BY wtsfd.form_id SEPARATOR '###' ) AS meta_values\r\n\r\n FROM " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS wtsfd\r\n JOIN " . WPSC_TABLE_CHECKOUT_FORMS . " AS wtcf ON ( wtcf.id = wtsfd.form_id AND wtcf.active = 1 AND wtcf.unique_name IN ('billingfirstname','billinglastname','billingaddress',\r\n 'billingcity','billingstate','billingcountry','billingpostcode',\r\n 'billingemail','billingphone') )\r\n {$where_log_id}\r\n GROUP BY log_id"; if (!empty($search_on)) { $customer_details_query_having = " HAVING meta_values LIKE '%{$search_on}%'\r\n {$found_country_region_having}\r\n {$email_search}\r\n {$total_search} \r\n "; } else { $customer_details_query_having = ''; } $order_by = " {$orderby_log_id} {$limit_string}"; $full_customer_details_query = $customer_details_query_select . $customer_details_query_having . $order_by; $customer_details_results = $wpdb->get_results($full_customer_details_query, 'ARRAY_A'); if (is_null($customer_details_results)) { $full_customer_details_query = $customer_details_query_select . $order_by; $customer_details_results = $wpdb->get_results($full_customer_details_query, 'ARRAY_A'); } if (!$is_export) { $customers_count_result = $wpdb->get_results(substr($full_customer_details_query, 0, strpos($full_customer_details_query, 'LIMIT')), 'ARRAY_A'); $num_records = count($customers_count_result); } else { $num_records = count($customer_details_results); } //Code to get all the users along with their id and email in an array $query = "SELECT users.ID,users.user_email, GROUP_CONCAT(usermeta.meta_value \r\n ORDER BY usermeta.umeta_id SEPARATOR '###' ) AS name\r\n FROM {$wpdb->users} AS users\r\n JOIN {$wpdb->usermeta} AS usermeta ON usermeta.user_id = users.id\r\n WHERE usermeta.meta_key IN ('first_name','last_name','wpshpcrt_usr_profile')\r\n GROUP BY users.id DESC"; $reg_user = $wpdb->get_results($query, 'ARRAY_A'); for ($i = 0; $i < sizeof($reg_user); $i++) { $user_email[$reg_user[$i]['ID']] = $reg_user[$i]['user_email']; $name = explode("###", $reg_user[$i]['name']); $user_fname[$reg_user[$i]['ID']] = $name[0]; $user_lname[$reg_user[$i]['ID']] = $name[1]; if (!empty($name[2])) { $unserialized_detail = unserialize($name[2]); $user_add[$reg_user[$i]['ID']] = $unserialized_detail[4]; $user_city[$reg_user[$i]['ID']] = $unserialized_detail[5]; $user_region[$reg_user[$i]['ID']] = !empty($unserialized_detail[6]) ? $unserialized_detail[6] : ''; $user_country[$reg_user[$i]['ID']] = $unserialized_detail[7][0]; $user_pcode[$reg_user[$i]['ID']] = $unserialized_detail[8]; $user_phone[$reg_user[$i]['ID']] = $unserialized_detail[18]; } } $country_result = $wpdb->get_results("SELECT isocode,country FROM " . WPSC_TABLE_CURRENCY_LIST, 'ARRAY_A'); $country_rows = $wpdb->num_rows; if ($country_rows > 0) { for ($i = 0; $i < sizeof($country_result); $i++) { $country[$country_result[$i]['isocode']] = $country_result[$i]['country']; } } if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', $sm_text_domain); } else { foreach ($customer_details_results as $result) { $meta_keys = explode('###', $result['meta_keys']); $meta_values = explode('###', $result['meta_values']); if (count($meta_keys) == count($meta_values)) { $customer_detail_data[$result['log_id']] = array_combine($meta_keys, $meta_values); } $result['last_order_id'] = $result['log_id']; $result['Last_Order'] = $order_date[$result['log_id']]; $result['_order_total'] = $last_order_total[$result['log_id']]; $result['count_orders'] = $count_orders[$result['log_id']]; $result['total_orders'] = $total_orders[$result['log_id']]; if (empty($customer_detail_data[$result['last_order_id']])) { $num_records--; continue; } $billing_user_details = $customer_detail_data[$result['last_order_id']]; $billing_user_details['billingstate'] = !empty($regions_ids[$billing_user_details['billingstate']]) ? $regions_ids[$billing_user_details['billingstate']] : $billing_user_details['billingstate']; $billing_user_details['billingcountry'] = !empty($country_data[$billing_user_details['billingcountry']]) ? $country_data[$billing_user_details['billingcountry']] : $billing_user_details['billingcountry']; if (SMPRO == false) { $result['Last_Order'] = 'Pro only'; $result['_order_total'] = 'Pro only'; $result['count_orders'] = 'Pro only'; $result['total_orders'] = 'Pro only'; } //Code to get the email for reg users from wp_users table if (!empty($result['id']) && $result['id'] > 0) { $result['email'] = $user_email[$result['id']]; $billing_user_details['billingemail'] = $user_email[$result['id']]; if (!empty($user_fname[$result['id']])) { $billing_user_details['billingfirstname'] = $user_fname[$result['id']]; } if (!empty($user_lname[$result['id']])) { $billing_user_details['billinglastname'] = $user_lname[$result['id']]; } $billing_user_details['billingaddress'] = $user_add[$result['id']]; $billing_user_details['billingcity'] = $user_city[$result['id']]; $billing_user_details['billingstate'] = !empty($regions_ids[$user_region[$result['id']]]) ? $regions_ids[$user_region[$result['id']]] : $user_region[$result['id']]; $billing_user_details['billingcountry'] = $country[$user_country[$result['id']]]; $billing_user_details['billingpostcode'] = $user_pcode[$result['id']]; $billing_user_details['billingphone'] = $user_phone[$result['id']]; } //NOTE: storing old email id in an extra column in record so useful to indentify record with emailid during updates. $result['Old_Email_Id'] = $billing_user_details['billingemail']; $records[] = !empty($billing_user_details) ? array_merge($billing_user_details, $result) : $result; unset($result); unset($meta_keys); unset($meta_values); unset($billing_user_details); } } } if (!isset($_POST['label']) || !empty($_POST['label']) && $_POST['label'] != 'getPurchaseLogs') { $encoded['items'] = !empty($records) ? $records : ''; $encoded['totalCount'] = $num_records; unset($records); return $encoded; } }
function get_data_wpsc_38($post, $offset, $limit, $is_export = false) { global $wpdb, $post_status, $parent_sort_id, $order_by; $_POST = $post; // Fix: PHP 5.4 $regions_ids = get_regions_ids(); $country_results = $wpdb->get_results("SELECT isocode, country FROM " . WPSC_TABLE_CURRENCY_LIST, 'ARRAY_A'); $country_data = array(); foreach ($country_results as $country_result) { $country_data[$country_result['isocode']] = $country_result['country']; } //Code to handle the show variations query function variation_query_params() { global $wpdb, $post_status, $parent_sort_id, $order_by; $post_status = "('publish', 'draft','inherit') AND products.ID NOT IN \r\r\n\t\t\t\t\t\t\t( SELECT product.ID FROM {$wpdb->prefix}posts AS product \r\r\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}posts AS product_variation \r\r\n\t\t\t\t\t\t\tON product_variation.ID = product.post_parent \r\r\n\t\t\t\t\t\t\tWHERE product_variation.post_status = 'trash' ) "; $parent_sort_id = " ,if(products.post_parent = 0,products.id,products.post_parent - 1 + (products.id)/pow(10,char_length(cast(products.id as char)))\t) as parent_sort_id"; $order_by = " ORDER BY parent_sort_id desc"; } // getting the active module // $active_module = $_POST ['active_module']; $active_module = isset($_POST['active_module']) ? $_POST['active_module'] : 'Products'; variation_query_params(); 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 $wpsc_default_image = WP_PLUGIN_URL . '/wp-e-commerce/wpsc-theme/wpsc-images/noimage.png'; if (isset($_POST['incVariation']) && $_POST['incVariation'] == 'true') { $show_variation = true; } else { // query params for non-variation products $show_variation = false; $post_status = "('publish', 'draft')"; $parent_sort_id = ''; $order_by = " ORDER BY products.id desc"; } $query_ids = "SELECT `ID` FROM {$wpdb->prefix}posts \r\r\n WHERE `post_type` = 'wpsc-product' \r\r\n AND `post_status` = 'publish' \r\r\n AND `post_parent`=0 \r\r\n AND `ID` NOT IN ( SELECT distinct `post_parent` \r\r\n FROM {$wpdb->prefix}posts WHERE `post_parent`>0)"; $result_ids = $wpdb->get_col($query_ids); $num_ids = $wpdb->num_rows; if ($num_ids > 0) { for ($i = 0; $i < sizeof($result_ids); $i++) { $simple_ids[$result_ids[$i]] = 0; } } // if max-join-size issue occurs $query = "SET SQL_BIG_SELECTS=1;"; $wpdb->query($query); $select = "SELECT SQL_CALC_FOUND_ROWS products.id,\r\r\n\t\t\t\t\tproducts.post_title,\r\r\n\t\t\t\t\tproducts.post_title as post_title_search,\r\r\n\t\t\t\t\tproducts.post_content,\r\r\n\t\t\t\t\tproducts.post_excerpt,\r\r\n\t\t\t\t\tproducts.post_status,\r\r\n\t\t\t\t\tproducts.post_parent,\r\r\n CAST(GROUP_CONCAT(DISTINCT term_relationships.term_taxonomy_id order by term_relationships.term_taxonomy_id SEPARATOR ',') AS CHAR(1000)) AS term_taxonomy_id,\r\r\n\t\t\t\t\tGROUP_CONCAT(prod_othermeta.meta_key order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_key,\r\r\n\t\t\t\t\tGROUP_CONCAT(prod_othermeta.meta_value order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_value\r\r\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 if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = trim($_POST['searchText']); $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))); } else { $search_on = $wpdb->_real_escape($search_on); $search_ons = explode(' ', $search_on); } //Code for searching using modified post title $query_title = "SELECT ID FROM {$wpdb->prefix}posts\r\r\n WHERE post_title LIKE '%{$search_on}%'\r\r\n AND post_type = 'wpsc-product'"; $records_title = $wpdb->get_col($query_title); $rows_title = $wpdb->num_rows; if ($rows_title > 0) { $search_title = "OR products.post_parent IN (\r\r\n SELECT ID FROM {$wpdb->prefix}posts\r\r\n WHERE post_title LIKE '%{$search_on}%'\r\r\n AND post_type = 'wpsc-product')"; } else { $search_title = " "; } if (is_array($search_ons) && !empty($search_ons)) { $term_taxonomy_ids = get_term_taxonomy_ids('"' . implode('","', $search_ons) . '"'); $search_condn = " HAVING "; foreach ($search_ons as $search_on) { $search_condn .= " concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR post_content LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR post_excerpt LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR if(post_status = 'publish','Published',post_status) LIKE '{$search_on}%'\r\r\n\t\t\t\t\t\t\t\t\t OR prod_othermeta_value LIKE '%{$search_on}%'\r\r\n {$search_title}\r\r\n\t\t\t\t\t\t\t\t\t OR"; } if (is_array($term_taxonomy_ids) && !empty($term_taxonomy_ids)) { foreach ($term_taxonomy_ids as $term_taxonomy_id) { $search_condn .= " term_taxonomy_id LIKE '%{$term_taxonomy_id}%' OR"; } } $search_condn = substr($search_condn, 0, -2); } else { $term_taxonomy_ids = get_term_taxonomy_ids('"' . $search_on . '"'); $search_condn = " HAVING concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR post_content LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR post_excerpt LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR if(post_status = 'publish','Published',post_status) LIKE '{$search_on}%'\r\r\n\t\t\t\t\t\t\t\t\t OR prod_othermeta_value LIKE '%{$search_on}%'\r\r\n {$search_title}\r\r\n\t\t\t\t\t\t\t\t\t "; if (is_array($term_taxonomy_ids) && !empty($term_taxonomy_ids)) { foreach ($term_taxonomy_ids as $term_taxonomy_id) { $search_condn .= " OR term_taxonomy_id LIKE '%{$term_taxonomy_id}%'"; } } } } else { $search_condn = ''; } $from_where = "FROM {$wpdb->prefix}posts as products\r\r\n\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}postmeta as prod_othermeta ON (prod_othermeta.post_id = products.id and\r\r\n\t\t\t\t\t\tprod_othermeta.meta_key IN ('_wpsc_price', '_wpsc_special_price', '_wpsc_sku', '_wpsc_stock', '_thumbnail_id','_wpsc_product_metadata') )\r\r\n \r\r\n LEFT JOIN {$wpdb->prefix}term_relationships AS term_relationships ON ( products.id = term_relationships.object_id )\r\r\n\r\r\n\t\t\t\t\t\tWHERE products.post_status IN {$post_status}\r\r\n\t\t\t\t\t\tAND products.post_type = 'wpsc-product'"; $group_by = " GROUP BY products.id "; $query = "{$select} {$from_where} {$group_by} {$search_condn} {$order_by} {$limit_string};"; $records = $wpdb->get_results($query); $num_rows = $wpdb->num_rows; //To get the total count $recordcount_query = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $recordcount_query[0]['count']; if ($num_rows <= 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { foreach ($records as &$record) { $record->post_content = str_replace('"', '\'', $record->post_content); $record->post_excerpt = str_replace('"', '\'', $record->post_excerpt); if (intval($record->post_parent) == 0) { $category_terms = wp_get_object_terms($record->id, 'wpsc_product_category', array('fields' => 'names', 'orderby' => 'name', 'order' => 'ASC')); $record->category = implode(', ', $category_terms); // To hide category name from Product's variations } $prod_meta_values = explode('###', $record->prod_othermeta_value); $prod_meta_key = explode('###', $record->prod_othermeta_key); if (count($prod_meta_key) != count($prod_meta_values)) { continue; } $prod_meta_key_values = array_combine($prod_meta_key, $prod_meta_values); if (intval($record->post_parent) > 0) { $title = get_post_field('post_title', $record->post_parent, 'raw'); $variation_terms = wp_get_object_terms($record->id, 'wpsc-variation', array('fields' => 'names', 'orderby' => 'name', 'order' => 'ASC')); $record->post_title = $title . ' - (' . implode(', ', $variation_terms) . ')'; } // $thumbnail = isset( $prod_meta_key_values['_thumbnail_id'] ) ? wp_get_attachment_image_src( $prod_meta_key_values['_thumbnail_id'], $image_size ) : ''; // $record->thumbnail = ( $thumbnail[0] != '' ) ? $thumbnail[0] : false; $thumbnail = wpsc_the_product_thumbnail('', '', $record->id, ''); $record->thumbnail = $thumbnail != '' ? $thumbnail : false; foreach ($prod_meta_key_values as $key => $value) { if (is_serialized($value)) { $unsez_data = unserialize($value); $unsez_data['weight'] = wpsc_convert_weight($unsez_data['weight'], "pound", $unsez_data['weight_unit']); // get the weight by converting it to repsective unit foreach ((array) $unsez_data as $meta_key => $meta_value) { if (is_array($meta_value)) { foreach ($meta_value as $sub_metakey => $sub_metavalue) { in_array($sub_metakey, $view_columns) ? $record->{$sub_metakey} = $sub_metavalue : ''; } } else { in_array($meta_key, $view_columns) ? $record->{$meta_key} = $meta_value : ''; } if ($record->post_parent == 0 && wpsc_product_has_children($record->id)) { if ($show_variation == true) { $record->_wpsc_price = $record->_wpsc_special_price = ' '; } elseif ($show_variation == false) { $parent_price = wpsc_product_variation_price_available($record->id); $record->_wpsc_price = substr($parent_price, 1, strlen($parent_price)); $record->_wpsc_special_price = substr($parent_price, 1, strlen($parent_price)); } } } unset($prod_meta_key_values[$value]); } else { in_array($key, $view_columns) ? $record->{$key} = $value : ''; } } unset($record->prod_othermeta_value); unset($record->prod_meta); unset($record->prod_othermeta_key); } } } elseif ($active_module == 'Orders') { if (SMPRO == true && function_exists('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); } get_packing_slip($log_ids, $log_ids_arr); } else { if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); } if (isset($_POST['fromDate'])) { $from_date = strtotime($_POST['fromDate']); $to_date = strtotime($_POST['toDate']); if ($to_date == 0) { $to_date = strtotime('today'); } // move it forward till the end of day $to_date += 86399; // Swap the two dates if to_date is less than from_date if ($to_date < $from_date) { $temp = $to_date; $to_date = $from_date; $from_date = $temp; } $where = " WHERE wtpl.date BETWEEN '{$from_date}' AND '{$to_date}'"; } $product_details = "SELECT wtcc.prodid AS product_id,\r\r\n CAST( CONCAT( if( products.post_parent > 0, SUBSTRING_INDEX( products.post_title, '(', 1 ), products.post_title ), if( products.post_parent > 0, CONCAT( if( terms.variations IS NULL, '', '(' ), \r\r\n terms.variations, \r\r\n if( terms.variations IS NULL, '', ')' ) ), '' ),\r\r\n if( postmeta.meta_value != '',' [', ' '),\r\r\n postmeta.meta_value,\r\r\n if( postmeta.meta_value != '',']', ' ' ) )\r\r\n AS CHAR(1000000) ) AS product_details,\r\r\n wtcc.name AS additional_product_name\r\r\n FROM " . WPSC_TABLE_CART_CONTENTS . " AS wtcc\r\r\n LEFT JOIN {$wpdb->prefix}posts AS products ON ( products.ID = wtcc.prodid )\r\r\n LEFT JOIN {$wpdb->prefix}postmeta AS postmeta ON ( postmeta.post_id = wtcc.prodid AND postmeta.meta_key = '_wpsc_sku' )\r\r\n LEFT JOIN {$wpdb->prefix}term_relationships AS term_relationships ON ( term_relationships.object_id = wtcc.prodid )\r\r\n LEFT JOIN \r\r\n (SELECT term_relationships.object_id AS object_id,\r\r\n GROUP_CONCAT( DISTINCT terms.name ORDER BY terms.term_id SEPARATOR ',' ) AS variations\r\r\n FROM {$wpdb->prefix}term_relationships AS term_relationships\r\r\n LEFT JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy ON ( term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id )\r\r\n LEFT JOIN {$wpdb->prefix}terms AS terms ON ( terms.term_id = term_taxonomy.term_id )\r\r\n WHERE term_taxonomy.taxonomy = 'wpsc-variation'\r\r\n GROUP BY object_id\r\r\n ) AS terms ON ( terms.object_id = wtcc.prodid )\r\r\n\r\r\n GROUP BY product_id\r\r\n "; $results = $wpdb->get_results($product_details, 'ARRAY_A'); $product_details_results = array(); foreach ($results as $result) { $product_details_results[$result['product_id']] = !empty($result['product_details']) ? $result['product_details'] : $result['additional_product_name']; } if (!empty($search_on)) { //Query for searching for Shipping_Country $search_condn_country_query = "SELECT DISTINCT wtcl.isocode\r\r\n FROM " . WPSC_TABLE_CURRENCY_LIST . " AS wtcl\r\r\n WHERE wtcl.country LIKE '%{$search_on}%'\r\r\n OR wtcl.continent LIKE '%{$search_on}%'\r\r\n "; $country_search_ons = $wpdb->get_col($search_condn_country_query); //Query for searching for Shipping_Region $search_condn_region_query = "SELECT DISTINCT wtrt.id\r\r\n FROM " . WPSC_TABLE_REGION_TAX . " AS wtrt\r\r\n WHERE wtrt.name LIKE '%{$search_on}%'\r\r\n "; $region_search_ons = $wpdb->get_col($search_condn_region_query); //Code for handling the search using user email id $email_query = "SELECT ID FROM {$wpdb->users} \r\r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_rows = $wpdb->num_rows; if ($email_rows > 0) { $email_query1 = "SELECT ID FROM {$wpdb->prefix}wpsc_purchase_logs \r\r\n WHERE user_ID IN (" . implode(",", $email_result) . ")"; $email_result1 = $wpdb->get_col($email_query1); $email_search = !empty($email_result1) ? " OR wtsfd.log_id IN ( " . implode(',', $email_result1) . " )" : ''; } //Code for handling search using shipping_county OR shipping_Region if (!empty($country_search_ons) || !empty($region_search_ons)) { $search_on_region_country .= " ("; foreach ($country_search_ons as $country_search_on) { $search_on_region_country .= "meta_values LIKE '%###{$country_search_on}###%' OR "; } for ($j = 0; $j < sizeof($region_search_ons); $j++) { $search_on_region_country .= "meta_values LIKE '%###{$region_search_ons[$j]}###%' OR "; } $search_on_region_country = trim($search_on_region_country, ' OR '); $search_on_region_country .= " )"; } else { $search_condn_checkout_form_details_query = " meta_values LIKE '%{$search_on}%' \r\r\n {$email_search}"; $search_on_region_country = ''; } } else { $search_on_region_country = ''; $search_condn_checkout_form_details_query = ''; } $having = !empty($search_condn_checkout_form_details_query) || !empty($search_on_region_country) ? " HAVING " . $search_condn_checkout_form_details_query . $search_on_region_country : ''; $checkout_form_details_select_query = "SELECT wtsfd.log_id AS purchase_log_id,\r\r\n GROUP_CONCAT( wtcf.unique_name ORDER BY wtcf.id SEPARATOR '###' ) AS meta_keys,\r\r\n GROUP_CONCAT( wtsfd.value ORDER BY wtsfd.form_id SEPARATOR '###' ) AS meta_values"; $checkout_form_details_from_query = " FROM " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS wtsfd\r\r\n LEFT JOIN " . WPSC_TABLE_CHECKOUT_FORMS . " as wtcf \r\r\n ON (wtsfd.form_id = wtcf.id)\r\r\n WHERE wtcf.active = 1 \r\r\n AND wtcf.unique_name IN ('billingfirstname', 'billinglastname', 'billingemail',\r\r\n 'shippingfirstname', 'shippinglastname', 'shippingaddress',\r\r\n 'shippingcity', 'shippingstate', 'shippingcountry', 'shippingpostcode','billingphone')\r\r\n "; $results = $wpdb->get_results($checkout_form_details_select_query . $checkout_form_details_from_query . " GROUP BY purchase_log_id" . $having, 'ARRAY_A'); $result_shipping = $results; $matched_checkout_form_details = false; if (empty($results)) { $results = $wpdb->get_results($checkout_form_details_select_query . $checkout_form_details_from_query . " GROUP BY purchase_log_id", 'ARRAY_A'); } else { $matched_checkout_form_details = true; } $checkout_form_details = array(); foreach ($results as $result) { $checkout_form_details[$result['purchase_log_id']] = array(); $checkout_form_details[$result['purchase_log_id']]['meta_keys'] = $result['meta_keys']; $checkout_form_details[$result['purchase_log_id']]['meta_values'] = $result['meta_values']; } $purchase_logs_select_query = "SELECT wtpl.id, \r\r\n wtpl.totalprice AS amount, \r\r\n wtpl.processed AS order_status, \r\r\n wtpl.user_ID AS customer_id, \r\r\n wtpl.date AS unixdate,\r\r\n wtpl.notes,\r\r\n wtpl.track_id,\r\r\n GROUP_CONCAT( CAST(wtcc.prodid AS CHAR) ORDER BY wtcc.id SEPARATOR ',' ) AS product_ids,\r\r\n CONCAT( CAST(SUM(wtcc.quantity) AS CHAR(100)), ' items') AS details"; $purchase_logs_from_query = " FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\r\n LEFT JOIN " . WPSC_TABLE_CART_CONTENTS . " AS wtcc ON ( wtcc.purchaseid = wtpl.id )\r\r\n "; if (!empty($search_on)) { $search_condn_purchase_log_ids = get_all_matched_purchase_log_ids($search_on, $checkout_form_details_from_query); $variation_search_query = "SELECT DISTINCT tr.object_id\r\r\n FROM {$wpdb->prefix}term_relationships AS tr\r\r\n LEFT JOIN {$wpdb->prefix}term_taxonomy AS tt ON ( tt.term_taxonomy_id = tr.term_taxonomy_id )\r\r\n LEFT JOIN {$wpdb->prefix}terms AS t ON ( t.term_id = tt.term_id )\r\r\n WHERE tt.taxonomy = 'wpsc-variation'\r\r\n AND t.name LIKE '%{$search_on}%'\r\r\n\r\r\n "; $object_ids = $wpdb->get_col($variation_search_query); $variation_search_ids = !empty($object_ids) ? " OR wtcc.prodid IN ( " . implode(',', $object_ids) . " )" : ''; $email_query = "SELECT ID FROM {$wpdb->users} \r\r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_search = !empty($email_result) ? " OR wtpl.user_ID IN ( " . implode(',', $email_result) . " )" : ''; $search_condn_purchase_logs = " AND ( wtpl.id LIKE '%{$search_on}%'\r\r\n OR totalprice LIKE '%{$search_on}%'\r\r\n OR notes LIKE '%{$search_on}%'\r\r\n OR date LIKE '%{$search_on}%'\r\r\n OR wtpl.track_id LIKE '%{$search_on}%'\r\r\n OR CASE wtpl.processed\r\r\n\t\t\t\t\t\t\t\t WHEN 1 THEN 'Incomplete Sale'\r\r\n\t\t\t\t\t\t\t\t WHEN 2 THEN 'Order Received'\r\r\n\t\t\t\t\t\t\t\t WHEN 3 THEN 'Accepted Payment'\r\r\n\t\t\t\t\t\t\t\t WHEN 4 THEN 'Job Dispatched'\r\r\n\t\t\t\t\t\t\t\t WHEN 5 THEN 'Closed Order'\r\r\n\t\t\t\t\t\t\t\t ELSE 'Payment Declined'\r\r\n\t\t\t\t\t\t\t END like '%{$search_on}%'\r\r\n OR wtcc.name LIKE '%{$search_on}%'\r\r\n {$variation_search_ids}\r\r\n {$email_search}\r\r\n )\r\r\n {$search_condn_purchase_log_ids}\r\r\n "; } else { $search_condn_purchase_logs = ''; } $query = $purchase_logs_select_query . $purchase_logs_from_query . $where . $search_condn_purchase_logs . " GROUP BY wtpl.id ORDER BY wtpl.id DESC {$limit_string}"; $results = $wpdb->get_results($query, 'ARRAY_A'); if (empty($results)) { for ($i = 0; $i < sizeof($result_shipping); $i++) { $log_id[$i] = $result_shipping[$i]['purchase_log_id']; } if (!is_null($log_id)) { $where .= "AND wtpl.id IN(" . implode(",", $log_id) . ")"; $query = $purchase_logs_select_query . $purchase_logs_from_query . $where . " GROUP BY wtpl.id ORDER BY wtpl.id DESC {$limit_string}"; $results = $wpdb->get_results($query, 'ARRAY_A'); } } if (!$is_export) { $orders_count_result = $wpdb->get_results(substr($query, 0, strpos($query, 'LIMIT')), 'ARRAY_A'); $num_records = count($orders_count_result); } else { $num_records = count($results); } $query = "SELECT ID,user_email FROM {$wpdb->users}"; $reg_user = $wpdb->get_results($query, 'ARRAY_A'); for ($i = 0; $i < sizeof($reg_user); $i++) { $user_email[$reg_user[$i]['ID']] = $reg_user[$i]['user_email']; } //To get the total count if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { foreach ($results as $data) { if ($matched_checkout_form_details && !isset($checkout_form_details[$data['id']])) { continue; } $checkout_form_details_keys = explode('###', $checkout_form_details[$data['id']]['meta_keys']); $checkout_form_details_values = explode('###', $checkout_form_details[$data['id']]['meta_values']); if (count($checkout_form_details_keys) == count($checkout_form_details_values)) { $checkout_form_data = array_combine($checkout_form_details_keys, $checkout_form_details_values); $name_emailid[0] = "<font class=blue>" . $checkout_form_data['billingfirstname'] . "</font>"; $name_emailid[1] = "<font class=blue>" . $checkout_form_data['billinglastname'] . "</font>"; $name_emailid[2] = "(" . $checkout_form_data['billingemail'] . ")"; //email comes at 7th position. $data['name'] = implode(' ', $name_emailid); //in front end,splitting is done with this space. $data['date'] = gmdate('Y-m-d H:i:s', $data['unixdate']); //Code to display the order date in GMT format if ($data['customer_id'] > 0) { $data['reg_email'] = $user_email[$data['customer_id']]; } else { $data['reg_email'] = ""; } $prod_ids = explode(',', $data['product_ids']); $products_name = ''; foreach ($prod_ids as $prod_id) { $products_name .= $product_details_results[$prod_id] . ', '; } $data['products_name'] = trim($products_name, ', '); if (!empty($checkout_form_data['shippingstate'])) { $ship_state = $checkout_form_data['shippingstate']; $checkout_form_data['shippingstate'] = $regions_ids[$ship_state] != '' ? $regions_ids[$ship_state] : $ship_state; } if (!empty($checkout_form_data['shippingcountry'])) { $ship_country = $checkout_form_data['shippingcountry']; $checkout_form_data['shippingcountry'] = $country_data[$ship_country] != '' ? $country_data[$ship_country] : $ship_country; } $records[] = !empty($checkout_form_data) ? array_merge($checkout_form_data, $data) : $data; } unset($data); unset($checkout_form_details_keys); unset($checkout_form_details_values); unset($checkout_form_data); } } } } else { //BOF Customer's module if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); } else { $search_on = ""; } $email_form_id = $wpdb->get_var("SELECT id FROM " . WPSC_TABLE_CHECKOUT_FORMS . " WHERE unique_name = 'billingemail'"); $query_max_users_ids = "SELECT GROUP_CONCAT(wtpl.id ORDER BY wtpl.date DESC SEPARATOR ',' ) AS last_order_id,\r\r\n GROUP_CONCAT(wtpl.totalprice ORDER BY wtpl.date DESC SEPARATOR ',' ) AS _order_total,\r\r\n DATE_FORMAT( MAX(FROM_UNIXTIME( wtpl.date )),'%b %e %Y' ) AS Last_Order,\r\r\n COUNT(wtpl.id) AS count_orders,\r\r\n SUM(wtpl.totalprice) AS total_orders\r\r\n \r\r\n FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\r\n LEFT JOIN " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS customer_email ON ( customer_email.log_id = wtpl.id AND customer_email.form_id = {$email_form_id} )\r\r\n WHERE wtpl.user_ID > 0\r\r\n Group by wtpl.user_ID"; $result_max_users_ids = $wpdb->get_results($query_max_users_ids, 'ARRAY_A'); $query_max_guest_ids = "SELECT GROUP_CONCAT(wtpl.id ORDER BY wtpl.date DESC SEPARATOR ',' ) AS last_order_id,\r\r\n GROUP_CONCAT(wtpl.totalprice ORDER BY wtpl.date DESC SEPARATOR ',' ) AS _order_total,\r\r\n DATE_FORMAT( MAX(FROM_UNIXTIME( wtpl.date )),'%b %e %Y' ) AS Last_Order,\r\r\n COUNT(wtpl.id) AS count_orders,\r\r\n SUM(wtpl.totalprice) AS total_orders\r\r\n \r\r\n FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\r\n LEFT JOIN " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS customer_email ON ( customer_email.log_id = wtpl.id AND customer_email.form_id = {$email_form_id} )\r\r\n WHERE wtpl.user_ID = 0\r\r\n GROUP BY customer_email.value\r\r\n ORDER BY Last_Order DESC"; $result_max_guest_ids = $wpdb->get_results($query_max_guest_ids, 'ARRAY_A'); for ($i = 0; $i < sizeof($result_max_guest_ids); $i++) { $temp_id = explode(",", $result_max_guest_ids[$i]['last_order_id']); $max_id[$i] = $temp_id[0]; $count_orders[$max_id[$i]] = $result_max_guest_ids[$i]['count_orders']; $total_orders[$max_id[$i]] = $result_max_guest_ids[$i]['total_orders']; $order_date[$max_id[$i]] = $result_max_guest_ids[$i]['Last_Order']; $temp_tot = explode(",", $result_max_guest_ids[$i]['_order_total']); $last_order_total[$max_id[$i]] = $temp_tot[0]; } $j = sizeof($max_id); for ($i = 0; $i < sizeof($result_max_users_ids); $i++, $j++) { $temp_id = explode(",", $result_max_users_ids[$i]['last_order_id']); $max_id[$j] = $temp_id[0]; $count_orders[$max_id[$j]] = $result_max_users_ids[$i]['count_orders']; $total_orders[$max_id[$j]] = $result_max_users_ids[$i]['total_orders']; $order_date[$max_id[$j]] = $result_max_users_ids[$i]['Last_Order']; $temp_tot = explode(",", $result_max_users_ids[$i]['_order_total']); $last_order_total[$max_id[$j]] = $temp_tot[0]; } $total_search = ""; if (!empty($search_on)) { $searched_region = $wpdb->get_col("SELECT code FROM " . WPSC_TABLE_REGION_TAX . " WHERE name LIKE '%{$search_on}%'"); $searched_country = $wpdb->get_col("SELECT isocode FROM " . WPSC_TABLE_CURRENCY_LIST . " WHERE country LIKE '%{$search_on}%' OR continent LIKE '%{$search_on}%'"); $found_country_region = array_merge($searched_region, $searched_country); $found_country_region_having = ''; foreach ($found_country_region as $country_region) { $found_country_region_having .= " OR meta_values LIKE '%{$country_region}%'"; } $email_query = "SELECT ID FROM {$wpdb->users} \r\r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_rows = $wpdb->num_rows; //Query to get the user ids of the rows whose content matches the search text $user_detail_query = "SELECT DISTINCT user_id FROM {$wpdb->usermeta} \r\r\n WHERE meta_key IN ('first_name','last_name','wpshpcrt_usr_profile') \r\r\n AND meta_value LIKE '%{$search_on}%'"; $user_detail_result = $wpdb->get_col($user_detail_query); $user_detail_rows = $wpdb->num_rows; //Code to merge all the user ids into a single array if ($user_detail_rows > 0) { for ($i = 0, $j = sizeof($email_result); $i < sizeof($user_detail_result); $i++, $j++) { $email_result[$j] = $user_detail_result[$i]; } } if ($email_rows > 0 || $user_detail_rows > 0) { $email_query1 = "SELECT ID FROM {$wpdb->prefix}wpsc_purchase_logs \r\r\n WHERE user_ID IN (" . implode(",", $email_result) . ")"; $email_result1 = $wpdb->get_col($email_query1); $email_search = !empty($email_result1) ? " OR wtsfd.log_id IN ( " . implode(',', $email_result1) . " )" : ''; } } $customer_details_query_select = "SELECT wtsfd.log_id AS log_id,\r\r\n GROUP_CONCAT( wtcf.unique_name ORDER BY wtcf.id SEPARATOR '###' ) AS meta_keys,\r\r\n GROUP_CONCAT( wtsfd.value ORDER BY wtsfd.form_id SEPARATOR '###' ) AS meta_values\r\r\n\r\r\n FROM " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS wtsfd\r\r\n JOIN " . WPSC_TABLE_CHECKOUT_FORMS . " AS wtcf ON ( wtcf.id = wtsfd.form_id AND wtcf.active = 1 AND wtcf.unique_name IN ('billingfirstname','billinglastname','billingaddress',\r\r\n 'billingcity','billingstate','billingcountry','billingpostcode',\r\r\n 'billingemail','billingphone') )\r\r\n WHERE log_id IN (" . implode(",", $max_id) . ")\r\r\n GROUP BY log_id"; if (!empty($search_on)) { $customer_details_query_having = " HAVING meta_values LIKE '%{$search_on}%'\r\r\n {$found_country_region_having}\r\r\n {$email_search}\r\r\n {$total_search} \r\r\n "; } else { $customer_details_query_having = ''; } $order_by = " ORDER BY FIND_IN_SET(log_id,'" . implode(",", $max_id) . "') {$limit_string}"; $full_customer_details_query = $customer_details_query_select . $customer_details_query_having . $order_by; $customer_details_results = $wpdb->get_results($full_customer_details_query, 'ARRAY_A'); if (is_null($customer_details_results)) { $full_customer_details_query = $customer_details_query_select . $order_by; $customer_details_results = $wpdb->get_results($full_customer_details_query, 'ARRAY_A'); } if (!$is_export) { $customers_count_result = $wpdb->get_results(substr($full_customer_details_query, 0, strpos($full_customer_details_query, 'LIMIT')), 'ARRAY_A'); $num_records = count($customers_count_result); } else { $num_records = count($customer_details_results); } //Code to get all the users along with their id and email in an array $query = "SELECT users.ID,users.user_email, GROUP_CONCAT(usermeta.meta_value \r\r\n ORDER BY usermeta.umeta_id SEPARATOR '###' ) AS name\r\r\n FROM {$wpdb->users} AS users\r\r\n JOIN {$wpdb->usermeta} AS usermeta ON usermeta.user_id = users.id\r\r\n WHERE usermeta.meta_key IN ('first_name','last_name','wpshpcrt_usr_profile')\r\r\n GROUP BY users.id DESC"; $reg_user = $wpdb->get_results($query, 'ARRAY_A'); for ($i = 0; $i < sizeof($reg_user); $i++) { $user_email[$reg_user[$i]['ID']] = $reg_user[$i]['user_email']; $name = explode("###", $reg_user[$i]['name']); $user_fname[$reg_user[$i]['ID']] = $name[0]; $user_lname[$reg_user[$i]['ID']] = $name[1]; if (!is_null($name[2])) { $unserialized_detail = unserialize($name[2]); $user_add[$reg_user[$i]['ID']] = $unserialized_detail[4]; $user_city[$reg_user[$i]['ID']] = $unserialized_detail[5]; $user_region[$reg_user[$i]['ID']] = $unserialized_detail[6]; $user_country[$reg_user[$i]['ID']] = $unserialized_detail[7][0]; $user_pcode[$reg_user[$i]['ID']] = $unserialized_detail[8]; $user_phone[$reg_user[$i]['ID']] = $unserialized_detail[18]; } } $country_result = $wpdb->get_results("SELECT isocode,country FROM " . WPSC_TABLE_CURRENCY_LIST, 'ARRAY_A'); $country_rows = $wpdb->num_rows; if ($country_rows > 0) { for ($i = 0; $i < sizeof($country_result); $i++) { $country[$country_result[$i]['isocode']] = $country_result[$i]['country']; } } if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { foreach ($customer_details_results as $result) { $meta_keys = explode('###', $result['meta_keys']); $meta_values = explode('###', $result['meta_values']); if (count($meta_keys) == count($meta_values)) { $customer_detail_data[$result['log_id']] = array_combine($meta_keys, $meta_values); } $result['last_order_id'] = $result['log_id']; $result['Last_Order'] = $order_date[$result['log_id']]; $result['_order_total'] = $last_order_total[$result['log_id']]; $result['count_orders'] = $count_orders[$result['log_id']]; $result['total_orders'] = $total_orders[$result['log_id']]; if (empty($customer_detail_data[$result['last_order_id']])) { $num_records--; continue; } $billing_user_details = $customer_detail_data[$result['last_order_id']]; $billing_user_details['billingstate'] = !empty($regions_ids[$billing_user_details['billingstate']]) ? $regions_ids[$billing_user_details['billingstate']] : $billing_user_details['billingstate']; $billing_user_details['billingcountry'] = !empty($country_data[$billing_user_details['billingcountry']]) ? $country_data[$billing_user_details['billingcountry']] : $billing_user_details['billingcountry']; if (SMPRO == false) { $result['Last_Order'] = 'Pro only'; $result['_order_total'] = 'Pro only'; $result['count_orders'] = 'Pro only'; $result['total_orders'] = 'Pro only'; } //Code to get the email for reg users from wp_users table if ($result['id'] > 0) { $result['email'] = $user_email[$result['id']]; $billing_user_details['billingemail'] = $user_email[$result['id']]; if (!empty($user_fname[$result['id']])) { $billing_user_details['billingfirstname'] = $user_fname[$result['id']]; } if (!empty($user_lname[$result['id']])) { $billing_user_details['billinglastname'] = $user_lname[$result['id']]; } $billing_user_details['billingaddress'] = $user_add[$result['id']]; $billing_user_details['billingcity'] = $user_city[$result['id']]; $billing_user_details['billingstate'] = !empty($regions_ids[$user_region[$result['id']]]) ? $regions_ids[$user_region[$result['id']]] : $user_region[$result['id']]; $billing_user_details['billingcountry'] = $country[$user_country[$result['id']]]; $billing_user_details['billingpostcode'] = $user_pcode[$result['id']]; $billing_user_details['billingphone'] = $user_phone[$result['id']]; } //NOTE: storing old email id in an extra column in record so useful to indentify record with emailid during updates. $result['Old_Email_Id'] = $billing_user_details['billingemail']; $records[] = !empty($billing_user_details) ? array_merge($billing_user_details, $result) : $result; unset($result); unset($meta_keys); unset($meta_values); unset($billing_user_details); } } } 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 = 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; } }