\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 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 " . "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]['num'] = $record_num; $menu_items[$count]['grandtotal'] = 0; $menu_items[$count]['price'] = $online_price; $menu_items[$count]['paper_price'] = $offline_price; $menu_items[$count]['special_price1'] = $special_price1; $menu_items[$count]['special_price2'] = $special_price2; $menu_items[$count]['special_price3'] = $special_price3; $query = "SELECT sum(package_cost / package_qty) FROM supplier_products,hotlunch_menu_products " . "WHERE hotlunch_menu_products.school_num = $school_num " . "AND supplier_products.school_num = $supplier_school_num " . "AND hotlunch_menu_products.menu_item_num = $record_num " . "AND supplier_products.record_num = hotlunch_menu_products.supplier_product_num"; $cost = round(100 * floatval(db_getVal($query)),0) / 100; $menu_items[$count]['cost'] = $cost; $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=revenue.csv"); header('Content-Transfer-Encoding: text'); echo("\xEF\xBB\xBF"); // // Output the menu title and dates // if ($mode == 'detail') echo(",,"); $prev_menu_title = ""; for ($i = 0; $i < count($menu_items); $i++) { echo(","); if ($menu_items[$i]['menu_title'] != $prev_menu_title) echo('"' . $menu_items[$i]['menu_title'] . '"'); $prev_menu_title = $menu_items[$i]['menu_title']; } echo("\n"); if ($mode == 'detail') echo(",,"); $prev_menu_date = ""; for ($i = 0; $i < count($menu_items); $i++) { echo(","); if ($menu_items[$i]['menu_date'] != $prev_menu_date) echo('"' . $menu_items[$i]['menu_date'] . '"'); $prev_menu_date = $menu_items[$i]['menu_date']; } echo("\n"); // // Output the menu items titles // if ($mode == 'detail') echo("\"Name\",$school_record[division_class_num_text],Type"); else echo($school_record['division_class_num_text']); for ($i = 0; $i < count($menu_items); $i++) { echo(","); echo('"' . $menu_items[$i]['title'] . '"'); } echo(",Total,Profit"); echo("\n"); // Get list of classes $query = "select c.*,program_name from classes as c left join class_programs as cp on c.class_program = cp.record_num " . "where c.school_num = $school_num " . "order by sequence"; $cresults = db_query($query); $processed_staff = array(); $division_totals = array(); while ($class = db_fetch_array($cresults)) { $class_num = $class['record_num']; $class_info = getClassInfo($class_num); // Get classroom orders for this menu date $query = "select distinct concat_ws(', ',st.last_name,st.first_name) as name,people_type as ptype,hloh.status as status,hloh.ordered_by_num,source,price_category " . "from people as st, hotlunch_order_header as hloh, hotlunch_order_items as hloi " . "where st.school_num = $base_school_num " . "and hloh.school_num = $school_num " . "and hloi.school_num = $school_num " . "and st.record_num = hloh.ordered_by_num " . "and st.record_num = hloi.ordered_by_num " . "and st.class_num = $class_num " . "and hloh.schedule_num = $schedule_num " . "AND hloh.status NOT IN ('Inactive','Deleted') " . "and hloi.schedule_num = $schedule_num " . "and hloi.menu_num in ($selected_menu_num) " . "and hloi.active != 'N' " . "and hloh.record_num = hloi.order_num " . "order by ptype desc,name"; $people = db_query($query); // Process each person if (db_num_rows($people) > 0) { $division_totals[$class_num]['info'] = $class_info; $division_totals[$class_num]['profit'] = 0; while ($person = db_fetch_array($people)) { extract($person); $ordered_by_type = $ptype; $ptotal = 0; if ($mode == 'detail') { echo('"' . $name . '"'); echo(","); echo('"' . $class_info . '"'); echo(","); echo('"' . $ordered_by_type . '"'); echo(","); } if ($ordered_by_type == 'Staff') $processed_staff[] = $ordered_by_num; // Get item totals for each person for ($i = 0; $i < count($menu_items); $i++) { $menu_item_num = intval($menu_items[$i]['num']); $price = $menu_items[$i]['price']; $cost = $menu_items[$i]['cost']; if ($price_category == 'Regular') { if ($source == 'On-Line') $price = $menu_items[$i]['price']; else $price = $menu_items[$i]['paper_price']; } else { if ($price_category == 'Special 1') $price = $menu_items[$i]['special_price1']; if ($price_category == 'Special 2') $price = $menu_items[$i]['special_price2']; if ($price_category == 'Special 3') $price = $menu_items[$i]['special_price3']; } // Lookup to see if this person has ordered this item $query = "select qty from hotlunch_order_items " . "where school_num = $school_num " . "and ordered_by_num = $ordered_by_num " . "and schedule_num = $schedule_num " . "and menu_item_num = $menu_item_num " . "and active != 'N'"; $mresults = db_query($query); $qty = 0; $net = 0; if (db_num_rows($mresults) == 1) { $mrow = db_fetch_array($mresults); $qty = $mrow['qty']; $net = $qty * $price; $pnet = 0; $ptotal += $net; if ($mode == 'detail') echo('"' . $currencySymbol . number_format($net,2) . '"'); if (!array_key_exists($i, $division_totals[$class_num])) $division_totals[$class_num][$i] = 0; if (!array_key_exists('profit', $division_totals[$class_num])) $$division_totals[$class_num]['profit'] = 0; $division_totals[$class_num][$i] += $net; $division_totals[$class_num]['profit'] += $qty * ($price - $cost); } if ($mode == 'detail') echo(","); } if ($mode == 'detail') echo('"' . $currencySymbol . number_format($ptotal,2) . '"' . "\n"); } } } // // Get other staff orders data // $pwhere = ""; if (count($processed_staff) > 0) $pwhere = "and s.record_num not in (" . implode(",",$processed_staff) . ") "; $query = "select concat_ws(', ',s.last_name,s.first_name) as name,'Staff' as ptype,concat('$currencySymbol',hloh.order_total) as total," . "hloh.status as status,hloh.ordered_by_num,source,price_category " . "from people as s, hotlunch_order_header as hloh, hotlunch_order_items as hloi " . "where s.school_num = $base_school_num " . "and hloh.school_num = $school_num " . "and hloi.school_num = $school_num " . "and s.record_num = hloh.ordered_by_num " . "and s.record_num = hloi.ordered_by_num " . "and s.status != 'Inactive' " . "and s.people_type = 'Staff' " . "AND hloh.status NOT IN ('Inactive','Deleted') " . "and hloh.schedule_num = $schedule_num " . "and hloi.menu_num in ($selected_menu_num) " . "and hloi.active != 'N' " . "and hloh.record_num = hloi.order_num " . $pwhere . "order by ptype,name"; $people = db_query($query); $division_totals['Staff']['info'] = 'Staff'; $division_totals['Staff']['profit'] = 0; // Process each person while ($person = db_fetch_array($people)) { extract($person); $ordered_by_type = $ptype; $ptotal = 0; if ($mode == 'detail') { echo('"' . $name . '"'); echo(","); echo(","); echo('"' . $ordered_by_type . '"'); echo(","); } // Get item totals for each person for ($i = 0; $i < count($menu_items); $i++) { $menu_item_num = intval($menu_items[$i]['num']); $price = $menu_items[$i]['price']; $cost = $menu_items[$i]['cost']; if ($price_category == 'Regular') { if ($source == 'On-Line') $price = $menu_items[$i]['price']; else $price = $menu_items[$i]['paper_price']; } else { if ($price_category == 'Special 1') $price = $menu_items[$i]['special_price1']; if ($price_category == 'Special 2') $price = $menu_items[$i]['special_price2']; if ($price_category == 'Special 3') $price = $menu_items[$i]['special_price3']; } // Lookup to see if this person has ordered this item $query = "select qty from hotlunch_order_items " . "where school_num = $school_num " . "and ordered_by_num = $ordered_by_num " . "and schedule_num = $schedule_num " . "and menu_num in ($selected_menu_num) " . "and menu_item_num = $menu_item_num " . "and active != 'N'"; $mresults = db_query($query); $qty = 0; $net = 0; if (db_num_rows($mresults) == 1) { $mrow = db_fetch_array($mresults); $qty = $mrow['qty']; $net = $qty * $price; $ptotal += $net; if ($mode == 'detail') echo('"' . $currencySymbol . number_format($net,2) . '"'); if (!array_key_exists($i, $division_totals['Staff'])) $division_totals['Staff'][$i] = 0; $division_totals['Staff'][$i] += $net; $division_totals['Staff']['profit'] += $qty * ($price - $cost); } if ($mode == 'detail') echo(","); } if ($mode == 'detail') echo('"' . $currencySymbol . number_format($ptotal,2) . '"' . "\n"); } // // Output the division totals // if ($mode == 'detail') { echo("\n"); // Output the menu items titles echo("Name,$school_record[division_class_num_text],"); for ($i = 0; $i < count($menu_items); $i++) { echo(","); echo($menu_items[$i]['title']); } echo(",Total\n"); } $grand_totals = array(); $total_profit = 0; foreach ($division_totals as $key => $value) { $class_num = $key; $class_info = $division_totals[$class_num]['info']; $ctotal = 0; if ($mode == 'detail') echo("Total,$class_info,"); else echo("$class_info"); for ($i = 0; $i < count($menu_items); $i++) { echo(","); $dtotal = 0; if (array_key_exists($class_num, $division_totals) && array_key_exists($i, $division_totals[$class_num])) $dtotal = $division_totals[$class_num][$i]; echo('"' . $currencySymbol . number_format($dtotal,2) . '"'); if (!array_key_exists($i, $grand_totals)) $grand_totals[$i] = 0; $grand_totals[$i] += $dtotal; $ctotal += $dtotal; } $profit = $division_totals[$class_num]['profit']; $total_profit += $profit; echo(',"' . $currencySymbol . number_format($ctotal,2) . '","' . $currencySymbol . number_format($profit,2) . '"' . "\n"); } // // Output school totals // if ($mode == 'detail') echo("School,,"); else echo("School"); $total = 0; for ($i = 0; $i < count($menu_items); $i++) { echo(","); echo('"' . $currencySymbol . number_format($grand_totals[$i],2) . '"'); $total += $grand_totals[$i]; } echo(',"' . $currencySymbol . number_format($total,2) . '","' . $currencySymbol . number_format($total_profit,2) . '"' . "\n"); ?>