using Edu.Model.Entity.Sell;
using Edu.Model.ViewModel.Sell;
using System;
using System.Collections.Generic;
using System.Linq;

namespace Edu.Repository.Sell
{
    /// <summary>
    /// 教务奖励明细仓储层
    /// </summary>
    public class RB_Teaching_BonusDetailRepository : BaseRepository<RB_Teaching_BonusDetail>
    {
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Teaching_BonusDetail_ViewModel> GetList(RB_Teaching_BonusDetail_ViewModel demodel)
        {
            string where = $@" 1=1 and  A.Status=0";

            if (demodel.Group_Id > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.BonusId > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.BonusId)} ={demodel.BonusId}";
            }
            if (!string.IsNullOrEmpty(demodel.BonusIds))
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.BonusId)} in({demodel.BonusIds})";
            }
            if (demodel.TeacherId > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.TeacherId)} ={demodel.TeacherId}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.ClassId)} ={demodel.ClassId}";
            }
            if (!string.IsNullOrEmpty(demodel.ClassIds)) 
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.ClassId)} in({demodel.ClassIds})";
            }
            if (demodel.Type > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.Type)} ={demodel.Type}";
            }
            if (demodel.CouseClassify > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.CouseClassify)} ={demodel.CouseClassify}";
            }

            string sql = $@" select A.* from RB_Teaching_BonusDetail   as A  LEFT JOIN rb_teaching_bonus as b on a.BonusId=b.Id  and b.State=2 where {where} order by a.Id desc";
            return Get<RB_Teaching_BonusDetail_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Teaching_BonusDetail_ViewModel> GetPageList(int pageIndex,int pageSize,out long count,RB_Teaching_BonusDetail_ViewModel demodel)
        {
            string where = $@" 1=1 and bd.Status=0";

            if (demodel.Group_Id > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.BonusId > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.BonusId)} ={demodel.BonusId}";
            }
            if (!string.IsNullOrEmpty(demodel.BonusIds))
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.BonusId)} in({demodel.BonusIds})";
            }
            if (!string.IsNullOrEmpty(demodel.Month))
            {
                where += $@" and b.{nameof(RB_Teaching_Bonus.Month)} ='{demodel.Month}'";
            }
            if (demodel.TeacherId > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.TeacherId)} ={demodel.TeacherId}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.ClassId)} ={demodel.ClassId}";
            }
            if (demodel.Type > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.Type)} ={demodel.Type}";
            }
            if (demodel.Q_BonusConfirm == 1)
            {
                where += $@" and b.State =2";
            }

            string sql = $@" select bd.*,c.ClassName from RB_Teaching_BonusDetail bd
inner join rb_teaching_bonus b on bd.BonusId = b.Id
left join rb_class c on bd.ClassId = c.ClassId
where {where} order by bd.Date asc";
            return GetPage<RB_Teaching_BonusDetail_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }


        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Teaching_BonusDetail_ViewModel> GetTeachingBonusDetailList(RB_Teaching_BonusDetail_ViewModel demodel)
        {
            string where = $@" 1=1 and b.Status=0";

            if (demodel.Group_Id > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id >= 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.BonusId > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.BonusId)} ={demodel.BonusId}";
            }
            if (!string.IsNullOrEmpty(demodel.BonusIds))
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.BonusId)} in({demodel.BonusIds})";
            }
            if (demodel.TeacherId > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.TeacherId)} ={demodel.TeacherId}";
            }
            if (!string.IsNullOrEmpty(demodel.TeacherIds))
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.TeacherId)} in({demodel.TeacherIds})";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.ClassId)} ={demodel.ClassId}";
            }
            if (demodel.Type > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.Type)} ={demodel.Type}";
            }

            string sql = $@" select b.*,t.TeacherName,case when b.CouseClassify =1 then c.ClassName else tr.LessonName end as ClassName,
