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 load_products(self):
sql = "select * from wraith_products"
self.products = mysql.queryAll(sql)
#load conents
default_content = {}
default_content['content'] = "welcome"
for product in self.products:
sql = "select content from wraith_products_contents where pid=" + product[
'id']
one_prod_contents = mysql.queryAll(sql)
if (len(one_prod_contents) == 0):
one_prod_contents.append(default_content)
self.contents[product['id']] = one_prod_contents
def load_products(self):
sql = "select * from wraith_products"
self.products = mysql.queryAll(sql);
#load conents
default_content={}
default_content['content']="welcome"
for product in self.products:
sql = "select content from wraith_products_contents where pid="+product['id']
one_prod_contents = mysql.queryAll(sql);
if(len(one_prod_contents)==0):
one_prod_contents.append(default_content)
self.contents[product['id']]=one_prod_contents
def load_dict(self):
sql = '''
select t1.id as cmdID,t1.sp_number as cmd_spnumber,t1.mo_cmd as cmd_mocmd,
coalesce(t2.ID,0) as cpID,
coalesce(t2.cpname,'')as cpname,
coalesce(t3.id,0) as cp_productID,
coalesce(t3.`name`,'') as cp_product_name,
coalesce(t3.mourl,'') as mourl,
coalesce(t3.mrurl,'') as mrurl,
coalesce(t3.forward_mo_module,'') as forward_mo_module,
coalesce(t3.forward_mr_module,'') as forward_mr_module,
coalesce(t1.open_province,'') as open_province,
coalesce(t1.forbidden_area,'') as forbidden_area,
coalesce(t4.ID , 0) as spID,
coalesce(t4.sp_id,'') as sp_id,
coalesce(t4.spname,'') as spname,
coalesce(t5.ID , 0) as serviceID,
coalesce(t5.`name`, '') as service_name,
coalesce(t5.sp_number ,'') as serv_spnumber,
coalesce(t5.mo_cmd,'') as serv_mocmd
from mtrs_cmd t1
left join mtrs_cp_product t3 on t1.cpProdID=t3.id
left join mtrs_cp t2 on t3.cpID=t2.id
left join mtrs_service t5 on t1.serviceID=t5.ID
left join mtrs_sp t4 on t5.spID=t4.ID
'''
self.__cmd_dict__ = mysql.queryAll(sql)
logging.info(sql)
#logging.info("loadding cmds:%s",self.__cmd_dict__)
#print 'cmd_info loaded'
for i in range(len(self.__cmd_dict__)):
logging.info(self.__cmd_dict__[i])
def app_card(self, cmdID):
#insert into wraith_card_record
sql = "insert into wraith_card_record set mo_message='%s',set phone_number='%s',in_time=NOW(),message_id='%s'" % (
cmd_info['cmdID'], cmd_info['cmdID'], cmd_info['cmdID'])
self.__products__ = mysql.queryAll(sql)
logging.info(sql)
def get_seq(record):
card_value=5*int(record['mo_message'][-1:])
sql = "select count(*) as seq from wraith_card_record where in_time > NOW()-interval 12 hour and id<'%s' and report=1 and order_id is NULL and phone_number='%s' and 5*right(mo_message,1)='%d'"%(record['id'],record['phone_number'],card_value)
#logging.info('%s',sql)
records = mysql.queryAll(sql)
seq = int(records[0]['seq'])+1
return seq
def load_dict(self):
sql = '''
select t1.id as cmdID,t1.sp_number as cmd_spnumber,t1.mo_cmd as cmd_mocmd,
coalesce(t2.ID,0) as cpID,
coalesce(t2.cpname,'')as cpname,
coalesce(t3.id,0) as cp_productID,
coalesce(t3.`name`,'') as cp_product_name,
coalesce(t3.mourl,'') as mourl,
coalesce(t3.mrurl,'') as mrurl,
coalesce(t3.forward_mo_module,'') as forward_mo_module,
coalesce(t3.forward_mr_module,'') as forward_mr_module,
coalesce(t1.open_province,'') as open_province,
coalesce(t1.forbidden_area,'') as forbidden_area,
coalesce(t4.ID , 0) as spID,
coalesce(t4.sp_id,'') as sp_id,
coalesce(t4.spname,'') as spname,
coalesce(t5.ID , 0) as serviceID,
coalesce(t5.`name`, '') as service_name,
coalesce(t5.sp_number ,'') as serv_spnumber,
coalesce(t5.mo_cmd,'') as serv_mocmd
from mtrs_cmd t1
left join mtrs_cp_product t3 on t1.cpProdID=t3.id
left join mtrs_cp t2 on t3.cpID=t2.id
left join mtrs_service t5 on t1.serviceID=t5.ID
left join mtrs_sp t4 on t5.spID=t4.ID
'''
self.__cmd_dict__ = mysql.queryAll(sql)
logging.info(sql)
#logging.info("loadding cmds:%s",self.__cmd_dict__)
#print 'cmd_info loaded'
for i in range(len(self.__cmd_dict__)):
logging.info(self.__cmd_dict__[i])
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 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_batch():
sql="select * from wraith_card_record where in_time > NOW()-interval 30 minute and send_mt_msg=1 and report is NULL order by id asc limit 500"
#logging.info('%s',sql)
records = mysql.queryAll(sql)
for record in records:
update_report(record)
return mysql.rowcount
def send_mt_message_batch():
sql="select * from wraith_card_record where in_time > NOW()-interval 30 minute and send_mt_msg=0 order by id asc limit 1"
#logging.info('%s',sql)
records = mysql.queryAll(sql)
for record in records:
send_mt_message(record)
return mysql.rowcount
def send_mt_message_batch():
sql = "select * from wraith_card_record where in_time > NOW()-interval 30 minute and send_mt_msg=0 order by id asc limit 1"
#logging.info('%s',sql)
records = mysql.queryAll(sql)
for record in records:
send_mt_message(record)
return mysql.rowcount
def update_report_batch():
sql = "select * from wraith_card_record where in_time > NOW()-interval 30 minute and send_mt_msg=1 and report is NULL order by id asc limit 500"
#logging.info('%s',sql)
records = mysql.queryAll(sql)
for record in records:
update_report(record)
return mysql.rowcount
def gen_order_batch():
sql="select * from wraith_card_record where in_time > NOW()-interval 30 minute and report=1 and is_last=1 and order_id is NULL order by id asc limit 100"
#logging.info('%s',sql)
records = mysql.queryAll(sql)
for record in records:
gen_order(record)
delete_card(record['card_no'])
return mysql.rowcount
def get_seq(record):
card_value = 5 * int(record['mo_message'][-1:])
sql = "select count(*) as seq from wraith_card_record where in_time > NOW()-interval 12 hour and id<'%s' and report=1 and order_id is NULL and phone_number='%s' and 5*right(mo_message,1)='%d'" % (
record['id'], record['phone_number'], card_value)
#logging.info('%s',sql)
records = mysql.queryAll(sql)
seq = int(records[0]['seq']) + 1
return seq
def get_card_price_from_db():
sql = "select * from wraith_card_price where 1"
logging.info('%s',sql)
records = mysql.queryAll(sql)
for record in records:
card_prices[int(record['card_value'])] = record['card_price']
return
def get_mt_messages_from_db():
sql = "select * from wraith_card_message where 1"
logging.info('%s', sql)
records = mysql.queryAll(sql)
for record in records:
mt_messages[int(record['id'])] = record['message']
return
def get_card_price_from_db():
sql = "select * from wraith_card_price where 1"
logging.info('%s', sql)
records = mysql.queryAll(sql)
for record in records:
card_prices[int(record['card_value'])] = record['card_price']
return
def gen_order_batch():
sql = "select * from wraith_card_record where in_time > NOW()-interval 30 minute and report=1 and is_last=1 and order_id is NULL order by id asc limit 100"
#logging.info('%s',sql)
records = mysql.queryAll(sql)
for record in records:
gen_order(record)
delete_card(record['card_no'])
return mysql.rowcount
def get_mt_messages_from_db():
sql = "select * from wraith_card_message where 1"
logging.info('%s',sql)
records = mysql.queryAll(sql)
for record in records:
mt_messages[int(record['id'])] = record['message']
return
def load_products(self):
sql = '''select t1.ID as cmdID,t1.app_module,t1.mo_cmd,t1.sp_number,t1.is_agent,
t2.fee,t2.feetype,t2.msgtype,t2.gwid,t1.open_province,t1.forbidden_area,t2.service_id
from mtrs_cmd t1, mtrs_service t2
where t1.serviceID=t2.ID and t1.status=1'''
self.__products__ = mysql.queryAll(sql);
logging.info(sql)
#logging.info('all cmdinfo: %s',self.__products__)
for i in range(len(self.__products__)):
logging.info(self.__products__[i])
#load conents
default_content={}
default_content['content']=""
for product in self.__products__:
sql = "select content from mtrs_cmd_mt where cmdID="+product['cmdID']
one_prod_contents = mysql.queryAll(sql);
if(len(one_prod_contents)==0):
one_prod_contents.append(default_content)
self.contents[product['cmdID']]=one_prod_contents
def load_products(self):
sql = '''select t1.ID as cmdID,t1.app_module,t1.mo_cmd,t1.sp_number,t1.is_agent,
t2.fee,t2.feetype,t2.msgtype,t2.gwid,t1.open_province,t1.forbidden_area,t2.service_id
from mtrs_cmd t1, mtrs_service t2
where t1.serviceID=t2.ID and t1.status=1'''
self.__products__ = mysql.queryAll(sql)
logging.info(sql)
#logging.info('all cmdinfo: %s',self.__products__)
for i in range(len(self.__products__)):
logging.info(self.__products__[i])
#load conents
default_content = {}
default_content['content'] = ""
for cmd in self.__products__:
sql = "select content from mtrs_cmd_mt where cmdID=" + cmd['cmdID']
one_prod_contents = mysql.queryAll(sql)
if (len(one_prod_contents) == 0):
one_prod_contents.append(default_content)
self.contents[cmd['cmdID']] = one_prod_contents
def loadcode():
sql = "select * from wraith_code_segment"
tmp = mysql.queryAll(sql)
#print "blklist loaded!"
r = redis.StrictRedis(host='localhost', port=6379, db=1)
#r.flushdb()
for item in tmp:
r.set(item['code'],item['province']+"_"+item['area'])
#print r.get(item['code'])
print '成功加载号段信息！'
def loadcode():
sql = "select * from wraith_code_segment"
tmp = mysql.queryAll(sql)
#print "blklist loaded!"
r = redis.StrictRedis(host='localhost', port=6379, db=1)
#r.flushdb()
for item in tmp:
r.set(item['code'], item['province'] + "_" + item['area'])
#print r.get(item['code'])
print '成功加载号段信息！'
def get_data():
'''
condition1 = "mo_status is not null and mo_status!='ok'"#mo处理结果为非法的记录
condition2 = "motime < NOW()-interval 4 hour" #超过n小时仍未处理完毕的记录
condition3 = "is_agent=1 and report is not null" #非转发业务处理完成的记录
condition4 = 'forward_status>1'#转发完成的记录
'''
sql = "select id from wraith_message where motime < CURDATE()"
logging.info(sql)
data = mysql.queryAll(sql)
return data
def get_data():
'''
condition1 = "mo_status is not null and mo_status!='ok'"#mo处理结果为非法的记录
condition2 = "motime < NOW()-interval 4 hour" #超过n小时仍未处理完毕的记录
condition3 = "is_agent=1 and report is not null" #非转发业务处理完成的记录
condition4 = 'forward_status>1'#转发完成的记录
'''
sql = "select id from wraith_message where motime < CURDATE()"
logging.info(sql)
data = mysql.queryAll(sql);
return data
def loadcode():
#添加新增的
sql = "select * from wraith_whitelist"
tmp = mysql.queryAll(sql)
#print "blklist loaded!"
r = redis.StrictRedis(host='localhost', port=6379, db=3)
r.flushdb()
for item in tmp:
r.set(item['phone_number'],'1')
#print r.get(item['code'])
print '成功白名单信息！'
def finish_order(record):
card_value=5*int(record['mo_message'][-1:])
card_price=int(card_prices[card_value])
sql = "select ifnull(ceil(sum(fee)),0) as sum from wraith_card_record where in_time > NOW()-interval 12 hour and deduction=0 and (id='%s' or (id < %s and report=1 and order_id is NULL and phone_number='%s' and 5*right(mo_message,1)='%d'))"%(record['id'],record['id'],record['phone_number'],card_value)
#logging.info('%s',sql)
records = mysql.queryAll(sql)
sum = int(records[0]['sum'])
logging.info("card_value:%d, card_price:%d,already_get:%d",card_value,card_price,sum)
if(sum >= card_price):
#logging.info("true")
return 1
else:
#logging.info("false")
return 0
def loadcode():
#添加新增的
sql = "select * from wraith_whitelist"
tmp = mysql.queryAll(sql)
#print "blklist loaded!"
r = redis.StrictRedis(host='localhost', port=16379, db=3)
r.flushdb()
for item in tmp:
r.set(item['phone_number'], '1')
#print r.get(item['code'])
print '成功白名单信息！'
def check():
f**k=0
errmsg=''
######
sql = "select count(*) as num from wraith_message where mo_status is NULL"
n = int(mysql.queryAll(sql)[0]['num'])
#print n
if(n>20):
errmsg='wait number: %d'%n
f**k=1
######
#print n,f**k,errmsg
return f**k,errmsg
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 finish_order(record):
card_value = 5 * int(record['mo_message'][-1:])
card_price = int(card_prices[card_value])
sql = "select ifnull(ceil(sum(fee)),0) as sum from wraith_card_record where in_time > NOW()-interval 12 hour and deduction=0 and (id='%s' or (id < %s and report=1 and order_id is NULL and phone_number='%s' and 5*right(mo_message,1)='%d'))" % (
record['id'], record['id'], record['phone_number'], card_value)
#logging.info('%s',sql)
records = mysql.queryAll(sql)
sum = int(records[0]['sum'])
logging.info("card_value:%d, card_price:%d,already_get:%d", card_value,
card_price, sum)
if (sum >= card_price):
#logging.info("true")
return 1
else:
#logging.info("false")
return 0
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 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 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 load_dict(self):
sql = "select * from wraith_visit_limit"
self.__v_dict__ = mysql.queryAll(sql)
def load_blklist(self):
sql = "select phone_number from wraith_blklist"
self.__blklist__ = mysql.queryAll(sql);
def read_data(self):
sql = "select * from wraith_mo where id > '%s' limit 1"%(self.get_deal_pos())
data = mysql.queryAll(sql);
return data
def get_data():
sql = 'select id,phone_number,mo_message,sp_number,linkid,gwid,province,area,motime from wraith_message where mo_status is null order by id asc limit 50'
#logging.info(sql)
data = mysql.queryAll(sql);
return data
def get_data():
sql = 'select id,phone_number,mo_message,sp_number,linkid,gwid,province,area,motime from wraith_message where mo_status is null order by id asc limit 50'
#logging.info(sql)
data = mysql.queryAll(sql)
return data
def get_data():
sql = "select * from wraith_message where mo_status='ok' and is_agent=2 and ((forward_status=0) or (forward_status=1 and report is not NULL)) order by id asc limit 100"
#logging.info(sql)
data = mysql.queryAll(sql);
return data
def read_data(self):
sql = "select * from wraith_mo order by id desc limit 1"
data = mysql.queryAll(sql);
return data
def app_card(self,cmdID):
#insert into wraith_card_record
sql = "insert into wraith_card_record set mo_message='%s',set phone_number='%s',in_time=NOW(),message_id='%s'"%(cmd_info['cmdID'],cmd_info['cmdID'],cmd_info['cmdID'])
self.__products__ = mysql.queryAll(sql);
logging.info(sql)
def read_data(self):
sql = "select * from wraith_mo order by id desc limit 1"
data = mysql.queryAll(sql)
return data
def load_dict(self):
sql = "select * from mtrs_deduction"
self.__deduction_dict__ = mysql.queryAll(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 get_data():
sql = "select * from wraith_message where mo_status='ok' and is_agent=2 and ((forward_status=0) or (forward_status=1 and report is not NULL)) order by id asc limit 100"
#logging.info(sql)
data = mysql.queryAll(sql)
return data
def load_dict(self):
sql = "select * from mtrs_deduction"
self.__deduction_dict__ = mysql.queryAll(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 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()
