/*
 * 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.elotech.websaude.integracao.cnes.backend.controller;

import br.com.elotech.websaude.integracao.cnes.backend.model.Estabelecimento;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import static br.com.elotech.websaude.integracao.cnes.backend.model.dao.Conexao.*;

/**
 *
 * @author elotech
 */
public class MedicoController {
    
    PreparedStatement pstmt;
    
    public void prestadorDeServico(List<Estabelecimento> estabelecimentos) throws SQLException {
        
        for(Estabelecimento e:estabelecimentos){
            
            int medCodigo = getMedicoByCnes(e.cnes);

            String sql = "";

            if (medCodigo > 0) { //ALTERACAO
                sql = "UPDATE medico SET "
                        + "cnes_unidade_id=?, " //1
                        + "med_crm=?,"//2
                        + "uf_codigo_crm=?,"//3
                        + "cnes=?,"//4
                        + "cnes_cnpj_mant=?,"//5
                        + "cnes_pfpj_ind=?,"//6
                        + "cnes_nivel_dep=?,"//7
                        + "cnes_r_social=?,"//8
                        + "med_nome=?,"//9
                        + "med_endereco=?,"//10
                        + "med_end_numero=?,"//11
                        + "med_end_complemento=?,"//12
                        + "med_end_bairro=?,"//13
                        + "med_end_cep=?,"//14
                        + "cnes_reg_saude=?,"//15
                        + "cnes_micro_reg=?,"//16
                        + "cnes_dist_sanit=?,"//17
                        + "cnes_dist_admin=?,"//18
                        + "med_end_telefone=?,"//19
                        + "cnes_fax=?,"//20
                        + "med_email=?,"//21
                        + "med_cpf=?,"//22
                        + "med_cnpj=?,"//23
                        + "cnes_cod_ativ=?,"//24
                        + "cnes_cod_client=?,"//25
                        + "cnes_num_alvara=?,"//26
                        + "cnes_data_exped=?,"//27
                        + "cnes_ind_orgexp=?,"//28
                        + "cnes_tp_unid_id=?,"//29
                        + "cnes_cod_turnat=?,"//30
                        + "cnes_d_tercsih=?,"//31
                        + "cnes_sigestgest=?,"//32
                        + "cnes_codmungest=?,"//33
                        + "cnes_statusmov=?,"//34
                        + "cnes_data_atu=?,"//35
                        + "cnes_usuario=?,"//36
                        + "med_cnes_importacao=true,"
                        + "med_nome_fantasia=?," +
                        "prestador_servico='O'," +
                        "cid_codigo=(select cid_codigo FROM cidade WHERE cid_codigo_ibge like '"+tratar(e.codmungest, 7)+"%' AND '"+tratar(e.codmungest, 7)+"'<>'null') "//3
                        + "WHERE med_codigo=" + medCodigo;

            } else { //INSERCAO
                sql = "INSERT INTO medico("
                        + "cnes_unidade_id, " //1
                        + "med_crm,"            //2
                        + "uf_codigo_crm,"//3
                        + "cnes,"//4
                        + "cnes_cnpj_mant,"//5
                        + "cnes_pfpj_ind,"//6
                        + "cnes_nivel_dep,"//7
                        + "cnes_r_social,"//8
                        + "med_nome,"//9
                        + "med_endereco,"//10
                        + "med_end_numero,"//11
                        + "med_end_complemento,"//12
                        + "med_end_bairro,"//13
                        + "med_end_cep,"//14
                        + "cnes_reg_saude,"//15
                        + "cnes_micro_reg,"//16
                        + "cnes_dist_sanit,"//17
                        + "cnes_dist_admin,"//18
                        + "med_end_telefone,"//19
                        + "cnes_fax,"//20
                        + "med_email,"//21
                        + "med_cpf,"//22
                        + "med_cnpj,"//23
                        + "cnes_cod_ativ,"//24
                        + "cnes_cod_client,"//25
                        + "cnes_num_alvara,"//26
                        + "cnes_data_exped,"//27
                        + "cnes_ind_orgexp,"//28
                        + "cnes_tp_unid_id,"//29
                        + "cnes_cod_turnat,"//30
                        + "cnes_d_tercsih,"//31
                        + "cnes_sigestgest,"//32
                        + "cnes_codmungest,"//33
                        + "cnes_statusmov,"//34
                        + "cnes_data_atu,"//35
                        + "cnes_usuario," + //36
                        "med_cnes_importacao," +
                        "med_nome_fantasia," +
                        "prestador_servico," +
                        "cid_codigo) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,true,?,'O',(select cid_codigo FROM cidade WHERE cid_codigo_ibge like '"+tratar(e.codmungest, 7)+"%' AND '"+tratar(e.codmungest, 7)+"'<>'null') )";//36
            }
            pstmt = CONN.prepareStatement(sql);
            
            pstmt.setString(1, tratar(e.unidadeId, 31));
            pstmt.setString(2, "NAOTEM");
            pstmt.setInt(3, 18);
            pstmt.setInt(   4, tratarInt(e.cnes));
            pstmt.setString(5, tratar(e.cnpjMant,14));
            pstmt.setString(6, tratar(e.pfpjInd,1));
            
            pstmt.setString(7, tratar(e.nivelDep, 1));
            pstmt.setString(8, tratar(e.rSocial, 60));
            pstmt.setString(9, tratar(e.rSocial, 60));
            pstmt.setString(10, e.logradouro);
            pstmt.setString(11, tratar(e.numero, 5));
            pstmt.setString(12, tratar(e.complement, 60));
            pstmt.setString(13, tratar(e.bairro, 60));
            pstmt.setString(14, tratar(e.codCep, 9));
            pstmt.setString(15, tratar(e.regSaude, 4));
            pstmt.setString(16, tratar(e.microReg, 6));
            pstmt.setString(17, tratar(e.distSanit, 4));
            pstmt.setString(18, tratar(e.distAdmin, 4));
            pstmt.setString(19, tratar(e.telefone, 40));
            pstmt.setString(20, tratar(e.fax, 60));
            pstmt.setString(21, tratar(e.eMail, 60));
            pstmt.setString(22, tratar(e.cpf, 11));
            pstmt.setString(23, tratar(e.cnpj, 25));
            
            pstmt.setString(24, tratar(e.codAtiv, 2));
            
            pstmt.setString(25, tratar(e.codClient, 2));
            pstmt.setString(26, tratar(e.numAlvara, 60));
            pstmt.setDate(  27, tratarDate(e.dataExped));
            pstmt.setString(28, tratar(e.indOrgexp, 2));
            
            pstmt.setString(29, e.tpUnidId);
            pstmt.setString(30, e.codTurnat);
            
            pstmt.setString(31, tratar(e.dTercsih, 1));
            pstmt.setString(32, tratar(e.sigestgest, 2));
            pstmt.setString(33, tratar(e.codmungest, 7));
            pstmt.setString(34, tratar(e.statusmov, 1));
            pstmt.setDate(  35, tratarDate(e.dataAtu));
            pstmt.setString(36, tratar(e.usuario, 12));
            pstmt.setString(37, tratar(e.nomeFanta, 60));
            
            pstmt.executeUpdate();
            CONN.commit();
            CONN.prepareStatement("UPDATE unidade SET uni_cnes_importador_ignorar=true WHERE uni_cnes="+e.cnes).executeUpdate();
            CONN.commit();
//            medCodigo = getMedicoByCnes(e.cnes);
            
        }
        
    }
    
    public int getMedicoByCnes(String cnes) throws SQLException {
        int result = 0;

        if (cnes != null) {
            if (cnes.length() > 0) {
                int cnesInt = Integer.valueOf(cnes);
                PreparedStatement pstmt = CONN.prepareStatement("SELECT med_codigo FROM medico WHERE cnes=?");
                pstmt.setInt(1, cnesInt);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    result = rs.getInt("med_codigo");
                }
            }

        }

        return result;
    }
    
}