using Edu.Model.Entity.User;
using Edu.Model.ViewModel.User;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Edu.Repository.User
{
    /// <summary>
    /// 市场部客户数据统计仓储层
    /// </summary>
    public class RB_Student_MarketRepository : BaseRepository<RB_Student_Market>
    {
        /// <summary>
        /// 市场部客户数据统计列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_Market_Extend> GetStudentMarketListRepository(RB_Student_Market_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* 
FROM RB_Student_Market AS A
WHERE 1=1
");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Market_Extend.Group_Id), query.Group_Id);
                }
                if (query.YearStr > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Market_Extend.YearStr), query.YearStr);
                }
                if (query.MonthStr > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Market_Extend.MonthStr), query.MonthStr);
                }
                if (query.Times > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Market_Extend.Times), query.Times);
                }
                if (query.IsQueryNotMoney == 1)
                {
                    builder.AppendFormat(" AND (A.{0}<>0 OR A.{1}<>0) ", nameof(RB_Student_Market_Extend.OrderNotMoney), nameof(RB_Student_Market_Extend.StudyOrderNotMoney));
                }
            }
            builder.AppendFormat(" ORDER BY A.{0} ASC ", nameof(RB_Student_Static_Extend.Id));
            return Get<RB_Student_Market_Extend>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 市场部客户数据月份统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_Market_Extend> GetStudentMarketMonthListRepository(RB_Student_Market_Extend query, bool isYear = false)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
 SELECT CONCAT(DATE_FORMAT(A.Date, '%Y-%m'),'-01') AS Date,A.YearStr,A.MonthStr,SUM(A.PushCount) AS PushCount,SUM(A.TrainCount) AS TrainCount,SUM(A.StudyCount) AS StudyCount
       ,SUM(A.MeiTuanCount) AS MeiTuanCount,SUM(A.EduCustomerSign) AS EduCustomerSign,SUM(A.EduCustomerNotSign) AS EduCustomerNotSign,SUM(A.PassBy) AS PassBy
       ,SUM(A.WeChatFriend) AS WeChatFriend,SUM(A.TransCount) AS TransCount,SUM(A.SchoolPromote) AS SchoolPromote,SUM(A.SchoolAgent) AS SchoolAgent,SUM(A.OfflineActivity) AS OfflineActivity
	   ,SUM(A.OfficialAccount) AS OfficialAccount,SUM(A.TeacherRecommend) AS TeacherRecommend,SUM(A.ByteDance) AS ByteDance,SUM(A.ThirdPartyPlatform) AS ThirdPartyPlatform
	   ,SUM(A.Other) AS Other,SUM(A.ClassACount) AS ClassACount,SUM(A.ClassBCount) AS ClassBCount,SUM(A.ClassCCount) AS ClassCCount,SUM(A.ClassDCount) AS ClassDCount
	   ,SUM(A.InvalidCount) AS InvalidCount,SUM(A.ContractCount) AS ContractCount,SUM(A.OrderMoney) AS OrderMoney,SUM(A.StudyOrderMoney) AS StudyOrderMoney
       ,SUM(A.OrderNotMoney) AS OrderNotMoney,SUM(A.StudyOrderNotMoney) AS StudyOrderNotMoney
