<?php
global $wpdb;

$default_from = '2017-12-14';
$default_to = current_time('Y-m-d');
if (isset($_GET['reset'])) {
    $_SESSION['reports']['contacts']['from'] = $default_from;
    $_SESSION['reports']['contacts']['to'] = $default_to;
    wp_redirect(admin_url('admin.php?page=reports'));
    exit;
}
$user = isset($_GET['user']) ? intval($_GET['user']) : 0;

if (!empty($_POST['date_from']) && !empty($_POST['date_to'])) {
    $_SESSION['reports']['contacts']['from'] = date('Y-m-d', strtotime($_POST['date_from'], current_time('timestamp')));
    $_SESSION['reports']['contacts']['to'] = date('Y-m-d', strtotime($_POST['date_to'], current_time('timestamp')));
} else if (empty($_SESSION['reports']['contacts']['from']) || empty($_SESSION['reports']['contacts']['to'])) {
    $_SESSION['reports']['contacts']['from'] = $default_from;
    $_SESSION['reports']['contacts']['to'] = $default_to;
}

// get counts by author
$sql = "SELECT COUNT(*) AS contact_count, display_name, post_author FROM {$wpdb->posts}
        LEFT JOIN {$wpdb->users} ON {$wpdb->users}.ID = post_author
        WHERE post_type = 'contact' AND post_status = 'publish'
        AND post_date BETWEEN '{$_SESSION['reports']['contacts']['from']} 00:00:00' AND '{$_SESSION['reports']['contacts']['to']} 23:59:59'
        GROUP BY post_author ORDER BY display_name";
$users = $wpdb->get_results($sql);
$total_users = count($users);
$total_count = array_reduce($users, function($carry, $item) {
    return $carry + $item->contact_count;
}, 0);
$user_filter = $user ? 'AND post_author = ' . $user : '';

// get contacts by author
$sql = "SELECT {$wpdb->posts}.*, display_name FROM {$wpdb->posts}
        LEFT JOIN {$wpdb->users} ON {$wpdb->users}.ID = post_author
        WHERE post_type = 'contact' AND post_status = 'publish' $user_filter
        AND post_date BETWEEN '{$_SESSION['reports']['contacts']['from']} 00:00:00' AND '{$_SESSION['reports']['contacts']['to']} 23:59:59'
        ORDER BY display_name, post_date DESC, post_title";
$results = $wpdb->get_results($sql);
$total = count($results);
$table_nav = '
    <div class="alignleft actions">
        <form action="" method="post">
        	From: <input type="text" name="date_from" class="datepicker" value="' . date('m/d/Y', strtotime($_SESSION['reports']['contacts']['from'], current_time('timestamp'))) . '">
        	To: <input type="text" name="date_to" class="datepicker" value="' . date('m/d/Y', strtotime($_SESSION['reports']['contacts']['to'], current_time('timestamp'))) . '">
        	<input type="submit" class="button" value="Go">
            ' . (
                $_SESSION['reports']['contacts']['from'] != $default_from || $_SESSION['reports']['contacts']['to'] != $default_to
                    ? '<a href="' . add_query_arg('reset', true) . '">Reset</a>'
                    : ''
            ) . '
        </form>
    </div>
    <div class="tablenav-pages"><span class="displaying-num">' . $total . ' contacts</span></div>
    <br class="clear">';
$table_headers = '
    <tr>
        <th class="manage-column column-cb index-column">&nbsp;</th>
        <th scope="col" class="manage-column column-primary">Contact</th>
        <th scope="col" class="manage-column column-author">Date Created</th>
        <th scope="col" class="manage-column column-author">Author</th>
    </tr>';
?>
<div class="wrap">
<h1>Contacts Report</h1>
<ul class="subsubsub">
    <li><a href="<?php echo remove_query_arg('user'); ?>" class="<?php if (!$user) echo 'current'; ?>">All <span class="count">(<?php echo $total_count; ?>)</span></a> |</li>
    <?php foreach ($users as $i => $row) : ?>
       <li><a href="<?php echo add_query_arg('user', $row->post_author); ?>" class="<?php if ($user == $row->post_author) echo 'current'; ?>"><?php echo $row->display_name; ?> <span class="count">(<?php echo $row->contact_count; ?>)</span></a><?php if ($i < $total_users - 1) echo ' |'; ?></li>
    <?php endforeach; ?>
</ul>
<div class="tablenav top">
    <?php echo $table_nav; ?>
</div>
<table class="wp-list-table widefat fixed striped posts">
	<thead>
    	<?php echo $table_headers; ?>
	</thead>
	<tbody id="the-list">
    <?php if ($total) : ?>
        <?php foreach ($results as $i => $row) :
            ?>
        <tr class="iedit author-self level-0 type-page status-publish hentry">
			<th scope="row" class="index-column" valign="top"><?php echo $i + 1; ?>.</th>
            <td data-colname="Contact" class="title column-title has-row-actions column-primary page-title">
                <strong><a href="<?php echo admin_url('post.php?action=edit&post=' . $row->ID); ?>"><?php echo $row->post_title; ?></a></strong>
                <button type="button" class="toggle-row"><span class="screen-reader-text">Show more details</span></button>
            </td>
            <td data-colname="Date Created"><?php echo get_the_date('m/d/Y', $row->ID); ?></td>
            <td data-colname="Author"><?php echo $row->display_name; ?></td>
        </tr>
        <?php endforeach; ?>
    <?php else : ?>
		<tr class="no-items"><td class="colspanchange" colspan="4">No contacts found.</td></tr>
    <?php endif; ?>
    </tbody>
	<tfoot>
    	<?php echo $table_headers; ?>
	</tfoot>
</table>
<div class="tablenav bottom">
	<?php echo $table_nav; ?>
</div>
</div>
<script>
jQuery(document).ready(function($) {
    $('.datepicker').datepicker({
        maxDate: new Date(),
        dateFormat: 'mm/dd/yy',
    });
});
</script>