package th.co.muangthai.endrprint.util.dbfactory;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


public class DBGenerateFactory extends Dbms {

    static org.apache.log4j.Logger log = org.apache.log4j.Logger.getLogger(DBGenerateFactory.class);

    // Oracle 10g

    String PATH_OUTPUT = "c:\\logging";

    private static Connection conn = null;
    private boolean annotationFlag = false;
    public final static int TYPE_RESULTSET = 1;
    public final static int TYPE_HIBERNATE_XML = 2;
    public final static int TYPE_HIBERNATE = 3;
    public final static int TYPE_PL = 4;
    public final static int PRINT_FILE = 1;
    public final static int PRINT_OUT = 2;
    private int printType = 2;

    private static String partPackage;


    public void setPrintType(int type) {
        this.printType = type;
    }

    public boolean isAnnotationFlag() {
        return annotationFlag;
    }

    public void setAnnotationFlag(boolean annotationFlag) {
        this.annotationFlag = annotationFlag;
    }

    //    public static void main(String[] a) {
//        new Programs().gen("TN_COURSE_GROUP");
//    }
    public DBGenerateFactory() {
    }

    public DBGenerateFactory(String host, String sid, String username, String password) {
        super.username = username;
        super.password = password;
        super.url = "jdbc:oracle:thin:@" + host + ":1521/" + sid;
//        super.url = "jdbc:oracle:thin:@10.1.0.250:1521/MTRAMUAT";
        // Test Conncetion
        try {
            log.info("Testing connection...");
            log.info("Host \t: " + host);
            log.info("SID \t: " + sid);
            log.info("User Name \t: " + username);
            log.info("Password \t: " + password);

            Class.forName(driverClass);
            conn = DriverManager.getConnection(super.url, super.username, super.password);
            log.info("OK.");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }


    }

