/// <summary>
/// 分页获取课程列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Course> GetButtonList(CurriculumSearchModel search)
{
///UPDATE TOP(1) [PRX].[dbo].[Course] SET [ID]='3', [CourseName]=N'绘画', [CoursePrice]='11.00', [StateID]='2', [Hours]='11', [Introduce]=N'没有介绍' WHERE ([ID]='3');
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Course "; //表或者视图
orderby = "ID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.CourseName))//按钮中文名称
{
sb.AppendFormat(" and CourseName like '%{0}%' ", search.CourseName);
}
if (!string.IsNullOrWhiteSpace(search.ComCode))//分校
{
sb.AppendFormat(" and ComCode = '{0}' ", search.ComCode);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Course> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Course>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取按钮列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <SYSButton> GetButtonList(ButtonListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" SYS_Button "; //表或者视图
orderby = "BTN_Id"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.BTN_Name)) //按钮中文名称
{
sb.AppendFormat(" and BTN_Name like '%{0}%' ", search.BTN_Name);
}
if (!string.IsNullOrWhiteSpace(search.BTN_Name_En))//城市
{
sb.AppendFormat(" and BTN_Name_En like '%{0}%' ", search.BTN_Name_En);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <SYSButton> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <SYSButton>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页每日结账单列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_DailyReport> GetDailyReportList(DailyReportSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_DailyReport "; //表或者视图
orderby = "ID desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.Name))//学生姓名
{
sb.AppendFormat(" and Name like '%{0}%' ", search.Name);
}
if (!string.IsNullOrWhiteSpace(search.BindPhone))//联系电话
{
sb.AppendFormat(" and BindPhone like '%{0}%' ", search.BindPhone);
}
if (!string.IsNullOrWhiteSpace(search.timeStart))//开班时间
{
sb.AppendFormat(" and CreateTime > = '{0}' ", search.timeStart);
}
if (!string.IsNullOrWhiteSpace(search.timeEnd))//结束时间
{
sb.AppendFormat(" and CreateTime <= '{0}' ", search.timeEnd);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_DailyReport> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_DailyReport>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 返回所有的市场资源/预约单信息 ,EnrollAuditList页面的 微信页面
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_EnrollAudit> GetEnrollAuditInfo(EnrollAuditInfoSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_EnrollAudit "; //表或者视图
orderby = "CreateTime desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.APID)) //判断值。
{
sb.AppendFormat(" and APID = '{0}' ", search.APID);
}
if (!string.IsNullOrWhiteSpace(search.DiscountID)) //判断值。
{
sb.AppendFormat(" and DiscountID = {0} ", search.DiscountID);
}
if (!string.IsNullOrWhiteSpace(search.StateID)) //判断值。
{
sb.AppendFormat(" and StateID = {0} ", search.StateID);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_EnrollAudit> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_EnrollAudit>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 查询文件管理
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <Files> GetFileseList(FileManageListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" Files "; //表或者视图
orderby = "ID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.FileName))//名称
{
sb.AppendFormat(" and FileName like '%{0}%' ", search.FileName);
}
if (!string.IsNullOrWhiteSpace(search.FileTitle))//名称
{
sb.AppendFormat(" and FileTitle like '%{0}%' ", search.FileTitle);
}
//判断是否添加了管理员和校长权限，添加了就不查询全部
int isnull = 0;
for (int i = 0; i < search.isnull.Count; i++)
{
if (search.isnull[i] == "1" || search.isnull[i] == "4")
{
isnull = 1;
}
}
if (search.isnull.Count == 0)
{
isnull = 2;
}
//根据获取的角色来判断是否是管理员和校长，不是就按角色本身来查询
if (isnull != 1)
{
for (int i = 0; i < search.isnull.Count; i++)
{
if (search.isnull[i] != "1" || search.isnull[i] != "4")
{
sb.AppendFormat(" and ToRoles like '%{0}%' ", search.isnull[i]);
}
}
}
if (isnull == 2)
{
sb.AppendFormat(" and 1<>1 ");
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <Files> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <Files>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取按钮列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <Teachers> GetButtonList(TeacherSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" Teachers "; //表或者视图
orderby = "CreateTime"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
switch (search.LeaveDate)
{
case 1:
sb.Append(" and LeaveDate is null ");
break;
case 2:
sb.Append(" and LeaveDate <> '' ");
break;
}
if (!string.IsNullOrWhiteSpace(search.TeacherName))//按钮中文名称
{
sb.AppendFormat(" and name like '%{0}%' ", search.TeacherName);
}
//if (!string.IsNullOrWhiteSpace(search.BTN_Name_En))//城市
// sb.AppendFormat(" and BTN_Name_En like '%{0}%' ", search.BTN_Name_En);
where = sb.ToString();
int allcount = 0;
var list = CommonPage <Teachers> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <Teachers>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取时间段列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <DictionaryItem> GetDictionaryItemList(DictionaryItemSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" dictionaryItem "; //表或者视图
orderby = "CreateTime"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 AND DicTypeID=8 ");
if (!string.IsNullOrWhiteSpace(search.DicItemName))//时间段填写的条件查询
{
sb.AppendFormat(" and DicItemName like '%{0}%' ", search.DicItemName);
}
if (!string.IsNullOrWhiteSpace(search.DicItemID))//时间段下拉框的条件查询
{
sb.AppendFormat(" and DicItemID = '{0}' ", search.DicItemID);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <DictionaryItem> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <DictionaryItem>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取学生列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_AttendanceRecord> ClasshourDetailedlist(ClasshourDetailedSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_AttendanceRecord "; //表或者视图
orderby = "ID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 and AttendanceTypeID=2");
if (!string.IsNullOrWhiteSpace(search.StudentID))//学号
{
sb.AppendFormat(" and StudentID ='{0}' ", search.StudentID);
}
if (!string.IsNullOrWhiteSpace(search.ClassID))//班级ID
{
sb.AppendFormat(" and ClassID ='{0}' ", search.ClassID);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_AttendanceRecord> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_AttendanceRecord>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取教师上课表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_ClassAttendanceList> GetAttendanceRecordList(TeacherClassListSearch search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_ClassAttendanceList "; //表或者视图
orderby = "ClassIndex"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.timeStart))//开班时间
{
sb.AppendFormat(" and ClassDate > = '{0}' ", search.timeStart);
}
if (!string.IsNullOrWhiteSpace(search.timeEnd))//结束时间
{
sb.AppendFormat(" and ClassDate <= '{0}' ", search.timeEnd);
}
if (!string.IsNullOrWhiteSpace(search.teacherID))//vw_AttendanceRecord ID
{
sb.AppendFormat(" and (TeacherID ='{0}' or Teacher2ID ='{0}') ", search.teacherID);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_ClassAttendanceList> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_ClassAttendanceList>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取学生列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_AttendanceRecord> GetAttendanceRecordList(StudentScheduleSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_AttendanceRecord "; //表或者视图
orderby = "ID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.Name)) //学员姓名
{
sb.AppendFormat(" and Name like '%{0}%' ", search.Name);
}
//if (search.timeStart != null && search.timeEnd != null)//时间
// sb.AppendFormat(" and ClassDate between '{0}' and '{1}'", search.timeStart, search.timeEnd);
if (!string.IsNullOrWhiteSpace(search.timeStart))//开班时间
{
sb.AppendFormat(" and ClassDate > = '{0}' ", search.timeStart);
}
if (!string.IsNullOrWhiteSpace(search.timeEnd))//结束时间
{
sb.AppendFormat(" and ClassDate <= '{0}' ", search.timeEnd);
}
if (!string.IsNullOrWhiteSpace(search.AttendanceRecord_StudentID))//学号
{
sb.AppendFormat(" and StudentID ='{0}' ", search.AttendanceRecord_StudentID);
}
if (!string.IsNullOrWhiteSpace(search.AttendanceRecord_ID))//vw_AttendanceRecord ID
{
sb.AppendFormat(" and ID ='{0}' ", search.AttendanceRecord_ID);
}
if (!string.IsNullOrWhiteSpace(search.StudentID))//vw_AttendanceRecord ID
{
sb.AppendFormat(" and StudentID ='{0}' ", search.StudentID);
}
if (!string.IsNullOrWhiteSpace(search.ComCode))//校区
{
sb.AppendFormat(" and [ComCode] = '{0}' ", search.ComCode);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_AttendanceRecord> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_AttendanceRecord>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 报销列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Reimburse> GetReimburseList(ReimbursetListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Reimburse "; //表或者视图
orderby = "ID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.TeacherID))//老师名称
{
sb.AppendFormat(" and TeacherID = '{0}' ", search.TeacherID);
}
if (!string.IsNullOrWhiteSpace(search.StateID))//状态
{
sb.AppendFormat(" and StateID = '{0}' ", search.StateID);
}
if (!string.IsNullOrWhiteSpace(search.CreateTime_start))//开班时间
{
sb.AppendFormat(" and CreateTime > = '{0}' ", search.CreateTime_start);
}
if (!string.IsNullOrWhiteSpace(search.CreateTime_end))//结束时间
{
sb.AppendFormat(" and CreateTime <= '{0}' ", search.CreateTime_end);
}
if (!string.IsNullOrWhiteSpace(search.ComCode))//校区
{
sb.AppendFormat(" and [ComCode] = '{0}' ", search.ComCode);
}
if (!string.IsNullOrWhiteSpace(search.CreatorId))//创建人
{
sb.AppendFormat(" and CreatorId = '{0}' ", search.CreatorId);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Reimburse> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Reimburse>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取学生列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Students> GetStudentList(StudentListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Students "; //表或者视图
orderby = "ID desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.Name))//姓名
{
sb.AppendFormat(" and (Name like '%{0}%' or BindPhone like '%{0}%' )", search.Name);
}
//if (search.CreateTime_end != null && search.CreateTime_start !=null)//时间
// sb.AppendFormat(" and CreateTime between '{0}' and '{1}'", search.CreateTime_start,search.CreateTime_end);
if (!string.IsNullOrWhiteSpace(search.CreateTime_start))//开班时间
{
sb.AppendFormat(" and CreateTime > = '{0}' ", search.CreateTime_start);
}
if (!string.IsNullOrWhiteSpace(search.CreateTime_end))//结束时间
{
sb.AppendFormat(" and CreateTime <= '{0}' ", search.CreateTime_end);
}
if (!string.IsNullOrWhiteSpace(search.ComCode))//校区
{
sb.AppendFormat(" and [ComCode] = '{0}' ", search.ComCode);
}
if (!string.IsNullOrWhiteSpace(search.StudentDicItemID))//学员状态
{
sb.AppendFormat(" and StateID = '{0}' ", search.StudentDicItemID);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Students> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Students>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取教师列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Teachers> GetTeachersList(TeacherSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Teachers "; //表或者视图
orderby = "CreateTime desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.ComCode))//校区
{
sb.AppendFormat(" and [ComCode] = '{0}' ", search.ComCode);
}
if (search.LeaveDate == 1)//离职
{
sb.AppendFormat(" and LeaveDate is null ", search.LeaveDate);
}
else if (search.LeaveDate == 2)
{
sb.AppendFormat(" and LeaveDate is not null ", search.LeaveDate);
}
if (!string.IsNullOrWhiteSpace(search.TeacherName))//是否在职
{
sb.AppendFormat(" and name like '%{0}%' ", search.TeacherName);
}
if (!string.IsNullOrWhiteSpace(search.MobilePhone))//手机号码
{
sb.AppendFormat(" and MobilePhone like '%{0}%' ", search.MobilePhone);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Teachers> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Teachers>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 返回所有的市场资源/预约单信息
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Appointment> GetAPList(EnrollListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Appointment "; //表或者视图
orderby = "CreateTime desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.ApName)) //按钮中文名称
{
sb.AppendFormat(" and [ApName] like '%{0}%' ", search.ApName);
}
if (!string.IsNullOrWhiteSpace(search.ApTel))//城市
{
sb.AppendFormat(" and [ApTel] like '%{0}%' ", search.ApTel);
}
if (!string.IsNullOrWhiteSpace(search.ComCode))//校区
{
sb.AppendFormat(" and [ComCode] = '{0}' ", search.ComCode);
}
if (!string.IsNullOrWhiteSpace(search.timeStart))//记录创建时间
{
sb.AppendFormat(" and CreateTime > = '{0}' ", search.timeStart);
}
if (!string.IsNullOrWhiteSpace(search.timeEnd))//结束时间
{
sb.AppendFormat(" and CreateTime <= '{0}' ", search.timeEnd);
}
if (search.ApStateID != null)//状态
{
sb.AppendFormat(" and [ApStateID] = {0} ", search.ApStateID.Value);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Appointment> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Appointment>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取考勤识别列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_AttendanceOriginal> GetAttendanceOriginalDataList(TeachAttendanceOriginalListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_AttendanceOriginal "; //表或者视图
orderby = "InputDate desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 AND len(UserID) <=5");
if (!string.IsNullOrWhiteSpace(search.username))//姓名
{
sb.AppendFormat(" and username like '%{0}%' ", search.username);
}
if (!string.IsNullOrWhiteSpace(search.InputDate_start))//开班时间
{
sb.AppendFormat(" and workDates > = '{0}' ", search.InputDate_start);
}
if (!string.IsNullOrWhiteSpace(search.InputDate_end))//结束时间
{
sb.AppendFormat(" and workDates <= '{0}' ", search.InputDate_end + " 23:59:59");
}
if (!string.IsNullOrEmpty(search.ComCode))
{
if (search.ComCode == "1")//顺普
{
sb.AppendFormat(" and [iMachineNumber] = 1 ", search.username);
}
else if (search.ComCode == "3")
{
sb.AppendFormat(" and [iMachineNumber] = 7 ", search.username);
}
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_AttendanceOriginal> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_AttendanceOriginal>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 报销列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Transfer> GetTransferList(TransferListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Transfer "; //表或者视图
orderby = "ID desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.JName))//甲方
{
sb.AppendFormat(" and JStudentIDName like '%{0}%' ", search.JName);
}
if (!string.IsNullOrWhiteSpace(search.YName))//乙方
{
sb.AppendFormat(" and YStudentIDName like '%{0}%' ", search.YName);
}
if (!string.IsNullOrWhiteSpace(search.StateID))//状态
{
sb.AppendFormat(" and StateID = '{0}' ", search.StateID);
}
if (!string.IsNullOrWhiteSpace(search.CreateTime_start))//开班时间
{
sb.AppendFormat(" and CreateTime > = '{0}' ", search.CreateTime_start);
}
if (!string.IsNullOrWhiteSpace(search.CreateTime_end))//结束时间
{
sb.AppendFormat(" and CreateTime <= '{0}' ", search.CreateTime_end);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Transfer> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Transfer>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页优惠表列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <Message> GetMessageList(MessageListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" Message "; //表或者视图
orderby = "ID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.Title))//名称
{
sb.AppendFormat(" and Title like '%{0}%' ", search.Title);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <Message> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <Message>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取考勤识别列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_AttendanceOriginal> GetAttendanceOriginalDataList(AttendanceOriginalListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_AttendanceOriginal "; //表或者视图
orderby = "InputDate desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.username))//姓名
{
sb.AppendFormat(" and username like '%{0}%' ", search.username);
}
if (!string.IsNullOrWhiteSpace(search.UserID))//学号
{
sb.AppendFormat(" and UserID like '%{0}%' ", search.UserID);
}
if (!string.IsNullOrWhiteSpace(search.ClassName))//班级名称
{
sb.AppendFormat(" and ClassName like '%{0}%' ", search.ClassName);
}
if (!string.IsNullOrWhiteSpace(search.InputDate_start))//开班时间
{
sb.AppendFormat(" and InputDate > = '{0}' ", search.InputDate_start);
}
if (!string.IsNullOrWhiteSpace(search.InputDate_end))//结束时间
{
sb.AppendFormat(" and InputDate <= '{0}' ", search.InputDate_end);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_AttendanceOriginal> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_AttendanceOriginal>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取UserProgressListData页面数据
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_UserProgress> Getvw_UserProgressList(vw_UserProgressSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_UserProgress "; //表或者视图
orderby = "StudentID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.StudentID))//学生ID，学号
{
sb.AppendFormat(" and StudentID ='{0}' ", search.StudentID);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_UserProgress> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_UserProgress>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取班级列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Classes> GeClass_transfe_List(ClassesListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Classes "; //表或者视图
orderby = "ID"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 and StateID <> 4 ");
if (!string.IsNullOrWhiteSpace(search.ClassName))//当前讲师
{
sb.AppendFormat(" and ClassName like '%{0}%' ", search.ClassName);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Classes> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Classes>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取报名列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Enroll> GeEnrollList(EnrollListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Enroll "; //表或者视图
orderby = "ID desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.ApName))//姓名
{
sb.AppendFormat(" and name like '%{0}%' ", search.ApName);
}
if (!string.IsNullOrWhiteSpace(search.Enroll_StudentID))//学号
{
sb.AppendFormat(" and studentid like '%{0}%' ", search.Enroll_StudentID);
}
if (!string.IsNullOrWhiteSpace(search.ClassID))//班级号
{
sb.AppendFormat(" and ClassID = '{0}' ", search.ClassID);
}
if (search.TeachTypeID != null)//授课方式
{
sb.AppendFormat(" and TeachTypeID = '{0}' ", search.TeachTypeID);
}
if (!string.IsNullOrWhiteSpace(search.Name))//正式学员的姓名或学号
{
sb.AppendFormat(" and (Name like '%{0}%' or studentid like '%{0}%')", search.Name);
}
if (!string.IsNullOrWhiteSpace(search.BindPhone))//手机号码
{
sb.AppendFormat(" and BindPhone like '%{0}%' ", search.BindPhone);
}
if (!string.IsNullOrWhiteSpace(search.islesson)) //正式学员的姓名
{
if (search.islesson == "1") //试听
{
sb.AppendFormat(" and TeachTypeID = 1 ");
}
else
{
sb.AppendFormat(" and TeachTypeID > 1 ");
}
}
if (!string.IsNullOrWhiteSpace(search.ComCode))//校区
{
sb.AppendFormat(" and ComCode = '{0}' ", search.ComCode);
}
if (!string.IsNullOrWhiteSpace(search.Large))//剩余课时大于
{
sb.AppendFormat(" and RemainClassHour >= '{0}' ", search.Large);
}
if (!string.IsNullOrWhiteSpace(search.Small))//剩余课时小于
{
sb.AppendFormat(" and RemainClassHour <= '{0}' ", search.Small);
}
if (!string.IsNullOrWhiteSpace(search.timeStart))//开班时间
{
sb.AppendFormat(" and CreateTime >= '{0}' ", search.timeStart);
}
if (!string.IsNullOrWhiteSpace(search.timeEnd)) //结束时间
{
sb.AppendFormat(" and CreateTime <= '{0}' ", search.timeEnd);
}
if (!string.IsNullOrWhiteSpace(search.StudentDicItemID))//学员状态
{
if (search.StudentDicItemID == "3")
{
sb.AppendFormat(" and StateID in (0,1,3) ");
}
else
{
sb.AppendFormat(" and StateID = '{0}' ", search.StudentDicItemID);
}
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Enroll> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Enroll>(list, search.CurrentPage, search.PageSize, allcount));
}
/// <summary>
/// 分页获取班级列表
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public static PagedList <vw_Classes> GeClassesList(ClassesListSearchModel search)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_Classes "; //表或者视图
orderby = "ID desc"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(search.ComCode))//校区
{
sb.AppendFormat(" and [ComCode] = '{0}' ", search.ComCode);
}
if (!string.IsNullOrWhiteSpace(search.ClassName))//班级名称或ID
{
sb.AppendFormat(" and (ClassName like '%{0}%' OR ID LIKE '%{0}%')", search.ClassName);
}
if (!string.IsNullOrWhiteSpace(search.CourseID))//课程名称ID
{
sb.AppendFormat(" and CourseID like '%{0}%' ", search.CourseID);
}
if (!string.IsNullOrWhiteSpace(search.CourseName))//课程名称中文
{
sb.AppendFormat(" and CourseName like '%{0}%' ", search.CourseName);
}
if (!string.IsNullOrWhiteSpace(search.StartTime_start))//开班时间
{
sb.AppendFormat(" and StartTime > = '{0}' ", search.StartTime_start);
}
if (!string.IsNullOrWhiteSpace(search.StartTime_end))//开班时间
{
sb.AppendFormat(" and StartTime <= '{0}' ", search.StartTime_end);
}
if (!string.IsNullOrWhiteSpace(search.EndTime_start))//结班时间
{
sb.AppendFormat(" and EndTime > = '{0}' ", search.EndTime_start);
}
if (!string.IsNullOrWhiteSpace(search.EndTime_end))//结班时间
{
sb.AppendFormat(" and EndTime <= '{0}' ", search.EndTime_end);
}
if (!string.IsNullOrWhiteSpace(search.TeacherID))//当前讲师
{
sb.AppendFormat(" and TeacherID = '{0}' or Teacher2ID = '{0}'", search.TeacherID);
}
if (search.islisten == "1")//如果是试听班
{
sb.AppendFormat(" and TeachTypeID =1 ");
}
else if (search.islisten == "0")//不是试听班，报名班为了保证有学员课程表，只取未排班数据，以后根据实际情况改
{
sb.AppendFormat(" and TeachTypeID > 1 ");
}
else
{
}
if (search.TeachTypeID != 0 && search.TeachTypeID != null)//授课方式TeachTypeID
{
sb.AppendFormat(" and TeachTypeID = {0} ", search.TeachTypeID);
}
where = sb.ToString();
int allcount = 0;
var list = CommonPage <vw_Classes> .GetPageList(
out allcount, table, fields : fields, where : where.Trim(),
orderby : orderby, pageindex : search.CurrentPage, pagesize : search.PageSize, connect : DBKeys.PRX);
return(new PagedList <vw_Classes>(list, search.CurrentPage, search.PageSize, allcount));
}