curah_hujan_bot.py

#define library and main variable
import MySQLdb
from urllib2 import urlopen
import urllib2
import requests
import time
import json
def push_notifikasi(wilayah,stasiun,tanggal,jam,ch,status,state):
        tanggal_jam = str(tanggal) + " " + str(jam)
        data = {
        # kalau mau push ke 1 orang lihat tokennya ex:"c82HgPXsWNo:APA91bFc9oCDyNkW0IceYHlETfKM47VeaXsd_C1vNiby9QGkGqu06hY-GFRFbNv3Rh6WvrhNf1hrSFAVoKrJ7YOlIr8movNU6jUYiTENi_e37pcKoiOVMcfBYBqLpN5W-WiHnnvfqGZi" lihat di database user
        "to": "/topics/bbws",
        "data": {
                "bagian":wilayah,
                "lokasi":stasiun,
                "tanggal":tanggal_jam,
                "value":ch,
                "status":status,
                "state":state
                }
        }    
        kiriman = json.dumps(data)
        resp = requests.post(url='https://fcm.googleapis.com/fcm/send', headers=headers, data=kiriman)
        print resp
        
def insertdb(lat, lon, stasiun, id_stasiun, status, ch, wilayah, tanggal, jam, state):     
        conn_mysql = MySQLdb.connect("192.168.10.4","fewsm","bsolo99","fewsm")
        cursor = conn_mysql.cursor()
        sql ="""INSERT INTO notifikasi(
        lat, lon, stasiun, id_stasiun, status, bagian, lokasi, pelapor_id, pelapor, type, tma, debit, tanggal, jam, push_status
        ) VALUES (
        "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")"""%(
            lat, lon, stasiun, id_stasiun, status, wilayah, stasiun, '0', 'system', '1', ch, '0', tanggal, jam, '0')
        try:
            cursor.execute(sql)
            conn_mysql.commit()
            print "success"
        except:
            conn_mysql.rollback()
            print "error"
            print sql
        conn_mysql.close()
        
        #try :
        conn_mysql = MySQLdb.connect("192.168.10.4","fewsm","bsolo99","fewsm")
        cursor = conn_mysql.cursor()
        sql ="""UPDATE tb_info_siaga
        SET tanggal=concat("%s"," ","%s"), status='%s', nilai='%s'
        WHERE id_stas='%s' and type = '1'"""%(
            tanggal, jam, status, ch, id_stasiun
            )
        try:
            cursor.execute(sql)
            conn_mysql.commit()
            #push_notifikasi(wilayah,stasiun,tanggal,jam,ch,status,state)
            print "success"
            print sql
        except:
            conn_mysql.rollback()
            print "error"
            print sql
        conn_mysql.close()
        #except Exception as e: print(e)
        
def klasifikasi(rain, ll, stasiun, id_stasiun, wilayah, tanggal, jam):
        lon = (ll.split(","))[1]
        lat = (ll.split(","))[0]
        if wilayah == 1 :
                wilayah = "Hulu"
        elif wilayah == 2 :
                wilayah = "Madiun"
        elif wilayah == 3 :
                wilayah = "Hilir"
        else:
                wilayah = "Hilir"
        print "lon : " + str(lon)
        print "lat : " + str(lat)
        print "id_stasiun : " + str(id_stasiun)
        print "wilayah : " + str(wilayah)

        #cek data status sebelumnya (pantau 12 kebelakang)
        sql = """SELECT status FROM fewsm.notifikasi where id_stasiun = "%s" and TIMESTAMP(tanggal, jam) >= DATE_SUB(NOW(),INTERVAL 24 HOUR) order by tanggal desc, jam desc limit 1;""" %(id_stasiun)
        conn_mysql = MySQLdb.connect("192.168.10.4","fewsm","bsolo99","fewsm")
        cursor = conn_mysql.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        conn_mysql.close()
        old_status = (result[0])[0]
        print "old_status : " + str(old_status)
        
        if float(rain) >= 20:
            print 'hujan sangat lebat'
            status = 'hujan sangat lebat'
            if status != old_status :
                state = 3
                insertdb(lat, lon, stasiun, id_stasiun, status, rain, wilayah, tanggal, jam, state)
            else :
                print "status sama dengan sebelumnya"
        elif float(rain) >= 10 and float(rain) < 20:
            print 'hujan lebat'
            status = 'hujan lebat'
            if status != old_status :
                state = 2
                insertdb(lat, lon, stasiun, id_stasiun, status, rain, wilayah, tanggal, jam, state)
            else :
                print "status sama dengan sebelumnya"
        elif float(rain) >= 5 and float(rain) < 10:
            print 'hujan sedang'
            status = 'hujan sedang'
            if status != old_status :
                state = 1
                insertdb(lat, lon, stasiun, id_stasiun, status, rain, wilayah, tanggal, jam, state)
            else :
                print "status sama dengan sebelumnya"

            
        else : # default, could also just omit condition or 'if True'
            print 'normal'
            status = 'normal'
            state = 0

def data_stas(stasiun, tippingfactor, ll, id_stasiun, wilayah):
        sql = """SELECT max(SamplingDate), max(SamplingTime), sum(Rain) as rain, date_format(TIMESTAMP(samplingdate, samplingtime), '%Y-%m-%d %H') as jaman FROM """ + stasiun + """ where TIMESTAMP(samplingdate, samplingtime) >= DATE_SUB(NOW(),INTERVAL 24 HOUR) group by jaman order by SamplingDate desc, SamplingTime desc;"""
        conn_mysql = MySQLdb.connect("192.168.10.10","remote","remote","ffws")
        cursor = conn_mysql.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        conn_mysql.close()
        try:
                for data in result:
                        klasifikasi(str(data[2]), ll, stasiun, id_stasiun, wilayah, str(data[0]), str(data[1]))
        except:
                print "no data"

##	rain_agr = 0
##	try:
##		for data in result :
##			try:
##				rain = float(data[2]) * float(tippingfactor)
##			except:
##				rain = 0
##			rain_agr = rain_agr + rain
##			#print "data : " + str(data[2])
##			#print "rain : " + str(rain)
##		print "rain_agr : " + str(rain_agr)
##		print "tippingfactor : " + str(tippingfactor)
##		print "tanggal : " + str(data[0])
##		print "jam : " + str(data[1])
##		print "stasiun : " + str(stasiun)
##		klasifikasi(rain_agr, ll, stasiun, id_stasiun, wilayah, str(data[0]), str(data[1]))

##	except:
##		print "no data in 1 hour"


sql = """SELECT AgentID, AgentType, AgentName, TippingFactor, ll, wilayah from agent where AgentType = 1 and ews_mute = 0;"""
conn_mysql = MySQLdb.connect("192.168.10.10","remote","remote","ffws")
cursor = conn_mysql.cursor()
cursor.execute(sql)
result = cursor.fetchall()
conn_mysql.close()
for res in result :
        stas = (res[2].replace(" ","_")).lower()
        tipfac = res[3]
        ll = res[4]
        id_stasiun = res[0]
        wilayah = res[5] 
        data_stas(stas, tipfac, ll, id_stasiun, wilayah)