SYCW_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/archives/archivesCont...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using CallCenterApi.Interface.Controllers.Base;
using System.Web.Mvc;
using System.Data;
using CallCenterApi.DB;
using System.IO;
using System.Data.SqlClient;
using CallCenterApi.Interface.Models.Dto;
using CallCenterApi.Interface.Controllers.workorder;
using CallCenterApi.Interface.Models.Enum;
using CallCenter.Utility;
using CallCenterApi.Interface.Models.Filter;
using CallCenterApi.Interface.Models.Input;
using CallCenterApi.Model;
using CallCenterApi.Interface.Models.Common;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using Newtonsoft.Json;
using CallCenterApi.Common;

namespace CallCenterApi.Interface.Controllers.archives
{
    public class archivesController : BaseController
    {
        CallCenterApi.BLL.archives archivesbll = new BLL.archives();
        // GET: archives
        /// <summary>
        /// 查询档案物资表数据信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetList(int PageIndex, int PageSize,string A_archiveName)
        {
          
            
            #region 分页显示档案物资
            if (PageIndex == 0)
            {
                PageIndex = 1;
            }
            if (PageSize == 0)
            {
                PageSize = 10;
            }
            var sql = "";
            ///按名称模糊查询
            if (A_archiveName!=null)
            {
                if(A_archiveName!="")
                {
                    sql += "  and A_archiveName like '%" + A_archiveName + "%' ";
                }
               
            }
            Model.PageData<Model.archives> pageModel = new Model.PageData<Model.archives>();
            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                 " (select * from  ((SELECT ROW_NUMBER() OVER (PARTITION BY archives.id  ORDER BY Operationlog.OperationDate DESC ) AS rid,archives.*,Operationlog.OperationContent,Operationlog.OperationDate,archivestate.stateName from archives  left join Operationlog on Operationlog.archivesid=archives.id left join archivestate  on archives.A_archiveState=archivestate.id  )) t where   rid=1 ) a",// archives as a left join archivestate as s on a.A_archiveState=s.id left join Operationlog as o on a.id=o.archivesid
                 "a.id",
                 "*",//a.*,s.stateName as stateName,o.OperationContent,o.OperationDate
                 sql,
                 "ORDER BY a.id desc ",
                 PageSize,
                 PageIndex,
                 true,
                 out recordCount);
            List<Model.archives> modelList = new BLL.archives().DataTableToList(dt);
            var list = new List<Model.archives>();
            foreach (var item in modelList)
            {
                list.Add(new Model.archives
                {
                    id = item.id,
                    A_archiveCode = item.A_archiveCode,
                    A_archiveName = item.A_archiveName,
                    A_archiveState = item.A_archiveState,
                    A_archivePeople = item.A_archivePeople,
                    stateName = item.stateName,
                    OperationContent = item.OperationContent,
                    OperationDate = item.OperationDate
                });
            }
            var obj = new
            {
                rows = list,
                total = recordCount
            };
            return Content(obj.ToJson()); 
            #endregion

        }
        /// <summary>
        /// 查询是否存在该数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IfHave(int id)
        {
            var a = archivesbll.IfHave(id);
            return archivesbll.IfHave(id);
        }
        public ActionResult GetModelarchives(int id)
        {
            var model = archivesbll.GetModel(id);
            if (model == null)
                return Error("该信息不存在");
            var obj = new Model.archives
            {
                id = model.id,
                A_archiveCode=model.A_archiveCode,
                A_archiveName =model.A_archiveName,
                A_archiveState=model.A_archiveState,
                A_archivePeople =model.A_archivePeople,
                stateName = model.stateName,
                OperationContent = model.OperationContent,
                OperationDate = model.OperationDate
            };
            return Success("获取成功", obj);
        }
        /// <summary>
        /// 添加档案物资信息
        /// </summary>
        /// <param name="archive"></param>
        /// <returns></returns>
        public ActionResult AddorUpdateArchives(archivesInput archive)
        {
            
            var model = new  CallCenterApi.Model.archives();
            if (archive.isadd <= 0)
            {
                model.A_archiveCode = archive.A_archiveCode;//关联客户表中的F_CustomerCode客户编号 关联显示客户名称
                model.A_archiveName = archive.A_archiveName;//物资名称
                if (archive.A_archiveState == 0)
                {
                    model.A_archiveState = 1;//物资状态 关联物质状态表中的主键id  下拉选择    
                }
                else {
                    model.A_archiveState = archive.A_archiveState;//物资状态 关联物质状态表中的主键id  下拉选择
                }
               
                model.A_archivePeople =CurrentUser.UserData.F_UserCode; // //操作人 archive.A_archivePeople
                var archivesid = archivesbll.Add(model);
                if (archivesid > 0)
                {
                    var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                    Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;// CurrentUser.UserData.F_UserCode;//
                    Operationlogmodel.OperationDate = DateTime.Now;
                    Operationlogmodel.OperationContent = "添加了一条物资档案信息物资名称为："+ archive.A_archiveName;
                    Operationlogmodel.archivesid = archivesid;
                    AddorUpdOperationlog(Operationlogmodel);
                    return Success("添加档案成功" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                }
                else
                {
                    return Error("添加档案失败" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                }  
                
            }
                 else { 
                 model = archivesbll.GetModel(archive.id);
                 var archname = model.A_archiveName;
                 if (model == null)
                     return Error("该档案不存在");
                 //model.F_SortModel = input.F_SortModel;
                 model.A_archiveCode = archive.A_archiveCode;
                 model.A_archiveName = archive.A_archiveName;
                 model.A_archiveState = archive.A_archiveState;
                 var uarchiveid = archive.id;
                //model.A_archiveRecord = archive.A_archiveRecord;
                model.A_archivePeople = CurrentUser.UserData.F_UserCode;// CurrentUser.UserData.F_UserCode;//操作人archive.A_archivePeople
                var people = archive.A_archivePeople;
                 if (archivesbll.Update(model))
                 {
                     var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                    Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;// CurrentUser.UserData.F_UserCode;
                     Operationlogmodel.OperationDate = DateTime.Now;
                     Operationlogmodel.OperationContent = "修改了一条物资档案信息";
                     Operationlogmodel.archivesid = uarchiveid;
                     AddorUpdOperationlog(Operationlogmodel);
                     return Success("修改档案成功" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                 }
                 else {
                       return Error("修改档案失败" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                 }
                 }
        }

        /// <summary>
        ///  删除档案物资信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult DeleteQuestion(int id)
        {
            if (archivesbll.Delete(id))
            {
                var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                Operationlogmodel.OperationDate = DateTime.Now;
                Operationlogmodel.OperationContent = "删除了一条物资档案信息";
                Operationlogmodel.archivesid = id;
                AddorUpdOperationlog(Operationlogmodel);
                return Success("删除档案成功" + ",操作人：" + CurrentUser.UserData.F_UserCode); ;
            }
            else
            {
                return Error("删除档案失败!" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            }
        }
        /// <summary>
        /// 获取档案状态数据信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetArchivestate()
        {
            DataTable dt = new DataTable();
            dt = archivesbll.GetArchivestate().Tables[0];
            return Success("加载成功", dt);
        }
        /// <summary>
        /// 添加档案状态信息
        /// </summary>
        /// <param name="archive"></param>
        /// <returns></returns>
        public ActionResult Addorarchivestate(archivestateInput archivestate)
        {
          
            var model = new CallCenterApi.Model.archivestate();


            model.stateName = archivestate.stateName;//状态名
            var archivesid = archivesbll.Addarchivestate(model);
            if (archivesid > 0)
            {
                var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;//操作人
                Operationlogmodel.OperationDate = DateTime.Now;
                Operationlogmodel.OperationContent = "添加了一条物资状态信息";
               // Operationlogmodel.archivesid = archivesid;
                AddorUpdOperationlog(Operationlogmodel);
                return Success("添加成功" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            }
            else
            {
                return Error("添加失败" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            }
               
           

        }
        /// <summary>
        /// 删除物资状态信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult delarchivestate(int id)
        {
            if (archivesbll.delarchivestate(id))
            {
                var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                Operationlogmodel.OperationDate = DateTime.Now;
                Operationlogmodel.OperationContent = "删除了一条物资状态信息";
                //Operationlogmodel.archivesid = id;
                AddorUpdOperationlog(Operationlogmodel);
                return Success("删除成功" + ",操作人：" + CurrentUser.UserData.F_UserCode); ;
            }
            else
            {
                return Error("删除失败!" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            }
        }

        /// <summary>
        /// 获取外部客户操作表信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetCustomerBorrow(int PageIndex, int PageSize)
        {
            //DataTable dt = new DataTable();
            //dt = archivesbll.GetCustomerBorrow().Tables[0];
            //return Success("加载成功", dt);

            if (PageIndex == 0)
            {
                PageIndex = 1;
            }
            if (PageSize == 0)
            {
                PageSize = 10;
            }
            #region 分页显示客户操作人员的操作信息
           
            var sql = " ";
            Model.PageData<Model.customerBorrow> pageModel = new Model.PageData<Model.customerBorrow>();
            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                 " customerBorrow as c left join archives as a on c.archivesid=a.id ",
                 "c.id",
                 "c.*,a.A_archiveName as A_archiveName",
                 sql,
                 "ORDER BY c.id desc ",
                 PageSize,
                 PageIndex,
                 true,
                 out recordCount);
            var list = archivesbll.DataTableToListcustomerBorrow(dt);
            var obj = new
            {
                rows = list.Select(x => new
                {
                    id = x.id,
                    archivesid   = x.archivesid,
                    BorrowOreturn= x.BorrowOreturn,
                    BorrowDate   = x.BorrowDate,
                    BorrowPeople =   x.BorrowPeople,
                    A_archiveName=x.A_archiveName
                }),
                total = recordCount
            };
           
            return Content(obj.ToJson());
            #endregion
        }
        /// <summary>
        /// 添加与修改外部客户信息
        /// </summary>
        /// <param name="archive"></param>
        /// <returns></returns>
        public ActionResult AddorUpdateCustomerBorrow(customerBorrowInput customerBorrow)
        {
            var model = new CallCenterApi.Model.customerBorrow();
            if (customerBorrow.isadd <= 0)
            {
                ///修改物资状态为借阅中
                 //根据状态名称获取状态id
                var isBorrow = customerBorrow.BorrowOreturn.ToString();
                var stateid = 1;
                if (isBorrow.Contains("借阅"))
                {
                    stateid = archivesbll.GetStateid("借阅中");
                }
                else {
                    stateid = archivesbll.GetStateid("在库");
                }
               
                
                //根据获取到的状态id修改物资状态
                var upstate = archivesbll.updateState(Convert.ToInt32(customerBorrow.archivesid), stateid);

                ///添加客户操作信息
                model.archivesid = customerBorrow.archivesid;//物品编号
                model.BorrowOreturn = customerBorrow.BorrowOreturn;//区分借阅还是归还
                model.BorrowDate = DateTime.Now;//客户操作时间
                model.BorrowPeople = CurrentUser.UserData.F_UserCode;// CurrentUser.UserData.F_UserCode ; //操作人customerBorrow.BorrowPeople
                var archivesid = customerBorrow.archivesid;
                if (archivesbll.AddCustomerBorrow(model) > 0)
                {
                    var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                    Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                    Operationlogmodel.OperationDate = DateTime.Now;
                    Operationlogmodel.OperationContent = "添加了一条借阅归还信息";
                    Operationlogmodel.archivesid = archivesid;
                    AddorUpdOperationlog(Operationlogmodel);
                    return Success("添加成功" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                }
                else
                {
                    return Error("添加失败" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                }
            }
            else
            {
                model = archivesbll.GetCustomerModel(customerBorrow.id);
                if (model == null)
                    return Error("该信息不存在");
                //model.F_SortModel = input.F_SortModel;
                var uparchvieid = customerBorrow.archivesid;
                model.archivesid = customerBorrow.archivesid;
                model.BorrowOreturn = customerBorrow.BorrowOreturn;
                model.BorrowDate = customerBorrow.BorrowDate;
                model.BorrowPeople = CurrentUser.UserData.F_UserCode;// CurrentUser.UserData.F_UserCode;//操作人customerBorrow.BorrowPeople
                if (archivesbll.UpCustomerBorrow(model))

                {
                    var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                    Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                    Operationlogmodel.OperationDate = DateTime.Now;
                    Operationlogmodel.OperationContent = "修改了一条借阅归还信息";
                    Operationlogmodel.archivesid = uparchvieid;
                    AddorUpdOperationlog(Operationlogmodel);
                    return Success("修改成功" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                }
                else
                {
                    return Error("修改失败" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                }

            }


        }
        /// <summary>
        /// 删除一条客户操作的数据信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult DelCustomerBorrow(int id)
        {
            var model = new CallCenterApi.Model.archives();
            model = archivesbll.GetModel(id);
            var  archivesid = model.id;
            if (archivesbll.delCustomerBorrow(id))
            {
                var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                Operationlogmodel.OperationDate = DateTime.Now;
                Operationlogmodel.OperationContent = "删除了一条借阅归还信息";
                
                Operationlogmodel.archivesid = archivesid;
                AddorUpdOperationlog(Operationlogmodel);
                return Success("删除成功" + ",操作人：" + CurrentUser.UserData.F_UserCode); ;
            }
            else
            {
                return Error("删除失败!" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            }
        }


        /// <summary>
        /// 获取内部员工操作表信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetinsideOperation(int PageIndex, int PageSize)
        {
            #region 基础查询 暂时不用
            //DataTable dt = new DataTable();
            //dt = archivesbll.GetinsideOperation().Tables[0];
            //return Success("加载成功", dt); 
            #endregion
            #region 分页显示内部员工出入库的操作信息
            if (PageIndex == 0)
            {
                PageIndex = 1;
            }
            if (PageSize == 0)
            {
                PageSize = 10;
            }
            var sql = " ";
            Model.PageData<Model.insideOperation> pageModel = new Model.PageData<Model.insideOperation>();
            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                 " insideOperation as c left join archives as a on c.archivesid=a.id ",
                 "c.id",
                 "c.*,a.A_archiveName as A_archiveName",
                 sql,
                 "ORDER BY c.id desc ",
                 PageSize,
                 PageIndex,
                 true,
                 out recordCount);
            var list = archivesbll.DataTableToListinsideOperation(dt);
            var obj = new
            {
                rows = list.Select(x => new
                {
                    id = x.id,
                    archivesid      =x.archivesid,
                    Warehousing     =x.Warehousing,
                    WarehousingDate =x.WarehousingDate,
                    Operator        =x.Operator,                   
                    A_archiveName = x.A_archiveName
                }),
                total = recordCount
            };

            return Content(obj.ToJson());
            #endregion
        }
        /// <summary>
        /// 添加与修改内部员工信息
        /// </summary>
        /// <param name="archive"></param>
        /// <returns></returns>
        public ActionResult AddorUpdateinsideOperation(insideOperationInput insideOperation)
        {
            var model = new CallCenterApi.Model.insideOperation();
            if (insideOperation.isadd <= 0)
            {
                ///修改物资状态为借阅中
                //根据状态名称获取状态id
                var isBorrow = insideOperation.Warehousing.ToString();
                var stateid = 1;
                if (isBorrow.Contains("入库"))
                {
                    stateid = archivesbll.GetStateid("在库");
                }
                else
                {
                    stateid = archivesbll.GetStateid("已出库");
                }
                
                //根据获取到的状态id修改物资状态
                var upstate = archivesbll.updateState(Convert.ToInt32(insideOperation.archivesid), stateid);

                model.archivesid = insideOperation.archivesid;//物品编号
                model.Warehousing = insideOperation.Warehousing;//区分入库还是出库
                model.WarehousingDate = DateTime.Now;//操作时间
                model.Operator = CurrentUser.UserData.F_UserCode;// CurrentUser.UserData.F_UserCode; //操作人insideOperation.Operator
                var arvichid = insideOperation.archivesid;
                if (archivesbll.AddinsideOperation(model) > 0)
                {
                    var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                    Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                    Operationlogmodel.OperationDate = DateTime.Now;
                    Operationlogmodel.OperationContent = "添加了一条出入库信息";
                    Operationlogmodel.archivesid = arvichid;
                    AddorUpdOperationlog(Operationlogmodel);
                    return Success("添加成功" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                }
                else
                {
                    return Error("添加失败" + ",操作人：" + CurrentUser.UserData.F_UserCode);
                }
                    
               
            }
            model = archivesbll.GetinsideOperationModel(insideOperation.id);
            if (model == null)
                return Error("该信息不存在");
            //model.F_SortModel = input.F_SortModel;
            var archiveid = insideOperation.archivesid;
            model.archivesid = insideOperation.archivesid;
            model.Warehousing = insideOperation.Warehousing;
            model.WarehousingDate = DateTime.Now;
            model.Operator = CurrentUser.UserData.F_UserCode;// CurrentUser.UserData.F_UserCode;//操作人insideOperation.Operator
            if (archivesbll.UpinsideOperation(model))
            {
                var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                Operationlogmodel.OperationDate = DateTime.Now;
                Operationlogmodel.OperationContent = "修改了一条出入库信息";
                Operationlogmodel.archivesid = archiveid;
                AddorUpdOperationlog(Operationlogmodel);
                return Success("修改成功" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            }
            else
            {
                return Error("修改失败" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            }
                
            
        }
        /// <summary>
        /// 删除一条内部员工出入库信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult DelinsideOperation(int id)
        {
            var model = new CallCenterApi.Model.archives();
            model = archivesbll.GetModel(id);
            var archivesid = model.id;
            if (archivesbll.delinsideOperation(id))
            {
                var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                Operationlogmodel.OperationDate = DateTime.Now;
                Operationlogmodel.OperationContent = "删除了一条出入库信息";
                Operationlogmodel.archivesid = archivesid;
                AddorUpdOperationlog(Operationlogmodel);
                return Success("删除成功" + ",操作人：" + CurrentUser.UserData.F_UserCode); ;
            }
            else
            {
                return Error("删除失败!" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            }
        }
        /// <summary>
        /// 获取操作记录表信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetOperationlog(int PageIndex, int PageSize)
        {
            #region 基础查询 暂时不用
            //DataTable dt = new DataTable();
            //dt = archivesbll.GetOperationlog().Tables[0];
            //return Success("加载成功", dt); 
            #endregion
            #region 分页显示内部员工出入库的操作信息
            if (PageIndex == 0)
            {
                PageIndex = 1;
            }
            if (PageSize == 0)
            {
                PageSize = 10;
            }
            var sql = " ";
            Model.PageData<Model.Operationlog> pageModel = new Model.PageData<Model.Operationlog>();
            var recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                 " Operationlog as c left join archives as a on c.archivesid=a.id ",
                 "c.id",
                 "c.*,a.A_archiveName as A_archiveName",
                 sql,
                 "ORDER BY c.id desc ",
                 PageSize,
                 PageIndex,
                 true,
                 out recordCount);
            var list = archivesbll.DataTableToListOperationlog(dt);
            var obj = new
            {
                rows = list.Select(x => new
                {
                    id = x.id,
                    archivesid = x.archivesid,
                    Operator = x.Operator,
                    OperationContent = x.OperationContent,
                    OperationDate = x.OperationDate,
                    A_archiveName = x.A_archiveName
                    
                }),
                total = recordCount
            };

            return Content(obj.ToJson());
            #endregion
        }
        /// <summary>
        /// 添加与修改操作日志信息
        /// </summary>
        /// <param name="archive"></param>
        /// <returns></returns>
        public ActionResult AddorUpdOperationlog(OperationlogInput Operationlog)
        {
            
               
            var model = new CallCenterApi.Model.Operationlog();
            model.Operator = CurrentUser.UserData.F_UserCode;//操作人
            model.OperationContent = Operationlog.OperationContent;//操作内容
            model.OperationDate = DateTime.Now;//操作时间
            model.archivesid = Operationlog.archivesid;
            if (archivesbll.AddOperationlog(model) > 0)
                return Success("添加成功" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            return Error("添加失败" + ",操作人：" + CurrentUser.UserData.F_UserCode);
            
        }

        /// <summary>
        /// excel档案物资导入
        /// </summary>
        /// <returns></returns>
        public ActionResult ImportExcelarchives()
        {
            int userId = CurrentUser.UserData.F_UserId;
            HttpPostedFile _upFile = RequestString.GetFile("upFile");
            if (_upFile != null)
            {
                int headrow = 0;
                string filepath = "";
                string datepath = DateTime.Now.ToString("yyyyMMddHHMMss");
                string aLastName = _upFile.FileName.Substring(_upFile.FileName.LastIndexOf(".") + 1, (_upFile.FileName.Length - _upFile.FileName.LastIndexOf(".") - 1)); //扩展名
                if (aLastName != "xls" && aLastName != "xlsx")
                {
                    return Error("文件类型错误，请选择Excel文件");
                }
                string newpath = datepath + "_" + _upFile.FileName;
                if (!Directory.Exists(Server.MapPath(this.Request.ApplicationPath + "\\ExcelData")))
                {
                    Directory.CreateDirectory(Server.MapPath(this.Request.ApplicationPath + "\\ExcelData"));
                }
                filepath = this.Request.ApplicationPath + "/ExcelData/" + newpath;
                string PhysicalPath = Server.MapPath(filepath);
                _upFile.SaveAs(PhysicalPath);

                ///添加上传日志
                Model.T_Sys_Accessories model_T_Sys_Accessories = new Model.T_Sys_Accessories();
                model_T_Sys_Accessories.F_AddTime = DateTime.Now;//上传时间
                model_T_Sys_Accessories.F_FileName = _upFile.FileName;//附件名称
                model_T_Sys_Accessories.F_FileType = "exce";//附件类型
                model_T_Sys_Accessories.F_FileUrl = filepath;//附件地址
                model_T_Sys_Accessories.F_Size = _upFile.ContentLength;
                model_T_Sys_Accessories.F_UserCode = CurrentUser.UserData.F_UserCode;//上传人  
                int fid = new BLL.T_Sys_Accessories().Add(model_T_Sys_Accessories);

                DataTable dt = new DataTable();
                IWorkbook workbook = null;
                using (FileStream file = new FileStream(PhysicalPath, FileMode.Open, FileAccess.Read))
                {
                    if (aLastName == "xlsx") // 2007版本
                    {
                        workbook = new XSSFWorkbook(file);
                    }
                    else if (aLastName == "xls") // 2003版本
                    {
                        workbook = new HSSFWorkbook(file);
                    }
                }
                ISheet sheet = workbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                //获取sheet的第一行
                IRow headerRow = sheet.GetRow(headrow);
                int cellCount = headerRow.LastCellNum;
                if (cellCount < 1)
                    return Error("文件标题没有数据");
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    dt.Columns.Add(cell.ToString());
                }
                BLL.archives bll = new BLL.archives();
                var pbll = new BLL.archives();
                var list = pbll.DataTableToList(pbll.GetList().Tables[0]);
                int count = 0;
                ////客户名称加入来电单位
                for (int i = (sheet.FirstRowNum + headrow + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    dt.Rows.Add(dataRow);
                    if (dt.Rows.Count < 1)
                        return Error("文件内容没有数据");
                    //导入后要插入数据库
                    var model = new Model.archives();
                    //Model.T_RepositoryCategory pModel = new BLL.T_RepositoryCategory().GetModel(int.Parse(pid.Trim()));
                    var pmodel = list.FirstOrDefault();

                    model.A_archiveCode = dataRow[0].ToString();
                    model.A_archiveName = dataRow[1].ToString();
                    model.fileid = fid;
                    if (dataRow[2] == null)
                    {
                        model.A_archiveState = 1;
                    }
                    else
                    {
                        model.A_archiveState = int.Parse(dataRow[2].ToString());
                    }
                   
                    model.A_archivePeople = CurrentUser.UserData.F_UserCode;//操作人
                    int n = bll.Add(model);
                    if (n > 0)
                    {
                        var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
                        Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
                        Operationlogmodel.OperationDate = DateTime.Now;
                        Operationlogmodel.OperationContent = "导入了一条物资档案信息";
                        Operationlogmodel.archivesid = n;
                        AddorUpdOperationlog(Operationlogmodel);
                        count++;
                    }
                }
                if (dt == null || dt.Rows.Count == 0)
                    return Error("文件没有数据");
                else
                {
                    var jstr = DataTableToJson(dt);
                    return Success("导入成功 " + count + "条信息", jstr);
                }

            }
            return Error("文件不能为空");
        }
        /// <summary>
        /// 档案物资导出
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        /// <summary>
        /// 档案物资导出excel
        /// </summary>
        /// <returns></returns>
        public ActionResult ExportExcelarchives()
        {
            #region 获取数据
            DataTable dt = new DataTable();

            string str = "SELECT   A_archiveCode AS '客户编号',A_archiveName AS'物资名称 ',A_archiveState AS '物资状态',A_archivePeople AS '操作人' FROM archives where 1=1  ORDER BY id desc";
            dt = DbHelperSQL.Query(str).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                dr["客户编号"] = Utils.DropHTML(dr["客户编号"].ToString());
            }
            #endregion
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("所有物资", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        #region 暂时不用
        ///// <summary>
        ///// 档案物资状态导入excel
        ///// </summary>
        ///// <returns></returns>
        //public ActionResult ImportExcelarchivesTate()
        //{
        //    HttpPostedFile _upFile = RequestString.GetFile("upFile");

        //    if (_upFile != null)
        //    {
        //        int headrow = 0;
        //        string filepath = "";
        //        string datepath = DateTime.Now.ToString("yyyyMMddHHMMss");
        //        string aLastName = _upFile.FileName.Substring(_upFile.FileName.LastIndexOf(".") + 1, (_upFile.FileName.Length - _upFile.FileName.LastIndexOf(".") - 1)); //扩展名
        //        if (aLastName != "xls" && aLastName != "xlsx")
        //        {
        //            return Error("文件类型错误，请选择Excel文件");
        //        }
        //        string newpath = datepath + "_" + _upFile.FileName;
        //        if (!Directory.Exists(Server.MapPath(this.Request.ApplicationPath + "\\ExcelData")))
        //        {
        //            Directory.CreateDirectory(Server.MapPath(this.Request.ApplicationPath + "\\ExcelData"));
        //        }
        //        filepath = this.Request.ApplicationPath + "/ExcelData/" + newpath;
        //        string PhysicalPath = Server.MapPath(filepath);
        //        _upFile.SaveAs(PhysicalPath);


        //        DataTable dt = new DataTable();
        //        IWorkbook workbook = null;
        //        using (FileStream file = new FileStream(PhysicalPath, FileMode.Open, FileAccess.Read))
        //        {
        //            if (aLastName == "xlsx") // 2007版本
        //            {
        //                workbook = new XSSFWorkbook(file);
        //            }
        //            else if (aLastName == "xls") // 2003版本
        //            {
        //                workbook = new HSSFWorkbook(file);
        //            }
        //        }
        //        ISheet sheet = workbook.GetSheetAt(0);
        //        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
        //        //获取sheet的第一行
        //        IRow headerRow = sheet.GetRow(headrow);
        //        int cellCount = headerRow.LastCellNum;
        //        if (cellCount < 1)
        //            return Error("文件标题没有数据");
        //        for (int j = 0; j < cellCount; j++)
        //        {
        //            ICell cell = headerRow.GetCell(j);
        //            dt.Columns.Add(cell.ToString());
        //        }
        //        BLL.archives bll = new BLL.archives();
        //        var pbll = new BLL.archives();
        //        var list = pbll.DataTableToList1(pbll.GetArchivestate().Tables[0]);
        //        int count = 0;
        //        ////
        //        for (int i = (sheet.FirstRowNum + headrow + 1); i <= sheet.LastRowNum; i++)
        //        {
        //            IRow row = sheet.GetRow(i);
        //            DataRow dataRow = dt.NewRow();

        //            for (int j = row.FirstCellNum; j < cellCount; j++)
        //            {
        //                if (row.GetCell(j) != null)
        //                    dataRow[j] = row.GetCell(j).ToString();
        //            }
        //            dt.Rows.Add(dataRow);
        //            if (dt.Rows.Count < 1)
        //                return Error("文件内容没有数据");
        //            //导入后要插入数据库
        //            var model = new Model.archivestate();
        //            //Model.T_RepositoryCategory pModel = new BLL.T_RepositoryCategory().GetModel(int.Parse(pid.Trim()));
        //            var pmodel = list.FirstOrDefault();

        //            model.stateName = dataRow[0].ToString();
        //            int n = bll.Addarchivestate(model);
        //            if (n > 0)
        //            {
        //                var Operationlogmodel = new CallCenterApi.Interface.Models.Input.OperationlogInput();
        //                Operationlogmodel.Operator = CurrentUser.UserData.F_UserCode;
        //                Operationlogmodel.OperationDate = DateTime.Now;
        //                Operationlogmodel.OperationContent = "导入了一条物资档案状态信息";
        //                AddorUpdOperationlog(Operationlogmodel);
        //                count++;
        //            }
        //        }
        //        if (dt == null || dt.Rows.Count == 0)
        //            return Error("文件没有数据");
        //        else
        //        {
        //            var jstr = DataTableToJson(dt);
        //            return Success("导入成功 " + count + "条信息", jstr);
        //        }

        //    }
        //    return Error("文件不能为空");
        //} 
        #endregion
        #region 暂时不用
        ///// <summary>
        ///// 档案物资状态导出
        ///// </summary>
        ///// <param name="dt"></param>
        ///// <returns></returns>
        ///// <summary>
        ///// 档案物资状态导出excel
        ///// </summary>
        ///// <returns></returns>
        //public ActionResult ExportExcel()
        //{
        //    #region 获取数据
        //    DataTable dt = new DataTable();

        //    string str = "SELECT  stateName AS '物资状态' FROM archivestate where 1=1  ORDER BY id desc";
        //    dt = DbHelperSQL.Query(str).Tables[0];
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        dr["物资状态"] = Utils.DropHTML(dr["物资状态"].ToString());
        //    }
        //    #endregion
        //    NPOIHelper npoi = new NPOIHelper();
        //    if (npoi.ExportToExcel("所有物资状态", dt) == "")
        //    {
        //        return Success("导出成功");
        //    }
        //    else
        //    {
        //        return Error("导出失败");
        //    }
        //} 
        #endregion
        public string DataTableToJson(DataTable dt)
        {
            string JsonString = string.Empty;
            JsonString = JsonConvert.SerializeObject(dt);
            return JsonString;
        }
    }
}