/// <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); }