public void Setup(GameHistoryItem currentItem, GameReportScrollList currentScrollList)
{
item = currentItem;
nameLabel.text = item.labelText;
timeLabel.text = item.playedTimeText;
scrollList = currentScrollList;
}
/// <summary>
/// Danh sách lịch sử các trận đã chơi theo game Id
/// </summary>
/// <param name="zoneId"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public GameHistoryResult GetGameHistoryByZoneID(int zoneId, int skip, int take)
{
var rs = new GameHistoryResult();
var lst = new List <GameHistoryItem>();
var totalRecord = 0;
var sql = "SELECT a.*, b.username, b.fullname FROM [portal].[dbo].game_history a inner join [portal].[dbo].view_user_info b on a.user_id = b.id WHERE game_id = @zoneId ORDER BY id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";
var sqlTotalQuery = "SELECT count(*) as totalRecord FROM [portal].[dbo].game_history WHERE game_id = @zoneId";
var conn = new SqlConnection(Constants.DBConnection);
var MyCommand = new SqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var item1 = new GameHistoryItem
{
id = MyReader.GetInt32(MyReader.GetOrdinal("id")),
user_id = MyReader.GetInt32(MyReader.GetOrdinal("user_id")),
cash = MyReader.GetInt64(MyReader.GetOrdinal("cash")),
current_cash = MyReader.GetInt64(MyReader.GetOrdinal("current_cash")),
description = MyReader.GetString(MyReader.GetOrdinal("description")),
game_id = MyReader.GetInt32(MyReader.GetOrdinal("game_id")),
trans_type = MyReader.GetInt32(MyReader.GetOrdinal("trans_type")),
time = MyReader.GetDateTime(MyReader.GetOrdinal("time")),
before_cash = MyReader.GetInt64(MyReader.GetOrdinal("before_cash")),
username = MyReader.GetString(MyReader.GetOrdinal("username")),
fullname = MyReader.GetString(MyReader.GetOrdinal("fullname"))
};
lst.Add(item1);
}
//close reader cũ
MyReader.Close();
MyCommand = new SqlCommand(sqlTotalQuery, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
}
conn.Close();
rs.data = lst;
rs.totalRecord = totalRecord;
return(rs);
}
private void AddButtons()
{
for (int i = this.userData.gameHistory.Count - 1; i >= 0; i--)
{
GameHistoryItem item = new GameHistoryItem();
item.labelText = GetDisplayName(this.userData.gameHistory[i].gameName);
item.gameName = this.userData.gameHistory[i].gameName;
item.playedTimeText = this.userData.gameHistory[i].playTime.ToString("dd/MM/yyyy HH:mm");
GameObject newButton = buttonObjectPool.GetObject();
newButton.transform.SetParent(contentPanel, false);
GameHistoryPrefab favBtn = newButton.GetComponent <GameHistoryPrefab>();
favBtn.Setup(item, this);
}
}
/// <summary>
/// Danh sách lịch sử các trận đã chơi theo game Id
/// </summary>
/// <param name="zoneId"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public GameHistoryResult GetGameHistoryByZoneID(int zoneId, int skip, int take)
{
var rs = new GameHistoryResult();
var lst = new List <GameHistoryItem>();
int totalRecord = 0;
string sql = "SELECT a.*, b.username, b.fullname FROM newDB.game_history a inner join newDB.view_user_info b on a.user_id = b.id WHERE game_id = @zoneId ORDER BY id DESC LIMIT @skip, @take";
string sqlTotalQuery = "SELECT count(*) as totalRecord FROM newDB.game_history WHERE game_id = @zoneId";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
GameHistoryItem item1 = new GameHistoryItem();
item1.id = MyReader.GetInt64("id");
item1.user_id = MyReader.GetInt64("user_id");
item1.cash = MyReader.GetInt64("cash");
item1.current_cash = MyReader.GetInt64("current_cash");
item1.description = MyReader.GetString("description");
item1.game_id = MyReader.GetInt32("game_id");
item1.trans_type = MyReader.GetInt32("trans_type");
item1.time = MyReader.GetDateTime("time");
item1.before_cash = MyReader.GetInt64("before_cash");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
lst.Add(item1);
}
//close reader cũ
MyReader.Close();
MyCommand = new MySqlCommand(sqlTotalQuery, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32("totalRecord");
}
conn.Close();
rs.data = lst;
rs.totalRecord = totalRecord;
return(rs);
}
public GameHistoryResult ExportExcel(DateTime?from, DateTime?to)
{
var rs = new GameHistoryResult();
var lst = new List <GameHistoryItem>();
int totalRecord = 0;
var sql = "SELECT a.*, b.username, b.fullname FROM [newDB].[dbo].game_history a inner join [newDB].[dbo].view_user_info b on a.user_id = b.id WHERE a.trans_type = 16";
if (from != null && to != null)
{
sql += " and time >= '" + from.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' and time <= '" + to.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
sql += " order by id desc";
var conn = new SqlConnection(Constants.DBConnection);
var MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var item1 = new GameHistoryItem
{
id = MyReader.GetInt32(MyReader.GetOrdinal("id")),
user_id = MyReader.GetInt32(MyReader.GetOrdinal("user_id")),
cash = MyReader.GetInt64(MyReader.GetOrdinal("cash")),
current_cash = MyReader.GetInt64(MyReader.GetOrdinal("current_cash")),
description = MyReader.GetString(MyReader.GetOrdinal("description")),
game_id = MyReader.GetInt32(MyReader.GetOrdinal("game_id")),
trans_type = MyReader.GetInt32(MyReader.GetOrdinal("trans_type")),
time = MyReader.GetDateTime(MyReader.GetOrdinal("time")),
before_cash = MyReader.GetInt64(MyReader.GetOrdinal("before_cash")),
username = MyReader.GetString(MyReader.GetOrdinal("username")),
fullname = MyReader.GetString(MyReader.GetOrdinal("fullname"))
};
item1.timestring = item1.time.Value.ToShortDateString();
lst.Add(item1);
}
//close reader cũ
MyReader.Close();
rs.data = lst;
return(rs);
}
