public SeatingArrangementFinalDto seatingArrangementFinal(
string roomnumber,
string date,
string session,
Dictionary <string, string> seatandreg,
List <RoomConsolidatedReportDto> roomConsolidatedReport
)
{
SeatingArrangementFinalDto seatingArrangementFinalDto = new SeatingArrangementFinalDto()
{
RoomNumber = roomnumber,
Date = date,
Session = session,
SeatandRegisterNumber = seatandreg,
Report = roomConsolidatedReport
};
return(seatingArrangementFinalDto);
}
public async Task <SeatingArrangements> GetFinalSeatingArrangement(string date, string session)
{
var roomnumbers = await _context.Rooms
.Join(_context.RoomExams, x => x.Id, y => y.RoomId, (x, y) => new { x.Id, x.RoomNumber, y.ExamId })
.Join(_context.Exams, x => x.ExamId, y => y.Id, (x, y) => new { x.RoomNumber, x.Id, y.Date, y.Session })
.Where(x => x.Date == date && x.Session == session).Select(x => x.RoomNumber).Distinct()
.ToListAsync();
List <SeatingArrangementFinalDto> FinalHallAllocationReport = new List <SeatingArrangementFinalDto>();
foreach (var roomnumber in roomnumbers)
{
Console.WriteLine($"RoomNumber : {roomnumber}");
Dictionary <string, string> SeatandRegNumberPair = new Dictionary <string, string>();
using (var command = _context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = $@"select RegisterNumber,SeatNumber from seatings
right join seatingrooms on seatings.Id = seatingrooms.SeatingId
left join rooms r on seatingrooms.RoomId = r.Id AND RoomNumber='{roomnumber}'
left join roomexams r2 on r.Id = r2.RoomId
left join exams e on r2.ExamId = e.Id AND e.Date='{date}' AND Session='{session}'
WHERE RoomNumber='{roomnumber}' and e.Date='{date}' and e.Session='{session}'GROUP BY SeatNumber;";
command.CommandType = CommandType.Text;
await _context.Database.OpenConnectionAsync();
using (var result = await command.ExecuteReaderAsync())
{
while (await result.ReadAsync())
{
var RegNumber = result.GetString(0);
var SeatNumber = result.GetString(1);
SeatandRegNumberPair.Add(SeatNumber, RegNumber);
}
}
}
List <RoomConsolidatedReportDto> roomConsolidatedReport = new List <RoomConsolidatedReportDto>();
using (var command = _context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = $@"select SubjectCode,ShortForm,RoomNumber,count(r2.ExamId) as Count from seatings
join seatingexams s on seatings.Id = s.SeatingId
join exams e on s.ExamId = e.Id
join seatingrooms s2 on s.SeatingId = s2.SeatingId
join rooms r on s2.RoomId = r.Id
join roomexams r2 on s.ExamId = r2.ExamId and s2.RoomId=r2.RoomId
join departments d2 on seatings.DepartmentId = d2.Id
where e.Date='{date}' AND e.Session='{session}' AND RoomNumber='{roomnumber}'
group by DepartmentId,SubjectCode,RoomNumber
order by RoomNumber;";
command.CommandType = CommandType.Text;
await _context.Database.OpenConnectionAsync();
using (var result = await command.ExecuteReaderAsync())
{
while (await result.ReadAsync())
{
var SubjectCode = result.GetString(0);
var ShortForm = result.GetString(1);
var RoomNumber = result.GetString(2);
var Count = result.GetInt32(3);
RoomConsolidatedReportDto roomConsolidatedReportDto = new RoomConsolidatedReportDto()
{
SubjectCode = SubjectCode,
ShortForm = ShortForm,
Count = Count
};
roomConsolidatedReport.Add(roomConsolidatedReportDto);
Console.WriteLine($"SubjectCode : {SubjectCode} Shortform :{ShortForm} RoomNumber :{RoomNumber} Count :{Count}");
}
}
}
SeatingArrangementFinalDto data = _mapper.seatingArrangementFinal(roomnumber, date, session, SeatandRegNumberPair, roomConsolidatedReport);
Console.WriteLine(data.RoomNumber + "ds");
FinalHallAllocationReport.Add(data);
}
var sem_month = await _context.Semester.FirstOrDefaultAsync(x => x.Date == date);
SeatingArrangements seatingArrangements = new SeatingArrangements()
{
SemesterMonth = sem_month.SemesterMonths,
SeatingArrangement = FinalHallAllocationReport
};
return(seatingArrangements);
}