package reportbuilder;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionIndex {
    
    private String xmlConnectionString="";
    private Connection[] cons;
    public String[] username;
    public String[] password;
    public String[] host;
    public String[] port;
    public String[] sid;
    public boolean[] isMysql;
    public static boolean IS_MYSQL=false;
    public static String REPORT_SUFFIX="";
    
    
    public static String replaceOracle2Mysql(String sql)
    {
        return replaceOracle2Mysql(sql,IS_MYSQL);
    }
    
    public static String replaceOracle2Mysql(String sql,boolean isMysql)
    {
        if (isMysql){
                sql = sql.replaceAll("sysdate", "NOW()");
                sql = sql.replaceAll("SYSDATE", "NOW()");
                //sql = sql.replaceAll("\\", "\\\\");
                sql = sql.replaceAll("to_date", "str_to_date");
                sql = sql.replaceAll("TO_DATE", "STR_TO_DATE");
                sql = sql.replaceAll("yyyy-mm-dd hh24:mi:ss", "%Y-%m-%d %T");
                sql = sql.replaceAll("yyyy-MM-dd hh24:mi:ss", "%Y-%m-%d %T");
                sql = sql.replaceAll("yyyy/mm/dd hh24:mi:ss", "%Y/%m/%d %T");
                sql = sql.replaceAll("yyyy/MM/dd hh24:mi:ss", "%Y/%m/%d %T");
         }

     return sql;
    }

    public boolean isMysql(String conStr){
        boolean isMysql=(conStr.toLowerCase().indexOf("server=")>=0);
        if(isMysql){
            REPORT_SUFFIX="_myl";
        }else{
            REPORT_SUFFIX="";
        }            
     return isMysql;
    }
    public void init()throws Exception {  
    File fXml = new File(this.xmlConnectionString);
        XmlParserReport xml = new XmlParserReport(fXml);
        int rowCount = xml.getRowCount();
        cons = new Connection[rowCount];
        this.username = new String[rowCount];
        this.password = new String[rowCount];
        this.host = new String[rowCount];
        this.port = new String[rowCount];
        this.sid = new String[rowCount];
        this.isMysql = new boolean[rowCount];
        
        for (int i = 0; i < rowCount; i++) {
            String conStr = xml.getItemString(i, "connection_string");
            isMysql[i]=this.isMysql(conStr);
            if(this.isMysql(conStr)){
                String conSp[] = conStr.split(";");
                for (int s = 0; s < conSp.length; s++) {
                    try {
                        String vv[] = conSp[s].split("=");
                        if (vv[0].toLowerCase().equals("server")) {
                            this.host[i] = vv[1];
                        } else if (vv[0].toLowerCase().equals("port")) {
                            this.port[i] = vv[1];
                        } else if (vv[0].toLowerCase().equals("uid")) {
                            this.username[i] = vv[1];
                        } else if (vv[0].toLowerCase().equals("pwd")) {
                            this.password[i] = vv[1];
                        } else if (vv[0].toLowerCase().equals("database")) {
                            this.sid[i] = vv[1];
                        }
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            }else{
                conStr = conStr.replaceAll(":dedicated", "");
                conStr = conStr.replace(":shared", "");
                String conSp[] = conStr.split(";");
                for (int s = 0; s < conSp.length; s++) {
                    try {
                        String vv[] = conSp[s].split("=");
                        if (vv[0].toLowerCase().equals("data source")) {
                            if (vv[1].indexOf("/") < 0) {
                                this.host[i] = "localhost:1521";
                                this.sid[i] = vv[1];
                            } else {
                                String vvv[] = vv[1].split("/");
                                if (vvv[0].indexOf(":") < 0) {
                                    this.host[i] = vvv[0] + ":" + 1521;
                                    this.sid[i] = vvv[1];
                                } else {
                                    this.host[i] = vvv[0];
                                    this.sid[i] = vvv[1];
                                }
                            }
                        } else if (vv[0].toLowerCase().equals("user id")) {
                            this.username[i] = vv[1];
                        } else if (vv[0].toLowerCase().equals("password")) {
                            this.password[i] = vv[1];
                        }
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }
    public ConnectionIndex() throws Exception {  
        this.xmlConnectionString=ReportBuilder.xmlConnectionString;
        this.init();  
    }
    public ConnectionIndex(String xmlConnectionString) throws Exception {
        this.xmlConnectionString=xmlConnectionString;
        this.init();        
    }

    public Connection getConnection(int index) {
        Connection con = null;
        try {
            con = cons[index];
        } catch (Exception ex) {
        }
        try {
            if (con != null) {
                if (!con.isClosed()) {
                    return con;
                }
                throw new Exception();
            }
        } catch (Exception ex) {
            try {
                con.close();
            } catch (Exception ee) {
            }
            con = null;
        }

        IS_MYSQL=isMysql[index];
        
        if(isMysql[index]){
            
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
            }

            try {
                con = DriverManager.getConnection("jdbc:mysql://"+host[index] + ":" + port[index]+"/"+sid[index]+"?zeroDateTimeBehavior=convertToNull" , username[index], password[index]);
                con.setAutoCommit(false);
            } catch (SQLException e) {
            }
            REPORT_SUFFIX="_myl";
        }else{
        
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
            } catch (ClassNotFoundException e) {
            }

            try {
                con = DriverManager.getConnection("jdbc:oracle:thin:@" + host[index] + ":" + sid[index], username[index], password[index]);
            } catch (SQLException e) {
            }
            REPORT_SUFFIX="";
        }
        try {
            String sql="delete from cmreportprocessing where START_PROCESS < (SYSDATE - (" + ReportBuilder.deleteTimeOut + "/24/60/60))";
            if(ConnectionIndex.IS_MYSQL)sql="delete from cmreportprocessing where START_PROCESS < DATE_SUB(NOW() , INTERVAL " + (ReportBuilder.deleteTimeOut) + " SECOND))";
            sql=ConnectionIndex.replaceOracle2Mysql(sql);
            //con.createStatement().execute("ALTER SESSION SET NLS_SORT = THAI_DICTIONARY");
            con.createStatement().execute(sql);
            con.commit();
        } catch (Exception ex) {
        }
        return con;
    }

    public void CloseAll() {
        for (int i = 0; i < cons.length; i++) {
            try {
                cons[i].close();
            } catch (Exception ex) {
            }
        }
    }
}