protected override void GetSource()
{
using (PriemEntities context = new PriemEntities())
{
var query = from ob in context.extOlympBook
orderby ob.Number, ob.OlympNameName, ob.OlympLevelId
select new
{
ob.Id,
ob.OlympTypeId,
ob.Number,
ob.OlympTypeName,
ob.OlympNameName,
ob.OlympSubjectName,
ob.OlympLevelName
};
int?olTypeId = ComboServ.GetComboIdInt(cbOlympType);
if (olTypeId != null)
{
query = query.Where(c => c.OlympTypeId == olTypeId);
}
Dgv.DataSource = query;
SetVisibleColumnsAndNameColumns();
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
try
{
WordDoc wd = new WordDoc(string.Format(@"{0}\ListPerson.dot", MainClass.dirTemplates));
int k = dgvAbiturients.Columns.Count;
wd.AddNewTable(dgvAbiturients.Rows.Count + 1, k);
TableDoc td = wd.Tables[0];
int? facId = ComboServ.GetComboIdInt(cbFaculty);
string sFac = cbFaculty.Text.ToLower();
if (sFac.CompareTo("все") == 0)
{
sFac = "";
}
else
{
sFac = "получивших направления на послеление от ";
if (facId == 3)
{
sFac += "высшей школы менеджмента ";
}
else
{
sFac += sFac.Replace("кий", "кого ").Replace("ый", "ого ") + " факультета ";
}
}
wd.Fields["Faculty"].Text = sFac;
wd.Fields["Info"].Text = "для общежитий";
int i = 0;
td[0, 0] = "№ п/п";
for (int j = 0; j < k - 1; j++)
{
td[j + 1, 0] = dgvAbiturients.Columns[j + 1].HeaderText;
}
// печать из грида
foreach (DataGridViewRow dgvr in dgvAbiturients.Rows)
{
td[0, i + 1] = (i + 1).ToString();
for (int j = 0; j < k - 1; j++)
{
td[j + 1, i + 1] = dgvAbiturients.Rows[i].Cells[j + 1].Value.ToString();
}
i++;
}
}
catch (Exception exc)
{
WinFormsServ.Error("Ошибка вывода в Word: \n" + exc.Message);
}
}
private int?GetStudyBasisId()
{
if (ComboServ.IsNullOrEmpty(cbStudyBasis) || cbStudyBasis.Text == ComboServ.DISPLAY_ALL_VALUE)
{
return(null);
}
return(ComboServ.GetComboIdInt(cbStudyBasis));
}
private int?GetFacultyId()
{
if (ComboServ.IsNullOrEmpty(cbFaculty) || cbFaculty.Text == ComboServ.DISPLAY_ALL_VALUE)
{
return(null);
}
return(ComboServ.GetComboIdInt(cbFaculty));
}
private void btnPrint_Click(object sender, EventArgs e)
{
List <string> columnList = new List <string>();
foreach (DataGridViewColumn column in dgvAbitList.Columns)
{
if (column.Visible)
{
columnList.Add(column.Name);
}
}
try
{
WordDoc wd = new WordDoc(string.Format(@"{0}\Templates\ListAbit.dot", Application.StartupPath));
wd.AddNewTable(dgvAbitList.Rows.Count + 1, columnList.Count + 1);
TableDoc td = wd.Tables[0];
//переменные
int? FacId = ComboServ.GetComboIdInt(cbFaculty);
string sFac = cbFaculty.Text;
if (cbFaculty.Text == ComboServ.DISPLAY_ALL_VALUE)
{
sFac = "Все факультеты";
}
wd.Fields["Faculty"].Text = sFac;
//wd.Fields["Section"].Text = sForm;
int i = 0;
td[0, 0] = "№ п/п";
for (int j = 0; j < columnList.Count; j++)
{
td[j + 1, 0] = dgvAbitList.Columns[columnList[j]].HeaderText;
}
// печать из грида
foreach (DataGridViewRow dgvr in dgvAbitList.Rows)
{
td[0, i + 1] = (i + 1).ToString();
for (int j = 0; j < columnList.Count; j++)
{
td[j + 1, i + 1] = dgvAbitList.Rows[i].Cells[columnList[j]].Value.ToString();
}
i++;
}
}
catch (Exception exc)
{
WinFormsServ.Error("Ошибка вывода в Word: \n" + exc.Message);
}
}
//добавить
private void btnAdd_Click(object sender, EventArgs e)
{
if ((!Regex.IsMatch(tbMark.Text.Trim(), @"^[2-5]{0,1}$")))
{
WinFormsServ.Error("Неправильный формат оценки");
return;
}
int mark;
if (!int.TryParse(tbMark.Text.Trim(), out mark))
{
WinFormsServ.Error("Введите оценку");
return;
}
//если вдруг сломается комбобокс, выводя все предметы
if (_lMarks.Contains(cbSubjects.Text))
{
MessageBox.Show("Оценка по данному предмету уже есть!");
return;
}
int?subjId = ComboServ.GetComboIdInt(cbSubjects);
if (subjId == null)
{
WinFormsServ.Error("Не выбран предмет");
return;
}
try
{
using (PriemEntities context = new PriemEntities())
{
context.AttMarks_Insert(_personId, subjId, mark);
UpdateDataGrid();
}
}
catch (Exception ex)
{
WinFormsServ.Error("Ошибка при добавлении оценки: " + ex.Message);
}
}
private void FillGrid()
{
string query = @"SELECT t.FacultyName AS 'Факультет', t.CNT_Reg AS 'Абитуриентов из региона', t.CNT_All AS 'Абитуриентов всего',
convert(float, t.CNT_Reg)/convert(float, t.CNT_All) AS '% от общ',
t.MinEGE AS 'Мин сумма ЕГЭ', t.AvgEGE AS 'Сред сумма ЕГЭ', t.MaxEGE AS 'Макс сумма ЕГЭ'
FROM
(
SELECT DISTINCT FacultyName, COUNT(DISTINCT Person.Id) AS 'CNT_Reg',
(SELECT COUNT(DISTINCT q.PersonId) FROM ed.qAbitAll AS q WHERE qAbitAll.FacultyName=q.FacultyName AND q.StudyLevelGroupId = 1) AS 'CNT_All',
MIN(hlpStatMarksSum.SUM) AS 'MinEGE', AVG(convert(float, hlpStatMarksSum.SUM)) AS 'AvgEGE', MAX(hlpStatMarksSum.SUM) AS 'MaxEGE'
FROM ed.qAbitAll
INNER JOIN ed.Person ON Person.Id = qAbitAll.PersonId
INNER JOIN ed.hlpStatMarksSum ON hlpStatMarksSum.AbiturientId = qAbitAll.Id
LEFT JOIN ed.extEntryView ON extEntryView.AbiturientId = qAbitAll.Id
WHERE qAbitAll.StudyLevelGroupId=@StudyLevelGroupId AND RegionId=@RegionId
";
SortedList <string, object> sl = new SortedList <string, object>();
sl.Add("@RegionId", ComboServ.GetComboIdInt(cbRegion));
sl.Add("@StudyLevelGroupId", MainClass.studyLevelGroupId);
if (cbEntered.Checked)
{
query += " AND extEntryView.Id IS NOT NULL";
}
string groupby = " GROUP BY FacultyName ) t ORDER BY [% от общ] DESC";
DataTable tbl = MainClass.Bdc.GetDataSet(query + groupby, sl).Tables[0];
foreach (DataRow rw in tbl.Rows)
{
rw.SetField <double>("Сред сумма ЕГЭ", Math.Round(rw.Field <double>("Сред сумма ЕГЭ"), 2));
rw.SetField <double>("% от общ", Math.Round(rw.Field <double>("% от общ"), 2));
}
dgv.DataSource = tbl;
dgv.Columns["Факультет"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
//ReChart();
}
//обновление грида
protected override void GetSource()
{
string sFilters = " WHERE HostelAbit = 1 ";
int?facId = ComboServ.GetComboIdInt(cbFaculty);
if (facId != null)
{
sFilters += string.Format(" AND HasAssignToHostel = 1 AND HostelFacultyId = {0}", facId);
}
_sQuery = @"SELECT ed.extPerson.Id, PersonNum as Ид_номер, FIO as ФИО,
(Case When (CountryId = 1 AND NOT RegionId IS NULL) then RegionName else CountryName end) AS Место_жительства,
(Case when HostelFacultyId IS NULL then 'не выдано' else HostelFacultyName end) AS Факультет_выдавший_направление,
PassportData AS Паспортные_данные
FROM ed.qAbiturient INNER JOIN ed.extPerson ON ed.qAbiturient.PersonId = ed.extPerson.Id ";
HelpClass.FillDataGrid(Dgv, _bdc, _sQuery, sFilters, " ORDER BY ФИО");
dgvAbiturients.Columns[1].Width = 74;
dgvAbiturients.Columns[2].Width = 234;
dgvAbiturients.Columns[3].Width = 266;
}
private void GetFilters(ref IQueryable <qEntry> query)
{
using (PriemEntities context = new PriemEntities())
{
// Факультеты
if (cbFaculty.SelectedValue.ToString() != ComboServ.ALL_VALUE)
{
int?h = ComboServ.GetComboIdInt(cbFaculty);
if (h != null)
{
query = query.Where(c => c.FacultyId == h);
}
}
// основа обучения
if (cbStudyBasis.SelectedValue.ToString() != ComboServ.ALL_VALUE)
{
int?h = ComboServ.GetComboIdInt(cbStudyBasis);
if (h != null)
{
query = query.Where(c => c.StudyBasisId == h);
}
}
// форма обучения
if (cbStudyForm.SelectedValue.ToString() != ComboServ.ALL_VALUE)
{
int?h = ComboServ.GetComboIdInt(cbStudyForm);
if (h != null)
{
query = query.Where(c => c.StudyFormId == h);
}
}
// подвид программы
if (cbStudyLevel.SelectedValue.ToString() != ComboServ.ALL_VALUE)
{
int?h = ComboServ.GetComboIdInt(cbStudyLevel);
if (h != null)
{
query = query.Where(c => c.StudyLevelId == h);
}
}
// ускоренная
if (cbIsReduced.SelectedValue.ToString() != ComboServ.ALL_VALUE)
{
bool h = QueryServ.ToBoolValue(ComboServ.GetComboIdInt(cbIsReduced));
query = query.Where(c => c.IsReduced == h);
}
// параллельная
if (cbIsParallel.SelectedValue.ToString() != ComboServ.ALL_VALUE)
{
bool h = QueryServ.ToBoolValue(ComboServ.GetComboIdInt(cbIsParallel));
query = query.Where(c => c.IsParallel == h);
}
// для лиц с во
if (cbIsSecond.SelectedValue.ToString() != ComboServ.ALL_VALUE)
{
bool h = QueryServ.ToBoolValue(ComboServ.GetComboIdInt(cbIsSecond));
query = query.Where(c => c.IsSecond == h);
}
}
}
