PHP DB::Select方法代码示例

 public function viewEvents($id)
 {
     $data = DB::Select(' select * from c_req_cars where req_car_id=' . $id . ' ');
     foreach ($data as $k) {
         $t = '<h3>เรื่อง ' . $k->detail . '</h3>';
         $t .= '<ul class="uk-list uk-list-striped">';
         $t .= '<li>วันที่บันทึก(ว-ด-ป) : ' . date("d-m", strtotime($k->regis_date)) . '-' . (date("Y", strtotime($k->regis_date)) + 543) . ' เวลาบันทึก : ' . date("H:i:s", strtotime($k->regis_date)) . '</li>';
         $t .= '<li>ชื่อผู้ขอ : ' . $k->req_name . '</li>';
         $t .= '<li>ตำแหน่ง : ' . $k->position . '</li>';
         $t .= '<li>ฝ่าย : ' . $k->department . '</li>';
         $t .= '<li class="uk-text-danger" >ไปที่ : ' . $k->location . '</li>';
         $t .= '<li class="uk-text-danger" >รายละเอียด : ' . $k->detail . '</li>';
         $t .= '<li class="uk-text-danger" >ไปวันที่(ว-ด-ป) : ' . date("d-m", strtotime($k->godate)) . '-' . (date("Y", strtotime($k->godate)) + 543) . '</li>';
         $t .= '<li class="uk-text-danger" >ไปเวลา : ' . $k->gotime_start . '</li>';
         $t .= '<li class="uk-text-danger" >เลขทะเบียนรถ : ' . $k->car_number . '</li>';
         $t .= '<li>ผู้ขับ : ' . $k->driver . '</li>';
         if (count($this->chk_subwork($k->req_car_id)) > 0) {
             foreach ($this->chk_subwork($k->req_car_id) as $c) {
                 $t .= '<li class="uk-text-primary" ><mark>ไปกับฝ่าย : ' . $c->department . '</mark></li>';
             }
         }
         $t .= '</ul>';
     }
     return $t;
 }

