代码之家 › 专栏 › 技术社区 › Odyssee

php/laravel:创建chartdata,如果没有数据提交到数据库,则返回0

chart.js eloquent laravel php

Odyssee · 技术社区 · 7 年前

我正在尝试从数据库返回的数据创建图表。数据存储在数据库中,此表中可以有多个图表。由唯一值标识。(rigname)

array:102 [â¼
    0 => array:6 [â¼
      "id" => 2002
      "user_id" => "1"
      "rigname" => "home"
      "data" => "{"hashrate":12.364,"pool":"eu1.ethermine.org:4444","shares":{"valid":"2","invalid":"0"},"gpus":[{"temperature":"56","fanSpeed":"56","hashrates":12.364}]}"
      "created_at" => "2018-02-23 10:24:50"
      "updated_at" => "2018-02-23 10:24:50"
    ],
1 => array:6 [â¼
      "id" => 2019
      "user_id" => "1"
      "rigname" => "server"
      "data" => "{"hashrate":76.612,"pool":"eu1.ethermine.org:4444","shares":{"valid":"736","invalid":"2"},"gpus":[{"temperature":"65","fanSpeed":"67","hashrates":19.804},{"temp â¶"
      "created_at" => "2018-02-23 10:25:56"
      "updated_at" => "2018-02-23 10:25:56"
    ]

正如您所看到的,根据rigname,有两种可能的图表。我想在图表上显示两个Rig的hashrate值。这是可行的,但是,当一个钻机停止时,没有数据从该钻机提交到数据库。图表中未显示该装备的0值,正在使用最新的已知数据。(duh)

我通过如下修改查询来解决此问题:

$res[$value] = json_decode(MinerStatistics::where('rigname', $value)
            ->where('created_at','>=', \Carbon\Carbon::now()->subMinutes(20))
            ->orderBy('created_at', 'desc')
            ->get()
            ->toJson(), true
       );

现在,当钻机停止,我查询数据库时,我只得到过去x分钟的数据。这是在foreach中完成的,该foreach构建了一个新的多维数组,其中的数据按rigname排序。

当我重新启动钻机并开始向数据库报告时,这会引发一个新问题。图表上的值不是在当前时间戳处继续,而是在钻机停止时继续。通常,我会将上次报告的数据和以前报告的数据的时间戳与仍在运行的钻机的数据进行比较,并在每次运行的钻机报告时返回0,但它们报告的时间戳不同(都在8秒内)

如何修改返回的mysql数据,最终得到如下结果:

array:2 [â¼
  "home" => array:150 [
      0 => array:6 [â¼
        "id" => 2300
        "user_id" => "1"
        "rigname" => "home"
        "data" => "{"hashrate":12.359,"pool":"eu1.ethermine.org:4444","shares":{"valid":"5","invalid":"0"},"gpus":[{"temperature":"57","fanSpeed":"57","hashrates":12.359}]}"
        "created_at" => "2018-02-23 10:44:42"
        "updated_at" => "2018-02-23 10:44:42"
        ]
    1 => array:6 [â¼
      "id" => 2298
      "user_id" => "1"
      "rigname" => "home"
      "data" => "{"hashrate":12.367,"pool":"eu1.ethermine.org:4444","shares":{"valid":"5","invalid":"0"},"gpus":[{"temperature":"57","fanSpeed":"57","hashrates":12.367}]}"
      "created_at" => "2018-02-23 10:44:34"
      "updated_at" => "2018-02-23 10:44:34"
    ]
    2 => array:6 [â¼
      "id" => 2296
      "user_id" => "1"
      "rigname" => "home"
      "data" => "{"hashrate":12.367,"pool":"eu1.ethermine.org:4444","shares":{"valid":"5","invalid":"0"},"gpus":[{"temperature":"57","fanSpeed":"57","hashrates":12.367}]}"
      "created_at" => "2018-02-23 10:44:26"
      "updated_at" => "2018-02-23 10:44:26"
    ]

    ...
    Server stops, no data, should continue like this

    3 => [false] ,
    4 => [false] ,
    5 => [false] 
    ...
    rig reports again to the database

    36 => array:6 [â¼
      "id" => 2296
      "user_id" => "1"
      "rigname" => "home"
      "data" => "{"hashrate":12.367,"pool":"eu1.ethermine.org:4444","shares":{"valid":"5","invalid":"0"},"gpus":[{"temperature":"57","fanSpeed":"57","hashrates":12.367}]}"
      "created_at" => "2018-02-23 10:44:26"
      "updated_at" => "2018-02-23 10:44:26"
    ],
  ],
  "server" => array:150 [â¼ // This keeps running, or visa versa
    0 => array:6 [â¼
      "id" => 2301
      "user_id" => "1"
      "rigname" => "server"
      "data" => "{"hashrate":76.62,"pool":"eu1.ethermine.org:4444","shares":{"valid":"749","invalid":"2"},"gpus":[{"temperature":"64","fanSpeed":"67","hashrates":19.778},{"tempe â¶"
      "created_at" => "2018-02-23 10:44:44"
      "updated_at" => "2018-02-23 10:44:44"
    ]
    1 => array:6 [â¼
      "id" => 2299
      "user_id" => "1"
      "rigname" => "server"
      "data" => "{"hashrate":76.68,"pool":"eu1.ethermine.org:4444","shares":{"valid":"749","invalid":"2"},"gpus":[{"temperature":"64","fanSpeed":"65","hashrates":19.815},{"tempe â¶"
      "created_at" => "2018-02-23 10:44:36"
      "updated_at" => "2018-02-23 10:44:36"
    ]
    2 => array:6 [â¼
      "id" => 2297
      "user_id" => "1"
      "rigname" => "server"
      "data" => "{"hashrate":76.644,"pool":"eu1.ethermine.org:4444","shares":{"valid":"749","invalid":"2"},"gpus":[{"temperature":"64","fanSpeed":"65","hashrates":19.793},{"temp â¶"
      "created_at" => "2018-02-23 10:44:28"
      "updated_at" => "2018-02-23 10:44:28"
    ]
    3 => array:6 [â¼
      "id" => 2295
      "user_id" => "1"
      "rigname" => "server"
      "data" => "{"hashrate":76.105,"pool":"eu1.ethermine.org:4444","shares":{"valid":"749","invalid":"2"},"gpus":[{"temperature":"64","fanSpeed":"65","hashrates":19.793},{"temp â¶"
      "created_at" => "2018-02-23 10:44:20"
      "updated_at" => "2018-02-23 10:44:20"
    ]
    4 => array:6 [â¼
      "id" => 2293
      "user_id" => "1"
      "rigname" => "server"
      "data" => "{"hashrate":76.557,"pool":"eu1.ethermine.org:4444","shares":{"valid":"749","invalid":"2"},"gpus":[{"temperature":"64","fanSpeed":"65","hashrates":19.78},{"tempe â¶"
      "created_at" => "2018-02-23 10:44:12"
      "updated_at" => "2018-02-23 10:44:12"
    ]
    5 => array:6 [â¼
      "id" => 2291
      "user_id" => "1"
      "rigname" => "server"
      "data" => "{"hashrate":76.562,"pool":"eu1.ethermine.org:4444","shares":{"valid":"749","invalid":"2"},"gpus":[{"temperature":"64","fanSpeed":"65","hashrates":19.794},{"temp â¶"
      "created_at" => "2018-02-23 10:44:04"
      "updated_at" => "2018-02-23 10:44:04"
    ]
  ]
]

我编写的当前代码:

public function index()
{
    // Get the statistics from the database reported by the miner
    // Group them by the rig name
    $stats = MinerStatistics::where('user_id', Auth::user()->id)
        ->where('created_at','>=', Carbon::now()->subMinutes(20))
        ->orderBy('created_at', 'desc')
        ->take(300)
        ->get()
        ->groupBy('rigname');

    // Get the rig names
    $rigs = (object) [];
    foreach($stats as $key => $value) {
        $rigs->$key = $key;
    }

    // For each rig, get the last known stats between now and 10 seconds ago
    $table = (object) [];
    foreach($rigs as $key => $value) {
        $tableData = $stats[$value]
            ->where('created_at','>=', Carbon::now()->subSeconds(10));

        if (isset($tableData[0]) && $tableData !== []) {
            $table->$key = (object) $tableData[0];
            $table->$key->data = json_decode($tableData[0]['data'], true);
        } 
    }

    return view('rigs', [
        'rigs' => $table,
        'chart' => $this->chart($stats)
    ]);
}

    public function chart($data) 
    {
        $p = [];
        foreach($data as $key => $value) {
            $x = [];
            $time = [];
            foreach($value as $k => $v) {
                $time[] = \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $v['created_at'])->format('H:i:s');
                $x[] = json_decode($v['data'], true);
            }

            $hashrate = [];
            foreach($x as $k => $v){
                $hashrate[] = $v['hashrate'];
            }
            $p[$key] = [ 'time' => $time, 'hashrate' => $hashrate ];
        }

        $datasets = [];
        $n = 0;
        foreach($p as $key => $value) {
            $colors = [
                "0, 188, 212",
                "244, 67, 54",
                "255, 152, 0"
            ];

            $datasets[] = $this->chartData('Hashrate: ' . $key, array_reverse($value['hashrate']), $colors[$n++]);
        }
        dd($time);
        $chart = app()->chartjs
            ->name('lineChartTest')
            ->type('line')
            ->size(['width' => '100%', 'height' => '20%'])
            ->labels(array_reverse($time))
            ->datasets($datasets)
            ->optionsRaw("{ 
                scales: {
                    yAxes: [{
                        display: true,
                        ticks: {
                            suggestedMin: 0,    // minimum will be 0, unless there is a lower value.
                            beginAtZero: true   // minimum value will be 0.
                        }
                    }]
                }
            }");

        return $chart;
    }

    public function chartData($label, $data, $color)
    {
        return [
            "label" => $label,
            'backgroundColor' => "rgba(" . $color . ", 0)",
            'borderColor' => "rgba(" . $color . ", 0.7)",
            "pointBorderColor" => "rgba(" . $color . ", 0, 0.7)",
            "pointBackgroundColor" => "rgba(" . $color . ", 0.7)",
            "pointHoverBackgroundColor" => "#fff",
            "pointHoverBorderColor" => "rgba(220,220,220,1)",
            'data' => $data,
        ];
    }

