Пример #1
0
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)
}
Пример #2
0
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);
}