nj?4ddlZddlZddlZddlZddlmZddlmZddlm Z ddl m Z e deej d Z d Zd ed efd Zd ed efdZd ed efdZd ed efdZdeded efdZdS)N)Path)OpenAI)read_file_content) load_dotenvT)overrideOPENAI_API_KEY)api_keyu{You are a pharma data extraction engine. Extract EVERY field available from this document. Do not skip any field. Return JSON only with this structure: { "filename": string, "document_type_code": one of ["SOP","BMR","EQP","LIMS","MAINT","DEV","UNKNOWN"], "batch_id": string or null, "product_name": string or null, "plant": string or null, "manufacturing_date": string or null, "parameters": [ { "parameter_name": string, "observed_value": string or null, "lower_limit": string or null, "upper_limit": string or null, "unit": string or null, "process_step": string or null, "equipment_id": string or null, "timestamp": string or null, "operator_id": string or null, "status": one of ["Normal", "DEVIATION", "PASS", "FAIL", "Missing"], "notes": string or null, "source_reference": string or null, "confidence": number 0-100 } ], "lims_results": [ { "sample_id": string or null, "test_name": string or null, "result_value": string or null, "specification_low": string or null, "specification_high": string or null, "unit": string or null, "status": string or null, "test_date": string or null, "analyst_id": string or null, "instrument_id": string or null, "notes": string or null } ], "equipment_readings": [ { "timestamp": string or null, "equipment_id": string or null, "parameter": string or null, "value": string or null, "unit": string or null, "setpoint": string or null, "lower_limit": string or null, "upper_limit": string or null, "status": string or null, "operator_id": string or null, "notes": string or null } ], "maintenance_records": [ { "equipment_id": string or null, "maintenance_date": string or null, "maintenance_type": string or null, "performed_by": string or null, "duration_hours": string or null, "observation": string or null, "action_taken": string or null, "next_pm_due": string or null, "work_order_id": string or null, "sign_off_by": string or null, "status": string or null } ], "deviation_details": { "deviation_id": string or null, "severity": string or null, "description": string or null, "reported_by": string or null, "date_of_occurrence": string or null, "time_of_occurrence": string or null, "immediate_actions": array of strings, "equipment_id": string or null, "calibration_status": string or null, "batch_disposition": string or null, "root_cause_preliminary": string or null, "linked_records": object or null }, "sop_limits": [ { "process_step": string or null, "equipment_id": string or null, "parameter_name": string or null, "lower_limit": string or null, "upper_limit": string or null, "unit": string or null, "criticality": string or null, "action_if_exceeded": string or null } ], "metadata": { "total_parameters": number, "total_lims_results": number, "total_equipment_readings": number, "total_maintenance_records": number, "deviations_found": number, "missing_values": number, "low_confidence_count": number, "operators_found": array of strings, "equipment_found": array of strings, "extraction_method": string } } Field extraction rules: - BMR file: fill parameters[] — each row has batch_id, step_name, equipment_id, timestamp, parameter_name, observed_value, lower_limit, upper_limit, unit, status - Equipment Log file: fill equipment_readings[] — each row has timestamp, equipment_id, parameter, value, unit, setpoint, lower_limit, upper_limit, status, operator_id, notes - LIMS file: fill lims_results[] — each row has sample_id, batch_id, test_name, result_value, specification_low, specification_high, unit, status, test_date, analyst_id, instrument_id, notes - Maintenance file: fill maintenance_records[] — each row has equipment_id, maintenance_date, maintenance_type, performed_by, observation, action_taken, next_pm_due, work_order_id, sign_off_by, status - Deviation JSON: fill deviation_details{} with all fields - SOP PDF: fill sop_limits[] with all CPP rows CRITICAL: Never leave operator_id null if it exists in the source data. CRITICAL: Never leave status null if it exists in the source data. CRITICAL: Extract ALL rows — do not summarize or skip rows. CRITICAL: If this is a scanned/handwritten document — read every visible field carefully including stamps, signatures, handwritten values. Document filename: Document content: file_pathreturnc tjt|}d}|D]+}||z },|t |dkS#t$rYdSwxYw)uNReturns True if PDF has no extractable text — meaning it is a scanned image.2F)fitzopenstrget_textstripcloselen Exception)r doc total_textpages DC:\Users\Terasoftware\OneDrive\Desktop\faahhh\fyndo\fyndo\extract.pyis_scanned_pdfrs iI''  2 2D $--////11 1JJ :## uusA7A:: BBc8|jdvS)z!Returns True if file is an image.)z.pngz.jpgz.jpegz.tiffz.tifz.bmpz.webp)suffixlower)r s r is_image_filers   ! ! # #'b bbctd|jg}t|rt|d5}|}dddn #1swxYwYt j|}|j dd}|dkrd}| |d|d ntjt|}ttt!|d D]s}||}|d } | d }t j|}| |dd t||st)dt!|} td| ddd| d| d| d|jdt* d|j dd dg} |D]/} | dd| dd| d d!d"d#0t,jjd$d%d&d'd(d)| d(gd*d+id,-} | jd,jj}t;j|}|d.i|d.<d/|d.d0<|S)1zg Converts scanned PDF pages or images to base64 and sends to GPT-4o Vision for extraction. z0[EXTRACT] Using GPT-4o Vision for scanned file: rbN.r jpgjpegzimage/)b64mimeri,)dpipngz image/pngzNo images extracted from filez[EXTRACT] Sending z pages to GPT-4o VisiontextzdYou are a pharma data extraction engine specializing in scanned pharma documents. This document has z pages. Extract data from ALL pages. CRITICAL INSTRUCTIONS for scanned/handwritten documents: - Read EVERY cell in every table row across ALL u- pages — do not skip any row - CRITICAL: Look for rows with status DEVIATION — these are the most important rows - CRITICAL: Look for temperature readings above 70°C — these indicate deviations - For unclear or partially visible text — make your best interpretation based on context - For handwritten numbers — read carefully, common values are temperatures (60-80°C), weights (kg), percentages - Never write "(unclear)" — always provide your best reading of the value - If a value is truly unreadable — use null, not "(unclear)" - Read ALL ue pages — extract data from every page not just the first - For rotated or sideways text — rotate mentally and read it - For faded text — use context clues from surrounding cells to interpret - Batch IDs follow pattern like BTC0048, equipment IDs like EQP_GRAN_01 - Status values are typically: Normal, DEVIATION, Alert, Pass, Fail Document filename: z u@[See scanned document images above — read every visible field])typer* image_urlzdata:r'z;base64,r&high)urldetail)r-r.gpt-4oipsystemzYou are a pharma data extraction engine. Extract every visible field from scanned documents including handwritten data. Return JSON only.rolecontentuserr- json_objectr)model max_tokensmessagesresponse_format temperaturemetadatagpt4o_vision_ocrextraction_method) printnamerrreadbase64 b64encodedecoderrreplaceappendrrrangeminr get_pixmaptobytesr ValueErrorEXTRACT_PROMPTclientchat completionscreatechoicesmessager6jsonloadsget)r images_b64f img_bytesr&extrpage_numrpix total_pagesr6imgresponserawresults rextract_with_visionrcs  MY^ M MNNNJY )T " " !aI ! ! ! ! ! ! ! ! ! ! ! ! ! ! !y))0022$$&&..sB77 %< C#~~~>>????iI''c#c((B//00 A AHx=D//c/**C E**I"9--4466C   c;?? @ @ @ @ :8999j//K C{ C C CDDD SSS2= SS SS"N#SS& in55==kLNOO'SS  G6  @s6{@@CJ@@       {&--!g   "     /.H"  1  % -C Z__FJ33F:.@F:*+ MsAA!AcK d}t|rd}td|jn]|jdkr)t |rd}td|jntd|j|rt |}nt|}t d|j}| d|dd }tj j d d d d d|d gddid}|jdjj}t#j|}|dr d|dd<|j|d<d|d<|S#t"j$r"}t+|jd|cYd}~Sd}~wt,$r,}t+|jt/|cYd}~Sd}~wwxYw)NFTz[EXTRACT] Image file detected: z.pdfz [EXTRACT] Scanned PDF detected: zXTRACT] Text-based file: r+r,i@r2r3z~You are a pharma data extraction engine. Extract every field from every row. Never skip data. Return JSON only. No extra text.r4r7r-r8r)r9r;r<r=r>r*r@filename extractedstatusz parse_error: )rrArBrrrrcrrNrGrOrPrQrRrSrTr6rUrVrWJSONDecodeError_error_responserr)r use_visionrbr6promptr`raes rextract_documentrmsM27  # # BJ DINDD E E E E   # # % % / BN94M4M BJ EY^EE F F F F @ @@ A A A  A(33FF( 22G#++L).IIF^^K$@@F{.55!)$d !'#) "( 76H "1%-5CZ__Fzz*%% A:@z"#67&^z&x  DDDy~/Bq/B/BCCCCCCCC 777y~s1vv666666667s0E4E99GF%G% G2!GGGreerrorc2|d|ggggigdddddddggdd d S)Nrnr) total_parameterstotal_lims_resultstotal_equipment_readingstotal_maintenance_recordsdeviations_foundmissing_valueslow_confidence_countoperators_foundequipment_foundr@) rergrn parameters lims_resultsequipment_readingsmaintenance_recordsdeviation_details sop_limitsr>)rerns rririFsT ! !"#())* !$%!!!(     r )osrUrDrpathlibropenair file_readerrdotenvrgetenvrOrNboolrrdictrcrmrrirr rrsu )))))) T   "233 4 4 4BR d t    cTcdcccc\4\D\\\\F37d37t37373737lc#$r