Solapur_Comparison/main.py

from flask import Flask, request, render_template
from sqlalchemy import create_engine
import pandas as pd

app = Flask(__name__)

# ---------------------- SQL CONNECTION -------------------------
engine = create_engine("mysql+pymysql://root:tiger@localhost/solapur_db")


# ---------------------- COLUMN MAPPING -------------------------
contractor_map = {
    'Location': 'location',

    'ID of Manhole ': 'id_of_mh',
    'Ex. dia of MH Excavation    ': 'ex_dia_excavation',
    'Area of MH Excavation ': 'area_excavation',

    'Soft Murum/\n0 to 1.5': 'sm_0_1_5',
    'Soft Murum/\n1.5 to 3.0': 'sm_1_5_3_0',
    'Soft Murum/\n3.0 to 4.0': 'sm_3_0_4_0',

    'Hard Murum/WBM\n0 to 1.5': 'hm_0_1_5',
    'Hard Murum/WBM\n1.5 to 3.0': 'hm_1_5_3_0',

    'Soft Rock\n0 to 1.5': 'sr_0_1_5',
    'Soft Rock\n1.5 to 3.0': 'sr_1_5_3_0',

    'Hard Rock\n0 to 1.5': 'hr_0_1_5',
    'Hard Rock\n1.5 to 3.0': 'hr_1_5_3_0',
    'Hard Rock\n3.0 to 4.50': 'hr_3_0_4_5',
    'Hard Rock\n4.5 to 6.0': 'hr_4_5_6_0',
    'Hard Rock\n6.0 To 7.5': 'hr_6_0_7_5',

    'Soft Murum/Soil\n0 to 1.5': 'sms_0_1_5',
    'Soft Murum/Soil\n1.5 to 3.0': 'sms_1_5_3_0',
    'Soft Murum/Soil\n3.0 to 4.0': 'sms_3_0_4_0',

    'Hard Murum/WBM\n0 to 1.5.1': 'hmwbm_0_1_5',
    'Hard Murum/WBM\n1.5 & Above': 'hmwbm_1_5_above',

    'Soft Rock\n0 to 1.5.1': 'sr2_0_1_5',
    'Soft Rock\n1.5 & Above': 'sr2_1_5_above',

    'Hard Rock\n0 to 1.5.1': 'hr2_0_1_5',
    'Hard Rock\n1.5 & Above': 'hr2_1_5_above',
    'Hard Rock\n3.0 to 4.50.1': 'hr2_3_0_4_5',
    'Hard Rock\n4.5 to 6': 'hr2_4_5_6',
    'Hard Rock\n6.0 To 7.5.1': 'hr2_6_0_7_5',

    'Total': 'total'
}

client_map = {
    'Location': 'location',
    ' MH NOS': 'mh_nos',

    'GL                 (m)': 'gl',
    'MH Invert Level (m)': 'mh_invert_level',
    'MH Top Level (m)': 'mh_top_level',
    'Ex.Level (m)': 'ex_level',
    'MH Cutting Depth              (m)': 'mh_cutting_depth',
    'MH Depth': 'mh_depth',
    'Inner dia of MH (m)': 'inner_dia',
    'Dia of MH Cutting (m)': 'cut_dia',
    'Area of Ex. MH (Sqm)': 'area_exc_mh',

    'Marshi / Muddy / Slushy\n 0 to \n1.5 m': 'mms_0_1_5',
    'Marshi / Muddy / Slushy\n1.5 to 3.0 m': 'mms_1_5_3_0',
    'Marshi / Muddy / Slushy\n3.0 to 4.5m': 'mms_3_0_4_5',

    ' Soil / Soft Murum  \n0 to 1.5 m': 'ssm_0_1_5',
    'Soil / Soft Murum  \n1.5 to 3.0 m': 'ssm_1_5_3_0',
    'Soil / Soft Murum  \n3.0 to 4.5m': 'ssm_3_0_4_5',

    ' HM & WBM\n0 to 1.5 m': 'hmwbm_0_1_5',
    'HM & WBM\n1.5 to 3.0 m': 'hmwbm_1_5_3_0',
    'HM & WBM\n3.0 to 4.5m': 'hmwbm_3_0_4_5',

    ' Soft Rock/Asphalt Rd \n0 to 1.5 m': 'srar_0_1_5',
    ' Soft Rock/Asphalt Rd\n1.5 to 3.0 m': 'srar_1_5_3_0',
    ' Soft Rock/Asphalt Rd\n3.0 to 4.5m': 'srar_3_0_4_5',

    ' Hard Rock \n0 to 1.5 m': 'hr_0_1_5',
    'Hard Rock \n1.5 to 3.0 m': 'hr_1_5_3_0',
    'Hard Rock \n3.0 to 4.5 m': 'hr_3_0_4_5',
    'Hard Rock \n4.5 to 6 m': 'hr_4_5_6',
    'Hard Rock 6.0 to \n7.5m': 'hr_6_0_7_5',

    # SECOND SET
    'Marshi / Muddy / Slushy 0 to \n1.5 m': 'mms2_0_1_5',
    'Marshi / Muddy / Slushy\n1.5 to 3.0 m.1': 'mms2_1_5_3_0',
    'Marshi / Muddy / Slushy\n3.0 to 4.5m.1': 'mms2_3_0_4_5',

    ' Soil / Soft Murum    \n0 to 1.5 m': 'ssm2_0_1_5',
    'Soil / Soft Murum    \n1.5 to 3.0 m': 'ssm2_1_5_3_0',
    'Soil / Soft Murum    3.0 to 4.5m': 'ssm2_3_0_4_5',

    ' HM & WBM\n0 to 1.5 m.1': 'hmwbm2_0_1_5',
    'HM & WBM\n1.5 to 3.0 m.1': 'hmwbm2_1_5_3_0',
    'HM & WBM\n3.0 to 4.5m.1': 'hmwbm2_3_0_4_5',

    ' Soft Rock/Asphalt Rd \n0 to 1.5 m.1': 'srar2_0_1_5',
    'Soft Rock/Asphalt Rd \n1.5 to 3.0 m': 'srar2_1_5_3_0',
    'Soft Rock/Asphalt Rd \n3.0 to 4.5m': 'srar2_3_0_4_5',

    ' Hard Rock \n0 to 1.5 m.1': 'hr2_0_1_5',
    'Hard Rock\n1.5 to 3.0 m': 'hr2_1_5_3_0',
    'Hard Rock\n3.0 to 4.5 m': 'hr2_3_0_4_5',
    'Hard Rock\n4.5 to 6 m': 'hr2_4_5_6',
    'Hard Rock\n6.0 to \n7.5m': 'hr2_6_0_7_5'
}
@app.route("/", methods=["GET", "POST"])
def index():
     return render_template("upload_excel.html")

