/// <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); }