# -*- coding: utf-8 -*- # !/usr/bin/env python import os, sys from collections import OrderedDict import datetime 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.production') from script.base import init_env init_env(interactive = False) from apps.web.device.models import Device from apps.web.core.utils import generate_excel_report from apilib.utils_datetime import to_datetime, first_day_datetime_of_month from apps.web.user.models import ConsumeRecord from apps.web.dealer.models import Dealer months = [5] def print_uncharged_dev(startTime, endTime): dealerDict = {} devObjs = Device.objects.filter(simExpireDate__gte = startTime, simExpireDate__lte = endTime, simStatus = 'updated', ownerId__ne = None) for devObj in devObjs: # type: Device if devObj.logicalCode.startswith('B'): print('device<{}> is bt device.'.format(devObj.logicalCode)) continue ownerId = devObj.ownerId if not ownerId: continue if ownerId not in dealerDict: dealer = Dealer.objects.get(id = ownerId) dealerDict[ownerId] = { 'username': dealer.username, 'nickname': dealer.nickname, 'count': 1, 'devTypeDict': { devObj.devType['name']: 1 }, 'devNos': [devObj.devNo] } continue dealerDict[ownerId]['count'] += 1 dealerDict[ownerId]['devNos'].append(devObj.devNo) if not dealerDict[ownerId]['devTypeDict'].has_key(devObj.devType['name']): dealerDict[ownerId]['devTypeDict'][devObj.devType['name']] = 1 else: dealerDict[ownerId]['devTypeDict'][devObj.devType['name']] += 1 dealerList = dealerDict.values() def cmp_by_count(x, y): if x['count'] < y['count']: return -1 elif x['count'] > y['count']: return 1 return 0 dealerList = sorted(dealerList, cmp_by_count) allCount = 0 records = [] for info in dealerList: tempList = [u'%s的设备有%s台' % (k, v) for k, v in info['devTypeDict'].items()] print info['username'], info['nickname'], info['count'], ','.join(tempList), info['devNos'] dataList = [ (u'经销商账号', info['username']), (u'经销商昵称', info['nickname']), (u'数量', info['count']), (u'设备类型分布', ','.join(tempList)), (u'过期设备列表', ','.join(info['devNos'])), (u'拜访记录', ''), (u'备注', '') ] records.append(OrderedDict(dataList)) allCount += info['count'] generate_excel_report(u'过期未充值.xlsx', records, localSave = True) print u'未充值的设备合计:', allCount def print_unregister_dev(startTime, endTime): dealerDict = {} devObjs = Device.get_collection().find({'ownerId': '', 'registerLog.operation': 'unregister'}, {'registerLog': 1, 'devNo': 1}) for devObj in devObjs: operLog = devObj['registerLog'][-1] ownerId = str(operLog['ownerId']) if not (operLog['dateTimeAdded'] >= startTime and operLog['dateTimeAdded'] <= endTime): continue if not dealerDict.has_key(ownerId): dealer = Dealer.objects.get(id = ownerId) dealerDict[ownerId] = { 'id': ownerId, 'username': dealer.username, 'nickname': dealer.nickname, 'count': 1, 'devNos': [devObj['devNo']] } continue dealerDict[ownerId]['count'] += 1 dealerDict[ownerId]['devNos'].append(devObj['devNo']) dealerList = dealerDict.values() def cmp_by_count(x, y): if x['count'] < y['count']: return -1 elif x['count'] > y['count']: return 1 return 0 dealerList = sorted(dealerList, cmp_by_count) allCount = 0 records = [] for info in dealerList: rcds = ConsumeRecord.get_collection().find({'devNo': {'$in': info['devNos']}}, {'devType': 1}) devTypeNames = [rcd['devType'] for rcd in rcds[0:10]] devTypeNames = list(set(devTypeNames)) print info['username'], info['nickname'], info['count'], ','.join(devTypeNames), info['id'], info['devNos'] allCount += info['count'] dataList = [ (u'经销商账号', info['username']), (u'经销商昵称', info['nickname']), (u'数量', info['count']), (u'设备类型分布', ','.join(devTypeNames)), (u'经销商ID', info['id']), (u'过期设备列表', ','.join(info['devNos'])), (u'拜访记录', ''), (u'备注', '') ] records.append(OrderedDict(dataList)) generate_excel_report(u'解绑定设备.xlsx', records, localSave = True) print u'解绑的设备合计:', allCount for month in months: startTime = to_datetime('2020-%s-01 00:00:00' % month) timeTemp = startTime + datetime.timedelta(hours = 24 * 40) endTime = first_day_datetime_of_month(timeTemp) print u'如下是到期没有充值的名单' print_uncharged_dev(startTime, endTime) print '--------------------------------------------------------------------------------------------------------' print u'如下是解绑设备的名单' print_unregister_dev(startTime, endTime) print 'finished.'