/// <summary>
/// Search by country name - Country
/// </summary>
/// <param name="countryName"></param>
public IActionResult Get(string countryName)
{
Dictionary<string, Countries.CountryDiseases> countrydiseases = new Dictionary<string, Countries.CountryDiseases>();
string query = "";
try
{
database db = new database(database.maindb);
MySqlDataReader rd;
rd = db.ExecuteReader("SELECT code,sum(deaths) as nrDeaths FROM `diseasestatistics` where lower(country)=lower('" + countryName + "') group by code order by sum(deaths) desc limit 10");
while (rd.Read())
{
countrydiseases.Add(rd.GetString("code"), new Countries.CountryDiseases(rd.GetInt32("nrDeaths"), "", "",""));
}
SparqlRemoteEndpoint endpoint = new SparqlRemoteEndpoint(new Uri("http://dbpedia.org/sparql"), "http://dbpedia.org");
foreach (KeyValuePair<string, Countries.CountryDiseases> disease in countrydiseases)
{
query = @"SELECT * WHERE {
?url <http://dbpedia.org/ontology/icd10> ?ID.
?url rdfs:label ?name.
?url <http://dbpedia.org/ontology/abstract> ?description.
filter regex(str(lcase(?ID)), concat(lcase('" + disease.Key[0] + "'), '[" + disease.Key[1] + "][" + disease.Key[2] + "][.]?[0-9]?') )" +
"filter(langMatches(lang(?name), 'EN'))" +
"filter(langMatches(lang(?description), 'EN'))" +
"} limit 1";
SparqlResultSet results = endpoint.QueryWithResultSet(query);
if (results.Count > 0)
{
disease.Value.Description = results[0]["description"].ToString().Remove(results[0]["description"].ToString().Length - 3);
if (disease.Value.Description.Length > 300)
disease.Value.Description = disease.Value.Description.Remove(300) + " ...";
disease.Value.Disease = results[0]["name"].ToString().Remove(results[0]["name"].ToString().Length - 3);
disease.Value.Url = disease.Value.Disease.Replace(" ", "_");
}
else
{
query = @"SELECT * WHERE {
?url <http://dbpedia.org/ontology/icd10> ?ID.
?url rdfs:label ?name.
?url <http://dbpedia.org/ontology/abstract> ?description.
filter regex(str(lcase(?ID)), concat(lcase('" + disease.Key[0] + "'), '[" + disease.Key[1] + "][0-9][.]?[0-9]?') )" +
"filter(langMatches(lang(?name), 'EN'))" +
"filter(langMatches(lang(?description), 'EN'))" +
"} limit 1";
results = endpoint.QueryWithResultSet(query);
if (results.Count > 0)
{
disease.Value.Description = results[0]["description"].ToString().Remove(results[0]["description"].ToString().Length - 3);
if (disease.Value.Description.Length > 300)
disease.Value.Description = disease.Value.Description.Remove(300) + " ...";
disease.Value.Disease = results[0]["name"].ToString().Remove(results[0]["name"].ToString().Length - 3);
}
}
}
db.Close();
}
catch (Exception e) { return new ObjectResult(countrydiseases); }
return new ObjectResult(countrydiseases);
}
public string Login(string username, string password)
{
//-1 username or email doesnt exist
try
{
//if (!IsValidEmail(email)) return "-1";
database db = new database(database.maindb);
db.AddParam("?username", username);
db.AddParam("?password", password);
MySqlDataReader rd = db.ExecuteReader("select * from users where lower(username)=lower(?username) and password=?password");
if (!rd.HasRows)
{
Context.Session.SetInt32("on", 0);
// invalid user / pass
db.Close();
return "-1";
}
while (rd.Read())
{
Context.Session.SetInt32("on", 1);
Context.Session.SetInt32("id", rd.GetInt32("id"));
Context.Session.SetString("email", rd.GetString("email"));
Context.Session.SetString("username", rd.GetString("username"));
Context.Session.SetInt32("score", rd.GetInt32("score"));
Context.Session.SetString("country", rd.GetString("country"));
Context.Session.SetInt32("gender", rd.GetInt32("gender"));
Context.Session.SetString("birthday", rd.GetString("birthday"));
break;
}
db.Close();
}
catch (Exception e)
{
// HttpContext.Current.Session["on"] = 0;
return e.ToString();
}
return "1";
}
public IActionResult viewDisease(string diseaseName)
{
ViewBag.error = "false";
ViewBag.diseaseExists = "false";
try
{
if ((Context.Session.GetInt32("on") != null && Context.Session.GetInt32("on") == 1))
{
int userid = (int)Context.Session.GetInt32("id");
string key = diseaseName + userid.ToString();
Models.database db = new database(database.maindb);
MySqlDataReader rd = db.ExecuteReader("replace into viewHistory (id,diseaseName,ky) values(" + userid.ToString() + ", '" + diseaseName + "', '" + key + "');");
db.Close();
}
}
catch { }
try
{
ObjectResult obj = (ObjectResult)new MeAd.Raml.DiseaseDiseaseNameController().Get(diseaseName);
Dictionary<string, string> apil = (Dictionary<string, string>)obj.Value;
if (apil.Count != 0)
{
ViewBag.diseaseExists = "true";
apil["name"] = apil["name"].Replace("%20", " ");
ViewBag.api = apil;
string url = "http://www.wikidoc.org/api.php?action=query&titles=" + diseaseName + "_(patient_information)&export&contentformat=text/plaino";
try
{
WebRequest wrGETURL;
wrGETURL = WebRequest.Create(url);
Stream objStream;
objStream = wrGETURL.GetResponse().GetResponseStream();
StreamReader objReader = new StreamReader(objStream);
string content = objReader.ReadToEnd();
string[] split = content.Split(new string[] { "==" }, StringSplitOptions.None);
string symptoms = split[4].Replace("\\n", "<br/>").Replace(":*", "").Replace("*", "").Replace("[[", "").Replace("]]", "");
ViewBag.symptoms = symptoms;
}
catch
{ ViewBag.symptoms = "N.A."; }
}
}
catch { ViewBag.error = "true"; }
return View();
}
public string getSearch(int max)
{
Dictionary<int, string> mostSearchDiseases = new Dictionary<int, string>();
database db = new database(database.maindb);
int upLim = max * 4;
MySqlDataReader rd = db.ExecuteReader("select diseaseName, COUNT(diseaseName) as nr from viewHistory GROUP BY diseaseName Order by nr DESC LIMIT " + upLim.ToString());
int i = 0;
while (rd.Read() && i < max)
{
//iei valorile rd.GetString("numele coloanei") sau rd.GetInt32("nume coloana");
int deaths = rd.GetInt32("nr");
string code = rd.GetString("diseaseName");
if (mostSearchDiseases.ContainsValue(code)==false && mostSearchDiseases.ContainsKey(deaths)==false)
{
mostSearchDiseases.Add(deaths, code);
i++;
}
}
db.Close();
return JsonConvert.SerializeObject(mostSearchDiseases);
}
public string getTopUsers(int max)
{
Dictionary<string, int> mostSearchDiseases = new Dictionary<string, int>();
database db = new database(database.maindb);
int upLim = max * 4;
MySqlDataReader rd = db.ExecuteReader("select username, score from users Order by score DESC LIMIT " + upLim.ToString());
int i = 0;
while (rd.Read() && i < max)
{
//iei valorile rd.GetString("numele coloanei") sau rd.GetInt32("nume coloana");
int deaths = rd.GetInt32("score");
string code = rd.GetString("username");
if (mostSearchDiseases.ContainsKey(code) == false)
{
mostSearchDiseases.Add(code, deaths);
i++;
}
}
db.Close();
return JsonConvert.SerializeObject(mostSearchDiseases);
}
public string getMostCommonDiseases(int max)
{
Dictionary<int, string> mostSearchDiseases = new Dictionary<int, string>();
database db = new database(database.maindb);
int upLim = max * 4;
MySqlDataReader rd = db.ExecuteReader("select code, SUM(deaths) as deathsno from diseasestatistics GROUP BY code Order by deathsno DESC LIMIT "+upLim.ToString());
int i = 0;
while (rd.Read()&& i<max)
{
//iei valorile rd.GetString("numele coloanei") sau rd.GetInt32("nume coloana");
int deaths = rd.GetInt32("deathsno");
string code = rd.GetString("code");
string diseaseName = getDiseaseNameFromCode(code);
if (!mostSearchDiseases.ContainsValue(diseaseName))
{
mostSearchDiseases.Add(deaths, diseaseName);
i++;
}
}
db.Close();
return JsonConvert.SerializeObject(mostSearchDiseases);
}
public string getCountriesDiseaseObesity(string id, int min, int max)
{
string js = getCountriesDisease(id);
Dictionary<string, int> diseases = JsonConvert.DeserializeObject<Dictionary<string, int>>(js);
Dictionary<string, int> countries = new Dictionary<string, int>();
database db = new database(database.maindb);
MySqlDataReader rd = db.ExecuteReader("select country, obesity from countries");
while (rd.Read())
{
//iei valorile rd.GetString("numele coloanei") sau rd.GetInt32("nume coloana");
string countryName = rd.GetString("country");
int nr = rd.GetInt32("obesity");
if (diseases.ContainsKey(countryName) && (diseases[countryName] >= min && diseases[countryName] <= max))
{
countries.Add(countryName, diseases[countryName]);
}
}
db.Close();
return JsonConvert.SerializeObject(countries);
}
public string getCountriesDiseaseClimate(string id, string climate)
{
string js = getCountriesDisease(id);
Dictionary<string, int> diseases = JsonConvert.DeserializeObject<Dictionary<string, int>>(js);
Dictionary<string, int> countries = new Dictionary<string, int>();
database db = new database(database.maindb);
MySqlDataReader rd = db.ExecuteReader("select country, climate from countries where climate like '%" + climate + "%'");
while (rd.Read())
{
//iei valorile rd.GetString("numele coloanei") sau rd.GetInt32("nume coloana");
string countryName = rd.GetString("country");
string climateDB = rd.GetString("climate");
if (diseases.ContainsKey(countryName))
{
countries.Add(countryName, diseases[countryName]);
}
}
db.Close();
return JsonConvert.SerializeObject(countries);
}
public string getCountriesDisease(string id)
{
Dictionary<string, int> diseaseCount = new Dictionary<string, int>();
diseaseCount.Add(id, 10);
database db = new database(database.maindb);
MySqlDataReader rd = db.ExecuteReader("select country, SUM(deaths) as deaths from diseasestatistics where code like '" + id + "%' GROUP BY country");
while (rd.Read())
{
//iei valorile rd.GetString("numele coloanei") sau rd.GetInt32("nume coloana");
string countryName = rd.GetString("country");
int nr = rd.GetInt32("deaths");
diseaseCount.Add(countryName, nr);
}
db.Close();
return JsonConvert.SerializeObject(diseaseCount);
}
public string FBLogin(string token)
{
try
{
WebClient wc = new WebClient();
wc.Proxy = null;
string res = wc.DownloadString("https://graph.facebook.com/me?fields=email,name,first_name,last_name,gender&access_token=" + token);
Dictionary<string, string> response = JsonConvert.DeserializeObject<Dictionary<string, string>>(res);
if (response.ContainsKey("name"))
{
string id = response["id"];
string email = response["email"];
database db = new database(database.maindb);
db.AddParam("?fbid", id);
db.AddParam("?email", email);
db.AddParam("?username", response["first_name"]+response["last_name"]);
switch (response["gender"])
{
case "male":
db.AddParam("?gender", 1);
break;
case "female":
db.AddParam("?gender", 2);
break;
default:
db.AddParam("?gender", 0);
break;
}
MySqlDataReader rd;
rd = db.ExecuteReader("select * from users where facebookid = ?fbid or email = ?email");
if (rd.HasRows)
while (rd.Read())
{
Context.Session.SetInt32("on", 1);
Context.Session.SetString("email", rd.GetString("email"));
Context.Session.SetString("username", rd.GetString("username"));
Context.Session.SetInt32("id", rd.GetInt32("id"));
Context.Session.SetString("myname", rd.GetString("lastname") + " " + rd.GetString("firstname"));
break;
// return "2";
}
else
{
db.ExecuteNonQuery("insert into users (email,username,gender,facebookid) values (?email,?username,?gender,?fbid)");
rd = db.ExecuteReader("select * from users where facebookid = ?fbid or email = ?email");
while (rd.Read())
{
Context.Session.SetInt32("on", 1);
Context.Session.SetString("email", rd.GetString("email"));
Context.Session.SetInt32("id", rd.GetInt32("id"));
Context.Session.SetString("username", rd.GetString("username"));
Context.Session.SetInt32("gender", rd.GetInt32("gender"));
}
//return "3";
}
db.Close();
return "1";
}
else
return "0";
}
catch (Exception ex)
{
return ex.ToString();
}
}