def map(object_id: int):
myjson = Data.get_data(object_id)
g.cursor.execute('SELECT * FROM thanados.typesjson;')
types = g.cursor.fetchall()
g.cursor.execute(
'SELECT DISTINCT t.id, s.openatlas_class_name FROM thanados.typesforjson t LEFT JOIN thanados.searchdata s ON t.id::INT = s.type_id::INT WHERE s.site_id = %(id)s',
{'id': object_id})
jsontypes = g.cursor.fetchall()
availabletypes = {
'gravetypes': [],
'burialtypes': [],
'findtypes': [],
'bonetypes': []
}
for row in jsontypes:
if row.openatlas_class_name == 'feature':
availabletypes['gravetypes'].append(row.id)
if row.openatlas_class_name == 'stratigraphic_unit':
availabletypes['burialtypes'].append(row.id)
if row.openatlas_class_name == 'artifact':
availabletypes['findtypes'].append(row.id)
if row.openatlas_class_name == 'human_remains':
availabletypes['bonetypes'].append(row.id)
site_list = Data.get_list()
return render_template('map/map.html',
myjson=myjson[0].data,
object_id=object_id,
typesjson=types[0].types,
availables=availabletypes,
site_list=site_list,
leafletVersion="1.4")
def search():
site_list = Data.get_list()
form = SearchForm()
search_result = ''
if form.validate_on_submit():
g.cursor.execute(form.term.data)
for row in g.cursor.fetchall():
search_result += row.child_name + '<br>'
g.cursor.execute('SELECT * FROM thanados.typesjson;')
types = g.cursor.fetchall()
return render_template('search/search.html', form=form, search_result=search_result, typesjson=types[0].types, sitelist=site_list[0].sitelist)
def index():
site_list = Data.get_list()
sql0 = """
DROP TABLE IF EXISTS thanados.EntCount;
CREATE TABLE thanados.EntCount AS
SELECT * FROM thanados.searchdata WHERE site_id IN %(site_ids)s
"""
g.cursor.execute(sql0, {'site_ids': tuple(g.site_list)})
sql = """
SELECT '['
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE system_type = 'place' AND Path LIKE 'Place > Burial Site%') || ','
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE system_type = 'feature' AND Path LIKE 'Feature > Grave%') || ','
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE system_type = 'stratigraphic unit' AND Path LIKE 'Stratigraphic Unit > Burial%') || ','
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE system_type = 'find' AND Path LIKE 'Find >%') || ']'
"""
g.cursor.execute(sql)
counts = g.cursor.fetchone()
return render_template("/index/index.html", sitelist=site_list[0].sitelist, entitycount=counts[0])
def index():
site_list = Data.get_list()
sql = """
SELECT '['
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE openatlas_class_name = 'place'
AND site_id IN %(site_ids)s AND Path LIKE 'Place > Burial Site%%') || ','
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE openatlas_class_name = 'feature'
AND site_id IN %(site_ids)s AND Path LIKE 'Feature > Grave%%') || ','
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE openatlas_class_name = 'stratigraphic_unit'
AND site_id IN %(site_ids)s AND Path LIKE 'Stratigraphic unit > Burial%%') || ','
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE openatlas_class_name = 'artifact'
AND site_id IN %(site_ids)s AND Path LIKE 'Artifact >%%') || ','
|| (SELECT count(child_id)::TEXT FROM thanados.EntCount WHERE openatlas_class_name = 'human_remains'
AND site_id IN %(site_ids)s AND Path LIKE 'Human remains >%%') || ']'
"""
g.cursor.execute(sql, {'site_ids': tuple(g.site_list)})
counts = g.cursor.fetchone()
return render_template("/index/index.html",
isIndex=True,
sitelist=site_list[0].sitelist,
entitycount=counts[0])
def charts():
depth = Data.get_depth()
site_ids = tuple(g.site_list)
constr = Data.get_type_data('grave', 'Grave Constr%', site_ids)
gravetypes = Data.get_type_data('grave', 'Feature%', site_ids)
graveshape = Data.get_type_data('grave', 'Grave Shape%', site_ids)
burialtype = Data.get_type_data('burial', 'Stratigraphic Unit%', site_ids)
sex = Data.get_sex()
site_list = Data.get_list()
orientation = Data.get_orientation()
azimuth = Data.get_azimuth()
g.cursor.execute(
'select JSONB_agg(age) as age FROM thanados.ageatdeath as age;')
age = g.cursor.fetchall()
sql_finds = """
SELECT mydata::jsonb FROM (
SELECT '{"types": [' || string_agg (jsonstring, ', ') || ']}' AS mydata FROM
(SELECT '{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Weapon", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '%Weapons%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Riding Equipment", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '%Rider%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Knife", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '%Knife%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Other Equipment", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '% Equipment %' AND t.path NOT LIKE '%Firem%' AND t.path NOT LIKE '%Knife%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Other Finds", "count": ' ||
count(t.sitename) FILTER (WHERE t.path NOT LIKE '%Accessories%' AND t.path NOT LIKE '%Pottery%' AND t.path NOT LIKE '%Weapons%' AND t.path NOT LIKE '%Equipment%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Firemaking Equ.", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '%Firem%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Belt Accessories", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '%Belt Accessories%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Other Accessories", "count": ' ||
count(t.sitename) FILTER (WHERE t.path NOT LIKE '%Jewellery%' AND t.path NOT LIKE '%Belt Accessories%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Pottery", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '%Pottery%') || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Other Jewelry", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '%Jewellery%' AND t.path NOT LIKE '%Earring%' ) || '}, ' ||
'{"site_id": ' || t.id || ', "site": "' || t.sitename || '", "type": "Temple Ring", "count": ' ||
count(t.sitename) FILTER (WHERE t.path LIKE '%Earring%') || '}' AS jsonstring
FROM
(SELECT
m.id,
m.name AS sitename,
t.name AS type,
t.path
FROM model.entity m
JOIN thanados.entities e ON e.parent_id = m.id
JOIN thanados.entities e1 ON e1.parent_id = e.child_id
JOIN thanados.entities e2 ON e2.parent_id = e1.child_id
JOIN thanados.types_main t ON e2.child_id = t.entity_id
WHERE t.path LIKE 'Find%'
--GROUP BY m.id, sitename, type, t.path
ORDER BY 1, 4) t GROUP BY t.sitename, t.id) j)j
"""
g.cursor.execute(sql_finds)
finds = g.cursor.fetchall()
return render_template('charts/charts.html',
depth_data=depth[0].depth,
azimuth_data=azimuth[0].azimuth,
gravetypes_json=gravetypes[0],
construction=constr[0],
burial_types=burialtype[0],
find_types=finds[0].mydata,
age=age[0],
orientation_data=orientation[0].orientation,
sex_data=sex[0].sex,
grave_shape=graveshape[0],
sitelist=site_list[0].sitelist)
def sites():
site_list = Data.get_list()
return render_template('/sites/sites.html', sitelist=site_list[0].sitelist)
def entity_view(object_id: int, format_=None):
system_type = Data.get_system_type(object_id)
place_id = Data.get_parent_place_id(object_id)
data = Data.get_data(place_id)[0].data
entity = {}
sql = """
SELECT name, description FROM model.entity WHERE id = %(id)s
"""
g.cursor.execute(sql, {"id": object_id})
result = g.cursor.fetchone()
entity['name'] = result.name
entity['description'] = result.description
findtree = [{'name': 'finds', 'id': 13368, 'size': 0}]
sqlBubblePrepare = """
DROP TABLE IF EXISTS thanados.typeBubble;
CREATE TABLE thanados.typeBubble AS
WITH RECURSIVE supertypes AS (
SELECT
name,
name_path,
parent_id,
id,
0 as count
FROM
thanados.types_all
WHERE
id IN (SELECT id from thanados.types_all t JOIN thanados.searchdata s ON t.id = s.type_id WHERE t.topparent = '13368' AND s.site_id = %(site_id)s GROUP BY name, id, parent_id)
UNION
SELECT
l.name,
l.name_path,
l.parent_id,
l.id,
0 as count
FROM
thanados.types_all l JOIN supertypes s ON s.parent_id = l.id
) SELECT
*
FROM
supertypes ORDER BY name_path;
UPDATE thanados.typeBubble t SET count = l.size FROM (SELECT name, id, COUNT(type_id) AS size FROM thanados.types_all t LEFT JOIN thanados.searchdata s ON t.id = s.type_id WHERE s.site_id = %(site_id)s GROUP BY name, id) l WHERE t.id = l.id;
"""
g.cursor.execute(sqlBubblePrepare, {'site_id': place_id})
def getBubblechildren(id, node):
sql_getChildren = """
SELECT name, id, count AS size FROM thanados.typeBubble t WHERE t.parent_id = %(id)s;
"""
g.cursor.execute(sql_getChildren, {'id': id, 'site_id': place_id})
results = g.cursor.fetchall()
if results:
node['children'] = []
for row in results:
if row.size:
size = row.size
else:
size = 0
currentnode = {'name': row.name, 'id': row.id, 'size': size}
node['children'].append(currentnode)
getBubblechildren(row.id, currentnode)
getBubblechildren(findtree[0]['id'], findtree[0])
if format_ == 'json':
return json.dumps(data)
if format_ == 'network':
network = Data.getNetwork(object_id)
return render_template('entity/network.html',
place_id=place_id,
object_id=object_id,
mysitejson=data,
system_type=system_type,
entity=entity,
network=network)
if format_ == 'dashboard':
def getDims(_dim):
sql_dim = """
SELECT
parent_id AS "site_id",
site_name AS "label",
string_to_array('0-20, 20-40, 40-60, 60-80, 80-100, 100-120, 120-140, 140-160, 160-180, 180-200, 200-220, 220-240, 240-260, 260-280, 280-300, 300-320, 320-340, 340-360, 360-380, 380-400, 400-420, 420-440, 440-460, 460-480, 480-500, 500-520, 520-540, 540-560, 560-580, 580-600, over 600', ',') AS labels,
string_to_array(count(*) FILTER (WHERE VALUE <= 20) || ',' ||
count(*) FILTER (WHERE VALUE > 20 AND VALUE <= 40) || ',' ||
count(*) FILTER (WHERE VALUE > 40 AND VALUE <= 60) || ',' ||
count(*) FILTER (WHERE VALUE > 60 AND VALUE <= 80) || ',' ||
count(*) FILTER (WHERE VALUE > 80 AND VALUE <= 100) || ',' ||
count(*) FILTER (WHERE VALUE > 100 AND VALUE <= 120) || ',' ||
count(*) FILTER (WHERE VALUE > 120 AND VALUE <= 140) || ',' ||
count(*) FILTER (WHERE VALUE > 140 AND VALUE <= 160) || ',' ||
count(*) FILTER (WHERE VALUE > 160 AND VALUE <= 180) || ',' ||
count(*) FILTER (WHERE VALUE > 180 AND VALUE <= 200) || ',' ||
count(*) FILTER (WHERE VALUE > 200 AND VALUE <= 220) || ',' ||
count(*) FILTER (WHERE VALUE > 220 AND VALUE <= 240) || ',' ||
count(*) FILTER (WHERE VALUE > 240 AND VALUE <= 260) || ',' ||
count(*) FILTER (WHERE VALUE > 260 AND VALUE <= 280) || ',' ||
count(*) FILTER (WHERE VALUE > 280 AND VALUE <= 300) || ',' ||
count(*) FILTER (WHERE VALUE > 300 AND VALUE <= 320) || ',' ||
count(*) FILTER (WHERE VALUE > 320 AND VALUE <= 340) || ',' ||
count(*) FILTER (WHERE VALUE > 340 AND VALUE <= 360) || ',' ||
count(*) FILTER (WHERE VALUE > 360 AND VALUE <= 380) || ',' ||
count(*) FILTER (WHERE VALUE > 380 AND VALUE <= 400) || ',' ||
count(*) FILTER (WHERE VALUE > 300 AND VALUE <= 420) || ',' ||
count(*) FILTER (WHERE VALUE > 420 AND VALUE <= 440) || ',' ||
count(*) FILTER (WHERE VALUE > 440 AND VALUE <= 460) || ',' ||
count(*) FILTER (WHERE VALUE > 460 AND VALUE <= 480) || ',' ||
count(*) FILTER (WHERE VALUE > 480 AND VALUE <= 500) || ',' ||
count(*) FILTER (WHERE VALUE > 500 AND VALUE <= 520) || ',' ||
count(*) FILTER (WHERE VALUE > 520 AND VALUE <= 540) || ',' ||
count(*) FILTER (WHERE VALUE > 540 AND VALUE <= 560) || ',' ||
count(*) FILTER (WHERE VALUE > 560 AND VALUE <= 580) || ',' ||
count(*) FILTER (WHERE VALUE > 580 AND VALUE <= 600) || ',' ||
count(*) FILTER (WHERE VALUE > 600) , ',')::int[] AS data
FROM (
SELECT g.parent_id,
s.name AS site_name,
d.value::double precision
FROM thanados.tbl_sites s
JOIN thanados.graves g ON g.parent_id = s.id
JOIN thanados.dimensiontypes d ON g.child_id = d.entity_id
WHERE d.name = %(dim)s AND g.parent_id = %(place_id)s
) v
GROUP BY parent_id, site_name;
"""
g.cursor.execute(sql_dim, {'place_id': place_id, 'dim': _dim})
result = g.cursor.fetchone()
if result:
_data = {"labels": result.labels, "datasets": result.data}
return _data
else:
return {"labels": [], "datasets": []}
def getDegs():
sql_deg = """
SELECT
parent_id AS "site_id",
site_name AS "label",
string_to_array('0-20, 20-40, 40-60, 60-80, 80-100, 100-120, 120-140, 140-160, 160-180, 180-200, 200-220, 220-240, 240-260, 260-280, 280-300, 300-320, 320-340, 340-360', ',') AS labels,
string_to_array(count(*) FILTER (WHERE VALUE <= 20) || ',' ||
count(*) FILTER (WHERE VALUE > 20 AND VALUE <= 40) || ',' ||
count(*) FILTER (WHERE VALUE > 40 AND VALUE <= 60) || ',' ||
count(*) FILTER (WHERE VALUE > 60 AND VALUE <= 80) || ',' ||
count(*) FILTER (WHERE VALUE > 80 AND VALUE <= 100) || ',' ||
count(*) FILTER (WHERE VALUE > 100 AND VALUE <= 120) || ',' ||
count(*) FILTER (WHERE VALUE > 120 AND VALUE <= 140) || ',' ||
count(*) FILTER (WHERE VALUE > 140 AND VALUE <= 160) || ',' ||
count(*) FILTER (WHERE VALUE > 160 AND VALUE <= 180) || ',' ||
count(*) FILTER (WHERE VALUE > 180 AND VALUE <= 200) || ',' ||
count(*) FILTER (WHERE VALUE > 200 AND VALUE <= 220) || ',' ||
count(*) FILTER (WHERE VALUE > 220 AND VALUE <= 240) || ',' ||
count(*) FILTER (WHERE VALUE > 240 AND VALUE <= 260) || ',' ||
count(*) FILTER (WHERE VALUE > 260 AND VALUE <= 280) || ',' ||
count(*) FILTER (WHERE VALUE > 280 AND VALUE <= 300) || ',' ||
count(*) FILTER (WHERE VALUE > 300 AND VALUE <= 320) || ',' ||
count(*) FILTER (WHERE VALUE > 320 AND VALUE <= 340) || ',' ||
count(*) FILTER (WHERE VALUE > 340 AND VALUE <= 360), ',')::int[] AS data
FROM (
SELECT g.parent_id,
s.name AS site_name,
d.value::double precision
FROM thanados.tbl_sites s
JOIN thanados.graves g ON g.parent_id = s.id
JOIN thanados.burials b ON b.parent_id = g.child_id
JOIN thanados.dimensiontypes d ON b.child_id = d.entity_id
WHERE d.name = 'Degrees' AND g.parent_id = %(place_id)s
) v
GROUP BY parent_id, site_name;
"""
g.cursor.execute(sql_deg, {'place_id': place_id})
result = g.cursor.fetchone()
if result:
_data = {"labels": result.labels, "datasets": result.data}
return _data
else:
_data = {"labels": [], "datasets": []}
return _data
def getAzimuth():
sql_azimuth = """
SELECT
parent_id AS "site_id",
site_name AS "label",
string_to_array('0-20, 20-40, 40-60, 60-80, 80-100, 100-120, 120-140, 140-160, 160-180', ',') AS labels,
string_to_array(count(*) FILTER (WHERE VALUE <= 20) || ',' ||
count(*) FILTER (WHERE VALUE > 20 AND VALUE <= 40) || ',' ||
count(*) FILTER (WHERE VALUE > 40 AND VALUE <= 60) || ',' ||
count(*) FILTER (WHERE VALUE > 60 AND VALUE <= 80) || ',' ||
count(*) FILTER (WHERE VALUE > 80 AND VALUE <= 100) || ',' ||
count(*) FILTER (WHERE VALUE > 100 AND VALUE <= 120) || ',' ||
count(*) FILTER (WHERE VALUE > 120 AND VALUE <= 140) || ',' ||
count(*) FILTER (WHERE VALUE > 140 AND VALUE <= 160) || ',' ||
count(*) FILTER (WHERE VALUE > 160 AND VALUE <= 180), ',')::int[] AS data
FROM (
SELECT g.parent_id,
s.name AS site_name,
d.value::double precision
FROM thanados.tbl_sites s
JOIN thanados.graves g ON g.parent_id = s.id
JOIN thanados.dimensiontypes d ON g.child_id = d.entity_id
WHERE d.name = 'Azimuth' AND g.parent_id = %(place_id)s
) v
GROUP BY parent_id, site_name;
"""
g.cursor.execute(sql_azimuth, {'place_id': place_id})
result = g.cursor.fetchone()
if result:
_data = {"labels": result.labels, "datasets": result.data}
return _data
else:
return {"labels": [], "datasets": []}
def getAges():
sql_age = """
SELECT ages FROM thanados.dashage WHERE site_id = %(iwas)s
"""
g.cursor.execute(sql_age, {'iwas': place_id})
result = g.cursor.fetchone()
if result:
_data = result.ages
return _data
else:
return []
def getValueAges():
minAges = (118152, 118134, 117199)
maxAges = (118151, 118132, 117200)
sqlValueAges = """
DROP TABLE IF EXISTS thanados.valueAges;
CREATE TABLE thanados.valueAges AS
SELECT child_name, burial_id, count(burial_id), min, NULL::INT AS max
FROM thanados.searchdata
WHERE type_id IN (118152, 118134, 117199)
AND site_id = %(place_id)s GROUP BY child_name, burial_id, min ORDER BY count DESC;
UPDATE thanados.valueAges v SET max = d.max FROM
(SELECT child_name, burial_id, count(burial_id), min as max
FROM thanados.searchdata WHERE type_id IN (118151, 118132, 117200)
AND site_id = %(place_id)s GROUP BY child_name, burial_id, min) d WHERE v.burial_id = d.burial_id;
DELETE FROM thanados.valueAges WHERE min ISNULL OR max ISNULL;
SELECT jsonb_agg(jsonb_build_object(
'name', v.name,
'from', v.from,
'to', v.to
)) AS ages FROM (SELECT child_name AS name, min AS from, max AS to FROM thanados.valueAges) v;
"""
g.cursor.execute(sqlValueAges, {'place_id': place_id})
result = g.cursor.fetchone()
if result.ages:
_data = result.ages
return _data
else:
return []
def getSex():
sql_sex = """
DROP TABLE IF EXISTS thanados.sexDash;
CREATE TABLE thanados.sexDash AS (
SELECT burial_id, type, type_id
FROM thanados.searchdata WHERE type_id IN (25, 22374, 24, 22373, 118129, 22375) AND site_id = %(place_id)s);
INSERT INTO thanados.sexDash (
SELECT burial_id, 'subadult', '0'
FROM thanados.searchdata WHERE
type_id NOT IN (22283, 22284, 117201, 22285, 22286, 22287, 22288)
AND path LIKE 'Anthropology > Age%%'
AND site_id = %(place_id)s
AND burial_id NOT IN (SELECT burial_id FROM thanados.sexDash));
INSERT INTO thanados.sexDash (
SELECT burial_id, 'subadult', '0'
FROM thanados.searchdata WHERE
type_id NOT IN (118152, 118134, 117199)
AND path LIKE 'Absolute Age%%'
AND type_id NOT IN (118152, 118134, 117199)
AND min < 18
AND site_id = %(place_id)s
AND burial_id NOT IN (SELECT burial_id FROM thanados.sexDash));
SELECT jsonb_agg(jsonb_build_object(
'name', b.type,
'count', b.count)) AS sex FROM (SELECT type, count(type) FROM
(SELECT type
FROM thanados.sexDash) a GROUP BY type ORDER BY type ASC ) b
"""
g.cursor.execute(sql_sex, {'place_id': place_id})
result = g.cursor.fetchone()
if result.sex:
_data = result.sex
return _data
else:
return []
def getgender():
sql_gender = """
SELECT jsonb_agg(jsonb_build_object(
'name', b.type,
'count', b.count
)) AS gender FROM (SELECT type, count(type) FROM
(SELECT burial_id, type, type_id
FROM thanados.searchdata WHERE type_id IN (120168, 120167)
AND site_id = %(place_id)s) a GROUP BY type ORDER BY type DESC) b
"""
g.cursor.execute(sql_gender, {'place_id': place_id})
result = g.cursor.fetchone()
if result.gender:
_data = result.gender
return _data
else:
return []
def getSexDepth():
sqlSexDepth = """
DROP TABLE IF EXISTS thanados.sexDash;
CREATE TABLE thanados.sexDash AS (
SELECT burial_id, type, type_id
FROM thanados.searchdata
WHERE type_id IN (25, 22374, 24, 22373)
AND site_id = %(place_id)s);
INSERT INTO thanados.sexDash (
SELECT burial_id, type, searchdata.type_id
FROM thanados.searchdata
WHERE type_id IN (120168, 120167)
AND site_id = %(place_id)s
AND burial_id NOT IN (SELECT burial_id FROM thanados.sexDash));
INSERT INTO thanados.sexDash (
SELECT burial_id, 'Subadult', '0'
FROM thanados.searchdata
WHERE type_id NOT IN (22283, 22284, 117201, 22285, 22286, 22287, 22288)
AND path LIKE 'Anthropology > Age%%'
AND site_id = %(place_id)s
AND burial_id NOT IN (SELECT burial_id FROM thanados.sexDash));
INSERT INTO thanados.sexDash (
SELECT burial_id, 'Subadult', '0'
FROM thanados.searchdata
WHERE type_id NOT IN (118152, 118134, 117199)
AND path LIKE 'Absolute Age%%'
AND type_id NOT IN (118152, 118134, 117199)
AND min < 18
AND site_id = %(place_id)s
AND burial_id NOT IN (SELECT burial_id FROM thanados.sexDash));
UPDATE thanados.sexDash SET type = REPLACE (
type,
'?',
''
);
DROP TABLE IF EXISTS thanados.sexGraveDepth;
CREATE TABLE thanados.sexGraveDepth AS
(SELECT s.type, g.min FROM thanados.sexDash s JOIN thanados.burials b ON s.burial_id = b.child_id JOIN thanados.searchdata g ON g.child_id = b.parent_id
WHERE g.type = 'Height' AND g.system_type = 'feature');
DROP TABLE IF EXISTS thanados.sexGraveDepthJSON;
CREATE TABLE thanados.sexGraveDepthJSON AS
SELECT
type,
string_to_array('0-20, 20-40, 40-60, 60-80, 80-100, 100-120, 120-140, 140-160, 160-180, 180-200, 200-220, 220-240, 240-260, 260-280, 280-300, 300-320, 320-340, 340-360, 360-380, 380-400, 400-420, 420-440, 440-460, 460-480, 480-500, 500-520, 520-540, 540-560, 560-580, 580-600, over 600', ',') AS labels,
string_to_array(count(type) FILTER (WHERE min <= 20 ) || ',' ||
count(type) FILTER (WHERE min > 20 AND min <= 40 ) || ',' ||
count(type) FILTER (WHERE min > 40 AND min <= 60 ) || ',' ||
count(type) FILTER (WHERE min > 60 AND min <= 80 ) || ',' ||
count(type) FILTER (WHERE min > 80 AND min <= 100 ) || ',' ||
count(type) FILTER (WHERE min > 100 AND min <= 120 ) || ',' ||
count(type) FILTER (WHERE min > 120 AND min <= 140 ) || ',' ||
count(type) FILTER (WHERE min > 140 AND min <= 160 ) || ',' ||
count(type) FILTER (WHERE min > 160 AND min <= 180 ) || ',' ||
count(type) FILTER (WHERE min > 180 AND min <= 200 ) || ',' ||
count(type) FILTER (WHERE min > 200 AND min <= 220 ) || ',' ||
count(type) FILTER (WHERE min > 220 AND min <= 240 ) || ',' ||
count(type) FILTER (WHERE min > 240 AND min <= 260 ) || ',' ||
count(type) FILTER (WHERE min > 260 AND min <= 280 ) || ',' ||
count(type) FILTER (WHERE min > 280 AND min <= 300 ) || ',' ||
count(type) FILTER (WHERE min > 300 AND min <= 320 ) || ',' ||
count(type) FILTER (WHERE min > 320 AND min <= 340 ) || ',' ||
count(type) FILTER (WHERE min > 340 AND min <= 360 ) || ',' ||
count(type) FILTER (WHERE min > 360 AND min <= 380 ) || ',' ||
count(type) FILTER (WHERE min > 380 AND min <= 400 ) || ',' ||
count(type) FILTER (WHERE min > 300 AND min <= 420 ) || ',' ||
count(type) FILTER (WHERE min > 420 AND min <= 440 ) || ',' ||
count(type) FILTER (WHERE min > 440 AND min <= 460 ) || ',' ||
count(type) FILTER (WHERE min > 460 AND min <= 480 ) || ',' ||
count(type) FILTER (WHERE min > 480 AND min <= 500 ) || ',' ||
count(type) FILTER (WHERE min > 500 AND min <= 520 ) || ',' ||
count(type) FILTER (WHERE min > 520 AND min <= 540 ) || ',' ||
count(type) FILTER (WHERE min > 540 AND min <= 560 ) || ',' ||
count(type) FILTER (WHERE min > 560 AND min <= 580 ) || ',' ||
count(type) FILTER (WHERE min > 580 AND min <= 600 ) || ',' ||
count(type) FILTER (WHERE min > 600) , ',')::int[] AS data
FROM thanados.sexGraveDepth v GROUP BY type;
SELECT * FROM thanados.sexGraveDepthJSON;
"""
g.cursor.execute(sqlSexDepth, {'place_id': place_id})
result = g.cursor.fetchall()
_data = {}
_data['datasets'] = []
for row in result:
_data['labels'] = row.labels
_data['datasets'].append({'label': row.type, 'data': row.data})
return (_data)
SexDepthData = getSexDepth()
SexData = getSex()
GenderData = getgender()
ValueAgeData = getValueAges()
DashAgeData = getAges()
constrData = Data.get_type_data(
'grave', 'Grave Constr%',
tuple(ast.literal_eval('[' + str(place_id) + ']')))[0]
aziData = getAzimuth()
degData = getDegs()
depthData = getDims('Height')
widthData = getDims('Width')
lengthData = getDims('Length')
network = Data.getNetwork(place_id)
wordcloud = Data.get_wordcloud(place_id)
return render_template('entity/dashboard.html',
network=network,
entity=entity,
wordcloud=wordcloud,
mysitejson=data,
findBubble=findtree,
depthData=depthData,
widthData=widthData,
lengthData=lengthData,
degData=degData,
aziData=aziData,
constrData=constrData,
DashAgeData=DashAgeData,
ValueAgeData=ValueAgeData,
SexData=SexData,
GenderData=GenderData,
SexDepthData=SexDepthData)
return render_template('entity/view.html',
place_id=place_id,
object_id=object_id,
mysitejson=data,
system_type=system_type)
def vocabulary_view(object_id: int, format_=None):
object_id = object_id
loc_image = app.config["API_FILE_DISPLAY"]
use_api = app.config["USE_API"]
use_jpgs = app.config["USE_JPGS"]
if not use_api:
if use_jpgs:
loc_image = app.config["JPG_FOLDER_PATH"] + '/'
else:
loc_image = app.config["WEB_FOLDER_PATH"] + '/'
if not object_id:
return render_template('vocabulary/vocabulary.html')
# get dataset for type entity
sql_base = 'SELECT * FROM model.entity WHERE id = %(object_id)s;'
g.cursor.execute(sql_base, {'object_id': object_id})
output_base = g.cursor.fetchone()
sql_date = """
SELECT
date_part('year', begin_from) AS begin_from,
date_part('year', begin_to) AS begin_to,
date_part('year', end_from) AS end_from,
date_part('year', end_to) AS end_to
FROM model.entity WHERE id = %(object_id)s;
"""
g.cursor.execute(sql_date, {'object_id': object_id})
output_date = g.cursor.fetchone()
# check if exists
if not output_base:
abort(403)
# check if type class
CRMclass = output_base.cidoc_class_code
if CRMclass not in ['E55']:
abort(403)
extrefs = """
SELECT jsonb_agg(jsonb_strip_nulls(jsonb_build_object(
'identifier', t.identifier,
'domain', t.name,
'website', t.website,
'about', t.description,
'SKOS', t.skos,
'url', t.url,
'icon', r.icon_url
))) AS ext_types
FROM thanados.ext_types t JOIN thanados.refsys r ON t.id = r.entity_id
WHERE t.type_id = %(object_id)s;
"""
g.cursor.execute(extrefs, {'object_id': object_id})
extresult = g.cursor.fetchone()
# get top parent
sql_topparent = """
SELECT topparent FROM (
SELECT id::INTEGER, path, name_path, left(path, strpos(path, ' >') -1)::INTEGER AS
topparent FROM thanados.types_all WHERE path LIKE '%%>%%'
UNION ALL
SELECT id::INTEGER, path, name_path, PATH::INTEGER AS topparent FROM
thanados.types_all WHERE path NOT LIKE '%%>%%' ORDER BY name_path) tp
WHERE id = %(object_id)s"""
g.cursor.execute(sql_topparent, {'object_id': object_id})
topparent = g.cursor.fetchone().topparent
g.cursor.execute(
'select name, description, id from model.entity WHERE id = %(object_id)s',
{'object_id': topparent})
topparent = g.cursor.fetchone()
sql_topparent_info = """
select e.name, e.description, e.id, h.multiple, h.category
from model.entity e JOIN web.hierarchy h ON e.id = h.id WHERE e.id = %(topparent)s
"""
g.cursor.execute(sql_topparent_info, {'topparent': topparent.id})
result = g.cursor.fetchone()
topparent = {}
topparent['id'] = result.id
topparent['name'] = result.name
topparent['description'] = result.description
if result.multiple:
multi = 'multiple selection'
else:
multi = 'single selection'
type = ''
if result.category == 'standard':
type = 'Classification'
if result.category == 'value':
type = 'Value type'
elif result.category == 'custom':
type = 'Type'
topparent['selection'] = multi
topparent['type'] = type
topparent['forms'] = []
sql_forms = """
select openatlas_class_name as name FROM
web.hierarchy_openatlas_class WHERE hierarchy_id = %(topparent)s
"""
g.cursor.execute(sql_forms, {'topparent': topparent['id']})
forms_used = g.cursor.fetchall()
for row in forms_used:
topparent['forms'].append(row.name)
# get parent and path
sql_path_parent = 'SELECT name_path, parent_id FROM thanados.types_all WHERE id = %(object_id)s;'
g.cursor.execute(sql_path_parent, {'object_id': object_id})
output_path_parent = g.cursor.fetchone()
# get name of parent
sql_parentname = 'SELECT name FROM thanados.types_all WHERE id = %(object_id)s;'
g.cursor.execute(sql_parentname,
{'object_id': output_path_parent.parent_id})
output_parentname = g.cursor.fetchone()
#define time
time = {}
if output_base.begin_from:
time['earliest_begin'] = output_date.begin_from
if output_base.begin_to:
time['latest_begin'] = output_date.begin_to
if output_base.end_from:
time['earliest_end'] = output_date.end_from
if output_base.end_to:
time['latest_end'] = output_date.end_to
# define json
data = {}
data['id'] = output_base.id
data['name'] = output_base.name
data['path'] = output_path_parent.name_path
if output_base.description:
data['description'] = output_base.description
if output_path_parent.parent_id:
data['parent'] = output_path_parent.parent_id
data['parent_name'] = output_parentname.name
if len(time) > 0:
data['time'] = time
credits = None
license = None
if extresult.ext_types:
data['gazetteers'] = []
gazetteers = extresult.ext_types
for row in gazetteers:
if 'about' in row:
about = row['about']
else:
about = row['domain']
if row['website']:
about = row['domain'] + ': ' + row['website']
if 'SKOS' in row:
SKOS = row['SKOS']
else:
SKOS = None
extid = {
'SKOS': SKOS,
'url': row['url'],
'about': about,
'domain': row['domain'],
'identifier': row['identifier']
}
if row['domain'] == 'Wikidata' and format_ != 'json':
extid['description'] = Data.getWikidata(
row['identifier'])['description']
extid['label'] = Data.getWikidata(row['identifier'])['label']
extid['image'] = Data.getWikidataimage(row['identifier'])
if extid['image']:
try:
credits = extid['image']['metadata']['Artist']['value']
try:
credits = credits + '<br>Credit: ' + extid[
'image']['metadata']['Credit']['value']
except KeyError:
credits = extid['image']['metadata']['Artist'][
'value']
except KeyError:
try:
credits = extid['image']['metadata']['Credit'][
'value']
except KeyError:
credits = 'Author unknown'
try:
license = '<a href="' + extid['image']['metadata'][
'LicenseUrl']['value'] + '" target="blank_">'
try:
license = license + extid['image']['metadata'][
'LicenseShortName']['value'] + '</a>'
except KeyError:
license = ''
except KeyError:
try:
license = extid['image']['metadata'][
'LicenseShortName']['value']
except KeyError:
license = '<a href="' + extid['image'][
'origin'] + '">' + extid['image'][
'origin'] + '</a>'
if row['icon']:
extid['favicon'] = row['icon']
data['gazetteers'].append(extid)
if row['domain'] == 'Getty AAT' and format_ != 'json':
gettydata = Data.getGettyData(row['identifier'])
extid['description'] = gettydata['description']
extid['label'] = gettydata['label']
extid['qualifier'] = gettydata['qualifier']
# get subtypes
sql_children = 'SELECT id, name FROM thanados.types_all WHERE parent_id = %(object_id)s;'
g.cursor.execute(sql_children, {'object_id': object_id})
output_children = g.cursor.fetchall()
if output_children:
data['children'] = []
for row in output_children:
data['children'].append({'id': row.id, 'name': row.name})
# get files
sql_files = """SELECT
m.id
FROM model.entity m JOIN model.link l ON m.id = l.domain_id
WHERE l.range_id = %(object_id)s AND l.property_code = 'P67' AND m.openatlas_class_name =
'file'
"""
g.cursor.execute(sql_files, {'object_id': object_id})
output_files = g.cursor.fetchall()
# get file license
sql_filelicense = """
SELECT
name AS license, name_path::TEXT, t.id::INTEGER AS licId, domain_id::INTEGER
FROM thanados.types_all t JOIN model.link l ON t.id = l.range_id WHERE l.domain_id =
%(file_id)s AND l.property_code = 'P2' AND t.name_path LIKE 'License >%%'
"""
# define files
if output_files:
data['files'] = []
# get file references
sql_file_refs = """
SELECT
r.description AS title,
l.description AS reference
FROM model.entity r JOIN model.link l ON r.id = l.domain_id
WHERE l.range_id = %(file_id)s AND l.property_code = 'P67'
"""
for row in output_files:
file_name = (Data.get_file_path(row.id))
print(file_name)
file_id = (row.id)
file = {'id': file_id, 'file_name': (loc_image + file_name)}
g.cursor.execute(sql_file_refs, {'file_id': file_id})
output_file_refs = g.cursor.fetchone()
g.cursor.execute(sql_filelicense, {'file_id': file_id})
output_filelicense = g.cursor.fetchone()
if output_file_refs:
if output_file_refs.title:
file['source'] = output_file_refs.title
if output_file_refs.reference:
file['reference'] = output_file_refs.reference
# add licence information
if output_filelicense:
file['license'] = output_filelicense.license
file['licenseId'] = output_filelicense.licid
data['files'].append(file)
# get all subtypes recursively
sql_subtypesrec = """
SELECT id from thanados.types_all WHERE path LIKE %(type_name)s OR path LIKE
%(type_name2)s OR id = %(type_id)s
"""
entlist = []
g.cursor.execute(
sql_subtypesrec, {
'type_id': object_id,
'type_name': '%> ' + str(output_base.id) + ' >%',
'type_name2': str(output_base.id) + ' >%'
})
output_subtypesrec = g.cursor.fetchall()
if output_subtypesrec:
data['types_recursive'] = []
for row in output_subtypesrec:
data['types_recursive'].append(row.id)
entlist.append(row.id)
entlist = tuple(entlist)
# get all entitites with this type
sql_entities = """
SELECT child_id, child_name, maintype, type, type_id, min, lon, lat, context,
filename, openatlas_class_name FROM
thanados.searchdata s
WHERE type_id IN %(type_id)s AND s.site_id IN %(site_ids)s
"""
g.cursor.execute(sql_entities, {
'type_id': tuple([object_id]),
'site_ids': tuple(g.site_list)
})
output_direct_ents = g.cursor.fetchall()
if output_direct_ents:
data['entities'] = []
for row in output_direct_ents:
data['entities'].append({
'id':
row.child_id,
'name':
row.child_name,
'main_type':
row.maintype,
'type':
row.type,
'type_id':
row.type_id,
'value':
row.min,
'lon':
row.lon,
'lat':
row.lat,
'context':
row.context,
'file':
row.filename,
'openatlas_class_name':
row.openatlas_class_name
})
g.cursor.execute(sql_entities, {
'type_id': entlist,
'site_ids': tuple(g.site_list)
})
output_direct_ents = g.cursor.fetchall()
if output_direct_ents:
data['entities_recursive'] = []
for row in output_direct_ents:
data['entities_recursive'].append({
'id':
row.child_id,
'name':
row.child_name,
'main_type':
row.maintype,
'type':
row.type,
'type_id':
row.type_id,
'value':
row.min,
'lon':
row.lon,
'lat':
row.lat,
'context':
row.context,
'file':
row.filename,
'openatlas_class_name':
row.openatlas_class_name
})
# get type tree
def getchildren(id, node):
sql_getChildren = """
SELECT name, id FROM thanados.types_all WHERE parent_id = %(id)s ORDER BY name
"""
g.cursor.execute(sql_getChildren, {'id': id})
results = g.cursor.fetchall()
if results:
node['nodes'] = []
for row in results:
currentnode = {
'text': row.name,
'class': 'treenode',
'href': '/vocabulary/%r' % row.id,
'openNodeLinkOnNewTab': False
}
node['nodes'].append(currentnode)
getchildren(row.id, currentnode)
tree = [{'text': data['name'], 'class': 'toptreenode'}]
getchildren(object_id, tree[0])
hierarchy = {}
currentcolor = '#97C2FC'
if object_id == topparent['id']:
currentcolor = '#ff8c8c'
alltreeNodes = [{
'id': topparent['id'],
'label': topparent['name'],
'color': currentcolor
}]
alltreeEdges = []
def getTree(id):
sql_getChildren = """
SELECT DISTINCT name, id FROM thanados.types_all WHERE parent_id = %(id)s ORDER BY name
"""
g.cursor.execute(sql_getChildren, {'id': id})
results = g.cursor.fetchall()
if results:
for row in results:
currentcolor = '#97C2FC'
if row.id == object_id:
currentcolor = '#ff8c8c'
currentnode = {
'id': row.id,
'label': row.name,
'color': currentcolor
}
currentedge = {'from': id, 'to': row.id, 'color': '#757575'}
alltreeNodes.append(currentnode)
alltreeEdges.append(currentedge)
getTree(row.id)
getTree(topparent['id'])
hierarchy['nodes'] = alltreeNodes
hierarchy['edges'] = alltreeEdges
data['topparent'] = topparent
data['tree'] = tree
data['hierarchy'] = hierarchy
if format_ == 'json':
return json.dumps(data)
if object_id:
return render_template('vocabulary/view.html',
object_id=object_id,
data=data,
children=len(output_children),
credit=credits,
license=license,
children_recursive=len(entlist),
webfolder=app.config["WEB_FOLDER_PATH"])
def sites():
site_list = Data.get_list()
case_studies = app.config["DOMAIN_TYPES"]
return render_template('/sites/sites.html', sitelist=site_list[0].sitelist)
def vocabulary_view(object_id: int, format_=None):
object_id = object_id
if not object_id:
return render_template('vocabulary/vocabulary.html')
# get dataset for type entity
sql_base = 'SELECT * FROM model.entity WHERE id = %(object_id)s;'
g.cursor.execute(sql_base, {'object_id': object_id})
output_base = g.cursor.fetchone()
# check if exists
if not output_base:
abort(403)
# check if type class
CRMclass = output_base.class_code
if CRMclass not in ['E55']:
abort(403)
# get top parent
sql_topparent = """
SELECT topparent FROM (
SELECT id::INTEGER, path, name_path, left(path, strpos(path, ' >') -1)::INTEGER AS
topparent FROM thanados.types_all WHERE path LIKE '%%>%%'
UNION ALL
SELECT id::INTEGER, path, name_path, PATH::INTEGER AS topparent FROM
thanados.types_all WHERE path NOT LIKE '%%>%%' ORDER BY name_path) tp
WHERE id = %(object_id)s"""
g.cursor.execute(sql_topparent, {'object_id': object_id})
topparent = g.cursor.fetchone().topparent
g.cursor.execute('select name, description, id from model.entity WHERE id = %(object_id)s',
{'object_id': topparent})
topparent = g.cursor.fetchone()
sql_topparent_info = """
select e.name, e.description, e.id, h.multiple, h.standard AS system_type, h.value_type
from model.entity e JOIN web.hierarchy h ON e.id = h.id WHERE e.id = %(topparent)s
"""
g.cursor.execute(sql_topparent_info, {'topparent': topparent.id})
result = g.cursor.fetchone()
topparent = {}
topparent['id'] = result.id
topparent['name'] = result.name
topparent['description'] = result.description
if result.multiple:
multi = 'multiple selection'
else:
multi = 'single selection'
type = ''
if result.system_type:
type = 'System type'
if result.value_type:
type = 'Value type'
elif not result.system_type:
type = 'Custom type'
topparent['selection'] = multi
topparent['type'] = type
topparent['forms'] = []
sql_forms = """
select f.name FROM
web.form f JOIN web.hierarchy_form h ON f.id = h.form_id WHERE h.hierarchy_id = %(topparent)s
"""
g.cursor.execute(sql_forms, {'topparent': topparent['id']})
forms_used = g.cursor.fetchall()
for row in forms_used:
topparent['forms'].append(row.name)
# get parent and path
sql_path_parent = 'SELECT name_path, parent_id FROM thanados.types_all WHERE id = %(object_id)s;'
g.cursor.execute(sql_path_parent, {'object_id': object_id})
output_path_parent = g.cursor.fetchone()
# get name of parent
sql_parentname = 'SELECT name FROM thanados.types_all WHERE id = %(object_id)s;'
g.cursor.execute(sql_parentname, {'object_id': output_path_parent.parent_id})
output_parentname = g.cursor.fetchone()
# define json
data = {}
data['id'] = output_base.id
data['name'] = output_base.name
data['path'] = output_path_parent.name_path
if output_base.description:
data['description'] = output_base.description
if output_base.begin_from:
data['earliest_begin'] = output_base.begin_from
if output_base.begin_to:
data['latest_begin'] = output_base.begin_to
if output_base.begin_comment:
data['begin_comment'] = output_base.begin_comment
if output_base.end_from:
data['earliest_end'] = output_base.end_from
if output_base.end_to:
data['latest_end'] = output_base.end_to
if output_base.end_comment:
data['end_comment'] = output_base.end_comment
if output_path_parent.parent_id:
data['parent'] = output_path_parent.parent_id
data['parent_name'] = output_parentname.name
# get subtypes
sql_children = 'SELECT id, name FROM thanados.types_all WHERE parent_id = %(object_id)s;'
g.cursor.execute(sql_children, {'object_id': object_id})
output_children = g.cursor.fetchall()
if output_children:
data['children'] = []
for row in output_children:
data['children'].append({'id': row.id, 'name': row.name})
# get files
sql_files = """SELECT
m.id
FROM model.entity m JOIN model.link l ON m.id = l.domain_id
WHERE l.range_id = %(object_id)s AND l.property_code = 'P67' AND m.system_type =
'file'
"""
g.cursor.execute(sql_files, {'object_id': object_id})
output_files = g.cursor.fetchall()
# get file license
sql_filelicense = """
SELECT
name AS license, name_path::TEXT, t.id::INTEGER AS licId, domain_id::INTEGER
FROM thanados.types_all t JOIN model.link l ON t.id = l.range_id WHERE l.domain_id =
%(file_id)s AND l.property_code = 'P2' AND t.name_path LIKE 'License >%%'
"""
# define files
if output_files:
data['files'] = []
# get file references
sql_file_refs = """
SELECT
r.description AS title,
l.description AS reference
FROM model.entity r JOIN model.link l ON r.id = l.domain_id
WHERE l.range_id = %(file_id)s AND l.property_code = 'P67'
"""
for row in output_files:
file_name = (Data.get_file_path(row.id))
file_id = (row.id)
file = {'id': file_id, 'file_name': file_name}
g.cursor.execute(sql_file_refs, {'file_id': file_id})
output_file_refs = g.cursor.fetchone()
g.cursor.execute(sql_filelicense, {'file_id': file_id})
output_filelicense = g.cursor.fetchone()
if output_file_refs:
if output_file_refs.title:
file['source'] = output_file_refs.title
if output_file_refs.reference:
file['reference'] = output_file_refs.reference
# add licence information
if output_filelicense:
file['license'] = output_filelicense.license
file['licenseId'] = output_filelicense.licid
data['files'].append(file)
# get all subtypes recursively
sql_subtypesrec = """
SELECT id from thanados.types_all WHERE path LIKE %(type_name)s OR path LIKE
%(type_name2)s OR id = %(type_id)s
"""
entlist = []
g.cursor.execute(sql_subtypesrec,
{'type_id': object_id, 'type_name': '%> ' + str(output_base.id) + ' >%',
'type_name2': str(output_base.id) + ' >%'})
output_subtypesrec = g.cursor.fetchall()
if output_subtypesrec:
data['types_recursive'] = []
for row in output_subtypesrec:
data['types_recursive'].append(row.id)
entlist.append(row.id)
entlist = tuple(entlist)
# get all entitites with this type
sql_entities = """
SELECT child_id, child_name, maintype, type, type_id, min, lon, lat, context,
filename, system_type FROM
thanados.searchdata s
WHERE type_id IN %(type_id)s AND s.site_id IN %(site_ids)s
"""
g.cursor.execute(sql_entities, {'type_id': tuple([object_id]), 'site_ids': tuple(g.site_list)})
output_direct_ents = g.cursor.fetchall()
if output_direct_ents:
data['entities'] = []
for row in output_direct_ents:
data['entities'].append({'id': row.child_id, 'name': row.child_name, 'main_type':
row.maintype, 'type': row.type, 'type_id': row.type_id, 'value': row.min,
'lon': row.lon,
'lat': row.lat, 'context': row.context, 'file': row.filename,
'system_type':
row.system_type})
g.cursor.execute(sql_entities, {'type_id': entlist, 'site_ids': tuple(g.site_list)})
output_direct_ents = g.cursor.fetchall()
if output_direct_ents:
data['entities_recursive'] = []
for row in output_direct_ents:
data['entities_recursive'].append({'id': row.child_id, 'name': row.child_name,
'main_type':
row.maintype, 'type': row.type,
'type_id': row.type_id, 'value': row.min,
'lon': row.lon,
'lat': row.lat, 'context': row.context,
'file': row.filename,
'system_type':
row.system_type})
# get type tree
def getchildren(id, node):
sql_getChildren = """
SELECT name, id FROM thanados.types_all WHERE parent_id = %(id)s ORDER BY name
"""
g.cursor.execute(sql_getChildren, {'id': id})
results = g.cursor.fetchall()
if results:
node['nodes'] = []
for row in results:
currentnode = {'text': row.name,
'class': 'treenode',
'href': '/vocabulary/%r' % row.id,
'openNodeLinkOnNewTab': False}
node['nodes'].append(currentnode)
getchildren(row.id, currentnode)
tree = [{
'text': data['name'],
'class': 'toptreenode'
}]
getchildren(object_id, tree[0])
hierarchy = {}
currentcolor = '#97C2FC'
if object_id == topparent['id']:
currentcolor = '#ff8c8c'
alltreeNodes = [{'id': topparent['id'], 'label': topparent['name'], 'color' : currentcolor}]
alltreeEdges = []
def getTree(id):
sql_getChildren = """
SELECT name, id FROM thanados.types_all WHERE parent_id = %(id)s ORDER BY name
"""
g.cursor.execute(sql_getChildren, {'id': id})
results = g.cursor.fetchall()
if results:
for row in results:
currentcolor = '#97C2FC';
if row.id == object_id:
currentcolor= '#ff8c8c'
currentnode = {'id': row.id, 'label': row.name, 'color' : currentcolor}
currentedge = {'from': id, 'to': row.id, 'color': '#757575'}
alltreeNodes.append(currentnode)
alltreeEdges.append(currentedge)
getTree(row.id)
getTree(topparent['id'])
hierarchy['nodes'] = alltreeNodes
hierarchy['edges'] = alltreeEdges
data['topparent'] = topparent
data['tree'] = tree
data['hierarchy'] = hierarchy
if format_ == 'json':
return json.dumps(data)
if object_id:
return render_template('vocabulary/view.html', object_id=object_id, data=data,
children=len(output_children),
children_recursive=len(entlist))