JW/ServiceLogic/EMIS.CommonLogic/ScoreManage/SpecialityScoreSumServices.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.ScoreManage;
using System.Data;
using EMIS.Utility;
using EMIS.ViewModel;
using System.Dynamic;
using EMIS.ViewModel.ScoreManage;
using System.Linq.Expressions;
using EMIS.Entities;
using Bowin.Common.Linq;
using EMIS.DataLogic.Common.Students;
using EMIS.ViewModel.Students;
using EMIS.ViewModel.CacheManage;
using Bowin.Common.Utility;
using System.Text.RegularExpressions;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;

namespace EMIS.CommonLogic.ScoreManage
{
    public class SpecialityScoreSumServices : BaseServices, ISpecialityScoreSumServices
    {
        public SpecialityScoreSumDAL specialityScoreSumDAL { get; set; }
        public StudentsDAL StudentsDAL { get; set; }

        public SpecialityScoreSummaryView SpecialityScoreSumTable(Guid? collegeID, int? years, int? standardID, int? schoolyearNumID, int? schoolcodeID, Guid? classmajorID, int? inSchoolStatus)
        {
            Expression<Func<CF_Classmajor, bool>> classmajorExp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            Expression<Func<CF_Student, bool>> studentExp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            Expression<Func<EM_EducationMissionClass, bool>> missionClassExp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            SpecialityScoreSummaryView summaryView = new SpecialityScoreSummaryView();

            if (collegeID.HasValue)
            {
                classmajorExp = classmajorExp.And(x => x.CF_Grademajor.CF_Facultymajor.CollegeID == collegeID);
                studentExp = studentExp.And(x => x.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID == collegeID);
            }
            if (years.HasValue)
            {
                classmajorExp = classmajorExp.And(x => x.CF_Grademajor.GradeID == years);
                studentExp = studentExp.And(x => x.CF_Classmajor.CF_Grademajor.GradeID == years);
            }
            if (standardID.HasValue)
            {
                classmajorExp = classmajorExp.And(x => x.CF_Grademajor.CF_Facultymajor.StandardID == standardID);
                studentExp = studentExp.And(x => x.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID == standardID);
            }
            if (classmajorID.HasValue)
            {
                classmajorExp = classmajorExp.And(x => x.ClassmajorID == classmajorID);
                studentExp = studentExp.And(x => x.ClassmajorID == classmajorID);
            }

            if (inSchoolStatus.HasValue)
            {
                studentExp = studentExp.And(x => x.InSchoolStatusID == inSchoolStatus);
            }

            var courseQuery = specialityScoreSumDAL.GetCoursematerialPlanViewQueryable(classmajorExp, missionClassExp);

            if (schoolyearNumID.HasValue)
            {
                courseQuery = courseQuery.Where(x => x.SchoolyearNumID == schoolyearNumID);
            }
            if (schoolcodeID.HasValue)
            {
                courseQuery = courseQuery.Where(x => x.SchoolcodeID == schoolcodeID);
            }

            courseQuery = courseQuery.OrderBy(x => x.StartTermID).ThenBy(x => x.CourseName);

            //先获取当前院系所、学年、专业、学年数、学期
            var queryCoursematerialPlan = courseQuery.ToList();
            var queryFinallyScore = specialityScoreSumDAL.GetStudentScoreViewQueryable(collegeID, years, schoolyearNumID, schoolcodeID, classmajorID).ToList();
            var studentList = StudentsDAL.GetBaseStudentViewQueryable(studentExp).ToList();

            summaryView.StudentList = studentList.OrderBy(x => x.LoginID).ToList();
            summaryView.SchoolyearNums = new List<SpecialityScoreSummaryYearView>();
            //开始建立树形结构，顶层是学年数，从大到小排序
            var schoolyearNums = queryCoursematerialPlan.Select(x => x.SchoolyearNumID).Distinct().OrderBy(x => x).ToList();
            foreach (var schoolyearNum in schoolyearNums)
            {
                SpecialityScoreSummaryYearView schoolyearNumView = new SpecialityScoreSummaryYearView();

                schoolyearNumView.Title = IdNameExt.GetName(DictionaryItem.CF_SchoolyearNum, schoolyearNum);
                schoolyearNumView.SchoolyearNum = schoolyearNum;
                schoolyearNumView.CoursematerialNum = 0;
                //第二层，学期，按开课学期排序，不是按学期ID
                schoolyearNumView.SchoolCodeView = new List<SpecialityScoreSummarySchoolcodeView>();
                var schoolcodeList = queryCoursematerialPlan.Where(x => x.SchoolyearNumID == schoolyearNum)
                                    .GroupBy(x => new { x.StartTermID, x.SchoolcodeID })
                                    .Select(x => x.Key).OrderBy(x => x.StartTermID).ToList();
                foreach (var schoolCode in schoolcodeList)
                {
                    SpecialityScoreSummarySchoolcodeView schoolCodeView = new SpecialityScoreSummarySchoolcodeView();

                    schoolCodeView.Title = IdNameExt.GetName(DictionaryItem.CF_Semester, schoolCode.SchoolcodeID);
                    schoolCodeView.SchoolCodeID = schoolCode.SchoolcodeID;
                    schoolCodeView.StartTermID = schoolCode.StartTermID;
                    //第三层，课程，按名称排序
                    schoolCodeView.CoursematerialList = new List<SpecialityScoreSummaryCoursematerialView>();
                    var coursematerialList = queryCoursematerialPlan.Where(x => x.SchoolyearNumID == schoolyearNum
                                        && x.SchoolcodeID == schoolCode.SchoolcodeID)
                                    .GroupBy(x => new { x.CoursematerialID, x.CourseName })
                                    .Select(x => x.Key).OrderBy(x => x.CourseName).ToList();
                    foreach (var coursematerial in coursematerialList)
                    {
                        SpecialityScoreSummaryCoursematerialView coursematerialView = new SpecialityScoreSummaryCoursematerialView();

                        coursematerialView.Title = coursematerial.CourseName;
                        coursematerialView.CoursematerialID = coursematerial.CoursematerialID;

                        //该课程所有学生均未录入成绩时，不汇总到专业成绩里
                        var scoreCount = queryFinallyScore.Where(x => x.SchoolyearNumID == schoolyearNum
                                             && x.StarttermID == schoolCode.StartTermID && x.CoursematerialID == coursematerialView.CoursematerialID).ToList().Count();
                        if (scoreCount == 0)
                            continue;
                        //最后一层，成绩，按学生学号排序
                        var scoreList = (from s in studentList
                                         join fs in queryFinallyScore.Where(x => x.SchoolyearNumID == schoolyearNum
                                             && x.StarttermID == schoolCode.StartTermID && x.CoursematerialID == coursematerialView.CoursematerialID)
                                             on s.UserID equals fs.UserID into dfs
                                         from efs in dfs.DefaultIfEmpty()
                                         orderby s.LoginID
                                         select new SpecialityScoreSummaryScoreView
                                         {
                                             UserID = s.UserID,
                                             Score =  (efs == null ) ?null:efs.TotalScoreStr,
                                             //Score = (efs == null ) ? (int?)null : Convert.ToInt32(Math.Round((Convert.ToDecimal(efs.TotalScore)), 0))
                                         }).ToList();
                        coursematerialView.ScoreList = scoreList;

                        schoolCodeView.CoursematerialList.Add(coursematerialView);
                        schoolyearNumView.CoursematerialNum += 1;
                    }

                    //schoolyearNumView.SchoolCodeView.Add(schoolCodeView);
                    //因为某课程所有学生均未录入成绩时，不汇总到专业成绩里，如果某学期和学年数所有课程没有录入成绩时，不显示一二层学期和学年标题
                    if (schoolCodeView.CoursematerialList.Count > 0)
                        schoolyearNumView.SchoolCodeView.Add(schoolCodeView);
                }

                //summaryView.SchoolyearNums.Add(schoolyearNumView);
                //因为某课程所有学生均未录入成绩时，不汇总到专业成绩里，如果某学期和学年数所有课程没有录入成绩时，不显示一二层学期和学年标题
                if (schoolyearNumView.SchoolCodeView.Count > 0)
                    summaryView.SchoolyearNums.Add(schoolyearNumView);
            }

            return summaryView;
        }

