Dashboard Overview - <?php bloginfo('name'); ?>

Lets work first with my Dashboard menu, specifically in table for "Highest Selling Products" and "Latest Sales". In the pasted code identify why the the products being ordered and mark as "Paid" is not being displayed in this tables. My existing tables in database are needed in this problem is, categories, products, orders, order_items. Here is the code: 'Invalid data type specified']; // Default error if ($data_type) { // Note: The fetch_* functions need access to $wpdb, ensure they are defined globally or passed if needed. // Since they are defined outside this function but use `global $wpdb;` inside, it should work here. switch ($data_type) { case 'overview_boxes': $response = [ 'userCount' => fetch_wp_user_count(), 'categoryCount' => fetch_wp_category_count(), 'productCount' => fetch_wp_product_count(), 'totalSales' => fetch_wp_total_sales(), ]; break; case 'highest_selling_products': $response = fetch_wp_highest_selling_products(); break; case 'latest_sales': $response = fetch_wp_latest_sales(); break; case 'recent_products': $response = fetch_wp_recent_products(); break; default: error_log("Dashboard AJAX Error: Unknown data_type requested - " . $data_type); // Keep default error message break; } wp_send_json_success($response); } else { error_log("Dashboard AJAX Error: data_type parameter missing."); wp_send_json_error($response, 400); // Send error if data_type is missing } // wp_die(); // Included in wp_send_json_* functions } // --- *** NEW: Hook the handler to WordPress AJAX actions *** --- // IMPORTANT: These lines ideally belong in functions.php or a plugin file. // Placing them here might work, but it's not the standard/recommended practice. add_action('wp_ajax_get_dashboard_data', 'dashboard_overview_ajax_handler'); // For logged-in users // add_action('wp_ajax_nopriv_get_dashboard_data', 'dashboard_overview_ajax_handler'); // Uncomment if needed for non-logged-in users // --- Data Fetching Functions (Using NO prefix as requested) --- function fetch_wp_user_count() { global $wpdb; $users_table_name = 'users'; // WP Users table *always* uses prefix if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $users_table_name)) != $users_table_name) { return 0; } $count = $wpdb->get_var("SELECT COUNT(*) FROM $users_table_name"); return $count ? $count : 0; } function fetch_wp_category_count() { global $wpdb; $category_table_name = 'categories'; // NO prefix if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $category_table_name)) != $category_table_name) { return 0; } $count = $wpdb->get_var("SELECT COUNT(*) FROM $category_table_name"); return $count ? $count : 0; } function fetch_wp_product_count() { global $wpdb; $product_table_name = 'products'; // NO prefix if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $product_table_name)) != $product_table_name) { return 0; } $count = $wpdb->get_var("SELECT COUNT(*) FROM $product_table_name"); return $count ? $count : 0; } function fetch_wp_total_sales() { global $wpdb; $sales_table_name = 'orders'; // NO prefix $sales_amount_column = 'total_amount'; if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $sales_table_name)) != $sales_table_name) { return '0.00'; } $total = $wpdb->get_var($wpdb->prepare("SELECT SUM($sales_amount_column) FROM $sales_table_name WHERE status = %s", 'Paid')); return $total ? number_format_i18n($total, 2) : '0.00'; } function fetch_wp_highest_selling_products() { global $wpdb; $products = []; $products_table_name = 'products'; // NO prefix $sales_items_table_name = 'order_items'; // NO prefix $orders_table_name = 'orders'; // NO prefix if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $products_table_name)) != $products_table_name || $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $sales_items_table_name)) != $sales_items_table_name || $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $orders_table_name)) != $orders_table_name ) { return $products; } $results = $wpdb->get_results($wpdb->prepare(" SELECT p.name AS product_name, SUM(oi.quantity) AS items_sold, SUM(oi.quantity * oi.price_at_order) AS total_sales FROM {$sales_items_table_name} oi JOIN {$products_table_name} p ON oi.product_id = p.id JOIN {$orders_table_name} o ON oi.order_id = o.id WHERE o.status = %s GROUP BY p.id, p.name ORDER BY items_sold DESC LIMIT %d ", 'Paid', 5), ARRAY_A); if ($results) { foreach ($results as $row) { $products[] = [ 'name' => $row['product_name'], 'items_sold' => $row['items_sold'], 'total_sales' => number_format_i18n($row['total_sales'], 2), ]; } } return $products; } function fetch_wp_latest_sales() { global $wpdb; $sales = []; $sales_table_name = 'orders'; // NO prefix $sales_items_table_name = 'order_items'; // NO prefix $products_table_name = 'products'; // NO prefix if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $sales_table_name)) != $sales_table_name || $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $sales_items_table_name)) != $sales_items_table_name || $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $products_table_name)) != $products_table_name) { return $sales; } $results = $wpdb->get_results($wpdb->prepare(" SELECT s.id AS sale_id, GROUP_CONCAT(DISTINCT p.name SEPARATOR ', ') AS product_names, s.total_amount, s.order_date AS sale_date FROM {$sales_table_name} s LEFT JOIN {$sales_items_table_name} oi ON s.id = oi.order_id LEFT JOIN {$products_table_name} p ON oi.product_id = p.id WHERE s.status = 'Paid' GROUP BY s.id, s.total_amount, s.order_date ORDER BY s.order_date DESC LIMIT %d ", 5), ARRAY_A); if ($results) { foreach ($results as $row) { $sales[] = [ 'item_no' => 'Order #' . $row['sale_id'], 'product_name' => $row['product_names'] ?? 'N/A', 'total_sales' => number_format_i18n($row['total_amount'], 2), ]; } } return $sales; } // --- UPDATED fetch_wp_recent_products function --- function fetch_wp_recent_products() { global $wpdb; $products = []; $products_table_name = 'products'; // NO prefix // *** IMPORTANT: Define the *actual* date column name in your 'products' table here *** $products_date_column = 'created_at'; // <--- CHANGE THIS ('created_at', 'date_created', 'date_added', etc.) if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $products_table_name)) != $products_table_name) { error_log("Dashboard Snippet Error: Table '{$products_table_name}' not found."); return $products; // Table doesn't exist } // Check if the specified date column exists $columns = $wpdb->get_col("DESC $products_table_name"); if (!in_array($products_date_column, $columns)) { error_log("Dashboard Snippet Error: Specified date column '{$products_date_column}' not found in table '{$products_table_name}'. Please check the function."); return $products; // Column doesn't exist, cannot sort } // Prepare and execute the query $query = $wpdb->prepare(" SELECT name, {$products_date_column} AS created_at_alias FROM {$products_table_name} WHERE name IS NOT NULL AND name != '' AND {$products_date_column} IS NOT NULL ORDER BY {$products_date_column} DESC LIMIT %d", 5); $results = $wpdb->get_results($query, ARRAY_A); // Check for query errors if ($results === null) { error_log("Dashboard Snippet Error: WPDB query failed for recent products. Error: " . $wpdb->last_error . " Query: " . $query); return $products; // Query failed } // Process results if ($results) { foreach ($results as $row) { // Ensure name and date exist before adding if (isset($row['name']) && isset($row['created_at_alias'])) { $products[] = [ 'name' => $row['name'], 'date_added' => mysql2date(get_option('date_format'), $row['created_at_alias']), // Use the alias ]; } } } return $products; } // --- END UPDATED fetch_wp_recent_products function --- // --- Fetch Data for Stock Chart --- $product_table_name_chart = 'products'; // NO prefix $products_for_chart = []; $chart_data_error = ''; if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $product_table_name_chart)) == $product_table_name_chart) { $products_for_chart_raw = $wpdb->get_results( "SELECT name, in_stock FROM {$product_table_name_chart} WHERE name IS NOT NULL AND name != '' ORDER BY name ASC", ARRAY_A ); if ($products_for_chart_raw === null) { $chart_data_error = "Error querying products table: " . $wpdb->last_error; error_log("Dashboard Chart Error: " . $chart_data_error); } elseif (empty($products_for_chart_raw)) { $chart_data_error = "No products with names found in the '{$product_table_name_chart}' table."; } else { foreach ($products_for_chart_raw as $product) { $products_for_chart[] = [ 'name' => $product['name'], 'in_stock' => intval($product['in_stock'] ?? 0) ]; } } } else { $chart_data_error = "Error: Products table '{$product_table_name_chart}' not found."; } $chart_labels = json_encode(wp_list_pluck($products_for_chart, 'name')); $chart_data = json_encode(wp_list_pluck($products_for_chart, 'in_stock')); // --- END: Fetch Data for Stock Chart --- // --- Initial Data for Page Load --- $initialUserCount = fetch_wp_user_count(); $initialCategoryCount = fetch_wp_category_count(); $initialProductCount = fetch_wp_product_count(); $initialTotalSales = fetch_wp_total_sales(); $initialHighestSellingProducts = fetch_wp_highest_selling_products(); $initialLatestSales = fetch_wp_latest_sales(); $initialRecentProducts = fetch_wp_recent_products(); $currency_symbol = function_exists('get_woocommerce_currency_symbol') ? get_woocommerce_currency_symbol() : '₱'; ?> Dashboard Overview - <?php bloginfo('name'); ?> >

Users

Product Stock Overview

No product data available to display the chart.

Loading chart...

Highest Selling Products

Product Name	Items Sold	Total Sales
No sales data available

Latest Sales

Item No	Product(s)	Total Sales
No recent sales

Recently Added Products

Product Name	Date Added
No recently added products