294 lines
9.5 KiB
PHP
294 lines
9.5 KiB
PHP
<?php
|
|
$ValidAccess = 2010;
|
|
|
|
require_once("include/globals.php");
|
|
|
|
if (SessionActive() != 1 || ! UserLoggedIn()) {
|
|
header("Location: /");
|
|
exit();
|
|
}
|
|
|
|
$action = "menu-details";
|
|
|
|
|
|
// Ensure that only valid user types have access to this module
|
|
$AllowedUserTypes = array('SuperUser','System Administrator','Application Administrator','Application Volunteer','Schedule Volunteer','Administration Team','Schedule Team','Supplier','Financial Team');
|
|
if (checkPermission($AllowedUserTypes) != 1) {
|
|
header("Location: /?action=$action&error_code=NOACCESS");
|
|
return;
|
|
}
|
|
|
|
|
|
// Get parameters
|
|
if (array_key_exists('schedule_num',$_POST)) $schedule_num = intval($_POST['schedule_num']);
|
|
if (array_key_exists('menu_num',$_POST)) $selected_menu_num = $_POST['menu_num'];
|
|
if (!isset($mode)) $mode = 'detail';
|
|
|
|
if (!isset($schedule_num) || !isset($selected_menu_num)) {
|
|
header("Location: /?action=$action&error_code=NOSCHED");
|
|
return;
|
|
}
|
|
|
|
|
|
// Process the menu_num field
|
|
if (is_array($selected_menu_num)) $selected_menu_num = db_escape_string(implode(",",$selected_menu_num));
|
|
|
|
|
|
// Check if this is a valid schedule
|
|
$swhere = "";
|
|
if ($_SESSION['UserInfo']['type'] == 'Supplier') $swhere = " and supplier_num = " . $_SESSION['UserInfo']['supplier_num'];
|
|
$query = "select * from hotlunch_schedules " .
|
|
"where record_num = $schedule_num " .
|
|
"and school_num = $school_num $swhere";
|
|
$results = db_query($query);
|
|
|
|
if (db_num_rows($results) == 0) {
|
|
header("Location: /?action=$action&error_code=INVALIDSCHED");
|
|
return;
|
|
}
|
|
|
|
$hotlunch_schedule = db_fetch_array($results);
|
|
|
|
|
|
|
|
// If this is a ScheduleTeam user check if they have permission to access this schedule
|
|
if (in_array($_SESSION['UserInfo']['type'],array('Schedule Team','Schedule Volunteer'))) {
|
|
$userNum = intval($_SESSION['UserInfo']['record_num']);
|
|
$access = db_getVal("SELECT count(*) FROM hotlunch_schedules_people WHERE school_num = $school_num AND schedule_num = $schedule_num AND user_num = $userNum");
|
|
|
|
if (!$access || $access == 0) {
|
|
echo("<br>\n");
|
|
$error_message = "You are not allowed access to the requested hot lunch schedule";
|
|
include "form.messages.php";
|
|
return;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
// Get set of menu items for these dates
|
|
$query = "SELECT hlmi.*,hlmd.menu_title,hlmd.sequence as menu_sequence,hlmd.menu_date " .
|
|
"FROM hotlunch_menu_items as hlmi, hotlunch_menu_dates as hlmd " .
|
|
"WHERE hlmi.school_num = $school_num " .
|
|
"AND hlmi.menu_num IN ($selected_menu_num) " .
|
|
"AND hlmi.menu_num = hlmd.record_num " .
|
|
"ORDER BY menu_sequence,menu_date,sequence";
|
|
$results = db_query($query);
|
|
|
|
if (db_num_rows($results) == 0) {
|
|
header("Location: /?action=$action&error_code=NOMENUITEMS");
|
|
return;
|
|
}
|
|
|
|
$menu_items = array();
|
|
$count = 0;
|
|
while ($row = db_fetch_array($results)) {
|
|
extract($row);
|
|
$menu_items[$count]['menu_title'] = $menu_title;
|
|
$menu_items[$count]['menu_date'] = $menu_date;
|
|
$menu_items[$count]['title'] = $item_title;
|
|
$menu_items[$count]['online_price'] = $online_price;
|
|
$menu_items[$count]['num'] = $record_num;
|
|
$count++;
|
|
}
|
|
|
|
$cols = count($menu_items) + 1;
|
|
|
|
|
|
// Initialize the spreadsheet document type
|
|
header('Pragma: public');
|
|
header('Expires: 0');
|
|
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
|
|
header('Cache-Control: private',false);
|
|
header("Content-type: application/octet-stream; charset=utf-8");
|
|
header("Content-Disposition: attachment; filename=menu-totals.csv");
|
|
|
|
echo("\xEF\xBB\xBF");
|
|
|
|
$totals = array();
|
|
$totals['qty'] = 0;
|
|
$totals['revenue'] = 0;
|
|
$totals['costs'] = 0;
|
|
$totals['profit'] = 0;
|
|
|
|
|
|
// Output the menu title and dates
|
|
//
|
|
echo('"' . str_replace('"','""',$menu_items[0]['menu_title']) . '",');
|
|
echo('"' . $menu_items[0]['menu_date'] . '"');
|
|
echo("\n");
|
|
|
|
$Headings = array(getLanguageText('TEXT-ITEM'),
|
|
getLanguageText('TEXT-QUANTITY'),
|
|
getLanguageText('TEXT-ITEM-PRICE'),
|
|
getLanguageText('TEXT-NET-REVENUE'),
|
|
"",
|
|
getLanguageText('TEXT-ITEM-COST'),
|
|
getLanguageText('TEXT-NET-COST'),
|
|
getLanguageText('TEXT-ESTIMATED-PROFIT'),
|
|
"",
|
|
"",
|
|
"",
|
|
"",
|
|
"",
|
|
"",
|
|
"",
|
|
"",
|
|
);
|
|
// echo('"getLanguageText('TEXT-ITEM')","getLanguageText('TEXT-QUANTITY')","getLanguageText('TEXT-ITEM-PRICE')","getLanguageText('TEXT-NET-REVENUE');",,"Item Cost","Net Cost","Estimated Profit"');
|
|
for ($i = 0; $i < count($Headings); $i++) {
|
|
if ($i > 0) echo(",");
|
|
echo('"'.$Headings[$i].'"');
|
|
}
|
|
echo("\n");
|
|
|
|
|
|
//
|
|
// Output the menu items titles
|
|
//
|
|
for ($i = 0; $i < count($menu_items); $i++) {
|
|
echo('"' . str_replace('"','""',$menu_items[$i]['title']) . '"');
|
|
echo(",");
|
|
|
|
$menu_item_num = $menu_items[$i]['num'];
|
|
|
|
// Lookup the quantity of each item ordered
|
|
$query = "select sum(qty) as ordered, sum(extended_price) as revenue " .
|
|
"from hotlunch_order_items as hloi, hotlunch_order_header as hloh " .
|
|
"where hloi.school_num = $school_num " .
|
|
"and hloh.school_num = $school_num " .
|
|
"and hloi.schedule_num = $schedule_num " .
|
|
"and hloi.menu_item_num = $menu_item_num " .
|
|
"and hloi.order_num = hloh.record_num " .
|
|
"and hloi.active != 'N' " .
|
|
"and hloh.status IN ('Unpaid','Paid')";
|
|
$results = db_query($query);
|
|
$r = db_fetch_assoc($results);
|
|
$qty = intval($r['ordered']);
|
|
echo($qty);
|
|
echo(",");
|
|
|
|
// Print the item price
|
|
echo($menu_items[$i]['online_price']);
|
|
echo(",");
|
|
|
|
// Calculate net price
|
|
$net = $r['revenue'];
|
|
echo(number_format($net,2));
|
|
echo(",,");
|
|
|
|
$totals['revenue'] += $net;
|
|
|
|
// Get the cost of each item
|
|
$query = "SELECT sum(package_cost / package_qty) " .
|
|
"FROM supplier_products AS sp,hotlunch_menu_products AS hlp " .
|
|
"WHERE sp.school_num = $supplier_school_num " .
|
|
"AND hlp.school_num = $school_num " .
|
|
"AND hlp.menu_item_num = $menu_item_num " .
|
|
"AND hlp.supplier_product_num = sp.record_num";
|
|
$cost = floatval(db_getVal($query));
|
|
$cost = round(100 * $cost) / 100;
|
|
echo($cost);
|
|
echo(",");
|
|
|
|
// Calculate net cost
|
|
$net = $qty * $cost;
|
|
echo($net);
|
|
echo(",");
|
|
|
|
$totals['qty'] += $qty;
|
|
$totals['costs'] += $net;
|
|
|
|
// Calculate profit
|
|
$profit = $net - $cost;
|
|
echo($profit);
|
|
|
|
$totals['profit'] += $profit;
|
|
|
|
// Price points
|
|
$query = "select menu_item_price, sum(qty) as qty " .
|
|
"from hotlunch_order_items as hloi, hotlunch_order_header as hloh " .
|
|
"where hloi.school_num = $school_num " .
|
|
"and hloh.school_num = $school_num " .
|
|
"and hloi.schedule_num = $schedule_num " .
|
|
"and hloi.menu_item_num = $menu_item_num " .
|
|
"and hloi.order_num = hloh.record_num " .
|
|
"and hloi.active != 'N' " .
|
|
"and hloh.status IN ('Unpaid','Paid') " .
|
|
"group by 1 " .
|
|
"order by 1 desc";
|
|
$results = db_query($query);
|
|
if (db_num_rows($results) > 1) {
|
|
echo(",");
|
|
while ($row = db_fetch_assoc($results)) {
|
|
$qty = $row['qty'];
|
|
$price = $row['menu_item_price'];
|
|
echo(",$qty @ ".'$'."$price");
|
|
}
|
|
}
|
|
|
|
echo("\n");
|
|
}
|
|
|
|
echo(getLanguageText('TEXT-TOTAL')."," . $totals['qty'] . ",," . $totals['revenue'] . ",,," . $totals['costs'] . "," . $totals['profit']);
|
|
echo("\n");
|
|
echo("\n");
|
|
|
|
|
|
// Revenue by order status
|
|
echo(",".getLanguageText('TEXT-SALES').",".getLanguageText('TEXT-REVENUE')."\n");
|
|
$Status = array('Paid','Unpaid','Complimentary');
|
|
$sales = 0;
|
|
$total_revenue = 0;
|
|
for ($i = 0; $i < count($Status); $i++) {
|
|
$status = $Status[$i];
|
|
$query = "SELECT SUM(extended_price) FROM hotlunch_order_items AS hloi, hotlunch_order_header AS hloh " .
|
|
"WHERE hloh.school_num = $school_num " .
|
|
"AND hloi.school_num = $school_num " .
|
|
"AND hloi.menu_num IN ($selected_menu_num) " .
|
|
"AND hloh.record_num = hloi.order_num " .
|
|
"AND hloh.status = '$status'";
|
|
$revenue = db_getVal($query);
|
|
$revenue = floatval($revenue);
|
|
$sales += $revenue;
|
|
$sText = getLanguageText('TEXT-'.strtoupper($status));
|
|
echo("$sText,$revenue,");
|
|
if ($status != 'Complimentary') {
|
|
echo("$revenue");
|
|
$total_revenue += $revenue;
|
|
}
|
|
echo("\n");
|
|
}
|
|
|
|
echo(getLanguageText('TEXT-TOTAL').",$sales,$total_revenue\n");
|
|
echo("\n");
|
|
|
|
// Additional revenue
|
|
$query = "SELECT sum(amount) FROM hotlunch_menu_transactions WHERE school_num = $school_num AND menu_num IN ($selected_menu_num) AND type = 'Revenue'";
|
|
$additional_revenue = floatval(db_getVal($query));
|
|
echo(getLanguageText('TEXT-OTHER-REVENUE').",,$additional_revenue\n");
|
|
echo("\n");
|
|
|
|
// Menu item costs
|
|
$query = "SELECT SUM(actual_cost) FROM supplier_order_items_actual " .
|
|
"WHERE school_num = $school_num " .
|
|
"AND menu_num IN ($selected_menu_num)";
|
|
$cost = db_getVal($query);
|
|
echo(getLanguageText('TEXT-COST').",,$cost\n");
|
|
|
|
// Additional costs
|
|
$query = "SELECT sum(amount) FROM hotlunch_menu_transactions WHERE school_num = $school_num AND menu_num IN ($selected_menu_num) AND type = 'Expense'";
|
|
$additional_cost = db_getVal($query);
|
|
echo(getLanguageText('TEXT-OTHER-COSTS').",,$additional_cost\n");
|
|
echo("\n");
|
|
|
|
$total_revenue = floatval($total_revenue);
|
|
$additional_revenue = floatval($additional_revenue);
|
|
$cost = floatval($cost);
|
|
$additional_cost = floatval($additional_cost);
|
|
$profit = $total_revenue + $additional_revenue - $cost - $additional_cost;
|
|
echo(getLanguageText('TEXT-PROFIT').",,$profit\n");
|
|
|
|
|
|
?>
|