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
