// public String DateOfBirth { get; set; }
// RoomType { get; set; }
// public int Room { get; set; }
public void InsertCustomer()
{
String q = "insert into customers(FirstName,LastName,NIC,ContactNumber,Address,Gender,checkin,checkout,adults,children,DeluxeRoom,SuiteRoom,StandardRoom) values ('" + FirstName + "','" + LastName + "','" + NIC + "','" + ContactNo + "','" + Address + "','" + Gender + "','" + this.checkin + "','" + this.checkout + "','" + adults + "','" + children + "','" + DeluxeRoom + "','" + SuiteRoom + "','" + StandardRoom + "')";
try
{
using (DBConect db = new DBConect())
{
bool ok = db.insert(q);
if (ok)
{
MessageBox.Show("User inserted successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("User insertion failed", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
}
public void InsertCustomer()
{
String q = "insert into Customers(FirstName,LastName,ContactNumber,Address,Gender,DateOfBirth,RoomType,Rooms) values ('" + FirstName + "','" + LastName + "','" + ContactNo + "','" + Address + "','" + Gender + "','" + this.DateOfBirth + "','" + RoomType + "','" + Room + "')";
try
{
using (DBConect db = new DBConect())
{
bool ok = db.insert(q);
if (ok)
{
MessageBox.Show("User inserted successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("User insertion failed", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
}
private DataTable getAvailabilityForRoomType(String type)
{
String q;
if (type.Equals(""))
{
q = "select r.RoomID as 'Room ID',r.Type as 'Room Type',c.FirstName as 'First Name', b.CheckIn as 'Check In', b.CheckOut as 'Check Out' from rooms r,room_booking b,customers c where r.RoomID = b.RoomID AND c.ContactID = b.CustomerID";
}
else
{
q = "select r.RoomID as 'Room ID',r.Type as 'Room Type',c.FirstName as 'First Name', b.CheckIn as 'Check In', b.CheckOut as 'Check Out' from rooms r,room_booking b,customers c where r.RoomID = b.RoomID AND c.ContactID = b.CustomerID AND r.Type = '" + type + "'";
}
DataTable dt = new DataTable();
using (DBConect db = new DBConect())
{
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
dt.Load(r);
return(dt);
}
}
public void UpdateCustomer()
{
String q = "update customers set FirstName ='" + FirstName + "',LastName = '" + LastName + "', NIC = '" + NIC + "' ,ContactNumber = '" + ContactNo + "' , Address = '" + Address + "' , Gender = '" + Gender + "',checkin = '" + this.checkin + "',checkout = '" + this.checkout + "',adults = '" + adults + "',children = '" + children + "',DeluxeRoom = '" + DeluxeRoom + "',SuiteRoom = '" + SuiteRoom + "',StandardRoom = '" + StandardRoom + "' Where ContactID = '" + ContactID + "'";
try
{
using (DBConect db = new DBConect())
{
bool ok = db.update(q);
if (ok)
{
MessageBox.Show("User updated successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("User updation failed", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
// throw new NotImplementedException();
}
public ContactClass Search(String condition)
{
using (DBConect db = new DBConect())
{
String q = "select * from Customers where " + condition;
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
this.ContactID = Int32.Parse(r[0].ToString());
this.FirstName = r[1].ToString();
this.LastName = r[2].ToString();
this.NIC = r[3].ToString();
this.ContactNo = r[4].ToString();
this.Address = r[5].ToString();
this.Gender = r[6].ToString();
this.checkin = r[7].ToString();
this.checkout = r[8].ToString();
this.adults = r[9].ToString();
this.children = r[10].ToString();
this.DeluxeRoom = r[11].ToString();
this.SuiteRoom = r[12].ToString();
this.StandardRoom = r[13].ToString();
//this.Room = Int32.Parse(r[8].ToString());
}
return(this);
}
}
public void UpdateCustomer()
{
String q = "update Customers set FirstName ='" + FirstName + "',LastName = '" + LastName + "' ,ContactNumber = '" + ContactNo + "' ,Address = '" + Address + "' ,Gender = '" + Gender + "',DateOfBirth = '" + this.DateOfBirth + "',RoomType = '" + RoomType + "',Rooms = '" + Room + "' Where ContactID = '" + ContactID + "'";
try
{
using (DBConect db = new DBConect())
{
bool ok = db.update(q);
if (ok)
{
MessageBox.Show("User updated successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("User updation failed", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
// throw new NotImplementedException();
}
public void deleteCustomer()
{
String q = "delete from Customers Where ContactID = '" + ContactID + "'";
try
{
using (
DBConect db = new DBConect())
{
bool ok = db.delete(q);
if (ok)
{
MessageBox.Show("User Deleted successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("User deleted failed", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
}
public DataTable loadAllCustomers()
{
DataTable dt = new DataTable();
using (DBConect db = new DBConect())
{
String q = "select ContactID as 'Customer ID ',FirstName as 'First Name',LastName as 'Last Name' from Customers";
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
dt.Load(r);
}
return(dt);
}
public DataTable loadAllBookings()
{
DataTable dt = new DataTable();
using (DBConect db = new DBConect())
{
String q = "select customerNic as 'customerNic ',Room type as 'Room type',RoomNo as 'Last Name' from Room_Booking";
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
dt.Load(r);
}
return(dt);
}
private void loadRoomBookings()
{
roomBookList.Clear();
using (DBConect db = new DBConect())
{
String q = "select * from room_booking where RoomType='" + cmbRoomTypes.Text + "'";
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DateTime checkin = Convert.ToDateTime(reader["checkin"].ToString());
DateTime checkOut = Convert.ToDateTime(reader["checkout"].ToString());
roomBookList.Add(new RoomBooking(reader["RoomType"].ToString(), reader["RoomNo"].ToString(), checkin, checkOut));
}
}
}
private void getRoomPrice()
{
String q = "select Price from rooms where Type = '" + cmbRoomType.Text + "'";
using (DBConect db = new DBConect())
{
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
while (r.Read())
{
lblPrice1.Text = (Double.Parse(r[0].ToString()) * Int32.Parse(cmbRoom.Text)).ToString();
}
}
}
}
private void insertBooking(String nic, CheckBox c, String checkin, String checkout)
{
if (c.Checked)
{
using (DBConect db = new DBConect())
{
char[] x = c.Text.ToCharArray();
string roomType = x[0].ToString();
string roomNo = x[1].ToString();
String q = "INSERT INTO `room_booking`(`customerNic`, `RoomType`, `RoomNo`, `checkin`, `checkout`) VALUES ('" + nic + "','" + roomType + "','" + roomNo + "','" + checkin + "','" + checkout + "')";
MySqlCommand cmd = new MySqlCommand(q, db.con);
cmd.ExecuteNonQuery();
}
}
}
private List <String> getRoomNameList(String type)
{
List <String> result = new List <String>();
using (DBConect db = new DBConect())
{
String q = "select * from rooms where Type='" + type + "'";
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
result.Add(r["Type"].ToString() + r["RoomNo"].ToString());
}
}
return(result);
}
public void InsertRooms()
{
//int ContactId = txtUn;
//String FirstName = txtFn;
//String LastName = txtLn;
//String ContactNo = txtCn;
//String Address = txtAd;
//String Gender = txtGn;
//String DateOfBirth = txtDob;
//String RoomType = txtRt;
//String Room = txtRo;
String q = "insert into Rooms(CheckIn,CheckOut,Roomtypess,RoomNo) values ('" + this.CheckIn + "','" + this.CheckOut + "','" + Roomtypess + "','" + RoomNo + "')";
try
{
using (DBConect db = new DBConect())
{
bool ok = db.insert(q);
/* if (ok)
* {
*
* MessageBox.Show("Room inserted successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
* }
* else
* {
* MessageBox.Show("Room insertion failed", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
* }*/
//SqlCommand cmd = new SqlCommand(q, db.con);
//cmd.ExecuteNonQuery();
//MessageBox.Show("User inserted successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
//clearTexts();
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
}
public ContactClass Search(String condition)
{
using (DBConect db = new DBConect()) {
String q = "select * from Customers where " + condition;
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
this.ContactID = Int32.Parse(r[0].ToString());
this.FirstName = r[1].ToString();
this.LastName = r[2].ToString();
this.ContactNo = r[3].ToString();
this.Address = r[4].ToString();
this.Gender = r[5].ToString();
this.DateOfBirth = r[6].ToString();
this.RoomType = r[7].ToString();
this.Room = Int32.Parse(r[8].ToString());
}
return(this);
}
}
private void addUser()
{
String userName = txtUn.Text;
String password = txtPw.Text;
String q = "insert into Users(userName,password) values ('" + userName + "','" + password + "')";
try
{
using (DBConect db = new DBConect())
{
db.insert(q);
//SqlCommand cmd = new SqlCommand(q, db.con);
//cmd.ExecuteNonQuery();
//MessageBox.Show("User inserted successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
//clearTexts();
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
}
/*
* private void btnCheckForRoomType_Click(object sender, EventArgs e)
* {
* String q;
* if (type.Equals(""))
* {
* q = "select r.RoomID as 'Room ID',r.Type as 'Room Type',c.FirstName as 'First Name', b.CheckIn as 'Check In', b.CheckOut as 'Check Out' from rooms r,room_booking b,customers c where r.RoomID = b.RoomID AND c.ContactID = b.CustomerID";
* }
* else
* {
* q = "select r.RoomID as 'Room ID',r.Type as 'Room Type',c.FirstName as 'First Name', b.CheckIn as 'Check In', b.CheckOut as 'Check Out' from rooms r,room_booking b,customers c where r.RoomID = b.RoomID AND c.ContactID = b.CustomerID AND r.Type = '" + type + "'";
* }
*
*
* DataTable dt = new DataTable();
*
* using (DBConect db = new DBConect())
* {
* MySqlCommand cmd = new MySqlCommand(q, db.con);
* MySqlDataReader r = cmd.ExecuteReader();
*
* dt.Load(r);
* return dt;
* }*/
private void getRoomPrice()
{
String q = "select Type,Price from roomsPrice";
Double total = 0;
Dictionary <String, int> roomPriceList = new Dictionary <string, int>();
//String q = "select Price from roomsPrice where Type = '" + cmbDeluxeRoom.Text + "' or Type = '" + cmbSuiteRoom .Text+ "' or type = '" + cmbStandardRoom.Text + "'";
using (DBConect db = new DBConect())
{
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
while (r.Read())
{
roomPriceList.Add(r[0].ToString(), Int32.Parse(r[1].ToString()));
}
}
}
total = Int32.Parse(cmbDeluxeRoom.Text) * roomPriceList["Deluxe Room"] + Int32.Parse(cmbSuiteRoom.Text) * roomPriceList["Suite Room"] + Int32.Parse(cmbStandardRoom.Text) * roomPriceList["Standard Room"];
lblPrice1.Text = total.ToString();
}
private void btnLogin_Click(object sender, EventArgs e)
{
String userName = txtUn.Text;
String password = txtPw.Text;
String pass = null;
String q = "select password from Users where userName = '******'";
using (DBConect db = new DBConect())
{
MySqlCommand cmd = new MySqlCommand(q, db.con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
while (r.Read())
{
pass = r["password"].ToString();
}
if (pass.Equals(password))
{
frmMain m = new frmMain();
this.Hide();
m.Show();
}
else
{
MessageBox.Show("Invalid Password");
}
}
else
{
MessageBox.Show("Invalid user name");
}
}
}