Stats/src/StatsBundle/Controller/OltReportController.php

<?php

namespace StatsBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\Workflow\Exception\ExceptionInterface;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Sonata\AdminBundle\Admin\Pool;
use StatsBundle\Admin\OltAdmin;

class OltReportController extends Controller
{

    /**
     * @Route("admin/stats/olt/{oltServerId}/{oltDeviceId}/report", name="admin_stats_olt_report")
     */
    public function oltReport($oltServerId, $oltDeviceId) {

        $adminPool = $this->get('sonata.admin.pool');

        $filter = array('deviceId' => $oltDeviceId, 'deviceServer' => $oltServerId, 'deviceType' => 'FTTHBundle\Entity\OLT');
        $em = $this->get('doctrine')->getManager();
        $device = $em->getRepository('StatsBundle:Device')->findOneBy($filter);
        $data = $device->jsonExtraData();

        return $this->render('StatsBundle:Device:report.html.twig', array(
            'base_template' => $adminPool->getTemplate('layout'),
            'admin_pool' => $adminPool,
            'admin' => $adminPool->getAdminByAdminCode("stats.admin.olt"),
            'device' => $device,
            'data' => $data
        ));
    }

    public function onuStateCountAction($oltServerId, $oltDeviceId) {
        
        $em = $this->get('doctrine')->getManager();
        $onuStateCount = array();
        $count = $em->getRepository('StatsBundle:Onu')->createQueryBuilder('o')
            ->select('o.status','COUNT(o)')
            ->where('o.deviceServer = :oltServerId')
            ->andWhere('o.oltDeviceId = :oltDeviceId')
            ->setParameter('oltServerId', $oltServerId)
            ->setParameter('oltDeviceId', $oltDeviceId)
            ->groupBy('o.status')
            ->getQuery()->getScalarResult();
        
        $percent_online = $online = $offline = 0;    
        foreach($count as $c) {
            if($c['status']) {
                $status = "Online";
                $color = "#53D356";
                $online = $c[1];
            } else {
                $status = "Offline";
                $color = "#F82929";
                $offline = $c[1];
            }
            
            $onuStateCount[$status] = array('count' => $c[1], 'color' => $color);
        }

        if($offline != 0 || $online != 0) {
            $percent_online = ($online / ($offline + $online)) * 100;
        }

        return $this->render('StatsBundle:Device:Report/onuStateCount.html.twig', array(
            'onuStateCount' => $onuStateCount,
            'percent_online'=> $percent_online
        ));
    }

    public function oltInformationAction($oltServerId, $oltDeviceId) {
        
        $filter = array('deviceId' => $oltDeviceId, 'deviceServer' => $oltServerId, 'deviceType' => 'FTTHBundle\Entity\OLT');
        $em = $this->get('doctrine')->getManager();
        $device = $em->getRepository('StatsBundle:Device')->findOneBy($filter);

        $subName = "d_{$oltDeviceId}_s_{$oltServerId}";
        $redis = $this->get('redis');
        $key = "olt_scan_card_{$subName}";
    
        $dataCached = $redis->get($key);

        $cpu = $memory = array();
        if(isset($dataCached['cpu'])) {$cpu = $dataCached['cpu'];}
        if(isset($dataCached['memory'])) {$memory = $dataCached['memory'];}

        $data = $device->jsonExtraData();
        $load = "75";
        $ramMax = 1024;
        $ram = 500;

        return $this->render('StatsBundle:Device:Report/oltInformation.html.twig', array(
            'data' => $data,
            'load' => $load,
            'ramMax' => $ramMax,
            'ram' => $ram,
            'device' => $device,
            'cpu'  => $cpu,
            'memory' => $memory
        ));
    }

    public function oltCommandsAction($oltServerId, $oltDeviceId, $mark) {
        
        $mark = strtolower($mark);
        $subName = "d_{$oltDeviceId}_s_{$oltServerId}_cmd_{$mark}";

        $result = $targets = array();
        $endpoint = $this->get('endpoint.mysql');

        $targets["onu_stats"] = array("target" => "{$subName}_onu_stats");
        $targets["pon_stats"] = array("target" => "{$subName}_pon_stats");
        $targets["pon_octets"] = array("target" => "{$subName}_pon_octets");
        $targets["pon_scan"] = array("target" => "{$subName}_pon_scan");
        $targets["onu_scan"] = array("target" => "{$subName}_onu_scan");
        $errors = array();
        
        foreach($targets as $index => $t) {
            $search = array('targets' => array(0 => $t), 'maxDataPoints' => 10);
            try {
                $data = $endpoint->get(json_encode($search),'last', true);
            } catch(\Exception $e) {
                $errors[] = array("msg" => "Falla mysql_json_endpoint({$index}): {$e->getMessage()}");
                $data = array();
            }

            if(isset($data[0]) && isset($data[0]['datapoints'])) {
                $points = $data[0]['datapoints']; krsort($points);
                $result[$index] = $points;
            } else {
                $result[$index] = array();
            }
        }

        if($errors) {$this->logReport($oltDeviceId, $oltServerId, $errors);}
        
        return $this->render('StatsBundle:Device:Report/oltCommands.html.twig', array(
            'times' => $result
        ));
    }

