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


ownerDict = {}
def from_exl(fileName,ownerDict):
    book = xlrd.open_workbook(fileName)
    
    try:
        sheet = book.sheet_by_name("dongjun_devices")
    except Exception,e:
        print 'open excel file error =%s' % e
        exit(0)
    
    nrows = sheet.nrows
    rows = []
    for i in range(1, nrows):
        row = sheet.row_values(i)
        try:
            mobile = str(row[13]).replace('\t','').upper()
            if mobile in ownerDict:
                ownerDict[mobile] += 1
            else:
                ownerDict[mobile] = 1
        except Exception,e:
            print 'some error when update iccid=%s,e=%s' % (row[1],e)

from_exl(u'Q:\友商信息\友商分析\dongjun_devices2.xls', ownerDict)
from_exl(u'Q:\友商信息\友商分析\dongjun_devices3.xls', ownerDict)
from_exl(u'Q:\友商信息\友商分析\dongjun_devices4.xls', ownerDict)

records = []
for mobile,count in ownerDict.items():
    dataList = [
        (u'联系', mobile),
        (u'数量', count),
    ]

    records.append(OrderedDict(dataList))

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

print('OK')