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