kenken999/php
0
1<?php2 3class OrderClause4{5 private $pSet = null;6 private $cipherer = null;7 private $sessionPrefix = "";8 private $connection = "";9 10 private $_cachedFields = null;11 private $_cachedSortBySettings = null;12 13 14 /**15 * Constructor16 * params may include17 *18 * @param Object pSet19 * @param Boolean needReadRequest. When true, the object will read sorting command from request and update settings in the session.20 */21 function __construct( $_pSet, $_cipherer, $_sessionPrefix, $_connection, $needReadRequest = false )22 {23 $this->pSet = $_pSet;24 $this->cipherer = $_cipherer;25 $this->sessionPrefix = $_sessionPrefix;26 $this->connection = $_connection;27 28 if( $needReadRequest )29 $this->readRequest();30 }31 /**32 * Interface functions33 */34 35 static function createFromPage( $pageObject, $needReadRequest = false )36 {37 return new OrderClause($pageObject->pSet, $pageObject->cipherer, $pageObject->sessionPrefix, $pageObject->connection, $needReadRequest );38 }39 40 /**41 * Returns array with all current sorting data42 * Array in the form of43 * [0] => array( 'column' => <columns name from SQL query> 44 'index' => <1-based index of a column in the query select-list. 0 if not available>45 'expr' => <SQL expression representing the field> 46 'dir' => 'ASC' or 'DESC' 47 'hidden' => true or not set. Hidden sorting fields are added by the application and should not be reflected in the UI.48 )49 [1] => the same50 Example 51 SQL Query:52 select id, first, [last], concat(first, last) as fullname from users order by 2,4,birthdate desc53 54 Corresponding OrderFields array:55 [0] => array( 56 column => first57 index => 258 expr => first59 dir => ASC60 )61 [1] => array( 62 column => fullname63 index => 464 expr => concat(first, last)65 dir => ASC66 )67 [2] => array( 68 column => 69 index => 070 expr => birthdate71 dir => DESC72 )73 [3] => array( 74 column => id75 index => 176 expr => id77 dir => ASC78 hidden => true79 )80 81 */82 public function getOrderFields() 83 {84 if( $this->_cachedFields !== null )85 return $this->_cachedFields;86 87 $ret = array();88 $columns = array();89 90 $pSet = $this->pSet;91 92 $saved = $_SESSION[$this->sessionPrefix . "_orderby"];93 94 95 if( 0 != strlen( $saved['orderby'] ) )96 {97 // orderby format:98 // acolumn1;dcolumn2;acolumn399 $fields = explode(';', $saved['orderby'] );100 foreach( $fields as $f )101 {102 $dir = substr($f, 0, 1);103 if( $dir!='a' && $dir != 'd' )104 continue;105 $goodField = substr($f, 1);106 $fieldName = $pSet->getFieldByGoodFieldName( $goodField );107 $index = $pSet->getFieldIndex( $fieldName );108 if( !$index )109 continue;110 $ret[] = array( 'column' => $fieldName,111 'index' => $index,112 'expr' => RunnerPage::_getFieldSQLDecrypt( $fieldName, $this->connection, $this->pSet, $this->cipherer ),113 'dir' => ($dir == 'a' ? 'ASC' : 'DESC')114 );115 $columns[ $fieldName ] = true;116 }117 118 }119 else if( 0 != strlen( $saved['sortby'] ) )120 {121 // 'Sort by' control122 // $saved['sortby'] - 1-based index of selected value in the 'sort by' control123 124 $sortbySettings =& $this->getSortBySettings();125 $option = $sortbySettings[ $saved['sortby'] - 1 ];126 if( $option )127 {128 foreach( $option["fields"] as $f ) 129 {130 $ret[] = array( 'column' => $f["field"],131 'index' => $pSet->getFieldIndex( $f["field"] ),132 'expr' => RunnerPage::_getFieldSQLDecrypt( $f["field"], $this->connection, $this->pSet, $this->cipherer ),133 'dir' => ( $f["desc"] ? 'DESC' : 'ASC' )134 );135 $columns[ $f["field"] ] = true;136 }137 }138 139 }140 else141 {142 // use SQL sorting143 $ret = OrderClause::originalOrderFields( $pSet );144 foreach( $ret as $of ) {145 $columns[ $of['column'] ] = true;146 }147 $orderInfo = $pSet->getOrderIndexes();148 }149 150 151 if( $this->orderParsed() ) {152 // add key fields to the list to ensure persistent records order153 foreach( $pSet->getTableKeys() as $k )154 {155 if( isset( $columns[$k] ) )156 continue;157 158 $ret[] = array( 'column' => $k,159 'index' => $pSet->getFieldIndex( $k ),160 'expr' => RunnerPage::_getFieldSQLDecrypt( $k, $this->connection, $this->pSet, $this->cipherer ),161 'dir' => 'ASC',162 'hidden' => true163 );164 }165 }166 167 // group by sort168 $groupByRet = array();169 foreach( $pSet->getGroupFields() as $grField )170 {171 $grFieldPos = -1;172 foreach ( $ret as $key => $of )173 {174 if ( $of["column"] == $grField )175 {176 $grFieldPos = $key;177 break;178 }179 }180 181 if ( $grFieldPos != -1 )182 {183 $groupByRet[] = $ret[ $grFieldPos ];184 unset( $ret[ $grFieldPos ] );185 }186 else187 {188 $groupByRet[] = array(189 'column' => $grField,190 'index' => $pSet->getFieldIndex( $grField ),191 'expr' => RunnerPage::_getFieldSQLDecrypt( $grField, $this->connection, $this->pSet, $this->cipherer ),192 'dir' => 'ASC',193 'hidden' => true194 );195 }196 }197 198 $this->_cachedFields = array_merge($groupByRet, $ret);199 200 return $this->_cachedFields;201 }202 203 /**204 * Build order info based on the original SQL Query only205 * @return Array - see getOrderFields206 */207 public static function originalOrderFields( $pSet ) {208 $orderInfo = $pSet->getOrderIndexes();209 $ret = array();210 foreach( $orderInfo as $o )211 {212 if ( $o[0] == 0 ) {213 $field = $o[2]; 214 } else {215 $field = $pSet->GetFieldByIndex( $o[0] );216 }217 218 $ret[] = array(219 'column' => $field,220 'index' => $o[0],221 'expr' => $o[2],222 'dir' => $o[1]223 );224 225 }226 return $ret;227 }228 229 public function getOrderUrlParams()230 {231 $arrParams = array();232 $orderFields = $this->getOrderFields();233 foreach ( $orderFields as $key => $field )234 {235 if ( !$field['hidden'] )236 {237 $dirChar = $field['dir'] == 'ASC' ? 'a' : 'd';238 $arrParams[] = $dirChar.GoodFieldName($field['column']);239 }240 }241 242 return implode(";", $arrParams);243 } 244 245 /**246 * Builds and returns SQL Order By expression based on current settings247 * @return String248 */249 public function getOrderByExpression() 250 {251 $orderby = array();252 253 foreach( $this->getOrderFields() as $of )254 {255 /**256 * 'expr' is preferable to 'column' because it can work without the original field in the select-list257 * Example:258 * select id, last, concat(first, last) as full from users order by full *** ok259 * Selecting 'id' only:260 * select id from users order by full *** error 261 * select id from users order by concat(first, last) *** ok262 *263 * On the other hand, indices are preferred event more, because they can work in with subqueries and even UNIONs264 */265 266 if( $of["index"] ) {267 $orderby[] = $of["index"] . " " . $of["dir"];268 } else {269 $orderby[] = $of["expr"] . " " . $of["dir"];270 }271 }272 if( $orderby )273 return " order by " . implode( ", ", $orderby );274 return $this->pSet->getStrOrderBy();275 }276 277 /**278 * Returns Sort By conrtrol elements description279 * @return Array280 */281 public function getSortBySettings() 282 {283 if( $this->_cachedSortBySettings !== null )284 return $this->_cachedSortBySettings;285 286 $sortSettings = $this->pSet->getSortControlSettingsJSONString();287 $sortSettings = my_json_decode( $sortSettings );288 if( !$sortSettings || !$sortSettings )289 {290 $sortSettings = array();291 292 foreach( $this->pSet->getListFields() as $fName ) 293 {294 if( !$this->isFieldSortable( $fName ) )295 continue;296 297 $sortSettings[] = array( "label" => "", "fields" => array( array( "field" => $fName, "desc" => false, "labelOnly" => true ) ) );298 }299 } 300 301 $this->_cachedSortBySettings = $sortSettings;302 303 return $sortSettings; 304 }305 306 protected function isFieldSortable( $fName ) 307 {308 $type = $this->pSet->getFieldType( $fName );309 return !IsBinaryType( $type ) && ( $this->connection->dbType == nDATABASE_MySQL || $this->connection->dbType == nDATABASE_PostgreSQL || !IsTextType( $type ) ); 310 }311 312 /**313 * Returns selected index in the Sort By conrtrol based on current settings314 * -1 if no match found315 * @return Integer316 */317 public function getSortByControlIdx() 318 {319 $sortbySettings =& $this->getSortBySettings();320 $saved = $_SESSION[$this->sessionPrefix . "_orderby"];321 if( strlen( $saved["sortby"] ) != 0 )322 {323 $idx = (int)( $saved["sortby"] ) - 1;324 if( isset( $sortbySettings[$idx] ) )325 return $idx;326 }327 328 // try to match current order settings with one of the 'sort by' control elements329 330 // make normalized string of current sort settings first331 $orderFields =& $this->getOrderFields();332 foreach( $orderFields as $o )333 {334 if( !$o['hidden'] )335 $normOrder[] = array( $o['column'], $o['dir'] );336 }337 $sortString = my_json_encode( $normOrder );338 339 340 // make normalized string of each $sortbySettings element and compare to $normOrder341 342 foreach( $sortbySettings as $i => $s )343 {344 $normOrder = array();345 foreach( $s["fields"] as $f )346 $normOrder[] = array( $f['field'], $f["desc"] ? 'DESC' : 'ASC' );347 if( my_json_encode( $normOrder ) == $sortString )348 return $i;349 }350 return -1;351 }352 353 /**354 * DEPRECATED355 * Returns simplified version of current sorting data for use specifically in the ListQuery event356 * 357 * @return Array358 */359 public function getListQueryData() 360 {361 $arrFieldForSort = array();362 $arrHowFieldSort = array();363 364 foreach( $this->getOrderFields() as $of )365 {366 $arrFieldForSort[] = $of["index"];367 $arrHowFieldSort[] = $of["dir"];368 }369 370 return array( 371 "fieldsForSort" => $arrFieldForSort,372 "howToSortData" => $arrHowFieldSort373 );374 }375 376 /**377 * Implementation functions378 */379 380 381 /**382 * Read sort data from request and save it to the session383 */384 protected function readRequest() {385 386 if( strlen( postvalue("orderby") ) || strlen( postvalue("sortby") ) )387 $_SESSION[ $this->sessionPrefix . "_orderby" ] = array( "orderby" => postvalue("orderby"), "sortby" => postvalue("sortby") );388 }389 390 /**391 * @return Boolean - true when the original ORDER BY was successfully parsed by the wizard. Also true when there is no original order by392 */393 protected function orderParsed() {394 return !!$this->pSet->getOrderIndexes() || $this->pSet->getStrOrderBy() == "";395 }396}397?>