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_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_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_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
class TestDatabase(unittest.TestCase):
@classmethod
def setUpClass(cls):
pass
@classmethod
def tearDownClass(cls):
pass
def setUp(self):
self.db = Database()
def tearDown(self):
pass
def test_execute(self):
# exist
pass
# non-exist
# SQL Inject
# None
def test_get_table(self):
self.assertIn("sekolah", self.db.get_table())
def test_get_column(self):
# exist
self.assertIn("jumlah_pd", self.db.get_column("sekolah"))
# non-exist-column
self.assertNotIn("jumlah_pd", self.db.get_column("kecamatan"))
# invalid
invalid_inputs = ["non_exist_table", "1' or '1'='1", None]
for i in range(len(invalid_inputs)):
with self.subTest(i=i):
pass
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 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
def setUp(self):
self.db = Database()