using Edu.Common.Enum.Course;
using Edu.Common.Enum.Sale;
using Edu.Model.Entity.Course;
using Edu.Model.Entity.Grade;
using Edu.Model.Entity.Sell;
using Edu.Model.ViewModel.Sell;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Edu.Repository.Sell
{
    /// <summary>
    /// 订单仓储层
    /// </summary>
    public class RB_OrderRepository : BaseRepository<RB_Order>
    {
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetOrderListRepository(RB_Order_ViewModel demodel)
        {
            string where = $@" 1=1 ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            if (!string.IsNullOrEmpty(demodel.QOrderIds))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderId)} IN({demodel.QOrderIds})";
            }
            if (demodel.OrderType > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)demodel.OrderType}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} ={demodel.ClassId}";
            }
            if (demodel.SourceId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.SourceId)} ={demodel.SourceId}";
            }
            if (demodel.CourseId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.CourseId)} ={demodel.CourseId}";
            }
            if (!string.IsNullOrEmpty(demodel.ClassIds))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} in({demodel.ClassIds})";
            }
            if (!string.IsNullOrEmpty(demodel.SourceIds))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.SourceId)} in({demodel.SourceIds})";
            }
            if (demodel.OrderState > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} ={(int)demodel.OrderState}";
            }
            if (demodel.OrderSource > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderSource)} ={(int)demodel.OrderSource}";
            }
            if (demodel.OrderForm > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderForm)} ={(int)demodel.OrderForm}";
            }
            if (demodel.EnterID > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.EnterID)} ={demodel.EnterID}";
            }
            if (!string.IsNullOrEmpty(demodel.QEnterIds))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.EnterID)} IN({demodel.EnterID}) ";
            }
            if (demodel.Dept_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Dept_Id)} ={demodel.Dept_Id}";
            }
            if (demodel.Q_OrderState > 0)
            {
                if (demodel.Q_OrderState == 1)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} in(1,2)";
                }
                else if (demodel.Q_OrderState == 2)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} =3";
                }
            }
            if (!string.IsNullOrEmpty(demodel.ConfirmSTime))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.LXConfirmDate)} >='{demodel.ConfirmSTime}'";
            }
            if (!string.IsNullOrEmpty(demodel.ConfirmETime))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.LXConfirmDate)} <='{demodel.ConfirmETime} 23:59:59'";
            }
            if (demodel.JoinType == OrderJoinTypeEnum.InsertClass)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.JoinType)} ={(int)OrderJoinTypeEnum.InsertClass}";
            }
            if (!string.IsNullOrEmpty(demodel.Q_EffectTime))
            {
                where += string.Format(" and DATE_FORMAT(o.EffectTime,'%y/%m/%d')=DATE_FORMAT('{0}','%y/%m/%d') ", demodel.Q_EffectTime);
            }
            string orderBy = " o.OrderId asc";
            if (demodel.Q_OrderBy == 1)
            {
                orderBy = " o.OrderId asc";
            }
            else if (demodel.Q_OrderBy == 2)
            {
                orderBy = " o.CreateTime desc";
            }

            string sql = $@" 
SELECT o.*,c.ClassName,t.TeacherName,t.TeacherIcon,IFNULL(course.CourseName,'') AS CourseName,c.OpenTime,IFNULL(course.ClassHours,0) AS ClassHours
FROM RB_Order o 
    LEFT JOIN rb_class c on o.ClassId = c.ClassId
    LEFT JOIN rb_teacher t on c.Teacher_Id = t.TId
    LEFT JOIN rb_course AS course ON (o.CourseId=course.CourseId AND o.CourseId>0 AND o.OrderType=1) 
