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

    // singleton作成
    private static XbItemOrderDAO instance = null;
    // Connection変数
    private static Connection conn = null;
    // orderID
    private int orderId;

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

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

        return instance;
    }

    private void getItemChild(String grpId, String itemId, String lang) throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;
            StringBuffer  strb_sql = new StringBuffer();

            // メインリスト
            strb_sql.append("select ");
            strb_sql.append("wk.taxonomy_group_id as taxonomy_group_id, ");
            strb_sql.append("wk.taxonomy_id as taxonomy_id, ");
            strb_sql.append("wk.taxonomy_item_id as taxonomy_item_id, ");
            strb_sql.append("wk.item_type as item_type, ");
            strb_sql.append("wk.parent_taxonomy_item_id as parent_taxonomy_item_id, ");
            strb_sql.append("wk.parent_item_type as parent_item_type, ");
            strb_sql.append("wk.context_type as context_type, ");
            strb_sql.append("wk.presentation_order as presentation_order, ");
            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_work wk, xb_item_label la ");
            strb_sql.append("where ");
            strb_sql.append("wk.taxonomy_group_id = ? ");
            strb_sql.append("and parent_taxonomy_item_id = ? ");
            strb_sql.append("and la.label_lang = ? ");
            strb_sql.append("and wk.taxonomy_item_id = la.taxonomy_item_id ");
            strb_sql.append("order by presentation_order");
            // SQL設定
            stmt = conn.prepareStatement(strb_sql.toString());
            int index = 1;

            stmt.setString(index++, grpId);
            stmt.setString(index++, itemId);
            stmt.setString(index++, lang);

            ResultSet rs = stmt.executeQuery();

            while(rs.next()) {
                // サブリスト
                XbItemOrderObj obj = new XbItemOrderObj();
                obj.setItemOrder(orderId ++);
                obj.setTaxGrpId(rs.getString("taxonomy_group_id"));
                obj.setTaxId(rs.getString("taxonomy_id"));
                obj.setItemId(rs.getString("taxonomy_item_id"));
                obj.setItemType(rs.getString("item_type"));
                obj.setPItemId(rs.getString("parent_taxonomy_item_id"));
                obj.setPItemType(rs.getString("parent_item_type"));
                obj.setContextType(rs.getString("context_type"));
                obj.setPOrder(rs.getString("presentation_order"));
                obj.setLang(rs.getString("item_lang"));
                obj.setValue(rs.getString("item_name"));

                // 並び順を登録
                this.insert(obj);
                // 再帰処理
                this.getItemChild(obj.getTaxGrpId(), obj.getItemId(), obj.getLang());
            }
            // 結果セットをクローズ
            rs.close(); 
            // ステートメントをクローズ
            stmt.close(); 
        } catch (SQLException e) {
            throw new XbException(e, "SQLError : " + e.getMessage());
        }
    }

    private void insert(XbItemOrderObj obj)  throws XbException {
        try {
            // ステートメントを作成
            PreparedStatement stmt;

            StringBuffer  strb_sql = new StringBuffer();
            // 問合せの実行
            strb_sql.append("insert into ");
            strb_sql.append("xb_item (");
            strb_sql.append("item_order, ");
            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("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.setInt(index++, obj.getItemOrder());
            stmt.setString(index++, obj.getTaxGrpId());
            stmt.setString(index++, obj.getTaxId());
            stmt.setString(index++, obj.getItemId());
            stmt.setString(index++, obj.getItemType());
            stmt.setString(index++, obj.getPItemId());
            stmt.setString(index++, obj.getPItemType());
            stmt.setString(index++, obj.getContextType());
            stmt.setString(index++, obj.getPOrder());
            stmt.setString(index++, obj.getLang());
            stmt.setString(index++, obj.getValue());

            // SQL　実行
            stmt.executeUpdate();

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

    protected void setItemOrder(ArrayList<XbItemTopObj> obj) throws XbException {
        // 登録されている言語毎にアイテムを並べる
        for (int i = 0;i < obj.size();i ++) {
            orderId = 1;
            this.getItemChild(obj.get(i).getTaxGrpId(), obj.get(i).getItemId(), obj.get(i).getLang());
        }
    }
}