Как вывести вычисляемые поля в gridview?

[umrbek79]

Привет народ.

Я новичок в Yii2. Делаю маленький сайт. База данных основана на базе Nortwind, просто я добавил входящую и исходящую счет-фактуры (миграции прилагаю).
Все модели и CRUD ы создал, фольтрация, создания, datepicker ы - все настроил - работает.

Проблема в отчете. Точнее в выводе вычисляемых полей в грид.

Сам запрос в SQL:
SELECT d.DogovorID,
d.DogovorNum,
d.DogovorSumma,
s.CompanyName,
coalesce(faktur.faktursumma,0) AS faktursumma,
COALESCE(oplata.oplatasumma, 0) AS oplatasumma,
(coalesce(faktur.faktursumma,0) - COALESCE(oplata.oplatasumma, 0)) AS dolg
FROM Dogovor AS d
LEFT JOIN Suppliers AS s
ON s.SupplierID = d.SupplierID
LEFT JOIN (
SELECT p.DogovorID,
SUM(COALESCE(pd.UnitPrice, 0) * COALESCE(pd.Quantity, 0)) AS
faktursumma
FROM Prixods AS p
LEFT JOIN [Prixod Details] AS pd
ON pd.PrixodID = p.PrixodID
GROUP BY
p.DogovorID
) faktur
ON faktur.DogovorID = d.DogovorID
LEFT JOIN (
SELECT o.OplataDogovor,
SUM(COALESCE(o.OplataSumma, 0)) AS oplatasumma
FROM Oplata AS o
WHERE o.OplataDate < DATEADD(DAY, 1, @DolgData)
GROUP BY
o.OplataDogovor
) oplata
ON oplata.OplataDogovor = d.DogovorID
WHERE d.DogovorINOUT = 'IN'
ORDER BY
d.DogovorNum
**************************************** конец запроса
вот эту таблицу я хотел вывести в грид. Как это мне реализовать?

P.S.:Прошу сильно не пинать - это мой первый сайт на Yii2

[ElisDN]

Код: Выделить всё

$dataProvider = new SqlDataProvider(['sql' => new Expression('SELECT d.DogovorID, ... ORDER BY d.DogovorNum')]);

[umrbek79]

Спасибо, Дмитрий, за ответ.

Код из твоего сообщения я вставил в dolg\view\index.php, получил ошибку Method yii\db\Expression::__toString() must return a string value

Контролер, модель, модель поиска и вьюху вложил.

Дмитрий, я смотрел твои уроки по ютуб, отлично объясняешь. По твоим урокам я и начал изучать Yii.
Не мог бы ты пошагово кратко объяснить начинающему, как создать страничку с гридом, содержащим такие вычисляемые поля, как в моем случае. Я полазил в интернете и не нашел нигде такого объяснения, как именно с нуля создавать модель, контроллер и вид для вычисляемых полей.

Хотел сделать вьюху типа этого



Заранее премного благодарен.

модель

Код: Выделить всё

<?php

namespace app\models;

use Yii;
use yii\db\Query;