WHERE {where}
ORDER BY {orderBy}
";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取课程班级订单分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetOrderPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Order_ViewModel demodel)
        {
            string where = $@" 1=1";
            if (demodel.SourceId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)OrderTypeEnum.StudyabroadOrder} ";
            }
            else
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)OrderTypeEnum.CourseOrder} ";
            }

            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            else
            {
                if (demodel.ClassId > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} ={demodel.ClassId}";
                }
                if (demodel.SourceId > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.SourceId)} ={demodel.SourceId}";
                }
                if (demodel.OrderState > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} ={(int)demodel.OrderState}";
                }
                if (demodel.OrderSource > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderSource)} ={(int)demodel.OrderSource}";
                }
                if (demodel.OrderForm > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderForm)} ={(int)demodel.OrderForm}";
                }
                if (demodel.EnterID > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.EnterID)} ={demodel.EnterID}";
                }
                if (demodel.Dept_Id > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.Dept_Id)} ={demodel.Dept_Id}";
                }
                if (demodel.Q_OrderState > 0)
                {
                    if (demodel.Q_OrderState == 1)
                    {
                        where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} in(1,2,4)";
                    }
                    else if (demodel.Q_OrderState == 2)
                    {
                        where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} =3";
                    }
                }
                if (!string.IsNullOrEmpty(demodel.ClassName))
                {
                    where += $@" and c.{nameof(RB_Class.ClassName)} like '%{demodel.ClassName}%'";
                }
                if (!string.IsNullOrEmpty(demodel.ClassNo))
                {
                    where += $@" and c.{nameof(RB_Class.ClassNo)} ='{demodel.ClassNo}'";
                }
                if (!string.IsNullOrEmpty(demodel.GuestName))
                {
                    if (demodel.Group_Id > 0)
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and Group_Id ={demodel.Group_Id} and GuestName like '%{demodel.GuestName}%')";
                    }
                    else
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and GuestName like '%{demodel.GuestName}%')";
                    }
                }
                //学生表中学员编号
                if (demodel.Q_StudentId > 0)
                {
                    where += string.Format(" AND o.OrderId IN (SELECT OrderId FROM rb_student_orderguest WHERE Student_Id={0}) ", demodel.Q_StudentId);
                }
                if (demodel.CourseId > 0)
                {
                    where += $@" and c.{nameof(RB_Class.CouseId)} ={demodel.CourseId}";
                }
                if (!string.IsNullOrEmpty(demodel.StartTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} >='{demodel.StartTime}'";
                }
                if (!string.IsNullOrEmpty(demodel.EndTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} <='{demodel.EndTime} 23:59:59'";
                }
                if (!string.IsNullOrEmpty(demodel.OPStartTime))
                {
                    where += $@" and c.{nameof(RB_Class.OpenTime)} >='{demodel.OPStartTime}'";
                }
                if (!string.IsNullOrEmpty(demodel.OPEndTime))
                {
                    where += $@" and c.{nameof(RB_Class.OpenTime)} <='{demodel.OPEndTime} 23:59:59'";
                }
                if (demodel.Q_NotCollect == 1)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.PreferPrice)} > (o.{nameof(RB_Order_ViewModel.Income)} - o.{nameof(RB_Order_ViewModel.Refund)} + o.{nameof(RB_Order_ViewModel.PlatformTax)} + o.{nameof(RB_Order_ViewModel.DiscountMoney)}) ";
                }
            }
            if (demodel.HelpEnterId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.HelpEnterId)} = {demodel.HelpEnterId}";
            }
            if (demodel.CourseSubject > 0)
            {
                where += $@" and course.{nameof(RB_Order_ViewModel.CourseSubject)} = {demodel.CourseSubject}";
            }
            if (demodel.CustomerId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.CustomerId)} = {demodel.CustomerId}";
            }
            if (demodel.CreateBy > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.CreateBy)} = {demodel.CreateBy}";
            }
            if (demodel.CategoryId > 0)
            {
                where += $@" and cu.{nameof(RB_Order_ViewModel.CategoryId)} = {demodel.CategoryId}";
            }
            string orderBy = " o.OrderId asc";
            if (demodel.Q_OrderBy == 1)
            {
                orderBy = " o.OrderId asc";
            }
            else if (demodel.Q_OrderBy == 2)
            {
                orderBy = " o.CreateTime desc";
            }

            string sql = $@" 
SELECT o.*,c.ClassName,c.ClassNo,c.School_Id as ClassSchoolId,t.TeacherName,t.TeacherIcon,IFNULL(course.CourseName,'') AS CourseName
       ,C.OpenTime,course.CourseSubject,IFNULL(csubject.SubjectName,'') AS CourseSubjectName,IFNULL(school.SName,'') AS SchoolName
       ,IFNULL(c.ClassStatus,0) AS ClassStatus,IFNULL(course.CoverImg,'') AS CourseImg,IFNULL(course.B2BBackground,'') AS B2BBackground
	   ,IFNULL(course.B2BIcon,'') AS B2BIcon,cu.CustomerName,cu.ContactNumber as CustomerTel,c.ClassScrollType
FROM RB_Order o  LEFT JOIN rb_class c on o.ClassId = c.ClassId
     LEFT JOIN rb_teacher t on c.Teacher_Id = t.TId
     LEFT JOIN rb_course AS course ON (o.CourseId=course.CourseId AND o.CourseId>0 AND o.OrderType=1) 
     LEFT JOIN rb_course_subject AS csubject ON course.CourseSubject=csubject.Id
     LEFT JOIN rb_school AS school ON c.School_Id=school.SId
     LEFT JOIN rb_customer cu on cu.CustomerId = o.CustomerId
WHERE {where} ORDER BY {orderBy}
";
            return GetPage<RB_Order_ViewModel>(pageIndex, pageSize, out rowsCount, sql).ToList();
        }


        /// <summary>
        /// 获取我的订单统计
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public RB_Order_ViewModel GetMyOrderStatisticsRepository(RB_Order_ViewModel demodel)
        {
            string where = $@" 1=1";
            where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)OrderTypeEnum.CourseOrder} ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.HelpEnterId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.HelpEnterId)} = {demodel.HelpEnterId}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            else
            {
                if (demodel.ClassId > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} ={demodel.ClassId}";
                }
                if (demodel.OrderSource > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderSource)} ={(int)demodel.OrderSource}";
                }
                if (demodel.OrderForm > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderForm)} ={(int)demodel.OrderForm}";
                }
                if (demodel.EnterID > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.EnterID)} ={demodel.EnterID}";
                }
                if (demodel.Dept_Id > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.Dept_Id)} ={demodel.Dept_Id}";
                }
                if (!string.IsNullOrEmpty(demodel.ClassName))
                {
                    where += $@" and c.{nameof(RB_Class.ClassName)} like '%{demodel.ClassName}%'";
                }
                if (!string.IsNullOrEmpty(demodel.ClassNo))
                {
                    where += $@" and c.{nameof(RB_Class.ClassNo)} ='{demodel.ClassNo}'";
                }
                if (!string.IsNullOrEmpty(demodel.GuestName))
                {
                    if (demodel.Group_Id > 0)
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and Group_Id ={demodel.Group_Id} and GuestName like '%{demodel.GuestName}%')";
                    }
                    else
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and GuestName like '%{demodel.GuestName}%')";
                    }
                }
                if (demodel.CourseId > 0)
                {
                    where += $@" and c.{nameof(RB_Class.CouseId)} ={demodel.CourseId}";
                }
                if (!string.IsNullOrEmpty(demodel.StartTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} >='{demodel.StartTime}'";
                }
                if (!string.IsNullOrEmpty(demodel.EndTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} <='{demodel.EndTime} 23:59:59'";
                }
                if (!string.IsNullOrEmpty(demodel.OPStartTime))
                {
                    where += $@" and c.{nameof(RB_Class.OpenTime)} >='{demodel.OPStartTime}'";
                }
                if (!string.IsNullOrEmpty(demodel.OPEndTime))
                {
                    where += $@" and c.{nameof(RB_Class.OpenTime)} <='{demodel.OPEndTime} 23:59:59'";
                }
                if (demodel.Q_NotCollect == 1)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.PreferPrice)} > (o.{nameof(RB_Order_ViewModel.Income)} - o.{nameof(RB_Order_ViewModel.Refund)} + o.{nameof(RB_Order_ViewModel.PlatformTax)} + o.{nameof(RB_Order_ViewModel.DiscountMoney)}) ";
                }
            }
            if (demodel.CustomerId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.CustomerId)} ={demodel.CustomerId}";
            }
            if (demodel.CreateBy > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.CreateBy)} ={demodel.CreateBy}";
            }
            if (demodel.CourseSubject > 0)
            {
                where += $@" and course.{nameof(RB_Order_ViewModel.CourseSubject)} = {(int)demodel.CourseSubject}";
            }
            if (demodel.CategoryId > 0)
            {
                where += $@" and cus.{nameof(RB_Order_ViewModel.CategoryId)} = {demodel.CategoryId}";
            }
            string sql = $@"
