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 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
def events():
'''
时间类型为时间戳格式
:return:
'''
try:
post_arr = request.form
current_app.logger.debug(str(post_arr))
if not post_arr:
raise Exception('not post params ')
activity = Activity(post_arr)
venue = Venue(post_arr)
venue_db = VenueDB.venue_by_coordinate(venue.coordinate_lng,
venue.coordinate_lat)
if venue_db:
activity.venue_id = venue_db.id
else:
venue = VenueDB.add_venue(venue)
activity.venue_id = venue.id
# 处理时间
start_time = activity.start_time
end_time = activity.end_time
if start_time > end_time:
return jsonify({'code': 0, 'error': 'start_time > end_time'})
week = int(time.strftime('%w', time.localtime(start_time)))
activity.week = week
activity.start_time = start_time
activity.end_time = end_time
is_cycle = post_arr.get('is_cycle', '')
cluster_ids = request.args.get('cluster_id', None)
# 判断是否需要报名标示
if cluster_ids:
# 获取报名标示
sign = ActivityDB.get_activity_sign(cluster_ids)
current_app.logger.debug(' sign ---------------------------------')
if sign:
activity.sign = sign
else:
raise Exception(' no sign get')
current_app.logger.debug(' no sign ---------------------------------')
ActivityDB.update_activity(activity)
if is_cycle == 1:
# 创建周期活动
cycleActivity = CycleActivity()
cycleActivity.category = activity.category
cycleActivity.name = activity.name
cycleActivity.start_time = activity.start_time
cycleActivity.end_time = activity.end_time
cycleActivity.venue_id = activity.venue_id
cycleActivity.fee = activity.fee
cycleActivity.is_aa = activity.is_aa
cycleActivity.member_count_list = activity.member_count_limit
cycleActivity.place_num = activity.place_num
cycleActivity.mobile = activity.mobile
cycleActivity.club_id = activity.club_id
# 获取周期活动时间
cycleActivity.week = activity.week
CycleActivity().add(cycleActivity)
return jsonify({'code': 1})
except Exception, eo:
current_app.logger.error(str(eo))
return jsonify({'code': 0, 'error': "update activity error"})
def importDataFromExcel(file, year=2019):
wb = openpyxl.load_workbook(file, data_only=True, read_only=True)
sheet = wb._sheets[0]
locale.setlocale(locale.LC_ALL, 'sv_SE')
events = dict()
eventTypes = dict()
activityTypes = dict()
n = 0
et_name = None
try:
for cols in sheet.rows:
n += 1
if n <= 9:
continue
if cols[0].value is None and et_name is None:
continue
et_name = cols[0].value or et_name
et = eventTypes.get(et_name)
if et is None:
#logger.info(f"Event type {et_name}")
try:
et = EventType.objects.get(name=et_name)
except EventType.DoesNotExist:
et = EventType(name=et_name)
et.save()
eventTypes[et_name] = et
if cols[2].value is None:
break
event_name = f"{et_name} vecka {cols[1].value}"
date = cols[2].value
if year is not None:
date = date.replace(year=year)
event = events.get((event_name, date))
if event is None:
#logger.info(f"Event {event_name} {date}")
try:
event = Event.objects.get(name=event_name,
start_date=date,
type=et)
except Event.DoesNotExist:
event = Event(name=event_name,
start_date=date,
end_date=date,
type=et)
event.save()
coord = cols[19].value
if coord is not None:
if ' ' in coord:
first, last = coord.split(' ', maxsplit=1)
else:
first = coord
try:
coordinator = Member.objects.get(
user__first_name=first, user__last_name=last)
event.coordinators.add(coordinator)
except Member.DoesNotExist:
print(
f"Failed to find member {coord} to use as coordinator for {event_name}"
)
events[event_name] = event
at_name = cols[5].value
at = activityTypes.get(at_name)
if at is None:
#logger.info(f"Activity Type: {at_name}")
try:
at = ActivityType.objects.get(name=at_name)
except ActivityType.DoesNotExist:
at = ActivityType(name=at_name)
at.save()
activityTypes[at_name] = at
ebd = cols[18].value
activity = Activity(
name=f"{at_name} {calendar.day_name[date.weekday()]}",
event=event,
type=at,
earliest_bookable_date=ebd)
interval = cols[4].value.replace('—', '-').replace('–', '-') \
.replace(' ', '').replace('.', ':')
try:
(start_time, end_time) = interval.split('-')
except ValueError as e:
logger.error(e)
logger.error(interval)
#logger.info(f'{activity.name} {interval}')
(sh, sm) = start_time.split(':')
(eh, em) = end_time.split(':')
activity.start_time = datetime.time(hour=int(sh), minute=int(sm))
activity.end_time = datetime.time(hour=int(eh), minute=int(em))
activity.full_clean()
activity.save()
print(f'''Database row count:
{EventType.objects.all().count()} event types
{ActivityType.objects.all().count()} activity types
{Event.objects.all().count()} events
{Activity.objects.all().count()} activities'''.replace(
' ', ' '))
except:
print(f"Error on row {n}")
raise
