/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ctecinf.nfe;

import br.com.ctecinf.Utils;
import br.com.ctecinf.Config;
import br.com.ctecinf.Database;
import br.com.ctecinf.Empresa;
import br.com.ctecinf.Log;
import br.com.ctecinf.Main;
import br.com.ctecinf.table.TableModel;
import br.inf.portalfiscal.nfe.v100.evento.TProcEvento;
import br.inf.portalfiscal.nfe.v400.autorizacao.TNFe;
import br.inf.portalfiscal.nfe.v400.autorizacao.TNfeProc;
import br.inf.portalfiscal.nfe.v400.inutilizacao.TRetInutNFe;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 *
 * @author Cássio Conceição
 * @since 27/05/2019
 * @version 201905
 * @see http://ctecinf.com.br/
 */
public class Controller extends Database {

    public static Long getNextNNF() throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String query = "SELECT MAX(nnf) FROM nfe WHERE serie = '" + Empresa.getSerie() + "' AND tp_amb = " + Config.get("nfe.amb");

        try (Connection conn = Controller.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {
            if (rs.next()) {
                return rs.getLong(1) + 1;
            }
        } finally {
            DB_NAME = Config.get("db.name");
        }

        return null;
    }

    public static List<File> getXMLFiles(Calendar dt) throws Exception, IOException {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        dt.set(Calendar.DATE, dt.getActualMinimum(Calendar.DATE));
        java.sql.Date start = new java.sql.Date(dt.getTimeInMillis());

        dt.set(Calendar.DATE, dt.getActualMaximum(Calendar.DATE));
        java.sql.Date end = new java.sql.Date(dt.getTimeInMillis());

        String between = " BETWEEN '" + start + "' AND DATEADD(1 DAY TO DATE '" + end + "') ";

        String query = "SELECT nfe.xml_path, nfe_evento.xml_path, EXTRACT(MONTH FROM nfe.data_emissao), EXTRACT(YEAR FROM nfe.data_emissao), nfe.xml FROM nfe LEFT JOIN nfe_evento ON nfe_evento.nfe_id = nfe.id WHERE nfe.data_emissao " + between + " AND nfe.tp_amb=" + Config.get("nfe.amb");

        String mes = "";
        String ano = "";

        List<File> files = new ArrayList();

        try (Connection conn = Controller.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {

            while (rs.next()) {

                File file = new File(rs.getString(1));

                if (!file.exists()) {
                    Utils.writeFile(rs.getString(1), rs.getString(5));
                }

                files.add(file);

                String ev = rs.getString(2);

                if (ev != null && !ev.isEmpty()) {
                    files.add(new File(ev));
                }

                if (mes.isEmpty() && ano.isEmpty()) {
                    mes = rs.getString(3);
                    ano = rs.getString(4);
                }
            }
        } finally {
            DB_NAME = Config.get("db.name");
        }

        return files;
    }

    public static TableModel getNFCeEmitidas() throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String query = "SELECT "
                + "t.data_emissao AS \"Data\", "
                + "t.versao AS \"Versão\", "
                + "t.nnf AS \"Num. NFCe\", "
                + "CASE WHEN t.cancelada=1 THEN 'Sim' ELSE '' END AS \"Cancelada\", "
                + "t.chave_acesso AS \"Chave de Acesso\", "
                + "t.cliente AS \"CPF\", "
                + "CASE WHEN t.cancelada=1 THEN (SELECT xml_path FROM nfe_evento WHERE nfe_id=t.id) ELSE t.xml_path END AS \"Arquivo\", "
                + "t.qr_code AS \"URL\", "
                + "CASE WHEN t.cancelada=1 THEN (SELECT xml FROM nfe_evento WHERE nfe_id=t.id) ELSE t.xml END AS \"XML\" "
                + "FROM nfe t "
                + "WHERE t.tp_amb=" + Config.get("nfe.amb")
                + " ORDER BY t.data_emissao DESC";
        try {
            return Controller.getTableModel(query);
        } finally {
            DB_NAME = Config.get("db.name");
        }
    }

