package zhaobiao.db;

import java.util.*;
import java.sql.*;
import zhaobiao.db.*;
import zhaobiao.data.*;

/**
 * <p>Title: 產品信息管理類</p>
 * <p>Description: 有關產品的瀏覽操作</p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * @author unascribed
 * @version 1.0
 */

public class ProductManager {

  public ProductManager() {
  }

  /**  獲得所有的類別集合
   * @param 無
   * @return Vector類別集合
   */
  public Vector getTypeList()
  {
    Vector list=new Vector();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from type";
    try {
      ps=con.prepareStatement(sql);
      rs=ps.executeQuery();
      while(rs.next())
      {
        Type type=new Type();
        type.setType_id(rs.getLong("type_id"));
        type.setType_name(rs.getString("type_name"));
        type.setType_content(rs.getString("type_content"));
        list.add(type);
      }
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
    return list;

  }

  /**  根據類別id，獲得類別下的所有產品集合
   * @param long page
   * @param long pagemax
   * @param long type_id
   * @return Vector產品集合
   */

  public  Vector getProductList(long page ,long pagemax,long type_id)
  {
    page=page-1;
    Vector list=new Vector();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from product where type_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,type_id) ;
      rs=ps.executeQuery();
      int count=1;
      while(rs.next())
      {
        if (count>(page*pagemax)&&count<=(page*pagemax+pagemax)){
          Product pd=new Product();
          pd.setProduct_id(rs.getLong("product_id"));
          pd.setProduct_name(rs.getString("product_name"));
          pd.setType_id(rs.getLong("type_id"));
          pd.setProduct_content(rs.getString("product_content"));
          pd.setMaker_id(rs.getLong("maker_id"));
          list.add(pd);
        }
        count++;
      }
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
    return list;
  }

  /**  根據類別id，獲得類別下的所有產品集合的數目
   * @param long type_id（類別id）
   * @return long 類別下的所有產品集合的數目
   */
  public long getProductListCount(long type_id)
  {
    long count=0;
    Vector list=new Vector();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from product where type_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,type_id) ;
      rs=ps.executeQuery();

      while(rs.next())
      {
        count++;
      }
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }

    freeCon();
    return count;
  }

  /**根據該產品id獲得PPM表中的項目集合（去掉PPM表中重復的project_id
   * @param long page
   * @param long pagemax
   * @param long product_id(產品id)
   * @return Set 產品id相關的PPM表中的項目集合
   */

  public Set getProjectList(long page ,long pagemax,long product_id)
  {
    page=page-1;
    Set set1=new TreeSet();
    Set set2=new TreeSet();
    Basic basic=new Basic();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from ppm where product_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,product_id);
      rs=ps.executeQuery();
      int count=1;
      while(rs.next())
      {
        long projectid;
        Project pj=new Project();
        projectid=rs.getLong("project_id");
        pj=basic.getProject(projectid);
        if(set1.add(pj))
        {
          if (count>(page*pagemax)&&count<=(page*pagemax+pagemax))
          {
            set2.add(pj);;
          }
          count++;          //計數器加1
        }
      }

    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    basic.freeCon();
    freeCon();
    return set2;
  }

  /**  根據該產品id獲得PPM表中的項目集合數量（去掉PPM表中重復的project_id）
   * @param long product_id（產品id）
   * @return long 產品id獲得PPM表中的項目集合數量
   */

  public long getProjectListCount(long product_id)
  {
    Set set1=new TreeSet();
    long count=0;
    Basic basic=new Basic();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from ppm where product_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,product_id);
      rs=ps.executeQuery();
      while(rs.next())
      {
        long projectid;
        Project pj=new Project();
        projectid=rs.getLong("project_id");
        pj=basic.getProject(projectid);
        if(set1.add(pj))
        {
          count++;          //計數器加1
        }
      }

    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    basic.freeCon();
    freeCon();
    return count;
  }

  /** 根據廠商id獲的該廠商的所有產品的集合
   *
   * @param maker_id（廠商id）
   * @return Vector該廠商的所有產品的集合
   */

  public Vector getProductMakerid(long maker_id)
  {
    Basic basic=new Basic();
    Vector list=new Vector();
    Product pd=new Product();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from product where maker_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,maker_id);
      rs=ps.executeQuery();
      while(rs.next())
      {
        pd.setProduct_id(rs.getLong("product_id"));
        pd.setProduct_name(rs.getString("product_name"));
        pd.setType_id(rs.getLong("type_id"));
        pd.setProduct_content(rs.getString("product_content"));
        pd.setMaker_id(rs.getLong("maker_id"));
        list.add(pd);
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }

    freeCon();
    return list;
  }

  /**
   * 釋放數據庫資源<p>
   *PreparedStatement和ResultSep將關閉，Connection返回給連接池
  
   */
  public void freeCon(){
    try {
      if (rs!=null)
        rs.close() ;
      if (ps!=null)
        ps.close() ;
    }
    catch (SQLException ex) {
    }
    if (db!=null)
      db.freeConnection("idb",con) ;
  }

  public static void main(String[] args) {
    ProductManager productManager1 = new ProductManager();
  }
  private DBConnectionManager db;
  private Connection con=null;
  private ResultSet rs=null;
  private PreparedStatement ps=null;
}