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);
}
/// <summary>
/// Báo cáo Danh sách user nạp nhiều tiền nhất (theo tổng số tiền Su đã nạp)
/// </summary>
/// <param name="from"></param>
/// <param name="to"></param>
/// <param name="top"></param>
/// <returns></returns>
public List <ReportUserChargeMoneyItem> ReportUserChargedMost(DateTime from, DateTime to, int top)
{
var rs = new List <ReportUserChargeMoneyItem>();
string sql = String.Format("SELECT h.user_id, u.username, u.fullname, u.ip, u.gameCash, u.vCash, SUM(h.cash) AS 'SuNap' FROM newDB.game_history h INNER JOIN newDB.view_user_info u ON h.user_id = u.id WHERE h.trans_type = 4 and h.time > '{0}' and h.time < '{1}' group by h.user_id, u.username, u.fullname order by SUM(h.cash) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
ReportUserChargeMoneyItem item1 = new ReportUserChargeMoneyItem();
item1.user_id = MyReader.GetInt32("user_id");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
item1.ip = MyReader.GetString("ip");
item1.gameCash = MyReader.GetInt64("gameCash");
item1.vCash = MyReader.GetInt64("vCash");
if (String.IsNullOrEmpty(item1.fullname))
{
item1.fullname = item1.username;
}
item1.moenyCharged = MyReader.GetInt64("SuNap");
rs.Add(item1);
}
MyReader.Close();
conn.Close();
return(rs);
}
public 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);
}
protected void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
// Use the session state object variable to retrieve the user's record
SqlDataReader MyReader;
SqlConnection myConnection = new SqlConnection(SqlDataSource1.ConnectionString);
SqlCommand myCommand = new SqlCommand(SqlDataSource1.SelectCommand);
myCommand.Connection = myConnection;
myCommand.Parameters.AddWithValue("@email", Session["UserEmail"]);
myConnection.Open();
try
{
// populate the labels if the retrieve is successful
MyReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (MyReader.Read())
{
txtFName.Text = MyReader.GetString(2).Trim();
txtLName.Text = MyReader.GetString(3).Trim();
lblPassword.Text = MyReader.GetString(1).Trim();
txtEmail.Text = MyReader.GetString(0).Trim();
txtYear.Text = Convert.ToString(MyReader.GetInt32(4));
}
}
catch (Exception)
{
// what to do if the retrieval fails
Response.Redirect("Login.aspx");
}
myConnection.Close();
}
}
private void cmb_Product_SelectedIndexChanged(object sender, EventArgs e)
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.products where name ='" + cmb_Product.Text + "';";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(query, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
product_id = MyReader.GetString("product_id");
product_uprice = MyReader.GetInt32("unit_price").ToString();
// txt_p_custemer.Text = product_uprice;
}
ConDataBase.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Messege", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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 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 void getNoOfLeaveDays()
{
try
{
String a = "select COALESCE(sum(NoOfDays),0) from itp.leave_view where EmpId = '" + Connection.getUserIdFromConnectionString() + "' and Status = 'Accepted' and Type = 'Annual' ;";
String c = "select COALESCE(sum(NoOfDays),0) from itp.leave_view where EmpId = '" + Connection.getUserIdFromConnectionString() + "' and Status = 'Accepted' and Type = 'Casual';";
MySqlConnection MyConn = ConnectionOld.getConnection();
MySqlCommand SelectCommandAnnual = new MySqlCommand(a, MyConn);
MySqlCommand SelectCommandCasual = new MySqlCommand(c, MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = SelectCommandAnnual.ExecuteReader();
if (MyReader.Read())
{
labRemAnnual.Text = (14 - MyReader.GetInt32(0)).ToString();
}
MyConn.Close();
MyReader = null;
MyConn.Open();
MyReader = SelectCommandCasual.ExecuteReader();
if (MyReader.Read())
{
int x;
x = MyReader.GetInt32(0);
labRemCasual.Text = (7 - x).ToString();
}
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
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 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);
}
/*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 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 <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);
}
private void cmb_m_delete_SelectedIndexChanged(object sender, EventArgs e)
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.meterial where meterial_name ='" + cmb_m_delete.Text + "';";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(quarry, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
string dmid = MyReader.GetInt32("meterial_id").ToString();
lbl_m_d_id.Text = dmid;
string dmtName = MyReader.GetString("meterial_name");
lbl_m_name_delete.Text = dmtName;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Messege",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btn_next1_Click(object sender, EventArgs e)
{
if (Txt_countrylogo.Text != "")
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"SELECT * FROM youken_springs.employee ORDER BY e_id DESC LIMIT 1 ;";
MySqlCommand cmdDataBase1 = new MySqlCommand(query1, ConDataBase);
MySqlDataReader MyReader;
ConDataBase.Open();
cmdDataBase1.ExecuteNonQuery();
MyReader = cmdDataBase1.ExecuteReader();
while (MyReader.Read())
{
string e_id = MyReader.GetInt32("e_id").ToString();
fullname = MyReader.GetString("e_full_name");
lbl_hrm_reg_jname.Text = fullname;
lbl_hrm_job_e_id.Text = e_id;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Messege",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
pan_reg3.Visible = true;
// pan_reg1.Visible = true;
pan_reg2.Visible = false;
// pan_reg1.Visible = false;
// panel_dep_dependen.Visible = true;
// txt_em_name.Text = lbl_hrm_reg_jname.Text;
}
else
{
MessageBox.Show("Please Enter Employee Profile Picture!!", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}
/// <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);
}
/// <summary>
/// Danh sách lịch sử các trận đã chơi theo game Id
/// </summary>
/// <param name="zoneId"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public GameHistoryResult GetGameHistoryByZoneID(int zoneId, int skip, int take)
{
var rs = new GameHistoryResult();
var lst = new List <GameHistoryItem>();
int totalRecord = 0;
string sql = "SELECT a.*, b.username, b.fullname FROM newDB.game_history a inner join newDB.view_user_info b on a.user_id = b.id WHERE game_id = @zoneId ORDER BY id DESC LIMIT @skip, @take";
string sqlTotalQuery = "SELECT count(*) as totalRecord FROM newDB.game_history WHERE game_id = @zoneId";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyCommand.Parameters.AddWithValue("skip", skip);
MyCommand.Parameters.AddWithValue("take", take);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
GameHistoryItem item1 = new GameHistoryItem();
item1.id = MyReader.GetInt64("id");
item1.user_id = MyReader.GetInt64("user_id");
item1.cash = MyReader.GetInt64("cash");
item1.current_cash = MyReader.GetInt64("current_cash");
item1.description = MyReader.GetString("description");
item1.game_id = MyReader.GetInt32("game_id");
item1.trans_type = MyReader.GetInt32("trans_type");
item1.time = MyReader.GetDateTime("time");
item1.before_cash = MyReader.GetInt64("before_cash");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
lst.Add(item1);
}
//close reader cũ
MyReader.Close();
MyCommand = new MySqlCommand(sqlTotalQuery, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
totalRecord = MyReader.GetInt32("totalRecord");
}
conn.Close();
rs.data = lst;
rs.totalRecord = totalRecord;
return(rs);
}
//public void getMonthComboValues()
//{
// String[] m = {"January","February","March","April","May","June","July","August","September","October","November","December"};
// MySqlConnection MyConn = ConnectionOld.getConnection();
// MySqlCommand SelectCommand = new MySqlCommand("select distinct Month from ot;", MyConn);
// MySqlDataReader MyReader;
// MyConn.Open();
// MyReader = SelectCommand.ExecuteReader();
// while (MyReader.Read())
// {
// String mon = MyReader.GetString("Month");
// for (int i = 0; i < m.Length; i++)
// {
// if (mon != m[0])
// {
// combo_Month.Items.Add(mon);
// }
// }
// }
//}
public void getDetails()
{
MySqlConnection MyConn = ConnectionOld.getConnection();
MySqlCommand SelectCommand = new MySqlCommand("select * from itp.employee where EmpId = '" + Connection.getUserIdFromConnectionString() + "' ;", MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = SelectCommand.ExecuteReader();
while (MyReader.Read())
{
Emp_txt.Text = MyReader.GetInt32("EmpId").ToString();
First_txt.Text = MyReader.GetString("FirstName");
Last_txt.Text = MyReader.GetString("LastName");
dateTimePicker1.Text = MyReader.GetString("DOB");
if ((MyReader.GetString("Nationality") == "SriLankan"))
{
combo_Nat.Text = "SriLankan";
}
else
{
combo_Nat.Text = "Foreigner";
Nat_txt.Text = MyReader.GetString("Nationality");
}
NIC_txt.Text = MyReader.GetString("NIC");
Add_txt.Text = MyReader.GetString("Address");
Email_txt.Text = MyReader.GetString("Email");
Mob_txt.Text = MyReader.GetString("MobileNo");
Posi_combo.Text = MyReader.GetString("Position");
Dep_txt.Text = MyReader.GetString("DepId");
textBox5.Text = MyReader.GetDouble("BasicSalary").ToString();
String gender = MyReader.GetChar("Gender").ToString();
if (gender == "M")
{
radioButton1.Checked = true;
}
else
{
radioButton2.Checked = true;
}
}
}
/// <summary>
/// Select một user theo user_id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public UserInfo SelectOne(long id)
{
var user = new UserInfo();
string sql = "select * from newDB.view_user_info where id = " + id;
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
user.uid = MyReader.GetInt64("id");
user.userName = MyReader.GetString("username");
user.fullName = MyReader.GetString("fullname");
user.dateRegister = MyReader.GetDateTime("register_date");
user.isMale = MyReader.GetInt32("sex") == 1;
user.level = MyReader.GetInt32("level_id");
user.cash = MyReader.GetInt64("gameCash");
user.vcash = MyReader.GetInt64("vCash");
user.playsNumber = MyReader.GetInt32("totalGame");
user.playsWin = MyReader.GetInt32("totalWin");
if (!MyReader.IsDBNull(8))
{
user.email = MyReader.GetString("email");
}
if (!MyReader.IsDBNull(7))
{
user.phone = MyReader.GetString("mobile");
}
if (!MyReader.IsDBNull(21))
{
user.cmnd = MyReader.GetString("cmnd");
}
user.ipAddress = MyReader.GetString("ip");
user.isMobile = MyReader.GetInt32("isMobile");
user.lastLogin = MyReader.GetDateTime("last_login");
}
//close reader cũ
MyReader.Close();
conn.Close();
return(user);
}
private void btn_hrm_emreg1_depends_Click(object sender, EventArgs e)
{
if ((txt_noChild.Text != "") && (txt_noChild.Text != "0"))
{
pan_reg1.Visible = false;
string Constring = "datasource=localhost;port=3306;username=root;password="******"SELECT * FROM youken_springs.employee ORDER BY e_id DESC LIMIT 1 ;";
MySqlCommand cmdDataBase1 = new MySqlCommand(query1, ConDataBase);
MySqlDataReader MyReader;
ConDataBase.Open();
cmdDataBase1.ExecuteNonQuery();
MyReader = cmdDataBase1.ExecuteReader();
while (MyReader.Read())
{
string e_id = MyReader.GetInt32("e_id").ToString();
fullname = MyReader.GetString("e_full_name");
lbl_dpn_name.Text = fullname;
lbl_dpn_id.Text = e_id;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Messege",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("you havent dependents or does not add the fild!!", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}
/* public bool checkMobile()
* {
* if (Mob_txt.TextLength < 7 && Mob_txt.TextLength > 15)
* {
* return false;
* }
* return true;
* }
*/
public int getNewId()
{
String s = "select max(EmpId) from itp.employee;";
MySqlConnection MyConn = ConnectionOld.getConnection();
MySqlCommand SelectCommand = new MySqlCommand(s, MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = SelectCommand.ExecuteReader();
while (MyReader.Read())
{
ss = MyReader.GetInt32("max(EmpId)");
}
int n = ss + 1;
return(n);
}
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);
}
/// <summary>
/// Lấy ra Danh sách các Game
/// </summary>
/// <returns></returns>
public List <GameItem> GetAll()
{
var rs = new List <GameItem>();
string sql = "select * from newDB.game";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
GameItem item = new GameItem();
item.zoneID = MyReader.GetInt32("id");
item.name = MyReader.GetString("name");
item.displayStatus = MyReader.GetInt32("displayStatus");
item.gameOrder = MyReader.GetInt32("gameOrder");
rs.Add(item);
}
conn.Close();
return(rs);
}
private void com_metirial_SelectedIndexChanged(object sender, EventArgs e)
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.meterial where meterial_name ='" + com_metirial.Text + "';";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(quarry, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
mtName = MyReader.GetString("meterial_name");
mtquantity = MyReader.GetInt32("quantity").ToString();
string mid = MyReader.GetInt32("meterial_id").ToString();
lbl_m_name.Text = mtName;
lbl_currnt_stock.Text = mtquantity;
lbl_mid.Text = mid;
currmt_qun = int.Parse(mtquantity);
//try { mtquantity = MyReader.GetInt32("quantity").ToString(); }
//catch (Exception ex) { MessageBox.Show(ex.Message); }
//= int.Parse(lbl_currnt_stock.Text);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Messege",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
protected void submit_Click(object sender, EventArgs e)
{
String ReturnEmail = "";
String ReturnPassword = "";
int ReturnYoB = 0;
SqlDataReader MyReader;
SqlConnection myConnection = new SqlConnection(SqlDataSource1.ConnectionString);
SqlCommand myCommand = new SqlCommand(SqlDataSource1.SelectCommand);
myCommand.Connection = myConnection;
myCommand.Parameters.AddWithValue("@UserEmail", UserEmail.Text);
myCommand.Parameters.AddWithValue("@UserPassword", UserPassword.Text);
myConnection.Open();
MyReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (MyReader.Read())
{
ReturnEmail = MyReader.GetString(2).Trim() + " " + MyReader.GetString(3).Trim();
ReturnPassword = MyReader.GetString(1).Trim();
ReturnYoB = MyReader.GetInt32(4);
}
myConnection.Close();
if (UserPassword.Text == "" || UserEmail.Text == "")
{
ErrorMessage.Text = "Fields cannot be left blank";
}
else if (UserPassword.Text.Trim() == ReturnPassword)
{
Session["SessionEmail"] = UserEmail.Text.Trim();
Session["YoB"] = ReturnYoB;
Session["authenticated"] = "true";
Response.Redirect("http://atlas.itds.unt.edu/Rodriguez/Welcome.aspx");
}
else
{
ErrorMessage.Text = "<script>if(confirm('These credentials do not apear to match our records. Would you like to register?')){window.location.replace('http://atlas.itds.unt.edu/Rodriguez/Register.aspx');}</script>";
}
}
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>
/// Báo cáo Danh sách top người chơi thắng nhiều nhất (tính theo số trận thắng)
/// </summary>
/// <param name="from"></param>
/// <param name="to"></param>
/// <param name="top"></param>
/// <param name="vCash"></param>
/// <returns></returns>
public List <ReportUserPlayGameItem> ReportUserPlayWin(DateTime from, DateTime to, int top, bool vCash)
{
var rs = new List <ReportUserPlayGameItem>();
string sql = String.Format("select user_id, username, fullname, ip, gameCash, vCash, count(0) as 'totalGame' from newDB.view_user_history_total where is_active = 1 and time > '{0}' and time < '{1}' and cash > 0 group by user_id, username, fullname, ip order by COUNT(0) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);
if (vCash)
{
sql = String.Format("select user_id, username, fullname, ip, gameCash, vCash, count(0) as 'totalGame' from newDB.view_user_history_total_vCash where is_active = 1 and time > '{0}' and time < '{1}' and cash > 0 group by user_id, username, fullname, ip order by COUNT(0) desc limit 0, {2}", from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"), top);
}
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
ReportUserPlayGameItem item1 = new ReportUserPlayGameItem();
item1.user_id = MyReader.GetInt32("user_id");
item1.username = MyReader.GetString("username");
item1.fullname = MyReader.GetString("fullname");
item1.ip = MyReader.GetString("ip");
item1.gameCash = MyReader.GetInt64("gameCash");
item1.vCash = MyReader.GetInt64("vCash");
if (String.IsNullOrEmpty(item1.fullname))
{
item1.fullname = item1.username;
}
item1.totalGame = MyReader.GetInt32("totalGame");
rs.Add(item1);
}
//close reader cũ
MyReader.Close();
conn.Close();
return(rs);
}
