def getUsers(): company = request.args.get('company') dept = request.args.get('department') host, base, colection, dbuser, pwd = bl.mongoInit('userdetails') data = bl.getUsers(dept, company, host, base, colection, dbuser, pwd) return json.dumps(data)
def calculate(R_df, colection): phy = R_df[R_df['subsector'] == 'Physical'] org = R_df[R_df['subsector'] == 'Organizational'] tech = R_df[R_df['subsector'] == 'Technical'] phycid = phy.cid.unique() orgcid = org.cid.unique() techcid = tech.cid.unique() subs = ['Physical', 'Organizational', 'Technical'] host, base, colection, user, pwd = bl.mongoInit(colection) ## userid,survey,company should be passed from UI with API ##userid='588' ##company='ITH' ##survey='Quater 1' x = df = pd.DataFrame() for sub in subs: subdf = R_df[R_df['subsector'] == sub] subcid = subdf.cid.unique() for i in subcid: # print("i value is ", i, sub) subscore = subdf[subdf.cid == i].qscore.sum() / len( subdf[subdf.cid == i].index) df = subdf[subdf.cid == i] df['cscore'] = round(subscore, 2) x = pd.concat([df, x], ignore_index=True) # print(subcid) df = x.sort_values( ['cid']) # df has mean values of cscore i.e category score in R1 # print(df) return df
def getQuestions(): survey = request.args.get('survey') company = request.args.get('company') sector = request.args.get('sector') host, base, colection, dbuser, pwd = bl.mongoInit() data = bl.getSurveyQuestions(survey, company, host, base, colection, dbuser, pwd, sector)
def getUserId(): company = request.args.get('company') dept = request.args.get('department') email = request.args.get('email') survey = request.args.get('survey') host, base, colection, dbuser, pwd = bl.mongoInit('userdetails') data = bl.getID(dept, company, email, survey, host, base, colection, dbuser, pwd) id = ''.join(str(data)) return id
def chartsOne(): survey = request.args.get('survey') company = request.args.get('company') userid = request.args.get('userid') host, base, colection, dbuser, pwd = bl.mongoInit() sectors = ['R1', 'R2', 'R3', 'R4'] df = pd.DataFrame( columns=['sector', 'subsector', 'cid', 'qid', 'qscore', 'qconfidence']) for sector in sectors: document = bl.getSurveyDetails(userid, survey, company, host, base, colection, dbuser, pwd, sector) for i in document: df = df.append( { 'sector': i['rows']['sector'], 'subsector': i['rows']['subsector'], 'cid': i['rows']['cid'], 'qid': i['rows']['qid'], 'qscore': i['rows']['qscore'], 'qconfidence': i['rows']['qconfidence'] }, ignore_index=True) df = df.sort_values(['sector', 'subsector']) R1_df = df[df['sector'] == 'R1'] R2_df = df[df['sector'] == 'R2'] R3_df = df[df['sector'] == 'R3'] R4_df = df[df['sector'] == 'R4'] print("BHARATH") R1_df = cl.calculate(R1_df) R2_df = cl.calculate(R2_df) R3_df = cl.calculate(R3_df) R4_df = cl.calculate(R4_df) framelist = [R1_df, R2_df, R3_df, R4_df] mf = pd.DataFrame(columns=R1_df.columns.values) for i in framelist: phy = i[i['subsector'] == 'Physical'] org = i[i['subsector'] == 'Organisational'] tech = i[i['subsector'] == 'Technical'] i['avgsectscore'] = (phy.iloc[0]['cscore'] + org.iloc[0]['cscore'] + tech.iloc[0]['cscore']) / 3 mf = mf.append(i) mf.index = pd.RangeIndex(len(mf.index)) print(mf) return 'Done'
def chartsOne(): survey = request.args.get('survey') company = request.args.get('company') userid = request.args.get('userid') host, base, colection, dbuser, pwd = bl.mongoInit() df = pd.DataFrame( columns=['sector', 'subsector', 'cid', 'qid', 'qscore', 'qconfidence']) for sector in sectors: document = bl.getSurveyDetails(userid, survey, company, host, base, colection, dbuser, pwd, sector) for i in document: df = df.append( { 'sector': i['rows']['sector'], 'subsector': i['rows']['subsector'], 'cid': i['rows']['cid'], 'qid': i['rows']['qid'], 'qscore': i['rows']['qscore'], 'qconfidence': i['rows']['qconfidence'] }, ignore_index=True) df = df.sort_values(['sector', 'subsector']) R1_df = df[df['sector'] == 'R1'] R2_df = df[df['sector'] == 'R2'] R3_df = df[df['sector'] == 'R3'] R4_df = df[df['sector'] == 'R4'] R1_df = cl.calculate(R1_df) R2_df = cl.calculate(R2_df) R3_df = cl.calculate(R3_df) R4_df = cl.calculate(R4_df) framelist = [R1_df, R2_df, R3_df, R4_df] mf = pd.DataFrame(columns=R1_df.columns.values) for i in framelist: phy = i[i['subsector'] == 'Physical'] org = i[i['subsector'] == 'Organisational'] tech = i[i['subsector'] == 'Technical'] i['avgsectscore'] = (phy.iloc[0]['cscore'] + org.iloc[0]['cscore'] + tech.iloc[0]['cscore']) / 3 mf = mf.append(i) mf.index = pd.RangeIndex(len(mf.index)) avgR1_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')]) avgR1_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')]) avgR1_org = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R1')]) avgR1_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')]) avgR2_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')]) avgR2_org = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R2')]) avgR2_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')]) avgR3_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')]) avgR3_org = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R3')]) avgR3_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')]) avgR4_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')]) avgR4_org = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R4')]) avgR4_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')]) # mf[(mf['subsector']=='Physical') & (mf['sector']=='R1')]['subsector_avg']=avgR1_phy ## mf['subsector_avg'] = np.where( (mf['subsector']=='Physical') & (mf['sector']=='R1')) filter1 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')] filter1['subsector_avg'] = avgR1_phy filter2 = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R1')] filter2['subsector_avg'] = avgR1_org filter3 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')] filter3['subsector_avg'] = avgR1_tech filter4 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')] filter4['subsector_avg'] = avgR2_phy filter5 = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R2')] filter5['subsector_avg'] = avgR2_org filter6 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')] filter6['subsector_avg'] = avgR2_tech filter7 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')] filter7['subsector_avg'] = avgR3_phy filter8 = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R3')] filter8['subsector_avg'] = avgR3_org filter9 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')] filter9['subsector_avg'] = avgR3_tech filter10 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')] filter10['subsector_avg'] = avgR4_phy filter11 = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R4')] filter11['subsector_avg'] = avgR4_org filter12 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')] filter12['subsector_avg'] = avgR4_tech dfs = [ filter1, filter2, filter3, filter4, filter5, filter6, filter7, filter8, filter9, filter10, filter11, filter12 ] df = pd.concat(dfs) js = df.to_json() # print(df) return js
def chartsByDept(): import time import xlwt from xlwt import Workbook survey = request.args.get('survey') company = request.args.get('company') dept = request.args.get('department') host, base, colection, dbuser, pwd = bl.mongoInit('users') df = pd.DataFrame( columns=['sector', 'subsector', 'cid', 'qid', 'qscore', 'qconfidence']) for sector in sectors: document = bl.getSurveyDetailsByDept(survey, company, host, base, colection, dbuser, pwd, sector, dept) for i in document: df = df.append( { 'sector': i['rows']['sector'], 'subsector': i['rows']['subsector'], 'cid': i['rows']['cid'], 'cname': i['rows']['cname'], 'qid': i['rows']['qid'], 'qscore': i['rows']['qscore'], 'qconfidence': i['rows']['qconfidence'] }, ignore_index=True) df = df.sort_values(['sector', 'subsector']) df['qconfidence'] = df['qconfidence'].astype(int) df['qscore'] = df['qscore'].astype(int) R1_df = df[df['sector'] == 'R1'] R2_df = df[df['sector'] == 'R2'] R3_df = df[df['sector'] == 'R3'] R4_df = df[df['sector'] == 'R4'] R1_df = cl.calculate(R1_df, colection) R2_df = cl.calculate(R2_df, colection) R3_df = cl.calculate(R3_df, colection) R4_df = cl.calculate(R4_df, colection) framelist = [R1_df, R2_df, R3_df, R4_df] mf = pd.DataFrame(columns=R1_df.columns.values) for i in framelist: i['avgsectscore'] = i['qscore'].sum() / len(i['qscore']) mf = mf.append(i) mf.index = pd.RangeIndex(len(mf.index)) #phy=i[i['subsector']=='Physical'] #org=i[i['subsector']=='Organizational'] #tech=i[i['subsector']=='Technical'] #i['avgsectscore']=(phy.iloc[0]['subsector_avg']+org.iloc[0]['subsector_avg']+tech.iloc[0]['subsector_avg'])/3 #mf=mf.append(i) #mf.index = pd.RangeIndex(len(mf.index)) #avgR1_phy=mf[(mf['subsector']=='Physical') & (mf['sector']=='R1')].qscore.sum()/len(mf[(mf['subsector']=='Physical') & (mf['sector']=='R1')]) avgR1_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')]) avgR1_org = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R1')]) avgR1_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')]) avgR2_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')]) avgR2_org = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R2')]) avgR2_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')]) avgR3_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')]) avgR3_org = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R3')]) avgR3_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')]) avgR4_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')]) avgR4_org = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R4')]) avgR4_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')]) # mf[(mf['subsector']=='Physical') & (mf['sector']=='R1')]['subsector_avg']=avgR1_phy ## mf['subsector_avg'] = np.where( (mf['subsector']=='Physical') & (mf['sector']=='R1')) filter1 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')] filter1['subsector_avg'] = avgR1_phy filter2 = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R1')] filter2['subsector_avg'] = avgR1_org filter3 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')] filter3['subsector_avg'] = avgR1_tech filter4 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')] filter4['subsector_avg'] = avgR2_phy filter5 = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R2')] filter5['subsector_avg'] = avgR2_org filter6 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')] filter6['subsector_avg'] = avgR2_tech filter7 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')] filter7['subsector_avg'] = avgR3_phy filter8 = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R3')] filter8['subsector_avg'] = avgR3_org filter9 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')] filter9['subsector_avg'] = avgR3_tech filter10 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')] filter10['subsector_avg'] = avgR4_phy filter11 = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R4')] filter11['subsector_avg'] = avgR4_org filter12 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')] filter12['subsector_avg'] = avgR4_tech dfs = [ filter1, filter2, filter3, filter4, filter5, filter6, filter7, filter8, filter9, filter10, filter11, filter12 ] df = pd.concat(dfs) time = int(time.time()) uniqId = 545 ## Sent from UI later.. can be admin userid fileName = 'dataframe_{}.xlsx'.format(time) wb = Workbook() sheet1 = wb.add_sheet('Sheet 1') wb.save(fileName) writer = ExcelWriter(fileName) df.to_excel(writer, 'Sheet1', index=False) writer.save() return fileName
def genReport(): survey = request.args.get('survey') company = request.args.get('company') now = datetime.datetime.now() curdate = now.strftime("%Y-%m-%d") host, base, colection, dbuser, pwd = bl.mongoInit('users') #file='C:\\toHDD\\toViggu\\surveyapp_python\\dataframe_1555184982.xlsx' req = requests.get( "http://localhost/backend/chartsAll?survey={}&company={}".format( survey, company)) print(req.text) file = req.text r = requests.get( "http://localhost/backend/getAllRadar?dataframe={}".format(file)) jsonres = r.json() subscores = [] subscores.append(jsonres['Physical'][0]) subscores.append(jsonres['Organizational'][0]) subscores.append(jsonres['Technical'][0]) subscores.append(jsonres['Physical'][1]) subscores.append(jsonres['Organizational'][1]) subscores.append(jsonres['Technical'][1]) subscores.append(jsonres['Physical'][2]) subscores.append(jsonres['Organizational'][2]) subscores.append(jsonres['Technical'][2]) subscores.append(jsonres['Physical'][3]) subscores.append(jsonres['Organizational'][3]) subscores.append(jsonres['Technical'][3]) R0 = cl.genFullRadar(sectors, subsectors, subscores) r = requests.get( "http://localhost/backend/getAllRadarBySector?dataframe={}".format( file)) jsonres = r.json() R1_labels = jsonres[0]['labels'] R1_values = jsonres[0]['score'] R2_labels = jsonres[1]['labels'] R2_values = jsonres[1]['score'] R3_labels = jsonres[2]['labels'] R3_values = jsonres[2]['score'] R4_labels = jsonres[3]['labels'] R4_values = jsonres[3]['score'] R1 = cl.genRadar(R1_labels, R1_values, '#ff6666', 'Robustness') R2 = cl.genRadar(R2_labels, R2_values, '#ffcc99', 'Redundancy') R3 = cl.genRadar(R3_labels, R3_values, '#99ff99', 'Resourcefulness') R4 = cl.genRadar(R4_labels, R4_values, '#66b3ff', 'Rapidity') r = requests.get( "http://localhost/backend/getAllRadarAllSectors?dataframe={}".format( file)) jsonres = r.json() labels = jsonres['category'] values = jsonres['scores'] print(values) labels = pd.Series(labels) values = pd.Series(values) R5 = cl.barh(labels, values) r = requests.get( "http://localhost/backend/getAllRadar?dataframe={}".format(file)) jsonres = r.json() phy = jsonres['Physical'] org = jsonres['Organizational'] tech = jsonres['Technical'] subsecs = ['Physical', 'Organizational', 'Technical'] bar1 = [phy[0], org[0], tech[0]] bar2 = [phy[1], org[1], tech[1]] bar3 = [phy[2], org[2], tech[2]] bar4 = [phy[3], org[3], tech[3]] R1_bar = cl.barRadar('R1', bar1, subsecs) R2_bar = cl.barRadar('R2', bar2, subsecs) R3_bar = cl.barRadar('R3', bar3, subsecs) R4_bar = cl.barRadar('R4', bar4, subsecs) lists = [R0, R1, R2, R3, R4, R5, R1_bar, R2_bar, R3_bar, R4_bar] print(lists) doc = DocxTemplate("sampleWord.docx") s1 = doc.new_subdoc() s1.add_picture(R1, width=Inches(7)) s2 = doc.new_subdoc() s2.add_picture(R2, width=Inches(7)) s3 = doc.new_subdoc() s3.add_picture(R3, width=Inches(7)) s4 = doc.new_subdoc() s4.add_picture(R4, width=Inches(7)) s5 = doc.new_subdoc() s5.add_picture(R0, width=Inches(7)) s6 = doc.new_subdoc() s6.add_picture(R5, width=Inches(7)) s7 = doc.new_subdoc() s7.add_picture(R1_bar, width=Inches(5)) s8 = doc.new_subdoc() s8.add_picture(R2_bar, width=Inches(5)) s9 = doc.new_subdoc() s9.add_picture(R3_bar, width=Inches(5)) s10 = doc.new_subdoc() s10.add_picture(R4_bar, width=Inches(5)) req = requests.get( "http://localhost/backend/surveysCount?survey={}&company={}".format( survey, company)) count = req.text context = { 'bar1': s7, 'bar2': s8, 'bar3': s9, 'bar4': s10, 'chart5': s6, 'radar1': s1, 'radar2': s2, 'radar3': s3, 'radar4': s4, 'complete': s5, 'company': company, 'survey': survey, 'date': curdate, 'number': count } doc.render(context) filename = "Survey_Metrics_{}.docx".format(bl.getTimeStamp()) doc.save(filename) outputFile = bl.convertToPDF(filename) bl.sendReport(survey, company, outputFile) return "Sent Report"