View Code - 20260721

sync_daily_to_summary.php | 20260721_154033.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/sync_daily_to_summary.php
   Purpose: Calculate Summaries for Monthly (with Machine Grid Integration) & Semi-monthly
            and Push cleanly into manual_attendance_data table.
   ========================================================================== */

header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL);
ini_set('display_errors', '1');

$require_activity_helper = __DIR__ . '/helpers/activity_helper.php';
if (file_exists($require_activity_helper)) {
    require_once $require_activity_helper;
}

require __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('manual_attendance'); 
$USER       = $ctx['user'];
$COMPANY_ID = (int)$ctx['company_id'];
$USER_ID    = (int)$USER['id'];

$pdo = isset($ctx['pdo']) && $ctx['pdo'] instanceof PDO ? $ctx['pdo'] : null;
if (!$pdo) { 
    require __DIR__ . '/core/db.php'; 
}

$msg = ''; $error = '';

/* ===== Bound Parser Shared Helper ===== */
function _parse_period_bounds_local($monthYYYYMM, $periodLabel){
    $month = trim((string)$monthYYYYMM);
    $pl    = str_replace('–','-', trim((string)$periodLabel));
    if(!$month || !$pl) return [null,null];
    [$y,$m] = array_map('intval', explode('-', $month));
    [$a,$b] = array_map('intval', explode('-', $pl));
    $ps = sprintf('%04d-%02d-%02d', $y, $m, $a);
    $pe = sprintf('%04d-%02d-%02d', $y, $m, $b);
    return [$ps, $pe];
}

