Exemple #1
0
        private void BgWorkerExport_DoWork(object sender, DoWorkEventArgs e)
        {
            bgWorkerExport.ReportProgress(1);
            try
            {
                // 取得預設樣板
                Workbook  wb  = new Workbook(new MemoryStream(Properties.Resources.Template));
                Worksheet wst = wb.Worksheets[0];


                // 取得學生基本資料
                List <StudentInfo> StudentInfoList = QueryData.GetStudentInfoList3();

                List <string> StudentIDList = new List <string>();
                foreach (StudentInfo si in StudentInfoList)
                {
                    StudentIDList.Add(si.StudentID);
                }

                // 取得地址資訊
                Dictionary <string, JHAddressRecord> AddressDict = new Dictionary <string, JHAddressRecord>();
                List <JHAddressRecord> tmpAddress = JHAddress.SelectByStudentIDs(StudentIDList);
                foreach (JHAddressRecord rec in tmpAddress)
                {
                    if (!AddressDict.ContainsKey(rec.RefStudentID))
                    {
                        AddressDict.Add(rec.RefStudentID, rec);
                    }
                }

                // 取得電話資料
                Dictionary <string, JHPhoneRecord> PhoneDict = new Dictionary <string, JHPhoneRecord>();
                List <JHPhoneRecord> tmpPhone = JHPhone.SelectByStudentIDs(StudentIDList);
                foreach (JHPhoneRecord rec in tmpPhone)
                {
                    if (!PhoneDict.ContainsKey(rec.RefStudentID))
                    {
                        PhoneDict.Add(rec.RefStudentID, rec);
                    }
                }

                bgWorkerExport.ReportProgress(20);

                // 取得監護人父母資訊
                Dictionary <string, JHParentRecord> ParentDict = new Dictionary <string, JHParentRecord>();
                List <JHParentRecord> tmpParent = JHParent.SelectByStudentIDs(StudentIDList);
                foreach (JHParentRecord rec in tmpParent)
                {
                    if (!ParentDict.ContainsKey(rec.RefStudentID))
                    {
                        ParentDict.Add(rec.RefStudentID, rec);
                    }
                }

                // 轉換各項類別對照值
                Dictionary <string, string> MappingTag1 = new Dictionary <string, string>();
                Dictionary <string, string> MappingTag2 = new Dictionary <string, string>();
                Dictionary <string, string> MappingTag3 = new Dictionary <string, string>();
                Dictionary <string, string> MappingTag4 = new Dictionary <string, string>();

                // 取得學生類別
                Dictionary <string, List <string> > StudentTagDict = QueryData.GetStudentTagName(StudentIDList);

                // 解析對照設定
                XElement elmRoot = XElement.Parse(_Configure.MappingContent);
                if (elmRoot != null)
                {
                    foreach (XElement elm in elmRoot.Elements("Group"))
                    {
                        string gpName = elm.Attribute("Name").Value;
                        if (gpName == "學生身分")
                        {
                            foreach (XElement elm1 in elm.Elements("Item"))
                            {
                                string tagName = elm1.Attribute("TagName").Value;
                                if (!MappingTag1.ContainsKey(tagName) && tagName.Length > 0)
                                {
                                    MappingTag1.Add(tagName, elm1.Attribute("Code").Value);
                                }
                            }
                        }

                        if (gpName == "身心障礙")
                        {
                            foreach (XElement elm1 in elm.Elements("Item"))
                            {
                                string tagName = elm1.Attribute("TagName").Value;
                                if (!MappingTag2.ContainsKey(tagName) && tagName.Length > 0)
                                {
                                    MappingTag2.Add(tagName, elm1.Attribute("Code").Value);
                                }
                            }
                        }

                        if (gpName == "學生報名身分設定")
                        {
                            foreach (XElement elm1 in elm.Elements("Item"))
                            {
                                string tagName = elm1.Attribute("TagName").Value;
                                if (!MappingTag3.ContainsKey(tagName) && tagName.Length > 0)
                                {
                                    MappingTag3.Add(tagName, elm1.Attribute("Code").Value);
                                }
                            }
                        }

                        if (gpName == "失業勞工子女")
                        {
                            foreach (XElement elm1 in elm.Elements("Item"))
                            {
                                string tagName = elm1.Attribute("TagName").Value;
                                if (!MappingTag4.ContainsKey(tagName) && tagName.Length > 0)
                                {
                                    MappingTag4.Add(tagName, elm1.Attribute("Code").Value);
                                }
                            }
                        }
                    }
                }



                bgWorkerExport.ReportProgress(40);

                // 取得成績相關資料
                Dictionary <string, List <JHSemesterScoreRecord> > SemesterScoreRecordDict = new Dictionary <string, List <JHSemesterScoreRecord> >();
                List <JHSemesterScoreRecord> tmpSemsScore = JHSemesterScore.SelectByStudentIDs(StudentIDList);
                foreach (JHSemesterScoreRecord rec in tmpSemsScore)
                {
                    if (!SemesterScoreRecordDict.ContainsKey(rec.RefStudentID))
                    {
                        SemesterScoreRecordDict.Add(rec.RefStudentID, new List <JHSemesterScoreRecord>());
                    }

                    SemesterScoreRecordDict[rec.RefStudentID].Add(rec);
                }

                // 取得功過紀錄
                // 功過對照表
                JHMeritDemeritReduceRecord DemeritReduceRecord = JHMeritDemeritReduce.Select();
                // 懲
                Dictionary <string, List <JHDemeritRecord> > DemeritRecordDict = new Dictionary <string, List <JHDemeritRecord> >();
                List <JHDemeritRecord> tmpDemeritRecord = JHDemerit.SelectByStudentIDs(StudentIDList);
                foreach (JHDemeritRecord rec in tmpDemeritRecord)
                {
                    if (rec.Cleared == "是")
                    {
                        continue;
                    }

                    if (rec.OccurDate > _Configure.EndDate)
                    {
                        continue;
                    }
                    else
                    {
                        if (!DemeritRecordDict.ContainsKey(rec.RefStudentID))
                        {
                            DemeritRecordDict.Add(rec.RefStudentID, new List <JHDemeritRecord>());
                        }

                        DemeritRecordDict[rec.RefStudentID].Add(rec);
                    }
                }
                // 獎
                Dictionary <string, List <JHMeritRecord> > MeritRecordDict = new Dictionary <string, List <JHMeritRecord> >();
                List <JHMeritRecord> tmpMeritRecord = JHMerit.SelectByStudentIDs(StudentIDList);
                foreach (JHMeritRecord rec in tmpMeritRecord)
                {
                    if (rec.OccurDate > _Configure.EndDate)
                    {
                        continue;
                    }
                    else
                    {
                        if (!MeritRecordDict.ContainsKey(rec.RefStudentID))
                        {
                            MeritRecordDict.Add(rec.RefStudentID, new List <JHMeritRecord>());
                        }

                        MeritRecordDict[rec.RefStudentID].Add(rec);
                    }
                }

                // 取得服務學習時數
                StudentInfoList = QueryData.FillServiceLearn(StudentIDList, StudentInfoList, _Configure.EndDate);

                // 填入中低收入戶
                StudentInfoList = QueryData.FillIncomeType(StudentIDList, StudentInfoList);


                // 取得學生體適能資料並填入,嘉義版不卡日期,日期傳入不會限制
                StudentInfoList = QueryData.FillStudentFitness(StudentIDList, StudentInfoList, _Configure.EndDate);

                // 取得競賽總積分並填入學生資料
                StudentInfoList = QueryData.FillStudentCompetitionPerformanceSum(StudentIDList, StudentInfoList);

                // 填入 Excel 資料
                int wstRIdx = 1;
                bgWorkerExport.ReportProgress(70);

                foreach (StudentInfo si in StudentInfoList)
                {
                    // 考區代碼 0,嘉義區 10
                    wst.Cells[wstRIdx, 0].PutValue(10);

                    // 集報單位代碼 1
                    wst.Cells[wstRIdx, 1].PutValue(K12.Data.School.Code);

                    // 序號 2
                    wst.Cells[wstRIdx, 2].PutValue(wstRIdx);

                    // 學號 3
                    wst.Cells[wstRIdx, 3].PutValue(si.StudentNumber);

                    // 班級 4
                    wst.Cells[wstRIdx, 4].PutValue(si.ClassName);

                    // 座號 5
                    wst.Cells[wstRIdx, 5].PutValue(si.SeatNo);

                    // 學生姓名 6  // 2021-12-27 有些學校會為了整齊把學生的名字+空白,這裡濾掉
                    wst.Cells[wstRIdx, 6].PutValue(si.StudentName.Replace(" ", "").Replace(" ", ""));

                    // 身分證統一編號 7
                    wst.Cells[wstRIdx, 7].PutValue(si.IDNumber);

                    // 非中華民國身分證號 8
                    if (si.isTaiwanID)
                    {
                        wst.Cells[wstRIdx, 8].PutValue("");
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 8].PutValue("V");
                    }

                    // 性別 9
                    wst.Cells[wstRIdx, 9].PutValue(si.GenderCode);

                    // 出生年(民國年) 10
                    wst.Cells[wstRIdx, 10].PutValue(si.BirthYear);

                    // 出生月 11
                    wst.Cells[wstRIdx, 11].PutValue(si.BirthMonth);

                    // 出生日 12
                    wst.Cells[wstRIdx, 12].PutValue(si.BirthDay);

                    // 畢業學校代碼 13
                    wst.Cells[wstRIdx, 13].PutValue(K12.Data.School.Code);

                    // 畢業年(民國年) 14
                    int gyear;
                    if (int.TryParse(K12.Data.School.DefaultSchoolYear, out gyear))
                    {
                        wst.Cells[wstRIdx, 14].PutValue(gyear + 1);
                    }

                    // 畢肄業 15
                    wst.Cells[wstRIdx, 15].PutValue(1);


                    wst.Cells[wstRIdx, 16].PutValue(0);
                    wst.Cells[wstRIdx, 17].PutValue(0);
                    wst.Cells[wstRIdx, 18].PutValue(0);
                    wst.Cells[wstRIdx, 22].PutValue(0);

                    if (StudentTagDict.ContainsKey(si.StudentID))
                    {
                        foreach (string tagName in StudentTagDict[si.StudentID])
                        {
                            if (MappingTag1.ContainsKey(tagName))
                            {
                                // 學生身分 16
                                wst.Cells[wstRIdx, 16].PutValue(MappingTag1[tagName]);
                            }

                            if (MappingTag2.ContainsKey(tagName))
                            {
                                si.isSpecial = true;
                                // 身心障礙 18
                                wst.Cells[wstRIdx, 18].PutValue(MappingTag2[tagName]);
                            }

                            if (MappingTag3.ContainsKey(tagName))
                            {
                                // 學生報名身分 17
                                wst.Cells[wstRIdx, 17].PutValue(MappingTag3[tagName]);
                            }
                            if (MappingTag4.ContainsKey(tagName))
                            {
                                // 失業勞工子女 22
                                wst.Cells[wstRIdx, 22].PutValue(MappingTag4[tagName]);
                            }
                        }
                    }


                    // 就學區 19,不處理

                    // 低收入戶 20
                    if (si.incomeType1)
                    {
                        wst.Cells[wstRIdx, 20].PutValue(1);
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 20].PutValue(0);
                    }

                    // 中低收入戶 21
                    if (si.incomeType2)
                    {
                        wst.Cells[wstRIdx, 21].PutValue(1);
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 21].PutValue(0);
                    }



                    // 資料授權 23
                    wst.Cells[wstRIdx, 23].PutValue(0);

                    string parentName = "";
                    // 家長姓名 24
                    if (ParentDict.ContainsKey(si.StudentID))
                    {
                        if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].CustodianName))
                        {
                            parentName = ParentDict[si.StudentID].CustodianName;
                        }
                        else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].FatherName))
                        {
                            parentName = ParentDict[si.StudentID].FatherName;
                        }
                        else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].MotherName))
                        {
                            parentName = ParentDict[si.StudentID].MotherName;
                        }
                        else
                        {
                        }
                        wst.Cells[wstRIdx, 24].PutValue(parentName);
                    }


                    // 市內電話 25
                    // 市內電話分機 26
                    // 行動電話 27
                    if (PhoneDict.ContainsKey(si.StudentID))
                    {
                        wst.Cells[wstRIdx, 25].PutValue(PhoneDict[si.StudentID].Contact.Replace("-", "").Replace(")", "").Replace("(", ""));
                        wst.Cells[wstRIdx, 27].PutValue(PhoneDict[si.StudentID].Cell.Replace("-", "").Replace(")", "").Replace("(", ""));
                    }


                    // 郵遞區號 28
                    if (AddressDict.ContainsKey(si.StudentID))
                    {
                        if (AddressDict[si.StudentID].MailingZipCode != null)
                        {
                            string zipCode = AddressDict[si.StudentID].MailingZipCode;

                            if (zipCode.Length >= 3)
                            {
                                zipCode = zipCode.Substring(0, 3);
                            }

                            wst.Cells[wstRIdx, 28].PutValue(zipCode);
                        }

                        // 通訊地址 29
                        wst.Cells[wstRIdx, 29].PutValue(AddressDict[si.StudentID].MailingCounty + AddressDict[si.StudentID].MailingTown + AddressDict[si.StudentID].MailingDistrict + AddressDict[si.StudentID].MailingArea + AddressDict[si.StudentID].MailingDetail);
                    }

                    // 計算分數
                    if (SemesterScoreRecordDict.ContainsKey(si.StudentID))
                    {
                        si.CalcSemsScore5(SemesterScoreRecordDict[si.StudentID]);
                    }

                    // 健康與體育 30
                    if (si.isDomainHelPass)
                    {
                        wst.Cells[wstRIdx, 30].PutValue(1);
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 30].PutValue(0);
                    }

                    // 藝術 31
                    if (si.isDoaminArtPass)
                    {
                        wst.Cells[wstRIdx, 31].PutValue(1);
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 31].PutValue(0);
                    }

                    // 綜合活動 32
                    if (si.isDomainActPass)
                    {
                        wst.Cells[wstRIdx, 32].PutValue(1);
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 32].PutValue(0);
                    }

                    // 品德表現 33
                    List <JHDemeritRecord> recD;
                    List <JHMeritRecord>   recM;

                    if (DemeritRecordDict.ContainsKey(si.StudentID))
                    {
                        recD = DemeritRecordDict[si.StudentID];
                    }
                    else
                    {
                        recD = new List <JHDemeritRecord>();
                    }

                    if (MeritRecordDict.ContainsKey(si.StudentID))
                    {
                        recM = MeritRecordDict[si.StudentID];
                    }
                    else
                    {
                        recM = new List <JHMeritRecord>();
                    }

                    si.CalcDemeritMemeritScore(recD, recM, DemeritReduceRecord);
                    wst.Cells[wstRIdx, 33].PutValue(si.MeritDemeritScore);

                    // 服務學習 34
                    wst.Cells[wstRIdx, 34].PutValue(si.ServiceLearnScore);

                    // 體適能 35
                    si.CalcFitnessScore();
                    wst.Cells[wstRIdx, 35].PutValue(si.FitnessScore);

                    // 競賽表現 36,使用者自行處理
                    if (si.CompPerfSum.HasValue)
                    {
                        wst.Cells[wstRIdx, 36].PutValue(si.CompPerfSum.Value);
                    }
                    else  // 2021-12-27 嘉義協同國中 見安老師要求預設值 0
                    {
                        wst.Cells[wstRIdx, 36].PutValue("0");
                    }

                    // 2021-12-27 嘉義協同國中 見安老師要求預設值全部填1
                    // https://3.basecamp.com/4399967/buckets/15765350/todos/4475736499#__recording_4477516175
                    // 家長意見_高中 37
                    wst.Cells[wstRIdx, 37].PutValue("1");
                    // 家長意見_高職 38
                    wst.Cells[wstRIdx, 38].PutValue("1");
                    // 導師意見_高中 39
                    wst.Cells[wstRIdx, 39].PutValue("1");
                    // 導師意見_高職 40
                    wst.Cells[wstRIdx, 40].PutValue("1");
                    // 輔導教師意見_高中 41
                    wst.Cells[wstRIdx, 41].PutValue("1");
                    // 輔導教師意見_高職 42
                    wst.Cells[wstRIdx, 42].PutValue("1");


                    wstRIdx++;
                }

                bgWorkerExport.ReportProgress(100);

                e.Result = wb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private void BgWorkerExport_DoWork(object sender, DoWorkEventArgs e)
        {
            bgWorkerExport.ReportProgress(1);
            try
            {
                // 取得預設樣板
                Workbook  wb  = new Workbook(new MemoryStream(Properties.Resources.Template));
                Worksheet wst = wb.Worksheets[0];


                // 取得學生基本資料
                List <StudentInfo> StudentInfoList = QueryData.GetStudentInfoList3();

                List <string> StudentIDList = new List <string>();
                foreach (StudentInfo si in StudentInfoList)
                {
                    StudentIDList.Add(si.StudentID);
                }

                // 取得地址資訊
                Dictionary <string, JHAddressRecord> AddressDict = new Dictionary <string, JHAddressRecord>();
                List <JHAddressRecord> tmpAddress = JHAddress.SelectByStudentIDs(StudentIDList);
                foreach (JHAddressRecord rec in tmpAddress)
                {
                    if (!AddressDict.ContainsKey(rec.RefStudentID))
                    {
                        AddressDict.Add(rec.RefStudentID, rec);
                    }
                }

                // 取得電話資料
                Dictionary <string, JHPhoneRecord> PhoneDict = new Dictionary <string, JHPhoneRecord>();
                List <JHPhoneRecord> tmpPhone = JHPhone.SelectByStudentIDs(StudentIDList);
                foreach (JHPhoneRecord rec in tmpPhone)
                {
                    if (!PhoneDict.ContainsKey(rec.RefStudentID))
                    {
                        PhoneDict.Add(rec.RefStudentID, rec);
                    }
                }

                bgWorkerExport.ReportProgress(20);

                // 取得監護人父母資訊
                Dictionary <string, JHParentRecord> ParentDict = new Dictionary <string, JHParentRecord>();
                List <JHParentRecord> tmpParent = JHParent.SelectByStudentIDs(StudentIDList);
                foreach (JHParentRecord rec in tmpParent)
                {
                    if (!ParentDict.ContainsKey(rec.RefStudentID))
                    {
                        ParentDict.Add(rec.RefStudentID, rec);
                    }
                }

                // 轉換各項類別對照值
                Dictionary <string, string> MappingTag1 = new Dictionary <string, string>();
                Dictionary <string, string> MappingTag2 = new Dictionary <string, string>();
                Dictionary <string, string> MappingTag3 = new Dictionary <string, string>();
                Dictionary <string, string> MappingTag4 = new Dictionary <string, string>();

                // 取得學生類別
                Dictionary <string, List <string> > StudentTagDict = QueryData.GetStudentTagName(StudentIDList);

                // 解析對照設定
                XElement elmRoot = XElement.Parse(_Configure.MappingContent);
                if (elmRoot != null)
                {
                    foreach (XElement elm in elmRoot.Elements("Group"))
                    {
                        string gpName = elm.Attribute("Name").Value;
                        if (gpName == "學生身分")
                        {
                            foreach (XElement elm1 in elm.Elements("Item"))
                            {
                                string tagName = elm1.Attribute("TagName").Value;
                                if (!MappingTag1.ContainsKey(tagName) && tagName.Length > 0)
                                {
                                    MappingTag1.Add(tagName, elm1.Attribute("Code").Value);
                                }
                            }
                        }

                        if (gpName == "身心障礙")
                        {
                            foreach (XElement elm1 in elm.Elements("Item"))
                            {
                                string tagName = elm1.Attribute("TagName").Value;
                                if (!MappingTag2.ContainsKey(tagName) && tagName.Length > 0)
                                {
                                    MappingTag2.Add(tagName, elm1.Attribute("Code").Value);
                                }
                            }
                        }

                        if (gpName == "學生報名身分設定")
                        {
                            foreach (XElement elm1 in elm.Elements("Item"))
                            {
                                string tagName = elm1.Attribute("TagName").Value;
                                if (!MappingTag3.ContainsKey(tagName) && tagName.Length > 0)
                                {
                                    MappingTag3.Add(tagName, elm1.Attribute("Code").Value);
                                }
                            }
                        }

                        if (gpName == "失業勞工子女")
                        {
                            foreach (XElement elm1 in elm.Elements("Item"))
                            {
                                string tagName = elm1.Attribute("TagName").Value;
                                if (!MappingTag4.ContainsKey(tagName) && tagName.Length > 0)
                                {
                                    MappingTag4.Add(tagName, elm1.Attribute("Code").Value);
                                }
                            }
                        }
                    }
                }


                // 取得語言認證學生id
                List <string> hasLanguageCertificateIDList = QueryData.GetLanguageCertificate(StudentIDList);

                bgWorkerExport.ReportProgress(40);

                // 取得成績相關資料
                Dictionary <string, List <JHSemesterScoreRecord> > SemesterScoreRecordDict = new Dictionary <string, List <JHSemesterScoreRecord> >();
                List <JHSemesterScoreRecord> tmpSemsScore = JHSemesterScore.SelectByStudentIDs(StudentIDList);
                foreach (JHSemesterScoreRecord rec in tmpSemsScore)
                {
                    if (!SemesterScoreRecordDict.ContainsKey(rec.RefStudentID))
                    {
                        SemesterScoreRecordDict.Add(rec.RefStudentID, new List <JHSemesterScoreRecord>());
                    }

                    SemesterScoreRecordDict[rec.RefStudentID].Add(rec);
                }

                // 取得功過紀錄
                // 功過對照表
                JHMeritDemeritReduceRecord DemeritReduceRecord = JHMeritDemeritReduce.Select();
                // 懲
                Dictionary <string, List <JHDemeritRecord> > DemeritRecordDict = new Dictionary <string, List <JHDemeritRecord> >();
                List <JHDemeritRecord> tmpDemeritRecord = JHDemerit.SelectByStudentIDs(StudentIDList);
                foreach (JHDemeritRecord rec in tmpDemeritRecord)
                {
                    if (rec.Cleared == "是")
                    {
                        continue;
                    }

                    if (rec.OccurDate > _Configure.EndDate)
                    {
                        continue;
                    }
                    else
                    {
                        if (!DemeritRecordDict.ContainsKey(rec.RefStudentID))
                        {
                            DemeritRecordDict.Add(rec.RefStudentID, new List <JHDemeritRecord>());
                        }

                        DemeritRecordDict[rec.RefStudentID].Add(rec);
                    }
                }
                // 獎
                Dictionary <string, List <JHMeritRecord> > MeritRecordDict = new Dictionary <string, List <JHMeritRecord> >();
                List <JHMeritRecord> tmpMeritRecord = JHMerit.SelectByStudentIDs(StudentIDList);
                foreach (JHMeritRecord rec in tmpMeritRecord)
                {
                    if (rec.OccurDate > _Configure.EndDate)
                    {
                        continue;
                    }
                    else
                    {
                        if (!MeritRecordDict.ContainsKey(rec.RefStudentID))
                        {
                            MeritRecordDict.Add(rec.RefStudentID, new List <JHMeritRecord>());
                        }

                        MeritRecordDict[rec.RefStudentID].Add(rec);
                    }
                }

                // 填入幹部資料
                StudentInfoList = QueryData.FillCad(StudentIDList, StudentInfoList);

                // 填入中低收入戶
                StudentInfoList = QueryData.FillIncomeType(StudentIDList, StudentInfoList);

                // 填入競賽成績
                StudentInfoList = QueryData.FillStudentCompetitionScore(StudentIDList, StudentInfoList, _Configure.EndDate);

                // 取得學生體適能資料並填入
                StudentInfoList = QueryData.FillStudentFitness(StudentIDList, StudentInfoList, _Configure.EndDate);

                // 填入 Excel 資料
                int wstRIdx = 1;


                bgWorkerExport.ReportProgress(70);

                // 幹部限制
                List <string> CadreName1 = _Configure.LoadCareNames();

                foreach (StudentInfo si in StudentInfoList)
                {
                    // 考區代碼 0, 12/屏東考區
                    wst.Cells[wstRIdx, 0].PutValue(12);
                    // 集報單位代碼 1,學校代碼
                    wst.Cells[wstRIdx, 1].PutValue(K12.Data.School.Code);
                    // 序號 2
                    wst.Cells[wstRIdx, 2].PutValue(wstRIdx);
                    // 學號 3
                    wst.Cells[wstRIdx, 3].PutValue(si.StudentNumber);
                    // 班級 4
                    wst.Cells[wstRIdx, 4].PutValue(si.ClassName);
                    // 座號 5
                    wst.Cells[wstRIdx, 5].PutValue(si.SeatNo);
                    // 學生姓名 6
                    wst.Cells[wstRIdx, 6].PutValue(si.StudentName);
                    // 身分證統一編號 7
                    wst.Cells[wstRIdx, 7].PutValue(si.IDNumber);

                    // 性別 8
                    wst.Cells[wstRIdx, 8].PutValue(si.GenderCode);

                    // 出生年(民國年) 9
                    wst.Cells[wstRIdx, 9].PutValue(si.BirthYear);
                    // 出生月 10
                    wst.Cells[wstRIdx, 10].PutValue(si.BirthMonth);
                    // 出生日 11
                    wst.Cells[wstRIdx, 11].PutValue(si.BirthDay);
                    // 畢業學校代碼 12
                    wst.Cells[wstRIdx, 12].PutValue(K12.Data.School.Code);

                    // 畢業年(民國年) 13
                    int gyear;
                    if (int.TryParse(K12.Data.School.DefaultSchoolYear, out gyear))
                    {
                        wst.Cells[wstRIdx, 13].PutValue(gyear + 1);
                    }

                    // 畢肄業 14
                    wst.Cells[wstRIdx, 14].PutValue(1);


                    // 就學區 17

                    // 低收入戶 18
                    if (si.incomeType1)
                    {
                        wst.Cells[wstRIdx, 18].PutValue(1);
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 18].PutValue(0);
                    }

                    // 中低收入戶 19
                    if (si.incomeType2)
                    {
                        wst.Cells[wstRIdx, 19].PutValue(1);
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 19].PutValue(0);
                    }

                    wst.Cells[wstRIdx, 15].PutValue("0");
                    wst.Cells[wstRIdx, 16].PutValue("0");
                    wst.Cells[wstRIdx, 20].PutValue("0");
                    wst.Cells[wstRIdx, 28].PutValue("0");

                    if (StudentTagDict.ContainsKey(si.StudentID))
                    {
                        foreach (string tagName in StudentTagDict[si.StudentID])
                        {
                            if (MappingTag1.ContainsKey(tagName))
                            {
                                // 學生身分 15
                                wst.Cells[wstRIdx, 15].PutValue(MappingTag1[tagName]);
                            }

                            if (MappingTag2.ContainsKey(tagName))
                            {
                                si.isSpecial = true;
                                // 身心障礙 16
                                wst.Cells[wstRIdx, 16].PutValue(MappingTag2[tagName]);
                            }

                            if (MappingTag3.ContainsKey(tagName))
                            {
                                // 學生報名身分 28
                                wst.Cells[wstRIdx, 28].PutValue(MappingTag3[tagName]);
                            }
                            if (MappingTag4.ContainsKey(tagName))
                            {
                                // 失業勞工子女 20
                                wst.Cells[wstRIdx, 20].PutValue(MappingTag4[tagName]);
                            }
                        }
                    }



                    // 資料授權 21
                    wst.Cells[wstRIdx, 21].PutValue(0);

                    string parentName = "";


                    // 家長姓名 22
                    if (ParentDict.ContainsKey(si.StudentID))
                    {
                        if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].CustodianName))
                        {
                            parentName = ParentDict[si.StudentID].CustodianName;
                        }
                        else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].FatherName))
                        {
                            parentName = ParentDict[si.StudentID].FatherName;
                        }
                        else if (!string.IsNullOrWhiteSpace(ParentDict[si.StudentID].MotherName))
                        {
                            parentName = ParentDict[si.StudentID].MotherName;
                        }
                        else
                        {
                        }
                        wst.Cells[wstRIdx, 22].PutValue(parentName);
                    }


                    // 市內電話 23
                    // 行動電話 24
                    if (PhoneDict.ContainsKey(si.StudentID))
                    {
                        wst.Cells[wstRIdx, 23].PutValue(PhoneDict[si.StudentID].Contact.Replace("-", "").Replace(")", "").Replace("(", ""));
                        wst.Cells[wstRIdx, 24].PutValue(PhoneDict[si.StudentID].Cell.Replace("-", "").Replace(")", "").Replace("(", ""));
                    }


                    // 郵遞區號 25
                    if (AddressDict.ContainsKey(si.StudentID))
                    {
                        string zipCode = "";

                        if (AddressDict[si.StudentID].MailingZipCode != null)
                        {
                            zipCode = AddressDict[si.StudentID].MailingZipCode;
                        }

                        if (zipCode.Length >= 3)
                        {
                            zipCode = zipCode.Substring(0, 3);
                        }

                        wst.Cells[wstRIdx, 25].PutValue(zipCode);

                        // 通訊地址 26
                        wst.Cells[wstRIdx, 26].PutValue(AddressDict[si.StudentID].MailingCounty + AddressDict[si.StudentID].MailingTown + AddressDict[si.StudentID].MailingDistrict + AddressDict[si.StudentID].MailingArea + AddressDict[si.StudentID].MailingDetail);
                    }
                    // 非中華民國身分證號 27
                    if (si.isTaiwanID)
                    {
                        wst.Cells[wstRIdx, 27].PutValue("");
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 27].PutValue("V");
                    }


                    // 市內電話分機 29

                    // 均衡學習 30
                    // 計算分數
                    if (SemesterScoreRecordDict.ContainsKey(si.StudentID))
                    {
                        si.CalcSemsScore5(SemesterScoreRecordDict[si.StudentID]);
                        // 成績滿5學期才顯示
                        if (si.hasSemester5Score)
                        {
                            wst.Cells[wstRIdx, 30].PutValue(si.Semester5Score);
                        }
                    }

                    // 服務表現 31
                    si.CalcCadreScore(CadreName1);
                    wst.Cells[wstRIdx, 31].PutValue(si.ServiceScore);

                    // 品德表現 32
                    if (DemeritRecordDict.ContainsKey(si.StudentID))
                    {
                        if (MeritRecordDict.ContainsKey(si.StudentID))
                        {
                            si.CalcDemeritMemeritScore(DemeritRecordDict[si.StudentID], MeritRecordDict[si.StudentID], DemeritReduceRecord);
                        }
                        else
                        {
                            si.CalcDemeritMemeritScore(DemeritRecordDict[si.StudentID], new List <JHMeritRecord>(), DemeritReduceRecord);
                        }
                        wst.Cells[wstRIdx, 32].PutValue(si.MeritDemeritScore);
                    }
                    else
                    {
                        // 沒有懲戒
                        wst.Cells[wstRIdx, 32].PutValue(10);
                    }


                    // 競賽表現 33
                    si.CalcCompetitionScore();
                    wst.Cells[wstRIdx, 33].PutValue(si.CompetitionScore);

                    // 體適能 34
                    // 計算並填入
                    si.CalcFitnessScore();
                    wst.Cells[wstRIdx, 34].PutValue(si.FitnessScore);

                    // 本土語言認證 35
                    if (hasLanguageCertificateIDList.Contains(si.StudentID))
                    {
                        wst.Cells[wstRIdx, 35].PutValue(2);
                    }
                    else
                    {
                        wst.Cells[wstRIdx, 35].PutValue(0);
                    }



                    // 36~39 系統無法提供先空
                    // 適性發展_高中 36
                    // 適性發展_高職 37
                    // 適性發展_綜合高中 38
                    // 適性發展_五專 39

                    wstRIdx++;
                }

                bgWorkerExport.ReportProgress(100);

                e.Result = wb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Exemple #3