SELECT SUM(CASE WHEN o.OrderState <> 3 THEN o.PreferPrice ELSE 0 END) AS PreferPrice,
       SUM(CASE WHEN o.OrderState <> 3 THEN o.Income ELSE 0 END) AS Income,
       SUM(CASE WHEN o.OrderState <> 3 THEN o.PreferPrice - o.Income + o.Refund - o.DiscountMoney - o.PlatformTax ELSE 0 END) AS DueInMoney,
       SUM(CASE WHEN o.OrderState <> 3 THEN o.GuestNum ELSE 0 END) AS GuestNum,
       SUM(CASE WHEN o.OrderState = 3 THEN o.GuestNum ELSE 0 END) AS CancelNum,
       SUM(CASE WHEN ea.GiveOutMoney > 0 THEN ea.GiveOutMoney ELSE 
CASE WHEN o.CommissionMoney <> -1 AND o.OrderState <> 3 THEN o.CommissionMoney ELSE 0 END END) AS CommissionMoney,
       SUM(CASE WHEN o.OrderState <> 3 THEN o.ExtraRewardMoney - o.ExtraDeductMoney ELSE 0 END) AS ExtraRewardMoney
FROM RB_Order o LEFT JOIN rb_class c on o.ClassId = c.ClassId
     LEFT JOIN rb_course AS course ON (o.CourseId=course.CourseId AND o.CourseId>0 AND o.OrderType=1) 
     LEFT JOIN (SELECT em.OrderId,SUM(em.GiveOutMoney) as GiveOutMoney FROM rb_sell_achievements_emp em GROUP BY em.OrderId) ea on ea.OrderId = o.OrderId
     LEFT JOIN rb_customer AS cus ON O.CustomerId=cus.CustomerId
WHERE {where}
";
            return Get<RB_Order_ViewModel>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取留学就业订单分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetStudyOrderPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Order_ViewModel demodel)
        {
            string where = $@" 1=1";
            where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)OrderTypeEnum.StudyabroadOrder} ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            else
            {
                if (demodel.ClassId > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} ={demodel.ClassId}";
                }
                if (demodel.SourceId > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.SourceId)} ={demodel.SourceId}";
                }
                if (demodel.OrderState > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} ={(int)demodel.OrderState}";
                }
                if (demodel.OrderSource > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderSource)} ={(int)demodel.OrderSource}";
                }
                if (demodel.OrderForm > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderForm)} ={(int)demodel.OrderForm}";
                }
                if (demodel.EnterID > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.EnterID)} ={demodel.EnterID}";
                }
                if (demodel.Dept_Id > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.Dept_Id)} ={demodel.Dept_Id}";
                }
                if (demodel.Q_OrderState > 0)
                {
                    if (demodel.Q_OrderState == 1)
                    {
                        where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} in(1,2)";
                    }
                    else if (demodel.Q_OrderState == 2)
                    {
                        where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} =3";
                    }
                }
                if (!string.IsNullOrEmpty(demodel.GuestName))
                {
                    if (demodel.Group_Id > 0)
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and Group_Id ={demodel.Group_Id} and GuestName like '%{demodel.GuestName}%')";
                    }
                    else
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and GuestName like '%{demodel.GuestName}%')";
                    }
                }
                if (!string.IsNullOrEmpty(demodel.StartTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} >='{demodel.StartTime}'";
                }
                if (!string.IsNullOrEmpty(demodel.EndTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} <='{demodel.EndTime} 23:59:59'";
                }
                if (demodel.Q_NotCollect == 1)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.PreferPrice)} > (o.{nameof(RB_Order_ViewModel.Income)} - o.{nameof(RB_Order_ViewModel.Refund)} + o.{nameof(RB_Order_ViewModel.PlatformTax)} + o.{nameof(RB_Order_ViewModel.DiscountMoney)}) ";
                }
                if (demodel.OrderType > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)demodel.OrderType} ";
                }
            }
            string orderBy = " o.OrderId asc";
            if (demodel.Q_OrderBy == 1)
            {
                orderBy = " o.OrderId asc";
            }
            else if (demodel.Q_OrderBy == 2)
            {
                orderBy = " o.CreateTime desc";
            }
            string sql = $@" 
