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 csk
 * @version 1.0
 */

public class NewPPM {

  public NewPPM() {
  }
  public static void main(String[] args) {
    NewPPM newPPM1 = new NewPPM();
  }
  /**添加一個PPM記錄
   * @param ppm
   * @return 無
   */

  public void addPPM(PPM ppm)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    long maxid=0;
    try {
      PreparedStatement ps1 = con.prepareStatement("select max(id) as id from ppm");
      rs =ps1.executeQuery();
      if(rs.next() ){
        maxid = rs.getLong("id")+1 ;
      }
      else
        maxid=1;
      if(ps1!=null)
        ps1.close();
    }
    catch (SQLException ex) {
      ex.printStackTrace() ;
    }
    long zhongbiao=0;
    String sql="insert into ppm(id,project_id,product_id,maker_id,zhongbiao,price_id)values(?,?,?,?,?,?)";
    if(ppm.isZhongbiao())
      zhongbiao=1;
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,maxid);
      ps.setLong(2,ppm.getProject_id());
      ps.setLong(3,ppm.getProduct_id());
      ps.setLong(4,ppm.getMaker_id() );
      ps.setLong(5,zhongbiao);
      ps.setLong(6,ppm.getPrice_id());
      ps.executeQuery();
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
  }

  /**根據ppmid刪除該PPM記錄,在刪除該ppm記錄時，會刪除與之對應的price記錄
   *
   * @param id
   * @return 無
   */
  public void delPPMid(long id)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    ResultSet rs1=null;
    String sql1="select * from ppm where id=?";
    try {
      PreparedStatement ps1=con.prepareStatement(sql1);
      ps1.setLong(1,id);
      rs1=ps1.executeQuery();
      if(rs1.next())
      {
        NewPrice newprice=new NewPrice();
        try {
          newprice.delPrice(rs1.getLong("price_id"));//刪除與之對應的price記錄
        }
        catch (SQLException ex) {
          ex.printStackTrace();
        }
        newprice.freeCon();
      }
      if(rs1!=null)
        rs1.close();
      if(ps1!=null)
        ps1.close();
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    String sql="delete from ppm where id=?";
    try {
      PreparedStatement ps2=con.prepareStatement(sql);
      ps2.setLong(1,id);
      ps2.executeUpdate();
      if(ps2!=null)
        ps2.close();
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  /** 根據項目id刪除該PPM記錄
   *@param long pjid(項目id)
   *
   */
  public void delPPMProjectid(long pjid)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from ppm where project_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,pjid);
      rs=ps.executeQuery();
      while(rs.next())
      {
        delPPMid(rs.getLong("id"));//調用delPPMid（long ppmid）
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    freeCon();
  }

  /**根據產品id刪除該ppm記錄
   * @param pdid(產品id)
   */
  public void delPPMProductid(long pdid)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from ppm where product_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,pdid);
      rs=ps.executeQuery();
      while(rs.next())
      {
        delPPMid(rs.getLong("id"));//調用delPPMid（long ppmid）
      }
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
  }

  /** 根據廠商id刪除ppm記錄
   *@param long mkid（廠商id）
   * @reutn 無
   *
   */
  public void delPPMMakerid(long mkid)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from ppm where maker_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,mkid);
      rs=ps.executeQuery();
      while(rs.next())
      {
        delPPMid(rs.getLong("id"));
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
      freeCon();
    }
    freeCon();

  }

  /**更新一條ppm記錄
   *
   * @param ppm對象實例
   * @return 無
   */
  public void updatePPM(PPM ppm)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    long zhongbiao=0;
    String sql="update ppm set project_id=?,product_id=?,maker_id=?,zhongbiao=?,price_id=? where id=?";
    if(ppm.isZhongbiao())
      zhongbiao=1;
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,ppm.getProject_id() );
      ps.setLong(2,ppm.getProduct_id());
      ps.setLong(3,ppm.getMaker_id());
      ps.setLong(4,zhongbiao);
      ps.setLong(5,ppm.getPrice_id());
      ps.setLong(6,ppm.getId());
      ps.executeUpdate();
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    freeCon();
  }

  /**根據項目id和類型id刪除ppm集合
   *
   * @param pjid（項目id）
   * @param typeid（類型id）
   */
  public void delPPMProjectidTypeid(long pjid,long typeid)
  {
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from ppm where project_id=?";
    long ppmid;
    Basic basic=new Basic();
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,pjid);
      rs=ps.executeQuery();
      while(rs.next())
      {
        ppmid=rs.getLong("id");
        Product pd=new Product();
        pd=basic.getProduct(rs.getLong("product_id"));
        //如果產品屬于該類別，則進行刪除之
        if(pd.getType_id()==typeid)
        {
          delPPMid(ppmid);
        }
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    freeCon();
  }

  /**  根據項目id和產品id和廠商id得到相關的PPM
   * @param long project_id(項目id)
   * @param long product_id(產品id)
   * @param long maker_id(廠商id)
   * @return PPM對象
   */

  public PPM getPPMList(long project_id,long product_id,long maker_id)
  {
    PPM ppm=new PPM();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    Basic basic=new Basic();
    String sql="select *from ppm where project_id=? and product_id=? and maker_id=?";
    try {
      ps=con.prepareStatement(sql);
      ps.setLong(1,project_id);
      ps.setLong(2,product_id);
      ps.setLong(3,maker_id);
      rs=ps.executeQuery();
      if(rs.next())
      {
        ppm=basic.getPPM(rs.getLong("id"));

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

  }


  /**
   * 釋放數據庫資源<p>
   *PreparedStatement和ResultSep將關閉，Connection返回給連接池
   * @param      無
   * @repurn     無
   * @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;
}