package com.huiton.pub.dbx;

import java.sql.*;
import java.lang.*;
import java.util.*;
import java.io.*;
import com.huiton.pub.tools.*;
import com.huiton.pub.lan_tools.*;

/**
 * Title:    數據庫對象類，
 * Description:  對象生成后自動建立連接對象和會話對象，它封裝了數據庫對象的建立過程，
 * 其它的數據庫操作應該繼承本類
 * Copyright:    Copyright (c) 2001
 * Company: 利瑪信息技術有限公司
 * @author：王濤
 * @version 1.0
 */
public class JdbObj {

  /**
   * @const: driver 驅動程序名
   * @const: url 數據源名
   * @const: userName 用戶名
   * @const: password 用戶口令
   * @const: conn 連接對象
   * @const:
   * @const:
   */
  public String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
  public String url = "jdbc:odbc:camps";
  public String userName = "huiton";
  public String password = "cerp";
  public String database = "";
  public Connection conn = null;
  public String m_errMsg = "沒有錯誤信息";
  String m_sessionCode = null;
  String m_sysCode = null;
  String cfgFile = "btcDatabase.cfg";      //缺省配置文件名

  boolean login = false;

  public int m_scrollType = ResultSet.TYPE_SCROLL_SENSITIVE;     //缺省結果集油標類型
  public int m_updateType = ResultSet.CONCUR_READ_ONLY;
  private boolean autoClose = true;      //對象銷毀時自動關閉聯接

/**
 * title: 缺省構造函數，
 * Description: 從配置文件"btcDatabase.cfg"中讀取數據庫配置，配置文件必須放置在當前系統的工作目錄下
 * 其缺省設置如下：
 * 驅動：ODBC/JDBC
 * 用戶名:huiton,
 * 口令：cerp，
 * 數據源：cerp
*/
  public JdbObj() throws Exception {
    readCfg();
  }

  /**
   * 構造函數
   * Description 本函數利用登陸的用戶代號在SAM（系統管理)庫中獲取連庫所需要的參數，并返回相應連接
   * @param sessionCode: 登陸用戶的代號
   * @param sysCode：登陸要訪問的系統名稱。一個系統可能會對應不同的數據庫。如果sysCode為空或等于"sam"則返回系統管理庫
   */
  public JdbObj(String sessionCode,String sysCode) throws Exception {
    m_sessionCode = sessionCode;
    m_sysCode = sysCode;
    readCfg();
  }

  /**
   * 構造函數
   * Description 本函數利用登陸的用戶代號在SAM（系統管理)庫中獲取連庫所需要的參數，并返回相應連接。本函數可以制定一個新的數據庫連接配置文件
   * @param sessionCode: 登陸用戶的代號
   * @param sysCode：登陸要訪問的系統名稱。一個系統可能會對應不同的數據庫。如果sysCode為空或等于"sam"則返回系統管理庫
   *
   */
  public JdbObj(String sessionCode,String sysCode,String cfgFile) throws Exception {
    //確認配置文件名有效
    if (cfgFile==null || cfgFile=="")
      cfgFile = "btcDatabase.cfg";
    this.cfgFile = cfgFile;
    m_sessionCode = sessionCode;
    m_sysCode = sysCode;
    readCfg();
  }

  /**
   * title: 帶參構造函數
   * @param dbType 數據庫類型。如"Sql"，"Oracle"等。該常量在DbType中定義
   * @param dbUrl 數據源。根據不同的數據源，可打開不同的庫。該常量在DbUrl中定義
   */
  public JdbObj(int dbType,int dbUrl) throws Exception {

    switch (dbType) { //設置數據庫啟動程序
      case DbType.JDBC_ODBC:    //jdbc_odbc橋
        driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        switch (dbUrl) {
          case DbUrl.CERP:
            url = "jdbc:odbc:camps";
            break;
          default:
            m_errMsg = "不可識別的url, 請使用DbUrl提供的常數";
        }
        break;
      case DbType.SQL:    //SQL Server驅動程序
        driver = "com.inet.tds.TdsDriver";
        switch (dbUrl) {
          case DbUrl.CERP:
            url = "jdbc:inetdae:127.0.0.1:1433?database=camps9&charset=GBK";
            break;
          case DbUrl.MASTER:
            url = "jdbc:inetdae:127.0.0.1:1433?database=master&charset=GBK";
            userName = "sa";
            password = "";
            break;
          default:
            m_errMsg = "不可識別的url, 請使用DbUrl提供的常數";
        }
        break;
      default:
        m_errMsg = "不可使用的數據庫類型, 請使用DbType提供的常數";
    }

  }

  /**
   * 析構函數
   */
   public void finalize () throws Exception {
    if (this.autoClose)
      conn.close();    //關閉數據庫連接
   }

