package com.mrjw.guestbook;



import java.util.Properties;
import java.sql.*;
import java.util.*;
import java.io.*;


public class DBjw
{
  private String name;
  private String city;
  private String school;
  private String howdouknow;
  private String email;
  private String website;
  private String comments;
  private Vector guests;
  private String jwdates;
  private String query;
  private String jwshows;

  private int result;

  public DBjw()
 { result=0;
   name = "";
   city = "";
   school = "";
   howdouknow = "";
   email = "";
   website = "";
   comments = "";
   guests = new Vector();
   jwdates="";
   jwshows="";

 }

  public boolean addRecord() throws Exception
  { try
	{
	 Connection con = null;

	String OS = System.getProperty("os.name").toLowerCase();


	if ((OS.indexOf("windows xp")) > -1)
	{
	     // LAPTOP SETTINGS FOR ACCESS DB
		 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		 con = DriverManager.getConnection("jdbc:odbc:DATABASENAME","USERNAME","PASSWORD");

		 query = "INSERT INTO TABLENAME" +
			   " (name, city, school, howdouknow, email, website, comments, jwdates, jwshows)" +
			   " VALUES ('" +
			   name + "', '" +
			   city + "', '" +
			   school + "', '" +
			   howdouknow + "', '" +
			   email + "', '" +
			   website + "', '" +
			   comments + "', '" +
			   jwdates +"', '" +
			   jwshows +"'" +
			   ")";

	} else {
		 // GO DADDY SETTINGS FOR MYSQL DB
		 Class.forName("com.mysql.jdbc.Driver");
		 con = DriverManager.getConnection("jdbc:mysql://mysql181.secureserver.net/DATABASENAME","USERNAME","PASSWORD");

		 query = "INSERT INTO TABLENAME " +
			   " (name, city, school, howdouknow, email, website, comments, jwdates, jwshows) " +
			   " VALUES ('" +
			   name + "', '" +
			   city + "', '" +
			   school + "', '" +
			   howdouknow + "', '" +
			   email + "', '" +
			   website + "', '" +
			   comments + "', '" +
			   jwdates+ "', '"+
			   jwshows+"'" +
			   ");";
	}

	 Statement stmt = con.createStatement();

			   result = stmt.executeUpdate(query);

	System.out.println(query);

			   stmt.close();
			   con.close();


	}
   catch (Exception e)
	{
		System.out.println("Query " +query);
		e.printStackTrace();
	  throw e;
	}


   if ( result == 1 )
	   return true;
   else
	   return false;

   }


  public boolean updateShow() throws Exception
  { try
	{


	 Connection con = null;

	String OS = System.getProperty("os.name").toLowerCase();


	 if ((OS.indexOf("windows xp")) > -1)
	 {
	     // LAPTOP SETTINGS FOR ACCESS DB
		 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		 con = DriverManager.getConnection("jdbc:odbc:DATABASENAME","USERNAME","PASSWORD");
	 } else {
		 // GO DADDY SETTINGS FOR MYSQL DB
		 Class.forName("com.mysql.jdbc.Driver");
		 con = DriverManager.getConnection("jdbc:mysql://mysql181.secureserver.net/DATABASENAME","USERNAME","PASSWORD");
	 }

	 Statement stmt = con.createStatement();

	String showFlag;

	if (jwshows.trim().equals("true"))
		showFlag = "false";
	else showFlag ="true";



	 query = "UPDATE TABLENAME SET jwshows = '"+showFlag+"' WHERE " +
			   "name='" + name + "' and " +
			   "city='"+city+"' and " +
			   "school='"+school + "' and " +
			   "howdouknow='"+howdouknow + "' and " +
			   "email='"+email + "' and " +
			   "website='"+website + "' and " +
			   "comments='"+comments +"' and "+
			   "jwdates='"+jwdates + "'";


	System.out.println("QUERY "+jwshows+" "+query);

			   result = stmt.executeUpdate(query);


	System.out.println("QUERY "+query);

			   stmt.close();
			   con.close();


	}
    catch (Exception e)
	{
		System.out.println("Query " +query);
		e.printStackTrace();
	  throw e;
	}


   if ( result == 1 )
	   return true;
   else
	   return false;

   } // end of updateShow




public boolean showGuestbook() throws Exception
{ try
	{
	 Connection con = null;

	String OS = System.getProperty("os.name").toLowerCase();

	String query;

	if (OS.indexOf("windows") > -1)
	{
	     // LAPTOP SETTINGS FOR ACCESS DB
		 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		 con = DriverManager.getConnection("jdbc:odbc:DATABASENAME","USERNAME","PASSWORD");
		 query = "select * from guestbookjw order by jwdates";
	} else {
		 // GO DADDY SETTINGS FOR MYSQL DB
		 Class.forName("com.mysql.jdbc.Driver");
		 con = DriverManager.getConnection("jdbc:mysql://mysql181.secureserver.net/DATABASENAME","USERNAME","PASSWORD");
		  query = "select * from guestbookjw order by jwdates";
	}

	 Statement stmt = con.createStatement();

     ResultSet rs = stmt.executeQuery(query);

   String temp[] = new String[10];
   int i =0;

   guests.clear();

   while(rs.next()) {
		temp[0] = rs.getString("name");
		temp[1] = rs.getString("city");
		temp[2] = rs.getString("school");
		temp[3] = rs.getString("howdouknow");
		temp[4] = rs.getString("email");
		temp[5] = rs.getString("website");
		temp[6] = rs.getString("comments");
		temp[7] = rs.getString("jwdates");
		temp[8] = rs.getString("jwshows");

		if (temp[8] == "")
			temp[8] = "true";

	   guests.add(temp[0] +"%%%"+
				  temp[1] +"%%%"+
				  temp[2] +"%%%"+
				  temp[3] +"%%%"+
				  temp[4] +"%%%"+
				  temp[5] +"%%%"+
				  temp[6] +"%%%"+
				  temp[7] +"%%%"+
				  temp[8]);
   }

   stmt.close();
   con.close();

	}
   catch (Exception e)
	{ e.printStackTrace();
	  throw e;
	}


   if ( result == 1 )
	   return true;
   else
	   return false;

} // End of ShowGuestbook()


public String[] getGuest(int i)
{
	//System.out.println("I: "+i);

	String tempString = (String) guests.elementAt(i);
	String temp[] = tempString.split("%%%");

	for (int x=0; x < temp.length; x++)
		if (temp[x].equals("") || temp[x].equals(" "))
			temp[x] = "&nbsp;";

	return temp;
}

public int getSize()
{
	return guests.size();
}







