qdr.app.woo.plugins/studiou-wc-custom-reports/includes/class-db-manager.php

<?php
/**
 * Database Manager Class - Handles all database operations (HPOS Compatible)
 *
 * @package StudiouWCCustomReports
 */

if (!defined('ABSPATH')) {
    exit;
}

class StudiouWC_DB_Manager {
    
    public function get_product_categories_summary($date_from, $date_to, $statuses, $per_page, $paged, $orderby, $order) {
        global $wpdb;
        
        if (empty($statuses)) {
            return array();
        }
        
        $settings_manager = new StudiouWC_Settings_Manager();
        $settings = $settings_manager->get_settings();
        $default_property = $settings['default_property'] ?? '';
        
        $statuses_placeholder = implode(',', array_fill(0, count($statuses), '%s'));
        $offset = ($paged - 1) * $per_page;
        
        // Check if HPOS is enabled
        if ($this->is_hpos_enabled()) {
            $sql = $this->get_hpos_categories_query($statuses_placeholder);
        } else {
            $sql = $this->get_legacy_categories_query($statuses_placeholder);
        }
        
        // Add ordering
        $sql .= $this->get_order_clause($orderby, $order);
        $sql .= " LIMIT %d OFFSET %d";
        
        $query_params = array_merge(
            array($date_from, $date_to),
            $statuses,
            array($per_page, $offset)
        );
        
        $results = $wpdb->get_results($wpdb->prepare($sql, $query_params), ARRAY_A);
        
        // Get property counts for each category if property is set
        foreach ($results as &$result) {
            $result['property_counts'] = array();
            if (!empty($default_property)) {
                $result['property_counts'] = $this->get_property_counts_for_category(
                    $result['category'], 
                    $date_from, 
                    $date_to, 
                    $statuses, 
                    $default_property
                );
            }
        }
        
        return $results;
    }
    
    public function get_property_counts_for_category($category, $date_from, $date_to, $statuses, $property) {
        global $wpdb;
        
        if (empty($statuses) || empty($property)) {
            return array();
        }
        
        $statuses_placeholder = implode(',', array_fill(0, count($statuses), '%s'));
        $meta_key = 'attribute_pa_' . $property; // WooCommerce stores variation attributes as meta
        
        // Get order items for this category and date range, then check their variation meta
        if ($this->is_hpos_enabled()) {
            $base_sql = "
                SELECT oi.product_id, oi.variation_id, oi.product_qty, pm.meta_value as attr_value
                FROM {$wpdb->prefix}wc_orders o
                INNER JOIN {$wpdb->prefix}wc_order_product_lookup oi ON o.id = oi.order_id
                INNER JOIN {$wpdb->posts} p_main ON (
                    CASE 
                        WHEN oi.variation_id > 0 THEN 
                            (SELECT post_parent FROM {$wpdb->posts} WHERE ID = oi.variation_id)
                        ELSE oi.product_id 
                    END
                ) = p_main.ID
                INNER JOIN {$wpdb->term_relationships} tr_cat ON p_main.ID = tr_cat.object_id
                INNER JOIN {$wpdb->term_taxonomy} tt_cat ON tr_cat.term_taxonomy_id = tt_cat.term_taxonomy_id
                INNER JOIN {$wpdb->terms} t_cat ON tt_cat.term_id = t_cat.term_id
                LEFT JOIN {$wpdb->postmeta} pm ON (
                    CASE 
                        WHEN oi.variation_id > 0 THEN oi.variation_id
                        ELSE oi.product_id 
                    END
                ) = pm.post_id AND pm.meta_key = %s
                WHERE o.date_created_gmt >= %s 
                AND o.date_created_gmt <= %s 
                AND o.status IN ($statuses_placeholder)
                AND tt_cat.taxonomy = 'product_cat'
                AND t_cat.name = %s
                AND pm.meta_value IS NOT NULL
                AND pm.meta_value != ''
            ";
        } else {
            $base_sql = "
                SELECT oi.product_id, oi.variation_id, oi.product_qty, pm.meta_value as attr_value
                FROM {$wpdb->posts} p_order
                INNER JOIN {$wpdb->prefix}wc_order_product_lookup oi ON p_order.ID = oi.order_id
                INNER JOIN {$wpdb->posts} p_main ON (
                    CASE 
                        WHEN oi.variation_id > 0 THEN 
                            (SELECT post_parent FROM {$wpdb->posts} WHERE ID = oi.variation_id)
                        ELSE oi.product_id 
                    END
                ) = p_main.ID
                INNER JOIN {$wpdb->term_relationships} tr_cat ON p_main.ID = tr_cat.object_id
                INNER JOIN {$wpdb->term_taxonomy} tt_cat ON tr_cat.term_taxonomy_id = tt_cat.term_taxonomy_id
                INNER JOIN {$wpdb->terms} t_cat ON tt_cat.term_id = t_cat.term_id
                LEFT JOIN {$wpdb->postmeta} pm ON (
                    CASE 
                        WHEN oi.variation_id > 0 THEN oi.variation_id
                        ELSE oi.product_id 
                    END
                ) = pm.post_id AND pm.meta_key = %s
                WHERE p_order.post_date >= %s 
                AND p_order.post_date <= %s 
                AND p_order.post_status IN ($statuses_placeholder)
                AND p_order.post_type = 'shop_order'
                AND tt_cat.taxonomy = 'product_cat'
                AND t_cat.name = %s
                AND pm.meta_value IS NOT NULL
                AND pm.meta_value != ''
            ";
        }
        
        $query_params = array_merge(
            array($meta_key, $date_from, $date_to),
            $statuses,
            array($category)
        );
        
        // Debug: Log the query for troubleshooting
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('[Studiou Debug] Updated property counts query: ' . $wpdb->prepare($base_sql, $query_params));
        }
        
