<?php
/**
 * Example Shortcode
 */

add_shortcode( 'crb_table_view', 'crb_shortcode_crb_table_view' );
function crb_shortcode_crb_table_view( $atts, $content ) {
	global $wpdb;

	$page = max(get_query_var('paged'), 1);
	$results_per_page = 12; // 2 results * 6 options
	$offset = ($page - 1) * $results_per_page;

	$form_entry_metas = $wpdb->get_results( "
		SELECT
			SQL_CALC_FOUND_ROWS *,
			form_entries.id AS entry_id,
			form_entries.created_at,
			entry_meta.meta_value,
			form_fields.field_key

		FROM `{$wpdb->prefix}frm_items` as form_entries
		INNER JOIN `{$wpdb->prefix}frm_item_metas` as entry_meta
		 	ON form_entries.id = entry_meta.item_id
		INNER JOIN `{$wpdb->prefix}frm_fields` as form_fields
		 	ON entry_meta.field_id = form_fields.id

		 	WHERE form_entries.form_id = 6
		 	AND form_fields.field_key IN ( 'lyon_source', 'lyon_select_state', 'borrower_email', 'borrower_pri_ph', 'loan_amt_request', 'borrower_fullname' )
			LIMIT $offset, $results_per_page");


	$number_of_found_results = $wpdb->get_var('SELECT FOUND_ROWS()');

	$results = [];
	foreach ( $form_entry_metas as $entry ) {
		$results[$entry->entry_id]['created'] = $entry->created_at;
		$results[$entry->entry_id]['fields'][$entry->field_key] = $entry->meta_value;
	}

	ob_start(); ?>

	<table id="crb_lyon_applications_table" class="hover order-column" data-posts-per-page="4">
		<thead>
			<tr>
				<th class="lyon-compact filters-row" colspan="10" >
					<input type="text" id="picker-start-date" class="datepicker-from" placeholder="Select Start Date">
					<input type="text" id="picker-end-date" class="datepicker-to" placeholder="Select End Date">
					<select class="filter referring_co" id="1">
						<option value=""><?php _e( 'Select Company Referrer', 'crb' ); ?></option>
					</select>
					<select class="filter referring_parent_co" id="0">
						<option value=""><?php _e( 'Select Parent Company', 'crb' ); ?></option>
					</select>
				</th>
			</tr>

			<tr>
				<!--<th class="lyon-compact referring_parent_co"></th>
				<th class="lyon-compact referring_co"></th>-->
				<th class="lyon-compact"><?php _e( 'DETAIL', 'crb' ); ?></th>
				<th class="lyon-compact"><?php _e( 'Receive Date', 'crb' ); ?></th>
				<th class="lyon-compact"><?php _e( 'Source', 'crb' ); ?></th>
				<th class="lyon-compact"><?php _e( 'State', 'crb' ); ?></th>
				<th class="lyon-compact"><?php _e( 'Full Name', 'crb' ); ?></th>
				<th class="lyon-compact"><?php _e( 'Email', 'crb' ); ?></th>
				<th class="lyon-compact"><?php _e( 'Phone', 'crb' ); ?></th>
				<th class="lyon-compact"><?php _e( 'Loan Requested', 'crb' ); ?></th>
			</tr>
		</thead>

		<tbody>
			<?php foreach ( $results as $id => $result ) :

				$date_created = new DateTime( $result['created'] );
				?>
				<tr>
					<!-- <td class="lyon-body referring_parent_co"><?php echo isset( $result['fields']['referring_parent_co'] ) ? $result['fields']['referring_parent_co'] : '' ?></td>
					<td class="lyon-body referring_co"><?php echo isset( $result['fields']['referring_co'] ) ? $result['fields']['referring_co'] : '' ?></td> -->
					<td class="lyon-body">
						<?php if ( $result['fields']['lyon_source'] === 'web' ) : ?>
							<?php if ( $result['fields']['lyon_select_state'] === 'Texas' ) : ?>
								<a href="/application-detail/?apptype=texas&entryid=<?php echo $id ?>">Detail</a>
							<?php else : ?>
								<a href="/aplication-detail/?apptype=other&entryid=<?php echo $id ?>">Detail</a>
							<?php endif ?>
						<?php elseif ( $result['fields']['lyon_source'] === 'xml' ) : ?>
							<a href="/application-detail/?apptype=xml&entryid=<?php echo $id ?>">Detail</a>
						<?php endif; ?>
					</td>
					<td class="lyon-body"><?php echo $date_created->format('m/d/Y h:i a'); ?></td>
					<td class="lyon-body"><?php echo $result['fields']['lyon_source']; ?></td>
					<td class="lyon-body"><?php echo $result['fields']['lyon_select_state']; ?></td>
					<td class="lyon-body"><?php echo $result['fields']['borrower_fullname']; ?></td>
					<td class="lyon-body"><a href="mailto:<?php echo $result['fields']['borrower_email']; ?>"><?php echo $result['fields']['borrower_email']; ?></a></td>
					<td class="lyon-body"><?php echo $result['fields']['borrower_pri_ph']; ?></td>
					<td class="lyon-body"><?php echo $result['fields']['loan_amt_request']; ?></td>
				</tr>
			<?php endforeach; ?>
		</tbody>
	</table>

	<?php

	$max_pages = ceil($number_of_found_results / $results_per_page );
	if ($max_pages > 1) { ?>
		<div class="pagination">
			<ul>
				<?php
				if ($page > 1) echo '<li><a href="' . get_pagenum_link($page-1) . '" class="previouspostslink"></a></li>';

				for ($i=1; $i <= $max_pages; $i++) {
					echo '<li ' . ( $page == $i ? 'class="current_page_item"' : '' ) . '><a href="' . get_pagenum_link($i) . '">' . $i . '</a></li>';
				}
				if ($page < $max_pages) echo '<li><a href="' . get_pagenum_link($page+1) . '" class="nextpostslink"></a></li>';
				?>
			</ul>
		</div>
		<?php
	}

	$html = ob_get_clean();

	return $html;
}