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\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use v1\models\Obra;
use v1\models\ObraRubro;

use Yii;
use excel\web\Controller;

class ObraRubroController extends Controller
{


  public static $estiloCeldaIzquierda12 = [
    'font' => [
      'bold' => true,
      'size' => 12,
      'color' => ['argb' => 'FF000000'],
    ],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_LEFT,
      'vertical' => Alignment::VERTICAL_CENTER,
    ],
  ];

  public static $celdaVertical = [
    'font' => [
      'bold' => true,
      'size' => 14,
      'color' => ['argb' => 'FFFFFFFF'],
    ],
    'alignment' => [
      // 'textRotation' => 90,
      'horizontal' => Alignment::HORIZONTAL_CENTER,
      'vertical' => Alignment::VERTICAL_CENTER
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => "FF625FF5"]
    ],
  ];

  public static $celdaHorizontal = [
    'font' => [
      'bold' => true,
      'size' => 14,
      'color' => ['argb' => 'FF000000'],
    ],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
      'vertical' => Alignment::VERTICAL_CENTER
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => "FFE7E7E7"]
    ],
  ];

  public static $celdaFuente12 = [
    'font' => [
      'bold' => true,
      'size' => 10,
      'color' => ['argb' => 'FF000000'],
    ],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_LEFT,
      'vertical' => Alignment::VERTICAL_CENTER
    ],
  ];

  public static $celdaVerticalF12 = [
    'font' => [
      'bold' => true,
      'size' => 10,
      'color' => ['argb' => 'FF000000'],
    ],
    'alignment' => [
      'textRotation' => 90,
      'horizontal' => Alignment::HORIZONTAL_CENTER,
      'vertical' => Alignment::VERTICAL_CENTER
    ],
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => "FFE7E7E7"]
    ],
  ];
  public function encabezadoHoja($logo)
  {

    $celdas = [
      "A1" => [
        "valor" => $logo,
        "combinar" => "G4",
      ],
    ];

    foreach ($celdas as $coordenada => $valor) {
      $this->agregarCelda($coordenada, $valor);
    }

    $this->renglonActual = 6;

    return $this;
  }

  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(['fechaCompra' => SORT_DESC]);
    $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');
    $this->encabezadoHoja($this->logo($logo, 'A1'));
    $estiloEncabezado = array_merge(self::$celdaVertical, self::$bordes);

    $encabezadoTabla = [
      "A6" => [
        "valor" => "Rubro relacionado a la obra: $obra->nombre",
        "combinar" => "D6",
        "estilo" => $estiloEncabezado
      ],
      "A7" => [
        "valor" => "Fecha Inicial: $fechaInicio ",
        "combinar" => "B7",
        "estilo" => $estiloEncabezado
      ],
      "C7" => [
        "valor" => "Fecha Final: $fechaFin",
        "combinar" => "D7",
        "estilo" => $estiloEncabezado
      ]
    ];

    $this->agregarRenglones($encabezadoTabla);

    $encabezadoTabla = [
      "A9" => [
        "valor" => "Concepto Obra",
        "estilo" => $estiloEncabezado
      ],
      "B9" => [
        "valor" => "Cantidad ",
        "estilo" => $estiloEncabezado
      ],
      "C9" => [
        "valor" => "Fecha Compra",
        "estilo" => $estiloEncabezado
      ],
      "D9" => [
        "valor" => "Descripción",
        "estilo" => $estiloEncabezado
      ]
    ];
    $r = 10;
    $this->agregarRenglones($encabezadoTabla);
    $estiloCeldaIzquierda = array_merge(self::$bordes, self::$celdaFuente12);
    $estiloCondicional = $this->estiloCondicional();
    foreach ($query->each() as $obraRubro) {
      $fecha = (new DateTime($obraRubro->fechaCompra))->format('d-m-Y');

      $rubro = $obraRubro->conceptoObra;
      $renglones = [
        "A{$r}" => [
          "valor" => $rubro->concepto,
          "estilo" => $estiloCeldaIzquierda
        ],
        "B{$r}" => [
          "valor" => $obraRubro->cantidad,
          "estilo" => $estiloCeldaIzquierda,
          "formato" => "$#,##0.00",
          "estiloCondicional" => $estiloCondicional

        ],
        "C{$r}" => [
          "valor" =>  $fecha,
          "estilo" => $estiloCeldaIzquierda
        ],
        "D{$r}" => [
          "valor" => $obraRubro->descripcion,
          "estilo" => $estiloCeldaIzquierda
        ]
      ];
      $this->agregarRenglones($renglones);
      $r += 1;
    }
    $anchoColumnas = [
      "A" => ["ancho" => 20],
      "B" => ["ancho" => 20],
      "C" => ["ancho" => 20],
      "D" => ["ancho" => 20],
    ];
    $this->anchoColumnas($anchoColumnas);

    $documento = $this->obtenerHojaDeCalculo();
    $this->crear($documento,  "Rubro_" . date("YmdHis"), self::TIPO_EXCEL);
    \Yii::$app->end();
  }

}