include/db_filter_ui.php

   1 <?php
   2
   3 /**
   4  * User interface for the SQL filter classes
   5  *
   6  * Copyright 2008 BitSplash Software LLC
   7  * Copyright 2008 Alexander N. Sørnes <alex@thehandofagony.com>
   8  *
   9 */
  10
  11 require_once('db_filter.php');
  12
  13 define('FILTER_VALUES_NORMAL', 1);
  14 define('FILTER_VALUES_ENUM', 2);
  15 define('FILTER_VALUES_OPTION_BOOL', 3);
  16
  17 define('MAX_FILTERS', 50);
  18
  19 /* Info describing an available filter: what column it applies to,
  20     and what comparison options are available */
  21 class FilterInfo
  22 {
  23     private $sColumn;
  24     private $sDisplayName;
  25     private $aTypes; // Available filters for this column
  26     private $iValueType; // Normal, enum ...
  27     private $aValueTypeData; // List of enums
  28     private $aValueTypeDataDisplay; // Optional display names for enums
  29
  30     public function FilterInfo($sColumn, $sDisplayName, $aTypes, $iValueType = FILTER_VALUES_NORMAL, $aValueTypeData = array(), $aValueTypeDisplay = array())
  31     {
  32         $this->sColumn = $sColumn;
  33         $this->sDisplayName = $sDisplayName;
  34         $this->aTypes = $aTypes;
  35         $this->iValueType = $iValueType;
  36         $this->aValueTypeData = $aValueTypeData;
  37
  38         if(sizeof($aValueTypeData) && !sizeof($aValueTypeDisplay))
  39             $this->aValueTypeDataDisplay = $aValueTypeData;
  40         else
  41             $this->aValueTypeDataDisplay = $aValueTypeDisplay;
  42     }
  43
  44     public function getColumn()
  45     {
  46         return $this->sColumn;
  47     }
  48
  49     public function getDisplayName()
  50     {
  51         return $this->sDisplayName;
  52     }
  53
  54     public function getValueType()
  55     {
  56         return $this->iValueType;
  57     }
  58
  59     public function getValueTypeData()
  60     {
  61         return $this->aValueTypeData;
  62     }
  63
  64     public function getValueTypeDataDisplay()
  65     {
  66         return $this->aValueTypeDataDisplay;
  67     }
  68
  69     public function getTypes()
  70     {
  71         return $this->aTypes;
  72     }
  73
  74     public static function getOpName($iOpId)
  75     {
  76         switch($iOpId)
  77         {
  78             case FILTER_EQUALS:
  79                 return 'equal to';
  80             case FILTER_LIKE:
  81                 return 'like';
  82             case FILTER_CONTAINS:
  83                 return 'contains';
  84             case FILTER_STARTS_WITH:
  85                 return 'starts with';
  86             case FILTER_ENDS_WITH:
  87                 return 'ends with';
  88             case FILTER_NOT_LIKE:
  89                 return 'not like';
  90             case FILTER_NOT_EQUALS:
  91                 return 'not equal to';
  92             case FILTER_LESS_THAN:
  93                 return 'less than';
  94             case FILTER_GREATER_THAN:
  95                 return 'greater than';
  96         }
  97     }
  98 }
  99
 100 /* Class handling tables where the user can filter contents */
 101 class FilterInterface
 102 {
 103     private $aFilterInfo;
 104     private $oFilterSet;
 105     private $aEscapeChars;
 106     private $aEscapeCharsWith;
 107     private $sErrors; // Used to inform the user about errors (and to give advice)
 108
 109     public function FilterInterface($sTableName = '')
 110     {
 111         $this->aFilterInfo = array();
 112         $this->oFilterSet = new FilterSet(query_escape_string($sTableName));
 113         $this->aEscapeChars = array('.');
 114         $this->aEscapeCharsWith = array('-');
 115         $this->sErrors = '';
 116     }
 117
 118     public function AddFilterObject(Filter $oFilter)
 119     {
 120         $this->oFilterSet->AddFilterObject($oFilter);
 121     }
 122
 123     public function setFilterSet(FilterSet $oSet)
 124     {
 125         $this->oFilterSet = $oSet;
 126     }
 127
 128     /* Convenience function to add a filter option */
 129     public function AddFilterInfo($sColumn, $sDisplayName, $aTypes, $iValueType = VALUE_TYPE_NORMAL, $aValueTypeData = array(), $aValueTypeDisplay = array())
 130     {
 131         $this->aFilterInfo[$sColumn] = new FilterInfo($sColumn, $sDisplayName, $aTypes, $iValueType, $aValueTypeData, $aValueTypeDisplay);
 132     }
 133
 134     /* We can't use some special chars in variable names, such as '.' */
 135     public function escapeChars($sIn)
 136     {
 137         return str_replace($this->aEscapeChars, $this->aEscapeCharsWith, $sIn);
 138     }
 139
 140     public function unescapeChars($sIn)
 141     {
 142         return str_replace($this->aEscapeWith, $this->aEscape, $sIn);
 143     }
 144
 145     public function getUrlElement($iId, Filter $oFilter)
 146     {
 147         $sColumn = $this->escapeChars($oFilter->getColumn());
 148         $oColumn = $this->aFilterInfo[$sColumn];
 149
 150         $sId = $iId;
 151
 152         $shEditor = "&i{$sColumn}Op$sId={$oFilter->getOperatorId()}";
 153         $shEditor .= "&s{$sColumn}Data$sId={$oFilter->getData()}";
 154
 155         return $shEditor;
 156     }
 157
 158     public function getHiddenInputTag($iId, Filter $oFilter)
 159     {
 160         $sColumn = $this->escapeChars($oFilter->getColumn());
 161         $oColumn = $this->aFilterInfo[$sColumn];
 162
 163         $sId = $iId;
 164
 165         $shEditor = "<input type=\"hidden\" name=\"i{$sColumn}Op$sId\" value=\"{$oFilter->getOperatorId()}\">";
 166         $shEditor .= "<input type=\"hidden\" name=\"s{$sColumn}Data$sId\" value=\"{$oFilter->getData()}\" />";
 167
 168         return $shEditor;
 169     }
 170
 171     public function getOptionBoolEditor($iId, Filter $oFilter)
 172     {
 173         $sColumn = $this->escapeChars($oFilter->getColumn());
 174         $oColumn = $this->aFilterInfo[$oFilter->getColumn()];
 175         $sId = ($iId == -1) ? '' : $iId;
 176
 177         $aTypes = $oColumn->getTypes();
 178         $iOp = $aTypes[0];
 179
 180         if($iId == -1)
 181         {
 182             /* The first entry in the list of choices is the default */
 183             $aValues = $oColumn->getValueTypeData();
 184             $sData = $aValues[0];
 185         } else
 186         {
 187             $sData = $oFilter->getData();
 188         }
 189
 190         $shRet = "<input type=\"hidden\" name=\"i{$sColumn}Op$sId\" value=\"$iOp\" />";
 191
 192         if($sData == 'true')
 193             $sChecked = ' checked="checked"';
 194         else
 195             $sChecked = '';
 196
 197         $shRet .= "<input value=\"true\" $sChecked name=\"s{$sColumn}Data$sId\" type=\"checkbox\" />";
 198         $shRet .= ' '.$oColumn->getDisplayName();
 199
 200         return $shRet;
 201     }
 202
 203     public function getItemEditor($iId, Filter $oFilter)
 204     {
 205         $sColumn = $this->escapeChars($oFilter->getColumn());
 206         $oColumn = $this->aFilterInfo[$oFilter->getColumn()];
 207
 208         $sId = ($iId == -1) ? '' : $iId;
 209         $shEditor = $oColumn->getDisplayName().' ';
 210
 211         $aTypes = $oColumn->getTypes();
 212
 213         /* It doesn't make sense to show a dropdown menu of choices if there is only one
 214            If the filter is already active then there are more than one; one to remove */
 215         if($iId == -1 && sizeof($aTypes) == 1)
 216         {
 217             echo "<input type=\"hidden\" name=\"i{$sColumn}Op$sId\" value=\"{$aTypes[0]}\" />";
 218
 219             /* Printing 'equal to' sounds weird if it is the only choice */
 220             if($aTypes[0] != FILTER_EQUALS)
 221                 $shEditor .= $oColumn->getOpName($aTypes[0]);
 222         } else
 223         {
 224             $shEditor .= "<select name='i{$sColumn}Op$sId'>";
 225
 226             if($iId != -1)
 227             {
 228                 $sSel = '';
 229                 $sText = 'remove';
 230
 231                 $shEditor .= "<option value='0'$sSel>-- $sText --</option>";
 232             }
 233
 234             foreach($aTypes as $iType)
 235             {
 236                 if($oFilter->getOperatorId() == $iType)
 237                     $sSel = " selected='selected'";
 238                 else
 239                     $sSel = '';
 240                 $shEditor .= "<option value='$iType'$sSel>".$oColumn->getOpName($iType).'</option><br />';
 241             }
 242             $shEditor .= '</select> ';
 243         }
 244
 245         switch($oColumn->getValueType())
 246         {
 247             case FILTER_VALUES_NORMAL:
 248                 $shEditor .= "<input type='text' value=\"{$oFilter->getData()}\" name='s{$sColumn}Data$sId' size='30' />";
 249             break;
 250             case FILTER_VALUES_ENUM:
 251                 $shEditor .= $this->getEnumEditor($oColumn, $oFilter, $sId);
 252             break;
 253         }
 254
 255         return $shEditor;
 256     }
 257
 258     public function getEnumEditor($oColumn, $oFilter, $sId)
 259     {
 260         $sColumn = $this->escapeChars($oFilter->getColumn());
 261         $aOptions = $oColumn->getValueTypeData();
 262         $aOptionNames = $oColumn->getValueTypeDataDisplay();
 263
 264         $sData = $oFilter->getData();
 265
 266         $shEditor = "<select name=\"s{$sColumn}Data$sId\">";
 267
 268         if($sData)
 269             $shEditor .= "<option value=\"\">-- remove --</option>";
 270         else
 271             $shEditor .= "<option value=\"\">-- select --</option>";
 272
 273         for($i = 0; $i < sizeof($aOptions); $i++)
 274         {
 275             $sOption = $aOptions[$i];
 276             $sSelected = '';
 277             if($sData == $sOption)
 278                 $sSelected = ' selected="selected"';
 279             $shEditor .= "<option value=\"$sOption\"$sSelected>{$aOptionNames[$i]}</option>";
 280         }
 281
 282         $shEditor .= "</select>";
 283
 284         return $shEditor;
 285     }
 286
 287     /* Get filter data formatted to fit in a URL */
 288     public function getUrlData()
 289     {
 290         $shEditor = '';
 291         $aCounts = array();
 292
 293         foreach($this->oFilterSet->getFilters() as $oFilter)
 294         {
 295             $sColumn = $oFilter->getColumn();
 296
 297             if(!array_key_exists($sColumn, $aCounts))
 298                 $aCounts[$sColumn] = 0;
 299
 300             $shEditor .= $this->getUrlElement($aCounts[$sColumn], $oFilter);
 301
 302             $shEditor .= '<br />';
 303
 304             $aCounts[$sColumn]++;
 305         }
 306
 307         return $shEditor;
 308     }
 309
 310     /* Get a list of hidden input tags to preserve form data */
 311     public function getHiddenFormData()
 312     {
 313         $shEditor = '';
 314         $aCounts = array();
 315
 316         foreach($this->oFilterSet->getFilters() as $oFilter)
 317         {
 318             $sColumn = $oFilter->getColumn();
 319
 320             if(!array_key_exists($sColumn, $aCounts))
 321                 $aCounts[$sColumn] = 0;
 322
 323             $shEditor .= $this->getHiddenInputTag($aCounts[$sColumn], $oFilter);
 324
 325             $shEditor .= '<br />';
 326
 327             $aCounts[$sColumn]++;
 328         }
 329
 330         return $shEditor;
 331     }
 332
 333     public function getEditor()
 334     {
 335         $shNewItemsEditor = '';
 336         $shCurrentItemsEditor = '';
 337         $aCounts = array();
 338
 339         if(sizeof($this->oFilterSet->getFilters()))
 340              $shCurrentItemsEditor .= '<br /><b>Active filters</b><br />';
 341         foreach($this->oFilterSet->getFilters() as $oFilter)
 342         {
 343             $sColumn = $oFilter->getColumn();
 344
 345             if(!array_key_exists($sColumn, $aCounts))
 346                 $aCounts[$sColumn] = 0;
 347
 348             if($oFilter->getOperatorId() == FILTER_OPTION_BOOL)
 349                 $shCurrentItemsEditor .= $this->getOptionBoolEditor($aCounts[$sColumn], $oFilter);
 350             else
 351                 $shCurrentItemsEditor .= $this->getItemEditor($aCounts[$sColumn], $oFilter);
 352             $shCurrentItemsEditor .= '<br />';
 353
 354             $aCounts[$sColumn]++;
 355         }
 356
 357         $shNewItemsEditor .= '<b>Add new filter</b> <i>(You don&#8217;t have to fill out all rows.)</i><br />';
 358
 359         /* Show errors, if any */
 360         if($this->sErrors)
 361             $shNewItemsEditor .= "<font color=\"red\">{$this->sErrors}</font>";
 362
 363         foreach($this->aFilterInfo as $oOption)
 364         {
 365             $oDummyFilter = new Filter($oOption->getColumn(), 0, '');
 366             $aTypes = $oOption->getTypes();
 367
 368             if($oOption->getValueType() == FILTER_VALUES_OPTION_BOOL)
 369             {
 370                 if(!array_key_exists($oOption->getColumn(), $aCounts))
 371                     $shNewItemsEditor .= $this->getOptionBoolEditor(-1, $oDummyFilter);
 372             } else
 373             {
 374                 $shNewItemsEditor .= $this->getItemEditor(-1, $oDummyFilter);
 375             }
 376             $shNewItemsEditor .= '<br />';
 377         }
 378
 379         return $shNewItemsEditor.$shCurrentItemsEditor;
 380     }
 381
 382     public function getFilterInfo()
 383     {
 384         return $this->aFilterInfo;
 385     }
 386
 387     /* Reads all input related to filters for the given table column */
 388     public function readInputForColumn($aClean, FilterInfo $oOption)
 389     {
 390         $aReturn = array();
 391         $bChangedOption = false;
 392
 393         for($i = 0; array_key_exists('i'.$this->escapeChars($oOption->getColumn())."Op$i", $aClean); $i++)
 394         {
 395             $sColumn = $this->escapeChars($oOption->getColumn());
 396             $sData = query_escape_string(getInput("s{$sColumn}Data$i", $aClean));
 397             $iOp = $aClean["i{$sColumn}Op$i"];
 398
 399             if(!$iOp)
 400                 continue;
 401
 402             $oFilter = new Filter($oOption->getColumn(), $iOp, $sData);
 403
 404             /* Only show an option as an active filter if it has been changed
 405                from the default */
 406             if($oOption->getValueType() == FILTER_VALUES_OPTION_BOOL)
 407             {
 408                 /* The default option is the first entry in the list of choices */
 409                 $aChoices = $oOption->getValueTypeData();
 410                 $sDefault = $aChoices[0];
 411                 if(!$sData)
 412                     $sData = 'false';
 413                 if($sData == $sDefault)
 414                     continue;
 415                 $bChangedOption = true;
 416             }
 417
 418             $aReturn[] = $oFilter;
 419         }
 420
 421         if(array_key_exists('i'.$this->escapeChars($oOption->getColumn())."Op", $aClean))
 422         {
 423             $sColumn = $this->escapeChars($oOption->getColumn());
 424             $i = sizeof($aReturn);
 425             $sData = query_escape_string($aClean["s{$sColumn}Data"]);
 426             $iOp = $aClean["i{$sColumn}Op"];
 427
 428
 429             if($iOp && $sData && ($oOption->getValueType() != FILTER_VALUES_OPTON_BOOL || !$bChangedOoption))
 430             {
 431                 $oFilter = new Filter($oOption->getColumn(), $iOp, $sData);
 432                 $aReturn[] = $oFilter;
 433             } else if(!$iOp && $sData)
 434             {
 435                 /* The user probably meant to add a filter, but forgot to seelect
 436                    a filter criterion */
 437                 $this->sErrors .= 'You need to select a filter criterion from the drop-down list<br />';
 438             }
 439         }
 440
 441         return $aReturn;
 442     }
 443
 444     /* Reads an input array get enabled filters from form data.
 445        The given TableFilterSet defines available options */
 446     public function readInput($aClean)
 447     {
 448         $iCount = 0; // We set a maximum for how many filters a user can add,
 449                      // otherwise we may get a too long SQL query
 450
 451         foreach($this->getFilterInfo() as $oOption)
 452         {
 453             foreach($this->readInputForColumn($aClean, $oOption) as $oNewFilter)
 454             {
 455                 $iCount ++;
 456                 $this->AddFilterObject($oNewFilter);
 457                 if($iCount > MAX_FILTERS)
 458                     break;
 459             }
 460         }
 461     }
 462
 463     public function loadTable($sTableName)
 464     {
 465         $this->oFilterSet->loadTable($sTableName);
 466     }
 467
 468     public function saveTable($sTableName)
 469     {
 470         $this->oFilterSet->saveTable($sTableName);
 471     }
 472
 473     public function getFilterCount()
 474     {
 475         return $this->oFilterSet->getFilterCount();
 476     }
 477
 478      /* Returns an array of options, where the keys are the columns and the members
 479         are the settings themselves */
 480     public function getOptions()
 481     {
 482         $aOptions = array();
 483         foreach($this->oFilterSet->getFilters() as $oFilter)
 484         {
 485             if($oFilter->getOperatorId() == FILTER_OPTION_BOOL)
 486                 $aOptions[$oFilter->getColumn()] = $oFilter->getData();
 487         }
 488         foreach($this->aFilterInfo as $oFilterInfo)
 489         {
 490             if($oFilterInfo->getValueType() == FILTER_VALUES_OPTION_BOOL &&
 491                !array_key_exists($oFilterInfo->getColumn(), $aOptions))
 492             {
 493                 $aTypes = $oFilterInfo->getTypes();
 494                 $sDefault = $aTypes[0];
 495                 $aOptions[$oFilterInfo->getColumn()] = $sDefault;
 496             }
 497         }
 498         return $aOptions;
 499     }
 500
 501     public function getWhereClause()
 502     {
 503         return $this->oFilterSet->getWhereClause();
 504     }
 505
 506     public function getTable($sTable, $iLimit = 0)
 507     {
 508         $hResult = $this->oFilterSet->getMatchedItems($sTable, $iLimit);
 509
 510         if(!$hResult)
 511             return;
 512
 513         echo 'Selected '.$this->oFilterSet->getMatchedItemsCount($sTable).' rows<br><br>';
 514
 515         $oTable = new Table();
 516
 517         while($aRow = mysql_fetch_row($hResult))
 518         {
 519             $oRow = new TableRow();
 520
 521             foreach($aRow as $sCell)
 522             {
 523                 $oRow->AddTextCell($sCell);
 524             }
 525
 526             $oTable->AddRow($oRow);
 527         }
 528
 529         return $oTable->getString();
 530     }
 531 }
 532
 533 ?>