public ActionResult _AjaxList(GridCommand command, SnapshotFlowDet4LeanEngineSearchModel searchModel) { string selectSql = this.PrepareSearchStatement(command, searchModel); var total = this.genericMgr.FindAllWithNativeSql<int>(string.Format("select count(*) from ({0}) result", selectSql))[0]; string whereStatement = string.Format(" select * from ( {0} ) result where result.RowId between {1} and {2}", selectSql, command.PageSize * (command.Page - 1), command.PageSize * command.Page); //var returnList = this.genericMgr.FindEntityWithNativeSql<SnapshotFlowDet4LeanEngine>(whereStatement); var searchList = this.genericMgr.FindAllWithNativeSql<object[]>(whereStatement); var returnList = (from take in searchList select new SnapshotFlowDet4LeanEngine { Id = (Int64)take[1], Flow = (string)take[2], Item = (string)take[3], LocationFrom = (string)take[4], LocationTo = (string)take[5], OrderNo = (string)take[6], Lvl = Convert.ToInt16((take[7]).ToString()), ErrorId = Convert.ToInt16((take[8]).ToString()), Message = (string)take[9], CreateDate = (DateTime)take[10], BatchNo = (int)take[11], ItemDesc = (string)take[12], ReferenceItemCode = (string)take[13], }).ToList(); GridModel<SnapshotFlowDet4LeanEngine> gridMode = new GridModel<SnapshotFlowDet4LeanEngine>(); gridMode.Total = total; gridMode.Data = returnList; return PartialView(gridMode); //string //SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel); //return PartialView(GetAjaxPageData<SnapshotFlowDet4LeanEngine>(searchStatementModel, command)); }
public void ExportInfo(SnapshotFlowDet4LeanEngineSearchModel searchModel) { string selectSql = this.PrepareSearchStatement(null, searchModel); var searchList = this.genericMgr.FindAllWithNativeSql<object[]>(selectSql); var returnList = (from take in searchList select new SnapshotFlowDet4LeanEngine { Id = (Int64)take[1], Flow = (string)take[2], Item = (string)take[3], LocationFrom = (string)take[4], LocationTo = (string)take[5], OrderNo = (string)take[6], Lvl = Convert.ToInt16((take[7]).ToString()), ErrorId = Convert.ToInt16((take[8]).ToString()), Message = (string)take[9], CreateDate = (DateTime)take[10], BatchNo = (int)take[11], ItemDesc = (string)take[12], ReferenceItemCode = (string)take[13], }).ToList(); ExportToXLS<SnapshotFlowDet4LeanEngine>("ExportInfo", "xls", returnList); }
private string PrepareSearchStatement(GridCommand command, SnapshotFlowDet4LeanEngineSearchModel searchModel) { IList<object> param = new List<object>(); // string whereStatement = @"select t1.*,RowId=ROW_NUMBER()OVER(order by t1.id desc) from LOG_SnapshotFlowDet4LeanEngine as t1 where 1=1 and t1.ErrorId in (13,14,15) // and t1.BatchNo=(select MAX(t2.BatchNo) from LOG_SnapshotFlowDet4LeanEngine as t2 where // (t1.Flow=t2.Flow or ( t1.Flow is null and t2.Flow is null )) and // (t1.Item=t2.Item or ( t1.Item is null and t2.Item is null ))and // (t1.LocFrom=t2.LocFrom or ( t1.LocFrom is null and t2.LocFrom is null )) and // (t1.LocTo=t2.LocTo or ( t1.LocTo is null and t2.LocTo is null )) and // (t1.OrderNo=t2.OrderNo or ( t1.OrderNo is null and t2.OrderNo is null )) and // (t1.Lvl=t2.Lvl or ( t1.Lvl is null and t2.Lvl is null )) and // (t1.ErrorId=t2.ErrorId or ( t1.ErrorId is null and t2.ErrorId is null )) ) "; string whereStatement = @" select top 65532 RowId=ROW_NUMBER()OVER(order by t1.id desc),t1.Id, t1.Flow, t1.Item, t1.LocFrom, t1.LocTo, t1.OrderNo, t1.Lvl, isnull(t1.ErrorId,0) as ErrorId, t1.Msg, t1.CreateDate, t1.BatchNo,mi.Desc1,mi.RefCode from LOG_SnapshotFlowDet4LeanEngine as t1 left join MD_Item as mi on mi.Code=t1.Item where 1=1 and t1.BatchNo=(select MAX(t2.BatchNo) from LOG_SnapshotFlowDet4LeanEngine as t2) "; //and t1.ErrorId in (13,14,15) if (!string.IsNullOrWhiteSpace(searchModel.Item)) { whereStatement += string.Format(" and t1.Item='{0}' ", searchModel.Item); } //if (!string.IsNullOrWhiteSpace(searchModel.Flow)) //{ // whereStatement += string.Format(" and t1.Flow='{0}' ", searchModel.Flow); //} if (!string.IsNullOrWhiteSpace(searchModel.LocationFrom)) { whereStatement += string.Format(" and t1.LocFrom='{0}' ", searchModel.LocationFrom); } if (!string.IsNullOrWhiteSpace(searchModel.LocationTo)) { whereStatement += string.Format(" and t1.LocTo='{0}' ", searchModel.LocationTo); } if (searchModel.ErrorId.HasValue) { if (searchModel.ErrorId.Value == 0) { whereStatement += string.Format(" and t1.ErrorId is null"); } else { whereStatement += string.Format(" and t1.ErrorId='{0}' ", searchModel.ErrorId.Value); } } //if (!string.IsNullOrWhiteSpace(searchModel.OrderNo)) //{ // whereStatement += string.Format(" and t1.OrderNo='{0}' ", searchModel.OrderNo); //} //if (searchModel.CreateDateFrom.HasValue) //{ // whereStatement += string.Format(" and t1.CreateDate>='{0}' ", searchModel.CreateDateFrom.Value); //} //if (searchModel.CreateDateTo.HasValue) //{ // whereStatement += string.Format(" and t1.CreateDate<='{0}' ", searchModel.CreateDateTo.Value); //} return whereStatement; }
public ActionResult List(GridCommand command, SnapshotFlowDet4LeanEngineSearchModel searchModel) { SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel); ViewBag.PageSize = base.ProcessPageSize(command.PageSize); return View(); }