public function executeList(sfWebRequest $request) { $this->category = Doctrine::getTable('Category')->findOneById($request->getParameter('c_id', 0)); $this->forward404If(!$this->category); $q = Doctrine_Query::create()->from('Product p')->innerJoin('p.Category c')->addWhere('c.id = ?', $request->getParameter('c_id', 0))->addWhere('p.is_published = ?', true); $limit = $request->hasParameter('rows') ? $request->getParameter('rows', 10) : myConfig::get('products_per_page', '10', 'Кол-во товаров на странице'); $this->pager = new sfDoctrinePager('Product', $limit); $this->pager->setQuery($q); $this->pager->setPage($request->getParameter('page', 1)); $this->pager->init(); $this->category = Doctrine_Core::getTable('Category')->findOneById($request->getParameter('c_id', 0)); }
public function executeXlsPrice(sfWebRequest $request) { $company = Doctrine::getTable('Company')->find(array($request->getParameter('company_id'))); //check if file in cache yet $uploadDirName = sfConfig::get('sf_upload_dir') . '/' . myConfig::get('pricelist_dir', 'pricelist'); $filename = strtolower($company->name) . '_' . date('Y-m-d') . '.xls'; $filePath = $uploadDirName . '/' . $filename; if (file_exists($filePath)) { header("Pragma: no-cache"); header("Expires: 0"); header("Content-Type: application/vnd.ms-excel\r\n"); header("Content-Disposition: attachment; filename=\"" . $filename . "\"\r\n"); header("Cache-Control: max-age=0\r\n"); $fh = fopen($filePath, "rb"); fpassthru($fh); die; } $this->getRates($request); $catalog = Doctrine_Query::create()->select('c.*')->from('Category c')->addSelect('(SELECT count(*) FROM Product p WHERE p.category_id = c.id AND p.is_published = 1) as nb_products')->where('c.company_id = ?', $request->getParameter('company_id', false))->orderBy('c.root_id ASC, c.lft ASC')->execute(); $objPHPExcel = new PHPExcel(); $objPHPExcelWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $styleProdName = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'font' => array('size' => 11)); $styleProdPrice = $styleProdName; $styleProdPrice['alignment'] = array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $styleProdHeader = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THICK), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THICK), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THICK), 'right' => array('style' => PHPExcel_Style_Border::BORDER_THICK)), 'font' => array('size' => 11, 'bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0); $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.25); $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.25); $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75); $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.25); $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(60); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(60); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10); $objPHPExcel->getActiveSheet()->setCellValue('A1', $company->name . ', ' . $company->address . ', ' . $company->phone . "\nкурс: 1" . $this->mainCurrency->name . '=' . $this->saleCurrency->rate_to_main . $this->saleCurrency->name); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->mergeCells('A1:E1'); $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFont()->setSize(16); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->setCellValue('A2', 'Название'); $objPHPExcel->getActiveSheet()->setCellValue('B2', 'Цена тнг.'); $objPHPExcel->getActiveSheet()->setCellValue('C2', 'Цена розн.'); $objPHPExcel->getActiveSheet()->setCellValue('D2', 'Цена опт.'); $objPHPExcel->getActiveSheet()->setCellValue('E2', 'Примечание'); $objPHPExcel->getActiveSheet()->getStyle('A2:E2')->applyFromArray($styleProdHeader); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(28); // $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $i = 3; foreach ($catalog as $c) { if ($c->nb_products) { $cName = ($c->getNode()->hasParent() ? $c->getNode()->getParent() . ' / ' : '') . $c->name; $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $i, 4, $i); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $i, $cName); $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(28); $objPHPExcel->getActiveSheet()->getStyle("A{$i}:E{$i}")->applyFromArray($styleProdHeader); $i++; $q = Doctrine_Query::create()->from('Product p')->where('p.company_id = ?', $request->getParameter('company_id', false))->addWhere('p.category_id = ?', $c->id)->addWhere('p.is_published = ?', true)->orderBy('p.position ASC'); $products = $q->execute(); foreach ($products as $p) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $i, $p->name); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $i, round($p->price * $this->saleCurrency->rate_to_main, 0)); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $i, $p->price); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $i, $p->price2); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $i, $p->warranty); $objPHPExcel->getActiveSheet()->getStyle("A{$i}")->applyFromArray($styleProdName); $objPHPExcel->getActiveSheet()->getStyle("B{$i}")->applyFromArray($styleProdPrice); $objPHPExcel->getActiveSheet()->getStyle("C{$i}")->applyFromArray($styleProdPrice); $objPHPExcel->getActiveSheet()->getStyle("D{$i}")->applyFromArray($styleProdPrice); $objPHPExcel->getActiveSheet()->getStyle("E{$i}")->applyFromArray($styleProdPrice); $i++; } } } //save to cache $objPHPExcelWriter->save($filePath); header("Content-Type: application/vnd.ms-excel\r\n"); header("Content-Disposition: attachment; filename=\"" . strtolower($company->name) . '_' . date('Y-m-d') . ".xls\"\r\n"); header("Cache-Control: max-age=0\r\n"); $objPHPExcelWriter->save('php://output'); }