import java.net.URL;
import java.sql.*;

/**
 * class UserDAO
 * stores and retrieves objects of type User
 *
 * @author Jim
 * @version 1.0
 */
public class PackageDAO
{
    private static Connection theConn;

    /**
     * Finds a package from the database, specified by packageID
     *
     * @param packageID the target packageID
     * @return the Package retrieved from the database
     */
    public static Package getByPackageID(int packageID)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();
             Package p = null;

             ResultSet rs;
             Statement stmt;
             String sql;

             sql =
                "SELECT * " +
                "FROM Packages " +
                "WHERE packageID = " + packageID;
             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             rs = stmt.executeQuery(sql);

             // use only first row of result - should be unique
             if (rs.next()) {
                 p = new Package(rs.getInt("packageID"),
                                rs.getString("location"),
                                rs.getString("name"),
                                rs.getString("description"),
                                rs.getDouble("adultprice"),
                                rs.getDouble("childprice"),
                                rs.getString("departure"),
                                rs.getInt("sales")
                                );
             }
             rs.close();
             stmt.close();
             return p;
         }
         catch (Exception e) {
             System.out.println("COULD NOT COMPLETE QUERY: " + e.toString());
             //e.printStackTrace();
             return null;
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }


    /**
     * Finds all packages in the database.
     * Limited to first 20 packages.
     * Should use larger array or collection to get more
     *
     * @return array of Package objects retrieved from the database
     */
    public static Package[] getAll()
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();

             Package[] packages = new Package[20];
             Package p = null;

             ResultSet rs;
             Statement stmt;
             String sql;

             sql =
                "SELECT * FROM Packages";

             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             rs = stmt.executeQuery(sql);

             int count = 0;
             while (rs.next() && count<20) {
                p = new Package(rs.getInt("packageID"),
                                rs.getString("location"),
                                rs.getString("name"),
                                rs.getString("description"),
                                rs.getDouble("adultprice"),
                                rs.getDouble("childprice"),
                                rs.getString("departure"),
                                rs.getInt("sales")
                                );
                packages[count] = p;
                count++;
             }
             rs.close();
             stmt.close();
             return packages;
         }
         catch (Exception e) {
             System.out.println("COULD NOT COMPLETE QUERY: " + e.toString());
             //e.printStackTrace();
             return null;
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
     }

    /**
     * Stores a new Package object to the database
     *
     * @param package the Package object to be stored
     */
    public static void storeNew(Package p)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();

             //check for new object (packageID = 0)
             if (p.getPackageID()!=0){
                 System.out.println("Use this method to store newly created packages only");
             }
             else {
                 Statement stmt;
                 String sql;

                 sql =          // note that packageID will be set automatically by the database
                    "INSERT INTO Packages(location,name,description,adultprice,childprice,departure,sales) "+
                    "VALUES('" +
                        p.getLocation() + "','" +
                        p.getName() + "','" +
                        p.getDescription() + "'," +
                        p.getAdultPrice() + "," +
                        p.getChildPrice() + ",'" +
                        p.getDeparture() + "'," +
                        p.getSales() + ")";

                 System.out.println("Query: " + sql);   // for debugging SQL statement

                 stmt = theConn.createStatement();
                 int rowsAffected = stmt.executeUpdate(sql);

                 System.out.println(rowsAffected + " row(s) inserted");   // to check whether insert was successful

                 stmt.close();
            }
         }
         catch (Exception e) {
             System.out.println("COULD NOT STORE: " + e.toString());
             //e.printStackTrace();
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }

    /**
     * Stores an update Package object to the database
     *
     * @param package the Package object to be stored
     */
    public static void storeUpdated(Package p)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();


             Statement stmt;
             String sql;

             sql =          // note that packageID will be set automatically by the database
                "UPDATE Packages SET " +
                    "location = '" +p.getLocation() + "'," +
                    "name = '" + p.getName() + "'," +
                    "description = '" + p.getDescription() + "'," +
                    "adultprice = " + p.getAdultPrice() + "," +
                    "childprice = " + p.getChildPrice() + "," +
                    "departure = '" + p.getDeparture() + "'," +
                    "sales = " + p.getSales() +
                    " WHERE packageID = " + p.getPackageID();

             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             int rowsAffected = stmt.executeUpdate(sql);

             System.out.println(rowsAffected + " row(s) inserted");   // to check whether insert was successful

             stmt.close();
         }
         catch (Exception e) {
             System.out.println("COULD NOT STORE: " + e.toString());
             //e.printStackTrace();
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }

     /**
      * Gets a Connection object to the database
      *
      * @return the Connection object
      */
      private static Connection getConnection() throws Exception {
        Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
        //String URL = "jdbc:odbc:gcutours";
        //Connection c = DriverManager.getConnection(URL, null, null);
        Connection c = DriverManager.getConnection(
            "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" +
            "DBQ=c:/Documents and Settings/student/My Documents/databases/gcutours_app.mdb"
        );
        return c;
    }
}