/* =============================================================================
   2. CALCULATION & PROCESSING PIPELINE ENGINE
   ========================================================================== */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['trigger_summary_calc'])) {
    try {
        $month = trim($_POST['sync_month'] ?? '');
        $period_label = trim($_POST['sync_period_label'] ?? '');
        $payroll_type = trim($_POST['payroll_type'] ?? 'Monthly');

if (empty($month) || empty($period_label)) {
            throw new Exception("Please specify both Working Month and Period limits.");
        }

// 1. Calculate boundaries date ranges parameters
        [$start_iso, $end_iso] = _parse_period_bounds_local($month, $period_label);
        if (!$start_iso || !$end_iso) {
            throw new Exception("Calculated period timeline boundary is invalid.");
        }

// 2. Fetch active employees within this isolation profile range
        $emp_stmt = $pdo->prepare("
            SELECT DISTINCT e.id AS employee_id, e.name AS employee_name, e.department_name, e.designation_name, e.per_day
            FROM company_employee_master e
            WHERE e.company_id = ? AND e.is_active = 1
        ");
        $emp_stmt->execute([$COMPANY_ID]);
        $active_employees = $emp_stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($active_employees)) {
            throw new Exception("No active employee configuration matrix found for company isolation layer.");
        }

$pdo->beginTransaction();
        $processed_counter = 0;

foreach ($active_employees as $emp) {
            $emp_id = (int)$emp['employee_id'];
            $dept_name = trim($emp['department_name']);
            $per_day_rate = (float)$emp['per_day'];

// Fetch dynamic attendance rule mapping for department criteria
            $rule_stmt = $pdo->prepare("SELECT halfday_calculation_type FROM attendance_rules_master WHERE company_id = ? AND department_name = ? LIMIT 1");
            $rule_stmt->execute([$COMPANY_ID, $dept_name]);
            $calc_rule = $rule_stmt->fetchColumn() ?: 'half_day_salary';

$total_attendance_weight = 0.00;
            $has_records = false;

/* -------------------------------------------------------------------------
               CRITICAL ROUTER: Choose Data Source & Formula Engine (Machine vs Standard Grids)
               ------------------------------------------------------------------------- */
            if ($payroll_type === 'Monthly') {
                
                // First verify if records exist inside dedicated Machine-Wise Matrix Grid
                $check_mach = $pdo->prepare("
                    SELECT SUM(calculated_weight) as total_weight, COUNT(*) as log_count 
                    FROM employee_attendance_machinewise_grid 
                    WHERE company_id = ? AND employee_id = ? AND attendance_date BETWEEN ? AND ?
                ");
                $check_mach->execute([$COMPANY_ID, $emp_id, $start_iso, $end_iso]);
                $mach_res = $check_mach->fetch(PDO::FETCH_ASSOC);

if (!empty($mach_res['log_count']) && (int)$mach_res['log_count'] > 0) {
                    // Machine-Wise Logic: calculated_weight column directly defines the actual attendance percent!
                    $total_attendance_weight = (float)($mach_res['total_weight'] ?? 0.00);
                    $has_records = true;
                } else {
                    // Fallback to standard standard monthly grid table structure
                    $source_table = 'employee_attendance_monthly_grid';
                }
                
            } else {
                // Semi-monthly route strictly reads from raw atomic daily table logs
                $source_table = 'employee_attendance_daily';
            }

// Fallback Engine execution if data is pulled from standard status string tables
            if (!$has_records) {
                // Confirm if records exist in selected fallback table context
                $check_fallback = $pdo->prepare("SELECT COUNT(*) FROM $source_table WHERE company_id = ? AND employee_id = ? AND attendance_date BETWEEN ? AND ?");
                $check_fallback->execute([$COMPANY_ID, $emp_id, $start_iso, $end_iso]);
                
                if ((int)$check_fallback->fetchColumn() > 0) {
                    $status_stmt = $pdo->prepare("
                        SELECT status, COUNT(*) as cnt 
                        FROM $source_table 
                        WHERE company_id = ? AND employee_id = ? AND attendance_date BETWEEN ? AND ?
                        GROUP BY status
                    ");
                    $status_stmt->execute([$COMPANY_ID, $emp_id, $start_iso, $end_iso]);
                    $counts = $status_stmt->fetchAll(PDO::FETCH_KEY_PAIR) ?: [];

$p_count   = (int)($counts['Present'] ?? 0);
                    $pp_count  = (int)($counts['DoublePresent'] ?? 0); 
                    $h_count   = (int)($counts['Halfday'] ?? 0);
                    $hol_count = (int)($counts['Holiday'] ?? 0);

$total_attendance_weight = (float)$p_count;
                    $total_attendance_weight += ((float)$pp_count * 2.0); // Strict Multiplier Fix

if ($calc_rule === 'full_day_salary') {
                        $total_attendance_weight += (float)$h_count;
                    } else {
                        $total_attendance_weight += ((float)$h_count * 0.5);
                    }
                    $total_attendance_weight += (float)$hol_count;
                    $has_records = true;
                }
            }

// If no data rows found for this individual employee across arrays, skip calculations cleanly
            if (!$has_records) {
                continue;
            }

$computed_salary = round($per_day_rate * $total_attendance_weight, 2);
            $period_text = date('M Y', strtotime($start_iso)) . " | $payroll_type | $period_label";

// 3. Delete-then-Insert transaction synchronization pattern into manual_attendance_data
            $del_summary = $pdo->prepare("DELETE FROM manual_attendance_data WHERE company_id = ? AND employee_id = ? AND period_start = ? AND period_end = ?");
            $del_summary->execute([$COMPANY_ID, $emp_id, $start_iso, $end_iso]);

$ins_summary = $pdo->prepare("
                INSERT INTO manual_attendance_data 
                    (company_id, employee_id, employee_name, department, role, salary_period, period_start, period_end, per_day, total_attendance, salary, created_by, created_at, updated_at)
                VALUES 
                    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
            ");
            $ins_summary->execute([
                $COMPANY_ID, $emp_id, $emp['employee_name'], $dept_name, $emp['designation_name'], 
                $period_text, $start_iso, $end_iso, $per_day_rate, $total_attendance_weight, $computed_salary, $USER_ID
            ]);

$processed_counter++;
        }

if ($processed_counter === 0) {
            throw new Exception("No structural atomic data matching criteria across target storage schemas.");
        }

$pdo->commit();

if (function_exists('activity_create')) {
            activity_create('attendance', 'daily_to_summary_matrix_calc', $COMPANY_ID, "Compiled aggregate $payroll_type summaries with integrated machine matrices for $processed_counter profiles.");
        }

$msg = "Calculation complete! Computed and updated <strong>$payroll_type</strong> summaries for <strong>$processed_counter</strong> active staff profiles inside <code>manual_attendance_data</code> cleanly.";

} catch (Throwable $e) {
        if (isset($pdo) && $pdo->inTransaction()) $pdo->rollBack();
        $error = $e->getMessage();
    }
}

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

<div class="container py-5">
    <div class="row justify-content-center">
        <div class="col-lg-8">
            <div class="card shadow-sm border-0 mb-4 bg-white">
                <div class="card-body p-4 border-start border-primary border-4 rounded-start">
                    <h4 class="fw-bold text-dark mb-1"><i class="bi bi-calculator-fill text-primary"></i> Master Attendance to Summary Sync Engine</h4>
                    <p class="text-muted small mb-0">Processes atomic logs dynamically based on payroll type (Monthly / Semi-Monthly) including unified Machine Matrix Weights and syncs computed totals into <code>manual_attendance_data</code>.</p>
                </div>
            </div>

<?php if(!empty($msg)): ?>
                <div class="alert alert-success shadow-sm p-3 alert-dismissible fade show" role="alert">
                    <i class="bi bi-check-circle-fill me-2"></i> <?= $msg ?>
                    <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                </div>
            <?php endif; ?>

<?php if(!empty($error)): ?>
                <div class="alert alert-danger shadow-sm p-3 alert-dismissible fade show" role="alert">
                    <i class="bi bi-exclamation-triangle-fill me-2"></i> <strong>Calculation Halted:</strong> <?= htmlspecialchars($error) ?>
                    <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                </div>
            <?php endif; ?>

<div class="card shadow-sm border-0">
                <div class="card-header bg-dark text-white fw-bold py-3">
                    <i class="bi bi-sliders2 me-1"></i> Selection Scope Calculation Target Setup
                </div>
                <div class="card-body bg-light p-4">
                    <form method="POST" autocomplete="off" onsubmit="return confirm('Process sum weight summaries for chosen period limits? This will regenerate and overwrite previous records inside manual_attendance_data table.');">
                        <div class="row g-3 mb-4">
                            <div class="col-md-4">
                                <label class="form-label small fw-bold text-uppercase text-secondary">Working Month Context</label>
                                <input type="month" name="sync_month" id="sync_month" class="form-control form-control-lg fw-bold" value="<?= date('Y-m') ?>" onchange="updateInteractivePeriodsMapping()" required>
                            </div>
                            <div class="col-md-4">
                                <label class="form-label small fw-bold text-uppercase text-secondary">Payroll Type</label>
                                <select name="payroll_type" id="payroll_type" class="form-select form-select-lg fw-bold" onchange="updateInteractivePeriodsMapping()">
                                    <option value="Monthly">Monthly</option>
                                    <option value="Semi-monthly">Semi-monthly</option>
                                </select>
                            </div>
                            <div class="col-md-4">
                                <label class="form-label small fw-bold text-uppercase text-secondary">Period Range Label</label>
                                <select name="sync_period_label" id="sync_period" class="form-select form-select-lg fw-bold border-primary text-primary" required>
                                    </select>
                            </div>
                        </div>

<div class="border-top pt-4 text-end">
                            <button type="submit" name="trigger_summary_calc" class="btn btn-primary btn-lg px-5 fw-bold shadow">
                                <i class="bi bi-cpu-fill me-1"></i> COMPILE & SEND SUMMARIES
                            </button>
                        </div>
                    </form>
                </div>
            </div>
            
            <div class="alert alert-light border p-3 mt-4 rounded shadow-sm small text-muted font-monospace">
                * System Rules: Semi-monthly queries load from `employee_attendance_daily`. Monthly queries intelligently aggregate matching records inside `employee_attendance_machinewise_grid` (using <b>calculated_weight</b>) or fallback automatically to standard status logs.
            </div>
        </div>
    </div>
</div>

document.addEventListener('DOMContentLoaded', () => {
    updateInteractivePeriodsMapping();
});
</script>

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