function insert_event()
{
// Connect to the 'test' database
// The parameters are defined in the db_cn.inc file
// These are global constants
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
// Get the information entered into the webpage by the user
// These are available in the super global variable $_POST
// This is actually an associative array, indexed by a string
$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']);
// Create a String consisting of the SQL command. Remember that
// . is the concatenation operator. $varname within double quotes
// will be evaluated by PHP
$insertStmt = "INSERT INTO AdEvent (EventCode, AdEvent.Name, StartDate, EndDate,\n\t\t Description, AdType) values ( '{$eventCode}', '{$eventName}', '{$startDate}',\n '{$endDate}', '{$description}', '{$type}')";
//Execute the query. The result will just be true or false
$result = mysql_query($insertStmt);
$message = "";
if (!$result) {
$message = "Error in inserting Event: <br />Event Name: {$eventName}<br />Start Date: {$startDate}<br />End Date:\n{$endDate}<br />" . mysql_error();
} else {
$message = "Data for Event inserted successfully.<br />Event Name: {$eventName}<br />Start Date: {$startDate}<br />End\nDate: {$endDate}<br />Description: {$description}<br />Type: {$type}<br />";
}
ui_show_event_insert_result($message, $eventName, $startDate, $endDate, $description, $type);
}
function add_promotion_to_event()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$promoCodes = $_POST['promoCodes'];
$eventCode = $_REQUEST['eventCode'];
//echo "Item number = $itemNumber \nPromoCode = $promoCode";
//Calculate sale price
$message = "";
if (empty($promoCodes)) {
$message .= "Error: No Promotions Selected.";
} else {
$count = count($promoCodes);
$message .= "{$count} Promotions selected to be added to Event.<br />";
foreach ($promoCodes as $promoCode) {
if (!promotionExistsAlreadyInEvent($promoCode, $eventCode)) {
$insertStmt = "INSERT INTO AdEventPromotion (PromoCode, EventCode) values ( '{$promoCode}','{$eventCode}')";
$result = mysql_query($insertStmt);
if (!$result) {
$message .= "Error adding Promotion to Event. <br />Event Code: {$eventCode}<br />PromoCode:\n {$promoCode}<br /><br />" . mysql_error() . "<br />";
} else {
$message .= "Promotion added to Event successfully.<br />Event Code: {$eventCode}<br />PromoCode:\n {$promoCode}<br /><br />";
}
} else {
$message .= "Promotion (PromoCode: {$promoCode}) already exists in Event (Event Code: {$eventCode}).<br /><br />";
}
}
}
ui_show_adEvent_promotion_insert_result($message);
}
function insert_promotion()
{
// Connect to the 'test' database
// The parameters are defined in the teach_cn.inc file
// These are global constants
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
// Get the information entered into the webpage by the user
// These are available in the super global variable $_POST
// This is actually an associative array, indexed by a string
$promotionNumber = mysql_real_escape_string($_POST['promotionNumber']);
$promotionName = mysql_real_escape_string($_POST['promotionName']);
$promotionDescription = mysql_real_escape_string($_POST['promotionDescription']);
$promotionValue = mysql_real_escape_string($_POST['amountOff']);
$promotionType = mysql_real_escape_string($_POST['promotionType']);
// Create a String consisting of the SQL command. Remember that
// . is the concatenation operator. $varname within double quotes
// will be evaluated by PHP
$insertStmt = "INSERT INTO Promotion (Name, Description, AmountOff,\n\t\t PromoType) values ('{$promotionName}', '{$promotionDescription}',\n '{$promotionValue}', '{$promotionType}')";
//Execute the query. The result will just be true or false
$result = mysql_query($insertStmt);
$message = "";
if (!$result) {
$message = "Error in inserting Promotion. <br />Promotion Description: {$promotionDescription} <br />Promotion Type: {$promotionType} <br />Promotion Name: {$promotionName} <br />Amount Off: {$promotionValue}<br />" . mysql_error();
} else {
$message = "Data for Promotion inserted successfully. <br />Promotion Description: {$promotionDescription} <br />Promotion Type: {$promotionType} <br />Promotion Name: {$promotionName} <br />Amount Off: {$promotionValue}";
}
ui_show_promotion_insert_result($message, $promotionNumber, $promotionName, $promotionDescription, $promotionValue, $promotionType);
}
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 insert_item()
{
// Connect to the 'test' database
// The parameters are defined in the teach_cn.inc file
// These are global constants
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
// Get the information entered into the webpage by the user
// These are available in the super global variable $_POST
// This is actually an associative array, indexed by a string
$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']);
$purchaseCost = mysql_real_escape_string($_POST['purchaseCost']);
$retailPrice = mysql_real_escape_string($_POST['retailPrice']);
// Create a String consisting of the SQL command. Remember that
// . is the concatenation operator. $varname within double quotes
// will be evaluated by PHP
$insertStmt = "INSERT INTO Item (ItemNumber, ItemDescription, Category, DepartmentName,\n\t\t PurchaseCost, FullRetailPrice) values ( '{$itemNumber}','{$itemDescription}', '{$category}',\n '{$departmentName}', '{$purchaseCost}', '{$retailPrice}')";
//Execute the query. The result will just be true or false
$result = mysql_query($insertStmt);
$message = "";
if (!$result) {
$message = "Error in inserting Item. <br />Item Number: {$itemNumber}<br />Item Description:\n{$itemDescription} <br />Category:\n{$category}\n<br />Department\n Name: {$departmentName} <br />" . mysql_error();
} else {
$message = "Data for Item inserted successfully. <br />Item Number: {$itemNumber}<br />Item Description: {$itemDescription} <br />Category: {$category} <br />Department Name: {$departmentName}";
}
ui_show_item_insert_result($message, $itemNumber, $itemDescription, $category, $departmentName, $purchaseCost, $retailPrice);
}
function add_item_to_promotion()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$itemNumbers = $_POST['itemNumbers'];
$promoCode = $_REQUEST['promoCode'];
//echo "Item number = $itemNumber \nPromoCode = $promoCode";
//Calculate sale price
$promoType = getPromoType($promoCode);
$amountOff = getAmountOff($promoCode);
$message = "";
if (empty($itemNumbers)) {
$message .= "Error: No Items Selected.";
} else {
$count = count($itemNumbers);
$message .= "{$count} Items selected to be added to Promotion.<br />";
foreach ($itemNumbers as $itemNumber) {
if (!itemExistsAlreadyInPromotion($itemNumber, $promoCode)) {
$item_retail_price = getItemRetailPrice($itemNumber);
//echo "retail price : $item_retail_price \n promotype: $promoType \n amount off: $amountOff";
$salePrice = getSalePrice($item_retail_price, $promoType, $amountOff);
$insertStmt = "INSERT INTO PromotionItem (PromoCode, ItemNumber, SalePrice) values ( '{$promoCode}','{$itemNumber}', '{$salePrice}')";
$result = mysql_query($insertStmt);
if (!$result) {
$message .= "Error adding Item to Promotion. <br />Promo Code: {$promoCode}<br />Item Number:\n {$itemNumber}<br />Sale Price: {$salePrice}<br /><br />" . mysql_error() . "<br />";
} else {
$message .= "Item added to Promotion successfully.<br />Promo Code: {$promoCode}<br />Item Number:\n {$itemNumber}<br />Sale Price: {$salePrice}<br /><br />";
}
} else {
$message .= "Item (Item Number: {$itemNumber}) already exists in Promotion (Promo Code: {$promoCode}).<br /><br />";
}
}
}
ui_show_promotion_item_insert_result($message);
}
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 getPromotion()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$promoCode = $_REQUEST['promoCode'];
$sql = "SELECT PromoCode, Name, Description, AmountOff, PromoType\n FROM Promotion\n WHERE PromoCode = '" . $promoCode . "'";
$error_message = "Could not successfully run query to get promotion data ({$sql}) from DB: ";
$not_found_message = "Promotion with PromoCode ({$promoCode}) not found in DB";
$row = get_unique_row($sql, $error_message, $not_found_message);
$promoCode = $row["PromoCode"];
$name = $row["Name"];
$description = $row["Description"];
$amountOff = $row["AmountOff"];
$promoType = $row["PromoType"];
ui_show_update_promotion_form($promoCode, $name, $description, $amountOff, $promoType);
}
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 getItem()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$itemNumber = $_REQUEST['itemNumber'];
$sql = "SELECT ItemNumber, ItemDescription, Category, DepartmentName, PurchaseCost, FullRetailPrice\n FROM Item\n WHERE ItemNumber = '" . $itemNumber . "'";
$error_message = "Could not successfully run query to get item data ({$sql}) from DB: ";
$not_found_message = "Item with Item Number ({$itemNumber}) not found in DB";
$row = get_unique_row($sql, $error_message, $not_found_message);
$itemNumber = $row["ItemNumber"];
$itemDescription = $row["ItemDescription"];
$category = $row["Category"];
$deptName = $row["DepartmentName"];
$purchCost = $row["PurchaseCost"];
$retail = $row["FullRetailPrice"];
ui_show_update_item_form($itemNumber, $itemDescription, $category, $deptName, $purchCost, $retail);
}
function getEvent()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$eventCode = $_REQUEST['eventCode'];
$sql = "SELECT EventCode, AdEvent.Name, StartDate, EndDate, Description, AdType\n FROM AdEvent\n WHERE EventCode = '" . $eventCode . "'";
$error_message = "Could not successfully run query to get event data ({$sql}) from DB: ";
$not_found_message = "Event with Event Code ({$eventCode}) not found in DB";
$row = get_unique_row($sql, $error_message, $not_found_message);
$eventCode = $row["EventCode"];
$name = $row["Name"];
$startDate = $row["StartDate"];
$endDate = $row["EndDate"];
$description = $row["Description"];
$adType = $row["AdType"];
ui_show_update_event_form($eventCode, $name, $startDate, $endDate, $description, $adType);
}
function insert_promotion_item()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
$itemId = $_POST['itemId'];
$promotionId = $_POST['promotionId'];
$salePrice = $_POST['salePrice'];
$insertStatement = "INSERT INTO Promotion-Item (PromoCode, ItemNumber, SalePrice,\n\t\t PromoType) values ( '{$promotionId}', '{$itemId}',\n '{$salePrice}')";
//Execute the query. The result will just be true or false
$result = mysql_query($insertStatement);
$message = "";
if (!$result) {
$message = "Error in inserting Promotion: {$promotionId} , with Item: {$itemId}: ";
} else {
$message = "The Promotion {$promotionId} with Item {$itemId} was inserted successfully.";
}
ui_show_promotion_item_insert_result($message, $promotionId, $itemId, $salePrice);
}
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 update_item()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
// Get the bannerid and other data sent by the user from the query
$itemNumber = $_REQUEST['itemNumber'];
$sql = "SELECT E.EventCode, E.Name, E.StartDate, E.EndDate, E.Description, E.AdType, I.FullRetailPrice, PI.SalePrice, (I.FullRetailPrice - PI.SalePrice) AS Savings\nFROM AdEvent AS E\nINNER JOIN AdEventPromotion AS EP\n\tON E.EventCode = EP.EventCode\nINNER JOIN PromotionItem AS PI\n\tON EP.PromoCode = PI.PromoCode\nINNER JOIN Item AS I\n\tON PI.ItemNumber = I.ItemNumber\nWHERE I.ItemNumber = '{$itemNumber}'\nORDER BY Savings DESC";
$result = execute_SQL_query_with_no_error_report($sql);
$message = "";
if (!$result) {
$message .= "Error finding savings for Item: " . $itemNumber . " in database.<br />" . mysql_error() . "<hr />";
} else {
if (count_rows_in_result_set($result) == 0) {
$message .= "Item (Item Number: " . $itemNumber . ") is currently a not a part of any Ad Events at this moment.<hr />";
} else {
$message .= "Listed below are all of the events which offer the greatest amount of savings currently for Item\n with Item Number: " . $itemNumber . "<hr />";
}
}
ui_show_item_savings_details($message, $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 update_event()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
// Get the bannerid and other data sent by the user from the query
$eventCode = $_REQUEST['eventCode'];
$name = 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"]);
$updateStmt = "update AdEvent\n\tset AdEvent.Name = '" . $name . "', StartDate = '" . $startDate . "', EndDate = '" . $endDate . "',\n\tDescription = '" . $description . "', AdType = '" . $type . "'" . "WHERE EventCode = '" . $eventCode . "'";
$result = execute_SQL_query_with_no_error_report($updateStmt);
$message = "";
if (!$result) {
$message .= "Error in updating Event: " . $eventCode . " in database.<br />" . mysql_error() . "<hr />";
} else {
$message = "Data for Event updated successfully.<br />Event Code: {$eventCode} <br />Event Name: {$name}<br />Start\nDate:\n{$startDate}<br />End\nDate: {$endDate}<br />Description: {$description}<br />Type: {$type}<br /><br />";
}
ui_show_event_update_details($message);
}
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);
}
function update_promotion()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
// Get the bannerid and other data sent by the user from the query
$promoCode = $_POST['promoCode'];
$name = mysql_real_escape_string($_POST["promotionName"]);
$description = mysql_real_escape_string($_POST["promotionDescription"]);
$amountOff = mysql_real_escape_string($_POST["amountOff"]);
$promoType = mysql_real_escape_string($_POST["promotionType"]);
$updateStmt = "update Promotion\n\tset Name = '" . $name . "', Description = '" . $description . "', AmountOff = '" . $amountOff . "',\n\tPromoType = '" . $promoType . "'" . "WHERE PromoCode = '" . $promoCode . "'";
$result = execute_SQL_query_with_no_error_report($updateStmt);
$message = "";
if (!$result) {
$message .= "Error in updating Promotion: " . $promoCode . " in database.<br />" . mysql_error() . "<hr />";
} else {
$message = "Data for Promotion updated successfully.<br />PromoCode: {$promoCode}<br />Promotion Description:\n{$description} <br />Promotion\nType:\n{$promoType} <br />Promotion Name: {$name} <br />Amount Off: {$amountOff}<br /><br />";
}
$getPromoItemsStmt = "SELECT * FROM PromotionItem WHERE PromoCode = '{$promoCode}'";
//echo "$getPromoItemsStmt";
$promoItems = execute_SQL_query_with_no_error_report($getPromoItemsStmt);
$numPromoItems = count_rows_in_result_set($promoItems);
while ($promoItem = mysql_fetch_assoc($promoItems)) {
$id = $promoItem['ID'];
$oldSalePrice = $promoItem['SalePrice'];
$itemNo = $promoItem['ItemNumber'];
$retail = getRetailPrice($itemNo);
//echo "ID = $id PromoCode: $promoCode OldSalePrice: $oldSalePrice";
$newSalePrice = getNewSalePrice($retail, $promoType, $amountOff);
$promoItemUpdateStmt = "UPDATE PromotionItem\n set SalePrice = '{$newSalePrice}'\n where ID = '{$id}'";
$result = execute_SQL_query_with_no_error_report($promoItemUpdateStmt);
if (!$result) {
$message .= "Error in updating Promotion Item: " . $id . " in database.<br />" . mysql_error() . "<hr\n />";
} else {
$message .= "Data for Promotion Item with ID: " . $id . " updated successfully. <br />Old Sale Price: " . $oldSalePrice . "<br />New Sale Price: " . $newSalePrice . "<hr />";
}
}
ui_show_promotion_update_details($message);
}
function update_item()
{
connect_and_select_db(DB_SERVER, DB_UN, DB_PWD, DB_NAME);
// Get the bannerid and other data sent by the user from the query
$itemNumber = $_REQUEST['itemNumber'];
$itemDescription = mysql_real_escape_string($_POST["itemDescription"]);
$category = mysql_real_escape_string($_POST["category"]);
$deptName = mysql_real_escape_string($_POST["departmentName"]);
$purchCost = mysql_real_escape_string($_POST["purchaseCost"]);
$retail = mysql_real_escape_string($_POST["retailPrice"]);
$updateStmt = "update Item\n\tset ItemDescription = '" . $itemDescription . "', Category = '" . $category . "', DepartmentName = '" . $deptName . "',\n\tPurchaseCost = '" . $purchCost . "', FullRetailPrice = '" . $retail . "'" . "WHERE ItemNumber = '" . $itemNumber . "'";
$result = execute_SQL_query_with_no_error_report($updateStmt);
$message = "";
if (!$result) {
$message .= "Error in updating Item: " . $itemNumber . " in database.<br />" . mysql_error() . "<hr />";
} else {
$message = "Data for Item updated successfully. <br />Item Number: {$itemNumber}<br />Item Description: {$itemDescription}\n<br />Category: {$category} <br />Department Name: {$deptName}<br />Purchase Cost: {$purchCost}<br\n/>Retail Price: {$retail}<br /><br />";
}
$getPromoItemsStmt = "SELECT * FROM PromotionItem WHERE ItemNumber = '{$itemNumber}'";
//echo "$getPromoItemsStmt";
$promoItems = execute_SQL_query_with_no_error_report($getPromoItemsStmt);
$numPromoItems = count_rows_in_result_set($promoItems);
while ($promoItem = mysql_fetch_assoc($promoItems)) {
$id = $promoItem['ID'];
$promoCode = $promoItem['PromoCode'];
$oldSalePrice = $promoItem['SalePrice'];
//echo "ID = $id PromoCode: $promoCode OldSalePrice: $oldSalePrice";
$newSalePrice = getNewSalePrice($retail, $promoCode);
$promoItemUpdateStmt = "UPDATE PromotionItem\n set SalePrice = '{$newSalePrice}'\n where ID = '{$id}'";
$result = execute_SQL_query_with_no_error_report($promoItemUpdateStmt);
if (!$result) {
$message .= "Error in updating Promotion Item: " . $id . " in database.<br />" . mysql_error() . "<hr\n />";
} else {
$message .= "Data for Promotion Item with ID: " . $id . " updated successfully. <br />Old Sale Price: " . $oldSalePrice . "<br />New Sale Price: " . $newSalePrice . "<hr />";
}
}
ui_show_item_update_details($message);
}
