public List <VenueSpace> DisplayVenueSpace(Venue userVenue)
{
List <VenueSpace> spaceList = new List <VenueSpace>();
string sql_GetSpaceInfo = "SELECT s.id, s.name, s.open_from, s.open_to, s.daily_rate, s.max_occupancy FROM venue v" +
" JOIN space s ON s.venue_id = v.id" +
" WHERE v.name = @userInput";
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand spaceInfo = new SqlCommand(sql_GetSpaceInfo, conn);
spaceInfo.Parameters.AddWithValue("@userInput", userVenue.Name);
SqlDataReader spaceReader = spaceInfo.ExecuteReader();
while (spaceReader.Read() == true)
{
string open = Convert.ToString(spaceReader["open_from"]);
string close = Convert.ToString(spaceReader["open_to"]);
if (string.IsNullOrEmpty(open))
{
open = "0";
}
if (string.IsNullOrEmpty(close))
{
close = "";
}
string name = Convert.ToString(spaceReader["name"]);
decimal dailyRate = Convert.ToDecimal(spaceReader["daily_rate"]);
int maxOccupancy = Convert.ToInt32(spaceReader["max_occupancy"]);
int spaceId = Convert.ToInt32(spaceReader["id"]);
VenueSpace result = new VenueSpace(spaceId, name, open, close, dailyRate, maxOccupancy);
spaceList.Add(result);
}
}
}
catch (Exception ex)
{
spaceList = new List <VenueSpace>();
}
return(spaceList);
}
public void TestingToSeeIfMonthIsCorrect(string input, string expected)
{
//Arrange
VenueSpace test = new VenueSpace();
//Act
string result = test.ConvertToMonth(input);
//Assert
Assert.AreEqual(expected, result);
}
//load venues into combobox
private void Form2_Load(object sender, EventArgs e)
{
string venueQuery = "SELECT Venue.VenueID, Venue.VenueName FROM [Venue];";
string venueSpaceQuery = "SELECT VenueSpace.VenueSpaceID, VenueSpace.VenueSpaceName, VenueSpace.VenueID FROM [VenueSpace];";
string idfQuery = "SELECT IDF.IDFID, IDF.IDFName, IDF.VenueSpaceID FROM [IDF];";
string ppQuery = "SELECT PatchPanel.PatchPanelID, PatchPanel.PatchPanelName, PatchPanel.IDFID FROM [PatchPanel];";
string switchQuery = "SELECT Switch.SwitchID, Switch.DNSName, Switch.IDFID FROM [Switch];";
string ppPortQuery = "SELECT PatchPanelPort.PatchPanelPortID, PatchPanelPort.PatchPanelPortNum, PatchPanelPort.PatchPanelID FROM [PatchPanelPort] WHERE PatchPanelPort.SwitchPortID IS NULL;";
string switchPortQuery = "SELECT SwitchPort.SwitchPortID, SwitchPort.SwitchPortNum, SwitchPort.SwitchID FROM [SwitchPort] WHERE SwitchPort.PatchPanelPortID IS NULL;";
DataSet venDS = getDataSet(venueQuery);
DataSet vsDS = getDataSet(venueSpaceQuery);
DataSet idfDS = getDataSet(idfQuery);
DataSet swDS = getDataSet(switchQuery);
DataSet ppDS = getDataSet(ppQuery);
DataSet swpDS = getDataSet(switchPortQuery);
DataSet pppDS = getDataSet(ppPortQuery);
try
{
foreach (DataRow dr in venDS.Tables[0].Rows)
{
Venue venue = new Venue();
venue.venueID = (int)dr.ItemArray.GetValue(0);
venue.venueName = dr.ItemArray.GetValue(1).ToString();
venueList.Add(venue);
}
foreach (DataRow dr in vsDS.Tables[0].Rows)
{
VenueSpace venueSpace = new VenueSpace();
venueSpace.venueSpaceID = (int)dr.ItemArray.GetValue(0);
venueSpace.venueSpaceName = dr.ItemArray.GetValue(1).ToString();
venueSpace.venueID = (int)dr.ItemArray.GetValue(2);
venueSpaceList.Add(venueSpace);
}
foreach (DataRow dr in idfDS.Tables[0].Rows)
{
IDF idf = new IDF();
idf.idfID = (int)dr.ItemArray.GetValue(0);
idf.idfName = dr.ItemArray.GetValue(1).ToString();
idf.venueSpaceID = (int)dr.ItemArray.GetValue(2);
idfList.Add(idf);
}
foreach (DataRow dr in swDS.Tables[0].Rows)
{
Switch sw = new Switch();
sw.switchID = (int)dr.ItemArray.GetValue(0);
sw.switchNameDNS = dr.ItemArray.GetValue(1).ToString();
sw.idfID = (int)dr.ItemArray.GetValue(2);
switchList.Add(sw);
}
foreach (DataRow dr in ppDS.Tables[0].Rows)
{
PatchPanel pp = new PatchPanel();
pp.patchPanelID = (int)dr.ItemArray.GetValue(0);
pp.patchPanelName = dr.ItemArray.GetValue(1).ToString();
pp.idfID = (int)dr.ItemArray.GetValue(2);
PPList.Add(pp);
}
foreach (DataRow dr in swpDS.Tables[0].Rows)
{
SwitchPort swp = new SwitchPort();
swp.switchPortID = (int)dr.ItemArray.GetValue(0);
swp.switchPortNum = (int)dr.ItemArray.GetValue(1);
swp.switchID = (int)dr.ItemArray.GetValue(2);
switchPortList.Add(swp);
}
foreach (DataRow dr in pppDS.Tables[0].Rows)
{
PatchPanelPort ppp = new PatchPanelPort();
ppp.patchPanelPortID = (int)dr.ItemArray.GetValue(0);
ppp.patchPanelPortNum = (int)dr.ItemArray.GetValue(1);
ppp.patchPanelID = (int)dr.ItemArray.GetValue(2);
PPPList.Add(ppp);
}
}
catch (OleDbException exp)
{
MessageBox.Show("Database Error:" + exp.Message.ToString());
}
updateVenueBoxList(venueList);
updateDataGridView();
}
public HashSet <VenueSpace> DisplayTop5(List <Reservation> takenSpaces, Venue userVenue, int peopleAmt)
{
HashSet <VenueSpace> availableSpaces = new HashSet <VenueSpace>();
string sql_FindAvailableSpaces = "SELECT TOP 5 * FROM space" +
" WHERE name != @venueName" +
" AND venue_id = @venueId" +
" AND max_occupancy >= @peopleAmt" +
" ORDER BY daily_rate desc";
string sql_FindAllAvailableSpaces = "SELECT TOP 5 space.id, venue_id, space.name, is_accessible, open_from, open_to, daily_rate, max_occupancy FROM space" +
" JOIN venue ON space.venue_id = venue.id" +
" WHERE venue.name = @venueName" +
" AND max_occupancy >= @peopleAmt" +
" ORDER BY daily_rate desc";
if (takenSpaces.Count > 0)
{
foreach (Reservation item in takenSpaces)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand findSpaces = new SqlCommand(sql_FindAvailableSpaces, conn);
findSpaces.Parameters.AddWithValue("@venueName", item.Name);
findSpaces.Parameters.AddWithValue("@venueId", item.VenueId);
findSpaces.Parameters.AddWithValue("@peopleAmt", peopleAmt);
SqlDataReader findSpaceReader = findSpaces.ExecuteReader();
while (findSpaceReader.Read() == true)
{
string open = Convert.ToString(findSpaceReader["open_from"]);
string close = Convert.ToString(findSpaceReader["open_to"]);
if (string.IsNullOrEmpty(open))
{
open = "0";
}
if (string.IsNullOrEmpty(close))
{
close = "";
}
string name = Convert.ToString(findSpaceReader["name"]);
decimal dailyRate = Convert.ToDecimal(findSpaceReader["daily_rate"]);
int maxOccupancy = Convert.ToInt32(findSpaceReader["max_occupancy"]);
int spaceId = Convert.ToInt32(findSpaceReader["id"]);
VenueSpace result = new VenueSpace(spaceId, name, open, close, dailyRate, maxOccupancy);
bool spaceIsTaken = false;
foreach (Reservation reservation in takenSpaces)
{
if (reservation.Name == result.Name)
{
spaceIsTaken = true;
}
}
if (spaceIsTaken == false)
{
availableSpaces.Add(result);
}
}
}
}
}
else
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand findSpaces = new SqlCommand(sql_FindAllAvailableSpaces, conn);
findSpaces.Parameters.AddWithValue("@venueName", userVenue.Name);
findSpaces.Parameters.AddWithValue("@peopleAmt", peopleAmt);
SqlDataReader findSpaceReader = findSpaces.ExecuteReader();
while (findSpaceReader.Read() == true)
{
string open = Convert.ToString(findSpaceReader["open_from"]);
string close = Convert.ToString(findSpaceReader["open_to"]);
if (string.IsNullOrEmpty(open))
{
open = "0";
}
if (string.IsNullOrEmpty(close))
{
close = "";
}
string name = Convert.ToString(findSpaceReader["name"]);
decimal dailyRate = Convert.ToDecimal(findSpaceReader["daily_rate"]);
int maxOccupancy = Convert.ToInt32(findSpaceReader["max_occupancy"]);
int spaceId = Convert.ToInt32(findSpaceReader["id"]);
VenueSpace result = new VenueSpace(spaceId, name, open, close, dailyRate, maxOccupancy);
availableSpaces.Add(result);
}
}
}
return(availableSpaces);
}
