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

namespace Edu.Repository.Course
{
    /// <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}";
            }
            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 (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,c.ClassName 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
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 (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,
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 
where {where} 
GROUP BY b.TeacherId,t.TeacherName,b.UnitPrice
";
            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();
        }
    }
}