# Standard Library Imports from datetime import timedelta from flask import ( Flask, jsonify, render_template, request, url_for, make_response, session, send_file, Response, render_template_string, redirect, send_file) import pandas as pd import plotly.graph_objects as go from sqlalchemy import create_engine from config import tbl_mapping from data_connector.sqlite_connector import get_db_connection from lang_assistant.langhelper import chat_response, summary_extractor_from_df, chat_with_df, generate_graphdata from utilities.plotting import (get_validation_json, badges_get_pillar_dougnutdata, badges_get_badgecompletion_monthwise, get_wfrankwise_countmom, get_lst_topdepartment, get_wfrankwise_count, get_topfive_badgetitle) import os import sqlite3 import time import json app = Flask(__name__, static_url_path='/static') request_info = {} # Set secret key app.config['SECRET_KEY'] = 'faith' app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(minutes=60) @app.route("/validate_compensation", methods=['GET']) def load_compensation(): # con = sqlite3.connect("database.db") # df = pd.read_sql_query(f"SELECT * from learning", con) df = pd.DataFrame({'ID':[12,13], 'Status':['Done', 'In Progress']}) no_rows, no_cols = df.shape n_gui = df.GUI.nunique() if 'GUI' in df.columns else 'GUI not Found' tablevalues = {'n_rows':no_rows, 'n_cols':no_cols, 'unique_gui':n_gui} print('Calculation done') # Data for the bar chart bar_chart_data = { 'labels': ['Label 1', 'Label 2', 'Label 3', 'Label 4', 'Label 5'], 'values': [30, 40, 30, 21, 34] } # Data for the pie chart pie_chart_data = { 'labels': ['Label A', 'Label B', 'Label C', 'Label D', 'Label E'], 'values': [45, 30, 25, 9, 34] } # Create the bar chart figure bar_chart_figure = go.Figure( data=[ go.Bar( x=bar_chart_data['labels'], y=bar_chart_data['values'], marker_color='rgba(54, 162, 235, 0.5)', marker_line_color='rgba(54, 162, 235, 1)', marker_line_width=1 ) ], layout=go.Layout( title='Bar Chart', yaxis=dict(title='Values'), margin=dict(l=20, r=20, t=40, b=20) ) ) # Create the pie chart figure pie_chart_figure = go.Figure( data=[ go.Pie( labels=pie_chart_data['labels'], values=pie_chart_data['values'], hole=0.3, marker=dict(colors=['rgba(255, 99, 132, 0.5)', 'rgba(54, 162, 235, 0.5)', 'rgba(255, 206, 86, 0.5)'], line=dict(color='rgba(0, 0, 0, 0.5)', width=1)) ) ], layout=go.Layout( title='Pie Chart', margin=dict(l=20, r=20, t=40, b=20) ) ) # Convert the figures to HTML bar_chart_html = bar_chart_figure.to_html(full_html=False) pie_chart_html = pie_chart_figure.to_html(full_html=False) data = { 'Regex issue': [-90, -10, -5, 0], 'Null percentage': [-10, -35, 0, 0], 'Seems ok': [40, 45, 90, 100], 'data mismatch': [0, -10, -5, 0] } df = pd.DataFrame(data, index=['GTE', 'SMU', 'Service_Line', 'Sub_SL']) labels = df.index.to_list() reg_issue = df['Regex issue'].to_list() null_issue = df['Null percentage'].to_list() ok_data = df['Seems ok'].to_list() mismatch_issue = df['data mismatch'].to_list() tbl_selected = request_info.get('tbl_selected', []) return render_template("validate_compensation.html", req_tables = tbl_selected, bar_chart_html=bar_chart_html, pie_chart_html=pie_chart_html, table_info = tablevalues, labels = labels, reg_issue=reg_issue, null_issue=null_issue, ok_data=ok_data , mismatch_issue=mismatch_issue, show_sidebar=True) @app.route("/validate_badges", methods=['GET', 'POST']) def load_badges(): # con = sqlite3.connect("database.db") # df = pd.read_sql_query(f"SELECT * from badges", con) df = pd.read_csv("referencefiles/badges.csv") no_rows, no_cols = df.shape n_gui = df.GUI.nunique() if 'GUI' in df.columns else 'GUI not Found' tablevalues = {'n_rows':no_rows, 'n_cols':no_cols, 'unique_gui':n_gui} print('Calculation done') tbl_selected = request_info.get('tbl_selected', []) #session.get('tbl_selected', []) json_data = get_validation_json('badges') json_pillar_data = badges_get_pillar_dougnutdata() json_badgecompletion_data = badges_get_badgecompletion_monthwise() lst_topfive_badgetitle = get_topfive_badgetitle() return render_template("validate_badges.html", lst_topfive_badgetitle = lst_topfive_badgetitle, req_tables = tbl_selected, table_info = tablevalues, json_data = json_data, json_pillar_data=json_pillar_data, json_badgecompletion_data = json_badgecompletion_data, show_sidebar=True) @app.route("/validation", methods=['GET', 'POST']) @app.route("/validate_workforce", methods=['GET', 'POST']) def load_workforce(): # con = sqlite3.connect("database.db") # df = pd.read_sql_query(f"SELECT * from workforce", con) df = pd.read_csv(r"referencefiles/workforce.csv") no_rows, no_cols = df.shape n_gui = df.GUI.nunique() if 'GUI' in df.columns else 'GUI not Found' tablevalues = {'n_rows':no_rows, 'n_cols':no_cols, 'unique_gui':n_gui} json_val_data = get_validation_json('workforce') json_empdist = get_wfrankwise_countmom() lst_topfive_dept = get_lst_topdepartment() json_rankwise_empdist = get_wfrankwise_count() tbl_selected = request_info.get('tbl_selected', []) #['Badges', 'learning'] gpt_response = summary_extractor_from_df("""{"male": 56, "female": 44 }""") return render_template("validate_workforce.html", req_tables = tbl_selected, table_info = tablevalues, json_data = json_val_data, json_empdist = json_empdist, lst_topfive_dept = lst_topfive_dept, json_rankwise_empdist = json_rankwise_empdist, aicontent_genderanalysis = gpt_response, show_sidebar = True) @app.route("/validate_miscellaneous", methods=['GET', 'POST']) def load_miscellaneous(): tbl_selected = request_info.get('tbl_selected', []) return render_template("validate_miscellaneous.html", req_tables = tbl_selected, show_sidebar = True) @app.route("/timecard.html", methods=['GET', 'POST']) def load_timecard(): return render_template("timecard.html") @app.route("/get_llmresponse") def get_bot_response(): user_message = request.args.get('msg') dd_table_selected = request.args.get('table_selected') print(f"user message and table selected : {user_message}, {dd_table_selected}") print(f"request args : {request.args.get('msg')}") response_usrmsg = chat_with_df(user_message, table_name = dd_table_selected) return response_usrmsg @app.route("/get_val_llmresponse") def get_bot_valresponse(): user_message = request.args.get('msg') table_selected = request.args.get('table_selected') print(f"user message and table selected : {user_message}, {table_selected}") print(f"request args : {request.args.get('msg')}") try: llm_response_dict = generate_graphdata(user_message, table_name = table_selected) except Exception as e: llm_response_dict = dict(success=False, chart_type='text', chart_label=None, chart_json_data=None, text_to_display="Exception : Some error occured while processing, "+str(e)[:50] + "..") print(llm_response_dict) output_gendata = json.dumps(llm_response_dict) return output_gendata @app.route("/data.html", methods=['GET', 'POST']) @app.route("/data", methods=['GET','POST']) def data(): tbl_htmls = {} tbl_selected = request_info['tbl_selected'] print("-------------------------Hello world------------------------") print(tbl_selected) for tblname in tbl_selected: # Read sqlite query results into a pandas DataFrame # con = sqlite3.connect("database.db") # df = pd.read_sql_query(f"SELECT * from {tblname}", con) filepath = f"referencefiles/{tblname}.csv" df = pd.read_csv(filepath) top_records = df.copy() # con.close() html_top_records = top_records.to_html(index=False, table_id= f'dtable_{tblname}', classes='display nowrap table table-bordered table-striped table-condensed small p-1', justify='left') html_top_records = html_top_records.replace('', '') tbl_htmls[tblname] = html_top_records return render_template('data.html', table_htmls = tbl_htmls, req_tables = json.dumps(tbl_selected[0])) @app.route("/", methods=['GET', 'POST']) @app.route("/home", methods=['GET', 'POST']) @app.route("/home.html", methods=['GET', 'POST']) def hometest(): if request.method == 'GET': return render_template('home.html') elif request.method == 'POST': global request_info request_info['start_date'] = request.form.get('calendar_value').split(":")[0] request_info['end_date'] = request.form.get('calendar_value').split(":")[1] request_info['sl_subsl'] = request.form.get('sl_subsl') request_info['tbl_selected'] = request.form.getlist('tbl_selected') return redirect(url_for('data')) @app.route('/download/', methods=['GET']) def download_csv(table_name): # Assuming you have a mapping or logic to get the CSV file path from the table name csv_file_path = os.path.join('referencefiles', f"{table_name}.csv") if os.path.exists(csv_file_path): return send_file(csv_file_path, as_attachment=True) else: return "File not found", 404 # if __name__ == '__main__': # app.run(debug=True) # from waitress import serve # serve(app, host="0.0.0.0")