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 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);
}
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 function postBuyerFeedback(Request $request, Session $session, $auction_id)
{
if (!$session->userIsLoggedIn()) {
return $this->redirectTo('/login');
}
$auction = Auction::getAuctionWithId($auction_id);
Database::insert('INSERT INTO BuyerFeedback (content, speed_of_payment, communication, auction_id) VALUES (?,?,?,?)', [$request->post['feedback_comment'], $request->post['speed_of_payment'], $request->post['communication'], $auction->id]);
return $this->redirectTo('/dashboard/?message=' . urlencode('Feedback saved!'));
}
public function generateSession($user_id)
{
$auth_key = hash('sha512', (string) rand());
while (Database::checkExists($auth_key, 'token', 'Session')) {
$auth_key = hash('sha512', (string) rand());
}
Database::insert('INSERT INTO Session (user_id,token) VALUES(?,?)', [$user_id, $auth_key]);
$_SESSION['auth_token'] = $auth_key;
}
public function saveInput(array $input)
{
$errors = $this->validateInput($input);
if (count($errors)) {
fatalError($errors);
}
$parameters = [$input['auction_name'], $input['auction_description'], $input['starting_price'] * 100, $input['end_date_time'], $input['userrole_id'], $input['reserve_price'] * 100];
Database::insert('INSERT INTO Auction (name,description,starting_price,end_date,userrole_id, reserve_price) VALUES (?,?,?,?,?,?)', $parameters);
$auction_id = Database::lastID();
return $auction_id;
}
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 static function scanForItemWonNotifications()
{
$query = 'SELECT au.*, aumb.max_bid FROM Auction AS au JOIN AuctionsMaxBid AS aumb ON aumb.auction_id = au.id
WHERE au.end_date < NOW() AND au.id NOT IN
(SELECT auction_id FROM Notification WHERE type = ?)';
$results = Database::select($query, [NotificationType::itemWon()]);
if (count($results)) {
$auctions = Auction::arrayFromSQLRows($results);
foreach ($auctions as $auction) {
self::sendItemWonNotification($auction);
}
}
}
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;
}
public function saveInput(array $input)
{
$errors = $this->validateInput($input);
if (count($errors)) {
fatalError('Tried to save invalid item data');
}
if (!isset($input['auction_id'])) {
fatalError('Auction Id Was Not Set on Item (System Error)');
}
$name = $input['item_name'];
$description = $input['item_description'];
$image = $this->saveImage($input['image'], '/items');
$auction_id = $input['auction_id'];
$categories = $input['item_category'];
Database::insert('INSERT INTO Item (name, description,image_url,auction_id) VALUES (?,?,?,?)', [$name, $description, $image, $auction_id]);
$item_id = Database::lastID();
foreach ($categories as $category) {
Database::insert('INSERT INTO ItemCategory (item_id, category_id) VALUES (?,?)', [$item_id, $category]);
}
return $item_id;
}
public function processSignup(Request $request, Session $session)
{
if ($session->userIsLoggedIn()) {
return $this->redirectTo('/dashboard');
}
if (isset($request->post['email']) && isset($request->post['password']) && (isset($request->post['buyer_account']) || isset($request->post['seller_account'])) && ($request->post['buyer_account'] == 1 || $request->post['seller_account'] == 1)) {
if (Database::checkExists($request->post['email'], 'email', 'User')) {
return View::renderView('login', ['signup_errors' => 'Email already exists']);
}
Database::insert('INSERT INTO User (email,password) VALUES (?,?)', [$request->post['email'], password_hash($request->post['password'], PASSWORD_DEFAULT)]);
$user_id = Database::lastID();
if ($request->post['buyer_account'] == 1) {
Database::insert('INSERT INTO UserRole (user_id, role_id) VALUES (?,?)', [$user_id, Role::buyer()]);
}
if ($request->post['seller_account'] == 1) {
Database::insert('INSERT INTO UserRole (user_id, role_id) VALUES (?,?)', [$user_id, Role::seller()]);
}
$session->generateSession($user_id);
return $this->redirectTo('/dashboard');
}
return View::renderView('login', ['signup_errors' => 'You must complete the signup form']);
}
public static function clearForUser($user_id)
{
Database::insert('UPDATE Notification SET cleared = 1 WHERE user_id = ?', [$user_id]);
}
private function getAuctionData($id)
{
$result = Database::query('SELECT * FROM Auction WHERE id = ?', [$id]);
return $result;
}
public function incrementViewsNumber($user)
{
$auction_id = $this->id;
if (!is_null($user)) {
if (!is_null($user->buyerID())) {
$userrole_id = $user->buyerID();
} else {
$userrole_id = $user->sellerID();
}
} else {
$userrole_id = -1;
}
$query = "INSERT INTO View (userrole_id, auction_id) VALUES (?,?);";
Database::insert($query, [$userrole_id, $auction_id]);
}
public function getBuyerWatchCount()
{
$result = Database::query("SELECT COUNT(*) AS count FROM Watch WHERE userrole_id = ?", [$this->buyerID()]);
return (int) $result[0]['count'];
}
public function runTest(Request $request, Session $session)
{
$connect_test = Database::testConnection();
return $connect_test ? "Database connection test successful" : "Database connection test failed";
}
public static function categoriesForItem($item_id)
{
$results = Database::select('SELECT * FROM Category WHERE id IN (SELECT category_id FROM ItemCategory WHERE item_id = ?)', [$item_id]);
return self::arrayFromSQLRows($results);
}
