/*
 * 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.XbDivideMeasureObj;

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

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

    // テーブル名
	private String table1 = "xb_divide_measure";
	private String table2 = "xb_measure";

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

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

    	return divideMeasure;
    }

	public void insert(XbDivideMeasureObj obj) throws XbException{

		try {
			StringBuffer strb_sql = new StringBuffer();
		    // 問合せの実行
	    	strb_sql.append("INSERT INTO ");
	    	strb_sql.append(table1 + " ");
	    	strb_sql.append("VALUES ");
	    	strb_sql.append("(?,?,?,?,?)");

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

			int index = 1;
			stmt.setLong(index++, obj.getDivide_id());
			stmt.setInt(index++, obj.getDivide_type());
			stmt.setLong(index++, obj.getMeasure_id());
			stmt.setLong(index++, obj.getPresentation_order());
	        stmt.setTimestamp(index++, new java.sql.Timestamp(obj.getCreation_date().getTime()));

		   	// SQL　実行
		   	stmt.executeUpdate();

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

		} catch (SQLException e) {
	        throw new XbException(e, "SQLError : " + e.getMessage());
	    }
	}

	public ArrayList<XbDivideMeasureObj> selectAll(long divide_id) throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("DIVIDE_ID, ");
			strb_sql.append("DIVIDE_TYPE, ");
			strb_sql.append(table1 + ".MEASURE_ID, ");
			strb_sql.append("MEASURE_NAME, ");
			strb_sql.append("MONEY_FLAG, ");
			strb_sql.append("VALUE, ");
			strb_sql.append("PRESENTATION_ORDER, ");
			strb_sql.append(table1 + ".CREATION_DATE ");
			strb_sql.append("from " + table1 + ", " + table2 + " ");
			strb_sql.append("where DIVIDE_ID=? ");
			strb_sql.append("and " + table1 + ".MEASURE_ID=" + table2 + ".MEASURE_ID ");
			strb_sql.append("order by PRESENTATION_ORDER, ");
			strb_sql.append(table1 + ".DIVIDE_TYPE, ");
			strb_sql.append(table1 + ".MEASURE_ID ");

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

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

			ResultSet rs = stmt.executeQuery();
			ArrayList<XbDivideMeasureObj> relist = new ArrayList<XbDivideMeasureObj>();
			while(rs.next()) {
				XbDivideMeasureObj obj = new XbDivideMeasureObj();
				obj.setDivide_id(rs.getLong("DIVIDE_ID"));
				obj.setDivide_type(rs.getInt("DIVIDE_TYPE"));
				obj.setMeasure_id(rs.getLong("MEASURE_ID"));
				obj.setMeasure_name(rs.getString("MEASURE_NAME"));
				obj.setMoney_flag(Boolean.valueOf(rs.getString("MONEY_FLAG")));
				obj.setValue(rs.getString("VALUE"));
				obj.setPresentation_order(rs.getLong("PRESENTATION_ORDER"));
				obj.setCreation_date(rs.getDate("CREATION_DATE"));
				relist.add(obj);
			}

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

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

		    return relist;
		} catch (SQLException e) {
	        throw new XbException(e, "SQLError : " + e.getMessage());
	    }
	}

	public XbDivideMeasureObj select(long divide_id, long measure_id) throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("DIVIDE_ID, ");
			strb_sql.append("DIVIDE_TYPE, ");
			strb_sql.append(table1 + ".MEASURE_ID, ");
			strb_sql.append("MEASURE_NAME, ");
			strb_sql.append("MONEY_FLAG, ");
			strb_sql.append("VALUE, ");
			strb_sql.append("PRESENTATION_ORDER, ");
			strb_sql.append(table1 + ".CREATION_DATE ");
			strb_sql.append("from " + table1 + ", " + table2 + " ");
			strb_sql.append("where DIVIDE_ID=? ");
			strb_sql.append("and " + table1 + ".MEASURE_ID=? ");
			strb_sql.append("and " + table1 + ".MEASURE_ID=" + table2 + ".MEASURE_ID ");

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

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

			ResultSet rs = stmt.executeQuery();
			XbDivideMeasureObj obj = null;
			while(rs.next()) {
				obj = new XbDivideMeasureObj();
				obj.setDivide_id(rs.getLong("DIVIDE_ID"));
				obj.setDivide_type(rs.getInt("DIVIDE_TYPE"));
				obj.setMeasure_id(rs.getLong("MEASURE_ID"));
				obj.setMeasure_name(rs.getString("MEASURE_NAME"));
				obj.setMoney_flag(Boolean.valueOf(rs.getString("MONEY_FLAG")));
				obj.setValue(rs.getString("VALUE"));
				obj.setPresentation_order(rs.getLong("PRESENTATION_ORDER"));
				obj.setCreation_date(rs.getDate("CREATION_DATE"));
			}

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

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

		    return obj;
		} catch (SQLException e) {
	        throw new XbException(e, "SQLError : " + e.getMessage());
	    }
	}
}