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: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 276: 277: 278: 279: 280: 281: 282: 283: 284: 285: 286: 287: 288: 289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 309: 310: 311: 312: 313: 314: 315: 316: 317: 318: 319: 320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346: 347: 348: 349: 350: 351: 352: 353: 354: 355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365: 366: 367: 368: 369: 370: 371: 372: 373: 374: 375: 376: 377: 378: 379: 380: 381: 382: 383: 384: 385: 386: 387: 388: 389: 390: 391: 392: 393: 394: 395: 396: 397: 398: 399: 400: 401: 402: 403: 404: 405: 406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 420: 421: 422: 423: 424: 425: 426: 427: 428: 429: 430: 431: 432: 433: 434: 435: 436: 437: 438: 439: 440: 441: 442: 443: 444: 445: 446: 447: 448: 449: 450: 451: 452: 453: 454: 455: 456: 457: 458: 459: 460: 461: 462: 463: 464: 465: 466: 467: 468: 469: 470: 471: 472: 473: 474: 475: 476: 477: 478: 479: 480: 481: 482: 483: 484: 485: 486: 487: 488: 489: 490: 491: 492: 493: 494: 495: 496: 497: 498: 499: 500: 501: 502: 503: 504: 505: 506: 507: 508: 509: 510: 511: 512: 513: 514: 515: 516: 517: 518: 519: 520: 

<?php

/**
 * The Gravity Forms Query Condition class.
 */
class GF_Query_Condition {
    /**
     * @var string The chosen operator.
     */
    private $_operator = null;

    /**
     * @var array Boolean combined expressions.
     */
    private $_expressions = array();

    /**
     * @var mixed The left-hand expression.
     */
    private $_left = null;

    /**
     * @var mixed The right-hand expression.
     */
    private $_right = null;

    /**
     * @const string The AND operator.
     */
    const _AND = 'AND';

    /**
     * @const string The AND operator.
     */
    const _OR = 'OR';

    /**
     * @const string The less than operator.
     */
    const LT = '<';

    /**
     * @const string The less than or equal to operator.
     */
    const LTE = '<=';

    /**
     * @const string The greater than operator.
     */
    const GT = '>';

    /**
     * @const string The greater than or equal to operator.
     */
    const GTE = '>=';

    /**
     * @const string The equal to operator.
     */
    const EQ = '=';

    /**
     * @const string The not equal to operator.
     */
    const NEQ = '!=';

    /**
     * @const string The IN operator.
     */
    const IN = 'IN';

    /**
     * @const string The NOT IN operator.
     */
    const NIN = 'NOT IN';

    /**
     * @const string The LIKE operator.
     */
    const LIKE = 'LIKE';

    /**
     * @const string The NOT LIKE operator.
     */
    const NLIKE = 'NOT LIKE';

    /**
     * @const string The BETWEEN operator.
     */
    const BETWEEN = 'BETWEEN';

    /**
     * @const string The NOT BETWEEN operator.
     */
    const NBETWEEN = 'NOT BETWEEN';

    /**
     * @const string The inverse IN operator.
     */
    const CONTAINS = 'CONTAINS';

    /**
     * @const string The inverse NIN operator.
     */
    const NCONTAINS = 'NCONTAINS';

    /**
     * A condition.
     *
     * @param GF_Query_Column|GF_Query_Call|GF_Query_Literal|null $left The left-hand expression.
     * @param string|null $operator The operator.
     * @param GF_Query_Column|GF_Query_Call|GF_Query_Literal|GF_Query_Series|null $right The right-hand expression.
     *
     * @return GF_Query_Condition $this This condition.
     */
    public function __construct( $left = null, $operator = null, $right = null ) {

        $allowed_ops = array( self::LT, self::LTE, self::GT, self::GTE, self::EQ, self::NEQ, self::IN, self::NIN, self::LIKE, self::NLIKE, self::BETWEEN, self::NBETWEEN, self::CONTAINS, self::NCONTAINS );

        /**
         * Left-hand expression, non Series.
         */
        if ( self::is_valid_expression_type( $left ) && ! $left instanceof GF_Query_Series ) {
            $this->_left = $left;
        }

        /**
         * The operator.
         */
        if ( in_array( $operator, $allowed_ops ) ) {
            $this->_operator = $operator;
        }

        /**
         * Right-hand expression, non Series.
         */
        if ( self::is_valid_expression_type( $right ) ) {
            $this->_right = $right;
        }
    }