FROM RB_Student_Market AS A
WHERE 1=1 AND A.Date<>'0001-01-01 00:00:00'
");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Market_Extend.Group_Id), query.Group_Id);
                }
                if (query.YearStr > 0)
                {
                    if (isYear)
                    {
                        builder.AppendFormat(" AND A.{0}<={1} ", nameof(RB_Student_Market_Extend.YearStr), query.YearStr);
                    }
                    else
                    {
                        builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Market_Extend.YearStr), query.YearStr);
                    }
                }
            }
            builder.AppendFormat(" GROUP BY DATE_FORMAT(A.Date, '%Y-%m') ");
            builder.AppendFormat(" ORDER BY DATE_FORMAT(A.Date, '%Y-%m') ASC ");
            return Get<RB_Student_Market_Extend>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 市场部客户数据年份统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_Market_Extend> GetStudentMarketYearListRepository(RB_Student_Market_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
 SELECT CONCAT(DATE_FORMAT(A.Date, '%Y-%m'),'-01') AS Date,A.YearStr,A.MonthStr,SUM(A.PushCount) AS PushCount,SUM(A.TrainCount) AS TrainCount,SUM(A.StudyCount) AS StudyCount
       ,SUM(A.MeiTuanCount) AS MeiTuanCount,SUM(A.EduCustomerSign) AS EduCustomerSign,SUM(A.EduCustomerNotSign) AS EduCustomerNotSign,SUM(A.PassBy) AS PassBy
       ,SUM(A.WeChatFriend) AS WeChatFriend,SUM(A.TransCount) AS TransCount,SUM(A.SchoolPromote) AS SchoolPromote,SUM(A.SchoolAgent) AS SchoolAgent,SUM(A.OfflineActivity) AS OfflineActivity
	   ,SUM(A.OfficialAccount) AS OfficialAccount,SUM(A.TeacherRecommend) AS TeacherRecommend,SUM(A.ByteDance) AS ByteDance,SUM(A.ThirdPartyPlatform) AS ThirdPartyPlatform
	   ,SUM(A.Other) AS Other,SUM(A.ClassACount) AS ClassACount,SUM(A.ClassBCount) AS ClassBCount,SUM(A.ClassCCount) AS ClassCCount,SUM(A.ClassDCount) AS ClassDCount
	   ,SUM(A.InvalidCount) AS InvalidCount,SUM(A.ContractCount) AS ContractCount,SUM(A.OrderMoney) AS OrderMoney,SUM(A.StudyOrderMoney) AS StudyOrderMoney
       ,SUM(A.OrderNotMoney) AS OrderNotMoney,SUM(A.StudyOrderNotMoney) AS StudyOrderNotMoney
FROM RB_Student_Market AS A
WHERE 1=1 AND A.Date<>'0001-01-01 00:00:00'
");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Market_Extend.Group_Id), query.Group_Id);
                }
                if (query.YearStr > 0)
                {
                    builder.AppendFormat(" AND A.{0}<={1} ", nameof(RB_Student_Market_Extend.YearStr), query.YearStr);
                }
            }
            builder.AppendFormat(" GROUP BY A.YearStr ");
            builder.AppendFormat(" ORDER BY A.YearStr ASC ");
            return Get<RB_Student_Market_Extend>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 根据存储过程统计市场部客户信息
        /// </summary>
        /// <param name="DateStr"></param>
        /// <returns></returns>
        public RB_Student_Market_Extend GetStudentMarketByProcRepository(string DateStr)
        {
            string sql = string.Format("call proc_student_market('{0}') ", DateStr);
            var list = Get<RB_Student_Market_Extend>(sql);
            return list?.FirstOrDefault();
        }

        /// <summary>
        /// 检测指定日期是否已经生成数据
        /// </summary>
        /// <param name="Date"></param>
        /// <returns></returns>
        public bool CheckStudentMarketExistsRepository(string Date)
        {
            int result = 0;
            string sql = string.Format("SELECT COUNT(1) FROM RB_Student_Market WHERE DATE_FORMAT(Date,'%y/%m/%d')= DATE_FORMAT('{0}','%y/%m/%d') ", Date);
            var obj = base.ExecuteScalar(sql);
            if (obj != null)
            {
                Int32.TryParse(obj.ToString(), out result);
            }
            return result > 0;
        }

        /// <summary>
        /// 清除市场部客户数据统计信息
        /// </summary>
        public void DeleteStudentMarketByProcRepository()
        {
            string sql = "TRUNCATE TABLE RB_Student_Market ";
            base.Execute(sql);
        }
    }
}