public List <ZoneArticleView> listByCategoryId(int platType, int categoryId, int pageIndex, int pageSize)
{
string sqlWhere = "CatId=" + categoryId + " and platType=" + platType;
//if (sqlWhere.Length > 0) sqlWhere = sqlWhere.Substring(4);
//初始化分页
PagingModel paging = new PagingModel()
{
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = 0,
PageCount = 0
};
PageQueryModel pageQuery = new PageQueryModel()
{
TableName = "HQ_ShareZone_Article with(nolock)",
Fields = "GoodsId as goodsId,'' as head,'' as name,ShareContent as content,ArticleType as type,ShareImgList as pictures,ShareImgList as smallPictures,VideoList as videos,CreateTime as time,ShareCount as turnAmount,PromotionAmount as reward,'' as linkUrl",
OrderField = "ShareId desc",
SqlWhere = sqlWhere
};
List <ZoneArticleView> list = new CommonPageDAL().GetPageData <ZoneArticleView>(ConfigHelper.MssqlDBConnectionString_Sync, pageQuery, paging);
return(list);
}
public ResultModel <EnterprisesResp> List(PageQueryModel query)
{
//var str = _cacheclient.Get("test");
var list = _efContext.Enterprises.AsQueryable();
if (!string.IsNullOrWhiteSpace(query.Search))
{
list = list.Where(x => x.Name.Contains(query.Search));
}
var result = new ResultModel <EnterprisesResp>();
var dplist = new List <Enterprise>();
using (var _db = Connection())
{
dplist = base.Query().ToList();
}
var resultList = _mapper.Map <List <Enterprise>, List <EnterprisesResp> >(dplist.ToList());
//resultList.FirstOrDefault().Name = str;
//resultList[1].Name = _cacheclient.Pop("list1");
//resultList[2].Name = _cacheclient.Pop("list1");
//resultList[3].Name = _cacheclient.Pop("list1");
//resultList[4].Name = _cacheclient.Pop("list1");
result.Data.AddRange(resultList.Skip((query.Page - 1) * query.Limit).Take(query.Limit).ToList());
result.Code = 0;
result.Msg = "成功";
result.Count = dplist.Count();
return(result);
}
public IActionResult Index(PageQueryModel queryModel)
{
if (!String.IsNullOrEmpty(queryModel.SearchString))
{
queryModel.PageNumber = FIRST_PAGE_NUMBER;
}
queryModel.CategoryId = (queryModel.CategoryId == Guid.Empty || queryModel.CategoryId == null) ? null : queryModel.CategoryId;
queryModel.SearchString = String.IsNullOrEmpty(queryModel.SearchString) ? String.Empty : queryModel.SearchString;
queryModel.ColumnName = String.IsNullOrEmpty(queryModel.ColumnName) ? DEFAULT_SEARCH_COLUMN_NAME : queryModel.ColumnName;
queryModel.SortColumn = String.IsNullOrEmpty(queryModel.SortColumn) ? DEFAULT_SORT_COLUMN_NAME : queryModel.SortColumn;
try
{
int numberOfThePage = queryModel.PageNumber ?? FIRST_PAGE_NUMBER;
int defaultPageSize = queryModel.PageSize ?? DEFAULT_PAGE_SIZE;
int count, maxPrice;
var pageModel = new PageModel()
{
ColumnName = queryModel.ColumnName,
SearchString = queryModel.SearchString,
SortColumn = queryModel.SortColumn,
CountryId = CountryId,
PageSize = defaultPageSize,
PageIndex = numberOfThePage,
CurrentUser = CurrentUserId
};
var filterModel = new FilterModel()
{
CategoryId = queryModel.CategoryId,
CityIds = queryModel.CityIds,
MinimumPrice = queryModel.MinPriceValue,
MaximumPrice = queryModel.MaxPriceValue
};
List <AdGridViewModel> items = _adService.GetPageForAdGridByFilter(pageModel, filterModel, out count, out maxPrice);
ViewData["MinimumPrice"] = queryModel.MinPriceValue;
ViewData["MaximumPrice"] = queryModel.MaxPriceValue == 0 ? maxPrice : queryModel.MaxPriceValue;
ViewData["CityIds"] = queryModel.CityIds;
ViewData["CategoryId"] = queryModel.CategoryId;
ViewData["CurrentFilter"] = queryModel.SearchString;
ViewData["CurrentColumn"] = queryModel.ColumnName;
ViewData["SortColumn"] = queryModel.SortColumn;
ViewData["PageSize"] = queryModel.PageSize;
return(View(PaginatedList <AdGridViewModel> .CreatePageAsync(items, count, numberOfThePage, defaultPageSize, maxPrice)));
}
catch (Exception ex)
{
_logger.LogError(ex, ex.Message);
return(NotFound(ex.Message).WithError(ex.Message));
}
}
public List <Page> SearchPages(PageQueryModel query, Guid language)
{
var pages = new List <Page>();
using (var command = Context.Connection.GenerateCommand(
"ais.search_page",
new
{
id = query.Id,
permanentLink = query.PermanentLink,
pagetypeid = query.TypeId,
pagevisibilityid = query.VisibilityTypeId,
languageid = language
}))
{
using (var reader = command.ExecuteReaderSafety())
{
while (reader.Read())
{
pages.Add(
new Page
{
Id = reader.GetFieldValue <Guid>("id"),
Type = new Nomenclature {
Id = reader.GetFieldValue <Guid>("pagetypeid")
},
VisibilityType = new Nomenclature {
Id = reader.GetFieldValue <Guid>("pagevisibilityid")
},
ParentId = reader.GetFieldValue <Guid?>("parentid"),
Titles = new SortedDictionary <string, string>
{
{ language.ToString(), reader.GetFieldValue <string>("title") }
},
TitlesMenu = new SortedDictionary <string, string>
{
{ language.ToString(), reader.GetFieldValue <string>("titlemenu") }
},
Order = reader.GetFieldValue <long>("custorder"),
PermanentLink = reader.GetFieldValue <string>("permanentlink"),
DbId = reader.GetFieldValue <long>("id_num"),
ParentDbId = reader.GetFieldValue <long?>("parentid_num"),
LocationType = new Nomenclature {
Id = reader.GetFieldValue <Guid>("pagelocationid")
},
IsInNewWindow = reader.GetFieldValue <bool?>("innewwindow") ?? false
});
}
}
}
return(pages);
}
{ /// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionString"></param>
/// <param name="pageQuery">查询内容</param>
/// <param name="paging">分页</param>
/// <returns></returns>
public List <T> GetPageData <T>(string connectionString, PageQueryModel pageQuery, PagingModel paging) where T : new()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
SqlCommand comm = new SqlCommand();
comm.Connection = connection;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "CommonPageProc";
comm.Parameters.Add("@TableName", SqlDbType.VarChar, 5000).Value = pageQuery.TableName;
comm.Parameters.Add("@Fields", SqlDbType.VarChar, 5000).Value = pageQuery.Fields;
comm.Parameters.Add("@OrderField", SqlDbType.VarChar, 5000).Value = pageQuery.OrderField;
comm.Parameters.Add("@sqlWhere", SqlDbType.VarChar, 5000).Value = pageQuery.SqlWhere;
comm.Parameters.Add("@pageSize", SqlDbType.Int, 16).Value = paging.PageSize;
comm.Parameters.Add("@pageIndex", SqlDbType.Int, 16).Value = paging.PageIndex;
SqlParameter paraTP = new SqlParameter("@TotalPage", SqlDbType.Int);
paraTP.Direction = ParameterDirection.Output;
paraTP.Value = paging.PageCount;
comm.Parameters.Add(paraTP);
SqlParameter param = new SqlParameter("@TotalRecord", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
comm.Parameters.Add(param);
List <T> ent = null;
using (IDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection))
{
ent = DbHelperSQL.GetEntityList <T>(dr);
}
paging.PageCount = Convert.ToInt32(comm.Parameters["@TotalPage"].Value);
paging.RecordCount = Convert.ToInt32(param.Value);
comm.Parameters.Clear();
return(ent);
}
}
public List <UserFavoriteModel> list(int userId, int platType, int pageIndex, int pageSize)
{
String sqlWhere = "UserId=" + userId + " and PlatType=" + platType;
//初始化分页
PagingModel paging = new PagingModel()
{
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = 0,
PageCount = 0
};
PageQueryModel pageQuery = new PageQueryModel()
{
TableName = "HQ_User_Favorite with(nolock)",
Fields = "*",
OrderField = "CreateTime desc",
SqlWhere = sqlWhere
};
List <UserFavoriteModel> list = new CommonPageDAL().GetPageData <UserFavoriteModel>(ConfigHelper.MssqlDBConnectionString_Sync, pageQuery, paging);
return(list);
}
/// <summary>
/// 获取订单列表
/// </summary>
/// <param name="PlatType"></param>
/// <param name="orderStatus"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="date"></param>
/// <returns></returns>
public List <OrdersModel> GetOroderList(int PlatType, int orderStatus, int pageIndex, int pageSize, string date)
{
string sqlWhere = "PlatType=" + PlatType;
if (orderStatus != 0)
{
sqlWhere += " and Status=" + orderStatus;
}
if (!string.IsNullOrEmpty(date) || date.Equals("不限"))
{
DateTime startDate, endData;
string time = string.Format("{0}-01 00:00:00", date);
if (DateTime.TryParse(time, out startDate))
{
endData = startDate.AddMonths(1);
sqlWhere += string.Format(" and PayTime>='{0}' and PayTime<='{1}'", startDate, endData);
}
}
//初始化分页
PagingModel paging = new PagingModel()
{
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = 0,
PageCount = 0
};
PageQueryModel pageQuery = new PageQueryModel()
{
TableName = "HQ_Orders with(nolock)",
Fields = "OrderId,OrderSn,PayTime,Status,StatusDesc,GroupSuccessTime,ReceiveTime,OrderType,ModifyTime,PromotionAmount,Amount,GoodsPrice,GoodsQuantity,GoodsThumbnailUrl,GoodsName,GoodsId,PId,CustomParameters,UserId,AgentId,PlatType",
OrderField = "PayTime desc",
SqlWhere = sqlWhere
};
return(new CommonPageDAL().GetPageData <OrdersModel>(ConfigHelper.MssqlDBConnectionString_Sync, pageQuery, paging));
}
public List <UsersModel> listBelongOne(int userId, int pageIndex, int pageSize)
{
string sqlWhere = "BelongOneId=" + userId;
//if (sqlWhere.Length > 0) sqlWhere = sqlWhere.Substring(4);
//初始化分页
PagingModel paging = new PagingModel()
{
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = 0,
PageCount = 0
};
PageQueryModel pageQuery = new PageQueryModel()
{
TableName = "HQ_Users with(nolock)",
Fields = "*",
OrderField = "UserId desc",
SqlWhere = sqlWhere
};
List <UsersModel> list = new CommonPageDAL().GetPageData <UsersModel>(ConfigHelper.MssqlDBConnectionString_Sync, pageQuery, paging);
return(list);
}
public IActionResult GetWorkFlowInstances([FromQuery] PageQueryModel query)
{
return(Ok(_workFlowInstanceService.GetInstanceList(query.Page, query.Size)));
}
public IActionResult PageList(PageQueryModel query)
{
return(new JsonResult(_enterpriseService.List(query)));
}
public List <Page> SearchPages(PageQueryModel query)
{
return(cmsRepository.SearchPages(query, RequestData.LanguageId));
}
