\n"); $error_message = getLanguageText('ERRORMSG-NO-SCHEDULE-ACCESS'); include "form.messages.php"; exit(); } } 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"); $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) { if (!isset($action)) $action = ''; header("Location: /?action=$action&error_code=NOMENUITEMS"); exit(); } $menu_items = array(); $menuCount = 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++; if (!array_key_exists($menu_num, $menuCount)) $menuCount[$menu_num] = 0; $menuCount[$menu_num]++; } $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.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 " . "AND hloi.active = 'Y'"; $itemCount = db_getVal($query); // // Setup the PDF // $document_font = $school_record['document_font']; if (strlen($document_font) == 0) $document_font = "Helvetica"; $document_font_weight = ""; $document_text_color = "#000000"; $document_font_size = 9; $left_col = 5; $middle_col = 100; $title_width = 260; // Set parameters for this document $max_rows = 180; $nWidth = 20; $columns[] = 5; $columns[] = $columns[0] + 80; $columns[] = $columns[1] + $nWidth; $columns[] = $columns[2] + $nWidth; $columns[] = $columns[3] + intval($nWidth / 3); $columns[] = $columns[4] + $nWidth; $columns[] = $columns[5] + intval($nWidth / 3); $columns[] = $columns[6] + $nWidth; $columns[] = $columns[7] + $nWidth; $columns[] = $columns[8] + $nWidth; $columns[] = $columns[9] + $nWidth; $columns[] = $columns[10] + $nWidth; $columns[] = $columns[11] + $nWidth; $start_row = 5; $incr = 4.0; $logo_url = $school_record['logo_url']; $logo_image = "images/" . $school_record['logo_image']; $logo_row = $max_rows + 5; $report_title = $schedule_name; $document_name = "hotlunch.sales-summary.pdf"; $mode = 'Normal'; // // Setup PDF document // require("tcpdf/config/lang/eng.php"); require("tcpdf/tcpdf.php"); class PDF extends TCPDF { function Header() { global $incr, $left_col, $start_row, $title_width, $document_font, $document_font_size, $report_title, $columns, $alignment, $headings, $curr_row; global $school_name; $this->SetFont($document_font,"B",$document_font_size + 1); $this->SetFillColor(hexdec("bb"),hexdec("bb"),hexdec("bb")); // Report title $this->SetXY($left_col,$start_row); $this->Cell($title_width,$incr,$report_title,'0',1,'L',true); // School Name $this->SetXY($left_col + 150,$start_row); $this->Cell($title_width - 150,$incr,$school_name,'0',1,'R',true); } function Footer() { global $incr, $left_col, $title_width, $max_rows, $document_font, $document_font_size, $document_name, $logo_image, $logo_url, $logo_row, $school_name; // Print the date $this->SetFont($document_font,"",$document_font_size - 1); $this->SetXY($left_col,$logo_row); $date = dateString('datetime'); $this->Cell($title_width,$incr - 1,"$document_name: $date",'0',1,'R'); $this->SetXY($left_col,$logo_row + $incr); $this->Cell($title_width,$incr - 1,"$school_name",'0',1,'R'); $this->Image($logo_image,10,$logo_row, 50); $this->SetXY(20,$logo_row + 9); $this->SetFont($document_font,"B",$document_font_size); $this->Cell($title_width,$incr,$logo_url,'0',1,'L'); } } // Force exact physical size for Letter: 8.5 x 11 inches => 279.4mm x 215.9mm (landscape) $pageFormat = array(279.4, 215.9); $pdf= new PDF('L', 'mm', $pageFormat, true, 'UTF-8', false); // set default monospaced font $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED); //set margins $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT); //set auto page breaks $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM); //set image scale factor $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO); $font_red = hexdec("00"); $font_green = hexdec("00"); $font_blue = hexdec("00"); $pdf->SetTextColor($font_red,$font_green,$font_blue); $pdf->SetFillColor(hexdec("bb"),hexdec("bb"),hexdec("bb")); $pdf->SetFont($document_font,"",$document_font_size); $curr_row = $max_rows; $school_name = $school_record['school_name']; $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'); $Titles = array( '', $pTitle, $qTitle, '', $cTitle, '', $aTitle, $salesTitle, ); $tTitles = array( '', '', $qTitle, '', '', '', '', $salesTitle, ); $lastCol = 7; if ($itemCount > 0) { $Titles[] = ''; $Titles[] = $sTitle; $Titles[] = $tTitle; $tTitles[] = ''; $tTitles[] = ''; $tTitles[] = $tTitle; $lastCol = 10; } // Each menu item $prev_num = 0; $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), ); $pdf->SetFont($document_font,"",$document_font_size); $pdf->SetFillColor(hexdec("dd"),hexdec("dd"),hexdec("dd")); $line_count = 0; $curr_row = $max_rows + 1; for ($m = 0; $m < count($menu_items); $m++) { $menu_item = $menu_items[$m]; extract($menu_item); if ($menu_num != $prev_num) { $mCount = $menuCount[$menu_num]; // writeLog("mCount: $mCount"); // Print menu totals if ($prev_num != 0) { $curr_row += $incr / 2; $pdf->SetFont($document_font,"B",$document_font_size); $col = $columns[0]; $width = $columns[$lastCol + 1] - $col; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,"",'0',1,$align,true,'',1); $title = getLanguageText('TEXT-MENU-TOTAL'); $qty = $menuTotals['qty']; $sales = '$'.number_format($menuTotals['sales'],2); $discount = '$'.number_format($menuTotals['discount'],2); $align = 'R'; $i = 0; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$title,'0',1,$align,false,'',1); $i = 2; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 8; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$qty,'0',1,$align,false,'',1); $i = 7; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$sales,'0',1,$align,false,'',1); if ($itemCount > 0) { $i = 10; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$discount,'0',1,$align,false,'',1); } $pdf->SetFont($document_font,"",$document_font_size); $curr_row += $incr + 2; } if ($curr_row > ($max_rows - ($incr * $mCount * 3) - $incr * 5)) { $pdf->addPage(); $curr_row = $start_row + $incr * 2; } else $curr_row = $curr_row + $incr * 2; $pdf->SetFont($document_font,"B",$document_font_size + 1); $col = 5; $width = 25; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$menu_date,'0',1,'L',true); $col = 30; $width = 120; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$menu_title,'0',1,'L',true); $curr_row += $incr + 2; // writeLog("Count Titles: ".count($Titles)); $pdf->SetFont($document_font,"B",$document_font_size); for ($i = 1; $i < count($Titles); $i++) { $title = $Titles[$i]; // writeLog($title); $col = $columns[$i]; $ncol = $i + 1; $width = $columns[$ncol] - $col - 3; // writeLog("col: $col width: $width"); $align = 'R'; if ($i == 0) $align = 'L'; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$title,'0',1,$align,false,'',1); } $prev_num = $menu_num; $menuTotals = array('qty' => 0, 'sales' => 0, 'discount' => 0); $pdf->SetFont($document_font,"",$document_font_size); $curr_row += $incr + 2; } // Get menu item details $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'); // $cat = getLanguageText('TEXT-DISCOUNTED'); } 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; // Print menu item $c = 0; $text = $item_title; $col = $columns[$c]; $ncol = $c + 1; $width = $columns[$ncol] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$text,'0',1,'L',false,'',1); // Print item price $c = 1; $text = $oamt; $col = $columns[$c]; $ncol = $c + 1; $width = $columns[$ncol] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$text,'0',1,'R',false,'',1); // Print item qty ordered $c = 2; $text = $qty; $col = $columns[$c] - 6; $ncol = $c + 1; $width = $columns[$ncol] - $col - 8; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$text,'0',1,'R',false,'',1); // Print item category $c = 4; $text = $cat; $col = $columns[$c]; $ncol = $c + 1; $width = $columns[$ncol] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$text,'0',1,'R',false,'',1); // Print payment amount $c = 6; $text = $pamt; $col = $columns[$c]; $ncol = $c + 1; $width = $columns[$ncol] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$text,'0',1,'R',false,'',1); // Print total amount $c = 7; $text = $tamt; $col = $columns[$c]; $ncol = $c + 1; $width = $columns[$ncol] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$text,'0',1,'R',false,'',1); if ($itemCount > 0) { // Print total amount $c = 9; $text = $damt; $col = $columns[$c]; $ncol = $c + 1; $width = $columns[$ncol] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$text,'0',1,'R',false,'',1); // Print total amount $c = 10; $text = $samt; $col = $columns[$c]; $ncol = $c + 1; $width = $columns[$ncol] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$text,'0',1,'R',false,'',1); } $count++; $curr_row += $incr; } } // Print menu totals $curr_row += $incr / 2; $pdf->SetFont($document_font,"B",$document_font_size); $col = $columns[0]; $width = $columns[$lastCol + 1] - $col; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,"",'0',1,$align,true,'',1); $title = getLanguageText('TEXT-MENU-TOTAL'); $qty = $menuTotals['qty']; $sales = '$'.number_format($menuTotals['sales'],2); $discount = '$'.number_format($menuTotals['discount'],2); $align = 'R'; $i = 0; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$title,'0',1,$align,false,'',1); $i = 2; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 8; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$qty,'0',1,$align,false,'',1); $i = 7; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$sales,'0',1,$align,false,'',1); if ($itemCount > 0) { $i = 10; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$discount,'0',1,$align,false,'',1); } $pdf->SetFont($document_font,"",$document_font_size); if ($curr_row > ($max_rows - $incr * 8)) { $pdf->addPage(); $curr_row = $start_row + $incr * 2; } else $curr_row = $curr_row + $incr * 4; $pdf->SetFont($document_font,"B",$document_font_size); for ($i = 1; $i < count($tTitles); $i++) { $title = $tTitles[$i]; // writeLog($title); $col = $columns[$i]; $ncol = $i + 1; $width = $columns[$ncol] - $col - 3; // writeLog("col: $col width: $width"); $align = 'R'; if ($i == 0) $align = 'L'; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$title,'0',1,$align,false,'',1); } // $pdf->SetFont($document_font,"",$document_font_size); $curr_row = $curr_row + $incr; $align = 'R'; $i = 0; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $title = getLanguageText('TEXT-TOTALS'); $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$title,'0',1,$align,false,'',1); $i = 2; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 8; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$totalMeals,'0',1,$align,false,'',1); $i = 7; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $sales = '$'.number_format($totalSales, 2); $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$sales,'0',1,$align,false,'',1); if ($itemCount > 0) { $i = 10; $col = $columns[$i]; $width = $columns[$i + 1] - $col - 3; $discount = '$'.number_format($totalDiscount, 2); $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$discount,'0',1,$align,false,'',1); } // Print category totals $curr_row = $curr_row + $incr * 3; 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) { $c = 0; $col = $columns[$c]; $width = $columns[$c + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$title,'0',1,$align,false,'',1); $c = 2; $col = $columns[$c]; $width = $columns[$c + 1] - $col - 8; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$qty,'0',1,$align,false,'',1); $c = 7; $col = $columns[$c]; $width = $columns[$c + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,$total,'0',1,$align,false,'',1); $c = 8; $col = $columns[$c]; $width = $columns[$c + 1] - $col - 3; $pdf->SetXY($col,$curr_row); $pdf->Cell($width,$incr,"($cat)",'0',1,$align,false,'',1); $curr_row = $curr_row + $incr; } } // Output the completed PDF 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/pdf'); header("Content-Disposition: attachment; filename=$document_name"); header('Content-Transfer-Encoding: binary'); $pdf->Output("$document_name","D"); exit(); ?>