402 lines
13 KiB
PHP
402 lines
13 KiB
PHP
<?php
|
|
$ValidAccess = 2010;
|
|
|
|
require_once("include/globals.php");
|
|
|
|
if (SessionActive() != 1 || ! UserLoggedIn()) {
|
|
header("Location: /");
|
|
exit;
|
|
}
|
|
|
|
$action = "pwyc-details-csv";
|
|
// startLog($action);
|
|
|
|
|
|
// 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: $FormTarget?action=&error_code=NOACCESS");
|
|
return;
|
|
}
|
|
|
|
|
|
// Get parameters
|
|
$include_special_prices = 'N';
|
|
if (array_key_exists('schedule_num',$_GET)) $schedule_num = intval($_GET['schedule_num']);
|
|
if (array_key_exists('include_special_prices',$_GET)) $include_special_prices = $_GET['include_special_prices'];
|
|
if (array_key_exists('alternate_price_items_only',$_GET)) $alternate_price_items_only = $_GET['alternate_price_items_only'];
|
|
|
|
|
|
if (!isset($schedule_num)) {
|
|
header("Location: $FormTarget?action=&error_code=NOSCHED");
|
|
return;
|
|
}
|
|
|
|
|
|
writeLog("include_special_prices: $include_special_prices");
|
|
|
|
|
|
// 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: $FormTarget?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;
|
|
}
|
|
}
|
|
|
|
$IncludePaperOrders = true;
|
|
|
|
$numPaper = db_getVal("SELECT count(*) FROM hotlunch_order_header WHERE school_num = $school_num AND schedule_num = $schedule_num AND source = 'Paper'");
|
|
|
|
$priceCategories = array();
|
|
$query = "SELECT price_category, count(*) FROM hotlunch_order_header WHERE school_num = $school_num AND schedule_num = $schedule_num GROUP BY 1 ORDER BY price_category";
|
|
$results = db_query($query);
|
|
while ($row = db_fetch_assoc($results)) {
|
|
extract($row);
|
|
$priceCategories[] = $price_category;
|
|
writeLog("price_category: $price_category");
|
|
}
|
|
|
|
|
|
|
|
// 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 = hlmd.record_num " .
|
|
"AND hlmd.schedule_num = $schedule_num " .
|
|
"AND hlmi.item_type = 'Item' " .
|
|
"ORDER BY menu_sequence,menu_date,sequence";
|
|
$results = db_query($query);
|
|
|
|
if (db_num_rows($results) == 0) {
|
|
header("Location: $FormTarget?action=$action&error_code=NOMENUITEMS");
|
|
return;
|
|
}
|
|
|
|
$menu_items = array();
|
|
$count = 0;
|
|
$max_alt_prices = 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]['menu_num'] = $menu_num;
|
|
$menu_items[$count]['item_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]['num_alt_prices'] = $number_of_alternate_prices;
|
|
if ($number_of_alternate_prices > $max_alt_prices) $max_alt_prices = $number_of_alternate_prices;
|
|
|
|
$menu_items[$count]['alt_prices'][1] = $alternate_price1;
|
|
$menu_items[$count]['alt_prices'][2] = $alternate_price2;
|
|
$menu_items[$count]['alt_prices'][3] = $alternate_price3;
|
|
$menu_items[$count]['alt_prices'][4] = $alternate_price4;
|
|
$menu_items[$count]['alt_prices'][5] = $alternate_price5;
|
|
$menu_items[$count]['alt_prices'][6] = $alternate_price6;
|
|
$menu_items[$count]['alt_prices'][7] = $alternate_price7;
|
|
$menu_items[$count]['alt_prices'][8] = $alternate_price8;
|
|
$menu_items[$count]['alt_prices'][9] = $alternate_price9;
|
|
$menu_items[$count]['alt_prices'][10] = $alternate_price10;
|
|
|
|
$menu_items[$count]['menu_item_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=pwyc-details.csv");
|
|
|
|
echo("\xEF\xBB\xBF");
|
|
|
|
echo($hotlunch_schedule['schedule_name']);
|
|
echo("\n");
|
|
|
|
$prev_date = "";
|
|
$menuDateTotals = array();
|
|
$grandTotals = array();
|
|
$grandExpectedTotal = 0;
|
|
|
|
for ($i = 0; $i < count($menu_items); $i++) {
|
|
$menu_item = $menu_items[$i];
|
|
extract($menu_item);
|
|
|
|
|
|
if ($alternate_price_items_only == 'Alternate' && $num_alt_prices == 0) continue;
|
|
|
|
|
|
$IncludePaperOrders = true;
|
|
if ($numPaper == 0) $IncludePaperOrders = false;
|
|
if ($online_price == $offline_price) $IncludePaperOrders = false;
|
|
|
|
// Create prices array
|
|
$itemPrices = array();
|
|
$priceHeadings = array();
|
|
|
|
if ($IncludePaperOrders) {
|
|
$itemPrices[] = $offline_price;
|
|
$priceHeadings[] = getLanguageText('TEXT-PAPER');
|
|
}
|
|
|
|
$itemPrices[] = $online_price;
|
|
$priceHeadings[] = getLanguageText('TEXT-ON-LINE');
|
|
|
|
for ($j = 1; $j <= $num_alt_prices; $j++) {
|
|
$itemPrices[] = $alt_prices[$j];
|
|
$priceHeadings[] = getLanguageText('TEXT-ALTERNATE')." $j";
|
|
}
|
|
|
|
if (in_array('Special 1', $priceCategories) && $include_special_prices == 'Y') {
|
|
$itemPrices[] = $special_price1;
|
|
$priceHeadings[] = getLanguageText('TEXT-SPECIAL-1');
|
|
writeLog("priceHeadings: Special 1");
|
|
}
|
|
|
|
if (in_array('Special 2', $priceCategories) && $include_special_prices == 'Y') {
|
|
$itemPrices[] = $special_price2;
|
|
$priceHeadings[] = getLanguageText('TEXT-SPECIAL-2');
|
|
writeLog("priceHeadings: Special 2");
|
|
}
|
|
|
|
if (in_array('Special 3', $priceCategories) && $include_special_prices == 'Y') {
|
|
$itemPrices[] = $special_price3;
|
|
$priceHeadings[] = getLanguageText('TEXT-SPECIAL-3');
|
|
writeLog("priceHeadings: Special 3");
|
|
}
|
|
|
|
|
|
// Output the menu date information
|
|
if ($menu_date != $prev_date) {
|
|
if (count($menuDateTotals) > 0) {
|
|
$menuTotal = 0;
|
|
echo(getLanguageText('TEXT-MENU-DATE-TOTAL').",");
|
|
for ($j = 0; $j < count($menuDateTotals); $j++) {
|
|
echo($school_record['currency_symbol'].csv_number_format($menuDateTotals[$j],2).",");
|
|
$menuTotal += $menuDateTotals[$j];
|
|
}
|
|
|
|
for ($j = count($menuDateTotals); $j < $max_alt_prices; $j++) {
|
|
echo(",");
|
|
}
|
|
// echo($school_record['currency_symbol'].csv_number_format($menuTotal,2).',');
|
|
echo(',');
|
|
|
|
echo(',');
|
|
echo($school_record['currency_symbol'].csv_number_format($menuDateExpectedTotal,2).',');
|
|
|
|
echo($school_record['currency_symbol'].csv_number_format($menuTotal,2).',');
|
|
|
|
$difference = $menuDateExpectedTotal - $menuTotal;
|
|
echo($school_record['currency_symbol'].csv_number_format($difference,2).',');
|
|
|
|
echo("\n");
|
|
echo("----------------------------------------------------------------------------------------------------------");
|
|
echo("\n");
|
|
}
|
|
|
|
echo("\n");
|
|
echo("$menu_title - $menu_date\n");
|
|
$prev_date = $menu_date;
|
|
|
|
$menuDateTotals = array();
|
|
$menuDateExpectedTotal = 0;
|
|
}
|
|
|
|
echo(",");
|
|
echo($item_title);
|
|
echo("\n");
|
|
|
|
$Prices = implode(',',$alt_prices);
|
|
|
|
$query = "SELECT menu_item_price, sum(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 hloh.schedule_num = $schedule_num " .
|
|
"AND hloh.record_num = hloi.order_num " .
|
|
"AND hloh.ordered_by_num = hloi.ordered_by_num " .
|
|
"AND hloh.status IN ('Unpaid','Pending','Paid','Complimentary') " .
|
|
"AND hloh.ordered_by_type != 'Concession' " .
|
|
"AND hloi.schedule_num = $schedule_num " .
|
|
"AND hloi.menu_num = $menu_num " .
|
|
"AND hloi.menu_item_num = $menu_item_num " .
|
|
"AND hloi.active = 'Y' " .
|
|
"GROUP BY hloi.menu_item_price " .
|
|
"ORDER BY hloi.menu_item_price ASC";
|
|
// echo("\"$query\"\n");
|
|
$results = db_query($query);
|
|
|
|
$ItemCounts = array();
|
|
while ($row = db_fetch_array($results)) {
|
|
$price = $row[0];
|
|
$ItemCounts[$price] = $row[1];
|
|
}
|
|
|
|
// Display headings
|
|
echo(",");
|
|
for ($j = 0; $j < count($priceHeadings); $j++) {
|
|
echo($priceHeadings[$j].",");
|
|
}
|
|
echo(",");
|
|
echo(",");
|
|
echo(getLanguageText('TEXT-EXPECTED').',');
|
|
echo(getLanguageText('TEXT-ACTUAL').',');
|
|
echo(getLanguageText('TEXT-DIFFERENCE').',');
|
|
|
|
echo("\n");
|
|
|
|
// Display prices
|
|
echo(getLanguageText('TEXT-PRICE').",");
|
|
for ($j = 0; $j < count($priceHeadings); $j++) {
|
|
echo($school_record['currency_symbol'].csv_number_format($itemPrices[$j],2).",");
|
|
}
|
|
echo("\n");
|
|
|
|
// Display counts
|
|
$Totals = array();
|
|
$CountTotal = 0;
|
|
echo(getLanguageText('TEXT-QUANTITY').",");
|
|
for ($j = 0; $j < count($priceHeadings); $j++) {
|
|
$price = csv_number_format($itemPrices[$j],2);
|
|
$count = 0;
|
|
|
|
if (array_key_exists($price, $ItemCounts)) $count = $ItemCounts[$price];
|
|
echo("$count,");
|
|
$Totals[$j] = $price * $count;
|
|
$CountTotal += $count;
|
|
}
|
|
echo("\n");
|
|
|
|
// Display counts
|
|
echo(getLanguageText('TEXT-ITEM-TOTAL').",");
|
|
$menuItemTotal = 0;
|
|
for ($j = 0; $j < count($priceHeadings); $j++) {
|
|
$menuItemTotal += $Totals[$j];
|
|
$total = $school_record['currency_symbol'].csv_number_format($Totals[$j],2);
|
|
echo("$total,");
|
|
|
|
if (!array_key_exists($j, $menuDateTotals)) $menuDateTotals[$j] = 0;
|
|
if (!array_key_exists($j, $grandTotals)) $grandTotals[$j] = 0;
|
|
$menuDateTotals[$j] += $Totals[$j];
|
|
$grandTotals[$j] += $Totals[$j];
|
|
}
|
|
|
|
for ($j = count($priceHeadings); $j <= $max_alt_prices; $j++) {
|
|
echo(",");
|
|
}
|
|
|
|
echo(",");
|
|
echo(",");
|
|
$expected = $CountTotal * $online_price;
|
|
$menuDateExpectedTotal += $expected;
|
|
$grandExpectedTotal += $expected;
|
|
echo($school_record['currency_symbol'].csv_number_format($expected,2).',');
|
|
|
|
echo($school_record['currency_symbol'].csv_number_format($menuItemTotal,2).',');
|
|
|
|
$difference = $expected - $menuItemTotal;
|
|
echo($school_record['currency_symbol'].csv_number_format($difference,2).',');
|
|
|
|
echo("\n");
|
|
echo("\n");
|
|
}
|
|
|
|
|
|
// Last menu date totals
|
|
if (count($menuDateTotals) > 0) {
|
|
$menuTotal = 0;
|
|
echo(getLanguageText('TEXT-MENU-DATE-TOTAL').",");
|
|
for ($j = 0; $j < count($menuDateTotals); $j++) {
|
|
echo($school_record['currency_symbol'].csv_number_format($menuDateTotals[$j],2).",");
|
|
$menuTotal += $menuDateTotals[$j];
|
|
}
|
|
|
|
for ($j = count($menuDateTotals); $j < $max_alt_prices; $j++) {
|
|
echo(",");
|
|
}
|
|
// echo($school_record['currency_symbol'].csv_number_format($menuTotal,2));
|
|
echo(',');
|
|
|
|
echo(',');
|
|
echo($school_record['currency_symbol'].csv_number_format($menuDateExpectedTotal,2).',');
|
|
|
|
echo($school_record['currency_symbol'].csv_number_format($menuTotal,2).',');
|
|
|
|
$difference = $menuDateExpectedTotal - $menuTotal;
|
|
echo($school_record['currency_symbol'].csv_number_format($difference,2).',');
|
|
|
|
echo("\n");
|
|
echo("----------------------------------------------------------------------------------------------------------");
|
|
echo("\n");
|
|
}
|
|
|
|
|
|
if (count($grandTotals) > 0) {
|
|
$Total = 0;
|
|
echo(getLanguageText('TEXT-SCHEDULE-TOTAL').",");
|
|
for ($j = 0; $j < count($grandTotals); $j++) {
|
|
echo($school_record['currency_symbol'].csv_number_format($grandTotals[$j],2).",");
|
|
$Total += $grandTotals[$j];
|
|
}
|
|
|
|
for ($j = count($grandTotals); $j < $max_alt_prices; $j++) {
|
|
echo(",");
|
|
}
|
|
// echo($school_record['currency_symbol'].csv_number_format($Total,2));
|
|
echo(',');
|
|
|
|
echo(',');
|
|
echo($school_record['currency_symbol'].csv_number_format($grandExpectedTotal,2).',');
|
|
|
|
echo($school_record['currency_symbol'].csv_number_format($Total,2).',');
|
|
|
|
$difference = $grandExpectedTotal - $Total;
|
|
echo($school_record['currency_symbol'].csv_number_format($difference,2).',');
|
|
|
|
echo("\n");
|
|
echo("----------------------------------------------------------------------------------------------------------");
|
|
echo("\n");
|
|
}
|
|
|
|
|
|
function csv_number_format($Number, $decimals) {
|
|
$num = number_format($Number, $decimals);
|
|
$num = str_replace(",","",$num);
|
|
return($num);
|
|
}
|
|
|
|
?>
|