extract.py
"""Local extraction for two known synthetic text-PDF layouts; heuristic QA."""
import argparse,csv,json,re
from decimal import Decimal,InvalidOperation
from datetime import datetime
from pathlib import Path
import pdfplumber
from openpyxl import Workbook
from openpyxl.styles import Font,PatternFill
from openpyxl.utils import get_column_letter
ROOT=Path(__file__).resolve().parent
HEADERS={'invoice':['Line','Description','Qty','Unit price','Amount'],'statement':['Date','Reference','Description','Debit','Credit','Balance']}
NUMERIC={'invoice':{'Qty','Unit price','Amount'},'statement':{'Debit','Credit','Balance'}}
class LayoutError(ValueError): pass
def decimal_cell(raw):
raw=str(raw or '').strip()
if not re.fullmatch(r'(?:\d+|\d{1,3}(?:,\d{3})+)(?:\.\d{1,2})?',raw): return None
try: return Decimal(raw.replace(',',''))
except InvalidOperation: return None
def safe_text(raw):
raw=str(raw or ''); return "'"+raw if raw.startswith(('=','+','-','@','\t','\r')) else raw
def field(text,label):
m=re.search(re.escape(label)+r'\s*([0-9,]+(?:\.[0-9]+)?)',text)
return decimal_cell(m.group(1)) if m else None
def reconcile(actual,expected,incomplete=False):
status='INCOMPLETE' if incomplete or expected is None else ('PASS' if actual==expected else 'MISMATCH')
return {'status':status,'actual':str(actual),'expected':str(expected) if expected is not None else None,'difference':str(actual-expected) if expected is not None and not incomplete else None}
def extract_document(pdf_path):
pdf_path=Path(pdf_path); rows=[]; flags=[]; texts=[]; kind=None; document_id=None
with pdfplumber.open(pdf_path) as doc:
for page_no,page in enumerate(doc.pages,1):
text=page.extract_text() or ''; texts.append(text)
page_kind='invoice' if 'Invoice No:' in text else ('statement' if 'Statement No:' in text else None)
if page_kind is None or (kind and page_kind!=kind): raise LayoutError('UNSUPPORTED_OR_MIXED_LAYOUT')
kind=page_kind; matches=[t for t in page.extract_tables() if t and t[0]==HEADERS[kind]]
if len(matches)!=1: raise LayoutError(f'TABLE_SCHEMA:page={page_no}')
identity=re.search(r'(?:Invoice|Statement) No: ([A-Za-z0-9_-]+)',text)
if not identity or (document_id is not None and document_id!=identity.group(1)): raise LayoutError('DOCUMENT_ID_MISMATCH')
document_id=identity.group(1)
controls=re.findall(r'\bPage (\d+) of (\d+)\b',text)
if controls!=[(str(page_no),str(len(doc.pages)))]: raise LayoutError('PAGE_SEQUENCE')
for physical_row,raw in enumerate(matches[0][1:],1):
if len(raw)!=len(HEADERS[kind]) or all(not x for x in raw): raise LayoutError('INVALID_TABLE_ROW')
row={k:str(v or '').strip() for k,v in zip(HEADERS[kind],raw)}
row.update(source_pdf=pdf_path.name,source_page=page_no,source_row=physical_row,provenance=f'{pdf_path.name}:p{page_no}:r{physical_row}',review_status='OK')
bad=[]
for key in sorted(NUMERIC[kind]):
value=row[key]
if kind=='statement' and key in ('Debit','Credit') and value=='': continue
if decimal_cell(value) is None:
bad.append(key); flags.append({'provenance':row['provenance'],'column':key,'raw_value':value,'reason':'NOT_STRICT_DECIMAL','confidence':'LOW','action':'Review source; value left blank; never inferred from totals'}); row[key]=''
for key in HEADERS[kind]:
if key not in NUMERIC[kind]: row[key]=safe_text(row[key])
row['review_status']='REVIEW' if bad else 'OK'; row['low_confidence_columns']=';'.join(sorted(bad)); rows.append(row)
page_count=len(doc.pages)
if not rows: raise LayoutError('NO_DATA_ROWS')
text='\n'.join(texts); expected_rows=field(text,'Line items:' if kind=='invoice' else 'Transactions:')
if expected_rows is None: raise LayoutError('MISSING_ROW_CONTROL')
totals={}; row_checks=[]
def column_total(column,label):
missing=any(column in r['low_confidence_columns'].split(';') for r in rows)
total=sum((decimal_cell(r[column]) or Decimal(0) for r in rows),Decimal(0))
totals[column]=reconcile(total,field(text,label),missing)
if kind=='invoice':
column_total('Qty','Total quantity:'); column_total('Amount','Subtotal USD:')
for row in rows:
values=[decimal_cell(row[k]) for k in ('Qty','Unit price','Amount')]
status='INCOMPLETE' if any(v is None for v in values) else ('PASS' if values[0]*values[1]==values[2] else 'MISMATCH')
row_checks.append({'provenance':row['provenance'],'check':'quantity_x_unit_price','status':status})
else:
column_total('Debit','Total debits:'); column_total('Credit','Total credits:'); opening=field(text,'Opening balance:'); prior=opening
for row in rows:
debit=decimal_cell(row['Debit']) if row['Debit'] else Decimal(0); credit=decimal_cell(row['Credit']) if row['Credit'] else Decimal(0); current=decimal_cell(row['Balance'])
missing=bool(row['low_confidence_columns']) or prior is None or current is None
row_checks.append({'provenance':row['provenance'],'check':'running_balance',**reconcile((prior or Decimal(0))+(credit or Decimal(0))-(debit or Decimal(0)),current,missing)})
prior=current
totals['Closing balance']=reconcile(prior or Decimal(0),field(text,'Closing balance:'),prior is None)
missing=any('Debit' in r['low_confidence_columns'] or 'Credit' in r['low_confidence_columns'] for r in rows)
calculated=(opening or Decimal(0))+sum((decimal_cell(r['Credit']) or Decimal(0) for r in rows),Decimal(0))-sum((decimal_cell(r['Debit']) or Decimal(0) for r in rows),Decimal(0))
totals['Net movement to closing']=reconcile(calculated,field(text,'Closing balance:'),missing or opening is None)
row_count=reconcile(Decimal(len(rows)),expected_rows); statuses=[row_count['status']]+[v['status'] for v in totals.values()]+[v['status'] for v in row_checks]
quality='FAIL' if 'MISMATCH' in statuses else ('REVIEW_REQUIRED' if flags or 'INCOMPLETE' in statuses else 'PASS')
return rows,{'source_pdf':pdf_path.name,'kind':kind,'document_id':document_id,'pages':page_count,'page_sequence':'PASS','row_count':row_count,'totals':totals,'row_checks':row_checks,'low_confidence_cells':flags,'quality':quality,'confidence_method':'Deterministic parsing/arithmetic flags, not a calibrated probability.'}
def write_csv(path,rows,fields):
with Path(path).open('w',newline='',encoding='utf-8') as f:
writer=csv.DictWriter(f,fieldnames=fields); writer.writeheader(); writer.writerows(rows)
def run(source=None,output=None):
source=Path(source or ROOT/'sources'); output=Path(output or ROOT/'output'); output.mkdir(parents=True,exist_ok=True); documents=[]; all_rows={}; flags=[]
for name in ('invoice','statement'):
rows,qa=extract_document(source/(name+'.pdf')); documents.append(qa); all_rows[name]=rows; flags.extend(qa['low_confidence_cells']); write_csv(output/(name+'.csv'),rows,list(rows[0]))
wb=Workbook(); wb.remove(wb.active); wb.properties.title='SYNTHETIC invoice and statement extraction'; wb.properties.creator='Synthetic portfolio demo'
for kind,rows in all_rows.items():
ws=wb.create_sheet(kind); ws.append(list(rows[0]))
for row in rows:
values=[]
for key,value in row.items():
if key in NUMERIC[kind]: value=decimal_cell(value) if value else None
elif kind=='statement' and key=='Date': value=datetime.strptime(value,'%Y-%m-%d').date()
values.append(value)
ws.append(values)
for cells in ws.iter_rows(min_row=2):
for cell,key in zip(cells,rows[0]):
if key in NUMERIC[kind]: cell.number_format='0' if key=='Qty' else '#,##0.00'
if key=='Date': cell.number_format='yyyy-mm-dd'
ws.freeze_panes='A2'; ws.auto_filter.ref=ws.dimensions
for cell in ws[1]: cell.font=Font(bold=True,color='FFFFFF'); cell.fill=PatternFill('solid',fgColor='17324D')
for cells in ws.iter_rows(min_row=2):
if cells[-2].value=='REVIEW':
for cell in cells: cell.fill=PatternFill('solid',fgColor='FFF1CC')
widths={'Description':32,'provenance':34,'source_pdf':20,'low_confidence_columns':28,'review_status':18,'source_page':16,'source_row':16}
for i,key in enumerate(rows[0],1): ws.column_dimensions[get_column_letter(i)].width=widths.get(key,max(15,len(key)+3))
ws.sheet_view.showGridLines=False; ws.sheet_properties.pageSetUpPr.fitToPage=True
ws.page_setup.orientation='landscape'; ws.page_setup.paperSize=ws.PAPERSIZE_A3; ws.page_setup.fitToWidth=1; ws.page_setup.fitToHeight=1
ws.print_title_rows='1:1'; ws.print_options.horizontalCentered=True
for cell in ws[1]: cell.alignment=__import__('openpyxl').styles.Alignment(wrap_text=True,vertical='center')
ws.row_dimensions[1].height=30
ws=wb.create_sheet('review_queue'); review_fields=['provenance','column','raw_value','reason','confidence','action']; ws.append(review_fields); ws.freeze_panes='A2'
for flag in flags: ws.append([safe_text(flag[k]) for k in review_fields])
for i in range(1,len(review_fields)+1): ws.column_dimensions[get_column_letter(i)].width=34 if i==1 else (80 if i==6 else 24)
wb.save(output/'extracted.xlsx'); write_csv(output/'review_queue.csv',flags,review_fields)
qa={'synthetic':True,'documents':documents,'total_rows':sum(len(r) for r in all_rows.values()),'low_confidence_cell_count':len(flags),'overall_quality':'FAIL' if any(d['quality']=='FAIL' for d in documents) else ('REVIEW_REQUIRED' if flags or any(d['quality']=='REVIEW_REQUIRED' for d in documents) else 'PASS')}
(output/'qa.json').write_text(json.dumps(qa,indent=2)+'\n',encoding='utf-8')
lines=['# Synthetic extraction QA report','','**Overall result: '+qa['overall_quality']+'**','',f"{qa['total_rows']} extracted rows across {sum(d['pages'] for d in documents)} pages. {len(flags)} low-confidence cell(s).",'','Source totals are controls, not permission to invent missing values. Amounts use Decimal arithmetic.','','| Document | Rows / expected | Pages | Result |','|---|---:|---:|---|']
for d in documents: lines.append(f"| {d['source_pdf']} | {d['row_count']['actual']} / {d['row_count']['expected']} | {d['pages']} | {d['quality']} |")
for d in documents:
lines+=['','## '+d['source_pdf']+' reconciliations','','| Column/control | Known sum | Source control | Status | Difference |','|---|---:|---:|---|---:|']
for k,v in d['totals'].items(): lines.append(f"| {k} | {v['actual']} | {v['expected']} | {v['status']} | {v['difference'] if v['difference'] is not None else 'unresolved'} |")
counts={s:sum(r['status']==s for r in d['row_checks']) for s in ['PASS','MISMATCH','INCOMPLETE']}
lines+=['',f"Row arithmetic: {counts['PASS']} pass, {counts['MISMATCH']} mismatch, {counts['INCOMPLETE']} incomplete."]
lines+=['','## Manual review queue','','| Page/row provenance | Cell | Raw source | Action |','|---|---|---|---|']
for f in flags: lines.append(f"| {f['provenance']} | {f['column']} | {f['raw_value']} | Left blank; source review required |")
lines+=['','Confidence is a deterministic rule flag, not a statistical probability. No OCR or online service was used.','QA PASS means these controls agree for this known layout; it does not establish universal extraction accuracy.']
(output/'QA_REPORT.md').write_text('\n'.join(lines)+'\n',encoding='utf-8'); return qa
if __name__=='__main__':
p=argparse.ArgumentParser(description=__doc__); p.add_argument('--source',type=Path); p.add_argument('--output',type=Path); args=p.parse_args(); qa=run(args.source,args.output)
print(json.dumps({'synthetic':True,'total_rows':qa['total_rows'],'low_confidence_cells':qa['low_confidence_cell_count'],'quality':qa['overall_quality']})); raise SystemExit(2 if qa['overall_quality']=='FAIL' else 0)