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