func ReturnListings(db *sql.DB, o int, d int, t string) ([]Listing, error) {
results := make([]Listing, 0)
// Always prepare queries to be used multiple times. The parameter placehold is ?
stmt, err := db.Prepare(`
SELECT l.id, u.id, u.name, u.custom_picture, (u.positive_ratings - u.negative_ratings), l.date_leaving, c.name, c2.name, l.seats, l.fee
FROM listings AS l
JOIN users AS u ON l.driver = u.id
JOIN cities AS c ON l.origin = c.id
LEFT JOIN cities AS c2 ON l.destination = c2.id
WHERE l.origin = ?
AND l.destination = ?
AND l.date_leaving >= ?
AND l.seats > 0
AND l.date_leaving > NOW()
ORDER BY l.date_leaving
LIMIT 50
`)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
defer stmt.Close()
// db.Query() prepares, executes, and closes a prepared statement - three round
// trips to the databse. Call it infrequently as possible; use efficient SQL statments
rows, err := stmt.Query(o, d, t)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
// Always defer rows.Close(), even if you explicitly Close it at the end of the
// loop. The connection will have the chance to remain open otherwise.
defer rows.Close()
// The last rows.Next() call will encounter an EOF error and call rows.Close()
for rows.Next() {
var temp Listing
customPicture := false
name := ""
err := rows.Scan(&temp.Id, &temp.Driver, &name, &customPicture, &temp.Rating, &temp.Timestamp, &temp.Origin, &temp.Destination, &temp.Seats, &temp.Fee)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
prettyTime, err := util.PrettyDate(temp.Timestamp, false)
if err != nil {
return results, err
}
temp.Date = prettyTime.Month + " " + prettyTime.Day
temp.Time = prettyTime.Time
if customPicture {
temp.Picture = "https://5sur.com/images/" + name + "_50.png"
} else {
temp.Picture = "https://5sur.com/default_50.png"
}
results = append(results, temp)
}
return results, nil
}
func ReturnAllListings(db *sql.DB) ([]Listing, error) {
results := make([]Listing, 0)
// Always prepare queries to be used multiple times. The parameter placehold is ?
stmt, err := db.Prepare(`
SELECT l.id, u.id, u.name, u.custom_picture, (u.positive_ratings - u.negative_ratings), l.date_leaving, c.name, c2.name, l.seats, l.fee
FROM listings AS l
JOIN users AS u ON l.driver = u.id
JOIN cities AS c ON l.origin = c.id
LEFT JOIN cities AS c2 ON l.destination = c2.id
WHERE l.seats > 0
AND l.date_leaving > NOW()
ORDER BY l.date_leaving
LIMIT 50
`)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
defer stmt.Close()
rows, err := stmt.Query()
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
defer rows.Close()
for rows.Next() {
var temp Listing
customPicture := false
name := ""
err := rows.Scan(&temp.Id, &temp.Driver, &name, &customPicture, &temp.Rating, &temp.Timestamp, &temp.Origin, &temp.Destination, &temp.Seats, &temp.Fee)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
prettyTime, err := util.PrettyDate(temp.Timestamp, false)
if err != nil {
return results, err
}
temp.Date = prettyTime.Month + " " + prettyTime.Day
temp.Time = prettyTime.Time
if customPicture {
temp.Picture = "https://5sur.com/images/" + name + "_50.png"
} else {
temp.Picture = "https://5sur.com/default_50.png"
}
results = append(results, temp)
}
return results, nil
}
func GetDashListings(db *sql.DB, userId int) ([]DashListing, error) {
results := make([]DashListing, 0)
// Always prepare queries to be used multiple times. The parameter placehold is ?
stmt, err := db.Prepare(`
SELECT l.date_leaving, c.name, c2.name, l.id, l.seats, l.fee
FROM listings AS l
JOIN cities as c ON l.origin = c.id
LEFT JOIN cities as c2 ON l.destination = c2.id
WHERE l.driver = ?
ORDER BY l.date_leaving;
`)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
defer stmt.Close()
rows, err := stmt.Query(userId)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
// The last rows.Next() call will encounter an EOF error and call rows.Close()
for rows.Next() {
date := ""
var temp DashListing
err := rows.Scan(&date, &temp.Origin, &temp.Destination, &temp.ListingId, &temp.Seats, &temp.Fee)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
convertedDate, err := util.PrettyDate(date, false)
if err != nil {
return results, err
}
temp.Day = convertedDate.Day
temp.Month = convertedDate.Month
temp.Time = convertedDate.Time
temp.Alert, err = CheckReservationQueue(db, temp.ListingId)
if err != nil {
return results, err
}
// Also find if there are any new messages.
results = append(results, temp)
}
return results, nil
}
func GetDashReservations(db *sql.DB, userId int) ([]DashReservation, error) {
results := make([]DashReservation, 0)
stmt, err := db.Prepare(`
SELECT l.id, l.date_leaving, c.name, c2.name, l.seats, l.fee
FROM listings AS l
JOIN cities as c ON l.origin = c.id
LEFT JOIN cities as c2 ON l.destination = c2.id
JOIN reservations as r ON l.id = r.listing_id
WHERE r.passenger_id = ?
ORDER BY l.date_leaving;
`)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
defer stmt.Close()
rows, err := stmt.Query(userId)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
// The last rows.Next() call will encounter an EOF error and call rows.Close()
for rows.Next() {
temp := DashReservation{}
date := ""
err := rows.Scan(&temp.ListingId, &date, &temp.Origin, &temp.Destination, &temp.Seats, &temp.Fee)
if err != nil {
return results, util.NewError(err, "Error de la base de datos", 500)
}
convertedDate, err := util.PrettyDate(date, false)
if err != nil {
return results, err
}
temp.Day = convertedDate.Day
temp.Month = convertedDate.Month
temp.Time = convertedDate.Time
results = append(results, temp)
}
return results, nil
}
func ReturnIndividualListing(db *sql.DB, id int) (Listing, error) {
result := Listing{}
stmt, err := db.Prepare(`
SELECT l.id, u.id, u.name, u.custom_picture, (u.positive_ratings - u.negative_ratings), l.date_leaving, c.name, c2.name, l.seats, l.fee
FROM listings AS l
JOIN users AS u ON l.driver = u.id
JOIN cities AS c ON l.origin = c.id
LEFT JOIN cities AS c2 ON l.destination = c2.id
WHERE l.id = ?
`)
if err != nil {
return Listing{}, util.NewError(err, "Error de la base de datos", 500)
}
defer stmt.Close()
customPicture := false
name := ""
err = stmt.QueryRow(id).Scan(&result.Id, &result.Driver, &name, &customPicture, &result.Rating, &result.Timestamp, &result.Origin, &result.Destination, &result.Seats, &result.Fee)
if err != nil {
return Listing{}, util.NewError(nil, "Viaje no existe", 400)
}
prettyTime, err := util.PrettyDate(result.Timestamp, false)
if err != nil {
return result, err
}
result.Date = prettyTime.Month + " " + prettyTime.Day
result.Time = prettyTime.Time
if customPicture {
result.Picture = "https://5sur.com/images/" + name + "_50.png"
} else {
result.Picture = "https://5sur.com/default_50.png"
}
return result, nil
}
