using Edu.Common.Enum;
using Edu.Model.Entity.WeChat;
using Edu.Model.ViewModel.WeChat;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB.Dapper;

namespace Edu.Repository.WeChat
{
    /// <summary>
    /// 企业微信客户信息仓储层
    /// </summary>
    public class RB_WeChat_CustomerInfoRepository : BaseRepository<RB_WeChat_CustomerInfo>
    {
        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_WeChat_CustomerInfo_ViewModel> GetPageList(int pageIndex,int pageSize,out long count, RB_WeChat_CustomerInfo_ViewModel demodel)
        {
            DynamicParameters parameters = new DynamicParameters();
            string where = $@" 1=1 and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Status)} =0";
            if (demodel.Group_Id > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.CustomerType > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerType)} ={demodel.CustomerType}";
            }
            if (demodel.EmpId > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.EmpId)} ={demodel.EmpId}";
            }
            if (demodel.FriendState > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.FriendState)} ={demodel.FriendState}";
            }
            if (demodel.CustomerState > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerState)} ={demodel.CustomerState}";
            }
            if (demodel.ClueState > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.ClueState)} ={demodel.ClueState}";
            }
            if (demodel.AllotState > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.AllotState)} ={demodel.AllotState}";
            }
            if (demodel.StageId > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.StageId)} ={demodel.StageId}";
            }
            if (!string.IsNullOrEmpty(demodel.CustomerName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerName)} like @CustomerName";
                parameters.Add("CustomerName", "%" + demodel.CustomerName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.WeChatName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.WeChatName)} like @WeChatName";
                parameters.Add("WeChatName", "%" + demodel.WeChatName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.CustomerMobile))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerMobile)} like @CustomerMobile";
                parameters.Add("CustomerMobile", "%" + demodel.CustomerMobile + "%");
            }
            if (!string.IsNullOrEmpty(demodel.CorpName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CorpName)} like @CorpName";
                parameters.Add("CorpName", "%" + demodel.CorpName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.CorpFullName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CorpFullName)} like @CorpFullName";
                parameters.Add("CorpFullName", "%" + demodel.CorpFullName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.CreateSTime))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CreateTime)} >='{demodel.CreateSTime}'";
            }
            if (!string.IsNullOrEmpty(demodel.CreateETime))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CreateTime)} <='{demodel.CreateETime} 23:59:59'";
            }
            if (!string.IsNullOrEmpty(demodel.FriendSTime))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.FriendTime)} >='{demodel.FriendSTime}'";
            }
            if (!string.IsNullOrEmpty(demodel.FriendETime))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.FriendTime)} <='{demodel.FriendETime} 23:59:59'";
            }
            if (demodel.Q_NotFollowUpDay > 0)
            {//几天未跟进
                var followDay = DateTime.Now.AddDays(0 - demodel.Q_NotFollowUpDay).ToString("yyyy-MM-dd HH:mm:ss");
                where += $@" and (r.{nameof(RB_WeChat_CustomerInfo_ViewModel.LastFollowUpTime)} <='{followDay}' or r.{nameof(RB_WeChat_CustomerInfo_ViewModel.LastFollowUpTime)} is null)";
            }
            if (!string.IsNullOrEmpty(demodel.Q_Friends))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.FriendState)} in({demodel.Q_Friends})";
            }
            if (!string.IsNullOrEmpty(demodel.Q_AddWay))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.AddWay)} in({demodel.Q_AddWay})";
            }

            if (!string.IsNullOrEmpty(demodel.TurnCustomerSTime))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.TurnCustomerTime)} >='{demodel.TurnCustomerSTime}'";
            }
            if (!string.IsNullOrEmpty(demodel.TurnCustomerETime))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.TurnCustomerTime)} <='{demodel.TurnCustomerETime} 23:59:59'";
            }
            if (!string.IsNullOrEmpty(demodel.AllotCustomerSTime))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.AllotCustomerTime)} >='{demodel.AllotCustomerSTime}'";
            }
            if (!string.IsNullOrEmpty(demodel.AllotCustomerETime))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.AllotCustomerTime)} <='{demodel.AllotCustomerETime} 23:59:59'";
            }
            if (demodel.DeptId > 0)
            {
                where += $@" and e.RB_Department_Id ={demodel.DeptId}";
            }
            if (!string.IsNullOrEmpty(demodel.AbandonReason))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.AbandonReason)} ='{demodel.AbandonReason}'";
            }
            if (demodel.CustomerLibraryId > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerLibraryId)} ={demodel.CustomerLibraryId}";
            }

            #region 解析查询参数
            if (demodel.SelectList != null && demodel.SelectList.Any())
            {
                int Num = 1;
                where += " and (";
                foreach (var item in demodel.SelectList)
                {
                    if (item.IsCustom == 1)
                    {
                        item.Name = "z" + item.Name;
                    }
                    else if (item.IsCustom == 2)
                    {
                        //标签
                        item.Name = "LableIds";
                    }
                    else if (item.IsCustom == 3) {
                        //客户阶段
                        item.Name = "StageId";
                    }
                    if (Num != 1) { where += demodel.AddCondition == 1 ? " and" : " or"; }
                    switch (item.Type) { 
                        case 1://包含 都是int类型的
                            List<int> valueList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<int>>("[" + item.StartValue + "]");
                            where += "(";
                            switch (item.Direction) {
                                case 1://包含所有
                                    int Num1 = 1;
                                    foreach (var value in valueList)
                                    {
                                        if (Num1 != 1) { where += " and"; }
                                        if (item.IsCustom == 1)
                                        {
                                            where += $@" FIND_IN_SET('{value}',r.CustomContent -> '$.{item.Name}') ";
                                        }
                                        else
                                        {
                                            where += $@" FIND_IN_SET('{value}',r.{item.Name}) ";
                                        }
                                        Num1++;
                                    }
                                    break;
                                case 2://包含任意
                                    Num1 = 1;
                                    foreach (var value in valueList)
                                    {
                                        if (Num1 != 1) { where += " or"; }
                                        if (item.IsCustom == 1)
                                        {
                                            where += $@" FIND_IN_SET('{value}',r.CustomContent -> '$.{item.Name}') ";
                                        }
                                        else
                                        {
                                            where += $@" FIND_IN_SET('{value}',r.{item.Name}) ";
                                        }
                                        Num1++;
                                    }
                                    break;
                                case 3://不包含
                                    Num1 = 1;
                                    foreach (var value in valueList)
                                    {
                                        if (Num1 != 1) { where += " and"; }
                                        if (item.IsCustom == 1)
                                        {
                                            where += $@" !FIND_IN_SET('{value}',r.CustomContent -> '$.{item.Name}') ";
                                        }
                                        else
                                        {
                                            where += $@" !FIND_IN_SET('{value}',r.{item.Name}) ";
                                        }
                                        Num1++;
                                    }
                                    break;
                                case 4://为空
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' ='' ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} ='' ";
                                    }
                                    break;
                                case 5://不为空
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' <>'' ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} <>'' ";
                                    }
                                    break;
                            }
                            where += ")";
                            break;
                        case 2://等于 string类型
                            where += "(";
                            switch (item.Direction) {
                                case 1://等于
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' ='{item.StartValue}' ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} ='{item.StartValue}' ";
                                    }
                                    break;
                                case 2://不等于
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' <>'{item.StartValue}' ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} <>'{item.StartValue}' ";
                                    }
                                    break;
                                case 3://为空
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' ='' ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} ='' ";
                                    }
                                    break;
                                case 4://不为空
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' <>'' ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} <>'' ";
                                    }
                                    break;
                                case 5://模糊匹配
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' like '%{item.StartValue}%' ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} like '%{item.StartValue}%' ";
                                    }
                                    break;
                            }
                            where += ")";
                            break;
                        case 3://日期
                            List<string> vList = item.StartValue.Split(',',StringSplitOptions.RemoveEmptyEntries).ToList();
                            string vstr = "'" + string.Join("','", vList) + "'";
                            if (item.IsCustom == 1)
                            {
                                //where += $@" DATE_FORMAT(r.CustomContent -> '$.{item.Name}','%m月%d号') in({vstr}) ";
                                where += $" date_format(REPLACE( r.CustomContent -> '$.{item.Name}', '\"', ''), '%m月%d号') in({vstr}) ";
                            }
                            else
                            {
                                where += $@" r.{item.Name} in({vstr}) ";
                            }
                            break;
                        case 4://日期范围
                            if (item.IsCustom == 1)
                            {
                                where += $@" r.CustomContent -> '$.{item.Name}' >='{item.StartValue}' and r.CustomContent -> '$.{item.Name}' <='{item.EndValue} 23:59:59' ";
                            }
                            else
                            {
                                where += $@" r.{item.Name} >='{item.StartValue}' and r.{item.Name} <='{item.EndValue} 23:59:59' ";
                            }
                            break;
                        case 5://数值
                            switch (item.Direction) {
                                case 1://等于
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' ={item.StartValue} ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} ={item.StartValue} ";
                                    }
                                    break;
                                case 2://大于
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' >{item.StartValue} ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} >{item.StartValue} ";
                                    }
                                    break;
                                case 3://大于等于
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' >={item.StartValue} ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} >={item.StartValue} ";
                                    }
                                    break;
                                case 4://小于
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' <{item.StartValue} ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} <{item.StartValue} ";
                                    }
                                    break;
                                case 5://小于等于
                                    if (item.IsCustom == 1)
                                    {
                                        where += $@" r.CustomContent -> '$.{item.Name}' <={item.StartValue} ";
                                    }
                                    else
                                    {
                                        where += $@" r.{item.Name} <={item.StartValue} ";
                                    }
                                    break;
                            }
                            break;
                    }
                    Num++;
                }
                where += " )";
            }
            #endregion

            string orderByStr = " r.Id DESC";
            if (demodel.OrderBy > 0) {
                switch (demodel.OrderBy) {
                    case 1:
                        orderByStr = " r.CreateTime asc";
                        break;
                    case 2:
                        orderByStr = " r.CreateTime desc";
                        break;
                    case 3:
                        orderByStr = " r.LastFollowUpTime asc";
                        break;
                    case 4:
                        orderByStr = " r.LastFollowUpTime desc";
                        break;
                }
            }

            string sql = $@" 
