using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using EMIS.ViewModel;
using EMIS.Web.Controls;
using EMIS.CommonLogic.ScoreManage;
using Bowin.Web.Controls.Mvc;
using EMIS.ViewModel.ScoreManage;
using EMIS.Utility;
using System.Dynamic;
using EMIS.Entities;
using Bowin.Common.Mvc;
using EMIS.ViewModel.CacheManage;
using Bowin.Common.JSON;
using Bowin.Common.Utility;
using Bowin.Common.Data;
using Bowin.Common.Linq;
using System.IO;
using EMIS.CommonLogic.CalendarManage;
using System.Data;
using EMIS.CommonLogic.SystemServices;
using EMIS.CommonLogic.DQPSystem;
using EMIS.ViewModel.DQPSystem;
using System.Text;
using Aspose.Cells;

namespace EMIS.Web.Controllers.ScoreManage
{
    [Authorization]
    public class ScoreController : Controller
    {
        public Lazy<IScoreServices> scoreServices { get; set; }

        public Lazy<IFinalExaminationServices> finalExaminationServices { get; set; }

        public Lazy<IScoreParameterSettingServices> scoreParameterSettingServices { get; set; }
        public Lazy<IScoreParamterCollegeSettingServices> ScoreParamterCollegeSettingServices { get; set; }

        public Lazy<ICreditFormulaServices> creditFormulaServices { get; set; }

        public Lazy<IGradePointFormulaServices> gradePointFormulaServices { get; set; }

        public Lazy<IScoreFormulaServices> scoreFormulaServices { get; set; }
        public Lazy<ISchoolYearServices> SchoolYearServices { get; set; }

        public IExaminationSuspensionServices ExaminationSuspensionServices { get; set; }
        public IExaminationExemptionServices ExaminationExemptionServices { get; set; }
        public INotPassStudentServices notPassStudentServices { get; set; }
        public IMisconductServices MisconductServices { get; set; }

        public IImportScoreServices ImportScoreServices { get; set; }
        public IParameterServices ParameterServices { get; set; }

        public ISOCScoreServices SOCScoreServices { get; set; }
        
        /// <summary>
        /// 报表
        /// </summary>
        /// <param name="FinalExaminationID"></param>
        /// <returns></returns>
        public ActionResult Report(Guid? FinalExaminationID)
        {
            ViewBag.FinalExaminationID = FinalExaminationID;
            return View();
        }

        /// <summary>
        /// 多报表
        /// </summary>
        /// <param name="FinalExaminationID"></param>
        /// <returns></returns>
        public ActionResult ScoreReport(Guid? FinalExaminationID)
        {
            ViewBag.FinalExaminationID = FinalExaminationID;
            return View();
        }

        /// <summary>
        /// 成绩录入页面
        /// </summary>
        /// <returns></returns>
        public ActionResult List()
        {
            var curUser = EMIS.Utility.FormValidate.CustomPrincipal.Current;   //获取当前登录人
            ViewBag.UserName = curUser.Name;
            var schoolYear = SchoolYearServices.Value.GetSchoolYearIsCurrent(true);
            ViewBag.SchoolYearID = schoolYear == null ? "-1" : schoolYear.SchoolyearID.ToString();//默认当前学年
            return View();
        }
        [HttpPost]
        public ActionResult List(QueryParamsModel pararms)
        {
            ConfiguretView configuretView = ConfiguretExtensions.GetConfiguretermsView(pararms);
            var collegeID = pararms.getExtraGuid("CollegeComboGrid");
            var departmentID = pararms.getExtraGuid("DepartmentComboGrid");
            var coursematerialID = pararms.getExtraGuid("CoursematerialComboGrid");
            var courseTypeID = pararms.getExtraInt("DictionaryCourseType") == DropdownList.SELECT_ALL ? null : pararms.getExtraInt("DictionaryCourseType");
            var examsCategoryID = pararms.getExtraInt("DictionaryExamsCategory") == DropdownList.SELECT_ALL ? null : pararms.getExtraInt("DictionaryExamsCategory");
            var schoolyearID = pararms.getExtraGuid("SchoolyearDropdown");
            var approvalStatus = pararms.getExtraInt("DictionaryScoreState") == DropdownList.SELECT_ALL ? null : pararms.getExtraInt("DictionaryScoreState");
            var gradeYearID = pararms.getExtraInt("DictionaryGrade") == DropdownList.SELECT_ALL ? null : pararms.getExtraInt("DictionaryGrade");
            var isCheck = "true";//只显示录入人为登录人的信息
            return Json(scoreServices.Value.GetFinalExaminationViewGrid(configuretView, collegeID, departmentID, schoolyearID, coursematerialID, courseTypeID, examsCategoryID, approvalStatus, gradeYearID, isCheck, (int)pararms.page, (int)pararms.rows));
        }

