public IActionResult GetLocation(double lat, double lng) { try { string address = DistancesHelper.getAddressFromCoordinates(lat.ToString(new CultureInfo("en-US")), lng.ToString(new CultureInfo("en-US"))); return(Ok(new { address })); } catch (Exception ex) { return(UnprocessableEntity(ex.Message)); } }
/// <summary> /// Método de pesquisa de trabalho disponível para o mate com vários filtros disponíveis /// </summary> /// <param name="categories">Filtro de Categorias</param> /// <param name="address">Filtro da Morada</param> /// <param name="distance">Filtro de distância</param> /// <param name="rating">Filtro de Rating</param> /// <param name="mateId"></param> /// <returns>Retorna a listagem de posts</returns> public List <JobPostReturnedModel> GetJobs(Categories[] categories, string address, int?distance, int?rating, int mateId) { List <JobPostReturnedModel> posts = new List <JobPostReturnedModel>(); using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; bool hasQuery = false; string query = " where "; if (rating != null) { hasQuery = true; query += " (AverageRating >= " + rating + ")"; } if (categories.Length != 0) { if (hasQuery) { query += "and ("; int lastOne = categories.Length - 1; for (int i = 0; i < lastOne; i++) { query += "Category=" + (int)categories[i] + " or "; } query += "Category=" + (int)categories[lastOne] + " )"; } else { hasQuery = true; int lastOne = categories.Length - 1; query += "("; for (int i = 0; i < lastOne; i++) { query += "Category=" + (int)categories[i] + " or "; } query += "Category=" + (int)categories[lastOne] + ") "; } } if (hasQuery) { cmd.CommandText = "Select dbo.[JobPosts].Id, dbo.[JobPosts].Title, dbo.[JobPosts].Category, dbo.[JobPosts].Description, dbo.[JobPosts].Tradable, dbo.[JobPosts].InitialPrice, dbo.[JobPosts].Address, dbo.[JobPosts].EmployerId, dbo.[User].AverageRating FROM dbo.[JobPosts] INNER JOIN dbo.[User] ON dbo.[JobPosts].EmployerId = dbo.[User].Id" + query + "AND NOT EXISTS (SELECT * FROM dbo.[IgnoredJobs] WHERE dbo.[IgnoredJobs].MateId = " + mateId + " AND dbo.[IgnoredJobs].JobPostId = dbo.[JobPosts].Id ) AND dbo.[JobPosts].IsDone = 0 ORDER BY dbo.[JobPosts].Id ASC;"; } else { cmd.CommandText = "Select dbo.[JobPosts].Id, dbo.[JobPosts].Title, dbo.[JobPosts].Category, dbo.[JobPosts].Description, dbo.[JobPosts].Tradable, dbo.[JobPosts].InitialPrice, dbo.[JobPosts].Address, dbo.[JobPosts].EmployerId, dbo.[User].AverageRating FROM dbo.[JobPosts] INNER JOIN dbo.[User] ON dbo.[JobPosts].EmployerId = dbo.[User].Id WHERE NOT EXISTS (SELECT * FROM dbo.[IgnoredJobs] WHERE dbo.[IgnoredJobs].MateId = " + mateId + " AND dbo.[IgnoredJobs].JobPostId = dbo.[JobPosts].Id ) AND dbo.[JobPosts].IsDone = 0 ORDER BY dbo.[JobPosts].Id ASC;"; } using (SqlDataAdapter adpt = new SqlDataAdapter(cmd)) { DataTable table = new DataTable(); adpt.Fill(table); foreach (DataRow row in table.Rows) { JobPostReturnedModel post = new JobPostReturnedModel { Id = int.Parse(row["Id"].ToString()), Title = row["Title"].ToString(), Description = row["Description"].ToString(), Tradable = (bool)row["Tradable"], Category = (Categories)row["Category"], InitialPrice = (double)row["InitialPrice"], Address = row["Address"].ToString(), EmployerId = int.Parse(row["EmployerId"].ToString()), }; using (SqlCommand paymentCommand = _connection.Fetch().CreateCommand()) { paymentCommand.CommandType = CommandType.Text; paymentCommand.CommandText = "Select dbo.[PaymentJob].PaymentTypeId FROM dbo.[PaymentJob] WHERE dbo.[PaymentJob].JobPostId = " + post.Id; using (SqlDataAdapter adptPayment = new SqlDataAdapter(paymentCommand)) { DataTable tablePayment = new DataTable(); adptPayment.Fill(tablePayment); List <Payment> payments = new List <Payment>(); foreach (DataRow rowPayment in tablePayment.Rows) { payments.Add((Payment)rowPayment["PaymentTypeId"]); } post.PaymentMethod = payments.ToArray(); } } posts.Add(post); } } } if (address != null) { if (distance != null) { JobPostReturnedModel[] jobPosts = posts.ToArray(); posts = new List <JobPostReturnedModel>(); for (int i = 0; i < jobPosts.Length; i++) { int?distanceToWork = DistancesHelper.calculateDistanceBetweenAddresses(address, jobPosts[i].Address); if (distanceToWork != null) { if (distanceToWork <= distance) { jobPosts[i].Range = (int)distanceToWork; posts.Add(jobPosts[i]); } } } } } return(posts); }
/// <summary> /// Método de pesquisa de Anuncio disponível para o cliente com vários filtros /// </summary> /// <param name="categories">Filtro de Categorias</param> /// <param name="address">Filtro da Morada</param> /// <param name="distance">Filtro de distância</param> /// <param name="rating">Filtro de Rating</param> /// <param name="mateId"></param> /// <returns>Retorna a listagem de posts</returns> public List <Publication> GetPublications(string key, Categories[] categories, string address, int?distance) { List <Publication> posts = new List <Publication>(); using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; bool hasQuery = false; string query = " where "; if (categories.Length != 0) { if (hasQuery) { query += "and ("; int lastOne = categories.Length - 1; for (int i = 0; i < lastOne; i++) { query += "Category=" + (int)categories[i] + " or "; } query += "Category=" + (int)categories[lastOne] + " )"; } else { hasQuery = true; int lastOne = categories.Length - 1; query += "("; for (int i = 0; i < lastOne; i++) { query += "Category=" + (int)categories[i] + " or "; } query += "Category=" + (int)categories[lastOne] + ") "; } } if (key != null) { if (hasQuery) { query += "AND Description LIKE '%" + key + "%' or Title LIKE '%" + key + "%' "; } else { query += " Description LIKE '%" + key + "%' or Title LIKE '%" + key + "%' "; } } if (hasQuery || key != null) { cmd.CommandText = "SELECT * FROM Publication " + query + ";"; } else { cmd.CommandText = "SELECT * FROM Publication;"; } using (SqlDataAdapter adpt = new SqlDataAdapter(cmd)) { DataTable table = new DataTable(); adpt.Fill(table); string adr; foreach (DataRow row in table.Rows) { Publication post = new Publication { Id = int.Parse(row["Id"].ToString()), Tittle = row["Title"].ToString(), Description = row["Description"].ToString(), Tradable = (bool)row["Tradable"], Category = (Categories)row["Category"], InitialPrice = (double)row["Price"], UtilizadorId = int.Parse(row["UserId"].ToString()), InstrumentCondition = (Condition)row["Condition"], ImagePath = int.Parse(row["Image"].ToString()), }; adr = row["Localization"].ToString(); string[] adrS = adr.Split(','); Address nAdr = new Address(); nAdr.Street = adrS[0]; nAdr.StreetNumber = Int32.Parse(adrS[1]); nAdr.PostalCode = adrS[2]; nAdr.District = adrS[3]; nAdr.Country = adrS[4]; post.UserAddress = nAdr; posts.Add(post); } } if (address != null) { if (distance != null) { Publication[] publicationPosts = posts.ToArray(); posts = new List <Publication>(); for (int i = 0; i < publicationPosts.Length; i++) { int?distanceToUser = DistancesHelper.calculateDistanceBetweenAddresses(address, publicationPosts[i].UserAddress.ToString()); if (distanceToUser != null) { if (distanceToUser <= distance) { publicationPosts[i].Range = (int)distanceToUser; posts.Add(publicationPosts[i]); } } } } } } return(posts); }
/// <summary> /// Metodo que retorna mates baseado /// nos filtros que entrem como paramentros /// </summary> /// <param name="categories"></param> /// <param name="address"></param> /// <param name="rank"></param> /// <param name="distance"></param> /// <param name="rating"></param> /// <returns></returns> public List <Mate> GetMates(Categories[] categories, string address, Ranks?rank, int?distance, int?rating) { List <Mate> mates = new List <Mate>(); using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; bool hasQuery = false; string query = "WHERE "; if (rating != null) { hasQuery = true; query += " (AverageRating >= " + rating + ")"; } if (categories.Length != 0) { string innerSelect = "dbo.[User].id IN (Select MateId FROM dbo.[CategoriesFromM8] WHERE "; if (hasQuery) { query += "and (" + innerSelect; int lastOne = categories.Length - 1; for (int i = 0; i < lastOne; i++) { query += "CategoryId=" + (int)categories[i] + " or "; } query += "CategoryId=" + (int)categories[lastOne] + " )"; } else { hasQuery = true; int lastOne = categories.Length - 1; query += innerSelect; for (int i = 0; i < lastOne; i++) { query += "CategoryId=" + (int)categories[i] + " or "; } query += "CategoryId=" + (int)categories[lastOne] + " "; } query += ")"; } if (rank != null) { if ((int)rank > 0 && (int)rank < 5) { hasQuery = true; query += " and (RankId >= " + (int)rank + ")"; } } if (hasQuery) { cmd.CommandText = "SELECT dbo.[User].id, dbo.[User].UserName, dbo.[User].Email, dbo.[User].Description, " + "dbo.[User].FirstName, dbo.[User].LastName, dbo.[User].Address, " + "dbo.[User].AverageRating, dbo.[Mate].Range, dbo.[Mate].RankId " + "FROM dbo.[User] " + "INNER JOIN dbo.[Mate] ON dbo.[User].Id = dbo.[Mate].Id " + query + "ORDER BY Id;"; } else { cmd.CommandText = "SELECT dbo.[User].id, dbo.[User].UserName, dbo.[User].Email, dbo.[User].Description, " + "dbo.[User].FirstName, dbo.[User].LastName, dbo.[User].Address, " + "dbo.[User].AverageRating, dbo.[Mate].Range, dbo.[Mate].RankId " + "FROM dbo.[User] " + "INNER JOIN dbo.[Mate] ON dbo.[User].Id = dbo.[Mate].Id " + "ORDER BY Id;"; } using (SqlDataAdapter adpt = new SqlDataAdapter(cmd)) { DataTable table = new DataTable(); adpt.Fill(table); foreach (DataRow row in table.Rows) { Mate mate = new Mate { Id = int.Parse(row["Id"].ToString()), UserName = row["UserName"].ToString(), Email = row["Email"].ToString(), FirstName = row["FirstName"].ToString(), LastName = row["LastName"].ToString(), Address = row["Address"].ToString(), Range = int.Parse(row["Range"].ToString()), Rank = (Ranks)int.Parse(row["RankId"].ToString()), }; if (row["Description"] == DBNull.Value) { mate.Description = null; } else { mate.Description = row["Description"].ToString(); } if (row["AverageRating"] == DBNull.Value) { mate.AverageRating = 0; } else { mate.AverageRating = Convert.ToDouble(row["AverageRating"]); } using (SqlCommand categoryCommand = _connection.Fetch().CreateCommand()) { categoryCommand.CommandType = CommandType.Text; categoryCommand.CommandText = "Select dbo.[CategoriesFromM8].CategoryId FROM dbo.[CategoriesFromM8] WHERE MateId = " + mate.Id; using (SqlDataAdapter adptCategory = new SqlDataAdapter(categoryCommand)) { DataTable tableCategory = new DataTable(); adptCategory.Fill(tableCategory); List <Categories> categoriesList = new List <Categories>(); foreach (DataRow rowCategory in tableCategory.Rows) { categoriesList.Add((Categories)int.Parse(rowCategory["CategoryId"].ToString())); } mate.Categories = categoriesList.ToArray(); } } mates.Add(mate); } } } if (address != null) { if (distance != null) { Mate[] matesArray = mates.ToArray(); mates = new List <Mate>(); for (int i = 0; i < matesArray.Length; i++) { if (DistancesHelper.calculateDistanceBetweenAddresses(address, matesArray[i].Address) <= distance) { mates.Add(matesArray[i]); } } } } return(mates); }