 public String getName()
  {return  name;}
  public String getSchool()
  {return  school;}
  public String getHowDoUKnow()
  {return howdouknow;}
  public String getCity()
  {return  city;}
  public String getEmail()
  {return  email;}
  public String getWebsite()
  {return  website;}
  public String getComments()
 {return comments;}
  public String getJwdates()
 {return jwdates;}

 public String getJwshows()
 {return jwshows;}
 public void setJwshows()
 {this.jwshows = jwshows;}

 public void setJwdates(String jwdates)
 {this.jwdates = jwdates;}

 public void setName(String name)
 {this.name = name;}
 public void setSchool(String school)
 {this.school = school;}
 public void setHowDoUKnow(String howdouknow)
 {this.howdouknow = howdouknow;}
 public void setCity(String city)
 {this.city = city;}
 public void setEmail(String email)
 {this.email = email;}
 public void setWebsite(String website)
 {this.website = website;}
 public void setComments(String comments)
 {this.comments = comments;}

 public void setAll(String name, String city,String school, String howdouknow,String email,String website,String comments, String jwdates, String jwshows)
 {
	  this.name = name.replace('\'',' ');
	  this.school = school.replace('\'',' ');
	  this.howdouknow = howdouknow.replace('\'',' ');
	  this.city = city.replace('\'',' ');
	  this.email = email.replace('\'',' ');
	  this.website = website.replace('\'',' ');
	  this.comments = comments.replace('\'',' ');
	  this.jwdates = jwdates.replace('\'',' ');
	  this.jwshows = jwshows.replace('\'',' ');
 } // End of setAll

	 public String getTime()
	 {
		 String monthTwoDigits="";
		 String dayTwoDigits="";

		 //GregorianCalendar liftOffApollo11 = new GregorianCalendar(DateFormat.getDateTimeInstance() );
		 java.util.Date d = new java.util.Date();
		 java.text.DateFormat df1 = java.text.DateFormat.getDateTimeInstance(java.text.DateFormat.MEDIUM, java.text.DateFormat.MEDIUM);
		 java.text.DateFormat df2 = java.text.DateFormat.getTimeInstance(java.text.DateFormat.SHORT);
		 String s1 = df1.format(d);
		 String s2 = df2.format(d);

		 java.util.Calendar c = java.util.Calendar.getInstance();

		 if (c.get(Calendar.MONTH) < 10)
		 	monthTwoDigits = "0";
		 if (c.get(Calendar.DAY_OF_MONTH) < 10)
		 	dayTwoDigits = "0";


		System.out.println(c.get(Calendar.YEAR)+"-"+monthTwoDigits+c.get(Calendar.MONTH)+"-"+dayTwoDigits+c.get(Calendar.DAY_OF_MONTH)+" "+s2);
		System.out.println();
		 return c.get(Calendar.YEAR)+"-"+monthTwoDigits+c.get(Calendar.MONTH)+"-"+dayTwoDigits+c.get(Calendar.DAY_OF_MONTH) /*+" "+s2*/;

	 } // End of getTime()


}