common/modules/product/models/Category.php

<?php

namespace common\modules\product\models;

use common\components\artboxtree\ArtboxTreeBehavior;
use common\components\artboxtree\ArtboxTreeHelper;
use common\modules\rubrication\models\TaxGroup;
use Yii;

use yii\db\ActiveQuery;
use yii\db\Query;
use yii\helpers\VarDumper;

/**
 * This is the model class for table "category".
 *
 * @property integer $category_id
 * @property string $remote_id
 * @property integer $parent_id
 * @property string $path
 * @property integer $depth
 * @property string $image
 * @property string $brand_image
 * @property string $meta_title
 * @property string $meta_desc
 * @property string $meta_robots
 * @property string $seo_text
 * @property string $first_text
 * @property string $second_text
 * @property integer $product_unit_id
 * @property string $alias
 * @property string $h1
 * @property boolean $name
 * @property boolean $new_collection
 * @property Category $parent
 * @property Product[] $products
 * @property ProductUnit $productUnit
 * @property ProductCategory[] $productCategories
 * @property boolean $stock_program
 * @property boolean $on_order
 */
class Category extends \yii\db\ActiveRecord
{
    public $imageUpload;

    public $brandImageUpload;

    public function behaviors()
    {
        return [
            'artboxtree' => [
                'class' => ArtboxTreeBehavior::className(),
                'keyNameGroup' => null,
                'keyNamePath' => 'path',
            ],
            'slug' => [
                'class' => 'common\behaviors\Slug',
                'in_attribute' => 'name',
                'out_attribute' => 'alias',
                'translit' => true
            ],

        ];
    }

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'category';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['name'], 'string'],
            [['parent_id', 'depth', 'product_unit_id'], 'integer'],
            [['path', 'meta_desc', 'h1', 'seo_text'], 'string'],
            [['meta_title', 'image', 'brand_image'], 'string', 'max' => 255],
            [['meta_robots'], 'string', 'max' => 50],
            [['alias', 'name'], 'string', 'max' => 250],
            [['imageUpload', 'brandImageUpload'], 'safe'],
            [['imageUpload', 'brandImageUpload'], 'file', 'extensions' => 'jpg, gif, png'],
            [['first_text', 'second_text'], 'string', 'max' => 255],
            [['new_collection', 'stock_program', 'on_order'], 'boolean'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'category_id' => Yii::t('product', 'Category ID'),
            'parent_id' => Yii::t('product', 'Parent ID'),
            'path' => Yii::t('product', 'Path'),
            'depth' => Yii::t('product', 'Depth'),
            'image' => Yii::t('product', 'Image'),
            'imageUrl' => Yii::t('product', 'Image'),
            'brandImageUrl' => 'Картинка брэнда',
            'meta_title' => Yii::t('product', 'Meta Title'),
            'meta_desc' => Yii::t('product', 'Meta Desc'),
            'meta_robots' => Yii::t('product', 'Meta Robots'),
            'h1' => Yii::t('product', 'h1'),
            'seo_text' => Yii::t('product', 'Seo Text'),
            'product_unit_id' => Yii::t('product', 'Product Unit ID'),
            'alias' => 'url',
            'name' => Yii::t('product', 'Name'),
            'remote_id' => Yii::t('product', 'Remote ID'),
            'first_text' => 'Заголовок',
            'second_text' => 'Подзаголовок',
            'new_collection' => 'Новая коллекция (для коллекций)',
            'brand_image' => 'Картинка бренда',
            'stock_program' => 'Складская программа (для брендов)',
            'on_order' => 'Под заказ (для брендов)',
            'imageUpload' => 'Картинка',
            'brandImageUpload' => 'Картинка (бренда)'
            
        ];
    }

    public static function find()
    {
        return new CategoryQuery(get_called_class());
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getProductUnit()
    {
        return $this->hasOne(ProductUnit::className(), ['product_unit_id' => 'product_unit_id']);
    }

    public function getProducts() {
        return $this->hasMany(Product::className(), ['product_id' => 'product_id'])
            ->viaTable('product_category', ['category_id' => 'category_id']);
    }

    /**
     * Custom method for baccara
     *
     * @return ActiveQuery
     */
    public function getBaccaraProducts() {
        return Product::find()->joinWith('categories')
            ->where([
                'category.category_id' => $this->getChildrenByDepth(2)->column(),
            ]);
    }


    /**
     * @return \yii\db\ActiveQuery
     */
    public function getProductCategories()
    {
        return $this->hasMany(ProductCategory::className(), ['category_id' => 'category_id']);
    }

    public function getBrands(){
        return $this->getProducts()->select('brand.*')->joinWith('brand')->groupBy('brand.brand_id');

    }

    public function getTaxGroupsByLevel($level)
    {
        return $this->hasMany(TaxGroup::className(), ['tax_group_id' => 'tax_group_id'])
            ->viaTable('tax_group_to_category', ['category_id' => 'category_id'])
            ->andWhere(['level' => $level]);
    }

    public function getRemote_category()
    {
        return ArtboxTreeHelper::getArrayField($this->remote_id);
    }

    public function setRemote_category($value)
    {
        if (!empty($value) && is_array($value)) {
            $this->remote_id = ArtboxTreeHelper::setArrayField($value, false);
        }
    }

    public function getImageFile() {
        return empty($this->image) ? '/images/no_photo.png' : Yii::getAlias('@imagesDir/categories/'. $this->image);
    }

    public function getImageUrl()
    {
        return empty($this->image) ? '/images/no_photo.png' : Yii::getAlias('@imagesUrl/categories/' . $this->image);
    }

    /**
     * Gets path to brand's image file
     * @return string
     */
    public function getBrandImageFile() {
        return empty($this->brand_image) ? '/images/no_photo.png' : Yii::getAlias('@imagesDir/categories/'. $this->brand_image);
    }

    /**
     * Gets url of brand's image
     * @return string
     */
    public function getBrandImageUrl()
    {
        return empty($this->brand_image) ? '/images/no_photo.png' : Yii::getAlias('@imagesUrl/categories/' . $this->brand_image);
    }

    public function beforeSave($insert)
    {
        if (parent::beforeSave($insert)) {

            if (empty($this->parent_id))
                $this->parent_id = 0;

            return true;
        }
        return false;
    }
    
    public function getActiveFilters() {


        $query1 = (new Query())
            ->distinct()
            ->select([
                'option_id'
            ])
            ->from('tax_option')
            ->innerJoin('product_variant_option', 'tax_option.tax_option_id = product_variant_option.option_id')
            ->innerJoin('tax_group', 'tax_group.tax_group_id = tax_option.tax_group_id')
            ->innerJoin('product_variant', 'product_variant.product_variant_id = product_variant_option.product_variant_id')
            ->innerJoin('product', 'product.product_id = product_variant.product_id')
            ->innerJoin('product_category', 'product_category.product_id = product.product_id')
            ->where(['product_category.category_id' => $this->getChildrenByDepth(2)->asArray()->column(), 'tax_group.is_filter' => TRUE])
            ->andWhere(['!=', 'product_variant.stock', 0]);

        $query2 = (new Query())
            ->distinct()
            ->select([
                'option_id'
            ])
            ->from('tax_option')
            ->innerJoin('product_option', 'tax_option.tax_option_id = product_option.option_id')
            ->innerJoin('tax_group', 'tax_group.tax_group_id = tax_option.tax_group_id')
            ->innerJoin('product', 'product.product_id = product_option.product_id')
            ->innerJoin('product_category', 'product_category.product_id = product.product_id')
            ->innerJoin('product_variant', 'product_variant.product_id = product.product_id')
            ->where(['product_category.category_id' => $this->getChildrenByDepth(2)->asArray()->column(), 'tax_group.is_filter' => TRUE])
            ->andWhere(['!=', 'product_variant.stock', 0]);
        $query3 = (new Query())
            ->select([
                'tax_option.*',
                'tax_group.*',
                'tax_option.alias as option_alias',
                'tax_group.alias as group_alias',
                'tax_option.value as value',
                'tax_option.sort AS tax_option_sort',
                'tax_group.sort AS tax_group_sort',
            ])
            ->from(['tax_option' ])
            ->where(['tax_option.tax_option_id'=>$query1->union($query2)])
            ->innerJoin('tax_group','tax_group.tax_group_id = tax_option.tax_group_id')
            ->orderBy('tax_option.sort, tax_group.sort');
        return $query3;
    }

    public function getTaxGroupsForMenu()
    {

        $connection = Yii::$app->getDb();
        $command = $connection->createCommand('
                SELECT ton.alias as option_alias, *
                FROM tax_option as ton
                RIGHT JOIN tax_group ON ton.tax_group_id = tax_group.tax_group_id
                RIGHT JOIN tax_group_to_category ON tax_group.tax_group_id = tax_group_to_category.tax_group_id
                WHERE  ton.tax_option_id IN (
                SELECT po.option_id FROM product_option as po
                WHERE po.product_id IN (
                        SELECT product_id FROM product_category WHERE category_id = :category_id
                    )
                )
                AND tax_group.is_menu = true AND tax_group_to_category.category_id = :category_id',
            [
                ':category_id' => $this->category_id

            ]);
        return $command->queryAll();


    }

    public function getChildrenByDepth($depth)
    {
//        VarDumper::dump($this->getAllChildren($depth)
//
//            ->where([
//            'depth' => $depth,
//            'parent_id' => $this->getChildrenAL()
//        ])->asArray()->select(['category_id'])->createCommand()->rawSql, 10, true);
//
        
     
        return $this->getAllChildren($depth)

                    ->where([
                        'depth' => $depth,
                        'parent_id' => $this->getChildrenAL()->select('category_id')->column()
                    ])->asArray()->select(['category_id']);
    }

    public function setTaxGroup($value)
    {
        return $this->tax_group = $value;
    }

    public function getTaxGroup()
    {
        return $this->hasMany(TaxGroup::className(), ['tax_group_id' => 'tax_group_id'])
            ->viaTable('tax_group_to_category', ['category_id' => 'category_id']);
    }

    public function getParent()
    {
        return $this->hasOne(Category::className(), [
            'category_id' => 'parent_id',
        ]);
    }

    public function getGrandParent()
    {
        return $this->hasOne(Category::className(), [
            'category_id' => 'parent_id',
        ])->viaTable('category', [
            'category_id' => 'parent_id',
        ], function($query) { $query->from('category c2'); });
    }
}