        /// <summary>
        /// 成绩录入页面
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <returns></returns>
        public ActionResult Edit(Guid? finalExaminationID)
        {
            ScoreEditView scoreEditView = new ScoreEditView();
            if (finalExaminationID.HasValue)
            {
                scoreEditView = new ScoreEditView(finalExaminationServices.Value.GetFinalExaminationView(finalExaminationID));
                scoreEditView.ResultType = scoreServices.Value.GetScoreType(finalExaminationID);
            }
            var scoreParameterSetting = scoreParameterSettingServices.Value.GetScoreParameterSettingList(finalExaminationID);
            if (scoreParameterSetting != null)
            {
                scoreEditView.ScoreFormulaID = scoreEditView.ScoreFormulaID ?? scoreParameterSetting.ScoreFormulaID;
                scoreEditView.CreditFormulaID = scoreEditView.CreditFormulaID ?? scoreParameterSetting.CreditFormulaID;
                scoreEditView.GradePointFormulaID = scoreEditView.GradePointFormulaID ?? scoreParameterSetting.GradePointFormulaID;
                scoreEditView.IsTotalFormula = scoreParameterSetting.IsTotalFormula;
                scoreEditView.IsCreditFormula = scoreParameterSetting.IsCreditFormula;
                scoreEditView.IsGradePointFormula = scoreParameterSetting.IsGradePointFormula;
                scoreEditView.CustomScoreFormula = scoreFormulaServices.Value.GetCustomerScoreFormula();
                scoreEditView.ExamsStateSetting = scoreServices.Value.GetExamsStateSettingList();
            }
            scoreEditView.NormalExamsID = scoreServices.Value.GetNormalExamsID();
            scoreEditView.ExaminationSuspensonID = scoreServices.Value.GetExaminationSuspensionID();
            scoreEditView.ExaminationExemptionID = scoreServices.Value.GetExaminationExemptionID();
            scoreEditView.MisconductIDList = scoreServices.Value.GetMisconductIDList();

            var collegeScoreParameterSetting = ScoreParamterCollegeSettingServices.Value.GetScoreParameterCollegeSettingView(scoreEditView.CollegeID, scoreEditView.GradeYearID);
            if (collegeScoreParameterSetting != null)
            {
                scoreEditView.FixScoreTypeID = collegeScoreParameterSetting.ScoreTypeID;
            }
            scoreEditView.ScoreDigitCount = ParameterServices.GetParameterValue<int>(CF_ParameterType.ScoreDigitCount) ?? 1;
            
            return View(scoreEditView);
        }

