package library.DBConnectOperation;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import library.model.Book;
import library.model.BookType;
import library.model.BorrowBookEntity;
import library.model.Operator;
import library.model.Reader;
import library.model.ReaderType;

public class Dbo {
	//protected static String dbClassName="sun.jdbc.odbc.JdbcOdbcDriver";
	protected static String dbClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver";
	protected static String dbUrl="jdbc:microsoft:sqlserver://localhost:1433;databasename=db_library";   //設置數據庫服務器URL和數據源名稱
	protected static String dbUser="sa";
	protected static String dbPassword="";
	//protected static String second=null;
	private static Connection conn=null;
	private Dbo(){
		try{
			if(conn==null){
				Class.forName(dbClassName).newInstance();
				conn=DriverManager.getConnection(dbUrl,dbUser,dbPassword);
			}
			else
				return;
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static ResultSet executeQuery(String sql){		//Query Function
		try{
			if(conn==null)
				new Dbo();
			return
			conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE).executeQuery(sql);		
//在多用戶情況下，ResultSet.TYPE_SCROLL_SENSITIVE表示可以滾動查詢并且支持顯示其他用戶更新（用于并發）ResultSet.CONCUR_UPDATABLE（支持結果集更新）
		}
		catch(final SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	public static int executeUpdate(String sql){
		try{
			if(conn==null)
			new Dbo();
			return conn.createStatement().executeUpdate(sql);
		}catch(final SQLException e){
			e.printStackTrace();
			return -1;
		}
	}
	public static void close(){	//close the database connection
		try{
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			conn=null;
		}
	}
	
	//驗證系統管理員登陸
	public static Operator check(String name,String password){
		Operator operator=new Operator();
		String sql="Select * from tb_operator where name='"+name
		+"' and password='"+password+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				operator.setId(rs.getInt("operatorID"));
				operator.setName(rs.getString("name"));
				operator.setGrade(rs.getInt("admin"));
				operator.setPassword(rs.getString("password"));
				
			}
		}catch(Exception e){
				e.printStackTrace();
		}
		Dbo.close();
		return operator;
	}
	//書號查詢方法，由AddBook類和BorrowBook類、ModifuAndDelBook類調用,
	public static String selectBookISBN(String ISBN){
		String sql="select ISBN from tb_bookinfo where ISBN='"
			+ISBN+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				return rs.getString("ISBN");
			}
			else return null;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	//查詢圖書類別的方法，有AddBook類調用
	public static ArrayList selectBookCategory(){
		ArrayList arrayList=new ArrayList();
		String sql="select * from tb_bookType";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			while(rs.next()){
				BookType bookType=new BookType();
				bookType.setId(Integer.parseInt(rs.getString("id")));
				bookType.setTypeName(rs.getString("typeName"));
				bookType.setDays(Integer.parseInt(rs.getString("days")));
				bookType.setFk(Integer.parseInt(rs.getString("fk")));
				bookType.setExpired(Integer.parseInt(rs.getString("expire")));
				arrayList.add(bookType);	//將元素加入數組鏈表中
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		Dbo.close();
		return arrayList;
	}
	//查詢圖書類別名稱的方法，由AddBookType類調用
	public static String selectBookTypeName(String bookTypeName){
		String sql="select typeName from tb_bookType where typeName='"+bookTypeName+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next())
			return rs.getString("typeName");
			else return null;
			}catch(Exception e){
				e.printStackTrace();
				return null;
		}
	}
	//查詢借閱證編號，由類AddReader和BorrowBook類調用，由ModiFyandDelReader調用,由類ReturnBook調用
	public static String selectReaderId(String id){
		String sql="select id from tb_reader where id='"+id+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next())
				return rs.getString("id");
			else return null;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	//查詢讀者類型，由AddReader類調用，由ModiftAndDelReaderType調用
	public static ArrayList selectReaderType(){
		String sql="select * from tb_readerType";
		ResultSet rs=Dbo.executeQuery(sql);
		ArrayList arrayList=new ArrayList();
		try{
			while(rs.next()){
				ReaderType readerType=new ReaderType();
				readerType.setTypeId(rs.getInt("typeId"));
				readerType.setTypeName(rs.getString("typeName"));
				readerType.setMaxBorrowNumber(rs.getInt("maxBorrowNumber"));
				readerType.setmaxKeepDays(rs.getInt("maxKeepDays"));
				arrayList.add(readerType);
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		Dbo.close();
		return arrayList;
	}
	//查詢某讀者類型名稱的方法
	public static String selectReaderTypeName(String readerTypeName){
		String sql="select typeName from tb_readerType where typeName='"+readerTypeName+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next())
			return rs.getString("typeName");
			else return null;
			}catch(Exception e){
				e.printStackTrace();
				return null;
		}
	}
	//查詢管理員昵稱，由類BorrowBook調用
	public static String selectOperatorName(String name){
		String sql="select name from tb_operator where name='"+name+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next())
				return rs.getString("name");
			else return null;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	//查詢讀者信息,有類BorrowBook調用,由ModifyAndDelReader類調用
	public static Reader selectReader(String readerId){
		String sql="select * from tb_reader where id='"+readerId+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				Reader reader=new Reader();
				reader.setAvailableNumber(rs.getInt("availableNumber"));
				reader.setBirthday(rs.getString("birthday"));
				reader.setBzTime(rs.getString("bztime"));
				reader.setCareer(rs.getString("career"));
				reader.setCertificateType(rs.getString("certificateType"));
				reader.setIdentityCardNumber(rs.getString("identityCardNumber"));
				reader.setKeepMoney(rs.getInt("keepmoney"));
				reader.setName(rs.getString("name"));
				reader.setReaderId(rs.getString("id"));
				reader.setSex(rs.getString("sex"));
				reader.setTel(rs.getString("tel"));
				reader.setTypeId(rs.getInt("typeid"));
				Dbo.close();
				return reader;
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return null;
	}
	//查詢圖書信息，由類BorrowBook和ModifuAndDelBook類調用
	public static Book selectBook(String ISBN){
		String sql="select * from tb_bookinfo where ISBN='"+ISBN+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				Book book=new Book();
				book.setBookname(rs.getString("bookname"));
				book.setDate(rs.getString("date"));
				book.setISBN(rs.getString("ISBN"));
				book.setPrice(rs.getFloat("price"));
				book.setPublisher(rs.getString("publisher"));
				book.setTypeid(rs.getInt("id"));
				book.setWriter(rs.getString("writer"));
				book.setTranslator(rs.getString("translator"));
				Dbo.close();
				return book;
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return null;
	}
	//查詢指定ID的圖書類別名稱，由類BorrowBook調用
	public static String selectBookTypeName(int id){
		String sql="select typeName from tb_bookType where id="+id;
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				return rs.getString("typeName");
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return null;
	}
	//查詢指定ID的圖書類別,有類ModifyAndDelBook調用
	public static BookType selectSelectedBookCategory(int id){
		String sql="select * from tb_bookType where id="+id;
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				BookType bookType=new BookType();
				bookType.setId(Integer.parseInt(rs.getString("id")));
				bookType.setTypeName(rs.getString("typeName"));
				bookType.setDays(Integer.parseInt(rs.getString("days")));
				bookType.setFk(Integer.parseInt(rs.getString("fk")));
				bookType.setExpired(Integer.parseInt(rs.getString("expire")));
				return bookType;
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		Dbo.close();
		return null;
	}
	//查詢某讀者所述的讀者類所允許的最大借閱圖書天數,由類BorrowBook調用
	public static int selectKeepDaysofReader(int id){
		String sql="select maxKeepDays from tb_readerType where typeid="+id;
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				return rs.getInt("maxKeepDays");
			}
			else return 0;
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
	}
	//統計某讀者的已借閱的圖書數目
	public static int selectBorrowNumber(String readerid){
		String sql="select count(*) from tb_borrow where readerid='"+readerid+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try {
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;