Maintenance - Places - Zoom & Place Levels

From TNG_Wiki
Jump to: navigation, search

Purpose

This article shows you how you can create an Admin Report that lists all places having no zoom or place level (City/Town, County/Shire etc.) set. The resulting report lists all relevant places as a link that takes you directly to the 'Places / Edit Existing Place' page where you can insert the levels or set them using the 'show/hide clickable map' button (provided you have a Google Map key).

Creating the Report

  • In Admin/Reports select the 'Add New' tab.
  • Enter a Report Name and Description:
Rpt addnew.jpg
  • Select the 'No' radio button against 'Active' (this ensures the report is viewable only by the system administrator).
  • Copy the SQL code listed below and paste in the last text box (above the 'Save Report' button).
Rpt savereport.jpg
  • BE SURE you replace ALL instances of 'mytree' with your tree ID (inside single quotes).
  • Save the Report.


SQL Code - Single Tree

For TNG V7 and below, you need to change the admin_editplace.php in the following SQL to admin/editplace.php

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 = 'mytree'
UNION all SELECT gedcom, altbirthplace, altbirthdatetr
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, marrplace, marrdatetr
FROM `tng_families`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, deathplace, deathdatetr
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, burialplace, burialdatetr
FROM `tng_people`
WHERE gedcom = 'mytree'
UNION all SELECT gedcom, eventplace, eventdatetr
FROM tng_events
WHERE gedcom = 'mytree'
) 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


SQL Code - Multiple Trees

For TNG V9 and above, if you use Pooling Places you need to use the following SQL provide by Roger Moffat

SELECT id, 
concat('<a href="admin_editplace.php?ID=',pl.id,'",target=_blank>', pl.place, '</a>') as Places, notes, concat('<a href="placesearch.php?psearch=',replace(pl.place,' ','+'), '">', evcount, '</a>') as eventcount, First, Last

FROM tng_places as pl
join
( select 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 birthplace AS place, birthdatetr as dt
FROM `tng_people`
UNION all SELECT altbirthplace, altbirthdatetr
FROM `tng_people`
UNION all SELECT marrplace, marrdatetr
FROM `tng_families`
UNION all SELECT deathplace, deathdatetr
FROM `tng_people`
UNION all SELECT burialplace, burialdatetr
FROM `tng_people`
UNION all SELECT eventplace, eventdatetr
FROM tng_events

) as p group by place 

) as plc using ( place )
where (isnull(longitude) or isnull(latitude) or longitude='' or latitude = '') 
order by evcount desc, pl. place

Viewing and Using the Report

After saving the report changes, click the 'Test' button. The resulting report should look like this:

Rpt viewreport.jpg

If you click on a place name in the list it will take you to the 'Places / Edit Existing Place' page.

Acknowledgments