« Back to History
employee_payment_export.php
|
20260723_000646.php
Initial Domain Snapshot
Copy Code
<?php // File: /erp/employee_payment_export.php // Purpose: Single employee payment exporter + save-to-db (payment_history + advance if needed) // Usage (example): // /erp/employee_payment_export.php?single=1&fmt=csv&company_id=3&employee_id=1&amount=1000&payment_for=Salary // Optional bank details: account_no, ifsc, account_holder // Optional advance inputs: deduct_parts, adv_month (YYYY-MM), adv_cut (H1/H2/M), remark header('X-Frame-Options: SAMEORIGIN'); error_reporting(E_ALL); ini_set('display_errors',1); /* ---------- Auth bootstrap ---------- */ $auth_paths = [ __DIR__ . '/modules/auth/auth.php', __DIR__ . '/modules/auth/page_acl.php', __DIR__ . '/../modules/auth/auth.php', __DIR__ . '/../modules/auth/page_acl.php', ]; $inc=false; foreach($auth_paths as $p){ if(file_exists($p)){ require_once $p; $inc=true; break; } } if(!$inc){ http_response_code(500); echo "Auth include missing"; exit; } if(function_exists('require_login')) require_login(); $u = function_exists('auth_user') ? auth_user() : null; /* ---------- DB bootstrap ---------- */ $pdo = $GLOBALS['pdo'] ?? null; if(!$pdo){ $db_paths = [__DIR__.'/core/db.php', __DIR__.'/../core/db.php']; foreach($db_paths as $d){ if(file_exists($d)){ require_once $d; $pdo = $GLOBALS['pdo'] ?? $pdo; break; } } } if(!$pdo){ http_response_code(500); echo "DB connection missing"; exit; } /* ---------- helpers ---------- */ function jh($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); } function fmt_amt($n){ return number_format((float)$n,2,'.',''); } function table_exists(PDO $pdo, string $t): bool { try{ $st=$pdo->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=?"); $st->execute([$t]); return (bool)$st->fetchColumn(); } catch(Throwable $e){ return false; } } function col_exists(PDO $pdo, string $t, string $c): bool { try{ $st=$pdo->prepare("SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=? AND column_name=?"); $st->execute([$t,$c]); 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); $o=[]; foreach($rs as $r) $o[$r['Field']]=true; return $o; } catch(Throwable $e){ return []; } } function get_company_debit_ac(PDO $pdo, int $company_id): string { if (!table_exists($pdo,'company_bank_accounts')) return ''; $cols = table_cols($pdo,'company_bank_accounts'); foreach (['debit_ac_no','debit_account_no','debit_account','bank_account_no','account_no','ac_no'] as $c){ if (isset($cols[$c])){ try{ $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()); }catch(Throwable $e){ return ''; } } } return ''; } /* ---------- Minimal XLSX builder (for xlsx option) ---------- */ 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"?><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"?><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"?><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"?><cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/"><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"?><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"?><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"?><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/></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"/></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; } /* ---------- Read input ---------- */ $in = array_merge($_GET, $_POST); $single = isset($in['single']) && (string)$in['single']==='1'; $fmt = strtolower(trim((string)($in['fmt'] ?? 'csv'))); $company_id = isset($in['company_id']) ? (int)$in['company_id'] : (int)($u['company_id'] ?? 0); $employee_id = isset($in['employee_id']) ? (int)$in['employee_id'] : 0; $amount = isset($in['amount']) ? (float)$in['amount'] : 0.0; $payment_for = $in['payment_for'] ?? 'Salary'; $period_label = $in['period_label'] ?? null; $remark = $in['remark'] ?? null; $deduct_parts = isset($in['deduct_parts']) ? max(1,(int)$in['deduct_parts']) : 1; $adv_month = $in['adv_month'] ?? null; $adv_cut = $in['adv_cut'] ?? null; /* ---------- validate ---------- */ if(!$single){ http_response_code(400); echo "Use single=1 for single export."; exit; } if($company_id<=0 || $employee_id<=0 || $amount<=0){ http_response_code(400); echo "company_id, employee_id and amount are required."; exit; } /* ---------- bank snapshot if not provided ---------- */ $account_holder = trim((string)($in['account_holder'] ?? '')); $account_no = trim((string)($in['account_no'] ?? $in['acct_no'] ?? '')); $ifsc = trim((string)($in['ifsc'] ?? '')); // try employee_bank_data table if(($account_no==='' || $ifsc==='') && table_exists($pdo,'employee_bank_data')){ try{ $st = $pdo->prepare("SELECT beneficiary_name AS account_holder, beneficiary_ac_number AS account_no, ifsc_code AS ifsc FROM employee_bank_data WHERE company_id=? AND employee_id=? ORDER BY id DESC LIMIT 1"); $st->execute([$company_id,$employee_id]); $r = $st->fetch(PDO::FETCH_ASSOC); if($r){ if($account_holder==='') $account_holder = $r['account_holder'] ?? $account_holder; if($account_no==='') $account_no = $r['account_no'] ?? $account_no; if($ifsc==='') $ifsc = $r['ifsc'] ?? $ifsc; } }catch(Throwable $e){ /* ignore */ } } // fallback to latest payment_history if(($account_no==='' || $ifsc==='') && table_exists($pdo,'payment_history')){ try{ $st = $pdo->prepare("SELECT account_holder, account_no, ifsc FROM payment_history WHERE company_id=? AND employee_id=? AND payee_type='Employee' AND account_no<>'' AND ifsc<>'' ORDER BY created_at DESC LIMIT 1"); $st->execute([$company_id,$employee_id]); $r = $st->fetch(PDO::FETCH_ASSOC); if($r){ if($account_holder==='') $account_holder = $r['account_holder'] ?? $account_holder; if($account_no==='') $account_no = $r['account_no'] ?? $account_no; if($ifsc==='') $ifsc = $r['ifsc'] ?? $ifsc; } }catch(Throwable $e){ /* ignore */ } } /* ---------- get payee name from master ---------- */ $payee_name = ''; try{ $st = $pdo->prepare("SELECT COALESCE(name,'') AS nm, COALESCE(employee_code,'') AS code FROM company_employee_master WHERE company_id=? AND id=? LIMIT 1"); $st->execute([$company_id,$employee_id]); $r = $st->fetch(PDO::FETCH_ASSOC); if($r) $payee_name = trim(($r['nm'] ?? '').($r['code']?(' ['.$r['code'].']'):'')); }catch(Throwable $e){} /* ---------- Insert into payment_history ---------- */ try{ $phCols = table_cols($pdo,'payment_history'); $colsList = ['company_id','payee_type','employee_id','karigar_id','department_id','department_name','role','payee_name','payment_for','period_label','account_holder','account_no','ifsc','amount','payment_mode','status','remark','bank_table','created_by']; $placeList = [':cid',':payee_type',':eid',':kid',':deptid',':deptname',':role',':payee_name',':payment_for',':period_label',':acct_holder',':acct_no',':ifsc',':amount',"'Bank'","'Paid'",':remark',':bank_table',':created_by']; $paramsIns = [ ':cid'=>$company_id, ':payee_type'=>'Employee', ':eid'=>$employee_id, ':kid'=>0, ':deptid'=>null, ':deptname'=>null, ':role'=>null, ':payee_name'=>$payee_name, ':payment_for'=>$payment_for, ':period_label'=>$period_label, ':acct_holder'=>($account_holder?:null), ':acct_no'=>($account_no?:null), ':ifsc'=>($ifsc?:null), ':amount'=>$amount, ':remark'=>($remark?:null), ':bank_table'=> (table_exists($pdo,'employee_bank_data') ? 'employee_bank_data' : null), ':created_by'=> (int)($u['id'] ?? 0), ]; if(isset($phCols['upi_id'])){ $colsList[]='upi_id'; $placeList[]=':upi_id'; $paramsIns[':upi_id']=null; } if(isset($phCols['branch'])){ $colsList[]='branch'; $placeList[]=':branch'; $paramsIns[':branch']=null; } $sqlIns = "INSERT INTO payment_history (".implode(',',$colsList).") VALUES (".implode(',',$placeList).")"; $pdo->prepare($sqlIns)->execute($paramsIns); $payment_history_id = (int)$pdo->lastInsertId(); } catch(Throwable $e){ // Do not abort export on DB insert error — but report error_log("payment_history insert error: ".$e->getMessage()); $payment_history_id = 0; } /* ---------- If Advance -> insert into employee_advance (and fallback advance_data if exists) ---------- */ if ($payment_for === 'Advance') { try{ // employee_advance table exist ensure $pdo->exec(" CREATE TABLE IF NOT EXISTS employee_advance ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, date DATE NOT NULL, remark VARCHAR(255) NULL, deduct_parts INT NOT NULL DEFAULT 1, remaining_amount DECIMAL(12,2) NOT NULL DEFAULT 0, status ENUM('OPEN','CLOSED') NOT NULL DEFAULT 'OPEN', created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT NULL, INDEX(company_id), INDEX(employee_id), INDEX(status), INDEX(date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $adv_date = ($adv_month ? ($adv_month.'-01') : date('Y-m-01')); if($adv_cut==='H2') $adv_date = date('Y-m-16', strtotime($adv_date)); $remaining = $amount; $stmt = $pdo->prepare("INSERT INTO employee_advance (company_id, employee_id, amount, date, remark, deduct_parts, remaining_amount, status, created_by) VALUES (?,?,?,?,?,?,?,?,?)"); $stmt->execute([$company_id,$employee_id,$amount,$adv_date,$remark,$deduct_parts,$remaining,'OPEN', (int)($u['id'] ?? 0)]); $employee_advance_id = (int)$pdo->lastInsertId(); } catch(Throwable $e){ error_log("employee_advance insert error: ".$e->getMessage()); } // Also insert into advance_data if such table exists in schema (older code used advance_data) if (table_exists($pdo,'advance_data')) { try{ $adStmt = $pdo->prepare("INSERT INTO advance_data (company_id,payee_type,employee_id,karigar_id,khata_id,amount,deduct_parts,remaining_amount,status,date,period_label,remark,created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"); $pl_human = $period_label ?: ($adv_month ? ($adv_month.' '.($adv_cut==='H1'?'1-15':($adv_cut==='H2'?'16-end':'Month'))) : null); $adStmt->execute([$company_id,'Employee',$employee_id,0,null,$amount,$deduct_parts,$remaining,'OPEN',$adv_date,$pl_human,$remark,(int)($u['id'] ?? 0)]); }catch(Throwable $e){ error_log("advance_data insert error: ".$e->getMessage()); } } } /* ---------- Build single export row (bank format) ---------- */ $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' ]; $debit_ac = get_company_debit_ac($pdo, $company_id); $pay_date = date('d-M-Y'); $narration = $period_label ?: date('M-Y'); $ptype = 'N'; if($ifsc = strtoupper(trim($ifsc ?? ''))){ if(strpos($ifsc,'ICIC')===0 || strpos($ifsc,'ICIC0')===0) $ptype='I'; } $line = []; $line[] = $debit_ac; $line[] = $account_no; $line[] = ($account_holder?:$payee_name); $line[] = fmt_amt($amount); $line[] = $ptype; $line[] = $pay_date; $line[] = $ifsc; $line[] = ''; // mobile $line[] = ''; // email for($i=0;$i<4;$i++) $line[] = ''; for($i=0;$i<5;$i++) $line[] = ''; $line[] = ($remark ?: ''); $line[] = $narration; /* ---------- Output (xlsx or csv) ---------- */ $ts = date('Ymd_His'); $basename = "employee_payment_{$ts}"; if($fmt==='xlsx'){ $rows_for = []; $rows_for[] = $headers; $rows_for[] = $line; $tmpfile = xlsx_zip_build($basename, $rows_for); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment; filename="'.$basename.'.xlsx"'); header('Content-Length: '.filesize($tmpfile)); readfile($tmpfile); @unlink($tmpfile); exit; } // default CSV $filename = $basename.'.csv'; header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename="'.$filename.'"'); // BOM for excel echo "\xEF\xBB\xBF"; $out = fopen('php://output','w'); fputcsv($out, $headers); fputcsv($out, $line); fclose($out); exit;