c.ClassNo,s.SName from RB_Teaching_BonusDetail b
left join rb_teacher t on b.TeacherId = t.TId
left join rb_class c on b.ClassId = c.ClassId and b.CouseClassify =1
left join rb_school s on b.School_Id = s.SId
left join rb_reserve_class rc on b.ClassId = rc.ReserveClassId and b.CouseClassify =2
left join rb_triallesson tr on rc.TrialLessonId = tr.Id
where {where} order by b.TeacherId asc,b.Date asc";
            return Get<RB_Teaching_BonusDetail_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取统计
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Teaching_BonusDetail_ViewModel> GetTeachingBonusStatistics(RB_Teaching_BonusDetail_ViewModel demodel)
        {
            string where = $@" 1=1 and b.Status=0";

            if (demodel.Group_Id > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id >= 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.BonusId > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.BonusId)} ={demodel.BonusId}";
            }
            if (!string.IsNullOrEmpty(demodel.BonusIds))
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.BonusId)} in({demodel.BonusIds})";
            }
            if (demodel.TeacherId > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.TeacherId)} ={demodel.TeacherId}";
            }
            if (!string.IsNullOrEmpty(demodel.TeacherIds))
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.TeacherId)} in({demodel.TeacherIds})";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.ClassId)} ={demodel.ClassId}";
            }
            if (demodel.Type > 0)
            {
                where += $@" and b.{nameof(RB_Teaching_BonusDetail.Type)} ={demodel.Type}";
            }

            string sql = $@" select b.TeacherId,t.TeacherName,b.UnitPrice,s.SId,s.SName,
SUM(CASE WHEN b.Type =1 THEN b.CourseHour ELSE 0 END) AS CourseHour,
SUM(CASE WHEN b.Type =2 THEN b.CourseHour ELSE 0 END) AS DCourseHour,
SUM(b.DeductionHour) AS DeductionHour,
SUM(b.Money) AS Money
 from RB_Teaching_BonusDetail b
left join rb_teacher t on b.TeacherId = t.TId 
left join rb_school s on b.School_Id = s.SId
where {where} 
GROUP BY b.TeacherId,t.TeacherName,b.UnitPrice,s.SId
";
            return Get<RB_Teaching_BonusDetail_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取课时费奖励总金额
        /// </summary>
        /// <param name="id"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public decimal GetTeachingBonusTotalMoney(int id, int group_Id)
        {
            string sql = $@" select SUM(bd.Money) AS Money
from RB_Teaching_BonusDetail bd
INNER JOIN rb_teaching_bonus b on bd.BonusId = b.Id
where bd.Status=0 and bd.Group_Id ={group_Id} AND b.State=2 AND bd.TeacherId ={id}
";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 获取课时费发放的最后一个月
        /// </summary>
        /// <param name="id"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public string GetTeachingBonusLastlyMonth(int id, int group_Id)
        {
            string sql = $@"select b.`Month`
from RB_Teaching_BonusDetail bd
INNER JOIN rb_teaching_bonus b on bd.BonusId = b.Id
where bd.Status=0 and bd.Group_Id ={group_Id} AND b.State=2 AND bd.TeacherId ={id}
ORDER BY b.`Month` DESC LIMIT 1";
            var obj = ExecuteScalar(sql);
            return obj == null ? "" : obj.ToString();
        }

        /// <summary>
        /// 获取累积金额
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public decimal GetTeachingBonusDetailTotalMoney(RB_Teaching_BonusDetail_ViewModel demodel)
        {
            string where = $@" 1=1 and bd.Status=0";

            if (demodel.Group_Id > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.BonusId > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.BonusId)} ={demodel.BonusId}";
            }
            if (!string.IsNullOrEmpty(demodel.BonusIds))
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.BonusId)} in({demodel.BonusIds})";
            }
            if (!string.IsNullOrEmpty(demodel.Month))
            {
                where += $@" and b.{nameof(RB_Teaching_Bonus.Month)} ='{demodel.Month}'";
            }
            if (demodel.TeacherId > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.TeacherId)} ={demodel.TeacherId}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.ClassId)} ={demodel.ClassId}";
            }
            if (demodel.Type > 0)
            {
                where += $@" and bd.{nameof(RB_Teaching_BonusDetail.Type)} ={demodel.Type}";
            }

            string sql = $@" select sum(bd.Money) as Money from RB_Teaching_BonusDetail bd
