private void AssignTeacherCommand(object sender)
{
Teacher teacher = (Teacher)sender;
if (!teacher.ModuleStudyAction.Contains(CurrentMSA))
{
GlobalContext.context.TeacherSet.Find(teacher.Id).ModuleStudyAction.Add(CurrentMSA);
GlobalContext.context.ModuleStudyActionSet.Find(CurrentMSA.Id).Teacher = teacher;
bool exist = false;
if (teacher.TeacherWorkload != null)
{
foreach (var workload in teacher.TeacherWorkload)
{
if (workload.Year.ToString().Equals(CurrentMSA.Year))
{
workload.Amount += CurrentMSA.Hours;
exist = true;
}
}
if (!exist)
{
TeacherWorkload tw = new TeacherWorkload();
tw.Teacher = teacher;
tw.Year = int.Parse(CurrentMSA.Year);
tw.Amount += CurrentMSA.Hours;
teacher.TeacherWorkload.Add(tw);
}
}
else
{
TeacherWorkload tw = new TeacherWorkload();
tw.Teacher = teacher;
tw.Year = int.Parse(CurrentMSA.Year);
tw.Amount += CurrentMSA.Hours;
teacher.TeacherWorkload.Add(tw);
}
GlobalContext.context.SaveChanges();
}
CloseAction();
}
// GET: StatisticsController
public async Task <ActionResult> IndexAsync()
{
IndexViewModel indexViewModel = new IndexViewModel();
#region TeacherWorkload
List <TeacherWorkload> teacherWorkloads = new List <TeacherWorkload>();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
string workinghours = "SELECT teacherid, SUM(hoursnumber) AS workinghours "
+ "FROM teacherSubject "
+ "GROUP BY teacherid ";
string realhours = "SELECT ts.teacherid, SUM(gs.hoursnumber) AS realhours FROM teacherSubjectGroup tsg "
+ "LEFT JOIN groups g ON g.groupid = tsg.groupid "
+ "LEFT JOIN grades ON grades.gradeid = g.gradeid "
+ "LEFT JOIN teachersubject ts ON ts.teachersubjectid = tsg.teachersubjectid "
+ "LEFT JOIN gradeSubject gs ON gs.gradeid = g.gradeid AND gs.SubjectId = ts.SubjectId "
+ "LEFT JOIN teachers t ON ts.teacherid = t.teacherid "
+ "GROUP BY ts.teacherid ";
string query =
"SELECT TOP(5) CONCAT(surname, ' ', firstname, ' ', patronymic) AS fullname, w.workinghours, r.realhours "
+ "FROM teachers t "
+ $"LEFT JOIN ({workinghours}) AS w ON w.teacherid = t.teacherid "
+ $"LEFT JOIN ({realhours}) AS r ON r.teacherid = t.teacherid "
+ "WHERE w.workinghours != 0 AND r.realhours IS NOT NULL "
+ "ORDER BY CAST(r.realhours AS Float) / w.workinghours desc;";
command.CommandText = query;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new TeacherWorkload {
FullName = reader.GetString(0),
WorkingHoursNumber = reader.GetInt32(1),
RealWorkingHoursNumber = reader.GetInt32(2)
};
teacherWorkloads.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
#endregion
#region SubjectWorkload
List <SubjectWorkload> subjectWorkloads = new List <SubjectWorkload>();
var conn1 = _context.Database.GetDbConnection();
try
{
await conn1.OpenAsync();
using (var command = conn1.CreateCommand())
{
string workinghours = "SELECT subjectid, SUM(hoursnumber) AS tt "
+ "FROM gradeSubject "
+ "GROUP BY subjectid ";
string realhours = "SELECT s.subjectid, COUNT(homeworkid) AS hw FROM homeworks h "
+ "LEFT JOIN teacherSubjectGroup tsg ON tsg.teacherSubjectGroupId = h.teacherSubjectGroupId "
+ "LEFT JOIN teachersubject ts ON ts.teachersubjectid = tsg.teachersubjectid "
+ "LEFT JOIN subjects s ON ts.subjectid = s.subjectid "
+ "GROUP BY s.subjectid ";
string query =
"SELECT TOP(5) subjectname, w.tt, r.hw "
+ "FROM subjects s "
+ $"LEFT JOIN ({workinghours}) AS w ON w.subjectid = s.subjectid "
+ $"LEFT JOIN ({realhours}) AS r ON r.subjectid = s.subjectid "
+ "WHERE w.tt != 0 AND r.hw IS NOT NULL "
+ "ORDER BY CAST(r.hw AS Float) / w.tt desc ;";
command.CommandText = query;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new SubjectWorkload
{
SubjectName = reader.GetString(0),
TimetableHoursNumber = reader.GetInt32(1),
HomeworkHoursNumber = reader.GetInt32(2)
};
subjectWorkloads.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn1.Close();
}
#endregion
#region Turant
List <Turant> turants = new List <Turant>();
var conn2 = _context.Database.GetDbConnection();
try
{
await conn2.OpenAsync();
using (var command = conn2.CreateCommand())
{
string skipping = "SELECT pupilid, COUNT(skippingclassid) AS skippinghours "
+ "FROM skippingclasses "
+ "GROUP BY pupilid ";
string tthours = "SELECT gradeid, SUM(hoursnumber) * 7 AS tthours "
+ "FROM gradesubject "
+ "GROUP BY gradeid ";
string query =
"SELECT TOP(5) CONCAT(firstname, ' ',surname, ' ',patronymic) AS fullname, gradename, skippinghours, tthours "
+ "FROM pupils p "
+ "LEFT JOIN gradesinfo gi ON gi.gradeid = p.gradeid "
+ $"LEFT JOIN ({skipping}) AS w ON w.pupilid = p.pupilid "
+ $"LEFT JOIN ({tthours}) AS r ON r.gradeid = p.gradeid "
+ "WHERE tthours != 0 AND skippinghours IS NOT NULL "
+ "ORDER BY (CAST(skippinghours AS Float) / tthours) desc;";
command.CommandText = query;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new Turant
{
FullName = reader.GetString(0),
GradeName = reader.GetString(1),
SkippingClassesNumber = reader.GetInt32(2),
TimetableHoursNumber = 39 * 5 /*reader.GetInt32(3)*/
};
turants.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn2.Close();
}
#endregion
#region DobPerMonth
List <DobPerMonth> dobPerMonths = new List <DobPerMonth>();
var conn3 = _context.Database.GetDbConnection();
try
{
await conn3.OpenAsync();
using (var command = conn3.CreateCommand())
{
string dobs = " SELECT MONTH(dob) AS monthnumber, COUNT(pupilid) AS dobnumber " +
"FROM pupils " +
"GROUP BY MONTH(dob) ";
string query =
"SELECT number AS monthnumber, FORMAT(DateAdd( month , number, -1 ), 'MMMM', 'en-US') as month, COALESCE(r.dobnumber, 0) AS dobnumber " +
"FROM master..spt_values " +
$"LEFT JOIN({dobs}) AS r ON r.monthnumber = number " +
"WHERE Type = 'P' and number between 1 and 12 " +
"ORDER BY number";
command.CommandText = query;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new DobPerMonth
{
Month = reader.GetString(1),
DobNumber = reader.GetInt32(2)
};
dobPerMonths.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn3.Close();
}
#endregion
indexViewModel.TeacherWorkloads = teacherWorkloads;
indexViewModel.SubjectWorkloads = subjectWorkloads;
indexViewModel.Turants = turants;
indexViewModel.DobPerMonths = dobPerMonths;
return(View(indexViewModel));
}
