public ActionResult getSpecificUserDetails(string reviewer, string review, int rating, string email, Professional_Details_ViewModel vm)
{
string insertQuery = "INSERT INTO dbo.UserReviews(reviewerName,reviewText,rating,professionalEmail) VALUES(@reviewer,@review,@rating,@email)";
using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
SqlParameter param1 = new SqlParameter();
SqlParameter param2 = new SqlParameter();
SqlParameter param3 = new SqlParameter();
SqlParameter param4 = new SqlParameter();
param1.ParameterName = "@reviewer";
param2.ParameterName = "@review";
param3.ParameterName = "@rating";
param4.ParameterName = "@email";
param1.Value = reviewer;
param2.Value = review;
param3.Value = rating;
param4.Value = email;
connection.Open();
SqlCommand command = new SqlCommand(insertQuery, connection);
command.Parameters.Add(param1);
command.Parameters.Add(param2);
command.Parameters.Add(param3);
command.Parameters.Add(param4);
command.ExecuteNonQuery();
connection.Close();
}
//vm.Reviews.Add(new Review { reviewerName = reviewer, reviewText = review, rating = rating });
//return View(vm);
return(RedirectToAction("Professional_Roles"));
}
public ActionResult getSpecificUserDetails(string id, string uId, Professional_Details_ViewModel vmOld)
{
List <Review> reviews = new List <Review>();
string queryString = "SELECT reviewId, reviewText, reviewerName, rating, professionalEmail FROM dbo.UserReviews WHERE professionalEmail=@id";
using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = uId;
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.Add(param);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
reviews.Add(new Review()
{
ID = (int)reader[0], reviewText = reader[1].ToString(), reviewerName = reader[2].ToString(), rating = (int)reader[3], professionalId = reader[4].ToString()
});
}
}
connection.Close();
}
string oneStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 1 AND professionalEmail=@id";
int oneStarQueries;
using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = uId;
using (SqlCommand command = new SqlCommand(oneStarQuery, connection))
{
command.Parameters.Add(param);
connection.Open();
oneStarQueries = (int)command.ExecuteScalar();
}
connection.Close();
}
string twoStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 2 AND professionalEmail=@id";
int twoStarQueries;
using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = uId;
using (SqlCommand command = new SqlCommand(twoStarQuery, connection))
{
command.Parameters.Add(param);
connection.Open();
twoStarQueries = (int)command.ExecuteScalar();
}
connection.Close();
}
string threeStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 3 AND professionalEmail=@id";
int threeStarQueries;
using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = uId;
using (SqlCommand command = new SqlCommand(threeStarQuery, connection))
{
command.Parameters.Add(param);
connection.Open();
threeStarQueries = (int)command.ExecuteScalar();
}
connection.Close();
}
string fourStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 4 AND professionalEmail=@id";
int fourStarQueries;
using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = uId;
using (SqlCommand command = new SqlCommand(fourStarQuery, connection))
{
command.Parameters.Add(param);
connection.Open();
fourStarQueries = (int)command.ExecuteScalar();
}
connection.Close();
}
string fiveStarQuery = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE rating = 5 AND professionalEmail=@id";
int fiveStarQueries;
using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = uId;
using (SqlCommand command = new SqlCommand(fiveStarQuery, connection))
{
command.Parameters.Add(param);
connection.Open();
fiveStarQueries = (int)command.ExecuteScalar();
}
connection.Close();
}
string totalRating = "SELECT COUNT(*) FROM [dbo].[UserReviews] WHERE professionalEmail=@id";
int totalQueries;
using (SqlConnection connection = new SqlConnection("Server=tcp:se1-ratemyprofessional.database.windows.net,1433;Initial Catalog=Identity;Persist Security Info=False;User ID=rmpadmin;Password=TeamOne1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@id";
param.Value = uId;
using (SqlCommand command = new SqlCommand(totalRating, connection))
{
command.Parameters.Add(param);
connection.Open();
totalQueries = (int)command.ExecuteScalar();
}
connection.Close();
}
decimal averageRating = 0;
if (totalQueries > 0)
{
averageRating = (decimal)(5 * fiveStarQueries + 4 * fourStarQueries + 3 * threeStarQueries + 2 * twoStarQueries + 1 * oneStarQueries) / totalQueries;
}
var pro = _dbcontext.Users.Where(p => p.Email == id).FirstOrDefault();
if (pro == null)
{
return(new NotFoundResult());
}
Professional_Details_ViewModel vm = new Professional_Details_ViewModel()
{
professionalId = pro.Id,
Email = pro.Email,
FirstName = pro.FirstName,
LastName = pro.LastName,
Address = pro.Address,
City = pro.City,
State = pro.State,
Profession = pro.Profession,
Rate = pro.Rate,
Company = pro.Company,
Reviews = reviews,
Rating = averageRating
};
ViewBag.vm = vm;
return(View(vm));
}
