using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using VueWebApi.DLL.BLL;
using VueWebApi.Models;
using VueWebApi.Tools;

namespace VueWebApi.Controllers
{
    [RoutePrefix(prefix: "api/ImportExcel")]
    [ControllerGroup("Excel导入模板验证", "在线接口")]
    public class ImportExcelController : ApiController
    {
        //定义全局信息返回变量
        ToMessage mes = new ToMessage();


        #region [Excel导入模板下载]
        /// <summary>
        /// Excel导入模板下载
        /// </summary>
        /// <param name="FileCode">文件编码</param>
        /// <returns></returns>
        [Route(template: "DownLoadExcel")]
        [HttpGet]
        public HttpResponseMessage DownLoadExcel(string FileCode = null)
        {
            List<ScoreReport> list = ExcelList.ExcelData();
            list = list.Where(s => s.FileCode == FileCode).ToList();
            var filename = list[0].FileName + ".xls";
            string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"];
            var msg = fileip + "/Excel/" + filename;
            mes.code = "200";
            mes.data = msg;
            return TJson.toJson(mes);
        }
        #endregion

        #region [Excel导入文件上传、模板验证、数据量、验证]
        /// <summary>
        /// Excel导入文件上传、模板验证、数据量、验证
        /// </summary>
        /// <param name="FileCode">文件编码</param>
        /// <param name="files">上传文件</param>
        /// <returns></returns>
        [Route(template: "ExcelModelCheck")]
        [HttpPost]
        public HttpResponseMessage ExcelModelCheck()
        {

            string FileCode = HttpContext.Current.Request["FileCode"].ToString();
            HttpPostedFile files = HttpContext.Current.Request.Files[0];
            ExcelModelCheck list = new ExcelModelCheck();
            Dictionary<object, object> dList = new Dictionary<object, object>();
            list.json1 = ExcelCheckUpload(files, FileCode);  //文件上传
            if (list.json1.code == "300")
            {
                list.json1 = list.json1;
                return TJson.toJson(list);
            }
            list.json2 = ExcelCheck(files, FileCode);  //模板验证
            if (list.json2.code == "300")
            {
                list.json2 = list.json2;
                return TJson.toJson(list);
            }
            list.json3 = ExcelCheckCount(files, FileCode);  //数据量验证
            if (list.json3.code == "300")
            {
                list.json3 = list.json3;
                return TJson.toJson(list);
            }
            return TJson.toJson(list);
        }
        #endregion

