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