  /**
   * 激活數據庫實體。只有在激活實體后才可以進行其它操作。
   * @param devPassword 開發人員口令
   */
  public boolean active (String devPassword) {
    m_errMsg = "";
  try{
//System.out.println("enter active");
    if (devPassword.compareToIgnoreCase(DbConstStr.DEVPASSWORD) != 0)
      {m_errMsg = "口令錯誤";
       login = false;
       return false;
      }
/*
System.out.println(driver);
System.out.println(url);
System.out.println(userName);
System.out.println(password);
*/
   //創建與SAM庫的連接
   Class.forName(driver);//驅動
   Connection samConn = DriverManager.getConnection(url, userName, password);
   if (!database.equals(""))
    samConn.setCatalog(database);
   if (m_sessionCode == null || m_sysCode == null || m_sysCode.compareToIgnoreCase("sam")==0 ||
       m_sessionCode == "" || m_sysCode == "")  {
      conn = samConn; //返回系統庫
      return true;
   }
   //獲取定位數據庫的輔助信息
    Statement st = samConn.createStatement(m_scrollType,m_updateType);
    ResultSet rst = st.executeQuery("select company_code,year from sam_session where session_code = " + LanTools.toSqlString(m_sessionCode));
    if (rst == null)  {
      m_errMsg = "系統表存取錯誤：sam";
      samConn.close();
      return false;
    }
    if (!rst.next())
      {m_errMsg = "會話已經過期: " + m_sessionCode;
       samConn.close();
       return false;
      }
    //session有效，獲取公司號和年份
    String company = rst.getString("company_code");
    String year = rst.getString("year");
    //獲得請求的數據庫連接
    String mySql = "select driver_manager,db_url,db_user,db_pass from scg_db_config where company_code = ";
    mySql += LanTools.toSqlString(company);
    mySql += " and sys_code = " + LanTools.toSqlString(m_sysCode);
    mySql += " and year = " + LanTools.toSqlString(year);
    rst = st.executeQuery(mySql);
    if (rst==null || !rst.next())  {
      m_errMsg = "數據庫定義不存在：代碼：" + m_sysCode + " 公司：" + company + " 年份：" + year;
      samConn.close();
     return false;
    }

    driver = rst.getString("driver_manager");
    url = rst.getString("db_url");
    userName = rst.getString("db_user");
    password = cerp_crypt.get_decoded_pass(rst.getString("db_pass"));
    samConn.close();
   Class.forName(driver);//驅動
   conn = DriverManager.getConnection(url, userName, password);

   login = true;

   return true;
  }catch (Exception e)  {
    m_errMsg = "連接數據庫錯誤(" + url + "," + userName + "," + password + ")：" + e;
    e.printStackTrace();
  }
    return false;
  }

  /**
   * description 查詢當前數據庫中是否已經存在某個表
   * @param tblName 表名
   * return 表存在時返回true，否則返回false
   */
  public boolean hasTable(String tblName) throws Exception {
  try {
    DatabaseMetaData dbMeta = conn.getMetaData();
    ResultSet rstMeta = dbMeta.getTables(null,null,null,null);
    while (rstMeta.next())  {
//      System.out.println(rstMeta.getString("TABLE_NAME"));
     if (rstMeta.getString("TABLE_NAME").compareToIgnoreCase(tblName) == 0)
        return true;
    }
}catch (Exception e) {
  m_errMsg = e.getMessage();
}
    return false;
  }

  /**
   * description 查詢當前數據庫中是否已經存在某個庫
   * @param dbName 表名
   * return 表存在時返回true，否則返回false
   */
  public boolean hasCatalog(String dbName) throws Exception {
  try {
    DatabaseMetaData dbMeta = conn.getMetaData();
    ResultSet rstMeta = dbMeta.getCatalogs();
    while (rstMeta.next())  {
//      System.out.println(rstMeta.getString("TABLE_NAME"));
     if (rstMeta.getString("TABLE_CAT").compareToIgnoreCase(dbName) == 0)
        return true;
    }
}catch (Exception e) {
  m_errMsg = e.getMessage();
}
    return false;
  }

  /**
   * description 查找是否有指定的索引
   * @param: idxName 要查找的索引名
   * @param: tblName 索引所在的表名
   */
  public boolean hasIndex(String tblName,String idxName) {
  try {
    DatabaseMetaData dbMeta = conn.getMetaData();
    ResultSet rstMeta = dbMeta.getIndexInfo(null,null,tblName,false,true);

//System.out.println(idxName+"\n");
    while (rstMeta.next())  {
      String iname = rstMeta.getString("INDEX_NAME");
      if (iname == null)
        continue;
//System.out.print(iname + "\t");
      if (iname.compareToIgnoreCase(idxName)== 0)
        return true;
//System.out.println("next");
    }
}catch (Exception e)  {
  m_errMsg = e.getMessage();
}
    return false;
  }

