jeudi 26 août 2021

Query with empty results laravel eloquent

I´m traying to create statistics from my database. I´m changing application and before statistics was created from call table. But i change this and i was created a new table statistics and have this column:

id, id_ployed, call_id, created_at(nullable), updated_at(nullable)

and data example for example:

52,60,88988,4
53,60,88989,10
54,60,88990,6
62,9,88999,1

i have this function in my controller, but return empty data and it´s imposible:

$estadosLlamadas = EstadoLlamada::orderBy('desc')->get();

        if(isset($request->fromDate) && isset($request->toDate)){
            $fromDate = Carbon::parse($request->get('fromDate'));
            $toDate = Carbon::parse($request->get('toDate'));

            $fromDate = $fromDate->format('Y-m-d');
            $toDate = $toDate->format('Y-m-d');

        }else{
            $fromDate = new Carbon('first day of this month');
            $toDate = new Carbon('last day of this month');

            $fromDate = $fromDate->format('Y-m-d');
            $toDate = $toDate->format('Y-m-d');
        }

        $teleoperadoras = auth()->user()->whereIs('teleoperadora')->activos()->select(['id', 'nombre'])->orderBy('nombre', 'desc')->get();
        
        $array = [
            'toDate'   => $toDate,
            'fromDate' => $fromDate,
            'nombresEstados' => $estadosLlamadas->pluck('desc')->toArray(),
            'coloresEstados' => $estadosLlamadas->pluck('hex')->toArray()
        ];

        $query = Estadisticas::query()
            ->whereDate('estadisticas.created_at', '<=', $toDate)
            ->whereDate('estadisticas.created_at', '>=', $fromDate)
            ->whereIn('estadisticas.id_empleado', $teleoperadoras->pluck('id'))
            ->join('users', 'estadisticas.id_empleado', '=', 'users.id')->latest('estadisticas.updated_at')->get();


        foreach($teleoperadoras as $teleoperadora) {
            $array['teleoperadoras'][] = $teleoperadora->nombre;
            $array['id_teleoperadoras'][] = $teleoperadora->id;
            $array[$teleoperadora->id]['resultados'] = [];
            $array['llamadas'][] = $query->where('id_empleado', $teleoperadora->id)->count();

            $array['llamadasTodo'][$teleoperadora->id] = $query->where('id_empleado', $teleoperadora->id);

            foreach($estadosLlamadas as $estado) {
                $array[$teleoperadora->id]['resultados'][] = $query->where('id_empleado', $teleoperadora->id)
                                                            ->where('id_estado', $estado->id)->count();
            }
        }

        $array['nllamadas'] = $query->count();
        $array['fromDate'] = $fromDate . " 00:00:00";
        $array['toDate'] = $toDate . " 23:59:59";
        $roleUser = auth()->user()->getRoles()->first();

        return [
            'datos' => $array, 'estados' => $estadosLlamadas,
            'teleoperadoras' => $teleoperadoras, 'roleUser' => $roleUser,
        ];

this fill statics with chartJS:

$.ajax({
            type: 'GET',
            url: "",
            data: { 'fromDate': fromDate, 'toDate': toDate },
            success: function(response){
                console.log(response);

                $("#loadMe").modal('hide');

                var totalCall = 0;
                let totalCallByOperator = response["datos"]["llamadas"];

                $("#totalCall").append(response.datos.nllamadas)

                // append all teleoperator in view with calls number
                response["datos"]["teleoperadoras"].forEach(function(teleoperadora, index) {
                    $("#teleoperadoras").append('<b class="teleoperadora">'+teleoperadora + ": " + totalCallByOperator[index] +'</b> <br>');
                });

                // generate general statistics
                var ctx = document.getElementById('canvas1').getContext('2d');

                var myChart = new Chart(ctx, {
                    type: 'bar',
                
                    data: {
                        labels: response["datos"]["teleoperadoras"],
                        datasets: [{
                            label: 'Total de llamadas por operadora',
                            data: totalCallByOperator,
                            backgroundColor: [
                            'rgba(255, 99, 132, 0.2)',
                            'rgba(255, 159, 64, 0.2)',
                            'rgba(255, 205, 86, 0.2)',
                            'rgba(75, 192, 192, 0.2)',
                            'rgba(54, 162, 235, 0.2)',
                            'rgba(153, 102, 255, 0.2)',
                            'rgba(201, 203, 207, 0.2)'
                            ],
                            borderColor: [
                            'rgb(255, 99, 132)',
                            'rgb(255, 159, 64)',
                            'rgb(255, 205, 86)',
                            'rgb(75, 192, 192)',
                            'rgb(54, 162, 235)',
                            'rgb(153, 102, 255)',
                            'rgb(201, 203, 207)'
                            ],
                            borderWidth: 1
                        }]
                    },
                    options: {
                        scales: {
                            y: {
                                beginAtZero: true
                            }
                        }
                    }// end options

                }); // end chart object
            },
            error: function(xhr){
                alert(xhr.responseText);
            }
        });

and response from my controller its:

{datos: {9: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]},…}, estados: [,…],…}
datos: {9: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]},…}
9: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
11: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
22: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
23: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
24: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
25: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
26: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
49: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
50: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
60: {resultados: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
coloresEstados: ["#d26a5c", "#f3b760", "#46c37b", "#343a40", "#6c757d", "#d26a5c", "#d26a5c", "#5c80d1", "#70b9eb",…]
fromDate: "2021-08-01 00:00:00"
id_teleoperadoras: [9, 23, 24, 60, 22, 49, 11, 50, 25, 26]
llamadas: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
llamadasTodo: {9: [], 11: [], 22: [], 23: [], 24: [], 25: [], 26: [], 49: [], 50: [], 60: []}
nllamadas: 682
nombresEstados: ["ANULADA", "AUSENTE", "CONFIRMADA", "CONFIRMADA/ANULADA", "CONFIRMADA/AUSENTE", "ERRONEO NO EXISTE",…]
teleoperadoras: ["ARANZAZU SOLIS SANCHEZ", "CRISTINA LOPEZ UBRIC", "CRISTINA MORALES FERNANDEZ",…]
toDate: "2021-08-31 23:59:59"
estados: [,…]
roleUser: "admin"
teleoperadoras: [{id: 9, nombre: "ARANZAZU SOLIS SANCHEZ"}, {id: 23, nombre: "CRISTINA LOPEZ UBRIC"},…]

array "resultados" it´s where should be data to my statistics. This function it was ok before, but now not work.

My model its:

class Estadisticas extends Model
{
    protected $table = 'estadisticas';

    protected $primarykey = 'id';

    public $timestamps = false;

    protected $fillable = [
        'id_empleado', 'id_llamada', 'id_estado', 'created_at', 'updated_at'
    ];


    public function teleoperadora()
    {
        return $this->hasOne('App\User', 'id', 'id_empleado')->withDefault();
    }

    public function estado()
    {
        return $this->hasOne('App\EstadoLlamada', 'id', 'id_estado');
    }   
}

i hope that anybody can help me. thanks for read and sorry for my english



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire