Autobio_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/customer/CustomerCont...


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CallCenterApi.Interface.Controllers.Base;
using System.Data;
using CallCenter.Utility;
using CallCenterApi.Common;
using CallCenterApi.DB;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Newtonsoft.Json;

namespace CallCenterApi.Interface.Controllers.customer
{
    //[Authority]
    public class CustomerController : BaseController
    {
        /// <summary>
        /// 获取客户列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetList()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                string sql = " and F_DeleteFlag=0";
                DataTable dt = new DataTable();
                //联系人
                string strname = RequestString.GetQueryString("name");
                //电话
                string strtel = RequestString.GetQueryString("tel");
                //客户名称
                string strCus = RequestString.GetQueryString("Cusname");
                //省份
                string strprov = RequestString.GetQueryString("province");

                string strstarttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
                string strendtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));

                string strpageindex = RequestString.GetQueryString("page");
                int pageindex = 1;
                string strpagesize = RequestString.GetQueryString("pagesize");
                int pagesize = 10;

                if (strname.Trim() != "" && strname != "undefined")
                {
                    sql += " and F_CustomerName like '%" + strname.Trim() + "%' ";
                }
                if (strtel.Trim() != "" && strtel != "undefined")
                {
                    sql += " and F_Telephone like '%" + strtel + "%' ";
                }
                if (strCus.Trim() != "" && strCus != "undefined")
                {
                    sql += " and F_CustomerIndustry like '%" + strCus.Trim() + "%' ";
                }
                if (strprov.Trim() != "" && strprov.Trim() != "undefined")
                {
                    sql += " and F_Province like '%" + strprov.Trim() + "%' ";
                }

                if (strstarttime.Trim() != "" && strstarttime != "undefined")
                {
                    sql += " and datediff(day,F_CreatedOn,'" + strstarttime + "')<=0 ";
                }
                if (strendtime.Trim() != "" && strendtime != "undefined")
                {
                    sql += " and datediff(day,F_CreatedOn,'" + strendtime + "')>=0   ";
                }

                if (strpageindex.Trim() != "")
                {
                    pageindex = Convert.ToInt32(strpageindex);
                }

                if (strpagesize.Trim() != "")
                {
                    pagesize = Convert.ToInt32(strpagesize);
                }
                int recordCount = 0;
                dt = BLL.PagerBLL.GetListPager(
                    "T_Cus_CustomerBase",
                    "F_CustomerId",
                    "*",
                    sql,
                    "ORDER BY F_CustomerId desc",
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);

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

                res = Content(obj.ToJson());

            }
            return res;
        }

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetCustomer()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {

                int cid = Utils.StrToInt(RequestString.GetQueryString("cid"), 0);
                if (cid != 0)
                {
                    Model.T_Cus_CustomerBase userModel = new BLL.T_Cus_CustomerBase().GetModel(cid);
                    if (userModel != null)
                    {
                        res = Success("获取成功", userModel);
                    }
                    else
                    {
                        res = Error("获取失败");
                    }
                }
                else
                {
                    res = Error("参数传输失败");
                }

            }
            return res;
        }
        /// <summary>
        /// 通过来电号码获取来电弹屏左侧客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetCustomerByTel()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                string tel = HttpUtility.UrlDecode(RequestString.GetQueryString("tel"));
                if (!string.IsNullOrEmpty(tel))
                {
                    //var userModel = new BLL.T_Cus_CustomerBase().GetModelList(" F_Telephone like '%" + tel + "%' or F_Mobile like '%" + tel + "%' ");
                    //2018-04-25 lihai 问题是客户资料删除来电弹屏还有信息，添加F_DeleteFlag = 0条件 
                    var userModel = new BLL.T_Cus_CustomerBase().GetModelList(" (F_Telephone = '" + tel + "' or F_Mobile = '" + tel + "') AND F_DeleteFlag = 0 ");
                    if (userModel.Count() > 0)
                    {
                        res = Success("获取成功", userModel.Last());
                    }
                    else
                    {
                        res = Success("获取成功");
                    }
                }
                else
                {
                    res = Error("参数传输失败");
                }
            }
            return res;
        }

        /// <summary>
        /// 添加/修改客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult AddCustomer()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                int userId = CurrentUser.UserData.F_UserId;
                if (userId != 0)
                {
                    //联系人
                    string name = RequestString.GetFormString("name");
                    //省份
                    string province = RequestString.GetFormString("province");
                    //来电单位
                    string customerindustry = RequestString.GetFormString("customerindustry");
                    //电话
                    string mobile = RequestString.GetFormString("mobile");
                    //获取当前表格选择的客户
                    int cid = Utils.StrToInt(RequestString.GetFormString("cid"), 0);
                    #region 2018-05-03 lihai 添加验证判断
                    if (string.IsNullOrEmpty(name))
                        return Error("姓名不能为空！");
                    if (string.IsNullOrEmpty(province))
                        return Error("省份不能为空！");
                    if (!Validate.IsHasCHZN(province))
                        return Error("请输入有效的省份！");
                    if (Validate.GetStringLength(province) <= 2)
                        return Error("省份名称不能少于2个字！");
                    if (string.IsNullOrEmpty(mobile))
                        return Error("联系电话不能为空！");
                    if (string.IsNullOrEmpty(customerindustry))
                        return Error("来电单位不能为空！");
                    #endregion

                    Model.T_Cus_CustomerBase model = new Model.T_Cus_CustomerBase();
                    BLL.T_Cus_CustomerBase bll = new BLL.T_Cus_CustomerBase();
                    //添加或修改时要根据电话判断是否已有记录
                    if (cid == 0)
                    {
                        var cusmodel = bll.GetModelList("F_Telephone='" + mobile + "' AND F_DeleteFlag = 0");
                        if (cusmodel.Count() > 0)
                            return Error("已存在该号码的用户，请直接编辑！");
                        else
                        {
                            model.F_CustomerName = name;
                            model.F_Province = province;
                            model.F_CustomerIndustry = customerindustry;
                            //统一为F_Telephone
                            model.F_Telephone = mobile;

                            model.F_CreateBy = userId;
                            model.F_CreatedOn = DateTime.Now;
                            model.F_DeleteFlag = 0;
                            int n = bll.Add(model);
                            if (n > 0)
                            {
                                return Success("新增成功！", n);
                            }
                            else
                            {
                                return Error("新增失败！");
                            }
                        }
                    }
                    else
                    {
                        model = bll.GetModel(cid);
                        if (model != null)
                        {
                            if (!string.IsNullOrEmpty(name))
                                model.F_CustomerName = name;
                            if (!string.IsNullOrEmpty(province))
                                model.F_Province = province;
                            if (!string.IsNullOrEmpty(customerindustry))
                                model.F_CustomerIndustry = customerindustry;
                            if (!string.IsNullOrEmpty(mobile))
                                model.F_Telephone = mobile;
                            if (bll.Update(model))
                            {
                                return Success("修改成功！", model.F_CustomerId);
                            }
                            else
                            {
                                return Error("修改失败！");
                            }
                        }
                    }
                    List<string> lddep = new BLL.T_Cus_CustomerBase().GetLDdep();
                    var gdlddep = new BLL.T_Wo_WorkOrder().GetModelList("1=1").Select(p => p.Address).Distinct().ToList();//从工单来电单位中加载
                    var gdfkdep = new BLL.T_Wo_WorkOrder().GetModelList("1=1").Select(p => p.Source).Distinct().ToList();//从工单反馈单位中加载
                    foreach (var item in gdlddep)
                    {
                        if (!lddep.Contains(item))
                            lddep.Add(item);
                    }
                    foreach (var item in gdfkdep)
                    {
                        if (!lddep.Contains(item))
                            lddep.Add(item);
                    }
                    CacheHelper.Insert("LDDep", lddep);
                }
                return Error("参数错误");

            }
            return Error("未知错误，请重新登录");
        }

        /// <summary>
        /// 删除客户
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult DelCustomer(string[] ids)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {

                if (ids != null && ids.Length > 0)
                {
                    string idd = " ";
                    foreach (string str in ids)
                    {
                        idd += str + ",";
                    }
                    //string state = RequestString.GetQueryString("state");

                    string sql = "update T_Cus_CustomerBase set F_DeleteFlag=1 where F_CustomerId in (" + idd.TrimEnd(',') + ")";
                    if (!string.IsNullOrEmpty(idd.Trim()))
                    {
                        if (DbHelperSQL.ExecuteSql(sql) > 0)
                        {
                            res = Success("设置成功");
                            List<string> lddep = new BLL.T_Cus_CustomerBase().GetLDdep();
                            var gdlddep = new BLL.T_Wo_WorkOrder().GetModelList("1=1").Select(p => p.Address).Distinct().ToList();//从工单来电单位中加载
                            var gdfkdep = new BLL.T_Wo_WorkOrder().GetModelList("1=1").Select(p => p.Source).Distinct().ToList();//从工单反馈单位中加载
                            foreach (var item in gdlddep)
                            {
                                if (!lddep.Contains(item))
                                    lddep.Add(item);
                            }
                            foreach (var item in gdfkdep)
                            {
                                if (!lddep.Contains(item))
                                    lddep.Add(item);
                            }
                            CacheHelper.Insert("LDDep", lddep);
                        }
                        else
                        {
                            res = Error("设置失败");
                        }
                    }
                    else
                    {
                        res = Error("请选择用户");
                    }
                }
                else
                {
                    res = Error("获取参数失败");
                }

            }
            return res;
        }
        /// <summary>
        /// 绑定来电单位
        /// </summary>
        /// <returns></returns>
        public ActionResult BindLDdep()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            //string keypara = RequestString.GetQueryString("keypara");
            if (Request.IsAuthenticated)
            {
                //List<string> lddep = new BLL.T_Cus_CustomerBase().GetLDdep();
                List<string> lddep = null;
                lddep = CacheHelper.Get("LDDep") as List<string>;
                List<string> resdep = new List<string>();
                if (lddep != null && lddep.Count > 0)
                {
                    foreach (string dep in lddep)
                    {
                        if (!Utils.IsNumeric(dep))
                        {
                            resdep.Add(dep);
                        }
                        //if (dep.Contains(keypara))
                        //{
                        //    resdep.Add(dep);
                        //}
                    }
                    var obj = new
                    {
                        dep = resdep
                    };
                    res = Success("获取成功", obj);
                }
                else
                {
                    lddep = new BLL.T_Cus_CustomerBase().GetLDdep();//从客户信息查找
                    var gdlddep = new BLL.T_Wo_WorkOrder().GetModelList("1=1").Select(p => p.Address).Distinct().ToList();//从工单来电单位中加载
                    var gdfkdep = new BLL.T_Wo_WorkOrder().GetModelList("1=1").Select(p => p.Source).Distinct().ToList();//从工单反馈单位中加载
                    foreach (var item in gdlddep)
                    {
                        if (!lddep.Contains(item))
                            lddep.Add(item);
                    }
                    foreach (var item in gdfkdep)
                    {
                        if (!lddep.Contains(item))
                            lddep.Add(item);
                    }
                    if (lddep != null && lddep.Count > 0)
                    {
                        foreach (string dep in lddep)
                        {
                            if (!Utils.IsNumeric(dep)) {
                                resdep.Add(dep);
                            }
                            //if (!string.IsNullOrEmpty(keypara) || !Utils.IsNumeric(dep))
                            //{
                            //    if (dep.Contains(keypara))
                            //    {
                            //        resdep.Add(dep);
                            //    }

                            //}
                        }
                        var obj = new
                        {
                            dep = resdep
                        };
                        res = Success("获取成功", obj);
                    }
                    else
                        res = Error("获取失败，没有对应数据");
                }
            }
            return res;
        }
        /// <summary>
        /// 判断来电单位是否存在。不存在才可以
        /// </summary>
        /// <returns></returns>
        public ActionResult LDdepExist()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                string lddep = RequestString.GetQueryString("lddep");
                if (!string.IsNullOrEmpty(lddep))
                {
                    bool lddepexists = new BLL.T_Cus_CustomerBase().LDdepExist(lddep);
                    if (lddepexists)
                    {
                        res = Success("获取成功");
                    }
                    else
                    {
                        res = Error("获取失败");
                    }
                }
                else
                {
                    res = Error("参数传输失败");
                }
            }
            return res;
        }
        /// <summary>
        /// 导入excel
        /// </summary>
        /// <returns></returns>
        public ActionResult ExportExcel()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                //string filestr = RequestString.GetFormString("file");
                //string filestr = RequestString.GetQueryString("file");
                int userId = CurrentUser.UserData.F_UserId;
                if (userId != 0)
                {
                    ////需要先将文件保存到服务器项目下，再读取
                    ////传入的应该是整个文件，file和上传按钮
                    //string file = Request.Files[0].FileName;
                    //string fileextension = Request.Files[0].ContentType;

                    //if (!string.IsNullOrEmpty(file) && file != "undefined")
                    //{
                    //    //string extensionstr = Path.GetExtension(filestr);
                    //    //if (extensionstr == ".xls" || extensionstr == ".xlsx")
                    //    if (fileextension == "application/vnd.ms-excel" || fileextension == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                    //    {
                    //        //将上传的文件保存在服务器下
                    //        Request.SaveAs(Server.MapPath("~/App_Data/" + file), false);

                    //        if (System.IO.File.Exists(Server.MapPath("~/App_Data/" + file)))
                    //        {
                    //            HSSFWorkbook hssfworkbook;
                    //            XSSFWorkbook xssfworkbook;
                    //            ISheet sheet;
                    //            DataTable dt = new DataTable();
                    //            using (FileStream filestream = new FileStream(Path.GetFullPath(file), FileMode.Open, FileAccess.Read))
                    //            {
                    //                if (fileextension == "application/vnd.ms-excel")
                    //                {
                    //                    hssfworkbook = new HSSFWorkbook(filestream);
                    //                    sheet = hssfworkbook.GetSheetAt(0);
                    //                }
                    //                else
                    //                {
                    //                    xssfworkbook = new XSSFWorkbook(filestream);
                    //                    sheet = xssfworkbook.GetSheetAt(0);
                    //                }
                    //            }
                    //            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    //            IRow headerRow = sheet.GetRow(0);
                    //            int cellCount = headerRow.LastCellNum;

                    //            for (int j = 0; j < cellCount; j++)
                    //            {
                    //                ICell cell = headerRow.GetCell(j);
                    //                dt.Columns.Add(cell.ToString());
                    //            }

                    //            Model.T_Cus_CustomerBase model = new Model.T_Cus_CustomerBase();
                    //            BLL.T_Cus_CustomerBase bll = new BLL.T_Cus_CustomerBase();
                    //            int count = 0;
                    //            for (int i = (sheet.FirstRowNum + 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);

                    //                //导入后要插入数据库
                    //                model.F_CustomerName = dataRow[2].ToString();
                    //                model.F_Province = dataRow[0].ToString();
                    //                model.F_CustomerIndustry = dataRow[1].ToString();
                    //                model.F_Mobile = dataRow[3].ToString();

                    //                model.F_CreateBy = userId;
                    //                model.F_CreatedOn = DateTime.Now;
                    //                model.F_DeleteFlag = 0;
                    //                int n = bll.Add(model);
                    //                if (n > 0)
                    //                { count++; }
                    //            }
                    //            res = Success("共导入" + dt.Rows.Count + "条，成功" + count + "条", dt);
                    //        }
                    //        else
                    //        {
                    //            res = Error("文件未上传成功");
                    //        }
                    //    }
                    //    else
                    //    {
                    //        res = Error("文件类型错误");
                    //    }
                    //}
                    //else
                    //{
                    //    res = Error("参数传输失败");
                    //}
                    HttpPostedFile _upFile = RequestString.GetFile("upFile");
                    if (_upFile != null)
                    {
                        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")
                        {
                            res = 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);
                            }
                            //hssfworkbook = new HSSFWorkbook(file);
                        }
                        ISheet sheet = workbook.GetSheetAt(0);
                        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                        IRow headerRow = sheet.GetRow(0);
                        int cellCount = headerRow.LastCellNum;
                        if (cellCount < 1)
                            res = Error("文件标题没有数据");
                        for (int j = 0; j < cellCount; j++)
                        {
                            ICell cell = headerRow.GetCell(j);
                            dt.Columns.Add(cell.ToString());
                        }
                        Model.T_Cus_CustomerBase model = new Model.T_Cus_CustomerBase();
                        BLL.T_Cus_CustomerBase bll = new BLL.T_Cus_CustomerBase();
                        int count = 0;
                        //客户名称加入来电单位
                        List<string> lddep = null;
                        lddep = CacheHelper.Get("LDDep") as List<string>;
                        for (int i = (sheet.FirstRowNum + 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);
                            dt.Rows.Add(dataRow);
                            if (dt.Rows.Count < 1)
                                res = Error("文件内容没有数据");
                            //导入后要插入数据库
                            model.F_CustomerName = dataRow[2].ToString();
                            model.F_Province = dataRow[0].ToString();
                            model.F_CustomerIndustry = dataRow[1].ToString();
                            //model.F_Mobile = dataRow[3].ToString();
                            model.F_Telephone = dataRow[3].ToString();

                            model.F_CreateBy = userId;
                            model.F_CreatedOn = DateTime.Now;
                            model.F_DeleteFlag = 0;
                            int n = bll.Add(model);

                            if (n > 0)
                            {
                                count++;
                                var cusindustry = dataRow[1].ToString();
                                if (!lddep.Contains(cusindustry))
                                {
                                    lddep.Add(cusindustry);
                                    CacheHelper.Insert("LDDep", lddep);
                                }
                            }
                        }
                        //if (dt == null || dt.Rows.Count == 0)
                        if (dt == null || dt.Rows.Count == 0)
                            res = Error("文件没有数据");
                        //var jstr = DataTableToJson(dt);
                        else
                        {
                            var jstr = DataTableToJson(dt);
                            res = Success("导入成功", jstr);
                        }

                    }
                }
            }
            return res;
        }

        public string DataTableToJson(DataTable dt)
        {
            string JsonString = string.Empty;
            JsonString = JsonConvert.SerializeObject(dt);
            return JsonString;
        }
    }
}