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)