ods_api.php

private function get_states() {
    $whr = [];
    $arg=[];
    $mapfilter = $this->mapfilter("d");
    $lines = '';

    foreach(['Town_id','Location','District','Type','Sip','Ip','Smart','Devices_id','Name', 'Station', 'DimProfile_id'] as $gv) {
        if(isset($_GET[$gv])) {
            $alias = '';
            if($gv=='Devices_id')
                $alias = 'StatusReg.';
            if($gv=='DimProfile_id' || $gv=='Type')
                $alias = 'd.';
            $whr[] = "($alias`$gv`=?)";
            $arg[] = $_GET[$gv];
        }
    }

    if(isset($_GET['Mode'])) {
        $whr[] = "(StatusReg.`Mode`in (?))";
        $arg[] = $_GET['Mode'];
    }

    if(isset($_GET['box'])) {
        $whr[] = "(`Lines`.Devices_id =?)";
        $arg[] = $_GET['box'];
        $lines = 'left join `Lines` on `Lines`.id = d.Lines_id';
    }

    if(isset($_GET['Trouble'])) {
        $whr[] = '(`Trouble`' . ($_GET['Trouble']>0?'>':'=') . '0)';
    }

    if(isset($_GET['State'])) {
        $whr[] = '(`State`' . ($_GET['State']>0?'>':'=') . '0)';
    }

    if(isset($_GET['Power'])) {
        $whr[] = '('.($_GET['Power']>0?'`Power`>=7 or (d.`Type`=2 and `Power`>0)':'`Power`=0 or (d.`Type`=1 and `Power`<7)').')';
    }


    if(isset($_GET['mapfilter']))
        $whr[]=$mapfilter;

    $where = join($whr, " AND ");

    if(count($arg)==0)
        $arg=null;

    if($where !== "")
        $where = 'AND '.$where;

    $order = '';
    if(isset($_GET['order'])) {
        $order = "order by d.`type`, LPAD(d.CustomNum,10,'0')";
    }
//V11_Phase
//Anton_30day
//  StatusReg.Timestamp >= date_sub(now(), INTERVAL 30 DAY)
//  AND

//V11_Phase_30 day_++Anton
    $sql =
    "
    SELECT d.id, d.Name, d.CustomNum, d.Location, d.District, d.Type, d.lat, d.lng, d.Smart, d.sn,
    StatusReg.id as StatusReg_id, Power, Trouble, Sip, Ip, StatusReg.Mode, State, Timeallert, Controller, Door, Ous, Voltage,
        json_extract(StatusReg.Phase,\"$.v\") as PhaseV,
        StatusReg.Timestamp, StatusReg.Devices_id, IncomingLog_id, StatusReg.DimProfile_id, OutgoingLog_id,
        UNIX_TIMESTAMP(StatusReg.Timestamp) as unixtime,
         (
          SELECT COUNT(DISTINCT `Switch`)
          FROM `Lines`
          WHERE `Devices_id` = d.id
            AND `Switch` > 0
        ) AS num_contactors
    from StatusReg
    inner join Devices as d on Devices_id=d.id
    $lines
    inner join (select max(id) as id from StatusReg group by Devices_id) as ssr on ssr.id = StatusReg.id
    where
    (d.OffTime >= curdate() OR isnull(d.OffTime)) and not isnull(d.OnTime)
    $where
    $order
    ";

    //echo $sql;
    $this->run_query($sql, $arg, ['Devices', 'IncomingLog', 'OutgoingLog', 'StatusReg'],false,'id');
 }