/// <summary>
/// Guarda un registro a base de datos.
///
/// EN ESTE METODO SE MANDA A LLAMAR UNA PIEZA CLAVE PARA EL ANALISIS Y GENERACIÓn DE TRENDINGS (...)
/// </summary>
/// <param name="entry">Objeto que carga la información de entrada</param>
/// <returns>true si se insertó correctamente | false si ocurrió lo contrario.</returns>
public bool SetEntry(Entry entry)
{
using (MySqlConnection conn = GetConnection())
{
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = "INSERT INTO noticias(Titulo, Extracto, Contenido, Cubeta, NoticiaImportante, Tipo, FechaCreacion, FechaPublicacion, FechaActualizacion) VALUES(@Titulo, @Extracto, @Contenido, @Cubeta, @NoticiaImportante, @Tipo, @FechaCreacion, @FechaPublicacion, @FechaActualizacion)";
var cubeta = TextTools.MagicWords(string.Join(" ", entry.Titulo, entry.Extracto, entry.Contenido));
var dateTimeNow = DateTime.Now;
cmd.Parameters.AddWithValue("@Titulo", entry.Titulo);
cmd.Parameters.AddWithValue("@Extracto", entry.Extracto);
cmd.Parameters.AddWithValue("@Contenido", entry.Contenido);
cmd.Parameters.AddWithValue("@Cubeta", cubeta);
cmd.Parameters.AddWithValue("@NoticiaImportante", entry.NoticiaImportante);
cmd.Parameters.AddWithValue("@Tipo", entry.Tipo);
cmd.Parameters.AddWithValue("@FechaCreacion", dateTimeNow);
cmd.Parameters.AddWithValue("@FechaPublicacion", entry.FechaPublicacion);
cmd.Parameters.AddWithValue("@FechaActualizacion", dateTimeNow);
var recs = cmd.ExecuteNonQuery();
}
return(true);
}
public List <Entry> GetBehavior(string token)
{
List <Entry> list = new List <Entry>();
string keywords = "";
using (var conn = GetConnection())
{
var cmd = new MySqlCommand("SELECT * FROM behavior_bucket WHERE token=@token", conn);
cmd.Parameters.AddWithValue("@token", token);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
keywords = reader["keywords"].ToString();
}
}
}
using (MySqlConnection conn = GetConnection())
{
var querySearch = string.Format(@"SELECT * FROM noticias
WHERE MATCH (Cubeta) AGAINST ('{0}')
AND Activo = true order by FechaPublicacion DESC", keywords);
var cmd = new MySqlCommand(querySearch, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Entry()
{
Id = Convert.ToInt32(reader["Id"]),
IdBusqueda = 0,
Titulo = reader["Titulo"].ToString(),
Extracto = reader["Extracto"].ToString(),
Contenido = reader["Contenido"].ToString(),
NoticiaImportante = Convert.ToBoolean(reader["NoticiaImportante"]),
Tipo = reader["Tipo"].ToString(),
Link = TextTools.ToLinkString(reader["FechaPublicacion"].ToString()) + "/" + TextTools.ToLinkString(reader["Titulo"].ToString()),
FechaCreacion = Convert.ToDateTime(reader["FechaCreacion"]),
FechaPublicacion = Convert.ToDateTime(reader["FechaPublicacion"]),
FechaActualizacion = Convert.ToDateTime(reader["FechaActualizacion"]),
Activo = Convert.ToBoolean(reader["Activo"])
});
}
}
}
return(list);
}
public void CubetizaAll()
{
var textToCubetize = new List <KeyValuePair <string, string> >();
using (var conn = GetConnection())
{
var cmd = new MySqlCommand("SELECT * FROM noticias ORDER BY FechaPublicacion DESC", conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var id = Convert.ToInt32(reader["Id"]).ToString();
var titulo = reader["Titulo"].ToString();
var extracto = reader["Extracto"].ToString();
var contenido = reader["Contenido"].ToString();
var cubeta = TextTools.MagicWords(string.Join(' ', titulo, extracto, contenido));
var idTextPair = new KeyValuePair <string, string>(id, cubeta);
textToCubetize.Add(idTextPair);
}
}
}
foreach (var idTextPair in textToCubetize)
{
using (var conn = GetConnection())
{
var cmdUpdate = conn.CreateCommand() as MySqlCommand;
cmdUpdate.CommandText = "UPDATE noticias SET Cubeta=@Cubeta WHERE ID=@Id";
cmdUpdate.Parameters.AddWithValue("@Cubeta", idTextPair.Value);
cmdUpdate.Parameters.AddWithValue("@Id", idTextPair.Key);
var recs = cmdUpdate.ExecuteNonQuery();
}
}
}
public bool SetBehavior(View view)
{
using (MySqlConnection conn = GetConnection())
{
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = "INSERT INTO behavior(id_noticia, token, fecha_visita) VALUES(@id_noticia, @token, @fecha_visita)";
var dateTimeNow = DateTime.Now;
cmd.Parameters.AddWithValue("@id_noticia", view.IdNoticia);
cmd.Parameters.AddWithValue("@token", view.Token);
cmd.Parameters.AddWithValue("@fecha_visita", dateTimeNow);
var recs = cmd.ExecuteNonQuery();
}
// Obtener keywords para obtener notas similares
// Este paso puede ser omitido si se tienes keywords desde front
string keywords = "";
string classification = "";
using (var conn = GetConnection())
{
var cmd = new MySqlCommand(string.Format("SELECT * FROM noticias WHERE Id = {0}",
view.IdNoticia), conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var titulo = reader["Titulo"].ToString();
var extracto = reader["Extracto"].ToString();
keywords = TextTools.MostWords(string.Join(" ", titulo, extracto));
classification = string.Join(",", (new string[] { reader["Programa"].ToString(), reader["Seccion"].ToString(), reader["Categoria"].ToString() }).Where(s => !string.IsNullOrEmpty(s)));
}
}
}
var insert = true;
using (var conn = GetConnection())
{
var cmd = new MySqlCommand(string.Format("SELECT * FROM behavior_bucket WHERE token = '{0}'",
view.Token), conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
insert = false;
var bucket = reader["keywords"].ToString();
var bucketClass = reader["classification"].ToString();
keywords = string.Join(" ", TextTools.MostWords(keywords), bucket);
classification = string.Join(",", classification.Trim(), bucketClass.Trim());
// Keywords
Dictionary <string, string> k = new Dictionary <string, string>();
foreach (var anyword in keywords.Split(" "))
{
if (!string.IsNullOrEmpty(anyword))
{
if (!k.ContainsKey(anyword))
{
k.Add(anyword, "");
}
}
}
var kk = k.ToList();
keywords = "";
int c = 0;
foreach (var kw in kk)
{
if (c == 9)
{
break;
}
keywords += kw.Key + " ";
c++;
}
keywords = keywords.TrimEnd(' ');
// Classification
Dictionary <string, string> clss = new Dictionary <string, string>();
foreach (var anyclass in classification.Split(","))
{
if (!string.IsNullOrEmpty(anyclass))
{
if (!clss.ContainsKey(anyclass))
{
clss.Add(anyclass, "");
}
}
}
var cc = clss.ToList();
classification = "";
c = 0;
foreach (var cl in cc)
{
if (c == 6)
{
break;
}
classification += cl.Key + ",";
c++;
}
classification = classification.TrimEnd(',');
}
}
}
if (insert)
{
using (MySqlConnection conn = GetConnection())
{
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = "INSERT INTO behavior_bucket(token, keywords, classification) values(@token, @keywords, @classification)";
cmd.Parameters.AddWithValue("@token", view.Token);
cmd.Parameters.AddWithValue("@keywords", keywords);
cmd.Parameters.AddWithValue("@classification", classification);
cmd.ExecuteNonQuery();
}
}
else
{
using (var conn = GetConnection())
{
var cmdUpdate = conn.CreateCommand() as MySqlCommand;
cmdUpdate.CommandText = "UPDATE behavior_bucket SET keywords =@keywords,classification =@classification WHERE token =@token";
cmdUpdate.Parameters.AddWithValue("@token", view.Token);
cmdUpdate.Parameters.AddWithValue("@keywords", keywords);
cmdUpdate.Parameters.AddWithValue("@classification", classification);
cmdUpdate.ExecuteNonQuery();
}
}
return(true);
}
public List <Entry> Search(SearchPayload payload)
{
List <Entry> list = new List <Entry>();
var searchText = TextTools.MagicWords(payload.Phrase);
var dateTimeNow = DateTime.Now;
var hasRows = false;
int idBusqueda = 0;
int idInsert = -1;
if (string.IsNullOrEmpty(searchText))
{
return(list);
}
using (MySqlConnection conn = GetConnection())
{
MySqlCommand cmd = new MySqlCommand(string.Format("SELECT * FROM historico_busquedas WHERE palabras = '{0}'", searchText), conn);
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
hasRows = true;
while (reader.Read())
{
idBusqueda = Convert.ToInt32(reader["id"]);
}
}
}
}
using (MySqlConnection conn = GetConnection())
{
if (hasRows)
{
var cmdInsert = conn.CreateCommand() as MySqlCommand;
cmdInsert.CommandText = "INSERT INTO tendencias(id_busqueda, fecha_busqueda) values(@id_busqueda, @fecha_busqueda)";
dateTimeNow = dateTimeNow.AddSeconds((double)dateTimeNow.Second * -1);
cmdInsert.Parameters.AddWithValue("@id_busqueda", idBusqueda);
cmdInsert.Parameters.AddWithValue("@fecha_busqueda", dateTimeNow);
var recs = cmdInsert.ExecuteNonQuery();
}
else
{
var cmdInsert = conn.CreateCommand() as MySqlCommand;
cmdInsert.CommandText = "INSERT INTO historico_busquedas(palabras, fecha_alta) values(@palabras, @fecha_alta);select last_insert_id();";
cmdInsert.Parameters.AddWithValue("@palabras", searchText);
cmdInsert.Parameters.AddWithValue("@fecha_alta", dateTimeNow);
idInsert = Convert.ToInt32(cmdInsert.ExecuteScalar());
}
}
if (idInsert > 0)
{
using (MySqlConnection conn = GetConnection())
{
var cmdInsert = conn.CreateCommand() as MySqlCommand;
cmdInsert.CommandText = "INSERT INTO tendencias(id_busqueda, fecha_busqueda) values(@id_busqueda, @fecha_busqueda)";
dateTimeNow = dateTimeNow.AddSeconds((double)dateTimeNow.Second * -1);
cmdInsert.Parameters.AddWithValue("@id_busqueda", idInsert);
cmdInsert.Parameters.AddWithValue("@fecha_busqueda", dateTimeNow);
var recs = cmdInsert.ExecuteNonQuery();
}
}
using (MySqlConnection conn = GetConnection())
{
var querySearch = string.Format(@"SELECT * FROM noticias
WHERE MATCH (Cubeta) AGAINST ('{0}')
AND Activo = true order by FechaPublicacion DESC", searchText);
var cmd = new MySqlCommand(querySearch, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Entry()
{
Id = Convert.ToInt32(reader["Id"]),
IdBusqueda = idInsert > 0 ? idInsert : idBusqueda,
Titulo = reader["Titulo"].ToString(),
Extracto = reader["Extracto"].ToString(),
Contenido = reader["Contenido"].ToString(),
NoticiaImportante = Convert.ToBoolean(reader["NoticiaImportante"]),
Tipo = reader["Tipo"].ToString(),
Link = TextTools.ToLinkString(reader["FechaPublicacion"].ToString()) + "/" + TextTools.ToLinkString(reader["Titulo"].ToString()),
FechaCreacion = Convert.ToDateTime(reader["FechaCreacion"]),
FechaPublicacion = Convert.ToDateTime(reader["FechaPublicacion"]),
FechaActualizacion = Convert.ToDateTime(reader["FechaActualizacion"]),
Activo = Convert.ToBoolean(reader["Activo"])
});
}
}
}
return(list);
}