getConnection();
// Get statistics
try {
// Overall stats
$overall_stmt = $conn->query("
SELECT
COUNT(*) as total_results,
AVG(score) as avg_score,
COUNT(CASE WHEN is_contacted = 1 THEN 1 END) as contacted_count,
COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as this_week,
COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as this_month
FROM skin_quiz_results
");
$overall = $overall_stmt->fetch(PDO::FETCH_ASSOC);
// Skin type distribution
$skin_type_stmt = $conn->query("
SELECT
skin_type,
COUNT(*) as count,
AVG(score) as avg_score
FROM skin_quiz_results
GROUP BY skin_type
ORDER BY count DESC
");
$skin_types = $skin_type_stmt->fetchAll(PDO::FETCH_ASSOC);
// Age distribution
$age_stmt = $conn->query("
SELECT
CASE
WHEN age < 20 THEN 'أقل من 20'
WHEN age BETWEEN 20 AND 29 THEN '20-29'
WHEN age BETWEEN 30 AND 39 THEN '30-39'
WHEN age BETWEEN 40 AND 49 THEN '40-49'
ELSE '50+'
END as age_group,
COUNT(*) as count
FROM skin_quiz_results
GROUP BY age_group
ORDER BY age_group
");
$age_groups = $age_stmt->fetchAll(PDO::FETCH_ASSOC);
// Gender distribution
$gender_stmt = $conn->query("
SELECT
gender,
COUNT(*) as count
FROM skin_quiz_results
GROUP BY gender
");
$genders = $gender_stmt->fetchAll(PDO::FETCH_ASSOC);
// Top concerns
$concerns_data = [];
$all_results = $conn->query("SELECT skin_concerns FROM skin_quiz_results WHERE skin_concerns IS NOT NULL")->fetchAll(PDO::FETCH_ASSOC);
foreach ($all_results as $result) {
$concerns = json_decode($result['skin_concerns'], true);
if (is_array($concerns)) {
foreach ($concerns as $concern) {
if (!isset($concerns_data[$concern])) {
$concerns_data[$concern] = 0;
}
$concerns_data[$concern]++;
}
}
}
arsort($concerns_data);
// Daily results (last 30 days)
$daily_stmt = $conn->query("
SELECT
DATE(created_at) as date,
COUNT(*) as count
FROM skin_quiz_results
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date ASC
");
$daily_results = $daily_stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
$overall = ['total_results' => 0, 'avg_score' => 0, 'contacted_count' => 0, 'this_week' => 0, 'this_month' => 0];
$skin_types = [];
$age_groups = [];
$genders = [];
$concerns_data = [];
$daily_results = [];
}
$skinTypeNames = [
'oily' => 'دهنية',
'dry' => 'جافة',
'combination' => 'مختلطة',
'normal' => 'عادية',
'sensitive' => 'حساسة'
];
$concernNames = [
'acne' => 'حب الشباب',
'dark_spots' => 'البقع الداكنة',
'wrinkles' => 'التجاعيد',
'dryness' => 'الجفاف',
'large_pores' => 'المسام الواسعة',
'dullness' => 'البشرة الباهتة',
'dark_circles' => 'الهالات السوداء',
'redness' => 'الاحمرار'
];
?>
إحصائيات اختبار البشرة - Roz Skin