def update_mt_msg(record, mt_msg):
seq = get_seq(record)
sql = "update wraith_message set mt_message='%s' where id='%s'" % (
mt_msg, record['message_id'])
#logging.info('sql:%s',sql)
mysql.query(sql)
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 update_record_info(record,mt_msg,card_no,card_sec,seq,is_last,card_value,card_price):
sql = "update wraith_card_record set send_mt_msg='1',mt_message='%s',seq='%d', card_no='%s',card_sec='%s',is_last='%s',card_value='%s',card_price='%s' where id='%s' "%(mt_msg,seq,card_no,card_sec,is_last,card_value,card_price,record['id'])
#logging.info('%s',sql)
mysql.query(sql)
return
def gen_order(record):
card_value = 5 * int(record['mo_message'][-1:])
sql = "update wraith_card_record set order_id='%s' where id<=%s and report=1 and order_id is NULL and phone_number='%s' and 5*right(mo_message,1)='%d'" % (
'1', record['id'], record['phone_number'], card_value)
#logging.info('%s',sql)
mysql.query(sql)
return
def tag_area():
print "loading mobiledict..."
mobile_dict = load_mobile_dict()
if mobile_dict is not False:
print "mobiledict loaded:", len(mobile_dict)
sql = "select id, phone_number from wraith_mt where LENGTH(area)=0 or area is NULL or area='' limit 100"
while True:
result = mysql.queryAll(sql);
if(len(result)==0):
print 'no result'
time.sleep(5)
continue
else:
for row in result:
if(mobile_dict.has_key(row['phone_number'][:7])):
area = mobile_dict[row['phone_number'][:7]]['area']
else:
area = '0000'
update_sql = "update wraith_mt set area = '%s' where id = '%s'" % (area, row['id'])
print update_sql
mysql.query(update_sql)
def update_report(record):
sql='update wraith_card_record set report=(select report from wraith_message where id=%s) where id=%s'%(record['message_id'],record['id']);
#logging.info('sql:%s',sql)
mysql.query(sql);
#affected_num = mysql.conn.affected_rows()
#logging.info('affected_rows:%d'%(affected_num))
return
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 update_report(record):
sql = 'update wraith_card_record set report=(select report from wraith_message where id=%s) where id=%s' % (
record['message_id'], record['id'])
#logging.info('sql:%s',sql)
mysql.query(sql)
#affected_num = mysql.conn.affected_rows()
#logging.info('affected_rows:%d'%(affected_num))
return
def app_card(cmd_info,record):
#insert into wraith_card_record
sql = "insert into wraith_card_record set mo_message='%s', phone_number='%s',in_time=NOW(),fee='%d',message_id='%s',deduction='%s' "%(record['mo_message'],record['phone_number'],int(cmd_info['fee'])/100,record['id'],cmd_info['forward_status'])
logging.info(sql)
mysql.query(sql)
return ''
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 update_record_info(record, mt_msg, card_no, card_sec, seq, is_last,
card_value, card_price):
sql = "update wraith_card_record set send_mt_msg='1',mt_message='%s',seq='%d', card_no='%s',card_sec='%s',is_last='%s',card_value='%s',card_price='%s' where id='%s' " % (
mt_msg, seq, card_no, card_sec, is_last, card_value, card_price,
record['id'])
#logging.info('%s',sql)
mysql.query(sql)
return
def app_card(cmd_info, record):
#insert into wraith_card_record
sql = "insert into wraith_card_record set mo_message='%s', phone_number='%s',in_time=NOW(),fee='%d',message_id='%s',deduction='%s' " % (
record['mo_message'], record['phone_number'],
int(cmd_info['fee']) / 100, record['id'], cmd_info['forward_status'])
logging.info(sql)
mysql.query(sql)
return ''
def migrate(id):
sql = "replace into wraith_message_history select * from wraith_message where id='%s'"%(id)
logging.info('dbsql:%s',sql)
mysql.query(sql)
affected_num = mysql.conn.affected_rows()
logging.info('affected_rows:%d'%(affected_num))
if(affected_num != 1):
logging.info("failed to insert ,ignore")
return
sql = "delete from wraith_message where id='%s'"%(id)
logging.info('dbsql:%s',sql)
mysql.query(sql)
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 migrate(id):
sql = "replace into wraith_message_history select * from wraith_message where id='%s'" % (
id)
logging.info('dbsql:%s', sql)
mysql.query(sql)
affected_num = mysql.conn.affected_rows()
logging.info('affected_rows:%d' % (affected_num))
if (affected_num != 1):
logging.info("failed to insert ,ignore")
return
sql = "delete from wraith_message where id='%s'" % (id)
logging.info('dbsql:%s', sql)
mysql.query(sql)
def update_forward_info(message_id,forward_status,forward_result,forward_resp,forward_url,type):
global mysql
if( forward_status==4 \
or forward_status==3 \
or forward_status==5 \
or forward_status==13 \
or forward_status==14 \
or forward_status==15 \
or forward_status==16
):
sql = "update wraith_message set forward_status='%d' where id='%s'"%(forward_status, message_id)
else:
sql = "update wraith_message set forward_status='%d', forward_%s_result='%d',forward_%s_time=NOW(),forward_%s_resp='%s',forward_%s_url='%s' where id='%s'" \
%(forward_status,type,forward_result,type,type,forward_resp,type,forward_url,message_id)
#logging.info('dbsql:%s',sql)
mysql.query(sql)
def tag_area():
sql = "select id, phone_number from wraith_mt_copy where in_time > '2014' and LENGTH(province)=0 or province is NULL or province='' limit 100"
while True:
result = mysql.queryAll(sql);
if(len(result)==0):
print 'finished!'
return
else:
for row in result:
province,area = mobile_dict.get_mobile_area(row['phone_number'])
update_sql = "update wraith_mt_copy set province='%s',area = '%s' where id = '%s'" % (province,area, row['id'])
print update_sql
mysql.query(update_sql)
def update_forward_info(message_id,forward_status,forward_result,forward_resp,forward_url,type):
global mysql
if( forward_status==4 \
or forward_status==3 \
or forward_status==5 \
or forward_status==13 \
or forward_status==14 \
or forward_status==15 \
or forward_status==16
):
sql = "update wraith_message set forward_status='%d' where id='%s'"%(forward_status, message_id)
else:
sql = "update wraith_message set forward_status='%d', forward_%s_result='%d',forward_%s_time=NOW(),forward_%s_resp='%s',forward_%s_url='%s' where id='%s'" \
%(forward_status,type,forward_result,type,type,forward_resp,type,forward_url,message_id)
#logging.info('dbsql:%s',sql)
mysql.query(sql)
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 tag_area():
sql = "select id, phone_number from wraith_mt_copy where in_time > '2014' and LENGTH(province)=0 or province is NULL or province='' limit 100"
while True:
result = mysql.queryAll(sql)
if (len(result) == 0):
print 'finished!'
return
else:
for row in result:
province, area = mobile_dict.get_mobile_area(
row['phone_number'])
update_sql = "update wraith_mt_copy set province='%s',area = '%s' where id = '%s'" % (
province, area, row['id'])
print update_sql
mysql.query(update_sql)
def write_db(id, cmd_info, zone, mo_status):
####正式运营请注释掉此部分代码
'''
if(mo_status=='ok'):
report=',report=%d'%(2 if in_po(0.05) else 1)
else:
report=''
'''
report=''
####
if(len(cmd_info)>1):
sql = "update wraith_message set province='%s',area='%s', cmdID='%s',fee='%s',feetype='%s',service_id='%s',mt_message='%s',msgtype='%s', mo_status='%s',is_agent='%s' %s where id='%s'"%(zone[0],zone[1], cmd_info['cmdID'],cmd_info['fee'],cmd_info['feetype'],cmd_info['service_id'],cmd_info['mt_message'],cmd_info['msgtype'],mo_status,cmd_info['is_agent'],report,id)
else:
sql = "update wraith_message set province='%s',area='%s', mo_status='%s' where id='%s'" %(zone[0],zone[1],mo_status,id)
#logging.info('dbsql:%s',sql)
mysql.query(sql)
def write_db(id, cmd_info, zone, mo_status):
####正式运营请注释掉此部分代码
'''
if(mo_status=='ok'):
report=',report=%d'%(2 if in_po(0.05) else 1)
else:
report=''
'''
report = ''
####
if (len(cmd_info) > 1):
sql = "update wraith_message set province='%s',area='%s', cmdID='%s',fee='%s',feetype='%s',service_id='%s',mt_message='%s',msgtype='%s', mo_status='%s',is_agent='%s',forward_status='%d' %s where id='%s'" \
%(zone[0],zone[1], cmd_info['cmdID'],cmd_info['fee'],cmd_info['feetype'],cmd_info['service_id'],cmd_info['mt_message'],cmd_info['msgtype'],mo_status,cmd_info['is_agent'],cmd_info['forward_status'],report,id)
else:
sql = "update wraith_message set province='%s',area='%s', mo_status='%s' where id='%s'" % (
zone[0], zone[1], mo_status, id)
#logging.info('dbsql:%s',sql)
mysql.query(sql)
def write_cmd_info(id, cmd_info):
sql = '''
update wraith_message set
cmd_spnumber='%s',
cmd_mocmd='%s',
sp_id='%s',
cpname='%s',
cpID='%s',
spID='%s',
service_name='%s',
spname='%s',
cp_productID='%s',
cp_product_name='%s',
serviceID='%s',
serv_mocmd='%s',
serv_spnumber='%s'
where id='%s'
'''\
%(cmd_info['cmd_spnumber'],cmd_info['cmd_mocmd'],cmd_info['sp_id'],cmd_info['cpname'],cmd_info['cpID'],cmd_info['spID'],cmd_info['service_name'],cmd_info['spname'],cmd_info['cp_productID'],cmd_info['cp_product_name'],cmd_info['serviceID'],cmd_info['serv_mocmd'],cmd_info['serv_spnumber'],id)
#logging.info(sql)
mysql.query(sql)
def write_cmd_info(id, cmd_info):
sql = '''
update wraith_message set
cmd_spnumber='%s',
cmd_mocmd='%s',
sp_id='%s',
cpname='%s',
cpID='%s',
spID='%s',
service_name='%s',
spname='%s',
cp_productID='%s',
cp_product_name='%s',
serviceID='%s',
serv_mocmd='%s',
serv_spnumber='%s'
where id='%s'
'''\
%(cmd_info['cmd_spnumber'],cmd_info['cmd_mocmd'],cmd_info['sp_id'],cmd_info['cpname'],cmd_info['cpID'],cmd_info['spID'],cmd_info['service_name'],cmd_info['spname'],cmd_info['cp_productID'],cmd_info['cp_product_name'],cmd_info['serviceID'],cmd_info['serv_mocmd'],cmd_info['serv_spnumber'],id)
#logging.info(sql)
mysql.query(sql)
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 update_record_status(record):
sql = "update wraith_card_record set send_mt_message='1',mt_message='', where id='%s' "%(field,value,record['id'])
#logging.info('%s',sql)
mysql.query(sql)
return
def update_mt_msg(record,mt_msg):
seq=get_seq(record)
sql="update wraith_message set mt_message='%s' where id='%s'"%(mt_msg,record['message_id']);
#logging.info('sql:%s',sql)
mysql.query(sql);
def recycle_card():
sql="update wraith_card_no set status=0,update_time=NULL where `status`=1 and update_time < NOW() - interval 3 day"
mysql.query(sql);
def recycle_card():
sql = "update wraith_card_no set status=0,update_time=NULL where `status`=1 and update_time < NOW() - interval 3 day"
mysql.query(sql)
def delete_card(card_no):
sql="update wraith_card_no set status=2,update_time=NOW() where card_no='%s' limit 1"%(card_no)
#logging.info('%s',sql)
mysql.query(sql)
def update_record_status(record):
sql = "update wraith_card_record set send_mt_message='1',mt_message='', where id='%s' " % (
field, value, record['id'])
#logging.info('%s',sql)
mysql.query(sql)
return
def delete_card(card_no):
sql = "update wraith_card_no set status=2,update_time=NOW() where card_no='%s' limit 1" % (
card_no)
#logging.info('%s',sql)
mysql.query(sql)
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)
def gen_order(record):
card_value=5*int(record['mo_message'][-1:])
sql="update wraith_card_record set order_id='%s' where id<=%s and report=1 and order_id is NULL and phone_number='%s' and 5*right(mo_message,1)='%d'"%('1',record['id'],record['phone_number'],card_value)
#logging.info('%s',sql)
mysql.query(sql)
return
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()
