using Edu.Model.Entity.Contract;
using Edu.Model.ViewModel.Contract;
using Edu.Repository.User;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB.Dapper;
namespace Edu.Repository.Contract
{
/// <summary>
/// 教育合同仓储层
/// </summary>
public class RB_Education_ContractRepository : BaseRepository<RB_Education_Contract>
{
/// <summary>
/// 校区仓储层对象
/// </summary>
private readonly RB_SchoolRepository schoolRepository = new RB_SchoolRepository();
/// <summary>
/// 生成合同编号
/// </summary>
/// <param name="GroupId"></param>
/// <returns></returns>
public string CreateContractNumRepository(int GroupId, int schoolId)
{
var schoolModel = schoolRepository.GetEntity(schoolId);
string newContractNum = Common.Config.ContractDefaultTitle;
if (schoolModel != null && schoolModel.SId > 0)
{
newContractNum = schoolModel?.ContractTitle ?? Common.Config.ContractDefaultTitle;
}
var totalCount = GetContractCount(new RB_Education_Contract_ViewModel() { Group_Id = GroupId });
string num = (totalCount + 1 + 18).ToString("D4");
return newContractNum + num;
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="demodel"></param>
/// <param name="orderIds"></param>
/// <returns></returns>
public List<RB_Education_Contract_ViewModel> GetEducationContractPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Education_Contract_ViewModel demodel)
{
DynamicParameters parameters = new DynamicParameters();
string where = $@" 1=1";
if (demodel.Group_Id > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Group_Id)} ={demodel.Group_Id}";
}
if (demodel.School_Id > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.School_Id)} ={demodel.School_Id}";
}
if (demodel.OrderId > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.OrderId)} ={demodel.OrderId}";
}
if (demodel.CType > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.CType)} ={(int)demodel.CType}";
}
if (!string.IsNullOrEmpty(demodel.ContractNo))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.ContractNo)} LIKE @ContractNo ";
parameters.Add("ContractNo", "%" + demodel.ContractNo.Trim() + "%");
}
if (!string.IsNullOrEmpty(demodel.StudentName))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.StudentName)} LIKE @StudentName ";
parameters.Add("StudentName", "%" + demodel.StudentName.Trim() + "%");
}
if (demodel.IsAduit == 1)
{
if (demodel.Status >= 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Status)} ={demodel.Status}";
}
else
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Status)} NOT IN(0,4) ";
}
}
if (demodel.IsQueryAll == 1)
{
if (demodel.QCreateBy > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.CreateBy)} ={demodel.QCreateBy}";
}
}
else
{
if (demodel.CreateBy > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.CreateBy)} ={demodel.CreateBy}";
}
}
if (demodel.Status >= 0)
{
if (demodel.Status == 5)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Status)} in(2,5)";
}
else
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Status)} ={demodel.Status}";
}
}
string sql = $@" SELECT * FROM RB_Education_Contract WHERE {where} ORDER BY Id DESC ";
return GetPage<RB_Education_Contract_ViewModel>(pageIndex, pageSize, out rowsCount, sql, parameters).ToList();
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="demodel"></param>
/// <param name="orderIds"></param>
/// <returns></returns>
public List<RB_Education_Contract_ViewModel> GetList(RB_Education_Contract_ViewModel demodel)
{
DynamicParameters parameters = new DynamicParameters();
string where = $@" 1=1 AND IsSystemUse=1 ";
if (demodel.Group_Id > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Group_Id)} ={demodel.Group_Id}";
}
if (demodel.School_Id > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.School_Id)} ={demodel.School_Id}";
}
if (demodel.OrderId > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.OrderId)} ={demodel.OrderId}";
}
if (!string.IsNullOrEmpty(demodel.OrderIds))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.OrderId)} IN({demodel.OrderIds})";
}
if (demodel.GuestId > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.GuestId)} IN({demodel.GuestId})";
}
if (!string.IsNullOrEmpty(demodel.GuestIds))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.GuestId)} IN({demodel.GuestIds})";
}
if (demodel.CType > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.CType)} ={(int)demodel.CType}";
}
if (demodel.Status >= 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Status)} ={demodel.Status}";
}
if (!string.IsNullOrEmpty(demodel.ContractNo))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.ContractNo)} LIKE @ContractNo ";
parameters.Add("ContractNo", "%" + demodel.ContractNo.Trim() + "%");
}
if (!string.IsNullOrEmpty(demodel.StudentName))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.StudentName)} LIKE @StudentName ";
parameters.Add("StudentName", "%" + demodel.StudentName.Trim() + "%");
}
string sql = $@" SELECT * FROM RB_Education_Contract WHERE {where} ORDER BY Id DESC";
return Get<RB_Education_Contract_ViewModel>(sql, parameters).ToList();
}
/// <summary>
/// 根据查询条件获取合同的总条数
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public int GetContractCount(RB_Education_Contract_ViewModel where)
{
DynamicParameters parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"SELECT COUNT(1) FROM RB_Education_Contract WHERE 1=1 ");
if (where != null)
{
if (where.Group_Id > 0)
{
builder.AppendFormat($" AND {nameof(RB_Education_Contract_ViewModel.Group_Id)} ={where.Group_Id}");
}
if (where.ContractNo != null && !string.IsNullOrEmpty(where.ContractNo.Trim()))
{
builder.AppendFormat($@" AND {nameof(RB_Education_Contract_ViewModel.ContractNo)} LIKE @ContractNo ");
parameters.Add("ContractNo", "%" + where.ContractNo.Trim() + "%");
}
}
var obj = base.ExecuteScalar(builder.ToString(), parameters);
return obj == null ? 0 : Convert.ToInt32(obj);
}
/// <summary>
/// 获取订单完成情况一览 分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="demodel"></param>
/// <param name="datebaseStr"></param>
/// <returns></returns>
public List<RB_Education_Contract_ViewModel> GetContractPageListForFinance(int pageIndex, int pageSize, out long rowsCount, RB_Education_Contract_ViewModel demodel, string datebaseStr)
{
DynamicParameters parameters = new DynamicParameters();
string where = $@" 1=1 and o.OrderState <> 3";//排除取消订单
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.Status)} <>4 ";//只查询已审核通过的
if (demodel.Group_Id > 0)
{
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.Group_Id)} ={demodel.Group_Id}";
}
if (demodel.School_Id > 0)
{
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.School_Id)} ={demodel.School_Id}";
}
if (demodel.OrderId > 0)
{
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.OrderId)} ={demodel.OrderId}";
}
if (demodel.CType > 0)
{
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.CType)} ={(int)demodel.CType}";
}
if (!string.IsNullOrEmpty(demodel.ContractNo))
{
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.ContractNo)} LIKE @ContractNo ";
parameters.Add("ContractNo", "%" + demodel.ContractNo.Trim() + "%");
}
if (!string.IsNullOrEmpty(demodel.StudentName))
{
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.StudentName)} LIKE @StudentName ";
parameters.Add("StudentName", "%" + demodel.StudentName.Trim() + "%");
}
if (!string.IsNullOrEmpty(demodel.CourseName))
{
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.CourseName)} LIKE @CourseName ";
parameters.Add("CourseName", "%" + demodel.CourseName.Trim() + "%");
}
if (!string.IsNullOrEmpty(demodel.ClassName))
{
where += $@" AND c1.{nameof(RB_Education_Contract_ViewModel.ClassName)} LIKE @ClassName ";
parameters.Add("ClassName", "%" + demodel.ClassName.Trim() + "%");
}
if (demodel.CreateBy > 0)
{
where += $@" AND c.{nameof(RB_Education_Contract_ViewModel.CreateBy)} ={demodel.CreateBy}";
}
if (demodel.FinanceId > 0)
{
where += $@" AND f.FrID ={demodel.FinanceId}";
}
if (demodel.TradeWay > 0)
{
where += $@" AND t.Type ={demodel.TradeWay}";
}
if (demodel.AccountId > 0)
{
where += $@" AND t.AccountId ={demodel.AccountId}";
}
if (demodel.SaleId > 0)
{
where += $@" AND o.EnterID ={demodel.SaleId}";
}
if (!string.IsNullOrEmpty(demodel.StartTime))
{
where += $@" AND o.CreateTime >='{demodel.StartTime}'";
}
if (!string.IsNullOrEmpty(demodel.EndTime))
{
where += $@" AND o.CreateTime <='{demodel.EndTime} 23:59:59'";
}
string sql = $@"
SELECT c.*,c1.ClassName,c1.ClassNo,ca.AdjustPrice,o.JoinType,o.TargetJoinType,o.SourceOrderId,o.TargetOrderId
FROM RB_Education_Contract c
left join RB_Education_ContractAdjust ca on c.Id = ca.ContractId
left join rb_order o on c.OrderId = o.OrderId
left join rb_class c1 on o.ClassId = c1.ClassId
left join {datebaseStr}.rb_finance f on c.GuestId = f.GuestId and c.OrderId = f.OrderID and f.`Status` !=4
left join {datebaseStr}.rb_tradeway t on f.FrID = t.FinanceId
WHERE {where} GROUP BY c.Id ORDER BY c.Id DESC ";
return GetPage<RB_Education_Contract_ViewModel>(pageIndex, pageSize, out rowsCount, sql, parameters).ToList();
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowsCount"></param>
/// <param name="demodel"></param>
/// <param name="orderIds"></param>
/// <returns></returns>
public List<RB_Education_Contract_ViewModel> GetEducationContractPageList_V2(int pageIndex, int pageSize, out long rowsCount, RB_Education_Contract_ViewModel demodel)
{
DynamicParameters parameters = new DynamicParameters();
string where = $@" 1=1";
if (demodel.Group_Id > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Group_Id)} ={demodel.Group_Id}";
}
if (demodel.School_Id > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.School_Id)} ={demodel.School_Id}";
}
if (demodel.OrderId > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.OrderId)} ={demodel.OrderId}";
}
if (demodel.CType > 0)
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.CType)} ={(int)demodel.CType}";
}
if (!string.IsNullOrEmpty(demodel.ContractNo))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.ContractNo)} LIKE @ContractNo ";
parameters.Add("ContractNo", "%" + demodel.ContractNo.Trim() + "%");
}
if (!string.IsNullOrEmpty(demodel.StudentName))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.StudentName)} LIKE @StudentName ";
parameters.Add("StudentName", "%" + demodel.StudentName.Trim() + "%");
}
if (!string.IsNullOrWhiteSpace(demodel.SelectEexamineStatus))
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Status)} in( {demodel.SelectEexamineStatus})";
}
else
{
where += $@" AND {nameof(RB_Education_Contract_ViewModel.Status)} NOT IN(0,4) ";
}
string sql = $@" SELECT * FROM RB_Education_Contract WHERE {where} ORDER BY Id DESC ";
return GetPage<RB_Education_Contract_ViewModel>(pageIndex, pageSize, out rowsCount, sql, parameters).ToList();
}
/// <summary>
/// 根据学生id获取学生的有效合同信息
/// </summary>
/// <param name="teacherIds"></param>
/// <returns></returns>
public List<RB_Education_Contract_ViewModel> GetStudentContractInfo(int Student_Id, int Group_Id)
{
var parameters = new DynamicParameters();
StringBuilder builder = new StringBuilder();
builder.AppendFormat($@"SELECT sog.Student_Id,ec.* from rb_education_contract as ec LEFT JOIN rb_order as o on ec.OrderId=o.OrderId
LEFT JOIN rb_order_guest as og on og.OrderId=o.OrderId
LEFT JOIN rb_student_orderguest as sog on sog.GuestId=og.Id
where ec.Status =2 and og.`Status`=0 and o.OrderState =1 and sog.`Status`=0 and sog.Student_Id={Student_Id} and o.Group_Id={Group_Id}");
return Get<RB_Education_Contract_ViewModel>(builder.ToString(), parameters).ToList();
}
/// <summary>
/// 查询合同和订单金额、优惠不相同的数据
/// </summary>
/// <returns></returns>
public List<RB_Education_Contract_ViewModel> GetEducationAnomalyContractListRepository()
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"
SELECT A.Id,A.CType,A.OrderId,A.GuestId,A.ContractNo,A.StudentName,A.School_Id,A.SchoolName,A.FirstCourseFee,A.FirstDiscountMoney,A.FirstMoney,A.Money,B.PreferPrice,B.Income,B.PlatformTax,B.DiscountMoney,B.Refund
FROM rb_education_contract AS A INNER JOIN RB_order AS B ON A.OrderId=B.OrderId
WHERE B.OrderState=1 AND A.School_Id =0 and (A.Money<>(B.PreferPrice-B.DiscountMoney))
");
return Get<RB_Education_Contract_ViewModel>(builder.ToString()).ToList();
}
}
}