Smopp_v2/XLSProjectList.php

<?



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;
        }
        if (intval($parent->type) == 741 || intval($parent->type) == 743)
        {
            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) {
            if (!$note['checkpoint_id'])
                $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));
            /*foreach($cp as $key => $tt) {
                var_dump($key ."=>".$tt );
            }
            echo "<br>";*/
            $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['name'] = $b['name']; 
            $temp['desc'] =  $b['value'];/**/
            $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;



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

var_dump($rrr);


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);
//$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'];

		// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();

	// Set document properties
	$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
								 ->setLastModifiedBy("Maarten Balliauw")
								 ->setTitle("Office 2007 XLSX Report Document")
								 ->setSubject("Office 2007 XLSX Report Document")
								 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
								 ->setKeywords("office 2007 openxml php")
								 ->setCategory("Report result file");

	$style = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

	$style1 = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        )
    );
	$style2 = array(
        'alignment' => array(
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
        )
    );
	$sheet = $objPHPExcel->getActiveSheet();
	
	  
    $sheet->getDefaultStyle()->getFont()->setName('Times New Roman')->setSize(11);

    $sheet->getStyle("C3")->applyFromArray($style);
  
    $objPHPExcel->getActiveSheet()->getStyle('B11:E11')->applyFromArray($styleBorder);
	cellColor('B11:E11', 'f2f2f2');
    $sheet->getRowDimension('1')->setRowHeight(31.5);
    $sheet->getRowDimension('2')->setRowHeight(31.5);
    $sheet->getRowDimension('3')->setRowHeight(31.5);
    $sheet->getRowDimension('4')->setRowHeight(31.5);
    $sheet->getRowDimension('5')->setRowHeight(16.5);
    $sheet->getRowDimension('6')->setRowHeight(1);
    $sheet->getRowDimension('7')->setRowHeight(1);
    $sheet->getRowDimension('8')->setRowHeight(1);
    $sheet->getRowDimension('9')->setRowHeight(1);
    $sheet->getRowDimension('10')->setRowHeight(1);
    $sheet->getRowDimension('11')->setRowHeight(40);
    //$sheet->getRowDimension('1')->setRowHeight(31.5);
    $sheet->getStyle('C2')->getFont()->setBold(TRUE);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', "АКТ ПРИЕМКИ НА ПЛАНОВЫЙ РЕМОНТ.");
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C3', "Локомотив серия ".$proj['loco_type']." номер ".$proj['loco_number']);
	$sheet->mergeCells('A1:B1');
	$sheet->mergeCells('B11:C11');
    //$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2', 'Проект №'.$proj['id']);
	//cellColor('A1:J1', '8DB4E2');

	$sheet->getStyle('C3')->getFont()->setBold(TRUE);
	$sheet->getStyle('B11:D11')->getFont()->setBold(TRUE);
	//$sheet->getStyle('B3:B6')->getFont()->setBold(TRUE);

  //$sheet->setCellValue('B11', '№ п/п');
  $i=13;
  $k=1;
 

  
  
    foreach( $arrayMain as $mainItem) { 
            $sub = $i-1;
            $sheet->setCellValue('C'.$sub, $mainItem['namedesc']);
            $sheet->getStyle('C'.$sub)->getFont()->setBold(TRUE);
        //echo $mainItem['namedesc']; echo "<br>";
        //echo $mainItem['namedesc'] . '<br>';
        //echo "<pre>";
        //var_dump($mainItem); 
        //echo "</pre>";
            

            foreach($mainItem as $key => $item){
                
                

                if(strlen($item['desc']) <= 100){
                $heigt = 15;
                }elseif(strlen($item['desc']) <= 200){
                $heigt = 35;
                }elseif(strlen($item['desc']) <= 300){
                $heigt = 55;
                }
                $sheet->getRowDimension($i)->setRowHeight($heigt);    
                $sheet->getStyle("C".$i)->getAlignment()->setWrapText(true);
                $sheet->getStyle('B'.$i)->getFont()->setBold(TRUE);
                    /*echo $item['name'];
                    //var_dump(( null == $item['name']));
                    //var_dump((in_array($key,['namedesc','finished'])));
                    echo $item['desc'];*/
                    //var_dump($item);
                    //echo "<br>";
                    //var_dump("KEY => " .$key . " in_array = ". ( in_array($key,['namedesc','finished'])));echo "<br>";
                    if ( $key !== 0 ){
                        if( $key and in_array($key,['namedesc','finished'])) { continue;}
                    }
                    //var_dump($item['name'].'!');  echo "<br>";
                        
                    $descExplode = explode('__',$item['desc']);
                    
                    if( 1 == $item['color'] ) {
                        cellColor('C'.$i, 'f2f2f2');    
                        //$item['desc'] = substr($item['desc'],3);
                    } 
                    $sheet->setCellValue('B'.$i, $k); //echo $k;
                    // $sheet->setCellValue('C'.$i, $item['name']); //echo $item['name'];
                    $sheet->setCellValue('C'.$i, $item['desc']);
                    //$sheet->setCellValue('D'.$i, $key .'->'.in_array($key,['namedesc','finished']));
                    $i++;
                    $k++;
            }
            //$sud2 = $i + 1;
            //if (empty($finished_datetime))
            // $sheet->setCellValue('B'.$sud2, "Дата окончания осмотра: не завершена");
            //else
            //$sheet->setCellValue('B'.$sud2, "Дата окончания: " . $mainItem['finished']);
        
        $i += 2;
        //$k=1;
    }
    $i -= 2;
    for($m=$k;$m<$k+10;$m++){
            $sheet->getStyle('B'.$i)->getFont()->setBold(TRUE);
            $sheet->setCellValue('B'.$i, $m);
            //$sheet->setCellValue('B'.$i, ' ');
            $i++;
    }
    
    $i2 = $i + 2;
    $sheet->setCellValue('C'.$i2, '____________________________­­­­______________________');
    $i2 += 1;
    $sheet->setCellValue('C'.$i2,'____________________________­­­­______________________');
        
  //exit;
    $sheet->setCellValue('B11', 'Замечание бригадира приемки по работе электровоза');
  
    $sheet->setCellValue('D11', 'Ф.И.О., роспись исполнителя');
    $sheet->getStyle("D11")->getAlignment()->setWrapText(true);
    $sheet->setCellValue('E11', 'Роспись мастера (бригадира)');
	$sheet->getStyle('A10:L11')->getFont()->setBold(TRUE);

    $objPHPExcel->getActiveSheet()->getStyle("C3")->getFont()->setSize(18);
    $objPHPExcel->getActiveSheet()->getStyle("C2")->getFont()->setSize(18);
    //$objPHPExcel->getActiveSheet()->getStyle("C3:C4")->getFont()->setSize(18);
	//$sheet->setCellValue('C3', 'Акт №');

	//$sheet->setCellValue('C4', $namedesc[0]);
    $sheet->setCellValue('C4', 'Дата приемки­­­­__________');
    $sheet->setCellValue('C5', "Электровоз серия________№__________  ");
    $sheet->setCellValue('D5', " Вид ремонта___________");
    //$sheet->setCellValue('C6', 'Настоящий акт подверждает, что '.$project_finished->date);
	//$sheet->setCellValue('C7', "На локомотиве серии ".$proj['loco_type']." номер ".$proj['loco_number']);
	//$sheet->setCellValue('C8', "Приписки локомотивного депо: $depo");
	//$sheet->setCellValue('C9', 'В ходе приемки, испытания, осмотра и ремонта  Локомотива необходимо проведение следующих работ:');
    //$sheet->getStyle('C9')->getFont()->setBold(TRUE);
	//$sheet->setCellValue('C2', 'Значение');


	//cellColor('A10:L10', '8DB4E2');

	$sheet->getStyle('B11')->getFont()->setBold(TRUE);
	$sheet->getStyle('B10')->getFont()->getColor()->setRGB('FFFFFF');

