View Code - 20260722

payment_export_monthly.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ============================================================================ 
   File: /erp/payment_export_monthly.php
   Purpose: Bank payment export — produce .xlsx (all cells TEXT) without external libs.
            Fallback to TSV if Zip extension missing.
   Notes: All output cells are forced as TEXT (inlineStr).
   ============================================================================ */
header('X-Frame-Options: SAMEORIGIN');
error_reporting(E_ALL);
ini_set('display_errors', 1);

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_once __DIR__ . '/core/db.php';

/* ----------------- helpers for xlsx (all-as-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'); }

/**
 * Build sheet XML where EVERY cell is an inlineStr (text).
 */
function xlsx_build_sheet_xml_all_text(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;
      $raw = (string)$val;
      // Force ALL cells to inlineStr (text)
      $xml .= '<c r="'.$addr.'" t="inlineStr"><is><t>'.xlsx_xml_escape($raw).'</t></is></c>';
    }
    $xml .= '</row>';
  }
  $xml .= '</sheetData></worksheet>';
  return $xml;
}

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_all_text($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: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;
}

/* ----------------- small helpers ----------------- */
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;
}

/* ----------------- Inputs / normalize month ----------------- */
$raw = trim((string)($_REQUEST['month_key'] ?? ''));
$y = isset($_REQUEST['y']) ? (int)$_REQUEST['y'] : 0;
$m = isset($_REQUEST['m']) ? (int)$_REQUEST['m'] : 0;
$debug = (int)($_GET['debug'] ?? 0);

if ($raw !== '') {
  if (preg_match('/^\d{4}-\d{2}$/',$raw)) $month_key = $raw.'-01';
  else { $t = strtotime($raw); $month_key = $t?date('Y-m-01',$t):date('Y-m-01'); }
} elseif ($y>0 && $m>0) {
  $month_key = sprintf('%04d-%02d-01',$y,$m);
} else {
  $month_key = date('Y-m-01');
}

/* ----------------- fetch employees / monthly_salary_report rows ----------------- */
$sql = "
  SELECT id, employee_id, employee_name, department_id, department_name, net_pay,
         COALESCE(beneficiary_name, employee_name, '') AS beneficiary_name,
         COALESCE(account_number, '') AS account_number,
         COALESCE(ifsc, '') AS ifsc,
         COALESCE(narration, '') AS narration,
         DATE_FORMAT(month_key,'%b-%Y') AS period_label
  FROM monthly_salary_report
  WHERE company_id = :cid
    AND DATE_FORMAT(month_key,'%Y-%m') = DATE_FORMAT(:m,'%Y-%m')
  ORDER BY department_name, employee_name
