PagePostParam postModel = new PagePostParam() { PageIndex = pagerUser.PageIndex, PageSize = pagerUser.PageSize, OrderBy = "id", OrderType = 0, ht = ht };
public class PagePostParam
{
/// <summary>
/// 当前页码
/// </summary>
public int PageIndex { get; set; }
/// <summary>
/// 页大小
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 排序字段
/// </summary>
public string OrderBy { get; set; }
/// <summary>
/// 排序类型 0 降序 1 升序
/// </summary>
public int OrderType { get; set; }
/// <summary>
/// 查询条件
/// </summary>
public Hashtable ht { get; set; }
}
[HttpPost]
[AuthorizeFilter]
public HttpResponseMessage getListByParam([FromBody] object value)
{
ApiResult<PageResultTable> res = new ApiResult<PageResultTable>();
try
{
JavaScriptSerializer Serializer = new JavaScriptSerializer();
PagePostParam postModel = Serializer.Deserialize<PagePostParam>(value.ToString());
Pager page_Model = new Pager();
page_Model.currentIndex = postModel.PageIndex;
page_Model.pageSize = postModel.PageSize;
page_Model.orderBy = postModel.OrderBy;
page_Model.orderType = postModel.OrderType == 0 ? OrderType.Desc : OrderType.Asc;
page_Model.tableName = "V_StudentAppointment";
page_Model.strWhere = " 1=1";
//hash查询参数
Hashtable New_ht = new Hashtable();
if (postModel.ht != null)
{
if (postModel.ht["StuNo"] != null && !string.IsNullOrEmpty(postModel.ht["StuNo"].ToString()))
{
page_Model.strWhere += " and StuNo like'%'+@StuNo+'%'";
New_ht["StuNo"] = postModel.ht["StuNo"];
}
if (postModel.ht["StuCarType"] != null && !string.IsNullOrEmpty(postModel.ht["StuCarType"].ToString()))
{
page_Model.strWhere += " and StuCarType =@StuCarType";
New_ht["StuCarType"] = postModel.ht["StuCarType"];
}
if (postModel.ht["CarType"] != null && !string.IsNullOrEmpty(postModel.ht["CarType"].ToString()))
{
page_Model.strWhere += " and CarType =@CarType";
New_ht["CarType"] = postModel.ht["CarType"];
}
if (postModel.ht["Class"] != null && !string.IsNullOrEmpty(postModel.ht["Class"].ToString()))
{
page_Model.strWhere += " and StudentClass =@Class";
New_ht["Class"] = postModel.ht["Class"];
}
if (postModel.ht["Coach"] != null && !string.IsNullOrEmpty(postModel.ht["Coach"].ToString()))
{
page_Model.strWhere += " and (CoachNo like '%'+@Coach+'%' or CoachName like '%'+@Coach+'%')";
New_ht["Coach"] = postModel.ht["Coach"];
}
if (postModel.ht["AppointmentStatus"] != null && !string.IsNullOrEmpty(postModel.ht["AppointmentStatus"].ToString()))
{
page_Model.strWhere += " and [Status] =@AppointmentStatus";
New_ht["AppointmentStatus"] = postModel.ht["AppointmentStatus"];
}
if (postModel.ht["TimeQuantum"] != null && !string.IsNullOrEmpty(postModel.ht["TimeQuantum"].ToString()))
{
page_Model.strWhere += " and CaseNo in (select CaseNo from StudentAppointmentTime where AppointmentTime =@TimeQuantum)";
New_ht["TimeQuantum"] = postModel.ht["TimeQuantum"];
}
if (postModel.ht["RegSite"] != null && !string.IsNullOrEmpty(postModel.ht["RegSite"].ToString()))
{
page_Model.strWhere += " and RegSiteId =@RegSite";
New_ht["RegSite"] = postModel.ht["RegSite"];
}
if (postModel.ht["TrainingGround"] != null && !string.IsNullOrEmpty(postModel.ht["TrainingGround"].ToString()))
{
page_Model.strWhere += " and EduSiteNo =@TrainingGround";
New_ht["TrainingGround"] = postModel.ht["TrainingGround"];
}
if (postModel.ht["StartDate"] != null && postModel.ht["EndDate"] != null)
{
page_Model.strWhere += " and CaseNo in (select CaseNo from StudentAppointmentTime where AppointmentDate between @StartDate and @EndDate)";
New_ht["StartDate"] = postModel.ht["StartDate"];
New_ht["EndDate"] = postModel.ht["EndDate"];
}
if (postModel.ht["Schoolid"] != null && !string.IsNullOrEmpty(postModel.ht["Schoolid"].ToString()))
{
page_Model.strWhere += " and SchoolId=@Schoolid";
New_ht["Schoolid"] = postModel.ht["Schoolid"];
}
}
page_Model.HtParam = New_ht;
if (page_Model != null)
{
B_Page.GetModel(page_Model);
res.ResultFlag = 1;
res.ResultMsg = "操作成功";
res.ResultObj = new PageResultTable() { RowsCout = page_Model.recordCount, dt = page_Model.dataSource };
}
else
{
res.ResultFlag = 0;
res.ResultMsg = "参数错误";
res.ResultObj = null;
}
}
catch (Exception ex)
{
res.ResultFlag = 0;
res.ResultMsg = ex.Message;
res.ResultObj = null;
//写错误日志
WebLogTool.WriteLog(ex, "StudentAppointment-getListByParam");
}
return HttpHelper.ResponseMessagetoJson(res);
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Wisdom.DAL.Framework.Data;
using System.Collections;
using System.Runtime.InteropServices;
using Wisdom.JPClient.Common.Utils;
namespace Wisdom.JPClient.Bll.Utils
{
public static class B_Page
{
private static D_Page dal = new D_Page();
/// <summary>
/// 获取分页实体
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static Pager GetModel(Pager model)
{
if (!string.IsNullOrEmpty(model.tableName))
{
if (!string.IsNullOrEmpty(model.tableName))
{
model.recordCount = dal.Count(model);
DataSet ds = dal.GetList(model);
model.dataSource = ds.Tables[0];
if (model.recordCount > 0 && model.dataSource.Rows.Count == 0 && model.pageSize != 0)
{
model.currentIndex = model.recordCount / model.pageSize;
if (model.recordCount % model.pageSize != 0)
{
model.currentIndex += 1;
}
ds = dal.GetList(model);
model.dataSource = ds.Tables[0];
}
}
}
return model;
}
/// <summary>
/// 总记录数
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int GetCount(Pager model)
{
return dal.Count(model);
}
/// <summary>
/// 查询所有数据列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static Pager GetAllList(Pager model)
{
if (!string.IsNullOrEmpty(model.tableName))
{
DataSet ds = dal.GetAllList(model);
model.dataSource = ds.Tables[0];
model.recordCount = model.dataSource.Rows.Count;
}
return model;
}
}
/// <summary>
/// 分页查询数据访问层
/// </summary>
public class D_Page
{
/// <summary>
/// 分页查询
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataSet GetList(Pager model)
{
BaseDal dal = new BaseDal();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append(model.Fileds);
strSql.Append(" FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(model.orderBy))
{
strSql.Append("order by T.");
strSql.Append(Utils2.ValidChar(model.orderBy));
if (model.orderType == OrderType.Asc)
{
strSql.Append(" asc ");
}
else
{
strSql.Append(" desc ");
}
}
else
{
strSql.Append(" order by T.id desc");
}
strSql.Append(") AS Row, T.* from ");
strSql.Append(model.tableName);
strSql.Append(" T with(nolock) ");
if (!string.IsNullOrEmpty(model.strWhere))
{
strSql.Append(" WHERE " + model.strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", (model.currentIndex - 1) * model.pageSize + 1, model.currentIndex * model.pageSize);
DataSet ds = null;
ds = dal.GetDataSetBySql(strSql.ToString(), model.HtParam);
return ds;
}
/// <summary>
/// 分页统计
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Count(Pager model)
{
BaseDal dal = new BaseDal();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT count(*) FROM ");
strSql.Append(model.tableName);
strSql.Append(" as T");
strSql.Append(" with(nolock) ");
if (!string.IsNullOrEmpty(model.strWhere))
{
strSql.Append(" where ");
strSql.Append(model.strWhere);
}
DataSet ds = null;
ds = dal.GetDataSetBySql(strSql.ToString(), model.HtParam);
int count = int.Parse(ds.Tables[0].Rows[0][0].ToString(), System.Globalization.CultureInfo.InvariantCulture);
return count;
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataSet GetAllList(Pager model)
{
BaseDal dal = new BaseDal();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append(model.Fileds);
strSql.Append(" FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(model.orderBy))
{
strSql.Append("order by T.");
strSql.Append(Utils2.ValidChar(model.orderBy));
if (model.orderType == OrderType.Asc)
{
strSql.Append(" asc ");
}
else
{
strSql.Append(" desc ");
}
}
else
{
strSql.Append(" order by T.id desc");
}
strSql.Append(") AS Row, T.* from ");
strSql.Append(model.tableName);
strSql.Append(" T with(nolock) ");
if (!string.IsNullOrEmpty(model.strWhere))
{
strSql.Append(" WHERE " + model.strWhere);
}
strSql.Append(" ) TT");
//strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", (model.currentIndex - 1) * model.pageSize + 1, model.currentIndex * model.pageSize);
DataSet ds = null;
ds = dal.GetDataSetBySql(strSql.ToString(), model.HtParam);
return ds;
}
}
public class Pager
{
private string _tableName;
private int _currentIndex = 1;
private int _pageSize = 15;
private int _pageCount;
private string _strWhere;
private string _orderBy;
private OrderType _orderType;
private int _recordCount = 0;
private DataTable _dataSource = null;
private string _fileds = "*";
private Hashtable _htParam = new Hashtable();
/// <summary>
/// 参数 hashtable
/// </summary>
public Hashtable HtParam
{
get { return _htParam; }
set { _htParam = value; }
}
/// <summary>
/// 表名
/// </summary>
public string tableName
{
get { return _tableName; }
set { _tableName = value; }
}
/// <summary>
/// 当前页
/// </summary>
public int currentIndex
{
get { return _currentIndex; }
set { _currentIndex = value; }
}
/// <summary>
/// 页大小
/// </summary>
public int pageSize
{
get { return _pageSize; }
set { _pageSize = value; }
}
/// <summary>
/// 总页数
/// </summary>
public int pageCount
{
get { return _pageCount; }
}
/// <summary>
/// 查询条件
/// </summary>
public string strWhere
{
get { return _strWhere; }
set { _strWhere = value; }
}
/// <summary>
/// 排序字段
/// </summary>
public string orderBy
{
get { return _orderBy; }
set { _orderBy = value; }
}
/// <summary>
/// 排序类型
/// </summary>
public OrderType orderType
{
get { return _orderType; }
set { _orderType = value; }
}
/// <summary>
/// 总行数
/// </summary>
public int recordCount
{
get { return _recordCount; }
set
{
_recordCount = value;
if (_recordCount == 0)
{
_pageCount = 1;
}
else
{
if (_pageSize <= 0)
{
_pageSize = 15;//分母不能为0
}
_pageCount = _recordCount / _pageSize;
if (_recordCount % _pageSize != 0)
{
_pageCount++;
}
}
}
}
/// <summary>
/// 数据源
/// </summary>
public DataTable dataSource
{
get { return _dataSource; }
set { _dataSource = value; }
}
/// <summary>
/// 查询字段 默认为所有字段
/// </summary>
public string Fileds
{
get { return _fileds; }
set { _fileds = value; }
}
}
public class Pager1
{
private string _tableName;
private int _currentIndex = 1;
private int _pageSize = 15;
private int _pageCount;
private string _strWhere;
private string _orderBy;
private OrderType _orderType;
private int _recordCount = 0;
private DataTable _dataSource = null;
private string _fileds = "*";
/// <summary>
/// 表名
/// </summary>
public string tableName
{
get { return _tableName; }
set { _tableName = value; }
}
/// <summary>
/// 当前页
/// </summary>
public int currentIndex
{
get { return _currentIndex; }
set { _currentIndex = value; }
}
/// <summary>
/// 页大小
/// </summary>
public int pageSize
{
get { return _pageSize; }
set { _pageSize = value; }
}
/// <summary>
/// 总页数
/// </summary>
public int pageCount
{
get { return _pageCount; }
}
/// <summary>
/// 查询条件
/// </summary>
public string strWhere
{
get { return _strWhere; }
set { _strWhere = value; }
}
/// <summary>
/// 排序字段
/// </summary>
public string orderBy
{
get { return _orderBy; }
set { _orderBy = value; }
}
/// <summary>
/// 排序类型
/// </summary>
public OrderType orderType
{
get { return _orderType; }
set { _orderType = value; }
}
/// <summary>
/// 总行数
/// </summary>
public int recordCount
{
get { return _recordCount; }
set
{
_recordCount = value;
if (_recordCount == 0)
{
_pageCount = 1;
}
else
{
_pageCount = _recordCount / _pageSize;
if (_recordCount % _pageSize != 0)
{
_pageCount++;
}
}
}
}
/// <summary>
/// 数据源
/// </summary>
public DataTable dataSource
{
get { return _dataSource; }
set { _dataSource = value; }
}
/// <summary>
/// 查询字段 默认为所有字段
/// </summary>
public string Fileds
{
get { return _fileds; }
set { _fileds = value; }
}
}
/// <summary>
/// 排序类型
/// </summary>
public enum OrderType
{
/// <summary>
/// 升序
/// </summary>
Asc,
/// <summary>
/// 降序
/// </summary>
Desc
}
}
浙公网安备 33010602011771号