/home/awneajlw/.trash/sale-record.php
<?php
/**
 * Sale Record Page - Sales Dashboard
 * This page displays sales analytics with KPI gauges and charts
 * Features: Revenue tracking, expense monitoring, monthly sales data visualization
 */

// Start session if not already started
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

// Include required files
require_once 'config/database.php';  // Database connection configuration
require_once 'includes/auth.php';    // Authentication functions

/**
 * Authentication Check
 * Redirect to welcome page if user is not logged in
 */
if (!isLoggedIn()) {
    header('Location: welcome.php');
    exit();
}

// Initialize variables for sales data
$sales_data = [];         // Array to store sales records
$total_amount = 0;        // Total sales amount
$total_revenue = 0;       // Total revenue calculated
$total_expense = 0;       // Total expenses calculated
$monthly_data = [];       // Monthly sales data for charts

/**
 * Database Connection and Sales Data Retrieval
 * Fetch sales data from orders table for analytics
 */
try {
    $database = new Database();
    $db = $database->getConnection();
    
    // Create orders table if it doesn't exist
    $create_table_sql = "CREATE TABLE IF NOT EXISTS orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        tracking_id VARCHAR(50) DEFAULT NULL,
        patient_name VARCHAR(255) NOT NULL,
        whatsapp_number VARCHAR(20) NOT NULL,
        frame_detail VARCHAR(255) DEFAULT NULL,
        lens_type VARCHAR(255) DEFAULT NULL,
        total_amount DECIMAL(10,2) NOT NULL,
        advance DECIMAL(10,2) DEFAULT 0,
        balance DECIMAL(10,2) DEFAULT 0,
        delivery_date DATE DEFAULT NULL,
        right_sph VARCHAR(10) DEFAULT NULL,
        right_cyl VARCHAR(10) DEFAULT NULL,
        right_axis VARCHAR(10) DEFAULT NULL,
        left_sph VARCHAR(10) DEFAULT NULL,
        left_cyl VARCHAR(10) DEFAULT NULL,
        left_axis VARCHAR(10) DEFAULT NULL,
        add_power VARCHAR(10) DEFAULT NULL,
        important_note TEXT DEFAULT NULL,
        description TEXT DEFAULT NULL,
        status VARCHAR(20) DEFAULT 'Pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_tracking_id (tracking_id),
        INDEX idx_patient_name (patient_name),
        INDEX idx_created_at (created_at)
    )";
    
    $db->exec($create_table_sql);
    
    $user_id = $_SESSION['user_id'];
    
    // Get sales data for current user
    $query = "SELECT 
                SUM(total_amount) as total_amount,
                SUM(advance) as total_revenue,
                SUM(balance) as total_expense,
                MONTH(created_at) as month,
                SUM(total_amount) as monthly_amount
              FROM orders 
              WHERE user_id = ? AND status = 'Completed'
              GROUP BY MONTH(created_at)
              ORDER BY month";
    $stmt = $db->prepare($query);
    $stmt->execute([$user_id]);
    $monthly_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get total sales data
    $total_query = "SELECT 
                      SUM(total_amount) as total_amount,
                      SUM(advance) as total_revenue,
                      SUM(balance) as total_expense
                    FROM orders 
                    WHERE user_id = ? AND status = 'Completed'";
    $total_stmt = $db->prepare($total_query);
    $total_stmt->execute([$user_id]);
    $totals = $total_stmt->fetch(PDO::FETCH_ASSOC);
    
    $total_amount = $totals['total_amount'] ?? 0;
    $total_revenue = $totals['total_revenue'] ?? 0;
    $total_expense = $totals['total_expense'] ?? 0;
    
    // Prepare monthly data for chart
    $monthly_data = [];
    $month_names = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
    
    for ($i = 1; $i <= 12; $i++) {
        $monthly_data[] = [
            'month' => $month_names[$i - 1],
            'amount' => 0
        ];
    }
    
    foreach ($monthly_results as $result) {
        if ($result['month'] >= 1 && $result['month'] <= 12) {
            $monthly_data[$result['month'] - 1]['amount'] = (float)$result['monthly_amount'];
        }
    }
    
} catch (Exception $e) {
    $error_message = 'Database error: ' . $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Sale Record - OPTI SLIP</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }
        
        body {
            font-family: 'Inter', sans-serif;
            background: linear-gradient(135deg, #1e3a8a 0%, #3730a3 100%);
            min-height: 100vh;
            padding: 20px;
        }
        
        .container {
            max-width: 1200px;
            margin: 0 auto;
        }
        
        .header {
            display: flex;
            align-items: center;
            margin-bottom: 30px;
            gap: 15px;
        }
        
        .back-btn {
            background: #10b981;
            color: white;
            border: none;
            border-radius: 8px;
            padding: 10px 20px;
            display: flex;
            align-items: center;
            gap: 8px;
            cursor: pointer;
            transition: all 0.3s ease;
            text-decoration: none;
            font-weight: 500;
        }
        
        .back-btn:hover {
            background: #059669;
            transform: scale(1.05);
            color: white;
        }
        
        .page-title {
            color: white;
            font-size: 24px;
            font-weight: 600;
        }
        
        .dashboard-card {
            background: white;
            border-radius: 20px;
            padding: 30px;
            box-shadow: 0 20px 40px rgba(0,0,0,0.1);
        }
        
        .logo-section {
            display: flex;
            align-items: center;
            justify-content: center;
            gap: 15px;
            margin-bottom: 40px;
        }
        
        .logo-icon {
            width: 50px;
            height: 50px;
            background: linear-gradient(135deg, #10b981 0%, #059669 100%);
            border-radius: 12px;
            display: flex;
            align-items: center;
            justify-content: center;
            color: white;
            font-size: 24px;
        }
        
        .logo-text {
            font-size: 28px;
            font-weight: 700;
            color: #1e3a8a;
        }
        
        .kpi-section {
            display: grid;
            grid-template-columns: repeat(3, 1fr);
            gap: 30px;
            margin-bottom: 40px;
        }
        
        .kpi-gauge {
            display: flex;
            flex-direction: column;
            align-items: center;
            gap: 15px;
        }
        
        .gauge-container {
            position: relative;
            width: 150px;
            height: 150px;
        }
        
        .gauge-circle {
            width: 100%;
            height: 100%;
            border-radius: 50%;
            position: relative;
            background: #f3f4f6;
            border: 12px solid #e5e7eb;
            display: flex;
            align-items: center;
            justify-content: center;
        }
        
        .gauge-fill {
            position: absolute;
            top: -12px;
            left: -12px;
            width: calc(100% + 24px);
            height: calc(100% + 24px);
            border-radius: 50%;
            background: conic-gradient(from 0deg, var(--gauge-color) 0deg, var(--gauge-color) var(--gauge-percentage), transparent var(--gauge-percentage));
            transition: all 0.5s ease;
            z-index: 1;
        }
        
        .gauge-mask {
            position: absolute;
            top: 50%;
            left: 50%;
            transform: translate(-50%, -50%);
            width: 70%;
            height: 70%;
            background: white;
            border-radius: 50%;
            z-index: 2;
        }
        
        .gauge-inner {
            position: absolute;
            top: 50%;
            left: 50%;
            transform: translate(-50%, -50%);
            width: 70%;
            height: 70%;
            background: white;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: 600;
            font-size: 14px;
            color: #374151;
            z-index: 3;
        }
        
        .amount-gauge {
            --gauge-color: #3b82f6;
        }
        
        .revenue-gauge {
            --gauge-color: #8b5cf6;
        }
        
        .expense-gauge {
            --gauge-color: #10b981;
        }
        
        .kpi-label {
            font-size: 16px;
            font-weight: 600;
            color: #374151;
            text-align: center;
        }
        
        .date-range-section {
            display: flex;
            align-items: center;
            justify-content: center;
            gap: 10px;
            margin-bottom: 40px;
        }
        
        .date-range {
            display: flex;
            align-items: center;
            gap: 10px;
            background: #f8fafc;
            padding: 12px 20px;
            border-radius: 10px;
            border: 1px solid #e2e8f0;
        }
        
        .date-text {
            color: #475569;
            font-weight: 500;
            font-size: 16px;
        }
        
        .calendar-icon {
            color: #64748b;
            font-size: 18px;
        }
        
        .chart-section {
            background: #f8fafc;
            border-radius: 15px;
            padding: 30px;
            border: 1px solid #e2e8f0;
        }
        
        .chart-header {
            display: flex;
            align-items: center;
            justify-content: space-between;
            margin-bottom: 30px;
            flex-wrap: wrap;
            gap: 20px;
        }
        
        .chart-title {
            font-size: 18px;
            font-weight: 600;
            color: #374151;
        }
        
        .chart-type {
            display: flex;
            align-items: center;
            gap: 10px;
            color: #6b7280;
            font-size: 14px;
        }
        
        .chart-container {
            position: relative;
            height: 400px;
            width: 100%;
        }
        
        @media (max-width: 768px) {
            .dashboard-card {
                padding: 20px;
                margin: 0 10px;
            }
            
            .kpi-section {
                grid-template-columns: 1fr;
                gap: 20px;
            }
            
            .gauge-container {
                width: 120px;
                height: 120px;
            }
            
            .logo-section {
                flex-direction: column;
                gap: 15px;
            }
            
            .chart-container {
                height: 300px;
            }
            
            .chart-header {
                flex-direction: column;
                align-items: flex-start;
                gap: 15px;
            }
        }
        
        @media (max-width: 480px) {
            .gauge-container {
                width: 100px;
                height: 100px;
            }
            
            .gauge-inner {
                font-size: 12px;
            }
            
            .kpi-label {
                font-size: 14px;
            }
            
            .chart-container {
                height: 250px;
            }
        }
    </style>
</head>
<body>
    <div class="container">
        <!-- Header -->
        <div class="header">
            <a href="home.php" class="back-btn">
                <i class="fas fa-arrow-left"></i>
                Back
            </a>
            <h1 class="page-title">sale Record</h1>
        </div>
        
        <!-- Dashboard Card -->
        <div class="dashboard-card">
            <!-- Logo Section -->
            <div class="logo-section">
                <div class="logo-icon">
                    <i class="fas fa-glasses"></i>
                </div>
                <div class="logo-icon">
                    <i class="fas fa-phone"></i>
                </div>
                <div class="logo-text">OPTI SLIP</div>
            </div>
            
            <!-- KPI Gauges -->
            <div class="kpi-section">
                <div class="kpi-gauge">
                    <div class="gauge-container">
                        <div class="gauge-circle">
                            <div class="gauge-fill amount-gauge" style="--gauge-percentage: <?php echo min(($total_amount / 100000) * 360, 360); ?>deg;"></div>
                            <div class="gauge-mask"></div>
                            <div class="gauge-inner">AMOUNT</div>
                        </div>
                    </div>
                    <div class="kpi-label">AMOUNT</div>
                </div>
                
                <div class="kpi-gauge">
                    <div class="gauge-container">
                        <div class="gauge-circle">
                            <div class="gauge-fill revenue-gauge" style="--gauge-percentage: <?php echo min(($total_revenue / 50000) * 360, 360); ?>deg;"></div>
                            <div class="gauge-mask"></div>
                            <div class="gauge-inner">REVENUE</div>
                        </div>
                    </div>
                    <div class="kpi-label">REVENUE</div>
                </div>
                
                <div class="kpi-gauge">
                    <div class="gauge-container">
                        <div class="gauge-circle">
                            <div class="gauge-fill expense-gauge" style="--gauge-percentage: <?php echo min(($total_expense / 30000) * 360, 360); ?>deg;"></div>
                            <div class="gauge-mask"></div>
                            <div class="gauge-inner">EXPENSE</div>
                        </div>
                    </div>
                    <div class="kpi-label">EXPENSE</div>
                </div>
            </div>
            
            <!-- Date Range -->
            <div class="date-range-section">
                <div class="date-range">
                    <i class="fas fa-calendar-alt calendar-icon"></i>
                    <span class="date-text">Jan 1 To Jan 30</span>
                    <i class="fas fa-calendar-alt calendar-icon"></i>
                </div>
            </div>
            
            <!-- Chart Section -->
            <div class="chart-section">
                <div class="chart-header">
                    <h3 class="chart-title">Chart Type:</h3>
                    <div class="chart-type">
                        <span>Line Chart</span>
                        <i class="fas fa-chart-line"></i>
                    </div>
                </div>
                
                <div class="chart-container">
                    <canvas id="salesChart"></canvas>
                </div>
            </div>
        </div>
    </div>
    
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Chart.js configuration
        const ctx = document.getElementById('salesChart').getContext('2d');
        
        const monthlyData = <?php echo json_encode($monthly_data); ?>;
        
        const chart = new Chart(ctx, {
            type: 'line',
            data: {
                labels: monthlyData.map(item => item.month),
                datasets: [{
                    label: 'Amount',
                    data: monthlyData.map(item => item.amount),
                    borderColor: '#10b981',
                    backgroundColor: 'rgba(16, 185, 129, 0.1)',
                    borderWidth: 3,
                    fill: true,
                    tension: 0.4,
                    pointBackgroundColor: '#10b981',
                    pointBorderColor: '#ffffff',
                    pointBorderWidth: 2,
                    pointRadius: 6,
                    pointHoverRadius: 8
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        display: false
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        max: 50000,
                        ticks: {
                            stepSize: 10000,
                            callback: function(value) {
                                return value.toLocaleString();
                            }
                        },
                        grid: {
                            color: '#e5e7eb'
                        }
                    },
                    x: {
                        grid: {
                            display: false
                        }
                    }
                },
                elements: {
                    point: {
                        hoverBackgroundColor: '#059669'
                    }
                }
            }
        });
        
        // Auto refresh every 30 seconds
        setTimeout(function() {
            location.reload();
        }, 30000);
    </script>
</body>
</html>
Path: home/awneajlw/.trash