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