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;
use yii\web\Response;

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();
  }


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

    $obra = Obra::findOne($idObra);
    $query = ObraRubro::find()
    ->select(['{{ConceptoObra}}.[[concepto]] AS concepto', 'SUM({{ObraRubro}}.[[cantidad]]) AS totalObraRubro'])
    ->innerJoin('{{ConceptoObra}}', '{{ObraRubro}}.[[idConceptoObra]] = {{ConceptoObra}}.[[id]]')
    ->andWhere(['{{ObraRubro}}.[[eliminado]]' => null])
    ->andWhere(['{{ObraRubro}}.[[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" => "Rubro 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);
    $TotalObraRubro= 0;

    foreach ($query as $obraObraRubro) {
      $TotalObraRubro=$TotalObraRubro+$obraObraRubro['totalObraRubro'];

      $renglones = [
        "A{$r}" => [
          "valor" => $obraObraRubro['concepto'],
          "estilo" => $estiloCeldaIzquierda
        ],
        "B{$r}" => [
          "valor" => $obraObraRubro['totalObraRubro'],
          "estilo" => $estiloCeldaIzquierda,
          "formato" => "$#,##0.00",
          "estiloCondicional" => $estiloCondicional

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