class Dolg extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */

    public static function tableName()
    {
        return '{{%dogovor}}';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['dogovor_summa', 'faktursumma', 'oplatasumma', 'dolg'], 'integer'],
            [['dogovor_num', 'company_name'], 'string', 'max' => 255],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'dogovor_num' => 'Номер договора',
            'dogovor_summa' => 'Сумма договора',
            'company_name' => 'Поставщик',
            'faktursumma' => 'Сумма счет-фактур',
            'oplatasumma' => 'Оплаченная сумма',
            'dolg' => 'Долг',
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getCustomer()
    {
        return $this->hasOne(Customers::className(), ['id' => 'customer_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getSupplier()
    {
        return $this->hasOne(Suppliers::className(), ['id' => 'supplier_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getOplatas()
    {
        return $this->hasMany(Oplata::className(), ['dogovor_id' => 'id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getOrders()
    {
        return $this->hasMany(Orders::className(), ['dogovor_id' => 'id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getPrixods()
    {
        return $this->hasMany(Prixods::className(), ['dogovor_id' => 'id']);
    }

    /**
     * @inheritdoc
     * @return \app\models\query\DolgQuery the active query used by this AR class.
     */
    public static function find()
    {
        return new \app\models\query\DolgQuery(get_called_class());
    }
}

модель поиска

Код: Выделить всё

<?php

namespace app\models\search;

use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use app\models\Dolg;
use yii\db\Query;

/**
 * DogovorSearch represents the model behind the search form of `app\models\Dogovor`.
 */
class DolgSearch extends Dolg
{
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [[['id', 'dogovor_summa'], 'integer'],
            [['dogovor_num', 'company_name', 'faktursumma', 'oplatasumma', 'dolg'], 'safe'],];

    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $query = Dolg::find();

        // add conditions that should always apply here

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

        //$this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        $fakturquery = (new Query())->select(['p.dogovor_id,
                                                       SUM(COALESCE(pd.UnitPrice, 0) * COALESCE(pd.Quantity, 0)) AS faktursumma'])
                                            ->from('prixods p')
                                            ->leftjoin('prixod-details pd', 'pd.Prixod_id = p.id')
                                            ->groupBy('p.dogovor_id');
        $oplataquery = (new Query())->select(['o.dogovor_id, SUM(COALESCE(o.summa, 0)) AS oplatasumma'])
                                    ->from('oplata o')
                                    ->where('o.date < 2018-01-01') //:data',[':data'=>$this->data])
                                    ->groupBy('o.dogovor_id');

        $query = (new Query)
            ->select(['d.id,
                       d.Dogovor_num,
                       d.Dogovor_summa,
                       s.Company_name,
                       coalesce(faktur.faktursumma,0) AS faktursumma,
                       COALESCE(oplata.summa, 0)  AS oplatasumma,
                       (coalesce(faktur.faktursumma,0) - COALESCE(oplata.summa, 0)) AS dolg'])
            ->from('Dogovor d')
            ->leftJoin('suppliers s', 's.id = d.Supplier_id')
            ->leftJoin(['faktur'=>$fakturquery], 'faktur.dogovor_id=d.id')
            ->leftJoin(['oplata'=>$oplataquery], 'oplata.dogovor_id=d.id')
            ->where(['LIKE', 'd.DogovorINOUT', 'IN'])
            ->orderBy('d.dogovor_num');

        $query->andFilterWhere(['id' => $this->id, 'dogovor_date' => $this->dogovor_date, 'dogovor_summa' => $this->dogovor_summa, 'customer_id' => $this->customer_id, 'supplier_id' => $this->supplier_id,]);

        $query->andFilterWhere(['like', 'dogovor_num', $this->dogovor_num])->andFilterWhere(['like', 'dogovor_type', $this->dogovor_type])->andFilterWhere(['like', 'dogovor_inout', $this->dogovor_inout]);

        return $dataProvider;
    }
}

Контроллер

Код: Выделить всё

<?php

namespace app\controllers;

use Yii;
use app\models\Dolg;
use app\models\search\DolgSearch;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;

class DolgController extends Controller
{
    public function actionIndex()
    {
        // return $this->render('index');

        $searchModel = new DolgSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'dataProvider' => $dataProvider,
            'model' => $searchModel
        ]);
    }


    public function actionSearch()
    {
        $dataProvider = Dolg::search();

        //...
    }

}

dolg\view\index.php

Код: Выделить всё

<?php

use yii\helpers\Html;
use yii\grid\GridView;
use kartik\date\DatePicker;
use yii\db\Query;
use yii\data\SqlDataProvider;
use yii\db\Expression;

/* @var $this yii\web\View */
/* @var $searchModel app\models\search\DolgSearch */
/* @var $dataProvider yii\data\ActiveDataProvider */


$this->title = 'Долги';
$this->params['breadcrumbs'][] = $this->title;
?>
<div class="dolg-index">

    <h1><?= Html::encode($this->title) ?></h1>
    <?php // echo $this->render('_search', ['model' => $searchModel]);
    //$this->load($params);
    $fakturquery = (new Query())->select(['p.dogovor_id,
    SUM(COALESCE(pd.UnitPrice, 0) * COALESCE(pd.Quantity, 0)) AS faktursumma'])
    ->from('prixods p')
    ->leftjoin('prixod-details pd', 'pd.Prixod_id = p.id')
    ->groupBy('p.dogovor_id');
    $oplataquery = (new Query())->select(['o.dogovor_id, SUM(COALESCE(o.summa, 0)) AS oplatasumma'])
    ->from('oplata o')
    ->where('o.date < 2018-01-01') //:data',[':data'=>$this->data])
    ->groupBy('o.dogovor_id');

    $query = (new Query)
    ->select(['d.id,
    d.Dogovor_num,
    d.Dogovor_summa,
    s.Company_name,
    coalesce(faktur.faktursumma,0) AS faktursumma,
    COALESCE(oplata.summa, 0)  AS oplatasumma,
    (coalesce(faktur.faktursumma,0) - COALESCE(oplata.summa, 0)) AS dolg'])
    ->from('Dogovor d')
    ->leftJoin('suppliers s', 's.id = d.Supplier_id')
    ->leftJoin(['faktur'=>$fakturquery], 'faktur.dogovor_id=d.id')
    ->leftJoin(['oplata'=>$oplataquery], 'oplata.dogovor_id=d.id')
    ->where(['LIKE', 'd.DogovorINOUT', 'IN'])
    ->orderBy('d.dogovor_num');
    $dataProvider = new SqlDataProvider(['sql' => new Expression($query)]);
    ?>

    <?= GridView::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],

            // 'id',
            'dogovor_num',
            'dogovor_summa',
            'company_name',
            'faktursumma',
            'oplatasumma',
            'dolg',

            ['class' => 'yii\grid\ActionColumn'],
        ],
    ]); ?>
</div>

[ElisDN]

Если используете голый SQL, то new SqlDataProvider(['sql' => $sql]). Если Query, то new ActiveDataProvider(['query' => $query]).

Остальное в гриде никак не отличается:

Код: Выделить всё

'columns' => [
    ...
    [
        'label' => 'Номер договора',
        'attribute' => 'dogovor_num',
    ],
    ...
],

И переместите этот код из представления в контроллер или в метод search..

[umrbek79]

Спасибо за ответ, Дмитрий.

Перенес код запроса в контроллер в actionSearch
Выходит ошибка: Getting unknown property: app\models\search\DolgSearch::company_name

[ElisDN]

Company_name
company_name

[umrbek79]

Имена исправил во вьюшке.
Теперь ошибка в модели
Getting unknown property: app\models\Dolg::Company_name
(даже с маленькой буквой такая же ошибка)

[ElisDN]

Добаьте переменные для всех вычисляемых полей:

Код: Выделить всё

class DolgSearch extends Dolg
{
    public $Company_name;
    ...
}

[umrbek79]

Добавил, таблица появилась, но...

# Номер договора Сумма договора Поставщик Сумма счет-фактур Оплаченная сумма Долг
1 10 1000000 (не задано) (не задано) (не задано) (не задано)
2 11 1000000 (не задано) (не задано) (не задано) (не задано)
3 12 2000000 (не задано) (не задано) (не задано) (не задано)
4 23 (не задано) (не задано) (не задано) (не задано) (не задано)

вот в таком виде.
Почти во всех договорах есть оплата, кроме последнего. Они не показаны. Или нужно составить запрос по другому?

[umrbek79]

В index контроллера перенес этот запрос

Controller:

Код: Выделить всё

 public function actionIndex()
    {
        // return $this->render('index');
        $fakturquery = (new Query())->select(['p.dogovor_id,
    SUM(COALESCE(pd.Unit_price, 0) * COALESCE(pd.Quantity, 0)) AS faktursumma'])->from('prixods p')->leftjoin('prixod_details pd', 'pd.prixod_id = p.id')->groupBy('p.dogovor_id');
        $oplataquery = (new Query())->select(['oplata.dogovor_id, SUM(COALESCE(oplata.summa, 0)) AS oplatasumma'])->from('oplata')->where('oplata.date < 2018-03-01')//:data',[':data'=>$this->data])
            ->groupBy('oplata.dogovor_id');

        $query = (new Query())->select(['d.id,
    d.dogovor_num,
    d.dogovor_summa,
    s.company_name,
    coalesce(faktur.faktursumma,0) AS faktursumma,
    COALESCE(oplata.oplatasumma, 0)  AS oplatasumma,
    (coalesce(faktur.faktursumma,0) - COALESCE(oplata.oplatasumma, 0)) AS dolg'])->from('Dogovor d')->leftJoin('suppliers s', 's.id = d.Supplier_id')->leftJoin(['faktur' => $fakturquery], 'faktur.dogovor_id=d.id')->leftJoin(['oplata' => $oplataquery], 'oplata.dogovor_id=d.id')->where(['LIKE', 'd.dogovor_inout', 'IN'])->orderBy('d.dogovor_num');
        $dataProvider = new ActiveDataProvider(['query' => $query]);

        $searchModel = new DolgSearch();
        //$dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', ['dataProvider' => $dataProvider, 'model' => $searchModel, 'searchModel' => $searchModel,]);
    }

поля исправил.
В итоге появилась таблица


Как проверить работоспособность подзапросов?

[Nex-Otaku]

Панель отладки откройте.

[umrbek79]

Вот панель отладки

[ElisDN]

Вот панель отладки

Теперь смотрите, верно ли запросы построены. Или копируйте в PhpMyAdmin и проверяйте целиком и по частям.