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),
});
}
}
}
}
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();
}
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);
}
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"));
}
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));
}
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)));
}
}
}
}
public Task <IEnumerable <Restaurant> > GetRestaurantsAsync(int page, int pagesize, DbFilter <Restaurant> filter)
{
return(_restaurantDataManager.GetRestaurantsAsync(page, pagesize, filter));
}
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;
}
}
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;
}
}