add color each column in each table true, 'data' => $data]); if ($status_code) http_response_code($status_code); exit; } } if (!function_exists('wp_send_json_error')) { function wp_send_json_error($data = null, $status_code = null) { header('Content-Type: application/json; charset=' . get_option('blog_charset')); echo json_encode(['success' => false, 'data' => $data]); if ($status_code) http_response_code($status_code); exit; } } if (!function_exists('sanitize_key')) { function sanitize_key($key) { return preg_replace('/[^a-zA-Z0-9_\-]/', '', $key); } } if (!function_exists('esc_html')) { function esc_html($text) { return htmlspecialchars($text, ENT_QUOTES, 'UTF-8'); } } if (!function_exists('esc_js')) { function esc_js($text) { return json_encode($text); } } // Basic JSON encoding for JS if (!function_exists('wp_list_pluck')) { function wp_list_pluck($list, $field, $index_key = null) { if (!is_array($list)) return []; $newlist = []; if ($index_key === null) { foreach ($list as $key => $value) { if (is_object($value) && isset($value->$field)) $newlist[$key] = $value->$field; elseif (is_array($value) && isset($value[$field])) $newlist[$key] = $value[$field]; } } else { foreach ($list as $value) { if (is_object($value) && isset($value->$field, $value->$index_key)) $newlist[$value->$index_key] = $value->$field; elseif (is_array($value) && isset($value[$field], $value[$index_key])) $newlist[$value[$index_key]] = $value[$field]; } } return $newlist; } } if (!function_exists('mysql2date')) { function mysql2date($format, $date, $tz = false) { $datetime = new DateTime($date); return $datetime->format($format); } } if (!function_exists('get_option')) { function get_option($option) { if ($option === 'date_format') return 'Y-m-d'; return false; } } // Placeholder if (!function_exists('number_format_i18n')) { function number_format_i18n($number, $decimals = 0) { return number_format($number, $decimals); } } // Placeholder if (!function_exists('get_woocommerce_currency_symbol')) { function get_woocommerce_currency_symbol() { return '$'; } } // Placeholder if (!function_exists('language_attributes')) { function language_attributes() { echo 'en-US'; } } // Placeholder if (!function_exists('bloginfo')) { function bloginfo($show) { if ($show === 'charset') echo 'UTF-8'; elseif ($show === 'name') echo 'My Application'; } } // Placeholder if (!function_exists('body_class')) { function body_class() { echo ''; } } // Placeholder if (!function_exists('admin_url')) { function admin_url($path) { return 'admin-ajax.php'; } } // Placeholder // Add dummy $wpdb if not in WP context if (!isset($GLOBALS['wpdb'])) { class DummyWpdb { public $last_error = ''; public function get_var($query) { error_log("DummyWpdb: Executing query: " . $query); return null; } public function get_results($query, $type = ARRAY_A) { error_log("DummyWpdb: Executing query: " . $query); return []; } public function prepare($query, ...$args) { return $query; } // Simple placeholder public function get_col($query) { error_log("DummyWpdb: Executing query: " . $query); return []; } } $GLOBALS['wpdb'] = new DummyWpdb(); } // Add dummy user functions if not in WP context if (!function_exists('wp_get_current_user')) { function wp_get_current_user() { $user = new stdClass(); $user->user_login = 'Guest'; $user->roles = []; return $user; } } if (!function_exists('user_can')) { function user_can($user, $capability) { return in_array($capability, $user->roles); } } } // --- Database Connection (Using WordPress $wpdb) --- global $wpdb; // --- Dedicated AJAX Handler Function --- function dashboard_overview_ajax_handler() { // Optional: Add nonce check for security // check_ajax_referer('dashboard_data_nonce', 'security'); $data_type = isset($_REQUEST['data_type']) ? sanitize_key($_REQUEST['data_type']) : null; $response = ['error' => '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 } // --- 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. // Ensure these are only added once if this file is included multiple times. if (!has_action('wp_ajax_get_dashboard_data', 'dashboard_overview_ajax_handler')) { add_action('wp_ajax_get_dashboard_data', 'dashboard_overview_ajax_handler'); // For logged-in users } // if (!has_action('wp_ajax_nopriv_get_dashboard_data', 'dashboard_overview_ajax_handler')) { // 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'; $order_items_table_name = 'order_items'; $orders_table_name = 'orders'; // Debug: Check table existence 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", $order_items_table_name)) != $order_items_table_name || $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $orders_table_name)) != $orders_table_name) { error_log("Dashboard Error: Required tables not found"); return $products; } $results = $wpdb->get_results($wpdb->prepare(" SELECT p.name AS product_name, SUM(oi.quantity) AS items_sold, SUM(oi.amount) AS total_sales FROM {$order_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 = 'Paid' GROUP BY p.id, p.name ORDER BY items_sold DESC LIMIT %d", 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), ]; } } else { error_log("Dashboard Debug: No highest selling products found"); } return $products; } function fetch_wp_latest_sales() { global $wpdb; $sales = []; $order_items_table_name = 'order_items'; $orders_table_name = 'orders'; $products_table_name = 'products'; // Debug: Check table existence if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $orders_table_name)) != $orders_table_name || $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $order_items_table_name)) != $order_items_table_name || $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $products_table_name)) != $products_table_name) { error_log("Dashboard Error: Required tables not found"); return $sales; } $results = $wpdb->get_results($wpdb->prepare(" SELECT o.id AS order_id, o.total_amount, o.created_at, GROUP_CONCAT(DISTINCT p.name SEPARATOR ', ') AS product_names FROM {$orders_table_name} o LEFT JOIN {$order_items_table_name} oi ON o.id = oi.order_id LEFT JOIN {$products_table_name} p ON oi.product_id = p.id WHERE o.status = 'Paid' GROUP BY o.id, o.total_amount, o.created_at ORDER BY o.created_at DESC LIMIT %d", 5), ARRAY_A); if ($results) { foreach ($results as $row) { $sales[] = [ 'item_no' => 'Order #' . $row['order_id'], 'product_name' => $row['product_names'] ?? 'N/A', 'total_sales' => number_format_i18n($row['total_amount'], 2), ]; } } else { error_log("Dashboard Debug: No latest sales found"); } 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() : '₱'; // --- Get Current Username (Using $_SESSION as per your apparent setup) --- // Note: This is non-standard for WordPress. $current_username = isset($_SESSION['username']) ? $_SESSION['username'] : 'Guest'; // Determine if user is admin based on username (assuming 'admin' is the admin username) // Note: This is also non-standard for WordPress. Use user_can() with roles if possible. $is_admin = ($current_username === 'admin'); ?> Dashboard Overview - <?php bloginfo('name'); ?> >

Dashboard Overview

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