/// <summary>
/// 取得評量比例設定
/// </summary>
public static Dictionary <string, decimal> GetScorePercentageHS()
{
Dictionary <string, decimal> returnData = new Dictionary <string, decimal>();
FISCA.Data.QueryHelper qh1 = new FISCA.Data.QueryHelper();
string query1 = @"select id,CAST(regexp_replace( xpath_string(exam_template.extension,'/Extension/ScorePercentage'), '^$', '0') as integer) as ScorePercentage from exam_template";
System.Data.DataTable dt1 = qh1.Select(query1);
foreach (System.Data.DataRow dr in dt1.Rows)
{
string id = dr["id"].ToString();
decimal sp = 50;
if (decimal.TryParse(dr["ScorePercentage"].ToString(), out sp))
{
returnData.Add(id, sp);
}
else
{
returnData.Add(id, 50);
}
}
return(returnData);
}
private void _BGWClassStudentAbsenceDetail_DoWork(object sender, DoWorkEventArgs e)
{
_SchoolYear = K12.Data.School.DefaultSchoolYear;
_GraduateStudentList = getGraduateStudent();
_ReStudentList = getReStudent();
QueryDeptCode(); //建立科別代號表
FISCA.Data.QueryHelper _Q = new FISCA.Data.QueryHelper();
DataTable dt = _Q.Select("select student.id,student.name,student.ref_class_id,student.status,gender,class.grade_year,dept.name as dept_name from student join class on student.ref_class_id=class.id join dept on class.ref_dept_id=dept.id");
List <StudentObj> StudentList = new List <StudentObj>();
foreach (DataRow row in dt.Rows)
{
StudentObj obj = new StudentObj(row);
StudentList.Add(obj);
}
//資料整理
CleanList(StudentList);
DeptDic = SortToDept(_CorrectList); //科別分類
普通科 = getDicByDept(DeptDic, "普通科");
綜合高中科 = getDicByDept(DeptDic, "綜合高中科");
職業科 = getDicByDept(DeptDic, "職業科");
//資料列印
Export();
}
//取得重讀學生物件清單
private List <ReStudentObj> getReStudent()
{
List <ReStudentObj> list = new List <ReStudentObj>();
FISCA.Data.QueryHelper _Q = new FISCA.Data.QueryHelper();
DataTable dt = _Q.Select("select ref_student_id,ss_name,ss_gender,ss_grade_year,ss_dept from update_record where school_year='" + _SchoolYear + "' and update_desc like '%重讀%'");
foreach (DataRow row in dt.Rows)
{
list.Add(new ReStudentObj(row));
}
return(list);
}
//取得上學年畢業生物件清單
private List <GraduateStudentObj> getGraduateStudent()
{
int year = Convert.ToInt32(_SchoolYear) - 1; //當前系統學年度-1
List <GraduateStudentObj> list = new List <GraduateStudentObj>();
FISCA.Data.QueryHelper _Q = new FISCA.Data.QueryHelper();
DataTable dt = _Q.Select("select ref_student_id,ss_name,ss_gender,ss_dept from update_record where update_code='501' and school_year='" + year + "'");
foreach (DataRow row in dt.Rows)
{
list.Add(new GraduateStudentObj(row));
}
return(list);
}
private void DetailContentImproved_Load(object sender, EventArgs e)
{
try
{
UISyncContext = TaskScheduler.FromCurrentSynchronizationContext();
ErrorTip = new ErrorTip();
if (Backend == null)
{
Backend = new FISCA.Data.QueryHelper();
}
}
catch
{
}
}
protected void InitDetailContent()
{
try
{
UISyncContext = TaskScheduler.FromCurrentSynchronizationContext();
ErrorTip = new ErrorTip();
if (Backend == null)
{
Backend = new FISCA.Data.QueryHelper();
}
}
catch
{
}
}
/// <summary>
/// 建立年班檢視。
/// </summary>
public TreeNavViewBase()
{
InitializeComponent();
NameComparer = new CustomStringComparer();
KCFactory = new KeyCatalogFactory() { NameSorter = KeyCatalogComparer, ToStringFormatter = KeyCatalogTitleFormat };
ShowRoot = true;
RootCaption = "所有項目";
Loading = new Node("讀取中...");
UISyncContext = TaskScheduler.FromCurrentSynchronizationContext();
//當 Student.SetFilteredSource 被呼叫時。
SourceChanged += new EventHandler(StudentGradeClassView_SourceChanged);
try
{//在設計模式下會爆炸，懶得處理了...
if (Backend == null)
Backend = new FISCA.Data.QueryHelper();
}
catch { }
}
//取得上學年畢業生物件清單
private List <GraduateStudentObj> getGraduateStudent()
{
_GCleanList = new List <GraduateStudentObj>();
_GErrorList = new List <GraduateStudentObj>();
int year = Convert.ToInt32(_SchoolYear) - 1; //當前系統學年度-1
Dictionary <String, GraduateStudentObj> dic = new Dictionary <string, GraduateStudentObj>();
FISCA.Data.QueryHelper _Q = new FISCA.Data.QueryHelper();
DataTable dt = _Q.Select("select update_record.ref_student_id,update_record.ss_name,student.student_number,update_record.ss_gender,update_record.ss_dept,tag_student.ref_tag_id from update_record left join tag_student on update_record.ref_student_id = tag_student.ref_student_id left join student on update_record.ref_student_id=student.id where update_code='501' and school_year='" + year + "'");
foreach (DataRow row in dt.Rows)
{
String id = row["ref_student_id"].ToString();
String name = row["ss_name"].ToString();
String student_number = row["student_number"].ToString();
String gender = row["ss_gender"].ToString();
String dept = row["ss_dept"].ToString();
String tagid = row["ref_tag_id"].ToString();
if (!dic.ContainsKey(id))
{
dic.Add(id, new GraduateStudentObj(id, name, student_number, gender, dept, new List <String>()));
}
dic[id].TagID.Add(tagid);
}
//判斷性別欄位是否異常
foreach (String id in dic.Keys)
{
if (dic[id].Gender != "1" && dic[id].Gender != "0")
{
_GErrorList.Add(dic[id]);
}
else
{
_GCleanList.Add(dic[id]);
}
}
return(_GCleanList);
}
private void Worker_DoWork(object sender, DoWorkEventArgs e)
{
_worker.ReportProgress(0);
// 以下補考邏輯 仿 原高中成績系統 SmartSchool.Evaluation 處理方式
#region 取得所有學生以及補考資訊
AccessHelper helper = new AccessHelper();
List <StudentRecord> allStudents = new List <StudentRecord>();
List <ClassRecord> allClasses = helper.ClassHelper.GetClass(_classIDList);
//WearyDogComputer computer = new WearyDogComputer();
Utility utility = new Utility();
double currentClass = 1;
double totalClasses = allClasses.Count;
foreach (ClassRecord aClass in allClasses)
{
List <StudentRecord> classStudents = aClass.Students;
//computer.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents);
utility.FillSemesterSubjectScoreInfoWithResit(helper, true, classStudents);
allStudents.AddRange(classStudents);
// 填上學生的修課紀錄
helper.StudentHelper.FillAttendCourse(int.Parse(_schoolYear), int.Parse(_semester), classStudents);
_worker.ReportProgress((int)(currentClass++ *60.0 / totalClasses));
}
double currentStudent = 1;
double totalStudents = allStudents.Count;
#endregion
#region 取得課程資訊 (授課教師)
List <K12.Data.CourseRecord> courseList = K12.Data.Course.SelectBySchoolYearAndSemester(int.Parse(_schoolYear), int.Parse(_semester));
#endregion
Dictionary <string, Dictionary <string, string> > subjectInfo = new Dictionary <string, Dictionary <string, string> >();
Dictionary <string, List <Dictionary <string, string> > > subjectStudentList = new Dictionary <string, List <Dictionary <string, string> > >();
// 補考群組Dict <課程名稱 +"_"+ 科目() +"_"+ 級別()+"_"+ 學分() +"_"+ "課程系統編號()" , UDT_MakeUpGroup>
Dictionary <string, UDT_MakeUpGroup> makeUpGroupDict = new Dictionary <string, UDT_MakeUpGroup>();
// 補考資料 List
List <UDT_MakeUpData> UDT_MakeUpDataList = new List <UDT_MakeUpData>();
// 整理 補考群組 補考資料
foreach (StudentRecord aStudent in allStudents)
{
List <StudentAttendCourseRecord> scattendList = aStudent.AttendCourseList;
string className = aStudent.RefClass.ClassName;
string seatNo = aStudent.SeatNo;
string studentName = aStudent.StudentName;
string studentNumber = aStudent.StudentNumber;
foreach (SemesterSubjectScoreInfo info in aStudent.SemesterSubjectScoreList)
{
if ("" + info.SchoolYear == _schoolYear && "" + info.Semester == _semester && !info.Pass)
{
StudentAttendCourseRecord scaRecord = scattendList.Find(sc => sc.Subject == info.Subject && sc.SubjectLevel == info.Level && sc.Credit == info.CreditDec());
string makeUpGroupKey = "";
// 是否為課程群組分類， 若其分數未取得學分(!info.Pass)、且補考標準， isCourseGroup 為 True
bool isCourseGroup = false;
if (info.Detail.GetAttribute("達補考標準") == "是" && scaRecord != null)
{
isCourseGroup = true;
//string makeUpGroupKey = scaRecord.CourseName + "_科目(" + scaRecord.Subject + ")_級別(" + scaRecord.SubjectLevel + ")_學分(" + scaRecord.Credit + ")_課程系統編號(" + scaRecord.CourseID + ")";
//makeUpGroupKey = scaRecord.CourseName + "_課程系統編號(" + scaRecord.CourseID + ")";
// 2019/04/26 穎驊與恩正確認， 同一學期的 課程名稱 在系統中是唯一值， 因此不必擔心重覆的問題，捨棄上面舊的補考群組名稱。
makeUpGroupKey = scaRecord.CourseName;
}
else if (info.Detail.GetAttribute("達補考標準") == "否" && scaRecord != null)
{
// 2019/04/26 穎驊與恩正確認， 不同課程的未達標準分開建立
makeUpGroupKey = scaRecord.CourseName + "_" + "未達補考標準群組";
}
else
{
// 不論有沒有達補考標準 該學期找不到 修課紀錄 通通分到未分群組
makeUpGroupKey = "未分群組";
}
if (!makeUpGroupDict.ContainsKey(makeUpGroupKey))
{
UDT_MakeUpGroup makeUpGroupRecord = new UDT_MakeUpGroup();
makeUpGroupRecord.MakeUp_Group = makeUpGroupKey;
makeUpGroupRecord.Description = "";
makeUpGroupRecord.MakeUp_Date = "";
makeUpGroupRecord.MakeUp_Time = "";
makeUpGroupRecord.MakeUp_Place = "";
makeUpGroupRecord.Ref_MakeUp_Batch_ID = _batch.UID;
// 只有課程群組 才會有 預設的 閱卷老師
// 授課教師 要另外對照出來(原本 smart school API 沒支援)，目前這邊會先抓教師一
if (isCourseGroup)
{
K12.Data.CourseRecord courseRecord = courseList.Find(cr => cr.ID == "" + scaRecord.CourseID);
makeUpGroupRecord.Ref_Teacher_ID = courseRecord != null ? courseRecord.MajorTeacherID : "";
}
else
{
makeUpGroupRecord.Ref_Teacher_ID = "";
}
makeUpGroupDict.Add(makeUpGroupKey, makeUpGroupRecord);
}
UDT_MakeUpData makeUpData = new UDT_MakeUpData();
// 參考補考梯次ID
makeUpData.Ref_MakeUp_Batch_ID = _batch.UID;
// 參考補考群組ID (這個時候還不會有 因為補考群組也正要新增)
//makeUpData.Ref_MakeUp_Group_ID = "";
makeUpData.Ref_MakeUp_Group_Name = makeUpGroupKey;
// 參考學生系統編號
makeUpData.Ref_Student_ID = aStudent.StudentID;
// 科別
makeUpData.Department = "";
// 科目
makeUpData.Subject = info.Subject;
// 級別
makeUpData.Level = info.Level;
// 學分
makeUpData.Credit = "" + info.CreditDec();
// 校部定
makeUpData.C_Is_Required_By = info.Detail.HasAttribute("修課校部訂") ? info.Detail.GetAttribute("修課校部訂") : "";
// 必選修
makeUpData.C_Is_Required = info.Detail.HasAttribute("修課必選修") ? info.Detail.GetAttribute("修課必選修") : "";
// 原始成績
makeUpData.Score = info.Detail.HasAttribute("原始成績") ? info.Detail.GetAttribute("原始成績") : "";
// 補考成績
makeUpData.MakeUp_Score = info.Detail.HasAttribute("補考成績") ? info.Detail.GetAttribute("補考成績") : "";
// 及格標準
makeUpData.Pass_Standard = info.Detail.HasAttribute("及格標準") ? info.Detail.GetAttribute("及格標準") : "";
// 補考標準
makeUpData.MakeUp_Standard = info.Detail.HasAttribute("補考標準") ? info.Detail.GetAttribute("補考標準") : "";
// 輸入小數位數限制
makeUpData.DecimalNumber = info.Detail.HasAttribute("位數限制") ? info.Detail.GetAttribute("位數限制") : "";
// 成績身分
makeUpData.CalRole = info.Detail.HasAttribute("成績身分") ? info.Detail.GetAttribute("成績身分") : "";
UDT_MakeUpDataList.Add(makeUpData);
}
}
_worker.ReportProgress(60 + (int)(currentStudent++ *20.0 / totalStudents));
}
// LOG 資訊
string _actor = DSAServices.UserAccount;;
string _client_info = ClientInfo.GetCurrentClientInfo().OutputResult().OuterXml;
// 拚 SQL
List <string> makeUpGroupDataList = new List <string>();
foreach (string groupKey in makeUpGroupDict.Keys)
{
UDT_MakeUpGroup makeUpGroupRecord = makeUpGroupDict[groupKey];
string data = string.Format(@"
SELECT
'{0}'::TEXT AS ref_makeup_batch_id
,'{1}'::TEXT AS makeup_group
,'{2}'::TEXT AS ref_teacher_id
,'{3}'::TEXT AS description
,'{4}'::TEXT AS makeup_date
,'{5}'::TEXT AS makeup_time
,'{6}'::TEXT AS makeup_place
", makeUpGroupRecord.Ref_MakeUp_Batch_ID, makeUpGroupRecord.MakeUp_Group, makeUpGroupRecord.Ref_Teacher_ID, makeUpGroupRecord.Description, makeUpGroupRecord.MakeUp_Date, makeUpGroupRecord.MakeUp_Time, makeUpGroupRecord.MakeUp_Place);
makeUpGroupDataList.Add(data);
}
string makeUpGroupDataString = string.Join(" UNION ALL", makeUpGroupDataList);
List <string> UDT_MakeUpDataDataList = new List <string>();
foreach (UDT_MakeUpData makeUpData in UDT_MakeUpDataList)
{
string data = string.Format(@"
SELECT
'{0}'::TEXT AS ref_makeup_batch_id
,'{1}'::TEXT AS ref_makeup_group_name
,'{2}'::TEXT AS ref_student_id
,'{3}'::TEXT AS subject
,'{4}'::TEXT AS level
,'{5}'::TEXT AS credit
,'{6}'::TEXT AS c_is_required_by
,'{7}'::TEXT AS c_is_required
,'{8}'::TEXT AS score
,'{9}'::TEXT AS makeup_score
,'{10}'::TEXT AS pass_standard
,'{11}'::TEXT AS makeup_standard
,'{12}'::TEXT AS decimalnumber
,'{13}'::TEXT AS calrole
", makeUpData.Ref_MakeUp_Batch_ID, makeUpData.Ref_MakeUp_Group_Name, makeUpData.Ref_Student_ID, makeUpData.Subject, makeUpData.Level, makeUpData.Credit, makeUpData.C_Is_Required_By, makeUpData.C_Is_Required, makeUpData.Score, makeUpData.MakeUp_Score, makeUpData.Pass_Standard, makeUpData.MakeUp_Standard, makeUpData.DecimalNumber, makeUpData.CalRole);
UDT_MakeUpDataDataList.Add(data);
}
string makeUpDataDataString = string.Join(" UNION ALL", UDT_MakeUpDataDataList);
string sql = string.Format(@"
WITH makeUpGroupData_row AS(
{0}
)
,makeUpDataData_row AS(
{1}
)
,insert_makeUpGroupData AS (
INSERT INTO $make.up.group(
ref_makeup_batch_id
,makeup_group
,ref_teacher_id
,description
,makeup_date
,makeup_time
,makeup_place
)
SELECT
makeUpGroupData_row.ref_makeup_batch_id::TEXT AS ref_makeup_batch_id
,makeUpGroupData_row.makeup_group::TEXT AS makeup_group
,makeUpGroupData_row.ref_teacher_id::TEXT AS ref_teacher_id
,makeUpGroupData_row.description::TEXT AS description
,makeUpGroupData_row.makeup_date::TEXT AS makeup_date
,makeUpGroupData_row.makeup_time::TEXT AS makeup_time
,makeUpGroupData_row.makeup_place::TEXT AS makeup_place
FROM
makeUpGroupData_row
RETURNING $make.up.group.*
)
,insert_makeUpDataData AS(
INSERT INTO $make.up.data(
ref_makeup_batch_id
,ref_makeup_group_id
,ref_student_id
,subject
,level
,credit
,c_is_required_by
,c_is_required
,score
,makeup_score
,pass_standard
,makeup_standard
,decimalnumber
,calrole
)
SELECT
makeUpDataData_row.ref_makeup_batch_id::TEXT AS ref_makeup_batch_id
,insert_makeUpGroupData.uid::TEXT AS ref_makeup_group_id
,makeUpDataData_row.ref_student_id::TEXT AS ref_student_id
,makeUpDataData_row.subject::TEXT AS subject
,makeUpDataData_row.level::TEXT AS level
,makeUpDataData_row.credit::TEXT AS credit
,makeUpDataData_row.c_is_required_by::TEXT AS c_is_required_by
,makeUpDataData_row.c_is_required::TEXT AS c_is_required
,makeUpDataData_row.score::TEXT AS score
,makeUpDataData_row.makeup_score::TEXT AS makeup_score
,makeUpDataData_row.pass_standard::TEXT AS pass_standard
,makeUpDataData_row.makeup_standard::TEXT AS makeup_standard
,makeUpDataData_row.decimalnumber::TEXT AS decimalnumber
,makeUpDataData_row.calrole::TEXT AS calrole
FROM
makeUpDataData_row
LEFT JOIN insert_makeUpGroupData ON insert_makeUpGroupData.makeup_group = makeUpDataData_row.ref_makeup_group_name
)
,insert_makeUpGroupData_Log AS (
INSERT INTO log(
actor
, action_type
, action
, target_category
, target_id
, server_time
, client_info
, action_by
, description
)
SELECT
'{2}'::TEXT AS actor
, 'Record' AS action_type
, '高中補考群組新增' AS action
, ''::TEXT AS target_category
, '' AS target_id
, now() AS server_time
, '{3}' AS client_info
, '高中補考群組新增'AS action_by
, ' 高中補考 學年度「'|| makeUpGroupData_row_Log.school_year||'」，學期「'|| makeUpGroupData_row_Log.semester||'」， 補考梯次「'|| makeUpGroupData_row_Log.makeup_batch||'」，新增補考群組 「'|| makeUpGroupData_row_Log.makeup_group ||'」，閱卷老師 「'|| COALESCE(makeUpGroupData_row_Log.teacher_name,'') ||'」。' AS description
FROM
(
SELECT
$make.up.batch.school_year AS school_year
,$make.up.batch.semester AS semester
,$make.up.batch.makeup_batch AS makeup_batch
,makeUpGroupData_row.makeup_group AS makeup_group
,teacher.teacher_name AS teacher_name
FROM makeUpGroupData_row
LEFT JOIN $make.up.batch ON $make.up.batch.uid :: TEXT = makeUpGroupData_row.ref_makeup_batch_id
LEFT JOIN teacher ON teacher.id :: TEXT = makeUpGroupData_row.ref_teacher_id
) AS makeUpGroupData_row_Log
)
,insert_makeUpDataData_Log AS (
INSERT INTO log(
actor
, action_type
, action
, target_category
, target_id
, server_time
, client_info
, action_by
, description
)
SELECT
'{2}'::TEXT AS actor
, 'Record' AS action_type
, '高中補考資料新增' AS action
, ''::TEXT AS target_category
, '' AS target_id
, now() AS server_time
, '{3}' AS client_info
, '高中補考資料新增'AS action_by
, ' 高中補考 學年度「'|| makeUpDataData_row_Log.school_year||'」，學期「'|| makeUpDataData_row_Log.semester||'」， 補考梯次「'|| makeUpDataData_row_Log.makeup_batch||'」，補考群組 「'|| makeUpDataData_row_Log.makeup_group ||'」，閱卷老師 「'|| COALESCE(makeUpDataData_row_Log.teacher_name,'') ||'」
新增 補考資料 學生系統編號 「'|| makeUpDataData_row_Log.ref_student_id||'」 ，學號 「'|| makeUpDataData_row_Log.student_number||'」，學生姓名 「'|| makeUpDataData_row_Log.student_name||'」
，科目 「'|| makeUpDataData_row_Log.subject||'」，級別 「'|| makeUpDataData_row_Log.level||'」，學分 「'|| makeUpDataData_row_Log.credit||'」，校部定 「'|| makeUpDataData_row_Log.c_is_required_by||'」，必選修 「'|| makeUpDataData_row_Log.c_is_required||'」
，分數 「'|| makeUpDataData_row_Log.score||'」 ，補考分數 「'|| makeUpDataData_row_Log.makeup_score||'」 ，及格標準 「'|| makeUpDataData_row_Log.pass_standard||'」，補考標準 「'|| makeUpDataData_row_Log.makeup_standard||'」' AS description
FROM
(
SELECT
$make.up.batch.school_year AS school_year
,$make.up.batch.semester AS semester
,$make.up.batch.makeup_batch AS makeup_batch
,insert_makeUpGroupData.makeup_group AS makeup_group
,teacher.teacher_name AS teacher_name
,student.id AS ref_student_id
,student.student_number AS student_number
,student.name AS student_name
,makeUpDataData_row.subject AS subject
,makeUpDataData_row.level AS level
,makeUpDataData_row.credit AS credit
,makeUpDataData_row.credit AS c_is_required_by
,makeUpDataData_row.credit AS c_is_required
,makeUpDataData_row.score AS score
,makeUpDataData_row.makeup_score AS makeup_score
,makeUpDataData_row.pass_standard AS pass_standard
,makeUpDataData_row.makeup_standard AS makeup_standard
FROM makeUpDataData_row
LEFT JOIN insert_makeUpGroupData ON insert_makeUpGroupData.makeup_group = makeUpDataData_row.ref_makeup_group_name
LEFT JOIN $make.up.batch ON $make.up.batch.uid :: TEXT = insert_makeUpGroupData.ref_makeup_batch_id
LEFT JOIN teacher ON teacher.id :: TEXT = insert_makeUpGroupData.ref_teacher_id
LEFT JOIN student ON student.id :: TEXT = makeUpDataData_row.ref_student_id
) AS makeUpDataData_row_Log
)
SELECT 0
", makeUpGroupDataString, makeUpDataDataString, _actor, _client_info);
//K12.Data.UpdateHelper uh = new K12.Data.UpdateHelper();
////執行sql
//uh.Execute(sql);
FISCA.Data.QueryHelper qh = new FISCA.Data.QueryHelper();
//執行sql
// 有補考群組才執行SQL
if (makeUpGroupDataList.Count > 0)
{
qh.Select(sql);
}
else
{
MsgBox.Show("本梯次班級沒有任何學生需要補考，故本梯次沒有任何補考群組。", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}