package zhaobiao.db;

import java.util.*;
import java.sql.*;
import zhaobiao.db.*;
import zhaobiao.data.*;
/**
 * <p>Title: 招標(biāo)管理信息系統(tǒng)</p>
 * <p>Description: 招標(biāo)管理信息系統(tǒng)的前臺(tái)設(shè)計(jì)</p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: 招標(biāo)信息管理系統(tǒng)</p>
 * @author csk
 * @version 1.0
 */

public class NewProduct {

  public NewProduct() {
  }
  public static void main(String[] args) {
    NewProduct newProduct1 = new NewProduct();
  }

  /**添加一種產(chǎn)品
   *
   * @param pd（產(chǎn)對(duì)象實(shí)例）
   * @return 無(wú)
   */
  public void addProduct(Product pd)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    long maxid=0;
    try {
      PreparedStatement ps1 = con.prepareStatement("select max(product_id) as id from product");
      rs =ps1.executeQuery();
      if(rs.next() ){
        maxid = rs.getLong("id")+1 ;
      }
      else
        maxid=1;
      if(ps1!=null)
        ps1.close();
    }
    catch (SQLException ex) {
      ex.printStackTrace() ;
    }
    String sql="insert into product(product_id,product_name,type_id,product_content,maker_id)values(?,?,?,?,?)";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,maxid);
      ps.setString(2,pd.getProduct_name()) ;
      ps.setLong(3,pd.getType_id());
      ps.setString(4,pd.getProduct_content());
      ps.setLong(5,pd.getMaker_id());
      ps.executeUpdate();
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
  }

  /**根據(jù)產(chǎn)品id刪除該產(chǎn)品，其中在刪除該產(chǎn)品時(shí)頁(yè)刪除ppm表中與該產(chǎn)品相關(guān)的記錄
   *
   * @param pdid(產(chǎn)品id)
   * @return 無(wú)
   */
  public void delProductid(long pdid)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="delete from product where product_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,pdid);
      ps.executeUpdate();
      //刪除所有的ppm表中的該產(chǎn)品記錄
      NewPPM newppm=new NewPPM();
      newppm.delPPMProductid(pdid);
      newppm.freeCon();
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  /**更新一個(gè)產(chǎn)品信息
   * @param Product pd(產(chǎn)品對(duì)象實(shí)例)
   * @return 無(wú)
   */

  public void upateProduct(Product pd)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="update product set product_name=?,type_id=?,product_content=?,maker_id=? where product_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setString(1,pd.getProduct_name());
      ps.setLong(2,pd.getType_id());
      ps.setString(3,pd.getProduct_content());
      ps.setLong(4,pd.getMaker_id());
      ps.setLong(5,pd.getProduct_id());

      ps.executeUpdate();
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
  }

  /**根據(jù)類別id刪除產(chǎn)品表中的有關(guān)產(chǎn)品
   *
   * @param typeid（類別id）
   * @return 無(wú)
   */
  public void delProductUpid(long typeid)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from product where type_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,typeid);
      rs=ps.executeQuery();
      while(rs.next())
      {
        delProductid(rs.getLong("product_id"));//調(diào)用刪除產(chǎn)品方法
      }
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
  }

  /**根據(jù)廠商id刪除產(chǎn)品表中的有關(guān)產(chǎn)品
   *
   * @param long mkid(廠商id)
   * @return 無(wú)
   */
  public void delProductMakerid(long mkid)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from product where maker_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,mkid);
      rs=ps.executeQuery();
      while(rs.next())
      {
        delProductid(rs.getLong("product_id"));//調(diào)用刪除產(chǎn)品方法
      }
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
  }

  /**根據(jù)廠商id獲得屬于該廠商的所有id
   *
   * @param maker_id
   * @return
   */
  public Vector getProductMakerid(long maker_id)
  {
    Basic basic=new Basic();
    Vector list=new Vector();
    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())
      {
        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);
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }

    freeCon();
    return list;
  }

  /**
   * 釋放數(shù)據(jù)庫(kù)資源<p>
   *PreparedStatement和ResultSep將關(guān)閉，Connection返回給連接池
   * @param      無(wú)
   * @repurn     無(wú)
   * @exception  SQLException
   *
   */
  public void freeCon(){
    try {
      if (rs!=null)
        rs.close() ;
      if (ps!=null)
        ps.close() ;
    }
    catch (SQLException ex) {
    }
    if (db!=null)
      db.freeConnection("idb",con) ;
  }

  private PreparedStatement ps=null;
  private DBConnectionManager db;
  private Connection con=null;
  private ResultSet rs=null;
}