public static int SetCityId(string cityname)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
return(db.Cities.FirstOrDefault(c => c.CityName == cityname).CityId);
}
}
public static List <ProfessionalDTO> GetProfessionalList()
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
return(ProfessionalConvertion.ProfessionalsListToDTO(db.Professionals.ToList()));
}
}
// public static List<ProfessionalDTO> GetFilteredProfessionals(int profession, int city)
// {
// using (RecommendationsEntities2 db = new RecommendationsEntities2())
// {
// List<ProfessionalDTO> profByspec = ProfessionalConvertion.ProfessionalsListToDTO(db.Professionals.ToList());
// List<SpecializationsForProfessionalsDTO> specializations = SpecForProfConvertion.SpecForProfListToDTO(db.SpecializationsForProfessionals.ToList());
// profByspec = profByspec.Where(p => profession == specializations.FirstOrDefault(e => e.Professional == p.UserId)?.specialization).ToList();
// List<ProfessionalDTO> professionalResult = profByspec.Where(p => p.City == city).ToList();
// if (professionalResult.Count() == 0 && profByspec.Count() > 0)
// {
// professionalResult = ProfessionalConvertion.ProfessionalsListToDTO(ProfessionalConvertion.ProfessionalsListToDal(professionalResult).Where(p => p.Users.Cities.Area == GetAreaByCityId(city)).ToList());
// }
// return professionalResult;
// }
// }
// public static int GetAreaByCityId(int city)
// {
// using (RecommendationsEntities2 db = new RecommendationsEntities2())
// {
// List<CitiesDTO> cities = CitiesConvertion.CityListToDTO(db.Cities.ToList());
// return cities.Find(f => f.CityId == city).Area.Value;
// }
// }
//}
public static List <Cities> GetCitiesByArea(int area)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
return(db.Cities.Where(c => c.Area == area).ToList());
}
}
//get professional
public static object GetProfessionalNameById(int id)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
return(db.Professionals.Find(id).BussName);
}
}
//get profession id by profession name
public static int GetProfessionbyName(string name)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
return(db.Professions.FirstOrDefault(p => p.ProfessionName == name).ProfessionId);
}
}
//cities
/// <summary>
/// get list of cities
/// </summary>
/// <returns></returns>
public static List <CitiesDTO> GetCitiyList()
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
return(CitiesConvertion.CityListToDTO(db.Cities.ToList()));
}
}
public static bool IsExistsPassword(string password, int id)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
var a = db.Users.FirstOrDefault(p => p.UserPassword == password && p.UserId != id);
return(a != null);
}
}
/// <summary>
/// search the recommendation of profesional
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static List <RecommendationsDTO> GetRecommendationsOfProf(int id)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
List <RecommendationsDTO> p = RecommendationConvertion.RecommendationListToDTO(db.Recommendations.Where(r => r.Professional == id).ToList());
return(p);
}
}
public static List <ProfessionDTO> GetProfessions()
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())//??????????????????????????????????
{
//להחזיר מייד רשימה של מקצועות וגם התמחויות או להחזיר רשימה של מקצועות וכאשר יבחר מקצוע תתבצע שוב קריאה לשרת ע"מ לקבל את רשימת ההתמחויות??????
return(ProfessionConvertion.professionsListToDTO(db.Professions.ToList()));
}
}
public static string selectedAreas()
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
string result = "";
int i = 1;
db.Areas.ToList().ForEach(a => { result += $"לאיזור{a.AreaName}הקש{i++}"; });
return(result);
}
}
//returns the profession that begin with the letters that in string 'str'
public static List <string> GetProfessionsByLetters(string str)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
List <string> profName = new List <string>();
for (int i = 0; i < str.Length; i++)
{
profName.AddRange(db.Professions.ToList().Where(p => p.ProfessionName[0] == str[i]).Select(n => n.ProfessionName).ToList());
}
return(profName);
}
}
/// <summary>
/// function for user login
/// check if the user exists in the DB
/// </summary>
/// <param name="userName"></param>
/// <param name="userPassword"></param>
/// <returns></returns>
public static int LogIn(string userName, string userPassword)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
Users user = db.Users.FirstOrDefault(u => u.UserPassword == userPassword && u.UserName == userName);
if (user != null)
{
return(user.UserId);
}
return(0);
}
}
public static UsersDTO GetProfessionalById(int id)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
var p = db.Users.Find(id);
if (p.Professionals != null)
{
return(ProfessionalConvertion.ProfessionalToDTO(p.Professionals));
}
return(UsersConvertion.UserToDTO(p));
}
}
public static List <Professionals> GetFilteredProfessionals(int letterForProf, int profession, int area, int city)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
List <string> profName = ProfessionsService.GetProfessionsByLetters(VoiceService.SelectedLetters(letterForProf));
List <Cities> cities = GetCitiesByArea(area);
int prof = ProfessionsService.GetProfessionbyName(profName[profession]);
int c = cities[city].CityId;
List <Professionals> professionals = db.Professionals.ToList();
List <ProfessionForProfessional> specforProf = db.ProfessionForProfessional.ToList();
List <Professionals> result = professionals.Where(p => p.Users.City == c && prof == specforProf.FirstOrDefault(s => s.Professional == p.ProfessionalId).Profession).ToList();
return(result);
}
}
public static bool SetProfessionsToProfessional(ProfessionalDTO professional)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
db.ProfessionForProfessional.AddRange(professional.professions.Select(p => new ProfessionForProfessional {
Profession = p.ProfessionId, Professional = professional.UserId
}));
try
{
db.SaveChanges();
return(true);
}
catch { return(false); }
}
}
public static bool RegisterProfessional(ProfessionalDTO professional)//??????profession
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
if (professional.CityName != null)
{
professional.City = SetCityId(professional.CityName);
}
var prof = db.Professionals.Find(professional.ProfessionalId);
var user = db.Users.Find(professional.UserId);
//ProfessionsService.SetProfessionsToProfessional(professional);הוספת מקצועות לבעל מקצוע
if (prof == null)
{
if (user == null)
{
db.Professionals.Add(ProfessionalConvertion.ProfessionalToDal(professional));
}
else
{
db.Professionals.Add(ProfessionalConvertion.ProfessionalWithoutUserToDal(professional));
}
}
else
{
prof.FirstName = professional.FirstName;
prof.LastName = professional.LastName;
prof.BussName = professional.BussName;
prof.NumHouse = professional.NumHouse;
prof.Street = professional.Street;
prof.DescriptionOn = professional.DescriptionOn;
prof.Users.City = professional.City;
prof.Users.IsManager = professional.IsManager;
prof.Users.UserPassword = professional.UserPassword;
prof.Users.UserPhone = professional.UserPhone;
prof.Users.UserName = professional.UserName;
//prof.ProfessionForProfessional.Add.AddRange(professional.professions.Select(p => new ProfessionForProfessional { Profession = p.ProfessionId, Professional = professional.UserId }));
//לעשות הוספת מקצועות לבעל מקצוע בשתי האפשרויות :יצירה ועדכון
}
try
{
db.SaveChanges();
return(true);
}
catch { return(false); }
}
}
/// <summary>
/// return list of profession filtered by profession and city
/// </summary>
/// <param name="profession"></param>
/// <param name="city"></param>
/// <returns></returns>
public static List <RecForProf> GetFilteredProfessionals(int profession, int city)//google maps!!!!!!!!!!!!!!!!
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
List <Professionals> f = db.Professionals.ToList();
List <ProfessionForProfessionalDTO> s = ProfForProfConvertion.ProfForProfListToDTO(db.ProfessionForProfessional.ToList());
List <RecForProf> d = f.Where(p => p.Users.City == city &&
profession == s.FirstOrDefault(e => e.Professional == p.Users.UserId)?.Profession).Select(p =>
new RecForProf {
Professional = convertion.ProfessionalConvertion.ProfessionalToDTO(p), NumRec = p.Recommendations.Count
, RateArrival = (int)p.Recommendations.ToList().Average(r => r.RateArrival).Value
, RatePrice = (int)p.Recommendations.ToList().Average(r => r.RatePrice).Value
, RateProfessionalism = (int)p.Recommendations.ToList().Average(r => r.RateProfessionalism).Value
}).ToList();
return(d);
}
}
/// <summary>
/// function for user register
/// add the user to users table in the DB
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static bool RegisterUser(UsersDTO user)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
var prof = db.Professionals.Find(user.UserId);
if (prof != null)
{
prof.UserSearch.Clear();
prof.Recommendations.Clear();
prof.ProfessionForProfessional.Clear();
db.Professionals.Remove(prof);
}
if (user.CityName != null)
{
user.City = SetCityId(user.CityName);
}
var u = db.Users.Find(user.UserId);
if (u == null)
{
db.Users.Add(UsersConvertion.UserToDal(user));
}
else
{
u.UserName = user.UserName;
u.UserPassword = user.UserPassword;
u.UserPhone = user.UserPhone;
u.UserEmail = user.UserEmail;
u.IsManager = user.IsManager;
u.City = user.City;
}
try
{
db.SaveChanges();
return(true);
}
catch { return(false); }
}
}
/// <summary>
/// function for create a recommendation
/// if the ratings is null- put 50 on it
/// take the date of today
/// </summary>
/// <param name="recommendation"></param>
/// <returns></returns>
public static bool CreateRecommendation(RecommendationsDTO recommendation)
{
using (RecommendationsEntities3 db = new RecommendationsEntities3())
{
var rec = db.Recommendations.FirstOrDefault(r => r.Professional == recommendation.Professional && r.UserId == recommendation.UserId);
if (rec != null)
{
return(false);
}
recommendation.RateArrival = recommendation.RateArrival == null ? 50 : recommendation.RateArrival;
recommendation.RatePrice = recommendation.RatePrice == null ? 50 : recommendation.RatePrice;
recommendation.RateProfessionalism = recommendation.RateProfessionalism == null ? 50 : recommendation.RateProfessionalism;
recommendation.RecommendationDate = DateTime.Now;
db.Recommendations.Add(RecommendationConvertion.RecommendationToDal(recommendation));
try
{
db.SaveChanges();
return(true);
}
catch { return(false); }
}
}
