package bookshop.run;
/**
 * <p>管理圖書的類，包括圖書的修改、查詢，刪除，添加 </p>
 */
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;
import bookshop.util.*;
import bookshop.book.*;
import javax.servlet.http.HttpServletRequest;
import java.util.Date;

public class op_book extends DataBase{
  private book abooks = new book();	                       //新的圖書類
		private javax.servlet.http.HttpServletRequest request; //建立頁面請求
		private boolean sqlflag = true ;		    //對接收到的數據是否正確
		private Vector booklist;
		ArrayList al=new ArrayList();			    //顯示圖書列表向量數組
		private int page = 1;				       //顯示的頁碼
		private int pageSize=3;				//每頁顯示的圖書數
		private int pageCount =0;				//頁面總數
		private int recordCount =0;  //查詢的記錄總數
		private int   cfirstpage=1;
		private int flag=1;			        
		public String sqlStr="";
		public Vector getBooklist() {
				return booklist;
				 
		}
	    public ArrayList getABooklist() {
					return al;
				 
			}
		public boolean getSqlflag() {
				return sqlflag;
		}
		public String to_String( String str) {
				try
				{
						return new String(str.getBytes("ISO8859-1"));
				}
				catch (Exception e)
				{
						return str;
				}
		}
		/**
		 * 將頁面表單傳來的資料分解
		 */
public boolean getRequest(javax.servlet.http.HttpServletRequest newrequest) {
  boolean flag = false;
  try{
   request = newrequest;
   String ID = request.getParameter("id");
   System.out.println("request id="+ID);
   long bookid = 0;
   try{
		   bookid = Long.parseLong(ID);
   }catch (Exception e){
   }
   abooks.setId(bookid);
   String bookname = request.getParameter("bookname");
   System.out.println("to bookname");
   if (bookname==null || bookname.equals(""))
   {
		   bookname = "";
		   sqlflag = false;
   }
   abooks.setBookName(to_String(bookname));
   String author = request.getParameter("author");
   if (author==null || author.equals(""))
   {
		   author = "";
		   sqlflag = false;
   }
   abooks.setAuthor(to_String(author));
   String publish = request.getParameter("publish");;
   if (publish==null)
   {
		   publish = "";
   }
   abooks.setPublish(to_String(publish));
   String bookclass = request.getParameter("bookclass");
   int bc = Integer.parseInt(bookclass);
   abooks.setBookClass(bc);
   String bookno = request.getParameter("bookno");
   if (bookno == null)
   {
		   bookno = "";
   }
   abooks.setBookNo(to_String(bookno));
   String picture = request.getParameter("picture");
   if (picture == null)
   {
		   picture = "images/01.gif";
   }
   abooks.setPicture(to_String(picture));
   float price;
   try	{
		   price =new Float(request.getParameter("price")).floatValue();
   } catch (Exception e){
		   price = 0;
		   sqlflag = false;
   }
   abooks.setPrince(price);
  
   int amount;
   try{
		   amount = new Integer(request.getParameter("amount")).intValue();
   }catch (Exception e){
		   sqlflag = false;
		   amount = 0;
   }
   abooks.setAmount(amount);
   
    int Leav_number;
	  try{
		Leav_number = new Integer(request.getParameter("Leav_number")).intValue();
	  }catch (Exception e){
			  sqlflag = false;
		Leav_number = 0;
	  }
	  abooks.setLeav_number(Leav_number);
   
  String content = request.getParameter("content");
   if (content == null)
   {
		   content = "";
   }
   abooks.setContent(to_String(content));
   if (sqlflag)
   {
		   flag = true;
   }
   return flag;
				}catch (Exception e){
   return flag;
				}
		}
		
		
		
		
		
