def loadcode():
#添加新增的
sql1 = "update wraith_blklist set status=3 where status=0 limit 10000" #status=3 正在导入
sql2 = "select phone_number from wraith_blklist where status=3"
sql3 = "update wraith_blklist set status=1 where status=3"
#print "blklist loaded!"
r = redis.StrictRedis(host='localhost', port=16379, db=2)
#r.flushdb()
try:
while True:
#print sql1
mysql.query(sql1)
#print sql2
tmp = mysql.queryAll(sql2)
if(mysql.rowcount()==0):
break
for item in tmp:
r.set(item['phone_number'],'1')
#print r.get(item['code'])
#print sql3
mysql.query(sql3)
#print '\n'
except Exception, e:
print e
def loadcode():
#添加新增的
sql1 = "update wraith_blklist set status=3 where status=0 limit 10000" #status=3 正在导入
sql2 = "select phone_number from wraith_blklist where status=3"
sql3 = "update wraith_blklist set status=1 where status=3"
#print "blklist loaded!"
r = redis.StrictRedis(host='localhost', port=6379, db=2)
#r.flushdb()
try:
while True:
#print sql1
mysql.query(sql1)
#print sql2
tmp = mysql.queryAll(sql2)
if(mysql.rowcount()==0):
break
for item in tmp:
r.set(item['phone_number'],'1')
#print r.get(item['code'])
#print sql3
mysql.query(sql3)
#print '\n'
except Exception, e:
print e
def stat(stat_hour):
print "\n\n***stat hour: " + stat_hour
db_stat_hour = "DATE_FORMAT(in_time,'%Y-%m-%d:%H')"
#db_stat_hour = sys.argv[1]
#delete old days if
sql = "delete from wraith_statistic where stat_time = '%s'" % (stat_hour)
print sql
mysql.query(sql)
##group
sql = "select gwid,sp_number,product_id,product_code,amount,province,count(*) as num from `wraith_mt` where %s='%s' group by gwid,sp_number,product_id,product_code,province" % (db_stat_hour,stat_hour)
print sql
result = mysql.queryAll(sql)
if(mysql.rowcount()>0):
for row in result:
where_clause = " %s='%s' and gwid='%s' and sp_number='%s' and product_id='%s' and product_code='%s' and amount='%s' and province='%s' "%(db_stat_hour,stat_hour,row['gwid'],row['sp_number'],row['product_id'],row['product_code'],row['amount'],row['province'])
print "num: " + row['num']
#count sucessful record number:
csql = "select count(*) as success_num, sum(amount) as success_amount from wraith_mt where %s and (report = '4' or report ='DELIVRD' or report = '0') " % (where_clause)
print csql
cresult = mysql.queryAll(csql)
success_num = cresult[0]['success_num']
success_amount = cresult[0]['success_amount'] if cresult[0]['success_amount'] else 0
#count other
#insert
csql = "insert into wraith_statistic(gwid,sp_number,product_id,product_code,amount,stat_time,province,num,success_num,all_amount)values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (row['gwid'],row['sp_number'],row['product_id'],row['product_code'],row['amount'],stat_hour,row['province'],row['num'],success_num,success_amount)
print csql
mysql.query(csql)
def stat(stat_hour):
logging.info("***stat hour:%s***",stat_hour)
print '***stat hour:%s***'%(stat_hour)
db_stat_hour = "DATE_FORMAT(timeStamp,'%Y-%m-%d:%H')"
#db_stat_hour = sys.argv[1]
sql = "select ditchId,price,province from wraith_wo_web where %s='%s' group by ditchId,price,province " % (db_stat_hour,stat_hour)
logging.info(sql)
result = mysql.queryAll(sql)
if(mysql.rowcount()==0):
return
for row in result:
where_clause = " %s='%s' and ditchId='%s' and price='%s' and province='%s' " \
%(db_stat_hour,stat_hour,row['ditchId'],row['price'],row['province'])
logging.info(where_clause)
#count msg_count_all 总条数
csql = "select count(*) as msg_count_all from wraith_wo_web where %s" % (where_clause)
logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_all = cresult[0]['msg_count_all']
#msg_count_suc and count amount_suc record number:成功条数 金额
csql = "select count(*) as msg_count_suc, sum(totalFee) as amount_suc from wraith_wo_web where %s and resultCode = '0' " % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_suc = cresult[0]['msg_count_suc']
amount_suc = cresult[0]['amount_suc'] if cresult[0]['amount_suc']!='None' else '0'
#成功转发成功的mr数量和金额
#count msg_count_forward and amount_forward record number
csql = "select count(*) as msg_count_forward_mr,sum(totalFee) as amount_forward from wraith_wo_web where %s and resultCode = '0' and forward_mr_result='1' " % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_forward_mr = cresult[0]['msg_count_forward_mr']
amount_forward = cresult[0]['amount_forward'] if cresult[0]['amount_forward']!='None' else '0'
#insert or update
csql = "select * from wraith_wo_web_statistic where stat_time='%s' and ditchId='%s' and price='%s' and province='%s'" \
%(stat_hour,row['ditchId'],row['price'],row['province'])
cresult = mysql.queryAll(csql)
#logging.info(csql)
if(len(cresult)>0):
csql = "update wraith_wo_web_statistic set msg_count_all='%s',msg_count_suc='%s',amount_suc='%s',msg_count_forward_mr='%s',amount_forward='%s' where stat_time='%s' and ditchId='%s' and price='%s' and province='%s'" \
%(msg_count_all,msg_count_suc,amount_suc,msg_count_forward_mr,amount_forward,stat_hour,row['ditchId'],row['price'],row['province'])
else:
csql = "insert into wraith_wo_web_statistic(stat_time,price,ditchId,province,msg_count_all,msg_count_suc,amount_suc,msg_count_forward_mr,amount_forward)values('%s','%s','%s','%s','%s','%s','%s','%s','%s')" \
% (stat_hour,row['price'],row['ditchId'],row['province'],msg_count_all,msg_count_suc,amount_suc,msg_count_forward_mr,amount_forward)
logging.info(csql)
mysql.query(csql)
def stat(stat_hour):
logging.info("***stat hour:%s***", stat_hour)
print '***stat hour:%s***' % (stat_hour)
db_stat_hour = "DATE_FORMAT(timeStamp,'%Y-%m-%d:%H')"
#db_stat_hour = sys.argv[1]
sql = "select ditchId,price,province from wraith_wo_web where %s='%s' group by ditchId,price,province " % (
db_stat_hour, stat_hour)
logging.info(sql)
result = mysql.queryAll(sql)
if (mysql.rowcount() == 0):
return
for row in result:
where_clause = " %s='%s' and ditchId='%s' and price='%s' and province='%s' " \
%(db_stat_hour,stat_hour,row['ditchId'],row['price'],row['province'])
logging.info(where_clause)
#count msg_count_all 总条数
csql = "select count(*) as msg_count_all from wraith_wo_web where %s" % (
where_clause)
logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_all = cresult[0]['msg_count_all']
#msg_count_suc and count amount_suc record number:成功条数 金额
csql = "select count(*) as msg_count_suc, sum(totalFee) as amount_suc from wraith_wo_web where %s and resultCode = '0' " % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_suc = cresult[0]['msg_count_suc']
amount_suc = cresult[0][
'amount_suc'] if cresult[0]['amount_suc'] != 'None' else '0'
#成功转发成功的mr数量和金额
#count msg_count_forward and amount_forward record number
csql = "select count(*) as msg_count_forward_mr,sum(totalFee) as amount_forward from wraith_wo_web where %s and resultCode = '0' and forward_mr_result='1' " % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_forward_mr = cresult[0]['msg_count_forward_mr']
amount_forward = cresult[0]['amount_forward'] if cresult[0][
'amount_forward'] != 'None' else '0'
#insert or update
csql = "select * from wraith_wo_web_statistic where stat_time='%s' and ditchId='%s' and price='%s' and province='%s'" \
%(stat_hour,row['ditchId'],row['price'],row['province'])
cresult = mysql.queryAll(csql)
#logging.info(csql)
if (len(cresult) > 0):
csql = "update wraith_wo_web_statistic set msg_count_all='%s',msg_count_suc='%s',amount_suc='%s',msg_count_forward_mr='%s',amount_forward='%s' where stat_time='%s' and ditchId='%s' and price='%s' and province='%s'" \
%(msg_count_all,msg_count_suc,amount_suc,msg_count_forward_mr,amount_forward,stat_hour,row['ditchId'],row['price'],row['province'])
else:
csql = "insert into wraith_wo_web_statistic(stat_time,price,ditchId,province,msg_count_all,msg_count_suc,amount_suc,msg_count_forward_mr,amount_forward)values('%s','%s','%s','%s','%s','%s','%s','%s','%s')" \
% (stat_hour,row['price'],row['ditchId'],row['province'],msg_count_all,msg_count_suc,amount_suc,msg_count_forward_mr,amount_forward)
logging.info(csql)
mysql.query(csql)
def fetch_A_card(card_value):
sql = "select card_no,card_sec from wraith_card_no where status=0 and card_value='%d' limit 1"%(card_value)
#logging.info('%s',sql)
records = mysql.queryAll(sql)
if(mysql.rowcount()):
card_no = records[0]['card_no']
card_sec = records[0]['card_sec']
#logging.info("card_no:%s",card_no)
sql="update wraith_card_no set status=1,update_time=NOW() where card_no='%s' limit 1"%(card_no)
#logging.info('%s',sql)
mysql.query(sql)
return card_no,card_sec
else:
logging.info("no card!!!!!!")
time.sleep(3)
return False
def fetch_A_card(card_value):
sql = "select card_no,card_sec from wraith_card_no where status=0 and card_value='%d' limit 1" % (
card_value)
#logging.info('%s',sql)
records = mysql.queryAll(sql)
if (mysql.rowcount()):
card_no = records[0]['card_no']
card_sec = records[0]['card_sec']
#logging.info("card_no:%s",card_no)
sql = "update wraith_card_no set status=1,update_time=NOW() where card_no='%s' limit 1" % (
card_no)
#logging.info('%s',sql)
mysql.query(sql)
return card_no, card_sec
else:
logging.info("no card!!!!!!")
time.sleep(3)
return False
def stat(stat_hour):
logging.info("***stat hour:%s***",stat_hour)
print '***stat hour:%s***'%(stat_hour)
db_stat_hour = "DATE_FORMAT(motime,'%Y-%m-%d:%H')"
#db_stat_hour = sys.argv[1]
sql = "select gwid,feetype,is_agent,cmdID,spID,serviceID,cpID,cp_productID,province from wraith_message where %s='%s' and cmdId!=0 group by gwid,feetype,is_agent,cmdID,spID,serviceID,cpID,cp_productID,province " % (db_stat_hour,stat_hour)
logging.info(sql)
result = mysql.queryAll(sql)
if(mysql.rowcount()==0):
return
for row in result:
where_clause = " %s='%s' and gwid='%s' and feetype='%s' and is_agent='%s' and cmdID='%s' and spID='%s' and serviceID='%s' and cpID='%s' and cp_productID='%s' and province='%s' " \
%(db_stat_hour,stat_hour,row['gwid'],row['feetype'],row['is_agent'],row['cmdID'],row['spID'],row['serviceID'],row['cpID'],row['cp_productID'],row['province'])
#logging.info(where_clause)
#count msg_count_all
csql = "select count(*) as msg_count_all from wraith_message where %s" % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_all = cresult[0]['msg_count_all']
#count msg_count_legal
csql = "select count(*) as msg_count_legal from wraith_message where %s and mo_status='ok'" % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_legal = cresult[0]['msg_count_legal']
#msg_count_suc and count amount_suc record number:
csql = "select count(*) as msg_count_suc, sum(fee) as amount_suc from wraith_message where %s and report = '1' " % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_suc = cresult[0]['msg_count_suc']
amount_suc = cresult[0]['amount_suc'] if cresult[0]['amount_suc']!='None' else '0'
#count msg_count_deduction record number
csql = "select count(*) as msg_count_deduction from wraith_message where %s and mo_status='ok' and forward_status in(1,2,3,6,7) " % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_deduction = cresult[0]['msg_count_deduction']
#count amount_deduction record number
csql = "select count(*) as msg_count_deduction_suc,sum(fee) as amount_deduction from wraith_message where %s and report=1 and mo_status='ok' and forward_status in(1,2,3,6,7) " % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_deduction_suc = cresult[0]['msg_count_deduction_suc']
amount_deduction = cresult[0]['amount_deduction'] if cresult[0]['amount_deduction']!='None' else '0'
#成功转发的mo数量
#count msg_count_forward and amount_forward record number
csql = "select count(*) as msg_count_forward_mo from wraith_message where %s and forward_mo_result='1' " % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_forward_mo = cresult[0]['msg_count_forward_mo']
#成功转发的mr数量和金额
#count msg_count_forward and amount_forward record number
csql = "select count(*) as msg_count_forward_mr,sum(fee) as amount_forward from wraith_message where %s and forward_mr_result='1' " % (where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_forward_mr = cresult[0]['msg_count_forward_mr']
amount_forward = cresult[0]['amount_forward'] if cresult[0]['amount_forward']!='None' else '0'
#insert or update
csql = "select * from wraith_statistic where stat_time='%s' and gwid='%s' and feetype='%s' and is_agent='%s' and cmdID='%s' and spID='%s' and serviceID='%s' and cpID='%s' and cpProdID='%s' and province='%s'" \
%(stat_hour,row['gwid'],row['feetype'],row['is_agent'],row['cmdID'],row['spID'],row['serviceID'],row['cpID'],row['cp_productID'],row['province'])
cresult = mysql.queryAll(csql)
#logging.info(csql)
if(len(cresult)>0):
csql = "update wraith_statistic set msg_count_all='%s',msg_count_legal='%s',msg_count_suc='%s',msg_count_deduction='%s',msg_count_deduction_suc='%s',amount_suc='%s',amount_deduction='%s',msg_count_forward_mo='%s',msg_count_forward_mr='%s',amount_forward='%s' where stat_time='%s' and gwid='%s' and feetype='%s' and is_agent='%s' and cmdID='%s' and spID='%s' and serviceID='%s' and cpID='%s' and cpProdID='%s' and province='%s'" \
%(msg_count_all,msg_count_legal,msg_count_suc,msg_count_deduction,msg_count_deduction_suc,amount_suc,amount_deduction,msg_count_forward_mo,msg_count_forward_mr,amount_forward,stat_hour,row['gwid'],row['feetype'],row['is_agent'],row['cmdID'],row['spID'],row['serviceID'],row['cpID'],row['cp_productID'],row['province'])
else:
csql = "insert into wraith_statistic(stat_time,gwid,feetype,is_agent,cmdID,spID,serviceID,cpID,cpProdID,province,msg_count_all,msg_count_legal,msg_count_suc,msg_count_deduction,msg_count_deduction_suc,amount_suc,amount_deduction,msg_count_forward_mo,msg_count_forward_mr,amount_forward)values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" \
% (stat_hour,row['gwid'],row['feetype'],row['is_agent'],row['cmdID'],row['spID'],row['serviceID'],row['cpID'],row['cp_productID'],row['province'],msg_count_all,msg_count_legal,msg_count_suc,msg_count_deduction,msg_count_deduction_suc,amount_suc,amount_deduction,msg_count_forward_mo,msg_count_forward_mr,amount_forward)
logging.info(csql)
mysql.query(csql)
r.set(item['phone_number'],'1')
#print r.get(item['code'])
#print sql3
mysql.query(sql3)
#print '\n'
except Exception, e:
print e
#去掉删除的
sql4 = "update wraith_blklist set status=4 where status=2 limit 10000" #status=3 正在导入
sql5 = "select phone_number from wraith_blklist where status=4"
sql6 = "delete from wraith_blklist where status=4"
try:
while True:
#print sql3
mysql.query(sql4)
tmp = mysql.queryAll(sql5)
if(mysql.rowcount()==0):
break
#r.flushdb()
for item in tmp:
r.delete(item['phone_number'])
mysql.query(sql6)
except Exception, e:
print e
print '成功加载黑名单信息！'
if __name__ == "__main__":
loadcode()
def stat(stat_hour):
logging.info("***stat hour:%s***", stat_hour)
print '***stat hour:%s***' % (stat_hour)
db_stat_hour = "DATE_FORMAT(motime,'%Y-%m-%d:%H')"
#db_stat_hour = sys.argv[1]
sql = "select gwid,feetype,is_agent,cmdID,spID,serviceID,cpID,cp_productID,province from wraith_message where %s='%s' and cmdId!=0 group by gwid,feetype,is_agent,cmdID,spID,serviceID,cpID,cp_productID,province " % (
db_stat_hour, stat_hour)
logging.info(sql)
result = mysql.queryAll(sql)
if (mysql.rowcount() == 0):
return
for row in result:
where_clause = " %s='%s' and gwid='%s' and feetype='%s' and is_agent='%s' and cmdID='%s' and spID='%s' and serviceID='%s' and cpID='%s' and cp_productID='%s' and province='%s' " \
%(db_stat_hour,stat_hour,row['gwid'],row['feetype'],row['is_agent'],row['cmdID'],row['spID'],row['serviceID'],row['cpID'],row['cp_productID'],row['province'])
#logging.info(where_clause)
#count msg_count_all
csql = "select count(*) as msg_count_all from wraith_message where %s" % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_all = cresult[0]['msg_count_all']
#count msg_count_legal
csql = "select count(*) as msg_count_legal from wraith_message where %s and mo_status='ok'" % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_legal = cresult[0]['msg_count_legal']
#msg_count_suc and count amount_suc record number:
csql = "select count(*) as msg_count_suc, sum(fee) as amount_suc from wraith_message where %s and report = '1' " % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_suc = cresult[0]['msg_count_suc']
amount_suc = cresult[0][
'amount_suc'] if cresult[0]['amount_suc'] != 'None' else '0'
#count msg_count_deduction record number
csql = "select count(*) as msg_count_deduction from wraith_message where %s and mo_status='ok' and forward_status in(1,2,3,6,7) " % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_deduction = cresult[0]['msg_count_deduction']
#count amount_deduction record number
csql = "select count(*) as msg_count_deduction_suc,sum(fee) as amount_deduction from wraith_message where %s and report=1 and mo_status='ok' and forward_status in(1,2,3,6,7) " % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_deduction_suc = cresult[0]['msg_count_deduction_suc']
amount_deduction = cresult[0]['amount_deduction'] if cresult[0][
'amount_deduction'] != 'None' else '0'
#成功转发的mo数量
#count msg_count_forward and amount_forward record number
csql = "select count(*) as msg_count_forward_mo from wraith_message where %s and forward_mo_result='1' " % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_forward_mo = cresult[0]['msg_count_forward_mo']
#成功转发的mr数量和金额
#count msg_count_forward and amount_forward record number
csql = "select count(*) as msg_count_forward_mr,sum(fee) as amount_forward from wraith_message where %s and forward_mr_result='1' " % (
where_clause)
#logging.info(csql)
cresult = mysql.queryAll(csql)
msg_count_forward_mr = cresult[0]['msg_count_forward_mr']
amount_forward = cresult[0]['amount_forward'] if cresult[0][
'amount_forward'] != 'None' else '0'
#insert or update
csql = "select * from wraith_statistic where stat_time='%s' and gwid='%s' and feetype='%s' and is_agent='%s' and cmdID='%s' and spID='%s' and serviceID='%s' and cpID='%s' and cpProdID='%s' and province='%s'" \
%(stat_hour,row['gwid'],row['feetype'],row['is_agent'],row['cmdID'],row['spID'],row['serviceID'],row['cpID'],row['cp_productID'],row['province'])
cresult = mysql.queryAll(csql)
#logging.info(csql)
if (len(cresult) > 0):
csql = "update wraith_statistic set msg_count_all='%s',msg_count_legal='%s',msg_count_suc='%s',msg_count_deduction='%s',msg_count_deduction_suc='%s',amount_suc='%s',amount_deduction='%s',msg_count_forward_mo='%s',msg_count_forward_mr='%s',amount_forward='%s' where stat_time='%s' and gwid='%s' and feetype='%s' and is_agent='%s' and cmdID='%s' and spID='%s' and serviceID='%s' and cpID='%s' and cpProdID='%s' and province='%s'" \
%(msg_count_all,msg_count_legal,msg_count_suc,msg_count_deduction,msg_count_deduction_suc,amount_suc,amount_deduction,msg_count_forward_mo,msg_count_forward_mr,amount_forward,stat_hour,row['gwid'],row['feetype'],row['is_agent'],row['cmdID'],row['spID'],row['serviceID'],row['cpID'],row['cp_productID'],row['province'])
else:
csql = "insert into wraith_statistic(stat_time,gwid,feetype,is_agent,cmdID,spID,serviceID,cpID,cpProdID,province,msg_count_all,msg_count_legal,msg_count_suc,msg_count_deduction,msg_count_deduction_suc,amount_suc,amount_deduction,msg_count_forward_mo,msg_count_forward_mr,amount_forward)values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" \
% (stat_hour,row['gwid'],row['feetype'],row['is_agent'],row['cmdID'],row['spID'],row['serviceID'],row['cpID'],row['cp_productID'],row['province'],msg_count_all,msg_count_legal,msg_count_suc,msg_count_deduction,msg_count_deduction_suc,amount_suc,amount_deduction,msg_count_forward_mo,msg_count_forward_mr,amount_forward)
logging.info(csql)
mysql.query(csql)
r.set(item['phone_number'],'1')
#print r.get(item['code'])
#print sql3
mysql.query(sql3)
#print '\n'
except Exception, e:
print e
#去掉删除的
sql4 = "update wraith_blklist set status=4 where status=2 limit 10000" #status=3 正在导入
sql5 = "select phone_number from wraith_blklist where status=4"
sql6 = "delete from wraith_blklist where status=4"
try:
while True:
#print sql3
mysql.query(sql4)
tmp = mysql.queryAll(sql5)
if(mysql.rowcount()==0):
break
#r.flushdb()
for item in tmp:
r.delete(item['phone_number'])
mysql.query(sql6)
except Exception, e:
print e
print '成功加载黑名单信息！'
if __name__ == "__main__":
loadcode()