private SearchNativeSqlStatementModel PrepareSearchStatement(GridCommand command, CabProductionViewSearchModel searchModel) { string statement = selectStatement; if (!string.IsNullOrWhiteSpace(searchModel.Flow)) { statement += string.Format(" and m.Flow = '{0}'", searchModel.Flow); } if (searchModel.Type != null) { statement += string.Format(" and m.Type = {0}", searchModel.Type); } if (searchModel.IsOut) { statement += " and exists(select 1 from ORD_OrderMstr_2 m2 where m2.OrderNo = m.ExtOrderNo)"; } string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); if (string.IsNullOrEmpty(sortingStatement)) { sortingStatement = " order by ExtSeq asc"; } SearchNativeSqlStatementModel searchStatementModel = new SearchNativeSqlStatementModel(); searchStatementModel.SelectSql = statement; searchStatementModel.SortingStatement = sortingStatement; return searchStatementModel; }
private SearchNativeSqlStatementModel PrepareSearchStatement(GridCommand command, CabProductionViewSearchModel searchModel) { string statement = selectStatement; if (!string.IsNullOrWhiteSpace(searchModel.Flow)) { statement += string.Format(" and mstr.Flow = '{0}'", searchModel.Flow); } //驾驶室已出库记录覆盖时长 int coveredDays = int.Parse(systemMgr.GetEntityPreferenceValue(com.Sconit.Entity.SYS.EntityPreference.CodeEnum.CoveredDaysOfCabOut)); statement += string.Format(" and co.CabType = {0} and co.Status in ({1})", (int)searchModel.Type, searchModel.IsOut ? "1,2" : "0"); if (searchModel.IsOut) statement += string.Format(" and co.OutDate Between '" + DateTime.Today.AddDays(1 - coveredDays) + "' and '" + DateTime.Today.AddDays(1) + "'"); if (command.SortDescriptors != null && command.SortDescriptors.Count > 0) { if (command.SortDescriptors[0].Member == "CabOutStatusDescription") { command.SortDescriptors[0].Member = "CabOutStatus"; } } string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); if (string.IsNullOrEmpty(sortingStatement)) { sortingStatement = " order by StartTime asc"; } SearchNativeSqlStatementModel searchStatementModel = new SearchNativeSqlStatementModel(); searchStatementModel.SelectSql = statement; searchStatementModel.SortingStatement = sortingStatement; return searchStatementModel; }
/// <summary> /// Search Statement /// </summary> /// <param name="command">Telerik GridCommand</param> /// <param name="searchModel">AliquotStartTask Search Model</param> /// <returns>return AliquotStartTask search model</returns> private SearchNativeSqlStatementModel PrepareStartSearchStatement(GridCommand command, AliquotStartTaskSearchModel searchModel) { int coveredDays = int.Parse(systemMgr.GetEntityPreferenceValue(com.Sconit.Entity.SYS.EntityPreference.CodeEnum.CoveredDaysOfKitProduction)); var selectStatement = string.Empty; if (searchModel.SearchIsStart) { selectStatement = " select m.OrderNo,m.Flow,m.TraceCode,d.Item,d.ItemDesc,t.StartTime from ORD_AliquotStartTask t WITH(NOLOCK) " + " inner join ORD_OrderMstr_4 m WITH(NOLOCK) on t.OrderNo=m.OrderNo inner join ORD_OrderDet_4 d WITH(NOLOCK) on m.Orderno=d.OrderNo" + " where t.Flow='" + searchModel.SearchFlow + "' and t.StartTime Between '" + DateTime.Today.AddDays(1 - coveredDays) + "' and '" + DateTime.Today.AddDays(1) + "'"; } else { selectStatement = "select m.OrderNo,m.Flow,m.TraceCode,d.Item,d.ItemDesc,Min(p.CPTime) as StartTime from ORD_OrderOpCPTime p WITH(NOLOCK) " + " inner join ORD_OrderMstr_4 m WITH(NOLOCK) on p.OrderNo=m.OrderNo inner join ORD_OrderDet_4 d WITH(NOLOCK) on m.OrderNo=d.OrderNo" + " where not exists(select 1 from ORD_AliquotStartTask t WITH(NOLOCK) where t.Flow=p.AssProdLine and m.TraceCode =t.VanNo and m.OrderNo=t.OrderNo) " + " and p.AssProdLine ='" + searchModel.SearchFlow + "'"; if (!string.IsNullOrWhiteSpace(searchModel.SearchVanFlow)) selectStatement += string.Format(" and p.VanProdLine ='{0}'", searchModel.SearchVanFlow); selectStatement += " group by m.OrderNo,m.Flow,m.TraceCode,d.Item,d.ItemDesc"; } string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors); if (string.IsNullOrWhiteSpace(sortingStatement)) { if (!searchModel.SearchIsStart) sortingStatement = " order by StartTime asc "; else sortingStatement = " order by StartTime desc "; } SearchNativeSqlStatementModel searchStatementModel = new SearchNativeSqlStatementModel(); searchStatementModel.SelectSql = selectStatement; searchStatementModel.SortingStatement = sortingStatement; return searchStatementModel; }