214 lines
8.5 KiB
Python
214 lines
8.5 KiB
Python
import os
|
|
import pandas as pd
|
|
from flask import request, redirect, url_for, current_app
|
|
from flask_login import current_user
|
|
from app import db
|
|
from app.models import Task, WorkDetail
|
|
from datetime import datetime
|
|
from app.service.logger import log_activity
|
|
# keep helper inside controller
|
|
def to_2_decimal(value):
|
|
try:
|
|
if value is None or value == "":
|
|
return None
|
|
return round(float(value), 2)
|
|
except (TypeError, ValueError):
|
|
return None
|
|
|
|
|
|
def upload_controller():
|
|
if 'file' not in request.files:
|
|
return "No file part"
|
|
|
|
file = request.files['file']
|
|
|
|
if file.filename == '':
|
|
return "No selected file"
|
|
|
|
filepath = os.path.join(current_app.config['UPLOAD_FOLDER'], file.filename)
|
|
file.save(filepath)
|
|
|
|
log_activity(current_user.username, "File Upload", f"Uploaded file: {file.filename}")
|
|
|
|
work_details_data = pd.read_excel(filepath, nrows=11, header=None, dtype=str)
|
|
|
|
work_details_dict = {
|
|
"name_of_work": work_details_data.iloc[0, 1],
|
|
"cover_agreement_no": work_details_data.iloc[1, 1],
|
|
"name_of_contractor": work_details_data.iloc[2, 1],
|
|
"name_of_tpi_agency": work_details_data.iloc[3, 1],
|
|
"name_of_division": work_details_data.iloc[4, 1],
|
|
"name_of_village": work_details_data.iloc[5, 1],
|
|
"block": work_details_data.iloc[6, 1],
|
|
"scheme_id": work_details_data.iloc[7, 1],
|
|
"date_of_billing": work_details_data.iloc[8, 1],
|
|
"measurement_book": work_details_data.iloc[9, 1],
|
|
"district": work_details_data.iloc[10, 1]
|
|
}
|
|
|
|
work_details_dict = {k: (None if pd.isna(v) else v) for k, v in work_details_dict.items()}
|
|
|
|
work_detail = WorkDetail(**work_details_dict)
|
|
db.session.add(work_detail)
|
|
|
|
data = pd.read_excel(filepath, skiprows=10)
|
|
data = data.astype(object).where(pd.notna(data), None)
|
|
|
|
expected_columns = [
|
|
"serial_number", "task_name", "unit", "qty", "rate", "boq_amount",
|
|
"previous_billed_qty", "previous_billing_amount",
|
|
"in_this_ra_bill_qty", "in_this_ra_billing_amount",
|
|
"cumulative_billed_qty", "cumulative_billed_amount",
|
|
"variation_qty", "variation_amount", "remark"
|
|
]
|
|
|
|
if data.shape[1] == len(expected_columns):
|
|
data.columns = expected_columns
|
|
else:
|
|
data.columns = expected_columns[:data.shape[1]]
|
|
|
|
current_main_task_serial = None
|
|
current_main_task_name = None
|
|
|
|
for _, row in data.iterrows():
|
|
|
|
task_name = str(row["task_name"]) if row["task_name"] else ""
|
|
serial_number = str(row["serial_number"]) if row["serial_number"] else None
|
|
|
|
if serial_number:
|
|
current_main_task_serial = serial_number
|
|
current_main_task_name = task_name
|
|
parent_id = None
|
|
else:
|
|
parent_id = current_main_task_serial
|
|
|
|
task = Task(
|
|
district=work_details_dict.get("district"),
|
|
block_name=work_details_dict["block"],
|
|
village_name=work_details_dict["name_of_village"],
|
|
serial_number=serial_number,
|
|
task_name=task_name,
|
|
unit=row["unit"],
|
|
qty=to_2_decimal(row["qty"]),
|
|
rate=to_2_decimal(row["rate"]),
|
|
boq_amount=to_2_decimal(row["boq_amount"]),
|
|
previous_billed_qty=to_2_decimal(row["previous_billed_qty"]),
|
|
previous_billing_amount=to_2_decimal(row["previous_billing_amount"]),
|
|
in_this_ra_bill_qty=to_2_decimal(row["in_this_ra_bill_qty"]),
|
|
in_this_ra_billing_amount=to_2_decimal(row["in_this_ra_billing_amount"]),
|
|
cumulative_billed_qty=to_2_decimal(row["cumulative_billed_qty"]),
|
|
cumulative_billed_amount=to_2_decimal(row["cumulative_billed_amount"]),
|
|
variation_qty=to_2_decimal(row["variation_qty"]),
|
|
variation_amount=to_2_decimal(row["variation_amount"]),
|
|
parent_id=parent_id,
|
|
parent_task_name=current_main_task_name if not serial_number else None,
|
|
remark=row["remark"]
|
|
)
|
|
|
|
db.session.add(task)
|
|
|
|
db.session.commit()
|
|
|
|
log_activity(
|
|
current_user.username,
|
|
"Database Insert",
|
|
f"Inserted work details and tasks from {file.filename}"
|
|
)
|
|
|
|
return redirect(url_for('main.display_tasks'))
|
|
|
|
|
|
# # File upload route
|
|
# @main.route('/upload', methods=['POST'])
|
|
# @login_required
|
|
# def upload():
|
|
# if 'file' not in request.files:
|
|
# return "No file part"
|
|
# file = request.files['file']
|
|
# if file.filename == '':
|
|
# return "No selected file"
|
|
# if file:
|
|
# filepath = os.path.join(current_app.config['UPLOAD_FOLDER'], file.filename)
|
|
# file.save(filepath)
|
|
# log_activity(current_user.username, "File Upload", f"Uploaded file: {file.filename}")
|
|
|
|
# # Read work details (first 11 rows)
|
|
# work_details_data = pd.read_excel(filepath, nrows=11, header=None)
|
|
# work_details_dict = {
|
|
|
|
# "name_of_work": work_details_data.iloc[0, 1],
|
|
# "cover_agreement_no": work_details_data.iloc[1, 1],
|
|
# "name_of_contractor": work_details_data.iloc[2, 1],
|
|
# "name_of_tpi_agency": work_details_data.iloc[3, 1],
|
|
# "name_of_division": work_details_data.iloc[4, 1],
|
|
# "name_of_village": work_details_data.iloc[5, 1],
|
|
# "block": work_details_data.iloc[6, 1],
|
|
# "scheme_id": work_details_data.iloc[7, 1],
|
|
# "date_of_billing": work_details_data.iloc[8, 1],
|
|
# "measurement_book": work_details_data.iloc[9, 1],
|
|
# "district": work_details_data.iloc[10, 1] # Example: row 11 (index 10), column 2 (index 1)
|
|
|
|
# }
|
|
|
|
# work_details_dict = {key: (None if pd.isna(value) else value) for key, value in work_details_dict.items()}
|
|
# work_detail = WorkDetail(**work_details_dict)
|
|
# db.session.add(work_detail)
|
|
|
|
# # Read task data starting from row 12
|
|
# data = pd.read_excel(filepath, skiprows=10)
|
|
# data = data.astype(str).replace({"nan": None, "NaT": None, "None": None})
|
|
|
|
# expected_columns = [
|
|
# "serial_number", "task_name", "unit", "qty", "rate", "boq_amount",
|
|
# "previous_billed_qty", "previous_billing_amount",
|
|
# "in_this_ra_bill_qty", "in_this_ra_billing_amount",
|
|
# "cumulative_billed_qty", "cumulative_billed_amount",
|
|
# "variation_qty", "variation_amount", "remark"
|
|
# ]
|
|
|
|
# if data.shape[1] == len(expected_columns):
|
|
# data.columns = expected_columns
|
|
# else:
|
|
# data.columns = expected_columns[:data.shape[1]] # Truncate
|
|
|
|
# current_main_task_serial = None
|
|
# current_main_task_name = None
|
|
|
|
# for _, row in data.iterrows():
|
|
# task_name = str(row["task_name"]) if row["task_name"] else ""
|
|
# serial_number = row["serial_number"]
|
|
|
|
# if serial_number: # Main task
|
|
# current_main_task_serial = serial_number
|
|
# current_main_task_name = task_name
|
|
# parent_id = None
|
|
# else: # Subtask
|
|
# parent_id = current_main_task_serial
|
|
|
|
# task = Task(
|
|
# district=work_details_dict.get("district"),
|
|
# block_name=work_details_dict["block"],
|
|
# village_name=work_details_dict["name_of_village"],
|
|
# serial_number=serial_number,
|
|
# task_name=task_name,
|
|
# unit=row["unit"],
|
|
# qty=row["qty"],
|
|
# rate=row["rate"],
|
|
# boq_amount=row["boq_amount"],
|
|
# previous_billed_qty=row["previous_billed_qty"],
|
|
# previous_billing_amount=row["previous_billing_amount"],
|
|
# in_this_ra_bill_qty=row["in_this_ra_bill_qty"],
|
|
# in_this_ra_billing_amount=row["in_this_ra_billing_amount"],
|
|
# cumulative_billed_qty=row["cumulative_billed_qty"],
|
|
# cumulative_billed_amount=row["cumulative_billed_amount"],
|
|
# variation_qty=row["variation_qty"],
|
|
# variation_amount=row["variation_amount"],
|
|
# parent_id=parent_id,
|
|
# parent_task_name=current_main_task_name if not serial_number else None,
|
|
# remark=row["remark"]
|
|
# )
|
|
# db.session.add(task)
|
|
|
|
# db.session.commit()
|
|
# log_activity(current_user.username, "Database Insert", f"Inserted work details and tasks from {file.filename}")
|
|
# return redirect(url_for('main.display_tasks')) |