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