parser_vapi_welspun.py

import re
import pdfplumber
from io import BytesIO
from openpyxl.styles import Alignment
from pdf_engine import apply_value_replacement, extract_header_value

def extract_all_commodities_from_text(pdf_text):
    commodities = []
    pattern = r'(\d{8})\s*[:\-]\s*(.+)'
    matches = re.findall(pattern, pdf_text)
    for hsn, desc in matches:
        full_desc = f"{hsn}: {desc.strip()}"
        commodities.append(full_desc)
    return commodities

def extract_vapi_welspun_items(pdf_lines, pdf_text=""):
    parsed_items = []
    box_commodities = []
    if pdf_text:
        box_commodities = extract_all_commodities_from_text(pdf_text)
        
    parsed_items.append({"dbk_sr": "", "hs_code": "", "description_text": "", "net_wt": "", "qty": "", "rate": "", "amount_usd": "", "amount_inr": "", "igst_per": "5.00", "igst_amt": "", "sqmtr": "", "box_commodities": box_commodities})
    return parsed_items


def map_vapi_welspun_items_to_excel_dynamic(ws, parsed_items, item_rules, inv_sr_no=1, start_overall_sr=1, start_excel_row=2, default_invoice_no="", default_invoice_date="", pdf_text="", lut_kws="", paid_kws="", parser_rule=""):
    curr_row = start_excel_row
    overall_sr = start_overall_sr
    
    pdf_text_upper = str(pdf_text).upper()
    pdf_lines = str(pdf_text).split("\n")
    
    l_keywords = [k.strip().upper() for k in str(lut_kws).split(",") if k.strip()]
    p_keywords = [k.strip().upper() for k in str(paid_kws).split(",") if k.strip()]
    
    matched_lut = any(kw.replace("NO.", "").replace(".", "").strip() in pdf_text_upper for kw in l_keywords if kw.strip())
    matched_paid = any(kw.replace(".", "").strip() in pdf_text_upper for kw in p_keywords if kw.strip())

    v_column_value = "LUT" if matched_lut else ("P" if matched_paid else "LUT")

    max_rows = len(parsed_items)
    first_item = parsed_items[0] if parsed_items else {}
    all_comms = first_item.get("box_commodities", [])

    for item_idx in range(max_rows):
        item_sr_no = item_idx + 1
        item = parsed_items[item_idx] if item_idx < len(parsed_items) else {}
        
        ws[f"G{curr_row}"] = inv_sr_no                    
        ws[f"H{curr_row}"] = item_sr_no                                      
        ws[f"V{curr_row}"] = v_column_value               
        
        ws[f"I{curr_row}"] = default_invoice_no
        if default_invoice_date and not "ROSC" in str(default_invoice_date):
            ws[f"J{curr_row}"] = default_invoice_date

        if all_comms:
            cell_ref_bs = f"BS{curr_row}"
            if item_idx < len(all_comms):
                ws[cell_ref_bs] = all_comms[item_idx]
            else:
                ws[cell_ref_bs] = ""
            ws[cell_ref_bs].alignment = Alignment(wrap_text=True)

        for field_name, r_info in item_rules.items():
            col_letter = r_info.get("col", "").strip().upper()
            rule_type_raw = str(r_info.get("type", "PDF Row Item")).strip()
            rule_val = str(r_info.get("rule", "")).strip()
            
            if not col_letter or col_letter in ["V", "BR", "BS", "S", "J"]:
                continue
                
            if "extract" in rule_type_raw.lower() or "box" in rule_type_raw.lower() or "header" in rule_type_raw.lower() or col_letter in ["BW", "BY"]:
                extracted_val = extract_header_value(pdf_lines, pdf_text, rule_val, "📦 Extract Inside Box (डब्बे के अंदर का टेक्स्ट)", "Exact Word", "", "None", field_label=field_name)
                if not extracted_val or not extracted_val.strip():
                    extracted_val = extract_header_value(pdf_lines, pdf_text, rule_val, "Right (आगे)", "Exact Word", "", "None", field_label=field_name)
                
                if extracted_val and "\n" in str(extracted_val):
                    lines = [l.strip() for l in str(extracted_val).split("\n") if l.strip()]
                    ws[f"{col_letter}{curr_row}"] = lines[item_idx] if item_idx < len(lines) else ""
                else:
                    ws[f"{col_letter}{curr_row}"] = extracted_val if item_idx == 0 else ""

        for field_name, r_info in item_rules.items():
            col_letter = r_info.get("col", "").strip().upper()
            rule_type_raw = str(r_info.get("type", "PDF Row Item")).strip()
            rule_val = str(r_info.get("rule", "")).strip()
            rule_val_lower = rule_val.lower()
            
            if not col_letter or col_letter in ["V", "I", "J", "G", "BR", "BS", "M"]:
                continue
            
            if "extract" in rule_type_raw.lower() or "box" in rule_type_raw.lower() or "header" in rule_type_raw.lower():
                continue
                
            cell_ref = f"{col_letter}{curr_row}"
            raw_val = ""
            clean_rule_val = rule_val_lower.replace("col_", "").strip()
            
            if clean_rule_val.isdigit() and col_letter in ["K", "S", "BU"]:
                col_idx = int(clean_rule_val)
                raw_val = item.get(f"col_{col_idx}", "")
                if col_letter == "S":
                    raw_val = f"{raw_val}B" if raw_val and not str(raw_val).upper().endswith("B") else raw_val

            elif col_letter == "K" or "hs" in rule_val_lower or "ritc" in rule_val_lower:
                raw_val = item.get("hs_code", "")
            elif col_letter == "BU" or "desc" in rule_val_lower:
                raw_val = item.get("description_text", "")
            elif col_letter == "S" or "dbk" in rule_val_lower:
                dbk = item.get("dbk_sr", "")
                raw_val = f"{dbk}B" if dbk and not dbk.upper().endswith("B") else dbk
            elif col_letter == "AB" or "wt" in rule_val_lower or "weight" in rule_val_lower:
                raw_val = item.get("net_wt", "")
            elif col_letter == "N" or "qty" in rule_val_lower or "quantity" in rule_val_lower:
                raw_val = item.get("qty", "")
            elif col_letter == "P" or "rate" in rule_val_lower:
                raw_val = item.get("rate", "")
            elif col_letter == "Q" or ("amount" in rule_val_lower and "usd" in rule_val_lower):
                raw_val = item.get("amount_usd", "")
            elif col_letter == "W" or "taxable" in rule_val_lower or "inr" in rule_val_lower:
                raw_val = item.get("amount_inr", "")
            elif col_letter == "X" or "igst%" in rule_val_lower or "igst per" in rule_val_lower:
                raw_val = item.get("igst_per", "5.00")
            elif col_letter == "Y" or "igst amount" in rule_val_lower:
                raw_val = item.get("igst_amt", "")
            elif col_letter == "Z" or "sqmtr" in rule_val_lower:
                raw_val = item.get("sqmtr", "")
            else:
                if clean_rule_val.isdigit():
                    col_idx = int(clean_rule_val)
                    raw_val = item.get(f"col_{col_idx}", "")

            if "=" in rule_val:
                raw_val = apply_value_replacement(str(raw_val), rule_val)

            try:
                if col_letter in ["S", "K", "BU"]:
                    ws[cell_ref] = str(raw_val).replace("\n", " ")
                else:
                    clean_num = str(raw_val).replace(",", "").replace("\n", "").strip()
                    ws[cell_ref] = float(clean_num) if clean_num else 0.0
            except:
                ws[cell_ref] = raw_val
                    
        curr_row += 1
        overall_sr += 1
        
    return ws, overall_sr, curr_row