Ejemplo n.º 1
0
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;
    }
}
Ejemplo n.º 2
0
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;
    }
}