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])