package anyframe.core.query.ria.mip.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import anyframe.common.util.DateUtil;
import anyframe.core.query.ria.mip.IMiPActionCommand;
import anyframe.core.query.ria.mip.IMiPQueryService;

import com.tobesoft.platform.data.Dataset;
import com.tobesoft.platform.data.VariableList;
import com.tobesoft.platform.data.Variant;

public class MiPQueryServiceTestGeneral extends AbstractTest {

	/**
	 * 테스트 수행을 위한 main
	 */
	public static void main(String[] args) throws Exception {
		MiPQueryServiceTestGeneral queryTest = new MiPQueryServiceTestGeneral();
		// 1. initialize context
		queryTest.setup();
		// 2. testInsertDataSet();
		queryTest.testInsertDataSet();
		// 4. testProcessAllDataSet();
		queryTest.testProcessAllDataSet();
		// 5. testProcessAllDataSetWithActionCommand();
		queryTest.testProcessAllDataSetWithActionCommand();
		// 6. testFindDataSetWithVariant();
		queryTest.testFindDataSetWithVariant();
		// 3. testUpdateDataSet();
		queryTest.testUpdateDataSet();

		// 7. close context
		queryTest.teardown();

		System.out.println("Successful!!!!!");
	}

	protected void setup() {
		super.setup();
		try {
			testInit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void testInit() throws Exception {
		DataSource dataSource = (DataSource) context
				.getBean("common_datasource");

		try {
			Connection conn = dataSource.getConnection();
			try {
				Statement statement = conn.createStatement();

				try {
					statement.executeUpdate("DROP TABLE TBL_MIP_TEST");
				} catch (SQLException e) {
					System.out.println("Fail to DROP Table.");
				}

				statement.executeUpdate("CREATE TABLE TBL_MIP_TEST ( "
						+ "TEST_CHAR CHAR(10), "
						+ "TEST_VARCHAR2 VARCHAR2(255),"
						+ "TEST_NUMBER NUMBER(13)," + "TEST_DATE DATE" + ")");
			} finally {
				conn.close();
			}
		} catch (SQLException e) {
			System.err.println("Unable to initialize database for test." + e);
		}
	}

	/**
	 * MiPlatform용 Query 서비스를 통해 DB에 신규 데이터를 입력하는 테스트 코드
	 */
	public void testInsertDataSet() throws Exception {
		IMiPQueryService mipQueryService = (IMiPQueryService) context
				.getBean("mipQueryService");

		Map queryMap = new HashMap();
		queryMap.put(IMiPQueryService.QUERY_INSERT, "createMiPQueryService");

		// INSERT, UPDATE, DELETE 유형별로 사용할 query id를 정의한 Map과 DB에 반영해야 할
		// Dataset을 전달. Dataset에는 다수의 데이터를 저장할 수 있음.
		int resultInsert = mipQueryService
				.update(queryMap, makeInsertDataSet());

		if (resultInsert != 3)
			throw new Exception("MiPlatform Dataset Insert failed");
	}

	/**
	 * MiPlatform용 Query 서비스를 통해 DB에 입력된 데이터를 수정하는 테스트 코드
	 */
	public void testUpdateDataSet() throws Exception {
		IMiPQueryService mipQueryService = (IMiPQueryService) context
				.getBean("mipQueryService");

		Map queryMap = new HashMap();
		queryMap.put(IMiPQueryService.QUERY_UPDATE, "updateMiPQueryService");

		// INSERT, UPDATE, DELETE 유형별로 사용할 query id를 정의한 Map과 DB에 반영해야 할
		// Dataset을 전달. Dataset에는 다수의 데이터를 저장할 수 있음.
		int resultUpdate = mipQueryService
				.update(queryMap, makeUpdateDataSet());

		if (resultUpdate != 1)
			throw new Exception("MiPlatform Dataset Update failed");
	}

	/**
	 * MiPlatform용 Query 서비스를 통해 DB에 데이터를 입력/수정/삭제하는 테스트 코드
	 */
	public void testProcessAllDataSet() throws Exception {
		IMiPQueryService mipQueryService = (IMiPQueryService) context
				.getBean("mipQueryService");

		Map queryMap = new HashMap();
		queryMap.put(IMiPQueryService.QUERY_UPDATE, "updateMiPQueryService");
		queryMap.put(IMiPQueryService.QUERY_INSERT, "createMiPQueryService");
		queryMap.put(IMiPQueryService.QUERY_DELETE, "deleteMiPQueryService");

		// INSERT, UPDATE, DELETE 유형별로 사용할 query id를 정의한 Map과 DB에 반영해야 할
		// Dataset을 전달. Dataset에는 다수의 데이터를 저장할 수 있음.
		int resultUpdate = mipQueryService.update(queryMap, makeAllDataSet());

		if (resultUpdate != 3)
			throw new Exception("MiPlatform Dataset ProcessAll failed");
	}

	/**
	 * MiPlatform용 Query 서비스를 통해 DB에 데이터를 입력/수정/삭제하는 테스트 코드 이때, ActionCommand를
	 * 통해 전후처리 수행
	 */
	public void testProcessAllDataSetWithActionCommand() throws Exception {
		IMiPQueryService mipQueryService = (IMiPQueryService) context
				.getBean("mipQueryService");

		Map queryMap = new HashMap();
		queryMap.put(IMiPQueryService.QUERY_INSERT, "createMiPQueryService");
		queryMap.put(IMiPQueryService.QUERY_UPDATE, "updateMiPQueryService");
		queryMap.put(IMiPQueryService.QUERY_DELETE, "deleteMiPQueryService");

		// INSERT, UPDATE, DELETE 유형별로 사용할 query id를 정의한 Map, DB에 반영해야 할
		// Dataset, DB에 접근하기 전과 후에 처리해야 할 작업을 정의한 ActionCommand를 전달
		// Dataset에는 다수의 데이터를 저장할 수 있음.
		int resultUpdate = mipQueryService.update(queryMap, makeAllDataSet(),
				new IMiPActionCommand() {

					public void postDelete(Dataset record, int currentRow) {
					}

					public void postInsert(Dataset record, int currentRow) {
					}

					public void postUpdate(Dataset record, int currentRow) {
					}

					public void preDelete(Dataset record, int currentRow) {
					}

					public void preInsert(Dataset record, int currentRow) {
						Variant variant = new Variant();
						variant.setObject("Anyframe preUpdate");
						record.setColumn(currentRow, "TEST_VARCHAR2", variant);
					}

					public void preUpdate(Dataset record, int currentRow) {
					}
				});

		if (resultUpdate != 3)
			throw new Exception(
					"MiPlatform Dataset ProcessAll With ActionCommand failed");
	}

	/**
	 * MiPlatform용 Query 서비스를 통해 DB에 데이터를 조회하는 테스트 코드 이때, 검색조건은 VariableList 형태임
	 */
	public void testFindDataSetWithVariant() throws Exception {
		IMiPQueryService mipQueryService = (IMiPQueryService) context
				.getBean("mipQueryService");

		// 실행할 query id와 VariableList 형태의 검색 조건을 전달
		Dataset resultDataSet = mipQueryService.search("findMiPQueryService",
				makeVariantList());
		if (resultDataSet.getRowCount() != 1)
			throw new Exception("MiPlatform VariableList Find failed");
	}

	/**
	 * MiPlatform용 Query 서비스를 통해 DB에 데이터를 조회하는 테스트 코드 이때, 검색조건은 Dataset 형태임
	 */
	public void testFindListDataSet(int expected) throws Exception {
		IMiPQueryService mipQueryService = (IMiPQueryService) context
				.getBean("mipQueryService");

		// 실행할 query id와 Dataset 형태의 검색 조건을 전달
		Dataset resultDataSet = mipQueryService.search(
				"findListMiPQueryService", makeSelectDataSet("%anyframe%"));

		if (resultDataSet.getRowCount() != expected)
			throw new Exception("MiPlatform Dataset Find failed");
	}

	private Dataset makeInsertDataSet() {
		Dataset insertDataSet = new Dataset();
		insertDataSet.setUpdate(true);
		insertDataSet.setDataSetID("anyframe_insert");
		insertDataSet.addStringColumn("TEST_CHAR");
		insertDataSet.addStringColumn("TEST_VARCHAR2");
		insertDataSet.addIntegerColumn("TEST_NUMBER");
		insertDataSet.addDateColumn("TEST_DATE");

		insertDataSet.appendRow();
		Variant variant = new Variant();
		variant.setObject("anyframe00");
		insertDataSet.setColumn(0, "TEST_CHAR", variant);
		variant = new Variant();
		variant.setObject("Anyframe MiPQueryService Test.");
		insertDataSet.setColumn(0, "TEST_VARCHAR2", variant);
		variant = new Variant();
		variant.setObject("12345678");
		insertDataSet.setColumn(0, "TEST_NUMBER", variant);
		variant = new Variant();
		variant.setObject(getDate());
		insertDataSet.setColumn(0, "TEST_DATE", variant);

		insertDataSet.appendRow();
		variant = new Variant();
		variant.setObject("anyframe01");
		insertDataSet.setColumn(1, "TEST_CHAR", variant);
		variant = new Variant();
		variant.setObject("Anyframe MiPQueryService Test.");
		insertDataSet.setColumn(1, "TEST_VARCHAR2", variant);
		variant = new Variant();
		variant.setObject("12345678");
		insertDataSet.setColumn(1, "TEST_NUMBER", variant);
		variant = new Variant();
		variant.setObject(getDate());
		insertDataSet.setColumn(1, "TEST_DATE", variant);

		insertDataSet.appendRow();
		variant = new Variant();
		variant.setObject("anyframe02");
		insertDataSet.setColumn(2, "TEST_CHAR", variant);
		variant = new Variant();
		variant.setObject("Anyframe MiPQueryService Test.");
		insertDataSet.setColumn(2, "TEST_VARCHAR2", variant);
		variant = new Variant();
		variant.setObject("12345678");
		insertDataSet.setColumn(2, "TEST_NUMBER", variant);
		variant = new Variant();
		variant.setObject(getDate());
		insertDataSet.setColumn(2, "TEST_DATE", variant);
		return insertDataSet;
	}

	private Dataset makeUpdateDataSet() {
		Dataset updateDataSet = new Dataset();
		updateDataSet.setDataSetID("anyframe_update");
		updateDataSet.addStringColumn("TEST_CHAR");
		updateDataSet.addStringColumn("TEST_VARCHAR2");
		updateDataSet.addIntegerColumn("TEST_NUMBER");
		updateDataSet.addDateColumn("TEST_DATE");

		updateDataSet.appendRow();
		updateDataSet.setUpdate(true);
		Variant variant = new Variant();
		variant.setObject("anyframe00");
		updateDataSet.setColumn(0, "TEST_CHAR", variant);
		variant = new Variant();
		variant.setObject("Anyframe MiPQueryService Test. - UPDATE");
		updateDataSet.setColumn(0, "TEST_VARCHAR2", variant);
		variant = new Variant();
		variant.setObject("12345678");
		updateDataSet.setColumn(0, "TEST_NUMBER", variant);
		variant = new Variant();
		variant.setObject(getDate());
		updateDataSet.setColumn(0, "TEST_DATE", variant);

		return updateDataSet;
	}

	private Dataset makeAllDataSet() throws Exception {
		Dataset allDataSet = new Dataset();
		allDataSet.setDataSetID("anyframe_all");
		allDataSet.addStringColumn("TEST_CHAR");
		allDataSet.addStringColumn("TEST_VARCHAR2");
		allDataSet.addIntegerColumn("TEST_NUMBER");
		allDataSet.addDateColumn("TEST_DATE");

		// for UPDATE
		allDataSet.appendRow();
		allDataSet.setUpdate(true);
		Variant variant = new Variant();
		variant.setObject("anyframe00");
		allDataSet.setColumn(0, "TEST_CHAR", variant);
		variant = new Variant();
		variant.setObject("Anyframe MiPQueryService Test. - UPDATE");
		allDataSet.setColumn(0, "TEST_VARCHAR2", variant);
		variant = new Variant();
		variant.setObject("12345678");
		allDataSet.setColumn(0, "TEST_NUMBER", variant);
		variant = new Variant();
		variant.setObject(getDate());
		allDataSet.setColumn(0, "TEST_DATE", variant);

		// for INSERT
		allDataSet.appendRow();
		variant = new Variant();
		variant.setObject("anyframe99");
		allDataSet.setColumn(1, "TEST_CHAR", variant);
		variant = new Variant();
		variant.setObject("Anyframe MiPQueryService Test.");
		allDataSet.setColumn(1, "TEST_VARCHAR2", variant);
		variant = new Variant();
		variant.setObject("12345678");
		allDataSet.setColumn(1, "TEST_NUMBER", variant);
		variant = new Variant();
		variant.setObject(getDate());
		allDataSet.setColumn(1, "TEST_DATE", variant);

		// for INSERT
		allDataSet.appendRow();
		allDataSet.setUpdate(true);
		variant = new Variant();
		variant.setObject("anyframe88");
		allDataSet.setColumn(2, "TEST_CHAR", variant);
		variant = new Variant();
		variant.setObject("Anyframe MiPQueryService Test.");
		allDataSet.setColumn(2, "TEST_VARCHAR2", variant);
		variant = new Variant();
		variant.setObject("12345678");
		allDataSet.setColumn(2, "TEST_NUMBER", variant);
		variant = new Variant();
		variant.setObject(getDate());
		allDataSet.setColumn(2, "TEST_DATE", variant);

		allDataSet.printDataset();
		return allDataSet;
	}

	private Dataset makeSelectDataSet(String searchKeyword) {
		Dataset selectDataSet = new Dataset();
		selectDataSet.setDataSetID("anyframe_select");
		selectDataSet.addStringColumn("SEARCH_KEYWORD");

		selectDataSet.appendRow();
		Variant variant = new Variant();
		variant.setObject(searchKeyword);
		selectDataSet.setColumn(0, "SEARCH_KEYWORD", variant);
		return selectDataSet;
	}

	private VariableList makeVariantList() {
		VariableList variableList = new VariableList();
		Variant variant = new Variant();
		variant.setObject("anyframe00");
		variableList.addVariable("SEARCH_KEYWORD", variant);
		return variableList;
	}

	private Timestamp getDate() {
		return DateUtil.string2Timestamp("2008-12-01", "yyyy-MM-dd");
	}

	protected String[] getConfigLocations() {
		return new String[] {
				"classpath*:/common/applicationContext-*.xml",
				"classpath*:/services/datasource/applicationContext-datasource-oracle.xml",
				"classpath*:/services/query/applicationContext-query-mip.xml",
				"classpath*:/services/query/applicationContext-query-sqlloader.xml" };
	}
}