#!/usr/bin/python
# coding:utf-8
import sys, os, logging
import Adafruit_DHT
import httplib, urllib
import time,datetime
import MySQLdb
import base64
import smtplib
import pandas as pd
from bokeh.models import BoxAnnotation
from bokeh.plotting import figure, output_file, show
from bokeh.models import HoverTool
from Crypto.Cipher import DES
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
# 程式檔案路徑
fPath = "/home/go38box/DHT22/"
# log 參數設定
FORMAT = '%(asctime)s - %(message)s'
logpath = fPath + "tempLogs"
logging.basicConfig(format = FORMAT, filename = logpath)
logger = logging.getLogger('tcpserver')
"""
console = logging.StreamHandler()
aformat = logging.Formatter('%(asctime)s - %(message)s')
console.setFormatter(aformat)
logger.addHandler(console)
"""
def main():
writeToDb = False
errId = -1
# 連接資料庫
db = db_connect()
# 取得最後一筆資料
lastRecord = get_last_record(db)
# 取得config資料表中的設定
[dbSendMail,dbTempMax,dbTempMin,dbHumidityMax,dbHumidityMin,dbTimeOffset,dbDifference,dbThingSpeakApiKey] = get_config(db)
#取得目前時間
dtnow = datetime.datetime.now()
#取得溫濕度
[temp,humidity] = dht22()
# 目前時間和最後一筆資料的時間相差幾秒
timeoffset = (dtnow - lastRecord[0]).seconds
# 送溫濕度到ThingSpeak，如果設定值為空的話，就不傳送
if dbThingSpeakApiKey!='':
params = urllib.urlencode({'field1': '{0:0.1f}'.format(temp), 'field2': '{0:0.1f}'.format(humidity), 'key': dbThingSpeakApiKey})
post_to_thingspeak2(params)
#溫濕度在0~100間才處理
if (humidity >0 and humidity <= 100) and (temp >0 and temp<=100):
if timeoffset > dbTimeOffset:
#目前時間和資料庫中最後一筆資料的時間超過X秒就直接寫入資料庫
logger.warning('Msg =>last(%s Temp:%s Humidity:%s) New(%s Temp:%6.2f Humidity:%6.2f) event:%s', lastRecord[0],lastRecord[1],lastRecord[2],dtnow,temp,humidity,'timeoffset > 60')
writeToDb = True
errId=2
else:
#目前時間和資料庫在X秒內，就檢查和上一筆的溫濕度不可超過所設定的範圍
#超過則不寫入資料庫
#溫度+-5度 ,濕度+-5度
#正負值由資料庫中設定
if (temp > lastRecord[1]+dbDifference) or (temp < lastRecord[1]-dbDifference):
logger.warning('Msg =>last(%s Temp:%s Humidity:%s) New(%s Temp:%6.2f Humidity:%6.2f) event:%s', lastRecord[0],lastRecord[1],lastRecord[2],dtnow,temp,humidity,'(temp > lastRecord[1]+'+str(dbDifference)+') or (temp < lastRecord[1]-'+str(dbDifference)+')')
writeToDb = False
errId=3
elif (humidity > lastRecord[2]+dbDifference) or (humidity < lastRecord[2]-dbDifference):
logger.warning('Msg =>last(%s Temp:%s Humidity:%s) New(%s Temp:%6.2f Humidity:%6.2f) event:%s', lastRecord[0],lastRecord[1],lastRecord[2],dtnow,temp,humidity,'(humidity > lastRecord[2]+'+str(dbDifference)+') or (humidity < lastRecord[2]-'+str(dbDifference)+')')
writeToDb = False
errId=4
else:
writeToDb = True
else:
# 溫濕度不在0~100間
logger.warning('Msg =>last(%s Temp:%s Humidity:%s) New(%s Temp:%6.2f Humidity:%6.2f) event:%s', lastRecord[0],lastRecord[1],lastRecord[2],dtnow,temp,humidity,'(humidity >0 and humidity <= 100) and (temp >0 and temp<=100)')
writeToDb = False
errId=1
# 依上方的判斷(writeToDb)，決定是否要寫入資料庫
if writeToDb:
#logger.warning('Msg =>last(%s Temp:%s Humidity:%s) New(%s Temp:%6.2f Humidity:%6.2f) event:%s', lastRecord[0],lastRecord[1],lastRecord[2],dtnow,temp,humidity,'OK')
# 將目前的溫濕度寫入資料庫
insert_to_mysql(db,dtnow,temp,humidity);
# 取得目前時間
dataframe = select_data(db,1)
lastRecordNew = get_last_record(db)
build_temp(dataframe[0],lastRecordNew,dbTempMax,dbTempMin)
build_humidity(dataframe[1],lastRecordNew,dbHumidityMax,dbHumidityMin)
# 依資料表config中欄位「send_mail」的設定，判斷是否要寄送E-Mail
if dbSendMail==1 :
if writeToDb == True and (lastRecordNew[1]>=dbTempMin and lastRecordNew[1]<=dbTempMax and lastRecordNew[2]>=dbHumidityMin and lastRecordNew[2]<=dbHumidityMax):
send_email(db,lastRecord[0],lastRecord[1],lastRecord[2],lastRecordNew[0],lastRecordNew[1],lastRecordNew[2],dbTempMax,dbTempMin,dbHumidityMax,dbHumidityMin,dbTimeOffset,dbDifference,timeoffset,0)
elif writeToDb == False:
# print 'writeToDb=false'
send_email(db,lastRecord[0],lastRecord[1],lastRecord[2],dtnow,temp,humidity,dbTempMax,dbTempMin,dbHumidityMax,dbHumidityMin,dbTimeOffset,dbDifference,timeoffset,errId)
elif lastRecordNew[1]<dbTempMin or lastRecordNew[1]>dbTempMax or lastRecordNew[2]<dbHumidityMin or lastRecordNew[2]>dbHumidityMax :
# print 'maxmine'
errId=5
logger.warning('Msg =>last(%s Temp:%s Humidity:%s) New(%s Temp:%6.2f Humidity:%6.2f) event:%s', lastRecord[0],lastRecord[1],lastRecord[2],dtnow,temp,humidity,'溫濕度異常，不在所設定的範圍內！')
send_email(db,lastRecord[0],lastRecord[1],lastRecord[2],lastRecordNew[0],lastRecordNew[1],lastRecordNew[2],dbTempMax,dbTempMin,dbHumidityMax,dbHumidityMin,dbTimeOffset,dbDifference,timeoffset,errId)
db.close()
# 遲接資料庫
def db_connect():
db = MySQLdb.connect(host="localhost",user="go38box", passwd="Go38box4$",db="temperature", charset="utf8")
return db
# 取得溫濕度
def dht22():
humidity, temperature = Adafruit_DHT.read_retry(Adafruit_DHT.AM2302,4)
if humidity is not None and temperature is not None:
print('Temp={0:0.1f}* Humidity={1:0.1f}%'.format(temperature,humidity))
return(temperature,humidity)
else:
print('Failed to get reading. Try again!')
return(0,0)
# 將溫濕度送到ThingSpeak上
def post_to_thingspeak2(payload):
headers = {"Content-type": "application/x-www-form-urlencoded","Accept": "text/plain"}
not_connected = 1
while (not_connected):
try:
conn = httplib.HTTPConnection("api.thingspeak.com",80,True,10)
conn.connect()
not_connected = 0
except (httplib.HTTPException, socket.error) as ex:
# logger.warning('Error: %s',ex)
print "Error: %s" % ex
conn.request("POST", "/update", payload, headers)
response = conn.getresponse()
print( response.status, response.reason, payload, time.strftime("%c"))
if response.status!=200 and response.reason!='OK':
logger.warning('ThingSpeak Error: status:%s reason:%s. payload:%s time:%s' ,response.status, response.reason, payload, time.strftime("%c"))
data = response.read()
conn.close()
# 將溫濕度寫入資料庫中
def insert_to_mysql(db,dtnow,temp,humidity):
cursor = db.cursor()
strnow = dtnow
strtemp = "%.1f" %(temp)
strhumidity = "%.1f" %(humidity)
insert = ("INSERT INTO temp (datetime, temp, humidity) VALUES (%s, %s, %s)")
data = (strnow,strtemp,strhumidity)
cursor.execute(insert,data)
db.commit()
# 從資料庫中取得最後一筆溫濕度記錄
def get_last_record(db):
cursor = db.cursor()
cursor.execute("select * from temp order by datetime desc limit 1")
record = cursor.fetchone()
lastTime = record[0]
lastTemp = record[1]
lastHumidity = record[2]
# print "Last Datetime = %s Temp = %s Humidity = %s" %(lastTime,lastTemp,lastHumidity)
return (lastTime,lastTemp,lastHumidity)
# 從資料庫查詢從目前時間往前x天的溫濕度資料，畫圖用
def select_data(db,sDay):
cursor = db.cursor()
cursor.execute("select * from temp where datetime BETWEEN DATE_SUB(NOW(),INTERVAL " + str(sDay) +" DAY) AND NOW()")
rows = cursor.fetchall()
#for record in rows:
# print "%s %s %s" % (record[0],record[1],record[2])
df = pd.DataFrame([[ij for ij in i] for i in rows])
df.rename(columns={0: 'DateTime', 1: 'Temp', 2: 'Humidity'}, inplace=True);
df_temp = df.sort_values(['DateTime'], ascending=[1]);
df_humidity = df.sort_values(['DateTime'], ascending=[2]);
return (df_temp,df_humidity)
# 產生溫度的html檔
def build_temp(df,lastRecord,dbTempMax,dbTempMin):
hover = HoverTool(tooltips=[
("時間", "@x{%F %H:%M:%S}"),
("溫度", "@y{%0.2f} (°C)"),
],
formatters={
'x' : 'datetime',
'y' : 'printf',
},)
TOOLS = ["pan,wheel_zoom,box_zoom,reset,save",hover]
p = figure(plot_width=540,plot_height=250,x_axis_type="datetime",y_range=(0, 40), tools=TOOLS,toolbar_location = "above", title="溫度 ("+str(lastRecord[0])+" "+str(lastRecord[1])+"°C)")
p.xgrid.grid_line_color=None
p.ygrid.grid_line_alpha=0.5
p.xaxis.axis_label = '時間'
p.yaxis.axis_label = '攝氏(°C)'
p.line(df['DateTime'], df['Temp'], color='navy', alpha=0.5)
#p.line(df['DateTime'], df['Temp'], line_dash="4 4", line_width = 1,color='navy', alpha=0.5)
#p.circle(df['DateTime'],df['Temp'], size=10, color="navy", alpha=0.5)
p.add_layout(BoxAnnotation(top=dbTempMin, fill_alpha=0.1, fill_color='red'))
p.add_layout(BoxAnnotation(bottom=dbTempMin, top=dbTempMax, fill_alpha=0.1, line_color='olive', fill_color='olive'))
p.add_layout(BoxAnnotation(bottom=dbTempMax, fill_alpha=0.1, fill_color='red'))
output_file(fPath+"db_temp.html")
show(p)
# 產生濕度的html檔
def build_humidity(df,lastRecord,dbHumidityMax,dbHumidityMin):
hover = HoverTool(tooltips=[
("時間", "@x{%F %H:%M:%S}"),
("濕度", "@y{%0.2f} (%)"),
],
formatters={
'x' : 'datetime',
'y' : 'printf',
},)
TOOLS = ["pan,wheel_zoom,box_zoom,reset,save",hover]
p = figure(plot_width=540,plot_height=250,x_axis_type="datetime",y_range=(20,105), tools=TOOLS,toolbar_location = "above", title="濕度 ("+str(lastRecord[0])+" "+str(lastRecord[2])+"%)")
p.xgrid.grid_line_color=None
p.ygrid.grid_line_alpha=0.5
p.xaxis.axis_label = '時間'
p.yaxis.axis_label = '百分比(%)'
p.line(df['DateTime'], df['Humidity'], color='navy', alpha=0.5)
p.add_layout(BoxAnnotation(top=dbHumidityMin, fill_alpha=0.1, fill_color='red'))
p.add_layout(BoxAnnotation(bottom=dbHumidityMin, top=dbHumidityMax, fill_alpha=0.1, line_color='olive', fill_color='olive'))
p.add_layout(BoxAnnotation(bottom=dbHumidityMax, fill_alpha=0.1, fill_color='red'))
output_file(fPath+"db_humidity.html")
show(p)
# 讀取資料庫中config資料表中的設定，id=0
def get_config(db):
cursor = db.cursor()
cursor.execute("select * from config where id=0")
record = cursor.fetchone()
return(record[1],record[2],record[3],record[4],record[5],record[6],record[7],record[8])
# 傳送email
def send_email(db,lastDatetime,lastTemp,lastHuminidy,newDatetime,newTemp,newHuminidy,dbTempMax,dbTempMin,dbHumidityMax,dbHumidityMin,dbTimeOffset,dbDifference,timeoffset,errId):
cursor = db.cursor()
cursor.execute("select * from smtp where id=0")
record = cursor.fetchone()
fromaddr = record[2]
toaddr = record[3]
pwd = decrypt_des(record[6])
sendTime = record[7]
sendStatus = record[8]
sendWaitTime = record[9]
sendCheckTime = record[10]
sendTimeOffset = (newDatetime - sendTime).seconds
goSendMail = False
print 'errId=' +str(errId)
print 'sendTimeOffset=' +str(sendTimeOffset)
if errId==0:
#errId=0代表溫濕度正常，但還要加入檢查，如果上一次寄送異常mail的時間和目前時間相比是否大於所設定的sendCheckTime，大於的話才切換為正常
if sendStatus==1 and (sendTimeOffset > sendCheckTime):
update_smtp_send_status(db,newDatetime,0)
#傳送回復正常的mail
msg = MIMEMultipart()
msg['From'] = fromaddr
msg['To'] = toaddr
msg['Subject'] = "Go38 溫濕度正常"
bodytable = """<font color="#00A800">溫濕度正常！</font><br>"""
bodytable += """
<br><font color="blue">*溫度正常範圍：""" + str(dbTempMin) + """ ~ """ + str(dbTempMax) + """ (°C)</font><br>
<font color="blue">*濕度正常範圍：""" + str(dbHumidityMin) + """ ~ """ + str(dbHumidityMax) + """ (%)</font><br>
"""
bodytable += """
<table align="left" border="1" cellpadding="1" cellspacing="1" style="width:500px">
<tbody>
<tr>
<td style="text-align:center"><a href="http://"""+str(record[1])+""" " target="_blank">"""+str(record[1])+"""</a></td>
<td style="text-align:center">日期</td>
<td style="text-align:center">溫度(°C)</td>
<td style="text-align:center">濕度(%)</td>
</tr>
<tr>
<td style="text-align:center">上一筆資料</td>
<td style="text-align:center">"""+ str(lastDatetime) +"""</td>
<td style="text-align:center">"""+ str(lastTemp) +"""</td>
<td style="text-align:center">"""+ str(lastHuminidy) +"""</td>
</tr>
<tr>
<td style="text-align:center">最新資料</td>
<td style="text-align:center">"""+ str(newDatetime) +"""</td>
<td style="text-align:center">"""+ str(newTemp) +"""</td>
<td style="text-align:center">"""+ str(newHuminidy) +"""</td>
</tr>
</tbody>
</table>
"""
msg.attach(MIMEText(bodytable, 'html', 'utf-8') )
server = smtplib.SMTP(record[4], record[5])
server.starttls()
server.login(fromaddr, pwd)
text = msg.as_string()
server.sendmail(fromaddr, toaddr, text)
server.quit()
else:
#最後傳送狀態，0：傳送正常，1：傳送異常
if sendStatus == 0:
goSendMail = True
elif sendStatus == 1:
#最後一次送mail的時間和目前時間相減，相差的秒數大於所設定的sendWaitTime，則再傳送一次異常mail
if sendTimeOffset > sendWaitTime:
goSendMail = True
##傳送溫濕度異常的mail
if goSendMail==True:
update_smtp_send_status(db,newDatetime,1)
msg = MIMEMultipart()
msg['From'] = fromaddr
msg['To'] = toaddr
msg['Subject'] = "Go38 溫濕度異常"
#body = str(record[1]) + "MAIL測試"
if errId==1:
bodytable = """<font color="red">偵測器回傳的溫濕度不在 0~100 之間，未寫入資料庫！</font><br>"""
elif errId==2:
bodytable = """<font color="red">目前時間和資料庫中最後一筆資料的時間超過"""+str(dbTimeOffset)+"""秒就直接寫入資料庫，不比對正負值！</font><br>"""
elif errId==3:
bodytable = """<font color="red">目前溫度與最後一筆資料相比，在"""+str(timeoffset)+"""秒內超過所設定的正負值"""+str(dbDifference)+"""！</font><br>"""
elif errId==4:
bodytable = """<font color="red">目前濕度與最後一筆資料相比，在"""+str(timeoffset)+"""秒內超過所設定的正負值"""+str(dbDifference)+"""！</font><br>"""
elif errId==5:
bodytable = """<font color="red">目前溫度或濕度不在所設定的正常範圍內！</font><br>"""
else:
bodytable = """<font color="red">沒有此errId<br>"""
bodytable += """
<br><font color="blue">*溫度正常範圍：""" + str(dbTempMin) + """ ~ """ + str(dbTempMax) + """ (°C)</font><br>
<font color="blue">*濕度正常範圍：""" + str(dbHumidityMin) + """ ~ """ + str(dbHumidityMax) + """ (%)</font><br>
"""
bodytable += """
<table align="left" border="1" cellpadding="1" cellspacing="1" style="width:500px">
<tbody>
<tr>
<td style="text-align:center"><a href="http://"""+str(record[1])+""" " target="_blank">"""+str(record[1])+"""</a></td>
<td style="text-align:center">日期</td>
<td style="text-align:center">溫度(°C)</td>
<td style="text-align:center">濕度(%)</td>
</tr>
<tr>
<td style="text-align:center">上一筆資料</td>
<td style="text-align:center">"""+ str(lastDatetime) +"""</td>
<td style="text-align:center">"""+ str(lastTemp) +"""</td>
<td style="text-align:center">"""+ str(lastHuminidy) +"""</td>
</tr>
<tr>
<td style="text-align:center">最新資料</td>
<td style="text-align:center">"""+ str(newDatetime) +"""</td>
<td style="text-align:center">"""+ str(newTemp) +"""</td>
<td style="text-align:center">"""+ str(newHuminidy) +"""</td>
</tr>
</tbody>
</table>
"""
# msg.attach(MIMEText(body, 'plain'))
msg.attach(MIMEText(bodytable, 'html', 'utf-8') )
# msg = MIMEText(mail_msg, 'html', 'utf-8')
server = smtplib.SMTP(record[4], record[5])
server.starttls()
server.login(fromaddr, pwd)
text = msg.as_string()
server.sendmail(fromaddr, toaddr, text)
server.quit()
# 更新資料表smtp中的SendTime與SendStatus
def update_smtp_send_status(db,dSendTime,dSendStatus):
cursor = db.cursor()
cursor.execute("update smtp set send_time='"+ str(dSendTime) +"' , send_status='"+str(dSendStatus)+"' where id=0")
db.commit()
# 解密，主要用於資料表中smtp的smtp_password欄位，加密過程還加了一組key，目前設定為"@Go38Box"
def decrypt_des(encrypted):
if encrypted is None:
return ""
try:
key = '@Go38Box'
# ECB方式
generator = DES.new(key, DES.MODE_ECB)
# 解碼
crypted_str = base64.b64decode(encrypted)
# 解密
result = generator.decrypt(crypted_str)
# 替換非空白字元
result = result.strip("")
#print "encrypted : "+str(encrypted)+" cipher :"+result
return result
except Exception, e:
print Exception, ":", e
return ""
# end main
if __name__ == "__main__":
sys.exit(main())
