import java.sql.*;
import java.util.*;

public class busSearch {
  //private String station_Name;//公交站點名
  //private int line_Number;//公交車次號
  String databasename = "jnbus"; //數(shù)據(jù)源名
  connectDB conndb = new connectDB(); //聲明連接數(shù)據(jù)庫類的一個對象
  public busSearch() {
  }

  /////////////////////////////////////////////////////////////////////////////
  public int countStation(String startStation, String endStation,
                          int line_number) {
    //功能：在某一個車次，查詢開始站和終點站之間的站點數(shù)(可直達的站點)
    //返回值：整形，站點數(shù)
    conndb.connectDb(databasename);
    int countStation = 0; //記錄站點數(shù)
    int startnum = 1, endnum = 1; //開始站點序號，終止站點序號，并初始化為1
    String sql1 = "select 序號 from stationinfo where 車次號=" + line_number +
        " and 站點名=" + "'" + startStation + "'" + "";
    String sql2 = "select 序號 from stationinfo where 車次號=" + line_number +
        " and 站點名=" + "'" + endStation + "'" + "";
    ResultSet rs1, rs2;
    try {
      rs1 = conndb.stmt.executeQuery(sql1);
      while (rs1.next()) {
        startnum = rs1.getInt("序號"); //開始站點序號
      }
      rs1.close();
      rs2 = conndb.stmt.executeQuery(sql2);
      while (rs2.next()) {
        endnum = rs2.getInt("序號"); //終止站點序號
      }
      rs2.close();
    }
    catch (SQLException e) {
      System.out.println(e); //打印sql執(zhí)行錯誤信息
    }
    countStation = compare(startnum, endnum); //求出兩個站點間的站點數(shù)
    return countStation; //返回站點數(shù)
  }

  /////////////////////////////////////////////////////////////////////////////
  public int compare(int a, int b) {
    //功能：計算兩個整數(shù)之差
    //返回值：整形，兩數(shù)之差
    int c = 0;
    if (a >= b) {
      c = a - b;
    }
    else
      c = b - a;
    return c;
  }

  /////////////////////////////////////////////////////////////////////////////
  public Vector search_Station(String station_name) {
    //功能：輸入站點名稱，輸出經(jīng)過該站點的車次，精確查詢
    //返回值：車次號向量
    conndb.connectDb(databasename);
    int line_number;
    Vector linenum_vector = new Vector(); //車次號向量
    linenum_vector.removeAllElements();
    String sql = "select 車次號 from stationinfo where 站點名=" + "'" + station_name +
        "'" + "";
    ResultSet rs;
    try {
      rs = conndb.stmt.executeQuery(sql);
      while (rs.next()) {
        line_number = rs.getInt("車次號"); //取出結(jié)果集中的車次號
        linenum_vector.addElement(Integer.toString(line_number)); //將車次號加入向量linenumVector
      }
      rs.close(); //關(guān)閉結(jié)果集
    }
    catch (SQLException e) {
      System.out.println(e);
    }
    return linenum_vector; //返回車次號向量
  }

  /////////////////////////////////////////////////////////////////////////////
  public Vector search_closeStation(String station_name) {
    //功能：輸入模糊站點名稱，輸出相近的站點名稱
    //返回值：站點名稱向量
    conndb.connectDb(databasename);
    String station_Name;
    Vector stationname_vector = new Vector(); //站點名向量
    stationname_vector.removeAllElements();
    String sql = "select distinct 站點名 from stationinfo where 站點名 like '%" +
        station_name + "%'";
    ResultSet rs;
    try {
      rs = conndb.stmt.executeQuery(sql);
      while (rs.next()) {
        station_Name = rs.getString("站點名");
        stationname_vector.addElement(station_Name);
      }
      rs.close();
    }
    catch (SQLException e) {
      System.out.println(e);
    }
    return stationname_vector;
  }

