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

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

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

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

        return instance;
    }

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

            // メインリスト
            strb_sql.append("select ");
            strb_sql.append("la.taxonomy_group_id as taxonomy_group_id, ");
            strb_sql.append("la.taxonomy_item_id as taxonomy_item_id, ");
            strb_sql.append("la.label_lang as item_lang, ");
            strb_sql.append("la.resource_value as item_name ");
            strb_sql.append("from ");
            strb_sql.append("xb_item_label la, ");
            strb_sql.append("(select ");
            strb_sql.append("wk1.taxonomy_group_id, ");
            strb_sql.append("wk1.parent_taxonomy_item_id as parent_taxonomy_item_id ");
            strb_sql.append("from xb_item_work wk1 ");
            strb_sql.append("where not exists ( ");
            strb_sql.append("select wk2.taxonomy_item_id ");
            strb_sql.append("from xb_item_work wk2 ");
            strb_sql.append("where wk2.taxonomy_item_id = wk1.parent_taxonomy_item_id) ");
            strb_sql.append("group by wk1.taxonomy_group_id,wk1.parent_taxonomy_item_id) top ");
            strb_sql.append("where la.taxonomy_group_id = ? ");
            strb_sql.append("and top.parent_taxonomy_item_id = la.taxonomy_item_id");
            // SQL設定
            stmt = conn.prepareStatement(strb_sql.toString());
            int index = 1;

            stmt.setLong(index++, grpId);

            ResultSet rs = stmt.executeQuery();

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

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

    private void insert(ArrayList<XbItemTopObj> obj)  throws XbException {
        try {
            for (int i = 0;i < obj.size();i ++) {
                XbItemTopObj label = obj.get(i);

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

                StringBuffer  strb_sql = new StringBuffer();
                // 問合せの実行
                strb_sql.append("insert into ");
                strb_sql.append("xb_item_top (");
                strb_sql.append("taxonomy_group_id, ");
                strb_sql.append("taxonomy_item_id, ");
                strb_sql.append("item_lang, ");
                strb_sql.append("item_name) ");
                strb_sql.append("values ");
                strb_sql.append("(?, ?, ?, ?)");

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

                int index = 1;
                stmt.setString(index++, label.getTaxGrpId());
                stmt.setString(index++, label.getItemId());
                stmt.setString(index++, label.getLang());
                stmt.setString(index++, label.getValue());

                // SQL　実行
                stmt.executeUpdate();

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

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

    protected ArrayList<XbItemTopObj> setItemTop(long grpId) throws XbException {
        // TOPアイテムを取得
        ArrayList<XbItemTopObj> itemTop = this.getItemTop(grpId);
        // TOPアイテムを登録
        this.insert(itemTop);

        return itemTop;
    }
}