import pymongo
from urllib import parse
import re 
from openpyxl import load_workbook
import datetime
# 打开Excel文件
workbook = load_workbook('tools\example.xlsx')

# 获取工作表
sheet = workbook['Sheet1']


user = parse.quote_plus("root")
passwd = parse.quote_plus("yfkj@6020")
host = parse.quote_plus("8.136.98.49")
port = "57017"
myclient = pymongo.MongoClient("mongodb://{0}:{1}@{2}:{3}/".format(user,passwd, host, port))
db = myclient.smartfarming
cbd_photo = db.sa_device_cbdphoto
device = db.sa_device
start = 1640966400
end = 1672502399
photo = cbd_photo.find({"addtime": {"$gte": start, "$lte": end}}).sort("device_id", 1)

for i in list(photo):
    indentify_result = i.get("indentify_result")
    sp = indentify_result.split("#")
    chong = {}
    for s in sp:
        csp = s.split(",")
        chong[csp[0]] = csp[1]
    last_row = sheet.max_row
    device_info = device.find_one({"id": i.get("id")})
    if "79" in chong.keys():
        sheet.insert_rows(last_row + 1)
        pest_num = chong["79"]
        province = device_info.get("province")
        city = device_info.get("city")
        district = device_info.get("district")
        lng = device_info.get("lng")
        lat = device_info.get("lat")
        at = i.get("at")
        ah = i.get("ah")
        addtime = i.get("addtime")
        date = datetime.datetime.fromtimestamp(addtime)
        datestr = date.strftime("%Y%m%d%H:%M:%S")
        sheet.cell(row=last_row + 1, column=1).value = device_info.get("device_id")
        sheet.cell(row=last_row + 1, column=2).value = "玉米螟"
        sheet.cell(row=last_row + 1, column=3).value = chong["79"]
        sheet.cell(row=last_row + 1, column=3).value = date
        sheet.cell(row=last_row + 1, column=3).value = date.year
        sheet.cell(row=last_row + 1, column=3).value = date.month
        sheet.cell(row=last_row + 1, column=3).value = date.day
        sheet.cell(row=last_row + 1, column=3).value = date.hour
        sheet.cell(row=last_row + 1, column=3).value = date.minute
        sheet.cell(row=last_row + 1, column=3).value = date.second
        sheet.cell(row=last_row + 1, column=3).value = province + city + district
        sheet.cell(row=last_row + 1, column=3).value = province
        sheet.cell(row=last_row + 1, column=3).value = city 
        sheet.cell(row=last_row + 1, column=3).value = district 
        sheet.cell(row=last_row + 1, column=3).value = at 
        sheet.cell(row=last_row + 1, column=3).value = ah 
        sheet.cell(row=last_row + 1, column=3).value = lng  
        sheet.cell(row=last_row + 1, column=3).value = lat
    if "52" in chong.keys():
        sheet.insert_rows(last_row + 1)
        pest_num = chong["52"]
        province = device_info.get("province")
        city = device_info.get("city")
        district = device_info.get("district")
        lng = device_info.get("lng")
        lat = device_info.get("lat")
        at = i.get("at")
        ah = i.get("ah")
        addtime = i.get("addtime")
        date = datetime.datetime.fromtimestamp(addtime)
        datestr = date.strftime("%Y%m%d%H:%M:%S")
        sheet.cell(row=last_row + 1, column=1).value = device_info.get("device_id")
        sheet.cell(row=last_row + 1, column=2).value = "桃蛀螟"
        sheet.cell(row=last_row + 1, column=3).value = chong["52"]
        sheet.cell(row=last_row + 1, column=3).value = date
        sheet.cell(row=last_row + 1, column=3).value = date.year
        sheet.cell(row=last_row + 1, column=3).value = date.month
        sheet.cell(row=last_row + 1, column=3).value = date.day
        sheet.cell(row=last_row + 1, column=3).value = date.hour
        sheet.cell(row=last_row + 1, column=3).value = date.minute
        sheet.cell(row=last_row + 1, column=3).value = date.second
        sheet.cell(row=last_row + 1, column=3).value = province + city + district
        sheet.cell(row=last_row + 1, column=3).value = province
        sheet.cell(row=last_row + 1, column=3).value = city 
        sheet.cell(row=last_row + 1, column=3).value = district 
        sheet.cell(row=last_row + 1, column=3).value = at 
        sheet.cell(row=last_row + 1, column=3).value = ah 
        sheet.cell(row=last_row + 1, column=3).value = lng  
        sheet.cell(row=last_row + 1, column=3).value = lat