Beispiel #1
0
    def _filter_new_items(self, uid=None, option=None):
        """筛选出用户自选的圈子标签"""
        ucs = list()
        if uid:
            ucs = UserCollectionLog.query.filter_by_(UCLcollector=uid,
                                                     UCLcoType=CollectionType.news_tag.value).first()
        item_query = Items.query.filter(Items.isdelete == False,
                                        Items.ITtype == ItemType.news.value)
        if option and int(option) == NewsItemPostion.category.value:
            if ucs:
                itids = json.loads(ucs.UCLcollection)
                my_item = item_query.filter(Items.ITid.in_(itids)).order_by(func.field(Items.ITid, *itids)).all()
                candidate_item = item_query.filter(Items.ITid.notin_(itids)).all()

            else:
                my_item = item_query.all()
                candidate_item = []
            items = dict(my_item=my_item, candidate_item=candidate_item)
        elif option and (int(option) == NewsItemPostion.homepage.value or int(option) == NewsItemPostion.post.value):
            if ucs:
                itids = json.loads(ucs.UCLcollection)
                items = item_query.filter(Items.ITid.in_(itids), Items.ITid != 'mynews'
                                          ).order_by(func.field(Items.ITid, *itids)).all()

            else:
                items = item_query.filter(Items.ITid != 'mynews').all()
        else:
            if ucs:
                itids = json.loads(ucs.UCLcollection)
                current_app.logger.info("itids = {}".format(itids))
                items = item_query.filter(Items.ITid.in_(itids)).order_by(func.field(Items.ITid, *itids)).all()

            else:
                items = item_query.all()
        return items
Beispiel #2
0
def get_answers(question_id, **params):
    """获得答案列表"""
    conditions = list()
    sort_conditions = list()

    query = Answer.query

    if 'ids' in params:
        conditions.append(Answer.question_id.in_(params['ids']))
    if params.get('create_time_sort') is not None:
        sort_condition = (Answer.create_time.asc()
                          if params['create_time_sort'] else
                          Answer.create_time.desc())
        sort_conditions.append(sort_condition)
    if params.get('id_field_sort'):
        sort_condition = func.field(Answer.id, *params['ids'])
        sort_conditions.append(sort_condition)

    conditions.append(Answer.question_id == question_id)

    query = query.filter(*conditions).order_by(*sort_conditions)

    if params.get('need_paginate'):
        query = utils.and_pagination(query, params['page'], params['size'])

    return query.all()
Beispiel #3
0
    def _get_pagination_query(query, pagination, api_model, model):
        if not pagination.get('sort'):
            pagination['sort'] = api_model.DEFAULT_SORT
        marker = None
        if pagination.get('marker'):
            key_attr = getattr(model, api_model.PRIMARY_KEY)
            marker_query = copy.copy(query)
            marker_query = marker_query.filter(
                key_attr == pagination['marker'])
            try:
                marker = marker_query.limit(1).one()
            except exc.NoResultFound:
                raise storage.InvalidMarker(
                    'Marker %s not found.' % pagination['marker'])
        limit = pagination.get('limit')
        # we sort by "severity" by its semantic than its alphabetical
        # order when "severity" specified in sorts.
        for sort_key, sort_dir in pagination['sort'][::-1]:
            if sort_key == 'severity':
                sort_dir_func = {'asc': asc, 'desc': desc}[sort_dir]
                query = query.order_by(sort_dir_func(
                    func.field(getattr(model, sort_key), 'low',
                               'moderate', 'critical')))
                pagination['sort'].remove((sort_key, sort_dir))

        sort_keys = [s[0] for s in pagination['sort']]
        sort_dirs = [s[1] for s in pagination['sort']]
        return oslo_sql_utils.paginate_query(
            query, model, limit, sort_keys, sort_dirs=sort_dirs, marker=marker)
