//строим запрос фильтров для абитуриентов
private string GetAbitFilterString()
{
string s = " AND ed.qAbiturient.StudyLevelGroupId = 1";
//обработали факультет
if (FacultyId != null)
{
s += " AND ed.qAbiturient.FacultyId = " + FacultyId.ToString();
}
return(s);
}
//печать
private void btnPrint_Click(object sender, EventArgs e)
{
if (ExamVedId == null)
{
return;
}
try
{
WordDoc wd = new WordDoc(string.Format(@"{0}\Templates\CryptoExamsVed.dot", Application.StartupPath));
TableDoc td = wd.Tables[0];
using (PriemEntities context = new PriemEntities())
{
extExamsVed ved = (from ev in context.extExamsVed
where ev.Id == ExamVedId
select ev).FirstOrDefault();
wd.Fields["Faculty"].Text = cbFaculty.Text.ToLower();
wd.Fields["Exam"].Text = ved.ExamName;
wd.Fields["StudyBasis"].Text = Util.ToStr(ved.StudyBasisId == null ? "все" : ved.StudyBasisName);
wd.Fields["Date"].Text = ved.Date.ToShortDateString();
wd.Fields["VedNum"].Text = ved.Number.ToString();
int i = 1;
// печать из грида
foreach (DataGridViewRow dgvr in dgvList.Rows)
{
td[0, i] = i.ToString();
td[1, i] = dgvr.Cells["Фамилия"].Value.ToString();
td[2, i] = dgvr.Cells["Имя"].Value.ToString();
td[3, i] = dgvr.Cells["Отчество"].Value.ToString();
td[4, i] = DateTime.Parse(dgvr.Cells["Дата_рождения"].Value.ToString()).ToShortDateString();
td[5, i] = dgvr.Cells["Ид_номер"].Value.ToString();
td[6, i] = FacultyId.ToString();
td[7, i] = ved.ExamName;
td[8, i] = ved.ExamId.ToString();
td[9, i] = ved.Date.ToShortDateString();;
td.AddRow(1);
i++;
}
td.DeleteLastRow();
}
}
catch (Exception exc)
{
WinFormsServ.Error("Ошибка вывода в Word: \n" + exc.Message);
}
}
private void btnPrintSticker_Click(object sender, EventArgs e)
{
if (ExamVedId == null)
{
return;
}
if (MainClass.IsOwner() || MainClass.IsCrypto() || MainClass.IsPasha() || MainClass.IsCryptoMain())
{
FileStream fileS = null;
string savePath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\Sticker.pdf";
float fontsize = 8;
try
{
using (PriemEntities context = new PriemEntities())
{
Document document = new Document(PageSize.A4, 50, 50, 50, 50);
document.SetMargins(18, 18, 36, 5);
using (fileS = new FileStream(savePath, FileMode.Create))
{
BaseFont bfTimes = BaseFont.CreateFont(string.Format(@"{0}\times.ttf", MainClass.dirTemplates), BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
iTextSharp.text.Font font = new iTextSharp.text.Font(bfTimes, 10);
PdfWriter pw = PdfWriter.GetInstance(document, fileS);
document.Open();
int cntCells = 2;
int.TryParse(tbCountCell.Text.Trim(), out cntCells);
string examId;
string examName;
string examDate;
string facId;
string vedNumber;
extExamsVed ved = (from ev in context.extExamsVed
where ev.Id == ExamVedId
select ev).FirstOrDefault();
examId = ved.ExamId.ToString();
examName = ved.ExamName.ToString();
examDate = ved.Date.ToShortDateString();
vedNumber = ved.Number.ToString();
facId = FacultyId.ToString();
DataSet dsPersons = bdc.GetDataSet(string.Format("SELECT DISTINCT ed.extPerson.Id, ed.extPerson.PersonNum as RegNum, ed.extPerson.FIO, ed.ExamsVedHistory.PersonVedNumber " +
"FROM ed.extPerson LEFT JOIN ed.ExamsVedHistory ON ed.ExamsVedHistory.PersonId = ed.extPerson.Id WHERE ed.ExamsVedHistory.ExamsVedId = '{0}' ORDER BY FIO ", ExamVedId.ToString()));
PdfPTable t = new PdfPTable(3);
float pgW = (PageSize.A4.Width - 36) / 3;
float[] headerwidths = { pgW, pgW, pgW };
t.SetWidths(headerwidths);
t.WidthPercentage = 100f;
t.SpacingBefore = 10f;
t.SpacingAfter = 10f;
t.DefaultCell.MinimumHeight = 120;
int cellsNum = (dsPersons.Tables[0].Rows.Count) * (cntCells + 1);
int ost = cellsNum % 3;
foreach (DataRow drr in dsPersons.Tables[0].Rows)
{
string text = drr["FIO"].ToString() + "\n" + drr["RegNum"].ToString() + " " + facId + "\n";
text += examDate + " " + examName;
Barcode128 barcode1 = new Barcode128();
barcode1.Code = vedNumber + "==" + drr["PersonVedNumber"].ToString() + "-";
Barcode128 barcode2 = new Barcode128();
barcode2.Code = vedNumber + "==" + drr["PersonVedNumber"].ToString() + "-";
PdfContentByte cb = pw.DirectContent;
iTextSharp.text.Image img1 = barcode1.CreateImageWithBarcode(cb, null, null);
img1.ScaleAbsolute(80f, 60f);
iTextSharp.text.Image img2 = barcode2.CreateImageWithBarcode(cb, iTextSharp.text.Color.BLACK, iTextSharp.text.Color.WHITE);
img2.ScaleAbsolute(80f, 60f);
PdfPTable ptPl = new PdfPTable(1);
float[] hwh = { pgW };
ptPl.SetWidthPercentage(hwh, PageSize.A4);
PdfPCell clPlText = new PdfPCell(new Phrase(text, new iTextSharp.text.Font(bfTimes, fontsize)));
clPlText.HorizontalAlignment = iTextSharp.text.Rectangle.ALIGN_CENTER;
clPlText.PaddingBottom = 2;
clPlText.PaddingTop = 2;
clPlText.Border = iTextSharp.text.Rectangle.NO_BORDER;
PdfPCell clPlBarc = new PdfPCell();
clPlBarc.AddElement(img1);
clPlBarc.HorizontalAlignment = iTextSharp.text.Rectangle.ALIGN_CENTER;
clPlBarc.PaddingTop = 1;
clPlBarc.PaddingLeft = 40;
clPlBarc.Border = iTextSharp.text.Rectangle.NO_BORDER;
ptPl.AddCell(clPlText);
ptPl.AddCell(clPlBarc);
PdfPCell pcell = new PdfPCell(ptPl);
pcell.PaddingTop = 6;
pcell.PaddingBottom = 6;
pcell.PaddingLeft = 6;
pcell.PaddingRight = 6;
pcell.FixedHeight = 100;
pcell.Border = iTextSharp.text.Rectangle.NO_BORDER;
t.AddCell(pcell);
PdfPCell pcell1;
for (int i = 0; i < cntCells; i++)
{
ptPl = new PdfPTable(1);
ptPl.SetWidthPercentage(hwh, PageSize.A4);
clPlText = new PdfPCell();
clPlText.AddElement(img2);
clPlText.PaddingLeft = 40;
clPlText.PaddingRight = 40;
clPlText.PaddingTop = 20;
clPlText.Border = iTextSharp.text.Rectangle.NO_BORDER;
clPlBarc = new PdfPCell(new Phrase((i + 1).ToString(), new iTextSharp.text.Font(bfTimes, fontsize)));
clPlBarc.HorizontalAlignment = iTextSharp.text.Rectangle.ALIGN_CENTER;
clPlBarc.PaddingTop = 1;
clPlBarc.Border = iTextSharp.text.Rectangle.NO_BORDER;
ptPl.AddCell(clPlText);
ptPl.AddCell(clPlBarc);
pcell1 = new PdfPCell(ptPl);
pcell1.FixedHeight = 100;
pcell1.Border = iTextSharp.text.Rectangle.NO_BORDER;
t.AddCell(pcell1);
}
}
for (int i = 0; i < 3 - ost; i++)
{
PdfPCell pc = new PdfPCell();
pc.Border = iTextSharp.text.Rectangle.NO_BORDER;
t.AddCell(pc);
}
if (t != null)
{
document.Add(t);
}
document.Close();
Process pr = new Process();
pr.StartInfo.Verb = "Print";
pr.StartInfo.FileName = string.Format(savePath);
pr.Start();
pr.Close();
}
}
}
catch (Exception exc)
{
WinFormsServ.Error(exc.Message);
}
finally
{
if (fileS != null)
{
fileS.Dispose();
}
}
}
else
{
WinFormsServ.Error("Невозможно создание наклеек, недостаточно прав");
}
}
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;
}
}
