def get_project_subjects(project, language=None):
logger.info("Getting /<project>/subjects")
connection = db_engine.connect()
project_id = get_project_id_from_name(project)
if language is not None:
query = """select
s.id, s.date_created, s.date_modified, s.deleted, s.type,
s.translation_id, s.legacy_id, s.date_born, s.date_deceased,
s.project_id, s.source,
COALESCE(t_fn.text, s.first_name) as first_name,
COALESCE(t_ln.text, s.last_name) as last_name,
COALESCE(t_plb.text, s.place_of_birth) as place_of_birth,
COALESCE(t_occ.text, s.occupation) as occupation,
COALESCE(t_prep.text, s.preposition) as preposition,
COALESCE(t_fln.text, s.full_name) as full_name,
COALESCE(t_desc.text, s.description) as description,
COALESCE(t_alias.text, s.alias) as alias,
COALESCE(t_prv.text, s.previous_last_name) as previous_last_name,
COALESCE(t_alt.text, s.alternative_form) as alternative_form
from subject s
LEFT JOIN translation_text t_fn ON t_fn.translation_id = s.translation_id and t_fn.language=:lang and t_fn.field_name='first_name'
LEFT JOIN translation_text t_ln ON t_ln.translation_id = s.translation_id and t_ln.language=:lang and t_ln.field_name='last_name'
LEFT JOIN translation_text t_plb ON t_plb.translation_id = s.translation_id and t_plb.language=:lang and t_plb.field_name='place_of_birth'
LEFT JOIN translation_text t_occ ON t_occ.translation_id = s.translation_id and t_occ.language=:lang and t_occ.field_name='occupation'
LEFT JOIN translation_text t_prep ON t_prep.translation_id = s.translation_id and t_prep.language=:lang and t_prep.field_name='preposition'
LEFT JOIN translation_text t_fln ON t_fn.translation_id = s.translation_id and t_fln.language=:lang and t_fln.field_name='full_name'
LEFT JOIN translation_text t_desc ON t_desc.translation_id = s.translation_id and t_desc.language=:lang and t_desc.field_name='description'
LEFT JOIN translation_text t_alias ON t_alias.translation_id = s.translation_id and t_alias.language=:lang and t_alias.field_name='alias'
LEFT JOIN translation_text t_prv ON t_prv.translation_id = s.translation_id and t_prv.language=:lang and t_prv.field_name='previous_last_name'
LEFT JOIN translation_text t_alt ON t_alt.translation_id = s.translation_id and t_alt.language=:lang and t_alt.field_name='alternative_form'
WHERE project_id = :p_id
"""
sql = sqlalchemy.sql.text(query)
statement = sql.bindparams(p_id=project_id, lang=language)
else:
sql = sqlalchemy.sql.text(
"SELECT * FROM subject WHERE project_id = :p_id")
statement = sql.bindparams(p_id=project_id)
results = []
for row in connection.execute(statement).fetchall():
results.append(dict(row))
connection.close()
return jsonify(results)
def get_tooltip(table, row_id, project=None, use_legacy=False):
"""
Get 'tooltip' style info for a single subject, tag, or location by its ID
table should be 'subject', 'tag', or 'location'
"""
connection = db_engine.connect()
try:
ident = int(row_id)
is_legacy_id = False
except ValueError:
ident = str(row_id)
is_legacy_id = True
if use_legacy:
ident = str(row_id)
is_legacy_id = True
project_sql = " AND project_id = :project_id "
if project is None:
project_sql = ""
if is_legacy_id:
if table == "subject":
stmnt = f"SELECT id, legacy_id, full_name, description FROM subject WHERE legacy_id=:id{project_sql}"
else:
stmnt = f"SELECT id, legacy_id, name, description FROM {table} WHERE legacy_id=:id{project_sql}"
else:
if table == "subject":
stmnt = f"SELECT id, legacy_id, full_name, description FROM subject WHERE id=:id{project_sql}"
else:
stmnt = f"SELECT id, legacy_id, name, description FROM {table} WHERE id=:id{project_sql}"
sql = sqlalchemy.sql.text(stmnt)
if project is None:
statement = sql.bindparams(id=ident)
else:
project_id = get_project_id_from_name(project)
statement = sql.bindparams(id=ident, project_id=project_id)
result = connection.execute(statement).fetchone()
connection.close()
if result is None:
return dict()
else:
return dict(result)
def get_project_works(project):
logger.info("Getting /<project>/works")
connection = db_engine.connect()
project_id = get_project_id_from_name(project)
sql = sqlalchemy.sql.text("SELECT * FROM work WHERE project_id = :p_id")
statement = sql.bindparams(p_id=project_id, )
results = []
for row in connection.execute(statement).fetchall():
results.append(dict(row))
connection.close()
return jsonify(results)
def get_legacyid_by_collection_id(project, collection_id):
logger.info("Getting /<project>/legacy/collection/<collection_id>")
connection = db_engine.connect()
sql = sqlalchemy.sql.text(
"SELECT pc.legacy_id FROM publication_collection pc WHERE pc.id = :pc_id AND deleted != 1"
)
statement = sql.bindparams(pc_id=collection_id)
results = []
for row in connection.execute(statement).fetchall():
results.append(dict(row))
connection.close()
return jsonify(results)
def get_publication(project, publication_id):
logger.info("Getting publication /{}/publication/{}".format(
project, publication_id))
connection = db_engine.connect()
sql = sqlalchemy.sql.text(
"SELECT * FROM publication WHERE id=:p_id ORDER BY name")
statement = sql.bindparams(p_id=publication_id)
results = []
for row in connection.execute(statement).fetchall():
results.append(dict(row))
connection.close()
return jsonify(results)
def get_manuscripts(project, publication_id):
logger.info("Getting manuscript /{}/manuscript/{}".format(
project, publication_id))
connection = db_engine.connect()
sql = sqlalchemy.sql.text(
'SELECT * FROM publication_manuscript WHERE publication_id=:pub_id')
statement = sql.bindparams(pub_id=publication_id)
results = []
for row in connection.execute(statement).fetchall():
results.append(dict(row))
connection.close()
return jsonify(results)
def get_collection_publication_by_legacyid(project, legacy_id):
logger.info("Getting /<project>/legacy/<legacy_id>")
connection = db_engine.connect()
project_id = get_project_id_from_name(project)
sql = sqlalchemy.sql.text(
"SELECT p.id as pub_id, pc.id as coll_id "
"FROM publication p "
"JOIN publication_collection pc ON pc.id = p.publication_collection_id "
"WHERE (p.legacy_id = :l_id OR pc.legacy_id = :l_id) AND pc.project_id = :p_id "
"ORDER BY pc.id")
statement = sql.bindparams(l_id=legacy_id, p_id=project_id)
results = []
for row in connection.execute(statement).fetchall():
results.append(dict(row))
connection.close()
return jsonify(results)
def get_collections(project):
config = get_project_config(project)
if config is None:
return jsonify({"msg": "No such project."}), 400
else:
logger.info("Getting collections /{}/collections".format(project))
connection = db_engine.connect()
status = 1 if config["show_internally_published"] else 2
project_id = get_project_id_from_name(project)
sql = sqlalchemy.sql.text(
""" SELECT id, name as title, published, date_created, date_modified, date_published_externally, legacy_id,
project_id, publication_collection_title_id, publication_collection_introduction_id, name FROM publication_collection WHERE project_id = :p_id AND published>=:p_status ORDER BY name """
)
statement = sql.bindparams(p_status=status, p_id=project_id)
results = []
for row in connection.execute(statement).fetchall():
results.append(dict(row))
connection.close()
return jsonify(results)
def get_project_locations(project):
logger.info("Getting /<project>/locations")
connection = db_engine.connect()
project_id = get_project_id_from_name(project)
# Get both locations and their translations
sql = sqlalchemy.sql.text(""" SELECT *,
( SELECT array_to_json(array_agg(row_to_json(d.*))) AS array_to_json
FROM ( SELECT tt.id, tt.text, tt."language", t.neutral_text, tt.field_name, tt.table_name, t.id as translation_id,
tt.date_modified, tt.date_created
FROM (translation t
JOIN translation_text tt ON ((tt.translation_id = t.id)))
WHERE ((t.id = l.translation_id AND tt.table_name = 'location') AND tt.deleted = 0 AND t.deleted = 0) ORDER BY translation_id DESC) d) AS translations
FROM location l WHERE l.project_id = :p_id AND l.deleted = 0 ORDER BY NAME ASC """
)
statement = sql.bindparams(p_id=project_id, )
results = []
for row in connection.execute(statement).fetchall():
results.append(dict(row))
connection.close()
return jsonify(results)