        $results = $wpdb->get_results($wpdb->prepare($base_sql, $query_params), ARRAY_A);
        
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('[Studiou Debug] Found ' . count($results) . ' items with attribute values');
            error_log('[Studiou Debug] Sample results: ' . print_r(array_slice($results, 0, 3), true));
        }
        
        // Count quantities by attribute value
        $property_counts = array();
        foreach ($results as $result) {
            $attr_value = $result['attr_value'];
            $qty = $result['product_qty'];
            
            if (!empty($attr_value)) {
                if (!isset($property_counts[$attr_value])) {
                    $property_counts[$attr_value] = 0;
                }
                $property_counts[$attr_value] += $qty;
            }
        }
        
        if (defined('WP_DEBUG') && WP_DEBUG) {
            error_log('[Studiou Debug] Final property counts: ' . print_r($property_counts, true));
        }
        
        return $property_counts;
    }
    
    public function get_total_items($date_from, $date_to, $statuses) {
        global $wpdb;
        
        if (empty($statuses)) {
            return 0;
        }
        
        $statuses_placeholder = implode(',', array_fill(0, count($statuses), '%s'));
        
        if ($this->is_hpos_enabled()) {
            $sql = $this->get_hpos_total_items_query($statuses_placeholder);
        } else {
            $sql = $this->get_legacy_total_items_query($statuses_placeholder);
        }
        
        $query_params = array_merge(
            array($date_from, $date_to),
            $statuses
        );
        
        return $wpdb->get_var($wpdb->prepare($sql, $query_params));
    }
    
    /**
     * Get product attributes (not meta keys) for dropdown
     */
    public function get_product_properties() {
        global $wpdb;
        
        // Get WooCommerce product attributes
        $attributes = $wpdb->get_results("
            SELECT attribute_name, attribute_label 
            FROM {$wpdb->prefix}woocommerce_attribute_taxonomies 
            ORDER BY attribute_label
        ", ARRAY_A);
        
        $properties = array();
        foreach ($attributes as $attribute) {
            $properties[$attribute['attribute_name']] = $attribute['attribute_label'];
        }
        
        return $properties;
    }
    
    /**
     * Get property values for a specific attribute
     */
    public function get_property_values($property) {
        global $wpdb;
        
        if (empty($property)) {
            return array();
        }
        
        // Build taxonomy name for WooCommerce attributes
        $taxonomy = 'pa_' . $property;
        
        // Get terms for this attribute taxonomy
        $sql = "
            SELECT t.name, t.slug
            FROM {$wpdb->terms} t
            INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
            WHERE tt.taxonomy = %s
            ORDER BY t.name
        ";
        
        $results = $wpdb->get_results($wpdb->prepare($sql, $taxonomy), ARRAY_A);
        
        $values = array();
        foreach ($results as $result) {
            $values[$result['slug']] = $result['name'];
        }
        
        return $values;
    }
    
    /**
     * Get attribute name from attribute slug
     */
    public function get_attribute_name($attribute_slug) {
        global $wpdb;
        
        $result = $wpdb->get_var($wpdb->prepare("
            SELECT attribute_label 
            FROM {$wpdb->prefix}woocommerce_attribute_taxonomies 
            WHERE attribute_name = %s
        ", $attribute_slug));
        
        return $result ? $result : $attribute_slug;
    }
    
    /**
     * Check if HPOS (High-Performance Order Storage) is enabled
     */
    private function is_hpos_enabled() {
        return class_exists('Automattic\WooCommerce\Utilities\OrderUtil') && 
               \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
    }
    
    /**
     * Get HPOS compatible query for categories summary
     */
    private function get_hpos_categories_query($statuses_placeholder) {
        global $wpdb;
        
        return "
            SELECT 
                t.name as category,
                COUNT(DISTINCT o.id) as orders_count,
                SUM(oi.product_net_revenue) as total_price
            FROM {$wpdb->prefix}wc_orders o
            INNER JOIN {$wpdb->prefix}wc_order_product_lookup oi ON o.id = oi.order_id
            INNER JOIN {$wpdb->posts} p ON oi.product_id = p.ID OR oi.variation_id = p.ID
            INNER JOIN {$wpdb->term_relationships} tr ON (
                CASE 
                    WHEN oi.variation_id > 0 THEN 
                        (SELECT post_parent FROM {$wpdb->posts} WHERE ID = oi.variation_id)
                    ELSE oi.product_id 
                END
            ) = tr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            WHERE o.date_created_gmt >= %s 
            AND o.date_created_gmt <= %s 
            AND o.status IN ($statuses_placeholder)
            AND tt.taxonomy = 'product_cat'
            GROUP BY t.term_id, t.name
        ";
    }
    
    /**
     * Get legacy query for categories summary (pre-HPOS)
     */
    private function get_legacy_categories_query($statuses_placeholder) {
        global $wpdb;
        
        return "
            SELECT 
                t.name as category,
                COUNT(DISTINCT p_order.ID) as orders_count,
                SUM(oi.product_net_revenue) as total_price
            FROM {$wpdb->posts} p_order
            INNER JOIN {$wpdb->prefix}wc_order_product_lookup oi ON p_order.ID = oi.order_id
            INNER JOIN {$wpdb->posts} p ON oi.product_id = p.ID OR oi.variation_id = p.ID
            INNER JOIN {$wpdb->term_relationships} tr ON (
                CASE 
                    WHEN oi.variation_id > 0 THEN 
                        (SELECT post_parent FROM {$wpdb->posts} WHERE ID = oi.variation_id)
                    ELSE oi.product_id 
                END
            ) = tr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            WHERE p_order.post_date >= %s 
            AND p_order.post_date <= %s 
            AND p_order.post_status IN ($statuses_placeholder)
            AND p_order.post_type = 'shop_order'
            AND tt.taxonomy = 'product_cat'
            GROUP BY t.term_id, t.name
        ";
    }
    
    /**
     * Get HPOS compatible query for property counts
     */
    private function get_hpos_property_counts_query($statuses_placeholder) {
        global $wpdb;
        
        return "
            SELECT 
                t_attr.slug as property_value,
                SUM(oi.product_qty) as count
            FROM {$wpdb->prefix}wc_orders o
            INNER JOIN {$wpdb->prefix}wc_order_product_lookup oi ON o.id = oi.order_id
            INNER JOIN {$wpdb->posts} p_main ON (
                CASE 
                    WHEN oi.variation_id > 0 THEN 
                        (SELECT post_parent FROM {$wpdb->posts} WHERE ID = oi.variation_id)
                    ELSE oi.product_id 
                END
            ) = p_main.ID
            INNER JOIN {$wpdb->term_relationships} tr_cat ON p_main.ID = tr_cat.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt_cat ON tr_cat.term_taxonomy_id = tt_cat.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t_cat ON tt_cat.term_id = t_cat.term_id
            INNER JOIN {$wpdb->term_relationships} tr_attr ON (
                CASE 
                    WHEN oi.variation_id > 0 THEN oi.variation_id
                    ELSE oi.product_id 
                END
            ) = tr_attr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt_attr ON tr_attr.term_taxonomy_id = tt_attr.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t_attr ON tt_attr.term_id = t_attr.term_id
            WHERE o.date_created_gmt >= %s 
            AND o.date_created_gmt <= %s 
            AND o.status IN ($statuses_placeholder)
            AND tt_cat.taxonomy = 'product_cat'
            AND t_cat.name = %s
            AND tt_attr.taxonomy = %s
            GROUP BY t_attr.term_id, t_attr.slug
        ";
    }
    
    /**
     * Get legacy query for property counts (pre-HPOS)
     */
    private function get_legacy_property_counts_query($statuses_placeholder) {
        global $wpdb;
        
        return "
            SELECT 
                t_attr.slug as property_value,
                SUM(oi.product_qty) as count
            FROM {$wpdb->posts} p_order
            INNER JOIN {$wpdb->prefix}wc_order_product_lookup oi ON p_order.ID = oi.order_id
            INNER JOIN {$wpdb->posts} p_main ON (
                CASE 
                    WHEN oi.variation_id > 0 THEN 
                        (SELECT post_parent FROM {$wpdb->posts} WHERE ID = oi.variation_id)
                    ELSE oi.product_id 
                END
            ) = p_main.ID
            INNER JOIN {$wpdb->term_relationships} tr_cat ON p_main.ID = tr_cat.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt_cat ON tr_cat.term_taxonomy_id = tt_cat.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t_cat ON tt_cat.term_id = t_cat.term_id
            INNER JOIN {$wpdb->term_relationships} tr_attr ON (
                CASE 
                    WHEN oi.variation_id > 0 THEN oi.variation_id
                    ELSE oi.product_id 
                END
            ) = tr_attr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt_attr ON tr_attr.term_taxonomy_id = tt_attr.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t_attr ON tt_attr.term_id = t_attr.term_id
            WHERE p_order.post_date >= %s 
            AND p_order.post_date <= %s 
            AND p_order.post_status IN ($statuses_placeholder)
            AND p_order.post_type = 'shop_order'
            AND tt_cat.taxonomy = 'product_cat'
            AND t_cat.name = %s
            AND tt_attr.taxonomy = %s
            GROUP BY t_attr.term_id, t_attr.slug
        ";
    }
    
    /**
     * Get HPOS compatible query for total items
     */
    private function get_hpos_total_items_query($statuses_placeholder) {
        global $wpdb;
        
        return "
            SELECT COUNT(DISTINCT t.term_id)
            FROM {$wpdb->prefix}wc_orders o
            INNER JOIN {$wpdb->prefix}wc_order_product_lookup oi ON o.id = oi.order_id
            INNER JOIN {$wpdb->posts} p ON oi.product_id = p.ID OR oi.variation_id = p.ID
            INNER JOIN {$wpdb->term_relationships} tr ON (
                CASE 
                    WHEN oi.variation_id > 0 THEN 
                        (SELECT post_parent FROM {$wpdb->posts} WHERE ID = oi.variation_id)
                    ELSE oi.product_id 
                END
            ) = tr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            WHERE o.date_created_gmt >= %s 
            AND o.date_created_gmt <= %s 
            AND o.status IN ($statuses_placeholder)
            AND tt.taxonomy = 'product_cat'
        ";
    }
    
    /**
     * Get legacy query for total items (pre-HPOS)
     */
    private function get_legacy_total_items_query($statuses_placeholder) {
        global $wpdb;
        
        return "
            SELECT COUNT(DISTINCT t.term_id)
            FROM {$wpdb->posts} p_order
            INNER JOIN {$wpdb->prefix}wc_order_product_lookup oi ON p_order.ID = oi.order_id
            INNER JOIN {$wpdb->posts} p ON oi.product_id = p.ID OR oi.variation_id = p.ID
            INNER JOIN {$wpdb->term_relationships} tr ON (
                CASE 
                    WHEN oi.variation_id > 0 THEN 
                        (SELECT post_parent FROM {$wpdb->posts} WHERE ID = oi.variation_id)
                    ELSE oi.product_id 
                END
            ) = tr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            WHERE p_order.post_date >= %s 
            AND p_order.post_date <= %s 
            AND p_order.post_status IN ($statuses_placeholder)
            AND p_order.post_type = 'shop_order'
            AND tt.taxonomy = 'product_cat'
        ";
    }
    
    public function get_order_customers_list($date_from, $date_to, $statuses, $per_page, $paged, $orderby, $order) {
        global $wpdb;

        if (empty($statuses)) {
            return array();
        }

        $statuses_placeholder = implode(',', array_fill(0, count($statuses), '%s'));
        $offset = ($paged - 1) * $per_page;

        if ($this->is_hpos_enabled()) {
            $sql = $this->get_hpos_customers_query($statuses_placeholder);
        } else {
            $sql = $this->get_legacy_customers_query($statuses_placeholder);
        }

        $sql .= $this->get_customers_order_clause($orderby, $order);
        $sql .= " LIMIT %d OFFSET %d";

        $query_params = array_merge(
            array($date_from, $date_to),
            $statuses,
            array($per_page, $offset)
        );

        return $wpdb->get_results($wpdb->prepare($sql, $query_params), ARRAY_A);
    }

    public function get_order_customers_total_items($date_from, $date_to, $statuses) {
        global $wpdb;

        if (empty($statuses)) {
            return 0;
        }

        $statuses_placeholder = implode(',', array_fill(0, count($statuses), '%s'));

        if ($this->is_hpos_enabled()) {
            $sql = "
                SELECT COUNT(DISTINCT addr.email)
                FROM {$wpdb->prefix}wc_orders o
                INNER JOIN {$wpdb->prefix}wc_order_addresses addr ON o.id = addr.order_id AND addr.address_type = 'billing'
                WHERE o.date_created_gmt >= %s
                AND o.date_created_gmt <= %s
                AND o.status IN ($statuses_placeholder)
                AND addr.email != ''
            ";
        } else {
            $sql = "
                SELECT COUNT(DISTINCT pm_email.meta_value)
                FROM {$wpdb->posts} p
                INNER JOIN {$wpdb->postmeta} pm_email ON p.ID = pm_email.post_id AND pm_email.meta_key = '_billing_email'
                WHERE p.post_date >= %s
                AND p.post_date <= %s
                AND p.post_status IN ($statuses_placeholder)
                AND p.post_type = 'shop_order'
                AND pm_email.meta_value != ''
            ";
        }

        $query_params = array_merge(
            array($date_from, $date_to),
            $statuses
        );

        return $wpdb->get_var($wpdb->prepare($sql, $query_params));
    }

    private function get_hpos_customers_query($statuses_placeholder) {
        global $wpdb;

        return "
            SELECT
                MAX(CONCAT(addr.first_name, ' ', addr.last_name)) as customer_name,
                addr.email as customer_email,
                MAX(addr.phone) as customer_phone,
                COUNT(DISTINCT o.id) as orders_count,
                SUM(o.total_amount) as total_price
            FROM {$wpdb->prefix}wc_orders o
            INNER JOIN {$wpdb->prefix}wc_order_addresses addr ON o.id = addr.order_id AND addr.address_type = 'billing'
            WHERE o.date_created_gmt >= %s
            AND o.date_created_gmt <= %s
            AND o.status IN ($statuses_placeholder)
            AND addr.email != ''
            GROUP BY addr.email
        ";
    }

    private function get_legacy_customers_query($statuses_placeholder) {
        global $wpdb;

        return "
            SELECT
                MAX(CONCAT(pm_fn.meta_value, ' ', pm_ln.meta_value)) as customer_name,
                pm_email.meta_value as customer_email,
                MAX(pm_phone.meta_value) as customer_phone,
                COUNT(DISTINCT p.ID) as orders_count,
                SUM(CAST(pm_total.meta_value AS DECIMAL(10,2))) as total_price
            FROM {$wpdb->posts} p
            INNER JOIN {$wpdb->postmeta} pm_email ON p.ID = pm_email.post_id AND pm_email.meta_key = '_billing_email'
            LEFT JOIN {$wpdb->postmeta} pm_fn ON p.ID = pm_fn.post_id AND pm_fn.meta_key = '_billing_first_name'
            LEFT JOIN {$wpdb->postmeta} pm_ln ON p.ID = pm_ln.post_id AND pm_ln.meta_key = '_billing_last_name'
            LEFT JOIN {$wpdb->postmeta} pm_phone ON p.ID = pm_phone.post_id AND pm_phone.meta_key = '_billing_phone'
            LEFT JOIN {$wpdb->postmeta} pm_total ON p.ID = pm_total.post_id AND pm_total.meta_key = '_order_total'
            WHERE p.post_date >= %s
            AND p.post_date <= %s
            AND p.post_status IN ($statuses_placeholder)
            AND p.post_type = 'shop_order'
            AND pm_email.meta_value != ''
            GROUP BY pm_email.meta_value
        ";
    }

    private function get_customers_order_clause($orderby, $order) {
        $order = ($order === 'DESC') ? 'DESC' : 'ASC';

        switch ($orderby) {
            case 'customer_name':
                return " ORDER BY customer_name $order";
            case 'customer_email':
                return " ORDER BY customer_email $order";
            case 'orders_count':
                return " ORDER BY orders_count $order";
            case 'total_price':
                return " ORDER BY total_price $order";
            default:
                return " ORDER BY customer_name $order";
        }
    }

    private function get_order_clause($orderby, $order) {
        $order = ($order === 'DESC') ? 'DESC' : 'ASC';
        
        switch ($orderby) {
            case 'orders_count':
                return " ORDER BY orders_count $order";
            case 'total_price':
                return " ORDER BY total_price $order";
            case 'category':
            default:
                return " ORDER BY t.name $order";
        }
    }
}