View Code - 20260722

employee_salary_export.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* =============================================================================
   File: /erp/employee_salary_export.php
   Purpose: Employee salary export (XLSX default; TSV on &fmt=csv)
   Notes:
     - File-local only. Uses modules/auth/auth.php + core/db.php same as payment export.
     - Accepts POST['salary_rows'] JSON array of {employee_id,amount} OR
       use server-side source by POSTing:
         source_table, employee_col, amount_col, company_col (optional), company_id (optional)
     - Recognizes source="semi_monthly_salary_report" (from your semi_monthly_salary_report.php handoff form)
       and will fetch rows from that table (company_id, month_key, period_label, optional dept)
     - Will look up bank details from employee_bank_data (fallback: employees table)
   ============================================================================= */

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

const AMOUNT_DECIMALS       = 0;
const REMARKS_DEFAULT      = '';
const PAYMENT_TYPE_DEFAULT = 'N';

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

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

if (!isset($pdo) || !($pdo instanceof PDO)) {
    require_once __DIR__ . '/core/db.php';
}

/* ----------------- helpers (copied / adapted) ----------------- */

function ci_idx(array $headers, array $needles)
{
    foreach ($needles as $needle) {
        foreach ($headers as $i => $h) {
            if (strcasecmp(trim((string)$h), trim((string)$needle)) === 0) {
                return (int)$i;
            }
        }
    }
    return -1;
}

function fmt_amt($n)
{
    return number_format((float)$n, AMOUNT_DECIMALS, '.', '');
}

function payment_type_for_ifsc(?string $ifsc): string
{
    $x = strtoupper(trim((string)$ifsc));
    return (strpos($x, 'ICIC0') === 0) ? 'I' : PAYMENT_TYPE_DEFAULT;
}

function get_company_debit_ac(PDO $pdo, int $company_id): string
{
    if (!table_exists($pdo, 'company_bank_accounts')) {
        return '';
    }

$cols = table_cols($pdo, 'company_bank_accounts');

foreach (
        [
            'debit_ac_no',
            'debit_account_no',
            'debit_account',
            'bank_account_no',
            'account_no',
            'ac_no'
        ] as $c
    ) {
        if (isset($cols[$c])) {
            try {
                $st = $pdo->prepare(
                    "SELECT $c
                     FROM company_bank_accounts
                     WHERE company_id = ?
                     ORDER BY id DESC
                     LIMIT 1"
                );
                $st->execute([$company_id]);
                return trim((string)$st->fetchColumn());
            } catch (Throwable $e) {
                return '';
            }
        }
    }

return '';
}

/* -------- Minimal XLSX builder (reused) -------- */

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

