manyi/script/simCard/sync_dianxin_sim_card_from_excel.py

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