    public static TableModel getQuebraSequencia() throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String query = "SELECT sequence_value "
                + "FROM number_sequence(1, (SELECT MAX(nnf) FROM nfe WHERE tp_amb='" + Config.get("nfe.amb") + "')) "
                + "WHERE sequence_value NOT IN (SELECT nnf FROM nfe WHERE tp_amb='" + Config.get("nfe.amb") + "')";

        TableModel model = new TableModel();
        model.addColumn("Número Inicial", String.class);
        model.addColumn("Número Final", String.class);

        try (Connection conn = Controller.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {

            if (rs.next()) {

                Long num = rs.getLong(1);
                Object[] row = new Object[2];
                row[0] = rs.getLong(1);

                while (rs.next()) {

                    if (num + 1 != rs.getLong(1)) {

                        row[1] = num;
                        model.addRow(row);

                        row = new Object[2];
                        row[0] = rs.getLong(1);
                    }

                    num = rs.getLong(1);
                }

                row[1] = num;
                model.addRow(row);
            }
        } finally {
            DB_NAME = Config.get("db.name");
        }

        return model;
    }

    public static TableModel getNFCeRejeitadas() throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }
        String query = "SELECT "
                + "t.data_emissao AS \"Data\", "
                + "t.versao AS \"Versão\", "
                + "t.nnf AS \"Num. NFCe\", "
                + "t.motivo AS \"Motivo\", "
                + "t.cliente AS \"CPF\", "
                + "t.xml AS \"XML\", "
                + "t.qr_code AS \"URL\" "
                + "FROM nfe_rejeitada t "
                + "WHERE t.tp_amb=" + Config.get("nfe.amb")
                + " ORDER BY t.data_emissao DESC";

        try {
            return Controller.getTableModel(query);
        } finally {
            DB_NAME = Config.get("db.name");
        }
    }

    public static TableModel getVendas(Calendar dt) throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        dt.set(Calendar.DATE, dt.getActualMinimum(Calendar.DATE));
        java.sql.Date start = new java.sql.Date(dt.getTimeInMillis());

        dt.set(Calendar.DATE, dt.getActualMaximum(Calendar.DATE));
        java.sql.Date end = new java.sql.Date(dt.getTimeInMillis());

        String between = "nfe.data_emissao BETWEEN '" + start + "' AND DATEADD(1 DAY TO DATE '" + end + "') AND nfe.tp_amb = '" + Config.get("nfe.amb") + "' ";

        String query = "SELECT r.DESCRICAO AS \"Forma de Pagamento\", SUM(r.VALOR) AS \"Sub-Total\" "
                + "FROM nfe_pagamento r "
                + "LEFT JOIN nfe ON nfe.id = r.nfe_id "
                + "WHERE NOT (nfe.xml LIKE '%<xProd>PAGAMENTO ANTECIPADO</xProd>%') AND " + between
                + "GROUP BY r.DESCRICAO "
                + "ORDER BY r.DESCRICAO";

        return Controller.getTableModel(query);
    }

    public static TableModel getSaidaNCM(Calendar dt) throws Exception {

        dt.set(Calendar.DATE, dt.getActualMinimum(Calendar.DATE));
        java.sql.Date start = new java.sql.Date(dt.getTimeInMillis());

        dt.set(Calendar.DATE, dt.getActualMaximum(Calendar.DATE));
        java.sql.Date end = new java.sql.Date(dt.getTimeInMillis());

        String between = "nfe.data_emissao BETWEEN '" + start + "' AND DATEADD(1 DAY TO DATE '" + end + "') AND nfe.tp_amb = '" + Config.get("nfe.amb") + "' ";

        String query = "SELECT (SELECT descricao FROM ncm WHERE r.NCM_ID=ncm.id) AS \"NCM\", SUM(r.QTD_COM) AS \"Quantiade\", r.UND_COM AS \"Un. Comercial\" "
                + "FROM nfe_produto r "
                + "LEFT JOIN nfe ON nfe.id = r.nfe_id "
                + "WHERE " + between
                + "GROUP BY r.NCM_ID, r.UND_COM "
                + "ORDER BY r.NCM_ID";
        try {
            return Controller.getTableModel(query);
        } finally {
            DB_NAME = Config.get("db.name");
        }
    }

    public static String getXMLProc(Object nnf) throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String query = "SELECT nf.XML FROM NFE nf WHERE nf.NNF=" + nnf + " AND tp_amb=" + Config.get("nfe.amb");

        try (Connection conn = Controller.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {
            if (rs.next()) {
                return rs.getString(1);
            }
        } finally {
            DB_NAME = Config.get("db.name");
        }

        throw new Exception("Registro não encontrado.");
    }

    public static Long getIdLoteEvento() throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String query = "SELECT MAX(id_lote) FROM nfe_evento WHERE tp_amb=" + Config.get("nfe.amb");

        try (Connection conn = Controller.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {
            if (rs.next()) {
                return rs.getLong(1) + 1;
            }
        } finally {
            DB_NAME = Config.get("db.name");
        }

        throw new Exception("Registro não encontrado.");
    }

    public static Long getIdNFCe(String chaveAcesso) throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String query = "SELECT id FROM nfe WHERE tp_amb=" + Config.get("nfe.amb") + " AND chave_acesso = '" + chaveAcesso.replace("NFe", "").trim() + "'";

        try (Connection conn = Controller.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        } finally {
            DB_NAME = Config.get("db.name");
        }

        throw new Exception("Registro não encontrado.");
    }

    public static Long getNumeroAutorizacaoNFCe(String chaveAcesso) throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String query = "SELECT numero_autorizacao FROM nfe WHERE tp_amb=" + Config.get("nfe.amb") + " AND chave_acesso = '" + chaveAcesso.replace("NFe", "").trim() + "'";

        try (Connection conn = Controller.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        } finally {
            DB_NAME = Config.get("db.name");
        }

        throw new Exception("Registro não encontrado.");
    }

    public static void saveEvento(Long loteId, TProcEvento procEvento, String xmlProc, String xmlPath) throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String tpAmb = Config.get("nfe.amb");
        String versao = procEvento.getVersao();
        String nProt = procEvento.getRetEvento().getInfEvento().getNProt();
        String chaveAcesso = procEvento.getRetEvento().getInfEvento().getChNFe();
        Timestamp ts = new Timestamp(Utils.dateNFe2Date(procEvento.getRetEvento().getInfEvento().getDhRegEvento()).getTime().getTime());
        Long nfeId = getIdNFCe(chaveAcesso);

        String sql = "INSERT INTO nfe_evento (versao, tp_amb, data_evento, id_lote, chave_acesso_nfe, xml_path, xml, numero_protocolo, nfe_id) VALUES ('" + versao + "', " + tpAmb + ", '" + ts + "', " + loteId + ", '" + chaveAcesso + "', '" + xmlPath + "', '" + xmlProc + "', '" + nProt + "', " + nfeId + ")";

        Controller.executeUpdate(sql);

        sql = "UPDATE nfe SET cancelada = '1' WHERE id=" + nfeId;

        Controller.executeUpdate(sql);

        DB_NAME = Config.get("db.name");
    }

    public static void saveInutilizacao(TRetInutNFe retInutNFe, String xmlPath, String xmlProc) throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        String tpAmb = Config.get("nfe.amb");
        String nAut = retInutNFe.getInfInut().getNProt();
        String serie = retInutNFe.getInfInut().getSerie();
        String versao = retInutNFe.getVersao();

        Timestamp ts = new Timestamp(Utils.dateNFe2Date(retInutNFe.getInfInut().getDhRecbto()).getTime().getTime());

        int nnfIni = Integer.parseInt(retInutNFe.getInfInut().getNNFIni());
        int nnfFim = Integer.parseInt(retInutNFe.getInfInut().getNNFFin());

        try (Connection conn = Controller.openConnection(); Statement st = conn.createStatement()) {
            for (int nnf = nnfIni; nnf <= nnfFim; nnf++) {
                String sql = "INSERT INTO nfe (versao, serie, tp_amb, data_emissao, nnf, xml_path, xml, numero_autorizacao, chave_acesso, cliente, qr_code) VALUES ('" + versao + "', '" + serie + "', " + tpAmb + ", '" + ts + "', " + nnf + ", '" + xmlPath + "', '" + xmlProc + "', '" + nAut + "', 'NFC-e INUTILIZADA', null, null)";
                st.executeUpdate(sql);
            }
        } finally {
            DB_NAME = Config.get("db.name");
        }
    }
    
    public static boolean importarNFCe(File xmlFile) throws Exception {

        if (Main.TITLE.contains("PENDRIVE")) {
            DB_NAME = Config.get("db.name.migrate");
        }

        TNfeProc nfeProc = Utils.unmarshaller(TNfeProc.class, Utils.readFile(xmlFile));

        int tpAmb = Integer.parseInt(nfeProc.getNFe().getInfNFe().getIde().getTpAmb());
        String nnf = nfeProc.getNFe().getInfNFe().getIde().getNNF();

        String version = nfeProc.getNFe().getInfNFe().getVersao();
        String serie = nfeProc.getNFe().getInfNFe().getIde().getSerie();
        String nAut = nfeProc.getProtNFe().getInfProt().getNProt();

        Calendar cal = Utils.dateNFe2Date(nfeProc.getNFe().getInfNFe().getIde().getDhEmi());

        // Salva o XML
        String xmlProc = Utils.marshaller(nfeProc);

        File file = new File(NFCe.getProcNFePath(cal), nAut + "_v" + version + "-procNFe.xml");

        if (!file.exists()) {

            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }

            try (PrintWriter out = new PrintWriter(new BufferedWriter(new FileWriter(file, false)))) {
                out.println(xmlProc);
            }
        }

        // Gera PDF
        File pdf = DanfePDF.createFile(file);

        // Envia email para cliente
        if (nfeProc.getNFe().getInfNFe().getDest() != null && nfeProc.getNFe().getInfNFe().getDest().getEmail() != null && !nfeProc.getNFe().getInfNFe().getDest().getEmail().isEmpty()) {
            //DateFormat df = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.SHORT, new Locale("pt", "BR"));
            //Email.send(nfeProc.getNFe().getInfNFe().getDest().getEmail(), "NFC-e " + nfeProc.getNFe().getInfNFe().getEmit().getXNome(), "Em anexo segue sua NFC-e referente a compra efetuada na data " + df.format(Utils.dateNFe2Date(nfeProc.getNFe().getInfNFe().getIde().getDhEmi())) + " na empresa " + nfeProc.getNFe().getInfNFe().getEmit().getXNome() + ".\n\nAtt,\n" + nfeProc.getNFe().getInfNFe().getEmit().getXNome() + "\nFone: " + nfeProc.getNFe().getInfNFe().getEmit().getEnderEmit().getFone(), file, pdf);
        }

        // Salva no banco de dados
        try (Connection conn = Controller.openConnection()) {

            String chaveAcesso = nfeProc.getNFe().getInfNFe().getId().replace("NFe", "");
            Timestamp ts = new Timestamp(Utils.dateNFe2Date(nfeProc.getNFe().getInfNFe().getIde().getDhEmi()).getTime().getTime());
            String cliente = "NULL";
            String qrCode = nfeProc.getNFe().getInfNFeSupl().getQrCode();

            if (nfeProc.getNFe().getInfNFe().getDest() != null) {

                if (nfeProc.getNFe().getInfNFe().getDest().getCPF() != null) {
                    cliente = "'" + nfeProc.getNFe().getInfNFe().getDest().getCPF() + "'";
                }

                if (nfeProc.getNFe().getInfNFe().getDest().getIdEstrangeiro() != null) {
                    cliente = "'" + nfeProc.getNFe().getInfNFe().getDest().getIdEstrangeiro() + "'";
                }

                String nome = "NULL";
                String email = "NULL";

                if (nfeProc.getNFe().getInfNFe().getDest().getXNome() != null) {
                    nome = "'" + nfeProc.getNFe().getInfNFe().getDest().getXNome() + "'";
                }

                if (nfeProc.getNFe().getInfNFe().getDest().getEmail() != null) {
                    email = "'" + nfeProc.getNFe().getInfNFe().getDest().getEmail() + "'";
                }

                try (Statement st = conn.createStatement()) {
                    st.executeUpdate("INSERT INTO cliente (id, nome, cpf, email, data_cadastro) VALUES (GEN_ID(SEQ_CLIENTE_ID, 1), " + nome + ", " + cliente + ", " + email + ", '" + ts + "')");
                } catch (Exception ex) {
                    System.err.println("Cliente já cadastrado.");
                }
            }

            Long nfeId = null;

            try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT id FROM NFE WHERE NNF=" + nnf)) {
                if (rs.next()) {
                    nfeId = rs.getLong(1);
                }
            }

            if (nfeId != null) {
                importPagProd(conn, nfeId, nfeProc);
            } else {

                String sql = "INSERT INTO nfe (id, versao, serie, tp_amb, data_emissao, nnf, xml_path, xml, numero_autorizacao, chave_acesso, cliente, qr_code) VALUES (GEN_ID(SEQ_NFE_ID, 1), '" + version + "', '" + serie + "', " + tpAmb + ", '" + ts + "', " + nnf + ", '" + file.getPath() + "', '" + xmlProc + "', '" + nAut + "', '" + chaveAcesso + "', " + cliente + ", '" + qrCode + "')";

                try (Statement st = conn.createStatement()) {

                    st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                    ResultSet rs = st.getGeneratedKeys();

                    if (rs.next()) {
                        importPagProd(conn, rs.getLong(1), nfeProc);
                    }
                }
            }

        } finally {
            DB_NAME = Config.get("db.name");
        }

        return true;
    }

    private static void importPagProd(Connection conn, Long nfeId, TNfeProc nfeProc) throws SQLException {

        if (nfeId != null) {

            boolean exist = false;

            try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT id FROM nfe_pagamento WHERE nfe_id=" + nfeId)) {
                if (rs.next()) {
                    exist = true;
                }
            }

            // Incluir pagamentos
            if (!exist) {

                for (TNFe.InfNFe.Pag.DetPag pag : nfeProc.getNFe().getInfNFe().getPag().getDetPag()) {

                    String sql1;

                    if (pag.getTPag().equalsIgnoreCase(Constants.T_PAG_CARTAO_CRED)) {
                        sql1 = "INSERT INTO nfe_pagamento (id, descricao, valor, nfe_id) VALUES (GEN_ID(SEQ_NFE_PAGAMENTO_ID, 1), '" + Constants.FORMA_PGTO_CARTAO_CRED + " (" + getCartao(pag.getCard()) + ")', '" + pag.getVPag() + "', '" + nfeId + "')";
                    } else if (pag.getTPag().equalsIgnoreCase(Constants.T_PAG_CARTAO_DEB)) {
                        sql1 = "INSERT INTO nfe_pagamento (id, descricao, valor, nfe_id) VALUES (GEN_ID(SEQ_NFE_PAGAMENTO_ID, 1), '" + Constants.FORMA_PGTO_CARTAO_DEB + " (" + getCartao(pag.getCard()) + ")', '" + pag.getVPag() + "', '" + nfeId + "')";
                    } else if (pag.getTPag().equalsIgnoreCase(Constants.T_PAG_CHEQUE)) {
                        sql1 = "INSERT INTO nfe_pagamento (id, descricao, valor, nfe_id) VALUES (GEN_ID(SEQ_NFE_PAGAMENTO_ID, 1), '" + Constants.FORMA_PGTO_CHEQUE + "', '" + pag.getVPag() + "', '" + nfeId + "')";
                    } else if (pag.getTPag().equalsIgnoreCase(Constants.T_PAG_CRED_LOJA)) {
                        sql1 = "INSERT INTO nfe_pagamento (id, descricao, valor, nfe_id) VALUES (GEN_ID(SEQ_NFE_PAGAMENTO_ID, 1), '" + Constants.FORMA_PGTO_CREDIARIO + "', '" + pag.getVPag() + "', '" + nfeId + "')";
                    } else if (pag.getTPag().equalsIgnoreCase(Constants.T_PAG_DINHEIRO)) {
                        sql1 = "INSERT INTO nfe_pagamento (id, descricao, valor, nfe_id) VALUES (GEN_ID(SEQ_NFE_PAGAMENTO_ID, 1), '" + Constants.FORMA_PGTO_DINHEIRO + "', '" + pag.getVPag() + "', '" + nfeId + "')";
                    } else if (pag.getTPag().equalsIgnoreCase(Constants.T_PAG_PIX)) {
                        sql1 = "INSERT INTO nfe_pagamento (id, descricao, valor, nfe_id) VALUES (GEN_ID(SEQ_NFE_PAGAMENTO_ID, 1), '" + Constants.FORMA_PGTO_PIX + "', '" + pag.getVPag() + "', '" + nfeId + "')";
                    } else if (pag.getTPag().equalsIgnoreCase(Constants.T_PAG_DEPOSITO)) {
                        sql1 = "INSERT INTO nfe_pagamento (id, descricao, valor, nfe_id) VALUES (GEN_ID(SEQ_NFE_PAGAMENTO_ID, 1), '" + Constants.FORMA_PGTO_DEPOSITO + "', '" + pag.getVPag() + "', '" + nfeId + "')";
                    } else {
                        sql1 = "INSERT INTO nfe_pagamento (id, descricao, valor, nfe_id) VALUES (GEN_ID(SEQ_NFE_PAGAMENTO_ID, 1), '" + Constants.FORMA_PGTO_OUTROS + "', '" + pag.getVPag() + "', '" + nfeId + "')";
                    }

                    try (Statement st1 = conn.createStatement()) {
                        st1.executeUpdate(sql1);
                    } catch (Exception ex) {
                        Log.create(ex);
                    }
                }
            }

            exist = false;

            try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT id FROM nfe_produto WHERE nfe_id=" + nfeId)) {
                if (rs.next()) {
                    exist = true;
                }
            }

            // Incluir Produtos
            if (!exist) {

                for (TNFe.InfNFe.Det det : nfeProc.getNFe().getInfNFe().getDet()) {

                    TNFe.InfNFe.Det.Prod prod = det.getProd();

                    try (Statement st1 = conn.createStatement(); ResultSet rs = st1.executeQuery("SELECT * FROM produto WHERE id=" + prod.getCProd())) {

                        if (!rs.next()) {

                            try (Statement st2 = conn.createStatement()) {

                                String sql = "INSERT INTO PRODUTO (ID, DESCRICAO, VALOR_UNITARIO, ALIQUOTA, UND_COM, CFOP_ID,"
                                        + "    NCM_ID, ICMS_ID, ICMS_ORIGEM_ID, ULTIMA_ATUALIZACAO) "
                                        + "VALUES ("
                                        + "    '" + prod.getCProd() + "', "
                                        + "    '" + prod.getXProd() + "', "
                                        + "    '" + prod.getVUnCom() + "', "
                                        + "    '35.49', "
                                        + "    '" + prod.getUCom() + "', "
                                        + "    '" + prod.getCFOP() + "', "
                                        + "    '" + prod.getNCM() + "', "
                                        + "    '102', "
                                        + "    '0', "
                                        + "    '" + new Date(System.currentTimeMillis()) + "'"
                                        + ")";

                                st2.executeUpdate(sql);
                            }
                        }
                    }

                    try (Statement st1 = conn.createStatement()) {
                        st1.executeUpdate("INSERT INTO NFE_PRODUTO (ID, PRODUTO_ID, NCM_ID, UND_COM, QTD_COM, VALOR_UNIT,NFE_ID) VALUES ("
                                + "GEN_ID(SEQ_NFE_PRODUTO_ID, 1), "
                                + "'" + prod.getCProd() + "', "
                                + "'" + prod.getNCM() + "', "
                                + "'" + prod.getUCom() + "', "
                                + "'" + prod.getQCom() + "', "
                                + "'" + prod.getVUnCom() + "', "
                                + "'" + nfeId + "')");
                    } catch (Exception ex) {
                        Log.create(ex);
                    }
                }
            }

        }
    }

    private static String getCartao(TNFe.InfNFe.Pag.DetPag.Card card) {

        for (Cartao c : Cartao.CARTOES) {

            if (card.getTBand().equals("99") && card.getCNPJ().equals("92934215000106")) {
                return Cartao.banricomprasInstance().toString();
            } else if (card.getTBand().equals("99") && card.getCNPJ().equals("01722480000167")) {
                return Cartao.verdecardInstance().toString();
            } else if (card.getTBand().equalsIgnoreCase(c.gettBand())) {
                return c.toString();
            }
        }

        Cartao c = new Cartao();
        c.setCnpj(card.getCNPJ());
        c.setcAut(card.getCAut());
        c.setTpIntegra(Integer.parseInt(card.getTpIntegra()));
        c.settBand(card.getTBand());

        return c.toString();
    }
}