import openpyxl import pdfplumber import re from io import BytesIO # 🚀 Dedicated parsers import from parser_welspun import extract_welspun_items, map_items_to_excel_dynamic from parser_bkt import extract_bkt_items from parser_polycab import extract_polycab_items, map_polycab_items_to_excel_dynamic from parser_vapi_welspun import extract_vapi_welspun_items, map_vapi_welspun_items_to_excel_dynamic from pdf_engine import apply_rule_filter, extract_header_value from google_sheet_sync import load_template_from_sheet from supporting_engine import extract_data_from_supporting_file def process_invoices_backend(selected_shipper, shipper_info, main_inv_file, gst_inv_file, deec_decl_file): """ Gradio ke file objects ko process karke Excel file ki bytes return karta hai. """ if main_inv_file is None: return None, "⚠️ Mukhya invoice file missing hai!" try: rules = shipper_info.get("mapping_rules", {}) item_table_rules = shipper_info.get("item_table_rules", {}) assigned_parser = shipper_info.get("item_table_rule_name", "parser_welspun").strip().lower() igst_cfg = shipper_info.get("igst_config", {}) lut_kws = igst_cfg.get("lut_keywords", "") paid_kws = igst_cfg.get("paid_keywords", "") wb = load_template_from_sheet(selected_shipper) if wb is None: wb = openpyxl.Workbook() ws = wb["INV"] if "INV" in wb.sheetnames else wb.active first_inv_no = "INV" overall_item_sr = 1 excel_write_row = 2 # Gradio file object ka path ya bytes handle karna file_bytes = main_inv_file.read() if hasattr(main_inv_file, "read") else open(main_inv_file, "rb").read() file_name = getattr(main_inv_file, "name", "invoice.pdf") pdf_text = "" pdf_lines = [] if file_name.lower().endswith(".pdf"): with pdfplumber.open(BytesIO(file_bytes)) as pdf: for page in pdf.pages: t = page.extract_text() if t: pdf_text += t + "\n" pdf_lines.extend(t.split("\n")) else: excel_text, _ = extract_data_from_supporting_file(main_inv_file) if excel_text: pdf_text = excel_text pdf_lines = excel_text.split("\n") gst_text, _ = extract_data_from_supporting_file(gst_inv_file) if gst_inv_file else ("", None) deec_text, _ = extract_data_from_supporting_file(deec_decl_file) if deec_decl_file else ("", None) current_inv_number = "INV_1" current_inv_date = "" inv_data_dict = {} summary_row = 2 for field, r_info in rules.items(): kw = r_info.get("keyword", "").strip() if kw.startswith("'") and len(kw) > 1: kw = kw[1:].strip() pos = r_info.get("position", "Right (आगे)") target_cell = r_info.get("cell", "").strip().upper() mode = r_info.get("match_mode", "Exact Word") stop_kw = r_info.get("stop_kw", "").strip() flt = r_info.get("filter", "None") fallback_val = r_info.get("fallback", "").strip() doc_source = r_info.get("logic", "Main Invoice") extracted_logic = r_info.get("extracted_logic", "").strip() found_val = None target_lines, target_full_text = pdf_lines, pdf_text if "gst" in doc_source.lower() and gst_inv_file: target_lines = gst_text.split("\n") target_full_text = gst_text elif "deec" in doc_source.lower() and deec_decl_file: target_lines = deec_text.split("\n") target_full_text = deec_text if extracted_logic: try: clean_code = extracted_logic.replace("```python", "").replace("```", "").strip() clean_code = clean_code.replace(r"\n", "\n") local_vars = {"text": target_full_text, "lines": target_lines, "re": re} exec(clean_code, {}, local_vars) found_val = local_vars.get("value", None) except Exception: found_val = None if not found_val or not str(found_val).strip(): found_val = extract_header_value(target_lines, target_full_text, kw, pos, mode, stop_kw, flt, field_label=field, pdf_bytes=file_bytes) if not found_val or not str(found_val).strip(): if fallback_val: found_val = fallback_val inv_data_dict[field.lower()] = found_val if target_cell and "dynamic" not in target_cell.lower(): try: if "\n" in str(found_val): col_letters = re.findall(r'[A-Za-z]+', target_cell)[0].upper() start_row_num = int(re.findall(r'\d+', target_cell)[0]) if re.findall(r'\d+', target_cell) else summary_row for idx, line_val in enumerate(str(found_val).split("\n")): ws[f"{col_letters}{start_row_num + idx}"] = line_val.strip() else: cell_to_write = f"{target_cell}{summary_row}" if target_cell.isalpha() else target_cell ws[cell_to_write] = found_val except Exception: pass if "inv. no" in field.lower() or "invoice no" in field.lower(): if found_val: current_inv_number = found_val first_inv_no = found_val if "date" in field.lower() or "dt" in field.lower(): d_match = re.search(r'\b\d{2}[./-]\d{2}[./-]\d{4}\b', str(found_val)) if d_match: current_inv_date = d_match.group(0).replace(".", "/").replace("-", "/") elif found_val and not str(found_val).lower().startswith("inv"): current_inv_date = found_val ws[f"AH{summary_row}"] = 1 ws[f"AI{summary_row}"] = current_inv_number if current_inv_date: ws[f"AJ{summary_row}"] = current_inv_date resolved_item_rules = {} for i_name, i_info in item_table_rules.items(): i_type = i_info.get("type", "") i_rule = i_info.get("rule", "") if i_rule.startswith("'") and len(i_rule) > 1: i_rule = i_rule[1:].strip() i_col = i_info.get("col", "K") actual_rule_val = i_rule if i_type == "Header Field Mapping" and i_rule.lower() in inv_data_dict: actual_rule_val = inv_data_dict[i_rule.lower()] resolved_item_rules[i_name] = { "col": i_col, "type": i_type if i_type != "Header Field Mapping" else "Constant Text", "rule": actual_rule_val } if assigned_parser == "parser_bkt": parsed_items = extract_bkt_items(pdf_lines) elif assigned_parser == "parser_polycab": parsed_items = extract_polycab_items(pdf_lines, pdf_text=pdf_text) elif assigned_parser == "parser_vapi_welspun": parsed_items = extract_vapi_welspun_items(pdf_lines, pdf_text=pdf_text) else: parsed_items = extract_welspun_items(pdf_lines, pdf_text=pdf_text) if assigned_parser == "parser_polycab": ws, overall_item_sr, excel_write_row = map_polycab_items_to_excel_dynamic( ws, parsed_items, resolved_item_rules, 1, overall_item_sr, excel_write_row, current_inv_number, current_inv_date, pdf_text, lut_kws, paid_kws, assigned_parser ) elif assigned_parser == "parser_vapi_welspun": ws, overall_item_sr, excel_write_row = map_vapi_welspun_items_to_excel_dynamic( ws, parsed_items, resolved_item_rules, 1, overall_item_sr, excel_write_row, current_inv_number, current_inv_date, pdf_text, lut_kws, paid_kws, assigned_parser ) else: ws, overall_item_sr, excel_write_row = map_items_to_excel_dynamic( ws, parsed_items, resolved_item_rules, 1, overall_item_sr, excel_write_row, current_inv_number, current_inv_date, pdf_text, lut_kws, paid_kws, assigned_parser ) output = BytesIO() wb.save(output) short_shipper = selected_shipper.split(" ")[0].lower() clean_inv = re.sub(r'[\\/*?:"<>|]', "", first_inv_no) final_filename = f"{clean_inv}_{short_shipper}_MultiDoc.xlsx" return output.getvalue(), final_filename except Exception as e: return None, str(e)