public ActionResult DetailList(GridCommand GridCommand, MiscOrderSearchModel searchModel) { SearchCacheModel searchCacheModel = this.ProcessSearchModel(GridCommand, searchModel); if (this.CheckSearchModelIsNull(searchCacheModel.SearchObject)) { } else { SaveWarningMessage(Resources.SYS.ErrorMessage.Errors_NoConditions); } if (searchCacheModel.isBack == true) { ViewBag.Page = searchCacheModel.Command.Page == 0 ? 1 : searchCacheModel.Command.Page; } ViewBag.PageSize = base.ProcessPageSize(GridCommand.PageSize); return View(); }
public ActionResult _AjaxList(GridCommand command, MiscOrderSearchModel searchModel) { this.GetCommand(ref command, searchModel); if (!this.CheckSearchModelIsNull(searchModel)) { return PartialView(new GridModel(new List<MiscOrderMaster>())); } SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel); return PartialView(GetAjaxPageData<MiscOrderMaster>(searchStatementModel, command)); }
public void ExportDet(MiscOrderSearchModel 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; this.GetCommand(ref command, searchModel); if (!this.CheckSearchModelIsNull(searchModel)) { return; } string sql = PrepareSqlSearchStatement(searchModel); int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + sql + ") as r1").First(); string sortingStatement = string.Empty; #region sort condition if (command.SortDescriptors.Count != 0) { if (command.SortDescriptors[0].Member == "CreateDate") { command.SortDescriptors[0].Member = "CreateDate"; } else if (command.SortDescriptors[0].Member == "Item") { command.SortDescriptors[0].Member = "Item"; } else if (command.SortDescriptors[0].Member == "MiscOrderNo") { command.SortDescriptors[0].Member = "MiscOrderNo"; } else if (command.SortDescriptors[0].Member == "Location") { command.SortDescriptors[0].Member = "Location"; } else if (command.SortDescriptors[0].Member == "Qty") { command.SortDescriptors[0].Member = "Qty"; } sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); TempData["sortingStatement"] = sortingStatement; } #endregion if (string.IsNullOrWhiteSpace(sortingStatement)) { sortingStatement = " order by CreateDate,MiscOrderNo"; } 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); IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql); IList<MiscOrderDetail> MiscOrderDetailList = new List<MiscOrderDetail>(); if (searchResult != null && searchResult.Count > 0) { #region transform MiscOrderDetailList = (from tak in searchResult select new MiscOrderDetail { BaseUom = (string)tak[1], CreateDate = (DateTime)tak[2], CreateUserId = (int)tak[3], CreateUserName = (string)tak[4], EBELN = (string)tak[5], EBELP = (string)tak[6], Item = (string)tak[7], ItemDescription = (string)tak[8], LastModifyDate = (DateTime)tak[9], LastModifyUserId = (int)tak[10], LastModifyUserName = (string)tak[11], Location = (string)tak[12], ManufactureParty = (string)tak[13], MiscOrderNo = (string)tak[14], Qty = (decimal)tak[15], ReferenceItemCode = (string)tak[16], Remark = (string)tak[17], ReserveLine = (string)tak[18], ReserveNo = (string)tak[19], Sequence = (int)tak[20], UnitCount = (decimal)tak[21], UnitQty = (decimal)tak[22], Uom = (string)tak[23], WMSSeq = (string)tak[24], WorkHour = (decimal)tak[25], Note = (string)tak[26], EffectiveDate = (DateTime)tak[27], MoveType = (string)tak[28], Flow = (string)tak[29], WBS = (string)tak[30], }).ToList(); #endregion } GridModel<MiscOrderDetail> gridModel = new GridModel<MiscOrderDetail>(); gridModel.Total = total; gridModel.Data = MiscOrderDetailList; ExportToXLS<MiscOrderDetail>("ProductionAdjustMiscOrderDet.xls", gridModel.Data.ToList()); }
private string PrepareSqlSearchStatement(MiscOrderSearchModel searchModel) { string whereStatement = @" select d.BaseUom,d.CreateDate,d.CreateUser,d.CreateUserNm,d.EBELN,d.EBELP,d.Item, i.Desc1 As ItemDescription,d.LastModifyDate,d.LastModifyUser,d.LastModifyUserNm,m.Location,d.ManufactureParty,d.MiscOrderNo,d.Qty ,i.RefCode As ReferenceItemCode,d.Remark,d.ReserveLine,d.ReserveNo,d.Seq,d.UC As UnitCount,d.UnitQty,d.Uom,d.WMSSeq,d.WorkHour ,m.Note,m.effDate,m.MoveType,m.Flow,m.WBS from ORD_MiscOrderDet d with(nolock) inner join ORD_MiscOrderMstr m with(nolock) on d.MiscOrderNo=m.MiscOrderNo inner join MD_Item i On d.Item =i.Code where 1=1 "; if (!string.IsNullOrEmpty(searchModel.MiscOrderNo)) { whereStatement += string.Format(" and m.MiscOrderNo = '{0}'", searchModel.MiscOrderNo); } if (!string.IsNullOrEmpty(searchModel.CostCenter)) { whereStatement += string.Format(" and m.CostCenter = '{0}'", searchModel.CostCenter); } if (!string.IsNullOrEmpty(searchModel.CreateUserName)) { whereStatement += string.Format(" and d.CreateUserNm = '{0}'", searchModel.CreateUserName); } if (!string.IsNullOrEmpty(searchModel.Item)) { whereStatement += string.Format(" and d.Item = '{0}'", searchModel.Item); } if (!string.IsNullOrEmpty(searchModel.Flow)) { whereStatement += string.Format(" and m.Flow = '{0}'", searchModel.Flow); } if (!string.IsNullOrEmpty(searchModel.Note)) { whereStatement += string.Format(" and m.Note = '{0}'", searchModel.Note); } if (searchModel.Location != null) { whereStatement += string.Format(" and m.Location ={0}", searchModel.Location); } if (searchModel.MoveType != null) { whereStatement += string.Format(" and m.MoveType ={0}", searchModel.MoveType); } if (searchModel.GeneralLedger != null) { whereStatement += string.Format(" and m.GeneralLedger ={0}", searchModel.GeneralLedger); } if (searchModel.Status != null) { whereStatement += string.Format(" and m.Status ={0}", searchModel.Status); } whereStatement += string.Format(" and m.SubType ={0}", (int)CodeMaster.MiscOrderSubType.SY05); if (searchModel.StartDate != null && searchModel.EndDate != null) { whereStatement += string.Format(" and m.CreateDate between '{0}' and '{1}' ", searchModel.StartDate, searchModel.EndDate); } else if (searchModel.StartDate != null && searchModel.EndDate == null) { whereStatement += " and m.CreateDate >='" + searchModel.StartDate + "'"; } else if (searchModel.StartDate == null && searchModel.EndDate != null) { whereStatement += " and m.CreateDate <= '" + searchModel.EndDate + "'"; } return whereStatement; }
public void ExportMstr(MiscOrderSearchModel 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; this.GetCommand(ref command, searchModel); if (!this.CheckSearchModelIsNull(searchModel)) { return; } SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel); ExportToXLS<MiscOrderMaster>("ProductionAdjustMiscOrder.xls", GetAjaxPageData<MiscOrderMaster>(searchStatementModel, command).Data.ToList()); }
private SearchStatementModel PrepareSearchStatement(GridCommand command, MiscOrderSearchModel searchModel) { string whereStatement = string.Format("where m.SubType={0} ", (int)CodeMaster.MiscOrderSubType.SY05); IList<object> param = new List<object>(); HqlStatementHelper.AddLikeStatement("MiscOrderNo", searchModel.MiscOrderNo, HqlStatementHelper.LikeMatchMode.Anywhere, "m", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Status", searchModel.Status, "m", ref whereStatement, param); HqlStatementHelper.AddEqStatement("MoveType", searchModel.MoveType, "m", ref whereStatement, param); HqlStatementHelper.AddEqStatement("Location", searchModel.Location, "m", ref whereStatement, param); HqlStatementHelper.AddLikeStatement("Note", searchModel.Note, HqlStatementHelper.LikeMatchMode.Anywhere, "m", ref whereStatement, param); //HqlStatementHelper.AddLikeStatement("Remarks3", searchModel.Remarks3, HqlStatementHelper.LikeMatchMode.Anywhere, "m", ref whereStatement, param); //HqlStatementHelper.AddLikeStatement("Remarks4", searchModel.Remarks4, HqlStatementHelper.LikeMatchMode.Anywhere, "m", ref whereStatement, param); HqlStatementHelper.AddEqStatement("CreateUserName", searchModel.CreateUserName, "m", ref whereStatement, param); if (searchModel.StartDate != null & searchModel.EndDate != null) { HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "m", ref whereStatement, param); } else if (searchModel.StartDate != null & searchModel.EndDate == null) { HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "m", ref whereStatement, param); } else if (searchModel.StartDate == null & searchModel.EndDate != null) { HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "m", ref whereStatement, param); } if (command.SortDescriptors.Count > 0) { if (command.SortDescriptors[0].Member == "StatusDescription") { command.SortDescriptors[0].Member = "Status"; } } string sortingStatement = string.Empty; if (command.SortDescriptors.Count == 0) { sortingStatement = " order by CreateDate desc"; } else { 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 _AjaxDetailList(GridCommand command, MiscOrderSearchModel searchModel) { 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 == "CreateDate") { command.SortDescriptors[0].Member = "CreateDate"; } else if (command.SortDescriptors[0].Member == "Item") { command.SortDescriptors[0].Member = "Item"; } else if (command.SortDescriptors[0].Member == "MiscOrderNo") { command.SortDescriptors[0].Member = "MiscOrderNo"; } else if (command.SortDescriptors[0].Member == "Location") { command.SortDescriptors[0].Member = "Location"; } else if (command.SortDescriptors[0].Member == "Qty") { command.SortDescriptors[0].Member = "Qty"; } sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); TempData["sortingStatement"] = sortingStatement; } #endregion if (string.IsNullOrWhiteSpace(sortingStatement)) { sortingStatement = " order by MiscOrderNo"; } 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); IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql); IList<MiscOrderDetail> MiscOrderDetailList = new List<MiscOrderDetail>(); if (searchResult != null && searchResult.Count > 0) { #region MiscOrderDetailList = (from tak in searchResult select new MiscOrderDetail { BaseUom = (string)tak[1], CreateDate = (DateTime)tak[2], CreateUserId = (int)tak[3], CreateUserName = (string)tak[4], EBELN = (string)tak[5], EBELP = (string)tak[6], Item = (string)tak[7], ItemDescription = (string)tak[8], LastModifyDate = (DateTime)tak[9], LastModifyUserId = (int)tak[10], LastModifyUserName = (string)tak[11], Location = (string)tak[12], ManufactureParty = (string)tak[13], MiscOrderNo = (string)tak[14], Qty = (decimal)tak[15], ReferenceItemCode = (string)tak[16], Remark = (string)tak[17], ReserveLine = (string)tak[18], ReserveNo = (string)tak[19], Sequence = (int)tak[20], UnitCount = (decimal)tak[21], UnitQty = (decimal)tak[22], Uom = (string)tak[23], WMSSeq = (string)tak[24], WorkHour = (decimal)tak[25], Note = (string)tak[26], EffectiveDate = (DateTime)tak[27], MoveType = (string)tak[28], Flow = (string)tak[29], WBS = (string)tak[30], }).ToList(); #endregion } GridModel<MiscOrderDetail> gridModel = new GridModel<MiscOrderDetail>(); gridModel.Total = total; gridModel.Data = MiscOrderDetailList; return PartialView(gridModel); }