/*
 * 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 jp.co.going.xbrl.common.XbException;

/**
 * @author Going Dot Com Inc.
 */
public class XbLinkbaseRefDAO {

    // singleton作成
    private static XbLinkbaseRefDAO linkRef = null;
    // Connection変数
    private static Connection conn = null;
    // ステートメントを作成
    private PreparedStatement stmt = null;

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

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

        return linkRef;
    }

    protected void insert(XbLinkbaserefObj obj) throws XbException {

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

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

    		int index = 1;
    		stmt.setLong(index++, obj.getLinkbaserefId());
    		stmt.setString(index++, obj.getLinkbaseRole());
    		stmt.setString(index++, obj.getLinkbaseHref());
    		stmt.setLong(index++, obj.getTaxonomyId());
    		stmt.setLong(index++, obj.getTaxonomyGroupId());
            stmt.setString(index++, obj.getCategory());
            stmt.setTimestamp(index++, new java.sql.Timestamp(obj.getCreationDate().getTime()));

            // SQL　実行
    	   	stmt.executeUpdate();

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

    protected long select(String strTaxonomyId, String strCategry) 
        throws XbException {

        try {
            StringBuffer  strb_sql = new StringBuffer();

            // メインリスト
            strb_sql.append("select ");
            strb_sql.append("linkbaseref_id, ");
            strb_sql.append("linkbase_role, ");
            strb_sql.append("linkbase_href, ");
            strb_sql.append("taxonomy_id, ");
            strb_sql.append("taxonomy_group_id, ");
            strb_sql.append("category, ");
            strb_sql.append("creation_date ");
            strb_sql.append("from xb_linkbaseref ");
            strb_sql.append("where taxonomy_id = ? ");
            strb_sql.append("and category like ?");
            // SQL設定
            stmt = conn.prepareStatement(strb_sql.toString());
            int index = 1;

            stmt.setLong(index++, Long.parseLong(strTaxonomyId));
            stmt.setString(index++, strCategry + "%");

            ResultSet rs = stmt.executeQuery();

            long reLinkId = 0;
            if(rs.next()) {
                reLinkId = rs.getLong("linkbaseref_id");
            }

            // 結果セットをクローズ
            rs.close(); 

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

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