package swdyx.bhdt;
import java.sql.*;
import java.util.*;
import javax.sql.*;
import swdyx.bhdt.BhdtBo;
import java.lang.String.*;

public class DsjDao {
  protected DataSource ds;
  public DsjDao(DataSource ds) {
    this.ds = ds;
  }
  //查詢記錄數  用于分頁
  public int countlist(String filter) throws SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int count = 0;

    try {
      conn = ds.getConnection();
      String sql = "select count(*) from d_xx where gnlxdm='04' ";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        count = rs.getInt(1);
      }
      rs.close();
      pstmt.close();
    }
    catch (SQLException e) {
      rs.close();
      pstmt.close();
      conn.rollback();
    }
    finally {
      conn.close();
    }
    return count;
  } //查詢記錄數  用于分頁 end

//取出各大事記信息到列表中
  public List list(int offset, int limit, String filter) throws SQLException {
    ArrayList list = new ArrayList();
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql = "SELECT * FROM " +
          "(" +
          " SELECT A.*, rownum r " +
          "FROM " +
          "(" +
          "select xx.xh,xx.bt,xx.nr,xx.jzsj,xx.fbsj,bmdm.bmmc,gnlxdm.gnlxmc,yxbzdm.yxbzmc "+
          "from d_xx xx,z_gnlxdm gnlxdm,z_yxbzdm yxbzdm,z_bmdm bmdm "+
          "where xx.gnlxdm=gnlxdm.gnlxdm "+
          "and xx.yxbzdm=yxbzdm.yxbzdm "+
          "and xx.bmdm=bmdm.bmdm "+
          "order by xx.xh" +
          " ) A " +
          " WHERE rownum < " + ( (offset / limit + 1) * limit + 1) +
          " ) B " +
          " WHERE r >=" + (offset + 1);

      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
       BhdtBo bhdtBo = new BhdtBo();
        bhdtBo.setXh(rs.getString("xh"));
        bhdtBo.setBt(rs.getString("bt"));
        bhdtBo.setNr(rs.getString("nr"));
        bhdtBo.setFbsj(rs.getString("fbsj"));
        bhdtBo.setJzsj(rs.getString("jzsj"));
        bhdtBo.setBmmc(rs.getString("bmmc"));
        bhdtBo.setGnlxmc(rs.getString("gnlxmc"));
        bhdtBo.setYxbzmc(rs.getString("yxbzmc"));
        list.add(bhdtBo);
      }
      conn.close();
      pstmt.close();
    }
    catch (SQLException e) {
      conn.close();
      pstmt.close();
      conn.rollback();
    }
    finally {
      conn.close();
    }
    return list;
  } // end

  //獲取各大事記基本信息
  public BhdtBo retrieve(String xh) throws SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql = "select xx.xh,xx.bt,xx.nr,xx.jzsj,xx.fbsj,bmdm.bmmc,gnlxdm.gnlxmc,yxbzdm.yxbzmc "+
          "from d_xx xx,z_gnlxdm gnlxdm,z_yxbzdm yxbzdm,z_bmdm bmdm "+
          "where xx.gnlxdm=gnlxdm.gnlxdm "+
          "and xx.yxbzdm=yxbzdm.yxbzdm "+
          "and xx.bmdm=bmdm.bmdm "+
          "and xh=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, xh);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        BhdtBo bhdtBo = new BhdtBo();
        bhdtBo.setXh(rs.getString("xh"));
        bhdtBo.setBt(rs.getString("bt"));
        bhdtBo.setNr(rs.getString("nr"));
        bhdtBo.setFbsj(rs.getString("fbsj"));
        bhdtBo.setJzsj(rs.getString("jzsj"));
        bhdtBo.setBmmc(rs.getString("bmmc"));
        bhdtBo.setGnlxmc(rs.getString("gnlxmc"));
        bhdtBo.setYxbzmc(rs.getString("yxbzmc"));
       return bhdtBo;
      }
      rs.close();
      pstmt.close();
    }
    catch (SQLException e) {
      rs.close();
      pstmt.close();
      conn.rollback();
      e.printStackTrace();
    }
    finally {
      conn.close();
    }
    return null;
  } //end

}