# -*- coding: utf-8 -*- #!/usr/bin/env python import threading import uuid import simplejson as json import sys import datetime from script.base import init_env, setup_logger, get_logger from apilib.utils_datetime import to_datetime init_env(interactive = False) if len(sys.argv) < 2: logger = get_logger(__name__) else: logger = setup_logger(filename = sys.argv[1], namespace = __name__) from django.conf import settings from apilib.utils_mqtt import get_share_prefix from apps.web.constant import Const, MQTT_GROUP from apps.web.device.models import Device, SIMCard from apps.web.core.mqtt_client import MqttClient from apps.web.user.models import MyUser,ConsumeRecord from mongoengine import connect from mongoengine import Document from mongoengine import StringField from apps.web.dealer.models import DealerRechargeRecord startTime = to_datetime('2019-11-01 00:00:00') endTime = to_datetime('2019-12-01 00:00:00') simObjs = SIMCard.objects.filter(expireTime__gte = startTime,expireTime__lte = endTime) iccids = [card.iccid for card in simObjs] devObjs = Device.objects.filter(iccid__in = iccids) print u'到期的卡一共有:',len(iccids),u'包含这些卡的设备数量有',devObjs.count() devIccids = [dev.iccid for dev in devObjs] allDevNos = [dev.devNo for dev in devObjs] needCheckIccid = list(set(iccids) - set(devIccids)) print u'如下这些SIM卡,没有绑定到设备上,可能没有上线过,也可能卡未被使用:' for ic in needCheckIccid: print ic chargedDevNos = [] objs = DealerRechargeRecord.objects.filter(status = 'Paid') countAll,devCount = 0,0 for obj in objs: countAll += obj.totalFee devCount += len(obj.items) chargedDevNos.extend([item['devNo'] for item in obj.items]) print u'有记录以来,总共充值的金额有 ',countAll,u'有记录以来,总共充值的设备数目',devCount # print 'those dev is expired but uncharged,please check...' # for dev in devObjs: # print dev.devNo print u'即将过期或者已经过期,但是还没有充值的清单如下::::' leftDevNos = list(set(allDevNos) - set(chargedDevNos)) devNolist2 = [] for devNo in leftDevNos: dev = Device.get_dev(devNo) print devNo,dev['ownerId'],dev['devType'].get('name',u'未知') # from apps.web.user.models import ConsumeRecord # print u'如下设备本月有订单,但是却没有充值,需要确认清楚:' # needChargedDevNos = [] # for devNo in leftDevNos: # count = ConsumeRecord.objects.filter(devNo = devNo,dateTimeAdded__gte = startTime,dateTimeAdded__lte = endTime,isNormal = True).count() # if count > 0: # dev = Device.get_dev(devNo) # print u'设备编号',devNo,'ownerId',dev.get('ownerId',u'解绑'),u'是否在线',dev.online # ownerId = dev['ownerId'] # if not ownerId: # continue # needChargedDevNos.append(devNo) # # devObjs = Device.objects.filter(devNo__in = needChargedDevNos) # iccids = [obj.iccid for obj in devObjs] # simCards = SIMCard.objects.filter(iccid__in = iccids) # hezhouList,qibenList = [],[] # for sim in simCards: # if sim.supplier == 'hezhou': # hezhouList.append(sim.iccid) # else: # qibenList.append(sim.iccid) # # print u'如下卡是合宙家的,本月有订单,未充值,建议卡续费,保留一个月' # for sim in hezhouList: # print sim # # print u'如下卡是qiben家的,本月有订单,未充值,建议卡续费,保留一个月' # for sim in qibenList: # print sim # print u'统计没有续费的设备,都是些什么类型的设备' # for devNo in leftDevNos: # dev = Device.get_dev(devNo) # print devNo,dev['devType']['name'] # print devNo # import xlrd # from xlrd import xldate_as_tuple # # #检查充值了,但是不在卡数据里面的卡 # book = xlrd.open_workbook(u'F:/SIM卡数据导入/2019-06-24/合宙武汉大源科技有限公司6月份到期卡.xlsx') # book1 = xlrd.open_workbook(u'F:/SIM卡数据导入/2019-06-24/齐犇武汉微弗智通科技有限公司-到期明细.xlsx') # # iccids = [] # # try: # sheet = book.sheet_by_name("Sheet") # 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: # iccids.append(row[1].replace('\t','')) # except Exception,e: # print 'some error when update iccid=%s,e=%s' % (row[1],e) # # try: # sheet = book1.sheet_by_name("Sheet") # 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: # iccids.append(row[1].replace('\t','')) # except Exception,e: # print 'some error when update iccid=%s,e=%s' % (row[1],e) # # devObjs = Device.objects.filter(iccid__in = iccids) # print 'expire ic card num is ',len(iccids),'the device of sim card ,num is',devObjs.count() # allDevNos = [dev.devNo for dev in devObjs] # # startTime = to_datetime('2019-06-01 00:00:00') # endTime = to_datetime('2019-07-01 00:00:00') # objs = DealerRechargeRecord.objects.filter(finishedTime__gte = startTime,finishedTime__lte = endTime,status = 'Paid') # countAll,devCount = 0,0 # chargedDevNos = [] # for obj in objs: # countAll += obj.totalFee # devCount += len(obj.items) # chargedDevNos.extend([item['devNo'] for item in obj.items]) # # leftDevNos = list(set(allDevNos) - set(chargedDevNos)) # for devNo in leftDevNos: # print devNo