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())
{
//стартовый микро-запрос количества людей по фильтру
string query = @"SELECT COUNT(DISTINCT extAbit.PersonId)
FROM ed.extAbit
WHERE extAbit.StudyLevelGroupId=1 AND FacultyId=" + FacultyId.ToString() + " ";
string where = "";
if (LicenseProgramId != null)
{
where += " AND extAbit.LicenseProgramId=" + LicenseProgramId.ToString();
}
if (ObrazProgramId != null)
{
where += " AND extAbit.ObrazProgramId=" + ObrazProgramId.ToString();
}
if (ProfileId != null)
{
where += " AND extAbit.ProfileId='" + ProfileId.ToString() + "'";
}
if (StudyFormId != null)
{
where += " AND extAbit.StudyFormId=" + StudyFormId.ToString();
}
if (StudyBasisId != null)
{
where += " AND extAbit.StudyBasisId=" + StudyBasisId.ToString();
}
int cnt = (int)MainClass.Bdc.GetValue(query + where);
Watch wc;
if (cnt > 0)
{
wc = new Watch(cnt);
wc.Show();
}
else
{
wc = null;
return;
}
query = @"
SELECT DISTINCT
extAbit.PersonId, extAbit.FIO, EgeExamName.Name, hlpStatMaxApprovedEgeMarks.Value, (CASE WHEN hlpStatMaxApprovedEgeMarks.EgeExamNameId IN (11, 12, 13, 14) THEN 1 ELSE 0 END) AS ForeignLang
FROM ed.extAbit
INNER JOIN ed.extExamInEntry ON extExamInEntry.EntryId = extAbit.EntryId
INNER JOIN ed.EgeToExam ON EgeToExam.ExamId = extExamInEntry.ExamId
INNER JOIN ed.EgeExamName ON EgeExamName.Id = EgeToExam.EgeExamNameId
INNER JOIN ed.hlpStatMaxApprovedEgeMarks ON hlpStatMaxApprovedEgeMarks.PersonId = extAbit.PersonId AND hlpStatMaxApprovedEgeMarks.EgeExamNameId = EgeExamName.Id
WHERE extAbit.StudyLevelGroupId=1 AND extAbit.FacultyId=" + FacultyId.ToString() + " ";
where = "";
if (LicenseProgramId != null)
{
where += " AND extAbit.LicenseProgramId=" + LicenseProgramId.ToString();
}
if (ObrazProgramId != null)
{
where += " AND extAbit.ObrazProgramId=" + ObrazProgramId.ToString();
}
if (ProfileId != null)
{
where += " AND extAbit.ProfileId='" + ProfileId.ToString() + "'";
}
if (StudyFormId != null)
{
where += " AND extAbit.StudyFormId=" + StudyFormId.ToString();
}
if (StudyBasisId != null)
{
where += " AND extAbit.StudyBasisId=" + StudyBasisId.ToString();
}
DataTable tblPersons = MainClass.Bdc.GetDataSet(query + where + " ORDER BY hlpStatMaxApprovedEgeMarks.Value ").Tables[0];
var p_RAW = (from DataRow rw in tblPersons.Rows
select new
{
PersonId = rw.Field <Guid>("PersonId"),
FIO = rw.Field <string>("FIO"),
ExamName = rw.Field <string>("Name"),
IsForeign = rw.Field <int>("ForeignLang") == 1 ? true : false,
Value = rw.Field <int>("Value")
});
query = @"
SELECT PersonId, (case when extAbit.CompetitionId IN (1,2,7,8) then 1 else 0 end) AS VK
FROM ed.extAbit
WHERE extAbit.StudyLevelGroupId=1 AND extAbit.BackDoc=0
AND extAbit.FacultyId=" + FacultyId.ToString() + " ";
where = "";
if (LicenseProgramId != null)
{
where += " AND extAbit.LicenseProgramId=" + LicenseProgramId.ToString();
}
if (ObrazProgramId != null)
{
where += " AND extAbit.ObrazProgramId=" + ObrazProgramId.ToString();
}
if (ProfileId != null)
{
where += " AND extAbit.ProfileId='" + ProfileId.ToString() + "'";
}
if (StudyFormId != null)
{
where += " AND extAbit.StudyFormId=" + StudyFormId.ToString();
}
if (StudyBasisId != null)
{
where += " AND extAbit.StudyBasisId=" + StudyBasisId.ToString();
}
DataTable tblVKs = MainClass.Bdc.GetDataSet(query + where).Tables[0];
var VKs = from DataRow rw in tblVKs.Rows
select new
{
PersonId = rw.Field <Guid>("PersonId"),
VK = rw.Field <int>("VK") == 1 ? true : false
};
List <Guid> persons = p_RAW.Select(x => x.PersonId).Distinct().ToList();
query = @"SELECT DISTINCT
EgeExamName.Name
FROM ed.extExamInEntry
INNER JOIN ed.EgeToExam ON EgeToExam.ExamId = extExamInEntry.ExamId
INNER JOIN ed.EgeExamName ON EgeExamName.Id = EgeToExam.EgeExamNameId";
where = " WHERE extExamInEntry.FacultyId=" + FacultyId.ToString() + " ";
if (LicenseProgramId != null)
{
where += " AND extExamInEntry.LicenseProgramId=" + LicenseProgramId.ToString();
}
if (ObrazProgramId != null)
{
where += " AND extExamInEntry.ObrazProgramId=" + ObrazProgramId.ToString();
}
if (ProfileId != null)
{
where += " AND extExamInEntry.ProfileId='" + ProfileId.ToString();
}
if (StudyFormId != null)
{
where += " AND extExamInEntry.StudyFormId=" + StudyFormId.ToString();
}
if (StudyBasisId != null)
{
where += " AND extExamInEntry.StudyBasisId=" + StudyBasisId.ToString();
}
DataTable tblExams = MainClass.Bdc.GetDataSet(query + where).Tables[0];
dgv.DataSource = null;
DataTable tblSource = new DataTable();
tblSource.Columns.Add("Id", typeof(Guid));
tblSource.Columns.Add("ФИО", typeof(string));
foreach (DataRow rw in tblExams.Rows)
{
tblSource.Columns.Add(rw.Field <string>("Name"), typeof(int));
}
tblSource.Columns.Add("Сумма", typeof(int));
tblSource.Columns.Add("Green", typeof(bool));
Guid PersonId;
foreach (Guid pId in persons)
{
var rwData = from x in p_RAW
where x.PersonId == pId
select new { x.PersonId, x.FIO, x.ExamName, x.Value, x.IsForeign };
DataRow rw = tblSource.NewRow();
PersonId = rwData.First().PersonId;
rw["Id"] = PersonId;
rw["ФИО"] = rwData.First().FIO;
foreach (var ex in rwData.Select(x => new { x.ExamName, x.Value }))
{
rw[ex.ExamName] = ex.Value;
}
rw["Сумма"] = rwData.Where(x => x.IsForeign == false).Select(x => x.Value).DefaultIfEmpty(0).Sum() + rwData.Where(x => x.IsForeign == true).Select(x => x.Value).DefaultIfEmpty(0).Max();
rw["Green"] = VKs.Where(x => x.PersonId == PersonId && x.VK == true).Select(x => x.VK).DefaultIfEmpty(false).First();
tblSource.Rows.Add(rw);
wc.PerformStep();
}
query = "SELECT SUM(KCP) FROM ed.qEntry WHERE StudyLevelGroupId=1 AND FacultyId=" + FacultyId.ToString() + " ";
where = "";
if (LicenseProgramId != null)
{
where += " AND LicenseProgramId=" + LicenseProgramId.ToString();
}
if (ObrazProgramId != null)
{
where += " AND ObrazProgramId=" + ObrazProgramId.ToString();
}
if (ProfileId != null)
{
where += " AND ProfileId='" + ProfileId.ToString();
}
if (StudyFormId != null)
{
where += " AND StudyFormId=" + StudyFormId.ToString();
}
if (StudyBasisId != null)
{
where += " AND StudyBasisId=" + StudyBasisId.ToString();
}
int kcp = (int)MainClass.Bdc.GetValue(query + where);
tbKCP.Text = kcp.ToString();
var p_sums = (from DataRow rw in tblSource.Rows
select new { Id = rw.Field <Guid>("Id"), Sum = rw.Field <int>("Сумма") }).OrderByDescending(x => x.Sum);
int i = VKs.Where(x => x.VK == true).Select(x => x.PersonId).Distinct().Count();
foreach (var p in p_sums)
{
if (++i > kcp)
{
break;
}
for (int j = 0; j < tblSource.Rows.Count; j++)
{
if (tblSource.Rows[j].Field <Guid>("Id") == p.Id)
{
tblSource.Rows[j].SetField <bool>("Green", true);
break;
}
}
}
dgv.DataSource = tblSource;
dgv.Columns["Id"].Visible = false;
dgv.Columns["Green"].Visible = false;
dgv.Sort(dgv.Columns["Сумма"], ListSortDirection.Ascending);
dgv.Columns["ФИО"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
wc.Close();
wc = null;
}
}
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 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);
}
}