servicio-autos-api/modules/excel/controllers/ObraNominaController.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\Nomina;
use v1\models\Obra;
use v1\models\Pago;
use Yii;
use excel\web\Controller;

class ObraNominaController 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 actionExcelObraNomina($idObra)
  {
    $json = intval($this->req->get("json", ""));
    $obra = Obra::findOne($idObra);
    $query = Nomina::find()
      ->joinWith('empleado')
      ->andWhere(['{{Nomina}}.[[eliminado]]' => null])->andWhere(['{{Nomina}}.[[idObra]]' => $idObra]);
    $query->orderBy(['fechaInicio' => SORT_DESC]);
    $BASEPATH = \Yii::getAlias('@app') . "/web";
    $logo = $BASEPATH . '/img/logos/edesarrollos-unicolor-azul.png';
    $this->encabezadoHoja($this->logo($logo, 'A1'));
    $fechaInicio = (new DateTime($obra->fechaInicio))->format('d-m-Y');
    $fechaFin = (new DateTime($obra->fechaFinal))->format('d-m-Y');
    $estiloEncabezado = array_merge(self::$celdaVertical, self::$bordes);

    $encabezadoTabla = [
      "A6" => [
        "valor" => "Nomina relacionada 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 Inicio",
        "estilo" => $estiloEncabezado
      ],
      "D9" => [
        "valor" => "Fecha Fin",
        "estilo" => $estiloEncabezado
      ]
    ];
    $r = 10;
    $this->agregarRenglones($encabezadoTabla);
    $estiloCeldaIzquierda = array_merge(self::$bordes, self::$celdaFuente12);
    $estiloCondicional = $this->estiloCondicional();
    foreach ($query->each() as $obraNomina) {
      $fechaInicio = (new DateTime($obraNomina->fechaInicio))->format('d-m-Y');
      $fechaFin = (new DateTime($obraNomina->fechaFin))->format('d-m-Y');
      $empleado = $obraNomina->empleado;
      $renglones = [
        "A{$r}" => [
          "valor" => $empleado->nombre,
          "estilo" => $estiloCeldaIzquierda
        ],
        "B{$r}" => [
          "valor" => $obraNomina->montoPagado,
          "estilo" => $estiloCeldaIzquierda,
          "formato" => "$#,##0.00",
          "estiloCondicional" => $estiloCondicional

        ],
        "C{$r}" => [
          "valor" =>  $fechaInicio,
          "estilo" => $estiloCeldaIzquierda
        ],
        "D{$r}" => [
          "valor" =>  $fechaFin,
          "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,  "Nomina_" . date("YmdHis"), self::TIPO_EXCEL);
    \Yii::$app->end();
  }




}