def get_shop_fans(shop_id, offset=0, limit=None):
"""
select ff.user_id, fu.`name`, fv.visit_count, count(fg.goods_id) as num
from fd_t_fans ff
left join fd_t_user fu on ff.user_id = fu.user_id
left join fd_t_visitedshop fv on ff.user_id = fv.user_id
left join fd_t_favorite ffa on ff.user_id = ffa.user_id
left join fd_t_goods fg on ffa.goods_id = fg.goods_id
where ff.shop_id = 10061
and (fv.shop_id = 10061 or isnull(fv.shop_id))
and (fg.shop_id = 10061 or isnull(fg.shop_id))
GROUP BY ff.user_id"""
dbsession = DBSession()
total_num = dbsession.query(FD_T_Fans).filter(FD_T_Fans.shop_id == shop_id).count()
fans = dbsession.query(FD_T_Fans.user_id, FD_T_User.name, FD_T_Visitedshop.visit_count,
FD_T_Visitedshop.last_visit_time,
FD_T_User.portrait_url) \
.outerjoin(FD_T_User, FD_T_User.user_id == FD_T_Fans.user_id) \
.outerjoin(FD_T_Visitedshop, FD_T_Visitedshop.user_id == FD_T_User.user_id) \
.filter(FD_T_Fans.shop_id == shop_id) \
.filter(or_(FD_T_Visitedshop.shop_id == shop_id, FD_T_Visitedshop.shop_id.is_(None))) \
.group_by(FD_T_Fans.user_id)
if limit is not None:
fans = fans[offset: offset + limit]
return dict(total_num=total_num,
is_success=True,
fans=[dict(user_id=i[0],
user_name=i[1],
user_visit_num=i[2],
user_favorite_num=get_favorite_goods_num(i[0], shop_id),
user_last_visitime=trans.datetime_to_string(i[3]),
portrait_url=ImageHost + i[4] if i[4] else None,
index=j + offset + 1) for j, i in enumerate(fans)])
def get_goods_fans2(goods_id, offset, limit):
"""
SELECT
fu.user_id,
fu.`name`,
fu.portrait_url,
fv.last_visit_time,
fv.visit_count
FROM
fd_t_goods fd
LEFT JOIN fd_t_favorite ff ON fd.goods_id = ff.goods_id
LEFT JOIN fd_t_fans ffans ON ff.user_id = ffans.user_id
LEFT JOIN fd_t_user fu ON ff.user_id = fu.user_id
LEFT JOIN fd_t_visitedshop fv ON fu.user_id = fv.user_id
WHERE
fd.goods_id = 75
AND ffans.shop_id = fd.shop_id
AND (
fv.shop_id = fd.shop_id
OR fv.shop_id IS NULL
)
"""
session = DBSession()
rs = session.query(FD_T_Goods.goods_id, FD_T_User.user_id, FD_T_User.name, FD_T_User.portrait_url,
FD_T_Visitedshop.last_visit_time, FD_T_Visitedshop.visit_count) \
.outerjoin(FD_T_Favorite, FD_T_Favorite.goods_id == FD_T_Goods.goods_id) \
.outerjoin(FD_T_Fans, FD_T_Fans.user_id == FD_T_Favorite.user_id) \
.outerjoin(FD_T_User, FD_T_Favorite.user_id == FD_T_User.user_id) \
.outerjoin(FD_T_Visitedshop, FD_T_User.user_id == FD_T_Visitedshop.user_id) \
.filter(FD_T_Goods.goods_id == goods_id) \
.filter(FD_T_Fans.shop_id == FD_T_Goods.shop_id) \
.filter(or_(FD_T_Visitedshop.shop_id == FD_T_Goods.shop_id, FD_T_Visitedshop.shop_id == None)).all()
return dict(
total_num=len(rs),
fans=[
dict(user_id=int(i[1]),
name=i[2],
portrait_url=ImageHost + i[3] if i[3] else None,
last_visit_time=trans.datetime_to_string(i[4])
if i[4] else None,
visit_count=int(i[5]) if i[5] else 0,
collects=0,
index=offset + index + 1)
for index, i in enumerate(rs[offset:offset + limit])
],
)
def get_goods_fans2(goods_id, offset, limit):
"""
SELECT
fu.user_id,
fu.`name`,
fu.portrait_url,
fv.last_visit_time,
fv.visit_count
FROM
fd_t_goods fd
LEFT JOIN fd_t_favorite ff ON fd.goods_id = ff.goods_id
LEFT JOIN fd_t_fans ffans ON ff.user_id = ffans.user_id
LEFT JOIN fd_t_user fu ON ff.user_id = fu.user_id
LEFT JOIN fd_t_visitedshop fv ON fu.user_id = fv.user_id
WHERE
fd.goods_id = 75
AND ffans.shop_id = fd.shop_id
AND (
fv.shop_id = fd.shop_id
OR fv.shop_id IS NULL
)
"""
session = DBSession()
rs = session.query(FD_T_Goods.goods_id, FD_T_User.user_id, FD_T_User.name, FD_T_User.portrait_url,
FD_T_Visitedshop.last_visit_time, FD_T_Visitedshop.visit_count) \
.outerjoin(FD_T_Favorite, FD_T_Favorite.goods_id == FD_T_Goods.goods_id) \
.outerjoin(FD_T_Fans, FD_T_Fans.user_id == FD_T_Favorite.user_id) \
.outerjoin(FD_T_User, FD_T_Favorite.user_id == FD_T_User.user_id) \
.outerjoin(FD_T_Visitedshop, FD_T_User.user_id == FD_T_Visitedshop.user_id) \
.filter(FD_T_Goods.goods_id == goods_id) \
.filter(FD_T_Fans.shop_id == FD_T_Goods.shop_id) \
.filter(or_(FD_T_Visitedshop.shop_id == FD_T_Goods.shop_id, FD_T_Visitedshop.shop_id == None)).all()
return dict(
total_num=len(rs),
fans=[dict(user_id=int(i[1]),
name=i[2],
portrait_url=ImageHost + i[3] if i[3] else None,
last_visit_time=trans.datetime_to_string(i[4]) if i[4] else None,
visit_count=int(i[5]) if i[5] else 0,
collects=0,
index=offset + index + 1) for index, i in enumerate(rs[offset: offset + limit])],
)
def get_shop_fans(shop_id, offset=0, limit=None):
"""
select ff.user_id, fu.`name`, fv.visit_count, count(fg.goods_id) as num
from fd_t_fans ff
left join fd_t_user fu on ff.user_id = fu.user_id
left join fd_t_visitedshop fv on ff.user_id = fv.user_id
left join fd_t_favorite ffa on ff.user_id = ffa.user_id
left join fd_t_goods fg on ffa.goods_id = fg.goods_id
where ff.shop_id = 10061
and (fv.shop_id = 10061 or isnull(fv.shop_id))
and (fg.shop_id = 10061 or isnull(fg.shop_id))
GROUP BY ff.user_id"""
dbsession = DBSession()
total_num = dbsession.query(FD_T_Fans).filter(
FD_T_Fans.shop_id == shop_id).count()
fans = dbsession.query(FD_T_Fans.user_id, FD_T_User.name, FD_T_Visitedshop.visit_count,
FD_T_Visitedshop.last_visit_time,
FD_T_User.portrait_url) \
.outerjoin(FD_T_User, FD_T_User.user_id == FD_T_Fans.user_id) \
.outerjoin(FD_T_Visitedshop, FD_T_Visitedshop.user_id == FD_T_User.user_id) \
.filter(FD_T_Fans.shop_id == shop_id) \
.filter(or_(FD_T_Visitedshop.shop_id == shop_id, FD_T_Visitedshop.shop_id.is_(None))) \
.group_by(FD_T_Fans.user_id)
if limit is not None:
fans = fans[offset:offset + limit]
return dict(total_num=total_num,
is_success=True,
fans=[
dict(user_id=i[0],
user_name=i[1],
user_visit_num=i[2],
user_favorite_num=get_favorite_goods_num(
i[0], shop_id),
user_last_visitime=trans.datetime_to_string(i[3]),
portrait_url=ImageHost + i[4] if i[4] else None,
index=j + offset + 1) for j, i in enumerate(fans)
])
def queryGoodsByPage(self, shop_id, offset, count):
conn = self._pool.connection()
cursor = conn.cursor()
try:
goods_list = []
sql = "select goods_id, description, price, promotion_price, pic_url_list, publish_time from fd_t_goods where shop_id = %s and status = 1 and publish_time <= \
(select publish_time from fd_t_goods where shop_id = %s and status = 1 order by publish_time desc limit %s, 1) \
order by publish_time desc limit %s"
paras = (shop_id, shop_id, offset, count)
row_count = cursor.execute(sql, paras)
if row_count <= 0:
return goods_list
rows = cursor.fetchall()
for row in rows:
goods = {}
goods["id"] = row["goods_id"]
goods["name"] = row["description"]
goods["price"] = float(row["price"]) if row["price"] else None
goods["promot"] = float(row["promotion_price"]) if row["promotion_price"] else None
goods["pic"] = ""
pic_url_list_str = row["pic_url_list"]
if pic_url_list_str is not None:
pic_url_list = pic_url_list_str.split(',')
if pic_url_list:
goods["pic"] = OSS_URL_PRIFIX + pic_url_list[0]
goods["time"] = trans.datetime_to_string(row["publish_time"])
goods_list.append(goods)
return goods_list
except MySQLdb.Error, e:
user_log.error("Query goods by page failed! sql: %s, paras: %s, exception: %s", sql, paras, e)
return None