public JsonResult ProductDealerQuery(ProductDealerQueryRequest request) { if (string.IsNullOrEmpty(request.ProductName) && string.IsNullOrEmpty(request.DealerName)) { return(Json(new GridViewModel <ProductDealerQueryViewModel>(0, new List <ProductDealerQueryViewModel>(), request.sEcho), JsonRequestBehavior.AllowGet)); } var totalNumber = 0; var dt = GetProductDealerQuery(request, ref totalNumber); var data = BuildProductDealerQueryModel(dt); GridViewModel <ProductDealerQueryViewModel> result = new GridViewModel <ProductDealerQueryViewModel>(totalNumber, data, request.sEcho); return(Json(result, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 产品/经销商查询查询SQL /// </summary> /// <param name="request"></param> /// <param name="totalNumber"></param> /// <returns></returns> public DataTable GetProductDealerQuery(ProductDealerQueryRequest request, ref int totalNumber) { var countSQl = @"select count(1) from IN_CMS.V_FP_DEALER v where 1=1 {0}"; var sql = @"with cte as( select rownum rn,v.* from IN_CMS.V_FP_DEALER v where 1=1 {0} ) select * from cte c "; var conditions = string.Empty; if (!string.IsNullOrEmpty(request.ProductName)) { conditions += string.Format(" And v.FINANCIAL_PRODUCT_NME like '%{0}%' ", request.ProductName); } if (!string.IsNullOrEmpty(request.DealerName)) { conditions += string.Format(" AND v.COMPANY_NME like '%{0}%' ", request.DealerName); } if (request.StartIndex > 0 && request.EndIndex > 0) { sql += " where c.rn >={1} and c.rn<={2}"; sql = string.Format(sql, conditions, request.StartIndex, request.EndIndex); } else { sql = string.Format(sql, conditions); } countSQl = string.Format(countSQl, conditions); sql = string.Format(sql, conditions, request.StartIndex, request.EndIndex); DataTable dt = CommonFunction.ExecuteDataTableSql("CAPDB", sql); var num = CommonFunction.ExecuteScalar("CAPDB", countSQl); totalNumber = Convert.ToInt32(num); return(dt); }