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