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

package jp.co.going.xbrl.instance;

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.
 */
class XbMappingDAO20 extends XbMappingDAO {

    // singleton作成
    private static XbMappingDAO20 instance = null;
    private static Connection conn = null;

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

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

        return instance;
    }

    protected ArrayList<XbMappingObj> getMappingInfo(XbContextObj conObj, String groupId, String entity, String unit) throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;
            StringBuffer  strb_sql = new StringBuffer();

            // メインリスト
            strb_sql.append("select ");
            strb_sql.append("sort.item_order as item_order ");
            strb_sql.append(", map.taxonomy_item_id as taxonomy_item_id ");
            strb_sql.append(", sort.item_type as item_type ");
            strb_sql.append(", sort.parent_taxonomy_item_id as parent_taxonomy_item_id ");
            strb_sql.append(", sort.parent_item_type as parent_item_type ");
            strb_sql.append(", sort.context_type as context_type ");
            strb_sql.append(", item.item_value as item_value ");
            strb_sql.append(", item.entity as entity ");
            strb_sql.append(", item.period as period ");
            strb_sql.append(", item.unit as unit ");
            strb_sql.append(", foot.parent_etc_id as parent_etc_id ");
            strb_sql.append(", attr.value as value ");
            strb_sql.append("from ");
            strb_sql.append("(((xb_mapping map ");
            strb_sql.append("inner join xb_item sort ");
            strb_sql.append("on map.taxonomy_group_id = sort.taxonomy_group_id ");
            strb_sql.append("and map.taxonomy_item_id = sort.taxonomy_item_id ");
            strb_sql.append("and sort.parent_item_type != 'tuple') ");
            strb_sql.append("inner join xb_item_interface item ");
            strb_sql.append("on map.user_item_id = item.user_item_id) ");
            strb_sql.append("left outer join xb_item_footnote foot ");
            strb_sql.append("on map.taxonomy_group_id = foot.main_id ");
            strb_sql.append("and map.etc_id = foot.etc_id) ");
            strb_sql.append("left outer join xb_etc_attributes attr ");
            strb_sql.append("on foot.etc_id = attr.etc_id ");
            strb_sql.append("and attr.etc_type = 3 ");
            strb_sql.append("and attr.name = 'href' ");
            strb_sql.append("where ");
            strb_sql.append("map.taxonomy_group_id = ? ");
            strb_sql.append("and item.entity = ? ");
            strb_sql.append("and item.period >= ? ");
            strb_sql.append("and item.period <= ? ");
            strb_sql.append("and item.unit = ? ");
            strb_sql.append("and sort.context_type = ? ");
            strb_sql.append("group by sort.item_order, map.taxonomy_item_id, sort.item_type, sort.parent_taxonomy_item_id, sort.parent_item_type, sort.context_type, item.item_value, item.entity, item.period, item.unit, foot.parent_etc_id, attr.value");
            // SQL設定
            stmt = conn.prepareStatement(strb_sql.toString());
            int index = 1;

            stmt.setString(index++, groupId);
            stmt.setString(index++, entity);
            stmt.setString(index++, conObj.getPeriodStartDate());
            stmt.setString(index++, conObj.getPeriodEndDate());
            stmt.setString(index++, unit);
            stmt.setString(index++, conObj.getContextType());

            ResultSet rs = stmt.executeQuery();

            ArrayList<XbMappingObj> relist = new ArrayList<XbMappingObj>();
            while(rs.next()) {
                // サブリスト
                XbMappingObj obj = new XbMappingObj();
                obj.setItemOrder(rs.getString("item_order"));
                obj.setTaxonomyItemId(rs.getString("taxonomy_item_id"));
                obj.setItemType(rs.getString("item_type"));
                obj.setParentTaxonomyItemId(rs.getString("parent_taxonomy_item_id"));
                obj.setParentItemType(rs.getString("parent_item_type"));
                obj.setContextType(rs.getString("context_type"));
                obj.setItemValue(rs.getString("item_value"));
                obj.setEntity(rs.getString("entity"));
                obj.setPeriod(rs.getString("period"));
                obj.setUnit(rs.getString("unit"));
                obj.setParentEtcId(rs.getString("parent_etc_id"));
                obj.setValue(rs.getString("value"));
                relist.add(obj);
            }
            // 結果セットをクローズ
            rs.close(); 
            // ステートメントをクローズ
            stmt.close(); 

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