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"