SELECT o.*,C.`Name` AS StudyName,C.School_Id AS ClassSchoolId
FROM RB_Order o LEFT JOIN rb_studyabroad c ON o.SourceId = c.Id
WHERE {where} ORDER BY {orderBy}
";
            return GetPage<RB_Order_ViewModel>(pageIndex, pageSize, out rowsCount, sql).ToList();
        }

        /// <summary>
        /// 获取我的订单统计
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public RB_Order_ViewModel GetStudyOrderStatisticsRepository(RB_Order_ViewModel demodel)
        {
            string where = $@" 1=1";
            where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)OrderTypeEnum.StudyabroadOrder} ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            else
            {
                if (demodel.ClassId > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} ={demodel.ClassId}";
                }
                if (demodel.OrderSource > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderSource)} ={(int)demodel.OrderSource}";
                }
                if (demodel.OrderForm > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderForm)} ={(int)demodel.OrderForm}";
                }
                if (demodel.EnterID > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.EnterID)} ={demodel.EnterID}";
                }
                if (demodel.Dept_Id > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.Dept_Id)} ={demodel.Dept_Id}";
                }
                if (!string.IsNullOrEmpty(demodel.GuestName))
                {
                    if (demodel.Group_Id > 0)
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and Group_Id ={demodel.Group_Id} and GuestName like '%{demodel.GuestName}%')";
                    }
                    else
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and GuestName like '%{demodel.GuestName}%')";
                    }
                }
                if (!string.IsNullOrEmpty(demodel.StartTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} >='{demodel.StartTime}'";
                }
                if (!string.IsNullOrEmpty(demodel.EndTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} <='{demodel.EndTime} 23:59:59'";
                }
                if (demodel.Q_NotCollect == 1)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.PreferPrice)} > (o.{nameof(RB_Order_ViewModel.Income)} - o.{nameof(RB_Order_ViewModel.Refund)} + o.{nameof(RB_Order_ViewModel.PlatformTax)} + o.{nameof(RB_Order_ViewModel.DiscountMoney)}) ";
                }
            }
            string sql = $@" 
SELECT SUM(CASE WHEN o.OrderState <> 3 THEN o.PreferPrice ELSE 0 END) AS PreferPrice,
       SUM(CASE WHEN o.OrderState <> 3 THEN o.Income ELSE 0 END) AS Income,
       SUM(CASE WHEN o.OrderState <> 3 THEN o.PreferPrice - o.Income + o.Refund - o.DiscountMoney - o.PlatformTax ELSE 0 END) AS DueInMoney,
       SUM(CASE WHEN o.OrderState <> 3 THEN o.GuestNum ELSE 0 END) AS GuestNum,
       SUM(CASE WHEN o.OrderState = 3 THEN o.GuestNum ELSE 0 END) AS CancelNum,
       SUM(CASE WHEN o.CommissionMoney <> -1 AND o.OrderState <> 3 THEN o.CommissionMoney ELSE 0 END) AS CommissionMoney,
       SUM(CASE WHEN o.OrderState <> 3 THEN o.ExtraRewardMoney - o.ExtraDeductMoney ELSE 0 END) AS ExtraRewardMoney