inner join rb_teaching_bonus b on bd.BonusId = b.Id
where {where}";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToDecimal(obj);
        }


        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Teaching_BonusDetail_ViewModel> GetListByTeacherId(RB_Teaching_BonusDetail_ViewModel demodel)
        {
            string where = $@" 1=1 and  A.Status=0 and b.State=2";

            if (demodel.Group_Id > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.BonusId > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.BonusId)} ={demodel.BonusId}";
            }
            if (!string.IsNullOrEmpty(demodel.BonusIds))
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.BonusId)} in({demodel.BonusIds})";
            }
            if (demodel.TeacherId > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.TeacherId)} ={demodel.TeacherId}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.ClassId)} ={demodel.ClassId}";
            }
            if (!string.IsNullOrEmpty(demodel.ClassIds))
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.ClassId)} in({demodel.ClassIds})";
            }
            if (demodel.Type > 0)
            {
                where += $@" and  A.{nameof(RB_Teaching_BonusDetail.Type)} ={demodel.Type}";
            }
            if (!string.IsNullOrWhiteSpace(demodel.Month))//2021-06-30 Add by:W 
            {
                where += $@" and DATE_FORMAT(STR_TO_DATE(CONCAT(b.Month,'-01'),'%Y-%m-%d'),'%Y-%m-%d')>=DATE_FORMAT('{demodel.Month}','%Y-%m-%d')";
            }
            string sql = $@" select A.*,b.`Month` from RB_Teaching_BonusDetail   as A  LEFT JOIN rb_teaching_bonus as b on a.BonusId=b.Id   where {where} order by a.Id desc";
            return Get<RB_Teaching_BonusDetail_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取时间范围老师课时费
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="classIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<RB_Teaching_BonusDetail_ViewModel> GetTeacherBonusForDate(int group_Id, int schoolId, string classIds, string startTime, string endTime)
        {
            string where = "";
            if (!string.IsNullOrEmpty(classIds)) {
                where = $@" and d.ClassId in ({classIds})";
            }
            if (schoolId >= 0) {
                where = $@" and c.School_Id ={schoolId}";
            }

            string sql = $@"SELECT d.ClassId,SUM(d.Money) AS Money FROM rb_teaching_bonusdetail d 
INNER JOIN rb_teaching_bonus p on d.BonusId = p.Id
left join rb_class c on d.ClassId = c.ClassId
WHERE p.Group_Id ={group_Id} {where} and STR_TO_DATE(CONCAT(p.`Month`,'-01'),'%Y-%m-%d') >='{startTime}' and STR_TO_DATE(CONCAT(p.`Month`,'-01'),'%Y-%m-%d') <='{endTime} 23:59:59'
GROUP BY d.ClassId";
            return Get<RB_Teaching_BonusDetail_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取试听课老师课时费
        /// </summary>
        /// <param name="reserveClassId"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public RB_Teaching_BonusDetail_ViewModel GetReserveClassHours(int reserveClassId, int groupId)
        {
            string sql = $@"select Sum(d.CourseHour) as CourseHour,Sum(d.DeductionHour) as DeductionHour,Sum(d.Money) as Money 
from rb_teaching_bonusdetail d
left join rb_teaching_bonus b on b.Id = d.BonusId
where d.Status =0 and b.State =2 and d.Group_Id ={groupId} and d.ClassId ={reserveClassId} and d.CouseClassify =2";
            return Get<RB_Teaching_BonusDetail_ViewModel>(sql).FirstOrDefault();
        }
    }
}
