package com.workout;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Set;
import java.util.TreeSet;


/**
 * A simple class to get the data from the database and convert it to HTML.
 * Dont get too attached to this concept.  Its not the best thing to do.
 * Ultimately, I would just get the data as a bean from a business delegate
 * however its all about incremental steps for now. 
 * @author Robert Stave
 *
 */
public class MemberListHelper {


    static String PERSON_USERNAME = "username";
    static String PERSON_FNAME = "fname";
    static String PERSON_LNAME = "lname";
    static String PERSON_ADDRESS = "address";
    static String PERSON_GENDER = "gender";
    static String PERSON_BIRTH = "birth_date";
    static String PERSON_STATE = "state";
    static String PERSON_CITY = "city";


    public String getMemberListTable(){

        Connection connection = null;
        String theTable = "";

        try {
            Class.forName(WorkoutDatabase.DRIVER).newInstance();
            connection = DriverManager
            .getConnection(WorkoutDatabase.URL+WorkoutDatabase.DBNAME,
                    WorkoutDatabase.USERNAME,
                    WorkoutDatabase.PASSWORD);

            String theQuery = "Select * from person;";

            Statement select = connection.createStatement();
            ResultSet result = select.executeQuery(theQuery);


            String rows  = "";
            String header = "UsernameFirst NameLast Name";
            while (result.next()) {

                String userName = result.getString(PERSON_USERNAME);
                String firstName = result.getString(PERSON_FNAME);
                String lastName = result.getString(PERSON_LNAME);

                String theUrl = 
                    "" + userName  +"";
                String row = ""+ 
                "" + theUrl+ "" +
                "" + firstName+ "" +
                "" + lastName+ "" +
                ""; 
                rows = rows + row; 
            }

            theTable = "" +
            header +
            rows +
            "";

            result.close();

            connection.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            System.out.println("Access Error");
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("Driver Error");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }  
        return theTable;
    }



    public String getMemberData(String username){

        Connection connection = null;
        String theTable = "";



        try {
            Class.forName(WorkoutDatabase.DRIVER).newInstance();
            connection = DriverManager
            .getConnection(WorkoutDatabase.URL+WorkoutDatabase.DBNAME,
                    WorkoutDatabase.USERNAME,
                    WorkoutDatabase.PASSWORD);

            String theQuery = "Select * from person where username='"+username+"';";

            Statement select = connection.createStatement();
            ResultSet result = select.executeQuery(theQuery);


            String rows ="";
            String header = "User: "+username + "";
            while (result.next()) {


                String firstName = result.getString(PERSON_FNAME);
                String lastName = result.getString(PERSON_LNAME);
                String gender = result.getString(PERSON_GENDER);
                String dob = result.getString(PERSON_BIRTH);
                String city = result.getString(PERSON_CITY);
                String state = result.getString(PERSON_STATE);
                String address = result.getString(PERSON_ADDRESS);

                rows =  

                    "First Name" + firstName+ "" +
                    "Last Name" + lastName+ "" + 
                    "Gender" + gender+ "" +
                    "DOB" + dob+ "" +
                    "Address" + address+ "" +
                    "City" + city+ "" +
                    "State" + state+ "";
            }

            theTable = "" +
            header +
            rows +
            "";

            result.close();

            connection.close();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            System.out.println("Access Error");
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("Driver Error");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }  
        return theTable;
    }




    public static void main(String[] arg){

        MemberListHelper helper = new MemberListHelper();
        System.out.println(helper.getMemberListTable());
        System.out.println("0----------");
        System.out.println(helper.getMemberData("mo"));
    }
}