def insert_dhs(db, etldate, classify):
db1 = util.DBConnect()
fh_sql = """
select A.ORG_CODE,A.CUST_NO
from D_CONTRACT A
where A.CONTRACT_KEY = ?
"""
del_sql = """
delete from T_JGC_AZ_DHS where DATE_ID = ? and CONTRACT_CLASSIFY = ?
"""
insert_sql = """
insert into T_JGC_AZ_DHS(DATE_ID,CUST_NO,ORG_CODE,CONTRACT_KEY,TRANTIME,CONTRACT_CLASSIFY)
values(?,?,?,?,?,?)
"""
if classify == 'A':
mx_data = get_android_mx_data(db, etldate)
elif classify == 'I':
mx_data = get_iphone_mx_data(db, etldate)
else:
pass
db1.cursor.execute(del_sql, etldate, classify)
for key, data in mx_data.items():
db.cursor.execute(fh_sql, key)
row = db.cursor.fetchone()
db1.cursor.execute(insert_sql, etldate, row[1].strip(), row[0].strip(),
key, data, classify)
db1.conn.commit()
db1.closeDB()
def all_move_account_rate(db, etldate):
db1 = util.DBConnect()
sel_sql = """
select distinct org_code from M_EBANK_INFO
"""
db.cursor.execute(sel_sql)
org_code = []
row = db.cursor.fetchone()
while row:
org_code.append(row[0].strip())
row = db.cursor.fetchone()
insql = u"""
insert into t_jgc_jgzb(date_id,org_code,zblx,je1,je2) values(?,?,?,?,?)
"""
delete_jgzb(db, etldate, u'苹果手机动户率'.encode('gb2312'))
iphone_live_num = clac_rate(db, etldate, 'I')
iphone_num = get_phone_num(db, etldate, 'I')
android_phone_live_num = clac_rate(db, etldate, 'A')
android_phone_num = get_phone_num(db, etldate, 'A')
all_phone_live_num = {}
all_phone_num = {}
for key in org_code:
all_phone_num[key] = iphone_num.get(key, 0) + android_phone_num.get(
key, 0)
all_phone_live_num[key] = iphone_live_num.get(
key, 0) + android_phone_live_num.get(key, 0)
for key, value in all_phone_live_num.items():
org_code = key
je1 = value * 1.0 / all_phone_num.get(org_code)
db1.cursor.execute(insql.encode('gb2312'), etldate, org_code,
u'总手机动户率'.encode('gb2312'), je1, 0.0)
db1.conn.commit()
db1.closeDB()
def phone_move_account_rate(etldate):
try:
db = util.DBConnect()
android_move_account_rate(db, etldate)
iphone_move_account_rate(db, etldate)
all_move_account_rate(db, etldate)
finally:
db.closeDB()
def measure_clacs(etldate):
try:
db = util.DBConnect()
clac_contract_detail(db, etldate)
clac_contract_scrore(db, etldate)
db.conn.commit()
finally:
db.closeDB()
def large_loss(etldate):
try:
db = util.DBConnect()
delete_liushi(db, etldate)
large_ck_loss(db, etldate)
large_dk_loss(db, etldate)
db.conn.commit()
finally:
db.closeDB()
def iphone_move_account_rate(db, etldate):
db1 = util.DBConnect()
insql = u"""
insert into t_jgc_jgzb(date_id,org_code,zblx,je1,je2) values(?,?,?,?,?)
"""
delete_jgzb(db, etldate, u'苹果手机动户率'.encode('gb2312'))
phone_live_num = clac_rate(db, etldate, 'I')
phone_num = get_phone_num(db, etldate, 'I')
for key, value in phone_live_num.items():
org_code = key
je1 = value * 1.0 / phone_num.get(org_code)
db1.cursor.execute(insql.encode('gb2312'), etldate, org_code,
u'苹果手机动户率'.encode('gb2312'), je1, 0.0)
db1.conn.commit()
db1.closeDB()
def clac_contract_scrore(db, etldate):
db1 = util.DBConnect()
sql = """
select sum(d.score),p.id,def.PEI_FREQ from PE_CONTRACT_DETAIL d
inner join PE_CONTRACT p on d.contract_id = p.id
inner join PE_PEI_DEF def on def.pei_id = d.pe_pei_id
group by p.id,def.PEI_FREQ
"""
upsql = """
update PE_CONTRACT set score = ?,date = ? where id = ?
"""
db.cursor.execute(sql)
row = db.cursor.fetchone()
while row:
score = row[0] if row[0] is not None else 0
date = util.tostrdate(etldate)
pe_id = row[1]
db1.cursor.execute(upsql, score, date, pe_id)
row = db.cursor.fetchone()
db1.conn.commit()
db1.closeDB()
def measure_calc(measurename, org_code, etldate, detail_id, pei_freq, meatype):
db = util.DBConnect()
try:
if meatype == u'定性'.encode('gb2312'):
(score, fact) = mutil.qualitative(db, etldate, detail_id)
else:
fun = measures.get(measurename.decode('gb2312'))
if fun is None:
raise Exception(u"绩效指标未定义:%s" % (measurename.decode('gb2312')))
if pei_freq == u'年'.encode('gb2312'):
(score, fact) = fun(db, org_code, etldate, detail_id, 'year')
else:
(score, fact) = fun(db, org_code, etldate, detail_id,
'quarter')
usql = " update PE_CONTRACT_DETAIL d set d.score = ?,d.fact= ? where id = ?"
if (fact == False and isinstance(fact, bool)) or fact == None or (
score == False and isinstance(score, bool)) or score == None:
db.cursor.execute(usql, 0.0, 0.0, detail_id)
else:
db.cursor.execute(usql, score, fact, detail_id)
db.conn.commit()
finally:
db.closeDB()
def balancesql(date, ZBLX, org_code):
db = util.DBConnect()
sql = "SELECT * FROM t_jgc_jgzb WHERE DATE_ID = ? AND ZBLX = ? AND ORG_CODE = ? "
db.cursor.execute(sql, date, ZBLX.encode('gb2312'), org_code)
row = db.cursor.fetchone()
return row
def __init__(self, etldate=None):
self.db = util.DBConnect()
self.etldate = etldate
self.stretldate = util.tostrdate(self.etldate)
self.para = util.get_common_parameter(self.db)
if etldate is not None: self.para['TJRQ'] = etldate
def __init__(self, etldate):
self.db = util.DBConnect()
self.strdate = str(etldate[0:4]) + "-" + str(etldate[4:6]) + "-" + str(
etldate[6:8])
