private void btnUpdate_Click(object sender, EventArgs e)
{
bdcInet.OpenDatabase(MainClass.connStringOnline);
wc = new NewWatch(11);
wc.Show();
try
{
if (DateTime.Now < _One)
{
Fill_1();
}
else if (DateTime.Now.Date > _One && DateTime.Now < _Two)
{
Fill_2();
}
else if (DateTime.Now.Date > _Two && DateTime.Now < _Three)
{
Fill_3();
}
wc.Close();
}
catch { }
wc.Close();
wc = null;
bdcInet.CloseDataBase();
}
public FormA()
{
InitializeComponent();
this.MdiParent = MainClass.mainform;
bdcInet.OpenDatabase(MainClass.connStringOnline);
GetKC();
try
{
wc = new NewWatch(DateTime.Now.Date > _One ? (DateTime.Now.Date > _Two ? 33 : 22) : 11);
wc.Show();
Fill_1();
if (DateTime.Now.Date > _One)
{
Fill_2();
}
if (DateTime.Now.Date > _Two)
{
Fill_3();
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
wc.Close();
wc = null;
bdcInet.CloseDataBase();
}
private void tbFIO_TextChanged(object sender, EventArgs e)
{
//WinFormsServ.Search(dgv, "ФИО", tbFIO.Text);
NewWatch wc = new NewWatch(1);
wc.Show();
wc.SetText("Обработка, подождите...");
GridSearch();
wc.PerformStep();
wc.Close();
}
private void FillGrid()
{
string query = @"
SELECT DISTINCT
hlpStatMaxApprovedEgeMarks.EgeExamNameId, Region.Name AS RegionName,
COUNT(DISTINCT Person.Id) AS EgeCNT,
MIN(hlpStatMaxApprovedEgeMarks.Value) AS EgeMin,
AVG(convert(float, hlpStatMaxApprovedEgeMarks.Value)) AS EgeAvg,
MAX(hlpStatMaxApprovedEgeMarks.Value) AS EgeMax
FROM ed.hlpStatMaxApprovedEgeMarks
INNER JOIN ed.Person ON Person.Id = hlpStatMaxApprovedEgeMarks.PersonId
INNER JOIN ed.Region ON Region.Id = Person.RegionId
INNER JOIN ed.Abiturient ON Abiturient.PersonId = Person.Id
INNER JOIN ed.Entry ON Entry.Id = Abiturient.EntryId
INNER JOIN ed.StudyLevel ON StudyLevel.Id = Entry.StudyLevelId
LEFT JOIN ed.extEntryView ON extEntryView.AbiturientId = Abiturient.Id
WHERE StudyLevel.LevelGroupId = @LevelGroupId ";
SortedList <string, object> sl = new SortedList <string, object>();
sl.Add("@LevelGroupId", MainClass.studyLevelGroupId);
if (FacultyId.HasValue)
{
query += " AND Entry.FacultyId=@FacultyId ";
sl.Add("@FacultyId", FacultyId);
}
if (LicenseProgramId.HasValue)
{
query += " AND Entry.LicenseProgramId=@LicenseProgramId ";
sl.Add("@LicenseProgramId", LicenseProgramId);
}
if (ObrazProgramId.HasValue)
{
query += " AND Entry.ObrazProgramId=@ObrazProgramId ";
sl.Add("@ObrazProgramId", ObrazProgramId);
}
if (StudyFormId.HasValue)
{
query += " AND Entry.StudyFormId=@StudyFormId ";
sl.Add("@StudyFormId", StudyFormId);
}
if (StudyBasisId.HasValue)
{
query += " AND Entry.StudyBasisId=@StudyBasisId ";
sl.Add("@StudyBasisId", StudyBasisId);
}
if (chbEntered.Checked)
{
query += " AND extEntryView.AbiturientId IS NOT NULL ";
}
string groupby = "GROUP BY hlpStatMaxApprovedEgeMarks.EgeExamNameId, Region.Name";
NewWatch wc = new NewWatch(5);
wc.Show();
wc.SetText("Загрузка данных...");
DataTable tbl = MainClass.Bdc.GetDataSet(query + groupby, sl).Tables[0];
wc.PerformStep();
dgv.DataSource = null;
var data = from DataRow rw in tbl.Rows
select new
{
EgeExamNameId = rw.Field <int>("EgeExamNameId"),
RegionName = rw.Field <string>("RegionName"),
CNT = rw.Field <int?>("EgeCNT"),
EgeMin = rw.Field <int?>("EgeMin"),
EgeAvg = rw.Field <double?>("EgeAvg"),
EgeMax = rw.Field <int?>("EgeMax")
};
wc.PerformStep();
DataTable src_tbl = new DataTable();
src_tbl.Columns.Add("Регион", typeof(string));
src_tbl.Columns.Add("Кол-во абитуриентов", typeof(int));
src_tbl.Columns.Add("Мин.балл", typeof(int));
src_tbl.Columns.Add("Сред.балл", typeof(double));
src_tbl.Columns.Add("Макс.балл", typeof(int));
wc.SetText("Построение списка...");
foreach (var region in data.Where(x => x.EgeExamNameId == EgeExamNameId).Select(x => x.RegionName).Distinct())
{
DataRow row = src_tbl.NewRow();
row["Регион"] = region;
var balls = data.Where(x => x.EgeExamNameId == EgeExamNameId && x.RegionName == region);
row["Кол-во абитуриентов"] = balls.Select(x => x.CNT).DefaultIfEmpty(0).First();
row["Мин.балл"] = balls.Select(x => x.EgeMin).DefaultIfEmpty(0).First();
row["Сред.балл"] = Math.Round(balls.Select(x => x.EgeAvg).DefaultIfEmpty(0d).First().Value, 2);
row["Макс.балл"] = balls.Select(x => x.EgeMax).DefaultIfEmpty(0).First();
src_tbl.Rows.Add(row);
}
wc.PerformStep();
dgv.DataSource = src_tbl;
wc.Close();
}
private void FillGrid()
{
string query = @"
SELECT DISTINCT
EgeExamName.Name AS 'Экзамен', SP_Faculty.Name AS 'Факультет',
/*COUNT(DISTINCT Person.Id) AS EgeCNT, */
MIN(hlpStatMaxApprovedEgeMarks.Value) AS 'Мин ЕГЭ',
AVG(convert(float, hlpStatMaxApprovedEgeMarks.Value)) AS 'Сред ЕГЭ',
MAX(hlpStatMaxApprovedEgeMarks.Value) AS 'Макс ЕГЭ'
FROM ed.hlpStatMaxApprovedEgeMarks
INNER JOIN ed.Person ON Person.Id = hlpStatMaxApprovedEgeMarks.PersonId
INNER JOIN ed.EgeExamName ON EgeExamName.Id = hlpStatMaxApprovedEgeMarks.EgeExamNameId
INNER JOIN ed.Abiturient ON Abiturient.PersonId = Person.Id
INNER JOIN ed.Entry ON Entry.Id = Abiturient.EntryId
LEFT JOIN ed.ExamInEntry ON ExamInEntry.EntryId = Abiturient.EntryId
LEFT JOIN ed.EgeToExam ON EgeToExam.ExamId = ExamInEntry.ExamId
INNER JOIN ed.SP_Faculty ON SP_Faculty.Id = Entry.FacultyId
INNER JOIN ed.StudyLevel ON StudyLevel.Id = Entry.StudyLevelId
LEFT JOIN ed.extEntryView ON extEntryView.AbiturientId = Abiturient.Id
WHERE StudyLevel.LevelGroupId=@LevelGroupId AND Person.RegionId=@RegionId ";
SortedList <string, object> sl = new SortedList <string, object>();
sl.Add("@LevelGroupId", MainClass.studyLevelGroupId);
sl.Add("@RegionId", RegionId);
if (FacultyId.HasValue)
{
query += " AND Entry.FacultyId=@FacultyId ";
sl.Add("@FacultyId", FacultyId.Value);
}
if (LicenseProgramId.HasValue)
{
query += " AND Entry.LicenseProgramId=@LicenseProgramId ";
sl.Add("@LicenseProgramId", LicenseProgramId.Value);
}
if (ObrazProgramId.HasValue)
{
query += " AND Entry.ObrazProgramId=@ObrazProgramId ";
sl.Add("@ObrazProgramId", ObrazProgramId.Value);
}
if (StudyFormId.HasValue)
{
query += " AND Entry.StudyFormId=@StudyFormId ";
sl.Add("@StudyFormId", StudyFormId.Value);
}
if (StudyBasisId.HasValue)
{
query += " AND Entry.StudyBasisId=@StudyBasisId ";
sl.Add("@StudyBasisId", StudyBasisId.Value);
}
if (EgeExamNameId.HasValue)
{
query += " AND hlpStatMaxApprovedEgeMarks.EgeExamNameId=@EgeExamNameId ";
sl.Add("@EgeExamNameId", EgeExamNameId.Value);
}
if (chbEntered.Checked)
{
query += " AND extEntryView.AbiturientId IS NOT NULL ";
}
if (chbEgeToExamOnly.Checked)
{
query += " AND EgeToExam.EgeExamNameId IS NOT NULL AND EgeToExam.EgeExamNameId=hlpStatMaxApprovedEgeMarks.EgeExamNameId ";
}
string groupby = "GROUP BY EgeExamName.Name, SP_Faculty.Name ORDER BY 2, 1";
NewWatch wc = new NewWatch(5);
wc.Show();
wc.SetText("Загрузка данных...");
DataTable tbl = MainClass.Bdc.GetDataSet(query + groupby, sl).Tables[0];
wc.PerformStep();
var col = dgv.SortedColumn != null ? dgv.SortedColumn.Name : null;
var order = dgv.SortOrder;
dgv.DataSource = null;
foreach (DataRow rw in tbl.Rows)
{
rw.SetField <double>("Сред ЕГЭ", Math.Round(rw.Field <double>("Сред ЕГЭ"), 2));
}
wc.PerformStep();
//dgv.DataSource = src_tbl;
dgv.DataSource = tbl;
if (col != null)
{
dgv.Sort(dgv.Columns[col], order == SortOrder.Descending ? ListSortDirection.Descending : ListSortDirection.Ascending);
}
wc.Close();
}
private void btnOk_Click(object sender, EventArgs e)
{
if (!MainClass.IsPasha())
{
return;
}
using (PriemEntities context = new PriemEntities())
{
if (MessageBox.Show(string.Format("Создать бэкапы для таблиц и удалить рейтинг из текущих таблиц для \n {0}, {1}?", cbStudyLevelGroup.Text, cbStudyBasis.Text), "Внимание", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
NewWatch wc;
using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.RequiresNew, TimeSpan.FromHours(1)))
{
if (StudyBasisId == 2)
{
List <Guid> lstEntry = (from ev in context.extEntryView
select ev.AbiturientId).ToList <Guid>();
//Fixieren
IEnumerable <Fixieren> fixs = from fx in context.Fixieren
join ab in context.extAbit
on fx.AbiturientId equals ab.Id
where ab.StudyLevelGroupId == 1 && ab.StudyBasisId == 2 &&
(lstEntry.Contains(ab.Id) || ab.BackDoc)
select fx;
wc = new NewWatch();
wc.Show();
wc.SetText("Идет процесс. BackUp Fixieren. Общее число - " + fixs.Count());
wc.SetMax(fixs.Count());
foreach (Fixieren fix in fixs)
{
context.ExecuteStoreCommand("INSERT INTO ed.FixierenBackUp2(Number, AbiturientId, FixierenViewId) VALUES({0}, {1}, {2})", fix.Number, fix.AbiturientId, fix.FixierenViewId);
context.Fixieren_DELETE(fix.AbiturientId);
wc.PerformStep();
}
//_FirstWave
IEnumerable <C_FirstWave> firstW = from fx in context.C_FirstWave
join ab in context.extAbit
on fx.AbiturientId equals ab.Id
where ab.StudyLevelGroupId == 1 && ab.StudyBasisId == 2 &&
(lstEntry.Contains(ab.Id) || ab.BackDoc)
select fx;
wc.SetText("Идет процесс. BackUp C_FirstWave. Общее число - " + firstW.Count());
wc.SetMax(firstW.Count());
foreach (C_FirstWave fix in firstW)
{
context.ExecuteStoreCommand("INSERT INTO ed._FirstWaveBackUp2(AbiturientId, SortNum) VALUES({0}, {1})", fix.AbiturientId, fix.SortNum);
context.FirstWave_DeleteByAbId(fix.AbiturientId);
wc.PerformStep();
}
}
else
{
//Fixieren
IEnumerable <Fixieren> fixs = from fx in context.Fixieren
join ab in context.extAbit
on fx.AbiturientId equals ab.Id
where ab.StudyLevelGroupId == StudyLevelGroupId && ab.StudyBasisId == StudyBasisId
select fx;
wc = new NewWatch();
wc.Show();
wc.SetText("Идет процесс. BackUp Fixieren. Общее число - " + fixs.Count());
wc.SetMax(fixs.Count());
foreach (Fixieren fix in fixs)
{
context.ExecuteStoreCommand("INSERT INTO ed.FixierenBackUp2(Number, AbiturientId, FixierenViewId) VALUES({0}, {1}, {2})", fix.Number, fix.AbiturientId, fix.FixierenViewId);
context.Fixieren_DELETE(fix.AbiturientId);
wc.PerformStep();
}
//FixierenView
IEnumerable <FixierenView> fixViews = from fx in context.FixierenView
where fx.StudyLevelGroupId == StudyLevelGroupId && fx.StudyBasisId == StudyBasisId
select fx;
wc.SetText("Идет процесс. BackUp FixierenView. Общее число - " + fixViews.Count());
wc.SetMax(fixViews.Count());
foreach (FixierenView fix in fixViews)
{
context.FixierenViewBackup2_Insert(fix.Id, fix.StudyLevelGroupId, fix.FacultyId, fix.LicenseProgramId, fix.ObrazProgramId,
fix.ProfileId, fix.StudyBasisId, fix.StudyFormId, fix.IsSecond, fix.IsReduced, fix.IsParallel,
fix.IsCel, fix.DocNum, fix.Locked);
context.FixierenView_Delete(fix.Id);
wc.PerformStep();
}
//_FirstWave
IEnumerable <C_FirstWave> firstW = from fx in context.C_FirstWave
join ab in context.extAbit
on fx.AbiturientId equals ab.Id
where ab.StudyLevelGroupId == StudyLevelGroupId && ab.StudyBasisId == StudyBasisId
select fx;
wc.SetText("Идет процесс. BackUp C_FirstWave. Общее число - " + firstW.Count());
wc.SetMax(firstW.Count());
foreach (C_FirstWave fix in firstW)
{
context.ExecuteStoreCommand("INSERT INTO ed._FirstWaveBackUp2(AbiturientId, SortNum) VALUES({0}, {1})", fix.AbiturientId, fix.SortNum);
context.FirstWave_DeleteByAbId(fix.AbiturientId);
wc.PerformStep();
}
//_FirstWaveGreen
IEnumerable <C_FirstWaveGreen> firstWGr = from fx in context.C_FirstWaveGreen
join ab in context.extAbit
on fx.AbiturientId equals ab.Id
where ab.StudyLevelGroupId == StudyLevelGroupId && ab.StudyBasisId == StudyBasisId
select fx;
wc.SetText("Идет процесс. BackUp C_FirstWaveGreen. Общее число - " + firstWGr.Count());
wc.SetMax(firstWGr.Count());
foreach (C_FirstWaveGreen fix in firstWGr)
{
context.ExecuteStoreCommand("INSERT INTO ed._FirstWaveGreenBackUp2(AbiturientId, IsNew) VALUES({0}, {1})", fix.AbiturientId, fix.IsNew);
context.FirstWaveGreen_DeleteByAbId(fix.AbiturientId);
wc.PerformStep();
}
}
transaction.Complete();
wc.Close();
}
}
}
}
private void FillGridMarks(string abitFilters, string examFilters)
{
DataTable examTable = new DataTable();
DataSet ds;
string sQueryAbit;
string sQuery;
List <ListItem> egeList = new List <ListItem>();
List <ListItem> olympList = new List <ListItem>();
DataColumn clm;
clm = new DataColumn();
clm.ColumnName = "Ид_номер";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = "ФИО";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = "Рег_номер";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = "Направление";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = "Основа обучения";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = "Форма обучения";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = "Id";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = "Конкурс";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = "Сумма баллов";
clm.DataType = typeof(int);
examTable.Columns.Add(clm);
IList lst = examsId.GetKeyList();
string examsFields = string.Empty;
if (examsId.Count > 50)
{
if (!muchExams)
{
WinFormsServ.Error("Слишком много экзаменов, воспользуйтесь фильтрами!");
}
muchExams = true;
dgvMarks.DataSource = null;
return;
}
muchExams = false;
foreach (DictionaryEntry de in examsId)
{
clm = new DataColumn();
clm.ColumnName = de.Value.ToString();
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = de.Value.ToString() + "IsEge";
examTable.Columns.Add(clm);
clm = new DataColumn();
clm.ColumnName = de.Value.ToString() + "IsOlymp";
examTable.Columns.Add(clm);
}
NewWatch wc = new NewWatch();
wc.Show();
wc.SetText("Получение данных по абитуриентам...");
sQueryAbit = string.Format(@"SELECT DISTINCT
ed.extAbit.Id as Id,
extPerson.PersonNum as Ид_номер,
ed.extAbit.RegNum as Рег_номер,
ed.extAbitMarksSum.TotalSum AS Sum,
ed.extPerson.FIO as ФИО,
ed.extAbit.ObrazProgramCrypt + ' ' +(Case when NOT ed.extAbit.ProfileId IS NULL then ed.extAbit.ProfileName else ed.extAbit.ObrazProgramName end) as Spec,
ed.extAbit.StudyFormName AS StudyForm,
ed.extAbit.StudyBasisName AS StudyBasis,
Competition.Name AS CompName
FROM ed.extAbit
LEFT JOIN ed.extPerson ON ed.extAbit.PersonId = ed.extPerson.Id
LEFT JOIN ed.Competition ON ed.extAbit.CompetitionId = ed.Competition.Id
LEFT JOIN ed.extAbitMarksSum ON ed.extAbitMarksSum.Id = ed.extAbit.Id
--LEFT JOIN ed.qMark ON qMark.AbiturientId = extAbit.Id
{0}
ORDER BY ФИО", abitFilters);
ds = _bdc.GetDataSet(sQueryAbit);
var abit_data = from DataRow rw in ds.Tables[0].Rows
select new
{
Id = rw.Field <Guid>("Id"),
PersonNum = rw["Ид_номер"].ToString(),
RegNum = rw["Рег_номер"].ToString(),
Sum = rw.Field <int?>("Sum"),
FIO = rw.Field <string>("ФИО"),
StudyForm = rw.Field <string>("StudyForm"),
StudyBasis = rw.Field <string>("StudyBasis"),
Spec = rw.Field <string>("Spec"),
Competition = rw.Field <string>("CompName"),
};
List <Guid> ids = abit_data.Select(x => x.Id).Distinct().ToList();
//NewWatch wc = new NewWatch(ids.Count);
wc.SetText("Получение баллов из базы...");
string query = string.Format(@"SELECT AbiturientId,
qMark.ExamId,
qMark.Value,
case when qMark.IsFromEge IS NULL OR qMark.IsFromEge = 'False' then 0 else 1 end AS IsFromEge,
case when qMark.IsFromOlymp IS NULL OR qMark.IsFromOlymp = 'False' then 0 else 1 end AS IsFromOlymp
FROM ed.qMark
INNER JOIN ed.extAbit ON extAbit.Id = qMark.AbiturientId
{0}", abitFilters);
ds = _bdc.GetDataSet(query);
var marks = from DataRow rw in ds.Tables[0].Rows
select new
{
AbiturientId = rw.Field <Guid>("AbiturientId"),
ExamId = rw.Field <int?>("ExamId"),
Value = rw.Field <byte?>("Value"),
IsFromOlymp = rw.Field <int>("IsFromOlymp") == 1 ? true : false,
IsFromEge = rw.Field <int>("IsFromEge") == 1 ? true : false
};
marks.GetEnumerator();
wc.SetText("Построение списка...");
List <string> lstIds = new List <string>();
int iCntAbits = ids.Count();
wc.SetMax(iCntAbits);
//foreach (DataRow dsRow in ds.Tables[0].Rows)
foreach (Guid abitId in ids)
{
DataRow newRow;
newRow = examTable.NewRow();
var abit = abit_data.Where(x => x.Id == abitId).First();
newRow["Ид_номер"] = abit.PersonNum.ToString(); // dsRow["Ид_номер"].ToString();
newRow["ФИО"] = abit.FIO; //dsRow["ФИО"].ToString();
newRow["Рег_номер"] = abit.RegNum.ToString(); //dsRow["Рег_номер"].ToString();
newRow["Направление"] = abit.Spec; //dsRow["Spec"].ToString();
newRow["Основа обучения"] = abit.StudyBasis; //dsRow["StudyBasis"].ToString();
newRow["Форма обучения"] = abit.StudyForm; //dsRow["StudyForm"].ToString();
newRow["Id"] = abit.Id.ToString(); //dsRow["Id"].ToString();
newRow["Конкурс"] = abit.Competition; //dsRow["CompName"].ToString();
newRow["Сумма баллов"] = abit.Sum.HasValue ? abit.Sum.Value : 0; //dsRow["CompName"].ToString();
foreach (DictionaryEntry de in examsId)
{
int iExamId = 0;
if (!int.TryParse(de.Key.ToString(), out iExamId))
{
continue;
}
var mark_data = marks.Where(x => x.AbiturientId == abitId && x.ExamId == iExamId);
int markSum = mark_data.Select(x => x.Value).DefaultIfEmpty((byte?)0).Sum(x => x.Value);
bool isFromEge = mark_data.Select(x => x.IsFromEge).DefaultIfEmpty(false).First();
bool isFromOlymp = mark_data.Select(x => x.IsFromOlymp).DefaultIfEmpty(false).First();
newRow[de.Value.ToString()] = markSum == 0 ? "" : markSum.ToString(); //dsRow[de.Key.ToString()].ToString();
newRow[de.Value.ToString() + "IsEge"] = isFromEge.ToString(); //dsRow[de.Key.ToString() + "IsEge"].ToString();
newRow[de.Value.ToString() + "IsOlymp"] = isFromOlymp.ToString(); //dsRow[de.Key.ToString() + "IsOlymp"].ToString();
}
examTable.Rows.Add(newRow);
wc.PerformStep();
lstIds.Add(string.Format("'{0}'", abitId.ToString()));
//lstIds.Add(string.Format("'{0}'", dsRow["Id"].ToString()));
}
wc.Close();
DataView dv = new DataView(examTable);
dv.AllowNew = false;
dgvMarks.DataSource = dv;
dgvMarks.ReadOnly = true;
dgvMarks.Columns["Id"].Visible = false;
foreach (DictionaryEntry de in examsId)
{
dgvMarks.Columns[de.Value.ToString() + "IsEge"].Visible = false;
dgvMarks.Columns[de.Value.ToString() + "IsOlymp"].Visible = false;
}
dgvMarks.Columns["Ид_номер"].Width = 65;
dgvMarks.Columns["Рег_номер"].Width = 66;
dgvMarks.Columns["ФИО"].Width = 203;
dgvMarks.Columns["Форма обучения"].Width = 56;
dgvMarks.Columns["Основа обучения"].Width = 56;
dgvMarks.Columns["Направление"].Width = 100;
dgvMarks.Columns["Конкурс"].Width = 54;
dgvMarks.Columns["Сумма баллов"].Width = 49;
for (int i = 9; i < dgvMarks.Columns.Count; i = i + 1)
{
dgvMarks.Columns[i].Width = 43;
}
dgvMarks.Update();
}
private void FillGrid()
{
string query = @"
SELECT DISTINCT extPerson.Id, extPerson.FIO, hlpStatRatingList.SUM, hlpStatRatingList.Rank, Entry.KCP,
(case when q.CompetitionId IN (1,2,5,7,8) then 1 else 0 end) AS VKs,
(CASE when Rank <= Entry.KCP then 1 else 0 end) AS GREEN, (case when q.HasOriginals='True' then 1 else 0 end) AS Orig
FROM ed.qAbitAll as q
INNER JOIN ed.extPerson ON extPerson.Id = q.PersonId
INNER JOIN ed.Entry ON Entry.Id = q.EntryId
LEFT JOIN ed.hlpStatRatingList ON hlpStatRatingList.AbiturientId=q.Id
WHERE q.PersonId IN
(
SELECT PersonId
FROM ed.qAbitAll
WHERE qAbitAll.FacultyId <> q.FacultyId
AND qAbitAll.FacultyId = @OtherFacultyId
AND StudyLevelGroupId = @StudyLevelGroupId
AND qAbitAll.BackDoc = 0
)
AND StudyLevelGroupId = @StudyLevelGroupId
AND q.FacultyId=@FacultyId AND q.BackDoc = 0 AND q.StudyFormId=@StudyFormId AND q.StudyBasisId=@StudyBasisId AND q.CompetitionId<>6 ";
SortedList <string, object> sl = new SortedList <string, object>();
sl.Add("@StudyLevelGroupId", MainClass.studyLevelGroupId);
sl.Add("@FacultyId", FacultyId);
sl.Add("@OtherFacultyId", OtherFacultyId);
sl.Add("@StudyFormId", StudyFormId);
sl.Add("@StudyBasisId", StudyBasisId);
if (LicenseProgramId.HasValue)
{
query += " AND q.LicenseProgramId=@LicenseProgramId ";
sl.Add("@LicenseProgramId", LicenseProgramId);
}
if (ObrazProgramId.HasValue)
{
query += " AND q.ObrazProgramId=@ObrazProgramId ";
sl.Add("@ObrazProgramId", ObrazProgramId);
}
NewWatch wc = new NewWatch(3);
wc.Show();
wc.SetText("Данные вашего факультета...");
DataTable tbl = MainClass.Bdc.GetDataSet(query + " ORDER BY FIO ", sl).Tables[0];
var Persons = from DataRow rw in tbl.Rows
select new
{
PersonId = rw.Field <Guid>("Id"),
FIO = rw.Field <string>("FIO"),
SUM = rw.Field <int?>("SUM"),
Rank = rw.Field <long>("Rank"),
KCP = rw.Field <int?>("KCP") ?? 0,
Green = rw.Field <int>("GREEN") == 1 ? true : false,
Originals = rw.Field <int>("Orig") == 1 ? true : false,
VK = rw.Field <int>("VKs") == 1 ? true : false
};
query = @"
SELECT DISTINCT extPerson.Id AS PersonId,
(case when qAbitAll.CompetitionId IN (1,2,5,7,8) then 1 else 0 end) AS VKs,
(case when qAbitAll.HasOriginals='True' then 1 else 0 end) AS Orig,
qAbitAll.LicenseProgramCode + ' ' + qAbitAll.LicenseProgramName AS LP,
qAbitAll.ObrazProgramCrypt + ' ' + qAbitAll.ObrazProgramName AS OP,
qAbitAll.ProfileName,
hlpStatRatingList.SUM, hlpStatRatingList.Rank, Entry.KCP, (CASE when Rank <= Entry.KCP then 1 else 0 end) AS GREEN
FROM ed.qAbitAll
INNER JOIN ed.Entry ON Entry.Id = qAbitAll.EntryId
INNER JOIN ed.extPerson ON extPerson.Id=qAbitAll.PersonId
LEFT JOIN ed.hlpStatRatingList ON hlpStatRatingList.AbiturientId=qAbitAll.Id
WHERE qAbitAll.FacultyId=@OtherFacultyId
AND StudyLevelGroupId=@StudyLevelGroupId AND qAbitAll.CompetitionId<>6 AND qAbitAll.NotEnabled<>'True'
AND qAbitAll.BackDoc=0 AND qAbitAll.StudyBasisId=@OtherStudyBasisId AND qAbitAll.StudyFormId=@OtherStudyFormId ";
sl.Add("@OtherStudyFormId", OtherStudyFormId);
sl.Add("@OtherStudyBasisId", OtherStudyBasisId);
if (OtherLicenseProgramId.HasValue)
{
query += " AND qAbitAll.LicenseProgramId=@OtherLicenseProgramId ";
sl.Add("@OtherLicenseProgramId", OtherLicenseProgramId);
}
if (OtherObrazProgramId.HasValue)
{
query += " AND qAbitAll.ObrazProgramId=@OtherObrazProgramId ";
sl.Add("@OtherObrazProgramId", OtherObrazProgramId);
}
wc.SetText("Данные сравниваемого факультета...");
tbl = MainClass.Bdc.GetDataSet(query, sl).Tables[0];
var OtherMarks = from DataRow rw in tbl.Rows
select new
{
PersonId = rw.Field <Guid>("PersonId"),
LicenseProgram = rw.Field <string>("LP"),
ObrazProgram = rw.Field <string>("OP"),
Profile = rw.Field <string>("ProfileName"),
SUM = rw.Field <int?>("SUM"),
Rank = rw.Field <long?>("Rank"),
KCP = rw.Field <int?>("KCP"),
Green = rw.Field <int>("GREEN") == 1 ? true : false,
Originals = rw.Field <int>("Orig") == 1 ? true : false,
VK = rw.Field <int>("VKs") == 1 ? true : false
};
DataTable tblSource = new DataTable();
tblSource.Columns.Add("Id");
tblSource.Columns.Add("ФИО");
tblSource.Columns.Add("Сумма баллов у нас");
tblSource.Columns.Add("Рейтинг у нас", typeof(int));
tblSource.Columns.Add("Наш проходной (КЦ)", typeof(int));
tblSource.Columns.Add("Оригинал у нас");
tblSource.Columns.Add("Направление");
tblSource.Columns.Add("Образовательная программа");
tblSource.Columns.Add("Профиль");
tblSource.Columns.Add("Сумма баллов у них");
tblSource.Columns.Add("Рейтинг у них", typeof(int));
tblSource.Columns.Add("Их проходной (КЦ)", typeof(int));
tblSource.Columns.Add("Оригинал у них");
tblSource.Columns.Add("OurGREEN", typeof(bool));
tblSource.Columns.Add("TheirGREEN", typeof(bool));
tblSource.Columns.Add("OurVK", typeof(bool));
tblSource.Columns.Add("TheirVK", typeof(bool));
wc.SetText("Построение списка...");
wc.SetMax(Persons.Count());
foreach (var p in Persons)
{
var om = OtherMarks.Where(x => x.PersonId == p.PersonId);
foreach (var mrk in om)
{
DataRow row = tblSource.NewRow();
row["Id"] = p.PersonId;
row["ФИО"] = p.FIO;
row["Сумма баллов у нас"] = p.SUM;
row["Рейтинг у нас"] = p.Rank;
row["OurGREEN"] = p.Green;
row["OurVK"] = p.VK;
row["Наш проходной (КЦ)"] = p.KCP;
row["Оригинал у нас"] = p.Originals ? "Да" : "Нет";
row["Направление"] = mrk.LicenseProgram;
row["Образовательная программа"] = mrk.ObrazProgram;
row["Профиль"] = mrk.Profile;
row["Сумма баллов у них"] = mrk.SUM;
row["Рейтинг у них"] = mrk.Rank;
row["Их проходной (КЦ)"] = mrk.KCP;
row["Оригинал у них"] = mrk.Originals ? "Да" : "Нет";
row["TheirGREEN"] = mrk.Green;
row["TheirVK"] = mrk.VK;
tblSource.Rows.Add(row);
}
wc.PerformStep();
}
tblBaseSource = tblSource;
wc.Close();
int iMaxYellow = Persons.Select(x => x.KCP).DefaultIfEmpty(0).Max();
int iMaxYellowOther = OtherMarks.Select(x => x.KCP ?? 0).DefaultIfEmpty(0).Max();
//int tbYell = 0;
//int.TryParse(tbYellow.Text, out tbYell);
//int tbYellOther = 0;
//int.TryParse(tbYellowOther.Text, out tbYellOther);
if (string.IsNullOrEmpty(tbYellow.Text))
{
tbYellow.Text = iMaxYellow.ToString();
}
if (string.IsNullOrEmpty(tbYellowOther.Text))
{
tbYellowOther.Text = iMaxYellowOther.ToString();
}
if (tbFIO.Text.Length > 0)
{
GridSearch();
return;
}
dgv.DataSource = tblSource;
GridColumnsSizeAndVisible();
lblCount.Text = tblSource.Rows.Count.ToString();
}