Beispiel #4
0
    def _get_pagination_query(session, query, pagination, api_model, model):
        if not pagination.get('sort'):
            pagination['sort'] = api_model.DEFAULT_SORT
        marker = None
        if pagination.get('marker'):
            key_attr = getattr(model, api_model.PRIMARY_KEY)
            marker_query = copy.copy(query)
            marker_query = marker_query.filter(
                key_attr == pagination['marker'])
            try:
                marker = marker_query.limit(1).one()
            except exc.NoResultFound:
                raise storage.InvalidMarker(
                    'Marker %s not found.' % pagination['marker'])
        limit = pagination.get('limit')
        # we sort by "severity" by its semantic than its alphabetical
        # order when "severity" specified in sorts.
        for sort_key, sort_dir in pagination['sort'][::-1]:
            if sort_key == 'severity':
                engine = session.connection()
                if engine.dialect.name != "mysql":
                    raise aodh.NotImplementedError
                sort_dir_func = {'asc': asc, 'desc': desc}[sort_dir]
                query = query.order_by(sort_dir_func(
                    func.field(getattr(model, sort_key), 'low',
                               'moderate', 'critical')))
                pagination['sort'].remove((sort_key, sort_dir))

        sort_keys = [s[0] for s in pagination['sort']]
        sort_dirs = [s[1] for s in pagination['sort']]
        return oslo_sql_utils.paginate_query(
            query, model, limit, sort_keys, sort_dirs=sort_dirs, marker=marker)
Beispiel #5
0
 def list_product(self):
     """商品列表"""
     args = request.args.to_dict()
     filter_args = []
     if not (is_admin() or is_supplizer()):
         filter_args.append(Product.PRstatus.notin_((ProductStatus.interrupt.value, ProductStatus.reject.value,
                                                     ProductStatus.pending.value)))
     if is_supplizer():
         filter_args.append(Product.SUid == getattr(request, 'user').id)
     prlimited = args.get('prtimelimeted')
     if str(prlimited) in '01':
         filter_args.append(Product.PRtimeLimeted == prlimited)
     products = Product.query.filter(Product.isdelete == false(), *filter_args
                                     ).order_by(func.field(Product.PRstatus, ProductStatus.active.value,
                                                           ProductStatus.ready.value, ProductStatus.over.value),
                                                Product.PRissueStartTime.asc(),
                                                Product.createtime.desc()).all_with_page()
     products_fields = ['PRid', 'PRname', 'PRimg', 'PRlinePrice', 'PRtruePrice', 'PRnum', 'PRtimeLimeted',
                        'PRstatus', 'prstatus_zh', 'apply_num', 'PRissueStartTime', 'PRissueEndTime',
                        'PRuseStartTime', 'PRuseEndTime', 'interrupt', 'apply_num_str', 'buyer_avatar',
                        'start_time_str']
     for product in products:
         self._fill_product(product)
         product.fields = products_fields
         if not product.PRtimeLimeted:
             product.PRnum = '无限量'
     return Success(data=products)
Beispiel #6
0
 def _filter_my_box():
     usid = request.user.id
     res = MagicBoxJoin.query.filter(MagicBoxJoin.isdelete == False,
                                     MagicBoxJoin.USid == usid).order_by(
                                         func.field(MagicBoxJoin.MBJstatus,
                                                    0, 10, -10),
                                         MagicBoxJoin.createtime.desc())
     return res
Beispiel #7
0
 def get_all(cls, keyvals, key='id', user_id=None):
     if len(keyvals) == 0:
         return []
     resultset = cls.query.filter(getattr(cls, key).in_(keyvals))
     if user_id and hasattr(cls, 'user_id'):
         resultset = resultset.filter(cls.user_id == user_id)
     # We need the results in the same order as the input keyvals
     # So order by field in SQL
     resultset = resultset.order_by(func.field(getattr(cls, key), *keyvals))
     return place_nulls(key, keyvals, resultset.all())
