void FieldsValues(string BookingID)
{ // method is used to fill up the labels with the booking details
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
string sql = "Select ID,CustomerID,Date,Type,Total,Status from Booking where ID=@ID";
SqlCommand cmn = new SqlCommand(sql, myconn);
cmn.Parameters.AddWithValue("@ID", BookingID);
SqlDataReader dr;
try
{
myconn.Open();
dr = cmn.ExecuteReader();
dr.Read();
lblBooking.Text = dr["ID"].ToString();
lblCustomerID.Text = dr["CustomerID"].ToString();
lblType.Text = dr["Type"].ToString();
lblDate.Text = dr["Date"].ToString();
lblTotal.Text = dr["Total"].ToString();
lblStatus.Text = dr["Status"].ToString();
}
catch (SqlException ex)
{//display on the console the exeception message
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
finally
{// Close the connection
myconn.Close();
}
}
void HotelList()
{ // appear the whole hotels list
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
string sql = "Select HotelName from Hotel";
SqlCommand cmn = new SqlCommand(sql, myconn);
SqlDataReader dr;
try
{
myconn.Open();
dr = cmn.ExecuteReader();
while (dr.Read())
{
cmbHotel.Items.Add(dr["HotelName"].ToString());
}
}
catch (SqlException ex)
{//display on the console the exeception message
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
finally
{// Close the connection
myconn.Close();
}
}
void HotelLocation(string location)
{ // appear list of hotels on combo box based on the location
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
string sql = "Select HotelName from Hotel where Location=@location";
SqlCommand cmn = new SqlCommand(sql, myconn);
cmn.Parameters.AddWithValue("@location", location);
SqlDataReader dr;
try
{
myconn.Open();
dr = cmn.ExecuteReader();
while (dr.Read())
{
cmbHotel.Items.Add(dr["HotelName"].ToString());
}
}
catch (SqlException ex)
{//display on the console the exeception message
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
finally
{// Close the connection
myconn.Close();
}
}
void checkDepositStatus(string BookingNo)
{// Method is used to check the deposit status
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
string sql = "Select * from Booking where ID=@BookingNO";
SqlCommand cmd = new SqlCommand(sql, myconn);
SqlDataReader dr;
cmd.Parameters.AddWithValue("@BookingNO", BookingNo);
myconn.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
if (dr["Status"].ToString() == "Confirmed")
{
MessageBox.Show("The deposit had been paid before for the entered Booking No.", "Payment Status", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (dr["Status"].ToString() == "Not Confirmed" && dr["Type"].ToString() == "Hotel")
{
MessageBox.Show("The entered booking No is a hotel reservation, no need to pay a deposit", "Invalid Payment", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
SelectDeposit(txtBookingNo.Text);
gropboxPayment.Visible = true;
}
}
myconn.Close();
}
public string SelectCust(string CustomerID)
{ //checking if the customer has registered and recorded in the customers list
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
string Customer = "";
string sql = "Select ID,Name,ContactNumber from Customer where ID=@ID";
SqlCommand cmn = new SqlCommand(sql, myconn);
cmn.Parameters.AddWithValue("@ID", CustomerID);
SqlDataReader dr;
try
{
myconn.Open();
dr = cmn.ExecuteReader();
dr.Read();
Customer = $" Customer ID : \t \t \t {dr[0].ToString()} \n Customer Name : \t\t {dr[1].ToString()} \n Contact Number : \t\t { dr[2].ToString()} \n";
}
catch (SqlException ex)
{//display on the console the exeception message
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
finally
{
myconn.Close();
}
return(Customer);
}
void SelectDeposit(string BookingNo)
{
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
SqlCommand cmn;
SqlDataReader dr;
string sql = "select * from booking where id=@ID";
cmn = new SqlCommand(sql, myconn);
cmn.Parameters.AddWithValue("@ID", BookingNo);
try
{
myconn.Open();
dr = cmn.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
lblTotalAmount.Visible = false;
labelTotalAmount.Visible = false;
labelStatus.Visible = false;
lblStatus.Visible = false;
labelStatus.Visible = false;
lblStatus.Visible = false;
labelBookingAmount.Visible = false;
lblBookingAmount.Visible = false;
labelDepositPaid.Visible = true;
lblDepositPaid.Visible = true;
lblBookingNo.Text = dr["ID"].ToString();
lblType.Text = dr["Type"].ToString();
date = DateTime.Parse(dr["Date"].ToString());
lblDate.Text = date.ToShortDateString();
lblCustomerID.Text = dr["CustomerID"].ToString();
labelDeposit.Text = "Deposit";
deposit = double.Parse(dr["Deposit"].ToString());
lblDeposit.Text = deposit.ToString("C3");
labelCharge.Text = "Total Amount";
bookingAmount = double.Parse(dr["Total"].ToString()) - double.Parse(dr["Discount"].ToString());
lblCharge.Text = bookingAmount.ToString("C3");
labelDepositPaid.Text = "Remaining Amount";
Total = double.Parse(dr["Total"].ToString()) - double.Parse(dr["Deposit"].ToString()) - double.Parse(dr["Discount"].ToString());
lblDepositPaid.Text = Total.ToString("C3");
}
}
catch (SqlException ex)
{//display on the console the exeception message
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
finally
{// Close the connection
myconn.Close();
}
}
string FieldsValues(string BookingID)
{ //Fill up the combo boxes based on old booking details
string status = "Not confirmed";
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
string sql = "Select CustomerID,Date,Type,Package,Hotel,Room,HotelNight,Status from Booking where ID=@ID";
SqlCommand cmn = new SqlCommand(sql, myconn);
cmn.Parameters.AddWithValue("@ID", BookingID);
SqlDataReader dr;
try
{
myconn.Open();
dr = cmn.ExecuteReader();
dr.Read();
if (dr["Status"].ToString() == "Cancelled")
{
status = "Cancelled";
}
else if (dr["Status"].ToString() == "Successful")
{
status = "Successful";
}
else
{
txtICPassport.Text = dr["CustomerID"].ToString();
cmbBooking.SelectedItem = dr["Type"].ToString();
cmbLocation.SelectedItem = dr["Package"].ToString();
dateTimePicker1.Value = DateTime.Parse(dr["Date"].ToString());
cmbHotel.SelectedItem = dr["Hotel"].ToString();
cmbRoom.SelectedItem = dr["Room"].ToString();
cmbNight.SelectedItem = dr["HotelNight"].ToString();
}
}
catch (SqlException ex)
{//display on the console the exeception message
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
finally
{// Close the connection
myconn.Close();
}
return(status);
}
void checkBookingStatus(string BookingNo)
{ //Method is used to check if the booking no is cancelled or already had been paid the whole amount
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
string sql = "Select * from Booking where ID=@BookingNO";
SqlCommand cmd = new SqlCommand(sql, myconn);
SqlDataReader dr;
cmd.Parameters.AddWithValue("@BookingNO", BookingNo);
myconn.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
if (dr["Status"].ToString() == "Cancelled")
{
MessageBox.Show("The entered booking No had been cancelled before", "Booking Status", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtBookingNo.Focus();
}
else if (dr["Status"].ToString() == "Successful")
{
MessageBox.Show("The entered booking No had been paid the whole amount already", "Invalid Payment", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtBookingNo.Focus();
}
else
{
gropboxPaymentOption.Enabled = true;
txtBookingNo.Enabled = false;
cmbPaymentOption.Focus();
}
}
else
{
MessageBox.Show("The entered booking No does not exist.", "Invalid Booking NO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
myconn.Close();
}
void SelectBooking(string Type)
{ // select the successful booking from the database
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
SqlCommand cmn;
SqlDataReader dr;
string sql = "select b.ID,b.CustomerID,b.Type,b.Date,b.Total,b.Discount,p.Cost,(hr.Price*b.HotelNight),b.Deposit from Booking b, Hotel h, HotelRoom hr,Package p where b.Package=p.Package and b.Hotel=h.HotelName and b.Room=hr.Room and h.HotelName=hr.Hotel and b.id=(select Max(ID) from Booking)";
if (Type == "Hotel")
{
sql = "select b.ID,b.CustomerID,b.Type,b.Date,b.Total,b.Discount,(hr.Price*b.HotelNight) from Booking b, Hotel h, HotelRoom hr where b.Hotel=h.HotelName and b.Room=hr.Room and h.HotelName=hr.Hotel and b.id=(select Max(ID) from Booking)";
}
else if (Type == "Tour Package")
{
sql = "select b.ID,b.CustomerID,b.Type,b.Date,b.Total,b.Discount,p.Cost,b.Deposit from Booking b, Package p where b.Package=p.Package and b.id=(select Max(ID) from Booking)";
}
try
{
cmn = new SqlCommand(sql, myconn);
myconn.Open();
dr = cmn.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
TotalAmount = double.Parse(dr[4].ToString()) - double.Parse(dr[5].ToString());
Discount = double.Parse(dr[5].ToString());
lblBooking.Text = dr[0].ToString();
lblCustomerID.Text = dr[1].ToString();
lblType.Text = dr[2].ToString();
DateTime date = DateTime.Parse(dr[3].ToString());
lblDate.Text = date.ToShortDateString();
lblTotal.Text = TotalAmount.ToString("C2");
lblDiscount.Text = Discount.ToString("C2");
if (Type == "Tour Package & Hotel")
{
TourCost = double.Parse(dr[6].ToString());
lblTour.Text = TourCost.ToString("C2");
HotelCost = double.Parse(dr[7].ToString());
lblHotel.Text = HotelCost.ToString("C2");
lblDeposit.Visible = true;
labelDeposit.Visible = true;
Deposit = double.Parse(dr[8].ToString());
lblDeposit.Text = Deposit.ToString("C2");
lblNote.Visible = true;
}
else if (Type == "Hotel")
{
TourCost = 0;
lblTour.Text = TourCost.ToString("C2");
HotelCost = double.Parse(dr[6].ToString());
lblHotel.Text = HotelCost.ToString("C2");
}
else if (Type == "Tour Package")
{
TourCost = double.Parse(dr[6].ToString());
lblTour.Text = TourCost.ToString("C2");
HotelCost = 0;
lblHotel.Text = HotelCost.ToString("C2");
lblDeposit.Visible = true;
labelDeposit.Visible = true;
Deposit = double.Parse(dr[7].ToString());
lblDeposit.Text = Deposit.ToString("C2");
lblNote.Visible = true;
}
}
}
catch (SqlException ex)
{//display on the console the exeception message
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
finally
{// Close the connection
myconn.Close();
}
}
void SelectBooking(string BookingNo)
{
DBConnect conn = new DBConnect();
SqlConnection myconn = conn.GetSqlConnection();
SqlCommand cmn;
SqlDataReader dr;
string sql = "select * from booking where id=@ID";
cmn = new SqlCommand(sql, myconn);
cmn.Parameters.AddWithValue("@ID", BookingNo);
try
{
myconn.Open();
dr = cmn.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
if (dr["Status"].ToString() == "Not Confirmed" && (dr["Type"].ToString() == "Tour Package & Hotel" || dr["Type"].ToString() == "Tour Package"))
{
MessageBox.Show("You have to pay the deposit firstly to confirm the booking", "Unconfirmed Booking", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
labelStatus.Visible = true;
lblStatus.Visible = true;
labelTotalAmount.Visible = true;
lblTotalAmount.Visible = true;
lblBookingAmount.Visible = true;
labelBookingAmount.Visible = true;
labelCharge.Visible = true;
lblCharge.Visible = true;
labelDeposit.Visible = true;
lblDeposit.Visible = true;
labelDepositPaid.Visible = true;
lblDepositPaid.Visible = true;
labelDepositPaid.Text = "Deposit Paid";
labelDeposit.Text = "Discount";
labelCharge.Text = "Charge";
lblBookingNo.Text = dr["ID"].ToString();
lblCustomerID.Text = dr["CustomerID"].ToString();
lblType.Text = dr["Type"].ToString();
date = DateTime.Parse(dr["Date"].ToString());
lblDate.Text = date.ToShortDateString();
charge = double.Parse(dr["Charge"].ToString());
lblCharge.Text = charge.ToString("C3");
discount = double.Parse(dr["Discount"].ToString());
lblDeposit.Text = discount.ToString("C3");
deposit = 0;
lblDepositPaid.Text = deposit.ToString("C3");
bookingAmount = double.Parse(dr["Total"].ToString());
lblBookingAmount.Text = bookingAmount.ToString("C3");
Total = double.Parse(dr["Total"].ToString()) + double.Parse(dr["Charge"].ToString()) - double.Parse(dr["Discount"].ToString());
lblTotalAmount.Text = Total.ToString("C3");
if (dr["DepositStatus"].ToString() == string.Empty)
{
lblStatus.Text = "Null";
}
else
{
lblStatus.Text = dr["DepositStatus"].ToString();
}
if (dr["Status"].ToString() == "Confirmed" && dr["DepositStatus"].ToString() == string.Empty)
{
deposit = double.Parse(dr["Deposit"].ToString());
lblDepositPaid.Text = deposit.ToString("C3");
Total = double.Parse(dr["Total"].ToString()) + double.Parse(dr["Charge"].ToString()) - double.Parse(dr["Discount"].ToString()) - double.Parse(dr["deposit"].ToString());
lblTotalAmount.Text = Total.ToString("C3");
}
else if (dr["DepositStatus"].ToString() == "forfeited")
{
deposit = double.Parse(dr["Deposit"].ToString());
lblDepositPaid.Text = deposit.ToString("C3");
Total = double.Parse(dr["Total"].ToString()) + double.Parse(dr["Charge"].ToString()) - double.Parse(dr["Discount"].ToString());
lblTotalAmount.Text = Total.ToString("C3");
}
gropboxPayment.Visible = true;
}
}
}
catch (SqlException ex)
{//display on the console the exeception message
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
finally
{// Close the connection
myconn.Close();
}
}