function get_all_promo_events()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$startDate = mysql_real_escape_string($_POST['startDate']);
$endDate = mysql_real_escape_string($_POST['endDate']);
//Retrieve all events that occur during the time period including the outer bounds
if ($startDate == null || $startDate == "") {
$startDate = "0000-00-00";
}
if ($endDate == null || $endDate == "") {
$endDate = "9999-99-99";
}
//$startDate = str_replace("-", "", $startDate);
//$endDate = str_replace("-", "", $endDate);
$sql = "SELECT * FROM AdEvent WHERE " . "(StartDate >= '{$startDate}' " . "AND StartDate <= '{$endDate}') " . "OR (EndDate >= '{$startDate}' " . "AND EndDate <= '{$endDate}') " . "ORDER BY AdEvent.StartDate ASC;";
//$sql = "SELECT * FROM AdEvent ORDER BY AdEvent.StartDate ASC"; --older, incorrect version
$error_message = "Could not successfully run query ({$sql}) from DB: ";
$search_events_result = get_result_set_from_select_query($sql, $error_message);
//$result is non-empty. So count the rows
$numrows = mysql_num_rows($search_events_result);
//Create an appropriate message
$message = "";
if ($numrows == 0) {
$message = "No events found in database";
}
ui_show_events_retrieved($message, $search_events_result);
}
function get_events_matching_search_criteria()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$eventCode = mysql_real_escape_string($_POST['eventCode']);
$eventName = mysql_real_escape_string($_POST['eventName']);
$startDate = mysql_real_escape_string($_POST['startDate']);
$endDate = mysql_real_escape_string($_POST['endDate']);
$description = mysql_real_escape_string($_POST['description']);
$type = mysql_real_escape_string($_POST['type']);
if ($startDate == null || $startDate == "") {
$startDate = "0000-00-00";
}
if ($endDate == null || $endDate == "") {
$endDate = "9999-99-99";
}
$sql = "SELECT EventCode, AdEvent.Name, StartDate, EndDate, Description,AdType\n FROM AdEvent\n WHERE EventCode LIKE '%" . $eventCode . "%'\n AND AdEvent.Name LIKE '%" . $eventName . "%'\n AND Description LIKE '%" . $description . "%'\n AND AdEvent.AdType LIKE '%" . $type . "%'\n AND((StartDate >= '{$startDate}' " . "AND StartDate <= '{$endDate}') " . "OR (EndDate >= '{$startDate}' " . "AND EndDate\n <= '{$endDate}')) ORDER BY StartDate";
$error_message = "Could not successfully run query ({$sql}) from DB: ";
$search_events_result = get_result_set_from_select_query($sql, $error_message);
//$result is non-empty. So count the rows
$numrows = mysql_num_rows($search_events_result);
//Create an appropriate message
$message = "";
if ($numrows == 0) {
$message = "No events found in database";
}
ui_show_events_retrieved($message, $search_events_result);
}
function get_top50_sales()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
//Get top 50 items on sale and display the event that it is a part of
$sql = "SELECT E.EventCode, E.Name, E.StartDate, E.EndDate, I.ItemNumber, I.ItemDescription,\n I.Category, I.DepartmentName, I.PurchaseCost, I.FullRetailPrice, PI.SalePrice,\n (I.FullRetailPrice - PI.SalePrice) AS Savings FROM Item AS I\n INNER JOIN PromotionItem AS PI\n ON PI.ItemNumber = I.ItemNumber\n INNER JOIN AdEventPromotion AS EP\n ON EP.PromoCode = PI.PromoCode\n INNER JOIN AdEvent AS E\n ON E.EventCode = EP.EventCode\n ORDER BY Savings DESC\n LIMIT 0, 50";
$error_message = "Could not successfully run query ({$sql}) from DB: ";
$search_sales_result = get_result_set_from_select_query($sql, $error_message);
//$result is non-empty. So count the rows
$numrows = mysql_num_rows($search_sales_result);
//Create an appropriate message
$message = "";
if ($numrows == 0) {
$message = "No items on sale found in database";
}
ui_show_top50_sales_retrieved($message, $search_sales_result);
}
function get_items_matching_search_criteria()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$promoCode = mysql_real_escape_string($_POST['itemNumber']);
$name = mysql_real_escape_string($_POST['itemDescription']);
$description = mysql_real_escape_string($_POST['category']);
$sql = "SELECT *\n FROM Promotion\n WHERE PromoCode LIKE '%" . $promoCode . "%'\n AND Description LIKE '%" . $description . "%'\n AND Promotion.Name LIKE '%" . $name . "%'";
$error_message = "Could not successfully run query ({$sql}) from DB: ";
$search_promotions_result = get_result_set_from_select_query($sql, $error_message);
//$result is non-empty. So count the rows
$numrows = mysql_num_rows($search_promotions_result);
//Create an appropriate message
$message = "";
if ($numrows == 0) {
$message = "No promotions found in database";
}
ui_show_promotions_retrieved($message, $search_promotions_result);
}
function get_items_matching_search_criteria()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$itemNumber = mysql_real_escape_string($_POST['itemNumber']);
$itemDescription = mysql_real_escape_string($_POST['itemDescription']);
$category = mysql_real_escape_string($_POST['category']);
$departmentName = mysql_real_escape_string($_POST['departmentName']);
$sql = "SELECT ItemNumber, ItemDescription, Category, DepartmentName, PurchaseCost, FullRetailPrice\n FROM Item\n WHERE ItemNumber LIKE '%" . $itemNumber . "%'\n AND ItemDescription LIKE '%" . $itemDescription . "%'\n AND Category LIKE '%" . $category . "%'\n AND DepartmentName LIKE '%" . $departmentName . "'";
$error_message = "Could not successfully run query ({$sql}) from DB: ";
$search_items_result = get_result_set_from_select_query($sql, $error_message);
//$result is non-empty. So count the rows
$numrows = mysql_num_rows($search_items_result);
//Create an appropriate message
$message = "";
if ($numrows == 0) {
$message = "No items found in database";
}
ui_show_items_retrieved($message, $search_items_result);
}
function get_events_matching_search_criteria()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$promoCodes = $_POST['promoCodes'];
$eventCode = mysql_real_escape_string($_POST['eventCode']);
$name = mysql_real_escape_string($_POST['name']);
$startDate = mysql_real_escape_string($_POST['startDate']);
$endDate = mysql_real_escape_string($_POST['endDate']);
$description = mysql_real_escape_string($_POST['description']);
$adType = mysql_real_escape_string($_POST['adType']);
$sql = "SELECT *\n FROM AdEvent\n WHERE EventCode LIKE '%" . $eventCode . "%'\n AND AdEvent.Name LIKE '%" . $name . "%'\n AND StartDate LIKE '%" . $startDate . "%'\n AND EndDate LIKE '%" . $endDate . "%'\n AND Description LIKE '%" . $description . "%'\n AND AdType LIKE '%" . $adType . "%'";
$error_message = "Could not successfully run query ({$sql}) from DB: ";
$search_events_result = get_result_set_from_select_query($sql, $error_message);
//$result is non-empty. So count the rows
$numrows = mysql_num_rows($search_events_result);
//Create an appropriate message
$message = "";
if ($numrows == 0) {
$message = "No events found in database";
}
ui_show_events_retrieved($message, $search_events_result, $promoCodes);
}
