/**
 * 
 */
package hbu.david.cmc.work;

import hbu.david.cmc.bean.*;
import hbu.david.cmc.dao.*;
import hbu.david.cmc.util.StringUtil;

import java.util.*;
import java.sql.*;

/**
 * @author Administrator
 *
 */
public class CategoryBean {

	/**
	 * 
	 */
	public CategoryBean() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * @param args
	 * @throws SQLException 
	 */
	
	public static boolean isCategoryexist(Category category){
		boolean exist=false;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from category where name=?";
		
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, category.getName());
			rs=prepstmt.executeQuery();
			if(rs.next()){
				exist=true;
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			exist=true;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return exist;
	}
	/**
	 * 添加分類
	 * @return
	 * @throws SQLException 
	 */
	public static boolean addCategory(Category category){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="insert into category(name,introduce,userId,addTime) values(?,?,?,?)";
		if(isCategoryexist(category)){
			ok=false;
		}else{
			try{
				conn=DatabaseBean.getConnection();
				conn.setAutoCommit(false);
				prepstmt=conn.prepareStatement(sql);
				prepstmt.setString(1, category.getName());
				prepstmt.setString(2, category.getIntroduce());
				prepstmt.setInt(3, category.getUserId());
				prepstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
				ok=(1==prepstmt.executeUpdate());
				conn.commit();
				conn.setAutoCommit(true);
			}catch(SQLException e){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				ok=false;
			}finally{
				DatabaseBean.close(conn, prepstmt, null);
			}
		}
		return ok;
	}
	/**
	 * 獲取一定數量的category
	 * @throws SQLException 
	 */
	public static List<Category> getCategory(int start,int num,int userId){
		List<Category> arrayList=new ArrayList<Category>();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from category where userid=? order by addTime desc limit ?,?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setInt(1, userId);
			prepstmt.setInt(2, start);
			prepstmt.setInt(3, num);
			rs=prepstmt.executeQuery();
			Category category=null;
			while(rs.next()){
				category=new Category();
				/**
				category.setId(rs.getInt("id"));
				category.setName(rs.getString("name"));
				category.setIntroduce(rs.getString("introduce"));
				category.setUrl(rs.getString("url"));
				category.setOpen(rs.getInt("open"));
				category.setUserId(userId);
				category.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
				category.setPhotoNum(PhotoBean.getPhotoNum(categoryId, 0));
				*/
				category= getCategoryById(rs.getInt("id"));
				arrayList.add(category);
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			arrayList=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return arrayList;
	}
	
	public static List<Category> getCategory(int userId){
		List<Category> arrayList=new ArrayList<Category>();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from category where userid=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setInt(1, userId);
			rs=prepstmt.executeQuery();
			Category category=null;
			while(rs.next()){
				category=new Category();
				/**
				category.setId(rs.getInt("id"));
				category.setName(rs.getString("name"));
				category.setIntroduce(rs.getString("introduce"));
				category.setUrl(rs.getString("url"));
				category.setOpen(rs.getInt("open"));
				category.setUserId(userId);
				category.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
				*/
				category= getCategoryById(rs.getInt("id"));
				arrayList.add(category);
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			arrayList=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return arrayList;
	}
	/**
	 * 獲取category的個數
	 * @throws SQLException 
	 */
	public static int getCategoryNum(int userId){
		int num=0;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select count(*) from category where userid=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setInt(1, userId);
			rs=prepstmt.executeQuery();
			//Category category=null;
			while(rs.next()){
				num=rs.getInt(1);
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			num=0;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return num;
	}
	/**
	 * 刪除根據id號碼和usrId號碼
	 * @param args
	 * @throws SQLException 
	 */
	public static boolean deleteCategoryById(int id,int userId){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="delete from category where id=? and userId=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setInt(1, id);
			prepstmt.setInt(2, userId);
			ok=(1==prepstmt.executeUpdate());
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, null);
		}
		return ok;
		
	}
	
	/**
	 * 根據id獲取相冊信息
	 * @param args
	 * @throws SQLException 
	 */
	public static Category getCategoryById(int categoryId) {
		Category category=new Category();
		Connection conn=null;
		PreparedStatement prepstmt=null;
		ResultSet rs=null;
		String sql="select * from category where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setInt(1, categoryId);
			rs=prepstmt.executeQuery();
			while(rs.next()){
				category.setId(rs.getInt("id"));
				category.setName(rs.getString("name"));
				category.setIntroduce(rs.getString("introduce"));
				category.setOpen(rs.getInt("open"));
				category.setUrl(rs.getString("url"));
				category.setUserId(rs.getInt("userId"));
				category.setAddTime(StringUtil.changeTimestamp(rs.getTimestamp("addTime")));
				category.setPhotoNum(PhotoBean.getPhotoNum(categoryId, 0));
			}
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			category=null;
		}finally{
			DatabaseBean.close(conn, prepstmt, rs);
		}
		return category;
	}
	/**
	 * 修改某個分類的代表照片
	 * @param args
	 * @throws SQLException 
	 */
	public static boolean updateCategoryUrl(int id,int categoryId){
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="update category set url=? where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, PhotoBean.getPhoto(id).getUrl());
			prepstmt.setInt(2, categoryId);
			ok=(1==prepstmt.executeUpdate());
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, null);
		}
		return ok;
	}
	/**
	 * 更新分類
	 * @param args
	 * @throws SQLException 
	 */
	public static boolean updateCategory(Category category) {
		boolean ok=true;
		Connection conn=null;
		PreparedStatement prepstmt=null;
		String sql="update category set name=?,introduce=? where id=?";
		try{
			conn=DatabaseBean.getConnection();
			conn.setAutoCommit(false);
			
			prepstmt=conn.prepareStatement(sql);
			prepstmt.setString(1, category.getName());
			prepstmt.setString(2, category.getIntroduce());
			prepstmt.setInt(3, category.getId());
			ok=(1==prepstmt.executeUpdate());
			conn.commit();
			conn.setAutoCommit(true);
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			ok=false;
		}finally{
			DatabaseBean.close(conn, prepstmt, null);
		}
		return ok;
	} 
	public static void main(String[] args){
		System.out.print(PhotoBean.getPhotoNum(21, 0));
	}

}