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()