servicio-autos-api/modules/excel/controllers/ObraPagoController.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\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use v1\models\Obra;
use v1\models\Pago;
use Yii;
use excel\web\Controller;
use yii\web\Response;

class ObraPagoController extends Controller {

//Version de hugo
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 actionExcelObraPago($idObra) {
        $json = intval($this->req->get("json", ""));

        $obra = Obra::findOne($idObra);
        $query= Pago::find()	
          ->joinWith('conceptosObra')
          ->andWhere(['{{Pago}}.[[eliminado]]'=>null])->andWhere(['{{Pago}}.[[idObra]]'=>$idObra]);
          $query->orderBy(['fechaPago' => SORT_DESC]);
        if ($json) {
          \Yii::$app->getResponse()->format = Response::FORMAT_JSON;
          return $json;
        }
    
        $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" => "Pagos 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 Pago",
            "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 $obraPago) {
          $fecha = (new DateTime($obraPago->fechaPago))->format('d-m-Y');
          $Pago = $obraPago->conceptosObra;
    
          $renglones = [
            "A{$r}" => [
              "valor" => $Pago->concepto,
              "estilo" => $estiloCeldaIzquierda
            ],
            "B{$r}" => [
              "valor" => $obraPago->cantidad,
              "estilo" => $estiloCeldaIzquierda,
              "formato" => "$#,##0.00",
              "estiloCondicional" => $estiloCondicional
    
            ],
            "C{$r}" => [
              "valor" => $fecha,
              "estilo" => $estiloCeldaIzquierda
            ],
            "D{$r}" => [
              "valor" => $obraPago->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,  "Pago_" . date("YmdHis"), self::TIPO_EXCEL);
        \Yii::$app->end();
      }

      public function actionPagoConceptoExcel($idObra)
      {
        $json = intval($this->req->get("json", ""));
    
        $obra = Obra::findOne($idObra);
        $query = Pago::find()
        ->select(['{{ConceptoObra}}.[[concepto]] AS concepto', 'SUM({{Pago}}.[[cantidad]]) AS totalPago'])
        ->innerJoin('{{ConceptoObra}}', '{{Pago}}.[[idConceptoObra]] = {{ConceptoObra}}.[[id]]')
        ->andWhere(['{{Pago}}.[[eliminado]]' => null])
        ->andWhere(['{{Pago}}.[[idObra]]' => $idObra])
        ->groupBy('{{ConceptoObra}}.[[id]]') 
        ->asArray()
        ->all();
        if ($json) {
          \Yii::$app->getResponse()->format = Response::FORMAT_JSON;
          return $json;
        }
    
        $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" => "Pagos por Concepto de Obra, 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" => "Total:",
            "estilo" => $estiloEncabezado
          ],
    
        ];
    
        $r = 10;
        $estiloCondicional = $this->estiloCondicional();
        $this->agregarRenglones($encabezadoTabla);
        $estiloCeldaIzquierda = array_merge(self::$bordes, self::$celdaFuente12);
        $TotalPago= 0;

        foreach ($query as $obraPago) {
          $TotalPago=$TotalPago+$obraPago['totalPago'];

          $renglones = [
            "A{$r}" => [
              "valor" => $obraPago['concepto'],
              "estilo" => $estiloCeldaIzquierda
            ],
            "B{$r}" => [
              "valor" => $obraPago['totalPago'],
              "estilo" => $estiloCeldaIzquierda,
              "formato" => "$#,##0.00",
              "estiloCondicional" => $estiloCondicional
    
            ],
           
          ];
          $this->agregarRenglones($renglones);
          $r += 1;
        }
        $r+=1;
        $renglonesTotales = [
          "A{$r}" => [
              "valor" => "TOTAL",
              "estilo" => $estiloEncabezado
          ],
          "B{$r}" => [
              "valor" => $TotalPago, 
              "estilo" => $estiloCeldaIzquierda,
              "formato" => "$#,##0.00",
              "estiloCondicional" => $estiloCondicional
          ],
      ];
      $this->agregarRenglones($renglonesTotales);

        $anchoColumnas = [
          "A" => ["ancho" => 20],
          "B" => ["ancho" => 20],
          "C" => ["ancho" => 20],
          "D" => ["ancho" => 20],
        ];
        $this->anchoColumnas($anchoColumnas);
        $documento = $this->obtenerHojaDeCalculo();
        $this->crear($documento,  "PagoConcepto_" . date("YmdHis"), self::TIPO_EXCEL);
        \Yii::$app->end();
        return $documento;
      }

  }