Smopp_v2/XLStu162table.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));

header("Expires: 0");
session_start();
require_once 'PHPExcel-1.8.2/Classes/PHPExcel.php';
include 'auth.php';
include 'functions.php';
require_once "doctrine/bootstrap.php";
include 'CheckPoint.php';

include 'CheckPointType.php';
use CheckPoints\CheckPoint;
use CheckPointTypes\CheckPointType;
global $companyID;
//global $link;

$companyID = $_SESSION['company'];
$sort = $_SESSION['proj_sort'];
$activeid = (int)$_GET['activeid'];
/*
$parents = $entityManager->getRepository('Task')->findBy(array('input_id' => $activeid , 'parent_id' => 0), array('id' => 'DESC'));



$tasktypes = GrabTasktypes();
$tasktypesIds = GrabTasktypesIDs();
//var_dump($_SESSION['array_tasksid']);
//exit;

$arrayMain = [];

$query = mysqli_query($link, "SELECT write_to_twx,name FROM checkpoint_types");
$arrayTwx = [];

while ($checkpoint_arr = mysqli_fetch_array($query))
{
    $arrayTwx[$checkpoint_arr['name']] = $checkpoint_arr['write_to_twx'];
}

foreach($_SESSION['array_tasksid'] as $activeid) {
    
    foreach ($parents as $parent) {
        if (!in_array(intval($parent->type), $tasktypesIds)){
            continue;
        }    
        $status = GetTaskStatus($parent->id);
            
            if ($status == intval($sort)) {
                $array_parent[$parent->id] = $parent;
                $children = $entityManager->getRepository('Task')->findBy(array('parent_id' => $parent->id));
                $array_children[$parent->id] = $children;
            }
            

    }
    $array_type = [];
    $array_id = [];
    
    foreach ($array_children as $parent_id => $children) {
        
        if ($activeid == $parent_id) {
            foreach ($children as $res1) {
                    //EchoTask2($res1, $activeid, false);
                    $array_type[] = $res1->type;
                    $array_id[] = $res1->id;
            }
        }
    }
    
    
    $arResult = [];

    //$res = explode(',',$array_type);
    //$ids = explode(',',$array_id);
    $res = $array_type;
    $ids = $array_id;
    $arrIds = [];
    
    foreach($res as $key => $r){
            $arrIds[$ids[$key]] = $r;
    }

    foreach($arrIds as $key => $r){
        $tenpArray = [];
        $type = Tasktype::Find($r);
        $tenpArray['name'] = $type->name;
        $arResult[$key] = $tenpArray;
    }

    //echo '<pre><h3>Шаг 1.</h3><br/>'; var_dump($arResult); echo '<pre>'; die();

    foreach($arResult as $key => $id){
        $notes = FindTaskNotes($key);
        $tenpArray = [];
        foreach ($notes as $note) {
            $tenpArray[] = explode(', TWX',$note['text'])[0];
        }
        $arResult[$key]['notes'] = $tenpArray;
        
    }

    //echo '<pre><h3>Шаг 2.</h3><br/>'; var_dump($arResult); echo '<pre>'; die();

    foreach($arResult as $key => $id){
        $cps = CheckPoint::FindAllByTask($key);
        $tenpArray = [];
        
        foreach ($cps as $cp) {
           // var_dump($cp->name);echo "<br>";
            $textnotes = $entityManager->getRepository('Tasknote')->findBy(array('checkpoint_id' => $cp->id));
            
            $noteType = '';
            if ( 0 != count($textnotes)) {
                $noteType = 'Замечание: ';
            }
            foreach ($textnotes as $tn)
            {
                $noteType .= $tn->text.', ';
            }
            
            $noteType = substr_replace($noteType, ' ', -2);
            $temArray = [];
            $tenpArray['name'] = $cp->name;
            
            $tenpArray['notesType'] = $noteType;
            if ($cp->value != null){
                //echo $cp->value;
                $temArray[] = $cp->value;
            } else {
                $children = $cp->childrenCheckpoints;

                foreach ($children as $child) {
                    
                    if ( 1 == $child->state) {
                            
                        $temArray[]= $child->name;
                        //var_dump($arrayTwx[$child->name]); 
                    }
                    
                    //$temArray[] = $child->name;
                    //echo "$child->name: $child->value $child->state<br>";
                }
            }
            $tenpArray['value'] = implode(',',$temArray);
            
            if ( 1 != $arrayTwx[$tenpArray['value']] ) {
                            
                $tenpArray['grey'] = 1;
            } else {
                $tenpArray['grey'] = 0;
            }
            
            
            if (!empty($tenpArray['value']) && '' != $tenpArray['value']){ 
            
                $arResult[$key]['maps'][] = $tenpArray;
            }
        }
    }


    //echo '<pre><h3>Шаг 3.</h3><br/>'; var_dump($arResult); echo '<pre>'; die();

    $rrr = [];

    foreach($arResult as $res){
        $temp = [];
        
        
        foreach($res['notes'] as $r){
            $temp['name'] = $res['name']; 
            $temp['desc'] =  $r;
            $rrr[] = $temp;
        }
        $temp = [];
        foreach($res['maps'] as $b){
        
            
            $temp['color'] = $b['grey'];
            $temp['name'] = $res['name'];
            $temp['desc'] = $b['name'].': '.$b['value'].'. '. $b['notesType'];
            $rrr[] = $temp;
        }
        
    }

    $arResult = $rrr;
    $arResult['namedesc'] = GetTaskNameAndDesc($array_parent[$activeid]->type)[0];

    $finished = [];
    
    
    foreach ($array_children as $parent_id => $children) {
        if ($activeid != $parent_id)
        {
            continue;
        }
        
        foreach ($children as $res1) {
            foreach ($res1 as $key => $res) {
                switch ($key) {
                    case 'accepted_time':
                        $finished[] = $res;
                        break;
                    case 'account':
                        foreach ($res as $acckey => $accval) {
                            if ($acckey == 'name') {
                                $assignee_names[] = $accval;
                                break;
                            }
                        }
                        break;
                }
            }
            
        }
    }
    rsort($finished);
    $finished_datetime = $finished[0];
    
    foreach($finished_datetime as $key => $res){
        if ( 'date' == $key ) {
            $finished_datetime = $res;
        }  
    } 
    $arResult['finished'] = date('Y-m-d H:i:s', strtotime($finished_datetime));
    
    $arrayMain[] = $arResult;
//    break;
}

//exit;
//echo "<pre>";   
//var_dump($arrayMain);
//echo "</pre>";    
//var_dump($_SESSION['array_tasksid']);

//exit;





function cellColor($cells,$color){
    global $objPHPExcel;

    $objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()->applyFromArray(array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array(
             'rgb' => $color
        )
    ));
}

$styleBorder = array(
    'borders' => array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN
        )
    )
);


foreach (array_keys($_REQUEST) as $var)
{
    ${$var}=$_REQUEST[$var];
//echo "<b>$var:</b> ${$var} <br>";
}
$array_children = $_SESSION['array_children'];
$array_parent = $_SESSION['array_parent'];
$activeid = $_SESSION['actid'];
$lastActiveId = array_pop($_SESSION['array_tasksid']);
//$res_parent = $array_parent[$activeid];
$res_parent = $array_parent[$lastActiveId];





foreach($res_parent as $key => $r) {

    if ('type' == $key){
        $res_parent_type = $r;
    }
    if ('input_id' == $key){
        $res_parent_input_id = $r;
    }
    if ( 'created' == $key) {
        $res_parent_created = $r;
    }
}
*/
//echo "<pre>";
//var_dump($activeid);echo "<br>";
//var_dump($array_children);echo "<br>";

