« Back to History
salary_by_department.php
|
20260921_175631.php
Initial Domain Snapshot
Copy Code
<?php /* parse_pdfs_to_manual_noshell.php Pure-PHP PDF text extraction (no shell_exec) -> parse department totals -> upsert to department_manual_salary Drop in /erp/ and open in browser. Uses ERP auth bootstrap. */ require_once __DIR__ . '/modules/auth/page_acl.php'; $ctx = page_require_access('department_manual_salary'); $pdo = $ctx['pdo'] ?? null; $company_id = (int)($ctx['company_id'] ?? 0); if (!$pdo) { echo "<pre>DB connection missing (ctx['pdo']). Aborting.</pre>"; exit; } $default_pdf_dir = __DIR__ . '/uploads/pdfs'; $dir = isset($_REQUEST['dir']) ? trim($_REQUEST['dir']) : $default_pdf_dir; $m = isset($_REQUEST['m']) ? (int)$_REQUEST['m'] : (int)date('n'); $y = isset($_REQUEST['y']) ? (int)$_REQUEST['y'] : (int)date('Y'); $action = $_POST['action'] ?? null; // ensure manual table exists $pdo->exec(" CREATE TABLE IF NOT EXISTS department_manual_salary ( id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, dept_id INT NOT NULL, month INT NOT NULL, year INT NOT NULL, gross DECIMAL(12,2) DEFAULT 0, net DECIMAL(12,2) DEFAULT 0, advance DECIMAL(12,2) DEFAULT 0, remarks VARCHAR(255) DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY unique_entry (company_id, dept_id, month, year) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); // fetch departments map (lowercase => id) and name map $deptStmt = $pdo->prepare("SELECT id, name, code FROM company_departments WHERE company_id = :cid"); $deptStmt->execute([':cid'=>$company_id]); $dept_map = []; $dept_names = []; while ($r = $deptStmt->fetch(PDO::FETCH_ASSOC)) { if (!empty($r['name'])) $dept_map[strtolower($r['name'])] = (int)$r['id']; if (!empty($r['code'])) $dept_map[strtolower($r['code'])] = (int)$r['id']; $dept_names[(int)$r['id']] = $r['name'] ?? $r['code'] ?? ''; } /* ------------------------- Pure-PHP PDF text extractor Heuristics: - Read binary PDF - Extract strings in parentheses used by PDF text operators (Tj/TJ) - Extract arrays used by TJ operators - Fall back to all parentheses if needed - Returns a plain text blob (best-effort) ------------------------- */ function extract_text_from_pdf_basic($filepath) { $data = @file_get_contents($filepath); if ($data === false || $data === '') return ''; $texts = []; // 1) TJ arrays: [ (Hello) (World) ] TJ if (preg_match_all('/\[(.*?)\]\s*TJ/s', $data, $m_tj)) { foreach ($m_tj[1] as $chunk) { if (preg_match_all('/\((.*?)\)/s', $chunk, $m_parts)) { foreach ($m_parts[1] as $part) { $texts[] = $part; } } } } // 2) Tj operators: (Hello) Tj if (preg_match_all('/\((.*?)\)\s*Tj\b/s', $data, $m_tj2)) { foreach ($m_tj2[1] as $t) $texts[] = $t; } // 3) Single parentheses occurrences (fallback) if (empty($texts)) { if (preg_match_all('/\(([^)]{2,}?)\)/s', $data, $m_all)) { foreach ($m_all[1] as $t) $texts[] = $t; } } // join lines, collapse weird whitespace, remove control chars $out = implode("\n", $texts); // remove nulls and control chars except newline $out = preg_replace('/[\x00-\x1F\x7F\x80-\x9F]+/u', ' ', $out); $out = preg_replace('/[ ]{2,}/', ' ', $out); // split into lines and keep only lines with printable chars $lines = preg_split('/\r\n|\r|\n/', $out); $clean = []; foreach ($lines as $L) { $L2 = trim($L); if ($L2 === '') continue; // ignore lines that look like pure binary or object streams if (preg_match('/[^\x20-\x7E\s]/', $L2)) { // non-ASCII present; still keep short ascii-like parts $L2 = preg_replace('/[^\x20-\x7E]+/', ' ', $L2); $L2 = trim($L2); if ($L2 === '') continue; } $clean[] = $L2; } return implode("\n", $clean); } /* ------------------------- Parsing heuristics: text -> dept_key => amount - Uses several regex patterns similar to earlier scripts - dept_map: lowercase keys => dept_id - returns associative array mapping dept_key (lowercase or 'misc') => amount (float) ------------------------- */ function parse_totals_from_text_basic($text, $dept_map) { $text = trim($text); $res = []; if ($text === '') return $res; // patterns that typically appear in these reports $patterns = [ '/([A-Za-z0-9 \-\&\(\)\/]{2,80}?)\s+Total\s*Net[:\s]*([0-9\.,]+)/i', '/([A-Za-z0-9 \-\&\(\)\/]{2,80}?)\s+Total\s+Amount[:\s]*([0-9\.,]+)/i', '/([A-Za-z0-9 \-\&\(\)\/]{2,80}?)\s+Totals[:\s]*([0-9\.,]+)/i', '/([A-Za-z0-9 \-\&\(\)\/]{2,80}?)\s+Total[:\s]*([0-9\.,]+)/i', // pattern for lines like: "Total 121 56,220" -> capture last number on the line '/\bTotal\b[^\n\r]*?([0-9\.,]{2,})/i', ]; foreach ($patterns as $pat) { if (preg_match_all($pat, $text, $matches, PREG_SET_ORDER)) { foreach ($matches as $m) { if (count($m) >= 3) { $dept_raw = trim($m[1]); $amt_raw = $m[2]; } else { $dept_raw = ''; $amt_raw = $m[1]; } $num = floatval(str_replace(',', '', trim($amt_raw))); $found_key = null; if ($dept_raw !== '') { $dr = strtolower($dept_raw); foreach ($dept_map as $k=>$id) { if (strpos($dr, $k) !== false) { $found_key = $k; break; } } if (!$found_key) { // try tokens $parts = preg_split('/[\s\-\_]+/', $dr); foreach ($parts as $p) { if (isset($dept_map[$p])) { $found_key = $p; break; } } } } if (!$found_key) { // fallback: look for any known dept anywhere in the top part of file foreach ($dept_map as $k=>$id) { if (stripos($text, $k) !== false) { $found_key = $k; break; } } } $key = $found_key ?? 'misc'; if (!isset($res[$key])) $res[$key] = 0.0; $res[$key] += $num; } } } // additional heuristic: previous-line approach if (empty($res)) { $lines = preg_split('/\r\n|\r|\n/', $text); for ($i=0;$i<count($lines);$i++) { $ln = trim($lines[$i]); if (preg_match('/Total\s*(?:Net|Amount|Payable)?\s*[:]*\s*([0-9\.,]+)/i',$ln,$mm)) { $num = floatval(str_replace(',','',$mm[1])); // look back a few lines for a dept name $dept_candidate = ''; for ($j=$i-1;$j>=max(0,$i-6);$j--) { $prev = trim($lines[$j]); if ($prev !== '') { $dept_candidate = $prev; break; } } $key = 'misc'; if ($dept_candidate) { $dk = strtolower($dept_candidate); foreach ($dept_map as $k=>$id) { if (strpos($dk,$k)!==false) { $key = $k; break; } } } if (!isset($res[$key])) $res[$key] = 0.0; $res[$key] += $num; } } } return $res; } // map dept key to id function map_key_to_deptid($key, $dept_map) { $k = strtolower($key); return $dept_map[$k] ?? 0; } /* ------------------------- Main: scan directory, extract, parse, upsert ------------------------- */ $report = []; if ($action === 'scan') { $files = glob(rtrim($dir,'/') . '/*.pdf'); if (!$files) $report['error'] = "No PDF files found in " . htmlspecialchars($dir); else { foreach ($files as $pdf) { $entry = ['file'=>basename($pdf), 'extracted_by'=>'php_basic', 'mapped'=>[], 'errors'=>[]]; $text = extract_text_from_pdf_basic($pdf); if (trim($text) === '') { $entry['errors'][] = "Basic PHP extraction returned empty text. (Probably scanned image PDF; OCR required)"; $report[] = $entry; continue; } // parse totals $parsed = parse_totals_from_text_basic($text, $dept_map); // keys lowercase or 'misc' $entry['parsed_raw'] = $parsed; // upsert parsed amounts into manual table foreach ($parsed as $k=>$amt) { $dept_id = map_key_to_deptid($k, $dept_map); // 0 -> misc $stmt = $pdo->prepare(" INSERT INTO department_manual_salary (company_id, dept_id, month, year, gross, net, advance, remarks) VALUES (:cid, :did, :m, :y, :g, :n, :a, :r) ON DUPLICATE KEY UPDATE gross=VALUES(gross), net=VALUES(net), advance=VALUES(advance), remarks=VALUES(remarks), updated_at=NOW() "); $ok = $stmt->execute([ ':cid'=>$company_id, ':did'=>$dept_id, ':m'=>$m, ':y'=>$y, ':g'=>0.00, ':n'=>round((float)$amt,2), ':a'=>0.00, ':r'=>'Imported (php_basic) from ' . basename($pdf), ]); $entry['mapped'][] = ['key'=>$k, 'dept_id'=>$dept_id, 'dept_name'=>($dept_id?$dept_names[$dept_id] ?? '':'(unknown)'), 'amount'=>round((float)$amt,2), 'inserted'=> (bool)$ok]; } $report[] = $entry; } } } /* ------------------------- UI render ------------------------- */ ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>Parse PDFs (PHP-only) → Department Manual Salary</title> <style> body{font-family:Arial,Helvetica,sans-serif;padding:12px} table{border-collapse:collapse;width:100%} th,td{border:1px solid #ddd;padding:6px} th{background:#f7f7f7} .ok{color:green} .err{color:red} pre{background:#fafafa;padding:8px;border:1px solid #eee} </style> </head> <body> <h2>Parse PDFs (PHP-only) → Department Manual Salary</h2> <p style="color:#333">This version <strong>does not</strong> use shell_exec / external commands. It uses pure-PHP heuristics to extract text from text-based PDFs. If a PDF is scanned (image), this script cannot OCR it — you will need Tesseract/Poppler on the server (ask host to enable shell_exec or provide those binaries).</p> <form method="post"> <label>PDF Folder: <input type="text" name="dir" value="<?=htmlspecialchars($dir)?>" style="width:480px;"></label><br><br> <label>Month: <input type="number" name="m" value="<?=htmlspecialchars($m)?>" style="width:60px;"></label> <label style="margin-left:12px">Year: <input type="number" name="y" value="<?=htmlspecialchars($y)?>" style="width:80px;"></label> <button type="submit" name="action" value="scan" style="margin-left:12px;padding:6px 12px;">Scan & Import (PHP-only)</button> </form> <hr> <?php if (!empty($report)): ?> <h3>Scan Results</h3> <?php foreach ($report as $r): ?> <div style="margin-bottom:12px;padding:8px;border:1px solid #eee;"> <strong>File:</strong> <?=htmlspecialchars($r['file'])?> — <em>extracted by</em>: <?=htmlspecialchars($r['extracted_by'])?><br> <?php if (!empty($r['errors'])): ?> <div class="err">Errors: <?=htmlspecialchars(implode('; ',$r['errors']))?></div> <?php endif; ?> <table> <thead><tr><th>Parsed key</th><th>Dept ID</th><th>Dept Name</th><th>Amount</th><th>Inserted</th></tr></thead> <tbody> <?php foreach ($r['mapped'] as $mrow): ?> <tr> <td><?=htmlspecialchars($mrow['key'])?></td> <td><?=htmlspecialchars($mrow['dept_id'])?></td> <td><?=htmlspecialchars($mrow['dept_name'])?></td> <td align="right"><?=number_format($mrow['amount'],2)?></td> <td><?=($mrow['inserted']?'<span class="ok">yes</span>':'<span class="err">no</span>')?></td> </tr> <?php endforeach; ?> </tbody> </table> </div> <?php endforeach; ?> <?php endif; ?> <hr> <h4>Notes & Next steps</h4> <ul> <li>If many files show the error "Basic PHP extraction returned empty text", those PDFs are likely scanned images and require OCR. For OCR you need host support (tesseract + pdftoppm) or allow shell_exec to run commands. Ask hosting to enable / provide these.</li> <li>If you want me to produce a small CSV now from the successful imports, I can add a "Download CSV" button.</li> <li>If you want me to attempt a more advanced PHP extractor (using composer libraries like smalot/pdfparser), I can provide composer.json and instructions — but that needs composer on the server.</li> </ul> </body> </html>