        /// <summary>
        /// 获取标题
        /// </summary>
        /// <param name="collegeID"></param>
        /// <param name="years"></param>
        /// <param name="specialtyID"></param>
        /// <param name="schoolyearNumID"></param>
        /// <param name="schoolcodeID"></param>
        /// <param name="classmajorID"></param>
        /// <returns></returns>
        public List<string> SpecialityScoreSumTableTitle(Guid? collegeID, int? years, Guid? specialtyID, int? schoolyearNumID, int? schoolcodeID, Guid? classmajorID)
        {
            List<string> list = new List<string>();
            //var dynamicTypes = DictionaryHelper.GetDictionaryValue(DictionaryItem.CF_SchoolyearNum);
            //if (schoolyearNumID.HasValue)
            //    dynamicTypes = dynamicTypes.Where(x => x.Value == schoolyearNumID);
            ////先获取当前院系所、学年、专业、学年数、学期
            //var queryCoursematerialPlan = specialityScoreSumDAL.GetCoursematerialPlanViewQueryable(collegeID, years, specialtyID, schoolyearNumID, schoolcodeID).ToList();
            //list.Add("次序");
            //list.Add("学号");
            //list.Add("姓名");
            ////按学年数来分课程列
            //foreach (var dynamicType in dynamicTypes)
            //{
            //    var queryCoursematerial = queryCoursematerialPlan.Where(x => x.SchoolyearNumID == dynamicType.Value).ToList();
            //    foreach (var coursematerial in queryCoursematerial)
            //    {
            //        list.Add(coursematerial.CourseName);
            //    }
            //}
            return list;
        }


