LIOT/EMIS.Web/Controllers/ScoreManage/ExaminationScoreController.cs

using Bowin.Common.Data;
using Bowin.Common.Utility;
using Bowin.Web.Controls.Mvc;
using EMIS.CommonLogic.ExamManage;
using EMIS.CommonLogic.ScoreManage;
using EMIS.Utility;
using EMIS.ViewModel;
using EMIS.ViewModel.ScoreManage;
using EMIS.Web.Controls;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Data;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Reflection;

namespace EMIS.Web.Controllers.ScoreManage
{
    [Authorization]
    public class ExaminationScoreController : Controller
    {
        //
        // GET: /ExaminationScore/

        public IExaminationScoreServices examinationScoreServices { get; set; }
        public ILevelSettingSubjectServices levelSettingSubjectServices { get; set; }
        public CommonLogic.CalendarManage.ISchoolYearServices schoolYearServices { get; set; }
        public IExaminationBatchServices examinationBatchServices { get; set; }
        public CommonLogic.ExaminationApply.IExaminationTypeServices examinationTypeServices { get; set; }
        public IProjectFeeServices projectFeeServices { get; set; }


        public ActionResult List()
        {
            return View();
        }

        [HttpPost]
        public ActionResult List(QueryParamsModel pararms)
        {
            ConfiguretView configuretView = ConfiguretExtensions.GetConfiguretermsView(pararms);
            var schoolyearID = pararms.getExtraGuid("ddlSchoolyear");
            var examinationBatchID = pararms.getExtraGuid("cgExaminationBatch");
            var examinationTypeID = pararms.getExtraGuid("cgExaminationType");
            var examinationProjectID = pararms.getExtraGuid("cgExaminationProject");
            var schoolAreaID = pararms.getExtraInt("ddlSchoolArea") == DropdownList.SELECT_ALL ? null : pararms.getExtraInt("ddlSchoolArea");
            var collegeID = pararms.getExtraGuid("cgCollege");

            var year = pararms.getExtraInt("ddlYear") == Bowin.Web.Controls.Mvc.DropdownList.SELECT_ALL ? null : pararms.getExtraInt("ddlYear");
            var standardID = pararms.getExtraInt("DictionaryStandard") == Bowin.Web.Controls.Mvc.DropdownList.SELECT_ALL ? null : pararms.getExtraInt("DictionaryStandard");
            var classmajorID = pararms.getExtraGuid("cgClassmajor");
            if (configuretView.Attribute == Bowin.Web.Controls.Mvc.DropdownList.SELECT_ALL.ToString()) configuretView.Attribute = "";
            return base.Json(examinationScoreServices.GetExaminationScoreViewGrid(configuretView, schoolyearID, examinationBatchID, examinationTypeID, examinationProjectID,
               schoolAreaID, collegeID, year, standardID, classmajorID, (int)pararms.page, (int)pararms.rows));
        }

        /// <summary>
        /// 复制新增
        /// </summary>
        /// <param name="examinationScoreID"></param>
        /// <returns></returns>
        public ActionResult CopyAdd(Guid examinationScoreID)
        {
            ExaminationScoreView examinationScoreView = new ExaminationScoreView();
            examinationScoreView = examinationScoreServices.GetExaminationScoreView(examinationScoreID);

            return View("Edit", examinationScoreView);
        }

        /// <summary>
        /// 复制新增
        /// </summary>
        /// <param name="studentListView"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult CopyAdd(ExaminationScoreView examinationScoreView)
        {
            examinationScoreView.ExaminationScoreID = Guid.Empty;

            return this.Edit(examinationScoreView);
        }

        /// <summary>
        /// 新增/修改
        /// </summary>
        /// <param name="examinationScoreID"></param>
        /// <returns></returns>
        [HttpGet]
        public ActionResult Edit(Guid? examinationScoreID)
        {
            ExaminationScoreView examinationScoreView = new ExaminationScoreView();
            if (examinationScoreID.HasValue && examinationScoreID != Guid.Empty)
            {
                examinationScoreView = examinationScoreServices.GetExaminationScoreView(examinationScoreID);
            }
            return View(examinationScoreView);
        }

