public void ExportKBTurnoverRate(KanbanCardSearchModel searchModel)
{
if (searchModel.StartDate == null || searchModel.EndDate == null)
{
SaveWarningMessage("开始结束时间不能为空。");
}
else if (searchModel.StartDate.Value.AddDays(31) < searchModel.EndDate)
{
SaveWarningMessage("一次最多只能查30天的。");
}
else
{
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);
}
}
string searchSql = PrepareTurnoverRateSearchStatement(searchModel);
IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(searchSql + " order by CardNo ");
IList<KanbanCard> opList = new List<KanbanCard>();
if (searchResult != null && searchResult.Count > 0)
{
#region
//c.CardNo,c.OpRefSeq,c.Item,c.RefItemCode,c.ItemDesc,c.Qty,c.opref,CONVERT(varchar(10), s.CreateDate, 121) as '扫描日期',COUNT(*) as '扫描次数'
opList = (from tak in searchResult
select new KanbanCard
{
CardNo = (string)tak[0],
OpRefSequence = (string)tak[1],
Item = (string)tak[2],
ReferenceItemCode = (string)tak[3],
ItemDescription = (string)tak[4],
Qty = (decimal)tak[5],
OpRef = (string)tak[6],
ScanDate = tak[7] == null ? searchModel.StartDate.Value.Date : Convert.ToDateTime(tak[7]),
//ScanDate = tak[7] == null ? searchModel.StartDate.Value.Date : (Convert.ToDateTime(tak[7]) < searchModel.StartDate.Value.Date ? searchModel.StartDate.Value.Date : (Convert.ToDateTime(tak[7]) > searchModel.EndDate.Value.Date ? searchModel.EndDate.Value.Date : Convert.ToDateTime(tak[7]))),
//ScanDate = Convert.ToDateTime(tak[7]),
ScanCount = (int?)tak[8],
Container1 = (string)tak[9],
ContainerDescription = (string)tak[10],
}).ToList();
#endregion
var groups = opList.GroupBy(g => new { g.CardNo, g.OpRefSequence, g.Item, g.ReferenceItemCode, g.ItemDescription, g.OpRef, g.Qty }).ToList();
var listDays = opList.Select(o => o.ScanDate).Distinct().OrderBy(o => o.Value).ToList();
#region
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("sheet1");
MemoryStream output = new MemoryStream();
#region 写入字段
IRow rowHeader = sheet1.CreateRow(0);
// 零件号PRP 旧零件号 零件名称 看板号 工位 包装量
for (int i = 0; i < 10; i++)
{
if (i == 0) //序号
{
rowHeader.CreateCell(i).SetCellValue("序号");
}
else if (i == 1) //OpRefSequence
{
rowHeader.CreateCell(i).SetCellValue("看板号");
}
else if (i == 2) //Item
{
rowHeader.CreateCell(i).SetCellValue("物料代码");
}
else if (i == 3) //ReferenceItemCode
{
rowHeader.CreateCell(i).SetCellValue("旧图号");
}
else if (i == 4) //ItemDescription
{
rowHeader.CreateCell(i).SetCellValue("物料描述");
}
else if (i == 5) //ItemDescription
{
rowHeader.CreateCell(i).SetCellValue("容器代码");
}
else if (i == 6) //ItemDescription
{
rowHeader.CreateCell(i).SetCellValue("容器描述");
}
else if (i == 7) //Qty
{
rowHeader.CreateCell(i).SetCellValue("包装量");
}
else if (i == 8) //OpRef
{
rowHeader.CreateCell(i).SetCellValue("工位");
}
else
{
//foreach (var date in listDays)
//{
// rowHeader.CreateCell(i).SetCellValue(date.Value.ToShortDateString());
// i++;
//}
var minDate = searchModel.StartDate.Value;
var maxDate = searchModel.EndDate.Value;
TimeSpan ts = minDate - maxDate; int sub = ts.Days;
while (minDate <= maxDate)
{
rowHeader.CreateCell(i).SetCellValue(minDate.ToShortDateString());
minDate = minDate.AddDays(1);
i++;
}
}
}
#endregion
#region 写入数值
int l = 0;
int rowIndex = 1;
foreach (var g in groups)
{
var firstPlan = g.First();
l++;
IRow rowDetail = sheet1.CreateRow(rowIndex);
int cell = 0;
rowDetail.CreateCell(cell++).SetCellValue(l);
rowDetail.CreateCell(cell++).SetCellValue(firstPlan.OpRefSequence);
rowDetail.CreateCell(cell++).SetCellValue(firstPlan.Item);
rowDetail.CreateCell(cell++).SetCellValue(firstPlan.ReferenceItemCode);
rowDetail.CreateCell(cell++).SetCellValue(firstPlan.ItemDescription);
rowDetail.CreateCell(cell++).SetCellValue(firstPlan.Container1);
rowDetail.CreateCell(cell++).SetCellValue(firstPlan.ContainerDescription);
rowDetail.CreateCell(cell++).SetCellValue(firstPlan.Qty.ToString("0.##"));
rowDetail.CreateCell(cell++).SetCellValue(firstPlan.OpRef);
var minDate1 = searchModel.StartDate.Value;
var maxDate1 = searchModel.EndDate.Value;
int i = 0;
while (minDate1 <= maxDate1)
{
var curenKbs = g.Where(p => p.ScanDate == minDate1);
var curenKb = curenKbs.Count() > 0 ? curenKbs.First() : new KanbanCard();
var createCell = rowDetail.CreateCell(cell++);
createCell.SetCellType(CellType.NUMERIC);
createCell.SetCellValue(Convert.ToDouble(curenKb.ScanCount));
minDate1 = minDate1.AddDays(1);
}
//foreach (var day in listDays)
//{
// var curenKbs = g.Where(p => p.ScanDate == day);
// var curenKb = curenKbs.Count() > 0 ? curenKbs.First() : new KanbanCard();
// var createCell = rowDetail.CreateCell(cell++);
// createCell.SetCellType(CellType.NUMERIC);
// createCell.SetCellValue(Convert.ToDouble(curenKb.ScanCount));
//}
rowIndex++;
}
#endregion
hssfworkbook.Write(output);
string filename = "ExportKBTurnoverRate.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
Response.BinaryWrite(output.GetBuffer());
Response.End();
#endregion
}
}
}
public ActionResult _AjaxTurnoverRateList(GridCommand command, KanbanCardSearchModel searchModel)
{
#region grid column
var columns = new List<GridColumnSettings>();
columns.Add(new GridColumnSettings
{
Member = "CardNo",
Title = Resources.KB.KanbanCard.KanbanCard_CardNo,
Sortable = false
});
columns.Add(new GridColumnSettings
{
Member = "OpRefSequence",
Title = Resources.KB.KanbanCard.KanbanCard_OpRefSequence,
Sortable = false
});
columns.Add(new GridColumnSettings
{
Member = "Item",
Title = Resources.KB.KanbanCard.KanbanCard_Item,
Sortable = false
});
columns.Add(new GridColumnSettings
{
Member = "ReferenceItemCode",
Title = Resources.KB.KanbanCard.KanbanCard_ReferenceItemCode,
Sortable = false
});
columns.Add(new GridColumnSettings
{
Member = "ItemDescription",
Title = Resources.KB.KanbanCard.KanbanCard_ItemDescription,
Sortable = false
});
columns.Add(new GridColumnSettings
{
Member = "Container1",
Title = Resources.SCM.FlowDetail.FlowDetail_Container,
Sortable = false
});
columns.Add(new GridColumnSettings
{
Member = "ContainerDescription",
Title = Resources.SCM.FlowDetail.FlowDetail_ContainerDescription,
Sortable = false
});
columns.Add(new GridColumnSettings
{
Member = "Qty",
Title = Resources.KB.KanbanCard.KanbanCard_Qty,
Sortable = false
});
columns.Add(new GridColumnSettings
{
Member = "OpRef",
Title = Resources.KB.KanbanCard.KanbanCard_OpRef,
Sortable = false
});
#endregion
if (searchModel.StartDate == null || searchModel.EndDate == null)
{
}
else if (searchModel.StartDate.Value.AddDays(31) < searchModel.EndDate)
{
}
else
{
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);
}
}
command.PageSize = command.PageSize == 0 ? 20000 : command.PageSize;
command.Page = command.Page == 0 ? 1 : command.Page;
string searchSql = PrepareTurnoverRateSearchStatement(searchModel);
int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + searchSql + ") as r1").First();
string sortingStatement = " order by CardNo ";
searchSql = string.Format("select * from (select RowId=ROW_NUMBER()OVER({0}),r1.* from ({1}) as r1 ) as rt where rt.RowId between {2} and {3}", sortingStatement, searchSql, (command.Page - 1) * command.PageSize, command.PageSize * command.Page);
IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(searchSql);
IList<KanbanCard> opList = new List<KanbanCard>();
if (searchResult != null && searchResult.Count > 0)
{
#region
//c.CardNo,c.OpRefSeq,c.Item,c.RefItemCode,c.ItemDesc,c.Qty,c.opref,CONVERT(varchar(10), s.CreateDate, 121) as '扫描日期',COUNT(*) as '扫描次数'
opList = (from tak in searchResult
select new KanbanCard
{
CardNo = (string)tak[1],
OpRefSequence = (string)tak[2],
Item = (string)tak[3],
ReferenceItemCode = (string)tak[4],
ItemDescription = (string)tak[5],
Qty = (decimal)tak[6],
OpRef = (string)tak[7],
//ScanDate = Convert.ToDateTime(tak[8]),
ScanDate = tak[8] == null ? searchModel.StartDate.Value.Date : Convert.ToDateTime(tak[8]),
//ScanDate = tak[8] == null ? searchModel.StartDate.Value.Date : (Convert.ToDateTime(tak[8]) < searchModel.StartDate.Value.Date ? searchModel.StartDate.Value.Date : (Convert.ToDateTime(tak[8]) > searchModel.EndDate.Value.Date ? searchModel.EndDate.Value.Date : Convert.ToDateTime(tak[8]))),
ScanCount = (int?)tak[9],
Container1 = (string)tak[10],
ContainerDescription = (string)tak[11],
}).ToList();
#endregion
#region
//var listDays = opList.Select(o => o.ScanDate).Distinct().OrderBy(o => o.Value).ToList();
//for (int i = 0; i < listDays.Count(); i++)
//{
// columns.Add(new GridColumnSettings
// {
// Member = "RowCellList[" + i + "].ScanCount",
// Title = listDays[i].Value.ToShortDateString(),
// Sortable = false
// });
//}
var minDate1 = searchModel.StartDate.Value;
var maxDate1 = searchModel.EndDate.Value;
int i = 0;
while (minDate1 <= maxDate1)
{
columns.Add(new GridColumnSettings
{
Member = "RowCellList[" + i + "].ScanCount",
Title = minDate1.ToShortDateString(),
Sortable = false
});
minDate1 = minDate1.AddDays(1);
}
IList<KanbanCard> kanbanCardList = new List<KanbanCard>();
#region body
var groups = opList.GroupBy(g => new { g.CardNo, g.OpRefSequence, g.Item, g.ReferenceItemCode, g.ItemDescription, g.OpRef, g.Qty, g.Container1, g.ContainerDescription }).ToList();
foreach (var g in groups)
{
KanbanCard k = new KanbanCard();
k.CardNo = g.Key.CardNo;
k.OpRefSequence = g.Key.OpRefSequence;
k.Item = g.Key.Item;
k.ReferenceItemCode = g.Key.ReferenceItemCode;
k.ItemDescription = g.Key.ItemDescription;
k.OpRef = g.Key.OpRef;
k.Qty = g.Key.Qty;
k.Container1 = g.Key.Container1;
k.ContainerDescription = g.Key.ContainerDescription;
List<com.Sconit.Entity.KB.RowCellK> rowList = new List<com.Sconit.Entity.KB.RowCellK>();
var minDate = searchModel.StartDate.Value;
var maxDate = searchModel.EndDate.Value;
while (minDate <= maxDate)
{
com.Sconit.Entity.KB.RowCellK rc = new com.Sconit.Entity.KB.RowCellK();
var currentKBs = g.Where(c => c.ScanDate == minDate);
var currentKB = currentKBs != null && currentKBs.Count() > 0 ? currentKBs.First() : new KanbanCard();
rc.ScanCount = currentKB.ScanCount.HasValue ? currentKB.ScanCount.Value : 0;
rowList.Add(rc);
minDate = minDate.AddDays(1);
}
k.RowCellList = rowList;
kanbanCardList.Add(k);
}
#endregion
ViewData["columns"] = columns.ToArray();
return PartialView(kanbanCardList);
#endregion
}
}
ViewData["columns"] = columns.ToArray();
return Json(null);
//return PartialView(new List<KanbanCard>());
}
private string PrepareTurnoverRateSearchStatement(KanbanCardSearchModel searchModel)
{
string sql = @"select c.CardNo,c.OpRefSeq,c.Item,c.RefItemCode,c.ItemDesc,c.Qty,c.opref,CONVERT(varchar(10), s.CreateDate, 121) as '扫描日期',case when CONVERT(varchar(10), s.CreateDate, 121) is null then 0 else COUNT(*) end as '扫描次数',det.Container,det.ContainerDesc
from KB_KanbanCard c
left join SCM_FlowDet det on det.Flow=c.Flow and det.Item=c.Item
left join KB_KanbanScan s on c.CardNo=s.CardNo
where 1=1 ";
if (!string.IsNullOrWhiteSpace(searchModel.Item))
{
sql += string.Format(" and c.Item='{0}' ", searchModel.Item);
}
if (!string.IsNullOrWhiteSpace(searchModel.BinTo))
{
sql += string.Format(" and c.opref='{0}' ", searchModel.BinTo);
}
if (!string.IsNullOrWhiteSpace(searchModel.CardNo))
{
sql += string.Format(" and c.CardNo='{0}' ", searchModel.CardNo);
}
if (!string.IsNullOrWhiteSpace(searchModel.OpRefSequence))
{
sql += string.Format(" and c.OpRefSeq='{0}' ", searchModel.OpRefSequence);
}
//sql += string.Format(" and ((s.CreateDate >= '{0}' and s.CreateDate <='{1}') or s.CreateDate is null) ", searchModel.StartDate,searchModel.EndDate);
//sql += string.Format(" ", );
return sql + " group by c.CardNo,c.OpRefSeq,c.Item,c.RefItemCode,c.ItemDesc,CONVERT(varchar(10), s.CreateDate, 121),c.Qty,c.opref,det.Container,det.ContainerDesc ";
}
public void ExportKanbanCard(KanbanCardSearchModel searchModel)
{
StringBuilder sb = new StringBuilder();
IList<object> param = new List<object>();
string selectStatement = " select k from KanbanCard as k where 1=1 ";
SecurityHelper.AddRegionPermissionStatement(ref selectStatement, "k", "Region");
sb.Append(selectStatement);
if (!string.IsNullOrEmpty(searchModel.Region))
{
// System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (!string.IsNullOrEmpty(searchModel.Region))
{
sb.Append(" and k.Region in (");
string[] regions = searchModel.Region.Split(',');
for (int ir = 0; ir < regions.Length; ir++)
{
sb.Append("?,");
param.Add(regions[ir]);
}
sb = sb.Remove(sb.Length - 1, 1);
sb.Append(")");
}
}
if (!string.IsNullOrWhiteSpace(searchModel.Supplier))
{
sb.Append(" and Supplier = ?");
param.Add(searchModel.Supplier);
}
if (!string.IsNullOrWhiteSpace(searchModel.Item))
{
sb.Append(" and Item = ?");
param.Add(searchModel.Item);
}
if (!string.IsNullOrWhiteSpace(searchModel.CardNo))
{
sb.Append(" and CardNo = ?");
param.Add(searchModel.CardNo);
}
if (searchModel.KBCalc != null)
{
sb.Append(" and KBCalc = ?");
param.Add(searchModel.KBCalc);
}
if (searchModel.FreezeDate != null)
{
sb.Append(" and FreezeDate <=?");
param.Add(searchModel.FreezeDate);
}
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
sb.Append(" and CreateDate between ? and ?");
param.Add(searchModel.StartDate);
param.Add(searchModel.EndDate);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
sb.Append(" and CreateDate >= ?");
param.Add(searchModel.StartDate);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
sb.Append(" and CreateDate <= ?");
param.Add(searchModel.EndDate);
}
IList<KanbanCard> kanbanCardList = this.genericMgr.FindAll<KanbanCard>(sb.ToString(), param.ToArray());
ExportToXLS<KanbanCard>("ExportKanbanCard", "xls", kanbanCardList);
}
public ActionResult TurnoverRateIndex(GridCommand command, KanbanCardSearchModel searchModel)
{
return View();
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, KanbanCardSearchModel searchModel)
{
string whereStatement = " where 1=1 ";
IList<object> param = new List<object>();
//区域权限
SecurityHelper.AddRegionPermissionStatement(ref whereStatement, "k", "Region");
if (!string.IsNullOrEmpty(searchModel.Region))
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (!string.IsNullOrEmpty(searchModel.Region))
{
sb.Append(" and k.Region in (");
string[] regions = searchModel.Region.Split(',');
for (int ir = 0; ir < regions.Length; ir++)
{
sb.Append("?,");
param.Add(regions[ir]);
}
sb = sb.Remove(sb.Length - 1, 1);
whereStatement += sb.ToString() + ")";
}
}
//HqlStatementHelper.AddEqStatement("Region", searchModel.Region, "k", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Supplier", searchModel.Supplier, "k", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "k", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("CardNo", searchModel.CardNo, "k", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("OpRef", searchModel.BinTo, HqlStatementHelper.LikeMatchMode.Start, "k", ref whereStatement, param);
//if (searchModel.KitCount != null && searchModel.KitCount.Value > 1)
//{
// HqlStatementHelper.AddEqStatement("KitCount", searchModel.KitCount.Value, "k", ref whereStatement, param);
//}
// HqlStatementHelper.AddEqStatement("NeedReprint", searchModel.NeedReprint, "k", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("OpRefSequence", searchModel.OpRefSequence, "k", ref whereStatement, param);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "k", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "k", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "k", ref whereStatement, param);
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
TempData["SortStatement"] = sortingStatement;
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountStatement;
searchStatementModel.SelectStatement = selectStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public string PrintcardNos(string cardNos, KanbanCardSearchModel searchModel)
{
IList<object> data = new List<object>();
IList<KanbanCard> kanbanCardList = null;
string sortStatement = TempData["SortStatement"] != null ? (string)TempData["SortStatement"] : " order by CreateDate asc ";
TempData["SortStatement"] = sortStatement;
try
{
if (!string.IsNullOrEmpty(cardNos))
{
//根据查询条件打印
if (cardNos.Equals("ALL", StringComparison.OrdinalIgnoreCase))
{
string whereStatement = " where 1=1 ";
IList<object> param = new List<object>();
//区域权限
SecurityHelper.AddRegionPermissionStatement(ref whereStatement, "k", "Region");
if (!string.IsNullOrEmpty(searchModel.Region))
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (!string.IsNullOrEmpty(searchModel.Region))
{
sb.Append(" and k.Region in (");
string[] regions = searchModel.Region.Split(',');
for (int ir = 0; ir < regions.Length; ir++)
{
sb.Append("?,");
param.Add(regions[ir]);
}
sb = sb.Remove(sb.Length - 1, 1);
whereStatement += sb.ToString() + ")";
}
}
HqlStatementHelper.AddEqStatement("Supplier", searchModel.Supplier, "k", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "k", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("CardNo", searchModel.CardNo, "k", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("OpRef", searchModel.BinTo, HqlStatementHelper.LikeMatchMode.Start, "k", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("OpRefSequence", searchModel.OpRefSequence, "k", ref whereStatement, param);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "k", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "k", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "k", ref whereStatement, param);
}
kanbanCardList = this.genericMgr.FindAll<KanbanCard>("from KanbanCard k " + whereStatement + sortStatement, param.ToArray());
}
//根据选择的行数打印
else
{
string[] array = cardNos.Split(',');
string selectStatement = string.Empty;
IList<object> selectPartyPara = new List<object>();
foreach (var para in array)
{
if (selectStatement == string.Empty)
{
selectStatement = " from KanbanCard where CardNo in (?";
}
else
{
selectStatement += ",?";
}
selectPartyPara.Add(para);
}
selectStatement += ")";
kanbanCardList = genericMgr.FindAll<KanbanCard>(selectStatement + sortStatement, selectPartyPara.ToArray());
}
}
else
{
throw new BusinessException("看板单号不能为空.");
}
string printUrl = string.Empty;
if (kanbanCardList.Count() > 0)
{
if (printUrl == string.Empty)
{
IList<PrintKanBanCard> printList = Mapper.Map<IList<KanbanCard>, IList<PrintKanBanCard>>(kanbanCardList);
data.Add(printList);
printUrl = reportGen.WriteToFile("KanBanCard.xls", data);
}
}
return printUrl;
}
catch (BusinessException ex)
{
SaveBusinessExceptionMessage(ex);
}
return string.Empty;
}
public ActionResult _AjaxList(GridCommand command, KanbanCardSearchModel 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<KanbanCard> gridModelKanbanCardList = GetAjaxPageData<KanbanCard>(searchStatementModel, command);
//if (searchModel.FreezeDate!=null && searchModel.FreezeDate.HasValue && gridModelKanbanCardList.Data != null && gridModelKanbanCardList.Data.Count() > 0)
//{
// foreach (KanbanCard kanbanCard in gridModelKanbanCardList.Data)
// {
// kanbanCard.IsFreeze = kanbanCard.FreezeDate <= searchModel.FreezeDate;
// }
//}
return PartialView(GetAjaxPageData<KanbanCard>(searchStatementModel, command));
}
public ActionResult List(GridCommand command, KanbanCardSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
return View();
}
public ActionResult _AjaxList(GridCommand command, KanbanCardSearchModel searchModel)
{
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
return PartialView(GetAjaxPageData<KanbanCard>(searchStatementModel, command));
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, KanbanCardSearchModel searchModel)
{
string whereStatement = string.Format(" where k.IsLost={0} ",true);
IList<object> param = new List<object>();
//区域权限
SecurityHelper.AddRegionPermissionStatement(ref whereStatement, "k", "Region");
if (!string.IsNullOrEmpty(searchModel.Region))
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (!string.IsNullOrEmpty(searchModel.Region))
{
sb.Append(" and k.Region in (");
string[] regions = searchModel.Region.Split(',');
for (int ir = 0; ir < regions.Length; ir++)
{
sb.Append("?,");
param.Add(regions[ir]);
}
sb = sb.Remove(sb.Length - 1, 1);
whereStatement += sb.ToString() + ")";
}
}
HqlStatementHelper.AddLikeStatement("BinTo", searchModel.BinTo, HqlStatementHelper.LikeMatchMode.Start, "k", 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 void ExportLostView(KanbanCardSearchModel searchModel)
{
IList<object> param = new List<object>();
string hql =string.Format( " select k from KanbanCard as k where IsLost={0} ",true);
SecurityHelper.AddRegionPermissionStatement(ref selectStatement, "k", "Region");
if (!string.IsNullOrEmpty(searchModel.Region))
{
// System.Text.StringBuilder sb = new System.Text.StringBuilder();
if (!string.IsNullOrEmpty(searchModel.Region))
{
hql+=" and k.Region in (";
string[] regions = searchModel.Region.Split(',');
for (int ir = 0; ir < regions.Length; ir++)
{
hql+="?,";
param.Add(regions[ir]);
}
hql = hql.Substring(0, hql.Length - 1) + ")";
}
}
if (!string.IsNullOrWhiteSpace(searchModel.BinTo))
{
hql += " and k.LocBin=? ";
param.Add(searchModel.BinTo);
}
IList<KanbanCard> kanbanCardList = this.genericMgr.FindAll<KanbanCard>(hql, param.ToArray());
ExportToXLS<KanbanCard>("ExportKanbanCard", "xls", kanbanCardList);
}