package com.robinson.sql;

import java.util.*;
import java.util.Date;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.text.ParseException;


/**
 * Created by IntelliJ IDEA.
 * User: Sean Robinson
 * Date: Oct 17, 2006
 * Time: 1:42:38 PM
 * To change this template use File | Settings | File Templates.
 */
public class CRSManager {



    public Date getMaxDate(Connection con, String equipmentid) throws SQLException {
       Date date = null;
        try {
            int id = Integer.parseInt(equipmentid);
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT dateend FROM inventory WHERE ID="+id);
            while(rs.next()){
                date = rs.getDate("dateend");
            }

        } catch(Exception e){

            e.printStackTrace();
            throw new SQLException("Error in getMaxDate method");
        }
        return date;
    }

    public Date getMinDate(Connection con, String equipmentid) throws SQLException {
        Date date = null;

        try {
            int id = Integer.parseInt(equipmentid);
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT datestart FROM inventory WHERE ID="+id);
            while(rs.next()){
                date = rs.getDate("datestart");
            }
        } catch(Exception e){
            e.printStackTrace();
            throw new SQLException("Error in getMinDate method");
        }

        return date;
    }
    public String convertDateToString(Date date) throws SQLException {
        SimpleDateFormat sf = new SimpleDateFormat("MM/dd/yyyy");
        Date date1  = new Date();
        String boo = sf.format(date1);
        return boo;
    }

    public Date convertStringToDate(String date) throws ParseException {

        SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
        Date current = new Date();
        String datestring = "10/11/2007";
        Date date1 = df.parse(date);



        return date1;
    }

    public void createPeriod(Connection con, int period, String labid, String desc ) throws SQLException {
        try {
           // int per = Integer.parseInt(period);
            int lab = Integer.parseInt(labid);


            PreparedStatement st = con.prepareStatement("INSERT INTO periods(name,fullname,equip) VALUES(?,?,?)");
            st.setInt(1,period);
            st.setString(2,desc);
            st.setInt(3,lab);
            st.executeUpdate();
            st.close();

        } catch(Exception e){
            System.out.println(e.getMessage());
            throw new SQLException("Error in createPeriod method");
        }
    }

    public List getPeriods(Connection con) throws SQLException {
        List periods = new ArrayList();
        try {
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT ID FROM periods ORDER by ID ASC");
            while(rs.next()){
                 periods.add(new PeriodList(rs.getInt("ID")));
            }
        } catch(Exception e){
            System.out.println(e.getMessage());
            throw new SQLException("Error in getPeriods method");
        }
        return periods;
    }

    public void createEquipmentDefaultReservations(Connection con, List periods, Date startdate, Date enddate, String equipmentid, int room, String username)
    throws SQLException {
        try {
            List pers = periods;
            GregorianCalendar minCal = new GregorianCalendar();
            GregorianCalendar maxCal = new GregorianCalendar();
            minCal.setTime(startdate);
            maxCal.setTime(enddate);
            while(!minCal.equals(maxCal)){

                for(int i=0;i<pers.size();i++){
                    PeriodList per = (PeriodList)pers.get(i);
                    createReservation(con,per.getPeriod(),minCal.getTime(),equipmentid,room,username);
                }
                minCal.add(Calendar.DAY_OF_MONTH,1);

            }

        } catch(Exception e){
            e.printStackTrace();
            //System.out.println(e.getMessage());
            //throw new SQLException("Error in createEquipmentDefaultReservations");
        }
    }

    public void createReservation(Connection con,int period, Date date, String equipmentid, int room,String username) throws SQLException {
        try {

            int equip = Integer.parseInt(equipmentid);

            SimpleDateFormat sf = new SimpleDateFormat("MM/dd/yyyy");
            String d = sf.format(date);
            java.sql.Date sqldate = java.sql.Date.valueOf(d);


            PreparedStatement st = con.prepareStatement("INSERT INTO reservations(username, period, equipment,room,date) VALUES(?,?,?,?,?)");
            st.setString(1,username);
            st.setInt(2,period);
            st.setInt(3,equip);
            st.setInt(4,room);
            st.setDate(5,sqldate);

            st.executeUpdate();
            st.close();

        } catch(Exception e){
            e.getMessage();
            e.printStackTrace();
            //System.out.println(e.getMessage());
            //throw new SQLException("Error in createReservation");
        }
    }
        public static void main(String args[]){
           try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:crs","","");

            CRSManager c = new CRSManager();
               List pers = c.getPeriods(con);

               Date start = c.getMinDate(con,"1");
               Date end = c.getMaxDate(con,"1");

               int room = 0;
               String user = "Nobody";


               c.createEquipmentDefaultReservations(con,pers,start,end,"1",room,user);
               /* for(int i=0; i<pers.size();i++){
                  PeriodList p = (PeriodList)pers.get(i);
                   System.out.println(p.getPeriod());
               } */
               // c.createEquipmentDefaultReservations(con,pers,start,end,"1",room,user);
               con.close();


               /*Date d2 = c.getMaxDate(con,"1");
               Date d1 = c.getMinDate(con,"1");
               SimpleDateFormat sf = new SimpleDateFormat("MM/dd/yyyy");
               System.out.println("Min Date: "+ sf.format(d1));
               System.out.println("Max Date: " + sf.format(d2));
               GregorianCalendar gc1 = new GregorianCalendar();
               GregorianCalendar gc2 = new GregorianCalendar();
               List dates = new ArrayList();

               gc1.setTime(d1);
               gc2.setTime(d2);


               while(!gc1.equals(gc2)){
                   Date d = gc1.getTime();
                   //System.out.println(sf.format(gc1.getTime()));
                   dates.add(new DateObj(d));
                   gc1.add(5,1);
               }

               for(int i=0; i<dates.size();i++){
                   GregorianCalendar c1= new GregorianCalendar();
                   GregorianCalendar c2 = new GregorianCalendar();

                  DateObj dateob = (DateObj)dates.get(i);
                   Date d = dateob.getDate();
                   c1.setTime(d);
                   Date d3 = gc1.getTime();
                   c2.setTime(d1);

                    System.out.println(sf.format(d));

                   if(c1.equals(c2)){
                       System.out.println("Match");
                   }



               } */

               /* Date d = gc2.getTime();
               System.out.println(sf.format(gc1.getTime()));
               System.out.println(sf.format(d));
               System.out.println(gc1.getTime());*/






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

    }

}