3 # FILE: SavedSearch.php
6 # - the "$SearchGroups" values used herein contain a multi-dimentional
7 # array in the form of:
8 # $Criteria["MAIN"]["SearchStrings"][<field names>] = <value>
9 # for fields with a single value, and:
10 # $Criteria[<field ID>]["SearchStrings"][<field name>][] = <value>
11 # for fields with multiple values
14 # Part of the Collection Workflow Integration System (CWIS)
15 # Copyright 2011-2013 Edward Almasy and Internet Scout Research Group
16 # http://scout.wisc.edu/cwis/
21 # ---- PUBLIC INTERFACE --------------------------------------------------
23 # search frequency mnemonics
34 function SavedSearch($SearchId, $SearchName = NULL, $UserId = NULL,
35 $Frequency = NULL, $SearchGroups = NULL)
37 # get our own database handle
40 # if search ID was provided
41 if ($SearchId !== NULL)
44 $this->SearchId = intval($SearchId);
46 # initialize our local copies of data
47 $this->DB->Query(
"SELECT * FROM SavedSearches"
48 .
" WHERE SearchId = '".$this->SearchId.
"'");
49 $this->Record = $this->DB->FetchRow();
51 # update search details where provided
52 if ($SearchName) { $this->
SearchName($SearchName); }
53 if ($UserId) { $this->
UserId($UserId); }
54 if ($Frequency) { $this->
Frequency($Frequency); }
58 # add new saved search to database
59 $this->DB->Query(
"INSERT INTO SavedSearches"
60 .
" (SearchName, UserId, Frequency) VALUES ("
61 .
"'".addslashes($SearchName).
"', "
63 .intval($Frequency).
")");
65 # retrieve and save ID of new search locally
66 $this->SearchId = $this->DB->LastInsertId();
68 # save frequency and user ID locally
69 $this->Record[
"SearchName"] = $SearchName;
70 $this->Record[
"UserId"] = $UserId;
71 $this->Record[
"Frequency"] = $Frequency;
74 # save search parameters if provided
75 if ($SearchGroups) { $this->
SearchGroups($SearchGroups); }
78 # get/set search parameters
83 # if new search parameters were supplied
86 # remove existing entries for this search from the database
87 $this->DB->Query(
"DELETE FROM SavedSearchTextParameters WHERE SearchId = ".$this->SearchId);
88 $this->DB->Query(
"DELETE FROM SavedSearchIdParameters WHERE SearchId = ".$this->SearchId);
90 # for each search group
91 foreach ($NewSearchGroups as $GroupIndex => $Group)
93 # if group holds single parameters
94 if ($GroupIndex ==
"MAIN")
96 # for each field within group
97 foreach ($Group[
"SearchStrings"] as $FieldName => $Value)
99 # convert value array to single value (if necessary)
100 if (is_array($Value))
102 $ConvertedValue =
"";
103 foreach ($Value as $SingleValue)
105 $ConvertedValue .= $SingleValue.
" ";
107 $Value = trim($ConvertedValue);
110 # add new text search parameter entry to database
111 if ($FieldName ==
"XXXKeywordXXX")
117 $Field = $Schema->GetFieldByName($FieldName);
118 $FieldId = $Field->Id();
120 $this->DB->Query(
"INSERT INTO SavedSearchTextParameters"
121 .
" (SearchId, FieldId, SearchText) VALUES"
122 .
" (".$this->SearchId.
", ".$FieldId.
", '".addslashes($Value).
"')");
127 # convert value(s) as appropriate for field type
128 $FieldId = ($GroupIndex[0] ==
"X")
129 ? substr($GroupIndex, 1)
131 $Field = $Schema->GetField($FieldId);
132 $FieldName = $Field->Name();
133 $Values = SavedSearch::TranslateValues($Field, $Group[
"SearchStrings"][$FieldName],
"SearchGroup to Database");
135 # for each converted value
136 foreach ($Values as $Value)
138 # add new ID search parameter entry to database
139 $this->DB->Query(
"INSERT INTO SavedSearchIdParameters"
140 .
" (SearchId, FieldId, SearchValueId) VALUES"
141 .
" (".$this->SearchId.
", ".$FieldId.
", ".$Value.
")");
146 # save search parameters locally
151 # if search groups not already read in
154 # for each text search parameter
155 $SearchGroups = array();
156 $this->DB->Query(
"SELECT * FROM SavedSearchTextParameters"
157 .
" WHERE SearchId = ".$this->SearchId);
158 while ($Record = $this->DB->FetchRow())
160 # add parameter to search criteria
161 if ($Record[
"FieldId"] == -101)
163 $SearchGroups[
"MAIN"][
"SearchStrings"][
"XXXKeywordXXX"] =
164 $Record[
"SearchText"];
168 $Field = $Schema->GetField($Record[
"FieldId"]);
169 $SearchGroups[
"MAIN"][
"SearchStrings"][$Field->Name()] =
170 $Record[
"SearchText"];
174 # for each value ID search parameter
175 $this->DB->Query(
"SELECT * FROM SavedSearchIdParameters"
176 .
" WHERE SearchId = ".$this->SearchId);
177 while ($Record = $this->DB->FetchRow())
179 # translate value based on field type
180 $FieldId = $Record[
"FieldId"];
181 if (!isset($Fields[$FieldId]))
183 $Fields[$FieldId] = $Schema->GetField($FieldId);
185 $Values = SavedSearch::TranslateValues($Fields[$FieldId],
186 $Record[
"SearchValueId"],
"Database to SearchGroup");
188 # add parameter to search criteria
189 foreach ($Values as $Value)
191 $SearchGroups[$FieldId][
"SearchStrings"]
192 [$Fields[$FieldId]->Name()][] = $Value;
196 # set appropriate logic in search parameters
197 foreach ($SearchGroups as $GroupIndex => $Group)
199 $SearchGroups[$GroupIndex][
"Logic"] =
204 # save search parameters locally
209 # return search parameters to caller
210 return $this->SearchGroups;
219 {
return $this->UpdateValue(
"SearchName", $NewValue); }
225 function Id() {
return $this->SearchId; }
233 {
return $this->UpdateValue(
"UserId", $NewValue); }
241 {
return $this->UpdateValue(
"Frequency", $NewValue); }
243 # set date search was last run to current date/time
246 $this->DB->Query(
"UPDATE SavedSearches SET DateLastRun = NOW() WHERE SearchId = ".$this->SearchId);
249 # get/set date search was last run
251 {
return $this->UpdateValue(
"DateLastRun", $NewValue); }
260 return self::TranslateSearchGroupsToUrlParameters($this->
SearchGroups());
302 # assume that no parameters will be found
305 # for each group in parameters
307 foreach ($SearchGroups as $GroupIndex => $Group)
309 # if group holds single parameters
310 if ($GroupIndex ==
"MAIN")
312 # for each field within group
313 foreach ($Group[
"SearchStrings"] as $FieldName => $Value)
315 # add segment to URL for this field
316 if ($FieldName ==
"XXXKeywordXXX")
322 $Field = $Schema->GetFieldByName($FieldName);
323 $FieldId = $Field->Id();
325 if (is_array($Value))
327 $UrlPortion .=
"&F".$FieldId.
"=";
329 foreach ($Value as $SingleValue)
331 $ValueString .= $SingleValue.
" ";
333 $UrlPortion .= urlencode(trim($ValueString));
337 $UrlPortion .=
"&F".$FieldId.
"=".urlencode($Value);
343 # convert value based on field type
344 $FieldId = ($GroupIndex[0] ==
"X")
345 ? substr($GroupIndex, 1)
347 $Field = $Schema->GetField($FieldId);
348 $FieldName = $Field->Name();
349 $Values = SavedSearch::TranslateValues($Field,
350 $Group[
"SearchStrings"][$FieldName],
351 "SearchGroup to Database");
355 foreach ($Values as $Value)
360 $UrlPortion .=
"&G".$FieldId.
"=".$Value;
364 $UrlPortion .=
"-".$Value;
370 # trim off any leading "&"
371 if (strlen($UrlPortion)) { $UrlPortion = substr($UrlPortion, 1); }
373 # return URL portion to caller
384 return self::TranslateSearchGroupsToUrlParameters($this->
SearchGroups());
395 # assume that no parameters will be found
396 $UrlPortion = array();
398 # for each group in parameters
400 foreach ($SearchGroups as $GroupIndex => $Group)
402 # if group holds single parameters
403 if ($GroupIndex ==
"MAIN")
405 # for each field within group
406 foreach ($Group[
"SearchStrings"] as $FieldName => $Value)
408 # add segment to URL for this field
409 if ($FieldName ==
"XXXKeywordXXX")
415 $Field = $Schema->GetFieldByName($FieldName);
416 $FieldId = $Field->Id();
418 if (is_array($Value))
421 foreach ($Value as $SingleValue)
423 $ValueString .= $SingleValue.
" ";
426 $UrlPortion[
"F".$FieldId] = urlencode(trim($ValueString));
430 $UrlPortion[
"F".$FieldId] = urlencode($Value);
436 # convert value based on field type
437 $FieldId = ($GroupIndex[0] ==
"X")
438 ? substr($GroupIndex, 1)
440 $Field = $Schema->GetField($FieldId);
441 $FieldName = $Field->Name();
442 $Values = SavedSearch::TranslateValues($Field,
443 $Group[
"SearchStrings"][$FieldName],
444 "SearchGroup to Database");
450 foreach ($Values as $Value)
455 $UrlPortion[$LeadChar.$FieldId] = $Value;
459 $UrlPortion[$LeadChar.$FieldId] .=
"-".$Value;
465 # return URL portion to caller
469 # set search groups from URL (GET method) parameters
470 # (returns search group array)
473 # if URL segment was passed in instead of GET var array
474 if (is_string($GetVars))
476 $GetVars = ParseQueryString($GetVars);
479 # start with empty list of parameters
480 $SearchGroups = array();
483 $AllFields = $Schema->GetFields(NULL, NULL, TRUE);
485 foreach ($AllFields as $Field)
487 $FieldId = $Field->Id();
488 $FieldName = $Field->Name();
490 # if URL included literal value for this field
491 if (isset($GetVars[
"F".$FieldId]))
493 # retrieve value and add to search parameters
494 $SearchGroups[
"MAIN"][
"SearchStrings"][$FieldName] =
495 $GetVars[
"F".$FieldId];
498 # if URL included group value for this field
499 if (isset($GetVars[
"G".$FieldId]))
501 # retrieve and parse out values
502 $Values = explode(
"-", $GetVars[
"G".$FieldId]);
505 $Values = SavedSearch::TranslateValues($Field, $Values,
506 "Database to SearchGroup");
508 # add values to searchgroups
509 $SearchGroups[$FieldId][
"SearchStrings"][$FieldName] = $Values;
512 # if URL included group value for this field
513 if (isset($GetVars[
"H".$FieldId]))
515 # retrieve and parse out values
516 $Values = explode(
"-", $GetVars[
"H".$FieldId]);
519 $Values = SavedSearch::TranslateValues($Field, $Values,
520 "Database to SearchGroup");
522 # add values to searchgroups
523 $SearchGroups[
"X".$FieldId][
"SearchStrings"][$FieldName] = $Values;
527 # if keyword pseudo-field was included in URL
528 if (isset($GetVars[
"FK"]))
530 # retrieve value and add to search parameters
531 $SearchGroups[
"MAIN"][
"SearchStrings"][
"XXXKeywordXXX"] = $GetVars[
"FK"];
535 foreach ($SearchGroups as $GroupIndex => $Group)
537 $SearchGroups[$GroupIndex][
"Logic"] = ($GroupIndex ==
"MAIN")
539 : (($GroupIndex[0] ==
"X")
543 # return parameters to caller
544 return $SearchGroups;
558 $IncludeHtml = TRUE, $StartWithBreak = TRUE, $TruncateLongWordsTo = 0)
560 return self::TranslateSearchGroupsToTextDescription($this->
SearchGroups(),
561 $IncludeHtml, $StartWithBreak, $TruncateLongWordsTo);
576 $IncludeHtml = TRUE, $StartWithBreak = TRUE, $TruncateLongWordsTo = 0)
580 # start with empty description
583 # set characters used to indicate literal strings
584 $LiteralStart = $IncludeHtml ?
"<i>" :
"\"";
585 $LiteralEnd = $IncludeHtml ?
"</i>" :
"\"";
586 $LiteralBreak = $IncludeHtml ?
"<br>\n" :
"\n";
588 # if this is a simple keyword search
589 if (isset($SearchGroups[
"MAIN"][
"SearchStrings"][
"XXXKeywordXXX"])
590 && (count($SearchGroups) == 1)
591 && (count($SearchGroups[
"MAIN"][
"SearchStrings"]) == 1))
593 # just use the search string
594 $Descrip .= $LiteralStart;
595 $Descrip .= defaulthtmlentities($SearchGroups[
"MAIN"][
"SearchStrings"][
"XXXKeywordXXX"]);
596 $Descrip .= $LiteralEnd . $LiteralBreak;
600 # start description on a new line (if requested)
603 $Descrip .= $LiteralBreak;
606 # define list of phrases used to represent logical operators
607 $WordsForOperators = array(
609 ">" =>
"is greater than",
610 "<" =>
"is less than",
611 ">=" =>
"is at least",
612 "<=" =>
"is no more than",
616 # for each search group
617 foreach ($SearchGroups as $GroupIndex => $Group)
620 if ($GroupIndex ==
"MAIN")
622 # for each field in group
623 foreach ($Group[
"SearchStrings"] as $FieldName => $Value)
625 # determine wording based on operator
626 preg_match(
"/^[=><!]+/", $Value, $Matches);
627 if (count($Matches) && isset($WordsForOperators[$Matches[0]]))
629 $Value = preg_replace(
"/^[=><!]+/",
"", $Value);
630 $Wording = $WordsForOperators[$Matches[0]];
634 $Wording =
"contains";
637 # if field is psuedo-field
638 if ($FieldName ==
"XXXKeywordXXX")
640 # add criteria for psuedo-field
641 $Descrip .=
"Keyword ".$Wording.
" "
642 .$LiteralStart.htmlspecialchars($Value)
643 .$LiteralEnd.$LiteralBreak;
648 $Field = $Schema->GetFieldByName($FieldName);
651 # add criteria for field
652 $Descrip .= $Field->GetDisplayName().
" ".$Wording.
" "
653 .$LiteralStart.htmlspecialchars($Value)
654 .$LiteralEnd.$LiteralBreak;
661 # for each field in group
664 foreach ($Group[
"SearchStrings"] as $FieldName => $Values)
667 $Values = SavedSearch::TranslateValues(
668 $FieldName, $Values,
"SearchGroup to Display");
672 foreach ($Values as $Value)
674 # determine wording based on operator
675 preg_match(
"/^[=><!]+/", $Value, $Matches);
676 $Operator = $Matches[0];
677 $Wording = $WordsForOperators[$Operator];
680 $Value = preg_replace(
"/^[=><!]+/",
"", $Value);
682 # add text to description
685 $Descrip .= $FieldName.
" ".$Wording.
" "
686 .$LiteralStart.htmlspecialchars($Value)
687 .$LiteralEnd.$LiteralBreak;
692 $Descrip .= ($IncludeHtml ?
" " :
" ")
693 .$LogicTerm.$Wording.
" ".$LiteralStart
694 .htmlspecialchars($Value).$LiteralEnd
703 # if caller requested that long words be truncated
704 if ($TruncateLongWordsTo > 4)
706 # break description into words
707 $Words = explode(
" ", $Descrip);
711 foreach ($Words as $Word)
713 # if word is longer than specified length
714 if (strlen(strip_tags($Word)) > $TruncateLongWordsTo)
716 # truncate word and add ellipsis
717 $Word = NeatlyTruncateString($Word, $TruncateLongWordsTo - 3);
720 # add word to new description
721 $NewDescrip .=
" ".$Word;
724 # set description to new description
725 $Descrip = $NewDescrip;
728 # return description to caller
738 return self::TranslateSearchGroupsToSearchFieldNames($this->
SearchGroups());
748 # start out assuming no fields are being searched
749 $FieldNames = array();
751 # for each search group defined
752 foreach ($SearchGroups as $GroupIndex => $Group)
754 # for each field in group
755 foreach ($Group[
"SearchStrings"] as $FieldName => $Values)
757 # add field name to list of fields being searched
758 $FieldNames[] = $FieldName;
762 # return list of fields being searched to caller
773 # define list with descriptions
775 self::SEARCHFREQ_NEVER =>
"Never",
776 self::SEARCHFREQ_HOURLY =>
"Hourly",
777 self::SEARCHFREQ_DAILY =>
"Daily",
778 self::SEARCHFREQ_WEEKLY =>
"Weekly",
779 self::SEARCHFREQ_BIWEEKLY =>
"Bi-Weekly",
780 self::SEARCHFREQ_MONTHLY =>
"Monthly",
781 self::SEARCHFREQ_QUARTERLY =>
"Quarterly",
782 self::SEARCHFREQ_YEARLY =>
"Yearly",
785 # for each argument passed in
786 $Args = func_get_args();
787 foreach ($Args as $Arg)
789 # remove value from list
790 $FreqDescr = array_diff_key($FreqDescr, array($Arg =>
""));
793 # return list to caller
802 $this->DB->Query(
"DELETE FROM SavedSearches"
803 .
" WHERE SearchId = ".intval($this->SearchId));
804 $this->DB->Query(
"DELETE FROM SavedSearchTextParameters"
805 .
" WHERE SearchId = ".intval($this->SearchId));
806 $this->DB->Query(
"DELETE FROM SavedSearchIdParameters"
807 .
" WHERE SearchId = ".intval($this->SearchId));
811 # ---- PRIVATE INTERFACE -------------------------------------------------
815 private $SearchGroups;
817 # utility function to convert between value representations
818 # (method accepts a value or array and always return an array)
819 # (this is needed because values are represented differently:
821 # in DB / in URL / in forms 0/1 123 456
822 # used in SearchGroups 0/1 jdoe cname
823 # displayed to user On/Off jdoe cname
824 # where "123" and "456" are option or controlled name IDs)
825 private static function TranslateValues($FieldOrFieldName, $Values, $TranslationType)
827 # start out assuming we won't find any values to translate
828 $ReturnValues = array();
830 # convert field name to field object if necessary
831 if (is_object($FieldOrFieldName))
833 $Field = $FieldOrFieldName;
839 $Field = $Schema->GetFieldByName($FieldOrFieldName);
842 # if incoming value is not an array
843 if (!is_array($Values))
845 # convert incoming value to an array
846 $Values = array($Values);
849 # for each incoming value
850 foreach ($Values as $Value)
852 switch ($TranslationType)
854 case "SearchGroup to Display":
855 # if field is Flag field
858 # translate value to true/false label and add leading operator
859 $ReturnValues[] = ($Value ==
"=1") ?
"=".$Field->FlagOnLabel() :
"=".$Field->FlagOffLabel();
861 elseif ($Field->Name() ==
"Cumulative Rating")
863 # translate numeric value to stars
864 $StarStrings = array(
871 preg_match(
"/[0-9]+$/", $Value, $Matches);
872 $Number = $Matches[0];
873 preg_match(
"/^[=><!]+/", $Value, $Matches);
874 $Operator = $Matches[0];
875 $ReturnValues[] = $Operator.$StarStrings[$Number];
880 $ReturnValues[] = $Value;
884 case "SearchGroup to Database":
885 # strip off leading operator on value
886 $Value = preg_replace(
"/^[=><!]+/",
"", $Value);
888 # look up index for value
891 # (for flag or number fields the value index is already what is used in SearchGroups)
894 $ReturnValues[] = $Value;
899 # (for user fields the value index is the user ID)
900 $User =
new CWUser(strval($Value));
903 $ReturnValues[] = $User->Id();
908 if (!isset($PossibleFieldValues))
910 $PossibleFieldValues = $Field->GetPossibleValues();
912 $NewValue = array_search($Value, $PossibleFieldValues);
913 if ($NewValue !== FALSE)
915 $ReturnValues[] = $NewValue;
920 $NewValue = $Field->GetIdForValue($Value);
921 if ($NewValue !== NULL)
923 $ReturnValues[] = $NewValue;
928 case "Database to SearchGroup":
929 # look up value for index
932 # (for flag fields the value index (0 or 1) is already what is used in Database)
935 $ReturnValues[] =
"=".$Value;
940 # (for flag fields the value index (0 or 1) is already what is used in Database)
943 $ReturnValues[] =
">=".$Value;
948 $User =
new CWUser(intval($Value));
951 $ReturnValues[] =
"=".$User->Get(
"UserName");
956 if (!isset($PossibleFieldValues))
958 $PossibleFieldValues = $Field->GetPossibleValues();
961 if (isset($PossibleFieldValues[$Value]))
963 $ReturnValues[] =
"=".$PossibleFieldValues[$Value];
968 $NewValue = $Field->GetValueForId($Value);
969 if ($NewValue !== NULL)
971 $ReturnValues[] =
"=".$NewValue;
978 # return array of translated values to caller
979 return $ReturnValues;
984 # utility function for updating values in database
985 private function UpdateValue($FieldName, $NewValue)
987 return $this->DB->UpdateValue(
"SavedSearches", $FieldName, $NewValue,
988 "SearchId = ".$this->SearchId, $this->Record);
991 # legacy methods for backward compatibility
static TranslateUrlParametersToSearchGroups($GetVars)
SQL database abstraction object with smart query caching.
static TranslateSearchGroupsToUrlParameterArray($SearchGroups)
Translate a search group array to an URL parameter array.
const SEARCHFREQ_QUARTERLY
UserId($NewValue=DB_NOVALUE)
Get/set user ID.
Frequency($NewValue=DB_NOVALUE)
Get/set search frequency.
SavedSearch($SearchId, $SearchName=NULL, $UserId=NULL, $Frequency=NULL, $SearchGroups=NULL)
const SEARCHFREQ_BIWEEKLY
static TranslateSearchGroupsToTextDescription($SearchGroups, $IncludeHtml=TRUE, $StartWithBreak=TRUE, $TruncateLongWordsTo=0)
Translate search group array into multi-line string describing search criteria.
DateLastRun($NewValue=DB_NOVALUE)
static TranslateSearchGroupsToUrlParameters($SearchGroups)
Translate search group array into URL parameters (e.g.
GetSearchGroupsAsTextDescription($IncludeHtml=TRUE, $StartWithBreak=TRUE, $TruncateLongWordsTo=0)
Get multi-line string describing search criteria.
SearchGroups($NewSearchGroups=NULL)
GetSearchFieldNames()
Get list of fields to be searched.
Delete()
Delete saved search.
SearchName($NewValue=DB_NOVALUE)
Get/set name of search.
GetSearchGroupsAsUrlParameterArray()
Get search groups as an URL parameter array.
GetSearchGroupsAsUrlParameters()
Get search groups as URL parameters (e.g.
CWIS-specific user class.
static GetSearchFrequencyList()
Get array of possible search frequency descriptions.
static TranslateSearchGroupsToSearchFieldNames($SearchGroups)
Extract list of fields to be searched from search group array.