manyi/script/simCard/compare_file_and_simcard.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 apilib.utils_datetime import to_datetime

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']:
    print u'卡供应商必须是qiben、hezhou'
    exit(0)
    
book = xlrd.open_workbook(excelFile)

try:
    if supplier == 'hezhou':
        sheet = book.sheet_by_name("Sheet")
    elif supplier == 'qiben':
        sheet = book.sheet_by_name("Sheet")
    elif supplier == 'jieyang':
        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 = {}
mapFromFile = {}


jj = 0
for i in range(1, nrows):
    row = sheet.row_values(i)
    
    if not row[18]:
        continue
    
    jj += 1
    
    expireTime = to_datetime(row[18] + ' 00:00:00')
    
    iccid = row[0]
    
    mapFromFile[iccid] = expireTime
    
    if jj % 1000 == 0 :
        iccids = mapFromFile.keys()
        cards = Device.get_collection().find({'iccid':{'$in':iccids}})
        for card in cards:
            tempiccid = card['iccid']
            if mapFromFile.has_key(tempiccid) and card.has_key('simExpireDate') and card.has_key('simStatus') and mapFromFile[tempiccid] != card['simExpireDate'] and card['simStatus'] != 'chargedUnupdated':
                print(tempiccid,mapFromFile[tempiccid],card['simExpireDate'])
        mapFromFile = {}
                            

iccids = mapFromFile.keys()
cards = SIMCard.get_collection().find({'iccid':{'$in':iccids}})
for card in cards:
    tempiccid = card['iccid']
    if mapFromFile.has_key(tempiccid) and  card.has_key('simExpireDate') and card.has_key('simStatus') and mapFromFile[tempiccid] != card['simExpireDate'] and card['simStatus'] != 'chargedUnupdated':
        print(tempiccid,mapFromFile[tempiccid],card['simExpireDate'])
mapFromFile = {}
        
print u'总共的数据为：%s条，成功导入：%s条' % (nrows-1,okCount)