        /// <summary>
        /// 新增/修改
        /// </summary>
        /// <param name="studentListView"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult Edit(ExaminationScoreView examinationScoreView)
        {
            try
            {
                examinationScoreServices.Edit(examinationScoreView);
                return Json(new ReturnMessage()
                {
                    IsSuccess = true,
                    Message = "保存成功。"
                });
            }
            catch (Exception ex)
            {
                return Json(new ReturnMessage()
                {
                    IsSuccess = false,
                    Message = "保存失败，原因：" + ex.Message
                });
            }
        }

        [HttpPost]
        public ActionResult Delete(string examinationScoreIDs)
        {
            try
            {
                var examinationScoreIDList = examinationScoreIDs.Split(',').Select(x => (Guid?)new Guid(x)).ToList();

                examinationScoreServices.Delete(examinationScoreIDList);
                return base.Json("删除成功");
            }
            catch (Exception ex)
            {
                string mge = ex.Message;
                return base.Json("删除失败，原因：" + mge + "！");
            }
        }

        public ActionResult Excel()
        {
            NpoiExcelHelper neh = new NpoiExcelHelper();
            ConfiguretView configuretView = ConfiguretExtensions.GetConfiguretermsView(null);

            var schoolyearID = Request.Form["ddlSchoolyear"].ParseStrTo<Guid>();
            var examinationBatchID = Request.Form["ddlExaminationBatch"].ParseStrTo<Guid>();
            var examinationTypeID = Request.Form["ddlExaminationType"].ParseStrTo<Guid>();
            var examinationProjectID = Request.Form["ddlExaminationProject"].ParseStrTo<Guid>();
            var schoolAreaID = Request.Form["ddlSchoolArea"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["ddlSchoolArea"].ParseStrTo<int>();
            var collegeID = Request.Form["cgCollege"].ParseStrTo<Guid>();

            var year = Request.Form["ddlYear"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["ddlYear"].ParseStrTo<int>();
            var standardID = Request.Form["DictionaryStandard"].ParseStrTo<int>() == DropdownList.SELECT_ALL ? null : Request.Form["DictionaryStandard"].ParseStrTo<int>();
            var classmajorID = Request.Form["cgClassmajor"].ParseStrTo<Guid>();

            var examinationScoreIDString = Request.Form["SelectedID"];
            var examinationScoreIDList = examinationScoreIDString.Split(',').Where(x => !string.IsNullOrEmpty(x)).Select(x => (Guid?)new Guid(x)).ToList();

            var dt = examinationScoreServices.GetExaminationScoreViewList(configuretView, schoolyearID, examinationBatchID, examinationTypeID, examinationProjectID, schoolAreaID,
                collegeID, year, standardID, classmajorID, examinationScoreIDList)
                    .Select(x => new
                    {
                        x.SchoolyearCode,
                        x.ExaminationBatchName,
                        x.ExaminationTypeName,
                        x.ExaminationProjectName,
                        x.ExaminationSubjectName,
                        x.SchoolAreaName,
                        x.CollegeName,
                        x.SchoolyearNumID,
                        x.StandardName,
                        x.ClassmajorName,
                        x.UserName,
                        x.CertificatesTypeName,
                        x.IDNumber,
                        x.Score,
                        x.Expire,
                        x.Remark
                    }).ToTable();

            string[] liststring = { "学年学期", "考试批次", "考试类型", "考试项目", "科目考核项", "校区", "院系", "年级", "专业", "班级", "姓名", "证件类型", "证件号码", "成绩", "有效日期", "备注" };

            neh.Export(dt, liststring, "项目成绩" + DateTime.Now.ToString("yyyyMMdd"));

            return Json(new ReturnMessage()
            {
                IsSuccess = true,
                Message = "导出成功。"
            });
        }

        [HttpPost]
        public ActionResult GetSubjectViewByProjectID(QueryParamsModel pararms)
        {
            ConfiguretView configuretView = ConfiguretExtensions.GetConfiguretermsView(pararms);
            var examinationProjectID = pararms.getExtraGuid("cgExaminationProject");
            var query = examinationScoreServices.GetSubjectByProjectGrid(configuretView, examinationProjectID, (int)pararms.page, (int)pararms.rows);
            return base.Json(query);
        }

        [HttpPost]
        public ActionResult SubjectDropDownList(DropdownListBindType? bindType, Guid? examinationProjectID)
        {
            DropdownListBindType dbt = bindType == null ? DropdownListBindType.SelectAll : bindType.Value;
            var projectQuery = examinationScoreServices.GetSubjectByProjectList(new ConfiguretView());
            if (examinationProjectID.HasValue)
            {
                projectQuery = projectQuery.Where(x => x.ExaminationProjectID == examinationProjectID);
            }
            else
            {
                var emptyList = new List<DropdownListItem>();
                Bowin.Web.Controls.Mvc.DropdownList.FormatDropdownItemList(dbt, emptyList);
                return Json(emptyList);
            }

            List<DropdownListItem> list = projectQuery.Select(x => new { x.ExaminationSubjectID, x.ExaminationSubject }).Distinct()
                .OrderBy(x => x.ExaminationSubjectID)
                .Select(x => new DropdownListItem { Text = x.ExaminationSubject, Value = x.ExaminationSubjectID.ToString() }).ToList();

            Bowin.Web.Controls.Mvc.DropdownList.FormatDropdownItemList(dbt, list);

            return base.Json(list);
        }

        /// <summary>
        /// Excel导入
        /// </summary>
        /// <param name="errorFile"></param>
        /// <param name="operationTips"></param>
        /// <returns></returns>
        [HttpGet]
        public ActionResult Import(string errorFile, string operationTips)
        {
            ViewBag.ErrorFile = errorFile;
            if (string.IsNullOrEmpty(operationTips))
            {
                operationTips = "";
            }
            ViewBag.operationTips = operationTips;
            return View();
        }

        public object GetCellValueByType(ICell cell)
        {

            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return "";
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.ERROR:
                    return cell.ErrorCellValue;
                case CellType.FORMULA:
                    return cell.CellFormula;
                case CellType.NUMERIC:
                    return cell.NumericCellValue;
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.Unknown:
                    return null;
                default:
                    return "";
            }
        }

        public List<T> convertToList<T>(DataTable dt) where T : new()
        {
            //定义集合
            List<T> ts = new List<T>();
            //获得此模型的类型
            Type type = typeof(T);
            //定义一个临时的变量
            string tempName = "";
            //遍历datatable中所有数据行
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                //获得此模型的公共属性
                PropertyInfo[] propertys = t.GetType().GetProperties();
                //遍历所有属性
                foreach (PropertyInfo pi in propertys)
                {
                    //将此属性赋值给临时变量
                    tempName = pi.Name;
                    //检查datatable是否包含此列
                    if (dt.Columns.Contains(tempName))
                    {
                        //判断此属性是否有setter，这个啥意思呢，就是我们的实体层的{get;set;}如果我们的实体有了set方法，就说明可以赋值！
                        if (!pi.CanWrite) continue;
                        {
                            //取值  
                            object value = dr[tempName];
                            if (value != DBNull.Value)
                                pi.SetValue(t, value, null);
                        }
                    }
                }
                //对象添加到泛型集合中
                ts.Add(t);
            }
            return ts;
        }