        public List<CoursematerialPlanView> DynamicTypesCount(Guid? collegeID, int? years, Guid? specialtyID, int? schoolyearNumID, int? schoolcodeID, Guid? classmajorID)
        {
            //先获取当前院系所、学年、专业、学年数、学期
            //var queryCoursematerialPlan = specialityScoreSumDAL.GetCoursematerialPlanViewQueryable(collegeID, years, specialtyID, schoolyearNumID, schoolcodeID).ToList();
            //return queryCoursematerialPlan;
            return new List<CoursematerialPlanView>();
        }

        /// <summary>
        /// 获取学年学期
        /// </summary>
        /// <param name="collegeID"></param>
        /// <param name="years"></param>
        /// <param name="specialtyID"></param>
        /// <param name="schoolyearNumID"></param>
        /// <param name="schoolcodeID"></param>
        /// <param name="classmajorID"></param>
        /// <returns></returns>
        public List<EMIS.Entities.CF_Schoolyear> ScoreSchoolyear(Guid? collegeID, int? years, Guid? specialtyID, int? schoolyearNumID, int? schoolcodeID, Guid? classmajorID)
        {
            var querySchoolyear = specialityScoreSumDAL.GetFinallyScoreQueryable(collegeID, years, schoolyearNumID, schoolcodeID, classmajorID).OrderBy(x => x.CF_Schoolyear.Code).Select(x => x.CF_Schoolyear).Distinct().ToList();
            return querySchoolyear;
        }


