manyi/script/simCard/import_sim_card.py

# -*- coding: utf-8 -*-
#!/usr/bin/env python

import os
import sys
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 apilib.utils_datetime import to_datetime
from apps.web.device.models import SIMCard
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'
    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('cardInfo')
        
except Exception,e:
    print 'open excel file error =%s' % e
    exit(0)

nrows = sheet.nrows
rows = []
okCount = 0
iccids = []

for i in range(1, nrows):
    row = sheet.row_values(i)
    for ii in range(3):
        try:
            if supplier == 'qiben':
                if (not row[14]) or (not row[15]):
                    continue
                expireTime = to_datetime(row[15], time_format= "%Y-%m-%d")
                SIMCard.get_collection().update_one({'iccid':str(row[0]).upper()},{'$set':{'iccid':str(row[0]).upper(),'imsi':row[1],'supplier':supplier,'expireTime':expireTime}},upsert=True)
                SIMCard.get_collection().update_one({'iccid':str(row[0]).lower()},{'$set':{'iccid':str(row[0]).lower(),'imsi':row[1],'supplier':supplier,'expireTime':expireTime}},upsert=True)
                
            elif supplier == 'hezhou':
                if (not row[6]) or (not row[7]):
                    continue
#                 activeTime = datetime.datetime(*xldate_as_tuple(row[8], 0))
                
                expireTime = datetime.datetime(*xldate_as_tuple(row[7], 0))
                iccid = str(row[1]).replace('\t', '')
                imsi = str(row[0]).replace('\t', '')
                SIMCard.get_collection().update_one({'iccid':iccid},{'$set':{'iccid':iccid,'imsi':imsi,'supplier':supplier,'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[10]) or (not row[11]):
                    continue
                expireTime = to_datetime(row[11])
                SIMCard.get_collection().update_one({'iccid':str(row[0]).upper()},{'$set':{'iccid':str(row[0]).upper(),'imsi':row[2],'supplier':supplier,'expireTime':expireTime}},upsert=True)
                SIMCard.get_collection().update_one({'iccid':str(row[0]).lower()},{'$set':{'iccid':str(row[0]).lower(),'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)

# Device.get_collection().update
print u'总共的数据为：%s条，成功导入：%s条' % (nrows-1,okCount)