0
        /// <summary>
        /// 取得學生資料
        /// </summary>
        /// <returns></returns>
        public static List <StudDataEntity> GetStudentDataList(List <string> StudentIDList)
        {
            List <StudDataEntity> retVal = new List <StudDataEntity>();

            // 取得地址資料
            Dictionary <string, JHAddressRecord> StudAddressDict = JHAddress.SelectByStudentIDs(StudentIDList).ToDictionary(x => x.RefStudentID);

            // 取得父母監護人
            Dictionary <string, JHParentRecord> StudParentDict = JHParent.SelectByStudentIDs(StudentIDList).ToDictionary(x => x.RefStudentID);

            // 取得電話
            Dictionary <string, JHPhoneRecord> StudPhoneDict = JHPhone.SelectByStudentIDs(StudentIDList).ToDictionary(x => x.RefStudentID);

            foreach (JHStudentRecord stud in JHStudent.SelectByIDs(StudentIDList))
            {
                StudDataEntity sde = new StudDataEntity();
                // 學生系統編號
                sde.StudentID = stud.ID;
                // 學號
                sde.StudentNumber = stud.StudentNumber;
                sde.Add("學號", sde.StudentNumber);
                // 姓名
                sde.Name = stud.Name;
                sde.Add("姓名", sde.Name);
                // 座號
                if (stud.SeatNo.HasValue)
                {
                    sde.SeatNo = stud.SeatNo.Value;
                    sde.Add("座號", string.Format("{0:00}", sde.SeatNo.Value));
                }
                // 班級
                if (stud.Class != null)
                {
                    sde.ClassName = stud.Class.Name;
                    sde.Add("班級", sde.ClassName);
                }
                // 完整戶籍地址
                if (StudAddressDict.ContainsKey(stud.ID))
                {
                    sde.Add("完整戶籍地址", StudAddressDict[stud.ID].PermanentAddress);
                }

                // 完整聯絡地址
                if (StudAddressDict.ContainsKey(stud.ID))
                {
                    sde.Add("完整聯絡地址", StudAddressDict[stud.ID].MailingAddress);
                }

                // 身分證號
                sde.Add("身分證號", stud.IDNumber);

                // 性別
                sde.Add("性別", stud.Gender);

                // 監護人姓名
                if (StudParentDict.ContainsKey(stud.ID))
                {
                    sde.Add("監護人姓名", StudParentDict[stud.ID].CustodianName);
                }

                // 學生戶籍電話
                if (StudPhoneDict.ContainsKey(stud.ID))
                {
                    sde.Add("戶籍電話", StudPhoneDict[stud.ID].Permanent);
                }

                // 學生聯絡電話
                if (StudPhoneDict.ContainsKey(stud.ID))
                {
                    sde.Add("聯絡電話", StudPhoneDict[stud.ID].Contact);
                }

                // 生日
                if (stud.Birthday.HasValue)
                {
                    sde.Add("西元生日(年/月/日)", stud.Birthday.Value.ToShortDateString());

                    string strb1 = (stud.Birthday.Value.Year - 1911) + "/" + string.Format("{0:00}", stud.Birthday.Value.Month) + "/" + string.Format("{0:00}", stud.Birthday.Value.Day);
                    string strb2 = (stud.Birthday.Value.Year - 1911) + string.Format("{0:00}", stud.Birthday.Value.Month) + string.Format("{0:00}", stud.Birthday.Value.Day);

                    sde.Add("民國生日(0年/0月/0日)", strb1);
                    sde.Add("民國生日(0年0月0日)", strb2);
                }

                retVal.Add(sde);
            }
            return(retVal);
        }
