Beispiel #1
0
        public static IEnumerable <Category> GetCategories(
            DbFilter filter        = DbFilter.All,
            IEnumerable <long> ids = null,
            DbOrder order          = DbOrder.None)
        {
            using (var connection = OpenConnection())
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * FROM Categories";
                    AddFilter(filter, command, ids);
                    AddOrder(order, command);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return(new Category
                            {
                                CategoryId = reader.GetInt64(0),
                                Name = reader.GetString(1),
                            });
                        }
                    }
                }
        }
Beispiel #2
0
        public static IEnumerable <Transaction> GetTransactions(
            DbFilter filter        = DbFilter.All,
            IEnumerable <long> ids = null,
            DbOrder order          = DbOrder.None)
        {
            using (var connection = OpenConnection())
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * FROM Transactions";
                    AddFilter(filter, command, ids);
                    AddOrder(order, command);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return(new Transaction
                            {
                                TransactionId = reader.GetInt64(0),
                                Date = reader.GetDateTime(1),
                                Type = reader.GetString(2),
                                Reference = reader.GetString(3),
                                Value = reader.GetInt32(4),
                                CategoryId = reader.GetValue(5) as long?,
                            });
                        }
                    }
                }
        }
        public void Search_ExplicitParams_ReturnsList()
        {
            IEnumerable <Restaurant> expected = new List <Restaurant> {
                new Restaurant {
                    Id   = 1,
                    Name = "test"
                },
                new Restaurant {
                    Id   = 2,
                    Name = "test2"
                }
            };
            var restaurantRepository           = new Mock <IRestaurantRepository>();
            DbFilter <Restaurant> actualFilter = null;

            restaurantRepository.Setup(x => x.GetRestaurantsAsync(3, 2, It.IsAny <DbFilter <Restaurant> >()))
            .Callback <int, int, DbFilter <Restaurant> >((page, pagesize, filter) => { actualFilter = filter; })
            .Returns(Task.FromResult <IEnumerable <Restaurant> >(expected));
            var filterParam = new FilterParam {
                Field = "Name", Operator = OperatorEnum.Like, Value = "Burger*"
            };

            var sut    = new RestaurantsController(restaurantRepository.Object);
            var actual = sut.Post(filterParam, 3, 2).Result;

            Assert.AreEqual(expected, actual);
            Assert.IsNotNull(actualFilter);
            Assert.AreEqual("Name", actualFilter.Field);
            Assert.AreEqual(OperatorEnum.Like, actualFilter.Operator);
            Assert.AreEqual("Burger*", actualFilter.Value);
            restaurantRepository.VerifyAll();
        }
