public List <AnnouncementItem> GetAll(int currentRecord = 0, int numberRecord = 10) { var lst = new List <AnnouncementItem>(); string sql = "SELECT * FROM [newDB].[dbo].[event] where [IsDelete] = 0 order by id desc OFFSET " + currentRecord + " ROWS FETCH NEXT " + numberRecord + "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 item = new AnnouncementItem { ID = MyReader.GetInt32(MyReader.GetOrdinal("id")), DisplayOrder = MyReader.GetInt32(MyReader.GetOrdinal("DisplayOrder")), begin_time = MyReader.GetDateTime(MyReader.GetOrdinal("DateStart")), end_time = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")), Subject = MyReader.GetString(MyReader.GetOrdinal("name")), Content = MyReader.GetString(MyReader.GetOrdinal("content")), }; lst.Add(item); } MyReader.Close(); conn.Close(); return(lst); }
/*update 24/03*/ public List <GameTextItem> GetAll() { var lst = new List <GameTextItem>(); string sql = "SELECT * FROM [portal].[dbo].chuchaychay ORDER BY id DESC"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { var item = new GameTextItem { ID = MyReader.GetInt32(MyReader.GetOrdinal("id")), Title = MyReader.GetString(MyReader.GetOrdinal("Title")), Link = MyReader.GetString(MyReader.GetOrdinal("Link")), DataStart = MyReader.GetDateTime(MyReader.GetOrdinal("DataStart")), DateEnd = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")), IsDelete = MyReader.GetBoolean(MyReader.GetOrdinal("IsDelete")), Order = MyReader.GetInt32(MyReader.GetOrdinal("Orders")), DataStartstring = MyReader.GetDateTime(MyReader.GetOrdinal("DataStart")).ToShortDateString(), DateEndstring = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")).ToShortDateString() }; lst.Add(item); } MyReader.Close(); conn.Close(); return(lst); }
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 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 top " + top + " b.id,b.username, b.fullname,sum(a.cash) as total_cash_win from [portal].[dbo].game_history a inner join [portal].[dbo].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"; var conn = new SqlConnection(Constants.DBConnection); var MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("zoneId", zoneId); MyCommand.Parameters.AddWithValue("top", top); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { var u = new UserInfo(); u.uid = MyReader.GetInt64(MyReader.GetOrdinal("id")); u.userName = MyReader.GetString(MyReader.GetOrdinal("username")); u.fullName = MyReader.GetString(MyReader.GetOrdinal("fullname")); u.cashWin = MyReader.GetInt64(MyReader.GetOrdinal("total_cash_win")); rs.Add(u); } MyReader.Close(); conn.Close(); return(rs); }
public AnnouncementItem GetOne(int ID) { var item = new AnnouncementItem(); string sql = "SELECT * FROM [newDB].[dbo].[event] WHERE id = @id"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("id", ID); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { item.ID = MyReader.GetInt32(MyReader.GetOrdinal("id")); item.begin_time = MyReader.GetDateTime(MyReader.GetOrdinal("DateStart")); item.end_time = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")); item.Subject = MyReader.GetString(MyReader.GetOrdinal("name")); item.UrlImage = MyReader.GetString(MyReader.GetOrdinal("UrlImage")); item.Content = MyReader.GetString(MyReader.GetOrdinal("content")); item.DoiTuong = MyReader.GetString(MyReader.GetOrdinal("DoiTuong")); item.ThoiGian = MyReader.GetString(MyReader.GetOrdinal("ThoiGian")); item.GameID = MyReader.GetInt32(MyReader.GetOrdinal("GameID")); item.DisplayOrder = MyReader.GetInt32(MyReader.GetOrdinal("DisplayOrder")); item.begin_timestring = item.begin_time.ToString(Constants.DateFormat); item.end_timestring = item.end_time.ToString(Constants.DateFormat); } MyReader.Close(); conn.Close(); return(item); }
public List <ConfigMiniPokerItem> GetConfigXeng() { var rs = new List <ConfigMiniPokerItem>(); var sql = "SELECT top 1 * FROM [newDB].[dbo].configQuayXeng 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 u = new ConfigMiniPokerItem { Id = MyReader.GetInt32(MyReader.GetOrdinal("id")), a = MyReader.GetInt32(MyReader.GetOrdinal("a")), a1 = MyReader.GetInt32(MyReader.GetOrdinal("a1")), a2 = MyReader.GetInt32(MyReader.GetOrdinal("a2")), a3 = MyReader.GetInt32(MyReader.GetOrdinal("a3")), a4 = MyReader.GetInt32(MyReader.GetOrdinal("a4")), a5 = MyReader.GetInt32(MyReader.GetOrdinal("a5")), a6 = MyReader.GetInt32(MyReader.GetOrdinal("a6")) }; rs.Add(u); } MyReader.Close(); conn.Close(); return(rs); }
public RoomItem GetDetailRoom(int roomId) { var lst = new RoomItem(); var sql = ""; sql = "Select * from [portal].[dbo].room r WHERE r.Id = @roomId"; var conn = new SqlConnection(Constants.DBConnection); var myCommand = new SqlCommand(sql, conn); myCommand.Parameters.AddWithValue("roomId", roomId); SqlDataReader MyReader; conn.Open(); MyReader = myCommand.ExecuteReader(); while (MyReader.Read()) { var item = new RoomItem { id = MyReader.GetInt32(MyReader.GetOrdinal("id")), name = MyReader.GetString(MyReader.GetOrdinal("name")), state = MyReader.GetInt32(MyReader.GetOrdinal("state")), game_id = MyReader.GetInt32(MyReader.GetOrdinal("game_id")), min_bet = MyReader.GetInt32(MyReader.GetOrdinal("min_bet")), }; lst = item; } return(lst); }
public GameTextItem GetOne(int ID) { var item = new GameTextItem(); string sql = "SELECT * FROM [portal].[dbo].chuchaychay WHERE id = @id"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("id", ID); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { item = new GameTextItem { ID = MyReader.GetInt32(MyReader.GetOrdinal("id")), Title = MyReader.GetString(MyReader.GetOrdinal("Title")), Link = MyReader.GetString(MyReader.GetOrdinal("Link")), DataStart = MyReader.GetDateTime(MyReader.GetOrdinal("DataStart")), DateEnd = MyReader.GetDateTime(MyReader.GetOrdinal("DateEnd")), IsDelete = MyReader.GetBoolean(MyReader.GetOrdinal("IsDelete")), Order = MyReader.GetInt32(MyReader.GetOrdinal("Orders")), }; } MyReader.Close(); conn.Close(); return(item); }
public List <GameGuideItem> GameGuideGetAll() { var lst = new List <GameGuideItem>(); string sql = "SELECT gui.*, ga.Name FROM [portal].[dbo].gameGuide gui LEFT JOIN [portal].[dbo].game ga ON ga.ID = gui.game_id ORDER BY gui.id DESC"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { var item = new GameGuideItem { id = MyReader.GetInt32(MyReader.GetOrdinal("id")), game = MyReader.GetString(MyReader.GetOrdinal("Name")), description = MyReader.GetString(MyReader.GetOrdinal("description")), game_id = MyReader.GetInt32(MyReader.GetOrdinal("game_id")), }; lst.Add(item); } MyReader.Close(); conn.Close(); return(lst); }
public GameGuideItem GameGuideGetOne(int id) { var item = new GameGuideItem(); string sql = "SELECT * FROM [portal].[dbo].gameGuide WHERE id = @id"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("id", id); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { item = new GameGuideItem { id = MyReader.GetInt32(MyReader.GetOrdinal("id")), description = MyReader.GetString(MyReader.GetOrdinal("description")), game_id = MyReader.GetInt32(MyReader.GetOrdinal("game_id")), }; } MyReader.Close(); conn.Close(); return(item); }
public ConfigMiniPokerItem GetConfigXengDetail(int id) { var rs = new ConfigMiniPokerItem(); var sql = "SELECT * FROM [newDB].[dbo].configQuayXeng where id = " + id; var conn = new SqlConnection(Constants.DBConnection); var MyCommand = new SqlCommand(sql, conn); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { rs = new ConfigMiniPokerItem { Id = MyReader.GetInt32(MyReader.GetOrdinal("id")), a = MyReader.GetInt32(MyReader.GetOrdinal("a")), a1 = MyReader.GetInt32(MyReader.GetOrdinal("a1")), a2 = MyReader.GetInt32(MyReader.GetOrdinal("a2")), a3 = MyReader.GetInt32(MyReader.GetOrdinal("a3")), a4 = MyReader.GetInt32(MyReader.GetOrdinal("a4")), a5 = MyReader.GetInt32(MyReader.GetOrdinal("a5")), a6 = MyReader.GetInt32(MyReader.GetOrdinal("a6")) }; } MyReader.Close(); conn.Close(); return(rs); }
private void comboBox_misCultivos_SelectedIndexChanged(object sender, EventArgs e) { Random random = new Random(); int num = random.Next(100); textBox1.Text = num.ToString() + " %"; DateTime today = DateTime.Today; double diff = 0; double porcentaje = 0; MySqlConnection conexion = Conectar(); String sentencia = "SELECT fecha_siembra FROM mis_cultivos WHERE nombre = '" + comboBox_misCultivos.Text + "'"; MySqlCommand comando = new MySqlCommand(sentencia, conexion); conexion.Open(); comando.ExecuteNonQuery(); MySqlDataReader MyReader; MyReader = comando.ExecuteReader(); while (MyReader.Read()) { var dateString = MyReader.GetDateTime(MyReader.GetOrdinal("fecha_siembra")).ToString("yyyy/MM/dd"); DateTime myDate = DateTime.Parse(dateString); diff = (today - myDate).TotalDays; } MyReader.Close(); String sentencia2 = "UPDATE mis_cultivos SET dias_transcurridos = " + diff + " WHERE nombre = '" + comboBox_misCultivos.Text + "'"; MySqlCommand comando2 = new MySqlCommand(sentencia2, conexion); comando2.ExecuteNonQuery(); String sentencia3 = "SELECT dias_desarrollo FROM mis_cultivos WHERE nombre = '" + comboBox_misCultivos.Text + "'"; MySqlCommand comando3 = new MySqlCommand(sentencia3, conexion); comando3.ExecuteNonQuery(); MyReader = comando3.ExecuteReader(); while (MyReader.Read()) { double dias = MyReader.GetDouble("dias_desarrollo"); porcentaje = (diff * 100) / dias; } porcentaje = Math.Round(porcentaje, 2); MyReader.Close(); conexion.Close(); descripcion.Text = "Su cultivo tiene " + diff.ToString() + " días de haberse sembrado, lo que corresponde a " + porcentaje + "% de su estación de crecimiento. Recuerda que en esta etapa es importante...(recomendación) " + "y debería parecerse a la siguiente imagen: "; }
/// <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); }
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); }
public string GetText() { var rs = ""; string sql = "Select noticeText from [portal].[dbo].cp where id = 1"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { rs = MyReader.GetString(MyReader.GetOrdinal("noticeText")); } MyReader.Close(); conn.Close(); 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); }
public List <ConfigMiniPokerItem> GetConfigTaiXiu() { var rs = new List <ConfigMiniPokerItem>(); var sql = "SELECT * FROM [newDB].[dbo].configBotTaixiu"; 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 ConfigMiniPokerItem { a1 = MyReader.GetInt32(MyReader.GetOrdinal("a1")), a2 = MyReader.GetInt32(MyReader.GetOrdinal("a2")), a3 = MyReader.GetInt32(MyReader.GetOrdinal("a3")), a4 = MyReader.GetInt32(MyReader.GetOrdinal("a4")), a5 = MyReader.GetInt32(MyReader.GetOrdinal("a5")), a6 = MyReader.GetInt32(MyReader.GetOrdinal("a6")), a7 = MyReader.GetInt32(MyReader.GetOrdinal("a7")), a8 = MyReader.GetInt32(MyReader.GetOrdinal("a8")), a9 = MyReader.GetInt32(MyReader.GetOrdinal("a9")), a10 = MyReader.GetInt32(MyReader.GetOrdinal("a10")), a11 = MyReader.GetInt32(MyReader.GetOrdinal("a11")), a12 = MyReader.GetInt32(MyReader.GetOrdinal("a12")), a13 = MyReader.GetInt32(MyReader.GetOrdinal("a13")), a14 = MyReader.GetInt32(MyReader.GetOrdinal("a14")), a15 = MyReader.GetInt32(MyReader.GetOrdinal("a15")), a16 = MyReader.GetInt32(MyReader.GetOrdinal("a16")), a17 = MyReader.GetInt32(MyReader.GetOrdinal("a17")), a18 = MyReader.GetInt32(MyReader.GetOrdinal("a18")), a19 = MyReader.GetInt32(MyReader.GetOrdinal("a19")), }; rs.Add(u); } MyReader.Close(); conn.Close(); return(rs); }
public List <UserInfo> RealTimeAccPlayingByGameId(out int total, int gameId, int top) { total = 0; var rs = new List <UserInfo>(); var sql = "select top " + top + " b.username, b.fullname from [portal].[dbo].play_log a inner join [portal].[dbo].view_user_info b on a.user_id = b.id where is_play = 1 and a.game_id = @gameId" + " group by b.username, b.fullname"; var sqlTotalQuery = "select count(a.id) as totalRecord from [portal].[dbo].play_log a inner join [portal].[dbo].view_user_info b on a.user_id = b.id where is_play = 1 and a.game_id = @gameId group by a.user_id"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("gameId", gameId); MyCommand.Parameters.AddWithValue("top", top); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { var u = new UserInfo { userName = MyReader.GetString(MyReader.GetOrdinal("username")), fullName = MyReader.GetString(MyReader.GetOrdinal("fullname")) }; rs.Add(u); } MyReader.Close(); MyCommand = new SqlCommand(sqlTotalQuery, conn); MyCommand.Parameters.AddWithValue("gameId", gameId); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { total = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord")); } conn.Close(); conn.Close(); return(rs); }
public GameItem SelectOne(int id) { var user = new GameItem(); var sql = "select * from [portal].[dbo].game where id = " + id; var conn = new SqlConnection(Constants.DBConnection); var myCommand = new SqlCommand(sql, conn); SqlDataReader MyReader; conn.Open(); MyReader = myCommand.ExecuteReader(); while (MyReader.Read()) { user.zoneID = MyReader.GetInt32(MyReader.GetOrdinal("id")); user.name = MyReader.GetString(MyReader.GetOrdinal("name")); user.displayStatus = MyReader.GetInt32(MyReader.GetOrdinal("displayStatus")); user.gameOrder = MyReader.GetInt32(MyReader.GetOrdinal("gameOrder")); } //close reader cũ MyReader.Close(); conn.Close(); return(user); }
/// <summary> /// Lấy ra Danh sách các Game /// </summary> /// <returns></returns> public List <GameItem> GetAll() { var rs = new List <GameItem>(); const string sql = "select * from [newDB].[dbo].[game]"; var conn = new SqlConnection(Constants.DBConnection); var myCommand = new SqlCommand(sql, conn); SqlDataReader MyReader; conn.Open(); MyReader = myCommand.ExecuteReader(); while (MyReader.Read()) { var item = new GameItem { zoneID = MyReader.GetInt32(MyReader.GetOrdinal("id")), name = MyReader.GetString(MyReader.GetOrdinal("name")), displayStatus = MyReader.GetInt32(MyReader.GetOrdinal("displayStatus")), gameOrder = MyReader.GetInt32(MyReader.GetOrdinal("gameOrder")) }; rs.Add(item); } conn.Close(); return(rs); }
public List <RoomItem> GetAll(int gameId) { var lst = new List <RoomItem>(); var sql = ""; if (gameId > 0) { sql = "Select r.*, g.name as 'game_name' from [portal].[dbo].room r inner join [portal].[dbo].game g on g.id = r.game_id WHERE r.game_id = @game_id"; } else { sql = "Select r.*, g.name as 'game_name' from [portal].[dbo].room r inner join [portal].[dbo].game g on g.id = r.game_id order by id desc"; } var conn = new SqlConnection(Constants.DBConnection); var myCommand = new SqlCommand(sql, conn); myCommand.Parameters.AddWithValue("game_id", gameId); SqlDataReader MyReader; conn.Open(); MyReader = myCommand.ExecuteReader(); while (MyReader.Read()) { var item = new RoomItem { id = MyReader.GetInt32(MyReader.GetOrdinal("id")), name = MyReader.GetString(MyReader.GetOrdinal("name")), state = MyReader.GetInt32(MyReader.GetOrdinal("state")), game_id = MyReader.GetInt32(MyReader.GetOrdinal("game_id")), min_bet = MyReader.GetInt32(MyReader.GetOrdinal("min_bet")), game_name = MyReader.GetString(MyReader.GetOrdinal("game_name")) }; lst.Add(item); } return(lst); }
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); }
/// <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 var valueCard = 0; var telcoId = 0; var user_id = 0; var sql = "SELECT user_id, valueCard, telcoId FROM [newDB].[dbo].game_history WHERE id = @id"; var conn = new SqlConnection(Constants.DBConnection); var MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("id", id); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { valueCard = MyReader.GetInt32(MyReader.GetOrdinal("valueCard")); telcoId = MyReader.GetInt32(MyReader.GetOrdinal("telcoId")); user_id = MyReader.GetInt32(MyReader.GetOrdinal("user_id")); } MyReader.Close(); //lấy mã thẻ từ hệ thống var sqlCardInfo = "SELECT top 1 * FROM [portal].[dbo].exchangeCardInfo WHERE value = '" + valueCard + "' AND telcoId = '" + telcoId + "' AND used = 0"; MyCommand = new SqlCommand(sqlCardInfo, conn); MyReader = MyCommand.ExecuteReader(); var cardId = 0; var cardSerial = ""; var cardNo = ""; var hetThe = true; while (MyReader.Read()) { cardId = MyReader.GetInt32(MyReader.GetOrdinal("id")); cardSerial = MyReader.GetString(MyReader.GetOrdinal("serial")); cardNo = MyReader.GetString(MyReader.GetOrdinal("cardNo")); hetThe = false; } MyReader.Close(); if (hetThe) { rs = false; } else { //cập nhật mã thẻ đã sử dụng string sqlUpdateCard = "UPDATE [portal].[dbo].exchangeCardInfo set used = 1, dateUse='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE id='" + cardId + "'"; MyCommand = new SqlCommand(sqlUpdateCard, conn); int rowUpdate = MyCommand.ExecuteNonQuery(); if (rowUpdate > 0) { //update thẻ này được sử dụng cho user nào var sqlUpdateLog = "INSERT INTO [portal].[dbo].exchangeHistory(cardId, userId) VALUES ('" + cardId + "','" + user_id + "')"; MyCommand = new SqlCommand(sqlUpdateLog, conn); rowUpdate = MyCommand.ExecuteNonQuery(); //cập nhật lại bản ghi là đã duyệt var sqlUpdateHistory = "UPDATE [newDB].[dbo].game_history set description = @description, trans_type = @trans_type WHERE id = @id"; MyCommand = new SqlCommand(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 var telcoName = ""; sql = "select * from [portal].[dbo].exchangeTelco WHERE id = '" + telcoId + "'"; MyCommand = new SqlCommand(sql, conn); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { telcoName = MyReader.GetString(MyReader.GetOrdinal("name")); } MyReader.Close(); sql = "insert into [newDB].[dbo].offlinemessage (userIDSend,userIDReceive,mes,datetimeSend) values (@userIDSend,@userIDReceive,@mes,now());"; MyCommand = new SqlCommand(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; var user_id = 0; var sql = "SELECT * FROM [newDB].[dbo].game_history WHERE id = @id"; var conn = new SqlConnection(Constants.DBConnection); var MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("id", id); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { cashReturn = MyReader.GetInt32(MyReader.GetOrdinal("cash")); user_id = MyReader.GetInt32(MyReader.GetOrdinal("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].[dbo].g_user WHERE user_id = @user_id"; MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("user_id", user_id); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { currentCash = MyReader.GetInt64(MyReader.GetOrdinal("gameCash")); } MyReader.Close(); //Cập nhật số tiền var sqlUpdateCash = "UPDATE [newDB].[dbo].g_user SET gameCash = gameCash + " + cashReturn + " WHERE user_id='" + user_id + "' "; MyCommand = new SqlCommand(sqlUpdateCash, conn); var rowUpdate = MyCommand.ExecuteNonQuery(); if (rowUpdate > 0) { var beforCash = currentCash; var afterCash = beforCash + cashReturn; sql = "INSERT into [newDB].[dbo].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 SqlCommand(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].[dbo].game_history where id = @id"; MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("id", id); rowUpdate = MyCommand.ExecuteNonQuery(); if (rowUpdate > 0) { rs = true; } } } conn.Close(); } 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].[dbo].a_paycard a inner join [portal].[dbo].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].[dbo].a_smsplus a INNER JOIN [portal].[dbo].user b ON a.targetUser=b.username WHERE amount > 0 AND a.responeTime >= @date3 AND a.responeTime <= @date4 ORDER BY dateCreated DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(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); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); int stt = 1; while (MyReader.Read()) { ChargeItem item1 = new ChargeItem(); item1.Stt = stt; item1.uid = MyReader.GetInt64(MyReader.GetOrdinal("user_id")); item1.username = MyReader.GetString(MyReader.GetOrdinal("username")); item1.fullname = MyReader.GetString(MyReader.GetOrdinal("fullname")); item1.type = MyReader.GetString(MyReader.GetOrdinal("type")); item1.cardNumber = MyReader.GetString(MyReader.GetOrdinal("cardNumber")); item1.cardSerial = MyReader.GetString(MyReader.GetOrdinal("cardSerial")); item1.telco = MyReader.GetString(MyReader.GetOrdinal("cp")); item1.Price = MyReader.GetInt64(MyReader.GetOrdinal("cardPrice")); item1.refNo = MyReader.GetString(MyReader.GetOrdinal("refNo")); item1.tranNo = MyReader.GetString(MyReader.GetOrdinal("tranNo")); item1.source = MyReader.GetString(MyReader.GetOrdinal("source")); item1.time = MyReader.GetDateTime(MyReader.GetOrdinal("dateCreated")); item1.timeString = item1.time.ToString("dd-MM-yyyy"); data.Add(item1); stt++; } MyReader.Close(); sql = "Select count(*) as totalRecord from [portal].[dbo].a_paycard where cardPrice > 0 and dateCreated >= @date1 and dateCreated <= @date2"; MyCommand = new SqlCommand(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(MyReader.GetOrdinal("totalRecord")); } MyReader.Close(); sql = "Select count(*) as totalRecord from [portal].[dbo].a_smsplus WHERE amount > 0 AND responeTime >= @date3 AND responeTime <= @date4"; MyCommand = new SqlCommand(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(MyReader.GetOrdinal("totalRecord")); } MyReader.Close(); conn.Close(); rs.data = data; rs.totalRecord = totalRecord; return(rs); }
public List <CardItem> GetAllCard(out int total, int skip, int take, int used = 0, int telcoId = 0, int value = 0, string seri = "") { var rs = new List <CardItem>(); var sql = "SELECT * FROM [portal].[dbo].exchangeCardInfo "; var sqlTotalQuery = "SELECT Count(id) as totalRecord FROM [portal].[dbo].exchangeCardInfo"; sql += " where used = " + used; sqlTotalQuery += " where used = " + used; if (telcoId > 0) { sql += " and telcoId = " + telcoId; sqlTotalQuery += " and telcoId = " + telcoId; } if (value > 0) { sql += " and value = " + value; sqlTotalQuery += " and value = " + value; } if (!string.IsNullOrEmpty(seri)) { sql += " and serial = '" + seri + "'"; sqlTotalQuery += " and serial = '" + value + "'"; } sql += " ORDER BY 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 CardItem { id = MyReader.GetInt32(MyReader.GetOrdinal("id")), serial = MyReader.GetString(MyReader.GetOrdinal("serial")), //cardNo = MyReader.GetString(MyReader.GetOrdinal("cardNo"), dateInput = MyReader.GetDateTime(MyReader.GetOrdinal("dateInput")), dateInputString = MyReader.GetDateTime(MyReader.GetOrdinal("dateInput")).ToShortDateString(), dateExpiredString = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")).ToShortDateString(), dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")), used = MyReader.GetByte(MyReader.GetOrdinal("used")), value = MyReader.GetInt32(MyReader.GetOrdinal("value")), telcoId = MyReader.GetInt32(MyReader.GetOrdinal("telcoId")) }; if (used > 0) { u.dateUse = MyReader.GetDateTime(MyReader.GetOrdinal("dateUse")); u.dateUseString = MyReader.GetDateTime(MyReader.GetOrdinal("dateUse")).ToShortDateString(); } rs.Add(u); } MyReader.Close(); MyCommand = new SqlCommand(sqlTotalQuery, conn); MyReader = MyCommand.ExecuteReader(); total = 0; while (MyReader.Read()) { total = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord")); } 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); }
public GiftCodeResult GetAll(bool used, int skip, int take) { var rs = new GiftCodeResult(); List <GiftCodeItem> data = new List <GiftCodeItem>(); int totalRecord = 0; string sql = ""; if (used) { if (used == true) { sql = "SELECT u.username, gc.* FROM [newDB].[dbo].giftcode gc inner join [newDB].[dbo].g_user u on gc.user_id = u.user_id WHERE gc.user_id > 0 ORDER BY gc.id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; } else { sql = "SELECT u.username, gc.* FROM [newDB].[dbo].giftcode gc inner join [newDB].[dbo].g_user u on gc.user_id = u.user_id ORDER BY gc.id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; } SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("skip", skip); MyCommand.Parameters.AddWithValue("take", take); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { var item = new GiftCodeItem { code = MyReader.GetString(MyReader.GetOrdinal("code")), ID = MyReader.GetInt32(MyReader.GetOrdinal("id")), dateCreated = MyReader.GetDateTime(MyReader.GetOrdinal("dateCreated")), dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")), name = MyReader.GetString(MyReader.GetOrdinal("name")), value = MyReader.GetInt32(MyReader.GetOrdinal("value")), user_id = MyReader.GetInt32(MyReader.GetOrdinal("user_id")), username = MyReader.GetString(MyReader.GetOrdinal("username")), fullname = MyReader.GetString(MyReader.GetOrdinal("fullname")), isVCash = MyReader.GetInt32(MyReader.GetOrdinal("isVCash")) }; item.sdateCreated = item.dateCreated.ToShortDateString(); item.sdateExpired = item.dateExpired.ToShortDateString(); data.Add(item); } MyReader.Close(); sql = "Select count(*) as totalRecord from [portal].[dbo].giftcode WHERE used = 1"; MyCommand = new SqlCommand(sql, conn); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord")); } MyReader.Close(); conn.Close(); } else { sql = "SELECT * FROM [portal].[dbo].giftcode WHERE used = 0 ORDER BY dateCreated DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("skip", skip); MyCommand.Parameters.AddWithValue("take", take); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { GiftCodeItem item = new GiftCodeItem(); item.code = MyReader.GetString(MyReader.GetOrdinal("code")); item.ID = MyReader.GetInt32(MyReader.GetOrdinal("id")); item.dateCreated = MyReader.GetDateTime(MyReader.GetOrdinal("dateCreated")); item.dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired"));; item.used = false; item.name = MyReader.GetString(MyReader.GetOrdinal("name")); item.value = MyReader.GetInt32(MyReader.GetOrdinal("value")); item.isVCash = MyReader.GetInt32(MyReader.GetOrdinal("isVCash")); item.user_id = 0; item.username = ""; item.fullname = ""; item.sdateCreated = item.dateCreated.ToShortDateString(); item.sdateExpired = item.dateExpired.ToShortDateString(); data.Add(item); } MyReader.Close(); sql = "Select count(*) as totalRecord from [portal].[dbo].giftcode WHERE used = 0"; MyCommand = new SqlCommand(sql, conn); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord")); } MyReader.Close(); conn.Close(); } rs.totalRecord = totalRecord; rs.data = data; return(rs); }
private void comboBox_misCultivos_SelectedIndexChanged(object sender, EventArgs e) { Random random = new Random(); int num = random.Next(100); textBox1.Text = num.ToString() + " %"; DateTime today = DateTime.Today; double diff = 0; double porcentaje = 0; String rec = ""; MySqlConnection conexion = Conectar(); String sentencia = "SELECT fecha_siembra FROM mis_cultivos WHERE nombre = '" + comboBox_misCultivos.Text + "'"; MySqlCommand comando = new MySqlCommand(sentencia, conexion); conexion.Open(); comando.ExecuteNonQuery(); MySqlDataReader MyReader; MyReader = comando.ExecuteReader(); while (MyReader.Read()) { var dateString = MyReader.GetDateTime(MyReader.GetOrdinal("fecha_siembra")).ToString("yyyy/MM/dd"); DateTime myDate = DateTime.Parse(dateString); diff = (today - myDate).TotalDays; } MyReader.Close(); String sentencia2 = "UPDATE mis_cultivos SET dias_transcurridos = " + diff + " WHERE nombre = '" + comboBox_misCultivos.Text + "'"; MySqlCommand comando2 = new MySqlCommand(sentencia2, conexion); comando2.ExecuteNonQuery(); String sentencia3 = "SELECT dias_desarrollo FROM mis_cultivos WHERE nombre = '" + comboBox_misCultivos.Text + "'"; MySqlCommand comando3 = new MySqlCommand(sentencia3, conexion); comando3.ExecuteNonQuery(); MyReader = comando3.ExecuteReader(); while (MyReader.Read()) { double dias = MyReader.GetDouble("dias_desarrollo"); porcentaje = (diff * 100) / dias; } porcentaje = Math.Round(porcentaje, 2); int porcentaje_round = ((int)Math.Round(porcentaje / 10.0)) * 10; MyReader.Close(); String sentencia4 = "SELECT recomendacion FROM " + comboBox_misCultivos.Text + " WHERE crecimiento = " + porcentaje_round + ""; MySqlCommand comando4 = new MySqlCommand(sentencia4, conexion); comando4.ExecuteNonQuery(); MyReader = comando4.ExecuteReader(); while (MyReader.Read()) { rec = MyReader.GetString(0); } MyReader.Close(); String sentencia5 = "SELECT imagen FROM " + comboBox_misCultivos.Text + " WHERE crecimiento = " + porcentaje_round + ""; MySqlCommand comando5 = new MySqlCommand(sentencia5, conexion); comando5.ExecuteNonQuery(); MyReader = comando5.ExecuteReader(); while (MyReader.Read()) { var bytes = (byte[])MyReader[0]; using (MemoryStream ms = new MemoryStream(bytes)) { pictureBox_imagenCrecimiento.Image = Image.FromStream(ms); } } MyReader.Close(); conexion.Close(); descripcion.Text = "Su cultivo tiene " + diff.ToString() + " días de haberse sembrado, lo que corresponde a " + porcentaje + "% de su estación de crecimiento. " + rec + " Su cultivo debería parecerse a la siguiente imagen: "; // pictureBox_imagenCrecimiento.Show(); }