    public function oltBandwidthAction($oltServerId, $oltDeviceId) {
        
        $subName = "d_{$oltDeviceId}_s_{$oltServerId}";

        $result = $targets = array();
        $endpoint = $this->get('endpoint.mysql');

        $targets["in_bandwidth"] = array("target" => "{$subName}_inbandwidth_olt");
        $targets["out_bandwidth"] = array("target" => "{$subName}_outbandwidth_olt");
        $errors = array();

        foreach($targets as $index => $t) {
            $search = array('targets' => array(0 => $t), 'maxDataPoints' => 25);
            try {
                $data = $endpoint->get(json_encode($search),'last', true);
            } catch(\Exception $e) {
                $errors[] = array("msg" => "Falla mysql_json_endpoint({$index}): {$e->getMessage()}");
                $data = array();
            }

            if(isset($data[0]) && isset($data[0]['datapoints'])) {
                $points = $data[0]['datapoints']; krsort($points);
                $result[$index] = $points;
            } else {
                $result[$index] = array();
            }
        }

        if($errors) {$this->logReport($oltDeviceId, $oltServerId, $errors);}
        
        return $this->render('StatsBundle:Device:Report/oltBandwidth.html.twig', array(
            'inBand' => $result['in_bandwidth'],
            'outBand' => $result['out_bandwidth']
        ));
    }

    public function ponBandwidthAction($oltServerId, $oltDeviceId) {
        
        $subName = "d_{$oltDeviceId}_s_{$oltServerId}";

        $data = $totals = $result = array();
        $redis = $this->get('redis');

        $key_olt_scan = "olt_scan_pons_{$subName}";
        $key_olt_pon_bandwidth = "olt_bandwidth_pons_{$subName}";
    
        $dataCached = $redis->get($key_olt_scan);
        $bandwidthCached = $redis->get($key_olt_pon_bandwidth);
        $restPercent = $sum = $total = $count = $totalIn = $totalOut = 0;

        foreach($dataCached as $index => $pon) {
            
            if(isset($pon['nexo'])) continue;

            if(isset($bandwidthCached[$index])) {
                $in = number_format($bandwidthCached[$index]['inBand'] / 1048576, 2);
                $out = number_format($bandwidthCached[$index]['outBand'] / 1048576, 2);

                if($in == 0 && $out == 0) continue;

                $count++;
                $t = $in + $out;

                $totalIn += $in;
                $totalOut += $out;
                $total += $t;
                
                $totals[$index] = $t;
                $result[$index] = array('ponPort' => $pon['ponPort'], 'in' => $in, 'out' => $out, 'total' => $t);
            }
        }
        
        /* http://www.color-hex.com/color/ff7f24 */
        $colors = array("#cc651c","#e57220","#ff7f24","#ff8b39","#ff984f","#ffa565","#ffb27b","#ffbf91","#ffcba7","#ffd8bd","#ffe5d3","#fff2e9","#ffffff");
        if($total > 0) {

            arsort($totals);
            $tops = array_slice($totals, 0, 10, true);
            $i = 0;
            foreach($tops as $index => $t) {
                $data[$index] = $result[$index];
                $sum += $t;
                $percent = number_format(($t / $total)*100, 2);
                $data[$index]['percent'] = $percent;
                $data[$index]['color'] = $colors[$i];
                $i++;
            }

            $restPercent = number_format((($total - $sum) / $total)*100, 2);
        }


        /* print_r("<pre>");
        print_r($data);
        print_r($sum.PHP_EOL);
        print_r($total.PHP_EOL);
        print_r($totalIn.PHP_EOL);
        print_r($totalOut.PHP_EOL);
        die; */
        
        return $this->render('StatsBundle:Device:Report/ponBandwidth.html.twig', array(
            'data'  => $data,
            'sum'   => $sum,
            'total' => $total,
            'totalIn'   => $totalIn,
            'totalOut'   => $totalOut,
            'restPercent'   => $restPercent
        ));
    }

