/*
 * 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.XbItemInterfaceObj;
import jp.co.going.xbrl.sample.dao.obj.XbTupleDataObj;

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

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

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

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

    	return item;
    }

	public void insert(XbTupleDataObj obj) throws XbException{

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

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

			int index = 1;
			stmt.setLong(index++, obj.getItem_order());
			stmt.setLong(index++, obj.getTaxonomy_group_id());
			stmt.setLong(index++, obj.getTaxonomy_item_id());
			stmt.setLong(index++, obj.getTaxonomy_item_seq());
			stmt.setLong(index++, obj.getParent_taxonomy_item_id());
			stmt.setLong(index++, obj.getParent_taxonomy_item_seq());
			stmt.setString(index++, obj.getItem_value());
			stmt.setLong(index++, obj.getContext_id());

		   	// SQL　実行
		   	stmt.executeUpdate();

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

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

	public ArrayList<XbTupleDataObj> selectAll() throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("item_order, ");
			strb_sql.append("taxonomy_group_id, ");
			strb_sql.append("taxonomy_item_id, ");
			strb_sql.append("taxonomy_item_seq, ");
			strb_sql.append("parent_taxonomy_item_id, ");
			strb_sql.append("parent_taxonomy_item_seq, ");
			strb_sql.append("item_value, ");
			strb_sql.append("context_id ");
			strb_sql.append("from xb_tuple_data ");
			strb_sql.append("order by item_order ");

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

			ResultSet rs = stmt.executeQuery();
			ArrayList<XbTupleDataObj> relist = new ArrayList<XbTupleDataObj>();
			while(rs.next()) {
				XbTupleDataObj obj = new XbTupleDataObj();
				obj.setItem_order(rs.getLong("item_order"));
				obj.setTaxonomy_group_id(rs.getLong("taxonomy_group_id"));
				obj.setTaxonomy_item_id(rs.getLong("taxonomy_item_id"));
				obj.setTaxonomy_item_seq(rs.getLong("taxonomy_item_seq"));
				obj.setParent_taxonomy_item_id(rs.getLong("parent_taxonomy_item_id"));
				obj.setParent_taxonomy_item_seq(rs.getLong("parent_taxonomy_item_seq"));
				obj.setItem_value(rs.getString("item_value"));
				obj.setContext_id(rs.getLong("context_id"));
				relist.add(obj);
			}

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

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

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

	public void delete() throws XbException{

		try {
			StringBuffer strb_sql = new StringBuffer();
		    // 問合せの実行
	    	strb_sql.append("delete from ");
	    	strb_sql.append("xb_tuple_data ");

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

		   	// SQL　実行
		   	stmt.executeUpdate();

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

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

	public ArrayList<XbItemInterfaceObj> selectItemInt(
			long user_item_id,
			String entity,
			String start_date,
			String end_date,
			String unit
		) throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("user_item_id, ");
			strb_sql.append("item_value, ");
			strb_sql.append("entity, ");
			strb_sql.append("period, ");
			strb_sql.append("unit ");
			strb_sql.append("from xb_item_interface ");
			strb_sql.append("where user_item_id=? ");
			strb_sql.append("and entity=? ");
			strb_sql.append("and period>=? and period<=? ");
			strb_sql.append("and unit=? ");

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

			int index = 1;
			stmt.setLong(index++, user_item_id);
			stmt.setString(index++, entity);
			stmt.setString(index++, start_date);
			stmt.setString(index++, end_date);
			stmt.setString(index++, unit);

			ResultSet rs = stmt.executeQuery();
			ArrayList<XbItemInterfaceObj> relist = new ArrayList<XbItemInterfaceObj>();
			while(rs.next()) {
				XbItemInterfaceObj obj = new XbItemInterfaceObj();
				obj.setUser_item_id(rs.getLong("user_item_id"));
				obj.setItem_value(rs.getString("item_value"));
				obj.setEntity(rs.getString("entity"));
				obj.setPeriod(rs.getString("period"));
				obj.setUnit(rs.getString("unit"));
				relist.add(obj);
			}

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

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

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