Beispiel #8
0
 def get_all(cls, keyvals, key='id', user_id=None):
     if len(keyvals) == 0:
         return []
     resultset = cls.query.filter(getattr(cls, key).in_(keyvals))
     if user_id and hasattr(cls, 'user_id'):
         resultset = resultset.filter(cls.user_id == user_id)
     # We need the results in the same order as the input keyvals
     # So order by field in SQL
     resultset = resultset.order_by(
         func.field(getattr(cls, key), *keyvals))
     return place_nulls(key, keyvals, resultset.all())
Beispiel #9
0
 def by_ids(cls, ids):
     """Returns a list of Bookmarks matching the IDs in `ids`;
     in MySQL the returned list is ordered the same as `ids`.
     """
     # Keep the order from Whoosh with a MySQL specific hack:
     # order_by FIELD() function.
     # http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
     engine = db.Session.get_bind()
     if engine.driver.startswith("mysql"):
         query = cls.get_public().filter(cls.id.in_(ids)).order_by(func.field(cls.id, *ids))
     else:
         query = cls.get_public().filter(cls.id.in_(ids)).order_by(cls.created_on.desc())
     return query
Beispiel #10
0
 def __getitem__(self, item):
     keys = self._identities[item]
     if not keys:
         return []
     query = self._session.query(self._cls)\
         .filter(self._key.in_(keys)) \
         .order_by(func.field(self._key, *keys))
     if self._options:
         query = query.options(*self._options)
     items = list(query)
     for item in items:
         item.__weight__ = self._weights.get(item.id, None)
     return items
Beispiel #11
0
 def _filter_joined_group():
     usid = request.user.id
     my_joined = GuessGroup.query.outerjoin(GuessRecord, GuessRecord.GGid == GuessGroup.GGid
                                            ).filter(GuessGroup.isdelete == False,
                                                     GuessRecord.isdelete == False,
                                                     GuessRecord.USid == usid,
                                                     # GuessRecord.GRstatus == GuessRecordStatus.valid.value,
                                                     # GuessGroup.GGendtime >= now,
                                                     # GuessGroup.GGstatus.in_((GuessGroupStatus.pending.value,
                                                     #                          GuessGroupStatus.waiting.value)),
                                                     ).order_by(func.field(GuessGroup.GGstatus, 0, 10, 20, -10),
                                                                GuessGroup.createtime.desc())
     return my_joined
Beispiel #12
0
 def _apply_order_by(self, orderby):
     if orderby is not None:
         for field in orderby:
             attr, order = list(field.items())[0]
             ordering_function = self.ordering_functions[order]
             if attr == 'severity':
                 self.query = self.query.order_by(ordering_function(
                     func.field(getattr(self.table, attr), 'low',
                                'moderate', 'critical')))
             else:
                 self.query = self.query.order_by(ordering_function(
                     getattr(self.table, attr)))
     else:
         self.query = self.query.order_by(desc(self.table.timestamp))
Beispiel #13
0
 def _apply_order_by(self, orderby):
     if orderby is not None:
         for field in orderby:
             attr, order = list(field.items())[0]
             ordering_function = self.ordering_functions[order]
             if attr == 'severity':
                 self.query = self.query.order_by(
                     ordering_function(
                         func.field(getattr(self.table, attr), 'low',
                                    'moderate', 'critical')))
             else:
                 self.query = self.query.order_by(
                     ordering_function(getattr(self.table, attr)))
     else:
         self.query = self.query.order_by(desc(self.table.timestamp))
