def exportBaselineBills():
if (current_user.is_authenticated == False):
return redirect("/login")
if (request.is_json == False):
return "Wrong data format!"
content = request.get_json()
scenarioID = content["scenarioID"]
bills = content["bils"]
mycursor = mydb.connection.cursor(MySQLdb.cursors.DictCursor)
mycursor.execute(
"SELECT BillName, BaselineBill, CurrentBill FROM BasicOutput WHERE scenarioID = %s order by BillName asc"
% scenarioID)
result = mycursor.fetchall()
kpis = []
for row in result:
bill = {}
bill["name"] = row["BillName"]
bill["base"] = row["BaselineBill"]
bill["curr"] = row["CurrentBill"]
for elem in bills:
if (elem["month"] == bill["name"]):
bill["base"] = elem["bill"]
break
kpis.append(bill)
performanceIndicators.computeKPIs(kpis, scenarioID)
mycursor.execute("DELETE FROM BasicOutput WHERE ScenarioID = %s" %
scenarioID)
mydb.connection.commit()
for bill in kpis:
mycursor.execute(
"insert into BasicOutput ( BillName," + "BaselineBill," +
"CurrentBill," + "SavingsMU," + "SavingsPercent," +
"AmountReturned," + "AmountYetToBeReturned," + "ROI," + "IRR," +
"NPV," + "ESCO," + "Client," +
"ScenarioID) values ('%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
(bill["name"], bill["base"], bill["curr"], bill["savingsMU"],
bill["savingsPercent"], bill["amntRet"], bill["rest"],
bill["roi"], bill["irr"], bill["npv"], bill["esco"],
bill["client"], scenarioID))
mydb.connection.commit()
return "succes"
def changeBill():
mycursor = mydb.connection.cursor(MySQLdb.cursors.DictCursor)
content = request.get_json()
billID = content["billID"]
scenarioID = content["scenarioID"]
name = content["name"]
base = float(content["base"])
curr = float(content["curr"])
mycursor.execute("SELECT OutputID, BillName, BaselineBill, CurrentBill FROM BasicOutput WHERE ScenarioID = %s ORDER BY BillName ASC" % scenarioID)
rows = mycursor.fetchall()
kpis = []
for row in rows:
bill = {}
bill["name"] = row["BillName"]
bill["id"] = row["OutputID"]
if(row["BillName"] == name):
bill["base"] = base
bill["curr"] = curr
else:
bill["base"] = row["BaselineBill"]
bill["curr"] = row["CurrentBill"]
kpis.append(bill)
performanceIndicators.computeKPIs(kpis, scenarioID)
for bill in kpis:
querystr = (("update BasicOutput set BillName = '%s',"+
"BaselineBill = %s,"+
"CurrentBill = %s,"+
"SavingsMU = %s,"+
"SavingsPercent = %s,"+
"AmountReturned = %s,"+
"AmountYetToBeReturned = %s,"+
"ROI = %s,"+
"IRR = %s,"+
"NPV = %s,"+
"ESCO = %s,"+
"Client = %s "+
"where OutputID = %s") %
(bill["name"],
bill["base"],
bill["curr"],
bill["savingsMU"],
bill["savingsPercent"],
bill["amntRet"],
bill["rest"],
bill["roi"],
bill["irr"],
bill["npv"],
bill["esco"],
bill["client"],
bill["id"]))
mycursor.execute(querystr)
mydb.connection.commit()
# if(math.isnan(bill["irr"])):
# bill["irr"] = "None"
return json.dumps(kpis)
def updateScenario():
print(request.is_json)
content = request.get_json()
mycursor = mydb.connection.cursor(MySQLdb.cursors.DictCursor)
mycursor.execute(("update Scenarios set Name = '%s', "+
"Value = %s, "+
"ValueType = '%s', "+
"Duration = %s, "+
"DurationType = '%s', "+
"Description = '%s', " +
"StartDate = '%s' " +
" where ID = %s") %
(
content['name'],
content['value'],
content['valueType'],
content['duration'],
content['durationType'],
content['description'],
content['startDate'],
content['id']
))
mydb.connection.commit()
scenarioID = content['id']
startDate = content['startDate']
duration = int(content['duration'])
if(content['durationType'] == "Years"):
duration = duration * 12
split = startDate.split("-")
year = int(split[0])
month = int(split[1])
kpis = []
for i in range(0,int(duration)):
bill = {}
bill["name"] = str(year) + "-" + '%02d' % month
bill["base"] = 0
bill["curr"] = 0
mycursor.execute("select BaselineBill, CurrentBill from BasicOutput where ScenarioID = %s and BillName = '%s'" % (scenarioID, bill["name"]))
rows = mycursor.fetchall()
if(len(rows) == 0):
bill["base"] = 0
bill["curr"] = 0
else:
bill["base"] = rows[0]["BaselineBill"]
bill["curr"] = rows[0]["CurrentBill"]
kpis.append(bill)
month = month + 1
if(month == 13):
month = 1
year = year + 1
performanceIndicators.computeKPIs(kpis, scenarioID)
mycursor.execute("DELETE FROM BasicOutput WHERE ScenarioID = %s" % scenarioID)
mydb.connection.commit()
for bill in kpis:
mycursor.execute("insert into BasicOutput ( BillName," +
"BaselineBill,"+
"CurrentBill,"+
"SavingsMU,"+
"SavingsPercent,"+
"AmountReturned,"+
"AmountYetToBeReturned,"+
"ROI,"+
"IRR,"+
"NPV,"+
"ESCO,"+
"Client,"+
"ScenarioID) values ('%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" %
(bill["name"],
bill["base"],
bill["curr"],
bill["savingsMU"],
bill["savingsPercent"],
bill["amntRet"],
bill["rest"],
bill["roi"],
bill["irr"],
bill["npv"],
bill["esco"],
bill["client"],
scenarioID))
mydb.connection.commit()
return "Succes"
def addNewScenario():
print(request.is_json)
content = request.get_json()
mycursor = mydb.connection.cursor(MySQLdb.cursors.DictCursor)
mycursor.execute("insert into Scenarios (UserID, Name, Value, ValueType, Duration, DurationType, Description, StartDate) " +
" values (%s,'%s',%s,'%s',%s,'%s','%s','%s')" %
(
current_user.id,
content['name'],
content['value'],
content['valueType'],
content['duration'],
content['durationType'],
content['description'],
content['startDate']
))
rowid = mycursor.lastrowid
mydb.connection.commit()
scenarioID = rowid
startDate = content['startDate']
duration = int(content['duration'])
if(content['durationType'] == "Years"):
duration = duration * 12
split = startDate.split("-")
year = int(split[0])
month = int(split[1])
kpis = []
for i in range(0,int(duration)):
bill = {}
bill["name"] = str(year) + "-" + '%02d' % month
bill["base"] = 0
bill["curr"] = 0
kpis.append(bill)
month = month + 1
if(month == 13):
month = 1
year = year + 1
performanceIndicators.computeKPIs(kpis, scenarioID)
for bill in kpis:
mycursor.execute(("insert into BasicOutput ( BillName," +
"BaselineBill,"+
"CurrentBill,"+
"SavingsMU,"+
"SavingsPercent,"+
"AmountReturned,"+
"AmountYetToBeReturned,"+
"ROI,"+
"IRR,"+
"NPV,"+
"ESCO,"+
"Client,"+
"ScenarioID) values ('%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)") %
(bill["name"],
bill["base"],
bill["curr"],
bill["savingsMU"],
bill["savingsPercent"],
bill["amntRet"],
bill["rest"],
bill["roi"],
bill["irr"],
bill["npv"],
bill["esco"],
bill["client"],
scenarioID))
mydb.connection.commit()
return 'rowid:' + str(rowid)