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 NewPrice {

  public NewPrice() {
  }

  /**添加一條標價記錄
   *
   * @param price（price對象實例）
   * @return 無
   */

  public void addPrice(Price price) {
    db = DBConnectionManager.getInstance();
    con = db.getConnection("idb");
    long maxid = 0;
    try {
      PreparedStatement ps1 = con.prepareStatement(
          "select max(price_id) as id from price");
      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 price(price_id,t1,t2,t3,t4,t5,t6,t7,t8)values(?,?,?,?,?,?,?,?,?)";
    try {
      ps = con.prepareStatement(sql);
      ps.setLong(1, maxid);
      ps.setString(2, price.getDevice_price());
      ps.setString(3, price.getTool_price());
      ps.setString(4, price.getService_price());
      ps.setString(5, price.getOptional_price());
      ps.setString(6, price.getRePro_price());
      ps.setString(7, price.getInter_price());
      ps.setString(8, price.getEnsure_price());
      ps.setString(9, price.getDeliver_time());
      ps.executeUpdate();
    }
    catch (SQLException ex) {
      ex.printStackTrace();

    }
    freeCon();

  }

  /**根據標價id刪除該標價記錄
   *
   * @param price_id
   * @return 無
   */
  public void delPrice(long price_id) {
    db = DBConnectionManager.getInstance();
    con = db.getConnection("idb");
    String sql = "delete from price where price_id=?";
    try {
      ps = con.prepareStatement(sql);
      ps.setLong(1, price_id);
      ps.executeUpdate();
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    freeCon();
  }

  /**更新一條標價記錄
   *
   * @param price
   * @return 無
   */

  public void updatePrice(Price price) {
    db = DBConnectionManager.getInstance();
    con = db.getConnection("idb");
    String sql =
        "update price set t1=?,t2=?,t3=?,t4=?,t5=?,t6=?,t7=?,t8=? where price_id=?";
    try {
      ps = con.prepareStatement(sql);

      ps.setString(1, price.getDevice_price());
      ps.setString(2, price.getTool_price());
      ps.setString(3, price.getService_price());
      ps.setString(4, price.getOptional_price());
      ps.setString(5, price.getRePro_price());
      ps.setString(6, price.getInter_price());
      ps.setString(7, price.getEnsure_price());
      ps.setString(8, price.getDeliver_time());
      ps.setLong(9, price.getPrice_id());
      ps.executeUpdate();
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    freeCon();
  }

  /**獲得標價表中的最大id（maxid）
   * @param 無
   * @return long
   */
  public long getMaxid() {
    db = DBConnectionManager.getInstance();
    con = db.getConnection("idb");
    long maxid = 0;

    try {
      PreparedStatement ps1 = con.prepareStatement(
          "select max(price_id) as id from price");
      rs = ps1.executeQuery();
      if (rs.next()) {
        maxid = rs.getLong("id");
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    freeCon();
    return maxid;

  }

  public static void main(String[] args) {
    NewPrice newPrice1 = new NewPrice();
  }

  /**
   * 釋放數據庫資源<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;
}