<?php

namespace app\controllers;

use Yii;
use yii\filters\AccessControl;
use yii\web\Controller;
use yii\filters\VerbFilter;
use app\models\LoginForm;
use app\models\ContactForm;
use app\models\CDR;
use app\models\CDRSearch;

class SiteController extends Controller
{
    /**
     * @inheritdoc
     */
    public function behaviors()
    {
        return [
            'access' => [
                'class' => AccessControl::className(),
                //'only' => ['logout'],
                'rules' => [
                    [
                        'actions' => ['logout'],
                        'allow' => true,
                        'roles' => ['@'],
                    ],
                    [
                        'actions' => ['index', 'daily-quality', 'search', 'weekly-quality'],
                        'allow' => true,
                        'roles' => ['?'],
                    ],
                ],
            ],
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'logout' => ['post'],
                ],
            ],
        ];
    }

    /**
     * @inheritdoc
     */
    public function actions()
    {
        return [
            'error' => [
                'class' => 'yii\web\ErrorAction',
            ],
            'captcha' => [
                'class' => 'yii\captcha\CaptchaAction',
                'fixedVerifyCode' => YII_ENV_TEST ? 'testme' : null,
            ],
        ];
    }

    /**
     * Displays homepage.
     *
     * @return string
     */
    public function actionIndex($month=null)
    {

        $chartTitle = '';
        $chartLabels = [];
        $chartData = [];

        $time = time();
        $setMonth = date('m');

        $searchModel = new CDRSearch();
        $params = Yii::$app->request->queryParams;

        if(!is_null($month))
        {
            $time = mktime(date("H"), date("i"), date("s") , $month);
            $setMonth = $month;
            //$params[$searchModel->formName()]['dateTimeOrigination'] = $time;
        }

        $chartTitle = Yii::t('app', 'Calls Quality for {month}: Delay >150ms, Jitter>40ms and Packet loss > 20%', ['month'=>date('F, Y', $time)]);

        $query = CDR::find();

        $query->innerJoinWith('cMRs')
        ->where([ '>', 'CMR.jitter',40])
        ->andwhere(['>', 'CMR.latency', 150])
        ->andwhere(['>', 'round(CMR.numberPacketsLost/CMR.numberPacketsSent*100)', 20]) ;

        $dataProvider = $searchModel->search($query, $params, $setMonth);
        //echo '<pre>'; print_r($dataProvider->getModels());  echo '</pre>';  die();

        $rows = (new \yii\db\Query())
            ->select(['AVG(CMR.jitter) AS jitter','AVG(CMR.latency) AS latency','AVG(round(CMR.numberPacketsLost/CMR.numberPacketsSent*100)) AS packetslost', 'FROM_UNIXTIME(dateTimeOrigination, "%d %M %Y") thedate'])
            ->from('CDR')
            ->innerJoin('CMR', 'CDR.globalCallID_callId=CMR.globalCallID_callId')
            ->where(['MONTH(FROM_UNIXTIME(dateTimeOrigination))'=>$setMonth])
            ->andwhere([ '>', 'jitter',40])
            ->andwhere(['>', 'latency', 150])
            ->andwhere(['>', 'round(CMR.numberPacketsLost/CMR.numberPacketsSent*100)', 20])
            ->groupBy('DATE(FROM_UNIXTIME(dateTimeOrigination))')
            ->all();

        $ipData = [];
        //place holder
        $ipData[-1]='Clear the Search';
        $encodedIps = Yii::$app->db->createCommand('SELECT DISTINCT origIpAddr FROM CDR')->queryColumn();
        foreach($encodedIps as $ip)
        {
            $ipData[$ip] = Yii::$app->converter->decimalToIP($ip);
        }

        $chartData['jitter'] = [];
        $chartData['latency'] = [];
        $chartData['packetslost'] = [];

       foreach($rows as $row)
       {
            $chartLabels[] = $row['thedate'];
            $chartData['jitter'][] = $row['jitter'];
            $chartData['latency'][] = $row['latency'];
            $chartData['packetslost'][] = $row['packetslost'];
       }

        $dataset =  [
            ['title'=>'Jitter','data'=>$chartData['jitter']],
            ['title'=>'Delay','data'=>$chartData['latency']],
            ['title'=>'Packets Loss','data'=>$chartData['packetslost']],
        ];

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
            'chartTitle' => $chartTitle,
            'chartLabels' => $chartLabels,
            'dataset' => $dataset,
            'ipData' => $ipData,
            'time' => $time,
        ]);
    }


    public function actionDailyQuality($day, $month, $year)
    {
        $chartTitle = '';
        $chartLabels = [];
        $chartData = [];

        $searchModel = new CDRSearch();
        $params = Yii::$app->request->queryParams;

        $time = mktime(0, 0, 0, $month, $day, $year);
        //$params[$searchModel->formName()]['dateTimeOrigination'] = $time;

        $chartTitle = Yii::t('app', 'Calls Quality for {month}: : Delay >150ms, Jitter>40ms and Packet loss > 20%', ['month'=>date('F d, Y', $time)]);

        $query = CDR::find();

        $query->innerJoinWith('cMRs')
        ->where([ '>', 'jitter',40])
        ->andwhere(['>', 'latency', 150])
        ->andwhere(['>', 'round(CMR.numberPacketsLost/CMR.numberPacketsSent*100)', 20]) ;

        $dataProvider = $searchModel->search($query, $params, $month, $year, $day);

        $rows = Yii::$app->db->createCommand('SELECT `values` AS call_hour,DATE_FORMAT(FROM_UNIXTIME(CDR.dateTimeOrigination), "%kH") as label_hour, COALESCE(AVG(CMR.jitter), 0) AS jitter, COALESCE(AVG(CMR.latency), 0) AS latency, COALESCE(AVG(round(CMR.numberPacketsLost/CMR.numberPacketsSent*100)), 0) AS numberPacketsLost FROM CDR INNER JOIN CMR ON CMR.globalCallID_callId = CDR.globalCallID_callId RIGHT JOIN Reference ON HOUR(FROM_UNIXTIME(CDR.dateTimeOrigination))=`values`  AND MONTH(FROM_UNIXTIME(CDR.dateTimeOrigination))=:month AND YEAR(FROM_UNIXTIME(CDR.dateTimeOrigination))=:year AND DAY(FROM_UNIXTIME(CDR.dateTimeOrigination))=:day AND jitter>40 AND latency>150 AND round(CMR.numberPacketsLost/CMR.numberPacketsSent*100)>20 GROUP BY call_hour ORDER BY call_hour ASC')
               ->bindValues([':month'=>$month, ':year'=>$year, ':day'=>$day])
               ->queryAll();

          // echo '<pre>'; print_r($rows);  echo '</pre>';  die();

        $ipData = [];
        //place holder
        $ipData[-1]='Clear the Search';
        $encodedIps = Yii::$app->db->createCommand('SELECT DISTINCT origIpAddr FROM CDR')->queryColumn();
        foreach($encodedIps as $ip)
        {
            $ipData[$ip] = Yii::$app->converter->decimalToIP($ip);
        }

        //data sets
        $chartLabels = [];
        $jitter = [];
        $latency = [];
        $numberPacketsLost = [];

       foreach($rows as $row)
       {
            $chartLabels[] = $row['label_hour'];
            $jitter[] = $row['jitter'];
            $latency[] = $row['latency'];
            $numberPacketsLost[] = $row['numberPacketsLost'];
       }
        //set up data
        $datasets = [
            //['title'=>'Call ID','data'=>$callId],
            ['title'=>'Jitter','data'=>$jitter],
            ['title'=>'Latency','data'=>$latency],
            ['title'=>'Packets Lost','data'=>$numberPacketsLost],
        ];


        return $this->render('daily', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
            'chartTitle' => $chartTitle,
            'chartLabels' => $chartLabels,
            'chartData' => $datasets,
            'ipData' => $ipData,
            'time' => $time
        ]);
    }

    public function actionWeeklyQuality($week=null, $year=null)
    {
        $chartTitle = '';
        $chartLabels = [];
        $chartData = [];

        $week = is_null($week) ? date('W') : $week;
        $year = is_null($year) ? date('Y') : $year;


        $startTimeOfWeek = strtotime($year.'W'.str_pad($week, 2, 0, STR_PAD_LEFT));
        $endTimeOfWeek = strtotime($year.'W'.str_pad($week, 2, 0, STR_PAD_LEFT).' +6 days');

        $startOfWeek = date("M d", $startTimeOfWeek);
        $endOfWeek = date("d", $endTimeOfWeek);
        $chartTitle = Yii::t('app', 'Calls Quality for week {n} ({week})', ['n'=>$week, 'week'=>$startOfWeek.' - '.$endOfWeek]);

        //echo "month:".date('n', $startTimeOfWeek).", year:".date('Y', $startTimeOfWeek).", start:".date('d', $startTimeOfWeek).", end:".date('d', $endTimeOfWeek);
        //die();

        $rows = Yii::$app->db->createCommand('SELECT `values` AS days,COALESCE(DATE_FORMAT(FROM_UNIXTIME(CDR.dateTimeOrigination), "%d"), -1) as label_day, COALESCE(AVG(CMR.jitter), 0) AS jitter, COALESCE(AVG(CMR.latency), 0) AS latency, COALESCE(AVG(round(CMR.numberPacketsLost/CMR.numberPacketsSent*100)), 0) AS numberPacketsLost FROM CDR INNER JOIN CMR ON CMR.globalCallID_callId = CDR.globalCallID_callId RIGHT JOIN Reference ON WEEKDAY(FROM_UNIXTIME(CDR.dateTimeOrigination))=`values`  AND MONTH(FROM_UNIXTIME(CDR.dateTimeOrigination))=:month AND YEAR(FROM_UNIXTIME(CDR.dateTimeOrigination))=:year AND DAY(FROM_UNIXTIME(CDR.dateTimeOrigination))>= :start AND DAY(FROM_UNIXTIME(CDR.dateTimeOrigination))<=:end WHERE Reference.`values`<7 GROUP BY days ORDER BY days ASC')
               ->bindValues([':month'=>intval(date('n', $startTimeOfWeek)), ':year'=>intval(date('Y', $startTimeOfWeek)), ':start'=>intval(date('d', $startTimeOfWeek)), ':end'=>intval(date('d', $endTimeOfWeek))])
               ->queryAll();

        //print_r($rows); die();

        //normalize arrays
        $rowsNormalized = [];
        foreach($rows as $row)
        {
            $rowChanged = $row;
            if($rowChanged['label_day']==-1)
            {
                $dayTime = strtotime($year.'W'.str_pad($week, 2, 0, STR_PAD_LEFT).' +'.$rowChanged['days'].' days');
                $rowChanged['label_day'] = date('d', $dayTime);
            }
            $rowsNormalized[] = $rowChanged;
        }

        //echo '<pre>'; print_r($rowsNormalized);  echo '</pre>';  die();

        $ipData = [];
        //place holder
        $ipData[-1]='Clear the Search';
        $encodedIps = Yii::$app->db->createCommand('SELECT DISTINCT origIpAddr FROM CDR')->queryColumn();
        foreach($encodedIps as $ip)
        {
            $ipData[$ip] = Yii::$app->converter->decimalToIP($ip);
        }

        //data sets
        $chartLabels = [];
        $jitter = [];
        $latency = [];
        $numberPacketsLost = [];

       foreach($rowsNormalized as $row)
       {
            $chartLabels[] = $row['label_day'];
            $jitter[] = $row['jitter'];
            $latency[] = $row['latency'];
            $numberPacketsLost[] = $row['numberPacketsLost'];
       }
        //set up data
        $datasets = [
            //['title'=>'Call ID','data'=>$callId],
            [
                'title'=>'Jitter',
                'data'=>$jitter,
                'fillColor' =>'rgba(37,116,168,0.2)',
                'strokeColor' =>'rgba(37,116,168,1)',
                'pointColor' =>'rgba(37,116,168,1)',
                'pointStrokeColor' =>'rgba(255,255,255,1.00)',
            ],
            [
                'title'=>'Latency','data'=>$latency,
                'fillColor' =>'rgba(204,46,59,0.2)',
                'strokeColor' =>'rgba(204,46,59,1)',
                'pointColor' =>'rgba(204,46,59,1)',
                'pointStrokeColor' =>'rgba(255,255,255,1.00)',
            ],
            [
                'title'=>'Packets Lost',
                'data'=>$numberPacketsLost,
                'fillColor' =>'rgba(80,124,75,0.2)'
            ],
        ];


        return $this->render('weekly', [
            'chartTitle' => $chartTitle,
            'chartLabels' => $chartLabels,
            'chartData' => $datasets,
            'ipData' => $ipData,
            'time' => $startTimeOfWeek
        ]);
    }

    public function actionSearch()
    {
        $searchModel = new CDRSearch();

        $dataProvider = $searchModel->search(null, Yii::$app->request->queryParams, date('m'));

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

    /**
     * Login action.
     *
     * @return string
     */
    public function actionLogin()
    {
        if (!Yii::$app->user->isGuest) {
            return $this->goHome();
        }

        $model = new LoginForm();
        if ($model->load(Yii::$app->request->post()) && $model->login()) {
            return $this->goBack();
        }
        return $this->render('login', [
            'model' => $model,
        ]);
    }

    /**
     * Logout action.
     *
     * @return string
     */
    public function actionLogout()
    {
        Yii::$app->user->logout();

        return $this->goHome();
    }

    /**
     * Displays contact page.
     *
     * @return string
     */
    public function actionContact()
    {
        $model = new ContactForm();
        if ($model->load(Yii::$app->request->post()) && $model->contact(Yii::$app->params['adminEmail'])) {
            Yii::$app->session->setFlash('contactFormSubmitted');

            return $this->refresh();
        }
        return $this->render('contact', [
            'model' => $model,
        ]);
    }

    /**
     * Displays about page.
     *
     * @return string
     */
    public function actionAbout()
    {
        return $this->render('about');
    }
}