public ActionResult _AjaxList(GridCommand command, HuTransSearchModel searchModel) { if (string.IsNullOrWhiteSpace(searchModel.Item)) { searchModel.Item = ""; } if (string.IsNullOrWhiteSpace(searchModel.Location)) { searchModel.Location = ""; } TempData["GridCommand"] = command; TempData["searchModel"] = searchModel; string isIncludeAllSelect = ""; if (!searchModel.IsIncludeAll) { isIncludeAllSelect = "(a.ExpireDate <GETDATE() and a.ExpireDate is not null) and"; } string sql = @"Select a.HuId,b.Qty,a.Item ,a.RefItemCode As ReferenceItemCode,a.ItemDesc As ItemDescription , a.LotNo,b.Location,a.UC As UnitCount,a.Uom, dbo.FormatDate(a.ExpireDate,'YYYY-MM-DD') As ExpireDateValue, Case when a.RemindExpireDate is null then '' else dbo.FormatDate(a.RemindExpireDate,'YYYY-MM-DD') End As RemindExpireDateValue from INV_Hu a ,VIEW_LocationLotDet b where a.HuId =b.HuId and " + isIncludeAllSelect + " a.HuId is not null and " + "a.Item like '" + searchModel.Item + "%' and b.Location like '" + searchModel.Location + "%'and a.LotNo between '" + searchModel.LotNo + "' and '" + searchModel.LotNoTo + "'"; int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + sql + ") as r1").First(); string sortingStatement = " Order by r1.Location,r1.Item"; sql = 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, sql, (command.Page - 1) * command.PageSize + 1, command.PageSize * command.Page); var ds = this.genericMgr.GetDatasetBySql(sql, null); var itemCategoryList = this.genericMgr.FindAll<ItemCategory>(); IList<Hu> huTypeList = Utility.IListHelper.DataTableToList<Hu>(ds.Tables[0]); foreach (var huData in huTypeList) { huData.MaterialsGroup = itemMgr.GetCacheItem(huData.Item).MaterialsGroup; huData.MaterialsGroupDesc = GetItemCategory(huData.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description; } GridModel<Hu> gridModel = new GridModel<Hu>(); gridModel.Total = total; gridModel.Data = huTypeList; return PartialView(gridModel); }
public ActionResult List(GridCommand command, HuTransSearchModel searchModel) { SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel); ViewBag.PageSize = base.ProcessPageSize(command.PageSize); return View(); }
public void Export(HuTransSearchModel searchModel) { int value = Convert.ToInt32(base.systemMgr.GetEntityPreferenceValue(EntityPreference.CodeEnum.MaxRowSizeOnPage)); GridCommand command = new GridCommand(); command.Page = 1; command.PageSize = value; SearchStatementModel searchStatementModel = this.PrepareHuTransSearchStatement(command, searchModel); GridModel<FreezeTransaction> List = new GridModel<FreezeTransaction>(); GridModel<PickTransaction> List1 = new GridModel<PickTransaction>(); GridModel<HuIdEpDateChangeTransaction> List2 = new GridModel<HuIdEpDateChangeTransaction>(); var itemCategoryList = this.genericMgr.FindAll<ItemCategory>(); if (searchModel.SearchType == "Freeze") { List = GetAjaxPageData<FreezeTransaction>(searchStatementModel, command); foreach (var ListData in List.Data) { if (ListData.Freeze) { ListData.HuAction = Resources.EXT.ControllerLan.Con_Freeze; } else { ListData.HuAction = Resources.EXT.ControllerLan.Con_UnFreeze; } ListData.ItemDescription = itemMgr.GetCacheItem(ListData.Item).FullDescription; ListData.MaterialsGroup = itemMgr.GetCacheItem(ListData.Item).MaterialsGroup; ListData.MaterialsGroupDesc = GetItemCategory(ListData.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description; } } else if (searchModel.SearchType == "Pick") { List1 = GetAjaxPageData<PickTransaction>(searchStatementModel, command); foreach (var ListData in List1.Data) { if (ListData.IsPick) { ListData.HuAction = Resources.EXT.ControllerLan.Con_UnPick; } else { ListData.HuAction = Resources.EXT.ControllerLan.Con_Pick; } ListData.ItemDescription = itemMgr.GetCacheItem(ListData.Item).FullDescription; ListData.MaterialsGroup = itemMgr.GetCacheItem(ListData.Item).MaterialsGroup; ListData.MaterialsGroupDesc = GetItemCategory(ListData.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description; } } else if (searchModel.SearchType == "EpChange") { List2 = GetAjaxPageData<HuIdEpDateChangeTransaction>(searchStatementModel, command); foreach (var ListData in List2.Data) { ListData.ItemDescription = itemMgr.GetCacheItem(ListData.Item).FullDescription; ListData.MaterialsGroup = itemMgr.GetCacheItem(ListData.Item).MaterialsGroup; ListData.MaterialsGroupDesc = GetItemCategory(ListData.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description; } } var fileName = string.Format("{0}Trans.xls", searchModel.SearchType); if (searchModel.SearchType == "Freeze") { ExportToXLS<FreezeTransaction>(fileName, List.Data.ToList()); } else if (searchModel.SearchType == "Pick") { ExportToXLS<PickTransaction>(fileName, List1.Data.ToList()); } else { ExportToXLS<HuIdEpDateChangeTransaction>(fileName, List2.Data.ToList()); } }
public ActionResult _AjaxEpChangeTransactionList(GridCommand command, HuTransSearchModel searchModel) { SearchStatementModel searchStatementModel = this.PrepareHuTransSearchStatement(command, searchModel); GridModel<HuIdEpDateChangeTransaction> List = GetAjaxPageData<HuIdEpDateChangeTransaction>(searchStatementModel, command); var itemCategoryList = this.genericMgr.FindAll<ItemCategory>(); foreach (var ListData in List.Data) { ListData.ItemDescription = itemMgr.GetCacheItem(ListData.Item).FullDescription; ListData.MaterialsGroup = itemMgr.GetCacheItem(ListData.Item).MaterialsGroup; ListData.MaterialsGroupDesc = GetItemCategory(ListData.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description; } return PartialView(List); }
public ActionResult _AjaxFreezeTransactionList(GridCommand command, HuTransSearchModel searchModel) { SearchStatementModel searchStatementModel = this.PrepareHuTransSearchStatement(command, searchModel); GridModel<FreezeTransaction> List = GetAjaxPageData<FreezeTransaction>(searchStatementModel, command); var itemCategoryList = this.genericMgr.FindAll<ItemCategory>(); foreach (var ListData in List.Data) { if (ListData.Freeze) { ListData.HuAction = Resources.EXT.ControllerLan.Con_Freeze; } else { ListData.HuAction = Resources.EXT.ControllerLan.Con_UnFreeze; } ListData.ItemDescription = itemMgr.GetCacheItem(ListData.Item).FullDescription; ListData.MaterialsGroup = itemMgr.GetCacheItem(ListData.Item).MaterialsGroup; ListData.MaterialsGroupDesc = GetItemCategory(ListData.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description; } return PartialView(List); }
public ActionResult _FreezeTrans(GridCommand command, HuTransSearchModel searchModel) { ViewBag.Item = searchModel.Item; ViewBag.Location = searchModel.Location; ViewBag.Bin = searchModel.Bin; ViewBag.HuId = searchModel.HuId; ViewBag.LotNo = searchModel.LotNo; ViewBag.LotNoTo = searchModel.LotNoTo; ViewBag.StartDate = searchModel.StartDate; ViewBag.EndDate = searchModel.EndDate; ViewBag.SearchType = searchModel.SearchType; ViewBag.SearchModel = searchModel; ViewBag.PageSize = this.ProcessPageSize(command.PageSize); return PartialView(); }
private SearchStatementModel PrepareHuTransSearchStatement(GridCommand command, HuTransSearchModel searchModel) { string whereStatement = "where 1=1"; IList<object> param = new List<object>(); HqlStatementHelper.AddEqStatement("Location", searchModel.Location, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Bin", searchModel.Bin, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("HuId", searchModel.HuId, HqlStatementHelper.LikeMatchMode.End, "l", ref whereStatement, param); if (searchModel.LotNo != null & searchModel.LotNoTo != null) { HqlStatementHelper.AddBetweenStatement("LotNo", searchModel.LotNo, searchModel.LotNoTo, "l", ref whereStatement, param); } else if (searchModel.LotNo != null & searchModel.LotNoTo == null) { HqlStatementHelper.AddGeStatement("LotNo", searchModel.LotNo, "l", ref whereStatement, param); } else if (searchModel.LotNo == null & searchModel.LotNoTo != null) { HqlStatementHelper.AddLeStatement("LotNo", searchModel.LotNoTo, "l", ref whereStatement, param); } HqlStatementHelper.AddEqStatement("CreateUserName", searchModel.CreateUserName, "l", ref whereStatement, param); if (searchModel.StartDate != null) { HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "l", ref whereStatement, param); } string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); if (command.SortDescriptors.Count == 0) { sortingStatement = " order by l.CreateDate desc"; } SearchStatementModel searchStatementModel = new SearchStatementModel(); searchStatementModel.SelectCountStatement = selectHuTransCountStatement; searchStatementModel.SelectStatement = selectHuTransStatement; if (searchModel.SearchType == "Freeze") { searchStatementModel.SelectCountStatement = selectHuTransCountStatement.Replace("PickTransaction", "FreezeTransaction"); searchStatementModel.SelectStatement = selectHuTransStatement.Replace("PickTransaction", "FreezeTransaction"); } else if (searchModel.SearchType == "EpChange") { searchStatementModel.SelectCountStatement = selectHuTransCountStatement.Replace("PickTransaction", "HuIdEpDateChangeTransaction"); searchStatementModel.SelectStatement = selectHuTransStatement.Replace("PickTransaction", "HuIdEpDateChangeTransaction"); } searchStatementModel.WhereStatement = whereStatement; searchStatementModel.SortingStatement = sortingStatement; searchStatementModel.Parameters = param.ToArray<object>(); return searchStatementModel; }