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

public class op_cd extends DataBase{
  private cd acds = new cd();	                       //新的CD類
        private javax.servlet.http.HttpServletRequest request; //建立頁(yè)面請(qǐng)求
        private boolean sqlflag = true ;		    //對(duì)接收到的數(shù)據(jù)是否正確
        private Vector cdlist;			    //顯示CD列表向量數(shù)組
        private int page = 1;				       //顯示的頁(yè)碼
        private int pageSize=10;				//每頁(yè)顯示的CD數(shù)
        private int pageCount =0;				//頁(yè)面總數(shù)
        private long recordCount =0;			        //查詢的記錄總數(shù)
        public String sqlStr="";
        public Vector getcdlist() {
                return cdlist;
        }
        public boolean getSqlflag() {
                return sqlflag;
        }
        public String to_String( String str) {
                try
                {
                        return new String(str.getBytes("ISO8859-1"));
                }
                catch (Exception e)
                {
                        return str;
                }
        }
        /**
         * 將頁(yè)面表單傳來(lái)的資料分解
         */
public boolean getRequest(javax.servlet.http.HttpServletRequest newrequest) {
  boolean flag = false;
  try{
   request = newrequest;
   String ID = request.getParameter("id");
   long cdid = 0;
   try{
           cdid = Long.parseLong(ID);
   }catch (Exception e){
   }
   acds.setId(cdid);
   String cdname = request.getParameter("cdname");
   if (cdname==null || cdname.equals(""))
   {
           cdname = "";
           sqlflag = false;
   }
   acds.setcdName(to_String(cdname));
   String Singer = request.getParameter("Singer");
   if (Singer==null || Singer.equals(""))
   {
           Singer = "";
           sqlflag = false;
   }
   acds.setSinger(to_String(Singer));
   String publish = request.getParameter("publish");;
   if (publish==null)
   {
           publish = "";
   }
   acds.setPublish(to_String(publish));
   String cdclass = request.getParameter("cdclass");
   int bc = Integer.parseInt(cdclass);
   acds.setcdClass(bc);
   String cdno = request.getParameter("cdno");
   if (cdno == null)
   {
           cdno = "";
   }
   acds.setcdNo(to_String(cdno));
   String picture = request.getParameter("picture");
   if (picture == null)
   {
           picture = "images/01.gif";
   }
   acds.setPicture(to_String(picture));
   float price;
   try	{
           price =new Float(request.getParameter("price")).floatValue();
   } catch (Exception e){
           price = 0;
           sqlflag = false;
   }
   acds.setPrice(price);
   int amount;
   try{
           amount = new Integer(request.getParameter("amount")).intValue();
   }catch (Exception e){
           sqlflag = false;
           amount = 0;
   }
   acds.setAmount(amount);
   String musiclist = request.getParameter("musiclist");
   if (musiclist == null)
   {
           musiclist = "";
   }
   acds.setmusiclist(to_String(musiclist));
   if (sqlflag)
   {
           flag = true;
   }
   return flag;
                }catch (Exception e){
   return flag;
                }
        }
        /**
         * 獲得查詢CD類別的sql語(yǔ)句
         * @return
         */
public String getSql() {
   sqlStr = "select id,classname from cd order by id";
   return sqlStr;
        }
        /**
         * 完成CD查詢，包括分類，分頁(yè)查詢
         * @param res
         * @return
         * @throws java.lang.Exception
         */
public boolean cd_search(HttpServletRequest res) throws Exception {
   DataBase db = new DataBase();
   db.connect();
   stmt = db.conn.createStatement ();
   request = res;
   String PAGE = request.getParameter("page");   //頁(yè)碼
   String classid = request.getParameter("classid");	//分類ID號(hào)
   String keyword = request.getParameter("keyword");	//查詢關(guān)鍵詞
   if (classid==null) classid="";
   if (keyword==null) keyword = "";
   keyword = to_String(keyword).toUpperCase();
   try {
           page = Integer.parseInt(PAGE);
   }catch (NumberFormatException e){
           page = 1;
   }
   //取出記錄數(shù)
   if (!classid.equals("") && keyword.equals("") ) {
           sqlStr = "select count(*) from cd where cdclass='"+classid + "'";
      }
  else if (!keyword.equals("")) {
      if (classid.equals("")){
           sqlStr = "select count(*) from cd where upper(cdname) like '%" +
                    keyword+ "%' or musiclist like '%" + keyword + "%'";
      } else {
          sqlStr = "select count(*) from cd where cdclass='" + classid
                   + "' and  (upper(cdname) like '%" +keyword+ "%' or "+
                   "musiclist like '%" + keyword + "%')";
           }
   } else {
           sqlStr = "select count(*) from cd";
          }
   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;
   }
   //設(shè)定有多少pageCount
   if (recordCount < 1)
            pageCount = 0;
   else
            pageCount = (int)(recordCount - 1) / pageSize + 1;
   //檢查查看的頁(yè)面數(shù)是否在范圍內(nèi)
   if (page < 1)
            page = 1;
  else if (page > pageCount)
            page = pageCount;
            rscount = (int) recordCount % pageSize;	 // 最后一頁(yè)記錄數(shù)
   //sql為倒序取值
   sqlStr = "select  a.id,a.cdname,a.cdclass,b.classname,"+
           "a.Singer,a.publish,a.cdno,a.musiclist,a.Price,a.amount,"+
           "a.Leav_number,a.regtime,a.picture from cd a,cdclass b"+
          " where a.cdclass = b.Id ";
  if (!classid.equals("") && keyword.equals("") ){  //如果類別不為空，非查詢
           if (page == 1)
           {
               sqlStr = sqlStr + " and a.cdclass='" + classid + "' "+
               "order by a.Id desc";
           } else {
               sqlStr = "select top "+ pageSize+" from (select top "+(recordCount-pageSize * (page-1)) +"from ("+sqlStr + " and a.cdclass='" + classid + "'))";

           }
  } else if (!keyword.equals("")) {  //如果是查詢資料
    if (page == 1){
      if (!classid.equals("")) {//查詢某一類
         sqlStr = sqlStr + "and a.cdclass='" +
         classid + "' and (upper(a.cdname) like '%" +
         keyword+ "%' or a.musiclist like '%" +
         keyword + "%')  order by a.Id desc";
      } else {		//查詢所有類
        sqlStr = sqlStr + " and (upper(a.cdname) like '%" +
        keyword+ "%' or a.musiclist like '%" +
        keyword + "%') order by a.Id desc";
      }