Smopp_v2/createAnalyticsXml.php

<?php

defined('YII_DEBUG') or define('YII_DEBUG', true);
defined('YII_ENV') or define('YII_ENV', 'dev');

require __DIR__ . '/api/vendor/autoload.php';
require __DIR__ . '/api/vendor/yiisoft/yii2/Yii.php';

$config = require __DIR__ . '/api/config/web.php';

(new yii\web\Application($config));


$res = new \app\models\AnalyticsModel();

$arrTasks =  $res->getTasks(); 
$arrProjecttypes = $res->getProjecttypes();
$arrTaskstypes = $res->getTaskstypes();

$arrAccounts = $res->getAccounts();
$arrJobtypes = $res->getJobtypes();
$arrJobtypesAccounts = $res->getJobtypesAccounts();
$arrSections = $res->getSections();
/*
foreach($arrTasks as $ar){
    
    var_dump($ar->project->id); echo "<br>";
    var_dump($ar->accounts->name); echo "<br>";
}
exit;
*/
require_once('PHPExcel-1.8.2/Classes/PHPExcel.php');
// Подключаем класс для вывода данных в формате excel
require_once('PHPExcel-1.8.2/Classes/PHPExcel/Writer/Excel5.php');
 
// Создаем объект класса PHPExcel
$xls = new PHPExcel();
// Устанавливаем индекс активного листа
$xls->setActiveSheetIndex(0);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Команды');
 
// Вставляем текст в ячейку A1

$sheet->setCellValue("A1", 'action');
$sheet->setCellValue("B1", 'loco_type');
$sheet->setCellValue("C1", 'status');
$sheet->setCellValue("D1", 'loco_number');
$sheet->setCellValue("E1", 'depo');
$sheet->setCellValue("F1", 'depo_service');
$sheet->setCellValue("G1", 'created');
$sheet->setCellValue("H1", 'section_id');
$sheet->setCellValue("I1", 'id_project');
$sheet->setCellValue("J1", 'task_created');
$sheet->setCellValue("K1", 'finished_time');
$sheet->setCellValue("L1", 'accepted_time');
$sheet->setCellValue("M1", 'expires');
$sheet->setCellValue("N1", 'type');

$sheet->setCellValue("O1", 'account_id');
$sheet->setCellValue("P1", 'account_name');
$sheet->setCellValue("Q1", 'task_status');
$sheet->setCellValue("R1", 'task_id');
//$sheet->setCellValue("O1", 'input_id');

$sheet->getStyle('A1')->getFill()->setFillType(
    PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle('A1')->getFill()->getStartColor()->setRGB('EEEEEE');
 
// Объединяем ячейки
//$sheet->mergeCells('A1:H1');
 
// Выравнивание текста
$sheet->getStyle('A1')->getAlignment()->setHorizontal(
    PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
for ($i = 2; $i < 20; $i++) {

    $j = 2;
    foreach($arrTasks as $task){
        
        
        switch ($i) {
            case 2:
                $value = $task->project->action;
                break;
            case 3:
                $value = $task->project->loco_type;
                break;
            case 4:
                $value = $task->project->status;
                break;     
            case 5:
                $value = $task->project->loco_number;
                break;
                
            case 6:
                $value = $task->project->depo;
                break;
            case 7:
                $value = $task->project->depo_service;
                break;    
           
            case 8:
                $value = $task->project->created;
                break;
            case 9:
                $value = $task->project->section_id;
                break;
            case 10:
                $value = $task->project->id;
                break;
            case 11:
                $value = $task->created;
                break;
            case 12:
                $value = $task->finished_time;
                break;
            case 13:
                $value = $task->accepted_time;
                break;    
            case 14:
                $value = $task->expires;
                break;
            case 15:
                $value = $task->type;
                break;
            case 16:
                $value = $task->accounts->id;
                break;
            case 17:
                $value = $task->accounts->name;
                break;
            case 18:
                $value = $task->status;
                break;
            case 19:
                $value = $task->id;
                break;
            case 20:
                $value = '';
                break;    
        }
        $sheet->setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                           ($value));
                                          
        // Применяем выравнивание
        $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $j++;
    }
}

$xls->createSheet();
$xls->setActiveSheetIndex(1);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Типы проектов');
$sheet->setCellValue("A1", 'id');
$sheet->setCellValue("B1", 'name');
$sheet->setCellValue("C1", 'company');
for ($i = 2; $i < 5; $i++) {

    $j = 2;
    foreach($arrProjecttypes as $projecttype){
        
        
        switch ($i) {
            case 2:
                $value = $projecttype->id;
                break;
            case 3:
                $value = $projecttype->name;
                break;
            case 4:
                $value = $projecttype->company;
                break;     
         }
        $sheet->setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                           ($value));
                                          
        // Применяем выравнивание
        $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $j++;
    }
}


