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