public ActionResult List(GridCommand command, InvTransSearchModel searchModel) { TempData["InvTransSearchModel"] = searchModel; ViewBag.PageSize = 20; return View(); }
private SearchStatementModel PrepareSearchStatement(GridCommand command, InvTransSearchModel searchModel) { string whereStatement = ""; IList<object> param = new List<object>(); HqlStatementHelper.AddLikeStatement("BWART", searchModel.BWART, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddBetweenStatement("BUDAT", searchModel.BUDAT != null?searchModel.BUDAT:DateTime.MinValue.ToString("yyyyMMdd"), searchModel.BUDATTo != null?searchModel.BUDATTo:DateTime.MaxValue.ToString("yyyyMMdd"), "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("EBELN", searchModel.EBELN, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("EBELP", searchModel.EBELP, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("FRBNR", searchModel.FRBNR, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddEqStatement("LIFNR", searchModel.LIFNR, "t", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Status", searchModel.Status, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("SGTXT", searchModel.SGTXT, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("LGORT", searchModel.LGORT, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("MATNR", searchModel.MATNR, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("XBLNR", searchModel.XBLNR, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("RSNUM", searchModel.RSNUM, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("RSPOS", searchModel.RSPOS, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("FRBNR", searchModel.FRBNR, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("XABLN", searchModel.XABLN, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); if (searchModel.StartTime != null & searchModel.EndTime != null) { HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartTime, searchModel.EndTime, "t", ref whereStatement, param); } else if (searchModel.StartTime != null & searchModel.EndTime == null) { HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartTime, "t", ref whereStatement, param); } else if (searchModel.StartTime == null & searchModel.EndTime != null) { HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndTime, "t", ref whereStatement, param); } string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); if (command.SortDescriptors.Count == 0) { sortingStatement = " order by t.CreateDate desc"; } SearchStatementModel searchStatementModel = new SearchStatementModel(); searchStatementModel.SelectCountStatement = selectCountStatement; searchStatementModel.SelectStatement = selectStatement; searchStatementModel.WhereStatement = whereStatement; searchStatementModel.SortingStatement = sortingStatement; searchStatementModel.Parameters = param.ToArray<object>(); return searchStatementModel; }
private SearchStatementModel PrepareSearchStatement(GridCommand command, InvTransSearchModel searchModel) { string whereStatement = " where 1=1 "; if (!string.IsNullOrWhiteSpace(searchModel.items)) { string items = searchModel.items.Replace("\r\n", ","); items = items.Replace("\n", ","); string[] itemArr = items.Split(','); string itemParam = string.Empty; for (int i = 0; i < itemArr.Length; i++) { whereStatement += string.Format(" and MATNR in('{0}') ", string.Join("','", itemArr)); } } IList<object> param = new List<object>(); HqlStatementHelper.AddLikeStatement("BWART", searchModel.BWART, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddBetweenStatement("BUDAT", searchModel.BUDAT != null?searchModel.BUDAT:DateTime.MinValue.ToString("yyyyMMdd"), searchModel.BUDATTo != null?searchModel.BUDATTo:DateTime.MaxValue.ToString("yyyyMMdd"), "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("EBELN", searchModel.EBELN, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("EBELP", searchModel.EBELP, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("FRBNR", searchModel.FRBNR, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddEqStatement("LIFNR", searchModel.LIFNR, "t", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Status", searchModel.Status, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("SGTXT", searchModel.SGTXT, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("LGORT", searchModel.LGORT, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); //HqlStatementHelper.AddLikeStatement("MATNR", searchModel.MATNR, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("XBLNR", searchModel.XBLNR, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("RSNUM", searchModel.RSNUM, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("RSPOS", searchModel.RSPOS, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("FRBNR", searchModel.FRBNR, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("XABLN", searchModel.XABLN, HqlStatementHelper.LikeMatchMode.Start, "t", ref whereStatement, param); HqlStatementHelper.AddEqStatement("ErrorCount", searchModel.ErrorCount, "t", ref whereStatement, param); if (searchModel.StartTime != null & searchModel.EndTime != null) { HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartTime, searchModel.EndTime, "t", ref whereStatement, param); } else if (searchModel.StartTime != null & searchModel.EndTime == null) { HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartTime, "t", ref whereStatement, param); } else if (searchModel.StartTime == null & searchModel.EndTime != null) { HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndTime, "t", ref whereStatement, param); } string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); if (command.SortDescriptors.Count == 0) { sortingStatement = " order by CreateDate desc"; } SearchStatementModel searchStatementModel = new SearchStatementModel(); searchStatementModel.SelectCountStatement = selectCountStatement; searchStatementModel.SelectStatement = selectStatement; searchStatementModel.WhereStatement = whereStatement; searchStatementModel.SortingStatement = sortingStatement; searchStatementModel.Parameters = param.ToArray<object>(); return searchStatementModel; }
public ActionResult _AjaxList(GridCommand command, InvTransSearchModel searchModel) { SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel); GridModel<InvTrans> gridlist = GetAjaxPageData<InvTrans>(searchStatementModel, command); TempData["InvTransSearchModel_1"] = searchModel; TempData["Total"] = gridlist.Total; foreach (InvTrans inv in gridlist.Data) { inv.StatusName = GetEnumDescription(inv.Status); } return View(gridlist); //return PartialView(GetAjaxPageData<ReceiptMaster>(searchStatementModel, command)); }
public ActionResult _AjaxList(GridCommand command, InvTransSearchModel searchModel) { SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel); int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + searchStatementModel.SelectStatement + searchStatementModel.WhereStatement + ") as r1", searchStatementModel.Parameters).First(); string sql = string.Format("select * from (select RowId=ROW_NUMBER()OVER({0}),r1.* from ({1}) as r1 ) as rt where rt.RowId between {2} and {3}", searchStatementModel.SortingStatement, searchStatementModel.SelectStatement + searchStatementModel.WhereStatement, (command.Page - 1) * command.PageSize, command.PageSize * command.Page); IList<InvTrans> invTranslist = this.genericMgr.FindEntityWithNativeSql<InvTrans>(sql, searchStatementModel.Parameters); TempData["InvTransSearchModel_1"] = searchModel; TempData["Total"] = total; foreach (InvTrans inv in invTranslist) { inv.StatusName = GetEnumDescription(inv.Status); } GridModel<InvTrans> gridModel = new GridModel<InvTrans>(); gridModel.Total = total; gridModel.Data = invTranslist; return View(gridModel); //return PartialView(GetAjaxPageData<ReceiptMaster>(searchStatementModel, command)); }