static void queryToReturnResults(string input)
{
string query = "";
int lines = 1;
string[] tokens = OpenNLP.Tokenizer(input, path);
foreach (var results in tokens)
{
if (lines == 1)
{
query = query + "word = '" + results + "' ";
lines++;
}
else
{
query = query + "or word = '" + results + "' ";
}
//Console.WriteLine(query);
}
int som = 1;
using (SqlConnection connection = new SqlConnection("Data Source=" + datasource + ";Initial Catalog=snipetts;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT dc.id as docid, d_title, d_url, word FROM words as wd inner join relation as rl ON rl.wordid = wd.id inner join docs as dc ON rl.docid = dc.id where " + query, connection))
{
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
Console.WriteLine(som++ + "{");
Console.WriteLine("docid: " + reader.GetInt32(0));
Console.WriteLine("title: " + reader.GetString(reader.GetOrdinal("d_title")));
Console.WriteLine("url: " + reader.GetString(reader.GetOrdinal("d_url")));
Console.WriteLine("word: " + reader.GetString(reader.GetOrdinal("word")));
Console.WriteLine("}");
Console.WriteLine("");
}
}
else
{
Console.WriteLine("Não existe essa palavra!");
}
}
connection.Close();
}
//Console.ReadLine();
}
public string queryToReturnResults(string input)
{
string result = "";
string query = "";
int lines = 1;
string[] tokens = OpenNLP.Tokenizer(input, path);
foreach (var results in tokens)
{
if (lines == 1)
{
query = query + "word = '" + results + "' ";
lines++;
}
else
{
query = query + "or word = '" + results + "' ";
}
}
int som = 1;
using (SqlConnection connection = new SqlConnection("Data Source=" + datasource + ";Initial Catalog=snipetts;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT dc.id as docid, d_title, d_url, word FROM words as wd inner join relation as rl ON rl.wordid = wd.id inner join docs as dc ON rl.docid = dc.id where " + query, connection))
{
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
result = result + "{'documents':[";
// Read advances to the next row.
while (reader.Read())
{
result = result + "{" + "'docid': '" + reader.GetInt32(0) + "', 'title':'" + reader.GetString(reader.GetOrdinal("d_title")) + "', 'url':'" + reader.GetString(reader.GetOrdinal("d_url")) +
", 'word': " + reader.GetString(reader.GetOrdinal("word")) + "'},";
}
result = result + "]}";
}
else
{
result = "null";
}
}
connection.Close();
}
return(result);
}
static void insertResults(string query)
{
int idDoc = lastIdDoc(); // last id docs sql
int idWord = lastIdWord();
int wordPosition = 0;
HashSet <string> hashSet = setStopWords();
HashSet <string> addedWords = setAddedWords(); // SELECT words FROM words
string api = "https://api.cognitive.microsoft.com/bing/v5.0/search?";
string safesearch = "Moderate";
string lang = "en-us";
int offset = 0;
int count = 10;
string word = query;
var client = new RestClient(api +
"safesearch=" + safesearch +
"&mkt=" + lang +
"&offset=" + offset +
"&count=" + count +
"&q=" + word);
var request = new RestRequest(Method.GET);
request.AddHeader("ocp-apim-subscription-key", "eb0b8f77941d45c5bead950366586f3d");
request.AddHeader("host", "api.cognitive.microsoft.com");
IRestResponse response = client.Execute(request);
JObject o = JObject.Parse(response.Content);
SqlConnection connection = new SqlConnection("Data Source=" + datasource + ";Initial Catalog=snipetts;Integrated Security=SSPI");
SqlCommand cmd;
cmd = new SqlCommand("INSERT INTO docs (id,d_title,d_content,d_url) VALUES (@id,@title,@content,@url)", connection);
connection.Open();
cmd.Parameters.Add("@id", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@title", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@content", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@url", System.Data.SqlDbType.NVarChar);
foreach (var results in o["webPages"]["value"])
{
//Console.WriteLine((string)results["name"]);
string title = (string)results["name"];
string url = (string)results["url"];
string content = (string)results["snippet"];
cmd.Parameters["@id"].Value = idDoc;
cmd.Parameters["@title"].Value = title;
cmd.Parameters["@content"].Value = content;
cmd.Parameters["@url"].Value = url;
cmd.ExecuteNonQuery();
string[] tokens = OpenNLP.Tokenizer(content, path);
foreach (var result in tokens)
{
if (OpenNLP.checkContainsALetter(result))
{
if (hashSet.Contains(result.ToLower()))
{
//Console.WriteLine("STOP WORD "+ result);
}
else
{
if (addedWords.Contains(result.ToLower()))
{
if (confirmIfExist(result, idDoc))
{
SqlCommand updateWord = new SqlCommand("UPDATE words SET tf=tf+1 WHERE word='" + result + "'", connection);
updateWord.ExecuteNonQuery();
//Console.WriteLine("REPETIDA " + result);
SqlCommand updateRelation = new SqlCommand("UPDATE relation SET tf=tf+1 WHERE docid='" + idDoc + "' AND wordid=(SELECT id FROM words WHERE word='" + result + "')", connection);
updateRelation.ExecuteNonQuery();
SqlCommand insertOffset = new SqlCommand("INSERT INTO offsetword (docid,offset,wordid) VALUES ('" + idDoc + "','" + wordPosition + "',(SELECT id FROM words WHERE word='" + result + "'))", connection);
insertOffset.ExecuteNonQuery();
}
else
{
SqlCommand update = new SqlCommand("UPDATE words SET tf=tf+1, df=df+1 WHERE word='" + result + "'", connection);
update.ExecuteNonQuery();
//Console.WriteLine("REPETIDA " + result);
SqlCommand insertOccurs = new SqlCommand("INSERT INTO relation (docid,wordid,tf) VALUES (" + idDoc + ",(SELECT id FROM words WHERE word='" + result + "'),1)", connection);
insertOccurs.ExecuteNonQuery();
SqlCommand insertOffset = new SqlCommand("INSERT INTO offsetword (docid,offset,wordid) VALUES ('" + idDoc + "','" + wordPosition + "',(SELECT id FROM words WHERE word='" + result + "'))", connection);
insertOffset.ExecuteNonQuery();
}
}
else
{
SqlCommand insert = new SqlCommand("INSERT INTO words (id,df,tf,word) VALUES (" + idWord + ",1,1,'" + result + "')", connection);
insert.ExecuteNonQuery();
addedWords.Add(result.ToLower());
SqlCommand insertOccurs = new SqlCommand("INSERT INTO relation (docid,wordid,tf) VALUES (" + idDoc + "," + idWord + ",1)", connection);
insertOccurs.ExecuteNonQuery();
SqlCommand insertOffset = new SqlCommand("INSERT INTO offsetword (docid,offset,wordid) VALUES ('" + idDoc + "','" + wordPosition + "','" + idWord + "')", connection);
insertOffset.ExecuteNonQuery();
idWord = idWord + 1;
}
}
wordPosition = wordPosition + 1;
}
}
wordPosition = 0;
idDoc = idDoc + 1;
}
Console.WriteLine("Dados inseridos!");
connection.Close();
}
static void queryToReturnResultsWithSequence(string input)
{
int lines = 1;
int som = 1;
int aux = 0; int aux1 = 1;
string query = "";
HashSet <string> hashSet = setStopWords();
string[] tokens = OpenNLP.Tokenizer(input, path);
foreach (var results in tokens)
{
if (OpenNLP.checkContainsALetter(results))
{
if (hashSet.Contains(results.ToLower()))
{
if (aux1 != 1)
{
aux++;
}
}
else
{
if (lines == 1)
{
query = "SELECT sl1.docid, d_title, d_url FROM (SELECT offs.offset, offs.docid FROM words as wd " +
"inner join relation as rl ON rl.wordid = wd.id " +
"inner join docs as dc ON rl.docid = dc.id " +
"inner join offsetword as offs ON offs.wordid = wd.id AND offs.docid = dc.id " +
"where word = '" + results + "' ) as sl" + aux1 +
" inner join docs as dc ON sl1.docid = dc.id ";
aux1++;
lines++;
}
else
{
query = query + " inner join (SELECT offs.offset, offs.docid FROM words as wd " +
"inner join relation as rl ON rl.wordid = wd.id " +
"inner join docs as dc ON rl.docid = dc.id " +
"inner join offsetword as offs ON offs.wordid = wd.id AND offs.docid = dc.id " +
"where word = '" + results + "') as sl" + aux1 +
" ON sl1.docid = sl" + aux1 + ".docid AND sl" + aux1 + ".offset = sl1.offset +" + ((aux1 - 1) + aux);
aux1++;
}
}
}
}
using (SqlConnection connection = new SqlConnection("Data Source=" + datasource + ";Initial Catalog=snipetts;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
Console.WriteLine(som++ + "{");
Console.WriteLine("docid: " + reader.GetInt32(0));
Console.WriteLine("title: " + reader.GetString(reader.GetOrdinal("d_title")));
Console.WriteLine("url: " + reader.GetString(reader.GetOrdinal("d_url")));
Console.WriteLine("}");
Console.WriteLine("");
}
}
else
{
Console.WriteLine("Não existe!");
}
}
connection.Close();
}
//Console.ReadLine();
}
public static string FindNames(OpenNLP.Tools.Parser.Parse sentenceParse)
{
if (mNameFinder == null)
{
mNameFinder = new OpenNLP.Tools.NameFind.EnglishNameFinder(mModelPath + "namefind\\");
}
string[] models = new string[] { "date", "location", "money", "organization", "percentage", "person", "time" };
return mNameFinder.GetNames(models, sentenceParse);
}
private string FindNames(OpenNLP.Tools.Parser.Parse sentenceParse)
{
if (_nameFinder == null)
{
_nameFinder = new OpenNLP.Tools.NameFind.EnglishNameFinder(_modelPath + "namefind\\");
}
var models = new[] { "date", "location", "money", "organization", "percentage", "person", "time" };
return _nameFinder.GetNames(models, sentenceParse);
}
