View Code - 20260721

attendance_summary_daily.php | 20260721_154032.php Initial Bulk Import

<?php
// Path: /erp/cron/attendance_summary_daily.php
// Purpose: Daily cron to compute fc_attendance_summary for all employees for a given date (default: today).
// Run from CLI via cron (example below).
// IMPORTANT: run under PHP CLI and with environment where config/core/db are accessible.

require_once __DIR__ . '/../core/db.php';
require_once __DIR__ . '/../config.php';

// This script is intended to be executed by CLI (cron). Minimal auth used.

$argv_date = $argv[1] ?? null;
$process_date = $argv_date ? $argv_date : date('Y-m-d'); // process this date
$db = getDB();

try {
  // Fetch list of companies we care about. If company scope needed, adjust.
  // For simplicity, we process for all employees present in fc_attendance for date.
  $sqlEmployees = "SELECT DISTINCT company_id, employee_id FROM fc_attendance WHERE DATE(punch_time) = :d";
  $stmt = $db->prepare($sqlEmployees);
  $stmt->execute([':d'=>$process_date]);
  $pairs = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($pairs as $p) {
    $cid = $p['company_id'];
    $eid = $p['employee_id'];

// Get earliest IN and latest OUT for date
    $q = $db->prepare("SELECT MIN(punch_time) as first_in, MAX(punch_time) as last_out FROM fc_attendance WHERE company_id = :cid AND employee_id = :eid AND DATE(punch_time) = :d");
    $q->execute([':cid'=>$cid, ':eid'=>$eid, ':d'=>$process_date]);
    $r = $q->fetch(PDO::FETCH_ASSOC);

$first_in = $r['first_in'] ?? null;
    $last_out = $r['last_out'] ?? null;

$total_hours = 0;
    $work_status = 'absent';
    $ot_hours = 0;
    $breaks = 0;

if ($first_in && $last_out) {
      $t1 = new DateTime($first_in);
      $t2 = new DateTime($last_out);
      $interval = $t2->getTimestamp() - $t1->getTimestamp();
      $total_hours = round($interval / 3600, 2);
      $work_status = 'present';
      // apply simple policy: if total_hours < 4 -> half_day
      if ($total_hours < 4) $work_status = 'half_day';
    }

// Apply leaves: if any approved leave overlaps date, mark on_leave
    $leaveCheck = $db->prepare("SELECT COUNT(*) as cnt FROM fc_leave WHERE company_id = :cid AND employee_id = :eid AND status='approved' AND :d BETWEEN start_date AND end_date");
    $leaveCheck->execute([':cid'=>$cid, ':eid'=>$eid, ':d'=>$process_date]);
    if ($leaveCheck->fetchColumn() > 0) {
      $work_status = 'on_leave';
      $total_hours = 0;
    }

// Compute OT: use company rule or default shift 9 hours
    $shift_hours = 9.0;
    if ($total_hours > $shift_hours) $ot_hours = round($total_hours - $shift_hours, 2);

// Upsert into fc_attendance_summary
    $up = $db->prepare("INSERT INTO fc_attendance_summary (company_id, employee_id, date, first_in, last_out, total_hours, work_status, ot_hours, breaks_hours, created_at)
      VALUES (:cid, :eid, :date, :fi, :lo, :th, :ws, :ot, :br, NOW())
      ON DUPLICATE KEY UPDATE first_in = VALUES(first_in), last_out = VALUES(last_out), total_hours = VALUES(total_hours), work_status = VALUES(work_status), ot_hours = VALUES(ot_hours), breaks_hours = VALUES(breaks_hours), updated_at = NOW()");
    $up->execute([
      ':cid'=>$cid, ':eid'=>$eid, ':date'=>$process_date, ':fi'=>$first_in, ':lo'=>$last_out,
      ':th'=>$total_hours, ':ws'=>$work_status, ':ot'=>$ot_hours, ':br'=>$breaks
    ]);

// (Optional) Create fc_ot entry for approved OT auto or create pending OT for approval. We'll create a pending record if ot_hours > 0
    if ($ot_hours > 0) {
      $exists = $db->prepare("SELECT COUNT(*) FROM fc_ot WHERE company_id=:cid AND employee_id=:eid AND date=:date AND hours = :hours");
      $exists->execute([':cid'=>$cid, ':eid'=>$eid, ':date'=>$process_date, ':hours'=>$ot_hours]);
      if ($exists->fetchColumn() == 0) {
        $ins = $db->prepare("INSERT INTO fc_ot (company_id, employee_id, date, from_time, to_time, hours, status, applied_at, meta)
           VALUES (:cid, :eid, :date, :from_time, :to_time, :hours, 'pending', NOW(), :meta)");
        $ins->execute([':cid'=>$cid, ':eid'=>$eid, ':date'=>$process_date, ':from_time'=>null, ':to_time'=>null, ':hours'=>$ot_hours, ':meta'=>json_encode(['auto_generated'=>1])]);
      }
    }
  }

echo "Attendance summary processed for date: $process_date\n";
  exit(0);
} catch (Exception $ex) {
  error_log("attendance_summary_daily error: " . $ex->getMessage());
  echo "Error: " . $ex->getMessage() . "\n";
  exit(1);
}