/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package reportbuilder;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 *
 * @author polwa
 */
public class ConvertOra2Mysql {
    public static String currentRunningTime =(new SimpleDateFormat("yyyyMMddHHmmss")).format(Calendar.getInstance().getTime()); 
    public static ConnectionIndex conn;
    public static String xmlConnectionString="",xmlConnectionStringOra="",xmlConnectionStringMysql="";
    public static int xmlConnectionStringOraIndex=0,xmlConnectionStringMysqlIndex=1;
    public static String tables="";
    public static boolean schemas_flag=true;
    public static boolean data_flag=false;
    public static boolean print_sql_flag=false;
    public static int numdata=50;
    public static Connection connOra,connMy;
    public static List tablesList,tablesColsList,tablesColsStringList,tablesColsStringInsertList,tablesPkList;
    public static List tablesMyDropList,tablesMyCreateScriptList,tablesMyDataDeleteList,tablesMyDataInsertList;
    private static void writeLogs(String data, Boolean append){
      writeLogs(data,append,false);
    }
    private static void writeLogs(String data, Boolean append,Boolean echoFlag){
      try{     
        String currentTime =(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(Calendar.getInstance().getTime());  
        String filepath=ReportBuilder.gcoopAllPath + ReportBuilder.coreName + "\\MYSQL\\"+"convert_mysql_"+tables+"_"+currentRunningTime+".log";
        if(append.booleanValue()==false)System.out.println("Create Log File:"+filepath);
        data=currentTime+"|"+data;
        boolean writeLog=false;
        if(echoFlag){
            System.out.println(data);
            writeLog=true;
        }else{ 
         if(print_sql_flag){
             System.out.println(data);
             writeLog=true;
         }              
        }            
        if(writeLog){
            File file = new File(filepath);
            Writer writer = new BufferedWriter(new FileWriter(file.getAbsoluteFile(), append));
            PrintWriter out = new PrintWriter(writer);
            try {
                out.println(new StringBuilder(data));           
                out.flush();
                writer.flush();
            } finally {
                writer.close();
                out.close();
            }
        }
     }catch(Exception e){
        e.printStackTrace();
     }
    }
    public static void getSrcTables(Connection con)throws Exception{
        
            //System.out.println("Get Oracle Tables ");
            writeLogs(("Get Oracle Tables "),true,true);
            Statement stmt = con.createStatement();
            String sql="select * from tab where ( tname not like '%$%' and tname not like '%PB%'  ) and  lower(tname) like '" + tables.toLowerCase() + "%'";
            writeLogs(sql,true);
            ResultSet rs = stmt.executeQuery(sql);
            tablesList =new ArrayList();
            tablesColsList = new ArrayList();
            tablesColsStringList = new ArrayList();
            tablesColsStringInsertList = new ArrayList();
            tablesPkList = new ArrayList();
            tablesMyDropList = new ArrayList();
            tablesMyCreateScriptList = new ArrayList();
            tablesMyDataDeleteList = new ArrayList();
            for (int i=0;rs.next();i++) {
                try{
                    tablesList.add(rs.getString("TNAME"));
                    tablesMyDropList.add("DROP TABLE "+rs.getString("TNAME"));
                    tablesMyDataDeleteList.add("TRUNCATE "+rs.getString("TNAME"));
                    //System.out.println("TNAME ("+(i+1)+"): "+tablesList.get(i)+" Convert to Mysql =>");
                    writeLogs(("TNAME ("+(i+1)+"): "+tablesList.get(i)+" Convert to Mysql =>"),true,true);
                    tablesMyCreateScriptList.add(getColumnsList(i,tablesList.get(i).toString(),con));  
                }catch(Exception e){
                    e.printStackTrace();
                    writeLogs(e.getMessage(),true,true);
                    if(tablesList.get(i)!=null)tablesList.remove(i);
                    if(tablesMyDropList.get(i)!=null)tablesMyDropList.remove(i);
                    if(tablesMyDataDeleteList.get(i)!=null)tablesMyDataDeleteList.remove(i);
                    if(tablesMyCreateScriptList.get(i)!=null)tablesMyCreateScriptList.remove(i);
                }
            }
            rs.close();
            //System.out.println("TABLES : ["+tablesList.size()+"]");
            writeLogs("TABLES : ["+tablesList.size()+"]",true,true);
    }
    
    public static void postTargetTables(Connection con)throws Exception{
            //System.out.println("Post Mysql Tables ");
            writeLogs(("Post Mysql Tables "),true,true);
            for(int i=0;i<tablesList.size();i++){
                try{  
                  //System.out.println("TABLE : "+tablesList.get(i));
                  writeLogs(("TABLE : "+tablesList.get(i)),true,true);
                  Statement stmt = con.createStatement();
                  String sqlDrop=tablesMyDropList.get(i).toString();
                  String sqlCreate=tablesMyCreateScriptList.get(i).toString();
                  writeLogs(sqlDrop,true);
                  try{stmt.executeUpdate(sqlDrop);}catch(Exception e){writeLogs(e.getMessage(),true,true);}
                  writeLogs(sqlCreate,true);
                  try{stmt.executeUpdate(sqlCreate);}catch(Exception e){e.printStackTrace();writeLogs(e.getMessage(),true,true);}
                }catch(Exception e){
                    e.printStackTrace();
                    writeLogs(e.getMessage(),true,true);
                }             
            }
    }
    
    public static void get2postTablesData(Connection conOra,Connection conMy)throws Exception{
            //System.out.println("Get Oracle Tables Data ");
            writeLogs(("Get Oracle Tables Data "),true,true);
            int fetch_rows=numdata;
            for(int i=0;i<tablesList.size();i++){
             try{
                //System.out.println("TABLE : "+tablesList.get(i));
                writeLogs(("TABLE : "+tablesList.get(i)),true,true);
                Statement stmt = conOra.createStatement();                
                Statement stmtmy = conMy.createStatement();
                String columnsSelect=tablesColsStringList.get(i).toString();
                String columnsInsert=tablesColsStringInsertList.get(i).toString();
                List columnsDesc =(ArrayList)tablesColsList.get(i);
                int j=0;long rows=0;
                String sqldelete=tablesMyDataDeleteList.get(i).toString();
                writeLogs(sqldelete,true);
                try{stmtmy.executeUpdate(sqldelete);}catch(Exception e){e.printStackTrace();}
                String sqlSelect="select count(*) as CNT from "+tablesList.get(i)+"";
                writeLogs(sqlSelect,true);
                ResultSet rs = stmt.executeQuery(sqlSelect);
                if(rs.next())rows=rs.getLong("CNT");
                //System.out.println("TABLE : "+tablesList.get(i)+"="+rows+" rows");
                writeLogs(("TABLE : "+tablesList.get(i)+"="+rows+" rows"),true,true);
                sqlSelect="select * from ( select rownum rnum,"+columnsSelect+" from "+tablesList.get(i)+" where rownum <="+(j+fetch_rows)+" ) where rnum >"+j+"" ;
                writeLogs(sqlSelect,true);
                rs = stmt.executeQuery(sqlSelect);
                boolean found=rs.next();
                String sqlInsert="insert into "+tablesList.get(i)+"("+columnsInsert+")VALUES ";
                while(found){
                 for (int k=0;found;k++) {
                    String sqlInsertVals="(";
                    for(int m=0;m<columnsDesc.size();m++){
                        List columnsDescription=(ArrayList)columnsDesc.get(m);
                        String columnDescName=columnsDescription.get(1).toString();
                        String columnDescType=columnsDescription.get(10).toString();
                        //System.out.println(columnDescName+"===="+columnDescType);
                        if(rs.getString(columnDescName)==null){
                          sqlInsertVals+="NULL,"; 
                        }else{
                            if(columnDescType.toLowerCase().indexOf("datetime")>=0){
                                sqlInsertVals+="STR_TO_DATE('"+rs.getString(columnDescName)+"','%Y/%m/%d %T'),";                            
                            }else if(columnDescType.indexOf("decimal")>=0){
                                sqlInsertVals+="'"+new DecimalFormat("##############.########").format(rs.getBigDecimal(columnDescName))+"',";
                            }else{
                                sqlInsertVals+="'"+rs.getString(columnDescName)+"',";
                            }
                        }
                        //System.out.println(sqlInsertVals);
                    }
                    sqlInsertVals=sqlInsertVals.substring(0,sqlInsertVals.length()-1)+"";
                    sqlInsertVals+="),";    
                    sqlInsert+=sqlInsertVals;
                    //sqlInsert=sqlInsert.replaceAll("[,)]", ")");
                    found=rs.next();
                 }
                rs.close(); 
                sqlInsert=sqlInsert.substring(0,sqlInsert.length()-1);
                //sqlInsert=sqlInsert.replaceAll("[\'null\']", "NULL");
                //System.out.println("Post to Mysql Tables="+tablesList.get(i)+" Fetch Data from "+j+" to "+(j+fetch_rows)+" of "+rows+" rows");
                writeLogs(("Post to Mysql Tables="+tablesList.get(i)+" Fetch Data from "+j+" to "+(j+fetch_rows)+" of "+rows+" rows"),true,true);
                writeLogs((sqlInsert),true); 
                try{stmtmy.executeUpdate(sqlInsert);conMy.commit();}catch(Exception e){e.printStackTrace();}
                j+=fetch_rows;
                sqlSelect="select * from ( select rownum rnum,"+columnsSelect+" from "+tablesList.get(i)+" where rownum <="+(j+fetch_rows)+" ) where rnum >"+j+"  ";
                writeLogs((sqlSelect),true);
                rs = stmt.executeQuery(sqlSelect);
                found=rs.next();
                sqlInsert="insert into "+tablesList.get(i)+"("+columnsInsert+")VALUES ";
                }
                rs.close(); 
              }catch(Exception e){
                e.printStackTrace();
                writeLogs(e.getMessage(),true,true);
              }
            }
    }
    
    public static String getColumnsList(int index,String table_name,Connection con)throws Exception{
        
           List columns =new ArrayList();
           List columnsPk =new ArrayList();
           String sql="SELECT column_name,data_type, data_length,DATA_PRECISION,DATA_SCALE,nullable,DATA_DEFAULT  " +
                      ", (SELECT decode(A.CONSTRAINT_TYPE,'P','PK','') as pk FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.TABLE_NAME = B.TABLE_NAME AND B.TABLE_NAME = C.table_name  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME and A.CONSTRAINT_TYPE in ('P') and c.column_name =b.column_name and rownum =1 ) as  IS_PK  " +
                      ", (SELECT decode(A.CONSTRAINT_TYPE,'R','FK','') as fk  FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.TABLE_NAME = B.TABLE_NAME AND B.TABLE_NAME = C.table_name  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME and A.CONSTRAINT_TYPE in ('R') and c.column_name =b.column_name and rownum =1 ) as  IS_FK  " +                             
                      ", c.table_name " +
                      "FROM USER_TAB_COLUMNS c  WHERE  lower(c.table_name) = lower('" + table_name + "') order by c.table_name asc , IS_PK asc, IS_FK asc";
                      ;
            writeLogs(sql,true);          
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            String sqlDDLMysql="CREATE TABLE IF NOT EXISTS "+table_name+" (";
            String sqlDDLMysqlPK="PRIMARY KEY (";
            String columnsString="",columnsStringInsert="";
            for (int i=0;rs.next();i++) {
                String sqlDataType="";
                String column_name=rs.getString("column_name");
                String sqlSelect_column="",sqlInsert_column="";
                String data_type=rs.getString("data_type");
                int data_length=rs.getInt("data_length");
                int DATA_PRECISION=rs.getInt("DATA_PRECISION");
                int DATA_SCALE=rs.getInt("DATA_SCALE");
                String nullable=rs.getString("nullable");
                String DATA_DEFAULT=null;
                try{
                    DATA_DEFAULT=rs.getString("DATA_DEFAULT");
                    if(DATA_DEFAULT!=null)
                        DATA_DEFAULT=DATA_DEFAULT.replaceAll("[(]", "").replaceAll("[)]", "").replaceAll("SYSDATE", "'0000-00-00 00:00:00'");
                }catch(Exception e){
                    e.printStackTrace();
                }
                String IS_PK=rs.getString("IS_PK");
                String IS_FK=rs.getString("IS_FK");
                boolean is_pk=IS_PK!=null&&IS_PK.equalsIgnoreCase("PK");
                List columnsDesc =new ArrayList();
                columnsDesc.add(table_name);
                columnsDesc.add(column_name);
                columnsDesc.add(data_type);
                columnsDesc.add(data_length);
                columnsDesc.add(DATA_PRECISION);
                columnsDesc.add(DATA_SCALE);
                columnsDesc.add(nullable);
                columnsDesc.add(DATA_DEFAULT);
                columnsDesc.add(IS_PK);
                columnsDesc.add(IS_FK);
                sqlDataType+=column_name+" ";
                sqlSelect_column=column_name;
                columnsStringInsert+=column_name+",";
                if(data_type.toLowerCase().indexOf("date")>=0){
                    data_type="DATETIME";
                    sqlDataType+=data_type;
                    sqlSelect_column="TO_CHAR("+sqlSelect_column+",'yyyy/MM/dd HH24:MI:ss') as "+column_name;
                    columnsString+=sqlSelect_column+",";
                }else if(data_type.toLowerCase().indexOf("char")>=0){
                    data_type=data_type.replaceAll("2", "");
                    sqlDataType+=data_type;
                    sqlDataType+="("+data_length+")";
                    columnsString+=sqlSelect_column+",";
                }else if(data_type.toLowerCase().indexOf("clob")>=0){
                    data_type="LONGTEXT";
                    sqlDataType+=data_type;
                    columnsString+=sqlSelect_column+",";
                }else if(data_type.toLowerCase().indexOf("blob")>=0){
                    data_type="LONGBLOB";
                    sqlDataType+=data_type;
                    columnsString+=sqlSelect_column+",";
                }else if(data_type.toLowerCase().indexOf("float")>=0){
                    data_type="FLOAT";
                    sqlDataType+=data_type;
                    columnsString+=sqlSelect_column+",";
                }else if(data_type.toLowerCase().indexOf("int")>=0){
                    data_type="INT";
                    sqlDataType+=data_type;
                    columnsString+=sqlSelect_column+",";
                }else{
                    data_type="DECIMAL";
                    columnsString+=sqlSelect_column+",";
                    sqlDataType+=data_type;
                    if(DATA_PRECISION>0)
                        sqlDataType+="("+DATA_PRECISION+","+DATA_SCALE+")";
                    else
                        sqlDataType+="("+(data_length-5)+",5)";
                }
                if(nullable.toLowerCase().indexOf("n")>=0||is_pk){
                    sqlDataType+=" NOT NULL ";
                    if(DATA_DEFAULT!=null&&DATA_DEFAULT.toLowerCase().indexOf("null")>=0==false){
                       sqlDataType+=" DEFAULT "+DATA_DEFAULT;
                    }
                }
                if(nullable.toLowerCase().indexOf("y")>=0&&is_pk==false){
                    try{
                        if(DATA_DEFAULT!=null){
                            sqlDataType+=" DEFAULT "+DATA_DEFAULT;
                        }else {
                            sqlDataType+=" DEFAULT NULL";
                        }
                    }catch(Exception e){
                       if(is_pk==false) 
                        sqlDataType+=" DEFAULT NULL";
                    }
                }
                columnsDesc.add(sqlDataType);
                columnsDesc.add(sqlSelect_column);
                columnsDesc.add(sqlInsert_column);
             columns.add(columnsDesc);
             if(is_pk){
                List columnsDescPk =new ArrayList();
                columnsDescPk.add(table_name);
                columnsDescPk.add(column_name);
                columnsDescPk.add(data_type);
                columnsDescPk.add(data_length);
                columnsDescPk.add(DATA_PRECISION);
                columnsDescPk.add(DATA_SCALE);
                columnsDescPk.add(nullable);
                columnsDescPk.add(DATA_DEFAULT);
                columnsDescPk.add(IS_PK);
                columnsDescPk.add(IS_FK);
                columnsPk.add(columnsDescPk);
                sqlDDLMysqlPK+=column_name+",";
             }
             writeLogs((" ("+(index+1)+"."+(i+1)+"): "),true,true);
             writeLogs((sqlDataType+(is_pk?","+IS_PK:"")),true,true);
             sqlDDLMysql+=sqlDataType+",";            
            }
            if(sqlDDLMysqlPK.indexOf(",")>0){
                sqlDDLMysqlPK=sqlDDLMysqlPK.substring(0, sqlDDLMysqlPK.length()-1)+")";
            }else{
                sqlDDLMysqlPK="";
            }
            rs.close();
            sqlDDLMysql+=sqlDDLMysqlPK;
            if(sqlDDLMysql.indexOf("PRIMARY")>0){
               sqlDDLMysql+=")"; 
            }else{
               sqlDDLMysql=sqlDDLMysql.substring(0, sqlDDLMysql.length()-1)+")";
            }
            sqlDDLMysql+="ENGINE=InnoDB DEFAULT CHARSET=tis620";
            tablesColsList.add(index,columns);
            tablesPkList.add(index,columnsPk);
            if(columnsString.length()>0)columnsString=columnsString.substring(0,columnsString.length()-1);
            tablesColsStringList.add(index,columnsString);
            if(columnsStringInsert.length()>0)columnsStringInsert=columnsStringInsert.substring(0,columnsStringInsert.length()-1);
            tablesColsStringInsertList.add(index,columnsStringInsert);
            writeLogs(("Column : ["+columns.size()+"] PK=  ["+columnsPk.size()+"]"),true,true);   
            writeLogs(("Mysql DDL : "+sqlDDLMysql),true,true);
            return sqlDDLMysql;
    }
    
    public static void main(String[]args){
        try{
        tables = args[0]+"%";
        }catch(Exception e){tables="AMSECUSERS%";}
        try{
        xmlConnectionStringOraIndex = Integer.parseInt(args[1]);
        }catch(Exception e){xmlConnectionStringMysqlIndex=0;}
        try{
        xmlConnectionStringMysqlIndex = Integer.parseInt(args[2]);
        }catch(Exception e){xmlConnectionStringMysqlIndex=3;}
        try{
        schemas_flag = Integer.parseInt(args[3])==1;
        }catch(Exception e){schemas_flag=true;}
        try{
        data_flag = Integer.parseInt(args[4])==1;
        }catch(Exception e){data_flag=true;}
        try{
        xmlConnectionString = args[5];
        }catch(Exception e){xmlConnectionString="D:\\ICOOP_ALL\\IEXT\\ICOOP\\XMLConfig\\server.connection_string.xml";}
        try{
        print_sql_flag = Integer.parseInt(args[6])==1;
        }catch(Exception e){print_sql_flag=false;}try{
        numdata = Integer.parseInt(args[7]);
        }catch(Exception e){numdata=50;}
        try{
            conn=new ConnectionIndex(xmlConnectionString);
            
            connOra = conn.getConnection(xmlConnectionStringOraIndex);
            xmlConnectionStringOra="host="+conn.host[xmlConnectionStringOraIndex];
            xmlConnectionStringOra+=",port="+conn.port[xmlConnectionStringOraIndex];
            xmlConnectionStringOra+=",sid="+conn.sid[xmlConnectionStringOraIndex];
            xmlConnectionStringOra+=",usr="+conn.username[xmlConnectionStringOraIndex];
            xmlConnectionStringOra+=",pwd="+conn.password[xmlConnectionStringOraIndex];
            //System.out.println("Connect to Oracle : "+xmlConnectionStringOra+" Status : "+(connOra!=null?(connOra.isClosed()?"Not Connected":"Connected"):"Not Connected"));
            writeLogs(("Connect to Oracle : "+xmlConnectionStringOra+" Status : "+(connOra!=null?(connOra.isClosed()?"Not Connected":"Connected"):"Not Connected")), false,true);
            
            
            connMy = conn.getConnection(xmlConnectionStringMysqlIndex);
            xmlConnectionStringMysql="host="+conn.host[xmlConnectionStringMysqlIndex];
            xmlConnectionStringMysql+=",port="+conn.port[xmlConnectionStringMysqlIndex];
            xmlConnectionStringMysql+=",sid="+conn.sid[xmlConnectionStringMysqlIndex];
            xmlConnectionStringMysql+=",usr="+conn.username[xmlConnectionStringMysqlIndex];
            xmlConnectionStringMysql+=",pwd="+conn.password[xmlConnectionStringMysqlIndex];
            //System.out.println("Connect to Mysql : "+xmlConnectionStringMysql+" Status : "+(connMy!=null?(connMy.isClosed()?"Not Connected":"Connected"):"Not Connected"));
            writeLogs(("Connect to Mysql : "+xmlConnectionStringMysql+" Status : "+(connMy!=null?(connMy.isClosed()?"Not Connected":"Connected"):"Not Connected")),true,true);
            
            getSrcTables(connOra);
            if(schemas_flag){
            postTargetTables(connMy);     
            }
            if(data_flag){
            get2postTablesData(connOra,connMy);
            }
            
        }catch(Exception e){
            e.printStackTrace();
            writeLogs(e.getMessage(),true,true);
        }
        try{
            conn.CloseAll();
        }catch(Exception e){
            e.printStackTrace();
            writeLogs(e.getMessage(),true,true);
        }
    }
}