/** * wpsc_additional_column_data. * * @access public * @param (array) $column * @return void * @todo Need to check titles / alt tags ( I don't think thumbnails have any in this code ) * @desc Switch function to generate columns the right way...no more UI hacking! * */ function wpsc_additional_column_data($column, $post_id) { $post = get_post($post_id); $is_parent = wpsc_product_has_children($post_id); $column = strtolower($column); do_action("wpsc_manage_products_column_{$column}", $post, $post_id, $is_parent); }
/** * Spits out the current products details in a table row for manage products page and variations on edit product page. * @access public * * @deprecated since 3.8.9 * @since 3.8 * @param $product (Object), $parent_product (Int) Note: I believe parent_product is unused */ function wpsc_product_row(&$product, $parent_product = null) { _wpsc_deprecated_function(__FUNCTION__, '3.8.9'); global $mode, $current_user, $wpsc_products; //is this good practice? <*****@*****.**> static $rowclass, $object_terms_cache = array(); // store terms associated with variants inside a cache array. This only requires 1 DB query. if (empty($object_terms_cache)) { $ids = wp_list_pluck($wpsc_products, 'ID'); $object_terms = wp_get_object_terms($ids, 'wpsc-variation', array('fields' => 'all_with_object_id')); foreach ($object_terms as $term) { if (!array_key_exists($term->object_id, $object_terms_cache)) { $object_terms_cache[$term->object_id] = array(); } $object_terms_cache[$term->object_id][$term->parent] = $term->name; } } $global_product = $product; setup_postdata($product); $product_post_type_object = get_post_type_object('wpsc-product'); $current_user_can_edit_this_product = current_user_can($product_post_type_object->cap->edit_post, $product->ID); $rowclass = 'alternate' == $rowclass ? '' : 'alternate'; $post_owner = $current_user->ID == $product->post_author ? 'self' : 'other'; $edit_link = get_edit_post_link($product->ID); if (isset($object_terms_cache[$product->ID])) { ksort($object_terms_cache[$product->ID]); $title = implode(', ', $object_terms_cache[$product->ID]); } else { $title = get_the_title($product->ID); } if (empty($title)) { $title = __('(no title)', 'wp-e-commerce'); } ?> <tr id='post-<?php echo $product->ID; ?> ' class='<?php echo trim($rowclass . ' author-' . $post_owner . ' status-' . $product->post_status); ?> iedit <?php if (get_option('wpsc_sort_by') == 'dragndrop') { echo 'product-edit'; } ?> ' valign="top"> <?php $posts_columns = get_column_headers('wpsc-product_variants'); if (empty($posts_columns)) { $posts_columns = array('image' => '', 'title' => __('Name', 'wp-e-commerce'), 'weight' => __('Weight', 'wp-e-commerce'), 'stock' => __('Stock', 'wp-e-commerce'), 'price' => __('Price', 'wp-e-commerce'), 'sale_price' => __('Sale Price', 'wp-e-commerce'), 'SKU' => __('SKU', 'wp-e-commerce'), 'hidden_alerts' => ''); } foreach ($posts_columns as $column_name => $column_display_name) { $attributes = "class=\"{$column_name} column-{$column_name}\""; switch ($column_name) { case 'date': /* !date case */ if ('0000-00-00 00:00:00' == $product->post_date && 'date' == $column_name) { $t_time = $h_time = __('Unpublished', 'wp-e-commerce'); $time_diff = 0; } else { $t_time = get_the_time(__('Y/m/d g:i:s A', 'wp-e-commerce')); $m_time = $product->post_date; $time = get_post_time('G', true, $post); $time_diff = time() - $time; if ($time_diff > 0 && $time_diff < 24 * 60 * 60) { $h_time = sprintf(__('%s ago', 'wp-e-commerce'), human_time_diff($time)); } else { $h_time = mysql2date(__('Y/m/d', 'wp-e-commerce'), $m_time); } } echo '<td ' . $attributes . '>'; if ('excerpt' == $mode) { echo apply_filters('post_date_column_time', $t_time, $post, $column_name, $mode); } else { echo '<abbr title="' . $t_time . '">' . apply_filters('post_date_column_time', $h_time, $post, $column_name, $mode) . '</abbr>'; } echo '<br />'; if ('publish' == $product->post_status) { _e('Published', 'wp-e-commerce'); } elseif ('future' == $product->post_status) { if ($time_diff > 0) { echo '<strong class="attention">' . __('Missed schedule', 'wp-e-commerce') . '</strong>'; } else { _e('Scheduled', 'wp-e-commerce'); } } else { _e('Last Modified', 'wp-e-commerce'); } echo '</td>'; break; case 'title': /* !title case */ $attributes = 'class="post-title column-title"'; $edit_link = wp_nonce_url($edit_link, 'edit-product_' . $product->ID); ?> <td <?php echo $attributes; ?> > <strong> <?php if ($current_user_can_edit_this_product && $product->post_status != 'trash') { ?> <span><a class="row-title" href="<?php echo esc_url($edit_link); ?> " title="<?php echo esc_attr(sprintf(__('Edit “%s”', 'wp-e-commerce'), $title)); ?> "><?php echo esc_html($title); ?> </a></span> <?php if ($parent_product) { ?> <a href="<?php echo esc_url($edit_link); ?> " title="<?php echo esc_attr(sprintf(__('Edit “%s”', 'wp-e-commerce'), $title)); ?> "><?php echo esc_html($title); ?> </a> <?php } ?> <?php } else { echo esc_html($title); } _post_states($product); $product_alert = apply_filters('wpsc_product_alert', array(false, ''), $product); if (!empty($product_alert['messages'])) { $product_alert['messages'] = implode("\n", (array) $product_alert['messages']); } if ($product_alert['state'] === true) { ?> <img alt='<?php echo $product_alert['messages']; ?> ' title='<?php echo $product_alert['messages']; ?> ' class='product-alert-image' src='<?php echo WPSC_CORE_IMAGES_URL; ?> /product-alert.jpg' alt='' /> <?php } // If a product alert has stuff to display, show it. // Can be used to add extra icons etc if (!empty($product_alert['display'])) { echo $product_alert['display']; } ?> </strong> <?php $has_var = ''; if (!$parent_product && wpsc_product_has_children($product->ID)) { $has_var = 'wpsc_has_variation'; } $actions = array(); if ($current_user_can_edit_this_product && 'trash' != $product->post_status) { $actions['edit'] = '<a class="edit-product" href="' . $edit_link . '" title="' . esc_attr__('Edit this product', 'wp-e-commerce') . '">' . __('Edit', 'wp-e-commerce') . '</a>'; //commenting this out for now as we are trying new variation ui quick edit boxes are open by default so we dont need this link. //$actions['quick_edit'] = "<a class='wpsc_editinline ".$has_var."' title='".esc_attr(__('Quick Edit', 'wp-e-commerce'))."' href='#'>".__('Quick Edit', 'wp-e-commerce')."</a>"; } $actions = apply_filters('post_row_actions', $actions, $product); $action_count = count($actions); $i = 0; echo '<div class="row-actions">'; foreach ($actions as $action => $link) { ++$i; $i == $action_count ? $sep = '' : ($sep = ' | '); echo "<span class='{$action}'>{$link}{$sep}</span>"; } echo '</div>'; ?> </td> <?php break; case 'image': /* !image case */ ?> <td class="product-image "> <?php $attachment_args = array('post_type' => 'attachment', 'numberposts' => 1, 'post_status' => null, 'post_parent' => $product->ID, 'orderby' => 'menu_order', 'order' => 'ASC'); if (isset($product->ID) && has_post_thumbnail($product->ID)) { echo get_the_post_thumbnail($product->ID, 'admin-product-thumbnails'); } else { $image_url = WPSC_CORE_IMAGES_URL . "/no-image-uploaded.gif"; ?> <img title='<?php esc_attr_e('Drag to a new position', 'wp-e-commerce'); ?> ' src='<?php echo esc_url($image_url); ?> ' alt='<?php echo esc_attr($title); ?> ' width='38' height='38' /> <?php } ?> </td> <?php break; case 'price': /* !price case */ $price = get_product_meta($product->ID, 'price', true); ?> <td <?php echo $attributes; ?> > <?php echo wpsc_currency_display($price); ?> <input type="text" class="wpsc_ie_field wpsc_ie_price" value="<?php echo esc_attr($price); ?> "> <a href="<?php echo $edit_link; ?> /#wpsc_downloads"><?php esc_html_e('Variant Download Files', 'wp-e-commerce'); ?> </a> </td> <?php break; case 'weight': $product_data['meta'] = array(); $product_data['meta'] = get_post_meta($product->ID, ''); foreach ($product_data['meta'] as $meta_name => $meta_value) { $product_data['meta'][$meta_name] = maybe_unserialize(array_pop($meta_value)); } $product_data['transformed'] = array(); if (!isset($product_data['meta']['_wpsc_product_metadata']['weight'])) { $product_data['meta']['_wpsc_product_metadata']['weight'] = ""; } if (!isset($product_data['meta']['_wpsc_product_metadata']['weight_unit'])) { $product_data['meta']['_wpsc_product_metadata']['weight_unit'] = ""; } $product_data['transformed']['weight'] = wpsc_convert_weight($product_data['meta']['_wpsc_product_metadata']['weight'], "pound", $product_data['meta']['_wpsc_product_metadata']['weight_unit'], false); $weight = $product_data['transformed']['weight']; if ($weight == '') { $weight = '0'; } ?> <td <?php echo $attributes; ?> > <span><?php echo esc_html($weight); ?> </span> <input type="text" class="wpsc_ie_field wpsc_ie_weight" value="<?php echo esc_attr($weight); ?> "> <a href="<?php echo $edit_link; ?> /#wpsc_tax"><?php esc_html_e('Set Variant Tax', 'wp-e-commerce'); ?> </a> </td> <?php break; case 'stock': $stock = get_post_meta($product->ID, '_wpsc_stock', true); ?> <td <?php echo $attributes; ?> > <span><?php echo $stock ? $stock : __('N/A', 'wp-e-commerce'); ?> </span> <input type="text" class="wpsc_ie_field wpsc_ie_stock" value="<?php echo esc_attr($stock); ?> "> <a href="<?php echo $edit_link; ?> /#wpsc_shipping"><?php esc_html_e('Set Variant Shipping', 'wp-e-commerce'); ?> </a> </td> <?php break; case 'categories': /* !categories case */ ?> <td <?php echo $attributes; ?> ><?php $categories = get_the_product_category($product->ID); if (!empty($categories)) { $out = array(); foreach ($categories as $c) { $out[] = "<a href='admin.php?page=wpsc-edit-products&category={$c->slug}'> " . esc_html(sanitize_term_field('name', $c->name, $c->term_id, 'category', 'display')) . "</a>"; } echo join(', ', $out); } else { esc_html_e('Uncategorized', 'wp-e-commerce'); } ?> </td> <?php break; case 'tags': /* !tags case */ ?> <td <?php echo $attributes; ?> ><?php $tags = get_the_tags($product->ID); if (!empty($tags)) { $out = array(); foreach ($tags as $c) { $out[] = "<a href='edit.php?tag={$c->slug}'> " . esc_html(sanitize_term_field('name', $c->name, $c->term_id, 'post_tag', 'display')) . "</a>"; } echo join(', ', $out); } else { esc_html_e('No Tags', 'wp-e-commerce'); } ?> </td> <?php break; case 'SKU': $sku = get_post_meta($product->ID, '_wpsc_sku', true); ?> <td <?php echo $attributes; ?> > <span><?php echo $sku ? $sku : esc_html__('N/A', 'wp-e-commerce'); ?> </span> <input type="text" class="wpsc_ie_field wpsc_ie_sku" value="<?php echo esc_attr($sku); ?> "> <input type="hidden" class="wpsc_ie_id wpsc_ie_field" value="<?php echo $product->ID; ?> "> <div class="wpsc_inline_actions"><input type="button" class="button-primary wpsc_ie_save" value="Save"><img src="<?php echo admin_url('images/wpspin_light.gif'); ?> " class="loading_indicator"><br/></div> </td> <?php break; case 'sale_price': $sale_price = get_post_meta($product->ID, '_wpsc_special_price', true); ?> <td <?php echo $attributes; ?> > <span><?php echo wpsc_currency_display($sale_price); ?> </span> <input type="text" class="wpsc_ie_field wpsc_ie_special_price" value="<?php echo esc_attr($sale_price); ?> "> </td> <?php break; case 'comments': /* !comments case */ ?> <td <?php echo $attributes; ?> ><div class="post-com-count-wrapper"> <?php $pending_phrase = sprintf(__('%s pending', 'wp-e-commerce'), number_format($pending_comments)); if ($pending_comments) { echo '<strong>'; } comments_number("<a href='edit-comments.php?p={$product->ID}' title='{$pending_phrase}' class='post-com-count'><span class='comment-count'>" . _x('0', 'comment count', 'wp-e-commerce') . '</span></a>', "<a href='edit-comments.php?p={$product->ID}' title='{$pending_phrase}' class='post-com-count'><span class='comment-count'>" . _x('1', 'comment count', 'wp-e-commerce') . '</span></a>', "<a href='edit-comments.php?p={$product->ID}' title='{$pending_phrase}' class='post-com-count'><span class='comment-count'>" . _x('%', 'comment count', 'wp-e-commerce') . '</span></a>'); if ($pending_comments) { echo '</strong>'; } ?> </div></td> <?php break; case 'author': /* !author case */ ?> <td <?php echo $attributes; ?> ><a href="edit.php?author=<?php the_author_meta('ID'); ?> "><?php the_author(); ?> </a></td> <?php break; case 'control_view': /* !control view case */ ?> <td><a href="<?php the_permalink(); ?> " rel="permalink" class="view"><?php esc_html_e('View', 'wp-e-commerce'); ?> </a></td> <?php break; case 'control_edit': /* !control edit case */ ?> <td><?php if ($current_user_can_edit_this_product) { echo "<a href='{$edit_link}' class='edit'>" . esc_html__('Edit', 'wp-e-commerce') . "</a>"; } ?> </td> <?php break; case 'control_delete': /* !control delete case */ ?> <td><?php if ($current_user_can_edit_this_product) { echo "<a href='" . wp_nonce_url("post.php?action=delete&post={$id}", 'delete-post_' . $product->ID) . "' class='delete'>" . __('Delete', 'wp-e-commerce') . "</a>"; } ?> </td> <?php break; case 'featured': /* !control featured case */ ?> <td><?php do_action('manage_posts_featured_column', $product->ID); ?> </td> <?php break; default: /* !default case */ ?> <td <?php echo $attributes; ?> ><?php do_action('manage_posts_custom_column', $column_name, $product->ID); ?> </td> <?php break; } } ?> </tr> <?php $product = $global_product; }
function get_data_wpsc_38($post, $offset, $limit, $is_export = false) { global $wpdb, $post_status, $parent_sort_id, $order_by; $_POST = $post; // Fix: PHP 5.4 $regions_ids = get_regions_ids(); $country_results = $wpdb->get_results("SELECT isocode, country FROM " . WPSC_TABLE_CURRENCY_LIST, 'ARRAY_A'); $country_data = array(); foreach ($country_results as $country_result) { $country_data[$country_result['isocode']] = $country_result['country']; } //Code to handle the show variations query function variation_query_params() { global $wpdb, $post_status, $parent_sort_id, $order_by; $post_status = "('publish', 'pending', 'draft', 'inherit') AND {$wpdb->prefix}posts.ID NOT IN \r\n\t\t\t\t\t\t\t( SELECT product.ID FROM {$wpdb->prefix}posts AS product \r\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}posts AS product_variation \r\n\t\t\t\t\t\t\tON product_variation.ID = product.post_parent \r\n\t\t\t\t\t\t\tWHERE product_variation.post_status = 'trash' ) "; $parent_sort_id = " ,if({$wpdb->prefix}posts.post_parent = 0,{$wpdb->prefix}posts.id,{$wpdb->prefix}posts.post_parent - 1 + ({$wpdb->prefix}posts.id)/pow(10,char_length(cast({$wpdb->prefix}posts.id as char)))\t) as parent_sort_id"; $order_by = " ORDER BY parent_sort_id desc"; } // getting the active module // $active_module = $_POST ['active_module']; $active_module = isset($_POST['active_module']) ? $_POST['active_module'] : 'Products'; variation_query_params(); if ($is_export === true) { $limit_string = ""; $image_size = "full"; } else { $limit_string = "LIMIT {$offset},{$limit}"; $image_size = "thumbnail"; } $wpdb->query("SET SESSION group_concat_max_len=999999"); // To increase the max length of the Group Concat Functionality $view_columns = !empty($_POST['viewCols']) ? json_decode(stripslashes($_POST['viewCols'])) : ''; if ($active_module == 'Products') { // <-products $wpsc_default_image = WP_PLUGIN_URL . '/wp-e-commerce/wpsc-theme/wpsc-images/noimage.png'; if (isset($_POST['incVariation']) && $_POST['incVariation'] == 'true') { $show_variation = true; } else { // query params for non-variation products $show_variation = false; $post_status = "('publish', 'pending', 'draft')"; $parent_sort_id = ''; $order_by = " ORDER BY {$wpdb->prefix}posts.id desc"; } $query_ids = "SELECT `ID` FROM {$wpdb->prefix}posts \r\n WHERE `post_type` = 'wpsc-product' \r\n AND `post_status` = 'publish' \r\n AND `post_parent`=0 \r\n AND `ID` NOT IN ( SELECT distinct `post_parent` \r\n FROM {$wpdb->prefix}posts WHERE `post_parent`>0)"; $result_ids = $wpdb->get_col($query_ids); $num_ids = $wpdb->num_rows; if ($num_ids > 0) { for ($i = 0; $i < sizeof($result_ids); $i++) { $simple_ids[$result_ids[$i]] = 0; } } // if max-join-size issue occurs $query = "SET SQL_BIG_SELECTS=1;"; $wpdb->query($query); $results_trash = array(); //Code to get the ids of all the products whose post_status is thrash $query_trash = "SELECT ID FROM {$wpdb->prefix}posts \r\n WHERE post_status = 'trash'\r\n AND post_type IN ('product')"; $results_trash = $wpdb->get_col($query_trash); $rows_trash = $wpdb->num_rows; $query_deleted = "SELECT distinct products.post_parent \r\n FROM {$wpdb->prefix}posts as products \r\n WHERE NOT EXISTS (SELECT * FROM {$wpdb->prefix}posts WHERE ID = products.post_parent) \r\n AND products.post_parent > 0 \r\n AND products.post_type = 'product_variation'"; $results_deleted = $wpdb->get_col($query_deleted); $rows_deleted = $wpdb->num_rows; for ($i = sizeof($results_trash), $j = 0; $j < sizeof($results_deleted); $i++, $j++) { $results_trash[$i] = $results_deleted[$j]; } if ($rows_trash > 0 || $rows_deleted > 0) { $trash_id = " AND {$wpdb->prefix}posts.post_parent NOT IN (" . implode(",", $results_trash) . ")"; } else { $trash_id = ""; } //Query to get the Category Ids $query_categories = "SELECT {$wpdb->prefix}posts.id as id,\r\n GROUP_CONCAT(distinct {$wpdb->prefix}term_relationships.term_taxonomy_id order by {$wpdb->prefix}term_relationships.object_id SEPARATOR '###') AS term_taxonomy_id\r\n FROM {$wpdb->prefix}posts\r\n JOIN {$wpdb->prefix}term_relationships ON ({$wpdb->prefix}posts.id = {$wpdb->prefix}term_relationships.object_id)\r\n WHERE {$wpdb->prefix}posts.post_status IN {$post_status}\r\n AND {$wpdb->prefix}posts.post_type LIKE 'wpsc-product'\r\n {$trash_id}\r\n GROUP BY id"; $records_categories = $wpdb->get_results($query_categories, 'ARRAY_A'); $category_ids_all = array(); foreach ($records_categories as $records_category) { $category_ids_all[$records_category['id']] = $records_category['term_taxonomy_id']; } //Query to get the term_taxonomy_id for all the product categories $query_terms = "SELECT terms.name, wt.term_taxonomy_id FROM {$wpdb->prefix}term_taxonomy AS wt\r\n JOIN {$wpdb->prefix}terms AS terms ON (wt.term_id = terms.term_id)\r\n WHERE wt.taxonomy LIKE 'wpsc_product_category'"; $results = $wpdb->get_results($query_terms, 'ARRAY_A'); $rows_terms = $wpdb->num_rows; if ($rows_terms > 0) { foreach ($results as $result) { $term_taxonomy[$result['term_taxonomy_id']] = $result['name']; } } // CAST(GROUP_CONCAT(DISTINCT term_relationships.term_taxonomy_id order by term_relationships.term_taxonomy_id SEPARATOR ',') AS CHAR(1000)) AS term_taxonomy_id $post_meta_select = !empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWpsc' ? ", GROUP_CONCAT(prod_othermeta.meta_key order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_key,\r\n\t\t\t\t\tGROUP_CONCAT(prod_othermeta.meta_value order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_value" : ""; $select = "SELECT SQL_CALC_FOUND_ROWS {$wpdb->prefix}posts.id,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_title,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_title as post_title_search,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_content,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_excerpt,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_status,\r\n\t\t\t\t\t{$wpdb->prefix}posts.post_parent\r\n\t\t\t\t\t{$post_meta_select}\r\n\t\t\t\t\t{$parent_sort_id}"; //Used as an alternative to the SQL_CALC_FOUND_ROWS function of MYSQL Database $select_count = "SELECT COUNT(*) as count"; // To get the count of the number of rows generated from the above select query $search = ""; $search_condn = ""; //Code to clear the advanced search temp table if (empty($_POST['search_query']) || empty($_POST['search_query'][0]) || !empty($_POST['searchText'])) { $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp"); delete_option('sm_advanced_search_query'); } $sm_advanced_search_results_persistent = 0; //flag to handle persistent search results //Advanced Search Code if (!empty($_POST['search_query']) && !empty($_POST['search_query'][0]) || !empty($_POST['searchText'])) { if (empty($_POST['searchText'])) { $search_query_diff = get_option('sm_advanced_search_query') != '' ? array_diff($_POST['search_query'], get_option('sm_advanced_search_query')) : $_POST['search_query']; } else { $search_query_diff = ''; } if (!empty($search_query_diff)) { $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp"); // query to reset advanced search temp table $advanced_search_query = array(); $i = 0; update_option('sm_advanced_search_query', $_POST['search_query']); foreach ($_POST['search_query'] as $search_string_array) { $search_string_array = json_decode(stripslashes($search_string_array), true); if (is_array($search_string_array)) { $advanced_search_query[$i] = array(); $advanced_search_query[$i]['cond_posts'] = ''; $advanced_search_query[$i]['cond_postmeta'] = ''; $advanced_search_query[$i]['cond_terms'] = ''; $advanced_search_query[$i]['cond_postmeta_col_name'] = ''; $advanced_search_query[$i]['cond_postmeta_col_value'] = ''; $advanced_search_query[$i]['cond_postmeta_operator'] = ''; $advanced_search_query[$i]['cond_terms_col_name'] = ''; $advanced_search_query[$i]['cond_terms_col_value'] = ''; $advanced_search_query[$i]['cond_terms_operator'] = ''; $search_value_is_array = 0; //flag for array of search_values foreach ($search_string_array as $search_string) { $search_col = !empty($search_string['col_name']) ? $search_string['col_name'] : ''; $search_operator = !empty($search_string['operator']) ? $search_string['operator'] : ''; $search_data_type = !empty($search_string['type']) ? $search_string['type'] : 'string'; $search_value = !empty($search_string['value']) ? $search_string['value'] : ($search_data_type == "number" ? '0' : ''); if (!empty($search_string['table_name']) && $search_string['table_name'] == $wpdb->prefix . 'posts') { if ($search_data_type == "number") { $advanced_search_query[$i]['cond_posts'] .= $search_string['table_name'] . "." . $search_col . " " . $search_operator . " " . $search_value; } else { if ($search_operator == 'is') { $advanced_search_query[$i]['cond_posts'] .= $search_string['table_name'] . "." . $search_col . " LIKE '" . $search_value . "'"; } else { if ($search_operator == 'is not') { $advanced_search_query[$i]['cond_posts'] .= $search_string['table_name'] . "." . $search_col . " NOT LIKE '" . $search_value . "'"; } else { $advanced_search_query[$i]['cond_posts'] .= $search_string['table_name'] . "." . $search_col . " " . $search_operator . "'%" . $search_value . "%'"; } } } $advanced_search_query[$i]['cond_posts'] .= " AND "; } else { if (!empty($search_string['table_name']) && $search_string['table_name'] == $wpdb->prefix . 'postmeta') { if ($search_value == 'inches') { $search_value = 'in'; } else { if ($search_value == 'pounds') { $search_value = 'pound'; } else { if ($search_value == 'ounces') { $search_value = 'ounce'; } else { if ($search_value == 'grams') { $search_value = 'gram'; } else { if ($search_value == 'kilograms') { $search_value = 'kilogram'; } } } } } $advanced_search_query[$i]['cond_postmeta_col_name'] .= $search_col; $advanced_search_query[$i]['cond_postmeta_col_value'] .= $search_value; if ($search_col != '_wpsc_price' && $search_col != '_wpsc_special_price' && $search_col != '_wpsc_sku' && $search_col != '_wpsc_stock') { $search_col_temp = $search_col; $search_value_temp = $search_value; $search_col = '_wpsc_product_metadata'; if ($search_operator == 'is' || $search_operator == 'is not') { $search_value = '%' . $search_col_temp . '";s:' . strlen($search_value_temp) . ':%"' . $search_value_temp . '";%'; } else { $search_value = '%' . $search_col_temp . '%' . $search_value_temp . '%'; } } if ($search_data_type == "number") { if ($search_col != '_wpsc_price' && $search_col != '_wpsc_special_price' && $search_col != '_wpsc_sku' && $search_col != '_wpsc_stock') { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value " . $search_operator . " " . $search_value . " )"; } else { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value " . $search_operator . " " . $search_value . " )"; } $advanced_search_query[$i]['cond_postmeta_operator'] .= $search_operator; } else { if ($search_operator == 'is') { $advanced_search_query[$i]['cond_postmeta_operator'] .= 'LIKE'; $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value LIKE '" . $search_value . "'" . " )"; } else { if ($search_operator == 'is not') { $advanced_search_query[$i]['cond_postmeta_operator'] .= 'NOT LIKE'; $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value NOT LIKE '" . $search_value . "'" . " )"; } else { $advanced_search_query[$i]['cond_postmeta_operator'] .= $search_operator; if ($search_col != '_wpsc_price' && $search_col != '_wpsc_special_price' && $search_col != '_wpsc_sku' && $search_col != '_wpsc_stock') { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value " . $search_operator . "'" . $search_value . "'" . " )"; } else { $advanced_search_query[$i]['cond_postmeta'] .= " ( " . $search_string['table_name'] . ".meta_key LIKE '" . $search_col . "' AND " . $search_string['table_name'] . ".meta_value " . $search_operator . "'%" . $search_value . "%'" . " )"; } } } } $advanced_search_query[$i]['cond_postmeta'] .= " AND "; $advanced_search_query[$i]['cond_postmeta_col_name'] .= " AND "; $advanced_search_query[$i]['cond_postmeta_col_value'] .= " AND "; $advanced_search_query[$i]['cond_postmeta_operator'] .= " AND "; } else { if (!empty($search_string['table_name']) && $search_string['table_name'] == $wpdb->prefix . 'term_relationships') { $advanced_search_query[$i]['cond_terms_col_name'] .= $search_col; $advanced_search_query[$i]['cond_terms_col_value'] .= $search_value; if ($search_operator == 'is') { $advanced_search_query[$i]['cond_terms'] .= " ( " . $wpdb->prefix . "term_taxonomy.taxonomy LIKE '" . $search_col . "' AND " . $wpdb->prefix . "terms.slug LIKE '" . $search_value . "'" . " )"; $advanced_search_query[$i]['cond_terms_operator'] .= 'LIKE'; } else { if ($search_operator == 'is not') { $advanced_search_query[$i]['cond_terms'] .= " ( " . $wpdb->prefix . "term_taxonomy.taxonomy LIKE '" . $search_col . "' AND " . $wpdb->prefix . "terms.slug NOT LIKE '" . $search_value . "'" . " )"; $advanced_search_query[$i]['cond_terms_operator'] .= 'NOT LIKE'; } else { $advanced_search_query[$i]['cond_terms'] .= " ( " . $wpdb->prefix . "term_taxonomy.taxonomy LIKE '" . $search_col . "' AND " . $wpdb->prefix . "terms.slug " . $search_operator . "'%" . $search_value . "%'" . " )"; $advanced_search_query[$i]['cond_terms_operator'] .= $search_operator; } } $advanced_search_query[$i]['cond_terms'] .= " AND "; $advanced_search_query[$i]['cond_terms_col_name'] .= " AND "; $advanced_search_query[$i]['cond_terms_col_value'] .= " AND "; $advanced_search_query[$i]['cond_terms_operator'] .= " AND "; } } } } $advanced_search_query[$i]['cond_posts'] = !empty($advanced_search_query[$i]['cond_posts']) ? substr($advanced_search_query[$i]['cond_posts'], 0, -4) : ''; $advanced_search_query[$i]['cond_postmeta'] = !empty($advanced_search_query[$i]['cond_postmeta']) ? substr($advanced_search_query[$i]['cond_postmeta'], 0, -4) : ''; $advanced_search_query[$i]['cond_terms'] = !empty($advanced_search_query[$i]['cond_terms']) ? substr($advanced_search_query[$i]['cond_terms'], 0, -4) : ''; $advanced_search_query[$i]['cond_postmeta_col_name'] = !empty($advanced_search_query[$i]['cond_postmeta_col_name']) ? substr($advanced_search_query[$i]['cond_postmeta_col_name'], 0, -4) : ''; $advanced_search_query[$i]['cond_postmeta_col_value'] = !empty($advanced_search_query[$i]['cond_postmeta_col_value']) ? substr($advanced_search_query[$i]['cond_postmeta_col_value'], 0, -4) : ''; $advanced_search_query[$i]['cond_postmeta_operator'] = !empty($advanced_search_query[$i]['cond_postmeta_operator']) ? substr($advanced_search_query[$i]['cond_postmeta_operator'], 0, -4) : ''; $advanced_search_query[$i]['cond_terms_col_name'] = !empty($advanced_search_query[$i]['cond_terms_col_name']) ? substr($advanced_search_query[$i]['cond_terms_col_name'], 0, -4) : ''; $advanced_search_query[$i]['cond_terms_col_value'] = !empty($advanced_search_query[$i]['cond_terms_col_value']) ? substr($advanced_search_query[$i]['cond_terms_col_value'], 0, -4) : ''; $advanced_search_query[$i]['cond_terms_operator'] = !empty($advanced_search_query[$i]['cond_terms_operator']) ? substr($advanced_search_query[$i]['cond_terms_operator'], 0, -4) : ''; } $i++; } } else { if (!empty($_POST['searchText'])) { $advanced_search_query[0]['cond_posts'] = $wpdb->prefix . 'posts' . ".id LIKE '" . $_POST['searchText'] . "'"; $advanced_search_query[1]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_title LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[2]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_status LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[3]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_content LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[4]['cond_posts'] = $wpdb->prefix . 'posts' . ".post_excerpt LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[5]['cond_postmeta'] = $wpdb->prefix . 'postmeta' . ".meta_value LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[6]['cond_terms'] = $wpdb->prefix . "term_taxonomy.taxonomy LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[7]['cond_terms'] = $wpdb->prefix . "terms.slug LIKE '%" . $_POST['searchText'] . "%'"; $advanced_search_query[8]['cond_terms'] = $wpdb->prefix . "terms.name LIKE '%" . $_POST['searchText'] . "%'"; } else { $sm_advanced_search_results_persistent = 1; } } } /* if (isset ( $_POST ['searchText'] ) && $_POST ['searchText'] != '') { $search_on = trim ( $_POST ['searchText'] ); $count_all_double_quote = substr_count( $search_on, '"' ); if ( $count_all_double_quote > 0 ) { $search_ons = array_filter( array_map( 'trim', explode( $wpdb->_real_escape( '"' ), $search_on ) ) ); } else { $search_on = $wpdb->_real_escape( $search_on ); $search_ons = explode( ' ', $search_on ); } //Code for searching using modified post title $query_title = "SELECT ID FROM {$wpdb->prefix}posts WHERE post_title LIKE '%$search_on%' AND post_type = 'wpsc-product'"; $records_title = $wpdb->get_col ( $query_title ); $rows_title = $wpdb->num_rows; if ($rows_title > 0) { $search_title = "OR products.post_parent IN ( SELECT ID FROM {$wpdb->prefix}posts WHERE post_title LIKE '%$search_on%' AND post_type = 'wpsc-product')"; } else { $search_title = " "; } if ( is_array( $search_ons ) && ! empty( $search_ons ) ) { $term_taxonomy_ids = get_term_taxonomy_ids( '"' . implode( '","', $search_ons ) . '"' ); $search_condn = " HAVING "; foreach ( $search_ons as $search_on ) { $search_condn .= " concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%$search_on%' OR post_content LIKE '%$search_on%' OR post_excerpt LIKE '%$search_on%' OR if(post_status = 'publish','Published',post_status) LIKE '$search_on%' OR prod_othermeta_value LIKE '%$search_on%' $search_title OR"; } if ( is_array( $term_taxonomy_ids ) && !empty( $term_taxonomy_ids ) ) { foreach ( $term_taxonomy_ids as $term_taxonomy_id ) { $search_condn .= " term_taxonomy_id LIKE '%$term_taxonomy_id%' OR"; } } $search_condn = substr( $search_condn, 0, -2 ); } else { $term_taxonomy_ids = get_term_taxonomy_ids( '"' . $search_on . '"' ); $search_condn = " HAVING concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%$search_on%' OR post_content LIKE '%$search_on%' OR post_excerpt LIKE '%$search_on%' OR if(post_status = 'publish','Published',post_status) LIKE '$search_on%' OR prod_othermeta_value LIKE '%$search_on%' $search_title "; if ( is_array( $term_taxonomy_ids ) && !empty( $term_taxonomy_ids ) ) { foreach ( $term_taxonomy_ids as $term_taxonomy_id ) { $search_condn .= " OR term_taxonomy_id LIKE '%$term_taxonomy_id%'"; } } } } else { $search_condn = ''; } */ //code for the advanced Search condition handling //Code for term_relationships if (!empty($advanced_search_query)) { $advanced_search_post_ids = array(); $index_search_string = 1; foreach ($advanced_search_query as &$advanced_search_query_string) { if (!empty($advanced_search_query_string['cond_terms'])) { $cond_terms_array = explode(" AND ", $advanced_search_query_string['cond_terms']); $cond_terms_col_name = !empty($advanced_search_query_string['cond_terms_col_name']) ? explode(" AND ", $advanced_search_query_string['cond_terms_col_name']) : ''; $cond_terms_col_value = !empty($advanced_search_query_string['cond_terms_col_value']) ? explode(" AND ", $advanced_search_query_string['cond_terms_col_value']) : ''; $cond_terms_operator = !empty($advanced_search_query_string['cond_terms_operator']) ? explode(" AND ", $advanced_search_query_string['cond_terms_operator']) : ''; $cond_terms_post_ids = ''; $cond_cat_post_ids = array(); // array for storing the cat post ids $result_terms_search = ''; $index = 0; foreach ($cond_terms_array as $cond_terms) { $query_advanced_search_taxonomy_id = "SELECT {$wpdb->prefix}term_taxonomy.term_taxonomy_id\r\n FROM {$wpdb->prefix}term_taxonomy\r\n JOIN {$wpdb->prefix}terms\r\n ON ( {$wpdb->prefix}terms.term_id = {$wpdb->prefix}term_taxonomy.term_id)\r\n WHERE " . $cond_terms; $result_advanced_search_taxonomy_id = $wpdb->get_col($query_advanced_search_taxonomy_id); if (!empty($result_advanced_search_taxonomy_id)) { $terms_search_result_flag = $index == sizeof($cond_terms_array) - 1 ? ', ' . $index_search_string : ', 0'; $terms_advanced_search_select = "SELECT DISTINCT {$wpdb->prefix}posts.id " . $terms_search_result_flag; $terms_advanced_search_select .= " ,1"; $terms_advanced_search_from = "FROM {$wpdb->prefix}posts\r\n JOIN {$wpdb->prefix}term_relationships\r\n ON ({$wpdb->prefix}term_relationships.object_id = {$wpdb->prefix}posts.id)"; $terms_advanced_search_where = "WHERE {$wpdb->prefix}term_relationships.term_taxonomy_id IN (" . implode(",", $result_advanced_search_taxonomy_id) . ")"; //Query to find if there are any previous conditions $count_temp_previous_cond = $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp \r\n SET flag = 0\r\n WHERE flag = " . $index_search_string); //Code to handle condition if the ids of previous cond are present in temp table if ($index == 0 && $count_temp_previous_cond > 0 || !empty($result_terms_search)) { $terms_advanced_search_from .= " JOIN {$wpdb->base_prefix}sm_advanced_search_temp\r\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.id)"; $terms_advanced_search_where .= "AND {$wpdb->base_prefix}sm_advanced_search_temp.flag = 0"; } $query_terms_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\r\n (" . $terms_advanced_search_select . " " . $terms_advanced_search_from . " " . $terms_advanced_search_where . " " . ")"; $result_terms_search = $wpdb->query($query_terms_search); //query when no attr cond has been applied $query_terms_search_cat_child = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\r\n ( SELECT {$wpdb->prefix}posts.id " . $terms_search_result_flag . " ,1\r\n FROM {$wpdb->prefix}posts \r\n JOIN {$wpdb->base_prefix}sm_advanced_search_temp\r\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.post_parent)\r\n WHERE {$wpdb->base_prefix}sm_advanced_search_temp.cat_flag = 1 )"; $result_terms_search_cat_child = $wpdb->query($query_terms_search_cat_child); } $index++; } //Query to reset the cat_flag $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp SET cat_flag = 0"); //Code to delete the unwanted post_ids $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp WHERE flag = 0"); } //Condn for postmeta if (!empty($advanced_search_query_string['cond_postmeta'])) { $cond_postmeta_array = explode(" AND ", $advanced_search_query_string['cond_postmeta']); $cond_postmeta_col_name = !empty($advanced_search_query_string['cond_postmeta_col_name']) ? explode(" AND ", $advanced_search_query_string['cond_postmeta_col_name']) : ''; $cond_postmeta_col_value = !empty($advanced_search_query_string['cond_postmeta_col_value']) ? explode(" AND ", $advanced_search_query_string['cond_postmeta_col_value']) : ''; $cond_postmeta_operator = !empty($advanced_search_query_string['cond_postmeta_operator']) ? explode(" AND ", $advanced_search_query_string['cond_postmeta_operator']) : ''; $index = 0; $cond_postmeta_post_ids = ''; $result_postmeta_search = ''; $postmeta_advanced_search_from = ''; $postmeta_advanced_search_where = ''; foreach ($cond_postmeta_array as $cond_postmeta) { $cond_postmeta_col_name1 = !empty($cond_postmeta_col_name[$index]) ? trim($cond_postmeta_col_name[$index]) : ''; $cond_postmeta_col_value1 = !empty($cond_postmeta_col_value[$index]) ? trim($cond_postmeta_col_value[$index]) : ''; $cond_postmeta_operator1 = !empty($cond_postmeta_operator[$index]) ? trim($cond_postmeta_operator[$index]) : ''; $postmeta_search_result_flag = $index == sizeof($cond_postmeta_array) - 1 ? ', ' . $index_search_string : ', 0'; //Query to find if there are any previous conditions $count_temp_previous_cond = $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp \r\n SET flag = 0\r\n WHERE flag = " . $index_search_string); //Code to handle condition if the ids of previous cond are present in temp table if ($index == 0 && $count_temp_previous_cond > 0 || !empty($result_postmeta_search)) { $postmeta_advanced_search_from = " JOIN {$wpdb->base_prefix}sm_advanced_search_temp\r\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}postmeta.post_id)"; $postmeta_advanced_search_where = " AND {$wpdb->base_prefix}sm_advanced_search_temp.flag = 0"; } if ($cond_postmeta_col_name1 == 'weight' || $cond_postmeta_col_name1 == 'height' || $cond_postmeta_col_name1 == 'width' || $cond_postmeta_col_name1 == 'length' || $cond_postmeta_col_name1 == 'local' || $cond_postmeta_col_name1 == 'international') { $postmeta_advanced_search_query = "SELECT DISTINCT temp.post_id " . $postmeta_search_result_flag . " ,0\r\n FROM (SELECT {$wpdb->prefix}postmeta.post_id, SUBSTRING_INDEX( SUBSTRING( {$wpdb->prefix}postmeta.meta_value, (\r\n INSTR( {$wpdb->prefix}postmeta.meta_value, '" . $cond_postmeta_col_name1 . "' ) + LENGTH('" . $cond_postmeta_col_name1 . "') ) +4 ) ,';', 1) \r\n AS " . $cond_postmeta_col_name1 . "\r\n FROM {$wpdb->prefix}postmeta " . $postmeta_advanced_search_from . "\r\n WHERE meta_key LIKE '_wpsc_product_metadata'\r\n {$postmeta_advanced_search_where}\r\n GROUP BY post_id\r\n HAVING " . $cond_postmeta_col_name1 . " " . $cond_postmeta_operator1 . " " . $cond_postmeta_col_value1 . ") AS temp"; } else { $postmeta_advanced_search_query = "SELECT DISTINCT {$wpdb->prefix}postmeta.post_id " . $postmeta_search_result_flag . " ,0\r\n FROM {$wpdb->prefix}postmeta " . $postmeta_advanced_search_from . "\r\n WHERE " . $cond_postmeta . " " . $postmeta_advanced_search_where; } $query_postmeta_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\r\n (" . $postmeta_advanced_search_query . ")"; $result_postmeta_search = $wpdb->query($query_postmeta_search); $index++; } //Query to delete the unwanted post_ids $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp WHERE flag = 0"); } //Cond for posts if (!empty($advanced_search_query_string['cond_posts'])) { $cond_posts_array = explode(" AND ", $advanced_search_query_string['cond_posts']); $index = 0; $cond_posts_post_ids = ''; $result_posts_search = ''; foreach ($cond_posts_array as $cond_posts) { $posts_advanced_search_from = ''; $posts_advanced_search_where = ''; $posts_search_result_flag = $index == sizeof($cond_posts_array) - 1 ? ', ' . $index_search_string : ', 0'; //Query to find if there are any previous conditions $count_temp_previous_cond = $wpdb->query("UPDATE {$wpdb->base_prefix}sm_advanced_search_temp \r\n SET flag = 0\r\n WHERE flag = " . $index_search_string); //Code to handle condition if the ids of previous cond are present in temp table if ($index == 0 && $count_temp_previous_cond > 0 || !empty($result_posts_search)) { $posts_advanced_search_from = " JOIN {$wpdb->base_prefix}sm_advanced_search_temp\r\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.id)"; $posts_advanced_search_where = " AND {$wpdb->base_prefix}sm_advanced_search_temp.flag = 0"; } $query_posts_search = "REPLACE INTO {$wpdb->base_prefix}sm_advanced_search_temp\r\n (SELECT DISTINCT {$wpdb->prefix}posts.id " . $posts_search_result_flag . " ,0\r\n FROM {$wpdb->prefix}posts " . $posts_advanced_search_from . "\r\n WHERE " . $cond_posts . "AND {$wpdb->prefix}posts.post_type = 'wpsc-product'" . $posts_advanced_search_where . ")"; $result_posts_search = $wpdb->query($query_posts_search); $index++; } //Query to delete the unwanted post_ids $wpdb->query("DELETE FROM {$wpdb->base_prefix}sm_advanced_search_temp WHERE flag = 0"); } $index_search_string++; } } //for combined $advanced_search_from = ''; $advanced_search_where = ''; if (!empty($advanced_search_query) || $sm_advanced_search_results_persistent == 1) { $advanced_search_from = " JOIN {$wpdb->base_prefix}sm_advanced_search_temp\r\n ON ({$wpdb->base_prefix}sm_advanced_search_temp.product_id = {$wpdb->prefix}posts.id)"; $advanced_search_where = " AND {$wpdb->base_prefix}sm_advanced_search_temp.flag > 0"; } $from = "FROM {$wpdb->prefix}posts"; $from_export = "FROM {$wpdb->prefix}posts\r\n \t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}postmeta as prod_othermeta ON (prod_othermeta.post_id = {$wpdb->prefix}posts.id and\r\n \t\t\t\t\t\t prod_othermeta.meta_key IN ('_wpsc_price', '_wpsc_special_price', '_wpsc_sku', '_wpsc_stock', '_thumbnail_id','_wpsc_product_metadata') )\r\n LEFT JOIN {$wpdb->prefix}term_relationships AS term_relationships ON ( {$wpdb->prefix}posts.id = term_relationships.object_id )"; $where = "WHERE {$wpdb->prefix}posts.post_status IN {$post_status}\r\n\t\t\t\t\tAND {$wpdb->prefix}posts.post_type = 'wpsc-product'\r\n {$trash_id}"; $group_by = " GROUP BY {$wpdb->prefix}posts.id "; $query = !empty($_POST['func_nm']) && $_POST['func_nm'] == 'exportCsvWpsc' ? "{$select} {$from_export} {$advanced_search_from} {$where} {$advanced_search_where} {$group_by} {$search_condn} {$order_by} {$limit_string};" : "{$select} {$from} {$advanced_search_from} {$where} {$advanced_search_where} {$group_by} {$search_condn} {$order_by} {$limit_string};"; // $query = "$select $from_where $advanced_search_string $group_by $search_condn $order_by $limit_string;"; $records = $wpdb->get_results($query); $num_rows = $wpdb->num_rows; //To get the total count $recordcount_query = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $recordcount_query[0]['count']; if (!empty($advanced_search_query) && !empty($advanced_search_post_ids)) { $advanced_search_post_ids = array_unique($advanced_search_post_ids); $num_records = sizeof($advanced_search_post_ids); } if ($num_rows <= 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', $sm_text_domain); } else { if (empty($_POST['func_nm'])) { $post_ids = array(); foreach ($records as $record) { $post_ids[] = $record->id; } $query_postmeta = "SELECT prod_othermeta.post_id as post_id,\r\n GROUP_CONCAT(prod_othermeta.meta_key order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_key,\r\n GROUP_CONCAT(prod_othermeta.meta_value order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_value\r\n FROM {$wpdb->prefix}postmeta as prod_othermeta \r\n WHERE post_id IN (" . implode(",", $post_ids) . ") AND\r\n prod_othermeta.meta_key IN ('_wpsc_price', '_wpsc_special_price', '_wpsc_sku', '_wpsc_stock', '_thumbnail_id','_wpsc_product_metadata')\r\n GROUP BY post_id"; $records_postmeta = $wpdb->get_results($query_postmeta, 'ARRAY_A'); $products_meta_data = array(); foreach ($records_postmeta as $record_postmeta) { $products_meta_data[$record_postmeta['post_id']] = array(); $products_meta_data[$record_postmeta['post_id']]['prod_othermeta_key'] = $record_postmeta['prod_othermeta_key']; $products_meta_data[$record_postmeta['post_id']]['prod_othermeta_value'] = $record_postmeta['prod_othermeta_value']; } foreach ($records as &$record) { $record->prod_othermeta_key = !empty($products_meta_data[$record->id]['prod_othermeta_key']) ? $products_meta_data[$record->id]['prod_othermeta_key'] : ''; $record->prod_othermeta_value = !empty($products_meta_data[$record->id]['prod_othermeta_value']) ? $products_meta_data[$record->id]['prod_othermeta_value'] : ''; } } foreach ($records as &$record) { $record->post_content = str_replace('"', '\'', $record->post_content); $record->post_excerpt = str_replace('"', '\'', $record->post_excerpt); // if ( intval($record->post_parent) == 0 ) { // $category_terms = wp_get_object_terms($record->id, 'wpsc_product_category', array( 'fields' => 'names', 'orderby' => 'name', 'order' => 'ASC' )); // $record->category = implode( ', ', $category_terms ); // To hide category name from Product's variations // } $product_type = wp_get_object_terms($record->id, 'product_type', array('fields' => 'slugs')); // Code to get the Category Name from the term_taxonomy_id if (isset($category_ids_all[$record->id])) { $category_names = ""; $category_id = explode('###', $category_ids_all[$record->id]); for ($j = 0; $j < sizeof($category_id); $j++) { if (isset($term_taxonomy[$category_id[$j]])) { $category_names .= $term_taxonomy[$category_id[$j]] . ', '; } } if ($category_names != "") { $category_names = substr($category_names, 0, -2); $record->category = $category_names; } } else { $record->category = ""; } $product_type = !is_wp_error($product_type) ? !empty($product_type[0]) ? $product_type[0] : '' : ''; // Code for WP_Error and empty check $record->category = $record->post_parent > 0 && $product_type == 'simple' || $record->post_parent == 0 ? !empty($record->category) ? $record->category : '' : ''; // To hide category name from Product's variations $prod_meta_values = explode('###', $record->prod_othermeta_value); $prod_meta_key = explode('###', $record->prod_othermeta_key); if (count($prod_meta_key) != count($prod_meta_values)) { continue; } $prod_meta_key_values = array_combine($prod_meta_key, $prod_meta_values); if (intval($record->post_parent) > 0) { $title = get_post_field('post_title', $record->post_parent, 'raw'); $variation_terms = wp_get_object_terms($record->id, 'wpsc-variation', array('fields' => 'names', 'orderby' => 'name', 'order' => 'ASC')); $record->post_title = $title . ' - (' . implode(', ', $variation_terms) . ')'; } // $thumbnail = isset( $prod_meta_key_values['_thumbnail_id'] ) ? wp_get_attachment_image_src( $prod_meta_key_values['_thumbnail_id'], $image_size ) : ''; // $record->thumbnail = ( $thumbnail[0] != '' ) ? $thumbnail[0] : false; $thumbnail = wpsc_the_product_thumbnail('', '', $record->id, ''); $record->thumbnail = $thumbnail != '' ? $thumbnail : false; foreach ($prod_meta_key_values as $key => $value) { if (is_serialized($value)) { $unsez_data = unserialize($value); $unsez_data['weight'] = wpsc_convert_weight($unsez_data['weight'], "pound", $unsez_data['weight_unit']); // get the weight by converting it to repsective unit foreach ((array) $unsez_data as $meta_key => $meta_value) { if (is_array($meta_value)) { foreach ($meta_value as $sub_metakey => $sub_metavalue) { in_array($sub_metakey, $view_columns) ? $record->{$sub_metakey} = $sub_metavalue : ''; } } else { in_array($meta_key, $view_columns) ? $record->{$meta_key} = $meta_value : ''; } if ($record->post_parent == 0 && wpsc_product_has_children($record->id)) { if ($show_variation == true) { $record->_wpsc_price = $record->_wpsc_special_price = ' '; } elseif ($show_variation == false) { $parent_price = version_compare(WPSC_VERSION, '3.8.10', '>=') == 1 ? wpsc_product_variation_price_from($record->id) : wpsc_product_variation_price_available($record->id); $record->_wpsc_price = substr($parent_price, 1, strlen($parent_price)); $record->_wpsc_special_price = substr($parent_price, 1, strlen($parent_price)); } } } unset($prod_meta_key_values[$value]); } else { in_array($key, $view_columns) ? $record->{$key} = $value : ''; } } unset($record->prod_othermeta_value); unset($record->prod_meta); unset($record->prod_othermeta_key); } } } elseif ($active_module == 'Orders') { if (SMPRO == true && function_exists('get_packing_slip') && (!empty($_POST['label']) && $_POST['label'] == 'getPurchaseLogs')) { $log_ids_arr = json_decode(stripslashes($_POST['log_ids'])); if (is_array($log_ids_arr)) { $log_ids = implode(', ', $log_ids_arr); } get_packing_slip($log_ids, $log_ids_arr); } else { if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); } if (isset($_POST['fromDate'])) { $from_date = strtotime($_POST['fromDate']); $to_date = strtotime($_POST['toDate']); if ($to_date == 0) { $to_date = strtotime('today'); } // move it forward till the end of day $to_date += 86399; // Swap the two dates if to_date is less than from_date if ($to_date < $from_date) { $temp = $to_date; $to_date = $from_date; $from_date = $temp; } $where = " WHERE wtpl.date BETWEEN '{$from_date}' AND '{$to_date}'"; } //Code to get the variation names $term_taxonomy_id_query = "SELECT term_relationships.object_id AS object_id,\r\n GROUP_CONCAT( DISTINCT terms.name ORDER BY terms.term_id SEPARATOR ',' ) AS variations\r\n FROM {$wpdb->prefix}term_relationships AS term_relationships\r\n LEFT JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy ON ( term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id )\r\n LEFT JOIN {$wpdb->prefix}terms AS terms ON ( terms.term_id = term_taxonomy.term_id )\r\n WHERE term_taxonomy.taxonomy = 'wpsc-variation'\r\n GROUP BY object_id"; $term_taxonomy_id_results = $wpdb->get_results($term_taxonomy_id_query, 'ARRAY_A'); $term_taxonomy_id_rows = $wpdb->num_rows; $term_names = array(); if ($term_taxonomy_id_rows > 0) { foreach ($term_taxonomy_id_results as $term_taxonomy_id_result) { $term_names[$term_taxonomy_id_result['object_id']] = $term_taxonomy_id_result['variations']; } } // LEFT JOIN {$wpdb->prefix}term_relationships AS term_relationships ON ( term_relationships.object_id = wtcc.prodid ) // LEFT JOIN // ( // ) AS terms ON ( terms.object_id = wtcc.prodid ) // CAST( CONCAT( if( products.post_parent > 0, SUBSTRING_INDEX( products.post_title, '(', 1 ), products.post_title ), if( products.post_parent > 0, CONCAT( if( terms.variations IS NULL, '', '(' ), // terms.variations, // if( terms.variations IS NULL, '', ')' ) ), '' ), // if( postmeta.meta_value != '',' [', ' '), // postmeta.meta_value, // if( postmeta.meta_value != '',']', ' ' ) ) // AS CHAR(1000000) ) AS product_details, $product_details = "SELECT wtcc.prodid AS product_id,\r\n postmeta.meta_value as sku,\r\n products.post_title as title,\r\n products.post_parent as post_parent,\r\n wtcc.name AS additional_product_name\r\n FROM " . WPSC_TABLE_CART_CONTENTS . " AS wtcc\r\n LEFT JOIN {$wpdb->prefix}posts AS products ON ( products.ID = wtcc.prodid )\r\n LEFT JOIN {$wpdb->prefix}postmeta AS postmeta ON ( postmeta.post_id = wtcc.prodid AND postmeta.meta_key = '_wpsc_sku' )\r\n GROUP BY product_id"; $results = $wpdb->get_results($product_details, 'ARRAY_A'); $product_details_results = array(); foreach ($results as $result) { $product_details = ''; if ($result['post_parent'] > 0) { $product_details = substr($result['title'], 0, strpos($result['title'], '(')); if (!empty($term_names[$result['product_id']])) { $product_details .= '(' . $term_names[$result['product_id']] . ')'; } } else { $product_details = $result['title']; } if (!empty($result['sku'])) { $product_details .= ' [' . $result['sku'] . ']'; } $product_details_results[$result['product_id']] = !empty($result['product_details']) ? $result['product_details'] : $result['additional_product_name']; } if (!empty($search_on)) { //Query for searching for Shipping_Country $search_condn_country_query = "SELECT DISTINCT wtcl.isocode\r\n FROM " . WPSC_TABLE_CURRENCY_LIST . " AS wtcl\r\n WHERE wtcl.country LIKE '%{$search_on}%'\r\n OR wtcl.continent LIKE '%{$search_on}%'\r\n "; $country_search_ons = $wpdb->get_col($search_condn_country_query); //Query for searching for Shipping_Region $search_condn_region_query = "SELECT DISTINCT wtrt.id\r\n FROM " . WPSC_TABLE_REGION_TAX . " AS wtrt\r\n WHERE wtrt.name LIKE '%{$search_on}%'\r\n "; $region_search_ons = $wpdb->get_col($search_condn_region_query); //Code for handling the search using user email id $email_query = "SELECT ID FROM {$wpdb->users} \r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_rows = $wpdb->num_rows; if ($email_rows > 0) { $email_query1 = "SELECT ID FROM {$wpdb->prefix}wpsc_purchase_logs \r\n WHERE user_ID IN (" . implode(",", $email_result) . ")"; $email_result1 = $wpdb->get_col($email_query1); $email_search = !empty($email_result1) ? " OR wtsfd.log_id IN ( " . implode(',', $email_result1) . " )" : ''; } //Code for handling search using shipping_county OR shipping_Region if (!empty($country_search_ons) || !empty($region_search_ons)) { $search_on_region_country .= " ("; foreach ($country_search_ons as $country_search_on) { $search_on_region_country .= "meta_values LIKE '%###{$country_search_on}###%' OR "; } for ($j = 0; $j < sizeof($region_search_ons); $j++) { $search_on_region_country .= "meta_values LIKE '%###{$region_search_ons[$j]}###%' OR "; } $search_on_region_country = trim($search_on_region_country, ' OR '); $search_on_region_country .= " )"; } else { $search_condn_checkout_form_details_query = " meta_values LIKE '%{$search_on}%' \r\n {$email_search}"; $search_on_region_country = ''; } } else { $search_on_region_country = ''; $search_condn_checkout_form_details_query = ''; } $having = !empty($search_condn_checkout_form_details_query) || !empty($search_on_region_country) ? " HAVING " . $search_condn_checkout_form_details_query . $search_on_region_country : ''; $checkout_form_details_select_query = "SELECT wtsfd.log_id AS purchase_log_id,\r\n GROUP_CONCAT( wtcf.unique_name ORDER BY wtcf.id SEPARATOR '###' ) AS meta_keys,\r\n GROUP_CONCAT( wtsfd.value ORDER BY wtsfd.form_id SEPARATOR '###' ) AS meta_values"; $checkout_form_details_from_query = " FROM " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS wtsfd\r\n LEFT JOIN " . WPSC_TABLE_CHECKOUT_FORMS . " as wtcf \r\n ON (wtsfd.form_id = wtcf.id)\r\n WHERE wtcf.active = 1 \r\n AND wtcf.unique_name IN ('billingfirstname', 'billinglastname', 'billingemail',\r\n 'shippingfirstname', 'shippinglastname', 'shippingaddress',\r\n 'shippingcity', 'shippingstate', 'shippingcountry', 'shippingpostcode','billingphone')\r\n "; $results = $wpdb->get_results($checkout_form_details_select_query . $checkout_form_details_from_query . " GROUP BY purchase_log_id" . $having, 'ARRAY_A'); $result_shipping = $results; $matched_checkout_form_details = false; if (empty($results)) { $results = $wpdb->get_results($checkout_form_details_select_query . $checkout_form_details_from_query . " GROUP BY purchase_log_id", 'ARRAY_A'); } else { $matched_checkout_form_details = true; } $checkout_form_details = array(); foreach ($results as $result) { $checkout_form_details[$result['purchase_log_id']] = array(); $checkout_form_details[$result['purchase_log_id']]['meta_keys'] = $result['meta_keys']; $checkout_form_details[$result['purchase_log_id']]['meta_values'] = $result['meta_values']; } $purchase_logs_select_query = "SELECT wtpl.id, \r\n wtpl.totalprice AS amount, \r\n wtpl.processed AS order_status, \r\n wtpl.user_ID AS customer_id, \r\n wtpl.date AS unixdate,\r\n wtpl.notes,\r\n wtpl.track_id,\r\n GROUP_CONCAT( CAST(wtcc.prodid AS CHAR) ORDER BY wtcc.id SEPARATOR ',' ) AS product_ids,\r\n CONCAT( CAST(SUM(wtcc.quantity) AS CHAR(100)), ' items') AS details"; $purchase_logs_from_query = " FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\n LEFT JOIN " . WPSC_TABLE_CART_CONTENTS . " AS wtcc ON ( wtcc.purchaseid = wtpl.id )\r\n "; if (!empty($search_on)) { $search_condn_purchase_log_ids = get_all_matched_purchase_log_ids($search_on, $checkout_form_details_from_query); $variation_search_query = "SELECT DISTINCT tr.object_id\r\n FROM {$wpdb->prefix}term_relationships AS tr\r\n LEFT JOIN {$wpdb->prefix}term_taxonomy AS tt ON ( tt.term_taxonomy_id = tr.term_taxonomy_id )\r\n LEFT JOIN {$wpdb->prefix}terms AS t ON ( t.term_id = tt.term_id )\r\n WHERE tt.taxonomy = 'wpsc-variation'\r\n AND t.name LIKE '%{$search_on}%'\r\n\r\n "; $object_ids = $wpdb->get_col($variation_search_query); $variation_search_ids = !empty($object_ids) ? " OR wtcc.prodid IN ( " . implode(',', $object_ids) . " )" : ''; $email_query = "SELECT ID FROM {$wpdb->users} \r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_search = !empty($email_result) ? " OR wtpl.user_ID IN ( " . implode(',', $email_result) . " )" : ''; $search_condn_purchase_logs = " AND ( wtpl.id LIKE '%{$search_on}%'\r\n OR totalprice LIKE '%{$search_on}%'\r\n OR notes LIKE '%{$search_on}%'\r\n OR date LIKE '%{$search_on}%'\r\n OR wtpl.track_id LIKE '%{$search_on}%'\r\n OR CASE wtpl.processed\r\n\t\t\t\t\t\t\t\t WHEN 1 THEN 'Incomplete Sale'\r\n\t\t\t\t\t\t\t\t WHEN 2 THEN 'Order Received'\r\n\t\t\t\t\t\t\t\t WHEN 3 THEN 'Accepted Payment'\r\n\t\t\t\t\t\t\t\t WHEN 4 THEN 'Job Dispatched'\r\n\t\t\t\t\t\t\t\t WHEN 5 THEN 'Closed Order'\r\n\t\t\t\t\t\t\t\t ELSE 'Payment Declined'\r\n\t\t\t\t\t\t\t END like '%{$search_on}%'\r\n OR wtcc.name LIKE '%{$search_on}%'\r\n {$variation_search_ids}\r\n {$email_search}\r\n )\r\n {$search_condn_purchase_log_ids}\r\n "; } else { $search_condn_purchase_logs = ''; } $query = $purchase_logs_select_query . $purchase_logs_from_query . $where . $search_condn_purchase_logs . " GROUP BY wtpl.id ORDER BY wtpl.id DESC {$limit_string}"; $results = $wpdb->get_results($query, 'ARRAY_A'); if (empty($results)) { $log_id = array(); for ($i = 0; $i < sizeof($result_shipping); $i++) { $log_id[$i] = $result_shipping[$i]['purchase_log_id']; } if (!empty($log_id)) { $where .= "AND wtpl.id IN(" . implode(",", $log_id) . ")"; $query = $purchase_logs_select_query . $purchase_logs_from_query . $where . " GROUP BY wtpl.id ORDER BY wtpl.id DESC {$limit_string}"; $results = $wpdb->get_results($query, 'ARRAY_A'); } } if (!$is_export) { $orders_count_result = $wpdb->get_results(substr($query, 0, strpos($query, 'LIMIT')), 'ARRAY_A'); $num_records = count($orders_count_result); } else { $num_records = count($results); } $query = "SELECT ID,user_email FROM {$wpdb->users}"; $reg_user = $wpdb->get_results($query, 'ARRAY_A'); for ($i = 0; $i < sizeof($reg_user); $i++) { $user_email[$reg_user[$i]['ID']] = $reg_user[$i]['user_email']; } //To get the total count if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', $sm_text_domain); } else { foreach ($results as $data) { if ($matched_checkout_form_details && !isset($checkout_form_details[$data['id']])) { continue; } $checkout_form_details_keys = explode('###', $checkout_form_details[$data['id']]['meta_keys']); $checkout_form_details_values = explode('###', $checkout_form_details[$data['id']]['meta_values']); if (count($checkout_form_details_keys) == count($checkout_form_details_values)) { $checkout_form_data = array_combine($checkout_form_details_keys, $checkout_form_details_values); $name_emailid[0] = "<font class=blue>" . $checkout_form_data['billingfirstname'] . "</font>"; $name_emailid[1] = "<font class=blue>" . $checkout_form_data['billinglastname'] . "</font>"; $name_emailid[2] = "(" . $checkout_form_data['billingemail'] . ")"; //email comes at 7th position. $data['name'] = implode(' ', $name_emailid); //in front end,splitting is done with this space. $data['date'] = gmdate('Y-m-d H:i:s', $data['unixdate']); //Code to display the order date in GMT format if ($data['customer_id'] > 0) { $data['reg_email'] = $user_email[$data['customer_id']]; } else { $data['reg_email'] = ""; } $prod_ids = explode(',', $data['product_ids']); $products_name = ''; foreach ($prod_ids as $prod_id) { $products_name .= $product_details_results[$prod_id] . ', '; } $data['products_name'] = trim($products_name, ', '); if (!empty($checkout_form_data['shippingstate'])) { $ship_state = $checkout_form_data['shippingstate']; $checkout_form_data['shippingstate'] = $regions_ids[$ship_state] != '' ? $regions_ids[$ship_state] : $ship_state; } if (!empty($checkout_form_data['shippingcountry'])) { $ship_country = $checkout_form_data['shippingcountry']; $checkout_form_data['shippingcountry'] = $country_data[$ship_country] != '' ? $country_data[$ship_country] : $ship_country; } $records[] = !empty($checkout_form_data) ? array_merge($checkout_form_data, $data) : $data; } unset($data); unset($checkout_form_details_keys); unset($checkout_form_details_values); unset($checkout_form_data); } } } } else { //BOF Customer's module if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); } else { $search_on = ""; } $email_form_id = $wpdb->get_var("SELECT id FROM " . WPSC_TABLE_CHECKOUT_FORMS . " WHERE unique_name = 'billingemail'"); $query_max_users_ids = "SELECT GROUP_CONCAT(wtpl.id ORDER BY wtpl.date DESC SEPARATOR ',' ) AS last_order_id,\r\n GROUP_CONCAT(wtpl.totalprice ORDER BY wtpl.date DESC SEPARATOR ',' ) AS _order_total,\r\n DATE_FORMAT( MAX(FROM_UNIXTIME( wtpl.date )),'%b %e %Y' ) AS Last_Order,\r\n COUNT(wtpl.id) AS count_orders,\r\n SUM(wtpl.totalprice) AS total_orders\r\n \r\n FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\n LEFT JOIN " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS customer_email ON ( customer_email.log_id = wtpl.id AND customer_email.form_id = {$email_form_id} )\r\n WHERE wtpl.user_ID > 0\r\n Group by wtpl.user_ID"; $result_max_users_ids = $wpdb->get_results($query_max_users_ids, 'ARRAY_A'); $query_max_guest_ids = "SELECT GROUP_CONCAT(wtpl.id ORDER BY wtpl.date DESC SEPARATOR ',' ) AS last_order_id,\r\n GROUP_CONCAT(wtpl.totalprice ORDER BY wtpl.date DESC SEPARATOR ',' ) AS _order_total,\r\n DATE_FORMAT( MAX(FROM_UNIXTIME( wtpl.date )),'%b %e %Y' ) AS Last_Order,\r\n COUNT(wtpl.id) AS count_orders,\r\n SUM(wtpl.totalprice) AS total_orders\r\n \r\n FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\n LEFT JOIN " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS customer_email ON ( customer_email.log_id = wtpl.id AND customer_email.form_id = {$email_form_id} )\r\n WHERE wtpl.user_ID = 0\r\n GROUP BY customer_email.value\r\n ORDER BY Last_Order DESC"; $result_max_guest_ids = $wpdb->get_results($query_max_guest_ids, 'ARRAY_A'); for ($i = 0; $i < sizeof($result_max_guest_ids); $i++) { $temp_id = !empty($result_max_guest_ids[$i]['last_order_id']) ? explode(",", $result_max_guest_ids[$i]['last_order_id']) : ''; $max_id[$i] = !empty($temp_id[0]) ? $temp_id[0] : 0; $count_orders[$max_id[$i]] = !empty($result_max_guest_ids[$i]['count_orders']) ? $result_max_guest_ids[$i]['count_orders'] : ''; $total_orders[$max_id[$i]] = !empty($result_max_guest_ids[$i]['total_orders']) ? $result_max_guest_ids[$i]['total_orders'] : ''; $order_date[$max_id[$i]] = !empty($result_max_guest_ids[$i]['Last_Order']) ? $result_max_guest_ids[$i]['Last_Order'] : ''; $temp_tot = !empty($result_max_guest_ids[$i]['_order_total']) ? explode(",", $result_max_guest_ids[$i]['_order_total']) : ''; $last_order_total[$max_id[$i]] = !empty($temp_tot[0]) ? $temp_tot[0] : ''; } $j = !empty($max_id) ? sizeof($max_id) : 0; for ($i = 0; $i < sizeof($result_max_users_ids); $i++, $j++) { $temp_id = !empty($result_max_users_ids[$i]['last_order_id']) ? explode(",", $result_max_users_ids[$i]['last_order_id']) : ''; $max_id[$j] = !empty($temp_id) ? $temp_id[0] : 0; $count_orders[$max_id[$j]] = !empty($result_max_users_ids[$i]['count_orders']) ? $result_max_users_ids[$i]['count_orders'] : ''; $total_orders[$max_id[$j]] = !empty($result_max_users_ids[$i]['total_orders']) ? $result_max_users_ids[$i]['total_orders'] : ''; $order_date[$max_id[$j]] = !empty($result_max_users_ids[$i]['Last_Order']) ? $result_max_users_ids[$i]['Last_Order'] : ''; $temp_tot = !empty($result_max_users_ids[$i]['_order_total']) ? explode(",", $result_max_users_ids[$i]['_order_total']) : ''; $last_order_total[$max_id[$j]] = !empty($temp_id) ? $temp_tot[0] : ''; } $total_search = ""; if (!empty($search_on)) { $searched_region = $wpdb->get_col("SELECT code FROM " . WPSC_TABLE_REGION_TAX . " WHERE name LIKE '%{$search_on}%'"); $searched_country = $wpdb->get_col("SELECT isocode FROM " . WPSC_TABLE_CURRENCY_LIST . " WHERE country LIKE '%{$search_on}%' OR continent LIKE '%{$search_on}%'"); $found_country_region = array_merge($searched_region, $searched_country); $found_country_region_having = ''; foreach ($found_country_region as $country_region) { $found_country_region_having .= " OR meta_values LIKE '%{$country_region}%'"; } $email_query = "SELECT ID FROM {$wpdb->users} \r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_rows = $wpdb->num_rows; //Query to get the user ids of the rows whose content matches the search text $user_detail_query = "SELECT DISTINCT user_id FROM {$wpdb->usermeta} \r\n WHERE meta_key IN ('first_name','last_name','wpshpcrt_usr_profile') \r\n AND meta_value LIKE '%{$search_on}%'"; $user_detail_result = $wpdb->get_col($user_detail_query); $user_detail_rows = $wpdb->num_rows; //Code to merge all the user ids into a single array if ($user_detail_rows > 0) { for ($i = 0, $j = sizeof($email_result); $i < sizeof($user_detail_result); $i++, $j++) { $email_result[$j] = $user_detail_result[$i]; } } if ($email_rows > 0 || $user_detail_rows > 0) { $email_query1 = "SELECT ID FROM {$wpdb->prefix}wpsc_purchase_logs \r\n WHERE user_ID IN (" . implode(",", $email_result) . ")"; $email_result1 = $wpdb->get_col($email_query1); $email_search = !empty($email_result1) ? " OR wtsfd.log_id IN ( " . implode(',', $email_result1) . " )" : ''; } } $where_log_id = !empty($max_id) ? 'WHERE log_id IN (' . implode(",", $max_id) . ')' : ''; $max_id_imploded = !empty($max_id) ? implode(",", $max_id) : ''; $orderby_log_id = !empty($max_id) ? "ORDER BY FIND_IN_SET(log_id,'{$max_id_imploded}')" : 'ORDER BY log_id'; $customer_details_query_select = "SELECT wtsfd.log_id AS log_id,\r\n GROUP_CONCAT( wtcf.unique_name ORDER BY wtcf.id SEPARATOR '###' ) AS meta_keys,\r\n GROUP_CONCAT( wtsfd.value ORDER BY wtsfd.form_id SEPARATOR '###' ) AS meta_values\r\n\r\n FROM " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS wtsfd\r\n JOIN " . WPSC_TABLE_CHECKOUT_FORMS . " AS wtcf ON ( wtcf.id = wtsfd.form_id AND wtcf.active = 1 AND wtcf.unique_name IN ('billingfirstname','billinglastname','billingaddress',\r\n 'billingcity','billingstate','billingcountry','billingpostcode',\r\n 'billingemail','billingphone') )\r\n {$where_log_id}\r\n GROUP BY log_id"; if (!empty($search_on)) { $customer_details_query_having = " HAVING meta_values LIKE '%{$search_on}%'\r\n {$found_country_region_having}\r\n {$email_search}\r\n {$total_search} \r\n "; } else { $customer_details_query_having = ''; } $order_by = " {$orderby_log_id} {$limit_string}"; $full_customer_details_query = $customer_details_query_select . $customer_details_query_having . $order_by; $customer_details_results = $wpdb->get_results($full_customer_details_query, 'ARRAY_A'); if (is_null($customer_details_results)) { $full_customer_details_query = $customer_details_query_select . $order_by; $customer_details_results = $wpdb->get_results($full_customer_details_query, 'ARRAY_A'); } if (!$is_export) { $customers_count_result = $wpdb->get_results(substr($full_customer_details_query, 0, strpos($full_customer_details_query, 'LIMIT')), 'ARRAY_A'); $num_records = count($customers_count_result); } else { $num_records = count($customer_details_results); } //Code to get all the users along with their id and email in an array $query = "SELECT users.ID,users.user_email, GROUP_CONCAT(usermeta.meta_value \r\n ORDER BY usermeta.umeta_id SEPARATOR '###' ) AS name\r\n FROM {$wpdb->users} AS users\r\n JOIN {$wpdb->usermeta} AS usermeta ON usermeta.user_id = users.id\r\n WHERE usermeta.meta_key IN ('first_name','last_name','wpshpcrt_usr_profile')\r\n GROUP BY users.id DESC"; $reg_user = $wpdb->get_results($query, 'ARRAY_A'); for ($i = 0; $i < sizeof($reg_user); $i++) { $user_email[$reg_user[$i]['ID']] = $reg_user[$i]['user_email']; $name = explode("###", $reg_user[$i]['name']); $user_fname[$reg_user[$i]['ID']] = $name[0]; $user_lname[$reg_user[$i]['ID']] = $name[1]; if (!empty($name[2])) { $unserialized_detail = unserialize($name[2]); $user_add[$reg_user[$i]['ID']] = $unserialized_detail[4]; $user_city[$reg_user[$i]['ID']] = $unserialized_detail[5]; $user_region[$reg_user[$i]['ID']] = !empty($unserialized_detail[6]) ? $unserialized_detail[6] : ''; $user_country[$reg_user[$i]['ID']] = $unserialized_detail[7][0]; $user_pcode[$reg_user[$i]['ID']] = $unserialized_detail[8]; $user_phone[$reg_user[$i]['ID']] = $unserialized_detail[18]; } } $country_result = $wpdb->get_results("SELECT isocode,country FROM " . WPSC_TABLE_CURRENCY_LIST, 'ARRAY_A'); $country_rows = $wpdb->num_rows; if ($country_rows > 0) { for ($i = 0; $i < sizeof($country_result); $i++) { $country[$country_result[$i]['isocode']] = $country_result[$i]['country']; } } if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', $sm_text_domain); } else { foreach ($customer_details_results as $result) { $meta_keys = explode('###', $result['meta_keys']); $meta_values = explode('###', $result['meta_values']); if (count($meta_keys) == count($meta_values)) { $customer_detail_data[$result['log_id']] = array_combine($meta_keys, $meta_values); } $result['last_order_id'] = $result['log_id']; $result['Last_Order'] = $order_date[$result['log_id']]; $result['_order_total'] = $last_order_total[$result['log_id']]; $result['count_orders'] = $count_orders[$result['log_id']]; $result['total_orders'] = $total_orders[$result['log_id']]; if (empty($customer_detail_data[$result['last_order_id']])) { $num_records--; continue; } $billing_user_details = $customer_detail_data[$result['last_order_id']]; $billing_user_details['billingstate'] = !empty($regions_ids[$billing_user_details['billingstate']]) ? $regions_ids[$billing_user_details['billingstate']] : $billing_user_details['billingstate']; $billing_user_details['billingcountry'] = !empty($country_data[$billing_user_details['billingcountry']]) ? $country_data[$billing_user_details['billingcountry']] : $billing_user_details['billingcountry']; if (SMPRO == false) { $result['Last_Order'] = 'Pro only'; $result['_order_total'] = 'Pro only'; $result['count_orders'] = 'Pro only'; $result['total_orders'] = 'Pro only'; } //Code to get the email for reg users from wp_users table if (!empty($result['id']) && $result['id'] > 0) { $result['email'] = $user_email[$result['id']]; $billing_user_details['billingemail'] = $user_email[$result['id']]; if (!empty($user_fname[$result['id']])) { $billing_user_details['billingfirstname'] = $user_fname[$result['id']]; } if (!empty($user_lname[$result['id']])) { $billing_user_details['billinglastname'] = $user_lname[$result['id']]; } $billing_user_details['billingaddress'] = $user_add[$result['id']]; $billing_user_details['billingcity'] = $user_city[$result['id']]; $billing_user_details['billingstate'] = !empty($regions_ids[$user_region[$result['id']]]) ? $regions_ids[$user_region[$result['id']]] : $user_region[$result['id']]; $billing_user_details['billingcountry'] = $country[$user_country[$result['id']]]; $billing_user_details['billingpostcode'] = $user_pcode[$result['id']]; $billing_user_details['billingphone'] = $user_phone[$result['id']]; } //NOTE: storing old email id in an extra column in record so useful to indentify record with emailid during updates. $result['Old_Email_Id'] = $billing_user_details['billingemail']; $records[] = !empty($billing_user_details) ? array_merge($billing_user_details, $result) : $result; unset($result); unset($meta_keys); unset($meta_values); unset($billing_user_details); } } } if (!isset($_POST['label']) || !empty($_POST['label']) && $_POST['label'] != 'getPurchaseLogs') { $encoded['items'] = !empty($records) ? $records : ''; $encoded['totalCount'] = $num_records; unset($records); return $encoded; } }
function wpsc_stock_control_forms() { global $post, $wpdb, $variations_processor, $wpsc_product_defaults; $product_data = get_post_custom($post->ID); $product_data['meta'] = maybe_unserialize($product_data); foreach ($product_data['meta'] as $meta_key => $meta_value) { $product_data['meta'][$meta_key] = $meta_value[0]; } $product_meta = array(); if (!empty($product_data['_wpsc_product_metadata'])) { $product_meta = maybe_unserialize($product_data["_wpsc_product_metadata"][0]); } // this is to make sure after upgrading to 3.8.9, products will have // "notify_when_none_left" enabled by default if "unpublish_when_none_left" // is enabled. if (!isset($product_meta['notify_when_none_left'])) { $product_meta['notify_when_none_left'] = 0; if (!empty($product_meta['unpublish_when_none_left'])) { $product_meta['notify_when_none_left'] = 1; } } if (!isset($product_meta['unpublish_when_none_left'])) { $product_meta['unpublish_when_none_left'] = ''; } // Display live title if stock is set if (isset($product_data['meta']['_wpsc_stock']) && is_numeric($product_data['meta']['_wpsc_stock'])) { $live_title = '<em id="wpsc_product_stock_metabox_live_title" class="wpsc_metabox_live_title">'; $live_title .= sprintf('<p><span>%s</span> %s</p>', wpsc_format_number($product_data['meta']['_wpsc_stock'], 0), _x('left in stock', 'live preview of stock remaining in admin', 'wp-e-commerce')); $live_title .= '</em>'; echo $live_title; } if (!empty($product_meta['unpublish_when_none_left']) && !isset($product_meta['notify_when_none_left'])) { } ?> <label for="wpsc_sku"><abbr title="<?php esc_attr_e('Stock Keeping Unit', 'wp-e-commerce'); ?> "><?php esc_html_e('SKU:', 'wp-e-commerce'); ?> </abbr></label> <?php if (!isset($product_data['meta']['_wpsc_sku'])) { $product_data['meta']['_wpsc_sku'] = $wpsc_product_defaults['meta']['sku']; } ?> <br /> <input size='32' type='text' class='text' id="wpsc_sku" name='meta[_wpsc_sku]' value='<?php echo esc_html($product_data['meta']['_wpsc_sku']); ?> ' /> <br style="clear:both" /> <?php if (!isset($product_data['meta']['_wpsc_stock'])) { $product_data['meta']['_wpsc_stock'] = ''; } ?> <br /><input class='limited_stock_checkbox' id='add_form_quantity_limited' type='checkbox' value='yes' <?php if (is_numeric($product_data['meta']['_wpsc_stock'])) { echo 'checked="checked"'; } else { echo ''; } ?> name='meta[_wpsc_limited_stock]' /> <label for='add_form_quantity_limited' class='small'><?php esc_html_e('Product has limited stock', 'wp-e-commerce'); ?> </label> <?php if ($post->ID > 0) { if (is_numeric($product_data['meta']['_wpsc_stock'])) { ?> <div class='edit_stock' style='display: block;'> <?php } else { ?> <div class='edit_stock' style='display: none;'><?php } ?> <?php if (wpsc_product_has_children($post->ID)) { ?> <?php $stock = wpsc_variations_stock_remaining($post->ID); ?> <p><?php echo sprintf(__('This product has variations. To edit the quantity, please use the <a href="%s">Variation Controls</a> below.', 'wp-e-commerce'), '#wpsc_product_variation_forms'); ?> </p> <p><?php printf(_n("%s variant item in stock.", "%s variant items in stock.", $stock, 'wp-e-commerce'), $stock); ?> </p> <?php } else { ?> <div style="margin-bottom:20px;"> <label for="stock_limit_quantity"><?php esc_html_e('Quantity in stock', 'wp-e-commerce'); ?> </label> <input type='number' min="0" step="1" style="width:80px; margin-left:50px;" id="stock_limit_quantity" name='meta[_wpsc_stock]' size='3' value='<?php echo absint($product_data['meta']['_wpsc_stock']); ?> ' class='stock_limit_quantity' /> </div> <?php $remaining_quantity = wpsc_get_remaining_quantity($post->ID); $reserved_quantity = $product_data['meta']['_wpsc_stock'] - $remaining_quantity; if ($reserved_quantity) { echo '<p><em>'; printf(_n('%s of them is reserved for pending or recently completed orders.', '%s of them are reserved for pending or recently completed orders.', $reserved_quantity, 'wp-e-commerce'), $reserved_quantity); echo '</em></p>'; } ?> <?php } ?> <p><?php esc_html_e('When stock reduces to zero:', 'wp-e-commerce'); ?> </p> <div class='notify_when_none_left'> <input type='checkbox' id="notify_when_oos" name='meta[_wpsc_product_metadata][notify_when_none_left]' class='notify_when_oos'<?php checked($product_meta['notify_when_none_left']); ?> /> <label for="notify_when_oos"><?php esc_html_e('Notify site owner via email', 'wp-e-commerce'); ?> </label> </div> <div class='unpublish_when_none_left'> <input type='checkbox' id="unpublish_when_oos" name='meta[_wpsc_product_metadata][unpublish_when_none_left]' class='unpublish_when_oos'<?php checked($product_meta['unpublish_when_none_left']); ?> /> <label for="unpublish_when_oos"><?php esc_html_e('Unpublish product from website', 'wp-e-commerce'); ?> </label> </div> </div> <?php } else { ?> <div style='display: none;' class='edit_stock'> <?php esc_html_e('Stock Qty', 'wp-e-commerce'); ?> <input type='text' name='meta[_wpsc_stock]' value='0' size='10' /> <div style='font-size:9px; padding:5px;'> <input type='checkbox' class='notify_when_oos' name='meta[_wpsc_product_metadata][notify_when_none_left]' /> <?php esc_html_e('Email site owner if this Product runs out of stock', 'wp-e-commerce'); ?> <input type='checkbox' class='unpublish_when_oos' name='meta[_wpsc_product_metadata][unpublish_when_none_left]' /> <?php esc_html_e('Set status to Unpublished if this Product runs out of stock', 'wp-e-commerce'); ?> </div> </div> <?php } wp_nonce_field('update', 'wpsc_product_stock_nonce'); }
function get_data_wpsc_38($post, $offset, $limit, $is_export = false) { global $wpdb, $post_status, $parent_sort_id, $order_by; $_POST = $post; // Fix: PHP 5.4 $regions_ids = get_regions_ids(); $country_results = $wpdb->get_results("SELECT isocode, country FROM " . WPSC_TABLE_CURRENCY_LIST, 'ARRAY_A'); $country_data = array(); foreach ($country_results as $country_result) { $country_data[$country_result['isocode']] = $country_result['country']; } //Code to handle the show variations query function variation_query_params() { global $wpdb, $post_status, $parent_sort_id, $order_by; $post_status = "('publish', 'draft','inherit') AND products.ID NOT IN \r\r\n\t\t\t\t\t\t\t( SELECT product.ID FROM {$wpdb->prefix}posts AS product \r\r\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}posts AS product_variation \r\r\n\t\t\t\t\t\t\tON product_variation.ID = product.post_parent \r\r\n\t\t\t\t\t\t\tWHERE product_variation.post_status = 'trash' ) "; $parent_sort_id = " ,if(products.post_parent = 0,products.id,products.post_parent - 1 + (products.id)/pow(10,char_length(cast(products.id as char)))\t) as parent_sort_id"; $order_by = " ORDER BY parent_sort_id desc"; } // getting the active module // $active_module = $_POST ['active_module']; $active_module = isset($_POST['active_module']) ? $_POST['active_module'] : 'Products'; variation_query_params(); if ($is_export === true) { $limit_string = ""; $image_size = "full"; } else { $limit_string = "LIMIT {$offset},{$limit}"; $image_size = "thumbnail"; } $wpdb->query("SET SESSION group_concat_max_len=999999"); // To increase the max length of the Group Concat Functionality $view_columns = json_decode(stripslashes($_POST['viewCols'])); if ($active_module == 'Products') { // <-products $wpsc_default_image = WP_PLUGIN_URL . '/wp-e-commerce/wpsc-theme/wpsc-images/noimage.png'; if (isset($_POST['incVariation']) && $_POST['incVariation'] == 'true') { $show_variation = true; } else { // query params for non-variation products $show_variation = false; $post_status = "('publish', 'draft')"; $parent_sort_id = ''; $order_by = " ORDER BY products.id desc"; } $query_ids = "SELECT `ID` FROM {$wpdb->prefix}posts \r\r\n WHERE `post_type` = 'wpsc-product' \r\r\n AND `post_status` = 'publish' \r\r\n AND `post_parent`=0 \r\r\n AND `ID` NOT IN ( SELECT distinct `post_parent` \r\r\n FROM {$wpdb->prefix}posts WHERE `post_parent`>0)"; $result_ids = $wpdb->get_col($query_ids); $num_ids = $wpdb->num_rows; if ($num_ids > 0) { for ($i = 0; $i < sizeof($result_ids); $i++) { $simple_ids[$result_ids[$i]] = 0; } } // if max-join-size issue occurs $query = "SET SQL_BIG_SELECTS=1;"; $wpdb->query($query); $select = "SELECT SQL_CALC_FOUND_ROWS products.id,\r\r\n\t\t\t\t\tproducts.post_title,\r\r\n\t\t\t\t\tproducts.post_title as post_title_search,\r\r\n\t\t\t\t\tproducts.post_content,\r\r\n\t\t\t\t\tproducts.post_excerpt,\r\r\n\t\t\t\t\tproducts.post_status,\r\r\n\t\t\t\t\tproducts.post_parent,\r\r\n CAST(GROUP_CONCAT(DISTINCT term_relationships.term_taxonomy_id order by term_relationships.term_taxonomy_id SEPARATOR ',') AS CHAR(1000)) AS term_taxonomy_id,\r\r\n\t\t\t\t\tGROUP_CONCAT(prod_othermeta.meta_key order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_key,\r\r\n\t\t\t\t\tGROUP_CONCAT(prod_othermeta.meta_value order by prod_othermeta.meta_id SEPARATOR '###') AS prod_othermeta_value\r\r\n\t\t\t\t\t{$parent_sort_id}"; //Used as an alternative to the SQL_CALC_FOUND_ROWS function of MYSQL Database $select_count = "SELECT COUNT(*) as count"; // To get the count of the number of rows generated from the above select query if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = trim($_POST['searchText']); $count_all_double_quote = substr_count($search_on, '"'); if ($count_all_double_quote > 0) { $search_ons = array_filter(array_map('trim', explode($wpdb->_real_escape('"'), $search_on))); } else { $search_on = $wpdb->_real_escape($search_on); $search_ons = explode(' ', $search_on); } //Code for searching using modified post title $query_title = "SELECT ID FROM {$wpdb->prefix}posts\r\r\n WHERE post_title LIKE '%{$search_on}%'\r\r\n AND post_type = 'wpsc-product'"; $records_title = $wpdb->get_col($query_title); $rows_title = $wpdb->num_rows; if ($rows_title > 0) { $search_title = "OR products.post_parent IN (\r\r\n SELECT ID FROM {$wpdb->prefix}posts\r\r\n WHERE post_title LIKE '%{$search_on}%'\r\r\n AND post_type = 'wpsc-product')"; } else { $search_title = " "; } if (is_array($search_ons) && !empty($search_ons)) { $term_taxonomy_ids = get_term_taxonomy_ids('"' . implode('","', $search_ons) . '"'); $search_condn = " HAVING "; foreach ($search_ons as $search_on) { $search_condn .= " concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR post_content LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR post_excerpt LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR if(post_status = 'publish','Published',post_status) LIKE '{$search_on}%'\r\r\n\t\t\t\t\t\t\t\t\t OR prod_othermeta_value LIKE '%{$search_on}%'\r\r\n {$search_title}\r\r\n\t\t\t\t\t\t\t\t\t OR"; } if (is_array($term_taxonomy_ids) && !empty($term_taxonomy_ids)) { foreach ($term_taxonomy_ids as $term_taxonomy_id) { $search_condn .= " term_taxonomy_id LIKE '%{$term_taxonomy_id}%' OR"; } } $search_condn = substr($search_condn, 0, -2); } else { $term_taxonomy_ids = get_term_taxonomy_ids('"' . $search_on . '"'); $search_condn = " HAVING concat(' ',REPLACE(REPLACE(post_title_search,'(',''),')','')) LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR post_content LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR post_excerpt LIKE '%{$search_on}%'\r\r\n\t\t\t\t\t\t OR if(post_status = 'publish','Published',post_status) LIKE '{$search_on}%'\r\r\n\t\t\t\t\t\t\t\t\t OR prod_othermeta_value LIKE '%{$search_on}%'\r\r\n {$search_title}\r\r\n\t\t\t\t\t\t\t\t\t "; if (is_array($term_taxonomy_ids) && !empty($term_taxonomy_ids)) { foreach ($term_taxonomy_ids as $term_taxonomy_id) { $search_condn .= " OR term_taxonomy_id LIKE '%{$term_taxonomy_id}%'"; } } } } else { $search_condn = ''; } $from_where = "FROM {$wpdb->prefix}posts as products\r\r\n\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}postmeta as prod_othermeta ON (prod_othermeta.post_id = products.id and\r\r\n\t\t\t\t\t\tprod_othermeta.meta_key IN ('_wpsc_price', '_wpsc_special_price', '_wpsc_sku', '_wpsc_stock', '_thumbnail_id','_wpsc_product_metadata') )\r\r\n \r\r\n LEFT JOIN {$wpdb->prefix}term_relationships AS term_relationships ON ( products.id = term_relationships.object_id )\r\r\n\r\r\n\t\t\t\t\t\tWHERE products.post_status IN {$post_status}\r\r\n\t\t\t\t\t\tAND products.post_type = 'wpsc-product'"; $group_by = " GROUP BY products.id "; $query = "{$select} {$from_where} {$group_by} {$search_condn} {$order_by} {$limit_string};"; $records = $wpdb->get_results($query); $num_rows = $wpdb->num_rows; //To get the total count $recordcount_query = $wpdb->get_results('SELECT FOUND_ROWS() as count;', 'ARRAY_A'); $num_records = $recordcount_query[0]['count']; if ($num_rows <= 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { foreach ($records as &$record) { $record->post_content = str_replace('"', '\'', $record->post_content); $record->post_excerpt = str_replace('"', '\'', $record->post_excerpt); if (intval($record->post_parent) == 0) { $category_terms = wp_get_object_terms($record->id, 'wpsc_product_category', array('fields' => 'names', 'orderby' => 'name', 'order' => 'ASC')); $record->category = implode(', ', $category_terms); // To hide category name from Product's variations } $prod_meta_values = explode('###', $record->prod_othermeta_value); $prod_meta_key = explode('###', $record->prod_othermeta_key); if (count($prod_meta_key) != count($prod_meta_values)) { continue; } $prod_meta_key_values = array_combine($prod_meta_key, $prod_meta_values); if (intval($record->post_parent) > 0) { $title = get_post_field('post_title', $record->post_parent, 'raw'); $variation_terms = wp_get_object_terms($record->id, 'wpsc-variation', array('fields' => 'names', 'orderby' => 'name', 'order' => 'ASC')); $record->post_title = $title . ' - (' . implode(', ', $variation_terms) . ')'; } // $thumbnail = isset( $prod_meta_key_values['_thumbnail_id'] ) ? wp_get_attachment_image_src( $prod_meta_key_values['_thumbnail_id'], $image_size ) : ''; // $record->thumbnail = ( $thumbnail[0] != '' ) ? $thumbnail[0] : false; $thumbnail = wpsc_the_product_thumbnail('', '', $record->id, ''); $record->thumbnail = $thumbnail != '' ? $thumbnail : false; foreach ($prod_meta_key_values as $key => $value) { if (is_serialized($value)) { $unsez_data = unserialize($value); $unsez_data['weight'] = wpsc_convert_weight($unsez_data['weight'], "pound", $unsez_data['weight_unit']); // get the weight by converting it to repsective unit foreach ((array) $unsez_data as $meta_key => $meta_value) { if (is_array($meta_value)) { foreach ($meta_value as $sub_metakey => $sub_metavalue) { in_array($sub_metakey, $view_columns) ? $record->{$sub_metakey} = $sub_metavalue : ''; } } else { in_array($meta_key, $view_columns) ? $record->{$meta_key} = $meta_value : ''; } if ($record->post_parent == 0 && wpsc_product_has_children($record->id)) { if ($show_variation == true) { $record->_wpsc_price = $record->_wpsc_special_price = ' '; } elseif ($show_variation == false) { $parent_price = wpsc_product_variation_price_available($record->id); $record->_wpsc_price = substr($parent_price, 1, strlen($parent_price)); $record->_wpsc_special_price = substr($parent_price, 1, strlen($parent_price)); } } } unset($prod_meta_key_values[$value]); } else { in_array($key, $view_columns) ? $record->{$key} = $value : ''; } } unset($record->prod_othermeta_value); unset($record->prod_meta); unset($record->prod_othermeta_key); } } } elseif ($active_module == 'Orders') { if (SMPRO == true && function_exists('get_packing_slip') && $_POST['label'] == 'getPurchaseLogs') { $log_ids_arr = json_decode(stripslashes($_POST['log_ids'])); if (is_array($log_ids_arr)) { $log_ids = implode(', ', $log_ids_arr); } get_packing_slip($log_ids, $log_ids_arr); } else { if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); } if (isset($_POST['fromDate'])) { $from_date = strtotime($_POST['fromDate']); $to_date = strtotime($_POST['toDate']); if ($to_date == 0) { $to_date = strtotime('today'); } // move it forward till the end of day $to_date += 86399; // Swap the two dates if to_date is less than from_date if ($to_date < $from_date) { $temp = $to_date; $to_date = $from_date; $from_date = $temp; } $where = " WHERE wtpl.date BETWEEN '{$from_date}' AND '{$to_date}'"; } $product_details = "SELECT wtcc.prodid AS product_id,\r\r\n CAST( CONCAT( if( products.post_parent > 0, SUBSTRING_INDEX( products.post_title, '(', 1 ), products.post_title ), if( products.post_parent > 0, CONCAT( if( terms.variations IS NULL, '', '(' ), \r\r\n terms.variations, \r\r\n if( terms.variations IS NULL, '', ')' ) ), '' ),\r\r\n if( postmeta.meta_value != '',' [', ' '),\r\r\n postmeta.meta_value,\r\r\n if( postmeta.meta_value != '',']', ' ' ) )\r\r\n AS CHAR(1000000) ) AS product_details,\r\r\n wtcc.name AS additional_product_name\r\r\n FROM " . WPSC_TABLE_CART_CONTENTS . " AS wtcc\r\r\n LEFT JOIN {$wpdb->prefix}posts AS products ON ( products.ID = wtcc.prodid )\r\r\n LEFT JOIN {$wpdb->prefix}postmeta AS postmeta ON ( postmeta.post_id = wtcc.prodid AND postmeta.meta_key = '_wpsc_sku' )\r\r\n LEFT JOIN {$wpdb->prefix}term_relationships AS term_relationships ON ( term_relationships.object_id = wtcc.prodid )\r\r\n LEFT JOIN \r\r\n (SELECT term_relationships.object_id AS object_id,\r\r\n GROUP_CONCAT( DISTINCT terms.name ORDER BY terms.term_id SEPARATOR ',' ) AS variations\r\r\n FROM {$wpdb->prefix}term_relationships AS term_relationships\r\r\n LEFT JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy ON ( term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id )\r\r\n LEFT JOIN {$wpdb->prefix}terms AS terms ON ( terms.term_id = term_taxonomy.term_id )\r\r\n WHERE term_taxonomy.taxonomy = 'wpsc-variation'\r\r\n GROUP BY object_id\r\r\n ) AS terms ON ( terms.object_id = wtcc.prodid )\r\r\n\r\r\n GROUP BY product_id\r\r\n "; $results = $wpdb->get_results($product_details, 'ARRAY_A'); $product_details_results = array(); foreach ($results as $result) { $product_details_results[$result['product_id']] = !empty($result['product_details']) ? $result['product_details'] : $result['additional_product_name']; } if (!empty($search_on)) { //Query for searching for Shipping_Country $search_condn_country_query = "SELECT DISTINCT wtcl.isocode\r\r\n FROM " . WPSC_TABLE_CURRENCY_LIST . " AS wtcl\r\r\n WHERE wtcl.country LIKE '%{$search_on}%'\r\r\n OR wtcl.continent LIKE '%{$search_on}%'\r\r\n "; $country_search_ons = $wpdb->get_col($search_condn_country_query); //Query for searching for Shipping_Region $search_condn_region_query = "SELECT DISTINCT wtrt.id\r\r\n FROM " . WPSC_TABLE_REGION_TAX . " AS wtrt\r\r\n WHERE wtrt.name LIKE '%{$search_on}%'\r\r\n "; $region_search_ons = $wpdb->get_col($search_condn_region_query); //Code for handling the search using user email id $email_query = "SELECT ID FROM {$wpdb->users} \r\r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_rows = $wpdb->num_rows; if ($email_rows > 0) { $email_query1 = "SELECT ID FROM {$wpdb->prefix}wpsc_purchase_logs \r\r\n WHERE user_ID IN (" . implode(",", $email_result) . ")"; $email_result1 = $wpdb->get_col($email_query1); $email_search = !empty($email_result1) ? " OR wtsfd.log_id IN ( " . implode(',', $email_result1) . " )" : ''; } //Code for handling search using shipping_county OR shipping_Region if (!empty($country_search_ons) || !empty($region_search_ons)) { $search_on_region_country .= " ("; foreach ($country_search_ons as $country_search_on) { $search_on_region_country .= "meta_values LIKE '%###{$country_search_on}###%' OR "; } for ($j = 0; $j < sizeof($region_search_ons); $j++) { $search_on_region_country .= "meta_values LIKE '%###{$region_search_ons[$j]}###%' OR "; } $search_on_region_country = trim($search_on_region_country, ' OR '); $search_on_region_country .= " )"; } else { $search_condn_checkout_form_details_query = " meta_values LIKE '%{$search_on}%' \r\r\n {$email_search}"; $search_on_region_country = ''; } } else { $search_on_region_country = ''; $search_condn_checkout_form_details_query = ''; } $having = !empty($search_condn_checkout_form_details_query) || !empty($search_on_region_country) ? " HAVING " . $search_condn_checkout_form_details_query . $search_on_region_country : ''; $checkout_form_details_select_query = "SELECT wtsfd.log_id AS purchase_log_id,\r\r\n GROUP_CONCAT( wtcf.unique_name ORDER BY wtcf.id SEPARATOR '###' ) AS meta_keys,\r\r\n GROUP_CONCAT( wtsfd.value ORDER BY wtsfd.form_id SEPARATOR '###' ) AS meta_values"; $checkout_form_details_from_query = " FROM " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS wtsfd\r\r\n LEFT JOIN " . WPSC_TABLE_CHECKOUT_FORMS . " as wtcf \r\r\n ON (wtsfd.form_id = wtcf.id)\r\r\n WHERE wtcf.active = 1 \r\r\n AND wtcf.unique_name IN ('billingfirstname', 'billinglastname', 'billingemail',\r\r\n 'shippingfirstname', 'shippinglastname', 'shippingaddress',\r\r\n 'shippingcity', 'shippingstate', 'shippingcountry', 'shippingpostcode','billingphone')\r\r\n "; $results = $wpdb->get_results($checkout_form_details_select_query . $checkout_form_details_from_query . " GROUP BY purchase_log_id" . $having, 'ARRAY_A'); $result_shipping = $results; $matched_checkout_form_details = false; if (empty($results)) { $results = $wpdb->get_results($checkout_form_details_select_query . $checkout_form_details_from_query . " GROUP BY purchase_log_id", 'ARRAY_A'); } else { $matched_checkout_form_details = true; } $checkout_form_details = array(); foreach ($results as $result) { $checkout_form_details[$result['purchase_log_id']] = array(); $checkout_form_details[$result['purchase_log_id']]['meta_keys'] = $result['meta_keys']; $checkout_form_details[$result['purchase_log_id']]['meta_values'] = $result['meta_values']; } $purchase_logs_select_query = "SELECT wtpl.id, \r\r\n wtpl.totalprice AS amount, \r\r\n wtpl.processed AS order_status, \r\r\n wtpl.user_ID AS customer_id, \r\r\n wtpl.date AS unixdate,\r\r\n wtpl.notes,\r\r\n wtpl.track_id,\r\r\n GROUP_CONCAT( CAST(wtcc.prodid AS CHAR) ORDER BY wtcc.id SEPARATOR ',' ) AS product_ids,\r\r\n CONCAT( CAST(SUM(wtcc.quantity) AS CHAR(100)), ' items') AS details"; $purchase_logs_from_query = " FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\r\n LEFT JOIN " . WPSC_TABLE_CART_CONTENTS . " AS wtcc ON ( wtcc.purchaseid = wtpl.id )\r\r\n "; if (!empty($search_on)) { $search_condn_purchase_log_ids = get_all_matched_purchase_log_ids($search_on, $checkout_form_details_from_query); $variation_search_query = "SELECT DISTINCT tr.object_id\r\r\n FROM {$wpdb->prefix}term_relationships AS tr\r\r\n LEFT JOIN {$wpdb->prefix}term_taxonomy AS tt ON ( tt.term_taxonomy_id = tr.term_taxonomy_id )\r\r\n LEFT JOIN {$wpdb->prefix}terms AS t ON ( t.term_id = tt.term_id )\r\r\n WHERE tt.taxonomy = 'wpsc-variation'\r\r\n AND t.name LIKE '%{$search_on}%'\r\r\n\r\r\n "; $object_ids = $wpdb->get_col($variation_search_query); $variation_search_ids = !empty($object_ids) ? " OR wtcc.prodid IN ( " . implode(',', $object_ids) . " )" : ''; $email_query = "SELECT ID FROM {$wpdb->users} \r\r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_search = !empty($email_result) ? " OR wtpl.user_ID IN ( " . implode(',', $email_result) . " )" : ''; $search_condn_purchase_logs = " AND ( wtpl.id LIKE '%{$search_on}%'\r\r\n OR totalprice LIKE '%{$search_on}%'\r\r\n OR notes LIKE '%{$search_on}%'\r\r\n OR date LIKE '%{$search_on}%'\r\r\n OR wtpl.track_id LIKE '%{$search_on}%'\r\r\n OR CASE wtpl.processed\r\r\n\t\t\t\t\t\t\t\t WHEN 1 THEN 'Incomplete Sale'\r\r\n\t\t\t\t\t\t\t\t WHEN 2 THEN 'Order Received'\r\r\n\t\t\t\t\t\t\t\t WHEN 3 THEN 'Accepted Payment'\r\r\n\t\t\t\t\t\t\t\t WHEN 4 THEN 'Job Dispatched'\r\r\n\t\t\t\t\t\t\t\t WHEN 5 THEN 'Closed Order'\r\r\n\t\t\t\t\t\t\t\t ELSE 'Payment Declined'\r\r\n\t\t\t\t\t\t\t END like '%{$search_on}%'\r\r\n OR wtcc.name LIKE '%{$search_on}%'\r\r\n {$variation_search_ids}\r\r\n {$email_search}\r\r\n )\r\r\n {$search_condn_purchase_log_ids}\r\r\n "; } else { $search_condn_purchase_logs = ''; } $query = $purchase_logs_select_query . $purchase_logs_from_query . $where . $search_condn_purchase_logs . " GROUP BY wtpl.id ORDER BY wtpl.id DESC {$limit_string}"; $results = $wpdb->get_results($query, 'ARRAY_A'); if (empty($results)) { for ($i = 0; $i < sizeof($result_shipping); $i++) { $log_id[$i] = $result_shipping[$i]['purchase_log_id']; } if (!is_null($log_id)) { $where .= "AND wtpl.id IN(" . implode(",", $log_id) . ")"; $query = $purchase_logs_select_query . $purchase_logs_from_query . $where . " GROUP BY wtpl.id ORDER BY wtpl.id DESC {$limit_string}"; $results = $wpdb->get_results($query, 'ARRAY_A'); } } if (!$is_export) { $orders_count_result = $wpdb->get_results(substr($query, 0, strpos($query, 'LIMIT')), 'ARRAY_A'); $num_records = count($orders_count_result); } else { $num_records = count($results); } $query = "SELECT ID,user_email FROM {$wpdb->users}"; $reg_user = $wpdb->get_results($query, 'ARRAY_A'); for ($i = 0; $i < sizeof($reg_user); $i++) { $user_email[$reg_user[$i]['ID']] = $reg_user[$i]['user_email']; } //To get the total count if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { foreach ($results as $data) { if ($matched_checkout_form_details && !isset($checkout_form_details[$data['id']])) { continue; } $checkout_form_details_keys = explode('###', $checkout_form_details[$data['id']]['meta_keys']); $checkout_form_details_values = explode('###', $checkout_form_details[$data['id']]['meta_values']); if (count($checkout_form_details_keys) == count($checkout_form_details_values)) { $checkout_form_data = array_combine($checkout_form_details_keys, $checkout_form_details_values); $name_emailid[0] = "<font class=blue>" . $checkout_form_data['billingfirstname'] . "</font>"; $name_emailid[1] = "<font class=blue>" . $checkout_form_data['billinglastname'] . "</font>"; $name_emailid[2] = "(" . $checkout_form_data['billingemail'] . ")"; //email comes at 7th position. $data['name'] = implode(' ', $name_emailid); //in front end,splitting is done with this space. $data['date'] = gmdate('Y-m-d H:i:s', $data['unixdate']); //Code to display the order date in GMT format if ($data['customer_id'] > 0) { $data['reg_email'] = $user_email[$data['customer_id']]; } else { $data['reg_email'] = ""; } $prod_ids = explode(',', $data['product_ids']); $products_name = ''; foreach ($prod_ids as $prod_id) { $products_name .= $product_details_results[$prod_id] . ', '; } $data['products_name'] = trim($products_name, ', '); if (!empty($checkout_form_data['shippingstate'])) { $ship_state = $checkout_form_data['shippingstate']; $checkout_form_data['shippingstate'] = $regions_ids[$ship_state] != '' ? $regions_ids[$ship_state] : $ship_state; } if (!empty($checkout_form_data['shippingcountry'])) { $ship_country = $checkout_form_data['shippingcountry']; $checkout_form_data['shippingcountry'] = $country_data[$ship_country] != '' ? $country_data[$ship_country] : $ship_country; } $records[] = !empty($checkout_form_data) ? array_merge($checkout_form_data, $data) : $data; } unset($data); unset($checkout_form_details_keys); unset($checkout_form_details_values); unset($checkout_form_data); } } } } else { //BOF Customer's module if (isset($_POST['searchText']) && $_POST['searchText'] != '') { $search_on = $wpdb->_real_escape(trim($_POST['searchText'])); } else { $search_on = ""; } $email_form_id = $wpdb->get_var("SELECT id FROM " . WPSC_TABLE_CHECKOUT_FORMS . " WHERE unique_name = 'billingemail'"); $query_max_users_ids = "SELECT GROUP_CONCAT(wtpl.id ORDER BY wtpl.date DESC SEPARATOR ',' ) AS last_order_id,\r\r\n GROUP_CONCAT(wtpl.totalprice ORDER BY wtpl.date DESC SEPARATOR ',' ) AS _order_total,\r\r\n DATE_FORMAT( MAX(FROM_UNIXTIME( wtpl.date )),'%b %e %Y' ) AS Last_Order,\r\r\n COUNT(wtpl.id) AS count_orders,\r\r\n SUM(wtpl.totalprice) AS total_orders\r\r\n \r\r\n FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\r\n LEFT JOIN " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS customer_email ON ( customer_email.log_id = wtpl.id AND customer_email.form_id = {$email_form_id} )\r\r\n WHERE wtpl.user_ID > 0\r\r\n Group by wtpl.user_ID"; $result_max_users_ids = $wpdb->get_results($query_max_users_ids, 'ARRAY_A'); $query_max_guest_ids = "SELECT GROUP_CONCAT(wtpl.id ORDER BY wtpl.date DESC SEPARATOR ',' ) AS last_order_id,\r\r\n GROUP_CONCAT(wtpl.totalprice ORDER BY wtpl.date DESC SEPARATOR ',' ) AS _order_total,\r\r\n DATE_FORMAT( MAX(FROM_UNIXTIME( wtpl.date )),'%b %e %Y' ) AS Last_Order,\r\r\n COUNT(wtpl.id) AS count_orders,\r\r\n SUM(wtpl.totalprice) AS total_orders\r\r\n \r\r\n FROM " . WPSC_TABLE_PURCHASE_LOGS . " AS wtpl\r\r\n LEFT JOIN " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS customer_email ON ( customer_email.log_id = wtpl.id AND customer_email.form_id = {$email_form_id} )\r\r\n WHERE wtpl.user_ID = 0\r\r\n GROUP BY customer_email.value\r\r\n ORDER BY Last_Order DESC"; $result_max_guest_ids = $wpdb->get_results($query_max_guest_ids, 'ARRAY_A'); for ($i = 0; $i < sizeof($result_max_guest_ids); $i++) { $temp_id = explode(",", $result_max_guest_ids[$i]['last_order_id']); $max_id[$i] = $temp_id[0]; $count_orders[$max_id[$i]] = $result_max_guest_ids[$i]['count_orders']; $total_orders[$max_id[$i]] = $result_max_guest_ids[$i]['total_orders']; $order_date[$max_id[$i]] = $result_max_guest_ids[$i]['Last_Order']; $temp_tot = explode(",", $result_max_guest_ids[$i]['_order_total']); $last_order_total[$max_id[$i]] = $temp_tot[0]; } $j = sizeof($max_id); for ($i = 0; $i < sizeof($result_max_users_ids); $i++, $j++) { $temp_id = explode(",", $result_max_users_ids[$i]['last_order_id']); $max_id[$j] = $temp_id[0]; $count_orders[$max_id[$j]] = $result_max_users_ids[$i]['count_orders']; $total_orders[$max_id[$j]] = $result_max_users_ids[$i]['total_orders']; $order_date[$max_id[$j]] = $result_max_users_ids[$i]['Last_Order']; $temp_tot = explode(",", $result_max_users_ids[$i]['_order_total']); $last_order_total[$max_id[$j]] = $temp_tot[0]; } $total_search = ""; if (!empty($search_on)) { $searched_region = $wpdb->get_col("SELECT code FROM " . WPSC_TABLE_REGION_TAX . " WHERE name LIKE '%{$search_on}%'"); $searched_country = $wpdb->get_col("SELECT isocode FROM " . WPSC_TABLE_CURRENCY_LIST . " WHERE country LIKE '%{$search_on}%' OR continent LIKE '%{$search_on}%'"); $found_country_region = array_merge($searched_region, $searched_country); $found_country_region_having = ''; foreach ($found_country_region as $country_region) { $found_country_region_having .= " OR meta_values LIKE '%{$country_region}%'"; } $email_query = "SELECT ID FROM {$wpdb->users} \r\r\n WHERE user_email LIKE '%{$search_on}%'"; $email_result = $wpdb->get_col($email_query); $email_rows = $wpdb->num_rows; //Query to get the user ids of the rows whose content matches the search text $user_detail_query = "SELECT DISTINCT user_id FROM {$wpdb->usermeta} \r\r\n WHERE meta_key IN ('first_name','last_name','wpshpcrt_usr_profile') \r\r\n AND meta_value LIKE '%{$search_on}%'"; $user_detail_result = $wpdb->get_col($user_detail_query); $user_detail_rows = $wpdb->num_rows; //Code to merge all the user ids into a single array if ($user_detail_rows > 0) { for ($i = 0, $j = sizeof($email_result); $i < sizeof($user_detail_result); $i++, $j++) { $email_result[$j] = $user_detail_result[$i]; } } if ($email_rows > 0 || $user_detail_rows > 0) { $email_query1 = "SELECT ID FROM {$wpdb->prefix}wpsc_purchase_logs \r\r\n WHERE user_ID IN (" . implode(",", $email_result) . ")"; $email_result1 = $wpdb->get_col($email_query1); $email_search = !empty($email_result1) ? " OR wtsfd.log_id IN ( " . implode(',', $email_result1) . " )" : ''; } } $customer_details_query_select = "SELECT wtsfd.log_id AS log_id,\r\r\n GROUP_CONCAT( wtcf.unique_name ORDER BY wtcf.id SEPARATOR '###' ) AS meta_keys,\r\r\n GROUP_CONCAT( wtsfd.value ORDER BY wtsfd.form_id SEPARATOR '###' ) AS meta_values\r\r\n\r\r\n FROM " . WPSC_TABLE_SUBMITED_FORM_DATA . " AS wtsfd\r\r\n JOIN " . WPSC_TABLE_CHECKOUT_FORMS . " AS wtcf ON ( wtcf.id = wtsfd.form_id AND wtcf.active = 1 AND wtcf.unique_name IN ('billingfirstname','billinglastname','billingaddress',\r\r\n 'billingcity','billingstate','billingcountry','billingpostcode',\r\r\n 'billingemail','billingphone') )\r\r\n WHERE log_id IN (" . implode(",", $max_id) . ")\r\r\n GROUP BY log_id"; if (!empty($search_on)) { $customer_details_query_having = " HAVING meta_values LIKE '%{$search_on}%'\r\r\n {$found_country_region_having}\r\r\n {$email_search}\r\r\n {$total_search} \r\r\n "; } else { $customer_details_query_having = ''; } $order_by = " ORDER BY FIND_IN_SET(log_id,'" . implode(",", $max_id) . "') {$limit_string}"; $full_customer_details_query = $customer_details_query_select . $customer_details_query_having . $order_by; $customer_details_results = $wpdb->get_results($full_customer_details_query, 'ARRAY_A'); if (is_null($customer_details_results)) { $full_customer_details_query = $customer_details_query_select . $order_by; $customer_details_results = $wpdb->get_results($full_customer_details_query, 'ARRAY_A'); } if (!$is_export) { $customers_count_result = $wpdb->get_results(substr($full_customer_details_query, 0, strpos($full_customer_details_query, 'LIMIT')), 'ARRAY_A'); $num_records = count($customers_count_result); } else { $num_records = count($customer_details_results); } //Code to get all the users along with their id and email in an array $query = "SELECT users.ID,users.user_email, GROUP_CONCAT(usermeta.meta_value \r\r\n ORDER BY usermeta.umeta_id SEPARATOR '###' ) AS name\r\r\n FROM {$wpdb->users} AS users\r\r\n JOIN {$wpdb->usermeta} AS usermeta ON usermeta.user_id = users.id\r\r\n WHERE usermeta.meta_key IN ('first_name','last_name','wpshpcrt_usr_profile')\r\r\n GROUP BY users.id DESC"; $reg_user = $wpdb->get_results($query, 'ARRAY_A'); for ($i = 0; $i < sizeof($reg_user); $i++) { $user_email[$reg_user[$i]['ID']] = $reg_user[$i]['user_email']; $name = explode("###", $reg_user[$i]['name']); $user_fname[$reg_user[$i]['ID']] = $name[0]; $user_lname[$reg_user[$i]['ID']] = $name[1]; if (!is_null($name[2])) { $unserialized_detail = unserialize($name[2]); $user_add[$reg_user[$i]['ID']] = $unserialized_detail[4]; $user_city[$reg_user[$i]['ID']] = $unserialized_detail[5]; $user_region[$reg_user[$i]['ID']] = $unserialized_detail[6]; $user_country[$reg_user[$i]['ID']] = $unserialized_detail[7][0]; $user_pcode[$reg_user[$i]['ID']] = $unserialized_detail[8]; $user_phone[$reg_user[$i]['ID']] = $unserialized_detail[18]; } } $country_result = $wpdb->get_results("SELECT isocode,country FROM " . WPSC_TABLE_CURRENCY_LIST, 'ARRAY_A'); $country_rows = $wpdb->num_rows; if ($country_rows > 0) { for ($i = 0; $i < sizeof($country_result); $i++) { $country[$country_result[$i]['isocode']] = $country_result[$i]['country']; } } if ($num_records == 0) { $encoded['totalCount'] = ''; $encoded['items'] = ''; $encoded['msg'] = __('No Records Found', 'smart-manager'); } else { foreach ($customer_details_results as $result) { $meta_keys = explode('###', $result['meta_keys']); $meta_values = explode('###', $result['meta_values']); if (count($meta_keys) == count($meta_values)) { $customer_detail_data[$result['log_id']] = array_combine($meta_keys, $meta_values); } $result['last_order_id'] = $result['log_id']; $result['Last_Order'] = $order_date[$result['log_id']]; $result['_order_total'] = $last_order_total[$result['log_id']]; $result['count_orders'] = $count_orders[$result['log_id']]; $result['total_orders'] = $total_orders[$result['log_id']]; if (empty($customer_detail_data[$result['last_order_id']])) { $num_records--; continue; } $billing_user_details = $customer_detail_data[$result['last_order_id']]; $billing_user_details['billingstate'] = !empty($regions_ids[$billing_user_details['billingstate']]) ? $regions_ids[$billing_user_details['billingstate']] : $billing_user_details['billingstate']; $billing_user_details['billingcountry'] = !empty($country_data[$billing_user_details['billingcountry']]) ? $country_data[$billing_user_details['billingcountry']] : $billing_user_details['billingcountry']; if (SMPRO == false) { $result['Last_Order'] = 'Pro only'; $result['_order_total'] = 'Pro only'; $result['count_orders'] = 'Pro only'; $result['total_orders'] = 'Pro only'; } //Code to get the email for reg users from wp_users table if ($result['id'] > 0) { $result['email'] = $user_email[$result['id']]; $billing_user_details['billingemail'] = $user_email[$result['id']]; if (!empty($user_fname[$result['id']])) { $billing_user_details['billingfirstname'] = $user_fname[$result['id']]; } if (!empty($user_lname[$result['id']])) { $billing_user_details['billinglastname'] = $user_lname[$result['id']]; } $billing_user_details['billingaddress'] = $user_add[$result['id']]; $billing_user_details['billingcity'] = $user_city[$result['id']]; $billing_user_details['billingstate'] = !empty($regions_ids[$user_region[$result['id']]]) ? $regions_ids[$user_region[$result['id']]] : $user_region[$result['id']]; $billing_user_details['billingcountry'] = $country[$user_country[$result['id']]]; $billing_user_details['billingpostcode'] = $user_pcode[$result['id']]; $billing_user_details['billingphone'] = $user_phone[$result['id']]; } //NOTE: storing old email id in an extra column in record so useful to indentify record with emailid during updates. $result['Old_Email_Id'] = $billing_user_details['billingemail']; $records[] = !empty($billing_user_details) ? array_merge($billing_user_details, $result) : $result; unset($result); unset($meta_keys); unset($meta_values); unset($billing_user_details); } } } if (!isset($_POST['label']) && $_POST['label'] != 'getPurchaseLogs') { $encoded['items'] = $records; $encoded['totalCount'] = $num_records; unset($records); return $encoded; } }
/** * Product Specials Widget content function * * Displays the latest products. * * Changes made in 3.8 that may affect users: * * 1. The product title link text does now not have a bold tag, it should be styled via css. * 2. <br /> tags have been ommitted. Padding and margins should be applied via css. * 3. Each product is enclosed in a <div> with a 'wpec-special-product' class. * 4. The product list is enclosed in a <div> with a 'wpec-special-products' class. * 5. Function now expect a single paramter with an array of options (used to be a string which prepended the output). */ function wpsc_specials($args = null, $instance) { global $wpdb; $args = wp_parse_args((array) $args, array('number' => 5)); if (!($number = (int) $instance['number'])) { $number = 5; } $show_thumbnails = isset($instance['show_thumbnails']) ? (bool) $instance['show_thumbnails'] : false; $show_description = isset($instance['show_description']) ? (bool) $instance['show_description'] : false; $show_discount = isset($instance['show_discount']) ? (bool) $instance['show_discount'] : false; $show_old_price = isset($instance['show_old_price']) ? (bool) $instance['show_old_price'] : false; $args = array('post_type' => 'wpsc-product', 'ignore_sticky_posts' => 1, 'post_status' => 'publish', 'post_parent' => 0, 'posts_per_page' => $number, 'no_found_rows' => true); add_filter('posts_join', '_wpsc_filter_special_widget_join'); add_filter('posts_where', '_wpsc_filter_special_widget_where'); $special_products = new WP_Query($args); remove_filter('posts_join', '_wpsc_filter_special_widget_join'); remove_filter('posts_where', '_wpsc_filter_special_widget_where'); if (!$special_products->post_count) { echo apply_filters('wpsc_specials_widget_no_items_message', __('We currently have no items on special.', 'wp-e-commerce')); return; } $product_ids = array(); while ($special_products->have_posts()) { $special_products->the_post(); ?> <h4><strong><a class="wpsc_product_title" href="<?php echo esc_url(wpsc_product_url(wpsc_the_product_id(), false)); ?> "><?php echo esc_html(wpsc_the_product_title()); ?> </a></h4></strong> <?php if ($show_description) { ?> <div class="wpsc-special-description"> <?php echo wpsc_the_product_description(); ?> </div> <?php } // close show description if (!in_array(wpsc_the_product_id(), $product_ids)) { $product_ids[] = wpsc_the_product_id(); $has_children = wpsc_product_has_children(get_the_ID()); $width = get_option('product_image_width'); $height = get_option('product_image_height'); if ($show_thumbnails) { if (wpsc_the_product_thumbnail()) { ?> <a rel="<?php echo str_replace(array(" ", '"', "'", '"', '''), array("_", "", "", "", ''), wpsc_the_product_title()); ?> " href="<?php echo esc_url(wpsc_the_product_permalink()); ?> "><img class="product_image" id="product_image_<?php echo esc_attr(wpsc_the_product_id()); ?> " alt="<?php echo esc_attr(wpsc_the_product_title()); ?> " title="<?php echo esc_attr(wpsc_the_product_title()); ?> " src="<?php echo esc_url(wpsc_the_product_thumbnail($width, $height)); ?> "/></a> <?php } else { ?> <a href="<?php esc_url(wpsc_the_product_permalink()); ?> "><img class="no-image" id="product_image_<?php echo esc_attr(wpsc_the_product_id()); ?> " alt="<?php echo esc_attr(wpsc_the_product_title()); ?> " title="<?php echo esc_attr(wpsc_the_product_title()); ?> " src="<?php echo esc_url(WPSC_CORE_THEME_PATH . '/wpsc-images/noimage.png'); ?> " width="<?php echo esc_attr($width); ?> " height="<?php echo esc_attr($height); ?> " /></a> <?php } ?> <br /> <?php } // close show thumbnails ?> <div id="special_product_price_<?php echo esc_attr(wpsc_the_product_id()); ?> "> <?php wpsc_the_product_price_display(array('output_old_price' => $show_old_price, 'output_you_save' => $show_discount)); ?> </div><br /> <?php } } wp_reset_postdata(); }
function wpsc_stock_control_forms() { global $post, $wpdb, $variations_processor, $wpsc_product_defaults; $product_data = get_post_custom($post->ID); $product_data['meta'] = maybe_unserialize($product_data); foreach ($product_data['meta'] as $meta_key => $meta_value) { $product_data['meta'][$meta_key] = $meta_value[0]; } $product_meta = array(); if (!empty($product_data["_wpsc_product_metadata"])) { $product_meta = maybe_unserialize($product_data["_wpsc_product_metadata"][0]); } // this is to make sure after upgrading to 3.8.9, products will have // "notify_when_none_left" enabled by default if "unpublish_when_none_left" // is enabled. if (!isset($product_meta['notify_when_none_left'])) { $product_meta['notify_when_none_left'] = 0; if (!empty($product_meta['unpublish_when_none_left'])) { $product_meta['notify_when_none_left'] = 1; } } if (!isset($product_meta['unpublish_when_none_left'])) { $product_meta['unpublish_when_none_left'] = ''; } if (!empty($product_meta['unpublish_when_none_left']) && !isset($product_meta['notify_when_none_left'])) { } ?> <label for="wpsc_sku"><abbr title="<?php esc_attr_e('Stock Keeping Unit', 'wpsc'); ?> "><?php esc_html_e('SKU:', 'wpsc'); ?> </abbr></label> <?php if (!isset($product_data['meta']['_wpsc_sku'])) { $product_data['meta']['_wpsc_sku'] = $wpsc_product_defaults['meta']['sku']; } ?> <br /> <input size='32' type='text' class='text' id="wpsc_sku" name='meta[_wpsc_sku]' value='<?php echo esc_html($product_data['meta']['_wpsc_sku']); ?> ' /> <br style="clear:both" /> <?php if (!isset($product_data['meta']['_wpsc_stock'])) { $product_data['meta']['_wpsc_stock'] = ''; } ?> <br /><input class='limited_stock_checkbox' id='add_form_quantity_limited' type='checkbox' value='yes' <?php if (is_numeric($product_data['meta']['_wpsc_stock'])) { echo 'checked="checked"'; } else { echo ''; } ?> name='meta[_wpsc_limited_stock]' /> <label for='add_form_quantity_limited' class='small'><?php esc_html_e('I have limited stock for this Product', 'wpsc'); ?> </label> <?php if ($post->ID > 0) { if (is_numeric($product_data['meta']['_wpsc_stock'])) { ?> <div class='edit_stock' style='display: block;'> <?php } else { ?> <div class='edit_stock' style='display: none;'><?php } ?> <?php if (wpsc_product_has_children($post->ID)) { ?> <?php $stock = wpsc_variations_stock_remaining($post->ID); ?> <p><?php esc_html_e('This Product has variations, to edit the quantity please use the Variation Controls below.', 'wpsc'); ?> </p> <p><?php printf(_n("%s variant item in stock.", "%s variant items in stock.", $stock, 'wpsc'), $stock); ?> </p> <?php } else { ?> <label for="stock_limit_quantity"><?php esc_html_e('Quantity:', 'wpsc'); ?> </label> <input type='text' id="stock_limit_quantity" name='meta[_wpsc_stock]' size='3' value='<?php echo $product_data['meta']['_wpsc_stock']; ?> ' class='stock_limit_quantity' /> <?php $remaining_quantity = wpsc_get_remaining_quantity($post->ID); $reserved_quantity = $product_data['meta']['_wpsc_stock'] - $remaining_quantity; if ($reserved_quantity) { ?> <p><em> <?php printf(_n('%s of them is reserved for pending or recently completed orders.', '%s of them are reserved for pending or recently completed orders.', $reserved_quantity, 'wpsc'), $reserved_quantity); ?> </em></p> <?php } ?> <?php } ?> <div class='notify_when_none_left'> <input type='checkbox' id="notify_when_oos" name='meta[_wpsc_product_metadata][notify_when_none_left]' class='notify_when_oos'<?php checked($product_meta['notify_when_none_left']); ?> /> <label for="notify_when_oos"><?php esc_html_e('Notify site owner if stock runs out', 'wpsc'); ?> </label> </div> <div class='unpublish_when_none_left'> <input type='checkbox' id="unpublish_when_oos" name='meta[_wpsc_product_metadata][unpublish_when_none_left]' class='unpublish_when_oos'<?php checked($product_meta['unpublish_when_none_left']); ?> /> <label for="unpublish_when_oos"><?php esc_html_e('Unpublish this Product if stock runs out', 'wpsc'); ?> </label> <p><em><?php esc_html_e('If stock runs out, this Product will not be available on the shop unless you untick this box or add more stock.', 'wpsc'); ?> </em></p> </div> </div> <?php } else { ?> <div style='display: none;' class='edit_stock'> <?php esc_html_e('Stock Qty', 'wpsc'); ?> <input type='text' name='meta[_wpsc_stock]' value='0' size='10' /> <div style='font-size:9px; padding:5px;'> <input type='checkbox' class='notify_when_oos' name='meta[_wpsc_product_metadata][notify_when_none_left]' /> <?php esc_html_e('Email site owner if this Product runs out of stock', 'wpsc'); ?> <input type='checkbox' class='unpublish_when_oos' name='meta[_wpsc_product_metadata][unpublish_when_none_left]' /> <?php esc_html_e('Set status to Unpublished if this Product runs out of stock', 'wpsc'); ?> </div> </div><?php } }
function wpsc_check_weight($state, $product) { global $wpdb; $custom_shipping = (array) get_option('custom_shipping_options'); $has_no_weight = false; $shipping_modules = array(); $product_meta = get_product_meta($product->ID, 'product_metadata', true); if (!$product->post_parent && wpsc_product_has_children($product->ID)) { return $state; } // only do anything if UPS is on and shipping is used if (array_search('ups', $custom_shipping) !== false) { $shipping_modules[] = 'UPS'; } if (array_search('weightrate', $custom_shipping) !== false) { $shipping_modules[] = 'Weight Rate'; } if (array_search('usps', $custom_shipping) !== false) { $shipping_modules[] = 'Weight Rate'; } if (empty($product_meta['no_shipping']) && !empty($shipping_modules)) { if ($product_meta['weight'] == 0) { // otherwise, use the weight from the products list table $has_no_weight = true; } if ($has_no_weight === true) { $state['state'] = true; $state['messages'][] = implode(',', $shipping_modules) . __(' does not support products without a weight set. Please either disable shipping for this product or give it a weight', 'wpsc'); } } return array('state' => $state['state'], 'messages' => $state['messages']); }
/** * wpsc_save_quickedit_box function * Saves input for the various meta in the quick edit boxes * * @todo UI * @todo Data validation / sanitization / security * @todo AJAX should probably return weight unit * @return $post_id (int) Post ID */ function wpsc_save_quickedit_box($post_id) { global $current_screen; if (defined('DOING_AUTOSAVE') && DOING_AUTOSAVE || empty($current_screen) || $current_screen->id != 'edit-wpsc-product' || !defined('DOING_AJAX') || !DOING_AJAX) { return; } $is_parent = (bool) wpsc_product_has_children($post_id); $product_meta = get_post_meta($post_id, '_wpsc_product_metadata', true); $weight_unit = $product_meta["weight_unit"]; $weight = wpsc_convert_weight($_POST["weight"], $weight_unit, "pound", true); if (isset($product_meta["weight"])) { unset($product_meta["weight"]); } $product_meta["weight"] = $weight; if (!$is_parent) { update_post_meta($post_id, '_wpsc_product_metadata', $product_meta); if (is_numeric($_POST['stock'])) { update_post_meta($post_id, '_wpsc_stock', $_POST['stock']); } else { update_post_meta($post_id, '_wpsc_stock', ''); } update_post_meta($post_id, '_wpsc_price', $_POST['price']); update_post_meta($post_id, '_wpsc_special_price', $_POST['sale_price']); } if ($_POST['sku'] == __('N/A', 'wpsc')) { update_post_meta($post_id, '_wpsc_sku', ''); } else { update_post_meta($post_id, '_wpsc_sku', $_POST['sku']); } return $post_id; }
/** * wpsc_additional_column_data. * * @access public * @param (array) $column * @return void * @todo Need to check titles / alt tags ( I don't think thumbnails have any in this code ) * @desc Switch function to generate columns the right way...no more UI hacking! * */ function wpsc_additional_column_data($column) { global $post; $is_parent = (bool) wpsc_product_has_children($post->ID); switch ($column) { case 'image': $attached_images = get_posts(array('post_type' => 'attachment', 'numberposts' => 1, 'post_parent' => $post->ID, 'orderby' => 'menu_order', 'order' => 'ASC')); if (isset($post->ID) && has_post_thumbnail($post->ID)) { echo get_the_post_thumbnail($post->ID, 'admin-product-thumbnails'); } else { if (!empty($attached_images)) { $attached_image = $attached_images[0]; $src = wp_get_attachment_url($attached_image->ID); ?> <div style='width:38px; height:38px; overflow:hidden;'> <img src='<?php echo $src; ?> ' alt='<?php _e('Drag to a new position', 'wpsc'); ?> ' width='38' height='38' /> </div> <?php } else { $image_url = WPSC_CORE_IMAGES_URL . "/no-image-uploaded.gif"; ?> <img src='<?php echo $image_url; ?> ' alt='<?php _e('Drag to a new position', 'wpsc'); ?> ' width='38' height='38' /> <?php } } break; case 'weight': if ($is_parent) { _e('N/A', 'wpsc'); } else { $product_data['meta'] = array(); $product_data['meta'] = get_post_meta($post->ID, ''); foreach ($product_data['meta'] as $meta_name => $meta_value) { $product_data['meta'][$meta_name] = maybe_unserialize(array_pop($meta_value)); } $product_data['transformed'] = array(); if (!isset($product_data['meta']['_wpsc_product_metadata']['weight'])) { $product_data['meta']['_wpsc_product_metadata']['weight'] = ""; } if (!isset($product_data['meta']['_wpsc_product_metadata']['weight_unit'])) { $product_data['meta']['_wpsc_product_metadata']['weight_unit'] = ""; } $product_data['transformed']['weight'] = wpsc_convert_weight($product_data['meta']['_wpsc_product_metadata']['weight'], "pound", $product_data['meta']['_wpsc_product_metadata']['weight_unit']); $weight = $product_data['transformed']['weight']; if ($weight == '') { $weight = '0'; } $unit = $product_data['meta']['_wpsc_product_metadata']['weight_unit']; switch ($unit) { case "pound": $unit = __(" lbs.", "wpsc"); break; case "ounce": $unit = __(" oz.", "wpsc"); break; case "gram": $unit = __(" g", "wpsc"); break; case "kilograms": case "kilogram": $unit = __(" kgs.", "wpsc"); break; } echo $weight . $unit; echo '<div id="inline_' . $post->ID . '_weight" class="hidden">' . $weight . '</div>'; } break; case 'stock': $stock = get_post_meta($post->ID, '_wpsc_stock', true); if ($stock == '') { $stock = __('N/A', 'wpsc'); } if (!$is_parent) { echo $stock; echo '<div id="inline_' . $post->ID . '_stock" class="hidden">' . $stock . '</div>'; } else { echo '~' . wpsc_variations_stock_remaining($post->ID); } break; case 'price': $price = get_post_meta($post->ID, '_wpsc_price', true); $has_var = '1'; if (!$is_parent) { echo wpsc_currency_display($price); echo '<div id="inline_' . $post->ID . '_price" class="hidden">' . trim($price) . '</div>'; $has_var = '0'; } else { echo wpsc_product_variation_price_available($post->ID) . '+'; } echo '<input type="hidden" value="' . $has_var . '" id="inline_' . $post->ID . '_has_var" />'; break; case 'sale_price': $price = get_post_meta($post->ID, '_wpsc_special_price', true); if (!$is_parent) { echo wpsc_currency_display($price); echo '<div id="inline_' . $post->ID . '_sale_price" class="hidden">' . $price . '</div>'; } else { echo wpsc_product_variation_price_available($post->ID) . '+'; } break; case 'SKU': $sku = get_post_meta($post->ID, '_wpsc_sku', true); if ($sku == '') { $sku = __('N/A', 'wpsc'); } echo $sku; echo '<div id="inline_' . $post->ID . '_sku" class="hidden">' . $sku . '</div>'; break; case 'cats': $categories = get_the_product_category($post->ID); if (!empty($categories)) { $out = array(); foreach ($categories as $c) { $out[] = "<a href='?post_type=wpsc-product&wpsc_product_category={$c->slug}'> " . esc_html(sanitize_term_field('name', $c->name, $c->term_id, 'category', 'display')) . "</a>"; } echo join(', ', $out); } else { _e('Uncategorized', 'wpsc'); } break; case 'featured': $featured_product_url = wp_nonce_url("index.php?wpsc_admin_action=update_featured_product&product_id={$post->ID}", 'feature_product_' . $post->ID); ?> <a class="wpsc_featured_product_toggle featured_toggle_<?php echo $post->ID; ?> " href='<?php echo $featured_product_url; ?> ' > <?php if (in_array($post->ID, (array) get_option('sticky_products'))) { ?> <img class='gold-star' src='<?php echo WPSC_CORE_IMAGES_URL; ?> /gold-star.gif' alt='<?php _e('Unmark as Featured', 'wpsc'); ?> ' title='<?php _e('Unmark as Featured', 'wpsc'); ?> ' /> <?php } else { ?> <img class='grey-star' src='<?php echo WPSC_CORE_IMAGES_URL; ?> /grey-star.gif' alt='<?php _e('Mark as Featured', 'wpsc'); ?> ' title='<?php _e('Mark as Featured', 'wpsc'); ?> ' /> <?php } ?> </a> <?php break; case 'hidden_alerts': $product_alert = apply_filters('wpsc_product_alert', array(false, ''), $post); if (!empty($product_alert['messages'])) { $product_alert['messages'] = implode("\n", (array) $product_alert['messages']); } if ($product_alert['state'] === true) { ?> <img alt='<?php echo $product_alert['messages']; ?> ' title='<?php echo $product_alert['messages']; ?> ' class='product-alert-image' src='<?php echo WPSC_CORE_IMAGES_URL; ?> /product-alert.jpg' alt='' /> <?php } // If a product alert has stuff to display, show it. // Can be used to add extra icons etc if (!empty($product_alert['display'])) { echo $product_alert['display']; } break; } }