function outputSearchColumns($fp, $table, $indent = '')
{
foreach ($table->columns as $column) {
$cn = $column->name;
$canShow = true;
switch ($column->type) {
case 'integer':
case 'smallint':
case 'bigint':
case 'decimal':
$displayType = 'numeric';
$columnCSSClass = 'right';
break;
case 'char':
case 'varchar':
case 'text':
case 'time':
$displayType = 'string';
$columnCSSClass = 'left';
break;
case 'date':
case 'datetime':
$displayType = 'date';
$columnCSSClass = 'left';
break;
default:
$canShow = false;
}
if (!$canShow) {
continue;
}
fprintf($fp, <<<EOF
%s%s: { heading: %s, displayType: %s, columnCSSClass: %s, sortable: Yes }
EOF
, $indent, $cn, identifierToHumanReadable($cn), $displayType, $columnCSSClass);
}
// foreach ($table->columns as $column)
}
function getWhereClauseParams($table, $searchName, $search, $searchType, &$langKeys)
{
global $ALLOWED_NUMERIC_QUERY_OPERATORS, $ALLOWED_STRING_QUERY_OPERATORS;
$haveAnyFulltextQueryOperators = false;
$searchWhereClausePHP = '';
$searchWhereClauseOr = '';
$searchWhereClauseIndent = $searchType == 'autocomplete' ? "\n\t\t\t" : "\n\t\t";
$searchWhereClauseConcat = '';
$searchWhereAssignments = array();
$searchWhereAssignmentsIndent = $searchType == 'autocomplete' ? "\t\t" : "\t";
$searchableColumnsPHPArray = "array(\n";
$searchableColumnsPHPArraySep = '';
if (isset($search['searchableColumns'])) {
foreach ($search['searchableColumns'] as $key => $column) {
$queryOperator = isset($column['queryOperator']) ? $column['queryOperator'] : '';
if ($queryOperator == 'fulltext') {
$haveAnyFulltextQueryOperators = true;
break;
}
}
foreach ($search['searchableColumns'] as $key => $column) {
$colName = isset($column['columnName']) ? $column['columnName'] : $key;
$pfx = isset($column['tableAlias']) ? $column['tableAlias'] : 'pri';
if ($pfx != '') {
$pfx = $pfx .= '.';
}
$title = isset($column['title']) ? $column['title'] : '';
if ($title == '') {
$title = identifierToHumanReadable($colName);
}
$sqlType = isset($column['sqlType']) ? $column['sqlType'] : '';
$queryOperator = isset($column['queryOperator']) ? $column['queryOperator'] : '';
switch ($sqlType) {
case 'integer':
case 'smallint':
case 'bigint':
case 'decimal':
if (!in_array($queryOperator, $ALLOWED_NUMERIC_QUERY_OPERATORS)) {
$queryOperator = '=';
}
if (isset($column['unsignedSearch']) && $column['unsignedSearch']) {
if ($searchType == 'search') {
$searchWhereClausePHP .= $searchWhereClauseIndent . $searchWhereClauseConcat . var_export_normal_precision($searchWhereClauseOr . '(? <> 0 and abs(' . $pfx . $colName . ') ' . $queryOperator . ' ?)', true);
if ($searchWhereClauseOr == '') {
$searchWhereClauseOr = ' or ';
}
if ($searchWhereClauseConcat == '') {
$searchWhereClauseConcat = '.';
}
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setInt(((\$queryCol == '') || (\$queryCol == " . var_export_normal_precision($pfx . $colName, true) . ")) ? 1 : 0);";
} else {
$searchWhereClausePHP .= $searchWhereClauseIndent . $searchWhereClauseConcat . var_export_normal_precision($searchWhereClauseOr . 'abs(' . $pfx . $colName . ') ' . $queryOperator . ' ?', true);
if ($searchWhereClauseOr == '') {
$searchWhereClauseOr = ' or ';
}
if ($searchWhereClauseConcat == '') {
$searchWhereClauseConcat = '.';
}
}
if ($sqlType == 'decimal') {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setDouble(abs((double)trim(\$query)));";
} else {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setInt(abs((int)trim(\$query)));";
}
} else {
if ($searchType == 'search') {
$searchWhereClausePHP .= $searchWhereClauseIndent . $searchWhereClauseConcat . var_export_normal_precision($searchWhereClauseOr . '(? <> 0 and ' . $pfx . $colName . ' ' . $queryOperator . ' ?)', true);
if ($searchWhereClauseOr == '') {
$searchWhereClauseOr = ' or ';
}
if ($searchWhereClauseConcat == '') {
$searchWhereClauseConcat = '.';
}
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setInt(((\$queryCol == '') || (\$queryCol == " . var_export_normal_precision($pfx . $colName, true) . ")) ? 1 : 0);";
} else {
$searchWhereClausePHP .= $searchWhereClauseIndent . $searchWhereClauseConcat . var_export_normal_precision($searchWhereClauseOr . $pfx . $colName . ' ' . $queryOperator . ' ?', true);
if ($searchWhereClauseOr == '') {
$searchWhereClauseOr = ' or ';
}
if ($searchWhereClauseConcat == '') {
$searchWhereClauseConcat = '.';
}
}
if ($sqlType == 'decimal') {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setDouble(\$query);";
} else {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setInt(\$query);";
}
}
$langKey = $searchName . '_search.searchableColumn.' . $colName . '.title';
if (is_array($langKeys)) {
$langKeys[$langKey] = $title;
}
$searchableColumnsPHPArray .= sprintf(<<<EOF
%s\t\t(object)array(
\t\t\t'name'=>%s,
\t\t\t'pfx'=>%s,
\t\t\t'title'=>_t(%s, %s),
\t\t\t'sqlType'=>%s,
\t\t\t'queryOperator'=>%s,
\t\t),
EOF
, $searchableColumnsPHPArraySep, var_export_normal_precision($colName, true), var_export_normal_precision($pfx, true), var_export_normal_precision($langKey, true), var_export_normal_precision($title, true), var_export_normal_precision($sqlType, true), var_export_normal_precision($queryOperator, true));
if ($searchableColumnsPHPArraySep == '') {
$searchableColumnsPHPArraySep = "\n";
}
break;
case 'char':
case 'varchar':
case 'text':
if (!in_array($queryOperator, $ALLOWED_STRING_QUERY_OPERATORS)) {
$queryOperator = '=';
}
if ($queryOperator == 'beginsWith' || $queryOperator == 'contains' || $queryOperator == 'endsWith' || $queryOperator == 'fulltext') {
if ($searchType == 'search') {
$tmppfx = '(? <> 0 and (';
$tmpsfx = '.\'))\'';
} else {
$tmppfx = $tmpsfx = '';
}
$searchWhereClausePHP .= $searchWhereClauseIndent . $searchWhereClauseConcat . ($searchWhereClauseOr != '' || $tmppfx != '' ? var_export_normal_precision($searchWhereClauseOr . $tmppfx, true) . '.' : '');
if ($queryOperator == 'fulltext') {
$searchWhereClausePHP .= '($canDoFulltextSearch ? (' . var_export_normal_precision('? = \'\' or match(' . $pfx . $colName . ') against (? in boolean mode)', true) . ') : (';
}
$searchWhereClausePHP .= '($db->hasCaseInsensitiveLike ? ' . var_export_normal_precision($pfx . $colName, true) . ' : ' . var_export_normal_precision('lower(' . $pfx . $colName . ')', true) . ')' . '.\' \'.$db->likeOperator.\' \'.' . '($db->hasCaseInsensitiveLike ? \'?\' : \'lower(?)\')';
if ($queryOperator == 'fulltext') {
$searchWhereClausePHP .= '))';
}
$searchWhereClausePHP .= $tmpsfx;
if ($searchWhereClauseOr == '') {
$searchWhereClauseOr = ' or ';
}
if ($searchWhereClauseConcat == '') {
$searchWhereClauseConcat = '.';
}
} else {
$searchWhereClausePHP .= $searchWhereClauseOr . '(? <> 0 and ' . $pfx . $colName . ' ' . $queryOperator . ' ?)';
if ($searchWhereClauseOr == '') {
$searchWhereClauseOr = ' or ';
}
if ($searchWhereClauseConcat == '') {
$searchWhereClauseConcat = '.';
}
}
if ($searchType == 'search') {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setInt(((\$queryCol == '') || (\$queryCol == " . var_export_normal_precision($pfx . $colName, true) . ")) ? 1 : 0);";
}
if ($queryOperator == 'beginsWith') {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setString(\$query.'%');";
} else {
if ($queryOperator == 'contains') {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setString('%'.\$query.'%');";
} else {
if ($queryOperator == 'endsWith') {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setString('%'.\$query);";
} else {
if ($queryOperator == 'fulltext') {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "if (\$canDoFulltextSearch) { \$ps->setString(\$ftquery); \$ps->setString(\$ftquery); } else { \$ps->setString('%'.\$query.'%'); }";
} else {
$searchWhereAssignments[] = $searchWhereAssignmentsIndent . "\$ps->setString(\$query);";
}
}
}
}
$langKey = $searchName . '_search.searchableColumn.' . $colName . '.title';
if (is_array($langKeys)) {
$langKeys[$langKey] = $title;
}
$searchableColumnsPHPArray .= sprintf(<<<EOF
%s\t\t(object)array(
\t\t\t'name'=>%s,
\t\t\t'pfx'=>%s,
\t\t\t'title'=>_t(%s, %s),
\t\t\t'sqlType'=>%s,
\t\t\t'queryOperator'=>%s,
\t\t),
EOF
, $searchableColumnsPHPArraySep, var_export_normal_precision($colName, true), var_export_normal_precision($pfx, true), var_export_normal_precision($langKey, true), var_export_normal_precision($title, true), var_export_normal_precision($sqlType, true), var_export_normal_precision($queryOperator, true));
if ($searchableColumnsPHPArraySep == '') {
$searchableColumnsPHPArraySep = "\n";
}
break;
default:
fprintf(STDERR, "WARNING: Invalid sqlType \"%s\" in searchable column \"%s\" in \"%s\" %s in %s table YAML file.\n", $sqlType, $colName, $searchName, $searchType, $table->tableName);
}
}
}
$searchableColumnsPHPArray .= "\n\t)";
$andWhere = '';
if (isset($search['andWhere'])) {
$andWhere = trim((string) $search['andWhere']);
}
$andWhereAssignments = array();
if (isset($search['andWhereAssignments']) && is_array($search['andWhereAssignments'])) {
foreach ($search['andWhereAssignments'] as $assignment) {
$expression = isset($assignment['expression']) && is_string($assignment['expression']) ? $assignment['expression'] : "''";
switch (isset($assignment['psType']) ? $assignment['psType'] : 'string') {
case 'boolean':
$andWhereAssignments[] = "\t\$ps->setBoolean({$expression});";
break;
case 'int':
$andWhereAssignments[] = "\t\$ps->setInt({$expression});";
break;
case 'float':
$andWhereAssignments[] = "\t\$ps->setFloat({$expression});";
break;
case 'double':
$andWhereAssignments[] = "\t\$ps->setDouble({$expression});";
break;
case 'string':
default:
$andWhereAssignments[] = "\t\$ps->setString({$expression});";
break;
case 'match':
$andWhereAssignments[] = "\t\$ps->setString('%'.{$expression}.'%');";
break;
case 'binary':
$andWhereAssignments[] = "\t\$ps->setBinary({$expression});";
break;
}
}
}
return array('haveAnyFulltextQueryOperators' => $haveAnyFulltextQueryOperators, 'searchWhereClausePHP' => $searchWhereClausePHP, 'searchWhereAssignments' => $searchWhereAssignments, 'searchableColumnsPHPArray' => $searchableColumnsPHPArray, 'searchableColumnsPHPArraySep' => $searchableColumnsPHPArraySep, 'andWhere' => $andWhere, 'andWhereAssignments' => $andWhereAssignments);
}
