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