        /// <summary>
        /// 成绩提交
        /// </summary>
        /// <param name="finalExaminationView"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult Submit(ScoreEditView finalExaminationView)
        {
            try
            {
                var dgScoreDetailList = DataGrid.GetTableData<StudentScoreInputView>("dgScoreDetailList");
                //因为成绩保存和提交调用同样的方法，参数传1表示成绩提交
                scoreServices.Value.Submit(finalExaminationView, dgScoreDetailList);
                return Json(new ReturnMessage()
                {
                    IsSuccess = true,
                    Message = "提交成功。"
                });
            }
            catch (Exception ex)
            {
                return Json(new ReturnMessage()
                {
                    IsSuccess = false,
                    Message = "提交失败，原因：" + ex.Message 
                });
            }
        }
        /// <summary>
        /// 申请重录页面
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <returns></returns>
        public ActionResult RebutSubmit(Guid? finalExaminationID)
        {
            ScoreEditView scoreEditView = new ScoreEditView();
            if (finalExaminationID.HasValue)
            {
                scoreEditView = new ScoreEditView(finalExaminationServices.Value.GetFinalExaminationView(finalExaminationID));
            }
            var scoreParameterSetting = scoreParameterSettingServices.Value.GetScoreParameterSettingList(finalExaminationID);
            if (scoreParameterSetting != null)
            {
                scoreEditView.ScoreFormulaID = scoreEditView.ScoreFormulaID ?? scoreParameterSetting.ScoreFormulaID;
                scoreEditView.CreditFormulaID = scoreEditView.CreditFormulaID ?? scoreParameterSetting.CreditFormulaID;
                scoreEditView.GradePointFormulaID = scoreEditView.GradePointFormulaID ?? scoreParameterSetting.GradePointFormulaID;
                scoreEditView.IsTotalFormula = scoreParameterSetting.IsTotalFormula;
                scoreEditView.IsCreditFormula = scoreParameterSetting.IsCreditFormula;
                scoreEditView.IsGradePointFormula = scoreParameterSetting.IsGradePointFormula;
                scoreEditView.CustomScoreFormula = scoreFormulaServices.Value.GetCustomerScoreFormula();
                scoreEditView.ExamsStateSetting = scoreServices.Value.GetExamsStateSettingList();
            }
            scoreEditView.NormalExamsID = scoreServices.Value.GetNormalExamsID();
            scoreEditView.ExaminationSuspensonID = scoreServices.Value.GetExaminationSuspensionID();
            scoreEditView.ExaminationExemptionID = scoreServices.Value.GetExaminationExemptionID();
            scoreEditView.MisconductIDList = scoreServices.Value.GetMisconductIDList();
            scoreEditView.ScoreDigitCount = ParameterServices.GetParameterValue<int>(CF_ParameterType.ScoreDigitCount) ?? 1;
            return View(scoreEditView);
        }


