<?php
class CuentaProveedor
{
    public $codproveedor;
    public $nombre_proveedor;
    public $total;
    public $saldo;
    public $de_01_a_30;
    public $de_31_a_60;
    public $de_61_a_90;
    public $mas_90;
    public $nombre_empresa;
    public $codclasificacion;

    function __construct()
    {
        !empty($this->total) && $this->total = '$' . number_format($this->total, 2, '.', ',');
        !empty($this->saldo) && $this->saldo = '$' . number_format($this->saldo, 2, '.', ',');
        !empty($this->de_01_a_30) && $this->de_01_a_30 = '$' . number_format($this->de_01_a_30, 2, '.', ',');
        !empty($this->de_31_a_60) && $this->de_31_a_60 = '$' . number_format($this->de_31_a_60, 2, '.', ',');
        !empty($this->de_61_a_90) && $this->de_61_a_90 = '$' . number_format($this->de_61_a_90, 2, '.', ',');
        !empty($this->mas_90) && $this->mas_90 = '$' . number_format($this->mas_90, 2, '.', ',');
    }

    public static function obtenerCXPPendientes(): object
    {
        $db = obtenerConexion();

        $query = "select cuentas.CodProveedor codproveedor,
            cuentas.NomProveedor nombre_proveedor, 
            sum(cuentas.MontoTotal) as total,
            sum(cuentas.Saldo) as saldo,
            sum(case when cuentas.diastranscurridos <=30 then cuentas.saldo else 0.0 end) as de_01_a_30,
            sum(case when cuentas.diastranscurridos >30 and cuentas.diastranscurridos <=60  then cuentas.saldo else 0.0 end) as de_31_a_60,
            sum(case when cuentas.diastranscurridos >60 and cuentas.diastranscurridos <=90  then cuentas.saldo else 0.0 end) as de_61_a_90,
            sum(case when cuentas.diastranscurridos >90  then cuentas.saldo else 0.0 end) as mas_90,
            empresas.nomempresa nombre_empresa, 
            proveedores.codclasificacion
        from [" . DB_NET . "].dbo.v_cxp_rpt cuentas
        inner join [" . DB_NET . "].dbo.gen_empresa empresas on cuentas.codempresa = empresas.codempresa
        inner join [" . DB_NET . "].dbo.gen_proveedor proveedores on cuentas.codproveedor = proveedores.codproveedor
        where cuentas.saldo > 0 and cuentas.codtipocxp in(1,2)
        group by cuentas.codproveedor,cuentas.nomproveedor,empresas.nomempresa,proveedores.codclasificacion
        order by cuentas.nomproveedor asc;";
        $stmt = $db->prepare($query, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED]);

        try {
            $stmt->execute();
            $data = $stmt->fetchAll(PDO::FETCH_CLASS, __CLASS__);
            $total_cxp = 0;
            $saldo_cxp = 0;
            $total_de_01_a_30 = 0;
            $total_de_31_a_60 = 0;
            $total_de_61_a_90 = 0;
            $total_mas_90 = 0;

            foreach ($data as $row) {
                $cxp_total = floatval(str_replace(',', '', str_replace('$', '', $row->total)));
                $cxp_saldo = floatval(str_replace(',', '', str_replace('$', '', $row->saldo)));
                $de_01_a_30 = floatval(str_replace(',', '', str_replace('$', '', $row->de_01_a_30)));
                $de_31_a_60 = floatval(str_replace(',', '', str_replace('$', '', $row->de_31_a_60)));
                $de_61_a_90 = floatval(str_replace(',', '', str_replace('$', '', $row->de_61_a_90)));
                $mas_90 = floatval(str_replace(',', '', str_replace('$', '', $row->mas_90)));

                $total_cxp += $cxp_total;
                $saldo_cxp += $cxp_saldo;
                $total_de_01_a_30 += $de_01_a_30;
                $total_de_31_a_60 += $de_31_a_60;
                $total_de_61_a_90 += $de_61_a_90;
                $total_mas_90 += $mas_90;
            }

            $totales = new stdClass();
            $totales->total = '$' . number_format($total_cxp, 2, '.', ',');
            $totales->saldo = '$' . number_format($saldo_cxp, 2, '.', ',');
            $totales->de_01_a_30 = '$' . number_format($total_de_01_a_30, 2, '.', ',');
            $totales->de_31_a_60 = '$' . number_format($total_de_31_a_60, 2, '.', ',');
            $totales->de_61_a_90 = '$' . number_format($total_de_61_a_90, 2, '.', ',');
            $totales->mas_90 = '$' . number_format($mas_90, 2, '.', ',');
            return (object)[
                'status' => 200,
                'data' => $data,
                'totales' => $totales
            ];
        } catch (PDOException $e) {
            return (object)['status' => 500, 'message' => $e->getMessage()];
        }
    }
}
