public bool GetCustRentStatus(string cid)
{
Object result = dataTier.ExecuteScalarQuery(string.Format(@"
SELECT COUNT(*)
FROM Rental
WHERE CID = {0}
AND ActDuration IS NULL;
", cid));
if (Convert.ToInt32(result) > 0)
{
return(true);
}
else
{
return(false);
}
}
//
// GetMovie:
//
// Retrieves Movie object based on MOVIE ID; returns null if movie is not
// found.
//
public Movie GetMovie(int MovieID)
{
string sql = String.Format(@"SELECT MovieName FROM Movies Where MovieID = '{0}';", MovieID);
var data = dataTier.ExecuteScalarQuery(sql);
if (data != null)
{
return(new Movie(MovieID, Convert.ToString(data)));
}
else
{
return(null);
}
}
//
// AddReview:
//
// Adds review based on MOVIE ID, returning a Review object containing
// the review, review's id, etc. If the add failed, null is returned.
//
public Review AddReview(int MovieID, int UserID, int Rating)
{
string sql = string.Format(@"Insert Into Reviews(MovieID, UserID, Rating) Values({0}, {1}, {2});
Select ReviewID From Reviews Where ReviewID = SCOPE_IDENTITY();",
MovieID, UserID, Rating);
object result = dataTier.ExecuteScalarQuery(sql);
if (result == null)
{
return(null);
}
int ReviewID = Convert.ToInt32(result.ToString());
Review r = new Review(ReviewID, MovieID, UserID, Rating);
return(r);
}
//
// 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);
}
// Gets total ridership of entire database
public Int64 GetTotalRidership()
{
Int64 result;
try
{
string sql = string.Format(@"
Select Sum(Cast(DailyTotal as bigint))
From Riderships
");
result = Convert.ToInt64(dataTier.ExecuteScalarQuery(sql));
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetTotalRidership: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(result);
}
//
//Get Number of Stations
//
public int getNumberOfStations()
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
//query for Number of Stations
string sql = string.Format(@"SELECT Count(*) From Stations");
object result = dataTier.ExecuteScalarQuery(sql);
int numStations = Convert.ToInt32(result);
return(numStations);
}
public bool GetRentStatus(string cid)
{
Object result = dataTier.ExecuteScalarQuery(string.Format(@"
SELECT COUNT(*)
FROM Rental
WHERE CID = {0}
AND ActDuration = NULL;
", cid));
return(false);
}
//
// GetMovie:
//
// Retrieves Movie object based on MOVIE ID; returns null if movie is not
// found.
//
public Movie GetMovie(int MovieID)
{
Movie newObj = null;
try
{
string sql = string.Format(@"Select MovieName
from Movies where MovieID = '{0}';", MovieID);
var result = dataTier.ExecuteScalarQuery(sql);
newObj = new Movie(MovieID, Convert.ToString(result));
return(result == null ? null : newObj); // returns null if movie is not found
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetAllNamedUsers: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
}
//
// GetCustRentStatus():
//
// Returns true if customer have ongoing rental,
// false if not.
//
public bool GetCustRentStatus(string cid)
{
bool rentStatus = false;
try
{
dataTier.openConnection();
Object result = dataTier.ExecuteScalarQuery(string.Format(@"
SELECT COUNT(*)
FROM Rental
WHERE CID = {0}
AND ActDuration IS NULL;
", cid));
if (Convert.ToInt32(result) > 0)
{
rentStatus = true;
}
else
{
rentStatus = false;
}
}
catch (Exception exc)
{
MessageBox.Show("GetCustRentStatus(): " + exc.Message);
}
finally
{
dataTier.closeConnection();
}
return(rentStatus);
}
public TotalInfo GetTotalInfo(string stationName)
{
//CTAStation station = new CTAStation()
stationName = stationName.Replace("'", "''");
try
{
//
// TODO!
//
//DataAccessTier.Data dataTier = new DataAccessTier.Data(DatabaseFilename);
string sql = string.Format(@"
SELECT Sum(Convert(bigint,DailyTotal)) As TotalOverall
FROM Riderships;
");
//MessageBox.Show(sql);
object r = dataTier.ExecuteScalarQuery(sql);
long totalOverall = Convert.ToInt64(r);
string sql2 = string.Format(@"
SELECT Sum(DailyTotal) As TotalRiders,
Avg(DailyTotal) As AvgRiders
FROM Riderships
INNER JOIN Stations ON Riderships.StationID = Stations.StationID
WHERE Name = '{0}';
", stationName);
//MessageBox.Show(sql);
DataSet result = dataTier.ExecuteNonScalarQuery(sql2);
System.Diagnostics.Debug.Assert(result.Tables["TABLE"].Rows.Count == 1);
DataRow R = result.Tables["TABLE"].Rows[0];
int stationTotal = Convert.ToInt32(R["TotalRiders"]);
double stationAvg = Convert.ToDouble(R["AvgRiders"]);
double percentage = ((double)stationTotal) / totalOverall * 100.0;
TotalInfo total = new TotalInfo(stationTotal, stationAvg, percentage);
return(total);
//stations = result;
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStationInfo: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
}
//
// GetMovie:
//
// Retrieves Movie object based on MOVIE ID; returns null if movie is not
// found.
//
public Movie GetMovie(int MovieID)
{
// SQL string
string SQL = string.Format(@"
SELECT MovieName
FROM Movies
WHERE MovieID = " + MovieID);
// Execute string
object MovieName = dataTier.ExecuteScalarQuery(SQL);
// Was object found
if (MovieName == null)
{
return(null);
}
// Movie object
Movie M = new Movie(MovieID, MovieName.ToString());
return(M);
}
//
// 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)
{
// Error handling
UserName = UserName.Replace("'", "''");
// SQL Query
string SQL = string.Format(@"SELECT UserName, UserID, Occupation
FROM Users
WHERE UserName = '******';", UserName);
// Execute Query
var userInfo = dataTier.ExecuteScalarQuery(SQL);
if (userInfo == null)
{
// SQL fails, return null
return(null);
}
else
{
User namedUser;
int userID;
DataSet ds = new DataSet();
// Execute Query
ds = dataTier.ExecuteNonScalarQuery(SQL);
var table = ds.Tables["TABLE"];
var row = table.Rows[0];
// Get userID
userID = System.Int32.Parse((row["UserID"]).ToString());
namedUser = new User(userID, UserName, (row["Occupation"]).ToString());
return(namedUser);
}
}
//
// '%' Average at particular station
//
public long getTotalRidershipAcrossAllStations()
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
//query for %Ridership
string sql = string.Format(@"SELECT SUM(Convert(bigInt, DailyTotal)) AS AllRidership FROM Riderships
INNER JOIN Stations
ON StationS.StationID = Riderships.StationID");
object result = dataTier.ExecuteScalarQuery(sql);
long allStationTotalRidership = Convert.ToInt64(result);
return(allStationTotalRidership);
}
public bool GetRentStatus(string cid)
{
Object result = dataTier.ExecuteScalarQuery(string.Format(@"
SELECT COUNT(*)
FROM Rental
WHERE CID = {0}
AND ActDuration = NULL;
", cid));
if (Convert.ToInt32(result) != 0)
return true;
else
return false;
}
//
//Average Ridership at particular station
//
public long getAvgRidership(int stationID)
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
//query for AvgRidership
string sql = string.Format(@"SELECT AVG(DailyTotal) AS AVGRidership FROM Riderships
INNER JOIN Stations
ON StationS.StationID = Riderships.StationID
WHERE Stations.StationID = '{0}'
", stationID);
object result = dataTier.ExecuteScalarQuery(sql);
long avgRidership = Convert.ToInt64(result);
return(avgRidership);
}
//
//Is this stop Handicap Acceisible?
//
public Boolean handicapAccessible(string stopName)
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
//query for Handicap Accessible
string sql = string.Format(@"Select ADA from Stops
Inner Join Stations
ON Stations.StationID = Stops.StationID
WHERE Stops.Name = '{0}'", stopName);
object result = dataTier.ExecuteScalarQuery(sql);
bool value = Convert.ToBoolean(result);
return(value);
}
//
//Direction of Travel
//
public char getDirection(string stopName)
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
//query for Direction of travel
string sql = string.Format(@"Select Stops.Direction from Stops
Inner Join Stations
ON Stations.StationID = Stops.StationID
Where Stops.Name = '{0}'", stopName);
object result = dataTier.ExecuteScalarQuery(sql);
char direction = Convert.ToChar(result);
return(direction);
}
//
//Location Longitude
//
public string getLongitude(string stopName)
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
//query for Location
string sql = string.Format(@"Select Stops.Longitude from Stops
Inner Join Stations
ON Stations.StationID = Stops.StationID
Where Stops.Name = '{0}'", stopName);
object result = dataTier.ExecuteScalarQuery(sql);
string longitude = Convert.ToString(result);
return(longitude);
}
//
// Ridership for weekdays
//
public long getRidershipOnSundayHoliday(int stationID)
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
//query to get Ridership for weekdays, saturday, sun/holiday
string sql = string.Format(@"SELECT Sum(DailyTotal) from Riderships
Inner Join Stations
ON stations.StationID = Riderships.StationID
Where Stations.StationID = '{0}' AND
Riderships.TypeOfDay = 'U'", stationID);
object result = dataTier.ExecuteScalarQuery(sql);
long ridership = Convert.ToInt64(result);
return(ridership);
}
//
// GetMovie:
//
// Retrieves Movie object based on MOVIE ID; returns null if movie is not
// found.
//
public Movie GetMovie(int MovieID)
{
//
// TODO!
//
string sql = string.Format("SELECT MovieName FROM Movies WHERE MovieID={0};", MovieID);
object result = datatier.ExecuteScalarQuery(sql);
if (result == null || result.ToString() == "")
{
return(null);
}
return(new Movie(MovieID, result.ToString()));
}
public long getRidershipT(string name)
{
long totalOverall;
try
{
string sql = string.Format(@"Select Sum(convert(bigint, DailyTotal)) as Total
FROM Riderships
INNER JOIN Stations
ON Riderships.StationID = Stations.StationID
Where Stations.Name ='{0}'", name);
Object result = dataTier.ExecuteScalarQuery(sql);
totalOverall = Convert.ToInt64(result);
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(totalOverall);
}
//Function to get Ridership Info
public CTARidership GetRiderInfo(string nameStation)
{
String sql = string.Format(@" SELECT Sum(DailyTotal) As Total, Avg(DailyTotal) As Average FROM Riderships INNER JOIN Stations ON Riderships.StationID = Stations.StationID WHERE Name = '{0}';", nameStation);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
DataRow row = ds.Tables["TABLE"].Rows[0];
DataRow row2 = ds.Tables["TABLE"].Rows[0];
//% Ridership
string sql2 = string.Format(@"SELECT Sum(Convert(bigint,DailyTotal)) As TotalAll FROM Riderships;");
object result = dataTier.ExecuteScalarQuery(sql2);
string per = string.Format("{0:0.00}%", ((double)Convert.ToInt64(row["Total"])) / Convert.ToInt64(result) * 100.0);
var ss = new CTARidership(Convert.ToInt32(row["Total"]).ToString("#,0"), Convert.ToDouble(row2["Average"]).ToString("0#,##0/day"), per);
return(ss);
}
public String getSecurityQuestion(String userName)
{
string sql = string.Format(@"
SELECT Question FROM Questions
INNER JOIN(
SELECT QuestionID FROM Users WHERE UserName='******'
)T
ON T.QuestionID = Questions.ID;
", userName);
Object result = dataTier.ExecuteScalarQuery(sql);
if (result == null)
{
return(String.Empty);
}
return(result.ToString());
}
//
// GetMovie:
//
// Retrieves Movie object based on MOVIE ID; returns null if movie is not
// found.
//
public Movie GetMovie(int MovieID)
{
//MovieID = 1000;
string sql = string.Format(@"SELECT MovieName FROM Movies where Movies.MovieID = {0};", MovieID);
object mname = dataTier.ExecuteScalarQuery(sql);
if (mname != null)
{
Movie movie = new Movie(MovieID, Convert.ToString(mname));
return(movie);
}
else
{
return(null);
}
}
private bool bikeAvailable(int bikeID)
{
DataAccessTier.Data data = new DataAccessTier.Data(this.Filename.Text);
string checkAva = string.Format(@"
SELECT RentedOut
FROM Bike
WHERE BID = {0};",
bikeID);
var available = data.ExecuteScalarQuery(checkAva);
if (available.Equals(true))
{
string msg = string.Format("Bike ID is not available: '{0}'",
bikeID);
MessageBox.Show(msg);
return(false);
}
return(true);
}
private bool bikeExists(int bikeID)
{
DataAccessTier.Data data = new DataAccessTier.Data(this.Filename.Text);
string checkID = string.Format(@"
SELECT BID
FROM Bike
WHERE BID = {0};",
bikeID);
var exists = data.ExecuteScalarQuery(checkID);
if (exists == null)
{
string msg = string.Format("Bike ID not found: '{0}'",
bikeID);
MessageBox.Show(msg);
return(false);
}
return(true);
}
public long TotalRidership()
{
long total = 0;
object result;
try
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
string sql = string.Format(@"
SELECT Sum(Convert(bigint,DailyTotal)) As TotalOverall
FROM Riderships;");
result = dataTier.ExecuteScalarQuery(sql);
total = Convert.ToInt64(result);
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.TotalRidership: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(total);
}
public int HolidayTotal(int id)
{
int total = 0;
object result;
try
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
string sql = string.Format(@"
SELECT Sum(DailyTotal) FROM Riderships
WHERE Riderships.StationID = '{0}' AND
TypeOfDay = 'U';", id);
result = dataTier.ExecuteScalarQuery(sql);
total = Convert.ToInt32(result);
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.HolidayTotal: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(total);
}
public int GetLineID(int id)
{
int lineid = 0;
object result;
try
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
string sql = string.Format(@"
SELECT LineID FROM StopDetails
WHERE StopID = '{0}';
", id);
result = dataTier.ExecuteScalarQuery(sql);
lineid = Convert.ToInt32(result);
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetLineID: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(lineid);
}
public string GetLon(int id)
{
string L = "";
object result;
try
{
DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile);
string sql = string.Format(@"
SELECT Longitude FROM Stops
WHERE StopID = '{0}';
", id);
result = dataTier.ExecuteScalarQuery(sql);
L = Convert.ToString(result);
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetLon: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(L);
}