Exemple #4
0
        void _bgWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            _bgWorker.ReportProgress(1);
            // 取得所選學生資料
            _StudentDataList = QueryData.GetStudentDataListByStudentIDs(_SelectStudentIDList);

            // 取得領域成績並判斷
            _StudentDataList = Utility.CalcStudDomainScorePass(_StudentDataList, _SchoolYear, _Semester, _passScore);

            _bgWorker.ReportProgress(30);


            // 取得收件人資料,取聯絡、戶籍 
            // 取得父母監護人資訊
            Dictionary <string, JHParentRecord> ParentRecordDict = new Dictionary <string, JHParentRecord>();

            foreach (JHParentRecord rec in JHParent.SelectByStudentIDs(_SelectStudentIDList))
            {
                if (!ParentRecordDict.ContainsKey(rec.RefStudentID))
                {
                    ParentRecordDict.Add(rec.RefStudentID, rec);
                }
            }

            // 取得地址相關資訊
            Dictionary <string, JHAddressRecord> AddressRecordDict = new Dictionary <string, JHAddressRecord>();

            foreach (JHAddressRecord rec in JHAddress.SelectByStudentIDs(_SelectStudentIDList))
            {
                if (!AddressRecordDict.ContainsKey(rec.RefStudentID))
                {
                    AddressRecordDict.Add(rec.RefStudentID, rec);
                }
            }


            Document DocTemp = null;

            // 讀取範本,如果沒有使用預設樣版
            if (_Configure.Template == null)
            {
                _Configure.Template = new Document(new MemoryStream(Properties.Resources.領域補考通知單範本));
                _Configure.Encode();
                _Configure.Save();
            }

            DocTemp = _Configure.Template.Clone();

            List <string> ColumnList = new List <string>();

            ColumnList.Add("學年度");
            ColumnList.Add("學期");
            ColumnList.Add("班級");
            ColumnList.Add("座號");
            ColumnList.Add("姓名");
            ColumnList.Add("學號");
            ColumnList.Add("學校名稱");
            ColumnList.Add("學校電話");
            ColumnList.Add("學校地址");
            ColumnList.Add("收件人地址");
            ColumnList.Add("收件人姓名");
            ColumnList.Add("補考數");
            ColumnList.Add("監護人姓名");
            ColumnList.Add("父親姓名");
            ColumnList.Add("母親姓名");
            ColumnList.Add("戶籍地址");
            ColumnList.Add("聯絡地址");
            ColumnList.Add("其他地址");



            for (int i = 1; i <= 10; i++)
            {
                ColumnList.Add("領域名稱" + i);
                ColumnList.Add("原始成績" + i);
            }


            List <Document> docList = new List <Document>();
            DataTable       dt      = new DataTable();

            // 填入欄位
            foreach (string name in ColumnList)
            {
                dt.Columns.Add(name);
            }

            _bgWorker.ReportProgress(70);

            foreach (StudentData sd in _StudentDataList)
            {
                // 填入資料
                DataRow row = dt.NewRow();
                row["學年度"]  = _SchoolYear;
                row["學期"]   = _Semester;
                row["班級"]   = sd.ClassName;
                row["座號"]   = sd.SeatNo;
                row["姓名"]   = sd.Name;
                row["學號"]   = sd.StudentNumber;
                row["學校名稱"] = K12.Data.School.ChineseName;
                row["學校電話"] = K12.Data.School.Telephone;
                row["學校地址"] = K12.Data.School.Address;

                int ReDomainCount = sd.GetReDomainCount();
                row["補考數"] = ReDomainCount;

                int rDomCOunt = 1;
                if (sd.StudSemesterScoreRecord != null)
                {
                    foreach (K12.Data.DomainScore ds in sd.StudSemesterScoreRecord.Domains.Values)
                    {
                        if (ds.Score.HasValue)
                        {
                            if (ds.Domain != "國語文" && ds.Domain != "英語") // 2017/1/9 穎驊 註解, 高雄不再分別使用 國語文 、英語 領域,而是兩領域合一算一"語文"領域成績 來看有沒有及格。
                            {
                                if (ds.Score.Value < _passScore)
                                {
                                    row["領域名稱" + rDomCOunt] = ds.Domain;
                                    row["原始成績" + rDomCOunt] = "";

                                    if (ds.ScoreOrigin.HasValue)
                                    {
                                        row["原始成績" + rDomCOunt] = ds.ScoreOrigin.Value;
                                    }

                                    rDomCOunt++;
                                }
                            }
                        }
                    }
                }

                if (ParentRecordDict.ContainsKey(sd.StudentID))
                {
                    row["監護人姓名"] = ParentRecordDict[sd.StudentID].CustodianName;
                    row["父親姓名"]  = ParentRecordDict[sd.StudentID].FatherName;
                    row["母親姓名"]  = ParentRecordDict[sd.StudentID].MotherName;

                    if (!string.IsNullOrEmpty(ParentRecordDict[sd.StudentID].CustodianName))
                    {
                        row["收件人姓名"] = ParentRecordDict[sd.StudentID].CustodianName;
                    }
                    else if (!string.IsNullOrEmpty(ParentRecordDict[sd.StudentID].FatherName))
                    {
                        row["收件人姓名"] = ParentRecordDict[sd.StudentID].FatherName;
                    }
                    else
                    {
                        row["收件人姓名"] = ParentRecordDict[sd.StudentID].MotherName;
                    }
                }
                if (AddressRecordDict.ContainsKey(sd.StudentID))
                {
                    row["戶籍地址"] = AddressRecordDict[sd.StudentID].PermanentAddress;
                    row["聯絡地址"] = AddressRecordDict[sd.StudentID].MailingAddress;
                    row["其他地址"] = AddressRecordDict[sd.StudentID].Address1Address;

                    if (!string.IsNullOrEmpty(AddressRecordDict[sd.StudentID].MailingAddress))
                    {
                        row["收件人地址"] = AddressRecordDict[sd.StudentID].MailingAddress;
                    }
                    else if (!string.IsNullOrEmpty(AddressRecordDict[sd.StudentID].PermanentAddress))
                    {
                        row["收件人地址"] = AddressRecordDict[sd.StudentID].PermanentAddress;
                    }
                    else
                    {
                        row["收件人地址"] = AddressRecordDict[sd.StudentID].Address1Address;
                    }
                }

                // 需要補考再出現
                if (ReDomainCount > 0)
                {
                    dt.Rows.Add(row);
                }
            }
            //#region debug
            //dt.TableName = "debug";
            //dt.WriteXml(Application.StartupPath+"\\dttable.xml");
            //StreamWriter sw = new StreamWriter(Application.StartupPath + "\\tempField.txt", false);
            //foreach (string str in DocTemp.MailMerge.GetFieldNames())
            //    sw.WriteLine(str);
            //sw.Flush();
            //sw.Close();

            //#endregion


            if (DocTemp != null)
            {
                // 處理固定欄位對應
                DocTemp.MailMerge.Execute(dt);
                DocTemp.MailMerge.RemoveEmptyParagraphs = true;
                DocTemp.MailMerge.DeleteFields();
            }

            _bgWorker.ReportProgress(90);
            e.Result = DocTemp;
        }
