def upload(): to_load = {} with open('/tmp/svrdata.pkl', 'rb') as f: to_load = cPickle.load(f) for d in to_load.get('pos_to_put', []): try: agent = Agent.select(Agent.q.AgentName == d['name'])[0] except IndexError: try: agent = Agent(**d['data']) # karena 'id'nya tidak standar, # SQLObject bilang Not Found except SQLObjectNotFound: agent = Agent.select(Agent.q.AgentName == d['name'])[0] # Periksa apakah table pemuat Logs untuk # pos ini telah tersedia try: rs = conn.queryAll("SELECT SamplingDate FROM %s \ LIMIT 0, 1" % (agent.table_name)) except: rs = conn.queryAll("CREATE TABLE %s \ LIKE tpl_agent" % agent.table_name) print agent.AgentName for l in d['logs']: print '\t', l['SamplingDate'], l['SamplingTime'] sql = "SELECT COUNT(*) FROM %s \ WHERE SamplingDate='%s' AND \ SamplingTime='%s'" % (agent.table_name, l['SamplingDate'], l['SamplingTime']) rs = conn.queryAll(sql) print '\t\trs[0][0]:', rs[0][0] if rs[0][0] == 0: sql = "INSERT INTO %s (RID, ReceivedDate, \ ReceivedTime, DataType, StatusPort, \ SamplingDate, SamplingTime, Temperature, \ Humidity, Rain, Rain1, Rain2, Rain3, \ Rain4, WLevel, Wlevel1, WLevel2, \ WLevel3, WLevel4, up_since, sq) VALUES (%s, '%s', \ '%s', %s, '%s', '%s', '%s', %s, %s, \ %s, %s, %s, %s, %s, %s, %s, %s, %s, \ %s, '%s', %s)" % ( agent.table_name, l['RID'], l['ReceivedDate'], l['ReceivedTime'], l['DataType'], l['StatusPort'], l['SamplingDate'], l['SamplingTime'], l['Temperature'], l['Humidity'], l['Rain'], l['Rain1'], l['Rain2'], l['Rain3'], l['Rain4'], l['WLevel'], l['WLevel1'], l['WLevel2'], l['WLevel3'], l['WLevel4'], l['up_since'], l['sq']) rs = conn.query(sql) print '\tsaved:', l['SamplingDate'], l['SamplingTime'] del l
def POST(self, table_name): try: pos = [ a for a in AgentTma.select(AgentTma.q.AgentType == 2) if a.table_name == table_name ][0] except IndexError: return web.notfound() inp = web.input() sql = "SELECT id FROM tma WHERE agent_id=%s AND waktu='%s' AND jam='%s'" % ( pos.id, to_date(inp.waktu), inp.jam) rs = conn.queryAll(sql) print 'pos.id: ', pos.id if pos.id >= 200 and pos.id not in [231, 233]: inp_manual = float(inp.tma) - pos.DPL else: inp_manual = float(inp.tma) print pos.sqlmeta.asDict() if not rs: tma = TinggiMukaAir(agent=pos, waktu=to_date(inp.waktu), jam=inp.jam, manual=inp_manual) #pub_object(tma) return web.redirect('/adm/tma/' + table_name, absolute=True)
def GET(self): '''''' web.header('Content-Type', 'application/json') web.header('Access-Control-Allow-Origin', '*') inp = web.input() sampling = inp.get('sampling') fields = 'curahhujan,tma6,vol6,tma12,vol12,tma18,vol18,inflow_q,inflow_v,intake_q,intake_v,spillway_q,spillway_v,vnotch_tin1,vnotch_q1,vnotch_tin2,vnotch_q2,vnotch_tin3,vnotch_q3,a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4,a5,b5,c5'.split(',') n = datetime.datetime.now() waktu = datetime.datetime(n.year, n.month, n.day) if sampling: d = to_date(sampling) waktu = datetime.datetime(d.year, d.month, d.day) wds = [d for d in WadukDaily.select(WadukDaily.q.waktu==waktu)] out = [] for d in wds: row = dict([(a, d.sqlmeta.asDict().get(a)) for a in fields]) row.update({'sampling': str(d.waktu), 'name': d.pos.table_name}) if d.pos and d.pos.prima_id: sql = "SELECT CONCAT(SamplingDate, ' ', SamplingTime) \ AS sampling, WLevel * 0.01 \ FROM %s \ WHERE CONCAT(SamplingDate, ' ', SamplingTime) <= NOW() \ ORDER BY SamplingDate DESC, SamplingTime DESC \ LIMIT 0, 1" % d.pos.table_name rst = conn.queryAll(sql) if rst: row.update({'sampling_wlevel': rst[0][0], 'wlevel': float(rst[0][1] or 0)}) out.append(row) return json.dumps(out)
def salinKeJamjaman(tablename, tgl=datetime.date.today(), timpa=False): '''Membaca data Rain dari <table>(5 menitan) menjadi satu jam, simpan ke table 'periodikjam' ''' aid = dict([a.split('\t') for a in open('agent_table.txt').readlines()])[tablename] agent = AgentCh.get(aid) sql = "SELECT SamplingDate, HOUR(SamplingTime), \ SUM(Rain * %(tipping_factor)s) FROM %(tablename)s \ WHERE SamplingDate='%(tgl)s' \ GROUP BY HOUR(SamplingTime) \ ORDER BY SamplingTime" for c in conn.queryAll(sql % dict( tipping_factor=agent.TippingFactor, tablename=tablename, tgl=tgl)): sampling = datetime.datetime.fromtimestamp( int(c[0].strftime('%s')) + int(c[1]) * 3600) row = dict(agent=agent, sampling=sampling, rain=c[2]) pj = PeriodikJam.select( AND(PeriodikJam.q.sampling == sampling, PeriodikJam.q.agent == agent)) if not pj.count(): PeriodikJam(**row) if timpa: pj[0].rain = c[2]
def GET(self, table_name, id=None): inp = web.input() tgl = to_date( inp.get('sampling', datetime.date.today().strftime('%Y-%m-%d'))) bd_id = BENDUNGAN_DICT.get(table_name) pos = AgentBd.get(int(bd_id)) if id: kegiatan = Kegiatan.get(int(id)) return render.adm.bendungan.kegiatan_show(kegiatan=kegiatan) if inp.get('sampling') and inp.get('paper'): sql = "SELECT k.id, k.petugas, k.uraian, f.id, f.filepath FROM \ kegiatan k, foto f \ WHERE f.obj_type='kegiatan' AND k.id=f.obj_id AND DATE(k.sampling)='%s' \ AND k.table_name='%s'" % (tgl.strftime('%Y-%m-%d'), table_name) rst = [{ 'kid': r[0], 'p': r[1], 'u': r[2], 'fid': r[3], 'f': r[4] } for r in conn.queryAll(sql)] print rst return render_plain.adm.bendungan.kegiatan_paper(pos, tgl, rst) sql = "SELECT k.petugas, DATE(k.sampling), k.uraian, k.id \ FROM kegiatan k, foto f \ WHERE f.obj_type='kegiatan' AND k.id=f.obj_id \ AND k.table_name='%s' AND YEAR(k.sampling)=%s \ AND MONTH(k.sampling)=%s \ ORDER BY DATE(k.sampling) DESC" % (table_name, tgl.year, tgl.month) rows = [r for r in conn.queryAll(sql)] tgls = list(set(r[1] for r in rows)) result = dict([(t, {}) for t in tgls]) for r in rows: if r[0] in result[r[1]]: result[r[1]][r[0]].append({'uraian': r[2], 'kid': r[3]}) else: result[r[1]][r[0]] = [{'uraian': r[2], 'kid': r[3]}] return render.adm.bendungan.kegiatan( dict(pos=pos, urutan_kegiatan=sorted(result, reverse=True), petugas=PETUGAS_CHOICES, kegiatan=result, tgl=tgl))
def get_ch_daily_on_pos(pos, today=datetime.date.today()): pos = [a for a in AgentCh.select(AgentCh.q.AgentType==1) if a.table_name == pos][0] sql = "SELECT id, waktu, manual FROM curahhujan WHERE agent_id=%s AND YEAR(waktu)=%s AND MONTH(waktu)=%s ORDER BY waktu" % (pos.id, today.year, today.month) rs = conn.queryAll(sql) out = [] for r in rs: out.append(dict(id = r[0], waktu=r[1], ch=r[2])) return out
def POST(self, table_name): try: pos = [a for a in AgentCh.select(AgentCh.q.AgentType==1) if a.table_name == table_name][0] except IndexError: return web.notfound() inp = web.input() sql = "SELECT id FROM curahhujan WHERE agent_id=%s AND waktu='%s'" % (pos.id, to_date(inp.waktu)) rs = conn.queryAll(sql) if not rs: ch = CurahHujan(agent=pos, waktu=to_date(inp.waktu), manual=float(inp.hujan)) # publish to MQTT Broker #pub_object(ch) return web.redirect('/adm/ch/' + table_name, absolute=True)
def export_rtow(bd_id, periode=datetime.date.today()): '''Return csv of RTOW Bendungan dg ID bd_id''' if periode.month < 11: periode = datetime.date(periode.year - 1, 11, 1) # 1 Nop tahun lalu else: periode = datetime.date(periode.year, 11, 1) end_periode = periode + datetime.timedelta(days=366) header = "waktu,po_tma,po_vol,po_outflow_q,po_inflow_q,po_bona,po_bonb,vol_bona,vol_bonb".split( ',') sql = "SELECT {0} FROM renncanaoperasi WHERE waktu BETWEEN '{1}' AND '{2}'".format( ','.join(header), periode, end_periode) rows = conn.queryAll(sql) return [header] + list(rows)
def GET(self, loc): agents = dict([(r.table_name, r.AgentId) for r in Agent.select(OR(Agent.q.AgentType==3, Agent.q.AgentType==2))]) if agents.get(loc): sql = "SELECT SamplingDate, SamplingTime, WLevel/100.0 FROM %s \ ORDER BY SamplingDate DESC, SamplingTime DESC \ LIMIT 0, 1" % loc rst = conn.queryAll(sql) if rst: pos = Agent.get(agents.get(loc)) sampling = datetime.datetime.fromtimestamp(int(rst[0][0].strftime('%s')) + rst[0][1].seconds) web.header('Content-Type', 'text/plain') web.header('Access-Control-Allow-Origin', '*') return pos.cname + '|' + sampling.strftime('%d%b%y|%H:%M') + '|' + 'TMA: %.2f' % (pos.DPL + float(rst[0][2] or 0)) return web.notfound()
def get_tma_daily_on_pos(pos, today=datetime.date.today()): pos = [a for a in AgentTma.select(OR(AgentTma.q.AgentType==2,AgentTma.q.AgentType==0)) if a.table_name == pos][0] sql = "SELECT waktu, jam, manual FROM tma WHERE agent_id=%s AND YEAR(waktu)=%s AND MONTH(waktu)=%s ORDER BY waktu, CAST(jam AS SIGNED)" % (pos.id, today.year, today.month) rs = conn.queryAll(sql) out = [] t = None rows = {} for r in rs: jam = len(r[1]) == 1 and '0' + r[1] or r[1] if t != r[0]: rows[r[0]] = {jam: {'lokal': r[2], 'ttg': r[2] + pos.DPL}} t = r[0] next rows[t].update({jam: {'lokal': r[2], 'ttg': r[2] + pos.DPL}}) return rows
def GET(self, pos_id): try: pos = AgentCh.get(int(pos_id)) except SQLObjectNotFound: return web.notfound() web_input = web.input() periode = web_input.get('p', 10) year = int(web_input.get('year', datetime.date.today().year)) sql = "SELECT waktu, manual FROM curahhujan \ WHERE agent_id=%s AND YEAR(waktu)=%s ORDER BY waktu" % (pos_id, year) setahun = [] # 10 harian bulan = 0 bln_ = [0, 0, 0] for r in conn.queryAll(sql): if r[0].month != bulan: if bulan: setahun.append((bulan, bln_)) bln_ = [0, 0, 0] bulan = r[0].month if r[0].day < 10: bln_[0] += r[1] elif r[0].day < 21: bln_[1] += r[1] else: bln_[2] += r[1] setahun.append((bulan, bln_)) setahun = dict(setahun) series = [setahun.get(b, [0, 0, 0]) for b in range(1, 13)] s = [] d = [] t = [] for data in series: s.append(data[0]) d.append(data[1]) t.append(data[2]) data = { 'periode': periode, 'year': year, 'pos': pos, 'series': (s, d, t) } template = (periode == 10) and render.curahhujan.daily10 \ or render.curahhujan.daily15 return template(data)
def GET(self): tanggalmin = web.input().get('tanggalmin') tanggalmax = web.input().get('tanggalmax') pos_ch = web.input().get('pos_ch') if not tanggalmin or not tanggalmax: return json.dumps({ 'response': 'pilih tanggal minimal dan tanggal maksimal untuk rentang waktu' }) else: tanggalmin = tanggalmin.split('/') tanggalmax = tanggalmax.split('/') sdate = date(int(tanggalmin[0]), int(tanggalmin[1]), int(tanggalmin[2])) # start date edate = date(int(tanggalmax[0]), int(tanggalmax[1]), int(tanggalmax[2])) # end date delta = edate - sdate # as timedelta data = [] sql = "SELECT HOUR(SamplingTime),SUM(Rain) FROM %s WHERE SamplingDate='%s' GROUP BY HOUR(SamplingTime) ORDER BY SamplingTime" for i in range(delta.days + 1): day = sdate + timedelta(days=i) s = sql % (str(pos_ch), day) h_data = dict([d for d in conn.queryAll(s)]) # print("h_data",h_data) t_data = [{ "Tick": h_data.get(h, str(0)), "HOUR": str(h) } for h in range(0, 24)] # print("data",t_data) for a in t_data: if a.get("Tick") == None: row = { 'Tick': "0", 'HOUR': a.get("HOUR"), 'SamplingDate': day } else: row = { 'Tick': str(a.get("Tick")), 'HOUR': a.get("HOUR"), 'SamplingDate': day } data.append(row) return json.dumps(data, default=json_serialize)
def GET(self): skr = datetime.datetime.today() if skr.hour > 7: dari = skr.replace(hour=7).replace(minute=0).replace(second=0) else: dari = skr.replace(day=skr.day - 1).replace(hour=7).replace(minute=0).replace(second=0) hingga = skr arr = [a.table_name for a in Agent.select(Agent.q.AgentType==1.0)] sql = "SELECT COUNT(*) FROM %(table_name)s \ WHERE CONCAT(SamplingDate, ' ', SamplingTime) \ BETWEEN '%(dari)s' AND '%(hingga)s'" rec_count = [] for a in arr: rst = conn.queryAll(sql % {'table_name': a, 'dari': dari, 'hingga': hingga}) rec_count.append({'table_name': a, 'banyak': rst[0][0]}) ideal = set([i*5 for i in range(0, 12)]) return render_anon.cp.ompong({'poses': rec_count})
def export_rtow(bd_id, periode=datetime.date.today()): '''Return csv of RTOW Bendungan dg ID bd_id''' if periode.month < 11: periode = datetime.date(periode.year - 1, 11, 1) # 1 Nop tahun lalu else: periode = datetime.date(periode.year, 11, 1) jhar = [30, 31, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31] rr = [] for i in range(12): rr.append(periode.replace(day=1)) rr.append(periode.replace(day=16)) periode += datetime.timedelta(days=jhar[i]) sql = "SELECT waktu, po_tma, po_vol, po_outflow_q, po_inflow_q, po_bona, po_bonb, vol_bona, vol_bonb \ FROM waduk_daily \ WHERE pos_id=%s AND waktu IN (%s) \ ORDER BY pos_id, waktu" % (bd_id, ','.join( ["'" + str(i) + "'" for i in rr])) rows = conn.queryAll(sql) header = "waktu,po_tma,po_vol,po_outflow_q,po_inflow_q,po_bona,po_bonb,vol_bona,vol_bonb".split( ',') return [header] + list(rows)
def GET(self, did=None): '''@params: did: (str)device id sampling: (datetime''' conn = pg.connect(dbname="bsolo3", user="******", password="******") cursor = conn.cursor() if did: sql = "SELECT content FROM raw WHERE content->>'device' LIKE %s ORDER BY id DESC LIMIT 35" cursor.execute(sql, ('%/' + did + '/%', )) ''' regx = re.compile('.*'+did+'/', re.IGNORECASE) rst = [r for r in db.sensors.find({"device": regx}, {"_id": 0}).sort( "_id", -1).limit(25)] ''' rst = [r[0] for r in cursor.fetchall()] if not rst: return web.notfound() if web.input().get('sampling', None): #try: sampling = to_date(web.input().get('sampling')) _dari = time.mktime(sampling.timetuple()) _hingga = _dari + 86400 # satu hari = 86400 ms ''' rst = [r for r in db.sensors.find( {"$and": [{"device": regx}, {"sampling": {"$gte": _dari}}, {"sampling": {"$lt": _hingga}}]}, {_id: 0})] ''' sql = "SELECT content FROM raw WHERE content->>'device' LIKE %s AND (content->>'sampling')::int >= %s AND (content->>'sampling')::int <= %s" cursor.execute(sql, ('%/' + did + '/%', _dari, _hingga)) rst = [r[0] for r in cursor.fetchall()] if not rst: return "Tidak Ada Data Pada Tanggal " + web.input().get( 'sampling') rst.reverse() #except Exception as e: # print e out = {} if web.input().get('raw'): out['periodic'] = rst else: out['periodic'] = [map_periodic(r) for r in rst] out["bsolo_logger"] = BSOLO_LOGGER.get(did) else: out = [] sql = "SELECT DISTINCT(content->>'device') FROM raw" cursor.execute(sql) out = [r[0] for r in cursor.fetchall()] cursor.close() conn.close() #testing from asnan (data untuk kategori grafik) tinggal return untuk melihat data = [] kategori = [] battery = [] signal_quality = [] tick = [] are_tick = False distance = [] are_distance = False wl_scale = [] are_wl_scale = False sun_radiation_scale = [] wind_dir_scale = [] temperature_ambien_scale = [] humidity_ambien_scale = [] wind_speed_scale = [] are_klimatologi = False r = out["periodic"] r.reverse() for j in r: if "distance" in j: are_distance = True distance.append(j.get("distance")) if "wl_scale" in j: are_wl_scale = True wl_scale.append(j.get("wl_scale")) if "tick" in j: if "sun_radiation_scale" and "wind_dir_scale" and "temperature_ambien_scale" and "humidity_ambien_scale" and "wind_speed_scale" in j: are_klimatologi = True sun_radiation_scale.append(j.get("sun_radiation_scale")) wind_dir_scale.append(j.get("wind_dir_scale")) temperature_ambien_scale.append( j.get("temperature_ambien_scale")) humidity_ambien_scale.append( j.get("humidity_ambien_scale")) wind_speed_scale.append(j.get("wind_speed_scale")) tick.append(j.get("tick")) else: are_tick = True tick.append(j.get("tick")) kategori.append(j.get("sampling")) battery.append(j.get("battery")) signal_quality.append(j.get("signal_quality")) #end data.append({'name': 'signal_quality', 'data': signal_quality}) data.append({'name': 'battery', 'data': battery}) if are_distance == True: data.append({'name': 'distance', 'data': distance}) jenis_prima = "SONAR" if are_wl_scale == True: data.append({'name': 'wl_scale', 'data': wl_scale}) jenis_prima = "PRESSURE" if are_tick == True: data.append({'name': 'tick', 'data': tick}) jenis_prima = "ARR" if are_klimatologi == True: data.append({'name': 'tick', 'data': tick}) data.append({ 'name': 'sun_radiation_scale', 'data': sun_radiation_scale }) data.append({'name': 'wind_dir_scale', 'data': wind_dir_scale}) data.append({ 'name': 'temperature_ambien_scale', 'data': temperature_ambien_scale }) data.append({ 'name': 'humidity_ambien_scale', 'data': humidity_ambien_scale }) data.append({'name': 'wind_speed_scale', 'data': wind_speed_scale}) jenis_prima = "KLIMATOLOGI" conn = Agent._connection sql = "SELECT cname from agent where prima_id = %s" % ('"' + did + '"') result = conn.queryAll(sql) if result: pname = result[0][0] else: pname = "--" #print result return render.sensor.sensor_graph({ 'data': str(data), 'kategori': str(kategori), 'did': did, 'jenis_prima': jenis_prima, 'pname': pname })
def POST(self): auth = web.ctx.env.get('HTTP_AUTHORIZATION') authreq = False allowed = [(u.username, u.password) for u in Authuser.select()] if auth is None: authreq = True else: auth = re.sub('^Basic', '', auth) username, password = base64.decodestring(auth).split(':') password = md5(password).hexdigest() auth = None if (username, password) in allowed: x = web.input() of = open('/tmp/svrdata.pkl', 'wb') of.write(x['svr_data']) of.close() to_load = {} with open('/tmp/svrdata.pkl', 'rb') as f: to_load = cPickle.load(f) for d in to_load.get('pos_to_put', []): try: agent = Agent.select(Agent.q.AgentName == d['name'])[0] except IndexError: try: agent = Agent(**d['data']) # karena 'id'nya tidak standar, # SQLObject bilang Not Found except SQLObjectNotFound: agent = Agent.select( Agent.q.AgentName == d['name'])[0] # Periksa apakah table pemuat Logs untuk # pos ini telah tersedia try: rs = conn.queryAll("SELECT SamplingDate FROM %s \ LIMIT 0, 1" % (agent.table_name)) except: rs = conn.queryAll("CREATE TABLE %s \ LIKE tpl_agent" % agent.table_name) for l in d['logs']: sql = "SELECT COUNT(*) FROM %s \ WHERE SamplingDate='%s' AND \ SamplingTime='%s'" % (agent.table_name, l['SamplingDate'], l['SamplingTime']) rs = conn.queryAll(sql) if rs[0][0] == 0: sql = "INSERT INTO %s (RID, ReceivedDate, \ ReceivedTime, DataType, StatusPort, \ SamplingDate, SamplingTime, Temperature, \ Humidity, Rain, Rain1, Rain2, Rain3, \ Rain4, WLevel, Wlevel1, WLevel2, \ WLevel3, WLevel4, up_since, sq) VALUES (%s, '%s', \ '%s', %s, '%s', '%s', '%s', %s, %s, \ %s, %s, %s, %s, %s, %s, %s, %s, %s, \ %s, '%s', %s)" % ( agent.table_name, l['RID'], l['ReceivedDate'], l['ReceivedTime'], l['DataType'], l['StatusPort'], l['SamplingDate'], l['SamplingTime'], l['Temperature'], l['Humidity'], l['Rain'], l['Rain1'], l['Rain2'], l['Rain3'], l['Rain4'], l['WLevel'], l['WLevel1'], l['WLevel2'], l['WLevel3'], l['WLevel4'], l['up_since'], l['sq']) rs = conn.query(sql) l = None try: new_pos_data = d['data'] for k in new_pos_data.keys(): setattr(agent, k, new_pos_data[k]) except: pass # POS to Del for d in to_load.get('pos_to_del', []): try: agent = Agent.select( Agent.q.AgentName == d['AgentName'])[0] agent.destroySelf() except: pass return "Ok" else: authreq = True if authreq: web.header('WWW-Authenticate', 'Basic realm="incoming"') web.ctx.status = '401 unauthorized' return """<html>
def curah_hujan_pos(self, pos_id, tahun=datetime.date.today().year, bulan=''): ''' Jika bulan valid, otomatis tahun juga valid, tampilkan curah hujan setiap hari pada bulan terpilih sumbu mendatar berisi tanggal Jika tahun valid, bulan kosong, tampilkan curah hujan 3 tahun lalu, dari tahun terpilih, sumbu mendatar berisi bulan-bulan ''' try: agent = AgentCh.get(pos_id) except SQLObjectNotFound: return web.notfound() if agent.AgentType not in (1.0, 0.0): return web.notfound() HIDE_THIS = [a.strip() for a in open('HIDE_ARR.txt').read().split(',')] agents = AgentCh.select( AND( OR(AgentCh.q.AgentType == KLIMATOLOGI, AgentCh.q.AgentType == 0.0), AgentCh.q.expose == True)).orderBy(( 'wilayah', 'urutan', )) agents = [a for a in agents if a.table_name not in HIDE_THIS] ch = agent.get_ch(tahun, bulan) data = [] for a in ch: try: data.append((a[0], a[2] or 0)) except: data.append((a[0], 0)) series = {} to_render = render.curahhujan.bulanan if bulan: # hujan per hari pada 'bulan' series = [ 0 for r in range(calendar.monthrange(tahun, int(bulan))[1]) ] sql = "SELECT waktu, manual, pagi, sore, malam, tmalam FROM curahhujan \ WHERE agent_id=%s AND YEAR(waktu)=%s AND MONTH(waktu)=%s" % ( pos_id, tahun, bulan) for d in conn.queryAll(sql): series[d[0].day - 1] = d[1] data = Struct( **{ 'series': series, 'categories': [s + 1 for s in range(len(series))], 'bulan': datetime.date(tahun, int(bulan), 1) }) to_render = render.curahhujan.harian elif data: # hujan per bulan pada 'tahun' th = data[0][0].year series[th] = [0 for r in range(0, 12)] for d in data: if d[0].year != th: th = d[0].year series[th] = [0 for r in range(0, 12)] series[th][d[0].month - 1] = d[1] else: series[th][d[0].month - 1] = d[1] data = [ Struct(**{ 'tahun': k, 'series': v }) for k, v in sorted(series.items()) ] ctx = {'pos': agent, 'data': data, 'poses': agents, 'wilayah': WILAYAH} return to_render(ctx)
def curah_hujan_pos(self, pos_id, tahun=datetime.date.today().year, bulan=''): ''' Jika bulan valid, otomatis tahun juga valid, tampilkan curah hujan setiap hari pada bulan terpilih sumbu mendatar berisi tanggal Jika tahun valid, bulan kosong, tampilkan curah hujan 3 tahun lalu, dari tahun terpilih, sumbu mendatar berisi bulan-bulan ''' try: agent = AgentCh.get(pos_id) except SQLObjectNotFound: return web.notfound() if agent.AgentType not in (1.0, 0.0): return web.notfound() ch = agent.get_ch(tahun, bulan) data = [] for a in ch: try: data.append((a[0], a[2] or 0)) except: data.append((a[0], 0)) series = {} to_render = render.curahhujan.bulanan if bulan: # hujan per hari pada 'bulan' series = [ 0 for r in range(calendar.monthrange(tahun, int(bulan))[1]) ] sql = "SELECT waktu, manual, pagi, sore, malam, tmalam FROM curahhujan \ WHERE agent_id=%s AND YEAR(waktu)=%s AND MONTH(waktu)=%s" % ( pos_id, tahun, bulan) for d in conn.queryAll(sql): series[d[0].day - 1] = d[1] data = Struct( **{ 'series': series, 'categories': [s + 1 for s in range(len(series))], 'bulan': datetime.date(tahun, int(bulan), 1) }) to_render = render.curahhujan.harian elif data: print data # hujan per bulan pada 'tahun' th = data[0][0].year series[th] = [0 for r in range(0, 12)] for d in data: if d[0].year != th: th = d[0].year series[th] = [0 for r in range(0, 12)] series[th][d[0].month - 1] = d[1] else: series[th][d[0].month - 1] = d[1] data = [ Struct(**{ 'tahun': k, 'series': v }) for k, v in sorted(series.items()) ] ctx = {'pos': agent, 'data': data} return to_render(ctx)
dict_agent = dict([(a.table_name, a.AgentId) for a in Agent.select(Agent.q.AgentType == 3)]) #f = open('/tmp/out.txt', 'w') for l in lines: (n, d, data) = l.strip().split('\t', 2) # print n b = 11 waktu = datetime.date(th, b, 1) pows = data.strip().split('\t') for i in range(len(pows)): if not i % 2: while waktu.day <= 15: sql = "SELECT id FROM waduk_daily WHERE pos_id=%s AND waktu='%s'" % ( dict_agent[n], waktu) rst = conn.queryAll(sql) if rst: wd = WadukDaily.get(rst[0][0]) print wd.waktu, n wd.po_outflow_q = float(pows[i]) #print rst else: wd = WadukDaily(**dict(pos=dict_agent[n], waktu=waktu, po_outflow_q=float(pows[i]))) print n, waktu #print dict_agent[n], n, waktu, pows[i] waktu += datetime.timedelta(days=1) else: while waktu.day != 1: sql = "SELECT id FROM waduk_daily WHERE pos_id=%s AND waktu='%s'" % (
def GET(self): conn = Agent._connection sql = "select k.waktu,k.ip,k.id_pos,a.cname,a.ll,a.show from kualitas_air k inner join (SELECT MAX(c.waktu) as max_date, c.ip, d.id,d.cname,d.ll,d.show FROM kualitas_air c, lokasi d WHERE c.id_pos=d.id GROUP BY d.id) a on a.max_date = k.waktu and a.id = k.id_pos and a.show = 1 order by k.id_pos ASC" rst = conn.queryAll(sql) all_pos = [] for ka in rst: a = ka[0].strftime("%b %Y") if ka[1] == None: ip_last_time = "Belum Tersedia" else: ip_last_time = ka[1] all_pos.append({ 'id': str(ka[2]), 'name': ka[3], 'll': ka[4], 'last_time': a, 'ip_last_time': ip_last_time }) js_foot = """ <script> var allPos = """ + str(all_pos) + """; for (var i = 0; i < allPos.length; i++) { var iw = `<div class="panel panel-default"> <div class="panel-heading"> <div class="panel-title"><h6>${allPos[i].name}</h6></div></div> <div class="panel-body"> <table class="table"> <tr><td>Koordinat</td><td>${allPos[i].ll}</td></tr> <tr><td>Data Terakhir</td><td>${allPos[i].last_time}</td></tr> <tr><td>IP Terakhir</td><td>${allPos[i].ip_last_time}</td></tr> </table> </div>` var lat = parseFloat(allPos[i].ll.split(',')[0]); var lng = parseFloat(allPos[i].ll.split(',')[1]); if(allPos[i].ip_last_time <= 1){ var iconka = ka_icon_blue; } else if(allPos[i].ip_last_time > 1 && allPos[i].ip_last_time <= 5){ var iconka = ka_icon_green; } else if(allPos[i].ip_last_time > 5 && allPos[i].ip_last_time <= 10){ var iconka = ka_icon_yellow; } else if(allPos[i].ip_last_time > 10){ var iconka = ka_icon_red; } else{ var iconka = ka_icon_orange; } var name = allPos[i].name, marker = new L.Marker(new L.latLng([lat,lng]), {title: name,icon: iconka} );//se property searched; marker.bindPopup(iw); markersLayer.addLayer(marker); } </script> <!-- Optional JavaScript --> <!-- jQuery first, then Popper.js, then Bootstrap JS --> <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script> """ return rendermap.map.kualitasair({'js_foot': js_foot})
def GET(self): inp = web.input() try: pola_operasi = open('pola_operasi.txt').read() except: pola_operasi = "Basah" if inp.get('periode'): periode = to_date(inp.periode) else: periode = datetime.date.today() if periode.month < 11: periode = datetime.date(periode.year - 1, 11, 1) # 1 Nop tahun lalu else: periode = datetime.date(periode.year, 11, 1) tanggal = periode if periode.month > 10: tanggal = datetime.date(tanggal.year + 1, 11, 1) if tanggal.year <= 2018: jhar = [30, 31, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31] rr = [] for i in range(12): rr.append(periode.replace(day=1)) rr.append(periode.replace(day=16)) periode += datetime.timedelta(days=jhar[i]) if tanggal.year > 2018: jhar = [30, 31, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31] rr = [] for i in range(12): rr.append(periode.replace(day=15)) rr.append(periode.replace(day=jhar[i])) periode += datetime.timedelta(days=jhar[i]) sql = "SELECT pos_id, waktu, po_outflow_q, po_tma, po_outflow_v, id, po_bona, po_bonb, vol_bona, vol_bonb \ FROM waduk_daily \ WHERE waktu IN (%s) \ ORDER BY pos_id, waktu" % ','.join( ["'" + str(i) + "'" for i in rr]) rst = conn.queryAll(sql) bdgs = dict([(a.AgentId, a) for a in AgentBd.select(AgentBd.q.AgentType == 3)]) bids = list(set([r[0] for r in rst])) data = [] bid = 0 row = [] for r in rst: if bid != r[0]: bid = r[0] row = [{ 'id': r[5], 'tma': r[3], 'q': r[2], 'v': r[4], 'bona': r[6], 'bonb': r[7], 'volbona': r[8], 'volbonb': r[9] }] data.append((r[0], row)) continue row.append({ 'id': r[5], 'tma': r[3], 'q': r[2], 'v': r[4], 'bona': r[6], 'bonb': r[7], 'volbona': r[8], 'volbonb': r[9] }) return render.adm.bendungan.rotw({ 'data': data, 'bendungan': bdgs, 'periode': rr, 'pola_operasi': pola_operasi, 'tanggal': tanggal })
def GET(self): conn = Agent._connection #sql = "select k.waktu,k.ip,k.id_pos,a.cname,a.ll from kualitas_air k inner join (SELECT MAX(c.waktu) as max_date, c.ip, d.id,d.cname,d.ll FROM kualitas_air c, lokasi d WHERE c.id_pos=d.id GROUP BY d.id) a on a.max_date = k.waktu and a.id = k.id_pos order by k.id_pos ASC" sql = "select k.waktu,k.ip,k.id_pos,a.cname,a.ll,a.show from kualitas_air k inner join (SELECT MAX(c.waktu) as max_date, c.ip, d.id,d.cname,d.ll,d.show FROM kualitas_air c, lokasi d WHERE c.id_pos=d.id GROUP BY d.id) a on a.max_date = k.waktu and a.id = k.id_pos and a.show = 1 order by k.id_pos ASC" rst = conn.queryAll(sql) all_pos = [] for ka in rst: a = ka[0].strftime("%b %Y") if ka[1] == None: ip_last_time = "Belum Tersedia" else: ip_last_time = ka[1] all_pos.append({ 'id': str(ka[2]), 'name': ka[3], 'll': ka[4], 'last_time': a, 'ip_last_time': ip_last_time }) js_foot = """ <script type="text/template" id="pos_infowindow"> <div class="item infowindow" id="pos_<%= index %>"> <span class="pos"><%= name %> </span> <span class="meter"> </span> </div> </script> <script> function init_map() { var my_options = { center: new google.maps.LatLng(-7.49592,111.568909), zoom: 9, styles: [ { "featureType": "all", "elementType": "geometry.fill", "stylers": [ { "weight": "2.00" } ] }, { "featureType": "all", "elementType": "geometry.stroke", "stylers": [ { "color": "#9c9c9c" } ] }, { "featureType": "all", "elementType": "labels.text", "stylers": [ { "visibility": "on" } ] }, { "featureType": "landscape", "elementType": "all", "stylers": [ { "color": "#f2f2f2" } ] }, { "featureType": "landscape", "elementType": "geometry.fill", "stylers": [ { "color": "#ffffff" } ] }, { "featureType": "landscape.man_made", "elementType": "geometry.fill", "stylers": [ { "color": "#ffffff" } ] }, { "featureType": "poi", "elementType": "all", "stylers": [ { "visibility": "off" } ] }, { "featureType": "road", "elementType": "all", "stylers": [ { "saturation": -100 }, { "lightness": 45 } ] }, { "featureType": "road", "elementType": "geometry.fill", "stylers": [ { "color": "#eeeeee" } ] }, { "featureType": "road", "elementType": "labels.text.fill", "stylers": [ { "color": "#7b7b7b" } ] }, { "featureType": "road", "elementType": "labels.text.stroke", "stylers": [ { "color": "#ffffff" } ] }, { "featureType": "road.highway", "elementType": "all", "stylers": [ { "visibility": "simplified" } ] }, { "featureType": "road.arterial", "elementType": "labels.icon", "stylers": [ { "visibility": "off" } ] }, { "featureType": "transit", "elementType": "all", "stylers": [ { "visibility": "off" } ] }, { "featureType": "water", "elementType": "all", "stylers": [ { "saturation": 100 }, { "lightness": 15 }, { "color": "#88b6f2" /* "#466cec" */ }, { "visibility": "on" } ] }, { "featureType": "water", "elementType": "geometry.fill", "stylers": [ { "color": "#88b6f2" } ] }, { "featureType": "water", "elementType": "labels.text.fill", "stylers": [ { "color": "#000000" } ] }, { "featureType": "water", "elementType": "labels.text.stroke", "stylers": [ { "color": "#ffffff" } ] } ], mapTypeId: google.maps.MapTypeId.TERRAIN }; var map = new google.maps.Map(document.getElementById('map'), my_options); var batas_das = new google.maps.KmlLayer( {url: 'http://hidrologi.bbws-bsolo.net/static/batas_das.kml', preserveViewport: true, map: map}); /* var s_bengawan_solo = new google.maps.KmlLayer( {url: 'http://hidrologi.bbws-bsolo.net/static/s_bengawan_solo.kml', preserveViewport: true, map: map}); */ var icons = { memenuhi_mutu_baku: { name: 'Memenuhi Mutu Baku', icon: 'http://maps.google.com/mapfiles/ms/icons/blue-dot.png' }, cemar_ringan: { name: 'Cemar Ringan', icon: 'http://maps.google.com/mapfiles/ms/icons/green-dot.png' }, cemar_sedang: { name: 'Cemar Sedang', icon: 'http://maps.google.com/mapfiles/ms/icons/yellow-dot.png' }, cemar_berat: { name: 'Cemar Berat', icon: 'http://maps.google.com/mapfiles/ms/icons/red-dot.png' } }; var legend = document.getElementById('legend'); for (var key in icons) { var type = icons[key]; var name = type.name; var icon = type.icon; var div = document.createElement('div'); div.innerHTML = '<img src="' + icon + '"> ' + name; legend.appendChild(div); } map.controls[google.maps.ControlPosition.RIGHT_BOTTOM].push(legend); var allPos = """ + str(all_pos) + """; var markers = {}; var infoWindow = new google.maps.InfoWindow; _.each(allPos, function(pos) { if(pos.ip_last_time <= 1){ var icon = 'http://maps.google.com/mapfiles/ms/icons/blue-dot.png'; }else if(pos.ip_last_time > 1 && pos.ip_last_time <= 5){ var icon = 'http://maps.google.com/mapfiles/ms/icons/green-dot.png'; }else if(pos.ip_last_time > 5 && pos.ip_last_time <= 10){ var icon = 'http://maps.google.com/mapfiles/ms/icons/yellow-dot.png'; }else if(pos.ip_last_time > 10){ var icon = 'http://maps.google.com/mapfiles/ms/icons/red-dot.png'; }else{ var icon = 'http://maps.google.com/mapfiles/ms/icons/orange-dot.png'; } var lat = parseFloat(pos.ll.split(',')[0]); var lng = parseFloat(pos.ll.split(',')[1]); var point = new google.maps.LatLng(lat, lng); var marker = new google.maps.Marker({ icon:icon, map: map, position: point }); markers[pos.id] = marker; bind_info_window(marker, map, infoWindow, "<a href='/kualitasair/chart/"+pos.id+"' style='font-weight: bold;font-size: 16px;'>"+ pos.name + "</a>"+"<br>"+"Data Terakhir : "+pos.last_time+"<br>"+"IP Terakhir : "+pos.ip_last_time); }); }; function bind_info_window(marker, map, infowindow, html) { google.maps.event.addListener(marker, 'click', function() { infowindow.setContent(html); infowindow.open(map, marker); }) }; </script> <script src="https://maps.googleapis.com/maps/api/js?key=AIzaSyAmnJGdC-ZhVd98H3mwMRv2GU2dlv1D7IA&callback=init_map"></script> """ return render.map.kualitasair({'js_foot': js_foot})
def import_input(self): print('import_input running') to_load = {} with open('/tmp/svrdata.pkl', 'rb') as f: to_load = pickle.load(f) for d in to_load.get('pos_to_put', []): try: agent = Agent.select(Agent.q.AgentName == d['name'])[0] except IndexError: try: agent = Agent(**d['data']) # karena 'id'nya tidak standar, # SQLObject bilang Not Found except SQLObjectNotFound: agent = Agent.select( Agent.q.AgentName == d['name'])[0] # Periksa apakah table pemuat Logs untuk # pos ini telah tersedia print('incoming:', agent.table_name) try: rs = conn.queryAll("SELECT SamplingDate FROM %s \ LIMIT 0, 1" % (agent.table_name)) except: try: rs = conn.queryAll("CREATE TABLE %s \ LIKE tpl_agent" % agent.table_name) except: pass for l in d['logs']: sql = "SELECT COUNT(*) FROM %s \ WHERE SamplingDate='%s' AND \ SamplingTime='%s'" % (agent.table_name, l['SamplingDate'], l['SamplingTime']) rs = conn.queryAll(sql) if rs[0][0] == 0: sql = "INSERT INTO %s (RID, ReceivedDate, \ ReceivedTime, DataType, StatusPort, \ SamplingDate, SamplingTime, Temperature, \ Humidity, Rain, Rain1, Rain2, Rain3, \ Rain4, WLevel, Wlevel1, WLevel2, \ WLevel3, WLevel4, up_since, sq) VALUES (%s, '%s', \ '%s', %s, '%s', '%s', '%s', %s, %s, \ %s, %s, %s, %s, %s, %s, %s, %s, %s, \ %s, '%s', %s)" % (agent.table_name, l['RID'], l['ReceivedDate'], l['ReceivedTime'], l['DataType'], l['StatusPort'], l['SamplingDate'], l['SamplingTime'], l['Temperature'], l['Humidity'], l['Rain'], l['Rain1'], l['Rain2'], l['Rain3'], l['Rain4'], l['WLevel'], l['WLevel1'], l['WLevel2'], l['WLevel3'], l['WLevel4'], l['up_since'], l['sq']) rs = conn.query(sql) l = None try: new_pos_data = d['data'] for k in new_pos_data.keys(): setattr(agent, k, new_pos_data[k]) except: pass # POS to Del for d in to_load.get('pos_to_del', []): try: agent = Agent.select( Agent.q.AgentName == d['AgentName'])[0] agent.destroySelf() except: pass return "Ok"