private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//connection to database
string Conn = "datasource=localhost;port=3306;username=root;password=;database=sbc;sslMode=none";
string numberPlate = numberPlateInput.Text;
//retrieve records from database
MySqlConnection MyConn = new MySqlConnection(Conn);
try
{
MyConn.Open();
MySqlDataReader MyReader;
string Query = "SELECT * FROM bus WHERE NumberPlate='" + numberPlate + "';";
MySqlCommand cmd = new MySqlCommand(Query, MyConn);
MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
MyReader = cmd.ExecuteReader();
MyReader.Read();
categoryInput.Text = MyReader.GetString("Category");
passengerPermitInput.Text = MyReader.GetString("PassengerPermit");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
private void cmb_inv_order_customer_SelectedIndexChanged(object sender, EventArgs e)
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.customers where customer_name ='" + cmb_inv_order_customer.Text + "';";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(query, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
custormer_id = MyReader.GetString("cus_id");
}
ConDataBase.Close();
lbl_rs.Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Messege", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
void Fillcombo_order_customers()
{
string con = "server=localhost;user id=root;password=;database=youken_springs";
string quarry = "select * from youken_springs.customers;";
MySqlConnection ConDataBase = new MySqlConnection(con);
MySqlCommand cmdDataBase = new MySqlCommand(quarry, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
string cuName = MyReader.GetString("customer_name");
cmb_inv_order_customer.Items.Add(cuName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//PESTAÑA "NUEVO", OPCION PARA AGREGAR NUEVOS PEDIDOS
private void agregarPedido_btn_Click_1(object sender, EventArgs e) // Hacer click en "Nuevo", hacer visible la tab pedidos y cargar los clientes existentes
{
DateTime today = DateTime.Today;
tab_pedidos.Visible = true;
tab_pedidos.SelectTab(0);
comboBox_producto1.SelectedIndex = -1;
textBox_cliente.Clear();
textBox_cantidad1.Clear();
cargar_productos();
folio_pedido();
MySqlConnection conexion = Conectar();
String sentencia = "SELECT nombre FROM clientes";
MySqlCommand comando = new MySqlCommand(sentencia, conexion);
conexion.Open();
comando.ExecuteNonQuery();
MySqlDataReader MyReader;
MyReader = comando.ExecuteReader();
while (MyReader.Read())
{
autotext_clientes.Add(MyReader.GetString(0));
}
textBox_cliente.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
textBox_cliente.AutoCompleteSource = AutoCompleteSource.CustomSource;
textBox_cliente.AutoCompleteCustomSource = autotext_clientes;
MyReader.Close();
conexion.Close();
}
// ***************************************************** PESTAÑA MODIFICAR PEDIDO *******************************************************************
private void pestañaModificarPedido_Btn_Click(object sender, EventArgs e)
{
tab_pedidos.Visible = true;
tab_pedidos.SelectTab(1);
textBox_buscarFolio.Clear();
cargar_productos();
MySqlConnection conexion = Conectar();
String sentencia = "SELECT nombre FROM clientes";
MySqlCommand comando = new MySqlCommand(sentencia, conexion);
conexion.Open();
comando.ExecuteNonQuery();
MySqlDataReader MyReader;
MyReader = comando.ExecuteReader();
while (MyReader.Read())
{
autotext_clientes.Add(MyReader.GetString(0));
}
textBox_clienteEncontrado.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
textBox_clienteEncontrado.AutoCompleteSource = AutoCompleteSource.CustomSource;
textBox_clienteEncontrado.AutoCompleteCustomSource = autotext_clientes;
MyReader.Close();
conexion.Close();
}
private void cmb_b_m_suppler_SelectedIndexChanged(object sender, EventArgs e)
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.suplier where suplier_name='" + cmb_b_m_suppler.Text + "';";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(quarry, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
string suName = MyReader.GetString("suplier_name");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Messege",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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);
}
}
private void btn_pro_upcontact_Click(object sender, EventArgs e)
{
pnl_hrm_detail_contact.Visible = true;
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.contact_detail where e_id ='" + lbl_pro_em_id.Text + "';";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(query, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
txt_land.Text = MyReader.GetString("land");
txt_mob.Text = MyReader.GetString("mobile");
txt_email.Text = MyReader.GetString("email");
txt_conAdd.Text = MyReader.GetString("contact_add");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Messege", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public ViewDriver()
{
InitializeComponent();
string Conn = "datasource=localhost;port=3306;username=root;password=;database=sbc;sslMode=none";
//get driver names from database and populate to combo box
string Query = "SELECT Name FROM busowner;";
MySqlConnection MyConn = new MySqlConnection(Conn);
MySqlCommand MyComd = new MySqlCommand(Query, MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = MyComd.ExecuteReader();
//for each return result of busowner name, add into combo box
while (MyReader.Read())
{
this.driverOwnerNameInput.Items.AddRange(new object[] {
MyReader.GetString("Name")
});
}
MyConn.Close();
}
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 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 <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);
}
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);
}
void Fillcombo_suppler()
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.suplier;";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(quarry, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
string suName = MyReader.GetString("suplier_name");
cmb_b_m_suppler.Items.Add(suName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//**************recuperation du nom et prenom de la table Perso dans le form Accident**************//
private void btnRecVict_Click(object sender, EventArgs e)
{
string sqlreq = "select Perso_Nom,Perso_Prenom From Perso INNER JOIN Accident ON Perso.Perso_Matricule = Accident.IdAccident where Perso.Perso_Matricule=" + Convert.ToInt32(textBoxMatr.Text) + "";
SqlDataReader MyReader;
SqlCommand cmd = new SqlCommand(sqlreq, chaineConn.Cnx);
chaineConn.Cnx.Open();
try
{
MyReader = cmd.ExecuteReader();
if (MyReader.HasRows)
{
while (MyReader.Read())
{
string nom = MyReader.GetString(0);
string prenom = MyReader.GetString(1);
textBoxNom.Text = nom;
textBoxPrenom.Text = prenom;
}
}
MyReader.Close();
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
chaineConn.Cnx.Close();
}
void FillcomboMaterial()
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.meterial;";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(quarry, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
string mtName = MyReader.GetString("meterial_name");
com_metirial.Items.Add(mtName);
string umtName = MyReader.GetString("meterial_name");
cmb_m_update.Items.Add(umtName);
string dmtName = MyReader.GetString("meterial_name");
cmb_m_delete.Items.Add(dmtName);
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
void FillcomboProduct()
{
string Constring = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.products;";
MySqlConnection ConDataBase = new MySqlConnection(Constring);
MySqlCommand cmdDataBase = new MySqlCommand(quarry, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
string pdName1 = MyReader.GetString("name");
cmb_product_name1.Items.Add(pdName1);
string pdName2 = MyReader.GetString("name");
cmb_product_name2.Items.Add(pdName2);
string pdName3 = MyReader.GetString("name");
cmb_product_name3.Items.Add(pdName3);
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
////////////////////////////////////////////////////////////order///////////////////////////////////////
void FillComboOrders()
{
string con = "datasource=localhost;port=3306;username=root;password="******"select * from youken_springs.products;";
MySqlConnection ConDataBase = new MySqlConnection(con);
MySqlCommand cmdDataBase = new MySqlCommand(query, ConDataBase);
MySqlDataReader MyReader;
try
{
ConDataBase.Open();
MyReader = cmdDataBase.ExecuteReader();
while (MyReader.Read())
{
string prname = MyReader.GetString("name");
cmb_Product.Items.Add(prname);
}
ConDataBase.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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);
}
}
/// <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);
}
private void comboboxpatient_name_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
clear_data();
string sql = "SELECT patient_contact_no,patient_age,patient_blood_group,patient_address,time_of_admission,patient_sex,referred_by,patient_occupation,patient_id from hopedatabase.patient_data where patient_name='" + comboboxpatient_name.SelectedItem + "';";
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MySqlDataReader MyReader;
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
patient_contact_no.Text = MyReader.GetString(0).ToString();
patient_age.Text = MyReader.GetString(1).ToString();
patient_bloodgroup.Text = MyReader.GetString(2).ToString();
patient_address.Text = MyReader.GetString(3).ToString();
time_of_admission.Text = MyReader.GetString(4).ToString();
patient_sex.Text = MyReader.GetString(5).ToString();
referred_by.Text = MyReader.GetString(6).ToString();
patient_occupation.Text = MyReader.GetString(7).ToString();
selected_id = MyReader.GetString(8).ToString();
}
MyReader.Close();
}
catch (Exception eee)
{
MessageBox.Show(eee.Message.ToString());
}
}
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();
}
}
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);
}
/// <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 b.id,b.username, b.fullname,sum(a.cash) as total_cash_win from newDB.game_history a inner join newDB.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" +
" limit 0, @top";
MySqlConnection conn = new MySqlConnection(Constants.DBConnection);
MySqlCommand MyCommand = new MySqlCommand(sql, conn);
MyCommand.Parameters.AddWithValue("zoneId", zoneId);
MyCommand.Parameters.AddWithValue("top", top);
MySqlDataReader MyReader;
conn.Open();
MyReader = MyCommand.ExecuteReader();
while (MyReader.Read())
{
UserInfo u = new UserInfo();
u.uid = MyReader.GetInt64("id");
u.userName = MyReader.GetString("username");
u.fullName = MyReader.GetString("fullname");
u.cashWin = MyReader.GetInt64("total_cash_win");
rs.Add(u);
}
MyReader.Close();
conn.Close();
return(rs);
}
/*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 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 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 checkStatusCancelled()
* {
*
* }*/
private void initField(String bookingID)
{
//connect db
string Conn = "datasource=localhost;port=3306;username=root;password=;database=sbc;sslMode=none";
//retrieve records from database
MySqlConnection MyConn = new MySqlConnection(Conn);
try
{
MyConn.Open();
MySqlDataReader MyReader;
String Query = "SELECT * FROM booking where BookingID ='" + bookingID + "';";
MySqlCommand cmd = new MySqlCommand(Query, MyConn);
MyReader = cmd.ExecuteReader();
if (MyReader.Read())
{
//populate data
bookingIDInput.Text = MyReader.GetString("BookingID");
customerNameInput.Text = MyReader.GetString("customerPhoneNo");
//initialize timedate
string currentDate = MyReader.GetString("BookingDate");
string[] tempCurrDate = currentDate.Split('/');
int currMonth = int.Parse(tempCurrDate[1]); //getting the from Month
int currDay = int.Parse(tempCurrDate[0]); //getting the from Day
int currYear = int.Parse(tempCurrDate[2]); //getting the from year
DateTime dateNow = new DateTime(currYear, currMonth, currDay);
dateInput.Value = dateNow;
departureInput.Text = MyReader.GetString("DepartLocation");
arrivalInput.Text = MyReader.GetString("ArrivalLocation");
//status combo box
this.statusCombo.Items.AddRange(new object[] {
"Booked",
"Booked but not paid",
"Cancelled"
});
passengerNoInput.Text = MyReader.GetString("passengerNumber");
remarksInput.Text = MyReader.GetString("remarks");
statusCombo.Text = MyReader.GetString("status");
MyReader.Close();
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
private void calculateRefund(string bookingID)
{
try
{
//Connection to database
string Conn = "datasource=localhost; port=3306;username=root;password=;database=sbc; sslMode=none";
string Query = "SELECT * FROM booking WHERE BookingID ='" + bookingID + "';";
MySqlConnection MyConn = new MySqlConnection(Conn);
MySqlCommand MyComd = new MySqlCommand(Query, MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = MyComd.ExecuteReader();
if (MyReader.Read())
{
string bookingDate = MyReader.GetString("BookingDate");
string[] tempDate = bookingDate.Split('/');
int expMonth = int.Parse(tempDate[1]); //getting the to Month
int expDay = int.Parse(tempDate[0]); //getting the to Day
int expYear = int.Parse(tempDate[2]); //getting the to year
DateTime bookedDate = new DateTime(expYear, expMonth, expDay);
DateTime currDate = DateTime.Now;
int refundAmount;
if (bookedDate.AddDays(-3) > currDate)
{
//Full refund
refundAmount = 200;
}
else if (bookedDate.AddDays(-2) > currDate)
{
//half refund
refundAmount = 200 / 2;
}
else
{
//no refund allowed
refundAmount = 0;
}
//populate input box
bookingIDInput.Text = bookingID;
dateInput.Text = bookingDate;
refundInput.Text = refundAmount.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}