Beispiel #14
0
    def _filter_not_joined_group():
        filter_args = []
        if common_user():  # 只筛选自己没参加过的团
            usid = request.user.id
            all_gr = db.session.query(GuessRecord.GGid, func.group_concat(GuessRecord.USid)
                                      ).filter(GuessRecord.isdelete == False,
                                               GuessRecord.GRstatus == GuessRecordStatus.valid.value
                                               ).group_by(GuessRecord.GGid).all()
            ggid_list = [gr[0] for gr in all_gr if usid not in gr[1]]
            filter_args.append(GuessGroup.GGid.in_(ggid_list))

        all_group = GuessGroup.query.filter(GuessGroup.isdelete == False,
                                            GuessGroup.GGendtime >= datetime.datetime.now(),
                                            GuessGroup.GGstatus.in_((GuessGroupStatus.pending.value,
                                                                     GuessGroupStatus.waiting.value)),
                                            * filter_args
                                            ).order_by(func.field(GuessGroup.GGstatus, 0, 10, 20, -10),
                                                       GuessGroup.createtime.desc())
        return all_group
    def get_all(cls, keyvals, key='id', user_id=None):
        """Works like a map function from keyvals to instances.


        Args:

            keyvals(list):  The list of values of the attribute.

            key (str, optional): The attribute to search by. By default, it is
                'id'.


        Returns:

            list: A list of model instances, in the same order as the list of
                keyvals.


        Examples:


            >>> User.get_all([2,5,7, 8000, 11])
            [email protected], [email protected], [email protected], None, [email protected]

            >>> User.get_all(['*****@*****.**', '*****@*****.**'], key='email')
            [email protected], [email protected]

        """
        if len(keyvals) == 0:
            return []
        resultset = cls.query.filter(getattr(cls, key).in_(keyvals))
        if user_id and hasattr(cls, 'user_id'):
            resultset = resultset.filter(cls.user_id == user_id)
        # We need the results in the same order as the input keyvals
        # So order by field in SQL
        resultset = resultset.order_by(
            func.field(getattr(cls, key), *keyvals))
        return place_nulls(key, keyvals, resultset.all())
Beispiel #16
0
 def get_team(self):
     """团队广场下内容"""
     data = parameter_required(('plid', ))
     secret_usid = data.get('secret_usid')
     csc = None
     if secret_usid:
         csc = self.get_customize_share_content(secret_usid,
                                                data.get('plid'))
     if csc:
         current_app.logger.info('get cscid: {}'.format(csc.CSCid))
         trids = json.loads(csc.TRids)
         tr_list = TravelRecord.query.filter(
             TravelRecord.isdelete == false(), TravelRecord.TRid.in_(trids),
             TravelRecord.TRstatus == TravelRecordStatus.published.value)
         if trids:
             tr_list = tr_list.order_by(
                 func.field(TravelRecord.TRid, *trids))
         tr_list = tr_list.all()
     else:
         tr_list = self._filter_team_travelrecord(
             data.get('plid')).all_with_page()
     [self._fill_travelrecord(x) for x in tr_list]
     return Success(data=tr_list)
Beispiel #17
0
 def getProduct(self, ids):
     q = self.session.query(Product)
     products = q.filter(Product.id.in_(ids)).order_by(
         func.field(Product.id, *ids))
     return products
Beispiel #18
0
    def get_exam_list(self, name_kw=None, page=1, per_page=15):
        self._query = self._model.query
        if name_kw:
            self._query = self._query.filter(self._model.name.like('%' + name_kw + '%'))

        if self.user_id:
            during_exam = self.get_during_exam(self.user_id)
            during_exam = 7  # TODO Just for test.
            exam_paper_msg = {exam_paper.exam_id: exam_paper.finished_time for exam_paper in ExaminationPaper.query.filter_by(user_id=self.user_id).all()}
            if during_exam:
                exam_pagination = self._query.filter(self._model.id.in_(exam_paper_msg.keys())).order_by(
                    case((
                        (self._model.id == during_exam, 1),
                        (self._model.id != during_exam, 2)
                    )),
                    self._model.create_time.desc()).paginate(page, per_page)
            else:
                exam_pagination = self._query.filter(self._model.id.in_(exam_paper_msg.keys())).order_by('-create_time').paginate(page, per_page)
            finished_exams = [ep.exam_id for ep in ExaminationPaper.query.filter_by(user_id=self.user_id).filter(ExaminationPaper.performance!='').all()]
            total, exam_list = exam_pagination.total, [
                {
                    'id': exam.id,
                    'name': exam.name,
                    'create_time': exam.create_time,
                    'question_num': exam.question_num,
                    'finish_time': exam_paper_msg.get(exam.id),
                    'option': self.map_exam_status(exam.id, during_exam, finished_exams, exam.begin_time, exam.end_time)
                } for exam in exam_pagination.items
            ]

        else:
            # Self.user_id is None means the current_user is superadmin.
            exam_pagination = self._query.order_by('-create_time').paginate(page, per_page)
            current_page_exam_ids = [exam.id for exam in exam_pagination.items]

            finished_examinees_nums = dict(db.session.query(ExaminationPaper.exam_id, func.count(ExaminationPaper.user_id)).filter(ExaminationPaper.exam_id.in_(current_page_exam_ids)).filter(ExaminationPaper.performance!='').group_by(ExaminationPaper.exam_id).order_by(func.field(ExaminationPaper.exam_id, *current_page_exam_ids)))
            total, exam_list = exam_pagination.total, [
                {
                    'id': exam.id,
                    'name': exam.name,
                    'question_num': exam.question_num,
                    'create_time': exam.create_time,
                    'finished_examinees_num': finished_examinees_nums.get(exam.id, 0),
                } for _index, exam in enumerate(exam_pagination.items)
            ]
        return total, exam_list
