void UpdateRight()
{
string ids = GetSelectedIdList();
string filt = string.IsNullOrEmpty(ids) ? "" : string.Format(" AND ed.qAbiturient.Id NOT IN ({0}) ", ids);
dgvRight.Rows.Clear();
DataTable dtAbits = new DataTable();
string query = string.Format(@"SELECT DISTINCT ObrazProgramId, ProfileId
FROM ed.qEntry WHERE qEntry.IsForeign = 1 AND qEntry.StudyLevelGroupId = {7} AND qEntry.FacultyId={0} AND qEntry.StudyFormId={1} AND
qEntry.StudyBasisId={2} {3} AND qEntry.IsSecond = {4} AND qEntry.IsReduced = {5} AND qEntry.IsParallel = {6}",
_facultyId, _studyFormId, _studyBasisId, _licenseProgramId.HasValue ? string.Format(" AND ed.qEntry.LicenseProgramId='{0}'", _licenseProgramId.Value) : "",
QueryServ.StringParseFromBool(_isSecond), QueryServ.StringParseFromBool(_isReduced), QueryServ.StringParseFromBool(_isParallel), _studyLevelGroupId);
DataSet dsPrograms = MainClass.Bdc.GetDataSet(query);
foreach (DataRow dr in dsPrograms.Tables[0].Rows)
{
string obProg = dr["ObrazProgramId"].ToString();
string spec = dr["ProfileId"].ToString();
string sQueryBody = "SELECT DISTINCT '' AS Sum, qAbiturient.Id as Id, qAbiturient.BAckDoc as backdoc, " +
" 'false' as Red, qAbiturient.RegNum as Рег_Номер, " +
" extPerson.FIO as ФИО, " +
" extPerson.EducDocument as Документ_об_образовании, " +
" extPerson.PassportSeries + ' №' + extPerson.PassportNumber as Паспорт, " +
" LicenseProgramCode + ' ' + LicenseProgramName + ' ' +(Case when NOT ed.qAbiturient.ProfileId IS NULL then ProfileName else ObrazProgramCrypt + ' ' + ObrazProgramName end) as Направление, " +
" Competition.NAme as Конкурс, qAbiturient.BackDoc " +
" FROM ed.qAbiturient " +
" INNER JOIN ed.extPerson ON qAbiturient.PersonId = extPerson.Id " +
" INNER JOIN ed.extEnableProtocol ON ed.qAbiturient.Id=ed.extEnableProtocol.AbiturientId " +
" LEFT JOIN ed.Competition ON Competition.Id = qAbiturient.CompetitionId ";
string sQueryJoinFW = string.Empty;
string sFilter = GetTotalFilter() + filt;
sFilter += " AND qAbiturient.ObrazProgramId = " + obProg;
sFilter += " AND qAbiturient.IsForeign = 1 ";
sFilter += string.IsNullOrEmpty(spec) ? " AND ed.qAbiturient.ProfileId IS NULL " : string.Format(" AND ed.qAbiturient.ProfileId='{0}'", spec);
string orderBy = " ORDER BY ФИО ";
DataTable dtProg = MainClass.Bdc.GetDataSet(sQueryBody + sQueryJoinFW + sFilter + orderBy).Tables[0];
dtAbits.Merge(dtProg);
}
FillGrid(dgvRight, dtAbits);
}
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 InitControls()
{
sQuery = string.Format("SELECT DISTINCT extAbitMarksSum.TotalSum as Sum, extAbit.Id as Id, extAbit.BAckDoc as backdoc, " +
" 'false' as Red, extAbit.RegNum as Рег_Номер, " +
" extPerson.FIO as ФИО, " +
" extPerson.EducDocument as Документ_об_образовании, " +
" extPerson.PassportSeries + ' №' + extPerson.PassportNumber as Паспорт, " +
" extAbit.ObrazProgramName + ' ' + (Case when extAbit.ProfileName IS NULL then '' else extAbit.ProfileName end) as Направление, " +
" Competition.NAme as Конкурс, extAbit.BackDoc " +
" FROM ed.extAbit INNER JOIN ed.extPerson ON extAbit.PersonId = extPerson.Id " +
" INNER JOIN ed.extEnableProtocol ON extAbit.Id = extEnableProtocol.AbiturientId " +
" INNER JOIN ed.extEntryView ON extAbit.Id = extEntryView.AbiturientId " +
" INNER JOIN ed.extAbitMarksSum ON extAbit.Id = extAbitMarksSum.Id " +
" LEFT JOIN ed.Competition ON Competition.Id = extAbit.CompetitionId ");
string q = string.Format(@"SELECT DISTINCT CONVERT(varchar(100), Id) AS Id, Number as Name FROM ed.extEntryView WHERE IsForeign = 1 AND Excluded=0 AND IsOld=0
AND FacultyId ={0} AND StudyFormId = {1} AND StudyBasisId = {2} {3} AND IsSecond = {4} AND IsReduced = {5} AND IsParallel = {6} AND IsListener = {7} AND StudyLevelGroupId = {8}",
_facultyId, _studyFormId, _studyBasisId,
_licenseProgramId.HasValue ? string.Format("AND LicenseProgramId = {0}", _licenseProgramId) : "",
QueryServ.StringParseFromBool(_isSecond),
QueryServ.StringParseFromBool(_isReduced), QueryServ.StringParseFromBool(_isParallel), QueryServ.StringParseFromBool(_isListener), _studyLevelGroupId);
ComboServ.FillCombo(cbHeaders, HelpClass.GetComboListByQuery(q), false, false);
cbHeaders.Visible = true;
lblHeaderText.Text = "Из представления к зачислению №";
chbInostr.Visible = true;
cbHeaders.SelectedIndexChanged += new EventHandler(cbHeaders_SelectedIndexChanged);
chbInostr.CheckedChanged += new System.EventHandler(UpdateGrids);
base.InitControls();
this.Text = "Приказ об исключении ";
this.chbEnable.Text = "Добавить всех выбранных слева абитуриентов в приказ об исключении";
this.chbFilter.Visible = false;
}
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();
}
}
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 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);
}
}
string GetTotalFilter(bool header)
{
string sFilter = string.Empty;
//обработали Направление
if (_licenseProgramId != null)
{
sFilter += " AND ed.qAbiturient.LicenseProgramId = " + _licenseProgramId;
}
sFilter += " AND qAbiturient.IsSecond = " + QueryServ.StringParseFromBool(_isSecond);
sFilter += " AND qAbiturient.IsReduced = " + QueryServ.StringParseFromBool(_isReduced);
sFilter += " AND qAbiturient.IsParallel = " + QueryServ.StringParseFromBool(_isParallel);
//обработали слушатель
sFilter += " AND qAbiturient.IsListener = " + QueryServ.StringParseFromBool(_isListener);
sFilter += " AND qAbiturient.BackDoc = 0 ";
sFilter += " AND (ed.qAbiturient.Id NOT IN (SELECT AbiturientId FROM ed.extEntryView WHERE IsListener = 0) OR qAbiturient.IsListener = 1)";
sFilter += "AND ((qAbiturient.IsListener = 0 AND qAbiturient.IsSecond = 0 AND qAbiturient.IsReduced = 0 AND qAbiturient.IsParallel = 0 AND qAbiturient.HasOriginals > 0) OR qAbiturient.IsListener = 1 OR qAbiturient.IsSecond = 1 OR qAbiturient.IsReduced = 1 OR qAbiturient.IsParallel = 1 OR qAbiturient.IsPaid = 1)";
if (_studyBasisId == 2)
{
sFilter += " AND qAbiturient.IsPaid > 0 ";
//sFilter += " AND EXISTS (SELECT Top(1) ed.PaidData.Id FROM ed.PaidData WHERE ed.PaidData.AbiturientId = qAbiturient.Id) ";
}
if (header)
{
switch (HeaderId)
{
case 1:
sFilter += " AND qAbiturient.CompetitionId=1 ";
sFilter += " AND PersonId IN (SELECT PersonId FROM ed.Olympiads WHERE OlympTypeId=1) ";
break;
case 2:
sFilter += " AND qAbiturient.CompetitionId=1 ";
sFilter += " AND PersonId IN (SELECT PersonId FROM ed.Olympiads WHERE OlympValueId=6 AND OlympTypeId=2) ";
break;
case 3:
//sFilter += " AND ed.qAbiturient.CompetitionId=1 ";
sFilter += " AND PersonId IN (SELECT PersonId FROM ed.Olympiads WHERE OlympValueId=5 AND OlympTypeId=2) ";
break;
case 4:
//sFilter += " AND ed.qAbiturient.CompetitionId=1 ";
sFilter += " AND PersonId IN (SELECT PersonId FROM ed.Olympiads WHERE OlympValueId=6 AND OlympTypeId IN (3,4)) ";
break;
case 5:
//sFilter += " AND ed.qAbiturient.CompetitionId=1 ";
sFilter += " AND PersonId IN (SELECT PersonId FROM ed.Olympiads WHERE OlympValueId=5 AND OlympTypeId IN (3,4)) ";
break;
case 6:
sFilter += " AND qAbiturient.CompetitionId=2 ";
break;
case 7:
sFilter += " AND qAbiturient.CompetitionId = 6";
break;
case 8:
sFilter += " AND qAbiturient.CompetitionId NOT IN (1,2,6,7,8) ";
break;
case 9:
sFilter += " AND qAbiturient.CompetitionId IN (1,8) ";
break;
case 10:
sFilter += " AND qAbiturient.CompetitionId IN (2,7) ";
break;
}
}
return(GetWhereClause("ed.qAbiturient") + sFilter);
}
