function retrieveAdEvent()
{
$eventCode = $_POST['eventCode'];
$name = $_POST['name'];
$startDateUnformatted = $_POST['startDate'];
$endDateUnformatted = $_POST['endDate'];
$description = $_POST['description'];
$adType = $_POST['adType'];
$temp = "";
$startDates = explode("/", $startDateUnformatted);
//[10], [28], [2015]
$startDatesReversed = array_reverse($startDates);
//[2015], [28], [10]
$temp = $startDatesReversed[1];
//[2015], [28], [10] t:[28]
$startDatesReversed[1] = $startDatesReversed[2];
//[2015], [10], [10] t:[28]
$startDatesReversed[2] = $temp;
//[2015], [10], [28]
$startDate = implode("-", $startDatesReversed);
//2015-10-28
$endDates = explode("/", $endDateUnformatted);
$endDatesReversed = array_reverse($endDates);
$temp = $endDatesReversed[1];
$endDatesReversed[1] = $endDatesReversed[2];
$endDatesReversed[2] = $temp;
$endDate = implode("-", $endDatesReversed);
$cond1 = "";
$cond2 = "";
$cond3 = "";
$cond4 = "";
$cond5 = "";
$cond6 = "";
$whereCondition = "";
if (isset($eventCode) && $eventCode != "") {
$cond1 = "EventCode = '" . $eventCode . "'";
}
if (isset($name) && $name != "") {
$cond2 = "Name = '" . $name . "'";
}
if (isset($startDate) && $startDate != "--") {
$cond3 = "StartDate = '" . $startDate . "'";
}
if (isset($endDate) && $endDate != "--") {
$cond4 = "EndDate = '" . $endDate . "'";
}
if (isset($description) && $description != "") {
$cond5 = "Description = '" . $description . "'";
}
if (isset($adType) && $adType != "---") {
$cond6 = "AdType = '" . $adType . "'";
}
if ($cond1 != "") {
$whereCondition = $whereCondition . $cond1;
}
if ($cond2 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond2;
} else {
$whereCondition = $whereCondition . $cond2;
}
}
if ($cond3 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond3;
} else {
$whereCondition = $whereCondition . $cond3;
}
}
if ($cond4 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond4;
} else {
$whereCondition = $whereCondition . $cond4;
}
}
if ($cond5 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond5;
} else {
$whereCondition = $whereCondition . $cond5;
}
}
if ($cond6 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond6;
} else {
$whereCondition = $whereCondition . $cond6;
}
}
//echo "$whereCondition";
$insertStatement = "SELECT EventCode, Name, StartDate, EndDate, \n\tDescription, AdType FROM AdEvent WHERE {$whereCondition}";
//echo "$insertStatement";
/*
echo "$cond1";
echo "$cond2";
echo "$cond3";
echo "$cond4";
echo "$cond5";
*/
//Construct SQL statements
//Execute the queries
$result = mysql_query($insertStatement);
$numberAdEventRows = mysql_num_rows($result);
//Test whether the queries were successful
if (!$result || $numberAdEventRows == 0) {
$message = "The retrieval of Ad Events was unsuccessful";
}
//Display the results
displayItemsPromotions($message, $result);
//Free the result sets
mysql_free_result($result);
}
function searchItemsByCategory()
{
$itemNumber = $_POST['itemNumber'];
$itemDescription = $_POST['itemDescription'];
$category = $_POST['category'];
$departmentName = $_POST['departmentName'];
$purchaseCost = $_POST['purchaseCost'];
$fullRetailPrice = $_POST['fullRetailPrice'];
$cond1 = "";
$cond2 = "";
$cond3 = "";
$cond4 = "";
$cond5 = "";
$cond6 = "";
$whereCondition = "";
if (isset($itemNumber) && $itemNumber != "") {
$cond1 = "ItemNumber = '" . $itemNumber . "'";
}
if (isset($itemDescription) && $itemDescription != "") {
$cond2 = "ItemDescription LIKE '%" . $itemDescription . "%'";
}
if (isset($category) && $category != "---") {
$cond3 = "Category = '" . $category . "'";
}
if (isset($departmentName) && $departmentName != "---") {
$cond4 = "DepartmentName = '" . $departmentName . "'";
}
if (isset($purchaseCost) && $purchaseCost != "") {
$cond5 = "PurchaseCost = '" . $purchaseCost . "'";
}
if (isset($fullRetailPrice) && $fullRetailPrice != "") {
$cond6 = "FullRetailPrice = '" . $fullRetailPrice . "'";
}
if ($cond1 != "") {
$whereCondition = $whereCondition . $cond1;
}
if ($cond2 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond2;
} else {
$whereCondition = $whereCondition . $cond2;
}
}
if ($cond3 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond3;
} else {
$whereCondition = $whereCondition . $cond3;
}
}
if ($cond4 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond4;
} else {
$whereCondition = $whereCondition . $cond4;
}
}
if ($cond5 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond5;
} else {
$whereCondition = $whereCondition . $cond5;
}
}
if ($cond6 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond6;
} else {
$whereCondition = $whereCondition . $cond6;
}
}
$item_search_sql = "SELECT ItemNumber, ItemDescription, Category, \n\tDepartmentName, PurchaseCost, FullRetailPrice FROM Item \n\tWHERE {$whereCondition}";
$itemResult = mysql_query($item_search_sql);
//Test whether the queries were successful
if (!$itemResult) {
$item_search_message = "The retrieval of items was unsuccessful: ";
}
$number_item_rows = mysql_num_rows($itemResult);
// Check if results turned out empty
$item_search_message = "";
if ($number_item_rows == 0) {
$item_search_message = "No items found in database";
}
//Display the results
displayItemsPromotions($item_search_message, $itemResult);
//Free the result sets
mysql_free_result($itemResult);
}
function retrievePromotions()
{
$promoCode = $_POST['promoCode'];
$name = $_POST['name'];
$description = $_POST['description'];
$amountOff = $_POST['amountOff'];
$promoType = $_POST['promoType'];
if ($promoType == 'Percent' && $amountOff >= 1) {
$amountOff = str_replace("%", "", $amountOff);
$amountOff = $amountOff / 100;
}
$cond1 = "";
$cond2 = "";
$cond3 = "";
$cond4 = "";
$cond5 = "";
$whereCondition = "";
if (isset($promoCode) && $promoCode != "") {
$cond1 = "PromoCode = '" . $promoCode . "'";
}
if (isset($name) && $name != "") {
$cond2 = "Name = '" . $name . "'";
}
if (isset($description) && $description != "") {
$cond3 = "Description = '" . $description . "'";
}
if (isset($amountOff) && $amountOff != "") {
$cond4 = "AmountOff = " . $amountOff . "";
}
if (isset($promoType) && $promoType != "---") {
$cond5 = "PromoType = '" . $promoType . "'";
}
if ($cond1 != "") {
$whereCondition = $whereCondition . $cond1;
}
if ($cond2 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond2;
} else {
$whereCondition = $whereCondition . $cond2;
}
}
if ($cond3 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond3;
} else {
$whereCondition = $whereCondition . $cond3;
}
}
if ($cond4 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond4;
} else {
$whereCondition = $whereCondition . $cond4;
}
}
if ($cond5 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond5;
} else {
$whereCondition = $whereCondition . $cond5;
}
}
//echo "$whereCondition";
$insertStatement = "SELECT PromoCode, Name, Description,\n\tAmountOff, PromoType FROM Promotion WHERE {$whereCondition}";
//echo "$insertStatement";
/*
echo "$cond1";
echo "$cond2";
echo "$cond3";
echo "$cond4";
echo "$cond5";
*/
/*
if($searchType == "Promotion Code"){
$insertStatement = "SELECT PromoCode, Name, Description,
AmountOff, PromoType FROM Promotion
WHERE PromoCode = '$searchData'";
}
else if($searchType == "Promotion Name"){
$insertStatement = "SELECT PromoCode, Name, Description,
AmountOff, PromoType FROM Promotion
WHERE Name = '$searchData'";
}
else if($searchType == "Promotion Description"){
$insertStatement = "SELECT PromoCode, Name, Description,
AmountOff, PromoType FROM Promotion
WHERE Description = '$searchData'";
}
else if($searchType == "Amount Off"){
$insertStatement = "SELECT PromoCode, Name, Description,
AmountOff, PromoType FROM Promotion
WHERE AmountOff = '$searchData'";
}
else if($searchType == "Promotion Type(Dollar/Percent)"){
$insertStatement = "SELECT PromoCode, Name, Description,
AmountOff, PromoType FROM Promotion
WHERE PromoType = '$searchData'";
}
*/
//Construct SQL statements
//Execute the queries
$result = mysql_query($insertStatement);
$numberPromotionRows = mysql_num_rows($result);
//Test whether the queries were successful
if (!$result || $numberPromotionRows == 0) {
$message = "The retrieval of promotions was unsuccessful";
}
//Display the results
displayItemsPromotions($message, $result);
//Free the result sets
mysql_free_result($result);
}
function searchItemsByCategory()
{
$promoCode = $_POST['promoCode'];
$promoName = $_POST['promoName'];
$amountOff = $_POST['amountOff'];
$promoType = $_POST['promoType'];
echo <<<EOD
\t<h2>Please check all items you would like to add to the promotion {$promoName}</h2>
<h2> Click submit to confirm the addition of all items to the promotion</h2>
<table>
EOD;
$itemNumber = $_POST['itemNumber'];
$itemDescription = $_POST['itemDescription'];
$category = $_POST['category'];
$departmentName = $_POST['departmentName'];
$purchaseCost = $_POST['purchaseCost'];
$fullRetailPrice = $_POST['fullRetailPrice'];
$cond1 = "";
$cond2 = "";
$cond3 = "";
$cond4 = "";
$cond5 = "";
$cond6 = "";
$whereCondition = "";
if (isset($itemNumber) && $itemNumber != "") {
$cond1 = "ItemNumber = '" . $itemNumber . "'";
}
if (isset($itemDescription) && $itemDescription != "") {
$cond2 = "ItemDescription = '" . $itemDescription . "'";
}
if (isset($category) && $category != "---") {
$cond3 = "Category = '" . $category . "'";
}
if (isset($departmentName) && $departmentName != "---") {
$cond4 = "DepartmentName = '" . $departmentName . "'";
}
if (isset($purchaseCost) && $purchaseCost != "") {
$cond5 = "PurchaseCost = '" . $purchaseCost . "'";
}
if (isset($fullRetailPrice) && $fullRetailPrice != "") {
$cond6 = "FullRetailPrice = '" . $fullRetailPrice . "'";
}
if ($cond1 != "") {
$whereCondition = $whereCondition . $cond1;
}
if ($cond2 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond2;
} else {
$whereCondition = $whereCondition . $cond2;
}
}
if ($cond3 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond3;
} else {
$whereCondition = $whereCondition . $cond3;
}
}
if ($cond4 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond4;
} else {
$whereCondition = $whereCondition . $cond4;
}
}
if ($cond5 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond5;
} else {
$whereCondition = $whereCondition . $cond5;
}
}
if ($cond6 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond6;
} else {
$whereCondition = $whereCondition . $cond6;
}
}
$item_search_sql = "SELECT ItemNumber, ItemDescription, Category, \n\tDepartmentName, PurchaseCost, FullRetailPrice FROM Item \n\tWHERE {$whereCondition}";
//Construct SQL statements
/*
if($searchType == "Item Number"){
$item_search_sql = "SELECT ItemNumber, ItemDescription, Category,
DepartmentName, PurchaseCost, FullRetailPrice FROM Item
WHERE ItemNumber = '$searchData'";
}
else if($searchType == "Item Description"){
$item_search_sql = "SELECT ItemNumber, ItemDescription, Category,
DepartmentName, PurchaseCost, FullRetailPrice FROM Item
WHERE ItemDescription = '$searchData'";
}
else if($searchType == "Category"){
$item_search_sql = "SELECT ItemNumber, ItemDescription, Category,
DepartmentName, PurchaseCost, FullRetailPrice FROM Item
WHERE Category = '$searchData'";
}
else if($searchType == "Department Name"){
$item_search_sql = "SELECT ItemNumber, ItemDescription, Category,
DepartmentName, PurchaseCost, FullRetailPrice FROM Item
WHERE DepartmentName = '$searchData'";
}
else if($searchType == "Purchase Cost"){
$item_search_sql = "SELECT ItemNumber, ItemDescription, Category,
DepartmentName, PurchaseCost, FullRetailPrice FROM Item
WHERE PurchaseCost = '$searchData'";
}
else if($searchType == "Full Retail Price"){
$item_search_sql = "SELECT ItemNumber, ItemDescription, Category,
DepartmentName, PurchaseCost, FullRetailPrice FROM Item
WHERE FullRetailPrice = '$searchData'";
}
*/
$itemResult = mysql_query($item_search_sql);
//Test whether the queries were successful
if (!$itemResult) {
$item_search_message = "The retrieval of items was unsuccessful: ";
}
$number_item_rows = mysql_num_rows($itemResult);
// Check if results turned out empty
$item_search_message = "";
if ($number_item_rows == 0) {
$item_search_message = "No items found in database";
}
//Display the results
displayItemsPromotions($item_search_message, $itemResult, $promoCode, $promoName, $amountOff, $promoType);
//Free the result sets
mysql_free_result($itemResult);
}
function searchPromotions()
{
$eventCode = $_POST['eventCode'];
$eventName = $_POST['eventName'];
echo <<<EOD
\t<h2>Please check all promotions you would like to add to the Ad Event {$eventName}</h2>
<h2> Click submit to confirm the addition of all promotions to the Ad Event</h2>
<table>
EOD;
$promoCode = $_POST['promoCode'];
$name = $_POST['name'];
$description = $_POST['description'];
$amountOff = $_POST['amountOff'];
$promoType = $_POST['promoType'];
if ($promoType == 'Percent' && $amountOff >= 1) {
$amountOff = str_replace("%", "", $amountOff);
$amountOff = $amountOff / 100;
}
$cond1 = "";
$cond2 = "";
$cond3 = "";
$cond4 = "";
$cond5 = "";
$whereCondition = "";
if (isset($promoCode) && $promoCode != "") {
$cond1 = "PromoCode = '" . $promoCode . "'";
}
if (isset($name) && $name != "") {
$cond2 = "Name = '" . $name . "'";
}
if (isset($description) && $description != "") {
$cond3 = "Description = '" . $description . "'";
}
if (isset($amountOff) && $amountOff != "") {
$cond4 = "AmountOff = " . $amountOff . "";
}
if (isset($promoType) && $promoType != "---") {
$cond5 = "PromoType = '" . $promoType . "'";
}
if ($cond1 != "") {
$whereCondition = $whereCondition . $cond1;
}
if ($cond2 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond2;
} else {
$whereCondition = $whereCondition . $cond2;
}
}
if ($cond3 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond3;
} else {
$whereCondition = $whereCondition . $cond3;
}
}
if ($cond4 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond4;
} else {
$whereCondition = $whereCondition . $cond4;
}
}
if ($cond5 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond5;
} else {
$whereCondition = $whereCondition . $cond5;
}
}
if ($cond6 != "") {
if (strlen($whereCondition) > 1) {
$whereCondition = $whereCondition . " AND " . $cond6;
} else {
$whereCondition = $whereCondition . $cond6;
}
}
$promo_search_sql = "SELECT PromoCode, Name, Description, AmountOff, PromoType FROM Promotion WHERE {$whereCondition}";
//Construct SQL statements
$promoResult = mysql_query($promo_search_sql);
//Test whether the queries were successful
if (!$promoResult) {
$promo_search_message = "The retrieval of items was unsuccessful: ";
}
$number_promo_rows = mysql_num_rows($promoResult);
// Check if results turned out empty
$promo_search_message = "";
if ($number_promo_rows == 0) {
$promo_search_message = "No items found in database";
}
//Display the results
displayItemsPromotions($promo_search_message, $promoResult, $eventCode, $eventName);
//Free the result sets
mysql_free_result($promoResult);
}