  /////////////////////////////////////////////////////////////////////////////
  public Vector search_LineProperty(int line_number) {
    //功能：輸入車次號,輸出該車次始發(fā)站,終點站,上行時間,下行時間,是否空調(diào)等屬性
    //返回值：所有屬性內(nèi)容的向量
    conndb.connectDb(databasename);
    String startstation, starttime, endstation, endtime, shifoukongtiao; //線路屬性
    Vector lineproperty_vector = new Vector(); //線路屬性向量
    Vector line_vector = new Vector(); //所有線路屬性集合向量
    lineproperty_vector.removeAllElements(); //向量清空
    line_vector.removeAllElements(); //向量清空
    String sql = "select 始發(fā)站,營運時間上,終點站,營運時間下,是否空調(diào) from businfo where 車次號=" +
        line_number + "";
    ResultSet rs;
    try {
      rs = conndb.stmt.executeQuery(sql); //執(zhí)行線路屬性搜索
      while (rs.next()) {
        startstation = rs.getString("始發(fā)站");
        starttime = rs.getString("營運時間上");
        endstation = rs.getString("終點站");
        endtime = rs.getString("營運時間下");
        shifoukongtiao = rs.getString("是否空調(diào)");
        lineproperty_vector.addElement(startstation);
        lineproperty_vector.addElement(starttime);
        lineproperty_vector.addElement(endstation);
        lineproperty_vector.addElement(endtime);
        lineproperty_vector.addElement(shifoukongtiao);
        line_vector.add(lineproperty_vector); //將線路屬性加入向量vector
      }
      rs.close();
    }
    catch (SQLException e) {
      System.out.println(e);
    }
    return line_vector;
  }

  /////////////////////////////////////////////////////////////////////////////
  public Vector search_Linestation(int line_number) {
    //功能：輸入車次號，輸出該車次經(jīng)過的站點
    //返回值：車次站點向量
    conndb.connectDb(databasename);
    String station_name; //站點名
    int station_number; //站點序號
    Vector line_vector = new Vector(); //該車次站點向量
    line_vector.removeAllElements();
    String sql = "select 序號,站點名 from stationinfo where 車次號=" + line_number;
    ResultSet rs;
    try {
      rs = conndb.stmt.executeQuery(sql);
      while (rs.next()) {
        Vector station_vector = new Vector(); //站點向量
        station_number = rs.getInt("序號");
        station_name = rs.getString("站點名");
        station_vector.addElement(Integer.toString(station_number)); //序號加入站點向量
        station_vector.addElement(station_name); //站點名加入站點向量
        line_vector.add(station_vector); //站點向量加入車次站點向量
      }
      rs.close();
    }
    catch (SQLException e) {
      System.out.println(e);
    }
    return line_vector;
  }

  /////////////////////////////////////////////////////////////////////////////
  public Vector search_nonstop(String start_station, String end_station) {
    //功能 ：輸入起始站和終點站，查詢可直達的車次
    //返回值：可直達車次向量
    conndb.connectDb(databasename);
    Vector nonstop_vector = new Vector(); //可直達車次向量
    nonstop_vector.removeAllElements();
    int line_number;
    String sql = "select 車次號,序號 from stationinfo  where  站點名= '" +
        start_station + "' and 車次號 in(select 車次號 from stationinfo where 站點名='" +
        end_station + "')";
    ResultSet rs;
    //站點均存在，進行查詢。
    try {
      rs = conndb.stmt.executeQuery(sql);
      while (rs.next()) {
        line_number = rs.getInt("車次號");
        nonstop_vector.addElement(Integer.toString(line_number));
      }
      rs.close();
    }
    catch (SQLException e) {
      System.out.println(e);
    }
    return nonstop_vector;
  }

  /////////////////////////////////////////////////////////////////////////////
  public Vector vector_intersection(Vector a, Vector b) {
    //功能：求兩個向量的交集
    //返回值：交集向量
    conndb.connectDb(databasename);
    Vector intersection = new Vector(); //交集向量
    int a_length, b_length;
    a_length = a.size(); //向量a的長度
    b_length = b.size(); //向量b的長度
    for (int i = 0; i < a_length; i++) {
      String a_element = a.get(i).toString();
      for (int j = 0; j < b_length; j++) {
        String b_element = b.get(j).toString();
        if (a_element.equalsIgnoreCase(b_element)) {
          intersection.add(b_element);
          break;
        }
      }
    }
    return intersection; //返回交集
  }

