\n"); $error_message = "You are not allowed access to the requested hot lunch schedule"; include "form.messages.php"; return; } } // Check that there are orders for this schedule writeLog("select count(*) from hotlunch_order_header where school_num = $school_num and schedule_num IN ($schedule_num) and ordered_by_type != 'Concession' and status NOT IN ('Inactive','Deleted')"); $num = db_getVal("select count(*) from hotlunch_order_header where school_num = $school_num and schedule_num IN ($schedule_num) and ordered_by_type != 'Concession' and status NOT IN ('Inactive','Deleted')"); writeLog("num: $num"); if ($num == 0) { $action = ""; header("Location: /?action=$action&schedule_num=$schedule_num&error_code=NOORDERS"); return; } $numMenuDates = 1; if (isset($selected_menus) && !is_array($selected_menus)) $selected_menus = array($selected_menus); if (is_array($selected_menus)) { $numMenuDates = count($selected_menus); $firstMenuDate = $selected_menus[0]; $menuNumbers = implode(',',$selected_menus); for ($i = 0; $i < count($selected_menus); $i++) { $curr_menu_num = $selected_menus[$i]; $schedule_num = db_getVal("SELECT schedule_num FROM hotlunch_menu_dates WHERE record_num = $curr_menu_num"); writeLog("schedule_num: $schedule_num curr_menu_num: $curr_menu_num"); include "supplier_orders.post.php"; } $schedule_num = $scheduleNums; } else { if (isset($selected_schedules) && is_array($selected_schedules)) { for ($i = 0; $i < count($selected_schedules); $i++) { $schedule_num = $selected_schedules[$i]; include "supplier_orders.post.php"; } $schedule_num = $scheduleNums; $menuNumbers = $selected_menus; $firstMenuDate = $selected_menus; } else { $menuNumbers = $selected_menus; $schedule_num = db_getVal("SELECT schedule_num FROM hotlunch_menu_dates WHERE record_num = $selected_menus"); } } // Check the class_num value for staff to ensure that every staff person has a valid class number $query = "SELECT * FROM people WHERE school_num = $base_school_num AND class_num IS NULL"; $results = db_query($query); if (db_num_rows($results)) { while ($row = db_fetch_assoc($results)) { $rnum = $row['record_num']; db_query("UPDATE people SET class_num = 0 WHERE school_num = $base_school_num AND class_num IS NULL AND record_num = $rnum"); } } // Get set of suppliers to process $swhere = ""; if (isset($selected_supplier_num) && $selected_supplier_num > 0) $swhere = "and soh.supplier_num = $selected_supplier_num "; $query = "select supplier_name,supplier_num,min(delivery_date) as delivery_date " . "from supplier_order_header as soh, suppliers as s " . "where soh.school_num = $school_num " . "and s.school_num = $supplier_school_num " . "and soh.schedule_num IN ($schedule_num) " . "and soh.menu_num in ($menuNumbers) " . "and soh.supplier_num = s.record_num " . $swhere . "GROUP BY 1,2 " . "order by supplier_name"; writeLog($query); $orders = db_query($query); // Ensure that there are supplier orders to process if (db_num_rows($orders) == 0) { $error_message = getLanguageText('ERRORMSG-NO-ORDERS-FOUND'); $action = "schedule-reports"; header("Location: /?action=$action&naction=$naction&schedule_num=$schedule_num&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message)); exit(); } // 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=supplier-orders.csv"); header('Content-Transfer-Encoding: text'); echo("\xEF\xBB\xBF"); // Process each order while ($order = db_fetch_array($orders)) { extract($order); $order_subtotal = 0; // Get supplier info $query = "select * from suppliers " . "where school_num = $supplier_school_num " . "and record_num = $supplier_num"; $results = db_query($query); $supplier = db_fetch_array($results); extract($supplier); // Get and process the order items $query = "SELECT sp.product_name,supplier_product_num,sum(online_qty) as online_qty,sp.package_qty,package_cost,sp.sequence " . "FROM supplier_order_items as soi, supplier_products as sp " . "WHERE soi.school_num = $school_num " . "AND sp.school_num = $supplier_school_num " . "AND soi.schedule_num IN ($schedule_num) " . "AND soi.menu_num in ($menuNumbers) " . "AND soi.supplier_num = $supplier_num " . "AND soi.supplier_product_num = sp.record_num " . "GROUP BY 1,2,4,5,6 " . "ORDER BY sp.sequence,product_name"; $order_items = db_query($query); $item_count = 0; $delivery_items = array(); $delivery = array(); // Initialize the delivery array $results = db_query("SELECT class_num,sequence FROM hotlunch_schedules_classes AS hlsc, classes AS c " . "WHERE hlsc.school_num = $school_num " . "AND hlsc.schedule_num IN ($schedule_num) " . "AND class_num = c.record_num " . "ORDER BY sequence"); if (db_num_rows($results) == 0) $results = db_query("SELECT record_num AS class_num,sequence FROM classes WHERE school_num = $base_school_num ORDER BY sequence"); while ($row = db_fetch_array($results)) { $num = $row['class_num']; if (!array_key_exists($num, $delivery)) $delivery[$num] = array(); $delivery[$num]['class_num'] = $num; } if (!array_key_exists('Staff', $delivery)) $delivery['Staff'] = array(); if (!array_key_exists('Concession', $delivery)) $delivery['Concession'] = array(); $delivery['Staff']['class_num'] = 0; $delivery['Concession']['class_num'] = 0; $numOrderItems = db_num_rows($order_items); if ($numOrderItems > 0) { $curr_item = 0; while ($row = db_fetch_array($order_items)) { $curr_item++; extract($row); // Get supplier product name $query = "select * from supplier_products " . "where school_num = $supplier_school_num " . "and supplier_num = $supplier_num " . "and record_num = $supplier_product_num"; $results = db_query($query); $row = db_fetch_array($results); $product_name = trim($row['product_name']); $supplier_code = $row['supplier_code']; $package_qty = $row['package_qty']; $package_cost = $row['package_cost']; if (strlen(trim($row['label_name']))) $product_name = trim($row['label_name']); $qty_ordered = intval(($online_qty + $package_qty - 1) / $package_qty); $estimated_cost = $qty_ordered * $package_cost; // Include the supplier code value if required if ($print_supplier_code == 'Before' && strlen($supplier_code) > 0) $product_name = "$supplier_code: $product_name"; if ($print_supplier_code == 'After' && strlen($supplier_code) > 0) $product_name = "$product_name ($supplier_code)"; $order_subtotal += $estimated_cost; if (!array_key_exists($item_count, $delivery_items)) $delivery_items[$item_count] = array(); $delivery_items[$item_count]['name'] = $product_name; $delivery_items[$item_count]['num'] = $supplier_product_num; $delivery_items[$item_count]['pqty'] = $package_qty; // Get totals by division if ($school_record['staff_meals_to_classroom'] == 'Y') { $query = "select division,classes.record_num as class_num,sum(hloi.qty * hlmp.supplier_product_qty) as quantity,classes.sequence " . "from hotlunch_menu_items as hlmi, " . "hotlunch_menu_products as hlmp, " . "supplier_products as sp, " . "hotlunch_order_items as hloi, " . "hotlunch_order_header as hloh, " . "people left join classes on (classes.school_num = $base_school_num and people.school_num = $base_school_num and people.class_num = classes.record_num) " . "where hlmi.school_num = $school_num " . "and hlmp.school_num = $school_num " . "and hloi.school_num = $school_num " . "and hloh.school_num = $school_num " . "and hloh.record_num = hloi.order_num " . "and hloh.status NOT IN ('Inactive','Deleted') " . "and sp.school_num = $supplier_school_num " . "and hlmi.menu_num in ($menuNumbers) " . "and hlmi.record_num = hlmp.menu_item_num " . "and hlmp.supplier_product_num = sp.record_num " . "and hloi.menu_item_num = hlmp.menu_item_num " . "and hloi.active != 'N' " . "and people.school_num = $base_school_num " . "and people.record_num = hloi.ordered_by_num " . "and sp.record_num = $supplier_product_num " . "and people.class_num > 0 and people.class_num is not null " . "group by 1,2,4 " . "order by classes.sequence"; } else { $query = "select division,classes.record_num as class_num,sum(hloi.qty * hlmp.supplier_product_qty) as quantity,classes.sequence " . "from hotlunch_menu_items as hlmi, " . "hotlunch_menu_products as hlmp, " . "supplier_products as sp, " . "hotlunch_order_items as hloi, " . "hotlunch_order_header as hloh, " . "people left join classes on (classes.school_num = $base_school_num and people.school_num = $base_school_num and people.class_num = classes.record_num) " . "where hlmi.school_num = $school_num " . "and hlmp.school_num = $school_num " . "and hloi.school_num = $school_num " . "and hloh.school_num = $school_num " . "and hloh.record_num = hloi.order_num " . "and hloh.status NOT IN ('Inactive','Deleted') " . "and sp.school_num = $supplier_school_num " . "and hlmi.menu_num in ($menuNumbers) " . "and hlmi.record_num = hlmp.menu_item_num " . "and hlmp.supplier_product_num = sp.record_num " . "and hloi.menu_item_num = hlmp.menu_item_num " . "and hloi.active != 'N' " . "and people.record_num = hloi.ordered_by_num " . "and sp.record_num = $supplier_product_num " . "and people.school_num = $base_school_num " . "and people.people_type != 'Staff' " . "and people.class_num > 0 and people.class_num is not null " . "group by 1,2,4 " . "order by classes.sequence"; } $delivery_results = db_query($query); // Save division totals to array while ($row = db_fetch_array($delivery_results)) { $div = $row['division']; $qty = $row['quantity']; $num = $row['class_num']; $index = 'Staff'; $class_info = 'Staff'; if ($num) { $index = $num; } $delivery[$index][$item_count] = $qty; $delivery[$index]['class_num'] = $num; $delivery[$index]['class_info'] = $class_info; $delivery[$index]['div'] = $div; } // Get staff totals if ($school_record['staff_meals_to_classroom'] == 'Y') { $query = "select 'Staff',sp.record_num as product_num,sp.product_name,sp.package_qty,sum(hloi.qty * hlmp.supplier_product_qty) as quantity " . "from hotlunch_menu_items as hlmi, " . "hotlunch_menu_products as hlmp, " . "supplier_products as sp, " . "hotlunch_order_items as hloi, " . "hotlunch_order_header as hloh, " . "people " . "where hlmi.school_num = $school_num " . "and hlmp.school_num = $school_num " . "and sp.school_num = $supplier_school_num " . "and hloi.school_num = $school_num " . "and hloh.school_num = $school_num " . "and hloh.record_num = hloi.order_num " . "and hloh.status NOT IN ('Inactive','Deleted') " . "and hlmi.menu_num in ($menuNumbers) " . "and hlmi.record_num = hlmp.menu_item_num " . "and hlmp.supplier_product_num = sp.record_num " . "and hloi.menu_item_num = hlmp.menu_item_num " . "and hloi.active != 'N' " . "and sp.record_num = $supplier_product_num " . "and people.school_num = $base_school_num " . "and people.record_num = hloi.ordered_by_num " . "and (people.class_num = 0 or people.class_num is null) " . "and people.people_type IN ('Staff','Customer') " . "group by 1,2,3"; } else { $query = "select 'Staff',sp.record_num as product_num,sp.product_name,sp.package_qty,sum(hloi.qty * hlmp.supplier_product_qty) as quantity " . "from hotlunch_menu_items as hlmi, " . "hotlunch_menu_products as hlmp, " . "supplier_products as sp, " . "hotlunch_order_items as hloi, " . "hotlunch_order_header as hloh, " . "people " . "where hlmi.school_num = $school_num " . "and hlmp.school_num = $school_num " . "and sp.school_num = $supplier_school_num " . "and hloi.school_num = $school_num " . "and hloh.school_num = $school_num " . "and hloh.record_num = hloi.order_num " . "and hloh.status NOT IN ('Inactive','Deleted') " . "and hlmi.menu_num in ($menuNumbers) " . "and hlmi.record_num = hlmp.menu_item_num " . "and hlmp.supplier_product_num = sp.record_num " . "and hloi.menu_item_num = hlmp.menu_item_num " . "and hloi.active != 'N' " . "and people.school_num = $base_school_num " . "and people.people_type IN ('Staff','Customer') " . "and sp.record_num = $supplier_product_num " . "and people.record_num = hloi.ordered_by_num " . "group by 1,2,3"; } $delivery_results = db_query($query); // Save staff totals to array $row = db_fetch_array($delivery_results); $qty = 0; if (is_array($row) && array_key_exists('quantity', $row)) $qty = $row['quantity']; $index = 'Staff'; if (!array_key_exists($index, $delivery)) $delivery[$index] = array(); if (!array_key_exists($item_count, $delivery[$index])) $delivery[$index][$item_count] = 0; $delivery[$index][$item_count] += $qty; $delivery[$index]['class_num'] = 'Staff'; $delivery[$index]['class_info'] = 'Staff'; // Get concession totals $query = "select 'Concession',sp.record_num as product_num,sp.product_name,sp.package_qty,sum(hloi.qty * hlmp.supplier_product_qty) as quantity " . "from hotlunch_menu_items as hlmi, " . "hotlunch_menu_products as hlmp, " . "supplier_products as sp, " . "hotlunch_order_items as hloi " . "where hlmi.school_num = $school_num " . "and hlmp.school_num = $school_num " . "and sp.school_num = $supplier_school_num " . "and hloi.school_num = $school_num " . "and hlmi.menu_num in ($menuNumbers) " . "and hlmi.record_num = hlmp.menu_item_num " . "and hlmp.supplier_product_num = sp.record_num " . "and hloi.menu_item_num = hlmp.menu_item_num " . "and hloi.ordered_by_type = 'Concession' " . "and hloi.active != 'N' " . "and sp.record_num = $supplier_product_num " . "group by 1,2,3"; $delivery_results = db_query($query); // Save concession totals to array $row = db_fetch_array($delivery_results); $qty = 0; if (is_array($row) && array_key_exists('quantity', $row)) $qty = $row['quantity']; $index = 'Concession'; if (!array_key_exists($index, $delivery)) $delivery[$index] = array(); if (!array_key_exists($item_count, $delivery[$index])) $delivery[$index][$item_count] = 0; $delivery[$index][$item_count] += $qty; $delivery[$index]['class_num'] = 'Concession'; $delivery[$index]['class_info'] = 'Concession'; $item_count++; } } // Start output for the supplier echo("$supplier_name\n"); $totals = array(); // Output the supplier order values for ($i = 0; $i < count($delivery_items); $i++) { $item_title = str_replace(",","",$delivery_items[$i]['name']); echo("," . $item_title . ""); $totals[$i] = 0; } echo("\n"); // Get set of classes to report on $classes = array(); foreach ($delivery as $k => $v) { $classes[] = $k; } echo("\n"); // Process each class for ($c = 0; $c < count($classes); $c++) { // Print class information $class = $classes[$c]; if ($school_record['i_display_division'] == 'Y') { if (is_numeric($class)) { $class_id = db_getVal("SELECT label_description FROM classes WHERE record_num = $class"); if ((!$class_id || strlen($class_id) == 0) && $school_record['i_display_division'] == 'Y') { $cr = db_query("SELECT * FROM classes WHERE school_num = $school_num AND record_num = $class"); $classRecord = db_fetch_assoc($cr); $class_id = $classRecord['division']; $class_id = $school_record['division_class_num_text'] . " $class_id"; if (strlen($classRecord['grade'])) $class_id = $classRecord['grade'] . " - $class_id"; } } else { $class_id = $class; } } else { if ($class != 'Staff' && $class != 'Concession') { $class_id = db_getVal("SELECT label_description FROM classes WHERE record_num = $class"); if (!$class_id || strlen($class_id) == 0) $class_id = getClassInfo($class,'internal',0); } else { $class_id = $class; } } echo("$class_id"); // Output each classes amounts for ($i = 0; $i < count($delivery_items); $i++) { $qty = 0; if (array_key_exists($class, $delivery) && array_key_exists($i, $delivery[$class])) $qty = number_format($delivery[$class][$i],0); echo(",$qty"); $totals[$i] += $qty; } echo("\n"); } // Output each classes amounts echo("TOTALS:"); for ($i = 0; $i < count($delivery_items); $i++) { $qty = number_format($totals[$i],0); echo(",$qty"); } echo("\n"); // Output package quantities echo("PACKAGE QTY:"); for ($i = 0; $i < count($delivery_items); $i++) { $qty = number_format($delivery_items[$i]['pqty'],0); echo(",$qty"); } echo("\n"); // Calculate supplier quantity echo("SUPPLIER QTY:"); for ($i = 0; $i < count($delivery_items); $i++) { $total = $totals[$i]; $pqty = $delivery_items[$i]['pqty']; $sqty = intval(($total + $pqty - 1) / $pqty); echo(",$sqty"); } echo("\n"); // Calculate surplus quantity echo("SURPLUS QTY:"); for ($i = 0; $i < count($delivery_items); $i++) { $total = $totals[$i]; $pqty = $delivery_items[$i]['pqty']; $sqty = intval(($total + $pqty - 1) / $pqty); $extra = ($pqty * $sqty) - $total; echo(",$extra"); } echo("\n"); echo("\n\n"); } echo("\n"); ?>