262 lines
9.5 KiB
PHP
262 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');
|
|
if (checkPermission($AllowedUserTypes) != 1) {
|
|
header("Location: /?action=$action&error_code=NOACCESS");
|
|
return;
|
|
}
|
|
|
|
|
|
$moduleName = "hotlunch_schedule.totals.csv.php";
|
|
// startLog($moduleName);
|
|
|
|
|
|
// Get parameters
|
|
if (array_key_exists('schedule_num',$_POST)) $schedule_num = intval($_POST['schedule_num']);
|
|
if (!isset($mode)) $mode = 'detail';
|
|
|
|
if (!isset($schedule_num)) {
|
|
header("Location: /?action=$action&error_code=INVALIDSCHED");
|
|
return;
|
|
}
|
|
|
|
|
|
// 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 = $_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) {
|
|
$error_message = getLanguageText('ERRORMSG-NO-SCHEDULE-ACCESS');
|
|
include "form.messages.php";
|
|
return;
|
|
}
|
|
}
|
|
|
|
|
|
// Ensure that the price_category has a value
|
|
db_query("UPDATE hotlunch_order_header SET price_category = 'Regular' WHERE school_num = $school_num AND source = 'On-Line' AND (price_category = '' OR price_category IS NULL)");
|
|
|
|
|
|
// 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=schedule-totals.csv");
|
|
header('Content-Transfer-Encoding: text');
|
|
|
|
$output = fopen('php://output','w');
|
|
|
|
// fputcsv($output,"\xEF\xBB\xBF");
|
|
|
|
|
|
|
|
// Set up pricing groups and titles
|
|
$priceGroups = array(
|
|
array('On-Line','Regular',getLanguageText('TEXT-ON-LINE'),"online_price"),
|
|
array('Paper','Regular',getLanguageText('TEXT-PAPER'),"offline_price"),
|
|
array('On-Line','Special 1',$Special1_Title,"special_price1"),
|
|
array('On-Line','Special 2',$Special2_Title,"special_price2"),
|
|
array('On-Line','Special 3',$Special3_Title,"special_price3"),
|
|
array('Concession','Regular'),
|
|
);
|
|
$titles = array(getLanguageText('TEXT-ITEM'));
|
|
$pricingGroups = array();
|
|
|
|
for ($i = 0; $i < count($priceGroups); $i++) {
|
|
$source = $priceGroups[$i][0];
|
|
$cat = $priceGroups[$i][1];
|
|
if ($source != 'Paper') {
|
|
$numOrders = db_getVal("SELECT count(*) FROM hotlunch_order_header WHERE school_num = $school_num AND schedule_num = $schedule_num AND source = '$source' AND price_category = '$cat'");
|
|
}
|
|
else {
|
|
$numOrders = db_getVal("SELECT count(*) FROM hotlunch_order_header WHERE school_num = $school_num AND schedule_num = $schedule_num AND source = 'Paper'");
|
|
}
|
|
|
|
if ($numOrders > 0) {
|
|
// $titles .= "," . $priceGroups[$i][2] . "," . getLanguageText('TEXT-PRICE') . "," . getLanguageText('TEXT-REVENUE') . ",";
|
|
$titles[] = $priceGroups[$i][2];
|
|
$titles[] = getLanguageText('TEXT-PRICE');
|
|
$titles[] = getLanguageText('TEXT-REVENUE');
|
|
$pricingGroups[] = array($source,$cat,$priceGroups[$i][3]);
|
|
}
|
|
}
|
|
|
|
$titles[] = getLanguageText('TEXT-QUANTITY');
|
|
$titles[] = getLanguageText('TEXT-COST');
|
|
$titles[] = getLanguageText('TEXT-NET');
|
|
$titles[] = getLanguageText('TEXT-PROFIT');
|
|
|
|
|
|
// Get set of menu dates to process
|
|
$menu_dates = db_query("SELECT * FROM hotlunch_menu_dates WHERE school_num = $school_num AND schedule_num = $schedule_num ORDER BY sequence,menu_date");
|
|
|
|
while ($row = db_fetch_array($menu_dates)) {
|
|
$selected_menu_num = $row['record_num'];
|
|
|
|
// Get set of menu items for this date
|
|
$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 " .
|
|
"AND item_type = 'Item' " .
|
|
"ORDER BY menu_sequence,menu_date,sequence";
|
|
$results = db_query($query);
|
|
|
|
if (db_num_rows($results) == 0) continue;
|
|
|
|
$menu_items = array();
|
|
$count = 0;
|
|
$total_revenue = 0;
|
|
$total_profit = 0;
|
|
$total_cost = 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]['offline_price'] = $offline_price;
|
|
$menu_items[$count]['special_price1'] = $special_price1;
|
|
$menu_items[$count]['special_price2'] = $special_price2;
|
|
$menu_items[$count]['special_price3'] = $special_price3;
|
|
$menu_items[$count]['menu_num'] = $menu_num;
|
|
$menu_items[$count]['num'] = $record_num;
|
|
$count++;
|
|
}
|
|
|
|
|
|
|
|
// Output the menu title and dates
|
|
//
|
|
$line = $menu_items[0]['menu_title'];
|
|
if ($hotlunch_schedule['schedule_type'] == 'Hot Lunch' || $hotlunch_schedule['schedule_type'] == 'Date Sensitive' || $hotlunch_schedule['display_menu_date'] == 'Y') {
|
|
$line .= " - " . date($school_record['date_format'],strtotime($menu_items[0]['menu_date']));
|
|
}
|
|
fputcsv($output,array($line));
|
|
|
|
fputcsv($output,$titles);
|
|
|
|
|
|
//
|
|
// Output the menu items titles
|
|
//
|
|
for ($i = 0; $i < count($menu_items); $i++) {
|
|
$menu_item_num = $menu_items[$i]['num'];
|
|
$menu_num = $menu_items[$i]['menu_num'];
|
|
|
|
$line = array();
|
|
$net_revenue = 0;
|
|
$item_qty = 0;
|
|
|
|
// Print the item title
|
|
$value = $menu_items[$i]['title'];
|
|
$line[] = $value;
|
|
|
|
// Process each price group
|
|
for ($j = 0; $j < count($pricingGroups); $j++) {
|
|
$source = $pricingGroups[$j][0];
|
|
$cat = $pricingGroups[$j][1];
|
|
$pfield = $pricingGroups[$j][2];
|
|
|
|
$catwhere = "and hloh.price_category = '$cat' ";
|
|
if ($source == 'Paper') $catwhere = "";
|
|
|
|
// Lookup the quantity of each item ordered
|
|
$query = "select sum(qty) as total 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 hloh.schedule_num = $schedule_num " .
|
|
"and hloi.order_num = hloh.record_num " .
|
|
"and hloi.menu_item_num = $menu_item_num " .
|
|
"and hloh.source = '$source' " .
|
|
"and hloh.status IN ('Unpaid','Paid') " .
|
|
$catwhere .
|
|
"and hloi.active != 'N'";
|
|
$qty = floatval(db_getVal($query));
|
|
$line[] = number_format($qty,0);
|
|
|
|
// Print the item price
|
|
$line[] = $currencySymbol . number_format($menu_items[$i][$pfield],2);
|
|
|
|
// Calculate net price
|
|
$item_qty += $qty;
|
|
$item_revenue = $qty * $menu_items[$i][$pfield];
|
|
$net_revenue += $qty * $menu_items[$i][$pfield];
|
|
$line[] = $currencySymbol . number_format($item_revenue,2);
|
|
$total_revenue += $net_revenue;
|
|
}
|
|
|
|
// Get the cost of each item
|
|
$query = "SELECT sum(package_cost / package_qty) " .
|
|
"FROM supplier_products AS sp,hotlunch_menu_products AS hlp " .
|
|
"WHERE hlp.school_num = $school_num " .
|
|
"AND sp.school_num = $supplier_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;
|
|
$line[] = $item_qty;
|
|
$line[] = $currencySymbol . number_format($cost,2);
|
|
|
|
// Calculate net cost
|
|
$net_cost = $item_qty * $cost;
|
|
// $net_cost = getSupplierCosts($schedule_num,$menu_num,$menu_item_num);
|
|
$line[] = $currencySymbol . number_format($net_cost,2);
|
|
|
|
// Calculate profit
|
|
$profit = $net_revenue - $net_cost;
|
|
$line[] = $currencySymbol . number_format($profit,2);
|
|
$total_profit += $profit;
|
|
$total_cost += $net_cost;
|
|
|
|
fputcsv($output,$line);
|
|
}
|
|
|
|
$query = "UPDATE hotlunch_menu_dates SET order_revenue = $total_revenue, supplier_costs = $total_cost, net_revenue = $profit " .
|
|
"WHERE school_num = $school_num AND record_num = $selected_menu_num";
|
|
db_query($query);
|
|
|
|
$line = array(getLanguageText('TEXT-TOTALS'),'','',$currencySymbol . number_format($total_revenue,2),'','','',$currencySymbol . number_format($total_profit,2));
|
|
fputcsv($output,$line);
|
|
fputcsv($output,array());
|
|
}
|
|
|
|
fclose($output);
|
|
|
|
?>
|