blitz/models/Statistics.php

<?php

namespace app\models;

use Yii;
use yii\base\Model;
use yii\db\Query;
use yii\helpers\ArrayHelper;


/**
 * @property array $subscriptions
 * */

class Statistics extends Model
{

    public $month;
    private $colors = [];

    public function init()
    {
        parent::init();
        foreach(Category::find()->all() as $category){
            $this->colors[$category->id] = $this->getRandomColor();
        }
    }

    public function rules()
    {
        return [
            [['month'], 'date', 'format' => 'php:Y.m'],
            [['month'], 'default', 'value' => date('Y.m', time())],
        ];
    }
    public function attributeLabels()
    {
        return [

            'month' => Yii::t('app', 'Month'),
        ];
    }



    public function searchUsers($params = []){
        $this->load($params);

        if(!$this->validate()){
            return [];
        }

        $select = "DAY(from_unixtime(created_at)) as day, COUNT(*) as count ";

        $data = (new Query())->select($select)
            ->from('user')
            ->andWhere(['month(FROM_UNIXTIME(created_at))' => explode('.', $this->month)[1]])
            ->andWhere(['year(FROM_UNIXTIME(created_at))' => explode('.', $this->month)[0]])
            ->groupBy('day')
            ->all();
        $countDayInMonth = cal_days_in_month(CAL_GREGORIAN, explode('.', $this->month)[1],explode('.', $this->month)[0]);
        $data = fill(ArrayHelper::map($data,'day','count'), $countDayInMonth);


        return $data;
    }

    public function searchAuctions($params = []){
        $this->load($params);

        if(!$this->validate()){
            return [];
        }
        $data = [];

        foreach(Auctions::statusNames() as $i=>$label){
            $data[] = [
                'data' => $this->selectAuctions($i),
                'label' => $label,
                'borderColor' => $this->getRandomColor(),
                'pointBackgroundColor' => $this->getRandomColor(),
                'pointBorderColor' => "#fff",
                'pointHoverBackgroundColor' => "#fff",
                'pointHoverBorderColor' => $this->getRandomColor(),
            ];
        }
        return $data;

    }

    public function searchCategory($params = []){
        $this->load($params);

        if(!$this->validate()){
            return [];
        }
        $data = [];
        $categories = Category::find()
            ->leftJoin('lots', '`lots`.`category_id` = `category`.`id`')
            ->where('lots.id is not null')
            ->groupBy(['category_id'])
            ->all();

        foreach ($categories as $category){
            $data['data'][] = $this->selectCategory($category->id);
            $data['labels'][] = $category->name;
            $data['backgroundColor'][] = $this->colors[$category->id];
        }
        return $data;
    }

    public function getSubscriptions(){
        $categories = Category::find()
            ->leftJoin('subscriptions', '`subscriptions`.`category_id` = `category`.`id`')
            ->where('subscriptions.id is not null')
            ->all();
        $subscriptions = Subscriptions::find()
            ->select(['category_id', 'count' => 'count(user_id)'])
            ->asArray()
            ->groupBy('category_id')->all();
        foreach($subscriptions as $i => $subscription){
            $data['data'][] = $subscription['count'];
            $data['labels'][] = $categories[$i]->name;
            $data['backgroundColor'][] = $this->colors[$categories[$i]->id];
        }
        return $data;
    }

    public function userBiddings(){
       $bidStatuses = ['Поданные', 'Одобренные', 'Отклоненные'];

        $data = [];

      $biddingData = Bidding::find()->select(['status', 'count' => 'count(user_id)'])
          ->groupBy(['status'])
          ->where(['user_id' => Yii::$app->user->id])
          ->asArray()
          ->all();

        foreach ($biddingData as $i => $item) {
            $data['data'][] = $item['count'];
            $data['labels'][] = $bidStatuses[$item['status']];
            $data['backgroundColor'][] = $this->getRandomColor();
        }
        return $data;
    }

    public function userMailing()
    {
        $categories = Category::find()
            ->leftJoin('subscriptions', '`subscriptions`.`category_id` = `category`.`id`')
            ->where(['user_id' => Yii::$app->user->id])
            ->all();

        $userMailing = Subscriptions::find()
            ->select(['category_id', 'count' => 'count(category_id)'])
            ->groupBy(['category_id'])
            ->where(['user_id' => Yii::$app->user->id])
            ->asArray()
            ->all();

        foreach($userMailing as $i => $mailing){
            $data['data'][] = $mailing['count'];
            $data['labels'][] = $categories[$i]->name;
            $data['backgroundColor'][] = $this->colors[$categories[$i]->id];
        }
        return $data;

    }

   /* public function searchUserAuctions(){
        $aucStatuses = ['Все','Выигранные','Отклоненные'];

        $data = [];

        $auctions = Auctions::find()
            ->where(['user_id' => Yii::$app->user->id])
            ->asArray()
            ->all();

        return $data;
    }*/

    private function selectAuctions($type){
        $data = [];
        $select = "DAY(date_start) as day, COUNT(*) as count ";

        $data = (new Query())->select($select)
            ->from('auctions')
            ->andWhere(['month(date_start)' => explode('.', $this->month)[1]])
            ->andWhere(['status' => $type])
            ->andWhere(['year(date_start)' => explode('.', $this->month)[0]])
            ->groupBy('day')
            ->all();
        $countDayInMonth = cal_days_in_month(CAL_GREGORIAN, explode('.', $this->month)[1],explode('.', $this->month)[0]);
        $data = fill(ArrayHelper::map($data,'day','count'), $countDayInMonth);
        return $data;
    }

    private function selectCategory($type){
        $data = [];
        $select = "COUNT(*) as count ";

        $data = (new Query())->select($select)
            ->from('auctions')
            ->leftJoin('lots', '`lots`.`id` = `auctions`.`lot_id`')
            ->leftJoin('category', '`category`.`id` = `lots`.`category_id`')
            ->andWhere(['month(date_start)' => explode('.', $this->month)[1]])
            ->andWhere(['lots.category_id' => $type])
            ->andWhere('category.id is not null')
            ->andWhere(['year(date_start)' => explode('.', $this->month)[0]])
            ->count();

        return $data;
    }

    private function getRandomColor(){
        $letters = ['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'];
        $color = '#';
        for ($i = 0; $i < 6; $i++) {
            $color .= $letters[random_int(0, 15)];
        }
        return $color;
    }

}