package com.sun.sqlloader.api.impl;

import java.io.BufferedReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.sun.sqlloader.api.ISqlLoadService;
@Service(value = "sqlLoadService")
public class SqlLoadServiceImpl implements ISqlLoadService{

	private Logger logger = LoggerFactory.getLogger(SqlLoadServiceImpl.class);
	
	@Value("${user}")
	private String userName;//用户名
	
	@Value("${pwd}")
	private String pwd;//密码
	
	@Value("${database}")
	private String Database;
	
	@Override
	public void importData(String filePath, String tableName, String fieldName) {
		String ctlfileName = filePath.substring(0, filePath.lastIndexOf("\\") + 1) + genLogName() + ".ctl";
		this.ctlFileWriter(filePath, tableName, fieldName, ctlfileName );
		
		logger.info("------>执行系统导入命令操作...");
		this.executive(userName, pwd, Database, filePath, ctlfileName);
	}

	@Override
	public void ctlFileWriter(String filePath,String tableName,String fieldName,String ctlfileName) {
		FileWriter fw = null;
        String strctl = "OPTIONS (skip=0)" +
        " LOAD DATA CHARACTERSET AL32UTF8 INFILE '"+filePath+"'" +   //设置字符集编码
        " APPEND INTO TABLE "+tableName+"" +
        " FIELDS TERMINATED BY ','" +  //分隔符号
        " OPTIONALLY  ENCLOSED BY \"'\"" +
        " TRAILING NULLCOLS "+fieldName+"";
         try {
            fw = new FileWriter(ctlfileName);
            logger.info("--------->生成控制文件操作...");
            fw.write(strctl);
         } 
         catch (IOException e){
               e.printStackTrace();
        }
        finally {
            try {
                fw.flush();
                fw.close();
            } 
            catch (IOException e){
                e.printStackTrace();
            }
            
        }
	}

	@Override
	public void executive(String userName, String pwd, String Database, String filePath, String ctlfileName) {
		InputStream ins = null;
        //要执行的DOS命令
		String logName = filePath.substring(0, filePath.lastIndexOf("\\") + 1) + genLogName() + ".log";
        String dos="sqlldr "+userName+"/"+pwd+"@"+Database+" control="+ctlfileName+" log="+logName+"";
        logger.info("logName:" + logName + ",filePath:" + filePath + ",dos:" + dos);
        try{
            Process process = Runtime.getRuntime().exec(dos);
            ins = process.getInputStream(); // 获取执行cmd命令后的信息
            BufferedReader reader = new BufferedReader(new InputStreamReader(ins,Charset.forName("UTF-8")));
            String line = null;
            while (null != (line = reader.readLine())){
               logger.info("调用dos的执行结果:" + line);
            }
            int exitValue = process.waitFor();
            if(exitValue==0){
                logger.info("返回值：" + exitValue+"\n数据导入成功");
            }else{
                logger.info("返回值：" + exitValue+"\n数据导入失败");
            }
            process.getOutputStream().close(); // 关闭
        }
        catch (Exception e){
            e.printStackTrace();
        }
	}
	
	/**
	 * 生成日志文件名称
	* @Title: genLogName 
	* @author sunt  
	* @date 2017年12月22日
	* @return String
	 */
	public String genLogName() {
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		return df.format(new Date());
	}
	
	@Test
	public void test() {
		this.importData("E:\\Books\\sqlloader.txt", "T_SQLLOADER", "(F_ID,F_MOBILE\"'13830604244'\",F_ACTIVITY_CODE \"'testcCode'\")");
	}

}