using Edu.Common.Enum;
using Edu.Model.Entity.Course;
using Edu.Model.Entity.Grade;
using Edu.Model.ViewModel.Course;
using Edu.Model.ViewModel.Grade;
using Edu.Model.ViewModel.Sell;
using Edu.Model.ViewModel.User;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB.Dapper;
namespace Edu.Repository.Grade
{
/// <summary>
/// 班级仓储层
/// </summary>
public class RB_ClassRepository : BaseRepository<RB_Class>
{
/// <summary>
/// 获取班级列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetClassListRepository(RB_Class_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.* ,IFNULL(B.CourseName,'') AS CourseName,IFNULL(C.GuestNum,0) AS GuestNum,IFNULL(D.SName,'') AS SchoolName
FROM rb_class AS A LEFT JOIN rb_course AS B ON A.CouseId=B.CourseId
LEFT JOIN(SELECT ClassId,SUM(GuestNum) AS GuestNum FROM rb_order WHERE OrderState=1 GROUP BY ClassId) AS C ON A.ClassId=C.ClassId
LEFT JOIN rb_school AS D ON A.School_Id=D.SId
WHERE 1=1
");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)DateStateEnum.Normal);
if (query != null)
{
if (query.CouseId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
}
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassId), query.ClassId);
}
if (!string.IsNullOrEmpty(query.Q_ClassIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_ViewModel.ClassId), query.Q_ClassIds);
}
if (query.School_Id > -1)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.School_Id), query.School_Id);
}
if (query.Teacher_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Teacher_Id), query.Teacher_Id);
}
if (!string.IsNullOrEmpty(query.MoreStatus))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_ViewModel.ClassStatus), query.MoreStatus);
}
//查询未报满的班级
if (query.IsQuerySurplus == 1)
{
builder.AppendFormat(" AND A.{0}>IFNULL(C.GuestNum,0) ", nameof(RB_Class_ViewModel.ClassPersion));
}
if (query.Teacher_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Teacher_Id), query.Teacher_Id);
}
}
return Get<RB_Class_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取班级课程名称以及老师信息列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetClassAndCourseListRepository(RB_Class_ViewModel query)
{
var parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,B.CourseName,D.TeacherName,IFNULL(D.TeacherHead,'') AS TeacherHead ,E.SName AS SchoolName,c.CateName,E.ManagerId,IFNULL(t.GuestNum,0) AS OrderStudentCount
FROM rb_class AS A LEFT JOIN rb_course AS B ON A.CouseId=B.CourseId
LEFT JOIN rb_course_category c on b.CateId = c.CateId
LEFT JOIN rb_teacher AS D ON A.Teacher_Id=D.TId
LEFT JOIN rb_school AS E ON A.School_Id=E.SId
LEFT JOIN (SELECT ClassId,SUM(GuestNum) AS GuestNum FROM rb_order WHERE OrderState IN (1,2) GROUP BY ClassId)t on A.ClassId = t.ClassId
WHERE 1=1
");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.School_Id), query.School_Id);
}
if (query.CouseId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassId), query.ClassId);
}
if (!string.IsNullOrEmpty(query.Q_ClassIds))
{
builder.AppendFormat(" AND A.{0} in ({1}) ", nameof(RB_Class_ViewModel.ClassId), query.Q_ClassIds);
}
if (query.Status >= 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)query.Status);
}
if (query.ClassStatus > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassStatus), (int)query.ClassStatus);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Class_ViewModel.OpenTime), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Class_ViewModel.OpenTime), query.EndTime);
}
if (!string.IsNullOrWhiteSpace(query.ClassName))
{
builder.AppendFormat(" AND A.{0} LIKE @ClassName ", nameof(RB_Class_ViewModel.ClassName));
parameters.Add("ClassName", "%" + query.ClassName.Trim() + "%");
}
if (!string.IsNullOrWhiteSpace(query.CourseName))
{
builder.AppendFormat(" AND B.{0} LIKE @CourseName ", nameof(RB_Class_ViewModel.CourseName));
parameters.Add("CourseName", "%" + query.CourseName.Trim() + "%");
}
if (!string.IsNullOrWhiteSpace(query.TeacherName))
{
builder.AppendFormat(" AND D.{0} LIKE @TeacherName ", nameof(RB_Class_ViewModel.TeacherName));
parameters.Add("TeacherName", "%" + query.TeacherName.Trim() + "%");
}
}
return Get<RB_Class_ViewModel>(builder.ToString(), parameters).ToList();
}
/// <summary>
/// 获取班级列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetClassListForProperty(RB_Class_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,B.CourseName
FROM rb_class AS A
left join rb_course AS B ON A.CouseId=B.CourseId
WHERE 1=1
");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)DateStateEnum.Normal);
builder.AppendFormat(" AND A.{0} in(1,2,3) ", nameof(RB_Class_ViewModel.ClassStatus));// 未开班 + 学习中的
if (query != null)
{
if (query.CouseId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
}
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassId), query.ClassId);
}
if (!string.IsNullOrEmpty(query.Q_ClassIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_ViewModel.ClassId), query.Q_ClassIds);
}
if (query.ClassStatus > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassStatus), (int)query.ClassStatus);
}
}
return Get<RB_Class_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取班级分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetClassPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Class_ViewModel query)
{
var parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,B.CourseName,C.AssistName,IFNULL(C.AssistIcon,'') AS AssistIcon,D.TeacherName,IFNULL(D.TeacherHead,'') AS TeacherHead
,E.SName AS SchoolName,IFNULL(F.RoomName,'') AS RoomName,plan.TotalPlanNum,ySplan.UsePlanNum
FROM rb_class AS A LEFT JOIN rb_course AS B ON A.CouseId=B.CourseId
LEFT JOIN rb_assist AS C ON A.Assist_Id=C.AId
LEFT JOIN rb_teacher AS D ON A.Teacher_Id=D.TId
LEFT JOIN rb_school AS E ON A.School_Id=E.SId
LEFT JOIN rb_class_room AS F ON A.ClassRoomId=F.RoomId
LEFT JOIN ((SELECT ClassId,COUNT(*) AS TotalPlanNum FROM rb_class_plan where `Status`=0 GROUP BY ClassId)) as plan on plan.ClassId=a.ClassId
LEFT JOIN ((SELECT ClassId,COUNT(*) AS UsePlanNum FROM rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY ClassId)) as ySplan on ySplan.ClassId=a.ClassId
WHERE 1=1
");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id >-1)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.School_Id), query.School_Id);
}
if (query.Status >= 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)query.Status);
}
if (query.CouseId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
}
if (query.ClassStatus > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassStatus), (int)query.ClassStatus);
}
if (!string.IsNullOrEmpty(query.QClassStatus))
{
builder.AppendFormat(" AND A.{0} IN ({1}) ", nameof(RB_Class_ViewModel.ClassStatus), query.QClassStatus);
}
if (!string.IsNullOrWhiteSpace(query.ClassName))
{
builder.AppendFormat(" AND A.{0} LIKE @ClassName ", nameof(RB_Class_ViewModel.ClassName));
parameters.Add("ClassName", "%" + query.ClassName.Trim() + "%");
}
if (!string.IsNullOrEmpty(query.ClassNo))
{
builder.AppendFormat(" AND A.{0} LIKE @ClassNo ", nameof(RB_Class_ViewModel.ClassNo));
parameters.Add("ClassNo", "%" + query.ClassNo.Trim() + "%");
}
if (!string.IsNullOrWhiteSpace(query.CourseName))
{
builder.AppendFormat(" AND B.{0} LIKE @CourseName ", nameof(RB_Course_ViewModel.CourseName));
parameters.Add("CourseName", "%" + query.CourseName.Trim() + "%");
}
if (!string.IsNullOrWhiteSpace(query.AssistName))
{
builder.AppendFormat(" AND C.{0} LIKE @AssistName ", nameof(RB_Assist_ViewModel.AssistName));
parameters.Add("AssistName", "%" + query.AssistName.Trim() + "%");
}
if (!string.IsNullOrWhiteSpace(query.TeacherName))
{
builder.AppendFormat(" AND D.{0} LIKE @TeacherName ", nameof(RB_Teacher_ViewModel.TeacherName));
parameters.Add("TeacherName", "%" + query.TeacherName.Trim() + "%");
}
if (!string.IsNullOrEmpty(query.StartTime))
{
builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Class_ViewModel.OpenTime), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Class_ViewModel.OpenTime), query.EndTime);
}
if (query.OldClassId > 0)
{
builder.AppendFormat(" AND A.{0}<>{1} ", nameof(RB_Class_ViewModel.ClassId), query.OldClassId);
}
if (query.Teacher_Id > 0)
{
builder.AppendFormat(" AND D.{0}={1} ", nameof(RB_Teacher_ViewModel.TId), query.Teacher_Id);
}
}
return GetPage<RB_Class_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
}
/// <summary>
/// 更新班级结课日期
/// </summary>
/// <param name="classId"></param>
/// <returns></returns>
public bool UpdateClassEndDateRepository(int classId)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
UPDATE rb_class AS A
INNER JOIN(
SELECT MAX(ClassDate) AS ClassDate,ClassId
FROM rb_class_plan WHERE Status=0
GROUP BY ClassId
) AS B ON A.ClassId=B.ClassId
SET A.EndClassDate=B.ClassDate
WHERE A.ClassId={0}
", classId);
return base.Execute(builder.ToString()) > 0;
}
/// <summary>
/// 获取日语培训分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetClassProductPageList(int pageIndex, int pageSize, out long rowsCount, RB_Class_ViewModel query)
{
var parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,B.CourseName,B.CourseFeature,D.TeacherName,IFNULL(D.TeacherHead,'') AS TeacherHead,E.SName AS SchoolName,R.RoomName
,IFNULL(t.GuestNum,0) AS OrderStudentCount,IFNULL(B.SellPrice,0) AS CourseSellPrice,IFNULL(B.OriginalPrice,0) AS CourseOriginalPrice
FROM rb_class AS A LEFT JOIN rb_course AS B ON A.CouseId=B.CourseId
LEFT JOIN rb_teacher AS D ON A.Teacher_Id=D.TId
LEFT JOIN rb_class_room AS R ON A.ClassRoomId=R.RoomId
LEFT JOIN rb_school AS E ON A.School_Id=E.SId
LEFT JOIN (SELECT ClassId,SUM(GuestNum) AS GuestNum FROM rb_order WHERE OrderState IN (1,2) GROUP BY ClassId)t on A.ClassId = t.ClassId
WHERE 1=1
");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), 0);
builder.AppendFormat(" AND A.{0} in(1,2) ", nameof(RB_Class_ViewModel.ClassStatus));
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > -1)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassId), query.ClassId);
}
if (query.CouseId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
}
//课程分类编号
if (query.CateId > 0)
{
builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_ViewModel.CateId), query.CateId);
}
if (query.Teacher_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Teacher_Id), query.Teacher_Id);
}
if (!string.IsNullOrWhiteSpace(query.ClassName))
{
builder.AppendFormat(" AND A.{0} LIKE @ClassName ", nameof(RB_Class_ViewModel.ClassName));
parameters.Add("ClassName", "%" + query.ClassName.Trim() + "%");
}
if (!string.IsNullOrEmpty(query.ClassNo))
{
builder.AppendFormat(" AND A.{0} ='{1}' ", nameof(RB_Class_ViewModel.ClassNo), query.ClassNo);
}
if (!string.IsNullOrWhiteSpace(query.CourseName))
{
builder.AppendFormat(" AND B.{0} LIKE @CourseName ", nameof(RB_Class_ViewModel.CourseName));
parameters.Add("CourseName", "%" + query.CourseName.Trim() + "%");
}
if (!string.IsNullOrWhiteSpace(query.TeacherName))
{
builder.AppendFormat(" AND D.{0} LIKE @TeacherName ", nameof(RB_Class_ViewModel.TeacherName));
parameters.Add("TeacherName", "%" + query.TeacherName.Trim() + "%");
}
if (!string.IsNullOrEmpty(query.StartTime))
{
builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_ViewModel.OpenTime), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_ViewModel.OpenTime), query.EndTime);
}
//报名截止日期开始
if (!string.IsNullOrEmpty(query.JoinStartTime))
{
builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_ViewModel.EndOrderTime), query.JoinStartTime);
}
//报名截止日期结束
if (!string.IsNullOrEmpty(query.JoinEndTime))
{
builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_ViewModel.EndOrderTime), query.JoinEndTime);
}
if (query.Q_CanApply == 1)
{
builder.AppendFormat(" AND (IFNULL(A.ClassPersion,0)-IFNULL(t.GuestNum,0))>0 ");
builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_ViewModel.EndOrderTime), DateTime.Now.ToString("yyyy-MM-dd"));
}
//销售平台
if (!string.IsNullOrEmpty(query.Saleplat))
{
var salePlatList = Common.ConvertHelper.StringToList(query.Saleplat);
string str = "";
if (salePlatList != null && salePlatList.Count > 0)
{
for (var i = 0; i < salePlatList.Count; i++)
{
if (i == 0)
{
str += string.Format(" FIND_IN_SET('{0}', B.Saleplat)>0 ", salePlatList[i]);
}
else
{
str += string.Format(" AND FIND_IN_SET('{0}', B.Saleplat)>0 ", salePlatList[i]);
}
}
}
if (!string.IsNullOrEmpty(str))
{
builder.AppendFormat(" AND ({0}) ", str);
}
}
}
builder.AppendFormat(" order by A.{0} asc", nameof(RB_Class_ViewModel.OpenTime));
return GetPage<RB_Class_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
}
/// <summary>
/// 班级类型统计
/// </summary>
/// <param name="qClassTypeIds"></param>
/// <returns></returns>
public List<ClassTypeStatic_ViewModel> GetClassTypeStaticListRepository(string qClassTypeIds)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.ClassType,A.TotalClassNum,IFNULL(B.CompleteClassNum,0) AS CompleteClassNum
FROM (
SELECT ClassType, COUNT(1) AS TotalClassNum
FROM rb_class WHERE `Status`=0 AND ClassStatus IN(1,2,3) AND ClassType IN({0})
GROUP BY ClassType
) AS A LEFT JOIN
(
SELECT ClassType, COUNT(1) AS CompleteClassNum
FROM rb_class WHERE `Status`=0 AND ClassStatus=3 AND ClassType IN({0})
GROUP BY ClassType
) AS B ON A.ClassType=B.ClassType
", qClassTypeIds);
return Get<ClassTypeStatic_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取教师列表
/// </summary>
/// <param name="GroupId"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetTeachingPerfClassList(int GroupId)
{
string sql = $@"
SELECT c.*
FROM rb_class c LEFT JOIN rb_teaching_perf t on (c.ClassId = t.ClassId AND t.`Status`=0)
WHERE c.`Status`=0 and c.Group_Id ={GroupId} and c.ClassStatus =3 AND t.Id IS NULL";
return Get<RB_Class_ViewModel>(sql).ToList();
}
/// <summary>
/// 统计教师带班数量
/// </summary>
/// <param name="teacherIds"></param>
/// <returns></returns>
public List<TeacherClassStatic_ViewModel> GetTeacherClassCountRepository(string teacherIds)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT Teacher_Id,COUNT(1) AS ClassCount
FROM rb_class
WHERE status=0 AND ClassStatus<>4 AND Teacher_Id IN({0})
GROUP BY Teacher_Id
", teacherIds);
return Get<TeacherClassStatic_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 根据学生id获取教师账户
/// </summary>
/// <param name="teacherIds"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetListByStudentId(int Student_Id, int Group_Id)
{
var parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT c.*,cp.ClassPlanId,cp.ClassDate
FROM rb_class as c LEFT JOIN rb_student_orderguest as sog on sog.ClassId=c.ClassId
LEFT JOIN (SELECT ClassId,MIN(ClassDate) as ClassDate,MIN(ClassPlanId) as ClassPlanId from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%y-%M-%d')>=DATE_FORMAT(now(),'%y-%M-%d') GROUP BY ClassId) as cp
on c.ClassId=cp.ClassId
WHERE c.`Status`=0 and sog.`Status`=0 ");
builder.AppendFormat(" AND sog.Student_Id={0} ", Student_Id);
builder.AppendFormat(" AND c.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), Group_Id);
return Get<RB_Class_ViewModel>(builder.ToString(), parameters).ToList();
}
/// <summary>
/// 获取app约课
/// </summary>
/// <param name="Group_Id"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetStuAppointment(int Group_Id)
{
var parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"SELECT a.CourseClassType,a.StudentNumType,SUM(b.OrderNum) as OrderStudentCount from rb_class as a LEFT JOIN
(SELECT ClassId,COUNT(OrderId) as OrderNum from rb_order where OrderState=1 GROUP BY ClassId ) as b on a.ClassId=b.ClassId
where a.`Status`=0 and a.IsSubscribe=1 and ClassStatus in(1,2)");
builder.AppendFormat(" AND a.Group_Id={0} ", Group_Id);
builder.AppendFormat(" GROUP BY a.CourseClassType,a.StudentNumType ");
return Get<RB_Class_ViewModel>(builder.ToString(), parameters).ToList();
}
/// <summary>
/// 获取班级编码
/// </summary>
/// <param name="OpenTime"></param>
/// <returns></returns>
public int GetMaxClassNoPrefix(string OpenTime)
{
string sql = $@"SELECT Max(ClassLetterNum) AS ClassLetterNum FROM RB_Class WHERE Status =0 AND OpenTime ='{OpenTime}'";
var obj = ExecuteScalar(sql);
return obj == null ? 0 : Convert.ToInt32(obj);
}
}
}