def download_cic_result(request, *args, **kwargs):
try:
context = {}
logger = logger_hander.set_logger()
mapped_df = request.session['Mapped_records']
result_df = request.session['Total_Records']
mapped_table = request.session['tables']
xlsx_Result_sheet_names = request.session['file']
if request.method == 'GET':
context.update({
"success": "Prediction Done . Result is Ready to Download.",
"file": xlsx_Result_sheet_names,
"tables": mapped_table,
"Mapped_records": mapped_df,
"Total_Records": result_df
})
return render(request, 'search_by_CIC_id.html', context)
if request.method == 'POST':
path = "download/{}".format(xlsx_Result_sheet_names)
if os.path.exists(path):
response = HttpResponse(
content_type='application/vnd.ms-excel')
response[
'Content-Disposition'] = 'attachment; filename={}'.format(
path)
wb = openpyxl.load_workbook(path)
wb.save(response)
os.remove(path)
logger.info("Response sent Successfully !!!")
return response
else:
logger.exception('File Already Downloaded')
messages.error(request, 'File Already Downloaded')
return redirect('/')
except Exception as e:
logger.exception(
'File Already Downloaded or Something Went Wrong, plz try again.{}'
.format(e))
messages.error(
request,
'File Already Downloaded or Something Went Wrong, plz try again.')
return redirect('/')
:param sheet: google sheet instance, List_cell_name:list of google sheet cells name
:return: true or flase
"""
flag = True
try:
for cell_name in List_cell_name:
cell = sheet.find(cell_name)
return flag
except gspread.exceptions.CellNotFound:
flag = False
logger.error("CellNotFound Exception")
return flag
def save_output_into_sheet(self, worksheet, df_list):
"""
This function will Save & Append output into Google sheet
:param worksheet: worksheet instance, df_list: list of data rows
:return: true or worksheet not found exception
"""
try:
for row in df_list:
worksheet.append_row(row)
logger.info("Output response of Rasa has been appended Successfully..!")
return True
except Exception as e:
excepName = type(e).__name__
logger.error(" Updating google sheet " + excepName)
return excepName
logger = logger_hander.set_logger()
def recommendation(request):
logger = logger_hander.set_logger()
with open('config.json') as config_file:
config_data = json.load(config_file)
model_file_path = config_data['model_file_path']
xlsx_Test_worksheet_name = config_data['xlsx_Test_worksheet_name']
xlsx_column_name_list = config_data['xlsx_column_name_list']
xlsx_primary_header_name = config_data['xlsx_primary_header_name']
context = {}
if request.method == 'POST':
myFile = request.FILES.get('excel_file', None)
if not myFile:
logger.error(" File Not Found. Please Upload a Xlsx file.")
messages.error(request,
'File Not Found. Please Upload a Xlsx file.')
return redirect('/')
if str(myFile).split('.')[-1] != "xlsx":
logger.error(" Wrong File Format. Please Upload a Xlsx file")
messages.error(request,
'Wrong File Format. Please Upload a Xlsx file')
return redirect('/')
try:
test_dataframe = pd.read_excel(myFile,
sheet_name=xlsx_Test_worksheet_name,
engine='openpyxl')
except Exception as e:
logger.exception(
"XLSX file not having Worksheet name - 'Data' : {}".format(e))
messages.error(request,
'XLSX file not having Worksheet name - Data')
return redirect('/')
if not test_dataframe.empty:
if (all(x in test_dataframe.columns
for x in xlsx_column_name_list)):
test_dataframe.dropna(subset=[xlsx_primary_header_name],
inplace=True)
pickle_model = load_model(model_file_path, logger)
final_test_dataframe = fetch_required_features(
test_dataframe, xlsx_column_name_list, logger)
try:
final_df = predict_result(pickle_model,
final_test_dataframe,
test_dataframe, logger)
for x in xlsx_column_name_list:
if final_df[x].isna().sum() > 0:
final_df.loc[final_df[x].isna(),
'Mapped to Company'] = "NA"
# print(final_df[final_df[x].isna()])
mapped_df = show_tables(final_df)
logger.info("mapped records :{}".format(mapped_df))
with pd.ExcelWriter(myFile) as writer:
xlsx_Result_worksheet_name = str(myFile).split(
".")[0] + "-MRE-OP-" + str(datetime.datetime.now(
).strftime("%Y-%m-%d-%H-%M-%S"))
final_df.to_excel(
writer,
sheet_name=str(xlsx_Result_worksheet_name),
index=False)
writer.save()
writer.close()
wb = openpyxl.load_workbook(myFile)
xlsx_Result_sheet_names = "MRE_Result_" + str(
myFile).split(".")[0] + "_" + str(
datetime.datetime.now().strftime(
"%Y-%m-%d-%H-%M-%S")) + ".xlsx"
wb.save("download/{}".format(xlsx_Result_sheet_names))
request.session['final_df_cols'] = final_df.columns.tolist(
)
request.session[
'final_df_values'] = final_df.values.tolist()
request.session['tables'] = [
mapped_df.to_html(index=False,
classes='students',
header="true")
]
request.session['Mapped_records'] = len(mapped_df)
request.session['Total_Records'] = len(final_df)
request.session['file'] = xlsx_Result_sheet_names
logger.info(
"Redirect to download CSV and view Mapped tables - Response sent Successfully !!!"
)
# return render(request, 'recommendation.html', context)
return redirect('download_result')
except Exception as e:
logger.exception(
'Required column list not present in "Data" Worksheet.'
)
messages.error(
request,
'Required column list not present in "Data" Worksheet.'
)
return redirect('/')
wb.save(xlsx_Result_sheet_names)
logger.info(" Response sent Successfully !!!")
context.update({
"success": "Prediction Done. Result is Ready to Download.",
"file": xlsx_Result_sheet_names
})
# return render(request, 'recommendation.html', context)
return redirect(
'/download_result/?type=GET&sheetName={}'.format(
xlsx_Result_sheet_names))
else:
logger.exception(
'Required column list not present in "Data" Worksheet.')
messages.error(
request,
'Required column list not present in "Data" Worksheet.')
return redirect('/')
else:
logger.exception('xlsx File of having "Data" Worksheet is Empty.')
messages.error(request,
'xlsx File of having "Data" Worksheet is Empty.')
return redirect('/')
return render(request, 'recommendation.html', context)
def save_df_db(request):
context = {}
logger = logger_hander.set_logger()
if request.method == 'GET':
try:
with open('config.json') as config_file:
config_data = json.load(config_file)
xlsx_Store_DB_primary_header_name = config_data[
'xlsx_primary_header_name']
xlsx_column_name_list = config_data['xlsx_column_name_list']
except Exception as e:
logger.exception(
"did not able to read config.json file : {}".format(e))
return HttpResponse('Config file Issue or Internal Issue')
final_df_val = request.session['final_df_values']
final_df_cols = request.session['final_df_cols']
load_dataframe = pd.DataFrame(final_df_val, columns=final_df_cols)
load_dataframe.dropna(subset=[xlsx_Store_DB_primary_header_name],
inplace=True)
incorrect_dataframe = load_dataframe[
load_dataframe[xlsx_column_name_list].isnull().any(1)]
incorrect_dataframe = pd.DataFrame(incorrect_dataframe[
incorrect_dataframe[xlsx_column_name_list].isna().any(1)])
correct_dataframe = pd.DataFrame(
load_dataframe[~load_dataframe[xlsx_column_name_list].isnull().
any(1)])
correct_dataframe = pd.DataFrame(
correct_dataframe[~correct_dataframe[xlsx_column_name_list].isna().
any(1)])
try:
for i, row in correct_dataframe.iterrows():
if not MREDB.objects.filter(Admit_ID=load_dataframe[
xlsx_Store_DB_primary_header_name][i]).exists():
db_cursor = MREDB.objects.create(
Admit_ID=load_dataframe['Admit Id'][i],
Name=load_dataframe['Name'][i],
T=load_dataframe['T'][i],
R=load_dataframe['R'][i],
A=load_dataframe['A'][i],
C=load_dataframe['C'][i],
K=load_dataframe['K'][i],
Last_Status=load_dataframe['Last Status'][i],
Last_Internal_Rating=load_dataframe[
'Last Internal Rating'][i],
Techability_Score=load_dataframe['Techability Score']
[i],
Learnability_Score=load_dataframe['Learnability Score']
[i],
Communicability_Score=load_dataframe[
'Communicability Score'][i],
Avg_TRACK=load_dataframe['Avg TRACK Score'][i],
Present_Percent=load_dataframe['Present Percent'][i],
Overall_Remarks=load_dataframe['Overall Remarks'][i],
Mapped_to_Company=load_dataframe['Mapped to Company']
[i])
db_cursor.save()
context.update({
"db_df_save":
"Successfully Store XLSX-Data into Database..!"
})
logger.info("Successfully Store XLSX-Data into Database..!")
return render(request, 'saveDB.html', context)
except Exception as e:
logger.exception(
"Something went wrong while saving df into db : {}".format(e))
messages.error(
request,
'Something went wrong while saving data into database.')
return redirect('/')
def searchCIC(request):
logger = logger_hander.set_logger()
try:
with open('config.json') as config_file:
config_data = json.load(config_file)
xlsx_Search_CIC_worksheet_name = config_data[
'xlsx_Test_worksheet_name']
xlsx_Search_CIC_column_name_list = config_data[
'xlsx_Search_CIC_column_name_list']
xlsx_Store_DB_column_name_list = config_data[
'xlsx_Store_DB_column_name_list']
xlsx_Store_DB_primary_header_name = config_data[
'xlsx_primary_header_name']
model_file_path = config_data['model_file_path']
except Exception as e:
logger.exception(
"did not able to read config.json file : {}".format(e))
messages.error(request, 'Config file Issue or Internal Issue')
return redirect('search_cic_id')
context = {}
result_df = pd.DataFrame(columns=xlsx_Store_DB_column_name_list)
if request.method == 'POST':
myFile = request.FILES.get('excel_file', None)
if not myFile:
logger.error(" File Not Found. Please Upload a Xlsx file.")
messages.error(request,
'File Not Found. Please Upload a Xlsx file.')
return redirect('search_cic_id')
if str(myFile).split('.')[-1] != "xlsx":
logger.error(" Wrong File Format. Please Upload a Xlsx file")
messages.error(request,
'Wrong File Format. Please Upload a Xlsx file')
return redirect('search_cic_id')
try:
search_CIC_dataframe = pd.read_excel(
myFile,
sheet_name=xlsx_Search_CIC_worksheet_name,
engine='openpyxl')
except Exception as e:
logger.exception(
"XLSX file not having Worksheet name - 'Data' : {}".format(e))
messages.error(request,
'XLSX file not having Worksheet name - "Data"')
return redirect('search_cic_id')
if not search_CIC_dataframe.empty:
try:
if (all(x in search_CIC_dataframe.columns
for x in xlsx_Search_CIC_column_name_list)):
search_CIC_dataframe.dropna(
subset=[xlsx_Store_DB_primary_header_name],
inplace=True)
for i in range(len(search_CIC_dataframe)):
if MREDB.objects.filter(Admit_ID=search_CIC_dataframe[
xlsx_Store_DB_primary_header_name]
[i]).exists():
serach_record = MREDB.objects.filter(
Admit_ID=search_CIC_dataframe[
xlsx_Store_DB_primary_header_name][i])[0]
Admit_ID = serach_record.Admit_ID
Name = serach_record.Name
T = serach_record.T
R = serach_record.R
A = serach_record.A
C = serach_record.C
K = serach_record.K
Last_Status = serach_record.Last_Status
Last_Internal_Rating = serach_record.Last_Internal_Rating
Techability_Score = serach_record.Techability_Score
Learnability_Score = serach_record.Learnability_Score
Communicability_Score = serach_record.Communicability_Score
Avg_TRACK = serach_record.Avg_TRACK
Present_Percent = serach_record.Present_Percent
Overall_Remarks = serach_record.Overall_Remarks
if serach_record.Mapped_to_Company != "nan":
Mapped_to_Company = serach_record.Mapped_to_Company
result_df = result_df.append(
{
"Admit Id": Admit_ID,
"Name": Name,
"T": T,
"R": R,
"A": A,
"C": C,
"K": K,
"Last Status": Last_Status,
"Last Internal Rating":
Last_Internal_Rating,
"Techability Score": Techability_Score,
"Learnability Score":
Learnability_Score,
"Communicability Score":
Communicability_Score,
"Avg TRACK Score": Avg_TRACK,
"Present Percent": Present_Percent,
"Overall Remarks": Overall_Remarks,
"Mapped to Company": Mapped_to_Company
},
ignore_index=True)
else:
pickle_model = load_model(
model_file_path, logger)
Last_Status, Last_Internal_Rating = convert_column(
Last_Status, Last_Internal_Rating, logger)
final_record = [
T, R, A, C, K, Last_Status,
Last_Internal_Rating
]
logger.info(
" Record Parameters for Testing :{}".
format(final_record))
final_result = predict_result(
pickle_model, [final_record], logger)
serach_record.Mapped_to_Company = final_result
serach_record.save()
result_df = result_df.append(
{
"Admit Id":
Admit_ID,
"Name":
Name,
"T":
T,
"R":
R,
"A":
A,
"C":
C,
"K":
K,
"Last Status":
Last_Status,
"Last Internal Rating":
Last_Internal_Rating,
"Techability Score":
Techability_Score,
"Learnability Score":
Learnability_Score,
"Communicability Score":
Communicability_Score,
"Avg TRACK Score":
Avg_TRACK,
"Present Percent":
Present_Percent,
"Overall Remarks":
Overall_Remarks,
"Mapped to Company":
serach_record.Mapped_to_Company
},
ignore_index=True)
mapped_df = show_tables(result_df)
logger.info("mapped Records: {}".format(len(mapped_df)))
with pd.ExcelWriter(myFile) as writer:
xlsx_Result_worksheet_name = str(myFile).split(
".")[0] + "-MRE-OP-" + str(datetime.datetime.now(
).strftime("%Y-%m-%d-%H-%M-%S"))
result_df.to_excel(
writer,
sheet_name=str(xlsx_Result_worksheet_name),
index=False)
writer.save()
writer.close()
wb = openpyxl.load_workbook(myFile)
xlsx_Result_sheet_names = "MRE_Result_" + str(
myFile).split(".")[0] + "_" + str(
datetime.datetime.now().strftime(
"%Y-%m-%d-%H-%M-%S")) + ".xlsx"
wb.save("download/{}".format(xlsx_Result_sheet_names))
request.session['tables'] = [
mapped_df.to_html(index=False,
classes='students',
header="true")
]
request.session['Mapped_records'] = len(mapped_df)
request.session['Total_Records'] = len(result_df)
request.session['file'] = xlsx_Result_sheet_names
logger.info(
"Redirect to download CSV and view Mapped tables - Response sent Successfully !!!"
)
# return render(request, 'search_by_CIC_id.html', context )
return redirect('download_cic_result')
except Exception as e:
logger.exception("Something went wrong : {}".format(e))
messages.error(request, 'Something went wrong')
return redirect('search_cic_id')
return render(request, 'search_by_CIC_id.html', context)
def saveDB(request):
logger = logger_hander.set_logger()
try:
with open('config.json') as config_file:
config_data = json.load(config_file)
xlsx_Store_DB_worksheet_name = config_data['xlsx_Test_worksheet_name']
xlsx_Store_DB_column_name_list = config_data[
'xlsx_Store_DB_column_name_list']
xlsx_Store_DB_primary_header_name = config_data[
'xlsx_primary_header_name']
except Exception as e:
logger.exception(
"did not able to read config.json file : {}".format(e))
messages.error(request, 'Config file Issue or Internal Issue')
return redirect('saveDB')
context = {}
if request.method == 'POST':
myFile = request.FILES.get('excel_file', None)
if not myFile:
logger.error(" File Not Found. Please Upload a Xlsx file.")
messages.error(request,
'File Not Found. Please Upload a Xlsx file.')
return redirect('saveDB')
if str(myFile).split('.')[-1] != "xlsx":
logger.error(" Wrong File Format. Please Upload a Xlsx file")
messages.error(request,
'Wrong File Format. Please Upload a Xlsx file')
return redirect('saveDB')
try:
load_dataframe = pd.read_excel(
myFile,
sheet_name=xlsx_Store_DB_worksheet_name,
engine='openpyxl')
except Exception as e:
logger.exception(
"XLSX file not having Worksheet name - 'Store-Data' : {}".
format(e))
messages.error(
request, 'XLSX file not having Worksheet name - "Store-Data"')
return redirect('saveDB')
if not load_dataframe.empty:
try:
if (all(x in load_dataframe.columns
for x in xlsx_Store_DB_column_name_list)):
load_dataframe.dropna(
subset=[xlsx_Store_DB_primary_header_name],
inplace=True)
for i in range(len(load_dataframe)):
if not MREDB.objects.filter(Admit_ID=load_dataframe[
xlsx_Store_DB_primary_header_name]
[i]).exists():
db_cursor = MREDB.objects.create(
Admit_ID=load_dataframe['Admit Id'][i],
Name=load_dataframe['Name'][i],
T=load_dataframe['T'][i],
R=load_dataframe['R'][i],
A=load_dataframe['A'][i],
C=load_dataframe['C'][i],
K=load_dataframe['K'][i],
Last_Status=load_dataframe['Last Status'][i],
Last_Internal_Rating=load_dataframe[
'Last Internal Rating'][i],
Techability_Score=load_dataframe[
'Techability Score'][i],
Learnability_Score=load_dataframe[
'Learnability Score'][i],
Communicability_Score=load_dataframe[
'Communicability Score'][i],
Avg_TRACK=load_dataframe['Avg TRACK Score'][i],
Present_Percent=load_dataframe[
'Present Percent'][i],
Overall_Remarks=load_dataframe[
'Overall Remarks'][i],
Mapped_to_Company=load_dataframe[
'Mapped to Company'][i])
db_cursor.save()
context.update({
"success":
"Successfully Store XLSX-Data into Database..!"
})
logger.info(
"Successfully Store XLSX-Data into Database..!")
return render(request, 'saveDB.html', context)
except Exception as e:
logger.exception(
"XLSX file columns name is issue or model saving issue : {}"
.format(e))
messages.error(
request,
'XLSX file columns name is issue or Internal issue')
return redirect('saveDB')
return render(request, 'saveDB.html', context)