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