public string SetUserExaminationBooking(int bookingID, DateTime bookingDateTime, string logonName, DateTime examinationApproved, int parkingID, int durationHours)
{
bool runStatus = false;
HttpContext.Current.User = (System.Security.Principal.GenericPrincipal)HttpContext.Current.Cache.Get("customPrincipal");
if ((HttpContext.Current.User != null) && (HttpContext.Current.User.IsInRole("Admin") || HttpContext.Current.User.IsInRole("Viewer") || HttpContext.Current.User.IsInRole("Donor"))) {
ExaminationBooking exBooking = new ExaminationBooking(bookingID, bookingDateTime, logonName, examinationApproved, durationHours);
if (parkingID != -1) exBooking.ParkingID = parkingID;
runStatus = Booker.SetExaminationBooking(exBooking);
}
return JsonConvert.SerializeObject(new { runStatus = runStatus });
}
public List<ExaminationBooking> GetAllExaminationBookings()
{
List<ExaminationBooking> bookings = new List<ExaminationBooking>();
SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + System.Web.HttpContext.Current.Server.MapPath(privilegesDatabase));
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT e.bookingID, e.examinationApproved, e.bookingDate, e.logonName, e.durationHours, e.parkingID, u.firstName, u.lastName FROM ExaminationBooking AS e JOIN Users AS u ON (e.logonName=u.logonName)", conn);
var reader = cmd.ExecuteReader();
// write each record
while (reader.Read()) {
DateTime dtResult;
int durationHours, bookingID;
Int32.TryParse(reader["bookingID"] != null ? reader["bookingID"].ToString() : String.Empty, out bookingID);
DateTime examinationApproved = reader.GetDateTime(reader.GetOrdinal("examinationApproved"));
Int32.TryParse(reader["durationHours"] != null ? reader["durationHours"].ToString() : String.Empty, out durationHours);
int? parkingID = reader.IsDBNull(reader.GetOrdinal("parkingID")) ? (int?)null : Int32.Parse(reader["parkingID"].ToString());
string readLogonName = reader["logonName"].ToString();
DateTime.TryParse(reader["bookingDate"] != null ? reader["bookingDate"].ToString() : String.Empty, out dtResult);
string firstName = reader["firstName"] != null ? reader["firstName"].ToString() : String.Empty;
string lastName = reader["lastName"] != null ? reader["lastName"].ToString() : String.Empty;
ExaminationBooking booking = new ExaminationBooking(bookingID, dtResult, readLogonName, examinationApproved, durationHours, parkingID);
if (!String.IsNullOrEmpty(firstName) && !String.IsNullOrEmpty(lastName)) {
booking.DisplayName = firstName + " " + lastName;
}
if (dtResult != null) bookings.Add(booking);
}
cmd.Dispose();
reader.Close();
conn.Dispose();
return bookings;
}
public bool SetExaminationBooking(ExaminationBooking booking)
{
int rowsUpdated = 0;
SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + System.Web.HttpContext.Current.Server.MapPath(privilegesDatabase));
conn.Open();
SqlCommand cmd;
if (booking.ParkingID.HasValue) {
cmd = new SqlCommand("UPDATE ExaminationBooking SET bookingDate=@bookingDate, examinationApproved=@examinationApproved, durationHours=@durationHours, parkingID=@parkingID WHERE bookingID=@bookingID", conn);
cmd.Parameters.Add("@parkingID", SqlDbType.Int);
cmd.Parameters["@parkingID"].Value = booking.ParkingID;
} else {
cmd = new SqlCommand("UPDATE ExaminationBooking SET bookingDate=@bookingDate, examinationApproved=@examinationApproved, durationHours=@durationHours WHERE bookingID=@bookingID", conn);
}
cmd.Parameters.Add("@bookingDate", SqlDbType.DateTime);
cmd.Parameters["@bookingDate"].Value = booking.BookingDate == DateTime.MinValue ? SqlDateTime.MinValue : booking.BookingDate;
cmd.Parameters.Add("@examinationApproved", SqlDbType.DateTime);
cmd.Parameters["@examinationApproved"].Value = booking.ExaminationApproved == DateTime.MinValue ? SqlDateTime.MinValue : booking.ExaminationApproved;
cmd.Parameters.Add("@bookingID", SqlDbType.Int);
cmd.Parameters["@bookingID"].Value = booking.BookingID;
cmd.Parameters.Add("@durationHours", SqlDbType.Int);
cmd.Parameters["@durationHours"].Value = booking.DurationHours;
rowsUpdated = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Dispose();
return rowsUpdated == 0 ? false : true;
}
public bool BookHealthExamination(DateTime bookingDate, int durationHours, string logonName)
{
int rowsUpdated = 0;
ExaminationBooking booking = new ExaminationBooking(bookingDate, durationHours, logonName);
SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + System.Web.HttpContext.Current.Server.MapPath(privilegesDatabase));
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO ExaminationBooking (bookingDate, examinationApproved, durationHours, logonName) values (@bookingDate, @examinationApproved, @durationHours, @logonName)", conn);
cmd.Parameters.Add("@bookingDate", SqlDbType.DateTime);
cmd.Parameters["@bookingDate"].Value = booking.BookingDate == DateTime.MinValue ? SqlDateTime.MinValue : booking.BookingDate;
cmd.Parameters.Add("@examinationApproved", SqlDbType.DateTime);
cmd.Parameters["@examinationApproved"].Value = booking.ExaminationApproved == DateTime.MinValue ? SqlDateTime.MinValue : booking.ExaminationApproved;
cmd.Parameters.Add("@durationHours", SqlDbType.Int);
cmd.Parameters["@durationHours"].Value = booking.DurationHours;
cmd.Parameters.Add("@logonName", SqlDbType.VarChar, 35);
cmd.Parameters["@logonName"].Value = booking.LogonName;
rowsUpdated = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Dispose();
return rowsUpdated == 0 ? false : true;
}