FROM RB_Order o left join rb_studyabroad c on o.SourceId = c.Id
WHERE {where}
";
            return Get<RB_Order_ViewModel>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取班级报名人数
        /// </summary>
        /// <param name="classIds"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetClassOrderPeopleNumRepository(string classIds)
        {
            string sql = $@"
SELECT ClassId, SUM(GuestNum) AS GuestNum 
FROM rb_order 
WHERE OrderState in (1,2) AND ClassId in ({classIds}) 
GROUP BY ClassId;";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 计算退款金额
        /// </summary>
        /// <param name="orderModel"></param>
        /// <param name="courseModel"></param>
        /// <param name="FinishHours"></param>
        /// <returns></returns>
        public decimal CalcBackMoneyRepository(RB_Order orderModel, RB_Course courseModel, decimal FinishHours, out decimal newClassHourPrice)
        {
            newClassHourPrice = 0;
            //课时单价
            decimal classHourPrice = 0;
            //原课时单价
            //classHourPrice = orderModel.PreferPrice / orderModel.GuestNum / classModel.ClassHours;
            //标准课时单价=课程卖价/课时
            classHourPrice = courseModel.SellPrice / courseModel.ClassHours;
            newClassHourPrice = classHourPrice;
            //预计退款金额
            decimal backMoney = 0;
            //违约金
            decimal breakContractPrice = (courseModel.ClassHours - FinishHours) * classHourPrice * Convert.ToDecimal(0.1);
            backMoney = (orderModel.Income + orderModel.PlatformTax - orderModel.Refund) / orderModel.GuestNum - FinishHours * classHourPrice - breakContractPrice;
            return backMoney;
        }

        #region 销售提成

        /// <summary>
        /// 查询所有款收齐的订单
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="eDate">开班时间</param>
        /// <param name="courseIds">内部班课程</param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetAllMoneyCollectedOrderList(int group_Id, string eDate, string courseIds, string enableDate)
        {
            string where = "";
            if (!string.IsNullOrEmpty(courseIds))
            {
                where += $" and c.CouseId not in ({courseIds})";//排除内部班
            }
            string sql = $@"SELECT o.* FROM rb_order o 
inner join rb_class c on o.ClassId = c.ClassId
WHERE o.Group_Id ={group_Id} and o.OrderState =1 and o.EffectStatus in(1,2) and o.CommissionRate >0 and o.Income >0 and o.CreateTime <'{enableDate}' and c.OpenTime <= '{eDate} 23:59:59' {where} and o.PreferPrice = (o.Income - o.Refund + o.DiscountMoney + o.PlatformTax) and (o.IsCommissionGiveOK =0 or (o.ExtraRewardMoney -o.ExtraDeductMoney)<> o.ExtraCommissionMoney);";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取销售 需补提成的订单数量
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public int GetUserLeaveOrderCommission(int userId, int group_Id)
        {
            string sql = $@"SELECT COUNT(0) FROM rb_order WHERE Group_Id ={group_Id} and OrderState =1 and (EnterID ={userId} or HelpEnterId ={userId} ) and CommissionBack >0";
            var obj = ExecuteScalar(sql);
            return obj != null ? Convert.ToInt32(obj) : 0;
        }

        /// <summary>
        /// 获取可发放提成的留学订单列表
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="eDate"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetAllCanSendStudyAbroadCommissionList(int group_Id, string eDate)
        {
            string sql = $@"SELECT * FROM rb_order WHERE Group_Id ={group_Id} and OrderType =2 and OrderState <>3 
and LXConfirmState =1 and IFNULL(CommissionMoney,0) = -1 and LXConfirmDate <='{eDate} 23:59:59'";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        #endregion

        #region 销售业绩统计

        /// <summary>
        /// 销售业绩统计
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="saleId"></param>
        /// <param name="schoolId"></param>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetSaleAchievementsRankStatistics(int pageIndex, int pageSize, out long count, int saleId, int schoolId, string startMonth, string endMonth, int group_Id)
        {
            string where = "";
            if (saleId > 0)
            {
                where += $@" and o.EnterID ={saleId}";
            }
            if (schoolId >= 0)
            {
                where += $@" and o.School_Id ={schoolId}";
            }

            string sql = $@"SELECT o.EnterID,SUM(o.PreferPrice) as PreferPrice,SUM(o.Income) AS Income,sum(o.PlatformTax) as PlatformTax,SUM(o.Refund) AS Refund,SUM(o.GuestNum) AS GuestNum,
SUM(CASE WHEN c.ClassStatus =3 then o.GuestNum else 0 END) AS JKNum,COUNT(0) AS CancelNum, SUM(IFNULL(ttt.XFMoney,0)) AS YSKMoney
 FROM rb_order o
LEFT JOIN rb_class c on o.ClassId = c.ClassId
LEFT JOIN (SELECT q.OrderId,
SUM((case when q.ClassHours>0 and q.TotalSub >0 then 
	(q.CourseFee - q.DiscountMoney / q.TotalSub * q.CourseFee) /q.ClassHours
	else 0 END) * IFNULL(q.CurrentDeductionHours,0)) AS XFMoney
FROM(
SELECT g.Id,g.OrderId
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee as CourseFee
,ec.FirstDiscountMoney + ec.SecondDiscountMoney + ec.ThirdDiscountMoney as DiscountMoney
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee
+ec.FirstBookFee + ec.SecondBookFee + ec.ThirdBookFee
+ec.FirstClassFee + ec.SecondClassFee + ec.ThirdClassFee as TotalSub
,ec.FirstClassHours + ec.SecondClassHours + ec.ThirdClassHours as ClassHours
,cc.CurrentDeductionHours
 FROM rb_order_guest g
INNER JOIN rb_education_contract ec on g.Id = ec.GuestId
LEFT JOIN (SELECT ch.OrderGuestId, SUM(ch.CurrentDeductionHours) AS CurrentDeductionHours FROM rb_class_check ch WHERE `Status` =0 GROUP BY ch.OrderGuestId) cc on cc.OrderGuestId = g.Id
)q GROUP BY q.OrderId) ttt on ttt.OrderId = o.OrderId
WHERE o.Group_Id={group_Id} and o.OrderState <> 3 and o.EnterID >0 {where} and o.CreateTime >='{startMonth}' and o.CreateTime <='{endMonth} 23:59:59'
GROUP BY o.EnterID ORDER BY SUM(o.Income + o.PlatformTax - o.Refund) DESC
";
            return GetPage<RB_Order_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取销售业绩统计
        /// </summary>
        /// <param name="saleId"></param>
        /// <param name="schoolId"></param>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetSaleAchievementsRankStatisticsToList(int saleId, int schoolId, string startMonth, string endMonth, int group_Id)
        {
            string where = "";
            if (saleId > 0)
            {
                where += $@" and o.EnterID ={saleId}";
            }
            if (schoolId >= 0)
            {
                where += $@" and o.School_Id ={schoolId}";
            }

            string sql = $@"SELECT o.EnterID,SUM(o.PreferPrice) as PreferPrice,SUM(o.Income) AS Income,sum(o.PlatformTax) as PlatformTax,SUM(o.Refund) AS Refund,SUM(o.GuestNum) AS GuestNum,
SUM(CASE WHEN c.ClassStatus =3 then o.GuestNum else 0 END) AS JKNum,COUNT(0) AS CancelNum, SUM(IFNULL(ttt.XFMoney,0)) AS YSKMoney
 FROM rb_order o
LEFT JOIN rb_class c on o.ClassId = c.ClassId
LEFT JOIN (SELECT q.OrderId,
SUM((case when q.ClassHours>0 and q.TotalSub >0 then 
	(q.CourseFee - q.DiscountMoney / q.TotalSub * q.CourseFee) /q.ClassHours
	else 0 END) * IFNULL(q.CurrentDeductionHours,0)) AS XFMoney
FROM(
SELECT g.Id,g.OrderId
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee as CourseFee
,ec.FirstDiscountMoney + ec.SecondDiscountMoney + ec.ThirdDiscountMoney as DiscountMoney
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee
+ec.FirstBookFee + ec.SecondBookFee + ec.ThirdBookFee
+ec.FirstClassFee + ec.SecondClassFee + ec.ThirdClassFee as TotalSub
,ec.FirstClassHours + ec.SecondClassHours + ec.ThirdClassHours as ClassHours
,cc.CurrentDeductionHours
 FROM rb_order_guest g
INNER JOIN rb_education_contract ec on g.Id = ec.GuestId
LEFT JOIN (SELECT ch.OrderGuestId, SUM(ch.CurrentDeductionHours) AS CurrentDeductionHours FROM rb_class_check ch WHERE `Status` =0 GROUP BY ch.OrderGuestId) cc on cc.OrderGuestId = g.Id
)q GROUP BY q.OrderId) ttt on ttt.OrderId = o.OrderId
WHERE o.Group_Id={group_Id} and o.OrderState <> 3 and o.EnterID >0 {where} and o.CreateTime >='{startMonth}' and o.CreateTime <='{endMonth}'
GROUP BY o.EnterID ORDER BY SUM(o.Income + o.PlatformTax - o.Refund) DESC
";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取时间范围内  新增订单统计
        /// </summary>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetNewOrderStatistics(string startMonth, string endMonth, int group_Id)
        {
            string sql = $@"SELECT o.EnterID,SUM(o.PreferPrice - o.DiscountMoney) as PreferPrice,SUM(o.GuestNum) AS GuestNum,COUNT(0) AS CancelNum
 FROM rb_order o
WHERE o.Group_Id={group_Id} and o.OrderState <> 3 and o.EnterID >0 and o.CreateTime >='{startMonth}' and o.CreateTime <='{endMonth} 23:59:59'
GROUP BY o.EnterID 
";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取时间范围内  学生上课金额
        /// </summary>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetOrderClassHoursMoneyList(string startMonth, string endMonth, int group_Id)
        {
            string sql = $@"SELECT o.EnterID,
SUM((case when q.ClassHours>0 and q.TotalSub >0 then 
	(q.CourseFee - q.DiscountMoney / q.TotalSub * q.CourseFee) /q.ClassHours
	else 0 END) * IFNULL(q.CurrentDeductionHours,0)) AS YSKMoney
FROM(
SELECT g.Id,g.OrderId
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee as CourseFee
,ec.FirstDiscountMoney + ec.SecondDiscountMoney + ec.ThirdDiscountMoney as DiscountMoney
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee
+ec.FirstBookFee + ec.SecondBookFee + ec.ThirdBookFee
+ec.FirstClassFee + ec.SecondClassFee + ec.ThirdClassFee as TotalSub
,ec.FirstClassHours + ec.SecondClassHours + ec.ThirdClassHours as ClassHours
,cc.CurrentDeductionHours
 FROM rb_order_guest g
INNER JOIN rb_education_contract ec on g.Id = ec.GuestId
INNER JOIN (
SELECT ch.OrderGuestId, SUM(ch.CurrentDeductionHours) AS CurrentDeductionHours 
FROM rb_class_check ch WHERE ch.`Status` =0 AND ch.ClassDate >='{startMonth}' AND ch.ClassDate <='{endMonth} 23:59:59' GROUP BY ch.OrderGuestId) cc on cc.OrderGuestId = g.Id
WHERE g.Group_Id ={group_Id}
)q 
LEFT JOIN rb_order o on q.OrderId = o.OrderId
GROUP BY o.EnterID
";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取时间范围内  结课学生数量
        /// </summary>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetOrderGuestEndClassList(string startMonth, string endMonth, int group_Id)
        {
            string sql = $@"SELECT o.EnterID,COUNT(0) AS JKNum FROM rb_order_guest g 
LEFT JOIN rb_order o on g.OrderId = o.OrderId
WHERE g.`Status`=0 and g.Group_Id ={group_Id} and g.GuestState =7 and o.OrderState <> 3 and g.GraduationTime >='{startMonth}' and g.GraduationTime >='{endMonth} 23:59:59'
GROUP BY o.EnterID
";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        #endregion

        #region 未完成的订单
        /// <summary>
        /// 获取我的未完成的订单
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetOrderNoComplete(RB_Order_ViewModel demodel)
        {
            string where = $@" 1=1 and (o.Income+o.PlatformTax+o.DiscountMoney-o.Refund)<o.PreferPrice ";
            where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)OrderTypeEnum.CourseOrder} ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            else
            {
                if (demodel.ClassId > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} ={demodel.ClassId}";
                }
                if (demodel.OrderState > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} ={(int)demodel.OrderState}";
                }
                if (demodel.OrderSource > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderSource)} ={(int)demodel.OrderSource}";
                }
                if (demodel.OrderForm > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderForm)} ={(int)demodel.OrderForm}";
                }
                if (demodel.EnterID > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.EnterID)} ={demodel.EnterID}";
                }
                if (demodel.Dept_Id > 0)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.Dept_Id)} ={demodel.Dept_Id}";
                }
                if (demodel.Q_OrderState > 0)
                {
                    if (demodel.Q_OrderState == 1)
                    {
                        where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} in(1,2)";
                    }
                    else if (demodel.Q_OrderState == 2)
                    {
                        where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} =3";
                    }
                }
                if (!string.IsNullOrEmpty(demodel.ClassName))
                {
                    where += $@" and c.{nameof(RB_Class.ClassName)} like '%{demodel.ClassName}%'";
                }
                if (!string.IsNullOrEmpty(demodel.GuestName))
                {
                    if (demodel.Group_Id > 0)
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and Group_Id ={demodel.Group_Id} and GuestName like '%{demodel.GuestName}%')";
                    }
                    else
                    {
                        where += $@" and o.OrderId in (select OrderId from rb_order_guest where Status =0 and GuestName like '%{demodel.GuestName}%')";
                    }
                }
                if (demodel.CourseId > 0)
                {
                    where += $@" and c.{nameof(RB_Class.CouseId)} ={demodel.CourseId}";
                }
                if (!string.IsNullOrEmpty(demodel.StartTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} >='{demodel.StartTime}'";
                }
                if (!string.IsNullOrEmpty(demodel.EndTime))
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.CreateTime)} <='{demodel.EndTime} 23:59:59'";
                }
                if (!string.IsNullOrEmpty(demodel.OPStartTime))
                {
                    where += $@" and c.{nameof(RB_Class.OpenTime)} >='{demodel.OPStartTime}'";
                }
                if (!string.IsNullOrEmpty(demodel.OPEndTime))
                {
                    where += $@" and c.{nameof(RB_Class.OpenTime)} <='{demodel.OPEndTime} 23:59:59'";
                }
                if (demodel.Q_NotCollect == 1)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.PreferPrice)} > (o.{nameof(RB_Order_ViewModel.Income)} - o.{nameof(RB_Order_ViewModel.Refund)} + o.{nameof(RB_Order_ViewModel.PlatformTax)} + o.{nameof(RB_Order_ViewModel.DiscountMoney)}) ";
                }
            }
            string orderBy = " o.OrderId asc";
            if (demodel.Q_OrderBy == 1)
            {
                orderBy = " o.OrderId asc";
            }
            else if (demodel.Q_OrderBy == 2)
            {
                orderBy = " o.CreateTime desc";
            }

            string sql = $@" 
