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 SetMarksForExam(int?examId) { try { using (PriemEntities context = new PriemEntities()) { int examInostr = 65; int filFacId = 18; string flt_backDoc = " AND ed.qAbiturient.BackDoc = 0 "; string flt_enable = " AND ed.qAbiturient.NotEnabled = 0 "; string flt_protocol = " AND ProtocolTypeId = 1 AND IsOld = 0 AND Excluded = 0 "; string flt_status = " AND ed.extFBSStatus.FBSStatusId IN (1,4) "; string flt_mark = string.Format(" AND NOT EXISTS( SELECT ed.Mark.Value FROM ed.Mark INNER JOIN ed.extExamInEntry ON ed.Mark.ExamInEntryId = ed.extExamInEntry.Id WHERE ed.Mark.AbiturientId = ed.qAbiturient.Id AND ed.extExamInEntry.ExamId = {0} AND ed.extExamInEntry.IsAdditional=0) ", examId); string flt_hasEge = string.Format(" AND ed.Person.Id IN (SELECT PersonId FROM ed.extEgeMark LEFT JOIN ed.EgeToExam ON ed.extEgeMark.EgeExamNameId = ed.EgeToExam.EgeExamNameId WHERE ed.EgeToExam.ExamId = {0})", examId); string flt_hasExam = string.Format(" AND ed.qAbiturient.EntryId IN (SELECT ed.extExamInEntry.EntryId FROM ed.extExamInEntry WHERE ed.extExamInEntry.ExamId = {0})", examId); string queryAbits = @"SELECT ed.qAbiturient.Id, ed.qAbiturient.FacultyId, ed.qAbiturient.EntryId FROM ed.qAbiturient LEFT JOIN ed.Person ON ed.qAbiturient.PersonId = ed.Person.Id LEFT JOIN ed.extFBSStatus ON ed.extFBSStatus.PersonId = Person.Id LEFT JOIN ed.extProtocol ON extProtocol.AbiturientId = qAbiturient.Id WHERE 1 = 1 "; DataSet ds = bdc.GetDataSet(queryAbits + GetAbitFilterString() + flt_backDoc + flt_enable + flt_protocol + flt_status + flt_mark + flt_hasExam + flt_hasEge); wtc.pBar.Maximum += ds.Tables[0].Rows.Count; //using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.RequiresNew)) //{ try { foreach (DataRow dsRow in ds.Tables[0].Rows) { int? balls = null; Guid abId = new Guid(dsRow["Id"].ToString()); Guid entryId = new Guid(dsRow["EntryId"].ToString()); int?exInEntryId = (from eie in context.extExamInEntry where eie.EntryId == entryId && eie.ExamId == examId select eie.Id).FirstOrDefault(); if (exInEntryId == null) { continue; } Guid egeCertificateId; if (examId != examInostr) { balls = (from emm in context.extEgeMarkMaxAbit join ete in context.EgeToExam on emm.EgeExamNameId equals ete.EgeExamNameId where emm.AbiturientId == abId && ete.ExamId == examId select emm.Value).Max(); egeCertificateId = (from emm in context.extEgeMarkMaxAbit join ete in context.EgeToExam on emm.EgeExamNameId equals ete.EgeExamNameId where emm.AbiturientId == abId && ete.ExamId == examId && emm.Value == balls select emm.EgeCertificateId).FirstOrDefault(); } else { List <int> lstInostr = (from ete in context.EgeToExam where ete.ExamId == examInostr select ete.EgeExamNameId).ToList <int>(); if (dsRow["FacultyId"].ToString() == filFacId.ToString()) { int?egeExamNameId = (from etl in context.EgeToLanguage join ab in context.qAbiturient on etl.LanguageId equals ab.LanguageId where etl.ExamId == examInostr && ab.Id == abId select etl.EgeExamNameId).FirstOrDefault(); if (egeExamNameId != null) { balls = (from emm in context.extEgeMarkMaxAbit where emm.AbiturientId == abId && emm.EgeExamNameId == egeExamNameId select emm.Value).Max(); } egeCertificateId = (from emm in context.extEgeMarkMaxAbit join ete in context.EgeToExam on emm.EgeExamNameId equals ete.EgeExamNameId where emm.AbiturientId == abId && ete.ExamId == examId && emm.Value == balls select emm.EgeCertificateId).FirstOrDefault(); } else { int cntEM = (from emm in context.extEgeMarkMaxAbit where lstInostr.Contains(emm.EgeExamNameId) && emm.AbiturientId == abId select emm.EgeMarkId).Count(); if (cntEM > 1) { int?egeExamNameId = (from etl in context.EgeToLanguage join ab in context.qAbiturient on etl.LanguageId equals ab.LanguageId where etl.ExamId == examInostr && ab.Id == abId select etl.EgeExamNameId).FirstOrDefault(); if (egeExamNameId != null) { balls = (from emm in context.extEgeMarkMaxAbit where emm.AbiturientId == abId && emm.EgeExamNameId == egeExamNameId select emm.Value).Max(); } egeCertificateId = (from emm in context.extEgeMarkMaxAbit join ete in context.EgeToExam on emm.EgeExamNameId equals ete.EgeExamNameId where emm.AbiturientId == abId && ete.ExamId == examId && emm.Value == balls select emm.EgeCertificateId).FirstOrDefault(); } else { balls = (from emm in context.extEgeMarkMaxAbit join ete in context.EgeToExam on emm.EgeExamNameId equals ete.EgeExamNameId where emm.AbiturientId == abId && ete.ExamId == examId select emm.Value).Max(); egeCertificateId = (from emm in context.extEgeMarkMaxAbit join ete in context.EgeToExam on emm.EgeExamNameId equals ete.EgeExamNameId where emm.AbiturientId == abId && ete.ExamId == examId && emm.Value == balls select emm.EgeCertificateId).FirstOrDefault(); } } } if (balls != null) { context.Mark_Insert(abId, exInEntryId, balls, dtDateExam.Value.Date, true, false, false, null, null, egeCertificateId); } else { continue; } wtc.PerformStep(); marksCount++; } //transaction.Complete(); } catch (Exception exc) { throw new Exception("Ошибка загрузки оценок1: " + exc.Message); } // } } } catch (Exception ex) { WinFormsServ.Error("Ошибка загрузки оценок " + ex.Message); wtc.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 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); } }