« Back to History
single_payment_export.php
|
20260921_175631.php
Initial Domain Snapshot
Copy Code
<?php /* ============================================================================= File: /erp/single_payment_export.php Purpose: Export exactly ONE payment row (Employee or Karigar) as Excel CSV Changes: - Beneficiary Name: bank.account_holder -> fallback to master name - File name: {bank_user_id}_{bank_user_id}UPLD_{ddmmyyyy}_01.csv - Payment Date: d-M-Y (text) - Debit A/c auto from company_bank_accounts (debit_ac_no), fallback companies ============================================================================ */ error_reporting(E_ALL); ini_set('display_errors', 1); require __DIR__ . '/modules/auth/auth.php'; require_login(); $u = auth_user(); $default_cid= (int)$u['company_id']; $pdo = $GLOBALS['pdo'] ?? null; if (!$pdo) { require __DIR__ . '/core/db.php'; } /* ---------- helpers ---------- */ function bad_request($msg, $code=400){ http_response_code($code); header('Content-Type: text/plain; charset=utf-8'); echo $msg; exit; } function table_exists(PDO $pdo, string $t): bool { try{ $st=$pdo->prepare("SHOW TABLES LIKE ?"); $st->execute([$t]); return (bool)$st->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); $out=[]; foreach($rs as $r){ $out[$r['Field']]=true; } return $out; } catch(Throwable $e){ return []; } } function detect_col(array $cols, array $cands, $fallback=null){ foreach($cands as $c){ if(isset($cols[$c])) return $c; } return $fallback; } /* force TEXT in Excel via ="..." */ function as_text($v){ $v=(string)$v; $v=str_replace('"','""',$v); return '="'.$v.'"'; } /* ---------- bank map (multi-key) ---------- */ function find_bank_map(PDO $pdo, string $payeeType): ?array { $empTables = ['employee_bank_data','employee_bank_details','employee_bank_master']; $karTables = ['karigar_bank_data','loom_karigar_bank_data','karigar_bank_details']; $tables = ($payeeType==='Karigar') ? $karTables : $empTables; foreach ($tables as $t) { if (!table_exists($pdo,$t)) continue; $cols = table_cols($pdo,$t); $keyEmp = ['company_employee_id','employee_id','emp_id','user_id']; $keyKar = ['karigar_id','loom_karigar_id','employee_id','user_id']; $payee_keys = array_values(array_filter(($payeeType==='Karigar'?$keyKar:$keyEmp), fn($k)=>isset($cols[$k]))); $map = [ '_table' => $t, 'payee_keys' => $payee_keys, 'company_id' => isset($cols['company_id']) ? 'company_id' : null, 'account_no' => detect_col($cols, ['account_no','account_number','acc_no']), 'ifsc' => detect_col($cols, ['ifsc','ifsc_code']), // broaden candidates for holder name 'account_holder' => detect_col($cols, ['account_holder','holder_name','acct_holder','beneficiary_name','beneficiary','account_name','name']), ]; if($map['payee_keys'] && $map['account_no'] && $map['ifsc']) return $map; } return null; } /* ---------- alt employee IDs ---------- */ function employee_alt_ids(PDO $pdo, int $company_id, int $employee_id): array { if(!table_exists($pdo,'company_employee_master')) return []; $cols = table_cols($pdo,'company_employee_master'); if(!isset($cols['employee_code'])) return []; $st=$pdo->prepare("SELECT employee_code FROM company_employee_master WHERE company_id=? AND id=? LIMIT 1"); $st->execute([$company_id,$employee_id]); $code=$st->fetchColumn(); if($code===false || $code===null || $code==='') return []; return ctype_digit((string)$code) ? [(int)$code] : [(string)$code]; } /* ---------- get payee name from master (fallback) ---------- */ function get_employee_name(PDO $pdo, int $company_id, int $employee_id): ?string { if(!table_exists($pdo,'company_employee_master')) return null; $cols=table_cols($pdo,'company_employee_master'); $nameCol = detect_col($cols,['name','full_name'],'name'); $st=$pdo->prepare("SELECT $nameCol FROM company_employee_master WHERE company_id=? AND id=? LIMIT 1"); $st->execute([$company_id,$employee_id]); $v=$st->fetchColumn(); return ($v!==false && $v!==null && $v!=='') ? (string)$v : null; } function get_karigar_name(PDO $pdo, int $company_id, int $karigar_id): ?string { foreach(['loom_karigar_master','karigar_master','karigar'] as $t){ if(!table_exists($pdo,$t)) continue; $cols=table_cols($pdo,$t); $nameCol = detect_col($cols,['name','karigar_name','full_name'],'name'); $cidCol = detect_col($cols,['company_id']); if($cidCol){ $st=$pdo->prepare("SELECT $nameCol FROM `$t` WHERE $cidCol=? AND id=? LIMIT 1"); $st->execute([$company_id,$karigar_id]); }else{ $st=$pdo->prepare("SELECT $nameCol FROM `$t` WHERE id=? LIMIT 1"); $st->execute([$karigar_id]); } $v=$st->fetchColumn(); if($v!==false && $v!==null && $v!=='') return (string)$v; } return null; } /* ---------- company bank meta (debit_ac + bank_user_id) ---------- */ function get_company_bank_meta(PDO $pdo, int $company_id): array { $out=['debit_ac'=>'','bank_user_id'=>'']; if(table_exists($pdo,'company_bank_accounts')){ $cols=table_cols($pdo,'company_bank_accounts'); $debitCol = detect_col($cols,['debit_ac_no','debit_account_no','debit_ac','bank_account_no','account_no']); $userCol = detect_col($cols,['bank_user_id','user_id','login_id']); $cidCol = detect_col($cols,['company_id'],'company_id'); if($debitCol){ $sql="SELECT ".($userCol?"$userCol AS bu,":"NULL AS bu,")."$debitCol AS da FROM company_bank_accounts WHERE $cidCol=? ORDER BY id DESC LIMIT 1"; $st=$pdo->prepare($sql); $st->execute([$company_id]); $r=$st->fetch(PDO::FETCH_ASSOC); if($r){ $out['debit_ac']=(string)($r['da']??''); $out['bank_user_id']= (string)($r['bu']??''); } } } // fallback from companies if needed if($out['debit_ac']==='' && table_exists($pdo,'companies')){ $cols=table_cols($pdo,'companies'); foreach(['debit_ac','debit_account','bank_account','bank_account_no','account_no'] as $c){ if(isset($cols[$c])){ $st=$pdo->prepare("SELECT $c FROM companies WHERE id=? LIMIT 1"); $st->execute([$company_id]); $v=$st->fetchColumn(); if($v!==false && $v!==null && $v!==''){ $out['debit_ac']=(string)$v; break; } } } } return $out; } /* ---------- inputs ---------- */ $company_id = isset($_GET['company_id']) ? (int)$_GET['company_id'] : $default_cid; $payee_type = (isset($_GET['payee_type']) && $_GET['payee_type']==='Karigar') ? 'Karigar' : 'Employee'; $employee_id = isset($_GET['employee_id']) ? (int)$_GET['employee_id'] : 0; $karigar_id = isset($_GET['karigar_id']) ? (int)$_GET['karigar_id'] : 0; $amount = isset($_GET['amount']) ? (float)$_GET['amount'] : 0; $payment_for = isset($_GET['payment_for']) ? trim((string)$_GET['payment_for']) : 'Salary'; $remark = isset($_GET['remark']) ? trim((string)$_GET['remark']) : ''; $periodLabel = isset($_GET['period_label']) ? trim((string)$_GET['period_label']) : ''; if($company_id<=0) bad_request("company_id missing/invalid"); if($amount<=0) bad_request("amount missing/invalid"); if($payee_type==='Employee' && $employee_id<=0) bad_request("employee_id missing"); if($payee_type==='Karigar' && $karigar_id<=0) bad_request("karigar_id missing"); /* ---------- bank snapshot ---------- */ $bankMap = find_bank_map($pdo, $payee_type); if(!$bankMap) bad_request("Bank table not found for $payee_type."); $bt = $bankMap['_table']; $cols = $bankMap; $idCands = [$payee_type==='Employee'?$employee_id:$karigar_id]; if($payee_type==='Employee'){ foreach(employee_alt_ids($pdo,$company_id,$employee_id) as $alt){ if(!in_array($alt,$idCands,true)) $idCands[]=$alt; } } $snap=null; foreach(($cols['payee_keys'] ?? []) as $keyCol){ foreach($idCands as $pid){ $sql = "SELECT ". ($cols['account_holder'] ? "{$cols['account_holder']} AS account_holder," : "NULL AS account_holder,"). "{$cols['account_no']} AS account_no, {$cols['ifsc']} AS ifsc FROM `$bt` WHERE `$keyCol`=:pid"; $params=[':pid'=>$pid]; if($cols['company_id']){ $sql.=" AND {$cols['company_id']}=:cid"; $params[':cid']=$company_id; } $sql.=" ORDER BY id DESC LIMIT 1"; $st=$pdo->prepare($sql); $st->execute($params); $snap=$st->fetch(PDO::FETCH_ASSOC); if($snap && !empty($snap['account_no']) && !empty($snap['ifsc'])) break 2; } } if(!$snap || empty($snap['account_no']) || empty($snap['ifsc'])){ bad_request("Bank details missing (need Account No + IFSC) for $payee_type."); } /* ---------- beneficiary name (holder -> master fallback) ---------- */ $benefName = (string)($snap['account_holder'] ?? ''); if($benefName===''){ $benefName = ($payee_type==='Employee') ? (get_employee_name($pdo,$company_id,$employee_id) ?? '') : (get_karigar_name($pdo,$company_id,$karigar_id) ?? ''); } /* ---------- company bank meta (debit_ac + bank_user_id) ---------- */ $meta = get_company_bank_meta($pdo,$company_id); $debit_ac = $meta['debit_ac'] ?? ''; $bank_user_id = $meta['bank_user_id'] ?: (string)$company_id; /* ---------- prepare fields ---------- */ $today = date('d-M-Y'); // 04-Sep-2025 $creditNarr = ''; if($periodLabel && preg_match('/(\d{4})-(\d{2})/', $periodLabel, $m)){ $y=(int)$m[1]; $mm=(int)$m[2]; $ts=strtotime(sprintf('%04d-%02d-01',$y,$mm)); $creditNarr = date('M-Y',$ts); } else { $creditNarr = date('M-Y'); } $amountStr = number_format($amount, 2, '.', ''); /* ---------- headers (exact) ---------- */ $headers = [ 'Debit A/c','Beneficiary A/c Number','Beneficiary Name','Amount','Payment', 'Payment Date','IFSC Code','Beneficiary Address 1','Beneficiary Address 2', 'Bene Add3','Bene Add4','Bene Add5','Bene Add6','Add detail1','Add detail2', 'Add detail3','Add detail4','Add detail5','Remarks','Credit Narration' ]; /* ---------- row (ALL TEXT) ---------- */ $row = [ as_text($debit_ac), as_text($snap['account_no']), as_text($benefName), as_text($amountStr), as_text('N'), as_text($today), as_text(strtoupper($snap['ifsc'])), as_text(''), as_text(''), as_text(''), as_text(''), as_text(''), as_text(''), as_text(''), as_text(''), as_text(''), as_text(''), as_text(''), as_text($remark !== '' ? $remark : $payment_for), as_text($creditNarr), ]; /* ---------- output CSV with required filename ---------- */ $ddmmyyyy = date('dmY'); $basename = "{$bank_user_id}_{$bank_user_id}UPLD_{$ddmmyyyy}_01"; $fname = $basename . ".csv"; header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename="'.$fname.'"'); echo "\xEF\xBB\xBF"; // UTF-8 BOM $fp = fopen('php://output', 'w'); fputcsv($fp, $headers); fputcsv($fp, $row); fclose($fp); exit;