//$i=12;
$finished = [];
$assignee_names = [];
//echo '<pre><h3>Шаг 2.</h3><br/>'; var_dump($array_children); echo '<pre>'; die();

foreach ($array_children as $parent_id => $children)
{
//echo '<pre><h3>Шаг 2.</h3><br/>'; var_dump($children); echo '<pre>'; die();
    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;
            }
        }
        $taskid = $res1->id;
        $type = $res1->type;
        $created = $res1->created;
        //$accepted = $res1->accepted_time;
        //$finished[] = $res1->finished_time;
        //$finished[] = $res1->accepted_time->date;
        //$assignee = $res1->assignees_arr;
        //$assignee_names[] = $res1->name;//GetAccount($assignee)['name'];
        //if ($finished == '')
        //    $finished = 'не завершена';
        //if ($accepted == '')
        //    $accepted = 'не принята';

        $namedesc = GetTaskNameAndDesc($type);
//        $sheet->setCellValueByColumnAndRow(0, $i, $taskid);
//        $sheet->setCellValueByColumnAndRow(1, $i, $namedesc[0]);
//        $sheet->setCellValueByColumnAndRow(2, $i, $namedesc[1]);
//        $sheet->setCellValueByColumnAndRow(3, $i, $finished);
//        $sheet->setCellValueByColumnAndRow(4, $i, $assignee_names);
//        $sheet->setCellValueByColumnAndRow(5, $i, $namedesc[1]);
//        $sheet->setCellValueByColumnAndRow(6, $i, $assignee_names);
        $notes = FindTaskNotes($taskid);
        if (sizeof($notes) > 0) {
            $nn = 2;
            $num = 0;
            $notestext = "$taskid. \n";
            foreach ($notes as $note) {
                $num++;
//                if ($num == 3)
//                    break;
                $notedate = $note['date'];
                $noteid = $note['id'];
                $notetext = $note['text'];
                //$sheet->setCellValueByColumnAndRow($nn, $i, $noteid." [".$notedate."] ".$notetext);
                $sheet->getStyle('C'.$i)->getAlignment()->setWrapText(true);
                $sheet->getStyle('D'.$i)->getAlignment()->setWrapText(true);
                $sheet->getStyle('E'.$i)->getAlignment()->setWrapText(true);
                $sheet->getStyle('F'.$i)->getAlignment()->setWrapText(true);
                $sheet->getStyle('G'.$i)->getAlignment()->setWrapText(true);
                $notestext .= $num.". ".$notetext."\n";
                //$nn++;
            }
            $sheet->setCellValueByColumnAndRow($nn, $i, $notestext);
            $i++;
        }
    }
}
$i--;
$sheet->getStyle("B12:E".$i)->applyFromArray($styleBorder);
$i+=2;
$sheet->getRowDimension($i)->setRowHeight(25);
rsort($finished);
$finished_datetime = $finished[0];
//echo '<pre><h3>Шаг 2.</h3><br/>'; var_dump($finished_datetime); echo '<pre>'; die();
//if (empty($finished_datetime))
//    $sheet->setCellValueByColumnAndRow(2, $i,  'Дата окончания осмотра: не завершена');
//else
//    $sheet->setCellValueByColumnAndRow(2, $i,  'Дата окончания осмотра: '.date('Y-m-d H:i:s', strtotime($finished_datetime->date)));
$i+=2;
$sheet->getRowDimension($i)->setRowHeight(30);
$assignee_names = array_unique($assignee_names);
//echo '<pre><h3>Шаг 2.</h3><br/>'; var_dump($assignee_names); echo '<pre>'; die();
//$sheet->setCellValueByColumnAndRow(2, $i,  'Исполнитель: '.implode(',', $assignee_names));

		// Rename worksheet
		$sheet->setTitle('Report');
		
		
		// Set active sheet index to the first sheet, so Excel opens this as the first sheet
		$objPHPExcel->setActiveSheetIndex(0);


		$sheet->getColumnDimension('A')->setWidth(1);
		$sheet->getColumnDimension('B')->setWidth(4.14);
		$sheet->getColumnDimension('C')->setWidth(51.29);
        $sheet->getColumnDimension('D')->setWidth(15.29);
		$sheet->getColumnDimension('E')->setWidth(15.29);
		$sheet->getColumnDimension('F')->setWidth(51.29);
		$sheet->getColumnDimension('G')->setWidth(51.29);
		$sheet->getColumnDimension('H')->setAutoSize(true);
		$sheet->getColumnDimension('I')->setAutoSize(true);
        $sheet->getColumnDimension('J')->setAutoSize(true);
        $sheet->getColumnDimension('K')->setAutoSize(true);
        $sheet->getColumnDimension('L')->setAutoSize(true);
        $sheet->getColumnDimension('M')->setAutoSize(true);

//foreach($sheet->getRowDimensions() as $rd) {
//$rd->setRowHeight(-1);
//}

		$objPHPExcel->getActiveSheet()->getStyle('E11')->getAlignment()->setWrapText(true);
		
		$sheet->getStyle("A10:M10")->applyFromArray($style2);
		
		$styleBorder = array('font' => array('bold' => true));
		$sheet->getStyle('B1:F1')->applyFromArray($styleBorder);
		$sheet->getStyle('A1')->getFont()->setBold(TRUE);

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

?>