def createNewMethod(name, description):
db.insertvaluessingle("method(name,description)", "(?,?)",
(name, description))
methods = db.selectallfrom('method')
currentMethod = methods[methods['name'] == name]
methodid = currentMethod['methodid'].iloc[len(currentMethod) - 1]
return methodid
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 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 newProject(projectName, projectYear, aggMethodName, aggMethodDesc):
methodid = createNewMethod(aggMethodName, aggMethodDesc)
users = db.selectallfrom("user")
userid = users['userid'].iloc[0]
db.insertvaluessingle(
"project(name,fk_userid,fk_methodid,currentPhase,currentYear,currentProject)",
"(?,?,?,?,?,?)",
(projectName, int(userid), int(methodid), int(1), projectYear, 0))
projects = db.selectallfrom('project')
currentProject = projects[projects['name'] == projectName]
projectid = currentProject['projectid'].iloc[len(currentProject) - 1]
changeCurrentProject(projectid)
templates = db.selectallfrom("template")
tempTable = templates[templates['vistype'] ==
"table"]['templateDict'].iloc[0]
tempCat = templates[templates['vistype'] ==
"categories"]['templateDict'].iloc[0]
tempBar = templates[templates['vistype'] ==
"barchart"]['templateDict'].iloc[0]
db.insertvaluessingle("template(vistype,templateDict,fk_projectid)",
"(?,?,?)", ("table", tempTable, int(projectid)))
db.insertvaluessingle("template(vistype,templateDict,fk_projectid)",
"(?,?,?)", ("categories", tempCat, int(projectid)))
db.insertvaluessingle("template(vistype,templateDict,fk_projectid)",
"(?,?,?)", ("barchart", tempBar, int(projectid)))
def write():
st.title("Validation")
columns = ["dataid","rule","value","parameterid"]
# Get all the alterations that are going to be done
alterations = pd.DataFrame(columns = columns)
parameters = get_indicators()
if st.button("Validate!"):
for i in range(len(parameters)):
realid = parameters[i][0]
parameteriddf = db.selectfromwhere("realid,fk_methodid,parameterid","parameter","realid = ? and fk_methodid = ?",(realid,int(currentMethod),))
parameterid = parameteriddf['parameterid'].iloc[0]
data = db.selectallfrom("data")
datadf = data[data['fk_parameterid'] == parameterid]
datadf = datadf[datadf['isActive'] == 1]
nanmethoddf = db.selectfromwhere("parameterid,nanmethod","parameter","parameterid = ?",(int(parameterid),))
nanmethod = nanmethoddf['nanmethod'].iloc[0]
nandf = datadf[datadf['value'] == "nan"]
print("Nandf: " + str(len(nandf)))
if len(nandf) > 0:
if nanmethod == 0:
st.write("There are several NaN values in the data for this indicator, but there has not been a method selected yet how to handle these. Please do so under the validation rules section and come back to this page!")
valparse.applynanMethod(nanmethod,nandf)
alterations = validateparameter(parameterid,alterations,columns)
if len(alterations) > 0:
for j in range(len(alterations)):
val = alterations['value'].iloc[j]
dataid = alterations['dataid'].iloc[j]
parid = alterations['parameterid'].iloc[j]
rule = alterations['rule'].iloc[j]
description = "Rule: " + rule
db.insertvaluessingle("alteration(old_val,new_val,description,fk_dataid,fk_projectid)","(?,?,?,?,?)", (val,"?",description,int(dataid),int(currentProject)))
else:
st.write("All good! According to the validation rules, there are no wrong values.")
alts = db.selectallfrom("alteration")
alts = alts[alts['fk_projectid'] == currentProject]
if len(alts) > 0:
interactivetable(alts)
else:
st.write("Currently no flagged alterations!")
def updateValue(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.insertvaluessingle("data(value,year,version,isActive,fk_parameterid,fk_projectid,fk_organisationid)","(?,?,?,?,?,?,?)",(newValue,year,newVersion,int(1),int(parid),int(projectid),int(orgid)))
db.deletevalue('alteration','alterationid = ? and fk_projectid = ?',(int(alterationid),int(currentProject)))
except:
print("Nope this didn't work !")
def computeInds(indicators):
organisations = db.selectallfrom("organisation")
organisations = organisations[organisations['fk_projectid'] == currentProject]
errorList = [(0,0)]
for i in range(len(organisations)):
orgid = organisations['organisationid'].iloc[i]
print("Current organisation: " + str(orgid) + " - " + str(i) + " of " + str(len(organisations)))
for j in range(len(indicators)):
realid = indicators['realid'].iloc[j]
formula = indicators['formula'].iloc[j]
parameterid = indicators['fk_parameterid'].iloc[j]
try:
value = calculateIndicator(orgid,formula)
except:
value = "nan"
errorList.append((orgid,parameterid))
if str(value) == "nan":
db.insertvaluessingle("data(value,year,version,isActive,fk_parameterid,fk_projectid,fk_organisationid)","(?,?,?,?,?,?,?)",(value,2019,"1.0",0,int(parameterid),int(currentProject),int(orgid)))
else:
db.insertvaluessingle("data(value,year,version,isActive,fk_parameterid,fk_projectid,fk_organisationid)","(?,?,?,?,?,?,?)",(value,2019,"1.0",1,int(parameterid),int(currentProject),int(orgid)))