\n"); $error_message = "Invalid menu number"; include "form.messages.php"; return; } $hotlunch_menu_date = db_fetch_array($results); $menu_title = $hotlunch_menu_date['menu_title']; $menu_date = date("l F jS, Y",strtotime($hotlunch_menu_date['menu_date'])); $mdate = date("Y-m-d",strtotime($hotlunch_menu_date['menu_date'])); if ($schedule['schedule_type'] == 'Fund Raising' || $schedule['schedule_type'] == 'Other') $menu_date = ""; // Update menu totals $query = "SELECT sum(extended_price) AS order_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 hloh.schedule_num = $schedule_num " . "AND hloh.record_num = hloi.order_num " . "AND hloi.menu_num = $menu_num " . "AND hloh.status IN ('Unpaid','Paid') " . "AND hloi.active = 'Y'"; $results = db_query($query); $r = db_fetch_assoc($results); $menu_order_revenue = floatval($r['order_revenue']); db_query("UPDATE hotlunch_menu_dates SET order_revenue = $menu_order_revenue WHERE record_num = $menu_num"); // // 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; // Set parameters for this document $max_rows = 250; $left_col = 15; $center_col = 115; $supplier_col = $left_col; $product_col = $supplier_col + 61; $qty_col = $product_col + 61; $estimated_col = $qty_col + 21; $actual_col = $estimated_col + 21; $start_row = 10; $curr_row = $start_row; $incr = 4; // // Setup PDF document // require("tcpdf/config/lang/eng.php"); require("tcpdf/tcpdf.php"); $logo_url = $school_record['logo_url']; $logo_image = "images/" . $school_record['logo_image']; $logo_row = $max_rows + 5; $document_name = "Menu Totals"; class PDF extends TCPDF { function Header() { return; } 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','',1); $this->Image($logo_image, 10, $logo_row, 50); $this->SetXY(10,$logo_row + 10); $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 => 215.9mm x 279.4mm (portrait) $pageFormat = array(215.9, 279.4); $pdf= new PDF('P', '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, 40); //set image scale factor $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO); $pdf->addPage(); $font_red = hexdec("00"); $font_green = hexdec("00"); $font_blue = hexdec("00"); $pdf->SetTextColor($font_red,$font_green,$font_blue); $school_name = $school_record['school_name']; $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size); // Page titles $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$school_name,'0',1,'L',true); $curr_row += $incr + 1; $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$schedule['schedule_name'],'0',1,'L',true); $curr_row += $incr + 1; $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$menu_title,'0',1,'L',true); $pdf->SetXY($center_col,$curr_row); $pdf->Cell(84,$incr,$menu_date,'0',1,'R',true); $curr_row += $incr + 5; $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); $page = 1; // Initialize totals $total = 0; $revenue = 0; $expenses = 0; // // Display order revenue // $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size + 1); $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(184,$incr + 1,"Revenue",'0',1,'L',true); $curr_row += $incr + 2; $pdf->SetFont($document_font,"B",$document_font_size); $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(163,$incr,"Revenue Source",'0',1,'L',true); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,"Amount",'0',1,'R',true); $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); $curr_row += $incr + 2; $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(163,$incr,getLanguageText('TEXT-REVENUE'),'0',1,'L',true); $revenue += $menu_order_revenue; $amount = $currencySymbol . number_format($menu_order_revenue,2); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $curr_row += $incr + 2; // // Get other revenue data for this menu date // $query = "select * from hotlunch_menu_transactions " . "where school_num = $school_num " . "and schedule_num = $schedule_num " . "and menu_num = $menu_num " . "and type = 'Revenue' " . "order by description"; $results = db_query($query); if (db_num_rows($results) > 0) { while ($row = db_fetch_array($results)) { $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(163,$incr,$row['description'],'0',1,'L',true); $amount = $currencySymbol . number_format($row['amount'],2); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $revenue += $row['amount']; $curr_row += $incr + 2; } } // Print total revenue $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size); $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(163,$incr,"Total Revenue :: ",'0',1,'R',true); $amount = $currencySymbol . number_format($revenue,2); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); $curr_row += $incr + 8; // // Get supplier order records for display // $query = "select sp.supplier_num,supplier_name,product_name,online_qty,supplier_product_qty,soi.package_qty,qty_ordered,estimated_cost,soi.actual_cost,soi.supplier_product_num,soi.record_num " . "from supplier_order_header as soh, supplier_order_items soi , suppliers as s, supplier_products as sp " . "where soh.school_num = $school_num " . "and soi.school_num = $school_num " . "and s.school_num = $supplier_school_num " . "and sp.school_num = $supplier_school_num " . "and soi.order_num = soh.record_num " . "and soh.supplier_num = s.record_num " . "and soh.schedule_num = $schedule_num " . "and soh.menu_num = $menu_num " . "and soi.supplier_product_num = sp.record_num " . "order by 1,2"; $results = db_query($query); // // Process supplier orders data set // $estimated_supplier_expenses = 0.00; $actual_supplier_expenses = 0.00; // Column headers for supplier orders $printHeader = true; if (db_num_rows($results) > 0) { $esubtotal = 0; $subtotal = 0; while ($row = db_fetch_array($results)) { if ($curr_row > $max_rows) { $pdf->addPage(); $curr_row = $start_row; $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size); // Page titles $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$school_name,'0',1,'L',true); $curr_row += $incr + 1; $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$schedule['schedule_name'],'0',1,'L',true); $curr_row += $incr + 1; $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$menu_title,'0',1,'L',true); $pdf->SetXY($center_col,$curr_row); $pdf->Cell(84,$incr,$menu_date,'0',1,'R',true); $curr_row += $incr + 5; $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); $printHeader = true; } // Display section header if required if ($printHeader) { $printHeader = false; $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size + 1); $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(184,$incr + 1,"Expenses",'0',1,'L',true); $curr_row += $incr + 2; $pdf->SetFont($document_font,"B",$document_font_size); $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(60,$incr,"Supplier",'0',1,'L',true); $pdf->SetXY($product_col,$curr_row); $pdf->Cell(60,$incr,"Product",'0',1,'L',true); $pdf->SetXY($qty_col,$curr_row); $pdf->Cell(20,$incr,"QTY",'0',1,'R',true); $pdf->SetXY($estimated_col,$curr_row); $pdf->Cell(20,$incr,"Estimated",'0',1,'R',true); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,"Actual",'0',1,'R',true); $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); $curr_row += $incr + 2; } extract($row); $pdf->SetFont($document_font,"",$document_font_size - 1); // Get actual_cost $actual_cost = db_getVal("SELECT sum(actual_cost) FROM supplier_order_items_actual " . "WHERE school_num = $school_num " . "AND schedule_num = $schedule_num " . "AND menu_num = $menu_num " . "AND supplier_product_num = $supplier_product_num"); if ($actual_cost < 0) { $actual_cost = 0; $estimated_cost = 0; } $actual = $currencySymbol . number_format($actual_cost,2); $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(60,$incr,$supplier_name,'0',1,'L',true); $pdf->SetXY($product_col,$curr_row); $pdf->Cell(60,$incr,substr($product_name,0,60),'0',1,'L',true); $pdf->SetXY($qty_col,$curr_row); $pdf->Cell(20,$incr,$qty_ordered,'0',1,'R',true); $estimated = $currencySymbol . number_format($estimated_cost,2); $pdf->SetXY($estimated_col,$curr_row); $pdf->Cell(20,$incr,$estimated,'0',1,'R',true); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$actual,'0',1,'R',true); $esubtotal += $estimated_cost; $subtotal += $actual_cost; $curr_row += $incr + 2; } $expenses += $subtotal; $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size); $pdf->SetXY($left_col,$curr_row); $pdf->Cell(142,$incr,"Supplier Expenses Subtotal :: ",'0',1,'R',true); $estimated_supplier_expenses = $esubtotal; $actual_supplier_expenses = $subtotal; writeLog("estimated_supplier_expenses: $estimated_supplier_expenses"); writeLog("actual_supplier_expenses: $actual_supplier_expenses"); $amount = $currencySymbol . number_format($esubtotal,2); $pdf->SetXY($estimated_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $amount = $currencySymbol . number_format($subtotal,2); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); $curr_row += $incr + 2; } $curr_row += 2; // Add a new page if there is not enough room if ($curr_row + $incr * 20 > $max_rows) { $pdf->addPage(); $curr_row = $start_row; $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size); // Page titles $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$school_name,'0',1,'L',true); $curr_row += $incr + 1; $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$schedule['schedule_name'],'0',1,'L',true); $curr_row += $incr + 1; $pdf->SetXY($left_col,$curr_row); $pdf->Cell(184,$incr,$menu_title,'0',1,'L',true); $pdf->SetXY($center_col,$curr_row); $pdf->Cell(84,$incr,$menu_date,'0',1,'R',true); $curr_row += $incr + 5; $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); } // // Display other expenses // $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size); $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(163,$incr,"Other Expenses",'0',1,'L',true); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,"Amount",'0',1,'R',true); $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); $curr_row += $incr + 2; // // Get other expense data for this menu date // $query = "select * from hotlunch_menu_transactions " . "where school_num = $school_num " . "and schedule_num = $schedule_num " . "and menu_num = $menu_num " . "and type = 'Expense' " . "order by description"; $results = db_query($query); $subtotal = 0; if (db_num_rows($results) > 0) { while ($row = db_fetch_array($results)) { $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(163,$incr,$row['description'],'0',1,'L',true); $amount = $currencySymbol . number_format($row['amount'],2); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $subtotal += $row['amount']; $curr_row += $incr + 2; } } $actual_expenses = $subtotal; writeLog("actual_expenses: $actual_expenses"); // Print subtotal other expenses $pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc")); $pdf->SetFont($document_font,"B",$document_font_size); $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(142,$incr,"Other Expenses Subtotal :: ",'0',1,'R',true); writeLog("expenses: $expenses"); $expenses += $subtotal; writeLog("expenses: $expenses"); writeLog("subtotal: $subtotal"); $amount = $currencySymbol . number_format($subtotal,2); $pdf->SetXY($estimated_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $curr_row += $incr + 8; $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(142,$incr,"Total Expenses :: ",'0',1,'R',true); $estimated_expenses = $estimated_supplier_expenses + $actual_expenses; $amount = $currencySymbol . number_format($estimated_expenses,2); $pdf->SetXY($estimated_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $actual_expenses = $expenses; $amount = $currencySymbol . number_format($expenses,2); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $curr_row += $incr + 5; $pdf->SetXY($supplier_col,$curr_row); $pdf->Cell(142,$incr,"Total Revenue :: ",'0',1,'R',true); $amount = $currencySymbol . number_format($revenue - $estimated_expenses,2); $pdf->SetXY($estimated_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $amount = $currencySymbol . number_format($revenue - $expenses,2); $pdf->SetXY($actual_col,$curr_row); $pdf->Cell(20,$incr,$amount,'0',1,'R',true); $pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee")); $pdf->SetFont($document_font,"",$document_font_size); // Output the completed PDF $filename = "menu-report.$mdate.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=$filename"); header('Content-Transfer-Encoding: binary'); $pdf->Output($filename,"D"); ?>