    public function oltConsumptionAction($oltServerId, $oltDeviceId) {
        
        $subName = "d_{$oltDeviceId}_s_{$oltServerId}";

        $result = $targets = array();
        $endpoint = $this->get('endpoint.mysql');

        $targets["in_consumption"] = array("target" => "{$subName}_inconsumption_olt");
        $targets["out_consumption"] = array("target" => "{$subName}_outconsumption_olt");
        $errors = array();

        foreach($targets as $index => $t) {
            $search = array('targets' => array(0 => $t), 'maxDataPoints' => 50);
            try {
                $data = $endpoint->get(json_encode($search),'last', true);
            } catch(\Exception $e) {
                $errors[] = array("msg" => "Falla mysql_json_endpoint({$index}): {$e->getMessage()}");
                $data = array();
            }
            
            if(isset($data[0]) && isset($data[0]['datapoints'])) {
                $points = $data[0]['datapoints']; krsort($points);
                $result[$index] = $points;
            } else {
                $result[$index] = array();
            }
        }
        
        if($errors) {$this->logReport($oltDeviceId, $oltServerId, $errors);}

        return $this->render('StatsBundle:Device:Report/oltConsumption.html.twig', array(
            'inCons' => $result['in_consumption'],
            'outCons' => $result['out_consumption']
        ));
    }

    private function logReport($deviceId, $serverId, $errors) {
        $em = $this->get('doctrine')->getManager();
        $device = $em->getRepository('\StatsBundle\Entity\Device')->findOneBy(array('deviceServer' => $serverId, 'deviceType' => 'FTTHBundle\Entity\OLT', 'deviceId' => $deviceId));
        if($device) {
            $tenancyId = $device->getTenancyId();
            $reportManager = $this->get('stats.report.manager');

            foreach($errors as $error) {
                $reportManager->create("FTTHBundle\Entity\OLT", "MYSQL_ENDPOINT_ERROR", $deviceId, $serverId, $tenancyId, $error);
            }
        }
    }

    public function oltReportAction($oltServerId, $oltDeviceId) {
        
        $em = $this->get('doctrine')->getManager();
        
        $reports = $em->getRepository('StatsBundle:Report')->createQueryBuilder('r')
            ->select('r')
            ->where('r.deviceServer = :oltServerId')
            ->andWhere('r.deviceId = :oltDeviceId')
            ->andWhere('r.deviceType = :deviceType')
            ->andWhere('r.created >= :created')
            ->setParameter('oltServerId', $oltServerId)
            ->setParameter('oltDeviceId', $oltDeviceId)
            ->setParameter('deviceType', 'FTTHBundle\Entity\OLT')
            ->setParameter('created', date("Y-m-d 00:00:00",strtotime("-5 days")))
            ->orderBy('r.id', 'DESC')
            ->getQuery()->getResult();

        $results = array();
        foreach($reports as $report) {
            $row = array();
            $row['type'] = $report->getReportType();
            $row['time'] = $report->getCreated()->format('Y-m-d H:i:s');
            $aux = json_decode($report->getExtraData(),true);
            if(isset($aux['msg'])) {
                $row['msg'] = $aux['msg'];
            } else {
                continue;
            }
            $results[] = $row;
        }


        return $this->render('StatsBundle:Device:Report/reportList.html.twig', array(
            'results' => $results
        ));
    }

    public function onuSignalAction($oltServerId, $oltDeviceId) {
        
        $em = $this->get('doctrine')->getManager();
        $onus = $em->getRepository('StatsBundle:Onu')->createQueryBuilder('o')
            ->select('o.txPower','o.rxPowerOlt', 'o.ponSerialNumber')
            ->where('o.deviceServer = :oltServerId')
            ->andWhere('o.oltDeviceId = :oltDeviceId')
            ->setParameter('oltServerId', $oltServerId)
            ->setParameter('oltDeviceId', $oltDeviceId)
            ->getQuery()->getResult();
        
        $colors = $signals = array();

        foreach($onus as $data) {
            if($data['txPower'] == null || $data['rxPowerOlt'] == null) continue;

            $color = $this->getRxColor($data['rxPowerOlt']);

            if(!isset($signals[$color])) $signals[$color] = array();

            $signals[$color][] = array('x' => $data['txPower'],'y' => $data['rxPowerOlt'], 'extraData' => $data['ponSerialNumber'], 'id' => "{$data['ponSerialNumber']}~{$oltDeviceId}~{$oltServerId}");
            
            //$colors[] = $this->getRxColor($data['rxPowerOlt']);
        }
        $_signals = array();
        foreach($signals as $color => $points) {
            $_signals[$color] = json_encode($points);
        }


        return $this->render('StatsBundle:Device:Report/onuSignal.html.twig', array(
            'signals' => $_signals
        ));
    }

    private function getRxColor($value) {


        if($value < -30)
            return "#a00400"; //bordó
        elseif($value >= -30 and $value < -28)
            return "#ff4d3f"; //rojo
        elseif($value >= -28 and $value < -26)
            return "#ffa732"; //amarillo
        elseif($value >= -26 and $value < -20)
            return "#00a65a"; //verde
        elseif($value >= -20 and $value < -15)
            return "#ffa732"; //amarillo
        elseif($value >= -15 and $value < -13)
            return "#ff4d3f"; //rojo
        else
            return "#a00400"; //bordó
    }
    
}