private List <Appointment> GetFreeAppointmentsForDay(TimeInterval time, RecommendationDTO appointmentsDay)
{
List <Appointment> freeAppointments = new List <Appointment>();
List <Appointment> takenAppointments = appointmentsDay.TakenAppointments;
TimeIterator timeIterator = GetTimeIterator(time, appointmentsDay.DoctorShift);
// iterate through every taken appointment
for (int i = 0; i < takenAppointments.Count(); i++)
{
Appointment takenAp = takenAppointments[i];
freeAppointments.AddRange(GetFreeAppointmentsUntilTaken(timeIterator, takenAp, appointmentsDay.Doctor));
// skip taken appointment
timeIterator.SkipInterval(takenAp.TimeInterval);
// if taken appointment is the last one for given day, fill the rest of the time with free appointments
if (i == takenAppointments.Count() - 1) //last one
{
freeAppointments.AddRange(GetFreeAppointmentsRestOfTheDay(timeIterator, appointmentsDay.Doctor));
}
}
// if no taken appointments exist for given day, fill the list with free appointments for that day
if (takenAppointments.Count() == 0)
{
freeAppointments.AddRange(GetFreeAppointmentsRestOfTheDay(timeIterator, appointmentsDay.Doctor));
}
return(freeAppointments);
}
public HttpResponseMessage EditReccomendation([FromBody] RecommendationDTO recommendationDTO)
{
var recommendation = DTOFactory.Parse(recommendationDTO);
UnitOfWork.RecommendationRepository.Update(recommendation);
return(Request.CreateResponse(HttpStatusCode.OK, DTOFactory.Create(recommendation)));
}
public static Recommendation RecommendationtoDal(RecommendationDTO com)
{
return(new Recommendation
{
RecommendationCode = com.RecommendationCode,
UserCode = com.UserCode,
FreeDescription = com.FreeDescription
});
}
//יצירת המלצה חדשה
public static bool AddRecommendation(RecommendationDTO com)
{
using (DAL.MoveilEntities db = new DAL.MoveilEntities())
{
db.Recommendations.Add(CONVERTERES.RecommendationConverters.RecommendationtoDal(com));
db.SaveChanges();
return(true);
}
}
public static RecommendationDTO GetRecommendationDTO(Recommendation Recommendation)
{
RecommendationDTO RecommendationDTO = new RecommendationDTO();
RecommendationDTO.Score = Recommendation.Score;
RecommendationDTO.RecommendationId = Recommendation.RecommendationId;
RecommendationDTO.TeacherId = Recommendation.TeacherId;
RecommendationDTO.RecommendationContents = Recommendation.RecommendationContents;
RecommendationDTO.SubjectId = Recommendation.SubjectId;
return(RecommendationDTO);
}
public static void Setup(TestContext context)
{
RecommendationDTO recommendationDto1 = new RecommendationDTO
{
RecommendationId = 1,
Note = "Esta bien",
Mark = 10,
SupplierId = 1,
NameSupllier = "Juan",
LastNameSupllier = "Ortega",
EmailSupllier = "*****@*****.**",
UsuarioId = 1,
UserName = "******"
};
RecommendationDTO recommendationDto2 = new RecommendationDTO
{
RecommendationId = 2,
Note = "Esta bien",
Mark = 8,
SupplierId = 2,
NameSupllier = "Luis",
LastNameSupllier = "Gonzales",
EmailSupllier = "*****@*****.**",
UsuarioId = 2,
UserName = "******"
};
listaRecommendationsDTO.Add(recommendationDto1);
listaRecommendationsDTO.Add(recommendationDto2);
Recommendation recommendation1 = new Recommendation
{
RecommendationId = 1,
UsuarioId = 1,
SupplierId = 1,
Note = "Esta bien",
Mark = 10
};
Recommendation recommendation2 = new Recommendation
{
RecommendationId = 2,
UsuarioId = 2,
SupplierId = 2,
Note = "Esta bien",
Mark = 8
};
listaRecommendations.Add(recommendation1);
listaRecommendations.Add(recommendation2);
}
public IHttpActionResult addRecommendation(RecommendationDTO rec)
{
return(Ok(BL.RecommendationBL.AddRecommendation(rec)));
}
public Envelope <RecommendationDTO> GetRecommendationsByUserID(int userID, int pageNumber, int?pageMaxSize)
{
// Check if user exists
if (!db.Users.Where(u => u.ID == userID).Any())
{
throw new NotFoundException(userNotFoundMessage);
}
var maxSize = (pageMaxSize.HasValue ? pageMaxSize.Value : defaultPageSize);
var totalNumberOfItems = 0;
var pageCount = 0;
// Doing left outer join with grouping and aggregation in LINQ is a pain.
// I tried for hours and decided to do it the poor mans way, raw SQL style.
var resultList = new List <RecommendationDTO>();
// Get the DB connection
var conn = db.Database.GetDbConnection();
try
{
// Open connection and let the fun begin
conn.Open();
using (var command = conn.CreateCommand())
{
// The SQL query to get the total number of books being recommended
var countQuery = String.Format("SELECT COUNT(*) "
+ "FROM "
+ "( "
+ "SELECT 1 "
+ "FROM Books b "
+ "LEFT JOIN Reviews r ON b.ID = r.BookID "
+ "WHERE NOT EXISTS "
+ "( "
+ "SELECT 1 "
+ "FROM Loans l "
+ "WHERE l.BookID = b.ID AND l.UserID = {0} "
+ ") "
+ "AND NOT EXISTS "
+ "( "
+ "SELECT 1 "
+ "FROM Reviews r2 "
+ "WHERE r2.BookID = b.ID AND r2.UserID = {0} "
+ ") "
+ "GROUP BY b.ID "
+ ") ",
userID);
// Set and execute the query
command.CommandText = countQuery;
var reader = command.ExecuteReader();
// Get the count
if (reader.HasRows)
{
reader.Read();
totalNumberOfItems = reader.GetInt32(0);
}
reader.Dispose();
// Calculate pagination
pageCount = (int)Math.Ceiling(totalNumberOfItems / (double)maxSize);
var skip = (pageNumber - 1) * maxSize;
// The SQL query for the recommendations
var recommendationsQuery = String.Format("SELECT b.ID, b.Title, b.Author, b.PublishDate, b.ISBN, avg(r.Rating) "
+ "FROM Books b "
+ "LEFT JOIN Reviews r ON b.ID = r.BookID "
+ "WHERE NOT EXISTS "
+ "( "
+ "SELECT 1 "
+ "FROM Loans l "
+ "WHERE l.BookID = b.ID AND l.UserID = {0} "
+ ") "
+ "AND NOT EXISTS "
+ "( "
+ "SELECT 1 "
+ "FROM Reviews r2 "
+ "WHERE r2.BookID = b.ID AND r2.UserID = {0} "
+ ") "
+ "GROUP BY b.ID "
+ "ORDER BY avg(r.Rating) DESC, b.Title ASC "
+ "LIMIT {1}, {2}",
userID, skip, maxSize);
// Set and execute the query
command.CommandText = recommendationsQuery;
reader = command.ExecuteReader();
// Read over and add the rows
if (reader.HasRows)
{
while (reader.Read())
{
var row = new RecommendationDTO
{
Book = new BookDTO
{
ID = reader.GetInt32(0),
Title = reader.GetString(1),
Author = reader.GetString(2),
PublishDate = reader.GetDateTime(3),
ISBN = reader.GetString(4),
}
};
if (!reader.IsDBNull(5))
{
row.AverageRating = reader.GetDouble(5);
}
resultList.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(new Envelope <RecommendationDTO>
{
Items = resultList,
Paging = new Paging
{
PageCount = pageCount,
PageSize = resultList.Count,
PageMaxSize = maxSize,
PageNumber = pageNumber,
TotalNumberOfItems = totalNumberOfItems,
}
});
}
