# -*- coding: utf-8 -*- #!/usr/bin/env python import sys,os 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 from apps.web.device.models import SIMCard,Device import xlrd from xlrd import xldate_as_tuple from apilib.utils_datetime import first_day_datetime_of_month from apps.web.dealer.models import DealerRechargeRecord # 专门导入电信的卡信息到数据库中。电信的卡给定的excel的位数比设备上报的少1位。如果走原来的流程,会导致设备的过期时间无法更新。 supplier = sys.argv[1] excelFile = sys.argv[2] if supplier not in ['tianyu','qiben']: print 'supplier para is error!' 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 = [] okCount = 0 iccids = [] simDict = {} for i in range(1, nrows): row = sheet.row_values(i) for ii in range(3): try: if supplier == 'qiben': if (not row[6]) or (not row[7]): SIMCard.get_collection().update_one({'iccid':row[1]},{'$set':{'iccid':row[1],'imsi':row[2],'supplier':supplier,'provider':u'电信'}},upsert=True) break activeTime = datetime.datetime.strptime(row[6] + ' 00:00:00',"%Y-%m-%d %H:%M:%S") expireTime = datetime.datetime.strptime(row[7] + ' 00:00:00',"%Y-%m-%d %H:%M:%S") SIMCard.get_collection().update_one({'iccid':row[1]},{'$set':{'iccid':row[1],'imsi':row[2],'supplier':supplier,'provider':u'电信','expireTime':expireTime}},upsert=True) iccids.append({'expireTime':expireTime,'iccid':row[1]}) elif supplier == 'tianyu': if (not row[11]) or (not row[12]): SIMCard.get_collection().update_one({'iccid':row[1]},{'$set':{'iccid':row[1],'imsi':row[2],'supplier':supplier,'provider':u'电信'}},upsert=True) break activeTime = datetime.datetime.strptime(row[11],"%Y-%m-%d %H:%M:%S") expireTime = datetime.datetime.strptime(row[12],"%Y-%m-%d %H:%M:%S") SIMCard.get_collection().update_one({'iccid':row[1]},{'$set':{'iccid':row[1],'imsi':row[2],'supplier':supplier,'provider':u'电信','expireTime':expireTime}},upsert=True) iccids.append({'expireTime':expireTime,'iccid':row[1]}) okCount += 1 if okCount % 300 == 0: print okCount break except Exception,e: print 'some error when update iccid=%s,e=%s' % (row[1],e) print u'总共的数据为:%s条,成功导入:%s条' % (nrows-1,okCount) print u'开始往设备同步电信卡的过期时间' devNos = [] # 当月已经充值的设备,更新状态的时候,不能更新为updated startTime = first_day_datetime_of_month() - datetime.timedelta(days=60) endTime = first_day_datetime_of_month() + datetime.timedelta(days=31) objs = DealerRechargeRecord.objects.filter(finishedTime__gte = startTime,finishedTime__lte = endTime,status = 'Paid') for obj in objs: devNos.extend([item['devNo'] for item in obj.items]) for icDict in iccids: try: devObj = Device.objects(iccid__icontains = icDict['iccid']).first() if devObj is None: continue if devObj.devNo in devNos: continue devObj.simExpireDate = icDict['expireTime'] devObj.simStatus = 'updated' devObj.simChargeAuto = True devObj.save() Device.invalid_many_device_cache([devObj.devNo]) print 'updated one device OK,devNo=',devObj.devNo except Exception,e: continue