Ejemplo n.º 1
0
      @Override
      protected Void doInBackground() throws Exception {
        progressBar.setValue(0);
        progressBar.setMaximum(6);
        btn_limpa_dados.setEnabled(false);
        btn_processa.setEnabled(false);
        int resp =
            JOptionPane.showConfirmDialog(
                panel, "Confirma?", "Limpeza de Dados", JOptionPane.YES_NO_OPTION);

        if (resp == 0) {
          // APAGANDO DADOS

          // PRODUTO
          if (cboxPRODU.isSelected()) {
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM PRODU");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(1);
            }
          }

          // FABRI
          if (cboxFABRI.isSelected()) {
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM FABRI");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(2);
            }
          }

          // FORNE
          if (cboxFORNE.isSelected()) {

            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM FORNE");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(3);
            }
          }

          // CLIEN
          if (cboxCLIEN.isSelected()) {

            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM CLIEN");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(4);
            }
          }

          // ENDER
          if (cboxENDER.isSelected()) {
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM CLXED");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(5);
            }

            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM ENDER");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(6);
            }
          }
          // JOptionPane.showMessageDialog(null,
          // "Dados importados com sucesso.");
          JOptionPane.showMessageDialog(
              getContentPane(),
              "Limpeza de dados realizada com sucesso",
              "Informação",
              JOptionPane.INFORMATION_MESSAGE);

        } else {
          JOptionPane.showMessageDialog(
              getContentPane(),
              "Limpeza de dados cancelada",
              "Informação",
              JOptionPane.INFORMATION_MESSAGE);
        }
        return null;
      }
