M\:l;ZfbJ^X (qvIn۳sRE]>= sHR<י.yrHlp޴-)QApgvThn+4,uQWxN3K>u D=>z. /Ol/4vՋPS+TtL;,)Gevng'Iw?x|N%tC\Hfc4 s#"D=\!tC4stu ;&L+RXϸ,3`5t g2<|SrpW<ʺm__|EG+y ?4%Oc]%:kRhRԢKJ>S)dy w"ʵ]PF{5ƾ=O~m7|P{7XɮdC8l/3}zϔc1X8WG  UțX꿍R!MQ46hK( Cv;!{l7dV$`lѪP" &>)WM=8`%<%*fFO9FE=6,N"yzׂVMCwLǰLgyG͸S-.}htX5ݶpr]: _iUbO潄ed<>A/qƿp[^3%Nu _1nqz!S=oH}OlC&c|aFeRH,DCnaB3ыn1GdZ`J2T>Ʒx D= ~.Pw7>mOpF|P܍CWp7]BScDRXԂ_,B{9.Q:hRM${tkfHAiy zRSYx_i4#Dlxسd}L7<õ.cI +E ]&L{0VuԀz^.vV#0eWBjQ%/fO D= ~.Pw7>mO8qDLAyaf; sD1lr')(i )~qO;>cI +E ]&L{0VuԀz^.vEnS%?Q |=哵Ȫox*1/D42;a%|Ry&楼ĩ]!ε< WUƽ7QEf}Dlxسd}yeA9'-vNfi J^poH}OlC&c|a9M687sbE. P5]=4 4bҚmOni2M\™g+ Esf8 C+dX!Cplӕw5t!n}ϽYzl~C饉jJ '*9!E/X2?lk̪SO%]@U"JM1X!b_Hw;yN~-]Ź MxE0^Ze~ 3ыn1Gdf4 #alOkn){1Ff^E2N%|Ry&楼ĩ]!ε< WU3Nk ˇ!><1=IʸI_y`̹\d_V 8?˵]n:Wi g;zF`cl=^mXvٺ d&y!oDaC&qFh2ZDmAO2=r(|wuL[m/K8aEd ";Y$p߭j0)Cl/Q K# 8ZۉV{;{dU,c&yj^KUy:̃ut}%3s#gOoHEB;z*oCw%'o{oQr^B! 488,jU{ qXWu*UU+?g+K' !a']kt^n355D)-Evde݆qBMx2&XЦFq|ˬW2O!b,8@UdEpAc9ϔl[WnWXq`۱D06 wY;F*۟  _/)jd'0rbv]-?:Kj >Lޔal} *Lټnki;'w3CaTFή@ 2¥bxdg{$9 D\E4T/n1yAmAgtN׽sz!Q |vHnU$v(ZNP]Z)|A5F5t ZӠa+m;`oki=EV jҳi3߱ȫ|Ge\8_Mkj(X5SؓAHspan class="php-comment"> */ 12: 13: namespace DataTables\Editor; 14: if (!defined('DATATABLES')) exit(); 15: 16: use DataTables; 17: 18: /** 19: * The Options class provides a convenient method of specifying where Editor 20: * should get the list of options for a `select`, `radio` or `checkbox` field. 21: * This is normally from a table that is _left joined_ to the main table being 22: * edited, and a list of the values available from the joined table is shown to 23: * the end user to let them select from. 24: * 25: * `Options` instances are used with the {@link Field::options} method. 26: * 27: * @example 28: * Get a list of options from the `sites` table 29: * <code> 30: * Field::inst( 'users.site' ) 31: * ->options( Options::inst() 32: * ->table( 'sites' ) 33: * ->value( 'id' ) 34: * ->label( 'name' ) 35: * ) 36: * </code> 37: * 38: * @example 39: * Get a list of options with custom ordering 40: * <code> 41: * Field::inst( 'users.site' ) 42: * ->options( Options::inst() 43: * ->table( 'sites' ) 44: * ->value( 'id' ) 45: * ->label( 'name' ) 46: * ->order( 'name DESC' ) 47: * ) 48: * </code> 49: * 50: * @example 51: * Get a list of options showing the id and name in the label 52: * <code> 53: * Field::inst( 'users.site' ) 54: * ->options( Options::inst() 55: * ->table( 'sites' ) 56: * ->value( 'id' ) 57: * ->label( [ 'name', 'id' ] ) 58: * ->render( function ( $row ) { 59: * return $row['name'].' ('.$row['id'].')'; 60: * } ) 61: * ) 62: * </code> 63: */ 64: class SearchPaneOptions extends DataTables\Ext { 65: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 66: * Private parameters 67: */ 68: 69: /** @var string Table to get the information from */ 70: private $_table = null; 71: 72: /** @var string Column name containing the value */ 73: private $_value = null; 74: 75: /** @var string[] Column names for the label(s) */ 76: private $_label = array(); 77: 78: /** @var string[] Column names for left join */ 79: private $_leftJoin = array(); 80: 81: /** @var callable Callback function to do rendering of labels */ 82: private $_renderer = null; 83: 84: /** @var callback Callback function to add where conditions */ 85: private $_where = null; 86: 87: /** @var string ORDER BY clause */ 88: private $_order = null; 89: 90: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 91: * Public methods 92: */ 93: 94: /** 95: * Get / set the column(s) to use as the label value of the options 96: * 97: * @param null|string|string[] $_ null to get the current value, string or 98: * array to get. 99: * @return Options|string[] Self if setting for chaining, array of values if 100: * getting. 101: */ 102: public function label ( $_=null ) 103: { 104: if ( $_ === null ) { 105: return $this; 106: } 107: else if ( is_string($_) ) { 108: $this->_label = array( $_ ); 109: } 110: else { 111: $this->_label = $_; 112: } 113: 114: return $this; 115: } 116: 117: /** 118: * Get / set the ORDER BY clause to use in the SQL. If this option is not 119: * provided the ordering will be based on the rendered output, either 120: * numerically or alphabetically based on the data returned by the renderer. 121: * 122: * @param null|string $_ String to set, null to get current value 123: * @return Options|string Self if setting for chaining, string if getting. 124: */ 125: public function order ( $_=null ) 126: { 127: return $this->_getSet( $this->_order, $_ ); 128: } 129: 130: /** 131: * Get / set the label renderer. The renderer can be used to combine 132: * multiple database columns into a single string that is shown as the label 133: * to the end user in the list of options. 134: * 135: * @param null|callable $_ Function to set, null to get current value 136: * @return Options|callable Self if setting for chaining, callable if 137: * getting. 138: */ 139: public function render ( $_=null ) 140: { 141: return $this->_getSet( $this->_renderer, $_ ); 142: } 143: 144: /** 145: * Get / set the database table from which to gather the options for the 146: * list. 147: * 148: * @param null|string $_ String to set, null to get current value 149: * @return Options|string Self if setting for chaining, string if getting. 150: */ 151: public function table ( $_=null ) 152: { 153: return $this->_getSet( $this->_table, $_ ); 154: } 155: 156: /** 157: * Get / set the column name to use for the value in the options list. This 158: * would normally be the primary key for the table. 159: * 160: * @param null|string $_ String to set, null to get current value 161: * @return Options|string Self if setting for chaining, string if getting. 162: */ 163: public function value ( $_=null ) 164: { 165: return $this->_getSet( $this->_value, $_ ); 166: } 167: 168: /** 169: * Get / set the method to use for a WHERE condition if it is to be 170: * applied to the query to get the options. 171: * 172: * @param null|callable $_ Function to set, null to get current value 173: * @return Options|callable Self if setting for chaining, callable if 174: * getting. 175: */ 176: public function where ( $_=null ) 177: { 178: return $this->_getSet( $this->_where, $_ ); 179: } 180: 181: /** 182: * Get / set the array values used for a leftJoin condition if it is to be 183: * applied to the query to get the options. 184: * 185: * @param string $table to get the information from 186: * @param string $field1 the first field to get the information from 187: * @param string $operator the operation to perform on the two fields 188: * @param string $field2 the second field to get the information from 189: * @return self 190: */ 191: public function leftJoin ( $table, $field1, $operator, $field2 ) 192: { 193: $this->_leftJoin[] = array( 194: "table" => $table, 195: "field1" => $field1, 196: "field2" => $field2, 197: "operator" => $operator 198: ); 199: 200: return $this; 201: } 202: 203: /** 204: * Adds all of the where conditions to the desired query 205: * 206: * @param string $query the query being built 207: * @return self 208: */ 209: private function _get_where ( $query ) 210: { 211: for ( $i=0 ; $i<count($this->_where) ; $i++ ) { 212: if ( is_callable( $this->_where[$i] ) ) { 213: $this->_where[$i]( $query ); 214: } 215: else { 216: $query->where( 217: $this->_where[$i]['key'], 218: $this->_where[$i]['value'], 219: $this->_where[$i]['op'] 220: ); 221: } 222: } 223: return $this; 224: } 225: 226: /** 227: * Adds a join for all of the leftJoin conditions to the 228: * desired query, using the appropriate values. 229: * 230: * @param string $query the query being built 231: * @return self 232: */ 233: private function _perform_left_join ( $query ) 234: { 235: if ( count($this->_leftJoin) ) { 236: for ( $i=0, $ien=count($this->_leftJoin) ; $i<$ien ; $i++ ) { 237: $join = $this->_leftJoin[$i]; 238: $query->join( $join['table'], $join['field1'].' '.$join['operator'].' '.$join['field2'], 'LEFT' ); 239: } 240: } 241: return $this; 242: } 243: 244: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 245: * Internal methods 246: */ 247: 248: /** 249: * Execute the options (i.e. get them) 250: * 251: * @param Database $db Database connection 252: * @return array List of options 253: * @internal 254: */ 255: public function exec ( $field, $editor, $http, $fields, $leftJoinIn ) 256: { 257: // If the value is not yet set then set the variable to be the field name 258: if ( $this->_value == null) { 259: $value = $field->dbField(); 260: } 261: else { 262: $value = $this->_value; 263: } 264: 265: // If the table is not yet set then set the table variable to be the same as editor 266: if ( $this->_table == null) { 267: $table = $editor->table(); 268: } 269: else { 270: $table = $this->_table; 271: } 272: 273: // If the label value has not yet been set then just set it to be the same as value 274: if ( $this->_label == null ) { 275: $label = $value; 276: } 277: else { 278: $label = $this->_label[0]; 279: } 280: 281: // Set the database from editor 282: $db = $editor->db(); 283: 284: $formatter = $this->_renderer; 285: 286: // We need a default formatter if one isn't provided 287: if ( ! $formatter ) { 288: $formatter = function ( $str ) { 289: return $str; 290: }; 291: } 292: 293: // Set up the join variable so that it will fit nicely later 294: if(count($this->_leftJoin) > 0){ 295: $join = $this->_leftJoin[0]; 296: } 297: else { 298: $join = $this->_leftJoin; 299: } 300: 301: // Set up the left join varaible so that it will fit nicely later 302: if(count($leftJoinIn) > 0) { 303: $leftJoin = $leftJoinIn[0]; 304: } 305: else { 306: $leftJoin = $leftJoinIn; 307: } 308: 309: // Set the query to get the current counts for viewTotal 310: $query = $db 311: ->query('select') 312: ->table( $table ); 313: 314: if ( $field->apply('get') && $field->getValue() === null ) { 315: $query->get( $value." as value", "COUNT(*) as count"); 316: $query->group_by( $value); 317: } 318: 319: // If a join is required then we need to add the following to the query 320: if (count($leftJoin) > 0){ 321: $query->join( $leftJoin['table'], $leftJoin['field1'].' '.$leftJoin['operator'].' '.$leftJoin['field2'], 'LEFT' ); 322: } 323: 324: // Construct the where queries based upon the options selected by the user 325: if( isset($http['searchPanes']) ) { 326: foreach ($fields as $fieldOpt) { 327: if( isset($http['searchPanes'][$fieldOpt->name()])){ 328: $query->where( function ($q) use ($fieldOpt, $http) { 329: for($j=0 ; $j<count($http['searchPanes'][$fieldOpt->name()]) ; $j++){ 330: $q->or_where( $fieldOpt->dbField(), $http['searchPanes'][$fieldOpt->name()][$j], '=' ); 331: } 332: }); 333: } 334: } 335: } 336: 337: $res = $query 338: ->exec() 339: ->fetchAll(); 340: 341: // Get the data for the pane options 342: $q = $db 343: ->query('select') 344: ->table( $table ) 345: ->get( $label." as label", $value." as value", "COUNT(*) as total" ) 346: ->group_by( $value ) 347: ->where( $this->_where ); 348: 349: // If a join is required then we need to add the following to the query 350: if (count($join) > 0){ 351: $q->join( $join['table'], $join['field1'].' '.$join['operator'].' '.$join['field2'], 'LEFT' ); 352: } 353: 354: if ( $this->_order ) { 355: // For cases where we are ordering by a field which isn't included in the list 356: // of fields to display, we need to add the ordering field, due to the 357: // select distinct. 358: $orderFields = explode( ',', $this->_order ); 359: 360: for ( $i=0, $ien=count($orderFields) ; $i<$ien ; $i++ ) { 361: $field = strtolower( $orderFields[$i] ); 362: $field = str_replace( ' asc', '', $field ); 363: $field = str_replace( ' desc', '', $field ); 364: $field = trim( $field ); 365: 366: if ( ! in_array( $field, $fields ) ) { 367: $q->get( $field ); 368: } 369: } 370: 371: $q->order( $this->_order ); 372: } 373: 374: $rows = $q 375: ->exec() 376: ->fetchAll(); 377: 378: // Create the output array 379: $out = array(); 380: 381: for ( $i=0, $ien=count($rows) ; $i<$ien ; $i++ ) { 382: $set = false; 383: for( $j=0 ; $j<count($res) ; $j ++) { 384: if($res[$j]['value'] == $rows[$i]['value']){ 385: $out[] = array( 386: "label" => $formatter($rows[$i]['label']), 387: "total" => $rows[$i]['total'], 388: "value" => $rows[$i]['value'], 389: "count" => $res[$j]['count'] 390: ); 391: $set = true; 392: } 393: } 394: if(!$set) { 395: $out[] = array( 396: "label" => $formatter($rows[$i]['label']), 397: "total" => $rows[$i]['total'], 398: "value" => $rows[$i]['value'], 399: "count" => 0 400: ); 401: } 402: 403: } 404: 405: // Only sort if there was no SQL order field 406: if ( ! $this->_order ) { 407: usort( $out, function ( $a, $b ) { 408: return is_numeric($a['label']) && is_numeric($b['label']) ? 409: ($a['label']*1) - ($b['label']*1) : 410: strcmp( $a['label'], $b['label'] ); 411: } ); 412: } 413: 414: return $out; 415: } 416: } 417: