package jp.co.smile.common;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

public class Department {

  public Department() {

  }

  public static String getDepartment(String departid) {

    StringBuffer optionList = new StringBuffer();

    //SQL文
    String statement = "SELECT DEPARTMENT_NAME, DEPARTMENT_ID " +
                       "FROM DEPARTMENT "                       +
                       "ORDER BY DEPARTMENT_ID"                 ;

    try {

      //Oracle的连接
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
      String url = "jdbc:oracle:thin:@192.168.0.165:1521:jxsf";

      String user = "smile";
      String password = "smile";
      Connection conn = DriverManager.getConnection(url,user,password);
      if(conn != null){

       System.out.println("Connection Success"+conn);
      }else{

       System.out.println("Connection failed"+conn);
      }

      Statement stmt = conn.createStatement();

      //SQL的执行
      ResultSet result = stmt.executeQuery(statement);

      //提交
      conn.commit();

      if (departid.trim().length() != 0) {

        Map departTemp = new HashMap();
        String departNm = null;

        while (result.next()) {

          departTemp.put(result.getString("DEPARTMENT_ID"), result.getString("DEPARTMENT_NAME"));

        }

        Iterator iteratr = departTemp.keySet().iterator();

        Iterator iteratr2 = departTemp.keySet().iterator();

        while (true) {

          if (iteratr.next().equals(departid)) {

            departNm = departTemp.get(departid).toString();
            break;
          }

        }

        optionList.append("<option value='" + departid + "'>" + departNm + "</option>");
        optionList.append("<option value=''> </option>");

        while (iteratr2.hasNext()) {

          String deid = iteratr2.next().toString();

          if (!deid.equals(departid)) {

            optionList.append("<option value='" + deid + "'>" + departTemp.get(deid).toString() + "</option>");

          }
        }

      } else {

        optionList.append("<option value=''> </option>");

        while (result.next()) {

          optionList.append("<option value='" + result.getString("DEPARTMENT_ID") + "'>" + result.getString("DEPARTMENT_NAME") + "</option>");

        }
      }

      conn.close();

    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (InstantiationException e) {
      e.printStackTrace();
    } catch (IllegalAccessException e) {
      e.printStackTrace();
    }

    return optionList.toString();
  }

  public static void  main(String[] args) {
    SAXReader reader = new SAXReader();
    try {
      Document doc = reader.read(new File("E:\\SWECTool\\bea\\user_projects\\domains\\default\\config.xml"));
      Element connPoolEle = (Element) doc.selectSingleNode("/Domain/JDBCConnectionPool");
      String url = connPoolEle.attributeValue("URL");
      System.out.println(url);
    } catch (DocumentException e) {

      e.printStackTrace();
    }

  }

}