Beispiel #4
0
        private static void AddFilter(
            DbFilter filter,
            SQLiteCommand command,
            IEnumerable <long> ids)
        {
            string id_string() => string.Join(", ", ids.Select(id => id.ToString()));

            switch (filter)
            {
            case DbFilter.All:
                break;

            case DbFilter.WhereNoCategory:
                command.CommandText += " WHERE CategoryId IS NULL";
                break;

            case DbFilter.WhereCategoryId:
                command.CommandText += " WHERE CategoryId in (@CategoryIds)";
                command.Parameters.AddWithValue("@CategoryIds", id_string());
                break;

            case DbFilter.WhereTransactionId:
                command.CommandText = " WHERE TransactionId in (@TransactionIds)";
                command.Parameters.AddWithValue("@TransactionIds", id_string());
                break;

            default:
                throw new ArgumentOutOfRangeException(nameof(filter),
                                                      "Selected filter is invalid for this query");
            }
        }
 protected void Button1_Click(object sender, EventArgs e)
 {
     if (0 == txtname.Text.Length && txtname.Text.Trim() == null)
     {
         AlertMsg("请输入名字");
         txtname.Focus();
         return;
     }
     if (0 == txtcontent.Text.Length)
     {
         AlertMsg("请输入内容");
         txtcontent.Focus();
         return;
     }
     Button1.Enabled = false;
     if (txtname.Text.Length == 0 && txtcontent.Text.Length == 0)
     {
         AlertMsg("留言失败");
         Response.Redirect(Request.RawUrl);
     }
     else
     {
         DbFilter.AddMsg(txtname.Text, txtcontent.Text);
         AlertMsg("留言成功");
     }
     MsgBind();
 }
        public void Search_ExplicitParams_ReturnsList()
        {
            IEnumerable <Review> expected = new List <Review> {
                new Review {
                    Id           = 1,
                    UserId       = 2,
                    RestaurantId = 3
                },
                new Review {
                    Id           = 1,
                    UserId       = 4,
                    RestaurantId = 7
                }
            };
            var reviewRepository           = new Mock <IReviewRepository>();
            DbFilter <Review> actualFilter = null;

            reviewRepository.Setup(x => x.GetReviewsAsync(3, 2, It.IsAny <DbFilter <Review> >()))
            .Callback <int, int, DbFilter <Review> >((page, pagesize, filter) => { actualFilter = filter; })
            .Returns(Task.FromResult(expected));
            var filterParam = new FilterParam {
                Field = "UserId", Operator = OperatorEnum.Equal, Value = 1
            };

            var sut    = new ReviewsController(reviewRepository.Object);
            var actual = sut.Post(filterParam, 3, 2).Result;

            Assert.AreEqual(expected, actual);
            Assert.IsNotNull(actualFilter);
            Assert.AreEqual("UserId", actualFilter.Field);
            Assert.AreEqual(OperatorEnum.Equal, actualFilter.Operator);
            Assert.AreEqual(1, actualFilter.Value);
            reviewRepository.VerifyAll();
        }
        public void GetRestaurantsAsync_Test()
        {
            var restaurantDataManager = new Mock <IRestaurantDataManager>();
            var expected = new Restaurant[] {
                new Restaurant {
                    Name    = "seafood and such",
                    Address = "123 bay street",
                    City    = "seaside",
                    Id      = 4556
                },
                new Restaurant
                {
                    Name    = "donuts r better",
                    Address = "0 icing circle",
                    City    = "seaside",
                    Id      = 4557
                }
            }.ToList();
            var expectedPage     = 1;
            var expectedPageSize = 20;
            var expectedDbFilter = new DbFilter <Restaurant> {
                Field = "City", Operator = OperatorEnum.Like, Value = "sea%"
            };

            restaurantDataManager.Setup(x => x.GetRestaurantsAsync(expectedPage, expectedPageSize, expectedDbFilter)).Returns(Task.FromResult <IEnumerable <Restaurant> >(expected));

            var restaurantRepo = new RestaurantRepository(restaurantDataManager.Object);
            var actual         = restaurantRepo.GetRestaurantsAsync(expectedPage, expectedPageSize, expectedDbFilter).Result;

            restaurantDataManager.VerifyAll();
            Assert.IsNotNull(actual);
            Assert.AreEqual(expected, actual);
        }
 public Task<IEnumerable<Review>> Get(int userId,
     [FromUri(BinderType = typeof(TypeConverterModelBinder))] int? page,
     [FromUri(BinderType = typeof(TypeConverterModelBinder))]int? pagesize)
 {
     var filter = new DbFilter<Review>() { Field = "UserId", Operator = OperatorEnum.Equal, Value = userId };
     return _reviewRepository.GetReviewsAsync(page ?? 1, pagesize ?? 1000, filter);
 }
        public void GetReviewsAsync_Test()
        {
            //fake userinfo provider for resolving userid
            var userInfoProvider = new Mock <IUserInfoProvider>();

            var reviewDataManager = new Mock <IReviewDataManager>();
            var expected          = new Review[] {
                new Review {
                    Id           = 789,
                    UserId       = 123,
                    RestaurantId = 22,
                    Heading      = "awesome!!",
                    Content      = "super food",
                    Rating       = 10
                }
            }.ToList();
            var expectedPage     = 1;
            var expectedPageSize = 20;
            var expectedDbFilter = new DbFilter <Review> {
                Field = "UserId", Operator = OperatorEnum.Equal, Value = 123
            };

            reviewDataManager.Setup(x => x.GetReviewsAsync(expectedPage, expectedPageSize, expectedDbFilter)).Returns(Task.FromResult <IEnumerable <Review> >(expected));

            var reviewRepo = new ReviewRepository(reviewDataManager.Object, userInfoProvider.Object);
            var actual     = reviewRepo.GetReviewsAsync(expectedPage, expectedPageSize, expectedDbFilter).Result;

            reviewDataManager.VerifyAll();
            Assert.IsNotNull(actual);
            Assert.AreEqual(1, actual.Count());
            Assert.AreEqual(expected[0], actual.First());
        }
        public void Get_ExplicitParams_ReturnsList()
        {
            IEnumerable <Restaurant> expected = new List <Restaurant> {
                new Restaurant {
                    Id   = 1,
                    Name = "test"
                },
                new Restaurant {
                    Id   = 2,
                    Name = "test2"
                }
            };
            var restaurantRepository           = new Mock <IRestaurantRepository>();
            DbFilter <Restaurant> actualFilter = null;

            restaurantRepository.Setup(x => x.GetRestaurantsAsync(3, 2, It.IsAny <DbFilter <Restaurant> >()))
            .Callback <int, int, DbFilter <Restaurant> >((page, pagesize, filter) => { actualFilter = filter; })
            .Returns(Task.FromResult(expected));
            var sut = new RestaurantsController(restaurantRepository.Object);

            var actual = sut.Get(3, 2, "TestCity").Result;

            Assert.AreEqual(expected, actual);
            Assert.IsNotNull(actualFilter);
            Assert.AreEqual("City", actualFilter.Field);
            Assert.AreEqual(OperatorEnum.Equal, actualFilter.Operator);
            Assert.AreEqual("TestCity", actualFilter.Value);
            restaurantRepository.VerifyAll();
        }
        public void StringField_Like_ShouldHaveCorrectSql()
        {
            var sut = new DbFilter <Restaurant>()
            {
                Field = "City", Operator = OperatorEnum.Like, Value = "Pitts%"
            };
            var filterString = sut.GetFilterSql("filterval");

            Assert.AreEqual(" City like @filterval", filterString);
        }
        public void IntField_Equal_ShouldHaveCorrectSql()
        {
            var sut = new DbFilter <Restaurant>()
            {
                Field = "Id", Operator = OperatorEnum.Equal, Value = "123"
            };
            var filterString = sut.GetFilterSql("filterval");

            Assert.AreEqual(" Id = @filterval", filterString);
        }
