View Code - 20260721

semi_recon.php | 20260721_154033.php Initial Bulk Import

<?php
// modules/expense_recon/api/semi_recon.php
// POST JSON API
// Input example:
// {
//   "company_id": 1,
//   "pay_period_start":"2025-11-01", "pay_period_end":"2025-11-15",
//   "bank_rows": [ { "date":"2025-11-08", "narration":"CMS/001823...", "ref_no":"CMS001823...", "amount":28260 } ],
//   // optional: override semi rows (useful for testing)
//   "semi_rows": [ {"department":"Folding","amount":21000}, {"department":"Semi","amount":21900} ],
//   "options": { "alloc_rules": { ... } }
// }

require_once __DIR__ . '/../../auth/page_acl.php';
$ctx = page_require_access('expense_recon');
$pdo = $ctx['pdo'] ?? null;
header('Content-Type: application/json; charset=utf-8');
if (!($pdo instanceof PDO)) { http_response_code(500); echo json_encode(['error'=>'pdo_missing']); exit; }

// read input
$raw = file_get_contents('php://input');
$in = json_decode($raw, true);
if (!is_array($in)) { http_response_code(400); echo json_encode(['error'=>'invalid_json']); exit; }

$company_id = (int)($in['company_id'] ?? $ctx['company_id'] ?? 0);
$bank_rows  = $in['bank_rows'] ?? [];
$options    = $in['options'] ?? [];

// derive period: prefer pay_period_start/pay_period_end, else month + half
$start = $in['pay_period_start'] ?? null;
$end   = $in['pay_period_end'] ?? null;
if (!$start || !$end) {
    // accept month + half
    $month = isset($in['month']) ? (int)$in['month'] : null;
    $year  = isset($in['year'])  ? (int)$in['year'] : null;
    $half  = isset($in['half'])  ? strtoupper($in['half']) : null; // H1 or H2
    if ($month && $year && in_array($half, ['H1','H2'])) {
        $from_day = ($half === 'H1') ? 1 : 16;
        $start = sprintf('%04d-%02d-%02d', $year, $month, $from_day);
        if ($half === 'H1') {
            $end = sprintf('%04d-%02d-15', $year, $month);
        } else {
            $end = date('Y-m-t', strtotime(sprintf('%04d-%02d-01', $year, $month)));
        }
    }
}

if (!$start || !$end) {
    http_response_code(400);
    echo json_encode(['error'=>'missing_period','message'=>'pay_period_start/pay_period_end or month+year+half required']);
    exit;
}

// default allocation rules (folding split + dept mapping)
$alloc_rules = [
    'folding' => ['repair_pct'=>30, 'loom_pct'=>70],
    'dept_map' => [
        'Semi' => 'semi_expense',
        'Bobin' => 'bobin_expense',
        'Folding' => 'folding_expense',
        'TFO' => 'tfo_expense',
        'Helper' => 'helper_expense'
    ],
    'default_to' => 'loom_expense'
];
if (!empty($options['alloc_rules']) && is_array($options['alloc_rules'])) {
    $alloc_rules = array_replace_recursive($alloc_rules, $options['alloc_rules']);
}

/**
 * Try to load saved semi-monthly rows from semi_monthly_salary_report table.
 * Returns array of ['department'=>..., 'amount'=>float]
 */
function load_semi_saved(PDO $pdo, int $cid, string $start, string $end) {
    // compute month_key and period_label from given start date
    $s_ts = strtotime($start);
    $year = (int)date('Y', $s_ts);
    $month = (int)date('n', $s_ts);
    // determine half by start day
    $start_day = (int)date('j', $s_ts);
    $period_label = ($start_day <= 15) ? 'H1' : 'H2';
    $month_key = date('Y-m-01', $s_ts);

$sql = "SELECT COALESCE(department_name,'Unknown') AS department, SUM(net_pay) AS amount
            FROM semi_monthly_salary_report
            WHERE company_id = :cid AND month_key = :mk AND period_label = :pl
            GROUP BY department";
    $st = $pdo->prepare($sql);
    $st->execute([':cid'=>$cid, ':mk'=>$month_key, ':pl'=>$period_label]);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC);
    $out = [];
    foreach ($rows as $r) {
        $out[] = ['department'=>$r['department'], 'amount'=> (float)$r['amount']];
    }
    return $out;
}

/**
 * Fallback: compute semi totals live using same logic as semi_monthly_salary_report.php
 * This tries to mirror the compute loop: fetch employees with payroll_period_name='Semi-monthly',
 * compute per-employee net for the half using attendance, extras, deduction, advances, then group by department.
 */
