package com.ejie.y41e.utils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.ejie.y41e.common.Y41eSqlConstantes;

/**
 *  
 *
 */
public final class Y41eSqlUtils {

    /**
     * AND
     */
    public static final String AND = " AND ";
    /**
     * WHERE
     */
    public static final String WHERE = " WHERE ";
    /**
     * FROM
     */
    public static final String FROM = " FROM ";
    /**
     * SELECT
     */
    public static final String SELECT = " SELECT ";
    /**
     * SELECT_1
     */
    public static final String SELECT_1 = " SELECT 1 ";
    /**
     * SELECT_COUNT
     */
    public static final String SELECT_COUNT = " SELECT COUNT(1) ";
    /**
     * INSERT
     */
    public static final String INSERT = " INSERT INTO ";
    /**
     * UPDATE
     */
    public static final String UPDATE = " UPDATE ";
    /**
     * DELETE
     */
    public static final String DELETE = " DELETE FROM ";
    /**
     * TRUNC
     */
    public static final String TRUNC = " TRUNC ";
    /**
     * GROUP_BY
     */
    public static final String GROUP_BY = " GROUP BY ";
    /**
     * LEFT_JOIN
     */
    public static final String LEFT_JOIN = " LEFT JOIN ";
    /**
     * JOIN
     */
    public static final String JOIN = " JOIN ";
    /**
     * ORDER_BY
     */
    public static final String ORDER_BY = " ORDER BY ";
    /**
     * NVL
     */
    public static final String NVL = " NVL ";
    /**
     * NVL2
     */
    public static final String NVL2 = " NVL2 ";
    /**
     * NOT_EXISTS
     */
    public static final String NOT_EXISTS = " NOT EXISTS (";

    /**
     * DEFAULT_WHERE
     */
    public static final String DEFAULT_WHERE = " WHERE 1=1 ";

    /**
     * SUBSTR_500
     */
    public static final String SUBSTR_500 = " SUBSTR(?, 0, 500) ";

    /**
     * SUBSTR_250
     */
    public static final String SUBSTR_250 = " SUBSTR(?, 0, 250) ";

    /**
     * SUBSTR
     */
    public static final String SUBSTR = " SUBSTR ";

    /**
     * LENGTH_0_244
     */
    public static final String LENGTH_0_244 =", 0, 244) ";

    /**
     * El metodo constructor.
     */
    private Y41eSqlUtils() {
    }

    /**
     * @param campoSql
     *            String
     * @return String
     */
    public static String normalizarCampoSql(String campoSql) {
        final StringBuilder sqlBuffer = new StringBuilder();
        sqlBuffer.append(" TRANSLATE(UPPER(");
        sqlBuffer.append(campoSql);
        sqlBuffer.append("),'");
        sqlBuffer.append(Y41eSqlConstantes.CARACTERES_A_TRADUCIR);
        sqlBuffer.append("','");
        sqlBuffer.append(Y41eSqlConstantes.CARACTERES_TRADUCIDOS);
        sqlBuffer.append("') ");
        return sqlBuffer.toString();
    }

    /**
     * The method generarWhereIgual.
     *
     * @param campo
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereIgual(String campo, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(Y41eSqlUtils.AND).append(campo).append(" = ?");
            params.add(valor);
        }
        return where.toString();
    }

    /**
     * The method generarWhereLikeNormalizado.
     *
     * @param campo
     *            String
     * @param valor
     *            String
     * @param params
     *            List<Object>
     * @param startsWith
     *            Boolean
     * @return String
     */
    public static String generarWhereIgualNormalizado(String campo, String valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (StringUtils.isNotBlank(valor)) {
            where.append(Y41eSqlUtils.AND).append(Y41eSqlUtils.normalizarCampoSql(campo)).append(" =  ")
            .append(Y41eSqlUtils.normalizarCampoSql("?"));
            params.add(valor.toUpperCase());

        }
        return where.toString();
    }

