def dealSql():
try:
dblist = connectDb.getConnectInfo()
dbconn = connectDb.connectDb(dblist)
dbconncr = dbconn.cursor()
#业务类型为1、2、6、7(告警类) 3、8(采集类)
dbconncr.execute("select * from monitor.t_mot_conf a where a.business_type in('1','2','6','7','3','8','13')")
#dbconncr.execute("select * from monitor.t_mot_conf a where a.business_type in('2')")
result = dbconncr.fetchall()
for confArray in result:
#要执行的SQL是预警类
if confArray[9] == '1':
if confArray[2] in ["SQL00", "SQL01"]:
sql0(confArray[1], dbconncr, confArray[4], confArray[7], confArray[0], confArray[2], confArray[3])
elif confArray[2] in ["SQL10", "SQL11"]:
startTimeInterValList = getTimeInterval.getInterVal(confArray[5], dbconncr)
for startTimeIndex in range(len(startTimeInterValList)):
sql1(confArray[1], dbconncr, startTimeInterValList[startTimeIndex][0], confArray[5],
confArray[4], confArray[0], confArray[7], confArray[2], confArray[3], startTimeInterValList[startTimeIndex][1], dbconn)
else:
with open(r'error.log','a') as error_log:
print >> error_log,('%s %s $s' % (time.ctime(), "'M_ID:'" + confArray[0], 'SQL Tpye Error:'))
#信息采集
else:
if confArray[2] == "SQL1":
startTimeInterValList = getTimeInterval.getInterVal(confArray[5], dbconncr)
for startTimeIndex in range(len(startTimeInterValList)):
dealCollectInfo.sql1(confArray[1], dbconncr, startTimeInterValList[startTimeIndex][0], confArray[5],
confArray[0], startTimeInterValList[startTimeIndex][1], dbconn)
connectDb.closeConnect(dbconn)
except Exception,e:
with open(r'error.log','a') as error_log:
print >> error_log,('%s %s %s' % (time.ctime(),'Deal Data Faild:',e))
def file_check(phoneNumList,role_name):
try:
dblist = connectDb.getConnectInfo()
dbconn = connectDb.connectDb(dblist[0])
cursor = dbconn.cursor()
cursor.execute(sql)
log_tables=cursor.fetchall()
log_list = []
for log_table in log_tables:
cursor.execute(check_sql % (log_table))
check_result=cursor.fetchall()
check_list=[]
for x in check_result:
y=x[0].split("/")
check_list.append(y[2])
check_list=set(check_list)
if (len(check_list) <= 1):
#print "S"
pass
else:
#生成短信告警信息
log_list.append('check_time:%s,bill.%s be exception: %s, please check!!!!'%(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),log_table,check_list))
cursor.close()
connectDb.closeConnect(dbconn)
#是否发短信
if (len(log_list) < 1):
pass
else:
#短信发送
message.message_send(log_list,phoneNumList)
except Exception,e:
with open(r'error.log','a') as error_log:
print >> error_log,('%s %s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),' Excute ft_info_data SQL Faild:',e))
def dealSql():
try:
dblist = connectDb.getConnectInfo()
#VOP库连接
dbconn = connectDb.connectDb(dblist[0])
dbconncr = dbconn.cursor()
#业务类型为12(告警类)
dbconncr.execute("select * from monitor.t_mot_conf a where a.business_type = '12' and not exists(select 1 from monitor.t_mot_noacq_plan t where t.acq_bg_time < to_char(sysdate,'hh24miss') and t.acq_ed_time > to_char(sysdate,'hh24miss') and a.m_id = t.m_id)")
result = dbconncr.fetchall()
for confArray in result:
#要执行的SQL是预警类
if confArray[9] == '1':
if confArray[2] in ["SQL00", "SQL01"]:
sql0(confArray[1], dbconncr, confArray[4], confArray[7], confArray[0], confArray[2], confArray[3], confArray[5], dbconn)
elif confArray[2] in ["SQL10", "SQL11"]:
startTimeInterValList = getTimeInterval.getInterVal(confArray[5], dbconncr)
for startTimeIndex in range(len(startTimeInterValList)):
sql1(confArray[1], dbconncr, startTimeInterValList[startTimeIndex][0], confArray[5],
confArray[4], confArray[0], confArray[7], confArray[2], confArray[3], startTimeInterValList[startTimeIndex][1], dbconn)
else:
with open(r'error.log','a') as error_log:
print >> error_log,('%s %s $s' % (time.ctime(), "'M_ID:'" + confArray[0], 'SQL Tpye Error:'))
#信息采集
else:
if confArray[2] == "SQL1":
startTimeInterValList = getTimeInterval.getInterVal(confArray[5], dbconncr)
for startTimeIndex in range(len(startTimeInterValList)):
dealCollectInfo.sql1(confArray[1], dbconncr, startTimeInterValList[startTimeIndex][0], confArray[5],
confArray[0], startTimeInterValList[startTimeIndex][1], dbconn)
connectDb.closeConnect(dbconn)
except Exception,e:
with open(r'error.log','a') as error_log:
print >> error_log,('%s %s %s' % (time.ctime(),'Deal Data Faild:',e))
def exeProcedure(sql):
#try:
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
Res=connectDb.getSQLrst(cursor)
connectDb.closeCursor(cursor)
connectDb.closeConnect(dbconn)
print Res
return Res
def execute_query(sqllist):
try:
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
for sql in sqllist:
connectDb.executeSQL(cursor,sql)
result=connectDb.getSQLrst(cursor)
connectDb.closeCursor(cursor)
connectDb.closeConnect(dbconn)
query_result=result[0][0]
#print query_result,type(query_result)
return query_result
except Exception,e:
query_err='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute_query falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(query_err,e))
return query_err
def get_dbInfo(sql):
try:
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
result=connectDb.getSQLrst(cursor)
connectDb.closeCursor(cursor)
connectDb.closeConnect(dbconn)
infodict={}
for res in result:
if infodict.has_key(res[0]):
infodict[res[0]].append(res[1:])
else:
infodict[res[0]]=[res[1:]]
#print infodict
return infodict
except Exception,e:
geterr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute get_dbInfo falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(geterr,e))
def getSqlRes(sql):
try:
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
cols=[]
for col in cursor.description:
cols.append(col[0])
#print cols
result=connectDb.getSQLrst(cursor)
reslist=[]
for res in result:
reslist.append(list(res))
connectDb.closeCursor(cursor)
connectDb.closeConnect(dbconn)
#print (cols,reslist)
return (cols,reslist)
except Exception,e:
getReserr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute getSqlRes falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(getReserr,e))
def execute_commitsql(sqllist):
try:
reslist=[]
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
for sql in sqllist:
connectDb.executeSQL(cursor,sql)
rowsaffected = cursor.rowcount
reslist.append(rowsaffected)
connectDb.sqlcommit(dbconn)
update_result=sum(reslist)
#print reslist
#print update_result,type(update_result)
connectDb.closeCursor(cursor)
connectDb.closeConnect(dbconn)
return update_result
except Exception,e:
update_err='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute_commitsql falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(update_err,e))
return update_err
def insertlog(phoneNumList,role_name,M_INTERVAL):
try:
dblist = connectDb.getConnectInfo()
#VOP库连接
dbconn = connectDb.connectDb(dblist[0])
dbconncr = dbconn.cursor()
if compareExecTime.compareExecTime(M_INTERVAL,role_name,dbconncr) == True :
# print "开始执行命令"
dbconncr.execute("select monitor.billpro_fk_idseq.nextval from dual")
result = dbconncr.fetchall()
SEQ_FK = result[0][0]
#采集数统计
rstlist = runCmd.runcmd("zhcjprod")
cjcount = 0
for result in rstlist:
#print result
cjcount += int(result.split(";")[1])
rstlistA = runCmd.runcmd("acquire_A")
rstlistB = runCmd.runcmd("acquire_B")
acquirelist = rstlistA + rstlistB
#分发数统计并入表
for acquire in acquirelist:
mvno = acquire.split(";")[0].split("/")[5]
prod = acquire.split(";")[0].split("/")[6]
count = int(acquire.split(";")[1])
# print 'MVNO:%s,PROD:%s,COUNT:%s'%(mvno,prod,count)
dbconncr.execute("insert into monitor.i_mv_billpro(MVNO, PROD, FILE_COUNT, INSERT_TIME, FLAG,SEQ_FK) values ('" + str(mvno) + "','" + str(prod) +"','" + str(count) + "',sysdate,'0','" + str(SEQ_FK) +"')")
dbconncr.execute("insert into monitor.t_mot_billpro_log(BILLPRO_ID, CJFILE_COUNT, MVNO, YY_COUNT, SJ_COUNT, DX_COUNT, ZZ_COUNT, INSERT_TIME, SEQ_FK, FLAG) select monitor.billpro_idseq.nextval,'" + str(cjcount) + "',mvno,YY,SJ,DX,ZZ,sysdate,'" + str(SEQ_FK) +"','0' from (select mvno,sum(decode(prod,'YY',file_count,0)) YY,sum(decode(prod,'SJ',file_count,0)) SJ,sum(decode(prod,'DX',file_count,0)) DX,sum(decode(prod,'ZZ',file_count,0)) ZZ from monitor.i_mv_billpro where flag = '0' and SEQ_FK = '" + str(SEQ_FK) +"' group by mvno)")
dbconncr.execute("delete from monitor.i_mv_billpro where flag = '0' and seq_fk = '"+ str(SEQ_FK) +"'")
#入短信表
for phoneNum in phoneNumList:
dbconncr.execute("""insert into monitor.sms_send_routine (SMS_ID, TEL_NUM, ROLE_NAME, CONTENT, STATUS, INSERT_TIME, SEND_TIME)
select monitor.sms_send_routine_pk_seq.nextval,'""" + str(phoneNum) + """','bill_infor',CONTENT, '0', sysdate, null from (select sum(t.cjfile_count)/count(1) || '个文件未采集，话单分发-情况：'||to_char(wm_concat('【' || mb.mvno_business_name || ':YY'|| t.yy_count ||',DX'|| t.dx_count ||',SJ'||t.sj_count || '】')) CONTENT from monitor.t_mot_billpro_log t,dbvop.mvno_business mb
where t.mvno = mb.mvno_business_mark
and t.flag = '0'
and t.seq_fk = '"""+ str(SEQ_FK) +"""'
group by t.seq_fk)""")
dbconncr.execute("update monitor.t_mot_billpro_log set flag = '2' where flag = '0' and seq_fk = '"+ str(SEQ_FK) +"'")
dbconn.commit()
dbconncr.close()
connectDb.closeConnect(dbconn)
else:
dbconncr.close()
connectDb.closeConnect(dbconn)
except Exception,e:
dbconn.rollback()
dbconncr.close()
connectDb.closeConnect(dbconn)
with open(r'error.log','a') as error_log:
print >> error_log,('%s insertlog Faild: %s'%(time.ctime(),e))
log_tables = []
log_tables.append('%s insertlog Faild: %s'%(time.ctime(),e))
message.message_send(log_tables,phoneList)
def dealBusiness(nb):
dblist = connectDb.getConnectInfo("dbConnect")
if type(dblist) is str:
return dblist
print str(nb) + "线程开始：" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
dbconn = connectDb.connectDb(dblist[0])
if type(dbconn) is str:
print dbconn
return dbconn
cursor = connectDb.getCursor(dbconn)
if type(cursor) is str:
connectDb.closeConnect(dbconn)
print cursor
return cursor
time.sleep(nb)
print str(nb) + "线程程序处理结束：" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
connectDb.sqlcommit(dbconn)
clsCor = connectDb.closeCursor(cursor)
clsCnt = connectDb.closeConnect(dbconn)
print "关闭连接：" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
if type(clsCor) is str:
print clsCor
if type(clsCnt) is str:
print clsCnt
def getSqlData(sql):
try:
filename='./config/billdbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
result=connectDb.getSQLrst(cursor)
return result
except Exception,e:
getDataerr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute getSqlData falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(getDataerr,e))
finally:
connectDb.closeCursor(cursor)
connectDb.closeConnect(dbconn)
def writeData():
try:
sql=getSql('./config/billsql.txt')[0]
Total,ErrRe,Err,Errrate=getSqlData(sql)[0]
txt="""在 %s 至 %s 该段时间内,话单总数为：%s,回收错单数为：%s,错单总数为：%s,错单率为：%s 。""" % (yestoday,today,Total,ErrRe,Err,Errrate)
with open(r'./config/data.txt','w') as data:
print >> data,('%s' %(txt))
except Exception,e:
getwritererr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute writerData falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(getwritererr,e))
