from operator import itemgetter
from django.db.models import Q
from io import BytesIO
from PIL import Image
import re
import socket
import xlwt
import xlrd
import os
import django
import sys

BASE_DIR = os.path.dirname(os.path.abspath(__file__))  # 定位到你的django根目录
sys.path.append(os.path.abspath(os.path.join(BASE_DIR, os.pardir)))

os.environ.setdefault("DJANGO_SETTINGS_MODULE",
                      "yfwlw_pro.settings")  # project_name 项目名称
django.setup()

from django.db import transaction
import random
import shutil
import cv2
from imutils import paths
from requests.auth import HTTPBasicAuth
import paho.mqtt.client as mqtt
# from apps.SimInfo.views import get_siminfo
from apps.AppInfoManage.models import Alarm_record, BZYdata, BZYphoto, CBDphoto, CBDphoto_desc, CBDstatus, CBDstatus_all, Equip, Equip_SimInfo, Equip_type, JKdata, JKphoto, JKphoto, Msg_Conf, Msg_Send, MyUser, QXZ_Conf, QXZ_Default_Conf, QXZdata, QXZdata_New, QXZstatus, QXZstatus_New, RecentCBDdata, RecentSCDdata, SCDdata, SCDstatus, SCDstatus_all, SCDstatus_all, User_Log, YBQstatus
from apps.ReportManage.all_dict import mobile_list, insect_dict_new, qxz_dict
import datetime
import requests
import time
import json
from qcloudsms_py import SmsSingleSender
from qcloudsms_py.httpclient import HTTPError
from qcloudsms_py import SmsMultiSender





# 短信应用 SDK AppID
appid = 1400227496  # SDK AppID 以1400开头
# 短信应用 SDK AppKey
appkey = "c9aba961bd30072f1e1b9272077abaea"

# print(random.randint(10,30))
# 测试1


# --------------------------------------------------------------

# 10.17号 统计发出去的杀虫灯是否在线， 调出环境温湿度不等于25、30，不在线的
book1 = xlwt.Workbook()  # 创建一个excel对象
sheet1 = book1.add_sheet('Sheet1', cell_overwrite_ok=True)  # 添加一个sheet页
sheet2 = book1.add_sheet('Sheet2', cell_overwrite_ok=True)  # 添加一个sheet页
title = ["设备号", "在线状态", "温度", "湿度"]
for x in range(len(title)):
    sheet1.write(0, x, title[x])
    sheet2.write(0, x, title[x])

book = xlrd.open_workbook('scd.xlsx')
# book=xlrd.open_workbook('2020.07.13.xlsx')
sheet = book.sheet_by_name('Sheet1')
lists = sheet.col_values(0)  # 获取到整列的内容
data = []  # 未找到的设备
# # lists = []
date = []  # 设备离线
datp = []  # 温湿度异常

dat = []  # 境温湿度等于25、30
da = []  # 在线的设备

for i in lists:
    try:
        scd_status = RecentSCDdata.objects.get(equip_id=i)
        scd_data = eval(scd_status.scd_data)
        equip_list = SCDstatus.objects.get(equip_id=i)
        res = requests.post("http://api.map.baidu.com/geocoder?location=%s,%s&coord_type=gcj02&output=json" %
                            (scd_data["lat"], scd_data["lng"]))
        addr = (eval(res.text))["result"]["addressComponent"]
        district = addr["district"]
        is_online = equip_list.is_online
        if district not in ["惠济区", "金水区"]:
            pass
            # print("设备所在地：", district)
            # if is_online == "0":
            #     dap = [i,is_online,scd_data["at"],scd_data["ah"]]
            #     print("离线的设备：",dap)
            #     date.append(dap)
            # else:
            #     da.append(i)
            # if scd_data["at"] == "25.0" and scd_data["ah"] == "30.0":
            #     dape = [i, is_online, scd_data["at"], scd_data["ah"]]
            # datp.append(dape)
        else:
            # print(type(is_online))
            if is_online == "0":
                dat.append({"equip":i,"addr":district})
            else:
                da.append({"equip":i,"addr":district})
    except:
        data.append(i)

# print("未在线设备个数：", len(dat))
# print("在线设备个数：", len(da))
print("未在线设备：", dat)
print("在线设备：", da)

# print(len(date))
# print(len(dape))

# for y in range(len(date)):
#     for i in range(len(date[y])):
#         sheet1.write(y+1, i, date[y][i])
# book1.save("5.xlsx")


# for y in range(len(datp)):
#     for i in range(len(datp[y])):
#         sheet2.write(y+1, i, datp[y][i])
# book1.save("6.xlsx")
# print("平台上未找到设备号", data)
# print("在惠济区、金水区设备", dat)

# ---------------------------------------------------------------


# # 9.10号分配邱玲195台设备

# username = MyUser.objects.get(username="南京睿泽生物技术有限公司")
# book=xlrd.open_workbook('邱玲.xlsx')

# sheet=book.sheet_by_name('Sheet1')
# lists = sheet.col_values(0)#获取到整列的内容
# datt = [] #已绑定用户
# dat = [] #未找到设备号
# for i in lists:
#     equip_list = Equip.objects.filter(equip_id=str(int(i)))
#     if equip_list:
#         for x in equip_list:
#             if x.equip_user:
#                 datt.append(x.equip_id)
#             else:
#                 x.equip_user = username
#                 x.save()
#     else:
#         dat.append(str(int(i)))
# print("设备已绑定用户=====",datt)
# print("未找到设备号=====",dat)