    /**
     * The method generarWhereIgualTrim.
     *
     * @param campo
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereIgualTrim(String campo, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(" AND LTRIM(").append(campo).append(",0) = LTRIM(?,0)");
            params.add(valor);
        }
        return where.toString();
    }

    /**
     * The method generarWhereDistinto.
     *
     * @param campo
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereDistinto(String campo, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(Y41eSqlUtils.AND).append(campo).append(" != ?");
            params.add(valor);
        }
        return where.toString();
    }

    /**
     * The method generarWhereMayor.
     *
     * @param campo
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereMayor(String campo, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(Y41eSqlUtils.AND).append(campo).append(" > ?");
            params.add(valor);
        }
        return where.toString();
    }

    /**
     * The method generarWhereMayorIgual.
     *
     * @param campo
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereMayorIgual(String campo, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(Y41eSqlUtils.AND).append(Y41eSqlUtils.TRUNC)
            .append("(")
            .append(campo).append(") >= ?");
            params.add(valor);
        }
        return where.toString();
    }

    /**
     * The method generarWhereMayorIgualFecha.
     *
     * @param campo
     *            String
     * @param formato
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereMayorIgualFecha(String campo, String formato, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(" AND TO_DATE(");
            where.append(campo).append(", '");
            where.append(formato).append("')");
            where.append(" >= ?");
            params.add(valor);
        }
        return where.toString();
    }

    /**
     * The method generarWhereIgualFecha.
     *
     * @param campo
     *            String
     * @param formato
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereIgualFecha(String campo, String formato, String valor, List<Object> params) {
        SimpleDateFormat formatoDeFecha = new SimpleDateFormat(formato);
        try {
            if (StringUtils.isNotBlank(valor)) {
                return Y41eSqlUtils.generarWhereIgual(campo, formatoDeFecha.parse(valor), params);
            }
        } catch (ParseException e) {
            return "";
        }
        return "";
    }

    /**
     * The method generarWhereMenor.
     *
     * @param campo
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereMenor(String campo, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(Y41eSqlUtils.AND).append(campo).append(" < ?");
            params.add(valor);
        }
        return where.toString();
    }

    //
    /**
     * The method generarWhereMenorIgual.
     *
     * @param campo
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereMenorIgual(String campo, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(Y41eSqlUtils.AND).append(Y41eSqlUtils.TRUNC)
            .append("(")
            .append(campo).append(") <= ?");
            params.add(valor);
        }
        return where.toString();
    }

    //
    /**
     * The method generarWhereMenorIgual.
     * 
     * @param formato
     *            String
     * @param campo
     *            String
     * @param valor
     *            Object
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereMenorIgualFecha(String campo, String formato, Object valor, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valor != null) {
            where.append(" AND TO_DATE(");
            where.append(campo).append(", '");
            where.append(formato).append("')");
            where.append(" <= ?");
            params.add(valor);
        }
        return where.toString();
    }

    /**
     * The method generarWhereLike.
     *
     * @param campo
     *            String
     * @param valor
     *            String
     * @param params
     *            List<Object>
     * @param startsWith
     *            Boolean
     * @return String
     */
    public static String generarWhereLike(String campo, String valor, List<Object> params, Boolean startsWith) {
        final StringBuilder where = new StringBuilder();
        if (StringUtils.isNotBlank(valor)) {
            where.append(" AND UPPER(").append(campo).append(") like ? ESCAPE  '\\'");
            if (startsWith) {
                params.add(valor.toUpperCase() + "%");
            } else {
                params.add("%" + valor.toUpperCase() + "%");
            }
            where.append(Y41eSqlUtils.AND).append(campo).append(" IS NOT NULL");
        }
        return where.toString();
    }

