o nj?4@sddlZddlZddlZddlZddlmZddlmZddlm Z ddl m Z e ddee dd Z d Zd ed efd dZd ed efddZd ed efddZd ed efddZdeded efddZdS)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_pathreturncCsXz!tt|}d}|D] }||7}q |t|dkWSty+YdSw)uNReturns True if PDF has no extractable text — meaning it is a scanned image.2F)fitzopenstrget_textstripcloselen Exception)r doc total_textpager+/var/www/html/fyndo/pharma/fyndo/extract.pyis_scanned_pdfs rcCs|jdvS)z!Returns True if file is an image.)z.pngz.jpgz.jpegz.tiffz.tifz.bmpz.webp)suffixlower)r rrr is_image_filesrcCstd|jg}t|rIt|d }|}Wdn1s"wYt|}|j dd}|dkr=d}| |d|d n5t t |}ttt|d D] }||}|jd d } | d }t|}| |dd qY||stdt|} td| ddd| d| d| d|jdt d|j dd dg} |D]} | dd| dd| d d!d"d#qtjjjd$d%d&d'd(d)| d(gd*d+id,d-} | jd,jj}t|}|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/)b64mimer i,)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 b64encodedecoderrreplaceappendr rrangeminr get_pixmaptobytesr ValueErrorEXTRACT_PROMPTclientchat completionscreatechoicesmessager2jsonloadsget)r images_b64f img_bytesr"extrpage_numrpix total_pagesr2imgresponserawresultrrrextract_with_visions|         r_c stzd}t|rd}td|jn|jdkr)t|r)d}td|jntd|j|r8t|}nAt|}t d|j}| d|dd }t j j j d d d d d|d gddidd}|jdjj}t|}|dryd|dd<|j|d<d|d<|WStjy}zt|jd|WYd}~Sd}~wty}zt|jt|WYd}~Sd}~ww)NFTz[EXTRACT] Image file detected: z.pdfz [EXTRACT] Scanned PDF detected: zXTRACT] Text-based file: r'r(i@r.r/z~You are a pharma data extraction engine. Extract every field from every row. Never skip data. Return JSON only. No extra text.r0r3r)r4r)r5r7r8r9r:r&r<filename extractedstatusz parse_error: )rr=r>rrrr_rrJrCrKrLrMrNrOrPr2rQrRrSJSONDecodeError_error_responserr)r use_visionr^r2promptr\r]errrextract_documentsR      rhr`errorcCs0|d|ggggigdddddddggdd d S)Nrir) total_parameterstotal_lims_resultstotal_equipment_readingstotal_maintenance_recordsdeviations_foundmissing_valueslow_confidence_countoperators_foundequipment_foundr<) r`rbri parameters lims_resultsequipment_readingsmaintenance_recordsdeviation_details sop_limitsr:r)r`rirrrrdFs*rd)osrQr@r pathlibropenair file_readerrdotenvrgetenvrKrJboolrrdictr_rhrrdrrrrs"       c6