app.py

import gradio as gr
import pandas as pd
from datetime import datetime
import pytz

# Updated Gradio app for YunExpress with new header mappings and hyperlinks

def process_file(file):
    file_name = file.name.lower()
    try:
        if file_name.endswith(('.xls', '.xlsx', '.xlsm')):
            # Read data from the "YunExpress" sheet
            df = pd.read_excel(file.name, sheet_name="YunExpress")
        else:
            return f"Unsupported file format: {file_name}", None
    except Exception as e:
        return f"Error reading file: {e}", None

    # Output headers
    output_headers = [
        "CustomerOrderNo.", "RoutingCode", "Trackingnumber", "AdditionalServices",
        "ShipmentProtectionPlusService", "SignatureService", "VatNumber", "EoriNumber",
        "IossCode", "CountryCode", "Name", "CertificateCode", "Company", "Street", "City",
        "Province/State", "ZipCode", "phone", "HouseNumber", "Email", "PackageNumber",
        "PackageWeight", "SenderFiastName", "SenderCompany", "SenderStreet", "SenderCity",
        "SenderProvince", "SenderPostalCode", "SenderCountry", "SenderTelephone", "SenderEmail",
        "SenderUSCI", "PlatformName", "PlatformProvince", "PlatformAddress", "PlatformPostalCode",
        "PlatformPhoneNumber", "PlatformEmail", "SalesPlatformLink", "CurrencyCode", "SKU1",
        "ItemDescription1", "ForeignItemDescription1", "DeclaredQuantity1", "UnitPrice1",
        "UnitWeight1", "HsCode1", "Remarks1", "SalesLink1", "Materials1", "Use1", "Brand1",
        "ModelType1", "Specs1", "FabricCreationMethod1", "ManufacturerID1", "ManufacturerName1",
        "ManufacturerCountry1", "ManufacturerState1", "ManufacturerCity1", "ManufacturerPostalCode1",
        "ManufacturerAddress1", "CargoCategory", "PaymentPlatform", "PaymentAccount",
        "PaymentTransactionNumber"
    ]

    output_df = pd.DataFrame("", index=range(len(df)), columns=output_headers)

    # 1. Order Number → CustomerOrderNo. (prefix "LB")
    if "Order Number" in df.columns:
        output_df["CustomerOrderNo."] = "LB" + df["Order Number"].astype(str)

    # 2. Name
    if {"Shipping First Name","Shipping Last Name","Shipping Company"}.issubset(df.columns):
        first = df["Shipping First Name"].fillna("").astype(str).str.strip()
        last  = df["Shipping Last Name"].fillna("").astype(str).str.strip()
        comp  = df["Shipping Company"].fillna("").astype(str).str.strip()
        output_df["Name"] = (first + " " + last + " " + comp).str.strip()

    # 3. Street
    addr1 = df.get("Shipping Address 1", pd.Series([""]*len(df))).fillna("").astype(str).str.strip()
    addr2 = df.get("Shipping Address 2", pd.Series([""]*len(df))).fillna("").astype(str).str.strip()
    output_df["Street"] = (addr1 + " " + addr2).str.strip()

    # 4. City
    if "Shipping City" in df.columns:
        output_df["City"] = df["Shipping City"]

    # 5. Province/State
    if "Shipping Province" in df.columns:
        output_df["Province/State"] = df["Shipping Province"]

    # 6. ZipCode
    if "Shipping ZIP" in df.columns:
        output_df["ZipCode"] = df["Shipping ZIP"].astype(str)

    # 7. CountryCode
    if "Shipping Country Code" in df.columns:
        output_df["CountryCode"] = df["Shipping Country Code"]

    # 8. Pad US 4-digit Zips with leading apostrophe
    zip_str = output_df["ZipCode"].fillna("")
    mask_us = (output_df.get("CountryCode","") == "US") & zip_str.str.match(r'^\d{4}$')
    output_df.loc[mask_us, "ZipCode"] = "'" + zip_str.str.zfill(5)

    # 9. phone
    if "Shipping Address Phone" in df.columns:
        output_df["phone"] = df["Shipping Address Phone"]

    # 10. PackageWeight (kg)
    if "Total Weight" in df.columns:
        output_df["PackageWeight"] = df["Total Weight"] / 1000

    # 11. DeclaredQuantity1
    if "Order Number" in df.columns and "Quantity" in df.columns:
        output_df["DeclaredQuantity1"] = df.groupby("Order Number")["Quantity"].transform("sum")

    # 12. Defaults & RoutingCode
    mask = output_df["CustomerOrderNo."].astype(bool)
    mask_no = mask & (output_df["CountryCode"] == "NO")
    mask_oth= mask & (output_df["CountryCode"] != "NO")
    output_df.loc[mask_oth, "RoutingCode"] = "HKTHZXR"
    output_df.loc[mask_no,  "RoutingCode"] = "HK-ASS-PF"

    defaults = {
        "UnitPrice1":2, "CurrencyCode":"USD",
        "ItemDescription1":"Eye Cosmetic Accessories",
        "UnitWeight1":0.02,
        "ForeignItemDescription1":"Eye Cosmetic Accessories"
    }
    for col,val in defaults.items():
        output_df.loc[mask, col] = val

    # 13. AdditionalServices for EU
    EU = {"AT","BE","BG","CY","CZ","DE","DK","EE","ES","FI",
          "FR","HR","HU","IE","IT","LT","LU","LV","MT","NL",
          "PL","PT","RO","SE","SI","SK","GR"}
    mask_eu = mask & output_df["CountryCode"].isin(EU)
    output_df.loc[mask_eu, "AdditionalServices"] = "v1"

    # Remove duplicates
    output_df = output_df.drop_duplicates(subset=["CustomerOrderNo."], keep="first")

    # Save file
    hk = pytz.timezone("Asia/Hong_Kong")
    date = datetime.now(hk).strftime("%y%m%d")
    name = f"yunexpress {date}.xlsx"
    output_df.to_excel(name, index=False)
    return output_df, name

# Interface
demo = gr.Blocks()
with demo:
    gr.Markdown("# Shipping - YunExpress")
    file_input = gr.File(label="Upload Excel File with a YunExpress sheet")
    btn = gr.Button("Process Data")
    out_table = gr.DataFrame()
    out_file  = gr.File(label="Download Processed File")
    btn.click(process_file, [file_input], [out_table, out_file])
    gr.HTML(
        """
        <div style="text-align:center;font-size:16px;margin-top:20px;">
            <h3>Shipping Tools</h3>
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-dhl-e-commerce">DHL</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-ec-ship">EC-Ship</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-fedex">Fedex</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-UPS">UPS</a><br>
            <a href="https://huggingface.co/spaces/leadingbridge/shipping-yunexpress">Yunexpress</a>
        </div>
        <div style="text-align:center;font-size:16px;margin-top:20px;">
            <h3>Administration Tools</h3>
            <a href="https://huggingface.co/spaces/leadingbridge/email-template">Email Template</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/product-feed">Google Merchant</a> | 
            <a href="https://huggingface.co/spaces/leadingbridge/tss-order">Order Processing</a>
        </div>
        """
    )

demo.launch()