  /////////////////////////////////////////////////////////////////////////////
  public Vector search_oncechange(String start_station, String end_station) {
    //功能： 輸入起始站和終止站，查詢一次換乘方案，換乘站點，乘車車次
    //返回值：一次乘車向量，先乘車次，換乘站點，換乘車次
    conndb.connectDb(databasename);
    String start_station_name, end_station_name;
    Vector start_station_vector = new Vector(); //經(jīng)過站點start_station的車次所經(jīng)過的站點向量
    Vector end_station_vector = new Vector(); //經(jīng)過站點end_station的車次所經(jīng)過的站點向量
    Vector change_station_vector = new Vector(); //換乘站點向量
    Vector change_start_vector = new Vector(); //先乘車次向量集
    Vector change_end_vector = new Vector(); //換乘車次向量集
    Vector change_solution_vector = new Vector(); //換乘方案向量集

    change_solution_vector.removeAllElements();
    int start_station_length, end_station_length, change_station_length;
    String sql1 = "select distinct 站點名 from stationinfo where 車次號 in(select distinct 車次號 from stationinfo where 站點名=" +
        "'" + start_station + "'" + ")";
    String sql2 = "select distinct 站點名 from stationinfo where 車次號 in(select distinct 車次號 from stationinfo where 站點名=" +
        "'" + end_station + "'" + ")";
    ResultSet rs1, rs2;
    try {
      rs1 = conndb.stmt.executeQuery(sql1);
      while (rs1.next()) {
        start_station_name = rs1.getString("站點名");
        start_station_vector.addElement(start_station_name); //將站點加入start_station_vector向量
      }
      start_station_length = start_station_vector.size(); //向量start_station_vector的長度
      rs1.close(); //關(guān)閉結(jié)果集
      rs2 = conndb.stmt.executeQuery(sql2);
      while (rs2.next()) {
        end_station_name = rs2.getString("站點名");
        end_station_vector.addElement(end_station_name); //將站點加入end_station_vector向量
      }
      end_station_length = end_station_vector.size(); //向量end_station_vector的長度
      rs2.close(); //關(guān)閉結(jié)果集
    }
    catch (SQLException e) {
      System.out.println(e);
    }
    change_station_vector = vector_intersection(start_station_vector,
                                                end_station_vector); //交集向量,換乘站點向量
    change_station_length = change_station_vector.size();
    for (int i = 0; i < change_station_length; i++) { //換乘方案
      Vector line_start_vector = new Vector(); //先乘車次向量
      Vector line_end_vector = new Vector(); //換乘車次向量
      Vector change_vector = new Vector(); //換乘方案向量
      //change_vector.removeAllElements();//先清空換乘方案向量
      String sql3 = "select  distinct 車次號 from stationinfo  where  站點名= '" +
          start_station +
          "' and 車次號 in(select distinct 車次號 from stationinfo where 站點名='" +
          change_station_vector.get(i).toString().trim() + "')";
      ResultSet rs3, rs4;
      try {
        rs3 = conndb.stmt.executeQuery(sql3); //求出先乘車次號
        while (rs3.next()) {
          line_start_vector.addElement(Integer.toString(rs3.getInt("車次號")));
        }
        //change_start_vector.add(line_start_vector);//先乘車次向量
        rs3.close();
        String sql4 = "select  distinct 車次號 from stationinfo  where  站點名= '" +
            change_station_vector.get(i).toString().trim() +
            "' and 車次號 in(select distinct 車次號 from stationinfo where 站點名='" +
            end_station + "')";
        rs4 = conndb.stmt.executeQuery(sql4);
        while (rs4.next()) {
          line_end_vector.addElement(Integer.toString(rs4.getInt("車次號")));
        }
        //change_end_vector.add(line_end_vector);//換乘車次向量
        rs4.close();
      }
      catch (SQLException e) {
        System.out.println(e);
      }
      change_vector.add(line_start_vector);
      change_vector.add(change_station_vector.get(i).toString());
      change_vector.add(line_end_vector);
      change_solution_vector.add(change_vector);
    }
    //說明1：先乘車次向量change_start_vector內(nèi)容是向量。
    //說明2：換乘車次向量change_end_vector內(nèi)容是向量。
    //說明3：先乘車次向量和換乘車次向量和換乘站點向量長度是一樣的。
    //說明4：換乘方案向量change_vector內(nèi)容是：先乘車次向量，換乘站點，換乘車次向量
    //說明5：換乘方案向量集change_solution_vector內(nèi)容是換乘方案向量
    return change_solution_vector;
  }

}