OA2021/DAL/NewClientDataService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using System.Data.SqlClient;
using System.Data;
namespace DAL
{
    public class NewClientDataService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<NewClientData> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<NewClientData>.excuteSql(new NewClientData(), "NewClientData", sql, CommandType.Text, param);
        }

        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        NewClientData excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<NewClientData> cdList = excuteSql(sql, param);
            //判断集合是否为空
            if (cdList == null || cdList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return cdList[0];
        }

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="cd"></param>
        /// <returns></returns>
        public bool AddClientData(NewClientData cd)
        {
            string sql = "insert into NewClientData values(@Number,@Lvl,@Client,@Weight,@ClientShort,@Contact,@Gender,@Passport,@PassportDate,@Job,@TelePhone,@Phone,@Email,@Location,@Address,@Birthday,@OtherInfo,@Ascription,@Remarks,@FZR,@WeChat,@Category,@PreDele,@FinlishedDele,@OPer,@OPdate,@IsDel)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Number",cd.Number),
                new SqlParameter("@Lvl",cd.Lvl),
                new SqlParameter("@Client",cd.Client),
                new SqlParameter("@Weight",cd.Weight),
                new SqlParameter("@ClientShort",cd.ClientShort),
                new SqlParameter("@Contact",cd.Contact),
                new SqlParameter("@Gender",cd.Gender),
                new SqlParameter("@Passport",cd.Passport),
                new SqlParameter("@PassportDate",cd.PassportDate),
                new SqlParameter("@Job",cd.Job),
                new SqlParameter("@TelePhone",cd.TelePhone),
                new SqlParameter("@Phone",cd.Phone),
                new SqlParameter("@Email",cd.Email),
                new SqlParameter("@Location",cd.Location),
                new SqlParameter("@Address",cd.Address),
                new SqlParameter("@Birthday",cd.Birthday),
                new SqlParameter("@OtherInfo",cd.OtherInfo),
                new SqlParameter("@Ascription",cd.Ascription),
                new SqlParameter("@Remarks",cd.Remarks),
                new SqlParameter("@FZR",cd.FZR),
                new SqlParameter("@WeChat",cd.WeChat),
                new SqlParameter("@Category",cd.Category),
                new SqlParameter("@PreDele",cd.PreDele),
                new SqlParameter("@FinlishedDele",cd.FinlishedDele),
                new SqlParameter("@OPer",cd.OPer),
                new SqlParameter("@OPdate",cd.OPdate),
                new SqlParameter("@IsDel",cd.IsDel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditClientData(NewClientData cd)
        {
            string sql = "update NewClientData set Number=@Number,Lvl=@Lvl,Client=@Client,Weight=@Weight,ClientShort=@ClientShort,Contact=@Contact,Gender=@Gender,Passport=@Passport,PassportDate=@PassportDate,Job=@Job,TelePhone=@TelePhone,Phone=@Phone,Email=@Email,Location=@Location,Address=@Address,Birthday=@Birthday,OtherInfo=@OtherInfo,Ascription=@Ascription,Remarks=@Remarks,FZR=@FZR,WeChat=@WeChat,Category=@Category,PreDele=@PreDele,FinlishedDele=@FinlishedDele,OPer=@OPer,OPdate=@OPdate,IsDel=@IsDel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[] {
                new SqlParameter("@Number",cd.Number),
                new SqlParameter("@Lvl",cd.Lvl),
                new SqlParameter("@Client",cd.Client),
                 new SqlParameter("@Weight",cd.Weight),
                new SqlParameter("@ClientShort",cd.ClientShort),
                new SqlParameter("@Contact",cd.Contact),
                new SqlParameter("@Gender",cd.Gender),
                new SqlParameter("@Passport",cd.Passport),
                new SqlParameter("@PassportDate",cd.PassportDate),
                new SqlParameter("@Job",cd.Job),
                new SqlParameter("@TelePhone",cd.TelePhone),
                new SqlParameter("@Phone",cd.Phone),
                new SqlParameter("@Email",cd.Email),
                new SqlParameter("@Location",cd.Location),
                new SqlParameter("@Address",cd.Address),
                new SqlParameter("@Birthday",cd.Birthday),
                new SqlParameter("@OtherInfo",cd.OtherInfo),
                new SqlParameter("@Ascription",cd.Ascription),
                new SqlParameter("@Remarks",cd.Remarks),
                new SqlParameter("@FZR",cd.FZR),
                new SqlParameter("@WeChat",cd.WeChat),
                new SqlParameter("@Category",cd.Category),
                new SqlParameter("@PreDele",cd.PreDele),
                new SqlParameter("@FinlishedDele",cd.FinlishedDele),
                new SqlParameter("@OPer",cd.OPer),
                new SqlParameter("@OPdate",cd.OPdate),
                new SqlParameter("@IsDel",cd.IsDel),
                new SqlParameter("@Id",cd.ID)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelClientData(int id)
        {
            string opdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            if (SqlHelper.ExecuteNonQuery("update NewClientData set IsDel = 1 , opdate='" + opdate + "' where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 根据Lvl，Contact,Client查询对象信息
        /// </summary>
        /// <param name="Contact">客户名称</param>
        /// <param name="Client">单位名称</param>
        /// <returns>返回空或者单个对象信息</returns>
        public NewClientData GetClientData(string lvl, string Contact, string Client)
        {
            //调用获取单个对象的方法
            return excuteType("select * from NewClientData where  Lvl= @Lvl and Contact = @Contact and Client = @Client and IsDel = 0", new SqlParameter("@Lvl", lvl), new SqlParameter("@Contact", Contact), new SqlParameter("@Client", Client));
        }


        /// <summary>
        /// 根据id查询对象信息
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>返回空或者单个对象信息</returns>
        public NewClientData GetClientDatabyid(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select * from NewClientData where id = @id and IsDel = 0", new SqlParameter("@id", id));
        }


        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>返回空或者单个对象信息</returns>
        public List<NewClientData> GetALL()
        {
            //调用获取单个对象的方法
            return excuteSql("select * from NewClientData where IsDel = 0 order by Id"); //
        }

        public List<NewClientData> GetALL(string fzrName,bool cbodz)
        {
            string sql = $"select * from NewClientData where IsDel = 0  ";
            string sqlWhere = string.Empty;

            if (cbodz)
            {
                sqlWhere += $" and (FZR = '|{fzrName}|' or fzr = '|{fzrName}' or fzr = '||{fzrName}' or fzr = '|{fzrName}||'  or fzr = '|||{fzrName}'  or fzr = '|{fzrName}|||') ";
            }
            else
            {
                sqlWhere = $"  and FZR like '%{fzrName}%' order by Id";
            }

            return excuteSql(sql + sqlWhere); //
        }

        /// <summary>
        /// 根据Contact,Client查询对象信息
        /// </summary>
        /// <param name="Contact">客户名称</param>
        /// <param name="Client">单位名称</param>
        /// <returns>返回空或者单个对象信息</returns>
        public List<NewClientData> GetClientDatabyCC(string Contact, string Client)
        {

            string sql = "select * from NewClientData where IsDel = 0";
            if (Contact != null && Contact != "")
                sql = sql + " like %@Contact%";
            if (Contact != null && Contact != "")
                sql = sql + " like %@Client%";

            //调用获取单个对象的方法
            return excuteSql(sql, new SqlParameter("@Contact", Contact), new SqlParameter("@Client", Client));
        }

        public List<NewClientData> GetClientDatabyUid(int pageIndex, out int sumPage, out int totalRecord, string lvl, string Contact, string Client, string area, int uid, string category, string range, string ascription)
        {
            string sqlwhere = "IsDel = 0 and (Contact like '%" + Contact + "%'or Remarks like '%" + Contact + "%') ";

            if (!string.IsNullOrEmpty(Client) || Client != "")
            {
                string temp = "";
                foreach (char s in Client)
                {
                    temp = temp + "%" + s;
                }
                sqlwhere = sqlwhere + " and Client like '" + temp + "%'";
            }

            if (!string.IsNullOrEmpty(area) || area != "")
            {
                string temparea = "";
                foreach (char s in area)
                {
                    temparea = temparea + "%" + s;
                }
                sqlwhere = sqlwhere + " and Location like '" + temparea + "%'";
            }


            if (!string.IsNullOrEmpty(uid.ToString()) || uid.ToString() != "")
            {
                string name = new UsersService().GetUsersByID(uid).CnName;
                sqlwhere = sqlwhere + " and (OPer='" + uid + "'" + " or FZR like '%" + name + "%')";
            }


            if (category != "全部")
                sqlwhere += " and Category = '" + category + "'";
            if (lvl != "全部")
                sqlwhere += " and Lvl = '" + lvl + "'";
            //                    case "青海":
            //            ddlCategory.DataSource = new SetDataService().GetAllOrderByRemark(37).FindAll(c => c.Name.Contains("青海"));
            //break;
            //        case "陕西":
            //            ddlCategory.DataSource = new SetDataService().GetAllOrderByRemark(37).FindAll(c => c.Name.Contains("陕西") || c.Name.Contains("西安"));
            //break;
            //        case "宁夏":
            //            ddlCategory.DataSource = new SetDataService().GetAllOrderByRemark(37).FindAll(c => c.Name.Contains("宁夏") || c.Name.Contains("银川"));
            //break;
            //        case "甘肃":
            //            ddlCategory.DataSource = new SetDataService().GetAllOrderByRemark(37).FindAll(c => c.Name.Contains("甘肃") || c.Name.Contains("兰州"));
            //break;
            switch (range)
            {
                case "四川":
                    sqlwhere += " and (Lvl like '%四%川%' or Lvl like '%成%都%')";
                    break;
                case "贵州":
                    sqlwhere += " and (Lvl like '%贵%州%' or Lvl like '%贵%阳%')";
                    break;
                case "云南":
                    sqlwhere += " and (Lvl like '%云%南%' or Lvl like '%昆%明%')";
                    break;
                case "重庆":
                    sqlwhere += " and (Lvl like '%重庆%')";
                    break;
                case "西藏":
                    sqlwhere += " and (Lvl like '%西%藏%' or Lvl like '%拉%萨%')";
                    break;
                case "青海":
                    sqlwhere += " and (Lvl like '%青%海%' or Lvl like '%西%宁%')";
                    break;
                case "陕西":
                    sqlwhere += " and (Lvl like '%陕%西%' or Lvl like '%西%安%')";
                    break;
                case "宁夏":
                    sqlwhere += " and (Lvl like '%宁%夏%' or Lvl like '%银%川%')";
                    break;
                case "甘肃":
                    sqlwhere += " and (Lvl like '%甘%肃%' or Lvl like '%兰%州%')";
                    break;
                case "新疆":
                    sqlwhere += " and (Lvl like '%新%疆%' or Lvl like '%乌%鲁%木%齐%')";
                    break;
                case "全部":
                    break;
            }
            if (ascription != "全部")
            {
                sqlwhere += "and Ascription  like  '%" + ascription + "%'";
            }

            return PageBase<NewClientData>.excutePageSql(new NewClientData(), "NewClientData", "NewClientData", "*", "weight desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        public List<NewClientData> GetClientData(int pageIndex, out int sumPage, out int totalRecord, string lvl, string FZR, string Contact, string Client, string area, string category, string range, string ascription)
        {

            string sqlwhere = "IsDel = 0 and (Contact like '%" + Contact + "%'or Remarks like '%" + Contact + "%')";

            if (!string.IsNullOrEmpty(Client) || Client != "")
            {
                string temp = "";
                foreach (char s in Client)
                {
                    temp = temp + "%" + s;
                }
                sqlwhere = sqlwhere + " and Client like '" + temp + "%'";
            }

            if (!string.IsNullOrEmpty(area) || area != "")
            {
                string temparea = "";
                foreach (char s in area)
                {
                    temparea = temparea + "%" + s;
                }
                sqlwhere = sqlwhere + " and Location like '" + temparea + "%'";
            }
            if (category != "全部")
                sqlwhere += " and Category = '" + category + "'";

            if (lvl != "全部")
                sqlwhere += " and Lvl = '" + lvl + "'";

            if (FZR != "全部")
                sqlwhere += " and FZR like '%" + FZR + "%'";

            switch (range)
            {
                case "四川":
                    sqlwhere += " and (Lvl like '%四%川%' or Lvl like '%成%都%')";
                    break;
                case "贵州":
                    sqlwhere += " and (Lvl like '%贵%州%' or Lvl like '%贵%阳%')";
                    break;
                case "云南":
                    sqlwhere += " and (Lvl like '%云%南%' or Lvl like '%昆%明%')";
                    break;
                case "重庆":
                    sqlwhere += " and (Lvl like '%重庆%')";
                    break;
                case "西藏":
                    sqlwhere += " and (Lvl like '%西%藏%' or Lvl like '%拉%萨%')";
                    break;
                case "青海":
                    sqlwhere += " and (Lvl like '%青%海%' or Lvl like '%西%宁%')";
                    break;
                case "陕西":
                    sqlwhere += " and (Lvl like '%陕%西%' or Lvl like '%西%安%')";
                    break;
                case "宁夏":
                    sqlwhere += " and (Lvl like '%宁%夏%' or Lvl like '%银%川%')";
                    break;
                case "甘肃":
                    sqlwhere += " and (Lvl like '%甘%肃%' or Lvl like '%兰%州%')";
                    break;
                case "新疆":
                    sqlwhere += " and (Lvl like '%新%疆%' or Lvl like '%乌%鲁%木%齐%')";
                    break;
                case "全部":
                    break;
            }

            if (ascription != "全部")
            {
                sqlwhere += "and Ascription  like  '%" + ascription + "%'";
            }

            return PageBase<NewClientData>.excutePageSql(new NewClientData(), "NewClientData", "NewClientData", "*", "weight desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        public List<NewClientData> GetClientDataByCboxDz(int pageIndex, out int sumPage, out int totalRecord, string lvl, string FZR, string Contact, string Client, string area, string category, string range, string ascription)
        {

            string sqlwhere = "IsDel = 0 and (Contact like '%" + Contact + "%'or Remarks like '%" + Contact + "%')";

            if (!string.IsNullOrEmpty(Client) || Client != "")
            {
                string temp = "";
                foreach (char s in Client)
                {
                    temp = temp + "%" + s;
                }
                sqlwhere = sqlwhere + " and Client like '" + temp + "%'";
            }

            if (!string.IsNullOrEmpty(area) || area != "")
            {
                string temparea = "";
                foreach (char s in area)
                {
                    temparea = temparea + "%" + s;
                }
                sqlwhere = sqlwhere + " and Location like '" + temparea + "%'";
            }
            if (category != "全部")
                sqlwhere += " and Category = '" + category + "'";

            if (lvl != "全部")
                sqlwhere += " and Lvl = '" + lvl + "'";

            if (FZR != "全部") {
                sqlwhere += $" and (FZR = '|{FZR}|' or fzr = '|{FZR}' or fzr = '||{FZR}' or fzr = '|{FZR}||'  or fzr = '|||{FZR}'  or fzr = '|{FZR}|||') ";
            }
            else
            {
                sqlwhere += $" and (FZR = '|张海麟|' or fzr = '|张海麟' or fzr = '||张海麟' or fzr = '|张海麟||'  or fzr = '|||张海麟'  or fzr = '|张海麟|||') ";
            }

            switch (range)
            {
                case "四川":
                    sqlwhere += " and (Lvl like '%四%川%' or Lvl like '%成%都%')";
                    break;
                case "贵州":
                    sqlwhere += " and (Lvl like '%贵%州%' or Lvl like '%贵%阳%')";
                    break;
                case "云南":
                    sqlwhere += " and (Lvl like '%云%南%' or Lvl like '%昆%明%')";
                    break;
                case "重庆":
                    sqlwhere += " and (Lvl like '%重庆%')";
                    break;
                case "西藏":
                    sqlwhere += " and (Lvl like '%西%藏%' or Lvl like '%拉%萨%')";
                    break;
                case "青海":
                    sqlwhere += " and (Lvl like '%青%海%' or Lvl like '%西%宁%')";
                    break;
                case "陕西":
                    sqlwhere += " and (Lvl like '%陕%西%' or Lvl like '%西%安%')";
                    break;
                case "宁夏":
                    sqlwhere += " and (Lvl like '%宁%夏%' or Lvl like '%银%川%')";
                    break;
                case "甘肃":
                    sqlwhere += " and (Lvl like '%甘%肃%' or Lvl like '%兰%州%')";
                    break;
                case "新疆":
                    sqlwhere += " and (Lvl like '%新%疆%' or Lvl like '%乌%鲁%木%齐%')";
                    break;
                case "全部":
                    break;
            }

            if (ascription != "全部")
            {
                sqlwhere += "and Ascription  like  '%" + ascription + "%'";
            }

            return PageBase<NewClientData>.excutePageSql(new NewClientData(), "NewClientData", "NewClientData", "*", "weight desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        public string GetLvl(string FZR)
        {
            string Lvl = "";

            using (DataTable dt = SqlHelper.TransferProcedure("GetLvlByFZR", CommandType.StoredProcedure, new SqlParameter("@FZR", FZR)))
            {
                if (dt != null && dt.Rows.Count != 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        Lvl += dt.Rows[i][0].ToString() + ",";
                    }
                }
            }

            return Lvl;
        }

        /// <summary>
        /// 雷怡 2021-08-3 14:49
        /// 获取市场客户总数量
        /// </summary>
        public int GetCustomerNumber() 
        {
            string sql = "select * from NewClientData where IsDel = 0";
            return excuteSql(sql).Count;
        }
    }
}