		/**
		 * 獲得查詢圖書類別的sql語句
		 * @return
		 */
public String getSql() {
   sqlStr = "select id,classname from book order by id";
   return sqlStr;
		}
		/**
		 * 完成圖書查詢，包括分類，分頁查詢
		 * @param res
		 * @return
		 * @throws java.lang.Exception
		 */
public boolean book_search(HttpServletRequest res) throws Exception {
	System.out.println("to search");
   DataBase db = new DataBase();
   db.connect();
   stmt = db.conn.createStatement ();
   request = res;
   String PAGE = request.getParameter("page");   //頁碼
   String classid = request.getParameter("classid");	//分類ID號
   String keyword = request.getParameter("keyword");	//查詢關鍵詞
   if (classid==null) classid="";
   if (keyword==null) keyword = "";
   keyword = to_String(keyword).toUpperCase();
   try {
		   page = Integer.parseInt(PAGE);
   }catch (NumberFormatException e){
		   page = 1;
   }
   //取出記錄數
   if (!classid.equals("") && keyword.equals("") ) {
		   sqlStr = "select count(*) from book where bookclass='"+classid + "'";
	  }
  else if (!keyword.equals("")) {
	  if (classid.equals("")){
		   sqlStr = "select count(*) from book where upper(bookname) like '%" +
					keyword+ "%' or upper(content) like '%" + keyword + "%'";
	  } else {
		  sqlStr = "select count(*) from book where bookclass='" + classid
				   + "' and  (upper(bookname) like '%" +keyword+ "%' or "+
				   "upper(content) like '%" + keyword + "%')";
		   }
   } else {
		   sqlStr = "select count(*) from book";
		  }
   int rscount = pageSize;
   try {
		   ResultSet rs1 = stmt.executeQuery(sqlStr);
		   if (rs1.next()) recordCount = rs1.getInt(1);
		   System.out.println("count="+recordCount);
		   rs1.close();
   }catch (SQLException e){
		   System.out.println(e.getMessage());
		   return false;
   }
   //設定有多少pageCount
   if (recordCount < 1)
			pageCount = 0;
   else
			pageCount = (int)(recordCount - 1) / pageSize + 1;
   //檢查查看的頁面數是否在范圍內
   if (page < 1)
			page = 1;
  else if (page > pageCount)
			page = pageCount;
			rscount = (int) recordCount % pageSize;	 // 最后一頁記錄數
   //sql為倒序取值
   sqlStr = "select   a.Id,a.BookName,a.BookClass,b.ClassName,"+
		   "a.Author,a.Publish,a.BookNo,a.Content,a.Price,a.Amount,"+
		   "a.Leav_number,a.RegTime,a.picture from book a,bookclass b"+
		  " where a.BookClass = b.Id ";
  if (!classid.equals("") && keyword.equals("") ){  //如果類別不為空，非查詢
		   if (page == 1)
		   {
			   sqlStr = sqlStr + " and a.bookclass='" + classid + "' "+
			   "order by a.Id desc";
		   } else {
			   sqlStr = sqlStr + " and a.bookclass='" + classid + "limit "+
					   (recordCount-pageSize * page)+","+(recordCount-pageSize * (page-1));
		   }
  } else if (!keyword.equals("")) {  //如果是查詢資料
	if (page == 1){
	  if (!classid.equals("")) {//查詢某一類
		 sqlStr = sqlStr + "and a.Bookclass='" +
		 classid + "' and (upper(a.bookname) like '%" +
		 keyword+ "%' or upper(a.content) like '%" +
		 keyword + "%')  order by a.Id desc";
	  } else {		//查詢所有類
		sqlStr = sqlStr + " and (upper(a.bookname) like '%" +
		keyword+ "%' or upper(a.content) like '%" +
		keyword + "%') order by a.Id desc";
	  }
	 } else {
	  if (!classid.equals("")){
		   sqlStr = sqlStr + " and a.Bookclass='" +
		   classid + "' and (upper(a.bookname) like '%" +
		   keyword+ "%' or upper(a.content) like '%" +
		   keyword + "%') limit "+(recordCount-pageSize * page)+","+
		   (recordCount-pageSize * (page-1));
	  } else {
			sqlStr = sqlStr + " and (upper(a.bookname) like '%" +
			keyword+ "%' or upper(a.content) like '%" +
			keyword + "%') limit "+(recordCount-pageSize * page)+","+
			(recordCount-pageSize * (page-1));
			}
			
						}
  } else {//非查詢，也非分類瀏覽
		 if (page == 1){
			  sqlStr = sqlStr + "  order by a.Id desc";
		  } else {
			  sqlStr = sqlStr + "limit "+(recordCount-pageSize * page)+","+
			  (recordCount-pageSize * (page-1));
		   }
		   System.out.println("sql="+sqlStr);
		}
		try  {
			  rs = stmt.executeQuery(sqlStr);
			  booklist = new Vector(rscount);
			  while (rs.next()){
								book book = new book();
								book.setId(rs.getLong("Id"));
								book.setBookName(rs.getString("BookName"));
								book.setBookClass(rs.getInt("BookClass"));
								book.setClassname(rs.getString("ClassName"));
								book.setAuthor(rs.getString("Author"));
								book.setPublish(rs.getString("Publish"));
								book.setBookNo(rs.getString("BookNo"));
								book.setContent(rs.getString("Content"));
								book.setPrince(rs.getFloat("Price"));
								book.setAmount(rs.getInt("Amount"));
								book.setLeav_number(rs.getInt("Leav_number"));
								book.setRegTime(rs.getString("RegTime"));
								book.setPicture(rs.getString("picture"));
								booklist.addElement(book);
								al.add(book);
						}
						rs.close();
						return true;
				}catch (Exception e){
						System.out.println(e.getMessage());
						return false;
				}
		}
		
		
		
		
		/**
		 * 完成圖書添加
		 * @return
		 * @throws java.lang.Exception
		 */
 public boolean insert() throws Exception {
 	 Date dd = new Date();
 	 
  sqlStr = "insert into book (Id,BookName,BookClass,Author,Publish,BookNo,"+
		   "Content,Price,Amount,Leav_number,RegTime,picture) values ('";
		   sqlStr=sqlStr+ abooks.getId()  + "','";
		   sqlStr = sqlStr + dataFormat.toSql(abooks.getBookName()) + "','";
		   sqlStr = sqlStr + abooks.getBookClass() + "','";
		   sqlStr = sqlStr + dataFormat.toSql(abooks.getAuthor()) + "','";
		   sqlStr = sqlStr + dataFormat.toSql(abooks.getPublish()) + "','";
		   sqlStr = sqlStr + dataFormat.toSql(abooks.getBookNo()) + "','";
		   sqlStr = sqlStr + dataFormat.toSql(abooks.getContent()) + "','";
		   sqlStr = sqlStr + abooks.getPrince() + "','";
		   sqlStr = sqlStr + abooks.getAmount() + "','";
		   sqlStr = sqlStr + abooks.getAmount() + "','";
		   sqlStr = sqlStr + dd  + "','";
		   sqlStr = sqlStr + abooks.getPicture()+"')";
				try{
						System.out.print("sqlStr insert="+sqlStr);
						DataBase db = new DataBase();
						//db.connect();
						//stmt =db.conn.createStatement ();
						stmt.execute(sqlStr);
						return true;
				}catch (SQLException sqle){
				  System.out.print(sqle.getMessage());
						return false;
				}
		}
		/**
		 * 完成圖書修改
		 * @return
		 * @throws java.lang.Exception
		 */
public boolean update() throws Exception {
  sqlStr = "update book set ";
  sqlStr = sqlStr + "BookName = '" + dataFormat.toSql(abooks.getBookName()) + "',";
  sqlStr = sqlStr + "BookClass = '" + abooks.getBookClass() + "',";
  sqlStr = sqlStr + "Author = '" + dataFormat.toSql(abooks.getAuthor()) + "',";
  sqlStr = sqlStr + "Publish = '" + dataFormat.toSql(abooks.getPublish()) + "',";
  sqlStr = sqlStr + "BookNo = '" + dataFormat.toSql(abooks.getBookNo()) + "',";
  sqlStr = sqlStr + "Content = '" + dataFormat.toSql(abooks.getContent()) + "',";
  sqlStr = sqlStr + "Price = '" + abooks.getPrince() + "',";
  sqlStr = sqlStr + "Amount = '" + abooks.getAmount() + "',";
  sqlStr = sqlStr + "Leav_number = '" + abooks.getLeav_number()+ "',";
  sqlStr = sqlStr + "picture = '" + abooks.getPicture() + "' ";
  sqlStr = sqlStr + "where Id = " + abooks.getId();
				try{
						DataBase db = new DataBase();
						//db.connect();
						//stmt =db.conn.createStatement ();
						stmt.execute(sqlStr);
						return true;
				} catch (SQLException e){
						System.out.print(e.getMessage());
						return false;
				}

		}
		/**
		 * 完成圖書刪除
		 * @param aid
		 * @return
		 * @throws java.lang.Exception
		 */
		public boolean delete( int aid ) throws Exception {
				sqlStr = "delete from book where Id = "  + aid ;
				try
				{         DataBase db = new DataBase();
						  //db.connect();
						  //stmt =db.conn.createStatement ();
						  stmt.execute(sqlStr);
						 return true;
				}
				catch (SQLException e)
				{
						System.out.println(e);
						return false;
				}
			  }
			  /**
			   * 完成圖書單本查詢，用于支持頁面的查看圖書詳細資料
			   * @param newid
			   * @return
			   * @throws java.lang.Exception
			   */
 public boolean getOnebook(int newid ) throws Exception {
	 DataBase db = new DataBase();
	 db.connect();
	 stmt =db.conn.createStatement ();
	 try {

		sqlStr = "select   a.Id,a.BookName,a.BookClass,b.ClassName,"+
				   "a.Author,a.Publish,a.BookNo,a.Content,a.Price,a.Amount,"+
				   "a.Leav_number,a.RegTime,a.picture from book a,bookclass b"+
				  " where a.BookClass = b.Id  and a.Id = "+ newid;
						rs = stmt.executeQuery(sqlStr);
						if (rs.next())
						{	booklist = new Vector(1);

							book book = new book();
							book.setId(rs.getLong("Id"));
							book.setBookName(rs.getString("BookName"));
							book.setBookClass(rs.getInt("BookClass"));
							book.setClassname(rs.getString("ClassName"));
							book.setAuthor(rs.getString("Author"));
							book.setPublish(rs.getString("Publish"));
							book.setBookNo(rs.getString("BookNo"));
							book.setContent(rs.getString("Content"));
							book.setPrince(rs.getFloat("Price"));
							book.setAmount(rs.getInt("Amount"));
							book.setLeav_number(rs.getInt("Leav_number"));
							book.setRegTime(rs.getString("RegTime"));
							book.setPicture(rs.getString("picture"));
							booklist.addElement(book);
						} else {
								rs.close();
								return false;
						}
						rs.close();
						return true;
				}
				catch (SQLException e)
				{
						return false;
				}
		}
		public int getPage() {				//顯示的頁碼
				return page;
		}
		public void setPage(int newpage) {
				page = newpage;
		}
		public int getPageSize(){			//每頁顯示的圖書數
				return pageSize;
		}
		public void setPageSize(int newpsize) {
				pageSize = newpsize;
		}
		public int getPageCount() {				//頁面總數
				return pageCount;
		}
		public void setPageCount(int newpcount) {
				pageCount = newpcount;
		}
		public int getRecordCount() {
				return recordCount;
		}
		public void setRecordCount(int newrcount) {
				recordCount= newrcount;
		}
		public int getCfirstpage()
		{
			return cfirstpage;
		}
		public void setCfirstpage(int n)
		{
			cfirstpage=n;
		}
	public int getFlag()
	{
		return flag;
	}
	public void setFlag(int n)
	{
		flag=n;
	}
  public op_book() {
	   }
}