static function importCartshop($tmp_file, $file, &$out)
{
global $CONFIG, $ST;
$dir = 'shared/php/';
include "{$dir}PHPExcel.php";
include "{$dir}PHPExcel/IOFactory.php";
PHPExcel_Settings::setLocale('ru');
if (file_ext($file) == 'xlsx') {
$objReader = new PHPExcel_Reader_Excel2007();
} elseif (file_ext($file) == 'xls') {
$objReader = new PHPExcel_Reader_Excel5();
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($tmp_file);
/*$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();
$keys=array();
$keys_flg=array('category_id'=>'id',
'parent_id'=>'parentid',
'name'=>'name',
'description'=>'description',
'image_name'=>'img',
'sort_order'=>'main_sort',
);
$out=array();
foreach ($objWorksheet->getRowIterator() as $row) {
$data=array();
$field=array();
$field_ext=array();
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$fld_flg=false;//Строка поля
$no_user_itm='';
foreach ($cellIterator as $cell) {
$val=trim(u2w($cell->getValue()));
if(!$fld_flg && $val=='category_id'){
$fld_flg=true;
}
if($fld_flg){//Грузим поля
if(isset($keys_flg[$val])){
$keys[$cell->getColumn()]=$keys_flg[$val];
}
// print_r($keys);
continue;
}
if($keys && isset($keys[$cell->getColumn()])){
if(is_integer($keys[$cell->getColumn()])){
$field["{$keys[$cell->getColumn()]}"]=$val;
}else{
$field_ext["{$keys[$cell->getColumn()]}"]=$val;
}
}
}
if(!$keys){
continue;
}
if(!empty($field_ext['id'])){
if($field_ext['img']){
if($img=@file_get_contents('http://www.farmcosmetica.ru/image/'.$field_ext['img'])){
$img_name=preg_replace('|^data/|','',$field_ext['img']);
$img_name=str_replace('/','_',$img_name);
$img_name="storage/catalog/".$img_name;
if(!file_exists($img_name)){
file_put_contents($img_name,$img);
}
$field_ext['img']="/".$img_name;
}else{
$field_ext['img']="";
}
}
$rs=$ST->select("SELECT * FROM sc_shop_catalog WHERE id={$field_ext['id']}");
if($rs->next()){
$ST->update('sc_shop_catalog',$field_ext,"id={$field_ext['id']}");
}else{
$ST->insert('sc_shop_catalog',$field_ext);
}
$out[$field_ext['id']]=$field_ext;
}
}
*/
$objPHPExcel->setActiveSheetIndex(1);
$objWorksheet = $objPHPExcel->getActiveSheet();
$keys = array();
$keys_flg = array('product_id' => 'id', 'categories' => 'categories', 'name' => 'name', 'quantity' => 'in_stock', 'image_name' => 'img', 'additional image names' => 'img_add', 'price' => 'price', 'date_added' => 'insert_time', 'date_modified' => 'update_time', 'viewed' => 'views', 'description' => 'html');
$out = array();
foreach ($objWorksheet->getRowIterator() as $row) {
$data = array();
$field = array();
$field_ext = array();
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$fld_flg = false;
//Строка поля
$no_user_itm = '';
// echo "\n";
foreach ($cellIterator as $cell) {
$val = trim(u2w($cell->getValue()));
if (!$fld_flg && $val == 'product_id') {
//$cell->getColumn()=='B' &&
$fld_flg = true;
}
if ($fld_flg) {
//Грузим поля
if (isset($keys_flg[$val])) {
$keys[$cell->getColumn()] = $keys_flg[$val];
}
// print_r($keys);
continue;
}
if ($keys && isset($keys[$cell->getColumn()])) {
if (is_integer($keys[$cell->getColumn()])) {
$field["{$keys[$cell->getColumn()]}"] = $val;
} else {
$field_ext["{$keys[$cell->getColumn()]}"] = $val;
}
}
}
if (!$keys) {
continue;
}
if (!empty($field_ext['id'])) {
if ($field_ext['img']) {
if ($img = @file_get_contents('http://www.farmcosmetica.ru/image/' . iconv('cp1251', 'utf-8', $field_ext['img']))) {
$img_name = preg_replace('|^data/|', '', $field_ext['img']);
$img_name = str_replace('/', '_', $img_name);
$img_name = "storage/catalog/goods/" . $img_name;
if (!file_exists($img_name)) {
file_put_contents($img_name, $img);
}
$field_ext['img'] = "/" . $img_name;
} else {
$field_ext['img'] = "";
}
}
if ($field_ext['img_add']) {
$images = explode(',', $field_ext['img_add']);
$field_ext['img_add'] = array();
foreach ($images as $i) {
if ($img = @file_get_contents('http://www.farmcosmetica.ru/image/' . iconv('cp1251', 'utf-8', $i))) {
$img_name = preg_replace('|^data/|', '', $i);
$img_name = str_replace('/', '_', $img_name);
$img_name = "storage/catalog/goods/" . $img_name;
if (!file_exists($img_name)) {
file_put_contents($img_name, $img);
}
$field_ext['img_add'][] = "/" . $img_name;
}
}
$field_ext['img_add'] = implode(',', $field_ext['img_add']);
}
$rs = $ST->select("SELECT * FROM sc_shop_item WHERE id={$field_ext['id']}");
$categories = $field_ext['categories'];
unset($field_ext['categories']);
if ($rs->next()) {
$ST->update('sc_shop_item', $field_ext, "id={$field_ext['id']}");
} else {
$ST->insert('sc_shop_item', $field_ext);
}
// if($categories){
// $categories=explode(',',$categories);
// $ST->delete('sc_shop_item2cat',"itemid={$field_ext['id']}");
// foreach ($categories as $c) {
// $ST->insert('sc_shop_item2cat',array('itemid'=>$field_ext['id'],'catid'=>$c));
// }
//
// }
// if($field_ext['manufacturer']){
// $rs=$ST->select("SELECT * FROM sc_manufacturer WHERE name='".SQL::slashes($field_ext['manufacturer'])."'");
// if($rs->next()){
// $man_id=$rs->getInt('id');
// }else{
// $man_id=$ST->insert("sc_manufacturer",array('name'=>$field_ext['manufacturer']));
// }
// $ST->update('sc_shop_item',array('manufacturer_id'=>$man_id),"id={$field_ext['id']}");
//
//
// }
// if($field_ext['Объем']){
// $rs=$ST->select("select * FROM sc_shop_prop_val WHERE prop_id=1 AND item_id={$field_ext['id']}");
// if($rs->next()){
// $ST->update('sc_shop_prop_val',array('value'=>$field_ext['Объем']),"id={$rs->get('id')}");
// }else{
// $ST->insert('sc_shop_prop_val',array('value'=>$field_ext['Объем'],'prop_id'=>1,"item_id"=>$field_ext['id']));
// }
// }
$out[$field_ext['id']] = $field_ext;
}
}
}
$nombrearchivo = "SAAC_" . quitarSimbolos($materiaabreviado) . "_" . quitarSimbolos($cursoabreviado) . "_" . quitarSimbolos($periodoabreaviado) . "_" . date("Y");
$valores = array("NombreArchivo" => "'{$nombrearchivo}'", "Codigo" => "'" . md5($codigocasilleros) . "'", "CodDocenteMateriaCurso" => "'{$codigodocentemateriacurso}'", "CodCasilleros" => "'{$codigocasilleros}'", "CodDocente" => "'{$codigodocente}'", "CodMateria" => "'{$codigomateria}'", "CodCurso" => "'{$codigocurso}'", "Direccion" => "'{$direccion}'", "Ubicacion" => "'Descarga'");
$registronotasexcel->insertarRegistro($valores);
/** Mostrar Errores */
/*error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);*/
date_default_timezone_set('America/La_Paz');
/** Incluir PHPExcel */
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/Worksheet/Drawing.php';
require_once 'funciones/funciones.php';
$nfx = adicionar("A", $cantidadcasilleros + 4 + 2);
$doc = new PHPExcel();
$locale = 'es';
$validLocale = PHPExcel_Settings::setLocale($locale);
//Propiedades del Documento
$doc->getProperties()->setCreator("Desarrollado por Ronald Franz Nina Layme - Cel:73230568")->setLastModifiedBy("Desarrollado por Ronald Franz Nina Layme - Cel:73230568")->setTitle("Registro de Notas - Sistema Académico Administrativo para Colegios Desarrollado por Ronald Franz Nina Layme - Cel:73230568")->setSubject("Sistema Académico Administrativo para Colegios")->setDescription("Sistema Académico Administrativo para Colegios, Registro de Notas")->setKeywords("Sistema Académico Administrativo para Colegios")->setCategory("Sistema Académico Administrativo para Colegios");
$doc->getActiveSheet()->getPageSetup()->setOrientation('landscape');
$doc->getActiveSheet()->getPageSetup()->setPaperSize(1);
$doc->getActiveSheet()->getPageMargins()->setRight(0.39);
$doc->getActiveSheet()->getPageMargins()->setLeft(0.39);
$doc->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
//Insertar Logo
$oLogo = new PHPExcel_Worksheet_Drawing();
$oLogo->setName('Logo');
$oLogo->setDescription('Logo');
$oLogo->setPath("../../imagenes/logos/" . $archivologo);
$oLogo->setHeight(80);
$oLogo->setCoordinates('B1');
$oLogo->setWorksheet($doc->getActiveSheet());
/**
* Create $this->objPHPExcel
* Set document name, title, author and locale.
*
* @param string $name Document name (not used as filename)
* @param string $title Document title
* @param string $author Author name
* @param string $locale Document locale (default fr_FR)
* @return ExcelWriter
* @throws \Exception
*/
public function createDocument($name = '', $title = '', $author = '', $locale = 'fr_FR')
{
$this->objPHPExcel = new \PHPExcel();
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array(' memoryCacheSize ' => '1024MB');
if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings)) {
throw new \Exception('Unable to change cache storage method. Please contact your administrator.');
}
$this->objPHPExcel->getProperties()->setCreated($author);
$this->objPHPExcel->getProperties()->setLastModifiedBy($author);
$this->objPHPExcel->getProperties()->setTitle($title);
$this->objPHPExcel->getProperties()->setCreator($author);
$validLocale = \PHPExcel_Settings::setLocale($locale);
$this->gotoSheetByIndex(0);
$this->rowIndex = 1;
$this->isLoaded = true;
return $this;
}
/**
*
*/
public function __construct($locale)
{
//Create the new instance of the document:
$this->setDocument(new sfPHPExcel());
PHPExcel_Settings::setLocale($locale);
$this->_locale = $locale;
}
/**
* @param ReportDataModel $model
* @param PersonsRecord $person
* @return string
* @throws \PHPExcel_Exception
* @throws \PHPExcel_Reader_Exception
*/
private function _create(ReportDataModel $model, PersonsRecord $person = null)
{
$formatter = \Yii::$app->formatter;
\PHPExcel_Shared_ZipStreamWrapper::register();
\PHPExcel_Shared_String::buildCharacterSets();
\PHPExcel_Settings::setLocale('ru');
$moneyFormat = '## ### ##0"грн."';
$fillColor = 'eaeaea';
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(12);
// $objPHPExcel->getProperties()
// ->setCreator('1')
// ->setLastModifiedBy("Maarten Balliauw")
// ->setTitle("Товары")
// ->setSubject("Office Document");
// ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
// ->setCategory("Result file");
//Лист с данными - Data
$objPHPExcel->getActiveSheet()->setTitle('Data')->setCellValue('A1', $model->getAttributeLabel('sumIncome'))->setCellValue('B1', $model->sumIncome)->setCellValue('A2', $model->getAttributeLabel('sumExpense'))->setCellValue('B2', $model->sumExpense)->setCellValue('A3', $model->getAttributeLabel('income'))->setCellValue('B3', $model->income)->setCellValue('A4', $model->getAttributeLabel('orderCount'))->setCellValue('B4', $model->orderCount)->setCellValue('C4', 'учитываются все доходы кроме внесения залога и регистрации')->setCellValue('A5', $model->getAttributeLabel('orderMin'))->setCellValue('B5', $model->orderMin)->setCellValue('A6', $model->getAttributeLabel('orderMax'))->setCellValue('B6', $model->orderMax)->setCellValue('A7', $model->getAttributeLabel('orderAvg'))->setCellValue('B7', $model->orderAvg)->setCellValue('A8', $model->getAttributeLabel('cardRegistrationCount'))->setCellValue('B8', $model->cardRegistrationCount)->setCellValue('A9', $model->getAttributeLabel('cardGiftRegistrationCount'))->setCellValue('B9', $model->cardGiftRegistrationCount)->setCellValue('A10', $model->getAttributeLabel('totalIncomeFromGiftCardRegistration'))->setCellValue('B10', $model->totalIncomeFromGiftCardRegistration)->setCellValue('A11', $model->getAttributeLabel('countServiceCardWasCreated'))->setCellValue('B11', $model->countServiceCardWasCreated)->setCellValue('A12', $model->getAttributeLabel('totalIncomeFromCardRegistration'))->setCellValue('B12', $model->totalIncomeFromCardRegistration)->setCellValue('A13', $model->getAttributeLabel('averageRegistrationChargeAmount'))->setCellValue('B13', $model->averageRegistrationChargeAmount)->setCellValue('C13', 'Считается по сумме которая пападает на счет, без залога')->setCellValue('A14', $model->getAttributeLabel('chargeCount'))->setCellValue('B14', $model->chargeCount)->setCellValue('A15', $model->getAttributeLabel('totalCharge'))->setCellValue('B15', $model->totalCharge)->setCellValue('A16', $model->getAttributeLabel('averageChargeAmount'))->setCellValue('B16', $model->averageChargeAmount)->setCellValue('A18', $model->getAttributeLabel('totalMoneyBackFromCard'))->setCellValue('B18', $model->totalMoneyBackFromCard)->setCellValue('A19', $model->getAttributeLabel('currentBonusesSpend'))->setCellValue('B19', $model->currentBonusesSpend)->setCellValue('A20', $model->getAttributeLabel('currentBonusesEmitted'))->setCellValue('B20', $model->currentBonusesEmitted)->setCellValue('A21', $model->getAttributeLabel('totalCardDebt'))->setCellValue('B21', $model->totalCardDebt)->setCellValue('A22', $model->getAttributeLabel('totalSpend'))->setCellValue('B22', $model->totalSpend)->setCellValue('A23', $model->getAttributeLabel('totalSpendWithCard'))->setCellValue('B23', $model->totalSpendWithCard)->setCellValue('A24', $model->getAttributeLabel('currentTicketsEmitted'))->setCellValue('B24', $model->currentTicketsEmitted)->setCellValue('A25', $model->getAttributeLabel('currentTicketsSpend'))->setCellValue('B25', $model->currentTicketsSpend)->setCellValue('A26', $model->getAttributeLabel('currentTicketsSpend'))->setCellValue('B26', $model->totalTickets)->setCellValue('A29', $model->getAttributeLabel('fromDate'))->setCellValue('B29', $formatter->asDatetime($model->fromDate, 'medium'))->setCellValue('A30', $model->getAttributeLabel('toDate'))->setCellValue('B30', $formatter->asDatetime($model->toDate, 'medium'));
$objPHPExcel->getActiveSheet()->getStyle('B1:B25')->getNumberFormat()->setFormatCode('# ##0');
//"грн."
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
//1 Лист - Статистика
$activeSheet = $objPHPExcel->createSheet(0);
$activeSheet->getDefaultColumnDimension()->setWidth(15);
$activeSheet->getDefaultRowDimension()->setRowHeight(20);
$activeSheet->getStyle('B2:B32')->getNumberFormat()->setFormatCode($moneyFormat);
$activeSheet->getStyle('C25:C30')->getNumberFormat()->setFormatCode($moneyFormat);
$activeSheet->getStyle('C16')->getNumberFormat()->setFormatCode($moneyFormat);
$activeSheet->getStyle('B7')->getNumberFormat()->setBuiltInFormatCode(1);
$activeSheet->getStyle('B12')->getNumberFormat()->setBuiltInFormatCode(1);
$activeSheet->getStyle('B13')->getNumberFormat()->setBuiltInFormatCode(1);
$activeSheet->getStyle('B15')->getNumberFormat()->setBuiltInFormatCode(1);
$activeSheet->getStyle('B20')->getNumberFormat()->setBuiltInFormatCode(1);
$activeSheet->setTitle('Статистика' . ($person !== null ? ' для ' . $person->fullName : ''))->mergeCells('A1:B1')->setCellValue('A1', $formatter->asDatetime($model->fromDate, 'long') . ' - ' . $formatter->asDatetime($model->toDate, 'long'))->mergeCells('A2:B2')->setCellValue('A2', 'Общая статистика по залу')->getStyle('A2:B2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue('A3', $model->getAttributeLabel('sumIncome'))->setCellValue('B3', $model->sumIncome)->setCellValue('A4', $model->getAttributeLabel('sumExpense'))->setCellValue('B4', $model->sumExpense)->setCellValue('A5', $model->getAttributeLabel('income'))->setCellValue('B5', $model->income)->mergeCells('A6:B6')->setCellValue('A6', 'Статистика по чекам')->getStyle('A6:B6')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$activeSheet->setCellValue('A7', $model->getAttributeLabel('orderCount'))->setCellValue('B7', $model->orderCount)->setCellValue('A8', $model->getAttributeLabel('orderMin'))->setCellValue('B8', $model->orderMin)->setCellValue('A9', $model->getAttributeLabel('orderMax'))->setCellValue('B9', $model->orderMax)->setCellValue('A10', $model->getAttributeLabel('orderAvg'))->setCellValue('B10', $model->orderAvg)->mergeCells('A11:B11')->setCellValue('A11', 'Статистика по картам')->getStyle('A11:B11')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$activeSheet->setCellValue('A12', $model->getAttributeLabel('cardRegistrationCount'))->setCellValue('B12', $model->cardRegistrationCount)->setCellValue('A13', $model->getAttributeLabel('cardGiftRegistrationCount'))->setCellValue('B13', $model->cardGiftRegistrationCount)->setCellValue('A14', $model->getAttributeLabel('totalIncomeFromGiftCardRegistration'))->setCellValue('B14', $model->totalIncomeFromGiftCardRegistration)->setCellValue('A15', $model->getAttributeLabel('countServiceCardWasCreated'))->setCellValue('B15', $model->countServiceCardWasCreated)->setCellValue('A16', $model->getAttributeLabel('totalIncomeFromCardRegistration'))->setCellValue('B16', $model->totalIncomeFromCardRegistration)->setCellValue('A17', $model->getAttributeLabel('totalCharge'))->setCellValue('B17', $model->totalCharge)->mergeCells('C16:C17')->setCellValue('C16', $model->totalIncomeFromCardRegistration + $model->totalCharge)->setCellValue('A18', $model->getAttributeLabel('averageRegistrationChargeAmount'))->setCellValue('B18', $model->averageRegistrationChargeAmount)->setCellValue('A20', $model->getAttributeLabel('chargeCount'))->setCellValue('B20', $model->chargeCount)->setCellValue('A19', $model->getAttributeLabel('averageChargeAmount'))->setCellValue('B19', $model->averageChargeAmount)->setCellValue('A21', $model->getAttributeLabel('totalMoneyBackFromCard'))->setCellValue('B21', $model->totalMoneyBackFromCard)->mergeCells('A22:B22')->setCellValue('A22', 'Статистика по бонусам')->getStyle('A22:B22')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$activeSheet->setCellValue('A23', $model->getAttributeLabel('currentBonusesEmitted'))->setCellValue('B23', $model->currentBonusesEmitted)->setCellValue('A24', $model->getAttributeLabel('currentBonusesSpend'))->setCellValue('B24', $model->currentBonusesSpend);
$activeSheet->mergeCells('A25:B25')->setCellValue('A25', 'Статистика по залу')->getStyle('A25:B25')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$activeSheet->setCellValue('A26', $model->getAttributeLabel('totalSpend'))->setCellValue('B26', $model->totalSpend)->setCellValue('A27', $model->getAttributeLabel('totalSpendWithCard'))->setCellValue('B27', $model->totalSpendWithCard)->setCellValue('A28', $model->getAttributeLabel('totalCardDebt'))->setCellValue('B28', $model->totalCardDebt)->setCellValue('A29', $model->getAttributeLabel('totalCardBonusDebt'))->setCellValue('B29', $model->totalCardBonusDebt)->setCellValue('A30', $model->getAttributeLabel('totalCardDebtPlusBonus'))->setCellValue('B30', $model->totalCardDebtPlusBonus);
// ->setCellValue('C29', $model->currentCardDebt)->setCellValue('D29', $model->getAttributeLabel('currentCardDebt'))
// ->setCellValue('C30', $model->currentCardDebtPlusBonus)->setCellValue('D30', $model->getAttributeLabel('currentCardDebtPlusBonus'));
$activeSheet->mergeCells('A32:B32')->setCellValue('A32', 'Статистика по билетам')->getStyle('A32:B32')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$activeSheet->setCellValue('A33', $model->getAttributeLabel('currentTicketsEmitted'))->setCellValue('B33', $model->currentTicketsEmitted)->setCellValue('A34', $model->getAttributeLabel('currentTicketsSpend'))->setCellValue('B34', $model->currentTicketsSpend)->setCellValue('A35', $model->getAttributeLabel('totalTickets'))->setCellValue('B35', $model->totalTickets);
$activeSheet->getStyle('C16:C17')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('C16:C17')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$activeSheet->getColumnDimension('A')->setAutoSize(true);
$activeSheet->getColumnDimension('B')->setAutoSize(true);
$activeSheet->getStyle('A1:B1')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_NONE]);
$activeSheet->getStyle('A2:B2')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$activeSheet->getStyle('A6:B6')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$activeSheet->getStyle('A11:B11')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$activeSheet->getStyle('A22:B22')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$activeSheet->getStyle('A25:B25')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$activeSheet->getStyle('A32:B32')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$activeSheet->getStyle('B4')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]);
$activeSheet->getStyle('C16')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]);
$activeSheet->getStyle('B22')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]);
$activeSheet->getStyle('B28:C30')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]);
//2 Лист - Статистика по объетам
$objectsSheet = $objPHPExcel->createSheet(1);
$objectsSheet->setTitle('По Объектам')->setCellValue('A2', 'Адрес')->setCellValue('B2', 'Объект')->setCellValue('C2', 'Всего');
$objectsSheet->getStyle('A2:Z2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$objectsSheet->getStyle('A2:Z2')->getBorders()->applyFromArray(['bottom' => ['style' => \PHPExcel_Style_Border::BORDER_MEDIUM]]);
if (!empty($model->objectsByDays)) {
$endObjectRow = count($model->objects) + 3;
$values = [];
$objectsSum = 0;
foreach ($model->objects as $objID => $value) {
$values[] = [$value['HardID'], $value['Name'], $value['cash']];
$objectsSum += $value['cash'];
}
$values[] = ['', 'Итого', $objectsSum];
$objectsSheet->fromArray($values, NULL, 'A3');
/**
* В массиве objectsByDays расположены ВСЕ существующие объекты в отсортированном порядке так же как и в objects
*/
$colN = 3;
foreach ($model->objectsByDays as $day => $array) {
// $columnName = array_shift($cols);
$columnName = PHPExcel_Cell::stringFromColumnIndex($colN++);
if (!$columnName) {
break;
}
$objectsSheet->setCellValue($columnName . '2', $day);
$column = [];
$objtotal = 0;
$column[] = [$day];
foreach ($array as $oID => $oStat) {
$objtotal += $oStat['cash'];
$column[] = [$oStat['cash']];
}
$column[] = [$objtotal];
$objectsSheet->fromArray($column, 0, $columnName . '2', true);
$objectsSheet->getColumnDimension($columnName)->setAutoSize(true);
}
if (isset($columnName)) {
$objectsSheet->getStyle("A2:{$columnName}2")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objectsSheet->getStyle("A2:{$columnName}2")->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$objectsSheet->getStyle("A{$endObjectRow}:{$columnName}{$endObjectRow}")->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]);
$objectsSheet->getStyle("C2:{$columnName}" . $endObjectRow)->getNumberFormat()->setFormatCode('### ### ##0"грн."');
$objectsSheet->getColumnDimension('A')->setAutoSize(true);
$objectsSheet->getColumnDimension('B')->setAutoSize(true);
$objectsSheet->getColumnDimension('C')->setAutoSize(true);
}
}
//3 Лист - Услуги
$servicesSheet = $objPHPExcel->createSheet(2);
$servicesSheet->setTitle('По Услугам')->setCellValue('B2', 'Услуга')->setCellValue('C2', 'Сумма');
$servicesSheet->getStyle('A2:C2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$servicesSheet->getStyle('A2:C2')->getBorders()->applyFromArray(['bottom' => ['style' => \PHPExcel_Style_Border::BORDER_MEDIUM]]);
$servicesSheet->getStyle('A2:E2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$servicesSheet->getStyle('A2:C2')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$endObjectRow = count($model->soldService) + 3;
$servicesSheet->getStyle("A{$endObjectRow}:C{$endObjectRow}")->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]);
$serviceValues = [];
$servicesSum = 0;
$_row = 0;
foreach ($model->soldService as $rowId => $value) {
$serviceValues[] = [++$_row, $value['Comment'], $value['cash']];
$servicesSum += $value['cash'];
}
$serviceValues[] = ['', 'Итого', $servicesSum];
$servicesSheet->fromArray($serviceValues, NULL, 'A3');
$servicesSheet->getColumnDimension('A')->setAutoSize(true);
$servicesSheet->getColumnDimension('B')->setAutoSize(true);
$servicesSheet->getColumnDimension('C')->setAutoSize(true);
$servicesSheet->getStyle('C2:C1000')->getNumberFormat()->setFormatCode('# ##0"грн."');
//4 Лист - Статьи
$actionsSheet = $objPHPExcel->createSheet(3);
$actionsSheet->setTitle('По Статьям')->setCellValue('B2', 'Статья')->setCellValue('C2', '')->setCellValue('D2', 'Количество')->setCellValue('E2', 'Сумма');
$actionsSheet->getStyle('A2:E2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);
$actionsSheet->getStyle('A2:E2')->getBorders()->applyFromArray(['bottom' => ['style' => \PHPExcel_Style_Border::BORDER_MEDIUM]]);
$actionsSheet->getStyle('B2:E2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$actionsSheet->getStyle('C2:C100')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$actionsSheet->getStyle('D2:D100')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$actionsSheet->getStyle('A2:E2')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]);
$endRow = count($model->actions) + 3;
$actionsSheet->getStyle("A{$endRow}:E{$endRow}")->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]);
$actionsValues = [];
$actionsSum = 0;
$_row = 0;
foreach ($model->actions as $rowId => $value) {
if ($value['Type'] === FinanceClassRecord::TYPE_OUT) {
$value['cash'] = $value['cash'] * -1;
}
$actionsValues[] = [++$_row, $value['Name'], $value['Type'] === FinanceClassRecord::TYPE_OUT ? '-' : '+', $value['count'], $value['cash']];
$actionsSum += $value['cash'];
}
$actionsValues[] = ['', '', '', 'Итого', $actionsSum];
$actionsSheet->fromArray($actionsValues, NULL, 'A3');
$actionsSheet->getColumnDimension('A')->setAutoSize(true);
$actionsSheet->getColumnDimension('B')->setAutoSize(true);
$actionsSheet->getColumnDimension('C')->setAutoSize(true);
$actionsSheet->getColumnDimension('D')->setAutoSize(true);
$actionsSheet->getColumnDimension('E')->setAutoSize(true);
$actionsSheet->getStyle('E2:E1000')->getNumberFormat()->setFormatCode('# ##0"грн."');
//Фокус на листе №
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setPreCalculateFormulas(false);
//Создание файла
$file = tempnam(sys_get_temp_dir(), 'excel');
$objWriter->save($file);
return $file;
}
public function createXlsxPdf($reportId, $arReportParams){
$templFile = 'template-2.xlsx';
$xlsxOutputFileName = $arReportParams['excel_file'];
$row = 15; // первая строка таблицы данных
// Инициализация PHPExcel
$inputFileName = "templates/{$templFile}"; // Файл шаблона
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName); // Загружаем шаблон
$locale = 'ru_ru'; // Локаль русская
PHPExcel_Settings::setLocale($locale); // устанавливаем локаль
$sheet = $objPHPExcel->getActiveSheet(); // устанавливаем активный лист
// Дополняем массив с данными по отчету данными по объекту отчета
// и сортированным и группированным массивом записей
$arReportParams = $this->expandReportData($arReportParams);
// Заполняем отчет
$sheet->setCellValue('C5', $reportId);
$sheet->setCellValue('C6', $reportId);
$sheet->setCellValue('C7', "с {$arReportParams['first_day']} по {$arReportParams['last_day']}");
$sheet->setCellValue('I5', $arReportParams['create_date']);
$sheet->setCellValue('C9', $arReportParams['object_name']);
$sheet->setCellValue('C10', $arReportParams['object_address']);
$sheet->setCellValue('C11', $arReportParams['object_phone']);
$sheet->setCellValue('C12', $arReportParams['object_email']);
// выводим записи по строкам начиная с $row
// Вставлять самую первую строку нужно указывая $row + 1, В конце удалим лишние
foreach ($arReportParams['sortedItems'] as $key => $value) {
$sheet->insertNewRowBefore($row + 1, 1); // Вставляем пустую строку в таблицу
// Начало группы записей по ОДНОЙ задаче
// Выводим Строку с альтернативным названием Задачи
if ($value['id'] > 0) {
$sheet->mergeCells("A{$row}:I{$row}"); // объединение ячеек
$sheet->getStyle("A{$row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setARGB('A7C0DC');
$sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$str = $value['objReportName'];
$h = $this->getXlsxRowHeight($str, 100, 15); // Вычисляем высоту строки
$sheet->getRowDimension($row)->setRowHeight($h);
$sheet->getStyle("B{$row}")->getAlignment()->setWrapText(true); // переносить по словам
$sheet->setCellValue("A{$row}", $str);
$row++;
}
// Выводим записи по ОДНОЙ задаче
$itemNum = 1;
foreach ($value['items'] as $index => $item) {
$sheet->insertNewRowBefore($row + 1, 1); // Вставляем пустую строку в таблицу
$sheet->mergeCells("B{$row}:G{$row}"); // объединение ячеек
$sheet->getStyle("B{$row}")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$str = $item['item_action'] . "-\n" . $item['item_result'];
$h = $this->getXlsxRowHeight($str, 150, 15);
$sheet->getRowDimension($row)->setRowHeight($h);
$sheet->getStyle("B{$row}")->getAlignment()->setWrapText(true); // переносить по словам
$sheet->setCellValue("A{$row}", $itemNum);
$sheet->setCellValue("B{$row}", $str);
$sheet->setCellValue("H{$row}", $item['correct_item_date']);
$sheet->setCellValue("I{$row}", $item['item_elapsed_hr'] ." ч. " . $item['item_elapsed_min'] . " мин.");
$row++;
$itemNum++;
}
}
$sheet->removeRow($row, 3);
$hours = floor($arReportParams['hours']) . " ч. " .
intval((round($arReportParams['hours'], 2) - floor($arReportParams['hours'])) * 60) . " мин.";
$sheet->setCellValue("I{$row}", $hours);
// Запись xlxs в файл
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("reports/{$xlsxOutputFileName}"); // Записываем в файл
// //Инициализация PDF Creator
// $pdfOutputFileName = $arReportParams['pdf_file'];
// $rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
// $rendererLibrary = 'mpdf'; // Папка с библиотекой
// $rendererLibraryPath = $_SERVER['DOCUMENT_ROOT'] . '/apps/vendor/appotter/' . $rendererLibrary;
// if (!PHPExcel_Settings::setPdfRenderer(
// $rendererName,
// $rendererLibraryPath
// )
// ) {
// die('Please set the $rendererName and $rendererLibraryPath values' .
// PHP_EOL . ' as appropriate for your directory structure');
// }
// // Создание PDF и запись в файл
// $objWriter = new PHPExcel_Writer_PDF($objPHPExcel);
// $objWriter->save("reports/{$pdfOutputFileName}");
// //Вызов родного PDF creator для примера
// //Saves file on the server as 'filename.pdf'
// $mpdf = new mPDF();
// $mpdf->WriteHTML($html);
// $mpdf->Output('filename.pdf', 'F');
}
