package com.jbaptech.accp.newspublish.db;

import com.jbaptech.accp.newspublish.data.SecondLevelTitle;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.text.SimpleDateFormat;

/**
 * SecondLevelTitleDbOpreation.
 * <p>Title: </p>  *
 * <p>Description: </p>  *
 * <p>Copyright: Copyright (c) 2005</p>  *
 * <p>Company: 北京阿博泰克北大青鳥信息技術有限公司</p> *
 * @author luohao
 * @version 1.0
 */
public class SecondLevelTitleDbOpreation {
  /**
   * constructor.
   */
  public SecondLevelTitleDbOpreation() {
  }

  /**
   * get second level title list by parent level title id.
   * @param parentTitleId int
   * @return ArrayList
   */
  public ArrayList getSecondLevelTitleListByFirstLevelTitle(int parentTitleId) {
    ArrayList list = new ArrayList();
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet res = null;

    try {
      dbConnection = ConnectionManager.getConnction();
      // 查詢數據SQL語句
      String strSql =
          "select * from SecondLevelTitle "
          + " where ParentTitle=(?) order by CreatTime desc";
      if (dbConnection != null) {
        System.out.println(dbConnection != null);
      }

      //查詢操作
      pStatement = dbConnection.prepareStatement(strSql);
      pStatement.setInt(1, parentTitleId);
      res = pStatement.executeQuery();
      while (res.next()) {
        SecondLevelTitle sTitle = new SecondLevelTitle();
        sTitle.setId(res.getInt("id"));
        sTitle.setTitleName(res.getString("TitleName"));
        sTitle.setFilePath(res.getString("FilePath"));
        sTitle.setCreater(res.getString("Creater"));
        sTitle.setCreateTime(res.getDate("CreatTime"));
        sTitle.setParentTitleId(res.getInt("ParentTitle"));
        list.add(sTitle);
      }
    } catch (SQLException sqlE) {
      sqlE.printStackTrace();
    }  finally {
      ConnectionManager.closeResultSet(res);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }

    return list;
  }

  /**
   * insert a row into SecondLevelTitle table.
   * @param sTitle SecondLevelTitle
   * @return int
   */
  public int insertOneRecord(SecondLevelTitle sTitle) {
    int result = 0;
    Connection con = null;
    PreparedStatement pStatement = null;

    try {
      Date currentTime = new Date();
      SimpleDateFormat HMFromat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
      String strCurrentTime = HMFromat.format(currentTime);

      con = ConnectionManager.getConnction();
      String strSql =
          "insert into SecondLevelTitle(Id,TitleName,FilePath,Creater,"
          + "CreatTime,ParentTitle) values(?,?,?,?,?,?)";
      pStatement = con.prepareStatement(strSql);
      pStatement.setInt(1, getNewId());
      pStatement.setString(2, sTitle.getTitleName());
      pStatement.setString(3, sTitle.getFilePath());
      pStatement.setString(4, sTitle.getCreater());
      pStatement.setString(5, strCurrentTime);
      pStatement.setInt(6, sTitle.getParentTitleId());
      System.out.println(strSql);
      result = pStatement.executeUpdate();
    } catch (SQLException sqlE) {
      sqlE.printStackTrace();
    } finally {
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(con);
    }
    return result;
  }

  /**
   * create a new  id of SecondLevelTitle.
   * @return int
   */
  private int getNewId() {
    int id = 0;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet resSet = null;
    try {
      con = ConnectionManager.getConnction();
      // 查詢數據SQL語句
      String sqlStr = "select max(id) from SecondLevelTitle ";
      //查詢操作
      pstmt = con.prepareStatement(sqlStr);
      resSet = pstmt.executeQuery();
      if (resSet.next()) {
        id = resSet.getInt(1);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }   finally {

      ConnectionManager.closeResultSet(resSet);
      ConnectionManager.closeStatement(pstmt);
      ConnectionManager.closeConnection(con);

    }
    return id + 1;
  }

}