View Code - 20260920

contractor_payment_export.php | 20260920_164915.php Initial Domain Snapshot

<?php
/* ============================================================================
   File: /erp/contractor_payment_export.php
   PURPOSE (LOCKED):
   - XLSX ONLY (no CSV/TSV, no fallback)
   - Column order EXACTLY as bank-accepted reference (A → T)
   - Debit A/c from company_bank_accounts.debit_ac_no (latest)
   - Remarks  : "{employee_name} {period}"
   - Credit Narration : "{contractor_type} - {mon yyyy}"
   - Filename: {bank_user_id}_{bank_user_id}UPLD_{DDMMYYYY}_01.xlsx
   ============================================================================ */

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

/* --------- Auth / Context --------- */
require_once __DIR__ . '/modules/auth/page_acl.php';
$ctx = page_require_access('contractor_payment');
$pdo = $ctx['pdo'];
$cid = (int)$ctx['company_id'];

/* --------- INPUT (from calling form) ---------
   $_POST['rows'][<contractor_employee_id>]['amount']
   Period must be provided by caller as:
     $_POST['period_label']  // e.g. "1-15" or "16-31"
   Month context (for credit narration):
     $_POST['narr_month']    // e.g. "jan"
     $_POST['narr_year']     // e.g. "2026"
------------------------------------------------ */
$rows = $_POST['rows'] ?? [];
if (!$rows) exit('No rows');

$periodLabel = trim((string)($_POST['period_label'] ?? ''));
$narrMonth   = strtolower(trim((string)($_POST['narr_month'] ?? '')));
$narrYear    = trim((string)($_POST['narr_year'] ?? ''));

if ($periodLabel === '' || $narrMonth === '' || $narrYear === '') {
  exit('Missing period / narration context');
}

/* --------- Helpers --------- */
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">'
      .'<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 = mb_substr($sheetName ?: 'Sheet1',0,31);
  $tmp = tempnam(sys_get_temp_dir(),'xlsx_'); @unlink($tmp);
  $sheetXml = xlsx_build_sheet_xml($rows);
  $now = gmdate('Y-m-d\TH:i:s\Z');

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

$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"/>
  <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"/>
</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"/>
  <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>');

$zip->addFromString('docProps/app.xml',
'<?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>');

$zip->addFromString('docProps/core.xml',
'<?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">'.$now.'</dcterms:created>
  <dcterms:modified xsi:type="dcterms:W3CDTF">'.$now.'</dcterms:modified>
</cp:coreProperties>');

$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="'.xlsx_xml_escape($sheetName).'" 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">
  <fonts count="1"><font><sz val="11"/><name val="Calibri"/></font></fonts>
  <fills count="1"><fill><patternFill patternType="none"/></fill></fills>
  <borders count="1"><border><left/><right/><top/><bottom/></border></borders>
  <cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellXfs>
</styleSheet>');

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

/* --------- Debit A/c + Bank User ID --------- */
$st = $pdo->prepare("
  SELECT bank_user_id, debit_ac_no
  FROM company_bank_accounts
  WHERE company_id=?
  ORDER BY id DESC
  LIMIT 1
");
$st->execute([$cid]);
$bank = $st->fetch(PDO::FETCH_ASSOC);
if (!$bank || trim((string)$bank['debit_ac_no'])==='') {
  exit('Debit account not found');
}
$bankUserId = trim((string)$bank['bank_user_id']);
$debitAc   = trim((string)$bank['debit_ac_no']);

/* --------- Headers (A → T EXACT) --------- */
$out = [];
$out[] = [
  'Debit A/c Number',              // A
  'Beneficiary A/c Number',        // B
  'Beneficiary Name',              // C
  'Amount',                        // D
  'Payment Type',                  // E
  'Payment Date',                  // F
  'IFSC Code',                     // G
  'Beneficiary Mobile No',         // H
  'Beneficiary email-id',          // I
  'Bene Add 1',                    // J
  'Bene Add 2',                    // K
  'Bene Add 3',                    // L
  'Bene Add 4',                    // M
  'Add detail 1',                  // N
  'Add detail 2',                  // O
  'Add detail 3',                  // P
  'Add detail 4',                  // Q
  'Add detail 5',                  // R
  'Remarks',                       // S
  'Credit Narration'               // T
];

/* --------- Rows --------- */
$payDate = date('d-M-Y');

foreach ($rows as $empId => $r) {
  $amt = (int)round((float)($r['amount'] ?? 0), 0);
  if ($amt<=0) continue;

$q = $pdo->prepare("
    SELECT employee_name, department, bene_name, account_no, ifsc_code
    FROM contractor_employee
    WHERE id=? AND cid=?
  ");
  $q->execute([(int)$empId, $cid]);
  $e = $q->fetch(PDO::FETCH_ASSOC);
  if (!$e || trim($e['account_no'])==='' || trim($e['ifsc_code'])==='') {
    exit('Missing bank details for contractor_employee id '.$empId);
  }

$employeeName = (string)$e['employee_name'];
  $benefName   = (string)($e['bene_name'] ?: $e['employee_name']);
  $ifsc        = strtoupper(trim((string)$e['ifsc_code']));
  $payType     = (strpos($ifsc,'ICIC0')===0) ? 'I' : 'N';
  $remarks     = $employeeName.' '.$periodLabel;                 // LOCKED
  $creditNar   = strtolower($e['department']).' - '.$narrMonth.' '.$narrYear; // LOCKED

$line = array_fill(0, 20, '');
  $line[0]  = $debitAc;
  $line[1]  = (string)$e['account_no'];
  $line[2]  = $benefName;
  $line[3]  = (string)$amt;
  $line[4]  = $payType;
  $line[5]  = $payDate;
  $line[6]  = $ifsc;
  // H–R empty by design
  $line[18] = $remarks;
  $line[19] = $creditNar;

$out[] = $line;
}

/* --------- Filename (LOCKED) --------- */
$fname = $bankUserId.'_'.$bankUserId.'UPLD_'.date('dmY').'_01.xlsx';

/* --------- STREAM XLSX ONLY --------- */
$xlsx = xlsx_zip_build($fname, $out);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$fname.'"');
header('Content-Length: '.filesize($xlsx));
readfile($xlsx);
@unlink($xlsx);
exit;