<?php
$time_start = microtime(True);
$users = array();
$searchresults = array();
$modmode = False;
if(array_key_exists('modkey', $_REQUEST) && $_REQUEST['modkey'] == 'raoamodsquad')
	$modmode = True;

$curr_gbp = 0.64;
$curr_eur = 0.76;
$curr_cad = 0.99;
$curr_jpy = 97.21;
$curr_cny = 6.17;
$curr_brl = 2.00;

$currency = array('amazon.com' => '1.00', 'amazon.co.uk' => $curr_gbp, 'amazon.ca' => $curr_cad, 'amazon.de' => $curr_eur, 'amazon.it' => $curr_eur, 'amazon.fr' => $curr_eur, 'amazon.es' => $curr_eur, 'amazon.com.br' => $curr_brl, 'amazon.cn' => $curr_cny, 'amazon.co.jp' => $curr_jpy);

$domains = array('us' => 'amazon.com', 'uk' => 'amazon.co.uk', 'ca' => 'amazon.ca', 'de' => 'amazon.de', 'it' => 'amazon.it', 'fr' => 'amazon.fr', 'es' => 'amazon.es', 'br' => 'amazon.com.br', 'cn' => 'amazon.cn', 'jp' => 'amazon.co.jp');
$domains_flipped = array_flip($domains);

$reddits = array('raoa' => 'RandomActsOfAmazon', 'raop' => 'RandomActsOfPolish', 'raowl' => 'RAOWL', 'raom' => 'RandomActsofMakeup', 'raoc' => 'RandomActsofCrafting', 'raowd' => 'RAoWarmDrinks');
if(array_key_exists('reddit', $_REQUEST) && array_key_exists(trim($_REQUEST['reddit']), $reddits))
	$reddit = trim($_REQUEST['reddit']);
else
	$reddit = 'raoa';
$dbhandle=mysqli_connect("localhost", "n8fq_wldb", "nq#q3%nm*hZz") or die ('I cannot connect to the database because: ' . mysql_error());
mysqli_select_db($dbhandle, 'n8fq_wldb');

