def update_dcust():
try:
db = util.DBConnect()
update(db)
db.conn.commit()
finally:
db.closeDB()
def query_hook():
try:
db = util.DBConnect()
q_sql = """
select distinct CUST_IN_NO,ORG_NO,MANAGER_NO from CUST_HOOK where TYP='存款' and HOOK_TYPE='管户'
"""
db.cursor.execute(q_sql)
row = db.cursor.fetchone()
rowdict = {}
while row:
rowdict[row[0] + row[1]] = [row[2], '存款']
row = db.cursor.fetchone()
q_sql = """
select distinct CUST_IN_NO,ORG_NO,MANAGER_NO from CUST_HOOK where TYP='贷款' and HOOK_TYPE='管户'
"""
db.cursor.execute(q_sql)
row = db.cursor.fetchone()
while row:
rowdict[row[0] + row[1]] = [row[2], '贷款']
row = db.cursor.fetchone()
q_sql = """
select distinct CUST_IN_NO,ORG_NO,MANAGER_NO from CUST_HOOK where TYP='电子银行' and HOOK_TYPE='管户'
"""
db.cursor.execute(q_sql)
row = db.cursor.fetchone()
while row:
rowdict[row[0] + row[1]] = [row[2], '电子银行']
row = db.cursor.fetchone()
print len(rowdict)
return rowdict
finally:
db.closeDB()
def update_org_id(insert_sql, now, account_no):
db = util.DBConnect()
try:
db.cursor.execute(insert_sql, now, account_no)
db.conn.commit()
finally:
db.closeDB()
def syn_hook_batch_id(etldate):
db = util.DBConnect()
try:
sql = """
SELECT C.ID, FROM_TELLER_NO
FROM YDW.CUST_HOOK_BATCH C
WHERE DEAL_STATUS != '已移交' AND DEAL_STATUS != '不同意' and hook_typ = '全部移交' order by ID DESC
"""
db.cursor.execute(sql)
row = db.cursor.fetchone()
movelist = {}
while row:
movelist[str(row[1])] = row[0]
row = db.cursor.fetchone()
for i in movelist:
print i, movelist[i]
au_sql = """
update ACCOUNT_HOOK set BATCH_ID = ? where MANAGER_NO= ? and ETL_DATE = ? and (BATCH_ID is null or BATCH_ID = 0 or BATCH_ID = -1)
"""
cu_sql = """
update CUST_HOOK set BATCH_ID = ? where MANAGER_NO= ? and ETL_DATE = ? and (BATCH_ID is null or BATCH_ID = 0 or BATCH_ID = -1)
"""
db.cursor.execute(au_sql, movelist[i], i, etldate)
db.cursor.execute(cu_sql, movelist[i], i, etldate)
db.conn.commit()
finally:
db.closeDB()
def test_init_mx20160511_manager_relation_full_one_by_one():
db = util.DBConnect()
sql = """
INSERT INTO MX20160511_MANAGER_RELATION_FULL(JIGOU, HUIJIHU, ACCOUNT, CUST_NO, MAN_ID,
ACCT_NAME, BIZHONG, RIJUN_2015, MONTH_RIJUN_201512, RIJUN_2016,
YM_BAL_201605, OPEN_DATE, XIAOHU, MANAGER_ID, FENRUN,
FLAG, ADDRESS1, ADDRESS2, TEL)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
fp = open(
'/home/plwu/tmp/files/mx20160511_manager_relation_full.csv.utf-8', 'r')
count = 0
dataList = []
while True:
line = fp.readline()
if not line:
break
line = line.replace('\"', '')
line = line.replace('\r', '')
line = line.replace('\n', '')
line.decode('utf-8').encode('gbk')
print line
value = line.split(',')
print value
db.cursor.execute(sql, value)
count = count + 1
print count
db.conn.commit()
def update_orgid(startdate,enddate):
info("update_orgid:%d-%d"%(startdate,enddate))
try :
db = util.DBConnect()
etldate=startdate
while int(etldate)<=int(enddate):
m4_sql="""
MERGE INTO YDW.F_CONTRACT_STATUS F
USING (SELECT O.ID DOID ,D.ID COID FROM D_ORG O JOIN D_CUST_CONTRACT D ON O.ORG0_CODE =D.OPEN_BRANCH_NO WHERE D.BUSI_TYPE<>'企业网上银行' and D.BUSI_TYPE<>'ETC') A
ON A.COID = F.CONTRACT_ID AND F.DATE_ID =?
WHEN MATCHED THEN UPDATE SET F.ORG_ID=A.DOID
"""
m6_sql="""
MERGE INTO YDW.F_CONTRACT_STATUS F
USING (SELECT O.ID DOID ,C.ID COID FROM D_CUST_CONTRACT C
JOIN EBANK_ORG D ON D.SUB_TYP in ('企业网上银行', 'ETC') AND C.BUSI_TYPE in ('企业网上银行', 'ETC') AND D.CUST_NET_NO=C.NET_CST_NO AND D.STATUS IN ('正常')
JOIN D_ORG O ON O.ORG0_CODE=D.ORG_NO) A
ON A.COID = F.CONTRACT_ID AND F.DATE_ID =?
WHEN MATCHED THEN UPDATE SET F.ORG_ID=A.DOID
"""
db.cursor.execute(m4_sql,etldate)
db.cursor.execute(m6_sql,etldate)
print etldate
etldate=int(util.daycalc(etldate,1))
db.conn.commit()
finally :
db.closeDB()
def etl_task_check(etldate, name):
try:
db = util.DBConnect()
sql = "select status from p_etl_task where date_id=? and task_name = ? "
db.cursor.execute(sql, int(etldate), name)
rows = db.cursor.fetchall()
if len(rows) > 1:
raise Exception("任务名不能重复")
if len(rows) == 0:
insertsql = "insert into p_etl_task(date_id,task_name,status,start_time) values (?,?,?,?)"
s = str(datetime.now())
db.cursor.execute(insertsql, int(etldate), name, "启动", s)
db.conn.commit()
process_info()
else:
status = rows[0][0]
process_info()
if status == "成功":
nn = "%s已完成，跳过该任务" % (name)
print nn
info(nn)
return False
else:
dsql = "delete from p_etl_task where date_id=? and task_name = ? "
db.cursor.execute(dsql, int(etldate), name)
s = str(datetime.now())
insertsql = "insert into p_etl_task(date_id,task_name,status,start_time) values (?,?,?,?)"
db.cursor.execute(insertsql, int(etldate), name, "启动", s)
db.conn.commit()
return True
finally:
db.closeDB()
def insert_to_hook(insert_sql, listdata):
try:
db = util.DBConnect()
db.cursor.executemany(insert_sql, listdata)
db.conn.commit()
finally:
db.closeDB()
def files2fact(self, ds):
try:
db = util.DBConnect()
custsql = ds.cust_sql()
rs = []
idx = 0
for row, flag in ds.to_fact_row():
if flag == False: continue
if row is None: break
r = ds.transfor_one_fact(row, self.table_desc)
if not r: continue
idx = idx + 1
rs.append(r)
if idx > 10000:
print "to_fact_row ,times=", datetime.now() - d1
info("insert into loan_hook 100:" +
str(datetime.now() - d0))
idx = 0
#print rs
db.cursor.executemany(custsql, rs)
db.conn.commit()
rs = []
if len(rs) > 0:
#print rs
db.cursor.executemany(custsql, rs)
db.conn.commit()
rs = []
finally:
db.closeDB()
def run(etldate):
try:
db = util.DBConnect()
sql = """truncate table BALANCE_MANAGE_ID IMMEDIATE"""
db.cursor.execute(sql)
db.conn.commit()
if os.path.exists(tfile): os.remove(tfile)
d1 = datetime.now()
dksql = """select a.account_no,f.cst_no,f.account_id,f.date_id,o.ORG0_CODE
from f_balance f
inner join D_LOAN_ACCOUNT a on f.account_id = a.id
inner join d_org o on f.ORG_ID = o.id
inner join cust_hook_his ch on ch.cust_in_no = a.cst_no and o.ORG0_CODE = ch.ORG_NO
where f.date_id = ? and f.ACCT_TYPE = '4'
"""
writer_manage_id(db, get_loan_acct_manage, dksql, etldate)
load_files()
print "load time =", datetime.now() - d1
"""
分割d_manage 测试放在这里，之后是放在etl每日任务的最后
"""
OUT_SQLCODE = 1
OUT_MSG = ''
msg = db.cursor.callproc("P_D_SALE_MANAGE_RELA",
[str(Config().etldate), OUT_SQLCODE, OUT_MSG])
if msg[1] != 0:
print "error msg:%s" % msg[2]
db.conn.commit()
finally:
db.closeDB()
def licai_bus(stardate, etldate):
try:
oneday = datetime.now()
db = util.DBConnect()
print "licai_bus:开始"
while stardate <= etldate:
sql0 = """
DELETE FROM FMS_TRANS_LOG WHERE DATE_ID=?
"""
db.cursor.execute(sql0, int(stardate))
db.conn.commit()
datas = fms_trans_log(stardate)
data = []
is_repeat = """
select * from FMS_TRANS_LOG where tran_date=? and APP_SNO=?
"""
for i in datas:
db.cursor.execute(is_repeat, [i[1], i[2]])
qrow = db.cursor.fetchone()
if qrow is None:
data.append(i)
sql = """
insert into FMS_TRANS_LOG (DATE_ID,TRAN_DATE,APP_SNO,TEL_TRAN_CODE,ACCEPTMETHOD,TRANS_STATUS,TRAN_BRANCH_CODE,TRAN_TELLER_CODE)
values(?,?,?,?,?,?,?,?)
"""
db.cursor.executemany(sql, data)
db.conn.commit()
print stardate, "完成", datetime.now() - oneday
stardate = int(util.daycalc(stardate, 1))
finally:
db.closeDB()
def man_dep(stardate, etldate):
try:
oneday = datetime.now()
db = util.DBConnect()
while stardate <= etldate:
sql = """
DELETE FROM F_BALANCE_TEMP
"""
db.cursor.execute(sql)
db.conn.commit()
sql1 = """
INSERT INTO YDW.F_BALANCE_TEMP(ACCOUNT_ID, ACCOUNT_TYPE_ID, MANAGE_ID, DATE_ID, ACCOUNT_STATUS_ID, ACCOUNT_PRICE_ID, BALANCE, OUT_BALANCE, CONTRACT_AMT, DR_AMOUNT, CR_AMOUNT, YEAR_PDT, RE_BALANCE, SUM_RE_INTEREST, LUPD_DATE_ID, IN_TO_OUT_AMOUNT, INT_BAL, ACCT_TYPE, CST_NO, INT_AMOUNT, NEXT_INT_DATE_ID, CLAC_INT, LAST_RECV_DATE, LAST_CINT_DATE, RELA_DEP_BAL, ORG_ID, CST_ID, ACCOUNT_TYPE2_ID)
SELECT ACCOUNT_ID, ACCOUNT_TYPE_ID, MAX(MANAGE_ID), DATE_ID, ACCOUNT_STATUS_ID, ACCOUNT_PRICE_ID, BALANCE, OUT_BALANCE, CONTRACT_AMT, DR_AMOUNT, CR_AMOUNT, YEAR_PDT, RE_BALANCE, SUM_RE_INTEREST, LUPD_DATE_ID, IN_TO_OUT_AMOUNT, INT_BAL, ACCT_TYPE, CST_NO, INT_AMOUNT, NEXT_INT_DATE_ID, CLAC_INT, LAST_RECV_DATE, LAST_CINT_DATE, RELA_DEP_BAL, MAX(ORG_ID), CST_ID, ACCOUNT_TYPE2_ID FROM F_BALANCE FF
WHERE FF.ACCOUNT_ID IN ( SELECT F.ACCOUNT_ID FROM F_BALANCE F WHERE F.DATE_ID = ? AND F.ACCT_TYPE = '1' AND F.BALANCE = 0 GROUP BY F.ACCOUNT_ID HAVING COUNT(*) > 1)
AND FF.DATE_ID = ? AND FF.ACCT_TYPE = '1' AND FF.BALANCE = 0 GROUP BY ACCOUNT_ID, ACCOUNT_TYPE_ID, DATE_ID, ACCOUNT_STATUS_ID, ACCOUNT_PRICE_ID, BALANCE, OUT_BALANCE, CONTRACT_AMT, DR_AMOUNT, CR_AMOUNT, YEAR_PDT, RE_BALANCE, SUM_RE_INTEREST, LUPD_DATE_ID, IN_TO_OUT_AMOUNT, INT_BAL, ACCT_TYPE, CST_NO, INT_AMOUNT, NEXT_INT_DATE_ID, CLAC_INT, LAST_RECV_DATE, LAST_CINT_DATE, RELA_DEP_BAL, CST_ID, ACCOUNT_TYPE2_ID
"""
db.cursor.execute(sql1, int(stardate), int(stardate))
sql2 = """
DELETE FROM F_BALANCE FF
WHERE FF.ACCOUNT_ID IN ( SELECT F.ACCOUNT_ID FROM F_BALANCE F WHERE F.DATE_ID = ? AND F.ACCT_TYPE = '1' AND F.BALANCE = 0 GROUP BY F.ACCOUNT_ID HAVING COUNT(*) > 1)
AND FF.DATE_ID = ? AND FF.ACCT_TYPE = '1' AND FF.BALANCE = 0
"""
db.conn.commit()
db.cursor.execute(sql2, int(stardate), int(stardate))
sql3 = """
INSERT INTO F_BALANCE(ACCOUNT_ID, ACCOUNT_TYPE_ID, MANAGE_ID, DATE_ID, ACCOUNT_STATUS_ID, ACCOUNT_PRICE_ID, BALANCE, OUT_BALANCE, CONTRACT_AMT, DR_AMOUNT, CR_AMOUNT, YEAR_PDT, RE_BALANCE, SUM_RE_INTEREST, LUPD_DATE_ID, IN_TO_OUT_AMOUNT, INT_BAL, ACCT_TYPE, CST_NO, INT_AMOUNT, NEXT_INT_DATE_ID, CLAC_INT, LAST_RECV_DATE, LAST_CINT_DATE, RELA_DEP_BAL, ORG_ID, CST_ID, ACCOUNT_TYPE2_ID)
SELECT * FROM F_BALANCE_TEMP
"""
db.cursor.execute(sql3)
db.conn.commit()
print stardate, "完成", datetime.now() - oneday
stardate = int(util.daycalc(stardate, 1))
finally:
db.closeDB()
def group_split():
pass
db = util.DBConnect()
try:
cursor = db.cursor
cursor.execute("select * from d_group")
row = cursor.fetchone()
datas = []
delsql = "delete from d_group_relation "
insersql = """
insert into d_group_relation( group_id,sale_code,sale_role,GROUP_TYPE,percent)
values ( ?,?,?,?,? )
"""
while row:
group_type = row[1]
group_key = row[3]
if group_key == "无":
data = [row[0], "无", "机构管理", group_type, row[2]]
datas.append(data)
else:
gks = group_key.split(";")
for gk in gks:
keys = gk.split("-")
data = [row[0], keys[0], keys[1], group_type, int(keys[2])]
datas.append(data)
row = cursor.fetchone()
cursor.execute(delsql)
cursor.execute(delsql)
cursor.executemany(insersql, datas)
db.conn.commit()
finally:
db.closeDB()
def update_org_id(insert_sql, listdata):
try:
db = util.DBConnect()
db.cursor.executemany(insert_sql, listdata)
db.conn.commit()
finally:
db.closeDB()
def input_pass(etldate):
db = util.DBConnect()
day = etldate % 100
temp_date = etldate
if day >= 1 and day <= 8: #取上月月初
temp_date = int(util.daycalc(etldate, -15))
date_sql = """ select MONTHBEG_ID from d_date where id = ? """
db.cursor.execute(date_sql, temp_date)
start_date = db.cursor.fetchone()
print start_date
try:
sql = """
UPDATE CUST_HOOK SET STATUS='正常', start_date = max(start_date, ?) WHERE STATUS='录入已审批'
"""
db.cursor.execute(sql, start_date[0])
sql = """
UPDATE ACCOUNT_HOOK SET STATUS='正常' , start_date = max(start_date, ?) WHERE STATUS='录入已审批'
"""
db.cursor.execute(sql, start_date[0])
sql = """
UPDATE PARENT_HOOK SET STATUS='正常' , start_date = max(start_date, ?) WHERE STATUS='录入已审批'
"""
db.cursor.execute(sql, start_date[0])
db.conn.commit()
finally:
db.closeDB()
def query_detail():
try:
db = util.DBConnect()
q_sql = """
select trim(c.CST_NO),i.CUST_LONG_NO,i.CUST_NAME,o.org0_code,i.CUST_ADDRESS,i.CUST_CREDIT_ADDRESS,c.BUSI_TYPE,c.NET_CST_NO,m.GROUP_KEY from F_CONTRACT_STATUS f
join D_CUST_CONTRACT c on f.CONTRACT_ID=c.ID and c.BUSI_TYPE in ('个人网上银行','企业网上银行','手机银行','支付宝卡通','丰收e支付','ETC','支付宝快捷支付') and left( CST_NO,1)='8'
left join D_CUST_INFO i on i.CUST_NO=c.CST_NO
join D_MANAGE m on m.ID=f.MANAGE_ID
join D_ORG o on o.id=f.org_id --and o.org1_code='966120'
where f.DATE_ID=20160630 and f.status<>'不确定'
order by c.CST_NO,c.OPEN_BRANCH_NO,m.GROUP_KEY
"""
db.cursor.execute(q_sql)
row = db.cursor.fetchall()
q1_sql = """
select trim(CST_NO),i.CUST_LONG_NO,i.CUST_NAME,case OPEN_BRANCH_NO when '966166' then '966163' else OPEN_BRANCH_NO end,i.CUST_ADDRESS,i.CUST_CREDIT_ADDRESS,BUSI_TYPE,NET_CST_NO,'无' from D_CUST_CONTRACT c
join D_CUST_INFO i on c.CST_NO=i.CUST_NO
where BUSI_TYPE in ('支付宝卡通','支付宝快捷支付') --and OPEN_BRANCH_NO in ('966120','966121','966122','966123','966125')
"""
db.cursor.execute(q1_sql)
row1 = db.cursor.fetchall()
tlist = list(row) + list(row1)
tlist.sort()
print len(row), len(row1)
return row + row1
finally:
db.closeDB()
def delete_hook(del_sql, hook_id):
try:
db = util.DBConnect()
db.cursor.execute(del_sql, hook_id)
db.conn.commit()
finally:
db.closeDB()
def starrun(etldate):
d1 = datetime.now()
Config().etldate = etldate
manager = multiprocessing.Manager()
q1 = manager.Queue()
pw1 = Process(target=util.queue2db, args=(q1, ))
print "start queue2db 1"
print "etldate:", etldate
pw1.start()
DimManage().setQueue(q1)
run(Config().etldate)
q1.put(None)
pw1.join()
db = util.DBConnect()
if Config().etldate % 100 <= 8:
ldate = int(util.daycalc(Config().etldate, 0 - Config().etldate % 100))
ldate = int(util.daycalc(ldate, 1 - ldate % 100))
else:
ldate = int(util.daycalc(Config().etldate, 1 - Config().etldate % 100))
#ldate=20160630
del_sql = """truncate table BALANCE_MANAGE_ID_TMP IMMEDIATE"""
db.cursor.execute(del_sql)
db.conn.commit()
ins_sql = """
insert into BALANCE_MANAGE_ID_TMP
select * from BALANCE_MANAGE_ID where ACCT_ID in(
select distinct a.id from ACCOUNT_HOOK ah
join D_ACCOUNT a on ah.ACCOUNT_NO=a.ACCOUNT_NO
where ah.TYP in ('存款','理财') and ah.STATUS='录入已审批')
"""
db.cursor.execute(ins_sql)
db.conn.commit()
mer_sql = """
merge into YDW.F_BALANCE f
using BALANCE_MANAGE_ID b
on f.date_id = ? and f.account_id = b.acct_id
when matched then update set f.manage_id=b.manage_id
"""
begin = thisdatetime.date(int(str(ldate)[0:4]), int(str(ldate)[4:6]),
int(str(ldate)[6:8]))
end = thisdatetime.date(int(str(Config().etldate)[0:4]),
int(str(Config().etldate)[4:6]),
int(str(Config().etldate)[6:8]))
for i in range((end - begin).days + 1):
mer_date = int(util.daycalc(ldate, i))
db.cursor.execute(mer_sql, mer_date)
db.conn.commit()
print mer_date, i, datetime.now() - d1
mer_sql = """
merge into YDW.F_BALANCE f
using BALANCE_MANAGE_ID b
on f.date_id = ? and f.account_id = b.acct_id
when matched then update set f.manage_id=b.manage_id
"""
db.cursor.execute(mer_sql, Config().etldate)
db.conn.commit()
print Config().etldate, datetime.now() - d1
db.closeDB()
print "over time =", datetime.now() - d1
def mid_bus(stardate, etldate):
try:
oneday = datetime.now()
db = util.DBConnect()
print "mid_bus:开始"
while stardate <= etldate:
sql_end = """
select monthend_id from d_date where id=%s
""" % (stardate)
db.cursor.execute(sql_end)
date_end_1 = db.cursor.fetchone()
date_end = date_end_1[0]
print date_end
sql0 = """
DELETE FROM MID_BUSINESS WHERE DATE_ID=?
"""
db.cursor.execute(sql0, int(stardate))
db.conn.commit()
sql = """
insert into MID_BUSINESS(date_id,mid_key1,mid_key2,org_code,sale_code,mid_type,sign_date,state,action)values
(?,?,?,?,?,?,?,?,?)
"""
db.cursor.executemany(sql, load_teller_imbs(stardate))
db.conn.commit()
insert_sql = """
insert into MID_BUSINESS(date_id,mid_key1,mid_key2,org_code,sale_code,mid_type,sign_date,state,action)
select * from
(
select a.open_date, a.net_cst_no,a.cst_no,OPEN_BRANCH_NO ,a.manage_code ,a.mid_type ,a.open_date1,'1' ,'I'
from
(select id,open_date,net_cst_no||'-' ||'shou' net_cst_no,cst_no,OPEN_BRANCH_NO ,manage_code ,'手机银行' mid_type,open_date open_date1 from D_CUST_CONTRACT where BUSI_TYPE = '手机银行' and OPEN_date=?
and SUB_TYPE ='专业版'
union all
select id,open_date,net_cst_no||'-'||'qyi' net_cst_no,cst_no,OPEN_BRANCH_NO ,manage_code ,'企业网上银行' mid_type ,open_date open_date1 from D_CUST_CONTRACT where BUSI_TYPE = '企业网上银行' and OPEN_date=?
union all
select id,open_date,net_cst_no||'-'||'geyin' net_cst_no,cst_no,OPEN_BRANCH_NO ,manage_code ,'个人网上银行' mid_type ,open_date open_date1 from D_CUST_CONTRACT where BUSI_TYPE = '个人网上银行' and OPEN_date=?
and SUB_TYPE ='专业版')a
inner join F_CONTRACT_STATUS f on a.id=f.CONTRACT_ID
and f.DATE_ID =? and F.STATUS IN ('正常','暂时冻结','停用','冻结','未激活') ------------状态f.date_id只取月末
union all
select open_date,id_number||'-'||'katong',cst_no,OPEN_BRANCH_NO ,manage_code ,'支付宝卡通' ,open_date,'1' ,'I' from D_CUST_CONTRACT where BUSI_TYPE = '支付宝卡通' and OPEN_date=?
union all
select open_date,id_number||'-'||'kuaijie',cst_no,OPEN_BRANCH_NO ,manage_code ,'支付宝快捷支付' ,open_date,'1' ,'I' from D_CUST_CONTRACT where BUSI_TYPE = '支付宝快捷支付' and OPEN_date=?
)
"""
db.cursor.execute(insert_sql, [
int(stardate),
int(stardate),
int(stardate),
int(date_end),
int(stardate),
int(stardate)
])
db.conn.commit()
print stardate, "完成", datetime.now() - oneday
stardate = int(util.daycalc(stardate, 1))
finally:
db.closeDB()
def run(etldate, batch_id=None):
db = util.DBConnect()
movelist = []
day = etldate % 100
tag = False
try:
if batch_id is None:
if day > 8:
sql = """
SELECT C.ID, FROM_TELLER_NO, TO_TELLER_NO, HOOK_TYP, TYP, STATUS, DEAL_STATUS
FROM YDW.CUST_HOOK_BATCH C
WHERE DEAL_STATUS='同意'
"""
#else:
# sql ="""
# SELECT C.ID, FROM_TELLER_NO, TO_TELLER_NO, HOOK_TYP, TYP, STATUS, DEAL_STATUS
# FROM YDW.CUST_HOOK_BATCH C
# JOIN F_USER f on c.from_teller_no = f.user_name and f.is_virtual = '是'
# WHERE DEAL_STATUS='同意'
# """
tag = True
db.cursor.execute(sql)
movelist = db.cursor.fetchall()
else:
sql = """
SELECT C.ID, FROM_TELLER_NO, TO_TELLER_NO, HOOK_TYP, TYP, STATUS, DEAL_STATUS
FROM YDW.CUST_HOOK_BATCH C
WHERE DEAL_STATUS='同意' and ID = ?
"""
db.cursor.execute(sql, batch_id)
movelist = db.cursor.fetchall()
if tag:
add_hook_his(db, etldate, movelist) #增加拉链历史
date_sql = """
select MONTHBEG_ID from d_date where id = ?
"""
db.cursor.execute(date_sql, etldate)
end_date = db.cursor.fetchone()
for i in movelist:
print i[0]
au_sql = """
update ACCOUNT_HOOK set MANAGER_NO = ?,STATUS = '正常',etl_date = ? where BATCH_ID = ?
"""
cu_sql = """
update CUST_HOOK set MANAGER_NO = ?,STATUS = '正常', etl_date = ? where BATCH_ID = ?
"""
bu_sql = """
update CUST_HOOK_BATCH set DEAL_STATUS = '已移交' where ID = ?
"""
print i[2], end_date[0], i[0]
db.cursor.execute(au_sql, i[2], end_date[0], i[0])
db.cursor.execute(cu_sql, i[2], end_date[0], i[0])
db.cursor.execute(bu_sql, i[0])
db.conn.commit()
finally:
db.closeDB()
def credit_bad(stardate,etldate):
try:
oneday=datetime.now()
db = util.DBConnect()
while stardate<=etldate:
sql0 = """
UPDATE YDW.REPORT_CREDIT_BAD
SET BAD_BAL=0, ALL_BAL=0, PPL_BAL=0
WHERE DATE_ID=?
"""
db.cursor.execute(sql0,int(stardate))
db.conn.commit()
sql1="""
SELECT SUM(REM_PPL),F.DATE_ID, D.MANAGER_NO FROM F_CREDIT_MPUR_20161031 F
INNER JOIN (SELECT DISTINCT A.MANAGER_NO,D.ACCOUNT_NO FROM CREDIT_BAD_HOOK A INNER JOIN D_CREDIT_CARD D ON D.CARD_NO=A.CARD_NO) D ON D.ACCOUNT_NO=F.ACCOUNT_NO
WHERE F.DATE_ID = ?
GROUP BY F.DATE_ID, D.MANAGER_NO
"""
db.cursor.execute(sql1,int(stardate))
row1=db.cursor.fetchall()
resultrow1=[]
for i in row1:
t=list(i[0:])
resultrow1.append(t)
sql2="""
UPDATE REPORT_CREDIT_BAD SET PPL_BAL=? WHERE DATE_ID=? AND SALE_CODE=?
"""
db.cursor.executemany(sql2,resultrow1)
"""不良 BAD_BAL"""
sql2="""
SELECT SUM(CASE WHEN F.MTHS_ODUE > 0 THEN F.STM_BALFRE+F.STM_BALINT+F.STM_BALMP+F.BAL_FREE +F.BAL_INT+F.BAL_MP ELSE 0 END) BAD_BAL,--不良透支金额
SUM(F.STM_BALFRE+F.STM_BALINT+F.STM_BALMP+F.BAL_FREE +F.BAL_INT+F.BAL_MP) ALL_BAL, --总额(少分期)
F.DATE_ID , A.MANAGER_NO
FROM F_CREDIT_BAD F
INNER JOIN D_CREDIT_CARD D ON D.ID=F.CRAD_ID
INNER JOIN (SELECT DISTINCT A.MANAGER_NO,D.ACCOUNT_NO,A.FLAG FROM CREDIT_BAD_HOOK A INNER JOIN D_CREDIT_CARD D ON D.CARD_NO=A.CARD_NO) A ON A.ACCOUNT_NO=D.ACCOUNT_NO
WHERE F.DATE_ID = ? AND ( -F.BAL_FREE + (CASE WHEN F.BAL_INT_FLAG='-' THEN F.BAL_INT ELSE -F.BAL_INT END) -F.STM_BALFRE + (CASE WHEN F.STMBALINT_FLAG='-' THEN F.STM_BALINT ELSE -F.STM_BALINT END) -F.BAL_MP - F.STM_BALMP ) < 0 AND F.CLOSE_CODE NOT IN ('W','Q','WQ')
GROUP BY F.DATE_ID,A.MANAGER_NO
"""
db.cursor.execute(sql2,int(stardate))
row2=db.cursor.fetchall()
resultrow2=[]
for i in row2:
t=list(i[0:])
resultrow2.append(t)
sql8="""
UPDATE REPORT_CREDIT_BAD SET BAD_BAL=?, ALL_BAL=? WHERE DATE_ID=? AND SALE_CODE=?
"""
db.cursor.executemany(sql8,resultrow2)
db.conn.commit()
print stardate,"完成",datetime.now()- oneday
stardate=int(util.daycalc(stardate,1))
finally :
db.closeDB()
def update_ltd(startdate, enddate):
try:
db = util.DBConnect()
etldate = startdate
while int(etldate) <= int(enddate):
update(db, etldate, u'TPDM_TS_DLCG_KHXX')
etldate = int(util.daycalc(etldate, 1))
db.conn.commit()
finally:
db.closeDB()
def run(self):
try:
self.db = util.DBConnect()
print "ck_start"
self.transfer_ck()
print "dk_start"
self.transfer_dk()
self.db.conn.commit()
finally:
self.close()
def update_org_manage_bal(etldate):
d1 = datetime.now()
try :
db = util.DBConnect()
delete(db,etldate)
update(db,etldate)
db.conn.commit()
finally :
db.closeDB()
print datetime.now() -d1
def run(etldate):
#update_cst_no() #ETC更新客户内码
try:
db = util.DBConnect()
sql = """truncate table CONTRACT_MANAGE_ID IMMEDIATE"""
db.cursor.execute(sql)
db.conn.commit()
if os.path.exists(tfile): os.remove(tfile)
d1 = datetime.now()
ebanksql = """
select trim(c.CST_NO),f.CONTRACT_ID,f.DATE_ID,o.org0_code,c.BUSI_TYPE from F_CONTRACT_STATUS f
join D_CUST_CONTRACT c on f.CONTRACT_ID=c.ID
join D_ORG o on f.org_id=o.id
where f.DATE_ID = ? and c.BUSI_TYPE in ('支付宝快捷支付') and substr(c.cst_no,1,1)='8'
"""
#writer_manage_id(db,get_ebank_cust_manage,ebanksql,etldate)
loanconsql = """
select c.CST_NO,f.CONTRACT_ID,f.DATE_ID,c.OPEN_BRANCH_NO from F_CONTRACT_STATUS f
join D_CUST_CONTRACT c on f.CONTRACT_ID=c.ID
where f.DATE_ID = ? and c.BUSI_TYPE='贷款合同'
"""
writer_manage_id(db, get_loan_cust_manage, loanconsql, etldate)
load_files()
print "load time =", datetime.now() - d1
if etldate % 100 <= 8:
ldate = int(util.daycalc(etldate, 0 - etldate % 100))
ldate = int(util.daycalc(ldate, 1 - ldate % 100))
else:
ldate = int(util.daycalc(etldate, 1 - etldate % 100))
for i in range(ldate, Config().etldate + 1):
mer_sql = """
merge into YDW.F_CONTRACT_STATUS f
using CONTRACT_MANAGE_ID b
on f.date_id = ? and f.contract_id = b.contract_id when matched then update set f.manage_id=b.manage_id
"""
db.cursor.execute(mer_sql, i)
db.conn.commit()
print i
"""
分割d_manage 测试放在这里，之后是放在etl每日任务的最后
"""
OUT_SQLCODE = 1
OUT_MSG = ''
msg = db.cursor.callproc("P_D_SALE_MANAGE_RELA",
[str(20150101), OUT_SQLCODE, OUT_MSG])
if msg[1] != 0:
print "error msg:%s" % msg[2]
db.conn.commit()
finally:
db.closeDB()
def load_loan_price_view(etldate):
db = util.DBConnect()
try:
db.cursor.execute("delete from GAS_BI_CUX_LOAN_CHECK_DTL_V where date_id = ?",(etldate))
db.conn.commit()
finally:
db.closeDB()
'''
导入总账系统下发的贷款视图
'''
prices = query_loan_para_price(etldate)
db = util.DBConnect()
try:
sql2 = """
SELECT A.ACCOUNT_NO,F.MANAGE_ID,t.GUA_TP_NAME,f.year_pdt FROM F_BALANCE F
inner join d_account_type t on t.id = f.ACCOUNT_TYPE_ID
INNER JOIN D_ACCOUNT A ON A.ID = F.ACCOUNT_ID WHERE F.DATE_ID=? AND F.ACCT_TYPE='4'
"""
db.cursor.execute(sql2, etldate )
row = db.cursor.fetchone()
datas = {}
desc = db.cursor.description
ld = len(desc)
sm = StarManage(2,[ DimStarPrice() ])
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
while row:
data = { desc[x][0] : row[x] for x in range(ld) }
dim_id = find_loan_price_id(sm, prices, data)
datas[ row[0] ] = (row[1],dim_id,row[3])
row = db.cursor.fetchone()
loan = BICUXLOAN()
loan.manages = datas
StarBase().files2fact2(loan, sm)
sm.finish()
finally:
db.closeDB()
def update_cst_no():
try:
db = util.DBConnect()
m_sql = """
update D_CUST_CONTRACT c set c.CST_NO=(select CST_NO from D_CREDIT_CARD where CARD_NO=c.CARD_NO) where c.BUSI_TYPE='ETC' and substr(c.CST_NO,1,1)<>'8'
"""
"""
跑完datastage之后可能会把第三方存管内码重新变成空，需要重新更新第三方存管的内码
"""
sql0 = u"""
UPDATE D_CUST_CONTRACT D SET CST_NO=( SELECT A.CST_NO FROM ( SELECT DISTINCT D.ACCT_NO,C.CST_NO,MIN(D.ID) AS ID FROM D_CUST_CONTRACT D
JOIN D_ACCOUNT C ON D.ACCT_NO=C.ACCOUNT_NO
WHERE D.BUSI_TYPE='第三方存管' GROUP BY D.ACCT_NO,C.CST_NO ) A WHERE D.ACCT_NO=A.ACCT_NO AND D.ID =A.ID FETCH FIRST 1 ROWS ONLY) WHERE D.BUSI_TYPE='第三方存管'
"""
"""更新完内码后，没有的内码会变成空，在更新manageid时会报错，要把空更新成‘无’"""
sql1 = u"""
update d_cust_contract set CST_NO='无' where BUSI_TYPE='第三方存管' and cst_no is null
"""
sql2 = u"""
update D_CUST_CONTRACT D set CST_NO=( select A.CST_NO from ( select distinct d.ACCT_NO,c.CST_NO,min(d.id) as id from D_CUST_CONTRACT D
join D_ACCOUNT C on D.ACCT_NO=C.ACCOUNT_NO
where d.BUSI_TYPE='手机银行' and d.CST_NO is null group by d.ACCT_NO,c.CST_NO ) A where D.ACCT_NO=A.ACCT_NO and d.id =A.id fetch first 1 rows only) where d.BUSI_TYPE='手机银行' and d.cst_no is null
"""
sql3 = u"""
MERGE INTO D_CUST_CONTRACT A
USING (SELECT C.CUST_NO,D.ID FROM D_CUST_INFO C JOIN D_CUST_CONTRACT D ON C.CUST_LONG_NO=D.CST_NO
WHERE C.ID IN(SELECT MAX(I.ID) AS CID FROM D_CUST_CONTRACT D JOIN D_CUST_INFO I ON D.CST_NO = I.CUST_LONG_NO
WHERE LENGTH(TRIM(D.CST_NO)) != 11 AND D.BUSI_TYPE = '支付宝卡通' AND D.CST_NO !='' GROUP BY I.CUST_LONG_NO ) ) B ON A.ID=B.ID
WHEN MATCHED THEN UPDATE SET A.CST_NO=B.CUST_NO
"""
sql4 = u"""
MERGE INTO D_CUST_CONTRACT A
USING (SELECT D.ID,MAX(C.CUST_NO) AS CUST_NO FROM D_CUST_CONTRACT D
JOIN D_CUST_INFO C ON C.CUST_LONG_NO='101'||(D.ID_NUMBER)
WHERE D.BUSI_TYPE='第三方存管' AND D.CST_NO='无' GROUP BY D.ID) B ON A.ID=B.ID
WHEN MATCHED THEN UPDATE SET A.CST_NO=B.CUST_NO
"""
sql5 = u"""
MERGE INTO D_CUST_CONTRACT A
USING (SELECT D.ID,MAX(C.CUST_NO) AS CUST_NO FROM D_CUST_CONTRACT D
JOIN D_CUST_INFO C ON C.CUST_LONG_NO='1'||CCRD15TO18(D.ID_NUMBER)
WHERE D.BUSI_TYPE='第三方存管' AND D.CST_NO='无' GROUP BY D.ID) B ON A.ID=B.ID
WHEN MATCHED THEN UPDATE SET A.CST_NO=B.CUST_NO
"""
db.cursor.execute(m_sql)
db.cursor.execute(sql0.encode('utf-8'))
db.cursor.execute(sql1.encode('utf-8'))
db.cursor.execute(sql2.encode('utf-8'))
db.cursor.execute(sql3.encode('utf-8'))
db.cursor.execute(sql4.encode('utf-8'))
db.cursor.execute(sql5.encode('utf-8'))
db.conn.commit()
finally:
db.closeDB()
def query_row(etldate, dh='A', org_no=None, db=None, account_id=None):
new = False
if db is None:
new = True
db = util.DBConnect()
try:
sql = """
select e.*,f.*,a.account_no,a.CASH_TP
from F_ACCOUNT_BALANCE f
inner join d_account_type_extend e on e.id=f.ACCOUNT_TYPE_ID
inner join d_account_gid a on a.id=f.account_id
inner join d_org_stat s on s.id = f.ORG_ID
"""
if account_id is not None:
sql = sql + " where f.account_id = ? and f.date_id = ? "
db.cursor.execute(sql, (account_id, int(etldate)))
else:
sql = sql + " where f.date_id = ? "
if dh == 'A':
pass
elif dh == 'D':
sql = sql + " and f.account_class in ('D','C') "
elif dh == 'H':
sql = sql + " and f.account_class in ('H') "
else:
raise Exception("not support dh typeL:%s" % dh)
#if org_no is not None:
# sql = sql + " and s.org_code = '%s'"%( org_no )
print sql
#sql = sql + " with ur "
db.cursor.execute(sql, etldate)
row = db.cursor.fetchone()
desc = db.cursor.description
ld = len(desc)
d = {}
while row:
data = {}
for x in range(ld):
data[desc[x][0]] = row[x]
if data["AUTO_DEP"] == '自动转存' and data["PRODUCT"] == '301' and data[
"BUSI_CODE"] == '13' and data["DUE_DATE"] == 18991231:
st = data["START_INTEREST"]
if st == 18991231: st = data["OPEN_DATE"]
data["DUE_DATE"] = add_month_date(st, data["TERM"])
if data["AUTO_DEP"] == '自动转存' and data["PRODUCT"] == '101' and data[
"BUSI_CODE"] == '17' and data["DUE_DATE"] == 18991231:
st = data["START_INTEREST"]
if st == 18991231: st = data["OPEN_DATE"]
data["DUE_DATE"] = add_month_date(st, data["TERM"])
yield data
row = db.cursor.fetchone()
finally:
if new: db.closeDB()
def update_py_date(etldate):
try:
db = util.DBConnect()
etldate = util.daycalc(etldate, 1)
next_date = util.daycalc(etldate, 1)
sql = """
UPDATE YDW.P_ETL_DATE SET PY_DATE_ID=?, PY_NEXT_DATE_ID=? WHERE JOB_SEQ =1
"""
db.cursor.execute(sql, int(etldate), int(next_date))
db.conn.commit()
finally:
db.closeDB()
def run(etldate):
try:
db = util.DBConnect()
sql = """truncate table BALANCE_MANAGE_ID IMMEDIATE"""
db.cursor.execute(sql)
db.conn.commit()
if os.path.exists(tfile): os.remove(tfile)
d1 = datetime.now()
creditsql = """
select c.CARD_NO,c.CST_NO,s.CARD_ID,s.DATE_ID,c.OPEN_BRANCH_CODE from F_CREDIT_CARD_STATUS s
join D_CREDIT_CARD c on s.CARD_ID=c.ID
where s.DATE_ID = ?
"""
writer_manage_id(db, get_credit_account_manage, creditsql, etldate)
load_files()
print "load time =", datetime.now() - d1
if etldate % 100 <= 8:
ldate = int(util.daycalc(etldate, 0 - etldate % 100))
ldate = int(util.daycalc(ldate, 1 - ldate % 100))
else:
ldate = int(util.daycalc(etldate, 1 - etldate % 100))
begin = thisdatetime.date(int(str(ldate)[0:4]), int(str(ldate)[4:6]),
int(str(ldate)[6:8]))
end = thisdatetime.date(int(str(Config().etldate)[0:4]),
int(str(Config().etldate)[4:6]),
int(str(Config().etldate)[6:8]))
for i in range((end - begin).days + 1):
mer_date = int(util.daycalc(ldate, i))
mer_sql = """
merge into YDW.F_CREDIT_CARD_STATUS f
using BALANCE_MANAGE_ID b
on f.date_id =? and f.card_id = b.acct_id
when matched then update set f.manage_id=b.manage_id
"""
db.cursor.execute(mer_sql, mer_date)
db.conn.commit()
print mer_date
"""
分割d_manage 测试放在这里，之后是放在etl每日任务的最后
"""
#OUT_SQLCODE = 1
#OUT_MSG=''
#msg = db.cursor.callproc("P_D_SALE_MANAGE_RELA",[str(Config().etldate),OUT_SQLCODE,OUT_MSG])
#if msg[1]!= 0:
# print "error msg:%s"%msg[2]
db.conn.commit()
finally:
db.closeDB()