SELECT * From RB_WeChat_CustomerInfo r
left join rb_employee e on r.EmpId = e.EmployeeId
WHERE {where} 
ORDER BY {orderByStr} ";
            return GetPage<RB_WeChat_CustomerInfo_ViewModel>(pageIndex, pageSize, out count, sql, parameters).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_WeChat_CustomerInfo_ViewModel> GetList(RB_WeChat_CustomerInfo_ViewModel demodel)
        {
            DynamicParameters parameters = new DynamicParameters();
            string where = $@" 1=1 and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Status)} =0";
            if (demodel.Group_Id > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (!string.IsNullOrEmpty(demodel.Q_CustomerIds))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Id)} in({demodel.Q_CustomerIds})";
            }
            if (demodel.CustomerType > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerType)} ={demodel.CustomerType}";
            }
            if (demodel.EmpId > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.EmpId)} ={demodel.EmpId}";
            }
            if (demodel.FriendState > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.FriendState)} ={demodel.FriendState}";
            }
            if (demodel.IsPublic > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.IsPublic)} ={demodel.IsPublic}";
            }
            if (demodel.CustomerState > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerState)} ={demodel.CustomerState}";
            }
            if (demodel.StageId > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.StageId)} ={demodel.StageId}";
            }
            if (!string.IsNullOrEmpty(demodel.WorkEmpId))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.WorkEmpId)} ='{demodel.WorkEmpId}'";
            }
            if (!string.IsNullOrEmpty(demodel.ExternalUserId))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.ExternalUserId)} ='{demodel.ExternalUserId}'";
            }
            if (!string.IsNullOrEmpty(demodel.CustomerMobile))
            {
                where += $@" and FIND_IN_SET('{demodel.CustomerMobile}'), r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerMobile)})";
            }
            if (!string.IsNullOrEmpty(demodel.CustomerName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerName)} like @CustomerName";
                parameters.Add("CustomerName", "%" + demodel.CustomerName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.WeChatName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.WeChatName)} like @WeChatName";
                parameters.Add("WeChatName", "%" + demodel.WeChatName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.CorpName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CorpName)} like @CorpName";
                parameters.Add("CorpName", "%" + demodel.CorpName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.CorpFullName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CorpFullName)} like @CorpFullName";
                parameters.Add("CorpFullName", "%" + demodel.CorpFullName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.Q_ExternalUserIds))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.ExternalUserId)} in({demodel.Q_ExternalUserIds})";
            }

            string sql = $@" 
