/// <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);
}
/// <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);
}
public PaymentItem PaymentDetail(int id)
{
var rs = new PaymentItem();
var sql = "SELECT us.username, us.fullname, pay.id, pay.buyer_uid, pay.buyer_fullname, pay.buyer_mobile, pay.total_amount, pay.payment_method, pay.bank_code, pay.transaction_status, pay.transaction_id, pay.time_request, pay.time_receive, pay.token FROM [portal].[dbo].a_bankcharge pay " +
" LEFT JOIN [portal].[dbo].user us ON pay.buyer_uid = us.id where pay.id = " + id;
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 PaymentItem
{
id = MyReader.GetInt32(MyReader.GetOrdinal("id")),
buyer_mobile = MyReader.GetString(MyReader.GetOrdinal("buyer_mobile")),
total_amount = MyReader.GetInt32(MyReader.GetOrdinal("total_amount"))
};
u.buyer_mobile = MyReader.GetString(MyReader.GetOrdinal("buyer_mobile"));
u.buyer_fullname = MyReader.GetString(MyReader.GetOrdinal("buyer_fullname"));
u.payment_method = MyReader.GetString(MyReader.GetOrdinal("payment_method"));
u.bank_code = MyReader.GetString(MyReader.GetOrdinal("bank_code"));
u.transaction_status = MyReader.GetString(MyReader.GetOrdinal("transaction_status"));
u.token = MyReader.GetString(MyReader.GetOrdinal("token"));
// index start 0
if (!MyReader.IsDBNull(3))
{
u.buyer_uid = MyReader.GetInt32(MyReader.GetOrdinal("buyer_uid"));
}
if (!MyReader.IsDBNull(11))
{
u.time_request = MyReader.GetDateTime(MyReader.GetOrdinal("time_request"));
u.time_request_string = u.time_request.ToShortDateString();
}
if (!MyReader.IsDBNull(12))
{
u.time_receive = MyReader.GetDateTime(MyReader.GetOrdinal("time_receive"));
u.time_receive_string = u.time_receive.ToShortDateString();
}
if (!MyReader.IsDBNull(10))
{
u.transaction_id = MyReader.GetString(MyReader.GetOrdinal("transaction_id"));
}
if (!MyReader.IsDBNull(0))
{
u.username = MyReader.GetString(MyReader.GetOrdinal("username"));
}
if (!MyReader.IsDBNull(1))
{
u.fullname = MyReader.GetString(MyReader.GetOrdinal("fullname"));
}
rs = u;
}
MyReader.Close();
conn.Close();
return(rs);
}
public List <PaymentItem> GetPayment(out int total, int skip, int take, int uid = 0, string username = "", string date = "", string status = "")
{
var rs = new List <PaymentItem>();
var sql = "SELECT us.username, us.fullname, pay.id, pay.buyer_uid, pay.buyer_fullname, pay.buyer_mobile, pay.total_amount, pay.payment_method, pay.bank_code, pay.transaction_status, pay.transaction_id, pay.time_request, pay.time_receive, pay.token FROM [portal].[dbo].a_bankcharge pay " +
" LEFT JOIN [portal].[dbo].user us ON pay.buyer_uid = us.id where pay.id > 0 ";
var sqlTotalQuery = "SELECT Count(pay.id) as totalRecord FROM [portal].[dbo].a_bankcharge pay LEFT JOIN [portal].[dbo].user us ON pay.buyer_uid = us.id where pay.id > 0 ";
if (uid > 0)
{
sql += " AND pay.buyer_uid = " + uid;
sqlTotalQuery += " AND pay.buyer_uid = " + uid;
}
if (!string.IsNullOrEmpty(username))
{
sql += " AND (LOWER(pay.buyer_mobile) LIKE '%" + username.ToLower() + "%' or LOWER(pay.buyer_fullname) LIKE '%" + username.ToLower() + "%' or LOWER(us.username) LIKE '%" + username.ToLower() + "%' or LOWER(us.fullname) LIKE '%" + username.ToLower() + "%')";
sqlTotalQuery += " AND (LOWER(pay.buyer_mobile) LIKE '%" + username.ToLower() + "%' or LOWER(pay.buyer_fullname) LIKE '%" + username.ToLower() + "%' or LOWER(us.username) LIKE '%" + username.ToLower() + "%' or LOWER(us.fullname) LIKE '%" + username.ToLower() + "%')";
}
if (!string.IsNullOrEmpty(date))
{
var datex = Convert.ToDateTime(date).ToString(Constants.DateFormat);
sql += " AND time_request >= '" + datex + "'";
sqlTotalQuery += " and time_request >= '" + datex + "'";
}
if (!string.IsNullOrEmpty(status))
{
if (status == "00")
{
sql += " AND transaction_status = '00'";
sqlTotalQuery += " and transaction_status = '00'";
}
else
{
sql += " AND transaction_status <> '00'";
sqlTotalQuery += " and transaction_status <> '00'";
}
}
sql += " ORDER BY pay.id desc OFFSET " + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY";
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 PaymentItem();
u.id = MyReader.GetInt32(MyReader.GetOrdinal("id"));
u.buyer_mobile = MyReader.GetString(MyReader.GetOrdinal("buyer_mobile"));
u.total_amount = MyReader.GetInt32(MyReader.GetOrdinal("total_amount"));
u.buyer_mobile = MyReader.GetString(MyReader.GetOrdinal("buyer_mobile"));
u.buyer_fullname = MyReader.GetString(MyReader.GetOrdinal("buyer_fullname"));
u.payment_method = MyReader.GetString(MyReader.GetOrdinal("payment_method"));
u.bank_code = MyReader.GetString(MyReader.GetOrdinal("bank_code"));
u.transaction_status = MyReader.GetString(MyReader.GetOrdinal("transaction_status"));
u.token = MyReader.GetString(MyReader.GetOrdinal("token"));
// index start 0
if (!MyReader.IsDBNull(3))
{
u.buyer_uid = MyReader.GetInt32(MyReader.GetOrdinal("buyer_uid"));
}
if (!MyReader.IsDBNull(11))
{
u.time_request = MyReader.GetDateTime(MyReader.GetOrdinal("time_request"));
u.time_request_string = u.time_request.ToShortDateString();
}
if (!MyReader.IsDBNull(12))
{
u.time_receive = MyReader.GetDateTime(MyReader.GetOrdinal("time_receive"));
u.time_receive_string = u.time_receive.ToShortDateString();
}
if (!MyReader.IsDBNull(10))
{
u.transaction_id = MyReader.GetString(MyReader.GetOrdinal("transaction_id"));
}
if (!MyReader.IsDBNull(0))
{
u.username = MyReader.GetString(MyReader.GetOrdinal("username"));
}
if (!MyReader.IsDBNull(1))
{
u.fullname = MyReader.GetString(MyReader.GetOrdinal("fullname"));
}
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);
}