        #region [Excel导入模板上传]
        /// <summary>
        /// Excel导入模板上传
        /// </summary>
        /// <param name="files">上传文件</param>
        /// <param name="FileCode">文件编码</param>
        /// <returns></returns>
        [Route(template: "ExcelCheckUpload")]
        [HttpGet]
        [HiddenApi]
        public ToMessage ExcelCheckUpload(HttpPostedFile files, string FileCode = null)
        {
            try
            {
                string savePath;
                string filename = Path.GetFileName(files.FileName);

                string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
                string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
                string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
                savePath = Path.Combine(path, filename);
                if (!FileType.Contains(fileEx))
                {
                    mes.code = "300";
                    mes.Message = "文件类型不对,上传失败！";
                    System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
                    return mes;
                }
                else if (Directory.Exists(path) == false)//如果不存在就创建file文件夹
                {
                    Directory.CreateDirectory(path); //添加文件夹
                    files.SaveAs(savePath);    //将选择的文件保存到指定文件夹下
                    mes.code = "200";
                    mes.Message = "文件上传成功！";
                }
                else if (Directory.Exists(savePath) == true)  //如果存在重名文件就提示
                {
                    System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
                    files.SaveAs(savePath);    //将选择的文件保存到指定文件夹下
                    mes.code = "200";
                    mes.Message = "文件上传成功！";
                }
                else
                {
                    files.SaveAs(savePath);    //将选择的文件保存到指定文件夹下
                    mes.code = "200";
                    mes.Message = "文件上传成功！";
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region [Excel导入模板验证]
        /// <summary>
        /// Excel导入模板验证
        /// </summary>
        /// <param name="files">上传文件</param>
        /// <param name="FileCode">文件编码</param>
        /// <returns></returns>
        [Route(template: "ExcelCheckUpload")]
        [HttpGet]
        [HiddenApi]
        public ToMessage ExcelCheck(HttpPostedFile files, string FileCode = null)
        {
            try
            {
                string savePath;

                string filename = Path.GetFileName(files.FileName);
                string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
                savePath = Path.Combine(path, filename);
                if (Directory.Exists(path) == false)//如果不存在就创建file文件夹
                {
                    Directory.CreateDirectory(path); //添加文件夹
                }
                else
                {
                    mes = ExcelCheckBLL.ExcelCheck(FileCode, savePath);
                    if (mes.code == "300") //上传模板不是指定模板
                    {
                        System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
                        return mes;
                    }
                }
                return mes;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.Message = e.Message;
            }
            return mes;
        }
        #endregion

        #region[Excel导入模板数据量验证]
        /// <summary>
        /// Excel导入模板数据量验证
        /// </summary>
        /// <param name="files">上传文件</param>
        /// <param name="FileCode">文件编码</param>
        /// <returns></returns>
        [HiddenApi]
        public ToMessage ExcelCheckCount(HttpPostedFile files, string FileCode = null)
        {
            string savePath;

            string filename = Path.GetFileName(files.FileName);
            int filesize = files.ContentLength;//获取上传文件的大小单位为字节byte
            string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
            int Maxsize = 10000 * 1024;//定义上传文件的最大空间大小为10M
            string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
            savePath = Path.Combine(path, filename);
            IWorkbook Workbook;
            DataTable table = new DataTable();
            using (FileStream fileStream = new FileStream(savePath, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                string fileExt = Path.GetExtension(savePath).ToLower();
                if (fileExt == ".xls")
                {
                    Workbook = new HSSFWorkbook(fileStream);
                }
                else if (fileExt == ".xlsx")
                {
                    Workbook = new XSSFWorkbook(fileStream);
                }
                else
                {
                    Workbook = null;
                }
            }
            //定位在第一个sheet
            ISheet sheet = Workbook.GetSheetAt(0);
            //sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);  
            //第一行为标题行
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            //int rowCount0 = sheet.FirstRowNum;
            int rowCount = sheet.LastRowNum;
            if (files == null || files.ContentLength <= 0)
            {
                mes.code = "300";
                mes.Message = "文件不能为空!";
                System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
                return mes;
            }
            mes = ImportExcel.ExcelCheckCountSum(savePath); //空数据文件验证
            if (mes.code == "300")
            {

                mes.code = "300";
                mes.Message = "文件不能为空！";
                System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
                return mes;
            }
            if (filesize >= Maxsize)
            {

                mes.code = "300";
                mes.Message = "上传文件超过10M，不能上传";
                System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
                return mes;
            }
            mes.code = "200";
            mes.Message = "数据量验证通过";

            return mes;
        }
        #endregion

        #region [Excel导入数据验证]
        /// <summary>
        /// Excel导入数据验证
        /// </summary>
        /// <param name="FileCode">文件编码</param>
        /// <param name="FileName">文件名称</param>
        /// <returns></returns>
        [Route(template: "ExcelCheckData")]
        [HttpPost]
        public HttpResponseMessage ExcelCheckData(string FileCode = null, string FileName = null)
        {
            string message = "";
            string StuCode = "";
            int count = 0;
            List<ExcelErro> list = new List<ExcelErro>();
            string savePath;
            string filename = FileName;
            string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
            savePath = Path.Combine(path, filename);
            list = ExcelCheckBLL.ExcelCheckData(FileCode, savePath, out StuCode, out message, out count);
            if (list.Count > 0)
            {
                System.IO.File.Delete(savePath);//执行IO文件删除,需引入命名空间System.IO;
            }
            Dictionary<object, object> dList = new Dictionary<object, object>();
            dList.Add("code", StuCode);
            dList.Add("Message", message);
            dList.Add("Count", count);
            dList.Add("list", list);
            return TJson.toJson(dList);
        }
        #endregion

        #region【Excel导入数据】
        /// <summary>
        /// Excel导入数据
        /// </summary>
        /// <param name="FileCode">文件编码</param>
        /// <param name="FileName">文件名称</param>
        /// <returns></returns>
        [Route(template: "ExcelImportSubmit")]
        [HttpPost]
        public HttpResponseMessage ExcelImportSubmit(string FileCode = null, string FileName = null)
        {
            var username = HttpContext.Current.Request.Cookies["admin"].Value.ToString(); //登录用户名 
            string savePath;
            string filename = FileName;
            string path = System.Web.HttpContext.Current.Server.MapPath("/InExcel/" + FileCode + "/");
            savePath = Path.Combine(path, filename);
            mes = ExcelCheckBLL.ExcelImportSubmit(FileCode, savePath, username);
            return TJson.toJson(mes);
        }
        #endregion
    }
}