public List<ClubVO> GetClubsBySchoolYear(SchoolYearSemester schoolYearSemester)
{
if (ClubsBySchoolYear.ContainsKey(schoolYearSemester))
return ClubsBySchoolYear[schoolYearSemester];
else
return new List<ClubVO>();
}
public List<Data.MeritRecord> GetMeritsBySchoolYear(SchoolYearSemester schoolYearSemester)
{
if (MeritsBySchoolYear.ContainsKey(schoolYearSemester))
return MeritsBySchoolYear[schoolYearSemester];
else
return new List<Data.MeritRecord>();
}
public List<ServiceVO> GetServicesBySchoolYear(SchoolYearSemester schoolYearSemester)
{
if (ServicesBySchoolYear.ContainsKey(schoolYearSemester))
return ServicesBySchoolYear[schoolYearSemester];
else
return new List<ServiceVO>();
}
public List<DomainVO> GetDomainsBySechoolYear(SchoolYearSemester SchoolYearSemester, string domainName)
{
if(DomainsBySchoolYear.ContainsKey(SchoolYearSemester))
{
if(DomainsBySchoolYear[SchoolYearSemester].ContainsKey(domainName))
{
return DomainsBySchoolYear[SchoolYearSemester][domainName];
}
}
return new List<DomainVO>();
}
//private Dictionary<string, List<DomainVO>> DomainsByDomainName = new Dictionary<string,List<DomainVO>>();
public void AddDomain(SchoolYearSemester SchoolYearSemester, string StudentId, string DomainName, decimal? DomainScore)
{
DomainVO domainVO = new DomainVO();
domainVO.DomainName = DomainName;
if(DomainScore.HasValue)
domainVO.DomainScore = DomainScore.Value;
else
domainVO.DomainScore = 0;
domainVO.SchoolYearSemester = SchoolYearSemester;
domainVO.StudentId = StudentId;
if(!DomainsBySchoolYear.ContainsKey(SchoolYearSemester))
DomainsBySchoolYear.Add(SchoolYearSemester, new Dictionary<string,List<DomainVO>>());
if (!DomainsBySchoolYear[SchoolYearSemester].ContainsKey(DomainName))
DomainsBySchoolYear[SchoolYearSemester].Add(DomainName, new List<DomainVO>());
DomainsBySchoolYear[SchoolYearSemester][DomainName].Add(domainVO);
//if(!DomainsByDomainName.ContainsKey(domainName))
// DomainsByDomainName.Add(domainName, new List<DomainVO>());
//DomainsByDomainName[domainName].Add(domainVO);
}
/// <summary>
/// 轉換學期歷程
/// </summary>
private void ConvertStudentHistoryDict()
{
List<SemesterHistoryRecord> semsHis = SemesterHistory.SelectByStudentIDs(_StudentIDList);
foreach (SemesterHistoryRecord shrec in semsHis)
foreach (SemesterHistoryItem shi in shrec.SemesterHistoryItems)
{
SchoolYearSemester sys = new SchoolYearSemester();
sys.SchoolYear = shi.SchoolYear.ToString();
sys.Semester = shi.Semester.ToString();
sys.GradeYear = shi.GradeYear.ToString();
if (!_StudentHistoryDict.ContainsKey(shi.RefStudentID))
_StudentHistoryDict.Add(shi.RefStudentID, new List<SchoolYearSemester>());
_StudentHistoryDict[shi.RefStudentID].Add(sys);
}
}
public ClubVO(DataRow row)
{
StudentId = ("" + row["ref_student_id"]).Trim();
SchoolYearSemester = new ValueObj.SchoolYearSemester(("" + row["school_year"]).Trim(), ("" + row["semester"]).Trim());
}
/// <summary>
/// 每1000筆資料,分批執行匯入
/// Return是Log資訊
/// </summary>
public override string Import(List <Campus.DocumentValidator.IRowStream> Rows)
{
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
Dictionary <string, string> dsn = new Dictionary <string, string>();
//取得學生學號對比系統編號
if (useImportKey == ImportKey.StudentNumber)
{
dsn = GetStudent().ToDictionary(x => x.Key, x => x.Value);
}
Dictionary <string, List <SubjectScore> > dss = new Dictionary <string, List <SubjectScore> >();
//group ref_student_id , school_year , semseter
foreach (IRowStream each in Rows)
{
string id;
switch (useImportKey)
{
case ImportKey.ID:
id = each.GetValue("學生系統編號");
break;
case ImportKey.StudentNumber:
id = dsn[each.GetValue("學號")];
break;
default:
//沒有主key
return(string.Empty);
}
string key = id + "#" + each.GetValue("學年度") + "#" + each.GetValue("學期");
SchoolYearSemester tmpsys = new SchoolYearSemester()
{
SchoolYear = int.Parse(each.GetValue("學年度")),
Semester = int.Parse(each.GetValue("學期"))
};
if (!dChangedStudentIDs.ContainsKey(tmpsys))
{
dChangedStudentIDs.Add(tmpsys, new List <string>());
}
if (!dChangedStudentIDs[tmpsys].Contains(id))
{
dChangedStudentIDs[tmpsys].Add(id);
}
if (!dss.ContainsKey(key))
{
dss.Add(key, new List <SubjectScore>());
}
SubjectScore ss = new SubjectScore()
{
Subject = each.GetValue("科目"),
Credit = decimal.Parse(each.GetValue("權數")),
Period = decimal.Parse(each.GetValue("節數")),
Score = decimal.Parse(each.GetValue("成績")),
Comment = "",
Domain = each.GetValue("領域"),
Type = textInfo.ToTitleCase(each.GetValue("Type")),
Text = "",
Effort = null
};
int tmp_level;
if (int.TryParse(each.GetValue("Level"), out tmp_level))
{
ss.Level = tmp_level;
}
if (ss.Type == "Regular")
{
ss.GPA = CourseGradeB.Tool.GPA.Eval(ss.Score.Value).Regular;
}
else if (ss.Type == "Honor")
{
ss.GPA = CourseGradeB.Tool.GPA.Eval(ss.Score.Value).Honors;
}
dss[key].Add(ss);
}
try
{
foreach (KeyValuePair <SchoolYearSemester, List <string> > item in dChangedStudentIDs)
{
Dictionary <string, SemesterScoreRecord> dssrUpdate = K12.Data.SemesterScore.SelectBySchoolYearAndSemester(item.Value, item.Key.SchoolYear, item.Key.Semester).ToDictionary(x => x.RefStudentID, x => x);
Dictionary <string, SemesterScoreRecord> dssrInsert = new Dictionary <string, SemesterScoreRecord>();
foreach (string id in item.Value.Distinct())
{
SemesterScoreRecord ssr;
if (!dssrUpdate.ContainsKey(id))
{
dssrInsert.Add(id, new SemesterScoreRecord(id, item.Key.SchoolYear, item.Key.Semester));
ssr = dssrInsert[id];
}
else
{
ssr = dssrUpdate[id];
}
foreach (SubjectScore ss in dss[id + "#" + item.Key.SchoolYear + "#" + item.Key.Semester])
{
if (ssr.Subjects.ContainsKey(ss.Subject))
{
ssr.Subjects[ss.Subject] = ss;
}
else
{
ssr.Subjects.Add(ss.Subject, ss);
}
}
}
//update semester score
if (dssrInsert.Count > 0)
{
List <SemesterScoreRecord>[] tmp = tool.Partition2(dssrInsert.Values.ToList <SemesterScoreRecord>(), 200);
foreach (List <SemesterScoreRecord> t in tmp)
{
K12.Data.SemesterScore.Insert(t);
}
}
if (dssrUpdate.Count > 0)
{
List <SemesterScoreRecord>[] tmp = tool.Partition2(dssrUpdate.Values.ToList <SemesterScoreRecord>(), 200);
foreach (List <SemesterScoreRecord> t in tmp)
{
K12.Data.SemesterScore.Update(t);
}
}
}
}
catch (Exception e)
{
throw e;
}
//ApplicationLog.Log("匯入服務學習記錄(新增)", "匯入", "已匯入新增服務學習時數\n共" + InsertList.Count + "筆");
return(string.Empty);
}
/// <summary>
/// 將資料解析為 doc merge 格式
/// </summary>
private DataTable FillMergeFiledData()
{
string schoolName = School.ChineseName;
DataTable dt = CreateMergeFieldTable();
foreach (string id in dicStudentByID.Keys)
{
DataRow row = dt.NewRow();
row["year"] = DateTime.Now.Year - 1911;
row["month"] = DateTime.Now.Month;
row["day"] = DateTime.Now.Day;
row["school_name"] = schoolName;
row["class_name"] = dicStudentByID[id].ClassName;
row["seat_no"] = dicStudentByID[id].SeatNo;
row["student_name"] = dicStudentByID[id].Name;
// 領域及格數
int passCount = 0;
// 領域加權平均清單
List <decimal> listDomainAvgScore = new List <decimal>();
// 領域
int d = 1;
foreach (string domain in dicStudentByID[id].listDomainFromStu)
{
// 領域名稱
row[$"{d}_domain"] = domain;
// 領域個學期分數與權重清單
List <ScoreRec> listScore = new List <ScoreRec>();
// 學年度學期
int s = 1;
foreach (string key in dicStudentByID[id].dicSchoolYear.Keys)
{
SchoolYearSemester ss = dicStudentByID[id].dicSchoolYear[key];
if (d == 1)
{
row[$"{s}_school_year"] = $"{ss.SchoolYear}學年度";
row[$"{s}_semester"] = $"第{ss.Semester}學期";
}
// 成績
if (dicStudentByID[id].dicScoreByDomainBySchoolYear.ContainsKey(key))
{
if (dicStudentByID[id].dicScoreByDomainBySchoolYear[key].ContainsKey(domain))
{
string score = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].Score;
string originScore = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].OriginScore;
string power = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].Power;
row[$"{d}_domain_{s}_score"] = score == originScore ? score : $"*{score}";
row[$"{d}_d_{s}_p"] = power;
ScoreRec sr = new ScoreRec();
sr.Score = score;
sr.Power = power;
listScore.Add(sr);
}
}
s++;
}
// 領域平均
if (listScore.Count > 0)
{
decimal totalScore = 0;
int domainScoreCount = 0;
foreach (ScoreRec sr in listScore)
{
totalScore += DecimalParser(sr.Score);
domainScoreCount++;
}
if (totalScore != 0)
{
decimal avgScore = Math.Round(totalScore / domainScoreCount, 2, MidpointRounding.AwayFromZero);
row[$"{d}_domain_avg"] = avgScore;
listDomainAvgScore.Add(avgScore);
if (avgScore >= 60)
{
passCount++;
}
}
}
// 及格數
row["pass_domain_count"] = passCount;
d++;
}
// 學期各領域平均
// 個學期領域平均的算術平均
decimal allTotalScore = 0;
int scoreCount = 0;
int sIndex = 1;
foreach (string key in dicStudentByID[id].dicSchoolYear.Keys)
{
// 學年度學期各領域成績資料
List <ScoreRec> listScore = new List <ScoreRec>();
foreach (string domain in dicStudentByID[id].listDomainFromStu)
{
if (dicStudentByID[id].dicScoreByDomainBySchoolYear.ContainsKey(key))
{
if (dicStudentByID[id].dicScoreByDomainBySchoolYear[key].ContainsKey(domain))
{
string score = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].Score;
string power = dicStudentByID[id].dicScoreByDomainBySchoolYear[key][domain].Power;
ScoreRec sr = new ScoreRec();
sr.Score = score;
sr.Power = power;
listScore.Add(sr);
}
}
}
if (listScore.Count > 0)
{
decimal totalScore = 0;
decimal totalPower = 0;
foreach (ScoreRec sr in listScore)
{
totalScore += DecimalParser(sr.Score) * DecimalParser(sr.Power);
totalPower += DecimalParser(sr.Power);
}
// 沒有權重就不幫你算
if (totalPower != 0)
{
decimal score = Math.Round(totalScore / totalPower, 2, MidpointRounding.AwayFromZero);
row[$"{sIndex}_all_domain_avg"] = score;
allTotalScore += score;
scoreCount++;
}
}
sIndex++;
}
// 總平均(算術平均)
if (listDomainAvgScore.Count > 0)
{
//foreach (double score in listDomainAvgScore)
//{
// totalScore += score;
//}
decimal avgScore = Math.Round(allTotalScore / scoreCount, 2, MidpointRounding.AwayFromZero);
row["all_domain_avg"] = avgScore;
}
dt.Rows.Add(row);
}
return(dt);
}
/// <summary>
/// 資料解析
/// </summary>
private void ParseData()
{
foreach (DataRow row in dtRsp.Rows)
{
string stuID = "" + row["id"];
// 新增學生物件
if (!dicStudentByID.ContainsKey(stuID))
{
StudentRec stuRec = new StudentRec();
stuRec.ID = stuID;
stuRec.Name = "" + row["name"];
stuRec.SeatNo = "" + row["seat_no"];
stuRec.ClassName = "" + row["class_name"];
dicStudentByID.Add(stuID, stuRec);
}
// 資料整理
{
StudentRec stuRec = dicStudentByID[stuID];
string schoolYear = "" + row["school_year"];
string semester = "" + row["semester"];
string domain = "" + row["領域"];
string ssKey = schoolYear + semester;
// 沒有領域就忽略
//if (domain != "")
if (listDomainName.Contains(domain) && domain != "特殊需求") //如果領域存在領域資料管理，且不是特殊需求才列印，沒有則忽略-Cynthia 2021.08
{
// 成績
if (!stuRec.dicScoreByDomainBySchoolYear.ContainsKey(ssKey))
{
stuRec.dicScoreByDomainBySchoolYear.Add(ssKey, new Dictionary <string, ScoreRec>());
}
ScoreRec sr = new ScoreRec();
sr.Score = "" + row["成績"];
sr.Power = "" + row["權數"];
sr.OriginScore = "" + row["原始成績"];
stuRec.dicScoreByDomainBySchoolYear[ssKey].Add(domain, sr);
// 領域
if (!stuRec.listDomainFromStu.Contains(domain))
{
stuRec.listDomainFromStu.Add(domain);
}
// 學年度學期
if (!stuRec.dicSchoolYear.Keys.Contains(ssKey))
{
SchoolYearSemester ss = new SchoolYearSemester();
ss.SchoolYear = schoolYear;
ss.Semester = semester;
stuRec.dicSchoolYear.Add(ssKey, ss);
}
}
}
}
// 領域根據對照表做排序
foreach (string id in dicStudentByID.Keys)
{
dicStudentByID[id].listDomainFromStu.Sort(delegate(string a, string b) {
int aIndex = listDomainName.FindIndex(name => name == a);
int bIndex = listDomainName.FindIndex(name => name == b);
if (aIndex > bIndex)
{
return(1);
}
else
{
return(-1);
}
});
}
}
static public void Main()
{
FactoryProvider.FieldFactory.Add(new FieldValidatorFactory());
MenuButton rbItemImport = MotherForm.RibbonBarItems["學生", "資料統計"]["匯入"]["成績相關匯入(穎驊版)"];
rbItemImport["匯入學期成績(穎驊版)"].Click += delegate
{
UpdateHelper uh = new UpdateHelper();
//uh.Execute("TRUNCATE TABLE sems_subj_score");
ImportSemesterScore2 wizard;
try
{
wizard = new ImportSemesterScore2();
wizard.IsSplit = false;
wizard.Execute();
}
catch (System.Exception)
{
throw;
}
if (wizard.dChangedStudentIDs.Count > 0)
{
BackgroundWorker bgw = new BackgroundWorker();
bgw.WorkerReportsProgress = true;
bgw.DoWork += delegate
{
#region DoWork
//調整有上傳學期成績學生
bgw.ReportProgress(1);
List <string> tmp_sid = new List <string>();
foreach (KeyValuePair <SchoolYearSemester, List <string> > item in wizard.dChangedStudentIDs)
{
CourseGradeB.Tool.SetAverage(item.Value, item.Key.SchoolYear, item.Key.Semester);
tmp_sid.AddRange(item.Value);
}
bgw.ReportProgress(2);
List <SemesterHistoryRecord> dshr = K12.Data.SemesterHistory.SelectByStudentIDs(tmp_sid);
Dictionary <SchoolYearSemester, List <string> > dStudentSchoolYearSemester = new Dictionary <SchoolYearSemester, List <string> >();
foreach (SemesterHistoryRecord shr in dshr)
{
foreach (SemesterHistoryItem item in shr.SemesterHistoryItems)
{
SchoolYearSemester tmpsys = new SchoolYearSemester()
{
SchoolYear = item.SchoolYear,
Semester = item.Semester
};
if (!dStudentSchoolYearSemester.ContainsKey(tmpsys))
{
dStudentSchoolYearSemester.Add(tmpsys, new List <string>());
}
if (!dStudentSchoolYearSemester[tmpsys].Contains(item.RefStudentID))
{
dStudentSchoolYearSemester[tmpsys].Add(item.RefStudentID);
}
}
}
bgw.ReportProgress(3);
foreach (KeyValuePair <SchoolYearSemester, List <string> > item in dStudentSchoolYearSemester)
{
CourseGradeB.Tool.SetCumulateGPA(item.Value, item.Key.SchoolYear, item.Key.Semester);
}
bgw.ReportProgress(100);
#endregion
};
bgw.ProgressChanged += delegate(object sender, System.ComponentModel.ProgressChangedEventArgs e) {
string message = e.ProgressPercentage == 100 ? "計算完成" : "計算中...";
FISCA.Presentation.MotherForm.SetStatusBarMessage("匯入學期成績 平均與累計GPA " + message, e.ProgressPercentage);
};
bgw.RunWorkerAsync();
}
};
}