View Code - 20260722

new_manual_attendance_entry.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/manual_attendance_entry_alt.php
   Purpose: Manual Attendance — Entry & Edit (Fixed)
   ========================================================================== */

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'; }

/* ===== Helpers ===== */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function qv($k,$d=null){ return isset($_REQUEST[$k])? (is_array($_REQUEST[$k])?$_REQUEST[$k]:trim((string)$_REQUEST[$k])) : $d; }
function pv($k,$d=null){ return isset($_POST[$k])? (is_array($_POST[$k])?$_POST[$k]:trim((string)$_POST[$k])) : $d; }
function jexit($arr){
  header('Content-Type: application/json; charset=utf-8');
  echo json_encode($arr); exit;
}

function col_exists(PDO $pdo, string $table, string $col): bool {
    $st = $pdo->prepare("SHOW COLUMNS FROM `$table` LIKE ?");
    $st->execute([$col]);
    return (bool)$st->fetch(PDO::FETCH_ASSOC);
}

function department_name_column(PDO $pdo): string {
    static $nameCol = null;
    if ($nameCol === null) {
        $nameCol = pick_name_column($pdo, 'company_departments', ['department_name', 'name', 'title', 'label']);
    }
    return $nameCol;
}

function find_department_name(PDO $pdo, int $companyId, int $departmentId): ?string {
    if ($departmentId <= 0) return null;
    $nameCol = department_name_column($pdo);
    $sql = "SELECT `$nameCol` FROM `company_departments` WHERE id = ?";
    $params = [$departmentId];

if (col_exists($pdo, 'company_departments', 'company_id')) {
        $sql .= " AND company_id = ?";
        $params[] = $companyId;
    }

$sql .= " LIMIT 1";
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $name = $st->fetchColumn();

if ($name !== false) {
        return trim((string)$name);
    }

$st = $pdo->prepare(" 
        SELECT department_name
        FROM company_employee_master
        WHERE company_id = ?
          AND department_id = ?
          AND department_name <> ''
        ORDER BY is_active DESC, id DESC
        LIMIT 1
    ");
    $st->execute([$companyId, $departmentId]);
    $name = $st->fetchColumn();
    return $name !== false ? trim((string)$name) : null;
}

function find_department_id(PDO $pdo, int $companyId, string $departmentName): ?int {
    $departmentName = trim($departmentName);
    if ($departmentName === '') return null;

$nameCol = department_name_column($pdo);
    $sql = "SELECT id FROM `company_departments` WHERE `$nameCol` = ?";
    $params = [$departmentName];

if (col_exists($pdo, 'company_departments', 'company_id')) {
        $sql .= " AND company_id = ?";
        $params[] = $companyId;
    }

$sql .= " ORDER BY id LIMIT 1";
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $id = $st->fetchColumn();

if ($id !== false) {
        return (int)$id;
    }

$st = $pdo->prepare(" 
        SELECT department_id
        FROM company_employee_master
        WHERE company_id = ?
          AND department_name = ?
          AND department_id IS NOT NULL
          AND department_id > 0
        ORDER BY is_active DESC, id DESC
        LIMIT 1
    ");
    $st->execute([$companyId, $departmentName]);
    $id = $st->fetchColumn();
    return $id !== false ? (int)$id : null;
}

function _parse_period_bounds($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];
}

/* AJAX Endpoints */
if (qv('ajax') === '1') {
    $act = qv('act','');

try {

/* ================= Departments ================= */
if ($act === 'departments') {

$nameCol = department_name_column($pdo);
        $sql = "SELECT id AS department_id, `$nameCol` AS department_name FROM `company_departments` WHERE 1=1";
        $params = [];

if (col_exists($pdo, 'company_departments', 'company_id')) {
            $sql .= " AND company_id = ?";
            $params[] = $COMPANY_ID;
        }
        if (col_exists($pdo, 'company_departments', 'is_active')) {
            $sql .= " AND is_active = 1";
        }

$sql .= " ORDER BY `$nameCol`";
        $stm = $pdo->prepare($sql);
        $stm->execute($params);
        $items = $stm->fetchAll(PDO::FETCH_ASSOC);

if (!$items) {
            $stm = $pdo->prepare(" 
                SELECT
                    COALESCE(NULLIF(department_id, 0), MIN(id)) AS department_id,
                    department_name
                FROM company_employee_master
                WHERE company_id = ?
                  AND is_active = 1
                  AND department_name <> ''
                GROUP BY department_name
                ORDER BY department_name
            ");
            $stm->execute([$COMPANY_ID]);
            $items = $stm->fetchAll(PDO::FETCH_ASSOC);
        }

jexit([
        'ok'    => true,
        'items' => $items
    ]);
}

/* ================= Employees ================= */
        if ($act === 'employees') {

$dept_id = (int)qv('department_id',0);
            $dept_name = find_department_name($pdo, $COMPANY_ID, $dept_id);

if ($dept_name === null) {
                jexit(['ok'=>true,'items'=>[]]);
            }

$stm = $pdo->prepare("
                SELECT id AS employee_id,
                       name AS employee_name,
                       per_day,
                       designation_name
                FROM company_employee_master
                WHERE company_id = ?
                  AND department_name = ?
                  AND is_active = 1
                ORDER BY name
            ");

$stm->execute([$COMPANY_ID, $dept_name]);

jexit(['ok'=>true,'items'=>$stm->fetchAll(PDO::FETCH_ASSOC)]);
        }

/* ================= Grid ================= */
        if ($act === 'fetch_grid') {

$m    = qv('month');
            $dept = (int)qv('dept');

$params = [$COMPANY_ID];
            $where  = "mad.company_id = ?";

if ($m) {
                $where .= " AND mad.period_start LIKE ?";
                $params[] = $m . '%';
            }

if ($dept > 0) {
                $deptName = find_department_name($pdo, $COMPANY_ID, $dept);
                if ($deptName === null) {
                    $where .= " AND 1 = 0";
                } else {
                    $where .= " AND mad.department = ?";
                    $params[] = $deptName;
                }
            }

$sql = "
                SELECT mad.*, cem.department_id
                FROM manual_attendance_data mad
                LEFT JOIN company_employee_master cem
                       ON cem.id = mad.employee_id
                WHERE $where
                ORDER BY mad.updated_at DESC
            ";

$stm = $pdo->prepare($sql);
            $stm->execute($params);

jexit(['ok'=>true,'rows'=>$stm->fetchAll(PDO::FETCH_ASSOC)]);
        }

/* ================= Load One ================= */
        if ($act === 'load_one') {

$id = (int)qv('id',0);

$stm = $pdo->prepare("
                SELECT mad.*, cem.department_id
                FROM manual_attendance_data mad
                  LEFT JOIN company_employee_master cem
                      ON cem.id = mad.employee_id
                WHERE mad.company_id = ?
                  AND mad.id = ?
            ");

$stm->execute([$COMPANY_ID, $id]);
            $row = $stm->fetch(PDO::FETCH_ASSOC);

if ($row) {
                $row['department_id'] = find_department_id($pdo, $COMPANY_ID, (string)($row['department'] ?? ''));
            }

jexit(['ok'=>true,'row'=>$row]);
        }

/* ================= Save (Insert + Update Properly) ================= */
        if ($act === 'save') {

$id        = (int)pv('id',0);
            $emp_id    = (int)pv('employee_id',0);
            $month     = pv('month');
            $plabel    = pv('period_label');
            $ptype     = pv('payroll_type');
            $per_day   = (float)pv('per_day',0);
            $total_att = (float)pv('total_attendance',0);
            $notes     = pv('notes','');

[$ps,$pe] = _parse_period_bounds($month, $plabel);

/* Fetch employee safely (company scoped) */
            $stm = $pdo->prepare("
                SELECT name, department_name, designation_name
                FROM company_employee_master
                                WHERE id = ? AND company_id = ?
            ");
            $stm->execute([$emp_id, $COMPANY_ID]);
            $E = $stm->fetch(PDO::FETCH_ASSOC);

if (!$E) {
                jexit(['ok'=>false,'msg'=>'Invalid Employee']);
            }

$salary      = round($per_day * $total_att, 2);
            $period_text = date('M Y', strtotime($ps)) . " | $ptype | $plabel";

/* ===== UPDATE MODE ===== */
            if ($id > 0) {

$sql = "
                    UPDATE manual_attendance_data SET
                        employee_id      = ?,
                        employee_name    = ?,
                        department       = ?,
                        role             = ?,
                        salary_period    = ?,
                        period_start     = ?,
                        period_end       = ?,
                        per_day          = ?,
                        total_attendance = ?,
                        salary           = ?,
                        notes            = ?,
                        updated_at       = NOW()
                    WHERE id = ?
                      AND company_id = ?
                ";

$pdo->prepare($sql)->execute([
                    $emp_id,
                    $E['name'],
                    $E['department_name'],
                    $E['designation_name'],
                    $period_text,
                    $ps,
                    $pe,
                    $per_day,
                    $total_att,
                    $salary,
                    $notes,
                    $id,
                    $COMPANY_ID
                ]);

// Log activity for update
                if (function_exists('activity_create')) {
                    activity_create('attendance', 'manual_attendance_update', $id, 'Manual attendance entry updated');
                }
            } else {

/* ===== INSERT MODE ===== */

$sql = "
                    INSERT INTO manual_attendance_data
                        (company_id,
                         employee_id,
                         employee_name,
                         department,
                         role,
                         salary_period,
                         period_start,
                         period_end,
                         per_day,
                         total_attendance,
                         salary,
                         notes,
                         created_by,
                         created_at,
                         updated_at)
                    VALUES
                        (?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())
                ";

$pdo->prepare($sql)->execute([
                    $COMPANY_ID,
                    $emp_id,
                    $E['name'],
                    $E['department_name'],
                    $E['designation_name'],
                    $period_text,
                    $ps,
                    $pe,
                    $per_day,
                    $total_att,
                    $salary,
                    $notes,
                    $USER_ID
                ]);
                // Log activity for insert
                if (function_exists('activity_create')) {
                    activity_create('attendance', 'manual_attendance_insert', $pdo->lastInsertId(), 'Manual attendance entry created');
                }
            }

jexit(['ok'=>true,'msg'=>'Record Saved Successfully']);
        }

} catch (Throwable $e) {

jexit(['ok'=>false,'msg'=>$e->getMessage()]);
    }
}
require_once __DIR__ . '/partials/header.php';
?>

<div class="container-fluid py-4">
    <h4 class="fw-bold mb-4">Manual Attendance Entry</h4>

<div class="card shadow-sm border-0 mb-4">
        <div class="card-body">
            <form id="altAttForm">
                <input type="hidden" name="id" id="alt_f_id" value="">
                <div class="row g-3">
                    <div class="col-md-3">
                        <label class="form-label small fw-bold">Month</label>
                        <input class="form-control" type="month" name="month" id="alt_f_month" value="<?= $defMonth ?>">
                    </div>
                    <div class="col-md-3">
                        <label class="form-label small fw-bold">Payroll Type</label>
                        <select class="form-select" name="payroll_type" id="alt_f_pt">
                            <option value="Monthly">Monthly</option>
                            <option value="Semi-monthly">Semi-monthly</option>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label small fw-bold">Period</label>
                        <select class="form-select" name="period_label" id="alt_f_period"></select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label small fw-bold">Department</label>
                        <select class="form-select" id="alt_f_dept"><option value="">-- Select --</option></select>
                    </div>
                    <div class="col-md-4">
                        <label class="form-label small fw-bold">Employee</label>
                        <select class="form-select" name="employee_id" id="alt_f_emp"><option value="">-- Select --</option></select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label small fw-bold">Per Day (₹)</label>
                        <input class="form-control" type="number" step="0.01" name="per_day" id="alt_f_per_day">
                    </div>
                    <div class="col-md-2">
                        <label class="form-label small fw-bold">Attendance</label>
                        <input class="form-control" type="number" step="0.25" name="total_attendance" id="alt_f_total" oninput="altRecalc()">
                    </div>
                    <div class="col-md-4">
                        <label class="form-label small fw-bold">Computed Salary</label>
                        <input class="form-control bg-light fw-bold text-primary" id="alt_f_salary_preview" readonly>
                    </div>
                    <div class="col-12 text-end border-top pt-3">
                        <button class="btn btn-outline-secondary me-2" type="button" onclick="altResetForm()">Clear</button>
                        <button class="btn btn-primary px-5" type="button" onclick="altSaveForm()">Save Attendance</button>
                    </div>
                </div>
            </form>
        </div>
    </div>

<div class="card shadow-sm border-0">
        <div class="card-header bg-white py-3">
            <div class="row g-2 align-items-center">
                <div class="col-md-3"><h6 class="mb-0 fw-bold">Saved Records</h6></div>
                <div class="col-md-2"><input type="month" id="grid_m" class="form-control form-control-sm" value="<?= $defMonth ?>" onchange="altFetchGrid()"></div>
                <div class="col-md-3"><select id="grid_d" class="form-select form-select-sm" onchange="altFetchGrid()"><option value="">All Departments</option></select></div>
            </div>
        </div>
        <div class="table-responsive">
            <table id="alt_grid" class="table table-hover align-middle mb-0" style="font-size:0.85rem;">
                <thead class="table-light">
                    <tr><th>Employee</th><th>Period</th><th class="text-end">Rate</th><th class="text-end">Days</th><th class="text-end">Salary</th><th class="text-center">Action</th></tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>
</div>

async function altLoadEmployees(deptId, targetSelId = 'alt_f_emp') {
    const sel = el(targetSelId);
    sel.innerHTML = '<option value="">Loading...</option>';
    const r = await fetch(`?ajax=1&act=employees&department_id=${deptId}`).then(r => r.json());
    sel.innerHTML = '<option value="">-- Select Employee --</option>';
    if (r.ok) {
        r.items.forEach(it => {
            const o = new Option(it.employee_name, it.employee_id);
            o.dataset.per_day = it.per_day || 0;
            sel.add(o);
        });
    }
}

async function altFetchGrid() {
    const m = el('grid_m').value, d = el('grid_d').value;
    const r = await fetch(`?ajax=1&act=fetch_grid&month=${m}&dept=${d}`).then(r=>r.json());
    const tbody = el('alt_grid').querySelector('tbody');
    tbody.innerHTML = '';
    if(r.ok) {
        r.rows.forEach(x => {
            tbody.innerHTML += `<tr>
                <td><strong>${x.employee_name}</strong><br><small class="text-muted">${x.department}</small></td>
                <td><small>${x.salary_period}</small></td>
                <td class="text-end">${x.per_day}</td>
                <td class="text-end">${x.total_attendance}</td>
                <td class="text-end fw-bold text-primary">₹${x.salary}</td>
                <td class="text-center"><button class="btn btn-sm btn-link" onclick="altEditRow(${x.id})">Edit</button></td>
            </tr>`;
        });
    }
}

async function altEditRow(id) {
    const r = await fetch(`?ajax=1&act=load_one&id=${id}`).then(r=>r.json());
    if(r.ok) {
        const x = r.row;
        el('alt_f_id').value = x.id;
        el('alt_f_month').value = x.period_start.slice(0,7);
        
        // Setup payroll type and period
        const periodParts = x.salary_period.split('|');
        const pType = periodParts[1] ? periodParts[1].trim() : 'Monthly';
        const pLabel = periodParts[2] ? periodParts[2].trim().replace('–','-') : '';
        
        el('alt_f_pt').value = pType;
        syncPeriods('alt_f_month', 'alt_f_pt', 'alt_f_period');
        el('alt_f_period').value = pLabel;
        
        // Load Dept and Employees
        el('alt_f_dept').value = x.department_id || '';
        await altLoadEmployees(x.department_id);
        el('alt_f_emp').value = x.employee_id;
        
        el('alt_f_per_day').value = x.per_day;
        el('alt_f_total').value = x.total_attendance;
        altRecalc();
        
        window.scrollTo({top: 0, behavior: 'smooth'});
    }
}

async function altSaveForm() {
    const fd = new FormData(el('altAttForm'));
    const r = await fetch('?ajax=1&act=save', {method:'POST', body:fd}).then(r=>r.json());
    if(r.ok) { 
        alert(r.msg); 
        altResetForm(); 
        altFetchGrid(); 
    } else { alert("Error: " + r.msg); }
}

function altRecalc() {
    const pd = parseFloat(el('alt_f_per_day').value || 0);
    const ta = parseFloat(el('alt_f_total').value || 0);
    el('alt_f_salary_preview').value = (pd * ta).toFixed(2);
}

function altResetForm(){

const month   = el('alt_f_month').value;
    const payroll = el('alt_f_pt').value;
    const period  = el('alt_f_period').value;
    const dept    = el('alt_f_dept').value;

// Clear only editable fields
    el('alt_f_id').value='';
    el('alt_f_emp').value='';
    el('alt_f_per_day').value='';
    el('alt_f_total').value='';
    el('alt_f_salary_preview').value='';

// Restore preserved fields
    el('alt_f_month').value=month;
    el('alt_f_pt').value=payroll;

syncPeriods('alt_f_month','alt_f_pt','alt_f_period');
    el('alt_f_period').value=period;
    el('alt_f_dept').value=dept;

if(dept){
        altLoadEmployees(dept);
    }
}
document.addEventListener('DOMContentLoaded', async () => {
    // Initial Loads
    const depts = await fetch('?ajax=1&act=departments').then(r=>r.json());
    if(depts.ok) depts.items.forEach(i => {
        el('alt_f_dept').add(new Option(i.department_name, i.department_id));
        el('grid_d').add(new Option(i.department_name, i.department_id));
    });

el('alt_f_dept').onchange = () => altLoadEmployees(el('alt_f_dept').value);
    el('alt_f_emp').onchange = () => {
        const opt = el('alt_f_emp').options[el('alt_f_emp').selectedIndex];
        el('alt_f_per_day').value = opt.dataset.per_day || 0;
        altRecalc();
    };
    el('alt_f_month').onchange = () => syncPeriods('alt_f_month', 'alt_f_pt', 'alt_f_period');
    el('alt_f_pt').onchange = () => syncPeriods('alt_f_month', 'alt_f_pt', 'alt_f_period');
    
    syncPeriods('alt_f_month', 'alt_f_pt', 'alt_f_period');
    altFetchGrid();
});
</script>
<?php require_once __DIR__ . '/partials/footer.php'; ?>