public static function getWonItemsForUser($userrole_id)
{
$results = Database::query('SELECT Item.*
FROM AuctionsWinners JOIN Item ON AuctionsWinners.id = Item.auction_id
WHERE userrole_id_winner = ?', [$userrole_id]);
return self::fromSQLRows($results);
}
private function getExactAuctionDataSearch($searchTerms, $request)
{
$query = "SELECT DISTINCT a.name, a.description, a.end_date, a.id, greatest(a.starting_price, IFNULL(m.max_bid + 1, 0)) as max_value, i.image_url, a.starting_price, a.userrole_id, a.created_at, a.updated_at FROM `Auction` a LEFT JOIN `Item` i ON a.id = i.auction_id LEFT JOIN `AuctionsMaxBid` m ON a.id = m.auction_id WHERE a.id IN (";
$relevanceAlias = $this->createRelevanceAlias($searchTerms);
$searchQuery = " SELECT DISTINCT tb.id FROM `Auction` a JOIN (";
$searchQuery = $searchQuery . "SELECT a.id, " . $relevanceAlias . " FROM `Auction` a";
$this->createLeftJoins($searchQuery, $searchTerms);
$searchQuery = $searchQuery . " HAVING relevance = (";
$searchQuery = $searchQuery . "SELECT " . $relevanceAlias . " FROM `Auction` a";
$this->createLeftJoins($searchQuery, $searchTerms);
$searchQuery = $searchQuery . " ORDER BY relevance DESC LIMIT 1";
$searchQuery = $searchQuery . ") AND relevance > 0";
$searchQuery = $searchQuery . ") tb";
$query = $query . $searchQuery . ")";
$selected_categories = $request->get;
$firstOr = true;
foreach ($selected_categories as $key => $category) {
if ($key == "search-bar" || $key == "date" || $key == "price") {
continue;
}
$query = $query . " AND a.id IN(SELECT a.id FROM `Item` it LEFT JOIN `ItemCategory` i ON it.id = i.item_id LEFT JOIN `Category` c ON i.category_id = c.id LEFT JOIN `Auction` a ON a.id\n= it.auction_id WHERE c.name=\"" . $category . "\" GROUP BY a.id HAVING COUNT(a.id) >= 1) ";
}
$query = $query . " GROUP BY a.name, a.id";
if (array_key_exists("date", $selected_categories)) {
$query = $selected_categories["date"] == "1" ? $query . " ORDER BY a.end_date" : $query . " ORDER BY a.end_date DESC";
}
if (array_key_exists("price", $selected_categories)) {
$query = $selected_categories["price"] == "1" ? $query . " ORDER BY max_value" : $query . " ORDER BY max_value DESC";
}
$result = Database::query($query);
return $result;
}
public static function forAuction($auction_id)
{
$results = Database::query('SELECT b.*, u.email, u.id AS user_id FROM Bid AS b
JOIN UserRole AS ur ON b.userrole_id = ur.id
JOIN User AS u ON ur.user_id = u.id
WHERE auction_id = ? ORDER BY b.created_at DESC', [$auction_id]);
return self::arrayFromSQLRows($results);
}
public static function getMeanRatingForUser($userrole_id)
{
//unprocessed results
$results = Database::query('SELECT avg(communication) as mean_communication,
avg(speed_of_payment) as mean_speed_of_payment,
count(*) as no_feedback
FROM BuyerFeedback WHERE auction_id IN
(SELECT id FROM AuctionsWinners WHERE userrole_id_winner = ?)', [$userrole_id]);
$mean_rating['mean_communication'] = isset($results[0]['mean_communication']) ? $results[0]['mean_communication'] : 0;
$mean_rating['mean_speed_of_payment'] = isset($results[0]['mean_speed_of_payment']) ? $results[0]['mean_speed_of_payment'] : 0;
return $mean_rating;
}
public function processLoginAttempt(Request $request, Session $session)
{
if ($session->userIsLoggedIn()) {
return $this->redirectTo('/dashboard');
}
if (!isset($request->post['email']) || !isset($request->post['password'])) {
return (new View('login', ['errors' => 'Form incomplete']))->render();
}
$results = Database::query('SELECT id, password FROM User WHERE email = ?', [$request->post['email']]);
if (isset($results[0]['id']) && password_verify($request->post['password'], $results[0]['password'])) {
$id = (int) $results[0]['id'];
$session->generateSession($id);
return $this->redirectTo('/dashboard');
}
return (new View('login', ['errors' => 'Login Incorrect']))->render();
}
public static function getMeanRatingForUser($userrole_id)
{
//unproccesed result
$results = Database::query('SELECT
avg(item_as_described) as mean_item_as_described,
avg(communication) as mean_communication,
avg(dispatch_time) as mean_dispatch_time,
avg(posting) as mean_posting,
count(*) as no_feedback
FROM SellerFeedback JOIN Auction ON SellerFeedback.auction_id = Auction.id
WHERE Auction.userrole_id = ?
GROUP BY Auction.userrole_id', [$userrole_id]);
$mean_rating['mean_item_as_described'] = isset($results[0]['mean_item_as_described']) ? $results[0]['mean_item_as_described'] : 0;
$mean_rating['mean_communication'] = isset($results[0]['mean_communication']) ? $results[0]['mean_communication'] : 0;
$mean_rating['mean_dispatch_time'] = isset($results[0]['mean_dispatch_time']) ? $results[0]['mean_dispatch_time'] : 0;
$mean_rating['mean_posting'] = isset($results[0]['mean_posting']) ? $results[0]['mean_posting'] : 0;
return $mean_rating;
}
private function getAuctionData($id)
{
$result = Database::query('SELECT * FROM Auction WHERE id = ?', [$id]);
return $result;
}
private function loadActiveUserFromSession()
{
$user_query = Database::query("SELECT id, email FROM User WHERE id IN(SELECT user_id FROM Session WHERE token = ?)", [$this->session_array['auth_token']]);
return $this->loadActiveUser($user_query);
}
public function getBuyerWatchCount()
{
$result = Database::query("SELECT COUNT(*) AS count FROM Watch WHERE userrole_id = ?", [$this->buyerID()]);
return (int) $result[0]['count'];
}
public static function getRecommendationsForUser($buyer_id, $seller_id = 0)
{
/*
recommend auctions that users who bid on the same auctions as me bid on
*/
$results = Database::query('SELECT Auction.* FROM Auction WHERE Auction.id IN
(SELECT Bid.auction_id FROM Bid WHERE Bid.userrole_id IN
(SELECT Bid.userrole_id FROM Bid WHERE Bid.auction_id IN
(SELECT Bid.auction_id FROM Bid WHERE Bid.userrole_id = ?)))
AND Auction.end_date > now() AND NOT Auction.userrole_id = ?
AND NOT EXISTS (SELECT * FROM Bid WHERE Bid.auction_id = Auction.id AND userrole_id = ?)', [$buyer_id, $seller_id, $buyer_id]);
if (count($results) == 0) {
/*
if on the auctions the user bid he was the only bidder
give suggestions from top categories he bought from
*/
$results = Database::query('SELECT DISTINCT Auction.* FROM Auction JOIN Item ON Auction.id = Item.auction_id
WHERE Item.id IN (SELECT DISTINCT(ItemCategory.item_id) FROM ItemCategory JOIN
(SELECT ItemCategory.category_id, COUNT(ItemCategory.item_id) as no_items FROM ItemCategory JOIN
(SELECT Item.id FROM Bid JOIN Item ON Bid.auction_id = Item.auction_id
WHERE Bid.userrole_id = ?) AS BidItems
ON ItemCategory.item_id = BidItems.id
GROUP BY ItemCategory.category_id
ORDER BY no_items
LIMIT 1) as TopCategories
ON ItemCategory.category_id = TopCategories.category_id)
AND Auction.end_date > now()
AND NOT Auction.userrole_id = ?
AND NOT EXISTS (SELECT * FROM Bid WHERE Bid.auction_id = Auction.id AND userrole_id = ?)', [$buyer_id, $seller_id, $buyer_id]);
}
if (count($results) == 0) {
/*
if the user did not bid on any auction, recommend from popular categories
*/
$results = Database::query('SELECT DISTINCT Auction.* FROM Auction JOIN Item ON Auction.id = Item.auction_id
WHERE Item.id IN (SELECT DISTINCT(ItemCategory.item_id) FROM ItemCategory JOIN
(SELECT ItemCategory.category_id, COUNT(ItemCategory.item_id) as no_items FROM ItemCategory
GROUP BY ItemCategory.category_id
ORDER BY no_items
LIMIT 1) as TopCategories
ON ItemCategory.category_id = TopCategories.category_id)
AND Auction.end_date > NOW()
AND NOT Auction.userrole_id = ?
AND NOT EXISTS (SELECT * FROM Bid WHERE Bid.auction_id = Auction.id AND userrole_id = ?)', [$seller_id, $buyer_id]);
}
return self::processAuctionsResultSetSql($results);
}
