Yii2 echo variable outside dataprovider to gridview

Question

<?php
$query1 = (new \yii\db\Query())
        ->select(["DATE_FORMAT(approvedate, '%m-%Y') as c_date", 'sum(totalhours) as total', 'count(sku) as sku'])
        ->from('sku3d')
        ->where(['status' => 'Approved'])->orWhere(['status' => 'Cancelled'])
        ->groupBy('c_date', 'status');

        $dataProvider1 = new ActiveDataProvider([
            'query' => $query1,
    ]);

        echo GridView::widget([
    'dataProvider' => $dataProvider1,
    'columns' => [
        'c_date:text:Month',
         [
        'header'=>'Cancelled SKU',
        'value' => function($data) {
              return Sku3d::getCancel();
         }
    ],
        'sku:text:Total Sku',
        'total:text:Total hours',

    ]
]); 

?>

My model:

public function getCancel()
{
    return (new \yii\db\Query())
    ->select(["DATE_FORMAT(approvedate, '%m-%Y') as c_date"])
    ->from('sku3d')
    ->where(['status' => 'Cancelled'])->groupBy('c_date')->count();
    }

I used the code above to count all the approved products and cancelled products as total. I need to count how many cancelled sku each month and list it in another columnm, now it list all without group by month. Example: In november there are 1 cancelled sku (2 hours) and 1 approved sku (4 hours) and in december there are 1 approved sku, no cancelled, it should show:

Month   Cancelled   Sku  Hours
11-2017    1         2     6
12-2017    0         1     3

Right now it showing:

Month   Cancelled   Sku  Hours
11-2017    1         2     6
12-2017    1         1     3

I'm stuck here, please advise. Thanks.

Kalu Kalu · Accepted Answer · 2017-12-14T08:57:13

in your view:

echo GridView::widget([
    'dataProvider' => $dataProvider1,
    'columns' => [
        'c_date:text:Month',
        [
            'header'=>'any title',
            'value' => function($data) {
                  return ModelName::getCountSomething();
             }
        ],
        'sku:text:Total Sku',
        'total:text:Total hours',
    ]
]);

In your Model:

public static function getCountSomething()
{
    return (new \yii\db\Query())
    ->select('*')
    ->from('sku3d')
    ->where(['status' => 'Cancelled'])
    ->count();

}

something like that.

Yii2 echo variable outside dataprovider to gridview

1 Answers