using Edu.Common.Enum;
using Edu.Common.Enum.Course;
using Edu.Model.Entity.Grade;
using Edu.Model.ViewModel.Grade;
using Edu.Model.ViewModel.Reserve;
using Edu.Model.ViewModel.Scroll;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB;
namespace Edu.Repository.Grade
{
/// <summary>
/// 班级上课计划仓储层
/// </summary>
public class RB_Class_PlanRepository : BaseRepository<RB_Class_Plan>
{
/// <summary>
/// 获取班级上课计划列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetClassPlanListRepository(RB_Class_Plan_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,IFNULL(B.RoomName,'') AS RoomName,IFNULL(T.TeacherName,'') AS TeacherName,IFNULL(T.TeacherHead,'') AS UserIcon,IFNULL(C.ClassName,'') AS ClassName
,Time.StartTime,time.EndTime,time.ClassTimeId,time.TimeHour
,TIMESTAMPDIFF(MINUTE, CONCAT('2020-12-16',' ',time.StartTime), CONCAT('2020-12-16',' ',time.EndTime)) AS ClassMinutes
FROM RB_Class_Plan AS A LEFT JOIN rb_class_room AS B ON A.ClassRoomId=B.RoomId
LEFT JOIN rb_teacher AS T ON A.TeacherId=T.TId
LEFT JOIN rb_class AS C ON A.ClassId=C.ClassId
LEFT JOIN rb_class_time AS time ON A.ClassPlanId=Time.ClassPlanId
WHERE 1=1
");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)DateStateEnum.Normal);
if (query != null)
{
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (query.ClassPlanId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
}
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
}
if (!string.IsNullOrEmpty(query.QClassPlanIds))
{
builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.QClassPlanIds);
}
if (!string.IsNullOrEmpty(query.QClassIds))
{
builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
}
if (!string.IsNullOrEmpty(query.QMonth))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m')= DATE_FORMAT('{1}-01','%y/%m') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QMonth);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
}
if (query.IsQRepeat == 1)
{
builder.AppendFormat(" AND IFNULL(A.{0},'')<>'' ", nameof(RB_Class_Plan_ViewModel.RepeatPlanIds));
}
if (query.PlanType > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.PlanType), query.PlanType);
}
if (!string.IsNullOrEmpty(query.QClassDateStr))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m/%d')= DATE_FORMAT('{1}','%y/%m/%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QClassDateStr);
}
}
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取班级上课计划列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetClassPlanListExtRepository(RB_Class_Plan_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,IFNULL(B.RoomName,'') AS RoomName,IFNULL(T.TeacherName,'') AS TeacherName,IFNULL(T.TeacherHead,'') AS UserIcon
,Time.StartTime,time.EndTime,time.ClassTimeId
,TIMESTAMPDIFF(MINUTE, CONCAT('2020-12-16',' ',time.StartTime), CONCAT('2020-12-16',' ',time.EndTime)) AS ClassMinutes
,time.TimeHour,c.ClassName,IFNULL(course.CourseName,'') AS CourseName
FROM RB_Class_Plan AS A INNER JOIN rb_class as c ON c.ClassId=A.ClassId
LEFT JOIN rb_class_room AS B ON A.ClassRoomId=B.RoomId
LEFT JOIN rb_teacher AS T ON A.TeacherId=T.TId
LEFT JOIN rb_class_time AS time ON A.ClassPlanId=Time.ClassPlanId
LEFT JOIN rb_course AS course ON c.CouseId=course.CourseId
WHERE 1=1 AND C.`Status`=0 AND C.ClassStatus<>3
");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)DateStateEnum.Normal);
if (query != null)
{
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (query.ClassRoomId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
}
if (query.ClassPlanId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
}
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (query.TeacherId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
}
if (query.ClassTimeId > 0)
{
builder.AppendFormat(" AND time.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassTimeId), query.ClassTimeId);
}
if (!string.IsNullOrEmpty(query.QClassIds))
{
builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
}
if (!string.IsNullOrEmpty(query.QMonth))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m')= DATE_FORMAT('{1}-01','%y/%m') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QMonth);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
}
if (!string.IsNullOrEmpty(query.QClassDateStr))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m/%d')= DATE_FORMAT('{1}','%y/%m/%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QClassDateStr);
}
if (query.PlanType > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.PlanType), query.PlanType);
}
}
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取上课计划
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetClassPlanList_V2(RB_Class_Plan_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT a.*,c.Teacher_Id,if(a.PlanType=2,'预约课',c.ClassName) as ClassName,c.ClassType,c.ClassHourMinute,T.TeacherName,if(a.PlanType=2,cou2.CourseName,cou.CourseName) as CourseName,if(a.PlanType=2,cou2.AddHoursMoney,cou.AddHoursMoney) as CourseAddHoursMoney
FROM rb_class_plan as a
LEFT JOIN rb_class c on a.ClassId = c.ClassId
LEFT JOIN rb_teacher AS T ON a.TeacherId=T.TId
LEFT JOIN rb_course as cou on cou.CourseId=c.CouseId
LEFT JOIN rb_course as cou2 on cou2.CourseId=a.CourseId
where a.`Status`=0 and case when a.PlanType=2 then 1=1 else c.ClassStatus in(2,3) and c.`Status`=0 end");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (query.ClassRoomId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
}
if (query.ClassPlanId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
}
if (query.TeacherId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
}
if (!string.IsNullOrEmpty(query.Q_TeacherIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.Q_TeacherIds);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
}
}
return Get<RB_Class_Plan_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_Plan_ViewModel> GetClassPlanModelRepository(RB_Class_Plan_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT a.*
FROM rb_class_plan as a
WHERE a.`Status`=0 ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (query.TeacherId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
}
if (!string.IsNullOrEmpty(query.QClassDateStr))
{
builder.AppendFormat(@" AND DATE_FORMAT(a.ClassDate,'%Y-%m-%d')=DATE_FORMAT('{0}','%Y-%m-%d') ", query.QClassDateStr);
}
else
{
builder.AppendFormat(@" AND DATE_FORMAT(a.ClassDate,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') ");
}
}
builder.AppendFormat(@" ORDER BY ClassDate DESC ");
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 判断是否存在
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool CheckExistsRepository(RB_Class_Plan_ViewModel model)
{
List<WhereHelper> list = new List<WhereHelper>()
{
new WhereHelper (nameof(RB_Class_Plan_ViewModel.ClassId),model.ClassId),
new WhereHelper (nameof(RB_Class_Plan_ViewModel.ClassDate),model.ClassDate),
};
return base.Exists(list);
}
/// <summary>
/// 批量验证同一班级的上课计划
/// </summary>
/// <param name="classId"></param>
/// <param name="qClassDate"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> BatchCheckExistsRepository(int classId, string qClassDate)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*
FROM rb_class_plan AS A
WHERE A.`Status`=0 AND A.ClassId={0} AND A.ClassDate IN({1})
", classId, qClassDate);
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 老师改变则修改后面的代课老师为新的教师编号
/// </summary>
/// <param name="TeacherId"></param>
/// <param name="classId"></param>
/// <returns></returns>
public bool UpdatePlanTeacherIdRepository(int TeacherId, int classId)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
UPDATE rb_class_plan
SET TeacherId={0}
WHERE ClassId={1} AND ClassDate>'{2}'
", TeacherId, classId, DateTime.Now.ToString("yyyy-MM-dd"));
return base.Execute(builder.ToString()) > 0;
}
/// <summary>
/// 根据编号删除班级上课计划
/// </summary>
/// <param name="ClassPlanIds"></param>
/// <returns></returns>
public bool DeleteClassPlanRepository(string ClassPlanIds)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@" DELETE FROM RB_Class_Plan WHERE ClassPlanId IN({0}) ", ClassPlanIds);
return base.Execute(builder.ToString()) > 0;
}
/// <summary>
/// 获取班级最后一次上课日期
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetClassPlanMaxDataRepository(RB_Class_Plan_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.ClassId,MAX(A.ClassDate) AS ClassDate
FROM rb_class_plan AS A
WHERE 1=1 ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (!string.IsNullOrEmpty(query.QClassIds))
{
builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
}
}
return Get<RB_Class_Plan_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_Plan_ViewModel> GetClassPlanLogPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Class_Plan_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT a.*,b.Teacher_Id,c.TeacherName
FROM rb_class_plan as a
LEFT JOIN rb_class as b on a.ClassId=b.ClassId
LEFT JOIN rb_teacher as c on c.TId=a.TeacherId
WHERE a.`Status`=0 and DATE_FORMAT(a.ClassDate,'%Y-%m-%d')<=DATE_FORMAT(NOW(),'%Y-%m-%d') ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
//if (query.School_Id > 0)
//{
// builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
//}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (query.TeacherId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
}
if (query.Status >= 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)query.Status);
}
}
builder.AppendFormat(@" order by ClassDate desc ");
return GetPage<RB_Class_Plan_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
}
/// <summary>
/// 获取班级累计上课时间（分钟）
/// </summary>
/// <param name="classId"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public int GetClassTotalMinute(int classId, int group_Id)
{
string sql = $@"SELECT SUM(t.minNum) AS minNum FROM(
SELECT TIMESTAMPDIFF(MINUTE, CONCAT('2020-12-16',' ',t.StartTime), CONCAT('2020-12-16',' ',t.EndTime)) AS minNum FROM rb_class_plan p
INNER JOIN rb_class_time t on p.ClassPlanId = t.ClassPlanId
WHERE p.`Status`=0 AND p.Group_Id={group_Id} AND p.ClassId ={classId}
)t;
";
var obj = ExecuteScalar(sql);
if (obj != null)
{
return Convert.ToInt32(obj);
}
else
{
return 0;
}
}
/// <summary>
/// 获取班级上课计划列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetTeacherPlanListRepository(RB_Class_Plan_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT *
FROM
( SELECT (SELECT COUNT(*) from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks,
A.*,class.ClassName,lp.LessonPlanNum,lps.LessonPlanSummaryNum,IFNULL(B.RoomName,'') AS RoomName,IFNULL(T.TeacherName,'') AS TeacherName,IFNULL(T.TeacherHead,'') AS UserIcon
FROM RB_Class_Plan AS A LEFT JOIN rb_class_room AS B ON A.ClassRoomId=B.RoomId
LEFT JOIN rb_class as class on a.ClassId=class.ClassId
LEFT JOIN rb_teacher AS T ON A.TeacherId=T.TId
LEFT JOIN (SELECT ClassPlanId,COUNT(*) as LessonPlanNum from rb_class_lessonplan where `Status`=0 GROUP BY ClassPlanId)as lp on a.ClassPlanId=lp.ClassPlanId
LEFT JOIN (SELECT ClassPlanId,COUNT(*) as LessonPlanSummaryNum from rb_class_lessonplan where `Status`=0 and ISNULL(Summary)=0 and LENGTH(trim(Summary))>0 GROUP BY ClassPlanId) as lps on a.ClassPlanId=lps.ClassPlanId
WHERE 1=1 and class.ClassStatus in(1,2,3) and t.`Status`=0 and a.`Status`=0 and class.`Status`=0 ORDER BY a.ClassId,a.ClassDate
) AS a
WHERE 1=1 ");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)DateStateEnum.Normal);
if (query != null)
{
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (query.ClassPlanId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
}
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
}
if (query.TeacherId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
}
if (!string.IsNullOrEmpty(query.QClassIds))
{
builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
}
if (!string.IsNullOrEmpty(query.QMonth))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m')= DATE_FORMAT('{1}-01','%y/%m') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QMonth);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
}
}
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取学生上课计划列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetStudentPlanListRepository(RB_Class_Plan_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat($@"
SELECT a.*,c.ClassName,lp.LessonPlanNum,c.ClassHours,b.RoomName,IFNULL(T.TeacherHead,'') AS UserIcon,IFNULL(T.TeacherName,'') AS TeacherName FROM rb_class_plan as a LEFT JOIN rb_class as c on a.ClassId=c.ClassId
LEFT JOIN rb_class_room as b on a.ClassRoomId=b.RoomId
LEFT JOIN rb_teacher AS T ON c.Teacher_Id=T.TId
LEFT JOIN rb_student_orderguest as sog on sog.ClassId=c.ClassId
LEFT JOIN (SELECT ClassPlanId,COUNT(*) as LessonPlanNum from rb_class_lessonplan where `Status`=0 GROUP BY ClassPlanId)as lp on a.ClassPlanId=lp.ClassPlanId
where sog.Account_Id={query.StuId} and c.ClassStatus in(1,2) and c.`Status`=0 and sog.`Status`=0
");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)DateStateEnum.Normal);
if (query != null)
{
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (query.ClassPlanId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
}
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
}
if (query.Teacher_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Teacher_Id), query.Teacher_Id);
}
if (!string.IsNullOrEmpty(query.QClassIds))
{
builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
}
if (!string.IsNullOrEmpty(query.QMonth))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m')= DATE_FORMAT('{1}-01','%y/%m') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QMonth);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
}
}
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 老师上课计划统计
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetClassPlanStatisticalRepository(RB_Class_Plan_ViewModel query)
{
StringBuilder where = new StringBuilder();
where.AppendFormat(" AND B.ClassStatus IN(1,2,3) ");
if (query != null)
{
if (query.Group_Id > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
}
if (query.TeacherId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
}
if (query.ClassRoomId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
}
if (query.School_Id > -1)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
}
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.TeacherId,A.ClassName,A.CourseName,A.CourseId,A.Teacher_Id,A.TeacherName,A.UserIcon,A.RoomName
,SUM(A.minNum) AS ClassMinutes,MIN(A.StartTime) AS StartTime,MAX(A.EndTime) AS EndTime,A.CompleteProgress,a.TotalPlanNum,A.FinishNum,
(SELECT COUNT(*) from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks
FROM
(
SELECT A.ClassPlanId, A.ClassId,A.ClassDate,A.ClassRoomId,A.TeacherId,B.ClassName,C.CourseName,c.CourseId,B.Teacher_Id,IFNULL(D.TeacherName,'') AS TeacherName
,IFNULL(D.TeacherHead,'') AS UserIcon,E.RoomName,TIMESTAMPDIFF(MINUTE, CONCAT('2020-12-16',' ',F.StartTime), CONCAT('2020-12-16',' ',F.EndTime)) AS minNum
,F.StartTime,F.EndTime,B.CompleteProgress,plan.TotalPlanNum,finishPlan.TotalPlanNum AS FinishNum
FROM rb_class_plan AS A INNER JOIN rb_class AS B ON A.ClassId=B.ClassId
LEFT JOIN rb_course AS C ON B.CouseId=C.CourseId
LEFT JOIN rb_teacher AS D ON A.TeacherId=D.TId
LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
LEFT JOIN rb_class_time F on A.ClassPlanId = F.ClassPlanId
LEFT JOIN (SELECT ClassId,COUNT(*) as TotalPlanNum from rb_class_plan where `Status`=0 AND ClassDate<='{1}' GROUP BY ClassId) as finishPlan on finishPlan.ClassId=a.ClassId
LEFT JOIN (SELECT ClassId,COUNT(*) as TotalPlanNum from rb_class_plan where `Status`=0 GROUP BY ClassId) as plan on plan.ClassId=a.ClassId
WHERE 1=1 AND A.`Status`=0 AND B.`Status`=0 {0}
) AS A
GROUP BY A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId,A.CompleteProgress
", where.ToString(),Common.ConvertHelper.FormatDate(DateTime.Now));
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 老师上课计划统计
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetClassPlanStatisticalForYK(RB_Class_Plan_ViewModel query)
{
StringBuilder where = new StringBuilder();
if (query != null)
{
if (query.Group_Id > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
}
if (query.TeacherId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
}
if (query.ClassRoomId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
}
if (query.School_Id > -1)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (query.CourseId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.CourseId), query.CourseId);
}
if (query.PlanType > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.PlanType), query.PlanType);
}
}
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.ClassPlanId,A.StuId,A.CourseId,A.ClassDate,A.ClassRoomId,A.CourseName,A.TeacherName,A.UserIcon,A.RoomName
,MIN(A.StartTime) AS StartTime,MAX(A.EndTime) AS EndTime,A.ChapterNo as Ranks
FROM
(
SELECT A.ClassPlanId,sa.StuId, A.CourseId,A.ClassDate,A.ClassRoomId,C.CourseName,IFNULL(D.TeacherName,'') AS TeacherName
,IFNULL(D.TeacherHead,'') AS UserIcon,E.RoomName,F.StartTime,F.EndTime,sa.ChapterNo
FROM rb_class_plan AS A
INNER JOIN rb_scroll_appointment sa on A.ClassPlanId = sa.ClassPlanId and sa.StuId ={2}
LEFT JOIN rb_course AS C ON A.CourseId=C.CourseId
LEFT JOIN rb_teacher AS D ON A.TeacherId=D.TId
LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
LEFT JOIN rb_class_time F on A.ClassPlanId = F.ClassPlanId
WHERE 1=1 AND A.`Status`=0 {0}
) AS A
GROUP BY A.ClassPlanId,A.StuId,A.CourseId ,A.ClassDate,A.ClassRoomId
", where.ToString(), Common.ConvertHelper.FormatDate(DateTime.Now), query.StuId);
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 查询指定计划的上课课时
/// </summary>
/// <param name="planId"></param>
/// <returns></returns>
public double GetCurrentPlanStudyHoursRepository(int planId)
{
string sql = "select (select Count(0) from rb_class_plan b where b.ClassId=a.ClassId and b.ClassDate<a.ClassDate) from rb_class_plan a where ClassPlanId=" + planId + " and `Status`=0";
object obj = ExecuteScalar(sql);
return Convert.IsDBNull(obj) ? -1 : Convert.ToDouble(obj);
}
#region 老师-我的课表
/// <summary>
/// 获取班级上课记录
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetTeacherPlan(int pageIndex, int pageSize, out long rowsCount, RB_Class_Plan_ViewModel query)
{
StringBuilder where = new StringBuilder();
StringBuilder whereReserve = new StringBuilder();
StringBuilder whereAppoint = new StringBuilder();
where.AppendFormat(" AND B.ClassStatus IN(1,2) and b.`Status`=0 ");
whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.Status), (int)DateStateEnum.Normal);
whereAppoint.AppendFormat(" AND a.{0}={1} and a.State <>5 ", nameof(RB_Reserve_Class_Extend.Status), (int)DateStateEnum.Normal);
string orderby = " a.ClassDate asc ,a.NewPlanDateTime asc";
if (query != null)
{
if (query.Group_Id > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.Group_Id), query.Group_Id);
whereAppoint.AppendFormat(" AND a.{0}={1} ", nameof(RB_Scroll_Appointment_ViewModel.Group_Id), query.Group_Id);
}
if (!string.IsNullOrEmpty(query.StartTime))
{
where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
whereReserve.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Reserve_Class_Extend.ClassDate), query.StartTime);
whereAppoint.AppendFormat(" AND a.{0}>='{1}' ", nameof(RB_Scroll_Appointment_ViewModel.Date), query.StartTime);
}
if (!string.IsNullOrEmpty(query.EndTime))
{
where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
whereReserve.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Reserve_Class_Extend.ClassDate), query.EndTime);
whereAppoint.AppendFormat(" AND a.{0}<='{1} 23:59:59' ", nameof(RB_Scroll_Appointment_ViewModel.Date), query.EndTime);
}
if (query.TeacherId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.TeacherId), query.TeacherId);
whereAppoint.AppendFormat(" AND t.TId={0} ", query.TeacherId);
}
if (query.ClassRoomId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.ClassRoomId), query.ClassRoomId);
whereAppoint.AppendFormat(" AND A.{0}={1} ", nameof(RB_Scroll_Appointment_ViewModel.RoomId), query.ClassRoomId);
}
if (query.School_Id > -1)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
}
if (!string.IsNullOrEmpty(query.StuName))
{
whereAppoint.AppendFormat(@" AND og.GuestName LIKE '%{0}%' ", query.StuName);
whereReserve.AppendFormat(@" AND Exists
(
SELECT 1
FROM rb_visitor_reserve AS vr LEFT JOIN rb_student AS B ON vr.Visitor_Id=B.StuId
WHERE vr.ReserveClassId=A.ReserveClassId AND vr.Status=0 AND B.StuName LIKE '%{0}%'
)
", query.StuName);
where.AppendFormat(@"AND EXISTS
(
SELECT 1 FROM rb_order_guest AS gu INNER JOIN rb_order AS o ON gu.OrderId=o.OrderId WHERE gu.`Status`=0 AND o.OrderState<>3 AND gu.ClassId=A.ClassId AND gu.GuestName LIKE '%{0}%'
)", query.StuName);
}
if (query.AppointState > 0)
{
if (query.AppointState == 1)
{
whereAppoint.AppendFormat(" AND A.{0}=1 ", nameof(RB_Scroll_Appointment_ViewModel.State));
}
else if (query.AppointState == 2)
{
whereAppoint.AppendFormat(" AND A.{0} in(2,3,4) ", nameof(RB_Scroll_Appointment_ViewModel.State));
}
}
if (query.OrderBy == 1)
{
orderby = " a.ClassDate desc ,a.NewPlanDateTime asc";
}
}
StringBuilder builder = new StringBuilder();
StringBuilder builderClass = new StringBuilder();
StringBuilder builderReserveClass = new StringBuilder();
StringBuilder builderAppoint = new StringBuilder();//约课
builderAppoint.AppendFormat(@"
SELECT 3 as ClassType,IFNULL(t1.ClassTimeId,0) as ClassTimeId,a.CourseSTime as NewPlanDateTime,a.CourseSTime as StartTime,a.CourseETime as EndTime, IFNULL(t1.ClassPlanId,0) as ClassPlanId, 0 as ClassId, a.Date as ClassDate, a.RoomId as ClassRoomId,'预约课' as ClassName, c.CourseName
,ac.AccountId as Teacher_Id,t.TeacherName,r.RoomName,r.SeatNum as RoomNum, og.TotalChapterNo as TotalPlanNum, GROUP_CONCAT(a.Id) AS AppointIds,a.ChapterNo,0 as ReserveType
FROM rb_scroll_appointment a INNER JOIN rb_course c on a.CourseId = c.CourseId
INNER JOIN rb_account ac on a.AccountId = ac.Id
INNER JOIN rb_teacher t on t.TId = ac.AccountId
INNER JOIN rb_class_room r on a.RoomId = r.RoomId
INNER JOIN rb_order_guest og on a.GuestId = og.Id
LEFT JOIN rb_class_time t1 on t1.ClassPlanId = a.ClassPlanId
WHERE 1=1 {0} GROUP BY a.CourseSTime,a.CourseETime,a.RoomId,a.Date ", whereAppoint.ToString());
builderReserveClass.AppendFormat(@"
SELECT 2 as ClassType,A.ReserveClassId as ClassTimeId,a.ClassTime AS NewPlanDateTime ,a.ClassTime as StartTime,a.EndTime,A.ClassPlanId,0 as ClassId,a.ClassDate,a.ClassRoomId,'试听课' as ClassName,a.ClassContent as CourseName,
a.TeacherId as Teacher_Id,IFNULL(D.TeacherName,'') AS TeacherName,IFNULL(E.RoomName,'') AS RoomName,
IFNULL(E.SeatNum,0) AS RoomNum,0 as TotalPlanNum,'' as AppointIds,0 as ChapterNo,A.ReserveType
FROM RB_Reserve_Class AS A LEFT JOIN rb_teacher AS D ON A.TeacherId=D.TId
LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
WHERE 1=1 {0} ", whereReserve.ToString());
builderClass.AppendFormat(@"
SELECT 1 as ClassType,F.ClassTimeId,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',f.StartTime,':00') AS NewPlanDateTime,F.StartTime,f.EndTime,A.ClassPlanId, A.ClassId,A.ClassDate,A.ClassRoomId,B.ClassName,C.CourseName,
B.Teacher_Id,IFNULL(D.TeacherName,'') AS TeacherName,E.RoomName,IFNULL(E.SeatNum,0) AS RoomNum,plan.TotalPlanNum , '' as AppointIds,0 as ChapterNo,0 as ReserveType
FROM rb_class_time as F INNER JOIN rb_class_plan AS A on F.ClassPlanId=a.ClassPlanId
INNER JOIN rb_class AS B ON A.ClassId=B.ClassId
LEFT JOIN rb_course AS C ON B.CouseId=C.CourseId
LEFT JOIN rb_teacher AS D ON B.Teacher_Id=D.TId
LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.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
WHERE 1=1 AND A.`Status`=0 {0}
", where.ToString());
if (query.ClassType == 1)
{
builder.AppendFormat(@"SELECT A.ClassType,A.ClassTimeId,A.Teacher_Id,A.TeacherName,A.RoomName, a.RoomNum,A.StartTime,A.EndTime,A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.ClassName,A.CourseName,a.TotalPlanNum,A.ReserveType,
(SELECT COUNT(*) from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks
FROM ( {0} ) AS A
GROUP BY a.ClassType,A.ClassTimeId,A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId ORDER BY {1}", builderClass.ToString(), orderby);
}
else if (query.ClassType == 2)
{
builder.AppendFormat(@"SELECT A.ClassType,A.ClassTimeId,A.Teacher_Id,A.TeacherName,A.RoomName, a.RoomNum,A.StartTime,A.EndTime,A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.ClassName,A.CourseName,a.TotalPlanNum,A.ReserveType,
(SELECT COUNT(*) from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks
FROM ( {0} ) AS A
GROUP BY a.ClassType,A.ClassTimeId,A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId ORDER BY {1} ", builderReserveClass.ToString(), orderby);
}
else if (query.ClassType == 3) {
builder.AppendFormat(@"SELECT A.ClassType,A.ClassTimeId,A.Teacher_Id,A.TeacherName,A.RoomName, a.RoomNum,A.StartTime,A.EndTime,A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.ClassName,A.CourseName,a.TotalPlanNum,A.AppointIds,A.ReserveType,
A.ChapterNo as Ranks
FROM ( {0} ) AS A
GROUP BY a.ClassType,A.ClassTimeId,A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId,A.StartTime ORDER BY {1}", builderAppoint.ToString(), orderby);
}
else
{
builder.AppendFormat(@"SELECT A.ClassType,A.ClassTimeId,A.Teacher_Id,A.TeacherName,A.RoomName, a.RoomNum,A.StartTime,A.EndTime,A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.ClassName,A.CourseName,a.TotalPlanNum,A.AppointIds,A.ReserveType,
case when A.ClassType =3 then A.ChapterNo else (SELECT COUNT(*) from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) end as Ranks
FROM ( {0} UNION all {1} UNION all {3} ) AS A
GROUP BY a.ClassType,A.ClassTimeId,A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId,A.StartTime ORDER BY {2} ", builderClass.ToString(), builderReserveClass.ToString(), orderby, builderAppoint.ToString());
}
return GetPage<RB_Class_Plan_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
}
#endregion
#region 检查上课计划是否重复
/// <summary>
/// 检查上课计划是否存在时间上的交集【HK2021-07-28新增】
/// </summary>
/// <param name="classId"></param>
/// <param name="timeList"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> CheckClassPlanRepository(int classId,List<TimeItem> timeList)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.* FROM
(
SELECT A.*,B.ClassTimeId,CONCAT(DATE_FORMAT(A.ClassDate,'%Y-%m-%d'),' ',B.StartTime,':00') AS StartTime,CONCAT(DATE_FORMAT(A.ClassDate,'%Y-%m-%d'),' ',B.EndTime,':00') AS EndTime
FROM rb_class_plan AS A INNER JOIN rb_class_time AS B ON A.ClassPlanId=B.ClassPlanId
INNER JOIN rb_class AS C ON A.ClassId=C.ClassId
WHERE A.`Status`=0 AND C.Status=0 AND A.ClassDate>='{0}' AND C.ClassStatus IN(2)
) AS A
WHERE 1=1
", Common.ConvertHelper.FormatDate(DateTime.Now));
if (classId > 0)
{
builder.AppendFormat(" AND A.{0}<>{1} ", nameof(RB_Class_Plan_ViewModel.ClassId), classId);
}
if (timeList != null && timeList.Count > 0)
{
string tempStr = "";
int index = 0;
foreach (var item in timeList)
{
if (index == 0)
{
tempStr += string.Format(" (NOT (A.StartTime > '{0}' OR A.EndTime < '{1}')) ", item.EndTime, item.StartTime);
}
else
{
tempStr += string.Format(" OR (NOT (A.StartTime > '{0}' OR A.EndTime < '{1}')) ", item.EndTime, item.StartTime);
}
index++;
}
if (!string.IsNullOrEmpty(tempStr))
{
builder.AppendFormat(" AND ({0}) ", tempStr);
}
}
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 批量添加上课计划
/// </summary>
/// <param name="planList"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> BatchInsertClassPlanRepository(List<RB_Class_Plan_ViewModel> planList)
{
List<RB_Class_Plan_ViewModel> newPlanList = new List<RB_Class_Plan_ViewModel>();
bool flag = true;
if (planList != null && planList.Count > 0)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(" INSERT INTO RB_Class_Plan(ClassId,ClassDate,Status,ClassRoomId,Group_Id,School_Id,TeacherId,BeiKeStatus,FanKuiStatus,ParentFanKuiStatus,PlanType) ");
builder.AppendFormat(" VALUES ");
string tempStr = "";
foreach (var item in planList)
{
tempStr += $",({item.ClassId},'{Common.ConvertHelper.FormatDate(item.ClassDate)}',0,{item.ClassRoomId},{item.Group_Id},{item.School_Id},{item.TeacherId},0,0,0,{item.PlanType}) ";
}
builder.Append(tempStr.TrimStart(','));
flag = base.Execute(builder.ToString()) > 0;
if (flag)
{
newPlanList = GetClassPlanListRepository(new RB_Class_Plan_ViewModel() { ClassId = planList[0].ClassId, StartTime = Common.ConvertHelper.FormatDate(DateTime.Now) });
}
}
return newPlanList;
}
/// <summary>
/// 批量更新上课计划冲突的计划编号
/// </summary>
/// <param name="planList"></param>
/// <returns></returns>
public bool BatchUpdateClassPlanRepository(List<RB_Class_Plan_ViewModel> planList)
{
bool flag = true;
if (planList != null && planList.Count > 0)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(" INSERT INTO RB_Class_Plan(ClassPlanId,RepeatPlanIds) ");
builder.AppendFormat(" VALUES ");
string tempStr = "";
foreach (var item in planList)
{
tempStr += $",({item.ClassPlanId},'{item.RepeatPlanIds}') ";
}
builder.Append(tempStr.TrimStart(','));
builder.Append(" ON DUPLICATE KEY UPDATE RepeatPlanIds =VALUES(RepeatPlanIds) ");
flag = base.Execute(builder.ToString()) > 0;
}
return flag;
}
/// <summary>
/// 检查班级上课计划是否存在重复，有重复则更新冲突的编号
/// </summary>
/// <param name="ClassId"></param>
/// <param name="result"></param>
public void CheckClassPlanRepeatRepository(int ClassId, out bool result)
{
result = false;
List<TimeItem> timeItemList = new List<TimeItem>();
var newPlanList = GetClassPlanListExtRepository(new RB_Class_Plan_ViewModel()
{
ClassId = ClassId,
StartTime = Common.ConvertHelper.FormatDate(DateTime.Now)
});
foreach (var item in newPlanList)
{
timeItemList.Add(new TimeItem()
{
StartTime = Common.ConvertHelper.FormatDate(item.ClassDate) + " " + item.StartTime + ":00",
EndTime = Common.ConvertHelper.FormatDate(item.ClassDate) + " " + item.EndTime + ":00",
});
}
var repeatList = CheckClassPlanRepository(ClassId, timeItemList);
var updatePlanList = new List<RB_Class_Plan_ViewModel>();
foreach (var item in newPlanList)
{
var tempRepeatList = repeatList?.Where(qitem => qitem.ClassDate == item.ClassDate)?.ToList();
List<int> ids = new List<int>();
if (tempRepeatList != null && tempRepeatList.Count > 0)
{
if (item.TeacherId > 0)
{
var teacherList = tempRepeatList?.Where(qitem => qitem.TeacherId == item.TeacherId)?.ToList();
if (teacherList != null && teacherList.Count > 0)
{
var tempIds = Common.ConvertHelper.StringToList(string.Join(",", teacherList.GroupBy(qitem => qitem.ClassPlanId).Select(qitem => qitem.Key)));
if (tempIds != null && tempIds.Count > 0)
{
ids.AddRange(tempIds);
}
}
}
if (item.ClassRoomId > 0)
{
var classroomList = tempRepeatList?.Where(qitem => qitem.ClassRoomId == item.ClassRoomId)?.ToList();
if (classroomList != null && classroomList.Count > 0)
{
var tempIds = Common.ConvertHelper.StringToList(string.Join(",", classroomList.GroupBy(qitem => qitem.ClassPlanId).Select(qitem => qitem.Key)));
if (tempIds != null && tempIds.Count > 0)
{
ids.AddRange(tempIds);
}
}
}
}
string RepeatPlanIds = "";
if (ids != null && ids.Count > 0)
{
RepeatPlanIds = string.Join(",", ids);
if (!result)
{
result = true;
}
}
updatePlanList.Add(new RB_Class_Plan_ViewModel()
{
ClassPlanId = item.ClassPlanId,
RepeatPlanIds = RepeatPlanIds,
});
}
if (updatePlanList != null && updatePlanList.Count > 0)
{
BatchUpdateClassPlanRepository(updatePlanList);
}
}
/// <summary>
/// 批量修改教室或者老师
/// </summary>
/// <param name="TeacherId"></param>
/// <param name="ClassRoomId"></param>
/// <param name="planIds"></param>
/// <returns></returns>
public bool UpdateClassPlanTeacherOrClassRoomIdRepository(int TeacherId, int ClassRoomId, string planIds)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(" UPDATE rb_class_plan SET ");
if (TeacherId > 0)
{
builder.AppendFormat(" TeacherId={0} ", TeacherId);
}
if (ClassRoomId > 0)
{
builder.AppendFormat(" ClassRoomId={0} ", ClassRoomId);
}
builder.AppendFormat(" WHERE ClassPlanId IN({0}) ", planIds);
return base.Execute(builder.ToString()) > 0;
}
#endregion
/// <summary>
/// 生成上课计划列表
/// </summary>
/// <param name="model"></param>
/// <param name="defaultPlanTimeList"></param>
/// <param name="finishClassHours"></param>
/// <param name="BasicMinutes"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> CreateClassPlanRepository(RB_Class_ViewModel model, List<ClassTimeItem> defaultPlanTimeList,decimal finishClassHours,int BasicMinutes,string defaultOpenTime="")
{
List<RB_Class_Plan_ViewModel> classPlanList = new List<RB_Class_Plan_ViewModel>();
if (model.ClassHours > 0 && defaultPlanTimeList != null && defaultPlanTimeList.Count > 0)
{
var srartDate = model.OpenTime;
if (model.OpenTime < DateTime.Now)
{
srartDate = DateTime.Now;
}
if (!string.IsNullOrEmpty(defaultOpenTime))
{
srartDate = Convert.ToDateTime(defaultOpenTime);
}
//按周排课和按月排课
if (model.ClassStyle == ClassStyleEnum.Week || model.ClassStyle == ClassStyleEnum.Month)
{
int i = 0;
double totalHours = Convert.ToDouble(finishClassHours);
while (model.ClassHours > totalHours)
{
var newDate = srartDate.AddDays(i);
foreach (var item in model.WeekDayList)
{
var planModel = new RB_Class_Plan_ViewModel()
{
ClassId = model.ClassId,
ClassPlanId = 0,
ClassRoomId = model.ClassRoomId,
Group_Id = model.Group_Id,
School_Id = model.School_Id,
PlanTimeList = new List<RB_Class_Time_ViewModel>(),
TeacherId = model.Teacher_Id,
ClassDate = newDate
};
if (model.ClassStyle == ClassStyleEnum.Week)
{
switch (item)
{
case "1":
if (newDate.DayOfWeek == DayOfWeek.Monday)
{
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
totalHours += dayHours;
classPlanList.Add(planModel);
}
}
break;
case "2":
if (newDate.DayOfWeek == DayOfWeek.Tuesday)
{
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
totalHours += dayHours;
classPlanList.Add(planModel);
}
}
break;
case "3":
if (newDate.DayOfWeek == DayOfWeek.Wednesday)
{
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
totalHours += dayHours;
classPlanList.Add(planModel);
}
}
break;
case "4":
if (newDate.DayOfWeek == DayOfWeek.Thursday)
{
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
totalHours += dayHours;
classPlanList.Add(planModel);
}
}
break;
case "5":
if (newDate.DayOfWeek == DayOfWeek.Friday)
{
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
totalHours += dayHours;
classPlanList.Add(planModel);
}
}
break;
case "6":
if (newDate.DayOfWeek == DayOfWeek.Saturday)
{
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
totalHours += dayHours;
classPlanList.Add(planModel);
}
}
break;
case "7":
if (newDate.DayOfWeek == DayOfWeek.Sunday)
{
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
totalHours += dayHours;
classPlanList.Add(planModel);
}
}
break;
}
}
else if (model.ClassStyle == ClassStyleEnum.Month)
{
if (newDate.Day == Convert.ToInt32(item))
{
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
totalHours += dayHours;
classPlanList.Add(planModel);
}
}
}
}
i++;
}
}
//固定日期
else if (model.ClassStyle == ClassStyleEnum.FixedDate)
{
foreach (var item in model.WeekDayList)
{
var planModel = new RB_Class_Plan_ViewModel()
{
ClassId = model.ClassId,
ClassPlanId = 0,
ClassRoomId = model.ClassRoomId,
Group_Id = model.Group_Id,
School_Id = model.School_Id,
PlanTimeList = new List<RB_Class_Time_ViewModel>(),
TeacherId = model.Teacher_Id,
ClassDate = Convert.ToDateTime(item)
};
var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
if (timeList != null && timeList.Count > 0)
{
CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
classPlanList.Add(planModel);
}
}
}
//约课
else
{
}
}
return classPlanList;
}
/// <summary>
/// 计算每天课时
/// </summary>
/// <param name="timeList"></param>
/// <param name="classTimeList"></param>
/// <param name="model"></param>
/// <returns></returns>
private double CalcDayClassHoursRepository(List<TimeItem> timeList, List<RB_Class_Time_ViewModel> classTimeList, RB_Class_ViewModel model, int BasicMinutes)
{
double dayClassHours = 0;
double dayMinutes = 0;
foreach (var sItem in timeList)
{
var timeModel = new RB_Class_Time_ViewModel()
{
StartTime = sItem.StartTime,
EndTime = sItem.EndTime,
TimeHour = sItem.TimeHour,
};
classTimeList.Add(timeModel);
var startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " " + sItem.StartTime + ":00");
var endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " " + sItem.EndTime + ":00");
TimeSpan span = endDate.Subtract(startDate);
if (span.TotalMinutes > 0)
{
dayMinutes += span.TotalMinutes;
}
}
if (model.ClassHourMinute > 0)
{
dayClassHours = dayMinutes / model.ClassHourMinute;
}
else
{
dayClassHours = dayMinutes / BasicMinutes;
}
return dayClassHours;
}
/// <summary>
/// 查询明日还有未备课的老师信息
/// </summary>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetNotPerparingTeachRepository()
{
var sql = @"select b.Id as AccountId,b.WorkUserId,c.TeacherName,Count(0) as ClassCount from rb_class_plan a
left join rb_account b on a.TeacherId=b.AccountId and b.AccountType=2
left join rb_teacher c on a.TeacherId=c.Tid
left join rb_class d on a.ClassId=d.ClassId
where ClassDate=DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y-%m-%d') and a.BeiKeStatus=0 and a.TeacherId>0 and d.ClassStatus=2 and d.`Status`=0 and a.`Status`=0 and b.WorkUserId is not null
group by TeacherId";
return Get<RB_Class_Plan_ViewModel>(sql,null).ToList();
}
/// <summary>
/// 获取最近7天上课计划
/// </summary>
/// <param name="classId"></param>
/// <param name="day"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetStudyCourseReview(int classId, int day, int group_Id)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT a.*
FROM rb_class_plan as a
WHERE a.`Status`=0 ");
if (group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), group_Id);
}
if (classId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), classId);
}
builder.AppendFormat(@$" And a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59'");
builder.AppendFormat(@" ORDER BY ClassDate DESC limit " + day);
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取最近7天上课计划 （约课）
/// </summary>
/// <param name="stuId"></param>
/// <param name="courseId"></param>
/// <param name="day"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetStudyCourseReviewForYK(int stuId, int courseId, int day, int group_Id)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT a.*
FROM rb_class_plan as a
inner join rb_scroll_appointment sa on a.ClassPlanId = sa.ClassPlanId
WHERE a.`Status`=0 ");
if (group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), group_Id);
}
if (courseId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.CourseId), courseId);
}
if (stuId > 0) {
builder.AppendFormat(" AND sa.StuId ={0} ", stuId);
}
builder.AppendFormat(@$" and a.PlanType =2 And a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59'");
builder.AppendFormat(@" ORDER BY ClassDate DESC limit " + day);
return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取最近的上课计划
/// </summary>
/// <param name="classId"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public RB_Class_Plan_ViewModel GetToDayClassPlan(int classId, int groupId)
{
string sql = $@"SELECT a.*,(select count(0) from rb_class_plan p where p.`Status`=0 and p.Group_Id ={groupId} and p.ClassId ={classId} and p.ClassDate < a.ClassDate) as LessonPlanNum
FROM rb_class_plan as a
WHERE a.`Status`=0 and a.Group_Id ={groupId} and a.ClassId ={classId}
and a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59'
order by a.ClassDate desc limit 1";
return Get<RB_Class_Plan_ViewModel>(sql).FirstOrDefault();
}
/// <summary>
/// 获取最近的上课计划
/// </summary>
/// <param name="stuId"></param>
/// <param name="courseId"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public RB_Class_Plan_ViewModel GetToDayClassPlanForYK(int stuId, int courseId, int groupId)
{
string sql = $@"SELECT a.*
FROM rb_class_plan as a
inner join rb_scroll_appointment sa on a.ClassPlanId = sa.ClassPlanId
WHERE a.`Status`=0 and a.Group_Id ={groupId} and a.PlanType =2 and a.CourseId ={courseId} and sa.StuId ={stuId}
and a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59'
order by a.ClassDate desc limit 1";
return Get<RB_Class_Plan_ViewModel>(sql).FirstOrDefault();
}
/// <summary>
/// 获取最近的上课计划
/// </summary>
/// <param name="classId"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetStudyClassPlanList(int classId, int groupId)
{
string sql = $@"
SELECT a.*,(select count(0) from rb_class_plan p where p.`Status`=0 and p.Group_Id ={groupId} and p.ClassId ={classId} and p.ClassDate <= a.ClassDate) as LessonPlanNum
FROM rb_class_plan as a
WHERE a.`Status`=0 and a.Group_Id ={groupId} and a.ClassId ={classId}
and a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59'
order by a.ClassDate desc";
return Get<RB_Class_Plan_ViewModel>(sql).ToList();
}
/// <summary>
/// 获取约课的最近上课计划
/// </summary>
/// <param name="classId"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public List<RB_Class_Plan_ViewModel> GetStudyClassPlanListForYK(int stuId, int guestId, int groupId)
{
string sql = $@"SELECT a.*,sa.ChapterNo as LessonPlanNum
FROM rb_class_plan as a
INNER JOIN rb_scroll_appointment sa on a.ClassPlanId = sa.ClassPlanId
WHERE a.`Status`=0 and a.Group_Id ={groupId} and sa.StuId ={stuId} and sa.GuestId ={guestId} and a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59'
GROUP BY a.ClassPlanId order by a.ClassDate desc";
return Get<RB_Class_Plan_ViewModel>(sql).ToList();
}
}
}