function xlsx_xml_escape(string $s): string
{
    return htmlspecialchars($s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function xlsx_build_sheet_xml(array $rows): string
{
    $xml =
        '<?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++;
        $xml .= '<row r="' . $r . '">';
        $c = 0;

foreach ($row as $val) {
            $c++;
            $addr = xlsx_col_name($c) . $r;
            $xml .=
                '<c r="' . $addr . '" t="inlineStr"><is><t>' .
                xlsx_xml_escape((string)$val) .
                '</t></is></c>';
        }

$xml .= '</row>';
    }

return $xml . '</sheetData></worksheet>';
}

function xlsx_zip_build(string $sheetName, array $rows): string
{
    $sheetName = $sheetName !== '' ? mb_substr($sheetName, 0, 31) : 'Sheet1';

$tmp = tempnam(sys_get_temp_dir(), 'xlsx_');
    if (file_exists($tmp)) {
        unlink($tmp);
    }

$sheetXml = xlsx_build_sheet_xml($rows);
    $coreDate = gmdate('Y-m-d\TH:i:s\Z');

$content_types = '<?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="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
  <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>';

$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"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/package/2006/relationships/extended-properties" Target="docProps/app.xml"/>
</Relationships>';

$app = '<?xml version="1.0" encoding="UTF-8"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/extended-properties"
            xmlns:vt="http://schemas.openxmlformats.org/officeDocument/docPropsVTypes">
  <Application>ERP</Application>
</Properties>';

$core = '<?xml version="1.0" encoding="UTF-8"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties"
                   xmlns:dc="http://purl.org/dc/elements/1.1/"
                   xmlns:dcterms="http://purl.org/dc/terms/"
                   xmlns:dcmitype="http://purl.org/dc/dcmitype/"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <dc:creator>ERP</dc:creator>
  <cp:lastModifiedBy>ERP</cp:lastModifiedBy>
  <dcterms:created xsi:type="dcterms:W3CDTF">' . $coreDate . '</dcterms:created>
  <dcterms:modified xsi:type="dcterms:W3CDTF">' . $coreDate . '</dcterms:modified>
</cp:coreProperties>';

$workbook = '<?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="' . xlsx_xml_escape($sheetName) . '" sheetId="1" r:id="rId1"/>
  </sheets>
</workbook>';

$wb_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>';

$styles = '<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="1">
    <font>
      <sz val="11"/>
      <color theme="1"/>
      <name val="Calibri"/>
      <family val="2"/>
    </font>
  </fonts>
  <fills count="1">
    <fill>
      <patternFill patternType="none"/>
    </fill>
  </fills>
  <borders count="1">
    <border>
      <left/><right/><top/><bottom/><diagonal/>
    </border>
  </borders>
  <cellStyleXfs count="1">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
  </cellStyleXfs>
  <cellXfs count="1">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
  </cellXfs>
</styleSheet>';

$zip = new ZipArchive();
    if (!$zip->open($tmp, ZipArchive::CREATE)) {
        throw new RuntimeException('Zip open failed');
    }

$zip->addFromString('[Content_Types].xml', $content_types);
    $zip->addFromString('_rels/.rels', $rels);
    $zip->addFromString('docProps/app.xml', $app);
    $zip->addFromString('docProps/core.xml', $core);
    $zip->addFromString('xl/workbook.xml', $workbook);
    $zip->addFromString('xl/_rels/workbook.xml.rels', $wb_rels);
    $zip->addFromString('xl/styles.xml', $styles);
    $zip->addFromString('xl/worksheets/sheet1.xml', $sheetXml);

$zip->close();

return $tmp;
}

/* ----------------- Build salary rows server-side (flexible) ----------------- */

function build_salary_rows_server(
    PDO $pdo,
    int $company_id,
    ?string $src_table,
    ?string $emp_col,
    ?string $amt_col,
    ?string $company_col
): array {
    $cands = [];

if (
        $src_table &&
        table_exists($pdo, $src_table) &&
        $emp_col &&
        $amt_col &&
        col_exists($pdo, $src_table, $emp_col) &&
        col_exists($pdo, $src_table, $amt_col)
    ) {
        $cands[] = [
            'table'       => $src_table,
            'emp_col'     => $emp_col,
            'amt_col'     => $amt_col,
            'company_col' => $company_col
        ];
    } else {
        if (
            table_exists($pdo, 'employee_salary') &&
            col_exists($pdo, 'employee_salary', 'employee_id') &&
            col_exists($pdo, 'employee_salary', 'amount')
        ) {
            $cands[] = [
                'table'       => 'employee_salary',
                'emp_col'     => 'employee_id',
                'amt_col'     => 'amount',
                'company_col' => null
            ];
        }

if (
            table_exists($pdo, 'employee_payments') &&
            col_exists($pdo, 'employee_payments', 'employee_id') &&
            col_exists($pdo, 'employee_payments', 'net_amount')
        ) {
            $cands[] = [
                'table'       => 'employee_payments',
                'emp_col'     => 'employee_id',
                'amt_col'     => 'net_amount',
                'company_col' => null
            ];
        }
    }

foreach ($cands as $cand) {
        $table = $cand['table'];
        $empc  = $cand['emp_col'];
        $amtc  = $cand['amt_col'];
        $compc = $cand['company_col'];

$where = ($compc && col_exists($pdo, $table, $compc))
            ? "WHERE $compc = ?"
            : "";

$sql =
            "SELECT $empc AS employee_id, SUM($amtc) AS amount
             FROM `$table`
             " . ($where !== "" ? $where : "") . "
             GROUP BY $empc";

try {
            $st = $pdo->prepare($sql);
            if ($where !== "") {
                $st->execute([$company_id]);
            } else {
                $st->execute();
            }

$out = [];
            foreach ($st as $r) {
                $eid = (int)($r['employee_id'] ?? 0);
                $amt = (float)($r['amount'] ?? 0);
                if ($eid > 0 && $amt > 0) {
                    $out[] = [
                        'employee_id' => $eid,
                        'amount'      => $amt
                    ];
                }
            }

if (!empty($out)) {
                return $out;
            }
        } catch (Throwable $e) {
            // ignore and try next candidate
        }
    }

return [];
}

/* ----------------- Inputs ----------------- */

$rows_json = $_POST['salary_rows'] ?? '[]';
$fmt       = strtolower($_GET['fmt'] ?? $_POST['fmt'] ?? 'xlsx');

$src_table  = trim((string)($_POST['source_table']  ?? ''));
$emp_col    = trim((string)($_POST['employee_col']  ?? ''));
$amt_col    = trim((string)($_POST['amount_col']    ?? ''));
$company_col = trim((string)($_POST['company_col'] ?? ''));

/* ----------------- Special case: semi_monthly_salary_report handoff ----------------- */

$pay_rows = [];

if (
    isset($_POST['source']) &&
    $_POST['source'] === 'semi_monthly_salary_report'
) {
    $hk          = $_POST['month_key']    ?? null;
    $pl          = $_POST['period_label'] ?? null;
    $dept_filter = isset($_POST['dept']) ? (int)$_POST['dept'] : 0;

$cid = $company_id;
    if (
        !empty($_POST['company_id']) &&
        (int)$_POST['company_id'] === $company_id
    ) {
        $cid = (int)$_POST['company_id'];
    }

if ($hk && $pl) {
        $sql =
            "SELECT employee_id,
                    net_pay AS amount,
                    beneficiary_name,
                    account_number,
                    ifsc,
                    payment_type
             FROM semi_monthly_salary_report
             WHERE company_id = ?
               AND month_key  = ?
               AND period_label = ?";

$params = [$cid, $hk, $pl];

if (
            $dept_filter > 0 &&
            col_exists($pdo, 'semi_monthly_salary_report', 'department_id')
        ) {
            $sql     .= " AND department_id = ?";
            $params[] = $dept_filter;
        }

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

foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
            $amt = (float)($r['amount'] ?? 0);
            $eid = (int)($r['employee_id'] ?? 0);

if ($eid > 0 && $amt > 0) {
                $entry = [
                    'employee_id' => $eid,
                    'amount'      => $amt
                ];

if (isset($r['beneficiary_name'])) {
                    $entry['beneficiary_name'] = $r['beneficiary_name'];
                }
                if (isset($r['account_number'])) {
                    $entry['account_number'] = $r['account_number'];
                }
                if (isset($r['ifsc'])) {
                    $entry['ifsc'] = $r['ifsc'];
                }
                if (isset($r['payment_type'])) {
                    $entry['payment_type'] = $r['payment_type'];
                }

$pay_rows[] = $entry;
            }
        }
    }
}

