api/StatsService.php - master - delphin/CatRW

api/StatsService.php
568 строк24 KB
delphin
перед глобальным изменением
04 мар 2026, 09:56
04 мар 2026, 09:56c6cfdf3
<?php
// /api/StatsService.php - ИСПРАВЛЕННАЯ ПОЛНАЯ ВЕРСИЯ

class StatsService {
    
    // 1. Общая статистика
    public static function getOverallStats() {
        try {
            $stats = [];
            
            // 1.1. Модели
            try {
                $modelsTotal = Database::selectOne("SELECT COUNT(*) as total FROM catalog_models");
                $modelsPublished = Database::selectOne("SELECT COUNT(*) as count FROM catalog_models WHERE status = 'published'");
                
                $stats['models'] = [
                    'total' => (int)($modelsTotal['total'] ?? 0),
                    'published' => (int)($modelsPublished['count'] ?? 0),
                    'draft' => (int)($modelsTotal['total'] ?? 0) - (int)($modelsPublished['count'] ?? 0)
                ];
            } catch (Exception $e) {
                error_log("Models stats error: " . $e->getMessage());
                $stats['models'] = ['total' => 0, 'published' => 0, 'draft' => 0];
            }
            
            // 1.2. Производители
            try {
                $manTotal = Database::selectOne("SELECT COUNT(*) as total FROM manufacturers");
                $manActive = Database::selectOne("SELECT COUNT(*) as count FROM manufacturers WHERE closed_year IS NULL");
                
                $stats['manufacturers'] = [
                    'total' => (int)($manTotal['total'] ?? 0),
                    'active' => (int)($manActive['count'] ?? 0),
                    'inactive' => (int)($manTotal['total'] ?? 0) - (int)($manActive['count'] ?? 0)
                ];
            } catch (Exception $e) {
                error_log("Manufacturers stats error: " . $e->getMessage());
                $stats['manufacturers'] = ['total' => 0, 'active' => 0, 'inactive' => 0];
            }
            
            // 1.3. Пользователи
            try {
                $usersTotal = Database::selectOne("SELECT COUNT(*) as total FROM users");
                $usersActive = Database::selectOne("SELECT COUNT(*) as count FROM users WHERE is_active = TRUE");
                
                $stats['users'] = [
                    'total' => (int)($usersTotal['total'] ?? 0),
                    'active' => (int)($usersActive['count'] ?? 0),
                    'inactive' => (int)($usersTotal['total'] ?? 0) - (int)($usersActive['count'] ?? 0)
                ];
            } catch (Exception $e) {
                error_log("Users stats error: " . $e->getMessage());
                $stats['users'] = ['total' => 0, 'active' => 0, 'inactive' => 0];
            }
            
            // 1.4. Ж/Д компании
            try {
                $railTotal = Database::selectOne("SELECT COUNT(*) as total FROM railway_companies");
                $railCountries = Database::selectOne("SELECT COUNT(DISTINCT country_code) as count FROM railway_companies WHERE country_code IS NOT NULL");
                
                $stats['railways'] = [
                    'total' => (int)($railTotal['total'] ?? 0),
                    'countries' => (int)($railCountries['count'] ?? 0)
                ];
            } catch (Exception $e) {
                error_log("Railways stats error: " . $e->getMessage());
                $stats['railways'] = ['total' => 0, 'countries' => 0];
            }
            
            // 1.5. Коллекции
            try {
                $colTotal = Database::selectOne("SELECT COUNT(*) as total FROM personal_collections");
                $colPublic = Database::selectOne("SELECT COUNT(*) as count FROM personal_collections WHERE is_public = TRUE");
                
                $stats['collections'] = [
                    'total' => (int)($colTotal['total'] ?? 0),
                    'public' => (int)($colPublic['count'] ?? 0),
                    'private' => (int)($colTotal['total'] ?? 0) - (int)($colPublic['count'] ?? 0)
                ];
            } catch (Exception $e) {
                $stats['collections'] = ['total' => 0, 'public' => 0, 'private' => 0];
            }
            
            // 1.6. Уникальные производители с моделями
            try {
                $uniqueMan = Database::selectOne("SELECT COUNT(DISTINCT manufacturer_id) as count FROM catalog_models");
                $stats['unique_manufacturers'] = (int)($uniqueMan['count'] ?? 0);
            } catch (Exception $e) {
                $stats['unique_manufacturers'] = 0;
            }
            
            return [
                'success' => true,
                'timestamp' => date('Y-m-d H:i:s'),
                'data' => $stats
            ];
            
        } catch (Exception $e) {
            error_log("Overall stats error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Ошибка получения статистики',
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    // 2. Статистика по моделям
    public static function getModelsStats() {
        try {
            $stats = [];
            
            // 2.1. По типам
            $byType = Database::select("
                SELECT type_code, COUNT(*) as count 
                FROM catalog_models 
                WHERE type_code IS NOT NULL
                GROUP BY type_code 
                ORDER BY count DESC
            ");
            $stats['by_type'] = $byType ?: [];
            
            // 2.2. По подтипам (топ 10)
            $bySubtype = Database::select("
                SELECT subtype_code, COUNT(*) as count 
                FROM catalog_models 
                WHERE subtype_code IS NOT NULL
                GROUP BY subtype_code 
                ORDER BY count DESC
                LIMIT 10
            ");
            $stats['by_subtype'] = $bySubtype ?: [];
            
            // 2.3. По типоразмерам
            $byScale = Database::select("
                SELECT scale_code, COUNT(*) as count 
                FROM catalog_models 
                WHERE scale_code IS NOT NULL
                GROUP BY scale_code 
                ORDER BY count DESC
            ");
            $stats['by_scale'] = $byScale ?: [];
            
            // 2.4. По производителям (топ 5)
            $byManufacturer = Database::select("
                SELECT m.name as manufacturer_name, COUNT(cm.id) as count
                FROM catalog_models cm
                JOIN manufacturers m ON cm.manufacturer_id = m.id
                GROUP BY cm.manufacturer_id
                ORDER BY count DESC
                LIMIT 5
            ");
            $stats['by_manufacturer'] = $byManufacturer ?: [];
            
            // 2.5. По эпохам (top 10)
            $byEra = Database::select("
                SELECT era_code, COUNT(*) as count 
                FROM catalog_models 
                WHERE era_code IS NOT NULL
                GROUP BY era_code 
                ORDER BY count DESC
                LIMIT 10
            ");
            $stats['by_era'] = $byEra ?: [];
            
            // 2.6. По освещению
            $stats['by_lighting'] = [
                'with_lights' => (int) Database::selectOne("SELECT COUNT(*) as count FROM catalog_models WHERE has_lights = TRUE")['count'],
                'without_lights' => (int) Database::selectOne("SELECT COUNT(*) as count FROM catalog_models WHERE has_lights = FALSE OR has_lights IS NULL")['count']
            ];
            
            // 2.7. По цифровым системам
            $stats['by_dcc'] = [
                'with_dcc' => (int) Database::selectOne("SELECT COUNT(*) as count FROM catalog_models WHERE has_dcc_decoder = TRUE")['count'],
                'without_dcc' => (int) Database::selectOne("SELECT COUNT(*) as count FROM catalog_models WHERE has_dcc_decoder = FALSE OR has_dcc_decoder IS NULL")['count']
            ];
            
            // 2.8. По ручным доработкам
            $stats['by_manual_work'] = [
                'with_electric' => (int) Database::selectOne("SELECT COUNT(*) as count FROM catalog_models WHERE manual_electric = TRUE")['count'],
                'with_painting' => (int) Database::selectOne("SELECT COUNT(*) as count FROM catalog_models WHERE manual_painting = TRUE")['count']
            ];
            
            return [
                'success' => true,
                'timestamp' => date('Y-m-d H:i:s'),
                'data' => $stats
            ];
            
        } catch (Exception $e) {
            error_log("Models stats error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Ошибка получения статистики моделей',
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    // 3. Статистика по производителям
    public static function getManufacturersStats() {
        try {
            $stats = [];
            
            // 3.1. По странам (топ 10)
            $byCountry = Database::select("
                SELECT country_code, COUNT(*) as count
                FROM manufacturers 
                WHERE country_code IS NOT NULL
                GROUP BY country_code 
                ORDER BY count DESC
                LIMIT 10
            ");
            $stats['by_country'] = $byCountry ?: [];
            
            // 3.2. С моделями/без моделей
            $withModels = Database::selectOne("
                SELECT 
                    COUNT(DISTINCT cm.manufacturer_id) as with_models,
                    (SELECT COUNT(*) FROM manufacturers) - COUNT(DISTINCT cm.manufacturer_id) as without_models
                FROM catalog_models cm
            ");
            $stats['with_models'] = $withModels ?: ['with_models' => 0, 'without_models' => 0];
            
            return [
                'success' => true,
                'timestamp' => date('Y-m-d H:i:s'),
                'data' => $stats
            ];
            
        } catch (Exception $e) {
            error_log("Manufacturers stats error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Ошибка получения статистики производителей',
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    // 4. Статистика по пользователям
    public static function getUsersStats() {
        try {
            $stats = [];
            
            // 4.1. По ролям
            $byRole = Database::select("
                SELECT role, COUNT(*) as count
                FROM users 
                GROUP BY role 
                ORDER BY 
                    CASE role
                        WHEN 'root' THEN 1
                        WHEN 'admin' THEN 2
                        WHEN 'editor' THEN 3
                        WHEN 'viewer' THEN 4
                        ELSE 5
                    END
            ");
            $stats['by_role'] = $byRole ?: [];
            
            // 4.2. По статусу активности
            $byStatus = Database::select("SELECT is_active, COUNT(*) as count FROM users GROUP BY is_active");
            $stats['by_status'] = $byStatus ?: [];
            
            // 4.3. По языку
            $byLanguage = Database::select("SELECT language, COUNT(*) as count FROM users WHERE language IS NOT NULL GROUP BY language ORDER BY count DESC");
            $stats['by_language'] = $byLanguage ?: [];
            
            // 4.4. По последнему входу (активность)
            $byLastLogin = [
                'active_last_7_days' => (int) Database::selectOne("SELECT COUNT(*) as count FROM users WHERE last_login >= DATE_SUB(NOW(), INTERVAL 7 DAY)")['count'],
                'active_last_30_days' => (int) Database::selectOne("SELECT COUNT(*) as count FROM users WHERE last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY)")['count'],
                'inactive_over_30_days' => (int) Database::selectOne("SELECT COUNT(*) as count FROM users WHERE (last_login < DATE_SUB(NOW(), INTERVAL 30 DAY) OR last_login IS NULL) AND is_active = TRUE")['count'],
            ];
            $stats['by_last_login'] = $byLastLogin;
            
            return [
                'success' => true,
                'timestamp' => date('Y-m-d H:i:s'),
                'data' => $stats
            ];
            
        } catch (Exception $e) {
            error_log("Users stats error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Ошибка получения статистики пользователей',
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    // 5. Статистика по Ж/Д компаниям
    public static function getRailwaysStats() {
        try {
            $stats = [];
            
            // 5.1. Компании с количеством моделей
            $modelsByRailway = Database::select("
                SELECT 
                    rc.id,
                    rc.code,
                    rc.name,
                    rc.country_code,
                    COUNT(cm.id) as model_count
                FROM railway_companies rc
                LEFT JOIN catalog_models cm ON cm.railway_company = rc.id
                GROUP BY rc.id
                ORDER BY model_count DESC, rc.name
            ");
            $stats['models_by_railway'] = $modelsByRailway ?: [];
            
            // 5.2. По странам
            $byCountry = Database::select("
                SELECT country_code, COUNT(*) as count
                FROM railway_companies 
                WHERE country_code IS NOT NULL
                GROUP BY country_code 
                ORDER BY count DESC
            ");
            $stats['by_country'] = $byCountry ?: [];
            
            // 5.3. По году основания
            $byFoundedYear = Database::select("
                SELECT 
                    CASE 
                        WHEN founded_year < 1900 THEN 'До 1900'
                        WHEN founded_year < 1950 THEN '1900-1949'
                        WHEN founded_year < 2000 THEN '1950-1999'
                        ELSE '2000 и позже'
                    END as period,
                    COUNT(*) as count
                FROM railway_companies 
                WHERE founded_year IS NOT NULL
                GROUP BY period
                ORDER BY 
                    CASE period
                        WHEN 'До 1900' THEN 1
                        WHEN '1900-1949' THEN 2
                        WHEN '1950-1999' THEN 3
                        WHEN '2000 и позже' THEN 4
                    END
            ");
            $stats['by_founded_period'] = $byFoundedYear ?: [];
            
            return [
                'success' => true,
                'timestamp' => date('Y-m-d H:i:s'),
                'data' => $stats
            ];
            
        } catch (Exception $e) {
            error_log("Railways stats error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Ошибка получения статистики Ж/Д компаний',
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    // 6. Статистика по типоразмерам
    public static function getScalesStats() {
        try {
            $stats = [];
            
            // 6.1. Список типоразмеров
            $scales = Database::select("
                SELECT code, name_en, scale_ratio
                FROM scales 
                ORDER BY code
            ");
            $stats['scales'] = $scales ?: [];
            
            // 6.2. Модели по типоразмерам
            $modelsByScale = Database::select("
                SELECT scale_code, COUNT(*) as model_count
                FROM catalog_models 
                WHERE scale_code IS NOT NULL
                GROUP BY scale_code 
                ORDER BY model_count DESC
            ");
            $stats['models_by_scale'] = $modelsByScale ?: [];
            
            // 6.3. По колее
            $byGauge = Database::select("
                SELECT 
                    CASE 
                        WHEN track_gauge_mm < 10 THEN 'Очень узкая (<10мм)'
                        WHEN track_gauge_mm < 15 THEN 'Узкая (10-14.9мм)'
                        WHEN track_gauge_mm < 20 THEN 'Стандартная (15-19.9мм)'
                        ELSE 'Широкая (>=20мм)'
                    END as gauge_type,
                    COUNT(*) as count
                FROM scales 
                WHERE track_gauge_mm IS NOT NULL
                GROUP BY gauge_type
                ORDER BY 
                    CASE gauge_type
                        WHEN 'Очень узкая (<10мм)' THEN 1
                        WHEN 'Узкая (10-14.9мм)' THEN 2
                        WHEN 'Стандартная (15-19.9мм)' THEN 3
                        WHEN 'Широкая (>=20мм)' THEN 4
                    END
            ");
            $stats['by_gauge_type'] = $byGauge ?: [];
            
            return [
                'success' => true,
                'timestamp' => date('Y-m-d H:i:s'),
                'data' => $stats
            ];
            
        } catch (Exception $e) {
            error_log("Scales stats error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Ошибка получения статистики типоразмеров',
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    // 7. Статистика по типам моделей
    public static function getTypesStats() {
        try {
            $stats = [];
            
            // 7.1. Типы моделей из таблицы model_types
            $modelTypes = Database::select("
                SELECT code
                FROM model_types 
                ORDER BY code
            ");
            $stats['model_types'] = $modelTypes ?: [];
            
            // 7.2. Подтипы из таблицы model_subtypes
            $modelSubtypes = Database::select("
                SELECT code, type_code
                FROM model_subtypes 
                ORDER BY type_code, code
            ");
            $stats['model_subtypes'] = $modelSubtypes ?: [];
            
            // 7.3. Группировка подтипов по типам
            $subtypesByType = [];
            foreach ($modelSubtypes as $subtype) {
                $typeCode = $subtype['type_code'] ?? 'other';
                if (!isset($subtypesByType[$typeCode])) {
                    $subtypesByType[$typeCode] = [];
                }
                $subtypesByType[$typeCode][] = $subtype['code'];
            }
            $stats['subtypes_by_type'] = $subtypesByType;
            
            // 7.4. Количество моделей по типам
            $modelsByType = Database::select("
                SELECT type_code, COUNT(*) as count
                FROM catalog_models 
                WHERE type_code IS NOT NULL
                GROUP BY type_code
                ORDER BY count DESC
            ");
            $stats['models_by_type'] = $modelsByType ?: [];
            
            // 7.5. Количество моделей по подтипам
            $modelsBySubtype = Database::select("
                SELECT subtype_code, COUNT(*) as count
                FROM catalog_models 
                WHERE subtype_code IS NOT NULL
                GROUP BY subtype_code
                ORDER BY count DESC
                LIMIT 20
            ");
            $stats['models_by_subtype'] = $modelsBySubtype ?: [];
            
            return [
                'success' => true,
                'timestamp' => date('Y-m-d H:i:s'),
                'data' => $stats
            ];
            
        } catch (Exception $e) {
            error_log("Types stats error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Ошибка получения статистики типов моделей',
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    // 8. Статистика по коллекциям
    public static function getCollectionsStats() {
        try {
            $stats = [];
            
            // Проверяем существование таблицы
            $tables = Database::select("SHOW TABLES LIKE 'personal_collections'");
            if (count($tables) === 0) {
                $stats['note'] = 'Таблица коллекций не существует';
                return [
                    'success' => true,
                    'timestamp' => date('Y-m-d H:i:s'),
                    'data' => $stats
                ];
            }
            
            // 8.1. По пользователям (топ 10)
            $byUser = Database::select("
                SELECT u.username, COUNT(pc.id) as collection_count
                FROM personal_collections pc
                JOIN users u ON pc.user_id = u.id
                GROUP BY u.id
                ORDER BY collection_count DESC
                LIMIT 10
            ");
            $stats['by_user'] = $byUser ?: [];
            
            // 8.2. По условию
            $byCondition = Database::select("
                SELECT condition_code, COUNT(*) as count
                FROM personal_collections 
                WHERE condition_code IS NOT NULL
                GROUP BY condition_code 
                ORDER BY count DESC
            ");
            $stats['by_condition'] = $byCondition ?: [];
            
            // 8.3. По видимости
            $byVisibility = Database::select("
                SELECT is_public, COUNT(*) as count
                FROM personal_collections 
                GROUP BY is_public
            ");
            $stats['by_visibility'] = $byVisibility ?: [];
            
            // 8.4. Общая стоимость коллекций
            $totalValue = Database::selectOne("
                SELECT 
                    SUM(purchase_price) as total_purchase_value,
                    COUNT(*) as total_items
                FROM personal_collections 
                WHERE purchase_price IS NOT NULL
            ");
            $stats['total_value'] = [
                'total_purchase_value' => (float)($totalValue['total_purchase_value'] ?? 0),
                'total_items' => (int)($totalValue['total_items'] ?? 0)
            ];
            
            return [
                'success' => true,
                'timestamp' => date('Y-m-d H:i:s'),
                'data' => $stats
            ];
            
        } catch (Exception $e) {
            error_log("Collections stats error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Ошибка получения статистики коллекций',
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
}
?>