MAINHACK

Viewing / Editing File: representative_detailed.php

<?php
require_once '../config/database.php';
require_once '../config/functions.php';

requireAuth('accountant');

// Get date range and representative
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$representative_id = $_GET['representative_id'] ?? '';
$overdue_days = $_GET['overdue_days'] ?? 45;

// Get all representatives
$stmt = $pdo->prepare("SELECT id, full_name FROM users WHERE role = 'representative' ORDER BY full_name ASC");
$stmt->execute();
$representatives = $stmt->fetchAll();

if (!$representative_id) {
    $representative_id = $representatives[0]['id'] ?? '';
}

// Get representative info
$stmt = $pdo->prepare("SELECT full_name FROM users WHERE id = ?");
$stmt->execute([$representative_id]);
$rep_info = $stmt->fetch();

// Get detailed sales data
$stmt = $pdo->prepare("
    SELECT 
        i.id as invoice_id,
        i.invoice_number,
        i.created_at,
        i.invoice_total,
        i.amount_paid_at_creation,
        i.remaining_amount,
        i.status,
        c.id as client_id,
        c.name as client_name,
        c.phone as client_phone,
        c.balance as current_client_balance,
        COALESCE(MAX(p.created_at), '1970-01-01') as last_payment_date,
        DATEDIFF(CURDATE(), COALESCE(MAX(p.created_at), i.created_at)) as days_since_payment
    FROM invoices i
    JOIN clients c ON i.client_id = c.id
    LEFT JOIN payments p ON c.id = p.client_id
    WHERE i.representative_id = ? AND DATE(i.created_at) BETWEEN ? AND ?
    GROUP BY i.id, i.invoice_number, i.created_at, i.invoice_total, i.amount_paid_at_creation, 
             i.remaining_amount, i.status, c.id, c.name, c.phone, c.balance
    ORDER BY i.created_at DESC
");
$stmt->execute([$representative_id, $date_from, $date_to]);
$invoices = $stmt->fetchAll();

// Get product sales summary
$stmt = $pdo->prepare("
    SELECT 
        p.name as product_name,
        p.unit,
        SUM(ii.quantity) as total_quantity,
        SUM(ii.line_total) as total_value,
        COUNT(DISTINCT i.id) as invoice_count
    FROM invoice_items ii
    JOIN invoices i ON ii.invoice_id = i.id
    JOIN products p ON ii.product_id = p.id
    WHERE i.representative_id = ? AND DATE(i.created_at) BETWEEN ? AND ?
    GROUP BY p.id, p.name, p.unit
    ORDER BY total_value DESC
");
$stmt->execute([$representative_id, $date_from, $date_to]);
$product_sales = $stmt->fetchAll();

// Get payments for this representative's clients
$stmt = $pdo->prepare("
    SELECT 
        p.id,
        p.receipt_number,
        p.amount,
        p.created_at,
        c.name as client_name,
        i.invoice_number
    FROM payments p
    JOIN clients c ON p.client_id = c.id
    LEFT JOIN invoices i ON p.linked_invoice_id = i.id
    WHERE p.representative_id = ? AND DATE(p.created_at) BETWEEN ? AND ?
    ORDER BY p.created_at DESC
");
$stmt->execute([$representative_id, $date_from, $date_to]);
$payments = $stmt->fetchAll();

// Calculate totals
$total_invoices = count($invoices);
$total_sales = array_sum(array_column($invoices, 'invoice_total'));
$total_paid = array_sum(array_column($invoices, 'amount_paid_at_creation')) + array_sum(array_column($payments, 'amount'));
$total_remaining = array_sum(array_column($invoices, 'remaining_amount'));

// Group by clients
$clients_data = [];
foreach ($invoices as $invoice) {
    $client_id = $invoice['client_id'];
    if (!isset($clients_data[$client_id])) {
        $clients_data[$client_id] = [
            'name' => $invoice['client_name'],
            'phone' => $invoice['client_phone'],
            'current_balance' => $invoice['current_client_balance'],
            'invoices' => [],
            'total_sales' => 0,
            'total_paid' => 0,
            'total_remaining' => 0,
            'days_since_payment' => $invoice['days_since_payment']
        ];
    }
    $clients_data[$client_id]['invoices'][] = $invoice;
    $clients_data[$client_id]['total_sales'] += $invoice['invoice_total'];
    $clients_data[$client_id]['total_paid'] += $invoice['amount_paid_at_creation'];
    $clients_data[$client_id]['total_remaining'] += $invoice['remaining_amount'];
}
?>

<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>تقرير مفصل للمندوب - حسابات عربية بن فريش</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.rtl.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <style>
        .report-header {
            background: linear-gradient(135deg, #198754, #20c997);
            color: white;
            border-radius: 15px;
        }
        .client-card {
            border-radius: 10px;
            border-left: 4px solid #0d6efd;
            transition: all 0.3s ease;
        }
        .client-overdue {
            border-left-color: #dc3545;
            background: #fff5f5;
        }
        .product-summary {
            background: #f8f9fa;
            border-radius: 10px;
            padding: 15px;
            margin-bottom: 20px;
        }
        @media print {
            .no-print { display: none !important; }
        }
    </style>
</head>
<body class="bg-light">
    <div class="container-fluid mt-4">
        <div class="d-flex justify-content-between align-items-center mb-4 no-print">
            <h2><i class="fas fa-user-chart me-2"></i>تقرير مفصل للمندوب</h2>
            <div>
                <button class="btn btn-success me-2" onclick="window.print()">
                    <i class="fas fa-print me-1"></i>طباعة التقرير
                </button>
                <a href="/reports/index.php" class="btn btn-outline-secondary">
                    <i class="fas fa-arrow-left me-1"></i>العودة
                </a>
            </div>
        </div>

<div class="report-header p-4 mb-4 no-print">
            <h5 class="mb-3"><i class="fas fa-filter me-2"></i>تصفية التقرير</h5>
            <form method="GET" class="row align-items-end">
                <div class="col-md-3">
                    <label class="form-label">المندوب</label>
                    <select class="form-select" name="representative_id" required>
                        <?php foreach ($representatives as $rep): ?>
                            <option value="<?= $rep['id'] ?>" <?= $representative_id == $rep['id'] ? 'selected' : '' ?>>
                                <?= htmlspecialchars($rep['full_name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-2">
                    <label class="form-label">من تاريخ</label>
                    <input type="date" class="form-control" name="date_from" value="<?= htmlspecialchars($date_from) ?>">
                </div>
                <div class="col-md-2">
                    <label class="form-label">إلى تاريخ</label>
                    <input type="date" class="form-control" name="date_to" value="<?= htmlspecialchars($date_to) ?>">
                </div>
                <div class="col-md-2">
                    <label class="form-label">أيام التأخير</label>
                    <input type="number" class="form-control" name="overdue_days" value="<?= $overdue_days ?>" min="1">
                </div>
                <div class="col-md-3">
                    <button type="submit" class="btn btn-light">
                        <i class="fas fa-search me-1"></i>تحديث التقرير
                    </button>
                </div>
            </form>
        </div>

<div class="row mb-4">
            <div class="col-md-3 mb-3">
                <div class="card bg-primary text-white">
                    <div class="card-body text-center">
                        <h3><?= $total_invoices ?></h3>
                        <p class="mb-0">إجمالي الفواتير</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="card bg-info text-white">
                    <div class="card-body text-center">
                        <h3><?= formatCurrency($total_sales) ?></h3>
                        <p class="mb-0">إجمالي المبيعات</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="card bg-success text-white">
                    <div class="card-body text-center">
                        <h3><?= formatCurrency($total_paid) ?></h3>
                        <p class="mb-0">إجمالي المسدد</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3 mb-3">
                <div class="card bg-warning text-white">
                    <div class="card-body text-center">
                        <h3><?= formatCurrency($total_remaining) ?></h3>
                        <p class="mb-0">إجمالي المتبقي</p>
                    </div>
                </div>
            </div>
        </div>

<div class="product-summary">
            <h5 class="text-primary mb-3"><i class="fas fa-boxes me-2"></i>ملخص مبيعات المنتجات</h5>
            <div class="table-responsive">
                <table class="table table-sm">
                    <thead>
                        <tr>
                            <th>المنتج</th>
                            <th>الكمية المباعة</th>
                            <th>إجمالي القيمة</th>
                            <th>عدد الفواتير</th>
                            <th>متوسط الفاتورة</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($product_sales as $product): ?>
                            <tr>
                                <td><?= htmlspecialchars($product['product_name']) ?></td>
                                <td><?= $product['total_quantity'] ?> <?= htmlspecialchars($product['unit']) ?></td>
                                <td><?= formatCurrency($product['total_value']) ?></td>
                                <td><?= $product['invoice_count'] ?></td>
                                <td><?= formatCurrency($product['total_value'] / $product['invoice_count']) ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>

<div class="card">
            <div class="card-header bg-primary text-white">
                <h5 class="mb-0">
                    <i class="fas fa-users me-2"></i>
                    تفاصيل العملاء - <?= htmlspecialchars($rep_info['full_name']) ?>
                </h5>
            </div>
            <div class="card-body">
                <?php foreach ($clients_data as $client_id => $client): ?>
                    <div class="client-card card mb-3 <?= $client['days_since_payment'] > $overdue_days ? 'client-overdue' : '' ?>">
                        <div class="card-header">
                            <div class="row align-items-center">
                                <div class="col-md-4">
                                    <h6 class="mb-0"><?= htmlspecialchars($client['name']) ?></h6>
                                    <small class="text-muted"><?= htmlspecialchars($client['phone']) ?></small>
                                </div>
                                <div class="col-md-2 text-center">
                                    <small class="text-muted">المبيعات</small>
                                    <div class="fw-bold"><?= formatCurrency($client['total_sales']) ?></div>
                                </div>
                                <div class="col-md-2 text-center">
                                    <small class="text-muted">المسدد</small>
                                    <div class="fw-bold text-success"><?= formatCurrency($client['total_paid']) ?></div>
                                </div>
                                <div class="col-md-2 text-center">
                                    <small class="text-muted">المتبقي</small>
                                    <div class="fw-bold text-warning"><?= formatCurrency($client['total_remaining']) ?></div>
                                </div>
                                <div class="col-md-2 text-center">
                                    <?php if ($client['days_since_payment'] > $overdue_days): ?>
                                        <span class="badge bg-danger">متأخر <?= $client['days_since_payment'] ?> يوم</span>
                                    <?php else: ?>
                                        <span class="badge bg-success">منتظم</span>
                                    <?php endif; ?>
                                </div>
                            </div>
                        </div>
                        <div class="card-body">
                            <div class="table-responsive">
                                <table class="table table-sm">
                                    <thead>
                                        <tr>
                                            <th>رقم الفاتورة</th>
                                            <th>التاريخ</th>
                                            <th>الإجمالي</th>
                                            <th>المدفوع</th>
                                            <th>المتبقي</th>
                                            <th>الحالة</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($client['invoices'] as $invoice): ?>
                                            <tr>
                                                <td><?= htmlspecialchars($invoice['invoice_number']) ?></td>
                                                <td><?= date('Y-m-d', strtotime($invoice['created_at'])) ?></td>
                                                <td><?= formatCurrency($invoice['invoice_total']) ?></td>
                                                <td><?= formatCurrency($invoice['amount_paid_at_creation']) ?></td>
                                                <td><?= formatCurrency($invoice['remaining_amount']) ?></td>
                                                <td>
                                                    <?php
                                                    $status_class = [
                                                        'paid' => 'success',
                                                        'partially_paid' => 'warning',
                                                        'unpaid' => 'danger'
                                                    ];
                                                    $status_text = [
                                                        'paid' => 'مدفوعة',
                                                        'partially_paid' => 'جزئية',
                                                        'unpaid' => 'غير مدفوعة'
                                                    ];
                                                    ?>
                                                    <span class="badge bg-<?= $status_class[$invoice['status']] ?>">
                                                        <?= $status_text[$invoice['status']] ?>
                                                    </span>
                                                </td>
                                            </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                <?php endforeach; ?>
            </div>
        </div>

<?php if (!empty($payments)): ?>
            <div class="card mt-4">
                <div class="card-header bg-success text-white">
                    <h5 class="mb-0"><i class="fas fa-money-bill me-2"></i>المدفوعات المحصلة</h5>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-hover">
                            <thead>
                                <tr>
                                    <th>رقم الإيصال</th>
                                    <th>العميل</th>
                                    <th>المبلغ</th>
                                    <th>التاريخ</th>
                                    <th>الفاتورة المرتبطة</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($payments as $payment): ?>
                                    <tr>
                                        <td><?= htmlspecialchars($payment['receipt_number']) ?></td>
                                        <td><?= htmlspecialchars($payment['client_name']) ?></td>
                                        <td><?= formatCurrency($payment['amount']) ?></td>
                                        <td><?= date('Y-m-d', strtotime($payment['created_at'])) ?></td>
                                        <td><?= $payment['invoice_number'] ? htmlspecialchars($payment['invoice_number']) : 'دفعة منفصلة' ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        <?php endif; ?>
    </div>

Cancel / Back

[ MAINHACK ]

Viewing / Editing File: representative_detailed.php

Server Info

System Features

User Info