manyi/script/simCard/sync_sim_card_from_excel_to_device.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

# 从SIM卡平台上导出来的excel，导入到数据库中，便于流量结算。
# 1、月末：根据用户的充值情况，把SIM卡数据全部导出来，发给卡商，进行充值。
# 2、月头：卡商充值后，我们利用import_sim_card脚本，把excel导出来，然后导入到数据库，并执行另外一个脚本update_device_sim_info_from_simdb更新设备的超时时间


supplier = sys.argv[1]
excelFile = sys.argv[2]

if supplier not in ['qiben','hezhou','jieyang','tianyu']:
    print u'卡供应商必须是qiben、hezhou jieyang tianyu'
    exit(0)
    
book = xlrd.open_workbook(excelFile)

try:
    if supplier == 'hezhou':
        sheet = book.sheet_by_name("Sheet1")
    elif supplier == 'qiben':
        sheet = book.sheet_by_name("Sheet1")
    elif supplier == 'jieyang':
        sheet = book.sheet_by_name("Sheet1")
    elif supplier == 'tianyu':
        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}},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,'expireTime':expireTime}},upsert=True)
            elif supplier == 'hezhou':
                if (not row[9]) or (not row[10]):
                    SIMCard.get_collection().update_one({'iccid':row[1]},{'$set':{'iccid':row[1],'imsi':row[2],'supplier':supplier}},upsert=True)
                    break
                activeTime = datetime.datetime(*xldate_as_tuple(row[9], 0))
                
                expireTime = datetime.datetime(*xldate_as_tuple(row[10], 0))
                
                SIMCard.get_collection().update_one({'iccid':row[1]},{'$set':{'iccid':row[1],'imsi':row[2],'supplier':supplier,'activeTime':activeTime,'expireTime':expireTime}},upsert=True)
            elif supplier == 'jieyang':
                SIMCard.get_collection().update_one({'iccid':row[1]},{'$set':{'iccid':row[1],'imsi':row[2],'supplier':supplier,'activeTime':activeTime,'expireTime':expireTime}},upsert=True)
            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}},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,'expireTime':expireTime}},upsert=True)
            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)