SYCW_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/customer/CustomerNewC...

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

namespace CallCenterApi.Interface.Controllers.customer
{
    public class CustomerNewController : BaseController
    {
        BLL.T_Cus_CustomerBaseNew cusbll = new BLL.T_Cus_CustomerBaseNew();
        BLL.T_Cus_ContactPersonNew conbll = new BLL.T_Cus_ContactPersonNew();
        BLL.T_Sys_Department deptbll = new BLL.T_Sys_Department();
        BLL.T_Sys_RoleInfo rolebll = new BLL.T_Sys_RoleInfo();
        BLL.T_Cus_CustomerFinance finbll = new BLL.T_Cus_CustomerFinance();
        #region 客户管理
        /// <summary>
        /// 获取客户列表
        /// </summary>
        /// <param name="code">客户编号</param>
        /// <param name="cmpname">公司名称</param>
        /// <param name="cmpaddress">公司地址</param>
        /// <param name="legname">法人姓名</param>
        /// <param name="legtel">法人电话</param>
        /// <param name="subtrade">所属行业</param>
        /// <param name="layer">客户信誉等级</param>
        /// <param name="bedept">客户归属部门</param>
        /// <param name="bereladd">客户归属涉税会计</param>
        /// <param name="beaudacc">客户归属审核会计</param>
        /// <param name="bemakeacc">客户归属做账会计</param>
        /// <param name="conname">联系人姓名</param>
        /// <param name="contel">联系人电话</param>
        /// <returns></returns>
        public ActionResult GetList(string keywords, string code, string cmpname, string cmpaddress,
            string legname, string legtel, string subtrade, string layer, string bedept,
            string berelacc, string beaudacc, string bemakeacc, string conname, string contel,
            int pageindex = 1, int pagesize = 10)
        {
            string sql = "";
            DataTable dt = new DataTable();
            #region 筛选条件
            if (!string.IsNullOrWhiteSpace(keywords))//关键字搜索
                sql += $" and F_KeyWords like '%" + keywords.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(code))//客户编号
                sql += $" and F_CustomerCode like '%" + code.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(cmpname))//公司名称
                sql += $" and F_KeyWords like '%" + cmpname.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(cmpaddress))//公司地址
                sql += $" and (F_CompanyAddress like '%" + cmpaddress.Trim() 
                    + "%' or F_RegisteredAddress like '%" + cmpaddress.Trim() 
                    + "%' or F_Address like '%" + cmpaddress.Trim() 
                    + "%' or F_Address1 like '%" + cmpaddress.Trim()
                    + "%' or F_Address2 like '%" + cmpaddress.Trim()
                    + "%' or F_Address3 like '%" + cmpaddress.Trim()
                    + "%' or F_Address4 like '%" + cmpaddress.Trim()
                    + "%')";
            if (!string.IsNullOrWhiteSpace(legname))//法人姓名
                sql += $" and F_LegalName like '%" + legname.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(legtel))//法人电话
                sql += $" and F_LegalTel like '%" + legtel.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(subtrade))//所属行业
                sql += $" and isnull(F_SubTrade,'') = '" + subtrade.Trim() + "'";
            if (!string.IsNullOrWhiteSpace(layer))//客户信誉等级
                sql += $" and isnull(F_Layer,'') = '" + layer.Trim() + "'";
            if (!string.IsNullOrWhiteSpace(bedept))//客户所属部门
                sql += $" and isnull(F_BeDept,'') = '" + bedept.Trim() + "'";
            if (!string.IsNullOrWhiteSpace(berelacc))//涉税会计
                sql += $" and F_BeRelatedAcc like '%" + berelacc.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(beaudacc))//审核会计
                sql += $" and F_BeAuditAcc like '%" + beaudacc.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(bemakeacc))//做账会计
                sql += $" and F_BeMakeAcc like '%" + bemakeacc.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(conname) || !string.IsNullOrWhiteSpace(contel))
            {
                var sqlcon = " where F_DeleteFlag=0 ";
                if (!string.IsNullOrWhiteSpace(conname)) // 联系人
                    sqlcon += " and F_Name like '%" + conname + "%'";
                if (!string.IsNullOrWhiteSpace(contel)) //联系人电话
                    sqlcon += " and F_Telephone like '%" + contel + "%'";
                sql += $" and F_CustomerId in (select F_CustomerId from T_Cus_ContactPersonNew " + sqlcon + " )";
            }
            #endregion

            int recordCount = 0;
            if(CurrentUser.UserData.F_RoleId==17)
            {
                sql += " and F_DeleteFlag=0";
            }
            if (!string.IsNullOrWhiteSpace(sql))
            {
                sql += " and F_DeleteFlag=0";
                dt = BLL.PagerBLL.GetListPager(
                    "T_Cus_CustomerBaseNew",
                    "F_CustomerId",
                    "*,dbo.GetDeptName(F_BeDept) as F_BeDeptName",
                    sql,
                    "ORDER BY F_CustomerId desc",
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);
                #region
                dt.Columns.Add("F_Contact");
                var dept = new BLL.T_Sys_Department().GetModelList("");
                foreach (DataRow dr in dt.Rows)
                {
                    #region 绑定联系人信息
                    var contact = "";
                    string cusid = dr["F_CustomerId"] != null ? dr["F_CustomerId"].ToString() : "";
                    if (cusid != "")
                    {
                        var conlist = conbll.GetModelList(" F_CustomerId=" + cusid);
                        if (conlist.Count > 0)
                        {
                            foreach (var item in conlist)
                            {
                                contact += item.F_Name + "(" + item.F_Telephone + "),";
                            }
                        }
                        dr["F_Contact"] = contact.TrimEnd(',');
                    }
                    #endregion
                }

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

            return Content(obj.ToJson());

        }
        /// <summary>
        /// 根据关键字搜索公司
        /// </summary>
        public ActionResult GetListByKey(string keywords)
        {
            string sql = "";
            if (!string.IsNullOrWhiteSpace(keywords))//关键字搜索
                sql += $" F_KeyWords like '%" + keywords.Trim() + "%'";
            var list = new List<Model.T_Cus_CustomerBaseNew>();
            if (!string.IsNullOrWhiteSpace(sql))
            {
                sql += " and F_DeleteFlag=0";
                list = cusbll.GetModelList(sql);
            }
            if (list.Count > 0)
                return Success("根据关键字获取公司信息", list.FirstOrDefault());
            else
                return Error("没有获取到相关公司，请重新搜索");
        }
        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetCustomer(int cusid)
        {
            if (cusid != 0)
            {
                Model.T_Cus_CustomerBaseNew userModel = cusbll.GetModel(cusid);
                if (userModel != null)
                {
                    #region 绑定部门
                    var deptname = "";
                    if (userModel.F_BeDept != null)
                    {
                        var deptmodel = deptbll.GetModel(userModel.F_BeDept.Value);
                        if (deptmodel != null)
                            deptname = deptmodel.F_DeptName;
                    }
                    #endregion
                    var conlist = conbll.GetModelList(" F_DeleteFlag=0 and F_CustomerId=" + cusid);
                    var finlist = finbll.GetModelList(" F_DeleteFlag=0 and  F_CustomerId=" + cusid);
                    var obj = new
                    {
                        CustomerBase = userModel,
                        BeDeptName = deptname,
                        ContactList = conlist,
                        FinaceList = finlist,
                    };
                    return Success("获取成功", obj);
                }
                else
                {
                    return Error("获取失败");
                }
            }
            else
            {
                return Error("参数传输失败");
            }
        }

        /// <summary>
        /// 通过来电号码获取来电弹屏左侧客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult GetCustomerByTel(string tel)
        {
            if (!string.IsNullOrWhiteSpace(tel))
            {
                var sql = $" and (F_LegalTel like '%" + tel.Trim() + "%') or  F_CustomerId in (select F_CustomerId from T_Cus_ContactPersonNew where F_Telephone like '%" + tel.Trim() + "%')";
                var userModel = new BLL.T_Cus_CustomerBaseNew().GetModelList(" F_DeleteFlag = 0 " + sql);
                if (userModel.Count() > 0)
                {
                    var model = userModel.Last();
                    int cusid = model.F_CustomerId;
                    var conlist = conbll.GetModelList(" F_DeleteFlag=0 and  F_CustomerId=" + cusid);
                    var finlist=finbll.GetModelList(" F_DeleteFlag=0 and  F_CustomerId=" + cusid);
                    #region 绑定部门
                    var deptname = "";
                    if (model.F_BeDept != null)
                    {
                        var deptmodel = deptbll.GetModel(model.F_BeDept.Value);
                        if (deptmodel != null)
                            deptname = deptmodel.F_DeptName;
                    }
                    #endregion
                    var obj = new
                    {
                        CustomerBase = model,
                        BeDeptName = deptname,
                        ContactList = conlist,
                        FinaceList = finlist,
                    };
                    return Success("获取成功", obj);
                }
                else
                {
                    return Success("获取成功");
                }
            }
            else
            {
                return Error("参数传输失败");
            }
        }

        /// <summary>
        /// 添加客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult Add(CustomerBaseNewInput input)
        {
            string usercode = CurrentUser.UserData.F_UserCode;
            #region 添加验证判断
            if (string.IsNullOrEmpty(input.F_CustomerCode))
                return Error("编号不能为空！");
            if (getunique(0, input.F_CustomerCode))
                return Error("编号已被占用，请重新输入！");
            #endregion
            var model = new Model.T_Cus_CustomerBaseNew();
            #region 保存客户基本信息
            model = inputtoobj(model, input);
            model.F_CreateBy = usercode;
            model.F_CreatedOn = DateTime.Now;
            model.F_DeleteFlag = 0;
            #endregion
            int n = cusbll.Add(model);
            if (n > 0)
            {
                #region 添加联系人
                if (input.ContactList != null && input.ContactList.Count > 0)
                {
                    var conmodel = new Model.T_Cus_ContactPersonNew();
                    conmodel.F_CustomerId = n;
                    conmodel.F_CreateBy = usercode;
                    conmodel.F_CreateOn = DateTime.Now;
                    conmodel.F_DeleteFlag = 0;
                    foreach (var item in input.ContactList)
                    {
                        conmodel.F_IsMain = item.F_IsMain;
                        conmodel.F_Name = item.F_Name;
                        conmodel.F_Telephone = item.F_Telephone;
                        conmodel.F_Duties = item.F_Duties;
                        conmodel.F_Email = item.F_Email;
                        conmodel.F_MSN = item.F_MSN;
                        conmodel.F_QQ = item.F_QQ;
                        conmodel.F_Remark = item.F_Remark;

                        conbll.Add(conmodel);
                    }
                }
                #endregion
                #region 添加财税情况
                var fincmodel = new CustomerFinanceInput();
                fincmodel.F_CustomerId = n;
                fincmodel.F_CustomerCode = model.F_CustomerCode;
                fincmodel.F_CreateBy = usercode;
                savefin(input.FinanceInfos);
                #endregion
                return Success("新增成功！");
            }
            else
                return Error("新增失败！");
        }

        /// <summary>
        /// 修改客户信息
        /// </summary>
        /// <returns></returns>
        public ActionResult Update(CustomerBaseNewInput input)
        {
            string usercode = CurrentUser.UserData.F_UserCode;
            #region 添加验证判断
            if (input.F_CustomerId <= 0)
                return Error("参数错误！");
            if (getunique(input.F_CustomerId, input.F_CustomerCode))
                return Error("编号已被占用，请重新输入！");
            #endregion
            var model = cusbll.GetModel(input.F_CustomerId);
            #region 保存客户基本信息
            model = inputtoobj(model, input);
            #endregion
            bool n = cusbll.Update(model);
            if (n)
            {
                return Success("保存成功！");
            }
            else
                return Error("保存失败！");
        }

        /// <summary>
        /// 根据传入的对象和input的内容返回对象
        /// </summary>
        private Model.T_Cus_CustomerBaseNew inputtoobj(Model.T_Cus_CustomerBaseNew model,CustomerBaseNewInput input)
        {
            var newmodel = model;
            if (!string.IsNullOrWhiteSpace(input.F_CustomerCode))
            {
                model.F_CustomerCode = input.F_CustomerCode;
            }
            else
                model.F_CustomerCode = model.F_CustomerCode;
            if (!string.IsNullOrWhiteSpace(input.F_CompanyName))
            {
                model.F_CompanyName = input.F_CompanyName;
                #region 处理关键字
                var keyword = model.F_CompanyName;
                var keyconfig = Configs.GetValue("keystring");
                var keystring = keyconfig.Split(',');
                foreach (var item in keystring)
                {
                    if (model.F_CompanyName.Contains(item))
                        keyword = keyword.Replace(item, "");
                }
                model.F_KeyWords = keyword;
                #endregion
            }
            if (!string.IsNullOrWhiteSpace(input.F_RegisteredAddress))
                model.F_RegisteredAddress = input.F_RegisteredAddress;
            if (!string.IsNullOrWhiteSpace(input.F_CompanyAddress))
                model.F_CompanyAddress = input.F_CompanyAddress;
            if (!string.IsNullOrWhiteSpace(input.F_LegalName))
                model.F_LegalName = input.F_LegalName;
            if (!string.IsNullOrWhiteSpace(input.F_LegalIDCards))
                model.F_LegalIDCards = input.F_LegalIDCards;
            if (!string.IsNullOrWhiteSpace(input.F_LegalTel))
                model.F_LegalTel = input.F_LegalTel;
            if (!string.IsNullOrWhiteSpace(input.F_Qualification))
                model.F_Qualification = input.F_Qualification;
            if (!string.IsNullOrWhiteSpace(input.F_Layer))
                model.F_Layer = input.F_Layer;
            if (!string.IsNullOrWhiteSpace(input.F_InvoiceRange))
                model.F_InvoiceRange = input.F_InvoiceRange;
            if (!string.IsNullOrWhiteSpace(input.F_SubTrade))
                model.F_SubTrade = input.F_SubTrade;
            if (!string.IsNullOrWhiteSpace(input.F_TaxCategory))
                model.F_TaxCategory = input.F_TaxCategory;
            if (!string.IsNullOrWhiteSpace(input.F_TaxPointDes))
                model.F_TaxPointDes = input.F_TaxPointDes;
            if (!string.IsNullOrWhiteSpace(input.F_WxPassword))
                model.F_WxPassword = input.F_WxPassword;
            if (input.F_BeDept > 0)
                model.F_BeDept = input.F_BeDept;
            if (!string.IsNullOrWhiteSpace(input.F_FinancialManager))
                model.F_FinancialManager = input.F_FinancialManager;
            if (!string.IsNullOrWhiteSpace(input.F_BusinessOwner))
                model.F_BusinessOwner = input.F_BusinessOwner;
            if (!string.IsNullOrWhiteSpace(input.F_BeRelatedAcc))
                model.F_BeRelatedAcc = input.F_BeRelatedAcc;
            if (!string.IsNullOrWhiteSpace(input.F_BeAuditAcc))
                model.F_BeAuditAcc = input.F_BeAuditAcc;
            if (!string.IsNullOrWhiteSpace(input.F_BeMakeAcc))
                model.F_BeMakeAcc = input.F_BeMakeAcc;
            if (!string.IsNullOrWhiteSpace(input.F_Remark1))
                model.F_Remark1 = input.F_Remark1;
            if (!string.IsNullOrWhiteSpace(input.F_Remark2))
                model.F_Remark2 = input.F_Remark2;
            if (!string.IsNullOrWhiteSpace(input.F_Remark3))
                model.F_Remark3 = input.F_Remark3;
            if (!string.IsNullOrWhiteSpace(input.F_CusService))
                model.F_CusService = input.F_CusService;
            if (!string.IsNullOrWhiteSpace(input.F_HandoverDate))
                model.F_HandoverDate = input.F_HandoverDate;
            if (input.F_ISBankSlips > 0)
                model.F_ISBankSlips = input.F_ISBankSlips;
            else
                model.F_ISBankSlips = 0;
            if (!string.IsNullOrWhiteSpace(input.F_Address))
                model.F_Address = input.F_Address;
            if (!string.IsNullOrWhiteSpace(input.F_Address1))
                model.F_Address1 = input.F_Address1;
            if (!string.IsNullOrWhiteSpace(input.F_Address2))
                model.F_Address2 = input.F_Address2;
            if (!string.IsNullOrWhiteSpace(input.F_Address3))
                model.F_Address3 = input.F_Address3;
            if (!string.IsNullOrWhiteSpace(input.F_Address4))
                model.F_Address4 = input.F_Address4;
            if (!string.IsNullOrWhiteSpace(input.F_Charges))
                model.F_Charges = input.F_Charges;
            if (input.F_ChargeType > 0)
                model.F_ChargeType = input.F_ChargeType;
            if (!string.IsNullOrWhiteSpace(input.F_CompanyLogo))
                model.F_CompanyLogo = input.F_CompanyLogo;
            if (input.F_CycleStart != null)
                model.F_CycleStart = input.F_CycleStart;
            if (input.F_CycleEnd != null)
                model.F_CycleEnd = input.F_CycleEnd;
            if (!string.IsNullOrWhiteSpace(input.F_TaxNumber))
                model.F_TaxNumber = input.F_TaxNumber;
            if (input.F_FeeExpires != null)
                model.F_FeeExpires = input.F_FeeExpires;
            return model;
        }

        /// <summary>
        /// 验证客户编号是否唯一
        /// </summary>
        private bool getunique(int id, string code)
        {
            var sql = " F_DeleteFlag=0 and F_CustomerCode='" + code + "'";
            if (id > 0)
                sql += " and F_CustomerId<>"+id;
            var count=cusbll.GetModelList(sql).Count();
            
            return count > 0;
        }
        /// <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 + ",";
                }
                if (!string.IsNullOrEmpty(idd.Trim()))
                {
                    if (cusbll.DeleteList(idd.TrimEnd(',')))
                    {
                        conbll.DeleteListByCusid(idd.TrimEnd(','));
                        return Success("设置成功");
                    }
                    else
                    {
                        return Error("设置失败");
                    }
                }
                else
                {
                    return Error("请选择用户");
                }
            }
            else
            {
                return Error("获取参数失败");
            }
        }
        #endregion
        #region 联系人
        /// <summary>
        /// 获取客户联系人列表
        /// </summary>
        public ActionResult GetConList(string tel, string cusid, int pageindex = 1, int pagesize = 10)
        {
            string sql = "";
            DataTable dt = new DataTable();
            #region 筛选条件
            if (!string.IsNullOrWhiteSpace(cusid))
                sql += " and F_CustomerId=" + cusid + "";
            if (!string.IsNullOrWhiteSpace(tel)) //联系人电话
                sql += " and F_Telephone like '%" + tel + "%'";
            #endregion
            int recordCount = 0;
            if (!string.IsNullOrWhiteSpace(sql))
            {
                sql += " and F_DeleteFlag=0";
                dt = BLL.PagerBLL.GetListPager(
                    "T_Cus_ContactPersonNew",
                    "F_ManId",
                    "*",
                    sql,
                    "ORDER BY F_ManId desc",
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);
            }
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };

            return Content(obj.ToJson());

        }
        /// <summary>
        /// 添加联系人
        /// </summary>
        /// <param name="coninput"></param>
        /// <returns></returns>
        public ActionResult AddContact(ContactPersonNewInput coninput)
        {
            string usercode = CurrentUser.UserData.F_UserCode;
            #region 添加验证判断
            if (coninput.F_CustomerId <= 0)
                return Error("客户ID获取失败！");
            #endregion
            var conmodel = new Model.T_Cus_ContactPersonNew();
            conmodel.F_CustomerId = coninput.F_CustomerId;
            conmodel.F_IsMain = coninput.F_IsMain;
            conmodel.F_Name = coninput.F_Name;
            conmodel.F_Telephone = coninput.F_Telephone;
            conmodel.F_Duties = coninput.F_Duties;
            conmodel.F_Email = coninput.F_Email;
            conmodel.F_MSN = coninput.F_MSN;
            conmodel.F_QQ = coninput.F_QQ;
            conmodel.F_Remark = coninput.F_Remark;
            conmodel.F_CreateBy = usercode;
            conmodel.F_CreateOn = DateTime.Now;
            conmodel.F_DeleteFlag = 0;
            int n = conbll.Add(conmodel);
            if (n > 0)
                return Success("联系人保存成功！");
            else
                return Error("联系人保存失败！");
        }

        /// <summary>
        /// 修改联系人
        /// </summary>
        /// <param name="coninput"></param>
        /// <returns></returns>
        public ActionResult UpdateContact(ContactPersonNewInput coninput)
        {
            string usercode = CurrentUser.UserData.F_UserCode;
            #region 添加验证判断
            if (coninput.F_ManId <= 0)
                return Error("参数错误！");
            if (coninput.F_CustomerId <= 0)
                return Error("客户ID获取失败！");
            #endregion
            var conmodel = conbll.GetModel(coninput.F_ManId);
            conmodel.F_CustomerId = coninput.F_CustomerId;
            conmodel.F_IsMain = coninput.F_IsMain;
            conmodel.F_Name = coninput.F_Name;
            conmodel.F_Telephone = coninput.F_Telephone;
            conmodel.F_Duties = coninput.F_Duties;
            conmodel.F_Email = coninput.F_Email;
            conmodel.F_MSN = coninput.F_MSN;
            conmodel.F_QQ = coninput.F_QQ;
            conmodel.F_Remark = coninput.F_Remark;
            var n = conbll.Update(conmodel);
            if (n)
                return Success("联系人保存成功！");
            else
                return Error("联系人保存失败！");
        }

        /// <summary>
        /// 删除联系人
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult DelContact(string[] ids)
        {
            if (ids != null && ids.Length > 0)
            {
                string idd = " ";
                foreach (string str in ids)
                {
                    idd += str + ",";
                }
                if (!string.IsNullOrEmpty(idd.Trim()))
                {
                    if (conbll.DeleteList(idd.TrimEnd(',')))
                    {
                        return Success("设置成功");
                    }
                    else
                    {
                        return Error("设置失败");
                    }
                }
                else
                {
                    return Error("请选择联系人");
                }
            }
            else
            {
                return Error("获取参数失败");
            }
        }
        #endregion
        #region 财税情况
        /// <summary>
        /// 根据客户id获取财税情况
        /// </summary>
        public ActionResult GetFinc(int cusid)
        {
            if (cusid != 0)
            {
                var model = finbll.GetModelList(" F_CustomerId=" + cusid).FirstOrDefault();
                if (model != null)
                    return Success("获取财税情况成功", model);
            }
            return Error("获取财税情况失败");
        }
        /// <summary>
        /// 保存财税情况
        /// </summary>
        public ActionResult SaveFinance(CustomerFinanceInput input)
        {
            string usercode = CurrentUser.UserData.F_UserCode;
            input.F_CreateBy = usercode;
            var res = savefin(input);
            if (res)
                return Success("保存成功");
            else
                return Error("保存失败");
        }
        /// <summary>
        /// 公用方法保存财税情况
        /// </summary>
        public bool savefin(CustomerFinanceInput input)
        {
            Model.T_Cus_CustomerFinance model = new Model.T_Cus_CustomerFinance();
            if (input.F_CusFinId > 0)
            {
                model = finbll.GetModel(input.F_CusFinId);
                if (model == null)
                    model = new Model.T_Cus_CustomerFinance();
            }
            #region 设置信息
            model.F_MonthState = input.F_MonthState;
            model.F_AnnualIncome = input.F_AnnualIncome;
            model.F_AnnualProfit = input.F_AnnualProfit;
            model.F_AnnualPersonCount = input.F_AnnualPersonCount;
            model.F_AnnualWages = input.F_AnnualWages;
            model.F_AnnualCost = input.F_AnnualCost;
            model.F_QuarterlyCost = input.F_QuarterlyCost;
            model.F_QuarterlyPersonCount = input.F_QuarterlyPersonCount;
            model.F_QuarterlyWages = input.F_QuarterlyWages;
            model.F_QuarterlyIncome = input.F_QuarterlyIncome;
            model.F_QPVAT = input.F_QPVAT;
            model.F_QPSurtax = input.F_QPSurtax;
            model.F_QPTaxes = input.F_QPTaxes;
            model.F_QPConTax = input.F_QPConTax;
            model.F_QPPerinTax = input.F_QPPerinTax;
            model.F_QPStampTax = input.F_QPStampTax;
            model.F_QPOtherTax = input.F_QPOtherTax;
            #endregion
            var res = false;
            if (input.F_CusFinId > 0)
            {
                res = finbll.Update(model);
            }
            else
            {
                model.F_CustomerId = input.F_CustomerId;
                model.F_CustomerCode = input.F_CustomerCode;
                model.F_CreateBy = input.F_CreateBy;
                model.F_CreatedOn = DateTime.Now;
                model.F_DeleteFlag = 0;
                res = finbll.Add(model)>0;
            }
            if (res)
                savefinlog(input);
            return res;
        }
        #region 财税情况日志
        private void savefinlog(CustomerFinanceInput input)
        {
            var model = new Model.T_Cus_CustomerFinanceLog();
            #region 设置信息
            model.F_CustomerId = input.F_CustomerId;
            model.F_CustomerCode = input.F_CustomerCode;
            model.F_CreateBy = input.F_CreateBy;
            model.F_CreatedOn = DateTime.Now;
            model.F_DeleteFlag = 0;
            model.F_MonthState = input.F_MonthState;
            model.F_AnnualIncome = input.F_AnnualIncome;
            model.F_AnnualProfit = input.F_AnnualProfit;
            model.F_AnnualPersonCount = input.F_AnnualPersonCount;
            model.F_AnnualWages = input.F_AnnualWages;
            model.F_AnnualCost = input.F_AnnualCost;
            model.F_QuarterlyCost = input.F_QuarterlyCost;
            model.F_QuarterlyPersonCount = input.F_QuarterlyPersonCount;
            model.F_QuarterlyWages = input.F_QuarterlyWages;
            model.F_QuarterlyIncome = input.F_QuarterlyIncome;
            model.F_QPVAT = input.F_QPVAT;
            model.F_QPSurtax = input.F_QPSurtax;
            model.F_QPTaxes = input.F_QPTaxes;
            model.F_QPConTax = input.F_QPConTax;
            model.F_QPPerinTax = input.F_QPPerinTax;
            model.F_QPStampTax = input.F_QPStampTax;
            model.F_QPOtherTax = input.F_QPOtherTax;
            #endregion
            new BLL.T_Cus_CustomerFinanceLog().Add(model);
        }
        #endregion
        #endregion
        #region 导入客户资料
        /// <summary>
        /// 导入excel
        /// </summary>
        public ActionResult ImportExcel()
        {
            string usercode = CurrentUser.UserData.F_UserCode;
            if (!string.IsNullOrWhiteSpace(usercode))
            {
                HttpPostedFile _upFile = RequestString.GetFile("upFile");
                if (_upFile != null)
                {
                    int headrow = 0;
                    #region 上传文件
                    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);
                    #endregion
                    DataTable dt = new DataTable();
                    #region 读取excel中内容
                    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());
                    }
                    #endregion
                    int count = 0;
                    for (int i = (sheet.FirstRowNum + headrow + 1); i <= sheet.LastRowNum; i++)
                    {
                        #region 数据入库
                        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 cusmodel = new Model.T_Cus_CustomerBaseNew();
                        var conmodel = new Model.T_Cus_ContactPersonNew();
                        #region  信息对应
                        //报税会计  做账会计  编号  客服  公司名称  负责人电话       交接日期
                        cusmodel.F_CustomerCode = dataRow["编号"].ToString();
                        //判断编号唯一
                        if (getunique(0, cusmodel.F_CustomerCode))
                            continue;
                        cusmodel.F_CompanyName = dataRow["公司名称"].ToString();
                        #region 处理关键字
                        var keyword = cusmodel.F_CompanyName;
                        var keyconfig = Configs.GetValue("keystring");
                        var keystring = keyconfig.Split(',');
                        foreach (var item in keystring)
                        {
                            if (cusmodel.F_CompanyName.Contains(item))
                                keyword = keyword.Replace(item, "");
                        }
                        cusmodel.F_KeyWords = keyword;
                        #endregion
                        cusmodel.F_BeRelatedAcc = dataRow["报税会计"].ToString();
                        cusmodel.F_BeMakeAcc = dataRow["做账会计"].ToString();
                        cusmodel.F_HandoverDate = dataRow["交接日期"].ToString();
                        cusmodel.F_CusService = dataRow["客服"].ToString();
                        cusmodel.F_CompanyAddress= dataRow["公司地址"].ToString();
                        cusmodel.F_RegisteredAddress = dataRow["注册地址"].ToString();
                        cusmodel.F_Address = dataRow["地址"].ToString();
                        cusmodel.F_Address1 = dataRow["地址1"].ToString();
                        cusmodel.F_Address2 = dataRow["地址2"].ToString();
                        cusmodel.F_Address3 = dataRow["地址3"].ToString();
                        cusmodel.F_Address4 = dataRow["地址4"].ToString();
                        cusmodel.F_WxPassword = "123456";
                        cusmodel.F_CreateBy = usercode;
                        cusmodel.F_CreatedOn = DateTime.Now;
                        cusmodel.F_DeleteFlag = 0;
                        #endregion
                        int n = cusbll.Add(cusmodel);

                        if (n > 0)
                        {
                            #region 添加联系人
                            conmodel.F_CustomerId = n;
                            conmodel.F_CreateBy = usercode;
                            conmodel.F_CreateOn = DateTime.Now;
                            conmodel.F_DeleteFlag = 0;
                            conmodel.F_IsMain = true;
                            if ((dataRow["负责人电话"] != null && dataRow["负责人电话"].ToString() != "")
                                   || (dataRow["负责人姓名"] != null && dataRow["负责人姓名"].ToString() != ""))
                            {
                                if (dataRow["负责人电话"] != null && dataRow["负责人电话"].ToString() != "")
                                    conmodel.F_Telephone = dataRow["负责人电话"].ToString();
                                if (dataRow["负责人姓名"] != null && dataRow["负责人姓名"].ToString() != "")
                                    conmodel.F_Name = dataRow["负责人姓名"].ToString();
                                conbll.Add(conmodel);
                            }
                            for (int nn = 0; nn <= 10; nn++)
                            {
                                if ((dataRow["负责人电话" + nn.ToString()] != null && dataRow["负责人电话" + nn.ToString()].ToString() != "") 
                                    || (dataRow["负责人姓名" + nn.ToString()] != null && dataRow["负责人姓名" + nn.ToString()].ToString() != ""))
                                {
                                    if (dataRow["负责人电话" + nn.ToString()] != null && dataRow["负责人电话" + nn.ToString()].ToString() != "")
                                        conmodel.F_Telephone = dataRow["负责人电话" + nn.ToString()].ToString();
                                    if (dataRow["负责人姓名" + nn.ToString()] != null && dataRow["负责人姓名" + nn.ToString()].ToString() != "")
                                        conmodel.F_Name = dataRow["负责人姓名" + nn.ToString()].ToString();
                                    conbll.Add(conmodel);
                                }
                            }

                            #endregion
                            #region 添加财税情况
                            var finmodel = new Model.T_Cus_CustomerFinance();
                            finmodel.F_CustomerId = n;
                            finbll.Add(finmodel);
                            #endregion
                            count++;
                        }
                        #endregion
                    }
                    if (dt == null || dt.Rows.Count == 0)
                        return Error("文件没有数据");
                    else
                    {
                        var jstr = DataTableToJson(dt);
                        if (count > 0)
                        {
                            return Success("导入成功 " + count + "条信息", jstr);
                        }
                        else
                        {
                            return Success("导入成功 " + count + "条信息", jstr);
                        }
                    }
                }
                return Error("数据源上传失败");
            }
            return Error("用户登录失败，请重新登录");
        }
        public string DataTableToJson(DataTable dt)
        {
            string JsonString = string.Empty;
            JsonString = JsonConvert.SerializeObject(dt);
            return JsonString;
        }
        #endregion
    }
}