# -*- coding: utf-8 -*- #!/usr/bin/env python import threading import uuid,os import simplejson as json import sys,time 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.update({"DJANGO_SETTINGS_MODULE": "configs.production"}) import django django.setup() from script.base import init_env, get_logger logger = get_logger(__name__) from apps.web.device.models import SIMCard,Device from apilib.utils_datetime import to_datetime from apps.web.dealer.models import DealerRechargeRecord from apps.web.constant import Const import datetime import xlrd from xlrd import xldate_as_tuple # 从SIM卡平台上导出来的excel,到设备表中,一条条比对,检查设备的过期时间是否正确,检查是否有遗漏的卡,没有对应到设备 supplier = sys.argv[1] excelFile = sys.argv[2] if supplier not in ['qiben','hezhou','jieyang','tianyu']: print u'卡供应商必须是qiben、hezhou' exit(0) book = xlrd.open_workbook(excelFile) try: sheet = book.sheet_by_name("sheet1") except Exception,e: print 'open excel file error =%s' % e exit(0) nrows = sheet.nrows rows = [] iccids = [] dxIccids = [] # 电信的iccid,不一样哟。设备报上来的比excel的多一位 for i in range(1, nrows): row = sheet.row_values(i) try: if u'电信' in row[7]: dxIccids.append(str(row[1].replace('\t','').upper())) dxIccids.append(str(row[1].replace('\t','').lower())) else: iccids.append(str(row[1].replace('\t','').upper())) iccids.append(str(row[1].replace('\t','').lower())) except Exception,e: print 'some error when update iccid=%s,e=%s' % (row[1],e) devIccids = [] devList= [] count = 0 devDict = {} devObjs = Device.objects(iccid__in = iccids,ownerId__ne = "") for devObj in devObjs: devDict[str(devObj.iccid).upper()] = devObj.simExpireDate from apilib.utils_datetime import first_day_datetime_of_month endDayMonth = to_datetime('2022-08-31 00:00:00') count = 0 for iccid in iccids: count += 1 if count % 500 == 0: print count if iccid not in devDict: continue else: devDate = devDict[iccid] if devDate is None: print u'设备上没有过期时间',devDate,iccid devObj = Device.objects(iccid = iccid,ownerId__ne = "").first() if devObj is None: continue devObj.simExpireDate = endDayMonth devObj.simStatus = 'updated' devObj.save() Device.invalid_many_device_cache([devObj.devNo]) print u'已经更正数据' continue deltaDays = (devDate - endDayMonth).days if deltaDays > 5 : print u'设备上的过期时间和月末日期相差太大',devDate,iccid devObj = Device.objects(iccid = iccid,ownerId__ne = "").first() if devObj is None: continue devObj.simExpireDate = endDayMonth devObj.simStatus = 'updated' devObj.save() Device.invalid_many_device_cache([devObj.devNo]) print u'已经更正数据' # 单独处理电信的数据 dxIccids = list(set(dxIccids)) count = 0 for iccid in dxIccids: count += 1 print count devObj = Device.objects(iccid__icontains = iccid,ownerId__ne = "").first() if devObj is None: continue else: devDate = devObj.simExpireDate if devDate is None: print u'电信卡,设备上没有过期时间',devDate,iccid devObj.simExpireDate = endDayMonth devObj.simStatus = 'updated' devObj.save() Device.invalid_many_device_cache([devObj.devNo]) print u'已经更正数据' continue deltaDays = (devDate - endDayMonth).days if deltaDays > 5 : print u'电信卡,设备上的过期时间和月末日期相差太大',devDate,iccid devObj.simExpireDate = endDayMonth devObj.simStatus = 'updated' devObj.save() Device.invalid_many_device_cache([devObj.devNo]) print u'已经更正数据' print 'check finished'