//
// GetCustomers():
//
// Returns all the customers from Db as a read only list.
//
public IReadOnlyList <Customer> GetCustomers()
{
List <Customer> customers = new List <Customer>();
try
{
// query
DataSet ds = dataTier.ExecuteNonScalarQuery(string.Format(@"
SELECT *
FROM Customer
ORDER BY LastName ASC,
FirstName ASC;
"
));
// build list
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
customers.Add(
new Customer(
Convert.ToInt32(row["CID"].ToString()),
row["FirstName"].ToString(),
row["LastName"].ToString(),
row["Email"].ToString()
)
);
}
}
catch (Exception e)
{
throw new ApplicationException(string.Format
("Error in Business.GetCustomers(): '{0}'", e.Message));
}
return(customers);
}
//
// List all the bikes:
//
private void cmdLoadBikes_Click(object sender, EventArgs e)
{
this.lstBikes.Items.Clear();
ClearBikeInfo();
// ADDED INDEX BikeTypes_PricePerHour FOR FASTER EXECUTION
string sql = string.Format(@"
Select BID, Year, Description, PricePerHour
From Bikes
Inner Join BikeTypes WITH (INDEX(BikeTypes_PricePerHour)) On Bikes.TID = BikeTypes.TID
Order by BID;
");
//MessageBox.Show(sql);
DataAccessTier.Data datatier = new DataAccessTier.Data(dbfilename);
try
{
DataSet ds = datatier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
Bike b = new Bike(
Convert.ToInt32(row["BID"]),
Convert.ToInt32(row["Year"]),
row["Description"].ToString(),
Convert.ToDecimal(row["PricePerHour"]));
this.lstBikes.Items.Add(b);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//
// GetUser
//
// Retrieves User from the database
//
public StockUser GetUser(string userName, string password)
{
string sql = string.Format(Records.LoginVerificationSQL, userName, password);
DataSet ds = datatier.ExecuteNonScalarQuery(sql);
DataTable dt = ds.Tables["UserDetails"];
int userID = 0;
string fName = null;
string lName = null;
string email = null;
double funds = 0.0;
foreach (DataRow row in dt.Rows)
{
userID = Convert.ToInt32(row["UserID"].ToString());
fName = row["FirstName"].ToString();
lName = row["LastName"].ToString();
email = row["Email"].ToString();
funds = Convert.ToDouble(row["FUNDS"].ToString());
}
return(new StockUser(userID, fName, lName, email, funds));
}
//
//Lines list
//
public List <String> getLines(string stopName)
{
List <String> lines = new List <string>();
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
//query to get lines...
string sql = string.Format(@"Select Color from Lines
Inner Join StopDetails
On StopDetails.LineID = Lines.LineID
Inner Join Stops
On Stops.StopID = StopDetails.StopID
Where Stops.Name = '{0}'", stopName);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
string ld = Convert.ToString(row["Color"]);
lines.Add(ld);
}
return(lines);
}
///
/// <summary>
/// Returns the CTA Stops associated with a given station,
/// ordered by name.
/// </summary>
/// <returns>Read-only list of CTAStop objects</returns>
///
public IReadOnlyList <CTAStop> GetStops(int stationID)
{
List <CTAStop> stops = new List <CTAStop>();
DataSet result;
try
{
//
// TODO!
//
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
string sql = string.Format(@"SELECT * FROM Stops
WHERE Stops.StationID = '{0}'; ", stationID);
result = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in result.Tables["TABLE"].Rows)
{
int Stationid = Convert.ToInt32(row["StationID"]);
int Stopid = Convert.ToInt32(row["StopID"]);
string name = string.Format("{0}", Convert.ToString(row["Name"]));
string direction = string.Format("{0}", Convert.ToString(row["Direction"]));
bool ada = Convert.ToBoolean(row["ADA"]);
double lat = Convert.ToDouble(row["Latitude"]);
double lon = Convert.ToDouble(row["Longitude"]);
var s = new BusinessTier.CTAStop(Stopid, name, Stationid, direction, ada, lat, lon);
stops.Add(s);
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStops: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stops);
}
//
// GetNamedUser:
//
// Retrieves User object based on USER NAME; returns null if user is not
// found.
//
// NOTE: there are "named" users from the Users table, and anonymous users
// that only exist in the Reviews table. This function only looks up "named"
// users from the Users table.
//
public User GetNamedUser(string UserName)
{
// Handle special character '
UserName = UserName.Replace("'", "''");
// SQL string
string SQL = string.Format(@"
SELECT UserID, Occupation
FROM Users
WHERE UserName = '******'");
// Execute SQL
DataSet ds = dataTier.ExecuteNonScalarQuery(SQL);
DataTable dt = ds.Tables["TABLE"];
// Create user object
UserName = UserName.Replace("''", "'");
int UserID = Convert.ToInt32(dt.Rows[0]["UserID"]);
string Occupation = Convert.ToString(dt.Rows[0]["Occupation"]);
User u = new User(UserID, UserName, Occupation);
// return User
return(u);
}
//
// GetNamedUser:
//
// Retrieves User object based on USER NAME; returns null if user is not
// found.
//
// NOTE: there are "named" users from the Users table, and anonymous users
// that only exist in the Reviews table. This function only looks up "named"
// users from the Users table.
//
public User GetNamedUser(string UserName)
{
UserName = UserName.Replace("'", "''");
string sql = String.Format(@"SELECT UserID, Occupation from Users where UserName = '******'", UserName);
var user = dataTier.ExecuteNonScalarQuery(sql);
User users = null;
if (user != null)
{
foreach (DataRow row in user.Tables["TABLE"].Rows)
{
users = new User(Convert.ToInt32(row["UserID"]), UserName, Convert.ToString(row["Occupation"]));
}
return(users);
}
else
{
return(null);
}
}
///
/// <summary>
/// Returns all the CTA Stations, ordered by name.
/// </summary>
/// <returns>Read-only list of CTAStation objects</returns>
///
public IReadOnlyList <CTAStation> GetStations()
{
List <CTAStation> stations = new List <CTAStation>();
try
{
string sql = "Select StationID , Name from Stations order by Name asc ";
DataSet result = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in result.Tables["TABLE"].Rows)
{
stations.Add(new CTAStation(Convert.ToInt32(row["StationID"]), Convert.ToString(row["Name"])));
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stations);
}
} // done
public Reviews GetReviews(int MovieID)
{
Reviews reviews = new Reviews();
string sql = string.Format(@"SELECT UserID, Rating
FROM Reviews
WHERE MovieID={0}
ORDER BY Rating Desc, UserID ASC;",
MovieID);
DataSet ds = datatier.ExecuteNonScalarQuery(sql);
DataTable dt = ds.Tables["TABLE"];
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
foreach (DataRow row in dt.Rows)
{
reviews.Add(new Review(Convert.ToInt32(row["UserID"]), MovieID, Convert.ToInt32(row["Rating"])));
}
return(reviews);
}
} //done
//
// GetUser:
//
// Retrieves User object based on USER ID; returns null if user is not
// found.
//
// NOTE: if the user exists in the Users table, then a meaningful name and
// occupation are returned in the User object. If the user does not exist
// in the Users table, then the user id has to be looked up in the Reviews
// table to see if he/she has submitted 1 or more reviews as an "anonymous"
// user. If the id is found in the Reviews table, then the user is an
// "anonymous" user, so a User object with name = "<UserID>" and no occupation
// ("") is returned. In other words, name = the user’s id surrounded by < >.
//
public User GetUser(int UserID)
{
try
{
string sql = string.Format(@"Select UserName, Occupation
from Users where UserID = '{0}';", UserID);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
if (ds.Tables["TABLE"].Rows.Count == 0)
{
return(null); // check if the user exists
}
DataRow row = ds.Tables["TABLE"].Rows[0];
User newObj = new User(UserID,
Convert.ToString(row["UserName"]),
Convert.ToString(row["Occupation"]));
return(newObj);
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetUser: '******'", ex.Message);
throw new ApplicationException(msg);
}
}
//
// User has clicked button indicating that selected customer is returning
// from their rental.
//
private void cmdReturn_Click(object sender, EventArgs e)
{
string version = "MSSQLLocalDB"; // for VS 2015 and newer:
string DBFile = "BikeHike.mdf";
string DBConnectionInfo = String.Format(@"Data Source=(LocalDB)\{0};AttachDbFilename=|DataDirectory|\{1};Integrated Security=True;",
version,
DBFile);
// Set up connection
SqlConnection db;
db = new SqlConnection(DBConnectionInfo);
db.Open();
// Initialize transaction (serializable)
SqlTransaction tx = db.BeginTransaction(IsolationLevel.Serializable);
// is a customer selected?
Customer c = (Customer)this.lstCustomers.SelectedItem;
if (c == null)
{
MessageBox.Show("Please select a customer...");
return;
}
int RID, N;
DateTime start;
double expectedDuration;
if (!c.OnRental(out RID, out start, out expectedDuration, out N))
{
MessageBox.Show("This customer has no bikes to return...");
tx.Rollback();
db.Close();
return;
}
//
// Compute duration of rental:
//
TimeSpan duration = DateTime.Now.Subtract(start);
double hours = (duration.Days * 24) +
duration.Hours +
(duration.Minutes / 60.0) +
(duration.Seconds / 3600.00);
// Used for deadlock
int retries = 0;
//MessageBox.Show("duration: " + hours.ToString());
while (retries < 3)
{
//
// now lookup the bikes involved in rental, and compute
// total price:
//
try
{
// ADDED INDEX BikeTypes_PricePerHour FOR FASTER EXECUTION
string sql = string.Format(@"
SELECT Bikes.BID, PricePerHour
FROM RentalDetails
INNER JOIN Bikes ON RentalDetails.BID = Bikes.BID
INNER JOIN BikeTypes WITH (INDEX(BikeTypes_PricePerHour)) ON Bikes.TID = BikeTypes.TID
WHERE RID = {0};
", RID);
//MessageBox.Show(sql);
DataAccessTier.Data datatier = new DataAccessTier.Data(dbfilename);
double totalprice = 0.0;
List <int> bids = new List <int>();
DataSet ds = datatier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["Table"].Rows)
{
bids.Add(Convert.ToInt32(row["BID"]));
totalprice += (hours * Convert.ToDouble(row["PricePerHour"]));
}
//
// we have what we need to update the rental record:
//
sql = string.Format(@"
UPDATE Rentals
SET ActDuration = {0}, TotalPrice = {1}
WHERE RID = {2};
", hours, totalprice, RID);
//MessageBox.Show(sql);
SqlCommand cmd = new SqlCommand();
cmd.Connection = db;
cmd.CommandText = sql;
cmd.Transaction = tx;
object result = cmd.ExecuteNonQuery();
// Rollback
if (System.Int32.Parse(result.ToString()) != 1)
{
tx.Rollback();
break;
}
//
// Finally, update each of the bikes' Rented flag so
// they can be rented again:
//
bool first = true;
sql = "";
foreach (int bid in bids)
{
if (first) //create base query:
{
first = false;
sql = string.Format(@"
UPDATE Bikes
SET Rented = 0
WHERE BID = {0}
", bid);
}
else // append to base query for next update:
{
sql = string.Format(@"
{0} OR BID = {1}
", sql, bid);
}
}//foreach
sql = sql + ";";
cmd.CommandText = sql;
cmd.Transaction = tx;
object result2 = cmd.ExecuteNonQuery();
// Delay
int timeInMS;
if (System.Int32.TryParse(this.textBox1.Text, out timeInMS) == true)
{
;
}
else
{
timeInMS = 0; // no delay:
}
System.Threading.Thread.Sleep(timeInMS);
// Rollback
if (bids.Count != N) // database correctness check:
{
MessageBox.Show("**Sanity check: # of bikes in rentals table doesn't agree with # of bikes in details table?!");
tx.Rollback();
break;
}
// Rollback
if (System.Int32.Parse(result2.ToString()) != N)
{
tx.Rollback();
break;
}
//
// Success, rental updated and all bikes returned:
//
string msg = string.Format("Return complete, total cost ${0:0.00}", totalprice);
MessageBox.Show(msg);
tx.Commit();
//
// Update selected customer since no longer renting:
//
int index = this.lstCustomers.SelectedIndex;
if (index >= 0)
{
this.lstCustomers.SelectedIndex = -1;
this.lstCustomers.SelectedIndex = index;
}
//
// Likewise, update selected bike (if any):
//
index = this.lstBikes.SelectedIndex;
if (index >= 0)
{
this.lstBikes.SelectedIndex = -1;
this.lstBikes.SelectedIndex = index;
}
//
// Finally, update the list of available bikes:
//
cmdForRent_Click(sender, e);
break;
}
// Deadlock
catch (SqlException ex)
{
if (ex.Number == 1205)
{
retries++;
}
else
{
break;
}
}
// Rollback
catch (Exception ex)
{
tx.Rollback();
MessageBox.Show(ex.Message);
break;
}
finally
{
db.Close();
}
}
}
public bool OnRental(out DateTime dueback)
{
dueback = DateTime.Now;
//
// bike is on rental based on Rented attribute:
//
string sql = string.Format(@"
Select Rented
From Bikes
Where BID = {0};
", BID);
DataAccessTier.Data datatier = new DataAccessTier.Data("BikeHike.mdf");
try
{
object result = datatier.ExecuteScalarQuery(sql);
if (result == null) // something is wrong, bike not found...
{
System.Diagnostics.Debug.WriteLine("**Internal error: bike not found?!");
return(false);
}
int rented = Convert.ToInt32(result);
if (rented == 0) // not rented:
{
return(false);
}
//
// Since the bike is rented, display expected return date and time...
// We have the Bike ID, so we have to join the rental details with the
// Rentals table to get the rental info --- in particular start time and
// expected duration. Note that this bike has probably been rented
// before, so to find the correct rental, we want the MOST RECENT rental
// for this bike. So we order the rentals by details ID in descending
// order, and take the first record (top 1).
//
sql = string.Format(@"
SELECT Top 1 StartTime, ExpDuration
FROM Rentals
INNER JOIN RentalDetails ON Rentals.RID = RentalDetails.RID
WHERE BID = {0}
ORDER BY RDID DESC;
", BID);
DataSet ds = datatier.ExecuteNonScalarQuery(sql);
if (ds.Tables["TABLE"].Rows.Count == 0) // not found?!
{
System.Diagnostics.Debug.WriteLine("**Internal error, rental record not found?!");
return(false);
}
DataRow row = ds.Tables["TABLE"].Rows[0];
DateTime start = Convert.ToDateTime(row["StartTime"]);
double duration = Convert.ToDouble(row["ExpDuration"]);
dueback = start.AddHours(duration);
return(true); // on rental:
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return(false);
}
}
//
// GetUser:
//
// Retrieves User object based on USER ID; returns null if user is not
// found.
//
// NOTE: if the user exists in the Users table, then a meaningful name and
// occupation are returned in the User object. If the user does not exist
// in the Users table, then the user id has to be looked up in the Reviews
// table to see if he/she has submitted 1 or more reviews as an "anonymous"
// user. If the id is found in the Reviews table, then the user is an
// "anonymous" user, so a User object with name = "<UserID>" and no occupation
// ("") is returned. In other words, name = the user’s id surrounded by < >.
//
public User GetUser(int UserID)
{
string sql = string.Format("Select * From Users Where UserID = {0}", UserID);
DataSet userData = dataTier.ExecuteNonScalarQuery(sql);
DataTable dt = userData.Tables["TABLE"];
if (dt.Rows.Count == 0) // not in users table
{
string sql2 = string.Format("Select * From Reviews Where UserID = {0}", UserID);
DataSet userData2 = dataTier.ExecuteNonScalarQuery(sql2);
DataTable dt2 = userData2.Tables["Table"];
if (dt2.Rows.Count == 0) // also not int reviews table
{
return(null);
}
else
{
string userName = string.Format("<anonymous>");
User s = new User(UserID, userName, null);
return(s);
}
}
else // in users table
{
foreach (DataRow row in dt.Rows)
{
string userName = row["UserName"].ToString();
string occupation = row["Occupation"].ToString();
User s = new User(UserID, userName, occupation);
return(s);
}
}
return(null);
}
private void PrimeData_SelectedIndexChanged(object sender, EventArgs e)
{
if (!fileExists(this.Filename.Text))
{
return;
}
DataAccessTier.Data data = new DataAccessTier.Data(this.Filename.Text);
string listData = this.PrimeData.Text;
//Check to see whether Primary Data Table contains Bikes or Customers or is empty
if (listData == "")
{
return;
}
if (listData.Contains("BID: "))
{
//Data is a Bike so get Bike data from Bike table
SecData.Items.Clear();
listData = listData.Substring(5);
string dispData = string.Format(@"
SELECT Bike_Type._Description, Bike.YearDeployed, Bike_Type.Price, Bike.RentedOut
FROM Bike
INNER JOIN Bike_Type ON Bike_Type.BTID = Bike.BTID
WHERE BID = {0};",
listData);
DataSet ds = new DataSet();
ds = data.ExecuteNonScalarQuery(dispData);
SecData.Items.Add("Type: " + ds.Tables[0].Rows[0]["_Description"].ToString());
SecData.Items.Add("Year: " + ds.Tables[0].Rows[0]["YearDeployed"].ToString());
SecData.Items.Add("Price: $" + ds.Tables[0].Rows[0]["Price"].ToString());
if (ds.Tables[0].Rows[0]["RentedOut"].ToString() == "False")
{
SecData.Items.Add("Status: Available");
}
else
{
SecData.Items.Add("Status: Unavailable");
string rentData = string.Format(@"
SELECT _Started, ExpectedHours
FROM Rentals
WHERE BID = {0} AND Returned IS NULL;",
listData);
DataSet rds = new DataSet();
rds = data.ExecuteNonScalarQuery(rentData);
DateTime start = Convert.ToDateTime(rds.Tables[0].Rows[0]["_Started"]);
DateTime end = start.AddHours(Convert.ToDouble(rds.Tables[0].Rows[0]["ExpectedHours"]));
SecData.Items.Add("Start: " + start.ToString());
SecData.Items.Add("Expected End: " + end.ToString());
}
}
else if (listData.Contains(" "))
{
return;
}
else
{
//Data is a Customer so get Customer data from Customer table
SecData.Items.Clear();
string[] names = listData.Split(',');
string last = names[0];
string first = names[1];
string dispData = string.Format(@"
SELECT CID, Email, RentingOut
FROM Customer
WHERE FirstName = '{0}' AND LastName = '{1}';",
first, last);
DataSet ds = new DataSet();
ds = data.ExecuteNonScalarQuery(dispData);
int CID = Int32.Parse(ds.Tables[0].Rows[0]["CID"].ToString());
SecData.Items.Add("ID: " + ds.Tables[0].Rows[0]["CID"].ToString());
SecData.Items.Add("Email: " + ds.Tables[0].Rows[0]["Email"].ToString());
if (ds.Tables[0].Rows[0]["RentingOut"].ToString() == "False")
{
SecData.Items.Add("Status: Available");
}
else
{
SecData.Items.Add("Status: Unavailable");
string rentData = string.Format(@"
SELECT RID, _Started, ExpectedHours
FROM Rentals
WHERE CID = {0} AND Returned IS NULL;",
CID);
DataSet rds = new DataSet();
rds = data.ExecuteNonScalarQuery(rentData);
DateTime start = Convert.ToDateTime(rds.Tables[0].Rows[0]["_Started"]);
DateTime end = start.AddHours(Convert.ToDouble(rds.Tables[0].Rows[0]["ExpectedHours"]));
SecData.Items.Add("Bikes Rented: " + rds.Tables[0].Rows.Count.ToString());
SecData.Items.Add("Start: " + start.ToString());
SecData.Items.Add("Expected End: " + end.ToString());
}
}
}
///
/// <summary>
/// Returns overall stats about crimes in Chicago.
/// </summary>
/// <returns>CrimeStats object</returns>
///
public CrimeStats GetOverallCrimeStats()
{
CrimeStats cs;
Business bus = new Business("C:\\Users\\Jacky\\Downloads\\CrimeDB.mdf");
string sql = @"
Select Min(Year) As MinYear, Max(Year) As MaxYear, Count(*) As Total
From Crimes;
";
DataSet result = dataTier.ExecuteNonScalarQuery(sql);
DataRow row = result.Tables["TABLE"].Rows[0];
int minYear = System.Convert.ToInt32(row["MinYear"]);
int maxYear = System.Convert.ToInt32(row["MaxYear"]);
long total = System.Convert.ToInt64(row["Total"]);
//
// TODO!
//
cs = new CrimeStats(total, minYear, maxYear);
return(cs);
}
public bool validateUser(User usr)
{
if (usr.userName == string.Empty || usr.password == string.Empty)
{
throw new System.ArgumentException("UserName or password can't be empty");
}
else
{
string sql = string.Format(@"
SELECT ID, FirstName, LastName, Email, Balance
FROM Users WHERE UserName='******' AND UserPassword='******';
", usr.userName, usr.password);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(false);
}
DataTable dt = ds.Tables["TABLE"];
foreach (DataRow r in dt.Rows)
{
usr.id = System.Convert.ToInt32(r["ID"]);
usr.firstName = System.Convert.ToString(r["FirstName"]);
usr.lastName = System.Convert.ToString(r["LastName"]);
usr.email = System.Convert.ToString(r["Email"]);
usr.balance = System.Convert.ToDouble(r["Balance"]);
return(true);
}
}
return(false);
}
//
// GetUser:
//
// Retrieves User object based on USER ID; returns null if user is not
// found.
//
// NOTE: if the user exists in the Users table, then a meaningful name and
// occupation are returned in the User object. If the user does not exist
// in the Users table, then the user id has to be looked up in the Reviews
// table to see if he/she has submitted 1 or more reviews as an "anonymous"
// user. If the id is found in the Reviews table, then the user is an
// "anonymous" user, so a User object with name = "<UserID>" and no occupation
// ("") is returned. In other words, name = the user’s id surrounded by < >.
//
public User GetUser(int UserID)
{
//
// TODO!
//
string sql = string.Format("Select * From Users Where UserID = {0}", UserID);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
//check if the user was found in the user file ... if found cerat new user object and return;
if (ds.Tables["TABLE"].Rows.Count != 0)
{
object ID = ds.Tables["TABLE"].Rows[0]["UserID"];
int uID = Convert.ToInt32(ID);
string name = Convert.ToString(ds.Tables["TABLE"].Rows[0]["UserName"]);
string Occupation = Convert.ToString(ds.Tables["TABLE"].Rows[0]["Occupation"]);
User u = new User(uID, name, Occupation);
return(u);
}
// not found now look in the review...
else
{
sql = string.Format(@"Select UserID From Reviews
Where UserID = '{0}'
Group by UserID", UserID);
object result = dataTier.ExecuteScalarQuery(sql);
if (result != null)
{
string name = string.Format("<{0}>", UserID);
User u = new User(UserID, name, "Unknown");
return(u);
}
}
return(null);
}
///
/// <summary>
/// Returns all the CTA Stations, ordered by name.
/// </summary>
/// <returns>Read-only list of CTAStation objects</returns>
///
public IReadOnlyList <CTAStation> GetStations()
{
List <CTAStation> stations = new List <CTAStation>();
try
{
//
// TODO!
//
string sql = string.Format(@"
SELECT Name
FROM Stations
ORDER BY Name ASC;");
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
int stationID = 0;
string stationName;
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
stationName = Convert.ToString(row["Name"]);
stations.Add(new CTAStation(stationID, stationName));
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stations);
}
//
// User has clicked button indicating that selected customer is returning
// from their rental.
//
private void cmdReturn_Click(object sender, EventArgs e)
{
// is a customer selected?
Customer c = (Customer)this.lstCustomers.SelectedItem;
if (c == null)
{
MessageBox.Show("Please select a customer...");
return;
}
int RID, N;
DateTime start;
double expectedDuration;
if (!c.OnRental(out RID, out start, out expectedDuration, out N))
{
MessageBox.Show("This customer has no bikes to return...");
return;
}
//
// Compute duration of rental:
//
TimeSpan duration = DateTime.Now.Subtract(start);
double hours = (duration.Days * 24) +
duration.Hours +
(duration.Minutes / 60.0) +
(duration.Seconds / 3600.00);
//MessageBox.Show("duration: " + hours.ToString());
//
// now lookup the bikes involved in rental, and compute
// total price:
//
try
{
string sql = string.Format(@"
SELECT Bikes.BID, PricePerHour
FROM RentalDetails
INNER JOIN Bikes ON RentalDetails.BID = Bikes.BID
INNER JOIN BikeTypes ON Bikes.TID = BikeTypes.TID
WHERE RID = {0};
", RID);
//MessageBox.Show(sql);
DataAccessTier.Data datatier = new DataAccessTier.Data(dbfilename);
double totalprice = 0.0;
List <int> bids = new List <int>();
DataSet ds = datatier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["Table"].Rows)
{
bids.Add(Convert.ToInt32(row["BID"]));
totalprice += (hours * Convert.ToDouble(row["PricePerHour"]));
}
//
// we have what we need to update the rental record:
//
sql = string.Format(@"
UPDATE Rentals
SET ActDuration = {0}, TotalPrice = {1}
WHERE RID = {2};
", hours, totalprice, RID);
//MessageBox.Show(sql);
int rowsModified = datatier.ExecuteActionQuery(sql);
if (rowsModified != 1)
{
MessageBox.Show("**Internal error, update of rental record failed?!");
return;
}
//
// Finally, update each of the bikes' Rented flag so
// they can be rented again:
//
bool first = true;
sql = "";
foreach (int bid in bids)
{
if (first) //create base query:
{
first = false;
sql = string.Format(@"
UPDATE Bikes
SET Rented = 0
WHERE BID = {0}
", bid);
}
else // append to base query for next update:
{
sql = string.Format(@"
{0} OR BID = {1}
", sql, bid);
}
}//foreach
sql = sql + ";";
//MessageBox.Show(sql);
if (bids.Count != N) // database correctness check:
{
MessageBox.Show("**Sanity check: # of bikes in rentals table doesn't agree with # of bikes in details table?!");
}
rowsModified = datatier.ExecuteActionQuery(sql);
if (rowsModified != N)
{
MessageBox.Show("**Internal error, update of bike record(s) failed?!");
return;
}
//
// Success, rental updated and all bikes returned:
//
string msg = string.Format("Return complete, total cost ${0:0.00}", totalprice);
MessageBox.Show(msg);
//
// Update selected customer since no longer renting:
//
int index = this.lstCustomers.SelectedIndex;
if (index >= 0)
{
this.lstCustomers.SelectedIndex = -1;
this.lstCustomers.SelectedIndex = index;
}
//
// Likewise, update selected bike (if any):
//
index = this.lstBikes.SelectedIndex;
if (index >= 0)
{
this.lstBikes.SelectedIndex = -1;
this.lstBikes.SelectedIndex = index;
}
//
// Finally, update the list of available bikes:
//
cmdForRent_Click(sender, e);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ReturnRental_Click(object sender, EventArgs e)
{
if (!fileExists(this.Filename.Text))
{
return;
}
DataAccessTier.Data data = new DataAccessTier.Data(this.Filename.Text);
string primeData = this.PrimeData.Text;
if (primeData.Contains(" ") || primeData == "")
{
return;
}
string[] name = primeData.Split(',');
string custIDSql = string.Format(@"
SELECT CID
FROM Customer
WHERE LastName = '{0}' AND FirstName = '{1}';",
name[0], name[1]);
var custIDOBJ = data.ExecuteScalarQuery(custIDSql);
int custID = Int32.Parse(custIDOBJ.ToString());
string custSql = string.Format(@"
SELECT RentingOut
FROM Customer
WHERE CID = {0};",
custID);
var d = data.ExecuteScalarQuery(custSql);
if (d.Equals(false))
{
MessageBox.Show("Customer is not currently renting out a bike...");
return;
}
string getBID = string.Format(@"
SELECT RID, BID
FROM Rentals
WHERE CID = {0} AND Returned IS NULL;",
custID);
DataSet ds = new DataSet();
ds = data.ExecuteNonScalarQuery(getBID);
string rentalUpdate = string.Format(@"
UPDATE Rentals
SET Returned = GetDate()
WHERE CID = {0};",
custID);
data.ExecuteActionQuery(rentalUpdate);
string custUpdate = string.Format(@"
UPDATE Customer
SET RentingOut = 0
WHERE CID = {0};",
custID);
data.ExecuteActionQuery(custUpdate);
foreach (DataRow row in ds.Tables[0].Rows)
{
string bikeUpdate = string.Format(@"
UPDATE Bike
SET RentedOut = 0
WHERE BID = {0};",
row["BID"]);
data.ExecuteActionQuery(bikeUpdate);
}
double total = 0.0;
DataSet money = new DataSet();
foreach (DataRow row in ds.Tables[0].Rows)
{
string bikeUpdate = string.Format(@"
SELECT Bike_Type.Price, Rentals._Started, Rentals.Returned
FROM Rentals
INNER JOIN Bike ON Bike.BID = Rentals.BID
INNER JOIN Bike_Type ON Bike.BTID = Bike_Type.BTID
WHERE RID = {0};",
row["RID"]);
money = data.ExecuteNonScalarQuery(bikeUpdate);
double price = Convert.ToDouble(money.Tables[0].Rows[0]["Price"]);
TimeSpan datetime = Convert.ToDateTime(money.Tables[0].Rows[0]["Returned"]).Subtract(Convert.ToDateTime(money.Tables[0].Rows[0]["_Started"]));
double time = datetime.TotalHours;
total = total + (price * time);
}
decimal sum = Convert.ToDecimal(total);
sum = Decimal.Round(sum, 2);
MessageBox.Show("Rental Cost: $" + sum.ToString());
}