}

	public static DruginfoDAO getFromApplicationContext(ApplicationContext ctx) {
		return (DruginfoDAO) ctx.getBean("DruginfoDAO");
	}

	public List<Druginfo> findByDate() {
		String sql = "select * from druginfo d where date_sub(d.productDate ,INTERVAL 7 DAY) <= current_date()";
		List<Druginfo> list = new ArrayList<Druginfo>();
		Session session = getHibernateTemplate().getSessionFactory()
				.openSession();
		conn = session.connection();
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Druginfo druginfo = new Druginfo();
				druginfo.setId(rs.getInt("id"));
				druginfo.setDrugName(rs.getString("drugName"));
				druginfo.setProducer(rs.getString("producer"));
				druginfo.setProductDate(rs.getTimestamp("productDate"));
				druginfo.setAmount(rs.getFloat("amount"));
				druginfo.setForcastNumber(rs.getInt("forcastNumber"));
				druginfo.setPrice(rs.getInt("price"));
				list.add(druginfo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public List<Druginfo> findByForcastNumber() {
		String sql = "select * from druginfo where forcastNumber>=amount";
		List<Druginfo> list = new ArrayList<Druginfo>();
		Session session = getHibernateTemplate().getSessionFactory()
				.openSession();
		conn = session.connection();
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Druginfo druginfo = new Druginfo();
				druginfo.setId(rs.getInt("id"));
				druginfo.setDrugName(rs.getString("drugName"));
				druginfo.setProducer(rs.getString("producer"));
				druginfo.setProductDate(rs.getTimestamp("productDate"));
				druginfo.setAmount(rs.getFloat("amount"));
				druginfo.setForcastNumber(rs.getInt("forcastNumber"));
				druginfo.setPrice(rs.getInt("price"));
				list.add(druginfo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public List<Druginfo> likeFindByName(String drugName) {

		String sql = "select d.*,c.unit from druginfo d ,classinfo c where d.subClassId= c.id and d.drugName like '"
				+ drugName + "%'";
		List<Druginfo> list = new ArrayList<Druginfo>();
		Session session = getHibernateTemplate().getSessionFactory()
				.openSession();
		conn = session.connection();
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Druginfo druginfo = new Druginfo();
				druginfo.setId(rs.getInt("id"));
				druginfo.setDrugName(rs.getString("drugName"));
				druginfo.setProducer(rs.getString("producer"));
				druginfo.setProductDate(rs.getTimestamp("productDate"));
				druginfo.setAmount(rs.getFloat("amount"));
				druginfo.setForcastNumber(rs.getInt("forcastNumber"));
				druginfo.setPrice(rs.getInt("price"));
				Classinfo classinfo = new Classinfo();
				classinfo.setUnit(rs.getString("unit"));
				druginfo.setClassinfo(classinfo);
				list.add(druginfo);
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	
	public List<Classinfo> findClassById(int id) {
		List<Classinfo> list = new ArrayList<Classinfo>();
		String sql = "select c.* from druginfo d ,classinfo c where d.subClassId= c.id and d.id ="
				+ id;
		Session session = getHibernateTemplate().getSessionFactory()
				.openSession();
		conn = session.connection();
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Classinfo classinfo = new Classinfo();
				classinfo.setUnit(rs.getString("unit"));
				list.add(classinfo);
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return list;
	}

	public boolean updAmount(Druginfo druginfo) {
		boolean i = false;
		String sql = "update druginfo set amount=amount-"
				+ druginfo.getAmount() + " where id=" + druginfo.getId()
				+ " and amount-" + druginfo.getAmount() + ">=0.0";
		Session session = getHibernateTemplate().getSessionFactory()
				.openSession();
		conn = session.connection();
		try {
			pstm = conn.prepareStatement(sql);
			i = !pstm.execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return i;
	}

	public List<Druginfo> findPriceById(int id) {
		List<Druginfo> list = new ArrayList<Druginfo>();
		String sql = "select * from druginfo where id ="+ id;
		Session session = getHibernateTemplate().getSessionFactory()
				.openSession();
		conn = session.connection();
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Druginfo durginfo = new Druginfo();
				durginfo.setPrice(rs.getInt("price"));
				list.add(durginfo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	
	
	public List<Druginfo> findClassByClassId(int id) {
		List<Druginfo> list = new ArrayList<Druginfo>();
		String sql = "select * from druginfo where subclassid = " + id;
		Session session = getHibernateTemplate().getSessionFactory()
				.openSession();
		conn = session.connection();
		try {
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Druginfo durginfo = new Druginfo();
				durginfo.setDrugName(rs.getString("drugName"));
				list.add(durginfo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return list;
	}

	public boolean upddateDrug(Druginfo druginfo) {
		boolean i = false;
		String sql = "update druginfo set drugName=?,productDate=?,producer=?,amount=?,forcastNumber=? where id=?";
		Session session = getHibernateTemplate().getSessionFactory()
				.openSession();
		conn = session.connection();
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, druginfo.getDrugName());
			pstm.setTimestamp(2, druginfo.getProductDate());
			pstm.setString(3, druginfo.getProducer());
			pstm.setFloat(4, druginfo.getAmount());
			pstm.setInt(5, druginfo.getForcastNumber());
			pstm.setInt(6, druginfo.getId());
			i = pstm.execute();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstm.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return i;
	}
}