///// <summary>
///// 根據測試SQL取得教師不排課時段資料
///// </summary>
///// <param name="Connections"></param>
///// <returns></returns>
//public static SIntegrationResult<STeacherBusy> SelectTest(List<Connection> Connections)
//{
// return Select(Connections, NativeQuery.TeacherBusySQL);
//}
/// <summary>
/// 從多個資料來源取得教師不排課時段資料
/// </summary>
/// <param name="Connections"></param>
/// <returns></returns>
public static SIntegrationResult<STeacherBusy> Select(List<Connection> Connections)
{
SIntegrationResult<STeacherBusy> Result = new SIntegrationResult<STeacherBusy>();
#region 取得不同資料來源的教師不排課時段，使用非同步執行
//Parallel.ForEach(Connections.ToConcurrentQueue(), x =>
Connections.ForEach(x =>
{
try
{
List<STeacherBusy> TeacherBusys = Select(x);
Result.Data.AddRange(TeacherBusys);
}
catch (Exception e)
{
Result.AddMessage("下載教師不排課時段時發生錯誤，連線來源『" + x.AccessPoint.Name + "』");
Result.AddMessage(e.Message);
Result.IsSuccess = false;
}
}
);
#endregion
#region 檢查教師不排課時段是否有衝突
if (Result.IsSuccess)
{
try
{
Tuple<List<STeacherBusy>, List<string>> TimeConflictMergeResult = TimeConflictTestByName(Result.Data);
if (TimeConflictMergeResult.Item2.Count > 0)
{
Result.AddMessage("教師不排課時段有時間衝突，請檢查!");
TimeConflictMergeResult.Item2.ForEach(x => Result.AddMessage(x));
Result.IsSuccess = false;
}
else
{
Result.Data = TimeConflictMergeResult.Item1;
Result.AddMessage("已成功下載" + Result.Data.Count + "筆教師不排課時段!已依教師名稱合併為相同的排課資源!");
}
}
catch (Exception e)
{
Result.AddMessage("檢查教師不排課時段是否有衝突時發生錯誤");
Result.AddMessage(e.Message);
Result.IsSuccess = false;
}
}
#endregion
return Result;
#region VB
//CopyTable cnSQL, cnJET, _
// "SELECT * FROM TeacherBusy", _
// "TeacherBusy"
#endregion
}
/// <summary>
/// 下載學期排課資料
/// </summary>
/// <param name="Connections"></param>
/// <param name="SchoolYear"></param>
/// <param name="Semester"></param>
/// <param name="Progress"></param>
/// <returns></returns>
public bool Download(List<Connection> Connections, string SchoolYear, string Semester,Action<int> Progress)
{
this.IsSuccess = true;
this.SchoolYear = SchoolYear;
this.Semester = Semester;
try
{
if (Progress!=null)
Progress(0);
SIntegrationResult<SClass> CourseSectionClassResult = SClass.Select(Connections, SchoolYear, Semester);
ClassResult = SClass.Select(Connections);
IsSuccess &= ClassResult.IsSuccess;
//取得排課專屬的班級名稱
List<string> ClassNames = ClassResult.Data
.Select(x => x.ClassName)
.ToList();
#region 因為排課課程分課是取得獨立課程的名稱，所以需將ID做轉換。
Dictionary<string,string> ClassNameIDMaps = new Dictionary<string,string>();
foreach(SClass Class in ClassResult.Data)
{
string[] ClassIDs = Class.ID.Split(new char[]{','});
string DSNS = ClassIDs[0];
string ClassName = DSNS+","+Class.ClassName;
if (!ClassNameIDMaps.ContainsKey(ClassName))
ClassNameIDMaps.Add(ClassName,Class.ID);
}
#endregion
//若課程分段的班級資料，沒有在排課專屬的班級資料中，就加入
foreach (SClass Class in CourseSectionClassResult.Data)
{
if (!ClassNames.Contains(Class.ClassName))
{
string[] ClassIDs = Class.ID.Split(new char[] { ',' });
if (ClassIDs.Length >= 1)
{
string DSNS = ClassIDs[0];
SClass ExtraClass = new SClass();
ExtraClass.ClassName = Class.ClassName;
ExtraClass.ID = DSNS +","+ Class.ClassName;
ExtraClass.GradeYear = string.Empty;
ExtraClass.NamingRule = string.Empty;
ExtraClass.TeacherName = string.Empty;
ExtraClass.TimeTableID = string.Empty;
ClassResult.Data.Add(ExtraClass);
}
}
}
ClassBusysResult = SClassBusy.Select(Connections);
IsSuccess &= ClassBusysResult.IsSuccess;
if (Progress != null)
Progress(10);
#region 取得教師資料，留意做特殊處理。
//取得課程分段的教師資料
SIntegrationResult<STeacher> CourseSectionTeacherResult = STeacher.SelectByCourseSection(Connections,SchoolYear,Semester);
//取得排課專屬的教師資料
TeacherResult = STeacher.Select(Connections, "_.GetTeacherEx");
//取得排課專屬的教師名稱
List<string> TeacherNames = TeacherResult.Data
.Select(x => x.Name)
.ToList();
//若課程分段的教師資料，沒有在排課專屬的教師資料中，就加入
foreach (STeacher Teacher in CourseSectionTeacherResult.Data)
{
if (!TeacherNames.Contains(Teacher.Name))
{
TeacherResult.Data.Add(Teacher);
}
}
IsSuccess &= TeacherResult.IsSuccess && CourseSectionTeacherResult.IsSuccess;
FullTeacherResult = STeacher.Select(Connections, "_.GetTeacher");
IsSuccess &= FullTeacherResult.IsSuccess;
#endregion
if (Progress != null)
Progress(20);
TeacherBusysResult = STeacherBusy.Select(Connections);
IsSuccess &= TeacherBusysResult.IsSuccess;
if (Progress != null)
Progress(30);
ClassroomResult = SClassroom.Select(Connections);
IsSuccess &= ClassroomResult.IsSuccess;
if (Progress != null)
Progress(40);
ClassroomBusysResult = SClassroomBusy.Select(Connections);
IsSuccess &= ClassroomBusysResult.IsSuccess;
if (Progress != null)
Progress(50);
LocationResult = SLocation.Select(Connections);
IsSuccess &= LocationResult.IsSuccess;
if (Progress != null)
Progress(60);
TimeTableResult = STimeTable.Select(Connections);
IsSuccess &= TimeTableResult.IsSuccess;
if (Progress != null)
Progress(70);
TimeTableSecsResult = STimeTableSec.Select(Connections);
IsSuccess &= TimeTableSecsResult.IsSuccess;
if (Progress != null)
Progress(80);
CourseSectionResult = SCourseSection.Select(Connections, SchoolYear, Semester);
#region 取得課程分段後做ID轉換，嘗試對應到獨立的班級清單。
foreach (SCourseSection Section in CourseSectionResult.Data)
{
if (ClassNameIDMaps.ContainsKey(Section.ClassID))
Section.ClassID = ClassNameIDMaps[Section.ClassID];
}
#endregion
IsSuccess &= CourseSectionResult.IsSuccess;
if (Progress != null)
Progress(90);
}
catch (Exception e)
{
IsSuccess = false;
throw e;
}
if (IsSuccess) Subjects = CourseSectionResult.Data.Select(x => x.Subject).Distinct().ToList();
Progress(100);
return IsSuccess;
}
/// <summary>
/// 取得時間表分段資料
/// </summary>
/// <param name="Connections"></param>
/// <returns></returns>
public static SIntegrationResult<STimeTableSec> Select(List<Connection> Connections)
{
SIntegrationResult<STimeTableSec> Result = new SIntegrationResult<STimeTableSec>();
#region 取得不同資料來源的時間表，使用非同步執行
//Parallel.ForEach(Connections.ToConcurrentQueue(), x =>
Connections.ForEach(x =>
{
try
{
List<STimeTableSec> TimeTableSecs = Select(x);
Result.Data.AddRange(TimeTableSecs);
}
catch (Exception e)
{
Result.AddMessage("下載時間表分段時發生錯誤，連線來源『" + x.AccessPoint.Name + "』");
Result.AddMessage(e.Message);
Result.IsSuccess = false;
}
}
);
#endregion
if (Result.IsSuccess)
Result.AddMessage("已成功下載" + Result.Data.Count + "筆時間表分段資料!");
return Result;
//return Select(Connections, NativeQuery.TimeTableSecSQL);
}
/// <summary>
/// 關閉
/// </summary>
public void Close()
{
this.ClassResult = null;
this.ClassroomBusysResult = null;
this.ClassroomResult = null;
this.CourseSectionResult = null;
this.LocationResult = null;
this.TeacherBusysResult = null;
this.TeacherResult = null;
this.TimeTableResult = null;
this.TimeTableSecsResult = null;
this.Subjects = null;
ContractService.CloseConnection();
IsSuccess = false;
}
private void SetElement(XElement Element, Action<int> Progress)
{
try
{
this.SchoolYear = Element.AttributeText("SchoolYear");
this.Semester = Element.AttributeText("Semester");
ClassResult = new SIntegrationResult<SClass>();
ClassResult.Data = new List<SClass>();
XElement elmClasses = Element.Element("Classes");
if (elmClasses != null)
{
foreach (XElement SubElement in elmClasses.Elements("Class"))
{
SClass Class = new SClass();
Class.Load(SubElement);
ClassResult.Data.Add(Class);
}
}
ClassBusysResult = new SIntegrationResult<SClassBusy>();
ClassBusysResult.Data = new List<SClassBusy>();
XElement elmClassBuys = Element.Element("ClassBusys");
if (elmClassBuys != null)
{
foreach (XElement SubElement in elmClassBuys.Elements("ClassBusy"))
{
SClassBusy ClassBusy = new SClassBusy();
ClassBusy.Load(SubElement);
ClassBusysResult.Data.Add(ClassBusy);
}
}
Progress.Invoke(10);
TeacherResult = new SIntegrationResult<STeacher>();
TeacherResult.Data = new List<STeacher>();
XElement elmTeachers = Element.Element("Teachers");
if (elmTeachers != null)
{
foreach (XElement SubElement in elmTeachers.Elements("Teacher"))
{
STeacher Teacher = new STeacher();
Teacher.Load(SubElement);
TeacherResult.Data.Add(Teacher);
}
}
FullTeacherResult = new SIntegrationResult<STeacher>();
FullTeacherResult.Data = new List<STeacher>();
XElement elmFullTeacher = Element.Element("FullTeachers");
if (elmFullTeacher != null)
{
foreach (XElement SubElement in elmFullTeacher.Elements("Teacher"))
{
STeacher Teacher = new STeacher();
Teacher.Load(SubElement);
FullTeacherResult.Data.Add(Teacher);
}
}
Progress.Invoke(20);
TeacherBusysResult = new SIntegrationResult<STeacherBusy>();
TeacherBusysResult.Data = new List<STeacherBusy>();
XElement elmTeacherBusy = Element.Element("TeacherBusys");
if (elmTeacherBusy != null)
{
foreach (XElement SubElement in elmTeacherBusy.Elements("TeacherBusy"))
{
STeacherBusy TeacherBusy = new STeacherBusy();
TeacherBusy.Load(SubElement);
TeacherBusysResult.Data.Add(TeacherBusy);
}
}
Progress.Invoke(30);
CourseSectionResult = new SIntegrationResult<SCourseSection>();
CourseSectionResult.Data = new List<SCourseSection>();
XElement elmCourseSections = Element.Element("CourseSections");
if (elmCourseSections != null)
{
foreach (XElement SubElement in elmCourseSections.Elements("CourseSection"))
{
SCourseSection CourseSection = new SCourseSection();
CourseSection.Load(SubElement);
CourseSectionResult.Data.Add(CourseSection);
}
}
Progress.Invoke(40);
ClassroomResult = new SIntegrationResult<SClassroom>();
ClassroomResult.Data = new List<SClassroom>();
XElement elmClassrooms = Element.Element("Classrooms");
if (elmClassrooms!=null)
{
foreach (XElement SubElement in elmClassrooms.Elements("Classroom"))
{
SClassroom Classroom = new SClassroom();
Classroom.Load(SubElement);
ClassroomResult.Data.Add(Classroom);
}
}
Progress.Invoke(50);
ClassroomBusysResult = new SIntegrationResult<SClassroomBusy>();
ClassroomBusysResult.Data = new List<SClassroomBusy>();
XElement elmClassroomBusys = Element.Element("ClassroomBusys");
if (elmClassroomBusys != null)
{
foreach (XElement SubElement in elmClassroomBusys.Elements("ClassroomBusy"))
{
SClassroomBusy ClassroomBusy = new SClassroomBusy();
ClassroomBusy.Load(SubElement);
ClassroomBusysResult.Data.Add(ClassroomBusy);
}
}
Progress.Invoke(60);
LocationResult = new SIntegrationResult<SLocation>();
LocationResult.Data = new List<SLocation>();
XElement elmLocations = Element.Element("Locations");
if (elmLocations != null)
{
foreach (XElement SubElement in elmLocations.Elements("Location"))
{
SLocation Location = new SLocation();
Location.Load(SubElement);
LocationResult.Data.Add(Location);
}
}
Progress.Invoke(70);
TimeTableResult = new SIntegrationResult<STimeTable>();
TimeTableResult.Data = new List<STimeTable>();
XElement elmTimeTables = Element.Element("TimeTables");
if (elmTimeTables != null)
{
foreach (XElement SubElement in elmTimeTables.Elements("TimeTable"))
{
STimeTable TimeTable = new STimeTable();
TimeTable.Load(SubElement);
TimeTableResult.Data.Add(TimeTable);
}
}
Progress.Invoke(80);
TimeTableSecsResult = new SIntegrationResult<STimeTableSec>();
TimeTableSecsResult.Data = new List<STimeTableSec>();
XElement elmTimeTableSecs = Element.Element("TimeTableSecs");
if (elmTimeTableSecs != null)
{
foreach (XElement SubElement in elmTimeTableSecs.Elements("TimeTableSec"))
{
STimeTableSec TimeTableSec = new STimeTableSec();
TimeTableSec.Load(SubElement);
TimeTableSecsResult.Data.Add(TimeTableSec);
}
}
Progress.Invoke(90);
Subjects = CourseSectionResult.Data.Select(x => x.Subject).Distinct().ToList();
Progress.Invoke(100);
IsSuccess = true;
}
catch (Exception e)
{
IsSuccess = false;
throw e;
}
}
/// <summary>
/// 依開始日期及結束日期從多個資料來源取得代課資料
/// </summary>
/// <param name="Connections"></param>
/// <param name="StartDate">開始日期</param>
/// <param name="EndDate">結束日期</param>
/// <returns></returns>
public static SIntegrationResult<SSubsuiteCourseSection> Select(List<Connection> Connections, DateTime StartDate, DateTime EndDate)
{
#region 取得不同資料來源的調課記錄，使用非同步執行
SIntegrationResult<SSubsuiteCourseSection> Result = new SIntegrationResult<SSubsuiteCourseSection>();
Connections.ForEach(x =>
{
try
{
#region 產生實際執行的SQL指令
StringBuilder strBuilder = new StringBuilder();
strBuilder.AppendFormat(NativeQuery.SubstituteCourseSectionTemplateSQL, StartDate.ToShortDateString(), EndDate.ToShortDateString());
string strExecuteSQL = strBuilder.ToString();
#endregion
List<SSubsuiteCourseSection> Subsuites = Select(x, strExecuteSQL);
Result.Data.AddRange(Subsuites);
}
catch (Exception e)
{
Result.AddMessage("下載代課資料時發生錯誤，連線來源『" + x.AccessPoint.Name + "』");
Result.AddMessage(e.Message);
Result.IsSuccess = false;
}
}
);
#endregion
if (Result.IsSuccess)
Result.AddMessage("已成功下載" + Result.Data.Count + "筆代課資料");
return Result;
}
///// <summary>
///// 依開始日期及結束日期從多個資料來源取得課程分段資料
///// </summary>
///// <param name="Connections"></param>
///// <param name="StartDate">開始日期</param>
///// <param name="EndDate">結束日期</param>
///// <returns></returns>
//public static SIntegrationResult<SCourseSection> Select(List<Connection> Connections,DateTime StartDate,DateTime EndDate)
//{
// #region 產生實際執行的SQL指令
// StringBuilder strBuilder = new StringBuilder();
// strBuilder.AppendFormat(NativeQuery.WeekCourseSectionTemplateSQL, StartDate.ToShortDateString(), EndDate.ToShortDateString());
// string strExecuteSQL = strBuilder.ToString();
// #endregion
// return Select(Connections, strExecuteSQL);
//}
/// <summary>
/// 依各學校的學年度及學期取得學期課程分段（用來做虛擬課程分段）
/// </summary>
/// <param name="Connections">多個連線來源</param>
/// <param name="DSNSSchoolYearSemesters">連線來源的學年度及學期</param>
/// <returns></returns>
public static SIntegrationResult<SCourseSection> Select(List<Connection> Connections,Dictionary<string,SchoolYearSemester> DSNSSchoolYearSemesters)
{
SIntegrationResult<SCourseSection> Result = new SIntegrationResult<SCourseSection>();
#region 取得不同資料來源的課程分段，使用非同步執行
Connections.ForEach(x =>
{
try
{
if (DSNSSchoolYearSemesters.ContainsKey(x.AccessPoint.Name))
{
#region 產生實際執行的SQL指令
//StringBuilder strBuilder = new StringBuilder();
//strBuilder.AppendFormat(NativeQuery.ScheduledCourseSectionTemplateSQL, DSNSSchoolYearSemesters[x.AccessPoint.Name].SchoolYear, DSNSSchoolYearSemesters[x.AccessPoint.Name].Semester);
//string strExecuteSQL = strBuilder.ToString();
#endregion
List<SCourseSection> CourseSections = Select(x, DSNSSchoolYearSemesters[x.AccessPoint.Name].SchoolYear,DSNSSchoolYearSemesters[x.AccessPoint.Name].Semester);
Result.Data.AddRange(CourseSections);
}
}
catch (Exception e)
{
Result.AddMessage("下載課程分段資料時發生錯誤，連線來源『" + x.AccessPoint.Name + "』");
Result.AddMessage(e.Message);
Result.IsSuccess = false;
}
}
);
#endregion
#region 若有超過一個以上的連線資訊，則重新編號
if (Result.IsSuccess)
Result.AddMessage("已成功下載" + Result.Data.Count + "筆課程分段資料");
#endregion
return Result;
}
/// <summary>
/// 依學年度及學期從多個資料來源取得課程分段資料
/// </summary>
/// <param name="Connections"></param>
/// <param name="SchoolYear"></param>
/// <param name="Semester"></param>
/// <returns></returns>
public static SIntegrationResult<SCourseSection> Select(List<Connection> Connections, string SchoolYear, string Semester)
{
SIntegrationResult<SCourseSection> Result = new SIntegrationResult<SCourseSection>();
#region 取得不同資料來源的課程分段，使用非同步執行
Connections.ForEach(x =>
{
try
{
List<SCourseSection> CourseSections = Select(x,SchoolYear,Semester);
Result.Data.AddRange(CourseSections);
}
catch (Exception e)
{
Result.AddMessage("下載課程分段資料時發生錯誤，連線來源『" + x.AccessPoint.Name + "』");
Result.AddMessage(e.Message);
Result.IsSuccess = false;
}
}
);
#endregion
#region 若有超過一個以上的連線資訊，則重新編號
if (Result.IsSuccess)
Result.AddMessage("已成功下載" + Result.Data.Count + "筆課程分段資料");
#endregion
return Result;
}
