private void verificaStatus(int handle)
{
if (handle == 0 || handle == 1)
{
button1.Text = "Liberar";
button2.Text = "Limpar";
}
else if (handle > 0)
{
String query = "SELECT STATUS FROM TN_SERVIDOREMAIL WHERE HANDLE = " + handle;
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
SqlDataReader reader = conexao.Pesquisa(query);
while (reader.Read())
{
if (Convert.ToInt32(reader["STATUS"]) == StatusServidorEmail.Cadastrado)
{
button1.Text = "Liberar";
button2.Text = "Limpar";
}
else if (Convert.ToInt32(reader["STATUS"]) == StatusServidorEmail.Encerrado)
{
button1.Text = "Voltar";
button2.Text = "Cancelar";
}
}
conexao.Desconectar();
reader.Close();
}
}
private void textSQL_KeyDown(object sender, KeyEventArgs e)
{
String sQuery;
if (e.KeyCode == Keys.F5)
{
if (String.IsNullOrEmpty(textSQL.Text))
{
MessageBox.Show("pesquisa incorreta");
}
else if (String.IsNullOrEmpty(textSQL.SelectedText))
{
sQuery = textSQL.Text;
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
BindingSource bind = new BindingSource();
bind.DataSource = conexao.DataTable(sQuery);
gridSQL.AutoGenerateColumns = true;
gridSQL.DataSource = bind.DataSource;
}
else
{
sQuery = textSQL.SelectedText;
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
BindingSource bind = new BindingSource();
bind.DataSource = conexao.DataTable(sQuery);
gridSQL.AutoGenerateColumns = true;
gridSQL.DataSource = bind.DataSource;
}
}
}
private void botaoLimpar(int handle)
{
if (handle == 0)
{
richTextBoxPop.Text = "";
richTextBoxPopPorta.Text = "";
richTextBoxSmtp.Text = "";
richTextBoxSmtpPorta.Text = "";
richTextBoxNome.Text = "";
}
else
{
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
String query = "SELECT * FROM TN_SERVIDOREMAIL WHERE HANDLE = " + handle;
SqlDataReader reader = conexao.Pesquisa(query);
while (reader.Read())
{
richTextBoxSmtp.Text = reader["SMTP"].ToString();
richTextBoxSmtpPorta.Text = reader["SMTPPORTA"].ToString();
richTextBoxPop.Text = reader["POP"].ToString();
richTextBoxPopPorta.Text = reader["POPPORTA"].ToString();
richTextBoxNome.Text = reader["NOME"].ToString();
}
reader.Close();
conexao.Desconectar();
}
}
public static int BuscarHandleRegistro(String tabela, String campo, String valorCampo)
{
int handle = 0;
String query;
query = "SELECT HANDLE FROM " + tabela + " WHERE " + campo + " = " + valorCampo;
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
conexao.Pesquisa(query);
SqlDataReader resultSet;
resultSet = conexao.Pesquisa(query);
while (resultSet.Read())
{
try
{
handle = Convert.ToInt32(resultSet["HANDLE"]);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
resultSet.Close();
conexao.Desconectar();
if (String.IsNullOrEmpty(handle.ToString()) || handle == 0)
{
MessageBox.Show("Não é possível instanciar objeto com handle = 0");
}
return(handle);
}
private void tentaInserirRegistro(UServidorEmail servidor)
{
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
String query = "INSERT INTO TN_SERVIDOREMAIL(SMTP,SMTPPORTA,POP,POPPORTA,NOME,STATUS,EHSSLSMTP,EHSSLPOP)" +
"VALUES('" + servidor.Smtp + "'," + servidor.Smtpporta + ",'" + servidor.Pop + "'," + servidor.Popporta + ",'" + servidor.Nome + "'," +
StatusServidorEmail.Ativo + ",'" + servidor.Sslsmtp + "','" + servidor.Sslpop + "')";
try
{
SqlDataReader reader = conexao.Pesquisa(query);
MessageBox.Show("Inserido com sucesso!");
reader.Close();
conexao.Desconectar();
conexao.Conectar();
String atualizaHandle = "SELECT MAX(HANDLE) HANDLE FROM TN_SERVIDOREMAIL";
SqlDataReader reader2 = conexao.Pesquisa(atualizaHandle);
while (reader2.Read())
{
internalHandle = Convert.ToInt32(reader2["HANDLE"]);
}
reader2.Close();
conexao.Desconectar();
verificaStatus(internalHandle);
}
catch (SqlException e)
{
MessageBox.Show(e.Message);
}
}
public static Email.TEmail informacoesEmail(String usuario)
{
Email.TEmail email = new Email.TEmail();
String query;
query = "SELECT TOP 1 A.USUARIOEMAIL," +
"A.SENHA, " +
"B.SMTP, " +
"B.SSL, " +
"B.PORTA " +
"FROM PS_EMAIL A" +
"INNER JOIN PS_SERVIDOREMAIL B ON B.HANDLE = A.SERVIDOREMAIL" +
"WHERE A.USUARIO = " + getHandleUsuarioAtual(usuario);
DAL.Connection conexao = new DAL.Connection();
SqlDataReader reader;
reader = conexao.Pesquisa(query);
while (reader.Read())
{
email.Porta = Convert.ToInt32(reader["PORTA"]);
email.Smtp = (reader["SMTP"].ToString());
email.Senha = (reader["SENHA"].ToString());
email.Ssl = TN_TECNOLOGIA.Controles.Control.retornaBoolean(reader["SSL"].ToString());
email.Usuario = reader["USUARIOEMAIL"].ToString();
}
return(email);
}
private void InserirUsuario(object sender, EventArgs e)
{
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
if (textBoxPassWord.Text == "" || textBoxUsuario.Text == "")
{
MessageBox.Show("É necessário informar usuário ou senha para registrar.");
}
else
{
conexao.AdicionarNovoUsuario(textBoxUsuario.Text, textBoxPassWord.Text);
}
}
private void tentaInserirRegistro(UServidorEmail servidor)
{
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
String query = "INSERT INTO TN_SERVIDOREMAIL(SMTP,SMTPPORTA,POP,POPPORTA,NOME,STATUS,EHSSLSMTP,EHSSLPOP)" +
"VALUES('" + servidor.Smtp + "'," + servidor.Smtpporta + ",'" + servidor.Pop + "'," + servidor.Popporta + ",'" + servidor.Nome + "'," +
StatusServidorEmail.Ativo + ",'" + servidor.Sslsmtp + "','" + servidor.Sslpop + "')";
try
{
SqlDataReader reader = conexao.Pesquisa(query);
MessageBox.Show("Inserido com sucesso!");
verificaStatus(internalHandle);
}
catch (SqlException e)
{
MessageBox.Show(e.Message);
}
}
public static int getHandleUsuarioAtual(String usuario)
{
int handleAtual = 0;
String query = "SELECT HANDLE FROM US_USUARIO WHERE USUARIO = " + usuario;
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
SqlDataReader reader = conexao.Pesquisa(query);
while (reader.Read())
{
handleAtual = Convert.ToInt32(reader["HANDLE"]);
if (handleAtual == 0)
{
MessageBox.Show("Não foi possível atribuir um handle para o usuário : " + usuario);
}
}
reader.Close();
conexao.Desconectar();
return(handleAtual);
}
private void Button1OnClick(object sender, EventArgs e)
{
if (button1.Text == "Liberar")
{
if (internalHandle == 0 || internalHandle == 1)
{
UServidorEmail servidor = new UServidorEmail();
servidor.Nome = richTextBoxNome.Text;
servidor.Pop = richTextBoxPop.Text;
servidor.Popporta = Convert.ToInt32(richTextBoxPopPorta.Text);
servidor.Smtp = richTextBoxSmtp.Text;
servidor.Smtpporta = Convert.ToInt32(richTextBoxSmtpPorta.Text);
if (checkBoxSslSmtp.Checked == true)
{
servidor.Sslsmtp = "S";
}
else
{
servidor.Sslsmtp = "N";
}
if (checkBoxSslPop.Checked == true)
{
servidor.Sslpop = "S";
}
else
{
servidor.Sslpop = "N";
}
if (VerificaCamposObrigatorios(servidor) == true)
{
tentaInserirRegistro(servidor);
}
}
else
{
UServidorEmail servidor = new UServidorEmail();
servidor.Nome = richTextBoxNome.Text;
servidor.Pop = richTextBoxPop.Text;
servidor.Popporta = Convert.ToInt32(richTextBoxPopPorta.Text);
servidor.Smtp = richTextBoxSmtp.Text;
servidor.Smtpporta = Convert.ToInt32(richTextBoxSmtpPorta.Text);
if (checkBoxSslSmtp.Checked == true)
{
servidor.Sslsmtp = "S";
}
else
{
servidor.Sslsmtp = "N";
}
if (checkBoxSslPop.Checked == true)
{
servidor.Sslpop = "S";
}
else
{
servidor.Sslpop = "N";
}
if (VerificaCamposObrigatorios(servidor) == true)
{
String query = "UPDATE TN_SERVIDOREMAIL SET STATUS =" + StatusServidorEmail.Ativo + "WHERE HANDLE = " + internalHandle;
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
SqlDataReader reader = conexao.Pesquisa(query);
verificaStatus(internalHandle);
}
}
}
else if (button1.Text == "Voltar")
{
DialogResult confirmacaoButton = MessageBox.Show("Deseja Continuar?", "Voltar", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2);
if (confirmacaoButton.ToString().ToUpper() == "YES")
{
String query = "UPDATE TN_SERVIDOREMAIL SET STATUS = 1 WHERE HANDLE = " + internalHandle;
DAL.Connection conexao = new DAL.Connection();
conexao.Conectar();
SqlDataReader reader = conexao.Pesquisa(query);
verificaStatus(internalHandle);
}
}
}
