/*
 * 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.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

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

/**
 * @author Going Dot Com Inc.
 */
public class XbItemLabel21DAO extends XbItemLabelDAO {

    // singleton作成
    private static XbItemLabelDAO instance = null;

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

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

        return instance;
    }

    protected ArrayList<XbItemLabelObj> getItemLabel(long grpId) throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;
            StringBuffer  strb_sql = new StringBuffer();

            // メインリスト
            strb_sql.append("select ");
            strb_sql.append("itm.taxonomy_group_id as taxonomy_group_id, ");
            strb_sql.append("itm.taxonomy_id as taxonomy_id, ");
            strb_sql.append("itm.taxonomy_item_id as taxonomy_item_id, ");
            strb_sql.append("res.label_lang as label_lang, ");
            strb_sql.append("res.resource_value as resource_value ");
            strb_sql.append("from ");
            strb_sql.append("xb_linkbaseref ref, ");
            strb_sql.append("xb_taxonomy_item itm, ");
            strb_sql.append("xb_linkbase_locator loc, ");
            strb_sql.append("xb_linkbase_arc arc, ");
            strb_sql.append("xb_linkbase_resource res ");
            strb_sql.append("where ref.linkbaseref_id = loc.linkbaseref_id ");
            strb_sql.append("and loc.linkbaseref_id = arc.linkbaseref_id ");
            strb_sql.append("and arc.linkbaseref_id = res.linkbaseref_id ");
            strb_sql.append("and ref.linkbase_role  = 'http://www.xbrl.org/2003/role/labelLinkbaseRef' ");
            strb_sql.append("and arc.arc_arcrole    = 'http://www.xbrl.org/2003/arcrole/concept-label' ");
            strb_sql.append("and res.label_role     = 'http://www.xbrl.org/2003/role/label' ");
            strb_sql.append("and itm.taxonomy_group_id = ? ");
            strb_sql.append("and ref.taxonomy_group_id = itm.taxonomy_group_id ");
            strb_sql.append("and itm.item_id = loc.loc_identifier ");
            strb_sql.append("and loc.loc_label = arc.arc_from ");
            strb_sql.append("and res.resource_label = arc.arc_to");
            // SQL設定
            stmt = conn.prepareStatement(strb_sql.toString());
            int index = 1;

            stmt.setLong(index++, grpId);

            ResultSet rs = stmt.executeQuery();

            ArrayList<XbItemLabelObj> relist = new ArrayList<XbItemLabelObj>();
            while(rs.next()) {
                // サブリスト
                XbItemLabelObj Obj = new XbItemLabelObj();
                Obj.setTaxGrpId(rs.getString("taxonomy_group_id"));
                Obj.setTaxId(rs.getString("taxonomy_id"));
                Obj.setItemId(rs.getString("taxonomy_item_id"));
                Obj.setLang(rs.getString("label_lang"));
                Obj.setValue(rs.getString("resource_value"));
                relist.add(Obj);
            }
            // 結果セットをクローズ
            rs.close(); 
            // ステートメントをクローズ
            stmt.close(); 

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