Beispiel #19
0
def view(page_nr=1):
    if not ModuleAPI.can_read('summary', True):
        flash(_('Valid membership is required for the summary module'),
              'warning')
        session['prev'] = 'summary.view'
        return abort(403)

    # First check if the delete argument is set before loading
    # the search results
    if request.form.get('delete') and request.method == 'POST':
        summary_id = request.form.get('delete')
        summary = Summary.query.filter(Summary.id == summary_id)\
            .first()

        if not summary:
            flash(_('Specified summary to delete does not exist'), 'danger')
        else:
            try:
                os.remove(os.path.join(UPLOAD_FOLDER, summary.path))
            except:
                flash(_('File does not exist, summary deleted.'), 'info')

            db.session.delete(summary)
            db.session.commit()
            flash(_('Summary successfully deleted.'))
            redir_url = url_for('summary.view', page_nr=page_nr)
            if request.args.get('search'):
                redir_url += ("?search=" + request.args.get('search'))
            return redirect(redir_url)

    if request.args.get('search'):
        search = request.args.get('search')

        summaries = Summary.query.all()
        summary_matches_per_course = {}
        course_max_scores = {}

        search_lower = search.lower().strip()

        for summary in summaries:
            course = summary.course.name.lower()
            title_ratio = fuzz.partial_ratio(
                search_lower, summary.title.lower())
            course_ratio = fuzz.partial_ratio(search_lower, course)
            education_ratio = fuzz.partial_ratio(
                search_lower, summary.education.name.lower())
            if title_ratio > 75 or course_ratio > 75 or education_ratio > 75:
                # Calculate the score for the summary
                # TODO: maybe use a weighted mean instead of max
                score = max(title_ratio, course_ratio, education_ratio)

                summary_tuple = (score, summary.id)

                # If the course did not occur before, add it
                # to the dictionaries and set the max score
                # to the score of this summary
                if course not in summary_matches_per_course:
                    summary_matches_per_course[course] = [summary_tuple]
                    course_max_scores[course] = score

                # Otherwise, add the summary to the list of the course
                # and update the maximum course score
                else:
                    summary_matches_per_course[course].append(summary_tuple)
                    course_max_scores[course] = max(score,
                                                    course_max_scores[course])
        if len(course_max_scores) == 0:
            summaries = None
        else:
            # Sort the courses by their max score
            courses_sorted = sorted(course_max_scores,
                                    key=course_max_scores.get, reverse=True)

            # Create the list of summary ids. These are ordered by course with
            # their maximum score, and for each course ordered by summary score
            summary_matches = []
            for course in courses_sorted:
                summary_matches.extend(list(zip(*sorted(
                    summary_matches_per_course[course], reverse=True)))[1])

            # Query the summaries. The order_by clause keeps them in the same
            # order as the summary_matches list
            summaries = Summary.query \
                .filter(Summary.id.in_(summary_matches)) \
                .order_by(func.field(Summary.id, *summary_matches)) \
                .paginate(page_nr, 15, True)
    else:
        search = ""
        summaries = Summary.query.join(Course)\
            .order_by(Course.name).paginate(page_nr, 15, True)

    path = '/static/uploads/summaries/'

    return render_template('summary/view.htm', path=path,
                           summaries=summaries, search=search,
                           title=_('Summaries'))