SELECT * From RB_WeChat_CustomerInfo r
WHERE {where} 
ORDER BY r.Id DESC ";
            return Get<RB_WeChat_CustomerInfo_ViewModel>(sql, parameters).ToList();
        }

        /// <summary>
        /// 获取客户重复跟进 分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_WeChat_CustomerInfo_ViewModel> GetCustomerRepeatToFollowUpPageList(int pageIndex, int pageSize, out long count, RB_WeChat_CustomerInfo_ViewModel demodel)
        {
            DynamicParameters parameters = new DynamicParameters();
            string where = $" 1=1 and r.`Status` =0 and r.CustomerState =1 and r.EmpId >0 and r.ExternalUserId <> '' ";
            if (demodel.Group_Id > 0)
            {
                where += $" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (!string.IsNullOrEmpty(demodel.CustomerName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerName)} like @CustomerName";
                parameters.Add("CustomerName", "%" + demodel.CustomerName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.WeChatName))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.WeChatName)} like @WeChatName";
                parameters.Add("WeChatName", "%" + demodel.WeChatName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.CustomerMobile))
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerMobile)} like @CustomerMobile";
                parameters.Add("CustomerMobile", "%" + demodel.CustomerMobile + "%");
            }
            if (!string.IsNullOrEmpty(demodel.CName))
            {
                where += $@" and r.CustomContent -> '$.z{demodel.CNameId}' like '%{demodel.CName}%' ";
            }
            if (demodel.EmpId > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.EmpId)} ={demodel.EmpId}";
            }
            if (demodel.DeptId > 0)
            {
                where += $@" and e.RB_Department_Id ={demodel.DeptId}";
            }

            string sql = $@"select r.ExternalUserId, COUNT(0) as Num from rb_wechat_customerinfo r
left join rb_employee e on r.EmpId = e.EmployeeId
where {where}
GROUP BY r.ExternalUserId HAVING COUNT(0) >1";
            return GetPage<RB_WeChat_CustomerInfo_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }


        /// <summary>
        /// 验证手机号码是否存在
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public bool ValidataMobile(RB_WeChat_CustomerInfo_ViewModel demodel)
        {
            DynamicParameters parameters = new DynamicParameters();
            string where = $@" 1=1 and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Status)} =0";
            if (demodel.Group_Id > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.Id > 0)
            {
                where += $@" and r.{nameof(RB_WeChat_CustomerInfo_ViewModel.Id)} <>{demodel.Id}";
            }
            if (!string.IsNullOrEmpty(demodel.CustomerMobile))
            {
                where += $@" and FIND_IN_SET('{demodel.CustomerMobile}'), r.{nameof(RB_WeChat_CustomerInfo_ViewModel.CustomerMobile)})";
            }

            string sql = $@" 
SELECT count(0) From RB_WeChat_CustomerInfo r
WHERE {where} ";
            var obj = ExecuteScalar(sql);
            return (obj == null ? 0 : Convert.ToInt32(obj)) > 0;
        }
    }
}
