public static DataTable GetDataRouteByID(string id)
{
string sql = "SELECT RouteID, " +
" ((SELECT CityName FROM CITIES WHERE CityID = FromCityID) + ' - '" +
" + (SELECT CityName FROM CITIES WHERE CityID = ToCityID)) as Route, Duration" +
" FROM ROUTES WHERE RouteID = \'" + id + "\'";
return(DAO.GetDataTable(sql));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static DataTable GetDataRoute()
{
string sql = "SELECT RouteID, " +
" ((SELECT CityName FROM CITIES WHERE CityID = FromCityID) + ' - '" +
" + (SELECT CityName FROM CITIES WHERE CityID = ToCityID)) as Route, Duration" +
" FROM ROUTES ORDER BY Route DESC ";
return(DAO.GetDataTable(sql));
}
//Get bus detail by busID
public static DataTable GetBusDetail(string id)
{
string sql = "SELECT b.BusID,b.BusName,bd.BusDetailID," +
" ((SELECT CityName FROM CITIES WHERE r.FromCityID = CityID) + ' - ' + (SELECT CityName FROM CITIES WHERE r.ToCityID = CityID)) as Route," +
" bd.DepartureDate,bd.DepartureTime,bd.AvailableSeat, b.TotalSeat" +
" FROM BUSES b, BUSDETAIL bd, ROUTES r" +
" WHERE b.BusID = bd.BusID" +
" AND r.RouteID = b.RouteID" +
" AND b.BusID = \'" + id + "\'";
return(DAO.GetDataTable(sql));
}
public static DataTable GetBusByName(string name)
{
return(DAO.GetDataTable("SELECT * FROM BUSES WHERE BusName = N\'" + name + "\'"));
}
/// <summary>
/// Get all buses
/// </summary>
/// <returns></returns>
public static DataTable GetBus()
{
return(DAO.GetDataTable("SELECT * FROM BUSES ORDER BY BusName"));
}
public static DataTable GetDataCityByName(string name)
{
return(DAO.GetDataTable("SELECT * FROM Cities WHERE CityName = N\'" + name + "\' ORDER BY CityID"));
}
/// <summary>
/// Get cities
/// </summary>
/// <returns></returns>
public static DataTable GetDataCity()
{
return(DAO.GetDataTable("SELECT * FROM Cities ORDER BY CityID"));
}
public static DataTable GetDataUsersByEmail(string email)
{
return(DAO.GetDataTable("SELECT * FROM Users WHERE Email LIKE '%" + email + "%'"));
}
/// <summary>
/// Get all users
/// </summary>
/// <returns>user datatable</returns>
public static DataTable GetDataUsers()
{
return(DAO.GetDataTable("SELECT * FROM Users"));
}