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
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()
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)
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)
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)
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
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())
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())
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
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
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
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))
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))
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())
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)
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
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
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'))
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)