private void LoadMarks() { if (!MainClass.IsPasha()) { return; } try { wtc = new Watch(2); wtc.Show(); marksCount = 0; if (ExamId == null) { foreach (KeyValuePair <string, string> ex in cbExam.Items) { int exId; if (int.TryParse(ex.Key, out exId)) { SetMarksForExam(exId); } } } else { SetMarksForExam(ExamId); } } catch (Exception ex) { WinFormsServ.Error("Ошибка загрузки оценок " + ex.Message); } finally { wtc.Close(); MessageBox.Show(string.Format("Зачтено {0} оценок", marksCount)); } }
private void FillGrid() { int num_pp = 0; int?iFacultyId = GetFacultyId(); int?iStudyFormId = GetStudyFormId(); int?iStudyBasisId = GetStudyBasisId(); dgvData.Rows.Clear(); int watch_count = GetWatchCount(); Watch wc = new Watch(watch_count + watch_count / 10); //+10% wc.Show(); int iTotalPlanBudzh = 0; int iTotalPlanPlatn = 0; int iTotalBudzh = 0; int iTotalPlatn = 0; using (PriemEntities context = new PriemEntities()) { var q_lp = from ent in context.qEntry where ent.StudyLevelGroupId == iStudyLevelGroupId select new { ent.FacultyId, ent.StudyFormId, ent.StudyBasisId, ent.LicenseProgramId, ent.LicenseProgramCode, ent.LicenseProgramName }; if (iFacultyId != null) { q_lp = q_lp.Where(x => x.FacultyId == iFacultyId); } if (iStudyFormId != null) { q_lp = q_lp.Where(x => x.StudyFormId == iStudyFormId); } //if (iStudyBasisId != null) // q_lp = q_lp.Where(x => x.StudyBasisId == iStudyBasisId); var LicensePrograms = q_lp.Select(x => new { x.LicenseProgramId, x.LicenseProgramCode, x.LicenseProgramName }).Distinct().OrderBy(x => x.LicenseProgramCode); foreach (var lProgram in LicensePrograms) { string query = string.Format(@" SELECT StudyBasisId, COUNT(Id) AS CNT FROM ed.extAbit WHERE LicenseProgramId=@LicenseProgramId AND (convert(date, DocInsertDate)<=@Date) AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : ""); DataTable tbl = MainClass.Bdc.GetDataSet(query, new SortedList <string, object>() { { "@LicenseProgramId", lProgram.LicenseProgramId }, { "@Date", dtpDate.Value.Date }, { "@SLGId", iStudyLevelGroupId }, { "@FacultyId", iFacultyId ?? 0 }, { "@StudyFormId", iStudyFormId } }).Tables[0]; int sum_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int>("CNT")).DefaultIfEmpty(0).First(); int sum_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int>("CNT")).DefaultIfEmpty(0).First(); int?kcp_b = 0; //(from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 1 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First(); int?kcp_p = 0; //(from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 2 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First(); query = string.Format(@"SELECT StudyBasisId, SUM(KCP) AS KCP FROM ed.qEntry WHERE LicenseProgramId=@LicenseProgramId AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : ""); tbl = MainClass.Bdc.GetDataSet(query, new SortedList <string, object>() { { "@LicenseProgramId", lProgram.LicenseProgramId }, { "@Date", dtpDate.Value.Date }, { "@SLGId", iStudyLevelGroupId }, { "@FacultyId", iFacultyId ?? 0 }, { "@StudyFormId", iStudyFormId } }).Tables[0]; kcp_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First(); kcp_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First(); //if (kcp_b == 0 || kcp_p == 0) //{ //} float conc_b = kcp_b == null ? 0f : (sum_b == 0 ? 0f : (float)sum_b / (float)kcp_b.Value); float conc_p = kcp_p == null ? 0f : (sum_p == 0 ? 0f : (float)sum_p / (float)kcp_p.Value); AddRow(new StatRow { Num = (++num_pp).ToString(), Name = lProgram.LicenseProgramName, Code = lProgram.LicenseProgramCode, PlanB = kcp_b.HasValue ? kcp_b.ToString() : "0", PlanP = kcp_p.HasValue ? kcp_p.ToString() : "0", SumB = sum_b.ToString(), SumP = sum_p.ToString(), ConcB = kcp_b == 0 ? "!КЦ" : Math.Round(conc_b, 2).ToString(), ConcP = kcp_p == 0 ? "!КЦ" : Math.Round(conc_p, 2).ToString() }); iTotalBudzh += sum_b; iTotalPlatn += sum_p; iTotalPlanBudzh += kcp_b ?? 0; iTotalPlanPlatn += kcp_p ?? 0; var q_op = (from ent in context.qEntry where ent.LicenseProgramId == lProgram.LicenseProgramId && ent.StudyLevelGroupId == iStudyLevelGroupId select new { ent.FacultyId, ent.StudyFormId, ent.StudyBasisId, ent.ObrazProgramId, ent.ObrazProgramCrypt, ent.ObrazProgramName }); if (iFacultyId != null) { q_op = q_op.Where(x => x.FacultyId == iFacultyId); } if (iStudyFormId != null) { q_op = q_op.Where(x => x.StudyFormId == iStudyFormId); } //if (iStudyBasisId != null) // q_op = q_op.Where(x => x.StudyBasisId == iStudyBasisId); var ObrazPrograms = q_op.Select(x => new { x.ObrazProgramId, x.ObrazProgramName, x.ObrazProgramCrypt }).Distinct().OrderBy(x => x.ObrazProgramCrypt); foreach (var oProgram in ObrazPrograms) { query = string.Format(@"SELECT StudyBasisId, COUNT(Id) AS CNT FROM ed.extAbit WHERE LicenseProgramId=@LicenseProgramId AND ObrazProgramId=@ObrazProgramId AND (convert(date, DocInsertDate)<=@Date) AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : ""); tbl = MainClass.Bdc.GetDataSet(query, new SortedList <string, object>() { { "@LicenseProgramId", lProgram.LicenseProgramId }, { "@ObrazProgramId", oProgram.ObrazProgramId }, { "@Date", dtpDate.Value.Date }, { "@SLGId", iStudyLevelGroupId }, { "@FacultyId", iFacultyId ?? 0 }, { "@StudyFormId", iStudyFormId } }).Tables[0]; sum_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int>("CNT")).DefaultIfEmpty(0).First(); sum_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int>("CNT")).DefaultIfEmpty(0).First(); //kcp_b = (from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 1 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First(); //kcp_p = (from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 2 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First(); query = string.Format(@"SELECT StudyBasisId, SUM(KCP) AS KCP FROM ed.qEntry WHERE LicenseProgramId=@LicenseProgramId AND ObrazProgramId=@ObrazProgramId AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : ""); tbl = MainClass.Bdc.GetDataSet(query, new SortedList <string, object>() { { "@LicenseProgramId", lProgram.LicenseProgramId }, { "@ObrazProgramId", oProgram.ObrazProgramId }, { "@Date", dtpDate.Value.Date }, { "@SLGId", iStudyLevelGroupId }, { "@FacultyId", iFacultyId ?? 0 }, { "@StudyFormId", iStudyFormId } }).Tables[0]; kcp_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First(); kcp_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First(); //if (kcp_b == 0 || kcp_p == 0) //{ //} conc_b = kcp_b == null ? 0f : (sum_b == 0 ? 0f : (float)sum_b / (float)kcp_b.Value); conc_p = kcp_p == null ? 0f : (sum_p == 0 ? 0f : (float)sum_p / (float)kcp_p.Value); AddRow(new StatRow { Num = (++num_pp).ToString(), Name = " " + oProgram.ObrazProgramName, Code = "",//oProgram.ObrazProgramCrypt, PlanB = kcp_b.HasValue ? kcp_b.ToString() : "0", PlanP = kcp_p.HasValue ? kcp_p.ToString() : "0", SumB = sum_b.ToString(), SumP = sum_p.ToString(), ConcB = kcp_b == 0 ? "!КЦ" : Math.Round(conc_b, 2).ToString(), ConcP = kcp_p == 0 ? "!КЦ" : Math.Round(conc_p, 2).ToString() }); var q_prof = (from ent in context.qEntry where ent.LicenseProgramId == lProgram.LicenseProgramId && ent.ObrazProgramId == oProgram.ObrazProgramId && ent.StudyLevelGroupId == iStudyLevelGroupId && ent.ProfileId != null select new { ent.FacultyId, ent.StudyFormId, ent.StudyBasisId, ent.ProfileId, ent.ProfileName, }); if (iFacultyId != null) { q_prof = q_prof.Where(x => x.FacultyId == iFacultyId); } if (iStudyFormId != null) { q_prof = q_prof.Where(x => x.StudyFormId == iStudyFormId); } //if (iStudyBasisId != null) // q_prof = q_prof.Where(x => x.StudyBasisId == iStudyBasisId); var Profiles = q_prof.Select(x => new { x.ProfileId, x.ProfileName }).Distinct().OrderBy(x => x.ProfileName); foreach (var prof in Profiles) { query = string.Format(@"SELECT StudyBasisId, COUNT(Id) AS CNT FROM ed.extAbit WHERE LicenseProgramId=@LicenseProgramId AND ObrazProgramId=@ObrazProgramId AND ProfileId=@ProfileId AND (convert(date, DocInsertDate)<=@Date) AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : ""); tbl = MainClass.Bdc.GetDataSet(query, new SortedList <string, object>() { { "@LicenseProgramId", lProgram.LicenseProgramId }, { "@ObrazProgramId", oProgram.ObrazProgramId }, { "@ProfileId", prof.ProfileId }, { "@Date", dtpDate.Value.Date }, { "@SLGId", iStudyLevelGroupId }, { "@FacultyId", iFacultyId ?? 0 }, { "@StudyFormId", iStudyFormId } }).Tables[0]; sum_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int>("CNT")).DefaultIfEmpty(0).First(); sum_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int>("CNT")).DefaultIfEmpty(0).First(); //kcp_b = (from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 1 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First(); //kcp_p = (from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 2 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First(); query = string.Format(@"SELECT StudyBasisId, SUM(KCP) AS KCP FROM ed.qEntry WHERE LicenseProgramId=@LicenseProgramId AND ObrazProgramId=@ObrazProgramId AND ProfileId=@ProfileId AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : ""); tbl = MainClass.Bdc.GetDataSet(query, new SortedList <string, object>() { { "@LicenseProgramId", lProgram.LicenseProgramId }, { "@ObrazProgramId", oProgram.ObrazProgramId }, { "@ProfileId", prof.ProfileId }, { "@Date", dtpDate.Value.Date }, { "@SLGId", iStudyLevelGroupId }, { "@FacultyId", iFacultyId ?? 0 }, { "@StudyFormId", iStudyFormId } }).Tables[0]; kcp_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First(); kcp_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First(); conc_b = kcp_b == null ? 0f : (sum_b == 0 ? 0f : (float)sum_b / (float)kcp_b.Value); conc_p = kcp_p == null ? 0f : (sum_p == 0 ? 0f : (float)sum_p / (float)kcp_p.Value); AddRow(new StatRow { Num = (++num_pp).ToString(), Name = " " + prof.ProfileName, Code = "",//oProgram.ObrazProgramCrypt, PlanB = kcp_b.HasValue ? kcp_b.ToString() : "0", PlanP = kcp_p.HasValue ? kcp_p.ToString() : "0", SumB = sum_b.ToString(), SumP = sum_p.ToString(), ConcB = kcp_b == 0 ? "!КЦ" : Math.Round(conc_b, 2).ToString(), ConcP = kcp_p == 0 ? "!КЦ" : Math.Round(conc_p, 2).ToString() }); wc.PerformStep(); } wc.PerformStep(); } wc.PerformStep(); } AddRow(new StatRow() { Num = "", Name = "", Code = "Всего", PlanB = iTotalPlanBudzh.ToString(), PlanP = iTotalPlanPlatn.ToString(), SumB = iTotalBudzh.ToString(), SumP = iTotalPlatn.ToString(), ConcB = "-", ConcP = "-" }); wc.Close(); } }
private void FillGrid() { using (PriemEntities context = new PriemEntities()) { Watch wc = new Watch(300); wc.Show(); var dates = (from ab in context.qAbitAll where ab.DocInsertDate != null && ab.DocInsertDate > dtpStart.Value && ab.DocInsertDate <= dtpEnd.Value && ab.StudyLevelGroupId == StudyLevelGroupId && (FacultyId.HasValue ? (ab.FacultyId == FacultyId.Value) : (true)) select ab.DocInsertDate).ToList().Select(x => x.Date).Distinct().OrderBy(x => x); string query = string.Format(@"SELECT DISTINCT LicenseProgramId, LicenseProgramCode + ' ' + LicenseProgramName AS Profession, ObrazProgramId, ObrazProgramCrypt + ' ' + ObrazProgramName AS ObrazProgram, ProfileId, ProfileName, convert(date, DocInsertDate) AS Date, COUNT(extAbit.Id) AS CNT FROM ed.extAbit INNER JOIN ed.Person ON Person.Id = extAbit.PersonId WHERE StudyLevelGroupId='{0}' AND DocInsertDate IS NOT NULL AND convert(date, DocInsertDate)>=@DateStart AND convert(date, DocInsertDate)<=@DateEnd {1} {2} {3} GROUP BY LicenseProgramId, LicenseProgramCode, LicenseProgramName, ObrazProgramId, ObrazProgramCrypt, ObrazProgramName, ProfileId, ProfileName, convert(date, DocInsertDate)", StudyLevelGroupId.ToString(), FacultyId == null ? "" : " AND FacultyId='" + FacultyId.ToString() + "' ", StudyBasisId == null ? "" : " AND StudyBasisId='" + StudyBasisId.ToString() + "' ", RegionId.HasValue ? " AND Person.RegionId='" + RegionId.Value.ToString() + "' " : ""); System.Data.DataTable tblStatRaw = MainClass.Bdc.GetDataSet(query, new SortedList <string, object>() { { "@DateStart", dtpStart.Value }, { "@DateEnd", dtpEnd.Value } }).Tables[0]; query = string.Format(@"SELECT LicenseProgramId, ObrazProgramId, ProfileId, SUM(KCP) AS KCP FROM ed.qEntry WHERE StudyLevelGroupId='{0}' {1} {2} GROUP BY LicenseProgramId, ObrazProgramId, ProfileId", StudyLevelGroupId.ToString(), FacultyId == null ? "" : " AND FacultyId='" + FacultyId.ToString() + "' ", StudyBasisId == null ? "" : " AND StudyBasisId='" + StudyBasisId.ToString() + "' "); System.Data.DataTable tblKC = MainClass.Bdc.GetDataSet(query).Tables[0]; query = string.Format(@"SELECT LicenseProgramId, ObrazProgramId, ProfileId, COUNT(extAbit.Id) AS CNT FROM ed.extAbit INNER JOIN ed.Person ON Person.Id = extAbit.PersonId WHERE StudyLevelGroupId='{0}' {1} {2} {3} GROUP BY LicenseProgramId, ObrazProgramId, ProfileId", StudyLevelGroupId.ToString(), FacultyId == null ? "" : " AND FacultyId='" + FacultyId.ToString() + "' ", StudyBasisId == null ? "" : " AND StudyBasisId='" + StudyBasisId.ToString() + "' ", RegionId.HasValue ? " AND Person.RegionId='" + RegionId.Value.ToString() + "' " : ""); System.Data.DataTable tblSumAbit = MainClass.Bdc.GetDataSet(query).Tables[0]; var SumAbit = from DataRow rw in tblSumAbit.Rows select new { LicenseProgramId = rw.Field <int>("LicenseProgramId"), ObrazProgramId = rw.Field <int>("ObrazProgramId"), ProfileId = rw.Field <Guid?>("ProfileId"), CNT = rw.Field <int>("CNT") }; var KCP_Data = from DataRow rw in tblKC.Rows select new { LicenseProgramId = rw.Field <int>("LicenseProgramId"), ObrazProgramId = rw.Field <int>("ObrazProgramId"), ProfileId = rw.Field <Guid?>("ProfileId"), KCP = rw.Field <int>("KCP") }; var _dataFULL = (from DataRow rw in tblStatRaw.Rows select new { LicenseProgramId = rw.Field <int>("LicenseProgramId"), LicenseProgramName = rw.Field <string>("Profession"), ObrazProgramId = rw.Field <int>("ObrazProgramId"), ObrazProgramName = rw.Field <string>("ObrazProgram"), ProfileId = string.IsNullOrEmpty(rw["ProfileId"].ToString()) ? (Guid?)null : rw.Field <Guid?>("ProfileId"), ProfileName = rw.Field <string>("ProfileName"), Date = rw.Field <DateTime>("Date"), CNT = rw.Field <int>("CNT") }).ToList(); System.Data.DataTable tblStat = new System.Data.DataTable(); dgvStatGrid.DataSource = null; tblStat.Columns.Add(new DataColumn("Name")); tblStat.Columns.Add(new DataColumn("KCP")); tblStat.Columns.Add(new DataColumn("SUM")); foreach (var d in dates) { tblStat.Columns.Add(new DataColumn(d.ToShortDateString())); } var lp_ids = (from x in _dataFULL select new { x.LicenseProgramId, x.LicenseProgramName }).Distinct().OrderBy(x => x.LicenseProgramName); foreach (var lp in lp_ids) { //создаём строку DataRow rwLP = tblStat.NewRow(); rwLP.SetField <string>("Name", lp.LicenseProgramName); int KCP = KCP_Data.Where(x => x.LicenseProgramId == lp.LicenseProgramId).Select(x => x.KCP).Sum(); rwLP.SetField <int>("KCP", KCP); int AbSum = SumAbit.Where(x => x.LicenseProgramId == lp.LicenseProgramId).Select(x => x.CNT).Sum(); rwLP.SetField <int>("SUM", AbSum); foreach (var d in dates) { int cnt = (from x in _dataFULL where x.LicenseProgramId == lp.LicenseProgramId && x.Date.Date == d select x.CNT).DefaultIfEmpty(0).Sum(); rwLP.SetField <int>(d.ToShortDateString(), cnt); } tblStat.Rows.Add(rwLP); //собираем вложенные образовательные программы var op_lst = (from x in _dataFULL where x.LicenseProgramId == lp.LicenseProgramId select new { x.ObrazProgramId, x.ObrazProgramName }).Distinct().OrderBy(x => x.ObrazProgramName); foreach (var op in op_lst) { //создаём строку DataRow rwOP = tblStat.NewRow(); rwOP.SetField <string>("Name", " " + op.ObrazProgramName); KCP = KCP_Data.Where(x => x.LicenseProgramId == lp.LicenseProgramId && x.ObrazProgramId == op.ObrazProgramId).Select(x => x.KCP).Sum(); rwOP.SetField <int>("KCP", KCP); AbSum = SumAbit.Where(x => x.LicenseProgramId == lp.LicenseProgramId && x.ObrazProgramId == op.ObrazProgramId).Select(x => x.CNT).Sum(); rwOP.SetField <int>("SUM", AbSum); foreach (var d in dates) { int cnt = (from x in _dataFULL where x.LicenseProgramId == lp.LicenseProgramId && x.Date.Date == d && x.ObrazProgramId == op.ObrazProgramId select x.CNT).DefaultIfEmpty(0).Sum(); rwOP.SetField <int>(d.ToShortDateString(), cnt); } tblStat.Rows.Add(rwOP); var prof_lst = (from x in _dataFULL where x.LicenseProgramId == lp.LicenseProgramId && x.ObrazProgramId == op.ObrazProgramId && x.ProfileId != null select new { x.ProfileId, x.ProfileName }).Distinct().OrderBy(x => x.ProfileName); foreach (var prof in prof_lst) { //создаём строку DataRow rwProf = tblStat.NewRow(); rwProf.SetField <string>("Name", " " + prof.ProfileName); KCP = KCP_Data.Where(x => x.LicenseProgramId == lp.LicenseProgramId && x.ObrazProgramId == op.ObrazProgramId && x.ProfileId == prof.ProfileId) .Select(x => x.KCP).Sum(); rwProf.SetField <int>("KCP", KCP); AbSum = SumAbit.Where(x => x.LicenseProgramId == lp.LicenseProgramId && x.ObrazProgramId == op.ObrazProgramId && x.ProfileId == prof.ProfileId).Select(x => x.CNT).Sum(); rwProf.SetField <int>("SUM", AbSum); foreach (var d in dates) { int cnt = (from x in _dataFULL where x.LicenseProgramId == lp.LicenseProgramId && x.Date.Date == d && x.ObrazProgramId == op.ObrazProgramId && x.ProfileId == prof.ProfileId select x.CNT).DefaultIfEmpty(0).Sum(); rwProf.SetField <int>(d.ToShortDateString(), cnt); } tblStat.Rows.Add(rwProf); wc.PerformStep(); } wc.PerformStep(); } wc.PerformStep(); } dgvStatGrid.DataSource = tblStat; dgvStatGrid.Columns["Name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; foreach (var d in dates) { dgvStatGrid.Columns[d.ToShortDateString()].Width = 63; } dgvStatGrid.Columns["Name"].HeaderText = "Название"; dgvStatGrid.Columns["KCP"].HeaderText = "КЦ"; dgvStatGrid.Columns["KCP"].Width = 50; dgvStatGrid.Columns["SUM"].HeaderText = "Заявлений с начала приёма"; dgvStatGrid.Columns["SUM"].Width = 63; dgvStatGrid.Columns["KCP"].Frozen = true; dgvStatGrid.Columns["Name"].Frozen = true; dgvStatGrid.Columns["SUM"].Frozen = true; dgvStatGrid.AllowUserToOrderColumns = false; wc.Close(); wc = null; } }
private void FillGrid() { using (PriemEntities context = new PriemEntities()) { //стартовый микро-запрос количества людей по фильтру string query = @"SELECT COUNT(DISTINCT extAbit.PersonId) FROM ed.extAbit WHERE extAbit.StudyLevelGroupId=1 AND FacultyId=" + FacultyId.ToString() + " "; string where = ""; if (LicenseProgramId != null) { where += " AND extAbit.LicenseProgramId=" + LicenseProgramId.ToString(); } if (ObrazProgramId != null) { where += " AND extAbit.ObrazProgramId=" + ObrazProgramId.ToString(); } if (ProfileId != null) { where += " AND extAbit.ProfileId='" + ProfileId.ToString() + "'"; } if (StudyFormId != null) { where += " AND extAbit.StudyFormId=" + StudyFormId.ToString(); } if (StudyBasisId != null) { where += " AND extAbit.StudyBasisId=" + StudyBasisId.ToString(); } int cnt = (int)MainClass.Bdc.GetValue(query + where); Watch wc; if (cnt > 0) { wc = new Watch(cnt); wc.Show(); } else { wc = null; return; } query = @" SELECT DISTINCT extAbit.PersonId, extAbit.FIO, EgeExamName.Name, hlpStatMaxApprovedEgeMarks.Value, (CASE WHEN hlpStatMaxApprovedEgeMarks.EgeExamNameId IN (11, 12, 13, 14) THEN 1 ELSE 0 END) AS ForeignLang FROM ed.extAbit INNER JOIN ed.extExamInEntry ON extExamInEntry.EntryId = extAbit.EntryId INNER JOIN ed.EgeToExam ON EgeToExam.ExamId = extExamInEntry.ExamId INNER JOIN ed.EgeExamName ON EgeExamName.Id = EgeToExam.EgeExamNameId INNER JOIN ed.hlpStatMaxApprovedEgeMarks ON hlpStatMaxApprovedEgeMarks.PersonId = extAbit.PersonId AND hlpStatMaxApprovedEgeMarks.EgeExamNameId = EgeExamName.Id WHERE extAbit.StudyLevelGroupId=1 AND extAbit.FacultyId=" + FacultyId.ToString() + " "; where = ""; if (LicenseProgramId != null) { where += " AND extAbit.LicenseProgramId=" + LicenseProgramId.ToString(); } if (ObrazProgramId != null) { where += " AND extAbit.ObrazProgramId=" + ObrazProgramId.ToString(); } if (ProfileId != null) { where += " AND extAbit.ProfileId='" + ProfileId.ToString() + "'"; } if (StudyFormId != null) { where += " AND extAbit.StudyFormId=" + StudyFormId.ToString(); } if (StudyBasisId != null) { where += " AND extAbit.StudyBasisId=" + StudyBasisId.ToString(); } DataTable tblPersons = MainClass.Bdc.GetDataSet(query + where + " ORDER BY hlpStatMaxApprovedEgeMarks.Value ").Tables[0]; var p_RAW = (from DataRow rw in tblPersons.Rows select new { PersonId = rw.Field <Guid>("PersonId"), FIO = rw.Field <string>("FIO"), ExamName = rw.Field <string>("Name"), IsForeign = rw.Field <int>("ForeignLang") == 1 ? true : false, Value = rw.Field <int>("Value") }); query = @" SELECT PersonId, (case when extAbit.CompetitionId IN (1,2,7,8) then 1 else 0 end) AS VK FROM ed.extAbit WHERE extAbit.StudyLevelGroupId=1 AND extAbit.BackDoc=0 AND extAbit.FacultyId=" + FacultyId.ToString() + " "; where = ""; if (LicenseProgramId != null) { where += " AND extAbit.LicenseProgramId=" + LicenseProgramId.ToString(); } if (ObrazProgramId != null) { where += " AND extAbit.ObrazProgramId=" + ObrazProgramId.ToString(); } if (ProfileId != null) { where += " AND extAbit.ProfileId='" + ProfileId.ToString() + "'"; } if (StudyFormId != null) { where += " AND extAbit.StudyFormId=" + StudyFormId.ToString(); } if (StudyBasisId != null) { where += " AND extAbit.StudyBasisId=" + StudyBasisId.ToString(); } DataTable tblVKs = MainClass.Bdc.GetDataSet(query + where).Tables[0]; var VKs = from DataRow rw in tblVKs.Rows select new { PersonId = rw.Field <Guid>("PersonId"), VK = rw.Field <int>("VK") == 1 ? true : false }; List <Guid> persons = p_RAW.Select(x => x.PersonId).Distinct().ToList(); query = @"SELECT DISTINCT EgeExamName.Name FROM ed.extExamInEntry INNER JOIN ed.EgeToExam ON EgeToExam.ExamId = extExamInEntry.ExamId INNER JOIN ed.EgeExamName ON EgeExamName.Id = EgeToExam.EgeExamNameId"; where = " WHERE extExamInEntry.FacultyId=" + FacultyId.ToString() + " "; if (LicenseProgramId != null) { where += " AND extExamInEntry.LicenseProgramId=" + LicenseProgramId.ToString(); } if (ObrazProgramId != null) { where += " AND extExamInEntry.ObrazProgramId=" + ObrazProgramId.ToString(); } if (ProfileId != null) { where += " AND extExamInEntry.ProfileId='" + ProfileId.ToString(); } if (StudyFormId != null) { where += " AND extExamInEntry.StudyFormId=" + StudyFormId.ToString(); } if (StudyBasisId != null) { where += " AND extExamInEntry.StudyBasisId=" + StudyBasisId.ToString(); } DataTable tblExams = MainClass.Bdc.GetDataSet(query + where).Tables[0]; dgv.DataSource = null; DataTable tblSource = new DataTable(); tblSource.Columns.Add("Id", typeof(Guid)); tblSource.Columns.Add("ФИО", typeof(string)); foreach (DataRow rw in tblExams.Rows) { tblSource.Columns.Add(rw.Field <string>("Name"), typeof(int)); } tblSource.Columns.Add("Сумма", typeof(int)); tblSource.Columns.Add("Green", typeof(bool)); Guid PersonId; foreach (Guid pId in persons) { var rwData = from x in p_RAW where x.PersonId == pId select new { x.PersonId, x.FIO, x.ExamName, x.Value, x.IsForeign }; DataRow rw = tblSource.NewRow(); PersonId = rwData.First().PersonId; rw["Id"] = PersonId; rw["ФИО"] = rwData.First().FIO; foreach (var ex in rwData.Select(x => new { x.ExamName, x.Value })) { rw[ex.ExamName] = ex.Value; } rw["Сумма"] = rwData.Where(x => x.IsForeign == false).Select(x => x.Value).DefaultIfEmpty(0).Sum() + rwData.Where(x => x.IsForeign == true).Select(x => x.Value).DefaultIfEmpty(0).Max(); rw["Green"] = VKs.Where(x => x.PersonId == PersonId && x.VK == true).Select(x => x.VK).DefaultIfEmpty(false).First(); tblSource.Rows.Add(rw); wc.PerformStep(); } query = "SELECT SUM(KCP) FROM ed.qEntry WHERE StudyLevelGroupId=1 AND FacultyId=" + FacultyId.ToString() + " "; where = ""; if (LicenseProgramId != null) { where += " AND LicenseProgramId=" + LicenseProgramId.ToString(); } if (ObrazProgramId != null) { where += " AND ObrazProgramId=" + ObrazProgramId.ToString(); } if (ProfileId != null) { where += " AND ProfileId='" + ProfileId.ToString(); } if (StudyFormId != null) { where += " AND StudyFormId=" + StudyFormId.ToString(); } if (StudyBasisId != null) { where += " AND StudyBasisId=" + StudyBasisId.ToString(); } int kcp = (int)MainClass.Bdc.GetValue(query + where); tbKCP.Text = kcp.ToString(); var p_sums = (from DataRow rw in tblSource.Rows select new { Id = rw.Field <Guid>("Id"), Sum = rw.Field <int>("Сумма") }).OrderByDescending(x => x.Sum); int i = VKs.Where(x => x.VK == true).Select(x => x.PersonId).Distinct().Count(); foreach (var p in p_sums) { if (++i > kcp) { break; } for (int j = 0; j < tblSource.Rows.Count; j++) { if (tblSource.Rows[j].Field <Guid>("Id") == p.Id) { tblSource.Rows[j].SetField <bool>("Green", true); break; } } } dgv.DataSource = tblSource; dgv.Columns["Id"].Visible = false; dgv.Columns["Green"].Visible = false; dgv.Sort(dgv.Columns["Сумма"], ListSortDirection.Ascending); dgv.Columns["ФИО"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; wc.Close(); wc = null; } }
private void btnPrintMatrix_Click(object sender, EventArgs e) { using (PriemEntities context = new PriemEntities()) { var facs = context.SP_Faculty.Select(x => new { x.Id, x.Name, x.Acronym }).ToList(); DataTable dt = new DataTable(); dt.Columns.Add("Fac"); foreach (string s in facs.Select(x => x.Name)) { dt.Columns.Add(s); } Watch wc = new Watch(facs.Count * 2); wc.Show(); foreach (var f in facs) { DataRow rw = dt.NewRow(); rw["Fac"] = f.Name; DataTable tblData = GetTableIntersect(f.Id); var data = from DataRow row in tblData.Rows select new { Id = row.Field <int>("FacultyId"), FacultyName = row.Field <string>("Факультет"), CNT = row.Field <int>("Количество людей") }; foreach (var d in data) { rw[d.FacultyName] = d.CNT; } dt.Rows.Add(rw); wc.PerformStep(); } RtfDocument doc = new RtfDocument(PaperSize.A4, PaperOrientation.Landscape, Lcid.Russian); RtfTable table = doc.addTable(dt.Rows.Count + 1, dt.Columns.Count); table.cell(0, 0).addParagraph().Text = ""; int m = 0; foreach (var f in facs) { table.FillCell(0, ++m, f.Acronym, FontStyleFlag.Normal); } int i = 1; table.CellPadding = 1.2f; table.setInnerBorder(RtfWriter.BorderStyle.Single, 0.7f); table.setOuterBorder(RtfWriter.BorderStyle.Single, 1f); foreach (DataRow r in dt.Rows) { table.setColWidth(0, 260f); for (int j = 0; j < r.ItemArray.Count(); j++) { table.FillCell(i, j, i == j ? "-" : r[j].ToString(), j > 0 ? FontStyleFlag.Bold : FontStyleFlag.Normal); } i++; wc.PerformStep(); } wc.Close(); string fname = MainClass.saveTempFolder + "/stat.rtf"; doc.save(fname); System.Diagnostics.Process.Start(fname); } }