« Back to History
payment_export_semi_strict.php
|
20260723_000646.php
Initial Domain Snapshot
Copy Code
<?php /* ============================================================================ File: /erp/payment_export_semi_strict.php Purpose: Safe Bank payment export (XLSX default; TSV on &fmt=csv) — Semi-Monthly Per-row strict bank matching. Aborts on missing/ambiguous bank info. Notes: Uses robust XLSX builder to avoid corrupt files ============================================================================ */ error_reporting(E_ALL); ini_set('display_errors', 1); const AMOUNT_DECIMALS = 0; const PAYMENT_TYPE_DEFAULT = 'N'; 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; } /* ---- Minimal XLSX helpers (sheet 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>'; } /* ---- Robust XLSX zip builder (complete parts) ---- */ 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'); $dept_id = isset($_GET['dept_id']) ? (int)$_GET['dept_id'] : 0; $debug = isset($_GET['debug']) && $_GET['debug']; /* derive 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 { $to_param = $_GET['to'] ?? date('Y-m-d'); $to_ts = strtotime($to_param)?:time(); $month_key = date('Y-m-01',$to_ts); $period = ((int)date('j',$to_ts) <= 15) ? 'H1' : 'H2'; } /* ------------- Load rows (strict -> relaxed -> build) ------------- */ $params = [':cid'=>$company_id, ':m'=>$month_key, ':p'=>$period]; $sql = " 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(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 .= " AND department_id = :d"; $params[':d']=$dept_id; } $sql .= " ORDER BY COALESCE(department_id,0), employee_name, id"; $st = $pdo->prepare($sql); $st->execute($params); $rows = $st->fetchAll(PDO::FETCH_ASSOC); /* server-side build fallback (if no semi data) */ 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 = table_exists($pdo,'beam_entry') ? 'beam_entry' : (table_exists($pdo,'beam_stock') ? 'beam_stock' : null); if ($BEAM) { $id_col = col_exists($pdo,$BEAM,'employee_id') ? 'employee_id' : (col_exists($pdo,$BEAM,'warper_id') ? 'warper_id' : null); if ($id_col) { if ($id_col === 'employee_id') { $sqlb = "SELECT be.employee_id AS employee_id, SUM(COALESCE(be.taka,0)*COALESCE(bq.warping_rate,0)) AS net_pay FROM `$BEAM` 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"; $stb = $pdo->prepare($sqlb); $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']; $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){} $rows[]=['id'=>0,'employee_id'=>$eid,'employee_name'=>$ename,'net_pay'=>$np,'beneficiary_name'=>$ename,'account_number'=>'','ifsc'=>'','remarks'=>$ename]; } } } } } if (!$rows) { exit('No rows to export.'); } /* ------------- strict per-row bank lookup (same logic as earlier) ------------- */ function find_bank_for_row(PDO $pdo, int $company_id, array $row): array { $eid = (int)($row['employee_id'] ?? 0); $ename = trim((string)($row['employee_name'] ?? $row['beneficiary_name'] ?? '')); // 1) by employee_bank_data.employee_id if ($eid>0 && table_exists($pdo,'employee_bank_data')) { $st=$pdo->prepare("SELECT 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=? ORDER BY updated_at DESC, id DESC LIMIT 1"); $st->execute([$company_id,$eid]); $r=$st->fetch(PDO::FETCH_ASSOC); if ($r && trim((string)$r['account_no'])!=='' && trim((string)$r['ifsc'])!=='') return ['status'=>'ok','bank'=>['ac'=>trim($r['account_no']),'ifsc'=>strtoupper(trim($r['ifsc'])),'name'=>trim($r['beneficiary_name']),'entry_name'=>trim($r['entry_name']),'mobile'=>trim($r['mobile']),'email'=>trim($r['email'])]]; } // 2) karigar_bank_data if exists if ($eid>0 && table_exists($pdo,'karigar_bank_data')) { $st=$pdo->prepare("SELECT beneficiary_ac_number AS account_no, ifsc_code AS ifsc, beneficiary_name, entry_name FROM karigar_bank_data WHERE company_id=? AND karigar_id=? ORDER BY updated_at DESC, id DESC LIMIT 1"); $st->execute([$company_id,$eid]); $r=$st->fetch(PDO::FETCH_ASSOC); if ($r && trim((string)$r['account_no'])!=='' && trim((string)$r['ifsc'])!=='') return ['status'=>'ok','bank'=>['ac'=>trim($r['account_no']),'ifsc'=>strtoupper(trim($r['ifsc'])),'name'=>trim($r['beneficiary_name']),'entry_name'=>trim($r['entry_name'])]]; } // 3) exact name match in employee_bank_data (only if unique) if ($ename!=='' && table_exists($pdo,'employee_bank_data')) { $st=$pdo->prepare("SELECT employee_id, beneficiary_ac_number AS account_no, ifsc_code AS ifsc, beneficiary_name, entry_name FROM employee_bank_data WHERE company_id=? AND (LOWER(TRIM(beneficiary_name)) = LOWER(TRIM(?)) OR LOWER(TRIM(entry_name)) = LOWER(TRIM(?))) ORDER BY updated_at DESC, id DESC"); $st->execute([$company_id,$ename,$ename]); $cands=$st->fetchAll(PDO::FETCH_ASSOC); $valid=[]; foreach($cands as $c){ if(trim((string)$c['account_no'])!=='' && trim((string)$c['ifsc'])!=='') $valid[]=$c; } if (count($valid)===1) return ['status'=>'ok','bank'=>['ac'=>trim($valid[0]['account_no']),'ifsc'=>strtoupper(trim($valid[0]['ifsc'])),'name'=>trim($valid[0]['beneficiary_name']),'entry_name'=>trim($valid[0]['entry_name']),'employee_id'=>$valid[0]['employee_id']]]; if (count($valid)>1) return ['status'=>'ambiguous','candidates'=>$valid]; } // 4) company_employee_master exact name -> then employee_bank_data for that id if ($ename!=='' && table_exists($pdo,'company_employee_master')) { $st=$pdo->prepare("SELECT id,name,mobile,email FROM company_employee_master WHERE company_id=? AND LOWER(TRIM(name)) = LOWER(TRIM(?))"); $st->execute([$company_id,$ename]); $emps=$st->fetchAll(PDO::FETCH_ASSOC); if (count($emps)===1) { $eid2=(int)$emps[0]['id']; if (table_exists($pdo,'employee_bank_data')) { $st2=$pdo->prepare("SELECT 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=? ORDER BY updated_at DESC, id DESC LIMIT 1"); $st2->execute([$company_id,$eid2]); $rb=$st2->fetch(PDO::FETCH_ASSOC); if ($rb && trim((string)$rb['account_no'])!=='' && trim((string)$rb['ifsc'])!=='') return ['status'=>'ok','bank'=>['ac'=>trim($rb['account_no']),'ifsc'=>strtoupper(trim($rb['ifsc'])),'name'=>trim($rb['beneficiary_name']),'entry_name'=>trim($rb['entry_name']),'employee_id'=>$eid2]]; } } if (count($emps)>1) return ['status'=>'ambiguous','candidates_emp'=>$emps]; } return ['status'=>'notfound']; } /* per-row matching */ $problems=[]; $matched=[]; foreach ($rows as $i=>$r) { $res = find_bank_for_row($pdo,$company_id,$r); if ($res['status']==='ok') $matched[$i]=$res['bank']; else $problems[]=['index'=>$i,'row'=>$r,'status'=>$res['status'],'details'=>$res['candidates'] ?? $res['candidates_emp'] ?? null]; } if (!empty($problems)) { header('Content-Type: text/html; charset=utf-8'); echo "<h2>Export aborted — bank mapping problems detected</h2>"; echo "<table border='1' cellpadding='6' style='border-collapse:collapse'><thead><tr><th>#</th><th>Row ID</th><th>Employee ID</th><th>Name</th><th>Net Pay</th><th>Dept</th><th>Problem</th><th>Details</th></tr></thead><tbody>"; foreach ($problems as $p) { $row=$p['row']; $idx=$p['index']; $rid=htmlspecialchars((string)($row['id']??'')); $eid=htmlspecialchars((string)($row['employee_id']??'')); $ename=htmlspecialchars((string)($row['employee_name']??$row['beneficiary_name']??'')); $np=htmlspecialchars((string)($row['net_pay']??'')); $dept=htmlspecialchars((string)($row['department_id']??'')); $prob=htmlspecialchars($p['status']); $det=''; if (!empty($p['details'])) $det='<pre style="white-space:pre-wrap">'.htmlspecialchars(json_encode($p['details'],JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES),ENT_QUOTES,'UTF-8').'</pre>'; echo "<tr><td>".($idx+1)."</td><td>{$rid}</td><td>{$eid}</td><td>{$ename}</td><td>{$np}</td><td>{$dept}</td><td>{$prob}</td><td>{$det}</td></tr>"; } echo "</tbody></table><p>Fix data in employee_bank_data / karigar_bank_data or ensure employee_id spaces match and retry.</p>"; exit; } /* Build output rows */ $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']; $outRows = []; $outRows[]=$headers; $debit_ac = ''; try{ if (table_exists($pdo,'company_bank_accounts')){ $q=$pdo->prepare("SELECT COALESCE(debit_ac_no,debit_account_no,debit_account,bank_account_no,account_no,ac_no,'') FROM company_bank_accounts WHERE company_id=? ORDER BY id DESC LIMIT 1"); $q->execute([$company_id]); $debit_ac = trim((string)$q->fetchColumn()); } }catch(Throwable$e){} $payDateFmt = date('d-M-Y'); $narr = ($period?:'') . ' - ' . date('M-Y', strtotime($month_key)); foreach ($rows as $i=>$r) { $bank = $matched[$i]; $amt = (int) round((float)($r['net_pay'] ?? 0), 0); if ($amt<=0) continue; $line = array_fill(0,count($headers),''); $line[0] = $debit_ac; $line[1] = (string)($bank['ac'] ?? ''); $line[2] = (string)($bank['name'] ?? ($r['employee_name'] ?? $r['beneficiary_name'] ?? '')); $line[3] = fmt_amt($amt); $line[4] = payment_type_for_ifsc($bank['ifsc'] ?? ''); $line[5] = $payDateFmt; $line[6] = (string)($bank['ifsc'] ?? ''); $line[7] = (string)($bank['mobile'] ?? ''); $line[8] = (string)($bank['email'] ?? ''); $line[18] = (string)($r['employee_name'] ?? $line[2]); $line[19] = $narr; $outRows[] = $line; } /* Stream */ $bank_user_id = (function($pdo,$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?: (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 fallback */ $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;