public void SaveTweets(PostTwitter postTwitter)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
if (postTwitter != null)
{
crawlerDB.PostTwitters.Add(postTwitter);
crawlerDB.SaveChangesAsync();
}
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public bool CheckIfExists(string categoria)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
IEnumerable<Categoria> cat = crawlerDB.Categorias.AsNoTracking().Where(p => p.Nome == categoria);
if (cat.Count() <= 0)
return false;
return true;
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public IEnumerable<PostTwitter> GetAll()
{
contextDB = new ContextDB();
try
{
using (var crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
var tweets =
crawlerDB.PostTwitters.Include(p => p.Categoria)
.Include(p => p.Estado)
.AsNoTracking()
.OrderBy(e => e.Estado.Nome)
.ToList();
return tweets;
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public IEnumerable<Categoria> GetAllCategories()
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
IEnumerable<Categoria> categories = crawlerDB.Categorias.AsNoTracking().ToList();
return categories;
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public IEnumerable<string> GetStatesAcronymsByCategory(string categoria)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
return
crawlerDB.PostTwitters.Include(a => a.Categoria)
.Where(c => c.Categoria.Nome == categoria)
.Select(p => p.Estado.Sigla)
.Distinct()
.ToList();
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public string GetStatebyId(int id)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
Task<Estado> state = crawlerDB.Estados.AsNoTracking().FirstOrDefaultAsync(e => e.EstadoId == id);
return state.Result.Nome;
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public IEnumerable<Estado> GetAllStates()
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
IEnumerable<Estado> states = crawlerDB.Estados.AsNoTracking().OrderBy(x => x.Nome).ToList();
return states;
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public List<int> GetTotalByDate(string categoria, DateTime dataInicio, DateTime dataFim, string estado)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
return
crawlerDB.PostTwitters.Include(c => c.Categoria)
.Include(e => e.Estado)
.AsNoTracking()
.AsEnumerable()
.Where(
p =>
p.Categoria.Nome == categoria && p.Estado.Nome == estado && p.Data >= dataInicio &&
p.Data <= dataFim)
.GroupBy(p => new {p.Data, p.Estado.Sigla, p.Categoria.Nome})
.OrderBy(p => p.Key.Data)
.Distinct()
.Select(p => p.Count())
.ToList();
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public int GetId(string categoria)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
var cat = crawlerDB.Categorias.AsNoTracking().FirstOrDefaultAsync(c => c.Nome == categoria);
return cat.Result.CategoriaId;
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public void SaveCategory(Categoria category)
{
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
if (category != null)
{
crawlerDB.Categorias.Add(category);
crawlerDB.SaveChangesAsync();
}
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public int GetTotalBetweenDates(string categoria, DateTime dataInicio, DateTime dataFim)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
return
crawlerDB.PostTwitters.Include(a => a.Categoria)
.AsNoTracking()
.Where(p => p.Data >= dataInicio && p.Data <= dataFim)
.Count(c => c.Categoria.Nome == categoria);
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public int GetTotal(string categoria)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
return
crawlerDB.PostTwitters.Include(a => a.Categoria)
.AsNoTracking()
.Count(c => c.Categoria.Nome == categoria);
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public IEnumerable<int> GetTotalByCategoryBetweenDates(string categoria, DateTime dataInicio, DateTime dataFim)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
return
crawlerDB.PostTwitters.Include(a => a.Categoria)
.AsNoTracking()
.Where(e => e.Categoria.Nome.ToString() == categoria.ToString() && e.Data >= dataInicio && e.Data <= dataFim)
.GroupBy(e => e.EstadoId)
.Select(e => e.Count())
.ToList();
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public IEnumerable<string> GetCategoryByCode(int codigo)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
return
crawlerDB.PostTwitters.Include(a => a.Categoria)
.AsNoTracking()
.Where(e => e.EstadoId == codigo)
.Select(p => p.Categoria.Nome)
.Distinct()
.ToList();
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}
public IEnumerable<int> GetTotalByCode(int codigo)
{
contextDB = new ContextDB();
try
{
using (CrawlerDB crawlerDB = new CrawlerDB())
{
contextDB.ConfigureContext(crawlerDB);
return
crawlerDB.PostTwitters.Include(a => a.Categoria)
.AsNoTracking()
.Where(e => e.EstadoId == codigo)
.GroupBy(c => c.Categoria.Nome)
.Select(p => p.Count())
.ToList();
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
contextDB = null;
}
}