function compute_semi_live(PDO $pdo, int $cid, string $start, string $end) {
    // 1) fetch employees for company where payroll_period_name='Semi-monthly' and active
    $sqlEmp = "SELECT id, name, department_id, department_name, designation_name, salary_type_name,
                      per_day, basic_salary, payment_type, beneficiary_name, account_number, ifsc
               FROM company_employee_master
               WHERE company_id = :cid
                 AND payroll_period_name = 'Semi-monthly'
                 AND COALESCE(is_active,0) = 1";
    $stEmp = $pdo->prepare($sqlEmp);
    $stEmp->execute([':cid'=>$cid]);
    $emps = $stEmp->fetchAll(PDO::FETCH_ASSOC);
    if (empty($emps)) return [];

// prepare statements used in original file
    $stAtt = $pdo->prepare("SELECT COALESCE(SUM(total_attendance),0) FROM manual_attendance_data WHERE company_id=? AND employee_id=? AND period_start >= ? AND period_end <= ?");
    $stExtra = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_extra WHERE company_id=? AND employee_id=? AND entry_date BETWEEN ? AND ?");
    $stDed = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_deduction WHERE company_id=? AND employee_id=? AND entry_date BETWEEN ? AND ?");
    $stAdv = $pdo->prepare("SELECT id, remaining_amount, `date` FROM employee_advance WHERE company_id=? AND employee_id=? AND status='OPEN' AND `date` BETWEEN ? AND ?");

// compute month/day counts used in original
    $from_ts = strtotime($start);
    $tot_days_in_month = (int)date('t', $from_ts);
    $start_day = (int)date('j', $from_ts);
    $period_label = ($start_day <= 15) ? 'H1' : 'H2';
    $days_in_half = ($period_label === 'H1') ? 15 : max(0, $tot_days_in_month - 15);

$deptAgg = [];
    foreach ($emps as $e) {
        $emp_id = (int)$e['id'];
        // attendance sum in half
        $stAtt->execute([$cid, $emp_id, $start, $end]);
        $att_days = (float)$stAtt->fetchColumn();

$per_day = (float)($e['per_day'] ?? 0);
        $basic = (float)($e['basic_salary'] ?? 0);
        if ($per_day <= 0 && $basic > 0) $per_day = $basic / max($tot_days_in_month,1);

$stype = trim((string)($e['salary_type_name'] ?? ''));
        if (strcasecmp($stype,'Attendance')===0) {
            $calc_basic = $per_day * $att_days;
        } elseif (strcasecmp($stype,'Fixed')===0) {
            $calc_basic = ($basic>0) ? ($basic * ($days_in_half / max($tot_days_in_month,1))) : 0;
        } elseif (strcasecmp($stype,'Fixed Attendance')===0) {
            $calc_basic = ($att_days>0 ? $per_day * $att_days : ($basic * ($days_in_half / max($tot_days_in_month,1))));
        } else {
            $calc_basic = $basic>0 ? ($basic * ($days_in_half / max($tot_days_in_month,1))) : ($per_day * $att_days);
        }

$stExtra->execute([$cid, $emp_id, $start, $end]);
        $extra = (float)$stExtra->fetchColumn();
        $stDed->execute([$cid, $emp_id, $start, $end]);
        $ded = (float)$stDed->fetchColumn();

$stAdv->execute([$cid, $emp_id, $start, $end]);
        $adv_deduct = 0.0;
        foreach ($stAdv->fetchAll(PDO::FETCH_ASSOC) as $a) {
            $rem = (float)$a['remaining_amount'];
            if ($rem <= 0) continue;
            $adv_deduct += $rem;
        }

$gross = $calc_basic + $extra;
        $net = round($gross - $ded - $adv_deduct, 0);

$deptName = $e['department_name'] ?: ('Dept-' . ((int)$e['department_id'] ?: 0));
        $deptAgg[$deptName] = ($deptAgg[$deptName] ?? 0) + $net;
    }

$out = [];
    foreach ($deptAgg as $d=>$amt) $out[] = ['department'=>$d,'amount'=> (float)$amt];
    return $out;
}

/**
 * Consolidate semi_rows source:
 * priority:
 * 1) if incoming JSON contains semi_rows -> use it
 * 2) try saved table semi_monthly_salary_report
 * 3) fall back to live compute
 */
$semi_rows = $in['semi_rows'] ?? null;
if ($semi_rows === null) {
    // try saved
    $semi_rows = load_semi_saved($pdo, $company_id, $start, $end);
    if (empty($semi_rows)) {
        // fallback to compute live
        $semi_rows = compute_semi_live($pdo, $company_id, $start, $end);
    }
}

// normalize into deptTotals map
$deptTotals = [];
foreach ($semi_rows as $r) {
    $dept = trim($r['department'] ?? $r['dept'] ?? 'Unknown');
    $amt  = (float)($r['amount'] ?? $r['amt'] ?? 0);
    if ($amt <= 0) continue;
    $deptTotals[$dept] = ($deptTotals[$dept] ?? 0.0) + $amt;
}
$grandTotal = array_sum($deptTotals);

// optional: quick response when no dept totals found
if ($grandTotal <= 0) {
    echo json_encode([
        'company_id'=>$company_id,
        'pay_period_start'=>$start,
        'pay_period_end'=>$end,
        'dept_totals'=>$deptTotals,
        'grand_total'=>$grandTotal,
        'results'=>[],
        'warning'=>'No semi totals found for the period (empty saved table and live compute returned nothing).'
    ], JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE);
    exit;
}

