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