    public void genBean(String tableName) throws Exception {
        List<String> listOfPK = new ArrayList<String>();
        //List<String> listOfMeta = new ArrayList<String>();
        List<MetaBean> listOfMetaBean = new ArrayList<MetaBean>();
        String sql = " select * from " + tableName;
        try {

            Class.forName(driverClass);
            conn = DriverManager.getConnection(super.url, super.username, super.password);
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();

            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, null, tableName);

            while (primaryKeys.next()) {
                String primaryKeyColumn = primaryKeys.getString("COLUMN_NAME");
                listOfPK.add(primaryKeyColumn);
            }

            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {

                String meta = null;
                String col = rsmd.getColumnName(i);
                String type = rsmd.getColumnTypeName(i);
                int scale = rsmd.getScale(i);
                boolean pk = false;

                log.info("col " + col);
                for (int k = 0; k < listOfPK.size(); k++) {
                    log.info("PK : " + listOfPK.get(k));
                    if (col.equals(listOfPK.get(k))) {
                        pk = true;
                    }
                }
                log.info("Have PK : " + pk);
                MetaBean metaBean = new MetaBean();
                if (pk) {
                    meta = getType(type, scale, true) + " " + getName(col, 0);

                    metaBean.setKey(true);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, true));
                } else {
                    meta = getType(type, scale, false) + " " + getName(col, 0);

                    metaBean.setKey(false);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, false));
                }

                log.info("meta : " + meta + "\n\n");
                //listOfMeta.add(meta);
                listOfMetaBean.add(metaBean);
            }
            rs.close();
            stmt.close();
            conn.close();
            // Create

            String p = PATH_OUTPUT;
            wrietBeanFile(p, getName(tableName, 1), listOfMetaBean, tableName);

        } catch (Exception e) {
            throw e;
        }
    }

    public void genBeanP(String tableName) throws Exception {
        List<String> listOfPK = new ArrayList<String>();
        //List<String> listOfMeta = new ArrayList<String>();
        List<MetaBean> listOfMetaBean = new ArrayList<MetaBean>();
        String sql = " select * from " + tableName;
        try {

            Class.forName(driverClass);
            conn = DriverManager.getConnection(super.url, super.username, super.password);
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();

            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, null, tableName);

            while (primaryKeys.next()) {
                String primaryKeyColumn = primaryKeys.getString("COLUMN_NAME");
                listOfPK.add(primaryKeyColumn);
            }

            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {

                String meta = null;
                String col = rsmd.getColumnName(i);
                String type = rsmd.getColumnTypeName(i);
                int scale = rsmd.getScale(i);
                boolean pk = false;

                log.info("col " + col);
                for (int k = 0; k < listOfPK.size(); k++) {
                    log.info("PK : " + listOfPK.get(k));
                    if (col.equals(listOfPK.get(k))) {
                        pk = true;
                    }
                }
                log.info("Have PK : " + pk);
                MetaBean metaBean = new MetaBean();
                if (pk) {
                    meta = getType(type, scale, true) + " " + getName(col, 0);

                    metaBean.setKey(true);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, true));
                } else {
                    meta = getType(type, scale, false) + " " + getName(col, 0);

                    metaBean.setKey(false);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, false));
                }

                log.info("meta : " + meta + "\n\n");
                //listOfMeta.add(meta);
                listOfMetaBean.add(metaBean);
            }
            rs.close();
            stmt.close();
            conn.close();
            // Create

            String p = PATH_OUTPUT;
            wrietBeanFileP(p, getName(tableName, 1), listOfMetaBean, tableName);

        } catch (Exception e) {
            throw e;
        }
    }

    public void genBeanP2(String tableName) throws Exception {
        List<String> listOfPK = new ArrayList<String>();
        //List<String> listOfMeta = new ArrayList<String>();
        List<MetaBean> listOfMetaBean = new ArrayList<MetaBean>();
        String sql = " select * from " + tableName;
        try {

            Class.forName(driverClass);
            conn = DriverManager.getConnection(super.url, super.username, super.password);
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();

            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, null, tableName);

            while (primaryKeys.next()) {
                String primaryKeyColumn = primaryKeys.getString("COLUMN_NAME");
                listOfPK.add(primaryKeyColumn);
            }

            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {

                String meta = null;
                String col = rsmd.getColumnName(i);
                String type = rsmd.getColumnTypeName(i);
                int scale = rsmd.getScale(i);
                boolean pk = false;

                log.info("col " + col);
                for (int k = 0; k < listOfPK.size(); k++) {
                    log.info("PK : " + listOfPK.get(k));
                    if (col.equals(listOfPK.get(k))) {
                        pk = true;
                    }
                }
                log.info("Have PK : " + pk);
                MetaBean metaBean = new MetaBean();
                if (pk) {
                    meta = getType(type, scale, true) + " " + getName(col, 0);

                    metaBean.setKey(true);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, true));
                } else {
                    meta = getType(type, scale, false) + " " + getName(col, 0);

                    metaBean.setKey(false);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, false));
                }

                log.info("meta : " + meta + "\n\n");
                //listOfMeta.add(meta);
                listOfMetaBean.add(metaBean);
            }
            rs.close();
            stmt.close();
            conn.close();
            // Create

            String p = PATH_OUTPUT;
            wrietBeanFileP2(p, getName(tableName, 1), listOfMetaBean, tableName);

        } catch (Exception e) {
            throw e;
        }
    }

    public void genOracleFromSql(String tableName, String sql) throws Exception {
        List<String> listOfPK = new ArrayList<String>();
        //List<String> listOfMeta = new ArrayList<String>();
        List<MetaBean> listOfMetaBean = new ArrayList<MetaBean>();
        try {

            Class.forName(driverClass);
            conn = DriverManager.getConnection(super.url, super.username, super.password);
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();

            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, null, tableName);

            while (primaryKeys.next()) {
                String primaryKeyColumn = primaryKeys.getString("COLUMN_NAME");
                listOfPK.add(primaryKeyColumn);
            }

            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {

                String meta = null;
                String col = rsmd.getColumnName(i);
                String type = rsmd.getColumnTypeName(i);
                int scale = rsmd.getScale(i);
                boolean pk = false;

                log.info("col " + col);
                for (int k = 0; k < listOfPK.size(); k++) {
                    log.info("PK : " + listOfPK.get(k));
                    if (col.equals(listOfPK.get(k))) {
                        pk = true;
                    }
                }
                log.info("Have PK : " + pk);
                MetaBean metaBean = new MetaBean();
                if (pk) {
                    meta = getType(type, scale, true) + " " + getName(col, 0);

                    metaBean.setKey(true);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, true));
                } else {
                    meta = getType(type, scale, false) + " " + getName(col, 0);

                    metaBean.setKey(false);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, false));
                }

                log.info("meta : " + meta + "\n\n");
                //listOfMeta.add(meta);
                listOfMetaBean.add(metaBean);
            }
            rs.close();
            stmt.close();
            conn.close();
            // Create

            String p = PATH_OUTPUT;
            wrietBeanFile(p, getName(tableName, 1), listOfMetaBean, tableName);

        } catch (Exception e) {
            throw e;
        }
    }

    public void genResultSetText(String tableName, int templateType) throws Exception {

        genResultSetText(tableName, "select * from " + tableName, templateType);
    }

    public void genResultSetText(String tableName, String sql, int templateType) throws Exception {
        List<String> listOfPK = new ArrayList<String>();
        //List<String> listOfMeta = new ArrayList<String>();
        List<MetaBean> listOfMetaBean = new ArrayList<MetaBean>();
        log.info("Show Sql : ");
        log.info("-----------------------------------------------------");
        log.info(sql);
        log.info("-----------------------------------------------------");

        try {

            Class.forName(driverClass);
            conn = DriverManager.getConnection(super.url, super.username, super.password);
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();

            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, "hr_owner1", tableName);

            log.info("Key Count : " + primaryKeys.toString());
            while (primaryKeys.next()) {
                String primaryKeyColumn = primaryKeys.getString("COLUMN_NAME");
                listOfPK.add(primaryKeyColumn);
            }

            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {

                String meta = null;
                String col = rsmd.getColumnName(i);
                String type = rsmd.getColumnTypeName(i);
                int scale = rsmd.getScale(i);

                boolean pk = false;
                MetaBean metaBean = new MetaBean();
                log.info("col " + col);
                for (int k = 0; k < listOfPK.size(); k++) {
                    log.info("PK : " + listOfPK.get(k));
                    if (col.equals(listOfPK.get(k))) {
                        pk = true;
                        metaBean.setKey(true);
                    }
                }
                log.info("Have PK : " + pk);

                if (rsmd.getPrecision(i) != 0) {
                    metaBean.setLength(rsmd.getPrecision(i));
                }
                if (rsmd.getScale(i) != 0) {
                    metaBean.setLength(rsmd.getScale(i));
                }
                if (pk) {
                    meta = getType(type, scale, true) + " " + getName(col, 0);

                    metaBean.setKey(true);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, true));
                } else {
                    meta = getType(type, scale, false) + " " + getName(col, 0);

                    metaBean.setKey(false);
                    metaBean.setName(col);
                    metaBean.setType(getType(type, scale, false));
                }

                log.info("meta : " + meta + "\n\n");
                //listOfMeta.add(meta);
                listOfMetaBean.add(metaBean);
            }
            rs.close();
            stmt.close();
            conn.close();
            // Create

            String p = PATH_OUTPUT;