//echo "</pre>";



//exit;

$namedesc = GetTaskNameAndDesc($res_parent_type);
//$namedesc = GetTaskNameAndDesc($res_parent['type']);
$proj = GetProject($res_parent_input_id);
$sections = \app\models\entity\Sections::findAll(['section_number' => $proj['loco_number']]);
//$proj = GetProject($res_parent['input_id']);
$depo = $proj['depo'];
$depo_service = $proj['depo_service'];
$project_finished = $res_parent_created;
//echo '<pre><h3>Шаг 1.</h3><br/>'; var_dump($finished); echo '<pre>'; die();

//$finished = $res_parent['created'];


        $query = 'select spl.value,tt.name from (select t.parent_id as id, ch.value from  `checkpoints` as `ch` 
                            LEFT JOIN `tasks` AS `t` ON ch.parent_task_id = t.id 
                            LEFT JOIN `projects_locotech` AS `pl` ON t.input_id = pl.id
                            WHERE pl.id = '.$proj['id'].' and ch.type = 310 ) as `spl` 
                            LEFT JOIN `tasks` AS `ts` ON spl.id = ts.id 
                            LEFT JOIN `tasktypes` AS `tt` ON ts.type = tt.id';

        $queryRes = mysqli_query($link, $query);   
        
        while ($checkpoint_arr = mysqli_fetch_array($queryRes))
        {   
            var_dump($checkpoint_arr);
        }
        
        
        
        $query = 'select ch.text,ch.value from `projects_locotech` AS `pl` 
            LEFT JOIN `tasks` as `t` ON pl.id = t.input_id 
            LEFT JOIN `checkpoints` as `ch` ON t.id = ch.parent_task_id 
            WHERE pl.id = '.$proj['id'].' and t.type = 741 ';
        
        $queryRes = mysqli_query($link, $query);   
        
        $invArray = [];
        $k = 32;
        while ($checkpoint_arr = mysqli_fetch_assoc($queryRes))
        {   
            $invArray[$k] =   $checkpoint_arr['value'];
            $k++;
        }
        $k = 32;
        foreach($invArray as $inv){
            $sheet->setCellValue('AI'.$k, ($inv[$k])? $inv[$k] : '-' );
            $k++;
        }
    
        //exit;
		
		$objPHPExcel = PHPExcel_IOFactory::load($_SERVER['DOCUMENT_ROOT'].'/uploads/xls/tu162.xlsx');
		
		$sheet = $objPHPExcel->getActiveSheet(0);
    
        $k = 8;
        $arrForSecondTableHeader = [];
        foreach($sections as $section){
            $sheet->setCellValue('B'.$k, $section->locomotive_series);
            $sheet->setCellValue('I'.$k, $section->section_subnumber);
            $sheet->setCellValue('P'.$k, $section->section_number.$section->letter);
            $sheet->setCellValue('X'.$k, ($depo)?$depo: 'пусто');
            $arrForSecondTableHeader[] = $section->section_number.$section->letter.'/'.$section->section_subnumber;
            $k++;
        }
        $arrSecondTbl = [1 => 'AB20',2 => 'AL20', 3 => 'AT20',4 => 'DB29'];
        $k = 1;
        foreach( $arrForSecondTableHeader as  $header) {
            $sheet->setCellValue($arrSecondTbl[$k],$header );
            $k++;
        }
        $sheet->setCellValue('B3', 'АКТ № ' . $proj['id']);
        //"Локомотив серия ".$proj['loco_type']." номер ".$proj['loco_number']
        //var_dump($proj);
        //exit;
        $sheet->setCellValue('AJ32', 22);
		
		// Redirect output to a client’s web browser (Excel5)
		header('Content-Type: application/vnd.ms-excel');
        $date1 = date('Y.m.d-H.i', strtotime($project_finished->date));

        header('Content-Disposition: attachment;filename="'.$date1.'-'.$proj['loco_type'].'-'.$proj['loco_number'].'.xls"');
		header('Cache-Control: max-age=0');
		// If you're serving to IE 9, then the following may be needed
		header('Cache-Control: max-age=1');
		
		// If you're serving to IE over SSL, then the following may be needed
		header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
		header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
		header ('Pragma: public'); // HTTP/1.0
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->save('php://output');

?>