//yangxuyu 2006.8.31
package bookInfo;

import java.sql.*;
import java.util.*;
import com.ConnDB1;
import com.Str;

public class BookInfoDB extends ConnDB1 implements IBookInfoDB{
	//Connection connection;
    private ArrayList booklists;
	private String strSQL;
   
    public BookInfoDB ()
    throws Exception
    {
        //
    }
    
    //得到圖書的詳細信??
    public BookInfo getBookDetailInfo (int bid)
    {
        try
        {
            ConnDB1.initialize();
            PreparedStatement preparedStatement = aConnection.prepareStatement ("SELECT bookInfoId,bookTypeId,price,amount,bookTitle,author,ISBN,pubDate,content,press,picPath FROM bookInfo WHERE bookInfoId = ?");
            preparedStatement.setInt (1, bid);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
            	BookInfo bookinfo = new BookInfo
                (
                    resultSet.getInt (1), resultSet.getInt (2), resultSet.getFloat (3),resultSet.getInt (4), resultSet.getString (5),
                    resultSet.getString (6), resultSet.getString (7), resultSet.getString (8),resultSet.getString (9), resultSet.getString (10), resultSet.getString (11)
                );
                preparedStatement.close ();
                ConnDB1.terminate();
                return bookinfo;
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                return null;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return null;
        }
    }

    public boolean find(String key) 
	{    
    	boolean gotIt=false;
    	try
        {
    		ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement (
            		"SELECT bookInfoId FROM bookinfo WHERE isbn = ?");
            preparedStatement.setString (1, key);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
            	gotIt=true;
            	preparedStatement.close ();
            	ConnDB1.terminate();
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                //return isValid;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            //return gotIt;
        }
        return gotIt;
	}
    
