public qAbiturient GetAbitByBarcode(int fileNum)
{
try
{
string abitQueryInet = @"SELECT qAbiturient.Barcode, EntryId, qAbiturient.HostelEduc,
(Case when qAbiturient.Enabled = 1 then 0 else 1 end) AS BackDoc,
qAbiturient.DateOfDisable AS BackDocDate, qAbiturient.DateOfStart AS DocDate,
qAbiturient.Priority, qAbiturient.LicenseProgramId, qAbiturient.ObrazProgramId,
qAbiturient.ProfileId, qAbiturient.FacultyId, qAbiturient.StudyFormId,
qAbiturient.StudyBasisId, qAbiturient.IsSecond,
qAbiturient.IsGosLine, qAbiturient.CommitId, qAbiturient.CommitNumber
FROM qAbiturient WHERE 0=0";
DataSet ds = _bdcInet.GetDataSet(abitQueryInet + " AND qAbiturient.Barcode = " + fileNum);
if (ds.Tables[0].Rows.Count == 0)
{
throw new Exception("Записей не найдено");
}
DataRow row = ds.Tables[0].Rows[0];
qAbiturient abit = new qAbiturient();
abit.IsSecond = (bool)row["IsSecond"];
abit.EntryId = (Guid)row["EntryId"];
abit.CommitId = (Guid)row["CommitId"];
abit.CommitNumber = (int?)row["CommitNumber"];
abit.FacultyId = (int)row["FacultyId"];
abit.LicenseProgramId = (int)row["LicenseProgramId"];
abit.ObrazProgramId = (int)row["ObrazProgramId"];
abit.ProfileId = (Guid?)(Util.ToNullObject(row["ProfileId"]));
abit.StudyFormId = (int)row["StudyFormId"];
abit.StudyBasisId = (int)row["StudyBasisId"];
abit.IsGosLine = QueryServ.ToBoolValue(row["IsGosLine"]);
abit.BackDoc = QueryServ.ToBoolValue(row["BackDoc"]);
abit.BackDocDate = row.Field <DateTime?>("BackDocDate");
abit.DocDate = (DateTime)row["DocDate"];
int prior;
if (!int.TryParse(row["Priority"].ToString(), out prior))
{
abit.Priority = null;
}
else
{
abit.Priority = prior;
}
abit.Barcode = int.Parse(row["Barcode"].ToString());
return(abit);
}
catch
{
return(null);
}
}
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 Dictionary <int, ChangeAbitClass> GetAbitList(DataSet dsAbits)
{
Dictionary <int, ChangeAbitClass> abits = (from dr in dsAbits.Tables[0].AsEnumerable()
select new
{
key = dr.Field <int>("Barcode"),
val = new ChangeAbitClass(
dr.Field <int>("Barcode"),
dr.Field <string>("Priority"),
QueryServ.ToBoolValue(dr.Field <int>("BackDoc"))
)
}).ToDictionary(n => n.key, n => n.val);
return(abits);
}
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 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);
}
}
}
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 extPersonAll GetPersonByBarcode(int fileNum)
{
try
{
string personQueryInet =
@"SELECT Id, Barcode, Name, SecondName, Surname, BirthDate, BirthPlace, Sex,
PassportTypeId, PassportSeries, PassportNumber, PassportAuthor, PassportDate,
PassportCode, '' AS PersonalCode, CountryId, NationalityId AS NationalityId, RegionId, Phone, Mobiles, Email,
Code, City, Street, House, Korpus, Flat,
CodeReal, CityReal, StreetReal, HouseReal, KorpusReal, FlatReal,
HostelAbit, HostelEduc, LanguageId,
SchoolCity, SchoolTypeId, SchoolName, SchoolNum, SchoolExitYear, IsExcellent,
CountryEducId, RegionEducId, AttestatRegion, AttestatSeries, AttestatNumber,
EducationDocumentSeries AS DiplomSeries, EducationDocumentNumber AS DiplomNum, AvgMark AS SchoolAVG,
(case when SchoolTypeId=1 then '' else SchoolName end) AS HighEducation, HEProfession AS HEProfession,
HEQualification AS HEQualification, DiplomaTheme AS HEWork,
EducationStart AS HEEntryYear, HEExitYear, HEStudyFormId, Parents AS PersonInfo, AddInfo AS ExtraInfo,
IsEqual, EqualDocumentNumber, HasTRKI, TRKICertificateNumber,
EnglishMark, StartEnglish
FROM extForeignPerson
WHERE 0=0";
DataSet ds = _bdcInet.GetDataSet(personQueryInet + " AND extForeignPerson.Barcode = " + fileNum);
if (ds.Tables[0].Rows.Count == 0)
{
throw new Exception("Записей не найдено");
}
DataRow row = ds.Tables[0].Rows[0];
extPersonAll pers = new extPersonAll();
pers.Id = (Guid)row["Id"];
pers.Barcode = (int?)row["Barcode"];
pers.FIO = Util.GetFIO(row["Surname"].ToString(), row["Name"].ToString(), row["SecondName"].ToString());
pers.Name = row["Name"].ToString();
pers.SecondName = row["SecondName"].ToString();
pers.Surname = row["Surname"].ToString();
pers.BirthDate = row.Field <DateTime?>("BirthDate") ?? DateTime.Now.Date;
pers.BirthPlace = row["BirthPlace"].ToString();
pers.PassportTypeId = (int)(row["PassportTypeId"]);
pers.PassportSeries = row["PassportSeries"].ToString();
pers.PassportNumber = row["PassportNumber"].ToString();
pers.PassportAuthor = row["PassportAuthor"].ToString();
pers.PassportDate = QueryServ.ToNullDateTimeValue(row["PassportDate"]);
pers.PassportCode = row["PassportCode"].ToString();
pers.PersonalCode = row["PersonalCode"].ToString();
pers.Sex = QueryServ.ToBoolValue(row["Sex"]);
pers.ForeignCountryId = (int?)(Util.ToNullObject(row["CountryId"]));
pers.ForeignNationalityId = (int)(row["NationalityId"]);
pers.RegionId = (int?)(Util.ToNullObject(row["RegionId"]));
pers.Phone = row["Phone"].ToString();
pers.Mobiles = row["Mobiles"].ToString();
pers.Email = row["Email"].ToString();
pers.Code = row["Code"].ToString();
pers.City = row["City"].ToString();
pers.Street = row["Street"].ToString();
pers.House = row["House"].ToString();
pers.Korpus = row["Korpus"].ToString();
pers.Flat = row["Flat"].ToString();
pers.CodeReal = row["CodeReal"].ToString();
pers.CityReal = row["CityReal"].ToString();
pers.StreetReal = row["StreetReal"].ToString();
pers.HouseReal = row["HouseReal"].ToString();
pers.KorpusReal = row["KorpusReal"].ToString();
pers.FlatReal = row["FlatReal"].ToString();
pers.HostelAbit = QueryServ.ToBoolValue(row["HostelAbit"]);
pers.HostelEduc = QueryServ.ToBoolValue(row["HostelEduc"]);
pers.IsEqual = QueryServ.ToBoolValue(row["IsEqual"]);
pers.HasTRKI = QueryServ.ToBoolValue(row["HasTRKI"]);
pers.EqualDocumentNumber = row["EqualDocumentNumber"].ToString();
pers.TRKICertificateNumber = row["TRKICertificateNumber"].ToString();
pers.HasAssignToHostel = false;
pers.HasExamPass = false;
pers.IsExcellent = QueryServ.ToBoolValue(row["IsExcellent"]);
pers.LanguageId = (int?)(Util.ToNullObject(row["LanguageId"]));
pers.SchoolCity = row["SchoolCity"].ToString();
pers.SchoolTypeId = (int?)(Util.ToNullObject(row["SchoolTypeId"]));
pers.SchoolName = row["SchoolName"].ToString();
pers.SchoolNum = row["SchoolNum"].ToString();
int SchoolExitYear = 0;
int.TryParse(row["SchoolExitYear"].ToString(), out SchoolExitYear);
pers.SchoolExitYear = SchoolExitYear;
pers.ForeignCountryEducId = (int?)(Util.ToNullObject(row["CountryEducId"]));
pers.RegionEducId = (int?)(Util.ToNullObject(row["RegionEducId"]));
pers.AttestatRegion = row["AttestatRegion"].ToString();
pers.AttestatSeries = row["AttestatSeries"].ToString();
pers.AttestatNum = row["AttestatNumber"].ToString();
pers.DiplomSeries = row["DiplomSeries"].ToString();
pers.DiplomNum = row["DiplomNum"].ToString();
pers.StartEnglish = QueryServ.ToBoolValue(row["StartEnglish"]);
int EnglishMark = 0;
int.TryParse(row["EnglishMark"].ToString(), out EnglishMark);
pers.EnglishMark = EnglishMark == 0 ? null : (int?)EnglishMark;
double avg;
if (!double.TryParse(row["SchoolAVG"].ToString(), out avg))
{
pers.SchoolAVG = null;
}
else
{
pers.SchoolAVG = avg;
}
pers.HighEducation = row["HighEducation"].ToString();
pers.HEProfession = row["HEProfession"].ToString();
pers.HEQualification = row["HEQualification"].ToString();
pers.HEEntryYear = (int?)(Util.ToNullObject(row["HEEntryYear"]));
pers.HEExitYear = (int?)(Util.ToNullObject(row["HEExitYear"]));
pers.HEStudyFormId = (int?)(Util.ToNullObject(row["HEStudyFormId"]));
pers.HEWork = row["HEWork"].ToString();
pers.PersonInfo = row["PersonInfo"].ToString();
pers.ExtraInfo = row["ExtraInfo"].ToString();
DataSet dsWork = _bdcInet.GetDataSet(string.Format(@"
SELECT PersonWork.WorkPlace + ', ' + PersonWork.WorkProfession + ', ' + PersonWork.WorkSpecifications + ' стаж: ' + PersonWork.Stage AS Work,
PersonWork.WorkPlace + ', ' + PersonWork.WorkProfession + ', ' + PersonWork.WorkSpecifications AS Place, PersonWork.Stage
FROM PersonWork WHERE PersonWork.PersonId = '{0}'", pers.Id));
if (dsWork.Tables[0].Rows.Count == 0)
{
pers.Stag = string.Empty;
pers.WorkPlace = string.Empty;
}
else if (dsWork.Tables[0].Rows.Count == 1)
{
pers.Stag = dsWork.Tables[0].Rows[0]["Stage"].ToString();
pers.WorkPlace = dsWork.Tables[0].Rows[0]["Place"].ToString();
}
else
{
string work = string.Empty;
foreach (DataRow dr in dsWork.Tables[0].Rows)
{
work += dr["Work"].ToString() + ";" + Environment.NewLine;
}
pers.WorkPlace = work;
}
DataSet dsScienceWork = _bdcInet.GetDataSet(string.Format(@"
SELECT ScienceWorkType.Name + ': ' + PersonScienceWork.WorkInfo AS ScienseWork
FROM PersonScienceWork LEFT JOIN ScienceWorkType ON PersonScienceWork.WorkTypeId = ScienceWorkType.Id WHERE PersonScienceWork.PersonId = '{0}'", pers.Id));
if (dsScienceWork.Tables[0].Rows.Count == 0)
{
pers.ScienceWork = string.Empty;
}
else
{
string work = string.Empty;
foreach (DataRow dr in dsScienceWork.Tables[0].Rows)
{
work += dr["ScienseWork"].ToString() + ";" + Environment.NewLine;
}
pers.ScienceWork = work;
}
return(pers);
}
catch
{
return(null);
}
}
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);
}
//миграция
private void MigrateAbits()
{
string query = string.Format("SELECT DISTINCT ed.extAbit.Id, ed.extAbit.RegNum, ed.Person.Name, ed.Person.SecondName, ed.Person.Surname, " +
"ed.Person.BirthDate, ed.Person.BirthPlace, ed.Person.Sex, " +
"ed.Person.PassportTypeId, ed.Person.PassportSeries, ed.Person.PassportNumber, ed.Person.PassportAuthor, ed.Person.PassportDate, " +
"ed.Person.NationalityId, ed.Person.RegionId, ed.Person.Phone, ed.Person.Mobiles, " +
"ed.extAbit.LanguageId, ed.Person.Privileges, " +
"ed.Person_Contacts.Code, Person_Contacts.City, Person_Contacts.Street, Person_Contacts.House, Person_Contacts.Korpus, Person_Contacts.Flat, " +
"ed.Person_Contacts.CodeReal, ed.Person_Contacts.CityReal, ed.Person_Contacts.StreetReal, ed.Person_Contacts.HouseReal, ed.Person_Contacts.KorpusReal, ed.Person_Contacts.FlatReal, " +
"ed.Person_EducationInfo.SchoolTypeId, ed.Person_EducationInfo.SchoolCity, ed.Person_EducationInfo.SchoolName, ed.Person_EducationInfo.SchoolNum, ed.Person_EducationInfo.SchoolExitYear, " +
"ed.Person_EducationInfo.AttestatRegion, ed.Person_EducationInfo.AttestatSeries, ed.Person_EducationInfo.AttestatNum, (case when ed.extAbit.AttDocOrigin = 1 then 'false' else 'true' end) AS CopyAtt, " +
"ed.Person_EducationInfo.DiplomSeries, ed.Person_EducationInfo.DiplomNum, " +
"ed.Person_EducationInfo.IsExcellent, ed.Country.Name as Nation, " +
"ed.extAbit.FacultyId, ed.extAbit.StudyFormId, ed.extAbit.StudyBasisId, " +
"ed.extAbit.LicenseProgramId, ed.extAbit.ProfileId, ed.extAbit.CompetitionId, ed.extAbit.StudyNumber, " +
"ed.extAbit.ObrazProgramName, ed.extAbit.ObrazProgramCrypt, " +
"ed.extAbit.DocDate, ed.extAbit.HostelEduc, ed.extAbit.IsListener, ed.extAbit.StudyPlanNumber, " +
"(Case When ed.extAbit.IsSecond = 1 then 1 else (case when ed.extabit.Isreduced = 1 then 2 else (case when ed.extabit.isparallel = 1 then 3 else 0 end) end) end) AS ListenerTypeId, " +
"ed.extEntryView.Id AS EntryProtId, ed.Person.DiplomSeries, ed.Person.DiplomNum, ed.Person.HEExitYear " +
"FROM ed.extAbit INNER JOIN ed.Person ON ed.extAbit.PersonId = ed.Person.Id " +
"Left join ed.Person_EducationInfo on ed.Person_EducationInfo.PersonId = ed.Person.Id" +
"Left join ed.Person_Contacts on ed.Person_Contacts.PersonId = ed.Person.Id" +
"INNER JOIN ed.extEntryView ON ed.extEntryView.AbiturientId = ed.extAbit.Id " +
"INNER JOIN ed.OrderNumbers ON ed.extEntryView.Id = ed.OrderNumbers.ProtocolId " +
"LEFT JOIN ed.Country ON ed.Country.Id=ed.Person.NationalityId " +
"WHERE ed.extAbit.Id IN ({1}) {0}", GetFilter("ed.extAbit"), Util.BuildStringWithCollectionWithApps(_slIds.Keys));
//string query = string.Format("SELECT DISTINCT Abiturient.SectionId,Abiturient.CompetitionId,case when Abiturient.AttestatCopy>0 then 1 else 0 end as AttestatCopy, Abiturient.SchoolTypeId,Abiturient.ExitYear,Abiturient.FileNum, Abiturient.FacultyId, Abiturient.StudyNumber, Abiturient.Surname, Abiturient.Name, Abiturient.Patronymic, Abiturient.BirthDate, Case When Abiturient.Sex>0 then 1 else 0 end as Sex , Abiturient.Privileges, Abiturient.PasswordTypeId, Abiturient.PaswSeries, Abiturient.PaswNumber, Abiturient.PaswDate,Abiturient.PaswAuthor, Abiturient.ZipCode, Abiturient.Adress, Abiturient.LifeAddress,Abiturient.Phone, Abiturient.CitizenId, Abiturient.RegionId,Abiturient.LanguageId, Abiturient.DocDate,Abiturient.AttestatSeries,Abiturient.AttestatNum,Abiturient.SchoolCity, Abiturient.SchoolName, Abiturient.SchoolNum,Abiturient.ProfessionId,Abiturient.SpecializationId,Abiturient.StudyFormId,Abiturient.GroupNum, qAbitProtocols.EntryOrderId FROM Abiturient INNER JOIN qAbitProtocols ON qAbitProtocols.Id = Abiturient.Id INNER JOIN Protocol ON qAbitProtocols.EntryOrderId = Protocol.Id WHERE NOT qAbitProtocols.EntryOrderId IS NULL AND NOT Protocol.OrderName IS NULL ") + GetFilter("Abiturient");
//string query = "SELECT Abiturient.SectionId,Abiturient.CompetitionId,case when Abiturient.AttestatCopy>0 then 1 else 0 end as AttestatCopy, Abiturient.SchoolTypeId,Abiturient.ExitYear,Abiturient.FileNum, Abiturient.FacultyId, Abiturient.StudyNumber, Abiturient.Surname, Abiturient.Name, Abiturient.Patronymic, Abiturient.BirthDate, Case When Abiturient.Sex>0 then 1 else 0 end as Sex , Abiturient.Privileges, Abiturient.PasswordTypeId, Abiturient.PaswSeries, Abiturient.PaswNumber, Abiturient.PaswDate,Abiturient.PaswAuthor, Abiturient.ZipCode, Abiturient.Adress, Abiturient.LifeAddress,Abiturient.Phone, Abiturient.CitizenId, Abiturient.RegionId,Abiturient.LanguageId, Abiturient.DocDate,Abiturient.AttestatSeries,Abiturient.AttestatNum,Abiturient.SchoolCity, Abiturient.SchoolName, Abiturient.SchoolNum,Abiturient.ProfessionId,Abiturient.SpecializationId,Abiturient.StudyFormId,Abiturient.GroupNum, qAbitProtocols.EntryOrderId FROM Abiturient INNER JOIN qAbitProtocols ON qAbitProtocols.Id = Abiturient.Id WHERE 1=1 " + GetFilter("Abiturient");
DataSet ds = _bdc.GetDataSet(query);
if (ds.Tables[0].Rows.Count == 0)
{
return;
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
string zc = dr["Code"].ToString().Replace(" ", "");
if (zc.Length > 10)
{
zc = zc.Substring(0, 10);
}
string pa = dr["PassportAuthor"].ToString();
if (pa.Length > 250)
{
pa = pa.Substring(0, 250);
}
string a = dr["Code"].ToString() + ", " + dr["City"].ToString() + ", " + dr["Street"].ToString() + ", д." + dr["House"].ToString() + (dr["Korpus"].ToString().Length == 0 ? "" : " к." + dr["Korpus"].ToString()) + " кв." + dr["Flat"].ToString();
if (a.Length > 250)
{
a = a.Substring(0, 250);
}
string la = dr["CodeReal"].ToString() + ", " + dr["CityReal"].ToString() + ", " + dr["StreetReal"].ToString() + ", д." + dr["HouseReal"].ToString() + (dr["KorpusReal"].ToString().Length == 0 ? "" : " к." + dr["KorpusReal"].ToString()) + " кв." + dr["FlatReal"].ToString();
if (dr["CodeReal"].ToString().Length == 0 && dr["CityReal"].ToString().Length == 0 && dr["StreetReal"].ToString().Length == 0 && dr["HouseReal"].ToString().Length == 0 && dr["KorpusReal"].ToString().Length == 0 && dr["FlatReal"].ToString().Length == 0)
{
la = "";
}
if (la.Length > 250)
{
la = la.Substring(0, 250);
}
string ph = dr["Phone"].ToString() + (dr["Mobiles"].ToString().Length == 0 ? "" : "; " + dr["Mobiles"].ToString());
if (ph.Length > 100)
{
ph = ph.Substring(0, 100);
}
string profId = slProf[dr["FacultyId"].ToString() + "_" + dr["LicenseProgramId"].ToString()];
string specId;
if (dr["ProfileId"] == null || dr["ProfileId"].ToString() == "0" || dr["ProfileId"].ToString() == "")
{
specId = "0";
}
else
{
specId = slSpec[dr["FacultyId"].ToString() + "_" + dr["LicenseProgramId"].ToString() + "_" + dr["ProfileId"].ToString()];
}
string educSeries = MainClass.studyLevelGroupId == 1 ? dr["AttestatSeries"].ToString() : dr["DiplomSeries"].ToString();
string educNum = MainClass.studyLevelGroupId == 1 ? dr["AttestatNum"].ToString() : dr["DiplomNum"].ToString();
string educYear = MainClass.studyLevelGroupId == 1 ? dr["SchoolExitYear"].ToString() : dr["HEExitYear"].ToString();
if (string.IsNullOrEmpty(educYear))
{
educYear = "0";
}
string s = string.Format(
"INSERT INTO Abiturient (" +
"[FileNum], [Name], [Patronymic], [Surname], " +
"[Privileges], [IsExcellent], [ListenerTypeId], [IsActualListener], " +
"[Hostel], [FacultyId], [ProfessionId], [SpecializationId], " +
"[StudyFormId], [SectionId], [CompetitionId], " +
"[DocDate], [CitizenId], [RegionId], [LanguageId], " +
"[AttestatSeries], [AttestatRegion], [AttestatNum], [AttestatCopy], " +
"[SchoolName], [SchoolCity], [SchoolNum], [SchoolTypeId], [ExitYear], " +
"[Phone], [ZipCode], [Adress], [LifeAddress], " +
"[BirthDate], [Sex], " +
"[PasswordTypeId], [PaswSeries], [PaswNumber], [PaswDate], [PaswAuthor], " +
"[StudyNumber], [EntryOrderId], [EduProgName], [EduProgKod], [StudyPlanNumber])" +
"VALUES (" +
"'{0}','{1}','{2}','{3}'," +
"'{4}','{5}','{6}','{7}', " +
"'{8}','{9}','{10}','{11}'," +
"'{12}','{13}','{14}'," +
"'{15}','{16}','{17}','{18}'," +
"'{19}','{20}','{21}','{22}'," +
"'{23}','{24}','{25}','{26}','{27}'," +
"'{28}','{29}','{30}','{31}'," +
"'{32}','{33}'," +
"'{34}','{35}','{36}','{37}','{38}'," +
"'{39}','{40}', '{41}','{42}', '{43}')",
dr["RegNum"].ToString(), dr["Name"].ToString(), dr["SecondName"].ToString(), dr["Surname"].ToString(),
dr["Privileges"].ToString(), QueryServ.QueryForBool(dr["IsExcellent"].ToString()), dr["ListenerTypeId"].ToString(), QueryServ.QueryForBool(dr["IsListener"].ToString()),
QueryServ.QueryForBool(dr["HostelEduc"].ToString()), dr["FacultyId"].ToString(), profId, specId,
dr["StudyBasisId"].ToString(), dr["StudyFormId"].ToString(), dr["CompetitionId"].ToString(),
dr["DocDate"].ToString(), _dRegion[dr["Nation"].ToString()], dr["RegionId"].ToString().Length == 0 ? "0" : dr["RegionId"].ToString(), dr["LanguageId"].ToString().Length == 0 ? "0" : dr["LanguageId"].ToString(),
educSeries, dr["AttestatRegion"].ToString(), educNum, QueryServ.QueryForBool(dr["CopyAtt"].ToString()),
dr["SchoolName"].ToString().Replace("'", ""), dr["SchoolCity"].ToString(), dr["SchoolNum"].ToString(), dr["SchoolTypeId"].ToString(), educYear,
ph, zc, a, la,
dr["BirthDate"].ToString(), QueryServ.QueryForBool(dr["Sex"].ToString()),
dr["PassportTypeId"].ToString(), dr["PassportSeries"].ToString(), dr["PassportNumber"].ToString(), dr["PassportDate"].ToString(), pa,
dr["StudyNumber"].ToString(), _slIds[dr["Id"].ToString()], dr["ObrazProgramName"].ToString(), dr["ObrazProgramCrypt"].ToString(), dr["StudyPlanNumber"].ToString());
_alQueries.Add(s);
_NewId++;
}
}
