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 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 starrun(etldate):
if etldate != 20170101: #第一天，手动生成
mergeall_ccrdfile(etldate)
db = DBConnect()
try:
db.cursor.execute(
"delete from F_CREDIT_MPUR_20161031 where date_id = ?", (etldate))
db.conn.commit()
finally:
db.closeDB()
sm = StarManage(1, [])
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
StarBase().files2fact2(CcrdMpur(), sm)
sm.finish()
def starrun(etldate):
if etldate != 20170101: #第一天，手动生成
mergeall_ccrdfile(etldate)
db = DBConnect()
try:
db.cursor.execute("delete from F_TRANSACTION f where date_id= ?",
(etldate))
db.conn.commit()
finally:
db.closeDB()
acct = CCRDACCT(etldate).loadfile2dict()
def card_key(row):
k = row[0].replace(".", "").strip()
return k
cards = CCRDCARD(etldate, "ALL").loadfile2dict2([0], card_key)
manager = multiprocessing.Manager()
dg = DimAccountGid()
dim1 = DimAccount2()
dim1.start()
dim2 = DimTransAction()
dim3 = DimTransActionType()
#遍历acct
sm = StarManage(2, [dim1, dim2, dim3, dg])
sm.manager = manager
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
for k in acct:
row = acct[k]
k = k.replace(".", "").strip()
card = cards.get(k)
ac = None
sn = 0
if card is not None:
for c in card:
if ac is None: ac = c
if c["MASTER_NBR"] == c['CARD_NBR'] and int(
Decimal(c["ISSUE_NBR"])) >= sn:
ac = c
if ac is not None:
row["CARD_NO"] = ac["MASTER_NBR"]
#row["CST_NAME"] = row["CST_NAME"].decode("gbk").encode("utf8").strip()
#row["ACCOUNT_NAME"] = row["CST_NAME"].decode("gbk").encode("utf8").strip()
dim1.find_dim_id_by_ccrd(row)
StarBase().files2fact2(Jorj(), sm)
sm.finish()
dim1.finish()
def busi_hook(etldate):
dg = DimAccountGid()
dim1 = DimAccountNew()
dim1.start()
sm = StarManage(1, [dg, dim1])
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
'''
info("run loan_hook star")
StarBase().files2fact(Loan_Contract(), sm)
sm.restart_fact_process()
info("run loan_hook end")
info("run deposit_hook")
StarBase().files2fact(Deposit_hq(), sm)
sm.restart_fact_process()
info("run deposit_dq")
StarBase().files2fact(Deposit_dq(), sm)
sm.restart_fact_process()
info("run deposit_hook end")
info("run ebank_hook star")
StarBase().files2fact(Ebank_kjzf(), sm)
sm.restart_fact_process()
StarBase().files2fact(Ebank_epay(), sm)
sm.restart_fact_process()
StarBase().files2fact(Ebank_zfb(), sm)
sm.restart_fact_process()
StarBase().files2fact(Ebank_pb(), sm)
sm.restart_fact_process()
StarBase().files2fact(Ebank_mb(), sm)
sm.restart_fact_process()
StarBase().files2fact(Ebank_cb(), sm)
sm.restart_fact_process()
#StarBase().files2fact(Ebank_etc(), sm)
#sm.restart_fact_process()
info("run finances_hook")
StarBase().files2fact(Finances(), sm)
sm.restart_fact_process()
info("run stock_hook")
StarBase().files2fact(Stock(), sm)
sm.restart_fact_process()
info("run insurance_hook")
StarBase().files2fact(Insurance(), sm)
sm.restart_fact_process()
info("run creditcard_hook")
StarBase().files2fact(Creditcard(), sm)
sm.restart_fact_process()
'''
info("run ParentAccount")
StarBase().files2fact(ParentAccount(), sm)
sm.restart_fact_process()
sm.finish()
def account_price_by_org(etldate, dh, org_no, hqsum, hq_agg_rates, aggr_rate,
close_accounts, last_close_accounts, lnew, pr, dqcl,
dqcldict, tran_deps):
prices = query_para_price(etldate)
last_day = int(daycalc(etldate, -1))
if org_no is None:
sm = StarManage(2, [])
else:
sm = StarManage(10, [])
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
rows = query_row(etldate, dh, org_no)
last_rows = {
row["ACCOUNT_ID"]: row
for row in query_row(last_day, dh, org_no)
}
db = util.DBConnect()
for row in rows:
if row["INTEREST_TYPE"] == '不计息':
continue
if row["TERM"] == 96 and row["START_INTEREST"] > 20020220:
continue
if row["CLOSE_DATE"] == etldate and last_row is None and row[
"ACCOUNT_CLASS"] == 'D':
#当天销户，昨日没有，说明是当日新开即销户,无需计息
continue
if row["START_INTEREST"] == 18991231:
row["START_INTEREST"] = row["OPEN_DATE"]
last_row = last_rows.get(row["ACCOUNT_ID"])
'''
if last_row is None and row["OPEN_DATE"] != etldate and etldate != FIRSTDATE:
lds = [ r for r in query_row(last_day, dh, org_no, db,row["ACCOUNT_ID"] ) ]
if len(lds) > 0 : last_row = lds[0]
'''
if (row["OPEN_DATE"] == row["CLOSE_DATE"] and row["OPEN_DATE"] !=
18991231): #or row["START_INTEREST"] == row["CLOSE_DATE"]:
put_fact_data2(sm, row, last_row, prices)
else:
ak = row["ACCOUNT_NO"] + "-" + row["CCY"] + "-" + row[
"CASH_TP"].split("-")[0]
nrt = hq_agg_rates.get(ak)
if nrt is None:
close_account, td0, agg_rate, hqinterest, hq_agg_rate = get_price_args(
row, last_row, hqsum, hq_agg_rates, aggr_rate,
close_accounts, last_close_accounts, tran_deps)
la = get_losernew_account(row, lnew)
laflag = True if la is not None and la[
"AP09LSTP"] == '1' else False
td = TimeDepositInterest(etldate, row, last_row, pr, dqcl,
dqcldict, close_account, laflag, td0,
agg_rate, hqinterest, hq_agg_rate)
rt = td.calc_interest()
else:
rt = nrt
ak = row["ACCOUNT_NO"] + "-" + row["CCY"] + "-" + row[
"CASH_TP"].split("-")[0]
put_fact_data(sm, rt, row, last_row, prices)
sm.finish()
db.closeDB()
def starrun(etldate):
db = DBConnect()
try:
db.cursor.execute("delete from MB_PB_ACCINF where date_id = ?",
(etldate))
db.conn.commit()
db.cursor.execute(
"delete from ODS_EBILLS_PA_QUOTEPRICE where date_id = ?",
(etldate))
db.conn.commit()
finally:
db.closeDB()
sm = StarManage(1, [])
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
StarBase().files2fact2(MBPBACCINF(), sm)
StarBase().files2fact2(PA_QUOTEPRICE(), sm)
sm.finish()
db = DBConnect()
try:
usql = """
merge into MB_PB_ACCINF f
using (select distinct account_no,cst_id,cst_no from d_account aa where aa.account_class in ('定期分户','活期分户') ) a
on f.date_id = ? and a.account_no =f.MAIF_ACCNO
when matched then update set f.cst_no=a.cst_no,f.cst_id=a.cst_id
"""
db.cursor.execute(usql, (etldate))
db.conn.commit()
finally:
db.closeDB()
def starrun(etldate):
sm = StarManage([])
StarBase().files2fact(Deposit_hq(), sm)
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_dep_price(etldate):
prices = query_para_price(etldate)
db = util.DBConnect()
try:
sql2 = """
select f.id,t.TERM,f.ACCOUNT_CLASS,t.PRODUCT,t.BUSI_CODE from f_account_balance_back f
inner join d_account_type_extend t on t.id = f.ACCOUNT_TYPE_ID
where f.DATE_ID = ?
-- and f.id in ( 2001759406,2000009510, 2000009514, 2000009531, 2000009533, 2000009657, 2000009662, 2000009684, 2000009688, 2000010144, 2000010146
-- ,2001951568, 2001859317, 2001859334, 2001859336, 2001859339, 2001859637, 2001859930, 2001859935, 2001860038, 2001860136
-- ,2001910408
-- )
"""
usql= """
update f_account_balance_back f
set f.price_id = ?
where f.id = ?
"""
db.cursor.execute(sql2, etldate )
row = db.cursor.fetchone()
datas = []
sm = StarManage(2,[ DimStarPrice() ])
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
desc = db.cursor.description
ld = len(desc)
while row:
data = {}
for x in range(ld):
data[ desc[x][0] ] = row[x]
dim_id = find_price_id(sm, prices, data)
datas.append( (dim_id,row[0]) )
row = db.cursor.fetchone()
sm.finish()
db.cursor.executemany(usql, datas )
db.conn.commit()
finally:
db.closeDB()
def starrun(etldate):
db = DBConnect()
try:
db.cursor.execute(
"delete from F_EBILLS_BU_TRANSACTIONINFO f where date_id= ?",
(etldate))
db.conn.commit()
finally:
db.closeDB()
manager = multiprocessing.Manager()
dim1 = DimBuTransaction()
dim2 = DimEbillsTranType()
sm = StarManage(1, [dim1, dim2])
sm.manager = manager
sm.start_dim_process()
sm.start_fact_process()
sm.setDimQueue()
sm.start()
StarBase().files2fact2(BuTransaction(), sm)
sm.finish()
snapshot(etldate)