public BookingDetail AddBookingDetails()
{
string uniqueCode = Guid.NewGuid().ToString().Replace("-", string.Empty).Substring(0, 10);
if (AddCreditCard(creditCardDetail) == 1)
{
if (AddGuest(guestDetail, creditCardDetail.cardNumber) == 1)
{
double amtPaid = roomDetail.roomCount * roomDetail.roomPrice;
BookingDetail bdObj = new BookingDetail(uniqueCode, roomDetail.checkInDate, roomDetail.checkOutDate, roomDetail.roomType, roomDetail.roomCount, amtPaid, guestDetail.fullName, guestDetail.emailAddr);
if (AddBooking(bdObj, guestDetail.idNumber) == 1)
{
AddOccupancyDates(bdObj);
return(bdObj);
}
}
}
return(new BookingDetail());
}
private int AddBooking(BookingDetail bdObj, string guestIdNum)
{
if (bdObj == null || string.IsNullOrEmpty(guestIdNum))
{
return(0);
}
string queryString = @"INSERT INTO master.[dbproject].[Booking] ([checkInDate]
,[checkOutDate]
,[roomType]
,[numOfRoom]
,[confirmationCode]
,[bookingOf])
VALUES(@checkInDate, @checkOutDate, @roomType, @numOfRoom, @confirmationCode,
(SELECT id FROM master.[dbproject].[Guest] WHERE validIdNumber=@guestIdNum))" ;
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@checkInDate", bdObj.checkInDate);
param[1] = new SqlParameter("@checkOutDate", bdObj.checkOutDate);
param[2] = new SqlParameter("@roomType", bdObj.roomType);
param[3] = new SqlParameter("@numOfRoom", bdObj.roomCount);
param[4] = new SqlParameter("@confirmationCode", bdObj.confirmationCode);
param[5] = new SqlParameter("@guestIdNum", guestIdNum);
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(queryString, connection))
{
command.Connection.Open();
if (param != null)
{
command.Parameters.AddRange(param);
}
return(command.ExecuteNonQuery());
}
}
}
private int AddOccupancyDates(BookingDetail bdObj)
{
if (bdObj == null)
{
return(0);
}
string queryString = @"INSERT INTO master.[dbproject].[OccupancyDates] ([inDate],[outDate],[roomId])
(SELECT DISTINCT TOP (@roomCount) @checkInDate, @checkOutDate, r.id
FROM master.[dbproject].Room r LEFT JOIN master.[dbproject].OccupancyDates od ON (r.id = od.roomId)
WHERE((od.inDate > @checkOutDate
OR od.outDate < @checkInDate)
OR od.roomID IS NULL)
AND r.isOccupied = 0
AND r.roomType = @roomType)" ;
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@checkInDate", bdObj.checkInDate);
param[1] = new SqlParameter("@checkOutDate", bdObj.checkOutDate);
param[2] = new SqlParameter("@roomType", bdObj.roomType);
param[3] = new SqlParameter("@roomCount", bdObj.roomCount);
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(queryString, connection))
{
command.Connection.Open();
if (param != null)
{
command.Parameters.AddRange(param);
}
return(command.ExecuteNonQuery());
}
}
}