415 lines
14 KiB
PHP
415 lines
14 KiB
PHP
<?php
|
|
$ValidAccess = 2010;
|
|
|
|
require_once("include/globals.php");
|
|
|
|
if (SessionActive() != 1 || ! UserLoggedIn()) {
|
|
header("Location: /");
|
|
exit;
|
|
}
|
|
|
|
$module = "hotlunch.sales-summary.csv";
|
|
// if ($_SESSION['UserInfo']['type'] == 'SuperUser') startLog($module);
|
|
|
|
|
|
// 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=&error_code=NOACCESS");
|
|
return;
|
|
}
|
|
|
|
|
|
// Get parameters
|
|
$report_include_items = 'Flagged';
|
|
if (array_key_exists('schedule_num',$_GET)) $schedule_num = intval($_GET['schedule_num']);
|
|
if (array_key_exists('report_start_date',$_GET)) $start_date = db_escape_string($_GET['report_start_date']);
|
|
if (array_key_exists('report_end_date',$_GET)) $end_date = db_escape_string($_GET['report_end_date']);
|
|
if (array_key_exists('report_include_items',$_GET)) $report_include_items = db_escape_string($_GET['report_include_items']);
|
|
|
|
$include_special1 = 'N';
|
|
$include_special2 = 'N';
|
|
$include_special3 = 'N';
|
|
if (array_key_exists('include_special1',$_GET)) $include_special1 = db_escape_string($_GET['include_special1']);
|
|
if (array_key_exists('include_special2',$_GET)) $include_special2 = db_escape_string($_GET['include_special2']);
|
|
if (array_key_exists('include_special3',$_GET)) $include_special3 = db_escape_string($_GET['include_special3']);
|
|
|
|
$pwhere = "'Regular'";
|
|
if ($include_special1 == 'Y') $pwhere .= ",'Special 1'";
|
|
if ($include_special2 == 'Y') $pwhere .= ",'Special 2'";
|
|
if ($include_special3 == 'Y') $pwhere .= ",'Special 3'";
|
|
$pwhere = "AND hloh.price_category IN ($pwhere) ";
|
|
|
|
|
|
if (!isset($schedule_num)) {
|
|
header("Location: /?action=&error_code=NOSCHED");
|
|
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 = 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 = getLanguageText('ERRORMSG-NO-SCHEDULE-ACCESS');
|
|
include "form.messages.php";
|
|
return;
|
|
}
|
|
}
|
|
|
|
|
|
if (!isset($start_date)) $start_date = db_getVal("SELECT min(menu_date) FROM hotlunch_menu_dates WHERE school_num = $school_num AND schedule_num = $schedule_num");
|
|
if (!isset($end_date)) $end_date = db_getVal("SELECT max(menu_date) FROM hotlunch_menu_dates WHERE school_num = $school_num AND schedule_num = $schedule_num");
|
|
|
|
|
|
// writeLog("start_date: $start_date");
|
|
// writeLog("end_date: $end_date");
|
|
|
|
|
|
$iwhere = "";
|
|
if ($report_include_items == 'Flagged') $iwhere = "AND sales_summary_item = 'Y' ";
|
|
|
|
// 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' " .
|
|
"AND menu_date >= '$start_date' " .
|
|
"AND menu_date <= '$end_date' " .
|
|
$iwhere .
|
|
"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;
|
|
$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]['menu_item_num'] = $record_num;
|
|
$count++;
|
|
}
|
|
|
|
$cols = count($menu_items) + 1;
|
|
|
|
//
|
|
// Determine if there are any discounted items
|
|
//
|
|
$query = "SELECT count(*) FROM hotlunch_order_items AS hloi, hotlunch_order_header AS hloh, hotlunch_menu_items AS hlmi " .
|
|
"WHERE hloi.school_num = $school_num " .
|
|
"AND hloh.school_num = $school_num " .
|
|
"AND hlmi.school_num = $school_num " .
|
|
"AND hloh.schedule_num = $schedule_num " .
|
|
"AND hloh.record_num = hloi.order_num " .
|
|
"AND hlmi.record_num = hloi.menu_item_num " .
|
|
"AND hloh.ordered_by_num = hloi.ordered_by_num " .
|
|
"AND hloh.ordered_by_type = 'Student' " .
|
|
"AND hloh.status IN ('Unpaid','Pending','Paid','Complimentary') " .
|
|
"AND hloh.ordered_by_type != 'Concession' " .
|
|
$pwhere .
|
|
"AND hloi.schedule_num = $schedule_num " .
|
|
"AND (hloi.menu_item_price != hlmi.online_price OR (hloi.menu_item_price * hloi.qty) != hloi.extended_price) " .
|
|
"AND hloi.active = 'Y'";
|
|
// writeLog($query);
|
|
$itemCount = db_getVal($query);
|
|
|
|
|
|
// 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=hotlunch.sales-summary.csv");
|
|
|
|
echo("\xEF\xBB\xBF");
|
|
|
|
|
|
echo($hotlunch_schedule['schedule_name']);
|
|
echo("\n");
|
|
|
|
$prev_date = "";
|
|
$totalMeals = 0;
|
|
$totalSales = 0;
|
|
$totalDiscount = 0;
|
|
$menuTotals = array('qty' => 0, 'sales' => 0, 'discount' => 0);
|
|
$categories = array('Regular', 'PWYC', 'Special 1', 'Special 2', 'Special 3');
|
|
$catTotals = array('Regular' => array(0, 0),
|
|
'PWYC' => array(0, 0),
|
|
'Special 1' => array(0, 0),
|
|
'Special 2' => array(0, 0),
|
|
'Special 3' => array(0, 0),
|
|
);
|
|
|
|
$aTitle = getLanguageText('TEXT-ACTUAL');
|
|
$qTitle = getLanguageText('TEXT-QUANTITY');
|
|
$cTitle = getLanguageText('TEXT-CATEGORY');
|
|
$pTitle = getLanguageText('TEXT-ITEM-PRICE');
|
|
$sTitle = getLanguageText('TEXT-DISCOUNT');
|
|
$tTitle = getLanguageText('TEXT-TOTAL-DISCOUNT');
|
|
$salesTitle = getLanguageText('TEXT-SALES');
|
|
|
|
for ($i = 0; $i < count($menu_items); $i++) {
|
|
$menu_item = $menu_items[$i];
|
|
extract($menu_item);
|
|
|
|
if ($menu_date != $prev_date) {
|
|
if (strlen($prev_date)) {
|
|
// Print menu totals
|
|
$title = getLanguageText('TEXT-MENU-TOTAL');
|
|
$qty = $menuTotals['qty'];
|
|
$sales = '$'.number_format($menuTotals['sales'],2);
|
|
$discount = '$'.number_format($menuTotals['discount'],2);
|
|
if ($itemCount > 0) {
|
|
echo(",$title,,$qty,,,,,\"$sales\",,,\"$discount\"\n");
|
|
}
|
|
else {
|
|
echo(",$title,,$qty,,,,\"$sales\"\n");
|
|
}
|
|
echo("\n\n");
|
|
}
|
|
|
|
if ($itemCount > 0) {
|
|
echo("$menu_title,$menu_date,$pTitle,$qTitle,,$cTitle,,$aTitle,$salesTitle,,$sTitle,$tTitle\n");
|
|
}
|
|
else {
|
|
echo("$menu_title,$menu_date,$pTitle,$qTitle,,$cTitle,,$salesTitle\n");
|
|
}
|
|
|
|
$prev_date = $menu_date;
|
|
$menuTotals = array('qty' => 0, 'sales' => 0, 'discount' => 0);
|
|
}
|
|
|
|
|
|
$query = "SELECT menu_item_price, price_category, 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') " .
|
|
$pwhere .
|
|
"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, price_category " .
|
|
"ORDER BY price_category,hloi.menu_item_price DESC";
|
|
// writeLog($query);
|
|
$results = db_query($query);
|
|
|
|
$count = 0;
|
|
while ($row = db_fetch_array($results)) {
|
|
$price = $row[0];
|
|
$cat = $row[1];
|
|
$qty = $row[2];
|
|
|
|
writeLog("$item_title :: $price :: $cat :: $qty");
|
|
|
|
if ($cat == '') $cat = 'Regular';
|
|
|
|
$total = $price * $qty;
|
|
$difference = $online_price - $price;
|
|
$discount = $difference * $qty;
|
|
if ($difference < 0) {
|
|
$difference = 0;
|
|
$discount = 0;
|
|
}
|
|
|
|
$pamt = '$'.number_format($price, 2);
|
|
$oamt = '$'.number_format($online_price, 2);
|
|
$damt = '$'.number_format($difference, 2);
|
|
$samt = '$'.number_format($discount, 2);
|
|
$tamt = '$'.number_format($total, 2);
|
|
|
|
$menuTotals['sales'] += $total;
|
|
$menuTotals['discount'] += $discount;
|
|
|
|
$totalDiscount += $discount;
|
|
$totalSales += $total;
|
|
|
|
$rCat = $cat;
|
|
if ($cat == 'Regular' && $price != $online_price) {
|
|
$rCat = 'PWYC';
|
|
$cat = getLanguageText('TEXT-PAY-WHAT-YOU-CAN');
|
|
}
|
|
else {
|
|
switch ($cat) {
|
|
case 'Special 1':
|
|
$cat = $Special1_Title;
|
|
break;
|
|
case 'Special 2':
|
|
$cat = $Special2_Title;
|
|
break;
|
|
case 'Special 3':
|
|
$cat = $Special3_Title;
|
|
break;
|
|
}
|
|
}
|
|
|
|
$menuTotals['qty'] += $qty;
|
|
$totalMeals += $qty;
|
|
// writeLog("Cat: $rCat $total");
|
|
$catTotals[$rCat][0] += $qty;
|
|
$catTotals[$rCat][1] += $total;
|
|
$item_title = str_replace('"',"\"\"",$item_title);
|
|
|
|
if ($itemCount > 0) {
|
|
echo(",\"$item_title\",\"$oamt\",\"$qty\",,\"$cat\",,\"$pamt\",\"$tamt\",,\"$damt\",\"$samt\"\n");
|
|
}
|
|
else {
|
|
echo(",\"$item_title\",\"$oamt\",\"$qty\",,\"$cat\",,\"$tamt\"\n");
|
|
}
|
|
$count++;
|
|
}
|
|
|
|
if ($count > 1) echo("\n");
|
|
// echo("\n");
|
|
}
|
|
|
|
// Print menu totals
|
|
$title = getLanguageText('TEXT-MENU-TOTAL');
|
|
$qty = $menuTotals['qty'];
|
|
$sales = '$'.number_format($menuTotals['sales'],2);
|
|
$discount = '$'.number_format($menuTotals['discount'],2);
|
|
if ($itemCount > 0) {
|
|
echo(",$title,,$qty,,,,,\"$sales\",,,\"$discount\"\n");
|
|
}
|
|
else {
|
|
echo(",$title,,$qty,,,\"$sales\"\n");
|
|
}
|
|
echo("\n\n");
|
|
|
|
|
|
// Grand totals
|
|
$tTotal = '$'.number_format($totalSales, 2);
|
|
$sTotal = '$'.number_format($totalDiscount, 2);
|
|
echo("\n");
|
|
|
|
$salesTitle = getLanguageText('TEXT-TOTAL-SALES');
|
|
if ($totalDiscount > 0) {
|
|
$sTitle = getLanguageText('TEXT-TOTALS');
|
|
echo(",,,$qTitle,,,,,$salesTitle,,,$tTitle\n");
|
|
echo(",$sTitle,,$totalMeals,,,,,\"$tTotal\",,,\"$sTotal\"\n");
|
|
}
|
|
else {
|
|
$sTitle = getLanguageText('TEXT-TOTAL');
|
|
echo(",,,$qTitle,,,,$salesTitle\n");
|
|
echo(",$sTitle,,$totalMeals,,,,,\"$tTotal\"\n");
|
|
}
|
|
|
|
echo("\n\n");
|
|
// writeLog(print_r($catTotals, true));
|
|
|
|
|
|
// Include category totals
|
|
for ($i = 0; $i < count($categories); $i++) {
|
|
$cat = $categories[$i];
|
|
$qty = $catTotals[$cat][0];
|
|
$total = '$'.number_format($catTotals[$cat][1],2);
|
|
switch ($cat) {
|
|
case 'Regular':
|
|
$title = getLanguageText('TEXT-REGULAR');
|
|
break;
|
|
|
|
case 'PWYC':
|
|
$title = getLanguageText('TEXT-PAY-WHAT-YOU-CAN');
|
|
break;
|
|
|
|
case 'Special 1':
|
|
$title = $Special1_Title;
|
|
break;
|
|
case 'Special 2':
|
|
$title = $Special2_Title;
|
|
break;
|
|
case 'Special 3':
|
|
$title = $Special3_Title;
|
|
break;
|
|
}
|
|
|
|
if ($qty > 0) echo(",$title,$qty,\"$total\",\"($cat)\"\n");
|
|
}
|
|
echo("\n\n");
|
|
|
|
// Get PWYC Orders
|
|
if ($itemCount > 0) {
|
|
$query = "SELECT distinct hloh.record_num as order_num, hloh.ordered_by_first_name, hloh.ordered_by_last_name, hloh.order_total, hloh.status, hloh.price_category " .
|
|
"FROM hotlunch_order_items AS hloi, hotlunch_order_header AS hloh, hotlunch_menu_items AS hlmi " .
|
|
"WHERE hloi.school_num = $school_num " .
|
|
"AND hloh.school_num = $school_num " .
|
|
"AND hlmi.school_num = $school_num " .
|
|
"AND hloh.schedule_num = $schedule_num " .
|
|
"AND hloh.record_num = hloi.order_num " .
|
|
"AND hlmi.record_num = hloi.menu_item_num " .
|
|
"AND hloh.ordered_by_num = hloi.ordered_by_num " .
|
|
"AND hloh.ordered_by_type = 'Student' " .
|
|
"AND hloh.status IN ('Unpaid','Pending','Paid','Complimentary') " .
|
|
"AND hloh.ordered_by_type != 'Concession' " .
|
|
"AND hloi.schedule_num = $schedule_num " .
|
|
"AND (hloi.menu_item_price != hlmi.online_price OR (hloi.menu_item_price * hloi.qty) != hloi.extended_price) " .
|
|
"AND hloi.active = 'Y' " .
|
|
"ORDER BY ordered_by_last_name, ordered_by_first_name";
|
|
// writeLog($query);
|
|
$pwycOrders = db_query($query);
|
|
|
|
if (db_num_rows($pwycOrders)) {
|
|
$title = getLanguageText('TEXT-DISCOUNTED-ORDERS');
|
|
echo("$title\n");
|
|
|
|
$oTitle = getLanguageText('TEXT-ORDER-NUMBER');
|
|
$fTitle = getLanguageText('TEXT-FIRST-NAME');
|
|
$lTitle = getLanguageText('TEXT-LAST-NAME');
|
|
$tTitle = getLanguageText('TEXT-ORDER-TOTAL');
|
|
$sTitle = getLanguageText('TEXT-STATUS');
|
|
$cTitle = getLanguageText('TEXT-CATEGORY');
|
|
echo("$oTitle,$fTitle,$lTitle,$tTitle,$sTitle,$cTitle\n");
|
|
|
|
while ($row = db_fetch_assoc($pwycOrders)) {
|
|
extract($row);
|
|
$order_total = '$'.number_format($order_total,2);
|
|
echo("$order_num,$ordered_by_first_name,$ordered_by_last_name,\"$order_total\",$status,$price_category\n");
|
|
}
|
|
}
|
|
}
|
|
|
|
function csv_number_format($Number, $decimals) {
|
|
$num = number_format($Number, $decimals);
|
|
$num = str_replace(",","",$num);
|
|
return($num);
|
|
}
|
|
|
|
?>
|