using RMYY_CallCenter_Api.DB;
using RMYY_CallCenter_Api.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace RMYY_CallCenter_Api.Controllers.System
{
    public class UserAccountController : BaseController
    {

        private Bll.T_Sys_UserAccount userbll = new Bll.T_Sys_UserAccount();
        // GET: UserAccount

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <returns></returns>
        //[HttpGet]
        public ActionResult GetList(string key,int isexport=0, int roleid = 0, int deptid = 0, int page = 1, int pagesize = 10)
        {
            DataTable dt = new DataTable();
            string sql = "";

            if (deptid > 0)
            {
                sql += $" and F_DeptId in(select F_DeptId from [dbo].[GetDeptid] ({deptid })) ";
            }
            if (roleid > 0)
            {
                sql += " and F_RoleId='" + roleid + "'";
            }

            if (!string.IsNullOrWhiteSpace(key))
            {
                sql += " and (F_UserCode like '%" + key + "%' or F_UserName like '%" + key + "%' or F_Telephone like '%" + key + "%')";
            }

            int recordCount = 0;

            if (isexport == 1)
            {
                string sql1 = " 1=1" + sql;
                dt =DbHelperSQL.Query(" select F_UserName,F_UserCode ,[dbo].[GetDepName](F_DeptId) deptname,[F_Sex],[F_Telephone],[F_WorkPhone]  ,[F_HomeAddress]  ,[F_Job] from T_Sys_UserAccount where " + sql1).Tables[0];
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("用户信息", dt, col()) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }



            dt = Bll.PagerBll.GetListPager(
                                    "T_Sys_UserAccount a with(nolock)",
                                    "F_UserId",
                                    "*,(select F_DeptName from T_Sys_Department  where F_DeptId=a.F_DeptId)as F_DeptName,(select F_RoleName from T_Sys_Role where F_RoleId=a.F_RoleId)as F_RoleName,(select F_ZXZName from T_Sys_SeatGroup  where F_ID=a.F_SeatGroup)as F_SeatGroupName",
                                    sql,
                                    "ORDER BY F_UserId desc",
                                    pagesize,
                                    page,
                                    true,
                                    out recordCount);
            var obj = new
            {
                state = "success",
                rows = dt,
                total = recordCount
            };

            return Content(obj.ToJson());
        }
        private string[] col()
        {
            string[] ccc = {
                   "姓名","工号","部门","性别","电话","单位电话","家庭住址","职务" 
            };

            return ccc;
        }
        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public ActionResult GetModel(int userid = 0, string usercode = "")
        {
            string sql = " 1=1 ";        
            if (userid > 0)
            {
                sql += " and F_UserId=" + userid;
            }
            if (!string.IsNullOrWhiteSpace(usercode))
            {
                sql += " and F_UserCode='" + usercode + "'";
            }
            if (string.IsNullOrEmpty(usercode)&& userid==0)
                return Error("获取失败");

            Model.T_Sys_UserAccount ua = new Bll.T_Sys_UserAccount().GetModelList(sql).FirstOrDefault();
            if (ua != null)
            {
                if( ua.F_DeptId>0)
                {
                    var dptmodel = new Bll.T_Sys_Department().GetModel(ua.F_DeptId);
                    ua.F_DeptName = dptmodel.F_DeptName;
                    ua.deptype = new Bll.T_Sys_Department().GetDeplayer(ua.F_DeptId);
                    ua.F_Location = dptmodel.F_Location;
                }
                
            

            }
            if (ua == null)
                return Error("不存在此用户信息！");
            return Success("获取成功", ua);
        }
        /// <summary>
        /// 获取所有的人员信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetModelalllist()
        {
            var sql = "";
            List<Model.T_Sys_UserAccount> ualist = new Bll.T_Sys_UserAccount().GetModelList(sql);
           
            return Success("获取成功", ualist);
        }

        // [Authority]
        //修改用户信息
        public ActionResult EditUsers(int userid, string Usercode, string Username, string Password, int DeptId, int RoleId,   int? SeatGroup, int? SeatFlag, string Remark, string Phon, string sex, string companyphone, string address, string job, string Extno = "")
        {
            var umodel = userbll.GetModelList("  F_UserId = " + userid).FirstOrDefault();
            if (umodel == null)
                return Error("获取用户失败");

            if (userbll.GetRecordCount(" F_UserCode='" + Usercode.Trim() + "' and F_UserId != " + userid) > 0)
            {
                return Error("已存在此工号，请重新输入！");
            }
            umodel.F_UserCode = Usercode.Trim();
            umodel.F_UserName = Username.Trim();
            umodel.F_ExtensionNumber = Extno.Trim();
            umodel.F_DeptId = DeptId;
            umodel.F_RoleId = RoleId;
            //umodel.F_SeatGroup = SeatGroup;
            //if (SeatGroup!=null &&SeatGroup > 0)
            //{
            //    umodel.F_SeatFlag = 1;
            //}
            //else
            //    umodel.F_SeatFlag = 0;
            umodel.F_SeatFlag = SeatFlag;
            umodel.F_Remark = Remark;
            umodel.F_Telephone = Phon;
            umodel.F_Sex = sex;
            umodel.F_WorkPhone = companyphone;
            umodel.F_HomeAddress = address;
            umodel.F_Job = job;
            if (userbll.Update(umodel))
                return Success("编辑成功！");
            else
                return Error("编辑失败！");
        }

        //[Authority]
        //添加用户信息
        public ActionResult AddUsers(string Usercode, string Username, string Password, int DeptId, int RoleId,int? SeatGroup, int? SeatFlag, string Remark, string Phon, string sex, string companyphone,string address,string job, string Extno = "")
        {
            if (userbll.GetRecordCount(" F_UserCode='" + Usercode.Trim() + "' ") > 0)
            {
                return Error("已存在此工号，请重新输入！");
            }

            Model.T_Sys_UserAccount ua = new Model.T_Sys_UserAccount();

            ua.F_UserCode = Usercode.Trim();
            ua.F_UserName = Username.Trim();
            ua.F_Password = Password.Trim();
            ua.F_ExtensionNumber = Extno.Trim();
            ua.F_DeptId = DeptId;
            ua.F_RoleId = RoleId;
            //ua.F_SeatGroup = SeatGroup;
            //if (SeatGroup > 0)
            //{
            //    ua.F_SeatFlag = 1;
            //}
            //else
            //    ua.F_SeatFlag = 0;

            ua.F_SeatFlag = SeatFlag;
            ua.F_Remark = Remark;
            ua.F_Telephone = Phon;
            ua.F_Sex = sex;
            ua.F_CreateUser = User.F_UserCode;
            ua.F_CreateTime = DateTime.Now;
            ua.F_WorkPhone = companyphone;
            ua.F_HomeAddress = address;
            ua.F_EnableFlag = 1;
            ua.F_Job = job;
            ua.F_DeleteFlag = 0;
            if (new Bll.T_Sys_UserAccount().Add(ua) > 0)
                return Success("新增成功！");
            else
                return Error("新增失败！");
        }

      
        //[Authority]
        //删除用户
        public ActionResult DeleteUsers(string[] ids)
        {
           if(User!=null)
            {
                if (ids == null || ids.Length <= 0)
                    return Error("请选择用户");
                var idStr = string.Join(",", ids);
                if (string.IsNullOrEmpty(idStr))
                    return Error("请选择用户");
                if (userbll.DeleteList(idStr))
                    return  Success("删除成功");
                else
                    return Error("删除失败");
            }
            return Error("请重新登陆！");
        }

        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="usercode"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public ActionResult ResetPwd(string oldpwd, string username, string phone, string usercode = "", string pwd = "")
        {
            if (User!=null)
            {
                if (string.IsNullOrEmpty(pwd))
                    return Error("请输入密码");
                var model = userbll.GetModel(usercode);
                if (model == null) return Error("此用户不存在");

                var oldmodel = userbll.GetModelList(" F_UserCode='" + usercode + "' and F_Password='" + oldpwd + "'");
                if (oldmodel == null || oldmodel.Count == 0)
                {
                    return Error("旧密码错误");

                }
                model.F_Password = pwd;
                model.F_UserName = username;
                model.F_Telephone = phone;
                if (userbll.Update(model))
                    return Success("重置密码成功");
                else
                    return Error("重置密码失败");
            }
            return Error("未知错误，请重新登录");
        }



        /// <summary>
        /// 获取坐席列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetGroupseatList()
        {
            if (User!=null)
            {
                DataTable dt = new DataTable();
                int deptid = User.F_DeptId;
                int roleid = User.F_RoleId;

                string sql = "";
                //if (roleid != 17)
                //{
                //    sql = " and F_DeptId=" + deptid;
                //}

                dt = userbll.GetList(" f_seatflag=1 and F_DeleteFlag=0 " + sql + " order by f_userid desc ").Tables[0];

                if (dt.Rows.Count > 0)
                {
                   return  Success("坐席列表加载成功", dt);
                }
                else
                {
                   return Error("坐席列表加载失败");
                }
            }
            return Error("未知错误，请重新登陆");
        }

        /// <summary>
        /// 编辑坐席分机号
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>

        public ActionResult EditExtensionNum(int userid, string Extno = "")
        {
            if (userid < 0)
                return Error("参数错误！");
            var usermodel = userbll.GetModel(userid);

            var modellist = userbll.GetModelList(" F_ExtensionNumber='"+Extno+ "' and F_UserId!='"+ userid + "' ");
            if (modellist != null && modellist.Count > 0)
            {

                return Error("分机号已存在！");
            }

            if (usermodel != null)
            {
                usermodel.F_ExtensionNumber = Extno;
                userbll.Update(usermodel);
                return Success("编辑成功！");
            }
            return Error("不存在此坐席信息！");
        }
        /// <summary>
        /// 启用  state = 0和禁用  state = 1 用户 
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public ActionResult DelUsers(string[] ids, int state = 0)
        {
            if (ids == null || ids.Length <= 0)
                return Error("请选择用户");
            var idStr = string.Join(",", ids);
            if (string.IsNullOrEmpty(idStr))
                return Error("请选择用户");

          
            //话务人员限制权限判断
            //if (state == 0)
            //{                 
            //        if (userbll.DeleteFlagList(idStr, state))
            //            return Success("设置成功");
            //        else
            //            return Error("设置失败");
                 
            //}
            if (userbll.DeleteFlagList(idStr, state))
                return Success("设置成功");
            else
                return Error("设置失败");
        }


        //获取坐席列表
        public ActionResult GetSeatList()
        {
            //List<Model.T_Sys_UserAccount> userSeartList = sysUserAccountBll.GetModelList(" f_seatflag=1 ");
            var drs = userbll.GetList("").Tables[0].Select("f_seatflag=1");
            var userSeartList = from r in drs
                                select new
                                {
                                    F_UserId = r["F_UserId"].ToString(),
                                    F_UserName = r["F_UserName"].ToString(),
                                    F_UserCode = r["F_UserCode"].ToString()
                                };
            var result = userSeartList.OrderBy(p => p.F_UserName.Substring(p.F_UserName.Length - 1)).ToList();

            return Success("列表加载成功", result);
        }

        // 导入号码读取填入数据库
        int vfileid = 0;
        public ActionResult  UserInput(int deptid)
        {
            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_Sys_UserAccount> userlist = userbll.GetModelList(" F_DeleteFlag=0");

                    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_Sys_UserAccount modelusermport = new Model.T_Sys_UserAccount();
                                #region
                                modelusermport.F_CreateUser = User.F_UserCode;

                                modelusermport.F_CreateTime = DateTime.Now;
                                modelusermport.F_DeleteFlag = 0;
                                modelusermport.F_DeptId = deptid;
                                modelusermport.F_Password = "e10adc3949ba59abbe56e057f20f883e";
                                modelusermport.F_EnableFlag = 0;
                                #endregion
                                if (rowscount > 0)
                                {
                                    foreach (DataRow dr in dtInputData.Rows)
                                    {
                                        sqlList.Clear();
                                        lstsql.Clear();
                                        #region 导入表内容
                                        //姓名	工号	电话	性别	单位电话	职务	家庭住址
                                        modelusermport.F_UserName = dr[0].ToString();//姓名
                                        string usercode= dr[1].ToString();//工号 
                                        Model.T_Sys_UserAccount oldusermodel = userlist.Find(t => t.F_UserCode == usercode);
                                        if (oldusermodel == null)
                                        {
                                            modelusermport.F_UserCode = dr[1].ToString();//工号 
                                        }
                                        else
                                        {
                                            continue;
                                        }

                                        modelusermport.F_Telephone= dr[2].ToString();//电话
                                        modelusermport.F_Sex = dr[3].ToString();//性别

                                        modelusermport.F_WorkPhone = dr[4].ToString();//单位电话 

                                        modelusermport.F_Job = dr[5].ToString();//职务 
                                        modelusermport.F_HomeAddress = dr[6].ToString();//家庭住址 
                                       
                                        #endregion
                                        int rmw = userbll.Add(modelusermport);
                                            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;
        }
    }
}