//[SconitAuthorize(Permissions = "Url_LocationIOB_View")] public ActionResult _AjaxList(GridCommand command, LocationTransactionSearchModel searchModel) { if (string.IsNullOrEmpty(searchModel.Location)) { return PartialView(new GridModel(new List<LocationTransactionView>())); } SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel); GridModel<string> itemModel = GetAjaxPageData<string>(searchStatementModel, command); #region 拼接库存收发存对象 IList<string> itemLocList = (IList<string>)itemModel.Data; string hql = "from LocationTransaction as l where l.Item in (?"; IList<object> param = new List<object>(); param.Add(itemLocList[0]); for (int i = 1; i < itemLocList.Count; i++) { hql += ",?"; param.Add(itemLocList[i]); } hql += ")"; hql += " and (l.LocationFrom = ? and l.LocationTo = ?)"; param.Add(searchModel.Location.Trim()); param.Add(searchModel.Location.Trim()); if (!string.IsNullOrEmpty(searchModel.Item)) { hql += " and l.Item = ?"; param.Add(searchModel.Item.Trim()); } IList<LocationTransaction> loctransList = genericMgr.FindAll<LocationTransaction>(hql, param.ToArray()); IList<LocationTransactionView> groupLoctransList = (from l in loctransList group l by new { Item = l.Item, Location = l.Location } into result select new LocationTransactionView { Item = result.Key.Item, GroupLocation = result.Key.Location, SumProcurementInQty = result.Sum(t => t.ProcurementInQty), SumProductionInQty = result.Sum(t => t.ProductionInQty), SumProductionOutQty = result.Sum(t => t.ProductionOutQty), SumDistributionOutQty = result.Sum(t => t.DistributionOutQty), SumTransferInQty = result.Sum(t => t.TransferInQty), SumTransferOutQty = result.Sum(t => t.TransferOutQty), }).ToList(); #endregion GridModel<LocationTransactionView> groupModel = new GridModel<LocationTransactionView>(); groupModel.Total = itemModel.Total; groupModel.Data = groupLoctransList; ViewBag.Total = groupModel.Total; return PartialView(groupModel); }
// [SconitAuthorize(Permissions = "Url_LocationIOB_View")] public ActionResult List(GridCommand command, LocationTransactionSearchModel searchModel) { TempData["LocationTransactionSearchModel"] = searchModel; if (string.IsNullOrEmpty(searchModel.Location)) { SaveWarningMessage(Resources.EXT.ControllerLan.Con_LocationCanNotBeEmpty); } return View(); }
// [SconitAuthorize(Permissions = "Url_LocationIOB_View")] public ActionResult List(GridCommand command, LocationTransactionSearchModel searchModel) { TempData["LocationTransactionSearchModel"] = searchModel; if (string.IsNullOrEmpty(searchModel.Location)) { SaveWarningMessage("库位不能为空"); } return View(); }
public ActionResult List(GridCommand command, LocationTransactionSearchModel searchModel) { SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel); if (this.CheckSearchModelIsNull(searchCacheModel.SearchObject)) { TempData["_AjaxMessage"] = ""; } else { SaveWarningMessage(Resources.ErrorMessage.Errors_NoConditions); } ViewBag.PageSize = this.ProcessPageSize(command.PageSize); return View(); }
public ActionResult _AjaxList(GridCommand command, LocationTransactionSearchModel searchModel) { if (!this.CheckSearchModelIsNull(searchModel)) { return PartialView(new GridModel(new List<LocationTransaction>())); } SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel); GridModel<LocationTransaction> gridModel = GetAjaxPageData<LocationTransaction>(searchStatementModel, command); var itemCategoryList = this.genericMgr.FindAll<ItemCategory>(); foreach (var listdata in gridModel.Data) { listdata.MaterialsGroup = itemMgr.GetCacheItem(listdata.Item).MaterialsGroup; listdata.MaterialsGroupDesc = GetItemCategory(listdata.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description; } return PartialView(gridModel); }
private SearchStatementModel PrepareSearchStatement(GridCommand command, LocationTransactionSearchModel searchModel) { string whereStatement = " where (l.LocationFrom = '" + searchModel.Location + "' or l.LocationTo = '" + searchModel.Location + "')"; IList<object> param = new List<object>(); // HqlStatementHelper.AddEqStatement("Location", searchModel.Location, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param); if (searchModel.StartDate != null & searchModel.EndDate != null) { HqlStatementHelper.AddBetweenStatement("EffectiveDate", searchModel.StartDate, searchModel.EndDate, "l", ref whereStatement, param); } else if (searchModel.StartDate != null & searchModel.EndDate == null) { HqlStatementHelper.AddGeStatement("EffectiveDate", searchModel.StartDate, "l", ref whereStatement, param); } else if (searchModel.StartDate == null & searchModel.EndDate != null) { HqlStatementHelper.AddLeStatement("EffectiveDate", searchModel.EndDate, "l", 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 _AjaxList(GridCommand command, LocationTransactionSearchModel searchModel) { if (!this.CheckSearchModelIsNull(searchModel)) { return PartialView(new GridModel(new List<LocationTransaction>())); } string sql = PrepareSqlSearchStatement(searchModel); int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + sql + ") as r1").First(); string sortingStatement = string.Empty; #region if (command.SortDescriptors.Count != 0) { if (command.SortDescriptors[0].Member == "TransactionTypeDescription") { command.SortDescriptors[0].Member = "TransType"; } else if (command.SortDescriptors[0].Member == "EffectiveDate") { command.SortDescriptors[0].Member = "EffDate"; } else if (command.SortDescriptors[0].Member == "ReceiptNo") { command.SortDescriptors[0].Member = "RecNo"; } else if (command.SortDescriptors[0].Member == "LocationFrom") { command.SortDescriptors[0].Member = "LocFrom"; } else if (command.SortDescriptors[0].Member == "LocationTo") { command.SortDescriptors[0].Member = "LocTo"; } else if (command.SortDescriptors[0].Member == "IOTypeDescription") { command.SortDescriptors[0].Member = "IOType"; } else if (command.SortDescriptors[0].Member == "CreateUserName") { command.SortDescriptors[0].Member = "CreateUserNm"; } else if (command.SortDescriptors[0].Member == "SapOrderNo") { command.SortDescriptors[0].Member = "ExtOrderNo"; } sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); TempData["sortingStatement"] = sortingStatement; } #endregion if (string.IsNullOrWhiteSpace(sortingStatement)) { sortingStatement = " order by EffDate desc"; } 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, command.PageSize*command.Page); IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql); IList<LocationTransaction> locationTransactionList = new List<LocationTransaction>(); if (searchResult != null && searchResult.Count > 0) { #region //lt.TransType,lt.EffDate,lt.OrderNo,lt.IpNo,lt.RecNo,lt.PartyFrom,lt.PartyTo,lt.Item,lt.IOType,lt.HuId,lt.LotNo,lt.Qty,au.CreateUserNm, //lt.CreateDate,om.ExtOrderNo,bp.Party,ba.Party locationTransactionList = (from tak in searchResult select new LocationTransaction { TransactionTypeDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.TransactionType, int.Parse((tak[1]).ToString())), EffectiveDate = (DateTime)tak[2], OrderNo = (string)tak[3], IpNo = (string)tak[4], ReceiptNo = (string)tak[5], PartyFrom = (string)tak[6], PartyTo = (string)tak[7], LocationFrom = (string)tak[8], LocationTo = (string)tak[9], Item = (string)tak[10], IOTypeDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.TransactionIOType, int.Parse((tak[11]).ToString())), HuId = (string)tak[12], LotNo = (string)tak[13], Qty = (decimal)tak[14], CreateUserName = (string)tak[15], CreateDate = (DateTime)tak[16], SapOrderNo = (string)tak[17], Supplier = tak[18] != null ? (string)tak[18] : string.Empty, ReserveNo = (string)tak[20], ReserveLine = (string)tak[21], }).ToList(); #endregion } GridModel<LocationTransaction> gridModel = new GridModel<LocationTransaction>(); gridModel.Total = total; gridModel.Data = locationTransactionList; return PartialView(gridModel); }
private string PrepareSqlSearchStatement(LocationTransactionSearchModel searchModel) { string whereStatement = @"select lt.TransType,lt.EffDate,lt.OrderNo,lt.IpNo,lt.RecNo,lt.PartyFrom,lt.PartyTo,lt.LocFrom,lt.LocTo, lt.Item,lt.IOType,lt.HuId,lt.LotNo,lt.Qty,(au.FirstName+au.LastName) as CreateUserNm,lt.CreateDate,om.ExtOrderNo,bp.Party ,lt.Id,md.ReserveNo,md.ReserveLine from VIEW_LocTrans as lt with(nolock) left join BIL_PlanBill as bp with(nolock) on lt.PlanBill=bp.Id left join ORD_OrderMstr_4 as om with(nolock) on lt.OrderNo=om.OrderNo left join ACC_User as au with(nolock) on lt.CreateUser=au.Id left join ORD_MiscOrderDet as md with(nolock) on lt.OrderDetId=md.Id where 1=1 "; //--left join ORD_MiscOrderMstr as mm with(nolock) on lt.OrderNo=mm.MiscOrderNo if (!string.IsNullOrEmpty(searchModel.ReserveNo)) { whereStatement += " and mm.ReserveNo = '" + searchModel.ReserveNo + "'"; } if (!string.IsNullOrEmpty(searchModel.ReserveLine)) { whereStatement += " and mm.ReserveLine = '" + searchModel.ReserveLine + "'"; } if (!string.IsNullOrEmpty(searchModel.PartyFrom)) { whereStatement += " and lt.PartyFrom = '" + searchModel.PartyFrom + "'"; } if (!string.IsNullOrEmpty(searchModel.CreateUserName)) { whereStatement += " and (au.FirstName+au.LastName) = '" + searchModel.CreateUserName + "'"; } if (!string.IsNullOrEmpty(searchModel.PartyTo)) { whereStatement += " and lt.PartyTo = '" + searchModel.PartyTo + "'"; } if (!string.IsNullOrEmpty(searchModel.LocationFrom)) { whereStatement += " and lt.LocFrom = '" + searchModel.LocationFrom + "'"; } if (!string.IsNullOrEmpty(searchModel.LocationTo)) { whereStatement += " and lt.LocTo = '" + searchModel.LocationTo + "'"; } if (!string.IsNullOrEmpty(searchModel.Item)) { whereStatement += " and lt.Item = '" + searchModel.Item + "'"; } if (!string.IsNullOrEmpty(searchModel.TransactionType)) { whereStatement += " and lt.TransType =" + searchModel.TransactionType; } if (!string.IsNullOrEmpty(searchModel.OrderNo)) { whereStatement += " and lt.OrderNo = '" + searchModel.OrderNo + "'"; } if (!string.IsNullOrEmpty(searchModel.ReceiptNo)) { whereStatement += " and lt.RecNo = '" + searchModel.ReceiptNo + "'"; } if (!string.IsNullOrEmpty(searchModel.IpNo)) { whereStatement += " and lt.IpNo = '" + searchModel.IpNo + "'"; } if (searchModel.StartDate != null & searchModel.EndDate != null) { whereStatement += string.Format(" and lt.CreateDate between '{0}' and '{1}' ", searchModel.StartDate, searchModel.EndDate); } else if (searchModel.StartDate != null & searchModel.EndDate == null) { whereStatement += " and lt.CreateDate >='" + searchModel.StartDate + "'"; } else if (searchModel.StartDate == null & searchModel.EndDate != null) { whereStatement += " and lt.CreateDate <= '" + searchModel.EndDate + "'"; } if (searchModel.EffeStartDate != null & searchModel.EffeEndDate != null) { whereStatement += string.Format(" and lt.EffDate between '{0}' and '{1}' ", searchModel.EffeStartDate.Value, searchModel.EffeEndDate.Value); } else if (searchModel.StartDate != null & searchModel.EndDate == null) { whereStatement += " and lt.EffDate >='" + searchModel.EffeStartDate + "'"; } else if (searchModel.StartDate == null & searchModel.EndDate != null) { whereStatement += " and lt.EffDate <= '" + searchModel.EffeEndDate + "'"; } return whereStatement; }
private SearchStatementModel PrepareSearchStatement(GridCommand command, LocationTransactionSearchModel searchModel) { string whereStatement = string.Empty; IList<object> param = new List<object>(); if (!string.IsNullOrEmpty(searchModel.CreateUserName)) { whereStatement = "where exists (select 1 from User as u where u.Id=l.CreateUserId and (u.FirstName+u.LastName) like '" + searchModel.CreateUserName + "%' )"; } HqlStatementHelper.AddEqStatement("PartyFrom", searchModel.PartyFrom, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("PartyTo", searchModel.PartyTo, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("LocationFrom", searchModel.LocationFrom, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("LocationTo", searchModel.LocationTo, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("TransactionType", searchModel.TransactionType, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("OrderNo", searchModel.OrderNo, "l", ref whereStatement, param); if (searchModel.StartDate != null & searchModel.EndDate != null) { HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "l", ref whereStatement, param); } else if (searchModel.StartDate != null & searchModel.EndDate == null) { HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "l", ref whereStatement, param); } else if (searchModel.StartDate == null & searchModel.EndDate != null) { HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "l", ref whereStatement, param); } string sortingStatement = " order by l.Id desc"; if (command.SortDescriptors.Count != 0) { if (command.SortDescriptors[0].Member == "IOTypeDescription") { command.SortDescriptors[0].Member = "IOType"; } else if (command.SortDescriptors[0].Member == "CreateUserName") { command.SortDescriptors[0].Member = "CreateUser"; } 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 ExportXLS(LocationTransactionSearchModel searchModel) { string sql = PrepareSqlSearchStatement(searchModel); string sortingStatement =TempData["sortingStatement"]!=null? TempData["sortingStatement"] as string:string.Empty; TempData["sortingStatement"] = sortingStatement; if (string.IsNullOrWhiteSpace(sortingStatement)) { sortingStatement = " order by lt.EffDate desc"; } IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql + sortingStatement); IList<LocationTransaction> locationTransactionList = new List<LocationTransaction>(); if (searchResult != null && searchResult.Count > 0) { #region //lt.TransType,lt.EffDate,lt.OrderNo,lt.IpNo,lt.RecNo,lt.PartyFrom,lt.PartyTo,lt.Item,lt.IOType,lt.HuId,lt.LotNo,lt.Qty,au.CreateUserNm, //lt.CreateDate,om.ExtOrderNo,bp.Party,ba.Party locationTransactionList = (from tak in searchResult select new LocationTransaction { TransactionTypeDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.TransactionType, int.Parse((tak[0]).ToString())), EffectiveDate = (DateTime)tak[1], OrderNo = (string)tak[2], IpNo = (string)tak[3], ReceiptNo = (string)tak[4], PartyFrom = (string)tak[5], PartyTo = (string)tak[6], LocationFrom = (string)tak[7], LocationTo = (string)tak[8], Item = (string)tak[9], IOTypeDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.TransactionIOType, int.Parse((tak[10]).ToString())), HuId = (string)tak[11], LotNo = (string)tak[12], Qty = (decimal)tak[13], CreateUserName = (string)tak[14], CreateDate = (DateTime)tak[15], SapOrderNo = (string)tak[16], Supplier = tak[17] != null ? (string)tak[17] : string.Empty, ReserveNo = (string)tak[19], ReserveLine = (string)tak[20], }).ToList(); #endregion } ExportToXLS<LocationTransaction>("ExportList", "xls", locationTransactionList); }
public void ExportXLS(LocationTransactionSearchModel searchModel) { int value = Convert.ToInt32(base.systemMgr.GetEntityPreferenceValue(EntityPreference.CodeEnum.MaxRowSizeOnPage)); GridCommand command = new GridCommand(); command.Page = 1; command.PageSize = !this.CheckSearchModelIsNull(searchModel) ? 0 : value; SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel); GridModel<LocationTransaction> gridModel = GetAjaxPageData<LocationTransaction>(searchStatementModel, command); var itemCategoryList = this.genericMgr.FindAll<ItemCategory>(); IList<string> hus= gridModel.Data.ToList().Where(p => p.TransactionType == com.Sconit.CodeMaster.TransactionType.RCT_PO || p.TransactionType == com.Sconit.CodeMaster.TransactionType.RCT_IIC_VOID).Where(p=>!string.IsNullOrWhiteSpace(p.HuId)).Select(p=>p.HuId).Distinct().ToList(); IList<Hu> huIds=new List<Hu>(); if (hus.Count > 0) { huIds = genericMgr.FindAllIn<Hu>(" from Hu where HuId in (? ", hus).Where(p => !string.IsNullOrWhiteSpace(p.SupplierLotNo)).ToList(); } var huDic = huIds.ToDictionary(d => d.HuId, d => d.SupplierLotNo); foreach (var listdata in gridModel.Data) { if (huDic.Count > 0 && !string.IsNullOrWhiteSpace(listdata.HuId)) { listdata.SupplierLotNo = huDic.ValueOrDefault(listdata.HuId); } listdata.MaterialsGroup = itemMgr.GetCacheItem(listdata.Item).MaterialsGroup; listdata.MaterialsGroupDesc = GetItemCategory(listdata.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description; } ExportToXLS<LocationTransaction>("LocationTransReport.xls", gridModel.Data.ToList()); }
private SearchStatementModel PrepareSearchStatement(GridCommand command, LocationTransactionSearchModel searchModel) { string whereStatement = string.Empty; IList<object> param = new List<object>(); HqlStatementHelper.AddEqStatement("PartyFrom", searchModel.PartyFrom, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("PartyTo", searchModel.PartyTo, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("LocationFrom", searchModel.LocationFrom, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("LocationTo", searchModel.LocationTo, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param); HqlStatementHelper.AddEqStatement("TransactionType", searchModel.TransactionType, "l", ref whereStatement, param); string timeType = searchModel.TimeType == 1 ? "CreateDate" : "EffectiveDate"; if (searchModel.StartDate != null & searchModel.EndDate != null) { HqlStatementHelper.AddBetweenStatement(timeType, searchModel.StartDate, searchModel.EndDate, "l", ref whereStatement, param); } else if (searchModel.StartDate != null & searchModel.EndDate == null) { HqlStatementHelper.AddGeStatement(timeType, searchModel.StartDate, "l", ref whereStatement, param); } else if (searchModel.StartDate == null & searchModel.EndDate != null) { HqlStatementHelper.AddLeStatement(timeType, searchModel.EndDate, "l", ref whereStatement, param); } string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); if (command.SortDescriptors.Count == 0) { sortingStatement = " order by l."+timeType+" desc"; } SearchStatementModel searchStatementModel = new SearchStatementModel(); searchStatementModel.SelectCountStatement = selectCountStatement; searchStatementModel.SelectStatement = selectStatement; searchStatementModel.WhereStatement = whereStatement; searchStatementModel.SortingStatement = sortingStatement; searchStatementModel.Parameters = param.ToArray<object>(); return searchStatementModel; }
public JsonResult CheckExportQty(LocationTransactionSearchModel searchModel) { int value = Convert.ToInt32(base.systemMgr.GetEntityPreferenceValue(EntityPreference.CodeEnum.MaxRowSizeOnPage)); GridCommand command = new GridCommand(); command.Page = 1; command.PageSize = !this.CheckSearchModelIsNull(searchModel) ? 0 : value; SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel); GridModel<LocationTransaction> gridModel = GetAjaxPageData<LocationTransaction>(searchStatementModel, command); var message = "OK"; if (value < gridModel.Total) { message = string.Format(Resources.EXT.ControllerLan.Con_ExportUnsuccessfully, value); } return Json(new { Message = message }); }