        public void ExportSpecialityScoreSumTable(Guid? collegeID, int? years, int? standardID, int? schoolyearNumID, int? schoolcodeID, Guid? classmajorID, int? inSchoolStatus)
        {
            string html = "";
            NpoiExcelHelper neh = new NpoiExcelHelper();
            var specialtyScoreSummaryView = this.SpecialityScoreSumTable(collegeID, years, standardID, schoolyearNumID, schoolcodeID, classmajorID, inSchoolStatus);
            Func<IWorkbook, HSSFCellStyle> noPassStyleFunc = (x =>
            {
                var style = (HSSFCellStyle)x.CreateCellStyle();

                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                style.Alignment = HorizontalAlignment.CENTER;
                style.VerticalAlignment = VerticalAlignment.CENTER;
                var font = (HSSFFont)x.CreateFont();
                font.FontHeightInPoints = 12;
                font.Boldweight = (short)FontBoldWeight.BOLD;
                font.Color = HSSFColor.RED.index;

                style.SetFont(font);
                return style;
            });
            List<NpoiExcelCellStyle> styleList = new List<NpoiExcelCellStyle>();

            var dtResult = new DataTable();
            if (specialtyScoreSummaryView != null)
            {
                var detail = new List<string>();
                var firstRow = new List<string>();
                var secondRow = new List<string>();
                var thirdRow = new List<string>();
                var forthRow = new List<List<string>>();
                var detailRow = new List<string>();

                //生成第一、二、三行表头，学年和学期和课程
                //第一行开头
                firstRow.Add("<tr>");
                firstRow.Add("<td rowspan=\"2\" colspan=\"3\" row=\"0\" col=\"0\">学期</td>");
                //第二行开头
                secondRow.Add("<tr>");
                //第三行开头
                thirdRow.Add("<tr>");
                thirdRow.Add("<td rowspan=\"0\" colspan=\"0\" row=\"2\" col=\"0\">次序</td>");
                thirdRow.Add("<td rowspan=\"0\" colspan=\"0\" row=\"2\" col=\"1\">学号</td>");
                thirdRow.Add("<td rowspan=\"0\" colspan=\"0\" row=\"2\" col=\"2\">姓名</td>");

                for (var i = 0; i < specialtyScoreSummaryView.StudentList.Count; i++)
                {
                    var student = specialtyScoreSummaryView.StudentList[i];
                    var studentColumns = new List<string>();
                    studentColumns.Add("<tr>");
                    studentColumns.Add("<td rowspan=\"0\" colspan=\"0\" row=\"" + (5 + i).ToString() + "\" col=\"0\">" + (i + 1).ToString() + "</td>");
                    studentColumns.Add("<td rowspan=\"0\" colspan=\"0\" row=\"" + (5 + i).ToString() + "\" col=\"1\">" + student.LoginID + "</td>");
                    studentColumns.Add("<td rowspan=\"0\" colspan=\"0\" row=\"" + (5 + i).ToString() + "\" col=\"2\">" + student.UserName + "</td>");
                    forthRow.Add(studentColumns);
                }
                //前三行明细
                var index = 3;
                for (var i = 0; i < specialtyScoreSummaryView.SchoolyearNums.Count; i++)
                {
                    var schoolyearNum = specialtyScoreSummaryView.SchoolyearNums[i];
                    firstRow.Add("<td rowspan=\"1\" colspan=\"" + schoolyearNum.CoursematerialNum + "\" row=\"0\" col=\"" + index.ToString() + "\">" + schoolyearNum.Title + "</td>");
                    for (var j = 0; j < schoolyearNum.SchoolCodeView.Count; j++)
                    {
                        var schoolcode = schoolyearNum.SchoolCodeView[j];
                        secondRow.Add("<td rowspan=\"1\" colspan=\"" + schoolcode.CoursematerialList.Count + "\" row=\"1\" col=\"" + index.ToString() + "\">" + schoolcode.Title + "</td>");
                        for (var k = 0; k < schoolcode.CoursematerialList.Count; k++)
                        {
                            var coursematerial = schoolcode.CoursematerialList[k];
                            thirdRow.Add("<td rowspan=\"0\" colspan=\"0\" row=\"2\" col=\"" + index.ToString() + "\">" + coursematerial.Title + "</td>");
                            for (var l = 0; l < coursematerial.ScoreList.Count; l++)
                            {
                                var score = coursematerial.ScoreList[l];
                                if (score.Score != "" && score.Score != null)
                                {
                                    decimal tmpScore;
                                    if (decimal.TryParse(score.Score, out tmpScore))
                                    {
                                        if (tmpScore < 60)
                                        {
                                            styleList.Add(new NpoiExcelCellStyle { ColumnIndex = index, RowIndex = l + 3, StyleFunc = noPassStyleFunc });
                                        }
                                    }
                                    else
                                    {
                                        styleList.Add(new NpoiExcelCellStyle { ColumnIndex = index, RowIndex = l + 3, StyleFunc = noPassStyleFunc });
                                    }
                                    forthRow[l].Add("<td rowspan=\"0\" colspan=\"0\" row=\"" + (l + 3).ToString() + "\" col=\"" + index.ToString() + "\">" + score.Score.ToString() + "</td>");
                                }
                                else
                                {
                                    forthRow[l].Add("<td rowspan=\"0\" colspan=\"0\" row=\"" + (l + 3).ToString() + "\" col=\"" + index.ToString() + "\"></td>");
                                }
                            }
                            index++;
                        }
                    }
                }
                //第一行结尾
                firstRow.Add("</tr>");
                //第二行结尾
                secondRow.Add("</tr>");
                //第三行结尾
                thirdRow.Add("</tr>");
                html += string.Join("", firstRow);
                html += string.Join("", secondRow);
                html += string.Join("", thirdRow);
                for (var i = 0; i < specialtyScoreSummaryView.StudentList.Count; i++)
                {
                    html += string.Join("", forthRow[i]);
                    html += "</tr>";
                }
            }
            neh.PresetCellStyle(styleList);
            neh.ExportTable(html, "专业成绩汇总");
        }
    }
}