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