searchutil.inc.php
5.88 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
<?php
class KTSearchUtil {
function _oneCriteriaSetToSQL($aOneCriteriaSet) {
$aSQL = array();
$aJoinSQL = array();
$criteria_set = array();
foreach ($aOneCriteriaSet as $order => $dataset) {
$type = KTUtil::arrayGet($dataset, "type");
$sql = KTUtil::arrayGet($dataset, "sql");
if (!empty($type)) {
$oCriterion = Criteria::getCriterionByNumber($dataset["type"]);
if (PEAR::isError($oCriterion)) {
return PEAR::raiseError('Invalid criteria specified.');
}
$criteria_set[] = array($oCriterion, $dataset["data"]);
} else if (!empty($sql)) {
$criteria_set[] = $sql;
} else {
return PEAR::raiseError('Invalid criteria specified.');
}
}
foreach ($criteria_set as $oCriterionPair) {
$oCriterion = $oCriterionPair[0];
$aReq = $oCriterionPair[1];
if (is_object($oCriterion)) {
$res = $oCriterion->searchSQL($aReq);
if (!is_null($res)) {
$aSQL[] = $res;
}
$res = $oCriterion->searchJoinSQL();
if (!is_null($res)) {
$aJoinSQL[] = $res;
}
} else {
$aSQL[] = array($oCriterion, $aReq);
}
}
$aCritParams = array();
$aCritQueries = array();
foreach ($aSQL as $sSQL) {
if (is_array($sSQL)) {
$aCritQueries[] = '('.$sSQL[0].')';
$aCritParams = array_merge($aCritParams , $sSQL[1]);
} else {
$aCritQueries[] = '('.$sSQL.')';
}
}
if (count($aCritQueries) == 0) {
return PEAR::raiseError("No search criteria were specified");
}
return array($aCritQueries, $aCritParams, $aJoinSQL);
}
function criteriaSetToSQL($aCriteriaSet, $iRecurseLevel = 0) {
$aJoinSQL = array();
$aSearchStrings = array();
$aParams = array();
foreach ($aCriteriaSet["subgroup"] as $k => $aOneCriteriaSet) {
$aValues = KTUtil::arrayGet($aOneCriteriaSet, "values");
$aSubgroup = KTUtil::arrayGet($aOneCriteriaSet, "subgroup");
if (!empty($aValues)) {
list($aThisCritQueries, $aThisParams, $aThisJoinSQL) = KTSearchUtil::_oneCriteriaSetToSQL($aOneCriteriaSet["values"]);
$aJoinSQL = array_merge($aJoinSQL, $aThisJoinSQL);
$aParams = array_merge($aParams, $aThisParams);
$tabs = str_repeat("\t", ($iRecurseLevel + 2));
$aSearchStrings[] = "\n$tabs(\n$tabs\t" . join("\n " . KTUtil::arrayGet($aOneCriteriaSet, 'join', "AND") . " ", $aThisCritQueries) . "\n$tabs)";
} else if (!empty($aSubgroup)) {
list($sThisSearchString, $aThisParams, $sThisJoinSQL) =
KTSearchUtil::criteriaSetToSQL($aOneCriteriaSet, $iRecurseLevel + 1);
$aJoinSQL[] = $sThisJoinSQL;
$aParams = array_merge($aParams, $aThisParams);
$aSearchStrings[] = $sThisSearchString;
}
}
$sJoinSQL = join(" ", $aJoinSQL);
$tabs = str_repeat("\t", $iRecurseLevel + 1);
$sSearchString = "\n$tabs(" . join("\n$tabs\t" . $aCriteriaSet['join'] . " ", $aSearchStrings) . "\n$tabs)";
return array($sSearchString, $aParams, $sJoinSQL);
}
function permissionToSQL($oUser, $sPermissionName) {
$oPermission =& KTPermission::getByName('ktcore.permissions.read');
$sPermissionLookupsTable = KTUtil::getTableName('permission_lookups');
$sPermissionLookupAssignmentsTable = KTUtil::getTableName('permission_lookup_assignments');
$sPermissionDescriptorsTable = KTUtil::getTableName('permission_descriptors');
$sJoinSQL = "
INNER JOIN $sPermissionLookupsTable AS PL ON D.permission_lookup_id = PL.id
INNER JOIN $sPermissionLookupAssignmentsTable AS PLA ON PL.id = PLA.permission_lookup_id AND PLA.permission_id = ?
";
$aGroups = GroupUtil::listGroupsForUserExpand($oUser);
$aPermissionDescriptors = KTPermissionDescriptor::getByGroups($aGroups, array('ids' => true));
$sPermissionDescriptors = DBUtil::paramArray($aPermissionDescriptors);
$sSQLString = "PLA.permission_descriptor_id IN ($sPermissionDescriptors)";
$aParams = array($oPermission->getId());
$aParams = array_merge($aParams, $aPermissionDescriptors);
return array($sSQLString, $aParams, $sJoinSQL);
}
function criteriaToQuery($aCriteriaSet, $oUser, $sPermissionName) {
global $default;
list($sSQLSearchString, $aCritParams, $sJoinSQL) = KTSearchUtil::criteriaSetToSQL($aCriteriaSet);
$sToSearch = KTUtil::arrayGet($aOrigReq, 'fToSearch', 'Live'); // actually never present in this version.
list ($sPermissionString, $aPermissionParams, $sPermissionJoin) = KTSearchUtil::permissionToSQL($oUser, $sPermissionName);
//$sQuery = DBUtil::compactQuery("
$sQuery = ("
SELECT
F.name AS folder_name, F.id AS folder_id, D.id AS document_id,
D.name AS document_name, D.filename AS file_name, COUNT(D.id) AS doc_count, 'View' AS view
FROM
$default->documents_table AS D
INNER JOIN $default->folders_table AS F ON D.folder_id = F.id
$sJoinSQL
INNER JOIN $default->status_table AS SL on D.status_id=SL.id
$sPermissionJoin
WHERE
$sPermissionString
AND SL.name = ?
AND ($sSQLSearchString)
GROUP BY D.id
ORDER BY doc_count DESC");
$aParams = array();
$aParams = array_merge($aParams, $aPermissionParams);
$aParams[] = $sToSearch;
$aParams = array_merge($aParams, $aCritParams);
return array($sQuery, $aParams);
}
}