      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:

    <?php

    namespace WPGMZA\MarkerListing;

    class AdvancedTable extends \WPGMZA\MarkerDataTable
    {
        public function __construct($map_id=null)
        {
            global $wpgmza;

            \WPGMZA\MarkerDataTable::__construct();

            // Temporary workaround for map ID not passed through datatables endpoint
            if($map_id == null && isset($_REQUEST['wpgmzaDataTableRequestData']))
                $map_id = (int)$_REQUEST['wpgmzaDataTableRequestData']['map_id'];

            $this->map_id = $map_id;
            $this->map = \WPGMZA\Map::createInstance($map_id);

            $table = $this->element->querySelector("table");
            $table->addClass('responsive');
            $table->addClass('wpgmza_table');

            if($wpgmza->settings->useLegacyHTML)
            {
                $this->element->setAttribute("id", "wpgmza_marker_holder_$map_id");
                $this->element->addClass("wpgmza_marker_holder");
                $this->element->setInlineStyle("width", "100%");

                $table->setAttribute("id", "wpgmza_table_$map_id");
                $table->removeClass("display");
                $table->setAttribute("cellspacing", "0");
                $table->setAttribute("cellpadding", "0");
                $table->setInlineStyle("width", "100%");

                foreach($table->querySelectorAll("thead>[data-wpgmza-column-name]") as $th)
                {
                    $name = $th->getAttribute("data-wpgmza-column-name");

                    switch($name)
                    {
                        case "icon":
                            $name = "marker";
                            break;

                        case "title":
                            $th->addClass("all");
                            break;
                    }

                    $th->addClass("wpgmza_table_$name");
                }

                if($tfoot = $table->querySelector('tfoot'))
                    $tfoot->remove();

                //$tbody = $this->element->ownerDocument->createElement('tbody');
                //$table->appendChild($tbody);
            }
        }

        protected function getColumns()
        {
            global $wpdb;
            global $wpgmza;

            $settings = $wpgmza->settings;
            $result = array();

            if(empty($settings->wpgmza_settings_markerlist_icon))
                $result['icon'] = '';

            if(empty($settings->wpgmza_settings_markerlist_title))
                $result['title'] = __('Title', 'wp-google-maps');

            if(empty($settings->wpgmza_settings_markerlist_category))
                $result['category'] = __('Category', 'wp-google-maps');

            if(empty($settings->wpgmza_settings_markerlist_address))
                $result['address'] = __('Address', 'wp-google-maps');

            if(empty($settings->wpgmza_settings_markerlist_description))
                $result['description'] = __('Description', 'wp-google-maps');

            if(empty($settings->wpgmza_settings_markerlist_link))
                $result['link'] = __('Link', 'wp-google-maps');

            // TODO: It might be more efficient to put this in a class property in the constructor
            $customFields = new \WPGMZA\CustomFields();

            foreach($customFields as $field)
                $result["custom_field_" . $field->id] = __($field->name, 'wp-google-maps');

            return $result;
        }

        protected function getCustomFieldColumnNames()
        {
            $result = array();

            // TODO: It might be more efficient to put this in a class property in the constructor
            $customFields = new \WPGMZA\CustomFields();

            foreach($customFields as $field)
                $result[] = "custom_field_" . $field->id;

            return $result;
        }

        /**
         * Exclude custom_field_* fields from search clause - they need to be in HAVING, not WHERE
         * @return array
         */
        protected function getSearchClause($input_params, &$query_params, $exclude_columns=null)
        {
            if(!$exclude_columns)
                $exclude_columns = array();

            if(isset($input_params['overrideMarkerIDs']) || isset($input_params['markerIDs']))
                return "";

            if(empty($input_params['search']['value']))
                return "";

            $exclude_columns = array_merge($exclude_columns, $this->getCustomFieldColumnNames());

            $sql = \WPGMZA\MarkerDataTable::getSearchClause($input_params, $query_params, $exclude_columns);

            if(empty($sql))
                return $sql;

            // TODO: This will be implemented as a trait in the future, when we drop support for PHP 5.3
            $sql = \WPGMZA\ProAdminMarkerDataTable::appendCategoryAndCustomFieldSearchClauses($sql, $input_params, $query_params);

            return $sql;
        }

        // TODO: This is duplicated from admin marker table. Use a trait when PHP 5.4 is the minimum version
        protected function filterColumns(&$columns, $input_params)
        {
            global $wpgmza;
            global $WPGMZA_TABLE_NAME_MARKERS;
            global $WPGMZA_TABLE_NAME_CATEGORIES;
            global $WPGMZA_TABLE_NAME_MARKERS_HAS_CATEGORIES;
            global $WPGMZA_TABLE_NAME_MARKERS_HAS_CUSTOM_FIELDS;

            foreach($columns as $key => $value)
            {
                $name = $this->getColumnNameByIndex($key);

                if(preg_match('/^custom_field_(\d+)$/', $name, $m))
                {
                    $field_id = (int)$m[1];

                    $columns[$key] = "(
                        SELECT value
                        FROM $WPGMZA_TABLE_NAME_MARKERS_HAS_CUSTOM_FIELDS
                        WHERE field_id=$field_id
                        AND object_id=$WPGMZA_TABLE_NAME_MARKERS.id
                    ) AS $name";
                }
                else
                    switch($name)
                    {
                        case 'icon':

                            $columns[$key] = \WPGMZA\ProMarker::getIconSQL($this->map_id, true);

                            break;

                        case 'category':

                            $columns[$key] = "(
                                SELECT GROUP_CONCAT(category_name SEPARATOR ', ')
                                FROM $WPGMZA_TABLE_NAME_CATEGORIES
                                WHERE $WPGMZA_TABLE_NAME_CATEGORIES.id IN (
                                    SELECT category_id
                                    FROM $WPGMZA_TABLE_NAME_MARKERS_HAS_CATEGORIES
                                    WHERE marker_id = $WPGMZA_TABLE_NAME_MARKERS.id
                                )
                            ) AS category";

                            break;

                        case 'link':

                            $text = __('More Details', 'wp-google-maps');

                            if(!empty($wpgmza->settings->wpgmza_settings_infowindow_link_text))
                                $text = __($wpgmza->settings->wpgmza_settings_infowindow_link_text, 'wp-google-maps');

                            $text = esc_sql($text);

                            $columns[$key] = "(
                                CASE WHEN LENGTH(link) > 0
                                THEN CONCAT('<a target=\"_BLANK\" href=\"', REPLACE(link, '\"', '&quot;'), '\">$text</a>')
                                ELSE ''
                                END
                            )
                            AS link";

                            break;
                    }
            }

            $columns[] = 'id';

            if($this->map->order_markers_by == \WPGMZA\MarkerListing::ORDER_BY_DISTANCE && !empty($input_params['filteringParams']['center']))
            {
                $lat = floatval($input_params['filteringParams']['center']['lat']) / 180 * 3.1415926;
                $lng = floatval($input_params['filteringParams']['center']['lng']) / 180 * 3.1415926;

                $columns['distance'] = "
                    (
                        6381 *

                        2 *

                        ATAN2(
                            SQRT(
                                POW( SIN( ( (X(latlng) / 180 * 3.1415926) - $lat ) / 2 ), 2 ) +
                                COS( X(latlng) / 180 * 3.1415926 ) * COS( $lat ) *
                                POW( SIN( ( (Y(latlng) / 180 * 3.1415926) - $lng ) / 2 ), 2 )
                            ),

                            SQRT(1 -
                                (
                                    POW( SIN( ( (X(latlng) / 180 * 3.1415926) - $lat ) / 2 ), 2 ) +
                                    COS( X(latlng) / 180 * 3.1415926 ) * COS( $lat ) *
                                    POW( SIN( ( (Y(latlng) / 180 * 3.1415926) - $lng ) / 2 ), 2 )
                                )
                            )
                        )
                    ) AS distance
                ";
            }

            return $columns;
        }

        protected function getHavingClause($input_params, &$query_params, $exclude_columns=null)
        {
            global $wpdb;

            // TODO: Can't filter on custom fields at the moment - we'd need to intersect I believe. Otherwise both WHERE and HAVING are applied with AND logic
            return '';

            if(empty($input_params['search']['value']))
                return "";

            $columns = array_keys($this->getColumns());
            $customFieldColumnNames = $this->getCustomFieldColumnNames();

            $exclude = array_diff($columns, $customFieldColumnNames);

            if(empty($exclude))
                return "";

            return \WPGMZA\MarkerDataTable::getSearchClause($input_params, $query_params, $exclude);
        }

        protected function getWhereClause($input_params, &$query_params, $clause_for_total=false)
        {
            $clauses = \WPGMZA\MarkerDataTable::getWhereClause($input_params, $query_params, $clause_for_total);

            if(!empty($input_params['markerIDs']))
            {
                $markerIDs = explode(',', $input_params['markerIDs']);
                $count = count($markerIDs);
                $placeholders = implode(',', array_fill(0, $count, '%d'));

                $clauses .= " AND id IN ($placeholders)";

                foreach($markerIDs as $id)
                    array_push($query_params, $id);
            }
            else if(isset($input_params['markerIDs']))
                return '0';

            return $clauses;
        }

        protected function getOrderBy($input_params, $column_keys)
        {
            global $WPGMZA_TABLE_NAME_MARKERS;
            global $WPGMZA_TABLE_NAME_CATEGORIES;
            global $WPGMZA_TABLE_NAME_MARKERS_HAS_CATEGORIES;

            if(!isset($input_params['overrideListingOrderSettings']) || $input_params['overrideListingOrderSettings'] == 'false')
            {
                // Use marker listing setting on initial draw
                switch($this->map->order_markers_by)
                {
                    case \WPGMZA\MarkerListing::ORDER_BY_TITLE:
                        return "title";
                        break;

                    case \WPGMZA\MarkerListing::ORDER_BY_ADDRESS:
                        return "address";
                        break;

                    case \WPGMZA\MarkerListing::ORDER_BY_DESCRIPTION:
                        return "description";
                        break;

                    case \WPGMZA\MarkerListing::ORDER_BY_CATEGORY:
                        return "category";
                        break;

                    case \WPGMZA\MarkerListing::ORDER_BY_CATEGORY_PRIORITY:
                        return "(
                            SELECT MAX(priority) FROM $WPGMZA_TABLE_NAME_CATEGORIES
                            WHERE $WPGMZA_TABLE_NAME_CATEGORIES.id IN (
                                SELECT category_id FROM $WPGMZA_TABLE_NAME_MARKERS_HAS_CATEGORIES
                                WHERE marker_id = $WPGMZA_TABLE_NAME_MARKERS.id
                            )
                        )";
                        break;

                    case \WPGMZA\MarkerListing::ORDER_BY_DISTANCE:
                        if($this->map->order_markers_by == \WPGMZA\MarkerListing::ORDER_BY_DISTANCE && !empty($input_params['filteringParams']['center']))
                            return "distance";

                    default:
                        return "$WPGMZA_TABLE_NAME_MARKERS.id";
                        break;
                }
            }

            return \WPGMZA\MarkerDataTable::getOrderBy($input_params, $column_keys);
        }

        protected function getOrderDirection($input_params)
        {
            if(!isset($input_params['overrideListingOrderSettings']) || $input_params['overrideListingOrderSettings'] == 'false')
            {
                // Use marker listing setting on initial draw
                $dir = (empty($this->map->order_markers_choice) ? MarkerListing::ORDER_DESC : $this->map->order_markers_choice);

                return ($dir == \WPGMZA\MarkerListing::ORDER_DESC ? 'DESC' : 'ASC');
            }

            return \WPGMZA\MarkerDataTable::getOrderDirection($input_params);
        }

        public function getRecords($input_params)
        {
            $records = \WPGMZA\MarkerDataTable::getRecords($input_params);

            for($index = 0; $index < count($records->data); $index++)
            {
                $last = count($records->data[$index]) - 1;

                $records->meta[$index]->id = $records->data[$index][$last];
                unset($records->data[$index][$last]);
            }

            return $records;
        }
    }