    /**
     * Tie several conditions together with an AND relationship.
     *
     * Accepts any number of GF_Query_Condition objects.
     *
     * @return GF_Query_Condition The condition.
     */
    public static function _and() {
        $conditions = array();

        foreach ( func_get_args() as $arg ) {
            if ( $arg instanceof GF_Query_Condition ) {
                $conditions[] = $arg;
            }
        }

        $_this = new self();
        $_this->_operator = self::_AND;
        $_this->_expressions = $conditions;
        return $_this;
    }

    /**
     * Tie several conditions together with an OR relationship.
     *
     * Accepts any number of GF_Query_Condition objects.
     *
     * @return GF_Query_Condition The condition.
     */
    public static function _or() {

        $conditions = array();

        foreach ( func_get_args() as $arg ) {
            if ( $arg instanceof GF_Query_Condition ) {
                $conditions[] = $arg;
            }
        }

        $_this = new self();
        $_this->_operator = self::_OR;
        $_this->_expressions = $conditions;
        return $_this;
    }

    /**
     * Compile the expressions into a SQL string.
     *
     * @param GF_Query $query The query.
     *
     * @return string The SQL string.
     */
    public function sql( $query ) {
        global $wpdb;

        /**
         * Both expressions are given.
         */
        if ( $this->operator && $this->left && $this->right ) {

            /**
             * Meta field.
             */
            if ( $this->left instanceof GF_Query_Column && ( ! $this->left->is_entry_column() && ! $this->left->is_meta_column() )  && $this->left->field_id ) {
                /**
                 * A meta field needs some extra conditions: "meta_key", "meta_value", and sometimes EXISTS.
                 */
                $alias = $query->_alias( $this->left->field_id, $this->left->source, 'm' );

                if ( $this->left->field_id == GF_Query_Column::META ) {

                    // Global meta search doesn't require a meta_key clause.
                    $compare_condition = new self(
                        new GF_Query_Column( 'meta_value', $this->left->source, $alias ),
                        $this->operator,
                        $this->right
                    );

                    return $query->_where_unwrap( $compare_condition );
                }

                /**
                 * Multi-input fields are processed in a more complex way.
                 *
                 * If a non-specific input is requested (radio, checkboxes, usually)
                 *  we have to include all the input ids in the query.
                 */
                if ( is_numeric( $this->left->field_id ) && intval( $this->left->field_id ) == $this->left->field_id ) {
                    if ( ( $field = GFFormsModel::get_field( GFAPI::get_form( $this->left->source ), $this->left->field_id ) ) && $field->get_entry_inputs() ) {

                        /**
                         * EQ and NEQ require an unordered comparison of all the values for the entry.
                         */
                        if ( in_array( $this->operator, array( self::EQ, self::NEQ ) ) && $this->right instanceof GF_Query_Series ) {
                            $compare_conditions = array();
                            foreach ( $this->right->values as $literal ) {
                                $subquery = $wpdb->prepare( sprintf( "SELECT 1 FROM `%s` WHERE `meta_key` LIKE %%s AND `meta_value` = %s AND `entry_id` = `%s`.`id`",
                                    GFFormsModel::get_entry_meta_table_name(), $literal->sql( $query ), $query->_alias( null, $this->left->source ) ),
                                    sprintf( '%d.%%', $this->left->field_id ) );
                                $compare_condition = new self( new GF_Query_Call( sprintf( '%sEXISTS', $this->operator == self::NEQ ? 'NOT ' : '' ), array( $subquery ) ) );
                                $compare_conditions []= $compare_condition->sql( $query );
                            }

                            $subquery = $wpdb->prepare( sprintf( "SELECT COUNT(1) FROM `%s` WHERE `meta_key` LIKE %%s AND `entry_id` = `%s`.`id`",
                                GFFormsModel::get_entry_meta_table_name(), $query->_alias( null, $this->left->source ) ),
                                sprintf( '%d.%%', $this->left->field_id ) );

                            /**
                             * Add length comparison to make sure all the needed values are found
                             *  and no extra ones exist.
                             */
                            $compare_conditions []= sprintf( "(%s) %s %d", $subquery, $this->operator == self::NEQ ? '!=' : '=', count( $compare_conditions ) );
                            return sprintf( "(%s)", implode( $this->operator == self::NEQ ? ' OR ' : ' AND ', $compare_conditions ) );

                            /**
                             * Inverse contains.
                             */
                        } elseif ( in_array( $this->operator, array( self::CONTAINS, self::NCONTAINS ) ) ) {
                            $subquery = $wpdb->prepare( sprintf( "SELECT 1 FROM `%s` WHERE `meta_key` LIKE %%s AND `meta_value` = %s AND `entry_id` = `%s`.`id`",
                                GFFormsModel::get_entry_meta_table_name(), $this->right->sql( $query ), $query->_alias( null, $this->left->source ) ),
                                sprintf( '%d.%%', $this->left->field_id ) );
                            $compare_condition = new self( new GF_Query_Call( sprintf( '%sEXISTS', $this->operator == self::NCONTAINS ? 'NOT ' : '' ), array( $subquery ) ) );
                            return $compare_condition->sql( $query );

                            /**
                             * One of.
                             */
                        } elseif ( in_array( $this->operator, array( self::IN, self::NIN ) ) && $this->right instanceof GF_Query_Series ) {
                            $subquery = $wpdb->prepare( sprintf( "SELECT 1 FROM `%s` WHERE `meta_key` LIKE %%s AND `meta_value` IN (%s) AND `entry_id` = `%s`.`id`",
                                GFFormsModel::get_entry_meta_table_name(), str_replace( '%', '%%', $this->right->sql( $query, ', ' ) ), $query->_alias( null, $this->left->source ) ),
                                sprintf( '%d.%%', $this->left->field_id ) );
                            $compare_condition = new self( new GF_Query_Call( sprintf( '%sEXISTS', $this->operator == self::NIN ? 'NOT ' : '' ), array( $subquery ) ) );
                            return $compare_condition->sql( $query );

                            /**
                             * Everything else.
                             */
                        } else {
                            $operator = $this->operator;
                            $is_negative = in_array( $operator, array( self::NLIKE, self::NBETWEEN, self::NEQ ) );
                            if ( $is_negative ) {
                                /**
                                 * Convert operator to positive, since we're doing it the NOT EXISTS way.
                                 */
                                switch ( $operator ) {
                                    case self::NLIKE:
                                        $operator = self::LIKE;
                                        break;
                                    case self::NBETWEEN:
                                        $operator = self::BETWEEN;
                                        break;
                                    case self::NEQ:
                                        $operator = self::EQ;
                                        break;
                                }
                            }

                            $subquery = $wpdb->prepare( sprintf( "SELECT 1 FROM `%s` WHERE `meta_key` LIKE %%s AND `meta_value` %s %s AND `entry_id` = `%s`.`id`",
                                GFFormsModel::get_entry_meta_table_name(), $operator, str_replace( '%', '%%', $this->right->sql( $query ) ), $query->_alias( null, $this->left->source ) ),
                                sprintf( '%d.%%', $this->left->field_id ) );
                            $compare_condition = new self( new GF_Query_Call( sprintf( '%sEXISTS', $is_negative ? 'NOT ' : '' ), array( $subquery ) ) );
                            return $compare_condition->sql( $query );
                        }
                    }
                }

                $compare_condition = self::_and(
                    new self(
                        new GF_Query_Column( 'meta_key', $this->left->source, $alias ),
                        self::EQ,
                        new GF_Query_Literal( $this->left->field_id )
                    ),
                    new self(
                        new GF_Query_Column( 'meta_value', $this->left->source, $alias ),
                        $this->operator,
                        $this->right
                    )
                );

                if ( ( in_array( $this->operator, array( self::NIN, self::NBETWEEN, self::NEQ ) ) && ! empty( $this->right ) )
                     || ( $this->operator == self::EQ && $this->right->value == '' )
                ) {
                    /**
                     * Empty string comparisons and negative comparisons need a NOT EXISTS clause to grab entries that
                     *  don't have the value set in the first place.
                     */
                    $subquery = $wpdb->prepare( sprintf( "SELECT 1 FROM `%s` WHERE `meta_key` = %%s AND `entry_id` = `%s`.`id`",
                        GFFormsModel::get_entry_meta_table_name(), $query->_alias( null, $this->left->source ) ), $this->left->field_id );
                    $not_exists = new self( new GF_Query_Call( 'NOT EXISTS', array( $subquery ) ) );
                    return $query->_where_unwrap( self::_or( $not_exists, $compare_condition ) );
                }

                return $query->_where_unwrap( $compare_condition );
            }

            if ( ( $left = $this->left_sql( $query ) ) && ( $right = $this->right_sql( $query ) ) ) {
                if ( in_array( $this->operator, array( self::NBETWEEN, self::BETWEEN ) ) ) {
                    return "($left {$this->operator} $right)";
                }

                if ( $this->left instanceof GF_Query_Column && $this->left->is_nullable_entry_column() ) {
                    if ( ( $this->operator == self::EQ && empty ( $this->right->value ) ) || ( $this->operator == self::NEQ && ! empty ( $this->right->value ) ) ) {
                        $right .= ' OR ' . $left . ' IS NULL';
                    }
                }

                return "$left {$this->operator} $right";
            }
        }

        if ( $this->left && ( $this->left instanceof GF_Query_Call || $this->left instanceof GF_Query_Column ) ) {
            return $this->left_sql( $query );
        }

        return '';
    }

