|
|
import gradio as gr |
|
|
import pandas as pd |
|
|
from datetime import datetime |
|
|
import pymssql |
|
|
|
|
|
|
|
|
def run_procedure(server, database, username, password, start_date, end_date): |
|
|
try: |
|
|
start_date = datetime.strptime(start_date, "%Y-%m-%d").date() |
|
|
end_date = datetime.strptime(end_date, "%Y-%m-%d").date() |
|
|
|
|
|
conn = pymssql.connect(server=server, user=username, password=password, database=database) |
|
|
cursor = conn.cursor() |
|
|
cursor.execute("EXEC dbo.Temp_Get_EQUITY_DocumentProcurement %s, %s", (start_date, end_date)) |
|
|
conn.commit() |
|
|
|
|
|
query = """ |
|
|
SELECT * FROM [DocumentAcquisition].dbo.Temp_Document_Procurement_Timeline |
|
|
WHERE Document_Procurement_Name = 'EQUITY' |
|
|
""" |
|
|
df = pd.read_sql(query, conn) |
|
|
conn.close() |
|
|
|
|
|
if df.empty: |
|
|
return None, None, "No data found for the given dates." |
|
|
else: |
|
|
csv_path = "document_procurement_report.csv" |
|
|
df.to_csv(csv_path, index=False) |
|
|
return df, csv_path, "Success" |
|
|
|
|
|
except Exception as e: |
|
|
return None, None, f"Error: {str(e)}" |
|
|
|
|
|
|
|
|
iface = gr.Interface( |
|
|
fn=run_procedure, |
|
|
inputs=[ |
|
|
gr.Textbox(label="Server Name", placeholder="e.g., myserver.database.windows.net"), |
|
|
gr.Textbox(label="Database Name", placeholder="e.g., DocumentAcquisition"), |
|
|
gr.Textbox(label="Username"), |
|
|
gr.Textbox(label="Password", type="password"), |
|
|
gr.Textbox(label="Start Date (YYYY-MM-DD)", value="2024-01-01"), |
|
|
gr.Textbox(label="End Date (YYYY-MM-DD)", value="2024-01-07"), |
|
|
], |
|
|
outputs=[ |
|
|
gr.Dataframe(label="Procedure Output"), |
|
|
gr.File(label="Download CSV Report"), |
|
|
gr.Textbox(label="Status / Error Message") |
|
|
], |
|
|
title="EQUITY Document Procurement Report Generator", |
|
|
description="Enter your SQL Server details and date range to run the stored procedure and download the report." |
|
|
) |
|
|
|
|
|
iface.launch() |
|
|
|
