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