Exemple #5
0
        public static List <StudInfoEntity> GetStudentEntityList(List <string> StudentIDList)
        {
            Dictionary <string, string> StudTagDic  = new Dictionary <string, string>();
            Dictionary <string, string> StudItemDic = new Dictionary <string, string>();

            Global._tempPhomeDict.Clear();

            // 取得對照表
            XmlDocument doc = StudSBTManager.GetDataFormSystem();

            if (doc != null)
            {
                if (doc.SelectSingleNode("Data") != null)
                {
                    foreach (XmlElement xe in doc.SelectSingleNode("Data"))
                    {
                        string StudTags = xe.GetAttribute("StudTag").Trim();
                        StudTags = StudTags.Replace(',', ',');
                        StudTags = StudTags.Replace(':', ':');

                        string[] StudTagsArr = StudTags.Split(',');


                        if (!string.IsNullOrEmpty(StudTags))
                        {
                            foreach (string str in StudTagsArr)
                            {
                                if (!StudTagDic.ContainsKey(str))
                                {
                                    StudTagDic.Add(str, xe.GetAttribute("FieldName") + xe.GetAttribute("ItemName"));
                                }
                            }
                        }

                        if (!string.IsNullOrEmpty(xe.GetAttribute("FieldName")) && !string.IsNullOrEmpty(xe.GetAttribute("ItemName")))
                        {
                            string Item = xe.GetAttribute("FieldName") + xe.GetAttribute("ItemName");
                            if (!StudItemDic.ContainsKey(Item))
                            {
                                StudItemDic.Add(Item, xe.GetAttribute("ItemValue"));
                            }
                        }
                    }
                }
            }

            List <StudInfoEntity> StudInfoEntityList = new List <StudInfoEntity>();

            // 建立相關讀取用到資訊

            Dictionary <string, JHLeaveInfoRecord> LeaveInfoRecordDic  = new Dictionary <string, JHLeaveInfoRecord>();
            Dictionary <string, List <string> >    StudentTagRecordDic = new Dictionary <string, List <string> >();
            Dictionary <string, JHPhoneRecord>     PhoneRecordDic      = new Dictionary <string, JHPhoneRecord>();
            Dictionary <string, JHParentRecord>    ParentRecordDic     = new Dictionary <string, JHParentRecord>();
            Dictionary <string, JHAddressRecord>   AddressRecordDic    = new Dictionary <string, JHAddressRecord>();

            // 畢業資訊
            foreach (JHLeaveInfoRecord lir in JHLeaveIfno.SelectByStudentIDs(StudentIDList))
            {
                if (!LeaveInfoRecordDic.ContainsKey(lir.RefStudentID))
                {
                    LeaveInfoRecordDic.Add(lir.RefStudentID, lir);
                }
            }

            // 學生 Tag
            foreach (JHStudentTagRecord str in JHStudentTag.SelectByStudentIDs(StudentIDList))
            {
                string strS = str.FullName;
                if (StudentTagRecordDic.ContainsKey(str.RefStudentID))
                {
                    StudentTagRecordDic[str.RefStudentID].Add(strS);
                }
                else
                {
                    List <string> strList = new List <string>();
                    strList.Add(strS);
                    StudentTagRecordDic.Add(str.RefStudentID, strList);
                }
            }
            // 電話資訊
            foreach (JHPhoneRecord pr in JHPhone.SelectByStudentIDs(StudentIDList))
            {
                if (!PhoneRecordDic.ContainsKey(pr.RefStudentID))
                {
                    PhoneRecordDic.Add(pr.RefStudentID, pr);
                }
                if (!Global._tempPhomeDict.ContainsKey(pr.RefStudentID))
                {
                    Global._tempPhomeDict.Add(pr.RefStudentID, new Dictionary <string, string>());
                }

                Global._tempPhomeDict[pr.RefStudentID].Add("戶籍電話", pr.Permanent);
                Global._tempPhomeDict[pr.RefStudentID].Add("聯絡電話", pr.Contact);
            }
            // 父母及監護人資訊
            foreach (JHParentRecord pr in JHParent.SelectByStudentIDs(StudentIDList))
            {
                if (!ParentRecordDic.ContainsKey(pr.RefStudentID))
                {
                    ParentRecordDic.Add(pr.RefStudentID, pr);
                }

                if (!Global._tempPhomeDict.ContainsKey(pr.RefStudentID))
                {
                    Global._tempPhomeDict.Add(pr.RefStudentID, new Dictionary <string, string>());
                }

                Global._tempPhomeDict[pr.RefStudentID].Add("父親電話", pr.FatherPhone);
                Global._tempPhomeDict[pr.RefStudentID].Add("母親電話", pr.MotherPhone);
                Global._tempPhomeDict[pr.RefStudentID].Add("監護人電話", pr.CustodianPhone);
            }

            // 地址
            foreach (JHAddressRecord ar in JHAddress.SelectByStudentIDs(StudentIDList))
            {
                if (!AddressRecordDic.ContainsKey(ar.RefStudentID))
                {
                    AddressRecordDic.Add(ar.RefStudentID, ar);
                }
            }

            foreach (JHStudentRecord studRec in JHStudent.SelectByIDs(StudentIDList))
            {
                if (studRec.Status == K12.Data.StudentRecord.StudentStatus.一般 || studRec.Status == K12.Data.StudentRecord.StudentStatus.輟學)
                {
                    StudInfoEntity sie = new StudInfoEntity();
                    // 初始
                    sie.ClassName = sie.SeatNo = string.Empty;

                    if (studRec.Class != null)
                    {
                        sie.ClassName = string.Format("{0:00}", studRec.Class.Name);
                    }

                    sie.Gender   = studRec.Gender;
                    sie.IDNumber = studRec.IDNumber;
                    sie.Name     = studRec.Name;
                    if (studRec.SeatNo.HasValue)
                    {
                        sie.SeatNo = string.Format("{0:00}", studRec.SeatNo.Value);
                    }
                    sie.StudentID     = studRec.ID;
                    sie.StudentNumber = string.Format("{0:00000000}", studRec.StudentNumber);

                    // 填入資料
                    if (!string.IsNullOrEmpty(JHSchool.Data.JHSchoolInfo.Code))
                    {
                        string strSchoolCode = JHSchoolInfo.Code;

                        if (strSchoolCode.Length >= 6)
                        {
                            strSchoolCode = strSchoolCode.Substring(0, 6);
                        }

                        sie.SetDataCellEntity("學校代碼", 6, strSchoolCode);
                        sie.SetDataCellEntity("畢業學校代碼", 6, strSchoolCode);
                    }

                    string strNum;

                    if (sie.StudentNumber.Length >= 8)
                    {
                        strNum = sie.StudentNumber.Substring(0, 8);
                    }
                    else
                    {
                        strNum = sie.StudentNumber;
                        for (int i = 0; i < (8 - sie.StudentNumber.Length); i++)
                        {
                            strNum = "0" + strNum;
                        }
                    }

                    sie.SetDataCellEntity("學號", 8, strNum);
                    if (!string.IsNullOrEmpty(sie.ClassName))
                    {
                        if (sie.ClassName.Length >= 3)
                        {
                            sie.ClassName = sie.ClassName.Substring(1, 2);
                        }

                        if (sie.ClassName.Length >= 2)
                        {
                            sie.SetDataCellEntity("班級", 2, sie.ClassName.Substring(0, 2));
                        }
                        else
                        {
                            sie.SetDataCellEntity("班級", 2, "0" + sie.ClassName);
                        }
                    }

                    if (!string.IsNullOrEmpty(sie.SeatNo))
                    {
                        if (sie.SeatNo.Length > 0)
                        {
                            sie.SetDataCellEntity("座號", 2, sie.SeatNo.Substring(0, 2));
                        }
                        else
                        {
                            sie.SetDataCellEntity("座號", 2, "0" + sie.SeatNo);
                        }
                    }

                    string StudName = sie.Name.Trim();
                    if (StudName.Length == 2)
                    {
                        StudName = StudName.Substring(0, 1) + "  " + StudName.Substring(1, 1);
                    }

                    sie.SetDataCellEntity("學生姓名", 20, StudName);
                    sie.SetDataCellEntity("身分證號", 10, sie.IDNumber);

                    if (studRec.Gender == "男")
                    {
                        sie.SetDataCellEntity("性別", 1, "1");
                    }
                    if (studRec.Gender == "女")
                    {
                        sie.SetDataCellEntity("性別", 1, "2");
                    }

                    if (studRec.Birthday.HasValue)
                    {
                        sie.SetDataCellEntity("出生年", 2, string.Format("{0:00}", studRec.Birthday.Value.Year - 1911));
                        sie.SetDataCellEntity("出生月", 2, string.Format("{0:00}", studRec.Birthday.Value.Month));
                        sie.SetDataCellEntity("出生日", 2, string.Format("{0:00}", studRec.Birthday.Value.Day));
                    }

                    if (LeaveInfoRecordDic.ContainsKey(studRec.ID))
                    {
                        if (LeaveInfoRecordDic[studRec.ID].SchoolYear.HasValue)
                        {
                            sie.SetDataCellEntity("畢業年度", 2, LeaveInfoRecordDic[studRec.ID].SchoolYear.Value + "");
                        }
                        if (LeaveInfoRecordDic[studRec.ID].Reason == "畢業")
                        {
                            sie.SetDataCellEntity("畢肄業", 1, "1");
                        }
                        if (LeaveInfoRecordDic[studRec.ID].Reason == "修業")
                        {
                            sie.SetDataCellEntity("畢肄業", 1, "0");
                        }
                    }



                    if (ParentRecordDic.ContainsKey(studRec.ID))
                    {
                        string ParentName = ParentRecordDic[studRec.ID].CustodianName.Trim();

                        if (ParentName.Length == 2)
                        {
                            ParentName = ParentName.Substring(0, 1) + "  " + ParentName.Substring(1, 1);
                        }
                        sie.SetDataCellEntity("家長姓名", 20, ParentName);


                        //// 戶籍
                        //string strPhone=ParentRecordDic[studRec.ID].CustodianPhone.Replace ("(","");
                        //strPhone = strPhone.Replace(")", "");
                        //strPhone = strPhone.Replace("-", "");
                        string strPhone = "";
                        if (Global._tempPhomeDict.ContainsKey(sie.StudentID))
                        {
                            if (Global._tempPhomeDict[sie.StudentID].ContainsKey(Global.SelectPhoneType))
                            {
                                strPhone = Global._tempPhomeDict[sie.StudentID][Global.SelectPhoneType].Replace("(", "");;
                                strPhone = strPhone.Replace(")", "");
                                strPhone = strPhone.Replace("-", "");
                            }
                        }
                        sie.SetDataCellEntity("緊急連絡電話", 20, strPhone);
                    }

                    if (AddressRecordDic.ContainsKey(studRec.ID))
                    {
                        string strAddress = "";
                        if (_UseMailAddress)
                        {
                            sie.SetDataCellEntity("郵遞區號", 3, AddressRecordDic[studRec.ID].MailingZipCode);
                            strAddress = AddressRecordDic[studRec.ID].MailingCounty + AddressRecordDic[studRec.ID].MailingTown + AddressRecordDic[studRec.ID].MailingDistrict + AddressRecordDic[studRec.ID].MailingArea + AddressRecordDic[studRec.ID].MailingDetail;
                        }
                        else
                        {
                            sie.SetDataCellEntity("郵遞區號", 3, AddressRecordDic[studRec.ID].PermanentZipCode);
                            strAddress = AddressRecordDic[studRec.ID].PermanentCounty + AddressRecordDic[studRec.ID].PermanentTown + AddressRecordDic[studRec.ID].PermanentDistrict + AddressRecordDic[studRec.ID].PermanentArea + AddressRecordDic[studRec.ID].PermanentDetail;
                        }

                        sie.SetDataCellEntity("地址", 80, strAddress);
                    }


                    if (PhoneRecordDic.ContainsKey(studRec.ID))
                    {
                        sie.SetDataCellEntity("手機", 10, PhoneRecordDic[studRec.ID].Cell);
                    }

                    // default value
                    sie.SetDataCellEntity("學生身分", 1, "0");
                    sie.SetDataCellEntity("身心障礙", 1, "0");
                    sie.SetDataCellEntity("中低收入戶", 1, "0");
                    sie.SetDataCellEntity("低收入戶", 1, "0");
                    sie.SetDataCellEntity("失業勞工子女", 1, "0");

                    if (StudentTagRecordDic.ContainsKey(studRec.ID))
                    {
                        foreach (string str in StudentTagRecordDic[studRec.ID])
                        {
                            if (StudTagDic.ContainsKey(str))
                            {
                                if (StudItemDic.ContainsKey(StudTagDic[str]))
                                {
                                    if (StudTagDic[str].IndexOf("學生身分") > -1)
                                    {
                                        sie.SetDataCellEntity("學生身分", 1, StudItemDic[StudTagDic[str]]);
                                    }

                                    if (StudTagDic[str].IndexOf("身心障礙") > -1)
                                    {
                                        sie.SetDataCellEntity("身心障礙", 1, StudItemDic[StudTagDic[str]]);
                                    }

                                    if (StudTagDic[str].IndexOf("低收入戶") == 0)
                                    {
                                        sie.SetDataCellEntity("低收入戶", 1, StudItemDic[StudTagDic[str]]);
                                    }

                                    //if (StudTagDic[str].IndexOf("中低收入戶") > -1)
                                    //    sie.SetDataCellEntity("中低收入戶", 1, StudItemDic[StudTagDic[str]]);

                                    if (StudTagDic[str].IndexOf("失業勞工子女") > -1)
                                    {
                                        sie.SetDataCellEntity("失業勞工子女", 1, StudItemDic[StudTagDic[str]]);
                                    }
                                }
                            }
                        }
                    }
                    StudInfoEntityList.Add(sie);
                }
            }

            return(StudInfoEntityList);
        }
