def dt(conn, msg):
if len(msg.text.split()) > 2:
naam = msg.text.split()[1]
error = ' '.join(msg.text.split()[2:])
rowid = mysql.set('INSERT INTO irc_dt (name, error) VALUES (%s, %s)', (naam, error))
conn.send('PRIVMSG %s :DT-fout %i added!\r\n' % (msg.channel, rowid))
else:
parts = msg.text.split()
if len(parts) == 1:
rows, count = mysql.get('SELECT * FROM irc_dt ORDER BY RAND()', '')
if count != 0:
conn.send('PRIVMSG %s :DT-fout %i: [%s] %s\r\n' % (msg.channel, rows[0][0], rows[0][1], rows[0][2]))
elif parts[1].isdigit():
rows, count = mysql.get('SELECT * FROM irc_dt WHERE id=%s', parts[1])
if count != 0:
conn.send('PRIVMSG %s :DT-fout %i: [%s] %s\r\n' % (msg.channel, rows[0][0], rows[0][1], rows[0][2]))
else:
rows, count = mysql.get('SELECT * FROM irc_dt WHERE name=%s ORDER BY RAND()', (parts[1]))
if count != 0:
conn.send('PRIVMSG %s :DT-fout: %s heeft in totaal al %i DT-fouten gemaakt\r\n' % (msg.channel, rows[0][1], count))
else:
conn.send('PRIVMSG %s :DT-fout: %s heeft nog geen DT-fouten gemaakt\r\n' % (msg.channel, parts[1]))
def quote(conn, msg):
parts = msg.text.split()
if len(parts) == 1: # Random quote
rows, count = mysql.get('SELECT * FROM irc_quote ORDER BY RAND()', '')
elif parts[1].isdigit(): # Haal quote nummer zoveel op
rows, count = mysql.get('SELECT * FROM irc_quote WHERE id=%s', parts[1])
else:
rows, count = mysql.get('SELECT * FROM irc_quote WHERE quote LIKE %s ORDER BY RAND()', ("%"+parts[1]+"%"))
if count != 0:
conn.send('PRIVMSG %s :Quote %i: %s\r\n' % (msg.channel, rows[0][0], rows[0][1]))
def init_concept_from_ts(self):
table = 'concept'
sql = 'create table if not exists %s(c_name varchar(50),code varchar(5000))' % table
if table not in self.tables:
if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME, sql):
raise Exception("create table %s failed" % table)
df_concept = ts.get_concept_classified()
df_concept = df_concept[['code', 'c_name']]
df_concept_dict = {}
for index, code_id in df_concept['code'].iteritems():
concept_name = df_concept['c_name'][index]
if concept_name in df_concept_dict:
if not code_id in df_concept_dict:
df_concept_dict[concept_name].append(code_id)
else:
df_concept_dict[concept_name] = []
df_concept_dict[concept_name].append(code_id)
concept_obj = {}
with open("concepts/concepts.json") as f:
concept_obj = json.load(f)
for key, value in concept_obj.items():
if key in df_concept_dict:
if value not in df_concept_dict[key]:
df_concept_dict[key].extend(value)
else:
df_concept_dict[key] = value
for key in df_concept_dict:
df_concept_dict[key] = json.dumps(df_concept_dict[key],
ensure_ascii=False)
df_concept = DataFrame({
'c_name': df_concept_dict.keys(),
'code': df_concept_dict.values()
})
set(self.engine, df_concept, table)
return get(self.engine, SQL % table)
def post(self):
fid = self.get_argument('fid', None)
password = self.get_argument('password', None)
if fid is None or password is None:
response = {'status': 'error', 'next': '/faculty-login'}
self.finish(json_encode(response))
return
fid = json_decode(fid)
password = json_decode(password)
faculty = mysql.get('faculty', {'fid': fid})
if faculty is None:
response = {'status': 'error', 'next': '/faculty-login'}
self.finish(json_encode(response))
return
if len(faculty) != 1:
response = {'status': 'error', 'next': '/faculty-login'}
self.finish(json_encode(response))
return
row = faculty[0]
response = {'status': 'error', 'next': '/faculty-login'}
if row:
if password == row['password']:
role = row['role']
self.set_cookie('role', role)
self.set_cookie('fid', fid)
response = {'status': 'ok', 'next': '/faculty-home'}
self.finish(json_encode(response))
def get_classified_stocks(type_name=MARKET_ALL):
"""
获取股票类型
Return
--------
DataFrame
code :股票代码
name :股票名称
"""
table = "info"
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
df = get(engine, SQL % table)
df = df[[
'code', 'name', 'timeToMarket', 'c_name', 'totals', 'outstanding',
'industry', 'area'
]]
if type_name == MARKET_SH:
df = df.ix[df.code.str[0] == '6']
elif type_name == MARKET_CYB:
df = df.ix[df.code.str[0] == '3']
elif type_name == MARKET_SZ:
df = df.ix[df.code.str[0] == '0']
else:
pass
return df.sort_values('code').reset_index(drop=True)
def init_forward_adjusted_price(self):
_today = datetime.now().strftime('%Y-%m-%d')
start_date = str(datetime.datetime.today().date() +
datetime.timedelta(-1))
end_date = str(datetime.datetime.today().date())
stock_infos = get_classified_stocks(market)
total_data = None
table = 'forward_adjusted_price'
sql = 'create table if not exists `%s`(date varchar(10),\
open float,\
high float,\
close float,\
low float,\
volume float,\
code varchar(8))' % table
if is_trading_day(_today):
for _index, code_id in stock_infos['code'].iteritems():
tmp_data = ts.get_k_data(code_id,
start=start_date,
end=end_date,
autype='qfq')
tmp_data['code'] = code_id
tmp_data = tmp_data.reset_index(drop=False)
total_data = tmp_data if _index == 0 else pd.concat(
[total_data, tmp_data],
ignore_index=True).drop_duplicates()
set(self.engine, total_data, table)
return get(self.engine, SQL % table)
def sync_uid():
result = mysql.get('id', {'name': 'uid'})
if len(result) == 0:
mysql.insert('id', {'name': 'uid', 'num': 0})
logic.global_uid = 0
else:
logic.global_uid = result[0]['num']
def is_trading_day(_date):
table = "calendar"
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
stock_dates_df = get(engine, SQL % table)
return stock_dates_df.query('calendarDate=="%s"' %
_date).isOpen.values[0] == 1
def post(self):
old = self.get_argument('old', None)
password = self.get_argument('password', None)
if old is None or password is None:
response = {'status': 'error'}
self.write(json_encode(response))
return
role = self.get_cookie('role')
fid = self.get_cookie('fid')
old = json_decode(old)
password = json_decode(password)
faculty = mysql.get('faculty', {
'fid': fid,
'password': old,
'role': role
})
if faculty is None or len(faculty) != 1:
response = {'status': 'error'}
self.write(json_encode(response))
return
sql = 'update faculty set password = "******" where fid = "%s"' % (
password, fid)
result = mysql.execute(sql)
if result:
response = {'status': 'ok'}
else:
response = {'status': 'error'}
self.write(json_encode(response))
def init_stock_basic_info(self):
table = 'info'
text = 'create table if not exists %s(code int,\
name varchar(20),\
industry varchar(20),\
c_name varchar(100),\
area varchar(20),\
pe float,\
outstanding float,\
totals float,\
totalAssets float,\
fixedAssets float,\
reserved float,\
reservedPerShare float,\
esp float,\
bvps float,\
pb float,\
timeToMarket int,\
undp float,\
perundp float,\
rev float,\
profit float,\
gpr float,\
npr float,\
limit-up-num int,\
continuous-limit-up-num int,\
holders int)' % table
if table not in self.tables:
if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME,
text):
raise Exception("create table %s failed" % table)
df = ts.get_stock_basics()
df = df.reset_index().rename_axis({'index': 'code'}, axis="columns")
df_concept = ts.get_concept_classified()
df_concept = df_concept[['code', 'c_name']]
df_concept_dict = {}
for index, code_id in df_concept['code'].iteritems():
concept_name = df_concept['c_name'][index]
if code_id in df_concept_dict:
if not concept_name in df_concept_dict:
df_concept_dict[code_id].append(concept_name)
else:
df_concept_dict[code_id] = []
df_concept_dict[code_id].append(concept_name)
for key in df_concept_dict:
df_concept_dict[key] = json.dumps(df_concept_dict[key],
ensure_ascii=False)
df_concept = DataFrame({
'code': df_concept_dict.keys(),
'c_name': df_concept_dict.values(),
'limit-up-num': 0,
'continuous-limit-up-num': 0
})
df_all = pd.merge(df, df_concept, how='left', on=['code'])
df_all.reset_index(drop=True)
set(self.engine, df_all, table)
return get(self.engine, SQL % table)
def is_code_exists(code_id):
table = "info"
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
stock_info = get(engine, SQL % table)
stock_info = stock_info[['code', 'timeToMarket']]
if len(stock_info[stock_info.code == code_id].index.tolist()) > 0:
return True
return False
def explain(conn, msg):
if len(msg.text.split()) > 1:
word = msg.text.split()[1]
rows, count = mysql.get('SELECT * FROM irc_assign WHERE word = %s', (word))
if count != 0:
conn.send('PRIVMSG %s :%s: %s\r\n' % (msg.channel, word, rows[0][2]))
else:
usage = 'Gebruik: ? woord'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def get_stock_volumes(market, data_times):
stock_id_frame = get_classified_stocks(market)
stock_ids = list()
stock_names = list()
stock_turnover = list()
stock_volume = list()
stock_concepts = list()
stock_dates = list()
stock_pchanges = list()
pydate_array = data_times.to_pydatetime()
date_only_array = np.vectorize(lambda s: s.strftime('%Y-%m-%d'))(
pydate_array)
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
for _date in date_only_array:
if is_trading_day(_date):
for code_index, code_id in stock_id_frame['code'].iteritems():
if not is_sub_new_stock(
str(stock_id_frame['timeToMarket'][code_index])):
turnover = 0
volume = 0
stock_name = stock_id_frame['name'][code_index]
stock_concept = stock_id_frame['c_name'][code_index]
hist_data = get_hist_data(engine, code_id, _date)
if hist_data is not None:
hist_data = hist_data[hist_data['volume'] > 0]
hist_data = hist_data[[
'turnover', 'volume', 'p_change'
]]
if not hist_data.empty:
turnover = hist_data['turnover'][0]
p_change = hist_data['p_change'][0]
if turnover > 5 and p_change > 5:
stock_dates.append(_date)
stock_ids.append(code_id)
stock_names.append(stock_name)
volume = hist_data['volume'][0]
stock_volume.append(volume)
stock_turnover.append(turnover)
stock_concepts.append(stock_concept)
stock_pchanges.append(p_change)
df = DataFrame({
'date': stock_dates,
'code': stock_ids,
'name': stock_names,
'turnover': stock_turnover,
'volume': stock_volume,
'c_name': stock_concepts,
'p_change': stock_pchanges
})
table = 'turnover'
sql = 'create table if not exists %s(date varchar(10),code varchar(10),name varchar(20),volume float,p_change float,c_name varchar(5000))' % table
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
set(engine, df, table)
return get(engine, SQL % table)
def init_trading_day(self):
table = 'calendar'
sql = 'create table if not exists `%s`(calendarDate varchar(10),isOpen int)' % table
if table not in self.tables:
if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME, sql):
raise Exception("create table %s failed" % table)
trading_day = ts.trade_cal()
if trading_day is not None:
set(self.engine, trading_day, str(table))
return get(self.engine, SQL % table)
def get(self):
#import pdb
#pdb.set_trace()
fid = self.get_cookie('fid')
self.clear_cookie('role')
roles = mysql.get('faculty', {'fid': fid})
if len(roles) == 0:
return
roles = roles[0]['role']
roles = roles.split(',')
self.render('faculty-role.html', roles=roles, fid=fid)
def post(self):
#import pdb
#pdb.set_trace()
fid = self.get_argument('fid')
passwd = self.get_argument('passwd')
result = mysql.get('password', {'fid': fid})
response = {'status': 'error'}
if len(result) == 1:
if passwd == result[0]['passwd']:
self.set_cookie('fid', fid)
response = {'status': 'ok'}
self.write(json_encode(response))
def get_post_trading_day(_date):
table = 'calendar'
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
df = get(engine, SQL % table)
_index = df[df.calendarDate == _date].index.tolist()[0]
if _index > 0:
_tindex = _index
while _tindex < len(df):
_tindex += 1
if df['isOpen'][_tindex] == 1:
return df['calendarDate'][_tindex]
raise Exception("can not find post trading day.")
def collect_concept_volume_price(data_times):
table = 'concept'
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
concepts = get(engine, SQL % table)
stock_infos = get(engine, SQL % 'info')
pydate_array = data_times.to_pydatetime()
date_only_array = np.vectorize(lambda s: s.strftime('%Y-%m-%d'))(
pydate_array)
tables = get_all_tables(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME)
infos = dict()
for _date in date_only_array:
if is_trading_day(_date):
for index, row in concepts.iterrows():
concept_name = row['c_name']
infos[concept_name] = list()
codes = json.loads(row['code'])
for code_id in codes:
if is_code_exists(code_id):
if is_after_release(code_id, _date):
if code_id in tables:
hist_data = get_hist_data(
engine, code_id, _date)
rate = hist_data['p_change']
if len(rate) > 0 and rate[0] > 5:
volume = hist_data['volume'][0]
pre_price = hist_data['close'][
0] + hist_data['price_change'][0]
c_index = stock_infos[
stock_infos.code ==
code_id].index.tolist()[0]
code_name = stock_infos.loc[c_index,
'name']
up_date = get_highest_time(
code_id, _date, pre_price)
infos[concept_name].append(
(code_id, code_name, rate[0], up_date,
volume))
return infos
def post(self):
fid = self.get_cookie('fid')
cookdo = []
result = mysql.get('cook_do', {'fid': fid})
flag = False
for one in result:
if one['did'] == 'all':
flag = True
break
cookdo.append(one['did'])
if flag == True:
cookdo = ['all']
response = {'status': 'ok', 'cookdo': cookdo}
self.write(json_encode(response))
def get_concepts(type_name):
"""
获取题材类型
Return
--------
DataFrame
code :股票代码
name :股票名称
"""
table = "concept"
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
df = get(engine, SQL % table)
return df.sort_values('code').reset_index(drop=True)
def post(self):
fid = self.get_cookie('fid')
passwd1 = self.get_argument('passwd1')
passwd2 = self.get_argument('passwd2')
result = mysql.get('password', {'fid': fid})
#print result
response = {'status': 'failure'}
if result and result[0] and result[0]['passwd'] == passwd1:
sql = 'update password set passwd = "%s" where fid = "%s"' % (passwd2, fid)
#print sql
r = mysql.execute(sql)
#print r
if r:
response = {'status': 'success'}
self.write(json_encode(response))
def is_after_release(code_id, _date):
table = "info"
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
stock_info = get(engine, SQL % table)
stock_info = stock_info[['code', 'timeToMarket']]
_index = stock_info[stock_info.code == code_id].index.tolist()[0]
time2Market = stock_info.loc[_index, 'timeToMarket']
if time2Market:
t = time.strptime(str(time2Market), "%Y%m%d")
y, m, d = t[0:3]
time2Market = datetime(y, m, d)
if (datetime.strptime(_date, "%Y-%m-%d") - time2Market).days > 0:
return True
return False
def post(self):
did = int(json_decode(self.get_argument('did')))
res = mysql.get('diet', {'did': did})
if res and res[0]:
picture = res[0]['picture']
full_path = os.path.join(os.path.dirname(__file__),
'static/pictures/' + picture)
os.remove(full_path)
mysql.delete('diet', {'did': did})
data.diet = mysql.get_all('diet')
response = {'status': 'ok'}
self.finish(json_encode(response))
return
response = {'status': 'err'}
self.finish(json_encode(response))
def post(self):
did = self.get_argument('did')
if did in logic.diet:
logic.diet.pop(did)
result = mysql.get('diet', {'did': did})
if result and result[0]:
picture = result[0]['pic']
full_path = os.path.join(logic.data_dir, 'pictures/' + picture)
if mysql.delete('diet', {'did': did}) and picture != '':
if os.path.isfile(full_path):
os.remove(full_path)
response = {'status': 'ok'}
self.finish(json_encode(response))
return
response = {'status': 'error'}
self.finish(json_encode(response))
def reassign(conn, msg):
if msg.user == settings.irc_OWNER or utils.isadmin(conn, msg):
if len(msg.text.split()) > 2:
word = msg.text.split()[1]
defin = ' '.join(msg.text.split()[2:])
rows, count = mysql.get('SELECT * FROM irc_assign WHERE word=%s', (word))
if count == 0:
conn.send('PRIVMSG %s :%s is not defined yet. Use !assign word def to assign it.\r\n' % (msg.channel, word))
else:
rowid = mysql.set('UPDATE irc_assign SET def=%s WHERE word=%s', (defin, word))
conn.send('PRIVMSG %s :%s reassigned to: %s\r\n' % (msg.channel, word, defin))
else:
usage = 'Gebruik: !reassign woord definitie'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
else:
usage = 'Je moet een administrator zijn om dit commando te kunnen uitvoeren.'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def assign(conn, msg):
if msg.user == settings.irc_OWNER or utils.isadmin(conn, msg):
if len(msg.text.split()) > 2:
word = msg.text.split()[1]
defin = ' '.join(msg.text.split()[2:])
rows, count = mysql.get('SELECT * FROM irc_assign WHERE word=%s', (word))
if count > 0:
conn.send('PRIVMSG %s :%s is already defined: %s\r\n' % (msg.channel, rows[0][1], rows[0][2]))
else:
rowid = mysql.set('INSERT INTO irc_assign (word, def) VALUES (%s, %s)', (word, defin))
conn.send('PRIVMSG %s :%s added to assign list.\r\n' % (msg.channel, word))
else:
usage = 'Gebruik: !assign woord definitie'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
else:
usage = 'Je moet een administrator zijn om dit commando te kunnen uitvoeren.'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def lijst(conn, msg):
aantal = 5
if len(msg.text.split()) > 1:
getal = msg.text.split()[1]
if getal.isdigit():
getal = int(getal)
if getal > 0:
aantal = getal
if getal > 15:
aantal = 15
rows, count = mysql.get('SELECT name, COUNT(*) FROM `irc_dt` GROUP BY name ORDER BY COUNT(*) DESC', '')
conn.send('PRIVMSG %s :Nr.: Naam - Aantal\r\n' % (msg.channel))
if count < aantal:
aantal = count
conn.send('PRIVMSG %s :DT-fouten top %i:\r\n' % (msg.channel, aantal))
for row in range(0, aantal):
conn.send('PRIVMSG %s : %i: %s - %s\r\n' % (msg.channel, (row+1), rows[row][0], rows[row][1]))
def __init__(self, fid):
self.fid = fid
self.name = faculty.get(fid)['name']
self.current = None
self.byway = []
self.doing = []
self.done = []
#self.deny = []
self.cookdo = []
self.waiters = set()
self.stamp = time.time()
self.queue = []
#init self.cookdo
result = mysql.get('cook_do', {'fid': self.fid})
all = False
for one in result:
if one['did'] == 'all':
all = True
self.cookdo.append(one['did'])
if all:
self.cookdo = ['all']
def lijst(conn, msg):
aantal = 10
if len(msg.text.split()) > 1:
getal = msg.text.split()[1]
if getal.isdigit():
getal = int(getal)
if getal > 0:
aantal = getal
if getal > 30:
aantal = 30
rows, count = mysql.get('SELECT * FROM irc_assign ORDER BY id DESC', '')
if aantal > count:
aantal = count
out=[]
for row in range(0, aantal):
out.append(rows[row][1])
conn.send('PRIVMSG %s :Last %i assigns: %s\r\n' % (msg.channel, aantal, ', '.join(out)))
def __init__(self, table):
self.table = table.upper()
self.seats = mysql.get('desks', {'desk': table})[0]['num']
self.pid = 0
self.gdemand = ''
self.comment = ''
self.orders = []
self.left = []
self.doing = []
self.done = []
self.cancel = [] #canceled by customer or waiter
self.payed = []
self.delete = [] #deleted by cashier
self.submit = 0
self.last = 0
self.stamp = time.time()
self.waiters = set()
self.history = []
self.power = 0
def active(conn, msg):
rows, count = mysql.get('SELECT * FROM irc_tell WHERE `to`=%s', (msg.user))
for row in rows:
conn.send('PRIVMSG %s :%s: [%s] %s: %s\r\n' % (msg.channel, row[3], row[1], row[2], row[4]))
count = mysql.set('DELETE FROM irc_tell WHERE `id`=%s', row[0])
def get(table, time_from, time_to):
if table in ('diet', 'category', 'faculty'):
res = mysql.get(table, time_from, time_to)
else:
res = myredis.get(table, time_from, time_to)
return res
def daily_analysis(data_times):
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
stock_infos = get(engine, SQL % 'info')
def get(table, con):
res = mysql.get(table, con)
return res
