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 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 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()
def starrun(etldate):
DimManage().refresh_cash()
d1 = datetime.now()
Config().etldate = etldate
manager = multiprocessing.Manager()
q1 = manager.Queue()
pw1 = Process(target=util.queue2db, args=(q1, ))
pw1.start()
info("update_balance_manage_load q1_proces_id=%s" % (str(pw1.pid)))
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))
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 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 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 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 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 run(etldate):
try:
db = util.DBConnect()
sql = """truncate table ACPT_MANAGE_ID IMMEDIATE"""
db.cursor.execute(sql)
db.conn.commit()
if os.path.exists(tfile): os.remove(tfile)
d1 = datetime.now()
acptsql = """
SELECT A.CST_NO,F.ACCOUNT_ID,F.DATE_ID,O.ORG0_CODE FROM F_ACPT_STATUS F
JOIN D_ACPT_ACCOUNT A ON F.ACCOUNT_ID=A.ID
JOIN D_ORG O ON F.ORG_ID=O.ID
WHERE DATE_ID= ?
"""
writer_manage_id(db, get_acpt_cust_manage, acptsql, etldate)
load_files()
print "load time =", datetime.now() - d1
if etldate % 100 <= 5:
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_ACPT_STATUS f
using ACPT_MANAGE_ID b
on f.date_id = ? and f.account_id = b.acpt_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 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 run2(etldate):
d1 = datetime.now()
etl_run(update_hook_fun, "即时生效", etldate)
etl_run3(etlpeople.starrun, "更新客户信息etl_people", etldate, etldate, 'OCR')
etl_run3(financialsale.runstar, "处理理财数据,承兑汇票,核销清单,逾期还款清单", etldate,
etldate, 'FTBHB')
etl_run2(etlcontract.run_etl, "处理丰收e支付、贷款合同、支付宝快捷支付和信用卡不良", etldate,
etldate)
etl_run(ccrd_star.starrun, "福农卡相关", etldate)
etl_run(updatehook.input_pass, "录入审批当天生效,应该在自动挂钩前", etldate)
#etl_run3(etlhook.starun, "新增挂钩关系etl_hook2", etldate, etldate, None)
#调整挂钩比例挂钩程序
etl_run(hookstar.starun, "新增挂钩,自动挂钩关系etl_hook2", etldate)
etl_run2(fbalance.insert_close, "补齐f_balance销户的存款账号1", etldate, etldate)
etl_run2(insert.man_dep, "补齐f_balance销户的存款账号2", etldate, etldate)
etl_run2(loanyearpdt.update_yearpdt, "更新贷款year_pdt字段", etldate, etldate)
etl_run(manage_id.starrun, "存贷款等manage_id", etldate)
etl_run(contract_id.starrun, "处理电子银行相关manage_id", etldate)
etl_run(credit_id.starrun, "信用卡credict_manage_id", etldate)
etl_run(call_sale_rela_proc, "call_sale_rela_proc", etldate)
#etl_run(calc_sale_manage, "执行分割d_manage 的存储过程", etldate)
etl_run(update_hook_fun2, "更新录入已审批状态以及月初移交生效", etldate)
if etldate % 100 == 8:
ldate = int(util.daycalc(etldate, 0 - etldate % 100))
ldate = int(util.daycalc(ldate, 1 - ldate % 100))
this_month_end = int(util.daycalc(etldate, 1))
last_one_day = int(util.daycalc(etldate, -1))
else:
ldate = int(util.daycalc(etldate, 1 - etldate % 100))
last_one_day = int(util.daycalc(etldate, -1))
last_month_end = int(util.daycalc(ldate, -1))
#ldate=etldate
wady, monthRange = calendar.monthrange(int(str(etldate)[0:4]),
int(str(etldate)[4:6]))
this_month_end = int(util.daycalc(ldate, monthRange - 1))
print last_one_day, this_month_end, last_month_end
etl_run2(reportinit.report_init, "初始化指标报表人员信息", ldate, etldate)
etl_run(dep_month.sum_dep_report, "客户经理存款指标报表-按月", etldate)
etl_run2(dep.man_dep, "客户经理存款指标报表", ldate, etldate)
etl_run2(loan.man_loan, "客户经理贷款指标报表", ldate, etldate)
etl_run2(ebank.man_dep, "客户经理其他指标报表", ldate, etldate)
etl_run2(creditcard.man_creditcard, "客户经理信用卡指标报表", ldate, etldate)
etl_run2(cny.cny, "外币折人名币", etldate, etldate)
etl_run3(ebills_hook.mergeods, "国际业务每天汇全量", last_one_day, etldate,
'EBILLS')
etl_run(ebills_star.starrun, "国际业务详情", etldate)
etl_run(ods_load.starrun, "ods全量等", etldate)
if int(etldate) == this_month_end:
etl_run3(financialsale.runstar, "国际业务结算量按月客户", ldate, etldate, 'CORP')
print '国际业务结算量按月客户'
update_sql()
fixseq.sequence()
process_info()
def starun(etldate):
db = DBConnect()
try:
print "delete from F_ACCOUNT_BALANCE", etldate
db.cursor.execute(
"delete from F_ACCOUNT_BALANCE f where ACCOUNT_CLASS='D' and date_id= ?",
(etldate))
#db.cursor.execute("delete from F_ACCOUNT_BALANCE f where ACCOUNT_CLASS='H' and date_id= ?",(etldate))
#db.cursor.execute("delete from F_ACCOUNT_BALANCE f where ACCOUNT_CLASS='C' and date_id= ?",(etldate))
#db.cursor.execute("delete from F_ACCOUNT_BALANCE f where date_id= ?",(etldate))
db.conn.commit()
print "finish delete from F_ACCOUNT_BALANCE", etldate
finally:
db.closeDB()
lastdate = int(util.daycalc(etldate, -1))
#if etldate != FIRSTDATE:
# mergeallfile(etldate)
act2 = DimAccount2()
act2.start()
ac = DimAccountGid()
dim1 = DimAccountTypeExtend()
dim2 = DimGroup()
sm = StarManage(10, [dim1, dim2, ac, act2])
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
'''
ds3 = HQAB()
if etldate >= ds3.firstday:
StarBase().files2fact2(ds3, sm)
if etldate >= ds3.firstday:
put_ds_data(ds3, sm, etldate)
sm.restart_fact_process()
'''
ds1 = DQAB()
if etldate >= ds1.firstday:
StarBase().files2fact2(ds1, sm)
if etldate >= ds1.firstday:
put_ds_data(ds1, sm, etldate)
sm.restart_fact_process()
'''
ds2 = DQABC()
if etldate >= ds2.firstday:
StarBase().files2fact2(ds2, sm)
if etldate >= ds2.firstday:
put_ds_data(ds2, sm, etldate)
sm.restart_fact_process()
'''
sm.finish()
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 update_ltd(startdate, enddate):
try:
db = util.DBConnect()
etldate = startdate
while int(etldate) <= int(enddate):
m_sql = """
merge into F_CONTRACT_STATUS f1
using F_CONTRACT_STATUS f2 on f1.contract_id = f2.contract_id and f2.date_id= ? and f1.date_id= ?
when matched then update set f1.last_trade_date=f2.last_trade_date
"""
db.cursor.execute(m_sql, int(util.daycalc(etldate, -1)), etldate)
update(db, etldate, u'手机银行', u'IBS_MB_PB_TRANFLOW')
update(db, etldate, u'企业网上银行', u'IBS_CB_TRANFLOW')
update(db, etldate, u'个人网上银行', u'IBS_PB_TRANFLOW')
etldate = int(util.daycalc(etldate, 1))
db.conn.commit()
finally:
db.closeDB()
def insert_close(startdate, enddate):
try:
d1 = datetime.now()
db = util.DBConnect()
etldate = startdate
while int(etldate) <= int(enddate):
m_sql = """
INSERT INTO YDW.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 T.ACCOUNT_ID, T.ACCOUNT_TYPE_ID, T.MANAGE_ID, ?, T.ACCOUNT_STATUS_ID, T.ACCOUNT_PRICE_ID, 0, T.OUT_BALANCE, T.CONTRACT_AMT, T.DR_AMOUNT, T.CR_AMOUNT, (case when T.date_id=d.YEAREND_ID then 0 else T.YEAR_PDT end), T.RE_BALANCE, T.SUM_RE_INTEREST, T.LUPD_DATE_ID, T.IN_TO_OUT_AMOUNT, T.INT_BAL, T.ACCT_TYPE, T.CST_NO, T.INT_AMOUNT, T.NEXT_INT_DATE_ID, T.CLAC_INT, T.LAST_RECV_DATE, T.LAST_CINT_DATE, T.RELA_DEP_BAL, T.ORG_ID, T.CST_ID, T.ACCOUNT_TYPE2_ID
FROM YDW.F_BALANCE T
JOIN D_DATE d ON T.DATE_ID=d.ID
JOIN D_ACCOUNT A ON T.ACCOUNT_ID=A.ID and a.close_date_id<= ? and a.close_date_id>d.L_L_YEAREND_ID
WHERE T.DATE_ID= ? AND T.ACCT_TYPE=1
"""
db.cursor.execute(m_sql, int(etldate), int(etldate),
int(util.daycalc(etldate, -1)))
print etldate, datetime.now() - d1
etldate = int(util.daycalc(etldate, 1))
db.conn.commit()
finally:
db.closeDB()
def update_yearpdt(startdate,enddate):
try :
d1=datetime.now()
db = util.DBConnect()
etldate=startdate
while int(etldate)<=int(enddate):
if str(etldate)[4:]=='0101':
m_sql ="""
update F_BALANCE set YEAR_PDT = BALANCE where DATE_ID= ? and ACCT_TYPE in (1,4)
"""
print 'YEAR_BEG_ID',etldate
db.cursor.execute(m_sql,int(etldate))
else:
m_sql ="""
MERGE INTO F_BALANCE F1
USING F_BALANCE F2 ON F1.ACCOUNT_ID=F2.ACCOUNT_ID AND F1.DATE_ID= ? AND F2.DATE_ID = ? AND F1.ACCT_TYPE in (1,4) AND F2.ACCT_TYPE in (1,4)
WHEN MATCHED THEN UPDATE SET F1.YEAR_PDT=F2.YEAR_PDT+F1.BALANCE
"""
#m1_sql ="""
#MERGE INTO F_BALANCE F1
#USING F_BALANCE F2 ON F1.ACCOUNT_ID=F2.ACCOUNT_ID AND F1.DATE_ID= ? AND F2.DATE_ID = ? AND F1.ACCT_TYPE in (1,4) AND F2.ACCT_TYPE in (1,4)
#WHEN MATCHED THEN UPDATE SET F1.ORG_ID=F2.ORG_ID
#"""
#tmp_sql ="""
#update F_BALANCE set ORG_ID=(select id from D_ORG where ORG0_CODE='966163') where ACCOUNT_ID in (
#select ACCOUNT_ID from F_BALANCE f
#join D_LOAN_ACCOUNT a on f.ACCOUNT_ID=a.ID
#join D_ORG o on f.ORG_ID=o.ID and o.ORG0_CODE='966166'
#where f.DATE_ID= ? and a.OPEN_BRANCH_CODE='966166' and f.ACCT_TYPE=4
#) and DATE_ID= ? and ACCT_TYPE=4"""
db.cursor.execute(m_sql,int(etldate),int(util.daycalc(etldate,-1)))
#db.cursor.execute(tmp_sql,int(etldate),int(etldate))
print etldate,datetime.now()-d1
etldate=int(util.daycalc(etldate,1))
db.conn.commit()
finally :
db.closeDB()
def cny(stardate, etldate):
try:
oneday = datetime.now()
db = util.DBConnect()
print "cny:开始"
while stardate <= etldate:
sql0 = """
DELETE FROM REPORT_SALE_RMB_EXG WHERE DATE_ID=?
"""
db.cursor.execute(sql0, int(stardate))
db.conn.commit()
sql = """
INSERT INTO REPORT_SALE_RMB_EXG
select a.DATE_ID,nvl(b.open_branch_code,a.THIRD_BRANCH_CODE),nvl(b.BRANCH_NAME,a.THIRD_BRANCH_NAME),a.SALE_CODE,a.SALE_NAME ,a.cust_name,a.CUST_NO,money
from
(select F.DATE_ID,R.THIRD_BRANCH_CODE,THIRD_BRANCH_NAME,R.SALE_CODE,R.SALE_NAME ,da.cust_name,da.CUST_NO,ROUND(SUM(F.BALANCE*R.PERCENT*0.01*P.RMB_EXG_RATE*0.0000000001),0) money
FROM F_BALANCE F
join d_cust_info da on f.cst_no=da.cust_no
INNER JOIN D_ACCOUNT_PRICE P ON P.ID=F.ACCOUNT_PRICE_ID
INNER JOIN D_SALE_MANAGE_RELA R ON R.MANAGE_ID = F.MANAGE_ID
INNER JOIN d_account d on f.ACCOUNT_ID=d.id
WHERE F.ACCOUNT_PRICE_ID = P.ID AND P.CCY IN ( 'AUD','EUR','GBP','HKD','JPY','SGD','USD' )
AND F.DATE_ID=? AND F.BALANCE !=0 and left(da.CUST_NO,2)='82' and f.ACCT_TYPE='1' and d.CLOSE_DATE_ID>=F.DATE_ID
GROUP BY F.DATE_ID,R.THIRD_BRANCH_CODE,SALE_CODE ,da.CUST_NAME,da.cust_no,R.THIRD_BRANCH_NAME,R.SALE_NAME) a
left join
(
select d.CST_NO,d.ccy,d.open_branch_code,b.BRANCH_NAME
from D_ACCOUNT d
join BRANCH b on d.OPEN_BRANCH_CODE=b.BRANCH_CODE
where ccy='CNY' and left(CST_NO,2)='82'
and CST_NO in
(select CST_NO
from
(select d.CST_NO,d.open_branch_code
from D_ACCOUNT d
where ccy='CNY' and left(CST_NO,2)='82'
group by d.CST_NO,d.open_branch_code)
group by CST_NO having count(*)=1)
group by d.CST_NO,d.ccy,d.open_branch_code,b.BRANCH_NAME
) b
on a.CUST_NO=b.CST_NO
"""
db.cursor.execute(sql, int(stardate))
db.conn.commit()
print stardate, "完成", datetime.now() - oneday
stardate = int(util.daycalc(stardate, 1))
finally:
db.closeDB()
def starrun(startdate, enddate):
d1 = datetime.now()
Config().etldate = enddate
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()
del_sql = """truncate table BALANCE_MANAGE_ID_TMP IMMEDIATE"""
db.cursor.execute(del_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
"""
ldate = startdate
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 loan_old(stardate, etldate):
try:
oneday = datetime.now()
db = util.DBConnect()
while stardate <= etldate:
yearmonth = str(stardate)[0:6]
print yearmonth
"""先删除再插入"""
sql0 = """
DELETE FROM OLD_BAD_LOANS WHERE LCAJDATE=%s
""" % (yearmonth)
db.cursor.execute(sql0)
sql = """
INSERT INTO OLD_BAD_LOANS ( AFAABRNO ,ORG0_NAME,LCAJDATE,DONE_DATE_ID ,RES1,RES2,RES3)
SELECT LCAABRNO ,ORG0_NAME,LCAJDATE,DUE_DATE_ID ,RES1,RES2,RES3 FROM
( (SELECT RNAGBRNO AS LCAABRNO,O.ORG0_NAME,LEFT(RNAUDATE,6) AS LCAJDATE,D.DUE_DATE_ID, SUM(RNAUAMT ) AS RES1,
SUM(RNARIAM2 + RNATIAM2+ RNBMIAM2+RNBNIAM2+RNCCIAM2) AS RES2,
SUM(0) AS RES3
FROM F_CORE_BLFMMTRN F
JOIN D_LOAN_ACCOUNT D ON D.ACCOUNT_NO = F.RNAAAC15 AND DAYS(TO_DATE(F.RNAUDATE,'YYYYMMDD'))>DAYS(TO_DATE(D.DUE_DATE_ID,'YYYYMMDD'))
JOIN D_ORG O ON F.RNAGBRNO=O.ORG0_CODE
WHERE RNABNOTE IN ('柜面收本金','柜面收本息','柜面收利息 ','自动收本金','自动收本息','自动收利息 ')
AND D.DUE_DATE_ID <= 20151231 AND RNAAWBFG='1' AND LEFT(RNAUDATE,6)=%s
GROUP BY RNAGBRNO,O.ORG0_NAME,LEFT(RNAUDATE,6),D.DUE_DATE_ID)
UNION ALL
(SELECT BL.AFAABRNO,O.ORG0_NAME,LEFT(BL.AFBKDATE,6) AS LCAJDATE ,LA.DUE_DATE_ID,SUM(BL.PRINCIPAL) AS RES1,SUM(INTEREST) AS RES2,SUM(0) AS RES3
FROM F_CORE_BLFMCNAF BL --核销贷款明细
INNER JOIN D_LOAN_ACCOUNT LA ON BL.AFAAAC15=LA.ACCOUNT_NO AND DAYS(TO_DATE(BL.AFBKDATE,'YYYYMMDD')) > DAYS(TO_DATE(LA.DUE_DATE_ID,'YYYYMMDD'))
INNER JOIN D_ORG O ON BL.AFAABRNO=O.ORG0_CODE
WHERE LA.DUE_DATE_ID<20160101 AND LEFT(BL.AFBKDATE,6)=%s
GROUP BY BL.AFAABRNO,O.ORG0_NAME,LEFT(BL.AFBKDATE,6),LA.DUE_DATE_ID) )
""" % (yearmonth, yearmonth)
db.cursor.execute(sql)
#row = db.cursor.fetchall()
#resultrow=[]
#for i in row:
# t=list(i[0:])
# resultrow.append(t)
##print resultrow
#db.cursor.executemany(sql1,resultrow)
db.conn.commit()
print stardate, "完成", datetime.now() - oneday
stardate = int(util.daycalc(stardate, 1))
finally:
db.closeDB()
def sum_dep_report(etldate):
day = int(str(etldate)[6:8])
if day <= 8:
startdate = int(str(etldate)[0:6]+"01")
lmend = int(util.daycalc(startdate,-1))
lmstart = int(str(lmend)[0:6]+"01")
msg = "dep.sum_dep_report,计算上个月报表:%d-%d"%(lmstart, lmend)
info( msg )
man_dep(lmstart, lmend)
msg = msg + "调用结束"
info( msg )
startdate = int(str(etldate)[0:6]+"01")
enddate = etldate
msg = "dep.sum_dep_report,计算本月报表:%d-%d"%(startdate, etldate)
info( msg )
man_dep(startdate,etldate)
msg = msg + "调用结束"
info( msg )
def starun(etldate):
db = DBConnect()
try:
db.cursor.execute("delete from F_CREDIT_BAD where date_id= ?",
(etldate))
db.conn.commit()
finally:
db.closeDB()
lastdate = int(util.daycalc(etldate, -1))
sm = StarManage(2, [])
#sm.start_dim_process()
sm.start_fact_process()
#sm.setDimQueue()
sm.start()
ds1 = CcrdAcct2()
StarBase().files2fact2(ds1, sm)
sm.finish()
def man_dep(stardate,etldate):
last_year = (int(str(etldate)[0:4])-1)*10000 +1231
info("start dep.man_dep %s-%s"%(str(stardate),str(etldate)))
try:
oneday=datetime.now()
db = util.DBConnect()
cllist = sum_cl_dep(db, etldate,last_year)
db = util.DBConnect()
while int(stardate) <= int(etldate):
sql0="""
UPDATE YDW.REPORT_MANAGER_DEP
SET PRI_LAST_AVG=0, PUB_LAST_AVG=0, LAST_AVG=0, PRI_THIS_AVG=0, PUB_THIS_AVG=0, THIS_AVG=0, FIN_LAST_AVG=0, FIN_THIS_AVG=0, PRI_BAL=0, PUB_BAL=0, FIN_BAL=0, BAL=0, LAST_AVG_SAL=0, ADD_AVG_SAL=0, PRI_MONTH_AVG=0, PUB_MONTH_AVG=0, MONTH_AVG=0, PRI_PDT=0, PUB_PDT=0, LICAI_PDT=0, DEP_SCORE=0, TRY_LAST_AVG_SAL=0, TRY_ADD_AVG_SAL=0,FLAG = 0
WHERE DATE_ID=?
"""
db.cursor.execute(sql0,int(stardate))
db.conn.commit()
sql="""
SELECT
0 AS DSCL,
0 AS DGCL,
BIGINT(SUM(CASE WHEN F.ACCT_TYPE=8 THEN NVL(F1.YEAR_PDT,0)*M.PERCENT/ 100 ELSE 0 END)/ DD.YEAR_DAYS) AS CL,--存量（理财部分)
BIGINT(SUM(CASE WHEN LEFT(F.CST_NO,2)='81' THEN ( CASE WHEN SUBSTR(DATE_ID,5,2)!='01'THEN (NVL(F.YEAR_PDT,0)-NVL(F2.YEAR_PDT,0))*M.PERCENT/ 100 ELSE NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 END ) ELSE 0 END)/ D.BEG_MONTH_DAYS) AS DSYLJ,--对私月日均
BIGINT(SUM(CASE WHEN LEFT(F.CST_NO,2)='82' THEN ( CASE WHEN SUBSTR(DATE_ID,5,2)!='01'THEN (NVL(F.YEAR_PDT,0)-NVL(F2.YEAR_PDT,0))*M.PERCENT/ 100 ELSE NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 END ) ELSE 0 END)/ D.BEG_MONTH_DAYS) AS DGYLJ,--对公月日均
BIGINT(SUM(CASE WHEN SUBSTR(DATE_ID,5,2)!='01' THEN (NVL(F.YEAR_PDT,0)-NVL(F2.YEAR_PDT,0))*M.PERCENT/ 100 ELSE NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 END )/ D.BEG_MONTH_DAYS) AS YLJ,--月日均
BIGINT(SUM(CASE WHEN LEFT(F.CST_NO,2)='81' AND F.ACCT_TYPE=1 THEN NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 ELSE 0 END)/ D.BEG_YEAR_DAYS) AS DSXL,--对私现量
BIGINT(SUM(CASE WHEN LEFT(F.CST_NO,2)='82' AND F.ACCT_TYPE=1 THEN NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 ELSE 0 END)/ D.BEG_YEAR_DAYS) AS DGXL,--对公现量
BIGINT(SUM(NVL(F.YEAR_PDT,0) *M.PERCENT/ 100)/ D.BEG_YEAR_DAYS) AS XL,--现量
BIGINT(SUM(CASE WHEN F.ACCT_TYPE=8 THEN NVL(F1.YEAR_PDT,0)*M.PERCENT/ 100 ELSE 0 END)/ DD.YEAR_DAYS) AS LCCL,--理财存量日均
BIGINT(SUM(CASE WHEN F.ACCT_TYPE=8 THEN NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 ELSE 0 END)/ D.BEG_YEAR_DAYS) AS LCXL,--理财现量
BIGINT(SUM(CASE WHEN LEFT(F.CST_NO,2)='81' AND F.ACCT_TYPE=1 THEN NVL(F.BALANCE,0)*M.PERCENT/ 100 ELSE 0 END)), --对私余额
BIGINT(SUM(CASE WHEN LEFT(F.CST_NO,2)='82' AND F.ACCT_TYPE=1 THEN NVL(F.BALANCE,0)*M.PERCENT/ 100 ELSE 0 END)), --对公余额
BIGINT(SUM(CASE WHEN F.ACCT_TYPE=8 AND A.CLOSE_DATE_ID>F.DATE_ID THEN NVL(F.BALANCE,0)*M.PERCENT/ 100 ELSE 0 END)), --理财余额
BIGINT(SUM(CASE WHEN LEFT(F.CST_NO,2)='81' AND F.ACCT_TYPE=1 THEN NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 ELSE 0 END)), --对私积数
BIGINT(SUM(CASE WHEN LEFT(F.CST_NO,2)='82' AND F.ACCT_TYPE=1 THEN NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 ELSE 0 END)), --对公积数
BIGINT(SUM(CASE WHEN F.ACCT_TYPE=8 AND A.CLOSE_DATE_ID>F.DATE_ID THEN NVL(F.YEAR_PDT,0)*M.PERCENT/ 100 ELSE 0 END)), --理财积数
BIGINT(SUM(CASE WHEN A.CLOSE_DATE_ID>F.DATE_ID THEN NVL(F.BALANCE,0)*M.PERCENT/ 100 ELSE 0 END)), --余额合计
NVL(M.THIRD_BRANCH_NAME,'无'),NVL(M.SALE_NAME,'无'),F.DATE_ID,M.SALE_CODE,M.THIRD_BRANCH_CODE
FROM F_BALANCE F
LEFT JOIN (SELECT DISTINCT ACCOUNT_ID,YEAR_PDT FROM F_BALANCE F JOIN D_ACCOUNT D ON F.ACCOUNT_ID = D.ID JOIN ACCOUNT_HOOK AH ON D.ACCOUNT_NO = AH.ACCOUNT_NO
WHERE DATE_ID=(SELECT L_YEAREND_ID FROM D_DATE WHERE ID=?) AND F.ACCT_TYPE IN(1,8) AND AH.START_DATE <=(SELECT L_YEAREND_ID FROM D_DATE WHERE ID=?) ) F1 ON F1.ACCOUNT_ID=F.ACCOUNT_ID
LEFT JOIN (SELECT ACCOUNT_ID,YEAR_PDT FROM F_BALANCE F WHERE DATE_ID=(SELECT L_MONTHEND_ID FROM D_DATE WHERE ID=?) AND ACCT_TYPE IN(1,8)) F2 ON F2.ACCOUNT_ID=F.ACCOUNT_ID
JOIN D_ACCOUNT A ON F.ACCOUNT_ID=A.ID
JOIN D_ACCOUNT_TYPE T ON F.ACCOUNT_TYPE_ID=T.ID AND ( F.ACCT_TYPE=8
OR LEFT(T.SUBJECT_CODE,4) IN ('2001','2003','2005','2006','2011','2014','2002','2004','2012','2013')
OR LEFT(T.SUBJECT_CODE,6) IN ('231401','231409','231421','231499','231403','231402','231422','201712','201713','201714','201702','201703','201704')
OR T.SUBJECT_CODE IN ('20070101','20070201','20070301','20170198','20171198'))
JOIN D_DATE D ON F.DATE_ID=D.ID
JOIN D_DATE DD ON D.L_YEAREND_ID=DD.ID
JOIN D_SALE_MANAGE_RELA M ON F.MANAGE_ID=M.MANAGE_ID
WHERE F.ACCT_TYPE IN (1,8) AND F.DATE_ID = ? ----AND M.THIRD_BRANCH_CODE IN ('966100') AND M.SALE_CODE IN ('9660160','X9660160')
GROUP BY F.DATE_ID,M.THIRD_BRANCH_CODE,M.THIRD_BRANCH_NAME,M.SALE_CODE,M.SALE_NAME,D.BEG_YEAR_DAYS,DD.YEAR_DAYS,D.BEG_MONTH_DAYS
"""
sql1="""
UPDATE YDW.REPORT_MANAGER_DEP SET PRI_LAST_AVG=?, PUB_LAST_AVG=?, LAST_AVG=?, PRI_MONTH_AVG=?, PUB_MONTH_AVG=?, MONTH_AVG=?, PRI_THIS_AVG=?, PUB_THIS_AVG=?, THIS_AVG=?, FIN_LAST_AVG=?, FIN_THIS_AVG=?, PRI_BAL=?, PUB_BAL=?, FIN_BAL=?,PRI_PDT=?,PUB_PDT=?,LICAI_PDT=?, BAL=?,ORG_NAME=?,SALE_NAME=?,FLAG = 1 WHERE DATE_ID=? AND SALE_CODE=? AND ORG_CODE=?
"""
sql2="""
UPDATE YDW.REPORT_MANAGER_DEP SET PRI_LAST_AVG=?, PUB_LAST_AVG=?, LAST_AVG=LAST_AVG+?,FLAG = 1
WHERE DATE_ID=? AND SALE_CODE=? AND ORG_CODE=?
"""
#info("dep.man_dep,sql : \n %s"%(sql))
db.cursor.execute(sql,int(stardate),int(stardate),int(stardate),int(stardate))
row=db.cursor.fetchone()
resultrow=[]
while row:
resultrow.append( list(row) )
row = db.cursor.fetchone()
#info("dep.man_dep,sql_update : \n %s"%(sql1))
db.cursor.executemany(sql1,resultrow)
for x in cllist:
x[3] = stardate
db.cursor.execute(sql2,x)
db.conn.commit()
sql2="""
MERGE INTO REPORT_MANAGER_DEP R
USING V_STAFF_INFO V ON V.USER_NAME=R.SALE_CODE AND R.DATE_ID =? ---AND R.SALE_NAME IS NULL
WHEN MATCHED THEN UPDATE SET R.SALE_NAME=V.NAME
"""
db.cursor.execute(sql2,int(stardate))
sql3="""
MERGE INTO REPORT_MANAGER_DEP R
USING D_ORG V ON V.ORG0_CODE=R.ORG_CODE AND R.DATE_ID =? ---AND R.ORG_NAME IS NULL
WHEN MATCHED THEN UPDATE SET R.ORG_NAME=V.ORG0_NAME
"""
db.cursor.execute(sql3,int(stardate))
db.conn.commit()
sql4="""
DELETE FROM REPORT_MANAGER_DEP WHERE DATE_ID=? AND FLAG=0
"""
db.cursor.execute(sql4,int(stardate))
db.conn.commit()
#orgdep.man_dep(stardate,stardate)
"""刷客户经理的佣金报表"""
month_end_sql="""
select month_end from D_DATE where ID=?
"""
db.cursor.execute(month_end_sql,stardate)
is_month_end=db.cursor.fetchall()
if is_month_end[0][0]=='Y':
info("dep.man_dep,depsal.man_dep_sal")
depsal.man_dep_sal(etldate)
#dep_sco.man_dep_sco_new(etldate,etldate)
print stardate,"完成",datetime.now()- oneday
info("dep.man_dep 完成")
stardate=int(util.daycalc(stardate,1))
finally :
db.closeDB()
def man_posatm(stardate,etldate):
try:
oneday=datetime.now()
db = util.DBConnect()
while stardate<=etldate:
yearmonth=str(stardate)[0:6]
print yearmonth
"""FARM_SERV_HIGH_NUM ---高服务点"""
sql="""
MERGE INTO REPORT_MANAGER_OTHER A
USING (SELECT POS_MON_DATE,POS_ORG_NO,POS_MANAGER_NO,SUM(HIGH_POS_NUM) TOTAL_NUM
FROM ((SELECT MON_DATE POS_MON_DATE,ORG_NO POS_ORG_NO,MANAGER_NO POS_MANAGER_NO, COUNT(1) HIGH_POS_NUM
FROM (SELECT DISTINCT B.MON_DATE,F.ORG_NO,F.MANAGER_NO,F.MERCHANT_NO,F.POS_NO,NVL(B.MON_TRAN_NUM,0) MON_TRAN_NUM
FROM (SELECT C.ORG_NO,C.MANAGER_NO,A.MERCHANT_NO,A.POS_NO,CASE WHEN A.END_DATE IS NULL THEN 20991231 ELSE A.END_DATE END POS_END_DATE
FROM D_POS A ----客户经理名下的所有POS编号
LEFT JOIN CUST_HOOK_HIS C ON C.CUST_NO=A.MERCHANT_NO AND C.CUST_IN_NO=A.POS_NO
WHERE C.TYP='POS' AND C.SUB_TYP='助农' AND C.STATUS IN ('待审批','已审批','预提交审批','正常','录入已审批'))F
LEFT JOIN (SELECT ORG_NO,MANAGER_NO,MERCHANT_NO,POS_NO,LEFT(DATE_ID,6) MON_DATE,SUM(TRAN_NUM) MON_TRAN_NUM,COUNT(1) TRAN_DAYS ----一个月中客户经理名下一个POS交易次数达到要求的(每一条记录代表每一个月独一无二POS产生的笔数)
FROM F_POS_TRAN_NUM WHERE LEFT(DATE_ID,6)= ? GROUP BY ORG_NO,MANAGER_NO,MERCHANT_NO,POS_NO,LEFT(DATE_ID,6) )B ---!!!!要加POS台数要求
ON F.MERCHANT_NO=B.MERCHANT_NO AND F.POS_NO=B.POS_NO AND F.MANAGER_NO=B.MANAGER_NO
WHERE ? <=POS_END_DATE )P ---这是?是参数
WHERE P.MON_TRAN_NUM>=60 ----这里的MON_DATE参数要改成变量
GROUP BY P.MON_DATE,P.ORG_NO,P.MANAGER_NO)
UNION ALL
(SELECT F.MON_DATE ATM_MON_DATE,F.ORG_NO ATM_ORG_NO,F.MANAGER_NO ATM_MANAGER_NO,COUNT(1) HIGH_ATM_NUM -----每月客户经理名下的台数
FROM (SELECT DISTINCT MON_DATE,ATM_NO,C.ORG_NO,C.MANAGER_NO,NVL(MON_TRAN_NUM,0) MON_TRAN_NUM--找到对应的客户经理(这是客户经理名下每一台独一无二ATM一个月中交易的次数)
FROM ( SELECT DISTINCT MON_ID MON_DATE,A.ATM_NO,MON_TRAN_NUM,CASE WHEN A.END_DATE IS NULL THEN 20991231 ELSE END_DATE END END_DATE
FROM D_ATM A ---找到对应的机器号
LEFT JOIN (SELECT LEFT(D_ATM_DATE_ID,6) MON_ID,D_ATM_ID,SUM(D_ATM_TRAN_NUM) MON_TRAN_NUM FROM
F_ATM_TRAN_NUM WHERE LEFT(D_ATM_DATE_ID,6)=?
GROUP BY LEFT(D_ATM_DATE_ID,6),D_ATM_ID ) MON_ATM_TRAN_NUM---一个月中客户经理下这个机器交易次数达到要求的
ON A.ID=MON_ATM_TRAN_NUM.D_ATM_ID ) MON_ATM
LEFT JOIN CUST_HOOK_HIS C ON MON_ATM.ATM_NO=C.CUST_IN_NO WHERE C.TYP='机具' AND C.SUB_TYP='助农终端'
AND C.STATUS IN ('待审批','已审批','预提交审批','正常','录入已审批') AND ? <MON_ATM.END_DATE ) F ----?是参数
WHERE F.MON_TRAN_NUM>=60 GROUP BY F.MON_DATE,F.ORG_NO,F.MANAGER_NO))A
GROUP BY POS_MON_DATE,POS_ORG_NO,POS_MANAGER_NO)PP
ON(LEFT(A.DATE_ID,6)=PP.POS_MON_DATE AND A.ORG_CODE=PP.POS_ORG_NO AND A.SALE_CODE=PP.POS_MANAGER_NO)
WHEN MATCHED THEN UPDATE SET A.FARM_SERV_HIGH_NUM=PP.TOTAL_NUM
"""
"""低服务活点率 """
sql1="""
MERGE INTO REPORT_MANAGER_OTHER A USING (SELECT POS_MON_DATE,POS_ORG_NO,POS_MANAGER_NO,SUM(HIGH_POS_NUM) TOTAL_NUM
FROM ((SELECT %s POS_MON_DATE,ORG_NO POS_ORG_NO,MANAGER_NO POS_MANAGER_NO, COUNT(1) HIGH_POS_NUM
FROM (SELECT DISTINCT B.MON_DATE,F.ORG_NO,F.MANAGER_NO,F.MERCHANT_NO,F.POS_NO,NVL(B.MON_TRAN_NUM,0) MON_TRAN_NUM
FROM (SELECT C.ORG_NO,C.MANAGER_NO,A.MERCHANT_NO,A.POS_NO,CASE WHEN A.END_DATE IS NULL THEN 20991231 ELSE A.END_DATE END POS_END_DATE
FROM D_POS A ----客户经理名下的所有POS编号
LEFT JOIN CUST_HOOK_HIS C ON C.CUST_NO=A.MERCHANT_NO AND C.CUST_IN_NO=A.POS_NO
WHERE C.TYP='POS' AND C.SUB_TYP='助农' AND C.STATUS IN ('待审批','已审批','预提交审批','正常','录入已审批') )F
LEFT JOIN
(SELECT ORG_NO,MANAGER_NO,MERCHANT_NO,POS_NO,LEFT(DATE_ID,6) MON_DATE,SUM(TRAN_NUM) MON_TRAN_NUM,COUNT(1) TRAN_DAYS ----一个月中客户经理名下一个POS交易次数达到要求的(每一条记录代表每一个月独一无二POS产生的笔数)
FROM F_POS_TRAN_NUM WHERE LEFT(DATE_ID,6)= %s GROUP BY ORG_NO,MANAGER_NO,MERCHANT_NO,POS_NO,LEFT(DATE_ID,6) )B ---!!!!要加POS台数要求
ON F.MERCHANT_NO=B.MERCHANT_NO AND F.POS_NO=B.POS_NO AND F.MANAGER_NO=B.MANAGER_NO
WHERE %s <=POS_END_DATE )P ---这是s是参数
WHERE P.MON_TRAN_NUM<60 ----这里的MON_DATE参数要改成变量注意是
GROUP BY P.MON_DATE,P.ORG_NO,P.MANAGER_NO)
UNION ALL
( SELECT %s ATM_MON_DATE,F.ORG_NO ATM_ORG_NO,F.MANAGER_NO ATM_MANAGER_NO,COUNT(1) HIGH_ATM_NUM -----每月客户经理名下的台数
FROM (SELECT DISTINCT MON_DATE,ATM_NO,C.ORG_NO,C.MANAGER_NO,NVL(MON_TRAN_NUM,0) MON_TRAN_NUM--找到对应的客户经理(这是客户经理名下每一台独一无二ATM一个月中交易的次数)
FROM( SELECT DISTINCT MON_ID MON_DATE,A.ATM_NO,MON_TRAN_NUM,CASE WHEN A.END_DATE IS NULL THEN 20991231 ELSE END_DATE END END_DATE
FROM D_ATM A ---找到对应的机器号
LEFT JOIN (SELECT LEFT(D_ATM_DATE_ID,6) MON_ID,D_ATM_ID,SUM(D_ATM_TRAN_NUM) MON_TRAN_NUM FROM
F_ATM_TRAN_NUM WHERE LEFT(D_ATM_DATE_ID,6)= %s
GROUP BY LEFT(D_ATM_DATE_ID,6),D_ATM_ID ) MON_ATM_TRAN_NUM---一个月中客户经理下这个机器交易次数达到要求的
ON A.ID=MON_ATM_TRAN_NUM.D_ATM_ID ) MON_ATM
LEFT JOIN CUST_HOOK_HIS C
ON MON_ATM.ATM_NO=C.CUST_IN_NO WHERE C.TYP='机具' AND C.SUB_TYP='助农终端' AND C.STATUS IN ('待审批','已审批','预提交审批','正常','录入已审批') AND %s<MON_ATM.END_DATE) F
WHERE F.MON_TRAN_NUM<60 GROUP BY F.MON_DATE,F.ORG_NO,F.MANAGER_NO))A
GROUP BY POS_MON_DATE,POS_ORG_NO,POS_MANAGER_NO)PP
ON LEFT(A.DATE_ID,6)=PP.POS_MON_DATE AND A.ORG_CODE=PP.POS_ORG_NO AND A.SALE_CODE=PP.POS_MANAGER_NO
WHEN MATCHED THEN UPDATE SET A.FARM_SERVICE_LOW_NUM=PP.TOTAL_NUM
"""%(yearmonth,yearmonth,stardate,yearmonth,yearmonth,stardate)
db.cursor.execute(sql,(yearmonth),(stardate),(yearmonth),(stardate))
db.cursor.execute(sql1)
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:
sql0 = """
UPDATE YDW.REPORT_MANAGER_OTHER
SET MB_LAST_NUM=0, MB_THIS_NUM=0, CB_LAST_NUM=0, CB_THIS_NUM=0, EPAY_LAST_NUM=0, EPAY_THIS_NUM=0, ETC_LAST_NUM=0, ETC_THIS_NUM=0, PB_LAST_NUM=0, PB_THIS_NUM=0, KT_LAST_NUM=0, KT_THIS_NUM=0, KJ_LAST_NUM=0, KJ_THIS_NUM=0, ADD_HIGH_POS_NUM=0, ADD_LOW_POS_NUM=0, FARM_SERV_HIGH_NUM=0, FARM_SERVICE_LOW_NUM=0, LAST_THIRD_DEPO_NUM=0, THIRD_DEPO_ADD_NUM=0, THIS_ADD_ETC_NUM=0, MB_ADD_NUM_SAL=0, CB_ADD_NUM_SAL=0, EPAY_ADD_NUM_SAL=0, ADD_HIGH_POS_SAL=0, ADD_LOW_POS_SAL=0, FARM_SERV_SAL=0, ADD_ETC_NUM_SAL=0, ADD_THIRD_DEPO_SAL=0, ALL_SCO=0, MB_ADD_SCO=0, CB_ADD_SCO=0, POS_ADD_SCO=0, BAD_ADD_SCO=0, ETC_ADD_SCO=0, EPAY_ADD_SCO=0, FRAM_SCO=0, POS_THIS_NUM=0, BASE_PAY=0, POSITION_PAY=0, BRANCH_NET_SAL=0, MANAGE_BUS_SAL=0, WORK_QUALITY_SAL=0, HIG_CIV_QUAL_SAL=0, JOB_SAT_SAL=0, DAY_DEP_COMP_PER=0, DAY_DEP_SAL=0, DAY_DEP_SEC_FEN=0, CREDIT_POOL=0, INTER_SET_SAL=0, SALE_VOC_SAL=0, ADD_EFC_CURSAL=0, ADD_FUNON_SAL=0, PER_CAR_DANERSAL=0, BUM_HOM_SAL=0, OTHER_ACHI_SAL=0, COMPRE_SAL=0, LABOR_COMP_SAL=0, PROV_FUND_SAL=0, SAFE_FAN_SAL=0, ALL_RISK_SAL=0, BAD_LOAN_PERSAL=0, FTP_ACH_SAL=0, COUNT_COMPLE_SAL=0, COUNT_COP_SSAL=0, HP_FINA_SAL=0, OTHER_SPEC_SAL1=0, OTHER_SPEC_SAL2=0, OTHER_SPEC_SAL3=0, OTHER_SPEC_SAL4=0, OTHER_SPEC_SAL5=0, BRANCH_SECO_FEN1=0, BRANCH_SECO_FEN2=0, BRANCH_SECO_FEN3=0, BRANCH_SECO_FEN4=0, OTHER_ACH_WAGES=0, OVER_WORK_SAL=0, OTHER_SAL1_DUAN=0, OTHER_SAL2=0, OTHER_SAL3_WEI=0, OTHER_SAL4_KE=0, OTHER_SAL5_GE=0, OTHER_SAL6=0, OTHER_SAL7=0, OTHER_SAL8=0, QJ_BAD_LOAN_SAL=0, ADD_FUNON_NUM=0, FLAG=0
WHERE DATE_ID=?
"""
db.cursor.execute(sql0, int(stardate))
db.conn.commit()
sql = """
SELECT COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='手机银行' AND F.SUB_TYPE='专业版' AND F.STATUS NOT IN ('已注销','注销') THEN C.CST_NO END )), --手机银行户数
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='手机银行' AND F.SUB_TYPE='专业版' AND F.STATUS NOT IN ('已注销','注销') AND (DAYS(TO_DATE(F.DATE_ID,'YYYYMMDD'))-DAYS(TO_DATE(MAX(F.LAST_LOGON_DATE,LEFT(C.OPEN_DATE,8)),'YYYYMMDD')))<=180 THEN C.CST_NO END )), --手机银行有效户数
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='个人网上银行' AND F.SUB_TYPE='专业版' AND F.STATUS NOT IN ('已注销','注销') THEN C.CST_NO END )), --个人银行户数
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='个人网上银行' AND F.SUB_TYPE='专业版' AND F.STATUS NOT IN ('已注销','注销') AND (DAYS(TO_DATE(F.DATE_ID,'YYYYMMDD'))-DAYS(TO_DATE(MAX(F.LAST_LOGON_DATE,LEFT(C.OPEN_DATE,8)),'YYYYMMDD')))<=180 THEN C.CST_NO END )), --个人银行有效户数
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='企业网上银行' AND F.STATUS<>'销户' THEN C.CST_NO END)), --企业银行有效户数存量
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='企业网上银行' AND F.STATUS<>'销户' AND (DAYS(TO_DATE(F.DATE_ID,'YYYYMMDD'))-DAYS(TO_DATE(MAX(F.LAST_LOGON_DATE,LEFT(C.OPEN_DATE,8)),'YYYYMMDD')))<=180 THEN C.CST_NO END )), --企业银行有效户数现量
SUM(CASE WHEN C.BUSI_TYPE='支付宝卡通' AND F1.STATUS IN ('签约成功','正常') THEN 1 ELSE 0 END ), --支付宝卡通有效户数存量
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='支付宝卡通' AND F.STATUS IN ('签约成功','正常') THEN C.CST_NO END )), --支付宝卡通有效户数现量
SUM(CASE WHEN C.BUSI_TYPE='支付宝快捷支付' THEN 1 ELSE 0 END ), --支付宝快捷支付有效户数存量
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='支付宝快捷支付' THEN C.CST_NO END )), --支付宝快捷支付有效户数现量
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE IN ('丰收e支付','新丰收e支付') AND F.STATUS<>'注销' THEN C.AGREMENT_NO END )), --丰收E支付户数
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE IN ('丰收e支付','新丰收e支付') AND F.STATUS<>'注销' AND (DAYS(TO_DATE(F.DATE_ID,'YYYYMMDD'))-DAYS(TO_DATE(MAX(F.LAST_TRADE_DATE,LEFT(C.OPEN_DATE,8)),'YYYYMMDD')))<=180 THEN C.AGREMENT_NO END )), --丰收E支付有效户数现量
SUM(CASE WHEN C.BUSI_TYPE='ETC' AND F1.STATUS IN ('签约成功','正常') THEN 1 ELSE 0 END), --ECT 存量
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='ETC' AND F.STATUS IN ('签约成功','正常') THEN C.NET_CST_NO END)), --ETC 现量
SUM (CASE WHEN C.BUSI_TYPE='第三方存管' AND F1.STATUS IN ('已签约') THEN 1 ELSE 0 END), --存量
COUNT(DISTINCT (CASE WHEN C.BUSI_TYPE='第三方存管' AND F.STATUS IN ('已签约') THEN C.ID_NUMBER END)), --量
F.DATE_ID,M.SALE_CODE
FROM F_CONTRACT_STATUS F
LEFT JOIN (SELECT CONTRACT_ID,STATUS,LAST_LOGON_DATE,LAST_TRADE_DATE FROM F_CONTRACT_STATUS F WHERE DATE_ID=(SELECT L_YEAREND_ID FROM D_DATE WHERE ID=?) ) F1 ON F1.CONTRACT_ID=F.CONTRACT_ID
JOIN D_CUST_CONTRACT C ON F.CONTRACT_ID=C.ID
JOIN D_DATE D ON F.DATE_ID=D.ID
JOIN D_DATE DD ON D.L_YEAREND_ID=DD.ID
JOIN D_SALE_MANAGE_RELA M ON F.MANAGE_ID=M.MANAGE_ID --AND M.MANAGE_TYPE<>'机构管理'
WHERE F.DATE_ID =? ----AND M.SALE_CODE IN ('9660160','X9660160')
GROUP BY F.DATE_ID,M.SALE_CODE
"""
sql1 = """
UPDATE YDW.REPORT_MANAGER_OTHER SET MB_LAST_NUM=?, MB_THIS_NUM=?, PB_LAST_NUM=?, PB_THIS_NUM=?, CB_LAST_NUM=?, CB_THIS_NUM=?, KT_LAST_NUM=?, KT_THIS_NUM=?, KJ_LAST_NUM=?, KJ_THIS_NUM=?, EPAY_LAST_NUM=?, EPAY_THIS_NUM=?, ETC_LAST_NUM=?, ETC_THIS_NUM=?,LAST_THIRD_DEPO_NUM=?,THIRD_DEPO_ADD_NUM=?,FLAG=1 WHERE DATE_ID=? AND SALE_CODE=?
"""
db.cursor.execute(sql, int(stardate), int(stardate))
row = db.cursor.fetchall()
#print row
resultrow = []
for i in row:
t = list(i[0:])
resultrow.append(t)
#print resultrow
db.cursor.executemany(sql1, resultrow)
db.conn.commit()
#更新ETC相关
sql_e = u"""SELECT LEFT(L_YEAREND_ID,4) FROM D_DATE WHERE ID=?"""
db.cursor.execute(sql_e, stardate)
row_e = db.cursor.fetchall()
last_year = row_e[0][0]
sql_et = u"""
SELECT COUNT(A.CUST_NET_NO),COUNT(B.CUST_NET_NO),CASE WHEN LENGTH(TRIM(A.TELLER_NO))<6 THEN A.ORG_NO ELSE A.TELLER_NO END ,A.ORG_NO
FROM ETC_DATA A LEFT JOIN ETC_DATA B ON A.CUST_NET_NO=B.CUST_NET_NO AND LEFT(B.DATE_ID,4)=?
WHERE LEFT(A.DATE_ID,4)=(SELECT YEAR FROM D_DATE WHERE ID=?) GROUP BY A.TELLER_NO,A.ORG_NO
"""
db.cursor.execute(sql_et, last_year, stardate)
row_et = db.cursor.fetchall()
u_sql_et = u"""
UPDATE YDW.REPORT_MANAGER_OTHER SET ETC_THIS_NUM=?,ETC_LAST_NUM=?,FLAG=1 WHERE SALE_CODE=? AND ORG_CODE=?
"""
resultrow_et = []
for i in row_et:
t = list(i[0:])
resultrow_et.append(t)
#print resultrow_et
db.cursor.executemany(u_sql_et, resultrow_et)
db.conn.commit()
"""刷客户经理的佣金报表"""
month_end_sql = """
select month_end from D_DATE where ID=?
"""
db.cursor.execute(month_end_sql, stardate)
is_month_end = db.cursor.fetchall()
if is_month_end[0][0] == 'Y':
ebanksal.man_ebank_sal(etldate)
posatm.man_posatm(stardate, stardate)
#ebank_sco.man_ebank_sco_new(stardate,stardate)
sql2 = """
DELETE FROM REPORT_MANAGER_OTHER WHERE DATE_ID=? AND FLAG=0
"""
db.cursor.execute(sql2, int(stardate))
db.conn.commit()
print stardate, "完成", datetime.now() - oneday
stardate = int(util.daycalc(stardate, 1))
finally:
db.closeDB()
def man_dep(stardate, etldate):
try:
db = util.DBConnect()
while stardate <= etldate:
sql = """
DELETE FROM REPORT_ORG_DEP WHERE DATE_ID = ?
"""
sql1 = """
INSERT INTO YDW.REPORT_ORG_DEP(DATE_ID, ORG_CODE, ORG_NAME, PRI_LAST, PRI_THIS)
SELECT F.DATE_ID,M.THIRD_BRANCH_CODE,M.THIRD_BRANCH_NAME,
SUM(F1.BALANCE) AS DSCL, --对私存量日均存款
SUM(F.BALANCE) AS DSXL --对私现量日均存款
FROM F_BALANCE F
LEFT JOIN (SELECT ACCOUNT_ID, BALANCE FROM F_BALANCE F WHERE DATE_ID=(SELECT L_YEAREND_ID FROM D_DATE WHERE ID= ?) ) F1 ON F1.ACCOUNT_ID=F.ACCOUNT_ID
JOIN D_ACCOUNT A ON F.ACCOUNT_ID=A.ID
JOIN D_ACCOUNT_TYPE T ON F.ACCOUNT_TYPE_ID=T.ID AND ( LEFT(T.SUBJECT_CODE,4) IN ('2003','2004')
OR LEFT(T.SUBJECT_CODE,6) IN ('200502') )
JOIN D_DATE D ON F.DATE_ID=D.ID
JOIN D_DATE DD ON D.L_YEAREND_ID=DD.ID
JOIN D_SALE_MANAGE_RELA M ON F.MANAGE_ID=M.MANAGE_ID
WHERE F.DATE_ID =?
GROUP BY F.DATE_ID,M.THIRD_BRANCH_CODE,M.THIRD_BRANCH_NAME
"""
sql2 = """
SELECT
SUM(F1.BALANCE) AS DGCL, --对公存量日均存款
SUM(F.BALANCE) AS DGXL, --对公现量日均存款
F.DATE_ID,M.THIRD_BRANCH_CODE
FROM F_BALANCE F
LEFT JOIN (SELECT ACCOUNT_ID, BALANCE FROM F_BALANCE F WHERE DATE_ID=(SELECT L_YEAREND_ID FROM D_DATE WHERE ID= ?) ) F1 ON F1.ACCOUNT_ID=F.ACCOUNT_ID
JOIN D_ACCOUNT A ON F.ACCOUNT_ID=A.ID
JOIN D_ACCOUNT_TYPE T ON F.ACCOUNT_TYPE_ID=T.ID AND ( LEFT(T.SUBJECT_CODE,4) IN ('2001','2002','2006','2011','2014','2012','2013')
OR LEFT(T.SUBJECT_CODE,6) IN ('200501') OR T.SUBJECT_CODE IN ('20070101','20070201','20070301') )
JOIN D_DATE D ON F.DATE_ID=D.ID
JOIN D_DATE DD ON D.L_YEAREND_ID=DD.ID
JOIN D_SALE_MANAGE_RELA M ON F.MANAGE_ID=M.MANAGE_ID
WHERE F.DATE_ID =?
GROUP BY F.DATE_ID,M.THIRD_BRANCH_CODE,M.THIRD_BRANCH_NAME
"""
sql3 = """
SELECT
SUM(F1.BALANCE) AS DGCL, --理财存量日均
SUM(F.BALANCE) AS DGXL, --理财现量日均
F.DATE_ID,M.THIRD_BRANCH_CODE
FROM F_BALANCE F
LEFT JOIN (SELECT ACCOUNT_ID, BALANCE FROM F_BALANCE F WHERE DATE_ID=(SELECT L_YEAREND_ID FROM D_DATE WHERE ID= ?) ) F1 ON F1.ACCOUNT_ID=F.ACCOUNT_ID
JOIN D_ACCOUNT A ON F.ACCOUNT_ID=A.ID
JOIN D_ACCOUNT_TYPE T ON F.ACCOUNT_TYPE_ID=T.ID AND T.SUBJECT_CODE IN ('20020104','20040108')
JOIN D_DATE D ON F.DATE_ID=D.ID
JOIN D_DATE DD ON D.L_YEAREND_ID=DD.ID
JOIN D_SALE_MANAGE_RELA M ON F.MANAGE_ID=M.MANAGE_ID
WHERE F.DATE_ID =?
GROUP BY F.DATE_ID,M.THIRD_BRANCH_CODE,M.THIRD_BRANCH_NAME
"""
db.cursor.execute(sql, int(stardate))
db.cursor.execute(sql1, int(stardate), int(stardate))
db.cursor.execute(sql2, int(stardate), int(stardate))
row2 = db.cursor.fetchall()
resultrow2 = []
for i in row2:
t = list(i[0:])
resultrow2.append(t)
#print resultrow2,"pub"
u_sql2 = """
UPDATE REPORT_ORG_DEP SET PUB_LAST=? ,PUB_THIS=? WHERE DATE_ID=? and ORG_CODE=?
"""
db.cursor.executemany(u_sql2, resultrow2)
db.cursor.execute(sql3, int(stardate), int(stardate))
row3 = db.cursor.fetchall()
resultrow3 = []
for i in row3:
t = list(i[0:])
resultrow3.append(t)
print resultrow3
u_sql3 = """
UPDATE REPORT_ORG_DEP SET FIN_LAST=?, FIN_THIS=? WHERE DATE_ID=? and ORG_CODE=?
"""
db.cursor.executemany(u_sql3, resultrow3)
db.conn.commit()
print stardate
stardate = int(util.daycalc(stardate, 1))
finally:
db.closeDB()
db.cursor.execute(mer_sql, Config().etldate)
db.conn.commit()
print Config().etldate, datetime.now() - d1
db.closeDB()
print "over time =", datetime.now() - d1
if __name__ == '__main__':
arglen = len(sys.argv)
print sys.argv
if arglen == 3:
stardate = int(sys.argv[1])
etldate = int(sys.argv[2])
print stardate, etldate
begin = thisdatetime.date(int(str(stardate)[0:4]),
int(str(stardate)[4:6]),
int(str(stardate)[6:8]))
end = thisdatetime.date(int(str(etldate)[0:4]), int(str(etldate)[4:6]),
int(str(etldate)[6:8]))
print begin, end
day = stardate
print day
for i in range((end - begin).days + 1):
Config().etldate = day
starrun(Config().etldate)
print "manage_id:", day, "ok"
day = util.daycalc(day, 1)
print i, day
else:
print "please input python %s yyyyMMdd yyyyMMdd" % sys.argv[0]
insert_sql = """
INSERT INTO CREDIT_BAD_HOOK(ORG_NO, MANAGER_NO, CARD_NO, ETL_DATE, FLAG)
SELECT A.ORG_NO, A.MANAGER_NO, D.CARD_NO, ?, 1
FROM ACCOUNT_HOOK A
INNER JOIN D_CREDIT_CARD D ON D.CARD_NO=A.ACCOUNT_NO AND D.PRODUCT != '0632'
INNER JOIN F_CREDIT_BAD F ON F.CRAD_ID=D.ID
LEFT JOIN CUST_HOOK_BATCH T ON T.ID=A.BATCH_ID
WHERE F.DATE_ID = ? AND T.ID IS Null AND A.TYP = '信用卡' AND A.STATUS in ( '已审批', '正常', '待审批', '预提交审批' ,'录入已审批' ) AND D.OPEN_DATE >= ?
"""
db.cursor.execute(insert_sql, etldate, etldate, old_critical_dt)
db.conn.commit()
finally:
db.closeDB()
if __name__ == '__main__':
arglen = len(sys.argv)
if arglen != 3:
print "please input python %s yyyyyMMdd yyyyMMdd " % (sys.argv[0])
else:
startdate = sys.argv[1]
enddate = sys.argv[2]
etldate = int(startdate)
while etldate <= int(enddate):
print etldate
Config().etldate = etldate
Config().stretldate = util.tostrdate(etldate)
run(etldate)
etldate = int(util.daycalc(etldate, 1))
def man_ebank_sco_new(stardate,etldate):
try:
db = util.DBConnect()
while stardate<=etldate:
u_sql0=u"""
UPDATE REPORT_MANAGER_OTHER SET MB_ADD_SCO=0,CB_ADD_SCO=0,POS_ADD_SCO=0,BAD_ADD_SCO=0,ETC_ADD_SCO=0,EPAY_ADD_SCO=0,FRAM_SCO=0,MB_PERCENT_SAL=0,PUB_PERCENT_SAL=0,ALL_SCO=0 WHERE DATE_ID=?
"""
db.cursor.execute(u_sql0,stardate)
db.conn.commit()
sql0=u"""SELECT LEFT(L_YEAREND_ID,4) FROM D_DATE WHERE ID=?"""
db.cursor.execute(sql0,stardate)
row0=db.cursor.fetchall()
last_year = row0[0][0]
#print last_year
pri_ebank = man_pri_loan_ebank(db,stardate)
#print pri_ebank
pub_loan = man_pri_loan_ebank(db,stardate)
#print pub_loan
sql1="""
SELECT R.DATE_ID,R.ORG_CODE,R.SALE_CODE,SUM((NVL(R.MB_THIS_NUM,0)-NVL(R1.MB_THIS_NUM,0))) AS 新增手机银行户数,NVL(P.TAR_SJ,0),
SUM( NVL(R.CB_THIS_NUM,0)-NVL(R1.CB_THIS_NUM,0)) AS 新增企业网银,NVL(P.TAR_WY,0),
SUM(NVL(R.POS_THIS_NUM,0)-R1.POS_THIS_NUM) AS 新拓展POS机,NVL(P.TAR_POS,0),
SUM(NVL(RMC.BAD_ALL/1000000.0,0)) AS 新增丰收贷记卡逾期本金,
SUM((NVL(ET.ETC_NUM,0))) AS 新增ETC指标得分,NVL(P.TAR_ETC,0),
SUM((NVL(R.EPAY_THIS_NUM,0)-NVL(R1.EPAY_THIS_NUM,0))) AS 新增有效丰收E支付,NVL(P.TAR_EPAY,0),
SUM((NVL(R.FARM_SERV_HIGH_NUM,0))) AS 助农服务点月活点指标达到60,SUM((NVL(R.FARM_SERVICE_LOW_NUM,0)))
FROM REPORT_MANAGER_OTHER R
JOIN ( SELECT DATE_ID, ORG_CODE,SALE_CODE,NVL((MB_THIS_NUM),0) AS MB_THIS_NUM ,NVL((CB_THIS_NUM),0) AS CB_THIS_NUM,NVL((EPAY_THIS_NUM),0) AS EPAY_THIS_NUM,
NVL((ETC_THIS_NUM),0) AS ETC_THIS_NUM,NVL(POS_THIS_NUM,0) AS POS_THIS_NUM
FROM REPORT_MANAGER_OTHER WHERE DATE_ID =(SELECT L_YEAREND_ID FROM D_DATE WHERE ID=?)
GROUP BY DATE_ID,ORG_CODE,SALE_CODE,MB_THIS_NUM,CB_THIS_NUM,EPAY_THIS_NUM,ETC_THIS_NUM,POS_THIS_NUM ) R1 ON R1.SALE_CODE=R.SALE_CODE AND R1.ORG_CODE=R.ORG_CODE
JOIN P_EBANK_NUM P ON R.ORG_CODE=P.THIRD_ORG_CODE AND R.SALE_CODE=P.MANAGER_CODE
LEFT JOIN (SELECT A.TELLER_NO,(COUNT(A.CUST_NET_NO) - COUNT(B.CUST_NET_NO)) AS ETC_NUM FROM ETC_DATA A LEFT JOIN ETC_DATA B ON A.CUST_NET_NO=B.CUST_NET_NO
AND LEFT(B.DATE_ID,4)=? WHERE LEFT(A.DATE_ID,4)=(SELECT YEAR FROM D_DATE WHERE ID=?)
GROUP BY A.TELLER_NO) ET ON ET.TELLER_NO=R.SALE_CODE
JOIN REPORT_MANAGER_CREDITCARD RMC ON RMC.SALE_CODE=R.SALE_CODE AND RMC.DATE_ID=R.DATE_ID
WHERE 1=1 AND R.DATE_ID=?
GROUP BY R.DATE_ID,R.ORG_CODE,R.SALE_CODE,P.TAR_SJ,P.TAR_WY,P.TAR_POS,RMC.BAD_ALL,P.TAR_ETC,P.TAR_EPAY
"""
db.cursor.execute(sql1,stardate,last_year,stardate,stardate)
row=db.cursor.fetchall()
#print row
"""参数"""
''' 取得手机银行 标准分，最高分，最低分'''
sql2=u"""
select h.HEADER_NAME,d.DETAIL_VALUE from T_PARA_TYPE t
join T_PARA_HEADER h on h.PARA_TYPE_ID=t.ID
join T_PARA_DETAIL d on d.PARA_HEADER_ID=h.ID
where t.TYPE_NAME='新增手机银行有效户数得分参数'
"""
db.cursor.execute(sql2.encode('utf-8'),stardate)
row2=db.cursor.fetchall()
#print row2
std_mb=max_mb=min_mb=0
for i in row2:
if i[0]=='标准分（分）':
std_mb=float(i[1])
elif i[0]=='最高分（分）':
max_mb=float(i[1])
elif i[0]=='最低分（分）':
min_mb=float(i[1])
print "shouji",std_mb,max_mb,min_mb
''' 取得企业网银 标准分，最高分，最低分'''
sql3=u"""
select h.HEADER_NAME,d.DETAIL_VALUE from T_PARA_TYPE t
join T_PARA_HEADER h on h.PARA_TYPE_ID=t.ID
join T_PARA_DETAIL d on d.PARA_HEADER_ID=h.ID
where t.TYPE_NAME='新增企业网银有效户数得分参数'
"""
db.cursor.execute(sql3.encode('utf-8'),stardate)
row3=db.cursor.fetchall()
#print row3
std_wy=max_wy=min_wy=0
for i in row3:
if i[0]=='标准分':
std_wy=float(i[1])
elif i[0]=='最高分':
max_wy=float(i[1])
elif i[0]=='最低分':
min_wy=float(i[1])
print std_wy,max_wy,min_wy
''' 取得pos机 标准分，最高分，最低分'''
sql4=u"""
select h.HEADER_NAME,d.DETAIL_VALUE from T_PARA_TYPE t
join T_PARA_HEADER h on h.PARA_TYPE_ID=t.ID
join T_PARA_DETAIL d on d.PARA_HEADER_ID=h.ID
where t.TYPE_NAME='新拓展pos机得分参数'
"""
db.cursor.execute(sql4.encode('utf-8'),stardate)
row4=db.cursor.fetchall()
#print row4
std_pos=max_pos=min_pos=0
for i in row4:
if i[0]=='标准分':
std_pos=float(i[1])
elif i[0]=='最高分':
max_pos=float(i[1])
elif i[0]=='最低分':
min_pos=float(i[1])
print std_pos,max_pos,min_pos
''' 取得新增丰收贷记卡逾期本金得分 标准分，最高扣分'''
sql5=u"""
SELECT H.HEADER_NAME,D.DETAIL_VALUE FROM T_PARA_TYPE T
JOIN T_PARA_HEADER H ON H.PARA_TYPE_ID=T.ID
JOIN T_PARA_DETAIL D ON D.PARA_HEADER_ID=H.ID
WHERE T.TYPE_NAME LIKE '新增丰收贷记卡逾期本金得分参数'
"""
db.cursor.execute(sql5.encode('utf-8'),stardate)
row5=db.cursor.fetchall()
#print row5
std_card=max_card=0
for i in row5:
if i[0]=='每万元扣分':
std_card=float(i[1])
elif i[0]=='最高分':
max_card=float(i[1])
print std_card,max_card
''' 取得新增ETC指标得分'''
sql6=u"""
SELECT H.HEADER_NAME,D.DETAIL_VALUE FROM T_PARA_TYPE T
JOIN T_PARA_HEADER H ON H.PARA_TYPE_ID=T.ID
JOIN T_PARA_DETAIL D ON D.PARA_HEADER_ID=H.ID
WHERE T.TYPE_NAME LIKE '新增ETC得分参数'
"""
db.cursor.execute(sql6.encode('utf-8'),stardate)
row6=db.cursor.fetchall()
#print row6
std_etc=max_etc=min_etc=0
for i in row6:
if i[0]=='标准分':
std_etc=float(i[1])
elif i[0]=='最高分':
max_etc=float(i[1])
elif i[0]=='最低分':
min_etc=float(i[1])
print std_etc,max_etc,min_etc
''' 取得新增有效e支付得分'''
sql7=u"""
SELECT H.HEADER_NAME,D.DETAIL_VALUE FROM T_PARA_TYPE T
JOIN T_PARA_HEADER H ON H.PARA_TYPE_ID=T.ID
JOIN T_PARA_DETAIL D ON D.PARA_HEADER_ID=H.ID
WHERE T.TYPE_NAME LIKE '新增有效丰收e支付得分参数'
"""
db.cursor.execute(sql7.encode('utf-8'),stardate)
row7=db.cursor.fetchall()
#print row7
std_ep=max_ep=min_ep=0
for i in row7:
if i[0]=='标准分':
std_ep=float(i[1])
elif i[0]=='最高分':
max_ep=float(i[1])
elif i[0]=='最低分':
min_ep=float(i[1])
print std_ep,max_ep,min_ep
''' 取得助农服务点月活点指标得分参数'''
sql8=u"""
SELECT H.HEADER_NAME,D.DETAIL_VALUE FROM T_PARA_TYPE T
JOIN T_PARA_HEADER H ON H.PARA_TYPE_ID=T.ID
JOIN T_PARA_DETAIL D ON D.PARA_HEADER_ID=H.ID
WHERE T.TYPE_NAME LIKE '助农服务点月活点率得分参数'
"""
db.cursor.execute(sql8.encode('utf-8'),stardate)
row8=db.cursor.fetchall()
#print row8
std_zn=max_zn=min_zn=0
for i in row8:
if i[0]=='标准分':
std_zn=float(i[1])
elif i[0]=='最高分':
max_zn=float(i[1])
elif i[0]=='最低分':
min_zn=float(i[1])
print std_zn,max_zn,min_zn
''' 取得助农服务点月活点指标限制参数'''
sql9=u"""
SELECT H.HEADER_NAME,D.DETAIL_VALUE FROM T_PARA_TYPE T
JOIN T_PARA_HEADER H ON H.PARA_TYPE_ID=T.ID
JOIN T_PARA_DETAIL D ON D.PARA_HEADER_ID=H.ID
WHERE T.TYPE_NAME LIKE '助农服务点月活点指标限制参数'
"""
db.cursor.execute(sql9.encode('utf-8'),stardate)
row9=db.cursor.fetchall()
#print row9
plan=0
for i in row9:
if i[0]=='完成率标准(%)':
plan=float(i[1])
print plan
''' 取得对私类贷款客户有效手机银行绑定率参数'''
sql10=u"""
SELECT H.HEADER_NAME,D.DETAIL_VALUE FROM T_PARA_TYPE T
JOIN T_PARA_HEADER H ON H.PARA_TYPE_ID=T.ID
JOIN T_PARA_DETAIL D ON D.PARA_HEADER_ID=H.ID
WHERE T.TYPE_NAME LIKE '对私类贷款客户有效手机银行绑定率参数'
"""
db.cursor.execute(sql10.encode('utf-8'))
row10=db.cursor.fetchall()
#print row10
std_pri=max_pri=min_pri=plan_pri=per_pri=0
for i in row10:
if i[0]=='标准分':
std_pri=float(i[1])
elif i[0]=='最高分':
max_pri=float(i[1])
elif i[0]=='最低分':
min_pri=float(i[1])
elif i[0]=='贷款客户有效手机银行绑定率':
plan_pri=float(i[1])
elif i[0]=='每超(减)百分点奖(扣)分':
per_pri=float(i[1])
print std_pri,max_pri,min_pri,plan_pri,per_pri
''' 取得公司类贷款客户有效网上银行绑定率参数'''
sql11=u"""
SELECT H.HEADER_NAME,D.DETAIL_VALUE FROM T_PARA_TYPE T
JOIN T_PARA_HEADER H ON H.PARA_TYPE_ID=T.ID
JOIN T_PARA_DETAIL D ON D.PARA_HEADER_ID=H.ID
WHERE T.TYPE_NAME LIKE '公司类贷款客户有效网上银行绑定率参数'
"""
db.cursor.execute(sql11.encode('utf-8'))
row11=db.cursor.fetchall()
#print row11
std_pub=max_pub=min_pub=plan_pub=per_pub=0
for i in row11:
if i[0]=='标准分':
std_pub=float(i[1])
elif i[0]=='最高分':
max_pub=float(i[1])
elif i[0]=='最低分':
min_pub=float(i[1])
elif i[0]=='公司类贷款客户有效网上银行绑定率':
plan_pub=float(i[1])
elif i[0]=='每超(减)百分点奖(扣)分':
per_pub=float(i[1])
print std_pub,max_pub,min_pub,plan_pub,per_pub
sql12=u"""
SELECT H.HEADER_NAME,D.DETAIL_VALUE FROM T_PARA_TYPE T
JOIN T_PARA_HEADER H ON H.PARA_TYPE_ID=T.ID
JOIN T_PARA_DETAIL D ON D.PARA_HEADER_ID=H.ID
WHERE T.TYPE_NAME LIKE '纯公司类贷款客户经理'
ORDER BY D.DETAIL_VALUE DESC
"""
db.cursor.execute(sql12.encode('utf-8'),etldate)
row12=db.cursor.fetchall()
manage_list=[]
for i in row12:
if i[0] == '客户经理':
manage_list.append(i[1])
#print manage_list
i=0
resultrow=[]
if(len(row)>0):
while True:
h1=row[i][0] #date_id
h2=row[i][1] #ORG_CODE
h3=row[i][2] #SALE_CODE
h4=row[i][3] #MB_THIS_NUM手机银行
if row[i][4] == 0: #TAR_SJ为空，则取0
score = 0
else:
score=(h4)/float(target_percent(row[i][4],stardate))*std_mb
if score>max_mb:score=max_mb
if score<min_mb:score=min_mb
qm1=int(score*100) #手机银行得分
h5=row[i][5]
if row[i][6] == 0:
scorew= 0
else:
scorew=(h5)/float(target_percent(row[i][6],stardate))*std_wy
if scorew>max_wy:scorew=max_wy
if scorew<min_wy:scorew=min_wy
qm2=int(scorew*100) #企业网银得分
h6=row[i][7]
if row[i][8] == 0:
scorep = 0
else :
scorep=(h6)/float(target_percent(row[i][8],stardate))*std_pos
if scorep>max_pos:scorep=max_pos
if scorep<min_pos:scorep=min_pos
qm3=int(scorep*100) #新拓展pos机得分
#print "**********",h3,h6,qm2,row[i][8]
h7=row[i][9]
scored = 0
if h7 == 0:
scored = 0
else:
scored = 0-h7*std_card
if scored<max_card:scored=max_card
qm4=int(scored*100) #新增逾期丰收贷记卡逾期本金得分
h8=row[i][10]
if row[i][11] == 0:
scoree = 0
else :
scoree = (h8)/float(target_percent(row[i][11],stardate))*std_etc
if scoree > max_etc:scoree=max_etc
if scoree < min_etc:scoree=min_etc
qm5=int(scoree*100) #新增ETC指标得分
h9=row[i][12]
if row[i][13] == 0:
scorep = 0
else :
scorep = (h9)/float(target_percent(row[i][13],stardate))*std_ep
if scorep > max_ep :scorep=max_ep
if scorep < min_ep :scorep=min_ep
qm6=int(scorep*100) #新增有效丰收e支付得分
h10=row[i][14]
h11=row[i][15]
if int(h11) != 0:
#print h10,h11
scorzn = 10*((float(h10)/(float(h10)+float(h11))*100-plan))/100 + std_zn
#print scorzn,h10/h11,h3
#print h11,scorzn
else:
scorzn = 0
if scorzn>max_zn:scorzn=max_zn
if scorzn<min_zn:scorzn=min_zn
#print h3,h10,h11,scorzn,std_zn,max_zn
qm7=int(scorzn*100) #助农服务点月平均活点率得分
mb_percent = pri_ebank.get((h3+h2),plan_pri/100.00)
scorsj = 100 * per_pri *(mb_percent - plan_pri/100.00) + std_pri
if scorsj>max_pri:scorsj=max_pri
if scorsj<min_pri:scorsj=min_pri
qm8=int(scorsj*100) #对私类客户贷款有效手机银行绑定率
#print mb_percent,scorsj,max_pri,min_pri,h3+h2,qm8
qm9=0
if h3 in manage_list:
pu_percent = pub_loan.get((h3+h2),plan_pub/100.00)
scorpu = 100 * per_pub *(pu_percent - plan_pub/100.00) + std_pub
if scorpu>max_pub:scorpu=max_pub
if scorpu<min_pub:scorpu=min_pub
qm9=int(scorpu*100) #公司类贷款客户有效网上银行绑定率
#print pu_percent,scorpu,max_pub,min_pub,h3+h2,qm9
qm_add = 0
sql_add = u"""
SELECT NVL(ADD_POINTS,0) FROM MAN_EBANK_ADD_POINTS WHERE KYEAR=? AND ORG_NO=? AND USER_NAME=?
"""
db.cursor.execute(sql_add.encode('utf-8'),str(stardate)[0:4],h2,h3)
row_add=db.cursor.fetchall()
#print row_add
if len(row_add) == 1:
qm_add = row_add[0]
#print qm_add
qm10 = qm1 + qm2 + qm3 + qm4 + qm5 + qm7 + qm8 + qm9
resultrow.append((qm1,qm2,qm3,qm4,qm5,qm6,qm7,qm8,qm9,qm10,h1,h2,h3))
#print resultrow
i=i+1
if i>=len(row):
break
''' 更新报表'''
u_sql=u"""
UPDATE REPORT_MANAGER_OTHER SET MB_ADD_SCO=?,CB_ADD_SCO=?,POS_ADD_SCO=?,BAD_ADD_SCO=?,ETC_ADD_SCO=?,EPAY_ADD_SCO=?,FRAM_SCO=?,MB_PERCENT_SAL=?,PUB_PERCENT_SAL=?,ALL_SCO=?,FLAG=1 WHERE DATE_ID=? and ORG_CODE=? and SALE_CODE=?
"""
db.cursor.executemany(u_sql,resultrow)
db.conn.commit()
print stardate,"完成"
stardate=int(util.daycalc(stardate,1))
finally :
db.closeDB()
