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 (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}";
}
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
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}";
}
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
LEFT JOIN rb_course AS course ON (o.CourseId=course.CourseId AND o.CourseId>0 AND o.OrderType=1)
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 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.CreateTime <'2021-12-01' 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();
}
/// <summary>
/// 获取教育同业可返佣订单列表
/// </summary>
/// <param name="groupId"></param>
/// <returns></returns>
public List<RB_Order_ViewModel> GetEduB2BOrderCanSendCommissionList(int groupId, string dateStr)
{
string sql = $@"SELECT o.* FROM rb_order o
INNER JOIN rb_customer_balancedetail b on o.OrderId = b.OrderId
WHERE o.Group_Id ={groupId} and o.OrderState =1 and o.OrderSource =9 and o.PayState =1 and b.RebateType =1 and b.BalanceType =1 and b.BalanceState =2 and o.PayDate <='{dateStr} 23:59:59'
GROUP BY o.OrderId";
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 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.CreateTime >='2021-12-01' 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>
/// <returns></returns>
public List<RB_Order_ViewModel> GetAllBonusOrderListRepository(int group_Id, string eDate,int SaleId)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT o.*
FROM rb_order o
WHERE 1=1
");
builder.AppendFormat(" AND o.OrderState IN({0},{1}) ", (int)OrderStateEnum.Normal, (int)OrderStateEnum.DropOut);
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.PreferPrice = (o.Income - o.Refund + o.DiscountMoney + o.PlatformTax) ");
if (group_Id > 0)
{
builder.AppendFormat(" o.Group_Id ={0} ", group_Id);
}
if (!string.IsNullOrEmpty(eDate))
{
builder.AppendFormat(" AND o.CreateTime <= '{0} 23:59:59' ", eDate);
}
if (SaleId > 0)
{
builder.AppendFormat(" AND ( EnterID={0} OR CreateBy={0} ) ", SaleId);
}
builder.AppendFormat(" GROUP BY o.OrderId ");
return Get<RB_Order_ViewModel>(builder.ToString()).ToList();
}
}
}