using Edu.Common.Enum;
using Edu.Model.Entity.Customer;
using Edu.Model.ViewModel.Customer;
using Edu.Model.ViewModel.User;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB.Dapper;
namespace Edu.Repository.Customer
{
/// <summary>
/// 学员约访表仓储层
/// </summary>
public class RB_Student_AppointmentRepository : BaseRepository<RB_Student_Appointment>
{
/// <summary>
/// 获取学员约访分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Student_Appointment_Extend> GetStudentAppointmentPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Student_Appointment_Extend query,bool IsQueryClue=false)
{
var parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.*,B.StuName
FROM RB_Student_Appointment AS A INNER JOIN rb_student AS B ON A.StuId=B.StuId
");
if (IsQueryClue)
{
builder.Append(" LEFT JOIN (SELECT Student_Id,Count(1) AS OrderCount FROM rb_student_orderguest GROUP BY Student_Id) AS C ON A.StuId=C.Student_Id ");
}
builder.Append(" WHERE 1=1 ");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.Status), (int)DateStateEnum.Normal);
if (IsQueryClue)
{
builder.Append(" AND IFNULL(C.OrderCount,0)=0 ");
builder.AppendFormat(" AND IFNULL(A.{0},'')='' ", nameof(RB_Student_Appointment_Extend.Feedback));
}
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.Group_Id), query.Group_Id);
}
if (query.StuId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.StuId), query.StuId);
}
if (query.Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.Id), query.Id);
}
if (query.CreateBy > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.CreateBy), query.CreateBy);
}
//指定日期的约访统计
if (!string.IsNullOrEmpty(query.QueryDate))
{
builder.AppendFormat(" AND ( DATE_FORMAT(A.{0},'%y/%m/%d')= DATE_FORMAT('{2}','%y/%m/%d') OR DATE_FORMAT(A.{1},'%y/%m/%d')= DATE_FORMAT('{2}','%y/%m/%d')) ", nameof(RB_Student_Appointment_Extend.CreateTime), nameof(RB_Student_Appointment_Extend.AppointmentTime), Common.ConvertHelper.FormatDate(query.QueryDate));
}
if (!string.IsNullOrEmpty(query.KeyWords))
{
builder.AppendFormat(" AND (B.{0} LIKE @KeyWords OR B.{1} LIKE @KeyWords OR B.{2} LIKE @KeyWords)", nameof(RB_Student_ViewModel.StuName), nameof(RB_Student_ViewModel.WeChatNo), nameof(RB_Student_ViewModel.StuRealMobile));
parameters.Add("KeyWords", "%" + query.KeyWords.Trim() + "%");
}
}
builder.AppendFormat(" ORDER BY A.{0} DESC ", nameof(RB_Student_Appointment_Extend.Id));
return GetPage<RB_Student_Appointment_Extend>(pageIndex, pageSize, out rowsCount, builder.ToString(),parameters).ToList();
}
/// <summary>
/// 获取约访统计列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<RB_Student_Appointment_Extend> GetStudentVisitStaticRepository(RB_Student_Appointment_Extend query)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.CreateBy,DATE_FORMAT(A.CreateTime,'%Y-%m-%d') AS CreateTime,Count(1) AS AppointmentCount
FROM RB_Student_Appointment AS A INNER JOIN rb_student AS B ON A.StuId=B.StuId
WHERE 1=1
");
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.Status), (int)DateStateEnum.Normal);
if (query != null)
{
if (query.Group_Id > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.Group_Id), query.Group_Id);
}
if (query.StuId > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.StuId), query.StuId);
}
if (query.CreateBy > 0)
{
builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_Appointment_Extend.CreateBy), query.CreateBy);
}
if (!string.IsNullOrEmpty(query.QueryDate))
{
//指定日期的约访统计
builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m/%d')= DATE_FORMAT('{1}','%y/%m/%d') ", nameof(RB_Student_Appointment_Extend.CreateTime), Common.ConvertHelper.FormatDate(query.QueryDate));
}
if (!string.IsNullOrEmpty(query.StartDate))
{
builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Student_Appointment_Extend.CreateTime), Common.ConvertHelper.FormatDate(query.StartDate));
}
if (!string.IsNullOrEmpty(query.EndDate))
{
builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Student_Appointment_Extend.CreateTime), Common.ConvertHelper.FormatDate(query.EndDate));
}
}
builder.AppendFormat(" GROUP BY A.CreateBy,DATE_FORMAT(A.CreateTime,'%Y/%m/%d') ");
return Get<RB_Student_Appointment_Extend>(builder.ToString()).ToList();
}
/// <summary>
/// 今日需联系学员（之前预约的跟进学员或本周期内需要跟进的学员）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="date">日期</param>
/// <param name="CreateBy">创建人</param>
/// <param name="UserType">1-课程顾问，2-老师</param>
/// <returns></returns>
public List<RB_Student_ViewModel> GetTodayConnectPageRepository(int pageIndex,int pageSize,out long rowsCount,string date,int CreateBy,int UserType=0,string KeyWords="")
{
var parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
string where1="", tempWhere="";
if (CreateBy > 0)
{
where1 += string.Format(@"
AND ( A.CreateBy IN({0})
OR A.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId IN({0}) AND `Status`=0)
)
", CreateBy);
tempWhere += string.Format(" AND CreateBy={0} ", CreateBy);
}
//老师每周跟进一次
if (UserType == 2)
{
tempWhere += string.Format(@" AND WEEK(CreateTime,1)=WEEK(NOW(),1) ");
}
else
{
tempWhere += string.Format(@" AND DATE_FORMAT(CreateTime, '%Y%m' )=DATE_FORMAT(NOW() , '%Y%m' ) ");
}
builder.AppendFormat(@"
SELECT *
FROM
(
SELECT A.*
FROM rb_student AS A
LEFT JOIN (SELECT StuId,COUNT(1) AS FollowCount FROM rb_student_follow WHERE 1=1 {0} GROUP BY StuId ) AS B ON A.StuId=B.StuId
WHERE 1=1 AND IFNULL(B.FollowCount,0)=0 {1}
) AS A LEFT JOIN (
SELECT A.Student_Id,Count(1) AS OrderCount
FROM rb_student_orderguest AS A INNER JOIN RB_Order AS B ON A.OrderId=B.OrderId
WHERE B.OrderState IN(1)
GROUP BY A.Student_Id
) AS C ON A.StuId=C.Student_Id
WHERE A.School_Id NOT IN(1,6,9) AND A.`Status`=0 AND A.StuStage<>7 AND A.AdvisorStatus<>7 AND IFNULL(C.OrderCount,0)>0
", tempWhere, where1);
if (!string.IsNullOrEmpty(KeyWords))
{
builder.AppendFormat(" AND (A.{0} LIKE @KeyWords OR A.{1} LIKE @KeyWords OR A.{2} LIKE @KeyWords)", nameof(RB_Student_ViewModel.StuName), nameof(RB_Student_ViewModel.WeChatNo), nameof(RB_Student_ViewModel.StuRealMobile));
parameters.Add("KeyWords", "%" + KeyWords.Trim() + "%");
}
return GetPage<RB_Student_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
}
}
}