# ---------------------- UPLOAD ROUTE -------------------------
@app.route("/upload_excel", methods=["GET", "POST"])
def upload_excel():
    if request.method == "POST":
        file = request.files["file"]

        if not file:
            return "No file uploaded!", 400

        # Read both sheets
        df1 = pd.read_excel(file, sheet_name=0)
        df2 = pd.read_excel(file, sheet_name=1)

        print("\n---- SHEET 1 COLUMNS ----")
        print(df1.columns.tolist())
        print("\n---- SHEET 2 COLUMNS ----")
        print(df2.columns.tolist())

        # Rename using mapping
        df1 = df1.rename(columns=contractor_map)
        df2 = df2.rename(columns=client_map)

        # Keep only mapped (known) columns (ignore if some mapped names don't exist)
        df1 = df1[[c for c in contractor_map.values() if c in df1.columns]]
        df2 = df2[[c for c in client_map.values() if c in df2.columns]]

        # Fill empty numeric cells with 0
        df1 = df1.fillna(0)
        df2 = df2.fillna(0)

        # ------------------- DIFFERENCE CALCULATION (EXACT COLUMNS) -------------------
        # Excel formula: SUM(sheet2 AC:AS) - SUM(sheet1 Q:AB)
        # Map those ranges to the cleaned column names (these lists reflect that mapping)

        contractor_cols = [
            
            'sms_0_1_5','sms_1_5_3_0','sms_3_0_4_0',
            'hmwbm_0_1_5','hmwbm_1_5_above','sr2_0_1_5','sr2_1_5_above',
            'hr2_0_1_5','hr2_1_5_above','hr2_3_0_4_5','hr2_4_5_6','hr2_6_0_7_5'
        ]

        client_cols = [
            
            'mms2_0_1_5','mms2_1_5_3_0','mms2_3_0_4_5',
            'ssm2_0_1_5','ssm2_1_5_3_0','ssm2_3_0_4_5',
            'hmwbm2_0_1_5','hmwbm2_1_5_3_0','hmwbm2_3_0_4_5',
            'srar2_0_1_5','srar2_1_5_3_0','srar2_3_0_4_5',
            'hr2_0_1_5','hr2_1_5_3_0','hr2_3_0_4_5','hr2_4_5_6','hr2_6_0_7_5'
        ]

        # For safety, only use columns that exist in the DataFrames — missing ones treated as zeros
        contractor_present = [c for c in contractor_cols if c in df1.columns]
        client_present = [c for c in client_cols if c in df2.columns]

        # Add any missing columns with zeros so sums align (keeps row alignment)
        for c in contractor_cols:
            if c not in df1.columns:
                df1[c] = 0.0
        for c in client_cols:
            if c not in df2.columns:
                df2[c] = 0.0

        # Now compute difference exactly like your Excel formula
        df_difference = pd.DataFrame()
        # If location exists in df1 use it, else try df2, else blank
        if 'location' in df1.columns:
            df_difference["location"] = df1["location"]
        elif 'location' in df2.columns:
            df_difference["location"] = df2["location"]
        else:
            df_difference["location"] = ""
        print("Contractor columns being summed:", [c for c in contractor_cols if c in df1.columns])
        print("Client columns being summed:", [c for c in client_cols if c in df2.columns])
        print("Contractor sum per row:")
        print(df1[contractor_cols].sum(axis=1).round(2))

        print("Client sum per row:")
        print(df2[client_cols].sum(axis=1).round(2))

        df_difference["difference"] = df2[client_cols].sum(axis=1) - df1[contractor_cols].sum(axis=1)

        # ---------------- INSERT INTO DATABASE -----------------------
        df1.to_sql("mh_exc_contractor", con=engine, if_exists="append", index=False)
        df2.to_sql("mh_exc_client", con=engine, if_exists="append", index=False)
        df_difference.to_sql("mh_exc_difference", con=engine, if_exists="append", index=False)

        return "Excel uploaded and ALL tables updated successfully!"

    return render_template("upload_excel.html")


# ---------------------- RUN FLASK APP -------------------------
if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5000, debug=True)