Admin Report that lists all places having no zoom or place level

From TNG_Wiki
Jump to navigation Jump to search

Description: Admin Report that lists all places having no zoom or place level, when you're using a TNG version lower than 8 you will have to replace admin_editplace.php with admin/editplace.php


Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate


SELECT id, pl.gedcom,
concat('<a href="admin_editplace.php?ID=',pl.id,'&tree=', pl.gedcom,'", target=_blank>', pl.place, 
'</a>') as Places,  notes, concat('<a href="placesearch.php?psearch=',replace(pl.place,' ','+'), '", 
target=_blank>', evcount, '</a>') as eventcount, First, Last
FROM tng_places as pl
join
( select gedcom, place, count(*) as evcount, if(max(dt) > 0, min(if(dt>0,year(dt),9999)), 'No') 
as First, if(max(dt) > 0, year(max(dt)), 'Date') as Last from
( SELECT gedcom, birthplace AS place, birthdatetr as dt
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION all SELECT gedcom, altbirthplace, altbirthdatetr
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION all SELECT gedcom, marrplace, marrdatetr
FROM `tng_families`
WHERE gedcom = 'savenije'
UNION all SELECT gedcom, deathplace, deathdatetr
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION all SELECT gedcom, burialplace, burialdatetr
FROM `tng_people`
WHERE gedcom = 'savenije'
UNION all SELECT gedcom, eventplace, eventdatetr
FROM tng_events
WHERE gedcom = 'savenije'
) as p group by gedcom, place
) as plc using ( gedcom, place )
where (isnull(zoom) or isnull(placelevel) or zoom=0 or placelevel=0)
order by pl.place, evcount desc, gedcom