        [HttpPost]
        public ActionResult Import(HttpPostedFileBase file)
        {
            try
            {
                var schoolyearID = Request.Form["ddlSchoolyear"].ParseStrTo<Guid>();
                var examinationBatchID = Request.Form["ddlExaminationBatch"].ParseStrTo<Guid>();
                var examinationTypeID = Request.Form["ddlExaminationType"].ParseStrTo<Guid>();
                var examinationProjectID = Request.Form["ddlExaminationProject"].ParseStrTo<Guid>();
                var subjectList = examinationScoreServices.GetSubjectByProject(examinationProjectID);
                ////1、要把文件加载到程序中
                ////2、获取sheet
                ////3、获取所有行
                ////4、获取行中所有的数据
                ////5、将获取到的数据，按照原有的数据格式，存储到一个数据容器中
                string sourceWebPath = FileUploadHelper.UploadFile(file);
                var sourcePhysicalPath = Server.MapPath(sourceWebPath);

                var excel = new NpoiExcelHelper();
                DataTable dtnew = excel.Import(sourcePhysicalPath);
                //创建一个数据表，用来存储 从Excel中导入的数据
                DataTable dt = new DataTable();

                //得到Excel文件地址   是包含路径的fileName
                string excelFilePath = sourcePhysicalPath;
                //创建一个工作簿   2007版本及以上  使用XSSFWorkbook   ，2007以前的版本 使用 使用HSSFWorkbook
                IWorkbook workbook = new HSSFWorkbook(file.InputStream);
                //创建一个sheet  ，用来存储Excel中的一个Sheet
                //ISheet sheet = workbook.GetSheet("Sheet1");//根据sheet的名字获取
                ISheet sheet = workbook.GetSheetAt(0);//根据sheet的索引，索引从0开始
                                                      //创建一个Row   存储第一行有效数据，通常 有可能是标题行
                IRow row = sheet.GetRow(sheet.FirstRowNum);
                //处理标题行， 遍历标题行中所有的单元格（Cell），每个单元格的值 即为列名
                for (int i = 0; i < row.Cells.Count; i++)
                {
                    var item = row.Cells[i];
                    object cellValue = this.GetCellValueByType(item);
                    string columnName = "";//DataTable的列明
                    if (cellValue == null || cellValue.ToString() == "")
                    {
                        columnName = "column" + dt.Columns.Count;//默认名
                    }
                    else
                    {
                        columnName = cellValue.ToString();//具体值单元格值  作为名字
                    }
                    dtnew.Columns[i].ColumnName = columnName;
                }
                //foreach (ICell item in row.Cells)
                //{
                //    //往DataTable中添加Excel中对应的列的值
                //    object cellValue = this.GetCellValueByType(item);
                //    string columnName = "";//DataTable的列明
                //    if (cellValue == null || cellValue.ToString() == "")
                //    {
                //        columnName = "column" + dt.Columns.Count;//默认名
                //    }
                //    else
                //    {
                //        columnName = cellValue.ToString();//具体值单元格值  作为名字
                //    }
                //    dtnew.Columns[0].ColumnName = columnName;
                //    dt.Columns.Add(columnName);
                //}
                //遍历所有的有效数据 并将有效数据 添加到DataTable
                //for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                //{
                //    IRow dataRow = sheet.GetRow(i);
                //    DataRow dr = dt.NewRow();// 创建与dt表具有相同架构的新数据行（Row）
                //    for (int j = 0; j < dataRow.Cells.Count; j++)
                //    {
                //        dr[j] = this.GetCellValueByType(dataRow.Cells[j]);
                //    }
                //    dt.Rows.Add(dr);//向dt数据表中添加一个数据行
                //}
                //将导入的数据表 绑定到dataGridView1
                //dataGridView1.DataSource = dt;



                if (!NpoiExcelHelper.GetIsCompatible(file.FileName))
                {
                    throw new Exception("只允许上传xls和xlsx格式的Excel文件！");
                }
               
                List<string> errcellheader = new List<string>();
                

                #region 表单头
                Dictionary<string, string> cellheader = new Dictionary<string, string>
                {
                   { "SchoolyearCode", "学年学期" },
                   { "ExaminationBatchName", "考试批次" },
                   { "ExaminationProjectName", "考试项目" },
                   { "UserName", "姓名" },
                   { "IDNumber", "证件号码" },
                };
                foreach(var subject in subjectList)
                {
                    var code = ViewModel.Cache.IdNameExt.GetDictionaryItem(DictionaryItem.EX_ExaminationSubject.ToString()).Where(x => x.Value == subject.ExaminationSubjectID).Select(x => x.Code).FirstOrDefault();
                    cellheader.Add(code, subject.ExaminationSubject);
                }
                cellheader.Add("ExpireImport", "有效日期");
                cellheader.Add("Remark", "备注");
                #endregion

                foreach (var dic in cellheader)
                {
                    errcellheader.Add(dic.Value);
                }
                errcellheader.Add("错误信息");
                StringBuilder errorMsg = new StringBuilder(); // 错误信息
                //string sourceWebPath = FileUploadHelper.UploadFile(file);
                //var sourcePhysicalPath = Server.MapPath(sourceWebPath);
                List <ExaminationScoreView> errList = new List<ExaminationScoreView>();
                DataTable errdt = new DataTable();
                List<ExaminationScoreView> dataList = new List<ExaminationScoreView>();
                int errCount = 0;
                int OkCount = 0;
                examinationScoreServices.Import(cellheader, out OkCount, out errdt, out errCount, dtnew, sourcePhysicalPath, schoolyearID, examinationBatchID, examinationTypeID, examinationProjectID);
                System.IO.File.Delete(sourcePhysicalPath);//删除本地缓存文件
                if (errCount > 0)
                {
                    //获取错误数据文件路径
                    string errorWebPath = WriteErrorFile(excel, errcellheader, errdt);
                        //string.Format("{0}", NpoiExcelHelper
                        //.EntityListToExcel2003(cellheader, , "项目成绩导入失败文件", sourcePhysicalPath));

                    ViewBag.ErrorFile = errorWebPath;

                    string Errinfo = string.Format("提示：{0}条信息导入成功，{1}条信息导入失败,点击查看。",
                            OkCount, errCount);

                    ViewBag.operationTips = Errinfo;

                    return RedirectToAction("MsgShow", "Common", new
                    {
                        WindowID = "none",
                        msg = Errinfo,
                        url = Url.Action("Import").AddMenuParameter() + "&errorFile=" + errorWebPath + "&operationTips=" + Errinfo + "&WindowID=" + Request["WindowID"]
                    });
                }
                else
                {
                    string successInfo = string.Format("提示：{0}条信息导入成功", OkCount);

                    return RedirectToAction("MsgShow", "Common", new
                    {
                        WindowID = Request["WindowID"],
                        msg = successInfo,
                        url = Url.Action("Import").AddMenuParameter() + "&WindowID=" + Request["WindowID"]
                    });
                }
            }
            catch (Exception ex)
            {
                return RedirectToAction("MsgShow", "Common", new
                {
                    WindowID = Request["WindowID"],
                    msg = "导入失败，原因：" + ex.Message + "！",
                    url = Url.Action("Import").AddMenuParameter() + "&WindowID=" + Request["WindowID"]
                });
            }
        }

