public ActionResult _AjaxList(GridCommand command, KanbanScanSearchModel searchModel) { if (!string.IsNullOrWhiteSpace(searchModel.CardNo)) { if (searchModel.CardNo.Length > 2 && searchModel.CardNo.Substring(0, 2).ToUpper() == "$K") { searchModel.CardNo = searchModel.CardNo.Substring(2, searchModel.CardNo.Length - 2); } } SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel); GridModel<KanbanScan> gridList = GetAjaxPageData<KanbanScan>(searchStatementModel, command); if (gridList.Data != null && gridList.Data.Count() > 0) { foreach (KanbanScan kanbanScan in gridList.Data) { var kanbanCards = this.genericMgr.FindAll<KanbanCard>(" select k from KanbanCard as k where k.FlowDetailId=? ", kanbanScan.FlowDetailId); if (kanbanCards != null && kanbanCards.Count > 0) { kanbanScan.KanbanNo = kanbanCards.First().OpRefSequence; } } } return PartialView(gridList); }
private SearchStatementModel PrepareSearchStatement(GridCommand command, KanbanScanSearchModel searchModel) { string whereStatement = string.Empty; IList<object> param = new List<object>(); //if (searchModel.IsTempKanbanCard.HasValue && searchModel.IsTempKanbanCard.Value) //{ // whereStatement += " where TempKanbanCard <> '' and TempKanbanCard is not null "; //} SecurityHelper.AddRegionPermissionStatement(ref whereStatement, "s", "Region"); HqlStatementHelper.AddEqStatement("CardNo", searchModel.CardNo, "s", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "s", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Supplier", searchModel.Supplier, "s", ref whereStatement, param); HqlStatementHelper.AddEqStatement("LogisticCenterCode", searchModel.LcCode, "s", ref whereStatement, param); //HqlStatementHelper.AddEqStatement("TempKanbanCard", searchModel.TempKanbanCard, "s", ref whereStatement, param); //HqlStatementHelper.AddGeStatement("ScanTime", searchModel.StartDate, "s", ref whereStatement, param); ////改到当日的最后一秒 //HqlStatementHelper.AddLeStatement("ScanTime", searchModel.EndDate.Add(new TimeSpan(23, 59, 59)), "s", ref whereStatement, param); //if (searchModel.StartDate != null & searchModel.EndDate != null) //{ // HqlStatementHelper.AddBetweenStatement("ScanTime", searchModel.StartDate, searchModel.EndDate, "s", ref whereStatement, param); //} //else if (searchModel.StartDate != null) { HqlStatementHelper.AddGeStatement("ScanTime", searchModel.StartDate, "s", ref whereStatement, param); } if ( searchModel.EndDate != null) { HqlStatementHelper.AddLeStatement("ScanTime", searchModel.EndDate, "s", ref whereStatement, param); } if (searchModel.IsNotOrdered) { HqlStatementHelper.AddEqStatement("IsOrdered", false, "s", ref whereStatement, param); } string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); 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 List(GridCommand command, KanbanScanSearchModel searchModel) { SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel); ViewBag.PageSize = base.ProcessPageSize(command.PageSize); return View(); }
public void ExportKanbanScanXLS(KanbanScanSearchModel searchModel) { StringBuilder sb = new StringBuilder(); IList<object> param = new List<object>(); sb.Append(@" select ks.CardNo,ks.Seq,ks.Supplier,ks.Flow,ks.SupplierName,ks.Item,ks.RefItemCode,ks.ItemDesc,ks.ScanQty,ks.ScanTime,ks.ScanUserNm,ks.OrderTime , ks.OrderUserNm,ks.OrderQty,kb.OpRefSeq from KB_KanbanScan as ks left join ( select FlowDetId,OpRefSeq from KB_KanbanCard group by FlowDetId,OpRefSeq ) as kb on ks.FlowDetId=kb.FlowDetId where 1=1 "); if (searchModel.IsTempKanbanCard.HasValue && searchModel.IsTempKanbanCard.Value) { sb.Append(" and TempKanbanCard <> '' and TempKanbanCard is not null "); } if (!string.IsNullOrWhiteSpace(searchModel.CardNo)) { if (!string.IsNullOrWhiteSpace(searchModel.CardNo)) { if (searchModel.CardNo.Length > 2 && searchModel.CardNo.Substring(0, 2).ToUpper() == "$K") { searchModel.CardNo = searchModel.CardNo.Substring(2, searchModel.CardNo.Length - 2); } } sb.Append(" and CardNo = ?"); param.Add(searchModel.CardNo); } if (searchModel.Item != null) { sb.Append(" and Item = ?"); param.Add(searchModel.Item); } if (searchModel.Supplier != null) { sb.Append(" and Supplier = ?"); param.Add(searchModel.Supplier); } if (searchModel.IsNotOrdered) { sb.Append(" and IsOrdered = 1"); } if (searchModel.StartDate != null) { sb.Append(" and ScanTime >=?"); param.Add(searchModel.StartDate); } if (searchModel.EndDate != null) { sb.Append(" and ScanTime < ?"); param.Add(searchModel.EndDate); } IList<KanbanScan> exportList = new List<KanbanScan>(); IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sb.ToString(), param.ToArray()); if (searchResult != null && searchResult.Count > 0) { //ks.CardNo,ks.Seq,ks.Supplier,ks.Flow,ks.SupplierName,ks.Item,ks.RefItemCode,ks.ItemDesc,ks.ScanQty,ks.ScanTime,ks.ScanUserNm,ks.OrderTime , //ks.OrderUserNm,ks.OrderQty,kb.OpRefSeq exportList = (from tak in searchResult select new KanbanScan { CardNo = (string)tak[0], Sequence = (string)tak[1], Supplier = (string)tak[2], Flow = (string)tak[3], SupplierName = (string)tak[4], Item = (string)tak[5], ReferenceItemCode = (string)tak[6], ItemDescription = (string)tak[7], ScanQty = (decimal)tak[8], ScanTime = (DateTime?)tak[9], ScanUserName = (string)tak[10], OrderTime = (DateTime?)tak[11], OrderUserName = (string)tak[12], OrderQty = (decimal)tak[13], KanbanNo = (string)tak[14], }).ToList(); } ExportToXLS<KanbanScan>("ExportKanbanScanXLS", "xls", exportList); }