using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Edu.Common.Enum.Course;
using Edu.Common.Enum.System;
using System.Web;
using Edu.Model.Entity.Grade;
using Edu.Model.ViewModel.Course;
using Edu.Model.ViewModel.Grade;
using Edu.Model.ViewModel.Sell;
using Spire.Pdf.Exporting.XPS.Schema;
using VT.FW.DB;
using VT.FW.DB.Dapper;
using Edu.Model.Entity.Sell;
using Edu.Model.Entity.User;
using Edu.Model.ViewModel.User;
using Org.BouncyCastle.Bcpg.OpenPgp;
using Castle.Core.Internal;
using Edu.Model.ViewModel.Scroll;
using Edu.Common.Plugin;
using System.Text.RegularExpressions;
namespace Edu.Repository.Grade
{
/// <summary>
/// 学员签到仓储层
/// </summary>
public class RB_Class_CheckRepository : BaseRepository<RB_Class_Check>
{
/// <summary>
/// 获取签到记录列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassCheckListRepository(RB_Class_Check_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,IFNULL(B.GuestName,'') AS GuestName,IFNULL(C.ClassName,'') AS ClassName,IFNULL(D.CourseName,'') AS CourseName,IFNULL(E.RoomName,'') AS RoomName
,IFNULL(F.TeacherName,'') AS TeacherName,IFNULL(B.OrderId,0) AS OrderId,IFNULL(F.TeacherHead,'') AS TeacherHead,ct.StartTime,ct.EndTime
FROM RB_Class_Check AS A LEFT JOIN rb_order_guest AS B ON A.OrderGuestId=B.Id
LEFT JOIN rb_class AS C ON A.ClassId=C.ClassId
LEFT JOIN rb_course AS D ON C.CouseId=D.CourseId
LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
LEFT JOIN rb_teacher AS F ON A.TeacherId=F.TId
LEFT JOIN rb_class_time ct on ct.ClassTimeId = A.ClassTimeId
WHERE 1=1 AND A.Status=0 ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
//builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassId), query.ClassId);
}
if (!string.IsNullOrEmpty(query.Q_ClassIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
}
if (query.OrderGuestId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
}
if (!string.IsNullOrEmpty(query.QClassPlanIds))
{
builder.AppendFormat(" AND ct.ClassPlanId IN({0}) ", query.QClassPlanIds);
}
if (!string.IsNullOrEmpty(query.QOrderGuestIds))
{
builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.QOrderGuestIds);
}
if (!string.IsNullOrWhiteSpace(query.StartDate))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')>=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
}
if (!string.IsNullOrWhiteSpace(query.EndDate))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')<=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.EndDate);
}
}
return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取签到列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassCheckStatistics(RB_Class_Check_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.ClassId,A.ClassDate,COUNT(0) AS CheckNum
FROM RB_Class_Check AS A
WHERE 1=1 AND `Status` =0 and CheckStatus =0 ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassId), query.ClassId);
}
if (!string.IsNullOrEmpty(query.Q_ClassIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
}
if (!string.IsNullOrWhiteSpace(query.StartDate))
{
builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
}
if (!string.IsNullOrWhiteSpace(query.EndDate))
{
builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_Check_ViewModel.ClassDate), query.EndDate);
}
}
builder.AppendFormat(" GROUP BY A.ClassId,A.ClassDate ");
return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取签到列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassCheckStatisticsForYK(RB_Class_Check_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT C.ClassPlanId as ClassTimeId,A.ClassDate,COUNT(0) AS CheckNum
FROM RB_Class_Check AS A
inner join rb_class_time C on A.ClassTimeId = C.ClassTimeId
WHERE 1=1 AND A.`Status` =0 and A.CheckStatus =0 ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
}
if (!string.IsNullOrEmpty(query.QClassPlanIds))
{
builder.AppendFormat(" AND C.ClassPlanId in({0}) ", query.QClassPlanIds);
}
if (!string.IsNullOrWhiteSpace(query.StartDate))
{
builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
}
if (!string.IsNullOrWhiteSpace(query.EndDate))
{
builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_Check_ViewModel.ClassDate), query.EndDate);
}
}
builder.AppendFormat(" GROUP BY C.ClassPlanId,A.ClassDate ");
return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 班级总人数
/// </summary>
/// <param name="classId"></param>
/// <returns></returns>
public int GetClassToalPeopleNum(int classId, int groupId)
{
string sql = $@"SELECT count(*) AS Num FROM(SELECT OrderGuestId FROM rb_class_check WHERE `Status`=0 AND Group_Id={groupId} AND ClassId ={classId} GROUP BY OrderGuestId)t";
var obj = ExecuteScalar(sql);
if (obj != null)
{
return Convert.ToInt32(obj);
}
else
{
return 0;
}
}
/// <summary>
/// 获取平均上课率
/// </summary>
/// <param name="classId"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public decimal GetClassAvgTakeClassRate(int classId, int group_Id)
{
string sql1 = $@"SELECT COUNT(0) AS TotalNum FROM rb_class_check WHERE `Status`=0 AND Group_Id={group_Id} AND ClassId = {classId};";
string sql2 = $@"SELECT COUNT(0) AS TotalNum FROM rb_class_check WHERE `Status`=0 AND Group_Id={group_Id} AND CheckStatus =0 AND ClassId = {classId};";
var obj1 = ExecuteScalar(sql1);
var obj2 = ExecuteScalar(sql2);
int Num1 = Convert.ToInt32(obj1 ?? "0"), Num2 = Convert.ToInt32(obj2 ?? "0");
if (Num1 == 0) { return 0; }
decimal avgClassRate = Math.Round(Convert.ToDecimal(Num2) / Num1, 2, MidpointRounding.AwayFromZero);
return avgClassRate;
}
/// <summary>
/// 学员完成学习分钟数
/// </summary>
/// <param name="guestIds">学员编号</param>
/// <returns></returns>
public List<GuestFinishMinute_ViewModel> GetGuestFinishMinutesRepository(string guestIds, int ClassCheckId = 0)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.OrderGuestId AS GuestId,SUM(A.StudyNum) AS FinishMinutes,SUM(A.CurrentDeductionHours) AS FinishClassHours
FROM rb_class_check AS A
WHERE A.`Status`=0 ");
if (!string.IsNullOrEmpty(guestIds))
{
builder.AppendFormat(" AND A.OrderGuestId IN({0})", guestIds);
}
if (ClassCheckId > 0)
{
builder.AppendFormat(" AND A.ClassCheckId<>{0} ", ClassCheckId);
}
builder.AppendFormat(" GROUP BY A.OrderGuestId ");
return Get<GuestFinishMinute_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 班级签到完成学习分钟数
/// </summary>
/// <param name="classIds">班级编号</param>
/// <returns></returns>
public List<GuestFinishMinute_ViewModel> GetClassFinishMinutesRepository(string classIds)
{
string where = "";
if (!string.IsNullOrEmpty(classIds))
{
where += string.Format(" AND A.ClassId IN({0}) ", classIds);
}
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.ClassId,SUM(A.FinishMinutes) AS FinishMinutes,SUM(A.CurrentDeductionHours) AS FinishClassHours FROM
(
SELECT A.ClassId,A.ClassDate,B.ClassPlanId,C.ClassTimeId,a.CurrentDeductionHours
,TIMESTAMPDIFF(MINUTE,(DATE_FORMAT(CONCAT('2021-01-01 ',C.StartTime,':00'),'%Y-%m-%d %H:%i')),(DATE_FORMAT(CONCAT('2021-01-01 ',C.EndTime,':00'), '%Y-%m-%d %H:%i'))) AS FinishMinutes
FROM rb_class_check AS A INNER JOIN rb_class_plan AS B ON A.ClassId=B.ClassId AND A.ClassDate=B.ClassDate AND B.`Status`=0
INNER JOIN rb_class_time AS C ON B.ClassPlanId=C.ClassPlanId
WHERE 1=1 {0}
GROUP BY A.ClassId,A.ClassDate,B.ClassPlanId,C.ClassTimeId
) AS A
WHERE 1=1
GROUP BY A.ClassId
", where);
return Get<GuestFinishMinute_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取班级签到列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassCheckList(RB_Class_Check_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@" SELECT A.* FROM RB_Class_Check AS A WHERE 1=1 ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassId), query.ClassId);
}
if (query.ClassCheckId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassCheckId), query.ClassCheckId);
}
if (query.OrderGuestId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
}
if (!string.IsNullOrEmpty(query.QOrderGuestIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.QOrderGuestIds);
}
if (!string.IsNullOrEmpty(query.Q_ClassIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
}
if (!string.IsNullOrEmpty(query.Q_ClassTimeIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.Q_ClassTimeIds);
}
if (!string.IsNullOrWhiteSpace(query.StartDate))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')>=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
}
if (!string.IsNullOrWhiteSpace(query.EndDate))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')<=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.EndDate);
}
if (query.ClassTimeId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.ClassTimeId);
}
if (!string.IsNullOrWhiteSpace(query.ClassTimeIds))
{
builder.AppendFormat(" AND A.{0} in ({1}) ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.ClassTimeIds);
}
}
return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取班级签到列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassCheckList_V2(RB_Class_Check_ViewModel query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat($@" SELECT A.*,sog.Student_Id as StuId FROM RB_Class_Check AS A
inner join rb_student_orderguest sog on A.OrderGuestId = sog.GuestId
WHERE 1=1 ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassId), query.ClassId);
}
if (query.ClassCheckId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassCheckId), query.ClassCheckId);
}
if (query.OrderGuestId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
}
if (query.StuId > 0)
{
builder.AppendFormat(" AND sog.Student_Id ={0} ", query.StuId);
}
if (!string.IsNullOrEmpty(query.Q_ClassIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
}
if (!string.IsNullOrEmpty(query.Q_ClassTimeIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.Q_ClassTimeIds);
}
if (!string.IsNullOrWhiteSpace(query.StartDate))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')>=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
}
if (!string.IsNullOrWhiteSpace(query.EndDate))
{
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')<=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.EndDate);
}
if (query.ClassTimeId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.ClassTimeId);
}
if (!string.IsNullOrWhiteSpace(query.ClassTimeIds))
{
builder.AppendFormat(" AND A.{0} in ({1}) ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.ClassTimeIds);
}
}
return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取学生的上课时间
/// </summary>
/// <param name="groupId"></param>
/// <param name="guestIds"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassGuestStudentTimeList(int groupId, string guestIds, string xFStartTime, string xFEndTime)
{
string where = "";
if (!string.IsNullOrEmpty(xFStartTime))
{
where += $@" and ClassDate >= '{xFStartTime}'";
}
if (!string.IsNullOrEmpty(xFEndTime))
{
where += $@" and ClassDate <= '{xFEndTime} 23:59:59'";
}
string sql = $@"SELECT OrderGuestId, SUM(CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check where `Status`=0 and Group_Id ={groupId} and OrderGuestId in ({guestIds}) {where} group by OrderGuestId";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
/// <summary>
/// 获取订单每月 消费课时
/// </summary>
/// <param name="groupId"></param>
/// <param name="orderIds"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassStudentHoursListForOrderMonth(int groupId, string orderIds)
{
string sql = $@"SELECT g.OrderId,DATE_FORMAT(c.ClassDate,'%Y-%m') as MonthDateStr,SUM(c.CurrentDeductionHours) CurrentDeductionHours FROM rb_order_guest g
left join rb_class_check c on g.Id = c.OrderGuestId
WHERE g.Group_Id ={groupId} and g.`Status`=0 and c.`Status` =0 and g.OrderId in({orderIds})
group by g.OrderId,DATE_FORMAT(c.ClassDate,'%Y-%m');";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
#region 学生出勤管理
/// <summary>
/// 获取学生签到数据
/// </summary>
/// <param name="demodel"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetStudentAttendanceDayStatistics(RB_Class_Check_ViewModel query)
{
DynamicParameters parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,IFNULL(B.GuestName,'') AS GuestName,IFNULL(C.ClassName,'') AS ClassName,C.ClassNo,IFNULL(D.CourseName,'') AS CourseName,IFNULL(E.RoomName,'') AS RoomName
,IFNULL(F.TeacherName,'') AS TeacherName,CONCAT(ct.StartTime,'~',ct.EndTime) as TimeBucket
FROM RB_Class_Check AS A
left join rb_class_time ct on ct.ClassTimeId = A.ClassTimeId
LEFT JOIN rb_order_guest AS B ON A.OrderGuestId=B.Id
LEFT JOIN rb_class AS C ON A.ClassId=C.ClassId
LEFT JOIN rb_course AS D ON C.CouseId=D.CourseId
LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
LEFT JOIN rb_teacher AS F ON A.TeacherId=F.TId
WHERE 1=1 AND A.Status=0 ");
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
}
if (query.School_Id >= 0)
{
builder.AppendFormat(" AND C.{0}={1} ", nameof(RB_Class_Check_ViewModel.School_Id), query.School_Id);
}
if (query.ClassId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassId), query.ClassId);
}
if (query.CouseId > 0)
{
builder.AppendFormat(" AND C.{0}={1} ", nameof(RB_Class_Check_ViewModel.CouseId), query.CouseId);
}
if (query.CheckStatus > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.CheckStatus), query.CheckStatus);
}
if (!string.IsNullOrEmpty(query.Q_ClassIds))
{
builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
}
if (query.OrderGuestId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
}
if (!string.IsNullOrEmpty(query.GuestName))
{
builder.AppendFormat(" AND B.GuestName like @GuestName ");
parameters.Add("GuestName", "%" + query.GuestName + "%");
}
if (!string.IsNullOrWhiteSpace(query.StartDate))
{
builder.AppendFormat(" AND A.{0} ='{1}' ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
}
}
return Get<RB_Class_Check_ViewModel>(builder.ToString(), parameters).ToList();
}
#endregion
#region 报表统计
/// <summary>
/// 获取教师时间范围内 累计上课时长
/// </summary>
/// <param name="groupId"></param>
/// <param name="userIds"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
public List<RB_Class_Check_ViewModel> GetTeacherHoursList(int groupId, string userIds, string startMonth, string endMonth)
{
string sql = $@"SELECT tt.TeacherId,tt.ClassId,SUM(timestampdiff(MINUTE,CONCAT('2021-06-28 ',tt.StartTime),CONCAT('2021-06-28 ',tt.EndTime))) as StudyNum FROM(
SELECT p.TeacherId,p.ClassId,p.ClassTimeId,t.StartTime,t.EndTime FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
WHERE p.`Status`=0 and p.Group_Id ={groupId} and p.TeacherId in ({userIds}) and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
GROUP BY p.TeacherId,p.ClassId,p.ClassTimeId
)tt
GROUP BY tt.TeacherId,tt.ClassId;";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
/// <summary>
/// 获取学生时间范围内 累计上课时长 + 合同课时单价
/// </summary>
/// <param name="groupId"></param>
/// <param name="userIds"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetStudentHoursList(int groupId, string userIds, string startMonth, string endMonth)
{
string sql = $@"SELECT q.TeacherId,q.OrderGuestId,q.CurrentDeductionHours,
case when q.ClassHours>0 and q.TotalSub >0 then
(q.CourseFee - q.DiscountMoney / q.TotalSub * q.CourseFee) /q.ClassHours
else 0 END AS UnitPrice
FROM (
SELECT tt.TeacherId,tt.OrderGuestId,tt.CurrentDeductionHours
,IFNULL(o.PreferPrice,0) -IFNULL(o.TextbookFee,0) -IFNULL(o.CoursewareFee,0) as CourseFee
,IFNULL(o.DiscountMoney,0) as DiscountMoney
,IFNULL(o.PreferPrice,0) as TotalSub
,IFNULL(g.TotalHours,0) as ClassHours
FROM(
SELECT p.TeacherId,p.OrderGuestId,SUM(p.CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check p
WHERE p.`Status`=0 and p.Group_Id ={groupId} and p.TeacherId in ({userIds}) and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
GROUP BY p.TeacherId,p.OrderGuestId
)tt
left join rb_order_guest g on g.Id = tt.OrderGuestId
left join rb_order o on o.OrderId =g.OrderId
GROUP BY tt.TeacherId,tt.OrderGuestId
)q";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
/// <summary>
/// 获取老师课时统计明细
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="classId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetTeacherConsumptionHoursDetialPageList(int pageIndex, int pageSize, out long count, int teacherId, int schoolId, int classId, string startMonth, string endMonth, int group_Id)
{
string where = $@" ";
if (teacherId > 0)
{
where += $@" and p.TeacherId ={teacherId}";
}
if (schoolId >= 0)
{
where += $@" and case when cp.PlanType=2 then cp.School_Id ={schoolId} else c.School_Id ={schoolId} end";
}
if (classId > 0)
{
where += $@" and p.ClassId ={classId}";
}
string sql = $@"
SELECT p.TeacherId,t2.TeacherName,t2.BaseHourFee,t2.BaseHoursEnabled,t2.EnableTime,t2.BaseHoursAdd,if(cp.PlanType=2,'预约课',c.ClassName) as ClassName,if(cp.PlanType=2,co2.CourseName,co.CourseName) as CourseName,p.ClassId,if(cp.PlanType=2,co2.AddHoursMoney,co.AddHoursMoney) as CourseAddHoursMoney,
c.ClassType,c.ClassNo,if(cp.PlanType=2,s2.SName,s.SName) as SName,c.ClassHourMinute,if(cp.PlanType=2,cp.CourseId,c.CouseId) as CouseId,p.ClassDate,p.ClassTimeId,t.StartTime as StartDate,t.EndTime as EndDate,Max(p.CurrentDeductionHours) as CurrentDeductionHours
FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
inner join rb_class_plan cp on t.ClassPlanId = cp.ClassPlanId
LEFT JOIN rb_teacher t2 on p.TeacherId = t2.TId
LEFT JOIN rb_class c on p.ClassId = c.ClassId
LEFT JOIN rb_course co on c.CouseId = co.CourseId
left join rb_course co2 on cp.CourseId = co2.CourseId
left join rb_school s on s.SId = c.School_Id
left join rb_school s2 on s2.SId = cp.School_Id
WHERE p.`Status`=0 and p.Group_Id ={group_Id} {where} and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
GROUP BY p.TeacherId,p.ClassId,p.ClassTimeId
ORDER BY p.ClassDate ASC
";
return GetPage<RB_Class_Check_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
}
/// <summary>
/// 获取老师课时统计明细
/// </summary>
/// <param name="teacherIds"></param>
/// <param name="schoolId"></param>
/// <param name="classId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetTeacherConsumptionHoursDetialList(string teacherIds, int schoolId, int classId, string startMonth, string endMonth, int group_Id)
{
string where = $@" ";
if (!string.IsNullOrEmpty(teacherIds) && teacherIds != "-1" && teacherIds != "0")
{
where += $@" and p.TeacherId in({teacherIds})";
}
if (schoolId >= 0)
{
where += $@" and case when cp.PlanType=2 then cp.School_Id ={schoolId} else c.School_Id ={schoolId} end";
}
if (classId > 0)
{
where += $@" and p.ClassId ={classId}";
}
string sql = $@"
SELECT p.TeacherId,t2.TeacherName,t2.BaseHourFee,t2.BaseHoursEnabled,t2.EnableTime,if(cp.PlanType=2,'预约课',c.ClassName) as ClassName,if(cp.PlanType=2,co2.CourseName,co.CourseName) as CourseName,if(cp.PlanType=2,co2.AddHoursMoney,co.AddHoursMoney) as CourseAddHoursMoney,
p.ClassId,if(cp.PlanType=2,s2.SName,s.SName) as SName,c.ClassType,c.ClassNo,c.ClassHourMinute,if(cp.PlanType=2,cp.CourseId,c.CouseId) as CouseId,p.ClassDate,p.ClassTimeId,t.StartTime as StartDate,t.EndTime as EndDate,Max(p.CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
inner join rb_class_plan cp on t.ClassPlanId = cp.ClassPlanId
LEFT JOIN rb_teacher t2 on p.TeacherId = t2.TId
LEFT JOIN rb_class c on p.ClassId = c.ClassId
LEFT JOIN rb_course co on c.CouseId = co.CourseId
left join rb_course co2 on cp.CourseId = co2.CourseId
left join rb_school s on s.SId = c.School_Id
left join rb_school s2 on s2.SId = cp.School_Id
WHERE p.`Status`=0 and p.Group_Id ={group_Id} {where} and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
GROUP BY p.TeacherId,p.ClassId,p.ClassTimeId
ORDER BY p.ClassDate ASC
";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
/// <summary>
/// 获取学生课时统计明细
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="classId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="studentName"></param>
/// <param name="orderId"></param>
/// <param name="contractNo"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetStudentConsumptionHoursDetialPageList(int pageIndex, int pageSize, out long count, int teacherId, int schoolId, int classId, string startMonth, string endMonth, string studentName, int orderId, string contractNo, int groupId, int StudentId)
{
string where = $@" ";
if (teacherId > 0)
{
where += $@" and p.TeacherId ={teacherId}";
}
if (schoolId >= 0)
{
where += $@" and case when cp.PlanType=2 then cp.School_Id ={schoolId} else c.School_Id ={schoolId} end";
}
if (classId > 0)
{
where += $@" and case when cp.PlanType=2 then t2.ClassId ={classId} else p.ClassId ={classId} end";
}
if (!string.IsNullOrEmpty(studentName))
{
where += $@" and t2.GuestName like '%{studentName}%'";
}
if (orderId > 0)
{
where += $@" and t2.OrderId ={orderId}";
}
if (StudentId > 0)
{
where += $@" and t2.Id ={StudentId}";
}
if (!string.IsNullOrEmpty(contractNo))
{
where += $@" and ec.ContractNo ='{contractNo}'";
}
string sql = $@"
SELECT tt.*,
case when tt.ClassHours>0 and tt.TotalSub >0 then
(tt.CourseFee - tt.DiscountMoney / tt.TotalSub * tt.CourseFee) /tt.ClassHours
else 0 END AS UnitPrice
FROM (
SELECT p.OrderGuestId,p.CurrentDeductionHours,t2.GuestName,p.TeacherId,t3.TeacherName,t2.OrderId,ec.ContractNo,if(cp.PlanType=2,'预约课',c.ClassName) as ClassName,c.ClassNo,if(cp.PlanType=2,co2.CourseName,co.CourseName) as CourseName,p.ClassId,if(cp.PlanType=2,cp.CourseId,c.CouseId) as CouseId
,p.ClassDate,p.ClassTimeId,t.StartTime as StartDate,t.EndTime as EndDate,o.EnterID,o.HelpEnterId
,IFNULL(o.PreferPrice,0) -IFNULL(o.TextbookFee,0) -IFNULL(o.CoursewareFee,0) as CourseFee
,IFNULL(o.DiscountMoney,0) as DiscountMoney
,IFNULL(o.PreferPrice,0) as TotalSub
,IFNULL(t2.TotalHours,0) as ClassHours
,o.JoinType,o.TargetJoinType,o.SourceOrderId,o.TargetOrderId
FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
inner join rb_class_plan cp on t.ClassPlanId = cp.ClassPlanId
LEFT JOIN rb_order_guest t2 on p.OrderGuestId = t2.Id
LEFT JOIN rb_order o on o.OrderId = t2.OrderId
LEFT JOIN rb_teacher t3 on p.TeacherId = t3.TId
LEFT JOIN rb_class c on p.ClassId = c.ClassId
LEFT JOIN rb_course co on c.CouseId = co.CourseId
left join rb_course co2 on cp.CourseId = co2.CourseId
LEFT JOIN rb_education_contract ec on ec.GuestId = p.OrderGuestId
WHERE p.`Status`=0 and p.Group_Id ={groupId} {where} and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
) tt
ORDER BY tt.ClassDate ASC
";
return GetPage<RB_Class_Check_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
}
/// <summary>
/// 获取学生课时统计明细
/// </summary>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="classId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="studentName"></param>
/// <param name="orderId"></param>
/// <param name="contractNo"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetStudentConsumptionHoursDetialList(int teacherId, int schoolId, int classId, string startMonth, string endMonth, string studentName, int orderId, string contractNo, int groupId, string classIds = "", int StudentId = 0)
{
string where = $@" ";
if (teacherId > 0)
{
where += $@" and p.TeacherId ={teacherId}";
}
if (schoolId >= 0)
{
where += $@" and case when cp.PlanType=2 then cp.School_Id ={schoolId} else c.School_Id ={schoolId} end";
}
if (classId > 0)
{
where += $@" and case when cp.PlanType=2 then t2.ClassId ={classId} else p.ClassId ={classId} end";
}
if (!string.IsNullOrEmpty(classIds) && classIds != "0")
{
where += $@" and case when cp.PlanType=2 then t2.ClassId in({classIds}) else p.ClassId in({classIds}) end";
}
if (!string.IsNullOrEmpty(studentName))
{
where += $@" and t2.GuestName like '%{studentName}%'";
}
if (orderId > 0)
{
where += $@" and t2.OrderId ={orderId}";
}
if (StudentId > 0)
{
where += $@" and t2.Id ={StudentId}";
}
if (!string.IsNullOrEmpty(contractNo))
{
where += $@" and ec.ContractNo ='{contractNo}'";
}
string sql = $@"
SELECT tt.*,
case when tt.ClassHours>0 and tt.TotalSub >0 then
(tt.CourseFee - tt.DiscountMoney / tt.TotalSub * tt.CourseFee) /tt.ClassHours
else 0 END AS UnitPrice
FROM (
SELECT p.OrderGuestId,p.CurrentDeductionHours,t2.GuestName,p.TeacherId,t3.TeacherName,t2.OrderId,ec.ContractNo,if(cp.PlanType=2,'预约课',c.ClassName) as ClassName,c.ClassNo,if(cp.PlanType=2,co2.CourseName,co.CourseName) as CourseName,p.ClassId,if(cp.PlanType=2,cp.CourseId,c.CouseId) as CouseId
,p.ClassDate,p.ClassTimeId,t.StartTime as StartDate,t.EndTime as EndDate,o.EnterID,o.HelpEnterId
,IFNULL(o.PreferPrice,0) -IFNULL(o.TextbookFee,0) -IFNULL(o.CoursewareFee,0) as CourseFee
,IFNULL(o.DiscountMoney,0) as DiscountMoney
,IFNULL(o.PreferPrice,0) as TotalSub
,IFNULL(t2.TotalHours,0) as ClassHours
FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
inner join rb_class_plan cp on t.ClassPlanId = cp.ClassPlanId
LEFT JOIN rb_order_guest t2 on p.OrderGuestId = t2.Id
LEFT JOIN rb_order o on o.OrderId = t2.OrderId
LEFT JOIN rb_teacher t3 on p.TeacherId = t3.TId
LEFT JOIN rb_class c on p.ClassId = c.ClassId
LEFT JOIN rb_course co on c.CouseId = co.CourseId
left join rb_course co2 on cp.CourseId = co2.CourseId
LEFT JOIN rb_education_contract ec on ec.GuestId = p.OrderGuestId
WHERE p.`Status`=0 and p.Group_Id ={groupId} {where} and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
) tt
ORDER BY tt.ClassDate ASC
";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
#endregion
#region 营收报表
/// <summary>
/// 获取班级已上课时
/// </summary>
/// <param name="groupId"></param>
/// <param name="classIds"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassLearnHoursList(int groupId, string classIds)
{
string sql = $@"SELECT t.ClassId,SUM(t.CurrentDeductionHours) AS CurrentDeductionHours FROM(
SELECT case when cp.PlanType=2 then t2.ClassId else p.ClassId end as ClassId,p.ClassDate,p.ClassTimeId,MAX(p.CurrentDeductionHours) as CurrentDeductionHours
FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
inner join rb_class_plan cp on t.ClassPlanId = cp.ClassPlanId
LEFT JOIN rb_order_guest t2 on p.OrderGuestId = t2.Id
WHERE p.`Status` =0 and p.Group_Id ={groupId} and case when cp.PlanType=2 then t2.ClassId in({classIds}) else p.ClassId in({classIds}) end GROUP BY case when cp.PlanType=2 then t2.ClassId else p.ClassId end,p.ClassDate,p.ClassTimeId
)t GROUP BY t.ClassId";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
/// <summary>
/// 获取班级学生已上课金额
/// </summary>
/// <param name="groupId"></param>
/// <param name="classIds"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetClassStuLearnMoneyList(int groupId, string classIds)
{
string sql = $@"SELECT hq.ClassId, SUM(hq.CurrentDeductionHours * hq.UnitPrice) AS UnitPrice FROM (
SELECT q.ClassId,q.OrderGuestId,q.CurrentDeductionHours,
case when q.ClassHours>0 and q.TotalSub >0 then
(q.CourseFee - q.DiscountMoney / q.TotalSub * q.CourseFee) /q.ClassHours
else 0 END AS UnitPrice
FROM (
SELECT tt.ClassId,tt.OrderGuestId,tt.CurrentDeductionHours
,IFNULL(o.PreferPrice,0) -IFNULL(o.TextbookFee,0) -IFNULL(o.CoursewareFee,0) as CourseFee
,IFNULL(o.DiscountMoney,0) as DiscountMoney
,IFNULL(o.PreferPrice,0) as TotalSub
,IFNULL(g.TotalHours,0) as ClassHours
FROM(
SELECT case when cp.PlanType=2 then t2.ClassId else p.ClassId end as ClassId,p.OrderGuestId,SUM(p.CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
inner join rb_class_plan cp on t.ClassPlanId = cp.ClassPlanId
LEFT JOIN rb_order_guest t2 on p.OrderGuestId = t2.Id
WHERE p.`Status`=0 and p.Group_Id ={groupId} and case when cp.PlanType=2 then t2.ClassId in({classIds}) else p.ClassId in({classIds}) end
GROUP BY case when cp.PlanType=2 then t2.ClassId else p.ClassId end,p.OrderGuestId
)tt
left join rb_order_guest g on g.Id = tt.OrderGuestId
left join rb_order o on o.OrderId =g.OrderId
GROUP BY tt.ClassId,tt.OrderGuestId
)q)hq GROUP BY hq.ClassId";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
#endregion
#region 简易报表
/// <summary>
/// 获取时间范围内的班级
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="classId"></param>
/// <param name="classNo"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Class_Check_ViewModel> GetAllClassNowMonthStatistics(string startTime, string endTime, int classId, string classNo, int schoolId, int group_Id)
{
string where = "";
if (classId > 0)
{
where += $@" and case when cp.PlanType=2 then t2.ClassId ={classId} else cc.ClassId ={classId} end";
}
if (schoolId >= 0)
{
where += $@" and case when cp.PlanType=2 then cp.School_Id ={schoolId} else c.School_Id ={schoolId} end";
}
if (!string.IsNullOrEmpty(classNo))
{
where += " and c.ClassNo ='" + classNo + "'";
}
string sql = $@"
SELECT q.ClassId,q.ClassNo,q.ClassName,SUM(q.CurrentDeductionHours) AS CurrentDeductionHours FROM(
SELECT case when cp.PlanType=2 then t2.ClassId else cc.ClassId end as ClassId,cc.ClassDate,cc.ClassTimeId,MAX(cc.CurrentDeductionHours) as CurrentDeductionHours,
case when cp.PlanType=2 then c2.ClassName else c.ClassName end as ClassName,case when cp.PlanType=2 then c2.ClassNo else c.ClassNo end as ClassNo
FROM rb_class_check cc
INNER JOIN rb_class_time t on cc.ClassTimeId = t.ClassTimeId
inner join rb_class_plan cp on t.ClassPlanId = cp.ClassPlanId
LEFT JOIN rb_order_guest t2 on cc.OrderGuestId = t2.Id
LEFT JOIN rb_class c on cc.ClassId = c.ClassId
LEFT JOIN rb_class c2 on t2.ClassId = c2.ClassId
WHERE cc.`Status` =0 and cc.Group_Id ={group_Id} and cc.ClassDate >='{startTime}' and cc.ClassDate <='{endTime} 23:59:59' {where} GROUP BY case when cp.PlanType=2 then t2.ClassId else cc.ClassId end,cc.ClassDate,cc.ClassTimeId)q GROUP BY q.ClassId";
return Get<RB_Class_Check_ViewModel>(sql).ToList();
}
#endregion
#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_Check_ViewModel> GetClassCheckPageList(int pageIndex, int pageSize, out long rowsCount, RB_Class_Check_ViewModel query)
{
StringBuilder builderWhere = new StringBuilder();
StringBuilder builder = new StringBuilder();
StringBuilder builderMakeUp = new StringBuilder();
if (query != null)
{
if (query.Group_Id > 0)
{
builderWhere.AppendFormat(" AND a.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
}
if (query.OrderGuestId > 0)
{
builderWhere.AppendFormat(" AND a.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
}
if (query.CheckStatus > -1)
{
builderWhere.AppendFormat(" AND a.{0}={1} ", nameof(RB_Class_Check_ViewModel.CheckStatus), query.CheckStatus);
if (query.CheckStatus == 1 && query.MakeUpStatus == 2)
{
builderMakeUp.AppendFormat(" INNER JOIN rb_student_makeup as stum on stum.ClassCheckId=a.ClassCheckId and stum.MakeUpStatus=2");
}
}
}
builder.AppendFormat(@" SELECT A.*,IFNULL(B.GuestName,'') AS GuestName,IFNULL(C.ClassName,'') AS ClassName,C.ClassNo,IFNULL(D.CourseName,'') AS CourseName,IFNULL(E.RoomName,'') AS RoomName
,IFNULL(F.TeacherName,'') AS TeacherName,CONCAT(ct.StartTime,'~',ct.EndTime) as TimeBucket
FROM RB_Class_Check AS A {0}
left join rb_class_time ct on ct.ClassTimeId = A.ClassTimeId
LEFT JOIN rb_order_guest AS B ON A.OrderGuestId=B.Id
LEFT JOIN rb_class AS C ON A.ClassId=C.ClassId
LEFT JOIN rb_course AS D ON C.CouseId=D.CourseId
LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
LEFT JOIN rb_teacher AS F ON A.TeacherId=F.TId
WHERE 1=1 AND A.Status=0 {1} ", builderMakeUp.ToString(), builderWhere.ToString());
return GetPage<RB_Class_Check_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
}
#endregion
/// <summary>
/// 班课学员签到
/// </summary>
/// <param name="list">学员信息</param>
/// <param name="CheckType">1-班课,2-约课</param>
/// <returns></returns>
public bool StudentCheckRepository(List<RB_Class_Check_ViewModel> list,int CheckType)
{
bool flag = false;
List<RB_Order_Guest_Extend> guestList = new List<RB_Order_Guest_Extend>();
List<RB_Order_ViewModel> orderList = new List<RB_Order_ViewModel>();
List<RB_Class_Time_ViewModel> classTimeList = new List<RB_Class_Time_ViewModel>();
List<Common.Message.PushMessageModel> pushList = new List<Common.Message.PushMessageModel>();
List<RB_Class_Check_ViewModel> oldCheckList = new List<RB_Class_Check_ViewModel>();
List<RB_Class_ViewModel> classList = new List<RB_Class_ViewModel>();
List<RB_Employee_Extend> empList = new List<RB_Employee_Extend>();
if (list != null && list.Count > 0)
{
string orderGuestIds = string.Join(",", list.Select(qitem => qitem.OrderGuestId).Distinct());
if (!string.IsNullOrEmpty(orderGuestIds))
{
guestList= GetOrderGuestListRepository(orderGuestIds);
oldCheckList = GetClassCheckList(new RB_Class_Check_ViewModel() { QOrderGuestIds = orderGuestIds });
}
if (guestList != null && guestList.Count > 0)
{
string orderIds = string.Join(",", guestList.Select(qitem => qitem.OrderId).Distinct());
if (!string.IsNullOrEmpty(orderIds))
{
orderList= GetOrderListRepository(orderIds);
}
}
if (orderList != null && orderList.Count > 0)
{
string enterIDs = string.Join(",", orderList.Select(qitem => qitem.EnterID).Distinct());
if (!string.IsNullOrEmpty(enterIDs))
{
empList= GetEmployeeListRepository(enterIDs);
}
}
string classTimeIds = string.Join(",", list.Select(qitem => qitem.ClassTimeId).Distinct());
if (!string.IsNullOrEmpty(classTimeIds))
{
classTimeList = GetClassTimeListRepository(classTimeIds,"");
}
string classIds = string.Join(",", list.Select(qitem => qitem.ClassId).Distinct());
if (!string.IsNullOrEmpty(classIds))
{
classList= GetClassListRepository(classIds);
}
}
foreach (var item in list)
{
var guestModel = guestList?.FirstOrDefault(qitem => qitem.Id == item.OrderGuestId) ?? new RB_Order_Guest_Extend();
item.CurrentDeductionHours = classTimeList?.FirstOrDefault(qitem => qitem.ClassTimeId == item.ClassTimeId)?.TimeHour ?? 2;
decimal currentDeductionHours = item.CurrentDeductionHours;
//if (item.CheckStatus == 2)
//{
// currentDeductionHours = 0;
//}
if (item.ClassCheckId > 0)
{
Dictionary<string, object> fileds = new Dictionary<string, object>()
{
{ nameof(RB_Class_Check_ViewModel.CheckStatus),item.CheckStatus},
{ nameof(RB_Class_Check_ViewModel.StudyNum),item.StudyNum},
{ nameof(RB_Class_Check_ViewModel.CurrentDeductionHours),currentDeductionHours},
};
List<WhereHelper> wheres = new List<WhereHelper>()
{
new WhereHelper (nameof(RB_Class_Check_ViewModel.ClassCheckId),item.ClassCheckId)
};
flag = base.Update(fileds, wheres);
}
else
{
item.CurrentDeductionHours = currentDeductionHours;
item.MakeUpStatus = 1;
var newId = base.Insert(item);
item.ClassCheckId = newId;
flag = newId > 0;
}
var guestOldCheckList = oldCheckList?.Where(qitem => qitem.OrderGuestId == item.OrderGuestId && qitem.ClassTimeId != item.ClassTimeId)?.ToList() ?? new List<RB_Class_Check_ViewModel>();
decimal guestOldFinishHours = 0;
if (guestOldCheckList != null && guestOldCheckList.Count > 0)
{
guestOldFinishHours = guestOldCheckList.Sum(qitem => qitem.CurrentDeductionHours);
}
if (flag)
{
//更新学员完成课时
var completeHours = guestOldFinishHours + currentDeductionHours;
Dictionary<string, object> orderFileds = new Dictionary<string, object>()
{
{nameof(RB_Order_Guest_ViewModel.CompleteHours),completeHours }
};
var orderModel = orderList?.FirstOrDefault(qitem => qitem.OrderId == guestModel.OrderId);
string LogContent = "";
var shenyuHours = guestModel.TotalHours - completeHours;
var classModel = classList?.FirstOrDefault(qitem => qitem.ClassId == guestModel.ClassId);
var sellerInfo = empList?.FirstOrDefault(qitem => qitem.EmployeeId == orderModel.EnterID);
if (shenyuHours > 0 && shenyuHours <= 12)
{
var shengyuMoney = orderModel.PreferPrice - orderModel.Income + orderModel.Refund - orderModel.PlatformTax - orderModel.DiscountMoney - orderModel.LessPrice;
#region 即将结课提醒 (销售端)
if (sellerInfo != null && !string.IsNullOrEmpty(sellerInfo?.WorkUserId ?? ""))
{
// 学员名称（名称+电话），剩余课时，关联单号，待收金额，班级老师，班级名称
var path = $"/sale/myOrder?OrderId={guestModel.OrderId}";
path = HttpUtility.UrlEncode(path);
string markdownContent = $"`结课预警` 学员即将结课通知\n>**概要信息** \n>学员名称：{guestModel.GuestName}({guestModel.Mobile})\n>剩余课时：<font color='warning'>{shenyuHours}</font>\n>是否续班：<font color='{(orderModel.RenewOrderId > 0 ? "info" : "warning")}'>{(orderModel.RenewOrderId > 0 ? "已续班(" + orderModel.RenewOrderId + ")" : "未续班")}</font>\n>关联单号：{guestModel.OrderId}\n>\n>班级名称：<font>{classModel.ClassName}</font>\n>请点击：[查看详情]({Common.Config.ErpUrl}/autologin?loginId={orderModel.EnterID}&target={path})";
Common.Message.PushMessageModel modelWork = new Common.Message.PushMessageModel()
{
CategoryId = PushMessageCategoryEnum.SalePeople,
Content = markdownContent,
CoverImg = "",
CreateByName = "系统",
JumpUrl = "",
WorkMsgType = "markdown",
SendTime = DateTime.Now,
SendType = 0,
Title = "学员即将结课通知",
Platform = 5,
ReceiveId = sellerInfo?.WorkUserId
};
pushList.Add(modelWork);
}
#endregion
#region 即将结课提醒 (管理端)
var queryNotifyManager = GetWorkUserIdByDictRepository("Push_EndClass");
if (queryNotifyManager != null && queryNotifyManager.Count > 0)
{
queryNotifyManager.ForEach(x =>
{
// 学员名称（名称+电话），剩余课时，关联单号，待收金额，班级老师，班级名称
var path = $"/sale/orderStatistics?OrderId={guestModel.OrderId}";
path = HttpUtility.UrlEncode(path);
string markdownContent = $"`结课预警` 学员即将结课通知\n>**概要信息** \n>学员名称：{guestModel.GuestName}({guestModel.Mobile})\n>剩余课时：<font color='warning'>{shenyuHours}</font>\n>是否续班：<font color='{(orderModel.RenewOrderId > 0 ? "info" : "warning")}'>{(orderModel.RenewOrderId > 0 ? "已续班(" + orderModel.RenewOrderId + ")" : "未续班")}</font>\n>关联单号：{guestModel.OrderId} ({sellerInfo?.EmName ?? ""})\n>\n>班级名称：<font>{classModel.ClassName}</font>\n>请点击：[查看详情]({Common.Config.ErpUrl}/autologin?loginId={x.Id}&target={path})";
Common.Message.PushMessageModel modelWork = new Common.Message.PushMessageModel()
{
CategoryId = PushMessageCategoryEnum.SalePeople,
Content = markdownContent,
CoverImg = "",
CreateByName = "系统",
JumpUrl = "",
WorkMsgType = "markdown",
SendTime = DateTime.Now,
SendType = 0,
Title = "学员即将结课通知",
Platform = 5,
ReceiveId = x.WorkUserId
};
pushList.Add(modelWork);
});
}
#endregion
}
else if (shenyuHours <= 0)
{
var shengyuMoney = orderModel.PreferPrice - orderModel.Income + orderModel.Refund - orderModel.PlatformTax - orderModel.DiscountMoney - orderModel.LessPrice;
#region 即将结课提醒 (销售端)
if (sellerInfo != null && !string.IsNullOrEmpty(sellerInfo?.WorkUserId ?? ""))
{
// 学员名称（名称+电话），剩余课时，关联单号，待收金额，班级老师，班级名称
var path = $"/sale/myOrder?OrderId={guestModel.OrderId}";
path = HttpUtility.UrlEncode(path);
string markdownContent = $"`结课通知` 学员已结课通知\n>**概要信息** \n>学员名称：{guestModel.GuestName}({guestModel.Mobile})\n>剩余课时：<font color='warning'>{shenyuHours}</font>\n>是否续班：<font color='{(orderModel.RenewOrderId > 0 ? "info" : "warning")}'>{(orderModel.RenewOrderId > 0 ? "已续班(" + orderModel.RenewOrderId + ")" : "未续班")}</font>\n>关联单号：{guestModel.OrderId}\n>\n>班级名称：<font>{classModel.ClassName}</font>\n>请点击：[查看详情]({Common.Config.ErpUrl}/autologin?loginId={orderModel.EnterID}&target={path})";
Common.Message.PushMessageModel modelWork = new Common.Message.PushMessageModel()
{
CategoryId = PushMessageCategoryEnum.SalePeople,
Content = markdownContent,
CoverImg = "",
CreateByName = "系统",
JumpUrl = "",
WorkMsgType = "markdown",
SendTime = DateTime.Now,
SendType = 0,
Title = "学员已结课通知",
Platform = 5,
ReceiveId = sellerInfo?.WorkUserId ?? ""
};
pushList.Add(modelWork);
}
#endregion
#region 即将结课提醒 (销售端)
var queryNotifyManager = GetWorkUserIdByDictRepository("Push_EndClass");
if (queryNotifyManager != null && queryNotifyManager.Count > 0)
{
queryNotifyManager.ForEach(x =>
{
// 学员名称（名称+电话），剩余课时，关联单号，待收金额，班级老师，班级名称
var path = $"/sale/orderStatistics?OrderId={guestModel.OrderId}";
path = HttpUtility.UrlEncode(path);
string markdownContent = $"`结课通知` 学员已结课通知\n>**概要信息** \n>学员名称：{guestModel.GuestName}({guestModel.Mobile})\n>剩余课时：<font color='warning'>{shenyuHours}</font>\n>是否续班：<font color='{(orderModel.RenewOrderId > 0 ? "info" : "warning")}'>{(orderModel.RenewOrderId > 0 ? "已续班(" + orderModel.RenewOrderId + ")" : "未续班")}</font>\n>关联单号：{guestModel.OrderId} ({sellerInfo?.EmName ?? ""})\n>\n>班级名称：<font>{classModel.ClassName}</font>\n>请点击：[查看详情]({Common.Config.ErpUrl}/autologin?loginId={x.Id}&target={path})";
Common.Message.PushMessageModel modelWork = new Common.Message.PushMessageModel()
{
CategoryId = PushMessageCategoryEnum.SalePeople,
Content = markdownContent,
CoverImg = "",
CreateByName = "系统",
JumpUrl = "",
WorkMsgType = "markdown",
SendTime = DateTime.Now,
SendType = 0,
Title = "学员已结课通知",
Platform = 5,
ReceiveId = x.WorkUserId
};
pushList.Add(modelWork);
});
}
#endregion
}
if (item.CheckStatus == 1)
{
#region 通知缺勤信息
var queryNotifyManager = GetWorkUserIdByDictRepository("Push_Absence");
if (queryNotifyManager != null && queryNotifyManager.Count > 0)
{
queryNotifyManager.ForEach(x =>
{
// 学员名称（名称+电话），剩余课时，关联单号，待收金额，班级老师，班级名称
var path = $"/stuMan/studentManage?OrderId={guestModel.OrderId}";
path = HttpUtility.UrlEncode(path);
string markdownContent = $"`缺勤通知` 学员缺勤信息告知\n>**概要信息** \n>学员名称：{guestModel.GuestName}({guestModel.Mobile})\n>缺勤时间：<font color='warning'>{Common.ConvertHelper.FormatDate(item.ClassDate)}</font>\n>关联单号：{guestModel.OrderId}\n>班级名称：<font>{classModel?.ClassName??""}</font>\n>请点击：[查看详情]({Common.Config.ErpUrl}/autologin?loginId={x.Id}&target={path})";
Common.Message.PushMessageModel modelWork = new Common.Message.PushMessageModel()
{
CategoryId = PushMessageCategoryEnum.SalePeople,
Content = markdownContent,
CoverImg = "",
CreateByName = "系统",
JumpUrl = "",
WorkMsgType = "markdown",
SendTime = DateTime.Now,
SendType = 0,
Title = "学员缺勤通知",
Platform = 5,
ReceiveId = x.WorkUserId
};
pushList.Add(modelWork);
});
}
#endregion
}
DynamicParameters guestParamters = new DynamicParameters();
string guestSql = "UPDATE RB_Order_Guest SET ";
int guestIndex = 0;
foreach (var gItem in orderFileds)
{
if (guestIndex > 0)
{
guestSql += ",";
}
guestSql += string.Format("{0}=@{0} ", gItem.Key);
guestParamters.Add(string.Format("@{0}", gItem.Key), gItem.Value);
guestIndex++;
}
guestSql += string.Format(" WHERE 1=1 AND Id=@Id ");
guestParamters.Add("@Id", item.OrderGuestId);
flag = base.Execute(guestSql, guestParamters) > 0;
if (flag && !string.IsNullOrEmpty(LogContent))
{
AddUserChangeLogRepository(new Model.Entity.Log.RB_User_ChangeLog()
{
Id = 0,
Type = 3,
CreateBy = item.CreateBy,
CreateTime = DateTime.Now,
Group_Id = item.Group_Id,
LogContent = LogContent,
School_Id = item.School_Id,
SourceId = guestModel.Id
});
}
#region B2B同业推送
if (orderModel != null && orderModel.CustomerId > 0)
{
//最后5个课时做处理
if (completeHours > guestModel.ValidClassHours)
{
//已停课
AddStudentNoticeRepository(new Model.Entity.Log.RB_B2B_StudentNotice()
{
Id = 0,
Type = 3,
StudentId = guestModel.Id,
CustomerId = orderModel.CustomerId,
Title = "停课通知",
Content = "学员" + guestModel.GuestName + " 有效课时消耗完毕，已自动停课，关联单号：" + guestModel.OrderId + "!",
IsLook = 2,
Status = 0,
CreateBy = item.CreateBy,
CreateTime = DateTime.Now,
Group_Id = item.Group_Id,
UpdateTime = DateTime.Now
});
}
else if (guestModel.TotalHours > guestModel.ValidClassHours && shenyuHours <= 5)
{
//即将欠费
AddStudentNoticeRepository(new Model.Entity.Log.RB_B2B_StudentNotice()
{
Id = 0,
Type = 1,
StudentId = guestModel.Id,
CustomerId = orderModel.CustomerId,
Title = "欠费预警",
Content = "学员" + guestModel.GuestName + " 即将欠费，剩余课时" + shenyuHours + "，关联单号：" + guestModel.OrderId + "!",
IsLook = 2,
Status = 0,
CreateBy = item.CreateBy,
CreateTime = DateTime.Now,
Group_Id = item.Group_Id,
UpdateTime = DateTime.Now
});
}
else if (guestModel.TotalHours == guestModel.ValidClassHours && shenyuHours <= 5 && shenyuHours >= 0)
{
//即将结课
AddStudentNoticeRepository(new Model.Entity.Log.RB_B2B_StudentNotice()
{
Id = 0,
Type = 2,
StudentId = guestModel.Id,
CustomerId = orderModel.CustomerId,
Title = "结课预警",
Content = "学员" + guestModel.GuestName + " 即将结课，剩余课时" + shenyuHours + "，关联单号：" + guestModel.OrderId + "!",
IsLook = 2,
Status = 0,
CreateBy = item.CreateBy,
CreateTime = DateTime.Now,
Group_Id = item.Group_Id,
UpdateTime = DateTime.Now
});
}
else if (guestModel.TotalHours == guestModel.ValidClassHours && shenyuHours == 0)
{
//已经结课
AddStudentNoticeRepository(new Model.Entity.Log.RB_B2B_StudentNotice()
{
Id = 0,
Type = 4,
StudentId = guestModel.Id,
CustomerId = orderModel.CustomerId,
Title = "结课通知",
Content = "学员" + guestModel.GuestName + " 已结课，关联单号：" + guestModel.OrderId + "!",
IsLook = 2,
Status = 0,
CreateBy = item.CreateBy,
CreateTime = DateTime.Now,
Group_Id = item.Group_Id,
UpdateTime = DateTime.Now
});
}
}
#endregion
}
}
if (pushList != null && pushList.Count > 0)
{
new Common.Message.MessageHelper().SendMessage(pushList);
}
return flag;
}
/// <summary>
/// 约课学员签到
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public bool AppointStuCheckRepository(List<RB_Scroll_Appointment_ViewModel> list)
{
bool flag = false;
if (list.Any())
{
//查询所有的上课计划
string planIds = string.Join(",", list.Select(x => x.ClassPlanId).Distinct());
//再查询上课时间
var timeList = GetClassTimeListRepository("", planIds);
string Date = DateTime.Now.ToString("yyyy-MM-dd");
string orderGuestIds = string.Join(",", list.Select(x => x.GuestId).Distinct());
var guestList= GetOrderGuestListRepository(orderGuestIds);
var oldCheckList = GetClassCheckList(new RB_Class_Check_ViewModel() { QOrderGuestIds = orderGuestIds });
foreach (var item in list)
{
//更新约课为已上课
Dictionary<string, object> keyValues = new Dictionary<string, object>()
{
{ nameof(RB_Scroll_Appointment_ViewModel.State), Common.Enum.Course.CourseAppointStateEnum.Coursed}
};
List<WhereHelper> wheres = new List<WhereHelper>()
{
new WhereHelper()
{
FiledName = nameof(RB_Scroll_Appointment_ViewModel.Id),
FiledValue = item.Id,
OperatorEnum =OperatorEnum.Equal
}
};
string appointSql = string.Format(" UPDATE RB_Scroll_Appointment SET State={0} WHERE Id={1} ", (int)Common.Enum.Course.CourseAppointStateEnum.Coursed, item.Id);
flag = base.Execute(appointSql) > 0;
if (flag)
{
var clist = JsonHelper.DeserializeObject<List<CourseTimeModel>>(item.CourseTime);
//更新 order_guest 表 上课章节 请假也暂时先算完成课时(请假应该是有补课的)
var gmodel = guestList?.FirstOrDefault(qitem => qitem.Id == item.GuestId);
var tempClassTimeList = timeList?.Where(qitem => qitem.ClassPlanId == item.ClassPlanId)?.ToList() ?? new List<RB_Class_Time_ViewModel>();
var guestOldCheckList = oldCheckList?.Where(qitem => qitem.OrderGuestId == item.GuestId && !tempClassTimeList.Any(sItem => sItem.ClassTimeId == qitem.ClassTimeId))?.ToList() ?? new List<RB_Class_Check_ViewModel>();
decimal guestOldFinishHours = 0;
if (guestOldCheckList != null && guestOldCheckList.Count > 0)
{
guestOldFinishHours = guestOldCheckList.Sum(qitem => qitem.CurrentDeductionHours);
}
if (gmodel != null)
{
//ld 2022-03-08 修改为学员上课最大课时
Dictionary<string, object> keyValues1 = new Dictionary<string, object>();
var completeHours = guestOldFinishHours + clist.Sum(x => x.ClassHours);
keyValues1.Add(nameof(Model.ViewModel.Sell.RB_Order_Guest_ViewModel.CourseChapterNo), item.ChapterNo);
keyValues1.Add(nameof(Model.ViewModel.Sell.RB_Order_Guest_ViewModel.CompleteHours), completeHours);
if (gmodel.TotalHours == completeHours)
{
keyValues1.Add(nameof(Model.ViewModel.Sell.RB_Order_Guest_ViewModel.GuestState), Common.Enum.Course.GuestStateEnum.Graduate);
}
if (keyValues1 != null && keyValues1.Count > 0)
{
DynamicParameters guestParamters = new DynamicParameters();
string guestSql = "UPDATE RB_Order_Guest SET ";
int guestIndex = 0;
foreach (var gItem in keyValues1)
{
if (guestIndex > 0)
{
guestSql += ",";
}
guestSql += string.Format("{0}=@{0} ", gItem.Key);
guestParamters.Add(string.Format("@{0}", gItem.Key), gItem.Value);
guestIndex++;
}
guestSql += string.Format(" WHERE 1=1 AND Id=@Id ");
guestParamters.Add("@Id", gmodel.Id);
flag = base.Execute(guestSql, guestParamters) > 0;
}
}
if (item.AppointType != 3)
{
//写入签到数据表 （非重复上课的 才写入签到数据）
var tlist = timeList.Where(x => x.ClassPlanId == item.ClassPlanId).ToList();
foreach (var qitem in tlist)
{
var ccModel = clist.Where(x => x.StartTime == qitem.StartTime).FirstOrDefault();
if (ccModel != null && !oldCheckList.Where(x => x.OrderGuestId == item.GuestId && x.ClassTimeId == qitem.ClassTimeId).Any())
{
//没有数据的插入 （有数据情况 =》 请假数据）
base.Insert(new RB_Class_Check()
{
ClassCheckId = 0,
ClassId = 0,
ClassDate = item.Date,
Status = 0,
ClassRoomId = item.RoomId,
Group_Id = item.Group_Id,
School_Id = qitem.School_Id,
TeacherId = item.TeacherId,
OrderGuestId = item.GuestId,
CheckStatus = 0,
CreateBy = 0,
CreateTime = DateTime.Now,
StudyNum = ccModel.Minutes,
MakeUpStatus = 1,
CurrentDeductionHours = ccModel.ClassHours,
IsAbsentHours = 0,
ClassTimeId = qitem.ClassTimeId
});
}
}
}
}
}
}
return flag;
}
/// <summary>
/// 新增日志
/// </summary>
/// <param name="model"></param>
private void AddUserChangeLogRepository(Model.Entity.Log.RB_User_ChangeLog model)
{
string sql = " INSERT INTO RB_User_ChangeLog(Type,CreateBy,CreateTime,Group_Id,LogContent,School_Id,SourceId) ";
sql += " VALUES(@Type,@CreateBy,@CreateTime,@Group_Id,@LogContent,@School_Id,@SourceId) ";
base.Execute(sql, model);
}
/// <summary>
/// 新增学生消息通知
/// </summary>
/// <param name="model"></param>
private void AddStudentNoticeRepository(Model.Entity.Log.RB_B2B_StudentNotice model)
{
string sql = " INSERT INTO RB_B2B_StudentNotice(Type,StudentId,CustomerId,Title,Content,IsLook,Status,CreateBy,CreateTime,Group_Id,UpdateTime) ";
sql += " VALUES(@Type,@StudentId,@CustomerId,@Title,@Content,@IsLook,@Status,@CreateBy,@CreateTime,@Group_Id,@UpdateTime) ";
base.Execute(sql, model);
}
/// <summary>
/// 根据订单编号获取订单列表
/// </summary>
/// <param name="OrderIds"></param>
/// <returns></returns>
public List<RB_Order_ViewModel> GetOrderListRepository(string OrderIds)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*
FROM RB_Order AS A
WHERE 1=1 AND A.OrderId IN({0})
",OrderIds);
return base.Get<RB_Order_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取学员列表
/// </summary>
/// <param name="GuestIds"></param>
/// <returns></returns>
public List<RB_Order_Guest_Extend> GetOrderGuestListRepository(string GuestIds)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*
FROM rb_order_guest AS A
WHERE 1=1 AND A.Id IN({0})
",GuestIds);
return base.Get<RB_Order_Guest_Extend>(builder.ToString()).ToList();
}
/// <summary>
/// 获取上课时段
/// </summary>
/// <param name="ClassTimeIds"></param>
/// <returns></returns>
public List<RB_Class_Time_ViewModel> GetClassTimeListRepository(string ClassTimeIds,string PlanIds)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*
FROM RB_Class_Time AS A
WHERE 1=1
");
if (!string.IsNullOrEmpty(ClassTimeIds))
{
builder.AppendFormat(" AND A.ClassTimeId IN({0}) ", ClassTimeIds);
}
if (!string.IsNullOrEmpty(PlanIds))
{
builder.AppendFormat(" AND A.ClassPlanId IN({0}) ", PlanIds);
}
return Get<RB_Class_Time_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取班级列表
/// </summary>
/// <param name="ClassIds"></param>
/// <returns></returns>
public List<RB_Class_ViewModel> GetClassListRepository(string ClassIds)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*
FROM RB_Class AS A
WHERE 1=1 AND A.ClassId IN({0})
",ClassIds);
return Get<RB_Class_ViewModel>(builder.ToString()).ToList();
}
/// <summary>
/// 获取指定人员的企业微信ID
/// </summary>
/// <param name="id">教师编号</param>
/// <returns></returns>
public List<RB_Employee_Extend> GetEmployeeListRepository(string Ids)
{
string sql = $"SELECT EmployeeId,EmName,WorkUserId FROM RB_Employee WHERE EmployeeId IN({Ids})";
return base.Get<RB_Employee_Extend>(sql).ToList();
}
/// <summary>
/// 获取指定DictKey的推送人员信息
/// </summary>
/// <param name="departmentIds"></param>
/// <returns></returns>
public List<RB_Account_ViewModel> GetWorkUserIdByDictRepository(string dictKey)
{
string sql = $"SELECT Id,WorkUserId FROM rb_account a where EXISTS(select DictKey from rb_dictvalue where DictKey='{dictKey}' and FIND_IN_SET(a.Id,Content)>0) and WorkUserId is not null";
return Get<RB_Account_ViewModel>(sql).ToList();
}
}
}