
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;

namespace CallCenterApi.Interface.Controllers.customer
{
    [Authority]
    public class CustomerController : BaseController
    {
        /// <summary>
        /// 获取客户列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetList(int isdc=0)
        {
            string sql = " and F_DeleteFlag=0";
            DataTable dt = new DataTable();

            string chargeDept = HttpUtility.UrlDecode(RequestString.GetQueryString("chargeDept"));
            string keyWord = HttpUtility.UrlDecode(RequestString.GetQueryString("keyWord"));
            //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 (chargeDept.Trim() != "" && chargeDept != "undefined")
            {
                sql += " and F_ChargeDept like '%" + chargeDept + "%' ";
            }
            if (keyWord.Trim() != "" && keyWord != "undefined")
            {
                sql += " and (F_Telephone like '%" + keyWord + "%' or F_Mobile like '%" + keyWord + "%' or F_ChargeTelephone like '%" + keyWord + "%' or F_Fax like '%" + keyWord + "%' or F_ChargeDept like '%" + keyWord + "%' or F_CustomerName like '%" + keyWord + "%') ";
            }
          

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

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            if (isdc>0)
            {
                var dtdc = DbHelperSQL.Query("select F_CustomerName 联系人姓名,F_Mobile 联系人电话,F_Email 邮箱,F_Address 地址,(select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_CusPhone=a.F_Mobile) as 工单数量 ,(select count(1) from dbo.T_Call_CallRecords where CallType=0  and CallNumber=a.F_Mobile ) as 来电数量"
                 + " from T_Cus_CustomerBase a WITH(NOLOCK) where 1=1 " + sql ).Tables[0];
                var msg = new NPOIHelper().ExportToExcel("市民资料", dtdc);
                if (msg == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
           
            string cols = "*,(select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_CusPhone=a.F_Mobile) as gdCount, (select count(1) from dbo.T_Call_CallRecords where CallType=0  and CallNumber=a.F_Mobile ) as ldCount ";
            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Cus_CustomerBase a WITH(NOLOCK) ",
                "F_CustomerId",
                cols,
                sql,
                "ORDER BY F_CustomerId desc",
                pagesize,
                pageindex,
                true,
                out recordCount);

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

            return Content(obj.ToJson());


        }

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetCustomer()
        {
            int cid = Utils.StrToInt(RequestString.GetQueryString("cid"), 0);
            if (cid != 0)
            {
                Model.T_Cus_CustomerBase User = new BLL.T_Cus_CustomerBase().GetModel(cid);
                if (User != null)
                {
                    return Success("获取成功", User);
                }
                else
                {
                    return Error("获取失败");
                }
            }
            else
            {
                return Error("参数传输失败");
            }

        }

        /// <summary>
        /// 添加/修改客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult AddCustomer()
        {
            string name = RequestString.GetFormString("name");
            string ename = RequestString.GetFormString("lxr");
            string mobile = RequestString.GetFormString("mobile");
            string telphone = RequestString.GetFormString("telphone");
            string chargeTelephone = RequestString.GetFormString("chargeTelephone");
            string fax = RequestString.GetFormString("fax");
            string postcode = RequestString.GetFormString("postcode");
            string email = RequestString.GetFormString("email");

            string chargeDept = RequestString.GetFormString("chargeDept");
            string serviceDept = RequestString.GetFormString("serviceDept");
            int serviceDeptID = RequestString.GetInt("serviceDeptID", 0);
            string customerIndustry = RequestString.GetFormString("customerIndustry");
            int industryId = RequestString.GetInt("industryId", 0);
            string sex = RequestString.GetFormString("sex");

            int cid = Utils.StrToInt(RequestString.GetFormString("cid"), 0);

            #region 验证
            if (string.IsNullOrEmpty(name))
            {
                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)
            {
                model.F_CustomerName = name;    //客户名称
                model.F_CustomerEName = ename;  //联系人

                model.F_Mobile = mobile;        //联系人手机
                model.F_Telephone = telphone;   //联系人电话-单位电话
                model.F_ChargeTelephone = chargeTelephone;  //客户负责人联系方式-其他人电话
                model.F_Fax = fax;              //传真
                model.F_PostCode = postcode;    //邮编
                model.F_Email = email;          //邮件

                model.F_ChargeDept = chargeDept;            //所在单位
                model.F_ServiceDept = serviceDept;          //部门
                model.F_ServiceDeptID = serviceDeptID;      //部门ID
                model.F_CustomerIndustry = customerIndustry;//职务
                model.F_IndustryId = industryId;    //职务ID
                model.F_Sex = sex;                  //性别

                model.F_CreateBy = User.F_UserId;          //创建人ID
                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)
                {
                    model.F_CustomerName = name;
                    model.F_CustomerEName = ename;
                    model.F_Mobile = mobile;
                    model.F_Telephone = telphone;
                    model.F_ChargeTelephone = chargeTelephone;
                    model.F_Fax = fax;
                    model.F_PostCode = postcode;
                    model.F_Email = email;
                    model.F_ChargeDept = chargeDept;      //所在单位
                    model.F_ServiceDept = serviceDept;      //部门
                    model.F_ServiceDeptID = serviceDeptID;    //部门ID
                    model.F_CustomerIndustry = customerIndustry;      //职务
                    model.F_IndustryId = industryId;   //职务ID
                    model.F_Sex = sex;   //职务ID
                    if (bll.Update(model))
                    {
                        return Success("修改成功！");
                    }
                    else
                    {
                        return Error("修改失败！");
                    }
                }
                else
                {
                    return Error("修改失败！");
                }
            }

        }

