public float getStayPrice(Int32 id_booking) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_GET_STAY_PRICE")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Price").As(SqlDbType.Float).AsOutput()
.Execute();
return (float)results["@Price"];
}
public Int32 getExtraNights(Int32 id_booking) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_GET_EXTRA_NIGHTS")
.WithParam("IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Extra").As(SqlDbType.Int).AsOutput()
.Execute();
return (Int32)results["@Extra"];
}
public Boolean bookingExists(Int32 id_booking) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_BOOKING_EXISTS")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Exists").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@Exists"];
}
public Boolean bookingHasAllInclusive(Int32 id_booking) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_BOOKING_ALL_INCLUSIVE")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Return").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@Return"];
}
public void AddsNewProperties()
{
SqlStoredProcedure someProc = new SqlStoredProcedure("aName").WithParam("@SomeParam").As(SqlDbType.Bit).AsOutput();
Assert.AreEqual(someProc.Parameters.Count(), 1);
Assert.AreEqual(someProc.Parameters.Peek().Direction, System.Data.ParameterDirection.Output);
Assert.AreEqual(someProc.Parameters.Peek().ParameterName, "@SomeParam");
}
public Boolean setCheckOutGuests(Int32 id_booking)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_CHECKED_OUT_GUESTS")
.WithParam("IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Seted").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@Seted"];
}
public Boolean insertHolderToBooking(Int32 id_booking, Int32 id_guest) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES]. SP_INSERT_HOLDER_BOOKING")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@IdGuest").As(SqlDbType.Int).Value(id_guest)
.WithParam("@Inserted").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@Inserted"];
}
public Boolean deleteBooking(Int32 id_booking, String descr) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_BOOKING_EXISTS")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@Descr").As(SqlDbType.VarChar).Value(id_booking)
.WithParam("@Deleted").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@Deleted"];
}
public Int32 addExtraToStay(Int32 id_booking, Int32 id_extra) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_INSERT_EXTRA_STAY")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@IdExtra").As(SqlDbType.Int).Value(id_extra)
.WithParam("@IdExInserted").As(SqlDbType.Int).AsOutput()
.Execute();
return (Int32)results["@IdExInserted"];
}
public Boolean saveBill(Int32 id_booking, String payment) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_INSERT_BILL")
.WithParam("@IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@PaymentMode").As(SqlDbType.VarChar).Value(payment)
.WithParam("@Inserted").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@Inserted"];
}
public void userLoginIsNotCorrect()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].validateUserPass")
.WithParam("@User").As(SqlDbType.VarChar).Value(user.ToString())
.WithParam("@Pass").As(SqlDbType.VarChar).Value("WRONG")
.WithParam("@RESULT").As(SqlDbType.Bit).AsOutput()
.Execute();
Assert.False((Boolean)results["@RESULT"]);
}
public DataTable extraBilled(DateTime from, DateTime to){
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_EXTRA_BILLED")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return (DataTable) results["@ReturnedValues"];
}
public DataTable outService(DateTime from, DateTime to){
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_OUT_SERVICE")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return (DataTable) results["@ReturnedValues"];
}
public DataTable occupiedRooms(DateTime from, DateTime to){
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_OCCUPIED_ROOMS")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return (DataTable) results["@ReturnedValues"];
}
public DataTable guestsPoints(DateTime from, DateTime to){
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_GUESTS_POINTS")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return (DataTable) results["@ReturnedValues"];
}
public Int32 getBookingPrice(Int32 id_hotel, Int32 id_regimen, DateTime checkin, DateTime checkout, Int32 extraGuests, Int32 id_roomtype) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_BOOKING_PRICE")
.WithParam("@IdHotel").As(SqlDbType.Int).Value(id_hotel)
.WithParam("@IdRegimen").As(SqlDbType.Int).Value(id_regimen)
.WithParam("@CheckIn").As(SqlDbType.DateTime).Value(checkin)
.WithParam("@CheckOut").As(SqlDbType.DateTime).Value(checkout)
.WithParam("@ExtraGuests").As(SqlDbType.Int).Value(extraGuests)
.WithParam("@IdRoomType").As(SqlDbType.Int).Value(id_roomtype)
.WithParam("@Price").As(SqlDbType.Int).AsOutput()
.Execute();
return (Int32)results["@Price"];
}
public Int32 insertStay(Int32 id_booking, Int32 id_hotel, Int32 id_reg, Int32 id_roomtype, DateTime start, Int32 nights) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_INSERT_STAY")
.WithParam("IdBooking").As(SqlDbType.Int).Value(id_booking)
.WithParam("@IdHotel").As(SqlDbType.Int).Value(id_hotel)
.WithParam("@IdRegimen").As(SqlDbType.Int).Value(id_reg)
.WithParam("@CheckIn").As(SqlDbType.DateTime).Value(start)
.WithParam("@Nights").As(SqlDbType.Int).Value(nights)
.WithParam("@IdRoomType").As(SqlDbType.Int).Value(id_roomtype)
.WithParam("@RoomNumber").As(SqlDbType.Int).AsOutput()
.Execute();
return (Int32)results["@RoomNumber"];
}
public DataTable cancelledBookings(DateTime from, DateTime to){
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_STATISTICS_CANCEL_BOOKINGS")
.WithParam("@From").As(SqlDbType.DateTime).Value(from)
.WithParam("@To").As(SqlDbType.DateTime).Value(to)
.WithParam("@ReturnedValues").AsDataTable().AsOutput()
.Execute();
return (DataTable) results["@ReturnedValues"];
}
public bool PersonExistance(String docType, Decimal docNumber)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_PERSON_EXISTS")
.WithParam("@DocType").As(SqlDbType.VarChar).Value(docType)
.WithParam("@DocNumber").As(SqlDbType.Decimal).Value(docNumber)
.WithParam("@GuestExist").As(SqlDbType.Bit).AsOutput()
.Execute();
return (bool)results["@GuestExist"];
}
public Boolean checkAvailability(Int32 id_hotel, Int32 id_regimen, DateTime checkin, DateTime checkout, Int32 extraGuests, Int32 id_roomtype) {
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_CHECK_AVAILABILITY")
.WithParam("@IdHotel").As(SqlDbType.Int).Value(id_hotel)
.WithParam("@IdRegimen").As(SqlDbType.Int).Value(id_regimen)
.WithParam("@CheckIn").As(SqlDbType.DateTime).Value(checkin)
.WithParam("@CheckOut").As(SqlDbType.DateTime).Value(checkout)
.WithParam("@ExtraGuests").As(SqlDbType.Int).Value(extraGuests)
.WithParam("@IdRoomType").As(SqlDbType.Int).Value(id_roomtype)
.WithParam("@Available").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@Available"];
}
public Boolean emailExistsForUpdate(Int32 id_guest, String mail){
if (!String.IsNullOrEmpty(mail))
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_EMAIL_EXISTS_UPDATE")
.WithParam("@Id").As(SqlDbType.Int).Value(id_guest)
.WithParam("@Email").As(SqlDbType.VarChar).Value(mail)
.WithParam("@EmailExist").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@EmailExist"];
}
else return false;
}
public void ExecuteReturnsAllOutputParams()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].OUTPUT_TEST")
.WithParam("@anOutput").AsOutput().As(SqlDbType.VarChar).WithMaximumSize(50)
.WithParam("@aValue").As(SqlDbType.Int).Value(10)
.WithParam("@anotherOutput").AsOutput().As(SqlDbType.Int)
.Execute();
Assert.AreEqual(results.Count, 2);
Assert.AreEqual(results["@anOutput"], "a Result");
Assert.AreEqual(results["@anotherOutput"], 150);
}
public void ExecuteWithComeback()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].RESULTS_TEST")
.Execute();
DataSet retVals = (DataSet) results["ReturnedValues"];
Assert.True(retVals.Tables[0].Rows.Count > 0);
}
public Int32 insertPerson(String name, String lastname, String docType, Decimal docNumber, String mail, Decimal phone, DateTime birthDate,
String street, Int32 streetNum, Int32 floor, String dept, String nationality)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_INSERT_PERSON")
.WithParam("@Name").As(SqlDbType.VarChar).Value(name)
.WithParam("@Lastname").As(SqlDbType.VarChar).Value(lastname)
.WithParam("@DocType").As(SqlDbType.VarChar).Value(docType)
.WithParam("@DocNumber").As(SqlDbType.Decimal).Value(docNumber)
.WithParam("@Mail").As(SqlDbType.VarChar).Value(mail)
.WithParam("@Phone").As(SqlDbType.Decimal).Value(phone)
.WithParam("@BirthDate").As(SqlDbType.DateTime).Value(birthDate)
.WithParam("@Street").As(SqlDbType.VarChar).Value(street)
.WithParam("@StreetNum").As(SqlDbType.Int).Value(streetNum)
.WithParam("@Floor").As(SqlDbType.Int).Value(floor)
.WithParam("@Dept").As(SqlDbType.VarChar).Value(dept)
.WithParam("@Nationality").As(SqlDbType.VarChar).Value(nationality)
.WithParam("@IdInserted").As(SqlDbType.Int).AsOutput()
.Execute();
return (Int32)results["@IdInserted"];
}
public Boolean setForManteinance(DateTime start, DateTime end, String descr)
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].SP_HOTEL_MANTEINANCE")
.WithParam("@IdHotel").As(SqlDbType.Int).Value((int) Hotel["id_hotel"])
.WithParam("@Start").As(SqlDbType.DateTime).Value(start)
.WithParam("@End").As(SqlDbType.DateTime).Value(end)
.WithParam("@Descr").As(SqlDbType.VarChar).Value(descr)
.WithParam("@Manteined").As(SqlDbType.Bit).AsOutput()
.Execute();
return (Boolean)results["@Manteined"];
}
public void RetunsValidDataReader()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].RESULTS_TEST")
.AsDataReader()
.Execute();
SqlDataReader retVals = (SqlDataReader)results["ReturnedValues"];
Assert.True(retVals.HasRows);
ConnectionManager.CloseConnection();
}
public void ReturnsValidDataTable()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].RESULTS_TEST")
.AsDataTable()
.Execute();
DataTable retVals = (DataTable)results["ReturnedValues"];
Assert.True(retVals.Rows.Count > 0);
}
public void ReturnValuesWorks()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].RETURN_TEST")
.WithParam("RetVal").AsReturnValue()
.Execute();
Assert.AreEqual(results["RetVal"], -4);
}
public void CreatesNewSoredProcedure()
{
SqlStoredProcedure someProc = new SqlStoredProcedure("aName");
Assert.AreEqual(someProc.StoredCommand.CommandText, "aName");
Assert.AreEqual(someProc.StoredCommand.CommandType, CommandType.StoredProcedure);
}
public void HasNoComeback()
{
SqlResults results = new SqlStoredProcedure("[BOBBY_TABLES].OUTPUT_TEST")
.WithParam("@anOutput").AsOutput().As(SqlDbType.VarChar).WithMaximumSize(50)
.WithParam("@aValue").As(SqlDbType.Int).Value(10)
.WithParam("@anotherOutput").AsOutput().As(SqlDbType.Int)
.Execute();
results["ReturnedValues"].GetType();
}
