<?php

if ( ! class_exists( 'WP_List_Table' ) ) {
	require_once ABSPATH . 'wp-admin/includes/class-wp-list-table.php';
}

class amazilonListTableClicks extends WP_List_Table {
	function __construct() {
		parent::__construct( array(
			'singular' => __( 'name' ),     //singular name of the listed records
			'plural'   => __( 'names' ),   //plural name of the listed records
			'ajax'     => false        //does this table support ajax?
		) );
		if ( isset( $_REQUEST['filter-reset'] ) ) {
			unset( $_REQUEST['amazilon-date-start'] );
			unset( $_REQUEST['amazilon-date-end'] );
		}
	}

	public function extra_tablenav( $which ) {
		if ( $which == 'top' ) {
			?>
            <script type="text/javascript">
                function amazilonSelectRange(r) {
                    if (r == 'today') {
                        jQuery('#amazilon-date-start,#amazilon-date-end').datepicker('setDate', '0');
                    } else if (r == 'yesterday') {
                        jQuery('#amazilon-date-start,#amazilon-date-end').datepicker('setDate', '-1');
                    } else if (r == '-7') {
                        jQuery('#amazilon-date-start').datepicker('setDate', '-7');
                        jQuery('#amazilon-date-end').datepicker('setDate', '0');
                    } else if (r == '-30') {
                        jQuery('#amazilon-date-start').datepicker('setDate', '-30');
                        jQuery('#amazilon-date-end').datepicker('setDate', '0');
                    } else if (r == '-90') {
                        jQuery('#amazilon-date-start').datepicker('setDate', '-90');
                        jQuery('#amazilon-date-end').datepicker('setDate', '0');
                    } else if (r == 'thismonth') {
                        jQuery('#amazilon-date-start').datepicker('setDate', '<?php echo date( 'Y-m-01' ); ?>');
                        jQuery('#amazilon-date-end').datepicker('setDate', '0');
                    }
                }
            </script>
            <div class="alignleft">
                <label for="amazilon-grouping"><?php _e( 'Group by', 'amazilon' ); ?>: </label>
                <select name="amazilon-grouping" id="amazilon-grouping">
                    <option value=""><?php _e( 'Standard', 'amazilon' ); ?></option>
                    <option value="asin"<?php selected( 'asin', $_REQUEST['amazilon-grouping'] ); ?>><?php _e( 'ASIN',
							'amazilon' ); ?></option>
                    <option value="sc"<?php selected( 'sc', $_REQUEST['amazilon-grouping'] ); ?>><?php _e( 'Shortcode',
							'amazilon' ); ?></option>
                    <option value="tpl"<?php selected( 'tpl', $_REQUEST['amazilon-grouping'] ); ?>><?php _e( 'Template',
							'amazilon' ); ?></option>
                </select>
                <label for="amazilon-daterange"><?php _e( 'Daterange', 'amazilon' ); ?>: </label>
                <select name="amazilon-daterange" id="amazilon-daterange" onchange="amazilonSelectRange(this.value);">
                    <option value=""><?php _e( '- everything -', 'amazilon' ); ?></option>
                    <option value="today"><?php _e( 'Today', 'amazilon' ); ?></option>
                    <option value="yesterday"><?php _e( 'Yesterday', 'amazilon' ); ?></option>
                    <option value="-7"><?php echo sprintf( __( 'last %s days', 'amazilon' ), 7 ); ?></option>
                    <option value="-30"><?php echo sprintf( __( 'last %s days', 'amazilon' ), 30 ); ?></option>
                    <option value="-90"><?php echo sprintf( __( 'last %s days', 'amazilon' ), 90 ); ?></option>
                    <option value="thismonth"><?php _e( 'this month', 'amazilon' ); ?></option>
                </select>
                <label for="amazilon-date"><?php _e( 'Date from', 'amazilon' ); ?>: </label>
                <input type="text" class="datepicker" name="amazilon-date-start" id="amazilon-date-start"
                       value="<?php if ( isset( $_REQUEST['amazilon-date-start'] ) ) {
					       echo esc_attr( $_REQUEST['amazilon-date-start'] );
				       } ?>"/>
                <label for="amazilon-date"><?php _e( 'Date until', 'amazilon' ); ?>: </label>
                <input type="text" class="datepicker" name="amazilon-date-end" id="amazilon-date-end"
                       value="<?php if ( isset( $_REQUEST['amazilon-date-end'] ) ) {
					       echo esc_attr( $_REQUEST['amazilon-date-end'] );
				       } ?>"/>
                <label for="amazilon-asin"><?php _e( 'ASIN', 'amazilon' ); ?>: </label>
                <input type="text" name="amazilon-asin" id="amazilon-asin"
                       value="<?php if ( isset( $_REQUEST['amazilon-asin'] ) ) {
					       echo esc_attr( $_REQUEST['amazilon-asin'] );
				       } ?>"/>
				<?php submit_button( __( 'Apply', 'amazilon' ), 'action', 'filter-submit', false ); ?>
				<?php submit_button( __( 'Reset', 'amazilon' ), 'doClear', 'filter-reset', false ); ?>
            </div>
			<?php
		} else {
			submit_button( __( 'Download as CSV', 'amazilon' ), 'action', 'amazilon-clicks-download', false );
		}
	}

	function no_items() {
		_e( 'No entries found.' );
	}

	function column_default( $item, $column_name ) {
		switch ( $column_name ) {
			case 'id':
			case 'sc':
			case 'tag':
			case 'clicks':
			case 'title':
				return $item[ $column_name ];
			case 'asin':
				return '<a href="#" onclick="jQuery(\'#amazilon-asin\').val(\'' . esc_attr( $item[ $column_name ] ) . '\');jQuery(\'#filter-submit\').click();return false;">' . esc_html( $item[ $column_name ] ) . '</a>';
			case 'referer':
				if ( substr( $item[ $column_name ], 0, 4 ) == 'http' ) {
					$iId = url_to_postid( $item[ $column_name ] );

					return ( ( (int) $iId > 0 && current_user_can( 'edit_posts' ) ) ? '<a href="' . get_edit_post_link( $iId ) . '">' . __( 'Edit' ) . '</a> | ' : '' ) . '<a href="' . esc_url( $item[ $column_name ] ) . '" target="_blank">' . $item[ $column_name ] . '</a><br />' . $item['title'];
				} else {
					return $item[ $column_name ] . '<br />' . $item['title'];
				}
			default:
				return 'N/A';
		}
	}

	function get_sortable_columns() {
		$sortable_columns = array(
			'clicks' => array( 'clicks', true ),
			'asin'   => array( 'asin', true ),
			'url'    => array( 'url', true ),
			'tag'    => array( 'tag', true ),
			'sc'     => array( 'sc', true ),
			'title'  => array( 'title', true )
		);

		return $sortable_columns;
	}

	function get_columns() {
		$columns = array(
			'clicks'  => __( 'Clicks', 'amazilon' ),
			'asin'    => __( 'ASIN', 'amazilon' ),
			'sc'      => __( 'Shortcode', 'amazilon' ),
			'tag'     => __( 'Associate-Tag', 'amazilon' ),
			'referer' => __( 'URL / Product', 'amazilon' )
		);

		return $columns;
	}

	function get_bulk_actions() {
	}

	function read_data() {
	}

	function prepare_items() {
		global $wpdb;
		$page                  = ( isset( $_GET['page'] ) ) ? esc_attr( $_GET['page'] ) : false;
		$columns               = $this->get_columns();
		$hidden                = array();
		$sortable              = $this->get_sortable_columns();
		$this->_column_headers = array( $columns, $hidden, $sortable );

		$orderby = ( ! empty( $_REQUEST['orderby'] ) ) ? $_REQUEST['orderby'] : 'clicks';
		$order   = ( ! empty( $_REQUEST['order'] ) ) ? $_REQUEST['order'] : 'desc';

		$per_page     = ( ! empty( $_REQUEST['per_page'] ) ) ? intval( $_REQUEST['per_page'] ) : 10;
		$current_page = $this->get_pagenum();

		$search = ( ! empty( $_REQUEST['s'] ) ) ? ' AND (asin LIKE "%' . esc_sql( $_REQUEST['s'] ) . '%" OR sc LIKE "%' . esc_sql( $_REQUEST['s'] ) . '%" OR tag LIKE "%' . esc_sql( $_REQUEST['s'] ) . '%" OR referer LIKE "%' . esc_sql( $_REQUEST['s'] ) . '%" OR title LIKE "%' . esc_sql( $_REQUEST['s'] ) . '%")' : '';

		$search .= ( ! empty( $_REQUEST['amazilon-date-start'] ) && preg_match( '=^\d{4}\-\d{2}\-\d{2}$=si',
				$_REQUEST['amazilon-date-start'] ) ) ? ' AND tstamp >= "' . esc_sql( $_REQUEST['amazilon-date-start'] ) . ' 00:00:00"' : '';

		$search .= ( ! empty( $_REQUEST['amazilon-date-end'] ) && preg_match( '=^\d{4}\-\d{2}\-\d{2}$=si',
				$_REQUEST['amazilon-date-end'] ) ) ? ' AND tstamp <= "' . esc_sql( $_REQUEST['amazilon-date-end'] ) . ' 23:59:59"' : '';

		$search .= ( ! empty( $_REQUEST['amazilon-asin'] ) ) ? ' AND asin = "' . esc_sql( $_REQUEST['amazilon-asin'] ) . '"' : '';

		if ( isset( $_REQUEST['amazilon-grouping'] ) && $_REQUEST['amazilon-grouping'] == 'asin' ) {
			$sFields  = 'id, asin, "*" AS sc, "*" AS tag, "*" AS referer, title, COUNT(*) AS clicks';
			$sGroupBy = 'asin';
		} elseif ( isset( $_REQUEST['amazilon-grouping'] ) && $_REQUEST['amazilon-grouping'] == 'sc' ) {
			$sFields  = 'id, "*" AS asin, sc, "*" AS tag, "*" AS referer, "" AS title, COUNT(*) AS clicks';
			$sGroupBy = 'sc';
		} elseif ( isset( $_REQUEST['amazilon-grouping'] ) && $_REQUEST['amazilon-grouping'] == 'tpl' ) {
			$sFields  = 'id, "*" AS asin, CONCAT(CASE WHEN LOCATE("|", sc) < 1 THEN LEFT(sc,LOCATE(")",sc)-1) ELSE LEFT(sc,LOCATE("|",sc)-1) END, "*") AS sc, "*" AS tag, "*" AS referer, "" AS title, COUNT(*) AS clicks';
			$sGroupBy = 'CONCAT(CASE WHEN LOCATE("|", sc) < 1 THEN LEFT(sc,LOCATE(")",sc)-1) ELSE LEFT(sc,LOCATE("|",sc)-1) END, "*")';
			$search   .= ' AND ( sc LIKE "template%" OR sc LIKE "vergleich%" OR sc LIKE "vergleich2%" )';
		} else {
			$sFields  = 'id, asin, sc, tag, referer, title, COUNT(*) AS clicks';
			$sGroupBy = 'c32';
		}

		$this->items = $wpdb->get_results( 'SELECT ' . $sFields . ' FROM ' . $wpdb->prefix . 'amazilon_clicktracker WHERE 1' . $search . ' GROUP BY ' . $sGroupBy . ' ORDER BY ' . esc_sql( $orderby ) . ' ' . esc_sql( $order ) . ' LIMIT ' . intval( ( $current_page - 1 ) * $per_page ) . ', ' . intval( $per_page ),
			ARRAY_A );
		$total_items = $wpdb->query( 'SELECT COUNT(*) FROM ' . $wpdb->prefix . 'amazilon_clicktracker WHERE 1' . $search . ' GROUP BY ' . $sGroupBy );

		$this->set_pagination_args( array(
				'total_items' => $total_items,
				'per_page'    => $per_page
			)
		);
	}

}
