def cityTotalElect(): x = dbutil.runSQLAsDict("""SELECT SUM(Consumption) as tot from ELECTRICITYREPORT""") if x: return x else: return "N/A"
def cityTotalWater(): x = dbutil.runSQLAsDict("""SELECT SUM(Consumption) as tot from WaterReport""") if x: return x else: return "N/A"
def cityAvgWater(): x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avg from WaterReport""") if x: return x else: return "N/A"
def cityAvgElect(): x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avg from ELECTRICITYREPORT""") if x: return x else: return "N/A"
def cityAvgNatGas(): x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avg from NaturalGasReport""") if x: return x else: return "N/A"
def cityTotalNatGas(): x = dbutil.runSQLAsDict("""SELECT SUM(Consumption) as tot from NaturalGasReport""") if x: return x else: return "N/A"
def cityTotalWater(): x = dbutil.runSQLAsDict( """SELECT SUM(Consumption) as tot from WaterReport""") if x: return x else: return "N/A"
def cityAvgWater(): x = dbutil.runSQLAsDict( """SELECT AVG(Consumption) as avg from WaterReport""") if x: return x else: return "N/A"
def totalTuples(): totAddress = dbutil.runSQLAsDict( """SELECT Count(ID) as cAd from address""") totElect = dbutil.runSQLAsDict( """SELECT Count(ID) as cEl from Electricityreport""") totNatGas = dbutil.runSQLAsDict( """SELECT Count(ID) as cNa from NaturalGasReport""") totCodeVio = dbutil.runSQLAsDict( """SELECT Count(ID) as cCoVi from CodeViolationsReport""") totWater = dbutil.runSQLAsDict( """SELECT Count(ID) as cWa from WaterReport""") # total = totAddress[0].cAD + totElect[0].cEL + totNatGas[0].cNA + totWater[0].cWA total2 = totAddress + totElect + totNatGas + totCodeVio + totWater return total2
def findOneByID(id): x = dbutil.runSQLAsDict("""SELECT * FROM ADDRESS WHERE ADDRESS.ID = {}""".format(id)) if x: return x else: return "N/A"
def getInfo(addrs): x = dbutil.runSQLAsDict("""SELECT id, Streetaddress, coord_lat, coord_lon from ADDRESS Where streetaddress = {}""".format( '\''+ addrs+'\'')) if x: return x else: return "N/A"
def getId(addrs): x = dbutil.runSQLAsDict("""SELECT ADDRESS.ID FROM ADDRESS WHERE ADDRESS.StreetAddress = {}""".format( '\''+ addrs+'\'')) if x: return x else: return "N/A"
def findOneByID(id): x = dbutil.runSQLAsDict("""SELECT * FROM ADDRESS WHERE ADDRESS.ID = {}""".format(id)) if x: return x else: return "N/A"
def cityTotalElect(): x = dbutil.runSQLAsDict( """SELECT SUM(Consumption) as tot from ELECTRICITYREPORT""") if x: return x else: return "N/A"
def getId(addrs): x = dbutil.runSQLAsDict("""SELECT ADDRESS.ID FROM ADDRESS WHERE ADDRESS.StreetAddress = {}""".format('\'' + addrs + '\'')) if x: return x else: return "N/A"
def cityAvgElect(): x = dbutil.runSQLAsDict( """SELECT AVG(Consumption) as avg from ELECTRICITYREPORT""") if x: return x else: return "N/A"
def cityAvgNatGas(): x = dbutil.runSQLAsDict( """SELECT AVG(Consumption) as avg from NaturalGasReport""") if x: return x else: return "N/A"
def cityTotalNatGas(): x = dbutil.runSQLAsDict( """SELECT SUM(Consumption) as tot from NaturalGasReport""") if x: return x else: return "N/A"
def getInfo(addrs): x = dbutil.runSQLAsDict( """SELECT id, Streetaddress, coord_lat, coord_lon from ADDRESS Where streetaddress = {}""".format('\'' + addrs + '\'')) if x: return x else: return "N/A"
def findCodeVio(addrs): x = dbutil.runSQLAsDict("""SELECT Violation, CaseType, Inspector from CodeViolationsreport, address where address.id = CodeViolationsreport.ADDRESS_ID and address.STREETADDRESS = {}""".format( '\'' + addrs + '\'')) if x: return x else: return "N/A"
def findNatGas(addrs): x = dbutil.runSQLAsDict("""SELECT Consumption from NaturalGasReport, Address Where Address.ID = NaturalGasReport.ADDRESS_ID AND Address.StreetAddress = {}""".format( '\'' + addrs + '\'')) if x: return x else: return "N/A"
def avgNumSeven(): return dbutil.runSQLAsDict("""SELECT avg(electricityreport.consumption) as ElectAvg, avg(waterreport.consumption) as WaterAvg, avg(naturalgasreport.consumption) as NatGasAvg from ELECTRICITYREPORT, waterreport, address, naturalgasreport where ELECTRICITYREPORT.ADDRESS_ID = address.id AND Waterreport.address_id = address.id and NaturalGasReport.address_id = address.id and address.streetaddress LIKE '7 %'""")
def findElectricity(addrs): x = dbutil.runSQLAsDict("""SELECT Consumption from ELECTRICITYREPORT, Address Where Address.ID = ELECTRICITYREPORT.ADDRESS_ID AND Address.StreetAddress = {}""".format( '\'' + addrs + '\'')) if x: return x else: return "N/A"
def findCodeVio(addrs): x = dbutil.runSQLAsDict( """SELECT Violation, CaseType, Inspector from CodeViolationsreport, address where address.id = CodeViolationsreport.ADDRESS_ID and address.STREETADDRESS = {}""".format('\'' + addrs + '\'')) if x: return x else: return "N/A"
def findElectricity(addrs): x = dbutil.runSQLAsDict( """SELECT Consumption from ELECTRICITYREPORT, Address Where Address.ID = ELECTRICITYREPORT.ADDRESS_ID AND Address.StreetAddress = {}""".format('\'' + addrs + '\'')) if x: return x else: return "N/A"
def findNatGas(addrs): x = dbutil.runSQLAsDict( """SELECT Consumption from NaturalGasReport, Address Where Address.ID = NaturalGasReport.ADDRESS_ID AND Address.StreetAddress = {}""".format('\'' + addrs + '\'')) if x: return x else: return "N/A"
def avgNumSix(): return dbutil.runSQLAsDict( """SELECT avg(electricityreport.consumption) as ElectAvg, avg(waterreport.consumption) as WaterAvg, avg(naturalgasreport.consumption) as NatGasAvg from ELECTRICITYREPORT, waterreport, address, naturalgasreport where ELECTRICITYREPORT.ADDRESS_ID = address.id AND Waterreport.address_id = address.id and NaturalGasReport.address_id = address.id and address.streetaddress LIKE '6 %'""")
def streetCodeVio(addrs): newAddrs = addrs.split(' ', 1) x = dbutil.runSQLAsDict("""SELECT Violation, CaseType, Inspector from CodeViolationsreport, Address Where Address.ID = CodeViolationsreport.ADDRESS_ID and Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\'')) if x: return x else: return "N/A"
def streetWater(addrs): newAddrs = addrs.split(' ', 1) x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avgSt from WaterReport, Address Where Address.ID = WaterReport.ADDRESS_ID and Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\'')) if x: return x else: return "N/A"
def streetCodeVio(addrs): newAddrs = addrs.split(' ', 1) x = dbutil.runSQLAsDict( """SELECT Violation, CaseType, Inspector from CodeViolationsreport, Address Where Address.ID = CodeViolationsreport.ADDRESS_ID and Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\'')) if x: return x else: return "N/A"
def streetElectricity(addrs): newAddrs = addrs.split(' ', 1) x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avgSt from ELECTRICITYREPORT, Address Where Address.ID = ElectricityReport.ADDRESS_ID and Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\'')) if x: return x else: return "N/A"
def streetWater(addrs): newAddrs = addrs.split(' ', 1) x = dbutil.runSQLAsDict( """SELECT AVG(Consumption) as avgSt from WaterReport, Address Where Address.ID = WaterReport.ADDRESS_ID and Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\'')) if x: return x else: return "N/A"
def streetElectricity(addrs): newAddrs = addrs.split(' ', 1) x = dbutil.runSQLAsDict( """SELECT AVG(Consumption) as avgSt from ELECTRICITYREPORT, Address Where Address.ID = ElectricityReport.ADDRESS_ID and Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\'')) if x: return x else: return "N/A"
def maxCityWater(): return dbutil.runSQLAsDict("""SELECT streetaddress, consumption from address, waterreport where ( select Max(Consumption) from waterreport) = waterreport.consumption AND address.id = waterreport.address_id""")
def maxCityWater(): return dbutil.runSQLAsDict("""SELECT streetaddress, consumption from address, waterreport where ( select Max(Consumption) from waterreport) = waterreport.consumption AND address.id = waterreport.address_id""")
def potentialLandscapingCustomers(): return dbutil.runSQLAsDict("""SELECT Count(violation) as LawnsInNeed from codeviolationsreport where violation = 'Overgrown Yard / Weeds'""")
def maxCityElect(): return dbutil.runSQLAsDict("""SELECT streetaddress, consumption from address, electricityreport where ( select Max(Consumption) from electricityreport) = electricityreport.consumption AND address.id = electricityreport.address_id""")
def potentialLandscapingCustomers(): return dbutil.runSQLAsDict( """SELECT Count(violation) as LawnsInNeed from codeviolationsreport where violation = 'Overgrown Yard / Weeds'""")
def fowlPlay(): return dbutil.runSQLAsDict( """SELECT count(id) as from CODEVIOLATIONSREPORT where VIOLATION = 'Fowl or Livestock Prohibited'""")
def treeSqueezers(): return dbutil.runSQLAsDict( """SELECT count(id) as from CODEVIOLATIONSREPORT where VIOLATION = 'Dead or Hazardous Trees'""")
def fowlPlay(): return dbutil.runSQLAsDict("""SELECT count(id) as from CODEVIOLATIONSREPORT where VIOLATION = 'Fowl or Livestock Prohibited'""")
def maxCityNatGas(): return dbutil.runSQLAsDict("""SELECT streetaddress, consumption from address, naturalgasreport where ( select Max(Consumption) from naturalgasreport) = naturalgasreport.consumption AND address.id = naturalgasreport.address_id""")
def treeSqueezers(): return dbutil.runSQLAsDict("""SELECT count(id) as from CODEVIOLATIONSREPORT where VIOLATION = 'Dead or Hazardous Trees'""")
def maxCityNatGas(): return dbutil.runSQLAsDict("""SELECT streetaddress, consumption from address, naturalgasreport where ( select Max(Consumption) from naturalgasreport) = naturalgasreport.consumption AND address.id = naturalgasreport.address_id""")
def maxCityElect(): return dbutil.runSQLAsDict("""SELECT streetaddress, consumption from address, electricityreport where ( select Max(Consumption) from electricityreport) = electricityreport.consumption AND address.id = electricityreport.address_id""")