1: <?php
  2: /**
  3:  * CSqlDataProvider implements a data provider based on a plain SQL statement.
  4:  *
  5:  * CSqlDataProvider provides data in terms of arrays, each representing a row of query result.
  6:  *
  7:  * Like other data providers, CSqlDataProvider also supports sorting and pagination.
  8:  * It does so by modifying the given {@link sql} statement with "ORDER BY" and "LIMIT"
  9:  * clauses. You may configure the {@link sort} and {@link pagination} properties to
 10:  * customize sorting and pagination behaviors.
 11:  *
 12:  * CSqlDataProvider may be used in the following way:
 13:  * <pre>
 14:  * $count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();
 15:  * $sql='SELECT * FROM tbl_user';
 16:  * $dataProvider=new CSqlDataProvider($sql, array(
 17:  *     'totalItemCount'=>$count,
 18:  *     'sort'=>array(
 19:  *         'attributes'=>array(
 20:  *              'id', 'username', 'email',
 21:  *         ),
 22:  *     ),
 23:  *     'pagination'=>array(
 24:  *         'pageSize'=>10,
 25:  *     ),
 26:  * ));
 27:  * // $dataProvider->getData() will return a list of arrays.
 28:  * </pre>
 29:  *
 30:  * Note: if you want to use the pagination feature, you must configure the {@link totalItemCount} property
 31:  * to be the total number of rows (without pagination). And if you want to use the sorting feature,
 32:  * you must configure {@link sort} property so that the provider knows which columns can be sorted.
 33:  *
 34:  * @author Qiang Xue <qiang.xue@gmail.com>
 35:  * @package system.web
 36:  * @since 1.1.4
 37:  */
 38: class CSqlDataProvider extends CDataProvider
 39: {
 40:     /**
 41:      * @var CDbConnection the database connection to be used in the queries.
 42:      * Defaults to null, meaning using Yii::app()->db.
 43:      */
 44:     public $db;
 45:     /**
 46:      * @var string|CDbCommand the SQL statement to be used for fetching data rows.
 47:      * Since version 1.1.13 this can also be an instance of {@link CDbCommand}.
 48:      */
 49:     public $sql;
 50:     /**
 51:      * @var array parameters (name=>value) to be bound to the SQL statement.
 52:      */
 53:     public $params=array();
 54:     /**
 55:      * @var string the name of key field. Defaults to 'id'.
 56:      */
 57:     public $keyField='id';
 58: 
 59:     /**
 60:      * Constructor.
 61:      * @param string|CDbCommand $sql the SQL statement to be used for fetching data rows. Since version 1.1.13 this can also be an instance of {@link CDbCommand}.
 62:      * @param array $config configuration (name=>value) to be applied as the initial property values of this class.
 63:      */
 64:     public function __construct($sql,$config=array())
 65:     {
 66:         $this->sql=$sql;
 67:         foreach($config as $key=>$value)
 68:             $this->$key=$value;
 69:     }
 70: 
 71:     /**
 72:      * Fetches the data from the persistent data storage.
 73:      * @return array list of data items
 74:      */
 75:     protected function fetchData()
 76:     {
 77:         if(!($this->sql instanceof CDbCommand))
 78:         {
 79:             $db=$this->db===null ? Yii::app()->db : $this->db;
 80:             $command=$db->createCommand($this->sql);
 81:         }
 82:         else
 83:             $command=clone $this->sql;
 84: 
 85:         if(($sort=$this->getSort())!==false)
 86:         {
 87:             $order=$sort->getOrderBy();
 88:             if(!empty($order))
 89:             {
 90:                 if(preg_match('/\s+order\s+by\s+[\w\s,\.]+$/i',$command->text))
 91:                     $command->text.=', '.$order;
 92:                 else
 93:                     $command->text.=' ORDER BY '.$order;
 94:             }
 95:         }
 96: 
 97:         if(($pagination=$this->getPagination())!==false)
 98:         {
 99:             $pagination->setItemCount($this->getTotalItemCount());
100:             $limit=$pagination->getLimit();
101:             $offset=$pagination->getOffset();
102:             $command->text=$command->getConnection()->getCommandBuilder()->applyLimit($command->text,$limit,$offset);
103:         }
104: 
105:         foreach($this->params as $name=>$value)
106:             $command->bindValue($name,$value);
107: 
108:         return $command->queryAll();
109:     }
110: 
111:     /**
112:      * Fetches the data item keys from the persistent data storage.
113:      * @return array list of data item keys.
114:      */
115:     protected function fetchKeys()
116:     {
117:         $keys=array();
118:         if($data=$this->getData())
119:         {
120:             if(is_object(reset($data)))
121:                 foreach($data as $i=>$item)
122:                     $keys[$i]=$item->{$this->keyField};
123:             else
124:                 foreach($data as $i=>$item)
125:                     $keys[$i]=$item[$this->keyField];
126:         }
127:         return $keys;
128:     }
129: 
130:     /**
131:      * Calculates the total number of data items.
132:      * This method is invoked when {@link getTotalItemCount()} is invoked
133:      * and {@link totalItemCount} is not set previously.
134:      * The default implementation simply returns 0.
135:      * You may override this method to return accurate total number of data items.
136:      * @return integer the total number of data items.
137:      */
138:     protected function calculateTotalItemCount()
139:     {
140:         return 0;
141:     }
142: }
143: