<?php

namespace App\Http\Controllers\Modulos\MineraAntucoya;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection as Collection;
use Illuminate\Support\Arr;
use Illuminate\Support\Str;

use App\Http\Controllers\Controller;
use App\Helpers\TimeHelper;
use App\Traits\Connection;
use App\Models\Conexion;

/**
 * Class MineraAntucoyaDashboardController
 *
 * @package App\Http\Controllers\Dashboard;
 */
class MineraAntucoyaDashboardController extends Controller
{
    /**
     * ID Connection
     *
     * @var integer
     */
    protected $connection_id = 11;

    public function index (Request $request)
    {
        $particulasAmbiente = 13001;
        $voltajeBateria = 13002;

        if($request->project == 13){
            $particulasAmbiente = 13001;
            $voltajeBateria = 13002;
        }else if($request->project == 12){
            $particulasAmbiente = 14001;
            $voltajeBateria = 14002;
        }else if($request->project == 14){
            $particulasAmbiente = 16001;
            $voltajeBateria = 16002;
        }

        return [
            'neblina' => $this->getDataNeblina($request, $particulasAmbiente, 'Cálculo de Neblina'),
            'particulas_ambiente' => $this->getDataParticulasAmbiente($request, $particulasAmbiente, 'Particulas Ambiente'),
            'voltaje_bateria' => $this->getDataVoltajeBateria($request, $voltajeBateria, 'Voltaje Bateria')
        ];
    }

    public function neblinaAcida (Request $request)
    {
        return [
            'neblina_acida' => $this->getDataNeblinaAcida ($request)
        ];
    }

    /**
     *
     * @param Request $request
     * @return array
     */
	public function getDataNeblinaAcida (Request $request)
    {
        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId($this->connection_id);

            $v1 = $request->v1 ? doubleval($request->v1) : 0;
            $v2 = $request->v2 && doubleval($request->v2) != 0 ? doubleval($request->v2) : 1;
            $v3 = $request->v3 && doubleval($request->v3) != 0 ? doubleval($request->v3) : 1;
            $v4 = $request->v4 && doubleval($request->v4) != 0 ? doubleval($request->v4) : 1;

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                }

                $pg->query("SELECT dat.fch_dato::date fch_dato,
                AVG(0 + (1 * dat.pm_10) + (1 *dat.pm_25) + (1 * dat.pm_100)) avg_formula
                FROM tbl_datos_sensor_particulas dat
                WHERE dat.id_disp=16001 AND fch_dato  BETWEEN $startDate AND $endDate
                GROUP BY dat.fch_dato::date
                ORDER BY fch_dato ASC");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $key => $value) {
                    $series[] = [
                        'text'       => \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                        "values"     => intval($value['avg_formula'])
                    ];
                }

                return [
                    'series'  => $series,
                    'title'  => 'Concentración de Neblina Acida'
                ];
            }
        } catch (\Throwable $th) {

            $data = [
                'series'     => [],
                'title'      => [],
                'message' => $th->getMessage()
            ];

            return $data;
        }
    }

	public function getDataNeblina (Request $request, $id_disp, $title)
    {
        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId($this->connection_id);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')::timestamp";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                }

                $pg->query("SELECT par.fch_dato,
                par.pm_10, par.pm_25, par.pm_100
                FROM tbl_datos_sensor_particulas par
                WHERE par.fch_dato BETWEEN ".$startDate." AND ".$endDate."
                AND par.id_disp=$id_disp
                AND par.pm_10 IS NOT NULL
                ORDER BY par.fch_dato ASC");

                $data = $pg->fetch_assoc();

                $series = [];

                $v1 = $request->v1 ? doubleval($request->v1) : 0;
                $v2 = $request->v2 && doubleval($request->v2) != 0 ? doubleval($request->v2) : 1;
                $v3 = $request->v3 && doubleval($request->v3) != 0 ? doubleval($request->v3) : 1;
                $v4 = $request->v4 && doubleval($request->v4) != 0 ? doubleval($request->v4) : 1;

                foreach ($data as $key => $value) {
                    $pm_10 = doubleval($value['pm_10']);

                    $pm_25 = doubleval($value['pm_25']);

                    $pm_100 = doubleval($value['pm_100']);

                    $pm = $v1 + ($v2 * $pm_10) + ($v3 * $pm_25) + ($v4 * $pm_100);

                    array_unshift($series, [
                        'pm' => $pm,
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return [
                    'series'     => $series,
                    'title'      => $title,
'query' => title
                ];
            }
        } catch (\Throwable $th) {
            report($th);

            return [
                'series'     => [],
                'title'      => [],
                'message'      => $th->getMessage(),
            ];

            return response()->json(['message' => $th->getMessage()], 500, []);
        }
    }

	public function getDataParticulasAmbiente (Request $request, $id_disp, $title)
    {
        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId($this->connection_id);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')::timestamp";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                }

                $pg->query("SELECT par.fch_dato,
                par.pm_10, par.pm_25, par.pm_100
                FROM tbl_datos_sensor_particulas par
                WHERE par.fch_dato BETWEEN ".$startDate." AND ".$endDate."
                AND par.id_disp=$id_disp
                AND par.pm_10 IS NOT NULL
                ORDER BY par.fch_dato ASC");

                $data = $pg->fetch_assoc();

                $series = [];
                foreach ($data as $key => $value) {
                   // if($value['pm_10'] != 0 && $value['pm_25'] != 0 && $value['pm_100'] != 0){
                        array_unshift($series, [
                            'pm_10' => round(doubleval($value['pm_10']), 2),
                            'pm_25' => round(doubleval($value['pm_25']), 2),
                            'pm_100' => round(doubleval($value['pm_100']) * 1000, 2),
                            'fch_dato' => $value['fch_dato'],
                            'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                        ]);
                    //}
                }

                return [
                    'series'     => $series,
                    'title'      => $title,
                ];
            }
        } catch (\Throwable $th) {
            report($th);
            return [
                'series'     => [],
                'title'      => [],
            ];
            return response()->json(['message' => $th->getMessage()], 500, []);
        }
    }

	public function getDataVoltajeBateria (Request $request, $id_disp, $title)
    {
        if($id_disp == 16002){
            try {
                date_default_timezone_set('America/Santiago');

                $pg = new Connection();

                $pg->setConnectionByConnectionId($this->connection_id);

                if ($pg->connect()) {
                    $from = "listar_fechas(to_char(CURRENT_DATE, 'YYYY-MM-DD 00:00:00'),
                    to_char(CURRENT_DATE, 'YYYY-MM-DD 23:59:59'), 1) fch";
                    if ($request->has('dateRange') && !is_null($request->dateRange)) {
                        $dateRange = \Str::of($request->dateRange)->explode(' - ');
                        $from = "listar_fechas(to_char(CURRENT_DATE, '".TimeHelper::formatDateRange($dateRange[0])."'),
                            to_char(CURRENT_DATE, '".TimeHelper::formatDateRange($dateRange[1])."'), 1) fch";
                    }

                    $pg->query("SELECT analog_input_1, analog_input_2, analog_input_3, fch.fch_dato
                    FROM $from
                    LEFT JOIN  tbl_datos_modulo_gpio sen ON sen.fch_dato=fch.fch_dato
                    AND sen.id_disp=$id_disp
                    ORDER BY fch.fch_dato ASC");

                    $data = $pg->fetch_assoc();

                    $series = [];

                    foreach ($data as $value) {
                        array_unshift($series, [
                            'analog_input_1' => doubleval($value['analog_input_1'] / 1000),
                            'analog_input_2' => doubleval($value['analog_input_2'] / 1000),
                            'analog_input_3' => doubleval($value['analog_input_3'] / 1000),
                            'fch_dato' => $value['fch_dato'],
                            'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                        ]);
                    }

                    return [
                        'series'     => $series
                    ];
                }

            } catch (\Throwable $th) {
                report($th);
                return response()->json(['message' => $th->getMessage()], 500, []);
            }
        }else{
            try {
                $pg = new Connection();

                $pg->setConnectionByConnectionId($this->connection_id);

                if ($pg->connect()) {
                    $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')::timestamp";
                    if ($request->has('range') && !is_null($request->range)) {
                        $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                        $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                    }

                    $pg->query("SELECT fch_dato,
                    volt_bat
                    FROM tbl_datos_bat_sensor_particulas
                    WHERE fch_dato BETWEEN ".$startDate." AND ".$endDate."
                    AND id_disp=$id_disp
                    AND volt_bat IS NOT NULL
                    ORDER BY fch_dato ASC");

                    $data = $pg->fetch_assoc();

                    $series = [];
                    foreach ($data as $key => $value) {
                        if($value['volt_bat'] != 0){
                            array_unshift($series, [
                                'voltaje_bateria' => round(doubleval($value['volt_bat']), 2),
                                'fch_dato' => $value['fch_dato'],
                                'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                            ]);
                        }
                    }

                    return [
                        'series'     => $series,
                        'title'      => $title,
                    ];
                }
            } catch (\Throwable $th) {
                report($th);
                return [
                    'series'     => [],
                    'title'      => [],
                    'message'      => $th->getMessage(),
                ];
            }
        }
    }

	public function getDataGraficaPotenciaMedidor (Request $request)
    {
        try {
            date_default_timezone_set('America/Santiago');

            $pg = new Connection();

            $pg->setConnectionByConnectionId($this->connection_id);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')";
                }

                $pg->query("SELECT fch.fch_dato,
                    med.pot_act_total medidor_kw
                    FROM listar_fechas((".$startDate.")::text, ".$endDate.", 1) fch
                    LEFT JOIN  tbl_datos_medidor_eem_ma400 med ON med.fch_dato=fch.fch_dato AND med.id_disp=10005
                    ORDER BY fch.fch_dato ASC");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $key => $value) {
                    array_unshift($series, [
                        "value"    => doubleval($value['medidor_kw']),
                        'fch_dato' => $value['fch_dato'],
                        'time' => $value['fch_dato'],
                        // 'time'     => strtotime($value['fch_dato']) * 1000
                        // 'time'     => \Carbon\Carbon::parse($value['fch_dato'])->setTimezone('America/Santiago')->timestamp
                    ]);
                }

                return [
                    'series'     => $series,
                    'title'      => 'Potencia Medidor',
                ];
            }
        } catch (\Throwable $th) {
            report($th);
            return [
                'series'     => [],
                'title'      => [],
            ];
            return response()->json(['message' => $th->getMessage()], 500, []);
        }
    }

	public function getDataEnergiaConsumida (Request $request)
    {
        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId($this->connection_id);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                }

                $pg->query("SELECT (EXTRACT (YEAR FROM fch_dato) || '-' || to_char(EXTRACT (MONTH FROM fch_dato)::integer,'00') || '-' || to_char(EXTRACT (DAY FROM fch_dato),'00'))  tiempo,
                ROUND(( MAX(med.ener_act_total) - MIN(med.ener_act_total) )::numeric,0) energia_Consumida_kwh
                FROM tbl_datos_medidor_eem_ma400 med
                WHERE med.id_disp= 10005 AND
                med.fch_dato BETWEEN
                  $startDate
                  AND
                  $endDate
                GROUP BY tiempo
                ORDER BY tiempo ASC");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $key => $value) {
                    $series[] = [
                        'text'       =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['tiempo']))->format('d/m/Y'),
                        "values"     => intval($value['energia_consumida_kwh'])
                    ];
                }

                return [
                    'series'  => $series,
                    'title'  => 'Energía Consumida'
                ];
            }
        } catch (\Throwable $th) {
            report($th);
            return [
                'series'     => [],
                'title'      => [],
            ];
            return response()->json(['message' => $th->getMessage()], 500, []);
        }
    }

    /**
     * Get data getDataVolumenLiquido | VOLUMEN LIQUIDO | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function getDataVolumenLiquido (Request $request)
    {
        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId($this->connection_id);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                }

                $pg->query("SELECT (EXTRACT (YEAR FROM fch_dato) || '-' || to_char(EXTRACT (MONTH FROM fch_dato)::integer,'FM00') || '-' || to_char(EXTRACT (DAY FROM fch_dato),'FM00'))  tiempo,
                ROUND((SUM(flu.vol_flujo ) * 60 )::numeric)  volumen
               FROM tbl_datos_flujometro_krohne_ifc050w flu
               WHERE flu.id_disp= 10004 AND
               flu.fch_dato BETWEEN
                  $startDate
                  AND
                  $endDate
               GROUP BY tiempo
               ORDER BY tiempo ASC");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $key => $value) {
                    $series[] = [
                        'text'       => TimeHelper::formatDateHuman($value['tiempo']),
                        "values"     => intval($value['volumen'])
                    ];
                }

                return [
                    'series'  => $series,
                    'title'  => 'Volumen liquido'
                ];
            }
        } catch (\Throwable $th) {
            report($th);
            return [
                'series'     => [],
                'title'      => [],
            ];
            return response()->json(['message' => $th->getMessage()], 500, []);
        }
    }

    /**
     * CHART PROFUNDIDAD | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function getProfundidad (Request $request, $id_disp)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {
                $pg->query("SELECT fch.fch_dato,
                altura_fluido profundidad --sen.analog_input_1 profundidad
                FROM listar_fechas(to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00'),
                to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'), 1) fch
                LEFT JOIN  tbl_datos_sensor_nivel_hidrostatico sen ON sen.fch_dato=fch.fch_dato AND sen.id_disp=$id_disp
                ORDER BY fch.fch_dato ASC;");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $key => $value) {
                    if($value['profundidad'] != NULL){
                        array_unshift($series, [
                            "value"    => round(doubleval($value['profundidad']), 2),
                            'fch_dato' => $value['fch_dato'],
                            'time'     =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                        ]);
                    }
                }

                return $series;
            }

        } catch (\Throwable $th) {
            report($th);
        }

        return $data;
    }

    /**
     * ULTIMO DATO PROFUNDIDAD GRAN PISCINA | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function ultimoDatoProdundidadGranPiscina (Request $request)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);
            // --ULTIMO DATO PROFUNDIDAD RIO
            if ($pg->connect()) {
                $pg->query("SELECT id_disp, fch_dato,
                altura_fluido profundidad
                --analog_input_1 profundidad
                FROM tbl_datos_sensor_nivel_hidrostatico
                WHERE id_disp=10012
                --AND analog_input_1 IS NOT NULL
                ORDER BY fch_dato DESC LIMIT 1");

                // $data = $pg->fetch_assoc();
                $data = $pg->getResponse();

                $profundidad = (float)$data['profundidad'];
                $data['profundidad'] = number_format($profundidad, 2, '.', '');
                $data['profundidad_porcentaje'] = number_format(($profundidad * 100) / 5, 2, '.', '');
            }
        } catch (\Throwable $th) {
            report($th);
        }

        return $data;
    }

    /**
     * ULTIMO DATO PROFUNDIDAD RIO | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function ultimoDatoProfundidadRio (Request $request)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);
            // --ULTIMO DATO PROFUNDIDAD RIO
            if ($pg->connect()) {
                $pg->query("SELECT id_disp, fch_dato,
                altura_fluido profundidad
                --analog_input_1 profundidad
                FROM tbl_datos_sensor_nivel_hidrostatico
                WHERE id_disp=10010
                --AND analog_input_1 IS NOT NULL
                ORDER BY fch_dato DESC LIMIT 1");

                // $data = $pg->fetch_assoc();
                $data = $pg->getResponse();

                $profundidad = (float)$data['profundidad'];
                $data['profundidad'] = number_format($profundidad, 2, '.', '');
                $data['profundidad_porcentaje'] = number_format(($profundidad * 100) / 5, 2, '.', '');
            }
        } catch (\Throwable $th) {
            report($th);
        }

        return $data;
    }

    /**
     * ULTIMO DATO PROFUNDIDAD PISCINA | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function ultimoProfundidadPiscina (Request $request)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {
                $pg->query("SELECT id_disp, fch_dato,
                altura_fluido profundidad
                --analog_input_1 profundidad
                FROM tbl_datos_sensor_nivel_hidrostatico
                WHERE id_disp=10009
                --AND analog_input_1 IS NOT NULL
                ORDER BY fch_dato DESC LIMIT 1");

                // $data = $pg->fetch_assoc();
                $data = $pg->getResponse();

                $profundidad = (float)$data['profundidad'];
                $data['profundidad'] = number_format($profundidad, 2, '.', '');
                $data['profundidad_porcentaje'] = number_format(($profundidad * 100) / 5, 2, '.', '');
            }
        } catch (\Throwable $th) {
            report($th);
        }

        return $data;
    }

    /**
     * HISTORIAL BOMBAS | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function historialBombas (Request $request)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {
                $pg->query("SELECT fch.fch_dato,
                CASE mod1.digital_output_1 WHEN 1 THEN 20 WHEN 0 THEN 10 ELSE NULL END AS pozo,
                CASE mod2.digital_output_1 WHEN 1 THEN 50 WHEN 0 THEN 40 ELSE NULL END AS piscina,
                CASE mod1.digital_input_2 WHEN 1 THEN 80 WHEN 0 THEN 70 ELSE NULL END AS ac_pozo,
                CASE mod2.digital_input_2 WHEN 1 THEN 110 WHEN 0 THEN 100 ELSE NULL END AS ac_piscina,
                CASE rut.input_digital WHEN 0 THEN 140 WHEN 1 THEN 130 ELSE NULL END AS ac_control_room
                FROM listar_fechas(to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00'),
                    to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'), 1) fch
                LEFT JOIN  tbl_datos_modulo_gpio mod1 ON mod1.fch_dato=fch.fch_dato AND mod1.id_disp=10003 --BOMBA POZO
                LEFT JOIN  tbl_datos_modulo_gpio mod2 ON mod2.fch_dato=fch.fch_dato AND mod2.id_disp=10002 --BOMBA PISCINA
                LEFT JOIN  tbl_datos_teltonika rut ON rut.fch_dato=fch.fch_dato AND rut.id_disp=10006 --ROUTER TELTONIKA
                ORDER BY fch.fch_dato ASC");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $value) {
                    array_unshift($series, [
                        'pozo' => intval($value['pozo']),
                        'piscina' => intval($value['piscina']),
                        'ac_pozo' => intval($value['ac_pozo']),
                        'ac_piscina' => intval($value['ac_piscina']),
                        'ac_control_room' => intval($value['ac_control_room']),
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return [
                    'series' => $series,
                    'title'  => 'Bombas Agua',
                ];
            }
        } catch (\Throwable $th) {
            dd($th);
        }

        return $data;
    }

    /**
     * Get data Tablero Solar | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function getDataTableroSolar (Request $request, $id_disp)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {

                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                }

                $pg->query("SELECT fch_dato, volt_bat
                FROM public.tbl_datos_mppt_epever
                WHERE id_disp=$id_disp AND
                fch_dato BETWEEN
                   $startDate
                   AND
                   $endDate");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $value) {
                    array_unshift($series, [
                        'value' => $value['volt_bat'],
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return $series;
            }

        } catch (\Throwable $th) {
            report($th);
        }

        return $data;
    }

    /**
     * Get data Voltaje Bateria Pozo | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function getDataVoltajeBateriaPozo (Request $request)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {

                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                }

                $pg->query("SELECT fch_dato, analog_input_2
                    FROM public.tbl_datos_modulo_gpio
                    WHERE id_disp=10003 AND digital_input_1 IS NOT NULL
                    AND fch_dato BETWEEN
                        $startDate
                    AND
                        $endDate");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $value) {
                    array_unshift($series, [
                        'value' => doubleval($value['analog_input_2'] / 1000),
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return $series;
            }
        } catch (\Throwable $th) {
            report($th);
        }

        return $data;
    }

    /**
     * Get data Control | Method GET
     *
     * @param Request $request
     * @return array
     */
    public function getDataControl (Request $request)
    {
        $series = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";
                if ($request->has('range') && !is_null($request->range)) {
                    $startDate = "to_char(CURRENT_DATE,'".$request->range['startDate']."')::timestamp";
                    $endDate   = "to_char(CURRENT_TIMESTAMP, '".$request->range['endDate']."')::timestamp";
                }

                $pg->query("SELECT id_disp, fch_dato,
                EXTRACT(epoch FROM (CURRENT_TIMESTAMP - fch_dato)) time_com,
                uptime, signal_dbm, ROUND((analog_input / 1000)::numeric,2 ) volt_bat,
                CASE input_digital WHEN 0 THEN 1 WHEN 1 THEN 0 ELSE input_digital END input_digital,
                output_digital,
                input_digital_isolated,
                600 time_alarm_com, '1' AS nombre_dato
                FROM public.tbl_datos_teltonika
                WHERE id_disp=10006 AND signal_dbm IS NOT NULL AND analog_input IS NOT NULL AND
                    fch_dato BETWEEN
                   $startDate
                   AND
                   $endDate");

                $data = $pg->fetch_assoc();

                foreach ($data as $value) {
                    array_unshift($series, [
                        'value' => doubleval($value['volt_bat']),
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return $series;
            }
        } catch (\Throwable $th) {
            report($th);
        }

        return $series;
    }

    /**
     * Get data Corriente | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function getDataVoltajePanel (Request $request, $id_disp)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";

                $pg->query("SELECT id_disp, fch_dato, volt_panel
                FROM public.tbl_datos_mppt_epever
                WHERE id_disp=$id_disp AND volt_panel IS NOT NULL
                 AND
                    fch_dato BETWEEN
                   $startDate
                   AND
                $endDate");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $value) {
                    array_unshift($series, [
                        'value' => round($value['volt_panel']),
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return $series;
            }
        } catch (\Throwable $th) {
            report($th);
            dd(1);
        }

        return [];
    }

    /**
     * Get data Corriente | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function getDataCorriente (Request $request, $id_disp)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";

                $pg->query("SELECT id_disp, fch_dato, cte_panel, cte_bat
                FROM public.tbl_datos_mppt_epever
                WHERE id_disp=$id_disp AND cte_panel IS NOT NULL AND cte_bat IS NOT NULL
                 AND
                    fch_dato BETWEEN
                   $startDate
                   AND
                $endDate");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $value) {
                    array_unshift($series, [
                        'cte_panel' => round($value['cte_panel']),
                        'cte_bat' => round($value['cte_bat']),
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return $series;
            }
        } catch (\Throwable $th) {
            report($th);
        }

        return [];
    }

        /**
     * Get data potencia | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function getDataPotenciaBateria (Request $request, $id_disp)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";

                $pg->query("SELECT id_disp, fch_dato, pot_panel, volt_bat, pot_bat
                FROM public.tbl_datos_mppt_epever
                WHERE id_disp=$id_disp AND volt_panel IS NOT NULL
                AND fch_dato BETWEEN $startDate AND $endDate");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $value) {
                    array_unshift($series, [
                        'value' => round((float)($value['pot_bat'] / 100), 2),
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return $series;
            }
        } catch (\Throwable $th) {
            report($th);
        }

        return [
            'tablero_gran_piscina' => $data
        ];
    }


        /**
     * Get data potencia | Method GET
     *
     * @param Request $request
     * @return array
     */
	public function getDataPotenciaPanel (Request $request, $id_disp)
    {
        $data = [];

        try {
            $pg = new Connection();

            $pg->setConnectionByConnectionId(11);

            if ($pg->connect()) {
                $startDate = "to_char(CURRENT_DATE,'YYYY-MM-DD 00:00:00')::timestamp";
                $endDate   = "to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')::timestamp";

                $pg->query("SELECT id_disp, fch_dato, pot_panel, volt_bat, pot_bat
                FROM public.tbl_datos_mppt_epever
                WHERE id_disp=$id_disp AND volt_panel IS NOT NULL
                AND fch_dato BETWEEN $startDate AND $endDate");

                $data = $pg->fetch_assoc();

                $series = [];

                foreach ($data as $value) {
                    array_unshift($series, [
                        'value' => round((float)($value['pot_panel'] / 100), 2),
                        'fch_dato' => $value['fch_dato'],
                        'time' =>  \Carbon\Carbon::parse(str_replace(' ', '', $value['fch_dato']))->format('d/m/Y H:i'),
                    ]);
                }

                return $series;
            }
        } catch (\Throwable $th) {
            report($th);
        }

        return [
            'tablero_gran_piscina' => $data
        ];
    }
}