def select_trayek():
db = Database()
from_table = [db.table_name["trayek"]]
select_column = [
db.column_name["trayek"]["nama_trayek"],
db.column_name["trayek"]["jarak"], db.column_name["trayek"]["jumlah"]
]
id_column = [db.column_name["trayek"]["nama_trayek"]]
query = sql.SQL("SELECT {0} "
"FROM {1} "
"ORDER BY nama_trayek ASC").format(
sql.SQL(', ').join(map(sql.Identifier, select_column)),
sql.Identifier(from_table[0]))
try:
result = db.execute(query, [], "fetch")
r_trayek = []
r_jarak = []
r_jumlah = []
if (result is not None):
for i in range(len(result)):
r_trayek.append(result[i][0])
r_jarak.append(result[i][1])
r_jumlah.append(result[i][2])
result_data = {
"trayek": r_trayek,
"jarak": r_jarak,
"jumlah": r_jumlah
}
except Exception as e:
error_log(module_name, e)
else:
return result_data
def select_jalur():
db = Database()
query = sql.SQL(
"SELECT "
"trayek.nama_trayek, "
"ST_Makeline( "
"ST_PointOnSurface(kecamatan.geometry) "
"ORDER BY ST_distance( "
"st_pointonsurface(kecamatan.geometry), "
"st_pointonsurface(kecamatan.geometry) "
") "
") "
"FROM kecamatan "
"JOIN kecamatan_trayek ON kecamatan_trayek.id_kecamatan = kecamatan.object_id "
"JOIN trayek ON kecamatan_trayek.id_trayek = trayek.id_trayek "
"GROUP BY trayek.nama_trayek "
"ORDER BY nama_trayek")
try:
result = db.execute(query, [], "fetch")
r_trayek = []
r_jalur = []
if (result is not None):
for i in range(len(result)):
r_trayek.append(result[i][0])
r_jalur.append(result[i][1])
result_data = {"trayek": r_trayek, "jalur": r_jalur}
except Exception as e:
error_log(module_name, e)
else:
return result_data
def select_kecamatan():
db = Database()
from_table = [db.table_name["kecamatan"]]
select_column = [
db.column_name["kecamatan"]["kecamatan"],
db.column_name["kecamatan"]["geometry"]
]
id_column = [db.column_name["kecamatan"]["kecamatan"]]
query = sql.SQL("SELECT kecamatan.kecamatan, ST_AsGeoJSON({1}) "
"FROM {2} "
"ORDER BY kecamatan.kecamatan ASC").format(
sql.Identifier(select_column[0]),
sql.Identifier(select_column[1]),
sql.Identifier(from_table[0]))
try:
result = db.execute(query, [], "fetch")
r_kecamatan = []
r_geojson = []
if (result is not None):
for i in range(len(result)):
r_kecamatan.append(result[i][0])
r_geojson.append(result[i][1])
result_data = {"kecamatan": r_kecamatan, "geojson": r_geojson}
except Exception as e:
error_log(module_name, e)
else:
return result_data
def select_sekolah(kecamatan):
db = Database()
from_table = [db.table_name["sekolah"], db.table_name["kecamatan"]]
select_column = [
db.column_name["kecamatan"]["kecamatan"],
db.column_name["sekolah"]["jumlah_pd"],
"ST_PointOnSurface({0})".format(
db.column_name["kecamatan"]["geometry"])
]
join_column = [
db.column_name["sekolah"]["id_kecamatan"],
db.column_name["kecamatan"]["object_id"]
]
id_column = [db.column_name["kecamatan"]["kecamatan"]]
query = sql.SQL("SELECT {0} "
"FROM {1} JOIN {2} ON {3} = {4}"
"WHERE {5} IN %s").format(
sql.SQL(', ').join(map(sql.Identifier, select_column)),
sql.Identifier(from_table[0]),
sql.Identifier(from_table[1]),
sql.Identifier(join_column[0]),
sql.Identifier(join_column[1]),
sql.Identifier(id_column[0]))
try:
result = db.execute(query, [kecamatan], "fetch")
result_data = {}
if (result[0] is not None):
for data in result:
result_data[data[0]] = data[0]
except Exception as e:
error_log(module_name, e)
else:
return result_data
def select_total_angkot():
db = Database()
query = sql.SQL("SELECT SUM(jumlah) FROM trayek")
try:
result = db.execute(query, [], "fetch")
except Exception as e:
error_log(module_name, e)
else:
return result[0][0]
def send_http_request():
info_log(module_name, "START SEND REQUEST")
while True:
try:
time.sleep(1740)
contents = urllib.request.urlopen(
"http://dashboard-angkot-to-school.herokuapp.com/").read()
info_log(module_name, contents)
except Exception as e:
error_log(module_name, e)
info_log(module_name, "END SEND REQUEST")
sys.exit()
def get_column(self, table):
from_table = [table]
query = sql.SQL("SELECT * FROM {0} LIMIT 0").format(
sql.Identifier(from_table[0]))
try:
result = self.execute(query, [], "cursor")
result_data = {}
if (result.description is not None):
for desc in result.description:
result_data[desc[0]] = desc[0]
except Exception as e:
error_log(module_name, e)
else:
return result_data
def get_table(self):
from_table = ["pg_class"]
select_column = ["relname"]
id_column = ["relkind", "relname"]
query = sql.SQL(
"SELECT {0} FROM {1} WHERE {2} = %s AND {3} !~ %s").format(
sql.SQL(', ').join(map(sql.Identifier, select_column)),
sql.Identifier(from_table[0]), sql.Identifier(id_column[0]),
sql.Identifier(id_column[1]))
try:
result = self.execute(query, ['r', '^(pg_|sql_)'], "fetch")
result_data = {}
if (result[0] is not None):
for data in result:
result_data[data[0]] = data[0]
except Exception as e:
error_log(module_name, e)
else:
return result_data
def execute(self, query, values, query_type: str):
try:
conn = psycopg2.connect(self.DATABASE_URL)
cur = conn.cursor()
if (len(values) == 0):
cur.execute(query)
else:
cur.execute(query, values)
except Exception as e:
error_log(module_name, e)
else:
if (query_type == "commit"):
conn.commit()
elif (query_type == "fetch"):
return cur.fetchall()
elif (query_type == "cursor"):
return cur
finally:
conn.close()
def select_angkot_per_kecamatan():
db = Database()
query = sql.SQL(
"SELECT "
"kecamatan.kecamatan, "
"kecamatan.luas, "
"SUM(trayek.jumlah) * (kecamatan.luas / total_luas.total) as jumlah_angkot, "
"trayek.nama_trayek "
"FROM kecamatan "
"JOIN kecamatan_trayek ON kecamatan_trayek.id_kecamatan=kecamatan.object_id "
"JOIN trayek ON kecamatan_trayek.id_trayek=trayek.id_trayek "
"JOIN ( "
"SELECT SUM(kecamatan.luas) as total, trayek.nama_trayek "
"FROM kecamatan "
"JOIN kecamatan_trayek ON kecamatan_trayek.id_kecamatan = kecamatan.object_id "
"JOIN trayek ON kecamatan_trayek.id_trayek = trayek.id_trayek "
"GROUP BY trayek.nama_trayek "
") AS total_luas ON total_luas.nama_trayek = trayek.nama_trayek "
"GROUP BY kecamatan.kecamatan, kecamatan.luas, kecamatan.geometry, total_luas.total, trayek.nama_trayek "
"ORDER BY kecamatan.kecamatan")
try:
result = db.execute(query, [], "fetch")
r_kecamatan = []
r_luas = []
r_angkot = []
r_trayek = []
if (result is not None):
for i in range(len(result)):
r_kecamatan.append(result[i][0])
r_luas.append(result[i][1])
r_angkot.append(result[i][2])
r_trayek.append(result[i][3])
result_data = {
"kecamatan": r_kecamatan,
"luas": r_luas,
"trayek": r_trayek,
"angkot": r_angkot
}
except Exception as e:
error_log(module_name, e)
else:
return result_data
def select_kebutuhan_total(kapasitas, limit):
db = Database()
query = sql.SQL(
"SELECT "
"kecamatan.kecamatan, "
"ROUND(CAST((sekolah.jumlah_pd/%s)/kecamatan.luas as numeric), 2) as jumlah_pd, "
"ROUND(CAST(SUM(trayek.jumlah)/kecamatan.luas as numeric), 2) as jumlah_angkot, "
"ROUND(CAST(SUM(trayek.jumlah)-(sekolah.jumlah_pd/%s)/kecamatan.luas as numeric), 2) as kebutuhan "
"FROM sekolah "
"JOIN kecamatan ON sekolah.id_kecamatan=kecamatan.object_id "
"JOIN kecamatan_trayek ON kecamatan_trayek.id_kecamatan=sekolah.id_kecamatan "
"JOIN trayek ON kecamatan_trayek.id_trayek=trayek.id_trayek "
"GROUP BY kecamatan.kecamatan, sekolah.jumlah_pd, kecamatan.luas "
"ORDER BY kebutuhan ASC LIMIT %s")
try:
result = db.execute(query, [kapasitas, kapasitas, limit], "fetch")
r_kecamatan = []
r_jumlah_pd = []
r_jumlah_angkot = []
r_kebutuhan = []
if (result is not None):
for i in range(len(result)):
r_kecamatan.append(result[i][0])
r_jumlah_pd.append(result[i][1])
r_jumlah_angkot.append(result[i][2])
r_kebutuhan.append(result[i][3])
result_data = {
"kecamatan": r_kecamatan,
"siswa": r_jumlah_pd,
"angkot": r_jumlah_angkot,
"kebutuhan": r_kebutuhan,
}
except Exception as e:
error_log(module_name, e)
else:
return result_data
jml_angkot = jml_angkot + angkot[index]["angkot"][j]
spatial.append([
siswa["kecamatan"][i],
json.loads(siswa['geojson'][i]), siswa["total"][i], jml_angkot
])
return render_template('index.html',
kecamatan=kecamatan,
trayek=trayek,
angkot_total=angkot_total,
siswa_total=siswa_total,
siswa=siswa,
angkot=angkot,
spatial=spatial)
#return render_template('index.html')
if __name__ == '__main__':
try:
info_log(module_name, "START SERVER")
multi_process = mp.Process(target=send_http_request)
multi_process.start()
app.run(host='0.0.0.0', debug=False, port=environ.get("PORT", 5000))
multi_process.join()
except Exception as e:
multi_process.close()
error_log(module_name, e)
info_log(module_name, "END SERVER")
os.execv(sys.executable, [sys.executable, 'app.py'] + sys.argv)
sys.exit()
