public List <Receptmodels> GetFood(string psql, int pid_user)
{
postgres m = new postgres();
DataTable dt = new DataTable();
List <Receptmodels> mt = new List <Receptmodels>();
dt = m.SqlQuery(psql, postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id_user", pid_user)
});
foreach (DataRow dr in dt.Rows)
{
Receptmodels r = new Receptmodels();
r.Id = Convert.ToInt16(dr["id_recept"].ToString());
r.Name = dr["name"].ToString();
r.Description = (string)dr["description"];
r.Url_pic = (string)dr["url_pic"];
r.Url_recept = (string)dr["url_recept"];
mt.Add(r);
}
return(mt);
}
public List <UserService> GetuserAsAdmin(int id, string sql)
{
postgres m = new postgres();
DataTable dt = new DataTable();
List <UserService> mt = new List <UserService>();
dt = m.SqlQuery(sql, postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id", id)
});
foreach (DataRow dr in dt.Rows)
{
UserService r = new UserService();
r.User_id = (int)dr["user_id"];
r.User = dr["username"].ToString();
r.email = (string)dr["email"];
r.active = (bool)dr["acc_active"];
r.Roles_id = (int)dr["roles_id"];
r.Last_login = (DateTime)dr["last_login"];
r.Last_name = (string)dr["last_name"];
r.First_name = (string)dr["fname"];
r.Settings_id = (int)dr["settings_id"];
mt.Add(r);
}
return(mt);
}
public Tuple <int, bool, string> Resetpassword(string validate)
{
postgres sql = new postgres();
DataTable dt = new DataTable();
dt = sql.SqlQuery("SELECT login.login_id, login.reset_hash, login.reset_time, users.username FROM login LEFT JOIN users ON users.login_id = login.login_id WHERE reset_hash = @hash;", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@hash", validate)
});
string hash = "";
DateTime date = DateTime.Now;
int login_id = 0;
string username = "";
foreach (DataRow dr in dt.Rows)
{
hash = dr["reset_hash"].ToString();
username = dr["username"].ToString();
date = (DateTime)dr["reset_time"];
login_id = (int)dr["login_id"];
}
if (!string.IsNullOrEmpty(hash))
{
if (validate == hash && date >= DateTime.UtcNow)
{
return(Tuple.Create(login_id, true, username));
}
}
return(Tuple.Create(login_id, false, username));
}
private void CreateUser(string user, string email, bool active, string Password, string fname, string last_name)
{
Tuple <byte[], byte[]> password = Generatepass(Password);
postgres sql = new postgres();
// Behöver skrivas om! klart!
postgres sql2 = new postgres();
int id = sql2.SqlQueryString("INSERT INTO login (salt, hash, reset_time, reset_hash) VALUES (@salt ,@hash, @time, 1) RETURNING login_id;", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@salt", password.Item1),
new NpgsqlParameter("@hash", password.Item2),
new NpgsqlParameter("@time", Convert.ToDateTime("1970-01-01 00:00:00"))
});
postgres sql3 = new postgres();
int id_setting = sql3.SqlQueryString("INSERT INTO usersettings (day_of_slumpcron) VALUES (6) RETURNING setting_id;", postgres.list = new List <NpgsqlParameter>()
{
});
sql.SqlNonQuery("INSERT INTO users (username,roles_id,email,acc_active,last_login,login_id,settings_id,fname,last_name) VALUES (@par1,'2',@email,@active,@last_login,@login_id,@settings_id,@fname,@last_name)", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@par1", user),
new NpgsqlParameter("@email", email),
new NpgsqlParameter("@active", active),
new NpgsqlParameter("@login_id", id),
new NpgsqlParameter("@settings_id", id_setting),
new NpgsqlParameter("@last_login", DateTime.Now),
new NpgsqlParameter("@fname", fname),
new NpgsqlParameter("@last_name", last_name)
});
}
public List <UsersEditViewmodel> Getuser(int id, string sql)
{
postgres m = new postgres();
System.Data.DataTable dt = new DataTable();
List <UsersEditViewmodel> mt = new List <UsersEditViewmodel>();
dt = m.SqlQuery(sql, postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id", id)
});
foreach (DataRow dr in dt.Rows)
{
UsersEditViewmodel r = new UsersEditViewmodel();
r.User_id = (int)dr["user_id"];
r.User = dr["username"].ToString();
r.email = (string)dr["email"];
r.First_name = (string)dr["fname"];
r.Last_name = (string)dr["last_name"];
r.CronoDay = Weeklist.CheckCronoNumber((int)dr["day_of_slumpcron"]);
mt.Add(r);
}
return(mt);
}
public void HardDeletOfUser(int userId)
{
postgres sql = new postgres();
sql.SqlNonQuery("SELECT harddeleteofuser(@user_id)", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@user_id", userId)
});
}
public Tuple <int, bool, string> AuthenticationUser(string ppassword, string userNameOrEmail)
{
byte[] salt = null, key = null;
int user_id = 0;
postgres m = new postgres();
string role = "";
bool active = false;
string sql;
userNameOrEmail = userNameOrEmail.ToLower();
sql = "select user_id, name, acc_active , login.salt , login.hash from users LEFT JOIN roles ON roles_id = id_roles LEFT JOIN login ON users.login_id = login.login_id where email =@par1 or users.username =@par1 ";
var dt = m.SqlQuery(sql, postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@par1", userNameOrEmail),
});
try
{
foreach (DataRow dr in dt.Rows)
{
salt = (byte[])dr["salt"];
key = (byte[])dr["hash"];
user_id = (int)dr["user_id"];
role = (string)dr["name"];
active = (bool)dr["acc_active"];
}
}
catch
{
}
if (salt != null && active == true)
{
using (var deriveBytes = new Rfc2898DeriveBytes(ppassword, salt))
{
byte[] newKey = deriveBytes.GetBytes(192);
if (!newKey.SequenceEqual(key))
{
return(Tuple.Create(user_id, false, role));
}
else
{
postgres p = new postgres();
p.SqlNonQuery("UPDATE public.users SET last_login=@d WHERE user_id = @user_id;", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@d", DateTime.Now),
new NpgsqlParameter("@user_id", user_id)
});
return(Tuple.Create(user_id, true, role));
}
}
}
else
{
return(Tuple.Create(user_id, false, role));
}
}
public void RemovefoodFromUser(string id_user, int id_recept)
{
postgres m = new postgres();
m.SqlNonQuery("DELETE FROM users_has_recept where recept_id =@id_recept AND user_id = @id_user", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id_user", Convert.ToInt16(id_user)),
new NpgsqlParameter("@id_recept", id_recept)
});
}
public void AddFoodToUser(string id_user, int id_recept)
{
postgres m = new postgres();
m.SqlNonQuery("INSERT INTO users_has_recept (recept_id ,user_id) values(@id_recept, @id_user)", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id_user", Convert.ToInt16(id_user)),
new NpgsqlParameter("@id_recept", id_recept)
});
}
public void DeleteUser(int User_id)
{
postgres sql = new postgres();
sql.SqlNonQuery("UPDATE Users SET username=@user, email=@email, acc_active = false WHERE user_id = @user_id", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@user_id", User_id),
new NpgsqlParameter("@user", "test"),
new NpgsqlParameter("@email", "*****@*****.**")
});
}
public void EditFood(int recept_id, string pname, string des, string url_pic, string url_recept)
{
postgres m = new postgres();
m.SqlNonQuery("UPDATE recept SET name = @name ,description = @description, url_pic =@url_pic,url_recept=@url_recept WHERE id_recept = @recept_id", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@recept_id", recept_id),
new NpgsqlParameter("@name", pname),
new NpgsqlParameter("@url_pic", url_pic),
new NpgsqlParameter("@url_recept", url_recept),
new NpgsqlParameter("@description", des)
});
}
public void AddNewFood(string pname, string des, string url_pic, string url_recept, int user_id)
{
postgres m = new postgres();
m.SqlNonQuery("INSERT INTO recept (name,description, created_by_user, url_pic,url_recept) values(@name,@description,@user_id,@url_pic,@url_recept)", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@name", pname),
new NpgsqlParameter("@description", des),
new NpgsqlParameter("@url_pic", url_pic),
new NpgsqlParameter("@url_recept", url_recept),
new NpgsqlParameter("@user_id", user_id)
});
}
public void Newpassword(int login_id, string newpassword)
{
Tuple <byte[], byte[]> password = _accountService.Generatepass(newpassword);
postgres sql = new postgres();
// behöver skrivas om! klart
sql.SqlNonQuery("UPDATE login set salt= @par2, hash =@par3 WHERE login_id =@par1", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@par1", login_id),
new NpgsqlParameter("@par2", password.Item1),
new NpgsqlParameter("@par3", password.Item2)
});
}
public bool Forgetpassword(string username)
{
postgres sql = new postgres();
DataTable dt = new DataTable();
string query;
if (username.Contains("@"))
{
query = "SELECT login_id, username, email, acc_active FROM users WHERE email = @username;";
}
else
{
query = "SELECT login_id, username, email, acc_active FROM users WHERE username = @username;";
}
dt = sql.SqlQuery(query, postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@username", username)
});
UserService r = new UserService();
foreach (DataRow dr in dt.Rows)
{
r.User = dr["username"].ToString();
r.email = (string)dr["email"];
r.active = (bool)dr["acc_active"];
r.Login_id = (int)dr["login_id"];
}
if (string.IsNullOrEmpty(r.User) || !r.active)
{
return(false);
}
string hash = GeneratePassword(60);
postgres sql1 = new postgres();
DateTime date = DateTime.UtcNow;
date = date.AddHours(3);
sql1.SqlNonQuery("UPDATE login set reset_hash=@hash , reset_time =@time WHERE login_id=@id ", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id", r.Login_id),
new NpgsqlParameter("@hash", hash),
new NpgsqlParameter("@time", date)
});
string message = EmailService.EmailOther(r.User, "Här kommer din återställningslänk: <a href=\"https://matslumpiator.se/Account/Resetpassword?validate=" + hash + "\" target=\"_blank\" >Återställlösenordet </a>");
_emailService.SendEmail(r.email, r.User, "Återställning Lösenord", message);
return(true);
}
public void UpdateUser(int User_id, string username, string email, string first_name, string last_name, int Slumpday)
{
postgres sql = new postgres();
//Behöver skrivas OM! klar
sql.SqlNonQuery("Select update_user(@username,@email,@first_name,@lastname,@cronoday,@user_id)", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@username", username),
new NpgsqlParameter("@email", email),
new NpgsqlParameter("@first_name", first_name),
new NpgsqlParameter("@lastname", last_name),
new NpgsqlParameter("@cronoday", Slumpday),
new NpgsqlParameter("@user_id", User_id)
});
}
public bool Checkslump(DateTime date, int user_id)
{
postgres m = new postgres();
bool check = false;
DataTable dt = new DataTable();
dt = m.SqlQuery("SELECT EXISTS(SELECT foodlist.date_now,foodlist.recept_id FROM public.foodlist Where foodlist.user_id = @user_id AND date_now = @date_now)", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@date_now", date),
new NpgsqlParameter("@user_id", user_id)
});
foreach (DataRow dr in dt.Rows)
{
check = (bool)dr["exists"];
}
return(check);
}
public void SaveSlump(int recept_id, int user_id, DateTime date, bool check)
{
date = date.Date;
string sql = "INSERT INTO foodlist (user_id,recept_id,date_now) values(@user_id,@recept_id,@date_now)";
if (check)
{
sql = "UPDATE foodlist SET recept_id = @recept_id WHERE date_now=@date_now AND user_id = @user_id";
}
postgres m = new postgres();
m.SqlNonQuery(sql, postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@recept_id", recept_id),
new NpgsqlParameter("@date_now", date),
new NpgsqlParameter("@user_id", user_id)
});
}
public List <Receptmodels> GetFoodListForReceptView(string psql, int pid_user, string search)
{
postgres m = new postgres();
DataTable dt = new DataTable();
List <Receptmodels> mt = new List <Receptmodels>();
if (!string.IsNullOrEmpty(search))
{
string modal = "%";
dt = m.SqlQuery(psql, postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id_user", pid_user),
new NpgsqlParameter("@search", modal += search += "%")
});
}
else
{
dt = m.SqlQuery(psql, postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id_user", pid_user)
});
}
foreach (DataRow dr in dt.Rows)
{
Receptmodels r = new Receptmodels();
r.Id = Convert.ToInt16(dr["id_recept"].ToString());
r.Name = dr["name"].ToString();
r.Description = (string)dr["description"];
r.Url_pic = (string)dr["url_pic"];
r.Url_recept = (string)dr["url_recept"];
r.cookingtime = (string)dr["time_name"];
r.TypeOfFood = (string)dr["type_name"];
r.Occasions = dr["occasion_id"].ToString();
r.Rating = (double)dr["average_rating"];
mt.Add(r);
}
return(mt);
}
public List <Receptmodels> Oldslumps(int user_id, DateTime date, DateTime dateto)
{
// Slump Slump = new Slump();
postgres m = new postgres();
List <Receptmodels> mt = new List <Receptmodels>();
DataTable dt = new DataTable();
dt = m.SqlQuery("SELECT foodlist.date_now,foodlist.recept_id,recept.name,recept.description,recept.url_pic,recept.url_recept, cookingtime.time_name, type_of_food.type_name, recept.average_rating,recept.occasion_id FROM public.foodlist join public.recept on foodlist.recept_id = recept.id_recept LEFT JOIN type_of_food ON recept.type_of_food_id = type_of_food.id LEFT JOIN cookingtime ON recept.cookingtime_id = cookingtime.id Where foodlist.user_id = @id_user AND date_now BETWEEN @datefrom AND @dateto ORDER BY date_now DESC; ", postgres.list = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@id_user", user_id),
new NpgsqlParameter("@dateto", dateto),
new NpgsqlParameter("@datefrom", date)
});
foreach (DataRow dr in dt.Rows)
{
Receptmodels r = new Receptmodels();
r.Id = (int)dr["recept_id"];
r.Name = dr["name"].ToString();
r.Date = (DateTime)dr["date_now"];
r.Description = (string)dr["description"];
r.Url_pic = (string)dr["url_pic"];
r.Url_recept = (string)dr["url_recept"];
r.cookingtime = (string)dr["time_name"];
r.TypeOfFood = (string)dr["type_name"];
r.Occasions = dr["occasion_id"].ToString();
r.Rating = (double)dr["average_rating"];
// var dateName = new Slumpservices();
r.DateName = NameOfDay(r.Date);
r.Weeknumbers = GetIso8601WeekOfYear(r.Date).ToString();
mt.Add(r);
}
return(mt);
}