manyi/script/simCard/check_expire_dev_and_simcard.py

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