private void LoadMarks()
{
if (!MainClass.IsPasha())
{
return;
}
try
{
wtc = new Watch(2);
wtc.Show();
marksCount = 0;
if (ExamId == null)
{
foreach (KeyValuePair <string, string> ex in cbExam.Items)
{
int exId;
if (int.TryParse(ex.Key, out exId))
{
SetMarksForExam(exId);
}
}
}
else
{
SetMarksForExam(ExamId);
}
}
catch (Exception ex)
{
WinFormsServ.Error("Ошибка загрузки оценок " + ex.Message);
}
finally
{
wtc.Close();
MessageBox.Show(string.Format("Зачтено {0} оценок", marksCount));
}
}
private void FillGrid()
{
int num_pp = 0;
int?iFacultyId = GetFacultyId();
int?iStudyFormId = GetStudyFormId();
int?iStudyBasisId = GetStudyBasisId();
dgvData.Rows.Clear();
int watch_count = GetWatchCount();
Watch wc = new Watch(watch_count + watch_count / 10); //+10%
wc.Show();
int iTotalPlanBudzh = 0;
int iTotalPlanPlatn = 0;
int iTotalBudzh = 0;
int iTotalPlatn = 0;
using (PriemEntities context = new PriemEntities())
{
var q_lp = from ent in context.qEntry
where ent.StudyLevelGroupId == iStudyLevelGroupId
select new
{
ent.FacultyId,
ent.StudyFormId,
ent.StudyBasisId,
ent.LicenseProgramId,
ent.LicenseProgramCode,
ent.LicenseProgramName
};
if (iFacultyId != null)
{
q_lp = q_lp.Where(x => x.FacultyId == iFacultyId);
}
if (iStudyFormId != null)
{
q_lp = q_lp.Where(x => x.StudyFormId == iStudyFormId);
}
//if (iStudyBasisId != null)
// q_lp = q_lp.Where(x => x.StudyBasisId == iStudyBasisId);
var LicensePrograms = q_lp.Select(x => new { x.LicenseProgramId, x.LicenseProgramCode, x.LicenseProgramName }).Distinct().OrderBy(x => x.LicenseProgramCode);
foreach (var lProgram in LicensePrograms)
{
string query = string.Format(@"
SELECT StudyBasisId, COUNT(Id) AS CNT FROM ed.extAbit WHERE LicenseProgramId=@LicenseProgramId AND (convert(date, DocInsertDate)<=@Date)
AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId
GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : "");
DataTable tbl = MainClass.Bdc.GetDataSet(query,
new SortedList <string, object>()
{
{ "@LicenseProgramId", lProgram.LicenseProgramId },
{ "@Date", dtpDate.Value.Date },
{ "@SLGId", iStudyLevelGroupId },
{ "@FacultyId", iFacultyId ?? 0 },
{ "@StudyFormId", iStudyFormId }
}).Tables[0];
int sum_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int>("CNT")).DefaultIfEmpty(0).First();
int sum_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int>("CNT")).DefaultIfEmpty(0).First();
int?kcp_b = 0; //(from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 1 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First();
int?kcp_p = 0; //(from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 2 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First();
query = string.Format(@"SELECT StudyBasisId, SUM(KCP) AS KCP FROM ed.qEntry WHERE LicenseProgramId=@LicenseProgramId
AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : "");
tbl = MainClass.Bdc.GetDataSet(query,
new SortedList <string, object>()
{
{ "@LicenseProgramId", lProgram.LicenseProgramId },
{ "@Date", dtpDate.Value.Date },
{ "@SLGId", iStudyLevelGroupId },
{ "@FacultyId", iFacultyId ?? 0 },
{ "@StudyFormId", iStudyFormId }
}).Tables[0];
kcp_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First();
kcp_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First();
//if (kcp_b == 0 || kcp_p == 0)
//{
//}
float conc_b = kcp_b == null ? 0f : (sum_b == 0 ? 0f : (float)sum_b / (float)kcp_b.Value);
float conc_p = kcp_p == null ? 0f : (sum_p == 0 ? 0f : (float)sum_p / (float)kcp_p.Value);
AddRow(new StatRow
{
Num = (++num_pp).ToString(),
Name = lProgram.LicenseProgramName,
Code = lProgram.LicenseProgramCode,
PlanB = kcp_b.HasValue ? kcp_b.ToString() : "0",
PlanP = kcp_p.HasValue ? kcp_p.ToString() : "0",
SumB = sum_b.ToString(),
SumP = sum_p.ToString(),
ConcB = kcp_b == 0 ? "!КЦ" : Math.Round(conc_b, 2).ToString(),
ConcP = kcp_p == 0 ? "!КЦ" : Math.Round(conc_p, 2).ToString()
});
iTotalBudzh += sum_b;
iTotalPlatn += sum_p;
iTotalPlanBudzh += kcp_b ?? 0;
iTotalPlanPlatn += kcp_p ?? 0;
var q_op = (from ent in context.qEntry
where ent.LicenseProgramId == lProgram.LicenseProgramId &&
ent.StudyLevelGroupId == iStudyLevelGroupId
select new
{
ent.FacultyId,
ent.StudyFormId,
ent.StudyBasisId,
ent.ObrazProgramId,
ent.ObrazProgramCrypt,
ent.ObrazProgramName
});
if (iFacultyId != null)
{
q_op = q_op.Where(x => x.FacultyId == iFacultyId);
}
if (iStudyFormId != null)
{
q_op = q_op.Where(x => x.StudyFormId == iStudyFormId);
}
//if (iStudyBasisId != null)
// q_op = q_op.Where(x => x.StudyBasisId == iStudyBasisId);
var ObrazPrograms = q_op.Select(x => new { x.ObrazProgramId, x.ObrazProgramName, x.ObrazProgramCrypt }).Distinct().OrderBy(x => x.ObrazProgramCrypt);
foreach (var oProgram in ObrazPrograms)
{
query = string.Format(@"SELECT StudyBasisId, COUNT(Id) AS CNT FROM ed.extAbit
WHERE LicenseProgramId=@LicenseProgramId AND ObrazProgramId=@ObrazProgramId AND (convert(date, DocInsertDate)<=@Date)
AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId
GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : "");
tbl = MainClass.Bdc.GetDataSet(query,
new SortedList <string, object>()
{
{ "@LicenseProgramId", lProgram.LicenseProgramId },
{ "@ObrazProgramId", oProgram.ObrazProgramId },
{ "@Date", dtpDate.Value.Date },
{ "@SLGId", iStudyLevelGroupId },
{ "@FacultyId", iFacultyId ?? 0 },
{ "@StudyFormId", iStudyFormId }
}).Tables[0];
sum_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int>("CNT")).DefaultIfEmpty(0).First();
sum_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int>("CNT")).DefaultIfEmpty(0).First();
//kcp_b = (from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 1 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First();
//kcp_p = (from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 2 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First();
query = string.Format(@"SELECT StudyBasisId, SUM(KCP) AS KCP FROM ed.qEntry WHERE LicenseProgramId=@LicenseProgramId
AND ObrazProgramId=@ObrazProgramId AND StudyLevelGroupId=@SLGId {0}
AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : "");
tbl = MainClass.Bdc.GetDataSet(query,
new SortedList <string, object>()
{
{ "@LicenseProgramId", lProgram.LicenseProgramId },
{ "@ObrazProgramId", oProgram.ObrazProgramId },
{ "@Date", dtpDate.Value.Date },
{ "@SLGId", iStudyLevelGroupId },
{ "@FacultyId", iFacultyId ?? 0 },
{ "@StudyFormId", iStudyFormId }
}).Tables[0];
kcp_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First();
kcp_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First();
//if (kcp_b == 0 || kcp_p == 0)
//{
//}
conc_b = kcp_b == null ? 0f : (sum_b == 0 ? 0f : (float)sum_b / (float)kcp_b.Value);
conc_p = kcp_p == null ? 0f : (sum_p == 0 ? 0f : (float)sum_p / (float)kcp_p.Value);
AddRow(new StatRow
{
Num = (++num_pp).ToString(),
Name = " " + oProgram.ObrazProgramName,
Code = "",//oProgram.ObrazProgramCrypt,
PlanB = kcp_b.HasValue ? kcp_b.ToString() : "0",
PlanP = kcp_p.HasValue ? kcp_p.ToString() : "0",
SumB = sum_b.ToString(),
SumP = sum_p.ToString(),
ConcB = kcp_b == 0 ? "!КЦ" : Math.Round(conc_b, 2).ToString(),
ConcP = kcp_p == 0 ? "!КЦ" : Math.Round(conc_p, 2).ToString()
});
var q_prof = (from ent in context.qEntry
where ent.LicenseProgramId == lProgram.LicenseProgramId &&
ent.ObrazProgramId == oProgram.ObrazProgramId &&
ent.StudyLevelGroupId == iStudyLevelGroupId &&
ent.ProfileId != null
select new
{
ent.FacultyId,
ent.StudyFormId,
ent.StudyBasisId,
ent.ProfileId,
ent.ProfileName,
});
if (iFacultyId != null)
{
q_prof = q_prof.Where(x => x.FacultyId == iFacultyId);
}
if (iStudyFormId != null)
{
q_prof = q_prof.Where(x => x.StudyFormId == iStudyFormId);
}
//if (iStudyBasisId != null)
// q_prof = q_prof.Where(x => x.StudyBasisId == iStudyBasisId);
var Profiles = q_prof.Select(x => new { x.ProfileId, x.ProfileName }).Distinct().OrderBy(x => x.ProfileName);
foreach (var prof in Profiles)
{
query = string.Format(@"SELECT StudyBasisId, COUNT(Id) AS CNT FROM ed.extAbit
WHERE LicenseProgramId=@LicenseProgramId AND ObrazProgramId=@ObrazProgramId AND ProfileId=@ProfileId AND (convert(date, DocInsertDate)<=@Date)
AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId
GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : "");
tbl = MainClass.Bdc.GetDataSet(query,
new SortedList <string, object>()
{
{ "@LicenseProgramId", lProgram.LicenseProgramId },
{ "@ObrazProgramId", oProgram.ObrazProgramId },
{ "@ProfileId", prof.ProfileId },
{ "@Date", dtpDate.Value.Date },
{ "@SLGId", iStudyLevelGroupId },
{ "@FacultyId", iFacultyId ?? 0 },
{ "@StudyFormId", iStudyFormId }
}).Tables[0];
sum_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int>("CNT")).DefaultIfEmpty(0).First();
sum_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int>("CNT")).DefaultIfEmpty(0).First();
//kcp_b = (from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 1 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First();
//kcp_p = (from DataRow x in tbl.Rows where x.Field<int>("StudyBasisId") == 2 select x.Field<int?>("KCP")).DefaultIfEmpty(0).First();
query = string.Format(@"SELECT StudyBasisId, SUM(KCP) AS KCP FROM ed.qEntry WHERE LicenseProgramId=@LicenseProgramId
AND ObrazProgramId=@ObrazProgramId AND ProfileId=@ProfileId
AND StudyLevelGroupId=@SLGId {0} AND StudyFormId=@StudyFormId GROUP BY StudyBasisId", iFacultyId != null ? "AND FacultyId=@FacultyId" : "");
tbl = MainClass.Bdc.GetDataSet(query,
new SortedList <string, object>()
{
{ "@LicenseProgramId", lProgram.LicenseProgramId },
{ "@ObrazProgramId", oProgram.ObrazProgramId },
{ "@ProfileId", prof.ProfileId },
{ "@Date", dtpDate.Value.Date },
{ "@SLGId", iStudyLevelGroupId },
{ "@FacultyId", iFacultyId ?? 0 },
{ "@StudyFormId", iStudyFormId }
}).Tables[0];
kcp_b = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 1 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First();
kcp_p = (from DataRow x in tbl.Rows where x.Field <int>("StudyBasisId") == 2 select x.Field <int?>("KCP")).DefaultIfEmpty(0).First();
conc_b = kcp_b == null ? 0f : (sum_b == 0 ? 0f : (float)sum_b / (float)kcp_b.Value);
conc_p = kcp_p == null ? 0f : (sum_p == 0 ? 0f : (float)sum_p / (float)kcp_p.Value);
AddRow(new StatRow
{
Num = (++num_pp).ToString(),
Name = " " + prof.ProfileName,
Code = "",//oProgram.ObrazProgramCrypt,
PlanB = kcp_b.HasValue ? kcp_b.ToString() : "0",
PlanP = kcp_p.HasValue ? kcp_p.ToString() : "0",
SumB = sum_b.ToString(),
SumP = sum_p.ToString(),
ConcB = kcp_b == 0 ? "!КЦ" : Math.Round(conc_b, 2).ToString(),
ConcP = kcp_p == 0 ? "!КЦ" : Math.Round(conc_p, 2).ToString()
});
wc.PerformStep();
}
wc.PerformStep();
}
wc.PerformStep();
}
AddRow(new StatRow()
{
Num = "",
Name = "",
Code = "Всего",
PlanB = iTotalPlanBudzh.ToString(),
PlanP = iTotalPlanPlatn.ToString(),
SumB = iTotalBudzh.ToString(),
SumP = iTotalPlatn.ToString(),
ConcB = "-",
ConcP = "-"
});
wc.Close();
}
}
private void FillGrid()
{
using (PriemEntities context = new PriemEntities())
{
Watch wc = new Watch(300);
wc.Show();
var dates = (from ab in context.qAbitAll
where ab.DocInsertDate != null && ab.DocInsertDate > dtpStart.Value && ab.DocInsertDate <= dtpEnd.Value &&
ab.StudyLevelGroupId == StudyLevelGroupId && (FacultyId.HasValue ? (ab.FacultyId == FacultyId.Value) : (true))
select ab.DocInsertDate).ToList().Select(x => x.Date).Distinct().OrderBy(x => x);
string query = string.Format(@"SELECT DISTINCT LicenseProgramId, LicenseProgramCode + ' ' + LicenseProgramName AS Profession,
ObrazProgramId, ObrazProgramCrypt + ' ' + ObrazProgramName AS ObrazProgram, ProfileId, ProfileName, convert(date, DocInsertDate) AS Date, COUNT(extAbit.Id) AS CNT
FROM ed.extAbit
INNER JOIN ed.Person ON Person.Id = extAbit.PersonId
WHERE StudyLevelGroupId='{0}' AND DocInsertDate IS NOT NULL AND convert(date, DocInsertDate)>=@DateStart AND convert(date, DocInsertDate)<=@DateEnd {1} {2} {3}
GROUP BY LicenseProgramId, LicenseProgramCode, LicenseProgramName, ObrazProgramId, ObrazProgramCrypt, ObrazProgramName, ProfileId, ProfileName, convert(date, DocInsertDate)",
StudyLevelGroupId.ToString(), FacultyId == null ? "" : " AND FacultyId='" + FacultyId.ToString() + "' ",
StudyBasisId == null ? "" : " AND StudyBasisId='" + StudyBasisId.ToString() + "' ",
RegionId.HasValue ? " AND Person.RegionId='" + RegionId.Value.ToString() + "' " : "");
System.Data.DataTable tblStatRaw = MainClass.Bdc.GetDataSet(query,
new SortedList <string, object>()
{
{ "@DateStart", dtpStart.Value }, { "@DateEnd", dtpEnd.Value }
}).Tables[0];
query = string.Format(@"SELECT LicenseProgramId, ObrazProgramId, ProfileId, SUM(KCP) AS KCP
FROM ed.qEntry
WHERE StudyLevelGroupId='{0}' {1} {2}
GROUP BY LicenseProgramId, ObrazProgramId, ProfileId", StudyLevelGroupId.ToString(),
FacultyId == null ? "" : " AND FacultyId='" + FacultyId.ToString() + "' ",
StudyBasisId == null ? "" : " AND StudyBasisId='" + StudyBasisId.ToString() + "' ");
System.Data.DataTable tblKC = MainClass.Bdc.GetDataSet(query).Tables[0];
query = string.Format(@"SELECT LicenseProgramId, ObrazProgramId, ProfileId, COUNT(extAbit.Id) AS CNT
FROM ed.extAbit
INNER JOIN ed.Person ON Person.Id = extAbit.PersonId
WHERE StudyLevelGroupId='{0}' {1} {2} {3}
GROUP BY LicenseProgramId, ObrazProgramId, ProfileId",
StudyLevelGroupId.ToString(),
FacultyId == null ? "" : " AND FacultyId='" + FacultyId.ToString() + "' ",
StudyBasisId == null ? "" : " AND StudyBasisId='" + StudyBasisId.ToString() + "' ",
RegionId.HasValue ? " AND Person.RegionId='" + RegionId.Value.ToString() + "' " : "");
System.Data.DataTable tblSumAbit = MainClass.Bdc.GetDataSet(query).Tables[0];
var SumAbit = from DataRow rw in tblSumAbit.Rows
select new
{
LicenseProgramId = rw.Field <int>("LicenseProgramId"),
ObrazProgramId = rw.Field <int>("ObrazProgramId"),
ProfileId = rw.Field <Guid?>("ProfileId"),
CNT = rw.Field <int>("CNT")
};
var KCP_Data = from DataRow rw in tblKC.Rows
select new
{
LicenseProgramId = rw.Field <int>("LicenseProgramId"),
ObrazProgramId = rw.Field <int>("ObrazProgramId"),
ProfileId = rw.Field <Guid?>("ProfileId"),
KCP = rw.Field <int>("KCP")
};
var _dataFULL = (from DataRow rw in tblStatRaw.Rows
select new
{
LicenseProgramId = rw.Field <int>("LicenseProgramId"),
LicenseProgramName = rw.Field <string>("Profession"),
ObrazProgramId = rw.Field <int>("ObrazProgramId"),
ObrazProgramName = rw.Field <string>("ObrazProgram"),
ProfileId = string.IsNullOrEmpty(rw["ProfileId"].ToString()) ? (Guid?)null : rw.Field <Guid?>("ProfileId"),
ProfileName = rw.Field <string>("ProfileName"),
Date = rw.Field <DateTime>("Date"),
CNT = rw.Field <int>("CNT")
}).ToList();
System.Data.DataTable tblStat = new System.Data.DataTable();
dgvStatGrid.DataSource = null;
tblStat.Columns.Add(new DataColumn("Name"));
tblStat.Columns.Add(new DataColumn("KCP"));
tblStat.Columns.Add(new DataColumn("SUM"));
foreach (var d in dates)
{
tblStat.Columns.Add(new DataColumn(d.ToShortDateString()));
}
var lp_ids =
(from x in _dataFULL
select new { x.LicenseProgramId, x.LicenseProgramName }).Distinct().OrderBy(x => x.LicenseProgramName);
foreach (var lp in lp_ids)
{
//создаём строку
DataRow rwLP = tblStat.NewRow();
rwLP.SetField <string>("Name", lp.LicenseProgramName);
int KCP = KCP_Data.Where(x => x.LicenseProgramId == lp.LicenseProgramId).Select(x => x.KCP).Sum();
rwLP.SetField <int>("KCP", KCP);
int AbSum = SumAbit.Where(x => x.LicenseProgramId == lp.LicenseProgramId).Select(x => x.CNT).Sum();
rwLP.SetField <int>("SUM", AbSum);
foreach (var d in dates)
{
int cnt = (from x in _dataFULL
where x.LicenseProgramId == lp.LicenseProgramId && x.Date.Date == d
select x.CNT).DefaultIfEmpty(0).Sum();
rwLP.SetField <int>(d.ToShortDateString(), cnt);
}
tblStat.Rows.Add(rwLP);
//собираем вложенные образовательные программы
var op_lst = (from x in _dataFULL
where x.LicenseProgramId == lp.LicenseProgramId
select new { x.ObrazProgramId, x.ObrazProgramName }).Distinct().OrderBy(x => x.ObrazProgramName);
foreach (var op in op_lst)
{
//создаём строку
DataRow rwOP = tblStat.NewRow();
rwOP.SetField <string>("Name", " " + op.ObrazProgramName);
KCP = KCP_Data.Where(x => x.LicenseProgramId == lp.LicenseProgramId && x.ObrazProgramId == op.ObrazProgramId).Select(x => x.KCP).Sum();
rwOP.SetField <int>("KCP", KCP);
AbSum = SumAbit.Where(x => x.LicenseProgramId == lp.LicenseProgramId && x.ObrazProgramId == op.ObrazProgramId).Select(x => x.CNT).Sum();
rwOP.SetField <int>("SUM", AbSum);
foreach (var d in dates)
{
int cnt = (from x in _dataFULL
where x.LicenseProgramId == lp.LicenseProgramId && x.Date.Date == d && x.ObrazProgramId == op.ObrazProgramId
select x.CNT).DefaultIfEmpty(0).Sum();
rwOP.SetField <int>(d.ToShortDateString(), cnt);
}
tblStat.Rows.Add(rwOP);
var prof_lst = (from x in _dataFULL
where x.LicenseProgramId == lp.LicenseProgramId && x.ObrazProgramId == op.ObrazProgramId && x.ProfileId != null
select new { x.ProfileId, x.ProfileName }).Distinct().OrderBy(x => x.ProfileName);
foreach (var prof in prof_lst)
{
//создаём строку
DataRow rwProf = tblStat.NewRow();
rwProf.SetField <string>("Name", " " + prof.ProfileName);
KCP = KCP_Data.Where(x => x.LicenseProgramId == lp.LicenseProgramId &&
x.ObrazProgramId == op.ObrazProgramId && x.ProfileId == prof.ProfileId)
.Select(x => x.KCP).Sum();
rwProf.SetField <int>("KCP", KCP);
AbSum = SumAbit.Where(x => x.LicenseProgramId == lp.LicenseProgramId &&
x.ObrazProgramId == op.ObrazProgramId && x.ProfileId == prof.ProfileId).Select(x => x.CNT).Sum();
rwProf.SetField <int>("SUM", AbSum);
foreach (var d in dates)
{
int cnt = (from x in _dataFULL
where x.LicenseProgramId == lp.LicenseProgramId && x.Date.Date == d &&
x.ObrazProgramId == op.ObrazProgramId && x.ProfileId == prof.ProfileId
select x.CNT).DefaultIfEmpty(0).Sum();
rwProf.SetField <int>(d.ToShortDateString(), cnt);
}
tblStat.Rows.Add(rwProf);
wc.PerformStep();
}
wc.PerformStep();
}
wc.PerformStep();
}
dgvStatGrid.DataSource = tblStat;
dgvStatGrid.Columns["Name"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
foreach (var d in dates)
{
dgvStatGrid.Columns[d.ToShortDateString()].Width = 63;
}
dgvStatGrid.Columns["Name"].HeaderText = "Название";
dgvStatGrid.Columns["KCP"].HeaderText = "КЦ";
dgvStatGrid.Columns["KCP"].Width = 50;
dgvStatGrid.Columns["SUM"].HeaderText = "Заявлений с начала приёма";
dgvStatGrid.Columns["SUM"].Width = 63;
dgvStatGrid.Columns["KCP"].Frozen = true;
dgvStatGrid.Columns["Name"].Frozen = true;
dgvStatGrid.Columns["SUM"].Frozen = true;
dgvStatGrid.AllowUserToOrderColumns = false;
wc.Close();
wc = null;
}
}
private void SetMarksForExam(int?examId)
{
try
{
using (PriemEntities context = new PriemEntities())
{
int examInostr = 65;
int filFacId = 18;
string flt_backDoc = " AND ed.qAbiturient.BackDoc = 0 ";
string flt_enable = " AND ed.qAbiturient.NotEnabled = 0 ";
string flt_protocol = " AND ProtocolTypeId = 1 AND IsOld = 0 AND Excluded = 0 ";
string flt_status = " AND ed.extFBSStatus.FBSStatusId IN (1,4) ";
string flt_mark = string.Format(" AND NOT EXISTS( SELECT ed.Mark.Value FROM ed.Mark INNER JOIN ed.extExamInEntry ON ed.Mark.ExamInEntryId = ed.extExamInEntry.Id WHERE ed.Mark.AbiturientId = ed.qAbiturient.Id AND ed.extExamInEntry.ExamId = {0} AND ed.extExamInEntry.IsAdditional=0) ", examId);
string flt_hasEge = string.Format(" AND ed.Person.Id IN (SELECT PersonId FROM ed.extEgeMark LEFT JOIN ed.EgeToExam ON ed.extEgeMark.EgeExamNameId = ed.EgeToExam.EgeExamNameId WHERE ed.EgeToExam.ExamId = {0})", examId);
string flt_hasExam = string.Format(" AND ed.qAbiturient.EntryId IN (SELECT ed.extExamInEntry.EntryId FROM ed.extExamInEntry WHERE ed.extExamInEntry.ExamId = {0})", examId);
string queryAbits = @"SELECT ed.qAbiturient.Id, ed.qAbiturient.FacultyId, ed.qAbiturient.EntryId FROM ed.qAbiturient
LEFT JOIN ed.Person ON ed.qAbiturient.PersonId = ed.Person.Id LEFT JOIN ed.extFBSStatus ON ed.extFBSStatus.PersonId = Person.Id
LEFT JOIN ed.extProtocol ON extProtocol.AbiturientId = qAbiturient.Id WHERE 1 = 1 ";
DataSet ds = bdc.GetDataSet(queryAbits + GetAbitFilterString() + flt_backDoc + flt_enable + flt_protocol + flt_status + flt_mark + flt_hasExam + flt_hasEge);
wtc.pBar.Maximum += ds.Tables[0].Rows.Count;
//using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.RequiresNew))
//{
try
{
foreach (DataRow dsRow in ds.Tables[0].Rows)
{
int? balls = null;
Guid abId = new Guid(dsRow["Id"].ToString());
Guid entryId = new Guid(dsRow["EntryId"].ToString());
int?exInEntryId = (from eie in context.extExamInEntry
where eie.EntryId == entryId && eie.ExamId == examId
select eie.Id).FirstOrDefault();
if (exInEntryId == null)
{
continue;
}
Guid egeCertificateId;
if (examId != examInostr)
{
balls = (from emm in context.extEgeMarkMaxAbit
join ete in context.EgeToExam
on emm.EgeExamNameId equals ete.EgeExamNameId
where emm.AbiturientId == abId && ete.ExamId == examId
select emm.Value).Max();
egeCertificateId = (from emm in context.extEgeMarkMaxAbit
join ete in context.EgeToExam
on emm.EgeExamNameId equals ete.EgeExamNameId
where emm.AbiturientId == abId && ete.ExamId == examId && emm.Value == balls
select emm.EgeCertificateId).FirstOrDefault();
}
else
{
List <int> lstInostr = (from ete in context.EgeToExam
where ete.ExamId == examInostr
select ete.EgeExamNameId).ToList <int>();
if (dsRow["FacultyId"].ToString() == filFacId.ToString())
{
int?egeExamNameId = (from etl in context.EgeToLanguage
join ab in context.qAbiturient
on etl.LanguageId equals ab.LanguageId
where etl.ExamId == examInostr && ab.Id == abId
select etl.EgeExamNameId).FirstOrDefault();
if (egeExamNameId != null)
{
balls = (from emm in context.extEgeMarkMaxAbit
where emm.AbiturientId == abId && emm.EgeExamNameId == egeExamNameId
select emm.Value).Max();
}
egeCertificateId = (from emm in context.extEgeMarkMaxAbit
join ete in context.EgeToExam
on emm.EgeExamNameId equals ete.EgeExamNameId
where emm.AbiturientId == abId && ete.ExamId == examId && emm.Value == balls
select emm.EgeCertificateId).FirstOrDefault();
}
else
{
int cntEM = (from emm in context.extEgeMarkMaxAbit
where lstInostr.Contains(emm.EgeExamNameId) && emm.AbiturientId == abId
select emm.EgeMarkId).Count();
if (cntEM > 1)
{
int?egeExamNameId = (from etl in context.EgeToLanguage
join ab in context.qAbiturient
on etl.LanguageId equals ab.LanguageId
where etl.ExamId == examInostr && ab.Id == abId
select etl.EgeExamNameId).FirstOrDefault();
if (egeExamNameId != null)
{
balls = (from emm in context.extEgeMarkMaxAbit
where emm.AbiturientId == abId && emm.EgeExamNameId == egeExamNameId
select emm.Value).Max();
}
egeCertificateId =
(from emm in context.extEgeMarkMaxAbit
join ete in context.EgeToExam
on emm.EgeExamNameId equals ete.EgeExamNameId
where emm.AbiturientId == abId && ete.ExamId == examId && emm.Value == balls
select emm.EgeCertificateId).FirstOrDefault();
}
else
{
balls = (from emm in context.extEgeMarkMaxAbit
join ete in context.EgeToExam
on emm.EgeExamNameId equals ete.EgeExamNameId
where emm.AbiturientId == abId && ete.ExamId == examId
select emm.Value).Max();
egeCertificateId =
(from emm in context.extEgeMarkMaxAbit
join ete in context.EgeToExam
on emm.EgeExamNameId equals ete.EgeExamNameId
where emm.AbiturientId == abId && ete.ExamId == examId && emm.Value == balls
select emm.EgeCertificateId).FirstOrDefault();
}
}
}
if (balls != null)
{
context.Mark_Insert(abId, exInEntryId, balls, dtDateExam.Value.Date, true, false, false, null, null, egeCertificateId);
}
else
{
continue;
}
wtc.PerformStep();
marksCount++;
}
//transaction.Complete();
}
catch (Exception exc)
{
throw new Exception("Ошибка загрузки оценок1: " + exc.Message);
}
// }
}
}
catch (Exception ex)
{
WinFormsServ.Error("Ошибка загрузки оценок " + ex.Message);
wtc.Close();
}
}
private void btnPrintMatrix_Click(object sender, EventArgs e)
{
using (PriemEntities context = new PriemEntities())
{
var facs = context.SP_Faculty.Select(x => new { x.Id, x.Name, x.Acronym }).ToList();
DataTable dt = new DataTable();
dt.Columns.Add("Fac");
foreach (string s in facs.Select(x => x.Name))
{
dt.Columns.Add(s);
}
Watch wc = new Watch(facs.Count * 2);
wc.Show();
foreach (var f in facs)
{
DataRow rw = dt.NewRow();
rw["Fac"] = f.Name;
DataTable tblData = GetTableIntersect(f.Id);
var data =
from DataRow row in tblData.Rows
select new
{
Id = row.Field <int>("FacultyId"),
FacultyName = row.Field <string>("Факультет"),
CNT = row.Field <int>("Количество людей")
};
foreach (var d in data)
{
rw[d.FacultyName] = d.CNT;
}
dt.Rows.Add(rw);
wc.PerformStep();
}
RtfDocument doc = new RtfDocument(PaperSize.A4, PaperOrientation.Landscape, Lcid.Russian);
RtfTable table = doc.addTable(dt.Rows.Count + 1, dt.Columns.Count);
table.cell(0, 0).addParagraph().Text = "";
int m = 0;
foreach (var f in facs)
{
table.FillCell(0, ++m, f.Acronym, FontStyleFlag.Normal);
}
int i = 1;
table.CellPadding = 1.2f;
table.setInnerBorder(RtfWriter.BorderStyle.Single, 0.7f);
table.setOuterBorder(RtfWriter.BorderStyle.Single, 1f);
foreach (DataRow r in dt.Rows)
{
table.setColWidth(0, 260f);
for (int j = 0; j < r.ItemArray.Count(); j++)
{
table.FillCell(i, j, i == j ? "-" : r[j].ToString(), j > 0 ? FontStyleFlag.Bold : FontStyleFlag.Normal);
}
i++;
wc.PerformStep();
}
wc.Close();
string fname = MainClass.saveTempFolder + "/stat.rtf";
doc.save(fname);
System.Diagnostics.Process.Start(fname);
}
}