    /**
     * The method generarWhereLikeNormalizado.
     *
     * @param campo
     *            String
     * @param valor
     *            String
     * @param params
     *            List<Object>
     * @param startsWith
     *            Boolean
     * @return String
     */
    public static String generarWhereLikeNormalizado(String campo, String valor, List<Object> params,
            Boolean startsWith) {
        final StringBuilder where = new StringBuilder();
        if (StringUtils.isNotBlank(valor)) {
            where.append(Y41eSqlUtils.AND).append(Y41eSqlUtils.normalizarCampoSql(campo)).append(" like ")
            .append(Y41eSqlUtils.normalizarCampoSql("?")).append(" ESCAPE  '\\'");
            if (startsWith) {
                params.add(valor.toUpperCase() + "%");
            } else {
                params.add("%" + valor.toUpperCase() + "%");
            }
            where.append(Y41eSqlUtils.AND).append(campo).append(" IS NOT NULL");
        }
        return where.toString();
    }

    /**
     * The method generarWhereSearch.
     *
     * @param campo
     *            String
     * @param valor
     *            String
     * @param params
     *            List<Object>
     * @param startsWith
     *            Boolean
     * @param search
     *            Boolean
     * @return String
     */
    public static String generarWhereSearch(String campo, String valor, List<Object> params, Boolean startsWith,
            Boolean search) {
        if (search) {
            return Y41eSqlUtils.generarWhereLikeNormalizado(campo, valor, params, startsWith);
        } else {
            return Y41eSqlUtils.generarWhereIgual(campo, valor, params);
        }
    }

    /**
     * The method generarWhereIn.*
     *
     * @param campo
     *            String*
     * @param valores
     *            List<?>*
     * @param params
     *            List<Object>*@return String
     */

    public static String generarWhereIn(String campo, List<?> valores, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valores != null && !valores.isEmpty() && !(valores.size() == 1 && "[]".equals(valores.get(0)))) {
            where.append(Y41eSqlUtils.AND).append(campo).append(" IN (");
            for (Integer i = 0; i < valores.size(); i++) {
                if (i > 0) {
                    where.append(", ");
                }
                where.append("?");
            }
            where.append(")");
            params.addAll(valores);
        }
        return where.toString();
    }

    /**
     * The method generarWhereNotIn.
     *
     * @param campo
     *            String
     * @param valores
     *            List<?>
     * @param params
     *            List<Object>
     * @return String
     */
    public static String generarWhereNotIn(String campo, List<?> valores, List<Object> params) {
        final StringBuilder where = new StringBuilder();
        if (valores != null && !valores.isEmpty()) {
            where.append(Y41eSqlUtils.AND).append(campo).append(" NOT IN (");
            for (Integer i = 0; i < valores.size(); i++) {
                if (i > 0) {
                    where.append(", ");
                }
                where.append("?");
            }
            where.append(")");
            params.addAll(valores);
        }
        return where.toString();
    }

    /**
     * The method getInteger.
     *
     * @param rs
     *            ResultSet
     * @param strColName
     *            String
     * @return Integer
     * @throws SQLException
     *             e
     */
    public static Integer getInteger(ResultSet rs, String strColName) throws SQLException {
        int nValue = rs.getInt(strColName);
        return rs.wasNull() ? null : nValue;
    }

    /**
     * The method getLong.
     *
     * @param rs
     *            ResultSet
     * @param strColName
     *            String
     * @return Integer
     * @throws SQLException
     *             e
     */
    public static Long getLong(ResultSet rs, String strColName) throws SQLException {
        Long nValue = rs.getLong(strColName);
        return rs.wasNull() ? null : nValue;
    }

    /**
     * The method getInteger.
     *
     * @param rs
     *            ResultSet
     * @param strColName
     *            String
     * @return Double
     * @throws SQLException
     *             e
     */
    public static Double getDouble(ResultSet rs, String strColName) throws SQLException {
        double nValue = rs.getDouble(strColName);
        return rs.wasNull() ? null : nValue;
    }
}
