def run_import(filename, hooktype, subtype=None):
if subtype == None: subtype = hooktype
data = xlrd.open_workbook(filename)
sheet = data.sheet_by_index(1)
nrows = sheet.nrows
manBranDict = {}
cust_sql = """
INSERT INTO YDW.CUST_HOOK(CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
account_sql = """
INSERT INTO YDW.ACCOUNT_HOOK(ACCOUNT_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
rs = []
indexdict = {
'贷款户(已认).xls': ['cust', 1, 12, 6, -1, -1],
'核销贷款认定.xls': ['cust', 1, 36, 0, -1, -1],
'对公.xls': ['cust', 2, 16, 0, 19, 18],
'对私存款.xls': ['cust', 2, 16, 0, -1, -1],
'信用卡.xls': ['account', 0, 14, 11, -1, -1, 2],
'企业网银.xls': ['cust', 2, 25, 24, -1, -1],
'ETC松门.xls': ['cust', 3, 12, 11, -1, -1],
}
if indexdict[filename][0] == 'cust':
i_sql = cust_sql
elif indexdict[filename][0] == 'account':
i_sql = account_sql
custdict = util.get_cust_info()
for r in range(1, nrows):
row = sheet.row_values(r)
if row[indexdict[filename][2]] == '' or row[indexdict[filename]
[3]] == '':
print row
continue
MANAGER_NO = str(int(sheet.cell(
r, indexdict[filename][2]).value)).strip()
ORG_NO = str(int(sheet.cell(r, indexdict[filename][3]).value)).strip()
if len(MANAGER_NO) != 7 or MANAGER_NO[0:3] != '966' or len(
ORG_NO) != 6 or ORG_NO[0:3] != '966':
print row
continue
if indexdict[filename][0] == 'cust':
CUST_IN_NO = str(int(sheet.cell(r, indexdict[filename][1]).value))
if subtype == 'ETC':
CUST_IN_NO = str(
util.get_cust_in_no_by_creditcard_no(
str(sheet.cell(r, indexdict[filename][1]).value)))
#print CUST_IN_NO
elif indexdict[filename][0] == 'account':
CUST_IN_NO = str(int(sheet.cell(r, indexdict[filename][-1]).value))
ACCOUNT_NO = str(int(sheet.cell(r, indexdict[filename][1]).value))
if indexdict[filename][4] >= 0:
PERCENTAGE = int(
float(sheet.cell(r, indexdict[filename][4]).value) * 100)
else:
PERCENTAGE = 100
if indexdict[filename][5] >= 0:
if sheet.cell(r, indexdict[filename][5]).value == u'是':
HOOK_TYPE = '管户'
else:
HOOK_TYPE = '分润'
else:
HOOK_TYPE = '管户'
START_DATE = int(time.strftime("20%y%m%d"))
END_DATE = 20991231
STATUS = '正常'
ETL_DATE = 20160630
SRC = '存量导入'
TYP = hooktype
SUB_TYP = subtype
info = custdict.get(CUST_IN_NO, ('0', '核心地址为:暂无', '信贷地址为:暂无'))
CUST_NO = info[0]
NOTE = str(info[1]) + str(info[2])
if indexdict[filename][0] == 'cust':
rs.append((CUST_NO, CUST_IN_NO, MANAGER_NO, ORG_NO, PERCENTAGE,
HOOK_TYPE, START_DATE, END_DATE, STATUS, ETL_DATE, SRC,
TYP, SUB_TYP, NOTE))
elif indexdict[filename][0] == 'account':
rs.append((ACCOUNT_NO, MANAGER_NO, ORG_NO, PERCENTAGE, HOOK_TYPE,
START_DATE, END_DATE, STATUS, ETL_DATE, SRC, TYP,
SUB_TYP, NOTE))
#print rs[-1]
insert_to_hook(i_sql, rs)
print len(rs)
def run_import(filename, org_no=None, subtype=None):
#if subtype==None:subtype=hooktype
data = xlrd.open_workbook(filename)
filename = '第三方存管'
print filename
sheet = data.sheet_by_index(0)
nrows = sheet.nrows
cust_sql = """
INSERT INTO YDW.CUST_HOOK(CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
del_cust_sql = """
DELETE FROM CUST_HOOK WHERE TYP='第三方存管' AND CUST_IN_NO= ? AND ORG_NO = ?
"""
u_sql = """
update F_CONTRACT_STATUS f set ORG_ID=(select ID from D_ORG where ORG0_CODE= ?)
where f.CONTRACT_ID in (select c.ID from D_CUST_CONTRACT c where c.BUSI_TYPE= ? and c.NET_CST_NO= ?) and f.date_id=20160630
"""
rc = []
ru = []
rd = []
indexdict = {
'第三方存管': ['cust', 0, 17, 16, 0, -1],
}
custdict = util.get_cust_info()
staffdict = util.get_staff_branch()
for r in range(1, nrows):
row = sheet.row_values(r)
CUST_IN_NO = str(row[indexdict[filename][1]]).strip()
MANAGER_NO = row[indexdict[filename][2]]
ORG_NO = str(row[indexdict[filename][3]]).strip()
#print CUST_IN_NO,MANAGER_NO,ORG_NO
if MANAGER_NO == '' or ORG_NO == '':
#print row,'1'
continue
if CUST_IN_NO[-2:] == '.0':
CUST_IN_NO = CUST_IN_NO[:-2]
CUST_IN_NO = str(int(CUST_IN_NO))
MANAGER_NO = str(int(MANAGER_NO)).strip()
ORG_NO = str(int(float(ORG_NO))).strip()
#print MANAGER_NO,ORG_NO
if MANAGER_NO[0:3] != '966' or len(
ORG_NO) != 6 or ORG_NO[0:3] != '966' or len(MANAGER_NO) != 7:
print row, '2'
continue
staffinfo = staffdict.get(MANAGER_NO)
if staffinfo is None:
print MANAGER_NO, '!!!!'
continue
if staffinfo[1] == '非客户经理' and staffinfo[0][:-1] != ORG_NO[:-1]:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
if staffinfo[1] == '客户经理' and staffinfo[0] != ORG_NO:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
#if indexdict[filename][0]=='cust':
# CUST_IN_NO=str(int(sheet.cell(r,indexdict[filename][1]).value))
if subtype == 'ETC':
CUST_IN_NO = str(
util.get_cust_in_no_by_creditcard_no(
str(row[indexdict[filename][1]])))
if CUST_IN_NO == '0': print row, '3'
# #print CUST_IN_NO
#elif indexdict[filename][0]=='account':
# CUST_IN_NO=str(int(sheet.cell(r,indexdict[filename][-1]).value))
# ACCOUNT_NO=str(int(sheet.cell(r,indexdict[filename][1]).value))
HOOK_TYPE = '管户'
PERCENTAGE = 100
START_DATE = 20160630
END_DATE = 20991231
STATUS = '正常'
ETL_DATE = 20160630
SRC = '存量导入'
TYP = '第三方存管'
SUB_TYP = '第三方存管'
info = custdict.get(CUST_IN_NO, ('0', '核心地址为:暂无', '信贷地址为:暂无'))
CUST_NO = info[0]
if CUST_NO == '0':
CUST_NO = str('101' + row[5].strip())
print CUST_IN_NO, '4'
if info[0] == '0':
NOTE = '地址暂无'
if info[2] is None:
NOTE = '核心地址为:' + str(info[1])
else:
NOTE = '信贷地址为:' + str(info[2])
rd.append((CUST_IN_NO, ORG_NO))
#netkey=row[indexdict[filename][4]]
#if type(netkey)==type(1.0):
# netkey=str(int(netkey))
#else:
# netkey=netkey.strip()
#if subtype=='ETC':
# netkey=netkey[1:]
#ru.append((ORG_NO,subtype,str(netkey.encode('utf-8'))))
if (CUST_NO, CUST_IN_NO, MANAGER_NO, ORG_NO, PERCENTAGE, HOOK_TYPE,
START_DATE, END_DATE, STATUS, ETL_DATE, SRC, TYP, SUB_TYP,
NOTE) not in rc:
rc.append((CUST_NO, CUST_IN_NO, MANAGER_NO, ORG_NO, PERCENTAGE,
HOOK_TYPE, START_DATE, END_DATE, STATUS, ETL_DATE, SRC,
TYP, SUB_TYP, NOTE))
print len(rd), str(datetime.datetime.now())
insert_to_hook(del_cust_sql, rd)
print len(rc), str(datetime.datetime.now())
insert_to_hook(cust_sql, rc)
def run_import(filename,org_no=None,subtype=None):
#if subtype==None:subtype=hooktype
data = xlrd.open_workbook(filename)
if '对公' in filename:
filename='对公'
elif '对私' in filename:
filename='对私'
elif '箬横' in filename:
filename='对私'
else:
return
print filename
sheet = data.sheet_by_index(0)
nrows = sheet.nrows
cust_sql="""
INSERT INTO YDW.CUST_HOOK(CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
cust_hishook=util.get_custhook('存款')
account_sql="""
INSERT INTO YDW.ACCOUNT_HOOK(ACCOUNT_NO,CARD_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE,FOLLOW_CUST,CUST_IN_NO)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
del_cust_sql="""
delete from CUST_HOOK where TYP = '存款' and ORG_NO= ? and CUST_IN_NO=?
"""
del_account_sql="""
delete from ACCOUNT_HOOK where ACCOUNT_NO=?
"""
account_hishook=util.get_accounthook('存款')
print len(cust_hishook),len(account_hishook)
rc=[]
ra=[]
rcd=[]
rad=[]
indexdict={
'对公':['cust',2,16,0,19,18],
'对私':['cust',2,16,0,-1,-1],
}
if indexdict[filename][0]=='cust':
i_sql=cust_sql
elif indexdict[filename][0]=='account':
i_sql=account_sql
custdict=util.get_cust_info()
#accountdict=util.get_dep_account(20160630,org_no)
#if accountdict.get('81000000000'):accountdict.pop('81000000000')
accountdictlist={}
staffdict=util.get_staff_branch()
for r in range(1,nrows):
#print r
row = sheet.row_values(r)
CUST_IN_NO=str(row[indexdict[filename][1]]).strip()
MANAGER_NO= row[indexdict[filename][2]]
ORG_NO = row[indexdict[filename][3]]
if MANAGER_NO=='' or ORG_NO=='':
#print row
continue
if str(MANAGER_NO)[0]!='9':
MANAGER_NO=str(MANAGER_NO).strip()
else:
MANAGER_NO=str(int(MANAGER_NO)).strip()
CUST_IN_NO=str(int(float(CUST_IN_NO))).strip()
ORG_NO =str(int(ORG_NO )).strip()
#print CUST_IN_NO,MANAGER_NO,ORG_NO
if len(ORG_NO)!=6 or ORG_NO[0:3]!='966' or len(MANAGER_NO)!=7:
print row
continue
staffinfo=staffdict.get(MANAGER_NO)
if staffinfo[1]=='非客户经理' and staffinfo[0][:-1]!=ORG_NO[:-1]:
print ORG_NO,staffdict.get(MANAGER_NO)
print '!!!!!',row
continue
if staffinfo[1]=='客户经理' and staffinfo[0]!=ORG_NO:
print ORG_NO,staffdict.get(MANAGER_NO)
print '!!!!!',row
continue
HOOK_TYPE='管户'
PERCENTAGE=100
if filename=='对公':
if row[indexdict[filename][5]]==u'是':
HOOK_TYPE='管户'
else:
HOOK_TYPE='分润'
PERCENTAGE=int(float(row[indexdict[filename][4]]))
START_DATE=20150101
END_DATE=20991231
STATUS='正常'
ETL_DATE=20160630
SRC='存量导入'
TYP='存款'
SUB_TYP='存款'
info=custdict.get(CUST_IN_NO,('0','核心地址为:暂无','信贷地址为:暂无'))
CUST_NO=info[0]
if CUST_NO==0:CUST_NO=str(row[2].strip())
if info[0]=='0':
NOTE='地址暂无'
if info[2] is None:
NOTE='核心地址为:'+str(info[1])
else:
NOTE='信贷地址为:'+str(info[2])
rcd.append((ORG_NO,CUST_IN_NO))
rc.append((CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE))
if ORG_NO not in accountdictlist:
print ORG_NO
accountdictlist[ORG_NO]=util.get_dep_account(20160630,ORG_NO)
if accountdictlist[ORG_NO].get('81000000000'):
accountdictlist[ORG_NO].pop('81000000000')
accountlist=accountdictlist[ORG_NO].get(CUST_IN_NO,[])
for i in accountlist:
ACCOUNT_NO = i[0]
CARD_NO = i[1]
if CARD_NO is None:CARD_NO=ACCOUNT_NO
SUB_TYP = i[2]
FOLLOW_CUST='客户号优先'
rad.append(ACCOUNT_NO)
ra.append((ACCOUNT_NO,CARD_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE,FOLLOW_CUST,CUST_IN_NO))
#print rs[-1]
insert_to_hook(del_cust_sql,rcd)
insert_to_hook(del_account_sql,rad)
insert_to_hook(cust_sql,rc)
insert_to_hook(account_sql,ra)
print len(rcd),len(rad),len(rc),len(ra)
def run_import(filename, org_no=None, subtype=None):
#if subtype==None:subtype=hooktype
data = xlrd.open_workbook(filename)
filename = '账号'
print filename
sheet = data.sheet_by_index(0)
nrows = sheet.nrows
cust_sql = """
INSERT INTO YDW.CUST_HOOK(CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
account_sql = """
INSERT INTO YDW.ACCOUNT_HOOK(ACCOUNT_NO,CARD_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE,FOLLOW_CUST,CUST_IN_NO)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
del_account_sql = """
delete from ACCOUNT_HOOK where ACCOUNT_NO=?
"""
account_hishook = util.get_accounthook('存款')
print len(account_hishook)
rc = []
ra = []
rd = []
indexdict = {
'账号': ['account', 1, 3, 0, 5, -1],
}
custdict = util.get_cust_info()
staffdict = util.get_staff_branch()
carddict, accountdict = util.get_cust_no_by_card_account_no()
for r in range(1, nrows):
#print r
row = sheet.row_values(r)
ACCOUNT_NO = str(row[indexdict[filename][1]]).strip()
MANAGER_NO = row[indexdict[filename][2]]
ORG_NO = row[indexdict[filename][3]]
#print CUST_IN_NO,MANAGER_NO,ORG_NO
if MANAGER_NO == '' or ORG_NO == '':
print row
continue
ACCOUNT_NO = str(int(ACCOUNT_NO)).strip()
MANAGER_NO = str(int(MANAGER_NO)).strip()
ORG_NO = str(int(ORG_NO)).strip()
if MANAGER_NO[0:3] != '966' or len(
ORG_NO) != 6 or ORG_NO[0:3] != '966':
print MANAGER_NO, ORG_NO, '~~~~~~~~'
continue
staffinfo = staffdict.get(MANAGER_NO)
if staffinfo is None:
print MANAGER_NO, '!!!!!!'
continue
if staffinfo[1] == '非客户经理' and staffinfo[0][:-1] != ORG_NO[:-1]:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
if staffinfo[1] == '客户经理' and staffinfo[0] != ORG_NO:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
HOOK_TYPE = '管户'
PERCENTAGE = int(float(row[indexdict[filename][4]]))
START_DATE = 20150101
END_DATE = 20991231
STATUS = '正常'
ETL_DATE = 20160630
SRC = '存量导入'
TYP = '存款'
SUB_TYP = '存款'
NOTE = '核心地址为:暂无' + '信贷地址为:暂无'
FOLLOW_CUST = '账号优先'
if ACCOUNT_NO in accountdict:
CARD_NO = accountdict.get(ACCOUNT_NO)[0]
CUST_IN_NO = accountdict.get(ACCOUNT_NO)[1]
if ACCOUNT_NO in carddict:
CARD_NO = ACCOUNT_NO
ACCOUNT_NO = carddict.get(CARD_NO)[0]
CUST_IN_NO = carddict.get(CARD_NO)[1]
info = custdict.get(CUST_IN_NO, ('0', '核心地址为:暂无', '信贷地址为:暂无'))
if info[0] == '0':
NOTE = '地址暂无'
if info[2] is None:
NOTE = '核心地址为:' + str(info[1])
else:
NOTE = '信贷地址为:' + str(info[2])
rd.append(ACCOUNT_NO)
ra.append((ACCOUNT_NO, CARD_NO, MANAGER_NO, ORG_NO, PERCENTAGE,
HOOK_TYPE, START_DATE, END_DATE, STATUS, ETL_DATE, SRC, TYP,
SUB_TYP, NOTE, FOLLOW_CUST, CUST_IN_NO))
insert_to_hook(del_account_sql, rd)
insert_to_hook(account_sql, ra)
print len(rd), len(ra)
def run_import(filename, org_no=None, subtype=None):
#if subtype==None:subtype=hooktype
data = xlrd.open_workbook(filename)
if '核销' in filename:
filename = '核销'
else:
filename = '贷款'
print filename
sheet = data.sheet_by_index(0)
nrows = sheet.nrows
cust_sql = """
INSERT INTO YDW.CUST_HOOK(CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
account_sql = """
INSERT INTO YDW.ACCOUNT_HOOK(ACCOUNT_NO,CARD_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE,FOLLOW_CUST,CUST_IN_NO)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
u_sql = """
update F_BALANCE set ORG_ID=(select ID from D_ORG where ORG0_CODE= ? ) where ACCOUNT_ID=(select ID from D_LOAN_ACCOUNT where ACCOUNT_NO= ? ) and DATE_ID=20160630
"""
del_cust_sql = """
delete from CUST_HOOK where TYP in ('贷款','存款') and ORG_NO= ? and CUST_IN_NO=?
"""
del_account_sql = """
delete from ACCOUNT_HOOK where ACCOUNT_NO=?
"""
rc = []
ra = []
ru = []
rcd = []
rad = []
indexdict = {
'贷款': ['cust', 1, 12, 6, -1, -1, 40],
'核销': ['cust', 1, 38, 37, 5, -1, 0],
}
custdict = util.get_cust_info()
accountdictlist = {}
staffdict = util.get_staff_branch()
for r in range(1, nrows):
row = sheet.row_values(r)
CUST_IN_NO = str(row[indexdict[filename][1]])
MANAGER_NO = row[indexdict[filename][2]]
ORG_NO = row[indexdict[filename][3]]
if MANAGER_NO == '' or ORG_NO == '':
print row
continue
CUST_IN_NO = str(int(float(CUST_IN_NO)))
MANAGER_NO = str(int(MANAGER_NO)).strip()
ORG_NO = str(int(ORG_NO)).strip()
if MANAGER_NO[0:3] != '966' or len(
ORG_NO) != 6 or ORG_NO[0:3] != '966' or len(MANAGER_NO) != 7:
print row
continue
#print CUST_IN_NO,MANAGER_NO,ORG_NO
staffinfo = staffdict.get(MANAGER_NO)
if staffinfo[1] == '非客户经理' and staffinfo[0][:-1] != ORG_NO[:-1]:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
if staffinfo[1] == '客户经理' and staffinfo[0] != ORG_NO:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
PERCENTAGE = 100
HOOK_TYPE = '管户'
START_DATE = 20150101
END_DATE = 20991231
STATUS = '正常'
ETL_DATE = 20160630
SRC = '存量导入'
TYP = '贷款'
SUB_TYP = '贷款'
info = custdict.get(CUST_IN_NO, ('0', '核心地址为:暂无', '信贷地址为:暂无'))
CUST_NO = info[0]
if CUST_NO == '0': CUST_NO = str(row[2].strip())
if info[0] == '0':
NOTE = '地址暂无'
if info[2] is None:
NOTE = '核心地址为:' + str(info[1])
else:
NOTE = '信贷地址为:' + str(info[2])
if ORG_NO not in accountdictlist:
print ORG_NO
accountdictlist[ORG_NO] = util.get_dep_account(20160630, ORG_NO)
if accountdictlist[ORG_NO].get('81000000000'):
accountdictlist[ORG_NO].pop('81000000000')
accountlist = accountdictlist[ORG_NO].get(CUST_IN_NO, [])
rcd.append((ORG_NO, CUST_IN_NO))
if filename == '核销':
#print len(row),indexdict[filename][-1]
ORG_NO1 = str(int(row[indexdict[filename][-1]])).strip()
if ORG_NO != ORG_NO1:
print ORG_NO1, ORG_NO, str(row[indexdict[filename][4]]), r
#ORG_NO=ORG_NO1
ru.append((ORG_NO, str(row[indexdict[filename][4]]).strip()))
if ORG_NO not in accountdictlist:
print ORG_NO
accountdictlist[ORG_NO] = util.get_dep_account(
20160630, ORG_NO)
if accountdictlist[ORG_NO].get('81000000000'):
accountdictlist[ORG_NO].pop('81000000000')
accountlist = accountdictlist[ORG_NO].get(CUST_IN_NO, [])
rc.append(
(CUST_NO, CUST_IN_NO, MANAGER_NO, ORG_NO, PERCENTAGE, HOOK_TYPE,
START_DATE, END_DATE, STATUS, ETL_DATE, SRC, TYP, SUB_TYP, NOTE))
TYP = SUB_TYP = '存款'
rc.append(
(CUST_NO, CUST_IN_NO, MANAGER_NO, ORG_NO, PERCENTAGE, HOOK_TYPE,
START_DATE, END_DATE, STATUS, ETL_DATE, SRC, TYP, SUB_TYP, NOTE))
for i in accountlist:
ACCOUNT_NO = i[0]
CARD_NO = i[1]
if CARD_NO is None: CARD_NO = ACCOUNT_NO
SUB_TYP = i[2]
FOLLOW_CUST = '客户号优先'
rad.append(ACCOUNT_NO)
ra.append((ACCOUNT_NO, CARD_NO, MANAGER_NO, ORG_NO, PERCENTAGE,
HOOK_TYPE, START_DATE, END_DATE, STATUS, ETL_DATE, SRC,
TYP, SUB_TYP, NOTE, FOLLOW_CUST, CUST_IN_NO))
#print rs[-1]
insert_to_hook(del_cust_sql, rcd)
insert_to_hook(del_account_sql, rad)
insert_to_hook(u_sql, ru)
insert_to_hook(cust_sql, rc)
insert_to_hook(account_sql, ra)
print len(rcd), len(rad), len(rc), len(ra), len(ru)
def run_import(filename, org_no=None, subtype=None):
#if subtype==None:subtype=hooktype
data = xlrd.open_workbook(filename)
if '委托银商' in filename:
filename = '委托银商'
sheet = data.sheet_by_index(0)
elif '助农' in filename:
filename = '助农'
sheet = data.sheet_by_index(0)
elif '自签传统' in filename:
filename = '自签传统'
sheet = data.sheet_by_index(0)
elif '信付通' in filename:
filename = '信付通'
sheet = data.sheet_by_index(0)
elif '行内' in filename:
filename = '行内'
sheet = data.sheet_by_index(0)
print filename
cust_sql = """
INSERT INTO YDW.CUST_HOOK(CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
del_cust_sql = """
DELETE FROM YDW.CUST_HOOK WHERE ID = ?
"""
cust_hishook = util.get_custhook('POS')
print len(cust_hishook)
rc = []
ra = []
indexdict = {
'委托银商': ['cust', 1, 11, 10, 3, 2],
'自签传统': ['cust', 1, 11, 10, 3, 2],
'助农': ['cust', 1, 11, 10, 3, 2],
'行内': ['cust', 1, 11, 10, 3, 2],
'信付通': ['cust', 1, 11, 10, 3, 2],
}
custdict = util.get_cust_info()
#accountdict=util.get_dep_account(20160630,org_no)
#if accountdict.get('81000000000'):accountdict.pop('81000000000')
accountdictlist = {}
staffdict = util.get_staff_branch()
nrows = sheet.nrows
for r in range(1, nrows):
#print r
row = sheet.row_values(r)
CUST_IN_NO = row[indexdict[filename][1]]
MANAGER_NO = str(row[indexdict[filename][2]]).strip()
ORG_NO = str(row[indexdict[filename][3]]).strip()
#print CUST_IN_NO,MANAGER_NO,ORG_NO
if MANAGER_NO == '' or ORG_NO == '':
#print row
continue
CUST_IN_NO = str(int(float(CUST_IN_NO))).strip()
MANAGER_NO = str(int(float(MANAGER_NO))).strip()
ORG_NO = str(int(float(ORG_NO))).strip()
if MANAGER_NO[0:3] != '966' or len(
ORG_NO) != 6 or ORG_NO[0:3] != '966' or len(MANAGER_NO) != 7:
print MANAGER_NO, ORG_NO, '~~~~~~~~~~~~~'
continue
staffinfo = staffdict.get(MANAGER_NO, None)
if staffinfo is None:
print MANAGER_NO, '!!!!!!!!!!!!!!'
continue
if staffinfo[1] == '非客户经理' and staffinfo[0][:-1] != ORG_NO[:-1]:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
if staffinfo[1] == '客户经理' and staffinfo[0] != ORG_NO:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
CUST_NO = CUST_IN_NO
CUST_IN_NO = str(row[indexdict[filename][-1]]).strip()
#print CUST_IN_NO
chh = cust_hishook.get(CUST_IN_NO + ORG_NO)
if chh:
delete_hook(del_cust_sql,
int(cust_hishook.get(CUST_IN_NO + ORG_NO)[-1]))
HOOK_TYPE = '管户'
START_DATE = 20150101
END_DATE = 20991231
STATUS = '正常'
ETL_DATE = 20160630
SRC = '存量导入'
TYP = 'POS'
SUB_TYP = filename
NOTE = str(row[indexdict[filename][4]].encode('UTF-8'))
PERCENTAGE = 100
rc.append(
(CUST_NO, CUST_IN_NO, MANAGER_NO, ORG_NO, PERCENTAGE, HOOK_TYPE,
START_DATE, END_DATE, STATUS, ETL_DATE, SRC, TYP, SUB_TYP, NOTE))
insert_to_hook(cust_sql, rc)
print len(rc)
def run_import(filename, org_no=None, subtype=None):
#if subtype==None:subtype=hooktype
data = xlrd.open_workbook(filename)
filename = '电子银行'
print filename
sheet = data.sheet_by_index(0)
nrows = sheet.nrows
cust_sql = """
INSERT INTO YDW.CUST_HOOK(CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
del_cust_sql = """
DELETE FROM CUST_HOOK WHERE TYP='电子银行' AND CUST_IN_NO= ? AND ORG_NO = ?
"""
u_sql = """
update F_CONTRACT_STATUS f set ORG_ID=(select ID from D_ORG where ORG0_CODE= ?)
where f.CONTRACT_ID in (select c.ID from D_CUST_CONTRACT c where c.BUSI_TYPE= ? and c.NET_CST_NO= ?) and f.date_id=20160630
"""
cust_hishook = util.get_custhook('电子银行')
print len(cust_hishook)
rc = []
#ru=[]
rd = []
indexdict = {
'电子银行': ['cust', 0, 16, 15, 4, 18],
}
custdict = util.get_cust_info()
accountdictlist = {}
staffdict = util.get_staff_branch()
for r in range(1, nrows):
#print r
row = sheet.row_values(r)
CUST_IN_NO = str(row[indexdict[filename][1]]).strip()
MANAGER_NO = row[indexdict[filename][2]]
if type(MANAGER_NO) == type('d'): MANAGER_NO = MANAGER_NO.strip()
#print MANAGER_NO
ORG_NO = str(row[indexdict[filename][3]]).strip()
#print CUST_IN_NO,MANAGER_NO,ORG_NO
if MANAGER_NO == '' or ORG_NO == '' or MANAGER_NO == u'无':
#print row
continue
MANAGER_NO = str(int(row[indexdict[filename][2]])).strip()
CUST_IN_NO = str(int(float(CUST_IN_NO))).strip()
ORG_NO = str(int(float(ORG_NO))).strip()
if MANAGER_NO[0:3] != '966' or len(
ORG_NO) != 6 or ORG_NO[0:3] != '966' or len(
MANAGER_NO) != 7 or MANAGER_NO == '无' or ORG_NO == '无':
print row
continue
staffinfo = staffdict.get(MANAGER_NO, None)
if staffinfo is None:
print MANAGER_NO, '!!!!!!!!!!!!!!!!!!!!!'
continue
if staffinfo[1] == '非客户经理' and staffinfo[0][:-1] != ORG_NO[:-1]:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
if staffinfo[1] == '客户经理' and staffinfo[0] != ORG_NO:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
chh = cust_hishook.get(CUST_IN_NO + ORG_NO)
rd.append((CUST_IN_NO, ORG_NO))
HOOK_TYPE = '管户'
PERCENTAGE = 100
START_DATE = 20150101
END_DATE = 20991231
STATUS = '正常'
ETL_DATE = 20160630
SRC = '存量导入'
TYP = '电子银行'
SUB_TYP = '电子银行'
info = custdict.get(CUST_IN_NO, ('0', '核心地址为:暂无', '信贷地址为:暂无'))
CUST_NO = info[0]
if CUST_NO == 0: CUST_NO = str(row[2].strip())
if info[0] == '0':
NOTE = '地址暂无'
if info[2] is None:
NOTE = '核心地址为:' + str(info[1])
else:
NOTE = '信贷地址为:' + str(info[2])
accountlist = row[6:13]
for i in accountlist:
i = str(i.encode('UTF-8'))
if i != '无':
SUB_TYP = i
rc.append((CUST_NO, CUST_IN_NO, MANAGER_NO, ORG_NO, PERCENTAGE,
HOOK_TYPE, START_DATE, END_DATE, STATUS, ETL_DATE,
SRC, TYP, SUB_TYP, NOTE))
#if i in ('企业网上银行','ETC'):
# ru.append((ORG_NO,subtype,CUST_IN_NO))
print len(rd), str(datetime.datetime.now())
insert_to_hook(del_cust_sql, rd)
print len(rc), str(datetime.datetime.now())
insert_to_hook(cust_sql, rc)
def run_import(filename, org_no=None, subtype=None):
#if subtype==None:subtype=hooktype
if '流水' in filename: return
data = xlrd.open_workbook(filename)
#if '对公' in filename:
# filename='对公'
#else:
# filename='对私'
filename = '理财'
print filename
sheet = data.sheet_by_index(0)
nrows = sheet.nrows
cust_sql = """
INSERT INTO YDW.CUST_HOOK(CUST_NO,CUST_IN_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
del_cust_sql = """
delete from CUST_HOOK where TYP='理财' and ORG_NO= ? and CUST_IN_NO=?
"""
cust_hishook = util.get_custhook('理财')
account_sql = """
INSERT INTO YDW.ACCOUNT_HOOK(ACCOUNT_NO,CARD_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE,FOLLOW_CUST,CUST_IN_NO,BALANCE)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
del_account_sql = """
DELETE FROM YDW.ACCOUNT_HOOK WHERE ACCOUNT_NO = ?
"""
account_hishook = util.get_accounthook('理财')
print len(cust_hishook), len(account_hishook)
rc = []
ra = []
rcd = []
rad = []
indexdict = {
'理财': ['cust', 0, 13, 12, 4, 14],
}
if indexdict[filename][0] == 'cust':
i_sql = cust_sql
elif indexdict[filename][0] == 'account':
i_sql = account_sql
custdict = util.get_cust_info()
staffdict = util.get_staff_branch()
#accountdict=util.get_fin_account(20160630,org_no)
#if accountdict.get('81000000000'):accountdict.pop('81000000000')
accountdictlist = {}
for r in range(1, nrows):
#print r
row = sheet.row_values(r)
CUST_IN_NO = str(row[indexdict[filename][1]]).strip()
MANAGER_NO = row[indexdict[filename][2]]
if type(MANAGER_NO) == type('d'): MANAGER_NO = MANAGER_NO.strip()
ORG_NO = str(row[indexdict[filename][3]]).strip()
BALANCE = 0 #=str(row[indexdict[filename][4]])[:-2]
#print CUST_IN_NO,MANAGER_NO,ORG_NO
if MANAGER_NO == '' or ORG_NO == '' or MANAGER_NO == u'无':
print row
continue
CUST_IN_NO = str(int(float(CUST_IN_NO))).strip()
MANAGER_NO = str(int(float(MANAGER_NO))).strip()
ORG_NO = str(int(ORG_NO)).strip()
if MANAGER_NO[0:3] != '966' or len(
ORG_NO) != 6 or ORG_NO[0:3] != '966':
print row, MANAGER_NO, ORG_NO
continue
if len(MANAGER_NO) == 6:
print row, '~~~~~~~~~~~'
continue
staffinfo = staffdict.get(MANAGER_NO)
if staffinfo is None:
print MANAGER_NO, '!!!!'
continue
if staffinfo[1] == '非客户经理' and staffinfo[0][:-1] != ORG_NO[:-1]:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
if staffinfo[1] == '客户经理' and staffinfo[0] != ORG_NO:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
rcd.append((ORG_NO, CUST_IN_NO))
HOOK_TYPE = '管户'
PERCENTAGE = int(float(row[indexdict[filename][5]]))
START_DATE = 20150101
END_DATE = 20991231
STATUS = '正常'
ETL_DATE = 20160630
SRC = '存量导入'
TYP = '理财'
SUB_TYP = '理财'
info = custdict.get(CUST_IN_NO, ('0', '核心地址为:暂无', '信贷地址为:暂无'))
CUST_NO = info[0]
if CUST_NO == 0: CUST_NO = str(row[2].strip())
if info[0] == '0':
NOTE = '地址暂无'
if info[2] is None:
NOTE = '核心地址为:' + str(info[1])
else:
NOTE = '信贷地址为:' + str(info[2])
rc.append(
(CUST_NO, CUST_IN_NO, MANAGER_NO, ORG_NO, PERCENTAGE, HOOK_TYPE,
START_DATE, END_DATE, STATUS, ETL_DATE, SRC, TYP, SUB_TYP, NOTE))
if ORG_NO not in accountdictlist:
print ORG_NO
accountdictlist[ORG_NO] = util.get_fin_account(20160630, ORG_NO)
if accountdictlist[ORG_NO].get('81000000000'):
accountdictlist[ORG_NO].pop('81000000000')
accountlist = accountdictlist[ORG_NO].get(CUST_IN_NO, [])
for i in accountlist:
ACCOUNT_NO = i
CARD_NO = ACCOUNT_NO
SUB_TYP = '理财'
FOLLOW_CUST = '客户号优先'
ahh = account_hishook.get(ACCOUNT_NO + ORG_NO)
rad.append(ACCOUNT_NO)
ra.append((ACCOUNT_NO, CARD_NO, MANAGER_NO, ORG_NO, PERCENTAGE,
HOOK_TYPE, START_DATE, END_DATE, STATUS, ETL_DATE, SRC,
TYP, SUB_TYP, NOTE, FOLLOW_CUST, CUST_IN_NO, BALANCE))
#print rs[-1]
insert_to_hook(del_cust_sql, rcd)
insert_to_hook(del_account_sql, rad)
insert_to_hook(cust_sql, rc)
insert_to_hook(account_sql, ra)
print len(rcd), len(rad), len(rc), len(ra)
def run_import(filename, org_no=None, subtype=None):
#if subtype==None:subtype=hooktype
data = xlrd.open_workbook(filename)
filename = '信用卡'
print filename
sheet = data.sheet_by_index(0)
nrows = sheet.nrows
account_sql = """
INSERT INTO YDW.ACCOUNT_HOOK(ACCOUNT_NO,CARD_NO,MANAGER_NO,ORG_NO,PERCENTAGE,HOOK_TYPE,START_DATE,END_DATE,STATUS,ETL_DATE,SRC,TYP,SUB_TYP,NOTE,FOLLOW_CUST,CUST_IN_NO)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
del_account_sql = """
DELETE FROM YDW.ACCOUNT_HOOK WHERE ACCOUNT_NO = ?
"""
account_hishook = util.get_accounthook('信用卡')
print len(account_hishook)
ra = []
ru = []
rd = []
indexdict = {
'信用卡': ['account', 2, 14, 11, 0],
}
custdict = util.get_cust_info()
staffdict = util.get_staff_branch()
for r in range(1, nrows):
#print r
row = sheet.row_values(r)
CUST_IN_NO = str(row[indexdict[filename][1]]).strip()
MANAGER_NO = row[indexdict[filename][2]]
ORG_NO = str(int(row[indexdict[filename][3]]))
ACCOUNT_NO = str(row[indexdict[filename][4]]).strip()
#print CUST_IN_NO,MANAGER_NO,ORG_NO
if MANAGER_NO == '' or ORG_NO == '':
#print row
continue
CUST_IN_NO = str(int(float(CUST_IN_NO))).strip()
MANAGER_NO = str(int(MANAGER_NO)).strip()
ORG_NO = str(int(ORG_NO)).strip()
if MANAGER_NO[0:3] != '966' or len(
ORG_NO) != 6 or ORG_NO[0:3] != '966':
print MANAGER_NO, ORG_NO, '~~~~~~~'
continue
if len(MANAGER_NO) == 6:
print row
continue
staffinfo = staffdict.get(MANAGER_NO)
if staffinfo[1] == '非客户经理' and staffinfo[0][:-1] != ORG_NO[:-1]:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
if staffinfo[1] == '客户经理' and staffinfo[0] != ORG_NO:
print ORG_NO, staffdict.get(MANAGER_NO)
print '!!!!!', row
continue
rd.append(ACCOUNT_NO)
HOOK_TYPE = '管户'
PERCENTAGE = 100
START_DATE = 20150101
END_DATE = 20991231
STATUS = '正常'
ETL_DATE = 20160630
SRC = '存量导入'
TYP = '信用卡'
SUB_TYP = '信用卡'
info = custdict.get(CUST_IN_NO, ('0', '核心地址为:暂无', '信贷地址为:暂无'))
if info[0] == '0':
NOTE = '地址暂无'
if info[2] is None:
NOTE = '核心地址为:' + str(info[1])
else:
NOTE = '信贷地址为:' + str(info[2])
FOLLOW_CUST = '账号优先'
CARD_NO = ACCOUNT_NO
ra.append((ACCOUNT_NO, CARD_NO, MANAGER_NO, ORG_NO, PERCENTAGE,
HOOK_TYPE, START_DATE, END_DATE, STATUS, ETL_DATE, SRC, TYP,
SUB_TYP, NOTE, FOLLOW_CUST, CUST_IN_NO))
print len(rd), str(datetime.datetime.now())
insert_to_hook(del_account_sql, rd)
print len(ra), str(datetime.datetime.now())
insert_to_hook(account_sql, ra)