using RMYY_CallCenter_Api.DB;
using RMYY_CallCenter_Api.Models;
using RMYY_CallCenter_Api.Models.Enum;
using RMYY_CallCenter_Api.Models.Input;
using RMYY_CallCenter_Api.Utility;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace RMYY_CallCenter_Api.Controllers
{
    public class EquipmentController : BaseController
    {
      private BLL.T_Equipment          equipbll = new BLL.T_Equipment();


        /// <summary>
        /// 获取设备列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetList(string equipmentname,int PageSize = 10,int PageIndex=1)
        {
            string sql = "";
            if (User .F_RoleCode !="GLY")
              sql+= " and F_DeptId='"+User.F_DeptId+"'";
            if(!string.IsNullOrEmpty(equipmentname))
            {
                sql += " and F_EquipName='" + equipmentname + "'";
            }
            
            var recordCount = 0;
            var dt = RMYY_CallCenter_Api.Bll.PagerBll.GetListPager(
              "T_Equipment",
              "F_EquipID",
              "*,(case F_state when 0 then '无效'    when 1 then '有效' else '' end ) statename,dbo.GetUserNameNew(F_CreateUser) F_CreateName ",
              sql,
              "ORDER BY F_CreateTime desc ",
               PageSize,
               PageIndex,
              true,
              out recordCount);

            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };

            return Content(obj.ToJson());
           // return Success("设备列表读取成功",dt);
        }



        /// <summary>
        /// 添加字典
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public ActionResult AddEquip(string equipname, string equipnumber, int state, decimal equipprice=0)
        {
            Model.T_Equipment equipModel = new Model.T_Equipment();

            equipModel.F_EquipName = equipname;
            equipModel.F_EquipNumber = equipnumber;
            equipModel.F_State = state;
            equipModel.F_EquipPrice = equipprice;
            equipModel.F_CreateTime = DateTime.Now;
            equipModel.F_CreateUser =User.F_UserCode;
            equipModel.F_DeptId = User.F_DeptId;
            if (equipbll.Add(equipModel) > 0)
            {
                Model.T_Sys_DictionaryValue dvmodel = new Model.T_Sys_DictionaryValue();
                dvmodel.F_DictionaryFlag = "SBXX";
                dvmodel.F_Name = equipname;
                dvmodel.F_State = true;
                dvmodel.F_Sort = 0;
                Bll.T_Sys_DictionaryValue dvbll = new Bll.T_Sys_DictionaryValue();
                dvbll.Add(dvmodel);
                return Success("设备添加成功");
            }

            else
                return Error("设备添加失败");
        }



        /// <summary>
        /// 编辑设备
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public ActionResult EditEquip(int equipid,string equipname,string equipnumber,int state, decimal equipprice=0)
        {
            Model.T_Equipment equipModel = equipbll.GetModel(equipid);
            if (equipModel == null)
                return Error("设备对象不存在");
            equipModel.F_EquipName = equipname;
            equipModel.F_EquipNumber = equipnumber;
            equipModel.F_State = state;
            equipModel.F_EquipPrice = equipprice;
            equipModel.F_DeptId = User.F_DeptId;
            if (equipbll.Update(equipModel))
                return Success("设备修改成功");
            else
                return Error("设备修改失败");
        }


        //[Authority]
        /// <summary>
        /// 删除字典
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult DelEquips(string[] ids)
        {
            if (ids == null || ids.Length <= 0)
                return Error("获取参数失败");
            StringBuilder sb = new StringBuilder();
            foreach (var item in ids)
            {
                sb.Append("'" + item + "',");
            }
            if (equipbll.DeleteList(sb.ToString().Trim(',')))
                return Success("删除成功");
            else
                return Error("删除失败");
        }


        //获取设备值列表
        public ActionResult GetAllList()
        {
            DataTable dt = new DataTable();
            dt = equipbll.GetList("   F_state=1 ").Tables[0];
            return Success("列表加载成功", dt);

        }


        #region 导入耗材
        int vfileid = 0;
        public ActionResult EquipsInput()
        {
            DataTable dtres = new DataTable();
            string errres = "";
            int i = 0;
            int count = 0;//导入成功的条数
            string Excelsource = "";
            int rcount = 0;
            int ccount = 0;
            StringBuilder strBuilder = null;

            Excelsource = uploadexcel();
            #region 将文件中的数据导入数据库

            try
            {

                //************************将文件中的数据导入数据库*************************//
                DataSet dt = ReadEcxel(Excelsource, 0);
                try
                {
                    List<Model.T_Equipment> equiplist = equipbll.GetModelList(" F_State=1");

                    if (dt != null && dt.Tables[0].Rows.Count != 0)
                    {
                        rcount = dt.Tables[0].Rows.Count;
                        ccount = dt.Tables[0].Columns.Count;
                        strBuilder = new StringBuilder();//用于如果导入失败，删除已经导入过的

                        DataTable dtInputData = dt.Tables[0];//要导入的电话号码
                        List<string> sqlList = new List<string>();
                        List<string> lstsql = new List<string>();
                        int ilns = dt.Tables[0].Columns.Count;
                        DataSet dsnew = new DataSet();


                        #region MyRegion
                        using (SqlConnection coon = new SqlConnection(DbHelperSQL.connectionString))
                        {
                            try
                            {
                                coon.Open();
                                int colscount = dtInputData.Columns.Count;//列数
                                int rowscount = dtInputData.Rows.Count;//行数


                                Model.T_Equipment modelEquip = new Model.T_Equipment();
                                #region
                                modelEquip.F_DeptId = User.F_DeptId;
                                modelEquip.F_CreateTime = DateTime.Now;
                                modelEquip.F_CreateUser = User.F_UserCode;
                                modelEquip.F_State = 1;

                                 
                                #endregion
                                if (rowscount > 0)
                                {
                                    foreach (DataRow dr in dtInputData.Rows)
                                    {
                                        sqlList.Clear();
                                        lstsql.Clear();
                                        #region 导入表内容
                                        //名称	型号	价格	 
                                        modelEquip.F_EquipName = dr[0].ToString();//名称
                                       modelEquip.F_EquipNumber= dr[1].ToString();
                                        try
                                        {
                                            modelEquip.F_EquipPrice = Convert.ToDecimal(dr[2].ToString());
                                        }
                                        catch 
                                        {

                                            modelEquip.F_EquipPrice = 0;
                                        }
                                        #endregion
                                        int rmw = equipbll.Add(modelEquip);
                                        if (rmw > 0)
                                        {
                                            count += 1;//导入成功，+1
                                        }


                                    }
                                    dtres = dtInputData;

                                }
                                else
                                {
                                    errres += "该文档的内容为空";
                                }
                            }
                            catch (Exception ex)
                            {
                                if (coon.State == ConnectionState.Open)
                                {
                                    coon.Close();
                                }
                                i++;
                                errres += i.ToString() + ".导入出现异常，共导入" + count + "条数据，请从第" + (count + 2).ToString() + "行开始导入;\n";
                                //if (strBuilder.Length > 0)
                                //{
                                //    errres += "重复的工号为：" + strBuilder.ToString() + "\n";
                                //}
                            }
                            finally
                            {
                                if (coon.State == ConnectionState.Open)
                                {
                                    coon.Close();
                                }
                            }


                        }

                        #endregion

                    }
                    else
                    {
                        i++;
                        errres += i.ToString() + ".Excel中无数据;\n";
                    }

                }
                catch
                {
                    i++;
                    errres += i.ToString() + ".导入出现异常，导入失败;\n";
                }
                finally
                {
                    dt.Clear();
                    dt.Dispose();
                }

            }
            catch
            {
                errres += i.ToString() + ".Excel格式不正确，系统错误，导入失败;\n";
            }

            #endregion
            if (errres != "")
            {
                return Error("导入失败！错误信息：" + errres);
            }
            else
            {
                string info = "本文件有" + rcount.ToString() + "行" + ccount.ToString() + "列数据";
                return Success("导入成功！" + info, vfileid);
            }
        }

        private string uploadexcel()
        {
            string Excelsource = "";
            try
            {

                HttpPostedFileBase _upfile = Request.Files[0];
                if (_upfile != null)
                {
                    string imgPath = "";

                    string newFileName = DateTime.Now.ToString("yyyyMMddHHmmss");
                    newFileName = newFileName + "_" + _upfile.FileName;
                    if (!Directory.Exists(Server.MapPath(this.Request.ApplicationPath + "\\ExcelData")))
                    {
                        Directory.CreateDirectory(Server.MapPath(this.Request.ApplicationPath + "\\ExcelData"));
                    }

                    imgPath = this.Request.ApplicationPath + "/ExcelData/" + newFileName.ToString();
                    string PhysicalPath = Server.MapPath(imgPath);
                    _upfile.SaveAs(PhysicalPath);

                    Excelsource = Server.MapPath(this.Request.ApplicationPath + "\\ExcelData\\") + newFileName.ToString();

                    Model.T_Sys_Accessories filesmodel = new Model.T_Sys_Accessories();


                    filesmodel.F_FileName = newFileName;

                    filesmodel.F_FileUrl = Excelsource;

                    filesmodel.F_FileType = _upfile.FileName.Substring(_upfile.FileName.LastIndexOf('.') + 1);
                    filesmodel.F_AddTime = DateTime.Now;
                    filesmodel.F_UserCode = User.F_UserCode;
                    Bll.T_Sys_Accessories bllfile = new Bll.T_Sys_Accessories();

                    vfileid = bllfile.Add(filesmodel);
                }
            }
            catch (Exception ex) { }
            return Excelsource;
        }
        private DataSet ReadEcxel(string FileName, int Rows)
        {
            return ToDataTable(FileName);
        }
        private DataSet ToDataTable(string filePath)
        {
            DataSet ds = new DataSet();
            DataTable dt = null;

            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

                NPOI.SS.UserModel.IWorkbook book = null;
                if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                    book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
                else if (filePath.IndexOf(".xls") > 0) // 2003版本
                    book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);


                NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0);


                NPOI.SS.UserModel.IRow row = sheet.GetRow(0);


                int firstCellNum = row.FirstCellNum;
                int lastCellNum = row.LastCellNum;


                dt = new DataTable(sheet.SheetName);

                for (int i = firstCellNum; i < 12; i++)
                {
                    if (row.GetCell(i) != null)
                    {
                        dt.Columns.Add(row.GetCell(i).ToString() + i.ToString(), typeof(string));
                    }
                    else
                    {
                        dt.Columns.Add(i.ToString(), typeof(string));
                    }
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    if (sheet.GetRow(i).GetCell(1) != null && sheet.GetRow(i).GetCell(1).ToString() != "")
                    {
                        DataRow newRow = dt.Rows.Add();
                        for (int j = firstCellNum; j < 12; j++)
                        {
                            if (sheet.GetRow(i).GetCell(j) != null)
                            {
                                newRow[j] = sheet.GetRow(i).GetCell(j).ToString();
                            }
                            else
                            {
                                newRow[j] = "";
                            }
                        }
                    }
                }

                ds.Tables.Add(dt);
            }
            catch (Exception ex)
            {

            }
            return ds;
        }
        #endregion

    }
}