public DateTime BuscarUltimaExecucao()
{
DateTime dtUltimaExecucao = new DateTime();
string query =
"SELECT TOP 1 DATA_REGISTRO " +
"FROM " + Tabelas_Guardian.RegistroRotina + " " +
"WHERE NOME = '" + Nome + "' " +
"ORDER BY DATA_REGISTRO DESC ";
using (SqlConnection connection = new SqlConnection(ConexaoGestor.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandTimeout = 500;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
dtUltimaExecucao = DateTime.ParseExact(reader["DATA_REGISTRO"].ToString() + "235959", "yyyyMMddHHmmss", new CultureInfo("pt-BR", false));
}
else
{
dtUltimaExecucao = DateTime.Now;
}
}
}
}
return(dtUltimaExecucao);
}
public static bool CarregarConfiguracoes()
{
string query = "SELECT REGISTRO_TXT, LOG_OCORRENCIA, LOG_EMAIL, LOG_ROTINA, LOG_AUDITORIA " +
"FROM " + Tabelas_Guardian.ConfigLog + "";
using (SqlConnection conexao = new SqlConnection(ConexaoGestor.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, conexao))
{
conexao.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["REGISTRO_TXT"].ToString().TrimStart().TrimEnd() == "S")
{
LogTxt = true;
}
if (reader["LOG_OCORRENCIA"].ToString().TrimStart().TrimEnd() == "S")
{
LogOcorrencia = true;
}
if (reader["LOG_EMAIL"].ToString().TrimStart().TrimEnd() == "S")
{
LogEmail = true;
}
if (reader["LOG_ROTINA"].ToString().TrimStart().TrimEnd() == "S")
{
LogRotina = true;
}
if (reader["LOG_AUDITORIA"].ToString().TrimStart().TrimEnd() == "S")
{
LogAuditoria = true;
}
}
}
}
}
Status = true;
return(Status);
}
public void RegistrarExecucao()
{
string query =
"INSERT INTO " + Tabelas_Guardian.RegistroRotina + " " +
"(NOME, DATA_REGISTRO, DESCRICAO) " +
"VALUES('" + Nome + "', '" + DateTime.Now.ToString("yyyyMMdd") + "', '" + Descricao + "')";
using (SqlConnection connection = new SqlConnection(ConexaoGestor.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
connection.Open();
command.ExecuteNonQuery();
}
}
}
public static void Log_Auditoria(string nomeRotina, Acao acao, double valor)
{
LogAuditoria logAuditoria = new LogAuditoria
{
NomeRotina = nomeRotina,
Data = DateTime.Now.ToString("yyyyMMdd"),
Hora = DateTime.Now.ToString("HH:mm:ss.fff"),
Acao = acao.ToString(),
Valor = valor,
Cliente = Service_Config.NomeCliente
};
LogDAO logDAO = new LogDAO();
logDAO.RegistrarLogAuditoria(logAuditoria, ConexaoGestor.Conexao());
}
public static void Log_Email(string email, string rotina, Status status, string infoAdicionais)
{
LogEmail logEmail = new LogEmail
{
Data = DateTime.Now.ToString("yyyyMMdd"),
Hora = DateTime.Now.ToString("HH:mm:ss.fff"),
Email = email,
Rotina = rotina,
Status = status.ToString(),
InfoAdicionais = infoAdicionais,
Aplicacao = Service_Config.NomeServico
};
LogDAO logDAO = new LogDAO();
logDAO.RegistrarLogEmail(logEmail, ConexaoGestor.Conexao());
}
public static void Log_Rotina(string siglaRotina, string nomeRotina, Tipo tipo)
{
LogRotina logRotina = new LogRotina
{
IdLog = DateTime.Now.ToString("yyyyMMddHHmmss") + siglaRotina,
IdCiclo = Main.IdCiclo,
Rotina = nomeRotina,
Tipo = tipo.ToString(),
Data = DateTime.Now.ToString("yyyyMMdd"),
Hora = DateTime.Now.ToString("HH:mm:ss.fff"),
Aplicacao = Service_Config.NomeServico,
Cliente = Service_Config.NomeCliente
};
LogDAO logDAO = new LogDAO();
logDAO.RegistrarLogRotina(logRotina, ConexaoGestor.Conexao());
}
public static void Log_Ocorrencia(string nomeRotina, string descricao, string descricaoTecnica, string informacoesAdicionais)
{
LogOcorrencia logOcorrencia = new LogOcorrencia
{
NomeRotina = nomeRotina,
Data = DateTime.Now.ToString("yyyyMMdd"),
Hora = DateTime.Now.ToString("HH:mm:ss.fff"),
Descricao = descricao.Replace("'", "|"),
DescricaoTecnica = descricaoTecnica.Replace("'", "|"),
InformacaoAdicional = informacoesAdicionais.Replace("'", "|")
};
informacoesAdicionais.Replace("'", "|");
logOcorrencia.Aplicacao = Service_Config.NomeServico;
logOcorrencia.Cliente = Service_Config.NomeCliente;
LogDAO logDAO = new LogDAO();
logDAO.RegistrarLogOcorrencia(logOcorrencia, ConexaoGestor.Conexao());
}
public List <Contato_Prospect> BuscarProspectsOntem()
{
List <Contato_Prospect> prospects = new List <Contato_Prospect>();
string query =
"SELECT PROSPECTS.RAZAO, P_NOME, P_OBSERVACAO, P_OBS_PROXIMO, P_DATA_PROXIMO, P_HORA_PROXIMO " +
"FROM CONTATO_PROSPECT " +
"INNER JOIN PROSPECTS ON CONTATO_PROSPECT.P_COD_PROSPECT = PROSPECTS.COD " +
"WHERE P_DATA_ATUAL = '" + DateTime.Now.AddDays(-1).ToString("yyyyMMdd") + "' " +
"ORDER BY PROSPECTS.RAZAO";
using (SqlConnection connection = new SqlConnection(ConexaoGestor.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandTimeout = 240;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Contato_Prospect prospect = new Contato_Prospect
{
Razao = reader["RAZAO"].ToString().Trim(),
NomeContato = reader["P_NOME"].ToString().Trim(),
Observacao = reader["P_OBSERVACAO"].ToString().Trim(),
ObservacaoProximo = reader["P_OBS_PROXIMO"].ToString().Trim(),
DataProximo = reader["P_DATA_PROXIMO"].ToString().Trim(),
HoraProximo = reader["P_HORA_PROXIMO"].ToString().Trim(),
};
prospects.Add(prospect);
}
}
}
}
return(prospects);
}
public List <Contato_Cliente> BuscarClientesOntem()
{
List <Contato_Cliente> clientes = new List <Contato_Cliente>();
string query =
"SELECT CLIENTES.RAZAO, CL_NOME, CL_OBSERVACAO, CL_OBS_PROXIMO, CL_DATA_PROXIMO, CL_HORA_PROXIMO " +
"FROM CONTATO_CLIENTE " +
"INNER JOIN CLIENTES ON CONTATO_CLIENTE.CL_COD = CONTATO_CLIENTE.CL_COD " +
"WHERE CL_DATA_ATUAL = '" + DateTime.Now.AddDays(-1).ToString("yyyyMMdd") + "' " +
"ORDER BY CLIENTES.RAZAO";
using (SqlConnection connection = new SqlConnection(ConexaoGestor.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandTimeout = 240;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Contato_Cliente cliente = new Contato_Cliente
{
Razao = reader["RAZAO"].ToString().Trim(),
NomeContato = reader["CL_NOME"].ToString().Trim(),
Observacao = reader["CL_OBSERVACAO"].ToString().Trim(),
ObservacaoProximo = reader["CL_OBS_PROXIMO"].ToString().Trim(),
DataProximo = reader["CL_DATA_PROXIMO"].ToString().Trim(),
HoraProximo = reader["CL_HORA_PROXIMO"].ToString().Trim(),
};
clientes.Add(cliente);
}
}
}
}
return(clientes);
}
public List <Contato_Lead> BuscarLeadsOntem()
{
List <Contato_Lead> leads = new List <Contato_Lead>();
string query =
"SELECT LEADS.L_RAZAO, C_NOME, C_OBSERVACAO, C_OBS_PROXIMO, C_DATA_PROXIMO, C_HORA_PROXIMO " +
"FROM CONTATO_LEAD " +
"INNER JOIN LEADS ON CONTATO_LEAD.C_COD_LEAD = LEADS.L_COD " +
"WHERE C_DATA_ATUAL = '" + DateTime.Now.AddDays(-1).ToString("yyyyMMdd") + "'" +
"ORDER BY LEADS.L_RAZAO";
using (SqlConnection connection = new SqlConnection(ConexaoGestor.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandTimeout = 240;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Contato_Lead lead = new Contato_Lead
{
Razao = reader["L_RAZAO"].ToString().Trim(),
NomeContato = reader["C_NOME"].ToString().Trim(),
Observacao = reader["C_OBSERVACAO"].ToString().Trim(),
ObservacaoProximo = reader["C_OBS_PROXIMO"].ToString().Trim(),
DataProximo = reader["C_DATA_PROXIMO"].ToString().Trim(),
HoraProximo = reader["C_HORA_PROXIMO"].ToString().Trim(),
};
leads.Add(lead);
}
}
}
}
return(leads);
}
public void BuscarConfigs(bool Rotina)
{
string query =
"SELECT CADASTRO, DELAY_CICLO, EMAIL_VALIDACAO, TIPO_UPLOAD, VALOR_UPLOAD, DELAY_UPLOAD, HORA_INICIO, HORA_FIM, TOP_REGISTROS, HORA " +
"FROM " + Tabelas_Guardian.ConfigUpload + " " +
"WHERE ROTINA = 'MAILING'";
using (SqlConnection conexao = new SqlConnection(ConexaoGestor.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, conexao))
{
conexao.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["CADASTRO"].ToString().TrimStart().TrimEnd() == "S")
{
Service_Config.CadastroHabilitado = true;
}
Service_Config.DelayCiclo = Convert.ToDouble(reader["DELAY_CICLO"].ToString().TrimStart().TrimEnd());
Service_Config.EmailValidacao = reader["EMAIL_VALIDACAO"].ToString().TrimStart().TrimEnd();
Service_Config.TipoUpload = reader["TIPO_UPLOAD"].ToString().TrimStart().TrimEnd();
Service_Config.ValorUpload = reader["VALOR_UPLOAD"].ToString().TrimStart().TrimEnd();
//Service_Config.DelayUpload = Convert.ToDouble(reader["DELAY_UPLOAD"].ToString().TrimStart().TrimEnd());
//Service_Config.UploadHoraInicio = Convert.ToDateTime(reader["HORA_INICIO"].ToString().TrimStart().TrimEnd());
//Service_Config.UploadHoraFim = Convert.ToDateTime(reader["HORA_FIM"].ToString().TrimStart().TrimEnd());
Service_Config.TopRegistros = reader["TOP_REGISTROS"].ToString().TrimStart().TrimEnd();
if (Rotina)
{
EnvioComercialController.HoraRotina = Convert.ToInt32(reader["HORA"].ToString().TrimStart().TrimEnd());
}
}
}
}
}
}
public static GuardianEmail CarregarConfiguracoes()
{
GuardianEmail email = new GuardianEmail();
string query =
" SELECT TOP 1 HOST, PORTA, SSL, LOGIN, SENHA, EMAIL, EMAIL_VALIDACAO " +
" FROM " + Tabelas_Portal.ConfigEmail + " ";
using (SqlConnection connection = new SqlConnection(ConexaoGestor.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
email.Servidor = reader["HOST"].ToString().Trim();
email.Porta = int.Parse(reader["PORTA"].ToString().Trim());
if (reader["SSL"].ToString().Trim().Equals("S"))
{
email.Ssl = true;
}
else
{
email.Ssl = false;
}
email.Login = reader["LOGIN"].ToString().Trim();
email.Senha = Criptografia.Descriptografar(reader["SENHA"].ToString().Trim());
email.EmailRemetente = reader["EMAIL"].ToString().Trim();
}
}
}
}
return(email);
}
