servicio-autos-api/modules/excel/controllers/ObraRubroController.php

<?php

namespace app\modules\excel\controllers;
use DateTime;
use DateTimeZone;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use v1\models\Nomina;
use v1\models\Obra;
use v1\models\ObraRubro;
use v1\models\Pago;
use Yii;
use yii\web\Controller;

class ObraRubroController extends Controller {

    public function actionExcelObraRubro($idObra) {
      $obra = Obra::findOne($idObra);
        $request = Yii::$app->request;
        $query= ObraRubro::find()	
        ->joinWith('conceptoObra')
        ->andWhere(['{{ObraRubro}}.[[eliminado]]'=>null])->andWhere(['{{ObraRubro}}.[[idObra]]'=>$idObra]);
        $query->orderBy(['creado' => SORT_DESC]);
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Rubro de la obra');

        $BASEPATH = \Yii::getAlias('@app') . "/web";
        $logo = $BASEPATH . '/img/logos/edesarrollos-unicolor-azul.png';
        $fechaInicio = (new DateTime($obra->fechaInicio))->format('d-m-Y');
        $fechaFin = (new DateTime($obra->fechaFinal))->format('d-m-Y');
        $drawing = new Drawing();
        $drawing->setName('Logotipo');
        $drawing->setDescription('Logotipo de la empresa');
        $drawing->setPath($logo);
        $drawing->setHeight(70);
        $drawing->setCoordinates('A1');
        $drawing->setOffsetX(10);
        $drawing->setOffsetY(10);
        $drawing->setWorksheet($sheet);

        $sheet->mergeCells('A5:J5');
        $sheet->setCellValue('A5','Rubro relacionado a la obra: ' . $obra->nombre);
        $sheet->mergeCells('A6:E6');
        $sheet->setCellValue('A6','Fecha Inicial: ' .$fechaInicio);
        $sheet->mergeCells('F6:J6');
        $sheet->setCellValue('F6','Fecha Final: ' .$fechaFin);
        $sheet->getStyle('A5')->getFont()->setBold(true);
        $sheet->getStyle('A5')->getFont()->setSize(16);

        $headerStyle = [
          'font' => [
              'bold' => true,
              'color' => ['argb' => 'FFFFFFFF'] // Letras en blanco
          ],
          'alignment' => [
              'horizontal' => Alignment::HORIZONTAL_CENTER,
              'vertical' => Alignment::VERTICAL_CENTER,
              'wrapText' => true
          ],
          'fill' => [
              'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
              'startColor' => ['argb' => 'FF625FF5'] // Fondo azul (con opacidad FF)
          ],
          'borders' => [
              'allBorders' => [
                  'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                  'color' => ['argb' => 'FF020073'], // Bordes azul oscuro (con opacidad FF)
              ],
          ],
      ];
      
      
      
      // Aplicar el borde a las celdas combinadas
      $sheet->getStyle('A5:J5')->applyFromArray( $headerStyle);
      $sheet->getStyle('A6:E6')->applyFromArray( $headerStyle);
      $sheet->getStyle('F6:J6')->applyFromArray( $headerStyle);
       
      $sheet->mergeCells('A8:C8');
      $sheet->setCellValue('A8', 'Concepto Obra');
      $sheet->getStyle('A8:C8')->applyFromArray( $headerStyle);
      $sheet->mergeCells('D8:E8');
      $sheet->setCellValue('D8', 'Cantidad');
      $sheet->getStyle('D8:E8')->applyFromArray( $headerStyle);
      $sheet->mergeCells('F8:G8');
      $sheet->setCellValue('F8', 'Fecha Compra');
      $sheet->getStyle('F8:G8')->applyFromArray( $headerStyle);
      $sheet->mergeCells('H8:J8');
      $sheet->setCellValue('H8', 'Descripción');
      $sheet->getStyle('H8:J8')->applyFromArray( $headerStyle);
      
      $cellBorderStyle = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['argb' => 'FF000000'], // Color negro
            ],
        ],
    ];
      $row = 9;

        foreach ($query->each() as $obraRubro) {
            $fecha = (new DateTime($obraRubro->fechaCompra))->format('d-m-Y');

            $gasto = $obraRubro->conceptoObra; 
            $sheet->mergeCells('A' . $row . ':C' . $row);
           $sheet->setCellValue('A' . $row, $gasto->concepto); 
            $sheet->getStyle('A' . $row . ':C' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('A' . $row . ':C' . $row)->applyFromArray($cellBorderStyle);

            $sheet->mergeCells('D' . $row . ':E' . $row);
            $sheet->setCellValue('D' . $row, $obraRubro->cantidad); 
            $sheet->getStyle('D' . $row . ':E' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('D' . $row . ':E' . $row)->applyFromArray($cellBorderStyle);
            $sheet->getStyle('D' . $row)
            ->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
            $sheet->mergeCells('F' . $row . ':G' . $row);
            $sheet->setCellValue('F' . $row, $fecha); 
            $sheet->getStyle('F' . $row . ':G' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('F' . $row . ':G' . $row)->applyFromArray($cellBorderStyle);
            
            $sheet->mergeCells('H' . $row . ':J' . $row);
            $sheet->setCellValue('H' . $row, $obraRubro->descripcion);
            $sheet->getStyle('H' . $row . ':J' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
            $sheet->getStyle('H' . $row . ':J' . $row)->applyFromArray($cellBorderStyle);
            $row++;
        }
       
       
        $sheet->getStyle('A5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        try {
          ob_start();
          $writer->save("php://output");
          $documento = ob_get_contents();
          ob_clean();
          Yii::$app->getResponse()->sendContentAsFile($documento, "Rubro.xlsx");
        } catch (\Exception $exception) {
          return null;
        }
    }

    public function actionExcelRubro($idObra) {
        $obra = Obra::findOne($idObra);
          $request = Yii::$app->request;
          $query= ObraRubro::find()	
          ->joinWith('conceptoObra')
          ->andWhere(['{{ObraRubro}}.[[eliminado]]'=>null])->andWhere(['{{ObraRubro}}.[[idObra]]'=>$idObra]);
          $query->orderBy(['creado' => SORT_DESC]);
          $spreadsheet = new Spreadsheet();
          $sheet = $spreadsheet->getActiveSheet();
          $sheet->setTitle('Rubro de la obra');
  
          $BASEPATH = \Yii::getAlias('@app') . "/web";
          $logo = $BASEPATH . '/img/logos/edesarrollos-unicolor-azul.png';
          $fechaInicio = (new DateTime($obra->fechaInicio))->format('d-m-Y');
          $fechaFin = (new DateTime($obra->fechaFinal))->format('d-m-Y');
          $drawing = new Drawing();
          $drawing->setName('Logotipo');
          $drawing->setDescription('Logotipo de la empresa');
          $drawing->setPath($logo);
          $drawing->setHeight(70);
          $drawing->setCoordinates('A1');
          $drawing->setOffsetX(10);
          $drawing->setOffsetY(10);
          $drawing->setWorksheet($sheet);
  
          $sheet->mergeCells('A5:J5');
          $sheet->setCellValue('A5','Rubro relacionado a la obra: ' . $obra->nombre);
          $sheet->mergeCells('A6:E6');
          $sheet->setCellValue('A6','Fecha Inicial: ' .$fechaInicio);
          $sheet->mergeCells('F6:J6');
          $sheet->setCellValue('F6','Fecha Final: ' .$fechaFin);
          $sheet->getStyle('A5')->getFont()->setBold(true);
          $sheet->getStyle('A5')->getFont()->setSize(16);
  
          $headerStyle = [
            'font' => [
                'bold' => true,
                'color' => ['argb' => 'FFFFFFFF'] // Letras en blanco
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['argb' => 'FF625FF5'] // Fondo azul (con opacidad FF)
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => 'FF020073'], // Bordes azul oscuro (con opacidad FF)
                ],
            ],
        ];
        
        
        
        // Aplicar el borde a las celdas combinadas
        $sheet->getStyle('A5:J5')->applyFromArray( $headerStyle);
        $sheet->getStyle('A6:E6')->applyFromArray( $headerStyle);
        $sheet->getStyle('F6:J6')->applyFromArray( $headerStyle);
         
        $sheet->mergeCells('A8:C8');
        $sheet->setCellValue('A8', 'Concepto Obra');
        $sheet->getStyle('A8:C8')->applyFromArray( $headerStyle);
        $sheet->mergeCells('D8:E8');
        $sheet->setCellValue('D8', 'Cantidad');
        $sheet->getStyle('D8:E8')->applyFromArray( $headerStyle);
        $sheet->mergeCells('F8:G8');
        $sheet->setCellValue('F8', 'Fecha Compra');
        $sheet->getStyle('F8:G8')->applyFromArray( $headerStyle);
        $sheet->mergeCells('H8:J8');
        $sheet->setCellValue('H8', 'Descripción');
        $sheet->getStyle('H8:J8')->applyFromArray( $headerStyle);
        
        $cellBorderStyle = [
          'borders' => [
              'allBorders' => [
                  'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                  'color' => ['argb' => 'FF000000'], // Color negro
              ],
          ],
      ];
        $row = 9;
  
          foreach ($query->each() as $obraRubro) {
              $fecha = (new DateTime($obraRubro->fechaCompra))->format('d-m-Y');
  
              $gasto = $obraRubro->conceptoObra; 
              $sheet->mergeCells('A' . $row . ':C' . $row);
             $sheet->setCellValue('A' . $row, $gasto->concepto); 
              $sheet->getStyle('A' . $row . ':C' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
              $sheet->getStyle('A' . $row . ':C' . $row)->applyFromArray($cellBorderStyle);
  
              $sheet->mergeCells('D' . $row . ':E' . $row);
              $sheet->setCellValue('D' . $row, $obraRubro->cantidad); 
              $sheet->getStyle('D' . $row . ':E' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
              $sheet->getStyle('D' . $row . ':E' . $row)->applyFromArray($cellBorderStyle);
              $sheet->getStyle('D' . $row)
              ->getNumberFormat()
              ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
              $sheet->mergeCells('F' . $row . ':G' . $row);
              $sheet->setCellValue('F' . $row, $fecha); 
              $sheet->getStyle('F' . $row . ':G' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
              $sheet->getStyle('F' . $row . ':G' . $row)->applyFromArray($cellBorderStyle);
              
              $sheet->mergeCells('H' . $row . ':J' . $row);
              $sheet->setCellValue('H' . $row, $obraRubro->descripcion);
              $sheet->getStyle('H' . $row . ':J' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY);
              $sheet->getStyle('H' . $row . ':J' . $row)->applyFromArray($cellBorderStyle);
              $row++;
          }
         
         
          $sheet->getStyle('A5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
          $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
          try {
           return $sheet;
          } catch (\Exception $exception) {
            return null;
          }
      }
}