package com.radius.common.dao.card;

import static org.junit.Assert.assertTrue;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.csv.CsvDataSetWriter;
import org.dbunit.dataset.excel.XlsDataSet;
import org.dbunit.dataset.xml.XmlDataSet;
import org.dbunit.ext.mssql.MsSqlDataTypeFactory;
import org.dbunit.operation.DatabaseOperation;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

/**
 * The Class DbInitTest.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:resources/spring-dao.xml" })
@TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = true)
@Transactional
public class DbInitTest {

    /** The data source. */
    @Autowired
    @Qualifier("dataSource")
    private DataSource dataSource;

    /**
     * Pre load.
     */
    @BeforeClass
    static public void preLoad() {
    }

    /**
     * Gets the connection.
     *
     * @return the connection
     * @throws Exception the exception
     */
    private IDatabaseConnection getConnection() throws Exception {
        // get connection
        Connection con = dataSource.getConnection();
        DatabaseMetaData databaseMetaData = con.getMetaData();
        // oracle schema name is the user name
        IDatabaseConnection connection = new DatabaseConnection(con);
        // IDatabaseConnection connection = new
        // DatabaseConnection(con,databaseMetaData.getUserName().toLowerCase());
        // IDatabaseConnection connection = new
        // DatabaseConnection(con,databaseMetaData.getUserName().toUpperCase(),true);
        DatabaseConfig config = connection.getConfig();
        // oracle 10g
       
        // ms sql
        config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
                new MsSqlDataTypeFactory());
        // receycle bin
        // config.setFeature(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES,
        // Boolean.TRUE);
        return connection;
    }

    /**
     * Export xls.
     *
     * @throws Exception the exception
     */
    @Test
    public void exportXls() throws Exception {
        String[] strs = { "person", "test_data"};
        this.exportXlsFrom(strs);
        assertTrue(new File("c://test/card.xls").exists());
    }

    /**
     * Export xls from.
     *
     * @param tableNames the table names
     * @throws Exception the exception
     */
    private void exportXlsFrom(String... tableNames) throws Exception {
        IDataSet dataSet = getConnection().createDataSet(tableNames);
        CsvDataSetWriter.write(dataSet, new File("c://test"));
        XmlDataSet.write(dataSet, new FileOutputStream(new File("c://test/test.xml")));
        XlsDataSet.write(dataSet, new FileOutputStream(new File("c://test/test.xls")));
    }

    /**
     * Import xls from.
     *
     * @throws DatabaseUnitException the database unit exception
     * @throws SQLException the sQL exception
     * @throws Exception the exception
     */
    public void importXlsFrom() throws DatabaseUnitException, SQLException,
            Exception {
        // 테스트데이터 입력
        IDataSet dataSet = new XlsDataSet(new FileInputStream("c://test/test.xls"));
        DatabaseOperation.CLEAN_INSERT.execute(getConnection(), dataSet);
    }

    /**
     * Test.
     */
    @Test
    public void test() {
        assertTrue(true);
    }

}

INSERT INTO table SELECT #{key}, #{keyName}, #{keyType}, #{createdDate}
        WHERE NOT EXISTS (SELECT * FROM table WHERE key = #{key})

<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="http://www.springframework.org/schema/task
        http://www.springframework.org/schema/task/spring-task-3.0.xsd">

<task:scheduler id="myScheduler"/>
<task:scheduled-tasks scheduler="myScheduler">
   <task:scheduled ref="pushService" method="excute" fixed-delay="5000"/>
</task:scheduled-tasks>
<bean id="pushService" class="com.PushService" autowire="byName"/>

select top (@pageSize)

            execDate, paymentID

    from tblPayment

    where paymentID not in (

                            select top ((@page - 1) * @pageSize) paymentID

                            from tblPayment

                                ORDER BY paymentID desc

                            )

    ORDER BY paymentID desc

select Row_Number() OVER (ORDER BY paymentID desc) AS rowNum,

            execDate, paymentID

        from

            tblPayment