manyi/script/spider_data/lyy_dealer_stats.py

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

import os,json
import urllib, urllib2, sys
import ssl
import xlrd
from xlrd import xldate_as_tuple
from collections import OrderedDict
from apps.web.core.utils import generate_excel_report
# 统计乐摇摇的经销商数据，按照设备量的大小，从高统计 https://m.leyaoyao.com/customer/message/t/81202242

rootdir = u'F:/Temp/lyy'

# 查询手机归属地的代码
def get_tel_zone(tel):
    if 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 '',''
    
# 遍历设备，并登记到经销商

dealerDict = {}
allCount = 0
listFile = os.listdir(rootdir)  # 列出文件夹下所有的目录与文件
for i in range(0, len(listFile)):
    path = os.path.join(rootdir, listFile[i])
    try:
        book = xlrd.open_workbook(path)
        sheet = book.sheet_by_name("sheet1")
    except Exception,e:
        print 'open excel file error =%s' % e
        continue
    
    nrows = sheet.nrows
    rows = []
    for i in range(1, nrows):
        row = sheet.row_values(i)
        allCount += 1
        try:
            tel = row[1]
            if tel not in dealerDict:
                dealerDict[tel] = {'count':1,'name':row[3],'devList':[row[0]]}
            else:
                dealerDict[tel]['count'] += 1
                if len(dealerDict[tel]['devList']) < 10:
                    dealerDict[tel]['devList'].append(str(row[0]))
                    
        except Exception,e:
            print 'some error when update e=%s' % (e)

dealerList = []        
for k,v in dealerDict.items():
    tempStr = ''
    try:
        tempStr = ','.join(v['devList'])
    except Exception,e:
        tempStr = ''
    dealerList.append((k,v['count'],v['name'],tempStr))
    
def cmp_xy(x,y):
    if x[1] < y[1]:
        return -1
    elif x[1] == y[1]:
        return 0
    return 1

dealerList.sort(cmp=cmp_xy, reverse=True)

records = []
for dealer in dealerList:
    try:
        print u'电话：%s,数量：%s,设备名称：%s,设备编号举例：%s' % (dealer[0],dealer[1],dealer[2],dealer[3])
    except Exception,e:
        continue
    
    province,city = get_tel_zone(dealer[0])
    dataList = [
        (u'电话', dealer[0]),
        (u'数量', dealer[1]),
        (u'设备名称', dealer[2]),
        (u'编号举例', dealer[3]),
        (u'手机归属省份', province),
        (u'手机归属城市', city),
    ]

    records.append(OrderedDict(dataList))

generate_excel_report('F:/Temp/lyy/202009.xlsx', records, localSave = True)
    
print u'共计：%s' % allCount