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

<?php

namespace app\modules\excel\controllers;

use DateTime;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use v1\models\Gasto;
use v1\models\Obra;
use Yii;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use yii\web\Response;
use excel\web\Controller;

class ObraGastoController 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 actionGastoExcel($idObra)
  {
    $json = intval($this->req->get("json", ""));

    $obra = Obra::findOne($idObra);
    $query = Gasto::find()
      ->joinWith('conceptosObra')
      ->andWhere(['{{Gasto}}.[[eliminado]]' => null])->andWhere(['{{Gasto}}.[[idObra]]' => $idObra]);
    $query->orderBy(['fechaCompra' => 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" => "Gastos 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;
    $estiloCondicional = $this->estiloCondicional();
    $this->agregarRenglones($encabezadoTabla);
    $estiloCeldaIzquierda = array_merge(self::$bordes, self::$celdaFuente12);

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

      $renglones = [
        "A{$r}" => [
          "valor" => $gasto->concepto,
          "estilo" => $estiloCeldaIzquierda
        ],
        "B{$r}" => [
          "valor" => $obraGasto->cantidad,
          "estilo" => $estiloCeldaIzquierda,
          "formato" => "$#,##0.00",
          "estiloCondicional" => $estiloCondicional

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



  public function actionGastoConceptoExcel($idObra)
  {
    $json = intval($this->req->get("json", ""));

    $obra = Obra::findOne($idObra);
    $query = Gasto::find()
    ->select(['{{ConceptoObra}}.[[concepto]] AS concepto', 'SUM({{Gasto}}.[[cantidad]]) AS totalGasto'])
    ->innerJoin('{{ConceptoObra}}', '{{Gasto}}.[[idConceptoObra]] = {{ConceptoObra}}.[[id]]') // Relación explícita con delimitadores
    ->andWhere(['{{Gasto}}.[[eliminado]]' => null])
    ->andWhere(['{{Gasto}}.[[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" => "Gastos 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);
    $TotalGasto= 0;

    foreach ($query as $obraGasto) {
      $TotalGasto=$TotalGasto+=$obraGasto['totalGasto'];
      $renglones = [
        "A{$r}" => [
          "valor" => $obraGasto['concepto'],
          "estilo" => $estiloCeldaIzquierda
        ],
        "B{$r}" => [
          "valor" => $obraGasto['totalGasto'],
          "estilo" => $estiloCeldaIzquierda,
          "formato" => "$#,##0.00",
          "estiloCondicional" => $estiloCondicional

        ],
       
      ];
      $this->agregarRenglones($renglones);
      $r += 1;
    }
    $r+=1;
    $renglonesTotales = [
      "A{$r}" => [
          "valor" => "TOTAL",
          "estilo" => $estiloEncabezado
      ],
      "B{$r}" => [
          "valor" => $TotalGasto, 
          "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,  "GastoConcepto_" . date("YmdHis"), self::TIPO_EXCEL);
    \Yii::$app->end();
    return $documento;
  }
}