def _insert_web_service(name, login, acl, description):
op.execute(webservice.insert().values(name=name,
login=login,
passwd=func.substring(
func.gen_salt('bf', 4), 8),
acl=acl,
description=description))
def get(self):
args = self.parse_arg()
releases = db.session.query(
func.substring(cast(Release.date, db.String), 0,8).label('month'),
func.count(Release.value).label('count'),
func.sum(Release.value).label('total_value'))
releases = self.filter_request(releases, args)
releases = releases.group_by('1')
releases = self.sort_request(releases, args)
release_count = releases.count()
(releases, offset, limit) = self.offset_limit(releases, args)
#Generate output structure
output = dict()
output["meta"] = {
"count": release_count,
"pagination" : {"offset" : offset, "limit": limit}
}
output["releases"] = [r._asdict() for r in releases]
return output
def stat_playlist():
data = {}
data["gdType"] = settings.Session.query(
func.substring(Playlist163.dsc, 4, 2).label('type'),
func.count('*').label('count')).group_by("type").all()
data["gdOver"] = settings.Session.query(
Playlist163.over.label('over'),
func.count('*').label('count')).group_by("over").all()
return data
class Article(BaseModel):
__tablename__ = 'articles'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(200), unique=False, nullable=False)
content = db.Column(db.Text, unique=False, nullable=False)
preview = column_property(func.substring(content, 0, 89) + '...')
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
category_id = db.Column(db.Integer,
db.ForeignKey('categories.id'),
nullable=False)
# One article has many images
images = db.relationship('Image',
lazy='subquery',
backref=db.backref('article', lazy='joined'))
def __repr__(self):
return '<Article %r>' % self.title
@classmethod
def find_by_uuid(cls, uuid):
return cls.query.filter_by(uuid=uuid).first()
@classmethod
def find_all_articles(cls, filter_params):
query = cls.query
query = query.filter(cls.title.like(f'%{filter_params["query"]}%'))
if filter_params['from']:
query = query.filter(cls.created_date >= filter_params['from'])
if filter_params['to']:
query = query.filter(cls.created_date <= filter_params['to'])
if filter_params['sort'] == OrderType.ASCENDING:
query = query.order_by(cls.created_date.asc())
elif filter_params['sort'] == OrderType.DESCENDING:
query = query.order_by(cls.created_date.desc())
return query.paginate(filter_params['page'],
filter_params['per_page'],
error_out=False)
def commit(self):
db.session.add(self)
db.session.commit()
def remove(self):
db.session.delete(self)
db.session.commit()
def ham_country_grid_coords(call):
if "sqlite" in db.engine.driver:
q = (
DxccPrefixes.query.filter(DxccPrefixes.call == func.substr(call, 1, func.LENGTH(DxccPrefixes.call)))
.order_by(func.length(DxccPrefixes.call).asc())
.limit(1)
)
else:
q = (
DxccPrefixes.query.filter(DxccPrefixes.call == func.substring(call, 1, func.LENGTH(DxccPrefixes.call)))
.order_by(func.length(DxccPrefixes.call).asc())
.limit(1)
)
if q.count() <= 0:
return None
else:
qth = coords_to_qth(q[0].lat, q[0].long, 6)
return {"qth": qth["qth"], "latitude": q[0].lat, "longitude": q[0].long}
def get(self):
args = self.parse_arg()
releases = db.session.query(
func.substring(cast(Release.date, db.String), 0,8).label('month'),
func.unnest(Release.activities).label('activity'),
func.count(Release.value).label('count'),
func.sum(Release.value).label('total_value'))
releases = self.filter_request(releases, args)
releases = releases.group_by('month', 'activity')
releases = self.sort_request(releases, args)
release_count = releases.count()
(releases, offset, limit) = self.offset_limit(releases, args)
#Generate output structure
output = dict()
output["meta"] = {
"count": release_count,
"pagination" : {"offset" : offset, "limit": limit}
}
months_dict = {}
for release in releases:
r = release._asdict()
current_month = r["month"]
del r["month"]
if current_month not in months_dict:
months_dict[current_month] = []
months_dict[current_month].append(r)
output["releases"] = [{"month" : month, "activities": activities} for month,activities in months_dict.iteritems()]
#Ugly hack to remove activities that were not requested by provided because
#some services are in 2 different activities
if 'activity' in args and args['activity'] != None:
activity_list = args['activity'].split(';')
for j, m in reversed(list(enumerate(output["releases"]))):
for i, a in reversed(list(enumerate(m["activities"]))):
if a["activity"] not in activity_list:
del m["activities"][i]
if len(output["releases"][j]["activities"]) == 0:
del output["releases"][j]
#Another hack to aggregate activities not in the top N
if 'aggregate' in args and (args['aggregate'] == "value" or args['aggregate'] == "count"):
top = []
activities = db.session.query(
Release.activities[1].label('activity'),
func.sum(Release.value).label('total_value'),
func.count(Release.value).label('count'))
activities = self.filter_request(activities, args)
activities = activities.filter(Release.activities[1] != "Autres")
activities = activities.group_by('activity')
if args['aggregate'] == "value":
activities = activities.order_by("total_value desc")
else :
activities = activities.order_by("count desc")
activities = activities[0:app.config["AGG_ACTIVITIES"]]
top = [a._asdict()['activity'] for a in activities]
for m in output["releases"]:
count = 0
total_value = 0
for i, a in reversed(list(enumerate(m["activities"]))):
if a["activity"] not in top:
count += a["count"]
total_value += a["total_value"]
del m["activities"][i]
if count > 0 and total_value > 0:
autres = {"activity": "Autres", "count": count,"total_value": total_value}
m["activities"].append(autres)
return output
def get_courses(page, per_page):
# get search parameters from get request
programs = request.args.get('programs').split(" ")
keyword = request.args.get('keyword')
sort_type = int(request.args.get('sortType'))
is_ascending = request.args.get('isAscending') == "true"
# create query
# find courses according to search parameter
if programs == ['']: # no programs selected
query = db.session.query(Course).filter(
Course.courseID.like('%' + keyword + '%')
| Course.courseDesc.like('%' + keyword + '%')
| Course.courseName.like('%' + keyword + '%')
| Course.prereqs.like('%' + keyword + '%')
)
else: # programs selected
query = db.session.query(Course).filter(
(Course.courseIDType.in_(programs)
& (
Course.courseID.like('%' + keyword + '%')
| Course.courseDesc.like('%' + keyword + '%')
| Course.courseName.like('%' + keyword + '%')
| Course.prereqs.like('%' + keyword + '%')
))
)
# modify query
# sort courses
if sort_type == 1: # by courseId
# gets length, location, courseID
componentQuery = db.session.query(
func.length(Course.courseID).label('length'),
func.locate(" ", Course.courseID).label('loc'),
Course.courseID
).subquery()
# gets courseNo from courseID
sortAttrQuery = db.session.query(
func.substring(
componentQuery.c.courseID,
componentQuery.c.loc,
componentQuery.c.length
).label('courseNo'),
componentQuery.c.courseID.label('courseID')
).subquery()
# join original query with courseNo attribute
query = query.outerjoin(
sortAttrQuery,
Course.courseID == sortAttrQuery.c.courseID
)
# sort order
if is_ascending:
query = query.order_by(sortAttrQuery.c.courseNo.asc())
else:
query = query.order_by(sortAttrQuery.c.courseNo.desc())
elif sort_type == 2: # by credits
# sort order
if is_ascending:
query = query.order_by(Course.creditHoursMin.asc())
else:
query = query.order_by(Course.creditHoursMin.desc())
elif sort_type == 3: # by program
# sort order
if is_ascending:
query = query.order_by(Course.courseIDType.asc())
else:
query = query.order_by(Course.courseIDType.desc())
else: # invalid choice
return jsonify(msg = "invalid sorting option"), HTTPStatus.BAD_REQUEST
# execute query
record_query = query.paginate(page, per_page, True)
# get results and pagination attributes
numResults = record_query.total
numPages = record_query.pages
results = record_query.items
# # add results to course array
courses = []
for result in results:
courses.append(result.as_dict())
return jsonify(
coursesForPage = courses,
numPages = numPages,
numResults = numResults,
)
def do_search(search_query, offset, result_count, new_domains, framework_slug):
try:
sort_dir = list(search_query['sort'][0].values())[0]['order']
except (KeyError, IndexError):
sort_dir = 'asc'
try:
sort_by = list(search_query['sort'][0].values())[0]['sort_by']
except (KeyError, IndexError):
sort_by = None
try:
terms = search_query['query']['filtered']['filter']['terms']
except (KeyError, IndexError):
terms = {}
roles_list = None
seller_types_list = None
if terms:
new_domains = 'prices.serviceRole.role' not in terms
try:
if new_domains:
roles_list = terms['domains.assessed']
else:
roles = terms['prices.serviceRole.role']
roles_list = set(_['role'][7:] for _ in roles)
except KeyError:
pass
try:
seller_types_list = terms['seller_types']
except: # noqa
pass
try:
search_term = search_query['query']['match_phrase_prefix']['name']
except KeyError:
search_term = ''
EXCLUDE_LEGACY_ROLES = not current_app.config['LEGACY_ROLE_MAPPING']
if new_domains:
q = db.session.query(Supplier).outerjoin(SupplierDomain).outerjoin(Domain) \
.outerjoin(SupplierFramework).outerjoin(Framework)
else:
q = db.session.query(Supplier).outerjoin(PriceSchedule).outerjoin(ServiceRole) \
.outerjoin(SupplierFramework).outerjoin(Framework)
q = q.filter(
Supplier.status != 'deleted', Supplier.abn != Supplier.DUMMY_ABN,
or_(Framework.slug == framework_slug, ~Supplier.frameworks.any()))
tsquery = None
if search_term:
if any(c in search_term for c in ['#', '-', '_', '/', '\\']):
tsquery = func.phraseto_tsquery(search_term)
elif ' ' in search_term:
tsquery = func.plainto_tsquery(search_term)
else:
tsquery = func.to_tsquery(search_term + ":*")
q = q.add_column(
func.ts_headline(
'english',
func.concat(Supplier.summary, ' ',
Supplier.data['tools'].astext, ' ',
Supplier.data['methodologies'].astext, ' ',
Supplier.data['technologies'].astext, ''), tsquery,
'MaxWords=25, MinWords=20, ShortWord=3, HighlightAll=FALSE, MaxFragments=1'
))
q = q.group_by(Supplier.id)
try:
code = search_query['query']['term']['code']
q = q.filter(Supplier.code == code)
except KeyError:
pass
if roles_list is not None:
if new_domains:
if EXCLUDE_LEGACY_ROLES:
d_agg = postgres.array_agg(cast(Domain.name, TEXT))
q = q.filter(SupplierDomain.status == 'assessed')
q = q.having(d_agg.contains(array(roles_list)))
else:
sr_agg = postgres.array_agg(
cast(func.substring(ServiceRole.name, 8), TEXT))
q = q.having(sr_agg.contains(array(roles_list)))
if seller_types_list is not None and 'recruiter' in seller_types_list:
q = q.filter(Supplier.is_recruiter == 'true')
seller_types_list.remove('recruiter')
if len(seller_types_list) == 0:
seller_types_list = None
if seller_types_list is not None:
selected_seller_types = select(
[postgres.array_agg(column('key'))],
from_obj=func.json_each_text(Supplier.data[('seller_type', )]),
whereclause=cast(column('value'), Boolean)).as_scalar()
q = q.filter(selected_seller_types.contains(array(seller_types_list)))
if sort_by:
if sort_by == 'latest':
ob = [desc(Supplier.last_update_time)]
else:
ob = [asc(Supplier.name)]
else:
if sort_dir == 'desc':
ob = [desc(Supplier.name)]
else:
ob = [asc(Supplier.name)]
if search_term:
ob = [desc(func.ts_rank_cd(Supplier.text_vector, tsquery))] + ob
q = q.filter(Supplier.text_vector.op('@@')(tsquery))
q = q.order_by(*ob)
raw_results = list(q)
results = []
for x in range(len(raw_results)):
if type(raw_results[x]) is Supplier:
result = raw_results[x]
else:
result = raw_results[x][0]
if raw_results[x][1] is not None and raw_results[x][1] != '':
result.summary = raw_results[x][1]
results.append(result)
sliced_results = results[offset:(offset + result_count)]
q = db.session.query(Supplier.code, Supplier.name, Supplier.summary, Supplier.is_recruiter,
Supplier.data, Domain.name.label('domain_name'),
SupplierDomain.status.label('domain_status'))\
.outerjoin(SupplierDomain, Domain)\
.filter(Supplier.id.in_([sr.id for sr in sliced_results]))\
.order_by(Supplier.name)
suppliers = [r._asdict() for r in q]
sliced_results = []
for key, group in groupby(suppliers, key=itemgetter('code')):
supplier = group.next()
supplier['seller_type'] = supplier.get(
'data') and supplier['data'].get('seller_type')
supplier['domains'] = {'assessed': [], 'unassessed': []}
for s in chain([supplier], group):
domain, status = s['domain_name'], s['domain_status']
if domain:
if status == 'assessed':
supplier['domains']['assessed'].append(domain)
else:
supplier['domains']['unassessed'].append(domain)
for e in ['domain_name', 'domain_status', 'data']:
supplier.pop(e, None)
sliced_results.append(supplier)
return sliced_results, len(results)
def do_search(search_query, offset, result_count, new_domains, framework_slug):
try:
sort_dir = list(search_query['sort'][0].values())[0]['order']
except (KeyError, IndexError):
sort_dir = 'asc'
try:
sort_by = list(search_query['sort'][0].values())[0]['sort_by']
except (KeyError, IndexError):
sort_by = None
try:
terms = search_query['query']['filtered']['filter']['terms']
except (KeyError, IndexError):
terms = {}
roles_list = None
seller_types_list = None
if terms:
new_domains = 'prices.serviceRole.role' not in terms
try:
if new_domains:
roles_list = terms['domains.assessed']
else:
roles = terms['prices.serviceRole.role']
roles_list = set(_['role'][7:] for _ in roles)
except KeyError:
pass
try:
seller_types_list = terms['seller_types']
except: # noqa
pass
try:
search_term = search_query['query']['match_phrase_prefix']['name']
except KeyError:
search_term = ''
EXCLUDE_LEGACY_ROLES = not current_app.config['LEGACY_ROLE_MAPPING']
if new_domains:
q = db.session.query(Supplier).outerjoin(SupplierDomain).outerjoin(Domain) \
.outerjoin(SupplierFramework).outerjoin(Framework)
else:
q = db.session.query(Supplier).outerjoin(PriceSchedule).outerjoin(ServiceRole) \
.outerjoin(SupplierFramework).outerjoin(Framework)
q = q.filter(Supplier.status != 'deleted', Supplier.abn != Supplier.DUMMY_ABN,
or_(Framework.slug == framework_slug, ~Supplier.frameworks.any()))
tsquery = None
if search_term:
if any(c in search_term for c in ['#', '-', '_', '/', '\\']):
tsquery = func.phraseto_tsquery(search_term)
elif ' ' in search_term:
tsquery = func.plainto_tsquery(search_term)
else:
tsquery = func.to_tsquery(search_term + ":*")
q = q.add_column(func.ts_headline(
'english',
func.concat(Supplier.summary,
' ',
Supplier.data['tools'].astext,
' ',
Supplier.data['methodologies'].astext,
' ',
Supplier.data['technologies'].astext, ''),
tsquery,
'MaxWords=25, MinWords=20, ShortWord=3, HighlightAll=FALSE, MaxFragments=1'
))
q = q.group_by(Supplier.id)
try:
code = search_query['query']['term']['code']
q = q.filter(Supplier.code == code)
except KeyError:
pass
if roles_list is not None:
if new_domains:
if EXCLUDE_LEGACY_ROLES:
d_agg = postgres.array_agg(cast(Domain.name, TEXT))
q = q.having(d_agg.contains(array(roles_list)))
else:
sr_agg = postgres.array_agg(cast(func.substring(ServiceRole.name, 8), TEXT))
q = q.having(sr_agg.contains(array(roles_list)))
if seller_types_list is not None and 'recruiter' in seller_types_list:
q = q.filter(Supplier.is_recruiter == 'true')
seller_types_list.remove('recruiter')
if len(seller_types_list) == 0:
seller_types_list = None
if seller_types_list is not None:
selected_seller_types = select(
[postgres.array_agg(column('key'))],
from_obj=func.json_each_text(Supplier.data[('seller_type',)]),
whereclause=cast(column('value'), Boolean)
).as_scalar()
q = q.filter(selected_seller_types.contains(array(seller_types_list)))
if sort_by:
if sort_by == 'latest':
ob = [desc(Supplier.last_update_time)]
else:
ob = [asc(Supplier.name)]
else:
if sort_dir == 'desc':
ob = [desc(Supplier.name)]
else:
ob = [asc(Supplier.name)]
if search_term:
ob = [desc(func.ts_rank_cd(Supplier.text_vector, tsquery))] + ob
q = q.filter(Supplier.text_vector.op('@@')(tsquery))
q = q.order_by(*ob)
raw_results = list(q)
results = []
for x in range(len(raw_results)):
if type(raw_results[x]) is Supplier:
result = raw_results[x]
else:
result = raw_results[x][0]
if raw_results[x][1] is not None and raw_results[x][1] != '':
result.summary = raw_results[x][1]
results.append(result)
sliced_results = results[offset:(offset + result_count)]
q = db.session.query(Supplier.code, Supplier.name, Supplier.summary, Supplier.is_recruiter,
Supplier.data, Domain.name.label('domain_name'),
SupplierDomain.status.label('domain_status'))\
.outerjoin(SupplierDomain, Domain)\
.filter(Supplier.id.in_([sr.id for sr in sliced_results]))\
.order_by(Supplier.name)
suppliers = [r._asdict() for r in q]
sliced_results = []
for key, group in groupby(suppliers, key=itemgetter('code')):
supplier = group.next()
supplier['seller_type'] = supplier.get('data') and supplier['data'].get('seller_type')
supplier['domains'] = {'assessed': [], 'unassessed': []}
for s in chain([supplier], group):
domain, status = s['domain_name'], s['domain_status']
if domain:
if status == 'assessed':
supplier['domains']['assessed'].append(domain)
else:
supplier['domains']['unassessed'].append(domain)
for e in ['domain_name', 'domain_status', 'data']:
supplier.pop(e, None)
sliced_results.append(supplier)
return sliced_results, len(results)
