View Code - 20260722

payment_export.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/payment_export.php
   Purpose: Bank payment export (CSV) for Employee Salary (Net Pay snapshot)
   Rules:
     - Beneficiary Name = beneficiary_name (fallback employee_name)
     - Remarks = employee_name
     - Narration = "<period_label> - <Mon-YYYY>"
     - Amount = net_pay (integer, 0 decimals)
     - All cells as TEXT (CSV as-is; Excel keeps leading zeros)
   Scope : Page-local only (no global/base edits)
   ============================================================================ */
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL); ini_set('display_errors', 1);

/* ---- Auth + PDO ---- */
require_once __DIR__ . '/modules/auth/auth.php';
require_login();
$u          = auth_user();
$company_id = (int)$u['company_id'];

$pdo = $GLOBALS['pdo'] ?? null;
if (!$pdo) { require __DIR__ . '/core/db.php'; }
if (!($pdo instanceof PDO)) { die('DB not available'); }

/* ---------- Inputs ---------- */
$month   = $_GET['month']   ?? date('Y-m');      // e.g. 2025-09
$period  = $_GET['period']  ?? 'FULL';           // 1-15 / 16-END / MONTHLY / FULL
$dept_id = (int)($_GET['dept_id'] ?? 0);
$ids     = isset($_POST['ids']) ? (array)$_POST['ids'] : [];

/* ---------- Helpers: safe SQL list ---------- */
function sql_int_list(array $ids): string {
  $out = [];
  foreach ($ids as $v) { $n = (int)$v; if ($n>0) $out[] = (string)$n; }
  return $out ? implode(',', $out) : '';
}

/* ---------- Step 1: Load salary snapshot rows (no bank columns) ---------- */
/* Assumed snapshot table: monthly_salary_report (adjust if needed)
   Columns used: id, company_id, employee_id, month, period, net_pay, dept_id */
$sql = "SELECT r.id, r.employee_id, r.net_pay, r.dept_id,
               e.name AS employee_name
        FROM monthly_salary_report r
        JOIN company_employee_master e
             ON e.id = r.employee_id
        WHERE r.company_id = :cid
          AND r.month = :m
          AND r.period = :p";
$params = [':cid'=>$company_id, ':m'=>$month, ':p'=>$period];

if ($dept_id > 0) { $sql .= " AND r.dept_id = :d"; $params[':d'] = $dept_id; }
if (!empty($ids)) {
  $in = sql_int_list($ids);
  if ($in !== '') { $sql .= " AND r.id IN ($in)"; }
}

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!$rows) { die("No salary rows found."); }

/* ---------- Step 2: Detect bank table & columns dynamically ---------- */
function findExistingTable(PDO $pdo, array $candidates): ?string {
  $place = implode(',', array_fill(0, count($candidates), '?'));
  $q = $pdo->prepare("
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ($place)
    LIMIT 1
  ");
  $q->execute($candidates);
  $t = $q->fetchColumn();
  return $t ?: null;
}
function pickExistingColumn(PDO $pdo, string $table, array $candidates): ?string {
  if (!$table) return null;
  $place = implode(',', array_fill(0, count($candidates), '?'));
  $params = $candidates;
  array_unshift($params, $table);
  $q = $pdo->prepare("
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
      AND COLUMN_NAME IN ($place)
    ORDER BY FIELD(COLUMN_NAME, $place) LIMIT 1
  ");
  $q->execute($params);
  $c = $q->fetchColumn();
  return $c ?: null;
}

$bankTable = findExistingTable($pdo, [
  'employee_bank_data', 'employee_bank_details', 'employee_bank'
]);

if (!$bankTable) {
  // Bank table missing — export can still proceed with empty IFSC/Account/Beneficiary (but usable remarks)
  $bankAccountCol   = null;
  $bankIFSCCol      = null;
  $beneficiaryCol   = null;
} else {
  $bankAccountCol = pickExistingColumn($pdo, $bankTable,
    ['account_no','account','ac_no','bank_account','account_number']);
  $bankIFSCCol = pickExistingColumn($pdo, $bankTable,
    ['ifsc','ifsc_code','bank_ifsc','ifscno']);
  $beneficiaryCol = pickExistingColumn($pdo, $bankTable,
    ['beneficiary_name','account_holder','holder_name','name_in_bank','name']);
}

/* ---------- Step 3: Fetch bank details in bulk (only existing columns) ---------- */
$empIds = array_values(array_unique(array_map(fn($r)=>(int)$r['employee_id'], $rows)));
$bankMap = []; // employee_id => ['acc'=>..., 'ifsc'=>..., 'ben'=>...]
if ($bankTable && $empIds) {
  $in = sql_int_list($empIds);
  $cols = ["employee_id"];
  if ($bankAccountCol) $cols[] = "`$bankAccountCol` AS acc";
  if ($bankIFSCCol)    $cols[] = "`$bankIFSCCol` AS ifsc";
  if ($beneficiaryCol) $cols[] = "`$beneficiaryCol` AS ben";
  $colSQL = implode(", ", $cols);

$sqlB = "SELECT $colSQL
           FROM `$bankTable`
           WHERE company_id = :cid AND employee_id IN ($in)";
  $stb = $pdo->prepare($sqlB);
  $stb->execute([':cid'=>$company_id]);
  while ($b = $stb->fetch(PDO::FETCH_ASSOC)) {
    $eid = (int)$b['employee_id'];
    $bankMap[$eid] = [
      'acc' => isset($b['acc']) ? (string)$b['acc'] : '',
      'ifsc'=> isset($b['ifsc'])? (string)$b['ifsc']: '',
      'ben' => isset($b['ben']) ? (string)$b['ben'] : '',
    ];
  }
}

/* ---------- Step 4: Emit CSV (TEXT cells; Excel keeps zeros) ---------- */
$filename = "PaymentExport_" . $month . "_" . $period . ".csv";
header('Content-Type: text/csv; charset=UTF-8');
header("Content-Disposition: attachment; filename=\"$filename\"");
$out = fopen("php://output", "w");

/* Header row (match your bank template if needed) */
fputcsv($out, ['Beneficiary Name','Account No','IFSC','Amount','Remarks','Narration']);

/* Narration label */
$label = $period . " - " . date('M-Y', strtotime($month . "-01"));

foreach ($rows as $r) {
  $eid        = (int)$r['employee_id'];
  $empName    = (string)$r['employee_name'];
  $netPay     = (int)round($r['net_pay']);

$b = $bankMap[$eid] ?? ['acc'=>'', 'ifsc'=>'', 'ben'=>''];
  $beneficiary = $b['ben'] !== '' ? $b['ben'] : $empName;
  $acc = (string)$b['acc'];   // keep as text
  $ifc = (string)$b['ifsc'];  // keep as text

fputcsv($out, [
    $beneficiary,
    $acc,
    $ifc,
    $netPay,
    $empName,
    $label
  ]);
}

fclose($out);
exit;