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);
}