  /**
   * 用當前連接獲得表的字段定義
   * @param tblName 要獲得的表名
   * @return  成功時返回由FieldDefn組成的向量組，否則返回null
   */
  public Vector getTableFields(String tblName)
  {
  try
  {
    System.out.println("JdbObj: getTableFields");
    ResultSet rstMeta = conn.getMetaData().getColumns(null,null,tblName,null);
    if (rstMeta!= null)
    {
      Vector vFields = new Vector();
      FieldDefn fd;
      while (rstMeta.next())
      {
        fd = new FieldDefn();
        fd.colName = rstMeta.getString("COLUMN_NAME");
        fd.colType = rstMeta.getInt("DATA_TYPE");
        fd.colLen = rstMeta.getInt("COLUMN_SIZE");
        fd.nullable = rstMeta.getBoolean("NULLABLE");
        fd.colDft = rstMeta.getString("COLUMN_DEF");
        vFields.add(fd);
      }
      return vFields;
    }
    else
      throw new Exception("沒有表的信息：" + tblName);
  }
  catch (Exception e)
  {
    e.printStackTrace();
    m_errMsg = e.getMessage();
    return null;
  }

  }

  /**
   * Title readCfg
   * Description 從配置文件中讀出用于到開系統配置庫的參數
   * return 如果配置文件存在，返回true，否則返回false并設置缺省值
   */
  boolean readCfg() {
  try {
//  System.out.print(System.getProperty("user.dir"));
//    FileInputStream cfgFile = new FileInputStream("btcDatabase.cfg");
    FileInputStream cfgFile = new FileInputStream(this.cfgFile);

    //讀入配置參數
    Properties proObj = new Properties();
    proObj.load(cfgFile);
    driver = proObj.getProperty("Driver","sun.jdbc.odbc.JdbcOdbcDriver");
    url = proObj.getProperty("UrlSam","jdbc:odbc:camps");
    userName = proObj.getProperty("UserName","huiton");
    password = proObj.getProperty("Password","cerp");
    database = proObj.getProperty("Database","");

    //解密口令
//    if (this.cfgFile.equalsIgnoreCase("btcDatabase.cfg"))
      password = cerp_crypt.get_decoded_pass(password);
    return true;

   }catch (Exception e)
   {//設置缺省值
    driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    url = "jdbc:odbc:camps";
    userName = "huiton";
    password = "cerp";

    m_errMsg = e.getMessage();
    return false;
   }
  }

  /**
   * 設置對象取消時是否關閉聯接
   * @param autoClose：為true時自動關閉聯接，否則保留聯接。缺省時為true
   */
  public void setAutoClose(boolean autoClose) {
    this.autoClose = autoClose;
  }

  /**
   * 本構造函數只適用于Cerp系統。它使用其參數直接聯接相應的數據庫，而不使用session。本函數應該和JdbObj()構造函數聯合使用。
   * 激活數據庫實體。只有在激活實體后才可以進行其它操作。
   * @param devPassword 開發人員口令
   * @param companyCode   要聯接的公司代號
   * @param year  要聯接的年份
   * @param sysCode 要聯接的子系統代號
   * @return 數據庫聯接成功是返回true，否則返回false
   */
  public boolean active (String devPassword,String companyCode,String year,String sysCode) {
    m_errMsg = "";
  try{
//System.out.println("enter active");
    if (devPassword.compareToIgnoreCase(DbConstStr.DEVPASSWORD) != 0)
      {m_errMsg = "口令錯誤";
       login = false;
       return false;
      }
    m_sessionCode = "Direct";     //直接聯接
    m_sysCode = sysCode;

   //創建與SAM庫的連接
   Class.forName(driver);//驅動
   Connection samConn = DriverManager.getConnection(url, userName, password);
   if (!database.equals(""))
    samConn.setCatalog(database);
   if (m_sessionCode == null || m_sysCode == null || m_sysCode.compareToIgnoreCase("sam")==0 ||
       m_sessionCode == "" || m_sysCode == "")  {
      conn = samConn; //返回系統庫
      return true;
   }
    //獲得請求的數據庫連接
    Statement st = samConn.createStatement();
    String mySql = "select driver_manager,db_url,db_user,db_pass from scg_db_config where company_code = ";
    mySql += LanTools.toSqlString(companyCode);
    mySql += " and sys_code = " + LanTools.toSqlString(m_sysCode);
    mySql += " and year = " + LanTools.toSqlString(year);
    ResultSet rst = st.executeQuery(mySql);
    if (rst==null || !rst.next())  {
      m_errMsg = "數據庫定義不存在：代碼：" + m_sysCode + " 公司：" + companyCode + " 年份：" + year;
      samConn.close();
     return false;
    }

    driver = rst.getString("driver_manager");
    url = rst.getString("db_url");
    userName = rst.getString("db_user");
    password = cerp_crypt.get_decoded_pass(rst.getString("db_pass"));
    samConn.close();
   Class.forName(driver);//驅動
   conn = DriverManager.getConnection(url, userName, password);

   login = true;

   return true;
  }catch (Exception e)  {
    m_errMsg = "連接數據庫錯誤(" + url + "," + userName + "," + password + ")：" + e;
    e.printStackTrace();
  }
    return false;
  }

}