    //添加圖書信息
    public int addBook (BookInfo books)
    {
        int rowsAffected = 0;
        try
        {
            ConnDB1.initialize();
            PreparedStatement preparedStatement = aConnection.prepareStatement ("INSERT INTO bookInfo (bookTypeId, bookTitle, author, ISBN, pubDate, price, content, press, amount,picPath) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            preparedStatement.setInt (1, books.getBookTypeId());
            preparedStatement.setString (2, books.getBookTitle());
            preparedStatement.setString (3, books.getAuthor());
            preparedStatement.setString (4, books.getIsbn());
            preparedStatement.setString (5, books.getPubdate());
            preparedStatement.setFloat (6, books.getPrice());
            preparedStatement.setString (7, books.getContent());
            preparedStatement.setString (8, books.getPress());
            preparedStatement.setInt (9, books.getAmount());
            preparedStatement.setString (10, books.getPicpath());
            //判斷是否重復添加
            if(find(books.getIsbn())){
			     rowsAffected =2; 
            }
            else{ 	
                 rowsAffected = preparedStatement.executeUpdate ();
            }
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    //刪除圖書信息
    public int deleteBooks (int id)
    {
        int rowsAffected = 0;
        try
        {
            ConnDB1.initialize();
            PreparedStatement preparedStatement = aConnection.prepareStatement ("DELETE FROM bookInfo WHERE bookInfoId = ?");
            preparedStatement.setInt (1, id);
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    //修改圖書信息
    public int modifyBooks (BookInfo books)
    {
        int rowsAffected = 0;
        try
        {
            ConnDB1.initialize();
            PreparedStatement preparedStatement = aConnection.prepareStatement ("UPDATE bookInfo SET bookTypeId=?, bookTitle=?, author=?, ISBN=?, pubDate=?, price=?, content=?, press=?, amount=?, picPath=? WHERE bookInfoId =?");
            preparedStatement.setInt (1, books.getBookTypeId());
            preparedStatement.setString (2, books.getBookTitle());
            preparedStatement.setString (3, books.getAuthor());
            preparedStatement.setString (4, books.getIsbn());
            preparedStatement.setString (5, books.getPubdate());
            preparedStatement.setFloat (6, books.getPrice());
            preparedStatement.setString (7, books.getContent());
            preparedStatement.setString (8, books.getPress());
            preparedStatement.setInt (9, books.getAmount());
            preparedStatement.setString (10, books.getPicpath());
            preparedStatement.setInt (11, books.getBookInfoId());
            
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
            ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    //  按圖書類別得到圖書列??
    public Collection getBooksList (int btid)
    {
        booklists = new ArrayList ();
        try
        {
            ConnDB1.initialize();
            PreparedStatement preparedStatement = aConnection.prepareStatement ("SELECT bookInfoId,bookTypeId,price,amount,bookTitle,author,ISBN,pubDate,content,press, picPath FROM bookInfo WHERE bookTypeId = ?");
            preparedStatement.setInt (1, btid);
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ())
            {
            	BookInfo booktype = new BookInfo
                (
                    resultSet.getInt (1), resultSet.getInt (2), resultSet.getFloat (3),resultSet.getInt (4), resultSet.getString (5),
                    resultSet.getString (6), resultSet.getString (7), resultSet.getString (8),resultSet.getString (9), resultSet.getString (10),resultSet.getString (11)
                );
            	booklists.add (booktype);
            }
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
            return null;
        }
        ConnDB1.terminate();
        //Collections.sort (booklists);
        return booklists;
    }
    public Collection getBooksLasted ()
    {
        booklists = new ArrayList ();
        try
        {
            ConnDB1.initialize();
            PreparedStatement preparedStatement = aConnection.prepareStatement ("SELECT bookInfoId,bookTypeId,price,amount,bookTitle,author,ISBN,pubDate,content,press, picPath FROM bookInfo");
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ())
            {
            	BookInfo booktype = new BookInfo
                (
                    resultSet.getInt (1), resultSet.getInt (2), resultSet.getFloat (3),resultSet.getInt (4), resultSet.getString (5),
                    resultSet.getString (6), resultSet.getString (7), resultSet.getString (8),resultSet.getString (9), resultSet.getString (10),resultSet.getString (11)
                );
            	booklists.add (booktype);
            }
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
            return null;
        }
        ConnDB1.terminate();
        //Collections.sort (booklists);
        return booklists;
    }
    
    //查詢，按查詢類別
    public Collection getBooksListBySearch (String search, String keyword)
    {
        booklists = new ArrayList ();
        //漢字轉換
        Str chr=new Str();
        keyword=chr.toChinese(keyword);
        try
        {
            switch(Integer.parseInt(search)){
                case 3:
            	strSQL="SELECT bookInfoId,bookTypeId,price,amount,bookTitle,author,ISBN,pubDate,content,press, picPath FROM bookInfo WHERE author = ?";
                break;
              
                case 4:
            	strSQL="SELECT bookInfoId,bookTypeId,price,amount,bookTitle,author,ISBN,pubDate,content,press, picPath FROM bookInfo WHERE press = ?";
                break;
                
                case 2:
            	strSQL="SELECT bookInfoId,bookTypeId,price,amount,bookTitle,author,ISBN,pubDate,content,press, picPath FROM bookInfo WHERE isbn = ?";
                break;
            
                default:
                strSQL="SELECT bookInfoId,bookTypeId,price,amount,bookTitle,author,ISBN,pubDate,content,press, picPath FROM bookInfo WHERE bookTitle = ?";
            }
            ConnDB1.initialize();
			PreparedStatement preparedStatement = aConnection.prepareStatement (strSQL);
            preparedStatement.setString (1, keyword);
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ())
            {
            	BookInfo booktype = new BookInfo
                (
                    resultSet.getInt (1), resultSet.getInt (2), resultSet.getFloat (3),resultSet.getInt (4), resultSet.getString (5),
                    resultSet.getString (6), resultSet.getString (7), resultSet.getString (8),resultSet.getString (9), resultSet.getString (10),resultSet.getString (11)
                );
            	booklists.add (booktype);
            }
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
            return null;
        }
        ConnDB1.terminate();
        //Collections.sort (booklists);
        return booklists;
    }
}