Beispiel #13
0
        public void Construct()
        {
            var filter = new DbFilter <SqlParameter>("WHERE a = @a and b = @b",
                                                     new[] { new SqlParameter("@a", 1), new SqlParameter("@b", 2) });

            Assert.Equal("WHERE a = @a and b = @b", filter.WhereClause);
            Assert.Equal(2, filter.Parameters.Length);
            Assert.Contains(filter.Parameters, p => p.ParameterName == "@a" && (int)p.Value == 1);
            Assert.Contains(filter.Parameters, p => p.ParameterName == "@b" && (int)p.Value == 2);
        }
    protected void MsgBind()
    {
        PagedDataSource pg = new PagedDataSource();

        pg.DataSource       = DbFilter.GetMsgList().Tables[0].DefaultView;
        pg.AllowPaging      = true;
        pg.PageSize         = 3;
        pg.CurrentPageIndex = int.Parse(ViewState["Page"].ToString());
        ViewState["count"]  = pg.PageCount;
    }
        public void Field_DoesNotExist_ThrowsException()
        {
            var sut = new DbFilter <Restaurant>()
            {
                Field = "testit", Operator = OperatorEnum.Equal, Value = "123"
            };

            Assert.ThrowsException <Exceptions.InvalidFilterFieldException>(() =>
                                                                            sut.GetFilterSql("filterval"));
        }
Beispiel #16
0
        public void GetRestaurantsAsync_FilterByCity_ReturnsAll()
        {
            var ids    = FillRestaurants();
            var sut    = new RestaurantDataManager(DbContext);
            var filter = new DbFilter <Restaurant> {
                Field = "City", Operator = OperatorEnum.Equal, Value = "ChocoCity"
            };
            var result = sut.GetRestaurantsAsync(1, 100, filter).Result.ToList();

            Assert.AreEqual(result.Count(), 3);
        }
        public void GetReviewsAsync_FilterByUser_ReturnsAll()
        {
            FillReviews();
            var sut    = new ReviewDataManager(DbContext);
            var filter = new DbFilter <Review> {
                Field = "UserId", Operator = OperatorEnum.Equal, Value = userIds[1].ToString()
            };
            var result = sut.GetReviewsAsync(1, 100, filter).Result.ToList();

            Assert.AreEqual(result.Count(), 2);
            //the reviews should be sorted by heading so the 2nd one will be first
            //this is the test data that was created
            //ds.Add(InsertReview(userIds[1], restaurantIds[0], "slow service", null, 1));
            //ids.Add(InsertReview(userIds[1], restaurantIds[2], "love it", "everything was great", 10));
            Assert.AreEqual(result[0].Content, "everything was great");
            Assert.IsTrue(string.IsNullOrEmpty(result[1].Content));
        }
Beispiel #18
0
        public static IEnumerable <IRule> GetRules(
            DbFilter filter        = DbFilter.All,
            IEnumerable <long> ids = null,
            DbOrder order          = DbOrder.None)
        {
            using (var connection = OpenConnection())
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * FROM Rules";
                    AddFilter(filter, command, ids);
                    AddOrder(order, command);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return(Rule.CreateRule(
                                             reader.GetString(1),
                                             reader.GetString(2),
                                             reader.GetString(3)));
                        }
                    }
                }
        }
Beispiel #19
0
 public Task <IEnumerable <Restaurant> > GetRestaurantsAsync(int page, int pagesize, DbFilter <Restaurant> filter)
 {
     return(_restaurantDataManager.GetRestaurantsAsync(page, pagesize, filter));
 }