select o.*,c.ClassName,c.School_Id as ClassSchoolId,t.TeacherName,t.TeacherIcon,IFNULL(course.CourseName,'') AS CourseName,C.OpenTime
from RB_Order o 
left join rb_class c on o.ClassId = c.ClassId
left join rb_teacher t on c.Teacher_Id = t.TId
LEFT JOIN rb_course AS course ON (o.CourseId=course.CourseId AND o.CourseId>0 AND o.OrderType=1) 
where {where} order by {orderBy}
";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        #endregion

        #region 简易报表

        /// <summary>
        /// 获取列表留学
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetListForReport(RB_Order_ViewModel demodel)
        {
            string where = $@" 1=1 ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id >= 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            if (demodel.OrderType > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderType)} ={(int)demodel.OrderType}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} ={demodel.ClassId}";
            }
            if (demodel.SourceId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.SourceId)} ={demodel.SourceId}";
            }
            if (demodel.CourseId > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.CourseId)} ={demodel.CourseId}";
            }
            if (!string.IsNullOrEmpty(demodel.ClassIds))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.ClassId)} in({demodel.ClassIds})";
            }
            if (!string.IsNullOrEmpty(demodel.SourceIds))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.SourceId)} in({demodel.SourceIds})";
            }
            if (demodel.OrderState > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} ={(int)demodel.OrderState}";
            }
            if (demodel.OrderSource > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderSource)} ={(int)demodel.OrderSource}";
            }
            if (demodel.OrderForm > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.OrderForm)} ={(int)demodel.OrderForm}";
            }
            if (demodel.EnterID > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.EnterID)} ={demodel.EnterID}";
            }
            if (demodel.Dept_Id > 0)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.Dept_Id)} ={demodel.Dept_Id}";
            }
            if (demodel.Q_OrderState > 0)
            {
                if (demodel.Q_OrderState == 1)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} in(1,2)";
                }
                else if (demodel.Q_OrderState == 2)
                {
                    where += $@" and o.{nameof(RB_Order_ViewModel.OrderState)} =3";
                }
            }
            if (!string.IsNullOrEmpty(demodel.ConfirmSTime))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.LXConfirmDate)} >='{demodel.ConfirmSTime}'";
            }
            if (!string.IsNullOrEmpty(demodel.ConfirmETime))
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.LXConfirmDate)} <='{demodel.ConfirmETime} 23:59:59'";
            }
            if (demodel.JoinType == OrderJoinTypeEnum.InsertClass)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.JoinType)} ={(int)OrderJoinTypeEnum.InsertClass}";
            }
            if (!string.IsNullOrEmpty(demodel.Q_EffectTime))
            {
                where += string.Format(" and DATE_FORMAT(o.EffectTime,'%y/%m/%d')=DATE_FORMAT('{0}','%y/%m/%d') ", demodel.Q_EffectTime);
            }
            string orderBy = " o.OrderId asc";
            if (demodel.Q_OrderBy == 1)
            {
                orderBy = " o.OrderId asc";
            }
            else if (demodel.Q_OrderBy == 2)
            {
                orderBy = " o.CreateTime desc";
            }

            string sql = $@" 
