public ActionResult New(int id) { var proposal = _context.Proposals.SingleOrDefault(m => m.Id == id); if (proposal == null) { return(HttpNotFound()); } ProgrammeRationale pr = null; if (proposal.ProgrammeRationaleId != null) { pr = _context.ProgrammeRationales.SingleOrDefault(m => m.Id == proposal.ProgrammeRationaleId); } if (pr.TentativePsId != null) { return(HttpNotFound()); } var allUnits = _context.Database.SqlQuery <Ref_Unit>("Select * from Ref_Unit where DepartmentId In(Select distinct DepartmentId From dbo.Department_General WHERE GeneralId = " + proposal.GeneralId + ")").ToList(); //var departmentIds = _context.Database.SqlQuery<int>("Select distinct DepartmentId From dbo.Department_General WHERE GeneralId = " + proposal.GeneralId).ToList(); var viewModel = new TentativePsFormViewModel { Proposal = proposal, AllUnits = allUnits, TentativePs = new TentativeP(), }; return(View("Form", viewModel)); }
public ActionResult Edit(int id) { var proposal = _context.Proposals.SingleOrDefault(c => c.Id == id); if (proposal == null) { return(HttpNotFound()); } if (proposal.ProgrammeRationaleId == null) { return(HttpNotFound()); } var pr = _context.ProgrammeRationales.SingleOrDefault(c => c.Id == proposal.ProgrammeRationaleId); var tentative = _context.TentativePs.SingleOrDefault(c => c.Id == pr.TentativePsId); if (tentative == null) { return(HttpNotFound()); } string selectedSubQuery = "SELECT UnitId FROM Year_Unit JOIN Year ON dbo.Year.Id = dbo.Year_Unit.YearId WHERE dbo.Year.TentativePsId = " + tentative.Id; //string selectedQuery = "SELECT * FROM Ref_Unit WHERE Id in (" + selectedSubQuery + ")"; //var selectedUnits = _context.Database.SqlQuery<Ref_Unit>(selectedQuery).ToList(); string selectedQuery = "SELECT * FROM Year_Unit WHERE UnitId in (" + selectedSubQuery + ") order by YearId"; var selectedUnits = _context.Database.SqlQuery <Year_Unit>(selectedQuery).ToList(); string unselectedQuery = "SELECT * FROM Ref_Unit WHERE Id not in (" + selectedSubQuery + ") AND DepartmentId IN(Select distinct DepartmentId From dbo.Department_General WHERE GeneralId = " + proposal.GeneralId + ")"; var unselectedUnits = _context.Database.SqlQuery <Ref_Unit>(unselectedQuery).ToList(); var viewModel = new TentativePsFormViewModel { Proposal = proposal, TentativePs = tentative, SelectedUnits = selectedUnits, UnselectedUnits = unselectedUnits }; return(View("Form", viewModel)); }
public ActionResult Save(TentativePsFormViewModel vm) { var tentative = vm.TentativePs; var proposal = _context.Proposals.SingleOrDefault(m => m.Id == vm.Proposal.Id); if (!ModelState.IsValid) { return(View("Form", tentative)); } if (tentative.Id == 0) { _context.TentativePs.Add(tentative); var pr = _context.ProgrammeRationales.SingleOrDefault(m => m.Id == proposal.Id); pr.TentativePsId = tentative.Id; pr.TentativeP = tentative; var addedYears = new List <int>(); foreach (string name in Request.Form.AllKeys) { try { int n = Convert.ToInt32(name); int yearNo = Convert.ToInt32(Request["year_" + name]); if (!addedYears.Contains(yearNo)) { addedYears.Add(yearNo); Year year = new Year(); year.TentativePsId = tentative.Id; year.TentativeP = tentative; year.YearNo = yearNo; year.TotalEcts = 0; _context.Years.Add(year); _context.SaveChanges(); } Year yearInDb = _context.Years.SingleOrDefault(m => m.TentativePsId == tentative.Id && m.YearNo == yearNo); Year_Unit yu = new Year_Unit(); yu.YearId = yearInDb.Id; yu.Year = _context.Years.SingleOrDefault(m => m.Id == yearInDb.Id); yu.UnitId = n; yu.Coe = Convert.ToInt32(Request["coe_" + name]); yu.Ects = Convert.ToInt32(Request["credits_" + name]); yu.Period = Convert.ToInt32(Request["period_" + name]); yu.Lecturer = Request["lecturer_" + name]; _context.Year_Unit.Add(yu); _context.SaveChanges(); //return Content(name + " " + Request["lecturer_" + name] + " " + Request["credits_" + name]); } catch (Exception e) { } } } else { //save all yearno, ects pairs to dictionary SortedDictionary <int, int> pairs = new SortedDictionary <int, int>(); string query = "Select YearNo From dbo.Year Where TentativePsId = " + tentative.Id; var yearNos = _context.Database.SqlQuery <int>(query).ToList(); foreach (int yearNo in yearNos) { string q = "Select TotalEcts From dbo.Year Where YearNo = " + yearNo + " and TentativePsId = " + tentative.Id; int ects = _context.Database.SqlQuery <int>(q).Single(); pairs.Add(yearNo, ects); } // remove exsting Year_Units and Years where TentativePsId == tentative.Id string queryYu = "DELETE FROM dbo.Year_Unit WHERE YearId in (Select Id From dbo.Year Where TentativePsId = " + tentative.Id + "); "; string queryY = "DELETE FROM dbo.Year Where TentativePsId = " + tentative.Id; _context.Database.ExecuteSqlCommand(queryYu); _context.Database.ExecuteSqlCommand(queryY); var addedYears = new List <int>(); foreach (string name in Request.Form.AllKeys) { try { int n = Convert.ToInt32(name); int yearNo = Convert.ToInt32(Request["year_" + name]); if (!addedYears.Contains(yearNo)) { addedYears.Add(yearNo); Year year = new Year(); year.TentativePsId = tentative.Id; year.YearNo = yearNo; if (pairs.ContainsKey(yearNo)) { year.TotalEcts = pairs[yearNo]; } else { year.TotalEcts = 0; } _context.Years.Add(year); _context.SaveChanges(); } Year yearInDb = _context.Years.SingleOrDefault(m => m.TentativePsId == tentative.Id && m.YearNo == yearNo); Year_Unit yu = new Year_Unit(); yu.YearId = yearInDb.Id; yu.Year = _context.Years.SingleOrDefault(m => m.Id == yearInDb.Id); yu.UnitId = n; yu.Coe = Convert.ToInt32(Request["coe_" + name]); yu.Ects = Convert.ToInt32(Request["credits_" + name]); yu.Period = Convert.ToInt32(Request["period_" + name]); yu.Lecturer = Request["lecturer_" + name]; _context.Year_Unit.Add(yu); _context.SaveChanges(); } catch (Exception e) { } } } _context.SaveChanges(); var jump = Request["jump"]; switch (jump) { case "0": { // Save pressed -> return form return(RedirectToAction("Edit", "TentativePs", new { id = proposal.Id })); } case "-1": { // Previous pressed -> return form return(RedirectToAction("Edit", "ProgrammeStudy", new { id = proposal.Id })); } case "1": { // Next pressed -> return next page return(RedirectToAction("YearTotals", "TentativePs", new { id = proposal.Id })); } case "A": { // A pressed -> go to Section A return(RedirectToAction("Edit", "General", new { id = proposal.Id })); } case "B": { // B pressed -> go to Section B return(RedirectToAction("Edit", "Rationale", new { id = proposal.Id })); } case "C": { // C pressed -> go to Section C return(RedirectToAction("Jump", "ExternalReview", new { id = proposal.Id })); } default: { return(RedirectToAction("Index", "Proposal")); } } }