Exemple #6
0
        /// <summary>
        /// 未達畢業標準通知單
        /// </summary>
        private void ExportDoc()
        {
            if (_students.Count == 0)
            {
                return;
            }
            _doc.Sections.Clear();

            if (_rc.Template == null)
            {
                _rc.Template = new ReportTemplate(Properties.Resources.未達畢業標準通知單樣板, TemplateType.Word);
            }

            _template = _rc.Template.ToDocument();

            string UserSelAddresseeAddress = _rc.GetString(PrintConfigForm.setupAddresseeAddress, "聯絡地址");
            string UserSelAddresseeName    = _rc.GetString(PrintConfigForm.setupAddresseeName, "監護人");

            _UserSelExportStudentList = _rc.GetBoolean(PrintConfigForm.setupExportStudentList, false);

            string   UserSeldtDate = "";
            DateTime dt;

            if (DateTime.TryParse(_rc.GetString(PrintConfigForm.setupdtDocDate, ""), out dt))
            {
                UserSeldtDate = ConvertDate1(dt);
            }
            else
            {
                UserSeldtDate = ConvertDate1(DateTime.Now);
            }

            List <StudentGraduationPredictData> StudentGraduationPredictDataList = new List <StudentGraduationPredictData>();
            // 取得學生ID,製作 Dict 用
            List <string> StudIDList = (from data in _students select data.ID).ToList();

            // Student Address,Key:StudentID
            Dictionary <string, JHAddressRecord> AddressDict = new Dictionary <string, JHAddressRecord>();
            // Student Parent,Key:StudentID
            Dictionary <string, JHParentRecord> ParentDict = new Dictionary <string, JHParentRecord>();

            // 地址
            foreach (JHAddressRecord rec in JHAddress.SelectByStudentIDs(StudIDList))
            {
                if (!AddressDict.ContainsKey(rec.RefStudentID))
                {
                    AddressDict.Add(rec.RefStudentID, rec);
                }
            }

            // 父母監護人
            foreach (JHParentRecord rec in JHParent.SelectByStudentIDs(StudIDList))
            {
                if (!ParentDict.ContainsKey(rec.RefStudentID))
                {
                    ParentDict.Add(rec.RefStudentID, rec);
                }
            }



            // 資料轉換 ..
            foreach (StudentRecord StudRec in _students)
            {
                if (!_result.ContainsKey(StudRec.ID))
                {
                    continue;
                }

                //處理年級為0的資料
                List <ResultDetail> zeroGrades = new List <ResultDetail>();

                StudentGraduationPredictData sgpd = new StudentGraduationPredictData();

                if (StudRec.Class != null)
                {
                    sgpd.ClassName = StudRec.Class.Name;
                }

                sgpd.Name = StudRec.Name;

                sgpd.SchoolAddress = K12.Data.School.Address;
                sgpd.SchoolName    = K12.Data.School.ChineseName;
                sgpd.SchoolPhone   = K12.Data.School.Telephone;

                sgpd.SeatNo        = StudRec.SeatNo;
                sgpd.StudentNumber = StudRec.StudentNumber;

                // 文字
                if (_result.ContainsKey(StudRec.ID))
                {
                    int GrYear;
                    foreach (ResultDetail rd in _result[StudRec.ID])
                    {
                        if (int.TryParse(rd.GradeYear, out GrYear))
                        {
                            //if (GrYear == 0) continue;
                            //後續處理
                            if (GrYear == 0)
                            {
                                zeroGrades.Add(rd);
                            }

                            // 組訊息
                            string Detail = "";
                            if (rd.Details.Count > 0)
                            {
                                Detail = string.Join(",", rd.Details.ToArray());
                            }

                            // 一年級
                            if (GrYear == 1 || GrYear == 7)
                            {
                                if (rd.Semester.Trim() == "1")
                                {
                                    sgpd.Text11 = Detail;
                                }

                                if (rd.Semester.Trim() == "2")
                                {
                                    sgpd.Text12 = Detail;
                                }
                            }

                            // 二年級
                            if (GrYear == 2 || GrYear == 8)
                            {
                                if (rd.Semester.Trim() == "1")
                                {
                                    sgpd.Text21 = Detail;
                                }

                                if (rd.Semester.Trim() == "2")
                                {
                                    sgpd.Text22 = Detail;
                                }
                            }

                            // 三年級
                            if (GrYear == 3 || GrYear == 9)
                            {
                                if (rd.Semester.Trim() == "1")
                                {
                                    sgpd.Text31 = Detail;
                                }

                                if (rd.Semester.Trim() == "2")
                                {
                                    sgpd.Text32 = Detail;
                                }
                            }
                        }
                    }
                }

                // 地址
                if (AddressDict.ContainsKey(StudRec.ID))
                {
                    if (UserSelAddresseeAddress == "聯絡地址")
                    {
                        sgpd.AddresseeAddress = AddressDict[StudRec.ID].MailingAddress;
                    }

                    if (UserSelAddresseeAddress == "戶籍地址")
                    {
                        sgpd.AddresseeAddress = AddressDict[StudRec.ID].PermanentAddress;
                    }
                }

                // 父母監護人
                if (ParentDict.ContainsKey(StudRec.ID))
                {
                    if (UserSelAddresseeName == "父親")
                    {
                        sgpd.AddresseeName = ParentDict[StudRec.ID].FatherName;
                    }

                    if (UserSelAddresseeName == "母親")
                    {
                        sgpd.AddresseeName = ParentDict[StudRec.ID].MotherName;
                    }

                    if (UserSelAddresseeName == "監護人")
                    {
                        sgpd.AddresseeName = ParentDict[StudRec.ID].CustodianName;
                    }
                }

                sgpd.DocDate = UserSeldtDate;

                foreach (ResultDetail rd in zeroGrades)
                {
                    sgpd.Text += string.Join(",", rd.Details);
                }

                StudentGraduationPredictDataList.Add(sgpd);
            }

            // 產生Word 套印
            Dictionary <string, object> FieldData = new Dictionary <string, object>();

            // 班座排序
            StudentGraduationPredictDataList = (from data in StudentGraduationPredictDataList orderby data.ClassName, data.SeatNo.PadLeft(3, '0') ascending select data).ToList();

            foreach (StudentGraduationPredictData sgpd in StudentGraduationPredictDataList)
            {
                FieldData.Clear();
                FieldData.Add("學校名稱", sgpd.SchoolName);
                FieldData.Add("學校電話", sgpd.SchoolPhone);
                FieldData.Add("學校地址", sgpd.SchoolAddress);
                FieldData.Add("收件人地址", sgpd.AddresseeAddress);
                FieldData.Add("收件人姓名", sgpd.AddresseeName);
                FieldData.Add("班級", sgpd.ClassName);
                FieldData.Add("座號", sgpd.SeatNo);
                FieldData.Add("姓名", sgpd.Name);
                FieldData.Add("學號", sgpd.StudentNumber);
                FieldData.Add("一上文字", sgpd.Text11);
                FieldData.Add("一下文字", sgpd.Text12);
                FieldData.Add("二上文字", sgpd.Text21);
                FieldData.Add("二下文字", sgpd.Text22);
                FieldData.Add("三上文字", sgpd.Text31);
                FieldData.Add("三下文字", sgpd.Text32);
                FieldData.Add("發文日期", sgpd.DocDate);
                FieldData.Add("所有說明", sgpd.Text);

                Aspose.Words.Document        each    = (Aspose.Words.Document)_template.Clone(true);
                Aspose.Words.DocumentBuilder builder = new Aspose.Words.DocumentBuilder(each);
                // 合併
                if (FieldData.Count > 0)
                {
                    builder.Document.MailMerge.Execute(FieldData.Keys.ToArray(), FieldData.Values.ToArray());
                }

                foreach (Aspose.Words.Section sec in each.Sections)
                {
                    _doc.Sections.Add(_doc.ImportNode(sec, true));
                }
            }

            // 產生學生清單
            if (_UserSelExportStudentList)
            {
                _wbStudentList = new Workbook();
                _wbStudentList.Worksheets[0].Cells[0, 0].PutValue("班級");
                _wbStudentList.Worksheets[0].Cells[0, 1].PutValue("座號");
                _wbStudentList.Worksheets[0].Cells[0, 2].PutValue("學號");
                _wbStudentList.Worksheets[0].Cells[0, 3].PutValue("學生姓名");
                _wbStudentList.Worksheets[0].Cells[0, 4].PutValue("收件人姓名");
                _wbStudentList.Worksheets[0].Cells[0, 5].PutValue("地址");
                //班級	座號	學號	學生姓名	收件人姓名	地址

                int rowIdx = 1;
                foreach (StudentGraduationPredictData sgpd in StudentGraduationPredictDataList)
                {
                    _wbStudentList.Worksheets[0].Cells[rowIdx, 0].PutValue(sgpd.ClassName);
                    _wbStudentList.Worksheets[0].Cells[rowIdx, 1].PutValue(sgpd.SeatNo);
                    _wbStudentList.Worksheets[0].Cells[rowIdx, 2].PutValue(sgpd.StudentNumber);
                    _wbStudentList.Worksheets[0].Cells[rowIdx, 3].PutValue(sgpd.Name);
                    _wbStudentList.Worksheets[0].Cells[rowIdx, 4].PutValue(sgpd.AddresseeName);
                    _wbStudentList.Worksheets[0].Cells[rowIdx, 5].PutValue(sgpd.AddresseeAddress);
                    rowIdx++;
                }

                _wbStudentList.Worksheets[0].AutoFitColumns();
            }
        }
