source: gs3-extensions/atlas-src/trunk/src/org/greenstone/atlas/server/PlaceInformation.java@ 23934

package org.greenstone.atlas.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import org.greenstone.atlas.client.Place;
import java.util.HashMap;

public class PlaceInformation
{
    static Statement _database = null;
    static HashMap<String, ArrayList<Place>> _knownPlaces = new HashMap<String, ArrayList<Place>>();

    /**
     * Default constructor Creates a connection to the database
     */
    public static void init()
    {
        try
        {
            System.out.print("Loading postgresql driver... ");
            Class.forName("org.postgresql.Driver");
            Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/GS3MapDB", "postgres", "admin");//DriverManager.getConnection("jdbc:postgresql://localhost/test", "sjm84", "password");
            System.out.println("Done!");
            _database = c.createStatement();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }

    /**
     * Searches the database for a specific place by using the place name and
     * the parent's place names
     * 
     * @param id
     *            is the id of the place to search for in the database
     * @return the list of places found (in case there is more than one match)
     */
    public static ArrayList<Place> getSpecificPlace(Long id)
    {
        try
        {
            ResultSet results = _database.executeQuery("SELECT * FROM places WHERE id = " + id);

            // Go through the results and create a list of places
            ArrayList<Place> placeList = new ArrayList<Place>();

            while (results.next())
            {
                Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));

                if (p != null)
                {
                    placeList.add(p);
                }
            }

            if (placeList.size() == 0)
            {
                placeList = null;
            }

            results.close();

            // Return the list of places
            return placeList;
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            return null;
        }
    }

    /**
     * Searches the database for a specific place by using the place name and
     * the parent's place names
     * 
     * @param placeName
     *            is the name of the place to find
     * @param parentPlaceName
     *            is the name of the place's parents (seperated by ", ")
     * @return the list of places found (in case there is more than one match)
     */
    public static ArrayList<Place> getSpecificPlace(String placeName, String parentPlaceName)
    {
        // Make sure the place name to find is not null
        if (placeName == null)
        {
            return null;
        }

        ResultSet results = null;

        String firstParent = null;
        String secondParent = null;
        String thirdParent = null;

        // Separate the parents
        if (parentPlaceName != null)
        {
            String[] parents = parentPlaceName.split(", ");

            if (parents.length == 1)
            {
                firstParent = parents[0];
            }
            else if (parents.length == 2)
            {
                firstParent = parents[0];
                secondParent = parents[1];
            }
            else
            {
                firstParent = parents[0];
                secondParent = parents[1];
                thirdParent = parents[2];
            }
        }

        try
        {
            // If the place does not have a parent
            if (parentPlaceName == null)
            {
                results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region is null " + "and country is null;");
            }
            // If the place has one parent
            else if (secondParent == null)
            {
                results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region is null " + "and country = '" + firstParent.replaceAll("'", "\\\\'") + "';");
            }
            // If the place has two parents
            else if (thirdParent == null)
            {
                results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region = '" + firstParent.replaceAll("'", "\\\\'") + "' " + "and country = '" + secondParent.replaceAll("'", "\\\\'") + "';");
            }
            // If the place has three parents
            else
            {
                results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion = '" + firstParent.replaceAll("'", "\\\\'") + "' " + "and region = '" + secondParent.replaceAll("'", "\\\\'") + "' " + "and country = '" + thirdParent.replaceAll("'", "\\\\'") + "';");
            }

            // Go through the results and create a list of places
            ArrayList<Place> placeList = new ArrayList<Place>();

            while (results.next())
            {
                Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));

                if (p != null)
                {
                    placeList.add(p);
                }
            }

            if (placeList.size() == 0)
            {
                placeList = null;
            }

            results.close();

            // Return the list of places
            return placeList;
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            return null;
        }
    }

    /**
     * Gets all of the place with the given name
     * 
     * @param placeName
     * @return
     */
    public static ArrayList<Place> getPlaces(String placeName)
    {
        ArrayList<Place> placeList = new ArrayList<Place>();

        ArrayList<Place> places = null;
        if((places = _knownPlaces.get(placeName)) != null)
        {
            return places;
        }
        
        try
        {
            if (_database == null)
            {
                System.out.println("***?***");
            }
            // Do a database query with the given place name
            ResultSet results = _database.executeQuery("SELECT * FROM places WHERE placename = \'" + placeName.replaceAll("'", "\\\\'") + "\'");

            // Go through the results and add them to the list of found places
            while (results.next())
            {
                Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));

                if (p != null)
                {
                    placeList.add(p);
                }
            }
            results.close();

            // Find if any places have this place name as a alternative name
            ResultSet altNames = _database.executeQuery("SELECT * FROM alternatenames WHERE alternatename = \'" + placeName.replaceAll("'", "\\\\'") + "\'");

            ArrayList<Long> alternateIndexes = new ArrayList<Long>();

            while (altNames.next())
            {
                alternateIndexes.add(altNames.getLong("placeindex"));
            }
            altNames.close();

            for (Long index : alternateIndexes)
            {
                results = _database.executeQuery("SELECT * FROM places WHERE id = " + index);

                while (results.next())
                {
                    Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));

                    if (p != null)
                    {
                        placeList.add(p);
                    }
                }
            }
            results.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }

        _knownPlaces.put(placeName, placeList);
        
        return placeList;
    }
}