/* ----------------- Fallback to posted JSON or server discovery ----------------- */

if (empty($pay_rows)) {
    $pay_rows = json_decode($rows_json, true);

if (!is_array($pay_rows) || empty($pay_rows)) {
        $pay_rows = build_salary_rows_server(
            $pdo,
            $company_id,
            $src_table,
            $emp_col,
            $amt_col,
            $company_col
        );
    }
}

if (empty($pay_rows)) {
    http_response_code(400);
    exit(
        'No rows to export. Provide salary_rows JSON or a valid source_table + columns.'
    );
}

/* ----------------- Output headers ----------------- */

$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'
];

$idx = [
    'debit'   => ci_idx($headers, ['Debit A/c Number']),
    'account' => ci_idx($headers, ['Beneficiary A/c Number']),
    'name'    => ci_idx($headers, ['Beneficiary Name']),
    'amount'  => ci_idx($headers, ['Amount']),
    'ptype'   => ci_idx(
        $headers,
        ['Payment Type (Mandatory for all types of payments)', 'Payment Type']
    ),
    'pdate'   => ci_idx($headers, ['Payment date', 'Payment Date']),
    'ifsc'    => ci_idx($headers, ['IFSC Code', 'IFSC']),
    'mobile'  => ci_idx($headers, ['Beneficiary Mobile No.', 'Mobile']),
    'email'   => ci_idx($headers, ['Beneficiary email-id', 'Email']),
    'remarks' => ci_idx($headers, ['Remarks']),
    'narr'    => ci_idx($headers, ['Credit Narration', 'Narration']),
];