SELECT o.*,c.ClassName,t.TeacherName,t.TeacherIcon,IFNULL(course.CourseName,'') AS CourseName,c.OpenTime,IFNULL(course.ClassHours,0) AS ClassHours
FROM RB_Order o 
    LEFT JOIN rb_class c on o.ClassId = c.ClassId
    LEFT JOIN rb_teacher t on c.Teacher_Id = t.TId
    LEFT JOIN rb_course AS course ON (o.CourseId=course.CourseId AND o.CourseId>0 AND o.OrderType=1) 
WHERE {where}
ORDER BY {orderBy}
";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        #endregion

        #region 教育同业

        /// <summary>
        /// 获取教育同业付款超时订单
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetEduB2BTimeoutOrderList(int groupId)
        {
            string sql = $@"SELECT * FROM rb_order WHERE Group_Id ={groupId} and OrderState =4 and OrderSource =9 and IFNULL(PayState,0) <>1 and CreateTime <'{DateTime.Now.AddMinutes(-30).ToString("yyyy-MM-dd HH:mm:ss")}'";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }

        #endregion

        #region 业绩提成

        /// <summary>
        /// 获取当月所有的订单
        /// </summary>
        /// <param name="group_Id"></param>
        /// <param name="eDate"></param>
        /// <param name="courseType">类型  1包含   2不包含</param>
        /// <param name="courseIds"></param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetAllAchievementsSendOrderList(int group_Id, string sDate, string eDate, int courseType, string courseIds, string schoolIds)
        {
            string where = "";
            if (!string.IsNullOrEmpty(courseIds))
            {
                if (courseType == 1)
                {
                    where += $" and c.CourseId in ({courseIds})";
                }
                else
                {
                    where += $" and c.CourseId not in ({courseIds})";
                }
            }
            if (!string.IsNullOrEmpty(schoolIds))
            {
                where += $" and c.School_Id in ({schoolIds})";
            }

            string sql = $@"SELECT o.*,c.School_Id as ClassSchoolId FROM rb_order o 
left join rb_class c on o.ClassId = c.ClassId
left join rb_sell_achievements_emp e on o.OrderId = e.OrderId
WHERE o.Group_Id ={group_Id} and o.OrderState =1 and o.Income >0 and o.CreateTime >='{sDate}' and o.CreateTime <= '{eDate} 23:59:59' 
and e.Id is null 
and o.PreferPrice = (o.Income - o.Refund + o.DiscountMoney + o.PlatformTax) {where}
group by o.OrderId
;";
            return Get<RB_Order_ViewModel>(sql).ToList();
        }
        #endregion

        /// <summary>
        /// 获取所有人头奖金的订单列表
        /// </summary>
        /// <param name="group_Id">集团编号</param>
        /// <param name="eDate">结束日期</param>
        /// <param name="PlanType">1-市场人员，2-课程顾问</param>
        /// <param name="SaleId">员工编号</param>
        /// <returns></returns>
        public List<RB_Order_ViewModel> GetAllBonusOrderListRepository(int group_Id,string startDate, string eDate, int PlanType, int SaleId)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT o.* 
