private void UpdateDataGrid()
{
gbOrders.Visible = true;
gbOrdersFor.Visible = true;
if (StudyFormId == null || StudyBasisId == null)
{
dgvViews.DataSource = null;
gbOrders.Visible = false;
gbOrdersFor.Visible = false;
return;
}
string query = string.Format("SELECT DISTINCT Id, Number as 'Номер представления' FROM ed.extEntryView WHERE StudyFormId={0} AND StudyBasisId={1} AND FacultyId= {2} AND LicenseProgramId = {3} AND IsListener = {4} AND IsSecond = {5} AND IsReduced = {6} AND IsParallel = {7} order by 2", StudyFormId, StudyBasisId, FacultyId, LicenseProgramId, QueryServ.StringParseFromBool(IsListener), QueryServ.StringParseFromBool(IsSecond), QueryServ.StringParseFromBool(IsReduced), QueryServ.StringParseFromBool(IsParallel));
HelpClass.FillDataGrid(dgvViews, _bdc, query, "");
dgvViews.Columns["Номер представления"].Width = 149;
dgvViews.Update();
if (dgvViews.RowCount == 0)
{
gbOrders.Visible = false;
gbOrdersFor.Visible = false;
}
}
//установить фильтры
public void SetFilters(string sFaculty, string sForm, string sGridQuery)
{
cbFaculty.SelectedItem = sFaculty;
HelpClass.FillDataGrid(dgvAbitList, _bdc, sGridQuery, "");
lblCount.Text = dgvAbitList.RowCount.ToString();
}
private void DoUpdate(string filters, string orderby, List <string> tableList)
{
List <string> lTables = tableList == null ? new List <string>() : tableList;
if (_list != null)
{
foreach (iFilter i in _list)
{
if (i is Filter)
{
string sTable = (i as Filter).GetFilterItem().Table;
if (!lTables.Contains(sTable))
{
lTables.Add(sTable);
}
}
}
}
if (MainClass._config.ColumnListPerson.Count == 0)
{
MainClass._config.ColumnListPerson.Add("ФИО");
}
string sortedColumn = string.Empty;
ListSortDirection order = ListSortDirection.Ascending;
bool sorted = false;
int index = dgvAbitList.CurrentRow == null ? -1 : dgvAbitList.CurrentRow.Index;
if (dgvAbitList.SortOrder != SortOrder.None)
{
sorted = true;
sortedColumn = dgvAbitList.SortedColumn.Name;
order = dgvAbitList.SortOrder == SortOrder.Ascending ? ListSortDirection.Ascending : ListSortDirection.Descending;
}
HelpClass.FillDataGrid(this.dgvAbitList, this._bdc, MainClass.qBuilder.GetQuery(MainClass._config.ColumnListPerson, lTables, "ed.Person"), filters, orderby);
if (_groupList == null)
{
if (dgvAbitList.Rows.Count > 0)
{
if (sorted && dgvAbitList.Columns.Contains(sortedColumn))
{
dgvAbitList.Sort(dgvAbitList.Columns[sortedColumn], order);
}
if (index >= 0 && index <= dgvAbitList.Rows.Count)
{
dgvAbitList.CurrentCell = dgvAbitList[1, index];
}
}
}
else
{
_groupList = null;
}
lblCount.Text = dgvAbitList.RowCount.ToString();
btnCard.Enabled = (dgvAbitList.RowCount != 0);
}
//обновление грида
protected override void GetSource()
{
_sQuery = @"SELECT ed.qAbitAll.Id, PersonNum as Ид_номер, FIO as ФИО,
RegNum as Рег_номер, FacultyName as Факультет, ObrazProgramCrypt as Код,
LicenseProgramName as Направление, ProfileName as Профиль,
StudyFormName as Форма, StudyBasisName as Основа, Priority AS Приоритет
FROM ed.qAbitAll INNER JOIN ed.extForeignPerson ON ed.qAbitAll.PersonId=ed.extForeignPerson.Id
WHERE personId in (SELECT distinct personId FROM ed.qAbiturient) ";
HelpClass.FillDataGrid(Dgv, _bdc, _sQuery, "", " ORDER BY ФИО, Рег_номер");
}
private void FillDataGrid(string filters)
{
string defQuery = @"SELECT DISTINCT ed.PersonChanges.PersonId AS Id, ed.extPerson.Surname + ' ' + ed.extPerson.Name + ' ' + ed.extPerson.SecondName AS ФИО,
ed.extPerson.PersonNum AS Ид_номер, ed.PersonChanges.FieldName AS Измененное_поле, ed.PersonChanges.OldValue AS Старое_значение,
ed.PersonChanges.NewValue AS Новое_значение, ed.PersonChanges.Date AS Дата_изменения,
ed.PersonChanges.Owner AS Автор_изменения,
Case When ed.PersonChanges.FacultyId = 0 then 'администратор' else ed.SP_Faculty.Acronym end AS Факультет
FROM ed.PersonChanges LEFT JOIN ed.SP_Faculty ON ed.PersonChanges.FacultyId = ed.SP_Faculty.Id
LEFT JOIN ed.extPerson ON ed.PersonChanges.PersonId = ed.extPerson.Id INNER JOIN ed.qAbiturient ON ed.qAbiturient.PersonId = ed.extPerson.Id WHERE 0=0";
HelpClass.FillDataGrid(dgvChanges, _bdc, defQuery, filters, " ORDER BY Date");
}
protected override void GetSource()
{
_sQuery = string.Format(@"SELECT ed.extAbit.Id as Id, RegNum as Рег_Номер, ed.Person.Surname + ' ' + ed.Person.Name + ' ' + ed.Person.SecondName as ФИО,
ed.Competition.Name as Тип_конкурса, OlympTypeName as Вид, OlympName AS Название, OlympLevelName AS Уровень, OlympSubjectName as Предмет,
OlympValueName as Степень, StudyFormName as Форма_обучения, ed.Person.BirthDate as Дата_рождения, PassportData as Паспорт
FROM ed.extAbit
INNER JOIN ed.Person ON ed.extAbit.PersonId = ed.Person.Id
INNER JOIN ed.extOlympiads ON ed.extOlympiads.AbiturientId = ed.extAbit.Id
LEFT JOIN ed.Competition ON ed.Competition.Id = ed.extAbit.CompetitionId ");
string sFilters = GetFilterString();
HelpClass.FillDataGrid(Dgv, _bdc, _sQuery, sFilters, " ORDER BY FIO");
}
//обновление грида
public virtual void UpdateDataGrid()
{
string sFilters;
btnCreateCsv.Enabled = true;
btnCreateRooms.Enabled = true;
btnDelete.Enabled = true;
btnChange.Enabled = true;
if (ExamVedRoomId != null)
{
sQuery = @"SELECT DISTINCT ed.extPerson.Id, ed.extPerson.PersonNum as Ид_номер, ed.extPerson.Surname AS Фамилия, ed.extPerson.Name AS Имя,
ed.extPerson.SecondName AS Отчество, ed.extPerson.BirthDate AS Дата_рождения
FROM ed.extPerson INNER JOIN ed.ExamsVedRoomHistory ON ed.ExamsVedRoomHistory.PersonId = ed.extPerson.Id ";
sFilters = string.Format("WHERE ed.ExamsVedRoomHistory.ExamsVedRoomId = '{0}' ", ExamVedRoomId.ToString());
}
if (ExamVedId != null)
{
sQuery = @"SELECT DISTINCT ed.extPerson.Id, ed.extPerson.PersonNum as Ид_номер, ed.extPerson.Surname AS Фамилия, ed.extPerson.Name AS Имя,
ed.extPerson.SecondName AS Отчество, ed.extPerson.BirthDate AS Дата_рождения
FROM ed.extPerson INNER JOIN ed.ExamsVedHistory ON ed.ExamsVedHistory.PersonId = ed.extPerson.Id ";
sFilters = string.Format("WHERE ed.ExamsVedHistory.ExamsVedId = '{0}' ", ExamVedId.ToString());
btnDelete.Enabled = false;
btnChange.Enabled = false;
}
else
{
btnCreateCsv.Enabled = false;
btnCreateRooms.Enabled = false;
btnDelete.Enabled = false;
btnChange.Enabled = false;
dgvList.DataSource = null;
dgvList.Update();
return;
}
if (!dgvList.Columns.Contains("Number"))
{
dgvList.Columns.Add("Number", "№");
dgvList.Update();
}
dgvList.Columns["Number"].DisplayIndex = 0;
HelpClass.FillDataGrid(dgvList, bdc, sQuery, sFilters, sOrderby);
}
//обновление грида
protected override void GetSource()
{
_orderBy = " ORDER BY Приложены_файлы, Дата_обновления DESC, ФИО";
_sQuery = @"SELECT DISTINCT qAbiturient.CommitId AS Id,
extForeignPerson.Surname + ' ' + extForeignPerson.Name + ' ' + extForeignPerson.SecondName as ФИО,
extForeignPerson.BirthDate AS Дата_рождения,
extForeignPerson.Nationality AS [Гражданство],
extForeignPerson.[CountryName] AS [Страна проживания],
qAbiturient.CommitNumber AS Barcode,
(
CASE WHEN EXISTS
(
SELECT Id
FROM [Application]
WHERE Application.CommitId = qAbiturient.CommitId
)
THEN CONVERT(bit, 1)
ELSE CONVERT(bit, 0)
END
) AS IsGosLine,
(
CASE WHEN EXISTS
(
SELECT extAbitFileNames_All.Id
FROM extAbitFileNames_All
WHERE extAbitFileNames_All.PersonId = extForeignPerson.Id
)
THEN 'да'
ELSE 'нет'
END
) AS Приложены_файлы,
(
SELECT MAX(extAbitFileNames_All.LoadDate)
FROM extAbitFileNames_All
WHERE extAbitFileNames_All.PersonId = extForeignPerson.Id
AND (extAbitFileNames_All.ApplicationId = qAbiturient.Id OR extAbitFileNames_All.ApplicationId IS NULL)
) AS Дата_обновления
FROM qAbiturient
INNER JOIN extForeignPerson ON qAbiturient.PersonId = extForeignPerson.Id
INNER JOIN qForeignApplicationOnly ON qForeignApplicationOnly.Id = qAbiturient.Id
WHERE qAbiturient.IsImported = 0 AND SemesterId = 1 AND Enabled = 1";
HelpClass.FillDataGrid(dgvAbiturients, _bdcInet, _sQuery + GetFilterString(), _orderBy);
dgvAbiturients.Columns["IsGosLine"].Visible = false;
btnLoad.Enabled = !(dgvAbiturients.RowCount == 0);
dgvAbiturients.Columns["Barcode"].Visible = false;
}
private void UpdateDataGrid()
{
if (StudyFormId == null || StudyBasisId == null)
{
dgvViews.DataSource = null;
return;
}
string query = string.Format(@"SELECT DISTINCT Id, Number as 'Номер представления' FROM ed.extDisEntryView
WHERE IsForeign = 1 AND StudyFormId={0} AND StudyBasisId={1} AND FacultyId= {2} {3} AND IsListener = {4} AND IsSecond = {5} AND IsReduced = {6} AND IsParallel = {7} AND StudyLevelGroupId = {8} order by 2",
StudyFormId, StudyBasisId, FacultyId,
LicenseProgramId.HasValue ? string.Format("AND LicenseProgramId = {0}", LicenseProgramId) : "",
QueryServ.StringParseFromBool(IsListener), QueryServ.StringParseFromBool(IsSecond),
QueryServ.StringParseFromBool(IsReduced), QueryServ.StringParseFromBool(IsParallel), StudyLevelGroupId);
HelpClass.FillDataGrid(dgvViews, _bdc, query, "");
}
protected override void GetSource()
{
_sQuery = @"SELECT DISTINCT extPerson.Id, extPerson.PersonNum, extPerson.FIO, extPerson.PassportData, extPerson.ForeignNationality AS NationalityName, extPerson.ForeignCountry AS CountryName, extPerson.EducDocument
FROM ed.extPerson ";
string join = "";
if (!chbShowAll.Checked)
{
join = @" LEFT JOIN ed.Abiturient ON Abiturient.PersonId = extPerson.Id
LEFT JOIN ed.Entry ON Entry.Id=Abiturient.EntryId
LEFT JOIN ed.StudyLevel ON StudyLevel.Id=Entry.StudyLevelId
WHERE Entry.IsForeign = 1 OR (Entry.Id IS NULL AND extPerson.NationalityId <> 1)";
}
HelpClass.FillDataGrid(Dgv, _bdc, _sQuery + join, "", " ORDER BY FIO");
SetVisibleColumnsAndNameColumns();
}
private void UpdateDataGridPerson()
{
string query = @"SELECT DISTINCT qAbiturient.Id, qAbiturient.Barcode FROM qAbiturient WHERE
(qAbiturient.Id IN
(
SELECT DISTINCT ab.Id FROM extAbitFileNames_All INNER JOIN qAbiturient AS ab ON extAbitFileNames_All.ApplicationId = ab.Id
WHERE (ab.DateReviewDocs IS NULL OR DateDiff(MINUTE, ab.DateReviewDocs, extAbitFileNames_All.LoadDate) > 0)
)
OR
qAbiturient.PersonId IN
(
SELECT DISTINCT pers.Id FROM extAbitFileNames_All INNER JOIN Person AS pers ON extAbitFileNames_All.PersonId = pers.Id
WHERE extAbitFileNames_All.ApplicationId IS NULL AND (pers.DateReviewDocs IS NULL OR DateDiff(MINUTE, pers.DateReviewDocs, extAbitFileNames_All.LoadDate) > 0)
))
AND qAbiturient.IsImported = 1 ";
DataSet dsAbits = _bdcInet.GetDataSet(query);
List <string> lstAbits = (from dr in dsAbits.Tables[0].AsEnumerable()
select dr["Barcode"].ToString()).ToList <string>();
if (lstAbits.Count == 0)
{
SetNullGrid();
return;
}
string s = string.Join(", ", lstAbits.ToArray());
string queryOur = string.Format(
@"SELECT extAbit.Id, extAbit.Barcode, Person.Surname + ' ' + Person.Name + ' ' + Person.SecondName as ФИО, Person.BirthDate AS Дата_рождения,
extAbit.FacultyName AS Факультет, extAbit.LicenseProgramName AS Направление, extAbit.ObrazProgramName AS Образ_программа,
extAbit.ProfileName AS Профиль, extAbit.StudyBasisName AS Основа, Person.Barcode AS PersonBarcode
FROM ed.extAbit INNER JOIN ed.Person ON extAbit.PersonId = Person.Id
WHERE 1=1 {0} AND extAbit.Barcode IN({1}) ORDER BY ФИО", GetFilterString("extAbit"), s);
HelpClass.FillDataGrid(dgvAbiturients, _bdc, queryOur, "");
dgvAbiturients.Columns["Barcode"].Visible = false;
dgvAbiturients.Columns["PersonBarcode"].Visible = false;
lblCount.Text = "Всего: " + dgvAbiturients.RowCount.ToString();
btnLoad.Enabled = !(dgvAbiturients.RowCount == 0);
}
public override void UpdateDataGrid()
{
if (StudyFormId == null || StudyBasisId == null)
{
dgvViews.DataSource = null;
return;
}
string qquery = @"SELECT DISTINCT Id, Number + ' (' + CONVERT(nvarchar, Date, 104) + ')' AS 'Номер представления'
FROM ed.extEntryView
WHERE StudyFormId={0} AND StudyBasisId={1} " + (FacultyId.HasValue? "AND FacultyId= {2} ":"") + (LicenseProgramId.HasValue ? "AND LicenseProgramId = {3} ":"") + @"AND IsListener = {4} AND IsSecond = {5}
AND IsReduced = {6} AND IsParallel = {7} AND IsForeign = {8} AND StudyLevelGroupId = {9}
order by 2";
string query = string.Format(qquery,
StudyFormId,
StudyBasisId,
FacultyId,
LicenseProgramId,
QueryServ.StringParseFromBool(IsListener),
QueryServ.StringParseFromBool(IsSecond),
QueryServ.StringParseFromBool(IsReduced),
QueryServ.StringParseFromBool(IsParallel),
QueryServ.StringParseFromBool(MainClass.dbType == PriemType.PriemForeigners),
StudyLevelGroupId);
HelpClass.FillDataGrid(dgvViews, _bdc, query, "");
if (dgvViews.Columns.Contains("Номер представления"))
{
dgvViews.Columns["Номер представления"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
if (dgvViews.Rows.Count == 0)
{
dgvViews.CurrentCell = null;
ViewProtocolInfo();
}
}
//обновление грида
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 ListPerson_Activated(object sender, EventArgs e)
{
HelpClass.FillDataGrid(dgvAbitList, _bdc, MainClass.qBuilder.GetQuery(MainClass._config.ColumnListPerson, "ed.Person"), "");
lblCount.Text = dgvAbitList.RowCount.ToString();
}
//обновление грида
public void UpdateDataGridAbits()
{
try
{
int?changeTp = ChangeTypeId;
string abitQuery = string.Format("SELECT Barcode, (Case When BackDoc = 1 then 1 else 0 end) AS BackDoc, Cast(Priority AS nvarchar(10)) AS Priority FROM ed.extAbit WHERE Barcode > 0 {0} ", GetFilterString("ed.extAbit"));
string abitQueryInet = string.Format("SELECT qAbiturient.Barcode, Cast(qAbiturient.Priority AS nvarchar(10)) AS Priority, (Case When qAbiturient.Enabled = 1 then 0 else 1 end) AS BackDoc FROM qAbiturient WHERE IsImported = 1 AND Barcode > 0 {0} ", GetFilterString("qAbiturient"));
Dictionary <int, ChangeAbitClass> abits;
Dictionary <int, ChangeAbitClass> abitsInet;
lstAbits = new Dictionary <int, ChangeAbitClass>();
switch (changeTp)
{
// приоритеты
case 1:
{
abitQuery += " AND BackDoc = 0 ";
abitQueryInet += " AND Enabled = 1 AND NOT Priority IS NULL ";
abits = GetAbitList(_bdc.GetDataSet(abitQuery));
abitsInet = GetAbitList(_bdcInet.GetDataSet(abitQueryInet));
if (abitsInet.Count == 0)
{
SetNullGrid();
return;
}
foreach (ChangeAbitClass cl in abits.Values)
{
if (!abitsInet.Keys.Contains(cl.Barcode))
{
continue;
}
ChangeAbitClass inAb = abitsInet[cl.Barcode];
if (cl.Priority != inAb.Priority)
{
lstAbits.Add(cl.Barcode, new ChangeAbitClass(cl.Barcode, inAb.Priority, cl.BackDoc));
}
}
break;
}
// забрал документы в инете
case 2:
{
abitQuery += " AND BackDoc = 0 ";
abitQueryInet += " AND Enabled = 0 ";
abits = GetAbitList(_bdc.GetDataSet(abitQuery));
abitsInet = GetAbitList(_bdcInet.GetDataSet(abitQueryInet));
if (abitsInet.Count == 0)
{
SetNullGrid();
return;
}
foreach (ChangeAbitClass cl in abits.Values)
{
if (!abitsInet.Keys.Contains(cl.Barcode))
{
continue;
}
ChangeAbitClass inAb = abitsInet[cl.Barcode];
if (!cl.BackDoc && inAb.BackDoc)
{
lstAbits.Add(cl.Barcode, new ChangeAbitClass(cl.Barcode, cl.Priority, inAb.BackDoc));
}
}
break;
}
// забрал документы в нашей базе
case 3:
{
abitQuery += " AND BackDoc = 1 ";
abitQueryInet += " AND Enabled = 1 ";
abits = GetAbitList(_bdc.GetDataSet(abitQuery));
abitsInet = GetAbitList(_bdcInet.GetDataSet(abitQueryInet));
if (abitsInet.Count == 0)
{
SetNullGrid();
return;
}
foreach (ChangeAbitClass cl in abits.Values)
{
if (!abitsInet.Keys.Contains(cl.Barcode))
{
continue;
}
ChangeAbitClass inAb = abitsInet[cl.Barcode];
if (cl.BackDoc && !inAb.BackDoc)
{
lstAbits.Add(cl.Barcode, new ChangeAbitClass(cl.Barcode, cl.Priority, cl.BackDoc));
}
}
break;
}
}
if (lstAbits.Count == 0)
{
SetNullGrid();
return;
}
string query = string.Format(
@"SELECT ed.extAbit.Id, Person.Surname + ' ' + Person.Name + ' ' + Person.SecondName as ФИО,
Person.BirthDate AS Дата_рождения, ed.extAbit.Barcode, Person.Barcode AS PersonBarcode,
ed.extAbit.FacultyName as Факультет, ed.extAbit.LicenseProgramName AS Направление, ed.extAbit.ObrazProgramName AS Образ_программа,
ed.extAbit.ProfileName AS Профиль, ed.extAbit.StudyBasisName AS Основа
FROM ed.extAbit INNER JOIN ed.Person ON ed.extAbit.PersonId = Person.Id
WHERE 1=1 {0} AND ed.extAbit.Barcode IN({1}) ORDER BY ФИО", GetFilterString("ed.extAbit"), GetListKeys(lstAbits));
HelpClass.FillDataGrid(dgvAbiturients, _bdc, query, "");
dgvAbiturients.Columns["Barcode"].Visible = false;
dgvAbiturients.Columns["PersonBarcode"].Visible = false;
lblCount.Text = "Всего: " + dgvAbiturients.RowCount.ToString();
}
catch (Exception exc)
{
WinFormsServ.Error(exc.Message);
}
btnLoad.Enabled = !(dgvAbiturients.RowCount == 0);
}
//обновление грида
public virtual void UpdateDataGrid()
{
//скрыли/показали кнопку, если надо
if (ExamVedId == null)
{
btnChange.Enabled = false;
btnDelete.Enabled = false;
btnCreateAdd.Enabled = false;
btnLock.Enabled = false;
lblLocked.Visible = false;
tbCountCell.Enabled = false;
btnPrintSticker.Enabled = false;
dgvList.DataSource = null;
dgvList.Update();
return;
}
else
{
if (cbExamVed.SelectedItem.ToString().Contains("дополнительная"))
{
btnCreateAdd.Enabled = false;
}
else
{
btnCreateAdd.Enabled = true;
}
using (PriemEntities context = new PriemEntities())
{
bool isLocked = (from ev in context.extExamsVed
where ev.Id == ExamVedId
select ev.IsLocked).FirstOrDefault();
if (isLocked)
{
lblLocked.Visible = true;
btnChange.Enabled = false;
btnDelete.Enabled = false;
btnLock.Enabled = false;
btnPrintSticker.Enabled = true;
tbCountCell.Enabled = true;
}
else
{
lblLocked.Visible = false;
btnChange.Enabled = true;
btnDelete.Enabled = true;
btnLock.Enabled = true;
btnPrintSticker.Enabled = false;
tbCountCell.Enabled = false;
}
}
}
//обработали номер
string sFilters = string.Format("WHERE ed.ExamsVedHistory.ExamsVedId = '{0}' ", ExamVedId.ToString());
if (!dgvList.Columns.Contains("Number"))
{
dgvList.Columns.Add("Number", "№");
dgvList.Update();
}
dgvList.Columns["Number"].DisplayIndex = 0;
HelpClass.FillDataGrid(dgvList, bdc, sQuery, sFilters, sOrderby);
}
public override void UpdateDataGrid()
{
try
{
string sOrderBy = string.Empty;
if (MainClass.dbType == PriemType.PriemMag)
{
sOrderBy = " ORDER BY comp , noexamssort, ed.extAbitMarksSum.TotalSum desc, preimsort desc, ed.qAbiturient.Coefficient, ed.extAbitMarksSum.TotalCount desc, ФИО";
}
else
{
sOrderBy = " ORDER BY comp , noexamssort, ed.extAbitMarksSum.TotalSum desc, preimsort desc, DopOrProfSort desc, olymp, attestat, attAvg desc, ed.qAbiturient.Coefficient, ed.extAbitMarksSum.TotalCount desc, ФИО";
}
string totalQuery = null;
iPlanValue = GetPlanValueAndCheckLock();
if (chbFix.Checked)
{
if (MainClass.dbType == PriemType.PriemMag)
{
_queryOrange = @", CASE WHEN EXISTS(SELECT PersonId FROM ed.hlpPersonsWithOriginals WHERE PersonId = ed.qAbiturient.PersonId AND EntryId <> ed.qAbiturient.EntryId) then 1 else 0 end as orange ";
}
else
{
_queryOrange = @", CASE WHEN EXISTS(SELECT ed.extEntryView.Id FROM ed.extEntryView INNER JOIN ed.Abiturient a ON ed.extEntryView.AbiturientId = a.Id WHERE a.PersonId = ed.qAbiturient.PersonId) then 1 else 0 end as orange ";
}
string queryFix = _queryBody + _queryOrange +
@" FROM ed.qAbiturient
INNER JOIN ed.extPerson ON ed.extPerson.Id = ed.qAbiturient.PersonId
INNER JOIN ed.Competition ON ed.Competition.Id = ed.qAbiturient.CompetitionId
INNER JOIN ed.Fixieren ON ed.Fixieren.AbiturientId=ed.qAbiturient.Id
LEFT JOIN ed.hlpEntryWithAddExams ON hlpEntryWithAddExams.EntryId = qAbiturient.EntryId
LEFT JOIN ed.FixierenView ON ed.Fixieren.FixierenViewId=ed.FixierenView.Id
LEFT JOIN ed.hlpAbiturientProfAdd ON ed.hlpAbiturientProfAdd.Id = ed.qAbiturient.Id
LEFT JOIN ed.hlpAbiturientProf ON ed.hlpAbiturientProf.Id = ed.qAbiturient.Id
LEFT JOIN ed.extAbitMarksSum ON ed.qAbiturient.Id = ed.extAbitMarksSum.Id";
string whereFix = string.Format(@" WHERE ed.FixierenView.StudyLevelGroupId = {10} AND ed.FixierenView.StudyFormId={0} AND ed.FixierenView.StudyBasisId={1} AND ed.FixierenView.FacultyId={2}
AND ed.FixierenView.LicenseProgramId={3} AND ed.FixierenView.ObrazProgramId={4} {5} AND ed.FixierenView.IsCel = {6}
AND ed.FixierenView.IsSecond = {7} AND ed.FixierenView.IsReduced = {8} AND ed.FixierenView.IsParallel = {9} ",
StudyFormId, StudyBasisId, FacultyId, LicenseProgramId, ObrazProgramId, ProfileId == null ? " AND ed.FixierenView.ProfileId IS NULL" : "AND ed.FixierenView.ProfileId='" + ProfileId + "'",
QueryServ.StringParseFromBool(false), QueryServ.StringParseFromBool(IsSecond), QueryServ.StringParseFromBool(IsReduced), QueryServ.StringParseFromBool(IsParallel), StudyLevelGroupId);
totalQuery = queryFix + whereFix + sOrderBy;
}
else
{
string sFilters = GetFilterString();
//не забрали доки
sFilters += " AND (ed.qAbiturient.BackDoc=0) ";
sFilters += " AND ed.qAbiturient.Id NOT IN (select abiturientid from ed.extentryview) ";
// кроме бэ преодолены мин планки
sFilters += " AND ((CompetitionId=1 OR CompetitionId=8) OR hlpMinMarkAbiturient.Id IS NULL )";
string examsCnt = _bdc.GetStringValue(string.Format(" SELECT Count(Id) FROM ed.extExamInEntry WHERE EntryId='{0}' ", EntryId.ToString()));
if (MainClass.dbType == PriemType.PriemMag)
{
_queryOrange = @", CASE WHEN EXISTS(SELECT PersonId FROM ed.hlpPersonsWithOriginals WHERE PersonId = ed.qAbiturient.PersonId AND EntryId <> ed.qAbiturient.EntryId) then 1 else 0 end as orange ";
// кроме бэ нужное кол-во оценок есть
sFilters += " AND ((CompetitionId=1 OR CompetitionId=8) OR ed.extAbitMarksSum.TotalCount = " + examsCnt + " ) ";
totalQuery = _queryBody + _queryOrange + _queryFrom + sFilters + sOrderBy;
}
else
{
_queryOrange = @", CASE WHEN EXISTS(SELECT ed.extEntryView.Id FROM ed.extEntryView INNER JOIN ed.Abiturient a ON ed.extEntryView.AbiturientId = a.Id WHERE a.PersonId = ed.qAbiturient.PersonId) then 1 else 0 end as orange ";
//if(StudyBasisId == 1)
// sFilters += " AND qAbiturient.Id NOT IN (SELECT AbiturientId FROM ed._FirstWaveGreenBackUp2)";
sFilters += " AND ed.qAbiturient.CompetitionId NOT IN (6, 1, 2, 7) ";
// кроме бэ и тех, у кого нет сертификатов и оценок нужное кол-во оценок есть
sFilters += @" AND ((CompetitionId=1 OR CompetitionId=8)
OR (ed.qAbiturient.PersonId NOT IN (SELECT PersonId FROM ed.EgeCertificate)
AND ed.qAbiturient.Id NOT IN (SELECT abiturientid from ed.Mark where IsFromEge = 1) and ed.extPerson.EgeInSPbgu = 0 and ed.qAbiturient.IsSecond = 0 and ed.qAbiturient.IsReduced = 0 and ed.qAbiturient.IsParallel = 0)
OR ed.extAbitMarksSum.TotalCount = " + examsCnt + " ) ";
totalQuery = _queryBody + _queryOrange + _queryFrom + sFilters + sOrderBy;
}
}
if (!dgvAbits.Columns.Contains("Number"))
{
dgvAbits.Columns.Add("Number", "№ п/п");
}
HelpClass.FillDataGrid(dgvAbits, _bdc, totalQuery, "");
dgvAbits.Columns["Id"].Visible = false;
dgvAbits.Columns["comp"].Visible = false;
dgvAbits.Columns["noexamssort"].Visible = false;
dgvAbits.Columns["preimsort"].Visible = false;
dgvAbits.Columns["olymp"].Visible = false;
dgvAbits.Columns["attestat"].Visible = false;
dgvAbits.Columns["attAvg"].Visible = false;
dgvAbits.Columns["DopOrProfSort"].Visible = false;
dgvAbits.Columns["orange"].Visible = false;
if (MainClass.dbType == PriemType.PriemMag)
{
dgvAbits.Columns["Серия аттестата"].Visible = false;
dgvAbits.Columns["Медалист"].HeaderText = "Красный диплом";
}
else
{
dgvAbits.Columns["Серия диплома"].Visible = false;
}
foreach (DataGridViewColumn column in dgvAbits.Columns)
{
column.SortMode = DataGridViewColumnSortMode.NotSortable;
}
lblCount.Text = dgvAbits.RowCount.ToString() + " Cвободных мест: " + iPlanValue;
}
catch (Exception ex)
{
WinFormsServ.Error("Ошибка при обновлении списка.", ex);
}
}