manyi/script/simCard/check_expire_dev_from_excel.py

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