Ejemplo n.º 2
0
      @Override
      protected Void doInBackground() throws Exception {
        progressBar.setValue(0);
        progressBar.setMaximum(31);
        btn_limpa_dados.setEnabled(false);
        btn_processa.setEnabled(false);

        int resp =
            JOptionPane.showConfirmDialog(
                panel, "Confirma?", "Processar Dados", JOptionPane.YES_NO_OPTION);

        if (resp == 0) {
          Connection ms = Conexao.getMysqlConnection();
          Connection vmd = Conexao.getSqlConnection();

          if (cboxTBNCM.isSelected()
              && cboxTBSEC.isSelected()
              && cboxFABRI.isSelected()
              && cboxPRODU.isSelected()
              && cboxCLIEN.isSelected()
              && cboxFORNE.isSelected()
              && cboxENDER.isSelected()) {

            // APAGANDO DADOS
            // PRODUTO
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM PRODU");
              stmt.close();
              System.out.println("Deletou PRODU");
              progressBar.setValue(1);
            }

            // FABRI
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM FABRI");
              stmt.close();
              System.out.println("Deletou FABRI");
              progressBar.setValue(2);
            }

            // FORNE
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM FORNE");
              stmt.close();
              System.out.println("Deletou FORNE");
              progressBar.setValue(3);
            }

            // TBNCM
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM TBNCM");
              stmt.close();
              System.out.println("Deletou TBNCM");
              progressBar.setValue(4);
            }

            // TBSEC
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM TBSEC");
              stmt.close();
              System.out.println("Deletou TBSEC");
              progressBar.setValue(5);
            }

            // CLIEN
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM CLIEN");
              stmt.close();
              System.out.println("Deletou CLIEN");
              progressBar.setValue(6);
            }

            // CLXED
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM CLXED");
              stmt.close();
              System.out.println("Deletou CLXED");
              progressBar.setValue(7);
            }

            // ENDER
            try (Statement stmt = Conexao.getSqlConnection().createStatement()) {
              stmt.executeUpdate("DELETE FROM ENDER");
              stmt.close();
              System.out.println("Deletou ENDER");
              progressBar.setValue(8);
            }
          }

          // IMPORTAÇÃO
          // TBNCM
          if (cboxTBNCM.isSelected()) {
            System.out.println("COMEÇOU TBNCM");
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM TBNCM");
              stmt.close();
              System.out.println("Deletou TBNCM");
              progressBar.setValue(9);
            }

            progressBar2.setValue(0);

            String msGRPRC = "SELECT * FROM ESTNCM";
            String vGRPRC = "Insert Into TBNCM (Cod_Ncm, Des_Ncm) Values (?,?)";
            try (PreparedStatement pVmd = vmd.prepareStatement(vGRPRC);
                PreparedStatement pMs = ms.prepareStatement(msGRPRC)) {
              ResultSet rs = pMs.executeQuery();

              // contar a qtde de registros
              int registros = contaRegistros("ESTNCM");
              progressBar2.setMaximum(registros);
              registros = 0;

              while (rs.next()) {
                // grava no varejo
                String ncm = rs.getString("ncm_codigo");
                if (ncm != null) {
                  ncm = ncm.replaceAll("\\D", "");
                  if (ncm.length() <= 8) {
                    pVmd.setString(1, rs.getString("ncm_codigo"));
                    pVmd.setString(2, rs.getString("ncm_descricao"));
                    pVmd.executeUpdate();
                  }
                }

                registros++;
                progressBar2.setValue(registros);
              }
              System.out.println("Funcionou TBNCM");
              pVmd.close();
              pMs.close();

              progressBar2.setValue(0);
            }
            progressBar.setValue(10);
          }

          // TBSEC
          if (cboxTBSEC.isSelected()) {
            System.out.println("COMEÇOU TBSEC");
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate(
                  "IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WHERE TABLE_NAME = 'tbsec' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'cod_aux' AND DATA_TYPE = 'VARCHAR' AND CHARACTER_MAXIMUM_LENGTH = 15 ) BEGIN ALTER TABLE tbsec ADD cod_aux VARCHAR(15) END ");
              stmt.close();
              System.out.println("CRIOU COLUNA AUX EM TBSEC");
              progressBar.setValue(11);
            }

            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM TBSEC");
              stmt.close();
              System.out.println("Deletou TBSEC");
              progressBar.setValue(12);
            }

            progressBar2.setValue(0);

            String msTBSEC = "SELECT * FROM ESTSEC";
            String vTBSEC = "Insert Into TBSEC (Cod_Seccao, Des_Seccao, Cod_Aux) Values (?,?,?)";
            try (PreparedStatement pVmd = vmd.prepareStatement(vTBSEC);
                PreparedStatement pMs = ms.prepareStatement(msTBSEC)) {

              ResultSet rs = pMs.executeQuery();

              // contar a qtde de registros
              int registros = contaRegistros("ESTSEC");
              progressBar2.setMaximum(registros);
              registros = 0;

              while (rs.next()) {
                // grava no varejo
                pVmd.setInt(1, prox("Cod_Seccao", "TBSEC"));
                pVmd.setString(2, rs.getString("sec_descricao"));
                pVmd.setString(3, rs.getString("sec_secao"));
                pVmd.executeUpdate();

                registros++;
                progressBar2.setValue(registros);
              }

              System.out.println("Funcionou TBSEC");
              pVmd.close();
              pMs.close();

              progressBar2.setValue(0);
            }
            progressBar.setValue(13);
          }

          // FABRI
          if (cboxFABRI.isSelected()) {
            System.out.println("COMEÇOU FABRI");
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM FABRI");
              stmt.close();
              System.out.println("Deletou FABRI");
              progressBar.setValue(14);
            }

            progressBar2.setValue(0);

            String msFabri = "SELECT * FROM ESTLAB";
            String vFabri = "Insert Into FABRI (Cod_Fabric, Des_Fabric, Num_Cnpj) Values (?,?,?)";
            try (PreparedStatement pVmd = vmd.prepareStatement(vFabri);
                PreparedStatement pFb = ms.prepareStatement(msFabri)) {

              ResultSet rs = pFb.executeQuery();

              // contar a qtde de registros
              int registros = contaRegistros("ESTLAB");
              progressBar2.setMaximum(registros);
              registros = 0;

              while (rs.next()) {
                // grava no varejo
                pVmd.setInt(1, rs.getInt("lab_laboratorio"));
                pVmd.setString(
                    2,
                    rs.getString("lab_razao").length() > 25
                        ? rs.getString("lab_razao").substring(0, 24)
                        : rs.getString("lab_razao"));
                pVmd.setString(
                    3,
                    rs.getString("lab_cnpj").length() > 14
                        ? rs.getString("lab_cnpj").substring(0, 14)
                        : rs.getString("lab_cnpj"));

                pVmd.executeUpdate();

                registros++;
                progressBar2.setValue(registros);
              }

              System.out.println("Funcionou Fabri");
              pVmd.close();
              pFb.close();

              progressBar2.setValue(0);
            }
            progressBar.setValue(15);
          }

          // PRODU
          if (cboxPRODU.isSelected()) {
            System.out.println("COMEÇOU PRODU");
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM PRODU");
              System.out.println("Deletou PRODU");
              progressBar.setValue(16);
            }

            progressBar2.setValue(0);

            String MsPRODU = "select * from estcad";
            String vPRODU =
                "Insert Into PRODU (Cod_Produt, Des_Produt, Des_Resumi, Des_Comple, Dat_Implan, Cod_Fabric, Cod_EAN, Cod_Ncm, Qtd_FraVen, Qtd_EmbVen, Ctr_Origem, Cod_Classi, Cod_Seccao, Cod_ClaTri, Ctr_Preco, Ctr_Lista, Ctr_Venda, Cod_GrpPrc) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try (PreparedStatement pVmd = vmd.prepareStatement(vPRODU);
                PreparedStatement pMs = ms.prepareStatement(MsPRODU); ) {

              ResultSet rs = pMs.executeQuery();

              // contar a qtde de registros
              int registros = contaRegistros("ESTCAD");
              progressBar2.setMaximum(registros);
              registros = 0;

              while (rs.next()) {
                // GRAVA NA PRODU
                pVmd.setInt(1, rs.getInt("CAD_CODIGO"));
                pVmd.setString(
                    2,
                    rs.getString("CAD_DESCRICAO").length() > 40
                        ? rs.getString("CAD_DESCRICAO").substring(0, 39)
                        : rs.getString("CAD_DESCRICAO"));
                pVmd.setString(
                    3,
                    rs.getString("CAD_DESCRICAO").length() > 24
                        ? rs.getString("CAD_DESCRICAO").substring(0, 23)
                        : rs.getString("CAD_DESCRICAO"));
                pVmd.setString(
                    4,
                    rs.getString("CAD_DESCRICAO").length() > 50
                        ? rs.getString("CAD_DESCRICAO").substring(0, 49)
                        : rs.getString("CAD_DESCRICAO"));
                pVmd.setDate(5, rs.getDate("CAD_DT_CADASTRO"));
                pVmd.setString(6, rs.getString("CAD_LABORATORIO"));
                pVmd.setString(7, rs.getString("CAD_COD_BARRA"));

                String ncm = rs.getString("CAD_NCM");
                if (ncm != null) {
                  ncm = ncm.replaceAll("\\D", "");
                  if (ncm.length() <= 8 && !ncm.equals("")) {
                    pVmd.setString(8, ncm);
                  } else {
                    pVmd.setString(8, "30049099");
                  }
                  pVmd.setString(8, "30049099");
                }

                pVmd.setString(9, rs.getString("CAD_QTDE_CAIXA"));
                pVmd.setInt(10, rs.getInt("CAD_QTDE_EMBALAGEM"));
                pVmd.setInt(11, 0);
                pVmd.setString(12, "0199");
                pVmd.setInt(13, 1);
                pVmd.setString(14, "A");
                pVmd.setString(15, "C");
                pVmd.setString(16, "N");
                pVmd.setString(17, "L");
                pVmd.setString(18, "A");

                pVmd.executeUpdate();

                // cadastraCamposQueFaltamPRODU(rs.getString("CODBARRA"));
                registros++;
                progressBar2.setValue(registros);
              }

              System.out.println("Funcionou PRODU");
              pVmd.close();
              pMs.close();

              progressBar2.setValue(0);
            }
            progressBar.setValue(17);
          }

          // PRXLJ
          if (cboxPRXLJ.isSelected()) {

            progressBar.setValue(18);

            String mProdu =
                "select CAD_CODIGO, CAD_CUSTO_MEDIO, CAD_ULT_PCOMPRA, CAD_PCUSTO from estcad1";
            String vPrxlj =
                "Update PRXLJ set Prc_CusLiqMed = ?, Prc_CusEnt = ?, Prc_CusLiq = ?, Prc_VenAtu = ? where Cod_Produt = ?";
            try (PreparedStatement pVmd = vmd.prepareStatement(vPrxlj);
                PreparedStatement pMs = ms.prepareStatement(mProdu)) {

              ResultSet rs = pMs.executeQuery();

              // contar a qtde de registros
              int registros = contaRegistros("ESTCAD1");
              progressBar2.setMaximum(registros);
              registros = 0;

              while (rs.next()) {
                // grava no varejo
                System.out.println(rs.getInt("CAD_CODIGO"));
                pVmd.setInt(1, rs.getInt("CAD_CUSTO_MEDIO"));
                pVmd.setString(2, rs.getString("CAD_ULT_PCOMPRA"));
                pVmd.setString(3, rs.getString("CAD_ULT_PCOMPRA"));
                pVmd.setString(4, rs.getString("CAD_PCUSTO"));
                pVmd.setInt(5, rs.getInt("CAD_CODIGO"));

                pVmd.executeUpdate();

                registros++;
                progressBar2.setValue(registros);
              }
              System.out.println("CADASTROU PRXLJ");
              ms.close();
              rs.close();

              progressBar2.setValue(0);
            }

            progressBar.setValue(19);
          }

          // FORNE
          if (cboxFORNE.isSelected()) {
            System.out.println("COMEÇOU FORNE");
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM FORNE");
              stmt.close();
              System.out.println("Deletou FORNE");
              progressBar.setValue(20);
            }

            progressBar2.setValue(0);

            String msFORNE =
                "select a.*, b.cid_cod_ibge as cod_ibge, b.cid_nome as cidade, b.cid_uf as uf from cpacdf a join carcid b on b.cid_cidade = a.cdf_cidade";
            String vFORNE =
                "Insert Into FORNE (Cod_Fornec, Num_CgcCpf, Num_CgfRg, Des_RazSoc, Des_Fantas, Cod_IBGE, Des_Endere, Des_Bairro, Num_Cep, Flg_Bloque, Num_Fone, Num_Fax, Des_Estado, Des_Cidade, Cod_RegTri, Nom_Contat, Des_Observ) Values (?,?,?,?,?,?,?,?,?,0,?,?,?,?,?,?,?)";
            try (PreparedStatement pVmd = vmd.prepareStatement(vFORNE);
                PreparedStatement pMs = ms.prepareStatement(msFORNE)) {

              ResultSet rs = pMs.executeQuery();

              // contar a qtde de registros
              int registros = contaRegistros("CPACDF");
              progressBar2.setMaximum(registros);
              registros = 0;

              while (rs.next()) {
                String tel = rs.getString("CDF_FONE");
                if (tel != null) {
                  tel = tel.replaceAll("\\D", "");
                }

                // grava no varejo
                pVmd.setInt(1, rs.getInt("CDF_COD_FORN"));
                pVmd.setString(
                    2, rs.getString("CDF_CNPJ").replace(".", "").replace("/", "").replace("-", ""));
                pVmd.setString(
                    3,
                    rs.getString("CDF_INSCRICAO")
                        .replace(".", "")
                        .replace("/", "")
                        .replace("-", ""));
                pVmd.setString(
                    4,
                    rs.getString("CDF_RAZAO").length() > 35
                        ? rs.getString("CDF_RAZAO").substring(0, 34)
                        : rs.getString("CDF_RAZAO"));
                pVmd.setString(
                    5,
                    rs.getString("CDF_RAZAO").length() > 25
                        ? rs.getString("CDF_RAZAO").substring(0, 24)
                        : rs.getString("CDF_RAZAO"));
                pVmd.setInt(6, rs.getInt("COD_IBGE"));
                pVmd.setString(
                    7,
                    rs.getString("CDF_ENDERECO").length() > 25
                        ? rs.getString("CDF_ENDERECO").substring(0, 24)
                        : rs.getString("CDF_ENDERECO"));
                pVmd.setString(
                    8,
                    rs.getString("CDF_BAIRRO").length() > 25
                        ? rs.getString("CDF_BAIRRO").substring(0, 24)
                        : rs.getString("CDF_BAIRRO"));
                pVmd.setString(
                    9, rs.getString("CDF_CEP").replace(".", "").replace("/", "").replace("-", ""));

                pVmd.setString(10, tel.length() > 11 ? tel.substring(0, 11) : tel);
                pVmd.setString(
                    11,
                    rs.getString("CDF_FAX")
                        .replace(".", "")
                        .replace("(", "")
                        .replace(")", "")
                        .replace("-", "")
                        .replaceAll(" ", ""));
                pVmd.setString(12, rs.getString("UF"));
                pVmd.setString(
                    13,
                    rs.getString("CIDADE").length() > 25
                        ? rs.getString("CIDADE").substring(0, 24)
                        : rs.getString("CIDADE"));

                if (rs.getString("UF").equals("CE")) {
                  pVmd.setInt(14, 4);
                }

                if (rs.getString("UF").equals("AC")
                    || rs.getString("UF").equals("AL")
                    || rs.getString("UF").equals("AP")
                    || rs.getString("UF").equals("AM")
                    || rs.getString("UF").equals("BA")
                    || rs.getString("UF").equals("DF")
                    || rs.getString("UF").equals("GO")
                    || rs.getString("UF").equals("MA")
                    || rs.getString("UF").equals("MT")
                    || rs.getString("UF").equals("MS")
                    || rs.getString("UF").equals("PB")
                    || rs.getString("UF").equals("PA")
                    || rs.getString("UF").equals("PE")
                    || rs.getString("UF").equals("PI")
                    || rs.getString("UF").equals("RN")
                    || rs.getString("UF").equals("RO")
                    || rs.getString("UF").equals("RR")
                    || rs.getString("UF").equals("SE")
                    || rs.getString("UF").equals("TO")) {
                  pVmd.setInt(14, 1);
                }

                if (rs.getString("UF").equals("ES")
                    || rs.getString("UF").equals("MG")
                    || rs.getString("UF").equals("PR")
                    || rs.getString("UF").equals("RJ")
                    || rs.getString("UF").equals("RS")
                    || rs.getString("UF").equals("SC")
                    || rs.getString("UF").equals("SP")) {

                  pVmd.setInt(14, 9);
                }

                pVmd.setString(
                    15,
                    rs.getString("CDF_CONTATO").length() > 25
                        ? rs.getString("CDF_CONTATO").substring(0, 24)
                        : rs.getString("CDF_CONTATO"));
                pVmd.setString(
                    16,
                    rs.getString("CDF_OBS").length() > 16
                        ? rs.getString("CDF_OBS").substring(0, 15)
                        : rs.getString("CDF_OBS"));

                // todo demais campo
                pVmd.executeUpdate();

                registros++;
                progressBar2.setValue(registros);
              }
              System.out.println("Funcionou FORNE");
              pVmd.close();
              pMs.close();

              progressBar2.setValue(0);
            }
            progressBar.setValue(21);
          }

          // CLIEN
          if (cboxCLIEN.isSelected()) {
            System.out.println("COMEÇOU CLIEN");
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate(
                  "IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WHERE TABLE_NAME = 'clien' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'clien_aux' AND DATA_TYPE = 'INT') BEGIN ALTER TABLE clien ADD clien_aux INT END ");
              stmt.close();
              System.out.println("CRIOU COLUNA clien_AUX");
              progressBar.setValue(22);
            }

            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate(
                  "IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WHERE TABLE_NAME = 'clien' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'empre_aux' AND DATA_TYPE = 'INT') BEGIN ALTER TABLE clien ADD empre_aux INT END ");
              stmt.close();
              System.out.println("CRIOU COLUNA empre_AUX");
              progressBar.setValue(23);
            }

            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM CLIEN");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(24);
            }

            progressBar2.setValue(0);

            String msCLIEN =
                "select a.*,b.cid_nome as cidade, b.cid_uf as uf, c.bai_nome as bairro from carcdc a left join cadbai c on c.bai_codigo = a.cdc_bairro and c.bai_cidade = a.cdc_cidade left join carcid b on b.cid_cidade = a.cdc_cidade";
            String vCLIEN =
                "Insert Into CLIEN (Cod_Client, Nom_Client, Dat_Cadast, Num_CpfCgc, Num_RgCgf, Num_FonCel, Des_Email, Des_Observ, Cod_RegTri, clien_aux, empre_aux, Sex_Client, Cod_GrpCli, Ctr_Vencim, Dat_UltCpr, Cod_EndRes) Values (?,?,?,?,?,?,?,?,?,?,?,null,1,'N',?,?)";
            try (PreparedStatement pVmd = vmd.prepareStatement(vCLIEN);
                PreparedStatement pMs = ms.prepareStatement(msCLIEN)) {

              ResultSet rs = pMs.executeQuery();

              // contar a qtde de registros
              int registros = contaRegistros("CARCDC");
              progressBar2.setMaximum(registros);
              registros = 0;

              while (rs.next()) {

                // grava no varejo
                int codigo = prox("Cod_Client", "CLIEN");
                pVmd.setInt(1, codigo);
                pVmd.setString(
                    2,
                    rs.getString("CDC_RAZAO").length() > 35
                        ? rs.getString("CDC_RAZAO").substring(0, 34)
                        : rs.getString("CDC_RAZAO"));
                pVmd.setDate(3, rs.getDate("CDC_DT_CADASTRO"));

                String cpf = rs.getString("CDC_CPF");
                if (cpf != null) {
                  cpf = cpf.replaceAll("\\D", "");
                  pVmd.setString(4, cpf.length() > 14 ? cpf.substring(0, 14) : cpf);
                }

                String rg = rs.getString("CDC_RG");
                if (rg != null) {
                  rg = rg.replaceAll("\\D", "");
                  pVmd.setString(5, rg.length() > 15 ? rg.substring(0, 15) : rg);
                }

                String cel = rs.getString("CDC_CELULAR");
                if (cel != null) {
                  cel = cel.replaceAll("\\D", "");
                  pVmd.setString(6, cel.length() > 15 ? cel.substring(0, 15) : cel);
                }

                pVmd.setString(7, rs.getString("CDC_EMAIL"));

                String obs = rs.getString("CDC_OBSERVACAO1");
                if (obs != null) {
                  pVmd.setString(8, obs.length() > 16 ? obs.substring(0, 16) : obs);
                }

                if (rs.getString("UF").equals("CE")) {
                  pVmd.setInt(9, 4);
                }
                if (rs.getString("UF").equals("AC")
                    || rs.getString("UF").equals("AL")
                    || rs.getString("UF").equals("AP")
                    || rs.getString("UF").equals("AM")
                    || rs.getString("UF").equals("BA")
                    || rs.getString("UF").equals("DF")
                    || rs.getString("UF").equals("GO")
                    || rs.getString("UF").equals("MA")
                    || rs.getString("UF").equals("MT")
                    || rs.getString("UF").equals("MS")
                    || rs.getString("UF").equals("PB")
                    || rs.getString("UF").equals("PA")
                    || rs.getString("UF").equals("PE")
                    || rs.getString("UF").equals("PI")
                    || rs.getString("UF").equals("RN")
                    || rs.getString("UF").equals("RO")
                    || rs.getString("UF").equals("RR")
                    || rs.getString("UF").equals("SE")
                    || rs.getString("UF").equals("TO")) {
                  pVmd.setInt(9, 1);
                }

                if (rs.getString("UF").equals("ES")
                    || rs.getString("UF").equals("MG")
                    || rs.getString("UF").equals("PR")
                    || rs.getString("UF").equals("RJ")
                    || rs.getString("UF").equals("RS")
                    || rs.getString("UF").equals("SC")
                    || rs.getString("UF").equals("SP")) {
                  pVmd.setInt(9, 9);
                }

                pVmd.setString(10, rs.getString("CDC_CLIENTE"));
                pVmd.setString(11, rs.getString("CDC_EMPRESA"));
                pVmd.setDate(12, rs.getDate("CDC_DT_ULT_COMPRA"));

                String tel = rs.getString("CDC_TELEFONE");
                if (tel != null && !tel.equals("")) {
                  tel = tel.replace("\\D", "");
                  if (tel.length() > 15) {
                    tel = tel.substring(0, 15);
                  }
                  pVmd.setString(13, tel);
                } else {
                  pVmd.setString(13, Integer.toString(codigo));
                }

                pVmd.executeUpdate();

                registros++;
                progressBar2.setValue(registros);
              }
              System.out.println("Funcionou CLIEN");
              pVmd.close();
              pMs.close();

              progressBar2.setValue(0);
            }
            progressBar.setValue(25);
          }

          // ENDER
          if (cboxENDER.isSelected()) {
            System.out.println("COMEÇOU ENDER");
            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM CLXED");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(26);
            }

            try (Statement stmt = vmd.createStatement()) {
              stmt.executeUpdate("DELETE FROM ENDER");
              stmt.close();
              System.out.println("Deletou");
              progressBar.setValue(27);
            }

            progressBar2.setValue(0);

            String msCLIEN_ENDER =
                "select a.cdc_cliente, a.cdc_cpf, a.cdc_empresa, a.cdc_telefone, a.cdc_razao, a.cdc_endereco, a.cdc_cep, a.cdc_dt_cadastro, "
                    + "a.cdc_proximo, a.cdc_complemento, b.cid_nome as cidade, b.cid_uf as uf, c.bai_nome as bairro "
                    + "from carcdc a left join cadbai c on c.bai_codigo = a.cdc_bairro and c.bai_cidade = a.cdc_cidade "
                    + "left join carcid b on b.cid_cidade = a.cdc_cidade";
            String vENDER =
                "Insert Into ENDER (Cod_EndFon, Nom_Contat, Des_Endere, Des_Bairro, Num_CEP, Des_Estado, Des_Cidade, Dat_Cadast) Values (?,?,?,?,?,?,?,?)";
            String vCLXED = "Insert Into CLXED (Cod_Client, Cod_EndFon) Values (?,?)";
            // String vCLIEN = "Update CLIEN set Cod_EndRes = ? where Cod_Client = ?";
            try (PreparedStatement pVmd = vmd.prepareStatement(vENDER);
                PreparedStatement pMS = ms.prepareStatement(msCLIEN_ENDER);
                PreparedStatement pVmdCLXED = vmd.prepareStatement(vCLXED);
                // PreparedStatement pVmdCLIEN = vmd.prepareStatement(vCLIEN)
                ) {

              ResultSet rs = pMS.executeQuery();

              // contar a qtde de registros
              int registros = contaRegistros("CARCDC");
              progressBar2.setMaximum(registros);
              registros = 0;

              while (rs.next()) {
                // grava no varejo
                // esta indo codigo repetido
                int codigo = getCod_Clien(rs.getInt("CDC_CLIENTE"), rs.getInt("CDC_EMPRESA"));

                String tel = rs.getString("CDC_TELEFONE");
                if (tel != null) {
                  tel = tel.replaceAll("\\D", "");
                  if (tel.length() > 15) {
                    tel = tel.substring(0, 15);
                  }

                  if (!existeCod_EndFon(tel)) {
                    pVmd.setString(1, tel);

                    String razao = rs.getString("CDC_RAZAO");
                    if (razao != null) {
                      pVmd.setString(2, razao.length() > 35 ? razao.substring(0, 35) : razao);
                    }

                    pVmd.setString(3, rs.getString("CDC_ENDERECO"));

                    String bairro = rs.getString("BAIRRO");
                    if (bairro != null) {
                      pVmd.setString(4, bairro.length() > 25 ? bairro.substring(0, 25) : bairro);
                    }

                    String cep = rs.getString("CDC_CEP");
                    if (cep != null) {
                      cep = cep.replaceAll("\\D", "");
                      pVmd.setString(5, cep.length() > 8 ? cep.substring(0, 8) : cep);
                    }

                    pVmd.setString(6, rs.getString("UF"));

                    String cidade = rs.getString("CIDADE");
                    if (cidade != null) {
                      pVmd.setString(7, cidade.length() > 25 ? cidade.substring(0, 15) : cidade);
                    }

                    pVmd.setDate(8, rs.getDate("CDC_DT_CADASTRO"));
                    pVmd.executeUpdate();

                    // CLXED
                    pVmdCLXED.setInt(1, codigo);
                    pVmdCLXED.setString(2, tel);
                    pVmdCLXED.executeUpdate();

                    // CLIEN
                    // pVmdCLIEN.setString(1, wfone);
                    // pVmdCLIEN.setString(2, rs.getString("CODCLI"));
                    // pVmdCLIEN.executeUpdate();

                    registros++;
                    progressBar2.setValue(registros);
                  }
                }
              }
              System.out.println("Funcionou ENDER");
              pVmd.close();
              pMS.close();

              progressBar2.setValue(0);
            }
            progressBar.setValue(28);
          }

          try (Statement stmt = vmd.createStatement()) {
            stmt.executeUpdate(
                "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WHERE TABLE_NAME = 'tbsec' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'cod_aux' AND DATA_TYPE = 'VARCHAR' AND CHARACTER_MAXIMUM_LENGTH = 15 ) BEGIN ALTER TABLE tbsec DROP COLUMN cod_aux END ");
            stmt.close();
            System.out.println("DELETOU COLUNA COD_AUX DA TBSEC");
            progressBar.setValue(29);
          }

          try (Statement stmt = vmd.createStatement()) {
            stmt.executeUpdate(
                "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WHERE TABLE_NAME = 'clien' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'clien_aux' AND DATA_TYPE = 'VARCHAR' AND CHARACTER_MAXIMUM_LENGTH = 15 ) BEGIN ALTER TABLE clien DROP COLUMN clien_aux END ");
            stmt.close();
            System.out.println("DELETOU COLUNA CLIEN_AUX DA CLIEN");
            progressBar.setValue(30);
          }

          try (Statement stmt = vmd.createStatement()) {
            stmt.executeUpdate(
                "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WHERE TABLE_NAME = 'clien' AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME = 'empre_aux' AND DATA_TYPE = 'VARCHAR' AND CHARACTER_MAXIMUM_LENGTH = 15 ) BEGIN ALTER TABLE clien DROP COLUMN empre_aux END ");
            stmt.close();
            System.out.println("DELETOU COLUNA EMPRE_AUX DA CLIEN");
            progressBar.setValue(31);
          }

          // JOptionPane.showMessageDialog(null,
          // "Dados importados com sucesso.");
          JOptionPane.showMessageDialog(
              getContentPane(),
              "Processamento de dados realizado com sucesso",
              "Informação",
              JOptionPane.INFORMATION_MESSAGE);

        } else {
          JOptionPane.showMessageDialog(
              getContentPane(),
              "Processamento de dados cancelado",
              "Informação",
              JOptionPane.INFORMATION_MESSAGE);
        }

        return null;
      }