public void Given_NullAsProjection_When_AskedToTransformOrders_OrderHelperThrowsArgNullException() { var statements = new OrderByStatement[0]; var data = new QueryHelperData(new Dictionary <string, string>(), new List <Join>()); Assert.That(() => OrderHelper.GetSourceBasedOrdersFrom(statements, null, data), Throws.InstanceOf <ArgumentNullException>()); }
private string CreateOrderByString(OrderByStatement orderByStatement, string tableName, Func<string, string> escape) { var direction = orderByStatement.Direction == OrderByDirection.Ascending ? "" : " DESC"; var columnReference = new ColumnReference(orderByStatement.PropertyName, tableName, escape); return columnReference + direction; }
/// <inheritdoc /> public ISelectStringBuilder OrderBy(string columnName) { var clause = new OrderByClause(columnName); OrderByStatement.Add(clause); return(this); }
/// <inheritdoc /> public ISelectStringBuilder OrderBy(string columnName, SortingOperator @operator) { var clause = new OrderByClause(columnName, @operator); OrderByStatement.Add(clause); return(this); }
private string CreateOrderByString(OrderByStatement orderByStatement, string tableName, Func <string, string> escape) { var direction = orderByStatement.Direction == OrderByDirection.Ascending ? "" : " DESC"; var columnReference = new ColumnReference(orderByStatement.PropertyName, tableName, escape); return(columnReference + direction); }
[Test] public void Get_WhenCalledWithOrder_ShouldReturnList() { //Arrange IEnumerable <EventModel> expectedList = TestsFacade.EventsFacade.BuildEventModelList(10); OrderByStatement <EventModel> orderByStatement = TestsFacade.OrderBytatementFacade.BuildOrderByStatement <EventModel>(); var repositoryMock = _mock.Mock <IGenericRepository <EventModel> >(); repositoryMock .Setup(items => items.Get(null, It.IsAny <Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> > >(), "")) .Returns(() => expectedList); GenericService <EventModel> service = _mock.Create <GenericService <EventModel> >(); //Act ResultService <IEnumerable <EventModel> > result = service.Get(null, orderByStatement, ""); //Assert Assert.IsNotNull(result); Assert.IsInstanceOf <ResultService <IEnumerable <EventModel> > >(result); Assert.IsTrue(result.Success); Assert.IsInstanceOf <IEnumerable <EventModel> >(result.Value); IEnumerable <EventModel> resultList = result.Value; Assert.IsNotNull(resultList); Assert.AreEqual(resultList.Count(), expectedList.Count()); Assert.AreEqual(resultList, expectedList); }
public virtual string[] GetPropertiesByPage(string selectFieldName, WhereStatement whereStatement, out int recordCount, int pageIndex = 1, int pageSize = 50, OrderByStatement orderByStatement = null) { whereStatement.DbHelper = this.DbHelper; SelectQueryBuilder sqlBuilder = new SelectQueryBuilder(this.DbHelper); sqlBuilder.SetSelectColumns(selectFieldName); sqlBuilder.SetFromTable(this.CurrentTableName); sqlBuilder.AddWhere(whereStatement); DbParameter[] parameters = null; string sql = sqlBuilder.BuildSQL(out parameters); recordCount = Convert.ToInt32(this.DbHelper.ExecuteScalar(PagingBuilder.CreateCountingSql(sql), parameters)); if (orderByStatement == null) { orderByStatement = new OrderByStatement(this._iEntity.PrimaryKey, Sorting.Ascending); } string orderBySql = orderByStatement.BuildOrderByStatement(); string pagedSql = PagingBuilder.CreatePagingSql(recordCount, pageSize, pageIndex, sql, orderBySql); DataTable dataTable = new DataTable(this.CurrentTableName); dataTable = this.DbHelper.Fill(pagedSql, parameters); return(DataTableUtil.FieldToArray(dataTable, selectFieldName)); }
/// <summary> /// 获取我的已发起工单 /// </summary> /// <returns></returns> public DataTable GetMyStartedOrders(int pageIndex, int pageSize, out int count) { WhereStatement where = new WhereStatement(); //string sStatus = JSRequest.GetRequestUrlParm(OrderEntity.FieldStatus, false); //int? status = JSValidator.ValidateInt(OrderEntity.FieldStatus, sStatus, false); //if (status != null) { where.Add(OrderEntity.FieldStatus, Comparison.Equals, status); } PermissionService permissionService = new PermissionService(); ViewManager manager = new ViewManager("VO_Order"); //1.0 获取当前员工数据 StaffEntity staff = permissionService.GetCurrentStaff(); //2.0 构建where从句 where.Add(OrderEntity.FieldStarterID, Comparison.Equals, staff.ID); where.Add(OrderEntity.FieldStatus, Comparison.NotEquals, (int)OrderStatus.Canceled); OrderByStatement orderby = new OrderByStatement(); orderby.Add(OrderEntity.FieldPriority, Sorting.Descending); orderby.Add(OrderEntity.FieldBookingTime, Sorting.Ascending); //3.0 获取已发起的数据 DataTable dt = manager.GetDataTableByPage(where, out count, pageIndex, pageSize, orderby); return(dt); }
/// <summary> /// 根据条件,获取数据(分页) /// </summary> /// <param name="whereStatement">条件对象</param> /// <param name="recordCount">数据数量</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">页容量</param> /// <param name="orderByStatement">排序对象</param> /// <returns></returns> public virtual DataTable GetDataTableByPage(WhereStatement whereStatement, out int recordCount, int pageIndex = 1, int pageSize = 50, OrderByStatement orderByStatement = null) { DataTable dataTable = new DataTable(this.CurrentTableName); whereStatement.DbHelper = this.DbHelper; SelectQueryBuilder sqlBuilder = new SelectQueryBuilder(this.DbHelper); sqlBuilder.SetSelectAllColumns(); sqlBuilder.SetFromTable(this.CurrentTableName); sqlBuilder.AddWhere(whereStatement); DbParameter[] parameters = null; string sql = sqlBuilder.BuildSQL(out parameters); recordCount = Convert.ToInt32(this.DbHelper.ExecuteScalar(PagingBuilder.CreateCountingSql(sql), parameters)); if (pageIndex * 1.0 > Math.Ceiling(recordCount * 1.0 / pageSize * 1.0)) { //超过总页数 return(dataTable); } if (orderByStatement == null) { orderByStatement = new OrderByStatement("ID", Sorting.Ascending); } string orderBySql = orderByStatement.BuildOrderByStatement(); string pagedSql = PagingBuilder.CreatePagingSql(recordCount, pageSize, pageIndex, sql, orderBySql); dataTable = this.DbHelper.Fill(pagedSql, parameters); return(dataTable); }
/// <summary> /// 获取我处理中的工单 /// </summary> /// <returns></returns> public DataTable GetMyHandlingOrders(int pageIndex, int pageSize, out int count) { PermissionService permissionService = new PermissionService(); ViewManager manager = new ViewManager("VO_Order"); //1.0 获取当前员工数据 StaffEntity staff = permissionService.GetCurrentStaff(); //2.0 构建where从句 WhereStatement where = new WhereStatement(); WhereClause clause = new WhereClause(OrderEntity.FieldStatus, Comparison.Equals, (int)OrderStatus.Handling); clause.AddClause(LogicOperator.Or, Comparison.Equals, (int)OrderStatus.Rejected); where.Add(clause); where.Add(OrderEntity.FieldHandlerID, Comparison.Equals, staff.ID); //2.1 构建orderby 从句 OrderByStatement orderby = new OrderByStatement(); orderby.Add(OrderEntity.FieldPriority, Sorting.Descending); orderby.Add(OrderEntity.FieldBookingTime, Sorting.Ascending); //3.0 获取已发起的数据 DataTable dt = manager.GetDataTableByPage(where, out count, pageIndex, pageSize, orderby); return(dt); }
/// <summary> /// Generates new orders based on the underlying source by resolving the appropriate projections from the /// given lambda expression. /// </summary> /// <param name="orderByStatements"> /// The order by statements. /// </param> /// <param name="projection"> /// The projection. /// </param> /// <param name="data"> /// The helper data. /// </param> /// <returns> /// The new set of orders. /// </returns> /// <exception cref="ArgumentNullException"> /// <paramref name="orderByStatements" />, <paramref name="projection" /> or <paramref name="data" /> is /// null. /// </exception> public static IEnumerable <OrderByStatement> GetSourceBasedOrdersFrom ( IEnumerable <OrderByStatement> orderByStatements, LambdaExpression projection, QueryHelperData data ) { if (orderByStatements == null) { throw new ArgumentNullException("orderByStatements"); } if (projection == null) { throw new ArgumentNullException("projection"); } if (data == null) { throw new ArgumentNullException("data"); } if (orderByStatements.All(x => x.IsBasedOnSource)) { return(orderByStatements); } ProjectionHelper.GetProjection(projection, new HelperContext(data, projection, HelperType.Select)); var orders = new List <OrderByStatement>(); foreach (var statement in orderByStatements) { if (statement.IsBasedOnSource) { orders.Add(statement); continue; } IProjection orderProjection; bool foundMapping = data.Mappings .TryGetValue(statement.Property, out orderProjection); if (foundMapping) { var order = new OrderByStatement { IsBasedOnSource = true, Order = new Order(orderProjection, statement.OrderAscending) }; orders.Add(order); } } return(orders); }
public void AddOrderByStatements <T>(IEnumerable <string> propertyNames, OrderByDirection orderByDirection, string alias) { var subQuery = GetOrCreateSubQuery <T>(alias); var orderByStatements = propertyNames .Select(x => _conventionReader.GetColumnName <T>(x)) .Select(x => OrderByStatement.Create(x, orderByDirection, subQuery.TableIdentifier)); _orderByStatements.AddRange(orderByStatements); }
//1. Is Ok //2. throws exception //3. propertyName is not valid //4. Value is not convertable to propertyName //5. Operation is not valid. //TODO [Test] public void GetOrderBy_WhenCalled_ShouldReturnOk() { //Arrange OrderByStatement <EventModel> orderByStatement = TestsFacade.OrderBytatementFacade.BuildOrderByStatement <EventModel>(); //Act ResultHandler <Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> > > result = this._service.GetOrderBy <EventModel>(orderByStatement); //Assert Assert.IsNotNull(result); Assert.IsInstanceOf <ResultHandler <Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> > > >(result); Assert.IsTrue(result.Success); }
[Test] public void GetOrderBy_WhenOrderByStatementNull_ShouldReturnFail() { //Arrange OrderByStatement <EventModel> orderByStatement = null; //Act ResultHandler <Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> > > result = this._service.GetOrderBy <EventModel>(orderByStatement); //Assert Assert.IsNotNull(result); Assert.IsFalse(result.Success); Assert.IsNull(result.Value); Assert.IsTrue(result.ErrorCode == ErrorCode.ValidationError); }
//[Test] //public async Task Get_WhenCalled_ShouldReturnList() //{ // //Arrange // this._repositoryMock // .Setup(items => items.Get(null, null, "", null, null)) // .Returns(() => this._asyncEnumerable); // //Act // var actual = this._genericService.Get(null, null, ""); // //Assert // Assert.IsNotNull(result); // Assert.IsInstanceOf<ResultHandler<IEnumerable<EventModel>>>(result); // Assert.IsTrue(result.Success); // Assert.IsInstanceOf<IEnumerable<EventModel>>(result.Value); // IEnumerable<EventModel> resultList = result.Value; // Assert.IsNotNull(resultList); // Assert.AreEqual(this._eventModels.Count(), resultList.Count()); // CollectionAssert.AreEqual(this._eventModels, resultList); //} //[Test] //public async Task Get_WhenCalledWithFilter_ShouldReturnList() //{ // //Arrange // EventModel filteredItem = expectedList.ToList()[0]; // this._repositoryMock // .Setup(items => items.Get(It.IsAny<Expression<Func<EventModel, bool>>>(), null, "", null, null)) // .Returns(() => Task.FromResult(this._eventModels)); // //Act // var actual = await this._genericService.Get(null, null, ""); // //Assert // Assert.IsNotNull(result); // Assert.IsInstanceOf<ResultHandler<IEnumerable<EventModel>>>(result); // Assert.IsTrue(result.Success); // Assert.IsInstanceOf<IEnumerable<EventModel>>(result.Value); // IEnumerable<EventModel> resultList = result.Value; // Assert.IsNotNull(resultList); // Assert.AreEqual(resultList.Count(), this._eventModels.Count()); // Assert.AreEqual(resultList.First().GetHashCode(), this._eventModels.First().GetHashCode()); //} [Test] public async Task Get_WhenCalledWithOrder_ShouldReturnList() { //Arrange OrderByStatement <EventModel> orderByStatement = TestsFacade.OrderBytatementFacade.BuildOrderByStatement <EventModel>(); this._repositoryMock .Setup(items => items.Get(null, It.IsAny <Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> > >(), "", null, null)) .Returns(() => this._asyncEnumerable); //Act var actual = this._eventService.Get(null, orderByStatement, ""); //Assert await AssertSuccessGet(actual); }
[Test] public void GetOrderBy_WhenOrderByStatementNull_ShouldReturnFail() { //Arrange OrderByStatement <EventModel> orderByStatement = null; OrderByService service = new OrderByService(); //Act ResultService <Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> > > result = service.GetOrderBy <EventModel>(orderByStatement); //Assert Assert.IsNotNull(result); Assert.IsFalse(result.Success); Assert.IsNull(result.Value); Assert.IsTrue(result.ErrorCode == ErrorCode.EntityNotValid); }
[Test] public void GetOrderBy_WhenOrderByStatementNotValid_ShouldReturnFail() { //Arrange OrderByStatement <EventModel> orderByStatement = TestsFacade.OrderBytatementFacade.BuildOrderByStatement <EventModel>(); orderByStatement.PropertyName = Guid.NewGuid().ToString(); //Act ResultHandler <Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> > > result = this._service.GetOrderBy <EventModel>(orderByStatement); //Assert Assert.IsNotNull(result); Assert.IsFalse(result.Success); Assert.IsNull(result.Value); Assert.IsTrue(result.ErrorCode == ErrorCode.ValidationError); }
public void Test_GetDataTableManager() { IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType); UserManager manager = new UserManager(dbHelper); UserEntity entity = new UserEntity(); WhereStatement where = new WhereStatement(dbHelper); where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333")); OrderByStatement orderby = new OrderByStatement(); orderby.Add(entity.PrimaryKey, Sorting.Ascending); int count = 0; DataTable dt1 = manager.GetDataTable(where, out count, orderby); DataTable dt2 = manager.GetDataTableByPage(where, out count, 2, 2, orderby); }
public DataTable GetOrderHandlers(Guid orderID) { //只能查视图 ViewManager manager = new ViewManager("VO_OrderHandlers"); WhereStatement where = new WhereStatement(); where.Add(OrderHandlerEntity.FieldOrderID, Comparison.Equals, orderID.ToString()); OrderByStatement orderby = new OrderByStatement(); orderby.Add(OrderHandlerEntity.FieldIsLeader, Sorting.Descending); int count = 0; DataTable dt = manager.GetDataTable(where, out count, orderby); return(dt); }
public void Test_GetListManager() { IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType); UserManager manager = new UserManager(dbHelper); UserEntity entity = new UserEntity(); WhereStatement where = new WhereStatement(dbHelper); where.Add(new WhereClause(entity.FieldF3, Comparison.In, new string[] { "333", "3333" })); where.Add(new WhereClause(entity.FieldF2, Comparison.Like, "aa%")); OrderByStatement orderby = new OrderByStatement(); orderby.Add(entity.PrimaryKey, Sorting.Ascending); int count = 0; List <UserEntity> list = manager.GetList(where, out count, orderby); List <UserEntity> listByPage = manager.GetListByPage(where, out count, 2, 2, orderby); }
public void Test_GetIdsManager() { IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType); UserManager manager = new UserManager(dbHelper); UserEntity entity = new UserEntity(); WhereStatement where = new WhereStatement(dbHelper); //where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));//Test Return Data where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "3334"));//Test Return String[0] OrderByStatement orderby = new OrderByStatement(); orderby.Add(entity.PrimaryKey, Sorting.Ascending); orderby.Add(entity.FieldDateTimeType, Sorting.Descending); string[] ids = manager.GetIds(where, orderby); Console.WriteLine(ids); }
/// <summary> /// 获取 指定资源code 的整个树形menu /// </summary> /// <param name="role"></param> /// <param name="resourceCode">资源code</param> /// <returns></returns> public DataTable GetMenu(RoleEntity role, string resourceCode) { //1.0 获取已授权的树形menu string[] ids = GetTreeMenuIds(role, resourceCode); //2.0 根据id 获取详细信息 ViewManager vmanager = new ViewManager("VP_RolePermission"); WhereStatement where = new WhereStatement(); where.Add("ID", Comparison.In, ids); OrderByStatement order = new OrderByStatement("Resource_SortCode", Sorting.Ascending); int count = 0; DataTable dt = vmanager.GetDataTable(where, out count, order); return(dt); }
public ResultService <IEnumerable <T> > Get(IEnumerable <FilterStatement <T> > filterStatements, OrderByStatement <T> orderByStatement = null, string includeProperties = "") { try { Expression <Func <T, bool> > filters = null; if (filterStatements != null && filterStatements.Any()) { var filterService = new FiltersService <T>(filterStatements); var filtersResult = filterService.BuildExpression(); if (!filtersResult.Success) { return(ResultService.Fail <IEnumerable <T> >(filtersResult.ErrorCode)); } filters = filtersResult.Value as Expression <Func <T, bool> >; } Func <IQueryable <T>, IOrderedQueryable <T> > orderBy = null; if (orderByStatement != null) { var orderByService = new OrderByService(); var orderByResult = orderByService.GetOrderBy <T>(orderByStatement); if (!orderByResult.Success) { return(ResultService.Fail <IEnumerable <T> >(orderByResult.ErrorCode)); } orderBy = orderByResult.Value as Func <IQueryable <T>, IOrderedQueryable <T> >; } var result = this._repository.Get(filters, orderBy, includeProperties); if (result == null) { return(ResultService.Fail <IEnumerable <T> >(ErrorCode.NotFound)); } return(ResultService.Ok <IEnumerable <T> >(result)); } catch (Exception ex) { _log.LogError(ex, "Failed to get objects from DB"); return(ResultService.Fail <IEnumerable <T> >(ex)); } }
public ResultHandler <IAsyncEnumerable <EventModel> > Get(IEnumerable <FilterStatement <EventModel> > filterStatements, OrderByStatement <EventModel> orderByStatement = null, string includeProperties = "") { try { IEnumerable <Expression <Func <EventModel, bool> > > filters = null; if (filterStatements != null) { var filtersResult = this._filtersService.BuildExpressions(filterStatements); if (!filtersResult.Success) { return(ResultHandler.Fail <IAsyncEnumerable <EventModel> >(filtersResult.ErrorCode)); } filters = filtersResult.Value; } Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> > orderBy = null; if (orderByStatement != null) { var orderByService = new OrderByService(); var orderByResult = orderByService.GetOrderBy <EventModel>(orderByStatement); if (!orderByResult.Success) { return(ResultHandler.Fail <IAsyncEnumerable <EventModel> >(orderByResult.ErrorCode)); } orderBy = orderByResult.Value as Func <IQueryable <EventModel>, IOrderedQueryable <EventModel> >; } IAsyncEnumerable <EventModel> result = this._repository.Get(filters, orderBy, includeProperties); return(ResultHandler.Ok(result)); } catch (Exception ex) { _log.LogError(ex, "Failed to get objects from DB"); return(ResultHandler.Fail <IAsyncEnumerable <EventModel> >(ex)); } }
/// <summary> /// 根据条件,获取数据 /// </summary> /// <param name="whereStatement">SQL条件对象</param> /// <param name="recordCount">数据数量</param> /// <param name="orderByStatement">排序对象</param> /// <returns></returns> public virtual DataTable GetDataTable(WhereStatement whereStatement, out int recordCount, OrderByStatement orderByStatement = null) { whereStatement.DbHelper = this.DbHelper; SelectQueryBuilder sqlBuilder = new SelectQueryBuilder(this.DbHelper); sqlBuilder.SetSelectAllColumns(); sqlBuilder.SetFromTable(this.CurrentTableName); sqlBuilder.AddWhere(whereStatement); if (orderByStatement == null) { orderByStatement = new OrderByStatement("ID", Sorting.Ascending); } sqlBuilder.AddOrderBy(orderByStatement); DbParameter[] parameters = null; string sql = sqlBuilder.BuildSQL(out parameters); DataTable dataTable = new DataTable(this.CurrentTableName); dataTable = this.DbHelper.Fill(sql, parameters); recordCount = dataTable.Rows.Count; return(dataTable); }
public virtual string[] GetProperties(string selectFieldName, WhereStatement whereStatement, OrderByStatement orderByStatement = null) { whereStatement.DbHelper = this.DbHelper; SelectQueryBuilder sqlBuilder = new SelectQueryBuilder(this.DbHelper); sqlBuilder.SetSelectColumns(selectFieldName); sqlBuilder.SetFromTable(this.CurrentTableName); sqlBuilder.AddWhere(whereStatement); if (orderByStatement == null) { orderByStatement = new OrderByStatement(this._iEntity.PrimaryKey, Sorting.Ascending); } sqlBuilder.AddOrderBy(orderByStatement); DbParameter[] parameters = null; string sql = sqlBuilder.BuildSQL(out parameters); DataTable dataTable = new DataTable(this.CurrentTableName); dataTable = this.DbHelper.Fill(sql, parameters); return(DataTableUtil.FieldToArray(dataTable, selectFieldName)); }
public List <T> GetListByPage(WhereStatement whereStatement, out int recordCount, int pageIndex = 1, int pageSize = 50, OrderByStatement orderByStatement = null) { DataTable dataTable = this.GetDataTableByPage(whereStatement, out recordCount, pageIndex, pageSize, orderByStatement); List <T> list = ToList(dataTable); return(list); }
public OrderByClause(OrderByStatement orderByStatement, Func<string, string> escape, string tableName = "") : this(orderByStatement.AsList(), escape, tableName) { }
public virtual List <T> GetList(WhereStatement whereStatement, out int recordCount, OrderByStatement orderByStatement = null) { DataTable dataTable = this.GetDataTable(whereStatement, out recordCount, orderByStatement); List <T> list = ToList(dataTable); return(list); }
private OrderByClause CreateOrderByClause(OrderByStatement orderByStatement) { return(new OrderByClause(orderByStatement, Escape, orderByStatement.TableIdentifier)); }
public virtual string[] GetIdsByPage(WhereStatement whereStatement, out int recordCount, int pageIndex = 1, int pageSize = 50, OrderByStatement orderByStatement = null) { return(this.GetPropertiesByPage(this._iEntity.PrimaryKey, whereStatement, out recordCount, pageIndex, pageSize, orderByStatement)); }
public virtual string[] GetIds(WhereStatement whereStatement, OrderByStatement orderByStatement = null) { return(this.GetProperties(this._iEntity.PrimaryKey, whereStatement, orderByStatement)); }