using Edu.Common.Enum;
using Edu.Model.Entity.Customer;
using Edu.Model.ViewModel.Customer;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Edu.Repository.Customer
{
    /// <summary>
    /// 学员跟进仓储层
    /// </summary>
    public class RB_Student_FollowRepository : BaseRepository<RB_Student_Follow>
    {
        /// <summary>
        /// 获取学员跟进分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_Follow_Extend> GetStudentFollowPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Student_Follow_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* 
FROM RB_Student_Follow AS A
WHERE 1=1
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Follow_Extend.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Follow_Extend.Group_Id), query.Group_Id);
                }
                if (query.StuId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Follow_Extend.StuId), query.StuId);
                }
                if (!string.IsNullOrEmpty(query.STime))
                {
                    builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Student_Follow_Extend.CreateTime), query.STime);
                }
                if (!string.IsNullOrEmpty(query.ETime))
                {
                    builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Student_Follow_Extend.CreateTime), query.ETime);
                }
            }
            builder.AppendFormat(" ORDER BY A.{0} DESC ", nameof(RB_Student_Follow_Extend.Id));
            return GetPage<RB_Student_Follow_Extend>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取学员跟进列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_Follow_Extend> GetStudentFollowListRepository( RB_Student_Follow_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* 
FROM RB_Student_Follow AS A
WHERE 1=1
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Follow_Extend.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Follow_Extend.Group_Id), query.Group_Id);
                }
                if (query.StuId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Follow_Extend.StuId), query.StuId);
                }
                if (!string.IsNullOrEmpty(query.QStuIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_Follow_Extend.StuId), query.QStuIds);
                }
            }
            builder.AppendFormat(" ORDER BY A.{0} DESC ", nameof(RB_Student_Follow_Extend.Id));
            return Get<RB_Student_Follow_Extend>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取学员跟进月度列表
        /// </summary>
        /// <param name="stuIds"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_Follow_Extend> GetStudentFollowUpMonthList(string stuIds, int group_Id)
        {
            string sql = $@"SELECT f.*,f1.Remark,f1.CreateTime FROM
(SELECT StuId,AssistType,DATE_FORMAT(CreateTime,'%Y-%m') as MonthStr,MAX(Id) AS Id FROM rb_student_follow WHERE Group_Id ={group_Id} and `Status` =0 and StuId in ({stuIds}) and AssistType in(2,4)
GROUP BY StuId,AssistType,DATE_FORMAT(CreateTime,'%Y-%m')
) f
INNER JOIN rb_student_follow f1 on f.Id = f1.Id
";
            return Get<RB_Student_Follow_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取学员续费月度列表
        /// </summary>
        /// <param name="stuIds"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_Follow_Extend> GetStudentRenewMonthList(string stuIds, int group_Id)
        {
            string sql = $@"SELECT s.StuId,DATE_FORMAT(o.CreateTime,'%Y-%m') AS MonthStr FROM rb_student s
INNER JOIN rb_student_orderguest sog on s.StuId = sog.Student_Id
INNER JOIN rb_order o on sog.OrderId = o.OrderId
INNER JOIN rb_order_guest og on og.Id = sog.GuestId
where s.Group_Id ={group_Id} and s.`Status` =0 and sog.`Status` =0 and o.OrderState <>3 and (o.RenewOrderId >0 or og.RenewState =2) and s.StuId in({stuIds})
GROUP BY s.StuId,DATE_FORMAT(o.CreateTime,'%Y-%m')
";
            return Get<RB_Student_Follow_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取学员介绍月度列表
        /// </summary>
        /// <param name="stuIds"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_Follow_Extend> GetStudentIntroductionMonthList(string stuIds, int group_Id)
        {
            string sql = $@"SELECT s.StuSourceId as StuId,DATE_FORMAT(o.CreateTime,'%Y-%m') AS MonthStr,COUNT(0) AS StuNum FROM rb_student s
INNER JOIN rb_student_orderguest sog on s.StuId = sog.Student_Id
INNER JOIN rb_order o on sog.OrderId = o.OrderId
INNER JOIN rb_order_guest og on og.Id = sog.GuestId
where s.Group_Id ={group_Id} and s.`Status` =0 and sog.`Status` =0 and o.OrderState <>3 and s.CreateType =4 and s.StuSourceId in({stuIds})
GROUP BY s.StuSourceId,DATE_FORMAT(o.CreateTime,'%Y-%m')
";
            return Get<RB_Student_Follow_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取学员最近的一条跟进记录
        /// </summary>
        /// <param name="stuIds"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_Follow_Extend> GetStudentFollowUpLatelyList(string stuIds, int group_Id)
        {
            string sql = $@"SELECT f.*,f1.Remark,f1.CreateTime FROM
(SELECT StuId,AssistType,MAX(Id) AS Id FROM rb_student_follow WHERE Group_Id ={group_Id} and `Status` =0 and StuId in ({stuIds}) and AssistType =2
GROUP BY StuId,AssistType
) f
INNER JOIN rb_student_follow f1 on f.Id = f1.Id
";
            return Get<RB_Student_Follow_Extend>(sql).ToList();
        }

        /// <summary>
        /// 有效数据量统计 (第一次跟进且不等于流失)
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public Dictionary<string, List<RB_Student_Follow_Extend>> GetStudentFollowStaticRepository(RB_Student_Follow_Extend query)
        {
            Dictionary<string, List<RB_Student_Follow_Extend>> dicList = new Dictionary<string, List<RB_Student_Follow_Extend>>();
            string sql = @"
SELECT A.CreateBy,COUNT(1) AS FollowCount
FROM  rb_student_follow AS A INNER JOIN(SELECT StuId,CreateBy,MIN(Id) AS Id  FROM rb_student_follow WHERE `Status`=0 GROUP BY StuId,CreateBy) AS B ON A.Id=B.Id
      INNER JOIN RB_student AS C  ON A.StuId=C.StuId
WHERE A.Status=0 AND C.Status=0 AND C.StuStage<>7 {0}

";
            if (query.CreateBy > 0)
            {
                sql += string.Format(" AND A.CreateBy={0} ", query.CreateBy);
            }
            sql += " GROUP BY A.CreateBy ";

            string todayWhere = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                todayWhere += string.Format(" AND to_days(A.CreateTime)=to_days('{0}') ", query.STime);
            }
            else
            {
                todayWhere += string.Format(" AND to_days(A.CreateTime)=to_days(now()) ");
            }

            //今日
            var todayList = Get<RB_Student_Follow_Extend>(string.Format(sql.ToString(), todayWhere)).ToList();
            dicList.Add("today", todayList);

            //昨日
            string yesWhere = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                yesWhere += string.Format(" AND TO_DAYS('{0}')-TO_DAYS(A.CreateTime)=1 ", query.STime);
            }
            else
            {
                yesWhere += string.Format(" AND TO_DAYS(now())-TO_DAYS(A.CreateTime)=1 ");
            }
            var yesList = Get<RB_Student_Follow_Extend>(string.Format(sql, yesWhere)).ToList();
            dicList.Add("yestoday", yesList);

            //本周
            string curWeek = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                curWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d' )) = YEARWEEK('{0}') ", query.STime);
            }
            else
            {
                curWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK(now()) ");
            }
            var curWeekList = Get<RB_Student_Follow_Extend>(string.Format(sql, curWeek)).ToList();
            dicList.Add("currentWeek", curWeekList);

            //上周
            string lastWeek = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                lastWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK('{0}')-1 ", query.STime);
            }
            else
            {
                lastWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK(now())-1 ");
            }
            var lastWeekList = Get<RB_Student_Follow_Extend>(string.Format(sql, lastWeek)).ToList();
            dicList.Add("lastWeek", lastWeekList);

            //本月
            string curMonth = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                curMonth += string.Format(" AND DATE_FORMAT(A.CreateTime,'%Y%m')=DATE_FORMAT('{0}','%Y%m') ", query.STime);
            }
            else
            {
                curMonth += string.Format(" AND DATE_FORMAT(A.CreateTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') ");
            }
            var curMonthList = Get<RB_Student_Follow_Extend>(string.Format(sql, curMonth)).ToList();
            dicList.Add("curMonth", curMonthList);

            //上月
            string lastMonth = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                lastMonth += string.Format(" AND PERIOD_DIFF(date_format('{0}','%Y%m'),date_format(A.CreateTime,'%Y%m')) =1 ", query.STime);
            }
            else
            {
                lastMonth += string.Format(" AND PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(A.CreateTime,'%Y%m')) =1 ");
            }
            var lastMonthList = Get<RB_Student_Follow_Extend>(string.Format(sql, lastMonth)).ToList();
            dicList.Add("lastMonth", lastMonthList);
            return dicList;
        }

        /// <summary>
        /// 课程顾问部有效回访总量
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public Dictionary<string, List<RB_Student_Follow_Extend>> GetConsultantFollowStaticRepository(RB_Student_Follow_Extend query)
        {
            Dictionary<string, List<RB_Student_Follow_Extend>> dicList = new Dictionary<string, List<RB_Student_Follow_Extend>>();
            string sql = @"
SELECT A.CreateBy,COUNT(1) AS FollowCount
FROM  rb_student_follow AS A  
WHERE A.Status=0 {0}
";
            if (query.CreateBy > 0)
            {
                sql += string.Format(" AND  A.CreateBy={0} ", query.CreateBy);
            }
            sql += " GROUP BY A.CreateBy ";
            string todayWhere = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                todayWhere += string.Format(" AND to_days(A.CreateTime)=to_days('{0}') ", query.STime);
            }
            else
            {
                todayWhere += string.Format(" AND to_days(A.CreateTime)=to_days(now()) ");
            }

            //今日
            var todayList = Get<RB_Student_Follow_Extend>(string.Format(sql.ToString(), todayWhere)).ToList();
            dicList.Add("today", todayList);

            //昨日
            string yesWhere = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                yesWhere += string.Format(" AND TO_DAYS('{0}')-TO_DAYS(A.CreateTime)=1 ", query.STime);
            }
            else
            {
                yesWhere += string.Format(" AND TO_DAYS(now())-TO_DAYS(A.CreateTime)=1 ");
            }
            var yesList = Get<RB_Student_Follow_Extend>(string.Format(sql, yesWhere)).ToList();
            dicList.Add("yestoday", yesList);

            //本周
            string curWeek = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                curWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d' )) = YEARWEEK('{0}') ", query.STime);
            }
            else
            {
                curWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK(now()) ");
            }
            var curWeekList = Get<RB_Student_Follow_Extend>(string.Format(sql, curWeek)).ToList();
            dicList.Add("currentWeek", curWeekList);

            //上周
            string lastWeek = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                lastWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK('{0}')-1 ", query.STime);
            }
            else
            {
                lastWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK(now())-1 ");
            }
            var lastWeekList = Get<RB_Student_Follow_Extend>(string.Format(sql, lastWeek)).ToList();
            dicList.Add("lastWeek", lastWeekList);

            //本月
            string curMonth = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                curMonth += string.Format(" AND DATE_FORMAT(A.CreateTime,'%Y%m')=DATE_FORMAT('{0}','%Y%m') ", query.STime);
            }
            else
            {
                curMonth += string.Format(" AND DATE_FORMAT(A.CreateTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') ");
            }
            var curMonthList = Get<RB_Student_Follow_Extend>(string.Format(sql, curMonth)).ToList();
            dicList.Add("curMonth", curMonthList);

            //上月
            string lastMonth = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                lastMonth += string.Format(" AND PERIOD_DIFF(date_format('{0}','%Y%m'),date_format(A.CreateTime,'%Y%m')) =1 ", query.STime);
            }
            else
            {
                lastMonth += string.Format(" AND PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(A.CreateTime,'%Y%m')) =1 ");
            }
            var lastMonthList = Get<RB_Student_Follow_Extend>(string.Format(sql, lastMonth)).ToList();
            dicList.Add("lastMonth", lastMonthList);
            return dicList;
        }


        /// <summary>
        /// 单资源回访平均数~~~就是一条资源跟进了几次   【平均】
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public Dictionary<string, List<RB_Student_Follow_Extend>> GetConsultantAvgFollowStaticRepository(RB_Student_Follow_Extend query)
        {
            Dictionary<string, List<RB_Student_Follow_Extend>> dicList = new Dictionary<string, List<RB_Student_Follow_Extend>>();
            string whereStr = "";
            if (query.CreateBy > 0)
            {
                whereStr += string.Format(" AND CreateBy={0} ", query.CreateBy);
            }
            string sql = @"
SELECT Avg(FollowCount) AS FollowEffectiveCount FROM
(
    SELECT A.StuId,COUNT(1) AS FollowCount
    FROM  rb_student_follow AS A  
    WHERE A.Status=0 " + whereStr + @" {0}
    GROUP BY A.StuId
)  AS A
";

            string todayWhere = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                todayWhere += string.Format(" AND to_days(A.CreateTime)=to_days('{0}') ", query.STime);
            }
            else
            {
                todayWhere += string.Format(" AND to_days(A.CreateTime)=to_days(now()) ");
            }

            //今日
            var todayList = Get<RB_Student_Follow_Extend>(string.Format(sql.ToString(), todayWhere)).ToList();
            dicList.Add("today", todayList);

            //昨日
            string yesWhere = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                yesWhere += string.Format(" AND TO_DAYS('{0}')-TO_DAYS(A.CreateTime)=1 ", query.STime);
            }
            else
            {
                yesWhere += string.Format(" AND TO_DAYS(now())-TO_DAYS(A.CreateTime)=1 ");
            }
            var yesList = Get<RB_Student_Follow_Extend>(string.Format(sql, yesWhere)).ToList();
            dicList.Add("yestoday", yesList);

            //本周
            string curWeek = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                curWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d' )) = YEARWEEK('{0}') ", query.STime);
            }
            else
            {
                curWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK(now()) ");
            }
            var curWeekList = Get<RB_Student_Follow_Extend>(string.Format(sql, curWeek)).ToList();
            dicList.Add("currentWeek", curWeekList);

            //上周
            string lastWeek = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                lastWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK('{0}')-1 ", query.STime);
            }
            else
            {
                lastWeek += string.Format(" AND YEARWEEK(date_format(A.CreateTime,'%Y-%m-%d')) = YEARWEEK(now())-1 ");
            }
            var lastWeekList = Get<RB_Student_Follow_Extend>(string.Format(sql, lastWeek)).ToList();
            dicList.Add("lastWeek", lastWeekList);

            //本月
            string curMonth = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                curMonth += string.Format(" AND DATE_FORMAT(A.CreateTime,'%Y%m')=DATE_FORMAT('{0}','%Y%m') ", query.STime);
            }
            else
            {
                curMonth += string.Format(" AND DATE_FORMAT(A.CreateTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') ");
            }
            var curMonthList = Get<RB_Student_Follow_Extend>(string.Format(sql, curMonth)).ToList();
            dicList.Add("curMonth", curMonthList);

            //上月
            string lastMonth = "";
            if (!string.IsNullOrEmpty(query.STime))
            {
                lastMonth += string.Format(" AND PERIOD_DIFF(date_format('{0}','%Y%m'),date_format(A.CreateTime,'%Y%m')) =1 ", query.STime);
            }
            else
            {
                lastMonth += string.Format(" AND PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(A.CreateTime,'%Y%m')) =1 ");
            }
            var lastMonthList = Get<RB_Student_Follow_Extend>(string.Format(sql, lastMonth)).ToList();
            dicList.Add("lastMonth", lastMonthList);
            return dicList;
        }
    }
}
