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

import os, sys,time,datetime
import urllib, urllib2, sys
import requests
from mongoengine import register_connection, PointField, DynamicDocument, StringField
import simplejson as json
import ssl
import xlrd
from xlrd import xldate_as_tuple
from collections import OrderedDict



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
from apps.web.core.utils import generate_excel_report

register_connection(alias = 'spider',
                    name = 'spider',
                    host = '211.159.224.10',
                    port = 27119,
                    username = 'service',
                    password = 'oOzjoQcO5DyyiN97AY0NpzJ6vztjNpx5',
                    authentication_source = 'admin')

class xcyDevice(Searchable):
    devNo = StringField(default = '')
    logicalCode = StringField(default = '')
    tel = StringField(default = '') 
    name = StringField(default = '')
    province = StringField(default = '')
    city = StringField(default = '')
    
    meta = {
        'collection': 'xcy_device',
        'db_alias': 'spider',
    }
    

def get_tel_zone(tel):
    if not tel or len(tel) <= 7:
        return '',''
    tel = tel.replace('-','')
    tel = tel.replace(' ','')
    print tel
    if tel[0] == '0':
        print '',''
    host = 'http://aiphone.market.alicloudapi.com'
    path = '/ai_mobile_number_belong_to_china/v1'
    method = 'GET'
    appcode = '8296fcd952e34713ba91fbdceb13e915'
    querys = 'MOBILE_NUMBER=%s' % tel
    bodys = {}
    url = host + path + '?' + querys
    try:
        request = urllib2.Request(url)
        request.add_header('Authorization', 'APPCODE ' + appcode)
        request.add_header('Content-Type', 'application/x-www-form-urlencoded; charset=UTF-8')
        response = urllib2.urlopen(request)
        content = response.read()
        if (content):
            result = json.loads(content)
            return result['ENTITY']['PROVINCE'],result['ENTITY']['CITY']
    except Exception,e:
        return '',''

records = []
telList = [obj.tel for obj in xcyDevice.objects.all() if obj.province=='' and obj.tel and obj.tel[0] != '0']
telList = list(set(telList))

for tel in telList:
    p,c = get_tel_zone(tel)
    print p,c,tel
    xcyDevice.get_collection().update({'tel':tel},{'$set':{'province':p,'city':c}},multi=True)
    
for obj in xcyDevice.objects.all():
    dataList = [
        (u'logialCode', obj.logicalCode),        
        (u'联系', obj.tel),
        (u'名称', obj.name),
        (u'省', obj.province),
        (u'市', obj.city),
    ]

    records.append(OrderedDict(dataList))

generate_excel_report('F:/xcy365.xlsx', records,True)

print('OK')