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

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

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

    // テーブル名
	private String table1 = "xb_mapping";
	private String table2 = "xb_etc";
	private String table3 = "xb_item";

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

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

    	return mapping;
    }

	public void insert(XbMappingObj 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.getTaxonomy_group_id());
			stmt.setLong(index++, obj.getTaxonomy_item_id());
			stmt.setLong(index++, obj.getUser_item_id());
			stmt.setInt(index++, obj.getItem_precision());
			stmt.setInt(index++, obj.getItem_decimal());
			stmt.setInt(index++, obj.getEtc_type());
			stmt.setLong(index++, obj.getEtc_id());
			stmt.setInt(index++, obj.getTuple_child_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, "SQLError : " + e.getMessage());
	    }
	}

	public ArrayList<XbMappingObj> selectAll(
			long taxonomy_group_id,
			String item_lang
		) throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("map.taxonomy_group_id, ");
			strb_sql.append("map.taxonomy_item_id, ");
			strb_sql.append("user_item_id, ");
			strb_sql.append("item_precision, ");
			strb_sql.append("item_decimal, ");
			strb_sql.append("etc_type, ");
			strb_sql.append("etc_id, ");
			strb_sql.append("etc_name, ");
			strb_sql.append("tuple_child_flag, ");
			strb_sql.append("description, ");
			strb_sql.append("creation_date ");
			strb_sql.append("from ( ");

			strb_sql.append("select ");
			strb_sql.append("taxonomy_group_id, ");
			strb_sql.append("taxonomy_item_id, ");
			strb_sql.append("user_item_id, ");
			strb_sql.append("item_precision, ");
			strb_sql.append("item_decimal, ");
			strb_sql.append(table1 + ".etc_type, ");
			strb_sql.append(table1 + ".etc_id, ");
			strb_sql.append("etc_name, ");
			strb_sql.append("tuple_child_flag, ");
			strb_sql.append("description, ");
			strb_sql.append(table1 + ".creation_date ");
			strb_sql.append("from ( " + table1 + " left join " + table2 + " ");
			strb_sql.append("on " + table1 + ".etc_type=" + table2 + ".etc_type ");
			strb_sql.append("and " + table1 + ".etc_id=" + table2 + ".etc_id ) ");
			strb_sql.append("where taxonomy_group_id=? ) map, ");

			strb_sql.append(table3 + " ");
			strb_sql.append("where map.taxonomy_group_id=" + table3 + ".taxonomy_group_id ");
			strb_sql.append("and map.taxonomy_item_id=" + table3 + ".taxonomy_item_id ");
			strb_sql.append("and item_lang=? ");

			strb_sql.append("order by item_order ");

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

			int index = 1;
			stmt.setLong(index++, taxonomy_group_id);
			stmt.setString(index++, item_lang);

			ResultSet rs = stmt.executeQuery();
			ArrayList<XbMappingObj> relist = new ArrayList<XbMappingObj>();
			while(rs.next()) {
				XbMappingObj obj = new XbMappingObj();
				obj.setTaxonomy_group_id(rs.getLong("taxonomy_group_id"));
				obj.setTaxonomy_item_id(rs.getLong("taxonomy_item_id"));
				obj.setUser_item_id(rs.getLong("user_item_id"));
				obj.setItem_precision(rs.getInt("item_precision"));
				obj.setItem_decimal(rs.getInt("item_decimal"));
				obj.setEtc_type(rs.getInt("etc_type"));
				obj.setEtc_id(rs.getLong("etc_id"));
				obj.setEtc_name(rs.getString("etc_name"));
				obj.setTuple_child_flag(rs.getInt("tuple_child_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, "SQLError : " + e.getMessage());
	    }
	}

	public XbMappingObj select(
			long taxonomy_group_id,
			long taxonomy_item_id
		) throws XbException{

		try {
			StringBuffer  strb_sql = new StringBuffer();
		    // 問合せの実行
			strb_sql.append("select ");
			strb_sql.append("taxonomy_group_id, ");
			strb_sql.append("taxonomy_item_id, ");
			strb_sql.append("user_item_id, ");
			strb_sql.append("item_precision, ");
			strb_sql.append("item_decimal, ");
			strb_sql.append(table1 + ".etc_type, ");
			strb_sql.append(table1 + ".etc_id, ");
			strb_sql.append("etc_name, ");
			strb_sql.append("tuple_child_flag, ");
			strb_sql.append("description, ");
			strb_sql.append(table1 + ".creation_date ");
			strb_sql.append("from ( " + table1 + " ");
			strb_sql.append("left join " + table2 + " ");
			strb_sql.append("on " + table1 + ".etc_type=" + table2 + ".etc_type ");
			strb_sql.append("and " + table1 + ".etc_id=" + table2 + ".etc_id ) ");
			strb_sql.append("where taxonomy_group_id=? ");
			strb_sql.append("and taxonomy_item_id=? ");

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

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

			ResultSet rs = stmt.executeQuery();
			XbMappingObj obj = null;
			while(rs.next()) {
				obj = new XbMappingObj();
				obj.setTaxonomy_group_id(rs.getLong("taxonomy_group_id"));
				obj.setTaxonomy_item_id(rs.getLong("taxonomy_item_id"));
				obj.setUser_item_id(rs.getLong("user_item_id"));
				obj.setItem_precision(rs.getInt("item_precision"));
				obj.setItem_decimal(rs.getInt("item_decimal"));
				obj.setEtc_type(rs.getInt("etc_type"));
				obj.setEtc_id(rs.getLong("etc_id"));
				obj.setEtc_name(rs.getString("etc_name"));
				obj.setTuple_child_flag(rs.getInt("tuple_child_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, "SQLError : " + e.getMessage());
	    }
	}
}