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

package jp.co.going.xbrl.taxonomy;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import jp.co.going.xbrl.common.XbException;

/**
 * @author Going Dot Com Inc.
 */
public class XbItemWork20DAO implements XbItemWorkDAO {

    // singleton作成
    private static XbItemWorkDAO instance = null;
    // Connection変数
    private static Connection conn = null;

    // コンストラクタ
    private XbItemWork20DAO() {
    }

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

        return instance;
    }

    public void insert(long grpId)
        throws XbException {

        try {
            // ステートメントを作成
            PreparedStatement stmt;

    		StringBuffer  strb_sql = new StringBuffer();
    	    // 問合せの実行
        	strb_sql.append("insert into ");
        	strb_sql.append("xb_item_work (");
            strb_sql.append("taxonomy_group_id, ");
            strb_sql.append("taxonomy_id, ");
            strb_sql.append("taxonomy_item_id, ");
            strb_sql.append("item_type, ");
            strb_sql.append("parent_taxonomy_item_id, ");
            strb_sql.append("parent_item_type, ");
            strb_sql.append("context_type, ");
            strb_sql.append("presentation_order) ");
            strb_sql.append("select ");
            strb_sql.append("itm1.taxonomy_group_id, ");
            strb_sql.append("itm1.taxonomy_id, ");
            strb_sql.append("itm1.taxonomy_item_id, ");
            strb_sql.append("itm1.item_substitutiongroup, ");
            strb_sql.append("itm2.taxonomy_item_id, ");
            strb_sql.append("itm2.item_substitutiongroup, ");
            strb_sql.append("type.attribute_name, ");
            strb_sql.append("arc.presentation_order ");
            strb_sql.append("from ");
            strb_sql.append("(xb_taxonomy_item itm1 ");
            strb_sql.append("left outer join xb_item_type type ");
            strb_sql.append("on type.type_name = itm1.item_type ");
            strb_sql.append("and type.specification_no = '2.0'),");
            strb_sql.append("xb_taxonomy_item itm2, ");
            strb_sql.append("xb_linkbase_arc arc, ");
            strb_sql.append("xb_linkbaseref ref, ");
            strb_sql.append("xb_linkbase_locator loc1, ");
            strb_sql.append("xb_linkbase_locator loc2 ");
            strb_sql.append("where ref.linkbaseref_id = loc1.linkbaseref_id ");
            strb_sql.append("and ref.linkbaseref_id = loc2.linkbaseref_id ");
            strb_sql.append("and ref.linkbaseref_id = arc.linkbaseref_id ");
            strb_sql.append("and ref.linkbase_role  = 'http://www.xbrl.org/linkprops/linkRef/presentation' ");
            strb_sql.append("and arc.arc_arcrole    = 'http://www.xbrl.org/linkprops/arc/child-parent' ");
            strb_sql.append("and itm1.taxonomy_group_id = itm2.taxonomy_group_id ");
            strb_sql.append("and itm1.taxonomy_group_id = ? ");
            strb_sql.append("and arc.arc_from = loc1.loc_label ");
            strb_sql.append("and loc2.loc_identifier = itm2.item_id ");
            strb_sql.append("and arc.arc_to = loc2.loc_label ");
            strb_sql.append("and loc1.loc_identifier = itm1.item_id ");
            strb_sql.append("group by itm1.taxonomy_group_id,itm1.taxonomy_id,itm1.taxonomy_item_id,itm1.item_substitutiongroup,itm2.taxonomy_item_id,itm2.item_substitutiongroup,type.attribute_name,arc.presentation_order");

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

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

    	   	// SQL　実行
    	   	stmt.executeUpdate();

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

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