JW/ServiceLogic/EMIS.CommonLogic/CalendarManage/CalendarViewServices.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Bowin.Common.Utility;
using EMIS.DataLogic.Common.CalendarManage;
using EMIS.Entities;
using EMIS.DataLogic.UniversityManage.AdministrativeOrgan;
using EMIS.ViewModel.CalendarManage;
using EMIS.ViewModel;
using Bowin.Common.Data;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.IO;
using System.Web;
using NPOI.SS.Util;

namespace EMIS.CommonLogic.CalendarManage
{
    public partial class CalendarViewServices : BaseServices, ICalendarViewServices
    {
        public SchoolYearDAL SchoolYearDAL { get; set; }
        public UniversityDAL UniversityDAL { get; set; }
        public ActivitieDAL ActivitieDAL { get; set; }
        public SCalendarDAL scalendarDAL { get; set; }
        internal static List<MonthView> GetMonths(SchoolYearView schoolYear)
        {
            if (schoolYear == null) throw new Exception("请求的学年学期不存在。");

            List<MonthView> result = new List<MonthView>();
            DateTime startDate = schoolYear.FirstWeek.Value;
            DateTime endDate = schoolYear.FirstWeek.Value.AddDays((schoolYear.WeeksNum.Value * 7) - 1);
            int spanMonths = endDate.MonthSubstract(startDate);
            int startMonth = startDate.Month;

            result.Add(new MonthView { Month = startMonth, ChineseName = startMonth.ConvertToChinese() + "月" });
            for (int i = 1; i <= spanMonths; i++)
            {
                result.Add(new MonthView { Month = startDate.AddMonths(i).Month, ChineseName = startDate.AddMonths(i).Month.ConvertToChinese() + "月" });
            }

            return result;
        }

        internal static List<WeekNumView> GetWeekNums(SchoolYearView schoolYear)
        {
            if (schoolYear == null) throw new Exception("请求的学年学期不存在。");

            List<WeekNumView> result = new List<WeekNumView>();

            for (int i = 1; i <= schoolYear.WeeksNum; i++)
            {
                result.Add(new WeekNumView { WeekNum = i, ChineseName = i.ConvertToChinese() });
            }

            return result;
        }

        internal static List<WeekDayView> GetWeekDays()
        {
            List<WeekDayView> result = new List<WeekDayView>();

            for (int i = 0; i < 6; i++)
            {
                result.Add(new WeekDayView { WeekDay = i, ChineseName = (i + 1).ConvertToChinese() });
            }
            result.Add(new WeekDayView { WeekDay = 7, ChineseName = "日" });

            return result;
        }

        internal static DateTime GetStartDate(SchoolYearView schoolYear)
        {
            if (schoolYear == null) throw new Exception("请求的学年学期不存在。");

            DateTime monday = schoolYear.FirstWeek.Value.AddDays((double)(1 - schoolYear.FirstWeek.Value.DayOfWeek));
            if (monday.Month == schoolYear.FirstWeek.Value.Month && monday.Day != 1)
            {
                monday = GetMonthStartDate(monday, schoolYear.FirstWeek.Value);
            }

            return monday;
        }

        internal static DateTime GetEndDate(SchoolYearView schoolYear)
        {
            if (schoolYear == null) throw new Exception("请求的学年学期不存在。");
            DateTime lastDate = schoolYear.FirstWeek.Value.AddDays(7 * schoolYear.WeeksNum.Value);
            DateTime sunday = lastDate.AddDays((double)(7 - lastDate.DayOfWeek));
            if (sunday.Month == lastDate.Month && sunday.Day != DateTime.DaysInMonth(lastDate.Year, lastDate.Month))
            {
                sunday = GetMonthEndDate(sunday, lastDate);
            }

            return sunday;
        }

        internal static DateTime GetMonthStartDate(DateTime curDate, DateTime startDate)
        {
            DateTime newDate = curDate.AddDays(-7);
            if (newDate.Month == startDate.Month && newDate.Day != 1)
            {
                return GetMonthStartDate(newDate, startDate);
            }
            else
            {
                return newDate;
            }
        }

        internal static DateTime GetMonthEndDate(DateTime curDate, DateTime endDate)
        {
            DateTime newDate = curDate.AddDays(7);
            if (newDate.Month == endDate.Month && newDate.Day != DateTime.DaysInMonth(endDate.Year, endDate.Month))
            {
                return GetMonthEndDate(newDate, endDate);
            }
            else
            {
                return newDate;
            }
        }

