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);
        }
예제 #2
0
        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);
        }