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