package com.accp.gz.th.zm.server.dao;

import java.util.ArrayList;
import java.sql.*;
import com.accp.gz.th.zm.server.entity.Goods;
import com.accp.gz.th.zm.client.javabean.PageBean;

/**
 * Goods表的處理類
 */
public class GoodsDAO {
    /**
     * 查找所有記錄
     */
    public ArrayList select() {

        ArrayList list = new ArrayList();

        Connection conn = ConnectionManager.getConn();
        Statement stmt =null;
        ResultSet rs =null;

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from Goods");
            while(rs.next()){
                Goods g=new Goods();
                g.setId(rs.getInt(1));
                g.setName(rs.getString(2));
                g.setPrice(rs.getInt(3));
                g.setNumber(rs.getInt(4));
                list.add(g);
            }
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }finally{
            ConnectionManager.Close(rs);
            ConnectionManager.Close(stmt);
            ConnectionManager.Close(conn);
        }

        return list;
    }

    /*** 方法 2 ： 根據SQL語句統計記錄總數 ***/
    public static int getTotalRows(String selectSql) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int count = 0;

        conn = ConnectionManager.getConn();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(selectSql);
            while (rs.next()) {
                count++;
            }
        } catch (SQLException ex) {
        } finally {
            ConnectionManager.Close(conn);
            ConnectionManager.Close(stmt);
            ConnectionManager.Close(rs);
            return count;
        }
    }


    /*** 方法 3 ： 根據 請求的頁面bean、查詢語句 進行商品記錄的分頁查詢 ***/
    public ArrayList select( PageBean pageBean ){

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList vec = new ArrayList();

        int queryPageNo = pageBean.getQueryPageNo(); // 獲得請求到達的頁號
        int rowsPerPage = pageBean.getRowsPerPage(); // 獲得每頁的行數
        String selectSql = "select * from Goods"; // 設置默認的查詢語句
        if( pageBean.getQuerySql()!= null )
             selectSql = pageBean.getQuerySql();  // 如果有查詢語句，則用指定的查詢語句

        // 統計總行數
        int totalRows = getTotalRows(selectSql);
        pageBean.setTotalRows(totalRows);
        // 統計總頁數
        int totalPage = totalRows % rowsPerPage == 0 ? totalRows / rowsPerPage :
                        totalRows / rowsPerPage + 1;
        pageBean.setTotalPage(totalPage);
        // 統計最后一頁的行數
        int lastPageRows = totalRows % rowsPerPage == 0 ? rowsPerPage :
                           totalRows % rowsPerPage;
        pageBean.setLastPageRows(lastPageRows);

        conn = ConnectionManager.getConn();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(selectSql);
            //rs = stmt.executeQuery("select * from Goods");

            // 跳過前面所有頁的行
            int skipRows = (queryPageNo - 1) * rowsPerPage;
            for (int i = 0; i < skipRows; i++)
                rs.next();

            int count = rowsPerPage;
            while ( rs!=null && rs.next() && count>0) {
                Goods g = new Goods();
                g.setId(rs.getInt(1));
                g.setName(rs.getString(2));
                g.setPrice(rs.getInt(3));
                g.setNumber(rs.getInt(4));
                vec.add(g);
                count--;
            }
        } catch (SQLException ex) {
        }
        finally{
            ConnectionManager.Close(conn);
            ConnectionManager.Close(stmt);
            ConnectionManager.Close(rs);
        }

        return vec;
    }

}