def index(*, page='1'):
page_index = get_page_index(page)
num = yield from Blog.findNumber('count(id)', "status=?", [1])
page = Page(num, page_index)
print('page info：%s' % page)
if page == 0:
blogs = []
else:
#sql = 'SELECT b.* ,COUNT(c.blog_id) as commentsNum FROM `blogs` b LEFT JOIN comments c ON b.id = c.blog_id GROUP BY b.`id` ORDER BY b.`created_at` DESC limit %s,%s'%(page.offset, page.limit)
sql = ' '.join([
'SELECT b.* ,u.`name` AS user_name,u.`avatar`,COUNT(c.blog_id) AS commentsNum ,ca.name_CH,ca.color FROM `blogs` b',
'LEFT JOIN users u ON b.`user_id` = u.`id`',
'LEFT JOIN comments c ON b.id = c.blog_id',
'LEFT JOIN category ca ON b.`category_id`=ca.id',
'WHERE b.`status` = 1', 'GROUP BY b.`id`',
'ORDER BY b.`created_at` DESC',
'LIMIT %s,%s' % (page.offset, page.limit)
])
blogs = yield from Blog.unionSelect(sql)
for blog in blogs:
if blog.cover:
blog.display = "block"
else:
blog.display = "none"
#blogs = yield from Blog.findAll(orderBy='created_at desc', limit=(page.offset, page.limit))
return {'__template__': 'index.html', 'page': page, 'blogs': blogs}
def api_blogs(request, *, userId, page='1'):
page_index = get_page_index(page)
num = yield from Blog.findNumber('count(id)', '`user_id`=?', [userId])
p = Page(num, page_index)
if num == 0:
return dict(page=p, blogs=())
blogs = ()
#blogs = yield from Blog.findAll(orderBy='created_at desc', limit=(p.offset, p.limit))
# sql = ' '.join(['SELECT b.* ,u.`name` AS user_name, u.`admin` FROM `blogs` b',
# 'LEFT JOIN users u ON b.`user_id` = u.`id`'
# 'GROUP BY b.`id`',
# 'ORDER BY b.`created_at` DESC',
# 'LIMIT %s,%s'%(p.offset, p.limit)])
L = [
'SELECT b.* ,u.`name` AS user_name, u.`admin`,COUNT(c.blog_id) AS commentsNum FROM `blogs` b',
'LEFT JOIN users u ON b.`user_id` = u.`id`',
'LEFT JOIN comments c ON b.id = c.blog_id',
"WHERE u.`id` = '%s'" % userId, 'GROUP BY b.`id`',
'ORDER BY b.`created_at` DESC',
'LIMIT %s,%s' % (p.offset, p.limit)
]
sql = ' '.join(L)
blogs = yield from Blog.unionSelect(sql)
for blog in blogs:
blog.type = getBlogType(blog)
blog.status = getBlogStatus(blog)
return dict(page=p, blogs=blogs)
def api_get_users(*, page='1'):
page_index = get_page_index(page)
num = yield from User.findNumber('count(id)', "admin=?", [0])
p = Page(num, page_index)
if num == 0:
return dict(page=p, users=())
users = yield from User.findAll('admin=?', [0],
orderBy='created_at desc',
limit=(p.offset, p.limit))
for u in users:
u.passwd = '******'
u.status = getUseStatus(users[0].status)
return dict(page=p, users=users)
def api_comments(*, page='1'):
page_index = get_page_index(page)
num = yield from Comment.findNumber('count(id)')
p = Page(num, page_index)
if num == 0:
return dict(page=p, comments=())
#comments = yield from Comment.findAll(orderBy='created_at desc', limit=(p.offset, p.limit))
sql = " ".join([
'SELECT c.* ,u.`name` AS user_name, u.`avatar` FROM `comments` c',
'LEFT JOIN users u ON c.`user_id` = u.`id`', 'GROUP BY c.`id`',
'ORDER BY c.`created_at` DESC',
'LIMIT %s,%s' % (p.offset, p.limit)
])
comments = yield from Comment.unionSelect(sql)
return dict(page=p, comments=comments)
def api_comments(request, *, page='1'):
page_index = get_page_index(page)
num = yield from Blog.findNumber('count(id)', '`status`=?', [1])
p = Page(num, page_index)
if num == 0:
return dict(page=p, comments=())
#comments = yield from Comment.findAll(orderBy='created_at desc', limit=(p.offset, p.limit))
sql = " ".join([
'SELECT b.*,COUNT(c.blog_id) AS commentsNum FROM `blogs` b',
'LEFT JOIN comments c ON b.id = c.blog_id',
"WHERE b.`status` = 1 AND b.`user_id`='%s'" % request.__user__.id,
'GROUP BY b.`id`', 'ORDER BY b.`created_at` DESC',
'LIMIT %s,%s' % (p.offset, p.limit)
])
comments = yield from Comment.unionSelect(sql)
if comments:
for c in comments:
c.commentState = getCommentState(c.commentState)
return dict(page=p, comments=comments)
def get_comments(blog_id, *, page='1'):
page_index = get_page_index(page)
num = yield from Comment.findNumber('count(id)', '`blog_id`=?', [blog_id])
page = Page(num, page_index)
blog = yield from Blog.findAll('`id`=?', [blog_id])
if num == 0:
comments = []
else:
sql = " ".join([
'SELECT c.* ,u.`name` AS user_name, u.`avatar` FROM `comments` c',
'LEFT JOIN users u ON c.`user_id` = u.`id`',
"WHERE c.`blog_id`='%s'" % blog_id, 'GROUP BY c.`id`',
'ORDER BY c.`created_at` DESC',
'LIMIT %s,%s' % (page.offset, page.limit)
])
comments = yield from Comment.unionSelect(sql)
return {
'__template__': 'comment/blog_comments.html',
'page': page,
'blog': blog[0],
'comments': comments
}
def followedUser(request,*,page=1):
if request.__user__ is not None:
user_Id = request.__user__.id
# sql = " ".join(['SELECT u.*,f.followed_user FROM follow f ',
# 'LEFT JOIN users u ON f.followed_user = u.id',
# "WHERE f.user_id ='%s'"%user_Id,
# "and f.status=1"])
# follows = yield from Follow.unionSelect(sql)
# sql = " ".join(['SELECT f1.status AS status1,f2.status AS status2 FROM follow f1,follow f2',
# 'WHERE f1.user_id = f2.followed_user AND f1.followed_user = f2.user_id',
# "AND f1.user_id = '%s'"%user_Id])
# follow_relation = yield from Follow.unionSelect(sql)
page_index = get_page_index(page)
num = yield from Follow.findNumber('count(id)',"user_id=? and status=?",[user_Id,1])
page = Page(num,page_index)
newFollows = []
sql = " ".join(['SELECT u.*,f1.user_id,f1.followed_user,f1.status AS status1,f2.status AS status2 FROM follow f1',
'LEFT JOIN follow f2 ON f1.user_id = f2.followed_user AND f1.followed_user = f2.user_id',
'LEFT JOIN users u ON f1.followed_user = u.id',
"WHERE f1.user_id = '%s'"%user_Id,
'LIMIT %s,%s'%(page.offset, page.limit)])
follows = yield from Follow.unionSelect(sql)
for index,follow in enumerate(follows):
newFollow = {}
newFollow['index']= index
newFollow['user_id'] = follow.id
newFollow['user_name'] = follow.name
newFollow['avatar_url'] = follow.avatar
sql2 = " ".join(['SELECT u.* FROM users u WHERE id IN(SELECT t1.followed_user FROM',
'(SELECT user_Id,followed_user FROM follow',
"WHERE user_id = '%s'"%user_Id,
')AS t1 INNER JOIN'
'(SELECT user_Id,followed_user FROM follow',
"WHERE user_id = '%s'"%follow.followed_user,
')AS t2',
'ON t1.followed_user = t2.followed_user) AND u.`status` = 1'])
followInfos = yield from Follow.unionSelect(sql2)
if(len(followInfos) >0):
newFollow['cf_count'] = len(followInfos)
newFollow['cf_info'] = followInfos[0].name
newFollow['followed_user'] = followInfos[0].id
else:
newFollow['cf_count'] = 0
newFollow['cf_info'] = ''
newFollow['followed_user'] = '******'
if follow.status1 == '1' and follow.status2 == '1':
relation_status = 2
is_followed = True
is_following = True
elif follow.status1 == '0' and follow.status2 == '1':
relation_status = 1
is_followed = False
is_following = True
elif follow.status1 == '1' and (follow.status2 == '0' or follow.status2 == None):
relation_status = 1
is_followed = True
is_following = False
else:
relation_status = 0
is_followed = False
is_following = False
newFollow['is_followed'] = is_followed
newFollow['is_following'] = is_following
newFollow['relation_status'] = relation_status
newFollows.append(newFollow)
return {
'__template__': 'user/follow.html',
'page':page,
'follows': newFollows
}
#return dict(data=newFollows)
else:
return web.HTTPFound('/signin')