";
$stmt = $pdo->prepare($sql);
$stmt->execute([':cid'=>$company_id, ':m'=>$month_key]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if ($debug) {
  header('Content-Type: text/html; charset=utf-8');
  echo "<h3>Debug payment_export_monthly</h3>";
  echo "<p>company_id: ".htmlspecialchars($company_id)."</p>";
  echo "<p>month_key: ".htmlspecialchars($month_key)."</p>";
  echo "<p>matched rows: ".count($rows)."</p>";
  echo "<pre>SQL: ".htmlspecialchars($sql)."</pre>";
  if ($rows) {
    echo "<table border=1 cellpadding=4><tr><th>id</th><th>emp</th><th>net</th><th>acc</th><th>ifsc</th></tr>";
    foreach($rows as $r) echo "<tr><td>{$r['id']}</td><td>".htmlspecialchars($r['employee_name'])."</td><td align=right>{$r['net_pay']}</td><td>".htmlspecialchars($r['account_number'])."</td><td>".htmlspecialchars($r['ifsc'])."</td></tr>";
    echo "</table>";
  }
  exit;
}

if (!$rows) { header('Content-Type:text/html; charset=utf-8'); echo "<p>No salary rows found.</p>"; exit; }

/* ----------------- Fetch company bank account info (filename + debit account) ----------------- */
/* NOTE: fix applied here — fetch debit_ac_no and bank_user_id from company_bank_accounts */
$st = $pdo->prepare("SELECT COALESCE(bank_user_id,'') AS bank_user_id, COALESCE(debit_ac_no,'') AS debit_ac_no 
                     FROM company_bank_accounts 
                     WHERE company_id = ? 
                     ORDER BY id DESC LIMIT 1");
$st->execute([$company_id]);
$bank = $st->fetch(PDO::FETCH_ASSOC) ?: ['bank_user_id' => (string)$company_id, 'debit_ac_no' => ''];

// bank_user_id used for filename (fallback to company_id if empty)
$bank_user_id = trim((string)$bank['bank_user_id']);
if ($bank_user_id === '') $bank_user_id = (string)$company_id;

// debit account number for column A (strip non-digits but keep empty fallback)
$debit_ac_no = trim((string)$bank['debit_ac_no']);
$debit_ac_no = $debit_ac_no !== '' ? preg_replace('/\D/','', $debit_ac_no) : '';

// file base
$file_date = date('dmY');
$file_base = sprintf("%s_%sUPLD_%s_01", $bank_user_id, $bank_user_id, $file_date);

/* ----------------- Build sheet rows (A..T) ----------------- */
$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','Beneficiary','Bene Add','Bene Add','Bene Add','Add detail 1','Add detail 2',
  'Add detail 3','Add detail 4','Add detail 5','Add detail 6','Remarks','Credit Narration'
];
$sheetRows = [];
$sheetRows[] = $headers;

// payment date formatted for column F
$payment_date = date('d-M-Y');

// Populate rows (ALL values forced to string/text)
foreach ($rows as $r) {
  $benef = trim((string)$r['beneficiary_name']);
  $acc = preg_replace('/\D/','', trim((string)$r['account_number']));
  $ifsc = strtoupper(trim((string)$r['ifsc']));
  $net = (float)$r['net_pay'];
  $payment_flag = (stripos($ifsc,'ICIC')===0) ? 'I' : 'N';
  $employee_name = trim((string)$r['employee_name']);
  $period_label = trim((string)$r['period_label']);

$line = array_fill(0, 20, '');

// A - Debit A/c Number (from company_bank_accounts.debit_ac_no). If debit_ac_no empty, fallback to bank_user_id.
  if ($debit_ac_no !== '') {
    $line[0] = (string)$debit_ac_no;
  } else {
    $line[0] = (string)$bank_user_id;
  }

// B - beneficiary account (from monthly_salary_report.account_number)
  $line[1] = $acc !== '' ? (string)$acc : '';
  $line[2] = (string)$benef;                   // C - beneficiary name
  $line[3] = (string) round($net,0);           // D - amount BUT as text
  $line[4] = (string)$payment_flag;            // E - I/N
  $line[5] = (string)$payment_date;            // F - payment date
  $line[6] = (string)$ifsc;                    // G - IFSC
  $line[18] = (string)$employee_name;          // S - Remarks / employee name
  $line[19] = (string)$period_label;           // T - Credit Narration

$sheetRows[] = $line;
}

/* ----------------- Create XLSX using xlsx_zip_build if ZipArchive available, else TSV fallback ----------------- */
$fullname_xlsx = $file_base . '.xlsx';
$fullname_tsv  = $file_base . '.tsv';

if (class_exists('ZipArchive')) {
  try {
    $tmp = xlsx_zip_build($bank_user_id, $sheetRows);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="'.$fullname_xlsx.'"');
    header('Content-Length: ' . filesize($tmp));
    readfile($tmp);
    @unlink($tmp);
    exit;
  } catch (Throwable $e) {
    // fallback to TSV below
  }
}

// TSV fallback (Excel will open). Ensure all fields as text (they already are strings)
$out = fopen('php://temp','w+');
fwrite($out, "\xEF\xBB\xBF");
foreach ($sheetRows as $r) {
  fputcsv($out, $r, "\t");
}
rewind($out);
header('Content-Type: text/tab-separated-values; charset=UTF-8');
header('Content-Disposition: attachment; filename="'.$fullname_tsv.'"');
fpassthru($out);
fclose($out);
exit;