* @package PHPExcel_Reader
* @copyright Copyright (c) 2006 - 2010 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.7.3, 2010-05-17
*/
/** PHPExcel root directory */
if (!defined('PHPEXCEL_ROOT')) {
/**
* @ignore
*/
define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
PHPExcel_Autoloader::Register();
PHPExcel_Shared_ZipStreamWrapper::register();
// check mbstring.func_overload
if (ini_get('mbstring.func_overload') & 2) {
throw new Exception('Multibyte function overloading in PHP must be disabled for string functions (2).');
}
}
/**
* PHPExcel_Reader_Excel2007
*
* @category PHPExcel
* @package PHPExcel_Reader
* @copyright Copyright (c) 2006 - 2010 PHPExcel (http://www.codeplex.com/PHPExcel)
*/
class PHPExcel_Reader_Excel2007 implements PHPExcel_Reader_IReader
{
/**
* @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;
}