// helper: allocate department amount with folding split
function allocate_dept_amount($dept, $amt, $alloc_rules) {
    $allocs = [];
    if (stripos($dept,'fold') !== false || stripos($dept,'folding') !== false) {
        $repair = round($amt * ($alloc_rules['folding']['repair_pct'] / 100.0), 2);
        $loom   = round($amt * ($alloc_rules['folding']['loom_pct'] / 100.0), 2);
        $allocs[] = ['account'=>'repair_expense','dept'=>$dept,'amount'=>$repair];
        $allocs[] = ['account'=>'loom_expense','dept'=>$dept,'amount'=>$loom];
    } else {
        $map = $alloc_rules['dept_map'] ?? [];
        $account = $map[$dept] ?? $map[ucfirst($dept)] ?? $alloc_rules['default_to'];
        $allocs[] = ['account'=>$account,'dept'=>$dept,'amount'=> round($amt,2)];
    }
    return $allocs;
}

// normalize bank rows
$norm_bank = [];
foreach ($bank_rows as $b) {
    $norm_bank[] = [
        'date' => $b['date'] ?? null,
        'narration' => trim((string)($b['narration'] ?? '')),
        'ref_no' => $b['ref_no'] ?? $b['ref'] ?? null,
        'amount' => (float)($b['amount'] ?? 0.0),
        'raw' => $b
    ];
}

// perform matching & persist results
$results = [];
foreach ($norm_bank as $b) {
    $bAmt = $b['amount'];
    $matched = false;
    $match_details = [];
    $suggested_allocs = [];

// 1) exact match to department
    foreach ($deptTotals as $dept => $dAmt) {
        if (abs($dAmt - $bAmt) <= 0.5) {
            $match_details[] = ['reason'=>'matched_dept_exact','dept'=>$dept,'dept_amount'=>round($dAmt,2),'confidence'=>0.92];
            $suggested_allocs = array_merge($suggested_allocs, allocate_dept_amount($dept, $dAmt, $alloc_rules));
            $matched = true;
            break;
        }
    }

// 2) exact match to grand total
    if (!$matched && abs($grandTotal - $bAmt) <= 0.5) {
        foreach ($deptTotals as $dept => $dAmt) {
            $suggested_allocs = array_merge($suggested_allocs, allocate_dept_amount($dept, $dAmt, $alloc_rules));
        }
        $match_details[] = ['reason'=>'matched_grand_total','confidence'=>0.95];
        $matched = true;
    }

// 3) narration hint (dept name appears)
    if (!$matched && !empty($b['narration'])) {
        foreach ($deptTotals as $dept => $dAmt) {
            if (stripos($b['narration'], $dept) !== false) {
                $alloc_amount = min($dAmt, $bAmt);
                $suggested_allocs = array_merge($suggested_allocs, allocate_dept_amount($dept, $alloc_amount, $alloc_rules));
                $match_details[] = ['reason'=>'narration_hint','dept'=>$dept,'confidence'=>0.7];
                $matched = true;
                // do not break; allow multiple narration hints
            }
        }
    }

// 4) proportional fallback across departments (based on dept totals)
    if (!$matched && $grandTotal > 0) {
        foreach ($deptTotals as $dept => $dAmt) {
            $share = ($dAmt / $grandTotal) * $bAmt;
            $suggested_allocs = array_merge($suggested_allocs, allocate_dept_amount($dept, $share, $alloc_rules));
        }
        $match_details[] = ['reason'=>'proportional_fallback','confidence'=>0.35];
        $matched = true;
    }

// persist into expense_recon_results
    $insertSql = "INSERT INTO expense_recon_results
                  (company_id, source_type, source_ref, bank_row_json, match_json, allocations_json, status, created_by)
                  VALUES (:cid,'semi',:sref,:bankjson,:matchjson,:allocjson,:status,:created_by)";
    $st = $pdo->prepare($insertSql);
    $st->execute([
        ':cid'=>$company_id,
        ':sref'=>$b['ref_no'] ?? null,
        ':bankjson'=>json_encode($b, JSON_UNESCAPED_UNICODE),
        ':matchjson'=>json_encode($match_details, JSON_UNESCAPED_UNICODE),
        ':allocjson'=>json_encode($suggested_allocs, JSON_UNESCAPED_UNICODE),
        ':status'=> $matched ? 'auto_matched' : 'pending',
        ':created_by'=> $ctx['user']['id'] ?? null
    ]);
    $insId = $pdo->lastInsertId();

$results[] = [
        'expense_recon_id' => (int)$insId,
        'bank_row' => $b,
        'matched' => $matched,
        'match_details' => $match_details,
        'allocations' => $suggested_allocs
    ];
}

// final output
echo json_encode([
    'company_id'=>$company_id,
    'pay_period_start'=>$start,
    'pay_period_end'=>$end,
    'dept_totals'=>$deptTotals,
    'grand_total'=>$grandTotal,
    'results'=>$results
], JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE);
exit;