/* ----------------- Fetch employee / bank details ----------------- */

$emp_ids = array_values(
    array_filter(
        array_unique(
            array_map(
                function ($r) {
                    return (int)($r['employee_id'] ?? 0);
                },
                $pay_rows
            )
        )
    )
);

$bank = [];
$meta = [];

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

try {

$sql = "
        SELECT id,
               name AS nm,
               '' AS mobile,
               '' AS email
        FROM company_employee_master
        WHERE company_id = ?
          AND id IN ($in)
    ";

$params = array_merge([$company_id], $emp_ids);
    $q      = $pdo->prepare($sql);
    $q->execute($params);

foreach ($q as $r) {
        $eid = (int)$r['id'];

$bank[$eid] = [
            'name'       => trim((string)$r['nm']),
            'ac'         => '',
            'ifsc'       => '',
            'entry_name' => ''
        ];

$meta[$eid] = [
            'mobile' => '',   // force blank
            'email'  => ''    // optional: blank email bhi
        ];
    }

} catch (Throwable $e) {
    /* ignore */
}try {

$params = array_merge([$company_id], $emp_ids);
    $q      = $pdo->prepare($sql);
    $q->execute($params);

foreach ($q as $r) {
        $eid = (int)$r['id'];

$bank[$eid] = [
            'name'       => trim((string)$r['nm']),
            'ac'         => '',
            'ifsc'       => '',
            'entry_name' => ''
        ];

$meta[$eid] = [
            'mobile' => '',   // force blank
            'email'  => ''    // optional: blank email bhi
        ];
    }

} catch (Throwable $e) {
    /* ignore */
}

try {
        if (table_exists($pdo, 'employee_bank_data')) {
            $params = array_merge([$company_id], $emp_ids);
            $sql =
                "SELECT employee_id,
                        beneficiary_name,
                        beneficiary_ac_number AS account_no,
                        ifsc_code AS ifsc,
                        entry_name
                 FROM employee_bank_data
                 WHERE company_id = ?
                   AND employee_id IN ($in)
                 ORDER BY employee_id, updated_at DESC, id DESC";

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

foreach ($st as $r) {
                $eid = (int)$r['employee_id'];

if (!isset($bank[$eid])) {
                    $bank[$eid] = [
                        'name'       => '',
                        'ac'         => '',
                        'ifsc'       => '',
                        'entry_name' => ''
                    ];
                }

$nm = trim((string)$r['beneficiary_name']);
                if ($nm !== '') {
                    $bank[$eid]['name'] = $nm;
                }
                if ($bank[$eid]['ac'] === '') {
                    $bank[$eid]['ac'] = trim((string)$r['account_no']);
                }
                if ($bank[$eid]['ifsc'] === '') {
                    $bank[$eid]['ifsc'] = strtoupper(trim((string)$r['ifsc']));
                }
                if ($bank[$eid]['entry_name'] === '') {
                    $bank[$eid]['entry_name'] = trim((string)$r['entry_name']);
                }
            }
        }
    } catch (Throwable $e) {
        /* ignore */
    }
}

/* ----------------- Prepare export rows ----------------- */

$rows_for   = [];
$rows_for[] = $headers;