        private string WriteErrorFile(NpoiExcelHelper excel, IEnumerable<string> errorCellheader, DataTable errorTable)
        {
            var errorExcelBuff = excel.ExportMemoryStream(errorTable, "", errorCellheader.ToArray());
            var logicalPath = Const.LOCAL_SETTING_UPLOAD_FILEPATH + "/" + Guid.NewGuid().ToString() + ".xls";
            var physicalPath = System.Web.HttpContext.Current.Server.MapPath(logicalPath);
            var fs = System.IO.File.Create(physicalPath);
            fs.Write(errorExcelBuff, 0, errorExcelBuff.Length);
            fs.Flush();
            fs.Close();

            return UrlHelper.GenerateContentUrl(logicalPath, System.Web.HttpContext.Current.Request.RequestContext.HttpContext);
        }

        public ActionResult GetTemplateByProject(Guid? schoolyearID, Guid? examinationBatchID, Guid? examinationTypeID, Guid? examinationProjectID)
        {
            NpoiExcelHelper neh = new NpoiExcelHelper();
            var schoolyearCode = schoolYearServices.GetSchoolYear(schoolyearID).Code;
            var batchName = examinationBatchServices.GetExamBatch(examinationBatchID).Name;
            var typeName = examinationTypeServices.GetExaminationTypeViewInfo(examinationTypeID).Name;
            var projectName = projectFeeServices.GetProject(examinationProjectID).Name;
            var subjectList = examinationScoreServices.GetSubjectByProject(examinationProjectID);

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn());
            dt.Columns.Add(new DataColumn());
            //dt.Columns.Add(new DataColumn());
            dt.Columns.Add(new DataColumn());
            dt.Columns.Add(new DataColumn());
            dt.Columns.Add(new DataColumn());
            foreach (var subject in subjectList)
            {
                dt.Columns.Add(new DataColumn());
            }
            dt.Columns.Add(new DataColumn());
            dt.Columns.Add(new DataColumn());

            DataRow row = dt.NewRow();
            row[0] = schoolyearCode;
            row[1] = batchName;
            row[2] = projectName;

            dt.Rows.Add(row);

            List<string> idlist = new List<string>();
            idlist.Add("学年学期");
            idlist.Add("考试批次");
            idlist.Add("考试项目");
            idlist.Add("姓名");
            idlist.Add("证件号码");
            foreach (var subject in subjectList)
            {
                idlist.Add(subject.ExaminationSubject);
            }
            idlist.Add("有效日期");
            idlist.Add("备注");
            //var dt = new ConvertToDataTable

            //string[] liststring = { "学年学期", "考试批次", "考试类型", "考试项目", "姓名", "证件号码", "成绩", "有效日期", "备注" };
            string[] liststring = idlist.ToArray();
            neh.Export(dt, liststring, projectName + "项目成绩模板" + DateTime.Now.ToString("yyyyMMdd"));

            return RedirectToAction("MsgShow", "Common", new
            {
                WindowID = Request["WindowID"],
                msg = "导出模板成功",
                url = Url.Action("Import").AddMenuParameter()
            });
        }
    }
}