# -*- 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

# 统计U净用户的数据
register_connection(alias = 'spider',
                    name = 'spider',
                    host = '116.62.228.194',
                    port = 27017,
                    username = 'dba',
                    password = 'dayuan@2020..',
                    authentication_source = 'admin')


class ujing(Searchable):
    meta = {
        'collection': 'ujing',
        'db_alias': 'spider',
        'unique_together': {'_id'}
    }

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 '',''
    
ownerDict = {}
for shopInfo in ujing.get_collection().find():
    if ownerDict.has_key(shopInfo['mobile']):
        ownerDict[shopInfo['mobile']] += shopInfo.get('deviceCount',0)
    else:
        ownerDict[shopInfo['mobile']] = shopInfo.get('deviceCount',0)

records = []
for mobile,count in ownerDict.items():
    zone = get_tel_zone(mobile)
    print zone[0],zone[1],mobile,count
    dataList = [
        (u'省份', zone[0]),
        (u'城市', zone[1]),
        (u'通讯', mobile),
        (u'数量', count),
    ]

    records.append(OrderedDict(dataList))

generate_excel_report('F:/test1.xlsx', records,False)