        [HttpPost]
        public ActionResult RebutSubmit(string UserID, Guid? finalExaminationID)
        {
            try
            {
                List<Guid?> list = new List<Guid?>();
                for (int i = 0; i < UserID.Split(',').Length; i++)
                {
                    if (!string.IsNullOrEmpty(UserID.Split(',')[i]))
                    {
                        Guid FinalExaminationID = new Guid(UserID.Split(',')[i]);
                        list.Add(FinalExaminationID);
                    }
                }
                scoreServices.Value.Rebut(list, finalExaminationID);
                return Json(new ReturnMessage()
                {
                    IsSuccess = true,
                    Message = "申请重录学生成绩成功，待审核通过后，可重新录入成绩。"
                });
            }
            catch (Exception ex)
            {
                return Json(new ReturnMessage()
                {
                    IsSuccess = true,
                    Message = "申请重录学生成绩失败，原因：" + ex.Message + "！"
                });
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult ScoreSave(ScoreEditView finalExaminationView)
        {
            try
            {
                var scoreDetailList = DataGrid.GetTableData<StudentScoreInputView>("dgScoreDetailList");
                //因为成绩保存和提交调用同样的方法，参数传null表示成绩保存
                scoreServices.Value.ScoreSave(finalExaminationView, scoreDetailList);
                return Json(new ReturnMessage()
                {
                    IsSuccess = true,
                    Message = "保存成功。"
                });
            }
            catch (Exception ex)
            {
                return Json(new ReturnMessage()
                {
                    IsSuccess = true,
                    Message = "保存失败，原因：" + ex.Message
                });
            }
        }
        /// <summary>
        /// 重置
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult Delete(Guid? finalExaminationID)
        {
            try
            {
                scoreServices.Value.ScoreResettingDelete(finalExaminationID);
                return Json("成功");
            }
            catch (Exception ex)
            {
                return Json("重置失败，原因：" + ex.Message);
            }
        }

        [HttpPost]
        public ActionResult GetStudentScoreInputView(Guid? finalExaminationID)
        {
            var queryStudent = scoreServices.Value.GetStudentScoreInputViewGrid(finalExaminationID);
            return Json(queryStudent);
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult Excel()
        {
            NpoiExcelHelper neh = new NpoiExcelHelper();
            ConfiguretView configuretView = ConfiguretExtensions.GetConfiguretermsView(null);
            var isCheck = Request.Form["isCheck"] == null ? "true" : Request.Form["isCheck"];
            var collegeID = Request.Form["CollegeComboGrid"].ParseStrTo<Guid>();
            var departmentID = Request.Form["DepartmentComboGrid"].ParseStrTo<Guid>();
            var coursematerialID = Request.Form["CoursematerialComboGrid"].ParseStrTo<Guid>();
            var courseTypeID = Request.Form["DictionaryCourseType"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryCourseType"].ParseStrTo<int>();
            var examsCategoryID = Request.Form["DictionaryExamsCategory"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryExamsCategory"].ParseStrTo<int>();
            var schoolyearID = Request.Form["SchoolyearDropdown"].ParseStrTo<Guid>();
            var approvalStatus = Request.Form["DictionaryScoreState"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryScoreState"].ParseStrTo<int>();
            var gradeYearID = Request.Form["DictionaryGrade"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryGrade"].ParseStrTo<int>();
            var dt = scoreServices.Value.GetFinalExaminationViewList(configuretView, collegeID, departmentID, schoolyearID, coursematerialID, courseTypeID, examsCategoryID, approvalStatus, gradeYearID, isCheck).Select(x => new
            {
                x.SchoolyearCode,
                x.GradeYearID,
                x.CourseCode,
                x.CourseName,
                x.CourseTypeName,
                x.ExamsCategoryName,
                x.CreatorUserName,
                x.EntryDeadlineTime,
                x.ClassName,
                x.SAPunlogStudentCount,
                x.StudentCount,
                x.ApprovalStatusName,
                x.Remarks
            }).ToTable();
            string[] liststring = { "学年学期", "年级", "课程代码", "课程名称", "课程类型", "考试性质","录入人", "录入截止日期", "录入班级",
                                      "未录入学生数", "学生数", "状态", "审核说明" };
            neh.Export(dt, liststring, "成绩录入信息");
            return RedirectToAction("MsgShow", "Common", new
            {
                msg = "导出成功！",
                url = Url.Action("List").AddMenuParameter()
            });
        }
        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="errorFile"></param>
        /// <returns></returns>
        public ActionResult Import()
        {
            return View(new ExcelImportResult());
        }

        [HttpPost]
        public ActionResult Export(Guid? finalExaminationID)
        {
            NpoiExcelHelper neh = new NpoiExcelHelper();
            var queryStudent = scoreServices.Value.GetStudentScoreInputViewGrid(finalExaminationID);
            var scoreTypes = DictionaryHelper.GetDictionaryValue(DictionaryItem.CF_ScoreType);
            var dt = new DataTable();
            var columns = new List<string> { "学号", "姓名", "考试性质" };
            foreach (var item in scoreTypes)
            {
                columns.Add(item.Name);
            }
            columns.ForEach(x => dt.Columns.Add(x));
            foreach (var scoreView in queryStudent.rows)
            {
                var dataRow = dt.NewRow();

                dataRow["学号"] = scoreView.LoginID;
                dataRow["姓名"] = scoreView.UserName;
                dataRow["考试性质"] = scoreView.ExamsCategoryName;
                foreach (var item in scoreTypes)
                {
                    dataRow[item.Name] = scoreView.ScoreDetail[item.Value.ToString()].Score;
                }

                dt.Rows.Add(dataRow);
            }
            neh.Export(dt, columns.ToArray(), "学生成绩信息");
            return RedirectToAction("MsgShow", "Common", new
            {
                msg = "导出成功！",
                url = Url.Action("Import").AddMenuParameter()
            });
        }

        [HttpPost]
        public ActionResult Import(Guid? finalExaminationID)
        {
            try
            {
                var file = Request.Files.Get("file");
                var accept = new[] { ".xls" };
                var p = Path.GetExtension(file.FileName);

                if (!accept.Contains(p))
                {
                    throw new Exception("只允许上传xls格式的Excel文件！");
                }

                string sourceWebPath = FileUploadHelper.UploadFile(file);
                var sourcePhysicalPath = Server.MapPath(sourceWebPath);

                var result = scoreServices.Value.ScoreImport(sourcePhysicalPath, finalExaminationID);

                return View(result);
            }
            catch (Exception ex)
            {
                return RedirectToAction("MsgShow", "Common", new
                {
                    WindowID = Request["WindowID"],
                    msg = "操作失败，原因：" + ex.Message,
                    url = Url.Action("Edit").AddMenuParameter()
                });
            }
        }
        private string GetScoreFormula(Guid? scoreFormulaID)
        {
            string reulst = string.Empty;
            ER_ScoreFormula scoreFormula = scoreFormulaServices.Value.GetScoreFormula(scoreFormulaID);
            var scoreType = IdNameExt.GetDictionaryItem(DictionaryItem.CF_ScoreType.ToString());
            if (scoreFormula != null)
            {
                reulst += "{\"ScoreFormulaID\":\"" + scoreFormula.ScoreFormulaID + "\",";
                foreach (var st in scoreType)
                {
                    ER_ScoreFormulaDetail scoreFormulaDetail = scoreFormula.ER_ScoreFormulaDetail.ToList().Where(x => x.ScoreType == st.Value).FirstOrDefault();
                    if (scoreFormulaDetail != null)
                        reulst += "\"Formula_" + st.Value + "\":\"" + scoreFormulaDetail.Percentage + "\",";
                    else
                        reulst += "\"Formula_" + st.Value + "\":\"0\",";
                }
                reulst = reulst.Substring(0, reulst.Length - 1) + "}";
            }
            return reulst;
        }
        private string GetCreditFormula(Guid? CreditFormulaID)
        {
            string reulst = string.Empty;
            ER_CreditFormula creditFormula = creditFormulaServices.Value.GetCreditFormula(CreditFormulaID);
            if (creditFormula != null)
            {
                ER_CreditFormula erCreditFormula = new ER_CreditFormula();
                erCreditFormula.CreditFormulaID = creditFormula.CreditFormulaID;
                erCreditFormula.Name = creditFormula.Name;
                erCreditFormula.Scores = creditFormula.Scores;
                reulst = erCreditFormula.ToJson();
            }
            return reulst;
        }
        private string GradePointFormula(Guid? GradePointFormulaID)
        {
            string reulst = string.Empty;
            ER_GradePointFormula gradePointFormula = gradePointFormulaServices.Value.GetGradePointFormula(GradePointFormulaID);
            if (gradePointFormula != null)
            {
                GradePointView gradePointFormulaView = new GradePointView();
                gradePointFormulaView.GradePointFormulaID = gradePointFormula.GradePointFormulaID;
                gradePointFormulaView.Name = gradePointFormula.Name;
                gradePointFormulaView.GradePointlimit = gradePointFormula.GradePointlimit;
                gradePointFormulaView.GradePointFloor = gradePointFormula.GradePointFloor;
                reulst = gradePointFormulaView.ToJson();
            }
            return reulst;
        }

        public ActionResult NotPassStudent()
        {
            var schoolYear = SchoolYearServices.Value.GetSchoolYearIsCurrent(true);
            ViewBag.SchoolYearID = schoolYear == null ? "-1" : schoolYear.Years.ToString();//默认当前学年
            return View();
        }
        [HttpPost]
        public ActionResult NotPassStudent(QueryParamsModel pararms)
        {
            ConfiguretView configuretView = ConfiguretExtensions.GetConfiguretermsView(pararms);
            var campusID = pararms.getExtraGuid("CampusDropdown");
            var years = pararms.getExtraInt("SchoolyearDictionaryDropDown") == DropdownList.SELECT_ALL ? null : pararms.getExtraInt("SchoolyearDictionaryDropDown");
            var standardID = pararms.getExtraInt("StandardDictionaryDropDown") == DropdownList.SELECT_ALL ? null : pararms.getExtraInt("StandardDictionaryDropDown");
            var classMajorID = pararms.getExtraGuid("ClassmajorDropdown");
            return Json(notPassStudentServices.GetNotPassStudentGrid(configuretView, campusID, years, standardID, classMajorID, (int)pararms.page, (int)pararms.rows));
        }

        [HttpPost]
        public ActionResult SetScoreBySOCScore(Guid? finalExaminationID)
        {
            try
            {
                var socScoreList = SOCScoreServices.GetScoreBySOCScore(finalExaminationID);
                return Json(new ReturnMessage<List<SOCCourseScoreView>>()
                {
                    IsSuccess = true,
                    Message = "同步成功，共同步" + socScoreList.Count + "条记录。",
                    Data = socScoreList
                });
            }
            catch (Exception ex)
            {
                return Json(new ReturnMessage()
                {
                    IsSuccess = true,
                    Message = "同步失败：" + ex.Message
                });
            }
        }

        [HttpPost]
        public ActionResult StudentResult(List<Guid?> finalExaminationIDList)
        {
            NpoiExcelHelper neh = new NpoiExcelHelper();
            ConfiguretView configuretView = ConfiguretExtensions.GetConfiguretermsView(null);
            var isCheck = Request.Form["isCheck"] == null ? "true" : Request.Form["isCheck"];
            var collegeID = Request.Form["CollegeComboGrid"].ParseStrTo<Guid>();
            var departmentID = Request.Form["DepartmentComboGrid"].ParseStrTo<Guid>();
            var coursematerialID = Request.Form["CoursematerialComboGrid"].ParseStrTo<Guid>();
            var courseTypeID = Request.Form["DictionaryCourseType"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryCourseType"].ParseStrTo<int>();
            var examsCategoryID = Request.Form["DictionaryExamsCategory"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryExamsCategory"].ParseStrTo<int>();
            var schoolyearID = Request.Form["SchoolyearDropdown"].ParseStrTo<Guid>();
            var approvalStatus = Request.Form["DictionaryScoreState"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryScoreState"].ParseStrTo<int>();
            var gradeYearID = Request.Form["DictionaryGrade"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryGrade"].ParseStrTo<int>();
            var finalExaminationList = scoreServices.Value.GetFinalExaminationViewList(configuretView, collegeID, departmentID, schoolyearID, coursematerialID, courseTypeID, examsCategoryID, approvalStatus, gradeYearID, null, finalExaminationIDList).ToList();
            var scoreFormulaList = scoreServices.Value.GetScoreFormulaList();
            AsposeExcelHelper excel = new AsposeExcelHelper();
            List<PageItem> pageList = new List<PageItem>();
            List<CellItem> cellItems = new List<CellItem>();



            foreach (var finalExamination in finalExaminationList)
            {
                var scoreFormula = scoreFormulaList.FirstOrDefault(x => x.ScoreFormulaID == finalExamination.ScoreFormulaID);
                CellItem cell = new CellItem();
                cell.Row = 1;
                cell.Col = 1;
                cell.Value = "学时：" + finalExamination.TotalHours.ToString();
                cellItems.Add(cell);
                CellItem cell1 = new CellItem();
                cell1.Row = 1;
                cell1.Col = 3;
                cell1.Value = finalExamination.ExamsDatetime == null ? "":finalExamination.ExamsDatetime.Value.ToLongDateString();
                cellItems.Add(cell1);
                CellItem cell2 = new CellItem();
                cell2.Row = 1;
                cell2.Col = 9;
                cell2.Value = DateTime.Now.ToShortDateString();
                cellItems.Add(cell2);
                CellItem cell3 = new CellItem();
                cell3.Row = 1;
                cell3.Col = 11;
                cell3.Value = scoreFormula == null ? "" : scoreFormula.Name;
                cellItems.Add(cell3);
                CellItem cell4 = new CellItem();
                cell4.Row = 2;
                cell4.Col = 1;
                cell4.Value = "系部：" + finalExamination.CollegeName;
                cellItems.Add(cell4);
                CellItem cell5 = new CellItem();
                cell5.Row = 2;
                cell5.Col = 5;
                cell5.Value = finalExamination.ClassName;
                cellItems.Add(cell5);
                CellItem cell6 = new CellItem();
                cell6.Row = 2;
                cell6.Col = 11;
                cell6.Value = finalExamination.ExamsCategoryName;
                cellItems.Add(cell6);
                var studentsScoreList = scoreServices.Value.GetStudentScoreInputViewGrid(finalExamination.FinalExaminationID).rows.ToList();
                var leftList = studentsScoreList.Where(x => x.No % 2 == 1).ToList();
                var rightList = studentsScoreList.Where(x => x.No % 2 == 0).ToList();
                string[][] leftvs = leftList.Select((data, i) => new string[]{
                    data.No.ToString(),
                    data.LoginID,
                    data.UserName,
                    data.ScoreDetail.FirstOrDefault(x => x.Value.ScoreTypeID == (int)CF_ScoreType.Peacetime).Value.Score.ToString(),
                    data.ScoreDetail.FirstOrDefault(x => x.Value.ScoreTypeID == (int)CF_ScoreType.Technique).Value.Score.ToString(),
                    data.ScoreDetail.FirstOrDefault(x => x.Value.ScoreTypeID == (int)CF_ScoreType.Theoretical).Value.Score.ToString(),
                    data.TotalScore.ToString(),
                    "",
                    "",
                    "",
                    "",
                    "",
                    "",
                    "",
                    ""
                }).ToArray();
                string[][] rightvs = rightList.Select((data, i) => new string[]{
                    "",
                    "",
                    "",
                    "",
                    "",
                    "",
                    "",
                    "",
                    data.No.ToString(),
                    data.LoginID,
                    data.UserName,
                    data.ScoreDetail.FirstOrDefault(x => x.Value.ScoreTypeID == (int)CF_ScoreType.Peacetime).Value.Score.ToString(),
                    data.ScoreDetail.FirstOrDefault(x => x.Value.ScoreTypeID == (int)CF_ScoreType.Technique).Value.Score.ToString(),
                    data.ScoreDetail.FirstOrDefault(x => x.Value.ScoreTypeID == (int)CF_ScoreType.Theoretical).Value.Score.ToString(),
                    data.TotalScore.ToString()
                }).ToArray();
                for (var i = 0; i < rightvs.Count(); i++)
                {
                    for (var j = 8; j < leftvs[i].Length; j++)
                    {
                        leftvs[i][j] = rightvs[i][j];
                    }
                }
                int fen90 = 0;
                int fen80 = 0;
                int fen70 = 0;
                int fen60 = 0;
                int fen0 = 0;
                fen90 = studentsScoreList.Where(x => x.TotalScore >= 90).Count();
                fen80 = studentsScoreList.Where(x => x.TotalScore >= 80 && x.TotalScore < 90).Count();
                fen70 = studentsScoreList.Where(x => x.TotalScore >= 70 && x.TotalScore < 80).Count();
                fen60 = studentsScoreList.Where(x => x.TotalScore >= 60 && x.TotalScore < 70).Count();
                fen0 = studentsScoreList.Where(x => x.TotalScore <= 60).Count();
                CellItem cell7 = new CellItem();
                cell7.Row = 6;
                cell7.Col = 2;
                cell7.Value = "90分以上：" + fen90.ToString() + "        80-90分：" + fen80.ToString() + "        70-80分：" + fen70.ToString() + "        60-70分：" + fen60.ToString() + "        60分以下：" + fen0.ToString();
                cellItems.Add(cell7);

                pageList.Add(new PageItem
                {
                    CellItemList = cellItems,
                    ListItemList = new List<ListItem>
                    {
                        new ListItem(){ StartRow=4,StartColumn=0,EndColumn=14,Values=leftvs },
                    }
                });
            }
            var newExcelName = Guid.NewGuid().ToString() + DateTime.Now.Ticks.ToString() + ".xlsx";
            FileInfo file = new FileInfo(Server.MapPath("~/Content/TemplateFile" + Path.DirectorySeparatorChar + "studentResult.xlsx"));
            file.CopyTo(Server.MapPath("~/Content/TemplateFile" + Path.DirectorySeparatorChar + newExcelName));
            FileInfo newfile = new FileInfo(Server.MapPath("~/Content/TemplateFile" + Path.DirectorySeparatorChar + newExcelName));
            var stream = new FileStream(Server.MapPath("~/Content/TemplateFile" + Path.DirectorySeparatorChar + newExcelName), FileMode.Open);
            var excelNewStream = excel.ExportToTemplate(stream, pageList);
            var fileName = ExcelToPDF.ExportToFile(excelNewStream, Const.LOCAL_SETTING_UPLOAD_FILEPATH);
            var url = HttpContext.Request.Url.Scheme + "://" + HttpContext.Request.Url.Host + ":" + HttpContext.Request.Url.Port + Url.Content("~/").TrimEnd('/');
            //var host = HttpContext.Request.Url;
            var fileUrl = url + fileName.Substring(1, fileName.Length - 1);
            stream.Close();
            newfile.Delete();
            return Json(new ReturnMessage()
            {
                IsSuccess = true,
                Message = fileUrl,
            });
        }


        [HttpPost]
        public ActionResult ReportTest()
        {

            //ExcelForPDF.Worksheet 
            AsposeExcelHelper excel = new AsposeExcelHelper();
            var pageList = new List<PageItem>();

            var stream = new FileStream(Server.MapPath("~/Content/TemplateFile" + Path.DirectorySeparatorChar + "scorePDF.xlsx"), FileMode.Open);
            var excelNewStream = excel.ExportToTemplate(stream, pageList);
            var fileName = ExcelToPDF.ExportToFile(excelNewStream, Const.LOCAL_SETTING_UPLOAD_FILEPATH);
            var url = HttpContext.Request.Url.Scheme + "://" + HttpContext.Request.Url.Host + ":" + HttpContext.Request.Url.Port + Url.Content("~/").TrimEnd('/');
            //var host = HttpContext.Request.Url;
            var fileUrl = url + fileName.Substring(1, fileName.Length - 1);
            return Json(new ReturnMessage()
            {
                IsSuccess = true,
                Message = fileUrl,
            });
                //File(Server.MapPath(fileName), "application/x-pdf", "测试" + DateTime.Now.ToString("yyyyMMdd") + ".pdf");
            
        }
    }
    
}