FROM rb_order o 
WHERE 1=1 
");
            builder.AppendFormat(" AND o.OrderState IN({0}) ", (int)OrderStateEnum.Normal);
            builder.AppendFormat(" AND o.{0}={1} ", nameof(RB_Order_ViewModel.OrderType), (int)OrderTypeEnum.CourseOrder);
            builder.AppendFormat(" AND o.CreateTime >= '2021-12-01' ");
            builder.AppendFormat(" AND o.OrderId NOT IN(SELECT B.OrderId FROM rb_personnel_bonus AS A INNER JOIN rb_personnel_bonusdetail AS B ON A.Id=B.BonusId WHERE A.PlanType={0} AND A.EmployeeId ={1}) ", PlanType, SaleId);
            //帐平
            builder.AppendFormat(" AND o.PreferPrice = (o.Income - o.Refund + o.DiscountMoney + o.PlatformTax) ");
            if (group_Id > 0)
            {
                builder.AppendFormat(" AND o.Group_Id ={0} ", group_Id);
            }
            if (!string.IsNullOrEmpty(startDate))
            {
                builder.AppendFormat(" AND o.CreateTime >= '{0}' ", startDate);
            }
            if (!string.IsNullOrEmpty(eDate))
            {
                builder.AppendFormat(" AND o.CreateTime <= '{0} 23:59:59' ", eDate);
            }
            if (SaleId > 0)
            {
                //市场人员
                if (PlanType == 1)
                {
                    builder.AppendFormat(" AND (EnterID={0}) ", SaleId);
                }
                //课程顾问
                else
                {
                    builder.AppendFormat(" AND (CourseConsultantId={0}) ", SaleId);
                }
            }
            builder.AppendFormat(" ORDER BY o.CreateTime ASC  ");
            return Get<RB_Order_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 市场部和课程顾问部销售额统计
        /// </summary>
        /// <param name="query"></param>
        /// <param name="qType">1-市场部，2-课程顾问部</param>
        /// <returns></returns>
        public Dictionary<string, List<RB_Order_ViewModel>> GetMarketConsultantStaticRepository(RB_Order_ViewModel query,int qType=1)
        {
            Dictionary<string, List<RB_Order_ViewModel>> dicList = new Dictionary<string, List<RB_Order_ViewModel>>();
            string sql = "";
            if (qType == 1)
            {
                sql = @" 
SELECT A.EnterID,SUM(A.Income) AS Income
FROM RB_Order AS A 
WHERE 1=1 AND A.OrderState IN(1,2) {0}
";
                if (query.EnterID > 0)
                {
                    sql += string.Format(" AND A.EnterID={0} ", query.EnterID);
                }
                sql += " GROUP BY A.EnterID ";
            }
            else
            {
                sql = @" 
SELECT A.CourseConsultantId,Count(1) AS OrderCount, SUM(A.Income) AS Income
FROM RB_Order AS A 
WHERE 1=1 AND A.OrderState IN(1,2) {0}
";
                if (query.CourseConsultantId > 0)
                {
                    sql += string.Format(" AND A.CourseConsultantId={0} ", query.CourseConsultantId);
                }
                sql += " GROUP BY A.CourseConsultantId ";
            }

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

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

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

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

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

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

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