/*
 * Copyright 2002-2008 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package anyframe.core.query.impl;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import oracle.jdbc.driver.OracleTypes;

import org.springframework.test.AbstractDependencyInjectionSpringContextTests;

import anyframe.core.query.IQueryService;

/**
 * @author SoYon Lim
 * @author JongHoon Kim
 */
public class QueryServiceCallableStatementTest extends
		AbstractDependencyInjectionSpringContextTests {

	protected IQueryService qs = null;

	protected DataSource ds = null;

	public void setDataSource(DataSource ds) {
		this.ds = ds;
	}

	public void setQueryService(IQueryService QueryService) {
		this.qs = QueryService;
	}

	protected String[] getConfigLocations() {
		setAutowireMode(AbstractDependencyInjectionSpringContextTests.AUTOWIRE_BY_NAME);
		return new String[] { "classpath*:/META-INF/spring/context-*.xml" };
	}

	public void init() throws Exception {
		// Try to drop the table. It may not exist and throw an exception.
		System.out.println("Attempting to drop old table");
		// m_service.updateBySQL("DROP TABLE TBL_RESERVATION", new String[] {},
		// new Object[] {});
		qs.updateBySQL("CREATE OR REPLACE PACKAGE java_refcursor AS "
				+ " type myrctype is ref cursor return EMP%ROWTYPE; "
				+ " function job_listing(j varchar2) return myrctype; "
				+ "end java_refcursor;", new String[] {}, new Object[] {});
		qs.updateBySQL("CREATE OR REPLACE PACKAGE BODY java_refcursor AS "
				+ " function job_listing(j varchar2) return myrctype is "
				+ "    rc myrctype; " + " begin "
				+ "    open rc for select * from emp where job = j; "
				+ "    return rc; " + " end; " + "end java_cursor;",
				new String[] {}, new Object[] {});

		StringBuffer sql = new StringBuffer();
		sql.append("CREATE OR REPLACE PACKAGE TEST_CURSOR AS ");
		sql.append("TYPE dReport IS REF CURSOR; ");
		sql.append("PROCEDURE TEST_PROC( ");
		sql.append("I_NAME IN VARCHAR2, ");
		sql.append("p_RecordSet OUT dReport ");
		sql.append("); ");
		sql.append("END TEST_CURSOR;");
		qs.updateBySQL(sql.toString(), new String[] {}, new Object[] {});

		sql = new StringBuffer();
		sql.append("CREATE OR REPLACE PACKAGE BODY TEST_CURSOR AS ");
		sql.append("PROCEDURE TEST_PROC( ");
		sql.append("I_NAME				  IN VARCHAR2, ");
		sql.append("p_RecordSet           OUT dReport ");
		sql.append(") AS ");
		sql.append("BEGIN ");
		sql.append("IF TRIM(I_NAME) IS NULL THEN ");
		sql.append("OPEN p_RecordSet FOR ");
		sql.append("SELECT 'N/A' NAME, 'BLOCK' STATUS ");
		sql.append("FROM   DUAL; ");
		sql.append("ELSE ");
		sql.append("OPEN p_RecordSet FOR ");
		sql.append("SELECT I_NAME NAME, 'ACTIVE' STATUS FROM DUAL ");
		sql.append("UNION ALL ");
		sql.append("SELECT I_NAME, 'READY' FROM DUAL ");
		sql.append("UNION ALL ");
		sql.append("SELECT I_NAME, 'BLOCK' FROM DUAL; ");
		sql.append("END IF; ");
		sql.append("END TEST_PROC; ");
		sql.append("END TEST_CURSOR; ");
		qs.updateBySQL(sql.toString(), new String[] {}, new Object[] {});
	}

	public void testCallableStatement_Function() {
		try {
			init();
			HashMap inVal = new HashMap();
			inVal.put("in_Val", new Integer(1));
			Map results = qs.execute("callFunction", inVal);
			System.out.println("rtVal Class: "
					+ results.get("out_Val").getClass());
			BigDecimal rtVal = (BigDecimal) results.get("out_Val");

			System.out.println("rtVal : " + results.get("out_Val"));
			assertTrue(rtVal.intValue() == 1);
		} catch (Exception e) {
			fail("fail to execute function.");
		}

	}

	public void testCallableStatementBySQL() {
		try {
			init();
			String sql = "{?=call test_function(?)}";
			String[] types = { "NUMERIC", "NUMERIC" };
			String[] bindings = { "OUT", "IN" };
			String[] names = { "out_val", "in_val" };
			HashMap inVal = new HashMap();
			inVal.put("in_val", new Integer(2));
			Map results = qs.executeBySQL(sql, types, names, bindings, inVal);

			assertEquals("{out_val=1}", results.toString());
		} catch (Exception e) {
			fail("fail to execute callable statement.");
		}
	}

	public void testOraclePackageByJdbc() throws Exception {
		init();

		Connection conn = null;
		ResultSet rs = null;
		CallableStatement statement = null;
		try {
			conn = this.ds.getConnection();
			statement = conn.prepareCall("{call TEST_CURSOR.TEST_PROC(?, ?)}");
			statement.setString(1, "KKN");
			statement.registerOutParameter(2, OracleTypes.CURSOR);
			statement.execute();

			rs = (ResultSet) statement.getObject(2);

			System.out.println(statement.getResultSet());
			while (rs.next()) {
				System.out.print(rs.getString(1) + " , ");
				System.out.println(rs.getString(2));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			rs.close();
			statement.close();
			conn.close();
		}
	}

	public void testOraclePackageByQueryService() throws Exception {
		init();
		HashMap inVal = new HashMap();
		inVal.put("in_Val", "KKN");
		Map results = qs.execute("callOraclePackage", inVal);
		System.out.println("rtVal Class: " + results.get("out_Val").getClass());
		ArrayList rtVal = (ArrayList) results.get("out_Val");

		for (int i = 0; i < rtVal.size(); i++) {
			Map result = (Map) rtVal.get(i);
			System.out.println(result.get("NAME") + " , "
					+ result.get("STATUS"));
		}

		inVal = new HashMap();
		inVal.put("in_Val", null);
		results = qs.execute("callOraclePackage", inVal);
		System.out.println("rtVal Class: " + results.get("out_Val").getClass());
		rtVal = (ArrayList) results.get("out_Val");

		for (int i = 0; i < rtVal.size(); i++) {
			Map result = (Map) rtVal.get(i);
			System.out.println(result.get("NAME") + " , "
					+ result.get("STATUS"));
		}
	}
}