        /// <summary>
        /// 删除客户
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult DelCustomer(string[] ids)
        {
            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)
                    {
                        return Success("设置成功");
                    }
                    else
                    {
                        return Error("设置失败");
                    }
                }
                else
                {
                    return Error("请选择用户");
                }
            }
            else
            {
                return Error("获取参数失败");
            }

        }

        /// <summary>
        /// 导入客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult ImportCustomer()
        {
            HttpPostedFile _upfile = RequestString.GetFile("upFile");
            int headrow = 0;
            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = npoi.ExcelToTable(_upfile, headrow);

            foreach (DataRow dr in dt.Rows)
            {
                headrow = headrow + 1;
                if (dr[0].ToString() != "" && dr[1].ToString() != "")
                {
                    Model.T_Cus_CustomerBase model = new Model.T_Cus_CustomerBase();
                    model.F_CustomerName = dr[0].ToString();    //客户姓名
                    model.F_Sex = dr[1].ToString();   //性别
                    model.F_Mobile = dr[2].ToString();          //手机号码
                    model.F_Email = dr[3].ToString();           //Email
                    model.F_Address = dr[4].ToString();      //部门
                    model.F_CreateBy = User.F_UserId;
                    model.F_CreatedOn = DateTime.Now;
                    model.F_DeleteFlag = 0;
                    if (new BLL.T_Cus_CustomerBase().Add(model) <= 0)
                    {
                        return Error("第" + headrow + "行，导入失败!");
                    }
                }
                else
                {
                    return Error("第" + headrow + "行，第1，2列信息不能为空，未导入");
                }
            }
            return Success("导入成功");
        }





        ///// <summary>
        ///// 导入excel
        ///// </summary>
        public ActionResult ImportExcel()
        {
                HttpPostedFile _upFile = RequestString.GetFile("upFile");
                if (_upFile != null)
                {
                    int headrow = 0;
                    #region 上传文件
                    string filepath = "";
                    string datepath = DateTime.Now.ToString("yyyyMMddHHMMss");
                    string aLastName = Path.GetExtension(_upFile.FileName);
                    string oriname = Path.GetFileNameWithoutExtension(_upFile.FileName);
                    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);
                    #endregion
                    NPOIHelper np = new NPOIHelper();
                    DataTable dt = np.ExcelToTable(_upFile, headrow);
                    string msg = string.Empty;
                    if (dt == null || dt.Rows.Count == 0)
                        return Error("文件没有数据");
                    else
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            headrow = headrow + 1;
                        if (string.IsNullOrEmpty(dr["联系人姓名"].ToString()))
                        {
                            msg = msg + "第" + headrow + "行，联系人姓名，未导入<br>";
                            continue;
                        }
                        if (string.IsNullOrEmpty(dr["联系人电话"].ToString()))
                        {
                            msg = msg + "第" + headrow + "行，联系人电话，未导入<br>";
                            continue;
                        }
                    var    cusmodel = new BLL .T_Cus_CustomerBase().GetModelBy(dr["联系人电话"].ToString(), dr["联系人姓名"].ToString());
                        if (cusmodel!=null )
                        {
                            msg = msg + "第" + headrow + "行，该联系人已存在，未导入<br>";
                            continue;
                        }
                        Model.T_Cus_CustomerBase model = new Model.T_Cus_CustomerBase();
                        model.F_CustomerName = dr["联系人姓名"].ToString(); ;    //
                        //    model.F_Sex = dr["性别"].ToString(); ;
                      model.F_Mobile = dr["联系人电话"].ToString(); ;            //手机号码
                        //model.F_Email = dr["邮箱"].ToString(); ;            //手机号码           //Email
                        //model.F_Address = dr["地址"].ToString(); ;      //部门
                            model.F_CreateBy = User.F_UserId;
                            model.F_CreatedOn = DateTime.Now;
                            model.F_DeleteFlag = 0;
                            if (new BLL.T_Cus_CustomerBase().Add(model) <= 0)
                            {

                                msg = msg + "第" + headrow + "行，客户电话重复，未导入<br>";
                            }
                        }
                        if (string.IsNullOrEmpty(msg))
                            return Success("导入成功 ");
                        else
                            return Error(msg);
                    }
                }
                return Error("数据源上传失败");
           
        }

        /// <summary>
        /// 导出客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult ExportCustomer()
        {
            string sql = string.Empty;
            BLL.T_Cus_CustomerBase bll = new BLL.T_Cus_CustomerBase();
            NPOIHelper npoi = new NPOIHelper();
            string chargeDept = HttpUtility.UrlDecode(RequestString.GetQueryString("chargeDept"));
            string keyWord = HttpUtility.UrlDecode(RequestString.GetQueryString("keyWord"));

            if (chargeDept.Trim() != "" && chargeDept != "undefined")
            {
                sql += " and F_ChargeDept like '%" + chargeDept + "%' ";
            }
            if (keyWord.Trim() != "" && keyWord != "undefined")
            {
                sql += " and (F_Telephone like '%" + keyWord + "%' or F_Mobile like '%" + keyWord + "%' or F_ChargeTelephone like '%" + keyWord + "%' or F_Fax like '%" + keyWord + "%' or F_CustomerName like '%" + keyWord + "%') ";
            }
           
            DataTable dt = bll.GetList(" F_DeleteFlag=0 " + sql).Tables[0];
            string result = npoi.ExportToExcel("客户信息", dt);

            return string.IsNullOrWhiteSpace(result) ? Success("导出成功") : Error("导出失败");
        }


        #region App手机端 通讯录接口
        /// <summary>
        /// 获取手机端 没有用
        /// </summary>
        /// <returns></returns>
        public ActionResult GetAppList()
        {
            BLL.T_Cus_CustomerBase bll = new BLL.T_Cus_CustomerBase();
            string keyWord = string.Empty;
            DataTable dt = new DataTable();
            string sql = " 1=1 and F_DeleteFlag=0 ";
            try
            {
                keyWord = RequestString.GetQueryString("keyWord");
                if (!string.IsNullOrWhiteSpace(keyWord))
                {
                    sql += " and (F_Telephone like '%" + keyWord + "%' or F_Mobile like '%" + keyWord + "%' or F_ChargeTelephone like '%" + keyWord + "%' or F_Fax like '%" + keyWord + "%' or F_ChargeDept like '%" + keyWord + "%' or F_CustomerName like '%" + keyWord + "%') ";
                }
                List<Model.T_Cus_CustomerBase> modelList = bll.GetModelList(sql);
                return Success("获取成功",
                    modelList.Select(x => new
                    {
                        F_CustomerId = x.F_CustomerId,
                        F_CustomerName = x.F_CustomerName,  //客户名称
                        F_CustomerEName = x.F_CustomerEName,//联系人
                        F_Mobile = x.F_Mobile,              //联系人手机
                        F_Telephone = x.F_Telephone,        //联系人电话-单位电话
                        F_ChargeTelephone = x.F_ChargeTelephone,    //客户负责人联系方式-其他人电话
                        F_Fax = x.F_Fax,                    //传真
                        F_PostCode = x.F_PostCode,          //邮编
                        F_Email = x.F_Email,                //邮件
                        F_ChargeDept = x.F_ChargeDept,      //所在单位
                        F_ServiceDeptID = x.F_ServiceDeptID,//部门ID
                        F_IndustryId = x.F_IndustryId,      //职务ID
                        F_Sex = x.F_Sex,                    //性别
                        F_CreatedOn = x.F_CreatedOn         //创建时间
                    })
                    );

            }
            catch (Exception err)
            {
                return Error("错误：" + err.ToString());
            }
            finally
            {
                dt.Clear();
                dt.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 获取市民资料列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetPublicList()
        {
            //string sql = " and F_IsDelete=0";
            string sql = " and 1=1 ";
            DataTable dt = new DataTable();

            string phone = HttpUtility.UrlDecode(RequestString.GetQueryString("phone"));
            string name = HttpUtility.UrlDecode(RequestString.GetQueryString("name"));

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

            if (phone.Trim() != "" && phone != "undefined")
            {
                sql += " and (F_CusPhone like '%" + phone + "%' or F_ConPhone like '%" + phone + "%') ";
            }
            if (name.Trim() != "" && name != "undefined")
            {
                sql += " and (F_ConName like '%" + name + "%' or F_CusName like '%" + name + "%' ) ";
            }

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

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            int recordCount = 0;
            string table = "(select a.F_Id,a.F_CusName,a.F_CusSex,a.F_CusPhone,a.F_CusAddress,a.F_ConName,a.F_ConPhone,a.F_ZipCode,a.F_Email "
                + "from (select *,row_number() OVER (PARTITION BY F_CusPhone ORDER BY F_Id DESC) as xh FROM dbo.T_Bus_WorkOrder where isnull(F_CusPhone,'')<>'') as a "
                + "where a.xh = 1) as t";
            dt = BLL.PagerBLL.GetListPager(
                table,
                "F_Id",
                "*",
                sql,
                "ORDER BY F_Id desc",
                pagesize,
                pageindex,
                true,
                out recordCount);

            dt.Columns.Add("gdCount", typeof(string));
            dt.Columns.Add("ldCount", typeof(string));

            foreach (DataRow dr in dt.Rows)
            {
                string sqlgd = "select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_CusPhone='" + dr["F_CusPhone"].ToString() + "'";
                dr["gdCount"] = DbHelperSQL.GetSingle(sqlgd).ToString();

                string sqlhw = "select count(1) from dbo.T_Call_CallRecords where CallType=0  and CallNumber='" + dr["F_CusPhone"].ToString() + "'";
                dr["ldCount"] = DbHelperSQL.GetSingle(sqlgd).ToString();
            }

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

            return Content(obj.ToJson());

        }

    }
}