php原生查询优化

一、php原生查询优化

    // 井不为空时
        if ($productId != '') {
            $data = $this->getQueryStatement($productId, $m_intSTime, $m_intETime)['query1'];
            return $this->getCommonValue($data);
            // 井为空时
        } elseif ($productId == '') {
            $data = $this->getQueryStatement($productId, $m_intSTime, $m_intETime)['query2'];
            return $this->getCommonValue($data);
        }
    }

    // 查询语句
    private function getQueryStatement($productId, $m_intSTime, $m_intETime)
    {
        // 第一个查询语句
        $query1 = "SELECT
            a.SERIAL,
            a.COMMANDID,
            a.CELLID,
            a.USERID,
            a.LIBTIME,
            a.[RETURN],
            a.VALUE,
            a.USERALIAS,
            (SELECT NAME FROM system_index WHERE ID = a.CELLID) AS NAME
          FROM
            system_command AS a
          WHERE
            a.CELLID IN (SELECT ID FROM system_index WHERE PARENTID = '{$productId}')
            AND a.LIBTIME BETWEEN '{$m_intSTime}' AND '{$m_intETime}'";
        // 第二个查询语句
        $query2 = "SELECT  
            a.SERIAL, 
            a.COMMANDID, 
            a.CELLID, 
            a.USERID,
            a.LIBTIME, 
            a.[RETURN],
            a.VALUE,
            a.USERALIAS,
            b.NAME
            FROM system_command AS a 
            JOIN system_index AS b ON a.CELLID = b.ID
            WHERE a.LIBTIME BETWEEN '{$m_intSTime}' AND '{$m_intETime}'";

        // 返回查询语句
        // 可以使用数组或其他适合的数据结构返回多个语句
        return ['query1' => $query1, 'query2' => $query2];
    }


    // 公共部分
    private function getCommonValue($data)
    {
        $daRet = $this->da->GetDataTable($data);
        if ($daRet->strState == '') {
            $arrID = $daRet->Data->fetchAll(PDO::FETCH_ASSOC);
        }
        // 获取到油井的名称
        for ($j = 0; $j < count($arrID); $j++) {
            $getOilValue = "select ID,Name,PARENTID,TYPE from system_index where ID = '{$arrID[$j]['CELLID']}'";
            $oilData = $this->da->GetDataTable($getOilValue);
            if ($oilData->strState == '') {
                $oilData = $oilData->Data->fetchAll(PDO::FETCH_ASSOC);
            }
            if ($oilData[0]['TYPE'] === '59') { // 井
                $arrID[$j]['parent'] = $oilData['0']['name'];
            } else { // 属性
                // 查找到它的上级
                $getOilLevel = "select ID,Name as Parent,TYPE from system_index where ID = '{$oilData[0]['PARENTID']}'";
                $oilLevel = $this->da->GetDataTable($getOilLevel);
                if ($oilLevel->strState == '') {
                    $oilLevel = $oilLevel->Data->fetchAll(PDO::FETCH_ASSOC);
                }
                $arrID[$j]['parent'] = $oilLevel[0]['Parent'];
            }
        }
        return json_encode($arrID);
    }
}