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(); } }}