        public CalendarView GetCalendarViewBySchoolYearID(Guid schoolYearID)
        {
            var university = UniversityDAL.UniversityRepository.GetSingle(x => true);
            if (university == null)
            {
                throw new Exception("请先设置学校信息。");
            }
            var schoolYear = SchoolYearDAL.GetSchoolYearQueryable(x => x.SchoolyearID == schoolYearID).FirstOrDefault();
            var calendarView = new CalendarView();

            calendarView.StartDate = GetStartDate(schoolYear);
            calendarView.EndDate = GetEndDate(schoolYear);
            List<int> yearList = new List<int>();
            for (int i = calendarView.StartDate.Year; i <= calendarView.EndDate.Year; i++)
            {
                yearList.Add(i);
            }
            var holidays = (from a in ActivitieDAL.GetActivitiesQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE).ToList()
                .Where(x => x.ActivitiesType == (int)CF_ActivitiesType.Holiday || x.ActivitiesType == (int)CF_ActivitiesType.Festival)
                            join yl in yearList on true equals true
                            select new { Activity = a, Year = yl }
                            )
                .Select(x => new DateTime(x.Year, x.Activity.ActivitiesTimeMonth ?? 1, x.Activity.ActivitiesTimeDay ?? 1)).ToList();
            StringBuilder holidayStr = new StringBuilder();
            var holidaysList = (from a in ActivitieDAL.GetActivitiesQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE).ToList()
                  .Where(x => x.ActivitiesType == (int)CF_ActivitiesType.Holiday || x.ActivitiesType == (int)CF_ActivitiesType.Festival)
                                join yl in yearList on true equals true
                                select new { Activity = a, Year = yl}
                            ).OrderByDescending(x => x.Year).ThenByDescending(x => x.Activity.ActivitiesTimeMonth).ThenBy(x => x.Activity.ActivitiesTimeDay).ThenBy(x => x.Activity.Name).ToList();
            foreach(var h in holidaysList)
            {
                if(calendarView.StartDate.Year==h.Year){
                    if (!holidayStr.ToString().Contains(h.Activity.Name))
                    {
                        var holidayName = holidaysList.Where(x => x.Activity.Name == h.Activity.Name && calendarView.StartDate.Year == x.Year).ToList();
                        if (holidayName.Count==1)
                        {
                            holidayStr.Append(h.Activity.Name + h.Activity.ActivitiesTimeMonth + "月" + h.Activity.ActivitiesTimeDay + "日；");
                        }
                        else 
                        {
                            holidayStr.Append(h.Activity.Name + h.Activity.ActivitiesTimeMonth + "月" + h.Activity.ActivitiesTimeDay);
                        }
                    }
                    else 
                    {
                        var holiday = holidaysList.Where(x => x.Activity.Name == h.Activity.Name && calendarView.StartDate.Year == x.Year).Max(x=>x.Activity.ActivitiesTimeDay);
                        if (h.Activity.ActivitiesTimeDay == holiday)
                        {
                            holidayStr.Append("、" + h.Activity.ActivitiesTimeDay + "日；");
                        }
                        else 
                        {
                            holidayStr.Append("、" + h.Activity.ActivitiesTimeDay);
                        }
                    }
                }
            }
            holidayStr.Replace("；", "。",holidayStr.Length-1,1);
            //校历假日
            StringBuilder scalendarStr = new StringBuilder();
            var scalendarList = (from a in scalendarDAL.GetSCalendarQueryable(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE).ToList()
                                join yl in yearList on true equals true
                                select new { Activity = a, Year = yl }
                            ).OrderByDescending(x => x.Year).ThenBy(x => x.Activity.ActivitiesTime).ThenBy(x=>x.Activity.Name).ToList();
            foreach (var s in scalendarList)
            {
                if (calendarView.StartDate.Year == s.Year)
                {
                    if (!scalendarStr.ToString().Contains(s.Activity.Name))
                    {
                        var scalendarName = scalendarList.Where(x => x.Activity.Name == s.Activity.Name && calendarView.StartDate.Year == x.Year).ToList();
                        if (scalendarName.Count == 1)
                        {
                            scalendarStr.Append(s.Activity.Name + s.Activity.ActivitiesTime.Value.Month + "月" + s.Activity.ActivitiesTime.Value.Day + "日；");
                        }
                        else
                        {
                            scalendarStr.Append(s.Activity.Name + s.Activity.ActivitiesTime.Value.Month + "月" + s.Activity.ActivitiesTime.Value.Day);
                        }
                    }
                    else
                    {
                        var scalendar = scalendarList.Where(x => x.Activity.Name == s.Activity.Name && calendarView.StartDate.Year == x.Year).Max(x => x.Activity.ActivitiesTime);
                        if (s.Activity.ActivitiesTime == scalendar)
                        {
                            scalendarStr.Append("、" + s.Activity.ActivitiesTime.Value.Day + "日；");
                        }
                        else
                        {
                            scalendarStr.Append("、" + s.Activity.ActivitiesTime.Value.Day);
                        }
                    }
                }
                //if (calendarView.StartDate.Year == s.Year)
                //{
                //    if (!scalendarStr.ToString().Contains(s.Activity.Name))
                //    {
                //        var scalendarName = scalendarList.Where(x => x.Activity.Name == s.Activity.Name && calendarView.StartDate.Year == x.Year).ToList();
                //        if (scalendarName.Count == 1)
                //        {
                //            scalendarStr.Append(" "+s.Activity.ActivitiesTime.Value.Month + "月" + s.Activity.ActivitiesTime.Value.Day + "日  " + s.Activity.Name+";");
                //        }
                //        else
                //        {
                //            scalendarStr.Append(" " + s.Activity.ActivitiesTime.Value.Month + "月" + s.Activity.ActivitiesTime.Value.Day + s.Activity.Name);
                //        }
                //    }
                //    else
                //    {
                //        var scalendar = scalendarList.Where(x => x.Activity.Name == s.Activity.Name && calendarView.StartDate.Year == x.Year).Max(x => x.Activity.ActivitiesTime);
                //        if (s.Activity.ActivitiesTime == scalendar)
                //        {
                //            scalendarStr.Append("、" + s.Activity.ActivitiesTime.Value.Day + "日  " + s.Activity.Name);
                //        }
                //        else
                //        {
                //            scalendarStr.Append("、" + s.Activity.ActivitiesTime.Value.Day);
                //        }
                //    }
                //}
            }
            calendarView.UniversityName = university.Name;
            calendarView.SchoolYear = schoolYear;
            calendarView.Months = GetMonths(schoolYear);
            calendarView.WeekNums = GetWeekNums(schoolYear);
            calendarView.WeekDays = GetWeekDays();
            calendarView.Holidays = holidays;
            calendarView.HolidayStr = holidayStr.ToString();
            calendarView.ScalendarStr = scalendarStr.ToString();
            return calendarView;
        }

        public void Excel(Guid schoolYearID, string HeaderText)
        {
            string fileName = HeaderText + ".xls";

            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            if (HttpContext.Current.Request.UserAgent.ToLower().IndexOf("firefox") > -1)
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
            }
            else
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            }
            HttpContext.Current.Response.Clear();

            var view = GetCalendarViewBySchoolYearID(schoolYearID);
            //创建工作薄
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            //创建表
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            //默认宽度-高度
            sheet1.DefaultColumnWidth = 6;
            sheet1.DefaultRowHeight = 10;
            short color_BLACK = HSSFColor.BLACK.index;

            ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.THIN;
            style.BorderLeft = BorderStyle.THIN;
            style.BorderRight = BorderStyle.THIN;
            style.BorderTop = BorderStyle.THIN;
            //设置背景（根据上面的定义的颜色 进行赋值）
            //style.FillForegroundColor = color_BLACK;
            //style.FillPattern = FillPatternType.SOLID_FOREGROUND;

            //style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index;
            //居中
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            //设置字体
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;//字号
            style.SetFont(font);

            //周数前面的空列数
            var emptyWeeks = (int)Math.Ceiling((decimal)view.SchoolYear.FirstWeek.Value.Subtract(view.StartDate).Days / 7);
            //周数后面的空列数
            var emptyWeeksEnd = (int)Math.Floor((double)view.EndDate.Subtract(view.SchoolYear.FirstWeek.Value.AddDays((double)view.SchoolYear.WeeksNum * 7)).Days / 7);
            //总周数包括空列
            var totalWeeks = view.SchoolYear.WeeksNum + emptyWeeks + emptyWeeksEnd;

            //表头
            IRow firstRow = sheet1.CreateRow(0);
            firstRow.HeightInPoints = 2 * 16;
            ICell firstRowCell = firstRow.CreateCell(0);
            ICellStyle firstRowStyle = hssfworkbook.CreateCellStyle();
            firstRowStyle.BorderBottom = BorderStyle.THIN;
            firstRowStyle.BorderLeft = BorderStyle.THIN;
            firstRowStyle.BorderRight = BorderStyle.THIN;
            firstRowStyle.BorderTop = BorderStyle.THIN;
            //居中
            firstRowStyle.Alignment = HorizontalAlignment.CENTER;
            firstRowStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font1 = hssfworkbook.CreateFont();
            font1.FontHeightInPoints = 18;//字号
            font1.Boldweight = (short)FontBoldWeight.BOLD;//粗体
            firstRowStyle.SetFont(font1);
            firstRowCell.CellStyle = firstRowStyle;
            
            for (int i = 1; i < totalWeeks.Value; i++)
            {
                ICell cell = firstRow.CreateCell(i);
                cell.CellStyle = firstRowStyle;
            }
            firstRow.Cells[0].SetCellValue(view.UniversityName + view.SchoolYear.Code + "学期校历");
            //将表头合并
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, totalWeeks.Value));
            //firstRow.Cells.ForEach(x => x.CellStyle = firstRowStyle);

            //第二行
            IRow secondRow = sheet1.CreateRow(1);
            secondRow.CreateCell(0).SetCellValue("星期\\月份");
            secondRow.Cells[0].CellStyle = style;

            for (var i = 0; i < view.Months.Count; i++)
            {
                var startDate = view.StartDate;
                var beginDate = startDate.AddDays(-1);
                int weekCount = 0;
                for (var j = 0; j < totalWeeks; j++)
                {
                    var curWeekStart = beginDate.AddDays(1 + (j * 7));
                    var curWeekEnd = beginDate.AddDays((j + 1) * 7);
                    if (curWeekEnd.Month == (view.Months[i].Month))
                    {  
                        weekCount++;
                    }
                }
                int count = secondRow.LastCellNum;
                ICell secondRowCell = secondRow.CreateCell(secondRow.LastCellNum);

                secondRowCell.SetCellValue(view.Months[i].ChineseName);
                secondRowCell.CellStyle = style;
                for (int k = 1; k < weekCount; k++)
                {
                    secondRow.CreateCell(secondRow.LastCellNum);
                    secondRow.Cells[secondRow.LastCellNum - 1].CellStyle = style;
                }
                sheet1.AddMergedRegion(new CellRangeAddress(1, 1, count, count + weekCount - 1));
            }

            //中间部分
            for (int i = 0; i < 7; i++)
            {
                IRow row = sheet1.CreateRow(1 + sheet1.LastRowNum);
                ICell cell = row.CreateCell(0);
                cell.SetCellValue(view.WeekDays[i].ChineseName);
                cell.CellStyle = style;

                var rowStartDate = view.StartDate.AddDays(i);
                for (var j = 0; j < totalWeeks; j++)
                {
                    ICellStyle tmpStyle = hssfworkbook.CreateCellStyle();
                    tmpStyle.BorderBottom = BorderStyle.THIN;
                    tmpStyle.BorderLeft = BorderStyle.THIN;
                    tmpStyle.BorderRight = BorderStyle.THIN;
                    tmpStyle.BorderTop = BorderStyle.THIN;
                    tmpStyle.Alignment = HorizontalAlignment.CENTER;
                    tmpStyle.VerticalAlignment = VerticalAlignment.CENTER;
                    tmpStyle.SetFont(font);

                    ICell cells = row.CreateCell(j + 1);
                    cells.CellStyle = tmpStyle;
                    var curDate = rowStartDate.AddDays(j * 7);
                    string str = curDate.Day.ToString();

                    if (curDate < view.SchoolYear.FirstWeek ||
                        curDate > view.SchoolYear.FirstWeek.Value.AddDays((double)view.SchoolYear.WeeksNum * 7))
                    {
                        cells.CellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                        cells.CellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                    }

                    else
                    {
                        cells.CellStyle.FillForegroundColor = HSSFColor.WHITE.index;
                        cells.CellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                    }

                    if (view.Holidays.Where(x => x.Date == curDate).ToList().Count > 0)
                        str += '★';
                    cells.SetCellValue(str);
                }
            }
            //底部
            IRow bottomRow = sheet1.CreateRow(sheet1.LastRowNum + 1);
            ICell bottomCell = bottomRow.CreateCell(0);
            bottomCell.SetCellValue("周次");
            bottomCell.CellStyle = style;
            for (int k = 0; k < totalWeeks; k++)
            {
                ICell cells = bottomRow.CreateCell(k + 1);
                cells.CellStyle = style;
                if (emptyWeeks <= k && k < totalWeeks - emptyWeeksEnd)
                    cells.SetCellValue(view.WeekNums[k - emptyWeeks].ChineseName);
            }
            sheet1.SetColumnWidth(0, 10 * 256);
            //sheet1.SetColumnWidth(1, 5 * 256);
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
            HttpContext.Current.Response.End();
        }
    }
}