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