function ConsultarPermisosCRM($idDoc)
{
    // -------------------------------------------
    // O P C I O N E S  D E  C R M   S E G U N   E L   R O L
    // D E L   U S U A R I O  Y   L A   C O M P A N I A
    // -------------------------------------------
    $permiso = DB::Select('
SELECT 
    nombreRol,
    adicionarDocumentoCRMRol,
    modificarDocumentoCRMRol,
    anularDocumentoCRMRol,
    consultarDocumentoCRMRol,
    aprobarDocumentoCRMRol
From
  documentocrm 
  Inner Join documentocrmcompania
    On documentocrmcompania.DocumentoCRM_idDocumentoCRM =
    documentocrm.idDocumentoCRM 
  Inner Join documentocrmrol
    On documentocrmrol.DocumentoCRM_idDocumentoCRM = documentocrm.idDocumentoCRM
  Inner Join rol
    On documentocrmrol.Rol_idRol = rol.idRol 
  Inner Join users
    On users.Rol_idRol = rol.idRol
Where
  users.id = ' . \Session::get("idUsuario") . ' And
  documentocrmcompania.Compania_idCompania = ' . \Session::get("idCompania") . ' AND idDocumentoCRM = "' . $idDoc . '"');
    return $permiso;
}

 public function getAddLivrabilEtapa($id_etapa)
 {
     $stadii = self::getStadiiLivrabil();
     $livrabile = DB::Select("SELECT \n            tl.id,   \n            tl.denumire \n            FROM etape_predare_livrabile epl\n            INNER JOIN obiectiv obj ON obj.id = epl.id_obiectiv AND obj.logical_delete = 0\n            INNER JOIN template_contract_tipizat_master tctm ON tctm.id = obj.id_template AND tctm.logical_delete = 0\n            INNER JOIN template_contract_tipizat_detail tctd ON tctd.id_template_contract_tipizat_master = tctm.id AND tctd.logical_delete = 0\n            INNER JOIN tip_livrabile tl ON tl.id = tctd.id_tip_livrabil AND tl.logical_delete = 0\n            WHERE epl.id_etapa = :id_etapa", array('id_etapa' => $id_etapa));
     //Debugbar::info("ETP=" . $id_etapa);
     return View::make('livrabile_etapa.add')->with('stadii', $stadii)->with('livrabile', self::object_to_array($livrabile))->with('id_etapa', $id_etapa);
 }

 public function GetIDS($Keywords, $Property)
 {
     if (!$Keywords) {
         return false;
     }
     if (is_array($Keywords)) {
         foreach ($Keywords as $string) {
             $sql_ext .= ($sql_ext ? ' or ' : '') . 'Keywords=\'' . $string . '\'';
         }
     } else {
         $sql_ext = 'Keywords=\'' . $Keywords . '\'';
     }
     $sql = 'SELECT ' . $Property . ' FROM tbl_keywords WHERE ' . $sql_ext;
     $DB = new DB();
     $RS = $DB->Select($sql);
     if ($RS) {
         $tmpIDS = '';
         foreach ($RS as $list) {
             if ($list[$Property]) {
                 $tmpIDS .= ($tmpIDS ? ',' : '') . $list[$Property];
             }
         }
         $tmpIDS = preg_replace('/(,{2,})/', ',', $tmpIDS);
         if (substr($tmpIDS, 0, 1) == ',') {
             $tmpIDS = substr($tmpIDS, 1);
         }
         if (substr($tmpIDS, -1) == ',') {
             $tmpIDS = substr($tmpIDS, 0, -1);
         }
         $IDS = explode(',', $tmpIDS);
         return implode(',', array_unique($IDS));
     }
     return false;
 }

function ConsultarPermisos($vista)
{
    $permiso = DB::Select('
    SELECT 
        nombreOpcion,
        nombreRol,
        adicionarRolOpcion,
        modificarRolOpcion,
        eliminarRolOpcion,
        consultarRolOpcion,
        rutaOpcion
    FROM
        rol AS r
            LEFT JOIN
        rolopcion AS ro ON ro.Rol_idRol = r.idRol
            LEFT JOIN
        users AS u ON u.Rol_idRol = r.idRol
            LEFT JOIN
        opcion AS o ON ro.Opcion_idOpcion = o.idOpcion
            LEFT JOIN
        paquete AS p ON o.Paquete_idPaquete = p.idPaquete
    WHERE
        Compania_idCompania = ' . \Session::get("idCompania") . ' AND id = ' . \Session::get("idUsuario") . ' AND rutaOpcion = "' . $vista . '"');
    return $permiso;
}

 public function Lists($UserID, $Page = 1, $PageSize = 10)
 {
     $DB = new DB();
     $Page = max(1, $Page);
     $Offset = ($Page - 1) * $PageSize;
     $Sql = 'SELECT PictureID, Path, Title, Keywords,UserID, IsPublic FROM tbl_pictures where UserID=' . $UserID . ' OR IsPublic=1 ORDER BY PictureID DESC';
     return $DB->Select($Sql, $Offset, $PageSize);
 }

 public function getDetaliiFactura($id_factura)
 {
     $total_defasurator = DB::select("SELECT\n            SUM(pret_fara_tva) AS valoare\n            FROM livrabile_etapa\n            WHERE logical_delete = 0\n            AND id_factura = :id_factura", array('id_factura' => $id_factura));
     $detalii = DB::Select("SELECT\n            df.id_det_fact,\n            df.nr_ordine,\n            df.denumire AS denumire_produs,\n            df.cantitate,\n            um.denumire AS um,\n            df.pret_unitar        \n            FROM detalii_factura df\n            LEFT OUTER JOIN um ON um.id = df.id_um AND um.logical_delete = 0            \n            WHERE df.logical_delete = 0\n            AND df.id_factura = :id_factura", array('id_factura' => $id_factura));
     $factura = self::getFacturaClient($id_factura);
     $ums = self::object_to_array(self::getUM());
     return View::make('facturi_client.list_detalii')->with('factura', $factura)->with('detalii', $detalii)->with('total_defasurator', $total_defasurator[0])->with('ums', $ums);
 }

 public function getAddObiectiv($id_contract = null)
 {
     $stadii_obiectiv = self::getStadiiObiectiv();
     $ids = self::getIDsDepartamente(Confide::getDepartamenteUser());
     $contracte = DB::Select("SELECT \n            id, denumire \n            FROM contract\n            WHERE logical_delete = 0\n            AND id_departament IN (" . $ids . ")");
     $templates = DB::select("SELECT\n            id, denumire\n            FROM template_contract_tipizat_master\n            WHERE logical_delete = 0\n            ORDER BY id");
     $id_tara = null;
     $id_regiune = null;
     $id_judet = null;
     $contract = null;
     if ($id_contract) {
         $contract = DB::Select("SELECT\n                id, id_tara, id_regiune, id_judet\n                FROM contract \n                WHERE id = :id_contract", array('id_contract' => $id_contract));
         if (count($contract) == 1) {
             $id_tara = $contract[0]->id_tara;
             $id_regiune = $contract[0]->id_regiune;
             $id_judet = $contract[0]->id_judet;
         }
     }
     return View::make('obiective.add')->with('contracte', self::object_to_array($contracte))->with('templates', self::object_to_array($templates))->with('stadii_obiectiv', $stadii_obiectiv)->with('id_contract', $id_contract)->with('id_tara', $id_tara)->with('id_regiune', $id_regiune)->with('id_judet', $id_judet)->with('departamente', self::object_to_array(Confide::getDepartamenteUser()));
 }

 public function LinksManager($Page = 1, $PageSize = 20, $UserID = 0)
 {
     $DB = new DB();
     if ($UserID) {
         $SqlWhere = ' WHERE CreatedUserID=' . $UserID;
     }
     $Sql = 'SELECT COUNT(ID) AS RecordCount From tbl_links ' . $SqlWhere;
     $rs = $DB->GetOne($Sql);
     if (!$rs || !$rs['RecordCount']) {
         return false;
     }
     $Data['RecordCount'] = $rs['RecordCount'];
     $Data['PageCount'] = ceil($Data['RecordCount'] / $PageSize);
     $Page = min(max(1, $Page), $Data['PageCount']);
     $Offset = ($Page - 1) * $PageSize;
     $Data['Page'] = $Page;
     $Data['PageSize'] = $PageSize;
     $Data['Data'] = $DB->Select('SELECT * FROM tbl_links ' . $SqlWhere . ' ORDER BY ID DESC', $Offset, $PageSize);
     return $Data;
 }

 public function UpdateCache()
 {
     $DB = new DB();
     $sql = 'SELECT * from tbl_variables';
     $rs = $DB->Select($sql);
     if ($rs) {
         foreach ($rs as $list) {
             if ($list['VariableType']) {
                 $list['Value'] = $this->StringToArray($list['VariableValue']);
             } else {
                 $list['Value'] = $list['VariableValue'];
             }
             $Data[$list['VariableName']] = $list;
         }
         $source .= '$Data=' . var_export($Data, true) . ";\n";
         $source = '<?php ' . "\n" . $source . "?>";
         $result = false;
         $filepath = DocumentRoot . '/Data/Config/Variables.cfg.php';
         $f = @fopen($filepath, 'ab+');
         if ($f) {
             @flock($f, LOCK_EX);
             fseek($f, 0);
             ftruncate($f, 0);
             $tmp = @fwrite($f, $source);
             if (!($tmp === FALSE)) {
                 $result = true;
             }
             @fclose($f);
         } else {
             return false;
         }
         @chmod($filepath, 0777);
         return $result;
     }
     return false;
 }

 public function getEditEtapa($id_etapa)
 {
     $etapa = DB::Select("SELECT \n            epl.id_etapa,\n            epl.termen_predare,\n            epl.id_um_timp,\n            epl.instiintare_contractor,\n            epl.nr_etapa,           \n            epl.id_obiectiv,\n            date_format(epl.data_start, '%d-%m-%Y') AS data_start \n            FROM etape_predare_livrabile epl            \n            WHERE epl.logical_delete = 0\n            AND epl.id_etapa = :id_etapa", array('id_etapa' => $id_etapa));
     $ums_timp = self::getUMTimp();
     return View::make('etape_termene.edit')->with('ums_timp', $ums_timp)->with('etapa', $etapa[0]);
 }

 public function support_ocsc_excel()
 {
     if (Session::get('level') != '') {
         $y = DB::Select(' select year(order_date) as year1 from s_salary_ocsc_detail group by year(order_date) order by year(order_date) desc ');
         return View::make('report.report7', array('data' => $y));
     } else {
         return View::make('login.index');
     }
 }

 function Topics($offset = 0, $limit = 20, $AreaID = -1)
 {
     $DB = new DB();
     if ($AreaID >= 0) {
         if ($AreaID) {
             $NextAreaID = GetNextAreaID($AreaID);
         }
         $sql_ext = 'AreaID=0';
         if ($NextAreaID) {
             $sql_ext .= ' OR (AreaID>=' . $AreaID . ' AND AreaID<' . $NextAreaID . ')';
         }
         if ($sql_ext) {
             $sql_ext = 'WHERE ' . $sql_ext;
         }
     }
     $sql = 'select * from tbl_topics ' . $sql_ext . ' ORDER BY TopicID DESC';
     return $DB->Select($sql, $offset, $limit);
 }

        $this->Where($conditionData, " AND ");
        return $this;
    }
    public function _Or($conditionData)
    {
        if (!($this->lastShackle == "Where")) {
            return setError(3);
        }
        $this->Where($conditionData, " OR ");
        return $this;
    }
    private function setError($msg)
    {
        $this->Error = $this->errorMsg[$msg];
        return false;
    }
    private function isAssoc($arr)
    {
        return array_keys($arr) !== range(0, count($arr) - 1);
    }
}
$DB = new DB();
//$a = $DB->Select("client")->SetData(array("id","name"))->Exec();
$data = array("name" => "Test", "lastname" => "Testing", "status" => 1);
$a = $DB->Select("client")->SetData(array("id", "CONCAT(name,' ',lastname) as nombre", "status"))->Exec();
//$a = $DB->Delete("client")->Where(["name","=","test"])->Exec();
if (!$a) {
    echo "<p style='color:red'> {$DB->Error} </p>";
} else {
    var_dump($a);
}

 public function GetAll($MysqlWhere = '')
 {
     $DB = new DB();
     return $DB->Select('select * from ' . $this->TableName . ' ' . $MysqlWhere . ' order by ' . $this->KeyID . ' DESC');
 }