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