Exemple #7
0
        void wizard_ImportPackage(object sender, SmartSchool.API.PlugIn.Import.ImportPackageEventArgs e)
        {
            // 尋找主要Key來判斷,如果有學生系統編號先用系統編號,沒有使用學號,
            Dictionary <string, RowData> RowDataDict          = new Dictionary <string, RowData>();
            Dictionary <string, int>     chkSidDict           = new Dictionary <string, int>();
            Dictionary <string, string>  chkSnumDict          = new Dictionary <string, string>();
            List <JHStudentRecord>       InsertStudentRecList = new List <JHStudentRecord>();
            List <JHStudentRecord>       StudentRecAllList    = JHStudent.SelectAll();

            foreach (JHStudentRecord rec in StudentRecAllList)
            {
                chkSidDict.Add(rec.ID, 0);
                string key = rec.StudentNumber + rec.StatusStr;
                if (!chkSnumDict.ContainsKey(key))
                {
                    chkSnumDict.Add(key, rec.ID);
                }
            }

            // 先處理學生基本資料
            foreach (RowData Row in e.Items)
            {
                string StudentID = "";

                if (Row.ContainsKey("學生系統編號"))
                {
                    string id = Row["學生系統編號"].ToString();
                    if (chkSidDict.ContainsKey(id))
                    {
                        StudentID = id;
                    }
                }

                if (StudentID == "")
                {
                    string ssNum = "", snum = "";
                    if (Row.ContainsKey("學號"))
                    {
                        snum = Row["學號"].ToString();
                        string status = "一般";
                        if (Row.ContainsKey("狀態"))
                        {
                            if (Row["狀態"].ToString() != "")
                            {
                                status = Row["狀態"].ToString();
                            }
                        }
                        ssNum = snum + status;
                    }

                    if (chkSnumDict.ContainsKey(ssNum))
                    {
                        StudentID = chkSnumDict[ssNum];
                    }
                    else
                    {
                        // 新增
                        if (chkSnumDict.ContainsKey(snum + "一般"))
                        {
                            // 有重複
                        }
                        else
                        {
                            // 批次新增
                            JHStudentRecord newRec = new JHStudentRecord();
                            newRec.StudentNumber = snum;
                            newRec.Status        = StudentRecord.StudentStatus.一般;
                            if (Row.ContainsKey("姓名"))
                            {
                                newRec.Name = Row["姓名"].ToString();
                            }

                            InsertStudentRecList.Add(newRec);
                        }
                    }
                }
            }

            // 如有新學生處理新增學生
            if (InsertStudentRecList.Count > 0)
            {
                JHStudent.Insert(InsertStudentRecList);
            }

            // 再次建立索引
            Dictionary <string, JHStudentRecord> StudRecAllDict = new Dictionary <string, JHStudentRecord>();

            StudentRecAllList = JHStudent.SelectAll();
            chkSidDict.Clear();
            chkSnumDict.Clear();
            foreach (JHStudentRecord rec in StudentRecAllList)
            {
                chkSidDict.Add(rec.ID, 0);
                string key = rec.StudentNumber + rec.StatusStr;
                if (!chkSnumDict.ContainsKey(key))
                {
                    chkSnumDict.Add(key, rec.ID);
                }

                StudRecAllDict.Add(rec.ID, rec);
            }

            List <string> StudentIDList = new List <string>();

            //比對
            foreach (RowData Row in e.Items)
            {
                string StudentID = "";

                if (Row.ContainsKey("學生系統編號"))
                {
                    string id = Row["學生系統編號"].ToString();
                    if (chkSidDict.ContainsKey(id))
                    {
                        StudentID = id;
                    }
                }

                if (StudentID == "")
                {
                    string ssNum = "", snum = "";
                    if (Row.ContainsKey("學號"))
                    {
                        snum = Row["學號"].ToString();
                        string status = "一般";
                        if (Row.ContainsKey("狀態"))
                        {
                            if (Row["狀態"].ToString() != "")
                            {
                                status = Row["狀態"].ToString();
                            }
                        }
                        ssNum = snum + status;
                    }

                    if (chkSnumDict.ContainsKey(ssNum))
                    {
                        StudentID = chkSnumDict[ssNum];
                    }
                }

                if (!string.IsNullOrEmpty(StudentID))
                {
                    if (!RowDataDict.ContainsKey(StudentID))
                    {
                        RowDataDict.Add(StudentID, Row);
                    }

                    StudentIDList.Add(StudentID);
                }
            }
            // 取得學生基本、父母監護人、電話、地址、UDT
            List <JHStudentRecord> StudentRecordList = JHStudent.SelectByIDs(StudentIDList);
            Dictionary <string, JHStudentRecord> StudentRecordDict = new Dictionary <string, JHStudentRecord>();

            foreach (JHStudentRecord rec in StudentRecordList)
            {
                if (!StudentRecordDict.ContainsKey(rec.ID))
                {
                    StudentRecordDict.Add(rec.ID, rec);
                }
            }

            List <JHParentRecord> ParentRecordList = JHParent.SelectByStudentIDs(StudentIDList);
            Dictionary <string, JHParentRecord> ParentRecordDict = new Dictionary <string, JHParentRecord>();

            foreach (JHParentRecord rec in ParentRecordList)
            {
                if (!ParentRecordDict.ContainsKey(rec.RefStudentID))
                {
                    ParentRecordDict.Add(rec.RefStudentID, rec);
                }
            }

            List <JHPhoneRecord> PhoneRecordList = JHPhone.SelectByStudentIDs(StudentIDList);
            Dictionary <string, JHPhoneRecord> PhoneRecordDict = new Dictionary <string, JHPhoneRecord>();

            foreach (JHPhoneRecord rec in PhoneRecordList)
            {
                if (!PhoneRecordDict.ContainsKey(rec.RefStudentID))
                {
                    PhoneRecordDict.Add(rec.RefStudentID, rec);
                }
            }

            List <JHAddressRecord> AddressRecordList = JHAddress.SelectByStudentIDs(StudentIDList);
            Dictionary <string, JHAddressRecord> AddressRecordDict = new Dictionary <string, JHAddressRecord>();

            foreach (JHAddressRecord rec in AddressRecordList)
            {
                if (!AddressRecordDict.ContainsKey(rec.RefStudentID))
                {
                    AddressRecordDict.Add(rec.RefStudentID, rec);
                }
            }

            AccessHelper             accHepler             = new AccessHelper();
            string                   qry                   = "ref_student_id in('" + string.Join("','", StudentIDList.ToArray()) + "')";
            List <StudentRecord_Ext> StudentRecord_ExtList = accHepler.Select <StudentRecord_Ext>(qry);
            Dictionary <string, StudentRecord_Ext> StudentRecord_ExtDict = new Dictionary <string, StudentRecord_Ext> ();

            foreach (StudentRecord_Ext rec in StudentRecord_ExtList)
            {
                if (!StudentRecord_ExtDict.ContainsKey(rec.RefStudentID))
                {
                    StudentRecord_ExtDict.Add(rec.RefStudentID, rec);
                }
            }

            // 開始處理
            List <JHStudentRecord>      updateStudentRecList        = new List <JHStudentRecord>();
            List <JHParentRecord>       updateParentRecList         = new List <JHParentRecord>();
            List <JHPhoneRecord>        updatePhoneList             = new List <JHPhoneRecord>();
            List <JHAddressRecord>      updateAddressList           = new List <JHAddressRecord>();
            List <StudentRecord_Ext>    insertStudentRecord_ExtList = new List <StudentRecord_Ext>();
            List <StudentRecord_Ext>    updateStudentRecord_ExtList = new List <StudentRecord_Ext>();
            List <JHClassRecord>        classRecAllList             = JHClass.SelectAll();
            Dictionary <string, string> classNameIDDict             = new Dictionary <string, string>();

            foreach (JHClassRecord rec in classRecAllList)
            {
                classNameIDDict.Add(rec.Name, rec.ID);
            }

            StringBuilder sbLog = new StringBuilder();

            foreach (string StudentID in RowDataDict.Keys)
            {
                RowData rd = RowDataDict[StudentID];

                if (StudentRecordDict.ContainsKey(StudentID))
                {
                    string tt = "學生系統編號:" + StudentID + ",學號:" + StudentRecordDict[StudentID].StudentNumber;
                    if (StudentRecordDict[StudentID].Class != null)
                    {
                        tt += "班級:" + StudentRecordDict[StudentID].Class.Name;
                    }

                    if (StudentRecordDict[StudentID].SeatNo.HasValue)
                    {
                        tt += "座號:" + StudentRecordDict[StudentID].SeatNo.Value;
                    }

                    sbLog.AppendLine(tt);
                    if (rd.ContainsKey("學號"))
                    {
                        string value = rd["學號"].ToString();
                        if (StudentRecordDict[StudentID].StudentNumber != value)
                        {
                            sbLog.AppendLine(string.Format("學號由「{0}」改為「{1}」", StudentRecordDict[StudentID].StudentNumber, value));
                        }
                        StudentRecordDict[StudentID].StudentNumber = value;
                    }
                    if (rd.ContainsKey("班級"))
                    {
                        string value    = rd["班級"].ToString();
                        string oldValue = "";

                        if (StudentRecordDict[StudentID].Class != null)
                        {
                            oldValue = StudentRecordDict[StudentID].Class.Name;
                        }

                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("班級由「{0}」改為「{1}」", oldValue, value));
                        }

                        if (classNameIDDict.ContainsKey(value))
                        {
                            StudentRecordDict[StudentID].RefClassID = classNameIDDict[value];
                        }
                    }
                    if (rd.ContainsKey("座號"))
                    {
                        string value    = rd["座號"].ToString();
                        string oldValue = "";
                        if (StudentRecordDict[StudentID].SeatNo.HasValue)
                        {
                            oldValue = StudentRecordDict[StudentID].SeatNo.Value.ToString();
                        }

                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("座號由「{0}」改為「{1}」", oldValue, value));
                        }

                        StudentRecordDict[StudentID].SeatNo = null;
                        int i;
                        if (int.TryParse(value, out i))
                        {
                            StudentRecordDict[StudentID].SeatNo = i;
                        }
                    }
                    if (rd.ContainsKey("姓名"))
                    {
                        string value = rd["姓名"].ToString();
                        if (StudentRecordDict[StudentID].Name != value)
                        {
                            sbLog.AppendLine(string.Format("姓名由「{0}」改為「{1}」", StudentRecordDict[StudentID].Name, value));
                        }

                        StudentRecordDict[StudentID].Name = value;
                    }
                    if (rd.ContainsKey("身分證號"))
                    {
                        string value = rd["身分證號"].ToString();
                        if (StudentRecordDict[StudentID].IDNumber != value)
                        {
                            sbLog.AppendLine(string.Format("身分證號由「{0}」改為「{1}」", StudentRecordDict[StudentID].IDNumber, value));
                        }

                        StudentRecordDict[StudentID].IDNumber = value;
                    }
                    if (rd.ContainsKey("國籍"))
                    {
                        string value = rd["國籍"].ToString();
                        if (StudentRecordDict[StudentID].Nationality != value)
                        {
                            sbLog.AppendLine(string.Format("國籍由「{0}」改為「{1}」", StudentRecordDict[StudentID].Nationality, value));
                        }

                        StudentRecordDict[StudentID].Nationality = value;
                    }
                    if (rd.ContainsKey("出生地"))
                    {
                        string value = rd["出生地"].ToString();
                        if (StudentRecordDict[StudentID].BirthPlace != value)
                        {
                            sbLog.AppendLine(string.Format("出生地由「{0}」改為「{1}」", StudentRecordDict[StudentID].BirthPlace, value));
                        }

                        StudentRecordDict[StudentID].BirthPlace = value;
                    }
                    if (rd.ContainsKey("生日"))
                    {
                        string value    = rd["生日"].ToString();
                        string oldValue = "";
                        if (StudentRecordDict[StudentID].Birthday.HasValue)
                        {
                            oldValue = StudentRecordDict[StudentID].Birthday.Value.ToShortDateString();
                        }

                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("生日由「{0}」改為「{1}」", oldValue, value));
                        }

                        StudentRecordDict[StudentID].Birthday = null;
                        DateTime dt;
                        if (DateTime.TryParse(value, out dt))
                        {
                            StudentRecordDict[StudentID].Birthday = dt;
                        }
                    }
                    if (rd.ContainsKey("性別"))
                    {
                        string value = rd["性別"].ToString();
                        if (StudentRecordDict[StudentID].Gender != value)
                        {
                            sbLog.AppendLine(string.Format("性別由「{0}」改為「{1}」", StudentRecordDict[StudentID].Gender, value));
                        }
                        StudentRecordDict[StudentID].Gender = value;
                    }
                    if (rd.ContainsKey("英文姓名"))
                    {
                        string value = rd["英文姓名"].ToString();
                        if (StudentRecordDict[StudentID].EnglishName != value)
                        {
                            sbLog.AppendLine(string.Format("英文姓名由「{0}」改為「{1}」", StudentRecordDict[StudentID].EnglishName, value));
                        }
                        StudentRecordDict[StudentID].EnglishName = value;
                    }
                    if (rd.ContainsKey("登入帳號"))
                    {
                        string value = rd["登入帳號"].ToString();
                        if (StudentRecordDict[StudentID].SALoginName != value)
                        {
                            sbLog.AppendLine(string.Format("登入帳號由「{0}」改為「{1}」", StudentRecordDict[StudentID].SALoginName, value));
                        }
                        StudentRecordDict[StudentID].SALoginName = value;
                    }
                    if (rd.ContainsKey("狀態"))
                    {
                        string value    = rd["狀態"].ToString();
                        string oldValue = StudentRecordDict[StudentID].StatusStr;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("狀態由「{0}」改為「{1}」", oldValue, value));
                        }

                        switch (value)
                        {
                        case "":
                        case "一般": StudentRecordDict[StudentID].Status = StudentRecord.StudentStatus.一般; break;

                        case "畢業或離校": StudentRecordDict[StudentID].Status = StudentRecord.StudentStatus.畢業或離校; break;

                        case "休學": StudentRecordDict[StudentID].Status = StudentRecord.StudentStatus.休學; break;

                        case "輟學": StudentRecordDict[StudentID].Status = StudentRecord.StudentStatus.輟學; break;

                        case "刪除": StudentRecordDict[StudentID].Status = StudentRecord.StudentStatus.刪除; break;
                        }
                    }
                }
                // 和班級走
                //if (rd.ContainsKey("年級") && StudentRecordDict.ContainsKey(StudentID)) { string value = rd["年級"].ToString(); }

                StudentRecord_Ext exRec = null;

                bool isNewExRec = false;
                if (StudentRecord_ExtDict.ContainsKey(StudentID))
                {
                    exRec = StudentRecord_ExtDict[StudentID];
                }
                else
                {
                    exRec = new StudentRecord_Ext();
                    exRec.RefStudentID = StudentID;
                    isNewExRec         = true;
                }

                if (rd.ContainsKey("英文別名"))
                {
                    string value = rd["英文別名"].ToString();
                    if (exRec.Nickname != value)
                    {
                        sbLog.AppendLine(string.Format("英文別名由「{0}」改為「{1}」", exRec.Nickname, value));
                    }
                    exRec.Nickname = value;
                }
                if (rd.ContainsKey("居留證號"))
                {
                    string value = rd["居留證號"].ToString();
                    if (exRec.PassportNumber != value)
                    {
                        sbLog.AppendLine(string.Format("居留證號由「{0}」改為「{1}」", exRec.PassportNumber, value));
                    }
                    exRec.PassportNumber = value;
                }
                if (rd.ContainsKey("GivenName"))
                {
                    string value = rd["GivenName"].ToString();
                    if (exRec.GivenName != value)
                    {
                        sbLog.AppendLine(string.Format("GivenName由「{0}」改為「{1}」", exRec.GivenName, value));
                    }

                    exRec.GivenName = value;
                }
                if (rd.ContainsKey("MiddleName"))
                {
                    string value = rd["MiddleName"].ToString();
                    if (exRec.MiddleName != value)
                    {
                        sbLog.AppendLine(string.Format("MiddleName由「{0}」改為「{1}」", exRec.MiddleName, value));
                    }

                    exRec.MiddleName = value;
                }
                if (rd.ContainsKey("FamilyName"))
                {
                    string value = rd["FamilyName"].ToString();
                    if (exRec.FamilyName != value)
                    {
                        sbLog.AppendLine(string.Format("FamilyName由「{0}」改為「{1}」", exRec.FamilyName, value));
                    }

                    exRec.FamilyName = value;
                }
                if (rd.ContainsKey("入學日期"))
                {
                    string value    = rd["入學日期"].ToString();
                    string oldValue = "";
                    if (exRec.EntranceDate.HasValue)
                    {
                        oldValue = exRec.EntranceDate.Value.ToShortDateString();
                    }
                    exRec.EntranceDate = null;
                    if (oldValue != value)
                    {
                        sbLog.AppendLine(string.Format("入學日期由「{0}」改為「{1}」", oldValue, value));
                    }

                    DateTime dt1;
                    if (DateTime.TryParse(value, out dt1))
                    {
                        exRec.EntranceDate = dt1;
                    }
                }
                if (rd.ContainsKey("畢業日期"))
                {
                    string value    = rd["畢業日期"].ToString();
                    string oldValue = "";
                    if (exRec.LeavingDate.HasValue)
                    {
                        oldValue = exRec.LeavingDate.Value.ToShortDateString();
                    }

                    if (oldValue != value)
                    {
                        sbLog.AppendLine(string.Format("畢業日期由「{0}」改為「{1}」", oldValue, value));
                    }

                    exRec.LeavingDate = null;
                    DateTime dt2;
                    if (DateTime.TryParse(value, out dt2))
                    {
                        exRec.LeavingDate = dt2;
                    }
                }


                if (isNewExRec)
                {
                    insertStudentRecord_ExtList.Add(exRec);
                }
                else
                {
                    updateStudentRecord_ExtList.Add(exRec);
                }

                if (ParentRecordDict.ContainsKey(StudentID))
                {
                    if (rd.ContainsKey("父親姓名"))
                    {
                        string value    = rd["父親姓名"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Father.Name;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("父親姓名由「{0}」改為「{1}」", oldValue, value));
                        }
                        ParentRecordDict[StudentID].Father.Name = value;
                    }
                    if (rd.ContainsKey("父親學歷"))
                    {
                        string value    = rd["父親學歷"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Father.EducationDegree;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("父親學歷由「{0}」改為「{1}」", oldValue, value));
                        }
                        ParentRecordDict[StudentID].Father.EducationDegree = value;
                    }
                    if (rd.ContainsKey("父親職業"))
                    {
                        string value    = rd["父親職業"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Father.Job;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("父親職業由「{0}」改為「{1}」", oldValue, value));
                        }
                        ParentRecordDict[StudentID].Father.Job = value;
                    }
                    if (rd.ContainsKey("父親電話"))
                    {
                        string value    = rd["父親電話"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Father.Phone;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("父親電話由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Father.Phone = value;
                    }

                    if (rd.ContainsKey("父親電子郵件"))
                    {
                        string value    = rd["父親電子郵件"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Father.EMail;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("父親電子郵件由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Father.EMail = value;
                    }


                    if (rd.ContainsKey("母親姓名"))
                    {
                        string value    = rd["母親姓名"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Mother.Name;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("母親姓名由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Mother.Name = value;
                    }
                    if (rd.ContainsKey("母親學歷"))
                    {
                        string value    = rd["母親學歷"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Mother.EducationDegree;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("母親學歷由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Mother.EducationDegree = value;
                    }
                    if (rd.ContainsKey("母親職業"))
                    {
                        string value    = rd["母親職業"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Mother.Job;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("母親職業由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Mother.Job = value;
                    }
                    if (rd.ContainsKey("母親電話"))
                    {
                        string value    = rd["母親電話"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Mother.Phone;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("母親電話由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Mother.Phone = value;
                    }

                    if (rd.ContainsKey("母親電子郵件"))
                    {
                        string value    = rd["母親電子郵件"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Mother.EMail;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("母親電子郵件由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Mother.EMail = value;
                    }


                    if (rd.ContainsKey("監護人姓名"))
                    {
                        string value    = rd["監護人姓名"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Custodian.Name;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("監護人姓名由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Custodian.Name = value;
                    }
                    if (rd.ContainsKey("監護人電話"))
                    {
                        string value    = rd["監護人電話"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Custodian.Phone;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("監護人電話由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Custodian.Phone = value;
                    }

                    if (rd.ContainsKey("監護人電子郵件"))
                    {
                        string value    = rd["監護人電子郵件"].ToString();
                        string oldValue = ParentRecordDict[StudentID].Custodian.EMail;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("監護人電子郵件由「{0}」改為「{1}」", oldValue, value));
                        }

                        ParentRecordDict[StudentID].Custodian.EMail = value;
                    }


                    updateParentRecList.Add(ParentRecordDict[StudentID]);
                }

                if (PhoneRecordDict.ContainsKey(StudentID))
                {
                    if (rd.ContainsKey("戶籍電話"))
                    {
                        string value    = rd["戶籍電話"].ToString();
                        string oldValue = PhoneRecordDict[StudentID].Permanent;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("戶籍電話由「{0}」改為「{1}」", oldValue, value));
                        }
                        PhoneRecordDict[StudentID].Permanent = value;
                    }
                    if (rd.ContainsKey("聯絡電話"))
                    {
                        string value    = rd["聯絡電話"].ToString();
                        string oldValue = PhoneRecordDict[StudentID].Contact;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("聯絡電話由「{0}」改為「{1}」", oldValue, value));
                        }

                        PhoneRecordDict[StudentID].Contact = value;
                    }
                    if (rd.ContainsKey("行動電話"))
                    {
                        string value    = rd["行動電話"].ToString();
                        string oldValue = PhoneRecordDict[StudentID].Cell;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("行動電話由「{0}」改為「{1}」", oldValue, value));
                        }

                        PhoneRecordDict[StudentID].Cell = value;
                    }
                    if (rd.ContainsKey("其它電話1"))
                    {
                        string value    = rd["其它電話1"].ToString();
                        string oldValue = PhoneRecordDict[StudentID].Phone1;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("其它電話1由「{0}」改為「{1}」", oldValue, value));
                        }

                        PhoneRecordDict[StudentID].Phone1 = value;
                    }
                    updatePhoneList.Add(PhoneRecordDict[StudentID]);
                }

                if (AddressRecordDict.ContainsKey(StudentID))
                {
                    if (rd.ContainsKey("戶籍:郵遞區號"))
                    {
                        string value    = rd["戶籍:郵遞區號"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Permanent.ZipCode;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("戶籍:郵遞區號由「{0}」改為「{1}」", oldValue, value));
                        }
                        AddressRecordDict[StudentID].Permanent.ZipCode = value;
                    }
                    if (rd.ContainsKey("戶籍:縣市"))
                    {
                        string value    = rd["戶籍:縣市"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Permanent.County;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("戶籍:縣市由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Permanent.County = value;
                    }
                    if (rd.ContainsKey("戶籍:鄉鎮市區"))
                    {
                        string value    = rd["戶籍:鄉鎮市區"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Permanent.Town;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("戶籍:鄉鎮市區由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Permanent.Town = value;
                    }
                    if (rd.ContainsKey("戶籍:村里"))
                    {
                        string value    = rd["戶籍:村里"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Permanent.District;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("戶籍:村里由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Permanent.District = value;
                    }
                    if (rd.ContainsKey("戶籍:鄰"))
                    {
                        string value    = rd["戶籍:鄰"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Permanent.Area;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("戶籍:鄰由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Permanent.Area = value;
                    }
                    if (rd.ContainsKey("戶籍:其他"))
                    {
                        string value    = rd["戶籍:其他"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Permanent.Detail;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("戶籍:其他由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Permanent.Detail = value;
                    }
                    if (rd.ContainsKey("聯絡:郵遞區號"))
                    {
                        string value    = rd["聯絡:郵遞區號"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Mailing.ZipCode;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("聯絡:郵遞區號由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Mailing.ZipCode = value;
                    }
                    if (rd.ContainsKey("聯絡:縣市"))
                    {
                        string value    = rd["聯絡:縣市"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Mailing.County;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("聯絡:縣市由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Mailing.County = value;
                    }
                    if (rd.ContainsKey("聯絡:鄉鎮市區"))
                    {
                        string value    = rd["聯絡:鄉鎮市區"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Mailing.Town;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("聯絡:鄉鎮市區由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Mailing.Town = value;
                    }
                    if (rd.ContainsKey("聯絡:村里"))
                    {
                        string value    = rd["聯絡:村里"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Mailing.District;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("聯絡:村里由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Mailing.District = value;
                    }
                    if (rd.ContainsKey("聯絡:鄰"))
                    {
                        string value    = rd["聯絡:鄰"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Mailing.Area;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("聯絡:鄰由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Mailing.Area = value;
                    }
                    if (rd.ContainsKey("聯絡:其他"))
                    {
                        string value    = rd["聯絡:其他"].ToString();
                        string oldValue = AddressRecordDict[StudentID].Mailing.Detail;
                        if (oldValue != value)
                        {
                            sbLog.AppendLine(string.Format("聯絡:其他由「{0}」改為「{1}」", oldValue, value));
                        }

                        AddressRecordDict[StudentID].Mailing.Detail = value;
                    }
                    sbLog.AppendLine();
                    updateAddressList.Add(AddressRecordDict[StudentID]);
                }
            }

            // 處理學生資料
            foreach (string key in StudentRecordDict.Keys)
            {
                updateStudentRecList.Add(StudentRecordDict[key]);
            }

            if (updateStudentRecList.Count > 0)
            {
                JHStudent.Update(updateStudentRecList);
            }

            if (updateParentRecList.Count > 0)
            {
                JHParent.Update(updateParentRecList);
            }

            if (updatePhoneList.Count > 0)
            {
                JHPhone.Update(updatePhoneList);
            }

            if (updateAddressList.Count > 0)
            {
                JHAddress.Update(updateAddressList);
            }

            if (insertStudentRecord_ExtList.Count > 0)
            {
                insertStudentRecord_ExtList.SaveAll();
            }

            if (updateStudentRecord_ExtList.Count > 0)
            {
                updateStudentRecord_ExtList.SaveAll();
            }

            FISCA.LogAgent.ApplicationLog.Log("匯入學生基本資料(New)", "匯入", sbLog.ToString());

            // 同步
            JHSchool.Student.Instance.SyncAllBackground();
            JHSchool.Data.JHStudent.RemoveAll();
            JHSchool.Data.JHStudent.SelectAll();
        }