def changeCurrentProject(newid):
currentProject = db.selectallfrom("project")
try:
currid = currentProject[currentProject['currentProject'] == 1]['projectid'].iloc[0]
db.updatevalues("project", "currentProject = ?", "projectid = ?", (int(0),int(currid)))
except:
print("There is no current project!")
db.updatevalues("project", "currentProject = ?", "projectid = ?", (int(1),int(newid)))
def valrulesupload(methodid, projectid):
st.write("Upload your validation rules")
datafile = st.file_uploader(type="xlsx", label="Validation rules")
try:
df = pd.read_excel(datafile)
st.write(df)
except:
st.write("Please upload your validation rules")
if st.button("Commit"):
db.insertvalrules(methodid, projectid, df)
db.updatevalues("project", "currentPhase = ?", "projectid = ?",
(int(4), int(projectid)))
st.button("Next import")
def dataupload(year, methodid, projectid):
st.write("Upload your data")
datafile = st.file_uploader(type="xlsx", label="Data")
try:
df = pd.read_excel(datafile)
st.write(df)
except:
st.write("Please upload your data")
if st.button("Commit"):
db.insertdata(year, methodid, projectid, df)
db.updatevalues("project", "currentPhase = ?", "projectid = ?",
(int(6), int(projectid)))
st.button("Next import")
def write():
st.title("Validation rules")
st.write("Add validation rule")
options = get_indicators()
indicator_selected = st.selectbox("Select Indicator", options=options)
realid = indicator_selected[0]
currentNaNmethoddf = db.selectfromwhere("realid,nanmethod","parameter", "realid = ?",(realid,))
currentNaNMethod = currentNaNmethoddf['nanmethod'].iloc[0]
st.write("Current way to deal with empty values: " + nanMethods[currentNaNMethod])
#if st.button("Update NaN-method"):
NaNoptions = ["None selected","Convert to 0", "Convert to no", "Set empty values to inactive"]
nanMethod = st.selectbox("Update NaN-method",options = NaNoptions)
if st.button("Update method"):
if nanMethod == "None selected":
db.updatevalues("parameter", "nanmethod = ?", "realid = ?",(0,realid))
elif nanMethod == "Convert to 0":
db.updatevalues("parameter", "nanmethod = ?", "realid = ?",(1,realid))
elif nanMethod == "Convert to no":
db.updatevalues("parameter", "nanmethod = ?", "realid = ?",(2,realid))
else:
db.updatevalues("parameter", "nanmethod = ?", "realid = ?",(3,realid))
indicators = db.selectallfrom("indicator")
parameters = db.selectallfrom("parameter")
parameters = parameters[parameters["fk_methodid"] == currentMethod]
questions = db.selectallfrom("question")
valrules = db.selectallfrom("validationrule")
valrules = valrules[valrules["fk_methodid"] == currentMethod]
parid = parameters[parameters["realid"] == realid]['parameterid'].iloc[0]
#ind = questions[questions['realid'] == realid]
#parid = questions[questions['fk_parameterid'] == par].iloc[0]
#print(parid)
temprules = db.selectfromwhere("fk_parameterid,fk_validationruleid","validationruleparameter_map", "fk_parameterid = ?",(int(parid),))
valruleids = temprules['fk_validationruleid'].tolist()
validationrules = valrules[valrules['validationruleid'].isin(valruleids)]
st.write("Other validation rules: ")
rows = selectable_data_table(validationrules[['rule','description']])
if st.button("Delete selected"):
for i in rows:
db.deletevalue("validationruleparameter_map","fk_parameterid = ? and fk_validationruleid = ?", (int(parid),int(validationrules['validationruleid'].iloc[i])))
newRule = st.text_input("New rule")
description = st.text_input("Description")
if st.button("Save rule"):
db.insertvaluessingle("validationrule(rule,description,fk_methodid)","(?,?,?)",(newRule,description,int(currentMethod)))
justAddedrule = db.selectfromwhere("rule,validationruleid","validationrule","rule = ?",(newRule,))
ruleid = justAddedrule['validationruleid'].iloc[0]
db.insertvaluessingle("validationruleparameter_map(fk_parameterid,fk_validationruleid)","(?,?)",(int(parid),int(ruleid)))
def makeInactive(dataid,newValue,alterationid):
data = db.selectallfrom("data")
datadf = data[data['isActive'] == True]
row = datadf[datadf['dataid'] == dataid]
db.updatevalues("data","isActive = ?","dataid = ?",(int(0),int(dataid)))
currentversion = row['version'].iloc[0]
tempsplit = currentversion.split(".")
fineversion = int(tempsplit[1]) + 1
newVersion = tempsplit[0] + "." + str(fineversion)
year = row['year'].iloc[0]
parid = row['fk_parameterid'].iloc[0]
projectid = row['fk_projectid'].iloc[0]
orgid = row['fk_organisationid'].iloc[0]
try:
db.deletevalue('alteration','alterationid = ? and fk_projectid = ?',(int(alterationid),int(currentProject)))
except:
print("Nope this didn't work !")
def organisationupload(projectid):
st.write("Upload your organisation import-file")
datafile = st.file_uploader(type="xlsx", label="Organisations")
try:
df = pd.read_excel(datafile)
st.write(df)
except:
st.write("Please upload your organisation data")
if st.button("Commit"):
colnames = df.columns.tolist()
popIndex = colnames.index("Population")
populations = colnames[popIndex + 1:]
orgidlist = db.insertorganisations(projectid, df)
df["organisationid"] = orgidlist
db.insertpopulations(projectid, populations, df)
db.updatevalues("project", "currentPhase = ?", "projectid = ?",
(int(2), int(projectid)))
st.button("Next import")
def applynanMethod(nanmethod, df):
if nanmethod == 0:
return
elif nanmethod == 1:
for i in range(len(df)):
dataid = df['dataid'].iloc[i]
db.updatevalues("data", "isActive = ?", "dataid = ?",
(int(0), int(dataid)))
versiondf = db.selectfromwhere("dataid,version", "data",
"dataid = ?", (int(dataid), ))
currentversion = versiondf['version'].iloc[0]
tempsplit = currentversion.split(".")
fineversion = int(tempsplit[1]) + 1
newVersion = tempsplit[0] + "." + str(fineversion)
newValue = "0"
year = df['year'].iloc[i]
parid = df['fk_parameterid'].iloc[i]
projectid = df['fk_projectid'].iloc[i]
orgid = df['fk_organisationid'].iloc[i]
db.insertvaluessingle(
"data(value,year,version,isActive,fk_parameterid,fk_projectid,fk_organisationid)",
"(?,?,?,?,?,?,?)", (newValue, year, newVersion, int(1),
int(parid), int(projectid), int(orgid)))
elif nanmethod == 2:
for i in range(len(df)):
dataid = df['dataid'].iloc[i]
db.updatevalues("data", "isActive = ?", "dataid = ?",
(int(0), int(dataid)))
versiondf = db.selectfromwhere("dataid,version", "data",
"dataid = ?", (int(dataid), ))
currentversion = versiondf['version'].iloc[0]
tempsplit = currentversion.split(".")
fineversion = int(tempsplit[1]) + 1
newVersion = tempsplit[0] + "." + str(fineversion)
newValue = "no"
year = df['year'].iloc[i]
parid = df['fk_parameterid'].iloc[i]
projectid = df['fk_projectid'].iloc[i]
orgid = df['fk_organisationid'].iloc[i]
db.insertvaluessingle(
"data(value,year,version,isActive,fk_parameterid,fk_projectid,fk_organisationid)",
"(?,?,?,?,?,?,?)", (newValue, year, newVersion, int(1),
int(parid), int(projectid), int(orgid)))
elif nanmethod == 3:
for i in range(len(df)):
dataid = df['dataid'].iloc[i]
db.updatevalues("data", "isActive = ?", "dataid = ?",
(int(0), int(dataid)))
else:
return
def write():
st.title("Template page")
options = getVisTypes()
visTypeSelected = st.selectbox("Select visualisation type",
options=options)
projects = db.selectallfrom("project")
currentProject = projects[projects["currentProject"] ==
1]['projectid'].iloc[0]
templates = db.selectallfrom("template")
projectTemplates = templates[templates["fk_projectid"] == currentProject]
myTemplateJSON = projectTemplates[projectTemplates['vistype'] ==
visTypeSelected]['templateDict'].iloc[0]
myTemplate = json.loads(myTemplateJSON)
if visTypeSelected == "barchart":
showLegend = st.checkbox("Show/Hide the legend",
value=myTemplate['showlegend'])
theme = getTheme(myTemplate['template'])
colorway = getColorway(myTemplate['colorway'])
data = composeDummyData(visTypeSelected)
figure = constructFigure(data, visTypeSelected)
layout = {
"colorway": colorway,
"showlegend": showLegend,
"template": theme
}
layoutJSON = json.dumps(layout, indent=4)
if st.button("Commit"):
db.updatevalues("template", "templateDict = ?",
"fk_projectid = ? AND vistype = ?",
(layoutJSON, int(currentProject), "barchart"))
figure.update_layout(layout)
st.write(figure)
elif visTypeSelected == "table":
data = composeDummyData(visTypeSelected)
header, cells = constructFigure(data, visTypeSelected)
colorCols = st.beta_columns((1, 1))
headerFillCol = colorCols[0].text_input(
"Header fill", value=myTemplate['headerFillCol'])
headerLineCol = colorCols[1].text_input(
"Header line", value=myTemplate['headerLineCol'])
cellsFillCol = colorCols[0].text_input(
"Cells fill", value=myTemplate['cellsFillCol'])
cellsLineCol = colorCols[1].text_input(
"Cells line", value=myTemplate['cellsLineCol'])
header.update({'fill_color': headerFillCol})
header.update({'line_color': headerLineCol})
cells.update({'fill_color': cellsFillCol})
cells.update({'line_color': cellsLineCol})
layout = {
"headerFillCol": headerFillCol,
"headerLineCol": headerLineCol,
"cellsFillCol": cellsFillCol,
"cellsLineCol": cellsLineCol
}
layoutJSON = json.dumps(layout, indent=4)
if st.button("Commit"):
db.updatevalues("template", "templateDict = ?",
"fk_projectid = ? AND vistype = ?",
(layoutJSON, int(currentProject), "table"))
figure = go.Figure(data=[go.Table(header=header, cells=cells)])
st.write(figure)
elif visTypeSelected == "categories":
print("Jup")
showLegend = st.checkbox("Show/Hide the legend",
value=myTemplate['showlegend'])
theme = getTheme(myTemplate['template'])
print("Jup2")
data = composeDummyData(visTypeSelected)
print("Skrt")
figure = constructFigure(data, visTypeSelected)
print("Jup3")
colorway = getColorway(myTemplate['colorway'])
layout = {
"colorway": colorway,
"showlegend": showLegend,
"template": theme
}
layoutJSON = json.dumps(layout, indent=4)
if st.button("Commit"):
db.updatevalues("template", "templateDict = ?",
"fk_projectid = ? AND vistype = ?",
(layoutJSON, int(currentProject), "categories"))
print(visTypeSelected)
figure.update_layout(layout)
st.write(figure)