Beispiel #20
0
        public async Task <IEnumerable <Restaurant> > GetRestaurantsAsync(int page, int pagesize, DbFilter <Restaurant> filter)
        {
            using (var db = GetConnection())
            {
                var query = @"SELECT  id, name, address, city
                                FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY Name ) AS RowNum, id, name, address, city
                                          FROM      Restaurant
                                          <<whereclause>>
                                        ) AS RowConstrainedResult
                                WHERE   RowNum >= @start
                                    AND RowNum < @end
                                ORDER BY RowNum";
                Task <IEnumerable <Restaurant> > result;
                var start = ((page - 1) * pagesize) + 1;
                var end   = start + pagesize;
                if (filter != null)
                {
                    query  = query.Replace("<<whereclause>>", string.Format("where {0}", filter.GetFilterSql("filterparam")));
                    result = db.QueryAsync <Restaurant>(query, new { start, end, filterparam = filter.Value });
                }
                else
                {
                    query  = query.Replace("<<whereclause>>", "");
                    result = db.QueryAsync <Restaurant>(query, new { start, end });
                }

                return(await result);
            }
        }
    //按照filter来查询数据库,并对结果处理,将各个int值转化为对应的可读的string信息,
    //并且使其结构于页面中的ResultDataGrid一致。
    //将处理后生成的datatable返回,ResultDataGrid只须与其绑定即可。
    private DataTable QueryAndReturnResultDataTable(DbFilter filter)
    {
        //开始查询了
        using (IBlazeDatabase db = DbFactory.GetDatabase())
        {
            IBlazeTable table = db.GetTable(TableString.ServerOperationLogTableName);
            DataSet     ds    = new DataSet();
            table.Get(ds, filter);
            DataTable dt = ds.Tables[TableString.ServerOperationLogTableName];

            if (dt.Rows.Count == 0)
            {
                return(null);
            }

            //按照log_time升序进行排序
            dt.DefaultView.Sort = TableString.ServerOperationLogFieldLogTime + " ASC";

            DataTable finalDataTable = new DataTable();
            finalDataTable.Columns.Add(new DataColumn("operation_id", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("user_name", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("target_type", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("target_name", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("operation_type", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("operation_result", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("description", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("log_time", typeof(DateTime)));


            foreach (DataRow row in dt.Rows)
            {
                //新的datarow,要加入到finalDataTable中去的
                DataRow newRow = finalDataTable.NewRow();

                //设置显示结果中的操作流水号
                Int64 opId = (Int64)row[TableString.ServerOperationLogFieldOperationId];
                if (opId <= 0)
                {
                    newRow["operation_id"] = "不合法的操作流水号:必须为正整数";
                }
                else
                {
                    newRow["operation_id"] = opId.ToString();
                }

                //设置平台用户名
                int userId = (int)row[TableString.ServerOperationLogFieldUserId];
                FSEye.Security.User user = AdminServer.TheInstance.SecurityManager.GetUser(userId);
                if (user == null)
                {
                    newRow["user_name"] = "无此用户";
                }
                else
                {
                    newRow["user_name"] = user.UserName;
                }

                //设置操作对象类型和操作对象名称
                //targetId = -2表示对多个组开始进行操作,多个组名存在description字段中
                int isServerGroup = (int)row[TableString.ServerOperationLogFieldServerOrGroup];
                int targetId      = (int)row[TableString.ServerOperationLogFieldTargetId];
                if (isServerGroup == 1)
                {
                    newRow["target_type"] = "服务器组";
                    if (targetId == -2)
                    {
                        newRow["target_name"] = "";
                    }
                    else
                    {
                        ServerGroup serverGroup = AdminServer.TheInstance.GameServerManager.GetGameServerGroup(targetId);
                        if (serverGroup == null)
                        {
                            newRow["target_name"] = "无此服务器组";
                        }
                        else
                        {
                            newRow["target_name"] = serverGroup.Name;
                        }
                    }
                }
                else if (isServerGroup == 0)
                {
                    newRow["target_type"] = "服务器";
                    GameServer server = AdminServer.TheInstance.GameServerManager.GetGameServer(targetId);
                    if (server == null)
                    {
                        newRow["target_name"] = "无此服务器";
                    }
                    else
                    {
                        newRow["target_name"] = server.Name;
                    }
                }
                else
                {
                    newRow["target_type"] = "不合法的对象类型";
                    newRow["target_name"] = "不合法的操作对象";
                }

                //设置操作类型
                int opTypeInt = (int)row[TableString.ServerOperationLogFieldOperationType];
                GameServer.ServerOperationType opTypeEnum = (GameServer.ServerOperationType)Enum.Parse(typeof(GameServer.ServerOperationType), opTypeInt.ToString());
                switch (opTypeEnum)
                {
                case GameServer.ServerOperationType.Start:
                    newRow["operation_type"] = "启动";
                    break;

                case GameServer.ServerOperationType.Close:
                    newRow["operation_type"] = "关闭";
                    break;

                case GameServer.ServerOperationType.Update:
                    newRow["operation_type"] = "更新";
                    break;

                case GameServer.ServerOperationType.Download:
                    newRow["operation_type"] = "下载更新包";
                    break;

                case GameServer.ServerOperationType.UpdateConfigFile:
                    newRow["operation_type"] = "上传配置文件";
                    break;

                default:
                    newRow["operation_type"] = "未知操作类型";
                    break;
                }

                //设置操作结果
                int resultSuccess = (int)row[TableString.ServerOperationLogFieldOperationResult];
                if (resultSuccess == 1)
                {
                    newRow["operation_result"] = "成功";
                }
                else if (resultSuccess == 0)
                {
                    newRow["operation_result"] = "失败";
                }
                else
                {
                    newRow["operation_result"] = "不合法的操作结果";
                }

                //设置操作描述
                newRow["description"] = (String)row[TableString.ServerOperationLogFieldDescription];


                //设置日志记录时间
                newRow["log_time"] = (DateTime)row[TableString.ServerOperationLogFieldLogTime];

                //将newrow加入到finaldatatable中
                finalDataTable.Rows.Add(newRow);
            }

            return(finalDataTable);
        }
    }
    protected void ButtonQuery_Click(object sender, EventArgs e)
    {
        //对GameServerManager的读权限
        if (!WebUtil.CheckPrivilege(TheAdminServer.GameServerManager.SecurityObject, OpType.READ, Session))
        {
            LabelOpMsg.Text    = StringDef.NotEnoughPrivilege;
            LabelOpMsg.Visible = true;
            return;
        }

        //按流水号查询的情况(相对简明)
        if (QueryByWhatRadioButtonList.SelectedValue == "operationId")
        {
            Int64 operationId = Int64.Parse(OperationIdTextBox.Text);

            if (operationId <= 0)
            {
                LabelOpMsg.Text        = "不合法的操作流水号 -- 必须为正整数";
                LabelOpMsg.Visible     = true;
                ResultDataGrid.Visible = false;
                return;
            }

            DbFilter operationIdFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationId, operationId);

            DbFilter finalFilter = operationIdFilter;

            //按照操作结果checkboxlist的勾选情况生成最终的finalfilter
            //这里将两项都勾和两项都不勾作为相同的情况处理---当作都勾
            if ((resultSucessCheckBoxList.Items.FindByValue("1").Selected == true) && (resultSucessCheckBoxList.Items.FindByValue("0").Selected == false))
            {
                DbFilter resultFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 1);
                finalFilter = FilterFactory.CreateAndFilter(operationIdFilter, resultFilter);
            }
            else if ((resultSucessCheckBoxList.Items.FindByValue("1").Selected == false) && (resultSucessCheckBoxList.Items.FindByValue("0").Selected == true))
            {
                DbFilter resultFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 0);
                finalFilter = FilterFactory.CreateAndFilter(operationIdFilter, resultFilter);
            }
            //这里之所以需要再加一个else,是为了限定只返回operation_result字段的值为0或1的记录,
            //这样就过滤掉了存储最大流水号的那条特殊记录(因为其operation_result字段的值为-1)
            else
            {
                DbFilter resultFilter = FilterFactory.CreateOrFilter(FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 1), FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 0));
                finalFilter = FilterFactory.CreateAndFilter(operationIdFilter, resultFilter);
            }

            DataTable dt = QueryAndReturnResultDataTable(finalFilter);

            if (dt == null)
            {
                LabelOpMsg.Text        = "没有查到满足条件的记录";
                LabelOpMsg.Visible     = true;
                ResultDataGrid.Visible = false;
                return;
            }

            DataView dv = new DataView(dt);
            ResultDataGrid.DataSource = dv;
            ResultDataGrid.DataBind();
            ResultDataGrid.Visible = true;
            LabelOpMsg.Visible     = false;
        }

        //按操作对象查询的情况(复杂)
        else if (QueryByWhatRadioButtonList.SelectedValue == "target")
        {
            if (ServerGroupDropDownList.SelectedServerGroup == null)
            {
                LabelOpMsg.Text        = "无操作对象";
                LabelOpMsg.Visible     = true;
                ResultDataGrid.Visible = false;
                return;
            }

            DbFilter finalFilter = null;

            //操作对象的filter
            if (ServerDropDownList.SelectedValue == "thisServerGroup")
            {
                int serverGroupId = ServerGroupDropDownList.SelectedServerGroup.Id;
                finalFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldTargetId, serverGroupId);
            }
            else
            {
                int serverId = int.Parse(ServerDropDownList.SelectedValue);
                finalFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldTargetId, serverId);
            }

            //平台用户的filter
            if (UserRadioButtonList.SelectedValue == "name")
            {
                if (UserNameTextBox.Text != String.Empty)
                {
                    FSEye.Security.User user = AdminServer.TheInstance.SecurityManager.GetUser(UserNameTextBox.Text);
                    if (user == null)
                    {
                        LabelOpMsg.Text        = "指定的平台用户不存在";
                        LabelOpMsg.Visible     = true;
                        ResultDataGrid.Visible = false;
                        return;
                    }
                    DbFilter userFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldUserId, user.SecurityObject.Id);
                    finalFilter = FilterFactory.CreateAndFilter(finalFilter, userFilter);
                }
            }
            else if (UserRadioButtonList.SelectedValue == "id")
            {
                if (UserIdTextBox.Text != String.Empty)
                {
                    int userId = int.Parse(UserIdTextBox.Text);
                    FSEye.Security.User user = AdminServer.TheInstance.SecurityManager.GetUser(userId);
                    if (user == null)
                    {
                        LabelOpMsg.Text        = "指定的平台用户不存在";
                        LabelOpMsg.Visible     = true;
                        ResultDataGrid.Visible = false;
                        return;
                    }
                    DbFilter userFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldUserId, userId);
                    finalFilter = FilterFactory.CreateAndFilter(finalFilter, userFilter);
                }
            }

            //操作类型的filter
            if (CheckBoxListOperation.SelectedIndex != -1)
            {
                DbFilter operationTypeFilter = null;
                foreach (ListItem item in CheckBoxListOperation.Items)
                {
                    if (item.Selected == true)
                    {
                        int opTypeInt = int.Parse(item.Value);

                        DbFilter singleTypeFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationType, opTypeInt);

                        if (operationTypeFilter == null)
                        {
                            operationTypeFilter = singleTypeFilter;
                        }
                        else
                        {
                            operationTypeFilter = FilterFactory.CreateOrFilter(operationTypeFilter, singleTypeFilter);
                        }
                    }
                }

                if (operationTypeFilter != null)
                {
                    finalFilter = FilterFactory.CreateAndFilter(finalFilter, operationTypeFilter);
                }
            }

            //操作结果的filter
            if ((resultSucessCheckBoxList.Items.FindByValue("1").Selected == true) && (resultSucessCheckBoxList.Items.FindByValue("0").Selected == false))
            {
                DbFilter resultFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 1);
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, resultFilter);
            }
            else if ((resultSucessCheckBoxList.Items.FindByValue("1").Selected == false) && (resultSucessCheckBoxList.Items.FindByValue("0").Selected == true))
            {
                DbFilter resultFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 0);
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, resultFilter);
            }
            else
            {
                DbFilter resultFilter = FilterFactory.CreateOrFilter(FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 1), FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 0));
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, resultFilter);
            }

            //时间的filter
            DateTime smallerDateTime;
            DateTime biggerDateTime;
            if ((StartDate.Selected == true) && (EndDate.Selected == true))
            {
                if (StartDate.SelectedDate <= EndDate.SelectedDate)
                {
                    smallerDateTime = StartDate.SelectedDate;
                    biggerDateTime  = EndDate.SelectedDate;
                }
                else
                {
                    smallerDateTime = EndDate.SelectedDate;
                    biggerDateTime  = StartDate.SelectedDate;
                }

                DbFilter timeFilter = FilterFactory.CreateAndFilter(FilterFactory.CreateLargerEqualFilter(TableString.ServerOperationLogFieldLogTime, smallerDateTime), FilterFactory.CreateLesserEqualFilter(TableString.ServerOperationLogFieldLogTime, biggerDateTime));
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, timeFilter);
            }
            else if ((StartDate.Selected == true) && (EndDate.Selected == false))
            {
                DateTime now = DateTime.Now;

                if (StartDate.SelectedDate <= now)
                {
                    smallerDateTime = StartDate.SelectedDate;
                    biggerDateTime  = now;
                }
                else
                {
                    smallerDateTime = now;
                    biggerDateTime  = StartDate.SelectedDate;
                }

                DbFilter timeFilter = FilterFactory.CreateAndFilter(FilterFactory.CreateLargerEqualFilter(TableString.ServerOperationLogFieldLogTime, smallerDateTime), FilterFactory.CreateLesserEqualFilter(TableString.ServerOperationLogFieldLogTime, biggerDateTime));
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, timeFilter);
            }
            else if ((StartDate.Selected == false) && (EndDate.Selected == true))
            {
                DateTime now = DateTime.Now;

                if (EndDate.SelectedDate <= now)
                {
                    smallerDateTime = EndDate.SelectedDate;
                    biggerDateTime  = now;
                }
                else
                {
                    smallerDateTime = now;
                    biggerDateTime  = EndDate.SelectedDate;
                }

                DbFilter timeFilter = FilterFactory.CreateAndFilter(FilterFactory.CreateLargerEqualFilter(TableString.ServerOperationLogFieldLogTime, smallerDateTime), FilterFactory.CreateLesserEqualFilter(TableString.ServerOperationLogFieldLogTime, biggerDateTime));
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, timeFilter);
            }

            //finalfilter生成完毕,可以查询了
            DataTable dt = QueryAndReturnResultDataTable(finalFilter);

            if (dt == null)
            {
                LabelOpMsg.Text        = "没有查到满足条件的记录";
                LabelOpMsg.Visible     = true;
                ResultDataGrid.Visible = false;
                return;
            }

            DataView dv = new DataView(dt);
            ResultDataGrid.DataSource = dv;
            ResultDataGrid.DataBind();
            ResultDataGrid.Visible = true;
            LabelOpMsg.Visible     = false;
        }

        else if (QueryByWhatRadioButtonList.SelectedValue == "time")
        {
            DbFilter finalFilter;

            //操作结果的filter
            if ((resultSucessCheckBoxList.Items.FindByValue("1").Selected == true) && (resultSucessCheckBoxList.Items.FindByValue("0").Selected == false))
            {
                DbFilter resultFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 1);
                finalFilter = resultFilter;
            }
            else if ((resultSucessCheckBoxList.Items.FindByValue("1").Selected == false) && (resultSucessCheckBoxList.Items.FindByValue("0").Selected == true))
            {
                DbFilter resultFilter = FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 0);
                finalFilter = resultFilter;
            }
            else
            {
                DbFilter resultFilter = FilterFactory.CreateOrFilter(FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 1), FilterFactory.CreateEqualFilter(TableString.ServerOperationLogFieldOperationResult, 0));
                finalFilter = resultFilter;
            }

            //时间的filter
            DateTime smallerDateTime;
            DateTime biggerDateTime;
            if ((StartDate.Selected == true) && (EndDate.Selected == true))
            {
                if (StartDate.SelectedDate <= EndDate.SelectedDate)
                {
                    smallerDateTime = StartDate.SelectedDate;
                    biggerDateTime  = EndDate.SelectedDate;
                }
                else
                {
                    smallerDateTime = EndDate.SelectedDate;
                    biggerDateTime  = StartDate.SelectedDate;
                }

                DbFilter timeFilter = FilterFactory.CreateAndFilter(FilterFactory.CreateLargerEqualFilter(TableString.ServerOperationLogFieldLogTime, smallerDateTime), FilterFactory.CreateLesserEqualFilter(TableString.ServerOperationLogFieldLogTime, biggerDateTime));
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, timeFilter);
            }
            else if ((StartDate.Selected == true) && (EndDate.Selected == false))
            {
                DateTime now = DateTime.Now;

                if (StartDate.SelectedDate <= now)
                {
                    smallerDateTime = StartDate.SelectedDate;
                    biggerDateTime  = now;
                }
                else
                {
                    smallerDateTime = now;
                    biggerDateTime  = StartDate.SelectedDate;
                }

                DbFilter timeFilter = FilterFactory.CreateAndFilter(FilterFactory.CreateLargerEqualFilter(TableString.ServerOperationLogFieldLogTime, smallerDateTime), FilterFactory.CreateLesserEqualFilter(TableString.ServerOperationLogFieldLogTime, biggerDateTime));
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, timeFilter);
            }
            else if ((StartDate.Selected == false) && (EndDate.Selected == true))
            {
                DateTime now = DateTime.Now;

                if (EndDate.SelectedDate <= now)
                {
                    smallerDateTime = EndDate.SelectedDate;
                    biggerDateTime  = now;
                }
                else
                {
                    smallerDateTime = now;
                    biggerDateTime  = EndDate.SelectedDate;
                }

                DbFilter timeFilter = FilterFactory.CreateAndFilter(FilterFactory.CreateLargerEqualFilter(TableString.ServerOperationLogFieldLogTime, smallerDateTime), FilterFactory.CreateLesserEqualFilter(TableString.ServerOperationLogFieldLogTime, biggerDateTime));
                finalFilter = FilterFactory.CreateAndFilter(finalFilter, timeFilter);
            }

            //finalfilter生成完毕,可以查询了
            DataTable dt = QueryAndReturnResultDataTable(finalFilter);

            if (dt == null)
            {
                LabelOpMsg.Text        = "没有查到满足条件的记录";
                LabelOpMsg.Visible     = true;
                ResultDataGrid.Visible = false;
                return;
            }

            DataView dv = new DataView(dt);
            ResultDataGrid.DataSource = dv;
            ResultDataGrid.DataBind();
            ResultDataGrid.Visible = true;
            LabelOpMsg.Visible     = false;
        }
    }
