70 lines
2.5 KiB
Python
70 lines
2.5 KiB
Python
import click
|
|
import pandas as pd
|
|
from datetime import datetime
|
|
from flask.cli import with_appcontext
|
|
from salary_analytics.app.extensions import db
|
|
from salary_analytics.app.models import RawTransaction
|
|
|
|
@click.group()
|
|
def commands():
|
|
"""Management commands for the salary analytics application."""
|
|
pass
|
|
|
|
@commands.command('upload-xls')
|
|
@click.argument('xls_path')
|
|
@with_appcontext
|
|
def upload_xls_cli(xls_path):
|
|
"""Uploads data from an XLS file to the analytics_raw_transactions table.
|
|
|
|
Args:
|
|
xls_path (str): The path to the XLS file.
|
|
"""
|
|
print(f"Attempting to upload data from {xls_path}...")
|
|
try:
|
|
df = pd.read_excel(xls_path, dtype=str)
|
|
|
|
# Convert date columns to datetime
|
|
date_cols = ["ENTRY_DATE", "VALUE_DATE", "PSTD_DATE", "TRAN_DATE"]
|
|
for col in date_cols:
|
|
if col in df.columns:
|
|
df[col] = pd.to_datetime(df[col], errors='coerce')
|
|
df[col] = df[col].fillna(pd.Timestamp.now())
|
|
|
|
# Convert numeric columns
|
|
for col in ["TRAN_AMT", "BALANCE"]:
|
|
if col in df.columns:
|
|
df[col] = pd.to_numeric(df[col].str.replace(",", ""), errors='coerce')
|
|
|
|
# Truncate TRAN_PARTICULAR to 100 chars
|
|
if "TRAN_PARTICULAR" in df.columns:
|
|
df["TRAN_PARTICULAR"] = df["TRAN_PARTICULAR"].astype(str).str.slice(0, 100)
|
|
|
|
# Prepare data for insertion into the database
|
|
records = []
|
|
for index, row in df.iterrows():
|
|
record = RawTransaction(
|
|
cust_id=row.get('CUST_ID'),
|
|
accountid=row.get('ACCOUNTID'),
|
|
tran_id=row.get('TRAN_ID'),
|
|
entry_date=row.get('ENTRY_DATE'),
|
|
value_date=row.get('VALUE_DATE'),
|
|
pstd_date=row.get('PSTD_DATE'),
|
|
tran_date=row.get('TRAN_DATE'),
|
|
tran_sub_ty=row.get('TRAN_SUB_TY'),
|
|
part_tran_ty=row.get('PART_TRAN_TY'),
|
|
channel=row.get('CHANNEL'),
|
|
tran_amt=row.get('TRAN_AMT'),
|
|
balance=row.get('BALANCE'),
|
|
isreverse=row.get('ISREVERSE'),
|
|
reverse=row.get('REVERSE'),
|
|
tran_particular=(row.get('TRAN_PARTICULAR') or '')[:100]
|
|
)
|
|
records.append(record)
|
|
|
|
db.session.add_all(records)
|
|
db.session.commit()
|
|
print(f"Successfully uploaded {len(records)} records to analytics_raw_transactions")
|
|
|
|
except Exception as e:
|
|
db.session.rollback()
|
|
print(f"Error uploading data: {str(e)}") |