if(array_key_exists('search_showhits', $_REQUEST) or array_key_exists('search_lucky', $_REQUEST)) {
	if(strlen($_REQUEST['search_namecity']) > 0 && $modmode) {
		$hits = array();
		$query = "SELECT `RegID` FROM `lists` WHERE `NameCity` LIKE '%".mysqli_real_escape_string($dbhandle,$_REQUEST['search_namecity'])."%'";
		$result = mysqli_query($dbhandle, $query);
		if($result) {
			while($hit = mysqli_fetch_assoc($result)) {
				$query = "SELECT * FROM `items` WHERE `RegID` = '".$hit['RegID']."' LIMIT 1";
				$hitresult = mysqli_query($dbhandle, $query);
				if($hitresult) {
					$hits[] = mysqli_fetch_assoc($hitresult);
					mysqli_free_result($hitresult);
				}
			}
		mysqli_free_result($result);
		}
	} else {
		$where = "";
		if(strlen($_REQUEST['search_username']) > 0)
			$where = $where." AND `Username` LIKE '%".mysqli_real_escape_string($dbhandle, $_REQUEST['search_username'])."%'";
		if(strlen($_REQUEST['search_keywords']) > 0) {
			mysqli_query($dbhandle, "INSERT INTO `searches` (`Search`,`Timestamp`) VALUES ('".mysqli_real_escape_string($dbhandle,$_REQUEST['search_keywords'])."',".time().")");
			$keywords = explode(" ", $_REQUEST['search_keywords']);
			$kwclauses = "";
			foreach($keywords as $keyword) {
				if($keyword[0] == "-")
					$kwclauses = $kwclauses." AND `ItemTitle` NOT LIKE '%".mysqli_real_escape_string($dbhandle, substr($keyword,1))."%'";
				else
					$kwclauses = $kwclauses." AND `ItemTitle` LIKE '%".mysqli_real_escape_string($dbhandle, $keyword)."%'";
			}
			$where = $where." AND (".substr($kwclauses,5).")";
		}

		$domainclauses = "";
		foreach($domains as $tag => $domain) {
			if(array_key_exists("search_domain_".$tag, $_REQUEST))
				$domainclauses = $domainclauses." OR `Domain` = '".$domain."'";
		}

		if(strlen($domainclauses) > 0)
			$domainclauses = " AND (".substr($domainclauses, 4).")";

		$where = $where.$domainclauses;

		if($where != "")
			$where = " WHERE ".substr($where,5);
		$query = "SELECT * FROM `items`".$where." ORDER BY rand() LIMIT 5000";
		$result = mysqli_query($dbhandle, $query);
		$hits = array();
		while ($hit = mysqli_fetch_assoc($result))
			$hits[] = $hit;
		mysqli_free_result($result);
	}

	foreach($hits as $hit) {
		if(array_key_exists('search_gifted', $_REQUEST) or abs($_REQUEST['search_days']) > 2) {
			$timeout = time() - (abs($_REQUEST['search_days']) * 86400);
			if($timeout < 0 or $timeout > time() - 259210)
				$timeout = time() - 604800;
			if(array_key_exists('search_gifted', $_REQUEST))
				$where = "WHERE `Gifted` > 0 AND `Last` > ".$timeout;
			else
				$where = "WHERE `Last` > ".$timeout;
			$query = "SELECT `User` FROM `f_".$reddit."` ".$where." AND `User` = '".mysqli_real_escape_string($dbhandle, $hit['Username'])."'";
			$result = mysqli_query($dbhandle, $query);
			if(mysqli_num_rows($result) == 0) {
				mysqli_free_result($result);
				continue;
			}
			mysqli_free_result($result);
		}
		$where = "";
		if(array_key_exists('search_requireaddress', $_REQUEST))
			$where = $where." AND `NameCity` != 'No address entered'";
		if(!array_key_exists('search_nsfw', $_REQUEST))
			$where = $where." AND `RegTitle` NOT LIKE '%nsfw%'";
		$query = "SELECT * FROM `lists` WHERE `RegID` = '".mysqli_real_escape_string($dbhandle, $hit['RegID'])."'".$where;
		$result = mysqli_query($dbhandle, $query);
		if($result) {
			$wl = mysqli_fetch_assoc($result);
			mysqli_free_result($result);
			if($currency[$wl['Domain']] == 0)
				continue;
			$price = round($hit['ItemPrice'] / $currency[$wl['Domain']], 2);
			if($price < abs($_REQUEST['search_price_high']) && $price > abs($_REQUEST['search_price_low'])) {
				$searchresults[] = array(
					'User' => $hit['Username'],
					'Domain' => $wl['Domain'],
					'Link' => $hit['ItemLink'],
					'Title' => $hit['ItemTitle'],
					'Price' => $price,
					'ListLink' => "http://".$wl['Domain']."/registry/wishlist/".$wl['RegID']."?sort=universal-price",
					'ListName' => $wl['RegTitle']
				);
			}
			if(count($searchresults) == 100)
				break;
		}
	}
/*
if(array_key_exists('search_lucky', $_REQUEST) && count($searchresults) > 0)
	header("Refresh: 1; URL=".$searchresults[0]['Link']);
*/
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Wishlist Database Search</title>
<style type="text/css">
body { background-color: #ffffff; font-family: sans-serif; }
div.title { font-size: x-large; font-weight: bold; text-align: center; margin-left: auto; margin-right: auto; }
div.form { padding: 1em; margin-left: auto; margin-right: auto; width: 40em; border: ridge 3px #c0c0c0; }
div.vanity { font-size: x-small; text-decoration: italic; }
div.results { margin-left: auto; margin-right: auto; margin-bottom: 2em; width: 80%; }
table.results { width: 100%; }
table.domainchecks { width: 100%; }
</style>
</head>
<body>
<div class="title">Wishlist Database Search</div>
<div style="text-align: center;"><a href="http://www.reddit.com/r/randomactsofamazon/wiki/faq#wiki_how_do_i_use_the_wishlist_search.3F">Instructions</a></div>
<div class="form">
<form action="wishlistquery2.php" method="post">
<?php
if(array_key_exists('search_price_low', $_REQUEST)) { $prefillpricelow = abs($_REQUEST['search_price_low']); } else { $prefillpricelow = 0.16; }
if(array_key_exists('search_price_high', $_REQUEST)) { $prefillpricehigh = abs($_REQUEST['search_price_high']); } else { $prefillpricehigh = 10.00; }
if(array_key_exists('search_days', $_REQUEST)) { $prefilldays = abs($_REQUEST['search_days']); } else { $prefilldays = 15; }
printf("<input type=\"hidden\" name=\"reddit\" value=\"%s\">\n", $reddit);
?>
Username: <input type="text" name="search_username" value="<?php echo $_REQUEST['search_username']; ?>"><p>
Keywords: <input type="text" name="search_keywords" value="<?php echo $_REQUEST['search_keywords']; ?>"><p>
Price (USD): <input type="text" size="6" name="search_price_low" value="<?php echo $prefillpricelow; ?>"> to <input type="text" size="6" name="search_price_high" value="<?php echo $prefillpricehigh; ?>"><p>
<?php if($modmode) { ?>Real Name/City: <input type="text" name="search_namecity" value="<?php echo $_REQUEST['search_namecity']; ?>"><input type="hidden" name="modkey" value="raoamodsquad"><p><?php } ?>
Include users active in last <input type="text" size="2" name="search_days" value="<?php echo $prefilldays; ?>"> days (min 3, max 30)<p>
<input type="checkbox" name="search_requireaddress" <?php if(array_key_exists('search_requireaddress', $_REQUEST)) { echo "checked"; } ?>> Include only lists with a shipping address<p>
<input type="checkbox" name="search_gifted" <?php if(array_key_exists('search_gifted', $_REQUEST)) { echo "checked"; } ?>> Include only users who have gifted<p>
<input type="checkbox" name="search_nsfw" <?php if(array_key_exists('search_nsfw', $_REQUEST)) { echo "checked"; } ?>> Include NSFW Lists<p>
Include only lists from:
<table class="domainchecks">
<tr>
<?php
$i = 0;
foreach($domains as $tag => $domain) {
	if(array_key_exists("search_domain_".$tag, $_REQUEST))
		$checked = " checked";
	else
		$checked = "";
	printf("<td><input type=\"checkbox\" name=\"search_domain_%s\"%s> %s</td>\n", $tag, $checked, $domain);
	$i++;
	if($i == 4) {
		$i = 0;
		echo "</tr>\n<tr>\n";
	}
}
?>
</tr>
</table>
<hr><p>
<input type="submit" name="search_showhits" value="Show me all results"> or <input type="submit" name="search_lucky" value="Take me to a random result">
</form>
</div>
<?php
if(count($searchresults) > 0) {
        echo "<div class=\"results\">".count($searchresults)." Results:<p>\n";
        echo "<table class=\"results\" border=\"2px\">";
        echo "<tr class=\"resultheader\"><td>User</td><td>List</td><td>Site</td><td>Item</td><td>Price</td></tr>\n";
        foreach($searchresults as $row) {
                printf("<tr><td><a href=\"http://www.reddit.com/u/%s\">%s</a></td><td><a href=\"%s\">%s</a></td><td>%s</td><td><a href=\"%s\">%s</a></td><td>$%s</td></tr>\n",
                        $row['User'],
                        $row['User'],
			$row['ListLink'],
			$row['ListName'],
                        strtoupper($domains_flipped[$row['Domain']]),
                        $row['Link'],
                        $row['Title'],
                        $row['Price']
                );
        }
        echo "</table>";
        echo "</div>\n";
}
elseif(array_key_exists('search_showhits', $_REQUEST) or array_key_exists('search_lucky', $_REQUEST))
        echo "<div class=\"results\">No results found!</div>\n\n";
?>
<div class="vanity">
Processing took <?php echo round(microtime(True) - $time_start, 4); ?> seconds.<br>
Developed and hosted by <a href="http://www.reddit.com/u/DersEvvak">/u/DersEvvak</a>
</div>
</body>
</html>