/*
 * Copyright (c) 2007, Going Dot Com Inc. All rights reserved.
 */

package jp.co.going.xbrl.sample.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import jp.co.going.xbrl.common.XbException;
import jp.co.going.xbrl.sample.dao.obj.XbContextObj;

/**
 * @author Masako Okayasu
 */
public class XbContextDao {

	// singleton作成
    private static XbContextDao context = null;
    // Connection変数
    private static Connection conn = null;
	// ステートメントを作成
    private PreparedStatement stmt;

    // コンストラクタ
    public XbContextDao(){
    }

    // インスタンス取得
    public static synchronized XbContextDao getInstance(Connection connection){
    	//  インスタンスを生成
    	if(context == null) {
    		conn = connection;
    		context = new XbContextDao();
    	}

    	return context;
    }

	public void insert(XbContextObj obj) throws XbException{

		try {
			StringBuffer strb_sql = new StringBuffer();
		    // 問合せの実行
	    	strb_sql.append("insert into ");
	    	strb_sql.append("xb_context ");
	    	strb_sql.append("values ");
	    	strb_sql.append("(?,?,?,?,?,?,?,?,?,?,?,?,?)");

			// SQL設定
			stmt = conn.prepareStatement(strb_sql.toString());

			int index = 1;
			stmt.setLong(index++, obj.getContext_id());
			stmt.setString(index++, obj.getContext_type());
			stmt.setString(index++, obj.getContext_name());
			stmt.setString(index++, obj.getName());
			stmt.setLong(index++, obj.getEntity_id());
			stmt.setInt(index++, obj.getFiscal_year());
			stmt.setString(index++, obj.getPeriod_start_date());
			stmt.setString(index++, obj.getPeriod_end_date());
			stmt.setInt(index++, obj.getItem_precision());
			stmt.setInt(index++, obj.getItem_cwa());
			stmt.setInt(index++, obj.getUnit_flag());
			stmt.setString(index++, obj.getDescription());
	        stmt.setTimestamp(index++, new java.sql.Timestamp(obj.getCreation_date().getTime()));

		   	// SQL　実行
		   	stmt.executeUpdate();

		    // ステートメントをクローズ
		    stmt.close(); 

		} catch (SQLException e) {
	        throw new XbException(e, "SQL設定エラー: SQLError : " + e.getMessage());
	    }
	}

	public ArrayList<XbContextObj> selectAll() throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("context_id, ");
			strb_sql.append("context_type, ");
			strb_sql.append("context_name, ");
			strb_sql.append("name, ");
			strb_sql.append("entity_id, ");
			strb_sql.append("fiscal_year, ");
			strb_sql.append("period_start_date, ");
			strb_sql.append("period_end_date, ");
			strb_sql.append("item_precision, ");
			strb_sql.append("item_cwa, ");
			strb_sql.append("unit_flag, ");
			strb_sql.append("description, ");
			strb_sql.append("creation_date ");
			strb_sql.append("from xb_context ");
			strb_sql.append("order by context_id ");

			// SQL設定
			stmt = conn.prepareStatement(strb_sql.toString());

			ResultSet rs = stmt.executeQuery();
			ArrayList<XbContextObj> relist = new ArrayList<XbContextObj>();
			while(rs.next()) {
				XbContextObj obj = new XbContextObj();
				obj.setContext_id(rs.getLong("context_id"));
				obj.setContext_type(rs.getString("context_type"));
				obj.setContext_name(rs.getString("context_name"));
				obj.setName(rs.getString("name"));
				obj.setEntity_id(rs.getLong("entity_id"));
				obj.setFiscal_year(rs.getInt("fiscal_year"));
				obj.setPeriod_start_date(rs.getString("period_start_date"));
				obj.setPeriod_end_date(rs.getString("period_end_date"));
				obj.setItem_precision(rs.getInt("item_precision"));
				obj.setItem_cwa(rs.getInt("item_cwa"));
				obj.setUnit_flag(rs.getInt("unit_flag"));
				obj.setDescription(rs.getString("description"));
				obj.setCreation_date(rs.getDate("creation_date"));
				relist.add(obj);
			}

		    // 結果セットをクローズ
		    rs.close();

		    // ステートメントをクローズ
		    stmt.close();

