/*
 * 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 java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

/**
 *
 * @author elotech
 */
public class MicroareaController {
    PreparedStatement pstmt;

    public void inativarTodos() throws SQLException {
        CONN.prepareStatement("UPDATE microarea SET ativo = false").executeUpdate();
    }
    
    public void cadastrarMicroarea(int co_seq_equipe,String microarea,int usr_codigo,boolean ativo) throws SQLException{
        int mic_codigo = verificaSeExiste(co_seq_equipe,microarea,usr_codigo);
        String sql = "INSERT INTO microarea(mic_descricao,co_seq_equipe,mic_responsavel,ativo) VALUES(?,?,?,?)";
        if(mic_codigo==0){
            pstmt = CONN.prepareStatement(sql);
        }else{
            sql = "UPDATE microarea SET mic_descricao=?,co_seq_equipe=?,mic_responsavel=?,ativo=? WHERE mic_codigo=?";
            pstmt = CONN.prepareStatement(sql);
                    pstmt.setInt(5, mic_codigo);
        }
        pstmt.setString(1, microarea);
        pstmt.setInt(2, co_seq_equipe);
        pstmt.setInt(3, usr_codigo);
        pstmt.setBoolean(4, ativo);
        pstmt.executeUpdate();
        CONN.commit();
    }

    private int verificaSeExiste(int co_seq_equipe, String microarea, int usr_codigo) throws SQLException {
        pstmt = CONN.prepareStatement("SELECT mic_codigo FROM microarea WHERE co_seq_equipe=? AND mic_responsavel=? AND mic_descricao=?");
        pstmt.setInt(1, co_seq_equipe);
        pstmt.setInt(2, usr_codigo);
        pstmt.setString(3, microarea);
        ResultSet rs = pstmt.executeQuery();
        if(rs.next()){
            return rs.getInt("mic_codigo");
        }
        return 0;
    }
    
    
    
}