Beispiel #20
0
def view_examination(page_nr=1):
    if not ModuleAPI.can_read('examination', True):
        flash(_('Valid membership is required for the examination module'),
              'warning')
        session['prev'] = 'examination.view_examination'
        return abort(403)

    # First check if the delete argument is set before loading
    # the search results
    if request.args.get('delete'):
        exam_id = request.args.get('delete')
        examination = Examination.query.filter(Examination.id == exam_id)\
            .first()

        if not examination:
            flash(_('Specified examination does not exist'), 'danger')
        else:
            try:
                os.remove(os.path.join(UPLOAD_FOLDER, examination.path))
            except:
                flash(_('File does not exist, examination deleted.'), 'info')

            db.session.delete(examination)
            db.session.commit()
            flash(_('Examination successfully deleted.'))

    # After deletion, do the search.
    if request.args.get('search'):
        search = request.args.get('search')

        exams = Examination.query.all()
        exam_matches_per_course = {}
        course_max_scores = {}

        search_lower = search.lower().strip()

        for exam in exams:
            course = exam.course.name.lower()
            comment_ratio = 0
            if exam.comment:
                comment_ratio = fuzz.partial_ratio(search_lower,
                                                   exam.comment.lower())
            course_ratio = fuzz.partial_ratio(search_lower, course)
            education_ratio = fuzz.partial_ratio(search_lower,
                                                 exam.education.name.lower())
            date_ratio = 0
            if exam.date:
                date_ratio = fuzz.partial_ratio(
                    search_lower, exam.date.strftime(DATE_FORMAT))

            if comment_ratio > 75 or course_ratio > 75 \
                    or education_ratio > 75 or date_ratio > 75:
                # Calculate the score for the exam
                # TODO: maybe use a weighted mean instead of max
                score = max(comment_ratio, course_ratio,
                            education_ratio, date_ratio)

                exam_tuple = (score, exam.id)

                # If the course did not occur before, add it
                # to the dictionaries and set the max score
                # to the score of this exam
                if course not in exam_matches_per_course:
                    exam_matches_per_course[course] = [exam_tuple]
                    course_max_scores[course] = score

                # Otherwise, add the exam to the list of the course
                # and update the maximum course score
                else:
                    exam_matches_per_course[course].append(exam_tuple)
                    course_max_scores[course] = max(score,
                                                    course_max_scores[course])
        if len(course_max_scores) == 0:
            examinations = None
        else:
            # Sort the courses by their max score
            courses_sorted = sorted(course_max_scores,
                                    key=course_max_scores.get, reverse=True)

            # Create the list of exam ids. These are ordered by course with
            # their maximum score, and for each course ordered by exam score
            exam_matches = []
            for course in courses_sorted:
                exam_matches.extend(list(zip(*sorted(
                    exam_matches_per_course[course], reverse=True)))[1])

            # Query the exams. The order_by clause keeps them in the same
            # order as the exam_matches list
            examinations = Examination.query \
                .filter(Examination.id.in_(exam_matches)) \
                .order_by(func.field(Examination.id, *exam_matches)) \
                .paginate(page_nr, 15, True)
    else:
        search = ""
        examinations = Examination.query.join(Course)\
            .order_by(Course.name).paginate(page_nr, 15, True)

    path = '/static/uploads/examinations/'

    return render_template('examination/view.htm', path=path,
                           examinations=examinations, search=search,
                           title=_('Examinations'), test_types=test_types)