def add_media_in_export(self, query, columns):
query = query.outerjoin(
TMedias, TMedias.uuid_attached_row == self.model.tableDef.c.permId)
query = query.add_columns(
func.string_agg(TMedias.title_fr, " | ").label('titreMedia'),
func.string_agg(TMedias.description_fr, " | ").label('descMedia'),
func.string_agg(
case([(TMedias.media_url == None, TMedias.media_path)],
else_=TMedias.media_url), " | ").label("urlMedia"))
query = query.group_by(*self.model.db_cols)
added_medias_cols = ["titreMedia", "descMedia", "urlMedia"]
columns = columns + added_medias_cols
return query, columns
def shelters(shelter_id):
"""Returns specific shelter with its properties"""
result = tree()
#shelter pictures folder path
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
## shelter picture query
shelter_pictures = db.session.query(ShelterPicture.shelter_id, func.string_agg(ShelterPicture.file_name,';').label("filename"), Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)\
.group_by(ShelterPicture.shelter_id, Category.name)\
.filter(ShelterPicture.shelter_id==shelter_id)
## shelter property query
shelter_properties = Property.query.filter(
Property.shelter_id == shelter_id)
for shelter_property in shelter_properties:
result[shelter_property.shelter_id][
shelter_property.attribute.
uniqueid] = shelter_property.get_values_as_string()
for picture in shelter_pictures:
if not result[picture.shelter_id]["shelterpicture"][picture.name]:
result[picture.shelter_id]["shelterpicture"][picture.name] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[picture.shelter_id]["shelterpicture"][picture.name].append(
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename))
return jsonify(result)
def bulk_circulation_events(self):
default = str(datetime.today()).split(" ")[0]
date = flask.request.args.get("date", default)
next_date = datetime.strptime(date, "%Y-%m-%d") + timedelta(days=1)
query = self._db.query(
CirculationEvent, Identifier, Work, Edition
) \
.join(LicensePool, LicensePool.id == CirculationEvent.license_pool_id) \
.join(Identifier, Identifier.id == LicensePool.identifier_id) \
.join(Work, Work.id == LicensePool.work_id) \
.join(Edition, Edition.id == Work.presentation_edition_id) \
.filter(CirculationEvent.start >= date) \
.filter(CirculationEvent.start < next_date) \
.order_by(CirculationEvent.start.asc())
query = query \
.options(lazyload(Identifier.licensed_through)) \
.options(lazyload(Work.license_pools))
results = query.all()
work_ids = map(lambda result: result[2].id, results)
subquery = self._db \
.query(WorkGenre.work_id, Genre.name) \
.join(Genre) \
.filter(WorkGenre.work_id.in_(work_ids)) \
.order_by(WorkGenre.affinity.desc()) \
.subquery()
genre_query = self._db \
.query(subquery.c.work_id, func.string_agg(subquery.c.name, ",")) \
.select_from(subquery) \
.group_by(subquery.c.work_id)
genres = dict(genre_query.all())
header = [
"time", "event", "identifier", "identifier_type", "title", "author",
"fiction", "audience", "publisher", "language", "target_age", "genres"
]
def result_to_row(result):
(event, identifier, work, edition) = result
return [
str(event.start) or "",
event.type,
identifier.identifier,
identifier.type,
edition.title,
edition.author,
"fiction" if work.fiction else "nonfiction",
work.audience,
edition.publisher,
edition.language,
work.target_age_string,
genres.get(work.id)
]
return [header] + map(result_to_row, results), date
def get_vocabualary(only_positive=False):
session = db_session()
query = session.query(
func.string_agg(
SourceData.filtered_words,
aggregate_order_by(literal_column("' '"), SourceData.id)))
if only_positive:
query = query.filter(SourceData.is_negative == False)
query = query.first()
session.close()
return u"%s" % str(query)
def get_negative():
session = db_session()
query = session.query(Statement.id.label('statement_id'), func.string_agg(TelegramTextMessage.message,
aggregate_order_by(literal_column("'. '"),
TelegramTextMessage.created)).label('agg_message')).\
filter(Statement.reviewed==True).\
filter(Statement.is_question==Statement.false_assumption).\
filter(and_(TelegramTextMessage.channel_id==Statement.channel_id, TelegramTextMessage.user_id==Statement.user_id)).\
filter(TelegramTextMessage.message_id.between(Statement.first_msg_id, Statement.last_msg_id)).\
group_by(Statement.id).\
subquery()
query_results = session.query(query.c.statement_id, query.c.agg_message, func.length(query.c.agg_message).label('len'), TelegramChannel.tags.label('tags')).\
outerjoin(Statement, Statement.id==query.c.statement_id).\
outerjoin(TelegramChannel, TelegramChannel.channel_id==Statement.channel_id).distinct().all()
session.close()
return query_results
def do_auto_review():
session = db_session()
subquery = session.query(Statement.id.label('statement_id'), func.length(func.string_agg(TelegramTextMessage.message,
aggregate_order_by(literal_column("'. '"),
TelegramTextMessage.created))).label('agg_message')).\
filter(Statement.reviewed==False).\
filter(and_(TelegramTextMessage.channel_id==Statement.channel_id, TelegramTextMessage.user_id==Statement.user_id)).\
filter(TelegramTextMessage.message_id.between(Statement.first_msg_id, Statement.last_msg_id)).\
group_by(Statement.id).\
subquery()
query = session.query(subquery.c.statement_id).filter(
subquery.c.agg_message < MINIMIM_QUESTION_LENGHT).subquery()
stmt = update(Statement).where(Statement.id.in_(query)).values(
reviewed=True, is_question=False, false_assumption=False)
session.execute(stmt)
session.commit()
session.close()
def latestshelters(count=1):
"""
Retrieves latest shelters (updates to existing shelters also count)
:param count: number of latest shelters to return
:type count: int
"""
result = tree()
#shelter pictures folder path
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
Supercategory = db.aliased(Category)
subquery = db.session.query(Shelter).filter(
Shelter.is_published == True).order_by(desc(
Shelter.updated_at)).limit(count).subquery()
querybase = db.session.query(subquery.c.id.label("shelter_id"), Category.name.label("category_name"), Supercategory.name.label("supercategory_name"), Attribute.name, Attribute.uniqueid,func.string_agg(Value.name,';').label("value"))\
.join(Property, subquery.c.id==Property.shelter_id)\
.join(Category, Category.id==Property.category_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Supercategory, Supercategory.id==Category.parent_id)\
.join(Association, Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.order_by(desc(subquery.c.updated_at))\
.group_by(subquery.c.updated_at,subquery.c.id, Supercategory.name, Category.name, Attribute.name, Attribute.uniqueid)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), ShelterPicture.is_main_picture, Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
catquery = db.session.query(
Category.name).filter(Category.section_id != None)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
# if shelter_id:
# shelter_properties = shelter_properties.filter(Property.shelter_id==shelter_id)
# shelter_pictures = shelter_pictures.filter(ShelterPicture.shelter_id==shelter_id)
#
# if request.args.getlist('attribute'):
# attribute = request.args.getlist('attribute')
#
# subquery = db.session.query(Property.shelter_id)\
# .join(Attribute, Attribute.id==Property.attribute_id)\
# .filter(Attribute.uniqueid.in_(attribute))\
# .group_by(Property.shelter_id)
#
# shelter_properties = shelter_properties.filter(subquery.subquery().c.shelter_id==Property.shelter_id)
# shelter_pictures = shelter_pictures.filter(subquery.subquery().c.shelter_id==ShelterPicture.shelter_id)
#
# if request.args.getlist('value'):
# value = request.args.getlist('value')
# if not request.args.getlist('attribute'):
# subquery = db.session.query(Property.shelter_id)\
# .join(Attribute, Attribute.id==Property.attribute_id)\
# .filter(Property.values.any(Value.name.in_(value)))\
# .group_by(Property.shelter_id)
# else:
# subquery = subquery.filter(Property.values.any(Value.name.in_(value)))
#
# shelter_properties = shelter_properties.filter(subquery.subquery().c.shelter_id==Property.shelter_id)
# shelter_pictures = shelter_pictures.filter(subquery.subquery().c.shelter_id==ShelterPicture.shelter_id)
#
# if request.args.get('q'):
# attribute = request.args.get('q')
#
# shelter_properties = shelter_properties.join(Tsvector, Property.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
# shelter_pictures = shelter_pictures.join(Tsvector, ShelterPicture.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
for shelter_property in shelter_properties:
if not result[shelter_property.shelter_id]:
for category in catquery:
if category.name == "Identification":
result[shelter_property.shelter_id][category.name]["Cover"]
result[shelter_property.shelter_id][
category.name]["Attributes"]
result[shelter_property.shelter_id][category.name]["Pictures"]
if request.args.get('format') == 'prettytext':
result[shelter_property.shelter_id][
shelter_property.supercategory_name]["Attributes"][
shelter_property.name] = shelter_property.value
else:
result[shelter_property.shelter_id][
shelter_property.supercategory_name]["Attributes"][
shelter_property.uniqueid] = shelter_property.value
for picture in shelter_pictures:
if picture.shelter_id in result:
if picture.is_main_picture == True:
result[picture.shelter_id]["Identification"]["Cover"] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
elif not result[picture.shelter_id][picture.name]["Pictures"]:
result[picture.shelter_id][picture.name]["Pictures"] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[picture.shelter_id][picture.name]["Pictures"].append(
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename))
return jsonify(result)
def allshelters(shelter_id=None):
"""
Retrieves shelters with all their attributes and pictures.
**Example requests**:
.. sourcecode:: html
# get all shelters
GET http://0.0.0.0:5000/api/v0.2/shelters
# get shelter whith shelter ID 11
GET http://0.0.0.0:5000/api/v0.2/shelters/11
# get all shelters which have attribute 'storeys'
GET http://0.0.0.0:5000/api/v0.2/shelters?attribute=storeys
# get all shelters which have 2 storeys
GET http://0.0.0.0:5000/api/v0.2/shelters?attribute=storeys&value=2
:param shelter_id: a unique shelter ID generated by the server
:type shelter_id: int
:query format:
if set to ``prettytext``,
attribute names are retrieved as nicely formatted text
(Capital letters, special characters and spaces allowed)
:query attribute:
attribute name
:query value:
attribute value
:query q:
Full text search. Works in English language only.
"""
result = tree()
#shelter pictures folder path
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
docpath = os.path.relpath(conf.SHELTERS_DOCUMENTS_SITE_PATH)
Supercategory = db.aliased(Category)
querybase = db.session.query(Property.shelter_id, Category.name.label("category_name"), Supercategory.name.label("supercategory_name"), Attribute.name, Attribute.uniqueid,func.string_agg(Value.name,';').label("value"))\
.join(Shelter, Shelter.id==Property.shelter_id)\
.join(Category, Category.id==Property.category_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Supercategory, Supercategory.id==Category.parent_id)\
.join(Association, Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.group_by(Property.shelter_id, Supercategory.name, Category.name, Attribute.name, Attribute.uniqueid)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), ShelterPicture.is_main_picture, Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
catquery = db.session.query(
Category.name).filter(Category.section_id != None)
docquerybase = db.session.query(ShelterDocument.shelter_id, ShelterDocument.file_name.label("filename"), ShelterDocument.category_id, Category.name)\
.join(Category, Category.id == ShelterDocument.category_id)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
shelter_documents = docquerybase
if shelter_id:
shelter_properties = shelter_properties.filter(
Property.shelter_id == shelter_id)
shelter_pictures = shelter_pictures.filter(
ShelterPicture.shelter_id == shelter_id)
shelter_documents = shelter_documents.filter(
ShelterDocument.shelter_id == shelter_id)
else:
#only query published shelters if no shelter_id supplied
shelter_properties = shelter_properties.filter(
Shelter.is_published == True)
if request.args.getlist('attribute'):
attribute = request.args.getlist('attribute')
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Attribute.uniqueid.in_(attribute))\
.group_by(Property.shelter_id)
shelter_properties = shelter_properties.filter(
subquery.subquery().c.shelter_id == Property.shelter_id)
shelter_pictures = shelter_pictures.filter(
subquery.subquery().c.shelter_id == ShelterPicture.shelter_id)
shelter_documents = shelter_documents.filter(
subquery.subquery().c.shelter_id == ShelterDocument.shelter_id)
if request.args.getlist('value'):
value = request.args.getlist('value')
if not request.args.getlist('attribute'):
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Property.values.any(Value.name.in_(value)))\
.group_by(Property.shelter_id)
else:
subquery = subquery.filter(
Property.values.any(Value.name.in_(value)))
shelter_properties = shelter_properties.filter(
subquery.subquery().c.shelter_id == Property.shelter_id)
shelter_pictures = shelter_pictures.filter(
subquery.subquery().c.shelter_id == ShelterPicture.shelter_id)
shelter_documents = shelter_documents.filter(
subquery.subquery().c.shelter_id == ShelterDocument.shelter_id)
if request.args.get('q'):
attribute = request.args.get('q')
shelter_properties = shelter_properties.join(
Tsvector, Property.shelter_id == Tsvector.shelter_id).filter(
Tsvector.lexeme.match(attribute))
shelter_pictures = shelter_pictures.join(
Tsvector, ShelterPicture.shelter_id == Tsvector.shelter_id).filter(
Tsvector.lexeme.match(attribute))
shelter_documents = shelter_documents.join(
Tsvector,
ShelterDocument.shelter_id == Tsvector.shelter_id).filter(
Tsvector.lexeme.match(attribute))
#print(shelter_properties)
#print(shelter_pictures)
for shelter_property in shelter_properties:
if not result[shelter_property.shelter_id]:
for category in catquery:
if category.name == "Identification":
result[shelter_property.shelter_id][category.name]["Cover"]
result[shelter_property.shelter_id][
category.name]["Attributes"]
result[shelter_property.shelter_id][category.name]["Pictures"]
result[shelter_property.shelter_id][category.name]["Documents"]
if request.args.get('format') == 'prettytext':
result[shelter_property.shelter_id][
shelter_property.supercategory_name]["Attributes"][
shelter_property.name] = shelter_property.value
else:
result[shelter_property.shelter_id][
shelter_property.supercategory_name]["Attributes"][
shelter_property.uniqueid] = shelter_property.value
for picture in shelter_pictures:
if picture.shelter_id in result:
if picture.is_main_picture == True:
if not result[picture.shelter_id]["Identification"]["Cover"]:
result[picture.shelter_id]["Identification"]["Cover"] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[
picture.shelter_id]["Identification"]["Cover"].append(
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename))
elif not result[picture.shelter_id][picture.name]["Pictures"]:
result[picture.shelter_id][picture.name]["Pictures"] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[picture.shelter_id][picture.name]["Pictures"].append(
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename))
for document in shelter_documents:
if document.shelter_id in result:
if not result[document.shelter_id][document.name]["Documents"]:
result[document.shelter_id][document.name]["Documents"] = [
"{}/{}/{}".format(docpath, document.shelter_id,
document.filename)
]
else:
result[document.shelter_id][document.name]["Documents"].append(
"{}/{}/{}".format(docpath, document.shelter_id,
document.filename))
return jsonify(result)
def export_query(separator=";"):
ordered_incalls = aliased(
Session.query(
Incall.exten.label('exten'),
Incall.context.label('context'),
User.id.label('user_id')
)
.join(Dialaction,
and_(Dialaction.category == 'incall',
Dialaction.action == 'user',
cast(Dialaction.categoryval, Integer) == Incall.id))
.join(User,
cast(Dialaction.actionarg1, Integer) == User.id)
.order_by(Incall.exten, Incall.context)
.subquery()
)
grouped_incalls = aliased(
Session.query(
ordered_incalls.c.user_id,
func.string_agg(ordered_incalls.c.exten, separator).label('exten'),
func.string_agg(ordered_incalls.c.context, separator).label('context')
)
.group_by(ordered_incalls.c.user_id)
.subquery()
)
ordered_call_permissions = aliased(
Session.query(
RightCall.name,
cast(RightCallMember.typeval, Integer).label('user_id')
)
.join(RightCallMember,
RightCallMember.rightcallid == RightCall.id)
.join(User,
and_(RightCallMember.type == 'user',
cast(RightCallMember.typeval, Integer) == User.id))
.order_by(RightCall.name)
.subquery()
)
grouped_call_permissions = aliased(
Session.query(
ordered_call_permissions.c.user_id,
func.string_agg(ordered_call_permissions.c.name, separator).label('name')
)
.group_by(ordered_call_permissions.c.user_id)
.subquery()
)
columns = (
User.uuid,
cast(User.entity_id, String),
User.firstname,
User.lastname,
User.email,
User.mobile_phone_number,
User.outgoing_caller_id,
User.language,
User.call_permission_password,
case([(User.enabled, '1')], else_='0'),
cast(User.ring_seconds, String),
cast(User.simultaneous_calls, String),
cast(User.enablehint, String),
cast(User.enablexfer, String),
cast(User.callrecord, String),
cast(User.enableonlinerec, String),
User.userfield,
User.username,
User.password,
CtiProfile.name,
cast(User.enableclient, String),
Voicemail.name,
Voicemail.number,
Voicemail.context,
Voicemail.password,
Voicemail.email,
cast(Voicemail.attach, String),
cast(Voicemail.deletevoicemail, String),
cast(cast(Voicemail.ask_password, Integer), String),
Line.endpoint,
Line.provisioning_code,
func.coalesce(Extension.context, Line.context),
SIP.name,
SIP.secret,
Extension.exten,
grouped_incalls.c.exten,
grouped_incalls.c.context,
grouped_call_permissions.c.name,
)
query = (
Session.query(*columns)
.outerjoin(User.voicemail)
.outerjoin(User.cti_profile)
.outerjoin(User.main_line_rel)
.outerjoin(UserLine.main_line_rel)
.outerjoin(Line.sip_endpoint)
.outerjoin(UserLine.extensions)
.outerjoin(grouped_incalls,
User.id == grouped_incalls.c.user_id)
.outerjoin(grouped_call_permissions,
User.id == grouped_call_permissions.c.user_id)
)
return COLUMNS, query
def do_analyse():
print("\r\n[do_analyse...]")
min_to_end_stmnt = static_assessment.maximum_question_length // letters_per_min
created_ago = datetime.datetime.now() - datetime.timedelta(
minutes=min_to_end_stmnt)
session = db_session()
stmnts = session.query(Statement.id, Statement.channel_id, Statement.user_id, Statement.first_msg_id, Statement.last_msg_id).\
filter(and_(Statement.created<created_ago, Statement.was_processed==False)).distinct().all()
if stmnts is None or len(stmnts) == 0:
print("[do_analyse] nothing to process.")
return
else:
print("[do_analyse] to process: ", len(stmnts))
pairs = dict()
for stmnt in stmnts:
stmnt_id, channel_id, user_id, first_id, last_id = stmnt
message_text = session.query(func.string_agg(TelegramTextMessage.message,
aggregate_order_by(literal_column("'. '"),
TelegramTextMessage.created))).\
filter(and_(TelegramTextMessage.channel_id==channel_id, TelegramTextMessage.user_id==user_id)).\
filter(TelegramTextMessage.message_id.between(first_id, last_id)).\
distinct().\
all()
pairs[stmnt_id] = message_text
session.close()
questions = list()
not_question = list()
for stmnt_id, message in pairs.items():
if len(message) == 0:
print("[Message len error]")
not_question.append(stmnt_id)
continue
is_question = analyser.validate(''.join(message[0]))
if is_question:
questions.append(stmnt_id)
else:
not_question.append(stmnt_id)
session = db_session()
if len(questions) > 0:
print("[do_analyse] questions found: ", len(questions))
update_query = Statement.__table__.update().values(is_question=True, was_processed=True).\
where(Statement.id.in_(questions))
session.execute(update_query)
if len(not_question) > 0:
print("[do_analyse] not questions: ", len(not_question))
update_query_2 = Statement.__table__.update().values(is_question=False, was_processed=True).\
where(Statement.id.in_(not_question))
session.execute(update_query_2)
session.commit()
session.close()
print("[do_analyse] done.")
def allshelters():
"""Returns all shelters and their properties"""
result = tree()
#shelter pictures folder path
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
querybase = db.session.query(Property.shelter_id,Attribute.name,Attribute.uniqueid,func.string_agg(Value.name,';').label("value"))\
.join(Shelter)\
.join(Attribute)\
.join(Association,Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.filter(Shelter.is_published == True)\
.group_by(Property.shelter_id, Attribute.name, Attribute.uniqueid)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
if request.args.getlist('attribute'):
attribute = request.args.getlist('attribute')
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Attribute.uniqueid.in_(attribute))\
.group_by(Property.shelter_id)
shelter_properties = querybase.filter(
subquery.subquery().c.shelter_id == Property.shelter_id)
shelter_pictures = picquerybase.filter(
subquery.subquery().c.shelter_id == ShelterPicture.shelter_id)
if request.args.getlist('value'):
value = request.args.getlist('value')
if not request.args.getlist('attribute'):
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Property.values.any(Value.name.in_(value)))\
.group_by(Property.shelter_id)
else:
subquery = subquery.filter(
Property.values.any(Value.name.in_(value)))
shelter_properties = querybase.filter(
subquery.subquery().c.shelter_id == Property.shelter_id)
shelter_pictures = picquerybase.filter(
subquery.subquery().c.shelter_id == ShelterPicture.shelter_id)
#print(shelter_properties)
#print(shelter_pictures)
if request.args.get('format') == 'prettytext':
for shelter_property in shelter_properties:
result[shelter_property.shelter_id][
shelter_property.name] = shelter_property.value
for picture in shelter_pictures:
if not result[picture.shelter_id]["shelterpicture"][picture.name]:
result[picture.shelter_id]["shelterpicture"][picture.name] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[picture.shelter_id]["shelterpicture"][
picture.name].append("{}/{}/{}".format(
picpath, picture.shelter_id, picture.filename))
else:
for shelter_property in shelter_properties:
result[shelter_property.shelter_id][
shelter_property.uniqueid] = shelter_property.value
for picture in shelter_pictures:
if not result[picture.shelter_id]["shelterpicture"][picture.name]:
result[picture.shelter_id]["shelterpicture"][picture.name] = [
"{}/{}/{}".format(picpath, picture.shelter_id,
picture.filename)
]
else:
result[picture.shelter_id]["shelterpicture"][
picture.name].append("{}/{}/{}".format(
picpath, picture.shelter_id, picture.filename))
return jsonify(result)
def latestshelters(count=1):
"""
Retrieves latest shelters (updates to existing shelters also count).
Only retrieves shelters that have pictures. If no count parameter is supplied, the API
retrieves the latest shelter.
:param count: number of latest shelters to return
:type count: int
**Example requests**:
.. sourcecode:: html
# get latest shelter
GET {0}/api/v0.2/shelters/latest
# get the 3 latest shelters
GET {0}/api/v0.2/shelters/latest/3
"""
result = tree()
pretty = False
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
Supercategory = db.aliased(Category)
subsubquery = db.session.query(ShelterPicture.shelter_id).filter(ShelterPicture.is_main_picture == True).subquery()
subquery= db.session.query(Shelter)\
.filter(Shelter.is_published == True)\
.filter(Shelter.id.in_(subsubquery))\
.order_by(desc(Shelter.updated_at))\
.limit(count).subquery()
querybase = db.session.query(subquery.c.id.label("shelter_id"), Category.name.label("category_name"), Supercategory.name.label("supercategory_name"), Attribute.name, Attribute.uniqueid, Attribute.type, func.string_agg(Value.name,';').label("value"))\
.join(Property, subquery.c.id==Property.shelter_id)\
.join(Category, Category.id==Property.category_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Supercategory, Supercategory.id==Category.parent_id)\
.join(Association, Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.order_by(desc(subquery.c.updated_at))\
.group_by(subquery.c.updated_at,subquery.c.id, Supercategory.name, Category.name, Attribute.name, Attribute.uniqueid,Attribute.type)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), ShelterPicture.is_main_picture, Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
catquery = db.session.query(Category.name).filter(Category.section_id != None)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
if request.args.get('format') == 'prettytext':
pretty = True
result = populate_dictree(shelter_properties, catquery, result, prettytext=pretty)
populate_pictures(shelter_pictures, result, picpath)
return jsonify(result)
def allshelters(shelter_id=None):
"""
Retrieves shelters with all of their attributes and pictures.
:param shelter_id: a unique shelter ID generated by the server
:type shelter_id: int
:query format:
if set to ``prettytext``,
attribute names are retrieved as nicely formatted text
(Capital letters, special characters and spaces allowed)
:query attribute:
attribute name
:query value:
attribute value
:query q:
Full text search. Works in English language only.
**Example requests**:
.. sourcecode:: html
# get all shelters
GET {0}/api/v0.2/shelters
# get shelter whith shelter ID 11
GET {0}/api/v0.2/shelters/11
# get all shelters which have attribute 'storeys'
GET {0}/api/v0.2/shelters?attribute=storeys
# get all shelters which have 2 storeys
GET {0}/api/v0.2/shelters?attribute=storeys&value=2
"""
result = tree()
picpath = os.path.relpath(conf.SHELTERS_PICTURES_SITE_PATH)
docpath = os.path.relpath(conf.SHELTERS_DOCUMENTS_SITE_PATH)
pretty = False
Supercategory = db.aliased(Category)
querybase = db.session.query(Property.shelter_id, Category.name.label("category_name"), Supercategory.name.label("supercategory_name"), Attribute.name, Attribute.uniqueid, Attribute.type, func.string_agg(Value.name,';').label("value"))\
.join(Shelter, Shelter.id==Property.shelter_id)\
.join(Category, Category.id==Property.category_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.join(Supercategory, Supercategory.id==Category.parent_id)\
.join(Association, Property.id==Association.property_id)\
.join(Value, Association.value_id==Value.id)\
.group_by(Property.shelter_id, Supercategory.name, Category.name, Attribute.name, Attribute.uniqueid, Attribute.type)
picquerybase = db.session.query(ShelterPicture.shelter_id, ShelterPicture.file_name.label("filename"), ShelterPicture.is_main_picture, Category.name)\
.join(Category, Category.id == ShelterPicture.category_id)
catquery = db.session.query(Category.name).filter(Category.section_id != None)
docquerybase = db.session.query(ShelterDocument.shelter_id, ShelterDocument.file_name.label("filename"), ShelterDocument.category_id, Category.name)\
.join(Category, Category.id == ShelterDocument.category_id)
##queries if no request arguments
shelter_properties = querybase
shelter_pictures = picquerybase
shelter_documents = docquerybase
if shelter_id:
shelter_properties = querybase.filter(Property.shelter_id==shelter_id)
shelter_pictures = picquerybase.filter(ShelterPicture.shelter_id==shelter_id)
shelter_documents = docquerybase.filter(ShelterDocument.shelter_id==shelter_id)
else:
#only query published shelters if no shelter_id supplied
shelter_properties = shelter_properties.filter(Shelter.is_published == True)
if request.args.getlist('attribute'):
attribute = request.args.getlist('attribute')
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Attribute.uniqueid.in_(attribute))\
.group_by(Property.shelter_id)
shelter_properties = shelter_properties.filter(subquery.subquery().c.shelter_id==Property.shelter_id)
shelter_pictures = shelter_pictures.filter(subquery.subquery().c.shelter_id==ShelterPicture.shelter_id)
shelter_documents = shelter_documents.filter(subquery.subquery().c.shelter_id==ShelterDocument.shelter_id)
if request.args.getlist('value'):
value = request.args.getlist('value')
if not request.args.getlist('attribute'):
subquery = db.session.query(Property.shelter_id)\
.join(Attribute, Attribute.id==Property.attribute_id)\
.filter(Property.values.any(Value.name.in_(value)))\
.group_by(Property.shelter_id)
else:
subquery = subquery.filter(Property.values.any(Value.name.in_(value)))
shelter_properties = shelter_properties.filter(subquery.subquery().c.shelter_id==Property.shelter_id)
shelter_pictures = shelter_pictures.filter(subquery.subquery().c.shelter_id==ShelterPicture.shelter_id)
shelter_documents = shelter_documents.filter(subquery.subquery().c.shelter_id==ShelterDocument.shelter_id)
if request.args.get('q'):
attribute = request.args.get('q')
shelter_properties = shelter_properties.join(Tsvector, Property.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
shelter_pictures = shelter_pictures.join(Tsvector, ShelterPicture.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
shelter_documents = shelter_documents.join(Tsvector, ShelterDocument.shelter_id==Tsvector.shelter_id).filter(Tsvector.lexeme.match(attribute))
if request.args.get('format') == 'prettytext':
pretty = True
result = populate_dictree(shelter_properties, catquery, result, prettytext=pretty)
populate_pictures(shelter_pictures, result, picpath)
populate_documents(shelter_documents, result, docpath)
return jsonify(result)
def export_query(tenant_uuid, separator=";"):
ordered_incalls = aliased(
Session.query(
Extension.exten.label('exten'),
Extension.context.label('context'),
User.id.label('user_id'),
).select_from(Incall).join(
Dialaction,
and_(
Dialaction.category == 'incall',
Dialaction.action == 'user',
cast(Dialaction.categoryval, Integer) == Incall.id,
),
).join(User,
cast(Dialaction.actionarg1, Integer) == User.id).join(
Extension,
and_(
Extension.type == 'incall',
cast(Extension.typeval, Integer) == Incall.id,
),
).order_by(Extension.exten, Extension.context).subquery())
grouped_incalls = aliased(
Session.query(
ordered_incalls.c.user_id,
func.string_agg(ordered_incalls.c.exten, separator).label('exten'),
func.string_agg(ordered_incalls.c.context,
separator).label('context'),
).group_by(ordered_incalls.c.user_id).subquery())
ordered_call_permissions = aliased(
Session.query(
RightCall.name,
cast(RightCallMember.typeval, Integer).label('user_id')).join(
RightCallMember,
RightCallMember.rightcallid == RightCall.id).join(
User,
and_(
RightCallMember.type == 'user',
cast(RightCallMember.typeval, Integer) == User.id,
),
).order_by(RightCall.name).subquery())
grouped_call_permissions = aliased(
Session.query(
ordered_call_permissions.c.user_id,
func.string_agg(ordered_call_permissions.c.name,
separator).label('name'),
).group_by(ordered_call_permissions.c.user_id).subquery())
columns = (
User.uuid,
User.subscription_type,
User.firstname,
User.lastname,
User.email,
User.mobile_phone_number,
User.outgoing_caller_id,
User.language,
User.call_permission_password,
case([(User.enabled, '1')], else_='0'),
cast(User.ring_seconds, String),
cast(User.simultaneous_calls, String),
cast(User.enablehint, String),
cast(User.enablexfer, String),
cast(User.dtmf_hangup, String),
case([(User.call_record_outgoing_external_enabled, '1')], else_='0'),
case([(User.call_record_outgoing_internal_enabled, '1')], else_='0'),
case([(User.call_record_incoming_external_enabled, '1')], else_='0'),
case([(User.call_record_incoming_internal_enabled, '1')], else_='0'),
cast(User.enableonlinerec, String),
User.userfield,
Voicemail.name,
Voicemail.number,
Voicemail.context,
Voicemail.password,
Voicemail.email,
cast(Voicemail.attach, String),
cast(Voicemail.deletevoicemail, String),
cast(cast(Voicemail.ask_password, Integer), String),
case(
[
(Line.endpoint_sip_uuid != None, 'sip'), # noqa
(Line.endpoint_sccp_id != None, 'sccp'), # noqa
(Line.endpoint_custom_id != None, 'custom'), # noqa
],
else_='',
),
Line.provisioning_code,
func.coalesce(Extension.context, Line.context),
EndpointSIP.username,
EndpointSIP.password,
Extension.exten,
grouped_incalls.c.exten,
grouped_incalls.c.context,
grouped_call_permissions.c.name,
)
query = (Session.query(*columns).outerjoin(User.voicemail).outerjoin(
User.main_line_rel).outerjoin(UserLine.main_line_rel).outerjoin(
Line.endpoint_sip).outerjoin(Line.line_extensions).outerjoin(
LineExtension.main_extension_rel).outerjoin(
grouped_incalls,
User.id == grouped_incalls.c.user_id).outerjoin(
grouped_call_permissions,
User.id == grouped_call_permissions.c.user_id).filter(
User.tenant_uuid == tenant_uuid))
return COLUMNS, query