$xls->createSheet();
$xls->setActiveSheetIndex(2);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Типы задач');
$sheet->setCellValue("A1", 'id');
$sheet->setCellValue("B1", 'name');
$sheet->setCellValue("C1", 'company');
$sheet->setCellValue("D1", 'main_task');
$sheet->setCellValue("E1", 'time_to_complete_minutes');
$sheet->setCellValue("F1", 'object_name');


for ($i = 2; $i < 8; $i++) {

    $j = 2;
    foreach($arrTaskstypes as $taskstype){
        
        
        switch ($i) {
            case 2:
                $value = $taskstype->id;
                break;
            case 3:
                $value = $taskstype->name;
                break;
            case 4:
                $value = $taskstype->company;
                break;     
            case 5:
                $value = $taskstype->main_task;
                break;
            case 6:
                $value = $taskstype->time_to_complete_minutes;
                break;
            case 7:
                $value = $taskstype->object_name;
                break;
         }
         
        $sheet->setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                           ($value));
                                          
        // Применяем выравнивание
        $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $j++;
    }
}


$xls->createSheet();
$xls->setActiveSheetIndex(3);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Аккаунты');
$sheet->setCellValue("A1", 'id');
$sheet->setCellValue("B1", 'name');
$sheet->setCellValue("C1", 'company');
$sheet->setCellValue("D1", 'position');
$sheet->setCellValue("E1", 'repair_site');
for ($i = 2; $i < 7; $i++) {

    $j = 2;
    foreach($arrAccounts as $accounts){
        
        
        switch ($i) {
            case 2:
                $value = $accounts->id;
                break;
            case 3:
                $value = $accounts->name;
                break;
            case 4:
                $value = $accounts->company;
                break;     
            case 5:
                $value = $accounts->position;
                break;
            case 6:
                $value = $accounts->repair_site;
                break;
         }
        $sheet->setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                           ($value));
                                          
        // Применяем выравнивание
        $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $j++;
    }
}


$xls->createSheet();
$xls->setActiveSheetIndex(4);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Должности');
$sheet->setCellValue("A1", 'id');
$sheet->setCellValue("B1", 'name');
$sheet->setCellValue("C1", 'company');
for ($i = 2; $i < 5; $i++) {

    $j = 2;
    foreach($arrJobtypes as $jobtype){
        
        
        switch ($i) {
            case 2:
                $value = $jobtype->id;
                break;
            case 3:
                $value = $jobtype->name;
                break;
            case 4:
                $value = $jobtype->company;
                break;     
         }
        $sheet->setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                           ($value));
                                          
        // Применяем выравнивание
        $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $j++;
    }
}


$xls->createSheet();
$xls->setActiveSheetIndex(5);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Промежуточная таблица');
$sheet->setCellValue("A1", 'account_id');
$sheet->setCellValue("B1", 'jobtype_id');
for ($i = 2; $i < 4; $i++) {

    $j = 2;
    foreach($arrJobtypesAccounts as $jobtypeAccount){
        
        
        switch ($i) {
            case 2:
                $value = $jobtypeAccount->account_id;
                break;
            case 3:
                $value = $jobtypeAccount->jobtype_id;
                break;
                 
         }
        $sheet->setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                           ($value));
                                          
        // Применяем выравнивание
        $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $j++;
    }
}



$xls->createSheet();
$xls->setActiveSheetIndex(6);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Подписываем лист
$sheet->setTitle('Секции');
$sheet->setCellValue("A1", 'id');
$sheet->setCellValue("B1", 'locomotive_series');
$sheet->setCellValue("C1", 'section_number');
$sheet->setCellValue("D1", 'section_subnumber');
for ($i = 2; $i < 6; $i++) {

    $j = 2;
    foreach($arrSections as $section){
        
        
        switch ($i) {
            case 2:
                $value = $section->id;
                break;
            case 3:
                $value = $section->locomotive_series;
                break;
            case 4:
                $value = $section->section_number;
                break;
            case 5:
                $value = $section->section_subnumber;
                break;
                 
         }
        $sheet->setCellValueByColumnAndRow(
                                          $i - 2,
                                          $j,
                                           ($value));
                                          
        // Применяем выравнивание
        $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->
                setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $j++;
    }
}


$xls->setActiveSheetIndex(0);


// Выводим HTTP-заголовки
 header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
 header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
 header ( "Cache-Control: no-cache, must-revalidate" );
 header ( "Pragma: no-cache" );
 header ( "Content-type: application/vnd.ms-excel" );
 header ( "Content-Disposition: attachment; filename=smopp.xls" );
 
// Выводим содержимое файла
 $objWriter = new PHPExcel_Writer_Excel5($xls);
 $objWriter->save('php://output');