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);
}
