def id_list(user_id, album_id, current_id=1, size=1, relation=0):
# type: (int, int, int, int, int) -> (list, int, int, int, int)
user_id = int(user_id)
album_id = int(album_id)
current_id = int(current_id)
size = int(size)
relation = int(relation)
if relation == -1:
level_sql = ''
elif relation == 0:
level_sql = 'and pic.level=0'
elif relation == 1:
level_sql = 'and pic.level<=1'
else:
return False, [], []
sql_format = \
'(' + 'SELECT pic.id, pic.title, pic.description, pic.level, \
file.id as "fileId", file.filename as "url", \
file.exif_timestamp as `exif_timestamp`, file.exif_lalo as `exif_lalo` \
FROM pic left join file on pic.file_id = file.id \
where pic.user_id=%(user_id)s and pic.album_id=%(album_id)s {} and {} \
{} limit {}'.format('{}', '{}', '{}', '{}') + ')'
sql = sql_format.format(level_sql, 'pic.id < %ld' % current_id, 'order by pic.id desc', size)
sql += 'UNION'
sql += sql_format.format(level_sql, 'pic.id >= %ld' % current_id, 'order by pic.id', size + 1)
result, count, new_id = dao.execute_sql(sql, kv=True, args={
'user_id': user_id,
'album_id': album_id
})
pre_id = []
next_id = []
for row in result:
if row['id'] < current_id:
next_id.append(row)
elif row['id'] > current_id:
pre_id.append(row)
else:
current = row
row['oUrl'] = url_with_name(row['url'], original=True)
row['qUrl'] = url_with_name(row['url'])
row['url'] = url_with_name(row['url'], thumb=True)
# pre_id.reverse()
return True, pre_id, next_id, current
def page_list(other_id, album_id, page=1, size=10, relation=0):
# type: (int, int, int, int, int) -> (list, int, int, int, int)
size = int(size)
other_id = int(other_id)
album_id = int(album_id)
page = int(page)
if page < 1:
page = 1
sql = "SELECT pic.id, pic.title, pic.description, pic.level, file.timestamp, file.exif_timestamp, file.filename\
FROM pic\
left join file on pic.file_id = file.id\
where user_id=%(other_id)s and album_id=%(album_id)s {} order by pic.id desc".format('{}')
if relation == -1:
sql = sql.format('')
elif relation == 0:
sql = sql.format('and pic.level=0')
elif relation == 1:
sql = sql.format('and pic.level<=1')
else:
return None, 0, 0, 0, 0
result, count, new_id = dao.execute_sql(sql, ret=False, args={
'other_id': other_id,
'album_id': album_id
})
page_count = int(operator.truediv(count - 1, size)) + 1
page = min(page, page_count)
sql += ' limit %d offset %d' % (size, (page - 1) * size)
result, this_page_count, new_id = dao.execute_sql(sql, kv=True, args={
'other_id': other_id,
'album_id': album_id
})
page = page if this_page_count > 0 else page_count
for row in result:
row['url'] = url_with_name(row['filename'], thumb=True)
row['qUrl'] = url_with_name(row['filename'])
return result, page_count, page, size, count
def new_pic(user_id, pic_file, conn=None, album_id=-1, title='', desc='', level=0, full_url=False, original=False):
if album_id is None or int(album_id) == -1:
de_album = album.default_album(user_id, conn=conn)
album_id = de_album.ID
sql = "INSERT INTO pic (user_id, album_id, file_id, title, description, level) VALUES \
(%(user_id)s, %(album_id)s, %(file_id)s, %(title)s, %(desc)s, %(level)s)"
args = {
'user_id': user_id,
'album_id': album_id,
'file_id': pic_file['id'],
'title': title,
'desc': desc,
'level': level
}
result, count, new_id, err = dao.do_execute_sql(sql=sql, conn=conn, new_id=True, args=args, commit=False)
if err is not None:
return None
url = url_with_name(pic_file['filename'], needhost=full_url, original=original)
return {
'id': new_id,
'album_id': album_id,
'url': url,
'exif_timestamp': pic_file['exif_timestamp'],
'timestamp': pic_file['timestamp']
}
def album_list(user_id, other_id):
relation = 0
lastPicUrl = 'lastPicUrl'
coverUrl = 'coverUrl'
sql = "SELECT ab.*, covers.filename as '{}', jp.filename as '{}' \
FROM \
album as ab \
left join (select file.filename, pic.album_id \
from pic, file \
where pic.file_id = file.id and pic.user_id = %(other_id)s \
order by file.timestamp desc limit 1) as jp \
on jp.album_id = ab.id \
left join (select file.filename, pic.id \
from pic, file \
where pic.file_id = file.id and pic.user_id = %(other_id)s) as covers \
on covers.id = ab.cover \
where ab.user_id=%(other_id)s {} order by ab.id desc".format(
coverUrl, lastPicUrl, '{}')
if user.isHome(user_id, other_id):
sql = sql.format('')
else:
relation = user.get_relation(other_id, user_id)
if relation == 0:
sql = sql.format('and level=0')
elif relation == 1:
sql = sql.format('and level<=1')
else:
return None, relation
result, count, new_id = dao.execute_sql(sql,
ret=True,
kv=True,
args={'other_id': other_id})
if count > 0:
for row in result:
if row[lastPicUrl] is not None:
row[lastPicUrl] = url_with_name(row[lastPicUrl], thumb=True)
if row[coverUrl] is not None:
row[coverUrl] = url_with_name(row[coverUrl], thumb=True)
return result, relation
else:
return None, relation
def user_with_db_result(result=None,
need_pwd=False,
need_username=False,
need_icon=False,
icon_name=None,
need_bg=False,
bg_name=None,
need_email=False):
if need_icon:
icon = files.filename(
result[6], needUrl=True) if icon_name is None else url_with_name(
icon_name, thumb=True)
else:
icon = None
if need_bg:
bg = files.filename(
result[7], needUrl=True) if bg_name is None else url_with_name(
bg_name, original=True)
else:
bg = None
username = result[1]
if need_email:
data = User.RGOpenIdController.user_data(username=username)
email = data['email'] if (data is not None
and 'email' in data) else None
else:
email = None
my_user = user(result[0], username if need_username else None,
result[2] if need_pwd else None, result[3], result[4],
result[5], icon, bg, result[8], result[9], result[10],
result[11], result[12], result[13], result[14], email)
my_user.iconId = result[6]
my_user.bgId = result[7]
return my_user
def filename(file_id, needUrl=False):
if file_id is None:
return None
sql = 'SELECT * FROM file where id=%(file_id)s'
result, count, new_id = dao.execute_sql(sql,
kv=True,
args={'file_id': file_id})
if count > 0:
name = result[0]['filename']
if needUrl:
return url_with_name(name)
else:
return name
else:
return None
def friend_page_list(user_id, page=1, size=10):
user_id = int(user_id)
size = int(size)
page = int(page)
if page < 1:
page = 1
sql_temp = "SELECT %s FROM user_relation as re, user as u %s \
where re.relation=1 and re.m_user_id=%s and re.o_user_id = u.id \
order by %s DESC" % ('%s', '%s', '%s', '%s')
sql = sql_temp % ('count(*)', '', '%(user_id)s', 're.addtime')
result, count, new_id = dao.execute_sql(sql,
ret=True,
args={
'user_id': user_id,
})
if count > 0:
count = result[0][0]
page_count = int(operator.truediv(count - 1, size)) + 1
page = min(page, page_count)
sql = sql_temp % ("re.addtime as 'follow_time', file.filename as 'icon', \
u.nickname, u.tag, u.id as 'ID', u.title as 'title', u.description as 'desc', u.addtime as 'addTime'",
' left join file on file.id = u.icon', '%(user_id)s',
'follow_time')
offset = (size, (page - 1) * size)
sql += (' limit %d offset %d' % offset)
result, this_page_count, new_id = dao.execute_sql(sql,
kv=True,
args={
'user_id': user_id,
})
page = page if this_page_count > 0 else page_count
for row in result:
icon = row['icon']
if icon is not None and len(icon):
row['icon'] = url_with_name(icon, thumb=True)
return result, page_count, page, size, count