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(ConexaoGuardian.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(ConexaoGuardian.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(ConexaoGuardian.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, ConexaoGuardian.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, ConexaoGuardian.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, ConexaoGuardian.Conexao());
}
public List <NfNaoIntegrada> BuscarNotas()
{
List <NfNaoIntegrada> notas = new List <NfNaoIntegrada>();
string query =
"SELECT dEmi, serie, nNF, CNPJ_emit, xNome_emit, natOp, vNF as 'valor' FROM NFe " +
"INNER JOIN CABECALHO_NFE WITH(NOLOCK) ON CABECALHO_NFE.Id = NFe.Id " +
"WHERE NFe.operacao = 'E' " +
"AND SUBSTRING(NFe.Id, 4, 50) NOT IN " +
"(SELECT ChaveNFe FROM ConsultasSefaz WITH(NOLOCK) " +
"WHERE cStatus = '101') " +
"AND(NFe.integracao IS NULL OR NFe.integracao = 'GUARDIAN') " +
"ORDER BY dEmi";
using (SqlConnection connection = new SqlConnection(ConexaoGuardian.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandTimeout = 240;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
NfNaoIntegrada nota = new NfNaoIntegrada
{
Emissao = reader["dEmi"].ToString().Trim(),
Serie = reader["serie"].ToString().Trim(),
Numero = reader["nNF"].ToString().Trim(),
Cnpj = reader["CNPJ_emit"].ToString().Trim(),
Nome = reader["xNome_emit"].ToString().Trim(),
Natureza = reader["natOp"].ToString().Trim(),
Valor = reader["valor"].ToString().Trim(),
};
notas.Add(nota);
}
}
}
}
return(notas);
}
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, ConexaoGuardian.Conexao());
}
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(ConexaoGuardian.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)
{
EnvioNFeController.HoraRotina = Convert.ToInt32(reader["HORA"].ToString().TrimStart().TrimEnd());
}
}
}
}
}
}
public List <string> BuscarEmails()
{
List <string> emails = new List <string>();
string query =
"SELECT EMAIL FROM " + Tabelas_Guardian.Emails + "";
using (SqlConnection conexao = new SqlConnection(ConexaoGuardian.Conexao()))
{
using (SqlCommand command = new SqlCommand(query, conexao))
{
conexao.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
emails.Add(reader["EMAIL"].ToString().Trim());
}
}
}
}
return(emails);
}