View Code - 20260722

expoeter_company_employee_master.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/expoeter_company_employee_master.php
   Purpose: Employee payment export (EXCEL .xlsx)
            - Employee bank details from company_employee_master
            - Bank format aligned (S=Remarks, T=Credit Narration)
            - Payment Type: ICICI = I, else N
   ============================================================================= */

error_reporting(E_ALL);
ini_set('display_errors', 1);

/* ---------- AUTH ---------- */
require_once __DIR__ . '/modules/auth/auth.php';
require_login();

$u          = auth_user();
$company_id = (int)($u['company_id'] ?? 0);

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

/* ---------- INPUT ---------- */
$rows_json = $_POST['salary_rows'] ?? '[]';
$pay_rows  = json_decode($rows_json, true);

if (!is_array($pay_rows) || empty($pay_rows)) {
    http_response_code(400);
    exit('No salary rows received');
}

/* ---------- HELPERS ---------- */
function paymentTypeFromIfsc(string $ifsc): string
{
    return (stripos($ifsc, 'ICIC') === 0) ? 'I' : 'N';
}

function colName(int $n): string
{
    $s = '';
    while ($n > 0) {
        $m = ($n - 1) % 26;
        $s = chr(65 + $m) . $s;
        $n = (int)(($n - $m - 1) / 26);
    }
    return $s;
}

/* ---------- HEADERS (BANK ORDER – FINAL) ---------- */
$headers = [
    'Debit A/c Number',
    'Beneficiary A/c Number',
    'Beneficiary Name',
    'Amount',
    'Payment Type (Mandatory for all types of payments)',
    'Payment date',
    'IFSC Code',
    'Beneficiary Mobile No.',
    'Beneficiary email-id',
    'Bene Address 1',
    'Bene Address 2',
    'Bene Address 3',
    'Bene Address 4',
    'Add detail 1',
    'Add detail 2',
    'Add detail 3',
    'Add detail 4',
    'Add detail 5',
    'Remarks',
    'Credit Narration'
];

/* ---------- COMPANY BANK INFO ---------- */
$st = $pdo->prepare("
    SELECT debit_ac_no, bank_user_id
    FROM company_bank_accounts
    WHERE company_id = ?
    ORDER BY id DESC
    LIMIT 1
");
$st->execute([$company_id]);
$bankRow = $st->fetch(PDO::FETCH_ASSOC) ?: [];

$debit_ac     = trim((string)($bankRow['debit_ac_no'] ?? ''));
$bank_user_id = trim((string)($bankRow['bank_user_id'] ?? ''));

if ($bank_user_id === '') {
    $bank_user_id = (string)$company_id; // safe fallback
}

/* ---------- EMPLOYEE IDS ---------- */
$emp_ids = array_values(array_unique(array_map(
    fn($r) => (int)($r['employee_id'] ?? 0),
    $pay_rows
)));

if (empty($emp_ids)) {
    exit('No valid employee IDs');
}

$in = implode(',', array_fill(0, count($emp_ids), '?'));

/* ---------- FETCH EMPLOYEE DATA ---------- */
$sql = "
    SELECT
        id,
        name,
        beneficiary_name,
        account_number,
        ifsc
    FROM company_employee_master
    WHERE company_id = ?
      AND id IN ($in)
";
$st = $pdo->prepare($sql);
$st->execute(array_merge([$company_id], $emp_ids));

$emp = [];
foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
    $emp[(int)$r['id']] = $r;
}

/* ---------- BUILD ROWS ---------- */
$rows   = [];
$rows[] = $headers;

$payDateFmt = date('d-M-Y');
$narration  = date('M-Y');

foreach ($pay_rows as $r) {
    $eid = (int)($r['employee_id'] ?? 0);
    $amt = (float)($r['amount'] ?? 0);

if ($eid <= 0 || $amt <= 0 || !isset($emp[$eid])) {
        continue;
    }

$e = $emp[$eid];

$rows[] = [
        $debit_ac,                              // Debit A/c
        $e['account_number'] ?? '',             // Beneficiary A/c
        $e['beneficiary_name'] ?? '',           // Beneficiary Name
        number_format($amt, 0, '.', ''),        // Amount

paymentTypeFromIfsc($e['ifsc'] ?? ''),  // Payment Type: I / N

$payDateFmt,                            // Payment date
        strtoupper($e['ifsc'] ?? ''),           // IFSC

'', '', '', '', '',                     // Mobile + Email + Bene Addr 1–4 (BLANK)
        '', '', '', '', '',                     // Add detail 1–5 (BLANK)

$e['name'] ?? '',                       // Remarks (Column S)
        $narration                              // Credit Narration (Column T)
    ];
}

/* ---------- XLSX SHEET XML ---------- */
$sheetXml = '<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
 xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetData>';

$r = 0;
foreach ($rows as $row) {
    $r++;
    $sheetXml .= '<row r="'.$r.'">';
    $c = 0;
    foreach ($row as $val) {
        $c++;
        $cell = colName($c).$r;
        $sheetXml .= '<c r="'.$cell.'" t="inlineStr"><is><t>'
            . htmlspecialchars((string)$val, ENT_QUOTES | ENT_XML1)
            . '</t></is></c>';
    }
    $sheetXml .= '</row>';
}
$sheetXml .= '</sheetData></worksheet>';

/* ---------- ZIP XLSX (EXCEL SAFE) ---------- */
$tmp = tempnam(sys_get_temp_dir(), 'xlsx_');
@unlink($tmp);

$zip = new ZipArchive();
$zip->open($tmp, ZipArchive::CREATE);

$zip->addFromString('[Content_Types].xml', '<?xml version="1.0" encoding="UTF-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
 <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
 <Default Extension="xml" ContentType="application/xml"/>
 <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
 <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
 <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>');

$zip->addFromString('_rels/.rels', '<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
 <Relationship Id="rId1"
  Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
  Target="xl/workbook.xml"/>
</Relationships>');

$zip->addFromString('xl/workbook.xml', '<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
 xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
 <sheets>
  <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
 </sheets>
</workbook>');

$zip->addFromString('xl/_rels/workbook.xml.rels', '<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
 <Relationship Id="rId1"
  Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
  Target="worksheets/sheet1.xml"/>
 <Relationship Id="rId2"
  Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"
  Target="styles.xml"/>
</Relationships>');

$zip->addFromString('xl/styles.xml', '<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"/>');

$zip->addFromString('xl/worksheets/sheet1.xml', $sheetXml);
$zip->close();

/* ---------- FILENAME ---------- */
$dateStr = date('dmY');
$file = $bank_user_id . '_' . $bank_user_id . 'UPLD_' . $dateStr . '_01.xlsx';

/* ---------- OUTPUT ---------- */
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$file.'"');
header('Content-Length: ' . filesize($tmp));

readfile($tmp);
@unlink($tmp);
exit;