\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"); ?>