def get_activity_list(act_filter, page_size, page_index):
try:
if "by_distance" in act_filter and act_filter["act_filter"]:
querysql = 'select a.id as id,a.start_time as start_time,a.end_time as end_time ,a.venue_id as venue_id,a.member_count_limit as member_count_limit,a.member_count as member_count,' \
'a.`status` as `status`,c.`name` as club_name,c.head_url as club_head_url ,SUM(POWER(ABS(v.coordinate_lng - w.coordinate_lng),2),POWER(ABS(v.coordinate_lat - w.coordinate_lat),2)) as distance ' \
'from activities a LEFT JOIN clubs c on a.club_id = c.id ' \
'left join venues v on a.venue_id = v.id left join weixin_user w on a.id = w.uid where a.`status` = 1 order by distance asc'
elif "by_default" in act_filter and act_filter["by_default"]:
return
else:
querysql = 'select a.id as id,a.start_time as start_time,a.end_time as end_time ,a.venue_id as venue_id,a.member_count_limit as member_count_limit,a.member_count as member_count,' \
'a.`status` as `status`,c.`name` as club_name,c.head_url as club_head_url,a.category as category ' \
'from activities a LEFT JOIN clubs c on a.club_id = c.id where a.`status` in(0,1) '
'''
if "category" in act_filter and act_filter["category"]:
querysql = querysql + "and a.category = '" + act_filter["category"] + "'"
if "week" in act_filter and act_filter["week"]:
querysql = querysql + "and a.week = '" + act_filter["week"] + "'"
'''
print "querysql:", querysql
act_arr_list = db.session.execute(querysql)
act_list = []
if act_arr_list:
for act_arr in act_arr_list:
act = Activity(None)
act.id = act_arr[0]
act.start_time = act_arr[1]
act.end_time = act_arr[2]
act.venue_id = act_arr[3]
if act.venue_id:
venue = db.session.query(Venue).filter(
Venue.id == act.venue_id).first()
act.member_count_limit = act_arr[4]
act.member_count = act_arr[5]
act.status = act_arr[6]
club = Club(None)
club.name = act_arr[7]
club.head_url = act_arr[8]
act.category = act_arr[9]
act.venue = venue
act.club = club
act_list.append(act)
return act_list
except Exception, e:
raise SQLException(e)
return
def init_activity(user_id):
'''
初始化活动
:param activity:
:return:
'''
try:
activity = Activity(None)
# 设定初始化参数 ------begin
activity.status = 0
activity.member_count = 0
activity.creator_id = user_id
db.session.add(activity)
db.session.commit()
except Exception, e:
db.session.rollback()
raise SQLException(e)
def get_club_activity_list(club_id, page_size, page_index):
try:
querysql = 'select a.id as id,a.start_time as start_time,a.end_time as end_time ,a.venue_id as venue_id,a.member_count_limit as member_count_limit,a.member_count as member_count,' \
'a.`status` as `status`,c.`name` as club_name,c.head_url as club_head_url,a.category as category ' \
'from activities a LEFT JOIN clubs c on a.club_id = c.id where a.`status` in(0,1) '
querysql = querysql + "and a.club_id = '" + str(club_id) + "'"
act_arr_list = db.session.execute(querysql)
act_list = []
if act_arr_list:
for act_arr in act_arr_list:
act = Activity(None)
act.id = act_arr[0]
act.start_time = act_arr[1]
act.end_time = act_arr[2]
act.venue_id = act_arr[3]
if act.venue_id:
venue = db.session.query(Venue).filter(
Venue.id == act.venue_id).first()
act.member_count_limit = act_arr[4]
act.member_count = act_arr[5]
act.status = act_arr[6]
club = Club(None)
club.name = act_arr[7]
club.head_url = act_arr[8]
act.category = act_arr[9]
act.venue = venue
act.club = club
act_list.append(act)
return act_list
except Exception, e:
raise SQLException(e)
return
