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');
}
