catch_nbfd_admin/catch/report/controller/Report.php

<?php

namespace catchAdmin\report\controller;

use catcher\base\CatchRequest as Request;
use catcher\CatchResponse;
use catcher\base\CatchController;
use catchAdmin\report\model\Report as ReportModel;
use think\facade\Env;
use think\facade\Db;
class Report extends CatchController
{

    protected $reportModel;
    
    public function __construct(ReportModel $reportModel)
    {
        $this->reportModel = $reportModel;
    }
    
    /**
     * 首页数量数据
     * @param Request $request 
     */
    public function homePageCountData(Request $request)
    {
        $start_time = date('Y-m-d 00:00:00',time());
        $end_time = date('Y-m-d 23:59:59',time());

        
        $host=    Env::get('oracle.hostname',  '127.0.0.1');
        $port=    Env::get('oracle.hostport',  '1521');
        $instance_name=    Env::get('oracle.instance',  'ORCL');
        $username=    Env::get('oracle.username',  'root');
        $password=    Env::get('oracle.password',  'root');
        $conn = oci_connect($username, $password, $host.':'.$port.'/'.$instance_name,'AL32UTF8');

        if (!$conn) {
            $e = oci_error();
            trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
        }

        $sql='SELECT count(*) as TOTAL FROM DSSC2.W_DW_RF_RECORD   WHERE   RF_DATE >= to_date(\''. $start_time.'\', \'yyyy-mm-dd hh24:mi:ss\') AND RF_DATE <= to_date(\''.$end_time.'\', \'yyyy-mm-dd hh24:mi:ss\')   ORDER BY RF_DATE DESC';
        // 查询并渲染
    
        $stid = oci_parse($conn, $sql);
        oci_define_by_name($stid, 'TOTAL', $passCount);
        oci_execute($stid);
        oci_fetch($stid);

        oci_free_statement($stid);
        oci_close($conn);
        $alarmCount=Db::table('control_alarm')->whereBetweenTime('created_at', $start_time,$end_time)->count();
        $data = array(
            'passCount'=>(string)$passCount,
            'alarmCount'=>(string)$alarmCount,
        );
        return CatchResponse::success($data);
    }

    /**
     * 基站增长趋势数据
     * @param Request $request 
     */
    public function stationGrowthTrendData(Request $request)
    {
        $data = $this->reportModel->getStationGrowthTrendData() ?: [];
        
        return CatchResponse::success($data);
    }

    /**
     * 用户设备增长趋势数据
     * @param Request $request 
     */
    public function deviceGrowthTrendData(Request $request)
    {
        $data = $this->reportModel->getDeviceGrowthTrendData() ?: [];
        
        return CatchResponse::success($data);
    }

    /**
     * 过车总量统计
     * @param Request $request 
     */
    public function vehiclePassingCount(Request $request){
        $params=$request->param();
        $start_time=strtotime($params['timeRange'][0]);
        $end_time=strtotime($params['timeRange'][1]);
        $xAxisData=[];
        $yAxisData=[];
        for($i=$start_time;$i<$end_time+24*3600;$i+=24*3600){
            $time1=date('Y-m-d',$i);
            $time2=date('Y-m-d',$i);
            $xAxisData[]=date('Y-m-d',$i);
            // $cond['RF_DATE']=['timeRange',$time1,$time2];
            // $count=queryOracleCount('DSSC2.W_DW_RF_RECORD',$cond);
            // $count=mt_rand(0,99999);
            $count=Db::table('station_passing')->where('date','between',[$time1,$time2])->sum('num');
            $yAxisData[]=$count;
     
        }   
        
        // var_dump($end_time);
        return CatchResponse::success(['xAxisData'=>$xAxisData,'yAxisData'=>$yAxisData]);
    }
    
    /**
     * 区域过车统计
     * @param Request $request 
     */
    public function areaDeviceCollect(Request $request){

        $params=$request->param();
        $start_time=date('Y-m-d',strtotime($params['timeRange'][0]));
        $end_time=date('Y-m-d',strtotime($params['timeRange'][1]));
        $xAxisData=[];
        $yAxisData=[];
        $list=Db::table('station_passing')
                        ->where('date','between',[$start_time,$end_time])
                        ->field('address,sum(num) as count')
                        ->group('address')
                        ->order('count desc')
                        ->select()->toArray();
        foreach($list as $val){
            $xAxisData[]=$val['address'];
            $yAxisData[]=$val['count'];
        }        

        // $host=    Env::get('oracle.hostname',  '127.0.0.1');
        // $port=    Env::get('oracle.hostport',  '1521');
        // $instance_name=    Env::get('oracle.instance',  'ORCL');
        // $username=    Env::get('oracle.username',  'root');
        // $password=    Env::get('oracle.password',  'root');
     
    //     $conn = oci_connect('dssc3', 'dssc3', '61.175.203.188:6521/DSSC','AL32UTF8');

    //     if (!$conn) {
    //         $e = oci_error();
    //         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    //     }

    //    $sql='SELECT a.RF_ID,count(a.RF_FLAGID) num,b.DEVICE_NAME FROM DSSC2.W_DW_RF_RECORD a join DSSC2.ADM_DEV b ON a.RF_ID=b.LOGIN_NAME  WHERE  RF_DATE >= to_date(\''.$start_time.'\', \'yyyy-mm-dd hh24:mi:ss\') AND RF_DATE <= to_date(\''.$end_time.'\', \'yyyy-mm-dd hh24:mi:ss\')  GROUP BY a.RF_ID,b.DEVICE_NAME ORDER BY num desc';
    //     // var_dump($sql);
    //     // 查询并渲染
    //     $stid = oci_parse($conn, $sql);
    //     $r = oci_execute($stid);
    //     $rows=[];
    //     while($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    //         $rows[]=$row;
    //     }
    //     oci_free_statement($stid);
        
    //     foreach($rows as $val){
    //         $xAxisData[]=$val['DEVICE_NAME'];
    //         $yAxisData[]=$val['NUM'];
    //     }
    
      
        // return $rows;
        return CatchResponse::success(['xAxisData'=>$xAxisData,'yAxisData'=>$yAxisData]);
    }
}