_mocks.timesort.php

Go to the documentation of this file.

<?php
namespace GV\Mocks;

/**
 * Time merge calls.
 *
 * Might be in GF_Query soon.
 */
class GF_Query_Call_TIMESORT extends \GF_Query_Call {
    public function timesort_sql( $query ) {
        global $wpdb;

        list( $column, $sql ) = $this->parameters;
        $meta_table = \GFFormsModel::get_entry_meta_table_name();

        $alias = $query->_alias( $column->field_id, $column->source, 'm' );

/*
SELECT v,
IF(
POSITION('pm' IN v) > 0,

(
SUBSTRING_INDEX(v, ':', 1)
+ IF(SUBSTRING_INDEX(v, ':', 1) < 12, 12, 0)
) * 60,

SUBSTRING_INDEX(v, ':', 1) * 60
) +
RIGHT(IF(
POSITION('m' IN v) > 0,
SUBSTRING_INDEX(v, ' ', 1),
v
),2) t1

FROM meta;
*/

        // Detect if 'pm' is in the time field
        $pm_exists = "POSITION('pm' IN $alias.`meta_value`)";
        
        // Transform a pm time into minutes ((hour + (12 if hour > 12 else 0)) * 60)
        $minutes_12 = "(SUBSTRING_INDEX($alias.`meta_value`, ':', 1) + IF(SUBSTRING_INDEX($alias.`meta_value`, ':', 1) < 12, 12, 0)) * 60";

        // Transform a 24-hour time into minutes (hour * 60), maybe compensate 12 am = 0
        $minutes_24 = "(SUBSTRING_INDEX($alias.`meta_value`, ':', 1) - IF(POSITION('am' IN $alias.`meta_value`) AND SUBSTRING_INDEX($alias.`meta_value`, ':', 1) = '12', 12, 0)) * 60";

        // Minutes
        $minutes = "RIGHT(IF(POSITION('m' IN $alias.`meta_value`), SUBSTRING_INDEX($alias.`meta_value`, ' ', 1), $alias.`meta_value`), 2)";

        // Combine the insanity :)
        $condition = "IF($pm_exists, $minutes_12, $minutes_24) + $minutes";

        return "(SELECT $condition)";
    }
}