$debit_ac = get_company_debit_ac($pdo, $company_id);

$payDate    = date('Y-m-d');
$payDateFmt = date('d-M-Y', strtotime($payDate));
if (
    isset($_POST['source']) &&
    $_POST['source'] === 'semi_monthly_salary_report' &&
    !empty($_POST['month_key']) &&
    !empty($_POST['period_label'])
) {
    // Example: Dec-2025 (1-15)
    $narration = date('M-Y', strtotime($_POST['month_key'] . '-01'))
               . ' (' . $_POST['period_label'] . ')';
} else {
    // Old behavior (unchanged)
    $narration = date('M-Y', strtotime($payDate));
}

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

if ($eid <= 0 || $amt <= 0) {
        continue;
    }

// employee master name (LOCKED for remarks)
    $emp_name = isset($bank[$eid]['name'])
        ? (string)$bank[$eid]['name']
        : '';

// beneficiary record (for bank columns only)
    $rec = [
        'name' => '',
        'ac'   => '',
        'ifsc' => ''
    ];

$info = $meta[$eid] ?? ['mobile' => '', 'email' => ''];

// beneficiary data ONLY for beneficiary columns
    if (!empty($r['beneficiary_name'])) {
        $rec['name'] = $r['beneficiary_name'];
    }
    if (!empty($r['account_number'])) {
        $rec['ac'] = $r['account_number'];
    }
    if (!empty($r['ifsc'])) {
        $rec['ifsc'] = strtoupper($r['ifsc']);
    }

$line = array_fill(0, count($headers), '');

if ($idx['debit']   >= 0) $line[$idx['debit']]   = $debit_ac;
    if ($idx['account'] >= 0) $line[$idx['account']] = (string)$rec['ac'];
    if ($idx['name']    >= 0) $line[$idx['name']]    = (string)$rec['name'];
    if ($idx['amount']  >= 0) $line[$idx['amount']]  = fmt_amt($amt);
    if ($idx['ptype']   >= 0) $line[$idx['ptype']]   = payment_type_for_ifsc($rec['ifsc']);
    if ($idx['pdate']   >= 0) $line[$idx['pdate']]   = $payDateFmt;
    if ($idx['ifsc']    >= 0) $line[$idx['ifsc']]    = (string)$rec['ifsc'];
    $line[$idx['mobile']]  = '';
    if ($idx['email']   >= 0) $line[$idx['email']]   = (string)($info['email'] ?? '');

// ✅ REMARKS = employee master name ONLY
    if ($idx['remarks'] >= 0) {
        $line[$idx['remarks']] = $emp_name;
    }

if ($idx['narr'] >= 0) {
        $line[$idx['narr']] = $narration;
    }

$rows_for[] = $line;
}

/* ----------------- Build filename & stream ----------------- */

$st = $pdo->prepare(
    "SELECT COALESCE(bank_user_id, '')
     FROM company_bank_accounts
     WHERE company_id = ?
     ORDER BY id DESC
     LIMIT 1"
);
$st->execute([$company_id]);

$bank_user_id = trim((string)$st->fetchColumn()) ?: (string)$company_id;

$seqStr   = '01';
$dateStr  = date('dmY');
$sheetName = "{$bank_user_id}_{$bank_user_id}UPLD_{$dateStr}_{$seqStr}";
$fileName  = $sheetName . ($fmt === 'xlsx' ? '.xlsx' : '.tsv');

if ($fmt === 'xlsx') {
    $xlsxPath = xlsx_zip_build($sheetName, $rows_for);

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

readfile($xlsxPath);
    @unlink($xlsxPath);
    exit;
}

/* ----------------- TSV fallback ----------------- */

$out = fopen('php://temp', 'w+');
fwrite($out, "\xEF\xBB\xBF");

foreach ($rows_for as $row) {
    fwrite($out, implode("\t", array_map('strval', $row)) . "\r\n");
}

rewind($out);

header('Content-Type: text/tab-separated-values; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $fileName . '"');

fpassthru($out);
fclose($out);
exit;