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

<?php

namespace app\modules\excel\controllers;

use v1\models\HerramientaInventario;
use v1\models\Usuario;
use DateTime;
use DateTimeZone;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use v1\models\Herramienta;
use Yii;
use yii\web\Controller;

class HerramientaInventarioController extends Controller {

  public function actionExcelHerramientaInventario() {
    $request = Yii::$app->request;
    $inicio = $request->get('inicio');
    $fin = $request->get('fin');
    $idHerramienta = $request->get('idHerramienta');

    $inicioDate = $inicio ? DateTime::createFromFormat('Y-m-d', $inicio)->format('Y-m-d 00:00:00') : null;
    $finDate = $fin ? DateTime::createFromFormat('Y-m-d', $fin)->format('Y-m-d 23:59:59') : null;


    $query = HerramientaInventario::find()->where(['eliminado' => null]);
    if ($inicioDate) {
      $query->andWhere(['>=', 'creado', $inicioDate]);
    }
    if ($finDate) {
      $query->andWhere(['<=', 'creado', $finDate]);
    }

    if ($idHerramienta) {
      $query->andWhere(['idHerramienta' => $idHerramienta]);
    }

    $query->orderBy(['creado' => SORT_DESC]);


    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Inventario de Herramientas');

    $BASEPATH = \Yii::getAlias('@app') . "/web";
    $logo = $BASEPATH . '/img/logos/edesarrollos-unicolor-azul.png';

    $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', 'Inventario de Herramientas');
    $sheet->getStyle('A5')->getFont()->setBold(true);
    $sheet->getStyle('A5')->getFont()->setSize(16);
    $sheet->getStyle('A5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    $sheet->setCellValue('A6', 'Nombre');
    $sheet->setCellValue('B6', 'Tipo');
    $sheet->setCellValue('C6', 'Costo');
    $sheet->setCellValue('D6', 'Serie');
    $sheet->setCellValue('E6', 'Fecha de Compra');
    $sheet->setCellValue('F6', 'Hora de Compra');
    $sheet->setCellValue('G6', 'Estatus');
    $sheet->setCellValue('H6', 'Descripción');
    $sheet->setCellValue('I6', 'Cantidad');
    $sheet->setCellValue('J6', 'Fecha de Ingreso');
    $sheet->setCellValue('K6', 'Hora de Ingreso');

    $headerStyle = [
      'font' => ['bold' => true],
      'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
        'wrapText' => true
      ],
      'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => ['argb' => 'FFA0A0A0']
      ]
    ];
    $sheet->getStyle('A6:K6')->applyFromArray($headerStyle);

    $herramientas = Herramienta::find()
      ->select(['nombre', 'id'])
      ->indexBy('id')
      ->column();

      $row = 7;
      foreach ($query->each() as $inventario) {
          $herramientaInfo = 'N/A';
          if (isset($herramientas[$inventario->idHerramienta])) {
              $herramientaInfo = $herramientas[$inventario->idHerramienta];
          }
      
          $fechaCompra = (new DateTime($inventario->herramienta->fechaCompra, new DateTimeZone('UTC')))
              ->setTimezone(new DateTimeZone('America/Hermosillo'))
              ->format('d/m/Y');
          $horaCompra = (new DateTime($inventario->herramienta->fechaCompra, new DateTimeZone('UTC')))
              ->setTimezone(new DateTimeZone('America/Hermosillo'))
              ->format('H:i:s');

         $fechaIngreso = (new DateTime($inventario->fechaIngreso, new DateTimeZone('UTC')))
              ->setTimezone(new DateTimeZone('America/Hermosillo'))
              ->format('d/m/Y');
          $horaIngreso = (new DateTime($inventario->fechaIngreso, new DateTimeZone('UTC')))
              ->setTimezone(new DateTimeZone('America/Hermosillo'))
              ->format('H:i:s');
      
          $sheet->setCellValue('A' . $row, $inventario->herramienta->nombre);
          $sheet->setCellValue('B' . $row, $inventario->herramienta->tipoHerramienta->tipo);
          $sheet->setCellValue('C' . $row, $inventario->herramienta->costo);
          $sheet->getStyle('C' . $row)->getNumberFormat()->setFormatCode('$#,##0.00');
          $sheet->setCellValue('D' . $row, $inventario->herramienta->serie);
          $sheet->setCellValue('E' . $row, $fechaCompra);
          $sheet->setCellValue('F' . $row, $horaCompra);
          $sheet->setCellValue('G' . $row, $inventario->herramienta->estatus);
          $sheet->setCellValue('H' . $row, $inventario->herramienta->descripcion);
          $sheet->setCellValue('I' . $row, $inventario->cantidad);
          $sheet->setCellValue('J' . $row, $fechaIngreso);
          $sheet->setCellValue('K' . $row, $horaIngreso);
      
          $row++;
      }

    foreach (range('A', 'K') as $columnID) {
      $sheet->getColumnDimension($columnID)->setWidth(20);
    }

    $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, "Inventario_Herramientas.xlsx");
    } catch (\Exception $exception) {
      return null;
    }
  }
}