编辑:我已经找到了答案,请检查以下内容。

1 回复 | 直到 7 年前

Odyssee 7 年前

我做了一个数组 $timeLabels 此数组包含从当前时间到x分钟/小时前的时间戳,间隔为x分钟。

然后我在我的装备上循环,并将创建的\u at与时间标签中的值进行比较。如果在相同的范围内(+/-10秒),则来自集合的数据将进入一个以timeLabel为键的数组。当timeLabel没有值时,它会将0指定为数据。这实际上是可行的,但需要一些调整。

完整示例:

<?php

namespace App\Http\Controllers\Rigs;

use Auth;
use Carbon\Carbon;
use App\MinerStatistics;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class RigController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth');
    }

    /**
     * Show the rigs dashboard.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        // Get the statistics from the database reported by the miner
        // Group them by the rig name
        $stats = MinerStatistics::where('user_id', Auth::user()->id)
            ->where('created_at','>=', Carbon::now()->subMinutes(60))
            ->orderBy('created_at', 'desc')
            ->take(300)
            ->get()
            ->groupBy('rigname');

        // Get the rig names
        $rigs = (object) [];
        foreach($stats as $key => $value) {
            $rigs->$key = $key;
        }

        // For each rig, get the last known stats between now and 10 seconds ago
        $table = (object) [];
        foreach($rigs as $key => $value) {
            $tableData = $stats[$value]
                ->where('created_at','>=', Carbon::now()->subSeconds(10));

            if (isset($tableData[0]) && $tableData !== []) {
                $table->$key = (object) $tableData[0];
                $table->$key->data = json_decode($tableData[0]['data'], true);
            } 
        }

        return view('rigs', [
            'rigs' => $table,
            'chart' => $this->chart($stats)
        ]);
    }

    public function chart($data) 
    {
        $start = Carbon::now('Europe/Brussels');
        $end = Carbon::now('Europe/Brussels')->subMinutes(60);
        $timeLabels = $this->createTimeRange($end, $start);
        $chartData = (object) [];
        // Go over each returned rig
        foreach($data as $key => $value) {
            $arr = array(); // Create an empty array to store our results
            // Go over all our timeLabels
            foreach(array_reverse($timeLabels) as $t => $time) {
                // Create a range to match the incomming created_at and timeLabels
                // Needs finetuning!!
                $start = Carbon::createFromFormat('H:i', $time)->subSeconds(30)->format('H:i:s');
                $end = Carbon::createFromFormat('H:i', $time)->addSeconds(30)->format('H:i:s');

                foreach ($value as $k => $v) {
                    // Transform created_at to the same format as $start and $end
                    $created = Carbon::createFromFormat('Y-m-d H:i:s', $v['created_at'])->format('H:i:s');
                    if($created >= $start && $created <= $end) {
                        if(!isset($v->data['hashrate'])){
                            $hashrate_ = json_decode($v->data,true)['hashrate'];
                        } else {
                            $hashrate_ = $v->data['hashrate'];
                        }

                        $arr[$time] = $hashrate_;
                    } 
                }

                // If the set timeLabel has no value set to 0
                // Rig is not running...
                if (!isset($arr[$time])) {
                    $arr[$time] = '0';
                }

            }
            $chartData->$key = $arr;
        }

        $datasets = [];
        $n = 0;
        foreach($chartData as $key => $value) {
            $data = [];
            foreach ($value as $k => $v) {
                $data[] = $v;
            }
            $colors = [
                "0, 188, 212",
                "244, 67, 54",
                "255, 152, 0"
            ];

            $datasets[] = $this->chartData('Hashrate: ' . $key, array_reverse($data), $colors[$n++]);
        }


        $chart = app()->chartjs
            ->name('lineChartTest')
            ->type('line')
            ->size(['width' => '100%', 'height' => '20%'])
            ->labels($timeLabels)
            ->datasets($datasets)
            ->optionsRaw("{ 
                scales: {
                    yAxes: [{
                        display: true,
                        ticks: {
                            suggestedMin: 0,    // minimum will be 0, unless there is a lower value.
                            beginAtZero: true   // minimum value will be 0.
                        }
                    }]
                }
            }");

        return $chart;
    }

    public function chartData($label, $data, $color)
    {
        return [
            "label" => $label,
            'backgroundColor' => "rgba(" . $color . ", 0)",
            'borderColor' => "rgba(" . $color . ", 0.7)",
            "pointBorderColor" => "rgba(" . $color . ", 0, 0.7)",
            "pointBackgroundColor" => "rgba(" . $color . ", 0.7)",
            "pointHoverBackgroundColor" => "#fff",
            "pointHoverBorderColor" => "rgba(220,220,220,1)",
            'data' => $data,
        ];
    }

    private function createTimeRange($start, $end, $interval = '5 mins', $format = '24') {
        $startTime = strtotime($start); 
        $endTime   = strtotime($end);

        $returnTimeFormat = ($format == '12')?'g:i A':'G:i';

        $current   = time(); 
        $addTime   = strtotime('+'.$interval, $current); 
        $diff      = $addTime - $current;

        $times = array(); 
        while ($startTime < $endTime) { 
            $times[] = date($returnTimeFormat, $startTime); 
            $startTime += $diff; 
        } 
        $times[] = date($returnTimeFormat, $startTime); 
        return $times; 
    }
}

如果我这样做,我可以创建一个专用类,以便重用此逻辑。返回给图表的数据现在的格式如下:(正是我所需要的)

{#3436 â¼
  +"home": array:13 [â¼
    "17:47" => 13.44
    "17:42" => 13.417
    "17:37" => 13.439
    "17:32" => 13.436
    "17:27" => 13.438
    "17:22" => "0"
    "17:17" => "0"
    "17:12" => "0"
    "17:07" => "0"
    "17:02" => "0"
    "16:57" => "0"
    "16:52" => "0"
    "16:47" => "0"
  ]
  +"server": array:13 [â¼
    "17:47" => "0"
    "17:42" => 76.373
    "17:37" => 76.514
    "17:32" => 76.565
    "17:27" => 76.486
    "17:22" => "0"
    "17:17" => "0"
    "17:12" => "0"
    "17:07" => "0"
    "17:02" => "0"
    "16:57" => "0"
    "16:52" => "0"
    "16:47" => "0"
  ]
}