public List <CampSite> GetCampSitesInCampGround(Campground campground)
{
List <CampSite> campSites = new List <CampSite>();
using (SqlConnection connection = new SqlConnection(_connectionstring))
{
connection.Open();
const string sqlParkCommand = "SELECT [site_id],[campground_id],[site_number],[max_occupancy],[accessible],[max_rv_length],[utilities] FROM [ParkReservation].[dbo].[site] where campground_id = @campgroundID";
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@campgroundId", campground.CampgroundId);
cmd.CommandText = sqlParkCommand;
cmd.Connection = connection;
//Pull data off the table
SqlDataReader reader = cmd.ExecuteReader();
//Looping through the table and populating the list with all the rows
while (reader.Read())
{
CampSite campsite = new CampSite();
campsite.Id = Convert.ToInt32(reader["site_id"]);
campsite.CampgroundId = Convert.ToInt32(reader["campground_id"]);
campsite.SiteNumber = Convert.ToInt32(reader["site_number"]);
campsite.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
campsite.Accesible = Convert.ToBoolean(reader["accessible"]);
campsite.MaxRvLength = Convert.ToInt32(reader["max_rv_length"]);
campsite.Utilities = Convert.ToBoolean(reader["utilities"]);
campSites.Add(campsite);
}
}
return(campSites);
}
/// <summary>
///
/// </summary>
/// <param name="campgroundId"></param>
/// <param name="arrivalDate"></param>
/// <param name="depatureDate"></param>
/// <returns></returns>
public Dictionary <int, CampSite> GetCampsiteData(string campgroundId, string arrivalDate, string depatureDate)
{
Dictionary <int, CampSite> campsite = new Dictionary <int, CampSite>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(siteReservationData, conn);
command.Parameters.AddWithValue("@campgroundId", campgroundId);
command.Parameters.AddWithValue("@from_date", arrivalDate);
command.Parameters.AddWithValue("@to_date", depatureDate);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CampSite cs = new CampSite();
cs.Id = Convert.ToInt32(reader["site_id"]);
cs.CampGroundId = Convert.ToInt32(reader["campground_id"]);
cs.Accessiblity = Convert.ToBoolean(reader["accessible"]);
cs.MaxRevLength = Convert.ToInt32(reader["max_rv_length"]);
cs.Utilities = Convert.ToString(reader["utilities"]);
cs.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
return(campsite);
}
/// <summary>
/// Allows user to search for a reservation.
/// </summary>
/// <param name="campgroundId"></param>
/// <param name="arrivalDate"></param>
/// <param name="departureDate"></param>
/// <returns></returns>
public IList <CampSite> SearchReservationRun(int campgroundId, DateTime arrivalDate, DateTime departureDate)
{
List <CampSite> sites = new List <CampSite>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL_ListOfSites, conn);
cmd.Parameters.AddWithValue("@campgroundId", campgroundId);
cmd.Parameters.AddWithValue("@arrivalDate", arrivalDate);
cmd.Parameters.AddWithValue("@departureDate", departureDate);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
CampSite site = ConvertReaderToSite(reader);
sites.Add(site);
}
}
}
catch (SqlException ex)
{
Console.WriteLine("Error listing all the parks");
Console.WriteLine(ex.Message);
throw;
}
return(sites);
}
public IActionResult Edit(CampSite obj)
{
if (ModelState.IsValid)
{
_db.Campsite.Update(obj);
_db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(obj));
}
private CampSite ConvertReaderToSite(SqlDataReader reader)
{
CampSite campSite = new CampSite();
campSite.SiteId = Convert.ToInt32(reader["site_id"]);
campSite.CampgroundId = Convert.ToInt32(reader["campground_id"]);
campSite.SiteNumber = Convert.ToInt32(reader["site_number"]);
campSite.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
campSite.IsAccessible = Convert.ToInt32(reader["accessible"]);
campSite.MaxRvLength = Convert.ToInt32(reader["max_rv_length"]);
campSite.HasUtilties = Convert.ToInt32(reader["utilities"]);
return(campSite);
}
public List <CampSite> Search(int campgroundId, string arrivalDate, string departureDate)
{
List <CampSite> output = new List <CampSite>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// int arrivalMonth = arrivalDate.
SqlCommand cmd = new SqlCommand("SELECT *, campground.daily_fee FROM site JOIN campground ON campground.campground_id = site.campground_id WHERE site.site_id NOT IN(SELECT reservation.site_id FROM reservation WHERE(@arrivalDate <= reservation.from_date AND @departureDate >= reservation.to_date) OR(@arrivalDate <= reservation.from_date AND @departureDate <= reservation.to_date AND @departureDate >= reservation.from_date) OR (@arrivalDate >= reservation.from_date AND @departureDate <= reservation.to_date) OR (@arrivalDate >= reservation.from_date AND @arrivalDate <= reservation.from_date AND @departureDate >= reservation.to_date)) AND campground.campground_id = @campgroundId ; ", conn);
cmd.Parameters.AddWithValue("@campgroundId", campgroundId);
cmd.Parameters.AddWithValue("@arrivalDate", arrivalDate);
cmd.Parameters.AddWithValue("@departureDate", departureDate);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
CampSite campSite = new CampSite();
// Campground campground = new Campground();
campSite.SiteId = Convert.ToInt32(reader["site_id"]);
campSite.CampgroundId = Convert.ToInt32(reader["campground_id"]);
campSite.CampsiteNumber = Convert.ToInt32(reader["site_number"]);
campSite.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
campSite.Accessible = Convert.ToBoolean(reader["accessible"]);
campSite.MaxRvLength = Convert.ToString(reader["max_rv_length"]);
campSite.Utilities = Convert.ToBoolean(reader["utilities"]);
campSite.DailyFee = Convert.ToDecimal(reader["daily_fee"]);
output.Add(campSite);
//output.Add(campground);
}
}
}
catch (SqlException ex)
{
Console.WriteLine("An error reading the database: " + ex.Message);
}
return(output);
}
//Methods
public Dictionary <int, CampSite> ListCampSites(DateTime[] reservationRange, int campgroundSelection)
{
Dictionary <int, CampSite> output = new Dictionary <int, CampSite>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL_CampSite, conn);
cmd.Parameters.AddWithValue("@requested_start", reservationRange[0]);
cmd.Parameters.AddWithValue("@requested_end", reservationRange[1]);
cmd.Parameters.AddWithValue("@campground_id", campgroundSelection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int key = Convert.ToInt32(reader["site_id"]);
CampSite site = new CampSite
{
Campground_Id = Convert.ToInt32(reader["campground_id"]),
Site_Number = Convert.ToInt32(reader["site_number"]),
Max_Occupancy = Convert.ToInt32(reader["max_occupancy"]),
Max_RV_Length = Convert.ToInt32(reader["max_rv_length"]),
AccessibleBool = Convert.ToBoolean(reader["accessible"]),
UtilitiesBool = Convert.ToBoolean(reader["utilities"])
};
output[key] = site;
}
}
}
catch (SqlException ex)
{
throw;
}
return(output);
}
//checks for date range
//lists all the sites after they have been
//left as variables to pass through instead of an object because parameters are from different models&
//include class level variables from CampingCLI
public IList <CampSite> SearchForSite(int parkID, int campGroundID, DateTime from_date, DateTime to_date)
{
List <CampSite> openSites = new List <CampSite>();
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SQL_ReservationSearch;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@park_id1", parkID);
cmd.Parameters.AddWithValue("@campground_id1", campGroundID);
cmd.Parameters.AddWithValue("@from_date1", from_date);
cmd.Parameters.AddWithValue("@to_date1", to_date);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
CampSite site = new CampSite();
site.SiteNumber = Convert.ToInt32(reader["site_number"]);
site.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
site.Accessible = Convert.ToBoolean(reader["accessible"]);
site.MaxRVLength = Convert.ToInt32(reader["max_rv_length"]);
site.Fee = Convert.ToDouble(reader["daily_fee"]);
openSites.Add(site);
}
}
}
catch (SqlException ex)
{
throw;
}
return(openSites);
}
public List <CampSite> GetAvailableReservations(Campground campground, DateTime arrivalDate, DateTime departureDate)
{
List <CampSite> listOfAvailableCampsites = new List <CampSite>();
using (SqlConnection connection = new SqlConnection(_connectionstring))
{
connection.Open();
const string sqlParkCommand = "select * from site where campground_id = @SiteId and site_id not in (Select site.site_id from site " +
" Join reservation on reservation.site_id = site.site_id Where site.campground_id = @SiteId and " +
"((From_date BETWEEN @arrivaldate AND @departuredate) OR (To_date BETWEEN @arrivaldate AND @departuredate) " +
"OR (From_date <= @arrivaldate AND To_date >= @departuredate) ))";
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@arrivaldate", arrivalDate);
cmd.Parameters.AddWithValue("@departuredate", departureDate);
cmd.Parameters.AddWithValue("@SiteId", campground.CampgroundId);
cmd.CommandText = sqlParkCommand;
cmd.Connection = connection;
//Pull data off the table
SqlDataReader reader = cmd.ExecuteReader();
//Looping through the table and populating the list with all the rows
while (reader.Read())
{
CampSite campSite = new CampSite();
campSite.Id = Convert.ToInt32(reader["site_id"]);
campSite.CampgroundId = Convert.ToInt32(reader["campground_id"]);
campSite.SiteNumber = Convert.ToInt32(reader["site_number"]);
campSite.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
campSite.Accesible = Convert.ToBoolean(reader["accessible"]);
campSite.MaxRvLength = Convert.ToInt32(reader["max_rv_length"]);
campSite.Utilities = Convert.ToBoolean(reader["utilities"]);
listOfAvailableCampsites.Add(campSite);
}
}
return(listOfAvailableCampsites);
}
// //public HashSet<CampSite> ReturnAvailableCampsites(List<CampSite> campSites, List<Reservation> reservations)
// {
// List<CampSite> ReservedSites = new List<CampSite>(campSites);
// HashSet<CampSite> availableSites = new HashSet<CampSite>();
// foreach (var reservation in reservations)
// {
// foreach (var site in ReservedSites)
// {
// if(site.Id != reservation.SiteId)
// {
// }
// }
// }
// return availableSites;
// }
//}
public int WriteReservation(CampSite campsite, String reservationName, DateTime arrival, DateTime departure)
{
int reservationID;
using (SqlConnection connection = new SqlConnection(_connectionstring))
{
connection.Open();
const string sqlParkCommand = "Insert into reservation (site_id, name, from_date, to_date)" +
" Values (@siteid, @name, @from_date, @to_date); SELECT CAST(SCOPE_IDENTITY() as int);";
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@siteid", campsite.Id);
cmd.Parameters.AddWithValue("@name", reservationName);
cmd.Parameters.AddWithValue("@from_date", arrival);
cmd.Parameters.AddWithValue("@to_date", departure);
cmd.CommandText = sqlParkCommand;
cmd.Connection = connection;
cmd.ExecuteNonQuery();
reservationID = (int)cmd.ExecuteScalar();
}
return(reservationID);
}
public List <CampSite> FindReservation(CampSearch campSearch)
{
try
{
int accessible = 0;
int utilities = 0;
List <CampSite> availableSites = new List <CampSite>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
if (campSearch.IsAccessible == false)
{
accessible = 0;
}
else
{
accessible = 1;
}
if (campSearch.NeedUtilities == false)
{
utilities = 0;
}
else
{
utilities = 1;
}
SqlCommand cmd = new SqlCommand(SQL_ReservationSearch, connection);
cmd.Parameters.AddWithValue("@campground_id", campSearch.CampgroundId);
cmd.Parameters.AddWithValue("@from_date", campSearch.ArrivalDate);
cmd.Parameters.AddWithValue("@to_date", campSearch.DepartureDate);
cmd.Parameters.AddWithValue("@max_occupancy", campSearch.MaxOccupancy);
cmd.Parameters.AddWithValue("@accessible", accessible);
cmd.Parameters.AddWithValue("@max_rv_length", campSearch.RVLength);
cmd.Parameters.AddWithValue("@utilities", utilities);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int from = Convert.ToInt32(reader["open_from_mm"]);
int to = Convert.ToInt32(reader["open_to_mm"]);
if (campSearch.ArrivalDate.Month < from || campSearch.DepartureDate.Month > to)
{
break;
}
else if (campSearch.ArrivalDate < DateTime.Today || campSearch.DepartureDate < campSearch.ArrivalDate)
{
availableSites = null;
break;
}
else
{
CampSite site = new CampSite();
bool isAccessible = Convert.ToBoolean(reader["accessible"]);
string YesOrNO = site.BoolToString(isAccessible);
bool hasUtilities = Convert.ToBoolean(reader["utilities"]);
string YON = site.BoolToString(hasUtilities);
site.SiteId = Convert.ToInt32(reader["site_id"]);
site.SiteNumber = Convert.ToInt32(reader["site_number"]);
site.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
site.Accessible = YesOrNO;
site.MaxRvLength = Convert.ToInt32(reader["max_rv_length"]);
site.Utilities = YON;
site.Cost = Convert.ToDouble(reader["daily_fee"]);
availableSites.Add(site);
}
}
}
return(availableSites);
}
catch (SqlException e)
{
throw;
}
}
/// <summary>
/// 예약하기
/// </summary>
/// <param name="people"></param>
/// <param name="site"></param>
/// <returns></returns>
public static string DoReservation(People people, CampSite site)
{
try
{
var dayParameter = "";
for (var i = 0; i < site.StayDay; i++)
{
dayParameter =
string.Format(
"r1{0}={1}&rr{0}={2}&rr1{0}={3}&rTo{0}=0&b{0}=2&m{0}=&rdidx{0}={4}&cnt{0}=&rr2{0}={5}&date{0}={6}&dateEnd{0}={7}&dc_cost{0}={8}&base_cost{0}={9}&people_cost{0}=5000",
i,
HttpUtility.UrlEncode(site.SiteName, Encoding.GetEncoding("euc-kr")),
HttpUtility.UrlEncode(site.SiteNumberName, Encoding.GetEncoding("euc-kr")),
site.TotalCost,
site.SiteNumber,
site.Cost,
site.StartDate.ToString("yyyy-MM-dd"),
site.StartDate.AddDays(site.StayDay).ToString("yyyy-MM-dd"),
i == 0 ? 0 : site.DcCost, site.Cost
);
}
string parameter =
string.Format(
"campidx={0}&classnum={1}&ddate={2}&ri=1&gidx={3}&roomCount={4}&roomChk=0&roomCnt=1&{5}&roomtotal={6}&count={7}&oi=0&optCount=0&optChk=&optCnt=0&optiontotal=0&agree=on&name={8}&hp1={9}&hp2={10}&hp3={11}&email1={12}&email2={13}&user_car_num={14}&send_message=&pay_type=%B9%AB%C5%EB%C0%E5&bankinfo=%5B%BF%EC%B8%AE%5D+1002-635-646857+%28%BF%B9%B1%DD%C1%D6%3A%C3%A4%C8%F1%BD%C2%29&gopaymethod=&goodname=%BC%B1%C0%CE%C0%E5&buyername=%C8%AB%B1%E6%B5%BF&buyeremail=hkd%40abcd.com&buyertel=010-1234-5678&acceptmethod=SKIN%28ORIGINAL%29%3AHPP%281%29&oid=Merchant_Order_ID&INIregno=&ini_encfield=&ini_certid="ainterest=&paymethod=&cardcode=&cardquota=&rbankcode=&reqsign=DONE&encrypted=&sessionkey=&uid=&sid=&version=4000&clickcontrol=enable",
site.Idx,
site.Classnum,
site.StartDate.ToString("yyyy-MM-dd"),
site.GroupIdx,
site.RoomCount,
dayParameter,
site.TotalCost,
site.StayDay,
HttpUtility.UrlEncode(people.Name, Encoding.GetEncoding("euc-kr")),
people.CellPhone1,
people.CellPhone2,
people.CellPhone3,
people.Email1,
people.Email2,
people.CarNumber);
var httpWRequest =
(HttpWebRequest)
WebRequest.Create("http://www.campingsearch.co.kr/campReserve/reserveTableInfo_act.asp");
httpWRequest.Accept = "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8";
httpWRequest.Headers.Add("Accept-Encoding", "gzip,deflate,sdch");
httpWRequest.Headers.Add("Accept-Language", "en,en-US;q=0.8,ko;q=0.6");
httpWRequest.Headers.Add("Origin", "http://www.campingsearch.co.kr");
httpWRequest.Referer =
"http://www.campingsearch.co.kr/campReserve/reserveTableInfo_1.asp?optiontotal=0&roomtotal=40%2C000%BF%F8&oi=0&gidx=175&campidx=362&ri=1&ddate=2014-07-22&roomCount=15&roomChk=0&roomCnt=1&optCount=0&optChk=&optCnt=0&classnum=N&r10=%C4%B7%C7%CE%281%B9%DA%7E3%B9%DA%29&rr0=%C4%B7%C7%CE%281%B9%DA%7E3%B9%DA%29&rr10=40000&rTo0=0&b0=1&m0=&rdidx0=309&cnt0=&rr20=40000";
httpWRequest.UserAgent = "Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; WOW64; Trident/6.0)";
httpWRequest.ContentType = "application/x-www-form-urlencoded";
httpWRequest.KeepAlive = true;
httpWRequest.Method = "Post";
httpWRequest.ContentLength = Encoding.GetEncoding("euc-kr").GetBytes(parameter).Length;
var sw = new StreamWriter(httpWRequest.GetRequestStream(), Encoding.GetEncoding("euc-kr"));
sw.Write(parameter);
sw.Close();
var theResponse = (HttpWebResponse)httpWRequest.GetResponse();
var sr = new StreamReader(theResponse.GetResponseStream(), Encoding.GetEncoding("euc-kr"));
string resultHtml = sr.ReadToEnd();
return(resultHtml);
}
catch (Exception ex)
{
Util.ErrorLog(MethodBase.GetCurrentMethod(), ex, "에러");
return(null);
}
}
/// <summary>
/// Gets the campgrounds and places them in a dictionary
/// DATATYPE: is a dictionary
/// </summary>
/// <param name="parkName"></param>
/// <returns></returns>
public Dictionary <int, CampSite> GetOpenCampSites(int campgroundId, DateTime arrivalDate, DateTime depatureDate)
{
Dictionary <int, CampSite> campSite = new Dictionary <int, CampSite>();
int intArrivalDate = arrivalDate.Month;
int intDepartureDate = depatureDate.Month;
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(viewSites, conn);
command.Parameters.AddWithValue("@cgId", campgroundId);
command.Parameters.AddWithValue("@from_date_month", intArrivalDate);
command.Parameters.AddWithValue("@to_date_month", intDepartureDate);
command.Parameters.AddWithValue("@from_date", arrivalDate.AddDays(-1));
command.Parameters.AddWithValue("@to_date", depatureDate.AddDays(1));
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//create new camp ground
CampSite sites = new CampSite();
//set campground properites (reader[])
sites.Id = Convert.ToInt32(reader["site_id"]);
sites.SiteNumber = Convert.ToInt32(reader["site_number"]);
sites.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
sites.Accessiblity = Convert.ToString(reader["accessible"]);
//if the accessibility in sql is true or false
//then the accessiblity will be label as yes or no
if (sites.Accessiblity == "true")
{
sites.Accessiblity = "Yes";
}
else
{
sites.Accessiblity = "No";
}
sites.MaxRevLength = Convert.ToString(reader["max_rv_length"]);
//if the RV length is 0 or null
//then will write n/a to the console
if (sites.MaxRevLength == null || sites.MaxRevLength == "0")
{
sites.MaxRevLength = "N/A";
}
//it the utilities is set to false
// then utilities will be labeled as n/a
//if utilities are true the will label it yes
sites.Utilities = Convert.ToString(reader["utilities"]);
if (sites.Utilities == "false")
{
sites.Utilities = "N/A";
}
else
{
sites.Utilities = "Yes";
}
//if the campsite does not contain the site id in the key
//then it will add it to our campsite dictionary
if (!campSite.ContainsKey(sites.Id))
{
campSite.Add(sites.Id, sites);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(campSite);
}
