public int Add(User user)
{
return _client.Proxy.Add(user);
}
public void AddUser(User u)
{
db.User.Add(u);
db.SaveChanges();
}
public void UpdateUser(User u)
{
db.Entry(u).State = System.Data.EntityState.Modified;
db.SaveChanges();
}
public User GetUserByUsername(string usrname)
{
string command = string.Format("Select Id, Username, Email, Name, Passion1, Passion2, Passion3, BirthDate from Users where Username = '******'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
//cmd.ExecuteReader();
User userFound = new User();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
userFound.Id = Int32.Parse(reader.GetValue(0).ToString());
userFound.Username = reader.GetValue(1).ToString();
userFound.Email = reader.GetValue(2).ToString();
userFound.Name = reader.GetValue(3).ToString();
userFound.Passion1 = Int32.Parse(reader.GetValue(4).ToString());
userFound.Passion2 = Int32.Parse(reader.GetValue(5).ToString());
userFound.Passion3 = Int32.Parse(reader.GetValue(6).ToString());
userFound.Bday = DateTime.Parse(reader.GetValue(7).ToString(), new System.Globalization.CultureInfo("fr-FR", true));
}
}
sqlConnection1.Close();
return userFound;
}
public List<User> GetAllUsers(string realname)
{
int myid = GetUserByUsername(realname).Id;
int[] values = GetAllMyConnections(myid);
List<int> results = new List<int>();
string command = string.Format("Select id from Users ");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int hisId = Int32.Parse(reader.GetValue(0).ToString());
bool found = false;
int i = 0;
while (!found && i<100)
{
if((values[i]==myid && values[i+1]==hisId) || (values[i+1] == myid && values[i]== hisId))
{
found = true;
}
else
{
i += 2;
}
}
if (!found)
{
results.Add(hisId);
}
}
}
List<User> users = new List<User>();
int[] S = results.Distinct().ToArray();
sqlConnection1.Close();
foreach (int i in S)
{
command = string.Format("Select Id, Username, Email, Name, Passion1, Passion2, Passion3 , BirthDate , isAdvisor from Users where Id = '" + i + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (!reader.GetValue(1).ToString().Equals(realname) && (Int32.Parse(reader.GetValue(8).ToString())!=1))
{
User user = new User();
user.Id = Int32.Parse(reader.GetValue(0).ToString());
user.Username = reader.GetValue(1).ToString();
user.Email = reader.GetValue(2).ToString();
user.Name = reader.GetValue(3).ToString();
user.Passion1 = Int32.Parse(reader.GetValue(4).ToString());
user.Passion2 = Int32.Parse(reader.GetValue(5).ToString());
user.Passion3 = Int32.Parse(reader.GetValue(6).ToString());
user.Bday = DateTime.Parse(reader.GetValue(7).ToString(), new System.Globalization.CultureInfo("fr-FR", true));
users.Add(user);
}
}
}
sqlConnection1.Close();
}
return users;
}
public List<User> GetAllResults(int passion1, int passion2, int passion3,string realname)
{
List<int> results = new List<int>();
if (passion1 != 0)
{
string command = string.Format("Select id from Users Where Passion1 = '" + passion1 + "'" + " OR Passion1 = '" + passion2 + "'" + " OR Passion1 = '" + passion3 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
results.Add(Int32.Parse(reader.GetValue(0).ToString()));
}
}
sqlConnection1.Close();
}
if (passion2 != 0)
{
string command = string.Format("Select id from Users Where passion2 = '" + passion1 + "'" + " OR passion2 = '" + passion2 + "'" + " OR passion2 = '" + passion3 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
results.Add(Int32.Parse(reader.GetValue(0).ToString()));
}
}
sqlConnection1.Close();
}
if (passion3 != 0)
{
string command = string.Format("Select id from Users Where passion3 = '" + passion1 + "'" + " OR passion2 = '" + passion2 + "'" + " OR passion3 = '" + passion3 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
results.Add(Int32.Parse(reader.GetValue(0).ToString()));
}
}
sqlConnection1.Close();
}
List<User> users = new List<User>();
int[] S = results.Distinct().ToArray();
foreach (int i in S)
{
//i wanted to check if i am in the list too so prevent the program from showing myself.
//if (i == Int32.Parse((request.Cookies.Get("userName").Value)))
//{
string command = string.Format("Select Id, Username, Email, Name, Passion1, Passion2, Passion3 , BirthDate from Users where Id = '" + i + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (!reader.GetValue(1).ToString().Equals(realname))
{
User user = new User();
user.Id = Int32.Parse(reader.GetValue(0).ToString());
user.Username = reader.GetValue(1).ToString();
user.Email = reader.GetValue(2).ToString();
user.Name = reader.GetValue(3).ToString();
user.Passion1 = Int32.Parse(reader.GetValue(4).ToString());
user.Passion2 = Int32.Parse(reader.GetValue(5).ToString());
user.Passion3 = Int32.Parse(reader.GetValue(6).ToString());
user.Bday = DateTime.Parse(reader.GetValue(7).ToString(), new System.Globalization.CultureInfo("fr-FR", true));
users.Add(user);
}
}
}
sqlConnection1.Close();
//==========================================================
foreach (User u in users)
{
command = string.Format("Select Passion from Passions where Id = '" + u.Passion1 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
u.Passion1String = reader.GetValue(0).ToString();
}
}
sqlConnection1.Close();
command = string.Format("Select Passion from Passions where Id = '" + u.Passion2 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
u.Passion2String = reader.GetValue(0).ToString();
}
}
sqlConnection1.Close();
command = string.Format("Select Passion from Passions where Id = '" + u.Passion3 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
u.Passion3String = reader.GetValue(0).ToString();
}
}
sqlConnection1.Close();
}
}
//User users2 = new User();
//foreach (User u in users)
//{
//}
return users;
}
public List<User> GetAllPendingConnections(User myUser)
{
//Edit this
List<int> results = new List<int>();
if (myUser != null)
{
//string command = string.Format("Select ReceiverId from Connections Where SenderId = '" + myUser.Id + "'" + " AND Accepted = '0' ");
//cmd.CommandText = command;
//cmd.CommandType = CommandType.Text;
//cmd.Connection = sqlConnection1;
//sqlConnection1.Open();
//using (SqlDataReader reader = cmd.ExecuteReader())
//{
// while (reader.Read())
// {
// results.Add(Int32.Parse(reader.GetValue(0).ToString()));
// }
//}
sqlConnection1.Open();
string command = string.Format("Select ReceiverId from Connections Where SenderId = '" + myUser.Id + "'" + " AND Accepted = '0' ");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
results.Add(Int32.Parse(reader.GetValue(0).ToString()));
}
}
sqlConnection1.Close();
}
List<User> users = new List<User>();
foreach (int i in results)
{
string command = string.Format("Select Id, Username, Email, Name, Passion1, Passion2, Passion3, BirthDate from Users where Id = '" + i + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
User user = new User();
user.Id = Int32.Parse(reader.GetValue(0).ToString());
user.Username = reader.GetValue(1).ToString();
user.Email = reader.GetValue(2).ToString();
user.Name = reader.GetValue(3).ToString();
user.Passion1 = Int32.Parse(reader.GetValue(4).ToString());
user.Passion2 = Int32.Parse(reader.GetValue(5).ToString());
user.Passion3 = Int32.Parse(reader.GetValue(6).ToString());
user.Bday = DateTime.Parse(reader.GetValue(7).ToString(), new System.Globalization.CultureInfo("fr-FR", true));
users.Add(user);
}
}
sqlConnection1.Close();
//==========================================================
foreach (User u in users)
{
command = string.Format("Select Passion from Passions where Id = '" + u.Passion1 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
u.Passion1String = reader.GetValue(0).ToString();
}
}
sqlConnection1.Close();
command = string.Format("Select Passion from Passions where Id = '" + u.Passion2 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
u.Passion2String = reader.GetValue(0).ToString();
}
}
sqlConnection1.Close();
command = string.Format("Select Passion from Passions where Id = '" + u.Passion3 + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
u.Passion3String = reader.GetValue(0).ToString();
}
}
sqlConnection1.Close();
}
}
return users;
}
public List<User> GetAllAdvisors()
{
List<int> results = new List<int>();
string command = string.Format("Select id from Users where isAdvisor = 1");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
results.Add(Int32.Parse(reader.GetValue(0).ToString()));
}
}
List<User> users = new List<User>();
int[] S = results.Distinct().ToArray();
sqlConnection1.Close();
foreach (int i in S)
{
command = string.Format("Select Id, Username, Email, Name, Passion1, Passion2, Passion3 , BirthDate , isAdvisor from Users where Id = '" + i + "'");
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
User user = new User();
user.Id = Int32.Parse(reader.GetValue(0).ToString());
user.Username = reader.GetValue(1).ToString();
user.Email = reader.GetValue(2).ToString();
user.Name = reader.GetValue(3).ToString();
user.Passion1 = Int32.Parse(reader.GetValue(4).ToString());
user.Passion2 = Int32.Parse(reader.GetValue(5).ToString());
user.Passion3 = Int32.Parse(reader.GetValue(6).ToString());
user.Bday = DateTime.Parse(reader.GetValue(7).ToString(), new System.Globalization.CultureInfo("fr-FR", true));
users.Add(user);
}
}
sqlConnection1.Close();
}
return users;
}
