package jnestore.javabeans;

import javax.sql.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;

import jnestore.javabeans.*;

/** 
 *Catalog類負責從數據庫中獲得商品信息
 */

public class Catalog {	 
    
    int rowsPerPage = 3;
    DataAccess dBean = new DataAccess();
    // 依據商品庫存編號（SKU）查詢商品
    public ItemInfo getItem(String sku) {
    	
    	ItemInfo item = new ItemInfo();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pStatement = null;

        try {
            //獲得數據庫連接
            con = dBean.getConnection();
            String sql = "SELECT * FROM CATALOG_ITEMS WHERE SKU = ?";
            //生成PrepatedStatement對象 
            pStatement = con.prepareStatement(sql);
            pStatement.setString(1, sku);
            //通過pStatement執行查詢，返回結果集對象rs
            rs = pStatement.executeQuery();
            //將結果集中數據信息封裝到ItemInfo對象中
            while (rs.next()) {
                item.setSku(rs.getString("SKU"));
                item.setName(rs.getString("NAME"));
                item.setDescription(rs.getString("DESCRIPTION"));
                item.setPrice(rs.getFloat("PRICE"));
            }

        } catch (Exception e) {
            System.out.println("Error retrieving SKU: " + sku);
        } finally {
            try {
                rs.close();
                pStatement.close();
                con.close();
            } catch (Exception ex) {
                System.out.println("Error closing database");
            }
            return item;           
       
         }
  }  

    //取得所有商品信息，以ArrayList形式返回
    public ArrayList getAllItems() {         
        ArrayList items = new ArrayList();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pStatement = null;

        try {
            //獲得數據庫連接
            con = dBean.getConnection();

            String sql = "SELECT * FROM CATALOG_ITEMS";
            //生成PrepatedStatement對象
            pStatement = con.prepareStatement(sql);
            //通過pStatement執行查詢，返回結果集對象rs
            rs = pStatement.executeQuery();
            //遍歷結果集，將每一行的數據封裝到一個ItemInfo對象中，ItemInfo對象
            //被存儲到集合items中
            while (rs.next()) {
                ItemInfo item = new ItemInfo();
                item.setSku(rs.getString("SKU"));
                item.setName(rs.getString("NAME"));
                item.setDescription(rs.getString("DESCRIPTION"));
                item.setPrice(rs.getFloat("PRICE"));

                items.add(item);
            }

        } catch (Exception e) {
            System.out.println("Error retrieving catalog items");
        } finally {
            try {
                rs.close();
                pStatement.close();
                con.close();
            } catch (Exception ex) {
                System.out.println("Error closing database");
            }
            return items;
          }
    }
       //返回catalog_items表中記錄數
       private  int getAvailableCount()throws Exception
	{
		 Connection con = null;
		 Statement stmt = null;
		  ResultSet rs = null;
		  int ret =0;
		try {
		 con = dBean.getConnection();		
		 stmt=con.createStatement();
		String strSql="select count(*) from catalog_items";
		rs=stmt.executeQuery(strSql);
		while(rs.next())
		{
			ret=rs.getInt(1);
		}
	    } catch (Exception e) {
            System.out.println("Error ");
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch (Exception ex) {
                System.out.println("Error closing database");
            }
		return ret;		
	 }
  }
	//根據總行數計算總頁數
	private int countTotalPage(int totalRows) {     
          if (totalRows % this.rowsPerPage==0){
           return totalRows/this.rowsPerPage;
         }else{
          return totalRows/this.rowsPerPage + 1;        
        }
      }
	//返回存儲了指定頁數據的PageDataBean對象
        public PageDataBean getPageData(String page)throws Exception
	{
	       DataAccess dBean = new DataAccess();
	       Connection con = dBean.getConnection();
	       Statement stmt = null;
	       ResultSet rs = null;
		int totalRows = getAvailableCount();//獲得表中總行數
		int totalPages =  countTotalPage(totalRows);//獲得總頁數
		ArrayList data = new ArrayList();
		
		PageDataBean pageBean=new PageDataBean();
			
		try
		{
			
			int pageNum=Integer.parseInt(page);
			 stmt=con.createStatement();
			
			String strSql="select *   from catalog_items order by sku limit " + (pageNum-1)*rowsPerPage
			 +"," + rowsPerPage;//查詢page頁所對應的數據
			 rs=stmt.executeQuery(strSql);
	
			while(rs.next())
			{				
					ItemInfo item = new ItemInfo();	
					 item.setSku(rs.getString("SKU"));
                                          item.setName(rs.getString("NAME"));
                                         item.setDescription(rs.getString("DESCRIPTION"));
                                          item.setPrice(rs.getFloat("PRICE")); 
					 data.add(item);				
			}			
			
			pageBean.setCurPage(pageNum);
			pageBean.setData(data);//將page頁對應的數據封裝到pageBean對象中
			pageBean.setTotalPage(totalPages);			
			
		}
		catch (Exception e) {
                 System.out.println("Error ");
           } finally {
               try {
                  rs.close();
		  stmt.close();
                  con.close();
                } catch (Exception ex) {
                   System.out.println("Error closing database");
                }
	     return pageBean;	
	   }
	}


}