View Code - 20260722

user_work_report_summry.php | 20260722_120325.php Initial Domain Snapshot

<?php
require_once __DIR__ . '/modules/auth/page_acl.php';

$ctx = page_require_access('user_work_report_summry');

$u          = $ctx['user'] ?? null;
$company_id = (int)($ctx['company_id'] ?? 0);
$pdo        = $ctx['pdo'] ?? null;

if (!$pdo) {
    require_once __DIR__ . '/core/db.php';
}

require_once __DIR__ . '/partials/header.php';

function h($str)
{
    return htmlspecialchars((string)$str, ENT_QUOTES, 'UTF-8');
}

// =========================
// ROLE ACCESS
// =========================
$role_name = strtolower((string)($u['role_name'] ?? $u['role'] ?? ''));
$is_super_user = in_array($role_name, ['owner', 'admin', 'developer']);

$report_date = $_GET['report_date'] ?? date('Y-m-d');

if (!$is_super_user) {
    $user_id = (int)($u['id'] ?? 0);
} else {
    $user_id = isset($_GET['user_id']) ? (int)$_GET['user_id'] : 0;
}

/* =========================
   USERS
========================= */
$user_sql = "SELECT id, name FROM users WHERE company_id = :cid ORDER BY name ASC";
$user_stmt = $pdo->prepare($user_sql);
$user_stmt->execute([':cid' => $company_id]);
$users = $user_stmt->fetchAll(PDO::FETCH_ASSOC);

/* =========================
   REPORT DATA (PERFECTED RELATION & JSON COUNT)
========================= */
// 1. यहाँ 'reference_id' के जरिए 'kasab_entry' की 'id' को सीधे जोड़ा गया है।
// 2. Kasab एंट्री होने पर JSON एरे के अंदर मौजूद weights की संख्या (JSON_LENGTH) को जोड़ा जा रहा है, बाकी कामों के लिए 1 (यानी नॉर्मल काउंट) गिना जा रहा है।
$sql = "
SELECT
    l.user_id,
    u.name AS user_name,
    d.work_label,
    SUM(CASE 
        WHEN (LOWER(l.page_slug) LIKE '%kasab%' OR LOWER(d.work_label) LIKE '%kasab%') THEN
            CASE 
                WHEN JSON_VALID(ke.weight_json) THEN JSON_LENGTH(JSON_EXTRACT(ke.weight_json, '$.weights'))
                ELSE 0 
            END
        ELSE 1 
    END) AS total_work
FROM user_activity_logs l
INNER JOIN user_work_definitions d
    ON d.company_id = l.company_id
    AND d.page_slug = l.page_slug
LEFT JOIN users u
    ON u.id = l.user_id
LEFT JOIN kasab_entry ke
    ON ke.id = l.reference_id 
    AND ke.company_id = l.company_id
WHERE l.company_id = :cid
AND DATE(l.created_at) = :report_date
AND d.is_active = 1
GROUP BY l.user_id, d.work_label, u.name, d.sort_order
ORDER BY u.name ASC, d.sort_order ASC, total_work DESC
";

$params = [
    ':cid'         => $company_id,
    ':report_date' => $report_date
];

if ($user_id > 0) {
    // SQL इन्जेक्शन और एम्ब्रीगुइटी से बचने के लिए इसे अलग से अपेंड किया गया है
    $sql_parts = explode("WHERE", $sql);
    $sql = $sql_parts[0] . " WHERE l.user_id = :uid AND " . $sql_parts[1];
    $params[':uid'] = $user_id;
}

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* =========================
   FORMAT DATA
========================= */
$report = [];
foreach ($rows as $r) {
    $uid = (int)$r['user_id'];
    if (!isset($report[$uid])) {
        $report[$uid] = [
            'user_name' => $r['user_name'],
            'items'     => [],
            'total'     => 0
        ];
    }
    $report[$uid]['items'][] = [
        'work_label' => $r['work_label'],
        'total_work' => (int)$r['total_work']
    ];
    $report[$uid]['total'] += (int)$r['total_work'];
}
?>

<div class="container-fluid py-3">
    <div class="card shadow-sm border-0">
        <div class="card-header d-flex justify-content-between align-items-center flex-wrap gap-2">
            <div class="d-flex flex-column flex-md-row align-items-md-center gap-2 w-100">
                <div class="flex-grow-1">
                    <h4 class="mb-0">User Work Report Summary</h4>
                    <div class="text-muted small" id="companyNameForExport"><b>Company:</b> <?= h($company_name) ?></div>
                </div>
                <div class="d-flex align-items-center gap-2">
                    <button type="button" class="btn btn-outline-secondary btn-sm ms-2" onclick="window.print()">
                        <i class="fas fa-print"></i> Print
                    </button>
                    <button type="button" class="btn btn-outline-success btn-sm ms-2" onclick="exportPDF()">
                        <i class="fas fa-file-pdf"></i> Export PDF
                    </button>
                </div>
            </div>