Beispiel #23
0
        public async Task <IEnumerable <Review> > GetReviewsAsync(int page, int pagesize, DbFilter <Review> filter)
        {
            using (var db = GetConnection())
            {
                var query = @"SELECT  id, user_id as UserId, restaurant_id as RestaurantId, heading, content, rating
                                FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY heading ) AS RowNum, id, user_id, restaurant_id, heading, content, rating
                                          FROM      Review
                                          <<whereclause>>
                                        ) AS RowConstrainedResult
                                WHERE   RowNum >= @start
                                    AND RowNum < @end
                                ORDER BY RowNum";
                IEnumerable <Review> result;
                var    start = ((page - 1) * pagesize) + 1;
                var    end   = start + pagesize;
                object param;
                if (filter != null)
                {
                    query = query.Replace("<<whereclause>>", string.Format("where {0}", filter.GetFilterSql("filterparam")));
                    param = new { start, end, filterparam = filter.Value };
                }
                else
                {
                    query = query.Replace("<<whereclause>>", "");
                    param = new { start, end };
                }
                result = await db.QueryAsync <Review>(query, param);

                return(result);
            }
        }
    //按照filter来查询数据库,并对结果处理,将各个int值转化为对应的可读的string信息,
    //并且使其结构于页面中的ResultDataGrid一致。
    //将处理后生成的datatable返回,ResultDataGrid只须与其绑定即可。
    private DataTable QueryAndReturnResultDataTable(DbFilter filter)
    {
        //开始查询了
        using (IBlazeDatabase db = DbFactory.GetDatabase())
        {
            IBlazeTable table = db.GetTable(TableString.ServerOperationLogTableName);
            DataSet ds = new DataSet();
            table.Get(ds, filter);
            DataTable dt = ds.Tables[TableString.ServerOperationLogTableName];

            if (dt.Rows.Count == 0)
            {
                return null;
            }

            //按照log_time升序进行排序
            dt.DefaultView.Sort = TableString.ServerOperationLogFieldLogTime + " ASC";

            DataTable finalDataTable = new DataTable();
            finalDataTable.Columns.Add(new DataColumn("operation_id", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("user_name", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("target_type", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("target_name", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("operation_type", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("operation_result", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("description", typeof(String)));
            finalDataTable.Columns.Add(new DataColumn("log_time", typeof(DateTime)));


            foreach (DataRow row in dt.Rows)
            {
                //新的datarow,要加入到finalDataTable中去的
                DataRow newRow = finalDataTable.NewRow();

                //设置显示结果中的操作流水号
                Int64 opId = (Int64)row[TableString.ServerOperationLogFieldOperationId];
                if (opId <= 0)
                {
                    newRow["operation_id"] = "不合法的操作流水号:必须为正整数";
                }
                else
                {
                    newRow["operation_id"] = opId.ToString();
                }

                //设置平台用户名
                int userId = (int)row[TableString.ServerOperationLogFieldUserId];
                FSEye.Security.User user = AdminServer.TheInstance.SecurityManager.GetUser(userId);
                if (user == null)
                {
                    newRow["user_name"] = "无此用户";
                }
                else
                {
                    newRow["user_name"] = user.UserName;
                }

                //设置操作对象类型和操作对象名称
                //targetId = -2表示对多个组开始进行操作,多个组名存在description字段中
                int isServerGroup = (int)row[TableString.ServerOperationLogFieldServerOrGroup];
                int targetId = (int)row[TableString.ServerOperationLogFieldTargetId];
                if (isServerGroup == 1)
                {
                    newRow["target_type"] = "服务器组";
                    if (targetId == -2)
                    {
                        newRow["target_name"] = "";
                    }
                    else
                    {
                        ServerGroup serverGroup = AdminServer.TheInstance.GameServerManager.GetGameServerGroup(targetId);
                        if (serverGroup == null)
                        {
                            newRow["target_name"] = "无此服务器组";
                        }
                        else
                        {
                            newRow["target_name"] = serverGroup.Name;
                        }
                    }
                }
                else if (isServerGroup == 0)
                {
                    newRow["target_type"] = "服务器";
                    GameServer server = AdminServer.TheInstance.GameServerManager.GetGameServer(targetId);
                    if (server == null)
                    {
                        newRow["target_name"] = "无此服务器";
                    }
                    else
                    {
                        newRow["target_name"] = server.Name;
                    }
                }
                else
                {
                    newRow["target_type"] = "不合法的对象类型";
                    newRow["target_name"] = "不合法的操作对象";
                }

                //设置操作类型
                int opTypeInt = (int)row[TableString.ServerOperationLogFieldOperationType];
                GameServer.ServerOperationType opTypeEnum = (GameServer.ServerOperationType)Enum.Parse(typeof(GameServer.ServerOperationType), opTypeInt.ToString());
                switch (opTypeEnum)
                {
                    case GameServer.ServerOperationType.Start:
                        newRow["operation_type"] = "启动";
                        break;
                    case GameServer.ServerOperationType.Close:
                        newRow["operation_type"] = "关闭";
                        break;
                    case GameServer.ServerOperationType.Update:
                        newRow["operation_type"] = "更新";
                        break;
                    case GameServer.ServerOperationType.Download:
                        newRow["operation_type"] = "下载更新包";
                        break;
                    case GameServer.ServerOperationType.UpdateConfigFile:
                        newRow["operation_type"] = "上传配置文件";
                        break;
                    default:
                        newRow["operation_type"] = "未知操作类型";
                        break;
                }

                //设置操作结果
                int resultSuccess = (int)row[TableString.ServerOperationLogFieldOperationResult];
                if (resultSuccess == 1)
                {
                    newRow["operation_result"] = "成功";
                }
                else if (resultSuccess == 0)
                {
                    newRow["operation_result"] = "失败";                    
                }
                else
                {
                    newRow["operation_result"] = "不合法的操作结果";
                }

                //设置操作描述
                newRow["description"] = (String)row[TableString.ServerOperationLogFieldDescription];


                //设置日志记录时间
                newRow["log_time"] = (DateTime)row[TableString.ServerOperationLogFieldLogTime];

                //将newrow加入到finaldatatable中
                finalDataTable.Rows.Add(newRow);
            }

            return finalDataTable;
        }

    }