def get_redeem_p(self): ret_redeem = {} sql = "select * from new_balance_redeem_2014" #sql = "select * from balance_2014" ss = " where (Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407' or SubString(report_date,1,6) = '201408')" #ss = " where (Substring(report_date,1,6) = '201405' or Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407' )" sql = sql + ss self.cursor.execute(sql) rows = self.cursor.fetchall() key_time = get_time('20140101') #week_count = [35,35,35,35,35,34,34] week_count = [14,13,13,13,13,13,13] #week_count = [18,18,18,18,17,17,17] i = 0 for row in rows: i = i + 1 if i % 10000 == 0: print i id,user_id,report_date,tBalance,yBalance,total_purchase_amt,direct_purchase_amt,purchase_bal_amt,purchase_bank_amt,total_redeem_amt,consume_amt,transfer_amt,tftobal_amt,tftocard_amt,share_amt,category1,category2,category3,category4 = row if user_id not in ret_redeem.keys(): ret_redeem[user_id] = {0:1,1:0,2:0,3:0,4:0,5:0,6:0 } day = (get_time(report_date) - key_time).days day = day%7 ret_redeem[user_id][day] += 1 for user in ret_redeem.keys(): for day in range(7): ret_redeem[user][day] = float(ret_redeem[user][day])/week_count[day] f = file("../../data/概率/user_redeem_p.pkl",'wb') pickle.dump(ret_redeem,f) f.close()
def get_redeem_mean(self): ret_redeem = {} sql = "select * from new_balance_redeem_2014" ss = " where (Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407' or SubString(report_date,1,6) = '201408')" #ss = " where (Substring(report_date,1,6) = '201405' or Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407' )" sql = sql + ss self.cursor.execute(sql) rows = self.cursor.fetchall() key_time = get_time('20140101') f1 = file("data/user_redeem_day.pkl",'rb') week_count = pickle.load(f1) f1.close() i = 0 for row in rows: i = i + 1 if i % 10000 == 0: print i id,user_id,report_date,tBalance,yBalance,total_purchase_amt,direct_purchase_amt,purchase_bal_amt,purchase_bank_amt,total_redeem_amt,consume_amt,transfer_amt,tftobal_amt,tftocard_amt,share_amt,category1,category2,category3,category4 = row day = (get_time(report_date) - key_time).days #day = day%7 if day not in ret_redeem.keys(): ret_redeem[day] = 0 ret_redeem[day] += total_redeem_amt for user in ret_redeem.keys(): ret_redeem[user] = float(ret_redeem[user])/week_count[user] f = file("data/user_redeem_value.pkl",'wb') pickle.dump(ret_redeem,f) f.close()
def get_purchase_mean(self): ret_purchase = {} sql = "select * from new_balance_purchase_2014" ss = " where (Substring(report_date,1,6) = '201405' or Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407' or SubString(report_date,1,6) = '201408')" ss = " where (Substring(report_date,1,6) = '201405' or Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407' )" sql = sql + ss self.cursor.execute(sql) rows = self.cursor.fetchall() key_time = get_time('20140101') f1 = file("../../data/user_平均/user_purchase_day.pkl",'rb') week_count = pickle.load(f1) f1.close() i = 0 for row in rows: i = i + 1 if i % 10000 == 0: print i id,user_id,report_date,tBalance,yBalance,total_purchase_amt,direct_purchase_amt,purchase_bal_amt,purchase_bank_amt,total_redeem_amt,consume_amt,transfer_amt,tftobal_amt,tftocard_amt,share_amt,category1,category2,category3,category4 = row if user_id not in ret_purchase.keys(): ret_purchase[user_id] = {0:1,1:0,2:0,3:0,4:0,5:0,6:0 } day = (get_time(report_date) - key_time).days day = day%7 ret_purchase[user_id][day] += direct_purchase_amt for user in ret_purchase.keys(): for day in range(7): if week_count[user][day] > 0: ret_purchase[user][day] = float(ret_purchase[user][day])/week_count[user][day] f = file("../../data/user_平均/user_purchase_value.pkl",'wb') pickle.dump(ret_purchase,f) f.close()
def who(self): sorted_clients = {} all_clients = len(threading.enumerate()) - 1 if all_clients == 1: self.client.send("\r\n" + str(all_clients) + " user, up " + \ get_time(time.time(), start_time)) else: self.client.send("\r\n" + str(all_clients) + " users, up " + \ get_time(time.time(), start_time)) # # self.client.send("\r\nUSER TTY ADDRESS PORT CONN LAST WHAT\r\n") self.client.send("\r\nUSER TTY ADDRESS PORT LAST WHAT\r\n") for item in active_clients: sorted_clients[int(active_clients[item]["tty"])] = active_clients[item]["last"] for item, _ in reversed(sorted(sorted_clients.items(), key=operator.itemgetter(1))): self.client.send(active_clients[item]["username"] + \ get_space("user", len(active_clients[item]["username"])) + \ "pts/" + active_clients[item]["tty"] + \ get_space("tty", len(active_clients[item]["tty"]) + 7) + \ active_clients[item]["address"] + \ get_space("address", len(active_clients[item]["address"])) + \ str(active_clients[item]["port"]) + \ get_space("port", len(str(active_clients[item]["port"]))) + \ # get_time(time.time(), active_clients[item]["connection"]) get_time(time.time(), active_clients[item]["last"]) + \ active_clients[item]["what"]) self.client.send("\r\n") if active_clients[int(self.thread_number)]["login"]: change_account(self.username, "commands", data[self.username]["commands"] + 1)
def get_purchase_day(self): ret_purchase = {} sql = "select * from new_balance_purchase_2014" ss = " where (Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407' or SubString(report_date,1,6) = '201408')" #ss = " where (Substring(report_date,1,6) = '201405' or Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407')" sql = sql + ss self.cursor.execute(sql) rows = self.cursor.fetchall() key_time = get_time('20140101') i = 0 for row in rows: i = i + 1 if i % 10000 == 0: print i id,user_id,report_date,tBalance,yBalance,total_purchase_amt,direct_purchase_amt,purchase_bal_amt,purchase_bank_amt,total_redeem_amt,consume_amt,transfer_amt,tftobal_amt,tftocard_amt,share_amt,category1,category2,category3,category4 = row day = (get_time(report_date) - key_time).days if day not in ret_purchase.keys(): ret_purchase[day] = 0 #day = day%7 ret_purchase[day] += 1 f = file("data/user_purchase_day.pkl",'wb') pickle.dump(ret_purchase,f) f.close()
def users(self): sorted_users = {} self.client.send("\r\nUSER LAST\r\n") for item in data: sorted_users[item] = {} sorted_users[item]["last"] = data[item]["last_active"] for item, _ in reversed(sorted(sorted_users.items(), key=operator.itemgetter(1))): self.client.send(item + get_space("user", len(item)) + \ get_time(time.time(), data[item]["last_active"])) self.client.send("\r\n") if active_clients[int(self.thread_number)]["login"]: change_account(self.username, "commands", data[self.username]["commands"] + 1)
import MySQLdb from get_time import * import matplotlib.pyplot as plt import cPickle as pickle conn = MySQLdb.connect(host = "localhost",user = '******',passwd = 'zhao0108',db = 'zhao') cursor = conn.cursor() sql = "select report_date,tBalance from balance_2014" ss = " where (Substring(report_date,1,6) = '201406' or SubString(report_date,1,6) = '201407' or SubString(report_date,1,6) = '201408')" sql = sql + ss cursor.execute(sql) rows = cursor.fetchall() key_time = get_time('20140601') total_money = {} i = 0 for row in rows: i = i + 1 if i % 10000 == 0: print i report_date,tBalance = row day = (get_time(report_date) - key_time).days if day not in total_money.keys(): total_money[day] = 0 total_money[day] += tBalance print total_money f = file("../data/total.pkl",'wb') pickle.dump(total_money,f) f.close() X = []
def login(self): if active_clients[int(self.thread_number)]["login"]: self.client.send("\r\nAlready logged in.\r\n") change_account(self.username, "commands", data[self.username]["commands"] + 1) else: if len(self.command) > 1: self.username = self.command[1] else: self.client.send("\r\nUsername? ") self.receive_string = "" self.get_username() self.count = 0 for self.character in self.receive_string[-3:]: if ord(self.character) in [0, 3, 4, 10, 13]: self.count += 1 self.receive_length = self.count if self.receive_length > 0: self.username = self.receive_string[:-self.receive_length].strip() else: self.username = self.receive_string.strip() if self.username in data: self.client.send("\r\nPassword? ") self.receive_string = "" self.get_password() self.count = 0 for self.character in self.receive_string[-3:]: if ord(self.character) in [0, 3, 4, 10, 13]: self.count += 1 self.receive_length = self.count if self.receive_length > 0: self.password = self.receive_string[:-self.receive_length].strip() else: self.password = self.receive_string.strip() if self.password == data[self.username]["password"]: change_client(int(self.thread_number), "login", True) change_client(int(self.thread_number), "username", self.username) self.client.send("\r\nLogged in as " + self.username.upper() + \ ", last login " + get_time(time.time(), data[self.username]["last_login"]).replace(" ", "") + " ago from " + \ data[self.username]["last_address"] + "\r\n") self.get_message_output("Logged in as " + self.username.upper() + ".") change_account(self.username, "last_login", time.time()) change_account(self.username, "last_address", self.address[0]) change_account(self.username, "all_commands", data[self.username]["all_commands"] + 1) else: self.client.send("\r\nNot valid password.\r\n") else: if self.username != "": self.client.send("\r\nNot a valid username.\r\n") else: self.client.send("\r\n")
# encoding:utf8 import cPickle as pickle from get_time import * from random import random keytime = get_time('20140101') f1 = file("../data/概率/user_purchase_p.pkl",'rb') f2 = file("../data/概率/user_redeem_p.pkl",'rb') f3 = file("../data/user_平均/user_purchase_value.pkl",'rb') f4 = file("../data/user_平均/user_redeem_value.pkl",'rb') f5 = file("../data/关键用户/user_purchase.pkl",'rb') f6 = file("../data/关键用户/user_redeem.pkl",'rb') ret_purchase_p = pickle.load(f1) ret_redeem_p = pickle.load(f2) ret_purchase_value = pickle.load(f3) ret_redeem_value = pickle.load(f4) purchase_user = pickle.load(f5) redeem_user = pickle.load(f6) print len(ret_purchase_value) print len(purchase_user) print len(ret_purchase_p) f1.close() f2.close() f3.close() f4.close() f5.close()