/*
 * DB 쿼리후 결과를 화일로 저장 하는 프로그램
 * Secheong Park
 * cozyhill@hanmail.net
 */
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Locale;
import java.util.Map;
import java.util.TimeZone;

/** Class for only management connecting to database */
public class GeneratorForQuery{

 public Connection con; 
 
 private Map queryMap = new HashMap();  
 
 
 private int sucessCnt = 0;
 private String workDate;
 
 public GeneratorForQuery(String workDate){  
  try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
  }catch(ClassNotFoundException cfe){cfe.printStackTrace();}
  
  this.workDate = workDate; //날짜설
  
  setQueryMap();
  System.out.println("########### KeyMap: "+ queryMap.keySet());
  System.out.println("########### KeyMapSize: "+ queryMap.size());
  
  
  System.out.println("########### workDate: "+ workDate);
 }
 
 /** Method for connectiong to database */
 public void connect(){
  String url="jdbc:oracle:thin:@localhost:ORCL";
  String id="scott";
  String pw="tiger";
  try{
   con=DriverManager.getConnection(url, id, pw);
   con.setAutoCommit(false);   
  }catch(SQLException sqle){sqle.printStackTrace();}
 }

 /** Method for disconnectiong from database */
 public void disconnect() throws SQLException, Exception{
  try{
   if(con!=null&&!con.isClosed()){
    con.commit();
    con.close();  
   }    
  }catch(SQLException se) {
   se.printStackTrace();
   System.out.println("SQLException : Error in con.close()");
   throw new SQLException(se.getMessage() + "\nSQLException : Error in con.close()");   
  } catch(Exception e) {
   e.printStackTrace();   
   throw new Exception(e.getMessage() + "\nException : Error in disconnect()");     
  }          
 } 
 
 public void commit() throws SQLException, Exception{
  try{  
     if (con!=null) {
      con.commit();
     }
  }catch(SQLException se) {
   System.out.println("SQLException : Error in con.commit()");
   throw new SQLException(se.getMessage() + "\nSQLException : Error in con.commit()");   
  } catch(Exception e) {
   e.printStackTrace();   
   throw new Exception(e.getMessage() + "\nException : Error in con.commit()");     
  }     
 }
   
   public void rollback() throws SQLException, Exception{
     try{     
     if (con!=null) {  
    con.rollback();   
     }
     }catch(SQLException se) {
   System.out.println("SQLException : Error in con.rollback()");
   throw new SQLException(se.getMessage() + "\nSQLException : Error in con.rollback()");   
  } catch(Exception e) {
   e.printStackTrace();   
   throw new Exception(e.getMessage() + "\nException : Error in con.rollback()");     
  }
   } 

 //Method for a string variable of SQL
 public String sql(String str){
  return "'"+str+"'";
 }

 /**
  * 쿼리맵을 설정 한다.
  *
  */
 public void setQueryMap(){
  //쿼리 1
  String QUERY1 = "select 1 from dual";
  
  //쿼리 2
  String QUERY2 = "select 2 from dual";
  
  //쿼리3
  String QUERY3 = "select 3 from dual";
   
  queryMap.put("QUERY1", QUERY1);
  queryMap.put("QUERY2", QUERY2);
  queryMap.put("QUERY3", QUERY3);
  
 }
 
 /**
  * 시스템 날자를 yyyyMMdd형식으로 읽어온다.
  *
  * @return String yyyyMMdd형식의 문자열
  */
 public static String getDate() {
  TimeZone tz = TimeZone.getDefault();
  // tz.setRawOffset((60 * 60 * 1000) * 9);
  // TimeZone.setDefault(tz);

  SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
  Calendar cal = Calendar.getInstance(tz);
  java.util.Date currentTime_1 = cal.getTime();
  currentTime_1.setTime(currentTime_1.getTime() + ((long) -1 * 1000 * 60 * 60 * 24));
  String dateString = formatter.format(currentTime_1);

  return dateString;
 }
 
 /**
  * check date string validation with the default format "yyyyMMdd".
  *
  * @param s
  *            date string you want to check with default format "yyyyMMdd".
  * @return date Date
  */
 public static Date checkDate(String s) throws ParseException {
  return checkDate(s, "yyyyMMdd");
 }

 /**
  * check date string validation with an user defined format.
  *
  * @param s
  *            date string you want to check.
  * @param format
  *            string representation of the date format. For example,
  *            "yyyy/MM/dd".
  * @return date Date
  */
 public static Date checkDate(String s, String format) throws ParseException {
  if (s == null)
   throw new ParseException("date string to check is null", 0);
  if (format == null)
   throw new ParseException("format string to check date is null", 0);

  SimpleDateFormat formatter = new SimpleDateFormat(format, Locale.KOREA);
  Date date = null;
  try {
   date = formatter.parse(s);
  } catch (ParseException e) {
   /*
    * throw new ParseException( e.getMessage() + " with format \"" +
    * format + "\"", e.getErrorOffset() );
    */
   throw new ParseException(" wrong date:\"" + s + "\" with format \""
     + format + "\"", 0);
  }

  if (!formatter.format(date).equals(s))
   throw new ParseException("Out of bound date:\"" + s
     + "\" with format \"" + format + "\"", 0);
  return date;
 }
 
 public void test(){
  Statement stmt = null;
  ResultSet rset = null;  
  try { 
   connect();
   stmt = con.createStatement(); 
   rset=stmt.executeQuery("Select count(*) from tab");
   rset.next();   
   System.out.println("테이블수:" + rset.getInt(1));
   disconnect();
  } catch(Exception se) {
   se.printStackTrace();      
  } finally {
   try{
    if(rset!=null) rset.close();
    if(stmt!=null) stmt.close();    
   }catch(SQLException sqle){sqle.printStackTrace();} 
  }
 }

 /** 주어진 길이(len)에서 문자열(s)의 길이를 제외한 우측을 특정 character(padc)로 채워준다. */
    public String rpad( String s, int len, char padc ) {
        int rlen = s.getBytes().length;
        if ( rlen >= len ) {
            return s;
        }
        StringBuffer sb = new StringBuffer(s);
        for ( int i = rlen; i < len; i++ )
            sb.append(padc);
        return sb.toString();
    }
   
 private void makeFile(String key){
  Statement stmt = null;  
  ResultSet rset = null;
  ResultSetMetaData rsmd = null; 
  FileOutputStream fos = null;
  int rowCount = 0;
  String query = null;
  try {   
   query = (String) queryMap.get(key); //쿼리를 가져옴
   
   if(query==null||query.length()==0){
    throw new Exception("Key: "+ key + "가 존재하지 않습니다.");
   }
   
   String fileName = key + "_" + workDate.substring(2);
   if("RIC011P".equals(key)){
    fileName += "_01";
   }
   fileName += ".dat";   
    
   stmt = con.createStatement();   
   rset = stmt.executeQuery(query);
   rsmd = rset.getMetaData();
   
   fos = new FileOutputStream("/AA/data/"+ fileName);   
   
   System.out.println("########### Key: "+ key);
   System.out.println("########### query: "+ query);
   System.out.println("########### Column Count: "+ rsmd.getColumnCount());
   
   while(rset.next()){
    
    for(int i=0;i<rsmd.getColumnCount();i++){
     if(i!=0){
      fos.write("|".getBytes());
     }
     if(rset.getString(i+1)!=null){
      fos.write(rset.getString(i+1).getBytes());
     }     
    }    
    fos.write("\n".getBytes());
    rowCount ++;
   }
   
   System.out.println("########### Row Count: " + rowCount);   
   System.out.println("########### FileName 생성: " + fileName);
   
   sucessCnt++; //성공갯수
   
  } catch(Exception se) {
   se.printStackTrace(); 
   System.out.println(query);
  } finally {
   try{    
    if(fos!=null) fos.close();
    if(rset!=null) rset.close();
    if(stmt!=null) stmt.close(); 
   }catch(IOException ioe){    
    ioe.printStackTrace();
   }catch(SQLException sqle){
    sqle.printStackTrace();
   } 
  }
 }
 
 //데이터화일들을 생성한다.
 private void makeFiles() throws SQLException, Exception{
  Iterator iterator  = queryMap.keySet().iterator();
  
  while(iterator.hasNext()){
   makeFile((String)iterator.next());   
   System.out.println("------------------------------------------------");   
  }
 
 }
 
 public void processData(String[] agrs) throws SQLException, Exception{
  connect();
  for(int i=1;i<agrs.length;i++){
   makeFile(agrs[i]);
  }
  disconnect();
 }
 
 public void processAllData() throws SQLException, Exception{
  connect();
  makeFiles();
  disconnect();
 }
 /**
  * 파라미터 없이 실행시 전부 생성 어제날짜로
  * 파리미터 입력시 첫번째 파라미터에 날짜 입력 그외 파라미터는 생성하고자 하는 화일명들 입력
  * 파라미터가 하나인 경우는 해당날짜로 전부 생성
  * @param agrs
  * @throws Exception
  */
 public static void main(String[] agrs) throws Exception{
  int reqCnt = 0;
  
  GeneratorForQuery rm;  
  //rm.test();
  
  if(agrs==null || agrs.length == 0){
   rm = new GeneratorForQuery(getDate());
   rm.processAllData();
   reqCnt = rm.queryMap.size();
  }else if(agrs.length == 1){
   checkDate(agrs[0]); //날짜검증
   rm = new GeneratorForQuery(agrs[0]);
   rm.processAllData();
   reqCnt = rm.queryMap.size();
  }else {
   checkDate(agrs[0]); //날짜검증
   rm = new GeneratorForQuery(agrs[0]);   
   rm.processData(agrs);
   reqCnt = agrs.length -1;
  }
  
  System.out.println("########### 요청: "+ reqCnt + " 성공: " + rm.sucessCnt + " 실패: " + (reqCnt - rm.sucessCnt));
  System.out.println("########### 처리완료 ###########");
 }
 
}