public JsonResult ProductCarTypeQuery(ProductCarTypeQueryRequest request) { if (string.IsNullOrEmpty(request.ProductName) && string.IsNullOrEmpty(request.CarType)) { return(Json(new GridViewModel <ProductCarTypeQueryViewModel>(0, new List <ProductCarTypeQueryViewModel>(), request.sEcho), JsonRequestBehavior.AllowGet)); } var totalNumber = 0; var dt = GetProductCarTypeQuery(request, ref totalNumber); var data = BuildProductCarTypeQueryModel(dt); GridViewModel <ProductCarTypeQueryViewModel> result = new GridViewModel <ProductCarTypeQueryViewModel>(totalNumber, data, request.sEcho); return(Json(result, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 产品/车型查询查询SQL /// </summary> /// <param name="request"></param> /// <param name="totalNumber"></param> /// <returns></returns> public DataTable GetProductCarTypeQuery(ProductCarTypeQueryRequest request, ref int totalNumber) { var countSQl = @"select count(1) from IN_CMS.V_FP_ASSET v where 1=1 {0}"; var sql = @"with cte as( select rownum rn,v.* from IN_CMS.V_FP_ASSET 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.CarType)) { conditions += string.Format(" AND v.ASSET_MODEL_DSC like '%{0}%' ", request.CarType); } 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); }