View Code - 20260722

payment_export_warper.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ============================================================================ 
   File: /erp/payment_export_warper.php
   Purpose: Bank payment export (XLSX default; TSV on &fmt=csv) — Warper (Payable)
            Directly calculates and exports live matching net pay without static table mismatch.
   Module-level: uses payable as Amount sent to bank.
   ============================================================================ */
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 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 ---- */
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 ---------------- */
$fmt = strtolower($_GET['fmt'] ?? 'xlsx');
$debug = isset($_GET['debug']) && $_GET['debug'] ? true : false;

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 {
  $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';
}

if ($period === 'H1') {
  $from = date('Y-m-01', strtotime($month_key));
  $to   = date('Y-m-15', strtotime($month_key));
} else {
  $from = date('Y-m-16', strtotime($month_key));
  $to   = date('Y-m-t', strtotime($month_key));
}

/* ---------------- 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',
];

/* ---------------- Live Calculation Fetch Pipeline ---------------- */
// 1. Fetch Taka × Rate from company_beam_stock
$sql = "
SELECT
  cbs.warper_id AS employee_id,
  cem.name AS employee_name,
  COALESCE(SUM(CASE WHEN cbs.taka IS NULL OR cbs.taka='' THEN 0
                    ELSE CAST(cbs.taka AS DECIMAL(12,2)) END * COALESCE(q.warping_rate,0)),0) AS gross_salary
FROM company_beam_stock cbs
JOIN company_employee_master cem ON cem.id = cbs.warper_id AND cem.company_id = cbs.company_id
LEFT JOIN beam_qualities q ON q.id = cbs.quality_id AND q.company_id = cbs.company_id
WHERE cbs.company_id = :cid
  AND cbs.warp_date BETWEEN :from AND :to
  AND cem.is_active = 1 
  AND cem.department_id = 22
GROUP BY cbs.warper_id, cem.name
";
$st = $pdo->prepare($sql);
$st->execute([':cid'=>$company_id, ':from'=>$from, ':to'=>$to]);
$base_salaries = $st->fetchAll(PDO::FETCH_ASSOC);

// 2. Load side tables mapping streams (Extra, Deduction, Advance)
$extraBy = $dedBy = $advBy = [];

$qExtra = $pdo->prepare("SELECT employee_id, COALESCE(SUM(amount),0) s FROM employee_extra WHERE company_id=? AND entry_date BETWEEN ? AND ? GROUP BY employee_id");
$qExtra->execute([$company_id, $from, $to]);
foreach ($qExtra->fetchAll(PDO::FETCH_ASSOC) as $r){ $extraBy[(int)$r['employee_id']] = (float)$r['s']; }

$qDed = $pdo->prepare("SELECT employee_id, COALESCE(SUM(amount),0) s FROM employee_deduction WHERE company_id=? AND entry_date BETWEEN ? AND ? GROUP BY employee_id");
$qDed->execute([$company_id, $from, $to]);
foreach ($qDed->fetchAll(PDO::FETCH_ASSOC) as $r){ $dedBy[(int)$r['employee_id']] = (float)$r['s']; }

$qAdv = $pdo->prepare("SELECT employee_id, COALESCE(SUM(amount),0) s FROM employee_advance WHERE company_id=? AND date BETWEEN ? AND ? GROUP BY employee_id");
$qAdv->execute([$company_id, $from, $to]);
foreach ($qAdv->fetchAll(PDO::FETCH_ASSOC) as $r){ $advBy[(int)$r['employee_id']] = (float)$r['s']; }

// 3. Fetch Master Bank details context
$bankMap = [];
if (!empty($base_salaries)) {
  $emp_ids = array_column($base_salaries, 'employee_id');
  $in = implode(',', array_fill(0, count($emp_ids), '?'));
  $qBank = $pdo->prepare("SELECT employee_id, beneficiary_ac_number AS account_no, ifsc_code AS ifsc, beneficiary_name, mobile, email FROM employee_bank_data WHERE company_id=? AND employee_id IN ($in) ORDER BY updated_at DESC");
  $qBank->execute(array_merge([$company_id], $emp_ids));
  foreach ($qBank->fetchAll(PDO::FETCH_ASSOC) as $b) {
    $bankMap[(int)$b['employee_id']] = $b;
  }
}

/* ---------------- Process Lines ---------------- */
$outRows   = [];
$outRows[] = $headers;
$debit_ac  = get_company_debit_ac($pdo, $company_id);
$payDateFmt = date('d-M-Y');

foreach ($base_salaries as $r) {
  $eid = (int)$r['employee_id'];
  $gross = (float)$r['gross_salary'];
  $extra = (float)($extraBy[$eid] ?? 0);
  $ded   = (float)($dedBy[$eid] ?? 0);
  $adv   = (float)($advBy[$eid] ?? 0);
  
  $net_pay = round($gross + $extra - $ded - $adv, 0);
  if ($net_pay <= 0) continue;

$bData = $bankMap[$eid] ?? [];

$final = array_fill(0, count($headers), '');
  $final[0]  = $debit_ac;
  $final[1]  = (string)($bData['account_no'] ?? '');
  $final[2]  = (string)($bData['beneficiary_name'] ?? $r['employee_name']);
  $final[3]  = fmt_amt($net_pay);
  $final[4]  = payment_type_for_ifsc($bData['ifsc'] ?? '');
  $final[5]  = $payDateFmt;
  $final[6]  = (string)($bData['ifsc'] ?? '');
  $final[7]  = (string)($bData['mobile'] ?? '');
  $final[8]  = (string)($bData['email'] ?? '');
  $final[18] = trim((string)$r['employee_name']);
  $final[19] = ($period ?: '') . ' - ' . date('M-Y', strtotime($month_key));
  
  $outRows[] = $final;
}

if ($debug) {
  header('Content-Type: text/plain; charset=utf-8');
  echo "DEBUG LIVE RE-CALCULATION INTEGRITY\n";
  print_r($outRows);
  exit;
}

if (count($outRows) <= 1) { exit('No rows calculated to export.'); }

/* ---------------- Stream Output ---------------- */
$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;
}

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