Files
Shubhankar45 4e5e95775d --
2026-10-27 12:03:31 +00:00

115 lines
6.0 KiB
Python

"""Import eligible root-level CSV files into their form tables.
Usage: python -m scripts.import_csv_data [--only-empty]
Without a flag every form table is REPLACED by its CSV. ``--only-empty`` is the safe
mode for a database that already holds work: a table that has any rows is left
untouched, and only empty tables are loaded (e.g. the product lists).
"""
import argparse
import asyncio
import csv
import uuid
from pathlib import Path
from sqlalchemy import text
from app.database.session import AsyncSessionLocal
ROOT = Path(__file__).resolve().parent.parent
EXCLUDED_FILES = {"stock.csv"}
CSV_TABLES = {
"Weekly_Report.csv": ("weekly_report", ["employee_name", "report_date", "headquarter", "month", "no_of_days_worked", "no_of_doctor_called", "no_of_chemist_called", "pob_booked", "secondary_sales", "pob_objective", "secondary_objective", "product_offer_scheme_booked", "product_offer_scheme_objective"]),
"FIELD_ACTIVITY_REPORT.csv": ("field_activity_report", ["name", "headquarter", "month", "report_date", "station_worked", "no_of_doctors_called", "no_of_chemists_pds_called", "doctor_call_avg", "chemist_call_avg"]),
"Fare_Chart.csv": ("fare_chart", ["name", "headquarter", "serial_no", "report_date", "from_location", "to_location", "kilometers_km"]),
"Daily_Call_Report.csv": ("daily_call_report_csv", ["name", "headquarter", "report_date", "month", "doctor_sr_no", "doctor_name", "qualification", "product_promote", "product_sample", "remarks", "chemist_sr_no", "chemist_name", "pob", "stockist_visit", "order_value"]),
"CORE_Dr._CALL_CONVERSION.csv": ("core_dr_call_conversion", ["name", "headquarter", "month", "doctor_name", "date_of_visit", "product_targeted", "status"]),
"AREA_COVERED.csv": ("area_covered", ["name", "headquarter", "month", "sale_status", "area_to_cover", "sale_objective", "status"]),
"Sales_and_Stock_Statement.csv": ("sales_and_stock_statement", ["name", "headquarter", "report_date", "month", "serial_no", "product_name", "opening", "receipt", "sale", "closing", "value_in_pts"]),
"TOUR_PLAN.csv": ("tour_plan", ["name", "headquarter", "serial_no", "report_date", "from_location", "to_location", "pob", "stockiest_visit"]),
"STANDARD_TOUR_PLAN.csv": ("standard_tour_plan", ["name", "headquarter", "serial_no", "place"]),
"sales_projection.csv": ("sales_projection", ["name", "headquarter", "report_date", "month", "serial_no", "product_name", "pts", "closing_qty", "closing_value", "projection_qty", "projection_value"]),
}
SERIAL_COLUMNS = {"serial_no", "doctor_sr_no", "chemist_sr_no"}
def read_rows(path: Path, columns: list[str]) -> list[tuple[int, dict[str, str]]]:
with path.open(newline="", encoding="utf-8-sig") as file:
reader = csv.reader(file)
next(reader, None)
rows: list[tuple[int, dict[str, str]]] = []
for row in reader:
data = {
column: (row[index] if index < len(row) else "").strip()
for index, column in enumerate(columns)
}
if any(value for column, value in data.items() if column not in SERIAL_COLUMNS):
rows.append((len(rows) + 1, data))
return rows
async def apply_sales_projection_account(db) -> None:
await db.execute(
text(
'''UPDATE "sales_projection" AS projection
SET name = account.full_name,
headquarter = headquarters.name
FROM "user" AS account
LEFT JOIN headquarters ON headquarters.id = account.headquarters_id
WHERE account.id = projection.owner_user_id '''
)
)
async def import_csv_data(only_empty: bool = False) -> None:
csv_files = sorted(path for path in ROOT.glob("*.csv") if path.name not in EXCLUDED_FILES)
async with AsyncSessionLocal() as db:
imported_rows = 0
for path in csv_files:
table_name, columns = CSV_TABLES[path.name]
if only_empty:
count = await db.execute(text(f'SELECT COUNT(*) FROM "{table_name}"'))
existing = count.scalar_one()
if existing:
print(f"{table_name}: skipped, already has {existing} rows")
continue
else:
await db.execute(text(f'DELETE FROM "{table_name}"'))
# The Sales Projection CSV is the product list: only Sl. No., product and PTS come
# from it; every figure is filled in later, per month, on the form.
source_columns = ["serial_no", "product_name", "pts"] if table_name == "sales_projection" else columns
rows = read_rows(path, source_columns)
if table_name == "sales_projection":
rows = [(row_number, {**{c: "" for c in columns}, **data}) for row_number, data in rows]
if rows:
column_names = ", ".join(f'"{column}"' for column in columns)
parameters = ", ".join(f":{column}" for column in columns)
statement = text(
f'INSERT INTO "{table_name}" '
f'(id, source_row_number, {column_names}) '
f'VALUES (:id, :source_row_number, {parameters})'
)
await db.execute(
statement,
[
{"id": uuid.uuid4(), "source_row_number": row_number, **data}
for row_number, data in rows
],
)
imported_rows += len(rows)
print(f"{table_name}: {len(rows)} rows")
if table_name == "sales_projection":
await apply_sales_projection_account(db)
await db.commit()
print(f"Imported {imported_rows} rows from {len(csv_files)} CSV files.")
if __name__ == "__main__":
parser = argparse.ArgumentParser(description=__doc__.splitlines()[0])
parser.add_argument(
"--only-empty",
action="store_true",
help="load only tables that have no rows; never delete existing data",
)
asyncio.run(import_csv_data(parser.parse_args().only_empty))