/*
 * ZipCode.java
 */

package uw.ischool.info340.labs;

import javax.naming.*;
import javax.sql.*;
import java.sql.*;

public class ZipCode extends DbBaseConnect {
    
    public ZipCode() {
    /*
     * Call init() in the superclass to initialize the connection
     */
        init();
    }
    
    /*
     * Adds a new location of the zip table
     */
    public boolean addNewLocation(
            String zip, 
            String stateCode, 
            String cityName, 
            double longitude, 
            double latitude) 
    {
        // To DO
        return false;
    }

    /* 
     * Queries the zip table for all rows that match the cityName
     */
    public int queryZipCodeInfo(String cityName) {
        // TO DO 
        return 0; 
    }

    /* 
     * Gets the value of a particular row # and field name 
     */
    public String getDataField(int recordID, String fieldName) {
        // TO DO
        return ""; 
    }
        
   /*
    * Returns an XML document containing all state codes, lognitudes and
    * latitudes for a particulare city
    */
    public String getZipCodesForCityAsXML(String cityName) {
        String theQuery = "";
        try {
            /* Build up query */
            String p1 = "select zcode, scode, city, longitude, latitude from zip ";
            String p2 = "where city=upper('" + cityName + "')";
            theQuery = p1 + p2;
            
            /* Execute query -- the results are in r */
            Statement s = conn.createStatement();
            ResultSet r = s.executeQuery(theQuery);
            
            /*
             * We will keep track of the number of items processed
             */
            int count=0;
            
            /*
             * We will put the XML source into this variable
             */
            String t="";
            
            /*
             * A 'cursor' is used to move through results -- initially,
             * the cursor is set BEFORE the first row in the result set.
             * While next() returns TRUE we have a row to process 
             */
            while(r.next()) {
                /*
                 * Query generated a result -- now extract data by Name & Index
                 */
                String zcode    = r.getString("zcode");
                String scode 	= r.getString("scode");     // by column name
                String city 	= r.getString("city");     
                double longn	= r.getDouble(4);           // by index -- 4th column 
                double latit	= r.getDouble("latitude");  
                
                /* 
                 * Create XML fragment and concatenate it to XML string 
                 */
                t += "  <item>\n";
                t += "    <zip>"        + zcode + "</zip>\n";
                t += "    <scode>"      + scode + "</scode>\n";
                t += "    <city>" 	+ city + "</city>\n";
                t += "    <longitude>" 	+ longn + "</longitude>\n";
                t += "    <latitude>" 	+ latit + "</latitude>\n";
                t += "  </item>\n";
                
                count++;
            }
            
            /*
             * Check for results/no-results and build xml string 
             */
            if (count > 0) {
                t = "<results num='" + count + "' city='" + cityName + "'>\n" + t;
                t += "</results>\n";
            } else {
                t = "<noresults city='" + cityName +"'>";
                t += "  <query>"+ theQuery + "</query>";
                t += "  <reason>Unknown city</reason>";
                t += "</noresults>";
            }
            /*
             * Return the XML document of results 
             */
            return t;
            
        } catch(Exception e) {
            debugString = e.toString() + "Query: ||" + theQuery + "||";
            e.printStackTrace();
        }
        
        /*
         * Return an ERROR tag -- if there's an exception send this back
        */
        return "<ERROR function='getZipCodesForCityAsXML'>"
                + debugString
                + "</ERROR>";
    }
}