View source for Maintenance - Places - Zoom & Place Levels

{|align=right
|__TOC__
|} 
== 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:

: [[File: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). 

: [[File:rpt_savereport.jpg]]
*BE SURE you replace ALL instances of 'mytree' with your [[20090217230735|tree ID]] (inside single quotes).
*Save the Report.


== SQL Code - Single Tree ==

<span style="color: red">'''For TNG V7 and below''', you need to change the admin_editplace.php in the following SQL to</span> '''admin/editplace.php'''

<syntaxhighlight lang="SQL" enclose="div">
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
</syntaxhighlight>


== SQL Code - Multiple Trees ==

<span  style="color: red">'''For TNG V9 and above''', if you use [[Pooling Places]]</span> you need to use the following SQL provide by [[User:TheKiwi|Roger Moffat]]

<syntaxhighlight lang="SQL" enclose="div">
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
</syntaxhighlight>

== Viewing and Using the Report ==
After saving the report changes, click the 'Test' button. The resulting report should look like this:

[[File: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 ==
* Original SQL - Luke Weerts 
* See TNG Forum: https://tng.community/index.php?/forums/index.php?showtopic=3273 for a variety of 'tweaks' to the original SQL and that provided here.
* Roger Moffat provided the query for multiple trees [[Pooling Places]]


[[Category:Reports]]
[[Category:Administrator]]
[[Category:GoogleMaps]]
[[Category:Maintenance]]