using Aspose.Cells;
using EMIS.CommonLogic.UniversityManage.SpecialtyClassManage;
using EMIS.DataLogic.Repositories;
using EMIS.DataLogic.UniversityManage.SpecialtyClassManage;
using EMIS.Utility;
using EMIS.ViewModel.Report;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace EMIS.CommonLogic.Report
{
    public class NewReportServices: BaseServices, INewReportServices
    {
        public ClassmajorDAL ClassmajorDAL { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public MemoryStream TeacherselfSchedulingPDF(Guid SchoolyearID, Guid? UserID)
        {
            ReportHelper rh = new ReportHelper();

            MemoryStream ms = new MemoryStream();

            string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
            string sql2 = rh.GetReportSql("EducationScheduling/TeacherselfSchedulingView.sql");

            List<SqlParameter> sParams = new List<SqlParameter>();

            sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));

            if (UserID.HasValue)
            {
                sParams.Add(new SqlParameter("@UserID", UserID));
            }
            else
            {
                sParams.Add(new SqlParameter("@UserID", DBNull.Value));
            }


            var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

            var wk = rh.GetReportTemple("EducationScheduling/TeacherselfSchedulingView.xlsx");

            var ws = wk.Worksheets[0];

            string title = "{0}{1}学期课程表-{2}", code = "", staffName = "";

            if (ds.Tables[1].Rows.Count > 0)
            {
                code = ds.Tables[1].Rows[0]["Code"].ToString();
                staffName = ds.Tables[1].Rows[0]["StaffName"].ToString();
            }

            ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, staffName);

            //聚合唯一
            //ds.Tables[1].DefaultView.Sort = "StartTimes asc";
            var dtShow = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times");

            dtShow.DefaultView.Sort = "StartTimes asc";

            dtShow = dtShow.DefaultView.ToTable();

            int lineEnd = 2;

            for (int i = 0; i < dtShow.Rows.Count; i++)
            {
                string col1Format = "{0}\n{1}";

                ws.Cells[lineEnd, 0].PutValue(string.Format(col1Format, dtShow.Rows[i]["TimeDesc"], dtShow.Rows[i]["Times"] == DBNull.Value ? "" : "<" + dtShow.Rows[i]["Times"].ToString() + ">"));

                string condition = "StartTimes='{0}'";

                var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i]["StartTimes"]), "MinWeekNum asc");

                int lineMax = 0;

                for (int j = 0; j < weekRows.Count(); j++)
                {
                    int weekDay = (int)weekRows[j]["Weekday"];
                    //weekDay += 1;
                    var curCell = GetLastCell(ws, weekDay, 0, lineEnd, ref lineMax);

                    try
                    {
                        StringBuilder sbCell = new StringBuilder();

                        sbCell.AppendLine(weekRows[j]["Name"].ToString());
                        if (weekRows[j]["StudentCount"] != DBNull.Value)
                        {
                            sbCell.Append("(" + weekRows[j]["StudentCount"].ToString() + "人)\n");
                        }

                        sbCell.AppendLine(weekRows[j]["ClassroomName"].ToString());

                        if (weekRows[j]["WeekNums"] != DBNull.Value)
                        {
                            string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                            sbCell.AppendFormat("第{0}周\n", weekNum);

                        }

                        if (weekRows[j]["TeacherName"] != DBNull.Value)
                        {
                            sbCell.AppendFormat("<{0}>\n", weekRows[j]["TeacherName"]);
                        }

                        curCell.Value += sbCell.ToString();

                        if (weekRows[j]["StopWeekNumStr"] != DBNull.Value)
                        {
                            string stopStr = rh.SetNumberMerge(weekRows[j]["StopWeekNumStr"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                            curCell.HtmlString += string.Format("<Br><Font Style=\"COLOR: #FF0000;\">({0}停课)</Font>", stopStr);
                        }

                        //curCell.PutValue(sbCell.ToString());
                    }
                    catch
                    {

                    }
                }

                var mergeRange = ws.Cells.CreateRange(lineEnd, 0, lineMax + 1, 1);

                mergeRange.Merge();

                var style1 = rh.BuildBorderStyle(wk);
                StyleFlag stFlag1 = new StyleFlag();
                stFlag1.Borders = true;

                mergeRange.ApplyStyle(style1, stFlag1);

                var weekRange = ws.Cells.CreateRange(lineEnd, 1, lineMax + 1, 7);

                StyleFlag stFlag2 = new StyleFlag();
                stFlag2.LeftBorder = true;
                stFlag2.RightBorder = true;

                weekRange.ApplyStyle(style1, stFlag2);

                StyleFlag stFlag3 = new StyleFlag();
                stFlag3.BottomBorder = true;

                ws.Cells.CreateRange(lineEnd + lineMax, 1, 1, 7).ApplyStyle(style1, stFlag3);

                lineEnd = lineEnd + lineMax + 1;


            }

            ws.AutoFitRows();

            wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

            return ms;

        }
        public MemoryStream StudentForTeacherSchedulingPDF(Guid SchoolyearID, Guid StudentNo)
        {
            ReportHelper rh = new ReportHelper();

            MemoryStream ms = new MemoryStream();

            string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
            string sql2 = rh.GetReportSql("EducationScheduling/StudentForTeacherSchedulingView.sql");

            List<SqlParameter> sParams = new List<SqlParameter>();

            sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
            sParams.Add(new SqlParameter("@StudentNo", StudentNo));


            var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

            var wk = rh.GetReportTemple("EducationScheduling/StudentForTeacherSchedulingView.xlsx");

            var ws = wk.Worksheets[0];

            string title = "{0}{1}学期课程表-{2}", code = "", classMajorName = "";

            if (ds.Tables[1].Rows.Count > 0)
            {
                code = ds.Tables[1].Rows[0]["Code"].ToString();
                classMajorName = ds.Tables[1].Rows[0]["ClassmajorName"].ToString();
            }

            ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, classMajorName);

            //聚合唯一
            //ds.Tables[1].DefaultView.Sort = "StartTimes asc";
            var dtShow = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times");

            dtShow.DefaultView.Sort = "StartTimes asc";

            dtShow = dtShow.DefaultView.ToTable();

            int lineEnd = 2;

            for (int i = 0; i < dtShow.Rows.Count; i++)
            {
                string col1Format = "{0}\n{1}";

                ws.Cells[lineEnd, 0].PutValue(string.Format(col1Format, dtShow.Rows[i]["TimeDesc"], dtShow.Rows[i]["Times"] == DBNull.Value ? "" : "<" + dtShow.Rows[i]["Times"].ToString() + ">"));

                string condition = "StartTimes='{0}'";

                var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i]["StartTimes"]), "MinWeekNum asc");

                int lineMax = 0;

                for (int j = 0; j < weekRows.Count(); j++)
                {
                    int weekDay = (int)weekRows[j]["Weekday"];
                    //weekDay += 1;
                    var curCell = GetLastCell(ws, weekDay, 0, lineEnd, ref lineMax);

                    try
                    {
                        StringBuilder sbCell = new StringBuilder();

                        sbCell.AppendLine(weekRows[j]["Name"].ToString());

                        if (weekRows[j]["ClassroomName"] != DBNull.Value)
                        {
                            sbCell.Append(weekRows[j]["ClassroomName"].ToString() + "\n");
                        }

                        if (weekRows[j]["WeekNums"] != DBNull.Value)
                        {
                            string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                            sbCell.AppendFormat("第{0}周\n", weekNum);

                        }

                        if (weekRows[j]["TeacherName"] != DBNull.Value)
                        {
                            sbCell.AppendFormat("<{0}>\n", weekRows[j]["TeacherName"]);
                        }



                        curCell.Value += sbCell.ToString();

                        if (weekRows[j]["StopWeekNumStr"] != DBNull.Value)
                        {
                            string stopStr = rh.SetNumberMerge(weekRows[j]["StopWeekNumStr"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                            curCell.HtmlString += string.Format("<Br><Font Style=\"COLOR: #FF0000;\">({0}停课)</Font>", stopStr);
                        }

                        //curCell.PutValue(sbCell.ToString());
                    }
                    catch
                    {

                    }
                }

                var mergeRange = ws.Cells.CreateRange(lineEnd, 0, lineMax + 1, 1);

                mergeRange.Merge();

                var style1 = rh.BuildBorderStyle(wk);
                StyleFlag stFlag1 = new StyleFlag();
                stFlag1.Borders = true;

                mergeRange.ApplyStyle(style1, stFlag1);

                var weekRange = ws.Cells.CreateRange(lineEnd, 1, lineMax + 1, 7);

                StyleFlag stFlag2 = new StyleFlag();
                stFlag2.LeftBorder = true;
                stFlag2.RightBorder = true;

                weekRange.ApplyStyle(style1, stFlag2);

                StyleFlag stFlag3 = new StyleFlag();
                stFlag3.BottomBorder = true;

                ws.Cells.CreateRange(lineEnd + lineMax, 1, 1, 7).ApplyStyle(style1, stFlag3);

                lineEnd = lineEnd + lineMax + 1;


            }

            ws.AutoFitRows();

            wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

            return ms;
        }

        public MemoryStream TeacherSchedulingPDF(Guid SchoolyearID, Guid? CollegeID, Guid? UserID)
        {
            ReportHelper rh = new ReportHelper();

            MemoryStream ms = new MemoryStream();

            string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
            string sql2 = rh.GetReportSql("EducationScheduling/TeacherSchedulingView.sql");

            List<SqlParameter> sParams = new List<SqlParameter>();

            sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
            if (CollegeID.HasValue)
            {
                sParams.Add(new SqlParameter("@CollegeID", CollegeID));
            }
            else
            {
                sParams.Add(new SqlParameter("@CollegeID", DBNull.Value));
            }

            if (UserID.HasValue)
            {
                sParams.Add(new SqlParameter("@UserID", UserID));
            }
            else
            {
                sParams.Add(new SqlParameter("@UserID", DBNull.Value));
            }

            var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

            var wk = rh.GetReportTemple("EducationScheduling/TeacherSchedulingView.xlsx");

            var ws = wk.Worksheets[0];

            string title = "{0}{1}学期课程表-{2}", code = "", staffName = "";

            if (ds.Tables[1].Rows.Count > 0)
            {
                code = ds.Tables[1].Rows[0]["Code"].ToString();
                staffName = ds.Tables[1].Rows[0]["StaffName"].ToString();
            }

            ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, staffName);

            //聚合唯一
            //ds.Tables[1].DefaultView.Sort = "StartTimes asc";
            var dtShow = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times");

            dtShow.DefaultView.Sort = "StartTimes asc";

            dtShow = dtShow.DefaultView.ToTable();

            int lineEnd = 2;

            for (int i = 0; i < dtShow.Rows.Count; i++)
            {
                string col1Format = "{0}\n{1}";

                ws.Cells[lineEnd, 0].PutValue(string.Format(col1Format, dtShow.Rows[i]["TimeDesc"], dtShow.Rows[i]["Times"] == DBNull.Value ? "" : "<" + dtShow.Rows[i]["Times"].ToString() + ">"));

                string condition = "StartTimes='{0}'";

                var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i]["StartTimes"]), "StartTimes asc");

                int lineMax = 0;

                for (int j = 0; j < weekRows.Count(); j++)
                {
                    int weekDay = (int)weekRows[j]["Weekday"];
                    //weekDay += 1;
                    var curCell = GetLastCell(ws, weekDay, 0, lineEnd, ref lineMax);

                    try
                    {
                        StringBuilder sbCell = new StringBuilder();

                        sbCell.AppendLine(weekRows[j]["Name"].ToString());

                        sbCell.Append(weekRows[j]["StudentCount"] == DBNull.Value ? "" : "(" + weekRows[j]["StudentCount"].ToString() + "人)\n");

                        if (weekRows[j]["ClassroomName"] != DBNull.Value)
                        {
                            sbCell.Append(weekRows[j]["ClassroomName"].ToString() + "\n");
                        }

                        if (weekRows[j]["WeekNums"] != DBNull.Value)
                        {
                            string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                            sbCell.AppendFormat("第{0}周\n", weekNum);

                        }

                        if (weekRows[j]["TeacherName"] != DBNull.Value)
                        {
                            sbCell.AppendFormat("<{0}>\n", weekRows[j]["TeacherName"]);
                        }



                        curCell.Value += sbCell.ToString();

                        if (weekRows[j]["StopWeekNumStr"] != DBNull.Value)
                        {
                            string stopStr = rh.SetNumberMerge(weekRows[j]["StopWeekNumStr"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                            curCell.HtmlString += string.Format("<Br><Font Style=\"COLOR: #FF0000;\">({0}停课)</Font>", stopStr);
                        }

                        //curCell.PutValue(sbCell.ToString());
                    }
                    catch
                    {

                    }
                }

                var mergeRange = ws.Cells.CreateRange(lineEnd, 0, lineMax + 1, 1);

                mergeRange.Merge();

                var style1 = rh.BuildBorderStyle(wk);
                StyleFlag stFlag1 = new StyleFlag();
                stFlag1.Borders = true;

                mergeRange.ApplyStyle(style1, stFlag1);

                var weekRange = ws.Cells.CreateRange(lineEnd, 1, lineMax + 1, 7);

                StyleFlag stFlag2 = new StyleFlag();
                stFlag2.LeftBorder = true;
                stFlag2.RightBorder = true;

                weekRange.ApplyStyle(style1, stFlag2);

                StyleFlag stFlag3 = new StyleFlag();
                stFlag3.BottomBorder = true;

                ws.Cells.CreateRange(lineEnd + lineMax, 1, 1, 7).ApplyStyle(style1, stFlag3);

                lineEnd = lineEnd + lineMax + 1;


            }

            ws.AutoFitRows();

            wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

            return ms;
        }
        public MemoryStream TeacherPrintingSchedulingPDF(Guid SchoolyearID, Guid? CollegeID)
        {
            try
            {
                ReportHelper rh = new ReportHelper();

                MemoryStream ms = new MemoryStream();

                string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
                string sql2 = rh.GetReportSql("EducationScheduling/TeacherSchedulingView.sql");
                var wk = rh.GetReportTemple("EducationScheduling/TeacherSchedulingView.xlsx");

                var templateSheetCount = wk.Worksheets.Count;
                List<SqlParameter> teacherParams = new List<SqlParameter>();

                teacherParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
                if (CollegeID.HasValue)
                {
                    teacherParams.Add(new SqlParameter("@CollegeID", CollegeID));
                }
                else
                {
                    teacherParams.Add(new SqlParameter("@CollegeID", DBNull.Value));
                }
                teacherParams.Add(new SqlParameter("@UserID", DBNull.Value));
                var teacherds = this.UnitOfWork.QuerySQL(sql2, teacherParams);
                var tsReportViewList = DateTableHelper.DataTableToList<TeacherSchedulingView>(teacherds.Tables[0]);
                List<Guid?> teacherIDList = tsReportViewList.Select(x => x.UserID).Distinct().ToList();
                for (int i = templateSheetCount; i < teacherIDList.Count; i += templateSheetCount)
                {
                    for (int index = 0; index < templateSheetCount; index++)
                    {
                        wk.Worksheets.AddCopy(index); //.CopyTo(wk, "Sheet" + (i + 1).ToString(), true, true);
                    }
                }

                for (int m = 0; m < teacherIDList.Count(); m++)
                {
                    var userID = teacherIDList[m];
                    List<SqlParameter> sParams = new List<SqlParameter>();

                    sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
                    if (CollegeID.HasValue)
                    {
                        sParams.Add(new SqlParameter("@CollegeID", CollegeID));
                    }
                    else
                    {
                        sParams.Add(new SqlParameter("@CollegeID", DBNull.Value));
                    }
                    sParams.Add(new SqlParameter("@UserID", userID));
                    //if (UserID.HasValue)
                    //{
                    //    sParams.Add(new SqlParameter("@UserID", UserID));
                    //}
                    //else
                    //{
                    //    sParams.Add(new SqlParameter("@UserID", DBNull.Value));
                    //}

                    var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

                    var ws = wk.Worksheets[m];

                    string title = "{0}{1}学期课程表-{2}", code = "", staffName = "";

                    if (ds.Tables[1].Rows.Count > 0)
                    {
                        code = ds.Tables[1].Rows[0]["Code"].ToString();
                        staffName = ds.Tables[1].Rows[0]["StaffName"].ToString();
                    }

                    ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, staffName);

                    //聚合唯一
                    //ds.Tables[1].DefaultView.Sort = "StartTimes asc";
                    var dtShow = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times");

                    dtShow.DefaultView.Sort = "StartTimes asc";

                    dtShow = dtShow.DefaultView.ToTable();

                    int lineEnd = 2;

                    for (int i = 0; i < dtShow.Rows.Count; i++)
                    {
                        string col1Format = "{0}\n{1}";

                        ws.Cells[lineEnd, 0].PutValue(string.Format(col1Format, dtShow.Rows[i]["TimeDesc"], dtShow.Rows[i]["Times"] == DBNull.Value ? "" : "<" + dtShow.Rows[i]["Times"].ToString() + ">"));

                        string condition = "StartTimes='{0}'";

                        var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i]["StartTimes"]), "StartTimes asc");

                        int lineMax = 0;

                        for (int j = 0; j < weekRows.Count(); j++)
                        {
                            int weekDay = (int)weekRows[j]["Weekday"];
                            //weekDay += 1;
                            var curCell = GetLastCell(ws, weekDay, 0, lineEnd, ref lineMax);

                            try
                            {
                                StringBuilder sbCell = new StringBuilder();

                                sbCell.AppendLine(weekRows[j]["Name"].ToString());

                                sbCell.Append(weekRows[j]["StudentCount"] == DBNull.Value ? "" : "(" + weekRows[j]["StudentCount"].ToString() + "人)\n");

                                if (weekRows[j]["ClassroomName"] != DBNull.Value)
                                {
                                    sbCell.Append(weekRows[j]["ClassroomName"].ToString() + "\n");
                                }

                                if (weekRows[j]["WeekNums"] != DBNull.Value)
                                {
                                    string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                                    sbCell.AppendFormat("第{0}周\n", weekNum);

                                }

                                if (weekRows[j]["TeacherName"] != DBNull.Value)
                                {
                                    sbCell.AppendFormat("<{0}>\n", weekRows[j]["TeacherName"]);
                                }



                                curCell.Value += sbCell.ToString();

                                if (weekRows[j]["StopWeekNumStr"] != DBNull.Value)
                                {
                                    string stopStr = rh.SetNumberMerge(weekRows[j]["StopWeekNumStr"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                                    curCell.HtmlString += string.Format("<Br><Font Style=\"COLOR: #FF0000;\">({0}停课)</Font>", stopStr);
                                }

                                //curCell.PutValue(sbCell.ToString());
                            }
                            catch
                            {

                            }
                        }

                        var mergeRange = ws.Cells.CreateRange(lineEnd, 0, lineMax + 1, 1);

                        mergeRange.Merge();

                        var style1 = rh.BuildBorderStyle(wk);
                        StyleFlag stFlag1 = new StyleFlag();
                        stFlag1.Borders = true;

                        mergeRange.ApplyStyle(style1, stFlag1);

                        var weekRange = ws.Cells.CreateRange(lineEnd, 1, lineMax + 1, 7);

                        StyleFlag stFlag2 = new StyleFlag();
                        stFlag2.LeftBorder = true;
                        stFlag2.RightBorder = true;

                        weekRange.ApplyStyle(style1, stFlag2);

                        StyleFlag stFlag3 = new StyleFlag();
                        stFlag3.BottomBorder = true;

                        ws.Cells.CreateRange(lineEnd + lineMax, 1, 1, 7).ApplyStyle(style1, stFlag3);

                        lineEnd = lineEnd + lineMax + 1;


                    }

                    ws.AutoFitRows();
                }

                wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

                return ms;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public MemoryStream ClassmajorSchedulingPDF(Guid SchoolyearID, Guid? CollegeID, Guid? ClassmajorID, int? Years)
        {
            ReportHelper rh = new ReportHelper();

            MemoryStream ms = new MemoryStream();

            string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
            string sql2 = rh.GetReportSql("EducationScheduling/ClassmajorWithSchedule.sql");

            List<SqlParameter> sParams = new List<SqlParameter>();

            sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
            if (CollegeID.HasValue)
            {
                sParams.Add(new SqlParameter("@CollegeID", CollegeID));
            }
            else
            {
                sParams.Add(new SqlParameter("@CollegeID", DBNull.Value));
            }

            if (ClassmajorID.HasValue)
            {
                sParams.Add(new SqlParameter("@ClassmajorID", ClassmajorID));
            }
            else
            {
                sParams.Add(new SqlParameter("@ClassmajorID", DBNull.Value));
            }

            if (Years.HasValue)
            {
                sParams.Add(new SqlParameter("@Years", Years));
            }
            else
            {
                sParams.Add(new SqlParameter("@Years", DBNull.Value));
            }

            var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

            var wk = rh.GetReportTemple("EducationScheduling/ClassmajorSchedulingView.xlsx");

            var ws = wk.Worksheets[0];

            string title = "{0}{1}学期课程表-{2}", code = "", classMajorName = "";

            if (ds.Tables[1].Rows.Count > 0)
            {
                code = ds.Tables[1].Rows[0]["Code"].ToString();
                classMajorName = ds.Tables[1].Rows[0]["ClassMajorName"].ToString();
            }

            ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, classMajorName);

            //聚合唯一
            //ds.Tables[1].DefaultView.Sort = "StartTimes asc";
            var dtShow = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times");

            dtShow.DefaultView.Sort = "StartTimes asc";

            dtShow = dtShow.DefaultView.ToTable();

            int lineEnd = 2;

            for (int i = 0; i < dtShow.Rows.Count; i++)
            {
                string col1Format = "{0}\n{1}";

                ws.Cells[lineEnd, 0].PutValue(string.Format(col1Format, dtShow.Rows[i]["TimeDesc"], dtShow.Rows[i]["Times"] == DBNull.Value ? "" : "<" + dtShow.Rows[i]["Times"].ToString() + ">"));

                string condition = "StartTimes='{0}'";

                var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i]["StartTimes"]), "MinWeekNum asc");

                int lineMax = 0;

                for (int j = 0; j < weekRows.Count(); j++)
                {
                    int weekDay = (int)weekRows[j]["Weekday"];
                    //weekDay += 1;
                    var curCell = GetLastCell(ws, weekDay, 0, lineEnd, ref lineMax);

                    try
                    {
                        StringBuilder sbCell = new StringBuilder();

                        sbCell.AppendLine(weekRows[j]["Name"].ToString());

                        sbCell.Append(weekRows[j]["StudentCount"] == DBNull.Value ? "" : "(" + weekRows[j]["StudentCount"].ToString() + "人)\n");

                        if (weekRows[j]["ClassroomName"] != DBNull.Value)
                        {
                            sbCell.Append(weekRows[j]["ClassroomName"].ToString() + "\n");
                        }

                        if (weekRows[j]["WeekNums"] != DBNull.Value)
                        {
                            string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                            sbCell.AppendFormat("第{0}周\n", weekNum);

                        }

                        if (weekRows[j]["TeacherName"] != DBNull.Value)
                        {
                            sbCell.AppendFormat("<{0}>\n", weekRows[j]["TeacherName"]);
                        }



                        curCell.Value += sbCell.ToString();

                        if (weekRows[j]["StopWeekNumStr"] != DBNull.Value)
                        {
                            string stopStr = rh.SetNumberMerge(weekRows[j]["StopWeekNumStr"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                            curCell.HtmlString += string.Format("<Br><Font Style=\"COLOR: #FF0000;\">({0}停课)</Font>", stopStr);
                        }

                        //curCell.PutValue(sbCell.ToString());
                    }
                    catch
                    {

                    }
                }

                var mergeRange = ws.Cells.CreateRange(lineEnd, 0, lineMax + 1, 1);

                mergeRange.Merge();

                var style1 = rh.BuildBorderStyle(wk);
                StyleFlag stFlag1 = new StyleFlag();
                stFlag1.Borders = true;

                mergeRange.ApplyStyle(style1, stFlag1);

                var weekRange = ws.Cells.CreateRange(lineEnd, 1, lineMax + 1, 7);

                StyleFlag stFlag2 = new StyleFlag();
                stFlag2.LeftBorder = true;
                stFlag2.RightBorder = true;

                weekRange.ApplyStyle(style1, stFlag2);

                StyleFlag stFlag3 = new StyleFlag();
                stFlag3.BottomBorder = true;

                ws.Cells.CreateRange(lineEnd + lineMax, 1, 1, 7).ApplyStyle(style1, stFlag3);

                lineEnd = lineEnd + lineMax + 1;


            }

            ws.AutoFitRows();

            wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

            return ms;

        }

        public MemoryStream ClassmajorPrintingSchedulingPDF(Guid SchoolyearID, Guid? CollegeID)
        {
            try
            {
                ReportHelper rh = new ReportHelper();

                MemoryStream ms = new MemoryStream();

                var wk = rh.GetReportTemple("EducationScheduling/ClassmajorSchedulingView.xlsx");
                var templateSheetCount = wk.Worksheets.Count;

                string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
                string sql2 = rh.GetReportSql("EducationScheduling/ClassmajorWithSchedule.sql");

                List<SqlParameter> classParams = new List<SqlParameter>();
                classParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
                if (CollegeID.HasValue)
                {
                    //sParams.Add(new SqlParameter("@CollegeID", CollegeID));
                    classParams.Add(new SqlParameter("@CollegeID", CollegeID));
                }
                else
                {
                    //sParams.Add(new SqlParameter("@CollegeID", DBNull.Value));
                    classParams.Add(new SqlParameter("@CollegeID", DBNull.Value));
                }
                classParams.Add(new SqlParameter("@ClassmajorID", DBNull.Value));
                classParams.Add(new SqlParameter("@Years", DBNull.Value));

                var classds = this.UnitOfWork.QuerySQL(sql2, classParams);
                //var classTable = classds.Tables[0];
                var cpsReportViewList = DateTableHelper.DataTableToList<ClassmajorPrintingSchedulingReportView>(classds.Tables[0]);
                List<Guid?> classmajorIDList = cpsReportViewList.Select(x => x.ClassmajorID).Distinct().ToList();
                for (int i = templateSheetCount; i < classmajorIDList.Count; i += templateSheetCount)
                {
                    for (int index = 0; index < templateSheetCount; index++)
                    {
                        wk.Worksheets.AddCopy(index); //.CopyTo(wk, "Sheet" + (i + 1).ToString(), true, true);
                    }
                }
                for (int claI = 0; claI < classmajorIDList.Count(); claI++)
                {
                    List<SqlParameter> sParams = new List<SqlParameter>();

                    sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
                    if (CollegeID.HasValue)
                    {
                        sParams.Add(new SqlParameter("@CollegeID", CollegeID));
                    }
                    else
                    {
                        sParams.Add(new SqlParameter("@CollegeID", DBNull.Value));
                    }

                    var ClassmajorID = classmajorIDList[claI];
                    var cpsReportListByClassmajorID = cpsReportViewList.Where(x => x.ClassmajorID == ClassmajorID).ToList();
                    sParams.Add(new SqlParameter("@ClassmajorID", ClassmajorID));
                    sParams.Add(new SqlParameter("@Years", DBNull.Value));
                    var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

                    var ws = wk.Worksheets[claI];

                    string title = "{0}{1}学期课程表-{2}", code = "", classMajorName = "";
                    if(ds.Tables.Count == 0)
                    {
                        continue;
                    }

                    if (cpsReportListByClassmajorID.Count > 0)
                    {
                        code = cpsReportListByClassmajorID.FirstOrDefault().Code.ToString();
                        classMajorName = cpsReportListByClassmajorID.FirstOrDefault().ClassMajorName.ToString();
                    }

                    ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, classMajorName);

                    //聚合唯一
                    //ds.Tables[1].DefaultView.Sort = "StartTimes asc";
                    var dtShow = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times");

                    dtShow.DefaultView.Sort = "StartTimes asc";

                    dtShow = dtShow.DefaultView.ToTable();

                    int lineEnd = 2;

                    for (int i = 0; i < dtShow.Rows.Count; i++)
                    {
                        string col1Format = "{0}\n{1}";

                        ws.Cells[lineEnd, 0].PutValue(string.Format(col1Format, dtShow.Rows[i]["TimeDesc"], dtShow.Rows[i]["Times"] == DBNull.Value ? "" : "<" + dtShow.Rows[i]["Times"].ToString() + ">"));

                        string condition = "StartTimes='{0}'";

                        var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i]["StartTimes"]), "MinWeekNum asc");

                        int lineMax = 0;

                        for (int j = 0; j < weekRows.Count(); j++)
                        {
                            int weekDay = (int)weekRows[j]["Weekday"];
                            //weekDay += 1;
                            var curCell = GetLastCell(ws, weekDay, 0, lineEnd, ref lineMax);

                            try
                            {
                                StringBuilder sbCell = new StringBuilder();

                                sbCell.AppendLine(weekRows[j]["Name"].ToString());

                                sbCell.Append(weekRows[j]["StudentCount"] == DBNull.Value ? "" : "(" + weekRows[j]["StudentCount"].ToString() + "人)\n");

                                if (weekRows[j]["ClassroomName"] != DBNull.Value)
                                {
                                    sbCell.Append(weekRows[j]["ClassroomName"].ToString() + "\n");
                                }

                                if (weekRows[j]["WeekNums"] != DBNull.Value)
                                {
                                    string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                                    sbCell.AppendFormat("第{0}周\n", weekNum);

                                }

                                if (weekRows[j]["TeacherName"] != DBNull.Value)
                                {
                                    sbCell.AppendFormat("<{0}>\n", weekRows[j]["TeacherName"]);
                                }



                                curCell.Value += sbCell.ToString();

                                if (weekRows[j]["StopWeekNumStr"] != DBNull.Value)
                                {
                                    string stopStr = rh.SetNumberMerge(weekRows[j]["StopWeekNumStr"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                                    curCell.HtmlString += string.Format("<Br><Font Style=\"COLOR: #FF0000;\">({0}停课)</Font>", stopStr);
                                }

                                //curCell.PutValue(sbCell.ToString());
                            }
                            catch
                            {

                            }
                        }

                        var mergeRange = ws.Cells.CreateRange(lineEnd, 0, lineMax + 1, 1);

                        mergeRange.Merge();

                        var style1 = rh.BuildBorderStyle(wk);
                        StyleFlag stFlag1 = new StyleFlag();
                        stFlag1.Borders = true;

                        mergeRange.ApplyStyle(style1, stFlag1);

                        var weekRange = ws.Cells.CreateRange(lineEnd, 1, lineMax + 1, 7);

                        StyleFlag stFlag2 = new StyleFlag();
                        stFlag2.LeftBorder = true;
                        stFlag2.RightBorder = true;

                        weekRange.ApplyStyle(style1, stFlag2);

                        StyleFlag stFlag3 = new StyleFlag();
                        stFlag3.BottomBorder = true;

                        ws.Cells.CreateRange(lineEnd + lineMax, 1, 1, 7).ApplyStyle(style1, stFlag3);

                        lineEnd = lineEnd + lineMax + 1;


                    }
                    ws.AutoFitRows();
                }
                wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);
                return ms;
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public MemoryStream ClassroomReportPDF(Guid SchoolyearID, Guid? BuildingsInfoID, Guid? ClassroomID)
        {

            ReportHelper rh = new ReportHelper();
            MemoryStream ms = new MemoryStream();

            string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
            string sql2 = rh.GetReportSql("EducationScheduling/ClassroomSchedulingView.sql");

            List<SqlParameter> sParams = new List<SqlParameter>();

            sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
            if (BuildingsInfoID.HasValue)
            {
                sParams.Add(new SqlParameter("@BuildingsInfoID", BuildingsInfoID));
            }
            else
            {
                sParams.Add(new SqlParameter("@BuildingsInfoID", DBNull.Value));
            }

            if (ClassroomID.HasValue)
            {
                sParams.Add(new SqlParameter("@ClassroomID", ClassroomID));
            }
            else
            {
                sParams.Add(new SqlParameter("@ClassroomID", DBNull.Value));
            }

            var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

            var wk = rh.GetReportTemple("EducationScheduling/ClassroomSchedulingView.xlsx");

            var ws = wk.Worksheets[0];

            string title = "{0}{1}学期课程表-{2}", code = "", classroomName = "";

            if (ds.Tables[1].Rows.Count > 0)
            {
                code = ds.Tables[1].Rows[0]["Code"].ToString();
                classroomName = ds.Tables[1].Rows[0]["ClassroomName"].ToString();
            }

            ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, classroomName);

            //聚合唯一
            //ds.Tables[1].DefaultView.Sort = "StartTimes asc";
            var dtShow = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times");

            dtShow.DefaultView.Sort = "StartTimes asc";

            dtShow = dtShow.DefaultView.ToTable();

            int lineEnd = 2;

            for (int i = 0; i < dtShow.Rows.Count; i++)
            {
                string col1Format = "{0}\n{1}";

                ws.Cells[lineEnd, 0].PutValue(string.Format(col1Format, dtShow.Rows[i]["TimeDesc"], dtShow.Rows[i]["Times"] == DBNull.Value ? "" : "<" + dtShow.Rows[i]["Times"].ToString() + ">"));

                string condition = "StartTimes='{0}'";

                var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i]["StartTimes"]), "StartTimes asc");

                int lineMax = 0;

                for (int j = 0; j < weekRows.Count(); j++)
                {
                    //Name
                    int weekDay = (int)weekRows[j]["Weekday"];
                    //weekDay += 1;
                    var curCell = GetLastCell(ws, weekDay, 0, lineEnd, ref lineMax);

                    try
                    {
                        StringBuilder sbCell = new StringBuilder();

                        sbCell.AppendLine(weekRows[j]["Name"].ToString());

                        sbCell.Append(weekRows[j]["StudentCount"] == DBNull.Value ? "" : "(" + weekRows[j]["StudentCount"].ToString() + "人)\n");

                        if (weekRows[j]["Name"] != DBNull.Value)
                        {
                            sbCell.Append(weekRows[j]["ClassroomName"].ToString() + "\n");
                        }

                        if (weekRows[j]["WeekNums"] != DBNull.Value)
                        {
                            string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                            sbCell.AppendFormat("第{0}周\n", weekNum);

                        }

                        if (weekRows[j]["TeacherName"] != DBNull.Value)
                        {
                            sbCell.AppendFormat("<{0}>\n", weekRows[j]["TeacherName"]);
                        }

                        curCell.Value = sbCell.ToString();

                        //curCell.PutValue(sbCell.ToString());
                    }
                    catch
                    {

                    }


                }

                var mergeRange = ws.Cells.CreateRange(lineEnd, 0, lineMax + 1, 1);

                mergeRange.Merge();

                var style1 = rh.BuildBorderStyle(wk);
                StyleFlag stFlag1 = new StyleFlag();
                stFlag1.Borders = true;

                mergeRange.ApplyStyle(style1, stFlag1);

                var weekRange = ws.Cells.CreateRange(lineEnd, 1, lineMax + 1, 7);

                StyleFlag stFlag2 = new StyleFlag();
                stFlag2.LeftBorder = true;
                stFlag2.RightBorder = true;

                weekRange.ApplyStyle(style1, stFlag2);

                StyleFlag stFlag3 = new StyleFlag();
                stFlag3.BottomBorder = true;

                ws.Cells.CreateRange(lineEnd + lineMax, 1, 1, 7).ApplyStyle(style1, stFlag3);

                lineEnd = lineEnd + lineMax + 1;

            }

            ws.AutoFitRows();



            wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

            return ms;
        }

        public MemoryStream ClassroomPrintingReportPDF(Guid SchoolyearID, Guid? BuildingsInfoID)
        {

            try
            {
                ReportHelper rh = new ReportHelper();
                MemoryStream ms = new MemoryStream();

                string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
                string sql2 = rh.GetReportSql("EducationScheduling/ClassroomSchedulingView.sql");
                var wk = rh.GetReportTemple("EducationScheduling/ClassroomSchedulingView.xlsx");
                var templateSheetCount = wk.Worksheets.Count;

                List<SqlParameter> roomParams = new List<SqlParameter>();

                roomParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
                if (BuildingsInfoID.HasValue)
                {
                    roomParams.Add(new SqlParameter("@BuildingsInfoID", BuildingsInfoID));
                }
                else
                {
                    roomParams.Add(new SqlParameter("@BuildingsInfoID", DBNull.Value));
                }
                roomParams.Add(new SqlParameter("@ClassroomID", DBNull.Value));
                var roomds = this.UnitOfWork.QuerySQL(sql2, roomParams);

                var csReportViewList = DateTableHelper.DataTableToList<ClassroomSchedulingReportView>(roomds.Tables[0]);
                List<Guid?> roomIDList = csReportViewList.Select(x => x.ClassroomID).Distinct().ToList();
                for (int i = templateSheetCount; i < roomIDList.Count; i += templateSheetCount)
                {
                    for (int index = 0; index < templateSheetCount; index++)
                    {
                        wk.Worksheets.AddCopy(index); //.CopyTo(wk, "Sheet" + (i + 1).ToString(), true, true);
                    }
                }

                for (int m = 0; m < roomIDList.Count(); m++)
                {
                    var roomID = roomIDList[m];
                    List<SqlParameter> sParams = new List<SqlParameter>();

                    sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
                    if (BuildingsInfoID.HasValue)
                    {
                        sParams.Add(new SqlParameter("@BuildingsInfoID", BuildingsInfoID));
                    }
                    else
                    {
                        sParams.Add(new SqlParameter("@BuildingsInfoID", DBNull.Value));
                    }
                    sParams.Add(new SqlParameter("@ClassroomID", roomID));
                    //if (ClassroomID.HasValue)
                    //{
                    //    sParams.Add(new SqlParameter("@ClassroomID", ClassroomID));
                    //}
                    //else
                    //{
                    //    sParams.Add(new SqlParameter("@ClassroomID", DBNull.Value));
                    //}

                    var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

                    var ws = wk.Worksheets[m];

                    string title = "{0}{1}学期课程表-{2}", code = "", classroomName = "";

                    if (ds.Tables[1].Rows.Count > 0)
                    {
                        code = ds.Tables[1].Rows[0]["Code"].ToString();
                        classroomName = ds.Tables[1].Rows[0]["ClassroomName"].ToString();
                    }

                    ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, classroomName);

                    //聚合唯一
                    //ds.Tables[1].DefaultView.Sort = "StartTimes asc";
                    var dtShow = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times");

                    dtShow.DefaultView.Sort = "StartTimes asc";

                    dtShow = dtShow.DefaultView.ToTable();

                    int lineEnd = 2;

                    for (int i = 0; i < dtShow.Rows.Count; i++)
                    {
                        string col1Format = "{0}\n{1}";

                        ws.Cells[lineEnd, 0].PutValue(string.Format(col1Format, dtShow.Rows[i]["TimeDesc"], dtShow.Rows[i]["Times"] == DBNull.Value ? "" : "<" + dtShow.Rows[i]["Times"].ToString() + ">"));

                        string condition = "StartTimes='{0}'";

                        var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i]["StartTimes"]), "StartTimes asc");

                        int lineMax = 0;

                        for (int j = 0; j < weekRows.Count(); j++)
                        {
                            //Name
                            int weekDay = (int)weekRows[j]["Weekday"];
                            //weekDay += 1;
                            var curCell = GetLastCell(ws, weekDay, 0, lineEnd, ref lineMax);

                            try
                            {
                                StringBuilder sbCell = new StringBuilder();

                                sbCell.AppendLine(weekRows[j]["Name"].ToString());

                                sbCell.Append(weekRows[j]["StudentCount"] == DBNull.Value ? "" : "(" + weekRows[j]["StudentCount"].ToString() + "人)\n");

                                if (weekRows[j]["Name"] != DBNull.Value)
                                {
                                    sbCell.Append(weekRows[j]["ClassroomName"].ToString() + "\n");
                                }

                                if (weekRows[j]["WeekNums"] != DBNull.Value)
                                {
                                    string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                                    sbCell.AppendFormat("第{0}周\n", weekNum);

                                }

                                if (weekRows[j]["TeacherName"] != DBNull.Value)
                                {
                                    sbCell.AppendFormat("<{0}>\n", weekRows[j]["TeacherName"]);
                                }

                                curCell.Value = sbCell.ToString();

                                //curCell.PutValue(sbCell.ToString());
                            }
                            catch
                            {

                            }


                        }

                        var mergeRange = ws.Cells.CreateRange(lineEnd, 0, lineMax + 1, 1);

                        mergeRange.Merge();

                        var style1 = rh.BuildBorderStyle(wk);
                        StyleFlag stFlag1 = new StyleFlag();
                        stFlag1.Borders = true;

                        mergeRange.ApplyStyle(style1, stFlag1);

                        var weekRange = ws.Cells.CreateRange(lineEnd, 1, lineMax + 1, 7);

                        StyleFlag stFlag2 = new StyleFlag();
                        stFlag2.LeftBorder = true;
                        stFlag2.RightBorder = true;

                        weekRange.ApplyStyle(style1, stFlag2);

                        StyleFlag stFlag3 = new StyleFlag();
                        stFlag3.BottomBorder = true;

                        ws.Cells.CreateRange(lineEnd + lineMax, 1, 1, 7).ApplyStyle(style1, stFlag3);

                        lineEnd = lineEnd + lineMax + 1;

                    }

                    ws.AutoFitRows();
                }



                wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

                return ms;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public MemoryStream DepartmentSchedulingPDF(Guid SchoolyearID, Guid CollegeID, Guid DepartmentID, Guid? HandleModeID)
        {

            ReportHelper rh = new ReportHelper();
            MemoryStream ms = new MemoryStream();

            string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
            string sql2 = rh.GetReportSql("EducationScheduling/DepartmentWithSchedule.sql");

            List<SqlParameter> sParams = new List<SqlParameter>();

            sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
            sParams.Add(new SqlParameter("@CollegeID ", CollegeID));
            sParams.Add(new SqlParameter("@DepartmentID", DepartmentID));
            if (HandleModeID.HasValue)
            {
                sParams.Add(new SqlParameter("@HandleModeID", HandleModeID));
            }
            else
            {
                sParams.Add(new SqlParameter("@HandleModeID", DBNull.Value));
            }


            var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

            var wk = rh.GetReportTemple("EducationScheduling/DepartmentWithSchedule.xlsx");



            var ws = wk.Worksheets[0];

            string title = "{0}{1}学期-{2}课程表", schoolyearCode = "", DepartmentName = "";

            if (ds.Tables[1].Rows.Count > 0)
            {
                schoolyearCode = ds.Tables[1].Rows[0]["SchoolyearCode"].ToString();
                DepartmentName = ds.Tables[1].Rows[0]["DepartmentName"].ToString();
            }

            ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], schoolyearCode, DepartmentName);

            //聚合唯一
            var dtShow = ds.Tables[1].DefaultView.ToTable(true, "CourseName", "EducationMissionClassName", "TotalHours", "StudentCount", "TeacherName", "TitleDesc");



            for (int i = 0; i < dtShow.Rows.Count; i++)
            {

                ws.Cells[i + 2, 0].PutValue(dtShow.Rows[i]["CourseName"]);
                ws.Cells[i + 2, 1].PutValue(dtShow.Rows[i]["EducationMissionClassName"]);
                ws.Cells[i + 2, 2].PutValue(dtShow.Rows[i]["TotalHours"]);
                ws.Cells[i + 2, 3].PutValue(dtShow.Rows[i]["StudentCount"]);
                ws.Cells[i + 2, 4].PutValue(dtShow.Rows[i]["TeacherName"]);
                ws.Cells[i + 2, 5].PutValue(dtShow.Rows[i]["TitleDesc"]);

                //周几方法
                try
                {
                    string condition = "CourseName='{0}' and EducationMissionClassName='{1}' and TotalHours='{2}' and StudentCount='{3}' and TeacherName='{4}' and TitleDesc='{5}' ";

                    var weekRows = ds.Tables[1].Select(string.Format(condition, dtShow.Rows[i].ItemArray), "StartTimes asc");

                    for (int j = 0; j < weekRows.Count(); j++)
                    {
                        int weekDay = (int)weekRows[j]["Weekday"] == 0 ? 7 : (int)weekRows[j]["Weekday"];
                        weekDay += 5;
                        var curCell = ws.Cells[i + 2, weekDay];

                        if (weekRows[j]["ClassroomID"] != DBNull.Value)
                        {
                            string weekNum = rh.SetNumberMerge(weekRows[j]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                            string timeNum = rh.SetNumberMerge(weekRows[j]["Times"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                            if (curCell.Value != null && curCell.Value.ToString() != "")
                            {
                                curCell.Value += " \n";
                            }
                            else
                            {
                                curCell.Value = "";
                            }

                            curCell.Value += string.Format("第{0}周\n第{1}节\n({2})", weekNum, timeNum, weekRows[j]["ClassroomName"]);
                        }

                        if (weekRows[j]["StopWeekNumStr"] != DBNull.Value)
                        {
                            string stopStr = rh.SetNumberMerge(weekRows[j]["StopWeekNumStr"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                            curCell.HtmlString += string.Format("<Br><Font Style=\"COLOR: #FF0000;\">({0}停课)</Font>", stopStr);

                        }

                    }
                }
                catch (Exception ex)
                {

                    var a = ex;
                }



            }
            var style = rh.BuildBorderStyle(wk);

            //style.IsTextWrapped = true;
            if (dtShow.Rows.Count > 0)
            {
                var wstrange = ws.Cells.CreateRange(2, 0, dtShow.Rows.Count, 13);

                StyleFlag stFlag = new StyleFlag();
                stFlag.Borders = true;

                wstrange.ApplyStyle(style, stFlag);
            }

            ws.AutoFitColumns(0, 5);
            ws.AutoFitRows();

            wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

            return ms;
        }
        public MemoryStream CollegeSchedulingPDF(Guid SchoolyearID, Guid? CollegeID, int? Years)
        {
            ReportHelper rh = new ReportHelper();
            MemoryStream ms = new MemoryStream();

            string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
            string sql2 = rh.GetReportSql("EducationScheduling/ClassmajorWithSchedule.sql");

            List<SqlParameter> sParams = new List<SqlParameter>();

            sParams.Add(new SqlParameter("@SchoolyearID", SchoolyearID));
            if (CollegeID.HasValue)
            {
                sParams.Add(new SqlParameter("@CollegeID", CollegeID));
            }
            else
            {
                sParams.Add(new SqlParameter("@CollegeID", DBNull.Value));
            }

            sParams.Add(new SqlParameter("@ClassmajorID", DBNull.Value));

            if (Years.HasValue)
            {
                sParams.Add(new SqlParameter("@Years", Years));
            }
            else
            {
                sParams.Add(new SqlParameter("@Years", DBNull.Value));
            }

            var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);

            var wk = rh.GetReportTemple("EducationScheduling/CollegeSchedulingView.xlsx");

            var ws = wk.Worksheets[0];

            string title = "{0}{1}学期课程表-{2}", code = "", classMajorName = "";

            if (ds.Tables[1].Rows.Count > 0)
            {
                code = ds.Tables[1].Rows[0]["Code"].ToString();
                classMajorName = ds.Tables[1].Rows[0]["CollegeName"].ToString();
            }

            ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], code, classMajorName);

            var dicWeeks = GetDicWeeks();
            var dtTimes = ds.Tables[1].DefaultView.ToTable(true, "StartTimes", "TimeDesc", "Times").AsEnumerable().OrderBy(o => o.Field<int>("StartTimes")).ToList();


            //表头
            for (int i = 0; i < dicWeeks.Count; i++)
            {
                var rowFirst = i * dtTimes.Count + 2;
                ws.Cells[1, rowFirst].PutValue(dicWeeks[i].Item2);

                for (int j = 0; j < dtTimes.Count; j++)
                {
                    string subTitle = "";

                    if (dtTimes[j]["Times"] != DBNull.Value)
                    {

                        subTitle += "<" + dtTimes[j].Field<string>("Times") + ">\n";
                    }

                    if (dtTimes[j]["TimeDesc"] != DBNull.Value)
                    {
                        subTitle += dtTimes[j].Field<string>("TimeDesc");
                    }

                    ws.Cells[2, rowFirst + j].PutValue(subTitle);
                }

                var subTitleRange = ws.Cells.CreateRange(1, rowFirst, 1, dtTimes.Count);
                subTitleRange.Merge();

            }
            ws.Cells.CreateRange(0, 0, 1, dtTimes.Count * dicWeeks.Count + 2).Merge();
            ws.Cells.CreateRange(1, 0, 2, 2).Merge();
            var titleRange = ws.Cells.CreateRange(0, 0, 3, dtTimes.Count * dicWeeks.Count + 2);
            var style = rh.BuildBorderStyle(wk);
            StyleFlag stFlag = new StyleFlag();
            stFlag.Borders = true;

            titleRange.ApplyStyle(style, stFlag);

            //学年和班级聚合


            var dtSecond = ds.Tables[1].DefaultView.ToTable(true, "GrademajorID", "GrademajorName", "ClassMajorID", "ClassMajorName");

            dtSecond.DefaultView.Sort = "GrademajorName asc";

            var dtFirst = dtSecond.DefaultView.ToTable(true, "GrademajorID", "GrademajorName");

            int lineEnd = 3;

            for (int i = 0; i < dtFirst.Rows.Count; i++)
            {
                string condition = "GrademajorID='{0}'";

                var classRows = dtSecond.Select(string.Format(condition, dtFirst.Rows[i]["GrademajorID"]), "ClassMajorName asc");

                int jLineMax = 0;
                for (int j = 0; j < classRows.Count(); j++)
                {
                    string conditionItem = "GrademajorID='{0}' and ClassMajorID='{1}'";

                    var cellItems = ds.Tables[1].Select(string.Format(conditionItem, classRows[j]["GrademajorID"], classRows[j]["ClassMajorID"]));
                    int klineMax = 0;
                    for (int k = 0; k < cellItems.Count(); k++)
                    {
                        int addCol = 2;
                        addCol += dtTimes.FindIndex(w => w.Field<int>("StartTimes") == cellItems[k].Field<int>("StartTimes"));
                        var curCell = GetCrossTimeCell(ws, cellItems[k].Field<int>("Weekday"), dtTimes.Count, addCol, lineEnd + jLineMax, ref klineMax);
                        //var curCell=GetLastCell(ws,(int)cellItems[k]["Weekday"],

                        try
                        {
                            StringBuilder sbCell = new StringBuilder();

                            sbCell.AppendLine(cellItems[k]["Name"].ToString());

                            sbCell.Append(cellItems[k]["StudentCount"] == DBNull.Value ? "" : "(" + cellItems[k]["StudentCount"].ToString() + "人)\n");

                            if (cellItems[k]["Name"] != DBNull.Value)
                            {
                                sbCell.Append(cellItems[k].Field<string>("ClassroomName") + "\n");
                            }

                            if (cellItems[k]["WeekNums"] != DBNull.Value)
                            {
                                string weekNum = rh.SetNumberMerge(cellItems[k]["WeekNums"].ToString().Split(',').Select(s => int.Parse(s)).ToList());

                                sbCell.AppendFormat("第{0}周\n", weekNum);

                            }

                            if (cellItems[k]["TeacherName"] != DBNull.Value)
                            {
                                sbCell.AppendFormat("<{0}>\n", cellItems[k]["TeacherName"]);
                            }

                            curCell.Value = sbCell.ToString();

                            if (cellItems[k]["StopWeekNumStr"] != DBNull.Value)
                            {
                                string stopStr = rh.SetNumberMerge(cellItems[k]["StopWeekNumStr"].ToString().Split(',').Select(s => int.Parse(s)).ToList());
                                curCell.HtmlString += string.Format("<Br><Font Style=\"COLOR: #FF0000;\">({0}停课)</Font>", stopStr);

                            }

                            //curCell.PutValue(sbCell.ToString());
                        }
                        catch
                        {

                        }
                    }

                    //第二格合并
                    ws.Cells[lineEnd + jLineMax, 1].Value = classRows[j].Field<string>("ClassMajorName").Replace(classRows[j].Field<string>("GrademajorName"), "");

                    ws.Cells.CreateRange(lineEnd + jLineMax, 1, klineMax + 1, 1).Merge();

                    StyleFlag jLineFlag = new StyleFlag();
                    jLineFlag.LeftBorder = true;
                    jLineFlag.RightBorder = true;

                    ws.Cells.CreateRange(lineEnd + jLineMax, 0, klineMax + 1, dtTimes.Count * dicWeeks.Count + 2).ApplyStyle(style, jLineFlag);

                    StyleFlag jLinebuttFlag = new StyleFlag();
                    jLinebuttFlag.BottomBorder = true;

                    ws.Cells.CreateRange(lineEnd + jLineMax + klineMax, 1, 1, dtTimes.Count * dicWeeks.Count + 2).ApplyStyle(style, jLinebuttFlag);

                    //style


                    //lineEnd

                    jLineMax += klineMax + 1;

                }

                //第一格合并
                ws.Cells[lineEnd, 0].Value = dtFirst.Rows[i].Field<string>("GrademajorName");

                var gRange = ws.Cells.CreateRange(lineEnd, 0, jLineMax, 1);

                gRange.Merge();

                StyleFlag gFlag = new StyleFlag();

                gFlag.Borders = true;

                gRange.ApplyStyle(style, gFlag);

                lineEnd += jLineMax;
            }



            ws.AutoFitRows();

            wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

            return ms;
        }

        public MemoryStream StudentCountPDF(int? InSchoolStatusID)
        {
            try
            {
                ReportHelper rh = new ReportHelper();
                MemoryStream ms = new MemoryStream();
                StyleFlag commentFlag = new StyleFlag();
                commentFlag.Borders = true;
                commentFlag.Font = true;
                commentFlag.VerticalAlignment = true;
                commentFlag.HorizontalAlignment = true;

                string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
                string sql2 = rh.GetReportSql("StudentManage/StudentCountView.sql");

                List<SqlParameter> sParams = new List<SqlParameter>();

                if (InSchoolStatusID.HasValue)
                {
                    sParams.Add(new SqlParameter("@InSchoolStatusID", InSchoolStatusID));
                }
                else
                {
                    sParams.Add(new SqlParameter("@InSchoolStatusID", DBNull.Value));
                }

                var ds = this.UnitOfWork.QuerySQL(sql1 + "\r\n" + sql2, sParams);
                var wk = rh.GetReportTemple("StudentManage/StudentCountView.xlsx");
                var ws = wk.Worksheets[0];
                var style = rh.BuildBorderStyle(wk);
                int allRow = 4;
                string title = "{0}{1}学生学籍信息统计表", schoolyearCode = "";
                if (ds.Tables[1].Rows.Count > 0)
                {
                    schoolyearCode = ds.Tables[1].Rows[0]["SchoolyearCode"].ToString();
                }
                var table1 = DateTableHelper.DataTableToList<StudentCountReportView>(ds.Tables[1]);
                var gradeYearCount = table1.Select(x => x.GradeYear).Distinct().ToList().Count();
                var educationNameList = table1.Select(x => new { x.EducationID, x.EducationName }).Distinct().OrderBy(x => x.EducationID).ToList();
                var studentTypeList = table1.Select(x => new { x.EducationID, x.EducationName, x.StudentType, x.StudentTypeName }).Distinct().OrderBy(x => x.StudentType).ToList();
                var gGradeYearList = table1.GroupBy(x => new { x.GradeYear, x.EducationID, x.EducationName, x.StudentType, x.StudentTypeName }).Select(x => new
                {
                    GradeYear = x.Key.GradeYear,
                    StudentMaleCount = x.Sum(y => y.StudentMaleCount),
                    StudentFemaleCount = x.Sum(y => y.StudentFemaleCount),
                    ClassmajorCount = x.Sum(y => y.ClassmajorCount),
                    EducationID = x.Key.EducationID,
                    EducationName = x.Key.EducationName,
                    StudentType = x.Key.StudentType,
                    StudentTypeName = x.Key.StudentTypeName,
                }).OrderBy(x => x.GradeYear).ToList();
                var campusNameList = table1.Select(x => new { x.CampusID, x.CampusNo, x.CampusName }).OrderBy(x => x.CampusID).Distinct().OrderBy(x => x.CampusID).ToList();
                var collegeNameList = table1.Select(x => new { x.CampusID, x.CampusNo, x.CampusName, x.CollegeID, x.CollegeNo, x.CollegeName }).Distinct().OrderBy(x => x.CollegeID).ToList();
                var standardNameList = table1.Select(x => new { x.CampusID, x.CampusNo, x.CampusName, x.CollegeID, x.CollegeNo, x.CollegeName, x.StandardID, x.StandardName }).Distinct().OrderBy(x => x.StandardID).ToList();
                var gGradeYearList2 = table1.GroupBy(x => new
                {
                    x.GradeYear,
                    x.EducationID,
                    x.EducationName,
                    x.StudentType,
                    x.StudentTypeName,
                    x.CampusID,
                    x.CampusNo,
                    x.CampusName,
                    x.CollegeID,
                    x.CollegeNo,
                    x.CollegeName,
                    x.StandardID,
                    x.StandardName
                }).Select(x => new
                {
                    GradeYear = x.Key.GradeYear,
                    StudentMaleCount = x.Sum(y => y.StudentMaleCount),
                    StudentFemaleCount = x.Sum(y => y.StudentFemaleCount),
                    ClassmajorCount = x.Sum(y => y.ClassmajorCount),
                    EducationID = x.Key.EducationID,
                    EducationName = x.Key.EducationName,
                    StudentType = x.Key.StudentType,
                    StudentTypeName = x.Key.StudentTypeName,
                    CampusID = x.Key.CampusID,
                    CampusNo = x.Key.CampusNo,
                    CampusName = x.Key.CampusName,
                    CollegeID = x.Key.CollegeID,
                    CollegeNo = x.Key.CollegeNo,
                    CollegeName = x.Key.CollegeName,
                    StandardID = x.Key.StandardID,
                    StandardName = x.Key.StandardName,
                }).OrderBy(x => x.CampusID).ThenBy(x => x.CollegeID).ThenBy(x => x.StandardID).ThenBy(x => x.GradeYear).ToList();
                var collegeTotalList = table1.GroupBy(x => new
                {
                    x.CollegeID,
                    x.CollegeNo,
                    x.CollegeName,
                    x.GradeYear,
                    x.StudentType,
                    x.StudentTypeName,
                    x.EducationID,
                    x.EducationName,
                }).Select(x => new
                {
                    GradeYear = x.Key.GradeYear,
                    StudentMaleCount = x.Sum(y => y.StudentMaleCount),
                    StudentFemaleCount = x.Sum(y => y.StudentFemaleCount),
                    ClassmajorCount = x.Sum(y => y.ClassmajorCount),
                    CollegeID = x.Key.CollegeID,
                    CollegeNo = x.Key.CollegeNo,
                    CollegeName = x.Key.CollegeName,
                    StudentType = x.Key.StudentType,
                    StudentTypeName = x.Key.StudentTypeName,
                    EducationID = x.Key.EducationID,
                    EducationName = x.Key.EducationName,
                }).OrderBy(x => x.CollegeID).ThenBy(x => x.GradeYear).ToList();

                ws.Cells[0, 0].Value = string.Format(title, ds.Tables[0].Rows[0]["Name"], schoolyearCode);
                var titleRange = ws.Cells.CreateRange(0, 0, 1, 5 * studentTypeList.Count + 5);
                var titlestyle = rh.BuildBorderStyleAndSize(wk);
                titleRange.Merge();
                StyleFlag titleFlag = new StyleFlag();
                titleFlag.Borders = true;
                titleFlag.Font = true;
                titleFlag.VerticalAlignment = true;
                titleFlag.HorizontalAlignment = true;
                titleRange.ApplyStyle(titlestyle, titleFlag);
                var colCount = 4;
                var rowCount = 1;
                for (int i = 0; i < educationNameList.Count; i++)
                {
                    var education = educationNameList[i];
                    //第一行标题合并
                    ws.Cells[rowCount, colCount].Value = education.EducationName;
                    var gRange = ws.Cells.CreateRange(rowCount, colCount, 1, 5);
                    gRange.Merge();
                    gRange.ApplyStyle(style, commentFlag);
                    var stCount = colCount;

                    var studentTypeListByEducation = studentTypeList.Where(x => x.EducationID == education.EducationID).ToList();
                    for (int j = 0; j < studentTypeListByEducation.Count; j++)
                    {
                        var studentType = studentTypeListByEducation[j];
                        //第三行标题
                        ws.Cells[rowCount + 2, stCount].Value = "男";
                        ws.Cells[rowCount + 2, stCount].SetStyle(style);
                        ws.Cells[rowCount + 2, stCount + 1].Value = "女";
                        ws.Cells[rowCount + 2, stCount + 1].SetStyle(style);
                        ws.Cells[rowCount + 2, stCount + 2].Value = "总人数";
                        ws.Cells[rowCount + 2, stCount + 2].SetStyle(style);
                        ws.Cells[rowCount + 2, stCount + 3].Value = "班级数";
                        ws.Cells[rowCount + 2, stCount + 3].SetStyle(style);
                        //第二行标题合并
                        ws.Cells[rowCount + 1, stCount].Value = studentType.StudentTypeName;
                        var stRange = ws.Cells.CreateRange(rowCount + 1, stCount, 1, 4);
                        stRange.Merge();
                        stRange.ApplyStyle(style, commentFlag);
                        stCount += 4;
                        ws.Cells[rowCount + 1, stCount].Value = "小计";
                        var xiaojiRange = ws.Cells.CreateRange(rowCount + 1, stCount, 2, 1);
                        xiaojiRange.Merge();
                        xiaojiRange.ApplyStyle(style, commentFlag);
                        stCount += 1;
                        var gradeYearListByStudentType = gGradeYearList.Where(x => x.EducationID == education.EducationID && x.StudentType == studentType.StudentType).ToList();
                        int? allSchoolMaleCount = 0;
                        int? allSchoolFemaleCount = 0;
                        int? allSchoolClassCount = 0;
                        for (int k = 0; k < gradeYearListByStudentType.Count; k++)
                        {
                            var gradeYear = gradeYearListByStudentType[k];
                            if (j == 0)
                            {
                                //年级
                                ws.Cells[allRow + k, 3].Value = gradeYear.GradeYear.ToString();
                                ws.Cells[allRow + k, 3].SetStyle(style);
                            }
                            //男
                            ws.Cells[allRow + k, 4 + j * 4].Value = gradeYear.StudentMaleCount.ToString();
                            ws.Cells[allRow + k, 4 + j * 4].SetStyle(style);
                            allSchoolMaleCount += gGradeYearList[i].StudentMaleCount;
                            //女
                            ws.Cells[allRow + k, 5 + j * 4].Value = gradeYear.StudentFemaleCount.ToString();
                            ws.Cells[allRow + k, 5 + j * 4].SetStyle(style);
                            allSchoolFemaleCount += gGradeYearList[i].StudentFemaleCount;
                            //总
                            ws.Cells[allRow + k, 6 + j * 4].Value = (gradeYear.StudentMaleCount + gradeYear.StudentFemaleCount).ToString();
                            ws.Cells[allRow + k, 6 + j * 4].SetStyle(style);
                            //班级数
                            ws.Cells[allRow + k, 7 + j * 4].Value = gradeYear.ClassmajorCount.ToString();
                            ws.Cells[allRow + k, 7 + j * 4].SetStyle(style);
                            allSchoolClassCount += gGradeYearList[i].ClassmajorCount;
                            if (j == studentTypeListByEducation.Count - 1)
                            {
                                //小计
                                var xiaojiCount = gGradeYearList.Where(x => x.EducationID == education.EducationID && x.GradeYear == gradeYear.GradeYear).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                ws.Cells[allRow + k, 8 + j * 4].Value = xiaojiCount.ToString();
                                ws.Cells[allRow + k, 8 + j * 4].SetStyle(style);
                            }
                            if (i == educationNameList.Count - 1)
                            {
                                //总计
                                var zongjiCount = gGradeYearList2.Where(x => x.GradeYear == gradeYear.GradeYear).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                ws.Cells[allRow + k, 4 + studentTypeListByEducation.Count * 5].Value = zongjiCount.ToString();
                                ws.Cells[allRow + k, 4 + studentTypeListByEducation.Count * 5].SetStyle(style);
                            }
                        }
                        //总计
                        ws.Cells[allRow + gradeYearListByStudentType.Count(), 4 + j * 4].Value = allSchoolMaleCount;
                        ws.Cells[allRow + gradeYearListByStudentType.Count(), 4 + j * 4].SetStyle(style);
                        ws.Cells[allRow + gradeYearListByStudentType.Count(), 5 + j * 4].Value = allSchoolFemaleCount;
                        ws.Cells[allRow + gradeYearListByStudentType.Count(), 5 + j * 4].SetStyle(style);
                        ws.Cells[allRow + gradeYearListByStudentType.Count(), 6 + j * 4].Value = allSchoolMaleCount + allSchoolFemaleCount;
                        ws.Cells[allRow + gradeYearListByStudentType.Count(), 6 + j * 4].SetStyle(style);
                        ws.Cells[allRow + gradeYearListByStudentType.Count(), 7 + j * 4].Value = allSchoolClassCount;
                        ws.Cells[allRow + gradeYearListByStudentType.Count(), 7 + j * 4].SetStyle(style);
                        if (j == studentTypeListByEducation.Count - 1)
                        {
                            //总计小计
                            var xiaojiCount = gGradeYearList.Where(x => x.EducationID == education.EducationID).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                            ws.Cells[allRow + gradeYearListByStudentType.Count(), 8 + j * 4].Value = xiaojiCount.ToString();
                            ws.Cells[allRow + gradeYearListByStudentType.Count(), 8 + j * 4].SetStyle(style);
                        }
                        if (i == educationNameList.Count - 1)
                        {
                            //总计小计
                            var xiaojiCount = gGradeYearList.Where(x => x.EducationID == education.EducationID).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                            ws.Cells[allRow + gradeYearListByStudentType.Count(), 4 + studentTypeListByEducation.Count * 5].Value = xiaojiCount.ToString();
                            ws.Cells[allRow + gradeYearListByStudentType.Count(), 4 + studentTypeListByEducation.Count * 5].SetStyle(style);
                        }
                        allRow += gradeYearListByStudentType.Count + 1;
                    }

                    colCount += 5;

                }

                for (int i = 0; i < campusNameList.Count(); i++)
                {

                    var campus = campusNameList[i];
                    int? campusRowCout = 0;
                    var collegeListByCampus = collegeNameList.Where(x => x.CampusID == campus.CampusID).OrderBy(x => x.CollegeID).ToList();
                    var campusStartRow = allRow;
                    for (int j = 0; j < collegeListByCampus.Count(); j++)
                    {
                        var college = collegeListByCampus[j];
                        int? collegeRowCount = 0;
                        //var collegeGradeYearCount = gGradeYearList2.Where(x => x.CampusID == campus.CampusID && x.CollegeID == college.CollegeID).Count();
                        //ws.Cells[allRow, 2].Value = college.CollegeName;
                        //var collegeRange = ws.Cells.CreateRange(allRow, 2, 1, collegeRowCount.Value);
                        //collegeRange.Merge();
                        //collegeRange.ApplyStyle(style, commentFlag);
                        //campusRowCout += collegeGradeYearCount + 1;
                        var startRow = allRow;

                        var standardListByCollege = standardNameList.Where(x => x.CampusID == campus.CampusID && x.CollegeID == college.CollegeID).OrderBy(x => x.StandardID).ToList();
                        for (int k = 0; k < standardListByCollege.Count(); k++)
                        {
                            var standard = standardListByCollege[k];
                            var standardGradeYearCount = gGradeYearList2.Where(x => x.CampusID == campus.CampusID && x.CollegeID == college.CollegeID
                                    && x.StandardID == standard.StandardID).Count();
                            collegeRowCount += standardGradeYearCount + 1;
                            ws.Cells[allRow, 2].Value = standard.StandardName;
                            var gRange = ws.Cells.CreateRange(allRow, 2, standardGradeYearCount + 1, 1);
                            gRange.Merge();
                            gRange.ApplyStyle(style, commentFlag);
                            for (int m = 0; m < educationNameList.Count(); m++)
                            {
                                var education = educationNameList[m];
                                var studentTypeListByEducation = studentTypeList.Where(x => x.EducationID == education.EducationID).OrderBy(x => x.StudentType).ToList();
                                for (int n = 0; n < studentTypeListByEducation.Count(); n++)
                                {
                                    var studentType = studentTypeListByEducation[n];
                                    var dbListByStudentType = gGradeYearList2.Where(x => x.CampusID == campus.CampusID && x.CollegeID == college.CollegeID
                                    && x.StandardID == standard.StandardID && x.EducationID == education.EducationID && x.StudentType == studentType.StudentType).OrderBy(x => x.GradeYear).ToList();
                                    //int? allSchoolMaleCount = 0;
                                    //int? allSchoolFemaleCount = 0;
                                    //int? allSchoolClassCount = 0;
                                    for (int p = 0; p < dbListByStudentType.Count(); p++)
                                    {
                                        var db = dbListByStudentType[p];
                                        if (n == 0)
                                        {
                                            ws.Cells[allRow + p, 3].Value = db.GradeYear;
                                            ws.Cells[allRow + p, 3].SetStyle(style);
                                        }
                                        //男
                                        ws.Cells[allRow + p, 4 + n * 4].Value = db.StudentMaleCount.ToString();
                                        ws.Cells[allRow + p, 4 + n * 4].SetStyle(style);
                                        //allSchoolMaleCount += db.StudentMaleCount;
                                        //女
                                        ws.Cells[allRow + p, 5 + n * 4].Value = db.StudentFemaleCount.ToString();
                                        ws.Cells[allRow + p, 5 + n * 4].SetStyle(style);
                                        //allSchoolFemaleCount += gGradeYearList[i].StudentFemaleCount;
                                        //总
                                        ws.Cells[allRow + p, 6 + n * 4].Value = (db.StudentMaleCount + db.StudentFemaleCount).ToString();
                                        ws.Cells[allRow + p, 6 + n * 4].SetStyle(style);
                                        //班级数
                                        ws.Cells[allRow + p, 7 + n * 4].Value = db.ClassmajorCount.ToString();
                                        ws.Cells[allRow + p, 7 + n * 4].SetStyle(style);
                                        //allSchoolClassCount += gGradeYearList[i].ClassmajorCount;
                                        if (n == studentTypeListByEducation.Count - 1)
                                        {
                                            //小计
                                            var xiaojiCount = gGradeYearList2.Where(x => x.CampusID == campus.CampusID && x.CollegeID == college.CollegeID
                                            && x.StandardID == standard.StandardID && x.EducationID == education.EducationID
                                            && x.GradeYear == db.GradeYear).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                            ws.Cells[allRow + p, 8 + n * 4].Value = xiaojiCount.ToString();
                                            ws.Cells[allRow + p, 8 + n * 4].SetStyle(style);
                                        }
                                        if (m == educationNameList.Count - 1)
                                        {
                                            //总计
                                            var zongjiCount = gGradeYearList2.Where(x => x.CampusID == campus.CampusID && x.CollegeID == college.CollegeID
                                            && x.StandardID == standard.StandardID && x.GradeYear == db.GradeYear).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                            ws.Cells[allRow + p, 4 + studentTypeListByEducation.Count * 5].Value = zongjiCount.ToString();
                                            ws.Cells[allRow + p, 4 + studentTypeListByEducation.Count * 5].SetStyle(style);
                                        }
                                    }
                                    if (n == 0)
                                    {
                                        ws.Cells[allRow + dbListByStudentType.Count(), 3].Value = "小计";
                                        ws.Cells[allRow + dbListByStudentType.Count(), 3].SetStyle(style);
                                    }
                                    //男
                                    ws.Cells[allRow + dbListByStudentType.Count(), 4 + n * 4].Value = dbListByStudentType.Sum(x => x.StudentMaleCount).ToString();
                                    ws.Cells[allRow + dbListByStudentType.Count(), 4 + n * 4].SetStyle(style);
                                    //allSchoolMaleCount += db.StudentMaleCount;
                                    //女
                                    ws.Cells[allRow + dbListByStudentType.Count(), 5 + n * 4].Value = dbListByStudentType.Sum(x => x.StudentFemaleCount).ToString();
                                    ws.Cells[allRow + dbListByStudentType.Count(), 5 + n * 4].SetStyle(style);
                                    //allSchoolFemaleCount += gGradeYearList[i].StudentFemaleCount;
                                    //总
                                    ws.Cells[allRow + dbListByStudentType.Count(), 6 + n * 4].Value = dbListByStudentType.Sum(x => x.StudentFemaleCount + x.StudentFemaleCount).ToString();
                                    ws.Cells[allRow + dbListByStudentType.Count(), 6 + n * 4].SetStyle(style);
                                    //班级数
                                    ws.Cells[allRow + dbListByStudentType.Count(), 7 + n * 4].Value = dbListByStudentType.Sum(x => x.ClassmajorCount).ToString();
                                    ws.Cells[allRow + dbListByStudentType.Count(), 7 + n * 4].SetStyle(style);
                                    //allSchoolClassCount += gGradeYearList[i].ClassmajorCount;
                                    if (n == studentTypeListByEducation.Count - 1)
                                    {
                                        //小计
                                        var xiaojiCount = gGradeYearList2.Where(x => x.CampusID == campus.CampusID && x.CollegeID == college.CollegeID
                                        && x.StandardID == standard.StandardID && x.EducationID == education.EducationID).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                        ws.Cells[allRow + dbListByStudentType.Count(), 8 + n * 4].Value = xiaojiCount.ToString();
                                        ws.Cells[allRow + dbListByStudentType.Count(), 8 + n * 4].SetStyle(style);
                                    }
                                    if (m == educationNameList.Count - 1)
                                    {
                                        //总计
                                        var zongjiCount = gGradeYearList2.Where(x => x.CampusID == campus.CampusID && x.CollegeID == college.CollegeID
                                        && x.StandardID == standard.StandardID).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                        ws.Cells[allRow + dbListByStudentType.Count(), 4 + studentTypeListByEducation.Count * 5].Value = zongjiCount.ToString();
                                        ws.Cells[allRow + dbListByStudentType.Count(), 4 + studentTypeListByEducation.Count * 5].SetStyle(style);
                                    }
                                }
                            }

                            allRow += standardGradeYearCount + 1;
                        }

                        var collegeList = collegeTotalList.Where(x => x.CollegeID == college.CollegeID).ToList();
                        var gradeYearByCollegeList = collegeList.Select(x => x.GradeYear).Distinct().ToList();
                        for (int s = 0; s < educationNameList.Count(); s++)
                        {
                            var education = educationNameList[s];
                            //var collegeTotalListByEducation = collegeList.Where(x => x.EducationID == education.EducationID).ToList();
                            var studentTypeListByEducation = studentTypeList.Where(x => x.EducationID == education.EducationID).ToList();
                            for (int t = 0; t < studentTypeListByEducation.Count(); t++)
                            {
                                var studentType = studentTypeListByEducation[t];
                                var collegeTotalListByStudentType = collegeList.Where(x => x.EducationID == education.EducationID && x.StudentType == studentType.StudentType).ToList();
                                for (int u = 0; u < collegeTotalListByStudentType.Count(); u++)
                                {
                                    var db = collegeTotalListByStudentType[u];
                                    if (t == 0)
                                    {
                                        ws.Cells[allRow + u, 3].Value = db.GradeYear;
                                        var collegeYearRange = ws.Cells.CreateRange(allRow + u, 2, 1, 2);
                                        collegeYearRange.Merge();
                                        collegeYearRange.ApplyStyle(style, commentFlag);
                                    }
                                    //男
                                    ws.Cells[allRow + u, 4 + t * 4].Value = db.StudentMaleCount.ToString();
                                    ws.Cells[allRow + u, 4 + t * 4].SetStyle(style);
                                    //allSchoolMaleCount += db.StudentMaleCount;
                                    //女
                                    ws.Cells[allRow + u, 5 + t * 4].Value = db.StudentFemaleCount.ToString();
                                    ws.Cells[allRow + u, 5 + t * 4].SetStyle(style);
                                    //allSchoolFemaleCount += gGradeYearList[i].StudentFemaleCount;
                                    //总
                                    ws.Cells[allRow + u, 6 + t * 4].Value = (db.StudentMaleCount + db.StudentFemaleCount).ToString();
                                    ws.Cells[allRow + u, 6 + t * 4].SetStyle(style);
                                    //班级数
                                    ws.Cells[allRow + u, 7 + t * 4].Value = db.ClassmajorCount.ToString();
                                    ws.Cells[allRow + u, 7 + t * 4].SetStyle(style);
                                    //allSchoolClassCount += gGradeYearList[i].ClassmajorCount;
                                    if (t == studentTypeListByEducation.Count - 1)
                                    {
                                        //小计
                                        var xiaojiCount = collegeList.Where(x => x.EducationID == education.EducationID && x.GradeYear == db.GradeYear).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                        ws.Cells[allRow + u, 8 + t * 4].Value = xiaojiCount.ToString();
                                        ws.Cells[allRow + u, 8 + t * 4].SetStyle(style);
                                    }
                                    if (s == educationNameList.Count - 1)
                                    {
                                        //总计
                                        var zongjiCount = collegeList.Where(x => x.GradeYear == db.GradeYear).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                        ws.Cells[allRow + u, 4 + studentTypeListByEducation.Count * 5].Value = zongjiCount.ToString();
                                        ws.Cells[allRow + u, 4 + studentTypeListByEducation.Count * 5].SetStyle(style);
                                    }
                                }
                                if (t == 0)
                                {
                                    ws.Cells[allRow + collegeTotalListByStudentType.Count(), 3].Value = "合计";
                                    var collegehejiRange = ws.Cells.CreateRange(allRow + collegeTotalListByStudentType.Count(), 2, 1, 2);
                                    collegehejiRange.Merge();
                                    collegehejiRange.ApplyStyle(style, commentFlag);
                                }
                                //男
                                ws.Cells[allRow + collegeTotalListByStudentType.Count(), 4 + t * 4].Value = collegeTotalListByStudentType.Sum(x => x.StudentMaleCount).ToString();
                                ws.Cells[allRow + collegeTotalListByStudentType.Count(), 4 + t * 4].SetStyle(style);
                                //allSchoolMaleCount += db.StudentMaleCount;
                                //女
                                ws.Cells[allRow + collegeTotalListByStudentType.Count(), 5 + t * 4].Value = collegeTotalListByStudentType.Sum(x => x.StudentFemaleCount).ToString();
                                ws.Cells[allRow + collegeTotalListByStudentType.Count(), 5 + t * 4].SetStyle(style);
                                //allSchoolFemaleCount += gGradeYearList[i].StudentFemaleCount;
                                //总
                                ws.Cells[allRow + collegeTotalListByStudentType.Count(), 6 + t * 4].Value = collegeTotalListByStudentType.Sum(x => x.StudentFemaleCount + x.StudentFemaleCount).ToString();
                                ws.Cells[allRow + collegeTotalListByStudentType.Count(), 6 + t * 4].SetStyle(style);
                                //班级数
                                ws.Cells[allRow + collegeTotalListByStudentType.Count(), 7 + t * 4].Value = collegeTotalListByStudentType.Sum(x => x.ClassmajorCount).ToString();
                                ws.Cells[allRow + collegeTotalListByStudentType.Count(), 7 + t * 4].SetStyle(style);
                                //allSchoolClassCount += gGradeYearList[i].ClassmajorCount;
                                if (t == studentTypeListByEducation.Count - 1)
                                {
                                    //小计
                                    var xiaojiCount = collegeList.Where(x => x.EducationID == education.EducationID).Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                    ws.Cells[allRow + collegeTotalListByStudentType.Count(), 8 + t * 4].Value = xiaojiCount.ToString();
                                    ws.Cells[allRow + collegeTotalListByStudentType.Count(), 8 + t * 4].SetStyle(style);
                                }
                                if (s == educationNameList.Count - 1)
                                {
                                    //总计
                                    var zongjiCount = collegeList.Sum(x => x.StudentMaleCount + x.StudentFemaleCount);
                                    ws.Cells[allRow + collegeTotalListByStudentType.Count(), 4 + studentTypeListByEducation.Count * 5].Value = zongjiCount.ToString();
                                    ws.Cells[allRow + collegeTotalListByStudentType.Count(), 4 + studentTypeListByEducation.Count * 5].SetStyle(style);
                                }
                            }
                        }
                        allRow += gradeYearByCollegeList.Count() + 1;
                        ws.Cells[startRow, 1].Value = college.CollegeName;
                        var collegeRange = ws.Cells.CreateRange(startRow, 1, collegeRowCount.Value, 1);
                        collegeRange.Merge();
                        collegeRange.ApplyStyle(style, commentFlag);
                        ws.Cells[startRow + collegeRowCount.Value + 1, 1].Value = college.CollegeName + "合计";
                        var collegeTotalRange = ws.Cells.CreateRange(startRow + collegeRowCount.Value, 1, gradeYearByCollegeList.Count() + 1, 1);
                        collegeTotalRange.Merge();
                        collegeTotalRange.ApplyStyle(style, commentFlag);
                        campusRowCout += collegeRowCount + gradeYearByCollegeList.Count() + 1;
                    }
                    ws.Cells[campusStartRow, 0].Value = campus.CampusName;
                    var campusRange = ws.Cells.CreateRange(campusStartRow, 0, campusRowCout.Value, 1);
                    campusRange.Merge();
                    campusRange.ApplyStyle(style, commentFlag);

                }


                ws.Cells[rowCount, colCount].Value = "合计";

                var hejiRange = ws.Cells.CreateRange(1, colCount, 3, 1);

                hejiRange.Merge();

                hejiRange.ApplyStyle(style, commentFlag);

                //var gGradeYear = dtGradeYear.GroupBy(x => x.Field<int>("GradeYear")).ToList();

                //校区
                ws.Cells[4, 0].Value = "校区";
                var xiaoquRange = ws.Cells.CreateRange(4, 0, gradeYearCount + 1, 1);
                xiaoquRange.Merge();
                xiaoquRange.ApplyStyle(style, commentFlag);
                //全校
                ws.Cells[4, 1].Value = "全校";
                var quanxiaoRange = ws.Cells.CreateRange(4, 1, gradeYearCount + 1, 2);
                quanxiaoRange.Merge();
                quanxiaoRange.ApplyStyle(style, commentFlag);
                //全校总计
                ws.Cells[4 + gradeYearCount, 3].Value = "总计";
                var quanxiaoHejiRange = ws.Cells.CreateRange(4 + gradeYearCount, 3, 1, 1);
                quanxiaoHejiRange.Merge();
                quanxiaoHejiRange.ApplyStyle(style, commentFlag);

                ws.AutoFitColumns();
                wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

                return ms;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public MemoryStream StudentStatisticsPDF(int? InSchoolStatusID)
        {
            try
            {
                ReportHelper rh = new ReportHelper();
                MemoryStream ms = new MemoryStream();
                StyleFlag commentFlag = new StyleFlag();
                commentFlag.Borders = true;
                commentFlag.Font = true;
                commentFlag.VerticalAlignment = true;
                commentFlag.HorizontalAlignment = true;

                //string sql1 = rh.GetReportSql("EducationScheduling/University.sql");
                string sql2 = rh.GetReportSql("StudentManage/StudentStatisticsData.sql");

                List<SqlParameter> sParams = new List<SqlParameter>();

                if (InSchoolStatusID.HasValue)
                {
                    sParams.Add(new SqlParameter("@INSchoolStatus", InSchoolStatusID));
                }
                else
                {
                    sParams.Add(new SqlParameter("@INSchoolStatus", DBNull.Value));
                }

                var ds = this.UnitOfWork.QuerySQL(sql2, sParams);
                var wk = rh.GetReportTemple("StudentManage/StudentStatisticsData.xlsx");
                var ws = wk.Worksheets[0];
                var style = rh.BuildBorderStyle(wk);

                var table = DateTableHelper.DataTableToList<StudentStatisticsDataView>(ds.Tables[0]);
                var title = table.FirstOrDefault().unicersityName + table.FirstOrDefault().schoolCode + "年级专业学籍统计表";


                var classNoList = table.Select(x => x.classNo).Distinct().ToList();
                var collegeList = table.Select(x => x.collegeName).Distinct().ToList();
                var listByClassNo = table.GroupBy(x => new { x.collegeName, x.StandardsName, x.year, x.classNo }).ToList();

                ws.Cells[0, 0].Value = title;
                var titleRange = ws.Cells.CreateRange(0, 0, 1, classNoList.Count + 4);
                var titlestyle = rh.BuildBorderStyleAndSize(wk);
                titleRange.Merge();
                StyleFlag titleFlag = new StyleFlag();
                titleFlag.Borders = true;
                titleFlag.Font = true;
                titleFlag.VerticalAlignment = true;
                titleFlag.HorizontalAlignment = true;
                titleRange.ApplyStyle(titlestyle, titleFlag);
                ws.Cells[1, 1].Value = "系所函授站";
                ws.Cells[1, 1].SetStyle(style);
                ws.Cells[1, 2].Value = "院系所专业";
                ws.Cells[1, 2].SetStyle(style);
                ws.Cells[1, 3].Value = "年级";
                ws.Cells[1, 3].SetStyle(style);
                ws.Cells[1, 3 + classNoList.Count()].Value = "总计";
                ws.Cells[1, 3 + classNoList.Count()].SetStyle(style);
                for (int i = 0; i < classNoList.Count(); i++)
                {
                    ws.Cells[1, 3 + i + 1].Value = classNoList[i] + "班";
                    ws.Cells[1, 3 + i + 1].SetStyle(style);
                }
                for (int i = 0; i < collegeList.Count(); i++)
                {
                    var college = collegeList[i];
                    var standardList = table.Where(x => x.collegeName == college).Select(x => x.StandardsName).Distinct().ToList();
                    for (int j = 0; j < standardList.Count(); j++)
                    {
                        var standard = standardList[j];
                        var yearList = table.Where(x => x.collegeName == college && x.StandardsName == standard).Select(x => x.year).Distinct().ToList();
                        //ws.Cells[2 + j, 2].Value = standard;
                        //var standardRange = ws.Cells.CreateRange(2, 2, 1, 2);
                        //standardRange.Merge();
                        //standardRange.ApplyStyle(style, commentFlag);
                        for (int k = 0; k < yearList.Count(); k++)
                        {
                            var year = yearList[k];
                            var listByYearAndClassNo = listByClassNo.Where(x => x.Key.year == year).Select(x => new { x.Key.classNo, StudentCount = x.Sum(y => y.StudentCount) }).ToList();
                            ws.Cells[2 + k, 3].Value = year;
                            ws.Cells[2 + k, 3].SetStyle(style);
                            for (int m = 0; m < classNoList.Count(); m++)
                            {
                                var classNo = classNoList[m];
                                var listByClass = listByYearAndClassNo.Where(x => x.classNo == classNo).ToList();
                                ws.Cells[2 + k, 3 + m + 1].Value = listByClass.Sum(y => y.StudentCount);
                                ws.Cells[2 + k, 3 + m + 1].SetStyle(style);
                            }
                        }
                    }
                }

                wk.Save(ms, Aspose.Cells.SaveFormat.Pdf);

                return ms;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 获取空格和当行最大行数
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="weekDay"></param>
        /// <param name="firstCol"></param>
        /// <param name="lineEnd"></param>
        /// <param name="maxRow">最大行数</param>
        /// <returns>空格</returns>
        private Cell GetLastCell(Worksheet ws, int weekDay, int firstCol, int lineEnd, ref int maxRow)
        {
            int curRow = 0;
            int curCol = weekDay == 0 ? 7 : weekDay;

            curCol += firstCol;

            while (ws.Cells[curRow + lineEnd, curCol].Value != null)
            {
                curRow++;

            }

            if (maxRow < curRow)
            {
                maxRow = curRow;
            }


            return ws.Cells[curRow + lineEnd, curCol];
        }

        /// <summary>
        /// 时间段横向计算定位
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="weekDay"></param>
        /// <param name="dayNum"></param>
        /// <param name="addCol">原始偏量+时间段偏量</param>
        /// <param name="lineEnd"></param>
        /// <param name="maxRow"></param>
        /// <returns></returns>
        private Cell GetCrossTimeCell(Worksheet ws, int weekDay, int dayNum, int addCol, int lineEnd, ref int maxRow)
        {
            int curRow = 0;

            var dicWeeks = GetDicWeeks();

            int curCol = dicWeeks.FindIndex(w => w.Item1 == weekDay) * dayNum;

            curCol += addCol;

            while (ws.Cells[curRow + lineEnd, curCol].Value != null)
            {
                curRow++;

            }

            if (maxRow < curRow)
            {
                maxRow = curRow;
            }


            return ws.Cells[curRow + lineEnd, curCol];
        }
        private List<Tuple<int, string>> GetDicWeeks()
        {

            List<Tuple<int, string>> dic = new List<Tuple<int, string>>();

            dic.Add(new Tuple<int, string>(1, "星期一"));
            dic.Add(new Tuple<int, string>(2, "星期二"));
            dic.Add(new Tuple<int, string>(3, "星期三"));
            dic.Add(new Tuple<int, string>(4, "星期四"));
            dic.Add(new Tuple<int, string>(5, "星期五"));
            dic.Add(new Tuple<int, string>(6, "星期六"));
            dic.Add(new Tuple<int, string>(0, "星期日"));

            return dic;
        }
    }
}