public Dictionary <string, Dictionary <string, StudentScoreInfo> > GetStudentScoreDict(string item)
{
Dictionary <string, Dictionary <string, StudentScoreInfo> > value = new Dictionary <string, Dictionary <string, StudentScoreInfo> >();
try
{
//string item1 = "學業、專業及實習、國英數(部定必修)。";
//string item2 = "學業、專業及實習(部定必修)、國英數(部定必修)。";
string qry = "";
if (item == "學業、專業及實習、國英數(部定必修)。")
{
qry = @"
SELECT
ref_student_id AS student_id
,item_type
,item_name
,rank_type
,rank_name
,matrix_count
,score
,rank
,pr
,percentile
FROM rank_matrix
INNER JOIN rank_detail
ON rank_matrix.id = rank_detail.ref_matrix_id
WHERE rank_matrix.item_type = '5學期/技職繁星比序'
AND rank_type IN('學群排名','科排名')
AND item_name in('學業','專業及實習','國文部必','數學部必','英文部必')
AND rank_matrix.is_alive = true
";
}
if (item == "學業、專業及實習(部定必修)、國英數(部定必修)。")
{
qry = @"
SELECT
ref_student_id AS student_id
,item_type
,item_name
,rank_type
,rank_name
,matrix_count
,score
,rank
,pr
,percentile
FROM rank_matrix
INNER JOIN rank_detail
ON rank_matrix.id = rank_detail.ref_matrix_id
WHERE rank_matrix.item_type = '5學期/技職繁星比序'
AND rank_type IN('學群排名','科排名')
AND item_name in('學業','專業及實習部必','國文部必','數學部必','英文部必')
AND rank_matrix.is_alive = true
";
}
// 111學年度適用
if (item == "學業、專業及實習(部定必修)、技能領域(部定必修)、國英數(部定必修)")
{
qry = @"
SELECT
ref_student_id AS student_id
,item_type
,item_name
,rank_type
,rank_name
,matrix_count
,score
,rank
,pr
,percentile
FROM rank_matrix
INNER JOIN rank_detail
ON rank_matrix.id = rank_detail.ref_matrix_id
WHERE rank_matrix.item_type = '5學期/技職繁星比序(111年學年度適用)'
AND rank_type IN('學群排名','科排名')
AND item_name in('學業','專業及實習部必','國文部必','數學部必','英文部必','技能領域部必')
AND rank_matrix.is_alive = true
";
}
if (item == "學業、專業及實習、技能領域、國英數")
{
qry = @"
SELECT
ref_student_id AS student_id
,item_type
,item_name
,rank_type
,rank_name
,matrix_count
,score
,rank
,pr
,percentile
FROM rank_matrix
INNER JOIN rank_detail
ON rank_matrix.id = rank_detail.ref_matrix_id
WHERE rank_matrix.item_type = '5學期/技職繁星比序(111年學年度適用)'
AND rank_type IN('學群排名','科排名')
AND item_name in('學業','專業及實習','國文','數學','英文','技能領域')
AND rank_matrix.is_alive = true
";
}
if (qry != "")
{
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(qry);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
string sid = dr["student_id"].ToString();
// item_name:國文,rank_type:學群排名,rank_name:外語群
string item_name = dr["item_name"].ToString().Replace("部必", "");
string key = item_name + "_" + dr["rank_type"].ToString();
if (!value.ContainsKey(sid))
{
value.Add(sid, new Dictionary <string, StudentScoreInfo>());
}
StudentScoreInfo ssi = new StudentScoreInfo();
ssi.StudentID = sid;
ssi.ItemType = dr["item_type"].ToString();
ssi.ItemName = item_name;
ssi.RankType = dr["rank_type"].ToString();
ssi.RankName = dr["rank_name"].ToString();
int m, r, pr, p;
decimal s;
if (int.TryParse(dr["matrix_count"].ToString(), out m))
{
ssi.MatrixCount = m;
}
if (int.TryParse(dr["rank"].ToString(), out r))
{
ssi.Rank = r;
}
if (int.TryParse(dr["pr"].ToString(), out pr))
{
ssi.PR = pr;
}
if (int.TryParse(dr["percentile"].ToString(), out p))
{
ssi.Percentile = p;
}
if (decimal.TryParse(dr["score"].ToString(), out s))
{
ssi.Score = s;
}
if (!value[sid].ContainsKey(key))
{
value[sid].Add(key, ssi);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(value);
}
/// <summary>
/// 111學年度適用
/// </summary>
/// <param name="proReq"></param>
/// <param name="skillDomainReq"></param>
/// <param name="chineseEngMathReq"></param>
/// <returns></returns>
public Dictionary <string, Dictionary <string, StudentScoreInfo> > GetStudentScoreDict(bool proReq, bool skillDomainReq, bool chineseEngMathReq)
{
Dictionary <string, Dictionary <string, StudentScoreInfo> > value = new Dictionary <string, Dictionary <string, StudentScoreInfo> >();
//try
//{
string strPro = "'專業及實習'";
string strSkillDomain = "'技能領域'";
string strChineseEngMath = "'國文','數學','英文'";
if (proReq)
{
strPro = "'專業及實習部必'";
}
if (skillDomainReq)
{
strSkillDomain = "'技能領域部必'";
}
if (chineseEngMathReq)
{
strChineseEngMath = "'國文部必','數學部必','英文部必'";
}
//
string qry = @"
SELECT
ref_student_id AS student_id
,item_type
,item_name
,rank_type
,rank_name
,matrix_count
,score
,rank
,pr
,percentile
FROM rank_matrix
INNER JOIN rank_detail
ON rank_matrix.id = rank_detail.ref_matrix_id
WHERE rank_matrix.item_type = '5學期/技職繁星比序(111年學年度適用)'
AND rank_type IN('學群排名','科排名')
AND item_name in('學業',{0},{1},{2})
AND rank_matrix.is_alive = true
";
qry = string.Format(qry, strPro, strSkillDomain, strChineseEngMath);
try
{
QueryHelper qh = new QueryHelper();
DataTable dt = qh.Select(qry);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
string sid = dr["student_id"].ToString();
// item_name:國文,rank_type:學群排名,rank_name:外語群
string item_name = dr["item_name"].ToString().Replace("部必", "");
string key = item_name + "_" + dr["rank_type"].ToString();
if (!value.ContainsKey(sid))
{
value.Add(sid, new Dictionary <string, StudentScoreInfo>());
}
StudentScoreInfo ssi = new StudentScoreInfo();
ssi.StudentID = sid;
ssi.ItemType = dr["item_type"].ToString();
ssi.ItemName = item_name;
ssi.RankType = dr["rank_type"].ToString();
ssi.RankName = dr["rank_name"].ToString();
int m, r, pr, p;
decimal s;
if (int.TryParse(dr["matrix_count"].ToString(), out m))
{
ssi.MatrixCount = m;
}
if (int.TryParse(dr["rank"].ToString(), out r))
{
ssi.Rank = r;
}
if (int.TryParse(dr["pr"].ToString(), out pr))
{
ssi.PR = pr;
}
if (int.TryParse(dr["percentile"].ToString(), out p))
{
ssi.Percentile = p;
}
if (decimal.TryParse(dr["score"].ToString(), out s))
{
ssi.Score = s;
}
if (!value[sid].ContainsKey(key))
{
value[sid].Add(key, ssi);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(value);
}