    /**
     * Checks whether the expression is of a valid type.
     *
     * @param mixed $expression The expression to check.
     *
     * @return boolean Valid or not.
     */
    public static function is_valid_expression_type( $expression ) {
        return (
            ( $expression instanceof GF_Query_Literal ) ||
            ( $expression instanceof GF_Query_Column ) ||
            ( $expression instanceof GF_Query_Series ) ||
            ( $expression instanceof GF_Query_Call )
        );
    }

    /**
     * The right expression.
     *
     * @return string The SQL string or null for the right expression.
     */
    private function right_sql( $query ) {
        /**
         * (NOT) IN
         *
         * Only works with literal arrays, which can be made
         * up of a Literal, Column or Call.
         */
        if ( in_array( $this->operator, array( self::IN, self::NIN ) ) ) {
            if ( ! $this->right instanceof GF_Query_Series ) {
                return '';
            }

            return sprintf( '(%s)', $this->right->sql( $query, ', ' ) );

            /**
             * BETWEEN
             */
        } elseif ( in_array( $this->operator, array( self::BETWEEN, self::NBETWEEN ) ) ) {
            if ( ! $this->right instanceof GF_Query_Series ) {
                return '';
            }

            return $this->right->sql( $query, ' AND ' );
        }

        return $this->right->sql( $query );
    }

