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> GetList(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 (!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";
                }
            }

            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 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
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> GetPageList(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}";
            }
            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.CouseId > 0)
            {
                where += $@" and c.{nameof(RB_Class.CouseId)} ={demodel.CouseId}";
            }
            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 
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
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 GetMyOrderStatistics(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}";
            }
            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.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.CouseId > 0)
            {
                where += $@" and c.{nameof(RB_Class.CouseId)} ={demodel.CouseId}";
            }
            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 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}";
            }
            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.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}";
            }
            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 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> GetClassOrderPeopleNum(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();
        }
    }
}
