public ActionResult QueryConsultReplies([DataSourceRequest] DataSourceRequest request, ProductConsultSearchModel searchModel, string fromDateTime, string toDateTime) { var service = new ProductConsultService(); string searchStr = this.BuildConsultSearchString(searchModel, fromDateTime, toDateTime); int rowCount = 0, pageCount = 0; var paging = new Paging(searchStr, request.Page, request.PageSize); var list = service.QueryConsultReplies(paging, out pageCount, out rowCount); if (list != null) { var modelList = new List<ProductConsultModel>(); foreach (var consult in list) { modelList.Add(DataTransfer.Transfer<ProductConsultModel>(consult, typeof(Product_Consult))); } var dataSource = new DataSource() { Data = modelList, Total = rowCount, TotalPages = pageCount }; return Json(dataSource, JsonRequestBehavior.AllowGet); } return Json(null); }
private string BuildConsultSearchString(ProductConsultSearchModel model, string fromDateTime, string toDateTime) { StringBuilder sb=new StringBuilder(); const string selectStr = " productID in (Select ID ProductID from Product where "; #region 如果用户选了类别 if (model.ParentCategoryID > 0 && model.CategoryID > 0) { // 若选择了二级分类 if (model.BrandId > 0) { sb.Append("ProductBrandID in (").Append(model.BrandId).Append(")"); } else { sb.Append("ProductCategoryID in (").Append(model.CategoryID).Append(")"); } } else if (model.ParentCategoryID > 0 && model.CategoryID <= 0) { // 若没有选择二级分类 var service = new ProductConsultService(); var list = service.QuerySubCategoriesByParentId(model.ParentCategoryID); sb.Append("ProductCategoryID in ("); if (list != null && list.Count > 0) { // 有一级分类和二级分类 foreach (var category in list) { sb.Append(category.ID).Append(","); } sb.Remove(sb.Length - 1, 1); // 去除最后一个"," } else { // 只有一级分类,没有二级分类,则将0作为子分类的ID号 sb.Append("0"); } sb.Append(")"); // 添加”)“结束 } if (sb.Length > 0) { sb.Insert(0, selectStr).Append(")"); // 加上头部和尾部 } #endregion if (!string.IsNullOrWhiteSpace(model.ProductName)) { sb.Append( sb.Length > 0 ? " And ProductName like '%" + model.ProductName + "%'" : " ProductName like '%" + model.ProductName + "%'"); } if (!string.IsNullOrWhiteSpace(model.UserName)) { sb.Append( sb.Length > 0 ? " And UserName like '%" + model.UserName + "%'" : " UserName like '%" + model.UserName + "%'"); } if (!string.IsNullOrWhiteSpace(fromDateTime)) { sb.Append( sb.Length > 0 ? " And ConsultTime >= '" + fromDateTime + "'" : " ConsultTime >= '" + fromDateTime + "'"); } if (!string.IsNullOrWhiteSpace(toDateTime)) { sb.Append( sb.Length > 0 ? " And ConsultTime <= '" + toDateTime + "'" : " ConsultTime <= '" + toDateTime + "'"); } return sb.ToString(); }