# -*- coding: utf-8 -*-
# !/usr/bin/env python

import os, sys,time,datetime
import urllib
import requests
from mongoengine import register_connection, PointField, DynamicDocument, StringField
import simplejson as json
import urllib, urllib2, sys
import ssl
from django.db.models.fields import DateTimeField
import xlrd
from xlrd import xldate_as_tuple
from collections import OrderedDict
from apps.web.core.utils import generate_excel_report
PROJECT_ROOT = os.path.join(os.path.abspath(os.path.split(os.path.realpath(__file__))[0] + "/.."), '..')
sys.path.insert(0, PROJECT_ROOT)

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "configs.testing")

from script.base import init_env

init_env(interactive = False)

from apps.web.core.db import Searchable

register_connection(alias = 'spider',
                    name = 'spider',
                    host = '116.62.228.194',
                    port = 27017,
                    username = 'dba',
                    password = 'dayuan@2020..',
                    authentication_source = 'admin')

class xujiu_device_tel(Searchable):
    meta = {
        'collection': 'xujiu_dev_user',
        'db_alias': 'spider',
        'unique_together': {'gtel'}
    }

# 先把所有的设备二维码编号爬下来
# page=0
# while True:
#     url = 'https://trade.api3.sdaascloud.com/trade/nearbyChargers?key=&page=%s&size=10&latitude=30.48029&longitude=114.42073&raidus=3000000000' % page
#     strhtml = requests.get(url,timeout = 15).text
#     result = json.loads(strhtml)
#     if result.has_key('data') and result['data'].has_key('content') and len(result['data']['content']) == 0:
#         break
#     for dev in result['data']['content']:
#         xzzDevice.get_collection().update({'qrId':dev['qrId']},{'$set':dev},upsert = True)
#     page += 1

# 根据二维码编号，获取设备更详细的信息
def get_tel_zone(tel):
    if not tel or len(tel) <= 7:
        return '',''
    tel = tel.replace('-','')
    tel = tel.replace(' ','')
    print tel
    host = 'https://api04.aliyun.venuscn.com'
    path = '/mobile'
    method = 'GET'
    appcode = '8296fcd952e34713ba91fbdceb13e915'
    querys = 'mobile=%s' % tel
    bodys = {}
    url = host + path + '?' + querys
    try:
        request = urllib2.Request(url)
        request.add_header('Authorization', 'APPCODE ' + appcode)
        ctx = ssl.create_default_context()
        ctx.check_hostname = False
        ctx.verify_mode = ssl.CERT_NONE
        response = urllib2.urlopen(request, timeout = 15, context=ctx)
        content = response.read()
        if (content):
            result = json.loads(content)
            if result['msg'] != 'success':
                return '', ''
            return result['data']['prov'],result['data']['city']
    except Exception,e:
        return '',''

userDict = {}
for user in xujiu_device_tel.get_collection().find():
    if not user.has_key('manager_phone') or not user['manager_phone']:
        continue
    phone = user['manager_phone']
    if userDict.has_key(phone):
        userDict[phone] += 1
    else:
        userDict[phone] = 1
        
records = []
for phone,count in userDict.items():
    zone = get_tel_zone(phone)
    dataList = [
        (u'省份', zone[0]),
        (u'城市', zone[1]),
        (u'通讯', phone),
        (u'数量', count),
    ]

    records.append(OrderedDict(dataList))

generate_excel_report('F:/test4.xlsx', records,False)
print('OK')