    /**
     * The left expression.
     *
     * @return string The SQL string or null for the left expression.
     */
    private function left_sql( $query ) {
        if ( $this->left instanceof GF_Query_Call ) {

            $columns = array();

            foreach ( $this->left->parameters as $c ) {
                if ( $c instanceof GF_Query_Column ) {
                    $columns[] = $c;
                }
            }

            /**
             * Add a meta_key condition to a calls.
             */
            if ( $columns ) {
                $meta_key_conditions = array();
                foreach ( $columns as $column ) {
                    $alias = $column->alias ? $column->alias : $query->_alias( $column->field_id, $column->source, 'm' );
                    $condition = new GF_Query_Condition(
                        new GF_Query_Column( 'meta_key', $column->source, $alias ),
                        GF_Query_Condition::EQ,
                        new GF_Query_Literal( $column->field_id )
                    );

                    $meta_key_conditions []= $condition->sql( $query );
                }

                return implode( ' AND ',
                    array_merge(
                        $meta_key_conditions,
                        array( $this->left->sql( $query ) )
                    )
                );
            }
        }
        return $this->left->sql( $query );
    }

    /**
     * Retrieve all the columns present in the left, right clauses.
     *
     * @return array The columns.
     */
    public function get_columns() {
        $columns = array();

        if ( $this->left instanceof GF_Query_Column ) {
            $columns[] = $this->left;
        }

        if ( $this->right instanceof GF_Query_Column ) {
            $columns[] = $this->right;
        }

        /**
         * Support Calls
         */
        if ( $this->left instanceof GF_Query_Call) {

            $left_columns = array();

            foreach ( $this->left->parameters as $c ) {
                if ( $c instanceof GF_Query_Column ) {
                    $left_columns[] = $c;
                }
            }

            $columns = array_merge( $columns, $left_columns );
        }

        /**
         * Support series of columns.
         */
        if ( $this->right instanceof GF_Query_Series ) {

            $right_columns = array();

            foreach ( $this->right->values as $c ) {
                if ( $c instanceof GF_Query_Column ) {
                    $right_columns[] = $c;
                }
            }

            $columns = array_merge( $columns, $right_columns );
        }

        return $columns;
    }

    /**
     * Proxy read-only values.
     */
    public function __get( $key ) {
        switch ( $key ) :
            case 'operator':
                return $this->_operator;
            case 'expressions':
                return $this->_expressions;
            case 'left':
                return $this->_left;
            case 'right':
                return $this->_right;
            case 'columns':
                return $this->get_columns();
        endswitch;
    }
}