/// <summary>
/// Danh sách top user thắng nhiều (top) theo từng Game Id
/// </summary>
/// <param name="zoneId"></param>
/// <param name="top"></param>
/// <returns></returns>
public List <UserInfo> GetTopUserByZoneID(int zoneId, int top)
{
var rs = new List <UserInfo>();
var sql = "select b.id,b.username, b.fullname,sum(a.cash) as total_cash_win from newDB.game_history a inner join newDB.view_user_info b on a.user_id = b.id where a.cash > 0 AND (trans_type=1 or trans_type=18) AND game_id = @zoneId" +
" group by a.user_id order by total_cash_win desc" +
" limit 0, @top";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyCommand.Parameters.AddWithValue("top", top);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
UserInfo u = new UserInfo();
u.uid = MyReader.GetInt64("id");
u.userName = MyReader.GetString("username");
u.fullName = MyReader.GetString("fullname");
u.cashWin = MyReader.GetInt64("total_cash_win");
rs.Add(u);
}
MyReader.Close();
conn.Close();
return(rs);
}
/// <summary>
/// Báo cáo Danh sách user nạp nhiều tiền nhất (theo tổng số tiền Su đã nạp)
/// </summary>
/// <param name="from"></param>
/// <param name="to"></param>
/// <param name="top"></param>
/// <returns></returns>
public List <ReportUserChargeMoneyItem> ReportUserChargedMost(DateTime from, DateTime to, int top)
{
var rs = new List <ReportUserChargeMoneyItem>();
string sql = String.Format("SELECT h.user_id, u.username, u.fullname, u.ip, u.gameCash, u.vCash, SUM(h.cash) AS 'SuNap' FROM newDB.game_history h INNER JOIN newDB.view_user_info u ON h.user_id = u.id WHERE h.trans_type = 4 and h.time > '{0}' and h.time < '{1}' group by h.user_id, u.username, u.fullname order by SUM(h.cash) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
ReportUserChargeMoneyItem item1 = new ReportUserChargeMoneyItem();
item1.user_id = MyReader.GetInt32("user_id");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
item1.ip = MyReader.GetString("ip");
item1.gameCash = MyReader.GetInt64("gameCash");
item1.vCash = MyReader.GetInt64("vCash");
if (String.IsNullOrEmpty(item1.fullname))
{
item1.fullname = item1.username;
}
item1.moenyCharged = MyReader.GetInt64("SuNap");
rs.Add(item1);
}
MyReader.Close();
conn.Close();
return(rs);
}
public List <UserInfo> GetTopNapTien()
{
var rs = new List <UserInfo>();
var sql = "SELECT b.id, b.fullname, b.username, ABS(SUM(a.cash)) AS cashWin FROM [portal].[dbo].game_history a INNER JOIN [portal].[dbo].view_user_info b ON a.user_id = b.id WHERE trans_type = 4 AND a.cash > 0 GROUP BY b.id, b.fullname, b.username ORDER BY ABS(SUM(a.cash)) DESC";
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var u = new UserInfo
{
uid = MyReader.GetInt64(MyReader.GetOrdinal("id")),
userName = MyReader.GetString(MyReader.GetOrdinal("username")),
fullName = MyReader.GetString(MyReader.GetOrdinal("fullname")),
cashWin = MyReader.GetInt64(MyReader.GetOrdinal("cashWin"))
};
rs.Add(u);
}
MyReader.Close();
conn.Close();
return(rs);
}
public List <UserInfo> GetTopExp()
{
var rs = new List <UserInfo>();
var sql = "SELECT top 100 * FROM [portal].[dbo].view_user_info ORDER BY EXP DESC";
SqlConnection conn = new SqlConnection(Constants.DBConnection);
SqlCommand MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var u = new UserInfo
{
uid = MyReader.GetInt64(MyReader.GetOrdinal("id")),
userName = MyReader.GetString(MyReader.GetOrdinal("username")),
fullName = MyReader.GetString(MyReader.GetOrdinal("fullname")),
cashWin = MyReader.GetInt64(MyReader.GetOrdinal("EXP"))
};
rs.Add(u);
}
MyReader.Close();
conn.Close();
return(rs);
}
/// <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);
}
/// <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 List <UserInfo> GetGiaoDich(out int total, int type, int skip, int take, int uid = 0)
{
var rs = new List <UserInfo>();
var sql = "SELECT b.id, b.fullname, b.username, a.description, a.time, a.trans_type FROM [portal].[dbo].game_history a INNER JOIN [portal].[dbo].view_user_info b ON a.user_id = b.id WHERE a.trans_type = " + type;
var sqlTotalQuery = " SELECT Count(a.id) as totalRecord FROM [portal].[dbo].game_history a WHERE a.trans_type = " + type;
if (uid > 0)
{
sql += " AND user_id = " + uid;
sqlTotalQuery += " AND user_id = " + uid;
}
sql += " ORDER BY a.id desc OFFSET " + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY";
var conn = new SqlConnection(Constants.DBConnection);
var MyCommand = new SqlCommand(sql, conn);
SqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
var u = new UserInfo
{
uid = MyReader.GetInt64(MyReader.GetOrdinal("id")),
userName = MyReader.GetString(MyReader.GetOrdinal("username")),
fullName = MyReader.GetString(MyReader.GetOrdinal("fullname")),
description = MyReader.GetString(MyReader.GetOrdinal("description")),
dateRegister = MyReader.GetString(MyReader.GetOrdinal("time")),
typyPlay = MyReader.GetInt32(MyReader.GetOrdinal("trans_type"))
};
rs.Add(u);
}
MyReader.Close();
MyCommand = new SqlCommand(sqlTotalQuery, conn);
MyReader = MyCommand.ExecuteReader();
total = 0;
while (MyReader.Read())
{
total = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord"));
}
conn.Close();
return(rs);
}
/// <summary>
/// Select một user theo user_id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public UserInfo SelectOne(long id)
{
var user = new UserInfo();
string sql = "select * from newDB.view_user_info where id = " + id;
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
user.uid = MyReader.GetInt64("id");
user.userName = MyReader.GetString("username");
user.fullName = MyReader.GetString("fullname");
user.dateRegister = MyReader.GetDateTime("register_date");
user.isMale = MyReader.GetInt32("sex") == 1;
user.level = MyReader.GetInt32("level_id");
user.cash = MyReader.GetInt64("gameCash");
user.vcash = MyReader.GetInt64("vCash");
user.playsNumber = MyReader.GetInt32("totalGame");
user.playsWin = MyReader.GetInt32("totalWin");
if (!MyReader.IsDBNull(8))
{
user.email = MyReader.GetString("email");
}
if (!MyReader.IsDBNull(7))
{
user.phone = MyReader.GetString("mobile");
}
if (!MyReader.IsDBNull(21))
{
user.cmnd = MyReader.GetString("cmnd");
}
user.ipAddress = MyReader.GetString("ip");
user.isMobile = MyReader.GetInt32("isMobile");
user.lastLogin = MyReader.GetDateTime("last_login");
}
//close reader cũ
MyReader.Close();
conn.Close();
return(user);
}
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);
}
/// <summary>
/// Báo cáo Danh sách top người chơi thắng nhiều nhất (tính theo số trận thắng)
/// </summary>
/// <param name="from"></param>
/// <param name="to"></param>
/// <param name="top"></param>
/// <param name="vCash"></param>
/// <returns></returns>
public List <ReportUserPlayGameItem> ReportUserPlayWin(DateTime from, DateTime to, int top, bool vCash)
{
var rs = new List <ReportUserPlayGameItem>();
string sql = String.Format("select user_id, username, fullname, ip, gameCash, vCash, count(0) as 'totalGame' from newDB.view_user_history_total where is_active = 1 and time > '{0}' and time < '{1}' and cash > 0 group by user_id, username, fullname, ip order by COUNT(0) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);
if (vCash)
{
sql = String.Format("select user_id, username, fullname, ip, gameCash, vCash, count(0) as 'totalGame' from newDB.view_user_history_total_vCash where is_active = 1 and time > '{0}' and time < '{1}' and cash > 0 group by user_id, username, fullname, ip order by COUNT(0) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);
}
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
ReportUserPlayGameItem item1 = new ReportUserPlayGameItem();
item1.user_id = MyReader.GetInt32("user_id");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
item1.ip = MyReader.GetString("ip");
item1.gameCash = MyReader.GetInt64("gameCash");
item1.vCash = MyReader.GetInt64("vCash");
if (String.IsNullOrEmpty(item1.fullname))
{
item1.fullname = item1.username;
}
item1.totalGame = MyReader.GetInt32("totalGame");
rs.Add(item1);
}
//close reader cũ
MyReader.Close();
conn.Close();
return(rs);
}
/// <summary>
/// Danh sách user, sắp xếp theo đăng ký mới nhất
/// </summary>
/// <param name="id"></param>
/// <param name="username"></param>
/// <param name="fullname"></param>
/// <param name="phone"></param>
/// <param name="email"></param>
/// <param name="cmnd"></param>
/// <param name="regFrom"></param>
/// <param name="regTo"></param>
/// <param name="mobile"></param>
/// <param name="active"></param>
/// <param name="limit"></param>
/// <param name="take"></param>
/// <returns></returns>
public UserInfoSearchResult Search(long id, string username, string fullname, string phone, string email, string cmnd, DateTime regFrom, DateTime regTo, int mobile, bool?active, int limit, int take)
{
UserInfoSearchResult rs = new UserInfoSearchResult();
var lst = new List <UserInfo>();
int totalRecord = 0;
string sql = "select * from newDB.view_user_info where register_date >= '" + regFrom.ToString(Constants.DateFormat) + "' and register_date <= '" + regTo.ToString(Constants.DateFormat) + "'";
string sqlTotalQuery = "select count(*) as totalRecord from newDB.view_user_info where register_date >= '" + regFrom.ToString(Constants.DateFormat) + "' and register_date <= '" + regTo.ToString(Constants.DateFormat) + "'";
if (id > 0)
{
sql += " and id = " + id.ToString();
sqlTotalQuery += " and id = " + id.ToString();
}
if (!String.IsNullOrEmpty(username))
{
sql += " and username like '%" + username + "%'";
sqlTotalQuery += " and username like '%" + username + "%'";
}
if (!String.IsNullOrEmpty(fullname))
{
sql += " and fullname like '%" + fullname + "%'";
sqlTotalQuery += " and fullname like '%" + fullname + "%'";
}
if (!String.IsNullOrEmpty(phone))
{
sql += " and mobile like '%" + phone + "%'";
sqlTotalQuery += " and mobile like '%" + phone + "%'";
}
if (!String.IsNullOrEmpty(email))
{
sql += " and email like '%" + email + "%'";
sqlTotalQuery += " and email like '%" + email + "%'";
}
if (!String.IsNullOrEmpty(cmnd))
{
sql += " and cmnd like '%" + cmnd + "%'";
sqlTotalQuery += " and cmnd like '%" + cmnd + "%'";
}
if (mobile > 0)
{
sql += " and isMobile = " + mobile.ToString();
sqlTotalQuery += " and isMobile = " + mobile.ToString();
}
if (active != null)
{
if (Convert.ToBoolean(active))
{
sql += " and is_active = 1";
sqlTotalQuery += " and is_active = 1";
}
else
{
sql += " and is_active = 2";
sqlTotalQuery += " and is_active = 2";
}
}
sql += " order by id desc";
sql += " limit " + limit + ", " + take;
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
UserInfo user = new UserInfo();
user.uid = MyReader.GetInt64("id");
user.userName = MyReader.GetString("username");
user.fullName = MyReader.GetString("fullname");
user.dateRegister = MyReader.GetDateTime("register_date");
user.isMale = MyReader.GetInt32("sex") == 1;
user.level = MyReader.GetInt32("level_id");
user.cash = MyReader.GetInt64("gameCash");
user.vcash = MyReader.GetInt64("vCash");
user.playsNumber = MyReader.GetInt32("totalGame");
user.playsWin = MyReader.GetInt32("totalWin");
if (!MyReader.IsDBNull(8))
{
user.email = MyReader.GetString("email");
}
if (!MyReader.IsDBNull(7))
{
user.phone = MyReader.GetString("mobile");
}
if (!MyReader.IsDBNull(21))
{
user.cmnd = MyReader.GetString("cmnd");
}
user.ipAddress = MyReader.GetString("ip");
user.isMobile = MyReader.GetInt32("isMobile");
user.lastLogin = MyReader.GetDateTime("last_login");
lst.Add(user);
}
//close reader cũ
MyReader.Close();
MyCommand = new MySqlCommand(sqlTotalQuery, conn);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32("totalRecord");
}
conn.Close();
rs.data = lst;
rs.totalRecord = totalRecord;
return(rs);
}
/// <summary>
/// Danh sách user nạp thẻ, sms
/// </summary>
/// <param name="from"></param>
/// <param name="to"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public ChargeResult GetCharged(DateTime from, DateTime to, int skip, int take)
{
var rs = new ChargeResult();
var data = new List <ChargeItem>();
int totalRecord = 0;
string sql = "SELECT b.id AS 'user_id', b.username,b.fullname,'card' as 'type', cardNumber, cardSerial, cp,cardPrice, refNo, tranNo, source, dateCreated FROM portal.a_paycard a inner join portal.user b on a.username=b.id where a.cardPrice > 0 and a.dateCreated >= @date1 and dateCreated <= @date2 union all SELECT b.id AS 'user_id', b.username, b.fullname, 'sms' AS 'type', '' as 'cardNumber','' as 'cardSerial', telco as 'cp',amount as 'cardPrice', '' as 'refNo', '' as 'tranNo', '' as 'source' , responeTime as 'dateCreated' FROM portal.a_smsplus a INNER JOIN portal.user b ON a.targetUser=b.username WHERE amount > 0 AND a.responeTime >= @date3 AND a.responeTime <= @date4 ORDER BY dateCreated DESC LIMIT @skip, @take";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("date1", from.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date2", to.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date3", from.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date4", to.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
int stt = 1;
while (MyReader.Read())
{
ChargeItem item1 = new ChargeItem();
item1.Stt = stt;
item1.uid = MyReader.GetInt64("user_id");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
item1.type = MyReader.GetString("type");
item1.cardNumber = MyReader.GetString("cardNumber");
item1.cardSerial = MyReader.GetString("cardSerial");
item1.telco = MyReader.GetString("cp");
item1.Price = MyReader.GetInt64("cardPrice");
item1.refNo = MyReader.GetString("refNo");
item1.tranNo = MyReader.GetString("tranNo");
item1.source = MyReader.GetString("source");
item1.time = MyReader.GetDateTime("dateCreated");
item1.timeString = item1.time.ToString("dd-MM-yyyy");
data.Add(item1);
stt++;
}
MyReader.Close();
sql = "Select count(*) as totalRecord from portal.a_paycard where cardPrice > 0 and dateCreated >= @date1 and dateCreated <= @date2";
MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("date1", from.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date2", to.ToString(Constants.DateFormat));
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord += MyReader.GetInt32("totalRecord");
}
MyReader.Close();
sql = "Select count(*) as totalRecord from portal.a_smsplus WHERE amount > 0 AND responeTime >= @date3 AND responeTime <= @date4";
MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("date3", from.ToString(Constants.DateFormat));
MyCommand.Parameters.AddWithValue("date4", to.ToString(Constants.DateFormat));
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord += MyReader.GetInt32("totalRecord");
}
MyReader.Close();
conn.Close();
rs.data = data;
rs.totalRecord = totalRecord;
return(rs);
}
/// <summary>
/// Duyệt một yêu cầu đổi thưởng nào đó
/// </summary>
/// <param name="id"></param>
/// <param name="approval"></param>
/// <returns></returns>
public bool Approval(long id, bool approval)
{
var rs = false;
//
if (approval)
{
//lấy mã thẻ, cập nhật history, chèn sms offline
int valueCard = 0;
int telcoId = 0;
int user_id = 0;
string sql = "SELECT user_id, valueCard, telcoId FROM newDB.game_history WHERE id = @id";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("id", id);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
valueCard = MyReader.GetInt32("valueCard");
telcoId = MyReader.GetInt32("telcoId");
user_id = MyReader.GetInt32("user_id");
}
MyReader.Close();
//lấy mã thẻ từ hệ thống
string sqlCardInfo = "SELECT * FROM portal.exchangeCardInfo WHERE value = '" + valueCard + "' AND telcoId = '" + telcoId + "' AND used = 0 LIMIT 0,1";
MyCommand = new MySqlCommand(sqlCardInfo, conn);
MyReader = MyCommand.ExecuteReader();
int cardId = 0;
string cardSerial = "";
string cardNo = "";
bool hetThe = true;
while (MyReader.Read())
{
cardId = MyReader.GetInt32("id");
cardSerial = MyReader.GetString("serial");
cardNo = MyReader.GetString("cardNo");
hetThe = false;
}
MyReader.Close();
if (hetThe)
{
rs = false;
}
else
{
//cập nhật mã thẻ đã sử dụng
string sqlUpdateCard = "UPDATE portal.exchangeCardInfo set used = 1, dateUse='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE id='" + cardId + "'";
MyCommand = new MySqlCommand(sqlUpdateCard, conn);
int rowUpdate = MyCommand.ExecuteNonQuery();
if (rowUpdate > 0)
{
//update thẻ này được sử dụng cho user nào
string sqlUpdateLog = "INSERT INTO portal.exchangeHistory(cardId, userId) VALUES ('" + cardId + "','" + user_id + "')";
MyCommand = new MySqlCommand(sqlUpdateLog, conn);
rowUpdate = MyCommand.ExecuteNonQuery();
//cập nhật lại bản ghi là đã duyệt
string sqlUpdateHistory = "UPDATE newDB.game_history set description = @description, trans_type = @trans_type WHERE id = @id";
MyCommand = new MySqlCommand(sqlUpdateHistory, conn);
MyCommand.Parameters.AddWithValue("description", "Duyệt đổi thẻ cào " + cardSerial);
MyCommand.Parameters.AddWithValue("trans_type", 16);
MyCommand.Parameters.AddWithValue("id", id);
rowUpdate = MyCommand.ExecuteNonQuery();
//cập nhật vào mess offline
string telcoName = "";
sql = "select * from portal.exchangeTelco WHERE id = '" + telcoId + "'";
MyCommand = new MySqlCommand(sql, conn);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
telcoName = MyReader.GetString("name");
}
MyReader.Close();
sql = "insert into newDB.offlinemessage (userIDSend,userIDReceive,mes,datetimeSend) values (@userIDSend,@userIDReceive,@mes,now());";
MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("userIDSend", 8934);
MyCommand.Parameters.AddWithValue("userIDReceive", user_id);
MyCommand.Parameters.AddWithValue("mes", "Duyệt đổi thẻ, Nhà mạng " + telcoName + ", Mệnh giá " + valueCard + " VND, Mã thẻ: " + cardNo + ", Serial: " + cardSerial + " lúc " + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"));
rowUpdate = MyCommand.ExecuteNonQuery();
if (rowUpdate > 0)
{
rs = true;
}
}
}
conn.Close();
}
else
{
//hoàn trả tiền
long cashReturn = 0;
int user_id = 0;
string sql = "SELECT * FROM newDB.game_history WHERE id = @id";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("id", id);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
cashReturn = MyReader.GetInt32("cash");
user_id = MyReader.GetInt32("user_id");
}
MyReader.Close();
if (cashReturn < 0)
{
cashReturn = cashReturn * (-1);
}
//lấy ra số tiền hiện tại của user
long currentCash = 0;
sql = "SELECT gameCash FROM newDB.g_user WHERE user_id = @user_id";
MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("user_id", user_id);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
currentCash = MyReader.GetInt64("gameCash");
}
MyReader.Close();
//Cập nhật số tiền
string sqlUpdateCash = "UPDATE newDB.g_user SET gameCash = gameCash + " + cashReturn + " WHERE user_id='" + user_id + "' ";
MyCommand = new MySqlCommand(sqlUpdateCash, conn);
int rowUpdate = MyCommand.ExecuteNonQuery();
if (rowUpdate > 0)
{
long beforCash = currentCash;
long afterCash = beforCash + cashReturn;
sql = "INSERT into newDB.game_history (user_id,cash,current_cash,description,game_id,trans_type,tax,taxPercent,before_cash) values (@user_id,@cash,@current_cash,@description,@game_id, @trans_type,@tax,@taxPercent,@before_cash);";
MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("user_id", user_id);
MyCommand.Parameters.AddWithValue("cash", cashReturn);
MyCommand.Parameters.AddWithValue("current_cash", afterCash);
MyCommand.Parameters.AddWithValue("description", "Hoàn lại tiền vì không duyệt đổi thưởng");
MyCommand.Parameters.AddWithValue("game_id", 0);
MyCommand.Parameters.AddWithValue("trans_type", 16);
MyCommand.Parameters.AddWithValue("tax", 0);
MyCommand.Parameters.AddWithValue("taxPercent", 0);
MyCommand.Parameters.AddWithValue("before_cash", beforCash);
rowUpdate = MyCommand.ExecuteNonQuery();
if (rowUpdate > 0)
{
//xóa record yêu cầu đổi thưởng cũ đi
sql = "DELETE from newDB.game_history where id = @id";
MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("id", id);
rowUpdate = MyCommand.ExecuteNonQuery();
if (rowUpdate > 0)
{
rs = true;
}
}
}
conn.Close();
}
return(rs);
}