def getNewestReply(page=0, count=40):
"""
獲取最新的回復消息
:param page:
:param count:
:return: 返回dict， 包括回復相關的所有數據和話題標題，話題作者
"""
res = None
try:
res = dbsession.query(Reply, Article, User.u_name, User.u_headurl).join(Article, Article.id==Reply.a_id)\
.filter(User.id == Reply.u_id) \
.order_by(desc(Reply.r_time)).limit(count).offset(page*count) \
.all()
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
tmp = it[0].to_json()
tmp.update(it[1].to_json())
tmp['uname'] = it[2]
tmp['uheadurl'] = it[3]
# 計算距當前時間多久
tmp['time'] = timeutil.Datetime2HowLong(str(it[0].r_time))
lst.append(tmp)
return lst
def quite_contest(uid, id):
"""
用戶退出比賽
:param uid:
:param id:
:return:
"""
contest_detail = None
try:
contest_detail = dbsession.query(ContestDetail).filter(
and_(ContestDetail.c_id == id,
ContestDetail.u_id == uid)).one()
except NoResultFound as e:
return
except Exception as e:
dbsession.rollback()
raise e
contest_detail.c_status = 1
try:
dbsession.merge(contest_detail)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def update_user(user=None, **kwargs):
"""
:param kwargs:
:return:
"""
user_obj = {}
if isinstance(user, User):
user_obj = user
logging.debug("user is User instance.")
print("user is User instance.")
else:
if 'id' in kwargs:
user_obj = dbsession.query(User).filter_by(
id=kwargs['id']).one()
for k, v in kwargs.items():
if k == 'id':
continue
if v is not None:
setattr(user_obj, k, v)
try:
logging.debug(user_obj)
print(user_obj)
dbsession.merge(user_obj)
dbsession.commit()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
def get_contest_user_info(uid, cid):
"""
獲取用戶在某一場次比賽中的表現：
:param uid:
:param cid:
:return: json 串
"""
info = None
try:
info = dbsession.query(
ContestDetail, User.u_name, User.u_headurl).filter(
and_(ContestDetail.u_id == uid,
ContestDetail.c_id == cid)).join(
User, User.id == ContestDetail.u_id).one()
except NoResultFound as e:
return []
except Exception as e:
dbsession.rollback()
raise e
logging.debug(info)
if info:
tmp = info[0].to_json()
tmp['uname'] = info[1]
tmp['uheadurl'] = info[2]
return tmp
return []
def getArticleByUid(uid, page=0, count=40):
"""
獲取用戶發表的
:param uid:
:return:
"""
res = None
try:
res = dbsession.query(Article, User.u_name, User.u_headurl).join(User, Article.u_id == User.id)\
.filter(Article.u_id==uid).order_by(desc(Article.a_pub_time)).limit(count).offset(page*count).all()
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
tmp = it[0].to_json()
tmp['uname'] = it[1]
tmp['uheadurl'] = it[2]
# 計算距當前時間多久
tmp['time'] = timeutil.Datetime2HowLong(str(it[0].a_pub_time))
lst.append(tmp)
return lst
def get_contest_list(page=0, count=20):
"""
獲取比賽列表：默認獲取的是未結束（關閉）的
:param page:
:param count:
:return:
"""
ret = None
try:
# ret = dbsession.query(Contest).all()
# ret = dbsession.query(Contest).limit(count).offset(page * count).all()
ret = dbsession.query(
Contest, User.u_name).filter(Contest.u_id == User.id).order_by(
Contest.c_create_time).limit(count).offset(page *
count).all()
except Exception as e:
dbsession.rollback()
raise e
lst = []
if ret:
for it in ret:
tmp = it[0].to_json()
tmp['createuser'] = it[1]
lst.append(tmp)
return lst
def delReply(uid, rid):
"""
刪除一個評論
:param uid:
:param rid:
:return:
"""
recode = None
try:
recode = dbsession.query(Reply).filter(
and_(Reply.id == rid, Reply.u_id == uid)).one()
except NoResultFound as e:
raise e
except Exception as e:
dbsession.rollback()
raise e
if recode:
try:
recode.r_status = 1
dbsession.merge(recode)
dbsession.commit()
### 這個回復是回復的話題， 所有在此回復上的回復消息 都應該刪除
if recode.r_reply_id == 0:
dbsession.query(Reply).filter(
Reply.r_reply_id == recode.id).update(
{Reply.r_status: 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def delArticle(uid, id):
"""
刪除一個話題， 邏輯刪除
:param uid:
:param id:
:return: None, throw exception if error
"""
article = None
try:
article = dbsession.query(Article).filter(
and_(Article.id == id, Article.u_id == uid)).one()
except NoResultFound as e:
return "話題找不到了。。。"
except Exception as e:
dbsession.rollback()
raise e
if not article:
return "話題找不到了。。。"
article.a_status = 2 # 關閉（邏輯刪除）
try:
dbsession.merge(article)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def getArticleById(id):
"""
獲取文章數據
:param id:
:return:
"""
article = None
try:
query = dbsession.query(Article, User.u_name,
User.u_headurl).filter(
and_(Article.id == id,
Article.u_id == User.id))
article = query.one()
except NoResultFound as e:
raise e
except Exception as e:
dbsession.rollback()
raise e
if article:
retdata = article[0].to_json()
retdata['uname'] = article[1]
retdata['uheadurl'] = article[2]
# 計算距當前時間多久
retdata['time'] = timeutil.Datetime2HowLong(
str(article[0].a_pub_time))
return retdata
return None
def add_contest(uid, title, desc, stime, etime, money, logo):
"""
創建一個比賽：
:param uid:
:param title: 比賽標題
:param desc: 描述
:param stime: 開始時間：不能是當天或以前
:param etime: 結束時間，不能小於開始時間
:param money: 初始資金
:param logo: logo 圖片地址
:return:
"""
contest = Contest()
contest.u_id = uid
contest.c_title = title
contest.c_explain = desc
contest.c_start_date = stime
contest.c_end_date = etime
contest.c_logo_url = logo
contest.c_default_capital = money * 10000
try:
dbsession.add(contest)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
return contest
def get_contest_detail_rank_users(id, page=0, count=20):
"""
獲取單個比賽的排名數據：
:param id: 比賽id
:return:
"""
res = None
try:
res = dbsession.query(
ContestDetail.c_win_rate, ContestDetail.u_id,
User.u_name).filter(
and_(ContestDetail.c_id == id,
ContestDetail.c_status != 1)).join(
User, User.id == ContestDetail.u_id).order_by(
ContestDetail.c_win_rate.desc()).limit(
count).offset(page * count).all()
except Exception as e:
dbsession.rollback()
raise e
lst = []
if res:
idx = 1
for it in res:
t = {}
t['rate'] = it[0] # 盈利率
t['id'] = it[1] # uid
t['user'] = it[2] # 用戶名
t['rank'] = idx # 排名
lst.append(t)
idx += 1
return lst
def get_user_bymobile(mobile):
if not isinstance(mobile, str):
raise TypeError("name 类型错误", mobile)
try:
user = dbsession.query(User).filter_by(u_mobilephone=mobile).one()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
return user
def get_user_byemail(email):
if not isinstance(email, str):
raise TypeError("name 类型错误", email)
try:
user = dbsession.query(User).filter_by(u_email=email).one()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
return user
def getReplyByArticleId(aid, page=0, count=40):
"""
獲取話題的評論
:param aid:
:return:
"""
res = None
try:
query = dbsession.query(Reply, User.u_name, User.u_headurl).filter(
and_(Reply.a_id == aid, User.id == Reply.u_id))
query = query.limit(count).offset(count * page)
res = query.all()
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
recode = it[0].to_json()
recode['uname'] = it[1]
recode['uheadurl'] = it[2]
# type: 2
# 類型為2， 描述為： 回復了帖子
recode['type'] = 2
recode['type_desc'] = u"評論了帖子"
# 計算距當前時間多久
recode['time'] = timeutil.Datetime2HowLong(str(it[0].r_time))
# type： 3
# 回復了用戶的評論
if it[0].r_reply_id != 0:
aritcleid = it[0].r_reply_id
try:
query = dbsession.query(Reply, User.u_name).filter(
and_(Reply.id == aritcleid, User.id == Reply.u_id))
result = query.one()
except Exception as e:
dbsession.rollback()
raise e
recode['type'] = 3
recode['type_desc'] = u"回復了"
recode['reply_uname'] = result[1]
lst.append(recode)
return lst
def get_user_byid(id):
"""
:param id: 用户id
:return: 用户对象
"""
user = None
try:
user = dbsession.query(User).get(id)
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
return user
def get_stock_list(uid, cid, page=1, lmt=100):
"""
列出用戶的持股信息，默認100條
return: a list,
:param uid:
:param cid:
:param page:
:param lmt:
"""
if not all((uid, cid)):
logging.error("uid err", uid)
raise Exception("parameter err")
sql = "select * from tb_user_contest_positions where user_id = %s and stock_count > 0 and contest_id=%d" \
" order by updated_at desc" \
" limit %d, %d" % (uid, cid, (page-1)*lmt, lmt)
result = []
try:
query_res = dbsession.execute(sql)
dbsession.commit()
query_res = query_res.fetchall()
except Exception as e:
dbsession.rollback()
logging.error(e)
raise
# table column
headers = ("id", "created_at", "updated_at", "deleted_at", "uid",
"name", "code", "count", "price", "freeze", "cid")
for item in query_res:
dct = dict(zip(headers, item))
tm = dct['created_at']
dct['created_at'] = tm.strftime(
"%Y-%m-%d %H:%M:%S") if tm else None
tm = dct['updated_at']
dct['updated_at'] = tm.strftime(
"%Y-%m-%d %H:%M:%S") if tm else None
tm = dct['deleted_at']
dct['deleted_at'] = tm.strftime(
"%Y-%m-%d %H:%M:%S") if tm else None
dct['price'] = float(dct['price'])
dct['freeze'] = float(dct['freeze'])
result.append(dct)
return result
def delete_user(arg):
user = None
try:
if isinstance(arg, int):
user = dbsession.query(User).filter(User.id == arg).one()
if user is not None:
dbsession.delete(user)
dbsession.commit()
else:
logging.error("delete_user:user obj is not a User instance.")
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
def addReplyReply(uid, aid, rid, content):
"""
回復別人的回復消息
:param uid:
:param aid:
:param rid:
:return:
"""
reply = Reply()
reply.r_reply_id = rid
reply.u_id = uid
reply.a_id = aid
reply.r_content = content
try:
dbsession.add(reply)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def addReply(uid, aid, content):
"""
添加一個回復
:param uid:
:param aid:
:return:
"""
reply = Reply()
reply.u_id = uid
reply.a_id = aid
reply.r_content = content
try:
dbsession.add(reply)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
return reply.id
def close_contest(id):
"""
關閉比賽, 結束比薩不在這裏處理，在每天的收盤后會有定時任務處理！
:param id:
:return:
"""
test = None
try:
test = dbsession.query(Contest).filter(
and_(Contest.id == id, Contest.c_status != 3,
Contest.c_status != 2)).one()
if test:
test.c_status = 3 # 關閉
dbsession.merge(test)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def addArticle(uid, title, content):
"""
添加一個話題
:param uid:
:param title:
:param content:
:return:
"""
article = Article()
article.u_id = uid
article.a_title = title
article.a_content = content
try:
dbsession.add(article)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
return article.id
def getNewestArticles(page=0, count=20):
"""
按最新排序獲取，分頁
:param page:
:param count:
:return:
"""
res = None
try:
query = dbsession.query(
Article, User.u_name,
User.u_headurl).filter(User.id == Article.u_id).order_by(
desc(Article.a_pub_time)).limit(count).offset(page * count)
res = query.all()
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
recode = it[0].to_json()
recode['uname'] = it[1]
recode['uheadurl'] = it[2]
# 計算距當前時間多久
recode['time'] = timeutil.Datetime2HowLong(str(it[0].a_pub_time))
# 類型： 默認這裏讀出來的都是 文章， 文章默認是使用1 type
# 描述為：發表了帖子
recode['type'] = 1
recode['type_desc'] = u"發表了帖子"
lst.append(recode)
return lst
def get_user_contest_list(uid, all=False):
"""
獲取用戶參加的比賽
:param uid:
:param all: 是否是已完成的 False:未結束的， True 全部的
:return:
"""
res = None
try:
query = dbsession.query(Contest, ContestDetail, User.u_name)\
.join(ContestDetail, Contest.id==ContestDetail.c_id)\
.filter(ContestDetail.u_id==uid)\
.join(User, User.id==Contest.u_id)
# query = dbsession.query(ContestDetail, Contest, User.u_name).join(User, User.id==ContestDetail.u_id).filter(Contest.id==ContestDetail.c_id).filter(ContestDetail.u_id==uid)
if not all:
query = query.filter(
or_(Contest.c_status == 1, Contest.c_status == 0))
res = query.order_by(Contest.c_create_time).all()
# print(query)
except Exception as e:
dbsession.rollback()
raise e
if not res:
return []
lst = []
for it in res:
contest_dct = it[0].to_json()
contest_detail_dct = it[1].to_json()
dct = contest_dct
dct.update(contest_detail_dct)
dct['createuser'] = it[2]
lst.append(dct)
return lst
def upcountReply(uid, rid):
"""
給評論點贊, 如果電讚了得 就取消點讚
:param id:
:param uid:
:return:
"""
recode = None
try:
recode = dbsession.query(ReplyUpcounts).filter(
and_(ReplyUpcounts.r_id == rid,
ReplyUpcounts.u_id == uid)).one()
except NoResultFound as e:
pass
except Exception as e:
dbsession.rollback()
raise e
if not recode:
recode = ReplyUpcounts()
recode.u_id = uid
recode.r_id = rid
try:
dbsession.add(recode)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
try:
dbsession.query(Reply).filter(Reply.id == rid).update(
{Reply.r_upcounts: Reply.r_upcounts + 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
else:
try:
dbsession.delete(recode)
dbsession.commit()
dbsession.query(Reply).filter(Reply.id == rid).update(
{Reply.r_upcounts: Reply.r_upcounts - 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
def upcountArticle(aid, uid):
"""
給文章點贊, 如果電讚了得 就取消點讚
:param id:
:param uid:
:return:
"""
recode = None
try:
recode = dbsession.query(ArticleUpcounts).filter(
and_(ArticleUpcounts.a_id == aid,
ArticleUpcounts.u_id == uid)).one()
except NoResultFound as e:
pass
except Exception as e:
dbsession.rollback()
raise e
if not recode:
recode = ArticleUpcounts()
recode.u_id = uid
recode.a_id = aid
try:
dbsession.add(recode)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
try:
dbsession.query(Article).filter(Article.id == aid).update(
{Article.a_upcounts: Article.a_upcounts + 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
else:
try:
dbsession.delete(recode)
dbsession.commit()
dbsession.query(Article).filter(Article.id == aid).update(
{Article.a_upcounts: Article.a_upcounts - 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
def sale_stock(uid, stock_code, stock_name, stock_count):
"""
賣出
:param uid:
:param stock_code:
:param stock_name:
:param stock_count:
:return: if success return True,"" ortherwise return False, "errmsg" or raise the exceptions
"""
if not all([uid, stock_code, stock_name, stock_count]):
logging.error("參數錯誤：", uid, stock_code, stock_name, stock_count)
raise Exception("參數錯誤!")
if not all( [isinstance(uid, str), isinstance(stock_code, str),
isinstance(stock_name, str), isinstance(stock_count, int)]):
logging.error("參數錯誤：", uid, stock_code, stock_name, stock_count)
raise Exception("參數錯誤!")
# 查詢是否已經購買此股
stock = None
try:
stock = dbsession.query(UserStock).filter_by(u_id=uid).filter_by(s_stock_code=stock_code).one()
except NoResultFound as e:
dbsession.rollback()
logging.error(e)
if stock == None:
logging.info("用戶沒有持有持有該股票：", uid, stock_code)
return False, "沒有持有持有該股票"
### 剛好清倉
if stock.s_stock_count == stock_count:
try:
dbsession.delete(stock)
dbsession.commit()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
## 數據有錯誤
elif stock.s_stock_count < stock_count:
logging.info("用戶未持有這麽多股份：", uid, stock_code, stock_count)
return False, "未持有這麽多股份"
#正常買出
else:
stock.s_stock_count -= stock_count
try:
dbsession.merge(stock)
dbsession.commit()
except Exception as e:
logging.error(e)
dbsession.rollback()
raise e
return True, ""
def add_stock(uid, stock_code, stock_name, stock_count, stock_price):
"""
no return val
raise 參數錯誤異常
:param uid:
:param stock_code:
:param stock_name:
:param stock_count:
:param stock_price: 成本價
:return:
"""
if not all([uid, stock_code, stock_name, stock_count, stock_price]):
logging.error("參數錯誤：", uid, stock_code, stock_name, stock_count, stock_price)
raise Exception("參數錯誤!")
### 查詢是否已經買過該股票，是：添加持股數，否添加紀錄
stock = None
try:
stock = dbsession.query(UserStock).filter_by(u_id=uid).filter_by(s_stock_code=stock_code).one()
except NoResultFound as e:
dbsession.rollback()
logging.error(e)
# 沒記錄，沒買過
if stock is None:
stock = UserStock()
stock.u_id = uid
stock.s_stock_price = stock_price
stock.s_stock_code = stock_code
stock.s_stock_name = stock_name
stock.s_stock_count = stock_count
try:
dbsession.add(stock)
dbsession.commit()
except Exception as e:
dbsession.rollback()
logging.error(e)
raise e
else:
price = ((float)(stock.s_stock_price) * stock.s_stock_count + stock_price*stock_count) / (stock.s_stock_count+stock_count)
stock.s_stock_count += stock_count
stock.s_stock_price = price
try:
dbsession.merge(stock)
dbsession.commit()
except Exception as e:
dbsession.rollback()
logging.error(e)
raise e
def upcountArticleOnly(aid, uid):
"""
給文章點贊：只點贊，不存在重複點贊
:param aid:
:param uid:
:return: success：return true,""
fail： return false,"失敗原因"
:exception: exception returned if sql err
"""
recode = None
try:
recode = dbsession.query(ArticleUpcounts).filter(
and_(ArticleUpcounts.a_id == aid,
ArticleUpcounts.u_id == uid)).one()
except NoResultFound as e:
pass
except Exception as e:
dbsession.rollback()
raise e
if not recode:
recode = ArticleUpcounts()
recode.u_id = uid
recode.a_id = aid
try:
dbsession.add(recode)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
try:
dbsession.query(Article).filter(Article.id == aid).update(
{Article.a_upcounts: Article.a_upcounts + 1})
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
return True, ""
else:
return False, "你已經點過贊了"
def join_contest(uid, id):
"""
用戶參加比賽：根據id獲取比賽的相關信息，例如初始資金，將數據填入tb_contest_detail中
:param uid:
:param id:
:return:
"""
test = None
user_contest_detail = None
try:
test = dbsession.query(Contest).filter_by(id=id).one()
except Exception as e:
dbsession.rollback()
raise e
try:
user_contest_detail = dbsession.query(ContestDetail).filter(
and_(ContestDetail.u_id == uid,
ContestDetail.c_id == test.id)).one()
except NoResultFound as e:
pass
except Exception as e:
dbsession.rollback()
raise e
# 已經存在數據，説明已經加入比賽
if user_contest_detail:
raise Exception("已經加入該比賽！")
user_contest_detail = ContestDetail()
user_contest_detail.c_join_time = datetime.now()
user_contest_detail.c_money = test.c_default_capital # 初始資金
user_contest_detail.c_id = test.id
user_contest_detail.u_id = uid
try:
dbsession.add(user_contest_detail)
dbsession.commit()
except Exception as e:
dbsession.rollback()
raise e
def list_orders(uid, cid, type=None, page=1, count=40, finished=None):
"""
获取订单列表.
:param uid: 用户id
:param cid: 比赛id （0为非比赛委托单）
:param page:
:param count:
:param type: 交易类型
TRADE_TYPE_NONE = iota
TRADE_TYPE_BUY # 买入
TRADE_TYPE_SALE # 卖出
:return:
"""
uid = (int)(uid)
cid = (int)(cid)
page = (int)(page)
count = (int)(count)
sql = 'select * from tb_orders where '
where_cond = [
'user_id=%d' % uid,
'contest_id=%d' % cid,
]
order_by = ' order by updated_at desc '
limit_sql = ' limit %d, %d ' % ((page - 1) * count, count)
if type is not None:
# 交易类型过滤
where_cond.append('trade_type=%d' % type)
if finished == 'finished':
where_cond.append('order_status!=0')
elif finished == 'unfinished':
where_cond.append('order_status=0')
# 执行sql
sql = sql + ' and '.join(where_cond) + \
order_by + limit_sql
logging.debug('sql: %s' % sql)
raws = dbsession.execute(sql).fetchall()
dbsession.commit()
if not raws:
dbsession.rollback()
return []
# table colume
colums = ('id', 'submit_time', 'updated_at', 'deleted_at', 'uid',
'name', 'code', 'price', 'amount', 'transfer_fee',
'brokerage', 'volume', 'trade_type', 'type', 'status', 'cid')
result = []
for item in raws:
dct = dict(zip(colums, item))
tm = dct['submit_time']
dct['submit_time'] = tm.strftime(
"%Y-%m-%d %H:%M:%S") if tm else None
tm = dct['updated_at']
dct['updated_at'] = tm.strftime(
"%Y-%m-%d %H:%M:%S") if tm else None
tm = dct['deleted_at']
dct['deleted_at'] = tm.strftime(
"%Y-%m-%d %H:%M:%S") if tm else None
dct['price'] = float(dct['price'])
dct['brokerage'] = float(dct['brokerage'])
dct['transfer_fee'] = float(dct['transfer_fee'])
dct['volume'] = float(dct['volume'])
result.append(dct)
return result
