/*
 * 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 XbInterfaceDAO {

    // singleton作成
    private static XbInterfaceDAO taxGrp = null;
    // Connection変数
    private static Connection conn = null;

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

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

        return taxGrp;
    }

    protected void delete() throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;
            StringBuffer  strb_sql = new StringBuffer();
            // 問合せの実行
            strb_sql.append("delete from ");
            strb_sql.append("xb_interface ");

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

            // ステートメントをクローズ
            stmt.close();
        } catch (SQLException e) {
            throw new XbException(e, "SQLError : " + e.getMessage());
        }
    }

    protected void insertItem(XbMappingObj obj, String contextName, String unitName) throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;

    		StringBuffer  strb_sql = new StringBuffer();
    	    // 問合せの実行
        	strb_sql.append("insert into ");
        	strb_sql.append("xb_interface ");
        	strb_sql.append("values ");
        	strb_sql.append("(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

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

    		int index = 1;
    		stmt.setString(index ++, obj.getItemOrder());
            stmt.setString(index ++, obj.getTaxonomyItemId());
            stmt.setString(index ++, "1");
            stmt.setString(index ++, obj.getTaxonomyItemId());
            stmt.setString(index ++, "1");
    		stmt.setString(index ++, obj.getItemValue());
            stmt.setString(index ++, obj.getItemPrecision());
            stmt.setString(index ++, obj.getItemDecimal());
            stmt.setString(index ++, obj.getContextType());
            stmt.setString(index ++, contextName);
            stmt.setString(index ++, unitName);
            stmt.setString(index ++, obj.getValue());

            // SQL　実行
    	   	stmt.executeUpdate();

    	    // ステートメントをクローズ
    	    stmt.close();
        } catch (SQLException e) {
            throw new XbException(e, "SQLError : " + e.getMessage());
        }
	}

    protected void insertTuple(XbTupleDataObj obj, String contextName, String unitName) throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;

            StringBuffer  strb_sql = new StringBuffer();
            // 問合せの実行
            strb_sql.append("insert into ");
            strb_sql.append("xb_interface ");
            strb_sql.append("values ");
            strb_sql.append("(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

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

            int index = 1;
            stmt.setString(index ++, obj.getItemOrder());
            stmt.setString(index ++, obj.getTaxonomyItemId());
            stmt.setString(index ++, obj.getTaxonomyItemSeq());
            stmt.setString(index ++, obj.getParentTaxonomyItemId());
            stmt.setString(index ++, obj.getParentTaxonomyItemSeq());
            stmt.setString(index ++, obj.getItemValue());
            stmt.setString(index ++, obj.getItemPrecision());
            stmt.setString(index ++, obj.getItemDecimal());
            stmt.setString(index ++, obj.getContextType());
            stmt.setString(index ++, contextName);
            stmt.setString(index ++, unitName);
            stmt.setString(index ++, obj.getValue());

            // SQL　実行
            stmt.executeUpdate();

            // ステートメントをクローズ
            stmt.close();
        } catch (SQLException e) {
            throw new XbException(e, "SQLError : " + e.getMessage());
        }
    }

    protected ArrayList<XbInterfaceObj> select() throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;

            StringBuffer  strb_sql = new StringBuffer();
            // 問合せの実行
            strb_sql.append("select ");
            strb_sql.append("iface.item_order as item_order ");
            strb_sql.append(", iface.taxonomy_item_id as taxonomy_item_id ");
            strb_sql.append(", iface.taxonomy_item_seq as taxonomy_item_seq ");
            strb_sql.append(", iface.parent_taxonomy_item_id as parent_taxonomy_item_id ");
            strb_sql.append(", iface.parent_taxonomy_item_seq as parent_taxonomy_item_seq ");
            strb_sql.append(", iface.item_value as item_value ");
            strb_sql.append(", iface.item_precision as item_precision ");
            strb_sql.append(", iface.item_decimal as item_decimal ");
            strb_sql.append(", iface.context_type as context_type ");
            strb_sql.append(", iface.context_name as context_name ");
            strb_sql.append(", iface.unit_name as unit_name ");
            strb_sql.append(", iface.footnote_value as footnote_value ");
            strb_sql.append(", tax.namespace as namespace ");
            strb_sql.append(", tax.namespace_url as namespace_url ");
            strb_sql.append(", item.item_name as item_name ");
            strb_sql.append("from ");
            strb_sql.append("xb_interface iface, xb_taxonomy tax, xb_taxonomy_item item ");
            strb_sql.append("where ");
            strb_sql.append("item.taxonomy_item_id = iface.taxonomy_item_id ");
            strb_sql.append("and tax.taxonomy_id = item.taxonomy_id ");
            strb_sql.append("and iface.taxonomy_item_id = iface.parent_taxonomy_item_id ");
            strb_sql.append("and iface.taxonomy_item_seq = iface.parent_taxonomy_item_seq ");
            strb_sql.append("order by iface.item_order, iface.taxonomy_item_seq, iface.context_name");

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

            ResultSet rs = stmt.executeQuery();

            ArrayList<XbInterfaceObj> relist = new ArrayList<XbInterfaceObj>();
            while(rs.next()) {
                // サブリスト
                XbInterfaceObj obj = new XbInterfaceObj();
                obj.setItemOrder(rs.getString("item_order"));
                obj.setTaxonomyItemId(rs.getString("taxonomy_item_id"));
                obj.setTaxonomyItemSeq(rs.getString("taxonomy_item_seq"));
                obj.setParentTaxonomyItemId(rs.getString("parent_taxonomy_item_id"));
                obj.setParentTaxonomyItemSeq(rs.getString("parent_taxonomy_item_seq"));
                obj.setItemValue(rs.getString("item_value"));
                obj.setItemPrecision(rs.getString("item_precision"));
                obj.setItemDecimal(rs.getString("item_decimal"));
                obj.setContextType(rs.getString("context_type"));
                obj.setContextName(rs.getString("context_name"));
                obj.setUnitName(rs.getString("unit_name"));
                obj.setFootnoteValue(rs.getString("footnote_value"));
                obj.setNamespace(rs.getString("namespace"));
                obj.setNamespaceUrl(rs.getString("namespace_url"));
                obj.setItemName(rs.getString("item_name"));
                relist.add(obj);
            }
            // 結果セットをクローズ
            rs.close(); 
            // ステートメントをクローズ
            stmt.close(); 

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

    protected ArrayList<XbInterfaceObj> select(String parentId, String parentSeq) throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;

            StringBuffer  strb_sql = new StringBuffer();
            // 問合せの実行
            strb_sql.append("select ");
            strb_sql.append("iface.item_order as item_order ");
            strb_sql.append(", iface.taxonomy_item_id as taxonomy_item_id ");
            strb_sql.append(", iface.taxonomy_item_seq as taxonomy_item_seq ");
            strb_sql.append(", iface.parent_taxonomy_item_id as parent_taxonomy_item_id ");
            strb_sql.append(", iface.parent_taxonomy_item_seq as parent_taxonomy_item_seq ");
            strb_sql.append(", iface.item_value as item_value ");
            strb_sql.append(", iface.item_precision as item_precision ");
            strb_sql.append(", iface.item_decimal as item_decimal ");
            strb_sql.append(", iface.context_type as context_type ");
            strb_sql.append(", iface.context_name as context_name ");
            strb_sql.append(", iface.unit_name as unit_name ");
            strb_sql.append(", iface.footnote_value as footnote_value ");
            strb_sql.append(", tax.namespace as namespace ");
            strb_sql.append(", tax.namespace_url as namespace_url ");
            strb_sql.append(", item.item_name as item_name ");
            strb_sql.append("from ");
            strb_sql.append("xb_interface iface, xb_taxonomy tax, xb_taxonomy_item item ");
            strb_sql.append("where ");
            strb_sql.append("item.taxonomy_item_id = iface.taxonomy_item_id ");
            strb_sql.append("and tax.taxonomy_id = item.taxonomy_id ");
            strb_sql.append("and iface.parent_taxonomy_item_id = ? ");
            strb_sql.append("and iface.parent_taxonomy_item_seq = ? ");
            strb_sql.append("and iface.taxonomy_item_id != iface.parent_taxonomy_item_id ");
            strb_sql.append("order by iface.item_order, iface.taxonomy_item_seq, iface.context_name");

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

            stmt.setString(index++, parentId);
            stmt.setString(index++, parentSeq);

            ResultSet rs = stmt.executeQuery();

            ArrayList<XbInterfaceObj> relist = new ArrayList<XbInterfaceObj>();
            while(rs.next()) {
                // サブリスト
                XbInterfaceObj obj = new XbInterfaceObj();
                obj.setItemOrder(rs.getString("item_order"));
                obj.setTaxonomyItemId(rs.getString("taxonomy_item_id"));
                obj.setTaxonomyItemSeq(rs.getString("taxonomy_item_seq"));
                obj.setParentTaxonomyItemId(rs.getString("parent_taxonomy_item_id"));
                obj.setParentTaxonomyItemSeq(rs.getString("parent_taxonomy_item_seq"));
                obj.setItemValue(rs.getString("item_value"));
                obj.setItemPrecision(rs.getString("item_precision"));
                obj.setItemDecimal(rs.getString("item_decimal"));
                obj.setContextType(rs.getString("context_type"));
                obj.setContextName(rs.getString("context_name"));
                obj.setUnitName(rs.getString("unit_name"));
                obj.setFootnoteValue(rs.getString("footnote_value"));
                obj.setNamespace(rs.getString("namespace"));
                obj.setNamespaceUrl(rs.getString("namespace_url"));
                obj.setItemName(rs.getString("item_name"));
                relist.add(obj);
            }
            // 結果セットをクローズ
            rs.close(); 
            // ステートメントをクローズ
            stmt.close(); 

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