<?php if ($is_super_user): ?>
                <div class="col-auto">
                    <label class="form-label mb-1">User</label>
                    <select name="user_id" class="form-select">
                        <option value="0">All Users</option>
                        <?php foreach ($users as $usr): ?>
                            <option value="<?= (int)$usr['id'] ?>" <?= $user_id == $usr['id'] ? 'selected' : '' ?>>
                                <?= h($usr['name']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <?php endif; ?>

<div class="col-auto">
                    <button type="submit" class="btn btn-primary">Filter</button>
                </div>
            </form>
        </div>

<div class="card-body">
            <?php if (empty($report)): ?>
                <div class="alert alert-warning mb-0">No report data found.</div>
            <?php else: ?>
                <div class="row g-3">
                    <?php foreach ($report as $uid => $user_report): ?>
                        <div class="col-lg-6">
                            <div class="card h-100 border">
                                <div class="card-header bg-light">
                                    <div class="d-flex justify-content-between align-items-center">
                                        <div>
                                            <h5 class="mb-0"><?= h($user_report['user_name']) ?></h5>
                                            <small class="text-muted"><?= date('d/m/Y', strtotime($report_date)) ?> Work Report</small>
                                        </div>
                                        <div class="text-end">
                                            <div class="badge bg-primary fs-6">
                                                Total Count: <?= (int)$user_report['total'] ?>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                                <div class="card-body p-0">
                                    <div class="table-responsive">
                                        <table class="table table-bordered table-striped align-middle mb-0">
                                            <thead class="table-light">
                                                <tr>
                                                    <th width="70%">Work</th>
                                                    <th width="30%" class="text-center">Total Count</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($user_report['items'] as $item): ?>
                                                    <tr>
                                                        <td><?= h($item['work_label']) ?></td>
                                                        <td class="text-center fw-bold"><?= (int)$item['total_work'] ?></td>
                                                    </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    <?php endforeach; ?>
                </div>
            <?php endif; ?>
        </div>
    </div>
</div>

if (reportData.length === 0) {
        alert('No data to export!');
        return;
    }

let yOffset = 15;

doc.setFont("helvetica", "bold");
    doc.setFontSize(14);
    doc.text(`Company: ${companyName}`, 14, yOffset);
    yOffset += 8;

doc.setFontSize(16);
    doc.text("User Work Report Summary", 14, yOffset);
    yOffset += 7;

doc.setFont("helvetica", "normal");
    doc.setFontSize(10);
    doc.text(`Date: ${reportDate}`, 14, yOffset);
    yOffset += 10;

reportData.forEach((user) => {
        if (yOffset > 250) {
            doc.addPage();
            yOffset = 15;
            doc.setFont("helvetica", "bold");
            doc.setFontSize(14);
            doc.text(`Company: ${companyName}`, 14, yOffset);
            yOffset += 8;
            doc.setFontSize(16);
            doc.text("User Work Report Summary", 14, yOffset);
            yOffset += 7;
            doc.setFont("helvetica", "normal");
            doc.setFontSize(10);
            doc.text(`Date: ${reportDate}`, 14, yOffset);
            yOffset += 10;
        }

doc.setFillColor(245, 245, 245);
        doc.rect(14, yOffset, 182, 10, 'F');
        doc.setFont("helvetica", "bold");
        doc.setFontSize(12);
        doc.setTextColor(0, 0, 0);
        doc.text(user.user_name, 16, yOffset + 7);
        const totalText = `Total: ${user.total}`;
        doc.text(totalText, 196 - doc.getTextWidth(totalText), yOffset + 7);
        yOffset += 14;

doc.setFontSize(10);
        doc.setFillColor(230, 235, 240);
        doc.rect(14, yOffset, 182, 7, 'F');
        doc.text("Work Details", 16, yOffset + 5);
        doc.text("Total Count", 160, yOffset + 5);
        yOffset += 7;

doc.setFont("helvetica", "normal");
        user.items.forEach((item) => {
            if (yOffset > 270) {
                doc.addPage();
                yOffset = 15;
                doc.setFont("helvetica", "bold");
                doc.setFontSize(14);
                doc.text(`Company: ${companyName}`, 14, yOffset);
                yOffset += 8;
                doc.setFontSize(16);
                doc.text("User Work Report Summary", 14, yOffset);
                yOffset += 7;
                doc.setFont("helvetica", "normal");
                doc.setFontSize(10);
                doc.text(`Date: ${reportDate}`, 14, yOffset);
                yOffset += 10;
            }
            doc.setDrawColor(220, 220, 220);
            doc.line(14, yOffset + 5, 196, yOffset + 5);
            doc.text(item.work_label, 16, yOffset + 3);
            doc.text(String(item.total_work), 160, yOffset + 3);
            yOffset += 7;
        });
        yOffset += 10;
    });

doc.save(`Work_Report_${reportDate.replace(/\//g, '-')}.pdf`);
}
</script>

<?php require_once __DIR__ . '/partials/footer.php'; ?>