using Edu.Common.Enum;
using Edu.Common.Enum.Sale;
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_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.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.IsChaBan ==1 )
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.IsChaBan)} ={demodel.IsChaBan}";
            }
            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";
            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.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.HelpEnterId > 0) 
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.HelpEnterId)} = {demodel.HelpEnterId}";
            }
            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
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 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)}) ";
                }
            }

            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_class c on o.ClassId = c.ClassId
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();
        }


        #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 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 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;
        }

        #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}'
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();
        }

        #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.IsChaBan == 1)
            {
                where += $@" and o.{nameof(RB_Order_ViewModel.IsChaBan)} ={demodel.IsChaBan}";
            }
            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
    }
}
