<?php
   function _makeNumber($objPHPExcel,$col,$row,$text) {
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.$row,$text);
      _border($objPHPExcel,$col.$row);
      $objPHPExcel->getActiveSheet()->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
   }

   function _makeCompany($objPHPExcel,$col,$row,$text) {
      $text=_correct($text);
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.$row,$text);
      $objPHPExcel->setActiveSheetIndex(0)->getStyle($col.$row)->getFont()->setBold(true);
      $objPHPExcel->getActiveSheet()->getStyle($col.$row)->getAlignment()->setWrapText(true);
      _border($objPHPExcel,$col.$row);
   }

   function _makeContacts($objPHPExcel,$col,$row,$text) {
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.$row,$text);
      $objPHPExcel->getActiveSheet()->getStyle($col.$row)->getAlignment()->setWrapText(true);
      _border($objPHPExcel,$col.$row);
   }

   function _makeDate($objPHPExcel,$col,$row,$text) {
      if ($text=='') $text=' ';
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.$row,$text);
      $objPHPExcel->getActiveSheet()->getStyle($col.$row)->getAlignment()->setWrapText(true);
      _border($objPHPExcel,$col.$row);
      $objPHPExcel->getActiveSheet()->getStyle($col.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   }

   function _makeDummy($objPHPExcel,$col,$row) {
      $text=' ';
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.$row,$text);
   }

   function _makeRichComment($objPHPExcel,$col,$row,$objRichText) {
      $objPHPExcel->getActiveSheet()->getCell($col.$row)->setValue($objRichText);
      $objPHPExcel->getActiveSheet()->getStyle($col.$row)->getAlignment()->setWrapText(true);
      _border($objPHPExcel,$col.$row);
   }

   function _makeComment($objPHPExcel,$col,$row,$text) {
      $text=_correct($text);
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.$row,$text);
      $objPHPExcel->getActiveSheet()->getStyle($col.$row)->getAlignment()->setWrapText(true);
      _border($objPHPExcel,$col.$row);
   }

   function _correct($text) {
      $textParts=explode("*amp*",$text);
      $text=implode("&",$textParts);
      $text=stripslashes($text);
      return $text;
   }

   function _border($objPHPExcel,$cell) {
      $objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
   }

   function _colourRow($objPHPExcel,$cols,$row,$colour) {
      //
      $colours=array('FFFFFFFF','FFFF0000','FFFFFF00','FF92D050');
      //
      $range=$cols[0].$row.':'.$cols[6].$row;
      $objPHPExcel->getActiveSheet()->getStyle($range)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($colours[$colour]);
   }

   function _makeHeader($objPHPExcel,$cols,$startRow) {
      $r1=$startRow;
      $r2=$startRow-1;
      $r3=$startRow-2;
      $r4=$startRow-3;
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[1].$r4,'Partner attraction progress');
      $objPHPExcel->setActiveSheetIndex(0)->getStyle($cols[1].$r4)->getFont()->setName('Eras Medium ITC');
      $objPHPExcel->setActiveSheetIndex(0)->getStyle($cols[1].$r4)->getFont()->setSize(18);
      _addLogos($objPHPExcel,$cols,$r4);
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[1].$r3,'Filled in during the execution of the project.');
      $objPHPExcel->setActiveSheetIndex(0)->getStyle($cols[1].$r3)->getFont()->setSize(8);

      $objPHPExcel->getActiveSheet()->mergeCells($cols[1].$r2.':'.$cols[1].$r1);
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[1].$r2,'Company name');

      $objPHPExcel->getActiveSheet()->mergeCells($cols[2].$r2.':'.$cols[4].$r2);
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[2].$r2,'1st Contact');
      $objPHPExcel->getActiveSheet()->mergeCells($cols[5].$r2.':'.$cols[6].$r2);
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[5].$r2,'2nd Contact: Feedback');
      $objPHPExcel->setActiveSheetIndex(0)->getStyle($cols[1].$r2.':'.$cols[5].$r2)->getFont()->setBold(true);

      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[0].$r1,'Nr.');
      $objPHPExcel->setActiveSheetIndex(0)->getStyle($cols[0].$r1)->getFont()->setBold(true);

      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[2].$r1,'Contact name');
      $objPHPExcel->getActiveSheet()->getStyle($cols[2].$r1)->getAlignment()->setWrapText(true);

      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[3].$r1,'Phone call date');
      $objPHPExcel->getActiveSheet()->getStyle($cols[3].$r1)->getAlignment()->setWrapText(true);

      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[4].$r1,'Comments');

      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[5].$r1,'Phone call date');
      $objPHPExcel->getActiveSheet()->getStyle($cols[5].$r1)->getAlignment()->setWrapText(true);

      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[6].$r1,'Reply, Comments');
      $objPHPExcel->setActiveSheetIndex(0)->getStyle($cols[2].$r1.':'.$cols[6].$r1)->getFont()->setSize(8);

      $objPHPExcel->getActiveSheet()->getStyle($cols[0].$r1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
      $objPHPExcel->getActiveSheet()->getStyle($cols[1].$r2.':'.$cols[6].$r1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle($cols[0].$r4.':'.$cols[6].$r1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);
      $objPHPExcel->getActiveSheet()->getStyle($cols[1].$r2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle($cols[1].$r2)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFEEECE1');
      $objPHPExcel->getActiveSheet()->getStyle($cols[2].$r2.':'.$cols[4].$r1)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFD7E4BC');
      $objPHPExcel->getActiveSheet()->getStyle($cols[5].$r2.':'.$cols[6].$r1)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFFCD5B4');
   }

   function _addLogos($objPHPExcel,$cols,$row) {
      $objPHPExcel->getActiveSheet()->getRowDimension("$row")->setRowHeight(45);
      $objDrawing = new PHPExcel_Worksheet_Drawing();
      $objDrawing->setName('Logo');
      $objDrawing->setDescription('Logo');
      $objDrawing->setPath('GBlarge.png');
      $objDrawing->setHeight(58);
      $objDrawing->setCoordinates($cols[4].$row);
      $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
   }

   ini_set('memory_limit','512M');

   $projName=$_GET["p"];
   $dateStr=$_GET["d"];
   $data=$_POST["data"];
   $comps=explode('__#',"$data");

   // Error reporting
   error_reporting(E_ALL);

   // Include PHPExcel
   require_once '../Classes/PHPExcel.php';

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

   // Set document properties
   $objPHPExcel->getProperties() ->setCreator("Gateway Baltic")
   							         ->setLastModifiedBy("Gateway Baltic")
            							->setSubject("$projName")
            							->setDescription("Progress Report");

   $objPHPExcel->getDefaultStyle()->getFont()->setName('Tahoma');
   $objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
   $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
   $objPHPExcel->getActiveSheet()->setShowGridlines(false);
   $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);

   $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
   $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

   $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.4);
   $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.2);
   $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.2);
   $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.4);
   $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader(0.2);
   $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter(0.2);

   // set columns
   $cols=array('B','C','D','E','F','G','H','I');
   $widths=array(3,27,17,9,39,9,39);

   for ($i=0;$i<count($widths);$i++) $objPHPExcel->getActiveSheet()->getColumnDimension($cols[$i])->setWidth($widths[$i]);

   // $startRow is the row before the 1st company row!
   $startRow=4;
   //
   // build spreadsheet header
   _makeHeader($objPHPExcel,$cols,$startRow);
   // build spreadsheet body
   //
   // name,subproj,decision,[contacts,subName,date1,com1,date2,com2],...
   //   0     1      2          3      4        5     6    7     8
   //
   $rowNum=1;
   $rowCount=101;
   for ($i=0;$i<count($comps);$i+=9) {
      _makeNumber($objPHPExcel,$cols[0],$rowNum+$startRow,"$rowCount");
      _makeCompany($objPHPExcel,$cols[1],$rowNum+$startRow,$comps[$i]);
      $subProj=(integer) $comps[$i+1];
      if ($subProj==0) {
         _makeContacts($objPHPExcel,$cols[2],$rowNum+$startRow,$comps[$i+3]);
         _makeDate($objPHPExcel,$cols[3],$rowNum+$startRow,$comps[$i+5]);
         _makeComment($objPHPExcel,$cols[4],$rowNum+$startRow,$comps[$i+6]);
         _makeDate($objPHPExcel,$cols[5],$rowNum+$startRow,$comps[$i+7]);
         _makeComment($objPHPExcel,$cols[6],$rowNum+$startRow,$comps[$i+8]);
         _makeDummy($objPHPExcel,$cols[7],$rowNum+$startRow);
         _colourRow($objPHPExcel,$cols,$rowNum+$startRow,$comps[$i+2]);
      } else {
         $dec=$comps[$i+2];
         for ($k=0;$k<$subProj;$k++) {
            $text1=new PHPExcel_RichText();
            $text2=new PHPExcel_RichText();
            if ($k>0) {
               $i+=6;
               $rowNum++;
               _makeNumber($objPHPExcel,$cols[0],$rowNum+$startRow,'');
               _makeCompany($objPHPExcel,$cols[1],$rowNum+$startRow,'');
            }
            $text1run=$text1->createTextRun('Re: '.$comps[$i+4].".\n");
            $text1run->getFont()->setBold(true);
            $text1->createText(_correct($comps[$i+6]));
            $text2run=$text2->createTextRun('Re: '.$comps[$i+4].".\n");
            $text2run->getFont()->setBold(true);
            $text2->createText(_correct($comps[$i+8]));

            _makeContacts($objPHPExcel,$cols[2],$rowNum+$startRow,$comps[$i+3]);
            _makeDate($objPHPExcel,$cols[3],$rowNum+$startRow,$comps[$i+5]);
            _makeRichComment($objPHPExcel,$cols[4],$rowNum+$startRow,$text1);
            _makeDate($objPHPExcel,$cols[5],$rowNum+$startRow,$comps[$i+7]);
            _makeRichComment($objPHPExcel,$cols[6],$rowNum+$startRow,$text2);
            _makeDummy($objPHPExcel,$cols[7],$rowNum+$startRow);
            _colourRow($objPHPExcel,$cols,$rowNum+$startRow,$dec);
         }
         if ($subProj>1) {
            $mergeStart=$startRow+$rowNum+1-$subProj;
            $mergeEnd=$startRow+$rowNum;
            $objPHPExcel->getActiveSheet()->mergeCells($cols[0].$mergeStart.':'.$cols[0].$mergeEnd);
            $objPHPExcel->getActiveSheet()->mergeCells($cols[1].$mergeStart.':'.$cols[1].$mergeEnd);
         }
      }
      $rowNum++;
      $rowCount++;
   }

   $colours=array('FF92D050','FFFFFF00','FFFF0000');
   $texts=array('Interested in meeting','Could be interested in the future','Currently not interested');
   for ($i=0;$i<3;$i++) {
      $row=$startRow+$rowNum+1+$i;
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[1].$row,$texts[$i]);
      $objPHPExcel->getActiveSheet()->getStyle($cols[1].$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($colours[$i]);
   }

   $row+=2;
   $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cols[3].$row,' ');

   $objPHPExcel->getActiveSheet()->setTitle('Progress Report');
   $objPHPExcel->setActiveSheetIndex(0);

   // Save Excel 2007 file
   $projParts=explode(" ",$projName);
   $fileName=implode("_",$projParts);
   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
   $objWriter->save($fileName.'_Progress_Report_'.$dateStr.'.xlsx');

   echo json_encode(["status"=>'OK',"file"=>$fileName.'_Progress_Report_'.$dateStr.'.xlsx']);
?>
