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

public class op_book extends DataBase{
  private book abooks = new book();	                       //新的圖書類
        private javax.servlet.http.HttpServletRequest request; //建立頁面請求
        private boolean sqlflag = true ;		    //對接收到的數據是否正確
        private Vector booklist;			    //顯示圖書列表向量數組
        private int page = 1;				       //顯示的頁碼
        private int pageSize=10;				//每頁顯示的圖書數
        private int pageCount =0;				//頁面總數
        private long recordCount =0;			        //查詢的記錄總數
        public String sqlStr="";
        public Vector getBooklist() {
                return booklist;
        }
        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");
   long bookid = 0;
   try{
           bookid = Long.parseLong(ID);
   }catch (Exception e){
   }
   abooks.setId(bookid);
   String bookname = request.getParameter("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);
   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 {
   DataBase db = new DataBase();
   Connection conn=db.connect();
   Statement stmt = 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);
           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.prince,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 + "'";
           }
  } 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 {		//查詢所有類