		    return relist;
		} catch (SQLException e) {
	        throw new XbException(e, "SQL設定エラー: SQLError : " + e.getMessage());
	    }
	}

	public XbContextObj select(long context_id) throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("context_id, ");
			strb_sql.append("context_type, ");
			strb_sql.append("context_name, ");
			strb_sql.append("name, ");
			strb_sql.append("entity_id, ");
			strb_sql.append("fiscal_year, ");
			strb_sql.append("period_start_date, ");
			strb_sql.append("period_end_date, ");
			strb_sql.append("item_precision, ");
			strb_sql.append("item_cwa, ");
			strb_sql.append("unit_flag, ");
			strb_sql.append("description, ");
			strb_sql.append("creation_date ");
			strb_sql.append("from xb_context ");
			strb_sql.append("where context_id=? ");

			// SQL設定
			stmt = conn.prepareStatement(strb_sql.toString());

			int index = 1;
			stmt.setLong(index++, context_id);

			ResultSet rs = stmt.executeQuery();
			XbContextObj obj = null;
			while(rs.next()) {
				obj = new XbContextObj();
				obj.setContext_id(rs.getLong("context_id"));
				obj.setContext_type(rs.getString("context_type"));
				obj.setContext_name(rs.getString("context_name"));
				obj.setName(rs.getString("name"));
				obj.setEntity_id(rs.getLong("entity_id"));
				obj.setFiscal_year(rs.getInt("fiscal_year"));
				obj.setPeriod_start_date(rs.getString("period_start_date"));
				obj.setPeriod_end_date(rs.getString("period_end_date"));
				obj.setItem_precision(rs.getInt("item_precision"));
				obj.setItem_cwa(rs.getInt("item_cwa"));
				obj.setUnit_flag(rs.getInt("unit_flag"));
				obj.setDescription(rs.getString("description"));
				obj.setCreation_date(rs.getDate("creation_date"));
			}

		    // 結果セットをクローズ
		    rs.close();

		    // ステートメントをクローズ
		    stmt.close();

		    return obj;
		} catch (SQLException e) {
	        throw new XbException(e, "SQL設定エラー: SQLError : " + e.getMessage());
	    }
	}

	public XbContextObj selectByName(String context_name) throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("context_id, ");
			strb_sql.append("context_type, ");
			strb_sql.append("context_name, ");
			strb_sql.append("name, ");
			strb_sql.append("entity_id, ");
			strb_sql.append("fiscal_year, ");
			strb_sql.append("period_start_date, ");
			strb_sql.append("period_end_date, ");
			strb_sql.append("item_precision, ");
			strb_sql.append("item_cwa, ");
			strb_sql.append("unit_flag, ");
			strb_sql.append("description, ");
			strb_sql.append("creation_date ");
			strb_sql.append("from xb_context ");
			strb_sql.append("where context_name=? ");

			// SQL設定
			stmt = conn.prepareStatement(strb_sql.toString());

			int index = 1;
			stmt.setString(index++, context_name);

			ResultSet rs = stmt.executeQuery();
			XbContextObj obj = null;
			while(rs.next()) {
				obj = new XbContextObj();
				obj.setContext_id(rs.getLong("context_id"));
				obj.setContext_type(rs.getString("context_type"));
				obj.setContext_name(rs.getString("context_name"));
				obj.setName(rs.getString("name"));
				obj.setEntity_id(rs.getLong("entity_id"));
				obj.setFiscal_year(rs.getInt("fiscal_year"));
				obj.setPeriod_start_date(rs.getString("period_start_date"));
				obj.setPeriod_end_date(rs.getString("period_end_date"));
				obj.setItem_precision(rs.getInt("item_precision"));
				obj.setItem_cwa(rs.getInt("item_cwa"));
				obj.setUnit_flag(rs.getInt("unit_flag"));
				obj.setDescription(rs.getString("description"));
				obj.setCreation_date(rs.getDate("creation_date"));
			}

		    // 結果セットをクローズ
		    rs.close();

		    // ステートメントをクローズ
		    stmt.close();

		    return obj;
		} catch (SQLException e) {
	        throw new XbException(e, "SQL設定エラー: SQLError : " + e.getMessage());
	    }
	}

	public long getNextId() throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("max(context_id)+1 as max ");
			strb_sql.append("from xb_context ");

			// SQL設定
			stmt = conn.prepareStatement(strb_sql.toString());

			ResultSet rs = stmt.executeQuery();
			rs.next();

			return (rs.getLong("max")==0 ? 1 : rs.getLong("max"));
		} catch (SQLException e) {
	        throw new XbException(e, "SQL設定エラー: SQLError : " + e.getMessage());
	    }
	}
}