//            printSpecial1(p, getName(tableName, 1), listOfMetaBean, tableName);
//            if (1 == 1) {
//                return;
//            }
            if (templateType == TYPE_RESULTSET) {
                printResultsetText(p, getName(tableName, 1), listOfMetaBean, tableName);
            } else if (templateType == TYPE_HIBERNATE_XML) {
                printTableText(p, getName(tableName, 1), listOfMetaBean, tableName);
            } else if (templateType == TYPE_PL) {
                printPLText(p, getName(tableName, 1), listOfMetaBean, tableName);
            }
        } catch (Exception e) {
            throw e;
        }
    }

    public void printResultsetText(String path, String nameFile, List<MetaBean> metaList, String tableName) {
        try {

            final String NEW_LINE = System.getProperty("line.separator");
            String text = "";
            for (MetaBean metaBean : metaList) {
                if (metaBean.getType().equals("String")) {
                    text += "bean.set" + metaBean.getNameHUpper() + "(rs.getString(\"" + metaBean.getName() + "\"));" + NEW_LINE;
                } else if (metaBean.getType().equals("Date")) {
                    text += "bean.set" + metaBean.getNameHUpper() + "(rs.getDate(\"" + metaBean.getName() + "\"));" + NEW_LINE;
                } else if (metaBean.getType().equals("BigDecimal")) {
                    text += "bean.set" + metaBean.getNameHUpper() + "(rs.getBigDecimal(\"" + metaBean.getName() + "\"));" + NEW_LINE;
                }
            }

            log.info(text);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void printPLText(String path, String nameFile, List<MetaBean> metaList, String tableName) {
        try {

            final String NEW_LINE = System.getProperty("line.separator");

            String text = "";
            for (MetaBean metaBean : metaList) {
                String field = "v_" + tableName + "." + metaBean.getName().toLowerCase() + " := null;";

                text += field;
                text += NEW_LINE;

            }

            log.info(text);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void printTableText(String path, String nameFile, List<MetaBean> metaList, String tableName) {
        try {

            final String NEW_LINE = System.getProperty("line.separator");
            String temp = "  <af:column sortable=\"true\" headerText=\"xxxxxxx\"  sortProperty=\"{###}\">" + NEW_LINE +
                    "                <af:outputText value=\"#{row.{###}}\"/>" + NEW_LINE +
                    "     </af:column>";
            String text = "";
            for (MetaBean metaBean : metaList) {
                String field = temp.replace("{###}", metaBean.getNameHLower());

                text += field;
                text += NEW_LINE;

            }

            log.info(text);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void printHibernateMapping(String path, String nameFile, List<MetaBean> metaList, String tableName) {
        try {

            final String NEW_LINE = System.getProperty("line.separator");
            String text = "";

            text += "";

            text += "<?xml version=\"1.0\"?>\n" +
                    "<!DOCTYPE hibernate-mapping PUBLIC \"-//Hibernate/Hibernate Mapping DTD 3.0//EN\"\n" +
                    "\"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd\">\n" +
                    "<hibernate-mapping>\n" +
                    "    <class name=\"vsm.hrpost.bean." + getName2(tableName) + "\" table=\"" + tableName + "\">";
            text += NEW_LINE;
            text += "\t<composite-id>";

            for (MetaBean metaBean : metaList) {
                if (metaBean.isKey()) {
                    text += NEW_LINE;
                    text += "\t\t<key-property name=\"" + metaBean.getNameHLower() + "\" column=\"" + metaBean.getName() + "\"  type=\"" + metaBean.getHibernateType().toLowerCase() + "\" length=\"" + metaBean.getLength() + "\" />";
                    text += NEW_LINE;
                }

            }

            text += "\t</composite-id>";

            for (MetaBean metaBean : metaList) {
                if (!metaBean.isKey()) {
                    text += NEW_LINE;
                    String length = metaBean.getType().equals("Date") ? "length=\"7\"" : "length=\"" + metaBean.getLength() + "\"";
                    if (metaBean.getLength() == -127) {
                        length = "";
                    }
                    text += "\t\t<property name=\"" + metaBean.getNameHLower() + "\" type=\"" + metaBean.getHibernateType().toLowerCase() + "\">\n" +
                            "            \t\t\t<column name=\"" + metaBean.getName() + "\" " + length + "/>\n" +
                            "        \t\t</property>";
                    text += NEW_LINE;
                }

            }

            text += "</class>\n" +
                    "</hibernate-mapping>";

            if (this.printType == PRINT_FILE) {
                File file = new File(path, this.getName2(tableName) + ".hbm.xml");
                BufferedWriter writer = new BufferedWriter(new FileWriter(file, false));
                writer.write(text);
                writer.close();

                log.info("file --> " + file.getPath());


            } else {
                log.info(text);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void printSpecial1(String path, String nameFile, List<MetaBean> metaList, String tableName) {
        try {

            final String NEW_LINE = System.getProperty("line.separator");
            String temp = " <td align=\"center\">\n" +
                    "		<input name=\"listMcDomRtDBean[${idx}].{###}\" class=\"txtOrgCode\" value=\"${mcDomRtDBean.{###}}\"/>\n" +
                    "	    </td>\n";
            String text = "";
            for (MetaBean metaBean : metaList) {
                String field = temp.replace("{###}", metaBean.getNameHLower());

                text += field;
                text += NEW_LINE;

            }

            log.info(text);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void wrietBeanFile(String path, String nameFile, List<MetaBean> metaList, String tableName) {

        Writer writer = null;

        String nameCalss = "public class " + nameFile.substring(0, 1).toUpperCase() + nameFile.substring(1, nameFile.length()) + "Bean implements Serializable { ";
        String nameBean = nameFile.substring(0, 1).toUpperCase() + nameFile.substring(1, nameFile.length()) + "Bean.java";
        try {
            File file = new File(path, nameBean);
            final String NEW_LINE = System.getProperty("line.separator");
            writer = new BufferedWriter(new FileWriter(file, false));


            writer.append(partPackage);writer.append(NEW_LINE);writer.append(NEW_LINE);

            writer.append("import javax.persistence.Column;");writer.append(NEW_LINE);
            writer.append("import javax.persistence.Entity;");writer.append(NEW_LINE);
            writer.append("import javax.persistence.Table;");writer.append(NEW_LINE);
            writer.append("import java.math.BigDecimal;");writer.append(NEW_LINE);
            writer.append("import java.util.Date;");writer.append(NEW_LINE);
            writer.append("import java.io.Serializable;");writer.append(NEW_LINE);

            writer.append("import org.codehaus.jackson.annotate.JsonAutoDetect;");writer.append(NEW_LINE);
            writer.append("import org.codehaus.jackson.map.annotate.JsonSerialize;");writer.append(NEW_LINE);
            writer.append("import th.co.muangthai.util.JsonDateSerializer;");writer.append(NEW_LINE);

            writer.append(NEW_LINE);

            if (this.annotationFlag) {
                writer.append("@JsonAutoDetect" + NEW_LINE);
                writer.append("@Entity" + NEW_LINE);
                writer.append("@Table(name = \"" + tableName + "\")");
            }
            writer.append(NEW_LINE);



            writer.write(nameCalss + NEW_LINE);
            writer.append(NEW_LINE);
//            for (int i = 0; i < meta.size(); i++) {
//                writer.append("  private " + meta.get(i) + NEW_LINE);
//            }

            for (MetaBean metaBean : metaList) {

                if (this.annotationFlag) {
                    writer.append("\t@Column(name = \"" + metaBean.getName() + "\")" + NEW_LINE);
                }
                writer.append("\tprivate " + metaBean.getType() + " " + metaBean.getNameHLower() + ";" + NEW_LINE);
                //writer.append(NEW_LINE);
            }

            writer.append(NEW_LINE);

            for (MetaBean metaBean : metaList) {

                writer.append("\tpublic void set" + metaBean.getNameHUpper() + "(" + metaBean.getType() + " " + metaBean.getNameHLower() + ") {" + NEW_LINE);
                writer.append("\t\tthis." + metaBean.getNameHLower() + " = " + metaBean.getNameHLower() + ";" + NEW_LINE);
                writer.append("\t}" + NEW_LINE);
                writer.append(NEW_LINE);

                if ("boolean".equals(metaBean.getType().toLowerCase())) {
                    writer.append("\tpublic " + metaBean.getType() + " is" + metaBean.getNameHUpper() + "() {" + NEW_LINE);
                } else {
                    if("Date".equals(metaBean.getType())){
                        writer.append("\t@JsonSerialize(using = JsonDateSerializer.class)"+NEW_LINE);
                    }
                    writer.append("\tpublic " + metaBean.getType() + " get" + metaBean.getNameHUpper() + "() {" + NEW_LINE);
                }

                writer.append("\t\treturn this." + metaBean.getNameHLower() + ";" + NEW_LINE);
                writer.append("\t}" + NEW_LINE);
                writer.append(NEW_LINE);


            }


            writer.write(NEW_LINE + "}");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (writer != null) {
                    writer.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    public void wrietBeanFileP(String path, String nameFile, List<MetaBean> metaList, String tableName) {

        Writer writer = null;

        String nameCalss = "public class " + nameFile.substring(0, 1).toUpperCase() + nameFile.substring(1, nameFile.length()) + "Bean implements Serializable { ";
        String nameBean = nameFile.substring(0, 1).toUpperCase() + nameFile.substring(1, nameFile.length()) + "Bean.java";
        try {
            File file = new File(path, nameBean);
            final String NEW_LINE = System.getProperty("line.separator");
            writer = new BufferedWriter(new FileWriter(file, false));

            writer.append(partPackage);writer.append(NEW_LINE);writer.append(NEW_LINE);

            writer.append("import javax.persistence.Column;");writer.append(NEW_LINE);
            writer.append("import javax.persistence.Entity;");writer.append(NEW_LINE);
            writer.append("import javax.persistence.Table;");writer.append(NEW_LINE);
            writer.append("import java.math.BigDecimal;");writer.append(NEW_LINE);
            writer.append("import java.util.Date;");writer.append(NEW_LINE);
            writer.append("import java.io.Serializable;");writer.append(NEW_LINE);

            writer.append("import org.codehaus.jackson.annotate.JsonAutoDetect;");writer.append(NEW_LINE);
            writer.append("import org.codehaus.jackson.map.annotate.JsonSerialize;");writer.append(NEW_LINE);
            writer.append("import th.co.muangthai.util.JsonDateSerializer;");writer.append(NEW_LINE);

            writer.append(NEW_LINE);

            if (this.annotationFlag) {
                writer.append("@JsonAutoDetect" + NEW_LINE);
                //writer.append("@Entity" + NEW_LINE);
                //writer.append("@Table(name = \"" + tableName + "\")");
            }
            writer.append(NEW_LINE);



            writer.write(nameCalss + NEW_LINE);
            writer.append(NEW_LINE);
//            for (int i = 0; i < meta.size(); i++) {
//                writer.append("  private " + meta.get(i) + NEW_LINE);
//            }

            for (MetaBean metaBean : metaList) {

                //if (this.annotationFlag) {
                //    writer.append("\t@Column(name = \"" + metaBean.getName() + "\")" + NEW_LINE);
                //}
                writer.append("\tprivate " + metaBean.getType() + " " + metaBean.getNameHLower() + ";" + NEW_LINE);
                //writer.append(NEW_LINE);
            }

            writer.append(NEW_LINE);

            for (MetaBean metaBean : metaList) {

                writer.append("\tpublic void set" + metaBean.getNameHUpper() + "(" + metaBean.getType() + " " + metaBean.getNameHLower() + ") {" + NEW_LINE);
                writer.append("\t\tthis." + metaBean.getNameHLower() + " = " + metaBean.getNameHLower() + ";" + NEW_LINE);
                writer.append("\t}" + NEW_LINE);
                writer.append(NEW_LINE);

                if ("boolean".equals(metaBean.getType().toLowerCase())) {
                    writer.append("\tpublic " + metaBean.getType() + " is" + metaBean.getNameHUpper() + "() {" + NEW_LINE);
                } else {
                    if("Date".equals(metaBean.getType())){
                        writer.append("\t@JsonSerialize(using = JsonDateSerializer.class)"+NEW_LINE);
                    }
                    writer.append("\tpublic " + metaBean.getType() + " get" + metaBean.getNameHUpper() + "() {" + NEW_LINE);
                }

                writer.append("\t\treturn this." + metaBean.getNameHLower() + ";" + NEW_LINE);
                writer.append("\t}" + NEW_LINE);
                writer.append(NEW_LINE);


            }


            writer.write(NEW_LINE + "}");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (writer != null) {
                    writer.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    public void wrietBeanFileP2(String path, String nameFile, List<MetaBean> metaList, String tableName) {

        Writer writer = null;

        String nameCalss = "public class " + nameFile.substring(0, 1).toUpperCase() + nameFile.substring(1, nameFile.length()) + "Bean implements Serializable { ";
        String nameBean = nameFile.substring(0, 1).toUpperCase() + nameFile.substring(1, nameFile.length()) + "Bean.java";
        try {
            File file = new File(path, nameBean);
            final String NEW_LINE = System.getProperty("line.separator");
            writer = new BufferedWriter(new FileWriter(file, false));

            writer.append(partPackage);writer.append(NEW_LINE);writer.append(NEW_LINE);

            writer.append("import javax.persistence.Column;");writer.append(NEW_LINE);
            writer.append("import javax.persistence.Entity;");writer.append(NEW_LINE);
            writer.append("import javax.persistence.Table;");writer.append(NEW_LINE);
            writer.append("import java.math.BigDecimal;");writer.append(NEW_LINE);
            writer.append("import java.util.Date;");writer.append(NEW_LINE);
            writer.append("import java.io.Serializable;");writer.append(NEW_LINE);

            writer.append("import org.codehaus.jackson.annotate.JsonAutoDetect;");writer.append(NEW_LINE);
            writer.append("import org.codehaus.jackson.map.annotate.JsonSerialize;");writer.append(NEW_LINE);
            writer.append("import th.co.muangthai.util.JsonDateSerializer;");writer.append(NEW_LINE);

            writer.append(NEW_LINE);

            if (this.annotationFlag) {
                writer.append("@JsonAutoDetect" + NEW_LINE);
                //writer.append("@Entity" + NEW_LINE);
                //writer.append("@Table(name = \"" + tableName + "\")");
            }
            writer.append(NEW_LINE);



            writer.write(nameCalss + NEW_LINE);
            writer.append(NEW_LINE);
//            for (int i = 0; i < meta.size(); i++) {
//                writer.append("  private " + meta.get(i) + NEW_LINE);
//            }

            for (MetaBean metaBean : metaList) {

                //if (this.annotationFlag) {
                //    writer.append("\t@Column(name = \"" + metaBean.getName() + "\")" + NEW_LINE);
                //}
                writer.append("\tprivate " + metaBean.getType() + " " + metaBean.getNameHLower() + ";" + NEW_LINE);
                //writer.append(NEW_LINE);
            }

            writer.append(NEW_LINE);

            for (MetaBean metaBean : metaList) {
                writer.append("\tset" + metaBean.getNameHUpper() + "( null );" + NEW_LINE);
            }


            writer.write(NEW_LINE + "}");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (writer != null) {
                    writer.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    public String getName2(String field) {
        String name = getName(field, 1);

        return name.substring(0, 1).toUpperCase() + name.substring(1);
    }

    public String getName(String n, int flag) {
        String s[] = n.split("_");
        String nameTable = "";
        String f = "";
        String last = "";
        for (int i = 0; i < s.length; i++) {
            if (i == 0) {
                f = s[i].substring(0, 1).toLowerCase();
            } else if (i > 0) {
                f = s[i].substring(0, 1).toUpperCase();
            }

            last = s[i].substring(1, s[i].length());
            String token = f + last.toLowerCase();

            nameTable += token;
        }
        if (flag == 0) {
            return nameTable + ";";
        } else {
            return nameTable;
        }
    }

    public String getType(String typeName, int scale, boolean pk) {
        String t = null;

        if (typeName.equals("VARCHAR2")) {
            t = "String";
        } else if (typeName.equals("INTEGER") && pk == false) {
            t = "BigDecimal";
        } else if (typeName.equals("NUMBER") && scale <= 0 && pk == false) {
            t = "BigDecimal";
//        } else if (typeName.equals("NUMBER") && scale > 0 && pk == false) {
//            t = "Double";
        } else if (typeName.equals("NUMBER") && scale > 0 && pk == false) {
            t = "BigDecimal";
        } else if (typeName.equals("DATE")) {
            t = "Date";
        } else if (typeName.equals("CHAR")) {
            t = "Character";
        } else if (typeName.equals("INTEGER") && pk == true) {
            t = "Long";
            //t = "BigDecimal";
        } else if (typeName.equals("NUMBER") && pk == true) {
            //t = "Long";
            t = "BigDecimal";
        }
        return t;
    }

    public String getType1(String typeName, int scale, boolean pk) {
        String t = null;

        if (typeName.equals("VARCHAR2")) {
            t = "String";
        } else if (typeName.equals("INTEGER") && pk == false) {
            t = "Integer";
        } else if (typeName.equals("NUMBER") && scale <= 0 && pk == false) {
            t = "Integer";
        } else if (typeName.equals("NUMBER") && scale > 0 && pk == false) {
            t = "Double";
        } else if (typeName.equals("DATE")) {
            t = "Date";
        } else if (typeName.equals("CHAR")) {
            t = "Character";
        } else if (typeName.equals("INTEGER") && pk == true) {
            t = "Long";
        } else if (typeName.equals("NUMBER") && pk == true) {
            t = "Long";
        }
        return t;
    }

    public static void main(String[] args) {


        try {
//            String host = "10.80.1.132";
//            String sid = "thpostdb";
//            String userName = "hr_owner1";
//            String password = "passw0rd";

//  post =
//  (DESCRIPTION =
//    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.254.8.22)(PORT = 1521))
//    (CONNECT_DATA =
//      (SERVER = DEDICATED)
//      (SERVICE_NAME = post)
//    )
//  )

//            String host = "10.80.1.132";
//            String sid = "thpostdb";
//            String userName = "hr_owner1";
//            String password = "passw0rd";

//            String host = "10.80.1.165";
//            String sid = "tpbi";
//            String userName = "tpstat";
//            String password = "passw0rd";

//            String host = "10.254.3.101";
//            String sid = "bireport";
//            String userName = "tpstat";
//            String password = "tpstat2011";

//            String host = "10.80.1.165";
//            String sid = "mtl";
//            String userName = "mtram";
//            String password = "passw0rd";

//            String host = "10.1.0.187";
//            String sid = "orcl";
//            String userName = "mtram";
//            String password = "passw0rd";

//            String host = "10.1.0.250";
//            String sid = "mtramuat";
//            String userName = "mtram_ph3";
//            String password = "passw0rd";

//            String host = "localhost";
//            String sid = "orcl";
//            String userName = "mtram";
//            String password = "passw0rd";


            String host = "10.1.0.250";
            String sid = "WFPOSUAT";
            String userName = "UATWFPOS_SOAINFRA";
            String password = "plokij123";


            log.info("Start...");
            DBGenerateFactory work = new DBGenerateFactory(host, sid, userName, password);
            //String sql = "select et.ou_code,og.div_short,em.emp_code, pre.prefix_name || em.first_name || ' '|| em.last_name as emp_name,\n" + "po.position_short,ec.course_code,ec.course_desc,et.start_date,et.end_date,etd.training_fee,ec.institute,etd.flag_report\n" + ",(select count(*) from tn_external_train_detail xx \n" + "where xx.ou_code = et.ou_code and xx.emp_code = etd.emp_code\n" + "and xx.training_year = 2554 \n" + ") as emp_count\n" + "\n" + "from tn_external_train et\n" + "left join tn_external_course ec on ec.ou_code = et.ou_code and ec.course_code = et.course_code\n" + "left join tn_external_train_detail etd on etd.ou_code = et.ou_code and etd.training_year = et.training_year\n" + "     and et.course_code = etd.course_code \n" + "left join pn_employee em on em.ou_code = etd.ou_code and em.emp_code = etd.emp_code\n" + "left join pn_organization og on og.ou_code = em.ou_code and og.code_seq = em.code_seq and og.inactive = 'N'\n" + "left join pn_position po on po.ou_code = em.ou_code and po.gwork_code = em.gwork_code and po.position_code = em.position_code\n" + "left join db_pre_suff pre on pre.pre_suff_code = em.pre_name\n" + "where et.ou_code = '001' ";

            String sql =  "select ba.bank_code,\n" +
                    "       b.bank_name,\n" +
                    "       ba.bank_account_no,\n" +
                    "       (select 1\n" +
                    "          from data_statement s\n" +
                    "         where s.account_no = ba.bank_account_no\n" +
                    "           and s.effective_date = to_date('04042013', 'ddmmyyyy')\n" +
                    "           and rownum = 1) as exists_row_flag\n" +
                    "  from mst_bank_account ba\n" +
                    "  join mst_bank b\n" +
                    "    on ba.bank_code = b.bank_code\n" +
                    " order by exists_row_flag nulls last, ba.bank_code, ba.bank_account_no";

            //String tableName = "INF_ENDR"; // DATA_INSURED_REF_HIS

            String tableName = "wftask"; // DATA_INSURED_REF_HIS

//            String tableName = "DATA_LCP_LOG"; // DATA_INSURED_REF_HIS

            /*





             */

            //work.genOracleFromSql("TN_EXTERNAL_COURSE", sql);


            partPackage = "package th.co.muangthai.model.bean.master;";

//            partPackage = "package th.co.muangthai.model.bean.lcp;";

//            partPackage = "package th.co.muangthai.model.bean.master;";
//            partPackage = "package th.co.muangthai.model.bean.master;";
            //partPackage = "package th.co.muangthai.model.bean.load;";
//            partPackage = "package th.co.muangthai.model.bean.temp;";
            //partPackage = "package th.co.muangthai.model.bean.inf;";
//            partPackage = "package th.co.muangthai.model.bean.inf;";
            //String tableName= "gu_offense_detail";
            work.setPrintType(work.PRINT_OUT);
            work.setAnnotationFlag(true);


            work.genBean(tableName);
            //work.genBeanP(tableName);
            //work.genBeanP2(tableName);

            //work.genResultSetText(tableName, work.TYPE_RESULTSET);
//            work.genResultSetText("DATA_EXPENSE_MEMO", sql, work.TYPE_HIBERNATE);

            //work.setAnnotationFlag(true);
            //work.genOracle("PO_DAILY_IN_H");
        } catch (Exception ex) {
            ex.printStackTrace();
            Logger.getLogger(DBGenerateFactory.class.getName()).log(Level.SEVERE, null, ex);
        }

        log.info("End...");
    }
}