-- PlacesWithoutGeocodesForGroup.sql
-- 2013-10-21 Tom Holden ve3meo
/*
Lists those places and place details (sites) and their geographical coordinates (geocodes)
for events of persons who are members of named groups having a groupname containing the string
"focus" and for which one of the sets of coordinates is 0 (i.e., not geocoded). 
*/
SELECT P.NAME AS Place
	,P.Latitude * 0.0000001 AS PlaceLat
	,P.Longitude * 0.0000001 AS PlaceLong
	,PD.NAME AS Detail
	,PD.[Latitude] * 0.0000001 AS DetLat
	,PD.[Longitude] * 0.0000001 AS DetLong
FROM PlaceTable P
LEFT JOIN PlaceTable PD ON P.PlaceID = PD.MasterID
WHERE ifnull(PD.PlaceType, 2) = 2
	AND P.PlaceType = 0
	AND (
		(
			P.Latitude = 0
			AND P.Longitude = 0
			)
		OR (
			PD.Latitude = 0
			AND PD.Longitude = 0
			)
		)
	AND P.PlaceID -- restrict to places used by events for persons in named group
	IN (
		SELECT DISTINCT PlaceID
		FROM (
			SELECT OwnerID AS PersonID
				,EventID
				,PlaceID
				,SiteID
			FROM EventTable E
			WHERE OwnerType = 0
			
			UNION
			
			SELECT FatherID AS PersonID
				,EventID
				,PlaceID
				,SiteID
			FROM EventTable E
			INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
			WHERE OwnerType = 1
			
			UNION
			
			SELECT MotherID AS PersonID
				,EventID
				,PlaceID
				,SiteID
			FROM EventTable E
			INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
			WHERE OwnerType = 1
			)
		WHERE PersonID IN (
				SELECT DISTINCT P.PersonID
				FROM PersonTable P
					,GroupTable
				WHERE P.PersonID BETWEEN StartID
						AND EndID
					AND GroupID IN (
						SELECT DISTINCT LabelValue
						FROM LabelTable
						WHERE LabelType = 0
							AND LabelName LIKE '%focus%'
						) -- persons in groups with name containing "focus"
				)
		)
	AND (
		PD.PlaceID IsNull -- Places with no PlaceDetail
		OR PD.PlaceID -- OR restrict to sites used by events by persons in named group
		IN (
			SELECT DISTINCT SiteID
			FROM (
				SELECT OwnerID AS PersonID
					,EventID
					,PlaceID
					,SiteID
				FROM EventTable E
				WHERE OwnerType = 0
				
				UNION
				
				SELECT FatherID AS PersonID
					,EventID
					,PlaceID
					,SiteID
				FROM EventTable E
				INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
				WHERE OwnerType = 1
				
				UNION
				
				SELECT MotherID AS PersonID
					,EventID
					,PlaceID
					,SiteID
				FROM EventTable E
				INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
				WHERE OwnerType = 1
				)
			WHERE PersonID IN (
					SELECT DISTINCT P.PersonID
					FROM PersonTable P
						,GroupTable
					WHERE P.PersonID BETWEEN StartID
							AND EndID
						AND GroupID IN (
							SELECT DISTINCT LabelValue
							FROM LabelTable
							WHERE LabelType = 0
								AND LabelName LIKE '%focus%'
							) -- persons in groups with name containing "focus"
					)
			)
		)
ORDER BY Place
	,Detail;