« Back to History
payment_export_semi.php
|
20260921_175631.php
Initial Domain Snapshot
Copy Code
<?php /* ============================================================================ File: /erp/payment_export_semi.php Purpose: Bank payment export (XLSX default; TSV on &fmt=csv) — Semi-Monthly Compatible with Warper UI (accepts month_key+period OR to/payment_date) Tries: strict query -> relaxed query -> server-side build (from beam_entry) Bank/meta lookup: employee_bank_data (primary) + company_employee_master (fallback) Also: fills missing account/ifsc in matched rows from employee_bank_data Notes: - debug=1 prints derived SQL & sample rows (no download) - loose=1 relaxes strict filters (for debugging) ============================================================================ */ error_reporting(E_ALL); ini_set('display_errors', 1); const AMOUNT_DECIMALS = 0; const PAYMENT_TYPE_DEFAULT = 'N'; // derive 'I' for ICIC0 IFSC const REMARKS_DEFAULT = ''; const NARR_PREFIX_DEFAULT = ''; require_once __DIR__ . '/modules/auth/auth.php'; require_login(); $u = auth_user(); $company_id = (int)$u['company_id']; if (!isset($pdo) || !($pdo instanceof PDO)) { require_once __DIR__ . '/core/db.php'; } /* ---------------- Helpers ---------------- */ function table_exists(PDO $pdo, string $t): bool { try{ $q=$pdo->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=?"); $q->execute([$t]); return (bool)$q->fetchColumn(); } catch(Throwable $e){ return false; } } function col_exists(PDO $pdo, string $t, string $c): bool { try{ $q=$pdo->prepare("SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=? AND column_name=?"); $q->execute([$t,$c]); return (bool)$q->fetchColumn(); } catch(Throwable $e){ return false; } } function table_cols(PDO $pdo, string $t): array { try { $rs=$pdo->query("SHOW COLUMNS FROM `$t`")->fetchAll(PDO::FETCH_ASSOC); $o=[]; foreach($rs as $r){ $o[$r['Field']]=true; } return $o; }catch(Throwable $e){ return []; } } 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 ''; foreach (['debit_ac_no','debit_account_no','debit_account','bank_account_no','account_no','ac_no'] as $c){ if (col_exists($pdo,'company_bank_accounts',$c)) { $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()); } } return ''; } /* ---- Minimal XLSX builder (all cells TEXT) ---- */ 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/officeDocument/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; } /* ---------------- Inputs (compat + debug) ---------------- - Accepts month_key+period OR to/payment_date - debug=1 prints derived SQL & sample rows (no download) - loose=1 relaxes strict filters for debugging */ $fmt = strtolower($_GET['fmt'] ?? 'xlsx'); $dept_id = isset($_GET['dept_id']) ? (int)$_GET['dept_id'] : 0; $debug = isset($_GET['debug']) && $_GET['debug'] ? true : false; $loose = isset($_GET['loose']) && $_GET['loose'] ? true : false; // Prefer explicit month_key/period if (!empty($_GET['month_key'])) { $month_key = date('Y-m-01', strtotime($_GET['month_key'])); $period = strtoupper(substr($_GET['period'] ?? 'H2',0,2)); if (!in_array($period, ['H1','H2'])) $period = 'H2'; } else { // fallback: use to / payment_date / today $to_param = $_GET['to'] ?? $_GET['payment_date'] ?? date('Y-m-d'); $to_ts = strtotime($to_param); if ($to_ts === false) $to_ts = time(); $month_key = date('Y-m-01', $to_ts); $day = (int) date('j', $to_ts); $period = ($day <= 15) ? 'H1' : 'H2'; } $month_key = date('Y-m-01', strtotime($month_key)); /* ---------------- Headers (loom style) ---------------- */ $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', ]; /* ---------------- Fetch (BANK-only) + compatible build (uses employee_bank_data) ---------------- */ $params = [':cid'=>$company_id, ':m'=>$month_key, ':p'=>$period]; // 1) Try the strict SQL (existing behaviour) $sql_strict = " SELECT id, employee_id, employee_name, net_pay, COALESCE(beneficiary_name, employee_name, '') AS beneficiary_name, COALESCE(account_number, '') AS account_number, COALESCE(ifsc, '') AS ifsc, COALESCE(remarks, employee_name, '') AS remarks, COALESCE(narration, '') AS narration, COALESCE(payment_type,'BANK') AS payment_type, COALESCE(department_id,0) AS department_id FROM semi_monthly_salary_report WHERE company_id = :cid AND month_key = :m AND period_label = :p AND COALESCE(payment_type,'BANK') = 'BANK' AND net_pay > 0 AND COALESCE(account_number,'') <> '' AND COALESCE(ifsc,'') <> '' "; if ($dept_id > 0) { $sql_strict .= " AND department_id = :d"; $params[':d'] = $dept_id; } $sql_strict .= " ORDER BY COALESCE(department_id,0), employee_name, id"; $st = $pdo->prepare($sql_strict); $st->execute($params); $rows = $st->fetchAll(PDO::FETCH_ASSOC); // 2) If no rows, try a relaxed fetch (allow any payment_type, but require net_pay>0) if (empty($rows)) { $sql_relaxed = " SELECT id, employee_id, employee_name, net_pay, COALESCE(beneficiary_name, employee_name, '') AS beneficiary_name, COALESCE(account_number, '') AS account_number, COALESCE(ifsc, '') AS ifsc, COALESCE(remarks, employee_name, '') AS remarks, COALESCE(narration, '') AS narration, COALESCE(payment_type,'') AS payment_type, COALESCE(department_id,0) AS department_id FROM semi_monthly_salary_report WHERE company_id = :cid AND month_key = :m AND period_label = :p AND COALESCE(net_pay,0) > 0 "; if ($dept_id > 0) { $sql_relaxed .= " AND department_id = :d"; } $sql_relaxed .= " ORDER BY COALESCE(department_id,0), employee_name, id"; $st2 = $pdo->prepare($sql_relaxed); $st2->execute($params); $rows = $st2->fetchAll(PDO::FETCH_ASSOC); } // 3) If still no rows, attempt server-side build (derive from beam_entry production) if (empty($rows)) { $period_start = ($period === 'H1') ? date('Y-m-01', strtotime($month_key)) : date('Y-m-16', strtotime($month_key)); $period_end = ($period === 'H1') ? date('Y-m-15', strtotime($month_key)) : date('Y-m-t', strtotime($month_key)); $BEAM_TBL = table_exists($pdo,'beam_entry') ? 'beam_entry' : (table_exists($pdo,'beam_stock')?'beam_stock':null); if ($BEAM_TBL) { $id_col = col_exists($pdo,$BEAM_TBL,'employee_id') ? 'employee_id' : (col_exists($pdo,$BEAM_TBL,'warper_id') ? 'warper_id' : null); if ($id_col) { if ($id_col === 'employee_id') { $sql_build = " SELECT be.employee_id AS employee_id, SUM(COALESCE(be.taka,0) * COALESCE(bq.warping_rate,0)) AS net_pay FROM `$BEAM_TBL` be LEFT JOIN beam_qualities bq ON bq.id = be.quality_id AND bq.company_id = be.company_id WHERE be.company_id = ? AND be.entry_date BETWEEN ? AND ? GROUP BY be.employee_id"; try { $stb = $pdo->prepare($sql_build); $stb->execute([$company_id, $period_start, $period_end]); $built = $stb->fetchAll(PDO::FETCH_ASSOC); $rows = []; foreach ($built as $b) { $np = (float)($b['net_pay'] ?? 0); if ($np <= 0) continue; $eid = (int)($b['employee_id'] ?? 0); $ename = ''; try { $q = $pdo->prepare("SELECT name FROM company_employee_master WHERE company_id=? AND id=? LIMIT 1"); $q->execute([$company_id, $eid]); $ename = (string)$q->fetchColumn(); } catch(Throwable $e) { $ename = ''; } if ($ename==='') { try { $q = $pdo->prepare("SELECT karigar_name FROM loom_karigar_master WHERE company_id=? AND id=? LIMIT 1"); $q->execute([$company_id, $eid]); $ename = (string)$q->fetchColumn(); } catch(Throwable $e) { /* ignore */ } } $rows[] = [ 'id' => 0, 'employee_id' => $eid, 'employee_name' => $ename, 'net_pay' => $np, 'beneficiary_name' => $ename, 'account_number' => '', 'ifsc' => '', 'remarks' => $ename, 'narration' => '', 'payment_type' => 'BANK', 'department_id' => $dept_id ]; } } catch(Throwable $e) { // ignore build errors } } } } } /* ------------------ Fill missing account/ifsc from employee_bank_data ------------------ This patch ensures: for rows already matched from semi_monthly_salary_report (or built), if account_number or ifsc is blank, we try to fetch latest from employee_bank_data and merge into $rows so exporter can produce bank file without requiring another table. */ if (!empty($rows) && table_exists($pdo,'employee_bank_data')) { $need = []; foreach ($rows as $i=>$r) { $eid = (int)($r['employee_id'] ?? 0); $acct = trim((string)($r['account_number'] ?? $r['beneficiary_ac_number'] ?? '')); $ifsc = trim((string)($r['ifsc'] ?? '')); if ($eid>0 && ($acct === '' || $ifsc === '')) $need[$eid] = $eid; } if (!empty($need)) { $ids = array_values($need); $in = implode(',', array_fill(0,count($ids),'?')); $params_b = array_merge([$company_id], $ids); try { $sql = "SELECT employee_id, beneficiary_ac_number AS account_no, ifsc_code AS ifsc, beneficiary_name, entry_name, mobile, email FROM employee_bank_data WHERE company_id=? AND employee_id IN ($in) ORDER BY employee_id, updated_at DESC, id DESC"; $stb = $pdo->prepare($sql); $stb->execute($params_b); $bankmap = []; foreach ($stb as $r) { $eid = (int)$r['employee_id']; if (!isset($bankmap[$eid])) { $bankmap[$eid] = [ 'account_no' => trim((string)$r['account_no'] ?? ''), 'ifsc' => strtoupper(trim((string)$r['ifsc'] ?? '')), 'benef_name' => trim((string)$r['beneficiary_name'] ?? ''), 'entry_name' => trim((string)$r['entry_name'] ?? ''), 'mobile' => trim((string)$r['mobile'] ?? ''), 'email' => trim((string)$r['email'] ?? ''), ]; } } // merge into $rows (only fill blanks) foreach ($rows as $k=>$r) { $eid = (int)($r['employee_id'] ?? 0); if ($eid && isset($bankmap[$eid])) { if (empty(trim((string)($rows[$k]['account_number'] ?? '')))) { $rows[$k]['account_number'] = $bankmap[$eid]['account_no']; } if (empty(trim((string)($rows[$k]['ifsc'] ?? '')))) { $rows[$k]['ifsc'] = $bankmap[$eid]['ifsc']; } if (empty(trim((string)($rows[$k]['beneficiary_name'] ?? '')))) { $rows[$k]['beneficiary_name'] = $bankmap[$eid]['benef_name']; } if (empty(trim((string)($rows[$k]['remarks'] ?? '')))) { $rows[$k]['remarks'] = $bankmap[$eid]['entry_name']; } // also useful to attach mobile/email into rows for later use if desired $rows[$k]['_bank_mobile'] = $bankmap[$eid]['mobile']; $rows[$k]['_bank_email'] = $bankmap[$eid]['email']; } } } catch(Throwable $e) { // ignore bank-merge errors } } } /* If debug requested, print diagnostic info & sample rows (no download) */ if ($debug) { header('Content-Type: text/plain; charset=utf-8'); echo "DEBUG MODE\n"; echo "Derived month_key: $month_key\n"; echo "Derived period: $period\n"; echo "dept_id: $dept_id\n"; echo "loose mode: ".($loose?'YES':'NO')."\n\n"; echo "Strict SQL:\n".$sql_strict."\n\n"; echo "Prepared Params:\n"; foreach ($params as $k=>$v) echo "$k => $v\n"; $count = is_array($rows) ? count($rows) : 0; echo "\nRows matched (after strict/relaxed/build/merge): $count\n\n"; if ($count > 0) { echo "Sample rows (up to 50):\n"; $sample = array_slice($rows,0,50); foreach ($sample as $r) echo json_encode($r, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES)."\n"; } else { echo "No rows. Check semi_monthly_salary_report or try &loose=1\n"; } exit; } if (!$rows) { exit('No rows to export.'); } /* ---------------- Date/Narration ---------------- */ $monthEnd = date('Y-m-t', strtotime($month_key)); // month end $h1Date = date('Y-m-', strtotime($month_key)).'15'; // 15th $payDate = date('Y-m-d'); // use server-today as payment date $payDateFmt = date('d-M-Y', strtotime($payDate)); // dd-MMM-yyyy $monLbl = date('M-Y', strtotime($month_key)); $defaultNarr= ($period ?: '') . ' - ' . $monLbl; /* ---------------- Build Lines (employee_bank_data primary + company_employee_master fallback) ---------------- */ $outRows = []; $outRows[] = $headers; $debit_ac = get_company_debit_ac($pdo,$company_id); // Collect emp ids $emp_ids = []; foreach ($rows as $r){ $emp_ids[] = (int)($r['employee_id'] ?? 0); } $emp_ids = array_values(array_filter(array_unique($emp_ids))); $bank = []; $meta = []; if ($emp_ids) { $in = implode(',', array_fill(0,count($emp_ids),'?')); $params2 = array_merge([$company_id], $emp_ids); // Primary: employee_bank_data (your DB screenshot shows this table) try { if (table_exists($pdo,'employee_bank_data')) { $sql = "SELECT employee_id, beneficiary_ac_number AS account_no, beneficiary_name, ifsc_code AS ifsc, entry_name, mobile, email 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($params2); foreach ($st as $r) { $eid = (int)$r['employee_id']; if (!isset($bank[$eid])) { $bank[$eid] = [ 'name' => trim((string)($r['beneficiary_name'] ?? '')), 'ac' => trim((string)($r['account_no'] ?? '')), 'ifsc' => strtoupper(trim((string)($r['ifsc'] ?? ''))), 'entry_name' => trim((string)($r['entry_name'] ?? '')) ]; $meta[$eid] = [ 'mobile' => trim((string)($r['mobile'] ?? '')), 'email' => trim((string)($r['email'] ?? '')) ]; } } } } catch(Throwable $e) { /* ignore but could log */ } // Fallback: company_employee_master for name/mobile/email if not present try { $missing = []; foreach ($emp_ids as $id) { if (!isset($bank[(int)$id]) || $bank[(int)$id]['name']==='') $missing[] = (int)$id; } if (!empty($missing) && table_exists($pdo,'company_employee_master')) { $in2 = implode(',', array_fill(0,count($missing),'?')); $params3 = array_merge([$company_id], $missing); $sql = "SELECT id, name AS emp_name, mobile AS mobile, email AS email FROM company_employee_master WHERE company_id=? AND id IN ($in2)"; $st2 = $pdo->prepare($sql); $st2->execute($params3); foreach ($st2 as $r) { $eid = (int)$r['id']; if (!isset($bank[$eid])) $bank[$eid] = ['name'=>'','ac'=>'','ifsc'=>'','entry_name'=>'']; if ($bank[$eid]['name']==='') $bank[$eid]['name'] = trim((string)($r['emp_name'] ?? '')); if (!isset($meta[$eid])) $meta[$eid] = ['mobile'=>'','email'=>'']; if ($meta[$eid]['mobile']==='') $meta[$eid]['mobile'] = trim((string)($r['mobile'] ?? '')); if ($meta[$eid]['email']==='') $meta[$eid]['email'] = trim((string)($r['email'] ?? '')); } } } catch(Throwable $e) { /* ignore */ } } /* Build output rows in header order REMARKS column (index 18) => employee name CREDIT NARRATION (index 19) => "<period> - <Mon-YYYY>" */ foreach ($rows as $r){ $amt = (int) round((float)($r['net_pay'] ?? 0), 0); if ($amt<=0) continue; $eid = (int)($r['employee_id'] ?? 0); $rec = $bank[$eid] ?? [ 'name'=>trim((string)($r['beneficiary_name'] ?? $r['employee_name'] ?? '')), 'ac'=>trim((string)($r['account_number'] ?? $r['beneficiary_ac_number'] ?? '')), 'ifsc'=>strtoupper((string)($r['ifsc'] ?? '')), 'entry_name'=>trim((string)($r['remarks'] ?? $r['employee_name'] ?? '')) ]; $mobile = $meta[$eid]['mobile'] ?? ($r['_bank_mobile'] ?? ''); $email = $meta[$eid]['email'] ?? ($r['_bank_email'] ?? ''); // Build final row with exact header positions: $final = array_fill(0, count($headers), ''); $final[0] = $debit_ac; $final[1] = (string)($rec['ac'] ?? ''); $final[2] = (string)($rec['name'] ?? ($r['employee_name'] ?? '')); $final[3] = fmt_amt($amt); $final[4] = payment_type_for_ifsc($rec['ifsc'] ?? ($r['ifsc'] ?? '')); $final[5] = $payDateFmt; $final[6] = (string)($rec['ifsc'] ?? ($r['ifsc'] ?? '')); $final[7] = (string)$mobile; $final[8] = (string)$email; // 9..17 - address / add detail placeholders left blank // REMARKS: set to employee name (explicit) $emp_name_for_remarks = trim((string)($r['employee_name'] ?? $rec['name'] ?? '')); $final[18] = $emp_name_for_remarks; // CREDIT NARRATION: period + month label $final[19] = ($period ?: '') . ' - ' . date('M-Y', strtotime($month_key)); $outRows[] = $final; } /* ---------------- Stream (XLSX default; TSV on &fmt=csv) ---------------- */ $bank_user_id = (function(PDO $pdo,int $cid){ try{ $st=$pdo->prepare("SELECT bank_user_id FROM company_bank_accounts WHERE company_id=? ORDER BY id DESC LIMIT 1"); $st->execute([$cid]); $v=trim((string)$st->fetchColumn()); return $v!==''?$v:(string)$cid; } catch(Throwable $e){ return (string)$cid; } })($pdo,$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, $outRows); 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 (Excel-friendly, all text) */ $out=fopen('php://temp','w+'); fwrite($out, "\xEF\xBB\xBF"); foreach ($outRows 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;