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

  public Search() {
  }

  /**  對項目的搜索
   *
   * @param page
   * @param pagemax
   * @param keys 搜索關鍵字
   * @return Vector項目集合
   */
  public Vector searchProject(long page ,long pagemax,String keys)
  {
    page=page-1;
    Vector list=new Vector();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    String sql="select *from project where project_name like ? or project_cost like ? or project_company like ? or project_time like ? or project_content like ?";

    try {
      ps=con.prepareStatement(sql);
      ps.setString(1,"%"+keys+"%");
      ps.setString(2,"%"+keys+"%");
      ps.setString(3,"%"+keys+"%");
      ps.setString(4,"%"+keys+"%");
      ps.setString(5,"%"+keys+"%");
      rs=ps.executeQuery();


      int count=1;
      while(rs.next())
      {
        if (count>(page*pagemax)&&count<=(page*pagemax+pagemax)){
          Project pj=new Project();
          pj.setProject_id(rs.getLong("project_id"));
          pj.setProject_name(rs.getString("project_name"));
          pj.setProject_time(rs.getString("project_time"));
          pj.setProject_company(rs.getString("project_company"));
          pj.setProject_cost(rs.getString("project_cost"));
          pj.setProject_content(rs.getString("project_content"));
          list.add(pj);
        }
        count++;
      }
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
    return list;
  }

  /**  對產品的搜索
   * @param page
   * @param pagemax
   * @param keys 搜索關鍵字
   * @return Vector產品集合
   */
  public Vector searchProduct(String keys)
  {
    Vector list=new Vector();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
    keys="%"+keys+"%";
    String sql="select *from product where product_name like ? or product_content like ? ";
    try {
      ps=con.prepareStatement(sql);
      ps.setString(1,keys);
      ps.setString(2,keys);
      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("product_content");
        list.add(pd);
      }
    }
    catch (SQLException ex) {
      freeCon();
      ex.printStackTrace();
    }
    freeCon();
    return list;

  }

  /**  對廠商的搜索
   * @param page
   * @param pagemax
   * @param keys 搜索關鍵字
   * @return Vector廠商集合
   */
  public Vector searchMaker(String keys)
  {
    Vector list=new Vector();
    db=DBConnectionManager.getInstance();
    con=db.getConnection("idb");
     keys="%"+keys+"%";
    String sql="select *from maker where maker_name like ? or maker_address like ? or maker_cr_address like ?"+
               " or maker_cr_time like ? or maker_cr_cost like ? or maker_fr_name like ? or maker_fr_position like ?"+
               " or maker_relation like ? or maker_db_content like ?";
    try {
      ps=con.prepareStatement(sql);
      ps.setString(1,keys);
      ps.setString(2,keys);
      ps.setString(3,keys);
      ps.setString(4,keys);
      ps.setString(5,keys);
      ps.setString(6,keys);
      ps.setString(7,keys);
      ps.setString(8,keys);
      ps.setString(9,keys);
      rs=ps.executeQuery();
      while(rs.next())
      {
        Maker mk=new Maker();
        mk.setMaker_id(rs.getLong("maker_id"));
        mk.setMaker_name(rs.getString("maker_name"));
        mk.setMaker_address(rs.getString("maker_address"));
        mk.setMaker_cr_address(rs.getString("maker_cr_address"));
        mk.setMaker_cr_time(rs.getString("maker_cr_time"));
        mk.setMaker_cr_address(rs.getString("maker_cr_address"));
        mk.setMaker_cr_cost(rs.getString("maker_cr_cost"));
        mk.setMaker_fr_name(rs.getString("maker_fr_name"));
        mk.setMaker_fr_position(rs.getString("maker_fr_position"));
        mk.setMaker_attribute(rs.getString("maker_attribute"));
        mk.setMaker_relation(rs.getString("maker_relation"));
        mk.setMaker_db_content(rs.getString("maker_db_content"));
        list.add(mk);
      }
    }
    catch (SQLException ex) {
      freeCon();
      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) {
    Search search1 = new Search();
  }
  private DBConnectionManager db;
  private Connection con=null;
  private PreparedStatement ps=null;
  private ResultSet rs=null;
}