211 lines
7.7 KiB
Python
211 lines
7.7 KiB
Python
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)
|