// GET: Ajax
public ActionResult GetCategorizedProductByPage(int cateId, int page)
{
var products = Context.GetItems(i => i.CategoryId == cateId);
var paging = new PagingHelper().GetPageInfo(products.Count(), page, 9);
var result = products.ToList().GetRange(paging.StartIndex, paging.Count).Select(i => new ProductModel(i));
var model = new PagingViewModel<ProductModel>(paging, result.ToList());
return View();
}
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
if (!parts.sql.ToLower().Contains("order by")) throw new Exception("SQL Server 2012 Paging via OFFSET requires an ORDER BY statement.");
var sqlPage = string.Format("{0}\nOFFSET @{1} ROWS FETCH NEXT @{2} ROWS ONLY", parts.sql, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip, take }).ToArray();
return sqlPage;
}
public ActionResult Index(int?page)
{
int pageIndex = PagingHelper.GetPageIndex(page);
AttendanceRecordCalSearchModel q = new AttendanceRecordCalSearchModel();
IAttendanceRecordCalService ss = new AttendanceRecordCalService(Settings.Default.db);
IPagedList <AttendanceRecordCalView> records = ss.SearchCalView(q).ToPagedList(pageIndex, Settings.Default.pageSize);
ViewBag.Query = q;
SetExtraWorkTypeList(null);
return(View(records));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "wx_my_tijian";
* parameters[1].Value = "id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select t.*,(select username from wx_my_user u where t.userid=u.id) as username from wx_my_tijian t ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "ReceivedMoney";
* parameters[1].Value = "Id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select A.Id as Id, A.Remark as Remark, A.StoreInOrderId, A.CustomerId, A.Name as Name, A.ReceivedTime, A.ChargingCount, A.TotalPrice, A.InvoicedPrice, A.Admin, A.HasBeenInvoiced, A.UnitPriceDetails, A.InvoicedTime, A.InvoicedOperator, A.BeginChargingTime, A.EndChargingTime, A.Status as Status, B.AccountNumber, C.Name as CustomerName ");
strSql.Append("from ReceivedMoney A, StoreInOrder B, Customer C ");
strSql.Append("where A.StoreInOrderId = B.Id and A.CustomerId = C.Id ");
if (strWhere.Trim() != "")
{
strSql.Append(strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select A.Count as StoredOutCount, A.StoreOutWaitingGoodsId, B.AccountNumber, C.Count as StoredInCount, D.Name as GoodsName, E.Name as StoreName ");
strSql.Append("from StoreOutGoods A, StoreInOrder B, StoreInGoods C, Goods D, Store E ");
strSql.Append("where A.StoreInOrderId = B.Id and A.StoreInGoodsId = C.Id and C.GoodsId = D.Id and C.StoreId = E.Id ");
if (strWhere.Trim() != "")
{
strSql.Append(strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public void Page(string sql, StringBuilder sb)
{
PagingHelper.SplitSql(sql, out var parts);
var selectClause = parts.SqlOrderBy == null
? parts.SqlSelectRemoved
: parts.SqlSelectRemoved.Replace(parts.SqlOrderBy, string.Empty);
selectClause = selectClause
.Replace(" FROM ", string.Empty)
.Replace(parts.SqlFrom, string.Empty);
sb.Append(sql).Append($" :::{selectClause}");
}
public ActionResult SumOfStock([Bind(Include = "PartNr")] SumOfStockSearchModel q)
{
int pageIndex = 0;
int.TryParse(Request.QueryString.Get("page"), out pageIndex);
pageIndex = PagingHelper.GetPageIndex(pageIndex);
ISumOfStockService sos = new SumOfStockService(Settings.Default.db);
IPagedList <SumOfStock> sumOfStock = sos.SearchSumOfStock(q).ToPagedList(pageIndex, Settings.Default.pageSize);
ViewBag.Query = q;
return(View(sumOfStock));
}
public DataSet GetAllotStoreInGoodsList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select A.StoreInOrderId, B.AccountNumber, A.Id as Id, A.Count as StoredInCount, A.StoredInTime as StoredInTime, A.StoreId as SourceStoreId, C.Name as CustomerName, D.Name as SourceStoreName, E.Name as GoodsName ");
strSql.Append("from StoreInGoods A, StoreInOrder B, Customer C, Store D, Goods E ");
strSql.Append("where A.StoreInOrderId = B.Id and b.CustomerId = c.Id and A.StoreId = D.Id and A.GoodsId = E.Id ");
if (strWhere.Trim() != "")
{
strSql.Append(strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,shopid,Name,openid,createDate,weixinName,memberName,menberTel,memberAddress,successDingdan,failDingdan,cancelDingdan,status,zongjifen,zongcje ");
strSql.Append(" FROM wx_diancai_member ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public ActionResult StockReport(int?page)
{
int pageIndex = PagingHelper.GetPageIndex(page);
DashboardSearchModel q = new DashboardSearchModel();
IStockSumRecordService sss = new StockSumRecordService(Properties.Settings.Default.db);
IPagedList <StockSumRecord> stockSumRecord = sss.SearchStockReport(q).ToPagedList(pageIndex, Properties.Settings.Default.pageSize);
ViewBag.Query = q;
return(View(stockSumRecord));
}
/// <summary>
/// 获得Tags查询分页数据(搜索用到)
/// </summary>
public DataSet ArticleSearch(string channel_name, string tags, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,channel_id,call_index,title,zhaiyao as remark,tags,add_time,click,img_url,user_name,category_id,update_time from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where status=0 and datediff(d,add_time,getdate())>=0 and id in(select article_id from " + databaseprefix + "article_tags_relation ");
strSql.Append(" where tag_id=(select id from " + databaseprefix + "article_tags where title='" + tags + "'))");
if (!string.IsNullOrEmpty(channel_name))
{
strSql.Append(" and channel_id=(select id from " + databaseprefix + "site_channel where [name]='" + channel_name + "')");
}
recordCount = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public IHttpActionResult GetPage([FromUri] PageQuery query)
{
var dtos = context.Employees.Include(e => e.User)
.AsQueryable()
.Where(e => e.User != null)
.ProjectTo <UserDto>();
var result = PagingHelper.GetPagedResult(dtos, query);
foreach (var dto in result.PageCollection)
{
dto.Role = (Role)Enum.Parse(typeof(Role), userManager.GetRoles(dto.Id).First());
}
return(Ok(result));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "wx_hotel_room";
* parameters[1].Value = "id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,hotelid,roomType,indroduce,roomPrice,salePrice,facilities,createDate,sortid ");
strSql.Append(" FROM wx_hotel_room ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "wx_cards";
* parameters[1].Value = "id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,wid,openid,title,backPic,backMusic,backCartoon,characters,copyRight,buttonCharacter,display,name,url,ckCount,zfCount,createDate ");
strSql.Append(" FROM wx_cards ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public List <Model.dt_item_ex> GetList2(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select i.*,m.real_name,a.name as areaname FROM dt_item i left join dt_manager m on i.user_id=m.id left join dt_area_code a on i.area_code=a.code ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
var ds = DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
return((from DataRow dr in ds.Tables[0].Rows select new DBRowConvertor(dr).ConvertToEntity <Model.dt_item_ex>()).ToList());
}
public DataSet GetSearchList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select A.Status, A.Id AS Id, A.Count, A.StoredInTime, A.Remark, A.StoreWaitingGoodsId, B.AccountNumber, C.Name AS GoodsName, D.Name AS CustomerName, E.Name AS StoreName ");
strSql.Append("from StoreInGoods A, StoreInOrder B, Goods C, Customer D, Store E ");
strSql.Append("where A.StoreInOrderId = B.Id and A.GoodsId = C.Id and A.CustomerId = D.Id and A.StoreId = E.Id ");
if (strWhere.Trim() != "")
{
strSql.Append(strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "wx_diancai_dingdan_manage";
* parameters[1].Value = "id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,shopinfoid,openid,wid,orderNumber,deskNumber,customerName,customerTel,address,oderTime,oderRemark,payAmount,payStatus,'' as payStatusStr,createDate ");
strSql.Append(" FROM wx_diancai_dingdan_manage ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "wx_fc_album";
* parameters[1].Value = "Id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select Id, wid, fid, aid, sort_id, createDate,acontent,aname from
(select a.Id, a.wid, fid, aid, sort_id, a.createDate,acontent,aname from wx_fc_album a inner join wx_albums_info i on i.id=a.aid) s");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "wx_purchase_base";
* parameters[1].Value = "Id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select Id,activityName,activitySummary,activityTimebegin,email,emailPwd,smtp,shopsPwd,activeDescription,specialRemind,activityEndtitle,endExplanation,shopstel,address,introduction,goodName,costPrice,limitCount,groupPrice,totalCount,groupPerson,virtualPerson,copyrightSetup,activityTimeend,createtime ");
strSql.Append(",txtLatXPoint,txtLngYPoint FROM wx_purchase_base ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public ActionResult Index(int?page)
{
int pageIndex = PagingHelper.GetPageIndex(page);
StockSearchModel q = new StockSearchModel();
IStockService ss = new StockService(Settings.Default.db);
IPagedList <Stock> stocks = ss.Search(q).ToPagedList(pageIndex, Settings.Default.pageSize);
ViewBag.Query = q;
return(View(stocks));
}
public DataSet GetSearchList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select A.Id as Id, A.StoreInOrderId, A.Name, A.UnitPrice, A.Count, A.TotalPrice, A.Status, A.PaidTime, A.Admin, A.Customer, A.HasBeenInvoiced, A.Remark as Remark, B.AccountNumber ");
strSql.Append(" from StoreInCost A, StoreInOrder B ");
strSql.Append(" where A.StoreInOrderId = B.Id ");
if (strWhere.Trim() != "")
{
strSql.Append(strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,ROW_NUMBER() OVER(ORDER BY hotelid,orderTime desc) AS orderno,hotelid,openid,oderName,orderNumber,wxOrderNumber,identityNumber,tel,arriveTime,leaveTime,roomType,orderTime,orderNum,price,orderStatus,isDelete,'' as payStatusStr,createDate ");
strSql.Append(" FROM wx_hotel_dingdan ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int category_id, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select id, bId, iName, iType, imgPic, webUrl, remark, createDate, sort_id from wx_qp_img p");
strSql.Append(" where iType =" + category_id);
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select Id,baseid,sn,customerName,customerNum,address,tel,status,dateJoin,dateUse,craeteTime,Remark,openid ");
strSql.Append(" FROM wx_purchase_customer ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public IEnumerable <OnDemandQueryRequest> FindOnDemandReportsForScheduling(int partitionId, IEnumerable <OnDemandQueryType> queryTypes, ref string pageCookie, out bool complete, int pageSize = 100)
{
QueryFilter pagingQueryFilter = PagingHelper.GetPagingQueryFilter(QueryFilter.AndTogether(new QueryFilter[]
{
new ComparisonFilter(ComparisonOperator.Equal, DalHelper.PhysicalInstanceKeyProp, this.GetPartition(partitionId)),
new ComparisonFilter(ComparisonOperator.Equal, OnDemandQueryRequestSchema.Container, OnDemandQueryRequest.DefaultContainer)
}), pageCookie);
IEnumerable <OnDemandQueryRequest> result = (from OnDemandQueryRequest r in this.dataProviderDirectory.FindPaged <OnDemandQueryRequest>(pagingQueryFilter, null, false, null, pageSize)
where queryTypes.Any((OnDemandQueryType t) => t == r.QueryType)
select r).Cache <OnDemandQueryRequest>();
pageCookie = PagingHelper.GetProcessedCookie(pagingQueryFilter, out complete);
return(result);
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "wx_hotels_info";
* parameters[1].Value = "id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * ");
strSql.Append(" FROM wx_hotels_info ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
// GET: PVC
public ActionResult Index(int?page)
{
int pageIndex = PagingHelper.GetPageIndex(page);
PVCSearchModel q = new PVCSearchModel();
IPVCService us = new PVCService(Settings.Default.db, Session["user"] as User);
IPagedList <PVC> pvcs = us.Search(q).ToPagedList(pageIndex, Settings.Default.pageSize);
ViewBag.Query = q;
return(View(pvcs));
}
/// <summary>
/// 获得用户的微信帐号信息【查询分页数据】
/// </summary>
public DataSet GetUserWeiXinList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select w.id,w.uId,w.wxName,w.wxId,w.weixinCode,w.headerpic,w.createDate,w.endDate,w.isDelete,w.deleteDate,w.seq,m.role_id,
m.user_name,m.real_name,m.is_lock from wx_userweixin w left join dt_manager m on w.uId=m.id where w.isDelete=0 ");
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* SqlParameter[] parameters = {
* new SqlParameter("@tblName", SqlDbType.VarChar, 255),
* new SqlParameter("@fldName", SqlDbType.VarChar, 255),
* new SqlParameter("@PageSize", SqlDbType.Int),
* new SqlParameter("@PageIndex", SqlDbType.Int),
* new SqlParameter("@IsReCount", SqlDbType.Bit),
* new SqlParameter("@OrderType", SqlDbType.Bit),
* new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
* };
* parameters[0].Value = "wx_diancai_shopinfo";
* parameters[1].Value = "id";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
#region ExtensionMethod
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,wid,hotelName,hotelLogo,hoteltimeBegin,hoteltimeEnd,limiteOrder,dcRename,sendPrice,sendCost,freeSendcost,radius,sendArea,tel,address,personLimite,notice,hotelintroduction,email,emailpwd,stmp,css,createDate,kcType,miaoshu,xplace,yplace,hoteltimeBegin1,hoteltimeEnd1,hoteltimeBegin2,hoteltimeEnd2,Operator,Recommend,Mobile,ShopCode ");
strSql.Append(" FROM wx_diancai_shopinfo ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public IList <UserGroupContract> GetSingleUserGroupAutocomplete(string query, int?count, bool includeSearchInUsers)
{
if (query == null)
{
query = string.Empty;
}
var countValue = PagingHelper.GetAutocompleteCount(count);
var dbResult = m_userRepository.InvokeUnitOfWork(x => x.FindSingleUserGroups(0, countValue, query, includeSearchInUsers));
var result = m_mapper.Map <IList <UserGroupContract> >(dbResult);
return(result);
}
public ActionResult Index(int? id){
aId = id ?? 1;
List<ViewActivity> list = new List<ViewActivity>();
using (club = new ClubEntities()) {
list = club.ViewActivities.OrderByDescending(a => a.VarDate).Skip((aId - 1) * ClubConst.WebPageSize).Take(ClubConst.WebPageSize).ToList<ViewActivity>();
ViewBag.ActivityCount = club.ViewActivities.Count();
ViewBag.ActivityHistory = club.ViewActivities.OrderByDescending(a => a.EndDate < DateTime.Now).Take(6).ToList<ViewActivity>();
ViewBag.HotActivityBanners = club.ViewBanners.Where(b => b.Status == 1 && b.Variables == "sys-bt-activity-top").ToList<ViewBanner>();
ViewBag.bannerActivityRight = club.ViewBanners.Where(b => b.Status == 1 && b.Variables == "sys-bt-activity-right").FirstOrDefault();
}
PagingHelper pager=new PagingHelper(5,aId,ViewBag.ActivityCount,5);
ViewBag.PageStr = pager.GetPageStringPro("/activity/index/",false);
return View(list);
}
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,wid,qdName,qdPic,remark,succTimes,failTimes,sort_id,createDate ");
strSql.Append(" FROM wx_sjb_qiudui ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public JsonResult GetCustomerOrders(int page)
{
if (CacheHelper._CacheHelper.GetCustomer().IsLoggedIn)
{
var orders = Context.GetOrders(i => i.CustomerId == CacheHelper._CacheHelper.GetCustomer().Id).ToList();
var pageinfo = new PagingHelper();
pageinfo.GetPageInfo(orders.Count, page, 5);
var orderModels = orders.GetRange(pageinfo.StartIndex, pageinfo.Count).Select(i => new OrderModel(i)); ;
return Json(new
{
loggedIn = true,
order_table = RenderRazorViewToString("_CustomerOrderTablePartial", orderModels),
page_info = pageinfo
}, JsonRequestBehavior.AllowGet);
}
return Json(new
{
loggedIn = false,
link = Url.RouteUrl("Login")
}, JsonRequestBehavior.AllowGet);
}
public List<User> LoadUsersByPage(Func<user, bool> predicate, int page, out PagingHelper pagingInfo, bool loadBack = false, int take = 20)
{
var data = Context.GetUsersByPage(predicate, loadBack, take, page, out pagingInfo);
var result = data.Select(i => new User(i)).ToList();
return result;
}
/// <summary>
/// Builds an SQL query suitable for performing page based queries to the database
/// </summary>
/// <param name="skip">The number of rows that should be skipped by the query</param>
/// <param name="take">The number of rows that should be retruend by the query</param>
/// <param name="parts">The original SQL query after being parsed into it's component parts</param>
/// <param name="args">Arguments to any embedded parameters in the SQL query</param>
/// <returns>The final SQL query that should be executed.</returns>
public virtual string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
var sql = string.Format("{0}\nLIMIT @{1} OFFSET @{2}", parts.sql, args.Length, args.Length + 1);
args = args.Concat(new object[] { take, skip }).ToArray();
return sql;
}
public ActionResult GetNewsByPage(int page)
{
var newsList = Context.GetConditionalNews(i => i.IsPublic == true);
var paging = new PagingHelper().GetPageInfo(newsList.Count(), page, 5);
var items = newsList.ToList().GetRange(paging.StartIndex, paging.Count).Select(i => new NewsModel(i));
var model = new PagingViewModel<NewsModel>(paging, items.ToList());
return View();
}
public IEnumerable<user> GetUsersByPage(Func<user, bool> predicate, bool loadBack, int take, int page, out PagingHelper pagingInfo)
{
var context = new appsterEntities();
try
{
var totalItems = context.users.Count(predicate);
pagingInfo = PagingHelper.GetPageInfo(totalItems, page, take);
var skip = pagingInfo.CurentPage == 0 ? 0 : (pagingInfo.CurentPage - 1) * take;
if (loadBack)
{
var data = (from users in context.users.Where(predicate)
orderby users.id descending
select users).Skip(skip).Take(pagingInfo.Count).ToList();
return data;
}
else
{
var data = (from users in context.users.Where(predicate)
select users).Skip(skip).Take(pagingInfo.Count).ToList();
return data;
}
}
finally
{
context.Dispose();
}
}